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
|
alter table channel
rename to old_channel;
alter table channel_name
rename to old_channel_name;
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 channel (
id text
not null
primary key,
created_sequence bigint
unique
not null,
created_at text
not null,
last_sequence bigint
not null
);
insert into channel (id, created_sequence, created_at, last_sequence)
select
ch.id,
ch.created_sequence,
ch.created_at,
max(ch.created_sequence, coalesce(del.deleted_sequence, ch.created_sequence)) as last_seqeuence
from old_channel as ch
left join old_channel_deleted as del
using (id);
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_name (id, display_name, canonical_name)
select id, display_name, canonical_name
from old_channel_name;
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,
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,
last_sequence bigint
not null
);
insert into message (id, channel, sender, sent_sequence, sent_at, body, last_sequence)
select
msg.id,
msg.channel,
msg.sender,
msg.sent_sequence,
msg.sent_at,
msg.body,
max(msg.sent_sequence, coalesce(del.deleted_sequence, msg.sent_sequence)) as last_sequence
from
old_message as msg
left join old_message_deleted as del
using (id);
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_name;
drop table old_channel;
-- recreate existing indices
create index message_sent_at
on message (sent_at);
create index message_deleted_deleted_at
on message_deleted (deleted_at);
create index message_channel
on message(channel);
create index channel_created_sequence
on channel (created_sequence);
create index channel_created_at
on channel (created_at);
-- new indices
create index channel_last_sequence
on channel (last_sequence);
create index message_last_sequence
on message (last_sequence);
|
