function macuid() { $pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid"; $timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time"; $unix = new unix(); $pid = @file_get_contents($pidfile); 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 = "SELECT * FROM webfilters_nodes WHERE LENGTH(uid)>1"; $results = $q->QUERY_SQL($sql, "artica_backup"); while ($ligne = mysql_fetch_assoc($results)) { if ($ligne["MAC"] == "00:00:00:00:00:00") { continue; } if (!IsPhysicalAddress($ligne["MAC"])) { continue; } if ($GLOBALS["VERBOSE"]) { echo "{$ligne["MAC"]} = {$ligne["uid"]}\n"; } $array[$ligne["MAC"]] = $ligne["uid"]; } $sql = "SELECT * FROM webfilters_ipaddr WHERE LENGTH(uid)>1"; $results = $q->QUERY_SQL($sql, "artica_backup"); while ($ligne = mysql_fetch_assoc($results)) { $array2[$ligne["ipaddr"]] = $ligne["uid"]; } $tablesBrutes = $q->LIST_TABLES_HOURS(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } reset($array2); while (list($ipaddr, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE client='{$ipaddr}' AND LENGTH(uid)=0"); } } $tablesBrutes = $q->LIST_TABLES_dansguardian_events(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } if (IsCompressed($tablename)) { Uncompress($tablename); } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } reset($array2); while (list($ipaddr, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE CLIENT='{$ipaddr}' AND LENGTH(uid)=0"); } } $tablesBrutes = $q->LIST_TABLES_BLOCKED_DAY(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } if (IsCompressed($tablename)) { Uncompress($tablename); } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } } $tablesBrutes = $q->LIST_TABLES_BLOCKED_WEEK(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } if (IsCompressed($tablename)) { Uncompress($tablename); } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } } $tablesBrutes = $q->LIST_TABLES_YOUTUBE_DAYS(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } if (IsCompressed($tablename)) { Uncompress($tablename); } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } reset($array2); while (list($ipaddr, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE ipaddr='{$ipaddr}' AND LENGTH(uid)=0"); } } $tablesBrutes = $q->LIST_TABLES_YOUTUBE_WEEK(); while (list($tablename, $none) = each($tablesBrutes)) { reset($array); while (list($mac, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } if (IsCompressed($tablename)) { Uncompress($tablename); } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'"); } reset($array2); while (list($ipaddr, $uid) = each($array)) { $uid = mysql_escape_string2($uid); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}, {$mac} -> {$uid}\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE ipaddr='{$ipaddr}' AND LENGTH(uid)=0"); } } uid_resets(); }
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_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"; } } }