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
190
191
192
193
194
195
|
-- First, migrate the table we're actually interested in - `user`. This splits it into two parts:
--
-- * login, which is concerned with authentication data (and is non-synchronized,
-- though at the DB that doesn't matter much), and
-- * user, which is concerned with chat data (and is synchronized, ditto).
alter table user
rename to old_user;
create table login (
id text
not null
primary key,
display_name text
not null,
canonical_name text
not null
unique,
password text
not null
);
insert into
login (id, display_name, canonical_name, password)
select
id,
display_name,
canonical_name,
password_hash as password
from
old_user;
create table user (
id text
not null
primary key
references login (id),
created_at text
not null,
created_sequence bigint
not null
unique
);
insert into
user (id, created_at, created_sequence)
select
id,
created_at,
created_sequence
from
old_user;
-- Now, recreate the entire rest of the owl. Everything that referenced the original `user` table _except tokens_
-- references the user table. Tokens, which are authentication data, reference the login (authn) table.
alter table token
rename to old_token;
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,
user as login,
issued_at,
last_used_at
from
old_token;
alter table invite
rename to old_invite;
create table invite (
id text
primary key
not null,
issuer text
not null
references user (id),
issued_at text
not null
);
insert into
invite (id, issuer, issued_at)
select
id,
issuer,
issued_at
from
old_invite;
alter table message
rename to old_message;
create table message (
id text
not null
primary key,
conversation text
not null
references conversation (id),
sender text
not null
references user (id),
body text
null,
sent_at text
not null,
sent_sequence bigint
unique
not null,
last_sequence bigint
not null
);
insert into
message (id, conversation, sender, body, sent_at, sent_sequence, last_sequence)
select
id,
conversation,
sender,
body,
sent_at,
sent_sequence,
last_sequence
from
old_message;
alter table message_deleted
rename to old_message_deleted;
create table message_deleted (
id text
not null
primary key
references message (id),
deleted_at text
not null,
deleted_sequence bigint
unique
not null
);
insert into
message_deleted (id, deleted_at, deleted_sequence)
select
id,
deleted_at,
deleted_sequence
from
message_deleted;
drop table old_message_deleted;
drop table old_message;
drop table old_invite;
drop table old_token;
drop table old_user;
create index token_issued_at
on token (issued_at);
create index token_last_used_at
on token (last_used_at);
create index token_login
on token (login);
create index invite_issued_at
on invite (issued_at);
create index message_sent_at
on message (sent_at);
create index message_conversation
on message (conversation);
create index message_last_sequence
on message (last_sequence);
create index message_deleted_deleted_at
on message_deleted (deleted_at);
|
