SatSale

commit 131d2707cd1a69d1024e29c3980c94bc7591db98
parent aaacec48497b1b9dd628cb9398d13651f3a647c8
Author: Nick <nick@nickfarrow.com>
Date:   Fri, 18 Mar 2022 17:28:58 +1100

Merge pull request #54 from kristapsk/db-schema-versioning

Simple database schema versioning and migration
Diffstat:
M
payments/database.py
 | 
45
+++++++++++++++++++++++++++++++++++++++++++--
M
satsale.py
 | 
2
++

2 files changed, 45 insertions(+), 2 deletions(-)
diff --git a/payments/database.py b/payments/database.py
@@ -3,14 +3,55 @@ import logging
 
 
 def create_database(name="database.db"):
-    with sqlite3.connect("database.db") as conn:
-        logging.info("Creating new database.db...")
+    with sqlite3.connect(name) as conn:
+        logging.info("Creating new {}...".format(name))
         conn.execute(
             "CREATE TABLE payments (uuid TEXT, fiat_value DECIMAL, btc_value DECIMAL, method TEXT, address TEXT, time DECIMAL, webhook TEXT, rhash TEXT)"
         )
     return
 
 
+def _get_database_schema_version(name="database.db"):
+    with sqlite3.connect(name) as conn:
+        return conn.execute("SELECT version FROM schema_version").fetchone()[0]
+
+
+def _set_database_schema_version(version, name="database.db"):
+    with sqlite3.connect(name) as conn:
+        conn.execute("UPDATE schema_version SET version = {}".format(version))
+
+
+def _log_migrate_database(from_version, to_version, message):
+    logging.info("Migrating database from {} to {}: {}".format(
+        from_version, to_version, message))
+
+
+def migrate_database(name="database.db"):
+    with sqlite3.connect(name) as conn:
+        version_table_exists = conn.execute(
+            "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'schema_version'"
+        ).fetchone()
+        if version_table_exists:
+            schema_version = _get_database_schema_version(name)
+        else:
+            schema_version = 0
+
+    if schema_version < 1:
+        _log_migrate_database(0, 1, "Creating new table for schema version")
+        with sqlite3.connect(name) as conn:
+            conn.execute("CREATE TABLE schema_version (version INT)")
+            conn.execute("INSERT INTO schema_version (version) VALUES (1)")
+
+    #if schema_version < 2:
+    #   do next migration
+
+    new_version = _get_database_schema_version(name)
+    if schema_version != new_version:
+        logging.info(
+            "Finished migrating database schema from version {} to {}".format(
+                schema_version, new_version))
+
+
 def write_to_database(invoice, name="database.db"):
     with sqlite3.connect(name) as conn:
         cur = conn.cursor()
diff --git a/satsale.py b/satsale.py
@@ -50,6 +50,8 @@ logging.info("Initialised Flask with secret key: {}".format(app.config["SECRET_K
 # Create payment database if it does not exist
 if not os.path.exists("database.db"):
     database.create_database()
+# Check and migrate database to current version if needed
+database.migrate_database()
 
 
 # Render index page