From 0ec346d942450ac4a29c74d115428fd856af94b2 Mon Sep 17 00:00:00 2001 From: Erik Johnston Date: Fri, 17 Apr 2015 11:10:20 +0100 Subject: Add unique index to room_aliases, remove duplicates on upgrade. Convert some columns back to TEXT from bytea --- synapse/storage/schema/delta/16/room_alias_index.sql | 3 +++ synapse/storage/schema/delta/16/unique_constraints.sql | 8 ++++++++ 2 files changed, 11 insertions(+) create mode 100644 synapse/storage/schema/delta/16/room_alias_index.sql (limited to 'synapse/storage/schema/delta') diff --git a/synapse/storage/schema/delta/16/room_alias_index.sql b/synapse/storage/schema/delta/16/room_alias_index.sql new file mode 100644 index 0000000000..f82486132b --- /dev/null +++ b/synapse/storage/schema/delta/16/room_alias_index.sql @@ -0,0 +1,3 @@ + +CREATE INDEX IF NOT EXISTS room_aliases_id ON room_aliases(room_id); +CREATE INDEX IF NOT EXISTS room_alias_servers_alias ON room_alias_servers(room_alias); diff --git a/synapse/storage/schema/delta/16/unique_constraints.sql b/synapse/storage/schema/delta/16/unique_constraints.sql index 3604ea8427..fecf11118c 100644 --- a/synapse/storage/schema/delta/16/unique_constraints.sql +++ b/synapse/storage/schema/delta/16/unique_constraints.sql @@ -70,3 +70,11 @@ DROP INDEX IF EXISTS presence_list_observers; CREATE UNIQUE INDEX presence_list_observers ON presence_list( user_id, observed_user_id ); + +-- +DELETE FROM room_aliases WHERE rowid not in ( + SELECT MIN(rowid) FROM room_aliases GROUP BY room_alias +); + +DROP INDEX IF EXISTS room_aliases_id; +CREATE INDEX room_aliases_id ON room_aliases(room_id); -- cgit 1.4.1