0002-wads_out.sql (3067B)
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_wads_out( 18 IN shard_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 table_name TEXT DEFAULT 'wads_out'; 25 BEGIN 26 PERFORM create_partitioned_table( 27 'CREATE TABLE %I ' 28 '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 29 ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' 30 ',partner_serial_id INT8 NOT NULL' 31 ',amount taler_amount NOT NULL' 32 ',execution_time INT8 NOT NULL' 33 ') %s ;' 34 ,table_name 35 ,'PARTITION BY HASH (wad_id)' 36 ,shard_suffix 37 ); 38 PERFORM comment_partitioned_table( 39 'Wire transfers made to another exchange to transfer purse funds' 40 ,table_name 41 ,shard_suffix 42 ); 43 PERFORM comment_partitioned_column( 44 'Unique identifier of the wad, part of the wire transfer subject' 45 ,'wad_id' 46 ,table_name 47 ,shard_suffix 48 ); 49 PERFORM comment_partitioned_column( 50 'target exchange of the wad' 51 ,'partner_serial_id' 52 ,table_name 53 ,shard_suffix 54 ); 55 PERFORM comment_partitioned_column( 56 'Amount that was wired' 57 ,'amount' 58 ,table_name 59 ,shard_suffix 60 ); 61 PERFORM comment_partitioned_column( 62 'Time when the wire transfer was scheduled' 63 ,'execution_time' 64 ,table_name 65 ,shard_suffix 66 ); 67 END 68 $$; 69 70 71 CREATE FUNCTION constrain_table_wads_out( 72 IN partition_suffix TEXT 73 ) 74 RETURNS VOID 75 LANGUAGE plpgsql 76 AS $$ 77 DECLARE 78 table_name TEXT DEFAULT 'wads_out'; 79 BEGIN 80 table_name = concat_ws('_', table_name, partition_suffix); 81 EXECUTE FORMAT ( 82 'ALTER TABLE ' || table_name || 83 ' ADD CONSTRAINT ' || table_name || '_wad_out_serial_id_key' 84 ' UNIQUE (wad_out_serial_id) ' 85 ); 86 END 87 $$; 88 89 90 CREATE FUNCTION foreign_table_wads_out() 91 RETURNS VOID 92 LANGUAGE plpgsql 93 AS $$ 94 DECLARE 95 table_name TEXT DEFAULT 'wads_out'; 96 BEGIN 97 EXECUTE FORMAT ( 98 'ALTER TABLE ' || table_name || 99 ' ADD CONSTRAINT ' || table_name || '_foreign_partner' 100 ' FOREIGN KEY(partner_serial_id)' 101 ' REFERENCES partners(partner_serial_id) ON DELETE CASCADE' 102 ); 103 END 104 $$; 105 106 107 INSERT INTO exchange_tables 108 (name 109 ,version 110 ,action 111 ,partitioned 112 ,by_range) 113 VALUES 114 ('wads_out' 115 ,'exchange-0002' 116 ,'create' 117 ,TRUE 118 ,FALSE), 119 ('wads_out' 120 ,'exchange-0002' 121 ,'constrain' 122 ,TRUE 123 ,FALSE), 124 ('wads_out' 125 ,'exchange-0002' 126 ,'foreign' 127 ,TRUE 128 ,FALSE);