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 members_month_delete()
{
$sql = "SELECT DATE_FORMAT(zDate,'%Y%m') AS suffix,DATE_FORMAT(zDate,'%Y%m%d') AS suffix2,DAY(zDate) as tday,YEAR(zDate) AS tyear,month(zDate) AS tmonth FROM tables_day";
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
events_tail("{$GLOBALS["Q"]->mysql_error}\n------\n{$sql}\n----");
return;
}
$num_rows = mysql_num_rows($results);
if ($num_rows == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "No datas " . __FUNCTION__ . " " . __LINE__ . "\n";
}
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if ($GLOBALS["Q"]->TABLE_EXISTS("{$ligne["suffix"]}_members")) {
echo "Delete table {$ligne["suffix"]}_members\n";
$GLOBALS["Q"]->QUERY_SQL("DROP TABLE `{$ligne["suffix"]}_members`");
}
}
$GLOBALS["Q"]->QUERY_SQL("UPDATE tables_day SET month_members=0");
}
function include_tpl_file($path, $category)
{
$sock = new sockets();
$uuid = base64_decode($sock->getFrameWork("cmd.php?system-unique-id=yes"));
if ($uuid == null) {
echo "UUID=NULL; Aborting";
return;
}
if ($category == null) {
echo "CATEGORY=NULL; Aborting";
return;
}
if (!is_file($path)) {
echo "{$path} no such file\n";
return;
}
$q = new mysql_squid_builder();
$q->CreateCategoryTable($category);
$TableDest = "category_" . $q->category_transform_name($category);
$array = array();
$f = @explode("\n", @file_get_contents($path));
$count_websites = count($f);
$i = 0;
$d = 0;
$group = 0;
$prefix = "INSERT IGNORE INTO {$TableDest} (zmd5,zDate,category,pattern,uuid) VALUES";
while (list($index, $website) = each($f)) {
$i++;
$d++;
if ($d > 1000) {
$group = $group + $d;
events_tail("include_tpl_file({$category}):: importing {$group} sites...");
$d = 0;
}
if ($website == null) {
return;
}
$www = trim(strtolower($website));
if (preg_match("#www\\.(.+?)\$#i", $www, $re)) {
$www = $re[1];
}
$md5 = md5($www . $category);
if ($array[$md5]) {
echo "{$www} already exists\n";
continue;
}
$enabled = 1;
$sql_add[] = "('{$md5}',NOW(),'{$category}','{$www}','{$uuid}')";
$array[$md5] = true;
if ($GLOBALS["SIMULATE"]) {
echo "{$i}/{$count_websites}: {$sql_add}\n";
continue;
}
if (count($sql_add) > 500) {
$sql = $prefix . @implode(",", $sql_add);
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo "{$i}/{$count_websites} Failed: {$www}\n";
} else {
echo "{$i}/{$count_websites} Success: {$www}\n";
}
$sql_add = array();
}
}
if (count($sql_add) > 0) {
$sql = $prefix . @implode(",", $sql_add);
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo "{$i}/{$count_websites} Failed: {$www}\n";
} else {
echo "{$i}/{$count_websites} Success: {$www}\n";
}
$sql_add = array();
}
echo " -------------------------------------------------\n";
echo count($array) . " websites done\n";
echo " -------------------------------------------------\n";
}
function migrate_single_db_day($day, $newtable, $oldtable = null)
{
if ($oldtable == null) {
$oldtable = "dansguardian_events";
}
events_tail("Migrate {$day} day data to dansguardian_events_{$newtable}");
$table = "dansguardian_events_{$newtable}";
$qA = new mysql_squid_builder();
$qA->CheckTables($table);
$sql = "SELECT * FROM {$oldtable} WHERE DATE_FORMAT(zDate,'%Y-%m-%d')='{$day}'";
$q = new mysql();
$results = $q->QUERY_SQL($sql, "artica_events");
$countnom = mysql_num_rows($results);
events_tail("Migrate {$day} datas to dansguardian_events_{$newtable} ({$countnom})");
if (!$q->ok) {
events_tail("Mysql error {$q->mysql_error}");
return;
}
$prefixsql = "INSERT IGNORE INTO {$table} (`sitename`,`uri`,`TYPE`,`REASON`,`CLIENT`,`zDate`,`zMD5`,`remote_ip`,`country`,`QuerySize`,`uid`,`cached`) VALUES ";
$c = 0;
$f = array();
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$c++;
if (!isset($ligne["cached"])) {
$ligne["cached"] = 0;
}
$ligne["country"] = addslashes($ligne["country"]);
$ligne["sitename"] = addslashes($ligne["sitename"]);
$ligne["uri"] = addslashes($ligne["uri"]);
$ligne["TYPE"] = addslashes($ligne["TYPE"]);
$ligne["REASON"] = addslashes($ligne["REASON"]);
$ligne["CLIENT"] = addslashes($ligne["CLIENT"]);
$f[] = "('{$ligne["sitename"]}','{$ligne["uri"]}','{$ligne["TYPE"]}','{$ligne["REASON"]}','{$ligne["CLIENT"]}','{$ligne["zDate"]}',\n\t\t'{$ligne["zMD5"]}','{$ligne["remote_ip"]}','{$ligne["country"]}','{$ligne["QuerySize"]}','{$ligne["uid"]}','{$ligne["cached"]}')";
if (count($f) > 500) {
events_tail("Injecting {$c}/{$countnom} rows...");
$qA->QUERY_SQL("{$prefixsql}" . @implode(",", $f), "squidlogs");
if (!$qA->ok) {
echo $qA->mysql_error . "\n";
return;
}
$f = array();
}
}
if (count($f) > 0) {
events_tail("Injecting " . count($f) . "/{$countnom} rows...");
$qA->QUERY_SQL("{$prefixsql}" . @implode(",", $f), "squidlogs");
}
events_tail("Removing {$day} datas in old table...");
$sql = "DELETE FROM {$oldtable} WHERE DATE_FORMAT(zDate,'%Y-%m-%d')='{$day}'";
$q->QUERY_SQL($sql, "artica_events");
events_tail("{$day} = {$c} elements migrated to new dansguardian_events_{$newtable}");
return true;
}
function events($text, $line = 0)
{
if (function_exists("debug_backtrace")) {
$trace = debug_backtrace();
if (isset($trace[1])) {
$sourcefile = basename($trace[1]["file"]);
$sourcefunction = $trace[1]["function"];
$sourceline = $trace[1]["line"];
}
if ($line > 0) {
$sourceline = $line;
}
$text = "{$text} ({$sourcefunction}::{$sourceline})";
}
events_tail($text);
}
function include_tpl_file($path,$category){
$sock=new sockets();
$uuid=base64_decode($sock->getFrameWork("cmd.php?system-unique-id=yes"));
if($uuid==null){echo "UUID=NULL; Aborting";return;}
if($category==null){echo "CATEGORY=NULL; Aborting";return;}
if(!is_file($path)){
echo "$path no such file\n";
return;
}
$q=new mysql();
$array=array();
$f=@explode("\n",@file_get_contents($path));
$count_websites=count($f);
$i=0;$d=0;$group=0;
while (list ($index, $website) = each ($f) ){
$i++;
$d++;
if($d>1000){
$group=$group+$d;
events_tail("include_tpl_file($category):: importing $group sites...");
$d=0;
}
if($website==null){return;}
$www=trim(strtolower($website));
if(preg_match("#www\.(.+?)$#i",$www,$re)){$www=$re[1];}
$md5=md5($www.$category);
if($array[$md5]){echo "$www already exists\n";continue;}
$enabled=1;
$sql_add="INSERT INTO dansguardian_community_categories (zmd5,zDate,category,pattern,uuid) VALUES('$md5',NOW(),'$category','$www','$uuid')";
$array[$md5]=true;
if($GLOBALS["SIMULATE"]){echo "$i/$count_websites: $sql_add\n";continue;}
$q->QUERY_SQL($sql_add,"artica_backup");
if(!$q->ok){echo "$i/$count_websites Failed: $www\n";}else{echo "$i/$count_websites Success: $www\n";}
}
echo " -------------------------------------------------\n";
echo count($array)." websites done\n";
echo " -------------------------------------------------\n";
}
function events($text)
{
$pid = @getmypid();
$date = @date("H:i:s");
events_tail($text);
$logFile = "{$GLOBALS["ARTICALOGDIR"]}/ufdbguard-tail.debug";
$size = @filesize($logFile);
if ($size > 1000000) {
@unlink($logFile);
}
$f = @fopen($logFile, 'a');
@fwrite($f, "{$date} [{$pid}]:: " . basename(__FILE__) . " {$text}\n");
@fclose($f);
}
function UserSizeD_REPAIR($day)
{
$time = strtotime("{$day} 00:00:00");
$sourcetable = date("Ymd", $time) . "_hour";
$q = new mysql_squid_builder();
if (!$q->TABLE_EXISTS($sourcetable)) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$sourcetable} no such table\n";
}
return;
}
$tablename = "UserSizeD_" . date("Ymd", $time);
if (!$q->CreateUserSizeRTT_day($tablename)) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:{$tablename}</strong> Query failed {$q->mysql_error}\n";
}
ufdbguard_admin_events("{$tablename}: Query failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$prefix = "INSERT IGNORE INTO `{$tablename}` (`zMD5`,`uid`,`zdate`,\n\t`ipaddr`,`hostname`,`account`,`MAC`,`UserAgent`,`size`,`hits`,`hour`) VALUES ";
$sql = "SELECT client,hostname,account,hour,MAC,SUM(size) as size,SUM(hits) as hits,uid FROM {$sourcetable}\n\tGROUP BY client,hostname,account,hour,MAC,uid";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$q->mysql_error}\n";
}
return;
}
$total = mysql_num_rows($results);
if ($total == 0) {
events_tail("UserSizeD_REPAIR({$day}) Fatal!:No row for {$sourcetable}\n");
return;
}
$de = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$de++;
$zMD5 = md5(serialize($ligne));
$uid = addslashes($ligne["uid"]);
$hostname = addslashes($ligne["hostname"]);
$f[] = "('{$zMD5}','{$uid}','{$day}','{$ligne["client"]}','{$hostname}','{$ligne["account"]}','{$ligne["MAC"]}','','{$ligne["size"]}','{$ligne["hits"]}','{$ligne["hour"]}')";
if (count($f) > 500) {
$dep = round($de / $total * 100, 2);
events_tail("UserSizeD_REPAIR({$day}) {$de}/{$total} ({$dep})");
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
events_tail("UserSizeD_REPAIR({$day}) Fatal! {$q->mysql_error}");
return;
}
$f = array();
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$q->mysql_error}\n";
}
return;
}
$f = array();
}
if ($GLOBALS["VERBOSE"]) {
echo "<strong>SUCCESS: {$tablename}</strong>\n";
}
}
function WeekDaysNums()
{
if (isset($GLOBALS["ALREADYWeekDaysNums"])) {
return;
}
$GLOBALS["ALREADYWeekDaysNums"] = true;
$q = new mysql_squid_builder();
$sql = "SELECT tablename,zDate,DAYOFWEEK(zDate) as DayNumber,WEEK( zDate ) as WeekNumber FROM tables_day WHERE WeekNum=0";
$results = $q->QUERY_SQL($sql);
events_tail("WeekDaysNums(): WeekNum=0 -> " . mysql_num_rows($results), __LINE__);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zDate = $ligne["zDate"];
$WeekNumber = intval($ligne["WeekNumber"]);
if ($WeekNumber == 0) {
$sql = "SELECT WEEK('{$zDate} 00:00:00') as WeekNumber";
$ligne2 = mysql_fetch_array($q->QUERY_SQL($sql));
$WeekNumber = $ligne2["WeekNumber"];
events_tail("WeekDaysNums(): {$zDate}: ERROR, REPAIR {$zDate} 00:00:00 -> {$WeekNumber}", __LINE__);
}
events_tail("WeekDaysNums(): {$zDate}: {$ligne["tablename"]} -> WeekDay={$ligne["DayNumber"]}, WeekNum={$WeekNumber}", __LINE__);
$q->QUERY_SQL("UPDATE tables_day SET WeekDay={$ligne["DayNumber"]},WeekNum={$WeekNumber}\n\t\tWHERE tablename='{$ligne["tablename"]}'");
if (!$q->ok) {
events_tail("WeekDaysNums(): {$q->mysql_error}", __LINE__);
}
}
}
function perform($tabledata, $nexttable)
{
$q = new mysql_squid_builder();
if (!$q->CreateUsersDayTable($nexttable)) {
return false;
}
$q = new mysql_squid_builder();
if ($q->TABLE_EXISTS($nexttable)) {
$q->QUERY_SQL("DROP TABLE {$nexttable}");
}
$f = array();
if (!$q->CreateUsersDayTable($nexttable)) {
return false;
}
$sql = "SELECT SUM(size) as size, SUM(hits) as hits,client,hostname,uid,MAC FROM {$tabledata} GROUP BY client,hostname,uid,MAC";
$prefix = "INSERT IGNORE INTO {$nexttable} (zMD5,client,hostname,MAC,size,hits,uid) VALUES";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
stats_admin_events(0, "Processing failed with {$tabledata}", $q->mysql_error, __FILE__, __LINE__);
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$md5 = md5(serialize($ligne));
$client = mysql_escape_string2(trim(strtolower($ligne["client"])));
$uid = mysql_escape_string2(trim(strtolower($ligne["uid"])));
$hostname = mysql_escape_string2(trim(strtolower($ligne["hostname"])));
$MAC = mysql_escape_string2(trim(strtolower($ligne["MAC"])));
$f[] = "('{$md5}','{$client}','{$hostname}','{$MAC}','{$ligne["size"]}','{$ligne["hits"]}','{$uid}')";
if (count($f) > 500) {
$q->QUERY_SQL("{$prefix}" . @implode(",", $f));
events_tail("Processing " . count($f) . " rows");
if (!$q->ok) {
events_tail("Failed to process query to {$nexttable} {$q->mysql_error}");
return;
}
$f = array();
}
}
if (count($f) > 0) {
$q->QUERY_SQL("{$prefix}" . @implode(",", $f));
events_tail("Processing " . count($f) . " rows");
if (!$q->ok) {
events_tail("Failed to process query to {$nexttable} {$q->mysql_error}");
return;
}
$f = array();
}
return true;
}