diff options
Diffstat (limited to 'src/exchangedb')
| -rw-r--r-- | src/exchangedb/common-0001.sql | 171 | ||||
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 52 | ||||
| -rw-r--r-- | src/exchangedb/shard-0001-part.sql | 11 |
3 files changed, 234 insertions, 0 deletions
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql index 6dc661fd7..4a0aac381 100644 --- a/src/exchangedb/common-0001.sql +++ b/src/exchangedb/common-0001.sql @@ -382,6 +382,177 @@ BEGIN END $$; + +--------------------------- reserves_open_requests ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',request_timestamp INT8 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_payment_val INT8 NOT NULL' + ',reserve_payment_frac INT4 NOT NULL' + ',requested_purse_limit INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' + 'ON ' || table_name || ' ' + '(open_request_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'PRIMARY KEY (open_request_uuid),' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'UNIQUE (reserve_pub,request_timestamp)' + ); +END +$$; + + +--------------------------- reserves_open_deposits ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',request_timestamp INT8 NOT NULL' + ',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,request_timestamp);' + ); +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,reserve_pub)' + ); +END +$$; + + +--------------------------- reserves_close_requests ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_close_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_request_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' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ') %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_request_uuid_index ' + 'ON ' || table_name || ' ' + '(close_request_uuid);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_request_uuid_pkey ' + 'PRIMARY KEY (close_request_uuid)' + ); +END +$$; + + ---------------------------- reserves_out ------------------------------- CREATE OR REPLACE FUNCTION create_table_reserves_out( diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index 6a3b4ac29..c9c3e2f04 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -233,6 +233,58 @@ CREATE TABLE IF NOT EXISTS reserves_close_default SELECT add_constraints_to_reserves_close_partition('default'); + + + + +-- ------------------------------ reserves_open_requests ---------------------------------------- + +SELECT create_table_reserves_open_requests(); + +COMMENT ON TABLE reserves_open_requests + IS 'requests to keep a reserve open'; +COMMENT ON COLUMN reserves_open_requests.reserve_payment_val + IS 'Funding to pay for the request from the reserve balance itself.'; + +CREATE TABLE IF NOT EXISTS reserves_open_requests_default + PARTITION OF reserves_open_requests + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_open_request_partition('default'); + + +-- ------------------------------ reserves_open_deposits ---------------------------------------- + +SELECT create_table_reserves_open_deposits(); + +COMMENT ON TABLE reserves_open_deposits + IS 'coin contributions paying for a reserve to remain open'; +COMMENT ON COLUMN reserves_open_deposits.request_timestamp + IS 'Identifies the specific reserve open request being paid for.'; + +CREATE TABLE IF NOT EXISTS reserves_open_deposits_default + PARTITION OF reserves_open_deposits + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_open_deposits_partition('default'); + + +-- ------------------------------ reserves_close_requests ---------------------------------------- + +SELECT create_table_reserves_close_requests(); + +COMMENT ON TABLE reserves_close_requests + IS 'explicit requests by clients to affect an immediate closure of a reserve'; +COMMENT ON COLUMN reserves_close_requests.wire_target_h_payto + IS 'Identifies the credited bank account. Optional.'; + +CREATE TABLE IF NOT EXISTS reserves_close_requests_default + PARTITION OF reserves_close_requests + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_close_requests_partition('default'); + + -- ------------------------------ reserves_out ---------------------------------------- SELECT create_table_reserves_out(); diff --git a/src/exchangedb/shard-0001-part.sql b/src/exchangedb/shard-0001-part.sql index 5ee7fbf29..0f20be63e 100644 --- a/src/exchangedb/shard-0001-part.sql +++ b/src/exchangedb/shard-0001-part.sql @@ -42,8 +42,19 @@ BEGIN 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_close_requests(shard_suffix); + PERFORM add_constraints_to_reserves_close_requests_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); |