more work on SQL refactoring

1 files changed, 49 insertions, 22 deletions

diff --git a/src/exchangedb/0002-reserves_open_requests.sql b/src/exchangedb/0002-reserves_open_requests.sql
index e56553a5c..96084c1d9 100644
--- a/src/exchangedb/0002-reserves_open_requests.sql
+++ b/src/exchangedb/0002-reserves_open_requests.sql
@@ -14,8 +14,8 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
-CREATE OR REPLACE FUNCTION create_table_reserves_open_requests(
-  IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_open_requests(
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -23,11 +23,10 @@ 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'
+    'CREATE TABLE %I'
+      '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+      ',reserve_pub BYTEA NOT NULL'
       ',request_timestamp INT8 NOT NULL'
       ',expiration_date INT8 NOT NULL'
       ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
@@ -37,42 +36,60 @@ BEGIN
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (reserve_pub)'
-    ,shard_suffix
+    ,partition_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);'
+  PERFORM comment_partitioned_table (
+     'requests to keep a reserve open'
+    ,table_name
+    ,partition_suffix
   );
-  EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index '
-    'ON ' || table_name || ' '
-    '(reserve_pub);'
+  PERFORM comment_partitioned_column (
+     'Fee to pay for the request from the reserve balance itself.'
+    ,'reserve_payment_val'
+    ,table_name
+    ,partition_suffix
   );
 END
 $$;
 
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition(
+
+CREATE FUNCTION constrain_table_reserves_open_requests(
   IN partition_suffix VARCHAR
 )
 RETURNS void
 LANGUAGE plpgsql
 AS $$
+DECLARE
+  table_name VARCHAR default 'reserves_open_requests';
 BEGIN
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'ALTER TABLE reserves_open_requests_' || partition_suffix || ' '
-      'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid '
+    'ALTER TABLE ' || table_name || ' '
+      'ADD CONSTRAINT ' || table_name || '_by_uuid '
         'PRIMARY KEY (open_request_uuid),'
-      'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time '
+      'ADD CONSTRAINT ' || table_name || '_by_time '
         'UNIQUE (reserve_pub,request_timestamp)'
   );
 END
 $$;
 
 
+CREATE FUNCTION foreign_table_reserves_open_requests()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR default 'reserves_open_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
     (name
     ,version
@@ -84,4 +101,14 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'create'
     ,TRUE
+    ,FALSE),
+    ('reserves_open_requests'
+    ,'exchange-0002'
+    ,'constrain'
+    ,TRUE
+    ,FALSE),
+    ('reserves_open_requests'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
     ,FALSE);