Verify that the database file is valid and what the program expects?

Question

When connecting to a SQLite database from Python (using Python 2.6), what strategies exist to ensure that the program opens a valid database file (where valid here means "matches expected program")?

I would like to make sure that after some opening checks I can (reasonably) verify that the program opened the database file, which will work as expected - ideally CREATE everything if the file was new / empty and stop / warning if the file is a database for something else is broken somehow.

I assume the trick is to compare the open file scheme with the expected scheme in the program?
If so, how would you do it?
Otherwise, what else needs to be done?

Answer 1

In solutions where I need something like this, I try to store the .sql file located in the same directory as my code containing the assembly 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 character without a space, as I have code similar to the following method, which ensures the execution of the schema 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 .

Answer 2

SQLite has the user_version pragma that will store any arbitrary number in the database for you. Usually, you use this to track your own version of the scheme — for example, the first version of the application is 1, and when you change the version scheme of the three applications later, you set it to 2 to find that the code was updated by your code.

However, you can set any initial value. For example, start with 3656672354 and add it for internal version tracking. The probability that any other database matters in this range is almost zero.