1 files changed, 132 insertions, 0 deletions
diff --git a/src/db/categories.rs b/src/db/categories.rs
new file mode 100644
index 0000000..05b1323
--- /dev/null
+++ b/src/db/categories.rs
@@ -0,0 +1,132 @@
+use sqlx::sqlite::SqlitePool;
+
+use crate::model::category::{Category, Create, Update};
+
+pub async fn list(pool: &SqlitePool) -> Vec<Category> {
+    let res = sqlx::query_as::<_, Category>(
+        r#"
+SELECT
+    id,
+    name,
+    color
+FROM
+    categories
+WHERE
+    deleted_at IS NULL
+ORDER BY
+    name
+    "#,
+    )
+    .fetch_all(pool)
+    .await;
+
+    match res {
+        Ok(categories) => categories,
+        Err(err) => {
+            error!("Error listing categories: {:?}", err);
+            vec![]
+        }
+    }
+}
+
+pub async fn get(pool: &SqlitePool, id: i64) -> Option<Category> {
+    let query = r#"
+SELECT
+    id,
+    name,
+    color
+FROM
+    categories
+WHERE
+    id = ?
+    AND deleted_at IS NULL
+    "#;
+
+    let res = sqlx::query_as::<_, Category>(query)
+        .bind(id)
+        .fetch_one(pool)
+        .await;
+
+    match res {
+        Ok(p) => Some(p),
+        Err(err) => {
+            error!("Error looking for category {}: {:?}", id, err);
+            None
+        }
+    }
+}
+
+pub async fn create(pool: &SqlitePool, c: &Create) -> Option<i64> {
+    let res = sqlx::query(
+        r#"
+INSERT INTO
+    categories(name, color, created_at)
+VALUES
+    (?, ?, datetime())
+    "#,
+    )
+    .bind(c.name.clone())
+    .bind(c.color.clone())
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(x) => Some(x.last_insert_rowid()),
+        Err(err) => {
+            error!("Error creating category: {:?}", err);
+            None
+        }
+    }
+}
+
+pub async fn update(pool: &SqlitePool, id: i64, c: &Update) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    categories
+SET
+    name = ?,
+    color = ?,
+    updated_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(c.name.clone())
+    .bind(c.color.clone())
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error updating category {}: {:?}", id, err);
+            false
+        }
+    }
+}
+
+pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
+    let res = sqlx::query(
+        r#"
+UPDATE
+    categories
+SET
+    deleted_at = datetime()
+WHERE
+    id = ?
+    "#,
+    )
+    .bind(id)
+    .execute(pool)
+    .await;
+
+    match res {
+        Ok(_) => true,
+        Err(err) => {
+            error!("Error deleting category {}: {:?}", id, err);
+            false
+        }
+    }
+}