complete taler-merchant-depositcheck, 1st iteration, without testing

11 files changed, 248 insertions, 21 deletions

diff --git a/src/backenddb/.gitignore b/src/backenddb/.gitignore
new file mode 100644
index 00000000..e3c1e14d
--- /dev/null
+++ b/src/backenddb/.gitignore
@@ -0,0 +1 @@
+procedures.sql
diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am
index 8681d0ec..bb559ced 100644
--- a/src/backenddb/Makefile.am
+++ b/src/backenddb/Makefile.am
@@ -10,6 +10,10 @@ pkgcfg_DATA = \
 
 sqldir = $(prefix)/share/taler/sql/merchant/
 
+sqlinputs = \
+  pg_*.sql \
+  procedures.sql.in
+
 sql_DATA = \
   versioning.sql \
   merchant-0001.sql \
@@ -19,6 +23,15 @@ sql_DATA = \
   merchant-0005.sql \
   drop.sql
 
+BUILT_SOURCES = \
+  procedures.sql
+
+procedures.sql: procedures.sql.in pg_*.sql
+	chmod +w $@ || true
+	gcc -E -P -undef - < procedures.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@
+	chmod ugo-w $@
+
+
 if HAVE_POSTGRESQL
 if HAVE_GNUNETPQ
 plugin_LTLIBRARIES = \
@@ -86,6 +99,7 @@ libtaler_plugin_merchantdb_postgres_la_SOURCES = \
   pg_delete_instance_private_key.h pg_delete_instance_private_key.c \
   pg_purge_instance.h pg_purge_instance.c \
   pg_update_instance.h pg_update_instance.c \
+  pg_update_deposit_confirmation_status.h pg_update_deposit_confirmation_status.c \
   pg_update_instance_auth.h pg_update_instance_auth.c \
   pg_inactivate_account.h pg_inactivate_account.c \
   pg_activate_account.h pg_activate_account.c \
@@ -216,4 +230,5 @@ TESTS = \
 EXTRA_DIST = \
   test-merchantdb-postgres.conf \
   merchantdb-postgres.conf \
+  $(sqlinputs) \
   $(sql_DATA)
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index e92ce651..e5e91631 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -67,6 +67,11 @@ CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire
   (exchange_url,deposit_deadline)
   WHERE wire_pending;
 
+CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial
+  ON merchant_deposits
+  (deposit_confirmation_serial);
+
+
 -------------------------- Tokens -----------------------------
 
 CREATE TABLE IF NOT EXISTS merchant_token_families
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.c b/src/backenddb/pg_insert_deposit_to_transfer.c
index ddd9d254..1baf8963 100644
--- a/src/backenddb/pg_insert_deposit_to_transfer.c
+++ b/src/backenddb/pg_insert_deposit_to_transfer.c
@@ -36,29 +36,32 @@ TMH_PG_insert_deposit_to_transfer (
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_uint64 (&deposit_serial),
     TALER_PQ_query_param_amount_with_currency (pg->conn,
-                                 &dd->coin_contribution),
+                                               &dd->coin_contribution),
     GNUNET_PQ_query_param_timestamp (&dd->execution_time),
     GNUNET_PQ_query_param_auto_from_type (&dd->exchange_sig),
     GNUNET_PQ_query_param_auto_from_type (&dd->exchange_pub),
     GNUNET_PQ_query_param_auto_from_type (&dd->wtid),
     GNUNET_PQ_query_param_end
   };
+  bool wpc;
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_bool ("out_wire_pending_cleared",
+                                &wpc),
+    GNUNET_PQ_result_spec_end
+  };
+  enum GNUNET_DB_QueryStatus qs;
 
   PREPARE (pg,
            "insert_deposit_to_transfer",
-           "INSERT INTO merchant_deposit_to_transfer"
-           "(deposit_serial"
-           ",coin_contribution_value"
-           ",credit_serial"
-           ",execution_time"
-           ",signkey_serial"
-           ",exchange_sig"
-           ") SELECT $1, $2, credit_serial, $3, signkey_serial, $4"
-           " FROM merchant_transfers"
-           " CROSS JOIN merchant_exchange_signing_keys"
-           " WHERE exchange_pub=$5"
-           "   AND wtid=$6");
-  return GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "insert_deposit_to_transfer",
-                                             params);
+           "SELECT out_wire_cleared"
+           " FROM merchant_insert_deposit_to_transfer"
+           " ($1,$2,$3,$4,$5,$6);");
+  qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                 "insert_deposit_to_transfer",
+                                                 params,
+                                                 rs);
+  if (wpc)
+    GNUNET_log (GNUNET_ERROR_TYPE_INFO,
+                "Wire pending flag cleared (good!)\n");
+  return qs;
 }
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.sql b/src/backenddb/pg_insert_deposit_to_transfer.sql
new file mode 100644
index 00000000..ddc291c3
--- /dev/null
+++ b/src/backenddb/pg_insert_deposit_to_transfer.sql
@@ -0,0 +1,75 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 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 OR REPLACE FUNCTION merchant_insert_deposit_to_transfer (
+  IN in_deposit_serial INT8,
+  IN in_amount_with_fee taler_amount_currency,
+  IN in_execution_time INT8,
+  IN in_exchange_sig BYTEA,
+  IN in_exchange_pub BYTEA,
+  IN in_wtid BYTEA,
+  OUT out_wire_pending_cleared BOOL)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  decose INT8;
+BEGIN
+
+out_wire_pending_cleared=FALSE;
+INSERT INTO merchant_deposit_to_transfer
+  (deposit_serial
+  ,coin_contribution_value
+  ,credit_serial
+  ,execution_time
+  ,signkey_serial
+  ,exchange_sig
+  )
+  SELECT
+    in_deposit_serial
+   ,in_amount_with_fee
+   ,credit_serial
+   ,in_execution_time
+   ,signkey_serial
+   ,in_exchange_sig
+   FROM merchant_transfers
+     CROSS JOIN merchant_exchange_signing_keys
+     WHERE exchange_pub=in_exchange_pub
+     AND wtid=in_wtid
+  ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+  SELECT deposit_confirmation_serial
+    INTO decose
+    FROM merchant_deposits
+   WHERE deposit_serial=in_deposit_serial;
+
+  -- we made a change, check about clearing wire_pending
+  UPDATE merchant_deposit_confirmations
+    SET wire_pending=FALSE
+    WHERE (deposit_confirmation_serial=decose)
+    AND NOT EXISTS (
+      SELECT *
+        FROM merchant_deposits md
+        LEFT JOIN merchant_deposit_to_transfer mdtt
+          USING (deposit_serial)
+        WHERE md.deposit_confirmation_serial=decose
+          AND mdtt.credit_serial IS NULL);
+  out_wire_pending_cleared=FOUND;
+END IF;
+
+END $$;
diff --git a/src/backenddb/pg_template.c b/src/backenddb/pg_template.c
index 1a7f639b..6f9ed277 100644
--- a/src/backenddb/pg_template.c
+++ b/src/backenddb/pg_template.c
@@ -1,6 +1,6 @@
 /*
    This file is part of TALER
-   Copyright (C) 2023 Taler Systems SA
+   Copyright (C) 2024 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
diff --git a/src/backenddb/pg_template.h b/src/backenddb/pg_template.h
index 510a8faa..75e10d6e 100644
--- a/src/backenddb/pg_template.h
+++ b/src/backenddb/pg_template.h
@@ -1,6 +1,6 @@
 /*
    This file is part of TALER
-   Copyright (C) 2023 Taler Systems SA
+   Copyright (C) 2024 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
diff --git a/src/backenddb/pg_update_deposit_confirmation_status.c b/src/backenddb/pg_update_deposit_confirmation_status.c
new file mode 100644
index 00000000..cd0e827b
--- /dev/null
+++ b/src/backenddb/pg_update_deposit_confirmation_status.c
@@ -0,0 +1,58 @@
+/*
+   This file is part of TALER
+   Copyright (C) 2024 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/>
+ */
+/**
+ * @file backenddb/pg_update_deposit_confirmation_status.c
+ * @brief Implementation of the update_deposit_confirmation_status function for Postgres
+ * @author Christian Grothoff
+ */
+#include "platform.h"
+#include <taler/taler_error_codes.h>
+#include <taler/taler_dbevents.h>
+#include <taler/taler_pq_lib.h>
+#include "pg_update_deposit_confirmation_status.h"
+#include "pg_helper.h"
+
+
+enum GNUNET_DB_QueryStatus
+TMH_PG_update_deposit_confirmation_status (
+  void *cls,
+  uint64_t deposit_serial,
+  struct GNUNET_TIME_Absolute future_retry,
+  const char *emsg)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_uint64 (&deposit_serial),
+    GNUNET_PQ_query_param_absolute_time (&future_retry),
+    GNUNET_PQ_query_param_string (emsg),
+    GNUNET_PQ_query_param_end
+  };
+
+  check_connection (pg);
+  PREPARE (pg,
+           "update_deposit_confirmation_status",
+           "UPDATE merchant_deposit_confirmations SET"
+           " deposit_deadline_type=$2"
+           ",exchange_failure=$3"
+           " WHERE deposit_confirmation_serial="
+           "   (SELECT deposit_confirmation_serial"
+           "      FROM merchant_deposits"
+           "      WHERE deposit_serial=$1);");
+  return GNUNET_PQ_eval_prepared_non_select (
+    pg->conn,
+    "update_deposit_confirmation_status",
+    params);
+}
diff --git a/src/backenddb/pg_update_deposit_confirmation_status.h b/src/backenddb/pg_update_deposit_confirmation_status.h
new file mode 100644
index 00000000..59526ad9
--- /dev/null
+++ b/src/backenddb/pg_update_deposit_confirmation_status.h
@@ -0,0 +1,47 @@
+/*
+   This file is part of TALER
+   Copyright (C) 2024 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/>
+ */
+/**
+ * @file backenddb/pg_update_deposit_confirmation_status.h
+ * @brief implementation of the update_deposit_confirmation_status function for Postgres
+ * @author Christian Grothoff
+ */
+#ifndef PG_UPDATE_DEPOSIT_CONFIRMATION_STATUS_H
+#define PG_UPDATE_DEPOSIT_CONFIRMATION_STATUS_H
+
+#include <taler/taler_util.h>
+#include <taler/taler_json_lib.h>
+#include "taler_merchantdb_plugin.h"
+
+
+/**
+ * Update the deposit confirmation status associated with
+ * the given @a deposit_serial.
+ *
+ * @param cls closure
+ * @param deposit_serial deposit to update status for
+ * @param future_retry when should we ask the exchange again
+ * @param emsg error message to record
+ * @return database result code
+ */
+enum GNUNET_DB_QueryStatus
+TMH_PG_update_deposit_confirmation_status (
+  void *cls,
+  uint64_t deposit_serial,
+  struct GNUNET_TIME_Absolute future_retry,
+  const char *emsg);
+
+
+#endif
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index e57e617f..291008d0 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -132,8 +132,7 @@
 #include "pg_insert_pending_webhook.h"
 #include "pg_update_pending_webhook.h"
 #include "pg_lookup_pending_webhooks.h"
-// ^^^^^ + lookup_future_webhook
-// ^^^^^ + lookup_all_webhooks
+#include "pg_update_deposit_confirmation_status.h"
 #include "pg_set_transfer_status_to_confirmed.h"
 #include "pg_insert_exchange_keys.h"
 #include "pg_select_exchange_keys.h"
@@ -615,7 +614,8 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
     = &TMH_PG_delete_token_family;
   plugin->update_token_family
     = &TMH_PG_update_token_family;
-
+  plugin->update_deposit_confirmation_status
+    = &TMH_PG_update_deposit_confirmation_status;
 
   return plugin;
 }
diff --git a/src/backenddb/procedures.sql.in b/src/backenddb/procedures.sql.in
new file mode 100644
index 00000000..365bc94b
--- /dev/null
+++ b/src/backenddb/procedures.sql.in
@@ -0,0 +1,23 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 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/>
+--
+
+BEGIN;
+
+SET search_path TO merchant;
+
+#include "pg_insert_deposit_to_transfer.sql"
+
+COMMIT;