summary refs log tree commit diff
path: root/synapse/storage/schema
diff options
context:
space:
mode:
authorAlexander Maznev <alexander.maznev@gmail.com>2019-10-04 04:34:16 -0500
committerRichard van der Hoff <1389908+richvdh@users.noreply.github.com>2019-10-04 10:34:16 +0100
commit13c4345c844e75b0d1a4ce66e4fb2eb9820cb7f6 (patch)
tree03c4cab79fbf3b3510cf8ff47c7d856a6dc4775b /synapse/storage/schema
parentmedia/thumbnailer: Better quality for 1-bit / 8-bit color palette images (#2142) (diff)
downloadsynapse-13c4345c844e75b0d1a4ce66e4fb2eb9820cb7f6.tar.xz
Update `user_filters` table to have a unique index, and non-null columns (#1172)
Diffstat (limited to 'synapse/storage/schema')
-rw-r--r--synapse/storage/schema/delta/56/unique_user_filter_index.py46
1 files changed, 46 insertions, 0 deletions
diff --git a/synapse/storage/schema/delta/56/unique_user_filter_index.py b/synapse/storage/schema/delta/56/unique_user_filter_index.py
new file mode 100644
index 0000000000..4efc1a586f
--- /dev/null
+++ b/synapse/storage/schema/delta/56/unique_user_filter_index.py
@@ -0,0 +1,46 @@
+import logging
+
+from synapse.storage.engines import PostgresEngine
+
+logger = logging.getLogger(__name__)
+
+
+def run_upgrade(cur, database_engine, *args, **kwargs):
+    if isinstance(database_engine, PostgresEngine):
+        select_clause = """
+        CREATE TEMPORARY TABLE user_filters_migration AS
+            SELECT DISTINCT ON (user_id, filter_id) user_id, filter_id, filter_json
+            FROM user_filters;
+        """
+    else:
+        select_clause = """
+        CREATE TEMPORARY TABLE user_filters_migration AS
+            SELECT * FROM user_filters GROUP BY user_id, filter_id;
+        """
+    sql = (
+        """
+        BEGIN;
+            %s
+            DROP INDEX user_filters_by_user_id_filter_id;
+            DELETE FROM user_filters;
+            ALTER TABLE user_filters
+               ALTER COLUMN user_id SET NOT NULL
+               ALTER COLUMN filter_id SET NOT NULL
+               ALTER COLUMN filter_json SET NOT NULL;
+            INSERT INTO user_filters(user_id, filter_id, filter_json)
+                SELECT * FROM user_filters_migration;
+            DROP TABLE user_filters_migration;
+            CREATE UNIQUE INDEX user_filters_by_user_id_filter_id_unique
+                ON user_filters(user_id, filter_id);
+        END;
+    """
+        % select_clause
+    )
+    if isinstance(database_engine, PostgresEngine):
+        cur.execute(sql)
+    else:
+        cur.executescript(sql)
+
+
+def run_create(cur, database_engine, *args, **kwargs):
+    pass