From cf2e37cd876651e799893e8fe5babb51a9e12dd7 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <christian@grothoff.org>
Date: Sun, 27 Nov 2022 21:21:04 +0100
Subject: more work on SQL refactoring

---
 src/exchangedb/0002-reserves_close.sql | 47 ++++++++++++++++++++++++----------
 1 file changed, 33 insertions(+), 14 deletions(-)

(limited to 'src/exchangedb/0002-reserves_close.sql')

diff --git a/src/exchangedb/0002-reserves_close.sql b/src/exchangedb/0002-reserves_close.sql
index b68550a7..e93182bc 100644
--- a/src/exchangedb/0002-reserves_close.sql
+++ b/src/exchangedb/0002-reserves_close.sql
@@ -15,7 +15,7 @@
 --
 
 CREATE FUNCTION create_table_reserves_close(
-  IN shard_suffix VARCHAR DEFAULT NULL
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -25,8 +25,8 @@ DECLARE
 BEGIN
   PERFORM create_partitioned_table(
     'CREATE TABLE %I'
-      '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY'
-      ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+      '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+      ',reserve_pub BYTEA NOT NULL'
       ',execution_date INT8 NOT NULL'
       ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
       ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
@@ -38,18 +38,18 @@ BEGIN
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (reserve_pub)'
-    ,shard_suffix
+    ,partition_suffix
   );
   PERFORM comment_partitioned_table(
      'wire transfers executed by the reserve to close reserves'
     ,table_name
-    ,shard_suffix
+    ,partition_suffix
   );
   PERFORM comment_partitioned_column(
      'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.'
     ,'wire_target_h_payto'
     ,table_name
-    ,shard_suffix
+    ,partition_suffix
   );
 END
 $$;
@@ -64,19 +64,33 @@ AS $$
 DECLARE
   table_name VARCHAR default 'reserves_close';
 BEGIN
-  table_name = concat_ws('_', table_name, shard_suffix);
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'ALTER TABLE ' || table_name || ' '
-      'ADD CONSTRAINT ' || table_name || '_close_uuid_pkey '
-      'PRIMARY KEY (close_uuid)'
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_close_uuid_pkey'
+    ' PRIMARY KEY (close_uuid)'
   );
   EXECUTE FORMAT (
     'CREATE INDEX ' || table_name || '_by_reserve_pub_index '
-    'ON ' || table_name || ' '
-    '(reserve_pub);'
+    'ON ' || table_name || ' (reserve_pub);'
   );
-END
-$$;
+END $$;
+
+
+CREATE FUNCTION foreign_table_reserves_close()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR default 'reserves_close';
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
+    ' FOREIGN KEY (reserve_pub) '
+    ' REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+  );
+END $$;
 
 
 INSERT INTO exchange_tables
@@ -95,4 +109,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'constrain'
     ,TRUE
+    ,FALSE),
+    ('reserves_close'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
     ,FALSE);
-- 
cgit v1.2.3

