Virtual Currency and Tracking What is Given Where

Virtual Currency is currency, which is used inside games, websites, applications, etc. It can be bought and traded and in many cases has a conversion to real currency. For instance Facebook Credits, this is virtual currency and each credit is worth 10 cents.

A common problem I run into when building Currency Sinks or earns is; what part of the application gave out the currency and why? Some techniques I used to manage this problem are to create a constants file of a bunch of action ids and used the constants wherever the application writes updates to the balance.

This causes a problem. Developers will just reuse the same id for very distinct reasons. When debugging situations I have to trace through a bunch of code.

Additionally problems can arise on deploy, because the use of currency change maybe pushed out yet the constants file is not. This causes data issues and or even preventable exceptions disrupting the customer experience.  The later possibility is much more offensive to me personally then data corruption. I go above and beyond to prevent any negative views a customer may have. My work shouldn't piss anyone off it should enable them.

I've tackled the problem by manually managing where the currency was deducted from, but this is the last time. If you do the same thing more then once and it can easily be solved by automation, automate it to make your life easier -- my motto.

How am I tackling the problem now?

By default I am adding the calling Objects class and method to my CurrencyHistory Table with each Currency Transaction and top app name. Now on the row basis I can track what class and method changed the balance and where it was called. Below is a table structure used to track currency and history of the currency balance change.

--
-- Holds the Balance of a users currency
--
DROP TABLE IF EXISTS Currency;
CREATE TABLE `Currency` (
  `userId` bigint unsigned NOT NULL,
  `regular` int NOT NULL,
  `premium` int NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB;

--
-- Holds the info on How the balance is generated
--
DROP TABLE IF EXISTS CurrencyHistory;
CREATE TABLE CurrencyHistory (
    transactionId BIGINT UNSIGNED NOT NULL COMMENT "Globally unique Id"
    userId BIGINT UNSIGNED NOT NULL COMMENT "User Identifier",
    itemId bigint unsigned NOT NULL DEFAULT 0 "Unique Identifier on what was bought or sold",
    itemOwnerId bigint unsigned NOT NULL DEFAULT 0 "User that was the last owner",
    regularAmount INT NOT NULL "How much was earned or sold",
    premiumAmount INT NOT NULL "How much was earned or sold for premium currency",
    createDate TIMESTAMP NOT NULL "When this event occurred",
    callStackId DEFAULT NULL "What part of the code base contains the code that changed currency the lookup table is generated on the fly from the json string and stored offline"
    PRIMARY KEY(userId, createDate, transactionId)
) Engine=InnoDB;

Now to modify Currency here is some PHP code:

  /**
    * Update user's balance
    * 
    * @param int $userId
    * @param int $regularDelta
    * @param int $premiumDelta
    * @return bool
    */
    public function CurrencyDB_updateBalance($userId, $regularDelta = 0, $premiumDelta = 0,  $itemId = 0, $itemOwnerId = 0) {

        $this->validate->id($userId);
        $this->validate->int($regularDelta, $premiumDelta);
        if( $regularDelta == 0 && $premiumDelta == 0 ) {
            return 0;
        }

        $this->shard($userId)->beginTransaction('updateBalance');
        try {
          
            if( $regularDelta < 0 || $premiumDelta < 0 ) { 
                $row = $this->shard($userId)->selectRow("SELECT * FROM Currency WHERE userId=? FOR UPDATE", array($userId));
                if( $row->regular < -$regularDelta || $row->premium < -$premiumDelta ) {
                    throw new CurrencyDBException("Insufficient funds", CurrencyDBException::INSUFFICIENT_FUNDS);
                }
            }
            
            // modify the balance
            $query = "INSERT INTO Currency(userId, regular, premium) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE regular=regular+VALUES(regular), premium=premium+VALUES(premium)";
            $this->shard($userId)->update($query, array($userId, $regularDelta, $premiumDelta), $this->cacheKey($userId));
            
            // get the GUID and record the details of the transaction
            $transactionId = $this->getTicket();
            $query = "INSERT INTO CurrencyHistory(transactionId, userId, itemId, itemOwnerId, regularAmount, premiumAmount, createDate, fromWhere) VALUES(?, ?, ?, ?, ?, ?, NOW(), ?)";
            $callStackId = $this->getCallStackId(); // get the backtrace as an ID
            $this->shard($userId)->update($query, array($transactionId, $userId, $itemId, $itemOwnerId, $regularDelta, $premiumDelta, $callStackId));
            
            $this->shard($userId)->commit('updateBalance');

        } catch (Exception $e) {

            $this->shard($userId)->rollback('updateBalance');
            throw $e;

        }
        return 1;
    }

 /**
    * Return an Id for the code stack
    * 
    * @return bigint
    */
   private function getCallStackId(){
        if ($GLOBALS['cfg']['disable_feature_backtrace_sql'] != 1){
        
            $trace      = debug_backtrace();
            $notdone    = 1;
            $numLoops   = 0;
            $maxLoops   = 10; // hope its not nested 10 levels deep
        
            while($numLoops++ < $maxLoops){
            
                $test = (strpos($trace[0]['class'], 'CurrencyDB') === FALSE ? 0 : 1);

                if (!$test){
                    break;
                }
                array_shift($trace);
            }
        

            $loc = array();
            
            
            $loc[] = substr(str_replace("/var/www/html","",$_SERVER['PHP_SELF']), 1);
            $loc[] = ($trace[0]['class'] ? $trace[0]['class'] : 'main');
            $loc[] = $trace[0]['function'] ? $trace[0]['function'].'()' : 'called_from_script';
            return $this->getCodeLookupEntityId(json_encode($loc));
                
        }
        
        return 0;
 }       

mysql> select * from CodeLookup;
+----------------------+------------------------------------------------------------------------+
| entityId             | json                                                                   |
+----------------------+------------------------------------------------------------------------+
| 17113479085265723743 | ["test\/dathan\/test_CheckBonus.php","CheckBonus","CheckBonus_give()"] |
+----------------------+------------------------------------------------------------------------+