aboutsummaryrefslogtreecommitdiff
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/categories.rs174
-rw-r--r--src/db/incomes.rs656
-rw-r--r--src/db/jobs.rs42
-rw-r--r--src/db/payments.rs827
-rw-r--r--src/db/users.rs180
-rw-r--r--src/db/utils.rs24
6 files changed, 972 insertions, 931 deletions
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,
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<Table, rusqlite::Error> {
+ 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<Form, rusqlite::Error> {
+ 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<Stat, rusqlite::Error> {
+ 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::<i64, _>(&mut iter)
+ })
.await;
match res {
@@ -32,32 +57,35 @@ WHERE
}
}
-pub async fn list(pool: &SqlitePool, page: i64, per_page: i64) -> Vec<Table> {
+pub async fn list(conn: &Connection, page: i64, per_page: i64) -> Vec<Table> {
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::<Result<Vec<Table>, _>>()?;
+
+ 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::<i64, _>(&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<Form> {
+pub async fn get(conn: &Connection, id: i64) -> Option<Form> {
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<i64> {
- 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<i64> {
+ 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<i64> {
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::<Result<Vec<i64>, _>>()?;
+
+ 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<NaiveDate> {
- 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<NaiveDate> {
+ 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::<NaiveDate, _>(&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<i64, i64> {
- 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::<Result<Vec<(i64, i64)>, _>>()?;
+ 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<Stat> {
+pub async fn total_each_month(conn: &Connection) -> Vec<Stat> {
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::<Result<Vec<Stat>, _>>()?;
+
+ Ok(stats)
+ })
+ .await;
match res {
Ok(xs) => xs,
@@ -441,50 +488,59 @@ FROM
}
}
-pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+pub async fn last_week(conn: &Connection) -> Vec<Report> {
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::<Result<Vec<Report>, _>>()?;
+
+ Ok(xs)
+ })
+ .await;
match res {
- Ok(payments) => payments,
+ Ok(xs) => xs,
Err(err) => {
log::error!("Error listing payments for report: {:?}", err);
vec![]
diff --git a/src/db/jobs.rs b/src/db/jobs.rs
index 7d9386a..1d00408 100644
--- a/src/db/jobs.rs
+++ b/src/db/jobs.rs
@@ -1,32 +1,27 @@
-use sqlx::error::Error;
-use sqlx::sqlite::SqlitePool;
+use tokio_rusqlite::{named_params, Connection};
use crate::model::job::Job;
-pub async fn should_run(pool: &SqlitePool, job: Job) -> bool {
+pub async fn should_run(conn: &Connection, job: Job) -> bool {
let run_from = match job {
Job::WeeklyReport => "date('now', 'weekday 0', '-6 days')",
Job::MonthlyPayment => "date('now', 'start of month')",
};
let query = format!(
- r#"
-SELECT
- 1
-FROM
- jobs
-WHERE
- name = ?
- AND last_execution < {}
- "#,
+ r#"SELECT 1 FROM jobs WHERE name = ? AND last_execution < {}"#,
run_from
);
- let res = sqlx::query(&query).bind(job).fetch_one(pool).await;
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ Ok(stmt.exists([job.to_string()])?)
+ })
+ .await;
match res {
- Ok(_) => true,
- Err(Error::RowNotFound) => false,
+ Ok(r) => r,
Err(err) => {
log::error!("Error looking if job should run: {:?}", err);
false
@@ -34,17 +29,18 @@ WHERE
}
}
-pub async fn actualize_last_execution(pool: &SqlitePool, job: Job) {
+pub async fn actualize_last_execution(conn: &Connection, job: Job) {
let query = r#"
-UPDATE
- jobs
-SET
- last_execution = datetime()
-WHERE
- name = ?
+ UPDATE jobs
+ SET last_execution = datetime()
+ WHERE name = :name
"#;
- let res = sqlx::query(query).bind(job).execute(pool).await;
+ let res = conn
+ .call(move |conn| {
+ Ok(conn.execute(query, named_params![":name": job.to_string()])?)
+ })
+ .await;
match res {
Ok(_) => (),
diff --git a/src/db/payments.rs b/src/db/payments.rs
index 25b10f4..540a006 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -1,10 +1,6 @@
-use sqlx::error::Error;
-use sqlx::sqlite::{Sqlite, SqliteArguments};
-use sqlx::sqlite::{SqlitePool, SqliteRow};
-use sqlx::FromRow;
-use sqlx_core::row::Row;
use std::collections::HashMap;
use std::iter::FromIterator;
+use tokio_rusqlite::{Connection, Row, params_from_iter, types::ToSql, named_params};
use crate::db::utils;
use crate::model::frequency::Frequency;
@@ -13,71 +9,75 @@ use crate::model::report::Report;
use crate::queries;
use crate::utils::text;
-#[derive(FromRow)]
pub struct Count {
pub count: i64,
pub total_cost: i64,
}
+fn row_to_count(row: &Row) -> Result<Count, rusqlite::Error> {
+ Ok(Count {
+ count: row.get(0)?,
+ total_cost: row.get(1)?,
+ })
+}
+
+fn row_to_table(row: &Row) -> Result<payment::Table, rusqlite::Error> {
+ Ok(payment::Table {
+ id: row.get(0)?,
+ name: row.get(1)?,
+ cost: row.get(2)?,
+ user: row.get(3)?,
+ category_name: row.get(4)?,
+ category_color: row.get(5)?,
+ date: row.get(6)?,
+ frequency: row.get(7)?
+ })
+}
+
+fn row_to_stat(row: &Row) -> Result<payment::Stat, rusqlite::Error> {
+ Ok(payment::Stat {
+ start_date: row.get(0)?,
+ cost: row.get(1)?,
+ category_id: row.get(2)?,
+ })
+}
+
+fn row_to_form(row: &Row) -> Result<payment::Form, rusqlite::Error> {
+ Ok(payment::Form {
+ id: row.get(0)?,
+ name: row.get(1)?,
+ cost: row.get(2)?,
+ user_id: row.get(3)?,
+ category_id: row.get(4)?,
+ date: row.get(5)?,
+ frequency: row.get(6)?
+ })
+}
+
pub async fn count(
- pool: &SqlitePool,
+ conn: &Connection,
payment_query: &queries::Payments,
) -> Count {
- let query = format!(
- r#"
-SELECT
- COUNT(*) AS count,
- SUM(payments.cost) AS total_cost
-FROM
- payments
-INNER JOIN
- users ON users.id = payments.user_id
-INNER JOIN
- categories ON categories.id = payments.category_id
-WHERE
- payments.deleted_at IS NULL
- AND payments.frequency = ?
- {} {} {} {} {} {}
- "#,
- name_query(payment_query.name.clone()),
- cost_query(payment_query.cost.clone()),
- user_query(payment_query.user),
- category_query(payment_query.category),
- date_query(
- "payments.date >=".to_string(),
- payment_query.start_date.clone()
- ),
- date_query(
- "payments.date <=".to_string(),
- payment_query.end_date.clone()
- )
- );
-
- let res = bind_date(
- bind_date(
- bind_category(
- bind_user(
- bind_cost(
- bind_name(
- sqlx::query_as::<_, Count>(&query).bind(
- payment_query
- .frequency
- .unwrap_or(Frequency::Punctual),
- ),
- payment_query.name.clone(),
- ),
- payment_query.cost.clone(),
- ),
- payment_query.user,
- ),
- payment_query.category,
- ),
- payment_query.start_date.clone(),
- ),
- payment_query.end_date.clone(),
- )
- .fetch_one(pool)
- .await;
+ let mut query = format!(r#"
+ SELECT
+ COUNT(*) AS count,
+ SUM(payments.cost) AS total_cost
+ FROM payments
+ INNER JOIN users ON users.id = payments.user_id
+ INNER JOIN categories ON categories.id = payments.category_id
+ WHERE payments.deleted_at IS NULL
+ "#);
+
+ let mut params = Vec::<Box<dyn ToSql + Send>>::new();
+ complete_search_query(payment_query, &mut query, &mut params);
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let mut iter = stmt.query_map(params_from_iter(params), row_to_count)?;
+ utils::one(&mut iter)
+ })
+ .await;
match res {
Ok(count) => count,
@@ -92,79 +92,42 @@ WHERE
}
pub async fn list_for_table(
- pool: &SqlitePool,
+ conn: &Connection,
payment_query: &queries::Payments,
per_page: i64,
) -> Vec<payment::Table> {
- let offset = (payment_query.page.unwrap_or(1) - 1) * per_page;
-
- let query = format!(
- r#"
-SELECT
- payments.id,
- payments.name,
- payments.cost,
- users.name AS user,
- categories.name AS category_name,
- categories.color AS category_color,
- strftime('%d/%m/%Y', date) AS date,
- payments.frequency AS frequency
-FROM
- payments
-INNER JOIN
- users ON users.id = payments.user_id
-INNER JOIN
- categories ON categories.id = payments.category_id
-WHERE
- payments.deleted_at IS NULL
- AND payments.frequency = ?
- {} {} {} {} {} {}
-ORDER BY
- payments.date DESC
-LIMIT ?
-OFFSET ?
- "#,
- name_query(payment_query.name.clone()),
- cost_query(payment_query.cost.clone()),
- user_query(payment_query.user),
- category_query(payment_query.category),
- date_query(
- "payments.date >=".to_string(),
- payment_query.start_date.clone()
- ),
- date_query(
- "payments.date <=".to_string(),
- payment_query.end_date.clone()
- )
- );
-
- let res = bind_date(
- bind_date(
- bind_category(
- bind_user(
- bind_cost(
- bind_name(
- sqlx::query_as::<_, payment::Table>(&query).bind(
- payment_query
- .frequency
- .unwrap_or(Frequency::Punctual),
- ),
- payment_query.name.clone(),
- ),
- payment_query.cost.clone(),
- ),
- payment_query.user,
- ),
- payment_query.category,
- ),
- payment_query.start_date.clone(),
- ),
- payment_query.end_date.clone(),
- )
- .bind(per_page)
- .bind(offset)
- .fetch_all(pool)
- .await;
+ let mut query = format!(r#"
+ SELECT
+ payments.id,
+ payments.name,
+ payments.cost,
+ users.name AS user,
+ categories.name AS category_name,
+ categories.color AS category_color,
+ strftime('%d/%m/%Y', date) AS date,
+ payments.frequency AS frequency
+ FROM payments
+ INNER JOIN users ON users.id = payments.user_id
+ INNER JOIN categories ON categories.id = payments.category_id
+ WHERE payments.deleted_at IS NULL
+ "#);
+
+ let mut params = Vec::<Box<dyn ToSql + Send>>::new();
+ complete_search_query(payment_query, &mut query, &mut params);
+
+ query.push_str("ORDER BY payments.date DESC LIMIT ? OFFSET ?");
+ params.push(Box::new(per_page));
+ params.push(Box::new((payment_query.page.unwrap_or(1) - 1) * per_page));
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let payments = stmt
+ .query_map(params_from_iter(params), row_to_table)?
+ .collect::<Result<Vec<payment::Table>, _>>()?;
+ Ok(payments)
+ })
+ .await;
match res {
Ok(payments) => payments,
@@ -175,137 +138,101 @@ OFFSET ?
}
}
-fn name_query(name: Option<String>) -> String {
- if name.map_or_else(|| false, |str| !str.is_empty()) {
- format!(
- "AND {} LIKE ?",
- utils::format_key_for_search("payments.name")
- )
- } else {
- "".to_string()
- }
-}
-
-fn bind_name<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- name: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match name {
- Some(str) => {
- if str.is_empty() {
- query
- } else {
- query.bind(text::format_search(&str))
- }
- }
- _ => query,
- }
+fn complete_search_query(
+ q: &queries::Payments,
+ query: &mut String,
+ params: &mut Vec<Box<dyn ToSql + Send>>
+) {
+ complete_frequency(q.frequency, query, params);
+ complete_name(q.name.clone(), query, params);
+ complete_cost(q.cost.clone(), query, params);
+ complete_user(q.user, query, params);
+ complete_category(q.category, query, params);
+ complete_date("payments.date >=".to_string(), q.start_date.clone(), query, params);
+ complete_date("payments.date <=".to_string(), q.end_date.clone(), query, params);
}
-fn cost_query(cost: Option<String>) -> String {
- if cost.map_or_else(|| false, |str| !str.is_empty()) {
- "AND payments.cost = ?".to_string()
- } else {
- "".to_string()
- }
+fn complete_frequency(frequency: Option<Frequency>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ query.push_str("AND payments.frequency = ?");
+ params.push(Box::new(frequency.unwrap_or(Frequency::Punctual).to_string()));
}
-fn bind_cost<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- cost: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match cost {
- Some(str) => {
- if str.is_empty() {
- query
- } else {
- query.bind(str)
- }
+fn complete_name(name: Option<String>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(name) = name {
+ if !name.is_empty() {
+ query.push_str(format!(
+ "AND {} LIKE ?",
+ utils::format_key_for_search("payments.name")
+ ).as_str());
+ params.push(Box::new(name));
}
- _ => query,
- }
-}
-
-fn user_query(user: Option<i64>) -> String {
- if user.is_some() {
- "AND payments.user_id = ?".to_string()
- } else {
- "".to_string()
}
}
-fn bind_user<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- user: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match user {
- Some(id) => query.bind(id),
- _ => query,
- }
-}
-
-fn category_query(category: Option<i64>) -> String {
- if category.is_some() {
- "AND payments.category_id = ?".to_string()
- } else {
- "".to_string()
+fn complete_cost(cost: Option<String>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(cost) = cost {
+ if !cost.is_empty() {
+ query.push_str("AND payments.cost = ?");
+ params.push(Box::new(cost))
+ }
}
}
-fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
- category: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match category {
- Some(id) => query.bind(id),
- _ => query,
+fn complete_user(user: Option<i64>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(user) = user {
+ query.push_str("AND payments.user_id = ?");
+ params.push(Box::new(user))
}
}
-fn date_query(name_and_op: String, date: Option<String>) -> String {
- if date.map_or_else(|| false, |str| !str.is_empty()) {
- format!("AND {} ?", name_and_op)
- } else {
- "".to_string()
+fn complete_category(category: Option<i64>, query: &mut String, params: &mut Vec<Box<dyn ToSql + Send>>) {
+ if let Some(category) = category {
+ query.push_str("AND payments.category_id = ?");
+ params.push(Box::new(category));
}
}
-fn bind_date<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+fn complete_date(
+ name_and_op: String,
date: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match date {
- Some(d) => {
- if d.is_empty() {
- query
- } else {
- query.bind(d)
- }
+ query: &mut String,
+ params: &mut Vec<Box<dyn ToSql + Send>>
+) {
+ if let Some(date) = date {
+ if !date.is_empty() {
+ query.push_str(format!("AND {name_and_op} ?").as_str());
+ params.push(Box::new(date));
}
- _ => query,
}
}
-pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
+
+pub async fn list_for_stats(conn: &Connection) -> Vec<payment::Stat> {
let query = r#"
-SELECT
- strftime('%Y-%m-01', payments.date) AS start_date,
- SUM(payments.cost) AS cost,
- payments.category_id AS category_id
-FROM
- payments
-WHERE
- payments.deleted_at IS NULL
- AND payments.frequency = 'Punctual'
-GROUP BY
- start_date,
- payments.category_id;
+ SELECT
+ strftime('%Y-%m-01', payments.date) AS start_date,
+ SUM(payments.cost) AS cost,
+ payments.category_id AS category_id
+ FROM
+ payments
+ WHERE
+ payments.deleted_at IS NULL
+ AND payments.frequency = 'Punctual'
+ GROUP BY
+ start_date,
+ payments.category_id;
"#;
- let result = sqlx::query_as::<_, payment::Stat>(query)
- .fetch_all(pool)
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let payments = stmt
+ .query_map([], row_to_stat)?
+ .collect::<Result<Vec<payment::Stat>, _>>()?;
+ Ok(payments)
+ })
.await;
- match result {
+ match res {
Ok(payments) => payments,
Err(err) => {
log::error!("Error listing payments for statistics: {:?}", err);
@@ -314,29 +241,30 @@ GROUP BY
}
}
-pub async fn get_row(pool: &SqlitePool, id: i64, frequency: Frequency) -> i64 {
+pub async fn get_row(conn: &Connection, id: i64, frequency: Frequency) -> i64 {
let query = r#"
-SELECT
- row
-FROM (
- SELECT
- ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
- id
- FROM
- payments
- WHERE
- deleted_at IS NULL
- AND frequency = ?
-)
-WHERE
- id = ?
+ SELECT row
+ FROM (
+ SELECT
+ ROW_NUMBER () OVER (ORDER BY date DESC) AS row,
+ id
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND frequency = :frequency
+ )
+ WHERE id = :id
"#;
- let res = sqlx::query(query)
- .bind(frequency)
- .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, ":frequency": frequency.to_string()],
+ |row| row.get(0)
+ )?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
@@ -348,26 +276,28 @@ WHERE
}
}
-pub async fn get_for_form(pool: &SqlitePool, id: i64) -> Option<payment::Form> {
+pub async fn get_for_form(conn: &Connection, id: i64) -> Option<payment::Form> {
let query = r#"
-SELECT
- id,
- name,
- cost,
- user_id,
- category_id,
- strftime('%Y-%m-%d', date) AS date,
- frequency AS frequency
-FROM
- payments
-WHERE
- id = ?
- AND deleted_at IS NULL
+ SELECT
+ id,
+ name,
+ cost,
+ user_id,
+ category_id,
+ strftime('%Y-%m-%d', date) AS date,
+ frequency AS frequency
+ FROM payments
+ WHERE
+ id = :id
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query_as::<_, payment::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 {
@@ -379,26 +309,31 @@ WHERE
}
}
-pub async fn create(pool: &SqlitePool, p: &payment::Create) -> Option<i64> {
- let res = sqlx::query(
- r#"
-INSERT INTO
- payments(name, cost, user_id, category_id, date, frequency)
-VALUES
- (?, ?, ?, ?, ?, ?)
- "#,
- )
- .bind(p.name.clone())
- .bind(p.cost)
- .bind(p.user_id)
- .bind(p.category_id)
- .bind(p.date)
- .bind(p.frequency)
- .execute(pool)
- .await;
+pub async fn create(conn: &Connection, p: payment::Create) -> Option<i64> {
+ let query = r#"
+ INSERT INTO payments(name, cost, user_id, category_id, date, frequency)
+ VALUES (:name, :cost, :user_id, :category_id, :date, :frequency)
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ conn.execute(
+ query,
+ named_params![
+ ":name": p.name,
+ ":cost": p.cost,
+ ":user_id": p.user_id,
+ ":category_id": p.category_id,
+ ":date": p.date,
+ ":frequency": p.frequency.to_string()
+ ],
+ )?;
+ Ok(conn.last_insert_rowid())
+ })
+ .await;
match res {
- Ok(x) => Some(x.last_insert_rowid()),
+ Ok(payment_id) => Some(payment_id),
Err(err) => {
log::error!("Error creating payment: {:?}", err);
None
@@ -406,30 +341,35 @@ VALUES
}
}
-pub async fn update(pool: &SqlitePool, id: i64, p: &payment::Update) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- payments
-SET
- name = ?,
- cost = ?,
- user_id = ?,
- category_id = ?,
- date = ?,
- updated_at = datetime()
-WHERE
- id = ?
- "#,
- )
- .bind(p.name.clone())
- .bind(p.cost)
- .bind(p.user_id)
- .bind(p.category_id)
- .bind(p.date)
- .bind(id)
- .execute(pool)
- .await;
+pub async fn update(conn: &Connection, id: i64, p: payment::Update) -> bool {
+ let query = r#"
+ UPDATE payments
+ SET
+ name = :name,
+ cost = :cost,
+ user_id = :user_id,
+ category_id = :category_id,
+ date = :date,
+ updated_at = datetime()
+ WHERE id = :id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ Ok(conn.execute(
+ query,
+ named_params![
+ ":name": p.name,
+ ":cost": p.cost,
+ ":user_id": p.user_id,
+ ":category_id": p.category_id,
+ ":date": p.date,
+ ":id": id
+ ],
+ )?)
+ })
+ .await;
+
match res {
Ok(_) => true,
@@ -440,20 +380,12 @@ WHERE
}
}
-pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- payments
-SET
- deleted_at = datetime()
-WHERE
- id = ?
- "#,
- )
- .bind(id)
- .execute(pool)
- .await;
+pub async fn delete(conn: &Connection, id: i64) -> bool {
+ let query = r#"UPDATE payments 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,
@@ -465,33 +397,34 @@ WHERE
}
pub async fn search_category(
- pool: &SqlitePool,
+ conn: &Connection,
payment_name: String,
) -> Option<i64> {
- let query = format!(
- r#"
-SELECT
- category_id
-FROM
- payments
-WHERE
- deleted_at IS NULL
- AND {} LIKE ?
-ORDER BY
- updated_at, created_at
- "#,
- utils::format_key_for_search("name")
- );
-
- let res = sqlx::query(&query)
- .bind(text::format_search(&payment_name))
- .map(|row: SqliteRow| row.get("category_id"))
- .fetch_one(pool)
+ let query = format!(r#"
+ SELECT category_id
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND {} LIKE :name
+ ORDER BY updated_at, created_at
+ "#, utils::format_key_for_search("name"));
+
+ let payment_name_closure = payment_name.clone();
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ let mut iter = stmt.query_map(
+ named_params![":name": text::format_search(&payment_name_closure)],
+ |row| row.get(0)
+ )?;
+ utils::one::<i64, _>(&mut iter)
+ })
.await;
match res {
Ok(category) => Some(category),
- Err(Error::RowNotFound) => None,
+ Err(tokio_rusqlite::Error::Rusqlite(rusqlite::Error::QueryReturnedNoRows)) => None,
Err(err) => {
log::error!(
"Error looking for the category of {}: {:?}",
@@ -503,24 +436,25 @@ ORDER BY
}
}
-pub async fn is_category_used(pool: &SqlitePool, category_id: i64) -> bool {
+pub async fn is_category_used(conn: &Connection, category_id: i64) -> bool {
let query = r#"
-SELECT
- 1
-FROM
- payments
-WHERE
- category_id = ?
- AND deleted_at IS NULL
-LIMIT
- 1
+ SELECT 1
+ FROM payments
+ WHERE
+ category_id = :category_id
+ AND deleted_at IS NULL
+ LIMIT 1
"#;
- let res = sqlx::query(query).bind(category_id).fetch_one(pool).await;
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(&query)?;
+ Ok(stmt.exists(named_params![":category_id": category_id])?)
+ })
+ .await;
match res {
- Ok(_) => true,
- Err(Error::RowNotFound) => false,
+ Ok(is_used) => is_used,
Err(err) => {
log::error!(
"Error looking if category {} is used: {:?}",
@@ -532,31 +466,33 @@ LIMIT
}
}
-pub async fn repartition(pool: &SqlitePool) -> HashMap<i64, i64> {
+pub async fn repartition(conn: &Connection) -> HashMap<i64, i64> {
let query = r#"
-SELECT
- users.id AS user_id,
- COALESCE(payments.sum, 0) AS sum
-FROM
- users
-LEFT OUTER JOIN (
- SELECT
- user_id,
- SUM(cost) AS sum
- FROM
- payments
- WHERE
- deleted_at IS NULL
- AND frequency = 'Punctual'
- GROUP BY
- user_id
-) payments
-ON
- users.id = payments.user_id"#;
-
- let res = sqlx::query(query)
- .map(|row: SqliteRow| (row.get("user_id"), row.get("sum")))
- .fetch_all(pool)
+ SELECT
+ users.id,
+ COALESCE(payments.sum, 0)
+ FROM users
+ LEFT OUTER JOIN (
+ SELECT
+ user_id,
+ SUM(cost) AS sum
+ FROM payments
+ WHERE
+ deleted_at IS NULL
+ AND frequency = 'Punctual'
+ GROUP BY user_id
+ ) payments
+ ON users.id = payments.user_id
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let payments = stmt
+ .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
+ .collect::<Result<Vec<(i64, i64)>, _>>()?;
+ Ok(payments)
+ })
.await;
match res {
@@ -568,25 +504,25 @@ ON
}
}
-pub async fn create_monthly_payments(pool: &SqlitePool) {
+pub async fn create_monthly_payments(conn: &Connection) {
let query = r#"
-INSERT INTO
- payments(name, cost, user_id, category_id, date, frequency)
-SELECT
- name,
- cost,
- user_id,
- category_id,
- date() AS date,
- 'Punctual' AS frequency
-FROM
- payments
-WHERE
- frequency = 'Monthly'
- AND deleted_at IS NULL
+ INSERT INTO payments(name, cost, user_id, category_id, date, frequency)
+ SELECT
+ name,
+ cost,
+ user_id,
+ category_id,
+ date() AS date,
+ 'Punctual' AS frequency
+ FROM payments
+ WHERE
+ frequency = 'Monthly'
+ AND deleted_at IS NULL
"#;
- let res = sqlx::query(query).execute(pool).await;
+ let res = conn
+ .call(move |conn| Ok(conn.execute(query, [])?))
+ .await;
match res {
Ok(_) => (),
@@ -594,50 +530,51 @@ WHERE
}
}
-pub async fn last_week(pool: &SqlitePool) -> Vec<Report> {
+pub async fn last_week(conn: &Connection) -> Vec<Report> {
let query = r#"
-SELECT
- strftime('%d/%m/%Y', payments.date) AS date,
- (payments.name || ' (' || users.name || ')') AS name,
- payments.cost AS amount,
- (CASE
- WHEN
- payments.deleted_at IS NOT NULL
- THEN
- 'Deleted'
- WHEN
- payments.updated_at IS NOT NULL
- AND payments.created_at < date('now', 'weekday 0', '-13 days')
- THEN
- 'Updated'
- ELSE
- 'Created'
- END) AS action
-FROM
- payments
-INNER JOIN
- users
-ON
- payments.user_id = users.id
-WHERE
- payments.frequency = 'Punctual'
- AND (
- (
- payments.created_at >= date('now', 'weekday 0', '-13 days')
- AND payments.created_at < date('now', 'weekday 0', '-6 days')
- ) OR (
- payments.updated_at >= date('now', 'weekday 0', '-13 days')
- AND payments.updated_at < date('now', 'weekday 0', '-6 days')
- ) OR (
- payments.deleted_at >= date('now', 'weekday 0', '-13 days')
- AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
- )
- )
-ORDER BY
- payments.date
+ SELECT
+ strftime('%d/%m/%Y', payments.date) AS date,
+ (payments.name || ' (' || users.name || ')') AS name,
+ payments.cost AS amount,
+ (CASE
+ WHEN payments.deleted_at IS NOT NULL
+ THEN 'Deleted'
+ WHEN
+ payments.updated_at IS NOT NULL
+ AND payments.created_at < date('now', 'weekday 0', '-13 days')
+ THEN 'Updated'
+ ELSE 'Created'
+ END) AS action
+ FROM payments
+ INNER JOIN users
+ ON payments.user_id = users.id
+ WHERE
+ payments.frequency = 'Punctual'
+ AND (
+ (
+ payments.created_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.created_at < date('now', 'weekday 0', '-6 days')
+ ) OR (
+ payments.updated_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.updated_at < date('now', 'weekday 0', '-6 days')
+ ) OR (
+ payments.deleted_at >= date('now', 'weekday 0', '-13 days')
+ AND payments.deleted_at < date('now', 'weekday 0', '-6 days')
+ )
+ )
+ ORDER BY payments.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::<Result<Vec<Report>, _>>()?;
+
+ Ok(xs)
+ })
+ .await;
match res {
Ok(payments) => payments,
diff --git a/src/db/users.rs b/src/db/users.rs
index f463421..8b21ff4 100644
--- a/src/db/users.rs
+++ b/src/db/users.rs
@@ -1,24 +1,30 @@
-use sqlx::error::Error;
-use sqlx::sqlite::{SqlitePool, SqliteRow};
-use sqlx_core::row::Row;
+use tokio_rusqlite::{named_params, Connection, Row};
+use crate::db::utils;
use crate::model::user::User;
-pub async fn list(pool: &SqlitePool) -> Vec<User> {
- let res = sqlx::query_as::<_, User>(
- r#"
-SELECT
- id,
- name,
- email
-FROM
- users
-ORDER BY
- name
- "#,
- )
- .fetch_all(pool)
- .await;
+fn row_to_user(row: &Row) -> Result<User, rusqlite::Error> {
+ Ok(User {
+ id: row.get(0)?,
+ name: row.get(1)?,
+ email: row.get(2)?,
+ })
+}
+
+pub async fn list(conn: &Connection) -> Vec<User> {
+ let query = r#"SELECT id, name, email FROM users ORDER BY name"#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+
+ let users = stmt
+ .query_map([], row_to_user)?
+ .collect::<Result<Vec<User>, _>>()?;
+
+ Ok(users)
+ })
+ .await;
match res {
Ok(users) => users,
@@ -30,25 +36,30 @@ ORDER BY
}
pub async fn set_login_token(
- pool: &SqlitePool,
+ conn: &Connection,
email: String,
login_token: String,
) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- users
-SET
- login_token = ?,
- updated_at = datetime()
-WHERE
- email = ?
- "#,
- )
- .bind(login_token)
- .bind(email)
- .execute(pool)
- .await;
+ let query = r#"
+ UPDATE users
+ SET
+ login_token = :login_token,
+ updated_at = datetime()
+ WHERE
+ email = :email
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ Ok(conn.execute(
+ query,
+ named_params![
+ ":login_token": login_token,
+ ":email": email
+ ],
+ )?)
+ })
+ .await;
match res {
Ok(_) => true,
@@ -59,21 +70,18 @@ WHERE
}
}
-pub async fn remove_login_token(pool: &SqlitePool, id: i64) -> bool {
- let res = sqlx::query(
- r#"
-UPDATE
- users
-SET
- login_token = NULL,
- updated_at = datetime()
-WHERE
- id = ?
- "#,
- )
- .bind(id)
- .execute(pool)
- .await;
+pub async fn remove_login_token(conn: &Connection, id: i64) -> bool {
+ let query = r#"
+ UPDATE users
+ SET
+ login_token = NULL,
+ updated_at = datetime()
+ WHERE id = :id
+ "#;
+
+ let res = conn
+ .call(move |conn| Ok(conn.execute(query, named_params![":id": id])?))
+ .await;
match res {
Ok(_) => true,
@@ -85,59 +93,61 @@ WHERE
}
pub async fn get_by_login_token(
- pool: &SqlitePool,
+ conn: &Connection,
login_token: String,
) -> Option<User> {
- let res = sqlx::query_as::<_, User>(
- r#"
-SELECT
- id,
- name,
- email
-FROM
- users
-WHERE
- login_token = ?
- "#,
- )
- .bind(login_token)
- .fetch_one(pool)
- .await;
+ let query = r#"
+ SELECT
+ id,
+ name,
+ email
+ FROM users
+ WHERE login_token = :login_token
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt.query_map(
+ named_params![":login_token": login_token],
+ row_to_user,
+ )?;
+ utils::one(&mut iter)
+ })
+ .await;
match res {
Ok(user) => Some(user),
- Err(Error::RowNotFound) => None,
Err(err) => {
- log::error!("Error getting user from login token: {:?}", err);
+ log::error!("Error getting user from login_token: {err:?}");
None
}
}
}
pub async fn get_password_hash(
- pool: &SqlitePool,
+ conn: &Connection,
email: String,
) -> Option<String> {
- let res = sqlx::query(
- r#"
-SELECT
- password
-FROM
- users
-WHERE
- email = ?
- "#,
- )
- .bind(email)
- .map(|row: SqliteRow| row.get("password"))
- .fetch_one(pool)
- .await;
+ let query = r#"
+ SELECT password
+ FROM users
+ WHERE email = :email
+ "#;
+
+ let res = conn
+ .call(move |conn| {
+ let mut stmt = conn.prepare(query)?;
+ let mut iter = stmt
+ .query_map(named_params![":email": email], |row| row.get(0))?;
+ utils::one::<String, _>(&mut iter)
+ })
+ .await;
match res {
- Ok(hash) => Some(hash),
- Err(Error::RowNotFound) => None,
+ Ok(hash) => Some(hash.clone()),
Err(err) => {
- log::error!("Error getting password hash: {:?}", err);
+ log::error!("Error listing users: {:?}", err);
None
}
}
diff --git a/src/db/utils.rs b/src/db/utils.rs
index 621a69c..f61d20a 100644
--- a/src/db/utils.rs
+++ b/src/db/utils.rs
@@ -1,3 +1,27 @@
+use crate::model::report::Report;
+use tokio_rusqlite::Row;
+
pub fn format_key_for_search(value: &str) -> String {
format!("replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower({}), 'à', 'a'), 'â', 'a'), 'ç', 'c'), 'è', 'e'), 'é', 'e'), 'ê', 'e'), 'ë', 'e'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ù', 'u'), 'û', 'u'), 'ü', 'u')", value)
}
+
+pub fn one<A, I: Iterator<Item = Result<A, rusqlite::Error>>>(
+ mut iter: I,
+) -> Result<A, tokio_rusqlite::Error> {
+ match iter.next() {
+ Some(Ok(user)) => Ok(user),
+ Some(Err(err)) => Err(tokio_rusqlite::Error::Rusqlite(err)),
+ None => Err(tokio_rusqlite::Error::Rusqlite(
+ rusqlite::Error::QueryReturnedNoRows,
+ )),
+ }
+}
+
+pub fn row_to_report(row: &Row) -> Result<Report, rusqlite::Error> {
+ Ok(Report {
+ date: row.get(0)?,
+ name: row.get(1)?,
+ amount: row.get(2)?,
+ action: row.get(3)?,
+ })
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-02-02 03:01:13 +0000