SQL Script to search or extract bookmark

I have something like 6k bookmarks. And sometime I need to search for very specific bookmark (with specific tittle and url and tags) or I need to export only some bookmark. It's really difficult to do with the browser. I use [heidisql ](https://www.heidisql.com/)to connect to the sqlite database, that is `places.sqlite` in `C:\Users\YourName\AppData\Roaming\Mozilla\Firefox\Profiles\YourFirefoxProfileId\`.

So here's the script to query all firefox bookmarks

--tags and folders are all also named in the moz_bookmarks table. All unique tags are of type=2 and parent=4. While all unique folders are of type=2 and parent<>4.

--the column parent is self-reference to column id.

--each unique bookmark is identified by unique fk. each unique bookmark can have few entries in the table (different ids). One is for the title, and the others are for tags. One entry for every tag. All entry for the same bookmark have same fk.

--Every tags on a bookmark identified by reference in column parent, that itself is a reference to column id.

WITH

all_links AS (

SELECT A.fk,B.folder,A.title,datetime(A.dateAdded/1000000,'unixEpoch') AS dateAdded,datetime(A.lastModified/1000000,'unixEpoch') AS lastModified FROM

(SELECT * FROM moz_bookmarks WHERE TYPE=1 AND moz_bookmarks.title IS NOT NULL) A

INNER JOIN

(SELECT id,title as folder FROM moz_bookmarks WHERE TYPE = 2 AND parent<>4) B

ON A.parent=B.id),

folders AS (

SELECT id,title FROM moz_bookmarks WHERE TYPE = 2 AND parent<>4),

with_tags AS (

SELECT fk, GROUP_CONCAT(grouped_tags.title) AS tags FROM

(SELECT all_tags.*,moz_bookmarks.title FROM

(SELECT fk,parent FROM moz_bookmarks WHERE title IS NULL AND TYPE=1) all_tags

INNER JOIN

moz_bookmarks

ON all_tags.parent=moz_bookmarks.id) grouped_tags

GROUP BY fk)

SELECT moz_links.*,moz_places.url,moz_places.description,moz_places.title,moz_places.preview_image_url FROM

(SELECT all_links.fk,all_links.folder,all_links.title,all_links.dateAdded,all_links.lastModified,with_tags.tags FROM

all_links

LEFT JOIN

with_tags

ON with_tags.fk=all_links.fk) moz_links

INNER JOIN

moz_places

ON moz_links.fk=moz_places.id

--WHERE moz_links.title LIKE '%how%' AND url LIKE '%reddit.com%' AND tags LIKE '%Linux%'

--fruther query here ^

https://preview.redd.it/n49017dvfczf1.png?width=1920&format=png&auto=webp&s=4040c3578240c11f3ee41202b21af2b391e9fa4f