Search Support

Avoid support scams. We will never ask you to call or text a phone number or share personal information. Please report suspicious activity using the “Report Abuse” option.

Learn More

Delete query in SQLite3 for the "Other Bookmarks" folder

more options

Would this SQLite3 query safely / properly delete all of the bookmarks in the "Other Bookmarks" folder?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

DELETE FROM moz_bookmarks WHERE parent = 5

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

This is what I think, using "DB Browser for SQLite" to look at:

"C:\Program Files (x86)\Mozilla Firefox\firefox.exe" -chrome chrome://browser/content/places/places.xul"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

DB Browser for SQLite . . . http://sqlitebrowser.org/

https://www.sqlite.org/lang_delete.html

https://wiki.mozilla.org/Places/Places_SQL_queries_best_practices

Would this SQLite3 query safely / properly delete all of the bookmarks in the "Other Bookmarks" folder? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - DELETE FROM moz_bookmarks WHERE parent = 5 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - This is what I think, using "DB Browser for SQLite" to look at: "C:\Program Files (x86)\Mozilla Firefox\firefox.exe" -chrome chrome://browser/content/places/places.xul" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - DB Browser for SQLite . . . http://sqlitebrowser.org/ https://www.sqlite.org/lang_delete.html https://wiki.mozilla.org/Places/Places_SQL_queries_best_practices

All Replies (16)

more options

You shouldn't use WHERE parent = 5 because the actual ID isn't fixed. Note that your code leaves the (history) item in moz_places with the URL.

Here is a SELECT for unsorted bookmarks:

SELECT b.id AS "Bookmark ID", b.title AS "Title", h.url AS "Url", p.title AS "Folder"
FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h
WHERE (b.fk = h.id) AND (p.title LIKE 'Unsorted Bookmarks')
more options

It didn't do anything. Please see attached screenshot.

I entered it into the SQLite CLI, and it leaves it like it's waiting for something.

Also, shouldn't it say "Other Bookmarks" instead of "Unsorted Bookmarks"?

more options

Sorry it should indeed be 'Other Bookmarks'. I tested it with SQLite Manager in an older Firefox version and forgot to switch the profile directory.

This should work better. You can alternatively search for guid LIKE 'unfiled_____'

SELECT b.id AS "Bookmark ID", b.title AS "Title", h.url AS "Url", p.title AS "Folder"
FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h
WHERE (b.fk = h.id) AND (p.guid LIKE 'unfiled_____')
ORDER By "Title" ASC

I don't know how to recurse sub folders. That would require WITH RECURSIVE

more options

No problem, it's kind of a complex question.

The above didn't work either

. . . Shouldn't it start out with "DELETE" ?

There should be no problem, as long as I'm just deleting what's in the "Other Bookmarks" folder, LOL. I do this after an Excel macro appends my bookmarks at the bottom of another Worksheet.

I'm putting all of this together for an Excel .xlsm . . . I'll post a copy for download, with instructions, once I have it all together.

Modified by sludge7051-x

more options

SQLite Manager no longer works with FF57, which is why I'm asking about this now . . . I figured out to just SQLite3 . . . Please see page 2 on this PDF for the links:

bookmarks-to-CSV.pdf . . . [A pdf I have made, and uploaded to my Google Docs] . . . This first page provides all you need to know, to get your Bookmarks into a .CSV file (This won't do anything to your actual Bookmarks, it just extracts a copy of them.) . . . Then, the next few pages provide the details of what's going on . . . https://drive.google.com/open?id=1xYWPQtijqCzk-1nzTsTb0ZUVKYJNFokR

more options

Wait. It works.

I thought the syntax could be wrong, so I changed it to like the other query: 1.) Get rid of the quotes 2.) No more than one word for an alias 3.) See if it outputs "Other Bookmarks" to a CSV . . . but it didn't do anything

So I took a look at the .BAT file that worked, and compared the two. I tried different names for the input file. I thought maybe your query did work "as is". Conclusion: There must have been some hidden character, so my input file was not seen.

The following query works just as you had it, to output my "Other Bookmarks" to a CSV:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

.open C:/Users/MyUserName/AppData/Roaming/Mozilla/Firefox/Profiles/u55555dh.default/places.sqlite

.mode csv

.once C:/Users/MyUserName/Desktop/output.csv

SELECT b.id AS "Bookmark ID", b.title AS "Title", h.url AS "Url", p.title AS "Folder" FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h WHERE (b.fk = h.id) AND (p.guid LIKE 'unfiled_____') ORDER By "Title" ASC

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

This is a good test - See what your query is selecting, first, by sending it to a CSV

But now, how do I make it into a "Delete" query?

If I get rid of lines 2 and 3: .mode csv .once C:/Users/MyUserName/Desktop/output.csv

. . . and change the word SELECT to DELETE . . . that doesn't do it

Also, would this query select something to delete, other than what's in the "Other Bookmarks" folder? It seems like it, since that folder alone is not specified.

more options

I'm pretty sure that SELECT is OK, but DELETE doesn't work like that with JOINS and ORDER BY in SQLite.

You could try sth like this:

delete from moz_bookmarks where id = (SELECT b.id FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h WHERE (b.fk = h.id) AND (p.guid LIKE 'unfiled_____'))

more options

I used the ".open . . " as seen above, and this delete query . . . it didn't do anything.

I currently find these queries hard to follow / figure out what they're doing . . . does it work to delete everything in your "Other Bookmarks" folder?

I'm using the latest version of FF 57.0.4

more options

It didn't show the error code?

Sorry, try delete from moz_bookmarks where id IN ().

more options

. . . not sure where that goes . . . can u give me the whole query again? Much much thanks!

more options

delete from moz_bookmarks where id IN (SELECT b.id FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h WHERE (b.fk = h.id) AND (p.guid LIKE 'unfiled_____'))

more options

It works! Oooooh - this is so cool!

I notice they make some of these words capitalized. I changed them on what you have:

DELETE FROM moz_bookmarks WHERE id IN (SELECT b.id FROM (moz_bookmarks AS b INNER JOIN moz_bookmarks AS p ON p.id = b.parent), moz_places AS h WHERE (b.fk = h.id) AND (p.guid LIKE 'unfiled_____'))

What's the purpose of that? Just so they stand out when coding it?

I have noticed a bug. After I run the query, go to the FF browser, and click the word "Bookmarks" up by the Title Bar, and look in "Other Bookmarks". This does not reflect the deleted bookmarks. You have to close and re-open FF to see the change.

If you do Ctrl + Shift + B, however, they are gone from there, that section is updated "real-time."

more options

A.) Is the un-updated "Other Bookmarks" something that should be addressed in the query? But, "Other Bookmarks" is refreshed after I close and re-open FF - so maybe it's an issue with the browser?

However, FF now crashes on me frequently. It's never done that before. Please see screenshot.

B.) If I delete the "Other Bookmarks" manually, it updates them there. This is how I have been previously doing this, and never had any problems.

I'll export my bookmarks, which are in the Bookmarks Toolbar, de-install / re-install a new copy of FF, and quit using the query for now. That should fix it.

more options

You can try to open a new window to see if that reflect the changes in the bookmarks menu. It is also not a good idea to let other software modify places.sqlite while Firefox is running if that is what you do.

The purpose of capitalizing SQLite keywords is to make them easily noticeable when you look at the query

You can check for problems with the places.sqlite database (bookmarks and history) in the Firefox profile folder.

  • use "Verify Integrity" on the "Help -> Troubleshooting Information" (about:support) page

See also:

more options

I think NoScript crashed it, because, I noticed that that window popped up before it happened. One of the crash reports lists it at the top under the "Correlations" tab. I tested it:

I de-installed NoScript / Re-opened FF

I ran the "Other Bookmarks" delete query with FF open. It left some bookmarks there, as described above - and hasn't crashed, after a day, I didn't refresh FF, I kept it in sleep mode.

It looks like you can run the "Other Bookmarks" query with FF open, with no problem - it just doesn't refresh it under "Bookmarks" in the Title Bar.

I'd say the reason they're not seen in the Library "skin," is because that's a "refreshed" version of the database - It's the same thing if you open another copy of FF, they're not there, because that's a refreshed view of the database.

Modified by sludge7051-x

more options

. . . check for problems with the places.sqlite database (bookmarks and history) in the Firefox profile folder:

about:support or Help / Troubleshooting Information / "Verify Integrity"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

I clicked to "Verify Integrity" - Do you just look at these first 12? It looks ok:

Places Database / Verify Integrity

> Task: checkIntegrity + The database is sane > Task: checkCoherence + The database is coherent > Task: expire + Database cleaned up > Task: vacuum + Initial database size is 10240 KiB + The database has been vacuumed + Final database size is 10240 KiB > Task: stats + Database size is 10240 KiB

It ended up that I did not de-install/re-install FF to try and fix it. The browser was still working fine. I determined that if it crashed on me a couple more times, then I'd give that a try, but that hasn't happened.