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
|
alter table message
rename to old_message;
alter table message_deleted
rename to old_message_deleted;
create table conversation (
id text
not null
primary key,
created_sequence bigint
unique
not null,
created_at text
not null,
last_sequence bigint
not null
);
insert into
conversation (id, created_sequence, created_at, last_sequence)
select
id,
created_sequence,
created_at,
last_sequence
from
channel;
create table conversation_name (
id text
not null
primary key
references conversation (id),
display_name
not null,
canonical_name
not null
unique
);
insert into
conversation_name (id, display_name, canonical_name)
select
id,
display_name,
canonical_name
from
channel_name;
create table conversation_deleted (
id text
not null
primary key
references conversation (id),
deleted_sequence bigint
unique
not null,
deleted_at text
not null
);
insert into
conversation_deleted (id, deleted_sequence, deleted_at)
select
id,
deleted_sequence,
deleted_at
from
channel_deleted;
create table message (
id text
not null
primary key,
conversation text
not null
references conversation (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, conversation, sender, sent_sequence, sent_at, body, last_sequence)
select
id,
channel,
sender,
sent_sequence,
sent_at,
body,
last_sequence
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 channel_deleted;
drop table channel_name;
drop table channel;
create index conversation_created_sequence
on conversation (created_sequence);
create index conversation_created_at
on conversation (created_at);
create index conversation_last_sequence
on conversation (last_sequence);
create index message_deleted_deleted_at
on message_deleted (deleted_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);
|
