skeleton for batch withdraw logic (not finished)

2 files changed, 382 insertions, 0 deletions

diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index 9ca66cd4d..56f1df295 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -1509,6 +1509,245 @@ COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA, BYTEA,
 
 
 
+
+CREATE OR REPLACE FUNCTION exchange_do_batch_withdraw(
+  IN amount_val INT8,
+  IN amount_frac INT4,
+  IN rpub BYTEA,
+  IN now INT8,
+  IN min_reserve_gc INT8,
+  OUT reserve_found BOOLEAN,
+  OUT balance_ok BOOLEAN,
+  OUT kycok BOOLEAN,
+  OUT account_uuid INT8,
+  OUT ruuid INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  reserve_gc INT8;
+DECLARE
+  reserve_val INT8;
+DECLARE
+  reserve_frac INT4;
+BEGIN
+-- Shards: reserves by reserve_pub (SELECT)
+--         reserves_out (INSERT, with CONFLICT detection) by wih
+--         reserves by reserve_pub (UPDATE)
+--         reserves_in by reserve_pub (SELECT)
+--         wire_targets by wire_target_h_payto
+
+SELECT
+   current_balance_val
+  ,current_balance_frac
+  ,gc_date
+  ,reserve_uuid
+ INTO
+   reserve_val
+  ,reserve_frac
+  ,reserve_gc
+  ,ruuid
+  FROM reserves
+ WHERE reserves.reserve_pub=rpub;
+
+IF NOT FOUND
+THEN
+  -- reserve unknown
+  reserve_found=FALSE;
+  balance_ok=FALSE;
+  kycok=FALSE;
+  account_uuid=0;
+  ruuid=2;
+  RETURN;
+END IF;
+
+-- Check reserve balance is sufficient.
+IF (reserve_val > amount_val)
+THEN
+  IF (reserve_frac >= amount_frac)
+  THEN
+    reserve_val=reserve_val - amount_val;
+    reserve_frac=reserve_frac - amount_frac;
+  ELSE
+    reserve_val=reserve_val - amount_val - 1;
+    reserve_frac=reserve_frac + 100000000 - amount_frac;
+  END IF;
+ELSE
+  IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac)
+  THEN
+    reserve_val=0;
+    reserve_frac=reserve_frac - amount_frac;
+  ELSE
+    reserve_found=TRUE;
+    balance_ok=FALSE;
+    kycok=FALSE; -- we do not really know or care
+    account_uuid=0;
+    RETURN;
+  END IF;
+END IF;
+
+-- Calculate new expiration dates.
+min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc);
+
+-- Update reserve balance.
+UPDATE reserves SET
+  gc_date=min_reserve_gc
+ ,current_balance_val=reserve_val
+ ,current_balance_frac=reserve_frac
+WHERE
+  reserves.reserve_pub=rpub;
+
+reserve_found=TRUE;
+balance_ok=TRUE;
+
+
+-- Obtain KYC status based on the last wire transfer into
+-- this reserve. FIXME: likely not adequate for reserves that got P2P transfers!
+-- SELECT
+--    kyc_ok
+--   ,wire_target_serial_id
+--   INTO
+--    kycok
+--   ,account_uuid
+--   FROM reserves_in
+--   JOIN wire_targets ON (wire_source_h_payto = wire_target_h_payto)
+--  WHERE reserve_pub=rpub
+--  LIMIT 1; -- limit 1 should not be required (without p2p transfers)
+
+WITH reserves_in AS materialized (
+  SELECT wire_source_h_payto
+  FROM reserves_in WHERE
+  reserve_pub=rpub
+)
+SELECT
+  kyc_ok
+  ,wire_target_serial_id
+INTO
+  kycok
+  ,account_uuid
+FROM wire_targets
+  WHERE wire_target_h_payto = (
+    SELECT wire_source_h_payto
+      FROM reserves_in
+  );
+
+END $$;
+
+COMMENT ON FUNCTION exchange_do_batch_withdraw(INT8, INT4, BYTEA, INT8, INT8)
+  IS 'Checks whether the reserve has sufficient balance for a withdraw operation (or the request is repeated and was previously approved) and if so updates the database with the result. Excludes storing the planchets.';
+
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_batch_withdraw_insert(
+  IN cs_nonce BYTEA,
+  IN amount_val INT8,
+  IN amount_frac INT4,
+  IN h_denom_pub BYTEA,
+  IN ruuid INT8,
+  IN reserve_sig BYTEA,
+  IN h_coin_envelope BYTEA,
+  IN denom_sig BYTEA,
+  IN now INT8,
+  OUT out_denom_unknown BOOLEAN,
+  OUT out_nonce_reuse BOOLEAN,
+  OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  denom_serial INT8;
+BEGIN
+-- Shards: reserves by reserve_pub (SELECT)
+--         reserves_out (INSERT, with CONFLICT detection) by wih
+--         reserves by reserve_pub (UPDATE)
+--         reserves_in by reserve_pub (SELECT)
+--         wire_targets by wire_target_h_payto
+
+out_denom_unknown=TRUE;
+out_conflict=TRUE;
+out_nonce_reuse=TRUE;
+
+SELECT denominations_serial
+  INTO denom_serial
+  FROM denominations
+ WHERE denom_pub_hash=h_denom_pub;
+
+IF NOT FOUND
+THEN
+  -- denomination unknown, should be impossible!
+  out_denom_unknown=TRUE;
+  ASSERT false, 'denomination unknown';
+  RETURN;
+END IF;
+out_denom_unknown=FALSE;
+
+INSERT INTO reserves_out
+  (h_blind_ev
+  ,denominations_serial
+  ,denom_sig
+  ,reserve_uuid
+  ,reserve_sig
+  ,execution_date
+  ,amount_with_fee_val
+  ,amount_with_fee_frac)
+VALUES
+  (h_coin_envelope
+  ,denom_serial
+  ,denom_sig
+  ,ruuid
+  ,reserve_sig
+  ,now
+  ,amount_val
+  ,amount_frac)
+ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+  out_conflict=TRUE;
+  RETURN;
+END IF;
+out_conflict=FALSE;
+
+-- Special actions needed for a CS withdraw?
+out_nonce_reuse=FALSE;
+IF NOT NULL cs_nonce
+THEN
+  -- Cache CS signature to prevent replays in the future
+  -- (and check if cached signature exists at the same time).
+  INSERT INTO cs_nonce_locks
+    (nonce
+    ,max_denomination_serial
+    ,op_hash)
+  VALUES
+    (cs_nonce
+    ,denom_serial
+    ,h_coin_envelope)
+  ON CONFLICT DO NOTHING;
+
+  IF NOT FOUND
+  THEN
+    -- See if the existing entry is identical.
+    SELECT 1
+      FROM cs_nonce_locks
+     WHERE nonce=cs_nonce
+       AND op_hash=h_coin_envelope;
+    IF NOT FOUND
+    THEN
+      out_nonce_reuse=TRUE;
+      ASSERT false, 'nonce reuse attempted by client';
+      RETURN;
+    END IF;
+  END IF;
+END IF;
+
+END $$;
+
+COMMENT ON FUNCTION exchange_do_batch_withdraw_insert(BYTEA, INT8, INT4, BYTEA, INT8, BYTEA, BYTEA, BYTEA, INT8)
+  IS 'Stores information about a planchet for a batch withdraw operation. Checks if the planchet already exists, and in that case indicates a conflict';
+
+
+
+
 CREATE OR REPLACE FUNCTION exchange_do_withdraw_limit_check(
   IN ruuid INT8,
   IN start_time INT8,
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 238322c93..8d29581dd 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -797,6 +797,31 @@ prepare_statements (struct PostgresClosure *pg)
       " FROM exchange_do_withdraw"
       " ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10);",
       10),
+    /* Used in #postgres_do_batch_withdraw() to
+       update the reserve balance and check its status */
+    GNUNET_PQ_make_prepare (
+      "call_batch_withdraw",
+      "SELECT "
+      " reserve_found"
+      ",balance_ok"
+      ",kycok AS kyc_ok"
+      ",account_uuid AS payment_target_uuid"
+      ",ruuid"
+      " FROM exchange_do_batch_withdraw"
+      " ($1,$2,$3,$4,$5);",
+      5),
+    /* Used in #postgres_do_batch_withdraw_insert() to store
+       the signature of a blinded coin with the blinded coin's
+       details. */
+    GNUNET_PQ_make_prepare (
+      "call_batch_withdraw_insert",
+      "SELECT "
+      " out_denom_unknown AS denom_unknown"
+      ",out_conflict AS conflict"
+      ",out_nonce_reuse AS nonce_reuse"
+      " FROM exchange_do_batch_withdraw_insert"
+      " ($1,$2,$3,$4,$5,$6,$7,$8,$9);",
+      9),
     /* Used in #postgres_do_withdraw_limit_check() to check
        if the withdrawals remain below the limit under which
        KYC is not required. */
@@ -5244,6 +5269,122 @@ postgres_do_withdraw (
 
 
 /**
+ * Perform reserve update as part of a batch withdraw operation, checking
+ * for sufficient balance. Persisting the withdrawal details is done
+ * separately!
+ *
+ * @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param now current time (rounded)
+ * @param reserve_pub public key of the reserve to debit
+ * @param amount total amount to withdraw
+ * @param[out] found set to true if the reserve was found
+ * @param[out] balance_ok set to true if the balance was sufficient
+ * @param[out] kyc set to the KYC status of the reserve
+ * @param[out] ruuid set to the reserve's UUID (reserves table row)
+ * @return query execution status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_do_batch_withdraw (
+  void *cls,
+  struct GNUNET_TIME_Timestamp now,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
+  const struct TALER_Amount *amount,
+  bool *found,
+  bool *balance_ok,
+  struct TALER_EXCHANGEDB_KycStatus *kyc,
+  uint64_t *ruuid)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_TIME_Timestamp gc;
+  struct GNUNET_PQ_QueryParam params[] = {
+    TALER_PQ_query_param_amount (amount),
+    GNUNET_PQ_query_param_auto_from_type (reserve_pub),
+    GNUNET_PQ_query_param_timestamp (&now),
+    GNUNET_PQ_query_param_timestamp (&gc),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_bool ("reserve_found",
+                                found),
+    GNUNET_PQ_result_spec_bool ("balance_ok",
+                                balance_ok),
+    GNUNET_PQ_result_spec_bool ("kyc_ok",
+                                &kyc->ok),
+    GNUNET_PQ_result_spec_uint64 ("payment_target_uuid",
+                                  &kyc->payment_target_uuid),
+    GNUNET_PQ_result_spec_uint64 ("ruuid",
+                                  ruuid),
+    GNUNET_PQ_result_spec_end
+  };
+
+  gc = GNUNET_TIME_absolute_to_timestamp (
+    GNUNET_TIME_absolute_add (now.abs_time,
+                              pg->legal_reserve_expiration_time));
+  kyc->type = TALER_EXCHANGEDB_KYC_WITHDRAW;
+  return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                   "call_batch_withdraw",
+                                                   params,
+                                                   rs);
+}
+
+
+/**
+ * Perform insert as part of a batch withdraw operation, and persisting the
+ * withdrawal details.
+ *
+ * @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param nonce client-contributed input for CS denominations that must be checked for idempotency, or NULL for non-CS withdrawals
+ * @param collectable corresponding collectable coin (blind signature)
+ * @param now current time (rounded)
+ * @param ruuid reserve UUID
+ * @param[out] denom_unknown set if the denomination is unknown in the DB
+ * @param[out] conflict if the envelope was already in the DB
+ * @param[out] nonce_reuse if @a nonce was non-NULL and reused
+ * @return query execution status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_do_batch_withdraw_insert (
+  void *cls,
+  const struct TALER_CsNonce *nonce,
+  const struct TALER_EXCHANGEDB_CollectableBlindcoin *collectable,
+  struct GNUNET_TIME_Timestamp now,
+  uint64_t ruuid,
+  bool *denom_unknown,
+  bool *conflict,
+  bool *nonce_reuse)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    NULL == nonce
+    ? GNUNET_PQ_query_param_null ()
+    : GNUNET_PQ_query_param_auto_from_type (nonce),
+    TALER_PQ_query_param_amount (&collectable->amount_with_fee),
+    GNUNET_PQ_query_param_auto_from_type (&collectable->denom_pub_hash),
+    GNUNET_PQ_query_param_uint64 (&ruuid),
+    GNUNET_PQ_query_param_auto_from_type (&collectable->reserve_sig),
+    GNUNET_PQ_query_param_auto_from_type (&collectable->h_coin_envelope),
+    TALER_PQ_query_param_blinded_denom_sig (&collectable->sig),
+    GNUNET_PQ_query_param_timestamp (&now),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_bool ("denom_unknown",
+                                denom_unknown),
+    GNUNET_PQ_result_spec_bool ("conflict",
+                                conflict),
+    GNUNET_PQ_result_spec_bool ("nonce_reuse",
+                                nonce_reuse),
+    GNUNET_PQ_result_spec_end
+  };
+
+  return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                   "call_batch_withdraw_insert",
+                                                   params,
+                                                   rs);
+}
+
+
+/**
  * Check that reserve remains below threshold for KYC
  * checks after withdraw operation.
  *
@@ -13931,6 +14072,8 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
   plugin->reserves_in_insert = &postgres_reserves_in_insert;
   plugin->get_withdraw_info = &postgres_get_withdraw_info;
   plugin->do_withdraw = &postgres_do_withdraw;
+  plugin->do_batch_withdraw = &postgres_do_batch_withdraw;
+  plugin->do_batch_withdraw_insert = &postgres_do_batch_withdraw_insert;
   plugin->do_withdraw_limit_check = &postgres_do_withdraw_limit_check;
   plugin->do_deposit = &postgres_do_deposit;
   plugin->do_melt = &postgres_do_melt;