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