From 0adf5a093494bdb7f5d5c0f12913133e333ddfad Mon Sep 17 00:00:00 2001
From: Joris
Date: Fri, 31 Jan 2025 22:28:53 +0100
Subject: Migrate to tokio_rusqlite

---
 src/db/categories.rs | 174 ++++++++++++++++++++++++++++-----------------------
 1 file changed, 96 insertions(+), 78 deletions(-)

(limited to 'src/db/categories.rs')

diff --git a/src/db/categories.rs b/src/db/categories.rs
index fafe459..31cb3d0 100644
--- a/src/db/categories.rs
+++ b/src/db/categories.rs
@@ -1,24 +1,38 @@
-use sqlx::sqlite::SqlitePool;
+use tokio_rusqlite::{named_params, Connection, Row};
 
+use crate::db::utils;
 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;
+fn row_to_category(row: &Row) -> Result<Category, rusqlite::Error> {
+    Ok(Category {
+        id: row.get(0)?,
+        name: row.get(1)?,
+        color: row.get(2)?,
+    })
+}
+
+pub async fn list(conn: &Connection) -> Vec<Category> {
+    let query = r#"
+        SELECT
+            id,
+            name,
+            color
+        FROM categories
+        WHERE deleted_at IS NULL
+        ORDER BY name
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+
+            let users = stmt
+                .query_map([], row_to_category)?
+                .collect::<Result<Vec<Category>, _>>()?;
+
+            Ok(users)
+        })
+        .await;
 
     match res {
         Ok(categories) => categories,
@@ -29,26 +43,29 @@ ORDER BY
     }
 }
 
-pub async fn get(pool: &SqlitePool, id: i64) -> Option<Category> {
+pub async fn get(conn: &Connection, id: i64) -> Option<Category> {
     let query = r#"
-SELECT
-    id,
-    name,
-    color
-FROM
-    categories
-WHERE
-    id = ?
-    AND deleted_at IS NULL
+        SELECT
+            id,
+            name,
+            color
+        FROM categories
+        WHERE
+            id = :id
+            AND deleted_at IS NULL
     "#;
 
-    let res = sqlx::query_as::<_, Category>(query)
-        .bind(id)
-        .fetch_one(pool)
+    let res = conn
+        .call(move |conn| {
+            let mut stmt = conn.prepare(query)?;
+            let mut iter =
+                stmt.query_map(named_params![":id": id], row_to_category)?;
+            utils::one(&mut iter)
+        })
         .await;
 
     match res {
-        Ok(p) => Some(p),
+        Ok(category) => Some(category),
         Err(err) => {
             log::error!("Error looking for category {}: {:?}", id, err);
             None
@@ -56,22 +73,21 @@ WHERE
     }
 }
 
-pub async fn create(pool: &SqlitePool, c: &Create) -> Option<i64> {
-    let res = sqlx::query(
-        r#"
-INSERT INTO
-    categories(name, color)
-VALUES
-    (?, ?)
-    "#,
-    )
-    .bind(c.name.clone())
-    .bind(c.color.clone())
-    .execute(pool)
-    .await;
+pub async fn create(conn: &Connection, c: Create) -> Option<i64> {
+    let query = r#"INSERT INTO categories(name, color) VALUES (:name, :color)"#;
+
+    let res = conn
+        .call(move |conn| {
+            conn.execute(
+                query,
+                named_params![":name": c.name, ":color": c.color],
+            )?;
+            Ok(conn.last_insert_rowid())
+        })
+        .await;
 
     match res {
-        Ok(x) => Some(x.last_insert_rowid()),
+        Ok(category_id) => Some(category_id),
         Err(err) => {
             log::error!("Error creating category: {:?}", err);
             None
@@ -79,24 +95,24 @@ VALUES
     }
 }
 
-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;
+pub async fn update(conn: &Connection, id: i64, c: Update) -> bool {
+    let query = r#"
+        UPDATE categories
+        SET
+            name = :name,
+            color = :color,
+            updated_at = datetime()
+        WHERE id = :id
+    "#;
+
+    let res = conn
+        .call(move |conn| {
+            Ok(conn.execute(
+                query,
+                named_params![":name": c.name, ":color": c.color, ":id": id],
+            )?)
+        })
+        .await;
 
     match res {
         Ok(_) => true,
@@ -107,20 +123,22 @@ WHERE
     }
 }
 
-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;
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+    let res = conn
+        .call(move |conn| {
+            Ok(conn.execute(
+                r#"
+            UPDATE
+                categories
+            SET
+                deleted_at = datetime()
+            WHERE
+                id = :id
+            "#,
+                named_params![":id": id],
+            )?)
+        })
+        .await;
 
     match res {
         Ok(_) => true,
-- 
cgit v1.2.3