If you enable the SQLITE_DBPAGE extension then you can read the change counter from page 1. You might also want to set SQLITE_DBCONFIG_DEFENSIVE as otherwise that extension lets you write raw pages as well as read them.

Did you miss the second paragraph of the file format doc section you linked to?

In WAL mode, changes to the database are detected using the wal-index and so the change counter is not needed. Hence, the change counter might not be incremented on each transaction in WAL mode.

Did you miss the second paragraph of the file format doc section you linked to?

No, I didn't. Using sqlite_dbpage() should read the current page 1. If that page is in the WAL, it should read it from the WAL. That's exactly why I suggest using SQLite to read the data instead of reading the database file directly with some operating system utility or a scripting language, i.e. because SQLite knows where the real page 1 is.

Unfortunately, the "not needed" part means that page 1 won't be written to the WAL unless some other piece of it changes.

That I wasn't aware of. I stand corrected; thanks for setting me straight.

I'd have a look at dbhub.io .. not that it solves your problem, but rather than reinvent the wheel, you could have a look at how they manage transactions, commits, et al.

Can't be done inside SQLite. Write a script or program to look at the touch dates on the files.

Use rsync for backup. However:

If database is copied while it is being updated, the backed-up copy will be corrupt. You need to copy when all transactions are closed.

Roman

Hi,

interesting project (thumbsup).

I think you need to distinguish between https://en.wikipedia.org/wiki/Necessity_and_sufficiency .

There are a number of criteria to assume a database has not changed on file level etc.

https://sqlite.org/sqldiff.html is a thing but I suspect it will scan the entire db file and judging from the other replies I assume that currently that cannot be avoided unless you make assumptions about the database itsself.

If you are willing to do that it would be possible to add triggers to run a changelog into a table by making any change inserting into the changelog table the QUOTEd value of all changed columns.

You might also want to use dumps for cold storage as in https://sqlite.org/cli.html#converting_an_entire_database_to_a_text_file

And put that into cold storage. In my experience dump files may be bigger than the sqlite database but tend to be more compressible and more friendly for version control.

Regards, ralf

(If you don't want to store text format but storage size is a priority over recovery speed, you can store all index DDL from SQLite_Schema and drop all indexes (and vacuum) - then execute the DDL when restoring the file. Any index is by definition redundant data and can be completely removed from data and definition making compression more efficient.)

Thank you to everyone who replied! Based on your responses, my plan is to first check modification dates for any difference (without assuming anything about the actual timestamps to avoid the effects of clock drift and timing differences between machines) as well as a hash of the contents of the database if a modification time change is detected.