How to tell if sqlite database file is valid or not

Question

In the code below, pathToNonDatabase is the path to a simple text file, not a real sqlite database. I was hoping for sqlite3_open to detect that, but it doesn't (db is not NULL, and result is SQLITE_OK). So, how to detect that a file is not a valid sqlite database?

sqlite3 *db = NULL;
int result = sqlite3_open(pathToNonDatabase, &db);

if((NULL==db) || (result!=SQLITE_OK)) { 
   // invalid database
}

Answer 1

sqlite opens databases lazily. Just do something immediately after opening that requires it to be a database.

The best is probably pragma schema_version;.

• This will report 0 if the database hasn't been created (for instance, an empty file). In this case, it's safe work with (and run CREATE TABLE, etc)
• If the database has been created, it will return how many revisions the schema has gone through. This value might not be interesting, but that it's not zero is.
• If the file exists and isn't a database (or empty), you'll get an error.

If you want a somewhat more thorough check, you can use pragma quick_check;. This is a lighter-weight integrity check, which skips checking that the contents of the tables line up with the indexes. It can still be very slow.

Avoid integrity_check. It not only checks every page, but then verifies the contents of the tables against the indexes. This is positively glacial on a large database.