feat: database functionsHEADmain

1 files changed, 304 insertions, 43 deletions

diff --git a/src/db.rs b/src/db.rs
index db5534c..6cdf24f 100644
--- a/src/db.rs
+++ b/src/db.rs
@@ -1,62 +1,323 @@
-use sqlx::sqlite::SqlitePool;
+use std::{collections::HashSet, path::Path};
 
-use crate::{task::Task, Result};
+use chrono::NaiveDateTime;
+use rusqlite::{params, params_from_iter, Connection, ToSql};
+use uuid::Uuid;
 
-struct Db {
-    pool: SqlitePool,
-}
+use crate::{
+    error::DatabaseOpenError,
+    task::{Category, Log, Task},
+    Result,
+};
+
+struct Db(Connection);
 
 impl Db {
-    pub async fn create_task(&self, task: Task) -> Result<Task> {
-        let id = sqlx::query!(
-            "insert into tasks ( name, cron, archived, description ) values ( ?1, ?2, ?3, ?4 )",
-            task.name,
-            task.cron,
-            task.archived,
-            task.description
-        )
-        .execute(&self.pool)
-        .await?
-        .last_insert_rowid();
-
-        for category in task.categories.clone() {
-            let category_id = sqlx::query!(
-                "insert or ignore into categories ( name ) values ( ?1 )",
-                category
-            )
-            .execute(&self.pool)
-            .await?
-            .last_insert_rowid();
-
-            sqlx::query!(
+    pub fn create_connect_and_migrate(
+        path: impl AsRef<Path>,
+    ) -> std::result::Result<Self, DatabaseOpenError> {
+        if let Some(dir) = path.as_ref().parent() {
+            if dir.is_dir() {
+            } else {
+                std::fs::create_dir_all(dir)?;
+            }
+            let _file = std::fs::OpenOptions::new()
+                .append(true)
+                .create(true)
+                .open(path.as_ref())?;
+        }
+        let url = format!(
+            "{}",
+            path.as_ref()
+                .to_str()
+                .ok_or(DatabaseOpenError::InvalidPath)?
+        );
+        // let db = SqlitePool::connect(&url).await?;
+        // migrate!().run(&db).await?;
+        // Ok(Self { db })
+        let db = Connection::open(url)?;
+        db.execute_batch(include_str!("../migrations/1.sql"))?;
+        Ok(Self(db))
+    }
+
+    pub fn create_connect_memory() -> std::result::Result<Self, DatabaseOpenError> {
+        let db = Connection::open_in_memory()?;
+        db.execute_batch(include_str!("../migrations/1.sql"))?;
+        Ok(Self(db))
+    }
+
+    pub fn create_task(&self, task: &Task) -> Result<()> {
+        let task_id = Uuid::new_v4();
+        self.0.execute(
+            "insert into tasks ( id, name, cron, archived, description ) values ( ?1, ?2, ?3, ?4, ?5 )",
+            (task_id, &task.name, &task.cron, task.archived, &task.description),
+        )?;
+
+        for category in &task.categories {
+            let category_id = Uuid::new_v4();
+            self.0.execute(
+                "insert or ignore into categories ( id, name ) values ( ?1, ?2 )",
+                (category_id, category),
+            )?;
+
+            self.0.execute(
+                "insert into tasks_categories ( task_id, category_id ) values ( ?1, ?2 )",
+                [task_id, category_id],
+            )?;
+        }
+
+        Ok(())
+    }
+
+    pub fn read_task(&self, name: impl AsRef<str>) -> Result<Task> {
+        let name = name.as_ref();
+        let (task_id, mut task) = self
+            .0
+            .prepare("select id, name, cron, archived, description from tasks where name = ?1")?
+            .query_one([name], |row| {
+                Ok((
+                    row.get::<_, Uuid>(0)?,
+                    Task {
+                        name: row.get(1)?,
+                        cron: row.get(2)?,
+                        archived: row.get(3)?,
+                        description: row.get(4)?,
+                        categories: HashSet::new(),
+                    },
+                ))
+            })?;
+
+        task.categories = self
+            .0
+            .prepare("select name from tasks_categories join categories where task_id = ?1")?
+            .query_map([task_id], |row| Ok(Category::new(row.get(0)?)))?
+            .collect::<std::result::Result<HashSet<_>, _>>()?;
+
+        Ok(task)
+    }
+
+    // TODO: filter
+    pub fn read_tasks(&self, filter: Vec<TaskFilter>) -> Result<Vec<Task>> {
+        let mut tasks = self
+            .0
+            .prepare("select name, cron, archived, description, id from tasks")?
+            .query_map([], |row| {
+                Ok((
+                    row.get::<_, Uuid>(4)?,
+                    Task {
+                        name: row.get(0)?,
+                        cron: row.get(1)?,
+                        archived: row.get(2)?,
+                        description: row.get(3)?,
+                        categories: HashSet::new(),
+                    },
+                ))
+            })?
+            .collect::<std::result::Result<Vec<_>, _>>()?;
+
+        for (task_id, task) in &mut tasks {
+            let categories = self
+                .0
+                .prepare("select name from tasks_categories join categories where task_id = ?1")?
+                .query_map([*task_id], |row| Ok(Category::new(row.get(0)?)))?
+                .collect::<std::result::Result<HashSet<_>, _>>()?;
+            task.categories = categories;
+        }
+
+        Ok(tasks.into_iter().map(|(_id, task)| task).collect())
+    }
+
+    pub fn update_task(&self, name: impl AsRef<str>, updated_task: &Task) -> Result<()> {
+        let task_id = self.0
+            .prepare("update tasks set name = ?1, cron = ?2, archived = ?3, description = ?4 where name = ?5 returning id")?.query_one( (&updated_task.name, &updated_task.cron, updated_task.archived, &updated_task.description, name.as_ref()), |row| {
+                Ok(row.get::<_, Uuid>(0)?)
+            })?;
+        self.0
+            .execute("delete from tasks_categories where task_id = ?1", [task_id])?;
+        // TODO: clean up orphaned groups....
+        for category in &updated_task.categories {
+            let category_id = Uuid::new_v4();
+            self.0.execute(
+                "insert or ignore into categories ( id, name ) values ( ?1, ?2 )",
+                (category_id, category),
+            )?;
+
+            self.0.execute(
                 "insert into tasks_categories ( task_id, category_id ) values ( ?1, ?2 )",
-                id,
-                category_id
-            )
-            .execute(&self.pool)
-            .await?;
+                [task_id, category_id],
+            )?;
         }
+        Ok(())
+    }
 
-        Ok(task.add_id(id))
+    pub fn delete_task(&self, name: impl AsRef<str>) -> Result<()> {
+        self.0
+            .execute("delete from tasks where name = ?1", [name.as_ref()])?;
+        Ok(())
     }
 
-    pub async fn read_tasks(&self, select: Vec<TaskSelect>) -> Result<Vec<Task>> {
-        Ok(sqlx::query!("select * from tasks")
-            .fetch_all(&self.pool)
-            .await?)
+    pub fn create_log(&self, task_name: impl AsRef<str>, log: &Log) -> Result<()> {
+        self.0.execute("insert into log ( id, task_id, timestamp ) values ( ?1, (select id from tasks where name = ?2), ?3 )", (log.id, task_name.as_ref(), log.timestamp))?;
+        Ok(())
     }
 
-    pub async fn update_tasks(&self, select: Vec<TaskSelect>) -> Result<()> {
-        todo!()
+    pub fn read_logs(&self, filter: Vec<LogFilter>) -> Result<Vec<Log>> {
+        let mut query = "select id, timestamp from log".to_string();
+
+        if !filter.is_empty() {
+            query.push_str(" where ")
+        }
+        query.push_str(
+            &filter
+                .iter()
+                .map(|filter| filter.where_clause())
+                .collect::<Vec<_>>()
+                .join(", "),
+        );
+
+        let logs = self
+            .0
+            .prepare(&query)?
+            .query_map(params_from_iter(filter), |row| {
+                Ok(Log {
+                    id: row.get(0)?,
+                    timestamp: row.get(1)?,
+                })
+            })?
+            .collect::<std::result::Result<Vec<_>, _>>()?;
+
+        Ok(logs)
     }
 
-    pub async fn delete_tasks(&self, select: Vec<TaskSelect>) -> Result<()> {
-        todo!()
+    pub fn delete_log(&self, log: &Log) -> Result<()> {
+        self.0.execute("delete from logs where id = ?1", [log.id])?;
+        Ok(())
     }
 }
 
-pub enum TaskSelect {
-    Name(String),
+pub enum TaskFilter {
     Archived(bool),
     Category(String),
 }
+
+impl ToSql for TaskFilter {
+    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
+        match self {
+            TaskFilter::Archived(a) => a.to_sql(),
+            TaskFilter::Category(c) => c.to_sql(),
+        }
+    }
+}
+
+pub enum LogFilter {
+    Task(String),
+    Before(NaiveDateTime),
+    After(NaiveDateTime),
+}
+
+impl LogFilter {
+    fn where_clause(&self) -> &'static str {
+        match self {
+            LogFilter::Task(_) => "task_id = (select id from tasks where name = ?)",
+            LogFilter::Before(naive_date_time) => "timestamp < ?",
+            LogFilter::After(naive_date_time) => "timestamp > ?",
+        }
+    }
+}
+
+impl ToSql for LogFilter {
+    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
+        match self {
+            LogFilter::Task(n) => n.to_sql(),
+            LogFilter::Before(naive_date_time) => naive_date_time.to_sql(),
+            LogFilter::After(naive_date_time) => naive_date_time.to_sql(),
+        }
+    }
+}
+
+#[cfg(test)]
+mod tests {
+    use std::collections::HashSet;
+
+    use crate::task::{Category, Task};
+
+    use super::Db;
+
+    #[test]
+    fn create_task() {
+        let db = Db::create_connect_memory().unwrap();
+
+        let task = Task::new(
+            "piano".to_string(),
+            None,
+            Some("practice piano".to_string()),
+            Some(HashSet::from([Category::new("music".to_string())])),
+        );
+
+        db.create_task(&task).unwrap();
+    }
+
+    #[test]
+    fn read_task() {
+        let db = Db::create_connect_memory().unwrap();
+
+        let task = Task::new(
+            "piano".to_string(),
+            None,
+            Some("practice piano".to_string()),
+            Some(HashSet::from([Category::new("music".to_string())])),
+        );
+
+        db.create_task(&task).unwrap();
+
+        assert_eq!(task, db.read_task("piano").unwrap());
+    }
+
+    #[test]
+    fn read_tasks() {
+        let db = Db::create_connect_memory().unwrap();
+
+        let piano = Task::new(
+            "piano".to_string(),
+            None,
+            Some("practice piano".to_string()),
+            Some(HashSet::from([Category::new("music".to_string())])),
+        );
+
+        db.create_task(&piano).unwrap();
+
+        let german = Task::new(
+            "german".to_string(),
+            None,
+            Some("study german".to_string()),
+            None,
+        );
+
+        db.create_task(&german).unwrap();
+
+        assert_eq!(vec![piano, german], db.read_tasks(Vec::new()).unwrap());
+    }
+
+    // TODO: task orderings: by day, by week, by what's most urgent.
+
+    #[test]
+    fn filter_tasks() {}
+
+    #[test]
+    fn update_task() {}
+
+    #[test]
+    fn delete_task() {}
+
+    #[test]
+    fn create_log() {}
+
+    #[test]
+    fn read_logs() {}
+
+    #[test]
+    fn filter_logs() {}
+
+    #[test]
+    fn delete_log() {}
+}