libeufin-bank-0009.sql (3133B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 BEGIN; 17 18 SELECT _v.register_patch('libeufin-bank-0009', NULL, NULL); 19 SET search_path TO libeufin_bank; 20 21 -- Add missing unique constraints 22 ALTER TABLE taler_exchange_outgoing ADD UNIQUE (exchange_outgoing_id); 23 ALTER TABLE taler_exchange_incoming ADD UNIQUE (exchange_incoming_id); 24 ALTER TABLE taler_withdrawal_operations ADD UNIQUE (withdrawal_id); 25 26 CREATE TYPE transfer_status AS ENUM 27 ('permanent_failure' 28 ,'success' 29 ); 30 31 CREATE TABLE transfer_operations 32 ( transfer_operation_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 33 ,request_uid BYTEA UNIQUE NOT NULL CHECK (LENGTH(request_uid)=64) 34 ,wtid BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid)=32) 35 ,amount taler_amount NOT NULL 36 ,exchange_base_url TEXT NOT NULL 37 ,transfer_date INT8 NOT NULL 38 ,exchange_outgoing_id INT8 UNIQUE REFERENCES taler_exchange_outgoing(exchange_outgoing_id) ON DELETE CASCADE 39 ,creditor_payto TEXT NOT NULL 40 ,status transfer_status NOT NULL 41 ,status_msg TEXT 42 ,exchange_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) ON DELETE CASCADE 43 ,CONSTRAINT transfer_operations_polymorphism CHECK( 44 CASE status 45 WHEN 'success' THEN exchange_outgoing_id IS NOT NULL 46 ELSE exchange_outgoing_id IS NULL 47 END 48 ) 49 ); 50 COMMENT ON TABLE transfer_operations 51 IS 'Operation table for idempotent wire gateway transfers with status.'; 52 53 -- Migrate data from taler_exchange_outgoing to transfer_operations 54 INSERT INTO transfer_operations(transfer_operation_id, request_uid, amount, wtid, exchange_base_url, transfer_date, exchange_outgoing_id, creditor_payto, status, status_msg, exchange_id) 55 SELECT bank_transaction_id, request_uid, amount, wtid, exchange_base_url, transaction_date, exchange_outgoing_id, creditor_payto, 'success'::transfer_status, NULL, bank_account_id 56 FROM taler_exchange_outgoing JOIN bank_account_transactions ON bank_transaction = bank_transaction_id; 57 58 CREATE INDEX transfer_operations_status_index ON transfer_operations (status); 59 COMMENT ON INDEX transfer_operations_status_index IS 'for listing taler transfers by status'; 60 61 CREATE INDEX transfer_operations_account_index ON transfer_operations (exchange_id); 62 COMMENT ON INDEX transfer_operations_account_index IS 'for listing taler transfers by account'; 63 64 -- Remove unused columns 65 ALTER TABLE taler_exchange_outgoing 66 DROP COLUMN request_uid, 67 DROP COLUMN creditor_account_id, 68 DROP COLUMN wtid, 69 DROP COLUMN exchange_base_url; 70 71 COMMIT;