[taler-grid5k] 37/189: update test sql

[gnunet]

This is an automated email from the git hooks/post-receive script.

marco-boss pushed a commit to branch master
in repository grid5k.

commit 6064159cccd13a92629b900f0ba9c7485133d8f5
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Sun Feb 27 14:02:26 2022 +0100

    update test sql
---
 sql/exchange-0001.sql | 44 +++++++++++++++++++++++++++++++-------------
 1 file changed, 31 insertions(+), 13 deletions(-)

diff --git a/sql/exchange-0001.sql b/sql/exchange-0001.sql
index ef36bfe..184d62a 100644
--- a/sql/exchange-0001.sql
+++ b/sql/exchange-0001.sql
@@ -88,7 +88,10 @@ CREATE TABLE IF NOT EXISTS wire_targets_default
   PARTITION OF wire_targets
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
 
-CREATE INDEX IF NOT EXISTS wire_targets_wire_target_serial_id_index
+-- FIXME partition by serial_id rather than h_payto, 
+-- it is used more in join conditions - crucial for sharding to select this.
+-- Author: (Boss Marco)
+CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index
   ON wire_targets
   (wire_target_serial_id
   );
@@ -201,7 +204,7 @@ CREATE INDEX IF NOT EXISTS 
reserves_close_by_reserve_pub_index
 
 CREATE TABLE IF NOT EXISTS reserves_out
   (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) -- UNIQUE
+  ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations 
(denominations_serial)
   ,denom_sig BYTEA NOT NULL
   ,reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE 
CASCADE
@@ -316,7 +319,7 @@ CREATE TABLE IF NOT EXISTS known_coins
   (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations 
(denominations_serial) ON DELETE CASCADE
   ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)
-  ,age_hash BYTEA CHECK (LENGTH(age_hash)=32)
+  ,age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)
   ,denom_sig BYTEA NOT NULL
   ,remaining_val INT8 NOT NULL
   ,remaining_frac INT4 NOT NULL
@@ -330,8 +333,8 @@ COMMENT ON COLUMN known_coins.coin_pub
   IS 'EdDSA public key of the coin';
 COMMENT ON COLUMN known_coins.remaining_val
   IS 'Value of the coin that remains to be spent';
-COMMENT ON COLUMN known_coins.age_hash
-  IS 'Optional hash for age restrictions as per DD 24 (active if denom_type 
has the respective bit set)';
+COMMENT ON COLUMN known_coins.age_commitment_hash
+  IS 'Optional hash of the age commitment for age restrictions as per DD 24 
(active if denom_type has the respective bit set)';
 COMMENT ON COLUMN known_coins.denom_sig
   IS 'This is the signature of the exchange that affirms that the coin is a 
valid coin. The specific signature type depends on denom_type of the 
denomination.';
 CREATE TABLE IF NOT EXISTS known_coins_default
@@ -342,10 +345,6 @@ CREATE INDEX IF NOT EXISTS 
known_coins_by_known_coin_id_index
   ON known_coins
   (known_coin_id);
 
-CREATE INDEX IF NOT EXISTS known_coins_denominations_serial_index
-  ON known_coins
-  (denominations_serial);
-
 
 CREATE TABLE IF NOT EXISTS refresh_commitments
   (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -367,7 +366,7 @@ COMMENT ON COLUMN refresh_commitments.rc
 COMMENT ON COLUMN refresh_commitments.old_coin_pub
   IS 'Coin being melted in the refresh process.';
 COMMENT ON COLUMN refresh_commitments.h_age_commitment
-  IS '(optional) age commitment that was involved in the minting process of 
the coin, may be NULL.';
+  IS 'The (optional) age commitment that was involved in the minting process 
of the coin, may be NULL.';
 CREATE TABLE IF NOT EXISTS refresh_commitments_default
   PARTITION OF refresh_commitments
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -590,7 +589,7 @@ CREATE INDEX IF NOT EXISTS 
wire_out_by_wire_target_serial_id_index
 CREATE TABLE IF NOT EXISTS aggregation_tracking
   (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits 
(deposit_serial_id) ON DELETE CASCADE
-  ,wtid_raw BYTEA CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON 
DELETE CASCADE DEFERRABLE
+  ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES 
wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
   )
   PARTITION BY HASH (deposit_serial_id);
 COMMENT ON TABLE aggregation_tracking
@@ -1087,6 +1086,23 @@ COMMENT ON FUNCTION 
exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4)
   IS 'Check whether the withdrawals from the given reserve since the given 
time are below the given threshold';
 
 
+-- 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 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_deposit(
@@ -1140,7 +1156,7 @@ INSERT INTO wire_targets
   VALUES
   (in_h_payto
   ,in_receiver_wire_account)
-ON CONFLICT DO NOTHING
+ON CONFLICT DO NOTHING -- for CONFLICT ON (h_payto)
   RETURNING wire_target_serial_id INTO wtsi;
 
 IF NOT FOUND
@@ -1268,6 +1284,7 @@ CREATE OR REPLACE FUNCTION exchange_do_melt(
   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_h_age_commitment BYTEA,
   IN in_noreveal_index INT4,
   IN in_zombie_required BOOLEAN,
   OUT out_balance_ok BOOLEAN,
@@ -1290,6 +1307,7 @@ INSERT INTO refresh_commitments
   ,old_coin_sig
   ,amount_with_fee_val
   ,amount_with_fee_frac
+  ,h_age_commitment
   ,noreveal_index
   )
   VALUES
@@ -1298,6 +1316,7 @@ INSERT INTO refresh_commitments
   ,in_old_coin_sig
   ,in_amount_with_fee_val
   ,in_amount_with_fee_frac
+  ,in_h_age_commitment
   ,in_noreveal_index)
   ON CONFLICT DO NOTHING;
 
@@ -2010,4 +2029,3 @@ END $$;
 
 -- Complete transaction
 COMMIT;
-

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.