From be2c11a1797d8d16b86439a80a4f110f82bb5829 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 27 Nov 2022 02:00:38 +0100 Subject: 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 ++ src/exchangedb/0002-extensions.sql | 41 + 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 + src/exchangedb/0002-partner_accounts.sql | 47 ++ 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 ++ src/exchangedb/0002-signkey_revocations.sql | 37 + src/exchangedb/0002-wad_in_entries.sql | 93 +++ src/exchangedb/0002-wad_out_entries.sql | 14 + src/exchangedb/0002-wads_in.sql | 14 + src/exchangedb/0002-wads_in_entries.sql | 79 -- 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-partner_accounts.sql | 33 - 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 - 56 files changed, 1513 insertions(+), 1367 deletions(-) create mode 100644 src/exchangedb/0002-auditor_denom_sigs.sql create mode 100644 src/exchangedb/0002-auditors.sql create mode 100644 src/exchangedb/0002-exchange_sign_keys.sql create mode 100644 src/exchangedb/0002-extensions.sql create mode 100644 src/exchangedb/0002-global_fee.sql create mode 100644 src/exchangedb/0002-partner_accounts.sql create mode 100644 src/exchangedb/0002-partners.sql create mode 100644 src/exchangedb/0002-policy_details.sql create mode 100644 src/exchangedb/0002-policy_fulfillments.sql create mode 100644 src/exchangedb/0002-revolving_work_shards.sql create mode 100644 src/exchangedb/0002-signkey_revocations.sql create mode 100644 src/exchangedb/0002-wad_in_entries.sql delete mode 100644 src/exchangedb/0002-wads_in_entries.sql create mode 100644 src/exchangedb/0002-wire_accounts.sql create mode 100644 src/exchangedb/0002-wire_fee.sql create mode 100644 src/exchangedb/0002-work_shards.sql delete mode 100644 src/exchangedb/0003-partner_accounts.sql delete mode 100644 src/exchangedb/common-0002.sql (limited to 'src/exchangedb') diff --git a/src/exchangedb/0002-account_merges.sql b/src/exchangedb/0002-account_merges.sql index 88cbea130..c4102ac6f 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 7e6977f9e..25c394d26 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 c27310493..4739379e8 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 000000000..681a8b8e8 --- /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 +-- + + +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 000000000..3c18eef86 --- /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 +-- + + +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 8168b799b..103342c2a 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 d51ced57b..224430c95 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 e651fe15d..d34a84c8a 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 404deebab..35210443e 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 000000000..17511418d --- /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 +-- + +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/0002-extensions.sql b/src/exchangedb/0002-extensions.sql new file mode 100644 index 000000000..299e8ddd4 --- /dev/null +++ b/src/exchangedb/0002-extensions.sql @@ -0,0 +1,41 @@ +-- +-- 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 +-- + +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 000000000..8a63c0101 --- /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 +-- + +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 0b579759e..0df5ae01c 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 786d2a6ca..af2610c66 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 598603c79..1be54c3ca 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 34655f65b..c58d24318 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/0002-partner_accounts.sql b/src/exchangedb/0002-partner_accounts.sql new file mode 100644 index 000000000..2bf5a345e --- /dev/null +++ b/src/exchangedb/0002-partner_accounts.sql @@ -0,0 +1,47 @@ +-- +-- 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 +-- + + +CREATE TABLE IF NOT EXISTS partner_accounts + (payto_uri VARCHAR PRIMARY KEY + ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64) + ,last_seen INT8 NOT NULL + ); +CREATE INDEX IF NOT EXISTS partner_accounts_index_by_partner_and_time + ON partner_accounts (partner_serial_id,last_seen); +COMMENT ON TABLE partner_accounts + IS 'Table with bank accounts of the partner exchange. Entries never expire as we need to remember the signature for the auditor.'; +COMMENT ON COLUMN partner_accounts.payto_uri + IS 'payto URI (RFC 8905) with the bank account of the partner exchange.'; +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 000000000..992c04dac --- /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 +-- + +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 000000000..cd3c2f10e --- /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 +-- + +-- 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 000000000..6c01081af --- /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 +-- + +-- 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 451545e69..e26475c12 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 3905faaf2..2039cd931 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 375b0b698..0e0c98072 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 1e861cc52..08d7ca5ff 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 135b36df8..9f0aef067 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 1e420c5f2..a3183610a 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 859d6dd8e..9e6361a16 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 c5193beed..ce6077c5c 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 64f7027aa..e4b44557b 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 425a0b143..54274b262 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 daaae384d..509cc7d00 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 +-- + 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 7cb561a90..5f3b46040 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 fbb0b86c6..d08c961fe 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 0b3c37e54..2ca0ea718 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 380034b6d..132a123f0 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 a9ef5f861..e56553a5c 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 ea8cabbc4..e2aed9300 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 000000000..791966319 --- /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 +-- + +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/0002-signkey_revocations.sql b/src/exchangedb/0002-signkey_revocations.sql new file mode 100644 index 000000000..8e8b0a814 --- /dev/null +++ b/src/exchangedb/0002-signkey_revocations.sql @@ -0,0 +1,37 @@ +-- +-- 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 +-- + +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-wad_in_entries.sql b/src/exchangedb/0002-wad_in_entries.sql new file mode 100644 index 000000000..cc8b0a6fb --- /dev/null +++ b/src/exchangedb/0002-wad_in_entries.sql @@ -0,0 +1,93 @@ +-- +-- 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 +-- + +CREATE OR REPLACE FUNCTION create_table_wad_in_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_in_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_in_serial_id INT8' -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' + ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' + ',purse_expiration INT8 NOT NULL' + ',merge_timestamp INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wad_fee_val INT8 NOT NULL' + ',wad_fee_frac INT4 NOT NULL' + ',deposit_fees_val INT8 NOT NULL' + ',deposit_fees_frac INT4 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' + 'UNIQUE (wad_in_entry_serial_id) ' + ); +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 a475c6fbc..3f1076b25 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 48036641a..ca1466e05 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_in_entries.sql b/src/exchangedb/0002-wads_in_entries.sql deleted file mode 100644 index addd22722..000000000 --- a/src/exchangedb/0002-wads_in_entries.sql +++ /dev/null @@ -1,79 +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 --- - -CREATE OR REPLACE FUNCTION create_table_wad_in_entries( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'wad_in_entries'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_in_serial_id INT8' -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE - ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' - ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' - ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' - ',purse_expiration INT8 NOT NULL' - ',merge_timestamp INT8 NOT NULL' - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',wad_fee_val INT8 NOT NULL' - ',wad_fee_frac INT4 NOT NULL' - ',deposit_fees_val INT8 NOT NULL' - ',deposit_fees_frac INT4 NOT NULL' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by reserve_pub! - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' - 'IS ' || quote_literal('needed in reserve history computation') || ';' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' - 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' - 'UNIQUE (wad_in_entry_serial_id) ' - ); -END -$$; diff --git a/src/exchangedb/0002-wads_out.sql b/src/exchangedb/0002-wads_out.sql index afa56fbbf..a44b615e4 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 000000000..d23ec7306 --- /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 +-- + +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 000000000..7b53c6f37 --- /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 +-- + +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 9d04cf784..f34998b58 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 5e8f19059..08bc468d3 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 000000000..d33022054 --- /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 +-- + +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-partner_accounts.sql b/src/exchangedb/0003-partner_accounts.sql deleted file mode 100644 index 6ed372f50..000000000 --- a/src/exchangedb/0003-partner_accounts.sql +++ /dev/null @@ -1,33 +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 --- - - -CREATE TABLE IF NOT EXISTS partner_accounts - (payto_uri VARCHAR PRIMARY KEY - ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64) - ,last_seen INT8 NOT NULL - ); -CREATE INDEX IF NOT EXISTS partner_accounts_index_by_partner_and_time - ON partner_accounts (partner_serial_id,last_seen); -COMMENT ON TABLE partner_accounts - IS 'Table with bank accounts of the partner exchange. Entries never expire as we need to remember the signature for the auditor.'; -COMMENT ON COLUMN partner_accounts.payto_uri - IS 'payto URI (RFC 8905) with the bank account of the partner exchange.'; -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.'; diff --git a/src/exchangedb/0003-purse_actions.sql b/src/exchangedb/0003-purse_actions.sql index df8eecea7..c77dfb3c5 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 649efa11c..000000000 --- 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 --- - --------------------------------- - -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 7855c996e..8a1036085 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 -- - - - --- ------------------------------ 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 6998320ce..f7bf15f6a 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 9d2110c8e..8ecfc9604 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 ee03d4408..ba77d4d12 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" -- cgit v1.2.3