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