SQLite Forum

Hooks invoked when modifications done from another connection?
Login

Hooks invoked when modifications done from another connection?

(1) By ddevienne on 2021-01-07 17:50:38 [link] [source]

The C API has various hook APIs, for commit and rollback, update, pre-update, and finally wal commit.

The doc is not 100% clear however, on whether those hooks are called back only when it's the
connection the hook was registered with, that made the change; or whether they will also be called
back when those changes originate in a different connection (in the same process, or another).

The way I read it, only the last (wal) hook may be cross-connection, because it uses the phrase
invoked each time data is committed to a database in wal mode, emphasis on database, not
connection; while for other hooks, connection is used.

Is my reading of the doc correct?

What I actually need is an accurate timestamp to know when any DB managed by my server was
last modified. With the twist that several instances of the server may be running, on the same DBs.
And I'm thus wondering whether to tackle this via SQLite hooks, or using a more ad-hoc approach
in my own code / connections only.

An SQLite solution would work with any app (connection) doing changes,
including the official shell tool, while my ad-hoc solution would be tied to my code.

Thanks, --DD

PS: I'm still using Journal mode BTW, but was planning on switching to WAL.

(2) By Keith Medcalf (kmedcalf) on 2021-01-07 20:22:39 in reply to 1 [link] [source]

If when "hooking" the hooker you pass the connection (as in sqlite3*) then the hook is hookering that connection only.

If the hook was "global" (covered more than one connection) then there would be no need to pass the connection to hook to the hooker.

Noe that "connection" and "database" are entirely different things. You can have a million connections to one database, or one connection that connects to a million databases.

The hooker is hooking the connection, unless of course it is a "global hooker" that does not take a reference to a connection when hookering -- for example sqlite3_auto_extension.

(3) By ddevienne on 2021-01-08 08:54:06 in reply to 2 [link] [source]

OK, Thanks. Disappointed, I thought the SHM file of WAL allowed some cross-connection notification. Ad-hoc it will be then...

or one connection that connects to a million databases

Well, between 10 (the default) and 125 in fact, but I get the point.

(4) By anonymous on 2021-01-08 12:22:59 in reply to 3 [link] [source]

An option is to poll the data_version pragma

https://www.sqlite.org/pragma.html#pragma_data_version.

This will change for any update by any process, your connection will be able to see these changes and react. But you will need to poll it rather than have your hook triggered. It should be quite easy to implement though.

(5) By ddevienne on 2021-01-08 13:29:31 in reply to 4 [link] [source]

Thanks. Yes, I'm aware of data_version.

Problem is, this does not give me an accurate last-modified time.
Which is required on the protocol API I must implement.

(6) By anonymous on 2021-01-09 15:15:16 in reply to 5 [source]

How about using the database file's mtime an/or the inotify framework (if you server runs Linux)?