aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/exchangedb/0002-aggregation_tracking.sql68
-rw-r--r--src/exchangedb/0002-aggregation_transient.sql41
-rw-r--r--src/exchangedb/0002-cs_nonce_locks.sql52
-rw-r--r--src/exchangedb/0002-prewire.sql55
-rw-r--r--src/exchangedb/0002-purse_decision.sql37
-rw-r--r--src/exchangedb/0002-purse_requests.sql110
-rw-r--r--src/exchangedb/0002-recoup.sql182
-rw-r--r--src/exchangedb/0002-recoup_refresh.sql106
-rw-r--r--src/exchangedb/exchange-0001-part.sql176
9 files changed, 523 insertions, 304 deletions
diff --git a/src/exchangedb/0002-aggregation_tracking.sql b/src/exchangedb/0002-aggregation_tracking.sql
index 25c394d26..d5c852e87 100644
--- a/src/exchangedb/0002-aggregation_tracking.sql
+++ b/src/exchangedb/0002-aggregation_tracking.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_aggregation_tracking(
+CREATE FUNCTION create_table_aggregation_tracking(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -23,22 +23,43 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'aggregation_tracking';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient depost -- or something else ???
- ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
+ 'CREATE TABLE %I'
+ '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',deposit_serial_id INT8 PRIMARY KEY'
+ ',wtid_raw BYTEA NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (deposit_serial_id)'
,shard_suffix
);
+ PERFORM comment_partitioned_table(
+ 'mapping from wire transfer identifiers (WTID) to deposits (and back)'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifier of the wire transfer'
+ ,'wtid_raw'
+ ,table_name
+ ,shard_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_aggregation_tracking(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'aggregation_tracking';
+BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index '
+ 'CREATE INDEX ' || table_name || '_by_wtid_raw_index '
'ON ' || table_name || ' '
'(wtid_raw);'
);
@@ -46,21 +67,28 @@ BEGIN
'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index '
'IS ' || quote_literal('for lookup_transactions') || ';'
);
-
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_aggregation_serial_id_key'
+ ' UNIQUE (aggregation_serial_id) '
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_aggregation_tracking()
RETURNS VOID
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'aggregation_tracking';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' '
- 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key '
- 'UNIQUE (aggregation_serial_id) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_deposit'
+ ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient deposit???
+ ',ADD CONSTRAINT ' || table_name || '_foreign_wtid_raw'
+ ' REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
);
END
$$;
@@ -77,4 +105,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('aggregation_tracking'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('aggregation_tracking'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-aggregation_transient.sql b/src/exchangedb/0002-aggregation_transient.sql
index 4739379e8..2d77e63ca 100644
--- a/src/exchangedb/0002-aggregation_transient.sql
+++ b/src/exchangedb/0002-aggregation_transient.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_aggregation_transient(
+CREATE FUNCTION create_table_aggregation_transient(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -23,22 +23,37 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'aggregation_transient';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
- ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)'
- ',exchange_account_section TEXT NOT NULL'
- ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)'
- ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)'
- ') %s ;'
+ 'CREATE TABLE %I '
+ '(amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
+ ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)'
+ ',exchange_account_section TEXT NOT NULL'
+ ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)'
+ ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (wire_target_h_payto)'
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Sum of all of the aggregated deposits (without deposit fees)'
+ ,'amount_val'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifier of the wire transfer'
+ ,'wtid_raw'
,table_name
- ,'PARTITION BY HASH (wire_target_h_payto)'
,shard_suffix
);
-
END
$$;
diff --git a/src/exchangedb/0002-cs_nonce_locks.sql b/src/exchangedb/0002-cs_nonce_locks.sql
index d34a84c8a..effc0045f 100644
--- a/src/exchangedb/0002-cs_nonce_locks.sql
+++ b/src/exchangedb/0002-cs_nonce_locks.sql
@@ -14,40 +14,65 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks(
- shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_cs_nonce_locks(
+ partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ 'CREATE TABLE %I'
+ '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)'
',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)'
',max_denomination_serial INT8 NOT NULL'
') %s ;'
,'cs_nonce_locks'
,'PARTITION BY HASH (nonce)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash'
+ ,'cs_nonce_locks'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'actual nonce submitted by the client'
+ ,'nonce'
+ ,'cs_nonce_locks'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with'
+ ,'op_hash'
+ ,'cs_nonce_locks'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Maximum number of a CS denomination serial the nonce could be used with, for GC'
+ ,'max_denomination_serial'
+ ,'cs_nonce_locks'
+ ,partition_suffix
);
-
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition(
+
+CREATE FUNCTION constrain_table_cs_nonce_locks(
IN partition_suffix VARCHAR
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'cs_nonce_locks';
BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' '
- 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key '
- 'UNIQUE (cs_nonce_lock_serial_id)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_cs_nonce_lock_serial_id_key'
+ ' UNIQUE (cs_nonce_lock_serial_id)'
);
END
$$;
@@ -64,4 +89,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('cs_nonce_locks'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-prewire.sql b/src/exchangedb/0002-prewire.sql
index e26475c12..fb8dc2212 100644
--- a/src/exchangedb/0002-prewire.sql
+++ b/src/exchangedb/0002-prewire.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_prewire(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_prewire(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,9 +23,8 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'prewire';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
',wire_method TEXT NOT NULL'
',finished BOOLEAN NOT NULL DEFAULT false'
@@ -34,13 +33,47 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (prewire_uuid)'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'pre-commit data for wire transfers we are about to execute'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'set to TRUE if the bank responded with a non-transient failure to our transfer request'
+ ,'failed'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'set to TRUE once bank confirmed receiving the wire transfer request'
+ ,'finished'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'serialized data to send to the bank to execute the wire transfer'
+ ,'buf'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_prewire(
+ IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'prewire';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index '
+ 'CREATE INDEX ' || table_name || '_by_finished_index '
'ON ' || table_name || ' '
'(finished);'
);
@@ -50,7 +83,7 @@ BEGIN
);
-- FIXME: find a way to combine these two indices?
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index '
+ 'CREATE INDEX ' || table_name || '_by_failed_finished_index '
'ON ' || table_name || ' '
'(failed,finished);'
);
@@ -58,7 +91,6 @@ BEGIN
'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index '
'IS ' || quote_literal('for wire_prepare_data_get') || ';'
);
-
END
$$;
@@ -74,4 +106,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('prewire'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-purse_decision.sql b/src/exchangedb/0002-purse_decision.sql
index 2039cd931..f7a82810b 100644
--- a/src/exchangedb/0002-purse_decision.sql
+++ b/src/exchangedb/0002-purse_decision.sql
@@ -15,7 +15,7 @@
--
-CREATE OR REPLACE FUNCTION create_table_purse_decision(
+CREATE FUNCTION create_table_purse_decision(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -24,10 +24,9 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'purse_decision';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE
+ 'CREATE TABLE %I '
+ '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)'
',action_timestamp INT8 NOT NULL'
',refunded BOOL NOT NULL'
@@ -37,23 +36,34 @@ BEGIN
,'PARTITION BY HASH (purse_pub)'
,shard_suffix
);
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
+ PERFORM comment_partitioned_table(
+ 'Purses that were decided upon (refund or merge)'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Public key of the purse'
+ ,'purse_pub'
+ ,table_name
+ ,shard_suffix
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition(
+CREATE FUNCTION constrain_table_purse_decision(
IN partition_suffix VARCHAR
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'purse_decision';
BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE purse_decision_' || partition_suffix || ' '
- 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key '
- 'UNIQUE (purse_decision_serial_id) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_purse_action_serial_id_key'
+ ' UNIQUE (purse_decision_serial_id) '
);
END
$$;
@@ -70,4 +80,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('purse_decision'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-purse_requests.sql b/src/exchangedb/0002-purse_requests.sql
index 9f0aef067..666546346 100644
--- a/src/exchangedb/0002-purse_requests.sql
+++ b/src/exchangedb/0002-purse_requests.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_purse_requests(
+CREATE FUNCTION create_table_purse_requests(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -23,10 +23,9 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'purse_requests';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE
+ 'CREATE TABLE %I '
+ '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)'
',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)'
',purse_creation INT8 NOT NULL'
@@ -48,38 +47,102 @@ BEGIN
,'PARTITION BY HASH (purse_pub)'
,shard_suffix
);
+ PERFORM comment_partitioned_table(
+ 'Requests establishing purses, associating them with a contract but without a target reserve'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Public key of the purse'
+ ,'purse_pub'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Local time when the purse was created. Determines applicable purse fees.'
+ ,'purse_creation'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'When the purse is set to expire'
+ ,'purse_expiration'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Hash of the contract the parties are to agree to'
+ ,'h_contract_terms'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'see the enum TALER_WalletAccountMergeFlags'
+ ,'flags'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'set to TRUE if this purse currently counts against the number of free purses in the respective reserve'
+ ,'in_reserve_quota'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Total amount expected to be in the purse'
+ ,'amount_with_fee_val'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Purse fee the client agreed to pay from the reserve (accepted by the exchange at the time the purse was created). Zero if in_reserve_quota is TRUE.'
+ ,'purse_fee_val'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Total amount actually in the purse (updated)'
+ ,'balance_val'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST'
+ ,'purse_sig'
+ ,table_name
+ ,shard_suffix
+ );
+END
+$$;
+CREATE FUNCTION constrain_table_purse_requests(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'purse_requests';
+BEGIN
table_name = concat_ws('_', table_name, shard_suffix);
-- FIXME: change to materialized index by merge_pub!
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub '
+ 'CREATE INDEX ' || table_name || '_merge_pub '
'ON ' || table_name || ' '
'(merge_pub);'
);
-
-- FIXME: drop index on master (crosses shards)?
-- Or use materialized index? (needed?)
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration '
+ 'CREATE INDEX ' || table_name || '_purse_expiration '
'ON ' || table_name || ' '
'(purse_expiration);'
);
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE purse_requests_' || partition_suffix || ' '
- 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key '
- 'UNIQUE (purse_requests_serial_id) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_purse_requests_serial_id_key'
+ ' UNIQUE (purse_requests_serial_id) '
);
END
$$;
@@ -96,4 +159,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('purse_requests'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql
index a3183610a..b8f4f4cca 100644
--- a/src/exchangedb/0002-recoup.sql
+++ b/src/exchangedb/0002-recoup.sql
@@ -14,8 +14,8 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE OR REPLACE FUNCTION create_table_recoup(
- IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_recoup(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -23,51 +23,98 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'recoup';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
+ 'CREATE TABLE %I'
+ '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
',amount_val INT8 NOT NULL'
',amount_frac INT4 NOT NULL'
',recoup_timestamp INT8 NOT NULL'
- ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE'
+ ',reserve_out_serial_id INT8 NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub);'
- ,shard_suffix
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'
+ ,'coin_pub'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.'
+ ,'reserve_out_serial_id'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP'
+ ,'coin_sig'
+ ,table_name
+ ,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.'
+ ,'coin_blind'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_recoup(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup';
+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 || '_recoup_uuid_key'
+ ' UNIQUE (recoup_uuid) '
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_recoup()
RETURNS VOID
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE recoup_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key '
- 'UNIQUE (recoup_uuid) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_reserves_out'
+ ' REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' REFERENCES known_coins (coin_pub)'
);
END
$$;
-CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve(
- IN shard_suffix VARCHAR DEFAULT NULL
+
+CREATE FUNCTION create_table_recoup_by_reserve(
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -75,25 +122,87 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'recoup_by_reserve';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE %I'
'(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE
',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
') %s ;'
,table_name
,'PARTITION BY HASH (reserve_out_serial_id)'
- ,shard_suffix
+ ,partition_suffix
);
+ PERFORM comment_partitioned_table(
+ 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.'
+ ,table_name
+ ,partition_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_recoup_by_reserve(
+ IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup_by_reserve';
+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 || ' '
'(reserve_out_serial_id);'
);
+END
+$$;
+
+CREATE FUNCTION recoup_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO exchange.recoup_by_reserve
+ (reserve_out_serial_id
+ ,coin_pub)
+ VALUES
+ (NEW.reserve_out_serial_id
+ ,NEW.coin_pub);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION recoup_insert_trigger()
+ IS 'Replicate recoup inserts into recoup_by_reserve table.';
+
+
+CREATE FUNCTION recoup_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM exchange.recoup_by_reserve
+ WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
+ AND coin_pub = OLD.coin_pub;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION recoup_delete_trigger()
+ IS 'Replicate recoup deletions into recoup_by_reserve table.';
+
+
+CREATE FUNCTION master_table_recoup()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CREATE TRIGGER recoup_on_insert
+ AFTER INSERT
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
+ CREATE TRIGGER recoup_on_delete
+ AFTER DELETE
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
END
$$;
@@ -109,4 +218,29 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('recoup'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('recoup'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
+ ,FALSE),
+ ('recoup_by_reserve'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('recoup_by_reserve'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('recoup'
+ ,'exchange-0002'
+ ,'master'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-recoup_refresh.sql b/src/exchangedb/0002-recoup_refresh.sql
index 9e6361a16..a5ca69a64 100644
--- a/src/exchangedb/0002-recoup_refresh.sql
+++ b/src/exchangedb/0002-recoup_refresh.sql
@@ -15,7 +15,7 @@
--
-CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
+CREATE FUNCTION create_table_recoup_refresh(
IN shard_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
@@ -24,53 +24,101 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'recoup_refresh';
BEGIN
-
PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
- ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',recoup_timestamp INT8 NOT NULL'
- ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE'
+ 'CREATE TABLE %I'
+ '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+ ',known_coin_id BIGINT NOT NULL'
+ ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+ ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+ ',amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',recoup_timestamp INT8 NOT NULL'
+ ',rrc_serial INT8 NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
,shard_suffix
);
+ PERFORM comment_partitioned_table(
+ 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'
+ ,'coin_pub'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'FIXME: (To be) used for garbage collection (in the absence of foreign constraints, in the future)'
+ ,'known_coin_id'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).'
+ ,'rrc_serial'
+ ,table_name
+ ,shard_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.'
+ ,'coin_blind'
+ ,table_name
+ ,shard_suffix
+ );
+END
+$$;
- table_name = concat_ws('_', table_name, shard_suffix);
+CREATE FUNCTION constrain_table_recoup_refresh(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup_refresh';
+BEGIN
+ table_name = concat_ws('_', table_name, shard_suffix);
-- FIXME: any query using this index will be slow. Materialize index or change query?
-- Also: which query uses this index?
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index '
- 'ON ' || table_name || ' '
+ 'CREATE INDEX ' || table_name || '_by_rrc_serial_index'
+ ' ON ' || table_name || ' '
'(rrc_serial);'
);
EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
- 'ON ' || table_name || ' '
+ 'CREATE INDEX ' || table_name || '_by_coin_pub_index'
+ ' ON ' || table_name || ' '
'(coin_pub);'
);
-
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_recoup_refresh_uuid_key'
+ ' UNIQUE (recoup_refresh_uuid) '
+ );
END
$$;
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition(
- IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_recoup_refresh()
RETURNS VOID
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup_refresh';
BEGIN
EXECUTE FORMAT (
- 'ALTER TABLE recoup_refresh_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key '
- 'UNIQUE (recoup_refresh_uuid) '
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' REFERENCES known_coins (coin_pub)'
+ ' ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id'
+ ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
+ ' ADD CONSTRAINT ' || table_name || '_foreign_rrc_serial'
+ ' REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE'
);
END
$$;
@@ -87,4 +135,14 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('recoup_refresh'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('recoup_refresh'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index 29412ca75..85f6c3e76 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -14,182 +14,6 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-
--- ------------------------------ aggregation_transient ----------------------------------------
-
-SELECT create_table_aggregation_transient();
-
-COMMENT ON TABLE aggregation_transient
- IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated';
-COMMENT ON COLUMN aggregation_transient.amount_val
- IS 'Sum of all of the aggregated deposits (without deposit fees)';
-COMMENT ON COLUMN aggregation_transient.wtid_raw
- IS 'identifier of the wire transfer';
-
--- ------------------------------ aggregation_tracking ----------------------------------------
-
-SELECT create_table_aggregation_tracking();
-
-COMMENT ON TABLE aggregation_tracking
- IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
-COMMENT ON COLUMN aggregation_tracking.wtid_raw
- IS 'identifier of the wire transfer';
-
-SELECT add_constraints_to_aggregation_tracking_partition('default');
-
-
--- ------------------------------ recoup ----------------------------------------
-
-SELECT create_table_recoup();
-
-COMMENT ON TABLE recoup
- IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.';
-COMMENT ON COLUMN recoup.coin_pub
- IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
-COMMENT ON COLUMN recoup.reserve_out_serial_id
- IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.';
-COMMENT ON COLUMN recoup.coin_sig
- IS 'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP';
-COMMENT ON COLUMN recoup.coin_blind
- IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.';
-
-SELECT add_constraints_to_recoup_partition('default');
-
-
-SELECT create_table_recoup_by_reserve();
-
-COMMENT ON TABLE recoup_by_reserve
- IS 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.';
-
-CREATE OR REPLACE FUNCTION recoup_insert_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-BEGIN
- INSERT INTO exchange.recoup_by_reserve
- (reserve_out_serial_id
- ,coin_pub)
- VALUES
- (NEW.reserve_out_serial_id
- ,NEW.coin_pub);
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION recoup_insert_trigger()
- IS 'Replicate recoup inserts into recoup_by_reserve table.';
-
-CREATE TRIGGER recoup_on_insert
- AFTER INSERT
- ON recoup
- FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
-
-CREATE OR REPLACE FUNCTION recoup_delete_trigger()
- RETURNS trigger
- LANGUAGE plpgsql
- AS $$
-BEGIN
- DELETE FROM exchange.recoup_by_reserve
- WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
- AND coin_pub = OLD.coin_pub;
- RETURN OLD;
-END $$;
-COMMENT ON FUNCTION recoup_delete_trigger()
- IS 'Replicate recoup deletions into recoup_by_reserve table.';
-
-CREATE TRIGGER recoup_on_delete
- AFTER DELETE
- ON recoup
- FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
-
-
--- ------------------------------ recoup_refresh ----------------------------------------
-
-SELECT create_table_recoup_refresh();
-
-COMMENT ON TABLE recoup_refresh
- IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.';
-COMMENT ON COLUMN recoup_refresh.coin_pub
- IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
-COMMENT ON COLUMN recoup_refresh.known_coin_id
- IS 'FIXME: (To be) used for garbage collection (in the future)';
-COMMENT ON COLUMN recoup_refresh.rrc_serial
- IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).';
-COMMENT ON COLUMN recoup_refresh.coin_blind
- IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.';
-
-SELECT add_constraints_to_recoup_refresh_partition('default');
-
-
--- ------------------------------ prewire ----------------------------------------
-
-SELECT create_table_prewire();
-
-COMMENT ON TABLE prewire
- IS 'pre-commit data for wire transfers we are about to execute';
-COMMENT ON COLUMN prewire.failed
- IS 'set to TRUE if the bank responded with a non-transient failure to our transfer request';
-COMMENT ON COLUMN prewire.finished
- IS 'set to TRUE once bank confirmed receiving the wire transfer request';
-COMMENT ON COLUMN prewire.buf
- IS 'serialized data to send to the bank to execute the wire transfer';
-
--- ------------------------------ cs_nonce_locks ----------------------------------------
-
-SELECT create_table_cs_nonce_locks();
-
-COMMENT ON TABLE cs_nonce_locks
- IS 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash';
-COMMENT ON COLUMN cs_nonce_locks.nonce
- IS 'actual nonce submitted by the client';
-COMMENT ON COLUMN cs_nonce_locks.op_hash
- IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with';
-COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
- IS 'Maximum number of a CS denomination serial the nonce could be used with, for GC';
-
-SELECT add_constraints_to_cs_nonce_locks_partition('default');
-
-
--- ------------------------------ purse_requests ----------------------------------------
-
-SELECT create_table_purse_requests();
-
-COMMENT ON TABLE purse_requests
- IS 'Requests establishing purses, associating them with a contract but without a target reserve';
-COMMENT ON COLUMN purse_requests.purse_pub
- IS 'Public key of the purse';
-COMMENT ON COLUMN purse_requests.purse_creation
- IS 'Local time when the purse was created. Determines applicable purse fees.';
-COMMENT ON COLUMN purse_requests.purse_expiration
- IS 'When the purse is set to expire';
-COMMENT ON COLUMN purse_requests.h_contract_terms
- IS 'Hash of the contract the parties are to agree to';
-COMMENT ON COLUMN purse_requests.flags
- IS 'see the enum TALER_WalletAccountMergeFlags';
-COMMENT ON COLUMN purse_requests.in_reserve_quota
- IS 'set to TRUE if this purse currently counts against the number of free purses in the respective reserve';
-COMMENT ON COLUMN purse_requests.amount_with_fee_val
- IS 'Total amount expected to be in the purse';
-COMMENT ON COLUMN purse_requests.purse_fee_val
- IS 'Purse fee the client agreed to pay from the reserve (accepted by the exchange at the time the purse was created). Zero if in_reserve_quota is TRUE.';
-COMMENT ON COLUMN purse_requests.balance_val
- IS 'Total amount actually in the purse';
-COMMENT ON COLUMN purse_requests.purse_sig
- IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST';
-
-SELECT add_constraints_to_purse_requests_partition('default');
-
-
--- ------------------------------ purse_decisions ----------------------------------------
-
-SELECT create_table_purse_decision();
-
-COMMENT ON TABLE purse_decision
- IS 'Purses that were decided upon (refund or merge)';
-COMMENT ON COLUMN purse_decision.purse_pub
- IS 'Public key of the purse';
-
-SELECT add_constraints_to_purse_decision_partition('default');
-
-
-- ------------------------------ purse_merges ----------------------------------------
SELECT create_table_purse_merges();