SQLite Forum

Global PRAGMA data_version
Login

Global PRAGMA data_version

(1) By anonymous on 2021-05-16 00:00:53 [link] [source]

Hello,

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 [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 [link] [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.