In _init_state, track and deal with needed database schema upgrades
Change-Id: I18564e645f7876e71e1477e9027823096e67557c
diff --git a/db/db.py b/db/db.py
index 527e5ec..ec8e1f4 100644
--- a/db/db.py
+++ b/db/db.py
@@ -1,6 +1,6 @@
#! /usr/bin/python
-# Copyright 2014-2016 Linaro Limited
+# Copyright 2014-2018 Linaro Limited
# Authors: Dave Pigott <dave.pigott@linaro.org>,
# Steve McIntyre <steve.mcintyre@linaro.org>
#
@@ -26,6 +26,13 @@
TRUNK_ID_NONE = -1
+# The schema version that this code expects. If it finds an older version (or
+# no version!) at startup, it will auto-migrate to the latest version
+#
+# Version 0: Base, no version found
+# Version 1: No changes, except adding the version and coping with upgrade
+DATABASE_SCHEMA_VERSION = 1
+
if __name__ == '__main__':
vlandpath = os.path.abspath(os.path.normpath(os.path.dirname(sys.argv[0])))
sys.path.insert(0, vlandpath)
@@ -49,17 +56,49 @@
self.connection.close()
# Create the state table (if needed) and add its only record
+ #
+ # Use the stored record of the expected database schema to track what
+ # version the on-disk database is, and upgrade it to match the current code
+ # if necessary.
def _init_state(self):
+ found_db = False
+ current_db_version = 0
try:
sql = "SELECT * FROM state"
self.cursor.execute(sql)
- self.cursor.execute('DELETE FROM state')
+ found_db = True
except psycopg2.ProgrammingError:
self.connection.commit() # state doesn't exist; clear error
- sql = "CREATE TABLE state (last_modified TIMESTAMP)"
+ sql = "CREATE TABLE state (last_modified TIMESTAMP, schema_version INTEGER)"
self.cursor.execute(sql)
- sql = "INSERT INTO state (last_modified) VALUES (%s)"
- data = (datetime.datetime.now(), )
+ # We've just created a version 1 database
+ current_db_version = 1
+
+ if found_db:
+ # Grab the version of the database we have
+ try:
+ sql = "SELECT schema_version FROM state"
+ self.cursor.execute(sql)
+ current_db_version = self.cursor.fetchone()[0]
+ # No version found ==> we have "version 0"
+ except psycopg2.ProgrammingError:
+ self.connection.commit() # state doesn't exist; clear error
+ current_db_version = 0
+
+ # Now delete the existing state record, we'll write a new one in a
+ # moment
+ self.cursor.execute('DELETE FROM state')
+ logging.info("Found a database, version %d", current_db_version)
+
+ # Apply upgrades here!
+ if current_db_version < 1:
+ logging.info("Upgrading database to match schema version 1")
+ sql = "ALTER TABLE state ADD schema_version INTEGER"
+ self.cursor.execute(sql)
+ logging.info("Sschema version 1 upgrade successful")
+
+ sql = "INSERT INTO state (last_modified, schema_version) VALUES (%s, %s)"
+ data = (datetime.datetime.now(), DATABASE_SCHEMA_VERSION)
self.cursor.execute(sql, data)
self.connection.commit()