insert_transfer_details.sql (8918B)
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_merchant_serial INT8, 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 merchant.taler_amount_currency, 28 IN in_wire_fee merchant.taler_amount_currency, 29 IN ina_coin_values merchant.taler_amount_currency[], 30 IN ina_deposit_fees merchant.taler_amount_currency[], 31 IN ina_coin_pubs BYTEA[], 32 IN ina_contract_terms BYTEA[], 33 OUT out_no_account BOOL, 34 OUT out_no_exchange BOOL, 35 OUT out_duplicate BOOL, 36 OUT out_conflict BOOL, 37 -- IDs of ALL orders that became fully wired by this transfer; 38 -- one aggregated transfer can settle many orders. 39 OUT out_order_ids TEXT[]) 40 LANGUAGE plpgsql 41 AS $$ 42 DECLARE 43 my_signkey_serial INT8; 44 my_expected_credit_serial INT8; 45 my_affected_orders RECORD; 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 merchant.taler_amount_currency; 59 ini_deposit_fee merchant.taler_amount_currency; 60 BEGIN 61 62 out_order_ids=ARRAY[]::TEXT[]; 63 64 -- Determine account that was credited. 65 SELECT expected_credit_serial 66 INTO my_expected_credit_serial 67 FROM merchant_expected_transfers 68 WHERE exchange_url=in_exchange_url 69 AND wtid=in_wtid 70 AND account_serial= 71 (SELECT account_serial 72 FROM merchant_accounts 73 WHERE payto_uri=in_payto_uri); 74 75 IF NOT FOUND 76 THEN 77 out_no_account=TRUE; 78 out_no_exchange=FALSE; 79 out_duplicate=FALSE; 80 out_conflict=FALSE; 81 RETURN; 82 END IF; 83 out_no_account=FALSE; 84 85 -- Find exchange sign key 86 SELECT signkey_serial 87 INTO my_signkey_serial 88 FROM merchant.merchant_exchange_signing_keys 89 WHERE exchange_pub=in_exchange_pub 90 ORDER BY start_date DESC 91 LIMIT 1; 92 93 IF NOT FOUND 94 THEN 95 out_no_exchange=TRUE; 96 out_conflict=FALSE; 97 out_duplicate=FALSE; 98 RETURN; 99 END IF; 100 out_no_exchange=FALSE; 101 102 -- Add signature first, check for idempotent request 103 INSERT INTO merchant_transfer_signatures 104 (expected_credit_serial 105 ,signkey_serial 106 ,credit_amount 107 ,wire_fee 108 ,execution_time 109 ,exchange_sig) 110 VALUES 111 (my_expected_credit_serial 112 ,my_signkey_serial 113 ,in_total_amount 114 ,in_wire_fee 115 ,in_execution_time 116 ,in_exchange_sig) 117 ON CONFLICT DO NOTHING; 118 119 IF NOT FOUND 120 THEN 121 PERFORM 1 122 FROM merchant_transfer_signatures 123 WHERE expected_credit_serial=my_expected_credit_serial 124 AND signkey_serial=my_signkey_serial 125 AND credit_amount=in_total_amount 126 AND wire_fee=in_wire_fee 127 AND execution_time=in_execution_time 128 AND exchange_sig=in_exchange_sig; 129 IF FOUND 130 THEN 131 -- duplicate case 132 out_duplicate=TRUE; 133 out_conflict=FALSE; 134 RETURN; 135 END IF; 136 -- conflict case 137 out_duplicate=FALSE; 138 out_conflict=TRUE; 139 RETURN; 140 END IF; 141 142 out_duplicate=FALSE; 143 out_conflict=FALSE; 144 145 146 -- Note: the COALESCE is required, the exchange is allowed to 147 -- return an empty list of deposits for a wire transfer; without 148 -- it plpgsql raises 'upper bound of FOR loop cannot be null'. 149 FOR i IN 1..COALESCE(array_length(ina_coin_pubs,1),0) 150 LOOP 151 ini_coin_value=ina_coin_values[i]; 152 ini_deposit_fee=ina_deposit_fees[i]; 153 ini_coin_pub=ina_coin_pubs[i]; 154 ini_contract_term=ina_contract_terms[i]; 155 156 INSERT INTO merchant_expected_transfer_to_coin 157 (deposit_serial 158 ,expected_credit_serial 159 ,offset_in_exchange_list 160 ,exchange_deposit_value 161 ,exchange_deposit_fee) 162 SELECT 163 dep.deposit_serial 164 ,my_expected_credit_serial 165 ,i 166 ,ini_coin_value 167 ,ini_deposit_fee 168 FROM merchant_deposits dep 169 JOIN merchant_deposit_confirmations dcon 170 USING (deposit_confirmation_serial) 171 JOIN merchant_contract_terms cterm 172 USING (order_serial) 173 WHERE dep.coin_pub=ini_coin_pub 174 AND cterm.h_contract_terms=ini_contract_term 175 -- The exchange may list the same coin more than once in one 176 -- response, and a coin belongs to at most one wire transfer 177 -- (merchant_expected_transfer_to_coin is UNIQUE on 178 -- deposit_serial): keep the first association we saw instead of 179 -- failing the entire transaction. 180 ON CONFLICT (deposit_serial) DO NOTHING; 181 182 RAISE NOTICE 'iterating over affected orders'; 183 OPEN curs (arg_coin_pub:=ini_coin_pub); 184 LOOP 185 FETCH NEXT FROM curs INTO my_affected_orders; 186 EXIT WHEN NOT FOUND; 187 188 RAISE NOTICE 'checking affected order for completion'; 189 190 my_decose=my_affected_orders.deposit_confirmation_serial; 191 192 -- A deposit that failed permanently (say because the exchange 193 -- wired the money to an account we do not know, EC 2558) has 194 -- settlement_retry_needed=FALSE and a settlement_wtid, but is 195 -- NOT settled: it must not make the order count as wired. 196 PERFORM FROM merchant_deposits md 197 WHERE md.deposit_confirmation_serial=my_decose 198 AND (settlement_retry_needed 199 OR (settlement_wtid IS NULL) 200 OR (COALESCE(settlement_last_ec,0) <> 0) ); 201 IF NOT FOUND 202 THEN 203 -- must be all done, clear flag 204 UPDATE merchant_deposit_confirmations 205 SET wire_pending=FALSE 206 WHERE (deposit_confirmation_serial=my_decose); 207 208 IF FOUND 209 THEN 210 -- Also update contract terms, if all (other) associated 211 -- deposit_confirmations are also done. 212 213 RAISE NOTICE 'checking affected contract for completion'; 214 PERFORM FROM merchant_deposit_confirmations mdc 215 WHERE mdc.wire_pending 216 AND mdc.order_serial=my_affected_orders.order_serial; 217 IF NOT FOUND 218 THEN 219 220 UPDATE merchant_contract_terms 221 SET wired=TRUE 222 WHERE (order_serial=my_affected_orders.order_serial); 223 224 -- Select merchant_serial and order_id for webhook 225 SELECT order_id 226 INTO my_order_id 227 FROM merchant_contract_terms 228 WHERE order_serial=my_affected_orders.order_serial; 229 -- Remember ALL orders that completed, not just the last 230 -- one: an aggregated transfer settles many orders and 231 -- every one of them needs an ORDER_STATUS_CHANGED event. 232 IF NOT (my_order_id = ANY(out_order_ids)) 233 THEN 234 out_order_ids = array_append(out_order_ids, my_order_id); 235 END IF; 236 -- Insert pending webhook if it exists 237 INSERT INTO merchant.merchant_pending_webhooks 238 (merchant_serial 239 ,webhook_serial 240 ,url 241 ,http_method 242 ,header 243 ,body) 244 SELECT in_merchant_serial 245 ,mw.webhook_serial 246 ,mw.url 247 ,mw.http_method 248 ,merchant.replace_placeholder( 249 merchant.replace_placeholder(mw.header_template, 250 'order_id', 251 my_order_id), 252 'wtid', 253 encode(in_wtid, 'hex') 254 )::TEXT 255 ,merchant.replace_placeholder( 256 merchant.replace_placeholder(mw.body_template, 257 'order_id', 258 my_order_id), 259 'wtid', 260 encode(in_wtid, 'hex') 261 )::TEXT 262 FROM merchant_webhook mw 263 WHERE mw.event_type = 'order_settled'; 264 IF FOUND 265 THEN 266 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 267 END IF; -- found pending order_settled webhooks to deliver 268 END IF; -- no more merchant_deposits waiting for wire_pending 269 END IF; -- did clear wire_pending flag for deposit confirmation 270 END IF; -- no more merchant_deposits wait for settlement 271 272 END LOOP; -- END curs LOOP 273 CLOSE curs; 274 END LOOP; -- END FOR loop 275 276 END $$;