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