How to "delete empty bookmark folders" or "merge duplicate folders"

Question

After many bad sync experiences with XMarks+Firefox+Chrome+Android my bookmark folders are completely messed up: I have duplicate folder names, many but not all of them empty.

I stopped using Xmarks a while ago, obviously, to stop it from ruining things. I don't know how many bookmarks are gone... Xmarks has a convenient history of bookmark synchronizations, but it is too troublesome to fix everything from there (no automated way)

I'm thinking about manually editing places.sqlite bookmarks database...

Answer 1

After looking for a while I decided to roll fix manually the places.sqlite Firefox's bookmark database.

After downloading an sqlite3 client, and of course backing up my places.sqlite file I came with these queries.

In my case, I was not especially aware of descriptions, keyword or any metadata associated with the bookmarks.

1- Create an auxiliary table with one id->folder name:

CREATE TABLE ufld AS 
    SELECT min(id) AS id, title
    FROM moz_bookmarks
    group by title
    order by title;

2- Create another auxiliary table where every bookmark has its parent folder name and its ufld name (more columns than needed if you wanna check things out first):

CREATE TABLE bkm_fld_ufld AS 
SELECT 
    b.id AS bid, 
    b.parent AS bparent, 
    b.title AS btitle, 
    b.type AS btype,
    fld.id AS fid, 
    fld.parent AS fparent,
    fld.title AS ftitle,
    fld.type AS ftype,
    ufld.id AS ufldid,
    ufld.title AS ufldtitle
FROM moz_bookmarks b 
JOIN moz_bookmarks fld ON 
    fld.id = b.parent
JOIN ufld ON 
    ufld.title = fld.title;

3- Update the bookmarks using the auxiliary tables (leaving out 'special folders'):

UPDATE moz_bookmarks 
SET parent = (SELECT bkm_fld_ufld.ufldid 
              FROM bkm_fld_ufld 
              WHERE bkm_fld_ufld.bid = moz_bookmarks.id) 
WHERE guid NOT GLOB '*___';

4- Clean up:

DROP table ufld;
DROP table bkm_fld_ufld;

And that's it, now I can copy back the places.sqlite in my Firefox profile folder and enjoy the refreshed and cleaned bookmarks.

If someone comes up with a better way to do this, I'm hearing...

UPDATE for Firefox Sync

If Firefox Sync is enabled, then it will try to restore the tabs as they were before tinkering with the places.sqlite file.

The workaround was:

1. Disconnect Firefox from Firefox Sync
2. Fix places.sqlite as explained earlier
3. Export fixed bookmarks to a file
4. Connect to Firefox Sync and wait for it to do its thing
5. Delete all bookmarks
6. Restore the backup file of the fixed bookmarks

Voilà... Firefox Sync is now syncing all your connected devices with the fixed bookmarks