diff options
Diffstat (limited to 'src/exchangedb/exchange-0001-part.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 322 |
1 files changed, 22 insertions, 300 deletions
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index a478a0d1..b3534823 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -991,21 +991,8 @@ COMMENT ON COLUMN partners.master_sig -- ------------------------------ purse_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_requests - (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_expiration INT8 NOT NULL - ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) - ,age_limit INT4 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_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) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_requests(); + COMMENT ON TABLE purse_requests IS 'Requests establishing purses, associating them with a contract but without a target reserve'; COMMENT ON COLUMN purse_requests.purse_pub @@ -1021,45 +1008,18 @@ COMMENT ON COLUMN purse_requests.balance_val COMMENT ON COLUMN purse_requests.purse_sig IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST'; --- FIXME: change to materialized index by marge_pub! -CREATE INDEX IF NOT EXISTS purse_requests_merge_pub - ON purse_requests (merge_pub); - CREATE TABLE IF NOT EXISTS purse_requests_default PARTITION OF purse_requests FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_purse_requests_partition('default'); -- ------------------------------ purse_merges ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_merges - (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) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_merges(); + COMMENT ON TABLE purse_merges IS 'Merge requests where a purse-owner requested merging the purse into the account'; COMMENT ON COLUMN purse_merges.partner_serial_id @@ -1073,47 +1033,17 @@ COMMENT ON COLUMN purse_merges.merge_sig COMMENT ON COLUMN purse_merges.merge_timestamp IS 'when was the merge message signed'; -CREATE INDEX IF NOT EXISTS purse_merges_purse_pub - ON purse_merges (purse_pub); --- FIXME: change to materialized index by reserve_pub! -CREATE INDEX IF NOT EXISTS purse_merges_reserve_pub - ON purse_merges (reserve_pub); -COMMENT ON INDEX purse_merges_reserve_pub - IS 'needed in reserve history computation'; - CREATE TABLE IF NOT EXISTS purse_merges_default PARTITION OF purse_merges FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_purse_merges_partition('default'); - -- ------------------------------ account_merges ---------------------------------------- -CREATE TABLE IF NOT EXISTS account_merges - (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) - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_account_merges(); + COMMENT ON TABLE account_merges IS 'Merge requests where a purse- and account-owner requested merging the purse into the account'; COMMENT ON COLUMN account_merges.reserve_pub @@ -1123,48 +1053,17 @@ COMMENT ON COLUMN account_merges.purse_pub COMMENT ON COLUMN account_merges.reserve_sig IS 'signature by the reserve private key affirming the merge, of type TALER_SIGNATURE_WALLET_ACCOUNT_MERGE'; -CREATE INDEX IF NOT EXISTS account_merges_purse_pub - ON account_merges (purse_pub); -COMMENT ON INDEX account_merges_purse_pub - IS 'needed when checking for a purse merge status'; - --- FIXME: change to materialized index by reserve_pub! -CREATE INDEX IF NOT EXISTS account_merges_by_reserve_pub - ON account_merges (reserve_pub); - CREATE TABLE IF NOT EXISTS account_merges_default PARTITION OF account_merges FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_account_merges_partition('default'); -- ------------------------------ contracts ---------------------------------------- -CREATE TABLE IF NOT EXISTS contracts - (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) - ,e_contract BYTEA NOT NULL - ,purse_expiration INT8 NOT NULL - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_contracts(); + COMMENT ON TABLE contracts IS 'encrypted contracts associated with purses'; COMMENT ON COLUMN contracts.purse_pub @@ -1178,35 +1077,13 @@ CREATE TABLE IF NOT EXISTS contracts_default PARTITION OF contracts FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_contracts_partition('default'); -- ------------------------------ history_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS history_requests - (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) - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_history_requests(); + COMMENT ON TABLE history_requests IS 'Paid history requests issued by a client against a reserve'; COMMENT ON COLUMN history_requests.request_timestamp @@ -1220,18 +1097,10 @@ CREATE TABLE IF NOT EXISTS history_requests_default PARTITION OF history_requests FOR VALUES WITH (MODULUS 1, REMAINDER 0); - -- ------------------------------ close_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS close_requests - (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 - ,PRIMARY KEY (reserve_pub,close_timestamp) - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_close_requests(); + COMMENT ON TABLE close_requests IS 'Explicit requests by a reserve owner to close a reserve immediately'; COMMENT ON COLUMN close_requests.close_timestamp @@ -1248,17 +1117,8 @@ CREATE TABLE IF NOT EXISTS close_requests_default -- ------------------------------ purse_deposits ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_deposits - (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) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_deposits(); + COMMENT ON TABLE purse_deposits IS 'Requests depositing coins into a purse'; COMMENT ON COLUMN purse_deposits.partner_serial_id @@ -1272,43 +1132,17 @@ COMMENT ON COLUMN purse_deposits.amount_with_fee_val COMMENT ON COLUMN purse_deposits.coin_sig IS 'Signature of the coin affirming the deposit into the purse, of type TALER_SIGNATURE_PURSE_DEPOSIT'; --- FIXME: change to materialized index by coin_pub! -CREATE INDEX IF NOT EXISTS purse_deposits_by_coin_pub - ON purse_deposits (coin_pub); - CREATE TABLE IF NOT EXISTS purse_deposits_default PARTITION OF purse_deposits FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_purse_deposits_partition('default'); -- ------------------------------ wads_out ---------------------------------------- -CREATE TABLE IF NOT EXISTS wads_out - (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 - ) - PARTITION BY HASH (wad_id); +SELECT create_table_wads_out(); + COMMENT ON TABLE wads_out IS 'Wire transfers made to another exchange to transfer purse funds'; COMMENT ON COLUMN wads_out.wad_id @@ -1320,54 +1154,17 @@ COMMENT ON COLUMN wads_out.amount_val COMMENT ON COLUMN wads_out.execution_time IS 'Time when the wire transfer was scheduled'; -CREATE INDEX IF NOT EXISTS wads_out_index_by_wad_id - ON wads_out (wad_id); - CREATE TABLE IF NOT EXISTS wads_out_default PARTITION OF wads_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_wads_out_partition('default'); -- ------------------------------ wads_out_entries ---------------------------------------- -CREATE TABLE IF NOT EXISTS wad_out_entries - (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) - ) - PARTITION BY HASH (purse_pub); --- FIXME: convert to materialized index! -CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_reserve_pub - ON wad_out_entries (reserve_pub); +SELECT create_table_wad_out_entries(); + COMMENT ON TABLE wad_out_entries IS 'Purses combined into a wad'; COMMENT ON COLUMN wad_out_entries.wad_out_serial_id @@ -1397,36 +1194,12 @@ CREATE TABLE IF NOT EXISTS wad_out_entries_default PARTITION OF wad_out_entries FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_wad_out_entries_partition('default'); - -- ------------------------------ wads_in ---------------------------------------- -CREATE TABLE IF NOT EXISTS wads_in - (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) - ) - PARTITION BY HASH (wad_id); +SELECT create_table_wads_in(); + COMMENT ON TABLE wads_in IS 'Incoming exchange-to-exchange wad wire transfers'; COMMENT ON COLUMN wads_in.wad_id @@ -1442,44 +1215,13 @@ CREATE TABLE IF NOT EXISTS wads_in_default PARTITION OF wads_in FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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) ' - ); -END -$$; - SELECT add_constraints_to_wads_in_partition('default'); -- ------------------------------ wads_in_entries ---------------------------------------- -CREATE TABLE IF NOT EXISTS wad_in_entries - (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) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_wad_in_entries(); + COMMENT ON TABLE wad_in_entries IS 'list of purses aggregated in a wad according to the sending exchange'; COMMENT ON COLUMN wad_in_entries.wad_in_serial_id @@ -1504,31 +1246,11 @@ COMMENT ON COLUMN wad_in_entries.reserve_sig IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE'; COMMENT ON COLUMN wad_in_entries.purse_sig IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE'; --- FIXME: convert to materialized index! -CREATE INDEX IF NOT EXISTS wad_in_entries_reserve_pub - ON wad_in_entries (reserve_pub); -COMMENT ON INDEX wad_in_entries_reserve_pub - IS 'needed to compute reserve history'; CREATE TABLE IF NOT EXISTS wad_in_entries_default PARTITION OF wad_in_entries FOR VALUES WITH (MODULUS 1, REMAINDER 0); -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 -$$; - SELECT add_constraints_to_wad_in_entries_partition('default'); |