iPhone App With SQLITE FTS3 Xcode Release Build Not Working

My iPhone application uses SQLITE's FTS3 functions (specifically MATCH and OFFSET). These are essential to my optimized searching algorithm. I implemented FTS3 by including three SQLITE source files, namely sqlite3.c, sqlite3.h and sqlite3ext.h in my project, under a group named SQLite. I removed from the Frameworks group my previous reference to the libsqlite3.dylib library. I set the "Other C Flags" and "Other C++ Flags" project settings to -DSQLITE_ENABLE_FTS3=1. (I also tried simply setting those flags to -DSQLITE_ENABLE_FTS3.)

The application performs perfectly in the Simulator for b开发者_如何学Pythonoth debug and release builds. The application also performs perfectly on the iPhone, BUT only for the debug build!

The release build does not return result rows for any SQL calls using the MATCH and OFFSET keywords. My specific question is, "When I build the release version with my iPhone connected, is my FTS3 enabled version of SQLITE not being installing along with my app?" I see the sqlite.o object file in the appropriate Release-iphoneos subfolder on my Mac. It is a bit smaller than the one in the Debug-iphoneos subfolder, but I concluded this is due to a lack of debug symbols.

I am desperate for a solution, so any ideas at all will be greatly appreciated.

OK, for those who may benefit from my quirky experience, here was the solution. After much hair pulling I decided to relax some of the optimization in the Release version. I did this just to get another clue as to the problem - not as a solution. Sure enough, the code worked perfectly! Then, I said "OK, let me put the optimization back to smallest and fastest to continue my investigation." Magically, everything still worked!!! In summary, the only thing that I did was to change the optimization in the the Project settings, and then put it back! All of this, of course, was after a lot of debugging, recompiling after cleaning all targets, etc. NOTHING had made a difference until I changed and reset the optimization to the original value. Crazy, but that is my story and I'm sticking to it. Hope this helps someone else experiencing this problem.

Just a note that if you are only using fts3 you can just use the normal sqlite3 library that comes with the SDK. If you execute the following:

NSLog(@"Compile options specified when Apple built the library:");
if (sqlite3_prepare_v2(contentDatabase, "PRAGMA compile_options", -1, &statement, NULL) == SQLITE_OK)
{
    while (sqlite3_step(statement) == SQLITE_ROW)
    {
        NSLog(@"%s", sqlite3_column_text(statement, 0));
    }
}

It will tell you what compile options Apple used when compiling the default library. When I run this I see:

SQLite version = 3.7.2 [DATABASE] Compile options:

ENABLE_FTS3

ENABLE_FTS3_PARENTHESIS

ENABLE_LOCKING_STYLE=1

ENABLE_RTREE

OMIT_BUILTIN_TEST

OMIT_LOAD_EXTENSION

TEMP_STORE=1

THREADSAFE=2

This is compiling against iOS 4.3.5. I can confirm that fts3 virtual tables and the MATCH keyword are working in my build.

The first thing I would do is make a clean build of the iPhone release build and look in detail at the raw build log. You can look at the actual commands executed there to see if those extra compiler/preprocessor flags are actually configured to be used for that target.

If they are not then you probably made a mistake in the Target settings.

This answer is different from others in the following ways:

• Highlights the useful code supplied by @Robert Hawkey
• Shows full code and output for Xcode 9/Swift 4.0.3
• Notes that one can identify useful features of SQLite in iOS (eg. JSON SQL Functions)

tldr;

PRAGMA compile_options; (more on pragma)

Swift 4.0.3

func testSQLiteOptions(){
    var db: OpaquePointer? = nil
    var statement: OpaquePointer? = nil
    sqlite3_open(self.dbPath, &db)
    guard db != nil else {return}
    NSLog("Compile options specified when Apple built the library:");
    if (sqlite3_prepare_v2(db, "PRAGMA compile_options", -1, &statement, nil) == SQLITE_OK)
    {
      while (sqlite3_step(statement) == SQLITE_ROW)
      {
        NSLog("%s", sqlite3_column_text(statement, 0));
      }
    }
  }

Output

2017-12-09 06:34:04.233638-0800 jlmj[5997:2882220] Compile options specified when Apple built the library:
2017-12-09 06:34:04.236378-0800 jlmj[5997:2882220] BUG_COMPATIBLE_20160819
2017-12-09 06:34:04.236490-0800 jlmj[5997:2882220] COMPILER=clang-9.0.0
2017-12-09 06:34:04.236574-0800 jlmj[5997:2882220] DEFAULT_CACHE_SIZE=128
2017-12-09 06:34:04.236651-0800 jlmj[5997:2882220] DEFAULT_CKPTFULLFSYNC
2017-12-09 06:34:04.236727-0800 jlmj[5997:2882220] DEFAULT_JOURNAL_SIZE_LIMIT=32768
2017-12-09 06:34:04.236803-0800 jlmj[5997:2882220] DEFAULT_PAGE_SIZE=4096
2017-12-09 06:34:04.236968-0800 jlmj[5997:2882220] DEFAULT_SYNCHRONOUS=2
2017-12-09 06:34:04.237046-0800 jlmj[5997:2882220] DEFAULT_WAL_SYNCHRONOUS=1
2017-12-09 06:34:04.237121-0800 jlmj[5997:2882220] ENABLE_API_ARMOR
2017-12-09 06:34:04.237195-0800 jlmj[5997:2882220] ENABLE_COLUMN_METADATA
2017-12-09 06:34:04.237270-0800 jlmj[5997:2882220] ENABLE_DBSTAT_VTAB
2017-12-09 06:34:04.237345-0800 jlmj[5997:2882220] ENABLE_FTS3
2017-12-09 06:34:04.237421-0800 jlmj[5997:2882220] ENABLE_FTS3_PARENTHESIS
2017-12-09 06:34:04.237497-0800 jlmj[5997:2882220] ENABLE_FTS3_TOKENIZER
2017-12-09 06:34:04.237648-0800 jlmj[5997:2882220] ENABLE_FTS4
2017-12-09 06:34:04.237726-0800 jlmj[5997:2882220] ENABLE_FTS5
2017-12-09 06:34:04.237802-0800 jlmj[5997:2882220] ENABLE_JSON1
2017-12-09 06:34:04.237876-0800 jlmj[5997:2882220] ENABLE_LOCKING_STYLE=1
2017-12-09 06:34:04.237950-0800 jlmj[5997:2882220] ENABLE_PREUPDATE_HOOK
2017-12-09 06:34:04.238023-0800 jlmj[5997:2882220] ENABLE_RTREE
2017-12-09 06:34:04.238097-0800 jlmj[5997:2882220] ENABLE_SESSION
2017-12-09 06:34:04.238172-0800 jlmj[5997:2882220] ENABLE_SNAPSHOT
2017-12-09 06:34:04.238248-0800 jlmj[5997:2882220] ENABLE_SQLLOG
2017-12-09 06:34:04.238323-0800 jlmj[5997:2882220] ENABLE_UNKNOWN_SQL_FUNCTION
2017-12-09 06:34:04.238398-0800 jlmj[5997:2882220] ENABLE_UPDATE_DELETE_LIMIT
2017-12-09 06:34:04.238473-0800 jlmj[5997:2882220] HAS_CODEC_RESTRICTED
2017-12-09 06:34:04.238548-0800 jlmj[5997:2882220] HAVE_ISNAN
2017-12-09 06:34:04.238622-0800 jlmj[5997:2882220] MAX_LENGTH=2147483645
2017-12-09 06:34:04.238695-0800 jlmj[5997:2882220] MAX_MMAP_SIZE=20971520
2017-12-09 06:34:04.242805-0800 jlmj[5997:2882220] MAX_VARIABLE_NUMBER=500000
2017-12-09 06:34:04.242891-0800 jlmj[5997:2882220] OMIT_AUTORESET
2017-12-09 06:34:04.242967-0800 jlmj[5997:2882220] OMIT_LOAD_EXTENSION
2017-12-09 06:34:04.243045-0800 jlmj[5997:2882220] STMTJRNL_SPILL=131072
2017-12-09 06:34:04.243120-0800 jlmj[5997:2882220] SUBSTR_COMPATIBILITY
2017-12-09 06:34:04.243196-0800 jlmj[5997:2882220] THREADSAFE=2
2017-12-09 06:34:04.243509-0800 jlmj[5997:2882220] USE_URI

Reference

1. SQLite Compile-Time Options
2. JSON SQLite Functions
3. Pragma Compile Options