add triggers to populate reserve history table

12 files changed, 402 insertions, 22 deletions

diff --git a/src/exchangedb/0002-close_requests.sql b/src/exchangedb/0002-close_requests.sql
index d13822ef9..6a7028095 100644
--- a/src/exchangedb/0002-close_requests.sql
+++ b/src/exchangedb/0002-close_requests.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -117,6 +117,38 @@ END
 $$;
 
 
+CREATE OR REPLACE FUNCTION close_requests_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'close_requests'
+    ,NEW.close_request_serial_id);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION close_requests_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_close_requests()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER close_requests_on_insert
+    AFTER INSERT
+     ON close_requests
+     FOR EACH ROW EXECUTE FUNCTION close_requests_insert_trigger();
+END $$;
+
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -138,4 +170,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('close_requests'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql
index d6a81c458..0714e1bea 100644
--- a/src/exchangedb/0002-history_requests.sql
+++ b/src/exchangedb/0002-history_requests.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -99,6 +99,37 @@ BEGIN
 END $$;
 
 
+CREATE OR REPLACE FUNCTION history_requests_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'history_requests'
+    ,NEW.history_request_serial_id);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION history_requests_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_history_requests()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER history_requests_on_insert
+    AFTER INSERT
+     ON history_requests
+     FOR EACH ROW EXECUTE FUNCTION history_requests_insert_trigger();
+END $$;
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -120,4 +151,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('history_requests'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-purse_merges.sql b/src/exchangedb/0002-purse_merges.sql
index 4f1717736..60a2067a7 100644
--- a/src/exchangedb/0002-purse_merges.sql
+++ b/src/exchangedb/0002-purse_merges.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -125,6 +125,37 @@ END
 $$;
 
 
+CREATE OR REPLACE FUNCTION purse_merges_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'purse_merges'
+    ,NEW.purse_merge_request_serial_id);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION purse_merges_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_purse_merges()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER purse_merges_on_insert
+    AFTER INSERT
+     ON purse_merges
+     FOR EACH ROW EXECUTE FUNCTION purse_merges_insert_trigger();
+END $$;
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -146,4 +177,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('purse_merges'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql
index 600091b61..4b3452498 100644
--- a/src/exchangedb/0002-recoup.sql
+++ b/src/exchangedb/0002-recoup.sql
@@ -165,13 +165,13 @@ CREATE FUNCTION recoup_insert_trigger()
   LANGUAGE plpgsql
   AS $$
 BEGIN
-  INSERT INTO exchange.recoup_by_reserve
+  INSERT INTO recoup_by_reserve
     (reserve_out_serial_id
     ,coin_pub)
   VALUES
     (NEW.reserve_out_serial_id
     ,NEW.coin_pub);
-  INSERT INTO exchange.coin_history
+  INSERT INTO coin_history
     (coin_pub
     ,table_name
     ,serial_id)
@@ -179,7 +179,19 @@ BEGIN
     (NEW.coin_pub
     ,'recoup'
     ,NEW.recoup_uuid);
- RETURN NEW;
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  SELECT
+     res.reserve_pub
+    ,'recoup'
+    ,NEW.recoup_uuid
+  FROM reserves_out rout
+  JOIN reserves res
+    USING (reserve_uuid)
+    WHERE rout.reserve_out_serial_id = NEW.reserve_out_serial_id;
+  RETURN NEW;
 END $$;
 COMMENT ON FUNCTION recoup_insert_trigger()
   IS 'Replicates recoup inserts into recoup_by_reserve table and updates the coin_history table.';
@@ -190,7 +202,7 @@ CREATE FUNCTION recoup_delete_trigger()
   LANGUAGE plpgsql
   AS $$
 BEGIN
-  DELETE FROM exchange.recoup_by_reserve
+  DELETE FROM recoup_by_reserve
    WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
      AND coin_pub = OLD.coin_pub;
   RETURN OLD;
diff --git a/src/exchangedb/0002-reserve_history.sql b/src/exchangedb/0002-reserve_history.sql
new file mode 100644
index 000000000..b0c764306
--- /dev/null
+++ b/src/exchangedb/0002-reserve_history.sql
@@ -0,0 +1,138 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2023 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE FUNCTION create_table_reserve_history (
+  IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name TEXT DEFAULT 'reserve_history';
+BEGIN
+  PERFORM create_partitioned_table(
+    'CREATE TABLE %I'
+      '(reserve_history_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY'
+      ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)'
+      ',table_name TEXT NOT NULL'
+      ',serial_id INT8 NOT NULL'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (reserve_pub)'
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_table(
+    'Links to tables with entries that affected the transaction history of a reserve.'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'For which reserve is this a history entry'
+    ,'reserve_pub'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'In which table is the history entry'
+    ,'table_name'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Which is the generated serial ID of the entry in the table'
+    ,'serial_id'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Monotonic counter, used to generate Etags for caching'
+    ,'reserve_history_serial_id'
+    ,table_name
+    ,partition_suffix
+  );
+END
+$$;
+
+
+CREATE FUNCTION constrain_table_reserve_history(
+  IN partition_suffix TEXT
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name TEXT DEFAULT 'reserve_history';
+BEGIN
+  table_name = concat_ws('_', table_name, partition_suffix);
+  EXECUTE FORMAT (
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_reserve_history_serial_id_pkey'
+    ' PRIMARY KEY (reserve_history_serial_id) '
+    ',ADD CONSTRAINT ' || table_name || '_reserve_entry_key'
+    ' UNIQUE (reserve_pub, table_name, serial_id)'
+  );
+  EXECUTE FORMAT (
+    'CREATE INDEX ' || table_name || '_reserve_by_time'
+    ' ON ' || table_name || ' '
+    '(reserve_pub'
+    ',reserve_history_serial_id DESC'
+    ');'
+  );
+END
+$$;
+
+
+CREATE FUNCTION foreign_table_reserve_history()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name TEXT DEFAULT 'reserve_history';
+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
+    (name
+    ,version
+    ,action
+    ,partitioned
+    ,by_range)
+  VALUES
+    ('reserve_history'
+    ,'exchange-0002'
+    ,'create'
+    ,TRUE
+    ,FALSE),
+    ('reserve_history'
+    ,'exchange-0002'
+    ,'constrain'
+    ,TRUE
+    ,FALSE),
+    ('reserve_history'
+    ,'exchange-0002'
+    ,'foreign'
+    ,TRUE
+    ,FALSE)
+    ;
diff --git a/src/exchangedb/0002-reserves_close.sql b/src/exchangedb/0002-reserves_close.sql
index 2db4e5ccd..16669768d 100644
--- a/src/exchangedb/0002-reserves_close.sql
+++ b/src/exchangedb/0002-reserves_close.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -91,6 +91,37 @@ BEGIN
 END $$;
 
 
+CREATE OR REPLACE FUNCTION reserves_close_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'reserves_close'
+    ,NEW.close_uuid);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_close_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_reserves_close()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER reserves_close_on_insert
+    AFTER INSERT
+     ON reserves_close
+     FOR EACH ROW EXECUTE FUNCTION reserves_close_insert_trigger();
+END $$;
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -112,4 +143,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('reserves_close'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-reserves_in.sql b/src/exchangedb/0002-reserves_in.sql
index 7fc2811dc..1dfc5db69 100644
--- a/src/exchangedb/0002-reserves_in.sql
+++ b/src/exchangedb/0002-reserves_in.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -118,6 +118,38 @@ BEGIN
 END $$;
 
 
+
+CREATE OR REPLACE FUNCTION reserves_in_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'reserves_in'
+    ,NEW.reserve_in_serial_id);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_in_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_reserves_in()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER reserves_in_on_insert
+    AFTER INSERT
+     ON reserves_in
+     FOR EACH ROW EXECUTE FUNCTION reserves_in_insert_trigger();
+END $$;
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -139,4 +171,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('reserves_in'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-reserves_open_requests.sql b/src/exchangedb/0002-reserves_open_requests.sql
index c8f9c22e2..b51168dc0 100644
--- a/src/exchangedb/0002-reserves_open_requests.sql
+++ b/src/exchangedb/0002-reserves_open_requests.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -90,6 +90,37 @@ END
 $$;
 
 
+CREATE OR REPLACE FUNCTION reserves_open_requests_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  VALUES
+    (NEW.reserve_pub
+    ,'reserves_open_requests'
+    ,NEW.open_request_uuid);
+  RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_open_requests_insert_trigger()
+  IS 'Automatically generate reserve history entry.';
+
+
+CREATE FUNCTION master_table_reserves_open_requests()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  CREATE TRIGGER reserves_open_requests_on_insert
+    AFTER INSERT
+     ON reserves_open_requests
+     FOR EACH ROW EXECUTE FUNCTION reserves_open_requests_insert_trigger();
+END $$;
+
+
 INSERT INTO exchange_tables
     (name
     ,version
@@ -111,4 +142,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
+    ,FALSE),
+    ('reserves_open_requests'
+    ,'exchange-0002'
+    ,'master'
+    ,TRUE
     ,FALSE);
diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql
index 7c5cf554c..1e4038957 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--2023 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
@@ -157,12 +157,23 @@ CREATE FUNCTION reserves_out_by_reserve_insert_trigger()
   LANGUAGE plpgsql
   AS $$
 BEGIN
-  INSERT INTO exchange.reserves_out_by_reserve
+  INSERT INTO reserves_out_by_reserve
     (reserve_uuid
     ,h_blind_ev)
   VALUES
     (NEW.reserve_uuid
     ,NEW.h_blind_ev);
+  INSERT INTO reserve_history
+    (reserve_pub
+    ,table_name
+    ,serial_id)
+  SELECT
+     res.reserve_pub
+    ,'reserves_out'
+    ,NEW.reserve_out_serial_id
+  FROM
+    reserves res
+  WHERE res.reserve_uuid = NEW.reserve_uuid;
   RETURN NEW;
 END $$;
 COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
@@ -174,7 +185,7 @@ CREATE FUNCTION reserves_out_by_reserve_delete_trigger()
   LANGUAGE plpgsql
   AS $$
 BEGIN
-  DELETE FROM exchange.reserves_out_by_reserve
+  DELETE FROM reserves_out_by_reserve
    WHERE reserve_uuid = OLD.reserve_uuid;
   RETURN OLD;
 END $$;
diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in
index 6e0d5ae58..e209d5504 100644
--- a/src/exchangedb/exchange-0002.sql.in
+++ b/src/exchangedb/exchange-0002.sql.in
@@ -63,6 +63,7 @@ COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
 #include "0002-legitimization_processes.sql"
 #include "0002-legitimization_requirements.sql"
 #include "0002-reserves.sql"
+#include "0002-reserve_history.sql"
 #include "0002-reserves_in.sql"
 #include "0002-reserves_close.sql"
 #include "0002-close_requests.sql"
diff --git a/src/exchangedb/pg_get_reserve_history.c b/src/exchangedb/pg_get_reserve_history.c
index 86a33946d..c0cb08f59 100644
--- a/src/exchangedb/pg_get_reserve_history.c
+++ b/src/exchangedb/pg_get_reserve_history.c
@@ -648,7 +648,7 @@ TEH_PG_get_reserve_history (void *cls,
     GNUNET_PQ_query_param_end
   };
 
-  PREPARE (pg,
+  PREPARE (pg, // done
            "reserves_in_get_transactions",
            /*
            "SELECT"
@@ -677,7 +677,7 @@ TEH_PG_get_reserve_history (void *cls,
            "WHERE wire_target_h_payto = ( "
            "  SELECT wire_source_h_payto FROM ri "
            "); ");
-  PREPARE (pg,
+  PREPARE (pg, // DONE
            "get_reserves_out",
            /*
            "SELECT"
@@ -718,7 +718,7 @@ TEH_PG_get_reserve_history (void *cls,
            "   ON (ro.h_blind_ev = robr.h_blind_ev)"
            " JOIN denominations denom"
            "   ON (ro.denominations_serial = denom.denominations_serial);");
-  PREPARE (pg,
+  PREPARE (pg, // DONE
            "recoup_by_reserve",
            /*
            "SELECT"
@@ -767,7 +767,7 @@ TEH_PG_get_reserve_history (void *cls,
            "   JOIN wire_targets"
            "     USING (wire_target_h_payto)"
            " WHERE reserve_pub=$1;");
-  PREPARE (pg,
+  PREPARE (pg, // DONE
            "merge_by_reserve",
            "SELECT"
            " pr.amount_with_fee"
@@ -792,7 +792,7 @@ TEH_PG_get_reserve_history (void *cls,
            " WHERE pm.reserve_pub=$1"
            "  AND COALESCE(pm.partner_serial_id,0)=0" /* must be local! */
            "  AND NOT COALESCE (pdes.refunded, FALSE);");
-  PREPARE (pg,
+  PREPARE (pg, // done
            "history_by_reserve",
            "SELECT"
            " history_fee"
@@ -800,7 +800,7 @@ TEH_PG_get_reserve_history (void *cls,
            ",reserve_sig"
            " FROM history_requests"
            " WHERE reserve_pub=$1;");
-  PREPARE (pg,
+  PREPARE (pg, // done
            "open_request_by_reserve",
            "SELECT"
            " reserve_payment"
@@ -810,7 +810,7 @@ TEH_PG_get_reserve_history (void *cls,
            ",reserve_sig"
            " FROM reserves_open_requests"
            " WHERE reserve_pub=$1;");
-  PREPARE (pg,
+  PREPARE (pg, // done
            "close_request_by_reserve",
            "SELECT"
            " close_timestamp"
diff --git a/src/exchangedb/test_exchangedb.c b/src/exchangedb/test_exchangedb.c
index 90c539a98..56925acf0 100644
--- a/src/exchangedb/test_exchangedb.c
+++ b/src/exchangedb/test_exchangedb.c
@@ -33,9 +33,9 @@ static int result;
 /**
  * Report line of error if @a cond is true, and jump to label "drop".
  */
-#define FAILIF(cond)                              \
+#define FAILIF(cond)                            \
   do {                                          \
-    if (! (cond)) { break;}                      \
+    if (! (cond)) { break;}                     \
     GNUNET_break (0);                           \
     goto drop;                                  \
   } while (0)