Merge branch 'prop/rename-login-to-user'

1 files changed, 151 insertions, 0 deletions

diff --git a/migrations/20250323190045_rename_login_to_user.sql b/migrations/20250323190045_rename_login_to_user.sql
new file mode 100644
index 0000000..7b5861d
--- /dev/null
+++ b/migrations/20250323190045_rename_login_to_user.sql
@@ -0,0 +1,151 @@
+-- message
+-- message_deleted
+
+-- Set up the new `user` table, and copy existing logins into it
+create table user
+(
+    id               text
+        not null
+        primary key,
+    display_name     text
+        not null,
+    canonical_name   text
+        not null
+        unique,
+    password_hash    text
+        not null,
+    created_sequence bigint
+        unique
+        not null,
+    created_at       text
+        not null
+);
+
+insert into user (id, display_name, canonical_name, password_hash, created_sequence, created_at)
+select id, display_name, canonical_name, password_hash, created_sequence, created_at
+from login;
+
+-- Carry the rename through `token`, preserving data…
+alter table token
+    rename to old_token;
+
+create table token
+(
+    id           text
+        not null
+        primary key,
+    secret       text
+        not null
+        unique,
+    user         text
+        not null,
+    issued_at    text
+        not null,
+    last_used_at text
+        not null,
+    foreign key (user)
+        references user (id)
+);
+
+insert into token (id, secret, user, issued_at, last_used_at)
+select id, secret, login, issued_at, last_used_at
+from old_token;
+
+-- Carry the rename through `invite`, preserving data…
+alter table invite
+    rename to old_invite;
+
+create table invite
+(
+    id        text
+        primary key
+        not null,
+    issuer    text
+        not null
+        references user (id),
+    issued_at text
+        not null
+);
+
+insert into invite (id, issuer, issued_at)
+select id, issuer, issued_at
+from old_invite;
+
+-- Carry the rename through `message`, preserving data…
+alter table message
+    rename to old_message;
+
+create table message
+(
+    id            text
+        not null
+        primary key,
+    channel       text
+        not null
+        references channel (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, channel, sender, sent_sequence, sent_at, body, last_sequence)
+select id, channel, sender, sent_sequence, sent_at, body, last_sequence
+from old_message;
+
+-- Recreating `message` entails recreating `message_deleted`
+alter table message_deleted
+    rename to old_message_deleted;
+
+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;
+
+-- Delete old tables (which will take old indices with them)
+drop table old_message_deleted;
+drop table old_message;
+drop table old_invite;
+drop table old_token;
+drop table login;
+
+-- Recreate indices
+create index message_deleted_deleted_at
+    on message_deleted (deleted_at);
+
+create index message_sent_at
+    on message (sent_at);
+create index message_channel
+    on message (channel);
+create index message_last_sequence
+    on message (last_sequence);
+
+create index invite_issued_at
+    on invite (issued_at);
+
+create index token_issued_at
+    on token (issued_at);
+create index token_last_used_at
+    on token (last_used_at);
+create index token_user
+    on token (user);