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