depolymerizer-bitcoin-procedures.sql (12518B)
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 depolymerizer_bitcoin; 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 = 'depolymerizer_bitcoin'::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_credit_acc TEXT, 45 IN in_credit_name TEXT, 46 IN in_request_uid BYTEA, 47 IN in_wtid BYTEA, 48 IN in_metadata TEXT, 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 BEGIN 59 -- Check for idempotence and conflict 60 SELECT (amount != in_amount 61 OR credit_acc != in_credit_acc 62 OR credit_name != in_credit_name 63 OR exchange_url != in_exchange_base_url 64 OR wtid != in_wtid 65 OR metadata IS DISTINCT FROM in_metadata) 66 ,transfer_id, created_at 67 INTO out_request_uid_reuse, out_transfer_row_id, out_created_at 68 FROM transfer 69 WHERE request_uid = in_request_uid; 70 IF FOUND THEN 71 RETURN; 72 END IF; 73 74 -- Register a transfer operation 75 INSERT INTO transfer ( 76 amount, 77 exchange_url, 78 credit_acc, 79 credit_name, 80 request_uid, 81 wtid, 82 metadata, 83 created_at, 84 status 85 ) VALUES ( 86 in_amount, 87 in_exchange_base_url, 88 in_credit_acc, 89 in_credit_name, 90 in_request_uid, 91 in_wtid, 92 in_metadata, 93 in_now, 94 'requested' 95 ) ON CONFLICT (wtid) DO NOTHING 96 RETURNING transfer_id, created_at INTO out_transfer_row_id, out_created_at; 97 out_wtid_reuse=NOT FOUND; 98 IF out_wtid_reuse THEN 99 RETURN; 100 END IF; 101 -- Notify new transaction 102 PERFORM pg_notify('transfer', out_transfer_row_id || ''); 103 END $$; 104 COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it'; 105 106 CREATE FUNCTION register_tx_in( 107 IN in_txid BYTEA, 108 IN in_amount taler_amount, 109 IN in_debit_acc TEXT, 110 IN in_received_at INT8, 111 IN in_type incoming_type, 112 IN in_metadata BYTEA, 113 -- Error status 114 OUT out_reserve_pub_reuse BOOLEAN, 115 OUT out_mapping_reuse BOOLEAN, 116 OUT out_unknown_mapping BOOLEAN, 117 -- Success return 118 OUT out_tx_row_id INT8, 119 OUT out_valued_at INT8, 120 OUT out_new BOOLEAN, 121 OUT out_pending BOOLEAN 122 ) 123 LANGUAGE plpgsql AS $$ 124 DECLARE 125 local_authorization_pub BYTEA; 126 local_authorization_sig BYTEA; 127 local_taler_in_id INT8; 128 BEGIN 129 out_pending=false; 130 131 -- Check for idempotence, txid is a hash of the transaction data, if the txid match all info match 132 SELECT tx_in_id, received_at INTO out_tx_row_id, out_valued_at FROM tx_in WHERE txid = in_txid; 133 out_new=NOT FOUND; 134 IF NOT out_new THEN 135 RETURN; 136 END IF; 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_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 144 FROM prepared_in 145 WHERE authorization_pub = in_metadata; 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 out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve'); 154 IF out_reserve_pub_reuse THEN 155 RETURN; 156 END IF; 157 158 -- Insert new incoming transaction 159 INSERT INTO tx_in ( 160 txid, 161 amount, 162 debit_acc, 163 received_at 164 ) VALUES ( 165 in_txid, 166 in_amount, 167 in_debit_acc, 168 in_received_at 169 ) RETURNING tx_in_id, received_at INTO out_tx_row_id, out_valued_at; 170 -- Notify new incoming transaction registration 171 PERFORM pg_notify('tx_in', out_tx_row_id || ''); 172 173 IF out_pending THEN 174 -- Delay talerable registration until mapping again 175 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 176 VALUES (out_tx_row_id, local_authorization_pub); 177 ELSIF in_type IS NOT NULL THEN 178 UPDATE prepared_in 179 SET tx_in_id = out_tx_row_id 180 WHERE (tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve') 181 OR authorization_pub = local_authorization_pub; 182 -- Insert new incoming talerable tranreceived_atsaction 183 INSERT INTO taler_in ( 184 tx_in_id, 185 type, 186 metadata, 187 authorization_pub, 188 authorization_sig 189 ) VALUES ( 190 out_tx_row_id, 191 in_type, 192 in_metadata, 193 local_authorization_pub, 194 local_authorization_sig 195 ) RETURNING taler_in_id INTO local_taler_in_id; 196 -- Notify new incoming talerable transaction registration 197 PERFORM pg_notify('taler_in', local_taler_in_id::text); 198 END IF; 199 END $$; 200 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 201 202 203 CREATE FUNCTION register_bounce_tx_in( 204 IN in_txid BYTEA, 205 IN in_amount taler_amount, 206 IN in_debit_acc TEXT, 207 IN in_received_at INT8, 208 IN in_reason TEXT, 209 IN in_now INT8, 210 -- Success return 211 OUT out_tx_row_id INT8, 212 OUT out_tx_new BOOLEAN, 213 OUT out_bounce_row_id INT8, 214 OUT out_bounce_new BOOLEAN 215 ) 216 LANGUAGE plpgsql AS $$ 217 BEGIN 218 -- Register incoming transaction idempotently 219 SELECT register_tx_in.out_tx_row_id, register_tx_in.out_new 220 INTO out_tx_row_id, out_tx_new 221 FROM register_tx_in(in_txid, in_amount, in_debit_acc, in_received_at, NULL, NULL); 222 223 -- Register bounce 224 INSERT INTO bounced( 225 tx_in_id, 226 reason, 227 status 228 ) VALUES ( 229 out_tx_row_id, 230 in_reason, 231 'requested' 232 ) ON CONFLICT (tx_in_id) DO NOTHING; 233 END $$; 234 COMMENT ON FUNCTION register_bounce_tx_in IS 'Register an incoming transaction and bounce it idempotently'; 235 236 CREATE FUNCTION sync_out( 237 IN in_txid BYTEA, 238 IN in_replaces_txid BYTEA, 239 IN in_amount taler_amount, 240 IN in_credit_acc TEXT, 241 IN in_wtid BYTEA, 242 IN in_exchange_base_url TEXT, 243 IN in_metadata TEXT, 244 IN in_bounced_txid BYTEA, 245 IN in_created_at INT8, 246 IN in_confirmed BOOLEAN, 247 IN in_now INT8, 248 -- Success return 249 OUT out_tx_row_id INT8, 250 OUT out_new BOOLEAN, 251 OUT out_replaced BOOLEAN, 252 OUT out_recovered BOOLEAN 253 ) 254 LANGUAGE plpgsql AS $$ 255 DECLARE 256 local_id INT8; 257 local_status debit_status; 258 local_update BOOLEAN; 259 BEGIN 260 IF in_confirmed THEN 261 local_status='confirmed'; 262 ELSE 263 local_status='sent'; 264 END IF; 265 IF in_wtid IS NOT NULL THEN 266 -- Sync transfer status 267 SELECT 268 txid=in_replaces_txid, 269 txid IS NULL, 270 status!=local_status OR txid!=in_txid 271 INTO 272 out_replaced, 273 out_recovered, 274 local_update 275 FROM transfer 276 WHERE wtid=in_wtid; 277 IF local_update THEN 278 UPDATE transfer SET status=local_status,txid=in_txid 279 WHERE wtid=in_wtid; 280 END IF; 281 ELSIF in_bounced_txid IS NOT NULL THEN 282 -- Sync bounce status 283 SELECT 284 bounced.txid=in_replaces_txid, 285 bounced.txid IS NULL, 286 status!=local_status OR bounced.txid!=in_txid 287 INTO 288 out_replaced, 289 out_recovered, 290 local_update 291 FROM bounced JOIN tx_in USING (tx_in_id) 292 WHERE tx_in.txid=in_bounced_txid; 293 IF local_update THEN 294 UPDATE bounced SET status=local_status,txid=in_txid 295 FROM tx_in 296 WHERE bounced.tx_in_id=tx_in.tx_in_id AND tx_in.txid=in_bounced_txid; 297 END IF; 298 END IF; 299 300 IF in_confirmed THEN 301 -- Sync tx_out status 302 UPDATE tx_out SET txid=in_txid WHERE txid=in_replaces_txid; 303 out_replaced=out_replaced OR FOUND; 304 SELECT tx_out_id INTO out_tx_row_id 305 FROM tx_out WHERE txid=in_txid; 306 IF FOUND THEN 307 RETURN; 308 END IF; 309 out_new = TRUE; 310 311 -- Insert new outgoing transaction 312 INSERT INTO tx_out ( 313 amount, 314 credit_acc, 315 txid, 316 created_at 317 ) VALUES ( 318 in_amount, 319 in_credit_acc, 320 in_txid, 321 in_created_at 322 ) RETURNING tx_out_id INTO out_tx_row_id; 323 -- Notify new outgoing transaction registration 324 PERFORM pg_notify('tx_out', out_tx_row_id || ''); 325 326 IF in_wtid IS NOT NULL THEN 327 -- Insert new outgoing talerable transaction 328 INSERT INTO taler_out ( 329 tx_out_id, 330 wtid, 331 exchange_base_url, 332 metadata 333 ) VALUES ( 334 out_tx_row_id, 335 in_wtid, 336 in_exchange_base_url, 337 in_metadata 338 ) ON CONFLICT (wtid) DO NOTHING; 339 IF FOUND THEN 340 -- Notify new outgoing talerable transaction registration 341 PERFORM pg_notify('taler_out', out_tx_row_id || ''); 342 END IF; 343 END IF; 344 END IF; 345 END $$; 346 COMMENT ON FUNCTION sync_out IS 'Sync a debit blockchain state with local state'; 347 348 349 CREATE FUNCTION register_prepared_transfers ( 350 IN in_type incoming_type, 351 IN in_account_pub BYTEA, 352 IN in_authorization_pub BYTEA, 353 IN in_authorization_sig BYTEA, 354 IN in_recurrent BOOLEAN, 355 IN in_timestamp INT8, 356 -- Error status 357 OUT out_reserve_pub_reuse BOOLEAN 358 ) 359 LANGUAGE plpgsql AS $$ 360 DECLARE 361 talerable_tx INT8; 362 local_taler_in_id INT8; 363 idempotent BOOLEAN; 364 BEGIN 365 366 -- Check idempotency 367 SELECT type = in_type 368 AND account_pub = in_account_pub 369 AND recurrent = in_recurrent 370 INTO idempotent 371 FROM prepared_in 372 WHERE authorization_pub = in_authorization_pub; 373 374 -- Check idempotency and delay garbage collection 375 IF FOUND AND idempotent THEN 376 UPDATE prepared_in 377 SET registered_at=in_timestamp, authorization_sig=in_authorization_sig 378 WHERE authorization_pub=in_authorization_pub; 379 RETURN; 380 END IF; 381 382 -- Check reserve pub reuse 383 out_reserve_pub_reuse=in_type = 'reserve' AND ( 384 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 385 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 386 ); 387 IF out_reserve_pub_reuse THEN 388 RETURN; 389 END IF; 390 391 IF in_recurrent THEN 392 -- Finalize one pending right now 393 WITH moved_tx AS ( 394 DELETE FROM pending_recurrent_in 395 WHERE tx_in_id = ( 396 SELECT tx_in_id 397 FROM pending_recurrent_in 398 JOIN tx_in USING (tx_in_id) 399 WHERE authorization_pub = in_authorization_pub 400 ORDER BY received_at ASC 401 LIMIT 1 402 ) 403 RETURNING tx_in_id 404 ) 405 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 406 SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 407 FROM moved_tx 408 RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id; 409 IF talerable_tx IS NOT NULL THEN 410 PERFORM pg_notify('taler_in', local_taler_in_id::text); 411 END IF; 412 ELSE 413 -- Bounce all pending 414 WITH bounced AS ( 415 DELETE FROM pending_recurrent_in 416 WHERE authorization_pub = in_authorization_pub 417 RETURNING tx_in_id 418 ) 419 INSERT INTO bounced (tx_in_id, reason, status) 420 SELECT tx_in_id, 'cancelled mapping', 'requested' FROM bounced; 421 END IF; 422 423 -- Upsert registration 424 INSERT INTO prepared_in ( 425 type, 426 account_pub, 427 authorization_pub, 428 authorization_sig, 429 recurrent, 430 registered_at, 431 tx_in_id 432 ) VALUES ( 433 in_type, 434 in_account_pub, 435 in_authorization_pub, 436 in_authorization_sig, 437 in_recurrent, 438 in_timestamp, 439 talerable_tx 440 ) ON CONFLICT (authorization_pub) 441 DO UPDATE SET 442 type = EXCLUDED.type, 443 account_pub = EXCLUDED.account_pub, 444 recurrent = EXCLUDED.recurrent, 445 registered_at = EXCLUDED.registered_at, 446 tx_in_id = EXCLUDED.tx_in_id, 447 authorization_sig = EXCLUDED.authorization_sig; 448 END $$; 449 450 CREATE FUNCTION delete_prepared_transfers ( 451 IN in_authorization_pub BYTEA, 452 IN in_timestamp INT8, 453 OUT out_found BOOLEAN 454 ) 455 LANGUAGE plpgsql AS $$ 456 BEGIN 457 458 -- Bounce all pending 459 WITH bounced AS ( 460 DELETE FROM pending_recurrent_in 461 WHERE authorization_pub = in_authorization_pub 462 RETURNING tx_in_id 463 ) 464 INSERT INTO bounced (tx_in_id, reason, status) 465 SELECT tx_in_id, 'cancelled mapping', 'requested' FROM bounced; 466 467 -- Delete registration 468 DELETE FROM prepared_in 469 WHERE authorization_pub = in_authorization_pub; 470 out_found = FOUND; 471 472 END $$;