From 11052951b74b9ad4b6a9412ae490086235f9154b Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 3 Jan 2021 13:40:40 +0100 Subject: Rewrite in Rust --- sql/migrations/1.sql | 65 +++++++++++++++++++++++++++++++++++++ sql/migrations/2.sql | 44 +++++++++++++++++++++++++ sql/migrations/3.sql | 5 +++ sql/migrations/4.sql | 91 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 205 insertions(+) create mode 100644 sql/migrations/1.sql create mode 100644 sql/migrations/2.sql create mode 100644 sql/migrations/3.sql create mode 100644 sql/migrations/4.sql (limited to 'sql/migrations') diff --git a/sql/migrations/1.sql b/sql/migrations/1.sql new file mode 100644 index 0000000..d7c300e --- /dev/null +++ b/sql/migrations/1.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/sql/migrations/2.sql b/sql/migrations/2.sql new file mode 100644 index 0000000..c1d502f --- /dev/null +++ b/sql/migrations/2.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/sql/migrations/3.sql b/sql/migrations/3.sql new file mode 100644 index 0000000..a3d8a13 --- /dev/null +++ b/sql/migrations/3.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/sql/migrations/4.sql b/sql/migrations/4.sql new file mode 100644 index 0000000..ec386cb --- /dev/null +++ b/sql/migrations/4.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; -- cgit v1.2.3