0002-wad_out_entries.sql (4549B)
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 18 CREATE FUNCTION create_table_wad_out_entries( 19 IN partition_suffix TEXT DEFAULT NULL 20 ) 21 RETURNS VOID 22 LANGUAGE plpgsql 23 AS $$ 24 DECLARE 25 table_name TEXT DEFAULT 'wad_out_entries'; 26 BEGIN 27 PERFORM create_partitioned_table( 28 'CREATE TABLE %I ' 29 '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 30 ',wad_out_serial_id INT8' 31 ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' 32 ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' 33 ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' 34 ',purse_expiration INT8 NOT NULL' 35 ',merge_timestamp INT8 NOT NULL' 36 ',amount_with_fee taler_amount NOT NULL' 37 ',wad_fee taler_amount NOT NULL' 38 ',deposit_fees taler_amount NOT NULL' 39 ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' 40 ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' 41 ') %s ;' 42 ,table_name 43 ,'PARTITION BY HASH (purse_pub)' 44 ,partition_suffix 45 ); 46 PERFORM comment_partitioned_table( 47 'Purses combined into a wad' 48 ,table_name 49 ,partition_suffix 50 ); 51 PERFORM comment_partitioned_column( 52 'Wad the purse was part of' 53 ,'wad_out_serial_id' 54 ,table_name 55 ,partition_suffix 56 ); 57 PERFORM comment_partitioned_column( 58 'Target reserve for the purse' 59 ,'reserve_pub' 60 ,table_name 61 ,partition_suffix 62 ); 63 PERFORM comment_partitioned_column( 64 'Public key of the purse' 65 ,'purse_pub' 66 ,table_name 67 ,partition_suffix 68 ); 69 PERFORM comment_partitioned_column( 70 'Hash of the contract associated with the purse' 71 ,'h_contract' 72 ,table_name 73 ,partition_suffix 74 ); 75 PERFORM comment_partitioned_column( 76 'Time when the purse expires' 77 ,'purse_expiration' 78 ,table_name 79 ,partition_suffix 80 ); 81 PERFORM comment_partitioned_column( 82 'Time when the merge was approved' 83 ,'merge_timestamp' 84 ,table_name 85 ,partition_suffix 86 ); 87 PERFORM comment_partitioned_column( 88 'Total amount in the purse' 89 ,'amount_with_fee' 90 ,table_name 91 ,partition_suffix 92 ); 93 PERFORM comment_partitioned_column( 94 'Wad fee charged to the purse' 95 ,'wad_fee' 96 ,table_name 97 ,partition_suffix 98 ); 99 PERFORM comment_partitioned_column( 100 'Total deposit fees charged to the purse' 101 ,'deposit_fees' 102 ,table_name 103 ,partition_suffix 104 ); 105 PERFORM comment_partitioned_column( 106 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE' 107 ,'reserve_sig' 108 ,table_name 109 ,partition_suffix 110 ); 111 PERFORM comment_partitioned_column( 112 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE' 113 ,'purse_sig' 114 ,table_name 115 ,partition_suffix 116 ); 117 END 118 $$; 119 120 121 CREATE FUNCTION constrain_table_wad_out_entries( 122 IN partition_suffix TEXT 123 ) 124 RETURNS VOID 125 LANGUAGE plpgsql 126 AS $$ 127 DECLARE 128 table_name TEXT DEFAULT 'wad_out_entries'; 129 BEGIN 130 table_name = concat_ws('_', table_name, partition_suffix); 131 132 EXECUTE FORMAT ( 133 'ALTER TABLE ' || table_name || 134 ' ADD CONSTRAINT ' || table_name || '_wad_out_entry_serial_id_key' 135 ' UNIQUE (wad_out_entry_serial_id) ' 136 ); 137 END 138 $$; 139 140 141 CREATE FUNCTION foreign_table_wad_out_entries() 142 RETURNS VOID 143 LANGUAGE plpgsql 144 AS $$ 145 DECLARE 146 table_name TEXT DEFAULT 'wad_out_entries'; 147 BEGIN 148 EXECUTE FORMAT ( 149 'ALTER TABLE ' || table_name || 150 ' ADD CONSTRAINT ' || table_name || '_foreign_wad_out' 151 ' FOREIGN KEY(wad_out_serial_id)' 152 ' REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE' 153 ); 154 END 155 $$; 156 157 158 INSERT INTO exchange_tables 159 (name 160 ,version 161 ,action 162 ,partitioned 163 ,by_range) 164 VALUES 165 ('wad_out_entries' 166 ,'exchange-0002' 167 ,'create' 168 ,TRUE 169 ,FALSE), 170 ('wad_out_entries' 171 ,'exchange-0002' 172 ,'constrain' 173 ,TRUE 174 ,FALSE), 175 ('wad_out_entries' 176 ,'exchange-0002' 177 ,'foreign' 178 ,TRUE 179 ,FALSE);