wise-procedures.sql (7411B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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 SET search_path TO wise; 17 18 -- Remove all existing functions 19 DO 20 $do$ 21 DECLARE 22 _sql text; 23 BEGIN 24 SELECT INTO _sql 25 string_agg(format('DROP %s %s CASCADE;' 26 , CASE prokind 27 WHEN 'f' THEN 'FUNCTION' 28 WHEN 'p' THEN 'PROCEDURE' 29 END 30 , oid::regprocedure) 31 , E'\n') 32 FROM pg_proc 33 WHERE pronamespace = 'wise'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 42 CREATE FUNCTION register_tx_in( 43 IN in_balance_id INT8, 44 IN in_wise_ref TEXT, 45 IN in_amount taler_amount, 46 IN in_subject TEXT, 47 IN in_debit_payto TEXT, 48 IN in_debit_name TEXT, 49 IN in_valued_at INT8, 50 IN in_type incoming_type, 51 IN in_metadata BYTEA, 52 IN in_now INT8, 53 -- Error status 54 OUT out_reserve_pub_reuse BOOLEAN, 55 OUT out_mapping_reuse BOOLEAN, 56 OUT out_unknown_mapping BOOLEAN, 57 -- Success return 58 OUT out_tx_row_id INT8, 59 OUT out_valued_at INT8, 60 OUT out_new BOOLEAN, 61 OUT out_pending BOOLEAN 62 ) 63 LANGUAGE plpgsql AS $$ 64 DECLARE 65 local_authorization_pub BYTEA; 66 local_authorization_sig BYTEA; 67 local_taler_in_id INT8; 68 BEGIN 69 out_pending=false; 70 -- Check for idempotence 71 SELECT tx_in_id, valued_at 72 INTO out_tx_row_id, out_valued_at 73 FROM tx_in 74 WHERE wise_ref = in_wise_ref; 75 out_new = NOT found; 76 IF NOT out_new THEN 77 RETURN; 78 END IF; 79 80 -- Resolve mapping logic 81 IF in_type = 'map' THEN 82 SELECT type, account_pub, authorization_pub, authorization_sig, 83 tx_in_id IS NOT NULL AND NOT recurrent, 84 tx_in_id IS NOT NULL AND recurrent 85 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 86 FROM prepared_in 87 WHERE authorization_pub = in_metadata; 88 out_unknown_mapping = NOT FOUND; 89 IF out_unknown_mapping OR out_mapping_reuse THEN 90 RETURN; 91 END IF; 92 END IF; 93 94 95 -- Check conflict 96 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve'); 97 IF out_reserve_pub_reuse THEN 98 RETURN; 99 END IF; 100 101 -- Insert new incoming transaction 102 out_valued_at = in_valued_at; 103 INSERT INTO tx_in ( 104 balance_id, 105 wise_ref, 106 amount, 107 subject, 108 debit_payto, 109 debit_name, 110 valued_at, 111 registered_at 112 ) VALUES ( 113 in_balance_id, 114 in_wise_ref, 115 in_amount, 116 in_subject, 117 in_debit_payto, 118 in_debit_name, 119 in_valued_at, 120 in_now 121 ) 122 RETURNING tx_in_id INTO out_tx_row_id; 123 -- Notify new incoming transaction registration 124 PERFORM pg_notify('tx_in', in_balance_id || ' ' || out_tx_row_id); 125 126 IF out_pending THEN 127 -- Delay talerable registration until mapping again 128 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 129 VALUES (out_tx_row_id, local_authorization_pub); 130 ELSIF in_type IS NOT NULL AND in_debit_payto IS NOT NULL THEN 131 UPDATE prepared_in 132 SET tx_in_id = out_tx_row_id 133 WHERE ( 134 tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 135 ) OR authorization_pub = local_authorization_pub; 136 -- Insert new incoming talerable transaction 137 INSERT INTO taler_in ( 138 tx_in_id, 139 type, 140 metadata, 141 authorization_pub, 142 authorization_sig 143 ) VALUES ( 144 out_tx_row_id, 145 in_type, 146 in_metadata, 147 local_authorization_pub, 148 local_authorization_sig 149 ) RETURNING taler_in_id INTO local_taler_in_id; 150 -- Notify new incoming talerable transaction registration 151 PERFORM pg_notify('taler_in', in_balance_id || ' ' || local_taler_in_id); 152 END IF; 153 END $$; 154 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 155 156 157 CREATE FUNCTION register_prepared_transfers ( 158 IN in_type incoming_type, 159 IN in_account_pub BYTEA, 160 IN in_authorization_pub BYTEA, 161 IN in_authorization_sig BYTEA, 162 IN in_recurrent BOOLEAN, 163 IN in_timestamp INT8, 164 -- Error status 165 OUT out_reserve_pub_reuse BOOLEAN 166 ) 167 LANGUAGE plpgsql AS $$ 168 DECLARE 169 talerable_tx INT8; 170 local_taler_in_id INT8; 171 local_balance_id INT8; 172 idempotent BOOLEAN; 173 BEGIN 174 175 -- Check idempotency 176 SELECT type = in_type 177 AND account_pub = in_account_pub 178 AND recurrent = in_recurrent 179 INTO idempotent 180 FROM prepared_in 181 WHERE authorization_pub = in_authorization_pub; 182 183 -- Check idempotency and delay garbage collection 184 IF FOUND AND idempotent THEN 185 UPDATE prepared_in 186 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 187 WHERE authorization_pub=in_authorization_pub; 188 RETURN; 189 END IF; 190 191 -- Check reserve pub reuse 192 out_reserve_pub_reuse=in_type = 'reserve' AND ( 193 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 194 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 195 ); 196 IF out_reserve_pub_reuse THEN 197 RETURN; 198 END IF; 199 200 IF in_recurrent THEN 201 -- Finalize one pending right now 202 WITH pending AS ( 203 SELECT tx_in_id 204 FROM pending_recurrent_in 205 JOIN tx_in USING (tx_in_id) 206 WHERE authorization_pub = in_authorization_pub 207 ORDER BY valued_at ASC 208 LIMIT 1 209 ), moved_tx AS ( 210 DELETE FROM pending_recurrent_in 211 USING tx_in, pending 212 WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id 213 AND pending_recurrent_in.tx_in_id = pending.tx_in_id 214 RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id 215 ), inserted AS ( 216 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 217 SELECT tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 218 FROM moved_tx 219 RETURNING tx_in_id, taler_in_id 220 ) 221 SELECT inserted.tx_in_id, inserted.taler_in_id, moved_tx.balance_id 222 INTO talerable_tx, local_taler_in_id, local_balance_id 223 FROM inserted 224 JOIN moved_tx USING (tx_in_id); 225 IF talerable_tx IS NOT NULL THEN 226 PERFORM pg_notify('taler_in', local_balance_id || ' ' || local_taler_in_id); 227 END IF; 228 END IF; 229 230 -- Upsert registration 231 INSERT INTO prepared_in ( 232 type, 233 account_pub, 234 authorization_pub, 235 authorization_sig, 236 recurrent, 237 registered_at, 238 tx_in_id 239 ) VALUES ( 240 in_type, 241 in_account_pub, 242 in_authorization_pub, 243 in_authorization_sig, 244 in_recurrent, 245 in_timestamp, 246 talerable_tx 247 ) ON CONFLICT (authorization_pub) 248 DO UPDATE SET 249 type = EXCLUDED.type, 250 account_pub = EXCLUDED.account_pub, 251 recurrent = EXCLUDED.recurrent, 252 registered_at = EXCLUDED.registered_at, 253 tx_in_id = EXCLUDED.tx_in_id, 254 authorization_sig = EXCLUDED.authorization_sig; 255 END $$; 256 257 CREATE FUNCTION delete_prepared_transfers ( 258 IN in_authorization_pub BYTEA, 259 IN in_timestamp INT8, 260 OUT out_found BOOLEAN 261 ) 262 LANGUAGE plpgsql AS $$ 263 BEGIN 264 265 -- Delete registration 266 DELETE FROM prepared_in 267 WHERE authorization_pub = in_authorization_pub; 268 out_found = FOUND; 269 270 END $$;