Confessions of a cat

I did not know but it seems that the cool little sqlite3 mini-database engine had a full-text search application built into it. Unfortunately Apple (as usual) seems not too inclined on having it enabled (at least not in OSX Leopard 10.5.x).

What I read about it - it seems that it’s quite fast and usable for some purposes.

Some links first:

FTS usage in Sqlite3 in sqlite trac

Some performance and usage info on sqlite FTS3 from dotnetpearls

Enabling full text search on sqlite / Python 2.5 bundled with OSX (Leopard)

This last link gave me some ideas. I was wondering how I could make use of this small fulltext-search engine in django. I like django, and currently learning it, so that’s the reason behind.

Some links on enabling django to use sqlite full-text search:

fulltext search in sqlite and django using django’s signals

sqlite performance and django

I highly recommend the above links regarding django / python to anyone interested in those subjects as well. Very useful ideas can be found there thanks to Piotr. Appreciated.

So, back on to how to enable fts3 on OSX. Well, first of all, just grab the latest sqlite3 amalgamation tar.gz image from the sqlite download page. Unpack it, then compile it like this:

CFLAGS="-DSQLITE_ENABLE_FTS3=1" ./configure
make

Do not run ‘make install’ yet. After a successful compile you will find a ‘.libs’ directory in the source tree. cd into it, and you will see a ‘libsqlite3.x.x.x.dylib’ file, an other called ’sqlite3′, and some symlinks scattered around. Now, on OSX 10.5 we have python 2.5 and it has sqlite3 built in, which in essence loads the file located in ‘/usr/lib/libsqlite3.0.dylib’. I know it is not the best solution and later Apple updates will probably kill it, but just go ahead, rename the original file, and replace it with the freshly compiled one. (I have to let you know, it only compiles for one architecture, which on my machine was i386. Check with the ‘file’ command if you wish so).

Now you should do the same to the command line ‘/usr/bin/sqlite3′ executable. Replace it with your freshly compiled one. I have renamed the originals to [original_name].orig so that later - if needed, which I highly doubt - I can put them back.

Now if you go to your python interpreter by typing ‘python’ and do this:

import sqlite3
sqlite3.sqlite_version

It should print out the same version number which you donwloaded (3.6.17 in my case).

To test whether it really works, you can also try the command line sqlite3 client (just make sure the /usr/bin/sqlite3 one runs):

sqlite3
create virtual table t using fts3;

If everything runs OK - you’re good. Enjoy testing this nice baby

Of course I know that it’s probably much safer to install python with macports, then also install sqlite3 with it with the proper configure flags (if available). I did this with many of my stuff (I have haskell, clips, imagemagick, etc. installed via that) but I sometimes I like to make things easier on me / or more complicated it depends.

p.s.: If you have any further information on how to further optimize FTS3 tables, or how to use it better, please leave a comment. Thanks.