more SQL

1 files changed, 202 insertions, 32 deletions

diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index d1fc4c19..c196790c 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -2910,7 +2910,7 @@ RETRY:
  *
  * @param cls closure
  * @param master_pub public key of the exchange
- * @param h_wire_method hash of wire method
+ * @param wire_method the wire method
  * @param contract_date date of the contract to use for the lookup
  * @param[out] wire_fee wire fee charged
  * @param[out] closing_fee closing fee charged (irrelevant for us,
@@ -2932,9 +2932,10 @@ postgres_lookup_wire_fee (void *cls,
                           struct TALER_MasterSignatureP *master_sig)
 {
   struct PostgresClosure *pg = cls;
+  struct GNUNET_HashCode h_wire_method;
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_auto_from_type (master_pub),
-    GNUNET_PQ_query_param_string (wire_method), // FIXME: hash first?
+    GNUNET_PQ_query_param_auto_from_type (&h_wire_method),
     GNUNET_PQ_query_param_absolute_time (&contract_date),
     GNUNET_PQ_query_param_end
   };
@@ -2953,7 +2954,9 @@ postgres_lookup_wire_fee (void *cls,
   };
 
   check_connection (pg);
-  // FIXME: SQL needs updating: wire_method, etc!
+  GNUNET_CRYPTO_hash (wire_method,
+                      strlen (wire_method) + 1,
+                      &h_wire_method);
   return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
                                                    "lookup_wire_fee",
                                                    params,
@@ -3102,12 +3105,12 @@ postgres_lookup_deposits_by_contract_and_coin (
   enum GNUNET_DB_QueryStatus qs;
 
   check_connection (pg);
-  qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
-                                             "lookup_deposits_by_contract_and_coin",
-                                             params,
-                                             &
-                                             lookup_deposits_by_contract_and_coin_cb,
-                                             &ldcc);
+  qs = GNUNET_PQ_eval_prepared_multi_select (
+    pg->conn,
+    "lookup_deposits_by_contract_and_coin",
+    params,
+    &lookup_deposits_by_contract_and_coin_cb,
+    &ldcc);
   if (0 >= qs)
     return qs;
   return ldcc.qs;
@@ -3115,16 +3118,93 @@ postgres_lookup_deposits_by_contract_and_coin (
 
 
 /**
+ * Closure for #lookup_transfer_details_cb().
+ */
+struct LookupTransferDetailsContext
+{
+  /**
+   * Function to call for each order that was aggregated.
+   */
+  TALER_MERCHANTDB_TransferDetailsCallback cb;
+
+  /**
+   * Closure for @e cb.
+   */
+  void *cb_cls;
+
+  /**
+   * Plugin context.
+   */
+  struct PostgresClosure *pg;
+
+  /**
+   * Transaction result.
+   */
+  enum GNUNET_DB_QueryStatus qs;
+};
+
+
+/**
+ * Function to be called with the results of a SELECT statement
+ * that has returned @a num_results results.
+ *
+ * @param cls of type `struct LookupTransferDetailsContext *`
+ * @param result the postgres result
+ * @param num_result the number of results in @a result
+ */
+static void
+lookup_transfer_details_cb (void *cls,
+                            PGresult *result,
+                            unsigned int num_results)
+{
+  struct LookupTransferDetailsContext *ltdc = cls;
+  struct PostgresClosure *pg = ltdc->pg;
+
+  for (unsigned int i = 0; i<num_results; i++)
+  {
+    char *order_id;
+    struct TALER_Amount deposit_value;
+    struct TALER_Amount deposit_fee;
+    struct GNUNET_PQ_ResultSpec rs[] = {
+      GNUNET_PQ_result_spec_string ("order_id",
+                                    &order_id),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("exchange_deposit_value",
+                                   &deposit_value),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("exchange_deposit_fee",
+                                   &deposit_fee),
+      GNUNET_PQ_result_spec_end
+    };
+
+    if (GNUNET_OK !=
+        GNUNET_PQ_extract_result (result,
+                                  rs,
+                                  i))
+    {
+      GNUNET_break (0);
+      ltdc->qs = GNUNET_DB_STATUS_HARD_ERROR;
+      return;
+    }
+    ltdc->qs = i + 1;
+    ltdc->cb (ltdc->cb_cls,
+              order_id,
+              &deposit_value,
+              &deposit_fee);
+    GNUNET_PQ_cleanup_result (rs);
+  }
+}
+
+
+/**
  * Lookup transfer details.
  *
  * @param cls closure
  * @param instance_id instance to lookup payments for
- * @param exchange_url
- * @param payto_uri
- * @param wtid
- * @param total_amount
- * @param wire_fee
- * @param execution_time
+ * @param exchange_url the exchange that made the transfer
+ * @param payto_uri account that received the transfer
+ * @param wtid wire transfer subject
+ * @param total_amount amount that was transferred (in total, minus @a wire_fee)
+ * @param wire_fee the wire fee the exchange charged
+ * @param execution_time when the transfer was executed by the exchange
  * @param cb function to call with detailed transfer data
  * @param cb_cls closure for @a cb
  * @return transaction status
@@ -3142,6 +3222,42 @@ postgres_lookup_transfer_details (
   TALER_MERCHANTDB_TransferDetailsCallback cb,
   void *cb_cls)
 {
+  struct PostgresClosure *pg = cls;
+  struct TALER_Amount transfer_amount;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_string (instance_id),
+    GNUNET_PQ_query_param_string (exchange_url),
+    GNUNET_PQ_query_param_string (payto_uri),
+    GNUNET_PQ_query_param_auto_from_type (wtid),
+    TALER_PQ_query_param_amount (&transfer_amount),
+    GNUNET_PQ_query_param_absolute_time (&execution_time),
+    GNUNET_PQ_query_param_end
+  };
+  struct LookupTransferDetailsContext ltdc = {
+    .cb  = cb,
+    .cb_cls = cb_cls,
+    .pg = pg
+  };
+  enum GNUNET_DB_QueryStatus qs;
+
+  if (0 >
+      TALER_amount_subtract (&transfer_amount,
+                             total_amount,
+                             wire_fee))
+  {
+    GNUNET_break (0);
+    return GNUNET_DB_STATUS_HARD_ERROR;
+  }
+  check_connection (pg);
+  qs = GNUNET_PQ_eval_prepared_multi_select (
+    pg->conn,
+    "lookup_transfer_details",
+    params,
+    &lookup_transfer_details_cb,
+    &ltdc);
+  if (0 >= qs)
+    return qs;
+  return ltdc.qs;
 }
 
 
@@ -5554,8 +5670,8 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
                             " FROM merchant_transfers"
                             " WHERE exchange_url=$1"
                             "   AND wtid=$4"
-                            "   AND credit_account_val=$5"
-                            "   AND credit_account_frac=$6"
+                            "   AND credit_amount_val=$5"
+                            "   AND credit_amount_frac=$6"
                             "   AND account_serial="
                             "        (SELECT account_serial"
                             "           FROM merchant_accounts"
@@ -5595,6 +5711,75 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
                             " WHERE coin_pub=$7"
                             "   AND h_contract_terms=$8",
                             8),
+    /* for postgres_lookup_wire_fee() */
+    GNUNET_PQ_make_prepare ("lookup_wire_fee",
+                            "SELECT"
+                            " wire_fee_val"
+                            ",wire_fee_frac"
+                            ",closing_fee_val"
+                            ",closing_fee_frac"
+                            ",start_date"
+                            ",end_date"
+                            ",master_sig"
+                            " FROM merchant_exchange_wire_fees"
+                            " WHERE master_pub=$1"
+                            "   AND h_wire_method=$2"
+                            "   AND start_date <= $3"
+                            "   AND end_date > $3",
+                            3),
+    /* for postgres_lookup_deposits_by_contract_and_coin() */
+    GNUNET_PQ_make_prepare ("lookup_deposits_by_contract_and_coin",
+                            "SELECT"
+                            " exchange_url"
+                            ",amount_with_fee_val"
+                            ",amount_with_fee_frac"
+                            ",deposit_fee_val"
+                            ",deposit_fee_frac"
+                            ",refund_fee_val"
+                            ",refund_fee_frac"
+                            ",wire_fee_val"
+                            ",wire_fee_frac"
+                            ",h_wire"
+                            ",deposit_timestamp"
+                            ",refund_deadline"
+                            ",exchange_sig"
+                            ",exchange_pub"
+                            " FROM merchant_contract_terms"
+                            "  JOIN merchant_deposits USING (order_serial)"
+                            "  JOIN merchant_exchange_signing_keys USING (signkey_serial)"
+                            "  JOIN merchant_accounts USING (account_serial)"
+                            " WHERE h_contract_terms=$2"
+                            "   AND coin_pub=$3"
+                            "   AND merchant_contract_terms.merchant_serial="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            3),
+    /* for postgres_lookup_transfer_details() */
+    GNUNET_PQ_make_prepare ("lookup_transfer_details",
+                            "SELECT"
+                            " order_id"
+                            ",exchange_deposit_value_val"
+                            ",exchange_deposit_value_frac"
+                            ",exchange_deposit_fee_val"
+                            ",exchange_deposit_fee_frac"
+                            " FROM merchant_transfers"
+                            "  JOIN merchant_accounts USING (account_serial)"
+                            "  JOIN merchant_transfer_to_coin USING (credit_serial)"
+                            "  JOIN merchant_transfer_signatures USING (credit_serial)"
+                            "  JOIN merchant_deposits USING (deposit_serial)"
+                            "  JOIN merchant_contract_terms USING (order_serial)"
+                            " WHERE wtid=$4"
+                            "   AND merchant_transfers.exchange_url=$2"
+                            "   AND credit_amount_val=$5"
+                            "   AND credit_amount_frac=$6"
+                            "   AND payto_uri=$3"
+                            "   AND execution_time=$7"
+                            "   AND merchant_contract_terms.merchant_serial="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            7),
     /* OLD API: */
 
 #if 0
@@ -5632,21 +5817,6 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
                             " VALUES "
                             "($1, $2, $3, $4, $5, $6, $7, $8, $9)",
                             9),
-    GNUNET_PQ_make_prepare ("lookup_wire_fee",
-                            "SELECT"
-                            " wire_fee_val"
-                            ",wire_fee_frac"
-                            ",closing_fee_val"
-                            ",closing_fee_frac"
-                            ",start_date"
-                            ",end_date"
-                            ",exchange_sig"
-                            " FROM exchange_wire_fees"
-                            " WHERE exchange_pub=$1"
-                            "   AND h_wire_method=$2"
-                            "   AND start_date <= $3"
-                            "   AND end_date > $3",
-                            1),
     GNUNET_PQ_make_prepare ("find_contract_terms_from_hash",
                             "SELECT"
                             " contract_terms"