How to display bookmarks tagged 'press_germany' from Places.sqlite database? I use the SQLite Manager extension and DB Browser for SQLite.
I do not see any table of tags and do not know how Firefox creates these tags. All I found is:
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'
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;