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/incomes.rs | 656 +++++++++++++++++++++++++++++------------------------- 1 file changed, 356 insertions(+), 300 deletions(-) (limited to 'src/db/incomes.rs') diff --git a/src/db/incomes.rs b/src/db/incomes.rs index 97cb2b7..90282c0 100644 --- a/src/db/incomes.rs +++ b/src/db/incomes.rs @@ -1,26 +1,51 @@ use chrono::NaiveDate; -use sqlx::error::Error; -use sqlx::sqlite::{SqlitePool, SqliteRow}; -use sqlx_core::row::Row; use std::collections::HashMap; use std::iter::FromIterator; +use tokio_rusqlite::{named_params, Connection, Row}; +use crate::db::utils; use crate::model::income::{Create, Form, Stat, Table, Update}; use crate::model::report::Report; -pub async fn count(pool: &SqlitePool) -> i64 { +fn row_to_table(row: &Row) -> Result { + Ok(Table { + id: row.get(0)?, + date: row.get(1)?, + user: row.get(2)?, + amount: row.get(3)?, + }) +} + +fn row_to_form(row: &Row) -> Result { + Ok(Form { + id: row.get(0)?, + amount: row.get(1)?, + user_id: row.get(2)?, + month: row.get(3)?, + year: row.get(4)?, + }) +} + +fn row_to_stat(row: &Row) -> Result { + Ok(Stat { + date: row.get(0)?, + amount: row.get(1)?, + }) +} + +pub async fn count(conn: &Connection) -> i64 { let query = r#" -SELECT - COUNT(*) AS count -FROM - incomes -WHERE - incomes.deleted_at IS NULL - "#; - - let res = sqlx::query(query) - .map(|row: SqliteRow| row.get("count")) - .fetch_one(pool) + SELECT COUNT(*) + FROM incomes + WHERE incomes.deleted_at IS NULL + "#; + + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(query)?; + let mut iter = stmt.query_map([], |row| row.get(0))?; + utils::one::(&mut iter) + }) .await; match res { @@ -32,32 +57,35 @@ WHERE } } -pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec { +pub async fn list(conn: &Connection, page: i64, per_page: i64) -> Vec
{ let query = r#" -SELECT - incomes.id, - users.name AS user, - strftime('%m/%Y', incomes.date) AS date, - incomes.amount -FROM - incomes -INNER JOIN - users -ON - incomes.user_id = users.id -WHERE - incomes.deleted_at IS NULL -ORDER BY - incomes.date DESC -LIMIT ? -OFFSET ? + SELECT + incomes.id, + users.name AS user, + strftime('%m/%Y', incomes.date) AS date, + incomes.amount + FROM incomes + INNER JOIN users + ON incomes.user_id = users.id + WHERE incomes.deleted_at IS NULL + ORDER BY incomes.date DESC + LIMIT :limit + OFFSET :offset "#; - let res = sqlx::query_as::<_, Table>(query) - .bind(per_page) - .bind((page - 1) * per_page) - .fetch_all(pool) - .await; + let res = conn.call(move |conn| { + let mut stmt = conn.prepare(query)?; + + let incomes = stmt + .query_map( + named_params![":limit": per_page, ":offset": (page - 1) * per_page], + row_to_table + )? + .collect::, _>>()?; + + Ok(incomes) + }) + .await; match res { Ok(incomes) => incomes, @@ -68,31 +96,30 @@ OFFSET ? } } -pub async fn get_row(pool: &SqlitePool, id: i64) -> i64 { +pub async fn get_row(conn: &Connection, id: i64) -> i64 { let query = r#" -SELECT - row -FROM ( - SELECT - ROW_NUMBER () OVER (ORDER BY date DESC) AS row, - id - FROM - incomes - WHERE - deleted_at IS NULL -) -WHERE - id = ? + SELECT row + FROM ( + SELECT + ROW_NUMBER () OVER (ORDER BY date DESC) AS row, + id + FROM incomes + WHERE deleted_at IS NULL + ) + WHERE id = :id "#; - let res = sqlx::query(query) - .bind(id) - .map(|row: SqliteRow| row.get("row")) - .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| row.get(0))?; + utils::one::(&mut iter) + }) .await; match res { - Ok(count) => count, + Ok(row) => row, Err(err) => { log::error!("Error getting income row: {:?}", err); 1 @@ -100,28 +127,31 @@ WHERE } } -pub async fn get(pool: &SqlitePool, id: i64) -> Option
{ +pub async fn get(conn: &Connection, id: i64) -> Option { let query = r#" -SELECT - id, - amount, - user_id, - CAST(strftime('%m', date) AS INTEGER) as month, - CAST(strftime('%Y', date) AS INTEGER) as year -FROM - incomes -WHERE - id = ? - AND deleted_at IS NULL + SELECT + id, + amount, + user_id, + CAST(strftime('%m', date) AS INTEGER) as month, + CAST(strftime('%Y', date) AS INTEGER) as year + FROM incomes + WHERE + id = :id + AND deleted_at IS NULL "#; - let res = sqlx::query_as::<_, Form>(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_form)?; + utils::one(&mut iter) + }) .await; match res { - Ok(p) => Some(p), + Ok(income) => Some(income), Err(err) => { log::error!("Error looking for income {}: {:?}", id, err); None @@ -129,23 +159,28 @@ WHERE } } -pub async fn create(pool: &SqlitePool, i: &Create) -> Option { - let res = sqlx::query( - r#" -INSERT INTO - incomes(user_id, date, amount) -VALUES - (?, ?, ?) - "#, - ) - .bind(i.user_id) - .bind(i.date) - .bind(i.amount) - .execute(pool) - .await; +pub async fn create(conn: &Connection, i: Create) -> Option { + let query = r#" + INSERT INTO incomes(user_id, date, amount) + VALUES (:user_id, :date, :amount) + "#; + + let res = conn + .call(move |conn| { + conn.execute( + query, + named_params![ + ":user_id": i.user_id, + ":date": i.date, + ":amount": i.amount + ], + )?; + Ok(conn.last_insert_rowid()) + }) + .await; match res { - Ok(x) => Some(x.last_insert_rowid()), + Ok(income_id) => Some(income_id), Err(err) => { log::error!("Error creating income: {:?}", err); None @@ -154,31 +189,36 @@ VALUES } pub async fn defined_at( - pool: &SqlitePool, + conn: &Connection, user_id: i64, date: NaiveDate, ) -> Vec { let query = r#" -SELECT - id -FROM - incomes -WHERE - user_id = ? - AND date = ? - AND deleted_at IS NULL + SELECT id + FROM incomes + WHERE + user_id = :user_id + AND date = :date + AND deleted_at IS NULL "#; - let res = sqlx::query(query) - .bind(user_id) - .bind(date) - .map(|row: SqliteRow| row.get("id")) - .fetch_all(pool) + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(query)?; + + let incomes = stmt + .query_map( + named_params![":user_id": user_id, ":date": date], + |row| row.get(0), + )? + .collect::, _>>()?; + + Ok(incomes) + }) .await; match res { Ok(ids) => ids, - Err(Error::RowNotFound) => vec![], Err(err) => { log::error!("Error looking if income is defined: {:?}", err); vec![] @@ -186,26 +226,30 @@ WHERE } } -pub async fn update(pool: &SqlitePool, id: i64, i: &Update) -> bool { - let res = sqlx::query( - r#" -UPDATE - incomes -SET - user_id = ?, - date = ?, - amount = ?, - updated_at = datetime() -WHERE - id = ? - "#, - ) - .bind(i.user_id) - .bind(i.date) - .bind(i.amount) - .bind(id) - .execute(pool) - .await; +pub async fn update(conn: &Connection, id: i64, i: Update) -> bool { + let query = r#" + UPDATE incomes + SET + user_id = :user_id, + date = :date, + amount = :amount, + updated_at = datetime() + WHERE id = :id + "#; + + let res = conn + .call(move |conn| { + Ok(conn.execute( + query, + named_params![ + ":user_id": i.user_id, + ":date": i.date, + ":amount": i.amount, + ":id": id + ], + )?) + }) + .await; match res { Ok(_) => true, @@ -216,20 +260,12 @@ WHERE } } -pub async fn delete(pool: &SqlitePool, id: i64) -> bool { - let res = sqlx::query( - r#" -UPDATE - incomes -SET - deleted_at = datetime() -WHERE - id = ? - "#, - ) - .bind(id) - .execute(pool) - .await; +pub async fn delete(conn: &Connection, id: i64) -> bool { + let query = r#"UPDATE incomes SET deleted_at = datetime() WHERE id = :id"#; + + let res = conn + .call(move |conn| Ok(conn.execute(query, named_params![":id": id])?)) + .await; match res { Ok(_) => true, @@ -240,37 +276,34 @@ WHERE } } -pub async fn defined_for_all(pool: &SqlitePool) -> Option { - let res = sqlx::query( - r#" -SELECT - (CASE COUNT(users.id) == COUNT(min_income.date) - WHEN 1 THEN MIN(min_income.date) - ELSE NULL - END) AS date -FROM - users -LEFT OUTER JOIN ( - SELECT - user_id, - MIN(date) AS date - FROM - incomes - WHERE - deleted_at IS NULL - GROUP BY - user_id -) min_income -ON - users.id = min_income.user_id; - "#, - ) - .map(|row: SqliteRow| row.get("date")) - .fetch_one(pool) - .await; +pub async fn defined_for_all(conn: &Connection) -> Option { + let query = r#" + SELECT + (CASE COUNT(users.id) == COUNT(min_income.date) + WHEN 1 THEN MIN(min_income.date) + ELSE NULL + END) AS date + FROM users + LEFT OUTER JOIN + (SELECT + user_id, + MIN(date) AS date + FROM incomes + WHERE deleted_at IS NULL + GROUP BY user_id) min_income + ON users.id = min_income.user_id; + "#; + + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(query)?; + let mut iter = stmt.query_map([], |row| row.get(0))?; + utils::one::(&mut iter) + }) + .await; match res { - Ok(d) => d, + Ok(d) => Some(d), Err(err) => { log::error!("Error looking for incomes defined for all: {:?}", err); None @@ -279,12 +312,17 @@ ON } pub async fn cumulative( - pool: &SqlitePool, + conn: &Connection, from: NaiveDate, ) -> HashMap { - let res = sqlx::query(&cumulative_query(from)) - .map(|row: SqliteRow| (row.get("user_id"), row.get("income"))) - .fetch_all(pool) + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(&cumulative_query(from))?; + let incomes = stmt + .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))? + .collect::, _>>()?; + Ok(incomes) + }) .await; match res { @@ -306,34 +344,34 @@ pub async fn cumulative( fn cumulative_query(from: NaiveDate) -> String { format!( r#" -SELECT - users.id AS user_id, - COALESCE(incomes.income, 0) AS income -FROM - users -LEFT OUTER JOIN ( - SELECT - user_id, - CAST(ROUND(SUM(count)) AS INTEGER) AS income - FROM ( SELECT - I1.user_id, - ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count + users.id AS user_id, + COALESCE(incomes.income, 0) AS income FROM - ({}) AS I1 - INNER JOIN - ({}) AS I2 + users + LEFT OUTER JOIN ( + SELECT + user_id, + CAST(ROUND(SUM(count)) AS INTEGER) AS income + FROM ( + SELECT + I1.user_id, + ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count + FROM + ({}) AS I1 + INNER JOIN + ({}) AS I2 + ON + I2.date > I1.date + AND I2.user_id == I1.user_id + GROUP BY + I1.date, I1.user_id + ) + GROUP BY + user_id + ) incomes ON - I2.date > I1.date - AND I2.user_id == I1.user_id - GROUP BY - I1.date, I1.user_id - ) - GROUP BY - user_id -) incomes -ON - users.id = incomes.user_id + users.id = incomes.user_id "#, bounded_query(">".to_string(), from.format("%Y-%m-%d").to_string()), bounded_query("<".to_string(), "date()".to_string()) @@ -347,33 +385,33 @@ ON fn bounded_query(op: String, date: String) -> String { format!( r#" -SELECT - user_id, - date, - amount -FROM ( - SELECT - user_id, - {} AS date, - amount, - MAX(date) AS max_date - FROM - incomes - WHERE - date <= {} - AND deleted_at IS NULL - GROUP BY - user_id -) UNION -SELECT - user_id, - date, - amount -FROM - incomes -WHERE - date {} {} - AND deleted_at IS NULL + SELECT + user_id, + date, + amount + FROM ( + SELECT + user_id, + {} AS date, + amount, + MAX(date) AS max_date + FROM + incomes + WHERE + date <= {} + AND deleted_at IS NULL + GROUP BY + user_id + ) UNION + SELECT + user_id, + date, + amount + FROM + incomes + WHERE + date {} {} + AND deleted_at IS NULL "#, date, date, op, date ) @@ -383,54 +421,63 @@ WHERE /// /// For each month, from the first defined income and until now, /// compute the total income of the users. -pub async fn total_each_month(pool: &SqlitePool) -> Vec { +pub async fn total_each_month(conn: &Connection) -> Vec { let query = r#" -WITH RECURSIVE dates(date) AS ( - VALUES(( - SELECT - strftime('%Y-%m-01', MIN(date)) - FROM - incomes - WHERE - deleted_at IS NULL - )) - UNION ALL - SELECT - date(date, '+1 month') - FROM - dates - WHERE - date < date(date(), '-1 month') -) -SELECT - strftime('%Y-%m-01', dates.date) AS date, - ( - SELECT - SUM(amount) AS amount - FROM ( - SELECT ( + WITH RECURSIVE dates(date) AS ( + VALUES(( SELECT - amount + strftime('%Y-%m-01', MIN(date)) FROM incomes WHERE - user_id = users.id - AND date < date(dates.date, '+1 month') - AND deleted_at IS NULL - ORDER BY - date DESC - LIMIT - 1 - ) AS amount + deleted_at IS NULL + )) + UNION ALL + SELECT + date(date, '+1 month') FROM - users + dates + WHERE + date < date(date(), '-1 month') ) - ) AS amount -FROM - dates; + SELECT + strftime('%Y-%m-01', dates.date) AS date, + ( + SELECT + SUM(amount) AS amount + FROM ( + SELECT ( + SELECT + amount + FROM + incomes + WHERE + user_id = users.id + AND date < date(dates.date, '+1 month') + AND deleted_at IS NULL + ORDER BY + date DESC + LIMIT + 1 + ) AS amount + FROM + users + ) + ) AS amount + FROM + dates; "#; - let res = sqlx::query_as::<_, Stat>(query).fetch_all(pool).await; + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(query)?; + let stats = stmt + .query_map([], row_to_stat)? + .collect::, _>>()?; + + Ok(stats) + }) + .await; match res { Ok(xs) => xs, @@ -441,50 +488,59 @@ FROM } } -pub async fn last_week(pool: &SqlitePool) -> Vec { +pub async fn last_week(conn: &Connection) -> Vec { let query = r#" -SELECT - strftime('%m/%Y', incomes.date) AS date, - users.name AS name, - incomes.amount AS amount, - (CASE - WHEN - incomes.deleted_at IS NOT NULL - THEN - 'Deleted' - WHEN - incomes.updated_at IS NOT NULL - AND incomes.created_at < date('now', 'weekday 0', '-13 days') - THEN - 'Updated' - ELSE - 'Created' - END) AS action -FROM - incomes -INNER JOIN - users -ON - incomes.user_id = users.id -WHERE - ( - incomes.created_at >= date('now', 'weekday 0', '-13 days') - AND incomes.created_at < date('now', 'weekday 0', '-6 days') - ) OR ( - incomes.updated_at >= date('now', 'weekday 0', '-13 days') - AND incomes.updated_at < date('now', 'weekday 0', '-6 days') - ) OR ( - incomes.deleted_at >= date('now', 'weekday 0', '-13 days') - AND incomes.deleted_at < date('now', 'weekday 0', '-6 days') - ) -ORDER BY - incomes.date + SELECT + strftime('%m/%Y', incomes.date) AS date, + users.name AS name, + incomes.amount AS amount, + (CASE + WHEN + incomes.deleted_at IS NOT NULL + THEN + 'Deleted' + WHEN + incomes.updated_at IS NOT NULL + AND incomes.created_at < date('now', 'weekday 0', '-13 days') + THEN + 'Updated' + ELSE + 'Created' + END) AS action + FROM + incomes + INNER JOIN + users + ON + incomes.user_id = users.id + WHERE + ( + incomes.created_at >= date('now', 'weekday 0', '-13 days') + AND incomes.created_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.updated_at >= date('now', 'weekday 0', '-13 days') + AND incomes.updated_at < date('now', 'weekday 0', '-6 days') + ) OR ( + incomes.deleted_at >= date('now', 'weekday 0', '-13 days') + AND incomes.deleted_at < date('now', 'weekday 0', '-6 days') + ) + ORDER BY + incomes.date "#; - let res = sqlx::query_as::<_, Report>(query).fetch_all(pool).await; + let res = conn + .call(move |conn| { + let mut stmt = conn.prepare(query)?; + let xs = stmt + .query_map([], utils::row_to_report)? + .collect::, _>>()?; + + Ok(xs) + }) + .await; match res { - Ok(payments) => payments, + Ok(xs) => xs, Err(err) => { log::error!("Error listing payments for report: {:?}", err); vec![] -- cgit v1.2.3