Add upgrade path, rename table

1 files changed, 17 insertions, 3 deletions

diff --git a/synapse/storage/schema/delta/31/invites.sql b/synapse/storage/schema/delta/31/invites.sql

index 4f6fb9ea63..1c83430da4 100644
--- a/synapse/storage/schema/delta/31/invites.sql
+++ b/synapse/storage/schema/delta/31/invites.sql
@@ -14,7 +14,7 @@
  */
 
 
-CREATE TABLE invites(
+CREATE TABLE local_invites(
     stream_id BIGINT NOT NULL,
     inviter TEXT NOT NULL,
     invitee TEXT NOT NULL,
@@ -24,5 +24,19 @@ CREATE TABLE invites(
     replaced_by TEXT
 );
 
-CREATE INDEX invites_id ON invites(stream_id);
-CREATE INDEX invites_for_user_idx ON invites(invitee, locally_rejected, replaced_by, room_id);
+-- Insert all invites for local users into new `invites` table
+INSERT INTO local_invites SELECT
+    stream_ordering as stream_id,
+    sender as inviter,
+    state_key as invitee,
+    event_id,
+    room_id,
+    NULL as locally_rejected,
+    NULL as replaced_by
+FROM events
+NATURAL JOIN current_state_events
+NATURAL JOIN room_memberships
+WHERE membership = 'invite'  AND state_key IN (SELECT name FROM users);
+
+CREATE INDEX local_invites_id ON local_invites(stream_id);
+CREATE INDEX local_invites_for_user_idx ON local_invites(invitee, locally_rejected, replaced_by, room_id);