diff options
4 files changed, 90 insertions, 30 deletions
diff --git a/.sqlx/query-397bdfdb77651e3e65e9ec53cf075037c794cae08f79a689c7a037aa68d7c00c.json b/.sqlx/query-397bdfdb77651e3e65e9ec53cf075037c794cae08f79a689c7a037aa68d7c00c.json new file mode 100644 index 0000000..5cb7282 --- /dev/null +++ b/.sqlx/query-397bdfdb77651e3e65e9ec53cf075037c794cae08f79a689c7a037aa68d7c00c.json @@ -0,0 +1,20 @@ +{ + "db_name": "SQLite", + "query": "\n update channel\n set last_sequence = last_sequence + 1\n where id = $1\n returning last_sequence as \"next_sequence: Sequence\"\n ", + "describe": { + "columns": [ + { + "name": "next_sequence: Sequence", + "ordinal": 0, + "type_info": "Integer" + } + ], + "parameters": { + "Right": 1 + }, + "nullable": [ + false + ] + }, + "hash": "397bdfdb77651e3e65e9ec53cf075037c794cae08f79a689c7a037aa68d7c00c" +} diff --git a/.sqlx/query-54fe04ade0a01cfd0f3ddbedfa884d8a00f692eba656d3daa8119011b703cfcd.json b/.sqlx/query-54fe04ade0a01cfd0f3ddbedfa884d8a00f692eba656d3daa8119011b703cfcd.json deleted file mode 100644 index a739207..0000000 --- a/.sqlx/query-54fe04ade0a01cfd0f3ddbedfa884d8a00f692eba656d3daa8119011b703cfcd.json +++ /dev/null @@ -1,20 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n -- `max` never returns null, but sqlx can't detect that\n select max(sequence) as \"sequence!: Sequence\"\n from message\n where channel = $1\n ", - "describe": { - "columns": [ - { - "name": "sequence!: Sequence", - "ordinal": 0, - "type_info": "Null" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - true - ] - }, - "hash": "54fe04ade0a01cfd0f3ddbedfa884d8a00f692eba656d3daa8119011b703cfcd" -} diff --git a/migrations/20240928012031_channel_stored_sequence.sql b/migrations/20240928012031_channel_stored_sequence.sql new file mode 100644 index 0000000..badd88d --- /dev/null +++ b/migrations/20240928012031_channel_stored_sequence.sql @@ -0,0 +1,60 @@ +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/src/events/repo/message.rs b/src/events/repo/message.rs index ec17d55..f6fce0e 100644 --- a/src/events/repo/message.rs +++ b/src/events/repo/message.rs @@ -4,7 +4,7 @@ use crate::{ clock::DateTime, events::types::{self, Sequence}, repo::{ - channel::Channel, + channel::{self, Channel}, login::{self, Login}, message, }, @@ -30,7 +30,7 @@ impl<'c> Events<'c> { body: &str, sent_at: &DateTime, ) -> Result<types::ChannelEvent, sqlx::Error> { - let sequence = self.next_sequence_for(channel).await?; + let sequence = self.assign_sequence(&channel.id).await?; let id = message::Id::generate(); @@ -72,20 +72,20 @@ impl<'c> Events<'c> { Ok(message) } - async fn next_sequence_for(&mut self, channel: &Channel) -> Result<Sequence, sqlx::Error> { - let current = sqlx::query_scalar!( + async fn assign_sequence(&mut self, channel: &channel::Id) -> Result<Sequence, sqlx::Error> { + let next = sqlx::query_scalar!( r#" - -- `max` never returns null, but sqlx can't detect that - select max(sequence) as "sequence!: Sequence" - from message - where channel = $1 + update channel + set last_sequence = last_sequence + 1 + where id = $1 + returning last_sequence as "next_sequence: Sequence" "#, - channel.id, + channel, ) .fetch_one(&mut *self.0) .await?; - Ok(current.next()) + Ok(next) } pub async fn expire(&mut self, expire_at: &DateTime) -> Result<(), sqlx::Error> { |