0002-reserves_open_requests.sql (3756B)
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_open_requests( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT default 'reserves_open_requests'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',reserve_pub BYTEA NOT NULL' 30 ',request_timestamp INT8 NOT NULL' 31 ',expiration_date INT8 NOT NULL' 32 ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' 33 ',reserve_payment taler_amount NOT NULL' 34 ',requested_purse_limit INT4 NOT NULL' 35 ') %s ;' 36 ,table_name 37 ,'PARTITION BY HASH (reserve_pub)' 38 ,partition_suffix 39 ); 40 PERFORM comment_partitioned_table ( 41 'requests to keep a reserve open' 42 ,table_name 43 ,partition_suffix 44 ); 45 PERFORM comment_partitioned_column ( 46 'Fee to pay for the request from the reserve balance itself.' 47 ,'reserve_payment' 48 ,table_name 49 ,partition_suffix 50 ); 51 END 52 $$; 53 54 55 CREATE FUNCTION constrain_table_reserves_open_requests( 56 IN partition_suffix TEXT 57 ) 58 RETURNS void 59 LANGUAGE plpgsql 60 AS $$ 61 DECLARE 62 table_name TEXT default 'reserves_open_requests'; 63 BEGIN 64 table_name = concat_ws('_', table_name, partition_suffix); 65 EXECUTE FORMAT ( 66 'ALTER TABLE ' || table_name || 67 ' ADD CONSTRAINT ' || table_name || '_by_uuid' 68 ' PRIMARY KEY (open_request_uuid)' 69 ',ADD CONSTRAINT ' || table_name || '_by_time' 70 ' UNIQUE (reserve_pub,request_timestamp)' 71 ); 72 END 73 $$; 74 75 76 CREATE FUNCTION foreign_table_reserves_open_requests() 77 RETURNS void 78 LANGUAGE plpgsql 79 AS $$ 80 DECLARE 81 table_name TEXT default 'reserves_open_requests'; 82 BEGIN 83 EXECUTE FORMAT ( 84 'ALTER TABLE ' || table_name || 85 ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub ' 86 ' FOREIGN KEY (reserve_pub)' 87 ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE' 88 ); 89 END 90 $$; 91 92 93 CREATE OR REPLACE FUNCTION reserves_open_requests_insert_trigger() 94 RETURNS trigger 95 LANGUAGE plpgsql 96 AS $$ 97 BEGIN 98 INSERT INTO reserve_history 99 (reserve_pub 100 ,table_name 101 ,serial_id) 102 VALUES 103 (NEW.reserve_pub 104 ,'reserves_open_requests' 105 ,NEW.open_request_uuid); 106 RETURN NEW; 107 END $$; 108 COMMENT ON FUNCTION reserves_open_requests_insert_trigger() 109 IS 'Automatically generate reserve history entry.'; 110 111 112 CREATE FUNCTION master_table_reserves_open_requests() 113 RETURNS VOID 114 LANGUAGE plpgsql 115 AS $$ 116 BEGIN 117 CREATE TRIGGER reserves_open_requests_on_insert 118 AFTER INSERT 119 ON reserves_open_requests 120 FOR EACH ROW EXECUTE FUNCTION reserves_open_requests_insert_trigger(); 121 END $$; 122 123 124 INSERT INTO exchange_tables 125 (name 126 ,version 127 ,action 128 ,partitioned 129 ,by_range) 130 VALUES 131 ('reserves_open_requests' 132 ,'exchange-0002' 133 ,'create' 134 ,TRUE 135 ,FALSE), 136 ('reserves_open_requests' 137 ,'exchange-0002' 138 ,'constrain' 139 ,TRUE 140 ,FALSE), 141 ('reserves_open_requests' 142 ,'exchange-0002' 143 ,'foreign' 144 ,TRUE 145 ,FALSE), 146 ('reserves_open_requests' 147 ,'exchange-0002' 148 ,'master' 149 ,TRUE 150 ,FALSE);