taler-api-procedures.sql (8394B)
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 != 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 INSERT INTO transfer ( 94 tx_out_id, 95 exchange_base_url, 96 metadata, 97 request_uid, 98 wtid, 99 status, 100 status_msg 101 ) VALUES ( 102 local_tx_id, 103 in_exchange_base_url, 104 in_metadata, 105 in_request_uid, 106 in_wtid, 107 'success', 108 NULL 109 ) RETURNING transfer_id INTO out_transfer_row_id; 110 -- Notify new transaction 111 PERFORM pg_notify('outgoing_tx', out_transfer_row_id || ''); 112 END $$; 113 COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it'; 114 115 CREATE FUNCTION add_incoming( 116 IN in_amount taler_amount, 117 IN in_subject TEXT, 118 IN in_debit_payto TEXT, 119 IN in_type incoming_type, 120 IN in_account_pub BYTEA, 121 IN in_now INT8, 122 -- Error status 123 OUT out_reserve_pub_reuse BOOLEAN, 124 OUT out_mapping_reuse BOOLEAN, 125 OUT out_unknown_mapping BOOLEAN, 126 -- Success return 127 OUT out_tx_row_id INT8, 128 OUT out_created_at INT8 129 ) 130 LANGUAGE plpgsql AS $$ 131 DECLARE 132 local_pending BOOLEAN; 133 local_authorization_pub BYTEA; 134 local_authorization_sig BYTEA; 135 BEGIN 136 local_pending=false; 137 138 -- Resolve mapping logic 139 IF in_type = 'map' THEN 140 SELECT type, account_pub, authorization_pub, authorization_sig, 141 tx_in_id IS NOT NULL AND NOT recurrent, 142 tx_in_id IS NOT NULL AND recurrent 143 INTO in_type, in_account_pub, local_authorization_pub, local_authorization_sig, out_mapping_reuse, local_pending 144 FROM prepared_in 145 WHERE authorization_pub = in_account_pub; 146 out_unknown_mapping = NOT FOUND; 147 IF out_unknown_mapping OR out_mapping_reuse THEN 148 RETURN; 149 END IF; 150 END IF; 151 152 -- Check conflict 153 SELECT NOT local_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve') 154 INTO out_reserve_pub_reuse; 155 IF out_reserve_pub_reuse THEN 156 RETURN; 157 END IF; 158 159 -- Register incoming transaction 160 out_created_at=in_now; 161 INSERT INTO tx_in ( 162 amount, 163 debit_payto, 164 created_at, 165 subject 166 ) VALUES ( 167 in_amount, 168 in_debit_payto, 169 in_now, 170 in_subject 171 ) RETURNING tx_in_id INTO out_tx_row_id; 172 IF local_pending THEN 173 -- Delay talerable registration until mapping again 174 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 175 VALUES (out_tx_row_id, local_authorization_pub); 176 ELSE 177 UPDATE prepared_in 178 SET tx_in_id = out_tx_row_id 179 WHERE ( 180 tx_in_id IS NULL AND account_pub = in_account_pub AND type='reserve' 181 ) OR authorization_pub = local_authorization_pub; 182 INSERT INTO taler_in ( 183 tx_in_id, 184 type, 185 account_pub, 186 authorization_pub, 187 authorization_sig 188 ) VALUES ( 189 out_tx_row_id, 190 in_type, 191 in_account_pub, 192 local_authorization_pub, 193 local_authorization_sig 194 ); 195 -- Notify new incoming transaction 196 PERFORM pg_notify('incoming_tx', out_tx_row_id || ''); 197 END IF; 198 199 END $$; 200 COMMENT ON FUNCTION add_incoming IS 'Create an incoming taler transaction and register it'; 201 202 CREATE FUNCTION register_prepared_transfers ( 203 IN in_type incoming_type, 204 IN in_account_pub BYTEA, 205 IN in_authorization_pub BYTEA, 206 IN in_authorization_sig BYTEA, 207 IN in_recurrent BOOLEAN, 208 IN in_timestamp INT8, 209 -- Error status 210 OUT out_reserve_pub_reuse BOOLEAN 211 ) 212 LANGUAGE plpgsql AS $$ 213 DECLARE 214 talerable_tx INT8; 215 idempotent BOOLEAN; 216 BEGIN 217 218 -- Check idempotency 219 SELECT type = in_type 220 AND account_pub = in_account_pub 221 AND recurrent = in_recurrent 222 INTO idempotent 223 FROM prepared_in 224 WHERE authorization_pub = in_authorization_pub; 225 226 -- Check idempotency and delay garbage collection 227 IF FOUND AND idempotent THEN 228 UPDATE prepared_in 229 SET registered_at=in_timestamp 230 WHERE authorization_pub=in_authorization_pub; 231 RETURN; 232 END IF; 233 234 -- Check reserve pub reuse 235 out_reserve_pub_reuse=in_type = 'reserve' AND ( 236 EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve') 237 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 238 ); 239 IF out_reserve_pub_reuse THEN 240 RETURN; 241 END IF; 242 243 IF in_recurrent THEN 244 -- Finalize one pending right now 245 WITH moved_tx AS ( 246 DELETE FROM pending_recurrent_in 247 WHERE tx_in_id = ( 248 SELECT tx_in_id 249 FROM pending_recurrent_in 250 JOIN tx_in USING (tx_in_id) 251 WHERE authorization_pub = in_authorization_pub 252 ORDER BY created_at ASC 253 LIMIT 1 254 ) 255 RETURNING tx_in_id 256 ) 257 INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig) 258 SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 259 FROM moved_tx 260 RETURNING tx_in_id INTO talerable_tx; 261 IF talerable_tx IS NOT NULL THEN 262 PERFORM pg_notify('incoming_tx', talerable_tx::text); 263 END IF; 264 ELSE 265 -- Bounce all pending 266 WITH bounced AS ( 267 DELETE FROM pending_recurrent_in 268 WHERE authorization_pub = in_authorization_pub 269 RETURNING tx_in_id 270 ) 271 INSERT INTO bounced (tx_in_id) 272 SELECT tx_in_id FROM bounced; 273 END IF; 274 275 -- Upsert registration 276 INSERT INTO prepared_in ( 277 type, 278 account_pub, 279 authorization_pub, 280 authorization_sig, 281 recurrent, 282 registered_at, 283 tx_in_id 284 ) VALUES ( 285 in_type, 286 in_account_pub, 287 in_authorization_pub, 288 in_authorization_sig, 289 in_recurrent, 290 in_timestamp, 291 talerable_tx 292 ) ON CONFLICT (authorization_pub) 293 DO UPDATE SET 294 type = EXCLUDED.type, 295 account_pub = EXCLUDED.account_pub, 296 recurrent = EXCLUDED.recurrent, 297 registered_at = EXCLUDED.registered_at, 298 tx_in_id = EXCLUDED.tx_in_id, 299 authorization_sig = EXCLUDED.authorization_sig; 300 END $$; 301 302 CREATE FUNCTION delete_prepared_transfers ( 303 IN in_authorization_pub BYTEA, 304 IN in_timestamp INT8, 305 OUT out_found BOOLEAN 306 ) 307 LANGUAGE plpgsql AS $$ 308 BEGIN 309 310 -- Bounce all pending 311 WITH bounced AS ( 312 DELETE FROM pending_recurrent_in 313 WHERE authorization_pub = in_authorization_pub 314 RETURNING tx_in_id 315 ) 316 INSERT INTO bounced (tx_in_id) 317 SELECT tx_in_id FROM bounced; 318 319 -- Delete registration 320 DELETE FROM prepared_in 321 WHERE authorization_pub = in_authorization_pub; 322 out_found = FOUND; 323 324 END $$;