diff options
Diffstat (limited to 'database-versioning/libeufin-bank-0002.sql')
| -rw-r--r-- | database-versioning/libeufin-bank-0002.sql | 90 |
1 files changed, 90 insertions, 0 deletions
diff --git a/database-versioning/libeufin-bank-0002.sql b/database-versioning/libeufin-bank-0002.sql new file mode 100644 index 00000000..0859d80f --- /dev/null +++ b/database-versioning/libeufin-bank-0002.sql @@ -0,0 +1,90 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2023 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> + +BEGIN; + +SELECT _v.register_patch('libeufin-bank-0002', NULL, NULL); +SET search_path TO libeufin_bank; + +-- Forget about all pending operations +DELETE FROM cashout_operations WHERE local_transaction IS NULL; + +-- Remove challenge logic from cashout tables +ALTER TABLE cashout_operations + DROP COLUMN challenge, + DROP COLUMN tan_channel, + DROP COLUMN tan_info, + DROP COLUMN aborted, + ALTER COLUMN local_transaction SET NOT NULL; + +DROP TABLE challenges; + +ALTER TABLE customers + ADD tan_channel tan_enum NULL; + +CREATE TYPE op_enum + AS ENUM ('account_reconfig', 'account_auth_reconfig', 'account_delete', 'bank_transaction', 'cashout', 'withdrawal'); + +CREATE TABLE tan_challenges + (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,body TEXT NOT NULL + ,op op_enum NOT NULL + ,code TEXT NOT NULL + ,creation_date INT8 NOT NULL + ,expiration_date INT8 NOT NULL + ,retransmission_date INT8 NOT NULL DEFAULT 0 + ,confirmation_date INT8 DEFAULT NULL + ,retry_counter INT4 NOT NULL + ,customer INT8 NOT NULL + REFERENCES customers(customer_id) + ON DELETE CASCADE + ,tan_channel tan_enum NULL DEFAULT NULL + ,tan_info TEXT NULL DEFAULT NULL +); +COMMENT ON TABLE tan_challenges IS 'Stores 2FA challenges'; +COMMENT ON COLUMN tan_challenges.op IS 'The protected operation to run after the challenge'; +COMMENT ON COLUMN tan_challenges.code IS 'The pin code sent to the user and verified'; +COMMENT ON COLUMN tan_challenges.creation_date IS 'Creation date of the code'; +COMMENT ON COLUMN tan_challenges.retransmission_date IS 'When did we last transmit the challenge to the user'; +COMMENT ON COLUMN tan_challenges.expiration_date IS 'When will the code expire'; +COMMENT ON COLUMN tan_challenges.confirmation_date IS 'When was this challenge successfully verified, NULL if pending'; +COMMENT ON COLUMN tan_challenges.retry_counter IS 'How many tries are left for this code must be > 0'; +COMMENT ON COLUMN tan_challenges.tan_channel IS 'TAN channel to use, if null use customer configured one'; +COMMENT ON COLUMN tan_challenges.tan_info IS 'TAN info to use, if null use customer configured one'; + +CREATE INDEX tan_challenges_expiration_index + ON tan_challenges (expiration_date); +COMMENT ON INDEX tan_challenges_expiration_index + IS 'for garbage collection'; + +CREATE INDEX bearer_tokens_expiration_index + ON bearer_tokens (expiration_time); +COMMENT ON INDEX bearer_tokens_expiration_index + IS 'for garbage collection'; + +CREATE INDEX bank_account_transactions_expiration_index + ON bank_account_transactions (transaction_date); +COMMENT ON INDEX bank_account_transactions_expiration_index + IS 'for garbage collection'; + +ALTER TABLE taler_withdrawal_operations + ADD creation_date INT8 NOT NULL; +CREATE INDEX taler_withdrawal_operations_expiration_index + ON taler_withdrawal_operations (creation_date); +COMMENT ON INDEX taler_withdrawal_operations_expiration_index + IS 'for garbage collection'; + + +COMMIT; |