SQLite3, Full Text Search with FTS3, and iOS 4.0

Our iOS apps Med Mnemonics, Med Abbreviations, and ICD9 Consult handle huge amounts of data – 1500+ memory tricks and aides, 13,000+ medical abbreviations, and 17,000+ medical diagnosis codes respectively – and provide instantaneous search.  The key to doing so is the FTS3 extension to SQLite3 which enables fast, indexed search.

While updating both apps to support the new features of iOS 4.0 by compiling against the iOS 4.0 SDK, I discovered that compiling a custom build of SQLite3 (in my case, to enable FTS3) led to massive breakage when running on iOS 3.2 or earlier.  I could continue to just use the iOS 3.2 SDK, but that would mean no fast task switching, among other problems. No good!

The solution is to use the systemwide sqlite3 library and load FTS3 as an extension. To do so:

  1. Download the sqlite-fts3-extension project I created, which includes the necessary files from the sqlite3 project.
  2. Include the project in your own.  Add it as a dependency for your own target (so it will be built automatically) and drag the sqlite-3.6.23.1-fts3-ext.a product to your Link Binary with Libraries phase.
  3. In the class file before you load the database, add:

#import "RRFTS3ExtensionLoader.h"

at the top and add:

[RRFTS3ExtensionLoader loadFTS3];

somewhere before the database is opened.

I hope that helps someone! This code, like sqlite3 itself, is released into the public domain. My thanks to Pascal Pfiffner for his help in working through this.


Update: Linked to version 2.0 of the sqlite-fts3-extension project, which includes a workaround for iOS < 3.2, in which an older version of sqlite3 is included in the system. Specifically, sqlite3_strnicmp() is not an external symbol in that older version, so the fts3 extension would fail to load. This new version of the project includes a copy of that function as compat_sqlite3_strnicmp() and makes use of it throughout the fts3 code in place of sqlite3_strnicmp().

Update 2: Updated to version 2.1, which disables the extension when loading on iOS 5.0 and later.  Loading on 5.0 and 5.1 is not necessary, but is not harmful; FTS3 is included in the OS's distribution but loading our own doesn't appear to have ill effects. On iOS 6, however, loading a separate FTS3 extension breaks SQLite entirely.