wise-procedures.sql (8199B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 wise; 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 = 'wise'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 42 CREATE FUNCTION register_tx_in( 43 IN in_balance_id INT8, 44 IN in_wise_ref TEXT, 45 IN in_amount taler_amount, 46 IN in_subject TEXT, 47 IN in_debit_payto TEXT, 48 IN in_debit_name TEXT, 49 IN in_valued_at INT8, 50 IN in_type incoming_type, 51 IN in_metadata BYTEA, 52 IN in_now INT8, 53 -- Error status 54 OUT out_reserve_pub_reuse BOOLEAN, 55 OUT out_mapping_reuse BOOLEAN, 56 OUT out_unknown_mapping BOOLEAN, 57 -- Success return 58 OUT out_tx_row_id INT8, 59 OUT out_valued_at INT8, 60 OUT out_new BOOLEAN, 61 OUT out_pending BOOLEAN 62 ) 63 LANGUAGE plpgsql AS $$ 64 DECLARE 65 local_authorization_pub BYTEA; 66 local_authorization_sig BYTEA; 67 local_taler_in_id INT8; 68 BEGIN 69 out_pending=false; 70 -- Check for idempotence 71 SELECT tx_in_id, valued_at 72 INTO out_tx_row_id, out_valued_at 73 FROM tx_in 74 WHERE wise_ref = in_wise_ref; 75 out_new = NOT found; 76 IF NOT out_new THEN 77 RETURN; 78 END IF; 79 80 -- Resolve mapping logic 81 IF in_type = 'map' THEN 82 SELECT type, account_pub, authorization_pub, authorization_sig, 83 tx_in_id IS NOT NULL AND NOT recurrent, 84 tx_in_id IS NOT NULL AND recurrent 85 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 86 FROM prepared_in 87 WHERE balance_id = in_balance_id AND authorization_pub = in_metadata; 88 out_unknown_mapping = NOT FOUND; 89 IF out_unknown_mapping OR out_mapping_reuse THEN 90 RETURN; 91 END IF; 92 END IF; 93 94 95 -- Check conflict 96 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE balance_id = in_balance_id AND metadata = in_metadata AND type = 'reserve'); 97 IF out_reserve_pub_reuse THEN 98 RETURN; 99 END IF; 100 101 -- Insert new incoming transaction 102 out_valued_at = in_valued_at; 103 -- Lock for revenue id commit ordering 104 LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE; 105 INSERT INTO tx_in ( 106 balance_id, 107 wise_ref, 108 amount, 109 subject, 110 debit_payto, 111 debit_name, 112 valued_at, 113 registered_at 114 ) VALUES ( 115 in_balance_id, 116 in_wise_ref, 117 in_amount, 118 in_subject, 119 in_debit_payto, 120 in_debit_name, 121 in_valued_at, 122 in_now 123 ) 124 RETURNING tx_in_id INTO out_tx_row_id; 125 -- Notify new incoming transaction registration 126 PERFORM pg_notify('tx_in', in_balance_id || ' ' || out_tx_row_id); 127 128 -- Keep unresolved senders in the ledger without publishing or queueing them 129 IF in_debit_payto IS NULL THEN 130 out_pending = false; 131 RETURN; 132 END IF; 133 134 IF out_pending THEN 135 -- Delay talerable registration until mapping again 136 INSERT INTO pending_recurrent_in (tx_in_id, balance_id, authorization_pub) 137 VALUES (out_tx_row_id, in_balance_id, local_authorization_pub); 138 ELSIF in_type IS NOT NULL THEN 139 UPDATE prepared_in 140 SET tx_in_id = out_tx_row_id 141 WHERE balance_id = in_balance_id AND (( 142 tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 143 ) OR authorization_pub = local_authorization_pub); 144 -- Lock for incoming history id commit ordering 145 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 146 -- Insert new incoming talerable transaction 147 INSERT INTO taler_in ( 148 balance_id, 149 tx_in_id, 150 type, 151 metadata, 152 authorization_pub, 153 authorization_sig 154 ) VALUES ( 155 in_balance_id, 156 out_tx_row_id, 157 in_type, 158 in_metadata, 159 local_authorization_pub, 160 local_authorization_sig 161 ) RETURNING taler_in_id INTO local_taler_in_id; 162 -- Notify new incoming talerable transaction registration 163 PERFORM pg_notify('taler_in', in_balance_id || ' ' || local_taler_in_id); 164 END IF; 165 END $$; 166 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 167 168 169 CREATE FUNCTION register_prepared_transfers ( 170 IN in_balance_id INT8, 171 IN in_type incoming_type, 172 IN in_account_pub BYTEA, 173 IN in_authorization_pub BYTEA, 174 IN in_authorization_sig BYTEA, 175 IN in_recurrent BOOLEAN, 176 IN in_timestamp INT8, 177 -- Error status 178 OUT out_reserve_pub_reuse BOOLEAN 179 ) 180 LANGUAGE plpgsql AS $$ 181 DECLARE 182 talerable_tx INT8; 183 local_taler_in_id INT8; 184 local_balance_id INT8; 185 idempotent BOOLEAN; 186 BEGIN 187 188 -- Check idempotency 189 SELECT type = in_type 190 AND account_pub = in_account_pub 191 AND recurrent = in_recurrent 192 INTO idempotent 193 FROM prepared_in 194 WHERE balance_id = in_balance_id AND authorization_pub = in_authorization_pub; 195 196 -- Check idempotency and delay garbage collection 197 IF FOUND AND idempotent THEN 198 UPDATE prepared_in 199 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 200 WHERE balance_id = in_balance_id AND authorization_pub=in_authorization_pub; 201 RETURN; 202 END IF; 203 204 -- Check reserve pub reuse 205 out_reserve_pub_reuse=in_type = 'reserve' AND ( 206 EXISTS(SELECT FROM taler_in WHERE balance_id = in_balance_id AND metadata = in_account_pub AND type = 'reserve') 207 OR EXISTS(SELECT FROM prepared_in WHERE balance_id = in_balance_id AND account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 208 ); 209 IF out_reserve_pub_reuse THEN 210 RETURN; 211 END IF; 212 213 IF in_recurrent THEN 214 -- Finalize one pending right now. Lock history only if there is a row to publish. 215 DELETE FROM pending_recurrent_in 216 USING tx_in 217 WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id 218 AND pending_recurrent_in.tx_in_id = ( 219 SELECT pending.tx_in_id 220 FROM pending_recurrent_in AS pending 221 JOIN tx_in USING (tx_in_id) 222 WHERE pending.balance_id = in_balance_id 223 AND tx_in.balance_id = in_balance_id 224 AND authorization_pub = in_authorization_pub 225 ORDER BY valued_at ASC 226 LIMIT 1 227 ) 228 RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id 229 INTO talerable_tx, local_balance_id; 230 IF talerable_tx IS NOT NULL THEN 231 -- Lock for incoming history id commit ordering 232 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 233 INSERT INTO taler_in (balance_id, tx_in_id, type, metadata, authorization_pub, authorization_sig) 234 VALUES (in_balance_id, talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig) 235 RETURNING taler_in_id INTO local_taler_in_id; 236 PERFORM pg_notify('taler_in', local_balance_id || ' ' || local_taler_in_id); 237 END IF; 238 END IF; 239 240 -- Upsert registration 241 INSERT INTO prepared_in ( 242 balance_id, 243 type, 244 account_pub, 245 authorization_pub, 246 authorization_sig, 247 recurrent, 248 registered_at, 249 tx_in_id 250 ) VALUES ( 251 in_balance_id, 252 in_type, 253 in_account_pub, 254 in_authorization_pub, 255 in_authorization_sig, 256 in_recurrent, 257 in_timestamp, 258 talerable_tx 259 ) ON CONFLICT (balance_id, authorization_pub) 260 DO UPDATE SET 261 type = EXCLUDED.type, 262 account_pub = EXCLUDED.account_pub, 263 recurrent = EXCLUDED.recurrent, 264 registered_at = EXCLUDED.registered_at, 265 tx_in_id = EXCLUDED.tx_in_id, 266 authorization_sig = EXCLUDED.authorization_sig; 267 END $$; 268 269 CREATE FUNCTION delete_prepared_transfers ( 270 IN in_balance_id INT8, 271 IN in_authorization_pub BYTEA, 272 IN in_timestamp INT8, 273 OUT out_found BOOLEAN 274 ) 275 LANGUAGE plpgsql AS $$ 276 BEGIN 277 278 -- Delete registration 279 DELETE FROM prepared_in 280 WHERE balance_id = in_balance_id AND authorization_pub = in_authorization_pub; 281 out_found = FOUND; 282 283 END $$;