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_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_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($xtime = 0) { $dayFilter = 0; if ($xtime > 0) { $dayFilter = date("Ymd", $xtime); } $unix = new unix(); $pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . ".{$xtime}.pid"; $pidTime = "/etc/artica-postfix/pids/YoutubeByHour.time"; $pid = $unix->get_pid_from_file($pidfile); if ($unix->process_exists($pid)) { $timepid = $unix->PROCCESS_TIME_MIN($pid); die; } @unlink($pidfile); @file_put_contents($pidfile, getmypid()); @unlink($pidTime); @file_put_contents($pidTime, time()); if (isset($GLOBALS["youtube_days_executed"])) { return; } $GLOBALS["youtube_days_executed"] = true; $q = new mysql_squid_builder(); $timekey = date('YmdH'); $Today = date("Y-m-d H"); $currenttable = "youtubehours_{$timekey}"; $LIST_TABLES_YOUTUBE_HOURS = $q->LIST_TABLES_YOUTUBE_HOURS(); youtube_events("LIST_TABLES_YOUTUBE_HOURS = " . count($LIST_TABLES_YOUTUBE_HOURS)); while (list($tablesource, $value) = each($LIST_TABLES_YOUTUBE_HOURS)) { if ($tablesource == $currenttable) { continue; } $tablesourcetime = $q->TIME_FROM_YOUTUBE_HOUR_TABLE($tablesource); if (date("Y-m-d H", $tablesourcetime) == $Today) { continue; } if ($dayFilter > 0) { if (date("Ymd", $tablesourcetime) != $dayFilter) { continue; } } youtube_events("Processing Youtube table {$tablesource}", __LINE__); if ($q->COUNT_ROWS($tablesource) == 0) { $q->QUERY_SQL("DROP TABLE `{$tablesource}`"); continue; } if (!_youtube_days($tablesource)) { continue; } $q->QUERY_SQL("DROP TABLE {$tablesource}"); } if (count($GLOBALS["YOUTUBE_IDS"]) > 0) { _youtube_ids(); } if (!$GLOBALS["ONLYHOURS"]) { youtube_events("youtube_count()"); youtube_count(); youtube_events("youtube_dayz()"); youtube_dayz(); } }
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"; } } }