wise-procedures.sql (7556B)
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 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 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 IF out_pending THEN 129 -- Delay talerable registration until mapping again 130 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 131 VALUES (out_tx_row_id, local_authorization_pub); 132 ELSIF in_type IS NOT NULL AND in_debit_payto IS NOT NULL THEN 133 UPDATE prepared_in 134 SET tx_in_id = out_tx_row_id 135 WHERE ( 136 tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 137 ) OR authorization_pub = local_authorization_pub; 138 -- Lock for incoming history id commit ordering 139 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 140 -- Insert new incoming talerable transaction 141 INSERT INTO taler_in ( 142 tx_in_id, 143 type, 144 metadata, 145 authorization_pub, 146 authorization_sig 147 ) VALUES ( 148 out_tx_row_id, 149 in_type, 150 in_metadata, 151 local_authorization_pub, 152 local_authorization_sig 153 ) RETURNING taler_in_id INTO local_taler_in_id; 154 -- Notify new incoming talerable transaction registration 155 PERFORM pg_notify('taler_in', in_balance_id || ' ' || local_taler_in_id); 156 END IF; 157 END $$; 158 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 159 160 161 CREATE FUNCTION register_prepared_transfers ( 162 IN in_type incoming_type, 163 IN in_account_pub BYTEA, 164 IN in_authorization_pub BYTEA, 165 IN in_authorization_sig BYTEA, 166 IN in_recurrent BOOLEAN, 167 IN in_timestamp INT8, 168 -- Error status 169 OUT out_reserve_pub_reuse BOOLEAN 170 ) 171 LANGUAGE plpgsql AS $$ 172 DECLARE 173 talerable_tx INT8; 174 local_taler_in_id INT8; 175 local_balance_id INT8; 176 idempotent BOOLEAN; 177 BEGIN 178 179 -- Check idempotency 180 SELECT type = in_type 181 AND account_pub = in_account_pub 182 AND recurrent = in_recurrent 183 INTO idempotent 184 FROM prepared_in 185 WHERE authorization_pub = in_authorization_pub; 186 187 -- Check idempotency and delay garbage collection 188 IF FOUND AND idempotent THEN 189 UPDATE prepared_in 190 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 191 WHERE authorization_pub=in_authorization_pub; 192 RETURN; 193 END IF; 194 195 -- Check reserve pub reuse 196 out_reserve_pub_reuse=in_type = 'reserve' AND ( 197 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 198 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 199 ); 200 IF out_reserve_pub_reuse THEN 201 RETURN; 202 END IF; 203 204 IF in_recurrent THEN 205 -- Finalize one pending right now. Lock history only if there is a row to publish. 206 DELETE FROM pending_recurrent_in 207 USING tx_in 208 WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id 209 AND pending_recurrent_in.tx_in_id = ( 210 SELECT pending.tx_in_id 211 FROM pending_recurrent_in AS pending 212 JOIN tx_in USING (tx_in_id) 213 WHERE authorization_pub = in_authorization_pub 214 ORDER BY valued_at ASC 215 LIMIT 1 216 ) 217 RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id 218 INTO talerable_tx, local_balance_id; 219 IF talerable_tx IS NOT NULL THEN 220 -- Lock for incoming history id commit ordering 221 LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE; 222 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 223 VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig) 224 RETURNING taler_in_id INTO local_taler_in_id; 225 PERFORM pg_notify('taler_in', local_balance_id || ' ' || local_taler_in_id); 226 END IF; 227 END IF; 228 229 -- Upsert registration 230 INSERT INTO prepared_in ( 231 type, 232 account_pub, 233 authorization_pub, 234 authorization_sig, 235 recurrent, 236 registered_at, 237 tx_in_id 238 ) VALUES ( 239 in_type, 240 in_account_pub, 241 in_authorization_pub, 242 in_authorization_sig, 243 in_recurrent, 244 in_timestamp, 245 talerable_tx 246 ) ON CONFLICT (authorization_pub) 247 DO UPDATE SET 248 type = EXCLUDED.type, 249 account_pub = EXCLUDED.account_pub, 250 recurrent = EXCLUDED.recurrent, 251 registered_at = EXCLUDED.registered_at, 252 tx_in_id = EXCLUDED.tx_in_id, 253 authorization_sig = EXCLUDED.authorization_sig; 254 END $$; 255 256 CREATE FUNCTION delete_prepared_transfers ( 257 IN in_authorization_pub BYTEA, 258 IN in_timestamp INT8, 259 OUT out_found BOOLEAN 260 ) 261 LANGUAGE plpgsql AS $$ 262 BEGIN 263 264 -- Delete registration 265 DELETE FROM prepared_in 266 WHERE authorization_pub = in_authorization_pub; 267 out_found = FOUND; 268 269 END $$;