taler-api-procedures.sql (8586B)
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 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 local_taler_in_id INT8; 136 BEGIN 137 local_pending=false; 138 139 -- Resolve mapping logic 140 IF in_type = 'map' THEN 141 SELECT type, account_pub, authorization_pub, authorization_sig, 142 tx_in_id IS NOT NULL AND NOT recurrent, 143 tx_in_id IS NOT NULL AND recurrent 144 INTO in_type, in_account_pub, local_authorization_pub, local_authorization_sig, out_mapping_reuse, local_pending 145 FROM prepared_in 146 WHERE authorization_pub = in_account_pub; 147 out_unknown_mapping = NOT FOUND; 148 IF out_unknown_mapping OR out_mapping_reuse THEN 149 RETURN; 150 END IF; 151 END IF; 152 153 -- Check conflict 154 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'); 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 in_type=type 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 ) RETURNING taler_in_id INTO local_taler_in_id; 195 -- Notify new incoming transaction 196 PERFORM pg_notify('incoming_tx', local_taler_in_id::text); 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 local_taler_in_id INT8; 216 idempotent BOOLEAN; 217 BEGIN 218 219 -- Check idempotency 220 SELECT type = in_type 221 AND account_pub = in_account_pub 222 AND recurrent = in_recurrent 223 INTO idempotent 224 FROM prepared_in 225 WHERE authorization_pub = in_authorization_pub; 226 227 -- Check idempotency and delay garbage collection 228 IF FOUND AND idempotent THEN 229 UPDATE prepared_in 230 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 231 WHERE authorization_pub=in_authorization_pub; 232 RETURN; 233 END IF; 234 235 -- Check reserve pub reuse 236 out_reserve_pub_reuse=in_type = 'reserve' AND ( 237 EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve') 238 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 239 ); 240 IF out_reserve_pub_reuse THEN 241 RETURN; 242 END IF; 243 244 IF in_recurrent THEN 245 -- Finalize one pending right now 246 WITH moved_tx AS ( 247 DELETE FROM pending_recurrent_in 248 WHERE tx_in_id = ( 249 SELECT tx_in_id 250 FROM pending_recurrent_in 251 JOIN tx_in USING (tx_in_id) 252 WHERE authorization_pub = in_authorization_pub 253 ORDER BY created_at ASC 254 LIMIT 1 255 ) 256 RETURNING tx_in_id 257 ) 258 INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig) 259 SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 260 FROM moved_tx 261 RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id; 262 IF talerable_tx IS NOT NULL THEN 263 PERFORM pg_notify('incoming_tx', local_taler_in_id::text); 264 END IF; 265 ELSE 266 -- Bounce all pending 267 WITH bounced AS ( 268 DELETE FROM pending_recurrent_in 269 WHERE authorization_pub = in_authorization_pub 270 RETURNING tx_in_id 271 ) 272 INSERT INTO bounced (tx_in_id) 273 SELECT tx_in_id FROM bounced; 274 END IF; 275 276 -- Upsert registration 277 INSERT INTO prepared_in ( 278 type, 279 account_pub, 280 authorization_pub, 281 authorization_sig, 282 recurrent, 283 registered_at, 284 tx_in_id 285 ) VALUES ( 286 in_type, 287 in_account_pub, 288 in_authorization_pub, 289 in_authorization_sig, 290 in_recurrent, 291 in_timestamp, 292 talerable_tx 293 ) ON CONFLICT (authorization_pub) 294 DO UPDATE SET 295 type = EXCLUDED.type, 296 account_pub = EXCLUDED.account_pub, 297 recurrent = EXCLUDED.recurrent, 298 registered_at = EXCLUDED.registered_at, 299 tx_in_id = EXCLUDED.tx_in_id, 300 authorization_sig = EXCLUDED.authorization_sig; 301 END $$; 302 303 CREATE FUNCTION delete_prepared_transfers ( 304 IN in_authorization_pub BYTEA, 305 IN in_timestamp INT8, 306 OUT out_found BOOLEAN 307 ) 308 LANGUAGE plpgsql AS $$ 309 BEGIN 310 311 -- Bounce all pending 312 WITH bounced AS ( 313 DELETE FROM pending_recurrent_in 314 WHERE authorization_pub = in_authorization_pub 315 RETURNING tx_in_id 316 ) 317 INSERT INTO bounced (tx_in_id) 318 SELECT tx_in_id FROM bounced; 319 320 -- Delete registration 321 DELETE FROM prepared_in 322 WHERE authorization_pub = in_authorization_pub; 323 out_found = FOUND; 324 325 END $$;