diff options
author | Joris | 2025-02-07 08:25:28 +0100 |
---|---|---|
committer | Joris | 2025-02-07 08:25:28 +0100 |
commit | bfe4aa78d882b9d95bd1f954371136f3aa5c38c9 (patch) | |
tree | 597f20035a42f4603d4a1c693a4c070ef00e112e /src/db/migration | |
parent | fedb4e7c7ebf21619f89c29d011e288363a978e9 (diff) |
Migrate database at startup
Diffstat (limited to 'src/db/migration')
-rw-r--r-- | src/db/migration/01-init.sql | 65 | ||||
-rw-r--r-- | src/db/migration/02-payment-category.sql | 44 | ||||
-rw-r--r-- | src/db/migration/03-sign-in-token.sql | 5 | ||||
-rw-r--r-- | src/db/migration/04-plural-naming.sql | 91 | ||||
-rw-r--r-- | src/db/migration/05-strict-tables.sql | 107 |
5 files changed, 312 insertions, 0 deletions
diff --git a/src/db/migration/01-init.sql b/src/db/migration/01-init.sql new file mode 100644 index 0000000..d7c300e --- /dev/null +++ b/src/db/migration/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/migration/02-payment-category.sql b/src/db/migration/02-payment-category.sql new file mode 100644 index 0000000..c1d502f --- /dev/null +++ b/src/db/migration/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/migration/03-sign-in-token.sql b/src/db/migration/03-sign-in-token.sql new file mode 100644 index 0000000..a3d8a13 --- /dev/null +++ b/src/db/migration/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/migration/04-plural-naming.sql b/src/db/migration/04-plural-naming.sql new file mode 100644 index 0000000..ec386cb --- /dev/null +++ b/src/db/migration/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/migration/05-strict-tables.sql b/src/db/migration/05-strict-tables.sql new file mode 100644 index 0000000..cf7ef4b --- /dev/null +++ b/src/db/migration/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; |