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.rs662
-rw-r--r--src/db/jobs.rs46
-rw-r--r--src/db/migrations/01-init.sql65
-rw-r--r--src/db/migrations/02-payment-category.sql44
-rw-r--r--src/db/migrations/03-sign-in-token.sql5
-rw-r--r--src/db/migrations/04-plural-naming.sql91
-rw-r--r--src/db/migrations/05-strict-tables.sql107
-rw-r--r--src/db/mod.rs44
-rw-r--r--src/db/payments.rs869
-rw-r--r--src/db/users.rs180
-rw-r--r--src/db/utils.rs28
12 files changed, 1378 insertions, 937 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..688e9e1 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,37 +344,37 @@ 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())
+ bounded_query(">", &from.format("%Y-%m-%d").to_string()),
+ bounded_query("<", "date()")
)
}
@@ -344,36 +382,36 @@ ON
///
/// It filters incomes according to the operator and date,
/// and adds the income at this date.
-fn bounded_query(op: String, date: String) -> String {
+fn bounded_query(op: &str, date: &str) -> 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 a80ef68..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,20 +29,23 @@ 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(_) => (),
- Err(err) => log::error!("Error actualizing job last execution: {:?}", err),
+ Err(err) => {
+ log::error!("Error actualizing job last execution: {:?}", err)
+ }
}
}
diff --git a/src/db/migrations/01-init.sql b/src/db/migrations/01-init.sql
new file mode 100644
index 0000000..d7c300e
--- /dev/null
+++ b/src/db/migrations/01-init.sql
@@ -0,0 +1,65 @@
+CREATE TABLE IF NOT EXISTS "user" (
+ "id" INTEGER PRIMARY KEY,
+ "creation" TIMESTAMP NOT NULL,
+ "email" VARCHAR NOT NULL,
+ "name" VARCHAR NOT NULL,
+ CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+ CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+CREATE TABLE IF NOT EXISTS "job" (
+ "id" INTEGER PRIMARY KEY,
+ "kind" VARCHAR NOT NULL,
+ "last_execution" TIMESTAMP NULL,
+ "last_check" TIMESTAMP NULL,
+ CONSTRAINT "uniq_job_kind" UNIQUE ("kind")
+);
+
+CREATE TABLE IF NOT EXISTS "sign_in"(
+ "id" INTEGER PRIMARY KEY,
+ "token" VARCHAR NOT NULL,
+ "creation" TIMESTAMP NOT NULL,
+ "email" VARCHAR NOT NULL,
+ "is_used" BOOLEAN NOT NULL,
+ CONSTRAINT "uniq_sign_in_token" UNIQUE ("token")
+);
+
+CREATE TABLE IF NOT EXISTS "payment"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "user",
+ "name" VARCHAR NOT NULL,
+ "cost" INTEGER NOT NULL,
+ "date" DATE NOT NULL,
+ "frequency" VARCHAR NOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "income"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "user",
+ "date" DATE NOT NULL,
+ "amount" INTEGERNOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "category"(
+ "id" INTEGER PRIMARY KEY,
+ "name" VARCHAR NOT NULL,
+ "color" VARCHAR NOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "payment_category"(
+ "id" INTEGER PRIMARY KEY,
+ "name" VARCHAR NOT NULL,
+ "category" INTEGER NOT NULL REFERENCES "category",
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ CONSTRAINT "uniq_payment_category_name" UNIQUE ("name")
+);
diff --git a/src/db/migrations/02-payment-category.sql b/src/db/migrations/02-payment-category.sql
new file mode 100644
index 0000000..c1d502f
--- /dev/null
+++ b/src/db/migrations/02-payment-category.sql
@@ -0,0 +1,44 @@
+-- Add payment categories with accents from payment with accents
+
+INSERT INTO
+ payment_category (name, category, created_at)
+SELECT
+ DISTINCT lower(payment.name), payment_category.category, datetime('now')
+FROM
+ payment
+INNER JOIN
+ payment_category
+ON
+ replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
+WHERE
+ payment.name
+IN
+ (SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);
+
+-- Remove unused payment categories
+
+DELETE FROM
+ payment_category
+WHERE
+ name NOT IN (SELECT DISTINCT lower(name) FROM payment);
+
+-- Add category id to payment table
+
+PRAGMA foreign_keys = 0;
+
+ALTER TABLE payment ADD COLUMN "category" INTEGER NOT NULL REFERENCES "category" DEFAULT -1;
+
+PRAGMA foreign_keys = 1;
+
+UPDATE
+ payment
+SET
+ category = (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name))
+WHERE
+ EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name));
+
+DELETE FROM payment WHERE category = -1;
+
+-- Remove
+
+DROP TABLE payment_category;
diff --git a/src/db/migrations/03-sign-in-token.sql b/src/db/migrations/03-sign-in-token.sql
new file mode 100644
index 0000000..a3d8a13
--- /dev/null
+++ b/src/db/migrations/03-sign-in-token.sql
@@ -0,0 +1,5 @@
+DROP TABLE sign_in;
+
+ALTER TABLE user ADD COLUMN "password" TEXT NOT NULL DEFAULT "password";
+
+ALTER TABLE user ADD COLUMN "sign_in_token" TEXT NULL;
diff --git a/src/db/migrations/04-plural-naming.sql b/src/db/migrations/04-plural-naming.sql
new file mode 100644
index 0000000..ec386cb
--- /dev/null
+++ b/src/db/migrations/04-plural-naming.sql
@@ -0,0 +1,91 @@
+-- Payments
+
+CREATE TABLE IF NOT EXISTS "payments"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "users",
+ "name" TEXT NOT NULL,
+ "cost" INTEGER NOT NULL,
+ "date" DATE NOT NULL,
+ "frequency" TEXT NOT NULL,
+ "category_id" INTEGER NOT NULL REFERENCES "categories",
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
+ SELECT id, user_id, name, cost, date, frequency, category, created_at, edited_at, deleted_at
+ FROM payment;
+
+DROP TABLE payment;
+
+CREATE INDEX payment_date ON payments(date);
+
+-- Categories
+
+CREATE TABLE IF NOT EXISTS "categories"(
+ "id" INTEGER PRIMARY KEY,
+ "name" TEXT NOT NULL,
+ "color" TEXT NOT NULL,
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
+ SELECT id, name, color, created_at, edited_at, deleted_at
+ FROM category;
+
+DROP TABLE category;
+
+-- Users
+
+CREATE TABLE IF NOT EXISTS "users"(
+ "id" INTEGER PRIMARY KEY,
+ "email" TEXT NOT NULL,
+ "name" TEXT NOT NULL,
+ "password" TEXT NOT NULL,
+ "login_token" TEXT NULL,
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL,
+ CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+ CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+INSERT INTO users (id, created_at, email, name, password, login_token)
+ SELECT id, creation, email, name, password, sign_in_token
+ FROM user;
+
+DROP TABLE user;
+
+-- Jobs
+
+CREATE TABLE IF NOT EXISTS "jobs"(
+ "name" TEXT PRIMARY KEY,
+ "last_execution" DATE NOT NULL DEFAULT (datetime('now'))
+);
+
+INSERT INTO jobs (name, last_execution)
+ SELECT kind, last_execution
+ FROM job;
+
+DROP TABLE job;
+
+-- Incomes
+
+CREATE TABLE IF NOT EXISTS "incomes"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "users",
+ "date" DATE NOT NULL,
+ "amount" INTEGER NOT NULL,
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
+ SELECT id, user_id, date, amount, created_at, edited_at, deleted_at
+ FROM income;
+
+DROP TABLE income;
diff --git a/src/db/migrations/05-strict-tables.sql b/src/db/migrations/05-strict-tables.sql
new file mode 100644
index 0000000..cf7ef4b
--- /dev/null
+++ b/src/db/migrations/05-strict-tables.sql
@@ -0,0 +1,107 @@
+-- Activate strict mode
+
+-- Start with users and categories, as it’s referenced in other tables.
+-- Otherwise, the reference is set to the renamed non strict table.
+
+-- Users
+
+ALTER TABLE "users" RENAME TO "users_non_strict";
+
+CREATE TABLE IF NOT EXISTS "users"(
+ "id" INTEGER PRIMARY KEY,
+ "email" TEXT NOT NULL,
+ "name" TEXT NOT NULL,
+ "password" TEXT NOT NULL,
+ "login_token" TEXT NULL,
+ "created_at" TEXT NULL DEFAULT (datetime('now')),
+ "updated_at" TEXT NULL,
+ "deleted_at" TEXT NULL,
+ CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+ CONSTRAINT "uniq_user_name" UNIQUE ("name")
+) STRICT;
+
+INSERT INTO users (id, created_at, email, name, password, login_token)
+ SELECT id, created_at, email, name, password, login_token
+ FROM users_non_strict;
+
+DROP TABLE users_non_strict;
+
+-- Categories
+
+ALTER TABLE "categories" RENAME TO "categories_non_strict";
+
+CREATE TABLE IF NOT EXISTS "categories"(
+ "id" INTEGER PRIMARY KEY,
+ "name" TEXT NOT NULL,
+ "color" TEXT NOT NULL,
+ "created_at" TEXT NULL DEFAULT (datetime('now')),
+ "updated_at" TEXT NULL,
+ "deleted_at" TEXT NULL
+) STRICT;
+
+INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
+ SELECT id, name, color, created_at, updated_at, deleted_at
+ FROM categories_non_strict;
+
+DROP TABLE categories_non_strict;
+
+-- Payments
+
+ALTER TABLE "payments" RENAME TO "payments_non_strict";
+
+CREATE TABLE IF NOT EXISTS "payments"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "users",
+ "name" TEXT NOT NULL,
+ "cost" INTEGER NOT NULL,
+ "date" TEXT NOT NULL,
+ "frequency" TEXT NOT NULL,
+ "category_id" INTEGER NOT NULL REFERENCES "categories",
+ "created_at" TEXT NULL DEFAULT (datetime('now')),
+ "updated_at" TEXT NULL,
+ "deleted_at" TEXT NULL
+) STRICT;
+
+DROP INDEX IF EXISTS payment_date;
+CREATE INDEX payment_date ON payments(date);
+
+INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
+ SELECT id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at
+ FROM payments_non_strict;
+
+DROP TABLE payments_non_strict;
+
+-- Jobs
+
+ALTER TABLE "jobs" RENAME TO "jobs_non_strict";
+
+CREATE TABLE IF NOT EXISTS "jobs"(
+ "name" TEXT PRIMARY KEY,
+ "last_execution" TEXT NOT NULL DEFAULT (datetime('now'))
+) STRICT;
+
+INSERT INTO jobs (name, last_execution)
+ SELECT name, last_execution
+ FROM jobs_non_strict;
+
+DROP TABLE jobs_non_strict;
+
+-- Incomes
+
+ALTER TABLE "incomes" RENAME TO "incomes_non_strict";
+
+CREATE TABLE IF NOT EXISTS "incomes"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "users",
+ "date" TEXT NOT NULL,
+ "amount" INTEGER NOT NULL,
+ "created_at" TEXT NULL DEFAULT (datetime('now')),
+ "updated_at" TEXT NULL,
+ "deleted_at" TEXT NULL
+) STRICT;
+
+INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
+ SELECT id, user_id, date, amount, created_at, updated_at, deleted_at
+ FROM incomes_non_strict;
+
+DROP TABLE incomes_non_strict;
diff --git a/src/db/mod.rs b/src/db/mod.rs
index a0aa3dc..d0c4f7b 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,6 +1,50 @@
+use anyhow::{Error, Result};
+use rusqlite_migration::{Migrations, M};
+use tokio_rusqlite::Connection;
+
pub mod categories;
pub mod incomes;
pub mod jobs;
pub mod payments;
pub mod users;
mod utils;
+
+pub async fn init(path: &str) -> Result<Connection> {
+ let connection = Connection::open(path).await.map_err(|err| {
+ Error::msg(format!("Error opening connection: {err}"))
+ })?;
+
+ apply_migrations(&connection).await?;
+ set_pragma(&connection, "foreign_keys", "ON").await?;
+ set_pragma(&connection, "journal_mode", "wal").await?;
+ Ok(connection)
+}
+
+async fn apply_migrations(conn: &Connection) -> Result<()> {
+ let migrations = Migrations::new(vec![
+ M::up(include_str!("migrations/01-init.sql")),
+ M::up(include_str!("migrations/02-payment-category.sql")),
+ M::up(include_str!("migrations/03-sign-in-token.sql")),
+ M::up(include_str!("migrations/04-plural-naming.sql")),
+ M::up(include_str!("migrations/05-strict-tables.sql")),
+ ]);
+
+ Ok(conn
+ .call(move |conn| {
+ migrations.to_latest(conn).map_err(|migration_err| {
+ tokio_rusqlite::Error::Other(Box::new(migration_err))
+ })
+ })
+ .await?)
+}
+
+async fn set_pragma(conn: &Connection, key: impl Into<String>, value: impl Into<String>) -> Result<()> {
+ let key = key.into();
+ let value = value.into();
+ Ok(conn
+ .call(move |conn| {
+ conn.pragma_update(None, &key, &value)
+ .map_err(tokio_rusqlite::Error::Rusqlite)
+ })
+ .await?)
+}
diff --git a/src/db/payments.rs b/src/db/payments.rs
index b415a28..c3013d2 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -1,10 +1,8 @@
-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::{
+ named_params, params_from_iter, types::ToSql, Connection, Row,
+};
use crate::db::utils;
use crate::model::frequency::Frequency;
@@ -13,158 +11,123 @@ 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 = r#"
+ SELECT COUNT(*), SUM(payments.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
+ "#
+ .to_string();
+
+ 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,
- Err(err) => {
- log::error!("Error counting payments: {:?}", err);
- Count {
- count: 0,
- total_cost: 0,
- }
- }
+ Err(_) => Count {
+ count: 0,
+ total_cost: 0,
+ },
}
}
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 = 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
+ "#
+ .to_string();
+
+ 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,136 @@ 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 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 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_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 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_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(text::format_search(&name)));
+ }
}
}
-fn bind_cost<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+fn complete_cost(
cost: Option<String>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match cost {
- Some(str) => {
- if str.is_empty() {
- query
- } else {
- query.bind(str)
- }
+ 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))
}
- _ => 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>>,
+fn complete_user(
user: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match user {
- Some(id) => query.bind(id),
- _ => query,
+ 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 category_query(category: Option<i64>) -> String {
- if category.is_some() {
- "AND payments.category_id = ?".to_string()
- } else {
- "".to_string()
- }
-}
-
-fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
- query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+fn complete_category(
category: Option<i64>,
-) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
- match category {
- Some(id) => query.bind(id),
- _ => query,
+ 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 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 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 +276,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 +311,29 @@ 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 +345,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 +377,34 @@ 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 +415,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,96 +432,107 @@ 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
- "#,
+ 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 res = sqlx::query(&query)
- .bind(text::format_search(&payment_name))
- .map(|row: SqliteRow| row.get("category_id"))
- .fetch_one(pool)
+ 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 {}: {:?}",
- payment_name, err
+ payment_name,
+ err
);
None
}
}
}
-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: {:?}",
- category_id, err
+ category_id,
+ err
);
false
}
}
}
-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 {
@@ -566,25 +544,23 @@ 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(_) => (),
@@ -592,50 +568,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..2ff0f13 100644
--- a/src/db/utils.rs
+++ b/src/db/utils.rs
@@ -1,3 +1,29 @@
+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)
+ // Lower doesn’t work on accentuated letters, hence the need to remove manually accents for
+ // uppercase letters as well.
+ format!("replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(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'), 'À', '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-07-09 15:12:05 +0000