How to check SQLite file consistency (health check)

Question

I am using a SQLite database file in my Android application that is created from several pieces stored in the assets folder by using the following approach: ReignDesign - Using your own SQLite database in Android applications

On completion of the assemby process I would like to check if the database was correctly merged. The following approaches crossed my mind:

• MD5 hash comparison
• Checking table existence and entry counts

Which approach would you recommend? Is there a better way?

Thanks,

Philipp

Answer 1

In Api 11, SQLiteDatabase has a method isDatabaseIntegrityOk for checking integrity of SQLite databases :

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#isDatabaseIntegrityOk()

And source code is avaible here :

https://github.com/android/platform_frameworks_base/blob/ics-mr1-release/core/java/android/database/sqlite/SQLiteDatabase.java#L2585

It's easy to backport on old devices.

Answer 2

I had a similar situation in an application I'm developing. There are a variety of ways of doing it; but in the end I stopped worrying about what exactly and how to best measure database integrity, and instead focused on 'is my database usable by my application'.

So basically I test whether: 1) I can open the SQLite database properly 2) I can perform queries on the database and 3) Whether the result for a pre-defined query returns what is expected.

So basically: include a table with a record of known ID that gives a value that you know, then try to read that value. Checking the table count can't hope.

That said I'm hoping someone here with a good knowledge of DB systems will explain a) exactly what PRAGMA integrity_check does and b) how reliable it is and how efficient it is compared with manual table checks.

Answer 3

http://www.sqlite.org/pragma.html#pragma_integrity_check.

Answer 4

I would prefer second approach on the first one both with respect to performance and ease of coding.