Beispiel #1
0
/**
 * 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;
}
Beispiel #2
0
/**
 * 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;
}
Beispiel #3
0
/**
 * 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;
}