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)?

(7) By Brian Minton (bjmgeek) on 2023-03-22 15:21:58 in reply to 1 [link] [source]

As long as you're using a non-wal journal mode (e.g. delete or truncate), committing a transaction will change the mtime of the database file.

However, I have noticed that a long-running write transaction will write to the main database file as well as the journal file. So, I don't think a change in the mtime of the database file is a guarantee of a committed transaction.

If using wal mode, it's a little less clear, because readers still write to the shm file, and committing a transaction doesn't necessarily write to the db file itself, until a checkpoint happens. I suppose you could check the mtime of the wal file if it exists, otherwise the database file itself.

(8) By Keith Medcalf (kmedcalf) on 2023-03-22 16:16:24 in reply to 3 [link] [source]

No. THere is no "server". Your application is the server. You can hook your connection and then send whatever notification you like wherever you want to send it by whatever means you wish to transmit it.

Each connection is independent of each other connection.

(9) By Donal Fellows (dkfellows) on 2023-03-23 09:36:44 in reply to 2 [link] [source]

You can have a million connections to one database

You'll hit an OS limit on the total number of open files before that is reached. That's a good thing.

(10) By Rowan Worth (sqweek) on 2023-03-24 01:28:37 in reply to 7 [link] [source]

However, I have noticed that a long-running write transaction will write to the main database file as well as the journal file. So, I don't think a change in the mtime of the database file is a guarantee of a committed transaction.

What you're observing here is cache spill, which is not specifically related to the length of a transaction but rather it's size (ie. how many database pages it modifies). The cache_size and cache_spill PRAGMAs explain what is going on here.