FT3/FTS5 issues

[hdlj]

Hello groue,

Thanks for GRDB!
I am trying to use it with a custom sqlite version (including the FTS5 module).
With the GRDB target, everything works well.
However when I use the GRDBCustomSQLite target, this piece of code throws a SQLite 14 error:
let db = try! DatabaseQueue(path: file)

I have followed the steps you recommend without success, do you have any idea of what I've missed?

I was using the GRDB 0.67.0 before and I was able to do a FTS query with the fts3 module. However, when I execute the same code on the version 0.74.0, It crashes because of the following precondition:
GRDBPrecondition(observer.databaseEventKinds.isEmpty, "Invalid statement type for query \(String(reflecting: sql)): use UpdateStatement instead.")

Actually during a FTS query, the observer contains an Update Event which makes the query crash.

What do you think is the workaround here?

Thanks in advance for your answers!

hdlj

[groue]

Hello @hdlj,

GRDBCustomSQLite [...] SQLite 14 error

I'm not familiar with the SQLITE_CANTOPEN error. What specific option did you use in your custom SQLite build?

Invalid statement type for query \(String(reflecting: sql)): use UpdateStatement instead.

I have a slight idea about the problem, but I need your help: please provide some code that can run in a playground.

[groue]

Second answer

GRDBCustomSQLite [...] SQLite 14 error

Lacking context, I'll assume you are getting your SQLITE_CANTOPEN error when you open, with a FTS5-enabled custom build, a database created with a regular FTS3-enabed SQLite.

The very first question you have to ask yourself is whether FTS5 is compatible with full text indexes created by FTS3. If not, follow the recommended practice for migrating - it may involve dumping the content of the old database in a new one, I don't know.

Invalid statement type for query (String(reflecting: sql)): use UpdateStatement instead.

I may have been hard asking for a playground. I just need some code I can paste in a project, make it run, and see the error - I'll answer with a code change recommendation, or a change in GRDB so that it better supports your need.

[hdlj]

Hello @groue,

Thanks for your answers!

I have tested the custom sqlite with a new database and with different simulators and the error still happens.
Here is my configuration:

• CUSTOM_SQLLIBRARY_CFLAGS = -DSQLITE_ENABLE_FTS5
• CUSTOM_OTHER_SWIFT_FLAGS = -D SQLITE_ENABLE_FTS5

I try it also with the provided example (SQLITE_ENABLE_JSON1). I will ask this question on the SQLiteLib project also. I will keep you update if I find a solution.

Invalid statement type for query \(String(reflecting: sql)): use UpdateStatement instead.

Here is the setup code using a DatabasePool :

        db.write { db in
            try! db.execute("CREATE VIRTUAL TABLE search  USING fts3(title, tokenize = unicode61)")
            try! db.execute("INSERT INTO search(title) VALUES(?)", arguments: ["john 1"])
            try! db.execute("INSERT INTO search(title) VALUES(?)", arguments: ["john 2"])
            try! db.execute("INSERT INTO search(title) VALUES(?)", arguments: ["john 3"])
            try! db.execute("INSERT INTO search(title) VALUES(?)", arguments: ["john 4"])
            try! db.execute("INSERT INTO search(title) VALUES(?)", arguments: ["john 5"])
        }

then the following code issue the error:

let rows = db.read { db in
            return Row.fetchAll(db, "SELECT * FROM search")
        }

The following precondition:

        GRDBPrecondition(observer.databaseEventKinds.isEmpty, "Invalid statement type for query \(String(reflecting: sql)): use UpdateStatement instead.")

contains this database event kind:

 .Update(tableName: "sqlite_master", columnNames: ["tbl_name", ""type", "rootpage", "sql", "name"])

Thanks !

[groue]

Invalid statement type for query (String(reflecting: sql)): use UpdateStatement instead

OK SQLite announces that the statement SELECT * FROM search performs a database change. It does not, of course.

I'll try to have GRDB work around this SQLite oddity. Stay tuned.

[groue]

@hdlj: the crash is fixed in v0.75.1.

[groue]

About your custom FTS5 build and SQLITE_CANTOPEN: I recommend you keep on investigating. I have no particular useful experience on this topic, and there's no reason you can't have SQLite open your database.

For the record, sqlite3_open_v2, which you cite in swiftlyfalling/SQLiteLib#7, is the very first function called by GRDB upon database creation, with the following options: SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE. I hope this information can be useful to you.

[hdlj]

Thanks a lot for the fix!
I will keep you update if I find something about the custom sqlite issue.

[hdlj]

Hello groue,

For the custom sqlite issue. If found something. I explain it in this thread swiftlyfalling/SQLiteLib#7
It was related to the path format. It is strange that the normal sqlite and the customised one don't have the same behaviour given a path database.