From 5af4aea1e2f143499529b70f9cf191c6994265c6 Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Mon, 23 Jun 2025 21:28:36 -0400
Subject: Rename "channel" to "conversation" in the database.

I've - somewhat arbitrarily - started renaming column aliases, as well, though the corresponding Rust data model, API fields and nouns, and client code still references them as "channel" (or as derived terms).

As with so many schema changes, this entails a complete rebuild of a substantial portion of the schema. sqlite3 still doesn't have very many `alter table` primitives, for renaming columns in particular.
---
 ...250624003106_rename_channel_to_conversation.sql | 153 +++++++++++++++++++++
 1 file changed, 153 insertions(+)
 create mode 100644 migrations/20250624003106_rename_channel_to_conversation.sql

(limited to 'migrations')

diff --git a/migrations/20250624003106_rename_channel_to_conversation.sql b/migrations/20250624003106_rename_channel_to_conversation.sql
new file mode 100644
index 0000000..753d42b
--- /dev/null
+++ b/migrations/20250624003106_rename_channel_to_conversation.sql
@@ -0,0 +1,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);
-- 
cgit v1.2.3