0002-reserve_history.sql (3497B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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_reserve_history ( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'reserve_history'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(reserve_history_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY' 29 ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' 30 ',table_name TEXT NOT NULL' 31 ',serial_id INT8 NOT NULL' 32 ') %s ;' 33 ,table_name 34 ,'PARTITION BY HASH (reserve_pub)' 35 ,partition_suffix 36 ); 37 PERFORM comment_partitioned_table( 38 'Links to tables with entries that affected the transaction history of a reserve.' 39 ,table_name 40 ,partition_suffix 41 ); 42 PERFORM comment_partitioned_column( 43 'For which reserve is this a history entry' 44 ,'reserve_pub' 45 ,table_name 46 ,partition_suffix 47 ); 48 PERFORM comment_partitioned_column( 49 'In which table is the history entry' 50 ,'table_name' 51 ,table_name 52 ,partition_suffix 53 ); 54 PERFORM comment_partitioned_column( 55 'Which is the generated serial ID of the entry in the table' 56 ,'serial_id' 57 ,table_name 58 ,partition_suffix 59 ); 60 PERFORM comment_partitioned_column( 61 'Monotonic counter, used to generate Etags for caching' 62 ,'reserve_history_serial_id' 63 ,table_name 64 ,partition_suffix 65 ); 66 END 67 $$; 68 69 70 CREATE FUNCTION constrain_table_reserve_history( 71 IN partition_suffix TEXT 72 ) 73 RETURNS void 74 LANGUAGE plpgsql 75 AS $$ 76 DECLARE 77 table_name TEXT DEFAULT 'reserve_history'; 78 BEGIN 79 table_name = concat_ws('_', table_name, partition_suffix); 80 EXECUTE FORMAT ( 81 'ALTER TABLE ' || table_name || 82 ' ADD CONSTRAINT ' || table_name || '_reserve_history_serial_id_pkey' 83 ' PRIMARY KEY (reserve_history_serial_id) ' 84 ',ADD CONSTRAINT ' || table_name || '_reserve_entry_key' 85 ' UNIQUE (reserve_pub, table_name, serial_id)' 86 ); 87 EXECUTE FORMAT ( 88 'CREATE INDEX ' || table_name || '_reserve_by_time' 89 ' ON ' || table_name || ' ' 90 '(reserve_pub' 91 ',reserve_history_serial_id DESC' 92 ');' 93 ); 94 END 95 $$; 96 97 98 CREATE FUNCTION foreign_table_reserve_history() 99 RETURNS void 100 LANGUAGE plpgsql 101 AS $$ 102 DECLARE 103 table_name TEXT DEFAULT 'reserve_history'; 104 BEGIN 105 EXECUTE FORMAT ( 106 'ALTER TABLE ' || table_name || 107 ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' 108 ' FOREIGN KEY (reserve_pub) ' 109 ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE' 110 ); 111 END 112 $$; 113 114 115 116 INSERT INTO exchange_tables 117 (name 118 ,version 119 ,action 120 ,partitioned 121 ,by_range) 122 VALUES 123 ('reserve_history' 124 ,'exchange-0002' 125 ,'create' 126 ,TRUE 127 ,FALSE), 128 ('reserve_history' 129 ,'exchange-0002' 130 ,'constrain' 131 ,TRUE 132 ,FALSE), 133 ('reserve_history' 134 ,'exchange-0002' 135 ,'foreign' 136 ,TRUE 137 ,FALSE) 138 ;