libeufin-nexus-procedures.sql (26402B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023, 2024, 2025, 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 BEGIN; 17 SET search_path TO public; 18 CREATE EXTENSION IF NOT EXISTS pgcrypto; 19 20 SET search_path TO libeufin_nexus; 21 22 -- Remove all existing functions 23 DO 24 $do$ 25 DECLARE 26 _sql text; 27 BEGIN 28 SELECT INTO _sql 29 string_agg(format('DROP %s %s CASCADE;' 30 , CASE prokind 31 WHEN 'f' THEN 'FUNCTION' 32 WHEN 'p' THEN 'PROCEDURE' 33 END 34 , oid::regprocedure) 35 , E'\n') 36 FROM pg_proc 37 WHERE pronamespace = 'libeufin_nexus'::regnamespace; 38 39 IF _sql IS NOT NULL THEN 40 EXECUTE _sql; 41 END IF; 42 END 43 $do$; 44 45 CREATE FUNCTION ebics_id_gen() 46 RETURNS TEXT 47 LANGUAGE sql AS $$ 48 -- use gen_random_uuid to get some randomness 49 -- remove all - characters as they are not random 50 -- capitalise the UUID as some bank may still be case sensitive 51 -- end with 34 random chars which is valid for EBICS (max 35 chars) 52 SELECT upper(replace(gen_random_uuid()::text, '-', '')); 53 $$; 54 55 56 CREATE FUNCTION amount_normalize( 57 IN amount taler_amount 58 ,OUT normalized taler_amount 59 ) 60 LANGUAGE plpgsql IMMUTABLE AS $$ 61 BEGIN 62 normalized.val = amount.val + amount.frac / 100000000; 63 IF (normalized.val > 1::INT8<<52) THEN 64 RAISE EXCEPTION 'amount value overflowed'; 65 END IF; 66 normalized.frac = amount.frac % 100000000; 67 68 END $$; 69 COMMENT ON FUNCTION amount_normalize 70 IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.' 71 'It raises an exception when the resulting .val is larger than 2^52'; 72 73 CREATE FUNCTION amount_add( 74 IN l taler_amount 75 ,IN r taler_amount 76 ,OUT sum taler_amount 77 ) 78 LANGUAGE plpgsql IMMUTABLE AS $$ 79 BEGIN 80 sum = (l.val + r.val, l.frac + r.frac); 81 SELECT normalized.val, normalized.frac INTO sum.val, sum.frac FROM amount_normalize(sum) as normalized; 82 END $$; 83 COMMENT ON FUNCTION amount_add 84 IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52'; 85 86 CREATE FUNCTION register_outgoing( 87 IN in_amount taler_amount 88 ,IN in_debit_fee taler_amount 89 ,IN in_subject TEXT 90 ,IN in_execution_time INT8 91 ,IN in_credit_payto TEXT 92 ,IN in_end_to_end_id TEXT 93 ,IN in_msg_id TEXT 94 ,IN in_acct_svcr_ref TEXT 95 ,IN in_wtid BYTEA 96 ,IN in_exchange_url TEXT 97 ,IN in_metadata TEXT 98 ,OUT out_tx_id INT8 99 ,OUT out_found BOOLEAN 100 ,OUT out_initiated BOOLEAN 101 ) 102 LANGUAGE plpgsql AS $$ 103 DECLARE 104 init_id INT8; 105 local_amount taler_amount; 106 local_subject TEXT; 107 local_credit_payto TEXT; 108 local_wtid BYTEA; 109 local_exchange_base_url TEXT; 110 local_metadata TEXT; 111 local_end_to_end_id TEXT; 112 BEGIN 113 -- Check if already registered 114 SELECT outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 115 wtid, exchange_base_url, metadata 116 INTO out_tx_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 117 local_wtid, local_exchange_base_url, local_metadata 118 FROM outgoing_transactions LEFT JOIN talerable_outgoing_transactions USING (outgoing_transaction_id) 119 WHERE end_to_end_id = in_end_to_end_id OR acct_svcr_ref = in_acct_svcr_ref; 120 out_found=FOUND; 121 IF out_found THEN 122 -- Check metadata 123 -- TODO take subject if missing and more detailed credit payto 124 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 125 RAISE NOTICE 'outgoing tx %: stored subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 126 END IF; 127 IF in_credit_payto IS NOT NULL AND local_credit_payto != in_credit_payto THEN 128 RAISE NOTICE 'outgoing tx %: stored subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 129 END IF; 130 IF local_amount IS DISTINCT FROM in_amount THEN 131 RAISE NOTICE 'outgoing tx %: stored amount is % got %', in_end_to_end_id, local_amount, in_amount; 132 END IF; 133 IF local_wtid IS DISTINCT FROM in_wtid THEN 134 RAISE NOTICE 'outgoing tx %: stored wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 135 END IF; 136 IF local_exchange_base_url IS DISTINCT FROM in_exchange_url THEN 137 RAISE NOTICE 'outgoing tx %: stored exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 138 END IF; 139 IF local_metadata IS DISTINCT FROM in_metadata THEN 140 RAISE NOTICE 'outgoing tx %: stored metadata is % got %', in_end_to_end_id, local_metadata, in_metadata; 141 END IF; 142 END IF; 143 144 -- Check if initiated 145 SELECT initiated_outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 146 wtid, exchange_base_url, metadata 147 INTO init_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 148 local_wtid, local_exchange_base_url, local_metadata 149 FROM initiated_outgoing_transactions LEFT JOIN transfer_operations USING (initiated_outgoing_transaction_id) 150 WHERE end_to_end_id = in_end_to_end_id; 151 out_initiated=FOUND; 152 IF out_initiated AND NOT out_found THEN 153 -- Check metadata 154 -- TODO take subject if missing and more detailed credit payto 155 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 156 RAISE NOTICE 'outgoing tx %: initiated subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 157 END IF; 158 IF local_credit_payto IS DISTINCT FROM in_credit_payto THEN 159 RAISE NOTICE 'outgoing tx %: initiated subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 160 END IF; 161 IF local_amount IS DISTINCT FROM in_amount THEN 162 RAISE NOTICE 'outgoing tx %: initiated amount is % got %', in_end_to_end_id, local_amount, in_amount; 163 END IF; 164 IF in_wtid IS NOT NULL AND local_wtid != in_wtid THEN 165 RAISE NOTICE 'outgoing tx %: initiated wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 166 END IF; 167 IF in_exchange_url IS NOT NULL AND local_exchange_base_url != in_exchange_url THEN 168 RAISE NOTICE 'outgoing tx %: initiated exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 169 END IF; 170 IF in_metadata IS NOT NULL AND local_metadata != in_metadata THEN 171 RAISE NOTICE 'outgoing tx %: initiated metadata is % got %', in_end_to_end_id, local_metadata, in_metadata; 172 END IF; 173 END IF; 174 175 IF NOT out_found THEN 176 -- Store the transaction in the database 177 INSERT INTO outgoing_transactions ( 178 amount 179 ,debit_fee 180 ,subject 181 ,execution_time 182 ,credit_payto 183 ,end_to_end_id 184 ,acct_svcr_ref 185 ) VALUES ( 186 in_amount 187 ,in_debit_fee 188 ,in_subject 189 ,in_execution_time 190 ,in_credit_payto 191 ,in_end_to_end_id 192 ,in_acct_svcr_ref 193 ) 194 RETURNING outgoing_transaction_id 195 INTO out_tx_id; 196 197 -- Register as talerable if contains wtid 198 IF in_wtid IS NOT NULL THEN 199 SELECT end_to_end_id INTO local_end_to_end_id 200 FROM talerable_outgoing_transactions 201 JOIN outgoing_transactions USING (outgoing_transaction_id) 202 WHERE wtid=in_wtid; 203 IF FOUND THEN 204 IF local_end_to_end_id != in_end_to_end_id THEN 205 RAISE NOTICE 'wtid reuse: tx % and tx % have the same wtid %', in_end_to_end_id, local_end_to_end_id, in_wtid; 206 END IF; 207 ELSE 208 INSERT INTO talerable_outgoing_transactions( 209 outgoing_transaction_id, 210 wtid, 211 exchange_base_url, 212 metadata 213 ) VALUES ( 214 out_tx_id, 215 in_wtid, 216 in_exchange_url, 217 in_metadata 218 ); 219 PERFORM pg_notify('nexus_outgoing_tx', out_tx_id::text); 220 END IF; 221 END IF; 222 223 IF out_initiated THEN 224 -- Reconciles the related initiated transaction 225 UPDATE initiated_outgoing_transactions 226 SET 227 outgoing_transaction_id = out_tx_id 228 ,status = 'success' 229 ,status_msg = null 230 WHERE initiated_outgoing_transaction_id = init_id 231 AND status != 'late_failure'; 232 233 -- Reconciles the related initiated batch 234 UPDATE initiated_outgoing_batches 235 SET status = 'success', status_msg = null 236 WHERE message_id = in_msg_id AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 237 END IF; 238 END IF; 239 END $$; 240 COMMENT ON FUNCTION register_outgoing 241 IS 'Register an outgoing transaction and optionally reconciles the related initiated transaction with it'; 242 243 CREATE FUNCTION register_incoming( 244 IN in_amount taler_amount 245 ,IN in_credit_fee taler_amount 246 ,IN in_subject TEXT 247 ,IN in_execution_time INT8 248 ,IN in_debit_payto TEXT 249 ,IN in_uetr UUID 250 ,IN in_tx_id TEXT 251 ,IN in_acct_svcr_ref TEXT 252 ,IN in_type taler_incoming_type 253 ,IN in_metadata BYTEA 254 ,IN in_qr_reference_number TEXT 255 -- Error status 256 ,OUT out_reserve_pub_reuse BOOLEAN 257 ,OUT out_mapping_reuse BOOLEAN 258 ,OUT out_unknown_mapping BOOLEAN 259 -- Success return 260 ,OUT out_found BOOLEAN 261 ,OUT out_completed BOOLEAN 262 ,OUT out_talerable BOOLEAN 263 ,OUT out_pending BOOLEAN 264 ,OUT out_tx_id INT8 265 ,OUT out_bounce_id TEXT 266 ) 267 LANGUAGE plpgsql AS $$ 268 DECLARE 269 local_ref TEXT; 270 local_amount taler_amount; 271 local_subject TEXT; 272 local_debit_payto TEXT; 273 local_authorization_pub BYTEA; 274 local_authorization_sig BYTEA; 275 BEGIN 276 IF in_credit_fee = (0, 0)::taler_amount THEN 277 in_credit_fee = NULL; 278 END IF; 279 out_pending=FALSE; 280 281 -- Check if already registered 282 SELECT incoming_transaction_id, tx.subject, debit_payto, (tx.amount).val, (tx.amount).frac, metadata IS NOT NULL, end_to_end_id 283 INTO out_tx_id, local_subject, local_debit_payto, local_amount.val, local_amount.frac, out_talerable, out_bounce_id 284 FROM incoming_transactions AS tx 285 LEFT JOIN talerable_incoming_transactions USING (incoming_transaction_id) 286 LEFT JOIN bounced_transactions USING (incoming_transaction_id) 287 LEFT JOIN initiated_outgoing_transactions USING (initiated_outgoing_transaction_id) 288 WHERE uetr = in_uetr OR tx_id = in_tx_id OR acct_svcr_ref = in_acct_svcr_ref; 289 out_found=FOUND; 290 291 IF NOT out_found OR NOT out_talerable THEN 292 -- Resolve mapping logic 293 IF in_type = 'map' OR in_qr_reference_number IS NOT NULL THEN 294 SELECT type, account_pub, authorization_pub, authorization_sig, 295 incoming_transaction_id IS NOT NULL AND NOT recurrent, 296 incoming_transaction_id IS NOT NULL AND recurrent 297 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 298 FROM prepared_transfers 299 WHERE authorization_pub = in_metadata OR reference_number = in_qr_reference_number; 300 out_unknown_mapping = NOT FOUND; 301 IF out_unknown_mapping OR out_mapping_reuse THEN 302 RETURN; 303 END IF; 304 END IF; 305 306 -- Check reserve pub reuse 307 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_metadata AND type = 'reserve'); 308 IF out_reserve_pub_reuse THEN 309 RETURN; 310 END IF; 311 END IF; 312 313 IF out_found THEN 314 local_ref=COALESCE(in_uetr::text, in_tx_id, in_acct_svcr_ref); 315 -- Check metadata 316 IF in_subject != local_subject THEN 317 RAISE NOTICE 'incoming tx %: stored subject is ''%'' got ''%''', local_ref, local_subject, in_subject; 318 END IF; 319 IF in_debit_payto != local_debit_payto THEN 320 RAISE NOTICE 'incoming tx %: stored subject debit payto is % got %', local_ref, local_debit_payto, in_debit_payto; 321 END IF; 322 IF local_amount != in_amount THEN 323 RAISE NOTICE 'incoming tx %: stored amount is % got %', local_ref, local_amount, in_amount; 324 END IF; 325 UPDATE incoming_transactions 326 SET subject=COALESCE(subject, in_subject), 327 debit_payto=COALESCE(debit_payto, in_debit_payto), 328 uetr=COALESCE(uetr, in_uetr), 329 tx_id=COALESCE(tx_id, in_tx_id), 330 acct_svcr_ref=COALESCE(acct_svcr_ref, in_acct_svcr_ref) 331 WHERE incoming_transaction_id = out_tx_id; 332 out_completed=local_debit_payto IS NULL AND in_debit_payto IS NOT NULL; 333 IF out_completed THEN 334 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 335 END IF; 336 ELSE 337 -- Store the transaction in the database 338 INSERT INTO incoming_transactions ( 339 amount 340 ,credit_fee 341 ,subject 342 ,execution_time 343 ,debit_payto 344 ,uetr 345 ,tx_id 346 ,acct_svcr_ref 347 ) VALUES ( 348 in_amount 349 ,in_credit_fee 350 ,in_subject 351 ,in_execution_time 352 ,in_debit_payto 353 ,in_uetr 354 ,in_tx_id 355 ,in_acct_svcr_ref 356 ) RETURNING incoming_transaction_id INTO out_tx_id; 357 IF in_subject IS NOT NULL AND in_debit_payto IS NOT NULL THEN 358 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 359 END IF; 360 out_talerable=FALSE; 361 END IF; 362 363 -- Register as talerable if not already registered as such and not already bounced 364 IF in_type IS NOT NULL AND NOT out_talerable AND out_bounce_id IS NULL THEN 365 If out_pending THEN 366 -- Delay talerable registration until mapping again 367 INSERT INTO pending_recurrent_incoming_transactions (incoming_transaction_id, authorization_pub) 368 VALUES (out_tx_id, local_authorization_pub); 369 ELSE 370 UPDATE prepared_transfers 371 SET incoming_transaction_id = out_tx_id 372 WHERE (incoming_transaction_id IS NULL AND account_pub = in_metadata) OR authorization_pub = local_authorization_pub; 373 -- We cannot use ON CONFLICT here because conversion use a trigger before insertion that isn't idempotent 374 INSERT INTO talerable_incoming_transactions ( 375 incoming_transaction_id 376 ,type 377 ,metadata 378 ,authorization_pub 379 ,authorization_sig 380 ) VALUES ( 381 out_tx_id 382 ,in_type 383 ,in_metadata 384 ,local_authorization_pub 385 ,local_authorization_sig 386 ); 387 PERFORM pg_notify('nexus_incoming_tx', out_tx_id::text); 388 out_talerable=TRUE; 389 END IF; 390 END IF; 391 END $$; 392 393 CREATE FUNCTION register_and_bounce_incoming( 394 IN in_amount taler_amount 395 ,IN in_credit_fee taler_amount 396 ,IN in_subject TEXT 397 ,IN in_execution_time INT8 398 ,IN in_debit_payto TEXT 399 ,IN in_uetr UUID 400 ,IN in_tx_id TEXT 401 ,IN in_acct_svcr_ref TEXT 402 ,IN in_bounce_amount taler_amount 403 ,IN in_now_date INT8 404 ,IN in_bounce_id TEXT 405 ,IN in_cause TEXT 406 -- Error status 407 ,OUT out_talerable BOOLEAN 408 -- Success return 409 ,OUT out_found BOOLEAN 410 ,OUT out_completed BOOLEAN 411 ,OUT out_tx_id INT8 412 ,OUT out_bounce_id TEXT 413 ) 414 LANGUAGE plpgsql AS $$ 415 DECLARE 416 init_id INT8; 417 bounce_amount taler_amount; 418 BEGIN 419 -- Register incoming transaction 420 SELECT reg.out_found, reg.out_completed, reg.out_tx_id, reg.out_talerable 421 INTO out_found, out_completed, out_tx_id, out_talerable 422 FROM register_incoming(in_amount, in_credit_fee, in_subject, in_execution_time, in_debit_payto, in_uetr, in_tx_id, in_acct_svcr_ref, NULL, NULL, NULL) as reg; 423 -- Cannot bounce a transaction registered as talerable 424 IF out_talerable THEN 425 RETURN; 426 END IF; 427 -- Bounce incoming transaction 428 SELECT bounce.out_bounce_id INTO out_bounce_id FROM bounce_incoming(out_tx_id, in_bounce_amount, in_bounce_id, in_now_date, in_cause) AS bounce; 429 END $$; 430 431 CREATE FUNCTION bounce_incoming( 432 IN in_tx_id INT8 433 ,IN in_bounce_amount taler_amount 434 ,IN in_bounce_id TEXT 435 ,IN in_now_date INT8 436 ,IN in_cause TEXT 437 ,OUT out_bounce_id TEXT 438 ) 439 LANGUAGE plpgsql AS $$ 440 DECLARE 441 local_bank_id TEXT; 442 payto_uri TEXT; 443 init_id INT8; 444 BEGIN 445 -- Check if already bounced 446 SELECT end_to_end_id INTO out_bounce_id 447 FROM libeufin_nexus.initiated_outgoing_transactions 448 JOIN libeufin_nexus.bounced_transactions USING (initiated_outgoing_transaction_id) 449 WHERE incoming_transaction_id = in_tx_id; 450 451 -- Else initiate the bounce transaction 452 IF NOT FOUND THEN 453 out_bounce_id = in_bounce_id; 454 -- Get incoming transaction bank ID and creditor 455 SELECT COALESCE(uetr::text, tx_id, acct_svcr_ref), debit_payto 456 INTO local_bank_id, payto_uri 457 FROM libeufin_nexus.incoming_transactions 458 WHERE incoming_transaction_id = in_tx_id; 459 -- Initiate the bounce transaction 460 INSERT INTO libeufin_nexus.initiated_outgoing_transactions ( 461 amount 462 ,subject 463 ,credit_payto 464 ,initiation_time 465 ,end_to_end_id 466 ) VALUES ( 467 in_bounce_amount 468 ,'bounce ' || local_bank_id || ': ' || in_cause 469 ,payto_uri 470 ,in_now_date 471 ,in_bounce_id 472 ) 473 RETURNING initiated_outgoing_transaction_id INTO init_id; 474 -- Register the bounce 475 INSERT INTO libeufin_nexus.bounced_transactions (incoming_transaction_id, initiated_outgoing_transaction_id) 476 VALUES (in_tx_id, init_id); 477 END IF; 478 479 -- Delete from pending if any 480 DELETE FROM libeufin_nexus.pending_recurrent_incoming_transactions WHERE incoming_transaction_id = in_tx_id; 481 END$$; 482 483 CREATE FUNCTION taler_transfer( 484 IN in_request_uid BYTEA, 485 IN in_wtid BYTEA, 486 IN in_subject TEXT, 487 IN in_amount taler_amount, 488 IN in_exchange_base_url TEXT, 489 IN in_metadata TEXT, 490 IN in_credit_account_payto TEXT, 491 IN in_end_to_end_id TEXT, 492 IN in_timestamp INT8, 493 -- Error status 494 OUT out_request_uid_reuse BOOLEAN, 495 OUT out_wtid_reuse BOOLEAN, 496 -- Success return 497 OUT out_tx_row_id INT8, 498 OUT out_timestamp INT8 499 ) 500 LANGUAGE plpgsql AS $$ 501 BEGIN 502 -- Check for idempotence and conflict 503 SELECT (amount != in_amount 504 OR credit_payto != in_credit_account_payto 505 OR exchange_base_url != in_exchange_base_url 506 OR exchange_base_url != in_exchange_base_url 507 OR wtid != in_wtid) 508 ,transfer_operations.initiated_outgoing_transaction_id, initiation_time 509 INTO out_request_uid_reuse, out_tx_row_id, out_timestamp 510 FROM transfer_operations 511 JOIN initiated_outgoing_transactions 512 ON transfer_operations.initiated_outgoing_transaction_id=initiated_outgoing_transactions.initiated_outgoing_transaction_id 513 WHERE transfer_operations.request_uid = in_request_uid; 514 IF FOUND THEN 515 RETURN; 516 END IF; 517 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid); 518 IF out_wtid_reuse THEN 519 RETURN; 520 END IF; 521 out_timestamp=in_timestamp; 522 -- Initiate bank transfer 523 INSERT INTO initiated_outgoing_transactions ( 524 amount 525 ,subject 526 ,credit_payto 527 ,initiation_time 528 ,end_to_end_id 529 ) VALUES ( 530 in_amount 531 ,in_subject 532 ,in_credit_account_payto 533 ,in_timestamp 534 ,in_end_to_end_id 535 ) RETURNING initiated_outgoing_transaction_id INTO out_tx_row_id; 536 -- Register outgoing transaction 537 INSERT INTO transfer_operations( 538 initiated_outgoing_transaction_id 539 ,request_uid 540 ,wtid 541 ,exchange_base_url 542 ,metadata 543 ) VALUES ( 544 out_tx_row_id 545 ,in_request_uid 546 ,in_wtid 547 ,in_exchange_base_url 548 ,in_metadata 549 ); 550 out_timestamp = in_timestamp; 551 PERFORM pg_notify('nexus_outgoing_tx', out_tx_row_id::text); 552 END $$; 553 554 CREATE FUNCTION batch_outgoing_transactions( 555 IN in_timestamp INT8, 556 IN batch_ebics_id TEXT, 557 IN require_ack BOOLEAN 558 ) 559 RETURNS void 560 LANGUAGE plpgsql AS $$ 561 DECLARE 562 pending BOOLEAN; 563 batch_id INT8; 564 local_sum taler_amount DEFAULT (0, 0)::taler_amount; 565 tx record; 566 BEGIN 567 IF require_ack THEN 568 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL AND NOT awaiting_ack); 569 ELSE 570 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL); 571 END IF; 572 -- Create a new batch only if some transactions are not batched 573 IF (pending) THEN 574 -- Create batch 575 INSERT INTO initiated_outgoing_batches (creation_date, message_id) 576 VALUES (in_timestamp, batch_ebics_id) 577 RETURNING initiated_outgoing_batch_id INTO batch_id; 578 -- Link batched payment while computing the sum of amounts 579 FOR tx IN UPDATE initiated_outgoing_transactions 580 SET initiated_outgoing_batch_id=batch_id 581 WHERE initiated_outgoing_batch_id IS NULL 582 RETURNING amount 583 LOOP 584 SELECT sum.val, sum.frac 585 INTO local_sum.val, local_sum.frac 586 FROM amount_add(local_sum, tx.amount) AS sum; 587 END LOOP; 588 -- Update the batch with the sum of amounts 589 UPDATE initiated_outgoing_batches SET sum=local_sum WHERE initiated_outgoing_batch_id=batch_id; 590 END IF; 591 END $$; 592 593 CREATE FUNCTION batch_status_update( 594 IN in_message_id text, 595 IN in_status submission_state, 596 IN in_status_msg text, 597 OUT out_ok BOOLEAN 598 ) 599 LANGUAGE plpgsql AS $$ 600 DECLARE 601 local_batch_id INT8; 602 BEGIN 603 -- Check if there is a batch for this message id 604 SELECT initiated_outgoing_batch_id INTO local_batch_id 605 FROM initiated_outgoing_batches 606 WHERE message_id = in_message_id; 607 out_ok=FOUND; 608 IF FOUND THEN 609 -- Update unsettled batch status 610 UPDATE initiated_outgoing_batches 611 SET status = in_status, status_msg = in_status_msg 612 WHERE initiated_outgoing_batch_id = local_batch_id 613 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 614 615 -- When a batch succeed it doesn't mean that individual transaction also succeed 616 IF in_status = 'success' THEN 617 in_status = 'pending'; 618 END IF; 619 620 -- Update unsettled batch's transaction status 621 UPDATE initiated_outgoing_transactions 622 SET status = in_status, status_msg = in_status_msg 623 WHERE initiated_outgoing_batch_id = local_batch_id 624 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 625 END IF; 626 END $$; 627 628 CREATE FUNCTION tx_status_update( 629 IN in_end_to_end_id text, 630 IN in_message_id text, 631 IN in_status submission_state, 632 IN in_status_msg text, 633 OUT out_ok BOOLEAN 634 ) 635 LANGUAGE plpgsql AS $$ 636 DECLARE 637 local_status submission_state; 638 local_tx_id INT8; 639 BEGIN 640 -- Check current tx status 641 SELECT initiated_outgoing_transaction_id, status INTO local_tx_id, local_status 642 FROM initiated_outgoing_transactions 643 WHERE end_to_end_id = in_end_to_end_id; 644 out_ok=FOUND; 645 IF FOUND THEN 646 -- Update unsettled transaction status 647 IF in_status = 'permanent_failure' OR local_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 648 IF in_status = 'permanent_failure' AND local_status = 'success' THEN 649 in_status = 'late_failure'; 650 END IF; 651 UPDATE initiated_outgoing_transactions 652 SET status = in_status, status_msg = in_status_msg 653 WHERE initiated_outgoing_transaction_id = local_tx_id; 654 END IF; 655 656 -- Update unsettled batch status 657 UPDATE initiated_outgoing_batches 658 SET status = 'success', status_msg = NULL 659 WHERE message_id = in_message_id 660 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 661 END IF; 662 END $$; 663 664 CREATE FUNCTION register_prepared_transfers ( 665 IN in_type taler_incoming_type, 666 IN in_account_pub BYTEA, 667 IN in_authorization_pub BYTEA, 668 IN in_authorization_sig BYTEA, 669 IN in_recurrent BOOLEAN, 670 IN in_reference_number TEXT, 671 IN in_timestamp INT8, 672 -- Error status 673 OUT out_subject_reuse BOOLEAN, 674 OUT out_reserve_pub_reuse BOOLEAN 675 ) 676 LANGUAGE plpgsql AS $$ 677 DECLARE 678 talerable_tx INT8; 679 idempotent BOOLEAN; 680 BEGIN 681 682 -- Check idempotency 683 SELECT type = in_type 684 AND account_pub = in_account_pub 685 AND recurrent = in_recurrent 686 AND reference_number = in_reference_number 687 INTO idempotent 688 FROM prepared_transfers 689 WHERE authorization_pub = in_authorization_pub; 690 691 -- Check idempotency and delay garbage collection 692 IF FOUND AND idempotent THEN 693 UPDATE prepared_transfers 694 SET registered_at=in_timestamp 695 WHERE authorization_pub=in_authorization_pub; 696 RETURN; 697 END IF; 698 699 -- Check reserve pub reuse and reference_number clash 700 out_reserve_pub_reuse=in_type = 'reserve' AND ( 701 EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_account_pub AND type = 'reserve') 702 OR EXISTS(SELECT FROM prepared_transfers WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 703 ); 704 out_subject_reuse=EXISTS(SELECT FROM prepared_transfers WHERE authorization_pub != in_authorization_pub AND reference_number = in_reference_number); 705 IF out_reserve_pub_reuse OR out_subject_reuse THEN 706 RETURN; 707 END IF; 708 709 IF in_recurrent THEN 710 -- Finalize one pending right now 711 WITH moved_tx AS ( 712 DELETE FROM pending_recurrent_incoming_transactions 713 WHERE incoming_transaction_id = ( 714 SELECT incoming_transaction_id 715 FROM pending_recurrent_incoming_transactions 716 JOIN incoming_transactions USING (incoming_transaction_id) 717 WHERE authorization_pub = in_authorization_pub 718 ORDER BY execution_time ASC 719 LIMIT 1 720 ) 721 RETURNING incoming_transaction_id 722 ) 723 INSERT INTO talerable_incoming_transactions (incoming_transaction_id, type, metadata, authorization_pub, authorization_sig) 724 SELECT moved_tx.incoming_transaction_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 725 FROM moved_tx 726 RETURNING incoming_transaction_id INTO talerable_tx; 727 IF talerable_tx IS NOT NULL THEN 728 PERFORM pg_notify('nexus_incoming_tx', talerable_tx::text); 729 END IF; 730 ELSE 731 -- Bounce all pending 732 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 733 FROM incoming_transactions 734 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 735 WHERE authorization_pub = in_authorization_pub; 736 END IF; 737 738 -- Upsert registration 739 INSERT INTO prepared_transfers ( 740 type, 741 account_pub, 742 authorization_pub, 743 authorization_sig, 744 recurrent, 745 reference_number, 746 registered_at, 747 incoming_transaction_id 748 ) VALUES ( 749 in_type, 750 in_account_pub, 751 in_authorization_pub, 752 in_authorization_sig, 753 in_recurrent, 754 in_reference_number, 755 in_timestamp, 756 talerable_tx 757 ) ON CONFLICT (authorization_pub) 758 DO UPDATE SET 759 type = EXCLUDED.type, 760 account_pub = EXCLUDED.account_pub, 761 recurrent = EXCLUDED.recurrent, 762 reference_number = EXCLUDED.reference_number, 763 registered_at = EXCLUDED.registered_at, 764 incoming_transaction_id = EXCLUDED.incoming_transaction_id, 765 authorization_sig = EXCLUDED.authorization_sig; 766 END $$; 767 768 CREATE FUNCTION delete_prepared_transfers ( 769 IN in_authorization_pub BYTEA, 770 IN in_timestamp INT8, 771 OUT out_found BOOLEAN 772 ) 773 LANGUAGE plpgsql AS $$ 774 BEGIN 775 776 -- Bounce all pending 777 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 778 FROM incoming_transactions 779 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 780 WHERE authorization_pub = in_authorization_pub; 781 782 -- Delete registration 783 DELETE FROM prepared_transfers 784 WHERE authorization_pub = in_authorization_pub; 785 out_found = FOUND; 786 787 END $$;