taler-api-procedures.sql (8380B)
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 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'); 154 IF out_reserve_pub_reuse THEN 155 RETURN; 156 END IF; 157 158 -- Register incoming transaction 159 out_created_at=in_now; 160 INSERT INTO tx_in ( 161 amount, 162 debit_payto, 163 created_at, 164 subject 165 ) VALUES ( 166 in_amount, 167 in_debit_payto, 168 in_now, 169 in_subject 170 ) RETURNING tx_in_id INTO out_tx_row_id; 171 IF local_pending THEN 172 -- Delay talerable registration until mapping again 173 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 174 VALUES (out_tx_row_id, local_authorization_pub); 175 ELSE 176 UPDATE prepared_in 177 SET tx_in_id = out_tx_row_id 178 WHERE ( 179 tx_in_id IS NULL AND account_pub = in_account_pub AND type='reserve' 180 ) OR authorization_pub = local_authorization_pub; 181 INSERT INTO taler_in ( 182 tx_in_id, 183 type, 184 account_pub, 185 authorization_pub, 186 authorization_sig 187 ) VALUES ( 188 out_tx_row_id, 189 in_type, 190 in_account_pub, 191 local_authorization_pub, 192 local_authorization_sig 193 ); 194 -- Notify new incoming transaction 195 PERFORM pg_notify('incoming_tx', out_tx_row_id || ''); 196 END IF; 197 198 END $$; 199 COMMENT ON FUNCTION add_incoming IS 'Create an incoming taler transaction and register it'; 200 201 CREATE FUNCTION register_prepared_transfers ( 202 IN in_type incoming_type, 203 IN in_account_pub BYTEA, 204 IN in_authorization_pub BYTEA, 205 IN in_authorization_sig BYTEA, 206 IN in_recurrent BOOLEAN, 207 IN in_timestamp INT8, 208 -- Error status 209 OUT out_reserve_pub_reuse BOOLEAN 210 ) 211 LANGUAGE plpgsql AS $$ 212 DECLARE 213 talerable_tx INT8; 214 idempotent BOOLEAN; 215 BEGIN 216 217 -- Check idempotency 218 SELECT type = in_type 219 AND account_pub = in_account_pub 220 AND recurrent = in_recurrent 221 INTO idempotent 222 FROM prepared_in 223 WHERE authorization_pub = in_authorization_pub; 224 225 -- Check idempotency and delay garbage collection 226 IF FOUND AND idempotent THEN 227 UPDATE prepared_in 228 SET registered_at=in_timestamp 229 WHERE authorization_pub=in_authorization_pub; 230 RETURN; 231 END IF; 232 233 -- Check reserve pub reuse 234 out_reserve_pub_reuse=in_type = 'reserve' AND ( 235 EXISTS(SELECT FROM taler_in WHERE account_pub = in_account_pub AND type = 'reserve') 236 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 237 ); 238 IF out_reserve_pub_reuse THEN 239 RETURN; 240 END IF; 241 242 IF in_recurrent THEN 243 -- Finalize one pending right now 244 WITH moved_tx AS ( 245 DELETE FROM pending_recurrent_in 246 WHERE tx_in_id = ( 247 SELECT tx_in_id 248 FROM pending_recurrent_in 249 JOIN tx_in USING (tx_in_id) 250 WHERE authorization_pub = in_authorization_pub 251 ORDER BY created_at ASC 252 LIMIT 1 253 ) 254 RETURNING tx_in_id 255 ) 256 INSERT INTO taler_in (tx_in_id, type, account_pub, authorization_pub, authorization_sig) 257 SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 258 FROM moved_tx 259 RETURNING tx_in_id INTO talerable_tx; 260 IF talerable_tx IS NOT NULL THEN 261 PERFORM pg_notify('incoming_tx', talerable_tx::text); 262 END IF; 263 ELSE 264 -- Bounce all pending 265 WITH bounced AS ( 266 DELETE FROM pending_recurrent_in 267 WHERE authorization_pub = in_authorization_pub 268 RETURNING tx_in_id 269 ) 270 INSERT INTO bounced (tx_in_id) 271 SELECT tx_in_id FROM bounced; 272 END IF; 273 274 -- Upsert registration 275 INSERT INTO prepared_in ( 276 type, 277 account_pub, 278 authorization_pub, 279 authorization_sig, 280 recurrent, 281 registered_at, 282 tx_in_id 283 ) VALUES ( 284 in_type, 285 in_account_pub, 286 in_authorization_pub, 287 in_authorization_sig, 288 in_recurrent, 289 in_timestamp, 290 talerable_tx 291 ) ON CONFLICT (authorization_pub) 292 DO UPDATE SET 293 type = EXCLUDED.type, 294 account_pub = EXCLUDED.account_pub, 295 recurrent = EXCLUDED.recurrent, 296 registered_at = EXCLUDED.registered_at, 297 tx_in_id = EXCLUDED.tx_in_id, 298 authorization_sig = EXCLUDED.authorization_sig; 299 END $$; 300 301 CREATE FUNCTION delete_prepared_transfers ( 302 IN in_authorization_pub BYTEA, 303 IN in_timestamp INT8, 304 OUT out_found BOOLEAN 305 ) 306 LANGUAGE plpgsql AS $$ 307 BEGIN 308 309 -- Bounce all pending 310 WITH bounced AS ( 311 DELETE FROM pending_recurrent_in 312 WHERE authorization_pub = in_authorization_pub 313 RETURNING tx_in_id 314 ) 315 INSERT INTO bounced (tx_in_id) 316 SELECT tx_in_id FROM bounced; 317 318 -- Delete registration 319 DELETE FROM prepared_in 320 WHERE authorization_pub = in_authorization_pub; 321 out_found = FOUND; 322 323 END $$;