From 88b7a0784e0c9e66dad26e0d85ee06ce9f7c59b9 Mon Sep 17 00:00:00 2001
From: Masaya Tojo <masaya@tojo.tokyo>
Date: Sat, 4 Mar 2023 18:40:37 +0900
Subject: Make transaction type integer.

---
 deploy/transaction_type_to_integer.sql | 10 ++++++++++
 revert/transaction_type_to_integer.sql |  8 ++++++++
 sqitch.plan                            |  1 +
 verify/transaction_type_to_integer.sql | 16 ++++++++++++++++
 4 files changed, 35 insertions(+)
 create mode 100644 deploy/transaction_type_to_integer.sql
 create mode 100644 revert/transaction_type_to_integer.sql
 create mode 100644 verify/transaction_type_to_integer.sql

diff --git a/deploy/transaction_type_to_integer.sql b/deploy/transaction_type_to_integer.sql
new file mode 100644
index 0000000..8f18f5a
--- /dev/null
+++ b/deploy/transaction_type_to_integer.sql
@@ -0,0 +1,10 @@
+-- Deploy kakeibo:transaction_type_to_integer to pg
+-- requires: transactions
+
+BEGIN;
+
+ALTER TABLE kakeibo.transactions
+  ALTER COLUMN type TYPE integer
+  USING CASE type WHEN 'outgo' THEN 0 ELSE 1 END;
+
+COMMIT;
diff --git a/revert/transaction_type_to_integer.sql b/revert/transaction_type_to_integer.sql
new file mode 100644
index 0000000..482583e
--- /dev/null
+++ b/revert/transaction_type_to_integer.sql
@@ -0,0 +1,8 @@
+-- Revert kakeibo:transaction_type_to_integer from pg
+
+BEGIN;
+
+ALTER TABLE kakeibo.transactions ALTER COLUMN type TYPE kakeibo.transaction_type
+  USING CASE type WHEN 0 THEN 'outgo'::kakeibo.transaction_type ELSE 'income'::kakeibo.transaction_type END;
+
+COMMIT;
diff --git a/sqitch.plan b/sqitch.plan
index ef026a7..9d0965f 100644
--- a/sqitch.plan
+++ b/sqitch.plan
@@ -14,3 +14,4 @@ update_timestamp [appschema] 2023-02-28T14:30:04Z Masaya Tojo <masaya@tojo.tokyo
 transactions_update_timestamp [transactions update_timestamp] 2023-02-28T16:43:49Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to transactions table.
 items_update_timestamp [items update_timestamp] 2023-02-28T17:04:02Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to items table.
 @v1.0.0-dev1 2023-03-04T09:29:02Z Masaya Tojo <masaya@tojo.tokyo> # Tag v1.0.0-dev1.
+transaction_type_to_integer [transactions] 2023-03-04T09:32:34Z Masaya Tojo <masaya@tojo.tokyo> # Make transaction type integer.
diff --git a/verify/transaction_type_to_integer.sql b/verify/transaction_type_to_integer.sql
new file mode 100644
index 0000000..808d5df
--- /dev/null
+++ b/verify/transaction_type_to_integer.sql
@@ -0,0 +1,16 @@
+-- Verify kakeibo:transaction_type_to_integer on pg
+
+BEGIN;
+
+DO $$
+  BEGIN
+    ASSERT(SELECT 1
+             FROM information_schema.columns 
+            WHERE table_name = 'transactions'
+              AND table_schema = 'kakeibo'
+              AND column_name = 'type'
+              AND data_type = 'integer');
+  END
+$$;
+
+ROLLBACK;
-- 
cgit v1.2.3