public static function run()
{
//find oldest value
$oldest = \SYSTEM\SQL\SYS_LOG_OLDEST::Q1();
list($now_month, $now_year) = preg_split("/ /", date("m Y"));
//All fine -> abort
if ($oldest['year'] >= $now_year && $oldest['month'] >= $now_month) {
return cronstatus::CRON_STATUS_SUCCESFULLY;
}
//create folder if required
if (!file_exists(\SYSTEM\CONFIG\config::get(\SYSTEM\CONFIG\config_ids::SYS_CRON_LOG2SQLITE_PATH))) {
mkdir(\SYSTEM\CONFIG\config::get(\SYSTEM\CONFIG\config_ids::SYS_CRON_LOG2SQLITE_PATH), 0777, true);
}
$filename = \SYSTEM\CONFIG\config::get(\SYSTEM\CONFIG\config_ids::SYS_CRON_LOG2SQLITE_PATH) . $oldest['year'] . '.' . $oldest['month'] . '.db';
//extract whole month to file
$con = new \SYSTEM\DB\Connection(new \SYSTEM\DB\DBInfoSQLite($filename));
//create table
$con->query('CREATE TABLE IF NOT EXISTS `system_log` (' . ' `ID` INT(10) NOT NULL,' . ' `class` TEXT NOT NULL,' . ' `message` TEXT NOT NULL,' . ' `code` INT(11) NOT NULL,' . ' `file` TEXT NOT NULL,' . ' `line` INT(11) NOT NULL,' . ' `trace` TEXT NOT NULL,' . ' `ip` TEXT NOT NULL,' . ' `querytime` DOUBLE NOT NULL,' . ' `time` DATETIME NOT NULL,' . ' `server_name` CHAR(255) NOT NULL,' . ' `server_port` INT(10) NOT NULL,' . ' `request_uri` CHAR(255) NOT NULL,' . ' `post` TEXT NOT NULL,' . ' `http_referer` CHAR(255) NULL DEFAULT NULL,' . ' `http_user_agent` TEXT NOT NULL,' . ' `user` INT(11) NULL DEFAULT NULL,' . ' `thrown` BIT(1) NOT NULL,' . ' PRIMARY KEY (`ID`)' . ');');
//write data as trasaction
$con->exec('begin transaction');
set_time_limit(30);
$res = \SYSTEM\SQL\SYS_LOG_MONTH::QQ(array($oldest['month'], $oldest['year']));
while ($row = $res->next()) {
set_time_limit(30);
$row['time'] = array_key_exists('time_pg', $row) ? date("Y-m-d H:i:s", $row['time_pg']) : $row['time'];
if (!$con->exec('INSERT OR IGNORE INTO ' . \SYSTEM\SQL\system_log::NAME_MYS . '(`ID`, `class`, `message`, `code`, `file`, `line`, `trace`, `ip`, `querytime`, `time`,' . ' `server_name`, `server_port`, `request_uri`, `post`,' . ' `http_referer`, `http_user_agent`, `user`, `thrown`)' . 'VALUES (' . $row['ID'] . ', \'' . \SQLite3::escapeString($row['class']) . '\', \'' . \SQLite3::escapeString($row['message']) . '\', ' . $row['code'] . ', \'' . \SQLite3::escapeString($row['file']) . '\', ' . $row['line'] . ', \'' . \SQLite3::escapeString($row['trace']) . '\', \'' . $row['ip'] . '\', ' . $row['querytime'] . ', \'' . $row['time'] . '\', \'' . \SQLite3::escapeString($row['server_name']) . '\', ' . ($row['server_port'] ? $row['server_port'] : 'NULL') . ', \'' . \SQLite3::escapeString($row['request_uri']) . '\', \'' . \SQLite3::escapeString($row['post']) . '\', \'' . \SQLite3::escapeString($row['http_referer']) . '\', \'' . \SQLite3::escapeString($row['http_user_agent']) . '\', ' . ($row['user'] ? $row['user'] : '******') . ',' . true . ');')) {
new \SYSTEM\LOG\ERROR('failed to insert into log archiev');
return cronstatus::CRON_STATUS_FAIL;
}
//Delete single
if (!\SYSTEM\SQL\SYS_LOG_DEL::QI(array($row['ID']))) {
new \SYSTEM\LOG\ERROR('failed to delete log entries');
return cronstatus::CRON_STATUS_FAIL;
}
}
set_time_limit(30);
if (!$con->exec('end transaction')) {
new \SYSTEM\LOG\ERROR('failed to insert into log archiev');
return cronstatus::CRON_STATUS_FAIL;
}
return cronstatus::CRON_STATUS_SUCCESFULLY;
}
public static function QQ($dbinfo = null)
{
if (!$dbinfo) {
$dbinfo = \SYSTEM\system::getSystemDBInfo();
}
$con = new \SYSTEM\DB\Connection($dbinfo);
try {
if ($dbinfo instanceof \SYSTEM\DB\DBInfoPG) {
if (!\is_callable(static::get_class() . '::pgsql')) {
throw new \SYSTEM\LOG\ERROR(static::get_class() . ' failed: no pgsql implementation present.');
}
return $con->query(static::pgsql());
} else {
if ($dbinfo instanceof \SYSTEM\DB\DBInfoMYS) {
if (!\is_callable(static::get_class() . '::mysql')) {
throw new \SYSTEM\LOG\ERROR(static::get_class() . ' failed: no mysql implementation present.');
}
return $con->query(static::mysql());
} else {
if ($dbinfo instanceof \SYSTEM\DB\DBInfoAMQP) {
if (!\is_callable(static::get_class() . '::amqp')) {
throw new \SYSTEM\LOG\ERROR(static::get_class() . ' failed: no amqp implementation present.');
}
return $con->query(static::amqp());
} else {
if ($dbinfo instanceof \SYSTEM\DB\DBInfoSQLite) {
if (!\is_callable(static::get_class() . '::sqlite')) {
throw new \SYSTEM\LOG\ERROR(static::get_class() . ' failed: no sqlite implementation present.');
}
return $con->query(static::sqlite());
}
}
}
}
} catch (\Exception $e) {
throw new \Exception(static::get_class() . ' failed causing: ' . $e->getMessage(), $e->getCode(), $e);
}
throw new \Exception('Could not understand Database Settings. Check ur Database Settings');
}
public static function sai_mod__SYSTEM_SAI_saimod_sys_log_action_stats_name_basic_querytime($filter, $db)
{
$result = array();
if (!$db) {
$result = \SYSTEM\SQL\SYS_SAIMOD_LOG_BASIC_QUERYTIME::QA(array($filter));
} else {
$con = new \SYSTEM\DB\Connection(new \SYSTEM\DB\DBInfoSQLite(\SYSTEM\CONFIG\config::get(\SYSTEM\CONFIG\config_ids::SYS_CRON_LOG2SQLITE_PATH) . $db));
$res = $con->prepare('basic_querytime', 'SELECT datetime(strftime("%s",' . \SYSTEM\SQL\system_log::FIELD_TIME . ') - strftime("%s",' . \SYSTEM\SQL\system_log::FIELD_TIME . ')%:filter,"unixepoch", "localtime") as day,' . 'count(*) as count,' . 'avg(' . \SYSTEM\SQL\system_log::FIELD_QUERYTIME . ') as querytime_avg,' . 'max(' . \SYSTEM\SQL\system_log::FIELD_QUERYTIME . ') as querytime_max,' . 'min(' . \SYSTEM\SQL\system_log::FIELD_QUERYTIME . ') as querytime_min' . ' FROM ' . \SYSTEM\SQL\system_log::NAME_MYS . ' GROUP BY day' . ' ORDER BY day DESC' . ' LIMIT 30;', array(':filter' => $filter));
while ($row = $res->next()) {
$result[] = $row;
}
}
return \SYSTEM\LOG\JsonResult::toString($result);
}