diff options
author | Marco Boss <bossm8@bfh.ch> | 2022-02-27 21:23:20 +0100 |
---|---|---|
committer | Marco Boss <bossm8@bfh.ch> | 2022-02-27 21:23:20 +0100 |
commit | 58eb3d95fceb0742bed029944d49b8096ee390c2 (patch) | |
tree | 3e7523dc03d27b3c4a75a0f00cb7604a7e480499 /src/exchangedb | |
parent | 99dd36f7f6b22014ed671e661e6553ba7c41810b (diff) | |
download | exchange-58eb3d95fceb0742bed029944d49b8096ee390c2.tar.xz |
towards adding shard/partitioning init functionality
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/exchange-0001.sql | 922 |
1 files changed, 761 insertions, 161 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 184d62a7c..baf0056b0 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -96,15 +96,56 @@ CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index (wire_target_serial_id ); -CREATE TABLE IF NOT EXISTS reserves - (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY - ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32) - ,current_balance_val INT8 NOT NULL - ,current_balance_frac INT4 NOT NULL - ,expiration_date INT8 NOT NULL - ,gc_date INT8 NOT NULL - ) - PARTITION BY HASH (reserve_pub); + +CREATE OR REPLACE FUNCTION create_table_reserves( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'reserves' + ELSE + 'reserves_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (reserve_pub);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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' + ',current_balance_frac INT4 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_reserves + IS 'Create the reserves table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_reserves(); + COMMENT ON TABLE reserves IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.'; COMMENT ON COLUMN reserves.reserve_pub @@ -137,17 +178,57 @@ COMMENT ON INDEX reserves_by_gc_date_index IS 'for reserve garbage collection'; -CREATE TABLE IF NOT EXISTS reserves_in - (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_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) - ,exchange_account_section TEXT NOT NULL - ,execution_date INT8 NOT NULL - ) - PARTITION BY HASH (reserve_pub); +CREATE OR REPLACE FUNCTION create_table_reserves_in( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'reserves_in' + ELSE + 'reserves_in_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (reserve_pub);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_reserves_in + IS 'Create the reserves_in table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_reserves_in(); + COMMENT ON TABLE reserves_in IS 'list of transfers of funds into the reserves, one per incoming wire transfer'; COMMENT ON COLUMN reserves_in.wire_source_serial_id @@ -175,17 +256,58 @@ CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_reserve_in_serial_id_ ); -CREATE TABLE IF NOT EXISTS reserves_close - (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_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,closing_fee_val INT8 NOT NULL - ,closing_fee_frac INT4 NOT NULL) - PARTITION BY HASH (reserve_pub); +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'reserves_close' + ELSE + 'reserves_close_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (reserve_pub);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_reserves_close + IS 'Create the reserves_close table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_reserves_close(); + COMMENT ON TABLE reserves_close IS 'wire transfers executed by the reserve to close reserves'; COMMENT ON COLUMN reserves_close.wire_target_serial_id @@ -202,18 +324,58 @@ CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index (reserve_pub); -CREATE TABLE IF NOT EXISTS reserves_out - (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 - ) - PARTITION BY HASH (h_blind_ev); +CREATE OR REPLACE FUNCTION create_table_reserves_out( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'reserves_out' + ELSE + 'reserves_out_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (h_blind_ev);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_reserves_out + IS 'Create the reserves_out table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_reserves_out(); + COMMENT ON TABLE reserves_out IS 'Withdraw operations performed on reserves.'; COMMENT ON COLUMN reserves_out.h_blind_ev @@ -315,16 +477,56 @@ COMMENT ON COLUMN extensions.config IS 'Configuration of the extension as JSON-blob, maybe NULL'; -CREATE TABLE IF NOT EXISTS known_coins - (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 - ,remaining_frac INT4 NOT NULL - ) - PARTITION BY HASH (coin_pub); -- FIXME: or include denominations_serial? or multi-level partitioning? +CREATE OR REPLACE FUNCTION create_table_known_coins( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'known_coins' + ELSE + 'known_coins_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (coin_pub);' -- FIXME: or include denominations_serial? or multi-level partitioning? + ELSE + ';' + END; + + EXECUTE FORMAT( + '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' + ',remaining_frac INT4 NOT NULL' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_known_coins + IS 'Create the known_coins table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_known_coins(); + COMMENT ON TABLE known_coins IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations'; COMMENT ON COLUMN known_coins.denominations_serial @@ -346,17 +548,57 @@ CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index (known_coin_id); -CREATE TABLE IF NOT EXISTS refresh_commitments - (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 - ,h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32) - ,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 - ) - PARTITION BY HASH (rc); +CREATE OR REPLACE FUNCTION create_table_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'refresh_commitments' + ELSE + 'refresh_commitments_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (rc);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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' + ',h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)' + ',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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_refresh_commitments + IS 'Create the refresh_commitments table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_refresh_commitments(); + COMMENT ON TABLE refresh_commitments IS 'Commitments made when melting coins and the gamma value chosen by the exchange.'; COMMENT ON COLUMN refresh_commitments.noreveal_index @@ -379,19 +621,59 @@ CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index (old_coin_pub); -CREATE TABLE IF NOT EXISTS refresh_revealed_coins - (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 +CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'refresh_revealed_coins' + ELSE + 'refresh_revealed_coins_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (melt_serial_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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 - ) - PARTITION BY HASH (melt_serial_id); + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_refresh_revealed_coins + IS 'Create the refresh_revealed_coins table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_refresh_revealed_coins(); + COMMENT ON TABLE refresh_revealed_coins IS 'Revelations about the new coins that are to be created during a melting session.'; COMMENT ON COLUMN refresh_revealed_coins.rrc_serial @@ -422,14 +704,53 @@ CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index ON refresh_revealed_coins (melt_serial_id); +CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'refresh_transfer_keys' + ELSE + 'refresh_transfer_keys_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (melt_serial_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_refresh_transfer_keys + IS 'Create the refresh_transfer_keys table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_refresh_transfer_keys(); -CREATE TABLE IF NOT EXISTS refresh_transfer_keys - (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 - ) - PARTITION BY HASH (melt_serial_id); COMMENT ON TABLE refresh_transfer_keys IS 'Transfer keys of a refresh operation (the data revealed to the exchange).'; COMMENT ON COLUMN refresh_transfer_keys.rtc_serial @@ -458,28 +779,68 @@ COMMENT ON COLUMN extension_details.extension_options IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the extensions supported by the exchange.'; -CREATE TABLE IF NOT EXISTS deposits - (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY - ,shard INT8 NOT NULL - ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE - ,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_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) - ,tiny BOOLEAN NOT NULL DEFAULT FALSE - ,done BOOLEAN NOT NULL DEFAULT FALSE - ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE - ,extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE - ,UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms) - ) - PARTITION BY HASH (shard); +CREATE OR REPLACE FUNCTION create_table_deposits( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'deposits' + ELSE + 'deposits_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (shard);' + ELSE + ';' + END; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',shard INT8 NOT NULL' + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' + ',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_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)' + ',tiny BOOLEAN NOT NULL DEFAULT FALSE' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE' + ',UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_deposits + IS 'Create the deposits table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_deposits(); + CREATE TABLE IF NOT EXISTS deposits_default PARTITION OF deposits FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -530,16 +891,56 @@ COMMENT ON INDEX deposits_for_iterate_matching_index IS 'for deposits_iterate_matching'; -CREATE TABLE IF NOT EXISTS refunds - (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,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! - ) - PARTITION BY HASH (deposit_serial_id); +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'refunds' + ELSE + 'refunds_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (deposit_serial_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_refunds + IS 'Create the refunds table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_refunds(); + COMMENT ON TABLE refunds IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'; COMMENT ON COLUMN refunds.deposit_serial_id @@ -557,16 +958,56 @@ CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index (refund_serial_id); -CREATE TABLE IF NOT EXISTS wire_out - (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_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) - ,exchange_account_section TEXT NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ) - PARTITION BY HASH (wtid_raw); +CREATE OR REPLACE FUNCTION create_table_wire_out( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'wire_out' + ELSE + 'wire_out_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (wtid_raw);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_wire_out + IS 'Create the wire_out table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_wire_out(); + COMMENT ON TABLE wire_out IS 'wire transfers the exchange has executed'; COMMENT ON COLUMN wire_out.exchange_account_section @@ -585,13 +1026,52 @@ CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index (wire_target_serial_id); +CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'aggregation_tracking' + ELSE + 'aggregation_tracking_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (deposit_serial_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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' + ',wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ') %s' + ,table_name + ,partition_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_aggregation_tracking + IS 'Create the aggregation_tracking table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_aggregation_tracking(); -CREATE TABLE IF NOT EXISTS aggregation_tracking - (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE - ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE - ) - PARTITION BY HASH (deposit_serial_id); COMMENT ON TABLE aggregation_tracking IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)'; COMMENT ON COLUMN aggregation_tracking.wtid_raw @@ -632,17 +1112,57 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index (end_date); -CREATE TABLE IF NOT EXISTS recoup - (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) - ,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 - ) - PARTITION BY HASH (known_coin_id); +CREATE OR REPLACE FUNCTION create_table_recoup( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'recoup' + ELSE + 'recoup_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (known_coin_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)' + ',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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_recoup + IS 'Create the recoup table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_recoup(); + COMMENT ON TABLE recoup IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.'; COMMENT ON COLUMN recoup.known_coin_id @@ -668,17 +1188,57 @@ CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index (known_coin_id); -CREATE TABLE IF NOT EXISTS recoup_refresh - (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) - ,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 - ) - PARTITION BY HASH (known_coin_id); +CREATE OR REPLACE FUNCTION create_table_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'recoup_refresh' + ELSE + 'recoup_refresh_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (known_coin_id);' + ELSE + ';' + END; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)' + ',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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_recoup_refresh + IS 'Create the recoup_refresh table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_recoup_refresh(); + COMMENT ON TABLE recoup_refresh IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'; COMMENT ON COLUMN recoup_refresh.known_coin_id @@ -702,14 +1262,54 @@ CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index (known_coin_id); -CREATE TABLE IF NOT EXISTS prewire - (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 - ) - PARTITION BY HASH (prewire_uuid); +CREATE OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT '' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR; + partition_str VARCHAR; +BEGIN + + table_name = CASE + shard_suffix + WHEN '' THEN + 'prewire' + ELSE + 'prewire_' || shard_suffix + END; + + partition_str = CASE + shard_suffix + WHEN '' THEN + 'PARTITION BY HASH (prewire_uuid);' + ELSE + ';' + END; + + EXECUTE FORMAT( + '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_str + ); + +END +$$; + +COMMENT ON FUNCTION create_table_prewire + IS 'Create the prewire table, if argument `shard_suffix` is empty, a partitioned master table + without partitions will be created. If not empty, a shard table will be created'; + +SELECT create_table_prewire(); + COMMENT ON TABLE prewire IS 'pre-commit data for wire transfers we are about to execute'; COMMENT ON COLUMN prewire.failed |