more work on SQL refactoring

1 files changed, 124 insertions, 32 deletions

diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql
index e2aed9300..525672893 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.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_out(
-  IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_reserves_out(
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -23,14 +23,13 @@ AS $$
 DECLARE
   table_name VARCHAR default 'reserves_out';
 BEGIN
-
   PERFORM create_partitioned_table(
-    'CREATE TABLE IF NOT EXISTS %I'
-      '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+    'CREATE TABLE %I'
+      '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
       ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
-      ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)'
+      ',denominations_serial INT8 NOT NULL'
       ',denom_sig BYTEA NOT NULL'
-      ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
+      ',reserve_uuid INT8 NOT NULL'
       ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
       ',execution_date INT8 NOT NULL'
       ',amount_with_fee_val INT8 NOT NULL'
@@ -38,19 +37,47 @@ BEGIN
     ') %s ;'
     ,'reserves_out'
     ,'PARTITION BY HASH (h_blind_ev)'
-    ,shard_suffix
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_table (
+     'Withdraw operations performed on reserves.'
+    ,'reserves_out'
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column (
+     'Hash of the blinded coin, used as primary key here so that broken clients that use a non-random coin or blinding factor fail to withdraw (otherwise they would fail on deposit when the coin is not unique there).'
+    ,'h_blind_ev'
+    ,'reserves_out'
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column (
+     'We do not CASCADE ON DELETE for the foreign constrain here, as we may keep the denomination data alive'
+    ,'denominations_serial'
+    ,'reserves_out'
+    ,partition_suffix
   );
+END
+$$;
 
-  table_name = concat_ws('_', table_name, shard_suffix);
 
+CREATE FUNCTION constrain_table_reserves_out(
+  IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR default 'reserves_out';
+BEGIN
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index '
-    'ON ' || table_name || ' '
-    '(reserve_out_serial_id);'
+    'ALTER TABLE ' || table_name || ' '
+      'ADD CONSTRAINT ' || table_name || '_reserve_out_serial_id_key '
+        'UNIQUE (reserve_out_serial_id)'
   );
   -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well???
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index '
+    'CREATE INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index '
     'ON ' || table_name || ' '
     '(reserve_uuid, execution_date);'
   );
@@ -58,29 +85,30 @@ BEGIN
     'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index '
     'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';'
   );
-
 END
 $$;
 
 
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition(
-  IN partition_suffix VARCHAR
-)
+CREATE FUNCTION foreign_table_reserves_out()
 RETURNS void
 LANGUAGE plpgsql
 AS $$
+DECLARE
+  table_name VARCHAR default 'reserves_out';
 BEGIN
   EXECUTE FORMAT (
-    'ALTER TABLE reserves_out_' || partition_suffix || ' '
-      'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key '
-        'UNIQUE (reserve_out_serial_id)'
+    'ALTER TABLE ' || table_name || ' '
+      'ADD CONSTRAINT ' || table_name || '_foreign_denom '
+      'REFERENCES denominations (denominations_serial)'
+      'ADD CONSTRAINT ' || table_name || '_foreign_reserve '
+      'REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
   );
 END
 $$;
 
-CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve(
-  IN shard_suffix VARCHAR DEFAULT NULL
+
+CREATE FUNCTION create_table_reserves_out_by_reserve(
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -88,29 +116,78 @@ AS $$
 DECLARE
   table_name VARCHAR DEFAULT 'reserves_out_by_reserve';
 BEGIN
-
   PERFORM create_partitioned_table(
-  'CREATE TABLE IF NOT EXISTS %I'
+  'CREATE TABLE %I'
     '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
     ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)'
     ') %s '
     ,table_name
     ,'PARTITION BY HASH (reserve_uuid)'
-    ,shard_suffix
+    ,partition_suffix
   );
-
-  table_name = concat_ws('_', table_name, shard_suffix);
-
+  PERFORM comment_partitioned_column (
+     'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.'
+    ,table_name
+    ,partition_suffix
+  );
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+    'CREATE INDEX ' || table_name || '_main_index '
     'ON ' || table_name || ' '
     '(reserve_uuid);'
   );
-
 END
 $$;
 
 
+CREATE FUNCTION reserves_out_by_reserve_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO exchange.reserves_out_by_reserve
+    (reserve_uuid
+    ,h_blind_ev)
+  VALUES
+    (NEW.reserve_uuid
+    ,NEW.h_blind_ev);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
+  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
+
+
+CREATE FUNCTION reserves_out_by_reserve_delete_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  DELETE FROM exchange.reserves_out_by_reserve
+   WHERE reserve_uuid = OLD.reserve_uuid;
+  RETURN OLD;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
+  IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
+
+
+CREATE FUNCTION master_table_reserves_out()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER reserves_out_on_insert
+  AFTER INSERT
+   ON reserves_out
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
+  CREATE TRIGGER reserves_out_on_delete
+  AFTER DELETE
+    ON reserves_out
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+END $$;
+COMMENT ON FUNCTION master_table_reserves_out()
+  IS 'Setup triggers to replicate reserve_out into reserve_out_by_reserve.';
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -123,8 +200,23 @@ INSERT INTO exchange_tables
     ,'create'
     ,TRUE
     ,FALSE),
-    ('reserves_out_by_reserve' -- FIXME: do like this?
+    ('reserves_out'
+    ,'exchange-0002'
+    ,'constrain'
+    ,TRUE
+    ,FALSE),
+    ('reserves_out'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
+    ,FALSE),
+    ('reserves_out_by_reserve'
     ,'exchange-0002'
     ,'create'
     ,TRUE
+    ,FALSE),
+    ('reserves_out'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);