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