exchange_do_batch_coin_known.sql (11805B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 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 CREATE OR REPLACE FUNCTION exchange_do_batch4_known_coin( 18 IN in_coin_pub1 BYTEA, 19 IN in_denom_pub_hash1 BYTEA, 20 IN in_h_age_commitment1 BYTEA, 21 IN in_denom_sig1 BYTEA, 22 IN in_coin_pub2 BYTEA, 23 IN in_denom_pub_hash2 BYTEA, 24 IN in_h_age_commitment2 BYTEA, 25 IN in_denom_sig2 BYTEA, 26 IN in_coin_pub3 BYTEA, 27 IN in_denom_pub_hash3 BYTEA, 28 IN in_h_age_commitment3 BYTEA, 29 IN in_denom_sig3 BYTEA, 30 IN in_coin_pub4 BYTEA, 31 IN in_denom_pub_hash4 BYTEA, 32 IN in_h_age_commitment4 BYTEA, 33 IN in_denom_sig4 BYTEA, 34 OUT existed1 BOOLEAN, 35 OUT existed2 BOOLEAN, 36 OUT existed3 BOOLEAN, 37 OUT existed4 BOOLEAN, 38 OUT known_coin_id1 INT8, 39 OUT known_coin_id2 INT8, 40 OUT known_coin_id3 INT8, 41 OUT known_coin_id4 INT8, 42 OUT denom_pub_hash1 BYTEA, 43 OUT denom_pub_hash2 BYTEA, 44 OUT denom_pub_hash3 BYTEA, 45 OUT denom_pub_hash4 BYTEA, 46 OUT age_commitment_hash1 BYTEA, 47 OUT age_commitment_hash2 BYTEA, 48 OUT age_commitment_hash3 BYTEA, 49 OUT age_commitment_hash4 BYTEA) 50 LANGUAGE plpgsql 51 AS $$ 52 BEGIN 53 WITH dd AS ( 54 SELECT 55 denominations_serial, 56 coin 57 FROM denominations 58 WHERE denom_pub_hash 59 IN 60 (in_denom_pub_hash1, 61 in_denom_pub_hash2, 62 in_denom_pub_hash3, 63 in_denom_pub_hash4) 64 ),--dd 65 input_rows AS ( 66 VALUES 67 (in_coin_pub1, 68 in_denom_pub_hash1, 69 in_h_age_commitment1, 70 in_denom_sig1), 71 (in_coin_pub2, 72 in_denom_pub_hash2, 73 in_h_age_commitment2, 74 in_denom_sig2), 75 (in_coin_pub3, 76 in_denom_pub_hash3, 77 in_h_age_commitment3, 78 in_denom_sig3), 79 (in_coin_pub4, 80 in_denom_pub_hash4, 81 in_h_age_commitment4, 82 in_denom_sig4) 83 ),--ir 84 ins AS ( 85 INSERT INTO known_coins ( 86 coin_pub, 87 denominations_serial, 88 age_commitment_hash, 89 denom_sig, 90 remaining 91 ) 92 SELECT 93 ir.coin_pub, 94 dd.denominations_serial, 95 ir.age_commitment_hash, 96 ir.denom_sig, 97 dd.coin 98 FROM input_rows ir 99 JOIN dd 100 ON dd.denom_pub_hash = ir.denom_pub_hash 101 ON CONFLICT DO NOTHING 102 RETURNING known_coin_id 103 ),--kc 104 exists AS ( 105 SELECT 106 CASE 107 WHEN 108 ins.known_coin_id IS NOT NULL 109 THEN 110 FALSE 111 ELSE 112 TRUE 113 END AS existed, 114 ins.known_coin_id, 115 dd.denom_pub_hash, 116 kc.age_commitment_hash 117 FROM input_rows ir 118 LEFT JOIN ins 119 ON ins.coin_pub = ir.coin_pub 120 LEFT JOIN known_coins kc 121 ON kc.coin_pub = ir.coin_pub 122 LEFT JOIN dd 123 ON dd.denom_pub_hash = ir.denom_pub_hash 124 )--exists 125 SELECT 126 exists.existed AS existed1, 127 exists.known_coin_id AS known_coin_id1, 128 exists.denom_pub_hash AS denom_pub_hash1, 129 exists.age_commitment_hash AS age_commitment_hash1, 130 ( 131 SELECT exists.existed 132 FROM exists 133 WHERE exists.denom_pub_hash = in_denom_pub_hash2 134 ) AS existed2, 135 ( 136 SELECT exists.known_coin_id 137 FROM exists 138 WHERE exists.denom_pub_hash = in_denom_pub_hash2 139 ) AS known_coin_id2, 140 ( 141 SELECT exists.denom_pub_hash 142 FROM exists 143 WHERE exists.denom_pub_hash = in_denom_pub_hash2 144 ) AS denom_pub_hash2, 145 ( 146 SELECT exists.age_commitment_hash 147 FROM exists 148 WHERE exists.denom_pub_hash = in_denom_pub_hash2 149 )AS age_commitment_hash2, 150 ( 151 SELECT exists.existed 152 FROM exists 153 WHERE exists.denom_pub_hash = in_denom_pub_hash3 154 ) AS existed3, 155 ( 156 SELECT exists.known_coin_id 157 FROM exists 158 WHERE exists.denom_pub_hash = in_denom_pub_hash3 159 ) AS known_coin_id3, 160 ( 161 SELECT exists.denom_pub_hash 162 FROM exists 163 WHERE exists.denom_pub_hash = in_denom_pub_hash3 164 ) AS denom_pub_hash3, 165 ( 166 SELECT exists.age_commitment_hash 167 FROM exists 168 WHERE exists.denom_pub_hash = in_denom_pub_hash3 169 )AS age_commitment_hash3, 170 ( 171 SELECT exists.existed 172 FROM exists 173 WHERE exists.denom_pub_hash = in_denom_pub_hash4 174 ) AS existed4, 175 ( 176 SELECT exists.known_coin_id 177 FROM exists 178 WHERE exists.denom_pub_hash = in_denom_pub_hash4 179 ) AS known_coin_id4, 180 ( 181 SELECT exists.denom_pub_hash 182 FROM exists 183 WHERE exists.denom_pub_hash = in_denom_pub_hash4 184 ) AS denom_pub_hash4, 185 ( 186 SELECT exists.age_commitment_hash 187 FROM exists 188 WHERE exists.denom_pub_hash = in_denom_pub_hash4 189 )AS age_commitment_hash4 190 FROM exists; 191 192 RETURN; 193 END $$; 194 195 196 CREATE OR REPLACE FUNCTION exchange_do_batch2_known_coin( 197 IN in_coin_pub1 BYTEA, 198 IN in_denom_pub_hash1 BYTEA, 199 IN in_h_age_commitment1 BYTEA, 200 IN in_denom_sig1 BYTEA, 201 IN in_coin_pub2 BYTEA, 202 IN in_denom_pub_hash2 BYTEA, 203 IN in_h_age_commitment2 BYTEA, 204 IN in_denom_sig2 BYTEA, 205 OUT existed1 BOOLEAN, 206 OUT existed2 BOOLEAN, 207 OUT known_coin_id1 INT8, 208 OUT known_coin_id2 INT8, 209 OUT denom_pub_hash1 BYTEA, 210 OUT denom_pub_hash2 BYTEA, 211 OUT age_commitment_hash1 BYTEA, 212 OUT age_commitment_hash2 BYTEA) 213 LANGUAGE plpgsql 214 AS $$ 215 BEGIN 216 WITH dd AS ( 217 SELECT 218 denominations_serial, 219 coin 220 FROM denominations 221 WHERE denom_pub_hash 222 IN 223 (in_denom_pub_hash1, 224 in_denom_pub_hash2) 225 ),--dd 226 input_rows AS ( 227 VALUES 228 (in_coin_pub1, 229 in_denom_pub_hash1, 230 in_h_age_commitment1, 231 in_denom_sig1), 232 (in_coin_pub2, 233 in_denom_pub_hash2, 234 in_h_age_commitment2, 235 in_denom_sig2) 236 ),--ir 237 ins AS ( 238 INSERT INTO known_coins ( 239 coin_pub, 240 denominations_serial, 241 age_commitment_hash, 242 denom_sig, 243 remaining 244 ) 245 SELECT 246 ir.coin_pub, 247 dd.denominations_serial, 248 ir.age_commitment_hash, 249 ir.denom_sig, 250 dd.coin 251 FROM input_rows ir 252 JOIN dd 253 ON dd.denom_pub_hash = ir.denom_pub_hash 254 ON CONFLICT DO NOTHING 255 RETURNING known_coin_id 256 ),--kc 257 exists AS ( 258 SELECT 259 CASE 260 WHEN ins.known_coin_id IS NOT NULL 261 THEN 262 FALSE 263 ELSE 264 TRUE 265 END AS existed, 266 ins.known_coin_id, 267 dd.denom_pub_hash, 268 kc.age_commitment_hash 269 FROM input_rows ir 270 LEFT JOIN ins 271 ON ins.coin_pub = ir.coin_pub 272 LEFT JOIN known_coins kc 273 ON kc.coin_pub = ir.coin_pub 274 LEFT JOIN dd 275 ON dd.denom_pub_hash = ir.denom_pub_hash 276 )--exists 277 SELECT 278 exists.existed AS existed1, 279 exists.known_coin_id AS known_coin_id1, 280 exists.denom_pub_hash AS denom_pub_hash1, 281 exists.age_commitment_hash AS age_commitment_hash1, 282 ( 283 SELECT exists.existed 284 FROM exists 285 WHERE exists.denom_pub_hash = in_denom_pub_hash2 286 ) AS existed2, 287 ( 288 SELECT exists.known_coin_id 289 FROM exists 290 WHERE exists.denom_pub_hash = in_denom_pub_hash2 291 ) AS known_coin_id2, 292 ( 293 SELECT exists.denom_pub_hash 294 FROM exists 295 WHERE exists.denom_pub_hash = in_denom_pub_hash2 296 ) AS denom_pub_hash2, 297 ( 298 SELECT exists.age_commitment_hash 299 FROM exists 300 WHERE exists.denom_pub_hash = in_denom_pub_hash2 301 )AS age_commitment_hash2 302 FROM exists; 303 304 RETURN; 305 END $$; 306 307 308 CREATE OR REPLACE FUNCTION exchange_do_batch1_known_coin( 309 IN in_coin_pub1 BYTEA, 310 IN in_denom_pub_hash1 BYTEA, 311 IN in_h_age_commitment1 BYTEA, 312 IN in_denom_sig1 BYTEA, 313 OUT existed1 BOOLEAN, 314 OUT known_coin_id1 INT8, 315 OUT denom_pub_hash1 BYTEA, 316 OUT age_commitment_hash1 BYTEA) 317 LANGUAGE plpgsql 318 AS $$ 319 BEGIN 320 WITH dd AS ( 321 SELECT 322 denominations_serial, 323 coin 324 FROM denominations 325 WHERE denom_pub_hash 326 IN 327 (in_denom_pub_hash1, 328 in_denom_pub_hash2) 329 ),--dd 330 input_rows AS ( 331 VALUES 332 (in_coin_pub1, 333 in_denom_pub_hash1, 334 in_h_age_commitment1, 335 in_denom_sig1) 336 ),--ir 337 ins AS ( 338 INSERT INTO known_coins ( 339 coin_pub, 340 denominations_serial, 341 age_commitment_hash, 342 denom_sig, 343 remaining 344 ) 345 SELECT 346 ir.coin_pub, 347 dd.denominations_serial, 348 ir.age_commitment_hash, 349 ir.denom_sig, 350 dd.coin 351 FROM input_rows ir 352 JOIN dd 353 ON dd.denom_pub_hash = ir.denom_pub_hash 354 ON CONFLICT DO NOTHING 355 RETURNING known_coin_id 356 ),--kc 357 exists AS ( 358 SELECT 359 CASE 360 WHEN ins.known_coin_id IS NOT NULL 361 THEN 362 FALSE 363 ELSE 364 TRUE 365 END AS existed, 366 ins.known_coin_id, 367 dd.denom_pub_hash, 368 kc.age_commitment_hash 369 FROM input_rows ir 370 LEFT JOIN ins 371 ON ins.coin_pub = ir.coin_pub 372 LEFT JOIN known_coins kc 373 ON kc.coin_pub = ir.coin_pub 374 LEFT JOIN dd 375 ON dd.denom_pub_hash = ir.denom_pub_hash 376 )--exists 377 SELECT 378 exists.existed AS existed1, 379 exists.known_coin_id AS known_coin_id1, 380 exists.denom_pub_hash AS denom_pub_hash1, 381 exists.age_commitment_hash AS age_commitment_hash1 382 FROM exists; 383 384 RETURN; 385 END $$; 386 387 /*** Experiment using a loop ***/ 388 /* 389 CREATE OR REPLACE FUNCTION exchange_do_batch2_known_coin( 390 IN in_coin_pub1 BYTEA, 391 IN in_denom_pub_hash1 TEXT, 392 IN in_h_age_commitment1 TEXT, 393 IN in_denom_sig1 TEXT, 394 IN in_coin_pub2 BYTEA, 395 IN in_denom_pub_hash2 TEXT, 396 IN in_h_age_commitment2 TEXT, 397 IN in_denom_sig2 TEXT, 398 OUT existed1 BOOLEAN, 399 OUT existed2 BOOLEAN, 400 OUT known_coin_id1 INT8, 401 OUT known_coin_id2 INT8, 402 OUT denom_pub_hash1 TEXT, 403 OUT denom_pub_hash2 TEXT, 404 OUT age_commitment_hash1 TEXT, 405 OUT age_commitment_hash2 TEXT) 406 LANGUAGE plpgsql 407 AS $$ 408 DECLARE 409 ins_values RECORD; 410 BEGIN 411 FOR i IN 1..2 LOOP 412 ins_values := ( 413 SELECT 414 in_coin_pub1 AS coin_pub, 415 in_denom_pub_hash1 AS denom_pub_hash, 416 in_h_age_commitment1 AS age_commitment_hash, 417 in_denom_sig1 AS denom_sig 418 WHERE i = 1 419 UNION 420 SELECT 421 in_coin_pub2 AS coin_pub, 422 in_denom_pub_hash2 AS denom_pub_hash, 423 in_h_age_commitment2 AS age_commitment_hash, 424 in_denom_sig2 AS denom_sig 425 WHERE i = 2 426 ); 427 WITH dd (denominations_serial, coin) AS ( 428 SELECT denominations_serial, coin 429 FROM denominations 430 WHERE denom_pub_hash = ins_values.denom_pub_hash 431 ), 432 input_rows(coin_pub) AS ( 433 VALUES (ins_values.coin_pub) 434 ), 435 ins AS ( 436 INSERT INTO known_coins ( 437 coin_pub, 438 denominations_serial, 439 age_commitment_hash, 440 denom_sig, 441 remaining 442 ) SELECT 443 input_rows.coin_pub, 444 dd.denominations_serial, 445 ins_values.age_commitment_hash, 446 ins_values.denom_sig, 447 coin 448 FROM dd 449 CROSS JOIN input_rows 450 ON CONFLICT DO NOTHING 451 RETURNING known_coin_id, denom_pub_hash 452 ) 453 SELECT 454 CASE i 455 WHEN 1 THEN 456 COALESCE(ins.known_coin_id, 0) <> 0 AS existed1, 457 ins.known_coin_id AS known_coin_id1, 458 ins.denom_pub_hash AS denom_pub_hash1, 459 ins.age_commitment_hash AS age_commitment_hash1 460 WHEN 2 THEN 461 COALESCE(ins.known_coin_id, 0) <> 0 AS existed2, 462 ins.known_coin_id AS known_coin_id2, 463 ins.denom_pub_hash AS denom_pub_hash2, 464 ins.age_commitment_hash AS age_commitment_hash2 465 END 466 FROM ins; 467 END LOOP; 468 END; 469 $$;*/