Fulltext search in SQLite and Django app
An example of using SQLite full-text search FTS3 module
Since version about 3.5.9 SQLite contains full-text search module called FTS3 (older releases may have FTS1, FTS2). Using this module we can easily add fast full text search to a Django application (or any other using SQLite). During my tests I got FTS3 only on Python 2.6. On Python 2.5 and using pysqlite may be hard to get FTS3 (try recompiling with -DSQLITE_ENABLE_FTS3=1 flag).
Creating virtual table
Here is an example table:
FTS1 and FTS2 can be used exactly in the same way as FTS3. More on sqlite.org.
Adding data to a virtual table
We can start with importing data from existing table. With this script (executed from Django project folder) we can import the data:
Indexing new entries we can handle in Django with signals. For example in models.py add:
Full-text in SQLite
The query looks like this:
I'm testing this search solution on my JobMaster - job offer searcher hosted on megiteam.pl. As for now no problems occurred with this (except switch from Python 2.5 to Python 2.6). It doesn't leak memory (Nginx+FastCGI), and it doesn't seem to be slow (it index entries faster than Whoosh, but no number on it). It's simple, easy to setup, and no whoosh. solr, xapian needed, so it's cool way to add full text search to SQLite powered websites.