wise-procedures.sql (7197B)
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 BEGIN 68 out_pending=false; 69 -- Check for idempotence 70 SELECT tx_in_id, valued_at 71 INTO out_tx_row_id, out_valued_at 72 FROM tx_in 73 WHERE wise_ref = in_wise_ref; 74 out_new = NOT found; 75 IF NOT out_new THEN 76 RETURN; 77 END IF; 78 79 -- Resolve mapping logic 80 IF in_type = 'map' THEN 81 SELECT type, account_pub, authorization_pub, authorization_sig, 82 tx_in_id IS NOT NULL AND NOT recurrent, 83 tx_in_id IS NOT NULL AND recurrent 84 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 85 FROM prepared_in 86 WHERE authorization_pub = in_metadata; 87 out_unknown_mapping = NOT FOUND; 88 IF out_unknown_mapping OR out_mapping_reuse THEN 89 RETURN; 90 END IF; 91 END IF; 92 93 94 -- Check conflict 95 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve'); 96 IF out_reserve_pub_reuse THEN 97 RETURN; 98 END IF; 99 100 -- Insert new incoming transaction 101 out_valued_at = in_valued_at; 102 INSERT INTO tx_in ( 103 balance_id, 104 wise_ref, 105 amount, 106 subject, 107 debit_payto, 108 debit_name, 109 valued_at, 110 registered_at 111 ) VALUES ( 112 in_balance_id, 113 in_wise_ref, 114 in_amount, 115 in_subject, 116 in_debit_payto, 117 in_debit_name, 118 in_valued_at, 119 in_now 120 ) 121 RETURNING tx_in_id INTO out_tx_row_id; 122 -- Notify new incoming transaction registration 123 PERFORM pg_notify('tx_in', in_balance_id || ' ' || out_tx_row_id); 124 125 IF out_pending THEN 126 -- Delay talerable registration until mapping again 127 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 128 VALUES (out_tx_row_id, local_authorization_pub); 129 ELSIF in_type IS NOT NULL AND in_debit_payto IS NOT NULL THEN 130 UPDATE prepared_in 131 SET tx_in_id = out_tx_row_id 132 WHERE ( 133 tx_in_id IS NULL AND account_pub = in_metadata AND type='reserve' 134 ) OR authorization_pub = local_authorization_pub; 135 -- Insert new incoming talerable transaction 136 INSERT INTO taler_in ( 137 tx_in_id, 138 type, 139 metadata, 140 authorization_pub, 141 authorization_sig 142 ) VALUES ( 143 out_tx_row_id, 144 in_type, 145 in_metadata, 146 local_authorization_pub, 147 local_authorization_sig 148 ); 149 -- Notify new incoming talerable transaction registration 150 PERFORM pg_notify('taler_in', in_balance_id || ' ' || out_tx_row_id); 151 END IF; 152 END $$; 153 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 154 155 156 CREATE FUNCTION register_prepared_transfers ( 157 IN in_type incoming_type, 158 IN in_account_pub BYTEA, 159 IN in_authorization_pub BYTEA, 160 IN in_authorization_sig BYTEA, 161 IN in_recurrent BOOLEAN, 162 IN in_timestamp INT8, 163 -- Error status 164 OUT out_reserve_pub_reuse BOOLEAN 165 ) 166 LANGUAGE plpgsql AS $$ 167 DECLARE 168 talerable_tx INT8; 169 local_balance_id INT8; 170 idempotent BOOLEAN; 171 BEGIN 172 173 -- Check idempotency 174 SELECT type = in_type 175 AND account_pub = in_account_pub 176 AND recurrent = in_recurrent 177 INTO idempotent 178 FROM prepared_in 179 WHERE authorization_pub = in_authorization_pub; 180 181 -- Check idempotency and delay garbage collection 182 IF FOUND AND idempotent THEN 183 UPDATE prepared_in 184 SET registered_at=in_timestamp 185 WHERE authorization_pub=in_authorization_pub; 186 RETURN; 187 END IF; 188 189 -- Check reserve pub reuse 190 out_reserve_pub_reuse=in_type = 'reserve' AND ( 191 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 192 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 193 ); 194 IF out_reserve_pub_reuse THEN 195 RETURN; 196 END IF; 197 198 IF in_recurrent THEN 199 -- Finalize one pending right now 200 WITH pending AS ( 201 SELECT tx_in_id 202 FROM pending_recurrent_in 203 JOIN tx_in USING (tx_in_id) 204 WHERE authorization_pub = in_authorization_pub 205 ORDER BY valued_at ASC 206 LIMIT 1 207 ), moved_tx AS ( 208 DELETE FROM pending_recurrent_in 209 USING tx_in, pending 210 WHERE pending_recurrent_in.tx_in_id = tx_in.tx_in_id 211 AND pending_recurrent_in.tx_in_id = pending.tx_in_id 212 RETURNING pending_recurrent_in.tx_in_id, tx_in.balance_id 213 ), inserted AS ( 214 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 215 SELECT tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 216 FROM moved_tx 217 RETURNING tx_in_id 218 ) 219 SELECT inserted.tx_in_id, moved_tx.balance_id 220 INTO talerable_tx, local_balance_id 221 FROM inserted 222 JOIN moved_tx USING (tx_in_id); 223 IF talerable_tx IS NOT NULL THEN 224 PERFORM pg_notify('taler_in', local_balance_id || ' ' || talerable_tx); 225 END IF; 226 END IF; 227 228 -- Upsert registration 229 INSERT INTO prepared_in ( 230 type, 231 account_pub, 232 authorization_pub, 233 authorization_sig, 234 recurrent, 235 registered_at, 236 tx_in_id 237 ) VALUES ( 238 in_type, 239 in_account_pub, 240 in_authorization_pub, 241 in_authorization_sig, 242 in_recurrent, 243 in_timestamp, 244 talerable_tx 245 ) ON CONFLICT (authorization_pub) 246 DO UPDATE SET 247 type = EXCLUDED.type, 248 account_pub = EXCLUDED.account_pub, 249 recurrent = EXCLUDED.recurrent, 250 registered_at = EXCLUDED.registered_at, 251 tx_in_id = EXCLUDED.tx_in_id, 252 authorization_sig = EXCLUDED.authorization_sig; 253 END $$; 254 255 CREATE FUNCTION delete_prepared_transfers ( 256 IN in_authorization_pub BYTEA, 257 IN in_timestamp INT8, 258 OUT out_found BOOLEAN 259 ) 260 LANGUAGE plpgsql AS $$ 261 BEGIN 262 263 -- Delete registration 264 DELETE FROM prepared_in 265 WHERE authorization_pub = in_authorization_pub; 266 out_found = FOUND; 267 268 END $$;