Ubuntu
$data The telemetry data to insert
* @return int The inserted record ID
*/
public function insert(array $data) : int
{
$tableName = self::getPrefixedTableName();
$fields = ['idsite', 'server_time', 'idaction_url', 'bot_name', 'bot_type', 'http_status_code', 'response_size_bytes', 'response_time_ms', 'source'];
$values = [];
$bind = [];
foreach ($fields as $field) {
if (isset($data[$field])) {
$values[] = '?';
$bind[] = $data[$field];
} else {
$values[] = 'NULL';
}
}
$sql = sprintf('INSERT INTO `%s` (%s) VALUES (%s)', $tableName, implode(', ', $fields), implode(', ', $values));
Db::query($sql, $bind);
return (int) Db::get()->lastInsertId();
}
/**
* Delete bot telemetry records older than a specified date
*
* @param Date $date Delete records older than this date
* @return int Number of deleted records
*/
public function deleteOldRecords(Date $date) : int
{
$tableName = self::getPrefixedTableName();
$sql = sprintf('DELETE FROM `%s` WHERE server_time < ?', $tableName);
$result = Db::query($sql, [$date->getDatetime()]);
return (int) Db::get()->rowCount($result);
}
/**
* Delete bot telemetry records for specific sites
*
* @param array $siteIds Delete records older than this date
* @return int Number of deleted records
*/
public function deleteRecordsForIdSites(array $siteIds) : int
{
$tableName = self::getPrefixedTableName();
$siteIds = array_map('intval', $siteIds);
$sql = sprintf('DELETE FROM `%s` WHERE idsite IN (' . implode(', ', $siteIds) . ') LIMIT 25000', $tableName);
$result = Db::query($sql);
return (int) Db::get()->rowCount($result);
}
public function getLastServerTimeForSiteAndBotType(int $idSite, string $botType) : ?string
{
$tableName = self::getPrefixedTableName();
return Db::fetchOne(sprintf('SELECT MAX(server_time) FROM `%s` WHERE idsite = ? AND bot_type = ?', $tableName), [$idSite, $botType]);
}
/**
* @return int[]
*/
public function getDistinctIdSitesInTable(int $maxIdSite) : array
{
$tableName = self::getPrefixedTableName();
$idSitesLogTable = Db::fetchAll('SELECT DISTINCT idsite FROM ' . $tableName);
$idSitesLogTable = array_column($idSitesLogTable, 'idsite');
$idSitesLogTable = array_map('intval', $idSitesLogTable);
return array_filter($idSitesLogTable, function ($idSite) use($maxIdSite) {
return !empty($idSite) && $idSite <= $maxIdSite;
});
}
}