pg_insert_transfer_details.sql (7398B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 2025 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 17 18 DROP FUNCTION IF EXISTS merchant_do_insert_transfer_details; 19 CREATE FUNCTION merchant_do_insert_transfer_details ( 20 IN in_instance_id TEXT, 21 IN in_exchange_url TEXT, 22 IN in_payto_uri TEXT, 23 IN in_wtid BYTEA, 24 IN in_execution_time INT8, 25 IN in_exchange_pub BYTEA, 26 IN in_exchange_sig BYTEA, 27 IN in_total_amount taler_amount_currency, 28 IN in_wire_fee taler_amount_currency, 29 IN ina_coin_values taler_amount_currency[], 30 IN ina_deposit_fees taler_amount_currency[], 31 IN ina_coin_pubs BYTEA[], 32 IN ina_contract_terms BYTEA[], 33 OUT out_no_instance BOOL, 34 OUT out_no_account BOOL, 35 OUT out_no_exchange BOOL, 36 OUT out_duplicate BOOL, 37 OUT out_conflict BOOL) 38 LANGUAGE plpgsql 39 AS $$ 40 DECLARE 41 my_merchant_id INT8; 42 my_signkey_serial INT8; 43 my_expected_credit_serial INT8; 44 my_affected_orders RECORD; 45 my_merchant_serial INT8; 46 my_decose INT8; 47 my_order_id TEXT; 48 i INT8; 49 curs CURSOR (arg_coin_pub BYTEA) FOR 50 SELECT mcon.deposit_confirmation_serial, 51 mcon.order_serial 52 FROM merchant_deposits dep 53 JOIN merchant_deposit_confirmations mcon 54 USING (deposit_confirmation_serial) 55 WHERE dep.coin_pub=arg_coin_pub; 56 ini_coin_pub BYTEA; 57 ini_contract_term BYTEA; 58 ini_coin_value taler_amount_currency; 59 ini_deposit_fee taler_amount_currency; 60 BEGIN 61 62 -- Which instance are we using? 63 SELECT merchant_serial 64 INTO my_merchant_id 65 FROM merchant_instances 66 WHERE merchant_id=in_instance_id; 67 68 IF NOT FOUND 69 THEN 70 out_no_instance=TRUE; 71 out_no_account=FALSE; 72 out_no_exchange=FALSE; 73 out_duplicate=FALSE; 74 out_conflict=FALSE; 75 RETURN; 76 END IF; 77 out_no_instance=FALSE; 78 79 -- Determine account that was credited. 80 SELECT expected_credit_serial 81 INTO my_expected_credit_serial 82 FROM merchant_expected_transfers 83 WHERE exchange_url=in_exchange_url 84 AND wtid=in_wtid 85 AND account_serial= 86 (SELECT account_serial 87 FROM merchant_accounts 88 WHERE payto_uri=in_payto_uri 89 AND exchange_url=in_exchange_url 90 AND merchant_serial=my_merchant_id); 91 92 IF NOT FOUND 93 THEN 94 out_no_account=TRUE; 95 out_no_exchange=FALSE; 96 out_duplicate=FALSE; 97 out_conflict=FALSE; 98 RETURN; 99 END IF; 100 out_no_account=FALSE; 101 102 -- Find exchange sign key 103 SELECT signkey_serial 104 INTO my_signkey_serial 105 FROM merchant_exchange_signing_keys 106 WHERE exchange_pub=in_exchange_pub 107 ORDER BY start_date DESC 108 LIMIT 1; 109 110 IF NOT FOUND 111 THEN 112 out_no_exchange=TRUE; 113 out_conflict=FALSE; 114 out_duplicate=FALSE; 115 RETURN; 116 END IF; 117 out_no_exchange=FALSE; 118 119 -- Add signature first, check for idempotent request 120 INSERT INTO merchant_transfer_signatures 121 (expected_credit_serial 122 ,signkey_serial 123 ,credit_amount 124 ,wire_fee 125 ,execution_time 126 ,exchange_sig) 127 VALUES 128 (my_expected_credit_serial 129 ,my_signkey_serial 130 ,in_total_amount 131 ,in_wire_fee 132 ,in_execution_time 133 ,in_exchange_sig) 134 ON CONFLICT DO NOTHING; 135 136 IF NOT FOUND 137 THEN 138 PERFORM 1 139 FROM merchant_transfer_signatures 140 WHERE expected_credit_serial=my_expected_credit_serial 141 AND signkey_serial=my_signkey_serial 142 AND credit_amount=in_total_amount 143 AND wire_fee=in_wire_fee 144 AND execution_time=in_execution_time 145 AND exchange_sig=in_exchange_sig; 146 IF FOUND 147 THEN 148 -- duplicate case 149 out_duplicate=TRUE; 150 out_conflict=FALSE; 151 RETURN; 152 END IF; 153 -- conflict case 154 out_duplicate=FALSE; 155 out_conflict=TRUE; 156 RETURN; 157 END IF; 158 159 out_duplicate=FALSE; 160 out_conflict=FALSE; 161 162 163 FOR i IN 1..array_length(ina_coin_pubs,1) 164 LOOP 165 ini_coin_value=ina_coin_values[i]; 166 ini_deposit_fee=ina_deposit_fees[i]; 167 ini_coin_pub=ina_coin_pubs[i]; 168 ini_contract_term=ina_contract_terms[i]; 169 170 INSERT INTO merchant_expected_transfer_to_coin 171 (deposit_serial 172 ,expected_credit_serial 173 ,offset_in_exchange_list 174 ,exchange_deposit_value 175 ,exchange_deposit_fee) 176 SELECT 177 dep.deposit_serial 178 ,my_expected_credit_serial 179 ,i 180 ,ini_coin_value 181 ,ini_deposit_fee 182 FROM merchant_deposits dep 183 JOIN merchant_deposit_confirmations dcon 184 USING (deposit_confirmation_serial) 185 JOIN merchant_contract_terms cterm 186 USING (order_serial) 187 WHERE dep.coin_pub=ini_coin_pub 188 AND cterm.h_contract_terms=ini_contract_term 189 AND cterm.merchant_serial=my_merchant_id; 190 191 RAISE NOTICE 'iterating over affected orders'; 192 OPEN curs (arg_coin_pub:=ini_coin_pub); 193 LOOP 194 FETCH NEXT FROM curs INTO my_affected_orders; 195 EXIT WHEN NOT FOUND; 196 197 RAISE NOTICE 'checking affected order for completion'; 198 199 my_decose=my_affected_orders.deposit_confirmation_serial; 200 201 PERFORM FROM merchant_deposits md 202 WHERE md.deposit_confirmation_serial=my_decose 203 AND settlement_retry_needed 204 OR settlement_wtid IS NULL; 205 IF NOT FOUND 206 THEN 207 -- must be all done, clear flag 208 UPDATE merchant_deposit_confirmations 209 SET wire_pending=FALSE 210 WHERE (deposit_confirmation_serial=my_decose); 211 212 IF FOUND 213 THEN 214 -- Also update contract terms, if all (other) associated 215 -- deposit_confirmations are also done. 216 217 RAISE NOTICE 'checking affected contract for completion'; 218 PERFORM FROM merchant_deposit_confirmations mdc 219 WHERE mdc.wire_pending 220 AND mdc.order_serial=my_affected_orders.order_serial; 221 IF NOT FOUND 222 THEN 223 224 UPDATE merchant_contract_terms 225 SET wired=TRUE 226 WHERE (order_serial=my_affected_orders.order_serial); 227 228 -- Select merchant_serial and order_id for webhook 229 SELECT merchant_serial, order_id 230 INTO my_merchant_serial, my_order_id 231 FROM merchant_contract_terms 232 WHERE order_serial=my_affected_orders.order_serial; 233 234 -- Insert pending webhook if it exists 235 INSERT INTO merchant_pending_webhooks 236 (merchant_serial 237 ,webhook_serial 238 ,url 239 ,http_method 240 ,body) 241 SELECT mw.merchant_serial 242 ,mw.webhook_serial 243 ,mw.url 244 ,mw.http_method 245 ,replace_placeholder( 246 replace_placeholder(mw.body_template, 'order_id', my_order_id), 247 'wtid', encode(in_wtid, 'hex') 248 )::TEXT 249 FROM merchant_webhook mw 250 WHERE mw.event_type = 'order_settled' 251 AND mw.merchant_serial = my_merchant_serial; 252 END IF; -- no more merchant_deposits waiting for wire_pending 253 END IF; -- did clear wire_pending flag for deposit confirmation 254 END IF; -- no more merchant_deposits wait for settlement 255 256 END LOOP; -- END curs LOOP 257 CLOSE curs; 258 END LOOP; -- END FOR loop 259 260 END $$;