prepare tables for DD31

1 files changed, 171 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(