more work on SQL refactoring

1 files changed, 82 insertions, 24 deletions

diff --git a/src/exchangedb/0002-recoup_refresh.sql b/src/exchangedb/0002-recoup_refresh.sql
index 9e6361a16..a5ca69a64 100644
--- a/src/exchangedb/0002-recoup_refresh.sql
+++ b/src/exchangedb/0002-recoup_refresh.sql
@@ -15,7 +15,7 @@
 --
 
 
-CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
+CREATE FUNCTION create_table_recoup_refresh(
   IN shard_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
@@ -24,53 +24,101 @@ AS $$
 DECLARE
   table_name VARCHAR DEFAULT 'recoup_refresh';
 BEGIN
-
   PERFORM create_partitioned_table(
-    'CREATE TABLE IF NOT EXISTS %I'
-      '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
-      ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
-      ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
-      ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
-      ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
-      ',amount_val INT8 NOT NULL'
-      ',amount_frac INT4 NOT NULL'
-      ',recoup_timestamp INT8 NOT NULL'
-      ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE'
+    'CREATE TABLE %I'
+    '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+    ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+    ',known_coin_id BIGINT NOT NULL'
+    ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+    ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+    ',amount_val INT8 NOT NULL'
+    ',amount_frac INT4 NOT NULL'
+    ',recoup_timestamp INT8 NOT NULL'
+    ',rrc_serial INT8 NOT NULL'
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (coin_pub)'
     ,shard_suffix
   );
+  PERFORM comment_partitioned_table(
+     'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'
+    ,'coin_pub'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'FIXME: (To be) used for garbage collection (in the absence of foreign constraints, in the future)'
+    ,'known_coin_id'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).'
+    ,'rrc_serial'
+    ,table_name
+    ,shard_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.'
+    ,'coin_blind'
+    ,table_name
+    ,shard_suffix
+  );
+END
+$$;
 
-  table_name = concat_ws('_', table_name, shard_suffix);
 
+CREATE FUNCTION constrain_table_recoup_refresh(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'recoup_refresh';
+BEGIN
+  table_name = concat_ws('_', table_name, shard_suffix);
   -- FIXME: any query using this index will be slow. Materialize index or change query?
   -- Also: which query uses this index?
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index '
-    'ON ' || table_name || ' '
+    'CREATE INDEX ' || table_name || '_by_rrc_serial_index'
+    ' ON ' || table_name || ' '
     '(rrc_serial);'
   );
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
-    'ON ' || table_name || ' '
+    'CREATE INDEX ' || table_name || '_by_coin_pub_index'
+    ' ON ' || table_name || ' '
     '(coin_pub);'
   );
-
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_recoup_refresh_uuid_key'
+    ' UNIQUE (recoup_refresh_uuid) '
+  );
 END
 $$;
 
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition(
-  IN partition_suffix VARCHAR
-)
+
+CREATE FUNCTION foreign_table_recoup_refresh()
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'recoup_refresh';
 BEGIN
   EXECUTE FORMAT (
-    'ALTER TABLE recoup_refresh_' || partition_suffix || ' '
-      'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key '
-        'UNIQUE (recoup_refresh_uuid) '
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
+    ' REFERENCES known_coins (coin_pub)'
+    ' ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id'
+    ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
+    ' ADD CONSTRAINT ' || table_name || '_foreign_rrc_serial'
+    ' REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE'
   );
 END
 $$;
@@ -87,4 +135,14 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'create'
     ,TRUE
+    ,FALSE),
+    ('recoup_refresh'
+    ,'exchange-0002'
+    ,'constrain'
+    ,TRUE
+    ,FALSE),
+    ('recoup_refresh'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
     ,FALSE);