Firefox, SQLite and Places structure
effective immediately, we have moved to our dedicated servers in http://www.brizoma.com/
Please update your bookmarks, since we will not update this site anymore.
See you in BRIZOMA.COM
This article is the first part of a series about firefox, NoSQL, bookmarks, synchronisation and the cloud. The second part can be found here.
Some of my readers know that I prefer Linux environments for my own PCs, but I use a Mac with OS X and a Windows XP virtual machine at work; I need cross compatibility and “places” portability. Starting with version 3.0, Firefox no longer uses the old bookmarks.html file to store passwords or history.dat for your visited pages. It uses an SQLite data base named places.sqlite in your Mozilla profile folder instead. The file “places.sqlite” stores the annotations, bookmarks, favorite icons, input history, keywords, and browsing history (a record of visited pages).
Some hints about SQLite
SQLite is an ACID-compliant embedded relational database management system contained in a relatively small (~275 kiB) C programming library. The source code for SQLite is in the public domain and implements most of the SQL standard. In contrast to other databases, SQLite is not a separate process that is accessed from the client application, but an integral part of it.
Firefox and Thunderbird store a variety of configuration data (bookmarks, cookies, contacts, etc.) in internally managed SQLite databases. Skype also uses SQLite. Adobe uses SQLite as its file format in Adobe Photoshop Lightroom, Adobe AIR, and internally within Adobe Reader. Ruby on Rails’ default database is also SQLite. The Service Management Facility, used for service management within the Solaris and OpenSolaris operating systems, uses SQLite internally.
Due to its small size, SQLite is well suited to embedded systems, and is also included in Apple’s iOS (where it is used for the SMS/MMS, Calendar, Call history and Contacts storage), Symbian OS, Nokia’s Maemo, Google’s Android, RIM’s BlackBerry, Linux Foundation’s MeeGo and Palm’s webOS. However, it is also suitable for desktop operating systems; Apple adopted it as an option in Mac OS X’s Core Data API from the original implementation in Mac OS X 10.4 onwards, and also for administration of videos and songs on the iPhone.
Back to Firefox
You can edit the places.sqlite file using wonderful tools such as SQLiteMan, that’s available for Linux, MacOS and Windows. You may also like an extension to edit your local places data base. Download and install SQLiteManager add-on for Firefox > 3.6.4. Notice that this extension will also allow for managing any SQLite database on your computer, not only Firefox’. I personally prefer the standalone application, since I don’t like to burden my browser with extensions I don’t use on a daily basis. Notice that Firefox opens the places.sqlite with a lock that prevents the user from updating the file when Firefox is open.
You can interact with your local SQLite DB using standard SQL-formatted statements, such as:
sqlite3 places.sqlite “select * from moz_places where url like ‘%google%’;”
The places.sqlite file includes a number of tables as follows:
- moz_anno_attributes: Contains the names of all the annotations in the system and a name ID. There should be relatively few unique names.
- moz_annos: Contains the values of page annotations. It maps a page (reference to moz_places) and an annotation name (reference to moz_anno_attributes) to the value of the annotation.
- moz_bookmarks: This table contains bookmarks, folders, separators and tags, and defines the hierarchy. The hierarchy is defined via the parent column, which points to the moz_bookmarks record which is the parent. The position column numbers each of the peers beneath a given parent starting with 0 and incrementing higher with each addition. The fk column provides the id number of the corresponding record in moz_places.
- moz_bookmarks_roots: Lists special folders that are the root folders of certain content types in the bookmarks system. The records in this table are mapped to records in the moz_bookmarks table. Bookmarks, folders and separators are descendants of the bookmarks root, while tags and tagged URIs are descendants of the tag root, e.g. places, menu, toolbar, tags, unfiled.
- moz_favicons: This contains a list of unique favicon URIs and data. One or more pages in moz_places refer to each entry. When no pages reference a favicon, the icon entry will be deleted.
- moz_historyvisits: One entry in this table is created each time you visit a page. It contains the date, referrer, and other information specific to that visit. It contains a reference to the moz_places table which contains the URL and other global statistics.
- moz_inputhistory: A history of URLS typed by the user
- moz_items_annos: Contains the values of bookmark item annotations. It maps a bookmark, folder or separator (reference to moz_bookmarks) and an annotation name (reference to moz_anno_attributes) to the value of the annotation.
- moz_keywords: This table is a unique list of keywords. The moz_bookmarks table has a keyword_id column that maps to a record in moz_keywords table.
- moz_places: This is the main table of URIs and is managed by the history service. Any time a Places component wants to reference a URL, whether visited or not, it refers to this table. Each entry has an optional reference to the moz_favicon table to identify the favicon of the page. No two entries may have the same value in the url column.
If you delete the file places.sqlite you will kill your browsing history, and a new, empty file will be created automatically. Therefore your browser should not be running. In Firefox 3, places.sqlite will be rebuilt using the latest dated JSON file from the bookmarkbackups folder (usually 1-day delay); if no JSON backup exists, bookmarks will be imported from the old bookmarks.html, if found, that is the old Firefox 2 format for bookmarks.
Other SQLite files used by Firefox are:
- content-prefs.sqlite: Individual settings for pages.
- cookies.sqlite: Holds all of your cookies, including login information, session data, and preferences.
- downloads.sqlite: stores information (such as file name, URL, and date of download) about files downloaded
- formhistory.sqlite: Saved form data
- key3.db: Key database
- permissions.sqlite: Holds preferences about which sites you allow or prohibit to set cookies, to display images, to open popup windows and to initiate extensions installation.
- search.sqlite: Information about your search plugins
- signons.sqlite: Encrypted saved passwords (and URL exceptions where “NEVER SAVE PASSWORD” is selected), requires key3.db to work.
- urlclassifier.sqlite / urlclassifier2.sqlite / urlclassifier3.sqlite: Phishing and Attack site protection data.
- Interesting: gooog... you suck pip.net/monopoly 4 years ago
- Magic keywords on Google and the consequences of tailoring results - Gabriel Weinberg's Blog gabrielweinberg.com/blog/2012/10/m… via @yegg 4 years ago
- Using an SSD under linux? Improve your system and live of your disk following these simple steps bit.ly/OZRGM1 4 years ago