0002-reserves_open_deposits.sql (3544B)
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_deposits( 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_deposits'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' 30 ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' 31 ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' 32 ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' 33 ',contribution taler_amount NOT NULL' 34 ') %s ;' 35 ,table_name 36 ,'PARTITION BY HASH (coin_pub)' 37 ,partition_suffix 38 ); 39 PERFORM comment_partitioned_table( 40 'coin contributions paying for a reserve to remain open' 41 ,table_name 42 ,partition_suffix 43 ); 44 PERFORM comment_partitioned_column( 45 'Identifies the specific reserve being paid for (possibly together with reserve_sig).' 46 ,'reserve_pub' 47 ,table_name 48 ,partition_suffix 49 ); 50 END 51 $$; 52 53 54 CREATE FUNCTION constrain_table_reserves_open_deposits( 55 IN partition_suffix TEXT 56 ) 57 RETURNS void 58 LANGUAGE plpgsql 59 AS $$ 60 DECLARE 61 table_name TEXT default 'reserves_open_deposits'; 62 BEGIN 63 table_name = concat_ws('_', table_name, partition_suffix); 64 EXECUTE FORMAT ( 65 'ALTER TABLE ' || table_name || ' ' 66 'ADD CONSTRAINT ' || table_name || '_coin_unique ' 67 'PRIMARY KEY (coin_pub,coin_sig)' 68 ); 69 EXECUTE FORMAT ( 70 'CREATE INDEX ' || table_name || '_by_uuid ' 71 'ON ' || table_name || ' ' 72 '(reserve_open_deposit_uuid);' 73 ); 74 EXECUTE FORMAT ( 75 'CREATE INDEX ' || table_name || '_by_reserve ' 76 'ON ' || table_name || ' ' 77 '(reserve_pub);' 78 ); 79 END 80 $$; 81 82 83 CREATE OR REPLACE FUNCTION reserves_open_deposits_insert_trigger() 84 RETURNS trigger 85 LANGUAGE plpgsql 86 AS $$ 87 BEGIN 88 INSERT INTO exchange.coin_history 89 (coin_pub 90 ,table_name 91 ,serial_id) 92 VALUES 93 (NEW.coin_pub 94 ,'reserves_open_deposits' 95 ,NEW.reserve_open_deposit_uuid); 96 RETURN NEW; 97 END $$; 98 COMMENT ON FUNCTION reserves_open_deposits_insert_trigger() 99 IS 'Automatically generate coin history entry.'; 100 101 102 CREATE FUNCTION master_table_reserves_open_deposits() 103 RETURNS VOID 104 LANGUAGE plpgsql 105 AS $$ 106 BEGIN 107 CREATE TRIGGER reserves_open_deposits_on_insert 108 AFTER INSERT 109 ON reserves_open_deposits 110 FOR EACH ROW EXECUTE FUNCTION reserves_open_deposits_insert_trigger(); 111 END $$; 112 113 114 INSERT INTO exchange_tables 115 (name 116 ,version 117 ,action 118 ,partitioned 119 ,by_range) 120 VALUES 121 ('reserves_open_deposits' 122 ,'exchange-0002' 123 ,'create' 124 ,TRUE 125 ,FALSE), 126 ('reserves_open_deposits' 127 ,'exchange-0002' 128 ,'constrain' 129 ,TRUE 130 ,FALSE), 131 ('reserves_open_deposits' 132 ,'exchange-0002' 133 ,'master' 134 ,TRUE 135 ,FALSE);