magnet-bank-procedures.sql (9865B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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 magnet_bank; 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 = 'magnet_bank'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 CREATE FUNCTION register_tx_in( 42 IN in_code INT8, 43 IN in_amount taler_amount, 44 IN in_subject TEXT, 45 IN in_debit_account TEXT, 46 IN in_debit_name TEXT, 47 IN in_valued_at INT8, 48 IN in_type incoming_type, 49 IN in_metadata BYTEA, 50 IN in_now INT8, 51 -- Error status 52 OUT out_reserve_pub_reuse BOOLEAN, 53 -- Success return 54 OUT out_tx_row_id INT8, 55 OUT out_valued_at INT8, 56 OUT out_new BOOLEAN 57 ) 58 LANGUAGE plpgsql AS $$ 59 BEGIN 60 -- Check for idempotence 61 SELECT tx_in_id, valued_at 62 INTO out_tx_row_id, out_valued_at 63 FROM tx_in 64 WHERE (in_code IS NOT NULL AND magnet_code = in_code) -- Magnet transaction 65 OR (in_code IS NULL AND amount = in_amount AND debit_account = in_debit_account AND subject = in_subject); -- Admin transaction 66 out_new = NOT found; 67 IF NOT out_new THEN 68 RETURN; 69 END IF; 70 71 -- Check conflict 72 SELECT in_type = 'reserve'::incoming_type AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve') 73 INTO out_reserve_pub_reuse; 74 IF out_reserve_pub_reuse THEN 75 RETURN; 76 END IF; 77 78 -- Insert new incoming transaction 79 out_valued_at = in_valued_at; 80 INSERT INTO tx_in ( 81 magnet_code, 82 amount, 83 subject, 84 debit_account, 85 debit_name, 86 valued_at, 87 registered_at 88 ) VALUES ( 89 in_code, 90 in_amount, 91 in_subject, 92 in_debit_account, 93 in_debit_name, 94 in_valued_at, 95 in_now 96 ) 97 RETURNING tx_in_id INTO out_tx_row_id; 98 -- Notify new incoming transaction registration 99 PERFORM pg_notify('tx_in', out_tx_row_id || ''); 100 IF in_type IS NOT NULL THEN 101 -- Insert new incoming talerable transaction 102 INSERT INTO taler_in ( 103 tx_in_id, 104 type, 105 metadata 106 ) VALUES ( 107 out_tx_row_id, 108 in_type, 109 in_metadata 110 ); 111 -- Notify new incoming talerable transaction registration 112 PERFORM pg_notify('taler_in', out_tx_row_id || ''); 113 END IF; 114 END $$; 115 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 116 117 CREATE FUNCTION register_tx_out( 118 IN in_code INT8, 119 IN in_amount taler_amount, 120 IN in_subject TEXT, 121 IN in_credit_account TEXT, 122 IN in_credit_name TEXT, 123 IN in_valued_at INT8, 124 IN in_wtid BYTEA, 125 IN in_origin_exchange_url TEXT, 126 IN in_bounced INT8, 127 IN in_now INT8, 128 -- Success return 129 OUT out_tx_row_id INT8, 130 OUT out_result register_result 131 ) 132 LANGUAGE plpgsql AS $$ 133 BEGIN 134 -- Check for idempotence 135 SELECT tx_out_id INTO out_tx_row_id 136 FROM tx_out WHERE magnet_code = in_code; 137 138 IF FOUND THEN 139 out_result = 'idempotent'; 140 RETURN; 141 END IF; 142 143 -- Insert new outgoing transaction 144 INSERT INTO tx_out ( 145 magnet_code, 146 amount, 147 subject, 148 credit_account, 149 credit_name, 150 valued_at, 151 registered_at 152 ) VALUES ( 153 in_code, 154 in_amount, 155 in_subject, 156 in_credit_account, 157 in_credit_name, 158 in_valued_at, 159 in_now 160 ) 161 RETURNING tx_out_id INTO out_tx_row_id; 162 -- Notify new outgoing transaction registration 163 PERFORM pg_notify('tx_out', out_tx_row_id || ''); 164 165 -- Update initiated status 166 UPDATE initiated 167 SET 168 tx_out_id = out_tx_row_id, 169 status = 'success', 170 status_msg = NULL 171 WHERE magnet_code = in_code; 172 IF FOUND THEN 173 out_result = 'known'; 174 ELSE 175 out_result = 'recovered'; 176 END IF; 177 178 IF in_wtid IS NOT NULL THEN 179 -- Insert new outgoing talerable transaction 180 INSERT INTO taler_out ( 181 tx_out_id, 182 wtid, 183 exchange_base_url 184 ) VALUES ( 185 out_tx_row_id, 186 in_wtid, 187 in_origin_exchange_url 188 ) ON CONFLICT (wtid) DO NOTHING; 189 IF FOUND THEN 190 -- Notify new outgoing talerable transaction registration 191 PERFORM pg_notify('taler_out', out_tx_row_id || ''); 192 END IF; 193 ELSIF in_bounced IS NOT NULL THEN 194 UPDATE initiated 195 SET 196 tx_out_id = out_tx_row_id, 197 status = 'success', 198 status_msg = NULL 199 FROM bounced JOIN tx_in USING (tx_in_id) 200 WHERE initiated.initiated_id = bounced.initiated_id AND tx_in.magnet_code = in_bounced; 201 END IF; 202 END $$; 203 COMMENT ON FUNCTION register_tx_out IS 'Register an outgoing transaction idempotently'; 204 205 CREATE FUNCTION register_tx_out_failure( 206 IN in_code INT8, 207 IN in_bounced INT8, 208 IN in_now INT8, 209 -- Success return 210 OUT out_initiated_id INT8, 211 OUT out_new BOOLEAN 212 ) 213 LANGUAGE plpgsql AS $$ 214 DECLARE 215 current_status transfer_status; 216 BEGIN 217 -- Found existing initiated transaction or bounced transaction 218 SELECT status, initiated_id 219 INTO current_status, out_initiated_id 220 FROM initiated 221 LEFT JOIN bounced USING (initiated_id) 222 LEFT JOIN tx_in USING (tx_in_id) 223 WHERE initiated.magnet_code = in_code OR tx_in.magnet_code = in_bounced; 224 225 -- Update status if new 226 out_new = FOUND AND current_status != 'permanent_failure'; 227 IF out_new THEN 228 UPDATE initiated 229 SET 230 status = 'permanent_failure', 231 status_msg = NULL 232 WHERE initiated_id = out_initiated_id; 233 END IF; 234 END $$; 235 COMMENT ON FUNCTION register_tx_out_failure IS 'Register an outgoing transaction failure idempotently'; 236 237 CREATE FUNCTION taler_transfer( 238 IN in_request_uid BYTEA, 239 IN in_wtid BYTEA, 240 IN in_subject TEXT, 241 IN in_amount taler_amount, 242 IN in_exchange_base_url TEXT, 243 IN in_credit_account TEXT, 244 IN in_credit_name TEXT, 245 IN in_now INT8, 246 -- Error return 247 OUT out_request_uid_reuse BOOLEAN, 248 OUT out_wtid_reuse BOOLEAN, 249 -- Success return 250 OUT out_initiated_row_id INT8, 251 OUT out_initiated_at INT8 252 ) 253 LANGUAGE plpgsql AS $$ 254 BEGIN 255 -- Check for idempotence and conflict 256 SELECT (amount != in_amount 257 OR credit_account != in_credit_account 258 OR exchange_base_url != in_exchange_base_url 259 OR wtid != in_wtid) 260 ,initiated_id, initiated_at 261 INTO out_request_uid_reuse, out_initiated_row_id, out_initiated_at 262 FROM transfer JOIN initiated USING (initiated_id) 263 WHERE request_uid = in_request_uid; 264 IF FOUND THEN 265 RETURN; 266 END IF; 267 -- Check for wtid reuse 268 out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid); 269 IF out_wtid_reuse THEN 270 RETURN; 271 END IF; 272 -- Insert an initiated outgoing transaction 273 out_initiated_at = in_now; 274 INSERT INTO initiated ( 275 amount, 276 subject, 277 credit_account, 278 credit_name, 279 initiated_at 280 ) VALUES ( 281 in_amount, 282 in_subject, 283 in_credit_account, 284 in_credit_name, 285 in_now 286 ) RETURNING initiated_id 287 INTO out_initiated_row_id; 288 -- Insert a transfer operation 289 INSERT INTO transfer ( 290 initiated_id, 291 request_uid, 292 wtid, 293 exchange_base_url 294 ) VALUES ( 295 out_initiated_row_id, 296 in_request_uid, 297 in_wtid, 298 in_exchange_base_url 299 ); 300 PERFORM pg_notify('transfer', out_initiated_row_id || ''); 301 END $$; 302 303 CREATE FUNCTION initiated_status_update( 304 IN in_initiated_id INT8, 305 IN in_status transfer_status, 306 IN in_status_msg TEXT 307 ) 308 RETURNS void 309 LANGUAGE plpgsql AS $$ 310 DECLARE 311 current_status transfer_status; 312 BEGIN 313 -- Check current status 314 SELECT status INTO current_status FROM initiated 315 WHERE initiated_id = in_initiated_id; 316 IF FOUND THEN 317 -- Update unsettled transaction status 318 IF current_status = 'success' AND in_status = 'permanent_failure' THEN 319 UPDATE initiated 320 SET status = 'late_failure', status_msg = in_status_msg 321 WHERE initiated_id = in_initiated_id; 322 ELSIF current_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 323 UPDATE initiated 324 SET status = in_status, status_msg = in_status_msg 325 WHERE initiated_id = in_initiated_id; 326 END IF; 327 END IF; 328 END $$; 329 330 CREATE FUNCTION register_bounce_tx_in( 331 IN in_code INT8, 332 IN in_amount taler_amount, 333 IN in_subject TEXT, 334 IN in_debit_account TEXT, 335 IN in_debit_name TEXT, 336 IN in_valued_at INT8, 337 IN in_bounce_amount taler_amount, 338 IN in_reason TEXT, 339 IN in_now INT8, 340 -- Success return 341 OUT out_tx_row_id INT8, 342 OUT out_tx_new BOOLEAN, 343 OUT out_bounce_row_id INT8, 344 OUT out_bounce_new BOOLEAN 345 ) 346 LANGUAGE plpgsql AS $$ 347 BEGIN 348 -- Register incoming transaction idempotently 349 SELECT register_tx_in.out_tx_row_id, register_tx_in.out_new 350 INTO out_tx_row_id, out_tx_new 351 FROM register_tx_in(in_code, in_amount, in_subject, in_debit_account, in_debit_name, in_valued_at, NULL, NULL, in_now); 352 353 -- Check if already bounce 354 SELECT initiated_id 355 INTO out_bounce_row_id 356 FROM bounced JOIN initiated USING (initiated_id) 357 WHERE tx_in_id = out_tx_row_id; 358 out_bounce_new=NOT FOUND; 359 -- Else initiate the bounce transaction 360 IF out_bounce_new THEN 361 -- Initiate the bounce transaction 362 INSERT INTO initiated ( 363 amount, 364 subject, 365 credit_account, 366 credit_name, 367 initiated_at 368 ) VALUES ( 369 in_amount, 370 'bounce: ' || in_code, 371 in_debit_account, 372 in_debit_name, 373 in_now 374 ) 375 RETURNING initiated_id INTO out_bounce_row_id; 376 -- Register the bounce 377 INSERT INTO bounced ( 378 tx_in_id, 379 initiated_id, 380 reason 381 ) VALUES ( 382 out_tx_row_id, 383 out_bounce_row_id, 384 in_reason 385 ); 386 END IF; 387 END $$; 388 COMMENT ON FUNCTION register_bounce_tx_in IS 'Register an incoming transaction and bounce it idempotently';