Hacking Firefox bookmarks

I have a few thousand bookmarks in Firefox, many of which are tagged with project_ tags. I use these to bookmark material for future reference on subjects which I want read more about at some future date…

It turns out I’ve now got rather a lot of these project tags, and between them many hundreds of bookmarks. The bookmarks sit there, accumulating, waiting to fulfill their destiny and provide me with sweet, sweet knowledge.

To encourage myself to make some decisions about which are worth pursuing I wanted to get a list of these various project tags, together with a count of how many bookmarks I have saved for each.

Firefox stores bookmarks in a SQLite database called places.sqlite which can be perused with any SQLite capable database manager. I used the SQLite Manager add-on because it does exactly what I needed.

Find tags by pattern

SELECT title 
FROM moz_bookmarks 
WHERE title LIKE 'project_%' 
AND parent = 4

Count bookmarks for a given tag

SELECT tag.title, COUNT(bookmark.fk) 
FROM moz_bookmarks AS tag
JOIN moz_bookmarks AS bookmark 
ON bookmark.parent = tag.id
WHERE tag.title = 'project_javascript' 
AND tag.parent = 4

Find tags and count by pattern

After a few attempts I managed to combine these two into the final query that gives me the numbers I wanted:

SELECT tag.title, COUNT(tag.title)
FROM moz_bookmarks AS tag
LEFT JOIN moz_bookmarks AS bookmark
ON bookmark.parent = tag.id
WHERE tag.title LIKE 'project_%'
AND tag.parent = 4
GROUP BY tag.title
ORDER BY tag.title ASC