Assign sequence numbers from a counter, not by scanning messages

1 files changed, 60 insertions, 0 deletions

diff --git a/migrations/20240928012031_channel_stored_sequence.sql b/migrations/20240928012031_channel_stored_sequence.sql
new file mode 100644
index 0000000..badd88d
--- /dev/null
+++ b/migrations/20240928012031_channel_stored_sequence.sql
@@ -0,0 +1,60 @@
+alter table channel
+rename to old_channel;
+
+-- Add new columns
+create table channel (
+	id text
+		not null
+		primary key,
+	name text
+		not null
+		unique,
+	created_at text
+		not null,
+	last_sequence bigint
+		not null
+);
+
+-- Transfer data from original table
+insert into channel
+select
+	channel.id,
+	channel.name,
+	channel.created_at,
+	coalesce(
+		max(message.sequence),
+		0
+	) as last_sequence
+from old_channel as channel
+	left join message on channel.id = message.channel
+group by channel.id, channel.name;
+
+-- Fix up `message` foreign keys
+alter table message
+rename to old_message;
+
+create table message (
+	id text
+		not null
+		primary key,
+	sequence bigint
+		not null,
+	channel text
+		not null
+		references channel (id),
+	sender text
+		not null
+		references login (id),
+	body text
+		not null,
+	sent_at text
+		not null,
+	unique (channel, sequence)
+);
+
+insert into message
+select * from old_message;
+
+-- Bury the bodies respectfully
+drop table old_message;
+drop table old_channel;