SQLite数据库的校验和？ [英] Checksum for a SQLite database?

问题描述

我想知道SQLite数据库文件是否已以任何方式更新。我该怎么去实现呢？

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.

推荐答案

根据 http：//www.sqlite .org / fileformat.html SQLite3在文件的 24..27 字节中维护文件更改计数器数据库文件。它与文件更改时间无关，例如，文件更改时间可以在二进制还原或回滚后更改，而根本不更改：

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

要真正确保两个数据库文件相等只有在转储文件（ .dump ），将输出减少到 INSERT 语句并进行排序之后，才能确定比较的结果（也许通过某种加密安全的校验和）。但这简直是矫over过正。

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.

这篇关于SQLite数据库的校验和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！