/**
* @param $values array(last_trade, bid, ask, volume (optional))
*/
function insert_new_ticker($job, $exchange, $cur1, $cur2, $values)
{
// sanity and quality checks
if (!isset($values['last_trade'])) {
throw new Exception("No last_trade specified for {$cur1}/{$cur2} on {$exchange}");
// need at least this
}
if (isset($values['sell'])) {
throw new Exception("Invalid parameter: sell (should be bid)");
}
if (isset($values['buy'])) {
throw new Exception("Invalid parameter: buy (should be ask)");
}
if (!isset($values['volume'])) {
$values['volume'] = null;
}
if (!isset($values['bid'])) {
$values['bid'] = null;
}
if (!isset($values['ask'])) {
$values['ask'] = null;
}
if (strlen($exchange['name']) <= 1) {
throw new Exception("Invalid parameter: exchange '" . htmlspecialchars($exchange['name']) . "'");
}
if (strlen($cur1) <= 1) {
throw new Exception("Invalid parameter: currency1 '" . htmlspecialchars($cur1) . "'");
}
if (strlen($cur2) <= 1) {
throw new Exception("Invalid parameter: currency2 '" . htmlspecialchars($cur2) . "'");
}
crypto_log($exchange['name'] . " rate for {$cur1}/{$cur2}: " . $values['last_trade'] . " (" . $values['bid'] . "/" . $values['ask'] . ") [" . $values['volume'] . "]");
if ($values['bid'] > $values['ask']) {
crypto_log("<strong>WARNING:</strong> bid > ask");
}
// insert in new ticker value
$q = db()->prepare("INSERT INTO ticker SET exchange=:exchange, currency1=:currency1, currency2=:currency2, last_trade=:last_trade, bid=:bid, ask=:ask, volume=:volume, job_id=:job_id, is_daily_data=1, created_at=NOW(), created_at_day=TO_DAYS(NOW())");
$q->execute(array("exchange" => $exchange['name'], "currency1" => $cur1, "currency2" => $cur2, "last_trade" => $values['last_trade'], "bid" => $values['bid'], "ask" => $values['ask'], "volume" => $values['volume'], "job_id" => $job['id']));
$last_id = db()->lastInsertId();
crypto_log("Inserted new ticker id=" . $last_id);
// put into the most recent table
// TODO could also use a REPLACE statement
$q = db_master()->prepare("SELECT * FROM ticker_recent WHERE exchange=:exchange AND currency1=:currency1 AND currency2=:currency2 LIMIT 1");
$q->execute(array("exchange" => $exchange['name'], "currency1" => $cur1, "currency2" => $cur2));
if (!$q->fetch()) {
// insert in a new blank value (this will not occur very often)
$q = db_master()->prepare("INSERT INTO ticker_recent SET exchange=:exchange, currency1=:currency1, currency2=:currency2");
$q->execute(array("exchange" => $exchange['name'], "currency1" => $cur1, "currency2" => $cur2));
}
// update the previously existing recent value
$q = db()->prepare("UPDATE ticker_recent SET created_at=NOW(), last_trade=:last_trade, bid=:bid, ask=:ask, volume=:volume, job_id=:job_id\n WHERE exchange=:exchange AND currency1=:currency1 AND currency2=:currency2");
$q->execute(array("last_trade" => $values['last_trade'], "bid" => $values['bid'], "ask" => $values['ask'], "volume" => $values['volume'], "job_id" => $job['id'], "exchange" => $exchange['name'], "currency1" => $cur1, "currency2" => $cur2));
// all other data from today is now old
// NOTE if the system time changes between the next two commands, then we may erraneously
// specify that there is no valid daily data. one solution is to specify NOW() as $created_at rather than
// relying on MySQL
$q = db()->prepare("UPDATE ticker SET is_daily_data=0 WHERE is_daily_data=1 AND exchange=:exchange AND currency1=:currency1 AND currency2=:currency2 AND\n created_at_day = TO_DAYS(NOW()) AND id <> :id");
$q->execute(array("exchange" => $exchange['name'], "currency1" => $cur1, "currency2" => $cur2, "id" => $last_id));
}
// update as necessary
$stored[$date][$user_id][$exchange][$account_id][$currency]['min'] = min($balance['balance'], $stored[$date][$user_id][$exchange][$account_id][$currency]['min']);
$stored[$date][$user_id][$exchange][$account_id][$currency]['max'] = max($balance['balance'], $stored[$date][$user_id][$exchange][$account_id][$currency]['max']);
$stored[$date][$user_id][$exchange][$account_id][$currency]['close'] = $balance['balance'];
$stored[$date][$user_id][$exchange][$account_id][$currency]['samples']++;
$stored[$date][$user_id][$exchange][$account_id][$currency]['values'][] = $balance['balance'];
}
crypto_log("Processed " . number_format($count) . " balances entries");
// we now have lots of data; insert it
// danger! danger! five nested loops!
$insert_count = 0;
foreach ($stored as $date => $a) {
foreach ($a as $user_id => $b) {
foreach ($b as $exchange => $c) {
foreach ($c as $account_id => $d) {
foreach ($d as $currency => $summary) {
$q = db_master()->prepare("INSERT INTO graph_data_balances SET\n user_id=:user_id, exchange=:exchange, account_id=:account_id, currency=:currency, data_date=:data_date, samples=:samples,\n balance_min=:min, balance_opening=:open, balance_closing=:close, balance_max=:max, balance_stdev=:stdev");
$q->execute(array('user_id' => $user_id, 'exchange' => $exchange, 'account_id' => $account_id, 'currency' => $currency, 'data_date' => $date, 'samples' => $summary['samples'], 'min' => $summary['min'], 'open' => $summary['open'], 'close' => $summary['close'], 'max' => $summary['max'], 'stdev' => stdev($summary['values'])));
$insert_count++;
}
}
}
}
}
crypto_log("Inserted " . number_format($insert_count) . " balances entries into graph_data_balances");
// finally, delete all the old data
// we've exhausted over everything so this should be safe
$q = db_master()->prepare("DELETE FROM balances WHERE created_at <= :date ORDER BY created_at ASC");
$q->execute(array("date" => $cutoff_date));
crypto_log("Deleted " . number_format($count) . " summary entries");
batch_footer();
// "How long does it take for a page to be generated?"
// "What pages have the most database queries?"
// "What pages spend the most time in PHP as opposed to the database?"
// "How many jobs are running per hour?"
// "What jobs have the most database queries?"
// "Which jobs time out the most?"
// "How many blockchain requests fail?"
// "What jobs take the longest requesting URLs?"
// "How many jobs are being queued at once?"
// "Which queue types take the longest?"
// "What are the most common graph types?"
// "How many ticker graphs are being requested?"
// we've processed all the data we want; delete old metrics data
$q = db_master()->prepare("DELETE FROM performance_metrics_slow_queries");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_slow_urls");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_repeated_queries");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_repeated_urls");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_pages");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_graphs");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_jobs");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_queues");
$q->execute();
crypto_log("Deleted old metric data.");
batch_footer();
$c = $q->fetch();
$data['jobs_timeout'] = $c['c'];
$q = db()->prepare("SELECT SUM(notifications_sent) AS c, MAX(notifications_sent) AS m FROM user_properties");
$q->execute();
$c = $q->fetch();
$data['notifications_sent'] = $c['c'];
$data['max_notifications_sent'] = $c['m'];
// change all NULL values to 0
foreach ($data as $key => $value) {
if ($value === null) {
$data[$key] = 0;
}
}
// calculate MySQL statistics
// must request the master database manually!
$q = db_master()->prepare("SHOW GLOBAL STATUS");
$q->execute();
$mysql_mapping = array('mysql_uptime' => 'Uptime', 'mysql_threads' => 'Threads_running', 'mysql_questions' => 'Questions', 'mysql_slow_queries' => 'Slow_queries', 'mysql_opens' => 'Opened_tables', 'mysql_flush_tables' => 'Flush_commands', 'mysql_open_tables' => 'Open_tables', 'mysql_locks_immediate' => 'Table_locks_immediate', 'mysql_locks_blocked' => 'Table_locks_waited');
while ($s = $q->fetch()) {
if (($pos = array_search($s['Variable_name'], $mysql_mapping)) !== false) {
$data[$pos] = $s['Value'];
}
}
$data['disk_free_space'] = disk_free_space('/');
// get system statistics if defined (i.e. not Windows)
$query_extra = "";
if (function_exists('sys_getloadavg')) {
$top = sys_getloadavg();
$data['system_load_1min'] = $top[0];
$data['system_load_5min'] = $top[1];
$data['system_load_15min'] = $top[2];
<?php
/**
* A batch script to clean up old jobs.
* This always executes (no job framework) so it should be used sparingly or as necessary.
*
* Arguments (in command line, use "-" for no argument):
* $key/1 required the automated key
*/
define('USE_MASTER_DB', true);
// always use the master database for selects!
require __DIR__ . "/../inc/global.php";
require __DIR__ . "/_batch.php";
require_batch_key();
batch_header("Batch cleanup jobs", "batch_cleanup_jobs");
crypto_log("Current time: " . date('r'));
// simply delete all jobs that haven't executed and are over three months old
$q = db_master()->prepare("DELETE FROM jobs WHERE is_executed=1 AND executed_at < DATE_SUB(NOW(), INTERVAL 1 MONTH)");
$q->execute(array());
crypto_log("Deleted old jobs.");
batch_footer();
<?php
require __DIR__ . '/db_setup.php';
require __DIR__ . '/db_funcs.php';
require __DIR__ . '/yf_unit_tests_setup.php';
######################
echo 'db():' . PHP_EOL . print_r(db()->get_one('SELECT COUNT(*) AS num from ' . db('user') . ' '), 1);
echo 'db_t2():' . PHP_EOL . print_r(db_t2()->get_one('SELECT COUNT(*) AS num from ' . db_t2('user') . ' '), 1);
echo 'db_t3():' . PHP_EOL . print_r(db_t3()->get_one('SELECT COUNT(*) AS num from ' . db_t3('user') . ' '), 1);
echo 'db_rr():' . PHP_EOL . print_r(db_rr()->get_one('SELECT COUNT(*) AS num from ' . db_rr('user') . ' '), 1);
echo 'db_cr():' . PHP_EOL . print_r(db_cr()->get_one('SELECT COUNT(*) AS num from ' . db_cr('user') . ' '), 1);
echo 'db_m3():' . PHP_EOL . print_r(db_m3()->get_one('SELECT COUNT(*) AS num from ' . db_m3('user') . ' '), 1);
echo 'db_master():' . PHP_EOL . print_r(db_master()->get_one('SELECT COUNT(*) AS num from ' . db_master('user') . ' '), 1);
echo 'db_slave():' . PHP_EOL . print_r(db_slave()->get_one('SELECT COUNT(*) AS num from ' . db_slave('user') . ' '), 1);
$stored[$date][$user_id][$type] = array();
}
if (!isset($stored[$date][$user_id][$type]['open'])) {
$stored[$date][$user_id][$type] = array('min' => $ticker['balance'], 'max' => $ticker['balance'], 'open' => $ticker['balance'], 'close' => $ticker['balance'], 'samples' => 0, 'values' => array());
}
// update as necessary
$stored[$date][$user_id][$type]['min'] = min($ticker['balance'], $stored[$date][$user_id][$type]['min']);
$stored[$date][$user_id][$type]['max'] = max($ticker['balance'], $stored[$date][$user_id][$type]['max']);
$stored[$date][$user_id][$type]['close'] = $ticker['balance'];
$stored[$date][$user_id][$type]['samples']++;
$stored[$date][$user_id][$type]['values'][] = $ticker['balance'];
}
crypto_log("Processed " . number_format($count) . " summary entries");
// we now have lots of data; insert it
$insert_count = 0;
foreach ($stored as $date => $a) {
foreach ($a as $user_id => $b) {
foreach ($b as $type => $summary) {
$q = db_master()->prepare("INSERT INTO graph_data_summary SET\n user_id=:user_id, summary_type=:summary_type, data_date=:data_date, samples=:samples,\n balance_min=:min, balance_opening=:open, balance_closing=:close, balance_max=:max, balance_stdev=:stdev");
$q->execute(array('user_id' => $user_id, 'summary_type' => $type, 'data_date' => $date, 'samples' => $summary['samples'], 'min' => $summary['min'], 'open' => $summary['open'], 'close' => $summary['close'], 'max' => $summary['max'], 'stdev' => stdev($summary['values'])));
$insert_count++;
}
}
}
crypto_log("Inserted " . number_format($insert_count) . " summary entries into graph_data_summary");
// finally, delete all the old data
// we've exhausted over everything so this should be safe
$q = db_master()->prepare("DELETE FROM summary_instances WHERE created_at <= :date ORDER BY created_at ASC");
$q->execute(array("date" => $cutoff_date));
crypto_log("Deleted " . number_format($count) . " summary entries");
batch_footer();
$stored[$date][$exchange][$cur1][$cur2]['min'] = min($ticker['last_trade'], $stored[$date][$exchange][$cur1][$cur2]['min']);
$stored[$date][$exchange][$cur1][$cur2]['max'] = max($ticker['last_trade'], $stored[$date][$exchange][$cur1][$cur2]['max']);
$stored[$date][$exchange][$cur1][$cur2]['volume'] = max($ticker['volume'], $stored[$date][$exchange][$cur1][$cur2]['volume']);
$stored[$date][$exchange][$cur1][$cur2]['close'] = $ticker['last_trade'];
$stored[$date][$exchange][$cur1][$cur2]['samples']++;
$stored[$date][$exchange][$cur1][$cur2]['bid'] = $ticker['bid'];
// bid, ask are the last values for the day
$stored[$date][$exchange][$cur1][$cur2]['ask'] = $ticker['ask'];
$stored[$date][$exchange][$cur1][$cur2]['values'][] = $ticker['last_trade'];
}
crypto_log("Processed " . number_format($count) . " ticker entries");
// we now have lots of data; insert it
$insert_count = 0;
foreach ($stored as $date => $a) {
foreach ($a as $exchange => $b) {
foreach ($b as $cur1 => $c) {
foreach ($c as $cur2 => $summary) {
$q = db_master()->prepare("INSERT INTO graph_data_ticker SET\n exchange=:exchange, currency1=:currency1, currency2=:currency2, data_date=:data_date, samples=:samples, data_date_day=TO_DAYS(:data_date),\n volume=:volume, last_trade_min=:min, last_trade_opening=:open, last_trade_closing=:close, last_trade_max=:max, bid=:bid, ask=:ask, last_trade_stdev=:stdev");
$q->execute(array('exchange' => $exchange, 'currency1' => $cur1, 'currency2' => $cur2, 'data_date' => $date, 'samples' => $summary['samples'], 'volume' => $summary['volume'], 'min' => $summary['min'], 'open' => $summary['open'], 'close' => $summary['close'], 'max' => $summary['max'], 'bid' => $summary['bid'], 'ask' => $summary['ask'], 'stdev' => stdev($summary['values'])));
$insert_count++;
}
}
}
}
crypto_log("Inserted " . number_format($insert_count) . " summarised entries into graph_data_ticker");
// finally, delete all the old data
// we've exhausted over everything so this should be safe
$q = db_master()->prepare("DELETE FROM ticker WHERE created_at <= :date ORDER BY created_at ASC");
$q->execute(array("date" => $cutoff_date));
crypto_log("Deleted " . number_format($count) . " ticker entries");
batch_footer();
