more work on SQL refactoring

1 files changed, 58 insertions, 21 deletions

diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql
index 509cc7d00..823466942 100644
--- a/src/exchangedb/0002-refunds.sql
+++ b/src/exchangedb/0002-refunds.sql
@@ -14,8 +14,8 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
-CREATE OR REPLACE FUNCTION create_table_refunds(
-  IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_refunds(
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -24,46 +24,78 @@ DECLARE
   table_name VARCHAR DEFAULT 'refunds';
 BEGIN
   PERFORM create_partitioned_table(
-    'CREATE TABLE IF NOT EXISTS %I'
-      '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
-      ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE
-      ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
+    'CREATE TABLE %I'
+      '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+      ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+      ',deposit_serial_id INT8 NOT NULL'
       ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
       ',rtransaction_id INT8 NOT NULL'
       ',amount_with_fee_val INT8 NOT NULL'
       ',amount_with_fee_frac INT4 NOT NULL'
-      -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (coin_pub)'
-    ,shard_suffix
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_table(
+     'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.'
+    ,'deposit_serial_id'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund'
+    ,'rtransaction_id'
+    ,table_name
+    ,partition_suffix
   );
+END
+$$;
 
-  table_name = concat_ws('_', table_name, shard_suffix);
 
+CREATE FUNCTION constrain_table_refunds (
+  IN partition_suffix VARCHAR DEFAULT NULL
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'refunds';
+BEGIN
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+    'CREATE INDEX ' || table_name || '_by_coin_pub_index '
     'ON ' || table_name || ' '
     '(coin_pub);'
   );
-
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_refund_serial_id_key'
+    ' UNIQUE (refund_serial_id) '
+    ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
+  );
 END
 $$;
 
-CREATE OR REPLACE FUNCTION constrain0002_table_refunds (
-  IN partition_suffix VARCHAR DEFAULT NULL
-)
+
+CREATE FUNCTION foreign_table_refunds ()
 RETURNS void
 LANGUAGE plpgsql
 AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'refunds';
 BEGIN
   EXECUTE FORMAT (
-  -- FIXME: '_' issue if partition_suffix is NULL
-  -- => solve with general ALTER TABLE helper function!
-    'ALTER TABLE refunds_' || partition_suffix || ' '
-      'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key '
-        'UNIQUE (refund_serial_id) '
-      ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+    ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
+    ',ADD CONSTRAINT ' || table_name || '_foreign_deposit'
+    ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
   );
 END
 $$;
@@ -83,6 +115,11 @@ INSERT INTO exchange_tables
     ,FALSE),
     ('refunds'
     ,'exchange-0002'
-    ,'constrain0002'
+    ,'constrain'
+    ,TRUE
+    ,FALSE),
+    ('refunds'
+    ,'exchange-0002'
+    ,'foreign'
     ,TRUE
     ,FALSE);