diff options
-rw-r--r-- | deploy/delete_item.sql | 15 | ||||
-rw-r--r-- | deploy/delete_transaction.sql | 15 | ||||
-rw-r--r-- | deploy/insert_item.sql | 25 | ||||
-rw-r--r-- | deploy/insert_transaction.sql | 24 | ||||
-rw-r--r-- | revert/delete_item.sql | 7 | ||||
-rw-r--r-- | revert/delete_transaction.sql | 7 | ||||
-rw-r--r-- | revert/insert_item.sql | 7 | ||||
-rw-r--r-- | revert/insert_transaction.sql | 7 | ||||
-rw-r--r-- | sqitch.plan | 4 | ||||
-rw-r--r-- | verify/delete_item.sql | 37 | ||||
-rw-r--r-- | verify/delete_transaction.sql | 43 | ||||
-rw-r--r-- | verify/insert_item.sql | 31 | ||||
-rw-r--r-- | verify/insert_transaction.sql | 26 |
13 files changed, 0 insertions, 248 deletions
diff --git a/deploy/delete_item.sql b/deploy/delete_item.sql deleted file mode 100644 index 38b3083..0000000 --- a/deploy/delete_item.sql +++ /dev/null @@ -1,15 +0,0 @@ --- Deploy kakeibo:delete_item to pg --- requires: schema --- requires: items - -BEGIN; - -CREATE OR REPLACE FUNCTION kakeibo.delete_item( - p_item_id INTEGER -) RETURNS VOID AS $$ - BEGIN - DELETE FROM kakeibo.items WHERE id = p_item_id; - END; -$$ LANGUAGE plpgsql SECURITY DEFINER; - -COMMIT; diff --git a/deploy/delete_transaction.sql b/deploy/delete_transaction.sql deleted file mode 100644 index d3c23de..0000000 --- a/deploy/delete_transaction.sql +++ /dev/null @@ -1,15 +0,0 @@ --- Deploy kakeibo:delete_transaction to pg --- requires: schema --- requires: transactions - -BEGIN; - -CREATE OR REPLACE FUNCTION kakeibo.delete_transaction( - p_transaction_id INTEGER -) RETURNS VOID AS $$ - BEGIN - DELETE FROM kakeibo.transactions WHERE id = p_transaction_id; - END; -$$ LANGUAGE plpgsql SECURITY DEFINER; - -COMMIT; diff --git a/deploy/insert_item.sql b/deploy/insert_item.sql deleted file mode 100644 index 9f4e050..0000000 --- a/deploy/insert_item.sql +++ /dev/null @@ -1,25 +0,0 @@ --- Deploy kakeibo:insert_item to pg --- requires: schema --- requires: items - -BEGIN; - -CREATE OR REPLACE FUNCTION kakeibo.insert_item( - p_transaction_id INTEGER, - p_category TEXT, - p_subcategory TEXT, - p_amount INTEGER, - p_note TEXT -) RETURNS INTEGER AS $$ - DECLARE - inserted_id INTEGER; - BEGIN - INSERT INTO kakeibo.items (transaction_id, category, subcategory, amount, note) - VALUES (p_transaction_id, p_category, p_subcategory, p_amount, p_note) - RETURNING id INTO inserted_id; - - RETURN inserted_id; - END; -$$ LANGUAGE plpgsql SECURITY DEFINER; - -COMMIT; diff --git a/deploy/insert_transaction.sql b/deploy/insert_transaction.sql deleted file mode 100644 index eb5a38e..0000000 --- a/deploy/insert_transaction.sql +++ /dev/null @@ -1,24 +0,0 @@ --- Deploy kakeibo:insert_transaction to pg --- requires: schema --- requires: transactions - -BEGIN; - -CREATE OR REPLACE FUNCTION kakeibo.insert_transaction( - p_type_code INTEGER, - p_date DATE, - p_note TEXT -) RETURNS INTEGER AS $$ - DECLARE - inserted_id INTEGER; - BEGIN - INSERT INTO kakeibo.transactions ( - type_code, date, note - ) VALUES ( - p_type_code, p_date, p_note - ) RETURNING id INTO inserted_id; - RETURN inserted_id; - END; -$$ LANGUAGE plpgsql SECURITY DEFINER; - -COMMIT; diff --git a/revert/delete_item.sql b/revert/delete_item.sql deleted file mode 100644 index d5bda39..0000000 --- a/revert/delete_item.sql +++ /dev/null @@ -1,7 +0,0 @@ --- Revert kakeibo:delete_item from pg - -BEGIN; - -DROP FUNCTION kakeibo.delete_item; - -COMMIT; diff --git a/revert/delete_transaction.sql b/revert/delete_transaction.sql deleted file mode 100644 index 573a0b2..0000000 --- a/revert/delete_transaction.sql +++ /dev/null @@ -1,7 +0,0 @@ --- Revert kakeibo:delete_transaction from pg - -BEGIN; - -DROP FUNCTION kakeibo.delete_transaction; - -COMMIT; diff --git a/revert/insert_item.sql b/revert/insert_item.sql deleted file mode 100644 index 40417f0..0000000 --- a/revert/insert_item.sql +++ /dev/null @@ -1,7 +0,0 @@ --- Revert kakeibo:insert_item from pg - -BEGIN; - -DROP FUNCTION kakeibo.insert_item; - -COMMIT; diff --git a/revert/insert_transaction.sql b/revert/insert_transaction.sql deleted file mode 100644 index d579ca6..0000000 --- a/revert/insert_transaction.sql +++ /dev/null @@ -1,7 +0,0 @@ --- Revert kakeibo:insert_transaction from pg - -BEGIN; - -DROP FUNCTION kakeibo.insert_transaction; - -COMMIT; diff --git a/sqitch.plan b/sqitch.plan index 4e5968c..d29087f 100644 --- a/sqitch.plan +++ b/sqitch.plan @@ -5,10 +5,6 @@ kakeibo 2023-03-11T15:56:41Z Masaya Tojo <masaya@tojo.tokyo> # Add schema for use in kakeibo. transactions [kakeibo] 2023-03-11T16:50:25Z Masaya Tojo <masaya@tojo.tokyo> # Add table for tracking transactions. items [kakeibo transactions] 2023-03-12T04:48:30Z Masaya Tojo <masaya@tojo.tokyo> # Add table for tracking items. -insert_transaction [kakeibo transactions] 2023-03-12T07:06:15Z Masaya Tojo <masaya@tojo.tokyo> # Add function to create new transaction. -insert_item [kakeibo items] 2023-03-12T07:25:23Z Masaya Tojo <masaya@tojo.tokyo> # Add function to add new item. -delete_item [kakeibo items] 2023-03-12T08:14:34Z Masaya Tojo <masaya@tojo.tokyo> # Add function to delete item. -delete_transaction [kakeibo transactions] 2023-03-12T08:40:47Z Masaya Tojo <masaya@tojo.tokyo> # Add function to delete transaction. update_timestamp [kakeibo] 2023-03-12T13:57:49Z Masaya Tojo <masaya@tojo.tokyo> # Add function to update timestamp. transactions_update_timestamp [transactions update_timestamp] 2023-03-12T14:10:42Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to transactions table. items_update_timestamp [items update_timestamp] 2023-03-12T14:14:13Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to items table. diff --git a/verify/delete_item.sql b/verify/delete_item.sql deleted file mode 100644 index b1c3dd7..0000000 --- a/verify/delete_item.sql +++ /dev/null @@ -1,37 +0,0 @@ --- Verify kakeibo:delete_item on pg - -BEGIN; - -SELECT has_function_privilege('kakeibo.delete_item(INTEGER)', 'EXECUTE'); - -SET search_path = kakeibo; - -DO $$ - DECLARE - v_transaction_id INTEGER; - v_item_id INTEGER; - v_item_id_2 INTEGER; - BEGIN - SELECT insert_transaction(0, '2023-03-15', 'テスト') INTO v_transaction_id; - SELECT insert_item(v_transaction_id, 'カテ', 'サブカテ', 298, 'メモ') INTO v_item_id; - SELECT insert_item(v_transaction_id, 'カテ', 'サブカテ', 298, 'メモ') INTO v_item_id_2; - - PERFORM * FROM items WHERE id = v_item_id; - IF NOT FOUND THEN - RAISE EXCEPTION 'Inserted data is not found.'; - END IF; - - PERFORM delete_item(v_item_id); - PERFORM * FROM items WHERE id = v_item_id; - IF FOUND THEN - RAISE EXCEPTION 'Deleted data still exists.'; - END IF; - - PERFORM * FROM items WHERE id = v_item_id_2; - IF NOT FOUND THEN - RAISE EXCEPTION 'Unexpected delete data.'; - END IF; - END -$$; - -ROLLBACK; diff --git a/verify/delete_transaction.sql b/verify/delete_transaction.sql deleted file mode 100644 index 9f4d1d8..0000000 --- a/verify/delete_transaction.sql +++ /dev/null @@ -1,43 +0,0 @@ --- Verify kakeibo:delete_transaction on pg - -BEGIN; - -SELECT has_function_privilege('kakeibo.delete_transaction(INTEGER)', 'EXECUTE'); - -SET search_path = kakeibo; - -DO $$ - DECLARE - v_transaction_id INTEGER; - v_transaction_id_2 INTEGER; - v_item_id INTEGER; - BEGIN - SELECT insert_transaction(0, '2023-03-15', 'テスト') INTO v_transaction_id; - SELECT insert_transaction(1, '2023-03-15', 'テスト2') INTO v_transaction_id_2; - SELECT insert_item(v_transaction_id, 'カテ', 'サブカテ', 298, 'メモ') INTO v_item_id; - - BEGIN - PERFORM delete_transaction(v_transaction_id); - EXCEPTION - WHEN foreign_key_violation THEN - END; - PERFORM * FROM transactions WHERE id = v_transaction_id; - IF NOT FOUND THEN - RAISE EXCEPTION 'Unexpected delete data.'; - END IF; - - PERFORM delete_item(v_item_id); - PERFORM delete_transaction(v_transaction_id); - PERFORM * FROM transactions WHERE id = v_transaction_id; - IF FOUND THEN - RAISE EXCEPTION 'Deleted data still exists.'; - END IF; - - PERFORM * FROM transactions WHERE id = v_transaction_id_2; - IF NOT FOUND THEN - RAISE EXCEPTION 'Unexpected delete data.'; - END IF; - END -$$; - -ROLLBACK; diff --git a/verify/insert_item.sql b/verify/insert_item.sql deleted file mode 100644 index f1ce212..0000000 --- a/verify/insert_item.sql +++ /dev/null @@ -1,31 +0,0 @@ --- Verify kakeibo:insert_item on pg - -BEGIN; - -SELECT has_function_privilege('kakeibo.insert_item(INTEGER, TEXT, TEXT, INTEGER, TEXT)', 'EXECUTE'); - -SET search_path = kakeibo; - -DO $$ - DECLARE - v_transaction_id INTEGER; - v_item_id INTEGER; - BEGIN - SELECT insert_transaction(0, '2023-03-15', 'テスト') INTO v_transaction_id; - SELECT insert_item(v_transaction_id, 'カテ', 'サブカテ', 298, 'メモ') INTO v_item_id; - - PERFORM * - FROM items - WHERE id = v_item_id - AND transaction_id = v_transaction_id - AND category = 'カテ' - AND subcategory = 'サブカテ' - AND amount = 298 - AND note = 'メモ'; - IF NOT FOUND THEN - RAISE EXCEPTION 'Inserted data is not found.'; - END IF; - END -$$; - -ROLLBACK; diff --git a/verify/insert_transaction.sql b/verify/insert_transaction.sql deleted file mode 100644 index 7f6893c..0000000 --- a/verify/insert_transaction.sql +++ /dev/null @@ -1,26 +0,0 @@ --- Verify kakeibo:insert_transaction on pg - -BEGIN; - -SELECT has_function_privilege('kakeibo.insert_transaction(INTEGER, DATE, TEXT)', 'EXECUTE'); - -SET search_path = kakeibo; - -DO $$ - DECLARE - v_id INTEGER; - BEGIN - SELECT insert_transaction(0, '2023-03-15', 'テスト') INTO v_id; - PERFORM * - FROM transactions - WHERE id = v_id - AND type_code = 0 - AND date = '2023-03-15' - AND note = 'テスト'; - IF NOT FOUND THEN - RAISE EXCEPTION 'Inserted data is not found.'; - END IF; - END -$$; - -ROLLBACK; |