introduce stored procedure for coin balance check

2 files changed, 246 insertions, 1 deletions

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index dc6b2bba..1725b70e 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -899,6 +899,194 @@ COMMENT ON FUNCTION exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4)
 
 
 
+CREATE OR REPLACE FUNCTION exchange_do_check_coin_balance(
+  IN denom_val INT8, -- value of the denomination of the coin
+  IN denom_frac INT4, -- value of the denomination of the coin
+  IN in_coin_pub BYTEA, -- coin public key
+  IN check_recoup BOOLEAN, -- do we need to check the recoup table?
+  IN zombie_required BOOLEAN, -- do we need a zombie coin?
+  OUT balance_ok BOOLEAN, -- balance satisfied?
+  OUT zombie_ok BOOLEAN) -- zombie satisfied?
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  coin_uuid INT8; -- known_coin_id of coin_pub
+DECLARE
+  tmp_val INT8; -- temporary result
+DECLARE
+  tmp_frac INT8; -- temporary result
+DECLARE
+  spent_val INT8; -- how much of coin was spent?
+DECLARE
+  spent_frac INT8; -- how much of coin was spent?
+DECLARE
+  unspent_val INT8; -- how much of coin was refunded?
+DECLARE
+  unspent_frac INT8; -- how much of coin was refunded?
+BEGIN
+
+-- Note: possible future optimization: get the coin_uuid from the previous
+-- 'ensure_coin_known' and pass that here instead of the coin_pub. Might help
+-- a tiny bit with performance.
+SELECT known_coin_id INTO coin_uuid
+  FROM known_coins
+ WHERE coin_pub=in_coin_pub;
+
+IF NOT FOUND
+THEN
+  -- coin unknown, should be impossible!
+  balance_ok=FALSE;
+  zombie_ok=FALSE;
+  ASSERT false, 'coin unknown';
+  RETURN;
+END IF;
+
+
+spent_val = 0;
+spent_frac = 0;
+unspent_val = denom_val;
+unspent_frac = denom_frac;
+
+SELECT
+   SUM(amount_with_fee_val) -- overflow here is not plausible
+  ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+  INTO
+   tmp_val
+  ,tmp_frac
+  FROM deposits
+ WHERE known_coin_id=coin_uuid;
+
+IF tmp_val IS NOT NULL
+THEN
+  spent_val = spent_val + tmp_val;
+  spent_frac = spent_frac + tmp_frac;
+END IF;
+
+SELECT
+   SUM(amount_with_fee_val) -- overflow here is not plausible
+  ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits
+  INTO
+   tmp_val
+  ,tmp_frac
+  FROM refresh_commitments
+ WHERE old_known_coin_id=coin_uuid;
+
+IF tmp_val IS NOT NULL
+THEN
+  spent_val = spent_val + tmp_val;
+  spent_frac = spent_frac + tmp_frac;
+END IF;
+
+
+SELECT
+   SUM(rf.amount_with_fee_val) -- overflow here is not plausible
+  ,SUM(CAST(rf.amount_with_fee_frac AS INT8)) -- compute using 64 bits
+  INTO
+   tmp_val
+  ,tmp_frac
+  FROM deposits
+  JOIN refunds rf
+    USING (deposit_serial_id)
+  WHERE
+    known_coin_id=coin_uuid;
+IF tmp_val IS NOT NULL
+THEN
+  unspent_val = unspent_val + tmp_val;
+  unspent_frac = unspent_frac + tmp_frac;
+END IF;
+
+-- Note: even if 'check_recoup' is true, the tables below
+-- are in practice likely empty (as they only apply if
+-- the exchange (ever) had to revoke keys).
+IF check_recoup
+THEN
+
+  SELECT
+     SUM(amount_val) -- overflow here is not plausible
+    ,SUM(CAST(amount_frac AS INT8)) -- compute using 64 bits
+    INTO
+     tmp_val
+    ,tmp_frac
+    FROM recoup_refresh
+   WHERE known_coin_id=coin_uuid;
+
+  IF tmp_val IS NOT NULL
+  THEN
+    spent_val = spent_val + tmp_val;
+    spent_frac = spent_frac + tmp_frac;
+  END IF;
+
+  SELECT
+     SUM(amount_val) -- overflow here is not plausible
+    ,SUM(CAST(amount_frac AS INT8)) -- compute using 64 bits
+    INTO
+     tmp_val
+    ,tmp_frac
+    FROM recoup
+   WHERE known_coin_id=coin_uuid;
+
+  IF tmp_val IS NOT NULL
+  THEN
+    spent_val = spent_val + tmp_val;
+    spent_frac = spent_frac + tmp_frac;
+  END IF;
+
+  SELECT
+     SUM(amount_val) -- overflow here is not plausible
+    ,SUM(CAST(amount_frac AS INT8)) -- compute using 64 bits
+    INTO
+     tmp_val
+    ,tmp_frac
+    FROM recoup_refresh
+    JOIN refresh_revealed_coins rrc
+        USING (rrc_serial)
+    JOIN refresh_commitments rfc
+         ON (rrc.melt_serial_id = rfc.melt_serial_id)
+   WHERE rfc.old_known_coin_id=coin_uuid;
+
+  IF tmp_val IS NOT NULL
+  THEN
+    unspent_val = unspent_val + tmp_val;
+    unspent_frac = unspent_frac + tmp_frac;
+  END IF;
+
+  IF ( (0 < tmp_val) OR (0 < tmp_frac) )
+  THEN
+    -- There was a transaction that justifies the zombie
+    -- status, clear the flag
+    zombie_required=FALSE;
+  END IF;
+
+END IF;
+
+
+-- normalize results
+spent_val = spent_val + spent_frac / 100000000;
+spent_frac = spent_frac % 100000000;
+unspent_val = unspent_val + unspent_frac / 100000000;
+unspent_frac = unspent_frac % 100000000;
+
+-- Actually check if the coin balance is sufficient. Verbosely. ;-)
+IF (unspent_val > spent_val)
+THEN
+  balance_ok=TRUE;
+ELSE
+  IF (unspent_val = spent_val) AND (unspent_frac >= spent_frac)
+  THEN
+    balance_ok=TRUE;
+  ELSE
+    balance_ok=FALSE;
+  END IF;
+END IF;
+
+zombie_ok = NOT zombie_required;
+
+END $$;
+
+COMMENT ON FUNCTION exchange_do_check_coin_balance(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN)
+  IS 'Checks whether the coin has sufficient balance for all the operations associated with it';
+
+
 
 
 -- Complete transaction
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 73a03e75..8e184a9d 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -596,6 +596,16 @@ prepare_statements (struct PostgresClosure *pg)
       "lock_withdraw",
       "LOCK TABLE reserves_out;",
       0),
+    /* Used in #postgres_do_check_coin_balance() to check
+       a coin's balance */
+    GNUNET_PQ_make_prepare (
+      "call_check_coin_balance",
+      "SELECT "
+      " balance_ok"
+      ",zombie_ok"
+      " FROM exchange_do_check_coin_balance"
+      " ($1,$2,$3,$4,$5);",
+      5),
     /* Used in #postgres_do_withdraw() to store
        the signature of a blinded coin with the blinded coin's
        details before returning it during /reserve/withdraw. We store
@@ -4492,6 +4502,53 @@ postgres_get_withdraw_info (
 
 
 /**
+ * Check coin balance is sufficient to satisfy balance
+ * invariants.
+ *
+ * @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param coin_pub coin to check
+ * @param coin_value value of the coin's denomination (avoids internal lookup)
+ * @param check_recoup include recoup and recoup_refresh tables in calculation
+ * @param zombie_required additionally require coin to be a zombie coin
+ * @param[out] balance_ok set to true if the balance was sufficient
+ * @param[out] zombie_ok set to true if the zombie requirement was satisfied
+ * @return query execution status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_do_check_coin_balance (
+  void *cls,
+  const struct TALER_CoinSpendPublicKeyP *coin_pub,
+  const struct TALER_Amount *coin_value,
+  bool check_recoup,
+  bool zombie_required,
+  bool *balance_ok,
+  bool *zombie_ok)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    TALER_PQ_query_param_amount (coin_value),
+    GNUNET_PQ_query_param_auto_from_type (coin_pub),
+    GNUNET_PQ_query_param_bool (check_recoup),
+    GNUNET_PQ_query_param_bool (zombie_required),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_bool ("balance_ok",
+                                balance_ok),
+    GNUNET_PQ_result_spec_bool ("zombie_ok",
+                                zombie_ok),
+    GNUNET_PQ_result_spec_end
+  };
+
+  return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                   "call_check_coin_balance",
+                                                   params,
+                                                   rs);
+
+}
+
+
+/**
  * Perform withdraw operation, checking for sufficient balance
  * and possibly persisting the withdrawal details.
  *
@@ -11825,7 +11882,7 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
   plugin->get_latest_reserve_in_reference =
     &postgres_get_latest_reserve_in_reference;
   plugin->get_withdraw_info = &postgres_get_withdraw_info;
-  // plugin->insert_withdraw_info = &postgres_insert_withdraw_info;
+  plugin->do_check_coin_balance = &postgres_do_check_coin_balance;
   plugin->do_withdraw = &postgres_do_withdraw;
   plugin->do_withdraw_limit_check = &postgres_do_withdraw_limit_check;
   plugin->get_reserve_history = &postgres_get_reserve_history;