0002-aggregation_tracking.sql (3003B)
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_aggregation_tracking( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'aggregation_tracking'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I' 28 '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',batch_deposit_serial_id INT8 PRIMARY KEY' 30 ',wtid_raw BYTEA NOT NULL' 31 ') %s ;' 32 ,table_name 33 ,'PARTITION BY HASH (batch_deposit_serial_id)' 34 ,partition_suffix 35 ); 36 PERFORM comment_partitioned_table( 37 'mapping from wire transfer identifiers (WTID) to deposits (and back)' 38 ,table_name 39 ,partition_suffix 40 ); 41 PERFORM comment_partitioned_column( 42 'identifier of the wire transfer' 43 ,'wtid_raw' 44 ,table_name 45 ,partition_suffix 46 ); 47 END 48 $$; 49 50 51 CREATE FUNCTION constrain_table_aggregation_tracking( 52 IN partition_suffix TEXT 53 ) 54 RETURNS VOID 55 LANGUAGE plpgsql 56 AS $$ 57 DECLARE 58 table_name TEXT DEFAULT 'aggregation_tracking'; 59 BEGIN 60 table_name = concat_ws('_', table_name, partition_suffix); 61 EXECUTE FORMAT ( 62 'CREATE INDEX ' || table_name || '_by_wtid_raw_index ' 63 'ON ' || table_name || ' ' 64 '(wtid_raw);' 65 ); 66 EXECUTE FORMAT ( 67 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' 68 'IS ' || quote_literal('for lookup_transactions') || ';' 69 ); 70 EXECUTE FORMAT ( 71 'ALTER TABLE ' || table_name || 72 ' ADD CONSTRAINT ' || table_name || '_aggregation_serial_id_key' 73 ' UNIQUE (aggregation_serial_id) ' 74 ); 75 END 76 $$; 77 78 79 CREATE FUNCTION foreign_table_aggregation_tracking() 80 RETURNS VOID 81 LANGUAGE plpgsql 82 AS $$ 83 DECLARE 84 table_name TEXT DEFAULT 'aggregation_tracking'; 85 BEGIN 86 EXECUTE FORMAT ( 87 'ALTER TABLE ' || table_name || 88 ' ADD CONSTRAINT ' || table_name || '_foreign_deposit' 89 ' FOREIGN KEY (batch_deposit_serial_id)' 90 ' REFERENCES batch_deposits (batch_deposit_serial_id)' 91 ' ON DELETE CASCADE' 92 ); 93 END 94 $$; 95 96 97 INSERT INTO exchange_tables 98 (name 99 ,version 100 ,action 101 ,partitioned 102 ,by_range) 103 VALUES 104 ('aggregation_tracking' 105 ,'exchange-0002' 106 ,'create' 107 ,TRUE 108 ,FALSE), 109 ('aggregation_tracking' 110 ,'exchange-0002' 111 ,'constrain' 112 ,TRUE 113 ,FALSE), 114 ('aggregation_tracking' 115 ,'exchange-0002' 116 ,'foreign' 117 ,TRUE 118 ,FALSE);