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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
alter table login
rename to old_login;
alter table token
rename to old_token;
alter table invite
rename to old_invite;
alter table channel
rename to old_channel;
alter table channel_deleted
rename to old_channel_deleted;
alter table message
rename to old_message;
alter table message_deleted
rename to old_message_deleted;
create table login (
id text
not null
primary key,
display_name text
not null,
canonical_name text
not null
unique,
password_hash text
not null,
created_sequence bigint
unique
not null,
created_at text
not null
);
insert into login (id, display_name, canonical_name, password_hash, created_sequence, created_at)
select
id,
-- This isn't strictly correct, as existing names are not guaranteed to be
-- normalized, and as sqlite's built-in `lower` only operates on ASCII, but
-- without any way to do case folding and normalization in sqlite3, and
-- without any way to call out into Rust to do the work, this is the best we
-- can do. Normalization issues will produce errors at runtime when the
-- relevant rows are loaded.
name as display_name,
lower(name) as canonical_name,
password_hash,
created_sequence,
created_at
from old_login;
create table token (
id text
not null
primary key,
secret text
not null
unique,
login text
not null
references login (id),
issued_at text
not null,
last_used_at text
not null
);
insert into token (id, secret, login, issued_at, last_used_at)
select id, secret, login, issued_at, last_used_at from old_token;
create table invite (
id text
primary key
not null,
issuer text
not null
references login (id),
issued_at text
not null
);
insert into invite (id, issuer, issued_at)
select id, issuer, issued_at
from old_invite;
create table channel (
id text
not null
primary key,
created_sequence bigint
unique
not null,
created_at text
not null
);
create table channel_name (
id text
not null
primary key
references channel (id),
display_name
not null,
canonical_name
not null
unique
);
insert into channel (id, created_sequence, created_at)
select id, created_sequence, created_at from old_channel;
insert into channel_name (id, display_name, canonical_name)
select
id,
-- This isn't strictly correct, for the same reasons as above.
name as display_name,
name as canonical_name
from old_channel
where name is not null;
create table channel_deleted (
id text
not null
primary key
references channel (id),
deleted_sequence bigint
unique
not null,
deleted_at text
not null
);
insert into channel_deleted (id, deleted_sequence, deleted_at)
select id, deleted_sequence, deleted_at
from old_channel_deleted;
create table message (
id text
not null
primary key,
-- Starting from the code changes in this rev, values in this column will be
-- in NFC. However, we don't actually need to normalize historical values;
-- they're delivered to clients "verbatim" with respect to how they were sent,
-- which causes no harm.
channel text
not null
references channel (id),
sender text
not null
references login (id),
sent_sequence bigint
unique
not null,
sent_at text
not null,
body text
null
);
insert into message (id, channel, sender, sent_sequence, sent_at, body)
select id, channel, sender, sent_sequence, sent_at, body
from old_message;
create table message_deleted (
id text
not null
primary key
references message (id),
deleted_sequence bigint
unique
not null,
deleted_at text
not null
);
insert into message_deleted (id, deleted_sequence, deleted_at)
select id, deleted_sequence, deleted_at
from old_message_deleted;
drop table old_message_deleted;
drop table old_message;
drop table old_channel_deleted;
drop table old_channel;
drop table old_invite;
drop table old_token;
drop table old_login;
create index token_issued_at
on token (issued_at);
create index message_sent_at
on message (sent_at);
|
