function repair_tables()
{
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
$pid = @file_get_contents($pidfile);
$unix = new unix();
if ($unix->process_exists($pid, basename(__FILE__))) {
events_tail("Already executed pid {$pid}");
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$files = $unix->DirFiles("/usr/share/artica-postfix/ressources/logs/categorize-tables");
$php5 = $unix->LOCATE_PHP5_BIN();
while (list($none, $tablename) = each($files)) {
$filePath = "/usr/share/artica-postfix/ressources/logs/categorize-tables/{$tablename}";
if (!is_file($filePath)) {
@unlink($filePath);
continue;
}
$ARRAY = unserialize(@file_get_contents($filePath));
if (!is_array($ARRAY)) {
@unlink($filePath);
continue;
}
$PID = $ARRAY["PID"];
$CUR = $ARRAY["CURRENT"];
$MAX = $ARRAY["MAX"];
if ($CUR == $MAX) {
@unlink($filePath);
continue;
}
if ($unix->process_exists($PID)) {
continue;
}
categorize_tables_events("Ask to schedule table Current:{$CUR}/{$MAX}", null, $tablename, 1);
$unix->THREAD_COMMAND_SET("{$php5} " . __FILE__ . " --table {$tablename}");
}
}
function process_all_tables()
{
if ($GLOBALS["VERBOSE"]) {
echo "Loading...\n";
}
$unix = new unix();
$php = $unix->LOCATE_PHP5_BIN();
$nohup = $unix->find_program("nohup");
if ($GLOBALS["VERBOSE"]) {
"echo Loading done...\n";
}
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".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;
}
$timeexec = $unix->file_time_min($timefile);
if ($timeexec < 720) {
if ($GLOBALS["VERBOSE"]) {
echo "{$timeexec} <>720...\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
@file_put_contents($timefile, time());
$q = new mysql_squid_builder();
$tables = $q->LIST_TABLES_HOURS();
$current_table = date("Ymd") . "_hour";
$BIGARRAY = array();
$d = 0;
while (list($tablename, $ligne) = each($tables)) {
if ($current_table == $tablename) {
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename} SKIP...\n";
}
continue;
}
$d++;
$sql = "SELECT sitename,familysite,category,SUM(size) as size,SUM(hits) as hits,country \n\t\tFROM {$tablename} GROUP BY sitename,familysite,category HAVING LENGTH(category)=0";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
categorize_tables_events("MySQL error (after {$d} tables)", "{$q->mysql_error}", $tablename);
return;
}
$count = mysql_num_rows($results);
if ($count == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename} no row...\n";
}
continue;
} else {
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename} {$count} rows...\n";
}
}
$TIME_FROM_HOUR_TABLE = $q->TIME_FROM_HOUR_TABLE($tablename);
while ($ligne = mysql_fetch_assoc($results)) {
$sitename = trim($ligne["sitename"]);
$familysite = trim($ligne["familysite"]);
process_all_tables_percentage($sitename);
if ($sitename == null) {
if ($GLOBALS["VERBOSE"]) {
echo "Null value for {$sitename},{$familysite} aborting\n";
}
$q->QUERY_SQL("DELETE FROM {$tablename} WHERE `sitename`='{$ligne["sitename"]}'");
continue;
}
if ($sitename == '.') {
if ($GLOBALS["VERBOSE"]) {
echo "'.' value for {$sitename},{$familysite} aborting\n";
}
$q->QUERY_SQL("DELETE FROM {$tablename} WHERE `sitename`='{$ligne["sitename"]}'");
continue;
}
if (strpos($sitename, ',') > 0) {
$sitename = str_replace(",", "", $sitename);
$q->QUERY_SQL("UPDATE {$tablename} SET `sitename`='{$sitename}' WHERE `sitename`='{$ligne["sitename"]}'");
}
if (is_numeric($sitename)) {
if ($GLOBALS["VERBOSE"]) {
echo "Numeric value for {$sitename},{$familysite} aborting\n";
}
$q->QUERY_SQL("DELETE FROM {$tablename} WHERE `sitename`='{$ligne["sitename"]}'");
continue;
}
if (strpos($sitename, ".") == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "Seems to be a local domain for {$sitename},{$familysite} aborting\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET `category`='internal' WHERE `sitename`='{$ligne["sitename"]}'");
continue;
}
if (!isset($BIGARRAY[$sitename])) {
$BIGARRAY[$sitename]["familysite"] = $familysite;
$BIGARRAY[$sitename]["country"] = $ligne["country"];
$BIGARRAY[$sitename]["size"] = $ligne["size"];
$BIGARRAY[$sitename]["hits"] = $ligne["hits"];
} else {
$BIGARRAY[$sitename]["hits"] = $BIGARRAY[$ligne["sitename"]]["hits"] + $ligne["hits"];
$BIGARRAY[$sitename]["size"] = $BIGARRAY[$ligne["sitename"]]["size"] + $ligne["size"];
}
$BIGARRAY[$sitename]["TIME"][$TIME_FROM_HOUR_TABLE] = true;
}
}
$q->QUERY_SQL("TRUNCATE TABLE `notcategorized`");
$sql = "CREATE TABLE IF NOT EXISTS `notcategorized` (\n\t\t`sitename` VARCHAR(255) NOT NULL,\n\t\t`familysite` VARCHAR(255) NOT NULL,\n\t\t`domain` VARCHAR(5) NOT NULL,\n\t\t`country` VARCHAR(60) NOT NULL,\n\t\t`sent` smallint(1) NOT NULL DEFAULT 0,\n\t\t`hits` bigint(255) unsigned NOT NULL,\n\t\t`size` bigint(255) unsigned NOT NULL,\n\t\t`seen` TEXT NOT NULL,\n\t\tPRIMARY KEY (`sitename`),\n\t\tKEY `size` (`size`),\n\t\t KEY `hits` (`hits`),\n\t\t KEY `familysite` (`familysite`),\n\t\t KEY `domain` (`domain`),\n\t\t KEY `country` (`country`)\n\t\t) ENGINE=MyISAM;";
if (!$q->FIELD_EXISTS("notcategorized", "sent")) {
$q->QUERY_SQL("ALTER TABLE `notcategorized` ADD `sent` smallint(1) NOT NULL DEFAULT 0, ADD INDEX (`sent`)");
}
$q->QUERY_SQL($sql);
if (!$q->ok) {
categorize_tables_events("MySQL error (after {$d} items)", "{$q->mysql_error}", "notcategorized");
return;
}
if ($GLOBALS["VERBOSE"]) {
echo "FINAL " . count($BIGARRAY) . " items...\n";
}
if (count($BIGARRAY) > 0) {
$d = 0;
$prefix = "INSERT IGNORE INTO notcategorized (`sitename`,`familysite`,`country`,`domain`,`size`,`hits`,`seen`) VALUES ";
while (list($sitename, $infos) = each($BIGARRAY)) {
$d++;
$times = array();
$sitename = mysql_escape_string2($sitename);
$family = mysql_escape_string2($infos["familysite"]);
$country = mysql_escape_string2($infos["country"]);
$tt = explode(".", $family);
unset($tt[0]);
$domain = mysql_escape_string2(@implode(".", $tt));
while (list($a, $b) = each($infos["TIME"])) {
$times[] = $a;
}
$text_time = mysql_escape_string2(serialize($times));
if ($GLOBALS["VERBOSE"]) {
echo "('{$sitename}','{$family}','{$country}','{$domain}','{$infos["size"]}','{$infos["hits"]}','{$text_time}')\n";
}
$f[] = "('{$sitename}','{$family}','{$country}','{$domain}','{$infos["size"]}','{$infos["hits"]}','{$text_time}')";
if (count($f) > 500) {
process_all_tables_percentage("notcategorized {$d} rows...");
if ($GLOBALS["VERBOSE"]) {
echo "notcategorized 500 rows...\n";
}
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
echo $q->mysql_error . "\n";
categorize_tables_events("MySQL error (after {$d} items)", "{$q->mysql_error}", "notcategorized");
return;
}
$f = array();
}
}
if (count($f) > 0) {
if ($GLOBALS["VERBOSE"]) {
echo "notcategorized " . count($f) . " rows...\n";
}
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
categorize_tables_events("MySQL error (after {$d} items)", "{$q->mysql_error}", "notcategorized");
return;
}
}
}
}
