0002-wad_in_entries.sql (4694B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 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_wad_in_entries( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'wad_in_entries'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I ' 28 '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',wad_in_serial_id INT8' 30 ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' 31 ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' 32 ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' 33 ',purse_expiration INT8 NOT NULL' 34 ',merge_timestamp INT8 NOT NULL' 35 ',amount_with_fee taler_amount NOT NULL' 36 ',wad_fee taler_amount NOT NULL' 37 ',deposit_fees taler_amount NOT NULL' 38 ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' 39 ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' 40 ') %s ;' 41 ,table_name 42 ,'PARTITION BY HASH (purse_pub)' 43 ,partition_suffix 44 ); 45 PERFORM comment_partitioned_table( 46 'list of purses aggregated in a wad according to the sending exchange' 47 ,table_name 48 ,partition_suffix 49 ); 50 PERFORM comment_partitioned_column( 51 'wad for which the given purse was included in the aggregation' 52 ,'wad_in_serial_id' 53 ,table_name 54 ,partition_suffix 55 ); 56 PERFORM comment_partitioned_column( 57 'target account of the purse (must be at the local exchange)' 58 ,'reserve_pub' 59 ,table_name 60 ,partition_suffix 61 ); 62 PERFORM comment_partitioned_column( 63 'public key of the purse that was merged' 64 ,'purse_pub' 65 ,table_name 66 ,partition_suffix 67 ); 68 PERFORM comment_partitioned_column( 69 'hash of the contract terms of the purse' 70 ,'h_contract' 71 ,table_name 72 ,partition_suffix 73 ); 74 PERFORM comment_partitioned_column( 75 'Time when the purse was set to expire' 76 ,'purse_expiration' 77 ,table_name 78 ,partition_suffix 79 ); 80 PERFORM comment_partitioned_column( 81 'Time when the merge was approved' 82 ,'merge_timestamp' 83 ,table_name 84 ,partition_suffix 85 ); 86 PERFORM comment_partitioned_column( 87 'Total amount in the purse' 88 ,'amount_with_fee' 89 ,table_name 90 ,partition_suffix 91 ); 92 PERFORM comment_partitioned_column( 93 'Total wad fees paid by the purse' 94 ,'wad_fee' 95 ,table_name 96 ,partition_suffix 97 ); 98 PERFORM comment_partitioned_column( 99 'Total deposit fees paid when depositing coins into the purse' 100 ,'deposit_fees' 101 ,table_name 102 ,partition_suffix 103 ); 104 PERFORM comment_partitioned_column( 105 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE' 106 ,'reserve_sig' 107 ,table_name 108 ,partition_suffix 109 ); 110 PERFORM comment_partitioned_column( 111 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE' 112 ,'purse_sig' 113 ,table_name 114 ,partition_suffix 115 ); 116 END $$; 117 118 119 CREATE FUNCTION constrain_table_wad_in_entries( 120 IN partition_suffix TEXT 121 ) 122 RETURNS VOID 123 LANGUAGE plpgsql 124 AS $$ 125 DECLARE 126 table_name TEXT DEFAULT 'wad_in_entries'; 127 BEGIN 128 table_name = concat_ws('_', table_name, partition_suffix); 129 130 EXECUTE FORMAT ( 131 'ALTER TABLE ' || table_name || 132 ' ADD CONSTRAINT ' || table_name || '_wad_in_entry_serial_id_key' 133 ' UNIQUE (wad_in_entry_serial_id) ' 134 ); 135 END $$; 136 137 138 CREATE FUNCTION foreign_table_wad_in_entries() 139 RETURNS VOID 140 LANGUAGE plpgsql 141 AS $$ 142 DECLARE 143 table_name TEXT DEFAULT 'wad_in_entries'; 144 BEGIN 145 EXECUTE FORMAT ( 146 'ALTER TABLE ' || table_name || 147 ' ADD CONSTRAINT ' || table_name || '_foreign_wad_in' 148 ' FOREIGN KEY(wad_in_serial_id)' 149 ' REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE' 150 ); 151 END $$; 152 153 154 INSERT INTO exchange_tables 155 (name 156 ,version 157 ,action 158 ,partitioned 159 ,by_range) 160 VALUES 161 ('wad_in_entries' 162 ,'exchange-0002' 163 ,'create' 164 ,TRUE 165 ,FALSE), 166 ('wad_in_entries' 167 ,'exchange-0002' 168 ,'constrain' 169 ,TRUE 170 ,FALSE), 171 ('wad_in_entries' 172 ,'exchange-0002' 173 ,'foreign' 174 ,TRUE 175 ,FALSE);