From 49426740e8e0c59040f4f3721a658f225572582b Mon Sep 17 00:00:00 2001 From: Joris Date: Tue, 28 Nov 2017 09:11:19 +0100 Subject: Add search for payments --- server/migrations/1.sql | 65 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 65 insertions(+) create mode 100644 server/migrations/1.sql (limited to 'server/migrations') diff --git a/server/migrations/1.sql b/server/migrations/1.sql new file mode 100644 index 0000000..d7c300e --- /dev/null +++ b/server/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") +); -- cgit v1.2.3 From fb8f0fe577e28dae69903413b761da50586e0099 Mon Sep 17 00:00:00 2001 From: Joris Date: Sat, 10 Aug 2019 14:53:41 +0200 Subject: Remove payment category if unused after a payment is deleted --- server/migrations/2.sql | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) create mode 100644 server/migrations/2.sql (limited to 'server/migrations') diff --git a/server/migrations/2.sql b/server/migrations/2.sql new file mode 100644 index 0000000..1c829ec --- /dev/null +++ b/server/migrations/2.sql @@ -0,0 +1,23 @@ +-- 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); -- cgit v1.2.3 From c0ea63f8c1a8c7123b78798cec99726b113fb1f3 Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 17 Nov 2019 18:08:28 +0100 Subject: Optimize and refactor payments --- server/migrations/2.sql | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'server/migrations') diff --git a/server/migrations/2.sql b/server/migrations/2.sql index 1c829ec..efed046 100644 --- a/server/migrations/2.sql +++ b/server/migrations/2.sql @@ -21,3 +21,24 @@ 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 -- cgit v1.2.3 From bc48d7428607c84003658d5b88d41cf923d010fd Mon Sep 17 00:00:00 2001 From: Joris Date: Sat, 18 Jan 2020 16:18:26 +0100 Subject: Add deploy command --- server/migrations/2.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'server/migrations') diff --git a/server/migrations/2.sql b/server/migrations/2.sql index efed046..c1d502f 100644 --- a/server/migrations/2.sql +++ b/server/migrations/2.sql @@ -35,10 +35,10 @@ UPDATE 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)) + EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name)); DELETE FROM payment WHERE category = -1; -- Remove -DROP TABLE payment_category +DROP TABLE payment_category; -- cgit v1.2.3 From af8353c6164aaaaa836bfed181f883ac86bb76a5 Mon Sep 17 00:00:00 2001 From: Joris Date: Sun, 19 Jan 2020 14:03:31 +0100 Subject: Sign in with email and password --- server/migrations/3.sql | 5 +++++ 1 file changed, 5 insertions(+) create mode 100644 server/migrations/3.sql (limited to 'server/migrations') diff --git a/server/migrations/3.sql b/server/migrations/3.sql new file mode 100644 index 0000000..a3d8a13 --- /dev/null +++ b/server/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; -- cgit v1.2.3