From 82b34e813de4dadb8ec5bce068f7113e32e60ead Mon Sep 17 00:00:00 2001 From: Erik Johnston 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 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 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