kcode_task_board/
lib.rs

1//! Persistent graph-shaped task board owned by Kennedy.
2
3#![forbid(unsafe_code)]
4
5use std::{
6    path::{Path, PathBuf},
7    sync::{Arc, Mutex},
8};
9
10use kcode_credits::Credits;
11use rusqlite::{Connection, OptionalExtension, Transaction, TransactionBehavior, params};
12use serde::{Deserialize, Serialize};
13use thiserror::Error;
14use uuid::Uuid;
15
16#[rustfmt::skip]
17#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
18pub struct Category { pub id: String, pub name: String }
19#[rustfmt::skip]
20#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
21pub struct TaskSummary { pub id: String, pub title: String, pub user_id: String, pub credits: u64 }
22#[rustfmt::skip]
23#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
24pub struct Task {
25    pub id: String, pub title: String, pub description: String, pub user_id: String,
26    pub credits: u64, pub orphaned: bool, pub categories: Vec<Category>,
27    pub parents: Vec<TaskSummary>, pub children: Vec<TaskSummary>, pub related: Vec<TaskSummary>,
28}
29#[rustfmt::skip]
30#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
31pub struct Page<T> { pub items: Vec<T>, pub next_offset: Option<u64>, pub revision: u64 }
32#[rustfmt::skip]
33#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
34#[serde(rename_all = "camelCase")]
35pub struct CategoryPage { pub category: Category, pub tasks: Page<TaskSummary> }
36#[rustfmt::skip]
37#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
38#[serde(rename_all = "camelCase", deny_unknown_fields)]
39pub struct BrowsePage { pub user_id: String, pub offset: u64, pub limit: u32 }
40#[rustfmt::skip]
41#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
42#[serde(deny_unknown_fields)]
43pub struct CreateCategory { pub name: String }
44#[rustfmt::skip]
45#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
46#[serde(rename_all = "camelCase", deny_unknown_fields)]
47pub struct CreateTask {
48    pub title: String, #[serde(default)] pub description: String, pub user_id: String, pub credits: u64,
49    #[serde(default)] pub category_ids: Vec<String>, #[serde(default)] pub parent_ids: Vec<String>,
50    #[serde(default)] pub child_ids: Vec<String>, #[serde(default)] pub related_ids: Vec<String>,
51}
52#[rustfmt::skip]
53#[derive(Clone, Debug, Default, Deserialize, Eq, PartialEq, Serialize)]
54#[serde(rename_all = "camelCase", deny_unknown_fields)]
55pub struct UpdateTask {
56    pub task_id: String, pub title: Option<String>, pub description: Option<String>,
57    pub user_id: Option<String>, pub credits: Option<u64>, pub category_ids: Option<Vec<String>>,
58    pub parent_ids: Option<Vec<String>>, pub child_ids: Option<Vec<String>>,
59    pub related_ids: Option<Vec<String>>, #[serde(default)] pub complete: bool,
60}
61#[rustfmt::skip]
62#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
63#[serde(rename_all = "camelCase")]
64pub struct UpdateOutcome { pub completed: bool, pub task: Option<Task>, pub credits_awarded: u64, pub credit_warning: Option<String> }
65
66#[derive(Debug, Error)]
67pub enum Error {
68    #[error("task-board database failed: {0}")]
69    Database(#[from] rusqlite::Error),
70    #[error("task-board writer lane is unavailable")]
71    WriterUnavailable,
72    #[error("invalid task-board input: {0}")]
73    InvalidInput(&'static str),
74    #[error("{0} was not found")]
75    NotFound(&'static str),
76    #[error("only the top orphan can be updated or removed")]
77    NotTopOrphan,
78}
79
80#[derive(Clone)]
81pub struct TaskBoard {
82    path: Arc<PathBuf>,
83    writer: Arc<Mutex<Connection>>,
84    credits: Credits,
85}
86
87impl TaskBoard {
88    pub fn open(path: impl AsRef<Path>, credits: Credits) -> Result<Self, Error> {
89        let path = path.as_ref().to_path_buf();
90        let connection = Connection::open(&path)?;
91        connection.execute_batch(SCHEMA)?;
92        Ok(Self {
93            path: Arc::new(path),
94            writer: Arc::new(Mutex::new(connection)),
95            credits,
96        })
97    }
98
99    pub fn revision(&self) -> Result<u64, Error> {
100        revision(&self.reader()?)
101    }
102
103    pub fn categories(&self, page: BrowsePage) -> Result<Page<Category>, Error> {
104        let (offset, limit) = page_args(&page)?;
105        let connection = self.reader()?;
106        let mut statement = connection.prepare(
107            "SELECT DISTINCT c.id,c.name FROM categories c
108             JOIN task_categories tc ON tc.category_id=c.id JOIN tasks t ON t.id=tc.task_id
109             WHERE t.user_id=?1 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
110             ORDER BY c.name,c.id LIMIT ?2 OFFSET ?3",
111        )?;
112        let items = statement
113            .query_map(params![page.user_id, limit + 1, offset], |row| {
114                Ok(Category {
115                    id: row.get(0)?,
116                    name: row.get(1)?,
117                })
118            })?
119            .collect::<Result<Vec<_>, _>>()?;
120        page_result(items, page.offset, page.limit, revision(&connection)?)
121    }
122
123    pub fn tasks(&self, page: BrowsePage) -> Result<Page<TaskSummary>, Error> {
124        let (offset, limit) = page_args(&page)?;
125        let connection = self.reader()?;
126        let mut statement = connection.prepare(
127            "SELECT id,title,user_id,credits FROM tasks t WHERE user_id=?1
128             AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
129             ORDER BY title,id LIMIT ?2 OFFSET ?3",
130        )?;
131        let items = statement
132            .query_map(params![page.user_id, limit + 1, offset], summary_row)?
133            .collect::<Result<Vec<_>, _>>()?;
134        page_result(items, page.offset, page.limit, revision(&connection)?)
135    }
136
137    pub fn category(&self, category_id: &str, page: BrowsePage) -> Result<CategoryPage, Error> {
138        let (offset, limit) = page_args(&page)?;
139        let connection = self.reader()?;
140        let category = connection
141            .query_row(
142                "SELECT id,name FROM categories WHERE id=?1",
143                [category_id],
144                |row| {
145                    Ok(Category {
146                        id: row.get(0)?,
147                        name: row.get(1)?,
148                    })
149                },
150            )
151            .optional()?
152            .ok_or(Error::NotFound("category"))?;
153        let mut statement = connection.prepare(
154            "SELECT t.id,t.title,t.user_id,t.credits FROM tasks t JOIN task_categories tc ON tc.task_id=t.id
155             WHERE tc.category_id=?1 AND t.user_id=?2 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
156             ORDER BY t.title,t.id LIMIT ?3 OFFSET ?4")?;
157        let items = statement
158            .query_map(
159                params![category_id, page.user_id, limit + 1, offset],
160                summary_row,
161            )?
162            .collect::<Result<Vec<_>, _>>()?;
163        Ok(CategoryPage {
164            category,
165            tasks: page_result(items, page.offset, page.limit, revision(&connection)?)?,
166        })
167    }
168
169    pub fn task(&self, task_id: &str) -> Result<Task, Error> {
170        load_task(&self.reader()?, task_id)
171    }
172
173    pub fn create_category(&self, input: CreateCategory) -> Result<Category, Error> {
174        let name = required(input.name, "category name must not be empty")?;
175        let id = generated_id("category-id-");
176        self.write(|tx| {
177            tx.execute(
178                "INSERT INTO categories(id,name) VALUES(?1,?2)",
179                params![id, name],
180            )?;
181            Ok(())
182        })?;
183        Ok(Category { id, name })
184    }
185
186    pub fn remove_category(&self, category_id: &str) -> Result<(), Error> {
187        self.write(
188            |tx| match tx.execute("DELETE FROM categories WHERE id=?1", [category_id])? {
189                0 => Err(Error::NotFound("category")),
190                _ => Ok(()),
191            },
192        )
193    }
194
195    pub fn create_task(&self, input: CreateTask) -> Result<Task, Error> {
196        let title = required(input.title, "task title must not be empty")?;
197        required_ref(&input.user_id, "user_id must not be empty")?;
198        let credits = sql_u64(input.credits)?;
199        let id = generated_id("task-id-");
200        self.write(|tx| {
201            tx.execute(
202                "INSERT INTO tasks(id,title,description,user_id,credits) VALUES(?1,?2,?3,?4,?5)",
203                params![id, title, input.description, input.user_id, credits],
204            )?;
205            replace_categories(tx, &id, &input.category_ids)?;
206            replace_parents(tx, &id, &input.parent_ids)?;
207            replace_children(tx, &id, &input.child_ids)?;
208            replace_related(tx, &id, &input.related_ids)
209        })?;
210        self.task(&id)
211    }
212
213    pub fn update_task(&self, input: UpdateTask) -> Result<UpdateOutcome, Error> {
214        let task_id = input.task_id.clone();
215        let completion = self.write(|tx| {
216            ensure_task(tx, &task_id)?;
217            ensure_top_if_orphan(tx, &task_id)?;
218            if input.complete { return archive_and_delete(tx, &task_id).map(Some); }
219            let title = input.title.map(|value| required(value, "task title must not be empty")).transpose()?;
220            if let Some(user_id) = &input.user_id { required_ref(user_id, "user_id must not be empty")?; }
221            let credits = input.credits.map(sql_u64).transpose()?;
222            tx.execute(
223                "UPDATE tasks SET title=COALESCE(?2,title),description=COALESCE(?3,description),user_id=COALESCE(?4,user_id),credits=COALESCE(?5,credits) WHERE id=?1",
224                params![task_id, title, input.description, input.user_id, credits],
225            )?;
226            if let Some(ids) = input.category_ids { replace_categories(tx, &task_id, &ids)?; }
227            if let Some(ids) = input.parent_ids { replace_parents(tx, &task_id, &ids)?; }
228            if let Some(ids) = input.child_ids { replace_children(tx, &task_id, &ids)?; }
229            if let Some(ids) = input.related_ids { replace_related(tx, &task_id, &ids)?; }
230            tx.execute("DELETE FROM orphans WHERE task_id=?1", [&task_id])?;
231            Ok(None)
232        })?;
233        if let Some((user_id, credits)) = completion {
234            let credit_warning = self
235                .credits
236                .award(&user_id, credits)
237                .err()
238                .map(|error| error.to_string());
239            return Ok(UpdateOutcome {
240                completed: true,
241                task: None,
242                credits_awarded: credits,
243                credit_warning,
244            });
245        }
246        Ok(UpdateOutcome {
247            completed: false,
248            task: Some(self.task(&task_id)?),
249            credits_awarded: 0,
250            credit_warning: None,
251        })
252    }
253
254    pub fn remove_task(&self, task_id: &str) -> Result<(), Error> {
255        self.write(|tx| {
256            ensure_task(tx, task_id)?;
257            ensure_top_if_orphan(tx, task_id)?;
258            push_new_orphans(tx, task_id)?;
259            tx.execute("DELETE FROM tasks WHERE id=?1", [task_id])?;
260            Ok(())
261        })
262    }
263
264    pub fn top_orphan(&self) -> Result<Option<Task>, Error> {
265        let connection = self.reader()?;
266        let id = connection
267            .query_row(
268                "SELECT task_id FROM orphans ORDER BY sequence DESC LIMIT 1",
269                [],
270                |row| row.get::<_, String>(0),
271            )
272            .optional()?;
273        id.map(|id| load_task(&connection, &id)).transpose()
274    }
275
276    fn reader(&self) -> Result<Connection, Error> {
277        let connection = Connection::open(self.path.as_ref())?;
278        connection.execute_batch(
279            "PRAGMA foreign_keys=ON; PRAGMA query_only=ON; PRAGMA busy_timeout=37500;",
280        )?;
281        Ok(connection)
282    }
283
284    fn writer(&self) -> Result<std::sync::MutexGuard<'_, Connection>, Error> {
285        self.writer.lock().map_err(|_| Error::WriterUnavailable)
286    }
287
288    fn write<T>(
289        &self,
290        operation: impl FnOnce(&Transaction<'_>) -> Result<T, Error>,
291    ) -> Result<T, Error> {
292        let mut writer = self.writer()?;
293        let transaction = writer.transaction_with_behavior(TransactionBehavior::Immediate)?;
294        let output = operation(&transaction)?;
295        bump(&transaction)?;
296        transaction.commit()?;
297        Ok(output)
298    }
299}
300
301#[rustfmt::skip]
302mod store {
303use super::*;
304
305pub(super) fn load_task(connection: &Connection, id: &str) -> Result<Task, Error> {
306    let (title, description, user_id, credits) = connection.query_row("SELECT title,description,user_id,credits FROM tasks WHERE id=?1", [id], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get::<_, i64>(3)?))).optional()?.ok_or(Error::NotFound("task"))?;
307    let categories = query_categories(connection, id)?;
308    let parents = query_summaries(connection, "SELECT t.id,t.title,t.user_id,t.credits FROM task_edges e JOIN tasks t ON t.id=e.parent_id WHERE e.child_id=?1 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id) ORDER BY t.title,t.id", id)?;
309    let children = query_summaries(connection, "SELECT t.id,t.title,t.user_id,t.credits FROM task_edges e JOIN tasks t ON t.id=e.child_id WHERE e.parent_id=?1 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id) ORDER BY t.title,t.id", id)?;
310    let related = query_summaries(connection, "SELECT t.id,t.title,t.user_id,t.credits FROM related r JOIN tasks t ON t.id=CASE WHEN r.left_id=?1 THEN r.right_id ELSE r.left_id END WHERE (r.left_id=?1 OR r.right_id=?1) AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id) ORDER BY t.title,t.id", id)?;
311    let orphaned = connection.query_row("SELECT EXISTS(SELECT 1 FROM orphans WHERE task_id=?1)", [id], |row| row.get::<_, bool>(0))?;
312    Ok(Task { id: id.into(), title, description, user_id, credits: credits as u64, orphaned, categories, parents, children, related })
313}
314
315fn query_categories(connection: &Connection, id: &str) -> Result<Vec<Category>, rusqlite::Error> {
316    let mut statement = connection.prepare("SELECT c.id,c.name FROM task_categories tc JOIN categories c ON c.id=tc.category_id WHERE tc.task_id=?1 ORDER BY c.name,c.id")?;
317    statement.query_map([id], |row| Ok(Category { id: row.get(0)?, name: row.get(1)? }))?.collect()
318}
319fn query_summaries(connection: &Connection, sql: &str, id: &str) -> Result<Vec<TaskSummary>, rusqlite::Error> {
320    let mut statement = connection.prepare(sql)?; statement.query_map([id], summary_row)?.collect()
321}
322pub(super) fn summary_row(row: &rusqlite::Row<'_>) -> Result<TaskSummary, rusqlite::Error> {
323    Ok(TaskSummary { id: row.get(0)?, title: row.get(1)?, user_id: row.get(2)?, credits: row.get::<_, i64>(3)? as u64 })
324}
325
326fn replace_pairs(tx: &Transaction<'_>, delete: &str, insert: &str, id: &str, values: &[String], reverse: bool) -> Result<(), Error> {
327    tx.execute(delete, [id])?;
328    for value in values { if reverse { tx.execute(insert, params![value, id])?; } else { tx.execute(insert, params![id, value])?; } }
329    Ok(())
330}
331pub(super) fn replace_categories(tx: &Transaction<'_>, id: &str, values: &[String]) -> Result<(), Error> { replace_pairs(tx, "DELETE FROM task_categories WHERE task_id=?1", "INSERT OR IGNORE INTO task_categories(task_id,category_id) VALUES(?1,?2)", id, values, false) }
332pub(super) fn replace_parents(tx: &Transaction<'_>, id: &str, values: &[String]) -> Result<(), Error> { replace_pairs(tx, "DELETE FROM task_edges WHERE child_id=?1", "INSERT OR IGNORE INTO task_edges(parent_id,child_id) VALUES(?1,?2)", id, values, true) }
333pub(super) fn replace_children(tx: &Transaction<'_>, id: &str, values: &[String]) -> Result<(), Error> { replace_pairs(tx, "DELETE FROM task_edges WHERE parent_id=?1", "INSERT OR IGNORE INTO task_edges(parent_id,child_id) VALUES(?1,?2)", id, values, false) }
334pub(super) fn replace_related(tx: &Transaction<'_>, id: &str, values: &[String]) -> Result<(), Error> {
335    tx.execute("DELETE FROM related WHERE left_id=?1 OR right_id=?1", [id])?;
336    for value in values { let pair = if id <= value.as_str() { (id, value.as_str()) } else { (value.as_str(), id) }; tx.execute("INSERT OR IGNORE INTO related(left_id,right_id) VALUES(?1,?2)", params![pair.0, pair.1])?; }
337    Ok(())
338}
339
340pub(super) fn archive_and_delete(tx: &Transaction<'_>, id: &str) -> Result<(String, u64), Error> {
341    let (user_id, credits) = tx.query_row("SELECT user_id,credits FROM tasks WHERE id=?1", [id], |row| Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?)))?;
342    tx.execute("INSERT INTO completed_tasks(id,title,description,user_id,credits,completed_at) SELECT id,title,description,user_id,credits,CURRENT_TIMESTAMP FROM tasks WHERE id=?1", [id])?;
343    push_new_orphans(tx, id)?; tx.execute("DELETE FROM tasks WHERE id=?1", [id])?; Ok((user_id, credits as u64))
344}
345pub(super) fn push_new_orphans(tx: &Transaction<'_>, id: &str) -> Result<(), rusqlite::Error> {
346    tx.execute("INSERT OR IGNORE INTO orphans(task_id) SELECT e.child_id FROM task_edges e WHERE e.parent_id=?1 AND e.child_id<>?1 AND NOT EXISTS(SELECT 1 FROM task_edges other WHERE other.child_id=e.child_id AND other.parent_id<>?1) ORDER BY e.child_id", [id])?; Ok(())
347}
348pub(super) fn ensure_task(tx: &Transaction<'_>, id: &str) -> Result<(), Error> {
349    if tx.query_row("SELECT EXISTS(SELECT 1 FROM tasks WHERE id=?1)", [id], |row| row.get::<_, bool>(0))? { Ok(()) } else { Err(Error::NotFound("task")) }
350}
351pub(super) fn ensure_top_if_orphan(tx: &Transaction<'_>, id: &str) -> Result<(), Error> {
352    let position = tx.query_row("SELECT sequence FROM orphans WHERE task_id=?1", [id], |row| row.get::<_, i64>(0)).optional()?;
353    let top = tx.query_row("SELECT sequence FROM orphans ORDER BY sequence DESC LIMIT 1", [], |row| row.get::<_, i64>(0)).optional()?;
354    if position.is_some() && position != top { Err(Error::NotTopOrphan) } else { Ok(()) }
355}
356
357pub(super) fn page_result<T>(mut items: Vec<T>, offset: u64, limit: u32, revision: u64) -> Result<Page<T>, Error> {
358    let more = items.len() > limit as usize; items.truncate(limit as usize);
359    Ok(Page { items, next_offset: more.then(|| offset + u64::from(limit)), revision })
360}
361pub(super) fn page_args(page: &BrowsePage) -> Result<(i64, i64), Error> {
362    required_ref(&page.user_id, "user_id must not be empty")?;
363    if page.limit == 0 || page.limit > 200 { return Err(Error::InvalidInput("page limit must be from 1 through 200")); }
364    Ok((i64::try_from(page.offset).map_err(|_| Error::InvalidInput("page offset is too large"))?, i64::from(page.limit)))
365}
366pub(super) fn required(value: String, message: &'static str) -> Result<String, Error> { required_ref(&value, message)?; Ok(value) }
367pub(super) fn required_ref(value: &str, message: &'static str) -> Result<(), Error> { if value.trim().is_empty() { Err(Error::InvalidInput(message)) } else { Ok(()) } }
368pub(super) fn sql_u64(value: u64) -> Result<i64, Error> { i64::try_from(value).map_err(|_| Error::InvalidInput("credits exceed SQLite's supported range")) }
369pub(super) fn generated_id(prefix: &str) -> String { format!("{prefix}{}", &Uuid::new_v4().simple().to_string()[..12]) }
370pub(super) fn revision(connection: &Connection) -> Result<u64, Error> { Ok(connection.query_row("SELECT revision FROM metadata WHERE singleton=1", [], |row| row.get::<_, i64>(0))? as u64) }
371pub(super) fn bump(connection: &Connection) -> Result<(), rusqlite::Error> { connection.execute("UPDATE metadata SET revision=revision+1 WHERE singleton=1", [])?; Ok(()) }
372
373pub(super) const SCHEMA: &str = "PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA busy_timeout=37500;
374CREATE TABLE IF NOT EXISTS metadata(singleton INTEGER PRIMARY KEY CHECK(singleton=1),revision INTEGER NOT NULL CHECK(revision>=0)) STRICT;
375INSERT OR IGNORE INTO metadata(singleton,revision) VALUES(1,0);
376CREATE TABLE IF NOT EXISTS categories(id TEXT PRIMARY KEY,name TEXT NOT NULL) STRICT;
377CREATE TABLE IF NOT EXISTS tasks(id TEXT PRIMARY KEY,title TEXT NOT NULL,description TEXT NOT NULL,user_id TEXT NOT NULL,credits INTEGER NOT NULL CHECK(credits>=0)) STRICT;
378CREATE TABLE IF NOT EXISTS task_categories(task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,category_id TEXT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,PRIMARY KEY(task_id,category_id)) STRICT;
379CREATE TABLE IF NOT EXISTS task_edges(parent_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,child_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,PRIMARY KEY(parent_id,child_id)) STRICT;
380CREATE TABLE IF NOT EXISTS related(left_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,right_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,PRIMARY KEY(left_id,right_id)) STRICT;
381CREATE TABLE IF NOT EXISTS orphans(sequence INTEGER PRIMARY KEY AUTOINCREMENT,task_id TEXT NOT NULL UNIQUE REFERENCES tasks(id) ON DELETE CASCADE) STRICT;
382CREATE TABLE IF NOT EXISTS completed_tasks(id TEXT PRIMARY KEY,title TEXT NOT NULL,description TEXT NOT NULL,user_id TEXT NOT NULL,credits INTEGER NOT NULL,completed_at TEXT NOT NULL) STRICT;
383CREATE INDEX IF NOT EXISTS tasks_user ON tasks(user_id,title,id); CREATE INDEX IF NOT EXISTS task_categories_category ON task_categories(category_id,task_id); CREATE INDEX IF NOT EXISTS task_edges_child ON task_edges(child_id,parent_id); CREATE INDEX IF NOT EXISTS related_right ON related(right_id,left_id);";
384}
385
386use store::*;
kcode_task_board/lib.rs

kcode_task_board/
lib.rs
