Full Text Search: Roll Your Own Sqlite for iPhone and iPadBy Paul on June 16th, 2010 in Technobabble
Tags: sqlite, xcode
The built-in Sqlite library that comes with iOS (iPhone OS) does not include one of the features most requested by developers, Full Text Search! Working this out based on information currently available in the wild is not without difficulty.
As iPhone Developers, we like getting the drop on our competitors with cool features like FTS that are hard to get going. However, we frequently benefit from many others who share.
In the interests of paying it forward, here is how you get the FTS3 full text search module compiling for Sqlite on your iPhone, iPod Touch or iPad app.
1. How To Install Your Own Sqlite Fulltext in Xcode Project
1. Sqlite provides a combined source file for easy compilation into other projects. Download this â€œamalgamationâ€ file fromÂ http://www.sqlite.org/download.html
2. Open Xcode and follow these steps:
- Xcode menu â†’ Project â†’ New target â†’ Static library â†’ Target name: Sqlite
- Add your SqLite amalgamation source into the project, remember to select the Sqlite target in previous step
- Xcode menu â†’ Project â†’ Edit active target â†’ Tab General â†’ Direct Dependencies â†’ Add: SqLite
- Xcode menu â†’ Project â†’ Edit active target â†’ Tab General â†’ Linked Libraries â†’ removeÂ FRAMEWORK version of SqLite
- Xcode menu â†’ project â†’ edit active target â†’ Tab General â†’ Linked Libraries â†’ addÂ YOUR SqLite
3. Add the following directive to theÂ sqlite3.c file to enable Full Text Search.
4. You should now be able to build normally. Because youâ€™ve replaced theÂ iOSÂ framework version of Sqlite linked libraries withÂ your version, your app will use this version without any further changes. Nice!
2. How to Use FTS in SQLite?
1. Create a full text search table using theÂ VIRTUAL TABLE command. We do this on the desktop and move the .db file to the phone. Processing a large database on the device would be too slow. See the section below on installing the amalgamated SQLite source.
CREATE VIRTUAL TABLE emails using FTS3(id, to, from, subject, body, headers);
2. Use theÂ MATCH operator to query against theÂ FTS table. Only oneÂ MATCH per query is allowed!
SELECT * FROM emails WHERE body MATCH 'egg';
3. You may want to concatenate multiple columns into one big text field like this, and use an inner join to get the fields from the original table.
CREATE VIRTUAL TABLE emails_fts using FTS3(email_id, all_text); SELECT emails.* FROM emails INNER JOIN emails_fts ON emails.id = emails_fts.email_id WHERE emails_fts.all_text MATCH 'eggtastic' AND emails.from = 'sjobsï¼ apple.com' LIMIT 10
Unfortunately Sqlite FTS only supports European languages, and is not capable of ngram based searching (no CJK support!)
3. Installing Sqlite FTS on OS X (for developing DB files)
Of course you will need a local version of Sqlite built withÂ FTS to prepare your iPhone app database files. The default install on OS X does not contain the FTS3 extension. Hereâ€™s how to install it:
1. Download a copy of the amalgamated SQLite source codeÂ WITH configure and makefile scripts. This is usually the tar.gz file and not the zip file! For example,Â http://www.sqlite.org/sqlite-amalgamation-3.6.21.tar.gz is the right one.
2. Untar the files and from the command line execute:
CFLAGS="-DSQLITE_ENABLE_FTS3=1" ./configure make install
3. (OPTIONAL) You can run this version of SQLite directly from the source direcory, or you can point to it globally using:
FTS in the Wild
With 150k plus records, we have found FTS3 to be very fast, even on the humble iPhone 3G (read: very, very humble!) We typically get 200+ records back in under 2 seconds. However , each record is only a few lines long.
Remember, there are limits to how heavy your record can be. Make sure you implement a stop word filter (removing common words). Also, reindexing on the phone is not great, so you may want to avoid this if you can.
That’s it, you can install Sqlite FTS3 in Xcode, you can perform your own queries and you can install a local copy on your development machine. You’re ready to go!
ADDENDUM: (DEC 3, 2010) Some people have been reporting issues building for iPhone4 or iPad (ARMV7) processor architecture. David reported that you can overcome this â€œdatabase malformedâ€ error by setting the SQLite Target to compile with LLVM GCC 4.2 and not LLVM 1.6. Also, disable compiler optimization.