do_import_credits.sql (8850B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--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 17 DROP FUNCTION IF EXISTS exchange_do_import_credits; 18 CREATE FUNCTION exchange_do_import_credits( 19 -- All credits in one call come from one bank account and one HTTP 20 -- response, so the account and the reserve lifetimes are scalars. 21 IN in_exchange_account_name TEXT, 22 IN in_gc_date INT8, 23 IN in_reserve_expiration INT8, 24 -- Transfers into reserves. 25 IN ina_reserve_pub BYTEA[], 26 IN ina_wire_ref INT8[], 27 IN ina_credit taler_amount[], 28 IN ina_execution_date INT8[], 29 IN ina_wire_source_h_payto BYTEA[], 30 IN ina_h_normalized_payto BYTEA[], 31 IN ina_payto_uri TEXT[], 32 IN ina_notify TEXT[], 33 -- KYC authentication transfers. 34 IN ina_ka_account_pub BYTEA[], 35 IN ina_ka_wire_ref INT8[], 36 IN ina_ka_credit taler_amount[], 37 IN ina_ka_execution_date INT8[], 38 IN ina_ka_wire_source_h_payto BYTEA[], 39 IN ina_ka_h_normalized_payto BYTEA[], 40 IN ina_ka_payto_uri TEXT[], 41 IN ina_ka_notify TEXT[], 42 -- WAD transfers from other exchanges. 43 IN ina_wad_id BYTEA[], 44 IN ina_wad_origin_exchange_url TEXT[], 45 IN ina_wad_amount taler_amount[], 46 IN ina_wad_execution_date INT8[], 47 -- Which shard this batch belongs to, and how far it takes us. 48 IN in_job_name TEXT, 49 IN in_shard_start INT8, 50 IN in_shard_end INT8, 51 IN in_progress_row INT8, 52 IN in_lease_until INT8) 53 RETURNS TABLE (out_duplicate BOOLEAN) 54 LANGUAGE plpgsql 55 AS $$ 56 DECLARE 57 conflict BOOL; 58 dup BOOL; 59 uuid INT8; 60 i INT4; 61 my_is_wallet BOOL; 62 BEGIN 63 64 -- === transfers into reserves ============================================ 65 FOR i IN 1..COALESCE(array_length(ina_reserve_pub,1),0) 66 LOOP 67 my_is_wallet 68 = (LOWER (SUBSTRING (ina_payto_uri[i], 0, 23)) = 69 'payto://taler-reserve/') OR 70 (LOWER (SUBSTRING (ina_payto_uri[i], 0, 28)) = 71 'payto://taler-reserve-http/'); 72 INSERT INTO kyc_targets 73 (h_normalized_payto 74 ,is_wallet 75 ) VALUES ( 76 ina_h_normalized_payto[i] 77 ,my_is_wallet 78 ) 79 ON CONFLICT DO NOTHING; 80 INSERT INTO wire_targets 81 (wire_target_h_payto 82 ,h_normalized_payto 83 ,payto_uri 84 ) VALUES ( 85 ina_wire_source_h_payto[i] 86 ,ina_h_normalized_payto[i] 87 ,ina_payto_uri[i] 88 ) 89 ON CONFLICT DO NOTHING; 90 91 INSERT INTO reserves 92 (reserve_pub 93 ,current_balance 94 ,expiration_date 95 ,gc_date 96 ) VALUES ( 97 ina_reserve_pub[i] 98 ,ina_credit[i] 99 ,in_reserve_expiration 100 ,in_gc_date 101 ) 102 ON CONFLICT DO NOTHING 103 RETURNING reserve_uuid 104 INTO uuid; 105 conflict = NOT FOUND; 106 107 INSERT INTO reserves_in 108 (reserve_pub 109 ,wire_reference 110 ,credit 111 ,exchange_account_section 112 ,wire_source_h_payto 113 ,execution_date 114 ) VALUES ( 115 ina_reserve_pub[i] 116 ,ina_wire_ref[i] 117 ,ina_credit[i] 118 ,in_exchange_account_name 119 ,ina_wire_source_h_payto[i] 120 ,ina_execution_date[i] 121 ) 122 ON CONFLICT DO NOTHING; 123 124 IF NOT FOUND 125 THEN 126 IF conflict 127 THEN 128 dup = TRUE; 129 ELSE 130 dup = FALSE; 131 END IF; 132 ELSE 133 IF NOT conflict 134 THEN 135 EXECUTE FORMAT ( 136 'NOTIFY %s' 137 ,ina_notify[i]); 138 END IF; 139 dup = FALSE; 140 END IF; 141 142 IF (conflict AND NOT dup) 143 THEN 144 -- The reserve already existed, so the INSERT INTO reserves above did 145 -- nothing and left the transfer uncredited. Add it to the balance the 146 -- reserve already has. 147 -- 148 -- Because reserves_in.reserve_pub is that table's primary key, getting 149 -- here at all means the reserve had no reserves_in row: a reserve that 150 -- exists without ever having been wired to, i.e. one created by a purse 151 -- merge. The predecessor of this branch tried to INSERT that row a 152 -- second time and treated the resulting conflict as "already imported", 153 -- so the credit was recorded in reserves_in and then never added to 154 -- current_balance. 155 UPDATE reserves rs 156 SET 157 current_balance.frac = (rs.current_balance).frac+(ina_credit[i]).frac 158 - CASE 159 WHEN (rs.current_balance).frac + (ina_credit[i]).frac >= 100000000 160 THEN 100000000 161 ELSE 0 162 END 163 ,current_balance.val = (rs.current_balance).val+(ina_credit[i]).val 164 + CASE 165 WHEN (rs.current_balance).frac + (ina_credit[i]).frac >= 100000000 166 THEN 1 167 ELSE 0 168 END 169 ,expiration_date=GREATEST(expiration_date,in_reserve_expiration) 170 ,gc_date=GREATEST(gc_date,in_gc_date) 171 WHERE reserve_pub=ina_reserve_pub[i]; 172 EXECUTE FORMAT ( 173 'NOTIFY %s' 174 ,ina_notify[i]); 175 END IF; 176 177 out_duplicate = dup; 178 RETURN NEXT; 179 END LOOP; 180 181 -- === KYC authentication transfers ======================================= 182 FOR i IN 1..COALESCE(array_length(ina_ka_account_pub,1),0) 183 LOOP 184 INSERT INTO kycauths_in 185 (account_pub 186 ,wire_reference 187 ,credit 188 ,wire_source_h_payto 189 ,exchange_account_section 190 ,execution_date 191 ) VALUES ( 192 ina_ka_account_pub[i] 193 ,ina_ka_wire_ref[i] 194 ,ina_ka_credit[i] 195 ,ina_ka_wire_source_h_payto[i] 196 ,in_exchange_account_name 197 ,ina_ka_execution_date[i] 198 ) 199 ON CONFLICT DO NOTHING; 200 201 IF NOT FOUND 202 THEN 203 -- presumably already done 204 CONTINUE; 205 END IF; 206 207 UPDATE kyc_targets 208 SET target_pub=ina_ka_account_pub[i] 209 WHERE h_normalized_payto=ina_ka_h_normalized_payto[i]; 210 211 IF NOT FOUND 212 THEN 213 -- First time we see this account, setup everything. 214 my_is_wallet 215 = (LOWER (SUBSTRING (ina_ka_payto_uri[i], 0, 23)) = 216 'payto://taler-reserve/') OR 217 (LOWER (SUBSTRING (ina_ka_payto_uri[i], 0, 28)) = 218 'payto://taler-reserve-http/'); 219 INSERT INTO kyc_targets 220 (h_normalized_payto 221 ,is_wallet 222 ,target_pub 223 ) VALUES ( 224 ina_ka_h_normalized_payto[i] 225 ,my_is_wallet 226 ,ina_ka_account_pub[i]); 227 INSERT INTO wire_targets 228 (wire_target_h_payto 229 ,h_normalized_payto 230 ,payto_uri 231 ) VALUES ( 232 ina_ka_wire_source_h_payto[i] 233 ,ina_ka_h_normalized_payto[i] 234 ,ina_ka_payto_uri[i]); 235 END IF; 236 237 EXECUTE FORMAT ( 238 'NOTIFY %s' 239 ,ina_ka_notify[i]); 240 END LOOP; 241 242 -- === WAD transfers ====================================================== 243 FOR i IN 1..COALESCE(array_length(ina_wad_id,1),0) 244 LOOP 245 -- ON CONFLICT DO NOTHING is what the per-row INSERT this replaces 246 -- effectively did: it let the unique violation through to libgnunetpq, 247 -- which reports 23505 as "no results" and the caller shrugged it off. 248 -- Inside a function an unhandled violation would take the whole batch 249 -- down instead, and re-importing a shard has to stay harmless. 250 INSERT INTO wads_in 251 (wad_id 252 ,origin_exchange_url 253 ,amount 254 ,arrival_time 255 ) VALUES ( 256 ina_wad_id[i] 257 ,ina_wad_origin_exchange_url[i] 258 ,ina_wad_amount[i] 259 ,ina_wad_execution_date[i] 260 ) 261 ON CONFLICT DO NOTHING; 262 END LOOP; 263 264 -- === shard bookkeeping ================================================== 265 -- Deliberately part of this statement rather than a follow-up transaction: 266 -- the imports above and the record of how far we got have to become 267 -- visible together. Marking the shard completed is the same update, so 268 -- finishing a shard costs nothing extra either. 269 UPDATE work_shards 270 SET progress_row=GREATEST(progress_row,in_progress_row) 271 ,completed=(GREATEST(progress_row,in_progress_row) >= end_row) 272 ,last_attempt=in_lease_until 273 WHERE job_name=in_job_name 274 AND start_row=in_shard_start 275 AND end_row=in_shard_end; 276 277 RETURN; 278 END $$; 279 280 COMMENT ON FUNCTION exchange_do_import_credits 281 IS 'Imports one bank credit history response (reserve, KYC authentication and WAD transfers alike) and advances the work shard it belongs to, all in one statement. Returns one row per entry of ina_reserve_pub, in order, saying whether that transfer had already been imported.';