Check that a database file is valid and what the program is expecting?

When connecting to an SQLite database from Python (using Python 2.6), what strateg开发者_JAVA百科ies are there to ensure that a program has opened a valid database file (where valid here means "matches what the program is expecting")?

I'd like to make sure that after some opening checks I can be (reasonably) sure that the program has opened a database file that will work as expected - ideally CREATEing everything if the file was new/empty and stopping/warning if the file is a database for something else, or otherwise broken.

I'm guessing the trick is to compare the opened file's schema to an expected schema in the program?

If so, how would you go about doing?

Otherwise, what else should be done?

In solutions where I want something similar, I tend to keep a .sql file located at the same directory as my code, containing the build instructions for the database, using constructs similar to the following:

# setup
PRAGMA foreign_keys=ON;
PRAGMA journal_mode=TRUNCATE;
PRAGMA locking_mode=EXCLUSIVE;
PRAGMA synchronous=NORMAL;

# Locations
CREATE TABLE IF NOT EXISTS Locations (
    locID INTEGER PRIMARY KEY,
    locPath TEXT NOT NULL
);
# blah-blah
CREATE UNIQUE INDEX IF NOT EXISTS fldPath_idx
ON Folders(fldPath);
# and so on

just taking care that all SQL statements end with a semicolon as the last non-whitespace character of a line, since I have code like the following method that ensures the schema running every time my application starts:

def db_schema(self):
    cur= self._db.cursor()
    with io.open(self.SQLPATH, "r") as fp:
        sql_statement= ""
        for line in fp:
            line= line.rstrip()
            if line.startswith('#'): continue
            sql_statement+= line
            if line.endswith(";"):
                try:
                    cur.execute(sql_statement)
                except sql.OperationalError:
                    print("Failed:\n%s" % sql_statement)
                sql_statement= ""
    # file is done
    cur.close()

Note the use of CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS.

SQLite has a pragma user_version that will store any arbitrary number in the database for you. Normally you would use this for tracking your own schema version - eg first version of app is 1, and when you change schema three app versions later you set it to 2 to detect the schema upgrade has been done by your code.

However you could set it to any starting value. eg start with 3656672354 and add to that for your internal version tracking. The likelihood of any other database having a value within that range is virtually zero.