0002-close_requests.sql (4496B)
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_close_requests( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'close_requests'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I ' 28 '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' 30 ',close_timestamp INT8 NOT NULL' 31 ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' 32 ',close taler_amount NOT NULL' 33 ',close_fee taler_amount NOT NULL' 34 ',payto_uri TEXT NOT NULL' 35 ',done BOOL NOT NULL DEFAULT(FALSE)' 36 ',PRIMARY KEY (reserve_pub,close_timestamp)' 37 ') %s ;' 38 ,table_name 39 ,'PARTITION BY HASH (reserve_pub)' 40 ,partition_suffix 41 ); 42 PERFORM comment_partitioned_table( 43 'Explicit requests by a reserve owner to close a reserve immediately' 44 ,table_name 45 ,partition_suffix 46 ); 47 PERFORM comment_partitioned_column( 48 'When the request was created by the client' 49 ,'close_timestamp' 50 ,table_name 51 ,partition_suffix 52 ); 53 PERFORM comment_partitioned_column( 54 'Signature affirming that the reserve is to be closed' 55 ,'reserve_sig' 56 ,table_name 57 ,partition_suffix 58 ); 59 PERFORM comment_partitioned_column( 60 'Balance of the reserve at the time of closing, to be wired to the associated bank account (minus the closing fee)' 61 ,'close' 62 ,table_name 63 ,partition_suffix 64 ); 65 PERFORM comment_partitioned_column( 66 'Identifies the credited bank account. Optional.' 67 ,'payto_uri' 68 ,table_name 69 ,partition_suffix 70 ); 71 END $$; 72 73 74 CREATE FUNCTION constrain_table_close_requests( 75 IN partition_suffix TEXT 76 ) 77 RETURNS VOID 78 LANGUAGE plpgsql 79 AS $$ 80 DECLARE 81 table_name TEXT DEFAULT 'close_requests'; 82 BEGIN 83 table_name = concat_ws('_', table_name, partition_suffix); 84 EXECUTE FORMAT ( 85 'CREATE INDEX ' || table_name || '_by_close_request_uuid_index ' 86 'ON ' || table_name || ' ' 87 '(close_request_serial_id);' 88 ); 89 EXECUTE FORMAT ( 90 'CREATE INDEX ' || table_name || '_by_close_request_done_index ' 91 'ON ' || table_name || ' ' 92 '(done);' 93 ); 94 EXECUTE FORMAT ( 95 'ALTER TABLE ' || table_name || 96 ' ADD CONSTRAINT ' || table_name || '_close_request_uuid_pkey' 97 ' UNIQUE (close_request_serial_id)' 98 ); 99 END 100 $$; 101 102 103 CREATE FUNCTION foreign_table_close_requests() 104 RETURNS VOID 105 LANGUAGE plpgsql 106 AS $$ 107 DECLARE 108 table_name TEXT DEFAULT 'close_requests'; 109 BEGIN 110 EXECUTE FORMAT ( 111 'ALTER TABLE ' || table_name || 112 ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' 113 ' FOREIGN KEY (reserve_pub) ' 114 ' REFERENCES reserves(reserve_pub) ON DELETE CASCADE' 115 ); 116 END 117 $$; 118 119 120 CREATE OR REPLACE FUNCTION close_requests_insert_trigger() 121 RETURNS trigger 122 LANGUAGE plpgsql 123 AS $$ 124 BEGIN 125 INSERT INTO reserve_history 126 (reserve_pub 127 ,table_name 128 ,serial_id) 129 VALUES 130 (NEW.reserve_pub 131 ,'close_requests' 132 ,NEW.close_request_serial_id); 133 RETURN NEW; 134 END $$; 135 COMMENT ON FUNCTION close_requests_insert_trigger() 136 IS 'Automatically generate reserve history entry.'; 137 138 139 CREATE FUNCTION master_table_close_requests() 140 RETURNS VOID 141 LANGUAGE plpgsql 142 AS $$ 143 BEGIN 144 CREATE TRIGGER close_requests_on_insert 145 AFTER INSERT 146 ON close_requests 147 FOR EACH ROW EXECUTE FUNCTION close_requests_insert_trigger(); 148 END $$; 149 150 151 152 INSERT INTO exchange_tables 153 (name 154 ,version 155 ,action 156 ,partitioned 157 ,by_range) 158 VALUES 159 ('close_requests' 160 ,'exchange-0002' 161 ,'create' 162 ,TRUE 163 ,FALSE), 164 ('close_requests' 165 ,'exchange-0002' 166 ,'constrain' 167 ,TRUE 168 ,FALSE), 169 ('close_requests' 170 ,'exchange-0002' 171 ,'foreign' 172 ,TRUE 173 ,FALSE), 174 ('close_requests' 175 ,'exchange-0002' 176 ,'master' 177 ,TRUE 178 ,FALSE);