adding automatic schema migration mechanism

diff --git a/README.md b/README.md
index 5eb4ec222141ee6cf24c82ac6b8a12289d29eba0..3b33b93b8964cc5634335a6856bbf219fad2c769 100644 (file)
--- a/README.md
+++ b/README.md
@@ -13,8 +13,4 @@ To debug-run:
 LANG=C.UTF-8 FLASK_DEBUG=1 FLASK_APP=motion.py flask run
 ```
 
-To install database schema, run in an interactive python shell (`python`):
-```
-import motion
-motion.init_db()
-```
+The database schema is automatically installed when the table "schema_version" does not exist and the application is started.

diff --git a/motion.py b/motion.py
index e18ceefc228c04efa9bcab12ba456c534c49def3..741eb302d0d37f6cebc8f859ed8fe5d655260527 100644 (file)
--- a/motion.py
+++ b/motion.py
@@ -78,8 +78,16 @@ def close_connection(exception):
 def init_db():
     with app.app_context():
         db = get_db()
-        with app.open_resource('schema.sql', mode='r') as f:
-            db.execute(f.read())
+        try:
+            ver = db.prepare("SELECT version FROM schema_version")()[0][0];
+            print("Database Schema version: ", ver)
+        except postgresql.exceptions.UndefinedTableError:
+            ver = 0
+        if ver < 1:
+            with app.open_resource('sql/schema.sql', mode='r') as f:
+                db.execute(f.read())
+
+init_db()
 
 @app.route("/")
 def main():

diff --git a/schema.sql b/sql/schema.sql
old mode 100644 (file)
new mode 100755 (executable)
similarity index 88%
rename from schema.sql
rename to sql/schema.sql
index ed0d876..116e76f
--- a/schema.sql
+++ b/sql/schema.sql
@@ -24,3 +24,7 @@ CREATE TABLE vote (motion_id INTEGER NOT NULL,
                  result vote_type NOT NULL,
                  entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY(motion_id, voter_id));
+
+DROP TABLE IF EXISTS schema_version;
+CREATE TABLE schema_version (version INTEGER NOT NULL);
+INSERT INTO schema_version(version) VALUES(1);