diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/20240904135137_index_token_issued_at.sql | 1 | ||||
| -rw-r--r-- | migrations/20240904153315_channel.sql | 20 | ||||
| -rw-r--r-- | migrations/20240911230415_no_channel_membership.sql | 1 | ||||
| -rw-r--r-- | migrations/20240912013151_token_last_used.sql | 6 | ||||
| -rw-r--r-- | migrations/20240924232919_message_serial_numbers.sql | 38 | ||||
| -rw-r--r-- | migrations/20240928002608_channel_lifecycle.sql | 57 | ||||
| -rw-r--r-- | migrations/20240928012031_channel_stored_sequence.sql | 60 | ||||
| -rw-r--r-- | migrations/20240929013644_token_id.sql | 29 | ||||
| -rw-r--r-- | migrations/20241002003606_global_sequence.sql | 126 | ||||
| -rw-r--r-- | migrations/20241005020942_login.sql | 10 | ||||
| -rw-r--r-- | migrations/20241005020958_token.sql (renamed from migrations/20240831024047_login.sql) | 17 | ||||
| -rw-r--r-- | migrations/20241005021009_channel.sql | 16 | ||||
| -rw-r--r-- | migrations/20241005021022_message.sql (renamed from migrations/20240912145249_message.sql) | 9 | ||||
| -rw-r--r-- | migrations/20241005021100_event_sequence.sql | 10 |
14 files changed, 50 insertions, 350 deletions
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/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/20240831024047_login.sql b/migrations/20241005020958_token.sql index 758e9c7..046638a 100644 --- a/migrations/20240831024047_login.sql +++ b/migrations/20241005020958_token.sql @@ -1,22 +1,19 @@ -create table login ( +create table token ( 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, + 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/20240912145249_message.sql b/migrations/20241005021022_message.sql index ce9db0d..189612c 100644 --- a/migrations/20240912145249_message.sql +++ b/migrations/20241005021022_message.sql @@ -8,10 +8,15 @@ create table message ( sender text not null references login (id), - body text + sent_sequence bigint + unique not null, sent_at text + not null, + body text not null ); -create index message_sent_at on message (channel, sent_at); +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); |