function finduser($username) { $q = new mysql_squid_builder(); $TABLES = $q->LIST_TABLES_HOURS(); $username = trim($username); while (list($tablesource, $b) = each($TABLES)) { $results = $q->QUERY_SQL("SELECT * FROM {$tablesource} WHERE uid='{$username}'"); if (mysql_num_rows($results) > 0) { echo "Found in {$tablesource}\n"; } } }
die; } $mypid = getmypid(); @file_put_contents($pidfile, $mypid); $q = new mysql_squid_builder(); $sql = "SELECT * FROM categorize_changes"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { writelogs("Fatal Error: {$q->mysql_error}", __CLASS__ . '/' . __FUNCTION__, __FILE__, __LINE__); die; } if (mysql_num_rows($results) == 0) { echo "No changes\n"; die; } $table_hours = $q->LIST_TABLES_HOURS(); $table_days = $q->LIST_TABLES_DAYS(); $table_week = $q->LIST_TABLES_WEEKS(); $ALREADY = array(); $MAX = mysql_num_rows($results); $D = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { if (isset($ALREADY[$ligne["sitename"]])) { $q->QUERY_SQL("DELETE FROM categorize_changes WHERE `zmd5`='{$ligne["zmd5"]}'"); continue; } $website = $ligne["sitename"]; $categories = $q->GET_CATEGORIES($website, true); $ALREADY[$ligne["sitename"]] = true; reset($table_hours); reset($table_days);
function remove_numeric_members() { $unix = new unix(); $pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid"; $pid = @file_get_contents($pidfile); if ($pid < 100) { $pid = null; } if ($unix->process_exists($pid, basename(__FILE__))) { $timepid = $unix->PROCCESS_TIME_MIN($pid); ufdbguard_admin_events("Already executed pid {$pid} since {$timepid}", __FUNCTION__, __FILE__, __LINE__, "reports"); if ($GLOBALS["VERBOSE"]) { echo "Already executed pid {$pid}\n"; } return; } $q = new mysql_squid_builder(); $q->QUERY_SQL("DELETE FROM members_uid WHERE uid REGEXP '^[0-9]+\$'"); $q->QUERY_SQL("DELETE FROM UserAuthDaysGrouped WHERE uid REGEXP '^[0-9]+\$'"); $q->QUERY_SQL("DELETE FROM UserAutDB WHERE uid REGEXP '^[0-9]+\$'"); $tables = $q->LIST_TABLES_WWWUID(); while (list($tablename, $ligne) = each($tables)) { if (!preg_match("#^www_[0-9]+\$#", $ligne)) { continue; } $q->QUERY_SQL("DROP TABLE {$tablename}"); } $tables = $q->LIST_TABLES_DAYS(); while (list($tablename, $ligne) = each($tables)) { $q->QUERY_SQL("DELETE FROM {$tablename} WHERE uid REGEXP '^[0-9]+\$'"); } $tables = $q->LIST_TABLES_HOURS(); while (list($tablename, $ligne) = each($tables)) { $q->QUERY_SQL("DELETE FROM {$tablename} WHERE uid REGEXP '^[0-9]+\$'"); } $tables = $q->LIST_TABLES_WEEKS(); while (list($tablename, $ligne) = each($tables)) { $q->QUERY_SQL("DELETE FROM {$tablename} WHERE uid REGEXP '^[0-9]+\$'"); } $tables = $q->LIST_TABLES_MONTH(); while (list($tablename, $ligne) = each($tables)) { $q->QUERY_SQL("DELETE FROM {$tablename} WHERE uid REGEXP '^[0-9]+\$'"); } $tables = $q->LIST_TABLES_MEMBERS(); while (list($tablename, $ligne) = each($tables)) { $q->QUERY_SQL("DELETE FROM {$tablename} WHERE uid REGEXP '^[0-9]+\$'"); } }
function REPAIR_WORKING_TABLES() { $q = new mysql_squid_builder(); $LIST_TABLES_DAYS = $q->LIST_TABLES_HOURS(); events(count($LIST_TABLES_DAYS) . " daily tables"); while (list($tablename, $ligne) = each($LIST_TABLES_DAYS)) { $xtime = $q->TIME_FROM_HOUR_TABLE($tablename); $xdate = date("Y-m-d", $xtime); $danstable = "dansguardian_events_" . date("Ymd", $xtime); if ($GLOBALS["VERBOSE"]) { echo "{$tablename}: {$danstable} = {$xdate}\n"; } $q->QUERY_SQL("INSERT IGNORE INTO tables_day (tablename,zDate) VALUES ('{$tablename}','{$xdate}')"); } $currentTABLE = date("Ymd") . "_hour"; $LIST_TABLES_dansguardian_events = $q->LIST_TABLES_dansguardian_events(); while (list($tablename, $ligne) = each($LIST_TABLES_dansguardian_events)) { $xtime = $q->TIME_FROM_DANSGUARDIAN_EVENTS_TABLE($tablename); $hour_table = date("Ymd", $xtime) . "_hour"; if ($currentTABLE == $hour_table) { continue; } if (!$q->TABLE_EXISTS($hour_table)) { events(" ####### WARNING - NO TABLE {$hour_table} #############"); } } }
function UPDATE_CATEGORY_DAYS($sitename, $category) { $q = new mysql_squid_builder(); $TABLES_DAYS = $q->LIST_TABLES_HOURS(); while (list($tablename, $infos) = each($TABLES_DAYS)) { if ($GLOBALS["VERBOSE"]) { echo "{$tablename} -> {$category} FOR `{$sitename}`\n"; } $q->QUERY_SQL("UPDATE {$tablename} SET `category`='{$category}' WHERE `sitename`='{$sitename}'"); } }
function repair_table_days_hours() { $q = new mysql_squid_builder(); $array = $q->LIST_TABLES_HOURS(); while (list($tablename, $none) = each($array)) { $time = $q->TIME_FROM_HOUR_TABLE($tablename); $date = date("Y-m-d", $time); $tablename = "dansguardian_events_" . date("Ymd", $time); echo "{$tablename} -> {$date}\n"; $q->QUERY_SQL("INSERT IGNORE INTO tables_day (tablename,zDate) VALUES ('{$tablename}','{$date}')"); } }
function ScanDays() { $q = new mysql_squid_builder(true); $ARRAY_DAYS = array(); $tables = $q->LIST_TABLES_dansguardian_events(); while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_DANSGUARDIAN_EVENTS_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; } $tables = $q->LIST_TABLES_HOURS(); while (list($tablename, $line) = each($tables)) { $dayTime = $q->TIME_FROM_HOUR_TABLE($tablename); $day = date("Y-m-d", $dayTime); $ARRAY_DAYS[$day] = $dayTime; } $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 = $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; } $prefix = "INSERT IGNORE INTO tables_day (tablename,zDate) VALUES "; while (list($day, $dayTime) = each($ARRAY_DAYS)) { $tablename = "dansguardian_events_" . date("Ymd", $dayTime); if ($GLOBALS["VERBOSE"]) { echo "{$day}: [{$tablename}]\n"; } $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"; } ufdbguard_admin_events("Fatal {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "backup"); return false; } } return true; }
function repair() { $q = new mysql_squid_builder(); $C = 0; $currentDay = date("Ymd"); $LIST_TABLES_HOURS = $q->LIST_TABLES_HOURS(); while (list($tablename, $value) = each($LIST_TABLES_HOURS)) { $xtime = $q->TIME_FROM_HOUR_TABLE($tablename); if (date("Ymd", $xtime) == $currentDay) { continue; } $DayTable = date("Ymd", $xtime) . "_hour"; if (!$q->TABLE_EXISTS($DayTable)) { continue; } $tablenameS = "dansguardian_events_" . date("Ymd", $xtime); $sql = "SELECT COUNT(`sitename`) as tcount FROM {$DayTable} WHERE LENGTH(`category`)=0"; $ligne2 = mysql_fetch_array($q->QUERY_SQL($sql)); $max = $ligne2["tcount"]; if ($GLOBALS["VERBOSE"]) { echo "{$DayTable}/{$tablenameS} = {$max} no categorized\n"; } $sql = "UPDATE tables_day SET `not_categorized`={$max} WHERE tablename='{$tablenameS}'"; $q->QUERY_SQL($sql); } }
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 clean_squid_stats_dbs() { $sock = new sockets(); $DisableArticaProxyStatistics = $sock->GET_INFO("DisableArticaProxyStatistics"); $CleanArticaSquidDatabases = $sock->GET_INFO("CleanArticaSquidDatabases"); if (!is_numeric($DisableArticaProxyStatistics)) { $DisableArticaProxyStatistics = 0; } if (!is_numeric($CleanArticaSquidDatabases)) { $CleanArticaSquidDatabases = 0; } if (!$GLOBALS["FORCE"]) { if ($CleanArticaSquidDatabases == 0) { echo "Option is not activated...\n"; return; } } $q = new mysql_squid_builder(); $tables = $q->LIST_TABLES_DAYS(); $rows = 0; $count_tables = 0; while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_DAYS_BLOCKED(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_HOURS(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_MEMBERS(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_MONTH(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_WEEKS(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_WORKSHOURS(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_BLOCKED_WEEK(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_BLOCKED_DAY(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_VISITED(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $tables = $q->LIST_TABLES_dansguardian_events(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $q = new mysql_catz(); $tables = $q->LIST_TABLES_CATEGORIES(); while (list($num, $table) = each($tables)) { $rows = $rows + $q->COUNT_ROWS($table); if ($GLOBALS["VERBOSE"]) { echo " Delete table {$table} {$rows} rows \n"; } $count_tables++; $q->DELETE_TABLE($table); } $q = new mysql(); if ($q->DATABASE_EXISTS("catz")) { $q->DELETE_DATABASE("catz"); } if ($count_tables > 0) { mysql_admin_mysql(1, "Restarting MySQL service...", null, __FILE__, __LINE__); shell_exec("/etc/init.d/mysql restart"); } $sock->TOP_NOTIFY("{$count_tables} statistics tables as been deleted with {$rows} rows", "info"); //print_r($tables); }
function dump_days() { $q = new mysql_squid_builder(); echo "Today: " . date("Y-m-d") . "\n"; $LIST_TABLES_QUERIES = $q->LIST_TABLES_QUERIES(); while (list($index, $value) = each($LIST_TABLES_QUERIES)) { echo "{$value} : `{$index}` \n"; } $LIST_TABLES_HOURS = $q->LIST_TABLES_HOURS(); while (list($index, $value) = each($LIST_TABLES_HOURS)) { echo "{$value} : `{$index}` \n"; } $sql = "SELECT *,DATE_FORMAT( zDate, '%Y%m%d' ) AS tablesource,DAYOFWEEK(zDate) as DayNumber,WEEK( zDate ) AS tweek, \n\tYEAR( zDate ) AS tyear FROM tables_day ORDER BY zDate"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { writelogs_squid("Fatal: {$q->mysql_error} on `tables_day`", __FUNCTION__, __FILE__, __LINE__, "stats"); return false; } while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $tablename = $ligne["tablename"]; $countRows = $q->COUNT_ROWS($tablename); $tablesource = "{$ligne["tablesource"]}_hour"; $DayNumber = $ligne["DayNumber"]; $weekNum = $ligne["tweek"]; $week_table = "{$ligne["tyear"]}{$ligne["tweek"]}_week"; echo "{$tablename} ( {$countRows} rows) ------------------------------------------------------------\n"; $tablesource_exists = "yes"; $week_table_exists = "yes"; if (!$q->TABLE_EXISTS($tablesource)) { $tablesource_exists = "no"; } if (!$q->TABLE_EXISTS($week_table)) { $week_table_exists = "no"; } $week_table_rows = $q->COUNT_ROWS($week_table); echo "Table source {$tablesource} Exists:{$tablesource_exists}\n"; echo "Table Week {$week_table} Exists:{$week_table_exists} ( {$week_table_rows} rows )\n"; while (list($index, $value) = each($ligne)) { if (is_numeric($index)) { continue; } //echo "$index: `$value`\n"; } } }
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"; } } }
function REBUILD_FULL_MEMBERS() { $now = date("Ymd"); $currentable = "{$now}_hour"; $q = new mysql_squid_builder(); $TABLES = $q->LIST_TABLES_HOURS(); $username = trim($username); $c = 0; $MAX = count($TABLES); while (list($tablesource, $b) = each($TABLES)) { if ($tablesource == $currentable) { continue; } if (!preg_match("#^[0-9]+_hour#", $tablesource)) { echo "Skipping {$tablesource}\n"; continue; } $time = $q->TIME_FROM_HOUR_TABLE($tablesource); $DAY = date("d", $time); $YEAR = date("Y", $time); $MONTH = date("m", $time); $c++; $month_table = date("Ym", $time) . "_members"; echo "{$tablesource} -> Day: {$DAY}, Year: {$YEAR}, Month, {$MONTH}, table:{$month_table}\n"; $MONTH_TABLES[$month_table]["YEAR"] = $YEAR; $MONTH_TABLES[$month_table]["MONTH"] = $MONTH; build_progress("Parsing {$tablesource} {$c}/{$MAX}", 20); $NEWTABLES[$tablesource] = $month_table; } while (list($tablesource, $b) = each($MONTH_TABLES)) { echo "Cleaning/Creating {$tablesource}\n"; if ($q->TABLE_EXISTS($month_table)) { $q->QUERY_SQL("TRUNCATE TABLE {$month_table}"); } else { echo "Creating {$month_table}\n"; $q->CreateMembersMonthTable($month_table); } } $c = 0; while (list($tablesource, $month_table) = each($NEWTABLES)) { if (!preg_match("#^[0-9]+_hour#", $tablesource)) { continue; } $c++; build_progress("Parsing {$tablesource} {$c}/{$MAX}", 20); $results = $q->QUERY_SQL("SELECT SUM(hits) as hits,SUM(size) as size, MAC,client,hostname,uid FROM {$tablesource} GROUP BY MAC,client,hostname,uid"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("Parsing {$tablesource} {$c}/{$MAX} failed", 110); return; } $sum = mysql_num_rows($results); if ($sum == 0) { echo "{$tablesource} Nothing!\n"; continue; } $time = $q->TIME_FROM_HOUR_TABLE($tablesource); $DAY = date("d", $time); echo "Month table:{$month_table} Day {$DAY} {$sum} rows\n"; $prefix = "INSERT IGNORE INTO `{$month_table}` (zMD5,client,`day`,size,hits,uid,MAC,hostname) VALUES "; $row = array(); $d = 0; $IP = new IP(); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $zMD5 = md5(serialize($ligne)); $client = mysql_escape_string2($ligne["client"]); $uid = mysql_escape_string2($ligne["uid"]); $MAC = mysql_escape_string2($ligne["MAC"]); $hostname = mysql_escape_string2($ligne["hostname"]); $size = $ligne["size"]; $hits = $ligne["hits"]; if ($IP->isValid($uid)) { $uid = null; } $row[] = "('{$zMD5}','{$client}','{$DAY}','{$size}','{$hits}','{$uid}','{$MAC}','{$hostname}')"; $d++; if (count($row) > 500) { $q->QUERY_SQL($prefix . @implode(",", $row)); build_progress("Insterting " . count($row) . " elements", 20); echo "Added {$d} rows\n"; $row = array(); } } if (count($row) > 0) { $q->QUERY_SQL($prefix . @implode(",", $row)); build_progress("Insterting " . count($row) . " elements", 20); echo "Added {$d} rows\n"; $row = array(); } } MEMBERS_YEAR(true); CONDENSED(true); build_progress("{success}", 20); }
function members_repair() { $unix = new unix(); $q = new mysql_squid_builder(); $pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid"; $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); } $C = 0; $currentDay = date("Ymd"); $LIST_TABLES_HOURS = $q->LIST_TABLES_HOURS(); while (list($tablename, $value) = each($LIST_TABLES_HOURS)) { $xtime = $q->TIME_FROM_HOUR_TABLE($tablename); if (date("Ymd", $xtime) == $currentDay) { continue; } $member_table = date("Ymd", $xtime) . "_members"; $users_table = date("Ymd", $xtime) . "_users"; $source_table = "dansguardian_events_" . date("Ymd", $xtime); if (!$q->TABLE_EXISTS($member_table)) { if ($GLOBALS["VERBOSE"]) { echo "{$source_table} -> {$member_table} -> BUILD\n"; } stats_admin_events(1, "Repair: Members {$source_table} -> {$member_table}", null, __FILE__, __LINE__); if (members_hours_perfom($source_table, $member_table)) { users_day_perfom($member_table, $users_table); $C++; } continue; } if ($q->COUNT_ROWS($tablename) > 0) { if ($q->COUNT_ROWS($member_table) == 0) { if (members_hours_perfom($source_table, $member_table)) { stats_admin_events(2, "Repair: Members Success from \"{$source_table}\" to \"{$member_table}\"", null, __FILE__, __LINE__); users_day_perfom($member_table, $users_table); $C++; } continue; } } if ($GLOBALS["VERBOSE"]) { echo "{$source_table} -> {$member_table} OK\n"; } } $php = $unix->LOCATE_PHP5_BIN(); if ($C > 0) { shell_exec("{$php} /usr/share/artica-postfix/exec.squid.stats.totals.php --repair-members --byschedule"); } }