Global PRAGMA data_version
(1) By anonymous on 2021-05-16 00:00:53 [link] [source]
Is there something like the data_version pragma that works globally. Some kind of monotonic counter that increases on any database modification.
For coarsly invalidating a cache, I'd like a simple way to determine if an sqlite3 database changed since the cache elements were generated.
Adding triggers on each table and operations to increment a counter seems a bit heavy handed.
(2.2) By Keith Medcalf (kmedcalf) on 2021-05-16 01:43:15 edited from 2.1 in reply to 1 [link] [source]
What is your definition of Globally?
pragma data_version returns an integer which, if it changes between two calls to the pragma on the same connection, that some other connection has made a change to the database, thus invalidating the page cache for the connection on which the pragma was executed.
(3) By anonymous on 2021-05-16 01:47:36 in reply to 2.2 [source]
By globally I mean I don't want something that is tied to connections. Just a number that gets bumped whenever an update is made to the data.
(4) By Keith Medcalf (kmedcalf) on 2021-05-16 03:20:07 in reply to 3 [link] [source]
Write a function and spin it up on a separate thread.
open database connection for use only in this thread Loop Wait for 1 second. Run `pragma data_version` on the connection. Store the return value in a global volatile value. Until Process Termination close connection
Seems pretty straightforward to me.
(6) By anonymous on 2021-05-16 12:54:39 in reply to 4 [link] [source]
That assumes you have a persistent process, it doesn't work if at some point you have no process acting on the database.
(7) By Keith Medcalf (kmedcalf) on 2021-05-16 18:05:51 in reply to 6 [link] [source]
If you do not have a persistent process, then how do you have values cached?
(8) By anonymous on 2021-05-16 19:10:32 in reply to 7 [link] [source]
I just have data that is generated from the database and need a quick way to determine if it's fresh or not.
(5) By mlin (dnamlin) on 2021-05-16 10:13:06 in reply to 3 [link] [source]
The SQLITE_FCNTL_DATA_VERSION opcode for sqlite3_file_control() is probably closer to what you want: https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntldataversion
One wrinkle, it only reflects committed changes to the database. That sounds reasonable & innocuous, but it can cause you papercuts if you don't keep it in mind during long-running transactions.