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