diff options
author | Christian Grothoff <christian@grothoff.org> | 2022-11-27 15:33:29 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2022-11-27 15:33:29 +0100 |
commit | bbf3e6fe03f1f9f8c7d342004f65bab9f7b1b91c (patch) | |
tree | 48ef22cdc1752d197b25b6c42c029fb9f7ef9b0d /src/exchangedb | |
parent | 2eff222c524fa3b5ce2dd4a636aaec8dfb0862c7 (diff) |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/exchange-0002.sql | 1839 | ||||
-rw-r--r-- | src/exchangedb/shard-0001-part.sql | 134 | ||||
-rw-r--r-- | src/exchangedb/shard-0002-part.sql | 15 |
3 files changed, 0 insertions, 1988 deletions
diff --git a/src/exchangedb/exchange-0002.sql b/src/exchangedb/exchange-0002.sql deleted file mode 100644 index 600090bca..000000000 --- a/src/exchangedb/exchange-0002.sql +++ /dev/null @@ -1,1839 +0,0 @@ -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/shard-0001-part.sql b/src/exchangedb/shard-0001-part.sql deleted file mode 100644 index 8b6ec07f9..000000000 --- a/src/exchangedb/shard-0001-part.sql +++ /dev/null @@ -1,134 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- - -CREATE OR REPLACE FUNCTION setup_shard( - shard_idx INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - shard_suffix VARCHAR; -BEGIN - - shard_suffix = shard_idx::varchar; - - PERFORM create_table_wire_targets(shard_suffix); - PERFORM add_constraints_to_wire_targets_partition(shard_suffix); - - PERFORM create_table_reserves(shard_suffix); - - PERFORM create_table_legitimization_requirements(shard_suffix); - PERFORM add_constraints_to_legitimization_requirements_partition(shard_suffix); - - PERFORM create_table_legitimization_processes(shard_suffix); - PERFORM add_constraints_to_legitimization_processes_partition(shard_suffix); - - - PERFORM create_table_reserves_in(shard_suffix); - PERFORM add_constraints_to_reserves_in_partition(shard_suffix); - - PERFORM create_table_reserves_close(shard_suffix); - PERFORM add_constraints_to_reserves_close_partition(shard_suffix); - - PERFORM create_table_reserves_open_requests(shard_suffix); - PERFORM add_constraints_to_reserves_open_request_partition(shard_suffix); - - PERFORM create_table_reserves_open_deposits(shard_suffix); - PERFORM add_constraints_to_reserves_open_deposits_partition(shard_suffix); - - PERFORM create_table_reserves_out(shard_suffix); - PERFORM add_constraints_to_reserves_out_partition(shard_suffix); - - PERFORM create_table_reserves_out_by_reserve(shard_suffix); - - PERFORM create_table_known_coins(shard_suffix); - PERFORM add_constraints_to_known_coins_partition(shard_suffix); - - PERFORM create_table_refresh_commitments(shard_suffix); - PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); - - PERFORM create_table_refresh_revealed_coins(shard_suffix); - PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); - - PERFORM create_table_refresh_transfer_keys(shard_suffix); - PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); - - PERFORM create_table_deposits(shard_suffix); - PERFORM add_constraints_to_deposits_partition(shard_suffix); - - PERFORM create_table_deposits_by_ready(shard_suffix); - - PERFORM create_table_deposits_for_matching(shard_suffix); - - PERFORM create_table_refunds(shard_suffix); - PERFORM add_constraints_to_refunds_partition(shard_suffix); - - PERFORM create_table_wire_out(shard_suffix); - PERFORM add_constraints_to_wire_out_partition(shard_suffix); - - PERFORM create_table_aggregation_transient(shard_suffix); - - PERFORM create_table_aggregation_tracking(shard_suffix); - PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); - - PERFORM create_table_recoup(shard_suffix); - PERFORM add_constraints_to_recoup_partition(shard_suffix); - - PERFORM create_table_recoup_by_reserve(shard_suffix); - - PERFORM create_table_recoup_refresh(shard_suffix); - PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); - - PERFORM create_table_prewire(shard_suffix); - - PERFORM create_table_cs_nonce_locks(shard_suffix); - PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); - - PERFORM create_table_purse_requests(shard_suffix); - PERFORM add_constraints_to_purse_requests_partition(shard_suffix); - - PERFORM create_table_purse_decision(shard_suffix); - PERFORM add_constraints_to_purse_decision_partition(shard_suffix); - - PERFORM create_table_purse_merges(shard_suffix); - PERFORM add_constraints_to_purse_merges_partition(shard_suffix); - - PERFORM create_table_account_merges(shard_suffix); - PERFORM add_constraints_to_account_merges_partition(shard_suffix); - - PERFORM create_table_contracts(shard_suffix); - PERFORM add_constraints_to_contracts_partition(shard_suffix); - - PERFORM create_table_history_requests(shard_suffix); - - PERFORM create_table_close_requests(shard_suffix); - PERFORM add_constraints_to_close_requests_partition(shard_suffix); - - - PERFORM create_table_purse_deposits(shard_suffix); - PERFORM add_constraints_to_purse_deposits_partition(shard_suffix); - - PERFORM create_table_wad_out_entries(shard_suffix); - PERFORM add_constraints_to_wad_out_entries_partition(shard_suffix); - - PERFORM create_table_wads_in(shard_suffix); - PERFORM add_constraints_to_wads_in_partition(shard_suffix); - - PERFORM create_table_wad_in_entries(shard_suffix); - PERFORM add_constraints_to_wad_in_entries_partition(shard_suffix); -END -$$; diff --git a/src/exchangedb/shard-0002-part.sql b/src/exchangedb/shard-0002-part.sql deleted file mode 100644 index 723154f1d..000000000 --- a/src/exchangedb/shard-0002-part.sql +++ /dev/null @@ -1,15 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- |