/**
* Find the next job that should be executed.
* By default, just selects any job instance that is in the database that isn't
* already executing, hasn't already been finished, and hasn't errored out.
* @return a job array (id, job_type, [user_id], [arg_id]) or {@code false} if there is none
*/
function findJob(Connection $db, Logger $logger)
{
// TODO timeout jobs
// mark all repeatedly failing jobs as failing
$execution_limit = Config::get("job_execution_limit", 5);
$q = $db->prepare("SELECT * FROM jobs WHERE is_executed=0 AND is_executing=0 AND execution_count >= ?");
$q->execute(array($execution_limit));
if ($failed = $q->fetchAll()) {
$logger->info("Found " . number_format(count($failed)) . " jobs that have executed too many times ({$execution_limit})");
foreach ($failed as $f) {
$q = $db->prepare("UPDATE jobs SET is_executed=1,is_error=1 WHERE id=?");
$q->execute(array($f['id']));
$logger->info("Marked job " . $f['id'] . " as failed");
}
}
// find first a job that has zero execution count
$q = $db->prepare("SELECT * FROM jobs WHERE " . $this->defaultFindJobQuery() . " AND execution_count=0 LIMIT 1");
$q->execute();
if ($job = $q->fetch()) {
return $job;
}
// or, any job
$q = $db->prepare("SELECT * FROM jobs WHERE " . $this->defaultFindJobQuery() . " LIMIT 1");
$q->execute();
return $q->fetch();
}
/**
* Find a job that belongs to a premium user.
*/
function findJob(Connection $db, Logger $logger)
{
if ($this->isJobsDisabled($logger)) {
return false;
}
$q = $db->prepare("SELECT * FROM jobs WHERE user_id IN (SELECT id FROM user_properties WHERE is_premium=1) AND " . $this->defaultFindJobQuery() . " LIMIT 1");
$q->execute();
return $q->fetch();
}
/**
* Find a job that belongs to the system user.
*/
function findJob(Connection $db, Logger $logger)
{
if ($this->isJobsDisabled($logger)) {
return false;
}
$q = $db->prepare("SELECT * FROM jobs WHERE user_id = ? AND " . $this->defaultFindJobQuery() . " LIMIT 1");
$q->execute(array(get_site_config('system_user_id')));
return $q->fetch();
}
/**
* Find a job that is a test job.
*/
function findJob(Connection $db, Logger $logger)
{
if ($this->isJobsDisabled($logger)) {
return false;
}
$q = $db->prepare("SELECT * FROM jobs WHERE is_test_job=1 AND " . $this->defaultFindJobQuery() . " LIMIT 1");
$q->execute();
return $q->fetch();
}
/**
* Find a job that starts with the given prefix
*/
function findJob(Connection $db, Logger $logger)
{
if ($this->isJobsDisabled($logger)) {
return false;
}
$q = $db->prepare("SELECT * FROM jobs WHERE (job_prefix=? OR job_type=?) AND " . $this->defaultFindJobQuery() . " LIMIT 1");
$q->execute(array($this->job_prefix, $this->job_prefix));
return $q->fetch();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(Connection $db)
{
if (!file_exists($this->file)) {
throw new \Exception("Could not load file '" . $this->file . "'");
}
$sql = file_get_contents($this->file);
$q = $db->prepare($sql);
return $q->execute();
}
/**
* Get a list of all job instances that should be run soon.
* @return a list of job parameters
*/
function getPending(\Db\Connection $db)
{
$q = $db->prepare("SELECT * FROM performance_reports WHERE created_at > DATE_SUB(NOW(), INTERVAL " . \Openclerk\Config::get("job_metrics_report_interval", 60 * 12) . " MINUTE) LIMIT 1");
$q->execute();
if (!$q->fetch()) {
// there's no recent reports; make another one
return array(array("job_type" => $this->getName(), "arg" => null));
}
return array();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$mapping = array('litecoin_block' => 'blockcount_ltc', 'feathercoin_block' => 'blockcount_ftc', 'ppcoin_block' => 'blockcount_ppc', 'novacoin_block' => 'blockcount_nvc', 'primecoin_block' => 'blockcount_xpm', 'terracoin_block' => 'blockcount_trc', 'dogecoin_block' => 'blockcount_dog', 'megacoin_block' => 'blockcount_mec', 'ripple_block' => 'blockcount_xrp', 'namecoin_block' => 'blockcount_nmc', 'digitalcoin_block' => 'blockcount_dgc', 'worldcoin_block' => 'blockcount_wdc', 'ixcoin_block' => 'blockcount_ixc', 'vertcoin_block' => 'blockcount_vtc', 'netcoin_block' => 'blockcount_net', 'hobonickels_block' => 'blockcount_hbn', 'blackcoin_block' => 'blockcount_bc1', 'darkcoin_block' => 'blockcount_drk', 'vericoin_block' => 'blockcount_vrc', 'nxt_block' => 'blockcount_nxt', 'reddcoin_block' => 'blockcount_rdd', 'viacoin_block' => 'blockcount_via', 'nubits_block' => 'blockcount_nbt', 'nushares_block' => 'blockcount_nsr');
foreach ($mapping as $from => $to) {
$q = $db->prepare("UPDATE external_status SET job_type=? WHERE job_type=?");
if (!$q->execute(array($to, $from))) {
throw new \Exception("Could not apply external_status migration '{$from}' to '{$to}'");
}
$q = $db->prepare("UPDATE external_status_types SET job_type=? WHERE job_type=?");
if (!$q->execute(array($to, $from))) {
throw new \Exception("Could not apply external_status_types migration '{$from}' to '{$to}'");
}
}
return true;
}
/**
* If we have a ?job_id parameter, then select only this job
*/
function findJob(Connection $db, Logger $logger)
{
if ($this->isJobsDisabled($logger)) {
return false;
}
// mark all once-failed test jobs as failing
$q = $db->prepare("SELECT * FROM jobs WHERE is_executing=0 AND is_error=0 AND is_test_job=1 AND execution_count >= ?");
$q->execute(array(1));
if ($failed = $q->fetchAll()) {
$logger->info("Found " . number_format(count($failed)) . " test jobs that have failed once");
foreach ($failed as $job) {
$q = $db->prepare("UPDATE jobs SET is_executed=1,is_error=1 WHERE id=?");
$q->execute(array($job['id']));
$logger->info("Marked test job " . $job['id'] . " as failed");
}
}
// mark all jobs that have been executing for far too long as failed
$q = $db->prepare("SELECT * FROM jobs WHERE is_executing=1 AND execution_started <= DATE_SUB(NOW(), INTERVAL 30 MINUTE)");
$q->execute(array());
if ($timeout = $q->fetchAll()) {
$logger->info("Found " . number_format(count($timeout)) . " jobs that have timed out");
foreach ($timeout as $job) {
$q = $db->prepare("UPDATE jobs SET is_timeout=1, is_executing=0 WHERE id=?");
$q->execute(array($job['id']));
$logger->info("Marked job " . $job['id'] . " as timed out");
}
}
if (require_get("job_id", false)) {
$q = $db->prepare("SELECT * FROM jobs WHERE id=? LIMIT 1");
$q->execute(array(require_get("job_id")));
return $q->fetch();
} else {
return parent::findJob($db, $logger);
}
}
/**
* Get a list of all jobs that need to be queued, as an array of associative
* arrays with (job_type, arg_id, [user_id]).
*
* This could use e.g. {@link JobTypeFinder}
*/
function findJobs(Connection $db, Logger $logger)
{
$logger->info("Creating temporary table");
$q = $db->prepare("CREATE TABLE temp (\n created_at_day INT NOT NULL,\n INDEX(created_at_day)\n )");
$q->execute();
$logger->info("Inserting into temporary table");
$q = $db->prepare("INSERT INTO temp (SELECT created_at_day FROM ticker WHERE exchange = 'average' GROUP BY created_at_day)");
$q->execute();
$logger->info("Querying");
$q = $db->prepare("SELECT created_at_day, min(created_at) as date, count(*) as c\n FROM ticker\n WHERE exchange <> 'average' AND exchange <> 'themoneyconverter' and is_daily_data=1 and created_at_day not in (SELECT created_at_day FROM temp)\n GROUP BY created_at_day");
$q->execute();
$missing = $q->fetchAll();
$logger->info("Dropping temporary table");
$q = $db->prepare("DROP TABLE temp");
$q->execute();
$logger->info("Found " . number_format(count($missing)) . " days of missing average data");
$result = array();
foreach ($missing as $row) {
$logger->info("Average data for " . $row['date'] . " can be reconstructed from " . number_format($row['c']) . " ticker instances");
$result[] = array('job_type' => 'missing_average', 'arg_id' => $row['created_at_day'], 'user_id' => get_site_config('system_user_id'));
}
return $result;
}
/**
* Get the most recent database cache value for the given $key and $hash,
* or recompile it from the $callback and $arguments if the database cache value
* is more than $age seconds old.
*
* @param $db the database connection to use
* @param $key the cache key, must be < 255 chars string
* @param $hash must be < 32 chars string
* @param $age the maximum age for the cache in seconds
* @param $callback the function which will generate the content if the cache is invalidated or missing,
* must return less than 16 MB of content
* @param $args the arguments to pass to the callback, if any
*/
static function get(Connection $db, $key, $hash, $age, $callback, $args = array())
{
if (strlen($hash) > 255) {
throw new CacheException("Cannot cache with a key longer than 255 characters");
}
if (strlen($hash) > 32) {
throw new CacheException("Cannot cache with a hash longer than 32 characters");
}
$q = $db->prepare("SELECT * FROM cached_strings WHERE cache_key=? AND cache_hash=? AND created_at >= DATE_SUB(NOW(), INTERVAL {$age} SECOND)");
$q->execute(array($key, $hash));
if ($cache = $q->fetch()) {
$result = $cache['content'];
} else {
$result = call_user_func_array($callback, $args);
$q = $db->prepare("DELETE FROM cached_strings WHERE cache_key=? AND cache_hash=?");
$q->execute(array($key, $hash));
if (strlen($result) >= pow(2, 24)) {
throw new CacheException("Cache value is too large (> 16 MB)");
}
$q = $db->prepare("INSERT INTO cached_strings SET cache_key=?, cache_hash=?, content=?");
$q->execute(array($key, $hash, $result));
}
return $result;
}
function run(\Db\Connection $db, Logger $logger)
{
// "What pages are taking the longest to load?"
// "What pages spend the most time in PHP as opposed to the database?"
$report_type = "pages_slow";
// select the worst URLs
$q = $db->prepare("SELECT script_name, SUM(time_taken) AS time_taken, COUNT(id) AS page_count,\n SUM(db_prepare_time) + SUM(db_execute_time) + SUM(db_fetch_time) + SUM(db_fetch_all_time) AS database_time FROM performance_metrics_pages\n GROUP BY script_name ORDER BY SUM(time_taken) / COUNT(id) LIMIT " . \Openclerk\Config::get('metrics_report_count', 20));
$q->execute();
$data = $q->fetchAll();
$q = $db->prepare("INSERT INTO performance_reports SET report_type=?");
$q->execute(array($report_type));
$report_id = $db->lastInsertId();
foreach ($data as $row) {
$q = $db->prepare("INSERT INTO performance_report_slow_pages SET report_id=?, script_name=?, page_time=?, page_count=?, page_database=?");
$q->execute(array($report_id, $row['script_name'], $row['time_taken'], $row['page_count'], $row['database_time']));
}
$logger->info("Created report '{$report_type}'");
// we've processed all the data we want; delete old metrics data
$q = $db->prepare("DELETE FROM performance_metrics_pages");
$q->execute();
$logger->info("Deleted old metric data");
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE performance_report_slow_pages (\n id int not null auto_increment primary key,\n report_id int not null,\n created_at timestamp not null default current_timestamp,\n\n script_name varchar(255) null, -- might be null if running from CLI; probably not though\n\n page_count int not null,\n page_time int not null,\n page_database int null,\n\n INDEX(report_id)\n );");
return $q->execute();
}
/**
* Delete the given user. Triggers a 'user_deleted' event with the
* current user as an argument.
*/
function delete(\Db\Connection $db)
{
// delete all possible identities
$q = $db->prepare("DELETE FROM user_passwords WHERE user_id=?");
$q->execute(array($this->getId()));
$q = $db->prepare("DELETE FROM user_openid_identities WHERE user_id=?");
$q->execute(array($this->getId()));
$q = $db->prepare("DELETE FROM user_oauth2_identities WHERE user_id=?");
$q->execute(array($this->getId()));
$q = $db->prepare("DELETE FROM user_valid_keys WHERE user_id=?");
$q->execute(array($this->getId()));
$q = $db->prepare("DELETE FROM users WHERE id=?");
$q->execute(array($this->getId()));
\Openclerk\Events::trigger('user_deleted', $this);
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("DELETE FROM securities_update WHERE exchange=?");
return $q->execute(array('eligius'));
}
/**
* Remove the given OpenID identity from the given user.
*/
static function removeIdentity(\Db\Connection $db, User $user, $openid)
{
if (!$user) {
throw new \InvalidArgumentException("No user provided.");
}
$q = $db->prepare("DELETE FROM user_openid_identities WHERE user_id=? AND identity=? LIMIT 1");
return $q->execute(array($user->getId(), $openid));
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE users (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n updated_at timestamp null,\n\n email varchar(255) not null,\n last_login timestamp null,\n\n INDEX(email)\n );");
return $q->execute();
}
/**
* 得到写数据库.
*
* @return \Db\Connection
*/
public function getWriteDb()
{
return \Db\Connection::instance()->write($this->db());
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE user_oauth2_identities (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n user_id int not null,\n provider varchar(64) not null,\n uid varchar(64) not null,\n\n INDEX(user_id)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(Connection $db)
{
$q = $db->prepare("CREATE TABLE " . $this->getTable() . " (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n blockcount int not null,\n is_recent tinyint not null default 0,\n\n INDEX(is_recent)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE jobs (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n job_type varchar(32) not null,\n user_id int null,\n arg_id int null,\n\n is_executed tinyint not null default 0,\n is_executing tinyint not null default 0,\n is_error tinyint not null default 0,\n is_recent tinyint not null default 0,\n is_timeout tinyint not null default 0,\n\n execution_started timestamp null,\n executed_at timestamp null,\n execution_count tinyint not null default 0,\n\n INDEX(job_type),\n INDEX(user_id),\n INDEX(arg_id),\n\n INDEX(is_executed, is_executing, is_error),\n INDEX(is_executing),\n INDEX(is_error),\n INDEX(is_recent),\n INDEX(is_timeout)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("ALTER TABLE user_passwords\n ADD reset_password_secret varchar(128) null,\n ADD reset_password_requested timestamp null;\n ");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE cached_strings (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n cache_key varchar(255) not null,\n cache_hash varchar(32) not null,\n\n content mediumblob not null, /* up to 16 MB */\n\n UNIQUE(cache_key, cache_hash)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE uncaught_exceptions (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n message varchar(255) null,\n previous_message varchar(255) null,\n class_name varchar(255) null,\n filename varchar(255) null,\n line_number int null,\n raw blob null,\n\n argument_id int null,\n argument_type varchar(255) null,\n\n INDEX(class_name),\n INDEX(argument_type, argument_id)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("INSERT INTO users (id, created_at, updated_at, email, last_login) (SELECT id, created_at, updated_at, email, last_login FROM user_properties)");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("ALTER TABLE users\n MODIFY email varchar(255) null;");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(Connection $db)
{
$q = $db->prepare("CREATE TABLE " . $this->getTable() . " (\n id int not null auto_increment primary key,\n user_id int not null,\n created_at timestamp not null default current_timestamp,\n last_queue timestamp null,\n title varchar(255),\n\n " . $this->generateApiFields() . "\n\n is_disabled tinyint not null default 0,\n failures tinyint not null default 0,\n first_failure tinyint not null default 0,\n is_disabled_manually tinyint not null default 0,\n\n INDEX(user_id),\n INDEX(last_queue),\n INDEX(is_disabled),\n INDEX(is_disabled_manually)\n );");
return $q->execute();
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(\Db\Connection $db)
{
$q = $db->prepare("CREATE TABLE performance_metrics_slow_urls (\n id int not null auto_increment primary key,\n url_id int not null,\n url_count int not null,\n url_time int not null,\n page_id int not null,\n\n INDEX(url_id),\n INDEX(page_id)\n );");
return $q->execute();
}
/**
* Implements failing tables; if an account type fails multiple times,
* then send the user an email and disable the account.
* @see OpenclerkJobQueuer#getStandardJobs()
*/
function failed(\Exception $runtime_exception, Connection $db, Logger $logger)
{
// is this a standard job?
$standard = $this->findStandardJob();
if ($standard) {
$logger->info("Using standard job " . print_r($standard, true));
if (!$standard['failure']) {
$logger->info("Not a failure standard job");
return;
}
} else {
return;
}
$failing_table = $standard['table'];
$job = $this->job;
// find the relevant account_data for this standard job
$account_data = false;
foreach (account_data_grouped() as $label => $group) {
foreach ($group as $exchange => $data) {
if (isset($data['job_type']) && $job['job_type'] == $data['job_type']) {
$account_data = $data;
$account_data['exchange'] = $exchange;
break;
}
}
}
if (!$account_data) {
$logger->warn("Could not find any account data for job type '" . $job['job_type'] . "'");
}
$logger->info("Using account data " . print_r($account_data, true));
// don't count CloudFlare as a failure
if ($runtime_exception instanceof CloudFlareException || $runtime_exception instanceof \Openclerk\Apis\CloudFlareException) {
$logger->info("Not increasing failure count: was a CloudFlareException");
} else {
if ($runtime_exception instanceof IncapsulaException || $runtime_exception instanceof \Openclerk\Apis\IncapsulaException) {
$logger->info("Not increasing failure count: was a IncapsulaException");
} else {
if ($runtime_exception instanceof BlockchainException || $runtime_exception instanceof \Core\BlockchainException) {
$logger->info("Not increasing failure count: was a BlockchainException");
} else {
$q = $db->prepare("UPDATE {$failing_table} SET failures=failures+1,first_failure=IF(ISNULL(first_failure), NOW(), first_failure) WHERE id=?");
$q->execute(array($job['arg_id']));
$logger->info("Increasing account failure count");
}
}
}
$user = get_user($job['user_id']);
if (!$user) {
$logger->info("Warning: No user " . $job['user_id'] . " found");
} else {
// failed too many times?
$q = $db->prepare("SELECT * FROM {$failing_table} WHERE id=? LIMIT 1");
$q->execute(array($job['arg_id']));
$account = $q->fetch();
$logger->info("Current account failure count: " . number_format($account['failures']));
if ($account['failures'] >= get_premium_value($user, 'max_failures')) {
// disable it and send an email
$q = $db->prepare("UPDATE {$failing_table} SET is_disabled=1 WHERE id=?");
$q->execute(array($job['arg_id']));
crypto_log(print_r($account_data, true));
if ($user['email'] && !$account['is_disabled']) {
$email_type = $job['job_type'] == "notification" ? "failure_notification" : "failure";
send_user_email($user, $email_type, array("name" => $user['name'] ? $user['name'] : $user['email'], "exchange" => get_exchange_name($account_data['exchange']), "label" => $account_data['label'], "labels" => $account_data['labels'], "failures" => number_format($account['failures']), "message" => $runtime_exception->getMessage(), "length" => recent_format(strtotime($account['first_failure']), "", ""), "title" => isset($account['title']) && $account['title'] ? "\"" . $account['title'] . "\"" : "untitled", "url" => absolute_url(url_for("wizard_accounts"))));
$logger->info("Sent failure e-mail to " . htmlspecialchars($user['email']) . ".");
}
}
}
}
/**
* Apply only the current migration.
* @return true on success or false on failure
*/
function apply(Connection $db)
{
$q = $db->prepare("CREATE TABLE account_hash_currencies (\n id int not null auto_increment primary key,\n created_at timestamp not null default current_timestamp,\n\n exchange varchar(32) not null,\n currency varchar(3) not null,\n\n UNIQUE(exchange, currency)\n );");
return $q->execute();
}