prepare tables for DD31

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);