Firefox Bookmarks SQLite structure
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...
Answer
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...