sqlite3_update_hook - which changes are consiered?

(1) By John 42 (jtheisen42) on 2022-10-14 02:32:48 [link] [source]

The documentation doesn't explicitly say which changes are considered for change listening. It could be

Only changes on the connection the event is registered on.
Only changes in the current process.
All changes, including those made to the file by other processes.

I'm asking this because on

https://github.com/dotnet/efcore/issues/13827

the user Ciantic claims it's 1) and that would surprise me given the docs say nothing about it.

Can somebody say which it is?

(2) By Gunter Hick (gunter_hick) on 2022-10-14 11:13:45 in reply to 1 [source]

Since the update_hook is registered on a connection - and is thus an attribute of said connection - it can only intercept changes invoked on said connection.

"The sqlite3_update_hook() interface registers a callback function with the database connection ..."

It cannot access any changes made outside of that connection; not even other threads in the same process using a different connection, much less changes by another process.

If you need cross process notification, you will either have to implement this yourself or poll pragma user_vrsion.

(3) By ddevienne on 2022-10-14 11:29:06 in reply to 2 [link] [source]

Perhaps you meant pragma data_version?

This post https://sqlite.org/forum/info/e76cac71ac2db298 has an interesting take on discovering changes
to a single (main) DB file, from any connection. (It's new to me, I need to remember it.)

(6) By Gunter Hick (gunter_hick) on 2022-10-15 06:55:10 in reply to 3 [link] [source]

Actually no.

There is schema_version, which counts to DDL changes to the file, so you (and SQLite itself) can determine if prepared statements needs to be re-prepared.

There is data_version, which is a computed property of a connection, that changes exactly when some other connection commits a change to the database file, ignoring changes by your connection.

There is sqlite3_update_hook() which provides notification of changes you yourself have performed (excluding some cases as documented).

There is user_version, which is managed completely by the user and can be used to indicate changes to the database that the application considers relevant.

(4.1) By John 42 (jtheisen42) on 2022-10-14 12:29:29 edited from 4.0 in reply to 2 [link] [source]

Thanks for clearing that up.

I really think that should be documented, it's far from obvious.

The fact that the docs say that the callback is registered with a connection alone doesn't imply that changes only from that connections are considered: If there was a proper, global notification feature in sqlite, any listener would still be registered on a connection, wouldn't it? Where else if not on a connection? Connections are what manages all access to the database, after all.

I can only say I'm quite baffled that this isn't spelled out in the docs.

(5) By Gunter Hick (gunter_hick) on 2022-10-15 06:44:02 in reply to 4.1 [link] [source]

Please consider that SQLite is a library, not a client-server database. It runs completely within your process. A "connection" is a memory structure inside you process' address space. There is no record of a connection outside of your process.

Hence there is no such thing as a "listener" in SQLite, and no methods of IPC are provided.

(7.1) Originally by John 42 (jtheisen42) with edits by Stephan Beal (stephan) on 2022-10-17 18:02:24 from 7.0 in reply to 5 [link] [source]

Many standard programming libraries have data structures that can be subscribed for changes. .NET has ObservableCollection, for example.

These things are also in-process and have nothing to do with IPC.

It would be extremely helpful for UI development if there was a data store where you could subscribe to changes in the data you're displaying to the user.

Currently the docs read as if SQLite had such capabilities - and I'm pretty sure others read it like that as well given the activity in the GitHub ticket I've been linking to.

This has really nothing to do with in-process or out-of-process. Client-server databases also don't support this unfortunately, so SQlite isn't different here. I'm just complaining about the misleading docs.

PS: This is assuming we're talking about option 2), listening to all changes in-process. Option 3) does include some form of IPC, but since there's a rollback journal or WAL the information what changed from other processes exists in an efficient way anyway. I don't think SQLite's nature of being in-process makes this feature any more difficult to implement. It probably makes it easier.

((edited by admin to consolidate a mis-edit created before the initial post was moderator-approved. @John: you cannot edit your own posts until you have been approved to post without moderation (which you now how been).))

(8) By Gunter Hick (gunter_hick) on 2022-10-18 05:54:41 in reply to 7.1 [link] [source]

No, you cannot subscribe to changes in SQLite.

The sqlite3_xxx_hook() callbacks return information about what effects the statements YOU YOURSELF are executing on the database connection are having on the data. Think debugging and logging.