From 82b34e813de4dadb8ec5bce068f7113e32e60ead Mon Sep 17 00:00:00 2001
From: Erik Johnston <erik@matrix.org>
Date: Wed, 4 Mar 2015 12:04:19 +0000
Subject: SYN-67: Finish up implementing new database schema management

---
 synapse/storage/schema/schema_version.sql | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)
 create mode 100644 synapse/storage/schema/schema_version.sql

(limited to 'synapse/storage/schema/schema_version.sql')

diff --git a/synapse/storage/schema/schema_version.sql b/synapse/storage/schema/schema_version.sql
new file mode 100644
index 0000000000..83a8c7b7ce
--- /dev/null
+++ b/synapse/storage/schema/schema_version.sql
@@ -0,0 +1,29 @@
+/* Copyright 2015 OpenMarket Ltd
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+CREATE TABLE IF NOT EXISTS schema_version(
+    Lock char(1) NOT NULL DEFAULT 'X',  -- Makes sure this table only has one row.
+    version INTEGER NOT NULL,
+    upgraded BOOL NOT NULL,  -- Whether we reached this version from an upgrade or an initial schema.
+    CONSTRAINT schema_version_lock CHECK (Lock='X') ON CONFLICT REPLACE
+);
+
+CREATE TABLE IF NOT EXISTS schema_deltas(
+    version INTEGER NOT NULL,
+    file TEXT NOT NULL,
+    CONSTRAINT schema_deltas_ver_file UNIQUE (version, file) ON CONFLICT IGNORE
+);
+
+CREATE INDEX IF NOT EXISTS schema_deltas_ver ON schema_deltas(version);
-- 
cgit 1.5.1


From 5eefd1f618a19cc27cfdaf915dc3abd3720cd0e7 Mon Sep 17 00:00:00 2001
From: Erik Johnston <erik@matrix.org>
Date: Wed, 4 Mar 2015 13:52:18 +0000
Subject: Add unique constraint on schema_version.lock schema. Use conflict
 clause in sql.

---
 synapse/storage/__init__.py               | 6 +++---
 synapse/storage/schema/schema_version.sql | 3 ++-
 2 files changed, 5 insertions(+), 4 deletions(-)

(limited to 'synapse/storage/schema/schema_version.sql')

diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py
index d637e8c4f7..c09228c37f 100644
--- a/synapse/storage/__init__.py
+++ b/synapse/storage/__init__.py
@@ -645,7 +645,7 @@ def _setup_new_database(cur):
     cur.executescript(sql_script)
 
     cur.execute(
-        "INSERT INTO schema_version (version, upgraded)"
+        "INSERT OR REPLACE INTO schema_version (version, upgraded)"
         " VALUES (?,?)",
         (max_current_ver, False)
     )
@@ -748,7 +748,7 @@ def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
             )
 
             cur.execute(
-                "INSERT INTO schema_version (version, upgraded)"
+                "INSERT OR REPLACE INTO schema_version (version, upgraded)"
                 " VALUES (?,?)",
                 (v, True)
             )
@@ -803,7 +803,7 @@ def prepare_sqlite3_database(db_conn):
 
             if row and row[0]:
                 db_conn.execute(
-                    "INSERT INTO schema_version (version, upgraded)"
+                    "INSERT OR REPLACE INTO schema_version (version, upgraded)"
                     " VALUES (?,?)",
                     (row[0], False)
                 )
diff --git a/synapse/storage/schema/schema_version.sql b/synapse/storage/schema/schema_version.sql
index 83a8c7b7ce..20b1481ba5 100644
--- a/synapse/storage/schema/schema_version.sql
+++ b/synapse/storage/schema/schema_version.sql
@@ -17,7 +17,8 @@ CREATE TABLE IF NOT EXISTS schema_version(
     Lock char(1) NOT NULL DEFAULT 'X',  -- Makes sure this table only has one row.
     version INTEGER NOT NULL,
     upgraded BOOL NOT NULL,  -- Whether we reached this version from an upgrade or an initial schema.
-    CONSTRAINT schema_version_lock CHECK (Lock='X') ON CONFLICT REPLACE
+    CONSTRAINT schema_version_lock_x CHECK (Lock='X')
+    CONSTRAINT schema_version_lock_uniq UNIQUE (Lock)
 );
 
 CREATE TABLE IF NOT EXISTS schema_deltas(
-- 
cgit 1.5.1


From 17d319a20df6e92ddbb8ca5b9e08615d2975b466 Mon Sep 17 00:00:00 2001
From: Erik Johnston <erik@matrix.org>
Date: Wed, 4 Mar 2015 15:06:22 +0000
Subject: s/schema_deltas/applied_schema_deltas/

---
 synapse/storage/__init__.py               | 16 +++++++++-------
 synapse/storage/schema/schema_version.sql |  4 ++--
 2 files changed, 11 insertions(+), 9 deletions(-)

(limited to 'synapse/storage/schema/schema_version.sql')

diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py
index 98b877bd08..e2d5e5a41d 100644
--- a/synapse/storage/__init__.py
+++ b/synapse/storage/__init__.py
@@ -674,12 +674,13 @@ def _setup_new_database(cur):
     _upgrade_existing_database(
         cur,
         current_version=max_current_ver,
-        delta_files=[],
+        applied_delta_files=[],
         upgraded=False
     )
 
 
-def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
+def _upgrade_existing_database(cur, current_version, applied_delta_files,
+                               upgraded):
     """Upgrades an existing database.
 
     Delta files can either be SQL stored in *.sql files, or python modules
@@ -712,8 +713,9 @@ def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
 
     Args:
         cur (Cursor)
-        current_version (int): The current version of the schema
-        delta_files (list): A list of deltas that have already been applied
+        current_version (int): The current version of the schema.
+        applied_delta_files (list): A list of deltas that have already been
+            applied.
         upgraded (bool): Whether the current version was generated by having
             applied deltas or from full schema file. If `True` the function
             will never apply delta files for the given `current_version`, since
@@ -746,7 +748,7 @@ def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
         directory_entries.sort()
         for file_name in directory_entries:
             relative_path = os.path.join(str(v), file_name)
-            if relative_path in delta_files:
+            if relative_path in applied_delta_files:
                 continue
 
             absolute_path = os.path.join(
@@ -781,7 +783,7 @@ def _upgrade_existing_database(cur, current_version, delta_files, upgraded):
 
             # Mark as done.
             cur.execute(
-                "INSERT INTO schema_deltas (version, file)"
+                "INSERT INTO applied_schema_deltas (version, file)"
                 " VALUES (?,?)",
                 (v, relative_path)
             )
@@ -807,7 +809,7 @@ def _get_or_create_schema_state(txn):
 
     if current_version:
         txn.execute(
-            "SELECT file FROM schema_deltas WHERE version >= ?",
+            "SELECT file FROM applied_schema_deltas WHERE version >= ?",
             (current_version,)
         )
         return current_version, txn.fetchall(), upgraded
diff --git a/synapse/storage/schema/schema_version.sql b/synapse/storage/schema/schema_version.sql
index 20b1481ba5..0431e2d051 100644
--- a/synapse/storage/schema/schema_version.sql
+++ b/synapse/storage/schema/schema_version.sql
@@ -21,10 +21,10 @@ CREATE TABLE IF NOT EXISTS schema_version(
     CONSTRAINT schema_version_lock_uniq UNIQUE (Lock)
 );
 
-CREATE TABLE IF NOT EXISTS schema_deltas(
+CREATE TABLE IF NOT EXISTS applied_schema_deltas(
     version INTEGER NOT NULL,
     file TEXT NOT NULL,
     CONSTRAINT schema_deltas_ver_file UNIQUE (version, file) ON CONFLICT IGNORE
 );
 
-CREATE INDEX IF NOT EXISTS schema_deltas_ver ON schema_deltas(version);
+CREATE INDEX IF NOT EXISTS schema_deltas_ver ON applied_schema_deltas(version);
-- 
cgit 1.5.1

