libeufin-bank-0015.sql (3418B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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-0015', NULL, NULL); 19 20 SET search_path TO libeufin_bank; 21 22 -- Allow withdrawal not linked to a bank account 23 -- Store the exchange account ID instead of the payto 24 -- Support non reserve withdrawals 25 ALTER TABLE taler_withdrawal_operations ALTER COLUMN wallet_bank_account DROP NOT NULL, 26 ADD COLUMN exchange_bank_account INT8 REFERENCES bank_accounts(bank_account_id) ON DELETE SET NULL, 27 ADD COLUMN type taler_incoming_type NOT NULL DEFAULT 'reserve', 28 DROP CONSTRAINT taler_withdrawal_operations_reserve_pub_key; 29 UPDATE taler_withdrawal_operations SET exchange_bank_account=(SELECT bank_account_id FROM bank_accounts WHERE internal_payto=selected_exchange_payto); 30 ALTER TABLE taler_withdrawal_operations DROP COLUMN selected_exchange_payto; 31 CREATE UNIQUE INDEX taler_withdrawal_operations_unique_reserve_pub 32 ON taler_withdrawal_operations (reserve_pub) WHERE type = 'reserve'; 33 34 -- Add outgoing transactions metadata field 35 ALTER TABLE transfer_operations ADD COLUMN metadata TEXT; 36 37 -- Replace unused wad type with new mapping type 38 ALTER TYPE taler_incoming_type RENAME VALUE 'wad' TO 'map'; 39 40 ALTER TABLE taler_exchange_incoming 41 ADD COLUMN authorization_pub BYTEA CHECK (LENGTH(authorization_pub)=32), 42 ADD COLUMN authorization_sig BYTEA CHECK (LENGTH(authorization_sig)=64); 43 44 CREATE TABLE prepared_transfers ( 45 type taler_incoming_type NOT NULL, 46 account_pub BYTEA NOT NULL CHECK (LENGTH(account_pub)=32), 47 authorization_pub BYTEA UNIQUE NOT NULL CHECK (LENGTH(authorization_pub)=32), 48 authorization_sig BYTEA NOT NULL CHECK (LENGTH(authorization_sig)=64), 49 recurrent BOOLEAN NOT NULL, 50 withdrawal_id INT8 UNIQUE REFERENCES taler_withdrawal_operations(withdrawal_id), 51 registered_at INT8 NOT NULL, 52 bank_transaction_id INT8 UNIQUE REFERENCES bank_account_transactions(bank_transaction_id) ON DELETE CASCADE 53 ); 54 CREATE UNIQUE INDEX prepared_transfers_unique_reserve_pub 55 ON prepared_transfers (account_pub) WHERE type = 'reserve'; 56 CREATE INDEX prepared_transfers_timestamp 57 ON prepared_transfers (registered_at); 58 59 CREATE TABLE pending_recurrent_incoming_transactions( 60 bank_transaction_id INT8 NOT NULL UNIQUE REFERENCES bank_account_transactions(bank_transaction_id) ON DELETE CASCADE, 61 debtor_account_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) ON DELETE CASCADE, 62 authorization_pub BYTEA NOT NULL REFERENCES prepared_transfers(authorization_pub) 63 ); 64 CREATE INDEX pending_recurrent_incoming_transactions_auth_pub 65 ON pending_recurrent_incoming_transactions (authorization_pub); 66 67 COMMIT;