1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
-- /docs/foreign-keys.md
-- 2
BEGIN TRANSACTION;
-- *** channel_room ***
-- 4
-- adding UNIQUE to room_id here will auto-generate the usable index we wanted
CREATE TABLE "new_channel_room" (
"channel_id" TEXT NOT NULL,
"room_id" TEXT NOT NULL UNIQUE,
"name" TEXT NOT NULL,
"nick" TEXT,
"thread_parent" TEXT,
"custom_avatar" TEXT,
"last_bridged_pin_timestamp" INTEGER,
"speedbump_id" TEXT,
"speedbump_checked" INTEGER,
"speedbump_webhook_id" TEXT,
"guild_id" TEXT,
PRIMARY KEY("channel_id"),
FOREIGN KEY("guild_id") REFERENCES "guild_active"("guild_id") ON DELETE CASCADE
) WITHOUT ROWID;
-- 5
INSERT INTO new_channel_room (channel_id, room_id, name, nick, thread_parent, custom_avatar, last_bridged_pin_timestamp, speedbump_id, speedbump_checked, speedbump_webhook_id, guild_id) SELECT channel_id, room_id, name, nick, thread_parent, custom_avatar, last_bridged_pin_timestamp, speedbump_id, speedbump_checked, speedbump_webhook_id, guild_id FROM channel_room;
-- 6
DROP TABLE channel_room;
-- 7
ALTER TABLE new_channel_room RENAME TO channel_room;
-- *** message_channel ***
-- 4
CREATE TABLE "new_message_channel" (
"message_id" TEXT NOT NULL,
"channel_id" TEXT NOT NULL,
PRIMARY KEY("message_id"),
FOREIGN KEY("channel_id") REFERENCES "channel_room"("channel_id") ON DELETE CASCADE
) WITHOUT ROWID;
-- 5
-- don't copy any orphaned messages
INSERT INTO new_message_channel (message_id, channel_id) SELECT message_id, channel_id FROM message_channel WHERE channel_id IN (SELECT channel_id FROM channel_room);
-- 6
DROP TABLE message_channel;
-- 7
ALTER TABLE new_message_channel RENAME TO message_channel;
-- *** event_message ***
-- clean up any orphaned events
DELETE FROM event_message WHERE message_id NOT IN (SELECT message_id FROM message_channel);
-- 4
CREATE TABLE "new_event_message" (
"event_id" TEXT NOT NULL,
"event_type" TEXT,
"event_subtype" TEXT,
"message_id" TEXT NOT NULL,
"part" INTEGER NOT NULL,
"reaction_part" INTEGER NOT NULL,
"source" INTEGER NOT NULL,
PRIMARY KEY("message_id","event_id"),
FOREIGN KEY("message_id") REFERENCES "message_channel"("message_id") ON DELETE CASCADE
) WITHOUT ROWID;
-- 5
INSERT INTO new_event_message (event_id, event_type, event_subtype, message_id, part, reaction_part, source) SELECT event_id, event_type, event_subtype, message_id, part, reaction_part, source FROM event_message;
-- 6
DROP TABLE event_message;
-- 7
ALTER TABLE new_event_message RENAME TO event_message;
-- *** guild_space ***
-- 4
CREATE TABLE "new_guild_space" (
"guild_id" TEXT NOT NULL,
"space_id" TEXT NOT NULL,
"privacy_level" INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY("guild_id"),
FOREIGN KEY("guild_id") REFERENCES "guild_active"("guild_id") ON DELETE CASCADE
) WITHOUT ROWID;
-- 5
INSERT INTO new_guild_space (guild_id, space_id, privacy_level) SELECT guild_id, space_id, privacy_level FROM guild_space;
-- 6
DROP TABLE guild_space;
-- 7
ALTER TABLE new_guild_space RENAME TO guild_space;
-- *** reaction ***
-- 4
CREATE TABLE "new_reaction" (
"hashed_event_id" INTEGER NOT NULL,
"message_id" TEXT NOT NULL,
"encoded_emoji" TEXT NOT NULL,
PRIMARY KEY("hashed_event_id"),
FOREIGN KEY("message_id") REFERENCES "message_channel"("message_id") ON DELETE CASCADE
) WITHOUT ROWID;
-- 5
INSERT INTO new_reaction (hashed_event_id, message_id, encoded_emoji) SELECT hashed_event_id, message_id, encoded_emoji FROM reaction WHERE message_id IN (SELECT message_id FROM message_channel);
-- 6
DROP TABLE reaction;
-- 7
ALTER TABLE new_reaction RENAME TO reaction;
-- *** webhook ***
-- 4
-- using RESTRICT instead of CASCADE as a reminder that the webhooks also need to be deleted using the Discord API, it can't just be entirely automatic
CREATE TABLE "new_webhook" (
"channel_id" TEXT NOT NULL,
"webhook_id" TEXT NOT NULL,
"webhook_token" TEXT NOT NULL,
PRIMARY KEY("channel_id"),
FOREIGN KEY("channel_id") REFERENCES "channel_room"("channel_id") ON DELETE RESTRICT
) WITHOUT ROWID;
-- 5
INSERT INTO new_webhook (channel_id, webhook_id, webhook_token) SELECT channel_id, webhook_id, webhook_token FROM webhook WHERE channel_id IN (SELECT channel_id FROM channel_room);
-- 6
DROP TABLE webhook;
-- 7
ALTER TABLE new_webhook RENAME TO webhook;
-- *** sim ***
-- 4
-- while we're at it, rebuild this table to give it WITHOUT ROWID, remove UNIQUE, and replace the localpart column with username. no foreign keys needed
CREATE TABLE "new_sim" (
"user_id" TEXT NOT NULL,
"username" TEXT NOT NULL,
"sim_name" TEXT NOT NULL,
"mxid" TEXT NOT NULL,
PRIMARY KEY("user_id")
) WITHOUT ROWID;
-- 5
INSERT INTO new_sim (user_id, username, sim_name, mxid) SELECT user_id, sim_name, sim_name, mxid FROM sim;
-- 6
DROP TABLE sim;
-- 7
ALTER TABLE new_sim RENAME TO sim;
-- *** end ***
-- 10
PRAGMA foreign_key_check;
-- 11
COMMIT;
|
