-- -- 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 -- -- Everything in one big transaction BEGIN; SET search_path TO exchange; --------------------------------------------------------------------------- -- Stored procedures --------------------------------------------------------------------------- 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_extension_blocked BOOLEAN, IN in_extension_details VARCHAR, OUT out_exchange_timestamp INT8, OUT out_balance_ok BOOLEAN, OUT out_conflict BOOLEAN) LANGUAGE plpgsql AS $$ DECLARE wtsi INT8; -- wire target serial id DECLARE xdi INT8; -- eXstension details serial id BEGIN -- Shards: INSERT extension_details (by extension_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) IF NOT NULL in_extension_details THEN INSERT INTO exchange.extension_details (extension_options) VALUES (in_extension_details) RETURNING extension_details_serial_id INTO xdi; ELSE xdi=NULL; END IF; 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 ,extension_blocked ,extension_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_extension_blocked ,xdi) 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; 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, 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_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 partner_serial_id ,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 INTO my_amount_val ,my_amount_frac 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; 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; -- ... and mark purse as finished. -- FIXME: combine with UPDATE above? UPDATE purse_requests SET finished=true WHERE purse_pub=in_purse_pub; 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_finished BOOLEAN; BEGIN IF in_partner_url IS NULL THEN my_partner_serial_id=0; 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 ,finished INTO my_amount_val ,my_amount_frac ,my_purse_fee_val ,my_purse_fee_frac ,my_finished 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; ASSERT NOT my_finished, 'internal invariant failed'; -- 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; -- 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; -- ... and mark purse as finished. UPDATE exchange.purse_requests SET finished=true WHERE purse_pub=in_purse_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 (0 ,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, OUT out_found BOOLEAN) LANGUAGE plpgsql AS $$ DECLARE my_purse_pub BYTEA; DECLARE my_deposit record; BEGIN SELECT purse_pub INTO my_purse_pub FROM exchange.purse_requests WHERE (purse_expiration >= in_start_time) AND (purse_expiration < in_end_time) AND (NOT finished) AND (NOT refunded) ORDER BY purse_expiration ASC LIMIT 1; out_found = FOUND; IF NOT FOUND THEN RETURN; END IF; UPDATE exchange.purse_requests SET refunded=TRUE, finished=TRUE WHERE purse_pub=my_purse_pub; INSERT INTO exchange.purse_refunds (purse_pub) VALUES (my_purse_pub); -- 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) 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 ,request_timestamp ,coin_pub ,coin_sig ,contribution_val ,contribution_frac ) VALUES (in_reserve_sig ,in_reserve_pub ,in_request_timestamp ,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_reserve_sig BYTEA, IN in_desired_expiration 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 $$ BEGIN -- FIXME: implement! END $$; COMMIT;