Sometimes you need to remotely get your bookmarks, history or check the top-10 sites, etc. Here’s some neat tricks to get the info from the Firefox Places file called places.sqlite, which is an SQLite database with the following tables (see also the picture):
$ sqlite3 places.sqlite
SQLite version 3.6.17
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>.tables
moz_anno_attributes moz_favicons moz_keywords
moz_annos moz_historyvisits moz_places
moz_bookmarks moz_inputhistory
moz_bookmarks_roots moz_items_annos
SQLite version 3.6.17
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>.tables
moz_anno_attributes moz_favicons moz_keywords
moz_annos moz_historyvisits moz_places
moz_bookmarks moz_inputhistory
moz_bookmarks_roots moz_items_annos
To get all the saved history in history.out:
sqlite> .output history.out
sqlite> SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url
…> FROM moz_places, moz_historyvisits
…> WHERE moz_places.id = moz_historyvisits.place_id
…> ;
sqlite> .quit
sqlite> SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url
…> FROM moz_places, moz_historyvisits
…> WHERE moz_places.id = moz_historyvisits.place_id
…> ;
sqlite> .quit
Or using one line:
$ sqlite3 places.sqlite “SELECT datetime(moz_historyvisits.visit_date/1000000,’unixepoch’), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id”
Top-20 URL’s:
$ sqlite3 places.sqlite “SELECT moz_places.visit_count, moz_places.url FROM moz_places ORDER by visit_count DESC LIMIT 20;”
All URL’s (and dates) containing the word “solaris”:
$ sqlite3 places.sqlite ‘SELECT datetime(moz_historyvisits.visit_date/1000000,”unixepoch”), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id AND moz_places.url LIKE “%solaris%” ;’
All searches:
$ sqlite3 places.sqlite ‘SELECT datetime(moz_historyvisits.visit_date/1000000,”unixepoch”), moz_places.url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id AND (url LIKE “%search?q=%” OR url LIKE “%search?p=%” OR url LIKE “%results.aspx?q=%” OR url LIKE “%web?q=%”) ORDER by visit_date;’
Sources:
One thought on “Firefox and places.sqlite tricks”