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