libeufin-nexus-procedures.sql (26348B)
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 IF local_authorization_pub IS NOT NULL THEN 371 UPDATE prepared_transfers 372 SET incoming_transaction_id = out_tx_id 373 WHERE authorization_pub = local_authorization_pub; 374 END IF; 375 -- We cannot use ON CONFLICT here because conversion use a trigger before insertion that isn't idempotent 376 INSERT INTO talerable_incoming_transactions ( 377 incoming_transaction_id 378 ,type 379 ,metadata 380 ,authorization_pub 381 ,authorization_sig 382 ) VALUES ( 383 out_tx_id 384 ,in_type 385 ,in_metadata 386 ,local_authorization_pub 387 ,local_authorization_sig 388 ); 389 PERFORM pg_notify('nexus_incoming_tx', out_tx_id::text); 390 out_talerable=TRUE; 391 END IF; 392 END IF; 393 END $$; 394 395 CREATE FUNCTION register_and_bounce_incoming( 396 IN in_amount taler_amount 397 ,IN in_credit_fee taler_amount 398 ,IN in_subject TEXT 399 ,IN in_execution_time INT8 400 ,IN in_debit_payto TEXT 401 ,IN in_uetr UUID 402 ,IN in_tx_id TEXT 403 ,IN in_acct_svcr_ref TEXT 404 ,IN in_bounce_amount taler_amount 405 ,IN in_now_date INT8 406 ,IN in_bounce_id TEXT 407 ,IN in_cause TEXT 408 -- Error status 409 ,OUT out_talerable BOOLEAN 410 -- Success return 411 ,OUT out_found BOOLEAN 412 ,OUT out_completed BOOLEAN 413 ,OUT out_tx_id INT8 414 ,OUT out_bounce_id TEXT 415 ) 416 LANGUAGE plpgsql AS $$ 417 DECLARE 418 init_id INT8; 419 bounce_amount taler_amount; 420 BEGIN 421 -- Register incoming transaction 422 SELECT reg.out_found, reg.out_completed, reg.out_tx_id, reg.out_talerable 423 INTO out_found, out_completed, out_tx_id, out_talerable 424 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; 425 -- Cannot bounce a transaction registered as talerable 426 IF out_talerable THEN 427 RETURN; 428 END IF; 429 -- Bounce incoming transaction 430 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; 431 END $$; 432 433 CREATE FUNCTION bounce_incoming( 434 IN in_tx_id INT8 435 ,IN in_bounce_amount taler_amount 436 ,IN in_bounce_id TEXT 437 ,IN in_now_date INT8 438 ,IN in_cause TEXT 439 ,OUT out_bounce_id TEXT 440 ) 441 LANGUAGE plpgsql AS $$ 442 DECLARE 443 local_bank_id TEXT; 444 payto_uri TEXT; 445 init_id INT8; 446 BEGIN 447 -- Check if already bounced 448 SELECT end_to_end_id INTO out_bounce_id 449 FROM libeufin_nexus.initiated_outgoing_transactions 450 JOIN libeufin_nexus.bounced_transactions USING (initiated_outgoing_transaction_id) 451 WHERE incoming_transaction_id = in_tx_id; 452 453 -- Else initiate the bounce transaction 454 IF NOT FOUND THEN 455 out_bounce_id = in_bounce_id; 456 -- Get incoming transaction bank ID and creditor 457 SELECT COALESCE(uetr::text, tx_id, acct_svcr_ref), debit_payto 458 INTO local_bank_id, payto_uri 459 FROM libeufin_nexus.incoming_transactions 460 WHERE incoming_transaction_id = in_tx_id; 461 -- Initiate the bounce transaction 462 INSERT INTO libeufin_nexus.initiated_outgoing_transactions ( 463 amount 464 ,subject 465 ,credit_payto 466 ,initiation_time 467 ,end_to_end_id 468 ) VALUES ( 469 in_bounce_amount 470 ,'bounce ' || local_bank_id || ': ' || in_cause 471 ,payto_uri 472 ,in_now_date 473 ,in_bounce_id 474 ) 475 RETURNING initiated_outgoing_transaction_id INTO init_id; 476 -- Register the bounce 477 INSERT INTO libeufin_nexus.bounced_transactions (incoming_transaction_id, initiated_outgoing_transaction_id) 478 VALUES (in_tx_id, init_id); 479 END IF; 480 481 -- Delete from pending if any 482 DELETE FROM libeufin_nexus.pending_recurrent_incoming_transactions WHERE incoming_transaction_id = in_tx_id; 483 END$$; 484 485 CREATE FUNCTION taler_transfer( 486 IN in_request_uid BYTEA, 487 IN in_wtid BYTEA, 488 IN in_subject TEXT, 489 IN in_amount taler_amount, 490 IN in_exchange_base_url TEXT, 491 IN in_metadata TEXT, 492 IN in_credit_account_payto TEXT, 493 IN in_end_to_end_id TEXT, 494 IN in_timestamp INT8, 495 -- Error status 496 OUT out_request_uid_reuse BOOLEAN, 497 OUT out_wtid_reuse BOOLEAN, 498 -- Success return 499 OUT out_tx_row_id INT8, 500 OUT out_timestamp INT8 501 ) 502 LANGUAGE plpgsql AS $$ 503 BEGIN 504 -- Check for idempotence and conflict 505 SELECT (amount != in_amount 506 OR credit_payto != in_credit_account_payto 507 OR exchange_base_url != in_exchange_base_url 508 OR exchange_base_url != in_exchange_base_url 509 OR wtid != in_wtid) 510 ,transfer_operations.initiated_outgoing_transaction_id, initiation_time 511 INTO out_request_uid_reuse, out_tx_row_id, out_timestamp 512 FROM transfer_operations 513 JOIN initiated_outgoing_transactions 514 ON transfer_operations.initiated_outgoing_transaction_id=initiated_outgoing_transactions.initiated_outgoing_transaction_id 515 WHERE transfer_operations.request_uid = in_request_uid; 516 IF FOUND THEN 517 RETURN; 518 END IF; 519 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid); 520 IF out_wtid_reuse THEN 521 RETURN; 522 END IF; 523 out_timestamp=in_timestamp; 524 -- Initiate bank transfer 525 INSERT INTO initiated_outgoing_transactions ( 526 amount 527 ,subject 528 ,credit_payto 529 ,initiation_time 530 ,end_to_end_id 531 ) VALUES ( 532 in_amount 533 ,in_subject 534 ,in_credit_account_payto 535 ,in_timestamp 536 ,in_end_to_end_id 537 ) RETURNING initiated_outgoing_transaction_id INTO out_tx_row_id; 538 -- Register outgoing transaction 539 INSERT INTO transfer_operations( 540 initiated_outgoing_transaction_id 541 ,request_uid 542 ,wtid 543 ,exchange_base_url 544 ,metadata 545 ) VALUES ( 546 out_tx_row_id 547 ,in_request_uid 548 ,in_wtid 549 ,in_exchange_base_url 550 ,in_metadata 551 ); 552 out_timestamp = in_timestamp; 553 PERFORM pg_notify('nexus_outgoing_tx', out_tx_row_id::text); 554 END $$; 555 556 CREATE FUNCTION batch_outgoing_transactions( 557 IN in_timestamp INT8, 558 IN batch_ebics_id TEXT, 559 IN require_ack BOOLEAN 560 ) 561 RETURNS void 562 LANGUAGE plpgsql AS $$ 563 DECLARE 564 pending BOOLEAN; 565 batch_id INT8; 566 local_sum taler_amount DEFAULT (0, 0)::taler_amount; 567 tx record; 568 BEGIN 569 IF require_ack THEN 570 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL AND NOT awaiting_ack); 571 ELSE 572 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL); 573 END IF; 574 -- Create a new batch only if some transactions are not batched 575 IF (pending) THEN 576 -- Create batch 577 INSERT INTO initiated_outgoing_batches (creation_date, message_id) 578 VALUES (in_timestamp, batch_ebics_id) 579 RETURNING initiated_outgoing_batch_id INTO batch_id; 580 -- Link batched payment while computing the sum of amounts 581 FOR tx IN UPDATE initiated_outgoing_transactions 582 SET initiated_outgoing_batch_id=batch_id 583 WHERE initiated_outgoing_batch_id IS NULL 584 RETURNING amount 585 LOOP 586 SELECT sum.val, sum.frac 587 INTO local_sum.val, local_sum.frac 588 FROM amount_add(local_sum, tx.amount) AS sum; 589 END LOOP; 590 -- Update the batch with the sum of amounts 591 UPDATE initiated_outgoing_batches SET sum=local_sum WHERE initiated_outgoing_batch_id=batch_id; 592 END IF; 593 END $$; 594 595 CREATE FUNCTION batch_status_update( 596 IN in_message_id text, 597 IN in_status submission_state, 598 IN in_status_msg text, 599 OUT out_ok BOOLEAN 600 ) 601 LANGUAGE plpgsql AS $$ 602 DECLARE 603 local_batch_id INT8; 604 BEGIN 605 -- Check if there is a batch for this message id 606 SELECT initiated_outgoing_batch_id INTO local_batch_id 607 FROM initiated_outgoing_batches 608 WHERE message_id = in_message_id; 609 out_ok=FOUND; 610 IF FOUND THEN 611 -- Update unsettled batch status 612 UPDATE initiated_outgoing_batches 613 SET status = in_status, status_msg = in_status_msg 614 WHERE initiated_outgoing_batch_id = local_batch_id 615 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 616 617 -- When a batch succeed it doesn't mean that individual transaction also succeed 618 IF in_status = 'success' THEN 619 in_status = 'pending'; 620 END IF; 621 622 -- Update unsettled batch's transaction status 623 UPDATE initiated_outgoing_transactions 624 SET status = in_status, status_msg = in_status_msg 625 WHERE initiated_outgoing_batch_id = local_batch_id 626 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 627 END IF; 628 END $$; 629 630 CREATE FUNCTION tx_status_update( 631 IN in_end_to_end_id text, 632 IN in_message_id text, 633 IN in_status submission_state, 634 IN in_status_msg text, 635 OUT out_ok BOOLEAN 636 ) 637 LANGUAGE plpgsql AS $$ 638 DECLARE 639 local_status submission_state; 640 local_tx_id INT8; 641 BEGIN 642 -- Check current tx status 643 SELECT initiated_outgoing_transaction_id, status INTO local_tx_id, local_status 644 FROM initiated_outgoing_transactions 645 WHERE end_to_end_id = in_end_to_end_id; 646 out_ok=FOUND; 647 IF FOUND THEN 648 -- Update unsettled transaction status 649 IF in_status = 'permanent_failure' OR local_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 650 IF in_status = 'permanent_failure' AND local_status = 'success' THEN 651 in_status = 'late_failure'; 652 END IF; 653 UPDATE initiated_outgoing_transactions 654 SET status = in_status, status_msg = in_status_msg 655 WHERE initiated_outgoing_transaction_id = local_tx_id; 656 END IF; 657 658 -- Update unsettled batch status 659 UPDATE initiated_outgoing_batches 660 SET status = 'success', status_msg = NULL 661 WHERE message_id = in_message_id 662 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 663 END IF; 664 END $$; 665 666 CREATE FUNCTION register_prepared_transfers ( 667 IN in_type taler_incoming_type, 668 IN in_account_pub BYTEA, 669 IN in_authorization_pub BYTEA, 670 IN in_authorization_sig BYTEA, 671 IN in_recurrent BOOLEAN, 672 IN in_reference_number TEXT, 673 IN in_timestamp INT8, 674 -- Error status 675 OUT out_subject_reuse BOOLEAN, 676 OUT out_reserve_pub_reuse BOOLEAN 677 ) 678 LANGUAGE plpgsql AS $$ 679 DECLARE 680 talerable_tx INT8; 681 idempotent BOOLEAN; 682 BEGIN 683 684 -- Check idempotency 685 SELECT type = in_type 686 AND account_pub = in_account_pub 687 AND recurrent = in_recurrent 688 AND reference_number = in_reference_number 689 INTO idempotent 690 FROM prepared_transfers 691 WHERE authorization_pub = in_authorization_pub; 692 693 -- Check idempotency and delay garbage collection 694 IF FOUND AND idempotent THEN 695 UPDATE prepared_transfers 696 SET registered_at=in_timestamp 697 WHERE authorization_pub=in_authorization_pub; 698 RETURN; 699 END IF; 700 701 -- Check reserve pub reuse and reference_number clash 702 out_reserve_pub_reuse=in_type = 'reserve' AND ( 703 EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_account_pub AND type = 'reserve') 704 OR EXISTS(SELECT FROM prepared_transfers WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 705 ); 706 out_subject_reuse=EXISTS(SELECT FROM prepared_transfers WHERE authorization_pub != in_authorization_pub AND reference_number = in_reference_number); 707 IF out_reserve_pub_reuse OR out_subject_reuse THEN 708 RETURN; 709 END IF; 710 711 IF in_recurrent THEN 712 -- Finalize one pending right now 713 WITH moved_tx AS ( 714 DELETE FROM pending_recurrent_incoming_transactions 715 WHERE incoming_transaction_id = ( 716 SELECT incoming_transaction_id 717 FROM pending_recurrent_incoming_transactions 718 JOIN incoming_transactions USING (incoming_transaction_id) 719 ORDER BY execution_time ASC 720 LIMIT 1 721 ) 722 RETURNING incoming_transaction_id 723 ) 724 INSERT INTO talerable_incoming_transactions (incoming_transaction_id, type, metadata, authorization_pub, authorization_sig) 725 SELECT moved_tx.incoming_transaction_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 726 FROM moved_tx 727 RETURNING incoming_transaction_id INTO talerable_tx; 728 IF talerable_tx IS NOT NULL THEN 729 PERFORM pg_notify('nexus_incoming_tx', talerable_tx::text); 730 END IF; 731 ELSE 732 -- Bounce all pending 733 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 734 FROM incoming_transactions 735 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 736 WHERE authorization_pub = in_authorization_pub; 737 END IF; 738 739 -- Upsert registration 740 INSERT INTO prepared_transfers ( 741 type, 742 account_pub, 743 authorization_pub, 744 authorization_sig, 745 recurrent, 746 reference_number, 747 registered_at, 748 incoming_transaction_id 749 ) VALUES ( 750 in_type, 751 in_account_pub, 752 in_authorization_pub, 753 in_authorization_sig, 754 in_recurrent, 755 in_reference_number, 756 in_timestamp, 757 talerable_tx 758 ) ON CONFLICT (authorization_pub) 759 DO UPDATE SET 760 type = EXCLUDED.type, 761 account_pub = EXCLUDED.account_pub, 762 recurrent = EXCLUDED.recurrent, 763 reference_number = EXCLUDED.reference_number, 764 registered_at = EXCLUDED.registered_at, 765 incoming_transaction_id = EXCLUDED.incoming_transaction_id, 766 authorization_sig = EXCLUDED.authorization_sig; 767 END $$; 768 769 CREATE FUNCTION delete_prepared_transfers ( 770 IN in_authorization_pub BYTEA, 771 IN in_timestamp INT8, 772 OUT out_found BOOLEAN 773 ) 774 LANGUAGE plpgsql AS $$ 775 BEGIN 776 777 -- Bounce all pending 778 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 779 FROM incoming_transactions 780 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 781 WHERE authorization_pub = in_authorization_pub; 782 783 -- Delete registration 784 DELETE FROM prepared_transfers 785 WHERE authorization_pub = in_authorization_pub; 786 out_found = FOUND; 787 788 END $$;