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