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");
}
}
