Tuesday, March 9, 2010

Firefox - bookmarks -SQLite

Links: SQLite browser   SQLite backup

My parents noted some problems with Firefox bookmarks; crashes when attempting to organize bookmarks and so on. I experienced some weird effects from a large bookmark file on my system as well. I looked into how the bookmarks are stored.

We users typically access and manage our bookmarks via Firefox. However, unseen to users, Firefox relies upon SQLite, a flat-file (2-D) database application, to store and retrieve bookmark information on our hard drives. Many applications, like Firefox, require limited data storage and retrieval. A full 3-D database would be an extra installation process with additional problems. Accordingly, it's not uncommon to find SQLite built-in to many lighter applications; it's a free, open-source application that developers can easily adapt. What this means with Firefox is the bookmark data itself is kept in an SQLite file called places.sqlite. This file lives in the default, hidden Mozilla folder in the user's home directory, eg:


(Of course, "foo" is a stand-in for whatever one has actually named their user directory.) Although the data is in SQLite format, the bookmarks remain portable; Firefox allows users to export and import bookmarks as html files, instead of the less familiar SQLite database file format.

bookmark problems
If there are problems with bookmarks, it's not always clear whether the problem is in a single bookmark entry from a selected website, or whether the SQLite index which manages all the bookmarks has become corrupted. It's much easier to restore the database, so this is probably the best starting solution attempt.

database solution
As a first solution, it's easier to restore the database, which can be done in about 3 minutes. The steps, as I do them, are to, 1) export the bookmarks file, 2) delete the old bookmarks file (and related backups) in the mozilla folder, and then, 3) re-open Firefox and import the exported bookmarks. This will reestablish and repopulate the database, which should then be stable.

In greater detail, the steps are:

1) Open Firefox, go into Bookmarks -> Organize Bookmarks -> Import and Backup -> Export HTML . This exports the bookmarks in html format. I gave my exported file the name bookmark.html, but any name is fine.

2) Exit Firefox, open a terminal, and delete places.sqlite and other bookmark related files:
$ cd .mozilla/firefox/bQt4mrl3a.default
$ rm places.sqlite
$ rm bookmarks.html
$ cd bookmarkbackups
$ rm *

3) Re-open Firefox and import the previously exported bookmark.html file (or whatever you named it when exporting).

the database solution didn't work
The steps above seem to address potential database corruption possibilities, since a new database is created when Firefox is re-opened after deleting the bookmark files. If this does not solve the challenge, then the problem might be a corrupt bookmark entry. This would have been imported back into the database and so would persist. I have hundreds of bookmarks, which means that looking through them, bookmark by bookmark, attempting to locate a corrupted one, might take hours and hours of hunting. If the solution above doesn't repair my problem, and if I know it's not a hardware or OS problem, I would find it easier to simply delete all bookmarks and start over. However, if I were retired and had loads of free time, I might read on here...

entry by entry solution
First, it's worthwhile to make a copy of places.sqlite, and then examine the copy instead of the original. If one is able to locate the problem in the copy, one can repair it there without damaging the original. Copy it back when complete and tested.

Secondly, going into the places.sqlite file to examine it, bookmark by bookmark, can be done by Command Line commands or via a GUI. We're talking about masochism either way -- the ultimate masochism would be using the command line. Using command line sqlite commands is beyond the scope here, but there are many tutorials out there, such as this one at the SQLite site.

Thirdly, because command line appears less efficient in this case, I'd suggest using a GUI SQLite manager of some sort. This should make an inspection of the places.sqlite file easier than with the command line, at least for most people. The free, open-source SQLite Browser may be a good option.

SQLite Browser
I found this application easy to download, compile, and install. It took about 10 minutes all together. I downloaded the latest release from SQLite sourceforge project and then followed the standard steps. Two variations;

1) it uses qmake since it's Qt-based:
$ qmake -unix -recursive
$ make

2) it doesn't install, just compiles a binary. But we can copy the binary into any directory we wish and start if from there.

All of this, from download to finish, took perhaps 10 minutes. Again, once the binary has been created, I put it in a folder with some other apps, and started it from the command line:
$ ./sqlitebrowser

Fig. 1 SQLite Browser

With the program running, open the copy of the bookmarks file places.sqlite (Figure 1). The bookmarks file/database appears to have a fairly elaborate structure. Looking for corrupted characters would take too much time for my taste in this structure; it's easier for me to wipe-out my bookmarks and start over again. That said, SQLite Browser is a helpful application and would be very useful in a situation where one had a simple SQLite database, say, a personal database for birthdays or anniversaries.

direct backup of an SQLite database
We noted above that we could simply copy the database records (in Firefox's case places.sqlite), and use that for a backup. However, if a person wants to make a compressed snapshot which includes the entire directory structure, it's relatively easy from a terminal using command-line:

$ tar cvfz `date +%Y%m%d`_mtdb_bkup.sql.tar.gz DATABASE PATH

And then to restore:
$ tar xvfz ARCHIVE.tar.gz

Much more can obviously be said about these bookmark problems and about SQLite. It's good to understand that there is an additional layer beyond Firefox, under the hood. A couple of options were provided above and it's my hope we can all hang on to our bookmarks.

No comments: