#![forbid(unsafe_code)]
use std::{
path::{Path, PathBuf},
sync::{Arc, Mutex},
};
use kcode_credits::Credits;
use rusqlite::{Connection, OptionalExtension, Transaction, TransactionBehavior, params};
use serde::{Deserialize, Serialize};
use thiserror::Error;
use uuid::Uuid;
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
pub struct Category { pub id: String, pub name: String }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
pub struct TaskSummary { pub id: String, pub title: String, pub user_id: String, pub credits: u64 }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
pub struct Task {
pub id: String, pub title: String, pub description: String, pub user_id: String,
pub credits: u64, pub orphaned: bool, pub categories: Vec<Category>,
pub parents: Vec<TaskSummary>, pub children: Vec<TaskSummary>, pub related: Vec<TaskSummary>,
}
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
pub struct Page<T> { pub items: Vec<T>, pub next_offset: Option<u64>, pub revision: u64 }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct CategoryPage { pub category: Category, pub tasks: Page<TaskSummary> }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
#[serde(rename_all = "camelCase", deny_unknown_fields)]
pub struct BrowsePage { pub user_id: String, pub offset: u64, pub limit: u32 }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
#[serde(deny_unknown_fields)]
pub struct CreateCategory { pub name: String }
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
#[serde(rename_all = "camelCase", deny_unknown_fields)]
pub struct CreateTask {
pub title: String, #[serde(default)] pub description: String, pub user_id: String, pub credits: u64,
#[serde(default)] pub category_ids: Vec<String>, #[serde(default)] pub parent_ids: Vec<String>,
#[serde(default)] pub child_ids: Vec<String>, #[serde(default)] pub related_ids: Vec<String>,
}
#[rustfmt::skip]
#[derive(Clone, Debug, Default, Deserialize, Eq, PartialEq, Serialize)]
#[serde(rename_all = "camelCase", deny_unknown_fields)]
pub struct UpdateTask {
pub task_id: String, pub title: Option<String>, pub description: Option<String>,
pub user_id: Option<String>, pub credits: Option<u64>, pub category_ids: Option<Vec<String>>,
pub parent_ids: Option<Vec<String>>, pub child_ids: Option<Vec<String>>,
pub related_ids: Option<Vec<String>>, #[serde(default)] pub complete: bool,
}
#[rustfmt::skip]
#[derive(Clone, Debug, Deserialize, Eq, PartialEq, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct UpdateOutcome { pub completed: bool, pub task: Option<Task>, pub credits_awarded: u64, pub credit_warning: Option<String> }
#[derive(Debug, Error)]
pub enum Error {
#[error("task-board database failed: {0}")]
Database(#[from] rusqlite::Error),
#[error("task-board writer lane is unavailable")]
WriterUnavailable,
#[error("invalid task-board input: {0}")]
InvalidInput(&'static str),
#[error("{0} was not found")]
NotFound(&'static str),
#[error("only the top orphan can be updated or removed")]
NotTopOrphan,
}
#[derive(Clone)]
pub struct TaskBoard {
path: Arc<PathBuf>,
writer: Arc<Mutex<Connection>>,
credits: Credits,
}
impl TaskBoard {
pub fn open(path: impl AsRef<Path>, credits: Credits) -> Result<Self, Error> {
let path = path.as_ref().to_path_buf();
let connection = Connection::open(&path)?;
connection.execute_batch(SCHEMA)?;
Ok(Self {
path: Arc::new(path),
writer: Arc::new(Mutex::new(connection)),
credits,
})
}
pub fn revision(&self) -> Result<u64, Error> {
revision(&self.reader()?)
}
pub fn categories(&self, page: BrowsePage) -> Result<Page<Category>, Error> {
let (offset, limit) = page_args(&page)?;
let connection = self.reader()?;
let mut statement = connection.prepare(
"SELECT DISTINCT c.id,c.name FROM categories c
JOIN task_categories tc ON tc.category_id=c.id JOIN tasks t ON t.id=tc.task_id
WHERE t.user_id=?1 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
ORDER BY c.name,c.id LIMIT ?2 OFFSET ?3",
)?;
let items = statement
.query_map(params![page.user_id, limit + 1, offset], |row| {
Ok(Category {
id: row.get(0)?,
name: row.get(1)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
page_result(items, page.offset, page.limit, revision(&connection)?)
}
pub fn tasks(&self, page: BrowsePage) -> Result<Page<TaskSummary>, Error> {
let (offset, limit) = page_args(&page)?;
let connection = self.reader()?;
let mut statement = connection.prepare(
"SELECT id,title,user_id,credits FROM tasks t WHERE user_id=?1
AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
ORDER BY title,id LIMIT ?2 OFFSET ?3",
)?;
let items = statement
.query_map(params![page.user_id, limit + 1, offset], summary_row)?
.collect::<Result<Vec<_>, _>>()?;
page_result(items, page.offset, page.limit, revision(&connection)?)
}
pub fn category(&self, category_id: &str, page: BrowsePage) -> Result<CategoryPage, Error> {
let (offset, limit) = page_args(&page)?;
let connection = self.reader()?;
let category = connection
.query_row(
"SELECT id,name FROM categories WHERE id=?1",
[category_id],
|row| {
Ok(Category {
id: row.get(0)?,
name: row.get(1)?,
})
},
)
.optional()?
.ok_or(Error::NotFound("category"))?;
let mut statement = connection.prepare(
"SELECT t.id,t.title,t.user_id,t.credits FROM tasks t JOIN task_categories tc ON tc.task_id=t.id
WHERE tc.category_id=?1 AND t.user_id=?2 AND NOT EXISTS(SELECT 1 FROM orphans o WHERE o.task_id=t.id)
ORDER BY t.title,t.id LIMIT ?3 OFFSET ?4")?;
let items = statement
.query_map(
params![category_id, page.user_id, limit + 1, offset],
summary_row,
)?
.collect::<Result<Vec<_>, _>>()?;
Ok(CategoryPage {
category,
tasks: page_result(items, page.offset, page.limit, revision(&connection)?)?,
})
}
pub fn task(&self, task_id: &str) -> Result<Task, Error> {
load_task(&self.reader()?, task_id)
}
pub fn create_category(&self, input: CreateCategory) -> Result<Category, Error> {
let name = required(input.name, "category name must not be empty")?;
let id = generated_id("category-id-");
self.write(|tx| {
tx.execute(
"INSERT INTO categories(id,name) VALUES(?1,?2)",
params![id, name],
)?;
Ok(())
})?;
Ok(Category { id, name })
}
pub fn remove_category(&self, category_id: &str) -> Result<(), Error> {
self.write(
|tx| match tx.execute("DELETE FROM categories WHERE id=?1", [category_id])? {
0 => Err(Error::NotFound("category")),
_ => Ok(()),
},
)
}
pub fn create_task(&self, input: CreateTask) -> Result<Task, Error> {
let title = required(input.title, "task title must not be empty")?;
required_ref(&input.user_id, "user_id must not be empty")?;
let credits = sql_u64(input.credits)?;
let id = generated_id("task-id-");
self.write(|tx| {
tx.execute(
"INSERT INTO tasks(id,title,description,user_id,credits) VALUES(?1,?2,?3,?4,?5)",
params![id, title, input.description, input.user_id, credits],
)?;
replace_categories(tx, &id, &input.category_ids)?;
replace_parents(tx, &id, &input.parent_ids)?;
replace_children(tx, &id, &input.child_ids)?;
replace_related(tx, &id, &input.related_ids)
})?;
self.task(&id)
}
pub fn update_task(&self, input: UpdateTask) -> Result<UpdateOutcome, Error> {
let task_id = input.task_id.clone();
let completion = self.write(|tx| {
ensure_task(tx, &task_id)?;
ensure_top_if_orphan(tx, &task_id)?;
let title = input.title.map(|value| required(value, "task title must not be empty")).transpose()?;
if let Some(user_id) = &input.user_id { required_ref(user_id, "user_id must not be empty")?; }
let credits = input.credits.map(sql_u64).transpose()?;
tx.execute(
"UPDATE tasks SET title=COALESCE(?2,title),description=COALESCE(?3,description),user_id=COALESCE(?4,user_id),credits=COALESCE(?5,credits) WHERE id=?1",
params![task_id, title, input.description, input.user_id, credits],
)?;
if let Some(ids) = input.category_ids { replace_categories(tx, &task_id, &ids)?; }
if let Some(ids) = input.parent_ids { replace_parents(tx, &task_id, &ids)?; }
if let Some(ids) = input.child_ids { replace_children(tx, &task_id, &ids)?; }
if let Some(ids) = input.related_ids { replace_related(tx, &task_id, &ids)?; }
if input.complete { return archive_and_delete(tx, &task_id).map(Some); }
tx.execute("DELETE FROM orphans WHERE task_id=?1", [&task_id])?;
Ok(None)
})?;
if let Some((user_id, credits)) = completion {
let credit_warning = self
.credits
.award(&user_id, credits)
.err()
.map(|error| error.to_string());
return Ok(UpdateOutcome {
completed: true,
task: None,
credits_awarded: credits,
credit_warning,
});
}
Ok(UpdateOutcome {
completed: false,
task: Some(self.task(&task_id)?),
credits_awarded: 0,
credit_warning: None,
})
}
pub fn remove_task(&self, task_id: &str) -> Result<(), Error> {
self.write(|tx| {
ensure_task(tx, task_id)?;
ensure_top_if_orphan(tx, task_id)?;
push_new_orphans(tx, task_id)?;
tx.execute("DELETE FROM tasks WHERE id=?1", [task_id])?;
Ok(())
})
}
pub fn top_orphan(&self) -> Result<Option<Task>, Error> {
let connection = self.reader()?;
let id = connection
.query_row(
"SELECT task_id FROM orphans ORDER BY sequence DESC LIMIT 1",
[],
|row| row.get::<_, String>(0),
)
.optional()?;
id.map(|id| load_task(&connection, &id)).transpose()
}
fn reader(&self) -> Result<Connection, Error> {
let connection = Connection::open(self.path.as_ref())?;
connection.execute_batch(
"PRAGMA foreign_keys=ON; PRAGMA query_only=ON; PRAGMA busy_timeout=37500;",
)?;
Ok(connection)
}
fn writer(&self) -> Result<std::sync::MutexGuard<'_, Connection>, Error> {
self.writer.lock().map_err(|_| Error::WriterUnavailable)
}
fn write<T>(
&self,
operation: impl FnOnce(&Transaction<'_>) -> Result<T, Error>,
) -> Result<T, Error> {
let mut writer = self.writer()?;
let transaction = writer.transaction_with_behavior(TransactionBehavior::Immediate)?;
let output = operation(&transaction)?;
bump(&transaction)?;
transaction.commit()?;
Ok(output)
}
}
#[rustfmt::skip]
mod store {
use super::*;
pub(super) fn load_task(connection: &Connection, id: &str) -> Result<Task, Error> {
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"))?;
let categories = query_categories(connection, id)?;
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)?;
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)?;
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)?;
let orphaned = connection.query_row("SELECT EXISTS(SELECT 1 FROM orphans WHERE task_id=?1)", [id], |row| row.get::<_, bool>(0))?;
Ok(Task { id: id.into(), title, description, user_id, credits: credits as u64, orphaned, categories, parents, children, related })
}
fn query_categories(connection: &Connection, id: &str) -> Result<Vec<Category>, rusqlite::Error> {
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")?;
statement.query_map([id], |row| Ok(Category { id: row.get(0)?, name: row.get(1)? }))?.collect()
}
fn query_summaries(connection: &Connection, sql: &str, id: &str) -> Result<Vec<TaskSummary>, rusqlite::Error> {
let mut statement = connection.prepare(sql)?; statement.query_map([id], summary_row)?.collect()
}
pub(super) fn summary_row(row: &rusqlite::Row<'_>) -> Result<TaskSummary, rusqlite::Error> {
Ok(TaskSummary { id: row.get(0)?, title: row.get(1)?, user_id: row.get(2)?, credits: row.get::<_, i64>(3)? as u64 })
}
fn replace_pairs(tx: &Transaction<'_>, delete: &str, insert: &str, id: &str, values: &[String], reverse: bool) -> Result<(), Error> {
tx.execute(delete, [id])?;
for value in values { if reverse { tx.execute(insert, params![value, id])?; } else { tx.execute(insert, params![id, value])?; } }
Ok(())
}
pub(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) }
pub(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) }
pub(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) }
pub(super) fn replace_related(tx: &Transaction<'_>, id: &str, values: &[String]) -> Result<(), Error> {
tx.execute("DELETE FROM related WHERE left_id=?1 OR right_id=?1", [id])?;
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])?; }
Ok(())
}
pub(super) fn archive_and_delete(tx: &Transaction<'_>, id: &str) -> Result<(String, u64), Error> {
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)?)))?;
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])?;
push_new_orphans(tx, id)?; tx.execute("DELETE FROM tasks WHERE id=?1", [id])?; Ok((user_id, credits as u64))
}
pub(super) fn push_new_orphans(tx: &Transaction<'_>, id: &str) -> Result<(), rusqlite::Error> {
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(())
}
pub(super) fn ensure_task(tx: &Transaction<'_>, id: &str) -> Result<(), Error> {
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")) }
}
pub(super) fn ensure_top_if_orphan(tx: &Transaction<'_>, id: &str) -> Result<(), Error> {
let position = tx.query_row("SELECT sequence FROM orphans WHERE task_id=?1", [id], |row| row.get::<_, i64>(0)).optional()?;
let top = tx.query_row("SELECT sequence FROM orphans ORDER BY sequence DESC LIMIT 1", [], |row| row.get::<_, i64>(0)).optional()?;
if position.is_some() && position != top { Err(Error::NotTopOrphan) } else { Ok(()) }
}
pub(super) fn page_result<T>(mut items: Vec<T>, offset: u64, limit: u32, revision: u64) -> Result<Page<T>, Error> {
let more = items.len() > limit as usize; items.truncate(limit as usize);
Ok(Page { items, next_offset: more.then(|| offset + u64::from(limit)), revision })
}
pub(super) fn page_args(page: &BrowsePage) -> Result<(i64, i64), Error> {
required_ref(&page.user_id, "user_id must not be empty")?;
if page.limit == 0 || page.limit > 200 { return Err(Error::InvalidInput("page limit must be from 1 through 200")); }
Ok((i64::try_from(page.offset).map_err(|_| Error::InvalidInput("page offset is too large"))?, i64::from(page.limit)))
}
pub(super) fn required(value: String, message: &'static str) -> Result<String, Error> { required_ref(&value, message)?; Ok(value) }
pub(super) fn required_ref(value: &str, message: &'static str) -> Result<(), Error> { if value.trim().is_empty() { Err(Error::InvalidInput(message)) } else { Ok(()) } }
pub(super) fn sql_u64(value: u64) -> Result<i64, Error> { i64::try_from(value).map_err(|_| Error::InvalidInput("credits exceed SQLite's supported range")) }
pub(super) fn generated_id(prefix: &str) -> String { format!("{prefix}{}", &Uuid::new_v4().simple().to_string()[..12]) }
pub(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) }
pub(super) fn bump(connection: &Connection) -> Result<(), rusqlite::Error> { connection.execute("UPDATE metadata SET revision=revision+1 WHERE singleton=1", [])?; Ok(()) }
pub(super) const SCHEMA: &str = "PRAGMA foreign_keys=ON; PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA busy_timeout=37500;
CREATE TABLE IF NOT EXISTS metadata(singleton INTEGER PRIMARY KEY CHECK(singleton=1),revision INTEGER NOT NULL CHECK(revision>=0)) STRICT;
INSERT OR IGNORE INTO metadata(singleton,revision) VALUES(1,0);
CREATE TABLE IF NOT EXISTS categories(id TEXT PRIMARY KEY,name TEXT NOT NULL) STRICT;
CREATE 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;
CREATE 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;
CREATE 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;
CREATE 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;
CREATE TABLE IF NOT EXISTS orphans(sequence INTEGER PRIMARY KEY AUTOINCREMENT,task_id TEXT NOT NULL UNIQUE REFERENCES tasks(id) ON DELETE CASCADE) STRICT;
CREATE 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;
CREATE 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);";
}
use store::*;