From 095b45c1653bc93788a45b891f29efc30f0b1b07 Mon Sep 17 00:00:00 2001 From: Erik Johnston Date: Fri, 3 Feb 2017 18:12:53 +0000 Subject: Aggregate event push actions --- .../storage/schema/delta/40/event_push_summary.sql | 37 ++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 synapse/storage/schema/delta/40/event_push_summary.sql (limited to 'synapse/storage/schema') diff --git a/synapse/storage/schema/delta/40/event_push_summary.sql b/synapse/storage/schema/delta/40/event_push_summary.sql new file mode 100644 index 0000000000..3918f0b794 --- /dev/null +++ b/synapse/storage/schema/delta/40/event_push_summary.sql @@ -0,0 +1,37 @@ +/* Copyright 2017 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. + */ + +-- Aggregate of old notification counts that have been deleted out of the +-- main event_push_actions table. This count does not include those that were +-- highlights, as they remain in the event_push_actions table. +CREATE TABLE event_push_summary ( + user_id TEXT NOT NULL, + room_id TEXT NOT NULL, + notif_count BIGINT NOT NULL, + stream_ordering BIGINT NOT NULL +); + +CREATE INDEX event_push_summary_user_rm ON event_push_summary(user_id, room_id); + + +-- The stream ordering up to which we have aggregated the event_push_actions +-- table into event_push_summary +CREATE TABLE event_push_summary_stream_ordering ( + Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. + stream_ordering BIGINT NOT NULL, + CHECK (Lock='X') +); + +INSERT INTO event_push_summary_stream_ordering (stream_ordering) VALUES (0); -- cgit 1.5.1 From b4017539d49b16e2d91ca8e7d312c9da4dbf3ae7 Mon Sep 17 00:00:00 2001 From: David Baker Date: Fri, 17 Feb 2017 10:42:57 +0000 Subject: Make the pushers lang field column longer To accommodate things like zh-Hans-CN Fixes https://github.com/vector-im/riot-ios/issues/1031 --- synapse/storage/schema/delta/40/pushers.sql | 39 +++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 synapse/storage/schema/delta/40/pushers.sql (limited to 'synapse/storage/schema') diff --git a/synapse/storage/schema/delta/40/pushers.sql b/synapse/storage/schema/delta/40/pushers.sql new file mode 100644 index 0000000000..7e34927efa --- /dev/null +++ b/synapse/storage/schema/delta/40/pushers.sql @@ -0,0 +1,39 @@ +/* Copyright 2017 Vector Creations 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 pushers2 ( + id BIGINT PRIMARY KEY, + user_name TEXT NOT NULL, + access_token BIGINT DEFAULT NULL, + profile_tag VARCHAR(32) NOT NULL, + kind VARCHAR(8) NOT NULL, + app_id VARCHAR(64) NOT NULL, + app_display_name VARCHAR(64) NOT NULL, + device_display_name VARCHAR(128) NOT NULL, + pushkey TEXT NOT NULL, + ts BIGINT NOT NULL, + lang VARCHAR(16), + data TEXT, + last_stream_ordering INTEGER, + last_success BIGINT, + failing_since BIGINT, + UNIQUE (app_id, pushkey, user_name) +); + +INSERT INTO pushers2 SELECT * FROM PUSHERS; + +DROP TABLE PUSHERS; + +ALTER TABLE pushers2 RENAME TO pushers; -- cgit 1.5.1 From 4aa29508af2ac9ac07f91a33d5d682b4b5815a9c Mon Sep 17 00:00:00 2001 From: David Baker Date: Fri, 17 Feb 2017 10:51:49 +0000 Subject: Use TEXT rather than VARCHAR While we're changing anyway --- synapse/storage/schema/delta/40/pushers.sql | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'synapse/storage/schema') diff --git a/synapse/storage/schema/delta/40/pushers.sql b/synapse/storage/schema/delta/40/pushers.sql index 7e34927efa..054a223f14 100644 --- a/synapse/storage/schema/delta/40/pushers.sql +++ b/synapse/storage/schema/delta/40/pushers.sql @@ -17,14 +17,14 @@ CREATE TABLE IF NOT EXISTS pushers2 ( id BIGINT PRIMARY KEY, user_name TEXT NOT NULL, access_token BIGINT DEFAULT NULL, - profile_tag VARCHAR(32) NOT NULL, - kind VARCHAR(8) NOT NULL, - app_id VARCHAR(64) NOT NULL, - app_display_name VARCHAR(64) NOT NULL, - device_display_name VARCHAR(128) NOT NULL, + profile_tag TEXT NOT NULL, + kind TEXT NOT NULL, + app_id TEXT NOT NULL, + app_display_name TEXT NOT NULL, + device_display_name TEXT NOT NULL, pushkey TEXT NOT NULL, ts BIGINT NOT NULL, - lang VARCHAR(16), + lang TEXT, data TEXT, last_stream_ordering INTEGER, last_success BIGINT, -- cgit 1.5.1 From e4919b93297031de041b55473984a2913aeb0c5d Mon Sep 17 00:00:00 2001 From: Erik Johnston Date: Tue, 28 Feb 2017 11:17:27 +0000 Subject: Add stream_id index to device_lists_outbound_pokes As this is used for replication streaming --- synapse/storage/prepare_database.py | 2 +- .../storage/schema/delta/41/device_outbound_index.sql | 16 ++++++++++++++++ 2 files changed, 17 insertions(+), 1 deletion(-) create mode 100644 synapse/storage/schema/delta/41/device_outbound_index.sql (limited to 'synapse/storage/schema') diff --git a/synapse/storage/prepare_database.py b/synapse/storage/prepare_database.py index b357f22be7..ed84db6b4b 100644 --- a/synapse/storage/prepare_database.py +++ b/synapse/storage/prepare_database.py @@ -25,7 +25,7 @@ logger = logging.getLogger(__name__) # Remember to update this number every time a change is made to database # schema files, so the users will be informed on server restarts. -SCHEMA_VERSION = 40 +SCHEMA_VERSION = 41 dir_path = os.path.abspath(os.path.dirname(__file__)) diff --git a/synapse/storage/schema/delta/41/device_outbound_index.sql b/synapse/storage/schema/delta/41/device_outbound_index.sql new file mode 100644 index 0000000000..62f0b9892b --- /dev/null +++ b/synapse/storage/schema/delta/41/device_outbound_index.sql @@ -0,0 +1,16 @@ +/* Copyright 2017 Vector Creations 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 INDEX device_lists_outbound_pokes_stream ON device_lists_outbound_pokes(stream_id); -- cgit 1.5.1 From d766343668e63a7572dcfe571d38ea3e143f3c1c Mon Sep 17 00:00:00 2001 From: Erik Johnston Date: Wed, 1 Mar 2017 15:56:30 +0000 Subject: Add index to device_lists_stream --- synapse/storage/deviceinbox.py | 8 ++++---- synapse/storage/devices.py | 7 +++++++ .../storage/schema/delta/41/device_list_stream_idx.sql | 17 +++++++++++++++++ 3 files changed, 28 insertions(+), 4 deletions(-) create mode 100644 synapse/storage/schema/delta/41/device_list_stream_idx.sql (limited to 'synapse/storage/schema') diff --git a/synapse/storage/deviceinbox.py b/synapse/storage/deviceinbox.py index bde3b5cbbc..1951de0ce1 100644 --- a/synapse/storage/deviceinbox.py +++ b/synapse/storage/deviceinbox.py @@ -31,10 +31,10 @@ class DeviceInboxStore(BackgroundUpdateStore): super(DeviceInboxStore, self).__init__(hs) self.register_background_index_update( - "device_inbox_stream_index", - index_name="device_inbox_stream_id_user_id", - table="device_inbox", - columns=["stream_id", "user_id"], + "device_lists_stream_idx", + index_name="device_lists_stream_user_id", + table="device_lists_stream", + columns=["user_id", "device_id"], ) self.register_background_update_handler( diff --git a/synapse/storage/devices.py b/synapse/storage/devices.py index f9ed18d2aa..ed659b7001 100644 --- a/synapse/storage/devices.py +++ b/synapse/storage/devices.py @@ -33,6 +33,13 @@ class DeviceStore(SQLBaseStore): self._prune_old_outbound_device_pokes, 60 * 60 * 1000 ) + self.register_background_index_update( + "device_inbox_stream_index", + index_name="device_inbox_stream_id_user_id", + table="device_inbox", + columns=["stream_id", "user_id"], + ) + @defer.inlineCallbacks def store_device(self, user_id, device_id, initial_device_display_name): diff --git a/synapse/storage/schema/delta/41/device_list_stream_idx.sql b/synapse/storage/schema/delta/41/device_list_stream_idx.sql new file mode 100644 index 0000000000..b7bee8b692 --- /dev/null +++ b/synapse/storage/schema/delta/41/device_list_stream_idx.sql @@ -0,0 +1,17 @@ +/* Copyright 2017 Vector Creations 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. + */ + +INSERT into background_updates (update_name, progress_json) + VALUES ('device_lists_stream_idx', '{}'); -- cgit 1.5.1