CR-SQLite extension and SQLite commit/rollback/change hooks

[michaelmedellin]

I'm running Custom GRDB with the CR-SQLite extension loaded successfully. One of the features of CR-SQLite is that it increments a database version on every transaction commit to the database. For some reason, this is not happening correctly with GRDB.

The only time the database version in crsqlite will increment is once the connection to the database is closed. A new connection will increase the db version and apply that same version to all transactions until the connection is closed again.

I've tried using both DatabaseQueue and DatabasePool without success. I did get the version to increment after manually checkpointing the database when using a DatabasePool connection. Checkpointing basically functioned identically to closing the connection as far as the db version behavior.

As a sanity check, I've tested CRSQLite extension with SQLite.swift using a similar set up in iOS and the version increments correct on each transaction (ex. an insertion to a table). This makes me think that the way GRDB handles connections somehow prevents the extension from realizing there are new transactions committed. Additionally, this also functions correctly when running the same extension from the SQLite CLI.

Any advice on how better to understand the details of GRDB's SQLite connections and any pointers to solutions would be greatly appreciated. I'm diving deep into the docs and haven't found a good lead quite yet. Thank you!

[michaelmedellin]

I think this could possibly be related to CRSQLite requiring exclusive use of the commit, update, and rollback hooks.

See here: vlcn-io/cr-sqlite#396 (comment)

[groue]

Re: disabling database observation in GRDB, is there a straight forward approach for doing so?

Observation is already disabled in read-only connections, so this should not be too hard. Check what happens when you comment out those lines. Probably CRSQLite will just kick in.

If this works, we could move on to a real pull request, which introduces a new Configuration.transactionObservationIsEnabled flag. We'd discuss about the relevant tests.

Does it sound like a plan to you?

[michaelmedellin]

@groue yep, commenting those lines worked. Adding this as a supported configuration sounds like a plan.

[groue]

Good news! 🥳

I won't ask you for a pull request, because I don't have a clear idea of my requirements, here ;)

Can you live with your fork for a while?

This discussion can of course go on with our ideas.

[michaelmedellin]

I can definitely live with my fork for now. Glad I'm able to still use GRDB.

An area I'm exploring is if I can live with just using update hooks for observability. It's not ideal, but may work in a pinch.

[groue]

I can definitely live with my fork for now. Glad I'm able to still use GRDB.

Cool 👍

An area I'm exploring is if I can live with just using update hooks for observability. It's not ideal, but may work in a pinch.

Without the commit and rollback hooks, you can not know if the changes notified to the update hook are committed, or rollbacked. You can rollback transactions if you want, and GRDB automatically rollbacks transactions that throw an unhandled error.

The savepoint stack further complexifies the landscape. To address this, GRDB buffers changes notified to the update hook until the savepoint stack becomes empty. For an illustration, see https://swiftpackageindex.com/groue/grdb.swift/v6.29.2/documentation/grdb/transactionobserver#Database-Changes-And-Transactions