From 87198f124c989d014adc9a2bae5098cf80555d62 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Tue, 6 Dec 2022 13:29:23 +0100 Subject: refactor procedures.sql --- src/exchangedb/procedures.sql | 2641 ----------------------------------------- 1 file changed, 2641 deletions(-) delete mode 100644 src/exchangedb/procedures.sql (limited to 'src/exchangedb/procedures.sql') diff --git a/src/exchangedb/procedures.sql b/src/exchangedb/procedures.sql deleted file mode 100644 index a869d4739..000000000 --- a/src/exchangedb/procedures.sql +++ /dev/null @@ -1,2641 +0,0 @@ --- --- 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 --- - -BEGIN; - -SET search_path TO exchange; - - -CREATE OR REPLACE FUNCTION exchange_do_withdraw( - IN cs_nonce BYTEA, - IN amount_val INT8, - IN amount_frac INT4, - IN h_denom_pub BYTEA, - IN rpub BYTEA, - IN reserve_sig BYTEA, - IN h_coin_envelope BYTEA, - IN denom_sig BYTEA, - IN now INT8, - IN min_reserve_gc INT8, - OUT reserve_found BOOLEAN, - OUT balance_ok BOOLEAN, - OUT nonce_ok BOOLEAN, - OUT ruuid INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - reserve_gc INT8; -DECLARE - denom_serial INT8; -DECLARE - reserve_val INT8; -DECLARE - reserve_frac INT4; -BEGIN --- Shards: reserves by reserve_pub (SELECT) --- reserves_out (INSERT, with CONFLICT detection) by wih --- reserves by reserve_pub (UPDATE) --- reserves_in by reserve_pub (SELECT) --- wire_targets by wire_target_h_payto - -SELECT denominations_serial - INTO denom_serial - FROM exchange.denominations - WHERE denom_pub_hash=h_denom_pub; - -IF NOT FOUND -THEN - -- denomination unknown, should be impossible! - reserve_found=FALSE; - balance_ok=FALSE; - ruuid=0; - ASSERT false, 'denomination unknown'; - RETURN; -END IF; - - -SELECT - current_balance_val - ,current_balance_frac - ,gc_date - ,reserve_uuid - INTO - reserve_val - ,reserve_frac - ,reserve_gc - ,ruuid - FROM exchange.reserves - WHERE reserves.reserve_pub=rpub; - -IF NOT FOUND -THEN - -- reserve unknown - reserve_found=FALSE; - balance_ok=FALSE; - nonce_ok=TRUE; - ruuid=2; - RETURN; -END IF; - --- We optimistically insert, and then on conflict declare --- the query successful due to idempotency. -INSERT INTO exchange.reserves_out - (h_blind_ev - ,denominations_serial - ,denom_sig - ,reserve_uuid - ,reserve_sig - ,execution_date - ,amount_with_fee_val - ,amount_with_fee_frac) -VALUES - (h_coin_envelope - ,denom_serial - ,denom_sig - ,ruuid - ,reserve_sig - ,now - ,amount_val - ,amount_frac) -ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- idempotent query, all constraints must be satisfied - reserve_found=TRUE; - balance_ok=TRUE; - nonce_ok=TRUE; - RETURN; -END IF; - --- Check reserve balance is sufficient. -IF (reserve_val > amount_val) -THEN - IF (reserve_frac >= amount_frac) - THEN - reserve_val=reserve_val - amount_val; - reserve_frac=reserve_frac - amount_frac; - ELSE - reserve_val=reserve_val - amount_val - 1; - reserve_frac=reserve_frac + 100000000 - amount_frac; - END IF; -ELSE - IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac) - THEN - reserve_val=0; - reserve_frac=reserve_frac - amount_frac; - ELSE - reserve_found=TRUE; - nonce_ok=TRUE; -- we do not really know - balance_ok=FALSE; - RETURN; - END IF; -END IF; - --- Calculate new expiration dates. -min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc); - --- Update reserve balance. -UPDATE reserves SET - gc_date=min_reserve_gc - ,current_balance_val=reserve_val - ,current_balance_frac=reserve_frac -WHERE - reserves.reserve_pub=rpub; - -reserve_found=TRUE; -balance_ok=TRUE; - - - --- Special actions needed for a CS withdraw? -IF NOT NULL cs_nonce -THEN - -- Cache CS signature to prevent replays in the future - -- (and check if cached signature exists at the same time). - INSERT INTO exchange.cs_nonce_locks - (nonce - ,max_denomination_serial - ,op_hash) - VALUES - (cs_nonce - ,denom_serial - ,h_coin_envelope) - ON CONFLICT DO NOTHING; - - IF NOT FOUND - THEN - -- See if the existing entry is identical. - SELECT 1 - FROM exchange.cs_nonce_locks - WHERE nonce=cs_nonce - AND op_hash=h_coin_envelope; - IF NOT FOUND - THEN - reserve_found=FALSE; - balance_ok=FALSE; - nonce_ok=FALSE; - RETURN; - END IF; - END IF; -ELSE - nonce_ok=TRUE; -- no nonce, hence OK! -END IF; - -END $$; - - -COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA, BYTEA, BYTEA, BYTEA, INT8, INT8) - IS 'Checks whether the reserve has sufficient balance for a withdraw operation (or the request is repeated and was previously approved) and if so updates the database with the result'; - - - - -CREATE OR REPLACE FUNCTION exchange_do_batch_withdraw( - IN amount_val INT8, - IN amount_frac INT4, - IN rpub BYTEA, - IN now INT8, - IN min_reserve_gc INT8, - OUT reserve_found BOOLEAN, - OUT balance_ok BOOLEAN, - OUT ruuid INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - reserve_gc INT8; -DECLARE - reserve_val INT8; -DECLARE - reserve_frac INT4; -BEGIN --- Shards: reserves by reserve_pub (SELECT) --- reserves_out (INSERT, with CONFLICT detection) by wih --- reserves by reserve_pub (UPDATE) --- reserves_in by reserve_pub (SELECT) --- wire_targets by wire_target_h_payto - -SELECT - current_balance_val - ,current_balance_frac - ,gc_date - ,reserve_uuid - INTO - reserve_val - ,reserve_frac - ,reserve_gc - ,ruuid - FROM exchange.reserves - WHERE reserves.reserve_pub=rpub; - -IF NOT FOUND -THEN - -- reserve unknown - reserve_found=FALSE; - balance_ok=FALSE; - ruuid=2; - RETURN; -END IF; - --- Check reserve balance is sufficient. -IF (reserve_val > amount_val) -THEN - IF (reserve_frac >= amount_frac) - THEN - reserve_val=reserve_val - amount_val; - reserve_frac=reserve_frac - amount_frac; - ELSE - reserve_val=reserve_val - amount_val - 1; - reserve_frac=reserve_frac + 100000000 - amount_frac; - END IF; -ELSE - IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac) - THEN - reserve_val=0; - reserve_frac=reserve_frac - amount_frac; - ELSE - reserve_found=TRUE; - balance_ok=FALSE; - RETURN; - END IF; -END IF; - --- Calculate new expiration dates. -min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc); - --- Update reserve balance. -UPDATE reserves SET - gc_date=min_reserve_gc - ,current_balance_val=reserve_val - ,current_balance_frac=reserve_frac -WHERE - reserves.reserve_pub=rpub; - -reserve_found=TRUE; -balance_ok=TRUE; - -END $$; - -COMMENT ON FUNCTION exchange_do_batch_withdraw(INT8, INT4, BYTEA, INT8, INT8) - IS 'Checks whether the reserve has sufficient balance for a withdraw operation (or the request is repeated and was previously approved) and if so updates the database with the result. Excludes storing the planchets.'; - - - - - -CREATE OR REPLACE FUNCTION exchange_do_batch_withdraw_insert( - IN cs_nonce BYTEA, - IN amount_val INT8, - IN amount_frac INT4, - IN h_denom_pub BYTEA, - IN ruuid INT8, - IN reserve_sig BYTEA, - IN h_coin_envelope BYTEA, - IN denom_sig BYTEA, - IN now INT8, - OUT out_denom_unknown BOOLEAN, - OUT out_nonce_reuse BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - denom_serial INT8; -BEGIN --- Shards: reserves by reserve_pub (SELECT) --- reserves_out (INSERT, with CONFLICT detection) by wih --- reserves by reserve_pub (UPDATE) --- reserves_in by reserve_pub (SELECT) --- wire_targets by wire_target_h_payto - -out_denom_unknown=TRUE; -out_conflict=TRUE; -out_nonce_reuse=TRUE; - -SELECT denominations_serial - INTO denom_serial - FROM exchange.denominations - WHERE denom_pub_hash=h_denom_pub; - -IF NOT FOUND -THEN - -- denomination unknown, should be impossible! - out_denom_unknown=TRUE; - ASSERT false, 'denomination unknown'; - RETURN; -END IF; -out_denom_unknown=FALSE; - -INSERT INTO exchange.reserves_out - (h_blind_ev - ,denominations_serial - ,denom_sig - ,reserve_uuid - ,reserve_sig - ,execution_date - ,amount_with_fee_val - ,amount_with_fee_frac) -VALUES - (h_coin_envelope - ,denom_serial - ,denom_sig - ,ruuid - ,reserve_sig - ,now - ,amount_val - ,amount_frac) -ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - out_conflict=TRUE; - RETURN; -END IF; -out_conflict=FALSE; - --- Special actions needed for a CS withdraw? -out_nonce_reuse=FALSE; -IF NOT NULL cs_nonce -THEN - -- Cache CS signature to prevent replays in the future - -- (and check if cached signature exists at the same time). - INSERT INTO exchange.cs_nonce_locks - (nonce - ,max_denomination_serial - ,op_hash) - VALUES - (cs_nonce - ,denom_serial - ,h_coin_envelope) - ON CONFLICT DO NOTHING; - - IF NOT FOUND - THEN - -- See if the existing entry is identical. - SELECT 1 - FROM exchange.cs_nonce_locks - WHERE nonce=cs_nonce - AND op_hash=h_coin_envelope; - IF NOT FOUND - THEN - out_nonce_reuse=TRUE; - ASSERT false, 'nonce reuse attempted by client'; - RETURN; - END IF; - END IF; -END IF; - -END $$; - -COMMENT ON FUNCTION exchange_do_batch_withdraw_insert(BYTEA, INT8, INT4, BYTEA, INT8, BYTEA, BYTEA, BYTEA, INT8) - IS 'Stores information about a planchet for a batch withdraw operation. Checks if the planchet already exists, and in that case indicates a conflict'; - - - - --- NOTE: experiment, currently dead, see postgres_Start_deferred_wire_out; --- now done inline. FIXME: Remove code here once inline version is confirmed working nicely! -CREATE OR REPLACE PROCEDURE defer_wire_out() -LANGUAGE plpgsql -AS $$ -BEGIN - -IF EXISTS ( - SELECT 1 - FROM exchange.information_Schema.constraint_column_usage - WHERE table_name='wire_out' - AND constraint_name='wire_out_ref') -THEN - SET CONSTRAINTS wire_out_ref DEFERRED; -END IF; - -END $$; - - -CREATE OR REPLACE FUNCTION exchange_do_recoup_by_reserve( - IN res_pub BYTEA -) -RETURNS TABLE -( - denom_sig BYTEA, - denominations_serial BIGINT, - coin_pub BYTEA, - coin_sig BYTEA, - coin_blind BYTEA, - amount_val BIGINT, - amount_frac INTEGER, - recoup_timestamp BIGINT -) -LANGUAGE plpgsql -AS $$ -DECLARE - res_uuid BIGINT; - blind_ev BYTEA; - c_pub BYTEA; -BEGIN - SELECT reserve_uuid - INTO res_uuid - FROM exchange.reserves - WHERE reserves.reserve_pub = res_pub; - - FOR blind_ev IN - SELECT h_blind_ev - FROM exchange.reserves_out_by_reserve - WHERE reserves_out_by_reserve.reserve_uuid = res_uuid - LOOP - SELECT robr.coin_pub - INTO c_pub - FROM exchange.recoup_by_reserve robr - WHERE robr.reserve_out_serial_id = ( - SELECT reserves_out.reserve_out_serial_id - FROM exchange.reserves_out - WHERE reserves_out.h_blind_ev = blind_ev - ); - RETURN QUERY - SELECT kc.denom_sig, - kc.denominations_serial, - rc.coin_pub, - rc.coin_sig, - rc.coin_blind, - rc.amount_val, - rc.amount_frac, - rc.recoup_timestamp - FROM ( - SELECT * - FROM exchange.known_coins - WHERE known_coins.coin_pub = c_pub - ) kc - JOIN ( - SELECT * - FROM exchange.recoup - WHERE recoup.coin_pub = c_pub - ) rc USING (coin_pub); - END LOOP; -END; -$$; - -COMMENT ON FUNCTION exchange_do_recoup_by_reserve - IS 'Recoup by reserve as a function to make sure we hit only the needed partition and not all when joining as joins on distributed tables fetch ALL rows from the shards'; - - -CREATE OR REPLACE FUNCTION exchange_do_deposit( - IN in_amount_with_fee_val INT8, - IN in_amount_with_fee_frac INT4, - IN in_h_contract_terms BYTEA, - IN in_wire_salt BYTEA, - IN in_wallet_timestamp INT8, - IN in_exchange_timestamp INT8, - IN in_refund_deadline INT8, - IN in_wire_deadline INT8, - IN in_merchant_pub BYTEA, - IN in_receiver_wire_account VARCHAR, - IN in_h_payto BYTEA, - IN in_known_coin_id INT8, - IN in_coin_pub BYTEA, - IN in_coin_sig BYTEA, - IN in_shard INT8, - IN in_policy_blocked BOOLEAN, - IN in_policy_details_serial_id INT8, - OUT out_exchange_timestamp INT8, - OUT out_balance_ok BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - wtsi INT8; -- wire target serial id -BEGIN --- Shards: INSERT policy_details (by policy_details_serial_id) --- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING; --- INSERT deposits (by coin_pub, shard), ON CONFLICT DO NOTHING; --- UPDATE known_coins (by coin_pub) - -INSERT INTO exchange.wire_targets - (wire_target_h_payto - ,payto_uri) - VALUES - (in_h_payto - ,in_receiver_wire_account) -ON CONFLICT DO NOTHING -- for CONFLICT ON (wire_target_h_payto) - RETURNING wire_target_serial_id INTO wtsi; - -IF NOT FOUND -THEN - SELECT wire_target_serial_id - INTO wtsi - FROM exchange.wire_targets - WHERE wire_target_h_payto=in_h_payto; -END IF; - - -INSERT INTO exchange.deposits - (shard - ,coin_pub - ,known_coin_id - ,amount_with_fee_val - ,amount_with_fee_frac - ,wallet_timestamp - ,exchange_timestamp - ,refund_deadline - ,wire_deadline - ,merchant_pub - ,h_contract_terms - ,coin_sig - ,wire_salt - ,wire_target_h_payto - ,policy_blocked - ,policy_details_serial_id - ) - VALUES - (in_shard - ,in_coin_pub - ,in_known_coin_id - ,in_amount_with_fee_val - ,in_amount_with_fee_frac - ,in_wallet_timestamp - ,in_exchange_timestamp - ,in_refund_deadline - ,in_wire_deadline - ,in_merchant_pub - ,in_h_contract_terms - ,in_coin_sig - ,in_wire_salt - ,in_h_payto - ,in_policy_blocked - ,in_policy_details_serial_id) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: see if an identical record exists. - -- Note that by checking 'coin_sig', we implicitly check - -- identity over everything that the signature covers. - -- We do select over merchant_pub and wire_target_h_payto - -- primarily here to maximally use the existing index. - SELECT - exchange_timestamp - INTO - out_exchange_timestamp - FROM exchange.deposits - WHERE shard=in_shard - AND merchant_pub=in_merchant_pub - AND wire_target_h_payto=in_h_payto - AND coin_pub=in_coin_pub - AND coin_sig=in_coin_sig; - -- AND policy_details_serial_id=in_policy_details_serial_id; -- FIXME: is this required for idempotency? - - IF NOT FOUND - THEN - -- Deposit exists, but with differences. Not allowed. - out_balance_ok=FALSE; - out_conflict=TRUE; - RETURN; - END IF; - - -- Idempotent request known, return success. - out_balance_ok=TRUE; - out_conflict=FALSE; - - RETURN; -END IF; - - -out_exchange_timestamp=in_exchange_timestamp; - --- Check and update balance of the coin. -UPDATE known_coins - SET - remaining_frac=remaining_frac-in_amount_with_fee_frac - + CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val-in_amount_with_fee_val - - CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_coin_pub - AND ( (remaining_val > in_amount_with_fee_val) OR - ( (remaining_frac >= in_amount_with_fee_frac) AND - (remaining_val >= in_amount_with_fee_val) ) ); - -IF NOT FOUND -THEN - -- Insufficient balance. - out_balance_ok=FALSE; - out_conflict=FALSE; - RETURN; -END IF; - --- Everything fine, return success! -out_balance_ok=TRUE; -out_conflict=FALSE; - -END $$; - - - -CREATE OR REPLACE FUNCTION exchange_do_melt( - IN in_cs_rms BYTEA, - IN in_amount_with_fee_val INT8, - IN in_amount_with_fee_frac INT4, - IN in_rc BYTEA, - IN in_old_coin_pub BYTEA, - IN in_old_coin_sig BYTEA, - IN in_known_coin_id INT8, -- not used, but that's OK - IN in_noreveal_index INT4, - IN in_zombie_required BOOLEAN, - OUT out_balance_ok BOOLEAN, - OUT out_zombie_bad BOOLEAN, - OUT out_noreveal_index INT4) -LANGUAGE plpgsql -AS $$ -DECLARE - denom_max INT8; -BEGIN --- Shards: INSERT refresh_commitments (by rc) --- (rare:) SELECT refresh_commitments (by old_coin_pub) -- crosses shards! --- (rare:) SEELCT refresh_revealed_coins (by melt_serial_id) --- (rare:) PERFORM recoup_refresh (by rrc_serial) -- crosses shards! --- UPDATE known_coins (by coin_pub) - -INSERT INTO exchange.refresh_commitments - (rc - ,old_coin_pub - ,old_coin_sig - ,amount_with_fee_val - ,amount_with_fee_frac - ,noreveal_index - ) - VALUES - (in_rc - ,in_old_coin_pub - ,in_old_coin_sig - ,in_amount_with_fee_val - ,in_amount_with_fee_frac - ,in_noreveal_index) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: see if an identical record exists. - out_noreveal_index=-1; - SELECT - noreveal_index - INTO - out_noreveal_index - FROM exchange.refresh_commitments - WHERE rc=in_rc; - out_balance_ok=FOUND; - out_zombie_bad=FALSE; -- zombie is OK - RETURN; -END IF; - - -IF in_zombie_required -THEN - -- Check if this coin was part of a refresh - -- operation that was subsequently involved - -- in a recoup operation. We begin by all - -- refresh operations our coin was involved - -- with, then find all associated reveal - -- operations, and then see if any of these - -- reveal operations was involved in a recoup. - PERFORM - FROM exchange.recoup_refresh - WHERE rrc_serial IN - (SELECT rrc_serial - FROM exchange.refresh_revealed_coins - WHERE melt_serial_id IN - (SELECT melt_serial_id - FROM exchange.refresh_commitments - WHERE old_coin_pub=in_old_coin_pub)); - IF NOT FOUND - THEN - out_zombie_bad=TRUE; - out_balance_ok=FALSE; - RETURN; - END IF; -END IF; - -out_zombie_bad=FALSE; -- zombie is OK - - --- Check and update balance of the coin. -UPDATE known_coins - SET - remaining_frac=remaining_frac-in_amount_with_fee_frac - + CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val-in_amount_with_fee_val - - CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_old_coin_pub - AND ( (remaining_val > in_amount_with_fee_val) OR - ( (remaining_frac >= in_amount_with_fee_frac) AND - (remaining_val >= in_amount_with_fee_val) ) ); - -IF NOT FOUND -THEN - -- Insufficient balance. - out_noreveal_index=-1; - out_balance_ok=FALSE; - RETURN; -END IF; - - - --- Special actions needed for a CS melt? -IF NOT NULL in_cs_rms -THEN - -- Get maximum denominations serial value in - -- existence, this will determine how long the - -- nonce will be locked. - SELECT - denominations_serial - INTO - denom_max - FROM exchange.denominations - ORDER BY denominations_serial DESC - LIMIT 1; - - -- Cache CS signature to prevent replays in the future - -- (and check if cached signature exists at the same time). - INSERT INTO exchange.cs_nonce_locks - (nonce - ,max_denomination_serial - ,op_hash) - VALUES - (cs_rms - ,denom_serial - ,in_rc) - ON CONFLICT DO NOTHING; - - IF NOT FOUND - THEN - -- Record exists, make sure it is the same - SELECT 1 - FROM exchange.cs_nonce_locks - WHERE nonce=cs_rms - AND op_hash=in_rc; - - IF NOT FOUND - THEN - -- Nonce reuse detected - out_balance_ok=FALSE; - out_zombie_bad=FALSE; - out_noreveal_index=42; -- FIXME: return error message more nicely! - ASSERT false, 'nonce reuse attempted by client'; - END IF; - END IF; -END IF; - --- Everything fine, return success! -out_balance_ok=TRUE; -out_noreveal_index=in_noreveal_index; - -END $$; - - - -CREATE OR REPLACE FUNCTION exchange_do_refund( - IN in_amount_with_fee_val INT8, - IN in_amount_with_fee_frac INT4, - IN in_amount_val INT8, - IN in_amount_frac INT4, - IN in_deposit_fee_val INT8, - IN in_deposit_fee_frac INT4, - IN in_h_contract_terms BYTEA, - IN in_rtransaction_id INT8, - IN in_deposit_shard INT8, - IN in_known_coin_id INT8, - IN in_coin_pub BYTEA, - IN in_merchant_pub BYTEA, - IN in_merchant_sig BYTEA, - OUT out_not_found BOOLEAN, - OUT out_refund_ok BOOLEAN, - OUT out_gone BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - dsi INT8; -- ID of deposit being refunded -DECLARE - tmp_val INT8; -- total amount refunded -DECLARE - tmp_frac INT8; -- total amount refunded -DECLARE - deposit_val INT8; -- amount that was originally deposited -DECLARE - deposit_frac INT8; -- amount that was originally deposited -BEGIN --- Shards: SELECT deposits (coin_pub, shard, h_contract_terms, merchant_pub) --- INSERT refunds (by coin_pub, rtransaction_id) ON CONFLICT DO NOTHING --- SELECT refunds (by coin_pub) --- UPDATE known_coins (by coin_pub) - -SELECT - deposit_serial_id - ,amount_with_fee_val - ,amount_with_fee_frac - ,done -INTO - dsi - ,deposit_val - ,deposit_frac - ,out_gone -FROM exchange.deposits - WHERE coin_pub=in_coin_pub - AND shard=in_deposit_shard - AND merchant_pub=in_merchant_pub - AND h_contract_terms=in_h_contract_terms; - -IF NOT FOUND -THEN - -- No matching deposit found! - out_refund_ok=FALSE; - out_conflict=FALSE; - out_not_found=TRUE; - out_gone=FALSE; - RETURN; -END IF; - -INSERT INTO exchange.refunds - (deposit_serial_id - ,coin_pub - ,merchant_sig - ,rtransaction_id - ,amount_with_fee_val - ,amount_with_fee_frac - ) - VALUES - (dsi - ,in_coin_pub - ,in_merchant_sig - ,in_rtransaction_id - ,in_amount_with_fee_val - ,in_amount_with_fee_frac) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: see if an identical record exists. - -- Note that by checking 'coin_sig', we implicitly check - -- identity over everything that the signature covers. - -- We do select over merchant_pub and h_contract_terms - -- primarily here to maximally use the existing index. - PERFORM - FROM exchange.refunds - WHERE coin_pub=in_coin_pub - AND deposit_serial_id=dsi - AND rtransaction_id=in_rtransaction_id - AND amount_with_fee_val=in_amount_with_fee_val - AND amount_with_fee_frac=in_amount_with_fee_frac; - - IF NOT FOUND - THEN - -- Deposit exists, but have conflicting refund. - out_refund_ok=FALSE; - out_conflict=TRUE; - out_not_found=FALSE; - RETURN; - END IF; - - -- Idempotent request known, return success. - out_refund_ok=TRUE; - out_conflict=FALSE; - out_not_found=FALSE; - out_gone=FALSE; - RETURN; -END IF; - -IF out_gone -THEN - -- money already sent to the merchant. Tough luck. - out_refund_ok=FALSE; - out_conflict=FALSE; - out_not_found=FALSE; - RETURN; -END IF; - --- Check refund balance invariant. -SELECT - SUM(amount_with_fee_val) -- overflow here is not plausible - ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits - INTO - tmp_val - ,tmp_frac - FROM exchange.refunds - WHERE coin_pub=in_coin_pub - AND deposit_serial_id=dsi; -IF tmp_val IS NULL -THEN - RAISE NOTICE 'failed to sum up existing refunds'; - out_refund_ok=FALSE; - out_conflict=FALSE; - out_not_found=FALSE; - RETURN; -END IF; - --- Normalize result before continuing -tmp_val = tmp_val + tmp_frac / 100000000; -tmp_frac = tmp_frac % 100000000; - --- Actually check if the deposits are sufficient for the refund. Verbosely. ;-) -IF (tmp_val < deposit_val) -THEN - out_refund_ok=TRUE; -ELSE - IF (tmp_val = deposit_val) AND (tmp_frac <= deposit_frac) - THEN - out_refund_ok=TRUE; - ELSE - out_refund_ok=FALSE; - END IF; -END IF; - -IF (tmp_val = deposit_val) AND (tmp_frac = deposit_frac) -THEN - -- Refunds have reached the full value of the original - -- deposit. Also refund the deposit fee. - in_amount_frac = in_amount_frac + in_deposit_fee_frac; - in_amount_val = in_amount_val + in_deposit_fee_val; - - -- Normalize result before continuing - in_amount_val = in_amount_val + in_amount_frac / 100000000; - in_amount_frac = in_amount_frac % 100000000; -END IF; - --- Update balance of the coin. -UPDATE known_coins - SET - remaining_frac=remaining_frac+in_amount_frac - - CASE - WHEN remaining_frac+in_amount_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val+in_amount_val - + CASE - WHEN remaining_frac+in_amount_frac >= 100000000 - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_coin_pub; - - -out_conflict=FALSE; -out_not_found=FALSE; - -END $$; - --- COMMENT ON FUNCTION exchange_do_refund(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) --- IS 'Executes a refund operation, checking that the corresponding deposit was sufficient to cover the refunded amount'; - - - - -CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve( - IN in_reserve_pub BYTEA, - IN in_reserve_out_serial_id INT8, - IN in_coin_blind BYTEA, - IN in_coin_pub BYTEA, - IN in_known_coin_id INT8, - IN in_coin_sig BYTEA, - IN in_reserve_gc INT8, - IN in_reserve_expiration INT8, - IN in_recoup_timestamp INT8, - OUT out_recoup_ok BOOLEAN, - OUT out_internal_failure BOOLEAN, - OUT out_recoup_timestamp INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - tmp_val INT8; -- amount recouped -DECLARE - tmp_frac INT8; -- amount recouped -BEGIN --- Shards: SELECT known_coins (by coin_pub) --- SELECT recoup (by coin_pub) --- UPDATE known_coins (by coin_pub) --- UPDATE reserves (by reserve_pub) --- INSERT recoup (by coin_pub) - -out_internal_failure=FALSE; - - --- Check remaining balance of the coin. -SELECT - remaining_frac - ,remaining_val - INTO - tmp_frac - ,tmp_val -FROM exchange.known_coins - WHERE coin_pub=in_coin_pub; - -IF NOT FOUND -THEN - out_internal_failure=TRUE; - out_recoup_ok=FALSE; - RETURN; -END IF; - -IF tmp_val + tmp_frac = 0 -THEN - -- Check for idempotency - SELECT - recoup_timestamp - INTO - out_recoup_timestamp - FROM exchange.recoup - WHERE coin_pub=in_coin_pub; - - out_recoup_ok=FOUND; - RETURN; -END IF; - - --- Update balance of the coin. -UPDATE known_coins - SET - remaining_frac=0 - ,remaining_val=0 - WHERE coin_pub=in_coin_pub; - - --- Credit the reserve and update reserve timers. -UPDATE reserves - SET - current_balance_frac=current_balance_frac+tmp_frac - - CASE - WHEN current_balance_frac+tmp_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - current_balance_val=current_balance_val+tmp_val - + CASE - WHEN current_balance_frac+tmp_frac >= 100000000 - THEN 1 - ELSE 0 - END, - gc_date=GREATEST(gc_date, in_reserve_gc), - expiration_date=GREATEST(expiration_date, in_reserve_expiration) - WHERE reserve_pub=in_reserve_pub; - - -IF NOT FOUND -THEN - RAISE NOTICE 'failed to increase reserve balance from recoup'; - out_recoup_ok=TRUE; - out_internal_failure=TRUE; - RETURN; -END IF; - - -INSERT INTO exchange.recoup - (coin_pub - ,coin_sig - ,coin_blind - ,amount_val - ,amount_frac - ,recoup_timestamp - ,reserve_out_serial_id - ) -VALUES - (in_coin_pub - ,in_coin_sig - ,in_coin_blind - ,tmp_val - ,tmp_frac - ,in_recoup_timestamp - ,in_reserve_out_serial_id); - --- Normal end, everything is fine. -out_recoup_ok=TRUE; -out_recoup_timestamp=in_recoup_timestamp; - -END $$; - --- COMMENT ON FUNCTION exchange_do_recoup_to_reserve(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) --- IS 'Executes a recoup of a coin that was withdrawn from a reserve'; - - - - - - -CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin( - IN in_old_coin_pub BYTEA, - IN in_rrc_serial INT8, - IN in_coin_blind BYTEA, - IN in_coin_pub BYTEA, - IN in_known_coin_id INT8, - IN in_coin_sig BYTEA, - IN in_recoup_timestamp INT8, - OUT out_recoup_ok BOOLEAN, - OUT out_internal_failure BOOLEAN, - OUT out_recoup_timestamp INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - tmp_val INT8; -- amount recouped -DECLARE - tmp_frac INT8; -- amount recouped -BEGIN - --- Shards: UPDATE known_coins (by coin_pub) --- SELECT recoup_refresh (by coin_pub) --- UPDATE known_coins (by coin_pub) --- INSERT recoup_refresh (by coin_pub) - - -out_internal_failure=FALSE; - - --- Check remaining balance of the coin. -SELECT - remaining_frac - ,remaining_val - INTO - tmp_frac - ,tmp_val -FROM exchange.known_coins - WHERE coin_pub=in_coin_pub; - -IF NOT FOUND -THEN - out_internal_failure=TRUE; - out_recoup_ok=FALSE; - RETURN; -END IF; - -IF tmp_val + tmp_frac = 0 -THEN - -- Check for idempotency - SELECT - recoup_timestamp - INTO - out_recoup_timestamp - FROM exchange.recoup_refresh - WHERE coin_pub=in_coin_pub; - out_recoup_ok=FOUND; - RETURN; -END IF; - --- Update balance of the coin. -UPDATE known_coins - SET - remaining_frac=0 - ,remaining_val=0 - WHERE coin_pub=in_coin_pub; - - --- Credit the old coin. -UPDATE known_coins - SET - remaining_frac=remaining_frac+tmp_frac - - CASE - WHEN remaining_frac+tmp_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val+tmp_val - + CASE - WHEN remaining_frac+tmp_frac >= 100000000 - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_old_coin_pub; - - -IF NOT FOUND -THEN - RAISE NOTICE 'failed to increase old coin balance from recoup'; - out_recoup_ok=TRUE; - out_internal_failure=TRUE; - RETURN; -END IF; - - -INSERT INTO exchange.recoup_refresh - (coin_pub - ,known_coin_id - ,coin_sig - ,coin_blind - ,amount_val - ,amount_frac - ,recoup_timestamp - ,rrc_serial - ) -VALUES - (in_coin_pub - ,in_known_coin_id - ,in_coin_sig - ,in_coin_blind - ,tmp_val - ,tmp_frac - ,in_recoup_timestamp - ,in_rrc_serial); - --- Normal end, everything is fine. -out_recoup_ok=TRUE; -out_recoup_timestamp=in_recoup_timestamp; - -END $$; - - - - --- COMMENT ON FUNCTION exchange_do_recoup_to_coin(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) --- IS 'Executes a recoup-refresh of a coin that was obtained from a refresh-reveal process'; - - - -CREATE OR REPLACE PROCEDURE exchange_do_gc( - IN in_ancient_date INT8, - IN in_now INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - reserve_uuid_min INT8; -- minimum reserve UUID still alive -DECLARE - melt_min INT8; -- minimum melt still alive -DECLARE - coin_min INT8; -- minimum known_coin still alive -DECLARE - deposit_min INT8; -- minimum deposit still alive -DECLARE - reserve_out_min INT8; -- minimum reserve_out still alive -DECLARE - denom_min INT8; -- minimum denomination still alive -BEGIN - -DELETE FROM exchange.prewire - WHERE finished=TRUE; - -DELETE FROM exchange.wire_fee - WHERE end_date < in_ancient_date; - --- TODO: use closing fee as threshold? -DELETE FROM exchange.reserves - WHERE gc_date < in_now - AND current_balance_val = 0 - AND current_balance_frac = 0; - -SELECT - reserve_out_serial_id - INTO - reserve_out_min - FROM exchange.reserves_out - ORDER BY reserve_out_serial_id ASC - LIMIT 1; - -DELETE FROM exchange.recoup - WHERE reserve_out_serial_id < reserve_out_min; --- FIXME: recoup_refresh lacks GC! - -SELECT - reserve_uuid - INTO - reserve_uuid_min - FROM exchange.reserves - ORDER BY reserve_uuid ASC - LIMIT 1; - -DELETE FROM exchange.reserves_out - WHERE reserve_uuid < reserve_uuid_min; - --- FIXME: this query will be horribly slow; --- need to find another way to formulate it... -DELETE FROM exchange.denominations - WHERE expire_legal < in_now - AND denominations_serial NOT IN - (SELECT DISTINCT denominations_serial - FROM exchange.reserves_out) - AND denominations_serial NOT IN - (SELECT DISTINCT denominations_serial - FROM exchange.known_coins - WHERE coin_pub IN - (SELECT DISTINCT coin_pub - FROM exchange.recoup)) - AND denominations_serial NOT IN - (SELECT DISTINCT denominations_serial - FROM exchange.known_coins - WHERE coin_pub IN - (SELECT DISTINCT coin_pub - FROM exchange.recoup_refresh)); - -SELECT - melt_serial_id - INTO - melt_min - FROM exchange.refresh_commitments - ORDER BY melt_serial_id ASC - LIMIT 1; - -DELETE FROM exchange.refresh_revealed_coins - WHERE melt_serial_id < melt_min; - -DELETE FROM exchange.refresh_transfer_keys - WHERE melt_serial_id < melt_min; - -SELECT - known_coin_id - INTO - coin_min - FROM exchange.known_coins - ORDER BY known_coin_id ASC - LIMIT 1; - -DELETE FROM exchange.deposits - WHERE known_coin_id < coin_min; - -SELECT - deposit_serial_id - INTO - deposit_min - FROM exchange.deposits - ORDER BY deposit_serial_id ASC - LIMIT 1; - -DELETE FROM exchange.refunds - WHERE deposit_serial_id < deposit_min; - -DELETE FROM exchange.aggregation_tracking - WHERE deposit_serial_id < deposit_min; - -SELECT - denominations_serial - INTO - denom_min - FROM exchange.denominations - ORDER BY denominations_serial ASC - LIMIT 1; - -DELETE FROM exchange.cs_nonce_locks - WHERE max_denomination_serial <= denom_min; - -END $$; - - - - -CREATE OR REPLACE FUNCTION exchange_do_purse_deposit( - IN in_partner_id INT8, - IN in_purse_pub BYTEA, - IN in_amount_with_fee_val INT8, - IN in_amount_with_fee_frac INT4, - IN in_coin_pub BYTEA, - IN in_coin_sig BYTEA, - IN in_amount_without_fee_val INT8, - IN in_amount_without_fee_frac INT4, - IN in_reserve_expiration INT8, - IN in_now INT8, - OUT out_balance_ok BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - was_merged BOOLEAN; -DECLARE - psi INT8; -- partner's serial ID (set if merged) -DECLARE - my_amount_val INT8; -- total in purse -DECLARE - my_amount_frac INT4; -- total in purse -DECLARE - was_paid BOOLEAN; -DECLARE - my_in_reserve_quota BOOLEAN; -DECLARE - my_reserve_pub BYTEA; -BEGIN - --- Store the deposit request. -INSERT INTO exchange.purse_deposits - (partner_serial_id - ,purse_pub - ,coin_pub - ,amount_with_fee_val - ,amount_with_fee_frac - ,coin_sig) - VALUES - (in_partner_id - ,in_purse_pub - ,in_coin_pub - ,in_amount_with_fee_val - ,in_amount_with_fee_frac - ,in_coin_sig) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: check if coin_sig is the same, - -- if so, success, otherwise conflict! - PERFORM - FROM exchange.purse_deposits - WHERE coin_pub = in_coin_pub - AND purse_pub = in_purse_pub - AND coin_sig = in_cion_sig; - IF NOT FOUND - THEN - -- Deposit exists, but with differences. Not allowed. - out_balance_ok=FALSE; - out_conflict=TRUE; - RETURN; - END IF; -END IF; - - --- Debit the coin --- Check and update balance of the coin. -UPDATE known_coins - SET - remaining_frac=remaining_frac-in_amount_with_fee_frac - + CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val-in_amount_with_fee_val - - CASE - WHEN remaining_frac < in_amount_with_fee_frac - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_coin_pub - AND ( (remaining_val > in_amount_with_fee_val) OR - ( (remaining_frac >= in_amount_with_fee_frac) AND - (remaining_val >= in_amount_with_fee_val) ) ); - -IF NOT FOUND -THEN - -- Insufficient balance. - out_balance_ok=FALSE; - out_conflict=FALSE; - RETURN; -END IF; - - --- Credit the purse. -UPDATE purse_requests - SET - balance_frac=balance_frac+in_amount_without_fee_frac - - CASE - WHEN balance_frac+in_amount_without_fee_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - balance_val=balance_val+in_amount_without_fee_val - + CASE - WHEN balance_frac+in_amount_without_fee_frac >= 100000000 - THEN 1 - ELSE 0 - END - WHERE purse_pub=in_purse_pub; - -out_conflict=FALSE; -out_balance_ok=TRUE; - --- See if we can finish the merge or need to update the trigger time and partner. -SELECT COALESCE(partner_serial_id,0) - ,reserve_pub - INTO psi - ,my_reserve_pub - FROM exchange.purse_merges - WHERE purse_pub=in_purse_pub; - -IF NOT FOUND -THEN - RETURN; -END IF; - -SELECT - amount_with_fee_val - ,amount_with_fee_frac - ,in_reserve_quota - INTO - my_amount_val - ,my_amount_frac - ,my_in_reserve_quota - FROM exchange.purse_requests - WHERE (purse_pub=in_purse_pub) - AND ( ( ( (amount_with_fee_val <= balance_val) - AND (amount_with_fee_frac <= balance_frac) ) - OR (amount_with_fee_val < balance_val) ) ); -IF NOT FOUND -THEN - RETURN; -END IF; - --- Remember how this purse was finished. -INSERT INTO purse_decision - (purse_pub - ,action_timestamp - ,refunded) -VALUES - (in_purse_pub - ,in_now - ,FALSE) -ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - out_conflict=TRUE; - RETURN; -END IF; - -IF (my_in_reserve_quota) -THEN - UPDATE reserves - SET purses_active=purses_active-1 - WHERE reserve_pub IN - (SELECT reserve_pub - FROM exchange.purse_merges - WHERE purse_pub=my_purse_pub - LIMIT 1); -END IF; - - -IF (0 != psi) -THEN - -- The taler-exchange-router will take care of this. - UPDATE purse_actions - SET action_date=0 --- "immediately" - ,partner_serial_id=psi - WHERE purse_pub=in_purse_pub; -ELSE - -- This is a local reserve, update balance immediately. - INSERT INTO reserves - (reserve_pub - ,current_balance_frac - ,current_balance_val - ,expiration_date - ,gc_date) - VALUES - (my_reserve_pub - ,my_amount_frac - ,my_amount_val - ,in_reserve_expiration - ,in_reserve_expiration) - ON CONFLICT DO NOTHING; - - IF NOT FOUND - THEN - - UPDATE reserves - SET - current_balance_frac=current_balance_frac+my_amount_frac - - CASE - WHEN current_balance_frac + my_amount_frac >= 100000000 - THEN 100000000 - ELSE 0 - END - ,current_balance_val=current_balance_val+my_amount_val - + CASE - WHEN current_balance_frac + my_amount_frac >= 100000000 - THEN 1 - ELSE 0 - END - ,expiration_date=GREATEST(expiration_date,in_reserve_expiration) - ,gc_date=GREATEST(gc_date,in_reserve_expiration) - WHERE reserve_pub=my_reserve_pub; - END IF; - -END IF; - - -END $$; - - - - -CREATE OR REPLACE FUNCTION exchange_do_purse_merge( - IN in_purse_pub BYTEA, - IN in_merge_sig BYTEA, - IN in_merge_timestamp INT8, - IN in_reserve_sig BYTEA, - IN in_partner_url VARCHAR, - IN in_reserve_pub BYTEA, - IN in_wallet_h_payto BYTEA, - IN in_expiration_date INT8, - OUT out_no_partner BOOLEAN, - OUT out_no_balance BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - my_amount_val INT8; -DECLARE - my_amount_frac INT4; -DECLARE - my_purse_fee_val INT8; -DECLARE - my_purse_fee_frac INT4; -DECLARE - my_partner_serial_id INT8; -DECLARE - my_in_reserve_quota BOOLEAN; -BEGIN - -IF in_partner_url IS NULL -THEN - my_partner_serial_id=NULL; -ELSE - SELECT - partner_serial_id - INTO - my_partner_serial_id - FROM exchange.partners - WHERE partner_base_url=in_partner_url - AND start_date <= in_merge_timestamp - AND end_date > in_merge_timestamp; - IF NOT FOUND - THEN - out_no_partner=TRUE; - out_conflict=FALSE; - RETURN; - END IF; -END IF; - -out_no_partner=FALSE; - - --- Check purse is 'full'. -SELECT amount_with_fee_val - ,amount_with_fee_frac - ,purse_fee_val - ,purse_fee_frac - ,in_reserve_quota - INTO my_amount_val - ,my_amount_frac - ,my_purse_fee_val - ,my_purse_fee_frac - ,my_in_reserve_quota - FROM exchange.purse_requests - WHERE purse_pub=in_purse_pub - AND balance_val >= amount_with_fee_val - AND ( (balance_frac >= amount_with_fee_frac) OR - (balance_val > amount_with_fee_val) ); -IF NOT FOUND -THEN - out_no_balance=TRUE; - out_conflict=FALSE; - RETURN; -END IF; -out_no_balance=FALSE; - --- Store purse merge signature, checks for purse_pub uniqueness -INSERT INTO exchange.purse_merges - (partner_serial_id - ,reserve_pub - ,purse_pub - ,merge_sig - ,merge_timestamp) - VALUES - (my_partner_serial_id - ,in_reserve_pub - ,in_purse_pub - ,in_merge_sig - ,in_merge_timestamp) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: see if an identical record exists. - -- Note that by checking 'merge_sig', we implicitly check - -- identity over everything that the signature covers. - PERFORM - FROM exchange.purse_merges - WHERE purse_pub=in_purse_pub - AND merge_sig=in_merge_sig; - IF NOT FOUND - THEN - -- Purse was merged, but to some other reserve. Not allowed. - out_conflict=TRUE; - RETURN; - END IF; - - -- "success" - out_conflict=FALSE; - RETURN; -END IF; -out_conflict=FALSE; - - --- Initialize reserve, if not yet exists. -INSERT INTO reserves - (reserve_pub - ,expiration_date - ,gc_date) - VALUES - (in_reserve_pub - ,in_expiration_date - ,in_expiration_date) - ON CONFLICT DO NOTHING; - --- Remember how this purse was finished. -INSERT INTO purse_decision - (purse_pub - ,action_timestamp - ,refunded) -VALUES - (in_purse_pub - ,in_merge_timestamp - ,FALSE); - -IF (my_in_reserve_quota) -THEN - UPDATE reserves - SET purses_active=purses_active-1 - WHERE reserve_pub IN - (SELECT reserve_pub - FROM exchange.purse_merges - WHERE purse_pub=my_purse_pub - LIMIT 1); -END IF; - --- Store account merge signature. -INSERT INTO exchange.account_merges - (reserve_pub - ,reserve_sig - ,purse_pub - ,wallet_h_payto) - VALUES - (in_reserve_pub - ,in_reserve_sig - ,in_purse_pub - ,in_wallet_h_payto); - --- If we need a wad transfer, mark purse ready for it. -IF (0 != my_partner_serial_id) -THEN - -- The taler-exchange-router will take care of this. - UPDATE purse_actions - SET action_date=0 --- "immediately" - ,partner_serial_id=my_partner_serial_id - WHERE purse_pub=in_purse_pub; -ELSE - -- This is a local reserve, update reserve balance immediately. - - -- Refund the purse fee, by adding it to the purse value: - my_amount_val = my_amount_val + my_purse_fee_val; - my_amount_frac = my_amount_frac + my_purse_fee_frac; - -- normalize result - my_amount_val = my_amount_val + my_amount_frac / 100000000; - my_amount_frac = my_amount_frac % 100000000; - - UPDATE exchange.reserves - SET - current_balance_frac=current_balance_frac+my_amount_frac - - CASE - WHEN current_balance_frac + my_amount_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - current_balance_val=current_balance_val+my_amount_val - + CASE - WHEN current_balance_frac + my_amount_frac >= 100000000 - THEN 1 - ELSE 0 - END - WHERE reserve_pub=in_reserve_pub; - -END IF; - -RETURN; - -END $$; - -COMMENT ON FUNCTION exchange_do_purse_merge(BYTEA, BYTEA, INT8, BYTEA, VARCHAR, BYTEA, BYTEA, INT8) - IS 'Checks that the partner exists, the purse has not been merged with a different reserve and that the purse is full. If so, persists the merge data and either merges the purse with the reserve or marks it as ready for the taler-exchange-router. Caller MUST abort the transaction on failures so as to not persist data by accident.'; - - -CREATE OR REPLACE FUNCTION exchange_do_reserve_purse( - IN in_purse_pub BYTEA, - IN in_merge_sig BYTEA, - IN in_merge_timestamp INT8, - IN in_reserve_sig BYTEA, - IN in_reserve_quota BOOLEAN, - IN in_purse_fee_val INT8, - IN in_purse_fee_frac INT4, - IN in_reserve_pub BYTEA, - IN in_wallet_h_payto BYTEA, - OUT out_no_funds BOOLEAN, - OUT out_no_reserve BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -BEGIN - --- Store purse merge signature, checks for purse_pub uniqueness -INSERT INTO exchange.purse_merges - (partner_serial_id - ,reserve_pub - ,purse_pub - ,merge_sig - ,merge_timestamp) - VALUES - (NULL - ,in_reserve_pub - ,in_purse_pub - ,in_merge_sig - ,in_merge_timestamp) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotency check: see if an identical record exists. - -- Note that by checking 'merge_sig', we implicitly check - -- identity over everything that the signature covers. - PERFORM - FROM exchange.purse_merges - WHERE purse_pub=in_purse_pub - AND merge_sig=in_merge_sig; - IF NOT FOUND - THEN - -- Purse was merged, but to some other reserve. Not allowed. - out_conflict=TRUE; - out_no_reserve=FALSE; - out_no_funds=FALSE; - RETURN; - END IF; - - -- "success" - out_conflict=FALSE; - out_no_funds=FALSE; - out_no_reserve=FALSE; - RETURN; -END IF; -out_conflict=FALSE; - -PERFORM - FROM exchange.reserves - WHERE reserve_pub=in_reserve_pub; - -out_no_reserve = NOT FOUND; - -IF (in_reserve_quota) -THEN - -- Increment active purses per reserve (and check this is allowed) - IF (out_no_reserve) - THEN - out_no_funds=TRUE; - RETURN; - END IF; - UPDATE exchange.reserves - SET purses_active=purses_active+1 - WHERE reserve_pub=in_reserve_pub - AND purses_active < purses_allowed; - IF NOT FOUND - THEN - out_no_funds=TRUE; - RETURN; - END IF; -ELSE - -- UPDATE reserves balance (and check if balance is enough to pay the fee) - IF (out_no_reserve) - THEN - IF ( (0 != in_purse_fee_val) OR - (0 != in_purse_fee_frac) ) - THEN - out_no_funds=TRUE; - RETURN; - END IF; - ELSE - UPDATE exchange.reserves - SET - current_balance_frac=current_balance_frac-in_purse_fee_frac - + CASE - WHEN current_balance_frac < in_purse_fee_frac - THEN 100000000 - ELSE 0 - END, - current_balance_val=current_balance_val-in_purse_fee_val - - CASE - WHEN current_balance_frac < in_purse_fee_frac - THEN 1 - ELSE 0 - END - WHERE reserve_pub=in_reserve_pub - AND ( (current_balance_val > in_purse_fee_val) OR - ( (current_balance_frac >= in_purse_fee_frac) AND - (current_balance_val >= in_purse_fee_val) ) ); - IF NOT FOUND - THEN - out_no_funds=TRUE; - RETURN; - END IF; - END IF; -END IF; - -out_no_funds=FALSE; - - --- Store account merge signature. -INSERT INTO exchange.account_merges - (reserve_pub - ,reserve_sig - ,purse_pub - ,wallet_h_payto) - VALUES - (in_reserve_pub - ,in_reserve_sig - ,in_purse_pub - ,in_wallet_h_payto); - -END $$; - -COMMENT ON FUNCTION exchange_do_reserve_purse(BYTEA, BYTEA, INT8, BYTEA, BOOLEAN, INT8, INT4, BYTEA, BYTEA) - IS 'Create a purse for a reserve.'; - - - - - - -CREATE OR REPLACE FUNCTION exchange_do_account_merge( - IN in_purse_pub BYTEA, - IN in_reserve_pub BYTEA, - IN in_reserve_sig BYTEA, - OUT out_balance_ok BOOLEAN, - OUT out_conflict BOOLEAN) -LANGUAGE plpgsql -AS $$ -BEGIN - -- FIXME: function/API is dead! Do DCE? -END $$; - - - -CREATE OR REPLACE FUNCTION exchange_do_expire_purse( - IN in_start_time INT8, - IN in_end_time INT8, - IN in_now INT8, - OUT out_found BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - my_purse_pub BYTEA; -DECLARE - my_deposit record; -DECLARE - my_in_reserve_quota BOOLEAN; -BEGIN - --- FIXME: we should probably do this in a loop --- and expire all at once, instead of one per query -SELECT purse_pub - ,in_reserve_quota - INTO my_purse_pub - ,my_in_reserve_quota - FROM exchange.purse_requests - WHERE (purse_expiration >= in_start_time) AND - (purse_expiration < in_end_time) AND - purse_pub NOT IN (SELECT purse_pub - FROM purse_decision) - ORDER BY purse_expiration ASC - LIMIT 1; -out_found = FOUND; -IF NOT FOUND -THEN - RETURN; -END IF; - -INSERT INTO purse_decision - (purse_pub - ,action_timestamp - ,refunded) -VALUES - (my_purse_pub - ,in_now - ,TRUE); - -IF (my_in_reserve_quota) -THEN - UPDATE reserves - SET purses_active=purses_active-1 - WHERE reserve_pub IN - (SELECT reserve_pub - FROM exchange.purse_merges - WHERE purse_pub=my_purse_pub - LIMIT 1); -END IF; - --- restore balance to each coin deposited into the purse -FOR my_deposit IN - SELECT coin_pub - ,amount_with_fee_val - ,amount_with_fee_frac - FROM exchange.purse_deposits - WHERE purse_pub = my_purse_pub -LOOP - UPDATE exchange.known_coins SET - remaining_frac=remaining_frac+my_deposit.amount_with_fee_frac - - CASE - WHEN remaining_frac+my_deposit.amount_with_fee_frac >= 100000000 - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val+my_deposit.amount_with_fee_val - + CASE - WHEN remaining_frac+my_deposit.amount_with_fee_frac >= 100000000 - THEN 1 - ELSE 0 - END - WHERE coin_pub = my_deposit.coin_pub; - END LOOP; -END $$; - -COMMENT ON FUNCTION exchange_do_expire_purse(INT8,INT8,INT8) - IS 'Finds an expired purse in the given time range and refunds the coins (if any).'; - - - - -CREATE OR REPLACE FUNCTION exchange_do_history_request( - IN in_reserve_pub BYTEA, - IN in_reserve_sig BYTEA, - IN in_request_timestamp INT8, - IN in_history_fee_val INT8, - IN in_history_fee_frac INT4, - OUT out_balance_ok BOOLEAN, - OUT out_idempotent BOOLEAN) -LANGUAGE plpgsql -AS $$ -BEGIN - - -- Insert and check for idempotency. - INSERT INTO exchange.history_requests - (reserve_pub - ,request_timestamp - ,reserve_sig - ,history_fee_val - ,history_fee_frac) - VALUES - (in_reserve_pub - ,in_request_timestamp - ,in_reserve_sig - ,in_history_fee_val - ,in_history_fee_frac) - ON CONFLICT DO NOTHING; - - IF NOT FOUND - THEN - out_balance_ok=TRUE; - out_idempotent=TRUE; - RETURN; - END IF; - - out_idempotent=FALSE; - - -- Update reserve balance. - UPDATE exchange.reserves - SET - current_balance_frac=current_balance_frac-in_history_fee_frac - + CASE - WHEN current_balance_frac < in_history_fee_frac - THEN 100000000 - ELSE 0 - END, - current_balance_val=current_balance_val-in_history_fee_val - - CASE - WHEN current_balance_frac < in_history_fee_frac - THEN 1 - ELSE 0 - END - WHERE - reserve_pub=in_reserve_pub - AND ( (current_balance_val > in_history_fee_val) OR - ( (current_balance_frac >= in_history_fee_frac) AND - (current_balance_val >= in_history_fee_val) ) ); - - IF NOT FOUND - THEN - -- Either reserve does not exist, or balance insufficient. - -- Both we treat the same here as balance insufficient. - out_balance_ok=FALSE; - RETURN; - END IF; - - out_balance_ok=TRUE; -END $$; - - -CREATE OR REPLACE FUNCTION exchange_do_reserve_open_deposit( - IN in_coin_pub BYTEA, - IN in_known_coin_id INT8, - IN in_coin_sig BYTEA, - IN in_reserve_sig BYTEA, - IN in_reserve_pub BYTEA, - IN in_coin_total_val INT8, - IN in_coin_total_frac INT4, - OUT out_insufficient_funds BOOLEAN) -LANGUAGE plpgsql -AS $$ -BEGIN - -INSERT INTO exchange.reserves_open_deposits - (reserve_sig - ,reserve_pub - ,coin_pub - ,coin_sig - ,contribution_val - ,contribution_frac - ) - VALUES - (in_reserve_sig - ,in_reserve_pub - ,in_coin_pub - ,in_coin_sig - ,in_coin_total_val - ,in_coin_total_frac) - ON CONFLICT DO NOTHING; - -IF NOT FOUND -THEN - -- Idempotent request known, return success. - out_insufficient_funds=FALSE; - RETURN; -END IF; - - --- Check and update balance of the coin. -UPDATE exchange.known_coins - SET - remaining_frac=remaining_frac-in_coin_total_frac - + CASE - WHEN remaining_frac < in_coin_total_frac - THEN 100000000 - ELSE 0 - END, - remaining_val=remaining_val-in_coin_total_val - - CASE - WHEN remaining_frac < in_coin_total_frac - THEN 1 - ELSE 0 - END - WHERE coin_pub=in_coin_pub - AND ( (remaining_val > in_coin_total_val) OR - ( (remaining_frac >= in_coin_total_frac) AND - (remaining_val >= in_coin_total_val) ) ); - -IF NOT FOUND -THEN - -- Insufficient balance. - out_insufficient_funds=TRUE; - RETURN; -END IF; - --- Everything fine, return success! -out_insufficient_funds=FALSE; - -END $$; - - -CREATE OR REPLACE FUNCTION exchange_do_reserve_open( - IN in_reserve_pub BYTEA, - IN in_total_paid_val INT8, - IN in_total_paid_frac INT4, - IN in_reserve_payment_val INT8, - IN in_reserve_payment_frac INT4, - IN in_min_purse_limit INT4, - IN in_default_purse_limit INT4, - IN in_reserve_sig BYTEA, - IN in_desired_expiration INT8, - IN in_reserve_gc_delay INT8, - IN in_now INT8, - IN in_open_fee_val INT8, - IN in_open_fee_frac INT4, - OUT out_open_cost_val INT8, - OUT out_open_cost_frac INT4, - OUT out_final_expiration INT8, - OUT out_no_funds BOOLEAN) -LANGUAGE plpgsql -AS $$ -DECLARE - my_balance_val INT8; -DECLARE - my_balance_frac INT4; -DECLARE - my_cost_val INT8; -DECLARE - my_cost_tmp INT8; -DECLARE - my_cost_frac INT4; -DECLARE - my_years_tmp INT4; -DECLARE - my_years INT4; -DECLARE - my_needs_update BOOL; -DECLARE - my_purses_allowed INT8; -DECLARE - my_expiration_date INT8; -DECLARE - my_reserve_expiration INT8; -BEGIN - --- FIXME: use SELECT FOR UPDATE? -SELECT - purses_allowed - ,expiration_date - ,current_balance_val - ,current_balance_frac -INTO - my_purses_allowed - ,my_reserve_expiration - ,my_balance_val - ,my_balance_frac -FROM reserves -WHERE - reserve_pub=in_reserve_pub; - -IF NOT FOUND -THEN - -- FIXME: do we need to set a 'not found'? - RAISE NOTICE 'reserve not found'; - RETURN; -END IF; - --- Do not allow expiration time to start in the past already -IF (my_reserve_expiration < in_now) -THEN - my_expiration_date = in_now; -ELSE - my_expiration_date = my_reserve_expiration; -END IF; - -my_cost_val = 0; -my_cost_frac = 0; -my_needs_update = FALSE; -my_years = 0; - --- Compute years based on desired expiration time -IF (my_expiration_date < in_desired_expiration) -THEN - my_years = (31535999999999 + in_desired_expiration - my_expiration_date) / 31536000000000; - my_purses_allowed = in_default_purse_limit; - my_expiration_date = my_expiration_date + 31536000000000 * my_years; -END IF; - --- Increase years based on purses requested -IF (my_purses_allowed < in_min_purse_limit) -THEN - my_years = (31535999999999 + in_desired_expiration - in_now) / 31536000000000; - my_expiration_date = in_now + 31536000000000 * my_years; - my_years_tmp = (in_min_purse_limit + in_default_purse_limit - my_purses_allowed - 1) / in_default_purse_limit; - my_years = my_years + my_years_tmp; - my_purses_allowed = my_purses_allowed + (in_default_purse_limit * my_years_tmp); -END IF; - - --- Compute cost based on annual fees -IF (my_years > 0) -THEN - my_cost_val = my_years * in_open_fee_val; - my_cost_tmp = my_years * in_open_fee_frac / 100000000; - IF (CAST (my_cost_val + my_cost_tmp AS INT8) < my_cost_val) - THEN - out_open_cost_val=9223372036854775807; - out_open_cost_frac=2147483647; - out_final_expiration=my_expiration_date; - out_no_funds=FALSE; - RAISE NOTICE 'arithmetic issue computing amount'; - RETURN; - END IF; - my_cost_val = CAST (my_cost_val + my_cost_tmp AS INT8); - my_cost_frac = my_years * in_open_fee_frac % 100000000; - my_needs_update = TRUE; -END IF; - --- check if we actually have something to do -IF NOT my_needs_update -THEN - out_final_expiration = my_reserve_expiration; - out_open_cost_val = 0; - out_open_cost_frac = 0; - out_no_funds=FALSE; - RAISE NOTICE 'no change required'; - RETURN; -END IF; - --- Check payment (coins and reserve) would be sufficient. -IF ( (in_total_paid_val < my_cost_val) OR - ( (in_total_paid_val = my_cost_val) AND - (in_total_paid_frac < my_cost_frac) ) ) -THEN - out_open_cost_val = my_cost_val; - out_open_cost_frac = my_cost_frac; - out_no_funds=FALSE; - -- We must return a failure, which is indicated by - -- the expiration being below the desired expiration. - IF (my_reserve_expiration >= in_desired_expiration) - THEN - -- This case is relevant especially if the purse - -- count was to be increased and the payment was - -- insufficient to cover this for the full period. - RAISE NOTICE 'forcing low expiration time'; - out_final_expiration = 0; - ELSE - out_final_expiration = my_reserve_expiration; - END IF; - RAISE NOTICE 'amount paid too low'; - RETURN; -END IF; - --- Check reserve balance is sufficient. -IF (my_balance_val > in_reserve_payment_val) -THEN - IF (my_balance_frac >= in_reserve_payment_frac) - THEN - my_balance_val=my_balance_val - in_reserve_payment_val; - my_balance_frac=my_balance_frac - in_reserve_payment_frac; - ELSE - my_balance_val=my_balance_val - in_reserve_payment_val - 1; - my_balance_frac=my_balance_frac + 100000000 - in_reserve_payment_frac; - END IF; -ELSE - IF (my_balance_val = in_reserve_payment_val) AND (my_balance_frac >= in_reserve_payment_frac) - THEN - my_balance_val=0; - my_balance_frac=my_balance_frac - in_reserve_payment_frac; - ELSE - out_final_expiration = my_reserve_expiration; - out_open_cost_val = my_cost_val; - out_open_cost_frac = my_cost_frac; - out_no_funds=TRUE; - RAISE NOTICE 'reserve balance too low'; - RETURN; - END IF; -END IF; - -UPDATE reserves SET - current_balance_val=my_balance_val - ,current_balance_frac=my_balance_frac - ,gc_date=my_reserve_expiration + in_reserve_gc_delay - ,expiration_date=my_expiration_date - ,purses_allowed=my_purses_allowed -WHERE - reserve_pub=in_reserve_pub; - -out_final_expiration=my_expiration_date; -out_open_cost_val = my_cost_val; -out_open_cost_frac = my_cost_frac; -out_no_funds=FALSE; -RETURN; - -END $$; - -CREATE OR REPLACE FUNCTION insert_or_update_policy_details( - IN in_policy_hash_code BYTEA, - IN in_policy_json VARCHAR, - IN in_deadline INT8, - IN in_commitment_val INT8, - IN in_commitment_frac INT4, - IN in_accumulated_total_val INT8, - IN in_accumulated_total_frac INT4, - IN in_fee_val INT8, - IN in_fee_frac INT4, - IN in_transferable_val INT8, - IN in_transferable_frac INT4, - IN in_fulfillment_state SMALLINT, - OUT out_policy_details_serial_id INT8, - OUT out_accumulated_total_val INT8, - OUT out_accumulated_total_frac INT4, - OUT out_fulfillment_state SMALLINT) -LANGUAGE plpgsql -AS $$ -DECLARE - cur_commitment_val INT8; - cur_commitment_frac INT4; - cur_accumulated_total_val INT8; - cur_accumulated_total_frac INT4; -BEGIN - -- First, try to create a new entry. - INSERT INTO policy_details - (policy_hash_code, - policy_json, - deadline, - commitment_val, - commitment_frac, - accumulated_total_val, - accumulated_total_frac, - fee_val, - fee_frac, - transferable_val, - transferable_frac, - fulfillment_state) - VALUES (in_policy_hash_code, - in_policy_json, - in_deadline, - in_commitment_val, - in_commitment_frac, - in_accumulated_total_val, - in_accumulated_total_frac, - in_fee_val, - in_fee_frac, - in_transferable_val, - in_transferable_frac, - in_fulfillment_state) - ON CONFLICT (policy_hash_code) DO NOTHING - RETURNING policy_details_serial_id INTO out_policy_details_serial_id; - - -- If the insert was successful, return - -- We assume that the fullfilment_state was correct in first place. - IF FOUND THEN - out_accumulated_total_val = in_accumulated_total_val; - out_accumulated_total_frac = in_accumulated_total_frac; - out_fulfillment_state = in_fulfillment_state; - RETURN; - END IF; - - -- We had a conflict, grab the parts we need to update. - SELECT policy_details_serial_id, - commitment_val, - commitment_frac, - accumulated_total_val, - accumulated_total_frac - INTO out_policy_details_serial_id, - cur_commitment_val, - cur_commitment_frac, - cur_accumulated_total_val, - cur_accumulated_total_frac - FROM policy_details - WHERE policy_hash_code = in_policy_hash_code; - - -- calculate the new values (overflows throws exception) - out_accumulated_total_val = cur_accumulated_total_val + in_accumulated_total_val; - out_accumulated_total_frac = cur_accumulated_total_frac + in_accumulated_total_frac; - -- normalize - out_accumulated_total_val = out_accumulated_total_val + out_accumulated_total_frac / 100000000; - out_accumulated_total_frac = out_accumulated_total_frac % 100000000; - - IF (out_accumulated_total_val > (1 << 52)) - THEN - RAISE EXCEPTION 'accumulation overflow'; - END IF; - - - -- Set the fulfillment_state according to the values. - -- For now, we only update the state when it was INSUFFICIENT. - -- FIXME: What to do in case of Failure or other state? - IF (out_fullfillment_state = 1) -- INSUFFICIENT - THEN - IF (out_accumulated_total_val >= cur_commitment_val OR - (out_accumulated_total_val = cur_commitment_val AND - out_accumulated_total_frac >= cur_commitment_frac)) - THEN - out_fulfillment_state = 2; -- READY - END IF; - END IF; - - -- Now, update the record - UPDATE exchange.policy_details - SET - accumulated_val = out_accumulated_total_val, - accumulated_frac = out_accumulated_total_frac, - fulfillment_state = out_fulfillment_state - WHERE - policy_details_serial_id = out_policy_details_serial_id; -END $$; - -CREATE OR REPLACE FUNCTION batch_reserves_in( - IN in_reserve_pub BYTEA, - IN in_expiration_date INT8, - IN in_gc_date INT8, - IN in_wire_ref INT8, - IN in_credit_val INT8, - IN in_credit_frac INT4, - IN in_exchange_account_name VARCHAR, - IN in_exectution_date INT8, - IN in_wire_source_h_payto BYTEA, ---h_payto - IN in_payto_uri VARCHAR, - IN in_reserve_expiration INT8, - OUT out_reserve_found BOOLEAN, - OUT transaction_duplicate BOOLEAN, - OUT ruuid INT8) -LANGUAGE plpgsql -AS $$ -DECLARE - my_amount_val INT8; -DECLARE - my_amount_frac INT4; -BEGIN - - INSERT INTO reserves - (reserve_pub - ,current_balance_val - ,current_balance_frac - ,expiration_date - ,gc_date) - VALUES - (in_reserve_pub - ,in_credit_val - ,in_credit_frac - ,in_expiration_date - ,in_gc_date) - ON CONFLICT DO NOTHING - RETURNING reserve_uuid INTO ruuid; - - IF FOUND - THEN - -- We made a change, so the reserve did not previously exist. - out_reserve_found = FALSE; - ELSE - -- We made no change, which means the reserve existed. - out_reserve_found = TRUE; - END IF; - - --SIMPLE INSERT ON CONFLICT DO NOTHING - INSERT INTO wire_targets - (wire_target_h_payto - ,payto_uri) - VALUES - (in_wire_source_h_payto - ,in_payto_uri) - ON CONFLICT DO NOTHING; - - INSERT INTO reserves_in - (reserve_pub - ,wire_reference - ,credit_val - ,credit_frac - ,exchange_account_section - ,wire_source_h_payto - ,execution_date) - VALUES - (in_reserve_pub - ,in_wire_ref - ,in_credit_val - ,in_credit_frac - ,in_exchange_account_name - ,in_wire_source_h_payto - ,in_expiration_date); - - --IF THE INSERTION WAS A SUCCESS IT MEANS NO DUPLICATED TRANSACTION - IF FOUND - THEN - transaction_duplicate = FALSE; - IF out_reserve_found - THEN - UPDATE reserves - SET - current_balance_frac = current_balance_frac+in_credit_frac - - CASE - WHEN current_balance_frac + in_credit_frac >= 100000000 - THEN 100000000 - ELSE 1 - END - ,current_balance_val = current_balance_val+in_credit_val - + CASE - WHEN current_balance_frac + in_credit_frac >= 100000000 - THEN 1 - ELSE 0 - END - ,expiration_date=GREATEST(expiration_date,in_expiration_date) - ,gc_date=GREATEST(gc_date,in_expiration_date) - WHERE reserves.reserve_pub=in_reserve_pub; - out_reserve_found = TRUE; - RETURN; - ELSE - out_reserve_found=FALSE; - RETURN; - END IF; - out_reserve_found = TRUE; - ELSE - transaction_duplicate = TRUE; - IF out_reserve_found - THEN - out_reserve_found = TRUE; - RETURN; - ELSE - out_reserve_found = FALSE; - RETURN; - END IF; - END IF; -END $$; - -COMMIT; -- cgit v1.2.3