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/2.sql')

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/2.sql')

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/2.sql')

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