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 quotatable() { $q = new mysql_squid_builder(); $LIST_TABLES_QUOTADAY = $q->LIST_TABLES_QUOTADAY(); }
function ScanDays($onlyTable = false) { $q = new mysql_squid_builder(true); $ARRAY_DAYS = array(); $tables = $q->LIST_TABLES_YOUTUBE_DAYS(); //youtubeday_ while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_YOUTUBE_DAY_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; $TABLES[$tablename] = true; } $tables = $q->LIST_TABLES_USERSIZED(); //youtubeday_ while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_USERSIZED_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; $TABLES[$tablename] = true; } $tables = $q->LIST_TABLES_QUOTADAY(); //youtubeday_ while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_QUOTADAY_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; $TABLES[$tablename] = true; } $tables = $q->LIST_CAT_FAMDAY(); //youtubeday_ while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_CAT_FAMDAY_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; $TABLES[$tablename] = true; } $prefix = "INSERT IGNORE INTO tables_day (tablename,zDate) VALUES "; while (list($day, $dayTime) = each($ARRAY_DAYS)) { $tablename = "dansguardian_events_" . date("Ymd", $dayTime); $f[] = "('{$tablename}','{$day}')"; } if (count($f) > 0) { $q->QUERY_SQL($prefix . @implode(",", $f)); if (!$q->ok) { if ($GLOBALS["VERBOSE"]) { echo "Fatal {$q->mysql_error}\n"; } squid_admin_purge(0, "Fatal {$q->mysql_error}", "Backup process cannot be performed", __FILE__, __LINE__); ufdbguard_admin_events("Fatal {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "backup"); return false; } } if ($onlyTable) { return $TABLES; } return true; }
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"; } } }