[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] 10/15: more work on SQL refactoring
|
From: |
gnunet |
|
Subject: |
[taler-exchange] 10/15: more work on SQL refactoring |
|
Date: |
Sun, 27 Nov 2022 22:14:34 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
commit cf2e37cd876651e799893e8fe5babb51a9e12dd7
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sun Nov 27 21:21:04 2022 +0100
more work on SQL refactoring
---
src/exchange-tools/taler-exchange-dbinit.c | 29 ++--------
src/exchangedb/0002-account_merges.sql | 2 +
src/exchangedb/0002-aggregation_tracking.sql | 12 +++--
src/exchangedb/0002-auditor_denom_sigs.sql | 16 +-----
src/exchangedb/0002-auditors.sql | 16 +-----
src/exchangedb/0002-close_requests.sql | 1 +
src/exchangedb/0002-cs_nonce_locks.sql | 2 +-
src/exchangedb/0002-deposits.sql | 9 ++--
src/exchangedb/0002-exchange_sign_keys.sql | 16 +-----
src/exchangedb/0002-extensions.sql | 16 +-----
src/exchangedb/0002-global_fee.sql | 18 +------
src/exchangedb/0002-history_requests.sql | 1 +
src/exchangedb/0002-known_coins.sql | 19 +++----
src/exchangedb/0002-kyc_alerts.sql | 16 +-----
src/exchangedb/0002-partner_accounts.sql | 16 +-----
src/exchangedb/0002-partners.sql | 16 +-----
src/exchangedb/0002-policy_details.sql | 18 +------
src/exchangedb/0002-policy_fulfillments.sql | 16 +-----
src/exchangedb/0002-profit_drains.sql | 16 +-----
src/exchangedb/0002-purse_decision.sql | 10 ++--
src/exchangedb/0002-purse_deposits.sql | 8 +--
src/exchangedb/0002-purse_merges.sql | 21 ++++----
src/exchangedb/0002-purse_requests.sql | 30 +++++------
src/exchangedb/0002-recoup.sql | 2 +
src/exchangedb/0002-recoup_refresh.sql | 23 ++++----
src/exchangedb/0002-refresh_commitments.sql | 1 +
src/exchangedb/0002-refresh_revealed_coins.sql | 24 +++++----
src/exchangedb/0002-refresh_transfer_keys.sql | 6 +--
src/exchangedb/0002-refunds.sql | 2 +
src/exchangedb/0002-reserves.sql | 5 ++
src/exchangedb/0002-reserves_close.sql | 47 ++++++++++++-----
src/exchangedb/0002-reserves_in.sql | 9 ++--
src/exchangedb/0002-reserves_open_requests.sql | 17 +++---
src/exchangedb/0002-reserves_out.sql | 20 +++----
src/exchangedb/0002-revolving_work_shards.sql | 18 +------
src/exchangedb/0002-signkey_revocations.sql | 16 +-----
src/exchangedb/0002-wad_in_entries.sql | 1 +
src/exchangedb/0002-wad_out_entries.sql | 1 +
src/exchangedb/0002-wads_out.sql | 1 +
src/exchangedb/0002-wire_accounts.sql | 16 +-----
src/exchangedb/0002-wire_fee.sql | 18 +------
src/exchangedb/0002-wire_targets.sql | 20 ++++---
src/exchangedb/0002-work_shards.sql | 18 +------
src/exchangedb/Makefile.am | 1 -
src/exchangedb/exchange-0001.sql | 48 ++++++++---------
src/exchangedb/pg_create_tables.c | 38 +++++++++++---
src/exchangedb/pg_create_tables.h | 9 +++-
src/exchangedb/pg_setup_partitions.c | 73 --------------------------
src/exchangedb/pg_setup_partitions.h | 39 --------------
src/exchangedb/plugin_exchangedb_postgres.c | 3 --
src/include/taler_exchangedb_plugin.h | 49 ++++-------------
51 files changed, 271 insertions(+), 578 deletions(-)
diff --git a/src/exchange-tools/taler-exchange-dbinit.c
b/src/exchange-tools/taler-exchange-dbinit.c
index db3d65a2..af4f381f 100644
--- a/src/exchange-tools/taler-exchange-dbinit.c
+++ b/src/exchange-tools/taler-exchange-dbinit.c
@@ -91,7 +91,9 @@ run (void *cls,
}
}
if (GNUNET_OK !=
- plugin->create_tables (plugin->cls))
+ plugin->create_tables (plugin->cls,
+ force_create_partitions || num_partitions > 0,
+ num_partitions))
{
fprintf (stderr,
"Failed to initialize database.\n");
@@ -100,31 +102,6 @@ run (void *cls,
global_ret = EXIT_NOPERMISSION;
return;
}
- if (1 <
- num_partitions
- || (
- 1 == num_partitions
- && force_create_partitions))
- {
- enum GNUNET_GenericReturnValue r = GNUNET_OK;
-
- r = plugin->setup_partitions (plugin->cls,
- num_partitions);
- if (GNUNET_OK != r)
- {
- GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
- "Could not setup partitions. Dropping default ones again\n");
- if (GNUNET_OK != plugin->drop_tables (plugin->cls))
- {
- GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
- "Could not drop tables after failed partitioning, please
delete the DB manually\n");
- }
- TALER_EXCHANGEDB_plugin_unload (plugin);
- plugin = NULL;
- global_ret = EXIT_NOTINSTALLED;
- return;
- }
- }
if (gc_db || clear_shards)
{
if (GNUNET_OK !=
diff --git a/src/exchangedb/0002-account_merges.sql
b/src/exchangedb/0002-account_merges.sql
index d6400f42..b1995f20 100644
--- a/src/exchangedb/0002-account_merges.sql
+++ b/src/exchangedb/0002-account_merges.sql
@@ -99,8 +99,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+ ' FOREIGN KEY (reserve_pub) '
' REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_purse_pub'
+ ' FOREIGN KEY (purse_pub) '
' REFERENCES purse_requests (purse_pub)'
);
END
diff --git a/src/exchangedb/0002-aggregation_tracking.sql
b/src/exchangedb/0002-aggregation_tracking.sql
index d5c852e8..bd636d6f 100644
--- a/src/exchangedb/0002-aggregation_tracking.sql
+++ b/src/exchangedb/0002-aggregation_tracking.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_aggregation_tracking(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -31,18 +31,18 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (deposit_serial_id)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'mapping from wire transfer identifiers (WTID) to deposits (and back)'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'identifier of the wire transfer'
,'wtid_raw'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
END
$$;
@@ -57,7 +57,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'aggregation_tracking';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_by_wtid_raw_index '
'ON ' || table_name || ' '
@@ -86,8 +86,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_deposit'
+ ' FOREIGN KEY (deposit_serial_id) '
' 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'
+ ' FOREIGN KEY (wtid_raw) '
' REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
);
END
diff --git a/src/exchangedb/0002-auditor_denom_sigs.sql
b/src/exchangedb/0002-auditor_denom_sigs.sql
index 681a8b8e..3ed645af 100644
--- a/src/exchangedb/0002-auditor_denom_sigs.sql
+++ b/src/exchangedb/0002-auditor_denom_sigs.sql
@@ -15,7 +15,7 @@
--
-CREATE TABLE IF NOT EXISTS auditor_denom_sigs
+CREATE TABLE auditor_denom_sigs
(auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE
CASCADE
,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
@@ -30,17 +30,3 @@ COMMENT ON COLUMN auditor_denom_sigs.denominations_serial
IS 'Denomination the signature is for.';
COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
IS 'Signature of the auditor, of purpose
TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('auditor_denom_sigs'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-auditors.sql b/src/exchangedb/0002-auditors.sql
index 3c18eef8..32ec8446 100644
--- a/src/exchangedb/0002-auditors.sql
+++ b/src/exchangedb/0002-auditors.sql
@@ -15,7 +15,7 @@
--
-CREATE TABLE IF NOT EXISTS auditors
+CREATE TABLE auditors
(auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
,auditor_name VARCHAR NOT NULL
@@ -33,17 +33,3 @@ COMMENT ON COLUMN auditors.is_active
IS 'true if we are currently supporting the use of this auditor.';
COMMENT ON COLUMN auditors.last_change
IS 'Latest time when active status changed. Used to detect replays of old
messages.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('auditors'
- ,'exchange-0001'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-close_requests.sql
b/src/exchangedb/0002-close_requests.sql
index 75151898..32149b1b 100644
--- a/src/exchangedb/0002-close_requests.sql
+++ b/src/exchangedb/0002-close_requests.sql
@@ -112,6 +112,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+ ' FOREIGN KEY (reserve_pub) '
' REFERENCES reserves(reserve_pub) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-cs_nonce_locks.sql
b/src/exchangedb/0002-cs_nonce_locks.sql
index effc0045..0cb88b3f 100644
--- a/src/exchangedb/0002-cs_nonce_locks.sql
+++ b/src/exchangedb/0002-cs_nonce_locks.sql
@@ -68,7 +68,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'cs_nonce_locks';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_cs_nonce_lock_serial_id_key'
diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql
index 2be51903..679103c4 100644
--- a/src/exchangedb/0002-deposits.sql
+++ b/src/exchangedb/0002-deposits.sql
@@ -26,7 +26,7 @@ BEGIN
PERFORM create_partitioned_table(
'CREATE TABLE %I'
'(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
- ',partition INT8 NOT NULL'
+ ',shard 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'
@@ -130,10 +130,13 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (coin_pub) '
' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_coin_id'
+ ' FOREIGN KEY (known_coin_id) '
' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_policy_details'
+ ' FOREIGN KEY (policy_details_serial_id) '
' REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE'
);
END
@@ -152,7 +155,7 @@ BEGIN
PERFORM create_partitioned_table(
'CREATE TABLE %I'
'(wire_deadline INT8 NOT NULL'
- ',partition INT8 NOT NULL'
+ ',shard INT8 NOT NULL'
',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
',deposit_serial_id INT8'
') %s ;'
@@ -182,7 +185,7 @@ BEGIN
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_main_index '
'ON ' || table_name || ' '
- '(wire_deadline ASC, partition ASC, coin_pub);'
+ '(wire_deadline ASC, shard ASC, coin_pub);'
);
END
$$;
diff --git a/src/exchangedb/0002-exchange_sign_keys.sql
b/src/exchangedb/0002-exchange_sign_keys.sql
index 17511418..d6acc6bb 100644
--- a/src/exchangedb/0002-exchange_sign_keys.sql
+++ b/src/exchangedb/0002-exchange_sign_keys.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS exchange_sign_keys
+CREATE TABLE exchange_sign_keys
(esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
@@ -34,17 +34,3 @@ COMMENT ON COLUMN exchange_sign_keys.expire_sign
IS 'Time when this online signing key will no longer be used to sign.';
COMMENT ON COLUMN exchange_sign_keys.expire_legal
IS 'Time when this online signing key legally expires.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('exchange_sign_keys'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-extensions.sql
b/src/exchangedb/0002-extensions.sql
index 299e8ddd..5642ea13 100644
--- a/src/exchangedb/0002-extensions.sql
+++ b/src/exchangedb/0002-extensions.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS extensions
+CREATE TABLE extensions
(extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,name VARCHAR NOT NULL UNIQUE
,manifest BYTEA
@@ -25,17 +25,3 @@ COMMENT ON COLUMN extensions.name
IS 'Name of the extension';
COMMENT ON COLUMN extensions.manifest
IS 'Manifest of the extension as JSON-blob, maybe NULL. It contains common
meta-information and extension-specific configuration.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('extensions'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-global_fee.sql
b/src/exchangedb/0002-global_fee.sql
index 8a63c010..0a2f9b49 100644
--- a/src/exchangedb/0002-global_fee.sql
+++ b/src/exchangedb/0002-global_fee.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS global_fee
+CREATE TABLE global_fee
(global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,start_date INT8 NOT NULL
,end_date INT8 NOT NULL
@@ -35,20 +35,6 @@ COMMENT ON TABLE global_fee
COMMENT ON COLUMN global_fee.global_fee_serial
IS 'needed for exchange-auditor replication logic';
-CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
+CREATE INDEX global_fee_by_end_date_index
ON global_fee
(end_date);
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('global_fee'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-history_requests.sql
b/src/exchangedb/0002-history_requests.sql
index a8dbeb6a..5cd5c7b7 100644
--- a/src/exchangedb/0002-history_requests.sql
+++ b/src/exchangedb/0002-history_requests.sql
@@ -74,6 +74,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+ ' FOREIGN KEY (reserve_pub) '
' REFERENCES reserves(reserve_pub) ON DELETE CASCADE'
);
END $$;
diff --git a/src/exchangedb/0002-known_coins.sql
b/src/exchangedb/0002-known_coins.sql
index a45c7bc8..4cdb974e 100644
--- a/src/exchangedb/0002-known_coins.sql
+++ b/src/exchangedb/0002-known_coins.sql
@@ -16,7 +16,7 @@
CREATE FUNCTION create_table_known_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -36,42 +36,42 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
+ ,partition_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
+ ,partition_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
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'EdDSA public key of the coin'
,'coin_pub'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Value of the coin that remains to be spent'
,'remaining_val'
,table_name
- ,shard_suffix
+ ,partition_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
+ ,partition_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
+ ,partition_suffix
);
END
$$;
@@ -86,7 +86,7 @@ AS $$
DECLARE
table_name VARCHAR default 'known_coins';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_known_coin_id_key'
@@ -106,6 +106,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_denominations'
+ ' FOREIGN KEY (denominations_serial) '
' REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-kyc_alerts.sql
b/src/exchangedb/0002-kyc_alerts.sql
index 74872a9c..8e54846c 100644
--- a/src/exchangedb/0002-kyc_alerts.sql
+++ b/src/exchangedb/0002-kyc_alerts.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS kyc_alerts
+CREATE TABLE kyc_alerts
(h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)
,trigger_type INT4 NOT NULL
,UNIQUE(trigger_type,h_payto)
@@ -25,17 +25,3 @@ COMMENT ON COLUMN kyc_alerts.h_payto
IS 'hash of the payto://-URI for which the KYC status changed';
COMMENT ON COLUMN kyc_alerts.trigger_type
IS 'identifies the receiver of the alert, as the same h_payto may require
multiple components to be notified';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('kyc_alerts'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-partner_accounts.sql
b/src/exchangedb/0002-partner_accounts.sql
index 2bf5a345..0f4af92c 100644
--- a/src/exchangedb/0002-partner_accounts.sql
+++ b/src/exchangedb/0002-partner_accounts.sql
@@ -15,7 +15,7 @@
--
-CREATE TABLE IF NOT EXISTS partner_accounts
+CREATE TABLE partner_accounts
(payto_uri VARCHAR PRIMARY KEY
,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64)
@@ -31,17 +31,3 @@ COMMENT ON COLUMN partner_accounts.partner_master_sig
IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS by the partner
master public key';
COMMENT ON COLUMN partner_accounts.last_seen
IS 'Last time we saw this account as being active at the partner exchange.
Used to select the most recent entry, and to detect when we should check
again.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('partner_accounts'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-partners.sql b/src/exchangedb/0002-partners.sql
index 992c04da..ff57f8fc 100644
--- a/src/exchangedb/0002-partners.sql
+++ b/src/exchangedb/0002-partners.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS partners
+CREATE TABLE partners
(partner_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32)
,start_date INT8 NOT NULL
@@ -47,17 +47,3 @@ COMMENT ON COLUMN partners.master_sig
CREATE INDEX IF NOT EXISTS partner_by_wad_time
ON partners (next_wad ASC);
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('partners'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-policy_details.sql
b/src/exchangedb/0002-policy_details.sql
index cd3c2f10..c9bfd157 100644
--- a/src/exchangedb/0002-policy_details.sql
+++ b/src/exchangedb/0002-policy_details.sql
@@ -16,8 +16,8 @@
-- FIXME: this table should be sharded!
-CREATE TABLE IF NOT EXISTS policy_details
- (policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+CREATE TABLE policy_details
+ (policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,policy_hash_code BYTEA PRIMARY KEY CHECK(LENGTH(policy_hash_code)=16)
,policy_json VARCHAR
,deadline INT8 NOT NULL
@@ -57,17 +57,3 @@ COMMENT ON COLUMN policy_details.fulfillment_state
- 5 (Timeout)';
COMMENT ON COLUMN policy_details.fulfillment_id
IS 'Reference to the proof of the fulfillment of this policy, if it exists.
Invariant: If not NULL, this entry''s .hash_code MUST be part of the
corresponding policy_fulfillments.policy_hash_codes array.';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('policy_details'
- ,'exchange-0002'
- ,'create'
- ,FALSE -- BAD! FIXME!
- ,FALSE);
diff --git a/src/exchangedb/0002-policy_fulfillments.sql
b/src/exchangedb/0002-policy_fulfillments.sql
index 6c01081a..54f44df5 100644
--- a/src/exchangedb/0002-policy_fulfillments.sql
+++ b/src/exchangedb/0002-policy_fulfillments.sql
@@ -16,7 +16,7 @@
-- FIXME: this table should be sharded!
-CREATE TABLE IF NOT EXISTS policy_fulfillments
+CREATE TABLE policy_fulfillments
(fulfillment_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE PRIMARY KEY
,fulfillment_timestamp INT8 NOT NULL
,fulfillment_proof VARCHAR
@@ -33,17 +33,3 @@ COMMENT ON COLUMN policy_fulfillments.h_fulfillment_proof
IS 'Hash of the fulfillment_proof';
COMMENT ON COLUMN policy_fulfillments.policy_hash_codes
IS 'Concatenation of the policy_hash_code of all policy_details that are
fulfilled by this proof';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('policy_fulfillments'
- ,'exchange-0002'
- ,'create'
- ,FALSE -- BAD! FIXME!
- ,FALSE);
diff --git a/src/exchangedb/0002-profit_drains.sql
b/src/exchangedb/0002-profit_drains.sql
index bb713cd2..4aba9b46 100644
--- a/src/exchangedb/0002-profit_drains.sql
+++ b/src/exchangedb/0002-profit_drains.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS profit_drains
+CREATE TABLE profit_drains
(profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32)
,account_section VARCHAR NOT NULL
@@ -41,17 +41,3 @@ COMMENT ON COLUMN profit_drains.master_sig
IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT';
COMMENT ON COLUMN profit_drains.executed
IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not
replicated to auditor';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('profit_drains'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-purse_decision.sql
b/src/exchangedb/0002-purse_decision.sql
index f7a82810..e738292c 100644
--- a/src/exchangedb/0002-purse_decision.sql
+++ b/src/exchangedb/0002-purse_decision.sql
@@ -16,7 +16,7 @@
CREATE FUNCTION create_table_purse_decision(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -34,18 +34,18 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (purse_pub)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'Purses that were decided upon (refund or merge)'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Public key of the purse'
,'purse_pub'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
END
$$;
@@ -59,7 +59,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'purse_decision';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_purse_action_serial_id_key'
diff --git a/src/exchangedb/0002-purse_deposits.sql
b/src/exchangedb/0002-purse_deposits.sql
index 25ccf1aa..9452f434 100644
--- a/src/exchangedb/0002-purse_deposits.sql
+++ b/src/exchangedb/0002-purse_deposits.sql
@@ -112,8 +112,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_partner'
+ ' FOREIGN KEY (partner_serial_id) '
' REFERENCES partners(partner_serial_id) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (coin_pub) '
' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
);
END
@@ -127,17 +129,17 @@ INSERT INTO exchange_tables
,partitioned
,by_range)
VALUES
- ('purse-deposits'
+ ('purse_deposits'
,'exchange-0002'
,'create'
,TRUE
,FALSE),
- ('purse-deposits'
+ ('purse_deposits'
,'exchange-0002'
,'constrain'
,TRUE
,FALSE),
- ('purse-deposits'
+ ('purse_deposits'
,'exchange-0002'
,'foreign'
,TRUE
diff --git a/src/exchangedb/0002-purse_merges.sql
b/src/exchangedb/0002-purse_merges.sql
index f7b9b7d6..df369514 100644
--- a/src/exchangedb/0002-purse_merges.sql
+++ b/src/exchangedb/0002-purse_merges.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_purse_merges(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -35,42 +35,42 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (purse_pub)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'Merge requests where a purse-owner requested merging the purse into the
account'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'identifies the partner exchange, NULL in case the target reserve lives
at this exchange'
,'partner_serial_id'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'public key of the target reserve'
,'reserve_pub'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'public key of the purse'
,'purse_pub'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'signature by the purse private key affirming the merge, of type
TALER_SIGNATURE_WALLET_PURSE_MERGE'
,'merge_sig'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'when was the merge message signed'
,'merge_timestamp'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
END
$$;
@@ -85,7 +85,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'purse_merges';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
-- FIXME: change to materialized index by reserve_pub!
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_reserve_pub '
@@ -115,10 +115,13 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_partner_serial_id'
+ ' FOREIGN KEY (partner_serial_id) '
' REFERENCES partners(partner_serial_id) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+ ' FOREIGN KEY (reserve_pub) '
' REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_purse_pub'
+ ' FOREIGN KEY (purse_pub) '
' REFERENCES purse_requests (purse_pub) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-purse_requests.sql
b/src/exchangedb/0002-purse_requests.sql
index 66654634..5038c241 100644
--- a/src/exchangedb/0002-purse_requests.sql
+++ b/src/exchangedb/0002-purse_requests.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_purse_requests(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -45,72 +45,72 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (purse_pub)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'Requests establishing purses, associating them with a contract but
without a target reserve'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Public key of the purse'
,'purse_pub'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Local time when the purse was created. Determines applicable purse fees.'
,'purse_creation'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'When the purse is set to expire'
,'purse_expiration'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Hash of the contract the parties are to agree to'
,'h_contract_terms'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'see the enum TALER_WalletAccountMergeFlags'
,'flags'
,table_name
- ,shard_suffix
+ ,partition_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
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Total amount expected to be in the purse'
,'amount_with_fee_val'
,table_name
- ,shard_suffix
+ ,partition_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
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Total amount actually in the purse (updated)'
,'balance_val'
,table_name
- ,shard_suffix
+ ,partition_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
+ ,partition_suffix
);
END
$$;
@@ -124,7 +124,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'purse_requests';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
-- FIXME: change to materialized index by merge_pub!
EXECUTE FORMAT (
@@ -132,7 +132,7 @@ BEGIN
'ON ' || table_name || ' '
'(merge_pub);'
);
- -- FIXME: drop index on master (crosses shards)?
+ -- FIXME: drop index on master (crosses partitions)?
-- Or use materialized index? (needed?)
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_purse_expiration '
diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql
index b8f4f4cc..36e36d9d 100644
--- a/src/exchangedb/0002-recoup.sql
+++ b/src/exchangedb/0002-recoup.sql
@@ -105,8 +105,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_reserves_out'
+ ' FOREIGN KEY (reserve_out_serial_id) '
' REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (coin_pub) '
' REFERENCES known_coins (coin_pub)'
);
END
diff --git a/src/exchangedb/0002-recoup_refresh.sql
b/src/exchangedb/0002-recoup_refresh.sql
index a5ca69a6..bfcfb3d8 100644
--- a/src/exchangedb/0002-recoup_refresh.sql
+++ b/src/exchangedb/0002-recoup_refresh.sql
@@ -16,7 +16,7 @@
CREATE FUNCTION create_table_recoup_refresh(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -38,36 +38,36 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
+ ,partition_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
+ ,partition_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
+ ,partition_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
+ ,partition_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
+ ,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 refresh operation.'
,'coin_blind'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
END
$$;
@@ -82,7 +82,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'recoup_refresh';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
-- FIXME: any query using this index will be slow. Materialize index or
change query?
-- Also: which query uses this index?
EXECUTE FORMAT (
@@ -114,10 +114,13 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (coin_pub) '
' REFERENCES known_coins (coin_pub)'
- ' ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id'
+ ' FOREIGN KEY (known_coin_id) '
' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
- ' ADD CONSTRAINT ' || table_name || '_foreign_rrc_serial'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_rrc_serial'
+ ' FOREIGN KEY (rrc_serial) '
' REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-refresh_commitments.sql
b/src/exchangedb/0002-refresh_commitments.sql
index c3d5cfde..c63995c7 100644
--- a/src/exchangedb/0002-refresh_commitments.sql
+++ b/src/exchangedb/0002-refresh_commitments.sql
@@ -100,6 +100,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (old_coin_pub) '
' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql
b/src/exchangedb/0002-refresh_revealed_coins.sql
index 998b0dc9..912e4bbb 100644
--- a/src/exchangedb/0002-refresh_revealed_coins.sql
+++ b/src/exchangedb/0002-refresh_revealed_coins.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_refresh_revealed_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -37,54 +37,54 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'Revelations about the new coins that are to be created during a melting
session.'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'needed for exchange-auditor replication logic'
,'rrc_serial'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Identifies the refresh commitment (rc) of the melt operation.'
,'melt_serial_id'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM 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
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'envelope of the new coin to be signed'
,'coin_ev'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'exchange contributed values in the creation of the fresh coin (see /csr)'
,'ewv'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'hash of the envelope of the new coin to be signed (for lookups)'
,'h_coin_ev'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'exchange signature over the envelope'
,'ev_sig'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
END
$$;
@@ -99,7 +99,7 @@ AS $$
DECLARE
table_name VARCHAR DEFAULT 'refresh_revealed_coins';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_coins_by_melt_serial_id_index '
'ON ' || table_name || ' '
@@ -129,8 +129,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_melt'
+ ' FOREIGN KEY (melt_serial_id)'
' REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_denom'
+ ' FOREIGN KEY (denominations_serial)'
' REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-refresh_transfer_keys.sql
b/src/exchangedb/0002-refresh_transfer_keys.sql
index 07801590..4d10dda1 100644
--- a/src/exchangedb/0002-refresh_transfer_keys.sql
+++ b/src/exchangedb/0002-refresh_transfer_keys.sql
@@ -86,19 +86,17 @@ END
$$;
-CREATE FUNCTION foreign_table_refresh_transfer_keys(
- IN partition_suffix VARCHAR
-)
+CREATE FUNCTION foreign_table_refresh_transfer_keys()
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 || 'foreign_melt_serial_id'
+ ' FOREIGN KEY (melt_serial_id)'
' REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql
index 82346694..88af42db 100644
--- a/src/exchangedb/0002-refunds.sql
+++ b/src/exchangedb/0002-refunds.sql
@@ -93,8 +93,10 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+ ' FOREIGN KEY (coin_pub) '
' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
',ADD CONSTRAINT ' || table_name || '_foreign_deposit'
+ ' FOREIGN KEY (deposit_serial_id) '
' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql
index e5db97fe..03d17aee 100644
--- a/src/exchangedb/0002-reserves.sql
+++ b/src/exchangedb/0002-reserves.sql
@@ -94,6 +94,11 @@ DECLARE
table_name VARCHAR DEFAULT 'reserves';
BEGIN
table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_unique_uuid'
+ ' UNIQUE (reserve_uuid)'
+ );
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_by_expiration_index '
'ON ' || table_name || ' '
diff --git a/src/exchangedb/0002-reserves_close.sql
b/src/exchangedb/0002-reserves_close.sql
index b68550a7..e93182bc 100644
--- a/src/exchangedb/0002-reserves_close.sql
+++ b/src/exchangedb/0002-reserves_close.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_reserves_close(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -25,8 +25,8 @@ DECLARE
BEGIN
PERFORM create_partitioned_table(
'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'
+ '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',reserve_pub BYTEA NOT NULL'
',execution_date INT8 NOT NULL'
',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
@@ -38,18 +38,18 @@ BEGIN
') %s ;'
,table_name
,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'wire transfers executed by the reserve to close reserves'
,table_name
- ,shard_suffix
+ ,partition_suffix
);
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
+ ,partition_suffix
);
END
$$;
@@ -64,19 +64,33 @@ AS $$
DECLARE
table_name VARCHAR default 'reserves_close';
BEGIN
- table_name = concat_ws('_', table_name, shard_suffix);
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name || ' '
- 'ADD CONSTRAINT ' || table_name || '_close_uuid_pkey '
- 'PRIMARY KEY (close_uuid)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_close_uuid_pkey'
+ ' PRIMARY KEY (close_uuid)'
);
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_by_reserve_pub_index '
- 'ON ' || table_name || ' '
- '(reserve_pub);'
+ 'ON ' || table_name || ' (reserve_pub);'
);
-END
-$$;
+END $$;
+
+
+CREATE FUNCTION foreign_table_reserves_close()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_close';
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+ ' FOREIGN KEY (reserve_pub) '
+ ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+ );
+END $$;
INSERT INTO exchange_tables
@@ -95,4 +109,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'constrain'
,TRUE
+ ,FALSE),
+ ('reserves_close'
+ ,'exchange-0002'
+ ,'foreign'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-reserves_in.sql
b/src/exchangedb/0002-reserves_in.sql
index a5ef4dc8..d722a49e 100644
--- a/src/exchangedb/0002-reserves_in.sql
+++ b/src/exchangedb/0002-reserves_in.sql
@@ -61,8 +61,7 @@ BEGIN
,table_name
,partition_suffix
);
-END
-$$;
+END $$;
CREATE FUNCTION constrain_table_reserves_in(
@@ -76,9 +75,9 @@ DECLARE
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)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_reserve_in_serial_id_key'
+ ' UNIQUE (reserve_in_serial_id)'
);
EXECUTE FORMAT (
'CREATE INDEX ' || table_name || '_by_reserve_in_serial_id_index '
diff --git a/src/exchangedb/0002-reserves_open_requests.sql
b/src/exchangedb/0002-reserves_open_requests.sql
index 96084c1d..bbd5ec90 100644
--- a/src/exchangedb/0002-reserves_open_requests.sql
+++ b/src/exchangedb/0002-reserves_open_requests.sql
@@ -64,11 +64,11 @@ DECLARE
BEGIN
table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name || ' '
- 'ADD CONSTRAINT ' || table_name || '_by_uuid '
- 'PRIMARY KEY (open_request_uuid),'
- 'ADD CONSTRAINT ' || table_name || '_by_time '
- 'UNIQUE (reserve_pub,request_timestamp)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_by_uuid'
+ ' PRIMARY KEY (open_request_uuid)'
+ ',ADD CONSTRAINT ' || table_name || '_by_time'
+ ' UNIQUE (reserve_pub,request_timestamp)'
);
END
$$;
@@ -82,9 +82,10 @@ 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'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub '
+ ' FOREIGN KEY (reserve_pub)'
+ ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
);
END
$$;
diff --git a/src/exchangedb/0002-reserves_out.sql
b/src/exchangedb/0002-reserves_out.sql
index 52567289..77112f50 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.sql
@@ -71,9 +71,9 @@ DECLARE
BEGIN
table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name || ' '
- 'ADD CONSTRAINT ' || table_name || '_reserve_out_serial_id_key '
- 'UNIQUE (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 (
@@ -97,11 +97,13 @@ DECLARE
table_name VARCHAR default 'reserves_out';
BEGIN
EXECUTE FORMAT (
- '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'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_denom'
+ ' FOREIGN KEY (denominations_serial)'
+ ' REFERENCES denominations (denominations_serial)'
+ ',ADD CONSTRAINT ' || table_name || '_foreign_reserve '
+ ' FOREIGN KEY (reserve_uuid)'
+ ' REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
);
END
$$;
@@ -125,7 +127,7 @@ BEGIN
,'PARTITION BY HASH (reserve_uuid)'
,partition_suffix
);
- PERFORM comment_partitioned_column (
+ PERFORM comment_partitioned_table (
'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
diff --git a/src/exchangedb/0002-revolving_work_shards.sql
b/src/exchangedb/0002-revolving_work_shards.sql
index 79196631..83094297 100644
--- a/src/exchangedb/0002-revolving_work_shards.sql
+++ b/src/exchangedb/0002-revolving_work_shards.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
+CREATE UNLOGGED TABLE revolving_work_shards
(shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,last_attempt INT8 NOT NULL
,start_row INT4 NOT NULL
@@ -38,23 +38,9 @@ COMMENT ON COLUMN revolving_work_shards.end_row
COMMENT ON COLUMN revolving_work_shards.job_name
IS 'unique name of the job the workers on this shard are performing';
-CREATE INDEX IF NOT EXISTS
revolving_work_shards_by_job_name_active_last_attempt_index
+CREATE INDEX revolving_work_shards_by_job_name_active_last_attempt_index
ON revolving_work_shards
(job_name
,active
,last_attempt
);
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('revolving_work_shards'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-signkey_revocations.sql
b/src/exchangedb/0002-signkey_revocations.sql
index 8e8b0a81..37ab32c6 100644
--- a/src/exchangedb/0002-signkey_revocations.sql
+++ b/src/exchangedb/0002-signkey_revocations.sql
@@ -14,24 +14,10 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS signkey_revocations
+CREATE TABLE signkey_revocations
(signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON
DELETE CASCADE
,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
);
COMMENT ON TABLE signkey_revocations
IS 'Table storing which online signing keys have been revoked';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('signkey_revocations'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-wad_in_entries.sql
b/src/exchangedb/0002-wad_in_entries.sql
index b8099f8d..63c8bca2 100644
--- a/src/exchangedb/0002-wad_in_entries.sql
+++ b/src/exchangedb/0002-wad_in_entries.sql
@@ -158,6 +158,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_wad_in'
+ ' FOREIGN KEY(wad_in_serial_id)'
' REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE'
);
END $$;
diff --git a/src/exchangedb/0002-wad_out_entries.sql
b/src/exchangedb/0002-wad_out_entries.sql
index 1db15156..45a4813c 100644
--- a/src/exchangedb/0002-wad_out_entries.sql
+++ b/src/exchangedb/0002-wad_out_entries.sql
@@ -157,6 +157,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_wad_out'
+ ' FOREIGN KEY(wad_out_serial_id)'
' REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-wads_out.sql b/src/exchangedb/0002-wads_out.sql
index d0e8d75f..edad4a68 100644
--- a/src/exchangedb/0002-wads_out.sql
+++ b/src/exchangedb/0002-wads_out.sql
@@ -98,6 +98,7 @@ BEGIN
EXECUTE FORMAT (
'ALTER TABLE ' || table_name ||
' ADD CONSTRAINT ' || table_name || '_foreign_partner'
+ ' FOREIGN KEY(partner_serial_id)'
' REFERENCES partners(partner_serial_id) ON DELETE CASCADE'
);
END
diff --git a/src/exchangedb/0002-wire_accounts.sql
b/src/exchangedb/0002-wire_accounts.sql
index d23ec730..628bc599 100644
--- a/src/exchangedb/0002-wire_accounts.sql
+++ b/src/exchangedb/0002-wire_accounts.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS wire_accounts
+CREATE TABLE wire_accounts
(payto_uri VARCHAR PRIMARY KEY
,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
,is_active BOOLEAN NOT NULL
@@ -32,17 +32,3 @@ COMMENT ON COLUMN wire_accounts.last_change
IS 'Latest time when active status changed. Used to detect replays of old
messages.';
-- "wire_accounts" has no sequence because it is a 'mutable' table
-- and is of no concern to the auditor
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('wire_accounts'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-wire_fee.sql b/src/exchangedb/0002-wire_fee.sql
index 7b53c6f3..deb26cef 100644
--- a/src/exchangedb/0002-wire_fee.sql
+++ b/src/exchangedb/0002-wire_fee.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS wire_fee
+CREATE TABLE wire_fee
(wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,wire_method VARCHAR NOT NULL
,start_date INT8 NOT NULL
@@ -31,20 +31,6 @@ COMMENT ON TABLE wire_fee
COMMENT ON COLUMN wire_fee.wire_fee_serial
IS 'needed for exchange-auditor replication logic';
-CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
+CREATE INDEX wire_fee_by_end_date_index
ON wire_fee
(end_date);
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('wire_fee'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/0002-wire_targets.sql
b/src/exchangedb/0002-wire_targets.sql
index afb9197a..5e542108 100644
--- a/src/exchangedb/0002-wire_targets.sql
+++ b/src/exchangedb/0002-wire_targets.sql
@@ -15,7 +15,7 @@
--
CREATE FUNCTION create_table_wire_targets(
- IN shard_suffix VARCHAR DEFAULT NULL
+ IN partition_suffix VARCHAR DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
@@ -29,23 +29,24 @@ BEGIN
') %s ;'
,'wire_targets'
,'PARTITION BY HASH (wire_target_h_payto)'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_table(
'All senders and recipients of money via the exchange'
,'wire_targets'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Can be a regular bank account, or also be a URI identifying a
reserve-account (for P2P payments)'
,'payto_uri'
,'wire_targets'
- ,shard_suffix
+ ,partition_suffix
);
PERFORM comment_partitioned_column(
'Unsalted hash of payto_uri'
,'wire_target_h_payto'
- ,shard_suffix
+ ,'wire_targets'
+ ,partition_suffix
);
END $$;
@@ -56,11 +57,14 @@ CREATE FUNCTION constrain_table_wire_targets(
RETURNS void
LANGUAGE plpgsql
AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'wire_targets';
BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
EXECUTE FORMAT (
- 'ALTER TABLE wire_targets_' || partition_suffix || ' '
- 'ADD CONSTRAINT wire_targets_' || partition_suffix ||
'_wire_target_serial_id_key '
- 'UNIQUE (wire_target_serial_id)'
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_wire_target_serial_id_key'
+ ' UNIQUE (wire_target_serial_id)'
);
END
$$;
diff --git a/src/exchangedb/0002-work_shards.sql
b/src/exchangedb/0002-work_shards.sql
index d3302205..fbe7e708 100644
--- a/src/exchangedb/0002-work_shards.sql
+++ b/src/exchangedb/0002-work_shards.sql
@@ -14,7 +14,7 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-CREATE TABLE IF NOT EXISTS work_shards
+CREATE TABLE work_shards
(shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,last_attempt INT8 NOT NULL
,start_row INT8 NOT NULL
@@ -38,23 +38,9 @@ COMMENT ON COLUMN work_shards.end_row
COMMENT ON COLUMN work_shards.job_name
IS 'unique name of the job the workers on this shard are performing';
-CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
+CREATE INDEX work_shards_by_job_name_completed_last_attempt_index
ON work_shards
(job_name
,completed
,last_attempt ASC
);
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('work_shards'
- ,'exchange-0002'
- ,'create'
- ,FALSE
- ,FALSE);
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am
index d7dd0895..1d4ba1f5 100644
--- a/src/exchangedb/Makefile.am
+++ b/src/exchangedb/Makefile.am
@@ -213,7 +213,6 @@ libtaler_plugin_exchangedb_postgres_la_SOURCES = \
pg_get_expired_reserves.c pg_get_expired_reserves.h \
pg_start.h pg_start.c \
pg_rollback.h pg_rollback.c \
- pg_setup_partitions.h pg_setup_partitions.c \
pg_get_purse_request.c pg_get_purse_request.h \
pg_get_reserve_history.c pg_get_reserve_history.h \
pg_get_unfinished_close_requests.c pg_get_unfinished_close_requests.h \
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index fad27add..49b5f8b7 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -61,12 +61,12 @@ RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
- IF partition_suffix IS NULL
+ IF (partition_suffix IS NULL)
THEN
-- no partitioning, disable option
main_table_partition_str = '';
ELSE
- IF partition_suffix > 0
+ IF (partition_suffix::int > 0)
THEN
-- sharding, add shard name
table_name=table_name || '_' || partition_suffix;
@@ -93,7 +93,7 @@ LANGUAGE plpgsql
AS $$
BEGIN
IF ( (partition_suffix IS NOT NULL) AND
- (partition_suffix > 0) )
+ (partition_suffix::int > 0) )
THEN
-- sharding, add shard name
table_name=table_name || '_' || partition_suffix;
@@ -120,7 +120,7 @@ LANGUAGE plpgsql
AS $$
BEGIN
IF ( (partition_suffix IS NOT NULL) AND
- (partition_suffix > 0) )
+ (partition_suffix::int > 0) )
THEN
-- sharding, add shard name
table_name=table_name || '_' || partition_suffix;
@@ -142,7 +142,7 @@ COMMENT ON FUNCTION comment_partitioned_column
---------------------------------------------------------------------------
-CREATE FUNCTION create_tables(
+CREATE FUNCTION do_create_tables(
num_partitions INTEGER
-- NULL: no partitions, add foreign constraints
-- 0: no partitions, no foreign constraints
@@ -159,7 +159,7 @@ DECLARE
,action
,partitioned
,by_range
- FROM exchange_tables
+ FROM exchange.exchange_tables
WHERE NOT finished
ORDER BY table_serial_id ASC;
BEGIN
@@ -167,14 +167,14 @@ BEGIN
LOOP
CASE rec.action
-- "create" actions apply to master and partitions
- WHEN "create"
+ WHEN 'create'
THEN
IF (rec.partitioned AND
(num_partitions IS NOT NULL))
THEN
-- Create master table with partitioning.
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s (%s)'::text
,rec.action
,rec.name
,0
@@ -187,7 +187,7 @@ BEGIN
THEN
-- Range partition
EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %s_default'
+ 'CREATE TABLE exchange.%s_default'
' PARTITION OF %s'
' FOR DEFAULT'
,rec.name
@@ -196,7 +196,7 @@ BEGIN
ELSE
-- Hash partition
EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %s_default'
+ 'CREATE TABLE exchange.%s_default'
' PARTITION OF %s'
' FOR VALUES WITH (MODULUS 1, REMAINDER 0)'
,rec.name
@@ -207,7 +207,7 @@ BEGIN
FOR i IN 1..num_partitions LOOP
-- Create num_partitions
EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I'
+ 'CREATE TABLE exchange.%I'
' PARTITION OF %I'
' FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
,rec.name || '_' || i
@@ -220,31 +220,29 @@ BEGIN
ELSE
-- Only create master table. No partitions.
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s ()'::text
,rec.action
,rec.name
- ,NULL
);
END IF;
-- Constrain action apply to master OR each partition
- WHEN "constrain"
+ WHEN 'constrain'
THEN
ASSERT rec.partitioned, 'constrain action only applies to partitioned
tables';
IF (num_partitions IS NULL)
THEN
-- Constrain master table
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s (NULL)'::text
,rec.action
,rec.name
- ,NULL
);
ELSE
IF (num_partitions = 0)
THEN
-- Constrain default table
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s (%s)'::text
,rec.action
,rec.name
,'default'
@@ -253,7 +251,7 @@ BEGIN
-- Constrain each partition
FOR i IN 1..num_partitions LOOP
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s (%s)'::text
,rec.action
,rec.name
,i
@@ -262,22 +260,22 @@ BEGIN
END IF;
END IF;
-- Foreign actions only apply if partitioning is off
- WHEN "foreign"
+ WHEN 'foreign'
THEN
IF (num_partitions IS NULL)
THEN
- -- Only create master table. No partitions.
+ -- Add foreign constraints
EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
+ 'SELECT exchange.%s_table_%s (%s)'::text
,rec.action
,rec.name
,NULL
);
END IF;
- WHEN "master"
+ WHEN 'master'
THEN
EXECUTE FORMAT(
- 'PERFORM %s_table_%s'::text
+ 'SELECT exchange.%s_table_%s ()'::text
,rec.action
,rec.name
);
@@ -285,13 +283,13 @@ BEGIN
ASSERT FALSE, 'unsupported action type: ' || rec.action;
END CASE; -- END CASE (rec.action)
-- Mark as finished
- UPDATE exchange_tables
+ UPDATE exchange.exchange_tables
SET finished=TRUE
WHERE table_serial_id=rec.table_serial_id;
END LOOP; -- create/alter/drop actions
END $$;
-COMMENT ON FUNCTION create_tables
+COMMENT ON FUNCTION do_create_tables
IS 'Creates all tables for the given number of partitions that need
creating. Does NOT support sharding.';
diff --git a/src/exchangedb/pg_create_tables.c
b/src/exchangedb/pg_create_tables.c
index 63211cf5..1d5728d8 100644
--- a/src/exchangedb/pg_create_tables.c
+++ b/src/exchangedb/pg_create_tables.c
@@ -27,21 +27,47 @@
enum GNUNET_GenericReturnValue
-TEH_PG_create_tables (void *cls)
+TEH_PG_create_tables (void *cls,
+ bool support_partitions,
+ uint32_t num_partitions)
{
struct PostgresClosure *pg = cls;
struct GNUNET_PQ_Context *conn;
- enum GNUNET_GenericReturnValue ret;
+ enum GNUNET_GenericReturnValue ret = GNUNET_OK;
+ struct GNUNET_PQ_QueryParam params[] = {
+ support_partitions
+ ? GNUNET_PQ_query_param_uint32 (&num_partitions)
+ : GNUNET_PQ_query_param_null (),
+ GNUNET_PQ_query_param_end
+ };
+ struct GNUNET_PQ_PreparedStatement ps[] = {
+ GNUNET_PQ_make_prepare ("create_tables",
+ "SELECT"
+ " exchange.do_create_tables"
+ " ($1);"),
+ GNUNET_PQ_PREPARED_STATEMENT_END
+ };
+ struct GNUNET_PQ_ExecuteStatement es[] = {
+ GNUNET_PQ_make_try_execute ("SET search_path TO exchange;"),
+ GNUNET_PQ_EXECUTE_STATEMENT_END
+ };
+
conn = GNUNET_PQ_connect_with_cfg (pg->cfg,
"exchangedb-postgres",
"exchange-",
- NULL,
- NULL);
+ es,
+ ps);
if (NULL == conn)
return GNUNET_SYSERR;
- ret = GNUNET_PQ_exec_sql (conn,
- "procedures");
+ if (0 >
+ GNUNET_PQ_eval_prepared_non_select (conn,
+ "create_tables",
+ params))
+ ret = GNUNET_SYSERR;
+ if (GNUNET_OK == ret)
+ ret = GNUNET_PQ_exec_sql (conn,
+ "procedures");
GNUNET_PQ_disconnect (conn);
return ret;
}
diff --git a/src/exchangedb/pg_create_tables.h
b/src/exchangedb/pg_create_tables.h
index 7fb7a56f..58f5aae7 100644
--- a/src/exchangedb/pg_create_tables.h
+++ b/src/exchangedb/pg_create_tables.h
@@ -29,9 +29,16 @@
* Create the necessary tables if they are not present
*
* @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param support_partitions true to enable partitioning support (disables
foreign key constraints)
+ * @param num_partitions number of partitions to create,
+ * (0 to not actually use partitions, 1 to only
+ * setup a default partition, >1 for real partitions)
* @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
*/
enum GNUNET_GenericReturnValue
-TEH_PG_create_tables (void *cls);
+TEH_PG_create_tables (void *cls,
+ bool support_partitions,
+ uint32_t num_partitions);
+
#endif
diff --git a/src/exchangedb/pg_setup_partitions.c
b/src/exchangedb/pg_setup_partitions.c
deleted file mode 100644
index 6785931a..00000000
--- a/src/exchangedb/pg_setup_partitions.c
+++ /dev/null
@@ -1,73 +0,0 @@
-/*
- This file is part of TALER
- Copyright (C) 2022 Taler Systems SA
-
- TALER is free software; you can redistribute it and/or modify it under the
- terms of the GNU General Public License as published by the Free Software
- Foundation; either version 3, or (at your option) any later version.
-
- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
-
- You should have received a copy of the GNU General Public License along with
- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
- */
-/**
- * @file exchangedb/pg_setup_partitions.c
- * @brief Implementation of the setup_partitions function for Postgres
- * @author Christian Grothoff
- */
-#include "platform.h"
-#include "taler_error_codes.h"
-#include "taler_dbevents.h"
-#include "taler_pq_lib.h"
-#include "pg_setup_partitions.h"
-#include "pg_helper.h"
-
-/**
- * Setup partitions of already existing tables
- *
- * @param cls the `struct PostgresClosure` with the plugin-specific state
- * @param num the number of partitions to create for each partitioned table
- * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
- */
-enum GNUNET_GenericReturnValue
-TEH_PG_setup_partitions (void *cls,
- uint32_t num)
-{
- struct PostgresClosure *pg = cls;
- struct GNUNET_PQ_Context *conn;
- enum GNUNET_GenericReturnValue ret = GNUNET_OK;
- struct GNUNET_PQ_QueryParam params[] = {
- GNUNET_PQ_query_param_uint32 (&num),
- GNUNET_PQ_query_param_end
- };
- struct GNUNET_PQ_PreparedStatement ps[] = {
- GNUNET_PQ_make_prepare ("setup_partitions",
- "SELECT"
- " create_partitions"
- " ($1);"),
- GNUNET_PQ_PREPARED_STATEMENT_END
- };
- struct GNUNET_PQ_ExecuteStatement es[] = {
- GNUNET_PQ_make_try_execute ("SET search_path TO exchange;"),
- GNUNET_PQ_EXECUTE_STATEMENT_END
- };
-
- conn = GNUNET_PQ_connect_with_cfg (pg->cfg,
- "exchangedb-postgres",
- NULL,
- es,
- ps);
- if (NULL == conn)
- return GNUNET_SYSERR;
- ret = GNUNET_OK;
- if (0 > GNUNET_PQ_eval_prepared_non_select (conn,
- "setup_partitions",
- params))
- ret = GNUNET_SYSERR;
- GNUNET_PQ_disconnect (conn);
- return ret;
-}
-
diff --git a/src/exchangedb/pg_setup_partitions.h
b/src/exchangedb/pg_setup_partitions.h
deleted file mode 100644
index a3f56ff1..00000000
--- a/src/exchangedb/pg_setup_partitions.h
+++ /dev/null
@@ -1,39 +0,0 @@
-/*
- This file is part of TALER
- Copyright (C) 2022 Taler Systems SA
-
- TALER is free software; you can redistribute it and/or modify it under the
- terms of the GNU General Public License as published by the Free Software
- Foundation; either version 3, or (at your option) any later version.
-
- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
-
- You should have received a copy of the GNU General Public License along with
- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
- */
-/**
- * @file exchangedb/pg_setup_partitions.h
- * @brief implementation of the setup_partitions function for Postgres
- * @author Christian Grothoff
- */
-#ifndef PG_SETUP_PARTITIONS_H
-#define PG_SETUP_PARTITIONS_H
-
-#include "taler_util.h"
-#include "taler_json_lib.h"
-#include "taler_exchangedb_plugin.h"
-
-/**
- * Setup partitions of already existing tables
- *
- * @param cls the `struct PostgresClosure` with the plugin-specific state
- * @param num the number of partitions to create for each partitioned table
- * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
- */
-enum GNUNET_GenericReturnValue
-TEH_PG_setup_partitions (void *cls,
- uint32_t num);
-
-#endif
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c
b/src/exchangedb/plugin_exchangedb_postgres.c
index 146d9f8c..cdb9b623 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -120,7 +120,6 @@
#include "pg_get_policy_details.h"
#include "pg_persist_policy_details.h"
#include "pg_do_deposit.h"
-#include "pg_setup_partitions.h"
#include "pg_add_policy_fulfillment_proof.h"
#include "pg_do_melt.h"
#include "pg_do_refund.h"
@@ -5434,8 +5433,6 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
= &TEH_PG_select_purse_by_merge_pub;
plugin->set_purse_balance
= &TEH_PG_set_purse_balance;
- plugin->setup_partitions
- = &TEH_PG_setup_partitions;
plugin->batch_reserves_in_insert
= &TEH_PG_batch_reserves_in_insert;
diff --git a/src/include/taler_exchangedb_plugin.h
b/src/include/taler_exchangedb_plugin.h
index 6f5dedd0..a2e3237f 100644
--- a/src/include/taler_exchangedb_plugin.h
+++ b/src/include/taler_exchangedb_plugin.h
@@ -3132,49 +3132,17 @@ struct TALER_EXCHANGEDB_Plugin
* Create the necessary tables if they are not present
*
* @param cls the @e cls of this struct with the plugin-specific state
+ * @param support_partitions true to enable partitioning support (disables
foreign key constraints)
+ * @param num_partitions number of partitions to create,
+ * (0 to not actually use partitions, 1 to only
+ * setup a default partition, >1 for real partitions)
* @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
*/
enum GNUNET_GenericReturnValue
- (*create_tables)(void *cls);
+ (*create_tables)(void *cls,
+ bool support_partitions,
+ uint32_t num_partitions);
- /**
- * Initialize the database of a shard node
- *
- * @param cls the @e cls of this struct with the plugin-specific state
- * @param idx the current shard index, will be appended to tables as suffix
- * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
- */
- enum GNUNET_GenericReturnValue
- (*create_shard_tables)(void *cls,
- uint32_t idx);
-
- /**
- * Change already present tables of the database to num partitions
- * Only has an effect if there are default partitions only
- *
- * @param cls the @e cls of this struct with the plugin-specific state
- * @param num the number of partitions to create for each partitioned table
- * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
- */
- enum GNUNET_GenericReturnValue
- (*setup_partitions)(void *cls,
- uint32_t num);
-
- /**
- * Change already present tables of the database to num foreign tables on
- * num foreign servers (shards).
- * Only has an effect if there are default partitions only
- *
- * @param cls the @e cls of this struct with the plugin-specific state
- * @param num the number of shard servers to create. The shard servers
- * must follow the numbering of [1-N], have the same user as
- * the master and have tables named $TABLE_$N where $N is the same
- * as the servers index of N.
- * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
- */
- enum GNUNET_GenericReturnValue
- (*setup_foreign_servers)(void *cls,
- uint32_t num);
/**
* Start a transaction.
@@ -3480,7 +3448,8 @@ struct TALER_EXCHANGEDB_Plugin
*/
enum GNUNET_DB_QueryStatus
(*batch_reserves_in_insert)(void *cls,
- const struct TALER_EXCHANGEDB_ReserveInInfo
*reserves,
+ const struct
+ TALER_EXCHANGEDB_ReserveInInfo *reserves,
unsigned int reserves_length,
enum GNUNET_DB_QueryStatus *results);
--
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, 2022/11/27
- [taler-exchange] 10/15: more work on SQL refactoring,
gnunet <=
- [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