wabot-feature-sqlite 0.2.0

SQLite foundation for Wabot — the document store, migrations and connection handling, for a single-node deployment with no database server.
Documentation
//! [`SqliteJsonbStore`] as a [`CrudRepository`].
//!
//! The join that lets an application depend on
//! `Arc<dyn CrudRepository<OrderData>>` and point it at SQLite on a
//! single node, Postgres on several, and
//! [`InMemoryRepository`](wabot_core::repository::InMemoryRepository)
//! in a unit test, with no other difference.
//!
//! Written to behave **identically to the Postgres implementation**,
//! including the parts that are easy to get subtly wrong: listings
//! ordered newest-first, keyset paging that reads one row past the
//! page to decide whether another exists, and an update that matched
//! nothing being an error rather than silent success.

use async_trait::async_trait;
use rusqlite::types::Value as SqlValue;
use serde::de::DeserializeOwned;
use serde::Serialize;
use wabot_core::entity::{Entity, EntityFields};
use wabot_core::repository::{
    CrudRepository, Cursor, Page, PageOptions, ReadRepository, RepositoryError, RepositoryResult,
};

use crate::error::SqliteError;
use crate::jsonb::SqliteJsonbStore;

fn backend(error: SqliteError) -> RepositoryError {
    RepositoryError::Backend(error.to_string())
}

#[async_trait]
impl<D> ReadRepository<D> for SqliteJsonbStore<D>
where
    D: EntityFields + Serialize + DeserializeOwned + Send + Sync + 'static,
{
    async fn find(&self, id: &str) -> RepositoryResult<Option<Entity<D>>> {
        self.find_by_id(id).await.map_err(backend)
    }

    async fn find_by_ids(&self, ids: &[String]) -> RepositoryResult<Vec<Entity<D>>> {
        if ids.is_empty() {
            return Ok(Vec::new());
        }
        // Postgres uses `= ANY($1)` to keep one prepared statement
        // whatever the batch size. SQLite has no array type, so the
        // list is expanded — a different plan per distinct length,
        // which is the cost of the engine rather than of this code.
        let placeholders: Vec<String> = (1..=ids.len()).map(|i| format!("?{i}")).collect();
        let sql = format!(
            "SELECT \"data\" FROM {} WHERE \"id\" IN ({}) \
             ORDER BY \"created_at\" DESC, \"id\" DESC",
            self.table(),
            placeholders.join(", ")
        );
        let values = ids.iter().cloned().map(SqlValue::Text).collect();
        self.query(&sql, values).await.map_err(backend)
    }

    async fn find_all(&self) -> RepositoryResult<Vec<Entity<D>>> {
        let sql = format!(
            "SELECT \"data\" FROM {} ORDER BY \"created_at\" DESC, \"id\" DESC",
            self.table()
        );
        self.query(&sql, Vec::new()).await.map_err(backend)
    }

    async fn find_page(&self, options: PageOptions) -> RepositoryResult<Page<Entity<D>>> {
        // One row more than asked for: if it comes back, there is a
        // next page. Cheaper and more reliable than a second COUNT,
        // which could disagree with the page under concurrent writes.
        let limit = options.limit as i64 + 1;

        let (sql, values) = match &options.cursor {
            None => (
                format!(
                    "SELECT \"data\" FROM {} \
                     ORDER BY \"created_at\" DESC, \"id\" DESC LIMIT ?1",
                    self.table()
                ),
                vec![SqlValue::Integer(limit)],
            ),
            Some(cursor) => (
                // SQLite supports row-value comparison since 3.15, so
                // this walks the (created_at, id) index straight to the
                // boundary exactly as the Postgres version does,
                // instead of filtering rows it has already read.
                format!(
                    "SELECT \"data\" FROM {} \
                     WHERE (\"created_at\", \"id\") < (?1, ?2) \
                     ORDER BY \"created_at\" DESC, \"id\" DESC LIMIT ?3",
                    self.table()
                ),
                vec![
                    SqlValue::Integer(cursor.created_at_ms()),
                    SqlValue::Text(cursor.id().to_string()),
                    SqlValue::Integer(limit),
                ],
            ),
        };

        let rows = self.query(&sql, values).await.map_err(backend)?;

        let has_more = rows.len() > options.limit;
        let items: Vec<Entity<D>> = rows.into_iter().take(options.limit).collect();
        let next_cursor = has_more.then(|| items.last().map(Cursor::of)).flatten();
        Ok(Page { items, next_cursor })
    }
}

#[async_trait]
impl<D> CrudRepository<D> for SqliteJsonbStore<D>
where
    D: EntityFields + Serialize + DeserializeOwned + Send + Sync + 'static,
{
    async fn create(&self, item: &Entity<D>) -> RepositoryResult<()> {
        SqliteJsonbStore::create(self, item).await.map_err(backend)
    }

    /// An update that matched no row is [`RepositoryError::NotFound`],
    /// not silent success — updating something that isn't there is a
    /// bug rather than a no-op.
    async fn update(&self, item: &Entity<D>) -> RepositoryResult<()> {
        let updated = SqliteJsonbStore::update(self, item)
            .await
            .map_err(backend)?;
        if updated {
            Ok(())
        } else {
            Err(RepositoryError::NotFound {
                entity: std::any::type_name::<D>(),
                id: item.id().to_string(),
            })
        }
    }

    async fn delete(&self, item: &Entity<D>) -> RepositoryResult<()> {
        SqliteJsonbStore::delete(self, item.id())
            .await
            .map_err(backend)
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::database::SqliteDatabase;
    use serde::{Deserialize, Serialize};
    use wabot_core::entity::EntityData;

    #[derive(Debug, Clone, Serialize, Deserialize)]
    struct NoteData {
        #[serde(flatten)]
        base: EntityData,
        text: String,
    }

    impl EntityFields for NoteData {
        fn entity(&self) -> &EntityData {
            &self.base
        }
        fn entity_mut(&mut self) -> &mut EntityData {
            &mut self.base
        }
    }

    /// `create` stamps the id but leaves an already-set `created_at`
    /// alone, which is what lets these tests pin the ordering.
    fn note(text: &str, created_at: i64) -> Entity<NoteData> {
        let base = EntityData {
            created_at: Some(created_at),
            ..EntityData::default()
        };
        Entity::create(NoteData {
            base,
            text: text.to_string(),
        })
    }

    async fn repository() -> impl CrudRepository<NoteData> {
        SqliteJsonbStore::<NoteData>::new(SqliteDatabase::in_memory().expect("open"), "notes")
    }

    #[tokio::test]
    async fn the_crud_cycle_behaves() {
        let repository = repository().await;
        let mut entity = note("first", 1_000);

        repository.create(&entity).await.expect("create");
        assert_eq!(
            repository
                .find(entity.id())
                .await
                .expect("find")
                .expect("present")
                .data()
                .text,
            "first"
        );

        entity.update(|data| data.text = "second".into());
        repository.update(&entity).await.expect("update");
        assert_eq!(
            repository
                .find(entity.id())
                .await
                .unwrap()
                .unwrap()
                .data()
                .text,
            "second"
        );

        repository.delete(&entity).await.expect("delete");
        assert!(repository.find(entity.id()).await.unwrap().is_none());
    }

    /// The trait promises the stronger thing than the store does.
    #[tokio::test]
    async fn updating_something_absent_is_not_found() {
        let repository = repository().await;
        let error = repository
            .update(&note("ghost", 1))
            .await
            .expect_err("no such row");
        assert!(matches!(error, RepositoryError::NotFound { .. }), "{error}");
    }

    #[tokio::test]
    async fn listings_are_newest_first() {
        let repository = repository().await;
        for (text, at) in [("old", 1_000), ("new", 3_000), ("middle", 2_000)] {
            repository.create(&note(text, at)).await.expect("create");
        }

        let all = repository.find_all().await.expect("find_all");
        assert_eq!(
            all.iter()
                .map(|e| e.data().text.as_str())
                .collect::<Vec<_>>(),
            vec!["new", "middle", "old"]
        );
    }

    #[tokio::test]
    async fn find_by_ids_returns_only_what_exists() {
        let repository = repository().await;
        let first = note("a", 1_000);
        let second = note("b", 2_000);
        repository.create(&first).await.expect("create");
        repository.create(&second).await.expect("create");

        let found = repository
            .find_by_ids(&[
                first.id().to_string(),
                "missing".to_string(),
                second.id().to_string(),
            ])
            .await
            .expect("find_by_ids");
        assert_eq!(found.len(), 2);
        assert!(repository.find_by_ids(&[]).await.unwrap().is_empty());
    }

    /// Keyset paging, including the case it exists for: two entities
    /// created in the same millisecond must not make the boundary
    /// ambiguous and drop one.
    #[tokio::test]
    async fn pages_walk_the_whole_list_without_gaps_or_repeats() {
        let repository = repository().await;
        for index in 0..7 {
            // Deliberately colliding timestamps.
            repository
                .create(&note(&format!("n{index}"), 1_000 + (index as i64 / 3)))
                .await
                .expect("create");
        }

        let mut seen = Vec::new();
        let mut options = PageOptions::first(3);
        loop {
            let page = repository.find_page(options).await.expect("page");
            assert!(page.items.len() <= 3);
            seen.extend(page.items.iter().map(|e| e.data().text.clone()));
            match page.next_cursor {
                Some(cursor) => options = PageOptions::after(cursor, 3),
                None => break,
            }
        }

        assert_eq!(seen.len(), 7, "every item appeared exactly once: {seen:?}");
        let unique: std::collections::HashSet<_> = seen.iter().collect();
        assert_eq!(unique.len(), 7, "no repeats: {seen:?}");
    }

    #[tokio::test]
    async fn the_last_page_has_no_cursor() {
        let repository = repository().await;
        repository.create(&note("only", 1)).await.expect("create");

        let page = repository
            .find_page(PageOptions::first(10))
            .await
            .expect("page");
        assert_eq!(page.items.len(), 1);
        assert!(page.is_last(), "a short page is the last one");
    }
}