taler-api-procedures.sql (8854B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 SET search_path TO taler_api; 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 = 'taler_api'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 CREATE FUNCTION taler_transfer( 42 IN in_amount taler_amount, 43 IN in_exchange_base_url TEXT, 44 IN in_metadata TEXT, 45 IN in_subject TEXT, 46 IN in_credit_payto TEXT, 47 IN in_request_uid BYTEA, 48 IN in_wtid BYTEA, 49 IN in_now INT8, 50 -- Error status 51 OUT out_request_uid_reuse BOOLEAN, 52 OUT out_wtid_reuse BOOLEAN, 53 -- Success return 54 OUT out_transfer_row_id INT8, 55 OUT out_created_at INT8 56 ) 57 LANGUAGE plpgsql AS $$ 58 DECLARE 59 local_tx_id INT8; 60 BEGIN 61 -- Check for idempotence and conflict 62 SELECT (amount != in_amount 63 OR credit_payto != in_credit_payto 64 OR exchange_base_url != in_exchange_base_url 65 OR wtid != in_wtid 66 OR metadata IS DISTINCT FROM in_metadata) 67 ,transfer_id, created_at 68 INTO out_request_uid_reuse, out_transfer_row_id, out_created_at 69 FROM transfer 70 JOIN tx_out USING (tx_out_id) 71 WHERE request_uid = in_request_uid; 72 IF FOUND THEN 73 RETURN; 74 END IF; 75 -- Check for wtid reuse 76 out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid); 77 IF out_wtid_reuse THEN 78 RETURN; 79 END IF; 80 out_created_at=in_now; 81 -- Register exchange 82 INSERT INTO tx_out ( 83 amount, 84 subject, 85 credit_payto, 86 created_at 87 ) VALUES ( 88 in_amount, 89 in_subject, 90 in_credit_payto, 91 in_now 92 ) RETURNING tx_out_id INTO local_tx_id; 93 -- Lock for transfer id commit ordering 94 LOCK TABLE transfer IN SHARE ROW EXCLUSIVE MODE; 95 INSERT INTO transfer ( 96 tx_out_id, 97 exchange_base_url, 98 metadata, 99 request_uid, 100 wtid, 101 status, 102 status_msg 103 ) VALUES ( 104 local_tx_id, 105 in_exchange_base_url, 106 in_metadata, 107 in_request_uid, 108 in_wtid, 109 'success', 110 NULL 111 ) RETURNING transfer_id INTO out_transfer_row_id; 112 -- Notify new transaction 113 PERFORM pg_notify('outgoing_tx', out_transfer_row_id || ''); 114 END $$; 115 COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it'; 116 117 CREATE FUNCTION add_incoming( 118 IN in_amount taler_amount, 119 IN in_subject TEXT, 120 IN in_debit_payto TEXT, 121 IN in_type incoming_type, 122 IN in_account_pub BYTEA, 123 IN in_now INT8, 124 -- Error status 125 OUT out_reserve_pub_reuse BOOLEAN, 126 OUT out_mapping_reuse BOOLEAN, 127 OUT out_unknown_mapping BOOLEAN, 128 -- Success return 129 OUT out_tx_row_id INT8, 130 OUT out_created_at INT8 131 ) 132 LANGUAGE plpgsql AS $$ 133 DECLARE 134 local_pending BOOLEAN; 135 local_authorization_pub BYTEA; 136 local_authorization_sig BYTEA; 137 local_taler_in_id INT8; 138 BEGIN 139 local_pending=false; 140 141 -- Resolve mapping logic 142 IF in_type = 'map' THEN 143 SELECT type, account_pub, authorization_pub, authorization_sig, 144 tx_in_id IS NOT NULL AND NOT recurrent, 145 tx_in_id IS NOT NULL AND recurrent 146 INTO in_type, in_account_pub, local_authorization_pub, local_authorization_sig, out_mapping_reuse, local_pending 147 FROM prepared_in 148 WHERE authorization_pub = in_account_pub; 149 out_unknown_mapping = NOT FOUND; 150 IF out_unknown_mapping OR out_mapping_reuse THEN 151 RETURN; 152 END IF; 153 END IF; 154 155 -- Check conflict 156 out_reserve_pub_reuse=NOT local_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve'); 157 IF out_reserve_pub_reuse THEN 158 RETURN; 159 END IF; 160 161 -- Register incoming transaction 162 out_created_at=in_now; 163 -- Lock for revenue id commit ordering 164 LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE; 165 INSERT INTO tx_in ( 166 amount, 167 debit_payto, 168 created_at, 169 subject 170 ) VALUES ( 171 in_amount, 172 in_debit_payto, 173 in_now, 174 in_subject 175 ) RETURNING tx_in_id INTO out_tx_row_id; 176 IF local_pending THEN 177 -- Delay talerable registration until mapping again 178 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 179 VALUES (out_tx_row_id, local_authorization_pub); 180 ELSE 181 UPDATE prepared_in 182 SET tx_in_id = out_tx_row_id 183 WHERE ( 184 tx_in_id IS NULL AND account_pub = in_account_pub AND in_type=type AND type='reserve' 185 ) OR authorization_pub = local_authorization_pub; 186 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 187 INSERT INTO taler_in ( 188 tx_in_id, 189 type, 190 account_pub, 191 authorization_pub, 192 authorization_sig 193 ) VALUES ( 194 out_tx_row_id, 195 in_type, 196 in_account_pub, 197 local_authorization_pub, 198 local_authorization_sig 199 ) RETURNING taler_in_id INTO local_taler_in_id; 200 -- Notify new incoming transaction 201 PERFORM pg_notify('incoming_tx', local_taler_in_id::text); 202 END IF; 203 204 END $$; 205 COMMENT ON FUNCTION add_incoming IS 'Create an incoming taler transaction and register it'; 206 207 CREATE FUNCTION register_prepared_transfers ( 208 IN in_type incoming_type, 209 IN in_account_pub BYTEA, 210 IN in_authorization_pub BYTEA, 211 IN in_authorization_sig BYTEA, 212 IN in_recurrent BOOLEAN, 213 IN in_timestamp INT8, 214 -- Error status 215 OUT out_reserve_pub_reuse BOOLEAN 216 ) 217 LANGUAGE plpgsql AS $$ 218 DECLARE 219 talerable_tx INT8; 220 local_taler_in_id INT8; 221 idempotent BOOLEAN; 222 BEGIN 223 224 -- Check idempotency 225 SELECT type = in_type 226 AND account_pub = in_account_pub 227 AND recurrent = in_recurrent 228 INTO idempotent 229 FROM prepared_in 230 WHERE authorization_pub = in_authorization_pub; 231 232 -- Check idempotency and delay garbage collection 233 IF FOUND AND idempotent THEN 234 UPDATE prepared_in 235 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 236 WHERE authorization_pub=in_authorization_pub; 237 RETURN; 238 END IF; 239 240 -- Check reserve pub reuse 241 out_reserve_pub_reuse=in_type = 'reserve' AND ( 242 EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve') 243 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 244 ); 245 IF out_reserve_pub_reuse THEN 246 RETURN; 247 END IF; 248 249 IF in_recurrent THEN 250 -- Finalize one pending right now. 251 DELETE FROM pending_recurrent_in 252 WHERE tx_in_id = ( 253 SELECT tx_in_id 254 FROM pending_recurrent_in 255 JOIN tx_in USING (tx_in_id) 256 WHERE authorization_pub = in_authorization_pub 257 ORDER BY created_at ASC 258 LIMIT 1 259 ) 260 RETURNING tx_in_id INTO talerable_tx; 261 IF talerable_tx IS NOT NULL THEN 262 -- Lock for incoming history id commit ordering 263 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 264 INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig) 265 VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig) 266 RETURNING taler_in_id INTO local_taler_in_id; 267 PERFORM pg_notify('incoming_tx', local_taler_in_id::text); 268 END IF; 269 ELSE 270 -- Bounce all pending 271 WITH bounced AS ( 272 DELETE FROM pending_recurrent_in 273 WHERE authorization_pub = in_authorization_pub 274 RETURNING tx_in_id 275 ) 276 INSERT INTO bounced (tx_in_id) 277 SELECT tx_in_id FROM bounced; 278 END IF; 279 280 -- Upsert registration 281 INSERT INTO prepared_in ( 282 type, 283 account_pub, 284 authorization_pub, 285 authorization_sig, 286 recurrent, 287 registered_at, 288 tx_in_id 289 ) VALUES ( 290 in_type, 291 in_account_pub, 292 in_authorization_pub, 293 in_authorization_sig, 294 in_recurrent, 295 in_timestamp, 296 talerable_tx 297 ) ON CONFLICT (authorization_pub) 298 DO UPDATE SET 299 type = EXCLUDED.type, 300 account_pub = EXCLUDED.account_pub, 301 recurrent = EXCLUDED.recurrent, 302 registered_at = EXCLUDED.registered_at, 303 tx_in_id = EXCLUDED.tx_in_id, 304 authorization_sig = EXCLUDED.authorization_sig; 305 END $$; 306 307 CREATE FUNCTION delete_prepared_transfers ( 308 IN in_authorization_pub BYTEA, 309 IN in_timestamp INT8, 310 OUT out_found BOOLEAN 311 ) 312 LANGUAGE plpgsql AS $$ 313 BEGIN 314 315 -- Bounce all pending 316 WITH bounced AS ( 317 DELETE FROM pending_recurrent_in 318 WHERE authorization_pub = in_authorization_pub 319 RETURNING tx_in_id 320 ) 321 INSERT INTO bounced (tx_in_id) 322 SELECT tx_in_id FROM bounced; 323 324 -- Delete registration 325 DELETE FROM prepared_in 326 WHERE authorization_pub = in_authorization_pub; 327 out_found = FOUND; 328 329 END $$;