I'm trying to write some defensive code that is able to recover from database corruption. The idea is that if a disk fails and a database becomes corrupt it can be detected and synchronised from a backup copy.

To this end, I've just been trying to write a function that returns true only when it is sure the database is ok. I use PRAGMA integrity check; and compare the returned string with "ok". When I tried this with a few random database changes, though, I had a hard time trying to get the corruption to trigger. I did the following:

CREATE TABLE foo(bar);

I then went in with a text editor and started modifying the bar record. I changed "bar" to "car", but the change was not detected. I started modifying characters to the left and right of the "car" string, but still no corruption. I was able to get corruption to be detected when I truncated the file.

Can I take it from this behaviour that there isn't any checksum checking going on apart from headers and the BTrees themselves? Will the integrity_check at least guarantee me that I won't at some later stage get an SQLITE_CORRUPT return?

PRAGMA integrity_check does a good job of testing the integrity of the BTree layer in the file. (See http://www.sqlite.org/fileformat.html for a definition of the "btree layer".) The pragma also checks to make sure that all indices are correct. But other than that, changes to the database can easily go undetected.

Note that if you had had an index on the foo table,
your change of "bar" to "car" would have been detected
by the index checks.

D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to