0002-coin_deposits.sql (3951B)
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_coin_deposits( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'coin_deposits'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(coin_deposit_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY' 29 ',batch_deposit_serial_id INT8 NOT NULL' 30 ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' 31 ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' 32 ',amount_with_fee taler_amount NOT NULL' 33 ') %s ;' 34 ,table_name 35 ,'PARTITION BY HASH (coin_pub)' 36 ,partition_suffix 37 ); 38 PERFORM comment_partitioned_table( 39 'Coins which have been deposited with the respective per-coin signatures.' 40 ,table_name 41 ,partition_suffix 42 ); 43 PERFORM comment_partitioned_column( 44 'Link to information about the batch deposit this coin was used for' 45 ,'batch_deposit_serial_id' 46 ,table_name 47 ,partition_suffix 48 ); 49 END 50 $$; 51 52 53 CREATE FUNCTION constrain_table_coin_deposits( 54 IN partition_suffix TEXT 55 ) 56 RETURNS void 57 LANGUAGE plpgsql 58 AS $$ 59 DECLARE 60 table_name TEXT DEFAULT 'coin_deposits'; 61 BEGIN 62 table_name = concat_ws('_', table_name, partition_suffix); 63 EXECUTE FORMAT ( 64 'ALTER TABLE ' || table_name || 65 ' ADD CONSTRAINT ' || table_name || '_coin_deposit_serial_id_pkey' 66 ' PRIMARY KEY (coin_deposit_serial_id) ' 67 ',ADD CONSTRAINT ' || table_name || '_unique_coin_sig' 68 ' UNIQUE (coin_pub, coin_sig)' 69 ); 70 EXECUTE FORMAT ( 71 'CREATE INDEX ' || table_name || '_by_batch ' 72 'ON ' || table_name || ' ' 73 '(batch_deposit_serial_id);' 74 ); 75 END 76 $$; 77 78 79 CREATE FUNCTION foreign_table_coin_deposits() 80 RETURNS void 81 LANGUAGE plpgsql 82 AS $$ 83 DECLARE 84 table_name TEXT DEFAULT 'coin_deposits'; 85 BEGIN 86 EXECUTE FORMAT ( 87 'ALTER TABLE ' || table_name || 88 ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' 89 ' FOREIGN KEY (coin_pub) ' 90 ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE' 91 ',ADD CONSTRAINT ' || table_name || '_foreign_batch_deposits_id' 92 ' FOREIGN KEY (batch_deposit_serial_id) ' 93 ' REFERENCES batch_deposits (batch_deposit_serial_id) ON DELETE CASCADE' 94 ); 95 END 96 $$; 97 98 99 CREATE OR REPLACE FUNCTION coin_deposits_insert_trigger() 100 RETURNS trigger 101 LANGUAGE plpgsql 102 AS $$ 103 BEGIN 104 INSERT INTO exchange.coin_history 105 (coin_pub 106 ,table_name 107 ,serial_id) 108 VALUES 109 (NEW.coin_pub 110 ,'coin_deposits' 111 ,NEW.coin_deposit_serial_id); 112 RETURN NEW; 113 END $$; 114 COMMENT ON FUNCTION coin_deposits_insert_trigger() 115 IS 'Automatically generate coin history entry.'; 116 117 118 CREATE FUNCTION master_table_coin_deposits() 119 RETURNS VOID 120 LANGUAGE plpgsql 121 AS $$ 122 BEGIN 123 CREATE TRIGGER coin_deposits_on_insert 124 AFTER INSERT 125 ON coin_deposits 126 FOR EACH ROW EXECUTE FUNCTION coin_deposits_insert_trigger(); 127 END $$; 128 129 130 INSERT INTO exchange_tables 131 (name 132 ,version 133 ,action 134 ,partitioned 135 ,by_range) 136 VALUES 137 ('coin_deposits' 138 ,'exchange-0002' 139 ,'create' 140 ,TRUE 141 ,FALSE), 142 ('coin_deposits' 143 ,'exchange-0002' 144 ,'constrain' 145 ,TRUE 146 ,FALSE), 147 ('coin_deposits' 148 ,'exchange-0002' 149 ,'foreign' 150 ,TRUE 151 ,FALSE), 152 ('coin_deposits' 153 ,'exchange-0002' 154 ,'master' 155 ,TRUE 156 ,FALSE) 157 ;