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()"] |
+----------------------+------------------------------------------------------------------------+
