more work on SQL refactoring

1 files changed, 47 insertions, 7 deletions

diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql
index 0df5ae01c..978dea640 100644
--- a/src/exchangedb/0002-history_requests.sql
+++ b/src/exchangedb/0002-history_requests.sql
@@ -15,7 +15,7 @@
 --
 
 
-CREATE OR REPLACE FUNCTION create_table_history_requests(
+CREATE OR create_table_history_requests(
   IN shard_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
@@ -24,11 +24,10 @@ AS $$
 DECLARE
   table_name VARCHAR DEFAULT 'history_requests';
 BEGIN
-
   PERFORM create_partitioned_table(
-    'CREATE TABLE IF NOT EXISTS %I '
-      '(history_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
+    'CREATE TABLE %I '
+      '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE
+      ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)'
       ',request_timestamp INT8 NOT NULL'
       ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
       ',history_fee_val INT8 NOT NULL'
@@ -39,9 +38,45 @@ BEGIN
     ,'PARTITION BY HASH (reserve_pub)'
     ,shard_suffix
   );
+  PERFORM comment_partitioned_table(
+     'Paid history requests issued by a client against a reserve'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'When was the history request made'
+    ,'request_timestamp'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Signature approving payment for the history request'
+    ,'reserve_sig'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'History fee approved by the signature'
+    ,'history_fee_val'
+    ,table_name
+    ,shard_suffix
+  );
+END $$;
+
 
-END
-$$;
+CREATE OR foreign_table_history_requests()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'history_requests';
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+    ' REFERENCES reserves(reserve_pub) ON DELETE CASCADE'
+  );
+END $$;
 
 
 INSERT INTO exchange_tables
@@ -55,4 +90,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'create'
     ,TRUE
+    ,FALSE),
+    ('history_requests'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
     ,FALSE);