Checksum for a SQLite database?

Question

I'd like to be able to tell whether a SQLite database file has been updated in any way. How would I go about implementing that?

The first solution I think of is comparing checksums, but I don't really have any experience working with checksums.

Answer 1

According to http://www.sqlite.org/fileformat.html SQLite3 maintains a file change counter in byte 24..27 of the database file. It is independent of the file change time which, for example, can change after a binary restore or rollback while nothing changed at all:

$ sqlite3 test.sqlite 'create table test ( test )'
$ od --skip-bytes 24 --read-bytes=4 -tx1  test.sqlite | sed -n '1s/[^[:space:]]*[[:space:]]//p' | tr -d ' '
00000001
$ sqlite3 test.sqlite "insert into test values ( 'hello world');"
$ od --skip-bytes 24 --read-bytes=4 -tx1  test.sqlite | sed -n '1s/[^[:space:]]*[[:space:]]//p' | tr -d ' '
00000002
$ sqlite3 test.sqlite "delete from test;"
$ od --skip-bytes 24 --read-bytes=4 -tx1  test.sqlite | sed -n '1s/[^[:space:]]*[[:space:]]//p' | tr -d ' '
00000003
$ sqlite3 test.sqlite "begin exclusive; insert into test values (1); rollback;"
$ od --skip-bytes 24 --read-bytes=4 -tx1  test.sqlite | sed -n '1s/[^[:space:]]*[[:space:]]//p' | tr -d ' '
00000003

To be really sure that two database files are "equal" you can only be sure after dumping the files (.dump), reducing the output to the INSERT statements and sorting that result for compare (perhaps by some cryptographically secure checksum). But that is plain overkill.

Answer 2

A bit late an answer maybe, 10+ years later, but beside the file change counter, people might be interested in the dbhash utility (from the SQLite project itself), which provides a logical SHA1 checksum of an SQLite database, which is immune to physical differences.