0002-wire_out.sql (3405B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 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 17 CREATE FUNCTION create_table_wire_out( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'wire_out'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE IF NOT EXISTS %I' 28 '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',execution_date INT8 NOT NULL' 30 ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' 31 ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' 32 ',exchange_account_section TEXT NOT NULL' 33 ',amount taler_amount NOT NULL' 34 ') %s ;' 35 ,table_name 36 ,'PARTITION BY HASH (wtid_raw)' 37 ,partition_suffix 38 ); 39 PERFORM comment_partitioned_table( 40 'wire transfers the exchange has executed' 41 ,table_name 42 ,partition_suffix 43 ); 44 PERFORM comment_partitioned_column( 45 'identifies the configuration section with the debit account of this payment' 46 ,'exchange_account_section' 47 ,table_name 48 ,partition_suffix 49 ); 50 PERFORM comment_partitioned_column( 51 'Identifies the credited bank account and KYC status' 52 ,'wire_target_h_payto' 53 ,table_name 54 ,partition_suffix 55 ); 56 END 57 $$; 58 59 60 CREATE FUNCTION constrain_table_wire_out( 61 IN partition_suffix TEXT 62 ) 63 RETURNS void 64 LANGUAGE plpgsql 65 AS $$ 66 DECLARE 67 table_name TEXT DEFAULT 'wire_out'; 68 BEGIN 69 table_name = concat_ws('_', table_name, partition_suffix); 70 EXECUTE FORMAT ( 71 'CREATE INDEX ' || table_name || '_by_wire_target_h_payto_index ' 72 'ON ' || table_name || ' ' 73 '(wire_target_h_payto);' 74 ); 75 EXECUTE FORMAT ( 76 'ALTER TABLE ' || table_name || 77 ' ADD CONSTRAINT ' || table_name || '_wireout_uuid_pkey' 78 ' PRIMARY KEY (wireout_uuid)' 79 ); 80 END 81 $$; 82 83 84 CREATE FUNCTION wire_out_delete_trigger() 85 RETURNS trigger 86 LANGUAGE plpgsql 87 AS $$ 88 BEGIN 89 DELETE FROM exchange.aggregation_tracking 90 WHERE wtid_raw = OLD.wtid_raw; 91 RETURN OLD; 92 END $$; 93 COMMENT ON FUNCTION wire_out_delete_trigger() 94 IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint and conflicted with nice partitioning.'; 95 96 97 CREATE FUNCTION master_table_wire_out() 98 RETURNS void 99 LANGUAGE plpgsql 100 AS $$ 101 BEGIN 102 CREATE TRIGGER wire_out_on_delete 103 AFTER DELETE 104 ON wire_out 105 FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger(); 106 END $$; 107 108 109 INSERT INTO exchange_tables 110 (name 111 ,version 112 ,action 113 ,partitioned 114 ,by_range) 115 VALUES 116 ('wire_out' 117 ,'exchange-0002' 118 ,'create' 119 ,TRUE 120 ,FALSE), 121 ('wire_out' 122 ,'exchange-0002' 123 ,'constrain' 124 ,TRUE 125 ,FALSE), 126 ('wire_out' 127 ,'exchange-0002' 128 ,'master' 129 ,TRUE 130 ,FALSE);