feat: db crud functionsHEADmain

1 files changed, 44 insertions, 7 deletions

diff --git a/src/db.rs b/src/db.rs
index 4b07f91..84e748b 100644
--- a/src/db.rs
+++ b/src/db.rs
@@ -83,6 +83,10 @@ impl Db {
                 "insert or ignore into categories ( id, name ) values ( ?1, ?2 )",
                 (category_id, category),
             )?;
+            let category_id = self
+                .0
+                .prepare("select id from categories where name = ?")?
+                .query_one([category], |row| Ok(row.get(0)?))?;
 
             self.0.execute(
                 "insert into tasks_categories ( task_id, category_id ) values ( ?1, ?2 )",
@@ -120,12 +124,31 @@ impl Db {
         Ok(task)
     }
 
-    // TODO: filter and ordering
     pub fn read_tasks(&self, filter: Vec<TaskFilter>) -> Result<Vec<Task>> {
+        let mut query = String::from("select name, cron, archived, description, id from tasks");
+
+        let where_clause = filter
+            .iter()
+            .filter_map(|filter| match filter {
+                TaskFilter::Archived(_) => Some("archived = ?"),
+                TaskFilter::Category(_) => None,
+            })
+            .collect::<Vec<_>>()
+            .join(" and ");
+        if !where_clause.is_empty() {
+            query.push_str(" where ");
+            query.push_str(&where_clause);
+        }
+
+        let archived = filter.iter().filter_map(|filter| match filter {
+            TaskFilter::Archived(b) => Some(b),
+            TaskFilter::Category(_) => None,
+        });
+
         let mut tasks = self
             .0
-            .prepare("select name, cron, archived, description, id from tasks")?
-            .query_map([], |row| {
+            .prepare(&query)?
+            .query_map(params_from_iter(archived), |row| {
                 Ok((
                     row.get::<_, Uuid>(4)?,
                     Task {
@@ -148,6 +171,19 @@ impl Db {
             task.categories = categories;
         }
 
+        // TODO: can i do this filtering within sqlite?
+        let category_filters = filter.iter().filter_map(|filter| match filter {
+            TaskFilter::Archived(_) => None,
+            TaskFilter::Category(c) => Some(Category::new(c.to_string())),
+        });
+
+        for category in category_filters {
+            tasks = tasks
+                .into_iter()
+                .filter(|(_id, task)| task.categories.contains(&category))
+                .collect();
+        }
+
         Ok(tasks.into_iter().map(|(_id, task)| task).collect())
     }
 
@@ -196,7 +232,7 @@ impl Db {
                 .iter()
                 .map(|filter| filter.where_clause())
                 .collect::<Vec<_>>()
-                .join(", "),
+                .join(" and "),
         );
 
         let logs = self
@@ -214,11 +250,12 @@ impl Db {
     }
 
     pub fn delete_log(&self, log: &Log) -> Result<()> {
-        self.0.execute("delete from logs where id = ?1", [log.id])?;
+        self.0.execute("delete from log where id = ?1", [log.id])?;
         Ok(())
     }
 }
 
+// TODO: task filterings: if it's due or not: to be implemented separately
 pub enum TaskFilter {
     Archived(bool),
     Category(String),
@@ -243,8 +280,8 @@ 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 > ?",
+            LogFilter::Before(naive_date_time) => "timestamp <= ?",
+            LogFilter::After(naive_date_time) => "timestamp >= ?",
         }
     }
 }