From 9580dd19c23e5591cc022dce717eca7bc745c5b0 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 27 Nov 2022 00:16:00 +0100 Subject: intermediate step in major SQL refactoring (not done at all) --- src/exchangedb/.gitignore | 4 - src/exchangedb/0002-account_merges.sql | 66 + src/exchangedb/0002-aggregation_tracking.sql | 66 + src/exchangedb/0002-aggregation_transient.sql | 43 + src/exchangedb/0002-close_requests.sql | 85 + src/exchangedb/0002-contracts.sql | 59 + src/exchangedb/0002-cs_nonce_locks.sql | 53 + src/exchangedb/0002-denomination_revocations.sql | 37 + src/exchangedb/0002-denominations.sql | 72 + src/exchangedb/0002-deposits.sql | 146 + src/exchangedb/0002-history_requests.sql | 44 + src/exchangedb/0002-known_coins.sql | 61 + src/exchangedb/0002-kyc_alerts.sql | 41 + src/exchangedb/0002-legitimization_processes.sql | 72 + .../0002-legitimization_requirements.sql | 60 + src/exchangedb/0002-prewire.sql | 63 + src/exchangedb/0002-profit_drains.sql | 57 + src/exchangedb/0002-purse_decision.sql | 59 + src/exchangedb/0002-purse_deposits.sql | 68 + src/exchangedb/0002-purse_merges.sql | 71 + src/exchangedb/0002-purse_requests.sql | 85 + src/exchangedb/0002-recoup.sql | 98 + src/exchangedb/0002-recoup_refresh.sql | 76 + src/exchangedb/0002-refresh_commitments.sql | 67 + src/exchangedb/0002-refresh_revealed_coins.sql | 74 + src/exchangedb/0002-refresh_transfer_keys.sql | 55 + src/exchangedb/0002-refunds.sql | 72 + src/exchangedb/0002-reserves.sql | 74 + src/exchangedb/0002-reserves_close.sql | 73 + src/exchangedb/0002-reserves_in.sql | 83 + src/exchangedb/0002-reserves_open_deposits.sql | 70 + src/exchangedb/0002-reserves_open_requests.sql | 73 + src/exchangedb/0002-reserves_out.sql | 111 + src/exchangedb/0002-wad_out_entries.sql | 76 + src/exchangedb/0002-wads_in.sql | 60 + src/exchangedb/0002-wads_in_entries.sql | 79 + src/exchangedb/0002-wads_out.sql | 57 + src/exchangedb/0002-wire_out.sql | 67 + src/exchangedb/0002-wire_targets.sql | 67 + src/exchangedb/0003-partner_accounts.sql | 33 + src/exchangedb/0003-purse_actions.sql | 60 + src/exchangedb/0003-purse_deletion.sql | 94 + src/exchangedb/Makefile.am | 24 +- src/exchangedb/common-0001.sql | 2954 -------------------- src/exchangedb/common-0002.sql | 104 - src/exchangedb/exchange-0001-part.sql | 122 +- src/exchangedb/exchange-0001.sql | 783 ++++++ src/exchangedb/exchange-0001.sql.in | 33 - src/exchangedb/exchange-0002-part.sql | 36 - src/exchangedb/exchange-0002.sql | 1839 ++++++++++++ src/exchangedb/exchange-0002.sql.in | 37 + src/exchangedb/exchange-0003.sql.in | 35 + src/exchangedb/plugin_exchangedb_postgres.c | 1 - 53 files changed, 5534 insertions(+), 3265 deletions(-) create mode 100644 src/exchangedb/0002-account_merges.sql create mode 100644 src/exchangedb/0002-aggregation_tracking.sql create mode 100644 src/exchangedb/0002-aggregation_transient.sql create mode 100644 src/exchangedb/0002-close_requests.sql create mode 100644 src/exchangedb/0002-contracts.sql create mode 100644 src/exchangedb/0002-cs_nonce_locks.sql create mode 100644 src/exchangedb/0002-denomination_revocations.sql create mode 100644 src/exchangedb/0002-denominations.sql create mode 100644 src/exchangedb/0002-deposits.sql create mode 100644 src/exchangedb/0002-history_requests.sql create mode 100644 src/exchangedb/0002-known_coins.sql create mode 100644 src/exchangedb/0002-kyc_alerts.sql create mode 100644 src/exchangedb/0002-legitimization_processes.sql create mode 100644 src/exchangedb/0002-legitimization_requirements.sql create mode 100644 src/exchangedb/0002-prewire.sql create mode 100644 src/exchangedb/0002-profit_drains.sql create mode 100644 src/exchangedb/0002-purse_decision.sql create mode 100644 src/exchangedb/0002-purse_deposits.sql create mode 100644 src/exchangedb/0002-purse_merges.sql create mode 100644 src/exchangedb/0002-purse_requests.sql create mode 100644 src/exchangedb/0002-recoup.sql create mode 100644 src/exchangedb/0002-recoup_refresh.sql create mode 100644 src/exchangedb/0002-refresh_commitments.sql create mode 100644 src/exchangedb/0002-refresh_revealed_coins.sql create mode 100644 src/exchangedb/0002-refresh_transfer_keys.sql create mode 100644 src/exchangedb/0002-refunds.sql create mode 100644 src/exchangedb/0002-reserves.sql create mode 100644 src/exchangedb/0002-reserves_close.sql create mode 100644 src/exchangedb/0002-reserves_in.sql create mode 100644 src/exchangedb/0002-reserves_open_deposits.sql create mode 100644 src/exchangedb/0002-reserves_open_requests.sql create mode 100644 src/exchangedb/0002-reserves_out.sql create mode 100644 src/exchangedb/0002-wad_out_entries.sql create mode 100644 src/exchangedb/0002-wads_in.sql create mode 100644 src/exchangedb/0002-wads_in_entries.sql create mode 100644 src/exchangedb/0002-wads_out.sql create mode 100644 src/exchangedb/0002-wire_out.sql create mode 100644 src/exchangedb/0002-wire_targets.sql create mode 100644 src/exchangedb/0003-partner_accounts.sql create mode 100644 src/exchangedb/0003-purse_actions.sql create mode 100644 src/exchangedb/0003-purse_deletion.sql delete mode 100644 src/exchangedb/common-0001.sql create mode 100644 src/exchangedb/exchange-0001.sql delete mode 100644 src/exchangedb/exchange-0001.sql.in delete mode 100644 src/exchangedb/exchange-0002-part.sql create mode 100644 src/exchangedb/exchange-0002.sql create mode 100644 src/exchangedb/exchange-0003.sql.in (limited to 'src') diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index 540724b8..5afbccdf 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -5,10 +5,6 @@ test-exchangedb-postgres test-exchangedb-signkeys test-perf-taler-exchangedb bench-db-postgres -exchange-0001.sql -shard-0000.sql -shard-0001.sql -drop0001.sql shard-drop0001.sqltest-exchangedb-by-j-postgres test-exchangedb-by-j-postgres perf-exchangedb-reserves-in-insert-postgres diff --git a/src/exchangedb/0002-account_merges.sql b/src/exchangedb/0002-account_merges.sql new file mode 100644 index 00000000..88cbea13 --- /dev/null +++ b/src/exchangedb/0002-account_merges.sql @@ -0,0 +1,66 @@ +-- +-- 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_account_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'account_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' -- REFERENCES purse_requests (purse_pub) + ',wallet_h_payto BYTEA NOT NULL CHECK (LENGTH(wallet_h_payto)=32)' + ',PRIMARY KEY (purse_pub)' + ') %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 || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE account_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' + 'UNIQUE (account_merge_request_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-aggregation_tracking.sql b/src/exchangedb/0002-aggregation_tracking.sql new file mode 100644 index 00000000..7e6977f9 --- /dev/null +++ b/src/exchangedb/0002-aggregation_tracking.sql @@ -0,0 +1,66 @@ +-- +-- 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_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient depost -- or something else ??? + ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (deposit_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'ON ' || table_name || ' ' + '(wtid_raw);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' + 'IS ' || quote_literal('for lookup_transactions') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' + 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' + 'UNIQUE (aggregation_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-aggregation_transient.sql b/src/exchangedb/0002-aggregation_transient.sql new file mode 100644 index 00000000..c2731049 --- /dev/null +++ b/src/exchangedb/0002-aggregation_transient.sql @@ -0,0 +1,43 @@ +-- +-- 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_aggregation_transient( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_transient'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' + ',exchange_account_section TEXT NOT NULL' + ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; diff --git a/src/exchangedb/0002-close_requests.sql b/src/exchangedb/0002-close_requests.sql new file mode 100644 index 00000000..8168b799 --- /dev/null +++ b/src/exchangedb/0002-close_requests.sql @@ -0,0 +1,85 @@ +-- +-- 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_close_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + ',close_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',close_val INT8 NOT NULL' + ',close_frac INT4 NOT NULL' + ',close_fee_val INT8 NOT NULL' + ',close_fee_frac INT4 NOT NULL' + ',payto_uri VARCHAR NOT NULL' + ',done BOOL NOT NULL DEFAULT(FALSE)' + ',PRIMARY KEY (reserve_pub,close_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); +END +$$; + + +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' + 'ON ' || table_name || ' ' + '(close_request_serial_id);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_done_index ' + 'ON ' || table_name || ' ' + '(done);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE close_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT close_requests_' || partition_suffix || '_close_request_uuid_pkey ' + 'UNIQUE (close_request_serial_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-contracts.sql b/src/exchangedb/0002-contracts.sql new file mode 100644 index 00000000..d51ced57 --- /dev/null +++ b/src/exchangedb/0002-contracts.sql @@ -0,0 +1,59 @@ +-- +-- 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_contracts( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'contracts'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' + ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' + ',e_contract BYTEA NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE contracts_' || partition_suffix || ' ' + 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' + 'UNIQUE (contract_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-cs_nonce_locks.sql b/src/exchangedb/0002-cs_nonce_locks.sql new file mode 100644 index 00000000..e651fe15 --- /dev/null +++ b/src/exchangedb/0002-cs_nonce_locks.sql @@ -0,0 +1,53 @@ +-- +-- 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_cs_nonce_locks( + shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' + ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' + ',max_denomination_serial INT8 NOT NULL' + ') %s ;' + ,'cs_nonce_locks' + ,'PARTITION BY HASH (nonce)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' + 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' + 'UNIQUE (cs_nonce_lock_serial_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-denomination_revocations.sql b/src/exchangedb/0002-denomination_revocations.sql new file mode 100644 index 00000000..57668b35 --- /dev/null +++ b/src/exchangedb/0002-denomination_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 denomination_revocations + (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ); +COMMENT ON TABLE denomination_revocations + IS 'remembering which denomination keys have been revoked'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denomination_revocations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-denominations.sql b/src/exchangedb/0002-denominations.sql new file mode 100644 index 00000000..a18a752b --- /dev/null +++ b/src/exchangedb/0002-denominations.sql @@ -0,0 +1,72 @@ +-- +-- 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_denominations() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + +CREATE TABLE IF NOT EXISTS denominations + (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) + ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default later!) + ,age_mask INT4 NOT NULL DEFAULT (0) + ,denom_pub BYTEA NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,valid_from INT8 NOT NULL + ,expire_withdraw INT8 NOT NULL + ,expire_deposit INT8 NOT NULL + ,expire_legal INT8 NOT NULL + ,coin_val INT8 NOT NULL + ,coin_frac INT4 NOT NULL + ,fee_withdraw_val INT8 NOT NULL + ,fee_withdraw_frac INT4 NOT NULL + ,fee_deposit_val INT8 NOT NULL + ,fee_deposit_frac INT4 NOT NULL + ,fee_refresh_val INT8 NOT NULL + ,fee_refresh_frac INT4 NOT NULL + ,fee_refund_val INT8 NOT NULL + ,fee_refund_frac INT4 NOT NULL + ); +COMMENT ON TABLE denominations + IS 'Main denominations table. All the valid denominations the exchange knows about.'; +COMMENT ON COLUMN denominations.denom_type + IS 'determines cipher type for blind signatures used with this denomination; 0 is for RSA'; +COMMENT ON COLUMN denominations.age_mask + IS 'bitmask with the age restrictions that are being used for this denomination; 0 if denomination does not support the use of age restrictions'; +COMMENT ON COLUMN denominations.denominations_serial + IS 'needed for exchange-auditor replication logic'; + +CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index + ON denominations + (expire_legal); + +END +$$; + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denominations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql new file mode 100644 index 00000000..404deeba --- /dev/null +++ b/src/exchangedb/0002-deposits.sql @@ -0,0 +1,146 @@ +-- +-- 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_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wallet_timestamp INT8 NOT NULL' + ',exchange_timestamp INT8 NOT NULL' + ',refund_deadline INT8 NOT NULL' + ',wire_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',policy_details_serial_id INT8' -- REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id) ' + ',ADD CONSTRAINT deposits_' || partition_suffix || '_coin_pub_merchant_pub_h_contract_terms_key ' + 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_by_ready'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_deadline INT8 NOT NULL' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (wire_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(wire_deadline ASC, shard ASC, coin_pub);' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_for_matching'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (refund_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(refund_deadline ASC, merchant_pub, coin_pub);' + ); + +END +$$; diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql new file mode 100644 index 00000000..0b579759 --- /dev/null +++ b/src/exchangedb/0002-history_requests.sql @@ -0,0 +1,44 @@ +-- +-- 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_history_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'history_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + ',request_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',history_fee_val INT8 NOT NULL' + ',history_fee_frac INT4 NOT NULL' + ',PRIMARY KEY (reserve_pub,request_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + +END +$$; diff --git a/src/exchangedb/0002-known_coins.sql b/src/exchangedb/0002-known_coins.sql new file mode 100644 index 00000000..786d2a6c --- /dev/null +++ b/src/exchangedb/0002-known_coins.sql @@ -0,0 +1,61 @@ +-- +-- 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_known_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'known_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' + ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' + ',denom_sig BYTEA NOT NULL' + ',remaining_val INT8 NOT NULL DEFAULT(0)' + ',remaining_frac INT4 NOT NULL DEFAULT(0)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE known_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' + 'UNIQUE (known_coin_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-kyc_alerts.sql b/src/exchangedb/0002-kyc_alerts.sql new file mode 100644 index 00000000..74872a9c --- /dev/null +++ b/src/exchangedb/0002-kyc_alerts.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 kyc_alerts + (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) + ,trigger_type INT4 NOT NULL + ,UNIQUE(trigger_type,h_payto) + ); +COMMENT ON TABLE kyc_alerts + IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; +COMMENT ON COLUMN kyc_alerts.h_payto + IS 'hash of the payto://-URI for which the KYC status changed'; +COMMENT ON COLUMN kyc_alerts.trigger_type + IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_alerts' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-legitimization_processes.sql b/src/exchangedb/0002-legitimization_processes.sql new file mode 100644 index 00000000..598603c7 --- /dev/null +++ b/src/exchangedb/0002-legitimization_processes.sql @@ -0,0 +1,72 @@ +-- +-- 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_legitimization_processes( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',expiration_time INT8 NOT NULL DEFAULT (0)' + ',provider_section VARCHAR NOT NULL' + ',provider_user_id VARCHAR DEFAULT NULL' + ',provider_legitimization_id VARCHAR DEFAULT NULL' + ',UNIQUE (h_payto, provider_section)' + ') %s ;' + ,'legitimization_processes' + ,'PARTITION BY HASH (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_legitimization_processes_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + + partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_key ' + 'UNIQUE (legitimization_process_serial_id)'); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index ' + 'ON '|| partition_name || ' ' + '(provider_section,provider_legitimization_id)' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index ' + 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';' + ); +END +$$; diff --git a/src/exchangedb/0002-legitimization_requirements.sql b/src/exchangedb/0002-legitimization_requirements.sql new file mode 100644 index 00000000..34655f65 --- /dev/null +++ b/src/exchangedb/0002-legitimization_requirements.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 OR REPLACE FUNCTION create_table_legitimization_requirements( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',required_checks VARCHAR NOT NULL' + ',UNIQUE (h_payto, required_checks)' + ') %s ;' + ,'legitimization_requirements' + ,'PARTITION BY HASH (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_legitimization_requirements_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + + partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' + 'UNIQUE (legitimization_requirement_serial_id)'); +END +$$; diff --git a/src/exchangedb/0002-prewire.sql b/src/exchangedb/0002-prewire.sql new file mode 100644 index 00000000..451545e6 --- /dev/null +++ b/src/exchangedb/0002-prewire.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 OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' + ',wire_method TEXT NOT NULL' + ',finished BOOLEAN NOT NULL DEFAULT false' + ',failed BOOLEAN NOT NULL DEFAULT false' + ',buf BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (prewire_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'ON ' || table_name || ' ' + '(finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' + 'IS ' || quote_literal('for gc_prewire') || ';' + ); + -- FIXME: find a way to combine these two indices? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'ON ' || table_name || ' ' + '(failed,finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' + 'IS ' || quote_literal('for wire_prepare_data_get') || ';' + ); + +END +$$; diff --git a/src/exchangedb/0002-profit_drains.sql b/src/exchangedb/0002-profit_drains.sql new file mode 100644 index 00000000..bb713cd2 --- /dev/null +++ b/src/exchangedb/0002-profit_drains.sql @@ -0,0 +1,57 @@ +-- +-- 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 profit_drains + (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) + ,account_section VARCHAR NOT NULL + ,payto_uri VARCHAR NOT NULL + ,trigger_date INT8 NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,executed BOOLEAN NOT NULL DEFAULT FALSE + ); +COMMENT ON TABLE profit_drains + IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; +COMMENT ON COLUMN profit_drains.wtid + IS 'randomly chosen nonce, unique to prevent double-submission'; +COMMENT ON COLUMN profit_drains.account_section + IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; +COMMENT ON COLUMN profit_drains.payto_uri + IS 'specifies the account to be credited'; +COMMENT ON COLUMN profit_drains.trigger_date + IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; +COMMENT ON COLUMN profit_drains.amount_val + IS 'amount to be transferred'; +COMMENT ON COLUMN profit_drains.master_sig + IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; +COMMENT ON COLUMN profit_drains.executed + IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('profit_drains' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-purse_decision.sql b/src/exchangedb/0002-purse_decision.sql new file mode 100644 index 00000000..3905faaf --- /dev/null +++ b/src/exchangedb/0002-purse_decision.sql @@ -0,0 +1,59 @@ +-- +-- 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_purse_decision( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_decision'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',action_timestamp INT8 NOT NULL' + ',refunded BOOL NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_decision_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key ' + 'UNIQUE (purse_decision_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_deposits.sql b/src/exchangedb/0002-purse_deposits.sql new file mode 100644 index 00000000..375b0b69 --- /dev/null +++ b/src/exchangedb/0002-purse_deposits.sql @@ -0,0 +1,68 @@ +-- +-- 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_purse_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',PRIMARY KEY (purse_pub,coin_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by coin_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' + 'UNIQUE (purse_deposit_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_merges.sql b/src/exchangedb/0002-purse_merges.sql new file mode 100644 index 00000000..1e861cc5 --- /dev/null +++ b/src/exchangedb/0002-purse_merges.sql @@ -0,0 +1,71 @@ +-- +-- 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_purse_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY '-- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)'--REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE + ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' + ',merge_timestamp INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %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_purse_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' + 'UNIQUE (purse_merge_request_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_requests.sql b/src/exchangedb/0002-purse_requests.sql new file mode 100644 index 00000000..135b36df --- /dev/null +++ b/src/exchangedb/0002-purse_requests.sql @@ -0,0 +1,85 @@ +-- +-- 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_purse_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' + ',purse_creation INT8 NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',age_limit INT4 NOT NULL' + ',flags INT4 NOT NULL' + ',in_reserve_quota BOOLEAN NOT NULL DEFAULT(FALSE)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',purse_fee_val INT8 NOT NULL' + ',purse_fee_frac INT4 NOT NULL' + ',balance_val INT8 NOT NULL DEFAULT (0)' + ',balance_frac INT4 NOT NULL DEFAULT (0)' + ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by merge_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' + 'ON ' || table_name || ' ' + '(merge_pub);' + ); + + -- FIXME: drop index on master (crosses shards)? + -- Or use materialized index? (needed?) + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' + 'ON ' || table_name || ' ' + '(purse_expiration);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' + 'UNIQUE (purse_requests_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql new file mode 100644 index 00000000..1e420c5f --- /dev/null +++ b/src/exchangedb/0002-recoup.sql @@ -0,0 +1,98 @@ +-- +-- 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_recoup( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub);' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' + 'UNIQUE (recoup_uuid) ' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + +END +$$; diff --git a/src/exchangedb/0002-recoup_refresh.sql b/src/exchangedb/0002-recoup_refresh.sql new file mode 100644 index 00000000..859d6dd8 --- /dev/null +++ b/src/exchangedb/0002-recoup_refresh.sql @@ -0,0 +1,76 @@ +-- +-- 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_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: any query using this index will be slow. Materialize index or change query? + -- Also: which query uses this index? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' + 'ON ' || table_name || ' ' + '(rrc_serial);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' + 'UNIQUE (recoup_refresh_uuid) ' + ); +END +$$; diff --git a/src/exchangedb/0002-refresh_commitments.sql b/src/exchangedb/0002-refresh_commitments.sql new file mode 100644 index 00000000..c5193bee --- /dev/null +++ b/src/exchangedb/0002-refresh_commitments.sql @@ -0,0 +1,67 @@ +-- +-- 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_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_commitments'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' + ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',noreveal_index INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (rc)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- Note: index spans partitions, may need to be materialized. + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' + 'ON ' || table_name || ' ' + '(old_coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' + 'UNIQUE (melt_serial_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql b/src/exchangedb/0002-refresh_revealed_coins.sql new file mode 100644 index 00000000..64f7027a --- /dev/null +++ b/src/exchangedb/0002-refresh_revealed_coins.sql @@ -0,0 +1,74 @@ +-- +-- 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_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',freshcoin_index INT4 NOT NULL' + ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_ev BYTEA NOT NULL' -- UNIQUE' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' + ',ev_sig BYTEA NOT NULL' + ',ewv BYTEA NOT NULL' + -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'ON ' || table_name || ' ' + '(melt_serial_id);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' + 'UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' + 'UNIQUE (coin_ev) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' + 'UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + ); +END +$$; diff --git a/src/exchangedb/0002-refresh_transfer_keys.sql b/src/exchangedb/0002-refresh_transfer_keys.sql new file mode 100644 index 00000000..425a0b14 --- /dev/null +++ b/src/exchangedb/0002-refresh_transfer_keys.sql @@ -0,0 +1,55 @@ +-- +-- 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_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_transfer_keys'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' + ',transfer_privs BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' + 'UNIQUE (rtc_serial)' + ); +END +$$; diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql new file mode 100644 index 00000000..daaae384 --- /dev/null +++ b/src/exchangedb/0002-refunds.sql @@ -0,0 +1,72 @@ +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' + ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' + ',rtransaction_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION constrain0002_table_refunds ( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + -- FIXME: '_' issue if partition_suffix is NULL + -- => solve with general ALTER TABLE helper function! + 'ALTER TABLE refunds_' || partition_suffix || ' ' + 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' + 'UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('refunds' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('refunds' + ,'exchange-0002' + ,'constrain0002' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql new file mode 100644 index 00000000..7cb561a9 --- /dev/null +++ b/src/exchangedb/0002-reserves.sql @@ -0,0 +1,74 @@ +-- +-- 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_reserves( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' + ',current_balance_val INT8 NOT NULL DEFAULT(0)' + ',current_balance_frac INT4 NOT NULL DEFAULT(0)' + ',purses_active INT8 NOT NULL DEFAULT(0)' + ',purses_allowed INT8 NOT NULL DEFAULT(0)' + ',max_age INT4 NOT NULL DEFAULT(120)' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'ON ' || table_name || ' ' + '(expiration_date' + ',current_balance_val' + ',current_balance_frac' + ');' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' + 'IS ' || quote_literal('used in get_expired_reserves') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'ON ' || table_name || ' ' + '(gc_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' + 'IS ' || quote_literal('for reserve garbage collection') || ';' + ); + +END +$$; diff --git a/src/exchangedb/0002-reserves_close.sql b/src/exchangedb/0002-reserves_close.sql new file mode 100644 index 00000000..fbb0b86c --- /dev/null +++ b/src/exchangedb/0002-reserves_close.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 +-- + +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',execution_date INT8 NOT NULL' + ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ',close_request_row INT8 NOT NULL DEFAULT(0)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' + 'ON ' || table_name || ' ' + '(close_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' + 'PRIMARY KEY (close_uuid)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_in.sql b/src/exchangedb/0002-reserves_in.sql new file mode 100644 index 00000000..0b3c37e5 --- /dev/null +++ b/src/exchangedb/0002-reserves_in.sql @@ -0,0 +1,83 @@ +-- +-- 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_reserves_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',wire_reference INT8 NOT NULL' + ',credit_val INT8 NOT NULL' + ',credit_frac INT4 NOT NULL' + ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_in_serial_id);' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section ' + ',execution_date' + ');' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section,' + 'reserve_in_serial_id DESC' + ');' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' + 'UNIQUE (reserve_in_serial_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_open_deposits.sql b/src/exchangedb/0002-reserves_open_deposits.sql new file mode 100644 index 00000000..380034b6 --- /dev/null +++ b/src/exchangedb/0002-reserves_open_deposits.sql @@ -0,0 +1,70 @@ +-- +-- 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_reserves_open_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',contribution_val INT8 NOT NULL' + ',contribution_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid ' + 'ON ' || table_name || ' ' + '(reserve_open_deposit_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' + 'PRIMARY KEY (coin_pub,coin_sig)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_open_requests.sql b/src/exchangedb/0002-reserves_open_requests.sql new file mode 100644 index 00000000..a9ef5f86 --- /dev/null +++ b/src/exchangedb/0002-reserves_open_requests.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 +-- + +CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',request_timestamp INT8 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_payment_val INT8 NOT NULL' + ',reserve_payment_frac INT4 NOT NULL' + ',requested_purse_limit INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' + 'ON ' || table_name || ' ' + '(open_request_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'PRIMARY KEY (open_request_uuid),' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'UNIQUE (reserve_pub,request_timestamp)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql new file mode 100644 index 00000000..ea8cabbc --- /dev/null +++ b/src/exchangedb/0002-reserves_out.sql @@ -0,0 +1,111 @@ +-- +-- 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_reserves_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denom_sig BYTEA NOT NULL' + ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',execution_date INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,'reserves_out' + ,'PARTITION BY HASH (h_blind_ev)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid, execution_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' + ); + +END +$$; + + + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' + ') %s ' + ,table_name + ,'PARTITION BY HASH (reserve_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + +END +$$; diff --git a/src/exchangedb/0002-wad_out_entries.sql b/src/exchangedb/0002-wad_out_entries.sql new file mode 100644 index 00000000..a475c6fb --- /dev/null +++ b/src/exchangedb/0002-wad_out_entries.sql @@ -0,0 +1,76 @@ +-- +-- 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_out_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_out_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_out_serial_id INT8' -- REFERENCES wads_out (wad_out_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 || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' + 'UNIQUE (wad_out_entry_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wads_in.sql b/src/exchangedb/0002-wads_in.sql new file mode 100644 index 00000000..48036641 --- /dev/null +++ b/src/exchangedb/0002-wads_in.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 OR REPLACE FUNCTION create_table_wads_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',origin_exchange_url TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',arrival_time INT8 NOT NULL' + ',UNIQUE (wad_id, origin_exchange_url)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' + 'UNIQUE (wad_in_serial_id) ' + ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' + 'UNIQUE (wad_id, origin_exchange_url) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wads_in_entries.sql b/src/exchangedb/0002-wads_in_entries.sql new file mode 100644 index 00000000..addd2272 --- /dev/null +++ b/src/exchangedb/0002-wads_in_entries.sql @@ -0,0 +1,79 @@ +-- +-- 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 new file mode 100644 index 00000000..afa56fbb --- /dev/null +++ b/src/exchangedb/0002-wads_out.sql @@ -0,0 +1,57 @@ +-- +-- 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_wads_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',partner_serial_id INT8 NOT NULL' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',execution_time INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' + 'UNIQUE (wad_out_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wire_out.sql b/src/exchangedb/0002-wire_out.sql new file mode 100644 index 00000000..9d04cf78 --- /dev/null +++ b/src/exchangedb/0002-wire_out.sql @@ -0,0 +1,67 @@ +-- +-- 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_wire_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wire_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',execution_date INT8 NOT NULL' + ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wtid_raw)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' + 'ON ' || table_name || ' ' + '(wire_target_h_payto);' + ); + + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wire_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' + 'PRIMARY KEY (wireout_uuid)' + ); +END +$$; diff --git a/src/exchangedb/0002-wire_targets.sql b/src/exchangedb/0002-wire_targets.sql new file mode 100644 index 00000000..5e8f1905 --- /dev/null +++ b/src/exchangedb/0002-wire_targets.sql @@ -0,0 +1,67 @@ +-- +-- 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_wire_targets( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +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_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' + ',payto_uri VARCHAR NOT NULL' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (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( + 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 ' + 'UNIQUE (wire_target_serial_id)' + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_targets' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0003-partner_accounts.sql b/src/exchangedb/0003-partner_accounts.sql new file mode 100644 index 00000000..6ed372f5 --- /dev/null +++ b/src/exchangedb/0003-partner_accounts.sql @@ -0,0 +1,33 @@ +-- +-- 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 new file mode 100644 index 00000000..df8eecea --- /dev/null +++ b/src/exchangedb/0003-purse_actions.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 purse_actions + (purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32) + ,action_date INT8 NOT NULL + ,partner_serial_id INT8 + ); +COMMENT ON TABLE purse_actions + IS 'purses awaiting some action by the router'; +COMMENT ON COLUMN purse_actions.purse_pub + IS 'public (contract) key of the purse'; +COMMENT ON COLUMN purse_actions.action_date + IS 'when is the purse ready for action'; +COMMENT ON COLUMN purse_actions.partner_serial_id + IS 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown'; + +CREATE INDEX IF NOT EXISTS purse_action_by_target + ON purse_actions + (partner_serial_id,action_date); + + +CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO + purse_actions + (purse_pub + ,action_date) + VALUES + (NEW.purse_pub + ,NEW.purse_expiration); + RETURN NEW; +END $$; +COMMENT ON FUNCTION purse_requests_insert_trigger() + IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; + +CREATE TRIGGER purse_requests_on_insert + AFTER INSERT + ON purse_requests + FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); +COMMENT ON TRIGGER purse_requests_on_insert + ON purse_requests + IS 'Here we install an entry for the purse expiration.'; diff --git a/src/exchangedb/0003-purse_deletion.sql b/src/exchangedb/0003-purse_deletion.sql new file mode 100644 index 00000000..e655ee61 --- /dev/null +++ b/src/exchangedb/0003-purse_deletion.sql @@ -0,0 +1,94 @@ +-- +-- 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_purse_deletion( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' + ',XXX VARCHAR NOT NULL' + ') %s ;' + ,'purse_deletion' + ,'PARTITION BY HASH (XXX)' + ,shard_suffix + ); + COMMENT ON TABLE purse_deletion + IS 'signatures affirming explicit purse deletions'; + COMMENT ON COLUMN purse_deletion.purse_sig + IS 'signature of type XXX'; +END +$$; +COMMENT ON FUNCTION create_table_purse_deletion + IS 'Creates the purse_deletion table'; + +CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deletion_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deletion_' || partition_suffix || '_XXX ' + 'UNIQUE (XXX)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION alter_create_was_deleted_table_purse_requests ( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || + ' ADD COLUMN' + ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' + ); +END +$$; + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('purse_deletion' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('purse_deletion' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('purse_requests' + ,'exchange-0002' + ,'alter_create_was_deleted' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 595bb903..d7dd0895 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -15,37 +15,37 @@ pkgcfg_DATA = \ sqldir = $(prefix)/share/taler/sql/exchange/ sqlinputs = \ - common-0001.sql \ - exchange-0001.sql.in \ - exchange-0001-part.sql \ - common-0002.sql \ + 0002-*.sql \ + 0003-*.sql \ exchange-0002.sql.in \ - exchange-0002-part.sql + exchange-0003.sql.in sql_DATA = \ benchmark-0001.sql \ versioning.sql \ exchange-0001.sql \ + exchange-0002.sql \ drop.sql \ procedures.sql BUILT_SOURCES = \ - exchange-0001.sql \ + benchmark-0001.sql \ drop.sql \ + exchange-0001.sql \ procedures.sql CLEANFILES = \ - exchange-0001.sql \ - exchange-0002.sql + exchange-0002.sql \ + exchange-0003.sql -exchange-0001.sql: common-0001.sql exchange-0001-part.sql exchange-0001.sql.in +exchange-0002.sql: exchange-0002.sql.in 0002-*.sql chmod +w $@ || true - gcc -E -P -undef - < exchange-0001.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ -exchange-0002.sql: common-0002.sql exchange-0002-part.sql exchange-0002.sql.in +exchange-0003.sql: exchange-0003.sql.in 0003-*.sql chmod +w $@ || true - gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + gcc -E -P -undef - < exchange-0003.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ EXTRA_DIST = \ diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql deleted file mode 100644 index 3c2a850d..00000000 --- a/src/exchangedb/common-0001.sql +++ /dev/null @@ -1,2954 +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 --- - --------------------- Tables ---------------------------- - -CREATE OR REPLACE 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 - ,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( - table_definition, - table_name, - main_table_partition_str - ); - -END -$$; - ------------------------ wire_targets --------------------------- - -CREATE OR REPLACE FUNCTION create_table_wire_targets( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -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_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' - ',payto_uri VARCHAR NOT NULL' - ') %s ;' - ,'wire_targets' - ,'PARTITION BY HASH (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( - 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 ' - 'UNIQUE (wire_target_serial_id)' - ); -END -$$; - - ------------------------ legitimization_processes --------------------------- - -CREATE OR REPLACE FUNCTION create_table_legitimization_processes( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' - ',expiration_time INT8 NOT NULL DEFAULT (0)' - ',provider_section VARCHAR NOT NULL' - ',provider_user_id VARCHAR DEFAULT NULL' - ',provider_legitimization_id VARCHAR DEFAULT NULL' - ',UNIQUE (h_payto, provider_section)' - ') %s ;' - ,'legitimization_processes' - ,'PARTITION BY HASH (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_legitimization_processes_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - partition_name VARCHAR; -BEGIN - - partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); - - EXECUTE FORMAT ( - 'ALTER TABLE ' || partition_name - || ' ' - 'ADD CONSTRAINT ' || partition_name || '_serial_key ' - 'UNIQUE (legitimization_process_serial_id)'); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index ' - 'ON '|| partition_name || ' ' - '(provider_section,provider_legitimization_id)' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index ' - 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';' - ); -END -$$; - - ------------------------ legitimization_requirements --------------------------- - -CREATE OR REPLACE FUNCTION create_table_legitimization_requirements( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' - ',required_checks VARCHAR NOT NULL' - ',UNIQUE (h_payto, required_checks)' - ') %s ;' - ,'legitimization_requirements' - ,'PARTITION BY HASH (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_legitimization_requirements_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - partition_name VARCHAR; -BEGIN - - partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); - - EXECUTE FORMAT ( - 'ALTER TABLE ' || partition_name - || ' ' - 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' - 'UNIQUE (legitimization_requirement_serial_id)'); -END -$$; - - ------------------------- reserves ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'reserves'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' - ',current_balance_val INT8 NOT NULL DEFAULT(0)' - ',current_balance_frac INT4 NOT NULL DEFAULT(0)' - ',purses_active INT8 NOT NULL DEFAULT(0)' - ',purses_allowed INT8 NOT NULL DEFAULT(0)' - ',max_age INT4 NOT NULL DEFAULT(120)' - ',expiration_date INT8 NOT NULL' - ',gc_date INT8 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' - 'ON ' || table_name || ' ' - '(expiration_date' - ',current_balance_val' - ',current_balance_frac' - ');' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' - 'IS ' || quote_literal('used in get_expired_reserves') || ';' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' - 'ON ' || table_name || ' ' - '(reserve_uuid);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' - 'ON ' || table_name || ' ' - '(gc_date);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' - 'IS ' || quote_literal('for reserve garbage collection') || ';' - ); - -END -$$; - ------------------------ reserves_in ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_reserves_in( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'reserves_in'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' - ',wire_reference INT8 NOT NULL' - ',credit_val INT8 NOT NULL' - ',credit_frac INT4 NOT NULL' - ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' - ',exchange_account_section TEXT NOT NULL' - ',execution_date INT8 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' - 'ON ' || table_name || ' ' - '(reserve_in_serial_id);' - ); - -- FIXME: where do we need this index? Can we do better? - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' - 'ON ' || table_name || ' ' - '(exchange_account_section ' - ',execution_date' - ');' - ); - -- FIXME: where do we need this index? Can we do better? - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' - 'ON ' || table_name || ' ' - '(exchange_account_section,' - 'reserve_in_serial_id DESC' - ');' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_in_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' - 'UNIQUE (reserve_in_serial_id)' - ); -END -$$; - ---------------------------- reserves_close ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_close( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'reserves_close'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' - ',execution_date INT8 NOT NULL' - ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',closing_fee_val INT8 NOT NULL' - ',closing_fee_frac INT4 NOT NULL' - ',close_request_row INT8 NOT NULL DEFAULT(0)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' - 'ON ' || table_name || ' ' - '(close_uuid);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_close_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' - 'PRIMARY KEY (close_uuid)' - ); -END -$$; - - ---------------------------- close_requests --------------------------- - -CREATE OR REPLACE FUNCTION create_table_close_requests( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'close_requests'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE - ',close_timestamp INT8 NOT NULL' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',close_val INT8 NOT NULL' - ',close_frac INT4 NOT NULL' - ',close_fee_val INT8 NOT NULL' - ',close_fee_frac INT4 NOT NULL' - ',payto_uri VARCHAR NOT NULL' - ',done BOOL NOT NULL DEFAULT(FALSE)' - ',PRIMARY KEY (reserve_pub,close_timestamp)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); -END -$$; - - -CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'close_requests'; -BEGIN - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' - 'ON ' || table_name || ' ' - '(close_request_serial_id);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_done_index ' - 'ON ' || table_name || ' ' - '(done);' - ); -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE close_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT close_requests_' || partition_suffix || '_close_request_uuid_pkey ' - 'UNIQUE (close_request_serial_id)' - ); -END -$$; - - ---------------------------- reserves_open_requests ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'reserves_open_requests'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' - ',request_timestamp INT8 NOT NULL' - ',expiration_date INT8 NOT NULL' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',reserve_payment_val INT8 NOT NULL' - ',reserve_payment_frac INT4 NOT NULL' - ',requested_purse_limit INT4 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' - 'ON ' || table_name || ' ' - '(open_request_uuid);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' - 'PRIMARY KEY (open_request_uuid),' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' - 'UNIQUE (reserve_pub,request_timestamp)' - ); -END -$$; - - ---------------------------- reserves_open_deposits ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'reserves_open_deposits'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' - ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' - ',contribution_val INT8 NOT NULL' - ',contribution_frac INT4 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid ' - 'ON ' || table_name || ' ' - '(reserve_open_deposit_uuid);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' - 'PRIMARY KEY (coin_pub,coin_sig)' - ); -END -$$; - - ----------------------------- reserves_out ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_out( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'reserves_out'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' - ',denom_sig BYTEA NOT NULL' - ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',execution_date INT8 NOT NULL' - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ') %s ;' - ,'reserves_out' - ,'PARTITION BY HASH (h_blind_ev)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' - 'ON ' || table_name || ' ' - '(reserve_out_serial_id);' - ); - -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' - 'ON ' || table_name || ' ' - '(reserve_uuid, execution_date);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' - 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' - ); - -END -$$; - - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' - 'UNIQUE (reserve_out_serial_id)' - ); -END -$$; - -CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE - ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' - ') %s ' - ,table_name - ,'PARTITION BY HASH (reserve_uuid)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(reserve_uuid);' - ); - -END -$$; - ----------------------------- known_coins ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_known_coins( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR default 'known_coins'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' - ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' - ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' - ',denom_sig BYTEA NOT NULL' - ',remaining_val INT8 NOT NULL DEFAULT(0)' - ',remaining_frac INT4 NOT NULL DEFAULT(0)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE known_coins_' || partition_suffix || ' ' - 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' - 'UNIQUE (known_coin_id)' - ); -END -$$; - ----------------------------- refresh_commitments ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_refresh_commitments( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'refresh_commitments'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' - ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' - ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',noreveal_index INT4 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (rc)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - -- Note: index spans partitions, may need to be materialized. - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' - 'ON ' || table_name || ' ' - '(old_coin_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' - 'UNIQUE (melt_serial_id)' - ); -END -$$; - ------------------------------- refresh_revealed_coins -------------------------------- - -CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'refresh_revealed_coins'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' - ',freshcoin_index INT4 NOT NULL' - ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' - ',coin_ev BYTEA NOT NULL' -- UNIQUE' - ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' - ',ev_sig BYTEA NOT NULL' - ',ewv BYTEA NOT NULL' - -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard - ') %s ;' - ,table_name - ,'PARTITION BY HASH (melt_serial_id)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' - 'ON ' || table_name || ' ' - '(melt_serial_id);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' - 'UNIQUE (rrc_serial) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' - 'UNIQUE (coin_ev) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' - 'UNIQUE (h_coin_ev) ' - ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' - ); -END -$$; - ------------------------------ refresh_transfer_keys ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'refresh_transfer_keys'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' - ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' - ',transfer_privs BYTEA NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (melt_serial_id)' - ,shard_suffix - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' - 'UNIQUE (rtc_serial)' - ); -END -$$; - ----------------------------- deposits ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_deposits( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' - ',shard INT8 NOT NULL' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',wallet_timestamp INT8 NOT NULL' - ',exchange_timestamp INT8 NOT NULL' - ',refund_deadline INT8 NOT NULL' - ',wire_deadline INT8 NOT NULL' - ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' - ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' - ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' - ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',done BOOLEAN NOT NULL DEFAULT FALSE' - ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' - ',policy_details_serial_id INT8' -- REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' - 'ON ' || table_name || ' ' - '(coin_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' - 'PRIMARY KEY (deposit_serial_id) ' - ',ADD CONSTRAINT deposits_' || partition_suffix || '_coin_pub_merchant_pub_h_contract_terms_key ' - 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)' - ); -END -$$; - -CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_by_ready'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(wire_deadline INT8 NOT NULL' - ',shard INT8 NOT NULL' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' - ',deposit_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY RANGE (wire_deadline)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(wire_deadline ASC, shard ASC, coin_pub);' - ); - -END -$$; - - -CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_for_matching'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(refund_deadline INT8 NOT NULL' - ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ',deposit_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY RANGE (refund_deadline)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(refund_deadline ASC, merchant_pub, coin_pub);' - ); - -END -$$; - ----------------------------- wire_out ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_wire_out( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'wire_out'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' - ',execution_date INT8 NOT NULL' - ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',exchange_account_section TEXT NOT NULL' - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (wtid_raw)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' - 'ON ' || table_name || ' ' - '(wire_target_h_payto);' - ); - - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wire_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' - 'PRIMARY KEY (wireout_uuid)' - ); -END -$$; - ----------------------------- aggregation_transient ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_aggregation_transient( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aggregation_transient'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' - ',exchange_account_section TEXT NOT NULL' - ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' - ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (wire_target_h_payto)' - ,shard_suffix - ); - -END -$$; - ----------------------------- aggregation_tracking ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'aggregation_tracking'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient depost -- or something else ??? - ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (deposit_serial_id)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' - 'ON ' || table_name || ' ' - '(wtid_raw);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' - 'IS ' || quote_literal('for lookup_transactions') || ';' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' - 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' - 'UNIQUE (aggregation_serial_id) ' - ); -END -$$; - ------------------------------ recoup ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_recoup( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'recoup'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) - ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' - ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',recoup_timestamp INT8 NOT NULL' - ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub);' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' - 'ON ' || table_name || ' ' - '(coin_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE recoup_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' - 'UNIQUE (recoup_uuid) ' - ); -END -$$; - -CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'recoup_by_reserve'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE - ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_out_serial_id)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(reserve_out_serial_id);' - ); - -END -$$; - ----------------------------- recoup_refresh ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_recoup_refresh( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'recoup_refresh'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) - ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE - ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' - ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',recoup_timestamp INT8 NOT NULL' - ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: any query using this index will be slow. Materialize index or change query? - -- Also: which query uses this index? - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' - 'ON ' || table_name || ' ' - '(rrc_serial);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' - 'ON ' || table_name || ' ' - '(coin_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' - 'UNIQUE (recoup_refresh_uuid) ' - ); -END -$$; - ------------------------------ prewire ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_prewire( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'prewire'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' - ',wire_method TEXT NOT NULL' - ',finished BOOLEAN NOT NULL DEFAULT false' - ',failed BOOLEAN NOT NULL DEFAULT false' - ',buf BYTEA NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (prewire_uuid)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' - 'ON ' || table_name || ' ' - '(finished);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' - 'IS ' || quote_literal('for gc_prewire') || ';' - ); - -- FIXME: find a way to combine these two indices? - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' - 'ON ' || table_name || ' ' - '(failed,finished);' - ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' - 'IS ' || quote_literal('for wire_prepare_data_get') || ';' - ); - -END -$$; - ------------------------------ cs_nonce_locks ------------------------------ - -CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( - shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' - ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' - ',max_denomination_serial INT8 NOT NULL' - ') %s ;' - ,'cs_nonce_locks' - ,'PARTITION BY HASH (nonce)' - ,shard_suffix - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' - 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' - 'UNIQUE (cs_nonce_lock_serial_id)' - ); -END -$$; - --------------------------------------------------------------------------- --- Tables for P2P payments --------------------------------------------------------------------------- - -------------------------------- purse_requests ---------------------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_requests( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_requests'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' - ',purse_creation INT8 NOT NULL' - ',purse_expiration INT8 NOT NULL' - ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' - ',age_limit INT4 NOT NULL' - ',flags INT4 NOT NULL' - ',in_reserve_quota BOOLEAN NOT NULL DEFAULT(FALSE)' - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',purse_fee_val INT8 NOT NULL' - ',purse_fee_frac INT4 NOT NULL' - ',balance_val INT8 NOT NULL DEFAULT (0)' - ',balance_frac INT4 NOT NULL DEFAULT (0)' - ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)' - ',PRIMARY KEY (purse_pub)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by merge_pub! - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' - 'ON ' || table_name || ' ' - '(merge_pub);' - ); - - -- FIXME: drop index on master (crosses shards)? - -- Or use materialized index? (needed?) - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' - 'ON ' || table_name || ' ' - '(purse_expiration);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' - 'UNIQUE (purse_requests_serial_id) ' - ); -END -$$; - - ----------------------------- purse_merges ----------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_merges( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_merges'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY '-- UNIQUE - ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)'--REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE - ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' - ',merge_timestamp INT8 NOT NULL' - ',PRIMARY KEY (purse_pub)' - ') %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_purse_merges_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_merges_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' - 'UNIQUE (purse_merge_request_serial_id) ' - ); -END -$$; - -------------------------- account_merges ---------------------------- - -CREATE OR REPLACE FUNCTION create_table_account_merges( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'account_merges'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' -- REFERENCES purse_requests (purse_pub) - ',wallet_h_payto BYTEA NOT NULL CHECK (LENGTH(wallet_h_payto)=32)' - ',PRIMARY KEY (purse_pub)' - ') %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 || '_by_reserve_pub ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE account_merges_' || partition_suffix || ' ' - 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' - 'UNIQUE (account_merge_request_serial_id) ' - ); -END -$$; - - -------------------------------- purse_decision ---------------------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_decision( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_decision'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',action_timestamp INT8 NOT NULL' - ',refunded BOOL NOT NULL' - ',PRIMARY KEY (purse_pub)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_decision_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key ' - 'UNIQUE (purse_decision_serial_id) ' - ); -END -$$; - - -------------------------- contracts ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_contracts( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'contracts'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' - ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' - ',e_contract BYTEA NOT NULL' - ',purse_expiration INT8 NOT NULL' - ',PRIMARY KEY (purse_pub)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE contracts_' || partition_suffix || ' ' - 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' - 'UNIQUE (contract_serial_id) ' - ); -END -$$; - ---------------------------- history_requests -------------------------- - - -CREATE OR REPLACE FUNCTION create_table_history_requests( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'history_requests'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE - ',request_timestamp INT8 NOT NULL' - ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',history_fee_val INT8 NOT NULL' - ',history_fee_frac INT4 NOT NULL' - ',PRIMARY KEY (reserve_pub,request_timestamp)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix - ); - -END -$$; - -------------------------------- purse_deposits ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_deposits( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'purse_deposits'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE - ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE' - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' - ',PRIMARY KEY (purse_pub,coin_pub)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix - ); - - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by coin_pub! - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' - 'ON ' || table_name || ' ' - '(coin_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' - 'UNIQUE (purse_deposit_serial_id) ' - ); -END -$$; - ----------------------------- wads_out ------------------------------- -CREATE OR REPLACE FUNCTION create_table_wads_out( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'wads_out'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' - ',partner_serial_id INT8 NOT NULL' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',execution_time INT8 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (wad_id)' - ,shard_suffix - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wads_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' - 'UNIQUE (wad_out_serial_id) ' - ); -END -$$; - ---------------------------- wad_out_entries -------------------------- - -CREATE OR REPLACE FUNCTION create_table_wad_out_entries( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'wad_out_entries'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_out_serial_id INT8' -- REFERENCES wads_out (wad_out_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 || '_by_reserve_pub ' - 'ON ' || table_name || ' ' - '(reserve_pub);' - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' - 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' - 'UNIQUE (wad_out_entry_serial_id) ' - ); -END -$$; - --------------------------- wads_in -------------------------------- - -CREATE OR REPLACE FUNCTION create_table_wads_in( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'wads_in'; -BEGIN - - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' - ',origin_exchange_url TEXT NOT NULL' - ',amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',arrival_time INT8 NOT NULL' - ',UNIQUE (wad_id, origin_exchange_url)' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (wad_id)' - ,shard_suffix - ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wads_in_' || partition_suffix || ' ' - 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' - 'UNIQUE (wad_in_serial_id) ' - ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' - 'UNIQUE (wad_id, origin_exchange_url) ' - ); -END -$$; - - -------------------------- wads_in_entries -------------------------- - -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 -$$; - -------------------------------------------------------------------- -------------------------- 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 -$$; - ---------------------- Sharding --------------------------- - -CREATE OR REPLACE FUNCTION create_foreign_hash_partition( - source_table_name VARCHAR - ,modulus INTEGER - ,shard_suffix VARCHAR - ,current_shard_num INTEGER - ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' - ) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, shard_suffix; - - EXECUTE FORMAT( - 'CREATE FOREIGN TABLE IF NOT EXISTS %I ' - 'PARTITION OF %I ' - 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) ' - 'SERVER %I' - ,source_table_name || '_' || shard_suffix - ,source_table_name - ,modulus - ,current_shard_num-1 - ,shard_suffix - ); - - EXECUTE FORMAT( - 'ALTER FOREIGN TABLE %I OWNER TO %I' - ,source_table_name || '_' || shard_suffix - ,local_user - ); - -END -$$; - -CREATE OR REPLACE FUNCTION create_foreign_range_partition( - source_table_name VARCHAR - ,partition_num INTEGER -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - RAISE NOTICE 'TODO'; -END -$$; - -CREATE OR REPLACE FUNCTION prepare_sharding() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - CREATE EXTENSION IF NOT EXISTS postgres_fdw; - - PERFORM detach_default_partitions(); - - ALTER TABLE IF EXISTS wire_targets - DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE - ; - - ALTER TABLE IF EXISTS reserves - DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE - ; - - ALTER TABLE IF EXISTS reserves_in - DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE - ; - - ALTER TABLE IF EXISTS reserves_close - DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE - ; - - ALTER TABLE IF EXISTS reserves_out - DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey - ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key - ; - - ALTER TABLE IF EXISTS known_coins - DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey - ; - - ALTER TABLE IF EXISTS refresh_commitments - DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey - ; - - ALTER TABLE IF EXISTS refresh_revealed_coins - DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey - ; - - ALTER TABLE IF EXISTS refresh_transfer_keys - DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE - ; - - ALTER TABLE IF EXISTS deposits - DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS deposits_policy_details_serial_id_fkey - ,DROP CONSTRAINT IF EXISTS deposits_coin_pub_merchant_pub_h_contract_terms_key CASCADE - ; - - ALTER TABLE IF EXISTS refunds - DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wire_out - DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE - ; - - ALTER TABLE IF EXISTS aggregation_tracking - DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey - ; - - ALTER TABLE IF EXISTS recoup - DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE - ; - - ALTER TABLE IF EXISTS recoup_refresh - DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE - ; - - ALTER TABLE IF EXISTS prewire - DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE - ; - - ALTER TABLE IF EXISTS cs_nonce_locks - DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_requests - DROP CONSTRAINT IF EXISTS purse_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_decision - DROP CONSTRAINT IF EXISTS purse_decision_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_merges - DROP CONSTRAINT IF EXISTS purse_merges_pkey CASCADE - ; - - ALTER TABLE IF EXISTS account_merges - DROP CONSTRAINT IF EXISTS account_merges_pkey CASCADE - ; - - ALTER TABLE IF EXISTS contracts - DROP CONSTRAINT IF EXISTS contracts_pkey CASCADE - ; - - ALTER TABLE IF EXISTS history_requests - DROP CONSTRAINT IF EXISTS history_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS close_requests - DROP CONSTRAINT IF EXISTS close_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_deposits - DROP CONSTRAINT IF EXISTS purse_deposits_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wads_out - DROP CONSTRAINT IF EXISTS wads_out_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wad_out_entries - DROP CONSTRAINT IF EXISTS wad_out_entries_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wads_in - DROP CONSTRAINT IF EXISTS wads_in_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS wads_in_wad_id_origin_exchange_url_key - ; - - ALTER TABLE IF EXISTS wad_in_entries - DROP CONSTRAINT IF EXISTS wad_in_entries_pkey CASCADE - ; - -END -$$; - - -CREATE OR REPLACE FUNCTION create_shard_server( - shard_suffix VARCHAR - ,total_num_shards INTEGER - ,current_shard_num INTEGER - ,remote_host VARCHAR - ,remote_user VARCHAR - ,remote_user_password VARCHAR - ,remote_db_name VARCHAR DEFAULT 'taler-exchange' - ,remote_port INTEGER DEFAULT '5432' - ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Creating server %', remote_host; - - EXECUTE FORMAT( - 'CREATE SERVER IF NOT EXISTS %I ' - 'FOREIGN DATA WRAPPER postgres_fdw ' - 'OPTIONS (dbname %L, host %L, port %L)' - ,shard_suffix - ,remote_db_name - ,remote_host - ,remote_port - ); - - EXECUTE FORMAT( - 'CREATE USER MAPPING IF NOT EXISTS ' - 'FOR %I SERVER %I ' - 'OPTIONS (user %L, password %L)' - ,local_user - ,shard_suffix - ,remote_user - ,remote_user_password - ); - - EXECUTE FORMAT( - 'GRANT ALL PRIVILEGES ' - 'ON FOREIGN SERVER %I ' - 'TO %I;' - ,shard_suffix - ,local_user - ); - - PERFORM create_foreign_hash_partition( - 'wire_targets' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_in' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_out' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_out_by_reserve' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_close' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'history_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'close_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'open_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'known_coins' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_commitments' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_revealed_coins' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_transfer_keys' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'deposits' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); --- PERFORM create_foreign_range_partition( --- 'deposits_by_ready' --- ,total_num_shards --- ,shard_suffix --- ,current_shard_num --- ,local_user --- ); --- PERFORM create_foreign_range_partition( --- 'deposits_for_matching' --- ,total_num_shards --- ,shard_suffix --- ,current_shard_num --- ,local_user --- ); - PERFORM create_foreign_hash_partition( - 'refunds' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wire_out' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'aggregation_transient' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'aggregation_tracking' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup_by_reserve' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup_refresh' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'prewire' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'cs_nonce_locks' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - - ------------------- P2P -------------------- - - PERFORM create_foreign_hash_partition( - 'purse_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'purse_decision' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'purse_merges' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'account_merges' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'contracts' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - - PERFORM create_foreign_hash_partition( - 'purse_deposits' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wad_out_entries' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wads_in' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wad_in_entries' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - -END -$$; - -COMMENT ON FUNCTION create_shard_server - IS 'Create a shard server on the master - node with all foreign tables and user mappings'; - -CREATE OR REPLACE FUNCTION create_foreign_servers( - amount INTEGER - ,domain VARCHAR - ,remote_user VARCHAR DEFAULT 'taler' - ,remote_user_password VARCHAR DEFAULT 'taler' -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM prepare_sharding(); - - FOR i IN 1..amount LOOP - PERFORM create_shard_server( - i::varchar - ,amount - ,i - ,'shard-' || i::varchar || '.' || domain - ,remote_user - ,remote_user_password - ,'taler-exchange' - ,'5432' - ,'taler-exchange-httpd' - ); - END LOOP; - - PERFORM drop_default_partitions(); - -END -$$; diff --git a/src/exchangedb/common-0002.sql b/src/exchangedb/common-0002.sql index 388d5547..649efa11 100644 --- a/src/exchangedb/common-0002.sql +++ b/src/exchangedb/common-0002.sql @@ -16,27 +16,6 @@ -------------------------------- -CREATE TABLE IF NOT EXISTS exchange_tables - (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY - ,name VARCHAR NOT NULL - ,version VARCHAR NOT NULL - ,action VARCHAR NOT NULL - ,partitioned BOOL NOT NULL - ,by_range BOOL NOT NULL); -COMMENT ON TABLE exchange_tables - IS 'Tables of the exchange and their status'; -COMMENT ON COLUMN exchange_tables.name - IS 'Base name of the table (without partition/shard)'; -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)'; -COMMENT ON COLUMN exchange_tables.partitioned - IS 'TRUE if the table is partitioned'; -COMMENT ON COLUMN exchange_tables.by_range - IS 'TRUE if the table is partitioned by range'; - - INSERT INTO exchange_tables (name ,version @@ -44,10 +23,6 @@ INSERT INTO exchange_tables ,partitioned ,by_range) VALUES - ('denominations', 'exchange-0001', 'create', FALSE, FALSE) - ,('denomination_revocations', 'exchange-0001', 'create', FALSE, FALSE) - ,('kyc_alerts', 'exchange-0001', 'create', FALSE, FALSE) - ,('profit_drains', 'exchange-0001', 'create', FALSE, FALSE) ,('auditors', 'exchange-0001', 'create', FALSE, FALSE) ,('auditor_denom_sigs', 'exchange-0001', 'create', FALSE, FALSE) ,('exchange_sign_keys', 'exchange-0001', 'create', FALSE, FALSE) @@ -106,82 +81,3 @@ INSERT INTO exchange_tables -------------------- Tables ---------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_deletion( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' - ',XXX VARCHAR NOT NULL' - ') %s ;' - ,'purse_deletion' - ,'PARTITION BY HASH (XXX)' - ,shard_suffix - ); - COMMENT ON TABLE purse_deletion - IS 'signatures affirming explicit purse deletions'; - COMMENT ON COLUMN purse_deletion.purse_sig - IS 'signature of type XXX'; -END -$$; -COMMENT ON FUNCTION create_table_purse_deletion - IS 'Creates the purse_deletion table'; - -CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_deletion_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_deletion_' || partition_suffix || '_XXX ' - 'UNIQUE (XXX)' - ); -END -$$; - -CREATE OR REPLACE FUNCTION alter_create_was_deleted_table_purse_requests ( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_requests_' || partition_suffix || - ' ADD COLUMN' - ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' - ); -END -$$; - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('purse_deletion' - ,'exchange-0002' - ,'create' - ,TRUE - ,FALSE), - ('purse_deletion' - ,'exchange-0002' - ,'constrain' - ,TRUE - ,FALSE), - ('purse_requests' - ,'exchange-0002' - ,'alter_create_was_deleted' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index 59e684e1..7855c996 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -14,68 +14,9 @@ -- TALER; see the file COPYING. If not, see -- --- ------------------------------ denominations ---------------------------------------- --- ------------------------------ denomination_revocations ---------------------------------------- - -CREATE TABLE IF NOT EXISTS denomination_revocations - (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ); -COMMENT ON TABLE denomination_revocations - IS 'remembering which denomination keys have been revoked'; - - - --- -------------------------- kyc_alerts ---------------------------------------- - -CREATE TABLE IF NOT EXISTS kyc_alerts - (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) - ,trigger_type INT4 NOT NULL - ,UNIQUE(trigger_type,h_payto) - ); -COMMENT ON TABLE kyc_alerts - IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; -COMMENT ON COLUMN kyc_alerts.h_payto - IS 'hash of the payto://-URI for which the KYC status changed'; -COMMENT ON COLUMN kyc_alerts.trigger_type - IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; - - --- ------------------------------ profit drains ---------------------------------------- - -CREATE TABLE IF NOT EXISTS profit_drains - (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) - ,account_section VARCHAR NOT NULL - ,payto_uri VARCHAR NOT NULL - ,trigger_date INT8 NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ,executed BOOLEAN NOT NULL DEFAULT FALSE - ); -COMMENT ON TABLE profit_drains - IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; -COMMENT ON COLUMN profit_drains.wtid - IS 'randomly chosen nonce, unique to prevent double-submission'; -COMMENT ON COLUMN profit_drains.account_section - IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; -COMMENT ON COLUMN profit_drains.payto_uri - IS 'specifies the account to be credited'; -COMMENT ON COLUMN profit_drains.trigger_date - IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; -COMMENT ON COLUMN profit_drains.amount_val - IS 'amount to be transferred'; -COMMENT ON COLUMN profit_drains.master_sig - IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; -COMMENT ON COLUMN profit_drains.executed - IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; - - -- ------------------------------ wire_targets ---------------------------------------- SELECT create_table_wire_targets(); @@ -87,6 +28,7 @@ COMMENT ON COLUMN wire_targets.payto_uri 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); @@ -1423,66 +1365,4 @@ SELECT add_constraints_to_wad_in_entries_partition('default'); -- ------------------------------ partner_accounts ---------------------------------------- -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.'; - - ----------------------- router helper table (not synchronzied) ------------------------ - -CREATE TABLE IF NOT EXISTS purse_actions - (purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32) - ,action_date INT8 NOT NULL - ,partner_serial_id INT8 - ); -COMMENT ON TABLE purse_actions - IS 'purses awaiting some action by the router'; -COMMENT ON COLUMN purse_actions.purse_pub - IS 'public (contract) key of the purse'; -COMMENT ON COLUMN purse_actions.action_date - IS 'when is the purse ready for action'; -COMMENT ON COLUMN purse_actions.partner_serial_id - IS 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown'; - -CREATE INDEX IF NOT EXISTS purse_action_by_target - ON purse_actions - (partner_serial_id,action_date); - - -CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -BEGIN - INSERT INTO - purse_actions - (purse_pub - ,action_date) - VALUES - (NEW.purse_pub - ,NEW.purse_expiration); - RETURN NEW; -END $$; -COMMENT ON FUNCTION purse_requests_insert_trigger() - IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; - -CREATE TRIGGER purse_requests_on_insert - AFTER INSERT - ON purse_requests - FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); -COMMENT ON TRIGGER purse_requests_on_insert - ON purse_requests - IS 'Here we install an entry for the purse expiration.'; diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql new file mode 100644 index 00000000..6998320c --- /dev/null +++ b/src/exchangedb/exchange-0001.sql @@ -0,0 +1,783 @@ +-- +-- 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 +-- + +-- Everything in one big transaction +BEGIN; + +SET search_path TO exchange; + +--------------------------------------------------------------------------- +-- General procedures for DB setup +--------------------------------------------------------------------------- + +CREATE TABLE IF NOT EXISTS exchange_tables + (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,name VARCHAR NOT NULL + ,version VARCHAR NOT NULL + ,action VARCHAR NOT NULL + ,partitioned BOOL NOT NULL + ,by_range BOOL NOT NULL + ,finished BOOL NOT NULL DEFAULT(FALSE)); +COMMENT ON TABLE exchange_tables + IS 'Tables of the exchange and their status'; +COMMENT ON COLUMN exchange_tables.name + IS 'Base name of the table (without partition/shard)'; +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)'; +COMMENT ON COLUMN exchange_tables.partitioned + IS 'TRUE if the table is partitioned'; +COMMENT ON COLUMN exchange_tables.by_range + IS 'TRUE if the table is partitioned by range'; +COMMENT ON COLUMN exchange_tables.finished + IS 'TRUE if the respective migration has been run'; + + + +CREATE OR REPLACE 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 + ,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( + table_definition, + table_name, + main_table_partition_str + ); + +END +$$; + + + + + +CREATE OR REPLACE FUNCTION create_tables( + num_partitions INTEGER + ,shard_domain VARCHAR +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,by_range + FROM exchange_tables + WHERE NOT finished + ORDER BY table_serial_id ASC; +BEGIN + FOR rec IN tc + LOOP + -- First create the master table, either + -- completely unpartitioned, or with one + -- master and the 'default' partition + IF IS NULL num_partitions + THEN + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + ELSE + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,0 + ); + END IF + + IF NOT NULL shard_domain + THEN + -- FIXME: attach shards! + -- FIXME: how will migration work with shards!? + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_XXX_%s (%s)'::text + ,rec.action + ,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 + + IF 0 < num_partitions + THEN + -- FIXME: detach default partition! + END IF + + 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 + LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)' + ,rec.action + ,rec.name + ,shard_idx::varchar + ); + 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 +$$; diff --git a/src/exchangedb/exchange-0001.sql.in b/src/exchangedb/exchange-0001.sql.in deleted file mode 100644 index a01ac3a8..00000000 --- a/src/exchangedb/exchange-0001.sql.in +++ /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 --- - --- Everything in one big transaction -BEGIN; - --- Check patch versioning is in place. -SELECT _v.register_patch('exchange-0001', NULL, NULL); - --------------------- Schema ---------------------------- - -CREATE SCHEMA exchange; -COMMENT ON SCHEMA exchange IS 'taler-exchange data'; - -SET search_path TO exchange; - -#include "common-0001.sql" -#include "exchange-0001-part.sql" - -COMMIT; diff --git a/src/exchangedb/exchange-0002-part.sql b/src/exchangedb/exchange-0002-part.sql deleted file mode 100644 index 2381d8b2..00000000 --- a/src/exchangedb/exchange-0002-part.sql +++ /dev/null @@ -1,36 +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 --- - --- ------------------------------ 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'); - - - -SELECT create_tables('exchange-0002'); diff --git a/src/exchangedb/exchange-0002.sql b/src/exchangedb/exchange-0002.sql new file mode 100644 index 00000000..600090bc --- /dev/null +++ b/src/exchangedb/exchange-0002.sql @@ -0,0 +1,1839 @@ +BEGIN; +SELECT _v.register_patch('exchange-0002', NULL, NULL); +SET search_path TO exchange; +CREATE OR REPLACE FUNCTION create_table_denominations() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN +CREATE TABLE IF NOT EXISTS denominations + (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) + ,denom_type INT4 NOT NULL DEFAULT (1) + ,age_mask INT4 NOT NULL DEFAULT (0) + ,denom_pub BYTEA NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,valid_from INT8 NOT NULL + ,expire_withdraw INT8 NOT NULL + ,expire_deposit INT8 NOT NULL + ,expire_legal INT8 NOT NULL + ,coin_val INT8 NOT NULL + ,coin_frac INT4 NOT NULL + ,fee_withdraw_val INT8 NOT NULL + ,fee_withdraw_frac INT4 NOT NULL + ,fee_deposit_val INT8 NOT NULL + ,fee_deposit_frac INT4 NOT NULL + ,fee_refresh_val INT8 NOT NULL + ,fee_refresh_frac INT4 NOT NULL + ,fee_refund_val INT8 NOT NULL + ,fee_refund_frac INT4 NOT NULL + ); +COMMENT ON TABLE denominations + IS 'Main denominations table. All the valid denominations the exchange knows about.'; +COMMENT ON COLUMN denominations.denom_type + IS 'determines cipher type for blind signatures used with this denomination; 0 is for RSA'; +COMMENT ON COLUMN denominations.age_mask + IS 'bitmask with the age restrictions that are being used for this denomination; 0 if denomination does not support the use of age restrictions'; +COMMENT ON COLUMN denominations.denominations_serial + IS 'needed for exchange-auditor replication logic'; +CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index + ON denominations + (expire_legal); +END +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denominations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +CREATE TABLE IF NOT EXISTS denomination_revocations + (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ); +COMMENT ON TABLE denomination_revocations + IS 'remembering which denomination keys have been revoked'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denomination_revocations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +CREATE OR REPLACE FUNCTION create_table_wire_targets( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(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 ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( + 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 ' + 'UNIQUE (wire_target_serial_id)' + ); +END +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_targets' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE); +CREATE TABLE IF NOT EXISTS kyc_alerts + (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) + ,trigger_type INT4 NOT NULL + ,UNIQUE(trigger_type,h_payto) + ); +COMMENT ON TABLE kyc_alerts + IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; +COMMENT ON COLUMN kyc_alerts.h_payto + IS 'hash of the payto://-URI for which the KYC status changed'; +COMMENT ON COLUMN kyc_alerts.trigger_type + IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_alerts' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +CREATE TABLE IF NOT EXISTS profit_drains + (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) + ,account_section VARCHAR NOT NULL + ,payto_uri VARCHAR NOT NULL + ,trigger_date INT8 NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,executed BOOLEAN NOT NULL DEFAULT FALSE + ); +COMMENT ON TABLE profit_drains + IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; +COMMENT ON COLUMN profit_drains.wtid + IS 'randomly chosen nonce, unique to prevent double-submission'; +COMMENT ON COLUMN profit_drains.account_section + IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; +COMMENT ON COLUMN profit_drains.payto_uri + IS 'specifies the account to be credited'; +COMMENT ON COLUMN profit_drains.trigger_date + IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; +COMMENT ON COLUMN profit_drains.amount_val + IS 'amount to be transferred'; +COMMENT ON COLUMN profit_drains.master_sig + IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; +COMMENT ON COLUMN profit_drains.executed + IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('profit_drains' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +CREATE OR REPLACE FUNCTION create_table_legitimization_processes( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',expiration_time INT8 NOT NULL DEFAULT (0)' + ',provider_section VARCHAR NOT NULL' + ',provider_user_id VARCHAR DEFAULT NULL' + ',provider_legitimization_id VARCHAR DEFAULT NULL' + ',UNIQUE (h_payto, provider_section)' + ') %s ;' + ,'legitimization_processes' + ,'PARTITION BY HASH (h_payto)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_legitimization_processes_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_key ' + 'UNIQUE (legitimization_process_serial_id)'); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index ' + 'ON '|| partition_name || ' ' + '(provider_section,provider_legitimization_id)' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index ' + 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_legitimization_requirements( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',required_checks VARCHAR NOT NULL' + ',UNIQUE (h_payto, required_checks)' + ') %s ;' + ,'legitimization_requirements' + ,'PARTITION BY HASH (h_payto)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_legitimization_requirements_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' + 'UNIQUE (legitimization_requirement_serial_id)'); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' + ',current_balance_val INT8 NOT NULL DEFAULT(0)' + ',current_balance_frac INT4 NOT NULL DEFAULT(0)' + ',purses_active INT8 NOT NULL DEFAULT(0)' + ',purses_allowed INT8 NOT NULL DEFAULT(0)' + ',max_age INT4 NOT NULL DEFAULT(120)' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'ON ' || table_name || ' ' + '(expiration_date' + ',current_balance_val' + ',current_balance_frac' + ');' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' + 'IS ' || quote_literal('used in get_expired_reserves') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'ON ' || table_name || ' ' + '(gc_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' + 'IS ' || quote_literal('for reserve garbage collection') || ';' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_in'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY' + ',wire_reference INT8 NOT NULL' + ',credit_val INT8 NOT NULL' + ',credit_frac INT4 NOT NULL' + ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_in_serial_id);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section ' + ',execution_date' + ');' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section,' + 'reserve_in_serial_id DESC' + ');' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' + 'UNIQUE (reserve_in_serial_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL' + ',execution_date INT8 NOT NULL' + ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ',close_request_row INT8 NOT NULL DEFAULT(0)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' + 'ON ' || table_name || ' ' + '(close_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' + 'PRIMARY KEY (close_uuid)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_close_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',close_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',close_val INT8 NOT NULL' + ',close_frac INT4 NOT NULL' + ',close_fee_val INT8 NOT NULL' + ',close_fee_frac INT4 NOT NULL' + ',payto_uri VARCHAR NOT NULL' + ',done BOOL NOT NULL DEFAULT(FALSE)' + ',PRIMARY KEY (reserve_pub,close_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' + 'ON ' || table_name || ' ' + '(close_request_serial_id);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_done_index ' + 'ON ' || table_name || ' ' + '(done);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE close_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT close_requests_' || partition_suffix || '_close_request_uuid_pkey ' + 'UNIQUE (close_request_serial_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_deposits'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',contribution_val INT8 NOT NULL' + ',contribution_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid ' + 'ON ' || table_name || ' ' + '(reserve_open_deposit_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' + 'PRIMARY KEY (coin_pub,coin_sig)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL' + ',request_timestamp INT8 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_payment_val INT8 NOT NULL' + ',reserve_payment_frac INT4 NOT NULL' + ',requested_purse_limit INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' + 'ON ' || table_name || ' ' + '(open_request_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'PRIMARY KEY (open_request_uuid),' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'UNIQUE (reserve_pub,request_timestamp)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' + ',denominations_serial INT8 NOT NULL' + ',denom_sig BYTEA NOT NULL' + ',reserve_uuid INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',execution_date INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,'reserves_out' + ,'PARTITION BY HASH (h_blind_ev)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid, execution_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid INT8 NOT NULL' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' + ') %s ' + ,table_name + ,'PARTITION BY HASH (reserve_uuid)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_known_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'known_coins'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',denominations_serial INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' + ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' + ',denom_sig BYTEA NOT NULL' + ',remaining_val INT8 NOT NULL DEFAULT(0)' + ',remaining_frac INT4 NOT NULL DEFAULT(0)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE known_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' + 'UNIQUE (known_coin_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_commitments'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' + ',old_coin_pub BYTEA NOT NULL' + ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',noreveal_index INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (rc)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' + 'ON ' || table_name || ' ' + '(old_coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' + 'UNIQUE (melt_serial_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',melt_serial_id INT8 NOT NULL' + ',freshcoin_index INT4 NOT NULL' + ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' + ',denominations_serial INT8 NOT NULL' + ',coin_ev BYTEA NOT NULL' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' + ',ev_sig BYTEA NOT NULL' + ',ewv BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'ON ' || table_name || ' ' + '(melt_serial_id);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' + 'UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' + 'UNIQUE (coin_ev) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' + 'UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_transfer_keys'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',melt_serial_id INT8 PRIMARY KEY' + ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' + ',transfer_privs BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' + 'UNIQUE (rtc_serial)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',known_coin_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wallet_timestamp INT8 NOT NULL' + ',exchange_timestamp INT8 NOT NULL' + ',refund_deadline INT8 NOT NULL' + ',wire_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',policy_details_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id) ' + ',ADD CONSTRAINT deposits_' || partition_suffix || '_coin_pub_merchant_pub_h_contract_terms_key ' + 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_by_ready'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_deadline INT8 NOT NULL' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (wire_deadline)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(wire_deadline ASC, shard ASC, coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_for_matching'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (refund_deadline)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(refund_deadline ASC, merchant_pub, coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8 NOT NULL' + ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' + ',rtransaction_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION constrain0002_table_refunds ( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refunds_' || partition_suffix || ' ' + 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' + 'UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); +END +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('refunds' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('refunds' + ,'exchange-0002' + ,'constrain0002' + ,TRUE + ,FALSE); +CREATE OR REPLACE FUNCTION create_table_wire_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wire_out'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',execution_date INT8 NOT NULL' + ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wtid_raw)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' + 'ON ' || table_name || ' ' + '(wire_target_h_payto);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wire_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' + 'PRIMARY KEY (wireout_uuid)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_aggregation_transient( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_transient'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' + ',exchange_account_section TEXT NOT NULL' + ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',deposit_serial_id INT8 PRIMARY KEY' + ',wtid_raw BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (deposit_serial_id)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'ON ' || table_name || ' ' + '(wtid_raw);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' + 'IS ' || quote_literal('for lookup_transactions') || ';' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' + 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' + 'UNIQUE (aggregation_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_recoup( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',reserve_out_serial_id INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub);' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' + 'UNIQUE (recoup_uuid) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id INT8 NOT NULL' + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_id)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',known_coin_id BIGINT NOT NULL' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',rrc_serial INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' + 'ON ' || table_name || ' ' + '(rrc_serial);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' + 'UNIQUE (recoup_refresh_uuid) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' + ',wire_method TEXT NOT NULL' + ',finished BOOLEAN NOT NULL DEFAULT false' + ',failed BOOLEAN NOT NULL DEFAULT false' + ',buf BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (prewire_uuid)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'ON ' || table_name || ' ' + '(finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' + 'IS ' || quote_literal('for gc_prewire') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'ON ' || table_name || ' ' + '(failed,finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' + 'IS ' || quote_literal('for wire_prepare_data_get') || ';' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( + shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' + ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' + ',max_denomination_serial INT8 NOT NULL' + ') %s ;' + ,'cs_nonce_locks' + ,'PARTITION BY HASH (nonce)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' + 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' + 'UNIQUE (cs_nonce_lock_serial_id)' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_purse_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' + ',purse_creation INT8 NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',age_limit INT4 NOT NULL' + ',flags INT4 NOT NULL' + ',in_reserve_quota BOOLEAN NOT NULL DEFAULT(FALSE)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',purse_fee_val INT8 NOT NULL' + ',purse_fee_frac INT4 NOT NULL' + ',balance_val INT8 NOT NULL DEFAULT (0)' + ',balance_frac INT4 NOT NULL DEFAULT (0)' + ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' + 'ON ' || table_name || ' ' + '(merge_pub);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' + 'ON ' || table_name || ' ' + '(purse_expiration);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' + 'UNIQUE (purse_requests_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_purse_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_merges'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY ' + ',partner_serial_id INT8' + ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' + ',merge_timestamp INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + 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_purse_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' + 'UNIQUE (purse_merge_request_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_account_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'account_merges'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',wallet_h_payto BYTEA NOT NULL CHECK (LENGTH(wallet_h_payto)=32)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE account_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' + 'UNIQUE (account_merge_request_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_purse_decision( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_decision'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',action_timestamp INT8 NOT NULL' + ',refunded BOOL NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_decision_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key ' + 'UNIQUE (purse_decision_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_contracts( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'contracts'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' + ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' + ',e_contract BYTEA NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE contracts_' || partition_suffix || ' ' + 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' + 'UNIQUE (contract_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_history_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'history_requests'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',request_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',history_fee_val INT8 NOT NULL' + ',history_fee_frac INT4 NOT NULL' + ',PRIMARY KEY (reserve_pub,request_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_purse_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deposits'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',partner_serial_id INT8' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',coin_pub BYTEA NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',PRIMARY KEY (purse_pub,coin_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + table_name = concat_ws('_', table_name, shard_suffix); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' + 'UNIQUE (purse_deposit_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_wads_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_in'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',origin_exchange_url TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',arrival_time INT8 NOT NULL' + ',UNIQUE (wad_id, origin_exchange_url)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' + 'UNIQUE (wad_in_serial_id) ' + ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' + 'UNIQUE (wad_id, origin_exchange_url) ' + ); +END +$$; +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' + ',wad_in_serial_id INT8' + ',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); + 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 +$$; +CREATE OR REPLACE FUNCTION create_table_wads_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_out'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',partner_serial_id INT8 NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',execution_time INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' + 'UNIQUE (wad_out_serial_id) ' + ); +END +$$; +CREATE OR REPLACE FUNCTION create_table_wad_out_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_out_entries'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',wad_out_serial_id INT8' + ',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); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; +CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' + 'UNIQUE (wad_out_entry_serial_id) ' + ); +END +$$; +COMMIT; diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in index a09ea58f..9d2110c8 100644 --- a/src/exchangedb/exchange-0002.sql.in +++ b/src/exchangedb/exchange-0002.sql.in @@ -25,5 +25,42 @@ SELECT _v.register_patch('exchange-0002', NULL, NULL); SET search_path TO exchange; #include "0002-denominations.sql" +#include "0002-denomination_revocations.sql" +#include "0002-wire_targets.sql" +#include "0002-kyc_alerts.sql" +#include "0002-profit_drains.sql" +#include "0002-legitimization_processes.sql" +#include "0002-legitimization_requirements.sql" +#include "0002-reserves.sql" +#include "0002-reserves_in.sql" +#include "0002-reserves_close.sql" +#include "0002-close_requests.sql" +#include "0002-reserves_open_deposits.sql" +#include "0002-reserves_open_requests.sql" +#include "0002-reserves_out.sql" +#include "0002-known_coins.sql" +#include "0002-refresh_commitments.sql" +#include "0002-refresh_revealed_coins.sql" +#include "0002-refresh_transfer_keys.sql" +#include "0002-deposits.sql" +#include "0002-refunds.sql" +#include "0002-wire_out.sql" +#include "0002-aggregation_transient.sql" +#include "0002-aggregation_tracking.sql" +#include "0002-recoup.sql" +#include "0002-recoup_refresh.sql" +#include "0002-prewire.sql" +#include "0002-cs_nonce_locks.sql" +#include "0002-purse_requests.sql" +#include "0002-purse_merges.sql" +#include "0002-account_merges.sql" +#include "0002-purse_decision.sql" +#include "0002-contracts.sql" +#include "0002-history_requests.sql" +#include "0002-purse_deposits.sql" +#include "0002-wads_in.sql" +#include "0002-wads_in_entries.sql" +#include "0002-wads_out.sql" +#include "0002-wad_out_entries.sql" COMMIT; diff --git a/src/exchangedb/exchange-0003.sql.in b/src/exchangedb/exchange-0003.sql.in new file mode 100644 index 00000000..ee03d440 --- /dev/null +++ b/src/exchangedb/exchange-0003.sql.in @@ -0,0 +1,35 @@ +-- +-- 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 +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('exchange-0003', NULL, NULL); + +-------------------- Schema ---------------------------- + +CREATE SCHEMA exchange; +COMMENT ON SCHEMA exchange IS 'taler-exchange data'; + +SET search_path TO exchange; + + +#include "0003-partner_accounts.sql" +#include "0003-purse_actions.sql" +#include "0003-purse_deletion.sql" + +COMMIT; diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 2f8b9b57..146d9f8c 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -103,7 +103,6 @@ #include "pg_start.h" #include "pg_rollback.h" #include "pg_create_tables.h" -#include "pg_setup_foreign_servers.h" #include "pg_event_listen.h" #include "pg_event_listen_cancel.h" #include "pg_event_notify.h" -- cgit v1.2.3