sqlsqlitefirefoxbookmarks

sql query on tags in places.sqlite of firefox


How to display bookmarks that have the same tag labeled 'press_germany' from Places.sqlite database. I use the SQLite Manager extension as well as the DB Browser for SQLite software. I do not see any table of tags, and I do not know how firefox creates these tags.

All that I have found approaching my goal is in the following links:

stackoverflow, superuser.com.

I tried this query but it shows 0 results:

SELECT moz_places.url, moz_places.title   
FROM moz_places    
LEFT OUTER JOIN moz_bookmarks    
ON moz_places.id = moz_bookmarks.fk    
WHERE moz_bookmarks.title = 'press_germany'

Thanks.

A places.sqlite schema if helpful: Mozilla Developer The Places database


Solution

  • When you create a tag in Firefox, it creates an entry in moz_bookmarks where the title column will contain the tag, and the fk (foreign key that points to moz_places.id. Any bookmarks under that tag will have moz_bookmarks.parent pointing to the id of the tag. So the first thing you need to do is find the id of the tag.

    SELECT moz_bookmarks.id from moz_bookmarks
    WHERE moz_bookmarks.title == 'press_germany' AND moz_bookmarks.fk IS NULL
    

    By including the AND statement, you are ensuring you hit on the tag. If it's an actual bookmark, fk will have a value. We don't want that, we want fk to be NULL.

    Now that we have that, we need to use that in a query. You can do that as follows:

    SELECT moz_bookmarks.title
    FROM moz_bookmarks, (SELECT moz_bookmarks.id from moz_bookmarks
    WHERE moz_bookmarks.title == 'press_germany' AND moz_bookmarks.fk IS NULL) tag
    WHERE moz_bookmarks.parent == tag.id
    

    Note in the above query, we are using the results of the first query in the FROM statement (much like you did - but commenting for those who may not be familiar with this), and assigning it's results to the alias 'tag' (you can pick whatever alias you want as you probably know, just don't use a reserved word or create a collision with a field name).

    Your final statement would look something like this I suspect (if not, I'm pretty sure you will be able to tweak it from there):

    SELECT moz_places.id, moz_bookmarks.title, moz_places.url
    FROM  moz_bookmarks, (SELECT moz_bookmarks.id from moz_bookmarks
    WHERE moz_bookmarks.title == 'press_germany' AND moz_bookmarks.fk IS NULL) tag
    LEFT OUTER JOIN moz_places
    ON moz_places.id == moz_bookmarks.fk
    WHERE moz_bookmarks.parent == tag.id
    

    The above will not do a recursive list. In other words, if you have sub-folders within the folder, the sub-folder names will be listed in your query, but not their content, including sub-sub folders.

    If you want to pull all bookmarks recursively, here's a query I was able to come up with by taking someone else's query and tweaking it. Beyond that, I am not skilled at recursive queries.

    WITH RECURSIVE
    under_root(id, level) AS (
    VALUES (0,0)
    UNION ALL
    SELECT moz_bookmarks.id, under_root.level+1
       FROM moz_bookmarks JOIN under_root ON moz_bookmarks.parent=under_root.id
       ORDER BY 2 DESC
    )
    SELECT substr('.....................................................................................................',1,level*5) ||  moz_bookmarks.title AS "TITLE", CASE WHEN moz_places.url is null THEN "" ELSE moz_places.url END AS "URL", datetime(moz_bookmarks.dateAdded/1000000,"UNIXEPOCH","LOCALTIME") AS "Date Added", datetime(lastModified/1000000,"UNIXEPOCH","LOCALTIME") AS "Last Modified", CASE WHEN datetime(moz_places.last_visit_date/1000000,"UNIXEPOCH","LOCALTIME") IS null THEN "" ELSE datetime(moz_places.last_visit_date/1000000,'UNIXEPOCH','LOCALTIME') END AS "Last Visit Date", CASE WHEN moz_places.visit_count IS null OR moz_places.visit_count=0 THEN "" ELSE moz_places.visit_count END FROM moz_bookmarks JOIN under_root ON moz_bookmarks.id=under_root.id LEFT JOIN moz_places on moz_places.id=moz_bookmarks.fk;