[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] 03/15: more sql refactoring
|
From: |
gnunet |
|
Subject: |
[taler-exchange] 03/15: more sql refactoring |
|
Date: |
Sun, 27 Nov 2022 22:14:27 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
commit be2c11a1797d8d16b86439a80a4f110f82bb5829
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sun Nov 27 02:00:38 2022 +0100
more sql refactoring
---
src/exchangedb/0002-account_merges.sql | 14 +
src/exchangedb/0002-aggregation_tracking.sql | 14 +
src/exchangedb/0002-aggregation_transient.sql | 14 +
src/exchangedb/0002-auditor_denom_sigs.sql | 46 ++
src/exchangedb/0002-auditors.sql | 49 ++
src/exchangedb/0002-close_requests.sql | 14 +
src/exchangedb/0002-contracts.sql | 14 +
src/exchangedb/0002-cs_nonce_locks.sql | 14 +
src/exchangedb/0002-deposits.sql | 24 +
src/exchangedb/0002-exchange_sign_keys.sql | 50 ++
.../{exchange-0003.sql.in => 0002-extensions.sql} | 44 +-
src/exchangedb/0002-global_fee.sql | 54 ++
src/exchangedb/0002-history_requests.sql | 14 +
src/exchangedb/0002-known_coins.sql | 14 +
src/exchangedb/0002-legitimization_processes.sql | 14 +
.../0002-legitimization_requirements.sql | 14 +
...tner_accounts.sql => 0002-partner_accounts.sql} | 14 +
src/exchangedb/0002-partners.sql | 63 ++
src/exchangedb/0002-policy_details.sql | 73 ++
src/exchangedb/0002-policy_fulfillments.sql | 49 ++
src/exchangedb/0002-prewire.sql | 14 +
src/exchangedb/0002-purse_decision.sql | 14 +
src/exchangedb/0002-purse_deposits.sql | 14 +
src/exchangedb/0002-purse_merges.sql | 14 +
src/exchangedb/0002-purse_requests.sql | 14 +
src/exchangedb/0002-recoup.sql | 14 +
src/exchangedb/0002-recoup_refresh.sql | 14 +
src/exchangedb/0002-refresh_commitments.sql | 14 +
src/exchangedb/0002-refresh_revealed_coins.sql | 14 +
src/exchangedb/0002-refresh_transfer_keys.sql | 14 +
src/exchangedb/0002-refunds.sql | 16 +
src/exchangedb/0002-reserves.sql | 14 +
src/exchangedb/0002-reserves_close.sql | 14 +
src/exchangedb/0002-reserves_in.sql | 14 +
src/exchangedb/0002-reserves_open_deposits.sql | 14 +
src/exchangedb/0002-reserves_open_requests.sql | 14 +
src/exchangedb/0002-reserves_out.sql | 19 +
src/exchangedb/0002-revolving_work_shards.sql | 60 ++
...ge-0003.sql.in => 0002-signkey_revocations.sql} | 40 +-
...wads_in_entries.sql => 0002-wad_in_entries.sql} | 14 +
src/exchangedb/0002-wad_out_entries.sql | 14 +
src/exchangedb/0002-wads_in.sql | 14 +
src/exchangedb/0002-wads_out.sql | 14 +
src/exchangedb/0002-wire_accounts.sql | 48 ++
src/exchangedb/0002-wire_fee.sql | 50 ++
src/exchangedb/0002-wire_out.sql | 14 +
src/exchangedb/0002-wire_targets.sql | 30 +-
src/exchangedb/0002-work_shards.sql | 60 ++
src/exchangedb/0003-purse_actions.sql | 14 +
src/exchangedb/common-0002.sql | 83 ---
src/exchangedb/exchange-0001-part.sql | 511 -------------
src/exchangedb/exchange-0001.sql | 828 +++++----------------
src/exchangedb/exchange-0002.sql.in | 16 +-
src/exchangedb/exchange-0003.sql.in | 1 -
54 files changed, 1369 insertions(+), 1293 deletions(-)
diff --git a/src/exchangedb/0002-account_merges.sql
b/src/exchangedb/0002-account_merges.sql
index 88cbea13..c4102ac6 100644
--- a/src/exchangedb/0002-account_merges.sql
+++ b/src/exchangedb/0002-account_merges.sql
@@ -64,3 +64,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('account_merges'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-aggregation_tracking.sql
b/src/exchangedb/0002-aggregation_tracking.sql
index 7e6977f9..25c394d2 100644
--- a/src/exchangedb/0002-aggregation_tracking.sql
+++ b/src/exchangedb/0002-aggregation_tracking.sql
@@ -64,3 +64,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('aggregation_tracking'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-aggregation_transient.sql
b/src/exchangedb/0002-aggregation_transient.sql
index c2731049..4739379e 100644
--- a/src/exchangedb/0002-aggregation_transient.sql
+++ b/src/exchangedb/0002-aggregation_transient.sql
@@ -41,3 +41,17 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('aggregation_transient'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-auditor_denom_sigs.sql
b/src/exchangedb/0002-auditor_denom_sigs.sql
new file mode 100644
index 00000000..681a8b8e
--- /dev/null
+++ b/src/exchangedb/0002-auditor_denom_sigs.sql
@@ -0,0 +1,46 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+
+CREATE TABLE IF NOT EXISTS 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
+ ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
+ ,PRIMARY KEY (denominations_serial, auditor_uuid)
+ );
+COMMENT ON TABLE auditor_denom_sigs
+ IS 'Table with auditor signatures on exchange denomination keys.';
+COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid
+ IS 'Identifies the auditor.';
+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
new file mode 100644
index 00000000..3c18eef8
--- /dev/null
+++ b/src/exchangedb/0002-auditors.sql
@@ -0,0 +1,49 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+
+CREATE TABLE IF NOT EXISTS 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
+ ,auditor_url VARCHAR NOT NULL
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
+ );
+COMMENT ON TABLE auditors
+ IS 'Table with auditors the exchange uses or has used in the past. Entries
never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN auditors.auditor_pub
+ IS 'Public key of the auditor.';
+COMMENT ON COLUMN auditors.auditor_url
+ IS 'The base URL of the auditor.';
+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 8168b799..103342c2 100644
--- a/src/exchangedb/0002-close_requests.sql
+++ b/src/exchangedb/0002-close_requests.sql
@@ -83,3 +83,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('close_requests'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-contracts.sql
b/src/exchangedb/0002-contracts.sql
index d51ced57..224430c9 100644
--- a/src/exchangedb/0002-contracts.sql
+++ b/src/exchangedb/0002-contracts.sql
@@ -57,3 +57,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('contracts'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-cs_nonce_locks.sql
b/src/exchangedb/0002-cs_nonce_locks.sql
index e651fe15..d34a84c8 100644
--- a/src/exchangedb/0002-cs_nonce_locks.sql
+++ b/src/exchangedb/0002-cs_nonce_locks.sql
@@ -51,3 +51,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('cs_nonce_locks'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql
index 404deeba..35210443 100644
--- a/src/exchangedb/0002-deposits.sql
+++ b/src/exchangedb/0002-deposits.sql
@@ -144,3 +144,27 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('deposits'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('deposits_by_ready' -- FIXME: do this?
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('deposits_for_matching' -- FIXME: do this?
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-exchange_sign_keys.sql
b/src/exchangedb/0002-exchange_sign_keys.sql
new file mode 100644
index 00000000..17511418
--- /dev/null
+++ b/src/exchangedb/0002-exchange_sign_keys.sql
@@ -0,0 +1,50 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS 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)
+ ,valid_from INT8 NOT NULL
+ ,expire_sign INT8 NOT NULL
+ ,expire_legal INT8 NOT NULL
+ );
+COMMENT ON TABLE exchange_sign_keys
+ IS 'Table with master public key signatures on exchange online signing
keys.';
+COMMENT ON COLUMN exchange_sign_keys.exchange_pub
+ IS 'Public online signing key of the exchange.';
+COMMENT ON COLUMN exchange_sign_keys.master_sig
+ IS 'Signature affirming the validity of the signing key of purpose
TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
+COMMENT ON COLUMN exchange_sign_keys.valid_from
+ IS 'Time when this online signing key will first be used to sign messages.';
+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/exchange-0003.sql.in
b/src/exchangedb/0002-extensions.sql
similarity index 52%
copy from src/exchangedb/exchange-0003.sql.in
copy to src/exchangedb/0002-extensions.sql
index ee03d440..299e8ddd 100644
--- a/src/exchangedb/exchange-0003.sql.in
+++ b/src/exchangedb/0002-extensions.sql
@@ -14,22 +14,28 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
--- Everything in one big transaction
-BEGIN;
-
--- Check patch versioning is in place.
-SELECT _v.register_patch('exchange-0003', NULL, NULL);
-
--------------------- Schema ----------------------------
-
-CREATE SCHEMA exchange;
-COMMENT ON SCHEMA exchange IS 'taler-exchange data';
-
-SET search_path TO exchange;
-
-
-#include "0003-partner_accounts.sql"
-#include "0003-purse_actions.sql"
-#include "0003-purse_deletion.sql"
-
-COMMIT;
+CREATE TABLE IF NOT EXISTS extensions
+ (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,name VARCHAR NOT NULL UNIQUE
+ ,manifest BYTEA
+ );
+COMMENT ON TABLE extensions
+ IS 'Configurations of the activated extensions';
+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
new file mode 100644
index 00000000..8a63c010
--- /dev/null
+++ b/src/exchangedb/0002-global_fee.sql
@@ -0,0 +1,54 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS global_fee
+ (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,history_fee_val INT8 NOT NULL
+ ,history_fee_frac INT4 NOT NULL
+ ,account_fee_val INT8 NOT NULL
+ ,account_fee_frac INT4 NOT NULL
+ ,purse_fee_val INT8 NOT NULL
+ ,purse_fee_frac INT4 NOT NULL
+ ,purse_timeout INT8 NOT NULL
+ ,history_expiration INT8 NOT NULL
+ ,purse_account_limit INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (start_date)
+ );
+COMMENT ON TABLE global_fee
+ IS 'list of the global fees of this exchange, by date';
+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
+ 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 0b579759..0df5ae01 100644
--- a/src/exchangedb/0002-history_requests.sql
+++ b/src/exchangedb/0002-history_requests.sql
@@ -42,3 +42,17 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('history_requests'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-known_coins.sql
b/src/exchangedb/0002-known_coins.sql
index 786d2a6c..af2610c6 100644
--- a/src/exchangedb/0002-known_coins.sql
+++ b/src/exchangedb/0002-known_coins.sql
@@ -59,3 +59,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('known_coins'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-legitimization_processes.sql
b/src/exchangedb/0002-legitimization_processes.sql
index 598603c7..1be54c3c 100644
--- a/src/exchangedb/0002-legitimization_processes.sql
+++ b/src/exchangedb/0002-legitimization_processes.sql
@@ -70,3 +70,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('legitimization_processes'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-legitimization_requirements.sql
b/src/exchangedb/0002-legitimization_requirements.sql
index 34655f65..c58d2431 100644
--- a/src/exchangedb/0002-legitimization_requirements.sql
+++ b/src/exchangedb/0002-legitimization_requirements.sql
@@ -58,3 +58,17 @@ BEGIN
'UNIQUE (legitimization_requirement_serial_id)');
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('legitimization_requirements'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0003-partner_accounts.sql
b/src/exchangedb/0002-partner_accounts.sql
similarity index 90%
rename from src/exchangedb/0003-partner_accounts.sql
rename to src/exchangedb/0002-partner_accounts.sql
index 6ed372f5..2bf5a345 100644
--- a/src/exchangedb/0003-partner_accounts.sql
+++ b/src/exchangedb/0002-partner_accounts.sql
@@ -31,3 +31,17 @@ 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
new file mode 100644
index 00000000..992c04da
--- /dev/null
+++ b/src/exchangedb/0002-partners.sql
@@ -0,0 +1,63 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS 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
+ ,end_date INT8 NOT NULL
+ ,next_wad INT8 NOT NULL DEFAULT (0)
+ ,wad_frequency INT8 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,partner_base_url TEXT NOT NULL
+ );
+COMMENT ON TABLE partners
+ IS 'exchanges we do wad transfers to';
+COMMENT ON COLUMN partners.partner_master_pub
+ IS 'offline master public key of the partner';
+COMMENT ON COLUMN partners.start_date
+ IS 'starting date of the partnership';
+COMMENT ON COLUMN partners.end_date
+ IS 'end date of the partnership';
+COMMENT ON COLUMN partners.next_wad
+ IS 'at what time should we do the next wad transfer to this partner
(frequently updated); set to forever after the end_date';
+COMMENT ON COLUMN partners.wad_frequency
+ IS 'how often do we promise to do wad transfers';
+COMMENT ON COLUMN partners.wad_fee_val
+ IS 'how high is the fee for a wallet to be added to a wad to this partner';
+COMMENT ON COLUMN partners.partner_base_url
+ IS 'base URL of the REST API for this partner';
+COMMENT ON COLUMN partners.master_sig
+ IS 'signature of our master public key affirming the partnership, of purpose
TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
+
+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
new file mode 100644
index 00000000..cd3c2f10
--- /dev/null
+++ b/src/exchangedb/0002-policy_details.sql
@@ -0,0 +1,73 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+-- FIXME: this table should be sharded!
+
+CREATE TABLE IF NOT EXISTS policy_details
+ (policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,policy_hash_code BYTEA PRIMARY KEY CHECK(LENGTH(policy_hash_code)=16)
+ ,policy_json VARCHAR
+ ,deadline INT8 NOT NULL
+ ,commitment_val INT8 NOT NULL
+ ,commitment_frac INT4 NOT NULL
+ ,accumulated_total_val INT8 NOT NULL
+ ,accumulated_total_frac INT4 NOT NULL
+ ,fee_val INT8 NOT NULL
+ ,fee_frac INT4 NOT NULL
+ ,transferable_val INT8 NOT NULL
+ ,transferable_frac INT8 NOT NULL
+ ,fulfillment_state smallint NOT NULL CHECK(fulfillment_state between 0 and 5)
+ ,fulfillment_id BIGINT NULL REFERENCES policy_fulfillments (fulfillment_id)
ON DELETE CASCADE
+ );
+COMMENT ON TABLE policy_details
+ IS 'Policies that were provided with deposits via policy extensions.';
+COMMENT ON COLUMN policy_details.policy_hash_code
+ IS 'ID (GNUNET_HashCode) that identifies a policy. Will be calculated by
the policy extension based on the content';
+COMMENT ON COLUMN policy_details.policy_json
+ IS 'JSON object with options set that the exchange needs to consider when
executing a deposit. Supported details depend on the policy extensions
supported by the exchange.';
+COMMENT ON COLUMN policy_details.deadline
+ IS 'Deadline until the policy must be marked as fulfilled (maybe "forever")';
+COMMENT ON COLUMN policy_details.commitment_val
+ IS 'The amount that this policy commits to. Invariant: commitment >= fee';
+COMMENT ON COLUMN policy_details.accumulated_total_val
+ IS 'The sum of all contributions of all deposit that reference this policy.
Invariant: The fulfilment_state must be Insufficient as long as
accumulated_total < commitment';
+COMMENT ON COLUMN policy_details.fee_val
+ IS 'The fee for this policy, due when the policy is fulfilled or timed out';
+COMMENT ON COLUMN policy_details.transferable_val
+ IS 'The amount that on fulfillment or timeout will be transferred to the
payto-URI''s of the corresponding deposit''s. The policy fees must have been
already deducted from it. Invariant: fee+transferable <= accumulated_total.
The remaining amount (accumulated_total - fee - transferable) can be refreshed
by the owner of the coins when the state is Timeout or Success.';
+COMMENT ON COLUMN policy_details.fulfillment_state
+ IS 'State of the fulfillment:
+ - 0 (Failure)
+ - 1 (Insufficient)
+ - 2 (Ready)
+ - 4 (Success)
+ - 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
new file mode 100644
index 00000000..6c01081a
--- /dev/null
+++ b/src/exchangedb/0002-policy_fulfillments.sql
@@ -0,0 +1,49 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+-- FIXME: this table should be sharded!
+
+CREATE TABLE IF NOT EXISTS policy_fulfillments
+ (fulfillment_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE PRIMARY KEY
+ ,fulfillment_timestamp INT8 NOT NULL
+ ,fulfillment_proof VARCHAR
+ ,h_fulfillment_proof BYTEA NOT NULL CHECK(LENGTH(h_fulfillment_proof) = 64)
UNIQUE
+ ,policy_hash_codes BYTEA NOT NULL CHECK(0 = MOD(LENGTH(policy_hash_codes),
16))
+ );
+COMMENT ON TABLE policy_fulfillments
+ IS 'Proofs of fulfillment of policies that were set in deposits';
+COMMENT ON COLUMN policy_fulfillments.fulfillment_timestamp
+ IS 'Timestamp of the arrival of a proof of fulfillment';
+COMMENT ON COLUMN policy_fulfillments.fulfillment_proof
+ IS 'JSON object with a proof of the fulfillment of a policy. Supported
details depend on the policy extensions supported by the exchange.';
+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-prewire.sql b/src/exchangedb/0002-prewire.sql
index 451545e6..e26475c1 100644
--- a/src/exchangedb/0002-prewire.sql
+++ b/src/exchangedb/0002-prewire.sql
@@ -61,3 +61,17 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('prewire'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-purse_decision.sql
b/src/exchangedb/0002-purse_decision.sql
index 3905faaf..2039cd93 100644
--- a/src/exchangedb/0002-purse_decision.sql
+++ b/src/exchangedb/0002-purse_decision.sql
@@ -57,3 +57,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('purse_decision'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-purse_deposits.sql
b/src/exchangedb/0002-purse_deposits.sql
index 375b0b69..0e0c9807 100644
--- a/src/exchangedb/0002-purse_deposits.sql
+++ b/src/exchangedb/0002-purse_deposits.sql
@@ -66,3 +66,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('purse-deposits'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-purse_merges.sql
b/src/exchangedb/0002-purse_merges.sql
index 1e861cc5..08d7ca5f 100644
--- a/src/exchangedb/0002-purse_merges.sql
+++ b/src/exchangedb/0002-purse_merges.sql
@@ -69,3 +69,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('purse_merges'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-purse_requests.sql
b/src/exchangedb/0002-purse_requests.sql
index 135b36df..9f0aef06 100644
--- a/src/exchangedb/0002-purse_requests.sql
+++ b/src/exchangedb/0002-purse_requests.sql
@@ -83,3 +83,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('purse_requests'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql
index 1e420c5f..a3183610 100644
--- a/src/exchangedb/0002-recoup.sql
+++ b/src/exchangedb/0002-recoup.sql
@@ -96,3 +96,17 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('recoup'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-recoup_refresh.sql
b/src/exchangedb/0002-recoup_refresh.sql
index 859d6dd8..9e6361a1 100644
--- a/src/exchangedb/0002-recoup_refresh.sql
+++ b/src/exchangedb/0002-recoup_refresh.sql
@@ -74,3 +74,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('recoup_refresh'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-refresh_commitments.sql
b/src/exchangedb/0002-refresh_commitments.sql
index c5193bee..ce6077c5 100644
--- a/src/exchangedb/0002-refresh_commitments.sql
+++ b/src/exchangedb/0002-refresh_commitments.sql
@@ -65,3 +65,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('refresh_commitments'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql
b/src/exchangedb/0002-refresh_revealed_coins.sql
index 64f7027a..e4b44557 100644
--- a/src/exchangedb/0002-refresh_revealed_coins.sql
+++ b/src/exchangedb/0002-refresh_revealed_coins.sql
@@ -72,3 +72,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('refresh_revealed_coins'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-refresh_transfer_keys.sql
b/src/exchangedb/0002-refresh_transfer_keys.sql
index 425a0b14..54274b26 100644
--- a/src/exchangedb/0002-refresh_transfer_keys.sql
+++ b/src/exchangedb/0002-refresh_transfer_keys.sql
@@ -53,3 +53,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('refresh_transfer_keys'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql
index daaae384..509cc7d0 100644
--- a/src/exchangedb/0002-refunds.sql
+++ b/src/exchangedb/0002-refunds.sql
@@ -1,3 +1,19 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
CREATE OR REPLACE FUNCTION create_table_refunds(
IN shard_suffix VARCHAR DEFAULT NULL
)
diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql
index 7cb561a9..5f3b4604 100644
--- a/src/exchangedb/0002-reserves.sql
+++ b/src/exchangedb/0002-reserves.sql
@@ -72,3 +72,17 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-reserves_close.sql
b/src/exchangedb/0002-reserves_close.sql
index fbb0b86c..d08c961f 100644
--- a/src/exchangedb/0002-reserves_close.sql
+++ b/src/exchangedb/0002-reserves_close.sql
@@ -71,3 +71,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves_close'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-reserves_in.sql
b/src/exchangedb/0002-reserves_in.sql
index 0b3c37e5..2ca0ea71 100644
--- a/src/exchangedb/0002-reserves_in.sql
+++ b/src/exchangedb/0002-reserves_in.sql
@@ -81,3 +81,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves_in'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-reserves_open_deposits.sql
b/src/exchangedb/0002-reserves_open_deposits.sql
index 380034b6..132a123f 100644
--- a/src/exchangedb/0002-reserves_open_deposits.sql
+++ b/src/exchangedb/0002-reserves_open_deposits.sql
@@ -68,3 +68,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves_open_deposits'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-reserves_open_requests.sql
b/src/exchangedb/0002-reserves_open_requests.sql
index a9ef5f86..e56553a5 100644
--- a/src/exchangedb/0002-reserves_open_requests.sql
+++ b/src/exchangedb/0002-reserves_open_requests.sql
@@ -71,3 +71,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves_open_requests'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-reserves_out.sql
b/src/exchangedb/0002-reserves_out.sql
index ea8cabbc..e2aed930 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.sql
@@ -109,3 +109,22 @@ BEGIN
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('reserves_out'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('reserves_out_by_reserve' -- FIXME: do like this?
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-revolving_work_shards.sql
b/src/exchangedb/0002-revolving_work_shards.sql
new file mode 100644
index 00000000..79196631
--- /dev/null
+++ b/src/exchangedb/0002-revolving_work_shards.sql
@@ -0,0 +1,60 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT4 NOT NULL
+ ,end_row INT4 NOT NULL
+ ,active BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
+ );
+COMMENT ON TABLE revolving_work_shards
+ IS 'coordinates work between multiple processes working on the same job with
partitions that need to be repeatedly processed; unlogged because on system
crashes the locks represented by this table will have to be cleared anyway,
typically using "taler-exchange-dbinit -s"';
+COMMENT ON COLUMN revolving_work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN revolving_work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN revolving_work_shards.active
+ IS 'set to TRUE when a worker is active on the shard';
+COMMENT ON COLUMN revolving_work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN revolving_work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+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
+ 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/exchange-0003.sql.in
b/src/exchangedb/0002-signkey_revocations.sql
similarity index 55%
copy from src/exchangedb/exchange-0003.sql.in
copy to src/exchangedb/0002-signkey_revocations.sql
index ee03d440..8e8b0a81 100644
--- a/src/exchangedb/exchange-0003.sql.in
+++ b/src/exchangedb/0002-signkey_revocations.sql
@@ -14,22 +14,24 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
--- Everything in one big transaction
-BEGIN;
-
--- Check patch versioning is in place.
-SELECT _v.register_patch('exchange-0003', NULL, NULL);
-
--------------------- Schema ----------------------------
-
-CREATE SCHEMA exchange;
-COMMENT ON SCHEMA exchange IS 'taler-exchange data';
-
-SET search_path TO exchange;
-
-
-#include "0003-partner_accounts.sql"
-#include "0003-purse_actions.sql"
-#include "0003-purse_deletion.sql"
-
-COMMIT;
+CREATE TABLE IF NOT EXISTS 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-wads_in_entries.sql
b/src/exchangedb/0002-wad_in_entries.sql
similarity index 93%
rename from src/exchangedb/0002-wads_in_entries.sql
rename to src/exchangedb/0002-wad_in_entries.sql
index addd2272..cc8b0a6f 100644
--- a/src/exchangedb/0002-wads_in_entries.sql
+++ b/src/exchangedb/0002-wad_in_entries.sql
@@ -77,3 +77,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('wad_in_entries'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-wad_out_entries.sql
b/src/exchangedb/0002-wad_out_entries.sql
index a475c6fb..3f1076b2 100644
--- a/src/exchangedb/0002-wad_out_entries.sql
+++ b/src/exchangedb/0002-wad_out_entries.sql
@@ -74,3 +74,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('wad_out_entries'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-wads_in.sql b/src/exchangedb/0002-wads_in.sql
index 48036641..ca1466e0 100644
--- a/src/exchangedb/0002-wads_in.sql
+++ b/src/exchangedb/0002-wads_in.sql
@@ -58,3 +58,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('wads_in'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-wads_out.sql b/src/exchangedb/0002-wads_out.sql
index afa56fbb..a44b615e 100644
--- a/src/exchangedb/0002-wads_out.sql
+++ b/src/exchangedb/0002-wads_out.sql
@@ -55,3 +55,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('wads_out'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-wire_accounts.sql
b/src/exchangedb/0002-wire_accounts.sql
new file mode 100644
index 00000000..d23ec730
--- /dev/null
+++ b/src/exchangedb/0002-wire_accounts.sql
@@ -0,0 +1,48 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS wire_accounts
+ (payto_uri VARCHAR PRIMARY KEY
+ ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
+ ,is_active BOOLEAN NOT NULL
+ ,last_change INT8 NOT NULL
+ );
+COMMENT ON TABLE wire_accounts
+ IS 'Table with current and historic bank accounts of the exchange. Entries
never expire as we need to remember the last_change column indefinitely.';
+COMMENT ON COLUMN wire_accounts.payto_uri
+ IS 'payto URI (RFC 8905) with the bank account of the exchange.';
+COMMENT ON COLUMN wire_accounts.master_sig
+ IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS';
+COMMENT ON COLUMN wire_accounts.is_active
+ IS 'true if we are currently supporting the use of this account.';
+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
new file mode 100644
index 00000000..7b53c6f3
--- /dev/null
+++ b/src/exchangedb/0002-wire_fee.sql
@@ -0,0 +1,50 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS wire_fee
+ (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,wire_method VARCHAR NOT NULL
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,wire_fee_val INT8 NOT NULL
+ ,wire_fee_frac INT4 NOT NULL
+ ,closing_fee_val INT8 NOT NULL
+ ,closing_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (wire_method, start_date)
+ );
+COMMENT ON TABLE wire_fee
+ IS 'list of the wire fees of this exchange, by date';
+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
+ 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_out.sql b/src/exchangedb/0002-wire_out.sql
index 9d04cf78..f34998b5 100644
--- a/src/exchangedb/0002-wire_out.sql
+++ b/src/exchangedb/0002-wire_out.sql
@@ -65,3 +65,17 @@ BEGIN
);
END
$$;
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('wire_out'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0002-wire_targets.sql
b/src/exchangedb/0002-wire_targets.sql
index 5e8f1905..08bc468d 100644
--- a/src/exchangedb/0002-wire_targets.sql
+++ b/src/exchangedb/0002-wire_targets.sql
@@ -23,7 +23,7 @@ AS $$
BEGIN
PERFORM create_partitioned_table(
'CREATE TABLE IF NOT EXISTS %I'
- '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',wire_target_h_payto BYTEA PRIMARY KEY CHECK
(LENGTH(wire_target_h_payto)=32)'
',payto_uri VARCHAR NOT NULL'
') %s ;'
@@ -31,19 +31,34 @@ BEGIN
,'PARTITION BY HASH (wire_target_h_payto)'
,shard_suffix
);
+ PERFORM comment_partitioned_table(
+ 'All senders and recipients of money via the exchange'
+ ,'wire_targets'
+ ,shard_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
+ );
+ PERFORM comment_partitioned_column(
+ 'Unsalted hash of payto_uri'
+ ,'wire_target_h_payto'
+ ,shard_suffix
+ );
+
END
$$;
--- We need a separate function for this, as we call create_table only once but
need to add
--- those constraints to each partition which gets created
-CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition(
+
+CREATE OR REPLACE FUNCTION constrain_table_wire_targets(
IN partition_suffix VARCHAR
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
-
EXECUTE FORMAT (
'ALTER TABLE wire_targets_' || partition_suffix || ' '
'ADD CONSTRAINT wire_targets_' || partition_suffix ||
'_wire_target_serial_id_key '
@@ -64,4 +79,9 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'create'
,TRUE
+ ,FALSE),
+ ('wire_targets'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
,FALSE);
diff --git a/src/exchangedb/0002-work_shards.sql
b/src/exchangedb/0002-work_shards.sql
new file mode 100644
index 00000000..d3302205
--- /dev/null
+++ b/src/exchangedb/0002-work_shards.sql
@@ -0,0 +1,60 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--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/>
+--
+
+CREATE TABLE IF NOT EXISTS work_shards
+ (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,last_attempt INT8 NOT NULL
+ ,start_row INT8 NOT NULL
+ ,end_row INT8 NOT NULL
+ ,completed BOOLEAN NOT NULL DEFAULT FALSE
+ ,job_name VARCHAR NOT NULL
+ ,PRIMARY KEY (job_name, start_row)
+ );
+COMMENT ON TABLE work_shards
+ IS 'coordinates work between multiple processes working on the same job';
+COMMENT ON COLUMN work_shards.shard_serial_id
+ IS 'unique serial number identifying the shard';
+COMMENT ON COLUMN work_shards.last_attempt
+ IS 'last time a worker attempted to work on the shard';
+COMMENT ON COLUMN work_shards.completed
+ IS 'set to TRUE once the shard is finished by a worker';
+COMMENT ON COLUMN work_shards.start_row
+ IS 'row at which the shard scope starts, inclusive';
+COMMENT ON COLUMN work_shards.end_row
+ IS 'row at which the shard scope ends, exclusive';
+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
+ 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/0003-purse_actions.sql
b/src/exchangedb/0003-purse_actions.sql
index df8eecea..c77dfb3c 100644
--- a/src/exchangedb/0003-purse_actions.sql
+++ b/src/exchangedb/0003-purse_actions.sql
@@ -58,3 +58,17 @@ CREATE TRIGGER purse_requests_on_insert
COMMENT ON TRIGGER purse_requests_on_insert
ON purse_requests
IS 'Here we install an entry for the purse expiration.';
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('purse_actions'
+ ,'exchange-0002'
+ ,'create'
+ ,FALSE
+ ,FALSE);
diff --git a/src/exchangedb/common-0002.sql b/src/exchangedb/common-0002.sql
deleted file mode 100644
index 649efa11..00000000
--- a/src/exchangedb/common-0002.sql
+++ /dev/null
@@ -1,83 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2014--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/>
---
-
---------------------------------
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ,('auditors', 'exchange-0001', 'create', FALSE, FALSE)
- ,('auditor_denom_sigs', 'exchange-0001', 'create', FALSE, FALSE)
- ,('exchange_sign_keys', 'exchange-0001', 'create', FALSE, FALSE)
- ,('signkey_revocations', 'exchange-0001', 'create', FALSE, FALSE)
- ,('extensions', 'exchange-0001', 'create', FALSE, FALSE)
- ,('wire_fee', 'exchange-0001', 'create', FALSE, FALSE)
- ,('global_fee', 'exchange-0001', 'create', FALSE, FALSE)
- ,('wire_accounts', 'exchange-0001', 'create', FALSE, FALSE)
- ,('work_shards', 'exchange-0001', 'create', FALSE, FALSE)
- ,('revolving_work_shards', 'exchange-0001', 'create', FALSE, FALSE)
- ,('partners', 'exchange-0001', 'create', FALSE, FALSE)
- ,('partner_accounts', 'exchange-0001', 'create', FALSE, FALSE)
- ,('purse_actions', 'exchange-0001', 'create', FALSE, FALSE)
- ,('policy_fulfillments', 'exchange-0001', 'create', FALSE, FALSE) -- bad!
- ,('policy_details', 'exchange-0001', 'create', FALSE, FALSE) -- bad!
- ,('wire_targets''exchange-0001', 'create', TRUE, FALSE)
- ,('legitimization_processes', 'exchange-0001', 'create', TRUE, FALSE)
- ,('legitimization_requirements', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_in', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_close', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_open_requests', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_open_deposits', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_out', 'exchange-0001', 'create', TRUE, FALSE)
- ,('reserves_out_by_reserve', 'exchange-0001', 'create', TRUE, FALSE)
- ,('known_coins', 'exchange-0001', 'create', TRUE, FALSE)
- ,('refresh_commitments', 'exchange-0001', 'create', TRUE, FALSE)
- ,('refresh_revealed_coins', 'exchange-0001', 'create', TRUE, FALSE)
- ,('refresh_transfer_keys', 'exchange-0001', 'create', TRUE, FALSE)
- ,('refunds', 'exchange-0001', 'create', TRUE, FALSE)
- ,('deposits', 'exchange-0001', 'create', TRUE, FALSE)
- ,('deposits_by_ready', 'exchange-0001', 'create', TRUE, TRUE)
- ,('deposits_for_matching', 'exchange-0001', 'create', TRUE, TRUE)
- ,('wire_out', 'exchange-0001', 'create', TRUE, FALSE)
- ,('aggregation_transient', 'exchange-0001', 'create', TRUE, FALSE)
- ,('aggregation_tracking', 'exchange-0001', 'create', TRUE, FALSE)
- ,('recoup', 'exchange-0001', 'create', TRUE, FALSE)
- ,('recoup_by_reserve', 'exchange-0001', 'create', TRUE, FALSE)
- ,('recoup_refresh', 'exchange-0001', 'create', TRUE, FALSE)
- ,('prewire', 'exchange-0001', 'create', TRUE, FALSE)
- ,('cs_nonce_locks', 'exchange-0001', 'create', TRUE, FALSE)
- ,('purse_requests', 'exchange-0001', 'create', TRUE, FALSE)
- ,('purse_decision', 'exchange-0001', 'create', TRUE, FALSE)
- ,('purse_merges', 'exchange-0001', 'create', TRUE, FALSE)
- ,('account_merges', 'exchange-0001', 'create', TRUE, FALSE)
- ,('contracts', 'exchange-0001', 'create', TRUE, FALSE)
- ,('history_requests', 'exchange-0001', 'create', TRUE, FALSE)
- ,('close_requests', 'exchange-0001', 'create', TRUE, FALSE)
- ,('purse_deposists', 'exchange-0001', 'create', TRUE, FALSE)
- ,('wads_out', 'exchange-0001', 'create', TRUE, FALSE)
- ,('wads_out_entries', 'exchange-0001', 'create', TRUE, FALSE)
- ,('wads_in', 'exchange-0001', 'create', TRUE, FALSE)
- ,('wads_in_entries', 'exchange-0001', 'create', TRUE, FALSE)
- ON CONFLICT DO NOTHING;
-
-
-
--------------------- Tables ----------------------------
diff --git a/src/exchangedb/exchange-0001-part.sql
b/src/exchangedb/exchange-0001-part.sql
index 7855c996..8a103608 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -14,28 +14,6 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-
-
-
--- ------------------------------ wire_targets
----------------------------------------
-
-SELECT create_table_wire_targets();
-
-COMMENT ON TABLE wire_targets
- IS 'All senders and recipients of money via the exchange';
-COMMENT ON COLUMN wire_targets.payto_uri
- IS 'Can be a regular bank account, or also be a URI identifying a
reserve-account (for P2P payments)';
-COMMENT ON COLUMN wire_targets.wire_target_h_payto
- IS 'Unsalted hash of payto_uri';
-
-
-CREATE TABLE IF NOT EXISTS wire_targets_default
- PARTITION OF wire_targets
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-SELECT add_constraints_to_wire_targets_partition('default');
-
-
-- ------------------------------ legitimization_processes
----------------------------------------
SELECT create_table_legitimization_processes();
@@ -55,10 +33,6 @@ COMMENT ON COLUMN legitimization_processes.provider_user_id
COMMENT ON COLUMN legitimization_processes.provider_legitimization_id
IS 'Identifier for the specific legitimization process at the provider. NULL
if legitimization was not started.';
-CREATE TABLE IF NOT EXISTS legitimization_processes_default
- PARTITION OF legitimization_processes
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_legitimization_processes_partition('default');
@@ -75,10 +49,6 @@ COMMENT ON COLUMN legitimization_requirements.h_payto
COMMENT ON COLUMN legitimization_requirements.required_checks
IS 'space-separated list of required checks';
-CREATE TABLE IF NOT EXISTS legitimization_requirements_default
- PARTITION OF legitimization_requirements
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_legitimization_requirements_partition('default');
@@ -102,10 +72,6 @@ COMMENT ON COLUMN reserves.expiration_date
COMMENT ON COLUMN reserves.gc_date
IS 'Used to forget all information about a reserve during garbage
collection';
-CREATE TABLE IF NOT EXISTS reserves_default
- PARTITION OF reserves
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-- ------------------------------ reserves_in
----------------------------------------
SELECT create_table_reserves_in();
@@ -119,9 +85,6 @@ COMMENT ON COLUMN reserves_in.reserve_pub
COMMENT ON COLUMN reserves_in.credit_val
IS 'Amount that was transferred into the reserve';
-CREATE TABLE IF NOT EXISTS reserves_in_default
- PARTITION OF reserves_in
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
SELECT add_constraints_to_reserves_in_partition('default');
@@ -134,9 +97,6 @@ COMMENT ON TABLE reserves_close
COMMENT ON COLUMN reserves_close.wire_target_h_payto
IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
-CREATE TABLE IF NOT EXISTS reserves_close_default
- PARTITION OF reserves_close
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
SELECT add_constraints_to_reserves_close_partition('default');
@@ -154,10 +114,6 @@ COMMENT ON TABLE reserves_open_requests
COMMENT ON COLUMN reserves_open_requests.reserve_payment_val
IS 'Funding to pay for the request from the reserve balance itself.';
-CREATE TABLE IF NOT EXISTS reserves_open_requests_default
- PARTITION OF reserves_open_requests
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_reserves_open_request_partition('default');
@@ -170,9 +126,6 @@ COMMENT ON TABLE reserves_open_deposits
COMMENT ON COLUMN reserves_open_deposits.reserve_pub
IS 'Identifies the specific reserve being paid for (possibly together with
reserve_sig).';
-CREATE TABLE IF NOT EXISTS reserves_open_deposits_default
- PARTITION OF reserves_open_deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
SELECT add_constraints_to_reserves_open_deposits_partition('default');
@@ -188,10 +141,6 @@ COMMENT ON COLUMN reserves_out.h_blind_ev
COMMENT ON COLUMN reserves_out.denominations_serial
IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
-CREATE TABLE IF NOT EXISTS reserves_out_default
- PARTITION OF reserves_out
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_reserves_out_partition('default');
@@ -200,10 +149,6 @@ SELECT create_table_reserves_out_by_reserve();
COMMENT ON TABLE reserves_out_by_reserve
IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
- PARTITION OF reserves_out_by_reserve
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -243,97 +188,6 @@ CREATE TRIGGER reserves_out_on_delete
FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
--- ------------------------------ auditors
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS 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
- ,auditor_url VARCHAR NOT NULL
- ,is_active BOOLEAN NOT NULL
- ,last_change INT8 NOT NULL
- );
-COMMENT ON TABLE auditors
- IS 'Table with auditors the exchange uses or has used in the past. Entries
never expire as we need to remember the last_change column indefinitely.';
-COMMENT ON COLUMN auditors.auditor_pub
- IS 'Public key of the auditor.';
-COMMENT ON COLUMN auditors.auditor_url
- IS 'The base URL of the auditor.';
-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.';
-
-
--- ------------------------------ auditor_denom_sigs
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS 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
- ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64)
- ,PRIMARY KEY (denominations_serial, auditor_uuid)
- );
-COMMENT ON TABLE auditor_denom_sigs
- IS 'Table with auditor signatures on exchange denomination keys.';
-COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid
- IS 'Identifies the auditor.';
-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.';
-
-
--- ------------------------------ exchange_sign_keys
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS 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)
- ,valid_from INT8 NOT NULL
- ,expire_sign INT8 NOT NULL
- ,expire_legal INT8 NOT NULL
- );
-COMMENT ON TABLE exchange_sign_keys
- IS 'Table with master public key signatures on exchange online signing
keys.';
-COMMENT ON COLUMN exchange_sign_keys.exchange_pub
- IS 'Public online signing key of the exchange.';
-COMMENT ON COLUMN exchange_sign_keys.master_sig
- IS 'Signature affirming the validity of the signing key of purpose
TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
-COMMENT ON COLUMN exchange_sign_keys.valid_from
- IS 'Time when this online signing key will first be used to sign messages.';
-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.';
-
-
--- ------------------------------ signkey_revocations
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS 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';
-
-
--- ------------------------------ extensions
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS extensions
- (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,name VARCHAR NOT NULL UNIQUE
- ,manifest BYTEA
- );
-COMMENT ON TABLE extensions
- IS 'Configurations of the activated extensions';
-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.';
-
-
-- ------------------------------ known_coins
----------------------------------------
SELECT create_table_known_coins();
@@ -351,10 +205,6 @@ COMMENT ON COLUMN known_coins.age_commitment_hash
COMMENT ON COLUMN known_coins.denom_sig
IS 'This is the signature of the exchange that affirms that the coin is a
valid coin. The specific signature type depends on denom_type of the
denomination.';
-CREATE TABLE IF NOT EXISTS known_coins_default
- PARTITION OF known_coins
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_known_coins_partition('default');
@@ -371,10 +221,6 @@ COMMENT ON COLUMN refresh_commitments.rc
COMMENT ON COLUMN refresh_commitments.old_coin_pub
IS 'Coin being melted in the refresh process.';
-CREATE TABLE IF NOT EXISTS refresh_commitments_default
- PARTITION OF refresh_commitments
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_refresh_commitments_partition('default');
@@ -399,10 +245,6 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
COMMENT ON COLUMN refresh_revealed_coins.ev_sig
IS 'exchange signature over the envelope';
-CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
- PARTITION OF refresh_revealed_coins
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_refresh_revealed_coins_partition('default');
@@ -421,77 +263,9 @@ COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been
revealed, with the gamma entry being skipped';
-CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
- PARTITION OF refresh_transfer_keys
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_refresh_transfer_keys_partition('default');
--- ------------------------------ policy_fulfillments
-------------------------------------
-
-CREATE TABLE IF NOT EXISTS policy_fulfillments
- (fulfillment_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE PRIMARY KEY
- ,fulfillment_timestamp INT8 NOT NULL
- ,fulfillment_proof VARCHAR
- ,h_fulfillment_proof BYTEA NOT NULL CHECK(LENGTH(h_fulfillment_proof) = 64)
UNIQUE
- ,policy_hash_codes BYTEA NOT NULL CHECK(0 = MOD(LENGTH(policy_hash_codes),
16))
- );
-COMMENT ON TABLE policy_fulfillments
- IS 'Proofs of fulfillment of policies that were set in deposits';
-COMMENT ON COLUMN policy_fulfillments.fulfillment_timestamp
- IS 'Timestamp of the arrival of a proof of fulfillment';
-COMMENT ON COLUMN policy_fulfillments.fulfillment_proof
- IS 'JSON object with a proof of the fulfillment of a policy. Supported
details depend on the policy extensions supported by the exchange.';
-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';
-
--- ------------------------------ policy_details
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS policy_details
- (policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
- ,policy_hash_code BYTEA PRIMARY KEY CHECK(LENGTH(policy_hash_code)=16)
- ,policy_json VARCHAR
- ,deadline INT8 NOT NULL
- ,commitment_val INT8 NOT NULL
- ,commitment_frac INT4 NOT NULL
- ,accumulated_total_val INT8 NOT NULL
- ,accumulated_total_frac INT4 NOT NULL
- ,fee_val INT8 NOT NULL
- ,fee_frac INT4 NOT NULL
- ,transferable_val INT8 NOT NULL
- ,transferable_frac INT8 NOT NULL
- ,fulfillment_state smallint NOT NULL CHECK(fulfillment_state between 0 and 5)
- ,fulfillment_id BIGINT NULL REFERENCES policy_fulfillments (fulfillment_id)
ON DELETE CASCADE
- );
-COMMENT ON TABLE policy_details
- IS 'Policies that were provided with deposits via policy extensions.';
-COMMENT ON COLUMN policy_details.policy_hash_code
- IS 'ID (GNUNET_HashCode) that identifies a policy. Will be calculated by
the policy extension based on the content';
-COMMENT ON COLUMN policy_details.policy_json
- IS 'JSON object with options set that the exchange needs to consider when
executing a deposit. Supported details depend on the policy extensions
supported by the exchange.';
-COMMENT ON COLUMN policy_details.deadline
- IS 'Deadline until the policy must be marked as fulfilled (maybe "forever")';
-COMMENT ON COLUMN policy_details.commitment_val
- IS 'The amount that this policy commits to. Invariant: commitment >= fee';
-COMMENT ON COLUMN policy_details.accumulated_total_val
- IS 'The sum of all contributions of all deposit that reference this policy.
Invariant: The fulfilment_state must be Insufficient as long as
accumulated_total < commitment';
-COMMENT ON COLUMN policy_details.fee_val
- IS 'The fee for this policy, due when the policy is fulfilled or timed out';
-COMMENT ON COLUMN policy_details.transferable_val
- IS 'The amount that on fulfillment or timeout will be transferred to the
payto-URI''s of the corresponding deposit''s. The policy fees must have been
already deducted from it. Invariant: fee+transferable <= accumulated_total.
The remaining amount (accumulated_total - fee - transferable) can be refreshed
by the owner of the coins when the state is Timeout or Success.';
-COMMENT ON COLUMN policy_details.fulfillment_state
- IS 'State of the fulfillment:
- - 0 (Failure)
- - 1 (Insufficient)
- - 2 (Ready)
- - 4 (Success)
- - 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.';
-
-- ------------------------------ deposits
----------------------------------------
SELECT create_table_deposits();
@@ -513,10 +287,6 @@ COMMENT ON COLUMN deposits.policy_blocked
COMMENT ON COLUMN deposits.policy_details_serial_id
IS 'References policy extensions table, NULL if extensions are not used';
-CREATE TABLE IF NOT EXISTS deposits_default
- PARTITION OF deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_deposits_partition('default');
@@ -525,21 +295,12 @@ SELECT create_table_deposits_by_ready();
COMMENT ON TABLE deposits_by_ready
IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER
below';
-CREATE TABLE IF NOT EXISTS deposits_by_ready_default
- PARTITION OF deposits_by_ready
- DEFAULT;
-
SELECT create_table_deposits_for_matching();
COMMENT ON TABLE deposits_for_matching
IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via
TRIGGER below';
-CREATE TABLE IF NOT EXISTS deposits_for_matching_default
- PARTITION OF deposits_for_matching
- DEFAULT;
-
-
CREATE OR REPLACE FUNCTION deposits_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -683,10 +444,6 @@ COMMENT ON COLUMN refunds.deposit_serial_id
COMMENT ON COLUMN refunds.rtransaction_id
IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
-CREATE TABLE IF NOT EXISTS refunds_default
- PARTITION OF refunds
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_refunds_partition('default');
@@ -701,10 +458,6 @@ COMMENT ON COLUMN wire_out.exchange_account_section
COMMENT ON COLUMN wire_out.wire_target_h_payto
IS 'Identifies the credited bank account and KYC status';
-CREATE TABLE IF NOT EXISTS wire_out_default
- PARTITION OF wire_out
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_wire_out_partition('default');
CREATE OR REPLACE FUNCTION wire_out_delete_trigger()
@@ -737,11 +490,6 @@ COMMENT ON COLUMN aggregation_transient.amount_val
COMMENT ON COLUMN aggregation_transient.wtid_raw
IS 'identifier of the wire transfer';
-CREATE TABLE IF NOT EXISTS aggregation_transient_default
- PARTITION OF aggregation_transient
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-
-- ------------------------------ aggregation_tracking
----------------------------------------
SELECT create_table_aggregation_tracking();
@@ -751,65 +499,9 @@ COMMENT ON TABLE aggregation_tracking
COMMENT ON COLUMN aggregation_tracking.wtid_raw
IS 'identifier of the wire transfer';
-CREATE TABLE IF NOT EXISTS aggregation_tracking_default
- PARTITION OF aggregation_tracking
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_aggregation_tracking_partition('default');
--- ------------------------------ wire_fee
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS wire_fee
- (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,wire_method VARCHAR NOT NULL
- ,start_date INT8 NOT NULL
- ,end_date INT8 NOT NULL
- ,wire_fee_val INT8 NOT NULL
- ,wire_fee_frac INT4 NOT NULL
- ,closing_fee_val INT8 NOT NULL
- ,closing_fee_frac INT4 NOT NULL
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,PRIMARY KEY (wire_method, start_date)
- );
-COMMENT ON TABLE wire_fee
- IS 'list of the wire fees of this exchange, by date';
-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
- ON wire_fee
- (end_date);
-
-
--- ------------------------------ global_fee
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS global_fee
- (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,start_date INT8 NOT NULL
- ,end_date INT8 NOT NULL
- ,history_fee_val INT8 NOT NULL
- ,history_fee_frac INT4 NOT NULL
- ,account_fee_val INT8 NOT NULL
- ,account_fee_frac INT4 NOT NULL
- ,purse_fee_val INT8 NOT NULL
- ,purse_fee_frac INT4 NOT NULL
- ,purse_timeout INT8 NOT NULL
- ,history_expiration INT8 NOT NULL
- ,purse_account_limit INT4 NOT NULL
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,PRIMARY KEY (start_date)
- );
-COMMENT ON TABLE global_fee
- IS 'list of the global fees of this exchange, by date';
-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
- ON global_fee
- (end_date);
-
-
-- ------------------------------ recoup
----------------------------------------
SELECT create_table_recoup();
@@ -825,10 +517,6 @@ COMMENT ON COLUMN recoup.coin_sig
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.';
-CREATE TABLE IF NOT EXISTS recoup_default
- PARTITION OF recoup
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_recoup_partition('default');
@@ -837,10 +525,6 @@ 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 TABLE IF NOT EXISTS recoup_by_reserve_default
- PARTITION OF recoup_by_reserve
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
CREATE OR REPLACE FUNCTION recoup_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -896,10 +580,6 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
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.';
-CREATE TABLE IF NOT EXISTS recoup_refresh_default
- PARTITION OF recoup_refresh
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_recoup_refresh_partition('default');
@@ -916,33 +596,6 @@ COMMENT ON COLUMN prewire.finished
COMMENT ON COLUMN prewire.buf
IS 'serialized data to send to the bank to execute the wire transfer';
-CREATE TABLE IF NOT EXISTS prewire_default
- PARTITION OF prewire
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-
--- ------------------------------ wire_accounts
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS wire_accounts
- (payto_uri VARCHAR PRIMARY KEY
- ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
- ,is_active BOOLEAN NOT NULL
- ,last_change INT8 NOT NULL
- );
-COMMENT ON TABLE wire_accounts
- IS 'Table with current and historic bank accounts of the exchange. Entries
never expire as we need to remember the last_change column indefinitely.';
-COMMENT ON COLUMN wire_accounts.payto_uri
- IS 'payto URI (RFC 8905) with the bank account of the exchange.';
-COMMENT ON COLUMN wire_accounts.master_sig
- IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS';
-COMMENT ON COLUMN wire_accounts.is_active
- IS 'true if we are currently supporting the use of this account.';
-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
-
-
-- ------------------------------ cs_nonce_locks
----------------------------------------
SELECT create_table_cs_nonce_locks();
@@ -956,120 +609,9 @@ COMMENT ON COLUMN cs_nonce_locks.op_hash
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';
-CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
- PARTITION OF cs_nonce_locks
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_cs_nonce_locks_partition('default');
--- ------------------------------ work_shards
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS work_shards
- (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,last_attempt INT8 NOT NULL
- ,start_row INT8 NOT NULL
- ,end_row INT8 NOT NULL
- ,completed BOOLEAN NOT NULL DEFAULT FALSE
- ,job_name VARCHAR NOT NULL
- ,PRIMARY KEY (job_name, start_row)
- );
-COMMENT ON TABLE work_shards
- IS 'coordinates work between multiple processes working on the same job';
-COMMENT ON COLUMN work_shards.shard_serial_id
- IS 'unique serial number identifying the shard';
-COMMENT ON COLUMN work_shards.last_attempt
- IS 'last time a worker attempted to work on the shard';
-COMMENT ON COLUMN work_shards.completed
- IS 'set to TRUE once the shard is finished by a worker';
-COMMENT ON COLUMN work_shards.start_row
- IS 'row at which the shard scope starts, inclusive';
-COMMENT ON COLUMN work_shards.end_row
- IS 'row at which the shard scope ends, exclusive';
-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
- ON work_shards
- (job_name
- ,completed
- ,last_attempt ASC
- );
-
-
--- ------------------------------ revolving_work_shards
----------------------------------------
-
-CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
- (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
- ,last_attempt INT8 NOT NULL
- ,start_row INT4 NOT NULL
- ,end_row INT4 NOT NULL
- ,active BOOLEAN NOT NULL DEFAULT FALSE
- ,job_name VARCHAR NOT NULL
- ,PRIMARY KEY (job_name, start_row)
- );
-COMMENT ON TABLE revolving_work_shards
- IS 'coordinates work between multiple processes working on the same job with
partitions that need to be repeatedly processed; unlogged because on system
crashes the locks represented by this table will have to be cleared anyway,
typically using "taler-exchange-dbinit -s"';
-COMMENT ON COLUMN revolving_work_shards.shard_serial_id
- IS 'unique serial number identifying the shard';
-COMMENT ON COLUMN revolving_work_shards.last_attempt
- IS 'last time a worker attempted to work on the shard';
-COMMENT ON COLUMN revolving_work_shards.active
- IS 'set to TRUE when a worker is active on the shard';
-COMMENT ON COLUMN revolving_work_shards.start_row
- IS 'row at which the shard scope starts, inclusive';
-COMMENT ON COLUMN revolving_work_shards.end_row
- IS 'row at which the shard scope ends, exclusive';
-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
- ON revolving_work_shards
- (job_name
- ,active
- ,last_attempt
- );
-
---------------------------------------------------------------------------
--- Tables for P2P payments
---------------------------------------------------------------------------
-
--- ------------------------------ partners
----------------------------------------
-
-CREATE TABLE IF NOT EXISTS 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
- ,end_date INT8 NOT NULL
- ,next_wad INT8 NOT NULL DEFAULT (0)
- ,wad_frequency INT8 NOT NULL
- ,wad_fee_val INT8 NOT NULL
- ,wad_fee_frac INT4 NOT NULL
- ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
- ,partner_base_url TEXT NOT NULL
- );
-COMMENT ON TABLE partners
- IS 'exchanges we do wad transfers to';
-COMMENT ON COLUMN partners.partner_master_pub
- IS 'offline master public key of the partner';
-COMMENT ON COLUMN partners.start_date
- IS 'starting date of the partnership';
-COMMENT ON COLUMN partners.end_date
- IS 'end date of the partnership';
-COMMENT ON COLUMN partners.next_wad
- IS 'at what time should we do the next wad transfer to this partner
(frequently updated); set to forever after the end_date';
-COMMENT ON COLUMN partners.wad_frequency
- IS 'how often do we promise to do wad transfers';
-COMMENT ON COLUMN partners.wad_fee_val
- IS 'how high is the fee for a wallet to be added to a wad to this partner';
-COMMENT ON COLUMN partners.partner_base_url
- IS 'base URL of the REST API for this partner';
-COMMENT ON COLUMN partners.master_sig
- IS 'signature of our master public key affirming the partnership, of purpose
TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
-
-CREATE INDEX IF NOT EXISTS partner_by_wad_time
- ON partners (next_wad ASC);
-
-- ------------------------------ purse_requests
----------------------------------------
SELECT create_table_purse_requests();
@@ -1097,10 +639,6 @@ COMMENT ON COLUMN purse_requests.balance_val
COMMENT ON COLUMN purse_requests.purse_sig
IS 'Signature of the purse affirming the purse parameters, of type
TALER_SIGNATURE_PURSE_REQUEST';
-CREATE TABLE IF NOT EXISTS purse_requests_default
- PARTITION OF purse_requests
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_purse_requests_partition('default');
@@ -1113,10 +651,6 @@ COMMENT ON TABLE purse_decision
COMMENT ON COLUMN purse_decision.purse_pub
IS 'Public key of the purse';
-CREATE TABLE IF NOT EXISTS purse_decision_default
- PARTITION OF purse_decision
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_purse_decision_partition('default');
@@ -1137,10 +671,6 @@ COMMENT ON COLUMN purse_merges.merge_sig
COMMENT ON COLUMN purse_merges.merge_timestamp
IS 'when was the merge message signed';
-CREATE TABLE IF NOT EXISTS purse_merges_default
- PARTITION OF purse_merges
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_purse_merges_partition('default');
@@ -1157,10 +687,6 @@ COMMENT ON COLUMN account_merges.purse_pub
COMMENT ON COLUMN account_merges.reserve_sig
IS 'signature by the reserve private key affirming the merge, of type
TALER_SIGNATURE_WALLET_ACCOUNT_MERGE';
-CREATE TABLE IF NOT EXISTS account_merges_default
- PARTITION OF account_merges
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_account_merges_partition('default');
@@ -1179,10 +705,6 @@ COMMENT ON COLUMN contracts.pub_ckey
COMMENT ON COLUMN contracts.e_contract
IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after
decryption)';
-CREATE TABLE IF NOT EXISTS contracts_default
- PARTITION OF contracts
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_contracts_partition('default');
@@ -1199,10 +721,6 @@ COMMENT ON COLUMN history_requests.reserve_sig
COMMENT ON COLUMN history_requests.history_fee_val
IS 'History fee approved by the signature';
-CREATE TABLE IF NOT EXISTS history_requests_default
- PARTITION OF history_requests
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-- ------------------------------ close_requests
----------------------------------------
SELECT create_table_close_requests();
@@ -1218,10 +736,6 @@ COMMENT ON COLUMN close_requests.close_val
COMMENT ON COLUMN close_requests.payto_uri
IS 'Identifies the credited bank account. Optional.';
-CREATE TABLE IF NOT EXISTS close_requests_default
- PARTITION OF close_requests
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_close_requests_partition('default');
-- ------------------------------ purse_deposits
----------------------------------------
@@ -1241,10 +755,6 @@ COMMENT ON COLUMN purse_deposits.amount_with_fee_val
COMMENT ON COLUMN purse_deposits.coin_sig
IS 'Signature of the coin affirming the deposit into the purse, of type
TALER_SIGNATURE_PURSE_DEPOSIT';
-CREATE TABLE IF NOT EXISTS purse_deposits_default
- PARTITION OF purse_deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_purse_deposits_partition('default');
@@ -1263,10 +773,6 @@ COMMENT ON COLUMN wads_out.amount_val
COMMENT ON COLUMN wads_out.execution_time
IS 'Time when the wire transfer was scheduled';
-CREATE TABLE IF NOT EXISTS wads_out_default
- PARTITION OF wads_out
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_wads_out_partition('default');
@@ -1299,10 +805,6 @@ COMMENT ON COLUMN wad_out_entries.reserve_sig
COMMENT ON COLUMN wad_out_entries.purse_sig
IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
-CREATE TABLE IF NOT EXISTS wad_out_entries_default
- PARTITION OF wad_out_entries
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_wad_out_entries_partition('default');
-- ------------------------------ wads_in
----------------------------------------
@@ -1320,10 +822,6 @@ COMMENT ON COLUMN wads_in.amount_val
COMMENT ON COLUMN wads_in.arrival_time
IS 'Time when the wad was received';
-CREATE TABLE IF NOT EXISTS wads_in_default
- PARTITION OF wads_in
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_wads_in_partition('default');
@@ -1356,13 +854,4 @@ COMMENT ON COLUMN wad_in_entries.reserve_sig
COMMENT ON COLUMN wad_in_entries.purse_sig
IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
-CREATE TABLE IF NOT EXISTS wad_in_entries_default
- PARTITION OF wad_in_entries
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
SELECT add_constraints_to_wad_in_entries_partition('default');
-
-
--- ------------------------------ partner_accounts
----------------------------------------
-
------------------------ router helper table (not synchronzied)
------------------------
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 6998320c..f7bf15f6 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -23,7 +23,7 @@ SET search_path TO exchange;
-- General procedures for DB setup
---------------------------------------------------------------------------
-CREATE TABLE IF NOT EXISTS exchange_tables
+CREATE TABLE exchange_tables
(table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
,name VARCHAR NOT NULL
,version VARCHAR NOT NULL
@@ -38,7 +38,7 @@ COMMENT ON COLUMN exchange_tables.name
COMMENT ON COLUMN exchange_tables.version
IS 'Version of the DB in which the given action happened';
COMMENT ON COLUMN exchange_tables.action
- IS 'Action to take on the table (e.g. create, alter, constrain, unconstrain,
or drop)';
+ IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or
drop). Create, alter and drop are done for master and partitions; constrain is
only for partitions or for master if there are no partitions; foreign only on
master if there are no partitions.';
COMMENT ON COLUMN exchange_tables.partitioned
IS 'TRUE if the table is partitioned';
COMMENT ON COLUMN exchange_tables.by_range
@@ -47,8 +47,7 @@ COMMENT ON COLUMN exchange_tables.finished
IS 'TRUE if the respective migration has been run';
-
-CREATE OR REPLACE FUNCTION create_partitioned_table(
+CREATE FUNCTION create_partitioned_table(
IN table_definition VARCHAR
,IN table_name VARCHAR
,IN main_table_partition_str VARCHAR -- Used only when it is the main table
- we do not partition shard tables
@@ -58,28 +57,83 @@ RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
-
IF shard_suffix IS NOT NULL THEN
table_name=table_name || '_' || shard_suffix;
main_table_partition_str = '';
END IF;
-
EXECUTE FORMAT(
table_definition,
table_name,
main_table_partition_str
);
+END
+$$;
+COMMENT ON FUNCTION create_partitioned_table
+ IS 'Generic function to create a table that is partitioned.';
+
+
+CREATE FUNCTION comment_partitioned_table(
+ IN table_comment VARCHAR
+ ,IN table_name VARCHAR
+ ,IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF shard_suffix IS NOT NULL THEN
+ table_name=table_name || '_' || shard_suffix;
+ main_table_partition_str = '';
+ END IF;
+ EXECUTE FORMAT(
+ COMMENT ON TABLE %s IS '%s'
+ ,table_name
+ ,table_comment
+ );
END
$$;
+COMMENT ON FUNCTION create_partitioned_table
+ IS 'Generic function to create a comment on table that is partitioned.';
+CREATE FUNCTION comment_partitioned_column(
+ IN table_comment VARCHAR
+ ,IN column_name VARCHAR
+ ,IN table_name VARCHAR
+ ,IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF shard_suffix IS NOT NULL THEN
+ table_name=table_name || '_' || shard_suffix;
+ main_table_partition_str = '';
+ END IF;
+ EXECUTE FORMAT(
+ COMMENT ON COLUMN %s.%s IS '%s'
+ ,table_name
+ ,column_name
+ ,table_comment
+ );
+END
+$$;
+COMMENT ON FUNCTION create_partitioned_table
+ IS 'Generic function to create a comment on column of a table that is
partitioned.';
-CREATE OR REPLACE FUNCTION create_tables(
+
+
+
+CREATE FUNCTION create_tables(
num_partitions INTEGER
- ,shard_domain VARCHAR
+-- FIXME: not implemented like this, but likely good:
+-- NULL: no partitions, add foreign constraints
+-- 0: no partitions, no foreign constraints
+-- 1: only 1 default partition
+-- > 1: normal partitions
)
RETURNS VOID
LANGUAGE plpgsql
@@ -92,8 +146,36 @@ DECLARE
,by_range
FROM exchange_tables
WHERE NOT finished
+ AND partitioned
+ AND (action='create'
+ OR action='alter'
+ OR action='drop')
+ ORDER BY table_serial_id ASC;
+DECLARE
+ ta CURSOR FOR
+ SELECT table_serial_id
+ ,name
+ ,action
+ ,by_range
+ FROM exchange_tables
+ WHERE NOT finished
+ AND partitioned
+ AND action='constrain'
+ ORDER BY table_serial_id ASC;
+DECLARE
+ tf CURSOR FOR
+ SELECT table_serial_id
+ ,name
+ ,action
+ ,by_range
+ FROM exchange_tables
+ WHERE NOT finished
+ AND partitioned
+ AND action='foreign'
ORDER BY table_serial_id ASC;
BEGIN
+
+ -- run create/alter/drop actions
FOR rec IN tc
LOOP
-- First create the master table, either
@@ -101,6 +183,7 @@ BEGIN
-- master and the 'default' partition
IF IS NULL num_partitions
THEN
+ -- No partitions at all.
EXECUTE FORMAT(
'PERFORM %s_table_%s (%s)'::text
,rec.action
@@ -108,676 +191,113 @@ BEGIN
,NULL
);
ELSE
+ -- One default partition only.
EXECUTE FORMAT(
'PERFORM %s_table_%s (%s)'::text
,rec.action
,rec.name
,0
);
- END IF
- IF NOT NULL shard_domain
+ IF NOT IS NULL num_partitions
THEN
- -- FIXME: attach shards!
- -- FIXME: how will migration work with shards!?
- FOR i IN 1..num_partitions LOOP
+ IF rec.by_range
+ THEN
+ -- range partitions (only create default)
+ -- Create default partition.
EXECUTE FORMAT(
- 'PERFORM %s_XXX_%s (%s)'::text
- ,rec.action
+ 'CREATE TABLE %s_default PARTITION OF %s DEFAULT'
,rec.name
- ,i::varchar
- );
- END LOOP;
- ELSE
- FOR i IN 1..num_partitions LOOP
- EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'::text
- ,rec.action
,rec.name
- ,i::varchar
);
- END LOOP;
- END IF
+ ELSE
+ -- hash partitions
+ IF 0=num_partitions
+ THEN
+ -- Create default partition.
+ EXECUTE FORMAT(
+ 'CREATE TABLE IF NOT EXISTS %s_default PARTITION OF %s FOR VALUES
WITH (MODULUS 1, REMAINDER 0)'
+ ,rec.name
+ ,rec.name
+ );
+ END IF
+ FOR i IN 1..num_partitions LOOP
+ -- Create num_partitions
+ EXECUTE FORMAT(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ ' PARTITION OF %I'
+ ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
+ ,rec.name || '_' || i
+ ,rec.name
+ ,num_partitions
+ ,i-1
+ );
+ END LOOP;
+ END IF;
+ END IF;
+ UPDATE exchange_tables
+ SET finished=TRUE
+ WHERE table_serial_id=rec.table_serial_id;
+ END LOOP; -- create/alter/drop actions
- IF 0 < num_partitions
+ -- Run constrain actions
+ FOR rec IN ta
+ LOOP
+ IF IS NULL num_partitions
THEN
- -- FIXME: detach default partition!
+ -- Constrain master
+ EXECUTE FORMAT(
+ 'PERFORM %s_table_%s (%s)'::text
+ ,rec.action
+ ,rec.name
+ ,NULL
+ );
END IF
+ IF 0=num_partitions
+ THEN
+ -- constrain default partition
+ EXECUTE FORMAT(
+ 'PERFORM %s_table_%s (%s)'::text
+ ,rec.action
+ ,rec.name
+ ,0
+ );
+ END IF
+ FOR i IN 1..num_partitions LOOP
+ -- constrain each partition
+ EXECUTE FORMAT(
+ 'PERFORM %s_table_%s (%s)'::text
+ ,rec.action
+ ,rec.name
+ ,i::varchar
+ );
+ END LOOP;
UPDATE exchange_tables
SET finished=TRUE
WHERE table_serial_id=rec.table_serial_id;
END LOOP;
-END
-$$;
-
-COMMENT ON FUNCTION create_tables
- IS 'Creates all tables for the given number of partitions that need
creating.';
-
-
--- This is run last by dbinit, if partitions exist
--- or if 'force_create_partitions' is set (otherwise,
--- we are not expected to create partitions if there
--- is only 1).
-CREATE OR REPLACE FUNCTION create_partitions(
- IN part_idx INTEGER
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- tc CURSOR FOR
- SELECT name
- ,action
- ,partitioned
- ,by_range
- FROM exchange_tables
- WHERE version=in_version
- AND partitioned
- ORDER BY table_seria_id ASC;
-BEGIN
- FOR rec IN tc
+ -- run foreign actions
+ FOR rec IN tf
LOOP
- EXECUTE FORMAT(
- 'PERFORM %s_table_%s (%s)'
- ,rec.action
- ,rec.name
- ,shard_idx::varchar
- );
+ IF IS NULL num_partitions
+ THEN
+ -- Add foreign constraints
+ EXECUTE FORMAT(
+ 'PERFORM %s_table_%s (%s)'::text
+ ,rec.action
+ ,rec.name
+ ,NULL
+ );
+ END IF
+ UPDATE exchange_tables
+ SET finished=TRUE
+ WHERE table_serial_id=rec.table_serial_id;
END LOOP;
-END
-$$;
-
-COMMENT ON FUNCTION create_partitions
- IS 'Creates all partitions that need creating.';
-
-
-
-
-CREATE OR REPLACE FUNCTION drop_default_partitions_NG()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-DECLARE
- tc CURSOR FOR
- SELECT name
- FROM exchange_tables
- WHERE partitioned
- AND NOT by_range;
-BEGIN
- RAISE NOTICE 'Dropping default tables of partitioned tables';
- FOR rec IN tc
- LOOP
- EXECUTE FORMAT (
- 'DROP TABLE IF EXISTS %s_default ;'::text,
- rec.name;
-END
-$$;
-
-COMMENT ON FUNCTION drop_default_partitions
- IS 'Drop all default partitions once other partitions are attached.
- Might be needed in sharding too.';
-
-CREATE OR REPLACE FUNCTION detach_default_partitions_NG()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-DECLARE
- tc CURSOR FOR
- SELECT name
- FROM exchange_tables
- WHERE partitioned
- AND NOT by_range;
-BEGIN
- RAISE NOTICE 'Detaching all default table partitions';
- FOR rec IN tc
- LOOP
- EXECUTE FORMAT (
- 'ALTER TABLE IF EXISTS %s DETACH PARTITION %s_default;'::text,
- rec.name,
- rec.name
- );
- END LOOP;
END
$$;
-COMMENT ON FUNCTION detach_default_partitions
- IS 'We need to drop default and create new one before deleting the default
partitions
- otherwise constraints get lost too. Might be needed in sharding too';
-
-
-CREATE OR REPLACE FUNCTION create_hash_partition_NG(
- source_table_name VARCHAR
- ,modulus INTEGER
- ,partition_num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num;
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
- ,source_table_name || '_' || partition_num
- ,source_table_name
- ,modulus
- ,partition_num-1
- );
-
-END
-$$;
-
-
-CREATE OR REPLACE FUNCTION create_partitions_NG(
- num_partitions INTEGER
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- tc CURSOR FOR
- SELECT name
- FROM exchange_tables
- WHERE partitioned
- AND NOT by_range;
-DECLARE
- i INTEGER;
-BEGIN
- PERFORM detach_default_partitions();
- FOR rec IN tc
- LOOP
- i := num_partitions
- LOOP
-
- PERFORM create_hash_partition(
- quote_literal (rec.name)
- ,num_partitions
- ,i
- );
-
- i=i-1;
- EXIT WHEN i=0;
- END LOOP; -- i = num_partitions ... 0
- END LOOP; -- for all partitioned tables
-
- PERFORM drop_default_partitions();
-
-END
-$$;
-
-
--- OLD LOGIC:
--------------------------------------------------------------------
-------------------------- Partitions ------------------------------
--------------------------------------------------------------------
-
-
-CREATE OR REPLACE FUNCTION create_range_partition(
- source_table_name VARCHAR
- ,partition_num INTEGER
-)
- RETURNS void
- LANGUAGE plpgsql
-AS $$
-BEGIN
- RAISE NOTICE 'TODO';
-END
-$$;
-
-CREATE OR REPLACE FUNCTION detach_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Detaching all default table partitions';
-
- ALTER TABLE IF EXISTS wire_targets
- DETACH PARTITION wire_targets_default;
-
- ALTER TABLE IF EXISTS reserves
- DETACH PARTITION reserves_default;
-
- ALTER TABLE IF EXISTS reserves_in
- DETACH PARTITION reserves_in_default;
-
- ALTER TABLE IF EXISTS reserves_close
- DETACH PARTITION reserves_close_default;
-
- ALTER TABLE IF EXISTS history_requests
- DETACH partition history_requests_default;
-
- ALTER TABLE IF EXISTS close_requests
- DETACH partition close_requests_default;
-
- ALTER TABLE IF EXISTS reserves_open_requests
- DETACH partition reserves_open_requests_default;
-
- ALTER TABLE IF EXISTS reserves_out
- DETACH PARTITION reserves_out_default;
-
- ALTER TABLE IF EXISTS reserves_out_by_reserve
- DETACH PARTITION reserves_out_by_reserve_default;
-
- ALTER TABLE IF EXISTS known_coins
- DETACH PARTITION known_coins_default;
-
- ALTER TABLE IF EXISTS refresh_commitments
- DETACH PARTITION refresh_commitments_default;
-
- ALTER TABLE IF EXISTS refresh_revealed_coins
- DETACH PARTITION refresh_revealed_coins_default;
-
- ALTER TABLE IF EXISTS refresh_transfer_keys
- DETACH PARTITION refresh_transfer_keys_default;
-
- ALTER TABLE IF EXISTS deposits
- DETACH PARTITION deposits_default;
-
---- TODO range partitioning
--- ALTER TABLE IF EXISTS deposits_by_ready
--- DETACH PARTITION deposits_by_ready_default;
---
--- ALTER TABLE IF EXISTS deposits_for_matching
--- DETACH PARTITION deposits_default_for_matching_default;
-
- ALTER TABLE IF EXISTS refunds
- DETACH PARTITION refunds_default;
-
- ALTER TABLE IF EXISTS wire_out
- DETACH PARTITION wire_out_default;
-
- ALTER TABLE IF EXISTS aggregation_transient
- DETACH PARTITION aggregation_transient_default;
-
- ALTER TABLE IF EXISTS aggregation_tracking
- DETACH PARTITION aggregation_tracking_default;
-
- ALTER TABLE IF EXISTS recoup
- DETACH PARTITION recoup_default;
-
- ALTER TABLE IF EXISTS recoup_by_reserve
- DETACH PARTITION recoup_by_reserve_default;
-
- ALTER TABLE IF EXISTS recoup_refresh
- DETACH PARTITION recoup_refresh_default;
-
- ALTER TABLE IF EXISTS prewire
- DETACH PARTITION prewire_default;
-
- ALTER TABLE IF EXISTS cs_nonce_locks
- DETACH partition cs_nonce_locks_default;
-
- ALTER TABLE IF EXISTS purse_requests
- DETACH partition purse_requests_default;
-
- ALTER TABLE IF EXISTS purse_decision
- DETACH partition purse_decision_default;
-
- ALTER TABLE IF EXISTS purse_merges
- DETACH partition purse_merges_default;
-
- ALTER TABLE IF EXISTS account_merges
- DETACH partition account_merges_default;
-
- ALTER TABLE IF EXISTS contracts
- DETACH partition contracts_default;
-
- ALTER TABLE IF EXISTS purse_deposits
- DETACH partition purse_deposits_default;
-
- ALTER TABLE IF EXISTS wad_out_entries
- DETACH partition wad_out_entries_default;
-
- ALTER TABLE IF EXISTS wads_in
- DETACH partition wads_in_default;
-
- ALTER TABLE IF EXISTS wad_in_entries
- DETACH partition wad_in_entries_default;
-END
-$$;
-
-COMMENT ON FUNCTION detach_default_partitions
- IS 'We need to drop default and create new one before deleting the default
partitions
- otherwise constraints get lost too. Might be needed in sharding too';
-
-
-CREATE OR REPLACE FUNCTION drop_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Dropping default table partitions';
-
- DROP TABLE IF EXISTS wire_targets_default;
- DROP TABLE IF EXISTS reserves_default;
- DROP TABLE IF EXISTS reserves_in_default;
- DROP TABLE IF EXISTS reserves_close_default;
- DROP TABLE IF EXISTS reserves_open_requests_default;
- DROP TABLE IF EXISTS history_requests_default;
- DROP TABLE IF EXISTS close_requests_default;
-
- DROP TABLE IF EXISTS reserves_out_default;
- DROP TABLE IF EXISTS reserves_out_by_reserve_default;
- DROP TABLE IF EXISTS known_coins_default;
- DROP TABLE IF EXISTS refresh_commitments_default;
- DROP TABLE IF EXISTS refresh_revealed_coins_default;
- DROP TABLE IF EXISTS refresh_transfer_keys_default;
- DROP TABLE IF EXISTS deposits_default;
---DROP TABLE IF EXISTS deposits_by_ready_default;
---DROP TABLE IF EXISTS deposits_for_matching_default;
- DROP TABLE IF EXISTS refunds_default;
- DROP TABLE IF EXISTS wire_out_default;
- DROP TABLE IF EXISTS aggregation_transient_default;
- DROP TABLE IF EXISTS aggregation_tracking_default;
- DROP TABLE IF EXISTS recoup_default;
- DROP TABLE IF EXISTS recoup_by_reserve_default;
- DROP TABLE IF EXISTS recoup_refresh_default;
- DROP TABLE IF EXISTS prewire_default;
- DROP TABLE IF EXISTS cs_nonce_locks_default;
-
- DROP TABLE IF EXISTS purse_requests_default;
- DROP TABLE IF EXISTS purse_decision_default;
- DROP TABLE IF EXISTS purse_merges_default;
- DROP TABLE IF EXISTS account_merges_default;
- DROP TABLE IF EXISTS purse_deposits_default;
- DROP TABLE IF EXISTS contracts_default;
-
- DROP TABLE IF EXISTS wad_out_entries_default;
- DROP TABLE IF EXISTS wads_in_default;
- DROP TABLE IF EXISTS wad_in_entries_default;
-
-END
-$$;
-
-COMMENT ON FUNCTION drop_default_partitions
- IS 'Drop all default partitions once other partitions are attached.
- Might be needed in sharding too.';
-
-CREATE OR REPLACE FUNCTION create_partitions(
- num_partitions INTEGER
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- modulus INTEGER;
-BEGIN
-
- modulus := num_partitions;
-
- PERFORM detach_default_partitions();
-
- LOOP
-
- PERFORM create_hash_partition(
- 'wire_targets'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'reserves'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'reserves_in'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'reserves_close'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_reserves_close_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'reserves_out'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'reserves_out_by_reserve'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'known_coins'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'refresh_commitments'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_commitments_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'refresh_revealed_coins'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'refresh_transfer_keys'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'deposits'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_deposits_partition(num_partitions::varchar);
-
--- TODO: dynamically (!) creating/deleting deposits partitions:
--- create new partitions 'as needed', drop old ones once the aggregator has
made
--- them empty; as 'new' deposits will always have deadlines in the future,
this
--- would basically guarantee no conflict between aggregator and exchange
service!
--- SEE also:
https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
--- (article is slightly wrong, as this works:)
---CREATE TABLE tab (
--- id bigint GENERATED ALWAYS AS IDENTITY,
--- ts timestamp NOT NULL,
--- data text
--- PARTITION BY LIST ((ts::date));
--- CREATE TABLE tab_def PARTITION OF tab DEFAULT;
--- BEGIN
--- CREATE TABLE tab_part2 (LIKE tab);
--- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo');
--- alter table tab attach partition tab_part2 for values in ('2022-03-21');
--- commit;
--- Naturally, to ensure this is actually 100% conflict-free, we'd
--- need to create tables at the granularity of the wire/refund deadlines;
--- that is right now configurable via AGGREGATOR_SHIFT option.
-
--- FIXME: range partitioning
--- PERFORM create_range_partition(
--- 'deposits_by_ready'
--- ,modulus
--- ,num_partitions
--- );
---
--- PERFORM create_range_partition(
--- 'deposits_for_matching'
--- ,modulus
--- ,num_partitions
--- );
-
- PERFORM create_hash_partition(
- 'refunds'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_refunds_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'wire_out'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'aggregation_transient'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'aggregation_tracking'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_aggregation_tracking_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'recoup'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_recoup_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'recoup_by_reserve'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'recoup_refresh'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_recoup_refresh_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'prewire'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'cs_nonce_locks'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar);
-
-
- PERFORM create_hash_partition(
- 'close_requests'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'reserves_open_requests'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_reserves_open_request_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'history_requests'
- ,modulus
- ,num_partitions
- );
-
-
- ---------------- P2P ----------------------
-
- PERFORM create_hash_partition(
- 'purse_requests'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_purse_requests_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'purse_decision'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_purse_decision_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'purse_merges'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'account_merges'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_account_merges_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'contracts'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_contracts_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'purse_deposits'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_purse_deposits_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'wad_out_entries'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_wad_out_entries_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'wads_in'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar);
-
- PERFORM create_hash_partition(
- 'wad_in_entries'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_wad_in_entries_partition(num_partitions::varchar);
-
- num_partitions=num_partitions-1;
- EXIT WHEN num_partitions=0;
-
- END LOOP;
-
- PERFORM drop_default_partitions();
-
-END
-$$;
+COMMENT ON FUNCTION create_tables
+ IS 'Creates all tables for the given number of partitions that need
creating.';
diff --git a/src/exchangedb/exchange-0002.sql.in
b/src/exchangedb/exchange-0002.sql.in
index 9d2110c8..8ecfc960 100644
--- a/src/exchangedb/exchange-0002.sql.in
+++ b/src/exchangedb/exchange-0002.sql.in
@@ -28,6 +28,14 @@ SET search_path TO exchange;
#include "0002-denomination_revocations.sql"
#include "0002-wire_targets.sql"
#include "0002-kyc_alerts.sql"
+#include "0002-wire_fee.sql"
+#include "0002-global_fee.sql"
+#include "0002-wire_accounts.sql"
+#include "0002-auditors.sql"
+#include "0002-auditor_denom_sigs.sql"
+#include "0002-exchange_sign_keys.sql"
+#include "0002-signkey_revocations.sql"
+#include "0002-extensions.sql"
#include "0002-profit_drains.sql"
#include "0002-legitimization_processes.sql"
#include "0002-legitimization_requirements.sql"
@@ -59,8 +67,14 @@ SET search_path TO exchange;
#include "0002-history_requests.sql"
#include "0002-purse_deposits.sql"
#include "0002-wads_in.sql"
-#include "0002-wads_in_entries.sql"
+#include "0002-wad_in_entries.sql"
#include "0002-wads_out.sql"
#include "0002-wad_out_entries.sql"
+#include "0002-policy_fulfillments.sql"
+#include "0002-policy_details.sql"
+#include "0002-work_shards.sql"
+#include "0002-revolving_work_shards.sql"
+#include "0002-partners.sql"
+#include "0002-partner_accounts.sql"
COMMIT;
diff --git a/src/exchangedb/exchange-0003.sql.in
b/src/exchangedb/exchange-0003.sql.in
index ee03d440..ba77d4d1 100644
--- a/src/exchangedb/exchange-0003.sql.in
+++ b/src/exchangedb/exchange-0003.sql.in
@@ -28,7 +28,6 @@ COMMENT ON SCHEMA exchange IS 'taler-exchange data';
SET search_path TO exchange;
-#include "0003-partner_accounts.sql"
#include "0003-purse_actions.sql"
#include "0003-purse_deletion.sql"
--
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 <=
- [taler-exchange] 04/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 10/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 06/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 15/15: handle partner_serial_id being NULL, gnunet, 2022/11/27
- [taler-exchange] 14/15: -fix foreign key constraint issue, gnunet, 2022/11/27
- [taler-exchange] 13/15: -constraint no longer holds, gnunet, 2022/11/27
- [taler-exchange] 05/15: more work on SQL refactoring, gnunet, 2022/11/27
- [taler-exchange] 09/15: more sql refactoring, gnunet, 2022/11/27
- [taler-exchange] 11/15: -db tests pass again, gnunet, 2022/11/27
- [taler-exchange] 12/15: -cleanup, gnunet, 2022/11/27