/modules/perfmon/classes/general/query.php
PHP | 678 lines | 570 code | 73 blank | 35 comment | 89 complexity | e93bd48e37682417225255ca838ab1ef MD5 | raw file
- <?php
- class CPerfQueryJoin
- {
- var $left_table = "";
- var $left_column = "";
- var $left_const = "";
- var $right_table = "";
- var $right_column = "";
- var $right_const = "";
- function _parse($sql, &$table, &$column, &$const)
- {
- $match = array();
- if (preg_match("/^([`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1})\\.(.+)\$/", $sql, $match))
- {
- $table = $match[1];
- $column = $match[2];
- $const = "";
- }
- else
- {
- $table = "";
- $column = "";
- $const = $sql;
- }
- }
- function parse_left($sql)
- {
- $this->_parse($sql, $this->left_table, $this->left_column, $this->left_const);
- }
- function parse_right($sql)
- {
- $this->_parse($sql, $this->right_table, $this->right_column, $this->right_const);
- }
- }
- class CPerfQueryWhere
- {
- var $table_aliases_regex = "";
- var $equation_regex = "";
- var $sql = "";
- var $simplified_sql = "";
- var $joins = array();
- function __construct($table_aliases_regex)
- {
- $this->table_aliases_regex = $table_aliases_regex;
- $this->equation_regex = "(?:".$this->table_aliases_regex."\\.[`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1}|[0-9]+|'[^']*') (?:=|<|>|> =|< =|IS) (?:".$this->table_aliases_regex."\\.[`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1}|[0-9]+|'[^']*'|NULL)";
- }
- function parse($sql)
- {
- //Transform and simplify sql
- //
- //Remove balanced braces around equals
- $sql = $this->_remove_braces(CPerfQuery::removeSpaces($sql));
- //Replace "expr1 = <const1> or expr1 = <const2> or expr1 = <const3> ..."
- //with "expr1 in (<const1>, ...)"
- $new_sql = preg_replace_callback("/\\( (".$this->equation_regex."(?: OR ".$this->equation_regex.")+) \\)/i", array($this, "_or2in"), CPerfQuery::removeSpaces($sql));
- if ($new_sql !== null)
- $sql = CPerfQuery::removeSpaces($new_sql);
- //Replace IN with no more than 5 values to equal
- $sql = preg_replace("/ IN[ ]*\\([ ]*([0-9]+|'[^']*')([ ]*,[ ]*([0-9]+|'[^']*')[ ]*){0,5}[ ]*\\)/i", " = \\1 ", $sql);
- //Remove complex inner syntax
- while (preg_match("/\\([^()]*\\)/", $sql))
- $sql = preg_replace("/\\([^()]*\\)/", "", $sql);
- $this->simplified_sql = $sql;
- foreach (preg_split("/ and /i", $sql) as $str)
- {
- $match = array();
- if (preg_match("/(".$this->table_aliases_regex."\\.[`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1}) = (".$this->table_aliases_regex."\\.[`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1})/", $str, $match))
- {
- $join = new CPerfQueryJoin;
- $join->parse_left($match[1]);
- $join->parse_right($match[2]);
- $this->joins[] = $join;
- }
- elseif (preg_match("/(".$this->table_aliases_regex."\\.[`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1}) = ([0-9]+|'.+')/", $str, $match))
- {
- $join = new CPerfQueryJoin;
- $join->parse_left($match[1]);
- $join->parse_right($match[2]);
- $this->joins[] = $join;
- }
- }
- return !empty($this->joins);
- }
- //Remove balanced braces around equals
- function _remove_braces($sql)
- {
- while (true)
- {
- $new_sql = preg_replace("/\\([ ]*(".$this->equation_regex."(?: AND ".$this->equation_regex.")*)[ ]*\\)/i", "\\1", $sql);
- if ($new_sql === null)
- break;
- if ($new_sql === $sql)
- {
- $new_sql = preg_replace("/\\( \\( (".$this->equation_regex."(?: OR ".$this->equation_regex.")*) \\) \\)/i", "( \\1 )", trim($sql));
- if ($new_sql === null)
- break;
- if ($new_sql === $sql)
- break;
- }
- $sql = trim($new_sql);
- }
- return $sql;
- }
- function _or2in($or_match)
- {
- $sql = $or_match[0];
- $match = array();
- if (preg_match_all("/(".$this->table_aliases_regex."\\.[a-zA-Z0-9_]+|[0-9]+|'[^']*') (?:=) ([0-9]+|'[^']*')/", $or_match[1], $match))
- {
- if (count(array_unique($match[1])) == 1)
- $sql = $match[1][0]." IN ( ".implode(", ", $match[2])." )";
- }
- return $sql;
- }
- }
- class CPerfQueryTable
- {
- var $sql = "";
- var $name = "";
- var $alias = "";
- var $join = "";
- function parse($sql)
- {
- $sql = CPerfQuery::removeSpaces($sql);
- $match = array();
- if (preg_match("/^([`\"\\[\\]]{0,1}[a-z0-9_]+[`\"\\[\\]]{0,1}) ([`\"\\[\\]]{0,1}[a-z0-9_]+[`\"\\[\\]]{0,1}) on (.+)\$/i", $sql, $match))
- {
- $this->name = $match[1];
- $this->alias = $match[2];
- $this->join = $match[3];
- }
- if (preg_match("/^([`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1}) ([`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1})(\$| )/", $sql, $match))
- {
- $this->name = $match[1];
- $this->alias = $match[2];
- }
- elseif (preg_match("/^([`\"\\[\\]]{0,1}[a-zA-Z0-9_]+[`\"\\[\\]]{0,1})\$/", $sql, $match))
- {
- $this->name = $match[1];
- $this->alias = $this->name;
- }
- else
- {
- return false;
- }
- $this->sql = $sql;
- return true;
- }
- }
- class CPerfQueryFrom
- {
- var $sql = "";
- /** @var array[]CPerfQueryTable */
- var $tables = array();
- var $joins = array();
- function parse($sql)
- {
- $sql = CPerfQuery::removeSpaces($sql);
- $match = array();
- if (preg_match("/^select(.*) from (.*?) (where|group|having|order)/is", $sql, $match))
- $this->sql = $match[2];
- elseif (preg_match("/^select(.*) from (.*?)\$/is", $sql, $match))
- $this->sql = $match[2];
- else
- $this->sql = "";
- if ($this->sql)
- {
- $arJoinTables = preg_split("/(,|inner\\s+join|left\\s+join)(?=\\s+[`\"\\[\\]]{0,1}[a-z0-9_]+[`\"\\[\\]]{0,1})/is", $this->sql);
- foreach ($arJoinTables as $str)
- {
- $table = new CPerfQueryTable;
- if ($table->parse($str))
- {
- $this->tables[] = $table;
- }
- }
- if (count($this->tables) <= 0)
- return false;
- $tables_regex = "(?:".implode("|", $this->getTableAliases()).")";
- /** @var CPerfQueryTable $table */
- foreach ($this->tables as $table)
- {
- $where = new CPerfQueryWhere($tables_regex);
- if ($where->parse($table->join))
- {
- $this->joins = array_merge($this->joins, $where->joins);
- }
- }
- }
- return !empty($this->tables);
- }
- function getTableAliases()
- {
- $res = array();
- /** @var CPerfQueryTable $table */
- foreach ($this->tables as $table)
- $res[] = $table->alias;
- return $res;
- }
- }
- class CPerfQuery
- {
- var $sql = "";
- var $type = "unknown";
- var $subqueries = array();
- /** @var CPerfQueryFrom */
- var $from = null;
- /** @var CPerfQueryWhere */
- var $where = null;
- public static function transform2select($sql)
- {
- $match = array();
- if (preg_match("#^\\s*insert\\s+into\\s+(.+?)(\\(|)\\s*(\\s*select.*)\\s*\\2\\s*(\$|ON\\s+DUPLICATE\\s+KEY\\s+UPDATE)#is", $sql, $match))
- $result = $match[3];
- elseif (preg_match("#^\\s*DELETE\\s+#i", $sql))
- $result = preg_replace("#^\\s*(DELETE.*?FROM)#is", "select * from", $sql);
- elseif (preg_match("#^\\s*SELECT\\s+#i", $sql))
- $result = $sql;
- else
- $result = "";
- return $result;
- }
- public static function removeSpaces($str)
- {
- return trim(preg_replace("/[ \t\n\r]+/", " ", $str), " \t\n\r");
- }
- function parse($sql)
- {
- $this->sql = preg_replace("/([()=])/", " \\1 ", $sql);
- $this->sql = CPerfQuery::removeSpaces($this->sql);
- $match = array();
- if (preg_match("/^(select) /i", $this->sql, $match))
- $this->type = mb_strtolower($match[1]);
- else
- $this->type = "unknown";
- if ($this->type == "select")
- {
- //0 TODO replace literals with placeholders
- //1 remove subqueries from sql
- if (!$this->parse_subqueries())
- return false;
- //2 parse from
- $this->from = new CPerfQueryFrom;
- if (!$this->from->parse($this->sql))
- return false;
- $tables_regex = "(?:".implode("|", $this->from->getTableAliases()).")";
- $this->where = new CPerfQueryWhere($tables_regex);
- if (preg_match("/ where (.+?)(\$| group | having | order )/i", $this->sql, $match))
- $this->where->parse($match[1]);
- return true;
- }
- else
- {
- return false;
- }
- }
- function parse_subqueries()
- {
- $this->subqueries = array();
- $ar = preg_split("/(\\(\\s*select|\\(|\\))/is", $this->sql, -1, PREG_SPLIT_DELIM_CAPTURE);
- $subq = 0;
- $braces = 0;
- foreach ($ar as $i => $str)
- {
- if ($str == ")")
- $braces--;
- elseif (mb_substr($str, 0, 1) == "(")
- $braces++;
- if ($subq == 0)
- {
- if (preg_match("/^\\(\\s*select/is", $str))
- {
- $this->subqueries[] = mb_substr($str, 1);
- $subq++;
- unset($ar[$i]);
- }
- }
- elseif ($braces == 0)
- {
- $subq--;
- unset($ar[$i]);
- }
- else
- {
- $this->subqueries[count($this->subqueries) - 1] .= $str;
- unset($ar[$i]);
- }
- }
- $this->sql = implode('', $ar);
- return true;
- }
- function cmp($table, $alias)
- {
- if ($table === $alias)
- return true;
- elseif ($table === "`".$alias."`")
- return true;
- else
- return false;
- }
- function table_joins($table_alias)
- {
- //Lookup table by its alias
- $suggest_table = null;
- /** @var CPerfQueryTable $table */
- foreach ($this->from->tables as $table)
- {
- if ($this->cmp($table->alias, $table_alias))
- $suggest_table = $table;
- }
- if (!isset($suggest_table))
- return array();
- $arTableJoins = array(
- "WHERE" => array()
- );
- //1 iteration gather inter tables joins
- foreach ($this->from->joins as $join)
- {
- if ($this->cmp($join->left_table, $table_alias) && $join->right_table !== "")
- {
- if (!isset($arTableJoins[$join->right_table]))
- $arTableJoins[$join->right_table] = array();
- $arTableJoins[$join->right_table][] = $join->left_column;
- }
- elseif ($this->cmp($join->right_table, $table_alias) && $join->left_table !== "")
- {
- if (!isset($arTableJoins[$join->left_table]))
- $arTableJoins[$join->left_table] = array();
- $arTableJoins[$join->left_table][] = $join->right_column;
- }
- }
- //2 iteration gather inter tables joins from where
- foreach ($this->where->joins as $join)
- {
- if ($this->cmp($join->left_table, $table_alias) && $join->right_table !== "")
- {
- if (!isset($arTableJoins[$join->right_table]))
- $arTableJoins[$join->right_table] = array();
- $arTableJoins[$join->right_table][] = $join->left_column;
- }
- elseif ($this->cmp($join->right_table, $table_alias) && $join->left_table !== "")
- {
- if (!isset($arTableJoins[$join->left_table]))
- $arTableJoins[$join->left_table] = array();
- $arTableJoins[$join->left_table][] = $join->right_column;
- }
- }
- //3 iteration add constant filters from joins
- foreach ($this->from->joins as $join)
- {
- if ($this->cmp($join->left_table, $table_alias) && $join->right_table === "")
- {
- foreach ($arTableJoins as $i => $arColumns)
- $arTableJoins[$i][] = $join->left_column;
- }
- elseif ($this->cmp($join->right_table, $table_alias) && $join->left_table === "")
- {
- foreach ($arTableJoins as $i => $arColumns)
- $arTableJoins[$i][] = $join->right_column;
- }
- }
- //4 iteration add constant filters from where
- foreach ($this->where->joins as $join)
- {
- if ($this->cmp($join->left_table, $table_alias) && $join->right_table === "")
- {
- foreach ($arTableJoins as $i => $arColumns)
- $arTableJoins[$i][] = $join->left_column;
- }
- elseif ($this->cmp($join->right_table, $table_alias) && $join->left_table === "")
- {
- foreach ($arTableJoins as $i => $arColumns)
- $arTableJoins[$i][] = $join->right_column;
- }
- }
- if (empty($arTableJoins["WHERE"]))
- unset($arTableJoins["WHERE"]);
- return $arTableJoins;
- }
- function suggest_index($table_alias)
- {
- global $DB;
- $suggest_table = null;
- /** @var CPerfQueryTable $table */
- foreach ($this->from->tables as $table)
- {
- if ($this->cmp($table->alias, $table_alias))
- $suggest_table = $table;
- }
- if (!isset($suggest_table))
- return false;
- $arTableJoins = $this->table_joins($table_alias);
- //Next read indexes already have
- $arSuggest = array();
- if (!empty($arTableJoins))
- {
- if (!$DB->TableExists($suggest_table->name))
- return false;
- $table = new CPerfomanceTable;
- $arIndexes = $table->GetIndexes($suggest_table->name);
- foreach ($arIndexes as $index_name => $arColumns)
- $arIndexes[$index_name] = implode(",", $arColumns);
- //Test our suggestion against existing indexes
- foreach ($arTableJoins as $i => $arColumns)
- {
- $index_found = "";
- $arColumns = $this->_adjust_columns($arColumns);
- //Take all possible combinations of columns
- $arCombosToTest = $this->array_power_set($arColumns);
- foreach ($arCombosToTest as $arComboColumns)
- {
- if (!empty($arComboColumns))
- {
- $index2test = implode(",", $arComboColumns);
- //Try to find out if index already exists
- foreach ($arIndexes as $index_name => $index_columns)
- {
- if (mb_substr($index_columns, 0, mb_strlen($index2test)) === $index2test)
- {
- if (
- $index_found === ""
- || count(explode(",", $index_found)) < count(explode(",", $index2test))
- )
- $index_found = $index2test;
- }
- }
- }
- }
- //
- if (!$index_found)
- {
- sort($arColumns);
- $arSuggest[] = $suggest_table->alias.":".$suggest_table->name.":".implode(",", $arColumns);
- }
- }
- }
- if (!empty($arSuggest))
- {
- return $arSuggest;
- }
- else
- {
- return false;
- }
- }
- function array_power_set($array)
- {
- $results = array(array());
- foreach ($array as $element)
- foreach ($results as $combination)
- array_push($results, array_merge(array($element), $combination));
- return $results;
- }
- function _adjust_columns($arColumns)
- {
- $arColumns = array_unique($arColumns);
- while (mb_strlen(implode(",", $arColumns)) > 250)
- {
- //TODO: add brains here
- //1 exclude blobs and clobs
- //2 etc.
- array_pop($arColumns);
- }
- return $arColumns;
- }
- function has_where($table_alias = false)
- {
- if ($table_alias === false)
- return !empty($this->where->joins);
- foreach ($this->where->joins as $join)
- {
- if ($this->cmp($join->left_table, $table_alias))
- {
- return true;
- }
- elseif ($this->cmp($join->right_table, $table_alias))
- {
- return true;
- }
- }
- return false;
- }
- function find_value($table_name, $column_name)
- {
- //Lookup table by its name
- /** @var CPerfQueryTable $table */
- foreach ($this->from->tables as $table)
- {
- if ($table->name === $table_name)
- {
- $table_alias = $table->alias;
- foreach ($this->where->joins as $join)
- {
- if (
- $join->left_table === $table_alias
- && $join->left_column === $column_name
- && $join->right_const !== ""
- )
- {
- return $join->right_const;
- }
- elseif (
- $join->right_table === $table_alias
- && $join->right_column === $column_name
- && $join->left_const !== ""
- )
- {
- return $join->left_const;
- }
- }
- foreach ($this->from->joins as $join)
- {
- if (
- $join->left_table === $table_alias
- && $join->left_column === $column_name
- && $join->right_const !== ""
- )
- {
- return $join->right_const;
- }
- elseif (
- $join->right_table === $table_alias
- && $join->right_column === $column_name
- && $join->left_const !== ""
- )
- {
- return $join->left_const;
- }
- }
- }
- }
- return "";
- }
- function find_join($table_name, $column_name)
- {
- //Lookup table by its name
- $suggest_table = null;
- /** @var CPerfQueryTable $table */
- foreach ($this->from->tables as $table)
- {
- if ($table->name === $table_name)
- $suggest_table = $table;
- }
- if (!isset($suggest_table))
- return "";
- $table_alias = $suggest_table->alias;
- foreach ($this->where->joins as $join)
- {
- if (
- $join->left_table === $table_alias
- && $join->left_column === $column_name
- && $join->right_table !== ""
- )
- {
- return $join->right_table.".".$join->right_column;
- }
- elseif (
- $join->right_table === $table_alias
- && $join->right_column === $column_name
- && $join->left_table !== ""
- )
- {
- return $join->left_table.".".$join->left_column;
- }
- }
- foreach ($this->from->joins as $join)
- {
- if (
- $join->left_table === $table_alias
- && $join->left_column === $column_name
- && $join->right_table !== ""
- )
- {
- return $join->right_table.".".$join->right_column;
- }
- elseif (
- $join->right_table === $table_alias
- && $join->right_column === $column_name
- && $join->left_table !== ""
- )
- {
- return $join->left_table.".".$join->left_column;
- }
- }
- return "";
- }
- public static function remove_literals($sql)
- {
- return preg_replace('/(
- "[^"\\\\]*(?:\\\\.[^"\\\\]*)*" # match double quoted string
- |
- \'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*\' # match single quoted string
- |
- (?s:\\/\\*.*?\\*\\/) # multi line comments
- |
- \\/\\/.*?\\n # single line comments
- |
- (?<![A-Za-z_])([0-9]+\\.[0-9]+|[0-9]+)(?![A-Za-z_]) # an number
- |
- (?i:\\sIN\\s*\\(\\s*[0-9.]+(?:\\s*,\\s*[0-9.])*\\s*\\)) # in (1, 2, 3)
- |
- (?i:\\sIN\\s*\\(\\s*[\'].+?[\'](?:\\s*,\\s*[\'].+?[\'])*\\s*\\)) # in (\'a\', \'b\', \'c\')
- )/x', '', $sql);
- }
- }