[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] 04/15: more work on SQL refactoring
|
From: |
gnunet |
|
Subject: |
[taler-exchange] 04/15: more work on SQL refactoring |
|
Date: |
Sun, 27 Nov 2022 22:14:28 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
commit a322770d290cae69e7d2f7629ee575e068254428
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sun Nov 27 14:05:47 2022 +0100
more work on SQL refactoring
---
src/exchangedb/0002-deposits.sql | 342 +++++++++++++--
src/exchangedb/0002-known_coins.sql | 87 +++-
src/exchangedb/0002-legitimization_processes.sql | 55 ++-
.../0002-legitimization_requirements.sql | 49 ++-
src/exchangedb/0002-refresh_commitments.sql | 79 +++-
src/exchangedb/0002-refresh_revealed_coins.sql | 119 +++++-
src/exchangedb/0002-refresh_transfer_keys.sql | 82 +++-
src/exchangedb/0002-refunds.sql | 79 +++-
src/exchangedb/0002-reserves.sql | 75 +++-
src/exchangedb/0002-reserves_close.sql | 47 ++-
src/exchangedb/0002-reserves_in.sql | 81 ++--
src/exchangedb/0002-reserves_open_deposits.sql | 56 ++-
src/exchangedb/0002-reserves_open_requests.sql | 71 +++-
src/exchangedb/0002-reserves_out.sql | 156 +++++--
src/exchangedb/0002-wire_out.sql | 86 +++-
src/exchangedb/exchange-0001-part.sql | 464 ---------------------
src/exchangedb/exchange-0001.sql | 21 +-
17 files changed, 1187 insertions(+), 762 deletions(-)
diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql
index 35210443..874b33cc 100644
--- a/src/exchangedb/0002-deposits.sql
+++ b/src/exchangedb/0002-deposits.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_deposits(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_deposits(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,13 +23,12 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'deposits';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY
KEY'
- ',shard INT8 NOT NULL'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
- ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)
ON DELETE CASCADE' --- FIXME: column needed???
+ 'CREATE TABLE %I'
+ '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',partition INT8 NOT NULL'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+ ',known_coin_id INT8 NOT NULL' -- FIXME: column needed!?
',amount_with_fee_val INT8 NOT NULL'
',amount_with_fee_frac INT4 NOT NULL'
',wallet_timestamp INT8 NOT NULL'
@@ -43,43 +42,106 @@ BEGIN
',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
',done BOOLEAN NOT NULL DEFAULT FALSE'
',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE'
- ',policy_details_serial_id INT8' -- REFERENCES policy_details
(policy_details_serial_id) ON DELETE CASCADE'
+ ',policy_details_serial_id INT8'
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Used for load sharding in the materialized indices. Should be set based
on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.'
+ ,'shard'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Used for garbage collection'
+ ,'known_coin_id'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifies the target bank account and KYC status'
+ ,'wire_target_h_payto'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Salt used when hashing the payto://-URI to get the h_wire'
+ ,'wire_salt'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Set to TRUE once we have included this deposit in some aggregate wire
transfer to the merchant'
+ ,'done'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'True if the aggregation of the deposit is currently blocked by some
policy extension mechanism. Used to filter out deposits that must not be
processed by the canonical deposit logic.'
+ ,'policy_blocked'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'References policy extensions table, NULL if extensions are not used'
+ ,'policy_details_serial_id'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_deposits(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
- 'ON ' || table_name || ' '
- '(coin_pub);'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_deposit_serial_id_pkey'
+ ' PRIMARY KEY (deposit_serial_id) '
+ ',ADD CONSTRAINT ' || table_name ||
'_coin_pub_merchant_pub_h_contract_terms_key'
+ ' UNIQUE (coin_pub, merchant_pub, h_contract_terms)'
);
-
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_deposits()
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE deposits_' || partition_suffix || ' '
- 'ADD CONSTRAINT deposits_' || partition_suffix ||
'_deposit_serial_id_pkey '
- 'PRIMARY KEY (deposit_serial_id) '
- ',ADD CONSTRAINT deposits_' || partition_suffix ||
'_coin_pub_merchant_pub_h_contract_terms_key '
- 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_coin_id'
+ ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_policy_details'
+ ' REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE'
);
END
$$;
-CREATE OR REPLACE FUNCTION create_table_deposits_by_ready(
- IN shard_suffix VARCHAR DEFAULT NULL
+
+CREATE FUNCTION create_table_deposits_by_ready(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -87,33 +149,47 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'deposits_by_ready';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(wire_deadline INT8 NOT NULL'
- ',shard INT8 NOT NULL'
+ ',partition INT8 NOT NULL'
',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
',deposit_serial_id INT8'
') %s ;'
,table_name
,'PARTITION BY RANGE (wire_deadline)'
- ,shard_suffix
+ ,partition_suffix
);
+ PERFORM comment_partitioned_table(
+ 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER
below'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_deposits_by_ready(
+ IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits_by_ready';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'CREATE INDEX ' || table_name || '_main_index '
'ON ' || table_name || ' '
- '(wire_deadline ASC, shard ASC, coin_pub);'
+ '(wire_deadline ASC, partition ASC, coin_pub);'
);
-
END
$$;
-CREATE OR REPLACE FUNCTION create_table_deposits_for_matching(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_deposits_for_matching(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -121,9 +197,8 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'deposits_for_matching';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(refund_deadline INT8 NOT NULL'
',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
@@ -131,21 +206,175 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY RANGE (refund_deadline)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Enables fast lookups for deposits_iterate_matching, auto-populated via
TRIGGER below'
+ ,table_name
+ ,partition_suffix
);
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_deposits_for_matching(
+ IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits_for_matching';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
- 'ON ' || table_name || ' '
+ 'CREATE INDEX ' || table_name || '_main_index'
+ ' ON ' || table_name || ' '
'(refund_deadline ASC, merchant_pub, coin_pub);'
);
-
END
$$;
+CREATE OR REPLACE FUNCTION deposits_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ is_ready BOOLEAN;
+BEGIN
+ is_ready = NOT (NEW.done OR NEW.policy_blocked);
+
+ IF (is_ready)
+ THEN
+ INSERT INTO exchange.deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ INSERT INTO exchange.deposits_for_matching
+ (refund_deadline
+ ,merchant_pub
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.merchant_pub
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_insert_trigger()
+ IS 'Replicate deposit inserts into materialized indices.';
+
+
+CREATE OR REPLACE FUNCTION deposits_update_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+DECLARE
+ is_ready BOOLEAN;
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.policy_blocked);
+ is_ready = NOT (NEW.done OR NEW.policy_blocked);
+ IF (was_ready AND NOT is_ready)
+ THEN
+ DELETE FROM exchange.deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ DELETE FROM exchange.deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND merchant_pub = OLD.merchant_pub
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ IF (is_ready AND NOT was_ready)
+ THEN
+ INSERT INTO exchange.deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ INSERT INTO exchange.deposits_for_matching
+ (refund_deadline
+ ,merchant_pub
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.merchant_pub
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_update_trigger()
+ IS 'Replicate deposits changes into materialized indices.';
+
+
+CREATE OR REPLACE FUNCTION deposits_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.policy_blocked);
+
+ IF (was_ready)
+ THEN
+ DELETE FROM exchange.deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ DELETE FROM exchange.deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND merchant_pub = OLD.merchant_pub
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_delete_trigger()
+ IS 'Replicate deposit deletions into materialized indices.';
+
+
+CREATE FUNCTION master_table_deposits()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CREATE TRIGGER deposits_on_insert
+ AFTER INSERT
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger();
+ CREATE TRIGGER deposits_on_update
+ AFTER UPDATE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger();
+ CREATE TRIGGER deposits_on_delete
+ AFTER DELETE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
+END $$;
+
+
INSERT INTO exchange_tables
(name
,version
@@ -158,13 +387,38 @@ INSERT INTO exchange_tables
,'create'
,TRUE
,FALSE),
- ('deposits_by_ready' -- FIXME: do this?
+ ('deposits'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('deposits'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
+ ,FALSE),
+ ('deposits_by_ready'
,'exchange-0002'
,'create'
,TRUE
,FALSE),
- ('deposits_for_matching' -- FIXME: do this?
+ ('deposits_by_ready'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('deposits_for_matching'
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('deposits_for_matching'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('deposits'
+ ,'exchange-0002'
+ ,'master'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-known_coins.sql
b/src/exchangedb/0002-known_coins.sql
index af2610c6..a45c7bc8 100644
--- a/src/exchangedb/0002-known_coins.sql
+++ b/src/exchangedb/0002-known_coins.sql
@@ -15,7 +15,7 @@
--
-CREATE OR REPLACE FUNCTION create_table_known_coins(
+CREATE FUNCTION create_table_known_coins(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -24,11 +24,10 @@ AS $$
DECLARE
table_name VARCHAR default 'known_coins';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
+ 'CREATE TABLE %I'
+ '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',denominations_serial INT8 NOT NULL'
',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)'
',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)'
',denom_sig BYTEA NOT NULL'
@@ -36,26 +35,78 @@ BEGIN
',remaining_frac INT4 NOT NULL DEFAULT(0)'
') %s ;'
,table_name
- ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial?
or multi-level partitioning?;
+ ,'PARTITION BY HASH (coin_pub)'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'information about coins and their signatures, so we do not have to store
the signatures more than once if a coin is involved in multiple operations'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Denomination of the coin, determines the value of the original coin and
applicable fees for coin-specific operations.'
+ ,'denominations_serial'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'EdDSA public key of the coin'
+ ,'coin_pub'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Value of the coin that remains to be spent'
+ ,'remaining_val'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Optional hash of the age commitment for age restrictions as per DD 24
(active if denom_type has the respective bit set)'
+ ,'age_commitment_hash'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ '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.'
+ ,'denom_sig'
+ ,table_name
,shard_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition(
+
+CREATE FUNCTION constrain_table_known_coins(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR default 'known_coins';
+BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_known_coin_id_key'
+ ' UNIQUE (known_coin_id)'
+ );
+END
+$$;
+
+
+CREATE FUNCTION foreign_table_known_coins()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'known_coins';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE known_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key
'
- 'UNIQUE (known_coin_id)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_denominations'
+ ' REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
);
END
$$;
@@ -72,4 +123,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('known_coins'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('known_coins'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-legitimization_processes.sql
b/src/exchangedb/0002-legitimization_processes.sql
index 1be54c3c..6248da1f 100644
--- a/src/exchangedb/0002-legitimization_processes.sql
+++ b/src/exchangedb/0002-legitimization_processes.sql
@@ -14,16 +14,15 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_legitimization_processes(
+CREATE FUNCTION create_table_legitimization_processes(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS
IDENTITY' -- UNIQUE'
',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)'
',expiration_time INT8 NOT NULL DEFAULT (0)'
@@ -36,13 +35,53 @@ BEGIN
,'PARTITION BY HASH (h_payto)'
,shard_suffix
);
-
+ PERFORM comment_partitioned_table(
+ 'List of legitimization processes (ongoing and completed) by account and
provider'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'unique ID for this legitimization process at the exchange'
+ ,'legitimization_process_serial_id'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'foreign key linking the entry to the wire_targets table, NOT a primary
key (multiple legitimizations are possible per wire target)'
+ ,'h_payto'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'in the future if the respective KYC check was passed successfully'
+ ,'expiration_time'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Configuration file section with details about this provider'
+ ,'provider_section'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifier for the user at the provider that was used for the
legitimization. NULL if provider is unaware.'
+ ,'provider_user_id'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifier for the specific legitimization process at the provider. NULL
if legitimization was not started.'
+ ,'provider_legitimization_id'
+ ,'legitimization_processes'
+ ,shard_suffix
+ );
END
$$;
-- We need a separate function for this, as we call create_table only once but
need to add
-- those constraints to each partition which gets created
-CREATE OR REPLACE FUNCTION
add_constraints_to_legitimization_processes_partition(
+CREATE FUNCTION constrain_table_legitimization_processes(
IN partition_suffix VARCHAR
)
RETURNS void
@@ -51,7 +90,6 @@ AS $$
DECLARE
partition_name VARCHAR;
BEGIN
-
partition_name = concat_ws('_', 'legitimization_processes',
partition_suffix);
EXECUTE FORMAT (
@@ -83,4 +121,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('legitimization_processes'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-legitimization_requirements.sql
b/src/exchangedb/0002-legitimization_requirements.sql
index c58d2431..7aaf7b79 100644
--- a/src/exchangedb/0002-legitimization_requirements.sql
+++ b/src/exchangedb/0002-legitimization_requirements.sql
@@ -14,16 +14,15 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_legitimization_requirements(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_legitimization_requirements(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS
IDENTITY' -- UNIQUE'
',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)'
',required_checks VARCHAR NOT NULL'
@@ -31,15 +30,37 @@ BEGIN
') %s ;'
,'legitimization_requirements'
,'PARTITION BY HASH (h_payto)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'List of required legitimizations by account'
+ ,'legitimization_requirements'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'unique ID for this legitimization requirement at the exchange'
+ ,'legitimization_requirement_serial_id'
+ ,'legitimization_requirements'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'foreign key linking the entry to the wire_targets table, NOT a primary
key (multiple legitimizations are possible per wire target)'
+ ,'h_payto'
+ ,'legitimization_requirements'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'space-separated list of required checks'
+ ,'required_checks'
+ ,'legitimization_requirements'
+ ,partition_suffix
);
-
END
$$;
-- We need a separate function for this, as we call create_table only once but
need to add
-- those constraints to each partition which gets created
-CREATE OR REPLACE FUNCTION
add_constraints_to_legitimization_requirements_partition(
+CREATE FUNCTION constrain_table_legitimization_requirements(
IN partition_suffix VARCHAR
)
RETURNS void
@@ -48,14 +69,11 @@ AS $$
DECLARE
partition_name VARCHAR;
BEGIN
-
partition_name = concat_ws('_', 'legitimization_requirements',
partition_suffix);
-
EXECUTE FORMAT (
- 'ALTER TABLE ' || partition_name
- || ' '
- 'ADD CONSTRAINT ' || partition_name || '_serial_id_key '
- 'UNIQUE (legitimization_requirement_serial_id)');
+ 'ALTER TABLE ' || partition_name || ' '
+ 'ADD CONSTRAINT ' || partition_name || '_serial_id_key '
+ 'UNIQUE (legitimization_requirement_serial_id)');
END
$$;
@@ -71,4 +89,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('legitimization_requirements'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-refresh_commitments.sql
b/src/exchangedb/0002-refresh_commitments.sql
index ce6077c5..c3d5cfde 100644
--- a/src/exchangedb/0002-refresh_commitments.sql
+++ b/src/exchangedb/0002-refresh_commitments.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_refresh_commitments(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_refresh_commitments(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,12 +23,11 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'refresh_commitments';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)'
- ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON
DELETE CASCADE'
+ ',old_coin_pub BYTEA NOT NULL'
',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)'
',amount_with_fee_val INT8 NOT NULL'
',amount_with_fee_frac INT4 NOT NULL'
@@ -36,32 +35,72 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (rc)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Commitments made when melting coins and the gamma value chosen by the
exchange.'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'The gamma value chosen by the exchange in the cut-and-choose protocol'
+ ,'noreveal_index'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Commitment made by the client, hash over the various client inputs in
the cut-and-choose protocol'
+ ,'rc'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Coin being melted in the refresh process.'
+ ,'old_coin_pub'
+ ,table_name
+ ,partition_suffix
);
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+
+CREATE FUNCTION constrain_table_refresh_commitments(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_commitments';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
-- Note: index spans partitions, may need to be materialized.
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index '
+ 'CREATE INDEX ' || table_name || '_by_old_coin_pub_index '
'ON ' || table_name || ' '
'(old_coin_pub);'
);
-
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_melt_serial_id_key'
+ ' UNIQUE (melt_serial_id)'
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_refresh_commitments()
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_commitments';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE refresh_commitments_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_commitments_' || partition_suffix ||
'_melt_serial_id_key '
- 'UNIQUE (melt_serial_id)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
);
END
$$;
@@ -78,4 +117,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('refresh_commitments'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('refresh_commitments'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql
b/src/exchangedb/0002-refresh_revealed_coins.sql
index e4b44557..a7d4d439 100644
--- a/src/exchangedb/0002-refresh_revealed_coins.sql
+++ b/src/exchangedb/0002-refresh_revealed_coins.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins(
+CREATE FUNCTION create_table_refresh_revealed_coins(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -23,52 +23,115 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'refresh_revealed_coins';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
+ 'CREATE TABLE %I'
+ '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',melt_serial_id INT8 NOT NULL'
',freshcoin_index INT4 NOT NULL'
',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
- ',coin_ev BYTEA NOT NULL' -- UNIQUE'
- ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE'
+ ',denominations_serial INT8 NOT NULL'
+ ',coin_ev BYTEA NOT NULL'
+ ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)'
',ev_sig BYTEA NOT NULL'
',ewv BYTEA NOT NULL'
- -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
') %s ;'
,table_name
,'PARTITION BY HASH (melt_serial_id)'
,shard_suffix
);
+ PEFORM comment_partitioned_table(
+ 'Revelations about the new coins that are to be created during a melting
session.'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'needed for exchange-auditor replication logic'
+ ,'rrc_serial'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'Identifies the refresh commitment (rc) of the melt operation.'
+ ,'melt_serial_id'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'index of the fresh coin being created (one melt operation may result in
multiple fresh coins)'
+ ,'freshcoin_index'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'envelope of the new coin to be signed'
+ ,'coin_ev'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'exchange contributed values in the creation of the fresh coin (see /csr)'
+ ,'ewv'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'hash of the envelope of the new coin to be signed (for lookups)'
+ ,'h_coin_ev'
+ ,table_name
+ ,shard_suffix
+ );
+ PEFORM comment_partitioned_column(
+ 'exchange signature over the envelope'
+ ,'ev_sig'
+ ,table_name
+ ,shard_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_refresh_revealed_coins(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_revealed_coins';
+BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_coins_by_melt_serial_id_index '
+ 'CREATE INDEX ' || table_name || '_coins_by_melt_serial_id_index '
'ON ' || table_name || ' '
'(melt_serial_id);'
);
-
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_rrc_serial_key'
+ ' UNIQUE (rrc_serial) '
+ ',ADD CONSTRAINT ' || table_name || '_coin_ev_key'
+ ' UNIQUE (coin_ev) '
+ ',ADD CONSTRAINT ' || table_name || '_h_coin_ev_key'
+ ' UNIQUE (h_coin_ev) '
+ ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index)'
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_refresh_revealed_coins()
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_revealed_coins';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_rrc_serial_key '
- 'UNIQUE (rrc_serial) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_coin_ev_key '
- 'UNIQUE (coin_ev) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_h_coin_ev_key '
- 'UNIQUE (h_coin_ev) '
- ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_melt'
+ ' REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_denom'
+ ' REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
);
END
$$;
@@ -85,4 +148,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('refresh_revealed_coins'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('refresh_revealed_coins'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-refresh_transfer_keys.sql
b/src/exchangedb/0002-refresh_transfer_keys.sql
index 54274b26..07801590 100644
--- a/src/exchangedb/0002-refresh_transfer_keys.sql
+++ b/src/exchangedb/0002-refresh_transfer_keys.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_refresh_transfer_keys(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,33 +23,83 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'refresh_transfer_keys';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
+ 'CREATE TABLE %I'
+ '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',melt_serial_id INT8 PRIMARY KEY'
',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)'
',transfer_privs BYTEA NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Transfer keys of a refresh operation (the data revealed to the
exchange).'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'needed for exchange-auditor replication logic'
+ ,'rtc_serial'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifies the refresh commitment (rc) of the operation.'
+ ,'melt_serial_id'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'transfer public key for the gamma index'
+ ,'transfer_pub'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'array of TALER_CNC_KAPPA-1 transfer private keys that have been
revealed, with the gamma entry being skipped'
+ ,'transfer_privs'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
+
+CREATE FUNCTION constrain_table_refresh_transfer_keys(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_transfer_keys';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_rtc_serial_key'
+ ' UNIQUE (rtc_serial)'
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition(
+
+CREATE FUNCTION foreign_table_refresh_transfer_keys(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_transfer_keys';
BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix ||
'_rtc_serial_key '
- 'UNIQUE (rtc_serial)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || 'foreign_melt_serial_id'
+ ' REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
);
END
$$;
@@ -66,4 +116,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('refresh_transfer_keys'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('refresh_transfer_keys'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql
index 509cc7d0..82346694 100644
--- a/src/exchangedb/0002-refunds.sql
+++ b/src/exchangedb/0002-refunds.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_refunds(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_refunds(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -24,46 +24,78 @@ DECLARE
table_name VARCHAR DEFAULT 'refunds';
BEGIN
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
- ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE'
+ 'CREATE TABLE %I'
+ '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+ ',deposit_serial_id INT8 NOT NULL'
',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
',rtransaction_id INT8 NOT NULL'
',amount_with_fee_val INT8 NOT NULL'
',amount_with_fee_frac INT4 NOT NULL'
- -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub.
Multiple deposits may match a refund, this only identifies one of them.'
+ ,'deposit_serial_id'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund'
+ ,'rtransaction_id'
+ ,table_name
+ ,partition_suffix
);
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_refunds (
+ IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refunds';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+ 'CREATE INDEX ' || table_name || '_by_coin_pub_index '
'ON ' || table_name || ' '
'(coin_pub);'
);
-
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_refund_serial_id_key'
+ ' UNIQUE (refund_serial_id) '
+ ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
+ );
END
$$;
-CREATE OR REPLACE FUNCTION constrain0002_table_refunds (
- IN partition_suffix VARCHAR DEFAULT NULL
-)
+
+CREATE FUNCTION foreign_table_refunds ()
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refunds';
BEGIN
EXECUTE FORMAT (
- -- FIXME: '_' issue if partition_suffix is NULL
- -- => solve with general ALTER TABLE helper function!
- 'ALTER TABLE refunds_' || partition_suffix || ' '
- 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key '
- 'UNIQUE (refund_serial_id) '
- ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_deposit'
+ ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
);
END
$$;
@@ -83,6 +115,11 @@ INSERT INTO exchange_tables
,FALSE),
('refunds'
,'exchange-0002'
- ,'constrain0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('refunds'
+ ,'exchange-0002'
+ ,'foreign'
,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql
index 5f3b4604..e5db97fe 100644
--- a/src/exchangedb/0002-reserves.sql
+++ b/src/exchangedb/0002-reserves.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,9 +23,8 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'reserves';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)'
',current_balance_val INT8 NOT NULL DEFAULT(0)'
@@ -38,13 +37,65 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Summarizes the balance of a reserve. Updated when new funds are added or
withdrawn.'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'EdDSA public key of the reserve. Knowledge of the private key implies
ownership over the balance.'
+ ,'reserve_pub'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Current balance remaining with the reserve.'
+ ,'current_balance_val'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'Number of purses that were created by this reserve that are not expired
and not fully paid.'
+ ,'purses_active'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Number of purses that this reserve is allowed to have active at most.'
+ ,'purses_allowed'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Used to trigger closing of reserves that have not been drained after
some time'
+ ,'expiration_date'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Used to forget all information about a reserve during garbage collection'
+ ,'gc_date'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_reserves(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'reserves';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index '
+ 'CREATE INDEX ' || table_name || '_by_expiration_index '
'ON ' || table_name || ' '
'(expiration_date'
',current_balance_val'
@@ -56,12 +107,12 @@ BEGIN
'IS ' || quote_literal('used in get_expired_reserves') || ';'
);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index '
+ 'CREATE INDEX ' || table_name || '_by_reserve_uuid_index '
'ON ' || table_name || ' '
'(reserve_uuid);'
);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index '
+ 'CREATE INDEX ' || table_name || '_by_gc_date_index '
'ON ' || table_name || ' '
'(gc_date);'
);
@@ -69,7 +120,6 @@ BEGIN
'COMMENT ON INDEX ' || table_name || '_by_gc_date_index '
'IS ' || quote_literal('for reserve garbage collection') || ';'
);
-
END
$$;
@@ -85,4 +135,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_close.sql
b/src/exchangedb/0002-reserves_close.sql
index d08c961f..b68550a7 100644
--- a/src/exchangedb/0002-reserves_close.sql
+++ b/src/exchangedb/0002-reserves_close.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves_close(
+CREATE FUNCTION create_table_reserves_close(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -23,9 +23,8 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_close';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE /
PRIMARY KEY'
',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
',execution_date INT8 NOT NULL'
@@ -41,33 +40,40 @@ BEGIN
,'PARTITION BY HASH (reserve_pub)'
,shard_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index '
- 'ON ' || table_name || ' '
- '(close_uuid);'
+ PERFORM comment_partitioned_table(
+ 'wire transfers executed by the reserve to close reserves'
+ ,table_name
+ ,shard_suffix
);
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index '
- 'ON ' || table_name || ' '
- '(reserve_pub);'
+ PERFORM comment_partitioned_column(
+ 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.'
+ ,'wire_target_h_payto'
+ ,table_name
+ ,shard_suffix
);
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition(
+
+CREATE FUNCTION constrain_table_reserves_close(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_close';
BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_close_uuid_pkey '
+ 'PRIMARY KEY (close_uuid)'
+ );
EXECUTE FORMAT (
- 'ALTER TABLE reserves_close_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_close_' || partition_suffix ||
'_close_uuid_pkey '
- 'PRIMARY KEY (close_uuid)'
+ 'CREATE INDEX ' || table_name || '_by_reserve_pub_index '
+ 'ON ' || table_name || ' '
+ '(reserve_pub);'
);
END
$$;
@@ -84,4 +90,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves_close'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_in.sql
b/src/exchangedb/0002-reserves_in.sql
index 2ca0ea71..a5ef4dc8 100644
--- a/src/exchangedb/0002-reserves_in.sql
+++ b/src/exchangedb/0002-reserves_in.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves_in(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_in(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,9 +23,8 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_in';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
',wire_reference INT8 NOT NULL'
@@ -37,19 +36,58 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'list of transfers of funds into the reserves, one per incoming wire
transfer'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'Identifies the debited bank account and KYC status'
+ ,'wire_source_h_payto'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Public key of the reserve. Private key signifies ownership of the
remaining balance.'
+ ,'reserve_pub'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Amount that was transferred into the reserve'
+ ,'credit_val'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_reserves_in(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_in';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE reserves_in_' || partition_suffix || ' '
+ 'ADD CONSTRAINT reserves_in_' || partition_suffix ||
'_reserve_in_serial_id_key '
+ 'UNIQUE (reserve_in_serial_id)'
+ );
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_in_serial_id_index '
+ 'CREATE INDEX ' || table_name || '_by_reserve_in_serial_id_index '
'ON ' || table_name || ' '
'(reserve_in_serial_id);'
);
-- FIXME: where do we need this index? Can we do better?
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_exch_accnt_section_execution_date_idx '
+ 'CREATE INDEX ' || table_name ||
'_by_exch_accnt_section_execution_date_idx '
'ON ' || table_name || ' '
'(exchange_account_section '
',execution_date'
@@ -57,28 +95,12 @@ BEGIN
);
-- FIXME: where do we need this index? Can we do better?
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_exch_accnt_reserve_in_serial_id_idx '
+ 'CREATE INDEX ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx '
'ON ' || table_name || ' '
- '(exchange_account_section,'
- 'reserve_in_serial_id DESC'
+ '(exchange_account_section'
+ ',reserve_in_serial_id DESC'
');'
);
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_in_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_in_' || partition_suffix ||
'_reserve_in_serial_id_key '
- 'UNIQUE (reserve_in_serial_id)'
- );
END
$$;
@@ -94,4 +116,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves_in'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_open_deposits.sql
b/src/exchangedb/0002-reserves_open_deposits.sql
index 132a123f..35605d36 100644
--- a/src/exchangedb/0002-reserves_open_deposits.sql
+++ b/src/exchangedb/0002-reserves_open_deposits.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_open_deposits(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,10 +23,9 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_open_deposits';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE / PRIMARY KEY'
+ 'CREATE TABLE %I'
+ '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)'
',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
@@ -36,36 +35,48 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
+ ,partition_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid '
- 'ON ' || table_name || ' '
- '(reserve_open_deposit_uuid);'
+ PERFORM comment_partitioned_table(
+ 'coin contributions paying for a reserve to remain open'
+ ,table_name
+ ,partition_suffix
);
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve '
- 'ON ' || table_name || ' '
- '(reserve_pub);'
+ PERFORM comment_partitioned_column(
+ 'Identifies the specific reserve being paid for (possibly together with
reserve_sig).'
+ ,'reserve_pub'
+ ,table_name
+ ,partition_suffix
);
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition(
+
+CREATE FUNCTION constrain_table_reserves_open_deposits(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_open_deposits';
BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix ||
'_coin_unique '
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_coin_unique '
'PRIMARY KEY (coin_pub,coin_sig)'
);
+ EXECUTE FORMAT (
+ 'CREATE INDEX ' || table_name || '_by_uuid '
+ 'ON ' || table_name || ' '
+ '(reserve_open_deposit_uuid);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX ' || table_name || '_by_reserve '
+ 'ON ' || table_name || ' '
+ '(reserve_pub);'
+ );
END
$$;
@@ -81,4 +92,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves_open_deposits'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_open_requests.sql
b/src/exchangedb/0002-reserves_open_requests.sql
index e56553a5..96084c1d 100644
--- a/src/exchangedb/0002-reserves_open_requests.sql
+++ b/src/exchangedb/0002-reserves_open_requests.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves_open_requests(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_open_requests(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,11 +23,10 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_open_requests';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE /
PRIMARY KEY'
- ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
+ 'CREATE TABLE %I'
+ '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',reserve_pub BYTEA NOT NULL'
',request_timestamp INT8 NOT NULL'
',expiration_date INT8 NOT NULL'
',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
@@ -37,42 +36,60 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+ ,partition_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_open_uuid_index '
- 'ON ' || table_name || ' '
- '(open_request_uuid);'
+ PERFORM comment_partitioned_table (
+ 'requests to keep a reserve open'
+ ,table_name
+ ,partition_suffix
);
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index '
- 'ON ' || table_name || ' '
- '(reserve_pub);'
+ PERFORM comment_partitioned_column (
+ 'Fee to pay for the request from the reserve balance itself.'
+ ,'reserve_payment_val'
+ ,table_name
+ ,partition_suffix
);
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition(
+
+CREATE FUNCTION constrain_table_reserves_open_requests(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_open_requests';
BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix ||
'_by_uuid '
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_by_uuid '
'PRIMARY KEY (open_request_uuid),'
- 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix ||
'_by_time '
+ 'ADD CONSTRAINT ' || table_name || '_by_time '
'UNIQUE (reserve_pub,request_timestamp)'
);
END
$$;
+CREATE FUNCTION foreign_table_reserves_open_requests()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_open_requests';
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub '
+ 'REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+ );
+END
+$$;
+
+
INSERT INTO exchange_tables
(name
,version
@@ -84,4 +101,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves_open_requests'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('reserves_open_requests'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_out.sql
b/src/exchangedb/0002-reserves_out.sql
index e2aed930..52567289 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_reserves_out(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_out(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,14 +23,13 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_out';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ 'CREATE TABLE %I'
+ '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial)'
+ ',denominations_serial INT8 NOT NULL'
',denom_sig BYTEA NOT NULL'
- ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON
DELETE CASCADE'
+ ',reserve_uuid INT8 NOT NULL'
',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
',execution_date INT8 NOT NULL'
',amount_with_fee_val INT8 NOT NULL'
@@ -38,19 +37,47 @@ BEGIN
') %s ;'
,'reserves_out'
,'PARTITION BY HASH (h_blind_ev)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table (
+ 'Withdraw operations performed on reserves.'
+ ,'reserves_out'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column (
+ 'Hash of the blinded coin, used as primary key here so that broken
clients that use a non-random coin or blinding factor fail to withdraw
(otherwise they would fail on deposit when the coin is not unique there).'
+ ,'h_blind_ev'
+ ,'reserves_out'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column (
+ 'We do not CASCADE ON DELETE for the foreign constrain here, as we may
keep the denomination data alive'
+ ,'denominations_serial'
+ ,'reserves_out'
+ ,partition_suffix
);
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_reserves_out(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_out';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_out_serial_id_index '
- 'ON ' || table_name || ' '
- '(reserve_out_serial_id);'
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_reserve_out_serial_id_key '
+ 'UNIQUE (reserve_out_serial_id)'
);
-- FIXME: change query to use reserves_out_by_reserve instead and
materialize execution_date there as well???
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_uuid_and_execution_date_index '
+ 'CREATE INDEX ' || table_name ||
'_by_reserve_uuid_and_execution_date_index '
'ON ' || table_name || ' '
'(reserve_uuid, execution_date);'
);
@@ -58,29 +85,30 @@ BEGIN
'COMMENT ON INDEX ' || table_name ||
'_by_reserve_uuid_and_execution_date_index '
'IS ' || quote_literal('for get_reserves_out and
exchange_do_withdraw_limit_check') || ';'
);
-
END
$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition(
- IN partition_suffix VARCHAR
-)
+CREATE FUNCTION foreign_table_reserves_out()
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_out';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE reserves_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_out_' || partition_suffix ||
'_reserve_out_serial_id_key '
- 'UNIQUE (reserve_out_serial_id)'
+ 'ALTER TABLE ' || table_name || ' '
+ 'ADD CONSTRAINT ' || table_name || '_foreign_denom '
+ 'REFERENCES denominations (denominations_serial)'
+ 'ADD CONSTRAINT ' || table_name || '_foreign_reserve '
+ 'REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
);
END
$$;
-CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve(
- IN shard_suffix VARCHAR DEFAULT NULL
+
+CREATE FUNCTION create_table_reserves_out_by_reserve(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -88,29 +116,78 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'reserves_out_by_reserve';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON
DELETE CASCADE
',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)'
') %s '
,table_name
,'PARTITION BY HASH (reserve_uuid)'
- ,shard_suffix
+ ,partition_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
+ PERFORM comment_partitioned_column (
+ 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.'
+ ,table_name
+ ,partition_suffix
+ );
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'CREATE INDEX ' || table_name || '_main_index '
'ON ' || table_name || ' '
'(reserve_uuid);'
);
-
END
$$;
+CREATE FUNCTION reserves_out_by_reserve_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO exchange.reserves_out_by_reserve
+ (reserve_uuid
+ ,h_blind_ev)
+ VALUES
+ (NEW.reserve_uuid
+ ,NEW.h_blind_ev);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
+ IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
+
+
+CREATE FUNCTION reserves_out_by_reserve_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM exchange.reserves_out_by_reserve
+ WHERE reserve_uuid = OLD.reserve_uuid;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
+ IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
+
+
+CREATE FUNCTION master_table_reserves_out()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CREATE TRIGGER reserves_out_on_insert
+ AFTER INSERT
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
+ CREATE TRIGGER reserves_out_on_delete
+ AFTER DELETE
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+END $$;
+COMMENT ON FUNCTION master_table_reserves_out()
+ IS 'Setup triggers to replicate reserve_out into reserve_out_by_reserve.';
+
+
INSERT INTO exchange_tables
(name
,version
@@ -123,8 +200,23 @@ INSERT INTO exchange_tables
,'create'
,TRUE
,FALSE),
- ('reserves_out_by_reserve' -- FIXME: do like this?
+ ('reserves_out'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('reserves_out'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
+ ,FALSE),
+ ('reserves_out_by_reserve'
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('reserves_out'
+ ,'exchange-0002'
+ ,'master'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-wire_out.sql b/src/exchangedb/0002-wire_out.sql
index f34998b5..9c459fe9 100644
--- a/src/exchangedb/0002-wire_out.sql
+++ b/src/exchangedb/0002-wire_out.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_wire_out(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_wire_out(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,10 +23,9 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'wire_out';
BEGIN
-
PERFORM create_partitioned_table(
'CREATE TABLE IF NOT EXISTS %I'
- '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
+ '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
',execution_date INT8 NOT NULL'
',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)'
',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
@@ -36,37 +35,78 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (wtid_raw)'
- ,shard_suffix
+ ,partition_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_wire_target_h_payto_index '
- 'ON ' || table_name || ' '
- '(wire_target_h_payto);'
+ PERFORM comment_partitioned_table(
+ 'wire transfers the exchange has executed'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies the configuration section with the debit account of this
payment'
+ ,'exchange_account_section'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifies the credited bank account and KYC status'
+ ,'wire_target_h_payto'
+ ,table_name
+ ,partition_suffix
);
-
-
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition(
+
+CREATE FUNCTION constrain_table_wire_out(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'wire_out';
BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE wire_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey '
- 'PRIMARY KEY (wireout_uuid)'
+ 'CREATE INDEX ' || table_name || '_by_wire_target_h_payto_index '
+ 'ON ' || table_name || ' '
+ '(wire_target_h_payto);'
+ );
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_wireout_uuid_pkey'
+ ' PRIMARY KEY (wireout_uuid)'
);
END
$$;
+CREATE FUNCTION wire_out_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM exchange.aggregation_tracking
+ WHERE wtid_raw = OLD.wtid_raw;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION wire_out_delete_trigger()
+ IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces
an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint
and conflicted with nice partitioning.';
+
+
+CREATE FUNCTION master_table_wire_out()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CREATE TRIGGER wire_out_on_delete
+ AFTER DELETE
+ ON wire_out
+ FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger();
+END $$;
+
+
INSERT INTO exchange_tables
(name
,version
@@ -78,4 +118,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('wire_out'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('wire_out'
+ ,'exchange-0002'
+ ,'master'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/exchange-0001-part.sql
b/src/exchangedb/exchange-0001-part.sql
index 8a103608..29412ca7 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -14,470 +14,6 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
--- ------------------------------ legitimization_processes
----------------------------------------
-
-SELECT create_table_legitimization_processes();
-
-COMMENT ON TABLE legitimization_processes
- IS 'List of legitimization processes (ongoing and completed) by account and
provider';
-COMMENT ON COLUMN legitimization_processes.legitimization_process_serial_id
- IS 'unique ID for this legitimization process at the exchange';
-COMMENT ON COLUMN legitimization_processes.h_payto
- IS 'foreign key linking the entry to the wire_targets table, NOT a primary
key (multiple legitimizations are possible per wire target)';
-COMMENT ON COLUMN legitimization_processes.expiration_time
- IS 'in the future if the respective KYC check was passed successfully';
-COMMENT ON COLUMN legitimization_processes.provider_section
- IS 'Configuration file section with details about this provider';
-COMMENT ON COLUMN legitimization_processes.provider_user_id
- IS 'Identifier for the user at the provider that was used for the
legitimization. NULL if provider is unaware.';
-COMMENT ON COLUMN legitimization_processes.provider_legitimization_id
- IS 'Identifier for the specific legitimization process at the provider. NULL
if legitimization was not started.';
-
-SELECT add_constraints_to_legitimization_processes_partition('default');
-
-
--- ------------------------------ legitimization_requirements_
----------------------------------------
-
-SELECT create_table_legitimization_requirements();
-
-COMMENT ON TABLE legitimization_requirements
- IS 'List of required legitimization by account';
-COMMENT ON COLUMN
legitimization_requirements.legitimization_requirement_serial_id
- IS 'unique ID for this legitimization requirement at the exchange';
-COMMENT ON COLUMN legitimization_requirements.h_payto
- IS 'foreign key linking the entry to the wire_targets table, NOT a primary
key (multiple legitimizations are possible per wire target)';
-COMMENT ON COLUMN legitimization_requirements.required_checks
- IS 'space-separated list of required checks';
-
-SELECT add_constraints_to_legitimization_requirements_partition('default');
-
-
-
--- ------------------------------ reserves
----------------------------------------
-
-SELECT create_table_reserves();
-
-COMMENT ON TABLE reserves
- IS 'Summarizes the balance of a reserve. Updated when new funds are added or
withdrawn.';
-COMMENT ON COLUMN reserves.reserve_pub
- IS 'EdDSA public key of the reserve. Knowledge of the private key implies
ownership over the balance.';
-COMMENT ON COLUMN reserves.current_balance_val
- IS 'Current balance remaining with the reserve.';
-COMMENT ON COLUMN reserves.purses_active
- IS 'Number of purses that were created by this reserve that are not expired
and not fully paid.';
-COMMENT ON COLUMN reserves.purses_allowed
- IS 'Number of purses that this reserve is allowed to have active at most.';
-COMMENT ON COLUMN reserves.expiration_date
- IS 'Used to trigger closing of reserves that have not been drained after
some time';
-COMMENT ON COLUMN reserves.gc_date
- IS 'Used to forget all information about a reserve during garbage
collection';
-
--- ------------------------------ reserves_in
----------------------------------------
-
-SELECT create_table_reserves_in();
-
-COMMENT ON TABLE reserves_in
- IS 'list of transfers of funds into the reserves, one per incoming wire
transfer';
-COMMENT ON COLUMN reserves_in.wire_source_h_payto
- IS 'Identifies the debited bank account and KYC status';
-COMMENT ON COLUMN reserves_in.reserve_pub
- IS 'Public key of the reserve. Private key signifies ownership of the
remaining balance.';
-COMMENT ON COLUMN reserves_in.credit_val
- IS 'Amount that was transferred into the reserve';
-
-
-SELECT add_constraints_to_reserves_in_partition('default');
-
--- ------------------------------ reserves_close
----------------------------------------
-
-SELECT create_table_reserves_close();
-
-COMMENT ON TABLE reserves_close
- IS 'wire transfers executed by the reserve to close reserves';
-COMMENT ON COLUMN reserves_close.wire_target_h_payto
- IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
-
-
-SELECT add_constraints_to_reserves_close_partition('default');
-
-
-
-
-
-
--- ------------------------------ reserves_open_requests
----------------------------------------
-
-SELECT create_table_reserves_open_requests();
-
-COMMENT ON TABLE reserves_open_requests
- IS 'requests to keep a reserve open';
-COMMENT ON COLUMN reserves_open_requests.reserve_payment_val
- IS 'Funding to pay for the request from the reserve balance itself.';
-
-SELECT add_constraints_to_reserves_open_request_partition('default');
-
-
--- ------------------------------ reserves_open_deposits
----------------------------------------
-
-SELECT create_table_reserves_open_deposits();
-
-COMMENT ON TABLE reserves_open_deposits
- IS 'coin contributions paying for a reserve to remain open';
-COMMENT ON COLUMN reserves_open_deposits.reserve_pub
- IS 'Identifies the specific reserve being paid for (possibly together with
reserve_sig).';
-
-
-SELECT add_constraints_to_reserves_open_deposits_partition('default');
-
-
--- ------------------------------ reserves_out
----------------------------------------
-
-SELECT create_table_reserves_out();
-
-COMMENT ON TABLE reserves_out
- IS 'Withdraw operations performed on reserves.';
-COMMENT ON COLUMN reserves_out.h_blind_ev
- IS 'Hash of the blinded coin, used as primary key here so that broken
clients that use a non-random coin or blinding factor fail to withdraw
(otherwise they would fail on deposit when the coin is not unique there).';
-COMMENT ON COLUMN reserves_out.denominations_serial
- IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
-
-SELECT add_constraints_to_reserves_out_partition('default');
-
-
-SELECT create_table_reserves_out_by_reserve();
-
-COMMENT ON TABLE reserves_out_by_reserve
- IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-BEGIN
- INSERT INTO exchange.reserves_out_by_reserve
- (reserve_uuid
- ,h_blind_ev)
- VALUES
- (NEW.reserve_uuid
- ,NEW.h_blind_ev);
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
- IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_insert
- AFTER INSERT
- ON reserves_out
- FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-BEGIN
- DELETE FROM exchange.reserves_out_by_reserve
- WHERE reserve_uuid = OLD.reserve_uuid;
- RETURN OLD;
-END $$;
-COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
- IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_delete
- AFTER DELETE
- ON reserves_out
- FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
-
-
--- ------------------------------ known_coins
----------------------------------------
-
-SELECT create_table_known_coins();
-
-COMMENT ON TABLE known_coins
- IS 'information about coins and their signatures, so we do not have to store
the signatures more than once if a coin is involved in multiple operations';
-COMMENT ON COLUMN known_coins.denominations_serial
- IS 'Denomination of the coin, determines the value of the original coin and
applicable fees for coin-specific operations.';
-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_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.';
-
-SELECT add_constraints_to_known_coins_partition('default');
-
-
--- ------------------------------ refresh_commitments
----------------------------------------
-
-SELECT create_table_refresh_commitments();
-
-COMMENT ON TABLE refresh_commitments
- IS 'Commitments made when melting coins and the gamma value chosen by the
exchange.';
-COMMENT ON COLUMN refresh_commitments.noreveal_index
- IS 'The gamma value chosen by the exchange in the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.rc
- IS 'Commitment made by the client, hash over the various client inputs in
the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.old_coin_pub
- IS 'Coin being melted in the refresh process.';
-
-SELECT add_constraints_to_refresh_commitments_partition('default');
-
-
--- ------------------------------ refresh_revealed_coins
----------------------------------------
-
-SELECT create_table_refresh_revealed_coins();
-
-COMMENT ON TABLE refresh_revealed_coins
- IS 'Revelations about the new coins that are to be created during a melting
session.';
-COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
- IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
- IS 'Identifies the refresh commitment (rc) of the melt operation.';
-COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index
- IS 'index of the fresh coin being created (one melt operation may result in
multiple fresh coins)';
-COMMENT ON COLUMN refresh_revealed_coins.coin_ev
- IS 'envelope of the new coin to be signed';
-COMMENT ON COLUMN refresh_revealed_coins.ewv
- IS 'exchange contributed values in the creation of the fresh coin (see
/csr)';
-COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
- IS 'hash of the envelope of the new coin to be signed (for lookups)';
-COMMENT ON COLUMN refresh_revealed_coins.ev_sig
- IS 'exchange signature over the envelope';
-
-SELECT add_constraints_to_refresh_revealed_coins_partition('default');
-
-
--- ------------------------------ refresh_transfer_keys
----------------------------------------
-
-SELECT create_table_refresh_transfer_keys();
-
-COMMENT ON TABLE refresh_transfer_keys
- IS 'Transfer keys of a refresh operation (the data revealed to the
exchange).';
-COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
- IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
- IS 'Identifies the refresh commitment (rc) of the operation.';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
- IS 'transfer public key for the gamma index';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
- IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been
revealed, with the gamma entry being skipped';
-
-SELECT add_constraints_to_refresh_transfer_keys_partition('default');
-
-
--- ------------------------------ deposits
----------------------------------------
-
-SELECT create_table_deposits();
-
-COMMENT ON TABLE deposits
- IS 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
-COMMENT ON COLUMN deposits.shard
- IS 'Used for load sharding in the materialized indices. Should be set based
on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
-COMMENT ON COLUMN deposits.known_coin_id
- IS 'Used for garbage collection';
-COMMENT ON COLUMN deposits.wire_target_h_payto
- IS 'Identifies the target bank account and KYC status';
-COMMENT ON COLUMN deposits.wire_salt
- IS 'Salt used when hashing the payto://-URI to get the h_wire';
-COMMENT ON COLUMN deposits.done
- IS 'Set to TRUE once we have included this deposit in some aggregate wire
transfer to the merchant';
-COMMENT ON COLUMN deposits.policy_blocked
- IS 'True if the aggregation of the deposit is currently blocked by some
policy extension mechanism. Used to filter out deposits that must not be
processed by the canonical deposit logic.';
-COMMENT ON COLUMN deposits.policy_details_serial_id
- IS 'References policy extensions table, NULL if extensions are not used';
-
-SELECT add_constraints_to_deposits_partition('default');
-
-
-SELECT create_table_deposits_by_ready();
-
-COMMENT ON TABLE deposits_by_ready
- IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER
below';
-
-
-SELECT create_table_deposits_for_matching();
-
-COMMENT ON TABLE deposits_for_matching
- IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via
TRIGGER below';
-
-CREATE OR REPLACE FUNCTION deposits_insert_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-DECLARE
- is_ready BOOLEAN;
-BEGIN
- is_ready = NOT (NEW.done OR NEW.policy_blocked);
-
- IF (is_ready)
- THEN
- INSERT INTO exchange.deposits_by_ready
- (wire_deadline
- ,shard
- ,coin_pub
- ,deposit_serial_id)
- VALUES
- (NEW.wire_deadline
- ,NEW.shard
- ,NEW.coin_pub
- ,NEW.deposit_serial_id);
- INSERT INTO exchange.deposits_for_matching
- (refund_deadline
- ,merchant_pub
- ,coin_pub
- ,deposit_serial_id)
- VALUES
- (NEW.refund_deadline
- ,NEW.merchant_pub
- ,NEW.coin_pub
- ,NEW.deposit_serial_id);
- END IF;
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_insert_trigger()
- IS 'Replicate deposit inserts into materialized indices.';
-
-CREATE TRIGGER deposits_on_insert
- AFTER INSERT
- ON deposits
- FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger();
-
-CREATE OR REPLACE FUNCTION deposits_update_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-DECLARE
- was_ready BOOLEAN;
-DECLARE
- is_ready BOOLEAN;
-BEGIN
- was_ready = NOT (OLD.done OR OLD.policy_blocked);
- is_ready = NOT (NEW.done OR NEW.policy_blocked);
- IF (was_ready AND NOT is_ready)
- THEN
- DELETE FROM exchange.deposits_by_ready
- WHERE wire_deadline = OLD.wire_deadline
- AND shard = OLD.shard
- AND coin_pub = OLD.coin_pub
- AND deposit_serial_id = OLD.deposit_serial_id;
- DELETE FROM exchange.deposits_for_matching
- WHERE refund_deadline = OLD.refund_deadline
- AND merchant_pub = OLD.merchant_pub
- AND coin_pub = OLD.coin_pub
- AND deposit_serial_id = OLD.deposit_serial_id;
- END IF;
- IF (is_ready AND NOT was_ready)
- THEN
- INSERT INTO exchange.deposits_by_ready
- (wire_deadline
- ,shard
- ,coin_pub
- ,deposit_serial_id)
- VALUES
- (NEW.wire_deadline
- ,NEW.shard
- ,NEW.coin_pub
- ,NEW.deposit_serial_id);
- INSERT INTO exchange.deposits_for_matching
- (refund_deadline
- ,merchant_pub
- ,coin_pub
- ,deposit_serial_id)
- VALUES
- (NEW.refund_deadline
- ,NEW.merchant_pub
- ,NEW.coin_pub
- ,NEW.deposit_serial_id);
- END IF;
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_update_trigger()
- IS 'Replicate deposits changes into materialized indices.';
-
-CREATE TRIGGER deposits_on_update
- AFTER UPDATE
- ON deposits
- FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger();
-
-CREATE OR REPLACE FUNCTION deposits_delete_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-DECLARE
- was_ready BOOLEAN;
-BEGIN
- was_ready = NOT (OLD.done OR OLD.policy_blocked);
-
- IF (was_ready)
- THEN
- DELETE FROM exchange.deposits_by_ready
- WHERE wire_deadline = OLD.wire_deadline
- AND shard = OLD.shard
- AND coin_pub = OLD.coin_pub
- AND deposit_serial_id = OLD.deposit_serial_id;
- DELETE FROM exchange.deposits_for_matching
- WHERE refund_deadline = OLD.refund_deadline
- AND merchant_pub = OLD.merchant_pub
- AND coin_pub = OLD.coin_pub
- AND deposit_serial_id = OLD.deposit_serial_id;
- END IF;
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_delete_trigger()
- IS 'Replicate deposit deletions into materialized indices.';
-
-CREATE TRIGGER deposits_on_delete
- AFTER DELETE
- ON deposits
- FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
-
-
--- ------------------------------ refunds
----------------------------------------
-
-SELECT create_table_refunds();
-
-COMMENT ON TABLE refunds
- IS 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.';
-COMMENT ON COLUMN refunds.deposit_serial_id
- IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub.
Multiple deposits may match a refund, this only identifies one of them.';
-COMMENT ON COLUMN refunds.rtransaction_id
- IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
-
-SELECT add_constraints_to_refunds_partition('default');
-
-
--- ------------------------------ wire_out
----------------------------------------
-
-SELECT create_table_wire_out();
-
-COMMENT ON TABLE wire_out
- IS 'wire transfers the exchange has executed';
-COMMENT ON COLUMN wire_out.exchange_account_section
- IS 'identifies the configuration section with the debit account of this
payment';
-COMMENT ON COLUMN wire_out.wire_target_h_payto
- IS 'Identifies the credited bank account and KYC status';
-
-SELECT add_constraints_to_wire_out_partition('default');
-
-CREATE OR REPLACE FUNCTION wire_out_delete_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-BEGIN
- DELETE FROM exchange.aggregation_tracking
- WHERE wtid_raw = OLD.wtid_raw;
- RETURN OLD;
-END $$;
-COMMENT ON FUNCTION wire_out_delete_trigger()
- IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces
an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint
and conflicted with nice partitioning.';
-
-CREATE TRIGGER wire_out_on_delete
- AFTER DELETE
- ON wire_out
- FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger();
-
-
-- ------------------------------ aggregation_transient
----------------------------------------
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index f7bf15f6..208e8196 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -38,7 +38,7 @@ COMMENT ON COLUMN exchange_tables.name
COMMENT ON COLUMN exchange_tables.version
IS 'Version of the DB in which the given action happened';
COMMENT ON COLUMN exchange_tables.action
- IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or
drop). Create, alter and drop are done for master and partitions; constrain is
only for partitions or for master if there are no partitions; foreign only on
master if there are no partitions.';
+ IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or
drop). Create, alter and drop are done for master and each partition; constrain
is only for partitions or for master if there are no partitions; master only on
master (takes no argument); foreign only on master if there are no partitions.';
COMMENT ON COLUMN exchange_tables.partitioned
IS 'TRUE if the table is partitioned';
COMMENT ON COLUMN exchange_tables.by_range
@@ -94,7 +94,7 @@ BEGIN
END
$$;
-COMMENT ON FUNCTION create_partitioned_table
+COMMENT ON FUNCTION comment_partitioned_table
IS 'Generic function to create a comment on table that is partitioned.';
@@ -121,7 +121,7 @@ BEGIN
END
$$;
-COMMENT ON FUNCTION create_partitioned_table
+COMMENT ON FUNCTION comment_partitioned_column
IS 'Generic function to create a comment on column of a table that is
partitioned.';
@@ -139,6 +139,7 @@ CREATE FUNCTION create_tables(
LANGUAGE plpgsql
AS $$
DECLARE
+ -- FIXME: use only ONE cursor and then switch on action!
tc CURSOR FOR
SELECT table_serial_id
,name
@@ -173,6 +174,17 @@ DECLARE
AND partitioned
AND action='foreign'
ORDER BY table_serial_id ASC;
+DECLARE
+ tm CURSOR FOR
+ SELECT table_serial_id
+ ,name
+ ,action
+ ,by_range
+ FROM exchange_tables
+ WHERE NOT finished
+ AND partitioned
+ AND action='master'
+ ORDER BY table_serial_id ASC;
BEGIN
-- run create/alter/drop actions
@@ -285,10 +297,9 @@ BEGIN
THEN
-- Add foreign constraints
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'PERFORM %s_table_%s ()'::text
,rec.action
,rec.name
- ,NULL
);
END IF
UPDATE exchange_tables
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-exchange] branch master updated (95149f34 -> 07b4b1aa), gnunet, 2022/11/27
- [taler-exchange] 01/15: -remove shard support, gnunet, 2022/11/27
- [taler-exchange] 08/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 07/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 02/15: intermediate step in major SQL refactoring (not done at all), gnunet, 2022/11/27
- [taler-exchange] 03/15: more sql refactoring, gnunet, 2022/11/27
- [taler-exchange] 04/15: more work on SQL refactoring,
gnunet <=
- [taler-exchange] 10/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 06/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 15/15: handle partner_serial_id being NULL, gnunet, 2022/11/27
- [taler-exchange] 14/15: -fix foreign key constraint issue, gnunet, 2022/11/27
- [taler-exchange] 13/15: -constraint no longer holds, gnunet, 2022/11/27
- [taler-exchange] 05/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 09/15: more sql refactoring, gnunet, 2022/11/27
- [taler-exchange] 11/15: -db tests pass again, gnunet, 2022/11/27
- [taler-exchange] 12/15: -cleanup, gnunet, 2022/11/27