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.

Double joins

I finally groked something I was probably told to do 5 years ago. Actually I have no memory of being told to do this trick, but I should have been. It's so sweet. It's allowed me to rewrite and rethink the database queries for my archiving app.

If you don't know that a table join is, you'll have to learn about that first. So go do that and come back.

OK, now what is so sweet about a double join? First off, I'll point out that I'm not joining 3 tables together, I'm joining 2 tables together TWICE.

Why would I do this? A common idiom is to have a main table that contains all the fields that every widget (documents, articles in your store, whatever) has and another table that contains one or more rows per-widget that contain fields that only that widget or only that class of widget has. The alternative is to have one table that contains a field for every possible field of every possible class of widget, which can be very wasteful. And then you have to MODIFY TABLE each time you want to add or remove a field. Or you could have a table per class of widget, which reduces waste, but increases complexity.

So, a hypothetical ecommerce schema would looks roughly like:

CREATE TABLE main (
    int SKU PRIMARY KEY,
    timestamp created,
    int price,
    int in_stock,
    int category
);

CREATE TABLE extra {
    int SKU PRIMARY KEY,
    varchar(20) field,
    varchar(255) value
};

(Yes, extra.field really should be an ENUM or an INT.)

Now you want to find all the items with the keyword 'tablet', but you want to sort on the french name of the product:

SELECT SKU,E1.value as keyword,ES.value AS sort_field FROM main 
    JOIN extra AS E1 ON main.SKU = extra.SKU AND field = 'keyword'
    JOIN extra AS E2 ON main.SKU = extra.SKU AND field = 'fr'
    WHERE E1.value = 'tablet'
    ORDER BY sort_field;

Look at that, it's like you've temporarily added 2 new fields to main, 'keyword' and 'title'. What's more, to sort in English, I just change 'fr' to 'en'. To sort by manufacturer, change it to 'manufacture.'

Seems to me this invalidates 75% of the reason for NoSQL. The other 25% is that joins like the one above aren't very efficient for huge (aka web-scale) databases, the kind with multimillion records. The answer to that is that RAM IS CHEAP and USE SSDs.