Firefox 3 uses sqlite to store bookmarks (and most other things)
ever wanted to search your bookmarks with
SELECT * FROM moz_bookmarks WHERE title LIKE "%ruby%";
now you can:
in your firefox profile directory (cd .mozilla/firefox/rduxjr2p.default/), type:
# open db file
$ sqlite3 places.sqlite
# show available tables
sqlite> .tables
# set output to sql insert statements
sqlite> .mode insert
# send output to file "firefox_bookmarks"
sqlite> .output firefox_bookmarks
sqlite> SELECT * FROM moz_bookmarks WHERE title LIKE "%ruby%";
# list other available commands & options
sqlite> .help
mike@crunschidual:~$ cd .mozilla/firefox/rduxjr2p.default/
mike@crunschidual:~/.mozilla/firefox/rduxjr2p.default$ sqlite3 places.sqlite
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .help
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices TABLE Show names of all indices on TABLE
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML
http://wiki.tcl.tk/2633
http://www.w3schools.com/SQL/default.asp
http://www.geocities.com/colinpriley/sql/sqlitepg01.htm
Install SQLite3 in Ubuntu
http://theplana.wordpress.com/2007/05/11/install-sqlite3-on-ubuntu/http://www.webdotdev.com/nvd/content/view/347/
UNION ALL
SELECT *
FROM moz_places
WHERE id NOT IN (SELECT id FROM moz_places_temp)
AND id NOT IN (SELECT place_id FROM moz_places_deleted)
And moz_historyvisits_view looks very similar:
SELECT *
FROM moz_historyvisits_temp
UNION ALL
SELECT *
FROM moz_historyvisits
WHERE id NOT IN (SELECT id FROM moz_historyvisits_temp)
AND id NOT IN (SELECT visit_id FROM moz_historyvisits_deleted)
Each of these views has to have a few triggers created on it to allow for insertions, dmoz_places, our table looks like this:
moz_places_deleted
fk place_id
And for moz_historyvisits, our table looks like this:
moz_historyvisits_deleted
fk visit_id
Now, I’ll take a look at our views. The views will only be used for insertions, deletions, and updates. We cannot use them for selection because the performance is quite bad. The SQLite folks are working on this, but it will take some time before we can see that in release builds. The moz_places_view looks like this:
SELECT *
FROM moz_places_tempeletions, and updates. The triggers for moz_places_view are a bit simpler, so we’ll start there. Insertions are actually the easiest to manage. All we have to do is add the data to the temp table, and we’ll be set:
CREATE TEMPORARY TRIGGER moz_places_view_insert_trigger
INSTEAD OF INSERT
ON moz_places_view
BEGIN
INSERT INTO moz_places_temp
VALUES (MAX((SELECT MAX(id) FROM moz_places_temp),
(SELECT MAX(id) FROM moz_places)) + 1,
NEW.url, NEW.title, NEW.user_title, NEW.rev_host,
NEW.visit_count, NEW.hidden, NEW.typed, NEW.favicon_id);
END
However, when we do a deletion, we have a bit more work to do. We have to cover the case where data was added to the temporary table, and the case where it only lives in the permanent table. This isn’t terribly hard though, it turns out. We just have to delete the record from the temporary table with the right id, and add that same id to moz_places_deleted:
CREATE TEMPORARY TRIGGER moz_places_view_delete_trigger
INSTEAD OF DELETE
ON moz_places_view
BEGIN
DELETE FROM moz_places_temp
WHERE id = OLD.id;
INSERT INTO moz_places_deleted
VALUES (OLD.id);
END
Updates are probably the most interesting. If the data isn’t already in the temporary table, we need to get it there first. However, we don’t want to overwrite any existing data in that table because it is more up-to-date than the data in the permanent one. Regardless of Once we get that data into the temporary table, we can update it:
CREATE TEMPORARY TRIGGER moz_places_view_update_trigger
INSTEAD OF UPDATE
ON moz_places_view
BEGIN
INSERT INTO moz_places_temp
SELECT *
FROM moz_places
WHERE id = OLD.id
AND id NOT IN (SELECT id FROM moz_places_temp);
UPDATE moz_places_temp
SET url = NEW.url,
title = NEW.title,
user_title = NEW.user_title,
rev_host = NEW.rev_host,
visit_count = NEW.visit_count,
hidden = NEW.hidden,
typed = NEW.typed,
favicon_id = NEW.favicon_id;
END
Like moz_places_view, moz_historyvisits_view’s insertions aren’t terrible. There is one additional thing we need to worry about though. Currently, places uses a trigger on insertions and deletions from moz_historyvisits that updates moz_places.visit_count. That would involve a write, however, so we don’t want to do that. Instead, we update moz_places_view.visit_count and let the view handle the details:
CREATE TEMPORARY TRIGGER moz_historyvisits_view_insert_trigger
INSTEAD OF INSERT
ON moz_historyvisits_view
BEGIN
INSERT INTO moz_historyvisits_temp
VALUES (MAX((SELECT MAX(id) FROM moz_historyvisits_temp),
(SELECT MAX(id) FROM moz_historyvisits)) + 1,
from_visit = NEW.from_visit,
place_id = NEW.place_id,
visit_date = NEW.visit_date,
visit_type = NEW.visit_type,
session = NEW.session);
UPDATE moz_places_view
SET visit_count = visit_count + 1
WHERE moz_places_view_id = NEW.place_id;
END
Deletions also have to worry about the trigger. Once again, we update moz_places_view.visit_count accordingly:
CREATE TEMPORARY TRIGGER moz_historyvisits_view_delete_trigger
INSTEAD OF DELETE
ON moz_historyvisits_view
BEGIN
DELETE FROM moz_historyvisits_temp
WHERE id = OLD.id;
INSERT INTO moz_historyvisits_deleted
VALUES (OLD.id);
UPDATE moz_places_view
SET visit_count = visit_count - 1
WHERE moz_places_view.id = OLD.place_id;
END
We only have one case where we update moz_historyvisits, and I’m not even sure if it’s valid yet. As a result, this trigger may not actually be needed. The update trigger looks nearly identical to the on used for moz_places:
CREATE TEMPORARY TRIGGER moz_historyvisits_view_update_trigger
INSTEAD OF UPDATE
ON moz_historyvisits_view
BEGIN
INSERT INTO moz_historyvisits_temp
SELECT *
FROM moz_historyvisits
WHERE id = OLD.id
AND id NOT IN (SELECT id FROM moz_historyvisits_temp);
UPDATE moz_historyvisits_temp
SET from_visit = NEW.from_visit,
place_id = NEW.place_id,
visit_date = NEW.visit_date,
visit_type = NEW.visit_type,
session = NEW.session;
END
?
Since version 3 of Firefox, the browser has moved over from using flat files for keeping track of browsing history (history.dat) and bookmarks (bookmarks.html) to using SQLite databases (places.sqlite). This change over was required because the old flat file formats were badly implemented, clunky, and not able to handle the new demands of the location bar and browser history. Using a SQL database was the perfect solution for the complexity brought in with the new location bar and its dynamic searching of previous URLS, as SQL is easy to implement, is mostly compatible against multiple SQL application implementations (removing dependency on a single product), and powerful for cross referencing lookups. As a matter of fact, most of the data Firefox keeps now is stored in SQLite databases.
SQLite was also a good choice for the SQL solution because it can be implemented minimally straight into a product without needing a large install and a lot of bloat. While I like SQLite for this purpose and its ease of implementation, it lacks a lot of base SQL functionality that would be nice, like TABLE JOINS inside of DELETE statements, among many other language abilities. I wouldn’t suggest using it for large database driven products that require high optimization, which I believe it can’t handle. It’s meant as a simpler SQL implementation.
Anyways, I was very happy to see that when you delete URLs from the history in the newest version of Firefox that it actually deletes them out of the database as opposed to just hiding them, like it used to. The history manager actual seems to do its job quite well now, but I noticed one big problem. After attempting to delete all the URLs from a specific site out of the Firefox history manager, I noticed there were still some entries from that site in the SQLite database, which is a privacy problem.
After some digging, I realized that there are “hidden” entries inside of the history manager. A hidden entry is created when a URL is loaded in a frame or IFrame that you do not directly navigate too. These entries cannot be viewed through the history manager, and because of this, cannot be easily deleted outside of the history database without wiping the whole history.
At this point, I decided to go ahead and look at all the table structures for the history manager and figure out how they interact. Hidden entries are marked in places.sqlite::moz_places.history with the value “1”. According to a Firefox wiki “A hidden URL is one that the user did not specifically navigate to. These are commonly embedded pages, i-frames, RSS bookmarks and javascript calls.” So after figuring all of this out, I came up with some SQL commands to delete all hidden entries, which don’t really do anything anyways inside the database. Do note that Firefox has to be closed to work on the database so it is not locked.
sqlite3 places.sqlite
DELETE FROM moz_annos WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
DELETE FROM moz_inputhistory WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
DELETE FROM moz_historyvisits WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
.exit
This could all be done in 1 SQL statement in MySQL, but again, SQLite is not as robust :-\. There is also a “Favorite’s Icon” table in the database that might keep an icon stored as long as a hidden entry for the domain still exists, but I didn’t really look into it.
mike:$ sqlite3 places.sqlite
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> DELETE FROM moz_annos WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
sqlite> DELETE FROM moz_inputhistory WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
sqlite> DELETE FROM moz_historyvisits WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
sqlite> DELETE FROM moz_places WHERE hidden=1;
sqlite> .exit
