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
|
-- message
-- message_deleted
-- Set up the new `user` table, and copy existing logins into it
create table user
(
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 user (id, display_name, canonical_name, password_hash, created_sequence, created_at)
select id, display_name, canonical_name, password_hash, created_sequence, created_at
from login;
-- Carry the rename through `token`, preserving data…
alter table token
rename to old_token;
create table token
(
id text
not null
primary key,
secret text
not null
unique,
user text
not null,
issued_at text
not null,
last_used_at text
not null,
foreign key (user)
references user (id)
);
insert into token (id, secret, user, issued_at, last_used_at)
select id, secret, login, issued_at, last_used_at
from old_token;
-- Carry the rename through `invite`, preserving data…
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;
-- Carry the rename through `message`, preserving data…
alter table message
rename to old_message;
create table message
(
id text
not null
primary key,
channel text
not null
references channel (id),
sender text
not null
references user (id),
sent_sequence bigint
unique
not null,
sent_at text
not null,
body text
null,
last_sequence bigint
not null
);
insert into message (id, channel, sender, sent_sequence, sent_at, body, last_sequence)
select id, channel, sender, sent_sequence, sent_at, body, last_sequence
from old_message;
-- Recreating `message` entails recreating `message_deleted`
alter table message_deleted
rename to old_message_deleted;
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;
-- Delete old tables (which will take old indices with them)
drop table old_message_deleted;
drop table old_message;
drop table old_invite;
drop table old_token;
drop table login;
-- Recreate indices
create index message_deleted_deleted_at
on message_deleted (deleted_at);
create index message_sent_at
on message (sent_at);
create index message_channel
on message (channel);
create index message_last_sequence
on message (last_sequence);
create index invite_issued_at
on invite (issued_at);
create index token_issued_at
on token (issued_at);
create index token_last_used_at
on token (last_used_at);
create index token_user
on token (user);
|
