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