function quotatemp($aspid = false)
{
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
if ($GLOBALS["VERBOSE"]) {
echo "pidfile: {$pidfile}\n";
echo "timefile: {$timefile}\n";
}
if (!$GLOBALS["FORCE"]) {
if (!$aspid) {
$pid = @file_get_contents($pidfile);
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$timeexec = $unix->file_time_min($timefile);
if ($timeexec < 59) {
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
}
@unlink($timefile);
@file_put_contents($timefile, time());
$timekey = date('YmdH');
$stime = date("Y-m-d H:i:s");
$currenttable = "quotatemp_{$timekey}";
$q = new mysql_squid_builder();
$LIST_TABLES_QUOTA_TEMP = $q->LIST_TABLES_QUOTA_TEMP();
while (list($tableWork, $none) = each($LIST_TABLES_QUOTA_TEMP)) {
if ($tableWork == $currenttable) {
if ($GLOBALS["VERBOSE"]) {
echo "Skip Current table: {$currenttable}\n";
}
continue;
}
$xtime = $q->TIME_FROM_QUOTATEMP_TABLE($tableWork);
$Day = date("Y-m-d", $xtime);
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_TEMP:: Analyze table: {$tableWork} ({$Day})\n";
}
if (!compile_table_hour($tableWork, $xtime)) {
continue;
}
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_TEMP:: Remove table: {$tableWork} ({$Day})\n";
}
$q->QUERY_SQL("DROP TABLE {$tableWork}");
}
$table = "quotahours_" . date('YmdH');
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_HOURS:: Current table: {$table}\n";
}
$LIST_TABLES_QUOTA_HOURS = $q->LIST_TABLES_QUOTA_HOURS();
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_HOURS:: tables: " . count($LIST_TABLES_QUOTA_HOURS) . "\n";
}
print_r($LIST_TABLES_QUOTA_HOURS);
while (list($tableWork, $none) = each($LIST_TABLES_QUOTA_HOURS)) {
if ($tableWork == $table) {
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_HOURS:: Skip Current table: {$table}\n";
}
continue;
}
$xtime = $q->TIME_FROM_QUOTAHOUR_TABLE($tableWork);
$Day = date("Y-m-d", $xtime);
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_HOURS:: Analyze table: {$tableWork} ({$Day})\n";
}
if (!compile_table($tableWork, $xtime)) {
continue;
}
if ($GLOBALS["VERBOSE"]) {
echo "LIST_TABLES_QUOTA_HOURS:: Remove table: {$tableWork} ({$Day})\n";
}
$q->QUERY_SQL("DROP TABLE {$tableWork}");
}
$mysqladmin = $unix->find_program("mysqladmin");
shell_exec("{$mysqladmin} -u root -S /var/run/mysqld/squid-db.sock flush-tables >/dev/null 2>&1 &");
}
function updates_retranslation($MAC, $uid)
{
$GLOBALS["Q"] = new mysql_squid_builder();
if ($GLOBALS["VERBOSE"]) {
"echo Loading...\n";
}
$unix = new unix();
if ($GLOBALS["VERBOSE"]) {
"echo Loading done...\n";
}
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . md5($MAC . $uid) . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . md5($MAC . $uid) . ".time";
$pid = @file_get_contents($pidfile);
if (!$GLOBALS["FORCE"]) {
if ($pid < 100) {
$pid = null;
}
$unix = new unix();
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
$q = new mysql_squid_builder();
$sql = "UPDATE youtube_all SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$sql = "UPDATE UserAuthDaysGrouped SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$sql = "UPDATE UserAuthDays SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$TABLES = $q->LIST_TABLES_QUOTA_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_QUOTADAY();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_QUOTAMONTH();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_dansguardian_events();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_USERSIZED();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_DAYS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_WEEK();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_SEARCHWORDS_DAY();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_SEARCHWORDS_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_MONTH();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
}
function start_export()
{
$unix = new unix();
$sock = new sockets();
$q = new mysql_squid_builder();
$ArticaProxyStatisticsBackupFolder = $sock->GET_INFO("ArticaProxyStatisticsBackupFolder");
if ($ArticaProxyStatisticsBackupFolder == null) {
$ArticaProxyStatisticsBackupFolder = "/home/artica/squid/backup-statistics";
}
$ArticaProxyStatisticsBackupFolder = $ArticaProxyStatisticsBackupFolder . "/export";
$LIST_TABLES_YOUTUBE_HOURS = $q->LIST_TABLES_YOUTUBE_HOURS();
$LIST_TABLES_SIZEHOURS = $q->LIST_TABLES_SIZEHOURS();
$LIST_TABLES_QUOTA_HOURS = $q->LIST_TABLES_QUOTA_HOURS();
$LIST_TABLES_QUOTADAY = $q->LIST_TABLES_QUOTADAY();
$LIST_TABLES_QUOTAMONTH = $q->LIST_TABLES_QUOTAMONTH();
$LIST_TABLES_SEARCHWORDS_HOURS = $q->LIST_TABLES_SEARCHWORDS_HOURS();
$LIST_TABLES_SEARCHWORDS_DAY = $q->LIST_TABLES_SEARCHWORDS_DAY();
$LIST_TABLES_dansguardian_events = $q->LIST_TABLES_dansguardian_events();
$LIST_TABLES_HOURS = $q->LIST_TABLES_HOURS();
$LIST_TABLES_USERSIZED = $q->LIST_TABLES_USERSIZED();
$LIST_TABLES_YOUTUBE_WEEK = $q->LIST_TABLES_YOUTUBE_WEEK();
$LIST_TABLES_WEEKS = $q->LIST_TABLES_WEEKS();
$LIST_TABLES_MEMBERS = $q->LIST_TABLES_MEMBERS();
$LIST_TABLES_GSIZE = $q->LIST_TABLES_GSIZE();
$LIST_TABLES_GCACHE = $q->LIST_TABLES_GCACHE();
$LIST_TABLES_VISITED = $q->LIST_TABLES_VISITED();
$LIST_TABLES_BLOCKED = $q->LIST_TABLES_BLOCKED();
$LIST_TABLES_BLOCKED_WEEK = $q->LIST_TABLES_BLOCKED_WEEK();
$LIST_TABLES_BLOCKED_DAY = $q->LIST_TABLES_BLOCKED_DAY();
$LIST_TABLES_WWWUID = $q->LIST_TABLES_WWWUID();
$LIST_CAT_FAMDAY = $q->LIST_CAT_FAMDAY();
while (list($tablename, $none) = each($LIST_TABLES_YOUTUBE_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SIZEHOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTADAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTAMONTH)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SEARCHWORDS_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SEARCHWORDS_DAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTA_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_dansguardian_events)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_USERSIZED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED_WEEK)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_YOUTUBE_WEEK)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_WEEKS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_MEMBERS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_GSIZE)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_GCACHE)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_VISITED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED_DAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_CAT_FAMDAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_WWWUID)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($EXPORT_SOURCES)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_DESTINATIONS[] = $tablename;
}
@mkdir($ArticaProxyStatisticsBackupFolder, 0755, true);
$target_file = $ArticaProxyStatisticsBackupFolder . "/" . time() . ".sql.gz";
if (is_file($target_file)) {
@unlink($target_file);
}
$EXPORT_DESTINATIONS[] = "visited_sites";
$EXPORT_DESTINATIONS[] = "youtube_objects";
$EXPORT_DESTINATIONS[] = "UserAgents";
$EXPORT_DESTINATIONS[] = "UserAutDB";
$EXPORT_DESTINATIONS[] = "UserAuthDays";
$EXPORT_DESTINATIONS[] = "UserAuthDaysGrouped";
$EXPORT_DESTINATIONS[] = "UserSizeRTT";
$EXPORT_DESTINATIONS[] = "allsizes";
$unix = new unix();
$mysqldump = $unix->find_program("mysqldump");
$bzip2 = $unix->find_program("bzip2");
$bzip2_cmd = "| {$bzip2} ";
$AllTables = @implode(" ", $EXPORT_DESTINATIONS);
$cmd = "{$mysqldump} -S /var/run/mysqld/squid-db.sock --single-transaction --skip-add-drop-table --no-create-db --insert-ignore --skip-add-locks --skip-lock-tables squidlogs {$AllTables} {$bzip2_cmd}> {$target_file} 2>&1";
$t = time();
$failed = false;
if ($GLOBALS["VERBOSE"]) {
echo $cmd . "\n";
}
exec($cmd, $results);
while (list($index, $line) = each($results)) {
if (preg_match("#Couldn't#", $line)) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$line} took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
if (preg_match("#Error\\s+([0-9]+)#", $line)) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$line} took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
echo "{$line}\n";
}
$size = @filesize($target_file);
if ($size < 10000) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$size}Bytes < 10000bytes took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
if ($GLOBALS["VERBOSE"]) {
echo "{$target_file} {$size}Bytes " . FormatBytes($size / 1024) . "\n";
}
reset($EXPORT_SOURCES);
while (list($tablename, $none) = each($EXPORT_SOURCES)) {
$q->QUERY_SQL("DROP TABLE {$tablename}");
if ($GLOBALS["VERBOSE"]) {
echo "Removing table {$tablename}\n";
}
}
}
