aboutsummaryrefslogtreecommitdiff
path: root/server/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'server/migrations')
-rw-r--r--server/migrations/2.sql21
1 files changed, 21 insertions, 0 deletions
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