diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/incomes.rs | 6 | ||||
| -rw-r--r-- | src/db/migrations/01-init.sql | 65 | ||||
| -rw-r--r-- | src/db/migrations/02-payment-category.sql | 44 | ||||
| -rw-r--r-- | src/db/migrations/03-sign-in-token.sql | 5 | ||||
| -rw-r--r-- | src/db/migrations/04-plural-naming.sql | 91 | ||||
| -rw-r--r-- | src/db/migrations/05-strict-tables.sql | 107 | ||||
| -rw-r--r-- | src/db/mod.rs | 44 | ||||
| -rw-r--r-- | src/db/payments.rs | 17 | ||||
| -rw-r--r-- | src/db/utils.rs | 4 |
9 files changed, 368 insertions, 15 deletions
diff --git a/src/db/incomes.rs b/src/db/incomes.rs index 90282c0..688e9e1 100644 --- a/src/db/incomes.rs +++ b/src/db/incomes.rs @@ -373,8 +373,8 @@ fn cumulative_query(from: NaiveDate) -> String { ON 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()") ) } @@ -382,7 +382,7 @@ fn cumulative_query(from: NaiveDate) -> String { /// /// 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 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 4a6774c..c3013d2 100644 --- a/src/db/payments.rs +++ b/src/db/payments.rs @@ -61,9 +61,7 @@ pub async fn count( payment_query: &queries::Payments, ) -> Count { let mut query = r#" - SELECT - COUNT(*) AS count, - SUM(payments.cost) AS total_cost + 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 @@ -85,13 +83,10 @@ pub async fn count( 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, + }, } } @@ -192,7 +187,7 @@ fn complete_name( ) .as_str(), ); - params.push(Box::new(name)); + params.push(Box::new(text::format_search(&name))); } } } diff --git a/src/db/utils.rs b/src/db/utils.rs index f61d20a..2ff0f13 100644 --- a/src/db/utils.rs +++ b/src/db/utils.rs @@ -2,7 +2,9 @@ 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>>>( |