introduce stored procedure for coin balance check

1 files changed, 188 insertions, 0 deletions

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index dc6b2bba2..1725b70e0 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