[taler-merchant] branch master updated: DB schema changes for pg_lookup_pending_deposits.c

[gnunet]

This is an automated email from the git hooks/post-receive script.

grothoff pushed a commit to branch master
in repository merchant.

The following commit(s) were added to refs/heads/master by this push:
     new ced8baa6 DB schema changes for pg_lookup_pending_deposits.c
ced8baa6 is described below

commit ced8baa6d7097367e29d30127d55e90ad3e705c4
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sat Jan 6 15:32:56 2024 +0100

    DB schema changes for pg_lookup_pending_deposits.c
---
 src/backend/.gitignore                     |  1 +
 src/backend/taler-merchant-depositcheck.c  | 19 ++++++++++----
 src/backenddb/merchant-0002.sql            | 24 ++++++++++++++++++
 src/backenddb/pg_lookup_pending_deposits.c | 40 ++++++++++++++++++++++--------
 src/include/taler_merchantdb_plugin.h      |  2 +-
 5 files changed, 69 insertions(+), 17 deletions(-)

diff --git a/src/backend/.gitignore b/src/backend/.gitignore
index 62ef6e0a..69cbfcee 100644
--- a/src/backend/.gitignore
+++ b/src/backend/.gitignore
@@ -1,3 +1,4 @@
 taler-merchant-webhook
 taler-merchant-wirewatch
 taler-merchant-exchange
+taler-merchant-depositcheck
diff --git a/src/backend/taler-merchant-depositcheck.c 
b/src/backend/taler-merchant-depositcheck.c
index 4364c9f5..e130da9e 100644
--- a/src/backend/taler-merchant-depositcheck.c
+++ b/src/backend/taler-merchant-depositcheck.c
@@ -379,7 +379,7 @@ run_at (struct GNUNET_TIME_Absolute deadline)
 /**
  * Function called with detailed wire transfer data.
  *
- * @param cls closure with a `struct TransferQuery *`
+ * @param cls closure with a `struct ExchangeInteraction *`
  * @param dr HTTP response data
  */
 static void
@@ -400,6 +400,7 @@ deposit_get_cb (void *cls,
                   TALER_B2S (&w->coin_pub));
       // FIXME: this must filter this particular entry from the
       // select below for good!
+      // => need to update 'merchant_deposit_confirmations.wire_pending'!
       qs = db_plugin->insert_deposit_to_transfer (db_plugin->cls,
                                                   w->deposit_serial,
                                                   &dr->details.ok);
@@ -424,9 +425,6 @@ deposit_get_cb (void *cls,
                   dr->details.accepted.aml_decision,
                   TALER_B2S (&w->coin_pub));
       now = GNUNET_TIME_timestamp_get ();
-      // FIXME: this must filter this particular entry from the
-      // select below, at least until the KYC/AML request is
-      // satisfied; how will we learn that?
       qs = db_plugin->account_kyc_set_status (
         db_plugin->cls,
         w->instance_id,
@@ -444,6 +442,12 @@ deposit_get_cb (void *cls,
         GNUNET_SCHEDULER_shutdown ();
         return;
       }
+      // FIXME: if kyc_ok and 0==aml_decision, we
+      // should bump the deposit_deadline in the
+      // merchant_deposit_confirmations table just
+      // to avoid running the exact same query immediately
+      // again (in case there is an issue at the exchange)
+      // => update also 'exchange_failure'!
       break;
     }
   default:
@@ -452,7 +456,12 @@ deposit_get_cb (void *cls,
                   "Exchange %s returned tracking failure for deposited coin 
%s\n",
                   exchange_url,
                   TALER_B2S (&w->coin_pub));
-      /* FIXME: how to handle? */
+      // FIXME:
+      // We should bump the deposit_deadline in the
+      // merchant_deposit_confirmations table just
+      // to avoid running the exact same query immediately
+      // again (in case there is an issue at the exchange)
+      // => update also 'exchange_failure'!
       return;
     }
   } /* end switch */
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index 33324c8e..e92ce651 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -30,6 +30,11 @@ ALTER TABLE merchant_orders
    ADD COLUMN fulfillment_url TEXT DEFAULT NULL
   ,ADD COLUMN session_id TEXT DEFAULT '' NOT NULL;
 
+COMMENT ON COLUMN merchant_orders.fulfillment_url
+  IS 'URL where the wallet will redirect the user upon payment';
+COMMENT ON COLUMN merchant_orders.session_id
+  IS 'session_id to which the payment will be bound';
+
 
 CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_session
   ON merchant_orders
@@ -43,6 +48,25 @@ CREATE INDEX IF NOT EXISTS 
merchant_contract_terms_by_merchant_and_session
   ON merchant_contract_terms
   (merchant_serial,session_id);
 
+
+
+ALTER TABLE merchant_deposit_confirmations
+   ADD COLUMN deposit_deadline INT8 DEFAULT (0) NOT NULL,
+   ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL,
+   ADD COLUMN exchange_failure TEXT DEFAULT NULL;
+
+COMMENT ON COLUMN merchant_deposit_confirmations.deposit_deadline
+  IS 'when should the exchange make the wire transfer at the latest';
+COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending
+  IS 'true if we are awaiting wire details for a deposit of this purchase and 
are not blocked on KYC';
+COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure
+  IS 'Text describing exchange failures in making timely wire transfers for 
this deposit confirmation';
+
+CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire
+  ON merchant_deposit_confirmations
+  (exchange_url,deposit_deadline)
+  WHERE wire_pending;
+
 -------------------------- Tokens -----------------------------
 
 CREATE TABLE IF NOT EXISTS merchant_token_families
diff --git a/src/backenddb/pg_lookup_pending_deposits.c 
b/src/backenddb/pg_lookup_pending_deposits.c
index 8f12d5ea..a5103604 100644
--- a/src/backenddb/pg_lookup_pending_deposits.c
+++ b/src/backenddb/pg_lookup_pending_deposits.c
@@ -87,7 +87,7 @@ lookup_deposits_cb (void *cls,
                                             &h_contract_terms),
       GNUNET_PQ_result_spec_auto_from_type ("merchant_priv",
                                             &merchant_priv),
-      GNUNET_PQ_result_spec_string ("instance_id",
+      GNUNET_PQ_result_spec_string ("merchant_id",
                                     &instance_id),
       GNUNET_PQ_result_spec_auto_from_type ("h_wire",
                                             &h_wire),
@@ -154,16 +154,34 @@ TMH_PG_lookup_pending_deposits (
   PREPARE (pg,
            "lookup_pending_deposits",
            "SELECT"
-           " deposit_serial"
-           ",h_contract_terms"
-           ",merchant_priv"
-           ",instance_id"
-           ",h_wire"
-           ",amount_with_fee"
-           ",deposit_fee"
-           ",coin_pub"
-           " FROM merchant_deposits"
-           " FIXME");
+           " md.deposit_serial"
+           ",mct.h_contract_terms"
+           ",mk.merchant_priv"
+           ",mi.merchant_id"
+           ",ma.h_wire"
+           ",md.amount_with_fee"
+           ",md.deposit_fee"
+           ",md.coin_pub"
+           " FROM merchant_deposit_confirmations mdc"
+           " JOIN merchant_contract_terms mct"
+           "  USING (order_serial)"
+           " JOIN merchant_accounts ma"
+           "  USING (account_serial)"
+           " JOIN merchant_kyc kyc"
+           "  ON (ma.account_serial=kyc.account_serial)"
+           " JOIN merchant_instances mi"
+           "  ON (mct.merchant_serial=mi.merchant_serial)"
+           " JOIN merchant_keys mk"
+           "  ON (mi.merchant_serial=mk.merchant_serial)"
+           " JOIN merchant_deposits md"
+           "  USING (deposit_confirmation_serial)"
+           " WHERE mdc.wire_pending"
+           "  AND (mdc.exchange_url=$1)"
+           "  AND ($4 OR (mdc.deposit_deadline < $2))"
+           "  AND kyc.kyc_ok"
+           "  AND (0=kyc.aml_decision)"
+           " ORDER BY mdc.deposit_deadline ASC"
+           " LIMIT $3");
   qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                              "lookup_pending_deposits",
                                              params,
diff --git a/src/include/taler_merchantdb_plugin.h 
b/src/include/taler_merchantdb_plugin.h
index 8635c350..8981a727 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -812,7 +812,7 @@ typedef void
 (*TALER_MERCHANTDB_PendingDepositsCallback) (
   void *cls,
   uint64_t deposit_serial,
-  struct GNUNET_TIME_Absolute wire_deadline, /* missing in DB! Funky migration 
needed! */
+  struct GNUNET_TIME_Absolute wire_deadline,
   const struct TALER_PrivateContractHashP *h_contract_terms,
   const struct TALER_MerchantPrivateKeyP *merchant_priv,
   const char *instance_id,

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.