From b422be184e01b4cc35b9c9a6921379080c24edb3 Mon Sep 17 00:00:00 2001 From: Owen Jacobson Date: Fri, 4 Oct 2024 22:12:15 -0400 Subject: Start fresh with database migrations. The migration path from the original project inception to now was complicated and buggy, and stranded _both_ Kit and I with broken databases due to oversights and incomplete migrations. We've agreed to start fresh, once. If this is mistakenly started with an original-schema-flavour DB, startup will be aborted. --- migrations/20240831024047_login.sql | 22 ---- .../20240904135137_index_token_issued_at.sql | 1 - migrations/20240904153315_channel.sql | 20 ---- .../20240911230415_no_channel_membership.sql | 1 - migrations/20240912013151_token_last_used.sql | 6 - migrations/20240912145249_message.sql | 17 --- .../20240924232919_message_serial_numbers.sql | 38 ------- migrations/20240928002608_channel_lifecycle.sql | 57 ---------- .../20240928012031_channel_stored_sequence.sql | 60 ---------- migrations/20240929013644_token_id.sql | 29 ----- migrations/20241002003606_global_sequence.sql | 126 --------------------- migrations/20241005020942_login.sql | 10 ++ migrations/20241005020958_token.sql | 19 ++++ migrations/20241005021009_channel.sql | 16 +++ migrations/20241005021022_message.sql | 22 ++++ migrations/20241005021100_event_sequence.sql | 10 ++ 16 files changed, 77 insertions(+), 377 deletions(-) delete mode 100644 migrations/20240831024047_login.sql delete mode 100644 migrations/20240904135137_index_token_issued_at.sql delete mode 100644 migrations/20240904153315_channel.sql delete mode 100644 migrations/20240911230415_no_channel_membership.sql delete mode 100644 migrations/20240912013151_token_last_used.sql delete mode 100644 migrations/20240912145249_message.sql delete mode 100644 migrations/20240924232919_message_serial_numbers.sql delete mode 100644 migrations/20240928002608_channel_lifecycle.sql delete mode 100644 migrations/20240928012031_channel_stored_sequence.sql delete mode 100644 migrations/20240929013644_token_id.sql delete mode 100644 migrations/20241002003606_global_sequence.sql create mode 100644 migrations/20241005020942_login.sql create mode 100644 migrations/20241005020958_token.sql create mode 100644 migrations/20241005021009_channel.sql create mode 100644 migrations/20241005021022_message.sql create mode 100644 migrations/20241005021100_event_sequence.sql (limited to 'migrations') diff --git a/migrations/20240831024047_login.sql b/migrations/20240831024047_login.sql deleted file mode 100644 index 758e9c7..0000000 --- a/migrations/20240831024047_login.sql +++ /dev/null @@ -1,22 +0,0 @@ -create table login ( - id text - not null - primary key, - name text - not null - unique, - password_hash text - not null -); - -create table token ( - secret text - not null - primary key, - login text - not null, - issued_at text - not null, - foreign key (login) - references login (id) -); diff --git a/migrations/20240904135137_index_token_issued_at.sql b/migrations/20240904135137_index_token_issued_at.sql deleted file mode 100644 index b60f691..0000000 --- a/migrations/20240904135137_index_token_issued_at.sql +++ /dev/null @@ -1 +0,0 @@ -create index token_issued_at on token (issued_at); diff --git a/migrations/20240904153315_channel.sql b/migrations/20240904153315_channel.sql deleted file mode 100644 index e62b51f..0000000 --- a/migrations/20240904153315_channel.sql +++ /dev/null @@ -1,20 +0,0 @@ -create table channel ( - id text - not null - primary key, - name text - not null - unique -); - -create table channel_member ( - channel text - not null - references channel, - login text - not null - references login, - primary key (channel, login) -); - -create index channel_member_login on channel_member (login); diff --git a/migrations/20240911230415_no_channel_membership.sql b/migrations/20240911230415_no_channel_membership.sql deleted file mode 100644 index db5a054..0000000 --- a/migrations/20240911230415_no_channel_membership.sql +++ /dev/null @@ -1 +0,0 @@ -drop table channel_member; diff --git a/migrations/20240912013151_token_last_used.sql b/migrations/20240912013151_token_last_used.sql deleted file mode 100644 index 0b45cd9..0000000 --- a/migrations/20240912013151_token_last_used.sql +++ /dev/null @@ -1,6 +0,0 @@ -alter table token -add column last_used_at text - not null; - -update token -set last_used_at = issued_at; diff --git a/migrations/20240912145249_message.sql b/migrations/20240912145249_message.sql deleted file mode 100644 index ce9db0d..0000000 --- a/migrations/20240912145249_message.sql +++ /dev/null @@ -1,17 +0,0 @@ -create table message ( - id text - not null - primary key, - channel text - not null - references channel (id), - sender text - not null - references login (id), - body text - not null, - sent_at text - not null -); - -create index message_sent_at on message (channel, sent_at); diff --git a/migrations/20240924232919_message_serial_numbers.sql b/migrations/20240924232919_message_serial_numbers.sql deleted file mode 100644 index a53e4a2..0000000 --- a/migrations/20240924232919_message_serial_numbers.sql +++ /dev/null @@ -1,38 +0,0 @@ -create table sequenced_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 sequenced_message -select - id, - rank() over ( - partition by channel - order by sent_at - ) as sequence, - channel, - sender, - body, - sent_at -from message; - -drop table message; - -alter table sequenced_message -rename to message; - -create index message_sent_at on message (channel, sent_at); diff --git a/migrations/20240928002608_channel_lifecycle.sql b/migrations/20240928002608_channel_lifecycle.sql deleted file mode 100644 index bc690d7..0000000 --- a/migrations/20240928002608_channel_lifecycle.sql +++ /dev/null @@ -1,57 +0,0 @@ -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 -); - --- Transfer data from original table -insert into channel -select - channel.id, - channel.name, - coalesce( - min(message.sent_at), - strftime('%FT%R:%f+00:00', 'now', 'utc') - ) as created_at -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; diff --git a/migrations/20240928012031_channel_stored_sequence.sql b/migrations/20240928012031_channel_stored_sequence.sql deleted file mode 100644 index badd88d..0000000 --- a/migrations/20240928012031_channel_stored_sequence.sql +++ /dev/null @@ -1,60 +0,0 @@ -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; diff --git a/migrations/20240929013644_token_id.sql b/migrations/20240929013644_token_id.sql deleted file mode 100644 index ad4d8b4..0000000 --- a/migrations/20240929013644_token_id.sql +++ /dev/null @@ -1,29 +0,0 @@ -alter table token -rename to old_token; - -create table token ( - id text - not null - primary key, - secret text - not null - unique, - login text - not null, - issued_at text - not null, - last_used_at text - not null, - foreign key (login) - references login (id) -); - -insert into token -select - 'T' || lower(hex(randomblob(8))) as id, - * -from old_token; - -drop table old_token; - -create index token_issued_at on token (issued_at); diff --git a/migrations/20241002003606_global_sequence.sql b/migrations/20241002003606_global_sequence.sql deleted file mode 100644 index 198b585..0000000 --- a/migrations/20241002003606_global_sequence.sql +++ /dev/null @@ -1,126 +0,0 @@ -create table event_sequence ( - last_value bigint - not null -); - -create unique index event_sequence_singleton -on event_sequence (0); - --- Attempt to assign events sent so far a globally-unique sequence number, --- maintaining an approximation of the order they were sent in. This can --- introduce small ordering anomalies (where the resulting sequence differs --- from the order they were sent in) for events that were sent close in time; --- I've gone with chronological order here as it's the closest thing we have to --- a global ordering, and because the results will be intuitive to most users. -create temporary table raw_event ( - type text - not null, - at text - not null, - channel text - unique, - message text - unique, - check ((channel is not null and message is null) or (message is not null and channel is null)) -); - -insert into raw_event (type, at, channel) -select - 'channel' as type, - created_at as at, - id as channel -from channel; - -insert into raw_event (type, at, message) -select - 'message' as type, - sent_at as at, - id as message -from message; - -create temporary table event ( - type text - not null, - sequence - unique - not null, - at text - not null, - channel text - unique, - message text - unique, - check ((channel is not null and message is null) or (message is not null and channel is null)) -); - -insert into event -select - type, - rank() over (order by at) - 1 as sequence, - at, - channel, - message -from raw_event; - -drop table raw_event; - -alter table channel rename to old_channel; -alter table message rename to old_message; - -create table channel ( - id text - not null - primary key, - name text - unique - not null, - created_sequence bigint - unique - not null, - created_at text - not null -); - -insert into channel -select - c.id, - c.name, - e.sequence, - c.created_at -from old_channel as c join event as e - on e.channel = c.id; - -create table message ( - id text - not null - primary key, - 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 - not null -); - -insert into message -select - m.id, - m.channel, - m.sender, - e.sequence, - m.sent_at, - m.body -from old_message as m join event as e - on e.message = m.id; - -insert into event_sequence -select coalesce(max(sequence), 0) from event; - -drop table event; diff --git a/migrations/20241005020942_login.sql b/migrations/20241005020942_login.sql new file mode 100644 index 0000000..92d0875 --- /dev/null +++ b/migrations/20241005020942_login.sql @@ -0,0 +1,10 @@ +create table login ( + id text + not null + primary key, + name text + not null + unique, + password_hash text + not null +); diff --git a/migrations/20241005020958_token.sql b/migrations/20241005020958_token.sql new file mode 100644 index 0000000..046638a --- /dev/null +++ b/migrations/20241005020958_token.sql @@ -0,0 +1,19 @@ +create table token ( + id text + not null + primary key, + secret text + not null + unique, + login text + not null, + issued_at text + not null, + last_used_at text + not null, + foreign key (login) + references login (id) +); + +create index token_issued_at +on token (issued_at); diff --git a/migrations/20241005021009_channel.sql b/migrations/20241005021009_channel.sql new file mode 100644 index 0000000..ac36884 --- /dev/null +++ b/migrations/20241005021009_channel.sql @@ -0,0 +1,16 @@ +create table channel ( + id text + not null + primary key, + name text + unique + not null, + created_sequence bigint + unique + not null, + created_at text + not null +); + +create index channel_created_at +on channel (created_at); diff --git a/migrations/20241005021022_message.sql b/migrations/20241005021022_message.sql new file mode 100644 index 0000000..189612c --- /dev/null +++ b/migrations/20241005021022_message.sql @@ -0,0 +1,22 @@ +create table message ( + id text + not null + primary key, + 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 + not null +); + +create index message_sent_at +on message (sent_at); + diff --git a/migrations/20241005021100_event_sequence.sql b/migrations/20241005021100_event_sequence.sql new file mode 100644 index 0000000..ac6821d --- /dev/null +++ b/migrations/20241005021100_event_sequence.sql @@ -0,0 +1,10 @@ +create table event_sequence ( + last_value bigint + not null +); + +create unique index event_sequence_singleton +on event_sequence (0); + +insert into event_sequence +values (0); -- cgit v1.2.3