How to add FTS5?

[dunnl]

I was hoping I could use FTS5 by blindly adding -DSQLITE_ENABLE_FTS5 to the cabal file, since it looks like the FTS5 code is included in sqlite3.c. The library builds and passes cabal test, but it doesn't actually work. Here's what my ghci session looks like (I'm using sqlite-simple, but I don't see why that would make a difference. My sqlite-simple is built against the modified direct-sqlite.)

:set -XOverloadedStrings
import Database.SQLite.Simple
conn <- open "test.sqlite3"
execute_ conn "CREATE VIRTUAL TABLE fts5test USING fts5(data)"
execute_ conn "INSERT INTO fts5test values (\"This is a test\")"
query_ conn "PRAGMA compile_options;" :: IO [(Only String)]

Output

[Only {fromOnly = "COMPILER=gcc-7.2.0"},Only {fromOnly = "ENABLE_FTS3"},Only {fromOnly = "ENABLE_FTS3_PARENTHESIS"},Only {fromOnly = "ENABLE_FTS4"},Only {fromOnly = "ENABLE_FTS5"},Only {fromOnly = "SYSTEM_MALLOC"},Only {fromOnly = "THREADSAFE=1"}]

So far so good, it looks like FTS5 is enabled. But then:

query_ conn "SELECT * FROM fts5test WHERE data MATCH \'test\'" :: IO [Only String]

gives me

*** Exception: Sqlite3 return ErrorError while attempting to perform step: unable to use function MATCH in the request context

[sic]. However, the same query works fine in my system's sqlite3 executable. Accordingly, when I repeat all of the above steps with cabal configure -f systemlib, the last query works just fine.

[dunnl]

I would like to add: When I changed the preceding execute_ <blah>s to query_ <blah> :: IO [Only String] (in case sqlite-simple was returning an informational message), I simply got [] back. Also, my own sqlite3 executable DOES see the virtual tables created using direct-sqlite when built with -DSQLITE_ENABLE_FTS5. It seems that only the match part isn't working.

[nurpax]

Yep, sqlite-simple shouldn't matter here.

My guess would be that the -D feature enable somehow doesn't go through or that the sqlite-simple build (or your app build) somehow picks up an unmodified direct-sqlite build. Are you using stack? If you are, are you starting ghci with stack ghci?

Do you see any disadvantages to enabling FTS5 always? I could make a release that unconditionally turns it on in the release cabal file.

[dunnl]

I've built a little repo for testing this here to play around with different stack.yaml options so you can see my configuration. With that repo I've reproduced this problem on OS X and Linux.

When I get the time I'm going to keep looking into this, maybe without sqlite-simple or even the C API against the raw sqlite.c file.

[dunnl]

I followed the instructions here to build a little C program that runs individual queries. I've confirmed that the behavior is the same, (everything seems to work but MATCH), so I believe this is a problem with the direct-sqlite amalgamation. Still doing some tests to see if it's just a matter of updating the file.

Update: I ran my test against this fork that has an updated (3.20) amalgamation and -DSQLITE_ENABLE_FTS5 in the cabal file. The test works (I can insert and retrieve data from FTS5 virtual tables with MATCH). Any thoughts on putting together a new release with 3.20 and FTS5 enabled by default? (The cabal test passes). Is the stat64 patch still necessary?

[moll]

I believe the latest version of Direct SQLite in Hackage already comes with FTS5 enabled. Worked when I tried it.