1 files changed, 189 insertions, 0 deletions
diff --git a/migrations/20241019191531_canonical_names.sql b/migrations/20241019191531_canonical_names.sql
new file mode 100644
index 0000000..ab7cbf4
--- /dev/null
+++ b/migrations/20241019191531_canonical_names.sql
@@ -0,0 +1,189 @@
+alter table login
+rename to old_login;
+alter table token
+rename to old_token;
+alter table invite
+rename to old_invite;
+alter table channel
+rename to old_channel;
+alter table channel_deleted
+rename to old_channel_deleted;
+alter table message
+rename to old_message;
+alter table message_deleted
+rename to old_message_deleted;
+
+create table login (
+	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 login (id, display_name, canonical_name, password_hash, created_sequence, created_at)
+select
+	id,
+	-- This isn't strictly correct, as existing names are not guaranteed to be
+	-- normalized, and as sqlite's built-in `lower` only operates on ASCII, but
+	-- without any way to do case folding and normalization in sqlite3, and
+	-- without any way to call out into Rust to do the work, this is the best we
+	-- can do. Normalization issues will produce errors at runtime when the
+	-- relevant rows are loaded.
+	name as display_name,
+	lower(name) as canonical_name,
+	password_hash,
+	created_sequence,
+	created_at
+from old_login;
+
+create table token (
+	id text
+		not null
+		primary key,
+	secret text
+		not null
+		unique,
+	login text
+		not null
+		references login (id),
+	issued_at text
+		not null,
+	last_used_at text
+		not null
+);
+
+insert into token (id, secret, login, issued_at, last_used_at)
+select id, secret, login, issued_at, last_used_at from old_token;
+
+create table invite (
+	id text
+		primary key
+		not null,
+	issuer text
+		not null
+		references login (id),
+	issued_at text
+		not null
+);
+
+insert into invite (id, issuer, issued_at)
+select id, issuer, issued_at
+from old_invite;
+
+create table channel (
+	id text
+		not null
+		primary key,
+	created_sequence bigint
+		unique
+		not null,
+	created_at text
+		not null
+);
+
+create table channel_name (
+	id text
+		not null
+		primary key
+		references channel (id),
+	display_name
+		not null,
+	canonical_name
+		not null
+		unique
+);
+
+insert into channel (id, created_sequence, created_at)
+select id, created_sequence, created_at from old_channel;
+
+insert into channel_name (id, display_name, canonical_name)
+select
+	id,
+	-- This isn't strictly correct, for the same reasons as above.
+	name as display_name,
+	name as canonical_name
+from old_channel
+where name is not null;
+
+create table channel_deleted (
+	id text
+		not null
+		primary key
+		references channel (id),
+	deleted_sequence bigint
+		unique
+		not null,
+	deleted_at text
+		not null
+);
+
+insert into channel_deleted (id, deleted_sequence, deleted_at)
+select id, deleted_sequence, deleted_at
+from old_channel_deleted;
+
+create table message (
+	id text
+		not null
+		primary key,
+	-- Starting from the code changes in this rev, values in this column will be
+	-- in NFC. However, we don't actually need to normalize historical values;
+	-- they're delivered to clients "verbatim" with respect to how they were sent,
+	-- which causes no harm.
+	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
+		null
+);
+
+insert into message (id, channel, sender, sent_sequence, sent_at, body)
+select id, channel, sender, sent_sequence, sent_at, body
+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 old_channel_deleted;
+drop table old_channel;
+drop table old_invite;
+drop table old_token;
+drop table old_login;
+
+create index token_issued_at
+on token (issued_at);
+create index message_sent_at
+on message (sent_at);