0002-reserves_close.sql (3763B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2023 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_reserves_close( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT default 'reserves_close'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',reserve_pub BYTEA NOT NULL' 30 ',execution_date INT8 NOT NULL' 31 ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' 32 ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' 33 ',amount taler_amount NOT NULL' 34 ',closing_fee taler_amount NOT NULL' 35 ',close_request_row INT8 NOT NULL DEFAULT(0)' 36 ') %s ;' 37 ,table_name 38 ,'PARTITION BY HASH (reserve_pub)' 39 ,partition_suffix 40 ); 41 PERFORM comment_partitioned_table( 42 'wire transfers executed by the reserve to close reserves' 43 ,table_name 44 ,partition_suffix 45 ); 46 PERFORM comment_partitioned_column( 47 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.' 48 ,'wire_target_h_payto' 49 ,table_name 50 ,partition_suffix 51 ); 52 END 53 $$; 54 55 56 CREATE FUNCTION constrain_table_reserves_close( 57 IN partition_suffix TEXT 58 ) 59 RETURNS void 60 LANGUAGE plpgsql 61 AS $$ 62 DECLARE 63 table_name TEXT default 'reserves_close'; 64 BEGIN 65 table_name = concat_ws('_', table_name, partition_suffix); 66 EXECUTE FORMAT ( 67 'ALTER TABLE ' || table_name || 68 ' ADD CONSTRAINT ' || table_name || '_close_uuid_pkey' 69 ' PRIMARY KEY (close_uuid)' 70 ); 71 EXECUTE FORMAT ( 72 'CREATE INDEX ' || table_name || '_by_reserve_pub_index ' 73 'ON ' || table_name || ' (reserve_pub);' 74 ); 75 END $$; 76 77 78 CREATE FUNCTION foreign_table_reserves_close() 79 RETURNS void 80 LANGUAGE plpgsql 81 AS $$ 82 DECLARE 83 table_name TEXT default 'reserves_close'; 84 BEGIN 85 EXECUTE FORMAT ( 86 'ALTER TABLE ' || table_name || 87 ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' 88 ' FOREIGN KEY (reserve_pub)' 89 ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE' 90 ); 91 END $$; 92 93 94 CREATE OR REPLACE FUNCTION reserves_close_insert_trigger() 95 RETURNS trigger 96 LANGUAGE plpgsql 97 AS $$ 98 BEGIN 99 INSERT INTO reserve_history 100 (reserve_pub 101 ,table_name 102 ,serial_id) 103 VALUES 104 (NEW.reserve_pub 105 ,'reserves_close' 106 ,NEW.close_uuid); 107 RETURN NEW; 108 END $$; 109 COMMENT ON FUNCTION reserves_close_insert_trigger() 110 IS 'Automatically generate reserve history entry.'; 111 112 113 CREATE FUNCTION master_table_reserves_close() 114 RETURNS VOID 115 LANGUAGE plpgsql 116 AS $$ 117 BEGIN 118 CREATE TRIGGER reserves_close_on_insert 119 AFTER INSERT 120 ON reserves_close 121 FOR EACH ROW EXECUTE FUNCTION reserves_close_insert_trigger(); 122 END $$; 123 124 125 INSERT INTO exchange_tables 126 (name 127 ,version 128 ,action 129 ,partitioned 130 ,by_range) 131 VALUES 132 ('reserves_close' 133 ,'exchange-0002' 134 ,'create' 135 ,TRUE 136 ,FALSE), 137 ('reserves_close' 138 ,'exchange-0002' 139 ,'constrain' 140 ,TRUE 141 ,FALSE), 142 ('reserves_close' 143 ,'exchange-0002' 144 ,'foreign' 145 ,TRUE 146 ,FALSE), 147 ('reserves_close' 148 ,'exchange-0002' 149 ,'master' 150 ,TRUE 151 ,FALSE);