diff options
Diffstat (limited to 'src/exchangedb/exchange_do_batch_coin_known.sql')
| -rw-r--r-- | src/exchangedb/exchange_do_batch_coin_known.sql | 469 |
1 files changed, 469 insertions, 0 deletions
diff --git a/src/exchangedb/exchange_do_batch_coin_known.sql b/src/exchangedb/exchange_do_batch_coin_known.sql new file mode 100644 index 000000000..db96cb08c --- /dev/null +++ b/src/exchangedb/exchange_do_batch_coin_known.sql @@ -0,0 +1,469 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +CREATE OR REPLACE FUNCTION exchange_do_batch4_known_coin( + IN in_coin_pub1 BYTEA, + IN in_denom_pub_hash1 BYTEA, + IN in_h_age_commitment1 BYTEA, + IN in_denom_sig1 BYTEA, + IN in_coin_pub2 BYTEA, + IN in_denom_pub_hash2 BYTEA, + IN in_h_age_commitment2 BYTEA, + IN in_denom_sig2 BYTEA, + IN in_coin_pub3 BYTEA, + IN in_denom_pub_hash3 BYTEA, + IN in_h_age_commitment3 BYTEA, + IN in_denom_sig3 BYTEA, + IN in_coin_pub4 BYTEA, + IN in_denom_pub_hash4 BYTEA, + IN in_h_age_commitment4 BYTEA, + IN in_denom_sig4 BYTEA, + OUT existed1 BOOLEAN, + OUT existed2 BOOLEAN, + OUT existed3 BOOLEAN, + OUT existed4 BOOLEAN, + OUT known_coin_id1 INT8, + OUT known_coin_id2 INT8, + OUT known_coin_id3 INT8, + OUT known_coin_id4 INT8, + OUT denom_pub_hash1 BYTEA, + OUT denom_pub_hash2 BYTEA, + OUT denom_pub_hash3 BYTEA, + OUT denom_pub_hash4 BYTEA, + OUT age_commitment_hash1 BYTEA, + OUT age_commitment_hash2 BYTEA, + OUT age_commitment_hash3 BYTEA, + OUT age_commitment_hash4 BYTEA) +LANGUAGE plpgsql +AS $$ +BEGIN +WITH dd AS ( +SELECT + denominations_serial, + coin + FROM denominations + WHERE denom_pub_hash + IN + (in_denom_pub_hash1, + in_denom_pub_hash2, + in_denom_pub_hash3, + in_denom_pub_hash4) + ),--dd + input_rows AS ( + VALUES + (in_coin_pub1, + in_denom_pub_hash1, + in_h_age_commitment1, + in_denom_sig1), + (in_coin_pub2, + in_denom_pub_hash2, + in_h_age_commitment2, + in_denom_sig2), + (in_coin_pub3, + in_denom_pub_hash3, + in_h_age_commitment3, + in_denom_sig3), + (in_coin_pub4, + in_denom_pub_hash4, + in_h_age_commitment4, + in_denom_sig4) + ),--ir + ins AS ( + INSERT INTO known_coins ( + coin_pub, + denominations_serial, + age_commitment_hash, + denom_sig, + remaining + ) + SELECT + ir.coin_pub, + dd.denominations_serial, + ir.age_commitment_hash, + ir.denom_sig, + dd.coin + FROM input_rows ir + JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + ON CONFLICT DO NOTHING + RETURNING known_coin_id + ),--kc + exists AS ( + SELECT + CASE + WHEN + ins.known_coin_id IS NOT NULL + THEN + FALSE + ELSE + TRUE + END AS existed, + ins.known_coin_id, + dd.denom_pub_hash, + kc.age_commitment_hash + FROM input_rows ir + LEFT JOIN ins + ON ins.coin_pub = ir.coin_pub + LEFT JOIN known_coins kc + ON kc.coin_pub = ir.coin_pub + LEFT JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + )--exists +SELECT + exists.existed AS existed1, + exists.known_coin_id AS known_coin_id1, + exists.denom_pub_hash AS denom_pub_hash1, + exists.age_commitment_hash AS age_commitment_hash1, + ( + SELECT exists.existed + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS existed2, + ( + SELECT exists.known_coin_id + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS known_coin_id2, + ( + SELECT exists.denom_pub_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS denom_pub_hash2, + ( + SELECT exists.age_commitment_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + )AS age_commitment_hash2, + ( + SELECT exists.existed + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash3 + ) AS existed3, + ( + SELECT exists.known_coin_id + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash3 + ) AS known_coin_id3, + ( + SELECT exists.denom_pub_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash3 + ) AS denom_pub_hash3, + ( + SELECT exists.age_commitment_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash3 + )AS age_commitment_hash3, + ( + SELECT exists.existed + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash4 + ) AS existed4, + ( + SELECT exists.known_coin_id + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash4 + ) AS known_coin_id4, + ( + SELECT exists.denom_pub_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash4 + ) AS denom_pub_hash4, + ( + SELECT exists.age_commitment_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash4 + )AS age_commitment_hash4 +FROM exists; + +RETURN; +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_batch2_known_coin( + IN in_coin_pub1 BYTEA, + IN in_denom_pub_hash1 BYTEA, + IN in_h_age_commitment1 BYTEA, + IN in_denom_sig1 BYTEA, + IN in_coin_pub2 BYTEA, + IN in_denom_pub_hash2 BYTEA, + IN in_h_age_commitment2 BYTEA, + IN in_denom_sig2 BYTEA, + OUT existed1 BOOLEAN, + OUT existed2 BOOLEAN, + OUT known_coin_id1 INT8, + OUT known_coin_id2 INT8, + OUT denom_pub_hash1 BYTEA, + OUT denom_pub_hash2 BYTEA, + OUT age_commitment_hash1 BYTEA, + OUT age_commitment_hash2 BYTEA) +LANGUAGE plpgsql +AS $$ +BEGIN +WITH dd AS ( +SELECT + denominations_serial, + coin + FROM denominations + WHERE denom_pub_hash + IN + (in_denom_pub_hash1, + in_denom_pub_hash2) + ),--dd + input_rows AS ( + VALUES + (in_coin_pub1, + in_denom_pub_hash1, + in_h_age_commitment1, + in_denom_sig1), + (in_coin_pub2, + in_denom_pub_hash2, + in_h_age_commitment2, + in_denom_sig2) + ),--ir + ins AS ( + INSERT INTO known_coins ( + coin_pub, + denominations_serial, + age_commitment_hash, + denom_sig, + remaining + ) + SELECT + ir.coin_pub, + dd.denominations_serial, + ir.age_commitment_hash, + ir.denom_sig, + dd.coin + FROM input_rows ir + JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + ON CONFLICT DO NOTHING + RETURNING known_coin_id + ),--kc + exists AS ( + SELECT + CASE + WHEN ins.known_coin_id IS NOT NULL + THEN + FALSE + ELSE + TRUE + END AS existed, + ins.known_coin_id, + dd.denom_pub_hash, + kc.age_commitment_hash + FROM input_rows ir + LEFT JOIN ins + ON ins.coin_pub = ir.coin_pub + LEFT JOIN known_coins kc + ON kc.coin_pub = ir.coin_pub + LEFT JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + )--exists +SELECT + exists.existed AS existed1, + exists.known_coin_id AS known_coin_id1, + exists.denom_pub_hash AS denom_pub_hash1, + exists.age_commitment_hash AS age_commitment_hash1, + ( + SELECT exists.existed + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS existed2, + ( + SELECT exists.known_coin_id + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS known_coin_id2, + ( + SELECT exists.denom_pub_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + ) AS denom_pub_hash2, + ( + SELECT exists.age_commitment_hash + FROM exists + WHERE exists.denom_pub_hash = in_denom_pub_hash2 + )AS age_commitment_hash2 +FROM exists; + +RETURN; +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_batch1_known_coin( + IN in_coin_pub1 BYTEA, + IN in_denom_pub_hash1 BYTEA, + IN in_h_age_commitment1 BYTEA, + IN in_denom_sig1 BYTEA, + OUT existed1 BOOLEAN, + OUT known_coin_id1 INT8, + OUT denom_pub_hash1 BYTEA, + OUT age_commitment_hash1 BYTEA) +LANGUAGE plpgsql +AS $$ +BEGIN +WITH dd AS ( +SELECT + denominations_serial, + coin + FROM denominations + WHERE denom_pub_hash + IN + (in_denom_pub_hash1, + in_denom_pub_hash2) + ),--dd + input_rows AS ( + VALUES + (in_coin_pub1, + in_denom_pub_hash1, + in_h_age_commitment1, + in_denom_sig1) + ),--ir + ins AS ( + INSERT INTO known_coins ( + coin_pub, + denominations_serial, + age_commitment_hash, + denom_sig, + remaining + ) + SELECT + ir.coin_pub, + dd.denominations_serial, + ir.age_commitment_hash, + ir.denom_sig, + dd.coin + FROM input_rows ir + JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + ON CONFLICT DO NOTHING + RETURNING known_coin_id + ),--kc + exists AS ( + SELECT + CASE + WHEN ins.known_coin_id IS NOT NULL + THEN + FALSE + ELSE + TRUE + END AS existed, + ins.known_coin_id, + dd.denom_pub_hash, + kc.age_commitment_hash + FROM input_rows ir + LEFT JOIN ins + ON ins.coin_pub = ir.coin_pub + LEFT JOIN known_coins kc + ON kc.coin_pub = ir.coin_pub + LEFT JOIN dd + ON dd.denom_pub_hash = ir.denom_pub_hash + )--exists +SELECT + exists.existed AS existed1, + exists.known_coin_id AS known_coin_id1, + exists.denom_pub_hash AS denom_pub_hash1, + exists.age_commitment_hash AS age_commitment_hash1 +FROM exists; + +RETURN; +END $$; + +/*** Experiment using a loop ***/ +/* +CREATE OR REPLACE FUNCTION exchange_do_batch2_known_coin( + IN in_coin_pub1 BYTEA, + IN in_denom_pub_hash1 TEXT, + IN in_h_age_commitment1 TEXT, + IN in_denom_sig1 TEXT, + IN in_coin_pub2 BYTEA, + IN in_denom_pub_hash2 TEXT, + IN in_h_age_commitment2 TEXT, + IN in_denom_sig2 TEXT, + OUT existed1 BOOLEAN, + OUT existed2 BOOLEAN, + OUT known_coin_id1 INT8, + OUT known_coin_id2 INT8, + OUT denom_pub_hash1 TEXT, + OUT denom_pub_hash2 TEXT, + OUT age_commitment_hash1 TEXT, + OUT age_commitment_hash2 TEXT) +LANGUAGE plpgsql +AS $$ +DECLARE + ins_values RECORD; +BEGIN + FOR i IN 1..2 LOOP + ins_values := ( + SELECT + in_coin_pub1 AS coin_pub, + in_denom_pub_hash1 AS denom_pub_hash, + in_h_age_commitment1 AS age_commitment_hash, + in_denom_sig1 AS denom_sig + WHERE i = 1 + UNION + SELECT + in_coin_pub2 AS coin_pub, + in_denom_pub_hash2 AS denom_pub_hash, + in_h_age_commitment2 AS age_commitment_hash, + in_denom_sig2 AS denom_sig + WHERE i = 2 + ); + WITH dd (denominations_serial, coin) AS ( + SELECT denominations_serial, coin + FROM denominations + WHERE denom_pub_hash = ins_values.denom_pub_hash + ), + input_rows(coin_pub) AS ( + VALUES (ins_values.coin_pub) + ), + ins AS ( + INSERT INTO known_coins ( + coin_pub, + denominations_serial, + age_commitment_hash, + denom_sig, + remaining + ) SELECT + input_rows.coin_pub, + dd.denominations_serial, + ins_values.age_commitment_hash, + ins_values.denom_sig, + coin + FROM dd + CROSS JOIN input_rows + ON CONFLICT DO NOTHING + RETURNING known_coin_id, denom_pub_hash + ) + SELECT + CASE i + WHEN 1 THEN + COALESCE(ins.known_coin_id, 0) <> 0 AS existed1, + ins.known_coin_id AS known_coin_id1, + ins.denom_pub_hash AS denom_pub_hash1, + ins.age_commitment_hash AS age_commitment_hash1 + WHEN 2 THEN + COALESCE(ins.known_coin_id, 0) <> 0 AS existed2, + ins.known_coin_id AS known_coin_id2, + ins.denom_pub_hash AS denom_pub_hash2, + ins.age_commitment_hash AS age_commitment_hash2 + END + FROM ins; + END LOOP; +END; +$$;*/ |