[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] 01/02: more optimizations of tables with foreign keys
|
From: |
gnunet |
|
Subject: |
[taler-exchange] 01/02: more optimizations of tables with foreign keys |
|
Date: |
Sun, 10 Jan 2021 00:54:33 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
commit 02ecf68a3d9edde9ef48650f64b7332af845beee
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sat Jan 9 13:18:01 2021 +0100
more optimizations of tables with foreign keys
---
src/exchangedb/exchange-0002.sql | 50 +++++++++
src/exchangedb/plugin_exchangedb_postgres.c | 85 +++++++++------
src/include/taler_exchangedb_plugin.h | 154 +++++++++++++++++++++-------
3 files changed, 218 insertions(+), 71 deletions(-)
diff --git a/src/exchangedb/exchange-0002.sql b/src/exchangedb/exchange-0002.sql
index 26724708..3d17395b 100644
--- a/src/exchangedb/exchange-0002.sql
+++ b/src/exchangedb/exchange-0002.sql
@@ -231,6 +231,56 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
IS 'Identifies the h_blind_ev of the recouped coin (as h_coin_ev).';
+-- Change 'rc' in refresh_transfer_keys and refresh_revealed_coins tables to
'melt_serial_id'
+ALTER TABLE refresh_transfer_keys
+ ADD COLUMN melt_serial_id INT8 REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE;
+UPDATE refresh_transfer_keys
+ SET melt_serial_id=d.melt_serial_id
+ FROM refresh_transfer_keys o
+ INNER JOIN refresh_commitments d ON (d.rc = o.rc);
+ALTER TABLE refresh_transfer_keys
+ ALTER COLUMN melt_serial_id SET NOT NULL;
+ALTER TABLE refresh_transfer_keys
+ DROP COLUMN rc;
+COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the operation.';
+
+ALTER TABLE refresh_revealed_coins
+ ADD COLUMN melt_serial_id INT8 REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE;
+UPDATE refresh_revealed_coins
+ SET melt_serial_id=d.melt_serial_id
+ FROM refresh_revealed_coins o
+ INNER JOIN refresh_commitments d ON (d.rc = o.rc);
+ALTER TABLE refresh_revealed_coins
+ ALTER COLUMN melt_serial_id SET NOT NULL;
+ALTER TABLE refresh_revealed_coins
+ DROP COLUMN rc;
+COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
+ IS 'Identifies the refresh commitment (rc) of the operation.';
+
+
+-- Change 'merchant_pub' and 'h_contract_terms' and 'known_coin_id' in
'refunds' table
+-- to 'deposit_serial_id' instead!
+ALTER TABLE refunds
+ ADD COLUMN deposit_serial_id INT8 REFERENCES deposits (deposit_serial_id) ON
DELETE CASCADE;
+UPDATE refunds
+ SET deposit_serial_id=d.deposit_serial_id
+ FROM refunds o
+ INNER JOIN deposits d
+ ON ( (d.known_coin_id = o.known_coin_id) AND
+ (d.h_contract_terms = o.h_contract_terms) AND
+ (d.merchant_pub = o.merchant_pub) );
+ALTER TABLE refunds
+ ALTER COLUMN deposit_serial_id SET NOT NULL;
+ALTER TABLE refunds
+ DROP COLUMN merchant_pub,
+ DROP COLUMN h_contract_terms,
+ DROP COLUMN known_coin_id;
+COMMENT ON COLUMN refunds.deposit_serial_id
+ IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id.
Multiple deposits may match a refund, this only identifies one of them.';
+
+
+
-- Create additional tables...
CREATE TABLE IF NOT EXISTS auditors
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c
b/src/exchangedb/plugin_exchangedb_postgres.c
index 6d8e7273..d8dbd224 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -781,16 +781,22 @@ postgres_get_session (void *cls)
/* Store information about the desired denominations for a
refresh operation, used in #postgres_insert_refresh_reveal() */
GNUNET_PQ_make_prepare ("insert_refresh_revealed_coin",
+ "WITH rcx AS"
+ " (SELECT melt_serial_id"
+ " FROM refresh_commitments"
+ " WHERE rc=$1)"
"INSERT INTO refresh_revealed_coins "
- "(rc "
+ "(melt_serial_id "
",freshcoin_index "
",link_sig "
",denominations_serial "
",coin_ev"
",h_coin_ev"
",ev_sig"
- ") SELECT $1, $2, $3, denominations_serial, $5,
$6, $7 "
+ ") SELECT rcx.melt_serial_id, $2, $3, "
+ " denominations_serial, $5, $6, $7 "
" FROM denominations"
+ " CROSS JOIN rcx"
" WHERE denom_pub_hash=$4;",
7),
/* Obtain information about the coins created in a refresh
@@ -805,6 +811,8 @@ postgres_get_session (void *cls)
" FROM refresh_revealed_coins"
" JOIN denominations denom "
" USING (denominations_serial)"
+ " JOIN refresh_commitments"
+ " USING (melt_serial_id)"
" WHERE rc=$1"
" ORDER BY freshcoin_index ASC;",
1),
@@ -813,11 +821,12 @@ postgres_get_session (void *cls)
keys we learned */
GNUNET_PQ_make_prepare ("insert_refresh_transfer_keys",
"INSERT INTO refresh_transfer_keys "
- "(rc"
+ "(melt_serial_id"
",transfer_pub"
",transfer_privs"
- ") VALUES "
- "($1, $2, $3);",
+ ") SELECT melt_serial_id, $2, $3"
+ " FROM refresh_commitments"
+ " WHERE rc=$1",
3),
/* Used in #postgres_get_refresh_reveal() to retrieve transfer
keys from /refresh/reveal */
@@ -826,23 +835,24 @@ postgres_get_session (void *cls)
" transfer_pub"
",transfer_privs"
" FROM refresh_transfer_keys"
+ " JOIN refresh_commitments"
+ " USING (melt_serial_id)"
" WHERE rc=$1;",
1),
-
-
/* Used in #postgres_insert_refund() to store refund information */
GNUNET_PQ_make_prepare ("insert_refund",
"INSERT INTO refunds "
- "(known_coin_id "
- ",merchant_pub "
+ "(deposit_serial_id "
",merchant_sig "
- ",h_contract_terms "
",rtransaction_id "
",amount_with_fee_val "
",amount_with_fee_frac "
- ") SELECT known_coin_id, $2, $3, $4, $5, $6, $7"
- " FROM known_coins"
- " WHERE coin_pub=$1",
+ ") SELECT deposit_serial_id, $3, $5, $6, $7"
+ " FROM deposits"
+ " JOIN known_coins USING (known_coin_id)"
+ " WHERE coin_pub=$1"
+ " AND h_contract_terms=$4"
+ " AND merchant_pub=$2",
7),
/* Query the 'refunds' by coin public key */
GNUNET_PQ_make_prepare ("get_refunds_by_coin",
@@ -851,12 +861,13 @@ postgres_get_session (void *cls)
",merchant_sig"
",h_contract_terms"
",rtransaction_id"
- ",amount_with_fee_val"
- ",amount_with_fee_frac"
+ ",refunds.amount_with_fee_val"
+ ",refunds.amount_with_fee_frac"
",denom.fee_refund_val "
",denom.fee_refund_frac "
",refund_serial_id"
" FROM refunds"
+ " JOIN deposits USING (deposit_serial_id)"
" JOIN known_coins USING (known_coin_id)"
" JOIN denominations denom USING
(denominations_serial)"
" WHERE coin_pub=$1;",
@@ -864,9 +875,10 @@ postgres_get_session (void *cls)
/* Query the 'refunds' by coin public key, merchant_pub and contract
hash */
GNUNET_PQ_make_prepare ("get_refunds_by_coin_and_contract",
"SELECT"
- " amount_with_fee_val"
- ",amount_with_fee_frac"
+ " refunds.amount_with_fee_val"
+ ",refunds.amount_with_fee_frac"
" FROM refunds"
+ " JOIN deposits USING (deposit_serial_id)"
" JOIN known_coins USING (known_coin_id)"
" WHERE coin_pub=$1"
" AND merchant_pub=$2"
@@ -881,10 +893,11 @@ postgres_get_session (void *cls)
",rtransaction_id"
",denom.denom_pub"
",kc.coin_pub"
- ",amount_with_fee_val"
- ",amount_with_fee_frac"
+ ",refunds.amount_with_fee_val"
+ ",refunds.amount_with_fee_frac"
",refund_serial_id"
" FROM refunds"
+ " JOIN deposits USING (deposit_serial_id)"
" JOIN known_coins kc USING (known_coin_id)"
" JOIN denominations denom ON
(kc.denominations_serial = denom.denominations_serial)"
" WHERE refund_serial_id>=$1"
@@ -1086,9 +1099,9 @@ postgres_get_session (void *cls)
",rrc.link_sig"
" FROM refresh_commitments"
" JOIN refresh_revealed_coins rrc"
- " USING (rc)"
+ " USING (melt_serial_id)"
" JOIN refresh_transfer_keys tp"
- " USING (rc)"
+ " USING (melt_serial_id)"
" JOIN denominations denoms"
" ON (rrc.denominations_serial =
denoms.denominations_serial)"
" WHERE old_known_coin_id="
@@ -1239,16 +1252,17 @@ postgres_get_session (void *cls)
",wire_deadline"
",tiny"
",done"
- " FROM deposits"
+ " FROM deposits d"
" JOIN known_coins USING (known_coin_id)"
" WHERE wire_deadline >= $1"
" AND wire_deadline < $2"
" AND NOT (EXISTS (SELECT 1"
" FROM refunds"
- " WHERE (refunds.known_coin_id =
deposits.known_coin_id))"
+ " JOIN deposits dx USING
(deposit_serial_id)"
+ " WHERE (dx.known_coin_id =
d.known_coin_id))"
" OR EXISTS (SELECT 1"
" FROM aggregation_tracking"
- " WHERE
(aggregation_tracking.deposit_serial_id = deposits.deposit_serial_id)))"
+ " WHERE
(aggregation_tracking.deposit_serial_id = d.deposit_serial_id)))"
" ORDER BY wire_deadline ASC",
2),
/* Used in #postgres_select_wire_out_above_serial_id() */
@@ -1364,10 +1378,10 @@ postgres_get_session (void *cls)
" FROM recoup_refresh"
" INNER JOIN refresh_revealed_coins rrc"
" USING (rrc_serial)"
- " INNER JOIN refresh_commitments rc"
- " ON (rrc.rc = rc.rc)"
+ " INNER JOIN refresh_commitments rfc"
+ " ON (rrc.melt_serial_id =
rfc.melt_serial_id)"
" INNER JOIN known_coins old_coins"
- " ON (rc.old_known_coin_id =
old_coins.known_coin_id)"
+ " ON (rfc.old_known_coin_id =
old_coins.known_coin_id)"
" INNER JOIN known_coins new_coins"
" ON (new_coins.known_coin_id =
recoup_refresh.known_coin_id)"
" INNER JOIN denominations new_denoms"
@@ -1442,7 +1456,7 @@ postgres_get_session (void *cls)
" (SELECT rrc.rrc_serial"
" FROM refresh_commitments"
" JOIN refresh_revealed_coins rrc"
- " USING (rc)"
+ " USING (melt_serial_id)"
" WHERE old_known_coin_id="
" (SELECT known_coin_id"
" FROM known_coins"
@@ -1518,10 +1532,10 @@ postgres_get_session (void *cls)
" FROM recoup_refresh"
" JOIN refresh_revealed_coins rrc"
" USING (rrc_serial)"
- " JOIN refresh_commitments rc"
- " ON (rrc.rc = rc.rc)"
+ " JOIN refresh_commitments rfc"
+ " ON (rrc.melt_serial_id =
rfc.melt_serial_id)"
" JOIN known_coins old_coins"
- " ON (rc.old_known_coin_id =
old_coins.known_coin_id)"
+ " ON (rfc.old_known_coin_id =
old_coins.known_coin_id)"
" JOIN known_coins coins"
" ON (recoup_refresh.known_coin_id =
coins.known_coin_id)"
" JOIN denominations denoms"
@@ -1543,7 +1557,7 @@ postgres_get_session (void *cls)
"SELECT"
" okc.coin_pub AS old_coin_pub"
" FROM refresh_revealed_coins rrc"
- " JOIN refresh_commitments rcom USING (rc)"
+ " JOIN refresh_commitments rcom USING
(melt_serial_id)"
" JOIN known_coins okc ON
(rcom.old_known_coin_id = okc.known_coin_id)"
" WHERE h_coin_ev=$1"
" LIMIT 1;",
@@ -2041,6 +2055,7 @@ postgres_get_session (void *cls)
",rrc_serial"
",denominations_serial"
" FROM refresh_revealed_coins"
+ " JOIN refresh_commitments USING (melt_serial_id)"
" ORDER BY rrc_serial ASC;",
0),
GNUNET_PQ_make_prepare (
@@ -2051,6 +2066,7 @@ postgres_get_session (void *cls)
",transfer_pub"
",transfer_privs"
" FROM refresh_transfer_keys"
+ " JOIN refresh_commitments USING (melt_serial_id)"
" ORDER BY rtc_serial ASC;",
0),
GNUNET_PQ_make_prepare ("select_above_serial_by_table_deposits",
@@ -2080,10 +2096,11 @@ postgres_get_session (void *cls)
",merchant_sig"
",h_contract_terms"
",rtransaction_id"
- ",amount_with_fee_val"
- ",amount_with_fee_frac"
+ ",refunds.amount_with_fee_val"
+ ",refunds.amount_with_fee_frac"
",known_coin_id"
" FROM refunds"
+ " JOIN deposits USING (deposit_serial_id)"
" ORDER BY refund_serial_id ASC;",
0),
GNUNET_PQ_make_prepare ("select_above_serial_by_table_wire_out",
diff --git a/src/include/taler_exchangedb_plugin.h
b/src/include/taler_exchangedb_plugin.h
index e26a2096..83d28340 100644
--- a/src/include/taler_exchangedb_plugin.h
+++ b/src/include/taler_exchangedb_plugin.h
@@ -75,6 +75,32 @@ struct TALER_EXCHANGEDB_DenominationKeyInformationP
GNUNET_NETWORK_STRUCT_END
+/**
+ * Meta data about an exchange online signing key.
+ */
+struct TALER_EXCHANGEDB_SignkeyMetaData
+{
+ /**
+ * Start time of the validity period for this key.
+ */
+ struct GNUNET_TIME_Absolute start;
+
+ /**
+ * The exchange will sign messages with this key between @e start and this
time.
+ */
+ struct GNUNET_TIME_Absolute expire_sign;
+
+ /**
+ * When do signatures with this sign key become invalid?
+ * After this point, these signatures cannot be used in (legal)
+ * disputes anymore, as the Exchange is then allowed to destroy its side
+ * of the evidence. @e expire_legal is expected to be significantly
+ * larger than @e expire_sign (by a year or more).
+ */
+ struct GNUNET_TIME_Absolute expire_legal;
+
+};
+
/**
* Enumeration of all of the tables replicated by exchange-auditor
@@ -194,16 +220,97 @@ struct TALER_EXCHANGEDB_TableData
uint64_t denominations_serial;
} reserves_out;
- struct {} auditors;
- struct {} auditor_denom_sigs;
- struct {} exchange_sign_keys;
- struct {} signkey_revocations;
- struct {} known_coins;
- struct {} refresh_commitments;
- struct {} refresh_revealed_coins;
- struct {} refresh_transfer_keys;
- struct {} deposits;
- struct {} refunds;
+ struct
+ {
+ struct TALER_AuditorPublicKeyP auditor_pub;
+ char *auditor_url;
+ char *auditor_name;
+ bool is_active;
+ struct GNUNET_TIME_Absolute last_change;
+ } auditors;
+
+ struct
+ {
+ uint64_t auditor_uuid;
+ uint64_t denominations_serial;
+ struct TALER_AuditorSignatureP auditor_sig;
+ } auditor_denom_sigs;
+
+ struct
+ {
+ struct TALER_ExchangePublicKeyP exchange_pub;
+ struct TALER_MasterSignatureP master_sig;
+ struct TALER_EXCHANGEDB_SignkeyMetaData meta;
+ } exchange_sign_keys;
+
+ struct
+ {
+ uint64_t esk_serial;
+ struct TALER_MasterSignatureP master_sig;
+ } signkey_revocations;
+
+ struct
+ {
+ struct TALER_CoinSpendPublicKeyP coin_pub;
+ struct TALER_DenominationSignature denom_sig;
+ uint64_t denominations_serial;
+ } known_coins;
+
+ struct
+ {
+ struct TALER_RefreshCommitmentP rc;
+ struct TALER_CoinSpendSignatureP old_coin_sig;
+ struct TALER_Amount amount_with_fee;
+ uint32_t noreveal_index;
+ uint64_t old_known_coin_id;
+ } refresh_commitments;
+
+ struct
+ {
+ uint64_t freshcoin_index;
+ struct TALER_CoinSpendSignatureP link_sig;
+ void *coin_ev;
+ size_t coin_ev_size;
+ // h_coin_ev omitted, to be recomputed!
+ struct TALER_DenominationSignature ev_sig;
+ uint64_t denominations_serial;
+ uint64_t melt_serial_id;
+ } refresh_revealed_coins;
+
+ struct
+ {
+ struct TALER_TransferPublicKeyP tp;
+ struct TALER_TransferPrivateKeyP tprivs[TALER_CNC_KAPPA - 1];
+ uint64_t melt_serial_id;
+ } refresh_transfer_keys;
+
+ struct
+ {
+ struct TALER_Amount amount_with_fee;
+ struct GNUNET_TIME_Absolute wallet_timestamp;
+ struct GNUNET_TIME_Absolute exchange_timestamp;
+ struct GNUNET_TIME_Absolute refund_deadline;
+ struct GNUNET_TIME_Absolute wire_deadline;
+ struct TALER_MerchantPublicKeyP merchant_pub;
+ struct GNUNET_HashCode h_contract_terms;
+ // h_wire omitted, to be recomputed!
+ struct TALER_CoinSpendSignatureP coin_sig;
+ json_t *wire;
+ bool tiny;
+ bool done;
+ uint64_t known_coin_id;
+ } deposits;
+
+ struct
+ {
+ struct TALER_MerchantPublicKeyP merchant_pub; // FIXME
+ struct TALER_MerchantSignatureP merchant_sig;
+ struct GNUNET_HashCode h_contract_terms; // FIXME
+ uint64_t rtransaction_id;
+ struct TALER_Amount amount_with_fee;
+ uint64_t known_coin_id;
+ } refunds;
+
struct {} wire_out;
struct {} aggregation_tracking;
struct {} wire_fee;
@@ -463,33 +570,6 @@ typedef void
bool recoup_possible);
-/**
- * Meta data about an exchange online signing key.
- */
-struct TALER_EXCHANGEDB_SignkeyMetaData
-{
- /**
- * Start time of the validity period for this key.
- */
- struct GNUNET_TIME_Absolute start;
-
- /**
- * The exchange will sign messages with this key between @e start and this
time.
- */
- struct GNUNET_TIME_Absolute expire_sign;
-
- /**
- * When do signatures with this sign key become invalid?
- * After this point, these signatures cannot be used in (legal)
- * disputes anymore, as the Exchange is then allowed to destroy its side
- * of the evidence. @e expire_legal is expected to be significantly
- * larger than @e expire_sign (by a year or more).
- */
- struct GNUNET_TIME_Absolute expire_legal;
-
-};
-
-
/**
* Signature of a function called with information about the exchange's
* online signing keys.
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.