diff options
Diffstat (limited to 'src')
38 files changed, 1845 insertions, 1275 deletions
diff --git a/src/assets.rs b/src/assets.rs index 36fab55..fdfbfdd 100644 --- a/src/assets.rs +++ b/src/assets.rs @@ -1,8 +1,8 @@ use sha2::{Digest, Sha256}; use std::collections::HashMap; +use std::fmt::Write; use std::fs; use std::iter::FromIterator; -use std::fmt::Write; pub fn get() -> HashMap<String, String> { let paths = fs::read_dir("assets").unwrap().map(|e| { diff --git a/src/controller/balance.rs b/src/controller/balance.rs index c5d9d4a..ae6d810 100644 --- a/src/controller/balance.rs +++ b/src/controller/balance.rs @@ -2,7 +2,6 @@ use http_body_util::Full; use hyper::body::Bytes; use hyper::Response; use std::collections::HashMap; -use tera::Context; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -12,18 +11,18 @@ use crate::payer; use crate::templates; pub async fn get(wallet: &Wallet) -> Response<Full<Bytes>> { - let users = db::users::list(&wallet.pool).await; + let users = db::users::list(&wallet.db_conn).await; - let incomes_from = db::incomes::defined_for_all(&wallet.pool).await; + let incomes_from = db::incomes::defined_for_all(&wallet.db_conn).await; let user_incomes = match incomes_from { - Some(from) => db::incomes::cumulative(&wallet.pool, from).await, + Some(from) => db::incomes::cumulative(&wallet.db_conn, from).await, None => HashMap::new(), }; let template_user_incomes = get_template_user_incomes(&users, &user_incomes); let total_income: i64 = user_incomes.values().sum(); - let user_payments = db::payments::repartition(&wallet.pool).await; + let user_payments = db::payments::repartition(&wallet.db_conn).await; let template_user_payments = get_template_user_payments(&users, &user_payments); let total_payments: i64 = user_payments.iter().map(|p| p.1).sum(); @@ -31,18 +30,16 @@ pub async fn get(wallet: &Wallet) -> Response<Full<Bytes>> { let exceeding_payers = payer::exceeding(&users, &user_incomes, &user_payments); - let mut context = Context::new(); - context.insert("header", &templates::Header::Balance); - context.insert("connected_user", &wallet.user); - context.insert( - "incomes_from", - &incomes_from.map(|d| d.format("%d/%m/%Y").to_string()), + let context = minijinja::context!( + header => templates::Header::Balance, + connected_user => wallet.user, + incomes_from => incomes_from.map(|d| d.format("%d/%m/%Y").to_string()), + total_income => total_income, + user_incomes => template_user_incomes, + total_payments => total_payments, + user_payments => template_user_payments, + exceeding_payers => exceeding_payers ); - context.insert("total_income", &total_income); - context.insert("user_incomes", &template_user_incomes); - context.insert("total_payments", &total_payments); - context.insert("user_payments", &template_user_payments); - context.insert("exceeding_payers", &exceeding_payers); utils::template(&wallet.assets, &wallet.templates, "balance.html", context) } diff --git a/src/controller/categories.rs b/src/controller/categories.rs index ff2d8e7..8772e38 100644 --- a/src/controller/categories.rs +++ b/src/controller/categories.rs @@ -2,7 +2,6 @@ use http_body_util::Full; use hyper::body::Bytes; use hyper::Response; use std::collections::HashMap; -use tera::Context; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -15,13 +14,14 @@ pub async fn table( wallet: &Wallet, query: queries::Categories, ) -> Response<Full<Bytes>> { - let categories = db::categories::list(&wallet.pool).await; + let categories = db::categories::list(&wallet.db_conn).await; - let mut context = Context::new(); - context.insert("header", &templates::Header::Categories); - context.insert("connected_user", &wallet.user); - context.insert("categories", &categories); - context.insert("highlight", &query.highlight); + let context = minijinja::context!( + header => templates::Header::Categories, + connected_user => wallet.user, + categories => categories, + highlight => query.highlight + ); utils::template( &wallet.assets, @@ -40,11 +40,12 @@ async fn create_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let mut context = Context::new(); - context.insert("header", &templates::Header::Categories); - context.insert("connected_user", &wallet.user.clone()); - context.insert("form", &form); - context.insert("error", &error); + let context = minijinja::context!( + header => &templates::Header::Categories, + connected_user => &wallet.user.clone(), + form => &form, + error => &error + ); utils::template( &wallet.assets, @@ -64,7 +65,7 @@ pub async fn create( match validation::category::create(&form) { Some(category) => { - match db::categories::create(&wallet.pool, &category).await { + match db::categories::create(&wallet.db_conn, category).await { Some(id) => { utils::redirect(&format!("/categories?highlight={}", id)) } @@ -85,18 +86,19 @@ async fn update_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let category = db::categories::get(&wallet.pool, id).await; + let category = db::categories::get(&wallet.db_conn, id).await; let is_category_used = - db::payments::is_category_used(&wallet.pool, id).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Categories); - context.insert("connected_user", &wallet.user); - context.insert("id", &id); - context.insert("category", &category); - context.insert("is_category_used", &is_category_used); - context.insert("form", &form); - context.insert("error", &error); + db::payments::is_category_used(&wallet.db_conn, id).await; + + let context = minijinja::context!( + header => templates::Header::Categories, + connected_user => wallet.user, + id => id, + category => category, + is_category_used => is_category_used, + form => form, + error => error + ); utils::template( &wallet.assets, @@ -117,7 +119,8 @@ pub async fn update( match validation::category::update(&form) { Some(update_category) => { - if db::categories::update(&wallet.pool, id, &update_category).await + if db::categories::update(&wallet.db_conn, id, update_category) + .await { utils::redirect(&format!("/categories?highlight={}", id)) } else { @@ -129,7 +132,7 @@ pub async fn update( } pub async fn delete(id: i64, wallet: &Wallet) -> Response<Full<Bytes>> { - if db::categories::delete(&wallet.pool, id).await { + if db::categories::delete(&wallet.db_conn, id).await { utils::redirect("/categories") } else { update_form_feedback( diff --git a/src/controller/error.rs b/src/controller/error.rs index 0f6dcc1..fb1375c 100644 --- a/src/controller/error.rs +++ b/src/controller/error.rs @@ -3,7 +3,6 @@ use hyper::body::Bytes; use hyper::header::CACHE_CONTROL; use hyper::Response; use std::collections::HashMap; -use tera::{Context, Tera}; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -24,14 +23,15 @@ pub fn error( pub fn template( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, title: &str, message: &str, ) -> String { - let mut context = Context::new(); - context.insert("title", title); - context.insert("message", message); - context.insert("assets", assets); + let context = minijinja::context!( + title => title, + message => message, + assets => assets + ); - templates.render("error.html", &context).unwrap() + templates.render_str("error.html", &context).unwrap() } diff --git a/src/controller/incomes.rs b/src/controller/incomes.rs index f22098b..09d483e 100644 --- a/src/controller/incomes.rs +++ b/src/controller/incomes.rs @@ -4,7 +4,6 @@ use http_body_util::Full; use hyper::body::Bytes; use hyper::Response; use std::collections::HashMap; -use tera::Context; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -20,17 +19,18 @@ pub async fn table( query: queries::Incomes, ) -> Response<Full<Bytes>> { let page = query.page.unwrap_or(1); - let count = db::incomes::count(&wallet.pool).await; - let incomes = db::incomes::list(&wallet.pool, page, PER_PAGE).await; + let count = db::incomes::count(&wallet.db_conn).await; + let incomes = db::incomes::list(&wallet.db_conn, page, PER_PAGE).await; let max_page = (count as f32 / PER_PAGE as f32).ceil() as i64; - let mut context = Context::new(); - context.insert("header", &templates::Header::Incomes); - context.insert("connected_user", &wallet.user); - context.insert("incomes", &incomes); - context.insert("page", &page); - context.insert("max_page", &max_page); - context.insert("highlight", &query.highlight); + let context = minijinja::context!( + header => templates::Header::Incomes, + connected_user => wallet.user, + incomes => incomes, + page => page, + max_page => max_page, + highlight => query.highlight + ); utils::template( &wallet.assets, @@ -68,17 +68,18 @@ async fn create_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let users = db::users::list(&wallet.pool).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Incomes); - context.insert("connected_user", &wallet.user); - context.insert("users", &users); - context.insert("query", &query); - context.insert("current_month", &Utc::now().date_naive().month()); - context.insert("months", &MONTHS); - context.insert("form", &form); - context.insert("error", &error); + let users = db::users::list(&wallet.db_conn).await; + + let context = minijinja::context!( + header => templates::Header::Incomes, + connected_user => wallet.user, + users => users, + query => query, + current_month => Utc::now().date_naive().month(), + months => MONTHS, + form => form, + error => error, + ); utils::template( &wallet.assets, @@ -100,7 +101,7 @@ pub async fn create( match validation::income::create(&form) { Some(income) => { if !db::incomes::defined_at( - &wallet.pool, + &wallet.db_conn, income.user_id, income.date, ) @@ -109,9 +110,10 @@ pub async fn create( { error("Un revenu est déjà défini à cette date.").await } else { - match db::incomes::create(&wallet.pool, &income).await { + match db::incomes::create(&wallet.db_conn, income).await { Some(id) => { - let row = db::incomes::get_row(&wallet.pool, id).await; + let row = + db::incomes::get_row(&wallet.db_conn, id).await; let page = (row - 1) / PER_PAGE + 1; utils::redirect(&format!( "/incomes?page={}&highlight={}", @@ -141,19 +143,20 @@ async fn update_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let users = db::users::list(&wallet.pool).await; - let income = db::incomes::get(&wallet.pool, id).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Incomes); - context.insert("connected_user", &wallet.user); - context.insert("users", &users); - context.insert("id", &id); - context.insert("income", &income); - context.insert("query", &query); - context.insert("months", &MONTHS); - context.insert("form", &form); - context.insert("error", &error); + let users = db::users::list(&wallet.db_conn).await; + let income = db::incomes::get(&wallet.db_conn, id).await; + + let context = minijinja::context!( + header => &templates::Header::Incomes, + connected_user => &wallet.user, + users => &users, + id => &id, + income => &income, + query => &query, + months => &MONTHS, + form => &form, + error => &error + ); utils::template( &wallet.assets, @@ -182,15 +185,15 @@ pub async fn update( match validation::income::update(&form) { Some(income) => { let existing_incomes = db::incomes::defined_at( - &wallet.pool, + &wallet.db_conn, income.user_id, income.date, ) .await; if existing_incomes.into_iter().any(|eid| eid != id) { error("Un revenu est déjà défini à cette date.").await - } else if db::incomes::update(&wallet.pool, id, &income).await { - let row = db::incomes::get_row(&wallet.pool, id).await; + } else if db::incomes::update(&wallet.db_conn, id, income).await { + let row = db::incomes::get_row(&wallet.db_conn, id).await; let page = (row - 1) / PER_PAGE + 1; utils::redirect(&format!( "/incomes?page={}&highlight={}", @@ -209,7 +212,7 @@ pub async fn delete( wallet: &Wallet, query: queries::Incomes, ) -> Response<Full<Bytes>> { - if db::incomes::delete(&wallet.pool, id).await { + if db::incomes::delete(&wallet.db_conn, id).await { utils::redirect(&format!("/incomes?page={}", query.page.unwrap_or(1))) } else { update_form_feedback( diff --git a/src/controller/login.rs b/src/controller/login.rs index a1bf466..f7e0695 100644 --- a/src/controller/login.rs +++ b/src/controller/login.rs @@ -3,9 +3,8 @@ use http_body_util::Full; use hyper::body::Bytes; use hyper::header::SET_COOKIE; use hyper::Response; -use sqlx::sqlite::SqlitePool; use std::collections::HashMap; -use tera::{Context, Tera}; +use tokio_rusqlite::Connection; use crate::controller::utils::with_headers; use crate::controller::wallet::Wallet; @@ -18,14 +17,15 @@ use crate::validation; pub async fn page( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, error: Option<&str>, ) -> Response<Full<Bytes>> { let connected_user: Option<User> = None; - let mut context = Context::new(); - context.insert("connected_user", &connected_user); - context.insert("error", &error); + let context = minijinja::context!( + connected_user => &connected_user, + error => &error + ); utils::template(assets, templates, "login.html", context) } @@ -33,20 +33,22 @@ pub async fn page( pub async fn login( config: &Config, assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, form: HashMap<String, String>, - pool: SqlitePool, + db_conn: Connection, ) -> Response<Full<Bytes>> { match validation::login::login(&form) { Some(login) => { - match db::users::get_password_hash(&pool, login.email.clone()).await + match db::users::get_password_hash(&db_conn, login.email.clone()) + .await { Some(hash) => match bcrypt::verify(login.password, &hash) { Ok(true) => { - let login_token = cookie::generate_token(); + // TODO: error handling + let login_token = cookie::generate_token().unwrap(); if db::users::set_login_token( - &pool, + &db_conn, login.email, login_token.clone().to_string(), ) @@ -75,7 +77,10 @@ pub async fn login( } Ok(false) => not_authorized(assets, templates).await, Err(err) => { - log::error!("Error verifying bcrypt password: {:?}", err); + log::error!( + "Error verifying bcrypt password: {:?}", + err + ); server_error(assets, templates, "Erreur serveur").await } }, @@ -88,7 +93,7 @@ pub async fn login( async fn server_error( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, msg: &str, ) -> Response<Full<Bytes>> { page(assets, templates, Some(msg)).await @@ -96,7 +101,7 @@ async fn server_error( async fn not_authorized( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, ) -> Response<Full<Bytes>> { page( assets, @@ -107,7 +112,7 @@ async fn not_authorized( } pub async fn logout(config: &Config, wallet: &Wallet) -> Response<Full<Bytes>> { - if db::users::remove_login_token(&wallet.pool, wallet.user.id).await { + if db::users::remove_login_token(&wallet.db_conn, wallet.user.id).await { with_headers( utils::redirect("/"), vec![(SET_COOKIE, &cookie::logout(config))], diff --git a/src/controller/payments.rs b/src/controller/payments.rs index 8184015..3fa85a5 100644 --- a/src/controller/payments.rs +++ b/src/controller/payments.rs @@ -3,7 +3,6 @@ use hyper::body::Bytes; use hyper::header::CONTENT_TYPE; use hyper::Response; use std::collections::HashMap; -use tera::Context; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -20,24 +19,25 @@ pub async fn table( query: queries::Payments, ) -> Response<Full<Bytes>> { let page = query.page.unwrap_or(1); - let count = db::payments::count(&wallet.pool, &query).await; + let count = db::payments::count(&wallet.db_conn, &query).await; let payments = - db::payments::list_for_table(&wallet.pool, &query, PER_PAGE).await; + db::payments::list_for_table(&wallet.db_conn, &query, PER_PAGE).await; let max_page = (count.count as f32 / PER_PAGE as f32).ceil() as i64; - let users = db::users::list(&wallet.pool).await; - let categories = db::categories::list(&wallet.pool).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Payments); - context.insert("connected_user", &wallet.user); - context.insert("payments", &payments); - context.insert("page", &page); - context.insert("max_page", &max_page); - context.insert("query", &query); - context.insert("count", &count.count); - context.insert("total_cost", &count.total_cost); - context.insert("users", &users); - context.insert("categories", &categories); + let users = db::users::list(&wallet.db_conn).await; + let categories = db::categories::list(&wallet.db_conn).await; + + let context = minijinja::context!( + header => templates::Header::Payments, + connected_user => wallet.user, + payments => payments, + page => page, + max_page => max_page, + query => query, + count => count.count, + total_cost => count.total_cost, + users => users, + categories => categories + ); utils::template( &wallet.assets, @@ -60,17 +60,18 @@ async fn create_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let users = db::users::list(&wallet.pool).await; - let categories = db::categories::list(&wallet.pool).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Payments); - context.insert("connected_user", &wallet.user); - context.insert("users", &users); - context.insert("categories", &categories); - context.insert("query", &query); - context.insert("form", &form); - context.insert("error", &error); + let users = db::users::list(&wallet.db_conn).await; + let categories = db::categories::list(&wallet.db_conn).await; + + let context = minijinja::context!( + header => templates::Header::Payments, + connected_user => wallet.user, + users => users, + categories => categories, + query => query, + form => form, + error => error + ); utils::template( &wallet.assets, @@ -91,10 +92,12 @@ pub async fn create( match validation::payment::create(&form) { Some(create_payment) => { - match db::payments::create(&wallet.pool, &create_payment).await { + match db::payments::create(&wallet.db_conn, create_payment.clone()) + .await + { Some(id) => { let row = db::payments::get_row( - &wallet.pool, + &wallet.db_conn, id, create_payment.frequency, ) @@ -138,20 +141,21 @@ async fn update_form_feedback( form: HashMap<String, String>, error: Option<String>, ) -> Response<Full<Bytes>> { - let payment = db::payments::get_for_form(&wallet.pool, id).await; - let users = db::users::list(&wallet.pool).await; - let categories = db::categories::list(&wallet.pool).await; - - let mut context = Context::new(); - context.insert("header", &templates::Header::Payments); - context.insert("connected_user", &wallet.user); - context.insert("id", &id); - context.insert("payment", &payment); - context.insert("users", &users); - context.insert("categories", &categories); - context.insert("query", &query); - context.insert("form", &form); - context.insert("error", &error); + let payment = db::payments::get_for_form(&wallet.db_conn, id).await; + let users = db::users::list(&wallet.db_conn).await; + let categories = db::categories::list(&wallet.db_conn).await; + + let context = minijinja::context!( + header => templates::Header::Payments, + connected_user => wallet.user, + id => id, + payment => payment, + users => users, + categories => categories, + query => query, + form => form, + error => error + ); utils::template( &wallet.assets, @@ -179,10 +183,10 @@ pub async fn update( match validation::payment::update(&form) { Some(update_payment) => { - if db::payments::update(&wallet.pool, id, &update_payment).await { + if db::payments::update(&wallet.db_conn, id, update_payment).await { let frequency = query.frequency.unwrap_or(Frequency::Punctual); let row = - db::payments::get_row(&wallet.pool, id, frequency).await; + db::payments::get_row(&wallet.db_conn, id, frequency).await; let page = (row - 1) / PER_PAGE + 1; // TODO: keep name, cost, user and category when updating a line let query = queries::Payments { @@ -210,7 +214,7 @@ pub async fn delete( wallet: &Wallet, query: queries::Payments, ) -> Response<Full<Bytes>> { - if db::payments::delete(&wallet.pool, id).await { + if db::payments::delete(&wallet.db_conn, id).await { let query = queries::Payments { highlight: None, ..query @@ -232,7 +236,8 @@ pub async fn search_category( wallet: &Wallet, query: queries::PaymentCategory, ) -> Response<Full<Bytes>> { - match db::payments::search_category(&wallet.pool, query.payment_name).await + match db::payments::search_category(&wallet.db_conn, query.payment_name) + .await { Some(category_id) => utils::with_headers( Response::new(format!("{}", category_id).into()), diff --git a/src/controller/statistics.rs b/src/controller/statistics.rs index eb1e704..a546c67 100644 --- a/src/controller/statistics.rs +++ b/src/controller/statistics.rs @@ -1,7 +1,6 @@ use http_body_util::Full; use hyper::body::Bytes; use hyper::Response; -use tera::Context; use crate::controller::utils; use crate::controller::wallet::Wallet; @@ -9,19 +8,17 @@ use crate::db; use crate::templates; pub async fn get(wallet: &Wallet) -> Response<Full<Bytes>> { - let categories = db::categories::list(&wallet.pool).await; - let payments = db::payments::list_for_stats(&wallet.pool).await; - let incomes = db::incomes::total_each_month(&wallet.pool).await; + let categories = db::categories::list(&wallet.db_conn).await; + let payments = db::payments::list_for_stats(&wallet.db_conn).await; + let incomes = db::incomes::total_each_month(&wallet.db_conn).await; - let mut context = Context::new(); - context.insert("header", &templates::Header::Statistics); - context.insert("connected_user", &wallet.user); - context.insert( - "json_categories", - &serde_json::to_string(&categories).unwrap(), + let context = minijinja::context!( + header => templates::Header::Statistics, + connected_user => wallet.user, + json_categories => serde_json::to_string(&categories).unwrap(), + json_payments => serde_json::to_string(&payments).unwrap(), + json_incomes => serde_json::to_string(&incomes).unwrap() ); - context.insert("json_payments", &serde_json::to_string(&payments).unwrap()); - context.insert("json_incomes", &serde_json::to_string(&incomes).unwrap()); utils::template( &wallet.assets, diff --git a/src/controller/utils.rs b/src/controller/utils.rs index 1b58c68..ccef33c 100644 --- a/src/controller/utils.rs +++ b/src/controller/utils.rs @@ -5,7 +5,6 @@ use hyper::header::{ }; use hyper::{Response, StatusCode}; use std::collections::HashMap; -use tera::{Context, Tera}; use crate::controller::error; @@ -23,29 +22,45 @@ pub fn with_headers( pub fn template( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, path: &str, - context: Context, + context: minijinja::Value, ) -> Response<Full<Bytes>> { - let mut context = context; - context.insert("assets", assets); + let context = minijinja::context! { ..context, ..minijinja::context! { + assets => assets + }}; - match templates.render(path, &context) { + match render_template(templates, path, context) { Ok(template) => with_headers( Response::new(template.into()), vec![(CONTENT_TYPE, "text/html"), (CACHE_CONTROL, "no-cache")], ), - Err(err) => server_error( - assets, - templates, - &format!("Erreur lors de la préparation de la page : {:?}", err), - ), + Err(err) => { + log::error!("ERROR template rendering {}\n{:?}", path, err); + server_error( + assets, + templates, + &format!( + "Erreur lors de la préparation de la page : {:?}", + err + ), + ) + } } } +fn render_template( + templates: &minijinja::Environment<'_>, + name: &str, + context: minijinja::Value, +) -> Result<String, minijinja::Error> { + let template = templates.get_template(name)?; + template.render(context) +} + fn server_error( assets: &HashMap<String, String>, - templates: &Tera, + templates: &minijinja::Environment<'_>, msg: &str, ) -> Response<Full<Bytes>> { with_headers( @@ -56,8 +71,8 @@ fn server_error( ) } -pub fn text(str: String) -> Response<Full<Bytes>> { - let mut response = Response::new(str.into()); +pub fn text(str: impl Into<String>) -> Response<Full<Bytes>> { + let mut response = Response::new(str.into().into()); *response.status_mut() = StatusCode::OK; response } diff --git a/src/controller/wallet.rs b/src/controller/wallet.rs index 2a4a593..edc773d 100644 --- a/src/controller/wallet.rs +++ b/src/controller/wallet.rs @@ -1,13 +1,12 @@ -use sqlx::sqlite::SqlitePool; use std::collections::HashMap; -use tera::Tera; +use tokio_rusqlite::Connection; use crate::model::user::User; #[derive(Clone)] pub struct Wallet { - pub pool: SqlitePool, + pub db_conn: Connection, pub assets: HashMap<String, String>, - pub templates: Tera, + pub templates: minijinja::Environment<'static>, pub user: User, } 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)?, + }) } diff --git a/src/jobs/mod.rs b/src/jobs/mod.rs index 17df58c..3bfca71 100644 --- a/src/jobs/mod.rs +++ b/src/jobs/mod.rs @@ -1,26 +1,29 @@ mod weekly_report; -use sqlx::sqlite::SqlitePool; -use tera::Tera; use tokio::time::{sleep, Duration}; +use tokio_rusqlite::Connection; use crate::db; use crate::model::config::Config; use crate::model::job::Job; -pub async fn start(config: Config, pool: SqlitePool, templates: Tera) { +pub async fn start( + config: Config, + db_conn: Connection, + templates: minijinja::Environment<'_>, +) { loop { - if db::jobs::should_run(&pool, Job::WeeklyReport).await { + if db::jobs::should_run(&db_conn, Job::WeeklyReport).await { log::info!("Starting weekly report job"); - if weekly_report::send(&config, &pool, &templates).await { - db::jobs::actualize_last_execution(&pool, Job::WeeklyReport) + if weekly_report::send(&config, &db_conn, &templates).await { + db::jobs::actualize_last_execution(&db_conn, Job::WeeklyReport) .await; } } - if db::jobs::should_run(&pool, Job::MonthlyPayment).await { + if db::jobs::should_run(&db_conn, Job::MonthlyPayment).await { log::info!("Starting monthly payment job"); - db::payments::create_monthly_payments(&pool).await; - db::jobs::actualize_last_execution(&pool, Job::MonthlyPayment) + db::payments::create_monthly_payments(&db_conn).await; + db::jobs::actualize_last_execution(&db_conn, Job::MonthlyPayment) .await; } // Sleeping 8 hours diff --git a/src/jobs/weekly_report.rs b/src/jobs/weekly_report.rs index 0c10143..35bf5af 100644 --- a/src/jobs/weekly_report.rs +++ b/src/jobs/weekly_report.rs @@ -1,6 +1,5 @@ -use sqlx::sqlite::SqlitePool; use std::collections::HashMap; -use tera::{Context, Tera}; +use tokio_rusqlite::Connection; use crate::db; use crate::mail; @@ -9,12 +8,12 @@ use crate::payer; pub async fn send( config: &Config, - pool: &SqlitePool, - templates: &Tera, + db_conn: &Connection, + env: &minijinja::Environment<'_>, ) -> bool { - match get_weekly_report(pool, templates).await { + match get_weekly_report(db_conn, env).await { Ok(report) => { - let users = db::users::list(pool).await; + let users = db::users::list(db_conn).await; mail::send( config, users @@ -24,39 +23,43 @@ pub async fn send( address: u.email, }) .collect(), - "Rapport hebdomadaire".to_string(), - report, + "Rapport hebdomadaire", + &report, ) .await } Err(err) => { - log::error!("Error preparing weekly report from template: {:?}", err); + log::error!( + "Error preparing weekly report from template: {:?}", + err + ); false } } } async fn get_weekly_report( - pool: &SqlitePool, - templates: &Tera, -) -> Result<String, tera::Error> { - let users = db::users::list(pool).await; - let incomes_from = db::incomes::defined_for_all(pool).await; + db_conn: &Connection, + env: &minijinja::Environment<'_>, +) -> Result<String, minijinja::Error> { + let users = db::users::list(db_conn).await; + let incomes_from = db::incomes::defined_for_all(db_conn).await; let user_incomes = match incomes_from { - Some(from) => db::incomes::cumulative(pool, from).await, + Some(from) => db::incomes::cumulative(db_conn, from).await, None => HashMap::new(), }; - let user_payments = db::payments::repartition(pool).await; + let user_payments = db::payments::repartition(db_conn).await; let exceeding_payers = payer::exceeding(&users, &user_incomes, &user_payments); - let last_week_payments = db::payments::last_week(pool).await; - let last_week_incomes = db::incomes::last_week(pool).await; + let last_week_payments = db::payments::last_week(db_conn).await; + let last_week_incomes = db::incomes::last_week(db_conn).await; - let mut context = Context::new(); - context.insert("exceeding_payers", &exceeding_payers); - context.insert("payments", &last_week_payments); - context.insert("incomes", &last_week_incomes); - - templates.render("report/report.j2", &context) + let template = env.get_template("report/report.j2")?; + template.render(minijinja::context!( + name => "John", + exceeding_payers => exceeding_payers, + payments => last_week_payments, + incomes => last_week_incomes + )) } diff --git a/src/mail.rs b/src/mail.rs index c77e2ad..7017654 100644 --- a/src/mail.rs +++ b/src/mail.rs @@ -18,8 +18,8 @@ pub struct Recipient { pub async fn send( config: &Config, recipients: Vec<Recipient>, - subject: String, - message: String, + subject: &str, + message: &str, ) -> bool { let headers = format_headers(recipients.clone(), subject); @@ -56,7 +56,9 @@ pub async fn send( true } else { match String::from_utf8(output.stderr) { - Ok(error) => log::error!("Error sending email: {}", error), + Ok(error) => { + log::error!("Error sending email: {}", error) + } _ => log::error!("Error sending email"), }; false @@ -70,24 +72,24 @@ pub async fn send( } } -fn format_headers(recipients: Vec<Recipient>, subject: String) -> String { +fn format_headers(recipients: Vec<Recipient>, subject: &str) -> String { let recipients = recipients .into_iter() - .map(|r| format_address(r.name, r.address)) + .map(|r| format_address(&r.name, &r.address)) .collect::<Vec<String>>() .join(", "); format!( "Date: {}\nFrom: {}\nTo: {}\nSubject: {}", Utc::now().to_rfc2822(), - format_address(FROM_NAME.to_string(), FROM_ADDRESS.to_string()), + format_address(FROM_NAME, FROM_ADDRESS), recipients, subject, ) } -fn format_address(name: String, address: String) -> String { - format!("{} <{}>", name, address) +fn format_address(name: &str, address: &str) -> String { + format!("{name} <{address}>") } async fn spawn(mut command: Command, stdin: &[u8]) -> Result<Output, Error> { diff --git a/src/main.rs b/src/main.rs index 2b3aebd..30832d3 100644 --- a/src/main.rs +++ b/src/main.rs @@ -1,7 +1,7 @@ +use anyhow::Result; use hyper::server::conn::http1; use hyper::service::service_fn; use hyper_util::rt::TokioIo; -use sqlx::sqlite::SqlitePool; use tokio::net::TcpListener; mod assets; @@ -21,29 +21,30 @@ mod validation; use model::config; #[tokio::main] -async fn main() -> Result<(), Box<dyn std::error::Error + Send + Sync>> { - +async fn main() -> Result<()> { env_logger::init(); let config = config::from_env() .unwrap_or_else(|err| panic!("Error reading config: {err}")); - let pool = SqlitePool::connect(&format!("sqlite:{}", config.db_path)) - .await - .unwrap(); + let db_conn = db::init(&config.db_path).await?; let assets = assets::get(); - let templates = templates::get(); + let templates = templates::get()?; - tokio::spawn(jobs::start(config.clone(), pool.clone(), templates.clone())); + tokio::spawn(jobs::start( + config.clone(), + db_conn.clone(), + templates.clone(), + )); let listener = TcpListener::bind(config.socket_address).await?; log::info!("Starting server at {}", config.socket_address); loop { let config = config.clone(); - let pool = pool.clone(); + let db_conn = db_conn.clone(); let assets = assets.clone(); let templates = templates.clone(); let (stream, _) = listener.accept().await?; @@ -57,7 +58,7 @@ async fn main() -> Result<(), Box<dyn std::error::Error + Send + Sync>> { service_fn(move |req| { routes::routes( config.clone(), - pool.clone(), + db_conn.clone(), assets.clone(), templates.clone(), req, diff --git a/src/model/action.rs b/src/model/action.rs deleted file mode 100644 index a77543a..0000000 --- a/src/model/action.rs +++ /dev/null @@ -1 +0,0 @@ -use serde::Serialize; diff --git a/src/model/category.rs b/src/model/category.rs index de08dea..ecece96 100644 --- a/src/model/category.rs +++ b/src/model/category.rs @@ -1,6 +1,4 @@ -use serde::Serialize; - -#[derive(sqlx::FromRow, Serialize)] +#[derive(serde::Serialize, Clone)] pub struct Category { pub id: i64, pub name: String, diff --git a/src/model/frequency.rs b/src/model/frequency.rs index bb83e27..f96482c 100644 --- a/src/model/frequency.rs +++ b/src/model/frequency.rs @@ -1,7 +1,9 @@ -use serde::{Deserialize, Serialize}; +use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ValueRef}; use std::{fmt, str}; -#[derive(Debug, Clone, Copy, Serialize, Deserialize, PartialEq, sqlx::Type)] +#[derive( + Debug, Clone, Copy, serde::Serialize, serde::Deserialize, PartialEq, +)] pub enum Frequency { Punctual, Monthly, @@ -29,3 +31,19 @@ impl str::FromStr for Frequency { } } } + +impl FromSql for Frequency { + fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { + match value { + ValueRef::Text(text) => match std::str::from_utf8(text) { + Ok("Punctual") => Ok(Frequency::Punctual), + Ok("Monthly") => Ok(Frequency::Monthly), + Ok(str) => Err(FromSqlError::Other( + format!("Unknown frequency: {str}").into(), + )), + Err(err) => Err(FromSqlError::Other(err.into())), + }, + _ => Err(FromSqlError::InvalidType), + } + } +} diff --git a/src/model/income.rs b/src/model/income.rs index ef97b56..c953251 100644 --- a/src/model/income.rs +++ b/src/model/income.rs @@ -1,13 +1,12 @@ use chrono::NaiveDate; -use serde::Serialize; -#[derive(Debug, Clone, sqlx::FromRow, Serialize)] +#[derive(Debug, Clone, serde::Serialize)] pub struct Stat { pub date: String, pub amount: i64, } -#[derive(Debug, Clone, sqlx::FromRow, Serialize)] +#[derive(Debug, Clone, serde::Serialize)] pub struct Table { pub id: i64, pub date: String, @@ -15,7 +14,7 @@ pub struct Table { pub amount: i64, } -#[derive(Debug, Clone, sqlx::FromRow, Serialize)] +#[derive(Debug, Clone, serde::Serialize)] pub struct Form { pub id: i64, pub amount: i64, diff --git a/src/model/job.rs b/src/model/job.rs index 74151ae..f31cfa0 100644 --- a/src/model/job.rs +++ b/src/model/job.rs @@ -1,5 +1,13 @@ -#[derive(Debug, sqlx::Type)] +use std::fmt; + +#[derive(Debug)] pub enum Job { MonthlyPayment, WeeklyReport, } + +impl fmt::Display for Job { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + write!(f, "{:?}", self) + } +} diff --git a/src/model/payment.rs b/src/model/payment.rs index 5ce6bb9..d732528 100644 --- a/src/model/payment.rs +++ b/src/model/payment.rs @@ -1,9 +1,8 @@ use chrono::NaiveDate; -use serde::Serialize; use crate::model::frequency::Frequency; -#[derive(Debug, sqlx::FromRow, Serialize)] +#[derive(Debug, serde::Serialize)] pub struct Table { pub id: i64, pub name: String, @@ -15,7 +14,7 @@ pub struct Table { pub frequency: Frequency, } -#[derive(Debug, sqlx::FromRow, Serialize)] +#[derive(Debug, serde::Serialize)] pub struct Form { pub id: i64, pub name: String, @@ -26,14 +25,14 @@ pub struct Form { pub frequency: Frequency, } -#[derive(Debug, sqlx::FromRow, Serialize)] +#[derive(Debug, serde::Serialize)] pub struct Stat { pub start_date: String, pub cost: i64, pub category_id: i64, } -#[derive(Debug)] +#[derive(Debug, Clone)] pub struct Create { pub name: String, pub cost: i64, diff --git a/src/model/report.rs b/src/model/report.rs index 4858402..e944745 100644 --- a/src/model/report.rs +++ b/src/model/report.rs @@ -1,6 +1,7 @@ -use serde::Serialize; +use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ValueRef}; +use std::fmt; -#[derive(Debug, sqlx::FromRow, Serialize)] +#[derive(Debug, serde::Serialize)] pub struct Report { pub date: String, pub name: String, @@ -8,9 +9,32 @@ pub struct Report { pub action: Action, } -#[derive(Debug, PartialEq, Serialize, sqlx::Type)] +#[derive(Debug, PartialEq, serde::Serialize)] pub enum Action { Created, Updated, Deleted, } + +impl fmt::Display for Action { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + write!(f, "{:?}", self) + } +} + +impl FromSql for Action { + fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { + match value { + ValueRef::Text(text) => match std::str::from_utf8(text) { + Ok("Created") => Ok(Action::Created), + Ok("Updated") => Ok(Action::Updated), + Ok("Deleted") => Ok(Action::Deleted), + Ok(str) => Err(FromSqlError::Other( + format!("Unknown action: {str}").into(), + )), + Err(err) => Err(FromSqlError::Other(err.into())), + }, + _ => Err(FromSqlError::InvalidType), + } + } +} diff --git a/src/model/user.rs b/src/model/user.rs index e8a61bf..06165dd 100644 --- a/src/model/user.rs +++ b/src/model/user.rs @@ -1,6 +1,4 @@ -use serde::Serialize; - -#[derive(Debug, sqlx::FromRow, Clone, Serialize)] +#[derive(Debug, Clone, serde::Serialize)] pub struct User { pub id: i64, pub name: String, diff --git a/src/queries.rs b/src/queries.rs index 8ecc4fe..9699d57 100644 --- a/src/queries.rs +++ b/src/queries.rs @@ -1,7 +1,6 @@ use crate::model::frequency::Frequency; -use serde::{Deserialize, Serialize}; -#[derive(Deserialize, Serialize, Clone)] +#[derive(serde::Serialize, serde::Deserialize, Clone)] pub struct Payments { pub page: Option<i64>, pub name: Option<String>, @@ -69,18 +68,18 @@ pub fn payments_url(q: Payments) -> String { } } -#[derive(Deserialize, Serialize, Clone)] +#[derive(serde::Serialize, serde::Deserialize, Clone)] pub struct Incomes { pub page: Option<i64>, pub highlight: Option<i64>, } -#[derive(Deserialize, Serialize, Clone)] +#[derive(serde::Serialize, serde::Deserialize, Clone)] pub struct Categories { pub highlight: Option<i64>, } -#[derive(Deserialize, Serialize)] +#[derive(serde::Serialize, serde::Deserialize)] pub struct PaymentCategory { pub payment_name: String, } diff --git a/src/routes.rs b/src/routes.rs index ae87d39..7107a60 100644 --- a/src/routes.rs +++ b/src/routes.rs @@ -2,10 +2,9 @@ use http_body_util::{BodyExt, Full}; use hyper::body::{Bytes, Incoming}; use hyper::{Method, Request, Response}; use serde::Deserialize; -use sqlx::sqlite::SqlitePool; use std::collections::HashMap; use std::convert::Infallible; -use tera::Tera; +use tokio_rusqlite::Connection; use url::form_urlencoded; use crate::controller; @@ -18,9 +17,9 @@ use crate::utils::cookie; pub async fn routes( config: Config, - pool: SqlitePool, + db_conn: Connection, assets: HashMap<String, String>, - templates: Tera, + templates: minijinja::Environment<'static>, request: Request<Incoming>, ) -> Result<Response<Full<Bytes>>, Infallible> { let method = request.method(); @@ -29,7 +28,7 @@ pub async fn routes( let response = match (method, path) { (&Method::HEAD, ["status"]) => controller::utils::ok(), - (&Method::GET, ["status"]) => controller::utils::text("ok".to_string()), + (&Method::GET, ["status"]) => controller::utils::text("ok"), (&Method::GET, ["login"]) => { controller::login::page(&assets, &templates, None).await } @@ -39,7 +38,7 @@ pub async fn routes( &assets, &templates, body_form(request).await, - pool, + db_conn, ) .await } @@ -50,10 +49,10 @@ pub async fn routes( "icon.png" => file("assets/icon.png", "image/png").await, _ => controller::utils::not_found(), }, - _ => match connected_user(&config, &pool, &request).await { + _ => match connected_user(&config, &db_conn, &request).await { Some(user) => { let wallet = Wallet { - pool, + db_conn, assets, templates, user, @@ -69,12 +68,12 @@ pub async fn routes( async fn connected_user( config: &Config, - pool: &SqlitePool, + db_conn: &Connection, request: &Request<Incoming>, ) -> Option<User> { let cookie = request.headers().get("COOKIE")?.to_str().ok()?; let login_token = cookie::extract_token(config, cookie).ok()?; - db::users::get_by_login_token(pool, login_token.to_string()).await + db::users::get_by_login_token(db_conn, login_token.to_string()).await } async fn authenticated_routes( diff --git a/src/templates.rs b/src/templates.rs index 1f86717..f6f4e62 100644 --- a/src/templates.rs +++ b/src/templates.rs @@ -1,13 +1,9 @@ -use serde::Serialize; -use serde_json::json; -use serde_json::value::Value; -use std::collections::HashMap; -use tera::Tera; -use tera::{Error, Result}; +use anyhow::{Error, Result}; +use std::fs; use crate::queries; -#[derive(Debug, Serialize)] +#[derive(Debug, serde::Serialize)] pub enum Header { Payments, Categories, @@ -16,75 +12,132 @@ pub enum Header { Statistics, } -pub fn get() -> Tera { - let mut tera = match Tera::new("templates/**/*") { - Ok(t) => t, - Err(e) => { - log::error!("Parsing error(s): {}", e); - ::std::process::exit(1); - } +pub fn get() -> Result<minijinja::Environment<'static>> { + let mut env = minijinja::Environment::new(); + for path in read_files_recursive("templates") { + let path = path + .to_str() + .ok_or(Error::msg("Error getting string of path: {path:?}"))? + .to_string(); + let content = fs::read_to_string(&path).map_err(|err| { + Error::msg(format!("Error reading template {path}: {err}")) + })?; + let path_without_prefix = path + .strip_prefix("templates/") + .ok_or(Error::msg("Error removing prefix from template path"))? + .to_string(); + env.add_template_owned(path_without_prefix, content) + .map_err(|err| { + Error::msg(format!( + "Error adding template {path} to environment: {err}" + )) + })?; + } + + env.add_function("payments_params", payments_params); + env.add_function("pluralize", pluralize); + env.add_function("now", now); + + env.add_filter("numeric", numeric); + env.add_filter("euros", euros); + env.add_filter("round", round); + env.add_filter("with_param", with_param); + env.add_filter("filter", filter); + + Ok(env) +} + +fn read_files_recursive( + path: impl AsRef<std::path::Path>, +) -> Vec<std::path::PathBuf> { + let Ok(entries) = fs::read_dir(path) else { + return vec![]; }; - tera.register_function("payments_params", payments_params); - tera.register_filter("numeric", numeric); - tera.register_filter("euros", euros); - tera + entries + .flatten() + .flat_map(|entry| { + let Ok(meta) = entry.metadata() else { + return vec![]; + }; + if meta.is_dir() { + return read_files_recursive(entry.path()); + } + if meta.is_file() { + return vec![entry.path()]; + } + vec![] + }) + .collect() } -fn payments_params(args: &HashMap<String, Value>) -> Result<Value> { - let q = json!({ - "page": args.get("page"), - "name": args.get("name"), - "cost": args.get("cost"), - "frequency": args.get("frequency"), - "highlight": args.get("highlight"), - "user": args.get("user"), - "category": args.get("category"), - "start_date": args.get("start_date"), - "end_date": args.get("end_date"), - }); - - match serde_json::from_value(q) { - Ok(q) => Ok(json!(queries::payments_url(q))), - Err(msg) => Err(Error::msg(msg)), +fn payments_params(value: minijinja::Value) -> String { + let str = value.to_string().replace("none", "null"); + match serde_json::from_str(&str) { + Ok(q) => queries::payments_url(q), + Err(err) => { + log::error!("Error parsing payments params {}: {:?}", str, err); + "".to_string() + } } } -fn euros(value: &Value, _: &HashMap<String, Value>) -> Result<Value> { - match value { - Value::Number(n) => { - if let Some(n) = n.as_i64() { - let str = rgrouped(n.abs().to_string(), 3).join(" "); - let sign = if n < 0 { "-" } else { "" }; - Ok(json!(format!("{}{} €", sign, str))) - } else if let Some(n) = n.as_f64() { - Ok(json!(format!("{} €", n))) - } else { - Err(Error::msg("Error parsing number")) - } - } - _ => Err(Error::msg(format!("{:?} should be a number", value))), +fn now(format: &str) -> String { + let date = chrono::Local::now(); + format!("{}", date.format(format)) +} + +fn euros(n: i64) -> String { + let str = rgrouped(n.abs().to_string(), 3).join(" "); + let sign = if n < 0 { "-" } else { "" }; + format!("{}{} €", sign, str) +} + +fn numeric(n: i64) -> String { + let str = rgrouped(n.abs().to_string(), 3).join(" "); + let sign = if n < 0 { "-" } else { "" }; + format!("{}{}", sign, str) +} + +fn pluralize(n: i32, s: &str) -> String { + if n > 0 { + format!("{s}s") + } else { + s.to_string() + } +} + +fn round(n: f32) -> i32 { + n.round() as i32 +} + +fn with_param(url: &str, key: &str, value: String) -> String { + if url.contains("?") { + format!("{url}&{key}={value}") + } else { + format!("{url}?{key}={value}") } } -fn numeric(value: &Value, _: &HashMap<String, Value>) -> Result<Value> { - match value { - Value::Number(n) => { - if let Some(n) = n.as_i64() { - let str = rgrouped(n.abs().to_string(), 3).join(" "); - let sign = if n < 0 { "-" } else { "" }; - Ok(json!(format!("{}{}", sign, str))) - } else if let Some(n) = n.as_f64() { - Ok(json!(format!("{}", n))) - } else { - Err(Error::msg("Error parsing number")) +fn filter( + xs: Vec<minijinja::Value>, + key: &str, + value: String, +) -> Vec<minijinja::Value> { + let mut res = vec![]; + for x in xs { + if let Ok(v) = x.get_attr(key) { + if let Some(v) = v.as_str() { + if v == value { + res.push(x); + } } } - _ => Err(Error::msg(format!("{:?} should be a number", value))), } + res } -fn rgrouped(str: String, n: usize) -> Vec<String> { - let mut str = str; +fn rgrouped(str: impl Into<String>, n: usize) -> Vec<String> { + let mut str = str.into(); let mut l = str.len(); let mut res = vec![]; while l > n { diff --git a/src/utils/cookie.rs b/src/utils/cookie.rs index 826efa9..c621621 100644 --- a/src/utils/cookie.rs +++ b/src/utils/cookie.rs @@ -1,5 +1,5 @@ use hex; -use rand_core::{OsRng, RngCore}; +use rand_core::{OsRng, TryRngCore}; use crate::crypto::signed; use crate::model::config::Config; @@ -22,10 +22,12 @@ pub fn extract_token(config: &Config, cookie: &str) -> Result<String, String> { signed::verify(&config.auth_secret, signed_cookie) } -pub fn generate_token() -> String { +pub fn generate_token() -> Result<String, String> { let mut token = [0u8; TOKEN_BYTES]; - OsRng.fill_bytes(&mut token); - hex::encode(token) + OsRng + .try_fill_bytes(&mut token) + .map_err(|_| "Error generating random token")?; + Ok(hex::encode(token)) } fn cookie(config: &Config, token: &str, max_age_seconds: i32) -> String { |