| 1 | //! SQLite storage. One file in WAL mode; every query is scoped to a list. |
| 2 | |
| 3 | use std::path::Path; |
| 4 | use std::sync::{Arc, Mutex}; |
| 5 | |
| 6 | use rusqlite::{Connection, OptionalExtension, params}; |
| 7 | |
| 8 | /// Schema steps, applied in order and tracked by `PRAGMA user_version`. |
| 9 | /// Only ever append to this list: a migration may add, never drop or rewrite. |
| 10 | const MIGRATIONS: &[&str] = &[ |
| 11 | "CREATE TABLE lists ( |
| 12 | id INTEGER PRIMARY KEY, |
| 13 | token TEXT NOT NULL UNIQUE, |
| 14 | created_at INTEGER NOT NULL DEFAULT (unixepoch()) |
| 15 | ); |
| 16 | CREATE TABLE todos ( |
| 17 | id INTEGER PRIMARY KEY, |
| 18 | list_id INTEGER NOT NULL REFERENCES lists(id), |
| 19 | title TEXT NOT NULL, |
| 20 | completed INTEGER NOT NULL DEFAULT 0 |
| 21 | ); |
| 22 | CREATE INDEX todos_by_list ON todos(list_id, id);", |
| 23 | ]; |
| 24 | |
| 25 | #[derive(Debug, Clone, PartialEq, Eq)] |
| 26 | pub struct Todo { |
| 27 | pub id: i64, |
| 28 | pub title: String, |
| 29 | pub completed: bool, |
| 30 | } |
| 31 | |
| 32 | /// A shared handle to the one connection. SQLite serialises writers anyway, |
| 33 | /// and every statement here takes microseconds, so a mutex is all it needs. |
| 34 | #[derive(Clone)] |
| 35 | pub struct Db(Arc<Mutex<Connection>>); |
| 36 | |
| 37 | impl Db { |
| 38 | pub fn open(path: &Path) -> rusqlite::Result<Db> { |
| 39 | let conn = Connection::open(path)?; |
| 40 | conn.pragma_update(None, "journal_mode", "WAL")?; |
| 41 | conn.pragma_update(None, "synchronous", "NORMAL")?; |
| 42 | conn.pragma_update(None, "foreign_keys", "ON")?; |
| 43 | conn.busy_timeout(std::time::Duration::from_secs(5))?; |
| 44 | migrate(&conn)?; |
| 45 | Ok(Db(Arc::new(Mutex::new(conn)))) |
| 46 | } |
| 47 | |
| 48 | /// Runs `f` with the connection on tokio's blocking pool. |
| 49 | pub async fn run<T, F>(&self, f: F) -> rusqlite::Result<T> |
| 50 | where |
| 51 | T: Send + 'static, |
| 52 | F: FnOnce(&mut Connection) -> rusqlite::Result<T> + Send + 'static, |
| 53 | { |
| 54 | let db = self.0.clone(); |
| 55 | tokio::task::spawn_blocking(move || { |
| 56 | let mut conn = db.lock().unwrap_or_else(|e| e.into_inner()); |
| 57 | f(&mut conn) |
| 58 | }) |
| 59 | .await |
| 60 | .expect("database task panicked") |
| 61 | } |
| 62 | } |
| 63 | |
| 64 | fn migrate(conn: &Connection) -> rusqlite::Result<()> { |
| 65 | let applied: i64 = conn.pragma_query_value(None, "user_version", |r| r.get(0))?; |
| 66 | for (i, step) in MIGRATIONS.iter().enumerate().skip(applied as usize) { |
| 67 | let tx = conn.unchecked_transaction()?; |
| 68 | tx.execute_batch(step)?; |
| 69 | tx.pragma_update(None, "user_version", i as i64 + 1)?; |
| 70 | tx.commit()?; |
| 71 | } |
| 72 | Ok(()) |
| 73 | } |
| 74 | |
| 75 | pub fn find_list(conn: &Connection, token: &str) -> rusqlite::Result<Option<i64>> { |
| 76 | conn.query_row("SELECT id FROM lists WHERE token = ?1", [token], |r| r.get(0)) |
| 77 | .optional() |
| 78 | } |
| 79 | |
| 80 | pub fn create_list(conn: &Connection, token: &str) -> rusqlite::Result<i64> { |
| 81 | conn.execute("INSERT INTO lists (token) VALUES (?1)", [token])?; |
| 82 | Ok(conn.last_insert_rowid()) |
| 83 | } |
| 84 | |
| 85 | pub fn todos(conn: &Connection, list: i64) -> rusqlite::Result<Vec<Todo>> { |
| 86 | let mut stmt = conn.prepare_cached( |
| 87 | "SELECT id, title, completed FROM todos WHERE list_id = ?1 ORDER BY id", |
| 88 | )?; |
| 89 | stmt.query_map([list], |r| { |
| 90 | Ok(Todo { id: r.get(0)?, title: r.get(1)?, completed: r.get(2)? }) |
| 91 | })? |
| 92 | .collect() |
| 93 | } |
| 94 | |
| 95 | pub fn add(conn: &Connection, list: i64, title: &str) -> rusqlite::Result<()> { |
| 96 | conn.prepare_cached("INSERT INTO todos (list_id, title) VALUES (?1, ?2)")? |
| 97 | .execute(params![list, title])?; |
| 98 | Ok(()) |
| 99 | } |
| 100 | |
| 101 | /// Each of these returns whether the item was in the list. |
| 102 | pub fn toggle(conn: &Connection, list: i64, id: i64) -> rusqlite::Result<bool> { |
| 103 | let n = conn |
| 104 | .prepare_cached("UPDATE todos SET completed = NOT completed WHERE id = ?1 AND list_id = ?2")? |
| 105 | .execute([id, list])?; |
| 106 | Ok(n > 0) |
| 107 | } |
| 108 | |
| 109 | pub fn rename(conn: &Connection, list: i64, id: i64, title: &str) -> rusqlite::Result<bool> { |
| 110 | let n = conn |
| 111 | .prepare_cached("UPDATE todos SET title = ?3 WHERE id = ?1 AND list_id = ?2")? |
| 112 | .execute(params![id, list, title])?; |
| 113 | Ok(n > 0) |
| 114 | } |
| 115 | |
| 116 | pub fn destroy(conn: &Connection, list: i64, id: i64) -> rusqlite::Result<bool> { |
| 117 | let n = conn |
| 118 | .prepare_cached("DELETE FROM todos WHERE id = ?1 AND list_id = ?2")? |
| 119 | .execute([id, list])?; |
| 120 | Ok(n > 0) |
| 121 | } |
| 122 | |
| 123 | /// Completes every item, or reopens them all if every one already was. |
| 124 | pub fn toggle_all(conn: &Connection, list: i64) -> rusqlite::Result<()> { |
| 125 | let any_active: bool = conn |
| 126 | .prepare_cached("SELECT EXISTS (SELECT 1 FROM todos WHERE list_id = ?1 AND NOT completed)")? |
| 127 | .query_row([list], |r| r.get(0))?; |
| 128 | conn.prepare_cached("UPDATE todos SET completed = ?2 WHERE list_id = ?1")? |
| 129 | .execute(params![list, any_active])?; |
| 130 | Ok(()) |
| 131 | } |
| 132 | |
| 133 | pub fn clear_completed(conn: &Connection, list: i64) -> rusqlite::Result<()> { |
| 134 | conn.prepare_cached("DELETE FROM todos WHERE list_id = ?1 AND completed")? |
| 135 | .execute([list])?; |
| 136 | Ok(()) |
| 137 | } |
| 138 | |
amadan