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