0002-recoup_refresh.sql (5535B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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_recoup_refresh( 19 IN partition_suffix TEXT DEFAULT NULL 20 ) 21 RETURNS VOID 22 LANGUAGE plpgsql 23 AS $$ 24 DECLARE 25 table_name TEXT DEFAULT 'recoup_refresh'; 26 BEGIN 27 PERFORM create_partitioned_table( 28 'CREATE TABLE %I' 29 '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' 30 ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' 31 ',known_coin_id BIGINT NOT NULL' 32 ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' 33 ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' 34 ',amount taler_amount NOT NULL' 35 ',recoup_timestamp INT8 NOT NULL' 36 ',refresh_id INT8 NOT NULL' 37 ') %s ;' 38 ,table_name 39 ,'PARTITION BY HASH (coin_pub)' 40 ,partition_suffix 41 ); 42 PERFORM comment_partitioned_table( 43 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.' 44 ,table_name 45 ,partition_suffix 46 ); 47 PERFORM comment_partitioned_column( 48 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!' 49 ,'coin_pub' 50 ,table_name 51 ,partition_suffix 52 ); 53 PERFORM comment_partitioned_column( 54 'Used for garbage collection (in the absence of foreign constraints, in the future)' 55 ,'known_coin_id' 56 ,table_name 57 ,partition_suffix 58 ); 59 PERFORM comment_partitioned_column( 60 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).' 61 ,'refresh_id' 62 ,table_name 63 ,partition_suffix 64 ); 65 PERFORM comment_partitioned_column( 66 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.' 67 ,'coin_blind' 68 ,table_name 69 ,partition_suffix 70 ); 71 END 72 $$; 73 74 75 CREATE FUNCTION constrain_table_recoup_refresh( 76 IN partition_suffix TEXT 77 ) 78 RETURNS VOID 79 LANGUAGE plpgsql 80 AS $$ 81 DECLARE 82 table_name TEXT DEFAULT 'recoup_refresh'; 83 BEGIN 84 table_name = concat_ws('_', table_name, partition_suffix); 85 86 EXECUTE FORMAT ( 87 'CREATE INDEX ' || table_name || '_by_refresh_id_index' 88 ' ON ' || table_name || ' ' 89 '(refresh_id);' 90 ); 91 EXECUTE FORMAT ( 92 'COMMENT ON INDEX ' || table_name || '_by_refresh_id_index ' 93 'IS ' || quote_literal('used in exchange_do_melt for zombie coins (rare)') || ';' 94 ); 95 96 EXECUTE FORMAT ( 97 'CREATE INDEX ' || table_name || '_by_coin_pub_index' 98 ' ON ' || table_name || ' ' 99 '(coin_pub);' 100 ); 101 EXECUTE FORMAT ( 102 'ALTER TABLE ' || table_name || 103 ' ADD CONSTRAINT ' || table_name || '_recoup_refresh_uuid_key' 104 ' UNIQUE (recoup_refresh_uuid) ' 105 ); 106 END 107 $$; 108 109 110 CREATE FUNCTION foreign_table_recoup_refresh() 111 RETURNS VOID 112 LANGUAGE plpgsql 113 AS $$ 114 DECLARE 115 table_name TEXT DEFAULT 'recoup_refresh'; 116 BEGIN 117 EXECUTE FORMAT ( 118 'ALTER TABLE ' || table_name || 119 ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' 120 ' FOREIGN KEY (coin_pub) ' 121 ' REFERENCES known_coins (coin_pub)' 122 ',ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id' 123 ' FOREIGN KEY (known_coin_id) ' 124 ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' 125 ',ADD CONSTRAINT ' || table_name || '_foreign_refresh_id' 126 ' FOREIGN KEY (refresh_id) ' 127 ' REFERENCES refresh (refresh_id) ON DELETE CASCADE' 128 ); 129 END 130 $$; 131 132 133 CREATE OR REPLACE FUNCTION recoup_refresh_insert_trigger() 134 RETURNS trigger 135 LANGUAGE plpgsql 136 AS $$ 137 BEGIN 138 INSERT INTO exchange.coin_history 139 (coin_pub 140 ,table_name 141 ,serial_id) 142 VALUES 143 (NEW.coin_pub 144 ,'recoup_refresh::NEW' 145 ,NEW.recoup_refresh_uuid); 146 INSERT INTO exchange.coin_history 147 (coin_pub 148 ,table_name 149 ,serial_id) 150 SELECT 151 refresh.old_coin_pub 152 ,'recoup_refresh::OLD' 153 ,NEW.recoup_refresh_uuid 154 FROM refresh 155 WHERE refresh.refresh_id = NEW.refresh_id; 156 RETURN NEW; 157 END $$; 158 COMMENT ON FUNCTION coin_deposits_insert_trigger() 159 IS 'Automatically generate coin history entry.'; 160 161 162 CREATE FUNCTION master_table_recoup_refresh() 163 RETURNS VOID 164 LANGUAGE plpgsql 165 AS $$ 166 BEGIN 167 CREATE TRIGGER recoup_refresh_on_insert 168 AFTER INSERT 169 ON recoup_refresh 170 FOR EACH ROW EXECUTE FUNCTION recoup_refresh_insert_trigger(); 171 END $$; 172 173 174 175 INSERT INTO exchange_tables 176 (name 177 ,version 178 ,action 179 ,partitioned 180 ,by_range) 181 VALUES 182 ('recoup_refresh' 183 ,'exchange-0002' 184 ,'create' 185 ,TRUE 186 ,FALSE), 187 ('recoup_refresh' 188 ,'exchange-0002' 189 ,'constrain' 190 ,TRUE 191 ,FALSE), 192 ('recoup_refresh' 193 ,'exchange-0002' 194 ,'foreign' 195 ,TRUE 196 ,FALSE), 197 ('recoup_refresh' 198 ,'exchange-0002' 199 ,'master' 200 ,TRUE 201 ,FALSE);