From 3f9648eed48cd8b6cd35d0ae2ee5bbe25fa735ac Mon Sep 17 00:00:00 2001 From: Owen Jacobson Date: Mon, 21 Oct 2024 00:36:44 -0400 Subject: Canonicalize login and channel names. Canonicalization does two things: * It prevents duplicate names that differ only by case or only by normalization/encoding sequence; and * It makes certain name-based comparisons "case-insensitive" (generalizing via Unicode's case-folding rules). This change is complicated, as it means that every name now needs to be stored in two forms. Unfortunately, this is _very likely_ a breaking schema change. The migrations in this commit perform a best-effort attempt to canonicalize existing channel or login names, but it's likely any existing channels or logins with non-ASCII characters will not be canonicalize correctly. Since clients look at all channel names and all login names on boot, and since the code in this commit verifies canonicalization when reading from the database, this will effectively make the server un-usuable until any incorrectly-canonicalized values are either manually canonicalized, or removed It might be possible to do better with [the `icu` sqlite3 extension][icu], but (a) I'm not convinced of that and (b) this commit is already huge; adding database extension support would make it far larger. [icu]: https://sqlite.org/src/dir/ext/icu For some references on why it's worth storing usernames this way, see and the refernced talk, as well as . Bennett's treatment of this issue is, to my eye, much more readable than the referenced Unicode technical reports, and I'm inclined to trust his opinion given that he maintains a widely-used, internet-facing user registration library for Django. --- src/boot/app.rs | 34 ++++++++++++++++++++++++++++++++-- 1 file changed, 32 insertions(+), 2 deletions(-) (limited to 'src/boot/app.rs') diff --git a/src/boot/app.rs b/src/boot/app.rs index ef48b2f..1d88608 100644 --- a/src/boot/app.rs +++ b/src/boot/app.rs @@ -2,8 +2,11 @@ use sqlx::sqlite::SqlitePool; use super::Snapshot; use crate::{ - channel::repo::Provider as _, event::repo::Provider as _, login::repo::Provider as _, + channel::{self, repo::Provider as _}, + event::repo::Provider as _, + login::{self, repo::Provider as _}, message::repo::Provider as _, + name, }; pub struct Boot<'a> { @@ -15,7 +18,7 @@ impl<'a> Boot<'a> { Self { db } } - pub async fn snapshot(&self) -> Result { + pub async fn snapshot(&self) -> Result { let mut tx = self.db.begin().await?; let resume_point = tx.sequence().current().await?; @@ -48,3 +51,30 @@ impl<'a> Boot<'a> { }) } } + +#[derive(Debug, thiserror::Error)] +#[error(transparent)] +pub enum Error { + Name(#[from] name::Error), + Database(#[from] sqlx::Error), +} + +impl From for Error { + fn from(error: login::repo::LoadError) -> Self { + use login::repo::LoadError; + match error { + LoadError::Name(error) => error.into(), + LoadError::Database(error) => error.into(), + } + } +} + +impl From for Error { + fn from(error: channel::repo::LoadError) -> Self { + use channel::repo::LoadError; + match error { + LoadError::Name(error) => error.into(), + LoadError::Database(error) => error.into(), + } + } +} -- cgit v1.2.3 From 3fab58827017041168a769184469cff3722d6c38 Mon Sep 17 00:00:00 2001 From: Owen Jacobson Date: Wed, 23 Oct 2024 00:31:53 -0400 Subject: Make sure (most) queries avoid table scans. I've exempted inserts (they never scan in the first place), queries on `event_sequence` (at most one row), and the coalesce()s used for event replay (for now; these are obviously a performance risk area and need addressing). Method: ``` find .sqlx -name 'query-*.json' -exec jq -r '"explain query plan " + .query + ";"' {} + > explain.sql ``` Then go query by query through the resulting file. --- ...8ec70ecce6408cb241c9ac5f76bb484b214e720cb9.json | 56 ------------------- ...ccdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f.json | 20 ------- ...0a94e43226ee6aebdd14d7be97a708d66263a30abf.json | 12 +++++ ...b0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c.json | 44 --------------- ...701b86eddb5a1114202ba385f66f131b1601feec11.json | 62 ---------------------- ...9472f1138395b50cdfc9a28e9791e5484890f0201b.json | 62 ++++++++++++++++++++++ ...ae7e18ecb51d54d1e304dcbd847de145c0c75423a7.json | 20 ------- ...f05ffc52cb1712545ff539df49bbd47df194012f79.json | 56 ------------------- ...1602b8befafe751e9caeb0d5f279731e04c6925f46.json | 44 +++++++++++++++ ...50cfa88adb346b1770e32785166bd43cb853143a7f.json | 56 +++++++++++++++++++ ...7723ae0f13e93a1f18213e208d3a181936a36da5fb.json | 56 +++++++++++++++++++ ...50121451dbcf01e8bec8a987b58c699b27b5d737af.json | 12 +++++ migrations/20241023040731_missing_indices.sql | 17 ++++++ src/boot/app.rs | 2 +- src/channel/repo.rs | 14 ++--- src/login/repo.rs | 2 +- src/message/repo.rs | 16 +++--- 17 files changed, 275 insertions(+), 276 deletions(-) delete mode 100644 .sqlx/query-10a8d7ebb8a228c297f38e8ec70ecce6408cb241c9ac5f76bb484b214e720cb9.json delete mode 100644 .sqlx/query-15ec7a8aa11908f8b2022accdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f.json create mode 100644 .sqlx/query-2f26bccedd1226764e49f00a94e43226ee6aebdd14d7be97a708d66263a30abf.json delete mode 100644 .sqlx/query-2f4def5e6aa14d9bc4ca5db0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c.json delete mode 100644 .sqlx/query-40e0310af2814435cca882701b86eddb5a1114202ba385f66f131b1601feec11.json create mode 100644 .sqlx/query-4623f989492e9eae6788ee9472f1138395b50cdfc9a28e9791e5484890f0201b.json delete mode 100644 .sqlx/query-8eebba45e8267b9eebc7b1ae7e18ecb51d54d1e304dcbd847de145c0c75423a7.json delete mode 100644 .sqlx/query-947df87da285e68a710443f05ffc52cb1712545ff539df49bbd47df194012f79.json create mode 100644 .sqlx/query-9f611a3351f22ed16d67d41602b8befafe751e9caeb0d5f279731e04c6925f46.json create mode 100644 .sqlx/query-a4b34593fdf71bb911beb850cfa88adb346b1770e32785166bd43cb853143a7f.json create mode 100644 .sqlx/query-ad1485f0a5514bcfaf68477723ae0f13e93a1f18213e208d3a181936a36da5fb.json create mode 100644 .sqlx/query-e718f4064cbb3d1b27049450121451dbcf01e8bec8a987b58c699b27b5d737af.json create mode 100644 migrations/20241023040731_missing_indices.sql (limited to 'src/boot/app.rs') diff --git a/.sqlx/query-10a8d7ebb8a228c297f38e8ec70ecce6408cb241c9ac5f76bb484b214e720cb9.json b/.sqlx/query-10a8d7ebb8a228c297f38e8ec70ecce6408cb241c9ac5f76bb484b214e720cb9.json deleted file mode 100644 index f8dd228..0000000 --- a/.sqlx/query-10a8d7ebb8a228c297f38e8ec70ecce6408cb241c9ac5f76bb484b214e720cb9.json +++ /dev/null @@ -1,56 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n select\n channel.id as \"id: Id\",\n name.display_name as \"display_name: String\",\n name.canonical_name as \"canonical_name: String\",\n channel.created_at as \"created_at: DateTime\",\n channel.created_sequence as \"created_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at?: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n from channel\n left join channel_name as name\n using (id)\n left join channel_deleted as deleted\n using (id)\n left join message\n on channel.id = message.channel\n where channel.created_at < $1\n and message.id is null\n and deleted.id is null\n ", - "describe": { - "columns": [ - { - "name": "id: Id", - "ordinal": 0, - "type_info": "Text" - }, - { - "name": "display_name: String", - "ordinal": 1, - "type_info": "Null" - }, - { - "name": "canonical_name: String", - "ordinal": 2, - "type_info": "Null" - }, - { - "name": "created_at: DateTime", - "ordinal": 3, - "type_info": "Text" - }, - { - "name": "created_sequence: Sequence", - "ordinal": 4, - "type_info": "Integer" - }, - { - "name": "deleted_at?: DateTime", - "ordinal": 5, - "type_info": "Text" - }, - { - "name": "deleted_sequence?: Sequence", - "ordinal": 6, - "type_info": "Integer" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - false, - true, - true, - false, - false, - true, - true - ] - }, - "hash": "10a8d7ebb8a228c297f38e8ec70ecce6408cb241c9ac5f76bb484b214e720cb9" -} diff --git a/.sqlx/query-15ec7a8aa11908f8b2022accdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f.json b/.sqlx/query-15ec7a8aa11908f8b2022accdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f.json deleted file mode 100644 index 167e768..0000000 --- a/.sqlx/query-15ec7a8aa11908f8b2022accdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f.json +++ /dev/null @@ -1,20 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n insert into message_deleted (id, deleted_at, deleted_sequence)\n values ($1, $2, $3)\n returning 1 as \"deleted: bool\"\n ", - "describe": { - "columns": [ - { - "name": "deleted: bool", - "ordinal": 0, - "type_info": "Null" - } - ], - "parameters": { - "Right": 3 - }, - "nullable": [ - null - ] - }, - "hash": "15ec7a8aa11908f8b2022accdde2e9e4a49e52e74c3d0e28e82a0b01a3c9887f" -} diff --git a/.sqlx/query-2f26bccedd1226764e49f00a94e43226ee6aebdd14d7be97a708d66263a30abf.json b/.sqlx/query-2f26bccedd1226764e49f00a94e43226ee6aebdd14d7be97a708d66263a30abf.json new file mode 100644 index 0000000..686afb9 --- /dev/null +++ b/.sqlx/query-2f26bccedd1226764e49f00a94e43226ee6aebdd14d7be97a708d66263a30abf.json @@ -0,0 +1,12 @@ +{ + "db_name": "SQLite", + "query": "\n insert into message_deleted (id, deleted_at, deleted_sequence)\n values ($1, $2, $3)\n ", + "describe": { + "columns": [], + "parameters": { + "Right": 3 + }, + "nullable": [] + }, + "hash": "2f26bccedd1226764e49f00a94e43226ee6aebdd14d7be97a708d66263a30abf" +} diff --git a/.sqlx/query-2f4def5e6aa14d9bc4ca5db0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c.json b/.sqlx/query-2f4def5e6aa14d9bc4ca5db0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c.json deleted file mode 100644 index c1da170..0000000 --- a/.sqlx/query-2f4def5e6aa14d9bc4ca5db0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c.json +++ /dev/null @@ -1,44 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n select\n id as \"id: Id\",\n display_name as \"display_name: String\",\n canonical_name as \"canonical_name: String\",\n created_sequence as \"created_sequence: Sequence\",\n created_at as \"created_at: DateTime\"\n from login\n where coalesce(created_sequence <= $1, true)\n order by created_sequence\n ", - "describe": { - "columns": [ - { - "name": "id: Id", - "ordinal": 0, - "type_info": "Text" - }, - { - "name": "display_name: String", - "ordinal": 1, - "type_info": "Text" - }, - { - "name": "canonical_name: String", - "ordinal": 2, - "type_info": "Text" - }, - { - "name": "created_sequence: Sequence", - "ordinal": 3, - "type_info": "Integer" - }, - { - "name": "created_at: DateTime", - "ordinal": 4, - "type_info": "Text" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - false, - false, - false, - false, - false - ] - }, - "hash": "2f4def5e6aa14d9bc4ca5db0d1f81953cd5f584b76ca59ece17d9ef2ce29ee9c" -} diff --git a/.sqlx/query-40e0310af2814435cca882701b86eddb5a1114202ba385f66f131b1601feec11.json b/.sqlx/query-40e0310af2814435cca882701b86eddb5a1114202ba385f66f131b1601feec11.json deleted file mode 100644 index 3147d7f..0000000 --- a/.sqlx/query-40e0310af2814435cca882701b86eddb5a1114202ba385f66f131b1601feec11.json +++ /dev/null @@ -1,62 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n select\n message.channel as \"channel: channel::Id\",\n message.sender as \"sender: login::Id\",\n id as \"id: Id\",\n message.body as \"body: Body\",\n message.sent_at as \"sent_at: DateTime\",\n message.sent_sequence as \"sent_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence: Sequence\"\n from message\n left join message_deleted as deleted\n using (id)\n where message.channel = $1\n and deleted.id is null\n ", - "describe": { - "columns": [ - { - "name": "channel: channel::Id", - "ordinal": 0, - "type_info": "Text" - }, - { - "name": "sender: login::Id", - "ordinal": 1, - "type_info": "Text" - }, - { - "name": "id: Id", - "ordinal": 2, - "type_info": "Text" - }, - { - "name": "body: Body", - "ordinal": 3, - "type_info": "Text" - }, - { - "name": "sent_at: DateTime", - "ordinal": 4, - "type_info": "Text" - }, - { - "name": "sent_sequence: Sequence", - "ordinal": 5, - "type_info": "Integer" - }, - { - "name": "deleted_at: DateTime", - "ordinal": 6, - "type_info": "Text" - }, - { - "name": "deleted_sequence: Sequence", - "ordinal": 7, - "type_info": "Integer" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - false, - false, - false, - true, - false, - false, - true, - true - ] - }, - "hash": "40e0310af2814435cca882701b86eddb5a1114202ba385f66f131b1601feec11" -} diff --git a/.sqlx/query-4623f989492e9eae6788ee9472f1138395b50cdfc9a28e9791e5484890f0201b.json b/.sqlx/query-4623f989492e9eae6788ee9472f1138395b50cdfc9a28e9791e5484890f0201b.json new file mode 100644 index 0000000..bfab6d4 --- /dev/null +++ b/.sqlx/query-4623f989492e9eae6788ee9472f1138395b50cdfc9a28e9791e5484890f0201b.json @@ -0,0 +1,62 @@ +{ + "db_name": "SQLite", + "query": "\n select\n message.channel as \"channel: channel::Id\",\n message.sender as \"sender: login::Id\",\n id as \"id: Id\",\n message.body as \"body: Body\",\n message.sent_at as \"sent_at: DateTime\",\n message.sent_sequence as \"sent_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at?: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n from message\n left join message_deleted as deleted\n using (id)\n where message.channel = $1\n and deleted.id is null\n ", + "describe": { + "columns": [ + { + "name": "channel: channel::Id", + "ordinal": 0, + "type_info": "Text" + }, + { + "name": "sender: login::Id", + "ordinal": 1, + "type_info": "Text" + }, + { + "name": "id: Id", + "ordinal": 2, + "type_info": "Text" + }, + { + "name": "body: Body", + "ordinal": 3, + "type_info": "Text" + }, + { + "name": "sent_at: DateTime", + "ordinal": 4, + "type_info": "Text" + }, + { + "name": "sent_sequence: Sequence", + "ordinal": 5, + "type_info": "Integer" + }, + { + "name": "deleted_at?: DateTime", + "ordinal": 6, + "type_info": "Text" + }, + { + "name": "deleted_sequence?: Sequence", + "ordinal": 7, + "type_info": "Integer" + } + ], + "parameters": { + "Right": 1 + }, + "nullable": [ + false, + false, + false, + true, + false, + false, + false, + false + ] + }, + "hash": "4623f989492e9eae6788ee9472f1138395b50cdfc9a28e9791e5484890f0201b" +} diff --git a/.sqlx/query-8eebba45e8267b9eebc7b1ae7e18ecb51d54d1e304dcbd847de145c0c75423a7.json b/.sqlx/query-8eebba45e8267b9eebc7b1ae7e18ecb51d54d1e304dcbd847de145c0c75423a7.json deleted file mode 100644 index 0394bc5..0000000 --- a/.sqlx/query-8eebba45e8267b9eebc7b1ae7e18ecb51d54d1e304dcbd847de145c0c75423a7.json +++ /dev/null @@ -1,20 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n update message\n set body = \"\"\n where id = $1\n returning 1 as \"blanked: bool\"\n ", - "describe": { - "columns": [ - { - "name": "blanked: bool", - "ordinal": 0, - "type_info": "Null" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - null - ] - }, - "hash": "8eebba45e8267b9eebc7b1ae7e18ecb51d54d1e304dcbd847de145c0c75423a7" -} diff --git a/.sqlx/query-947df87da285e68a710443f05ffc52cb1712545ff539df49bbd47df194012f79.json b/.sqlx/query-947df87da285e68a710443f05ffc52cb1712545ff539df49bbd47df194012f79.json deleted file mode 100644 index d4bb122..0000000 --- a/.sqlx/query-947df87da285e68a710443f05ffc52cb1712545ff539df49bbd47df194012f79.json +++ /dev/null @@ -1,56 +0,0 @@ -{ - "db_name": "SQLite", - "query": "\n select\n id as \"id: Id\",\n name.display_name as \"display_name: String\",\n name.canonical_name as \"canonical_name: String\",\n channel.created_at as \"created_at: DateTime\",\n channel.created_sequence as \"created_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at?: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n from channel\n left join channel_name as name\n using (id)\n left join channel_deleted as deleted\n using (id)\n where coalesce(channel.created_sequence <= $1, true)\n order by name.canonical_name\n ", - "describe": { - "columns": [ - { - "name": "id: Id", - "ordinal": 0, - "type_info": "Text" - }, - { - "name": "display_name: String", - "ordinal": 1, - "type_info": "Text" - }, - { - "name": "canonical_name: String", - "ordinal": 2, - "type_info": "Text" - }, - { - "name": "created_at: DateTime", - "ordinal": 3, - "type_info": "Text" - }, - { - "name": "created_sequence: Sequence", - "ordinal": 4, - "type_info": "Integer" - }, - { - "name": "deleted_at?: DateTime", - "ordinal": 5, - "type_info": "Text" - }, - { - "name": "deleted_sequence?: Sequence", - "ordinal": 6, - "type_info": "Integer" - } - ], - "parameters": { - "Right": 1 - }, - "nullable": [ - false, - true, - true, - false, - false, - true, - true - ] - }, - "hash": "947df87da285e68a710443f05ffc52cb1712545ff539df49bbd47df194012f79" -} diff --git a/.sqlx/query-9f611a3351f22ed16d67d41602b8befafe751e9caeb0d5f279731e04c6925f46.json b/.sqlx/query-9f611a3351f22ed16d67d41602b8befafe751e9caeb0d5f279731e04c6925f46.json new file mode 100644 index 0000000..2d1f49e --- /dev/null +++ b/.sqlx/query-9f611a3351f22ed16d67d41602b8befafe751e9caeb0d5f279731e04c6925f46.json @@ -0,0 +1,44 @@ +{ + "db_name": "SQLite", + "query": "\n select\n id as \"id: Id\",\n display_name as \"display_name: String\",\n canonical_name as \"canonical_name: String\",\n created_sequence as \"created_sequence: Sequence\",\n created_at as \"created_at: DateTime\"\n from login\n where coalesce(created_sequence <= $1, true)\n order by canonical_name\n ", + "describe": { + "columns": [ + { + "name": "id: Id", + "ordinal": 0, + "type_info": "Text" + }, + { + "name": "display_name: String", + "ordinal": 1, + "type_info": "Text" + }, + { + "name": "canonical_name: String", + "ordinal": 2, + "type_info": "Text" + }, + { + "name": "created_sequence: Sequence", + "ordinal": 3, + "type_info": "Integer" + }, + { + "name": "created_at: DateTime", + "ordinal": 4, + "type_info": "Text" + } + ], + "parameters": { + "Right": 1 + }, + "nullable": [ + false, + false, + false, + false, + false + ] + }, + "hash": "9f611a3351f22ed16d67d41602b8befafe751e9caeb0d5f279731e04c6925f46" +} diff --git a/.sqlx/query-a4b34593fdf71bb911beb850cfa88adb346b1770e32785166bd43cb853143a7f.json b/.sqlx/query-a4b34593fdf71bb911beb850cfa88adb346b1770e32785166bd43cb853143a7f.json new file mode 100644 index 0000000..e886759 --- /dev/null +++ b/.sqlx/query-a4b34593fdf71bb911beb850cfa88adb346b1770e32785166bd43cb853143a7f.json @@ -0,0 +1,56 @@ +{ + "db_name": "SQLite", + "query": "\n select\n channel.id as \"id: Id\",\n name.display_name as \"display_name?: String\",\n name.canonical_name as \"canonical_name?: String\",\n channel.created_at as \"created_at: DateTime\",\n channel.created_sequence as \"created_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at?: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n from channel\n left join channel_name as name\n using (id)\n left join channel_deleted as deleted\n using (id)\n left join message\n on channel.id = message.channel\n where channel.created_at < $1\n and message.id is null\n and deleted.id is null\n ", + "describe": { + "columns": [ + { + "name": "id: Id", + "ordinal": 0, + "type_info": "Text" + }, + { + "name": "display_name?: String", + "ordinal": 1, + "type_info": "Null" + }, + { + "name": "canonical_name?: String", + "ordinal": 2, + "type_info": "Null" + }, + { + "name": "created_at: DateTime", + "ordinal": 3, + "type_info": "Text" + }, + { + "name": "created_sequence: Sequence", + "ordinal": 4, + "type_info": "Integer" + }, + { + "name": "deleted_at?: DateTime", + "ordinal": 5, + "type_info": "Text" + }, + { + "name": "deleted_sequence?: Sequence", + "ordinal": 6, + "type_info": "Integer" + } + ], + "parameters": { + "Right": 1 + }, + "nullable": [ + false, + false, + false, + false, + false, + false, + false + ] + }, + "hash": "a4b34593fdf71bb911beb850cfa88adb346b1770e32785166bd43cb853143a7f" +} diff --git a/.sqlx/query-ad1485f0a5514bcfaf68477723ae0f13e93a1f18213e208d3a181936a36da5fb.json b/.sqlx/query-ad1485f0a5514bcfaf68477723ae0f13e93a1f18213e208d3a181936a36da5fb.json new file mode 100644 index 0000000..ce757ba --- /dev/null +++ b/.sqlx/query-ad1485f0a5514bcfaf68477723ae0f13e93a1f18213e208d3a181936a36da5fb.json @@ -0,0 +1,56 @@ +{ + "db_name": "SQLite", + "query": "\n select\n id as \"id: Id\",\n name.display_name as \"display_name?: String\",\n name.canonical_name as \"canonical_name?: String\",\n channel.created_at as \"created_at: DateTime\",\n channel.created_sequence as \"created_sequence: Sequence\",\n deleted.deleted_at as \"deleted_at?: DateTime\",\n deleted.deleted_sequence as \"deleted_sequence?: Sequence\"\n from channel\n left join channel_name as name\n using (id)\n left join channel_deleted as deleted\n using (id)\n where channel.created_sequence <= $1\n order by name.canonical_name\n ", + "describe": { + "columns": [ + { + "name": "id: Id", + "ordinal": 0, + "type_info": "Text" + }, + { + "name": "display_name?: String", + "ordinal": 1, + "type_info": "Null" + }, + { + "name": "canonical_name?: String", + "ordinal": 2, + "type_info": "Null" + }, + { + "name": "created_at: DateTime", + "ordinal": 3, + "type_info": "Text" + }, + { + "name": "created_sequence: Sequence", + "ordinal": 4, + "type_info": "Integer" + }, + { + "name": "deleted_at?: DateTime", + "ordinal": 5, + "type_info": "Text" + }, + { + "name": "deleted_sequence?: Sequence", + "ordinal": 6, + "type_info": "Integer" + } + ], + "parameters": { + "Right": 1 + }, + "nullable": [ + false, + false, + false, + false, + false, + false, + false + ] + }, + "hash": "ad1485f0a5514bcfaf68477723ae0f13e93a1f18213e208d3a181936a36da5fb" +} diff --git a/.sqlx/query-e718f4064cbb3d1b27049450121451dbcf01e8bec8a987b58c699b27b5d737af.json b/.sqlx/query-e718f4064cbb3d1b27049450121451dbcf01e8bec8a987b58c699b27b5d737af.json new file mode 100644 index 0000000..0c21ec1 --- /dev/null +++ b/.sqlx/query-e718f4064cbb3d1b27049450121451dbcf01e8bec8a987b58c699b27b5d737af.json @@ -0,0 +1,12 @@ +{ + "db_name": "SQLite", + "query": "\n update message\n set body = ''\n where id = $1\n ", + "describe": { + "columns": [], + "parameters": { + "Right": 1 + }, + "nullable": [] + }, + "hash": "e718f4064cbb3d1b27049450121451dbcf01e8bec8a987b58c699b27b5d737af" +} diff --git a/migrations/20241023040731_missing_indices.sql b/migrations/20241023040731_missing_indices.sql new file mode 100644 index 0000000..4bfd76d --- /dev/null +++ b/migrations/20241023040731_missing_indices.sql @@ -0,0 +1,17 @@ +create index message_deleted_deleted_at +on message_deleted (deleted_at); + +create index message_channel +on message(channel); + +create index channel_created_sequence +on channel (created_sequence); + +create index channel_created_at +on channel (created_at); + +create index token_last_used_at +on token (last_used_at); + +create index invite_issued_at +on invite (issued_at); diff --git a/src/boot/app.rs b/src/boot/app.rs index 1d88608..e716b58 100644 --- a/src/boot/app.rs +++ b/src/boot/app.rs @@ -23,7 +23,7 @@ impl<'a> Boot<'a> { let resume_point = tx.sequence().current().await?; let logins = tx.logins().all(resume_point.into()).await?; - let channels = tx.channels().all(resume_point.into()).await?; + let channels = tx.channels().all(resume_point).await?; let messages = tx.messages().all(resume_point.into()).await?; tx.commit().await?; diff --git a/src/channel/repo.rs b/src/channel/repo.rs index e26ac2b..a49db52 100644 --- a/src/channel/repo.rs +++ b/src/channel/repo.rs @@ -104,13 +104,13 @@ impl<'c> Channels<'c> { Ok(channel) } - pub async fn all(&mut self, resume_at: ResumePoint) -> Result, LoadError> { + pub async fn all(&mut self, resume_at: Sequence) -> Result, LoadError> { let channels = sqlx::query!( r#" select id as "id: Id", - name.display_name as "display_name: String", - name.canonical_name as "canonical_name: String", + name.display_name as "display_name?: String", + name.canonical_name as "canonical_name?: String", channel.created_at as "created_at: DateTime", channel.created_sequence as "created_sequence: Sequence", deleted.deleted_at as "deleted_at?: DateTime", @@ -120,7 +120,7 @@ impl<'c> Channels<'c> { using (id) left join channel_deleted as deleted using (id) - where coalesce(channel.created_sequence <= $1, true) + where channel.created_sequence <= $1 order by name.canonical_name "#, resume_at, @@ -144,7 +144,7 @@ impl<'c> Channels<'c> { Ok(channels) } - pub async fn replay(&mut self, resume_at: Option) -> Result, LoadError> { + pub async fn replay(&mut self, resume_at: ResumePoint) -> Result, LoadError> { let channels = sqlx::query!( r#" select @@ -263,8 +263,8 @@ impl<'c> Channels<'c> { r#" select channel.id as "id: Id", - name.display_name as "display_name: String", - name.canonical_name as "canonical_name: String", + name.display_name as "display_name?: String", + name.canonical_name as "canonical_name?: String", channel.created_at as "created_at: DateTime", channel.created_sequence as "created_sequence: Sequence", deleted.deleted_at as "deleted_at?: DateTime", diff --git a/src/login/repo.rs b/src/login/repo.rs index c6bc734..611edd6 100644 --- a/src/login/repo.rs +++ b/src/login/repo.rs @@ -69,7 +69,7 @@ impl<'c> Logins<'c> { created_at as "created_at: DateTime" from login where coalesce(created_sequence <= $1, true) - order by created_sequence + order by canonical_name "#, resume_at, ) diff --git a/src/message/repo.rs b/src/message/repo.rs index 4cfefec..c8ceceb 100644 --- a/src/message/repo.rs +++ b/src/message/repo.rs @@ -79,8 +79,8 @@ impl<'c> Messages<'c> { message.body as "body: Body", message.sent_at as "sent_at: DateTime", message.sent_sequence as "sent_sequence: Sequence", - deleted.deleted_at as "deleted_at: DateTime", - deleted.deleted_sequence as "deleted_sequence: Sequence" + deleted.deleted_at as "deleted_at?: DateTime", + deleted.deleted_sequence as "deleted_sequence?: Sequence" from message left join message_deleted as deleted using (id) @@ -186,33 +186,31 @@ impl<'c> Messages<'c> { ) -> Result { let id = message.id(); - sqlx::query_scalar!( + sqlx::query!( r#" insert into message_deleted (id, deleted_at, deleted_sequence) values ($1, $2, $3) - returning 1 as "deleted: bool" "#, id, deleted.at, deleted.sequence, ) - .fetch_one(&mut *self.0) + .execute(&mut *self.0) .await?; // Small social responsibility hack here: when a message is deleted, its body is // retconned to have been the empty string. Someone reading the event stream // afterwards, or looking at messages in the channel, cannot retrieve the // "deleted" message by ignoring the deletion event. - sqlx::query_scalar!( + sqlx::query!( r#" update message - set body = "" + set body = '' where id = $1 - returning 1 as "blanked: bool" "#, id, ) - .fetch_one(&mut *self.0) + .execute(&mut *self.0) .await?; let message = self.by_id(id).await?; -- cgit v1.2.3