<?php /** * A batch script to clean up balances data and move it into the database as necessary. * 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 balances", "batch_cleanup_balances"); crypto_log("Current time: " . date('r')); // find all ticker data that needs to be inserted into the graph_data table // TODO currently all database dates and PHP logic is based on server side timezone, not GMT/UTC // database values need to all be modified to GMT before we can add '+00:00' for example $cutoff_date = date('Y-m-d', strtotime(get_site_config('archive_balances_data'))) . ' 23:59:59'; // +00:00'; $summary_date_prefix = " 00:00:00"; // +00:00 crypto_log("Cleaning up balances data earlier than " . htmlspecialchars($cutoff_date) . " into summaries..."); $q = db_master()->prepare("SELECT * FROM balances WHERE created_at <= :date ORDER BY created_at ASC"); $q->execute(array("date" => $cutoff_date)); // we're going to store this all in memory, because at least that way we don't have to // execute logic twice $stored = array(); $count = 0; while ($balance = $q->fetch()) {
<?php /** * A batch script to calculate performance metrics from data that has been collected. * 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 metrics", "batch_metrics"); crypto_log("Current time: " . date('r')); // "What database queries take the longest?" $report_type = "db_slow_queries"; // select the worst ten queries $q = db_master()->prepare("SELECT query_id, SUM(query_count) AS qc, SUM(query_time) AS qt, MIN(page_id) AS pid FROM performance_metrics_slow_queries\n GROUP BY query_id ORDER BY SUM(query_time) / SUM(query_count) LIMIT 10"); $q->execute(); $data = $q->fetchAll(); $q = db_master()->prepare("INSERT INTO performance_reports SET report_type=?"); $q->execute(array($report_type)); $report_id = db_master()->lastInsertId(); foreach ($data as $row) { $q = db_master()->prepare("INSERT INTO performance_report_slow_queries SET report_id=?, query_id=?, query_count=?, query_time=?, page_id=?"); $q->execute(array($report_id, $row['query_id'], $row['qc'], $row['qt'], $row['pid'])); } crypto_log("Created report '{$report_type}'"); // "What URLs take the longest to request?"
<?php /** * Batch script: calculate site statistics. * * Arguments (in command line, use "-" for no argument): * $key/1 required the automated key */ require __DIR__ . "/../inc/global.php"; require __DIR__ . "/_batch.php"; require_batch_key(); batch_header("Batch statistics", "batch_statistics"); crypto_log("Current time: " . date('r')); // calculate statistics $data = array(); $q = db()->prepare("SELECT COUNT(*) AS c, SUM(emails_sent) AS e FROM user_properties"); $q->execute(); $c = $q->fetch(); $data['total_users'] = $c['c']; $data['total_emails_sent'] = $c['e']; $q = db()->prepare("SELECT COUNT(*) AS c FROM user_properties WHERE is_disabled=1"); $q->execute(); $c = $q->fetch(); $data['disabled_users'] = $c['c']; $q = db()->prepare("SELECT COUNT(*) AS c FROM user_properties WHERE is_premium=1"); $q->execute(); $c = $q->fetch(); $data['premium_users'] = $c['c']; $q = db()->prepare("SELECT COUNT(*) AS c FROM user_properties WHERE graph_managed_type=?"); $q->execute(array('none')); $c = $q->fetch();
<?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 /** * Batch script: update external APIs status. * * 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 external status", "batch_external"); crypto_log("Current time: " . date('r')); // we just summarise the data from the last X jobs // (rather than the last X minutes, which would require a sort of all jobs) // because 'ticker' jobs are broken down into exchanges, we also want to get each individual exchange data $summary = array(); $jobs = (int) get_site_config('external_sample_size'); $queries = array("SELECT jobs.* FROM jobs\n WHERE jobs.id > (SELECT MAX(jobs.id) FROM jobs WHERE is_executed=1) - {$jobs} AND is_executed=1 AND job_type <> 'ticker' AND job_type <> 'securities_update'", "SELECT jobs.*, exchanges.name AS exchange FROM jobs\n JOIN exchanges ON jobs.arg_id=exchanges.id\n WHERE jobs.id > (SELECT MAX(jobs.id) FROM jobs WHERE is_executed=1) - {$jobs} AND is_executed=1 AND job_type = 'ticker'", "SELECT jobs.*, securities_update.exchange AS exchange FROM jobs\n JOIN securities_update ON jobs.arg_id=securities_update.id\n WHERE jobs.id > (SELECT MAX(jobs.id) FROM jobs WHERE is_executed=1) - {$jobs} AND is_executed=1 AND job_type = 'securities_update'"); $sample_size = 0; foreach ($queries as $query) { $q = db()->prepare($query); $q->execute(); while ($job = $q->fetch()) { $job_type = $job['job_type']; if (isset($job['exchange'])) { $job_type .= "_" . $job['exchange']; } if (!isset($summary[$job_type])) {
<?php /** * A batch script to clean up summary data and move it into the database as necessary. * 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 summary", "batch_cleanup_summary"); crypto_log("Current time: " . date('r')); // find all ticker data that needs to be inserted into the graph_data table // TODO currently all database dates and PHP logic is based on server side timezone, not GMT/UTC // database values need to all be modified to GMT before we can add '+00:00' for example $cutoff_date = date('Y-m-d', strtotime(get_site_config('archive_summary_data'))) . ' 23:59:59'; // +00:00'; $summary_date_prefix = " 00:00:00"; // +00:00 crypto_log("Cleaning up ticker data earlier than " . htmlspecialchars($cutoff_date) . " into summaries..."); $q = db_master()->prepare("SELECT * FROM summary_instances WHERE created_at <= :date ORDER BY created_at ASC"); $q->execute(array("date" => $cutoff_date)); // we're going to store this all in memory, because at least that way we don't have to // execute logic twice $stored = array(); $count = 0; while ($ticker = $q->fetch()) {
<?php /** * A batch script to clean up ticker data and move it into the database as necessary. * 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 ticker", "batch_cleanup_ticker"); crypto_log("Current time: " . date('r')); // find all ticker data that needs to be inserted into the graph_data table // TODO currently all database dates and PHP logic is based on server side timezone, not GMT/UTC // database values need to all be modified to GMT before we can add '+00:00' for example $cutoff_date = date('Y-m-d', strtotime(get_site_config('archive_ticker_data'))) . ' 23:59:59'; // +00:00'; $summary_date_prefix = " 00:00:00"; // +00:00 crypto_log("Cleaning up ticker data earlier than " . htmlspecialchars($cutoff_date) . " into summaries..."); // issue #243: make sure we store all historical data in a backup table $q = db_master()->prepare("REPLACE INTO ticker_historical (SELECT * FROM ticker WHERE created_at <= :date ORDER BY created_at ASC)"); $q->execute(array("date" => $cutoff_date)); crypto_log("Backed up historical data..."); $q = db_master()->prepare("SELECT * FROM ticker WHERE created_at <= :date ORDER BY created_at ASC"); $q->execute(array("date" => $cutoff_date)); // we're going to store this all in memory, because at least that way we don't have to