Firefox Bookmarks SQLite structure

Question

I am trying to write a Firefox 3 add-on which will enable me to easily re-tag bookmarks. For example I have some bookmarks tagged "development" and some tagged "Development" and I would like a way to easily update all the "delelopment" tags to "Development". Unfortunately I can not find an add-on to do this so I thought I would create my own.

Having not developed an add-on before I've managed to grasp the basics and discovered that FireFox stores all bookmarks in an SQLite database called Places.sqlite. Within that database there is a table called moz_bookmarks which contains all the bookmarks, tags and folders within the bookmarks directory. The structure of the bookmark folders and their child bookmarks is represented using a foreign key id which points to the parent folder's id in the same table which again recursses upwards to that parent folder's Id until it hits the bookmarks root.

However, where I become stuck is how the tags you apply in firefox are related to the bookmarks. Each tag has a type = 2 and parent ID = 4. However I can see no correlation between this and an actual bookmarks that use the tag. If I add a bookmark in firefox to no particular folder but give it 2 or 3 tags then it's parent folder ID is 5 which corresponds to "unfiled" but I can see no further correlation to the tags associated with it.

I have found this Wiki page on the structure but it does not really help.

It's driving me nuts :( Please help...

Celestial

Related: How can I restore Firefox bookmark files from SQLite files? – Paymar

Answer 1

You probably already found out yourself, but tags are applied as follows:

The central place for all URLS in the database is moz_places. The table moz_bookmarks refers to it by the foreign key column fk.

If you tag a bookmark, there are multiple entries in moz_bookmarks, all having the same reference fk: The first is the bookmark itself (having the title in the title column) For each tag, there's an additional entriy in moz_bookmarks having the same foreign key fk and refering to the tag in the parent coumn (which points to the moz_bookmarks row for the tag).

If you have a bookmark 'http://stackoverflow.com' titled 'Stackoverflow' with tags 'programming' and 'info', you will get:

moz_places
----------
id    url   (some more)
3636  http://stackoverflow.com

moz_bookmarks
-------------
id    type    fk     parent    title          (other columns omitted...)
332   1       3636   5         Stackoverflow  (parent=5 -> unfiled folder)
333   2       (NULL) 4         programming    (programming tag, parent=4 -> tags folder)
334   1       3636   333       (NULL)         (link to 'programming' tag)
335   2       (NULL) 4         info           (info tag, parent=4 see above)
336   1       3636   335       (NULL)         (link to 'info' tag)

Hope this helps...

Persiflage

thank you martin for returning to this question after so long. I had kinda moved on but failed to update this so thank you and keep up the good work. – Celestial

Answer 2

As MartinStettner suggested tag structures are based on the foreign key for the tag id so you first have to determine the moz_bookmark.id for the target tag.

This Mozilla PDF explains the relationship in sqllite ...

Tags result in two new entries in moz_bookmarks. The first one is the tag, with parent=4 (tags), and fk=NULL. The second entry follows the first one and has the previous tag as its parent, and fk points to the proper entry in moz_places.

Using that as a guide ... Once you know the id for the tag you can join moz_places.id ON moz_bookmarks.fk ...

    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 = N

Export ...

Holstein

Answer 3

I can't quite help you with the how-to, however, perhaps the extension 'SQLite Manager' will help you at least for the part where you're trying to figure out what to do. The plugin is a general manager, but it contains the default databases used by Firefox as standard option as well.

Using that extension it should be relatively straightforward to rename the keywords you like. If you're just looking for a way to fix it, this could work, if you still prefer to write your own tool, perhaps this one can at least help with the queries ;).

Solicitous

thanks for this comment, i had discovered this plugin. it's very useful for development and general Firefox curiosities. – Celestial

Answer 4

I can't help much either -- found your question looking for the same kind of answers...

What I have managed to find is the relevant Mozilla documentation. The bookmark system is called Places, and the the database tables are described at https://developer.mozilla.org/en-US/docs/The_Places_database.

Inviting

Answer 5

Because I had to dig deeper to find this information, here is the request to fetch url, title and creation date of all your bookmarks:

SELECT h.url, b.title, b.dateAdded
FROM moz_places h
JOIN moz_bookmarks b
ON h.id = b.fk;

I hope it'd help people looking for this answer.

Gamache