From 2c3855e80df3f3ca7acaf0fc919bca66d495230c Mon Sep 17 00:00:00 2001 From: Joris Date: Fri, 7 Feb 2025 14:54:22 +0100 Subject: Move migration to migrations folder --- src/db/migration/01-init.sql | 65 ------------------ src/db/migration/02-payment-category.sql | 44 ------------ src/db/migration/03-sign-in-token.sql | 5 -- src/db/migration/04-plural-naming.sql | 91 ------------------------- src/db/migration/05-strict-tables.sql | 107 ------------------------------ src/db/migrations/01-init.sql | 65 ++++++++++++++++++ src/db/migrations/02-payment-category.sql | 44 ++++++++++++ src/db/migrations/03-sign-in-token.sql | 5 ++ src/db/migrations/04-plural-naming.sql | 91 +++++++++++++++++++++++++ src/db/migrations/05-strict-tables.sql | 107 ++++++++++++++++++++++++++++++ src/db/mod.rs | 10 +-- 11 files changed, 317 insertions(+), 317 deletions(-) delete mode 100644 src/db/migration/01-init.sql delete mode 100644 src/db/migration/02-payment-category.sql delete mode 100644 src/db/migration/03-sign-in-token.sql delete mode 100644 src/db/migration/04-plural-naming.sql delete mode 100644 src/db/migration/05-strict-tables.sql create mode 100644 src/db/migrations/01-init.sql create mode 100644 src/db/migrations/02-payment-category.sql create mode 100644 src/db/migrations/03-sign-in-token.sql create mode 100644 src/db/migrations/04-plural-naming.sql create mode 100644 src/db/migrations/05-strict-tables.sql diff --git a/src/db/migration/01-init.sql b/src/db/migration/01-init.sql deleted file mode 100644 index d7c300e..0000000 --- a/src/db/migration/01-init.sql +++ /dev/null @@ -1,65 +0,0 @@ -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/migration/02-payment-category.sql b/src/db/migration/02-payment-category.sql deleted file mode 100644 index c1d502f..0000000 --- a/src/db/migration/02-payment-category.sql +++ /dev/null @@ -1,44 +0,0 @@ --- 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/migration/03-sign-in-token.sql b/src/db/migration/03-sign-in-token.sql deleted file mode 100644 index a3d8a13..0000000 --- a/src/db/migration/03-sign-in-token.sql +++ /dev/null @@ -1,5 +0,0 @@ -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/migration/04-plural-naming.sql b/src/db/migration/04-plural-naming.sql deleted file mode 100644 index ec386cb..0000000 --- a/src/db/migration/04-plural-naming.sql +++ /dev/null @@ -1,91 +0,0 @@ --- 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/migration/05-strict-tables.sql b/src/db/migration/05-strict-tables.sql deleted file mode 100644 index cf7ef4b..0000000 --- a/src/db/migration/05-strict-tables.sql +++ /dev/null @@ -1,107 +0,0 @@ --- 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/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 ef5ccef..d0c4f7b 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -22,11 +22,11 @@ pub async fn init(path: &str) -> Result { async fn apply_migrations(conn: &Connection) -> Result<()> { let migrations = Migrations::new(vec![ - M::up(include_str!("migration/01-init.sql")), - M::up(include_str!("migration/02-payment-category.sql")), - M::up(include_str!("migration/03-sign-in-token.sql")), - M::up(include_str!("migration/04-plural-naming.sql")), - M::up(include_str!("migration/05-strict-tables.sql")), + 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 -- cgit v1.2.3