From 08c3a6e77a3f61ffc9643a5e1f840df9078d0b36 Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Fri, 27 Sep 2024 21:25:15 -0400
Subject: Assign sequence numbers from a counter, not by scanning messages

---
 .../20240928012031_channel_stored_sequence.sql     | 60 ++++++++++++++++++++++
 1 file changed, 60 insertions(+)
 create mode 100644 migrations/20240928012031_channel_stored_sequence.sql

(limited to 'migrations')

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;
-- 
cgit v1.2.3