Module: QueryConstants

Included in:

BookmarksReader

Defined in:

lib/fundler/fundler_utils.rb

Overview

Schema

References

stackoverflow.com/questions/464516/firefox-bookmarks-sqlite-structure developer.mozilla.org/en-US/docs/Places developer.mozilla.org/en-US/docs/Retrieving_part_of_the_bookmarks_tree developer.mozilla.org/en-US/docs/XPCOM_Interface_Reference/nsINavBookmarksService davidkoepi.wordpress.com/2010/11/27/firefoxforensics/

Queries

select moz_places.url, moz_bookmarks.title from moz_places,moz_bookmarks where moz_places.id = moz_bookmarks.fk and moz_bookmarks.title != ”;

select keyword,url from moz_keywords left join moz_bookmarks on (moz_keywords.id = keyword_id) left join moz_places on (fk = moz_places.id);

select moz_places.url, datetime((moz_historyvisits.visit_date/1000000), ‘unixepoch’, ‘localtime’), moz_historyvisits.visit_type from moz_places, moz_historyvisits where moz_historyvisits.place_id = moz_places.id order by moz_historyvisits.visit_date desc;

Constant Summary

BOOKMARKS_QUERY =

all bookmarks

<<-SQL
  SELECT DISTINCT
      moz_places.url AS url,
      moz_bookmarks.title AS title,
      moz_bookmarks.id AS id,
      moz_items_annos.content AS description
  FROM
      moz_places,
      moz_bookmarks,
      moz_items_annos,
      moz_anno_attributes
  WHERE
      moz_anno_attributes.name = 'bookmarkProperties/description' AND
      moz_items_annos.anno_attribute_id = moz_anno_attributes.id AND
      moz_items_annos.item_id = moz_bookmarks.id AND
      moz_places.id = moz_bookmarks.fk AND
      moz_places.id IN (
              SELECT DISTINCT fk 
              FROM moz_bookmarks
              WHERE parent IN (
                  SELECT moz_bookmarks.id
                  FROM moz_bookmarks, moz_bookmarks_roots
                  WHERE moz_bookmarks_roots.root_name = 'tags'
                  AND moz_bookmarks.parent = moz_bookmarks_roots.folder_id
              )
          )
  ORDER BY UPPER(moz_bookmarks.title) ASC
SQL

QUERY_FOR_TAGS =

tag.id|tag.title

<<-SQL
  SELECT id, title FROM moz_bookmarks WHERE parent = 4;
SQL

QUERY_BY_TAG =

all bookmarks with the “given” tag.id

<<-SQL
  SELECT moz_places.id, moz_places.url, moz_places.title, moz_bookmarks.parent    
  FROM moz_places    
  LEFT OUTER JOIN moz_bookmarks    
  ON moz_places.id = moz_bookmarks.fk    
  WHERE moz_bookmarks.parent = @tag_id;
SQL

QUERY_FOR_SIMPLE_BOOKMARKS =

bookmark.id|bookmark.fk|bookmark.title …

> 1933|1387|SQLite Home Page

…

<<-SQL
  SELECT id,fk,title FROM moz_bookmarks WHERE parent = 2;
SQL

QUERY_FOR_LINK =

select the link between bookmars and tags fk_id = 1387

> 1934|1387|1144

<<-SQL
  SELECT id,fk,parent FROM moz_bookmarks WHERE title is null AND fk = @fk_id;
SQL

QUERY_FOR_TAG_BY_ID =

tag.id|tag.title parent = 1144

> 1144|coding

<<-SQL
  SELECT id, title FROM moz_bookmarks WHERE parent = 4 AND id = @parent;
SQL