[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] branch master updated: refactor procedures.sql
|
From: |
gnunet |
|
Subject: |
[taler-exchange] branch master updated: refactor procedures.sql |
|
Date: |
Tue, 06 Dec 2022 13:29:27 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
The following commit(s) were added to refs/heads/master by this push:
new 87198f12 refactor procedures.sql
87198f12 is described below
commit 87198f124c989d014adc9a2bae5098cf80555d62
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Tue Dec 6 13:29:23 2022 +0100
refactor procedures.sql
---
src/exchangedb/Makefile.am | 9 +-
src/exchangedb/exchange_do_account_merge.sql | 15 +
src/exchangedb/exchange_do_batch_reserves_in.sql | 130 +
src/exchangedb/exchange_do_batch_withdraw.sql | 106 +
.../exchange_do_batch_withdraw_insert.sql | 125 +
src/exchangedb/exchange_do_deposit.sql | 171 ++
src/exchangedb/exchange_do_expire_purse.sql | 99 +
src/exchangedb/exchange_do_gc.sql | 144 ++
src/exchangedb/exchange_do_history_request.sql | 85 +
...exchange_do_insert_or_update_policy_details.sql | 128 +
src/exchangedb/exchange_do_melt.sql | 186 ++
src/exchangedb/exchange_do_purse_deposit.sql | 244 ++
src/exchangedb/exchange_do_purse_merge.sql | 216 ++
src/exchangedb/exchange_do_recoup_by_reserve.sql | 82 +
src/exchangedb/exchange_do_recoup_to_coin.sql | 142 ++
src/exchangedb/exchange_do_recoup_to_reserve.sql | 144 ++
src/exchangedb/exchange_do_refund.sql | 211 ++
src/exchangedb/exchange_do_reserve_open.sql | 210 ++
.../exchange_do_reserve_open_deposit.sql | 87 +
src/exchangedb/exchange_do_reserve_purse.sql | 157 ++
src/exchangedb/exchange_do_withdraw.sql | 199 ++
src/exchangedb/pg_batch_reserves_in_insert.c | 11 +-
src/exchangedb/pg_persist_policy_details.c | 2 +-
src/exchangedb/plugin_exchangedb_postgres.c | 59 -
src/exchangedb/procedures.sql | 2641 --------------------
src/exchangedb/procedures.sql.in | 41 +
26 files changed, 2937 insertions(+), 2707 deletions(-)
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am
index 1d4ba1f5..4d9bfcb5 100644
--- a/src/exchangedb/Makefile.am
+++ b/src/exchangedb/Makefile.am
@@ -15,9 +15,11 @@ pkgcfg_DATA = \
sqldir = $(prefix)/share/taler/sql/exchange/
sqlinputs = \
+ exchange_do_*.sql \
+ procedures.sql.in \
0002-*.sql \
- 0003-*.sql \
exchange-0002.sql.in \
+ 0003-*.sql \
exchange-0003.sql.in
sql_DATA = \
@@ -38,6 +40,11 @@ CLEANFILES = \
exchange-0002.sql \
exchange-0003.sql
+procedures.sql: procedures.sql.in exchange_do_*.sql
+ chmod +w $@ || true
+ gcc -E -P -undef - < procedures.sql.in 2>/dev/null | sed -e "s/--.*//"
| awk 'NF' - >$@
+ chmod ugo-w $@
+
exchange-0002.sql: exchange-0002.sql.in 0002-*.sql
chmod +w $@ || true
gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e
"s/--.*//" | awk 'NF' - >$@
diff --git a/src/exchangedb/exchange_do_account_merge.sql
b/src/exchangedb/exchange_do_account_merge.sql
new file mode 100644
index 00000000..723154f1
--- /dev/null
+++ b/src/exchangedb/exchange_do_account_merge.sql
@@ -0,0 +1,15 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
diff --git a/src/exchangedb/exchange_do_batch_reserves_in.sql
b/src/exchangedb/exchange_do_batch_reserves_in.sql
new file mode 100644
index 00000000..faad2ca8
--- /dev/null
+++ b/src/exchangedb/exchange_do_batch_reserves_in.sql
@@ -0,0 +1,130 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+CREATE OR REPLACE FUNCTION 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 $$;
diff --git a/src/exchangedb/exchange_do_batch_withdraw.sql
b/src/exchangedb/exchange_do_batch_withdraw.sql
new file mode 100644
index 00000000..fedb7e91
--- /dev/null
+++ b/src/exchangedb/exchange_do_batch_withdraw.sql
@@ -0,0 +1,106 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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.';
+
+
+
diff --git a/src/exchangedb/exchange_do_batch_withdraw_insert.sql
b/src/exchangedb/exchange_do_batch_withdraw_insert.sql
new file mode 100644
index 00000000..98db840f
--- /dev/null
+++ b/src/exchangedb/exchange_do_batch_withdraw_insert.sql
@@ -0,0 +1,125 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
diff --git a/src/exchangedb/exchange_do_deposit.sql
b/src/exchangedb/exchange_do_deposit.sql
new file mode 100644
index 00000000..a2f5ba53
--- /dev/null
+++ b/src/exchangedb/exchange_do_deposit.sql
@@ -0,0 +1,171 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
diff --git a/src/exchangedb/exchange_do_expire_purse.sql
b/src/exchangedb/exchange_do_expire_purse.sql
new file mode 100644
index 00000000..82756abc
--- /dev/null
+++ b/src/exchangedb/exchange_do_expire_purse.sql
@@ -0,0 +1,99 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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).';
+
+
diff --git a/src/exchangedb/exchange_do_gc.sql
b/src/exchangedb/exchange_do_gc.sql
new file mode 100644
index 00000000..c6331c18
--- /dev/null
+++ b/src/exchangedb/exchange_do_gc.sql
@@ -0,0 +1,144 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE 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 $$;
+
+
+
diff --git a/src/exchangedb/exchange_do_history_request.sql
b/src/exchangedb/exchange_do_history_request.sql
new file mode 100644
index 00000000..2f604174
--- /dev/null
+++ b/src/exchangedb/exchange_do_history_request.sql
@@ -0,0 +1,85 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
+
diff --git a/src/exchangedb/exchange_do_insert_or_update_policy_details.sql
b/src/exchangedb/exchange_do_insert_or_update_policy_details.sql
new file mode 100644
index 00000000..c7fe64d1
--- /dev/null
+++ b/src/exchangedb/exchange_do_insert_or_update_policy_details.sql
@@ -0,0 +1,128 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
diff --git a/src/exchangedb/exchange_do_melt.sql
b/src/exchangedb/exchange_do_melt.sql
new file mode 100644
index 00000000..c0290b56
--- /dev/null
+++ b/src/exchangedb/exchange_do_melt.sql
@@ -0,0 +1,186 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
+
diff --git a/src/exchangedb/exchange_do_purse_deposit.sql
b/src/exchangedb/exchange_do_purse_deposit.sql
new file mode 100644
index 00000000..cddbd8d4
--- /dev/null
+++ b/src/exchangedb/exchange_do_purse_deposit.sql
@@ -0,0 +1,244 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
+
+
diff --git a/src/exchangedb/exchange_do_purse_merge.sql
b/src/exchangedb/exchange_do_purse_merge.sql
new file mode 100644
index 00000000..2e799c9f
--- /dev/null
+++ b/src/exchangedb/exchange_do_purse_merge.sql
@@ -0,0 +1,216 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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.';
+
diff --git a/src/exchangedb/exchange_do_recoup_by_reserve.sql
b/src/exchangedb/exchange_do_recoup_by_reserve.sql
new file mode 100644
index 00000000..6a7ea725
--- /dev/null
+++ b/src/exchangedb/exchange_do_recoup_by_reserve.sql
@@ -0,0 +1,82 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
diff --git a/src/exchangedb/exchange_do_recoup_to_coin.sql
b/src/exchangedb/exchange_do_recoup_to_coin.sql
new file mode 100644
index 00000000..5598ec20
--- /dev/null
+++ b/src/exchangedb/exchange_do_recoup_to_coin.sql
@@ -0,0 +1,142 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
diff --git a/src/exchangedb/exchange_do_recoup_to_reserve.sql
b/src/exchangedb/exchange_do_recoup_to_reserve.sql
new file mode 100644
index 00000000..39baba8f
--- /dev/null
+++ b/src/exchangedb/exchange_do_recoup_to_reserve.sql
@@ -0,0 +1,144 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
+
+
diff --git a/src/exchangedb/exchange_do_refund.sql
b/src/exchangedb/exchange_do_refund.sql
new file mode 100644
index 00000000..ceaabfe1
--- /dev/null
+++ b/src/exchangedb/exchange_do_refund.sql
@@ -0,0 +1,211 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
+
diff --git a/src/exchangedb/exchange_do_reserve_open.sql
b/src/exchangedb/exchange_do_reserve_open.sql
new file mode 100644
index 00000000..5e80f713
--- /dev/null
+++ b/src/exchangedb/exchange_do_reserve_open.sql
@@ -0,0 +1,210 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
diff --git a/src/exchangedb/exchange_do_reserve_open_deposit.sql
b/src/exchangedb/exchange_do_reserve_open_deposit.sql
new file mode 100644
index 00000000..72512270
--- /dev/null
+++ b/src/exchangedb/exchange_do_reserve_open_deposit.sql
@@ -0,0 +1,87 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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 $$;
+
diff --git a/src/exchangedb/exchange_do_reserve_purse.sql
b/src/exchangedb/exchange_do_reserve_purse.sql
new file mode 100644
index 00000000..a110c85a
--- /dev/null
+++ b/src/exchangedb/exchange_do_reserve_purse.sql
@@ -0,0 +1,157 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE FUNCTION exchange_do_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.';
+
+
+
+
diff --git a/src/exchangedb/exchange_do_withdraw.sql
b/src/exchangedb/exchange_do_withdraw.sql
new file mode 100644
index 00000000..9689bae5
--- /dev/null
+++ b/src/exchangedb/exchange_do_withdraw.sql
@@ -0,0 +1,199 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION exchange_do_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';
+
diff --git a/src/exchangedb/pg_batch_reserves_in_insert.c
b/src/exchangedb/pg_batch_reserves_in_insert.c
index 4a1a2792..455f080d 100644
--- a/src/exchangedb/pg_batch_reserves_in_insert.c
+++ b/src/exchangedb/pg_batch_reserves_in_insert.c
@@ -62,7 +62,8 @@ notify_on_reserve (struct PostgresClosure *pg,
enum GNUNET_DB_QueryStatus
TEH_PG_batch_reserves_in_insert (void *cls,
- const struct TALER_EXCHANGEDB_ReserveInInfo
*reserves,
+ const struct
+ TALER_EXCHANGEDB_ReserveInInfo *reserves,
unsigned int reserves_length,
enum GNUNET_DB_QueryStatus *results)
{
@@ -83,7 +84,7 @@ TEH_PG_batch_reserves_in_insert (void *cls,
"out_reserve_found AS conflicted"
",transaction_duplicate"
",ruuid AS reserve_uuid"
- " FROM batch_reserves_in"
+ " FROM exchange_do_batch_reserves_in"
" ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11);");
expiry = GNUNET_TIME_absolute_to_timestamp (
GNUNET_TIME_absolute_add (reserves->execution_time.abs_time,
@@ -101,7 +102,7 @@ TEH_PG_batch_reserves_in_insert (void *cls,
time; we do this before adding the actual transaction to "reserves_in",
as for a new reserve it can't be a duplicate 'add' operation, and as
the 'add' operation needs the reserve entry as a foreign key. */
- for (unsigned int i=0;i<reserves_length;i++)
+ for (unsigned int i = 0; i<reserves_length; i++)
{
const struct TALER_EXCHANGEDB_ReserveInInfo *reserve = &reserves[i];
struct GNUNET_PQ_QueryParam params[] = {
@@ -136,7 +137,7 @@ TEH_PG_batch_reserves_in_insert (void *cls,
params,
rs);
if (qs1 < 0)
- return qs1;
+ return qs1;
notify_on_reserve (pg,
&reserve->reserve_pub);
GNUNET_assert (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS != qs1);
@@ -144,7 +145,7 @@ TEH_PG_batch_reserves_in_insert (void *cls,
? GNUNET_DB_STATUS_SUCCESS_NO_RESULTS
: GNUNET_DB_STATUS_SUCCESS_ONE_RESULT;
if ( (! conflicted) && transaction_duplicate)
- TEH_PG_rollback(pg);
+ TEH_PG_rollback (pg);
}
return reserves_length;
}
diff --git a/src/exchangedb/pg_persist_policy_details.c
b/src/exchangedb/pg_persist_policy_details.c
index 28e2e4c5..2b778787 100644
--- a/src/exchangedb/pg_persist_policy_details.c
+++ b/src/exchangedb/pg_persist_policy_details.c
@@ -59,7 +59,7 @@ TEH_PG_persist_policy_details (
GNUNET_PQ_result_spec_end
};
-
+ // FIXME: prepare missing!!?!
return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
"call_insert_or_update_policy_details",
params,
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c
b/src/exchangedb/plugin_exchangedb_postgres.c
index cdb9b623..d04df467 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -249,8 +249,6 @@ prepare_statements (struct PostgresClosure *pg)
" FROM wire_targets"
" WHERE wire_target_h_payto=$1"
" LIMIT 1;"),
-
-
/* Used in #postgres_ensure_coin_known() */
GNUNET_PQ_make_prepare (
"get_known_coin_dh",
@@ -289,12 +287,6 @@ prepare_statements (struct PostgresClosure *pg)
" ON (ref.coin_pub=dep.coin_pub AND
ref.deposit_serial_id=dep.deposit_serial_id)"
" WHERE ref.refund_serial_id=$1"
" GROUP BY (dep.amount_with_fee_val, dep.amount_with_fee_frac);"),
- /* Used in #postgres_do_account_merge() */
- GNUNET_PQ_make_prepare (
- "call_account_merge",
- "SELECT 1"
- " FROM exchange_do_account_merge"
- " ($1, $2, $3);"),
/* Used in #postgres_update_kyc_requirement_by_row() */
GNUNET_PQ_make_prepare (
"update_legitimization_process",
@@ -495,56 +487,6 @@ postgres_get_policy_details (
}
-/* Persist the details to a policy in the policy_details table. If there
- * already exists a policy, update the fields accordingly.
- *
- * @param details The policy details that should be persisted. If an entry for
- * the given details->hash_code exists, the values will be updated.
- * @param[out] policy_details_serial_id The row ID of the policy details
- * @param[out] accumulated_total The total amount accumulated in that policy
- * @param[out] fulfillment_state The state of policy. If the state was
Insufficient prior to the call and the provided deposit raises the
accumulated_total above the commitment, it will be set to Ready.
- * @return query execution status
- */
-static enum GNUNET_DB_QueryStatus
-postgres_persist_policy_details (
- void *cls,
- const struct TALER_PolicyDetails *details,
- uint64_t *policy_details_serial_id,
- struct TALER_Amount *accumulated_total,
- enum TALER_PolicyFulfillmentState *fulfillment_state)
-{
- struct PostgresClosure *pg = cls;
- struct GNUNET_PQ_QueryParam params[] = {
- GNUNET_PQ_query_param_auto_from_type (&details->hash_code),
- TALER_PQ_query_param_json (details->policy_json),
- GNUNET_PQ_query_param_timestamp (&details->deadline),
- TALER_PQ_query_param_amount (&details->commitment),
- TALER_PQ_query_param_amount (&details->accumulated_total),
- TALER_PQ_query_param_amount (&details->policy_fee),
- TALER_PQ_query_param_amount (&details->transferable_amount),
- GNUNET_PQ_query_param_auto_from_type (&details->fulfillment_state),
- (details->no_policy_fulfillment_id)
- ? GNUNET_PQ_query_param_null ()
- : GNUNET_PQ_query_param_uint64 (&details->policy_fulfillment_id),
- GNUNET_PQ_query_param_end
- };
- struct GNUNET_PQ_ResultSpec rs[] = {
- GNUNET_PQ_result_spec_uint64 ("policy_details_serial_id",
- policy_details_serial_id),
- TALER_PQ_RESULT_SPEC_AMOUNT ("accumulated_total",
- accumulated_total),
- GNUNET_PQ_result_spec_uint32 ("fulfillment_state",
- fulfillment_state),
- GNUNET_PQ_result_spec_end
- };
-
- return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-
"call_insert_or_update_policy_details",
- params,
- rs);
-}
-
-
/**
* Perform melt operation, checking for sufficient balance
* of the coin and possibly persisting the melt details.
@@ -5019,7 +4961,6 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
plugin = GNUNET_new (struct TALER_EXCHANGEDB_Plugin);
plugin->cls = pg;
plugin->get_policy_details = &postgres_get_policy_details;
- plugin->persist_policy_details = &postgres_persist_policy_details;
plugin->add_policy_fulfillment_proof =
&postgres_add_policy_fulfillment_proof;
plugin->do_melt = &postgres_do_melt;
plugin->do_refund = &postgres_do_refund;
diff --git a/src/exchangedb/procedures.sql b/src/exchangedb/procedures.sql
deleted file mode 100644
index a869d473..00000000
--- 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 <http://www.gnu.org/licenses/>
---
-
-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;
diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in
new file mode 100644
index 00000000..b412b66d
--- /dev/null
+++ b/src/exchangedb/procedures.sql.in
@@ -0,0 +1,41 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+BEGIN;
+
+SET search_path TO exchange;
+
+#include "exchange_do_withdraw.sql"
+#include "exchange_do_batch_withdraw.sql"
+#include "exchange_do_batch_withdraw_insert.sql"
+#include "exchange_do_recoup_by_reserve.sql"
+#include "exchange_do_deposit.sql"
+#include "exchange_do_melt.sql"
+#include "exchange_do_refund.sql"
+#include "exchange_do_recoup_to_reserve.sql"
+#include "exchange_do_recoup_to_coin.sql"
+#include "exchange_do_gc.sql"
+#include "exchange_do_purse_deposit.sql"
+#include "exchange_do_reserve_purse.sql"
+#include "exchange_do_expire_purse.sql"
+#include "exchange_do_history_request.sql"
+#include "exchange_do_reserve_open_deposit.sql"
+#include "exchange_do_reserve_open.sql"
+#include "exchange_do_insert_or_update_policy_details.sql"
+#include "exchange_do_batch_reserves_in.sql"
+
+
+COMMIT;
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [taler-exchange] branch master updated: refactor procedures.sql,
gnunet <=