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