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
|
create table event_sequence (
last_value bigint
not null
);
create unique index event_sequence_singleton
on event_sequence (0);
-- Attempt to assign events sent so far a globally-unique sequence number,
-- maintaining an approximation of the order they were sent in. This can
-- introduce small ordering anomalies (where the resulting sequence differs
-- from the order they were sent in) for events that were sent close in time;
-- I've gone with chronological order here as it's the closest thing we have to
-- a global ordering, and because the results will be intuitive to most users.
create temporary table raw_event (
type text
not null,
at text
not null,
channel text
unique,
message text
unique,
check ((channel is not null and message is null) or (message is not null and channel is null))
);
insert into raw_event (type, at, channel)
select
'channel' as type,
created_at as at,
id as channel
from channel;
insert into raw_event (type, at, message)
select
'message' as type,
sent_at as at,
id as message
from message;
create temporary table event (
type text
not null,
sequence
unique
not null,
at text
not null,
channel text
unique,
message text
unique,
check ((channel is not null and message is null) or (message is not null and channel is null))
);
insert into event
select
type,
rank() over (order by at) - 1 as sequence,
at,
channel,
message
from raw_event;
drop table raw_event;
alter table channel rename to old_channel;
alter table message rename to old_message;
create table channel (
id text
not null
primary key,
name text
unique
not null,
created_sequence bigint
unique
not null,
created_at text
not null
);
insert into channel
select
c.id,
c.name,
e.sequence,
c.created_at
from old_channel as c join event as e
on e.channel = c.id;
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
not null
);
insert into message
select
m.id,
m.channel,
m.sender,
e.sequence,
m.sent_at,
m.body
from old_message as m join event as e
on e.message = m.id;
insert into event_sequence
select coalesce(max(sequence), 0) from event;
drop table event;
|
