oflow/
database.rs

1// Copyright (c) 2026
2//
3// This Source Code Form is subject to the terms of the Mozilla Public
4// License, v. 2.0. If a copy of the MPL was not distributed with this
5// file, You can obtain one at http://mozilla.org/MPL/2.0/.
6
7//! Database persistence layer for [`TodoList`].
8//!
9//! This module provides SQLite-backed storage methods that are attached to
10//! [`TodoList`] via `impl` blocks. These methods handle syncing in-memory
11//! state to the database, loading from the database, and querying by content.
12
13use crate::models::{Todo, TodoList};
14use sqlx::SqlitePool;
15
16impl TodoList {
17    /// Synchronize all in-memory todos to the database.
18    ///
19    /// Deletes database rows whose IDs are not present in the in-memory map,
20    /// then performs an upsert (INSERT ... ON CONFLICT DO UPDATE) for each
21    /// remaining todo in a single transaction. This ensures that the database
22    /// exactly reflects the current state of the in-memory [`TodoList`].
23    ///
24    /// # Arguments
25    ///
26    /// * `pool` - SQLite connection pool
27    ///
28    /// # Errors
29    ///
30    /// Returns an error if the transaction fails.
31    pub async fn sync_to_db(&self, pool: &SqlitePool) -> color_eyre::Result<()> {
32        let mut tx = pool.begin().await?;
33
34        // Delete todos that exist in the database but not in memory
35        let in_memory_ids: Vec<String> = self.todos.keys().cloned().collect();
36        if in_memory_ids.is_empty() {
37            // No todos in memory — delete everything from the database
38            sqlx::query("DELETE FROM todos")
39                .execute(&mut *tx)
40                .await?;
41        } else {
42            // Build placeholders for the NOT IN clause
43            let placeholders = in_memory_ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
44            let delete_sql = format!(
45                "DELETE FROM todos WHERE id NOT IN ({})",
46                placeholders
47            );
48            let mut query = sqlx::query(&delete_sql);
49            for id in &in_memory_ids {
50                query = query.bind(id);
51            }
52            query.execute(&mut *tx).await?;
53        }
54
55        // Upsert remaining todos
56        for todo in self.todos.values() {
57            sqlx::query(
58                "INSERT INTO todos (id, content, created_at, finished_at, finished)
59                VALUES (?, ?, ?, ?, ?)
60                ON CONFLICT(id) DO UPDATE SET
61                    content = excluded.content,
62                    finished_at = excluded.finished_at,
63                    finished = excluded.finished",
64            )
65            .bind(todo.id)
66            .bind(&todo.content)
67            .bind(todo.created_at)
68            .bind(todo.finished_at)
69            .bind(todo.finished)
70            .execute(&mut *tx)
71            .await?;
72        }
73
74        tx.commit().await?;
75
76        Ok(())
77    }
78
79    /// Load all todos from the database into memory.
80    ///
81    /// Replaces the current in-memory todo map with all rows from the
82    /// `todos` table.
83    ///
84    /// # Arguments
85    ///
86    /// * `pool` - SQLite connection pool
87    ///
88    /// # Errors
89    ///
90    /// Returns an error if the query fails.
91    pub async fn load_from_db(&mut self, pool: &SqlitePool) -> color_eyre::Result<()> {
92        let rows = sqlx::query_as::<_, Todo>("SELECT * FROM todos")
93            .fetch_all(pool)
94            .await?;
95
96        self.todos = rows.into_iter().map(|t| (t.id.to_string(), t)).collect();
97
98        Ok(())
99    }
100
101    /// Find a todo by its content.
102    ///
103    /// # Arguments
104    ///
105    /// * `pool` - Database connection pool
106    /// * `content` - Content to search for
107    ///
108    /// # Returns
109    ///
110    /// Returns the Todo if found, None otherwise.
111    pub async fn find_by_content(
112        pool: &SqlitePool,
113        content: &str,
114    ) -> color_eyre::Result<Option<Todo>> {
115        let res = sqlx::query_as::<_, Todo>("SELECT * FROM todos WHERE content = ? LIMIT 1")
116            .bind(content)
117            .fetch_optional(pool)
118            .await?;
119
120        Ok(res)
121    }
122}
oflow/database.rs

oflow/
database.rs
