/**
* Archive all server status records older than 1 week.
*
* Archiving means calculating averages per day, and storing 1 single
* history row for each day for each server.
*
* @param int $server_id
*/
public function archive($server_id = null)
{
$latest_date = new \DateTime('-1 week 0:0:0');
// Lock tables to prevent simultaneous archiving (by other sessions or the cron job)
try {
$this->db->pdo()->exec('LOCK TABLES ' . PSM_DB_PREFIX . 'servers_uptime WRITE, ' . PSM_DB_PREFIX . 'servers_history WRITE');
$locked = true;
} catch (\PDOException $e) {
// user does not have lock rights, ignore
$locked = false;
}
$latest_date_str = $latest_date->format('Y-m-d 00:00:00');
$sql_where_server = $this->createSQLWhereServer($server_id);
$records = $this->db->execute("SELECT `server_id`,`date`,`status`,`latency`\n\t\t\t\tFROM `" . PSM_DB_PREFIX . "servers_uptime`\n\t\t\t\tWHERE {$sql_where_server} `date` < :latest_date", array('latest_date' => $latest_date_str));
if (!empty($records)) {
// first group all records by day and server_id
$data_by_day = array();
foreach ($records as $record) {
$server_id = (int) $record['server_id'];
$day = date('Y-m-d', strtotime($record['date']));
if (!isset($data_by_day[$day][$server_id])) {
$data_by_day[$day][$server_id] = array();
}
$data_by_day[$day][$server_id][] = $record;
}
// now get history data day by day
$histories = array();
foreach ($data_by_day as $day => $day_records) {
foreach ($day_records as $server_id => $server_day_records) {
$histories[] = $this->getHistoryForDay($day, $server_id, $server_day_records);
}
}
// Save all
$this->db->insertMultiple(PSM_DB_PREFIX . 'servers_history', $histories);
// now remove all records from the uptime table
$this->db->execute("DELETE FROM `" . PSM_DB_PREFIX . "servers_uptime` WHERE {$sql_where_server} `date` < :latest_date", array('latest_date' => $latest_date_str), false);
}
if ($locked) {
$this->db->exec('UNLOCK TABLES');
}
return true;
}
