/** * Execute one or more queries. Does no fetching or anything, so execute only. * @param string|array $query * @return \psm\Util\Install\Installer */ protected function execSQL($query) { $query = !is_array($query) ? array($query) : $query; foreach ($query as $q) { $this->queries[] = $q; $this->db->exec($q); } return $this; }
/** * 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; }