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