copy it into a fixed width character terminal for better view
firefox places.sqlite contains all bookmarks
sqlite3 places.sqlite "select sql from sqlite_master
where tbl_name = 'moz_bookmarks' and type = 'table';"
each bookmark has a base entry in moz_bookmarks
where it has the title and the fk that links to moz_places.id
and it has an entry with the same fk for each tag
moz_places has an entry with id = moz_bookmarks.fk that holds the URL
and it has a title that usually is the same with that in moz_bookmarks
moz_bookmarks.title is more about the page, moz_places.title is more about the site
there is no id = 0 record in moz_bookmarks or moz_places
the id = 0 record is referred to signal the root of the reference chain
in moz_bookmarks:
a bookmark has:
one entry that contains its title and it's followed by
entries with the same fk
that link through their parent to one tag of that bookmark each
all those entries with the same fk have a null title, since this query returns no results:
select id, title from moz_bookmarks m where
(select parent from moz_bookmarks where id = m.parent) = 4 and
-- this selects entries that refer tags
-- because entries referred by their parent have parent = 4
title not null;
type is 1 for bookmarks and their links to tags
position does not appear to be important, maybe it refers to the position in the displayed list
keyword_id is null
folder_type is null
fields after title are not relevant for the bookmarks -- tags relationship
tags have:
title is the name of the tag
parent = 4, which is the id of the Tags folder
type = 2
fk = null
a folder has:
title is the name of the folder
parent is the id of the parent folder
the root folder is the Bookmarks Menu with id = 2
type = 2
fk = null
to select all tags of a bookmark:
parent = 4 eliminates the folder referred in the bookmark entry that contains the title
if there is more than one bookmark with that exact title then
fk = (select ...) only fits the first result
but if you replace that '=' with 'in', then all tags of all those bookmarks are listed in one column
tags used more than once by different bookmarks will appear only once in the list
sqlite> select title from moz_bookmarks where id in (select parent from moz_bookmarks where fk = (select fk from moz_bookmarks where title = 'David Icke – The Answer – full length interview. Direct no-email version – starts at 6.46 and please share with everyone you can to bypass censorship – David Icke')) and parent = 4;
or:
parent = 4 eliminates the folder referred in the bookmark entry that contains the title
verified
if there is more than one bookmark with that exact title then
all tags of all those bookmarks are listed in one column and
tags used more than once by different bookmarks will appear more than once in the list
sqlite> select m1.title from moz_bookmarks as m1, moz_bookmarks as m2, moz_bookmarks as m3 where m3.title = 'David Icke – The Answer – full length interview. Direct no-email version – starts at 6.46 and please share with everyone you can to bypass censorship – David Icke' and m2.fk = m3.fk and m1.id = m2.parent and m1.parent = 4;
the first version is ten times faster than the second version
time sqlite3 places.sqlite "select title from moz_bookmarks where id in (select parent from moz_bookmarks where fk = (select fk from moz_bookmarks where title = 'David Icke – The Answer – full length interview. Direct no-email version – starts at 6.46 and please share with everyone you can to bypass censorship – David Icke')) and parent = 4;"
NWO
David Icke
politics
news
video
continue
1:00:00 continue
real 0m0.012s
user 0m0.011s
sys 0m0.001s
time sqlite3 places.sqlite "select m1.title from moz_bookmarks as m1, moz_bookmarks as m2, moz_bookmarks as m3 where m3.title = 'David Icke – The Answer – full length interview. Direct no-email version – starts at 6.46 and please share with everyone you can to bypass censorship – David Icke' and m2.fk = m3.fk and m1.id = m2.parent and m1.parent = 4;"
NWO
David Icke
politics
news
video
continue
1:00:00 continue
real 0m0.183s
user 0m0.077s
sys 0m0.105s
to select all bookmarks that have a specific tag:
title is not null only for the bookmark entry that contains the title.
we know this because this returns no lines:
the subquery selects the id's of all tags
the outer query selects all references to all tags that have not null titles
sqlite> select title from moz_bookmarks where
parent in (select id from moz_bookmarks where parent = 4) and title is not null;
sqlite> select id, title from moz_bookmarks where fk in (select fk from moz_bookmarks where parent = (select id from moz_bookmarks where parent = 4 and title = '1984')) and title is not null;
sqlite> select m1.id, m1.title from moz_bookmarks m1, moz_bookmarks m2, moz_bookmarks m3 where m3.parent = 4 and m3.title = 'David Icke' and m3.id = m2.parent and m2.fk = m1.fk and m1.title not null;
select m1.id, m1.title, count(*) from moz_bookmarks m1, moz_bookmarks m2, moz_bookmarks m3 where m3.parent = 4 and m3.title = 'David Icke' and m3.id = m2.parent and m2.fk = m1.fk and m1.title not null;
1776|What David Icke Said in 1991, Dr Michio Kaku Confirms in 2008 - YouTube|443
time sqlite3 places.sqlite "select id, title from moz_bookmarks where fk in (select fk from moz_bookmarks where parent = (select id from moz_bookmarks where parent = 4 and title = 'David Icke')) and title is not null;"
real 0m0.011s
user 0m0.004s
sys 0m0.008s
time sqlite3 places.sqlite "select m1.id, m1.title from moz_bookmarks m1, moz_bookmarks m2, moz_bookmarks m3 where m3.parent = 4 and m3.title = 'David Icke' and m3.id = m2.parent and m2.fk = m1.fk and m1.title not null;"
real 0m0.012s
user 0m0.008s
sys 0m0.004s
to update a tag in all bookmarks
verified
you can use Firefox > Library window
find oldtag in Search Bookmarks
select all found bookmarks
edit oldtag into newtag in Tags
press Enter
or
verified
sqlite:
you need to update the parent of all entries that refer that bookmark:
the new tag must be already defined
there should be no entries that have both the old tag and the new tag at the same time
both these conditions can be easily configured in Firefox > Library window
sqlite3> update moz_bookmarks set parent = (select id from moz_bookmarks where title = 'new tag' and parent = 4) where id in (select id from moz_bookmarks where parent = (select id from moz_bookmarks where title = 'old tag' and parent = 4));
