sqlite3 vs firefox

Firefox keeps lots of useful in sqlite files in the user's profile. Under Linux, you will find these files in ~/.mozilla/firefox/PROFILE_DIR/. To find PROFILE_DIR, you pull look in ~/.mozilla/firefox/profiles.ini. Of interest to me is places.sqlite, which contains info on all sites visited. To pull a list out, simply do

 sqlite3 -csv ~/.mozilla/firefox/ucjmuboi.default/places.sqlite \
    'SELECT url,title,visit_count,visit_date/1000000 FROM moz_historyvisits JOIN moz_places ON place_id = moz_places.id'

The visit_date is bizarely in microseconds, so divide by 1,000,000 to get epoch seconds.

Of course, the above doesn't work on CentOS 5 nor 6. You wil get a Error: file is encrypted or is not a database error. CentOS 5 ships with sqlite 3.3.6, CentOS 6 ships with 3.6.20. Firefox uses 3.7 and creates a file that isn't backward compatible. Here is how to install a compatible version:

cd ~/work
wget http://www.sqlite.org/2016/sqlite-autoconf-3130000.tar.gz
tar zxvf sqlite-autoconf-3130000.tar.gz
cd sqlite-autoconf-3130000
./configure --prefix=/opt/sqlite-3130000
make all
sudo make install
sudo ln -s sqlite-3130000 /opt/sqlite
sudo bash -c "echo /opt/sqlite/lib > /etc/ld.so.conf.d/sqlite.conf"
sudo ldconfig

This will install 3.13.0. Make sure to check the download page for the latest version.

It should be pointed out that by putting /opt/sqlite/lib into ld.so.conf.d, we are overriding the .so default system .so. I don't know if this will break anything. I do know that it means that DBD::SQLite and /usr/bin/sqlite3 now use the new .so and this is what I want.