/**
* Generate SQL from Rule
* @param string $rule Rule to generate SQL for
* @return string|boolean
*/
function GenSQL($rule)
{
$rule = RunMacros($rule);
if (empty($rule)) {
//Cannot resolve Macros due to recursion. Rule is invalid.
return false;
}
//Pretty-print rule to dissect easier
$pretty = array('*' => '*', '(' => ' ( ', ')' => ' ) ', '/' => '/', '&&' => ' && ', '||' => ' || ', 'DATE_SUB ( NOW ( )' => 'DATE_SUB( NOW()');
$rule = str_replace(array_keys($pretty), $pretty, $rule);
$tmp = explode(" ", $rule);
$tables = array();
foreach ($tmp as $opt) {
if (strstr($opt, '%') && strstr($opt, '.')) {
$tmpp = explode(".", $opt, 2);
$tmpp[0] = str_replace("%", "", $tmpp[0]);
$tables[] = mres(str_replace("(", "", $tmpp[0]));
$rule = str_replace($opt, $tmpp[0] . '.' . $tmpp[1], $rule);
}
}
$tables = array_keys(array_flip($tables));
if (dbFetchCell('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = ? && COLUMN_NAME = ?', array($tables[0], 'device_id')) != 1) {
//Our first table has no valid glue, append the 'devices' table to it!
array_unshift($tables, 'devices');
}
$x = sizeof($tables) - 1;
$i = 0;
$join = "";
while ($i < $x) {
if (isset($tables[$i + 1])) {
$gtmp = ResolveGlues(array($tables[$i + 1]), 'device_id');
if ($gtmp === false) {
//Cannot resolve glue-chain. Rule is invalid.
return false;
}
$last = "";
$qry = "";
foreach ($gtmp as $glue) {
if (empty($last)) {
list($tmp, $last) = explode('.', $glue);
$qry .= $glue . ' = ';
} else {
list($tmp, $new) = explode('.', $glue);
$qry .= $tmp . '.' . $last . ' && ' . $tmp . '.' . $new . ' = ';
$last = $new;
}
if (!in_array($tmp, $tables)) {
$tables[] = $tmp;
}
}
$join .= "( " . $qry . $tables[0] . ".device_id ) && ";
}
$i++;
}
$sql = "SELECT * FROM " . implode(",", $tables) . " WHERE (" . $join . "" . str_replace("(", "", $tables[0]) . ".device_id = ?) && (" . str_replace(array("%", "@", "!~", "~"), array("", ".*", "NOT REGEXP", "REGEXP"), $rule) . ")";
return $sql;
}
/**
* Create a glue-chain
* @param array $tables Initial Tables to construct glue-chain
* @param string $target Glue to find (usual device_id)
* @param int $x Recursion Anchor
* @param array $hist History of processed tables
* @param array $last Glues on the fringe
* @return string|boolean
*/
function ResolveGlues($tables, $target, $x = 0, $hist = array(), $last = array())
{
if (sizeof($tables) == 1 && $x != 0) {
if (dbFetchCell('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = ? && COLUMN_NAME = ?', array($tables[0], $target)) == 1) {
return array_merge($last, array($tables[0] . '.' . $target));
} else {
return false;
}
} else {
$x++;
if ($x > 30) {
//Too much recursion. Abort.
return false;
}
foreach ($tables as $table) {
$glues = dbFetchRows('SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = ? && COLUMN_NAME LIKE "%\\_id"', array($table));
if (sizeof($glues) == 1 && $glues[0]['COLUMN_NAME'] != $target) {
//Search for new candidates to expand
$ntables = array();
list($tmp) = explode('_', $glues[0]['COLUMN_NAME'], 2);
$ntables[] = $tmp;
$ntables[] = $tmp . 's';
$tmp = dbFetchRows('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE "' . substr($table, 0, -1) . '_%" && TABLE_NAME != "' . $table . '"');
foreach ($tmp as $expand) {
$ntables[] = $expand['TABLE_NAME'];
}
$tmp = ResolveGlues($ntables, $target, $x++, array_merge($tables, $ntables), array_merge($last, array($table . '.' . $glues[0]['COLUMN_NAME'])));
if (is_array($tmp)) {
return $tmp;
}
} else {
foreach ($glues as $glue) {
if ($glue['COLUMN_NAME'] == $target) {
return array_merge($last, array($table . '.' . $target));
} else {
list($tmp) = explode('_', $glue['COLUMN_NAME']);
$tmp .= 's';
if (!in_array($tmp, $tables) && !in_array($tmp, $hist)) {
//Expand table
$tmp = ResolveGlues(array($tmp), $target, $x++, array_merge($tables, array($tmp)), array_merge($last, array($table . '.' . $glue['COLUMN_NAME'])));
if (is_array($tmp)) {
return $tmp;
}
}
}
}
}
}
}
//You should never get here.
return false;
}
/**
* Generate SQL from Group-Pattern
* @param string $pattern Pattern to generate SQL for
* @param string $search What to searchid for
* @return string
*/
function GenGroupSQL($pattern, $search = '', $extra = 0)
{
$pattern = RunGroupMacros($pattern);
if ($pattern === false) {
return false;
}
$tmp = explode(' ', $pattern);
$tables = array();
foreach ($tmp as $opt) {
if (strstr($opt, '%') && strstr($opt, '.')) {
$tmpp = explode('.', $opt, 2);
$tmpp[0] = str_replace('%', '', $tmpp[0]);
$tables[] = mres(str_replace('(', '', $tmpp[0]));
$pattern = str_replace($opt, $tmpp[0] . '.' . $tmpp[1], $pattern);
}
}
$pattern = rtrim($pattern, '&&');
$pattern = rtrim($pattern, '||');
$tables = array_keys(array_flip($tables));
if (dbFetchCell('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_NAME = ? && COLUMN_NAME = ?', array($tables[0], 'device_id')) != 1) {
//Our first table has no valid glue, append the 'devices' table to it!
array_unshift($tables, 'devices');
}
$x = sizeof($tables) - 1;
$i = 0;
$join = "";
while ($i < $x) {
if (isset($tables[$i + 1])) {
$gtmp = ResolveGlues(array($tables[$i + 1]), 'device_id');
if ($gtmp === false) {
//Cannot resolve glue-chain. Rule is invalid.
return false;
}
$last = "";
$qry = "";
foreach ($gtmp as $glue) {
if (empty($last)) {
list($tmp, $last) = explode('.', $glue);
$qry .= $glue . ' = ';
} else {
list($tmp, $new) = explode('.', $glue);
$qry .= $tmp . '.' . $last . ' && ' . $tmp . '.' . $new . ' = ';
$last = $new;
}
if (!in_array($tmp, $tables)) {
$tables[] = $tmp;
}
}
$join .= "( " . $qry . $tables[0] . ".device_id ) && ";
}
$i++;
}
if ($extra === 1) {
$sql_extra = ",`devices`.*";
}
if (!empty($search)) {
$search = str_replace("(", "", $tables[0]) . '.' . $search . ' AND';
}
if (!empty($join)) {
$join = '(' . rtrim($join, ' && ') . ') &&';
}
$sql = 'SELECT DISTINCT(' . str_replace('(', '', $tables[0]) . '.device_id)' . $sql_extra . ' FROM ' . implode(',', $tables) . ' WHERE ' . $join . ' ' . $search . ' (' . str_replace(array('%', '@', '!~', '~'), array('', '.*', 'NOT REGEXP', 'REGEXP'), $pattern) . ')';
return $sql;
}
