diff options
| author | Dan Ungureanu <udan1107@gmail.com> | 2015-07-07 03:21:38 +0300 |
|---|---|---|
| committer | Dan Ungureanu <udan1107@gmail.com> | 2015-07-10 23:18:17 +0300 |
| commit | eeafcad986917b581292a1c7ac0c27cf98f67787 (patch) | |
| tree | e5f245901869531e39daaec2e35fb4003251bbe3 | |
| parent | 11f42cd80ea60c44eca74ddae82414023f9cfce6 (diff) | |
Using the new parser to simulate queries.
Updated sql-parser library to udan11/sql-parser@e58d230.
Signed-off-by: Dan Ungureanu <udan1107@gmail.com>
25 files changed, 430 insertions, 426 deletions
diff --git a/libraries/import.lib.php b/libraries/import.lib.php index 9656e97503..9f379e6026 100644 --- a/libraries/import.lib.php +++ b/libraries/import.lib.php @@ -1388,33 +1388,33 @@ function PMA_handleSimulateDMLRequest() continue; } - // Parse and Analyze the query. - $parsed_sql = PMA_SQP_parse($sql_query); - $analyzed_sql = PMA_SQP_analyze($parsed_sql); + // Parsing the query. + $parser = new SqlParser\Parser($sql_query); + + if (empty($parser->statements[0])) { + continue; + } + + $statement = $parser->statements[0]; + $analyzed_sql_results = array( - 'parsed_sql' => $parsed_sql, - 'analyzed_sql' => $analyzed_sql + 'query' => $sql_query, + 'parser' => $parser, + 'statement' => $statement, ); - // Only UPDATE/DELETE queries accepted. - $query_type = $analyzed_sql_results['analyzed_sql'][0]['querytype']; - if ($query_type != 'UPDATE' && $query_type != 'DELETE') { + if ((!(($statement instanceof SqlParser\Statements\UpdateStatement) || + ($statement instanceof SqlParser\Statements\DeleteStatement))) || + (!empty($statement->join)) + ) { $error = $error_msg; break; } - // Only single-table queries accepted. - $table_references = PMA_getTableReferences($analyzed_sql_results); - $table_references = $table_references ? $table_references : ''; - if (preg_match('/JOIN/i', $table_references)) { + $tables = SqlParser\Utils\Query::getTables($statement); + if (count($tables) > 1) { $error = $error_msg; break; - } else { - $tables = explode(',', $table_references); - if (count($tables) > 1) { - $error = $error_msg; - break; - } } // Get the matched rows for the query. @@ -1444,20 +1444,18 @@ function PMA_handleSimulateDMLRequest() */ function PMA_getMatchedRows($analyzed_sql_results = array()) { - // Get the query type. - $query_type = (isset($analyzed_sql_results['analyzed_sql'][0]['querytype'])) - ? $analyzed_sql_results['analyzed_sql'][0]['querytype'] - : ''; + $statement = $analyzed_sql_results['statement']; $matched_row_query = ''; - if ($query_type == 'DELETE') { + if ($statement instanceof SqlParser\Statements\DeleteStatement) { $matched_row_query = PMA_getSimulatedDeleteQuery($analyzed_sql_results); - } else if ($query_type == 'UPDATE') { + } elseif ($statement instanceof SqlParser\Statements\UpdateStatement) { $matched_row_query = PMA_getSimulatedUpdateQuery($analyzed_sql_results); } // Execute the query and get the number of matched rows. $matched_rows = PMA_executeMatchedRowQuery($matched_row_query); + // URL to matched rows. $_url_params = array( 'db' => $GLOBALS['db'], @@ -1466,9 +1464,7 @@ function PMA_getMatchedRows($analyzed_sql_results = array()) $matched_rows_url = 'sql.php' . PMA_URL_getCommon($_url_params); return array( - 'sql_query' => PMA_Util::formatSql( - $analyzed_sql_results['parsed_sql']['raw'] - ), + 'sql_query' => PMA_Util::formatSql($analyzed_sql_results['query']), 'matched_rows' => $matched_rows, 'matched_rows_url' => $matched_rows_url ); @@ -1483,95 +1479,50 @@ function PMA_getMatchedRows($analyzed_sql_results = array()) */ function PMA_getSimulatedUpdateQuery($analyzed_sql_results) { - $where_clause = ''; - $extra_where_clause = array(); - $target_cols = array(); - - $prev_term = ''; - $i = 0; - $in_function = 0; - foreach ($analyzed_sql_results['parsed_sql'] as $key => $term) { - if (! isset($get_set_expr) - && preg_match( - '/\bSET\b/i', - isset($term['data']) ? $term['data'] : '' - ) - ) { - $get_set_expr = true; - continue; - } - - if (isset($get_set_expr)) { - if (preg_match( - '/\bWHERE\b|\bORDER BY\b|\bLIMIT\b/i', - isset($term['data']) ? $term['data'] : '' - ) - ) { - break; - } - if (!$in_function) { - if ($term['type'] == 'punct_listsep') { - $extra_where_clause[] = ' OR '; - } else if ($term['type'] == 'punct') { - $extra_where_clause[] = ' <> '; - } else if ($term['type'] == 'alpha_functionName') { - array_pop($extra_where_clause); - array_pop($extra_where_clause); - } else { - $extra_where_clause[] = $term['data']; - } - } else if ($term['type'] == 'punct_bracket_close_round') { - $in_function--; - } + $table_references = SqlParser\Utils\Query::getTables( + $analyzed_sql_results['statement'] + ); - if ($term['type'] == 'alpha_functionName') { - $in_function++; - } + $where = SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'WHERE' + ); - // Get columns in SET expression. - if ($prev_term != 'punct') { - if ($term['type'] != 'punct_listsep' - && $term['type'] != 'punct' - && $term['type'] != 'punct_bracket_open_round' - && $term['type'] != 'punct_bracket_close_round' - && !$in_function - && isset($term['data']) - ) { - if (isset($target_cols[$i])) { - $target_cols[$i] .= $term['data']; - } else { - $target_cols[$i] = $term['data']; - } - } - } else { - $i++; - } + if (empty($where)) { + $where = '1'; + } - $prev_term = $term['type']; - continue; - } + $diff = array(); + foreach ($analyzed_sql_results['statement']->set as $set) { + $columns[] = $set->column; + $diff[] = $set->column . ' <> ' . $set->value; + } + if (!empty($diff)) { + $where .= ' AND (' . implode(' OR ', $diff) . ')'; } - // Get table_references. - $table_references = PMA_getTableReferences($analyzed_sql_results); - $target_cols = implode(', ', $target_cols); + $order_and_limit = ''; - // Get WHERE clause. - $where_clause .= $analyzed_sql_results['analyzed_sql'][0]['where_clause']; - if (empty($where_clause)) { - $where_clause = (!empty($extra_where_clause) && $extra_where_clause[0]) - ? implode(' ', $extra_where_clause) - : '1'; + if (!empty($analyzed_sql_results['statement']->order)) { + $order_and_limit .= ' ORDER BY ' . SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'ORDER BY' + ); } - $matched_row_query = 'SELECT ' - . $target_cols - . ' FROM ' - . $table_references - . ' WHERE ' - . $where_clause; + if (!empty($analyzed_sql_results['statement']->limit)) { + $order_and_limit .= ' LIMIT ' . SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'LIMIT' + ); + } - return $matched_row_query; + return 'SELECT ' . implode(', ', $columns) . + ' FROM ' . implode(', ', $table_references) . + ' WHERE ' . $where . $order_and_limit; } /** @@ -1583,115 +1534,40 @@ function PMA_getSimulatedUpdateQuery($analyzed_sql_results) */ function PMA_getSimulatedDeleteQuery($analyzed_sql_results) { - $where_clause = ''; - - $where_clause .= $analyzed_sql_results['analyzed_sql'][0]['where_clause']; - if (empty($where_clause)) { - $where_clause = '1'; - } - - // Get the table_references. - $table_references = PMA_getTableReferences($analyzed_sql_results); - - $matched_row_query = 'SELECT * ' - . ' FROM ' - . $table_references - . ' WHERE ' - . $where_clause; - - return $matched_row_query; -} - -/** - * Finds table_references from a given query. - * Queries Supported: INSERT, UPDATE, DELETE, REPLACE, ALTER, DROP, TRUNCATE - * and RENAME. - * - * @param array $analyzed_sql_results Analyzed SQL results from parser - * - * @return string table_references - */ -function PMA_getTableReferences($analyzed_sql_results) -{ - $table_references = ''; - foreach ($analyzed_sql_results['parsed_sql'] as $key => $term) { - // Skip first KeyWord and other invalid keys. - if ($key == 0 || ! isset($term['data'])) { - continue; - } + $table_references = SqlParser\Utils\Query::getTables( + $analyzed_sql_results['statement'] + ); - // Get the query type. - $query_type = (isset($analyzed_sql_results['analyzed_sql'][0]['querytype'])) - ? $analyzed_sql_results['analyzed_sql'][0]['querytype'] - : ''; - - // Terms to 'ignore' from query for table_references. - $ignore_re = '/'; - // Terminating condition for table_references. - $terminate_re = '/'; - - // Create relevant Regular Expressions. - switch ($query_type) { - case 'REPLACE': - case 'INSERT': - $ignore_re .= '\bINSERT\b|\bREPLACE\b|\bLOW_PRIORITY\b|\bDELAYED\b' - . '|\bHIGH_PRIORITY\b|\bIGNORE\b|\bINTO\b'; - $terminate_re .= '\bPARTITION\b|\(|\bVALUE\b|\bVALUES\b|\bSELECT\b'; - break; - case 'UPDATE': - $ignore_re .= '\bUPDATE\b|\bLOW_PRIORITY\b|\bIGNORE\b'; - $terminate_re .= '\bSET\b|\bUSING\b'; - break; - case 'DELETE': - $ignore_re .= '\bDELETE\b|\bLOW_PRIORITY\b|\bQUICK\b|\bIGNORE\b' - . '|\bFROM\b'; - $terminate_re .= '\bPARTITION\b|\bWHERE\b|\bORDER\b|\bLIMIT\b|\bUSING\b'; - break; - case 'ALTER': - $ignore_re .= '\bALTER\b|\bONLINE\b|\bOFFLINE\b|\bIGNORE\b|\bTABLE\b'; - $terminate_re .= '\bADD\b|\bALTER\b|\bCHANGE\b|\bMODIFY\b|\bDROP\b' - . '|\bDISABLE\b|\bENABLE\b|\bRENAME\b|\bORDER\b|\bCONVERT\b' - . '|\bDEFAULT\b|\bDISCARD\b|\bIMPORT\b|\bCOALESCE\b|\bREORGANIZE\b' - . '|\bANALYZE\b|\bCHECK\b|\bOPTIMIZE\b|\bREBUILD\b|\bREPAIR\b' - . '|\bPARTITION\b|\bREMOVE\b|\bCHARACTER\b'; - break; - case 'DROP': - $ignore_re .= '\bDROP\b|\bTEMPORARY\b|\bTABLE\b|\bIF\b|\bEXISTS\b'; - $terminate_re .= '\bRESTRICT\b|\bCASCADE\b'; - break; - case 'TRUNCATE': - $ignore_re .= '\bTRUNCATE\b|\bTABLE\b'; - $terminate_re .= ''; - break; - case 'RENAME': - $ignore_re .= '\bRENAME\b|\bTABLE\b'; - $terminate_re .= '\bTO\b'; - break; - default: - return false; - } + $where = SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'WHERE' + ); - // Ignore 'case' in RegEx. - $ignore_re .= '/i'; - $terminate_re .= '/i'; + if (empty($where)) { + $where = '1'; + } - if ($query_type != 'TRUNCATE' - && preg_match($terminate_re, $term['data']) - ) { - break; - } + $order_and_limit = ''; - if (preg_match($ignore_re, $term['data']) - || ! is_numeric($key) - || $key == 0 - ) { - continue; - } + if (!empty($analyzed_sql_results['statement']->order)) { + $order_and_limit .= ' ORDER BY ' . SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'ORDER BY' + ); + } - $table_references .= ' ' . $term['data']; + if (!empty($analyzed_sql_results['statement']->limit)) { + $order_and_limit .= ' LIMIT ' . SqlParser\Utils\Query::getClause( + $analyzed_sql_results['statement'], + $analyzed_sql_results['parser']->list, + 'LIMIT' + ); } - return $table_references; + return 'SELECT * FROM ' . implode(', ', $table_references) . + ' WHERE ' . $where . $order_and_limit; } /** @@ -1713,93 +1589,6 @@ function PMA_executeMatchedRowQuery($matched_row_query) } /** - * Extracts unique table names from table_references. - * - * @param string $table_references table_references - * - * @return array $table_names - */ -function PMA_getTableNamesFromTableReferences($table_references) -{ - $table_names = array(); - $parsed_data = PMA_SQP_parse($table_references); - - $prev_term = array( - 'data' => '', - 'type' => '' - ); - $on_encountered = false; - $qualifier_encountered = false; - $i = 0; - foreach ($parsed_data as $key => $term) { - // To skip first 'raw' key and other invalid keys. - if (! is_numeric($key) - || ! isset($term['data']) - || ! isset($term['type']) - ) { - continue; - } - - $add_to_table_names = true; - - // Un-quote the data, if any. - if ($term['type'] == 'quote_backtick') { - $term['data'] = PMA_Util::unQuote($term['data']); - $term['type'] = 'alpha_identifier'; - } - - // New table name expected after 'JOIN' keyword. - if (preg_match('/\bJOIN\b/i', $term['data'])) { - $on_encountered = false; - } - - // If term is a qualifier, set flag. - if ($term['type'] == 'punct_qualifier') { - $qualifier_encountered = true; - } - - // Skip the JOIN conditions after 'ON' keyword. - if (preg_match('/\bON\b/i', $term['data'])) { - $on_encountered = true; - } - - // If the word is not an 'identifier', skip it. - if ($term['type'] != 'alpha_identifier') { - $add_to_table_names = false; - } - - // Skip table 'alias'. - if (preg_match('/\bAS\b/i', $prev_term['data']) - || $prev_term['type'] == 'alpha_identifier' - ) { - $add_to_table_names = false; - } - - // Everything fine up to now, add name to list if 'unique'. - if ($add_to_table_names - && ! $on_encountered - && ! in_array($term['data'], $table_names) - ) { - if (! $qualifier_encountered) { - $table_names[] = PMA_Util::backquote($term['data']); - $i++; - } else { - // If qualifier encountered, concatenate DB name and table name. - $table_names[$i-1] = $table_names[$i-1] - . '.' - . PMA_Util::backquote($term['data']); - $qualifier_encountered = false; - } - } - - // Update previous term. - $prev_term = $term; - } - - return $table_names; -} - -/** * Handles request for ROLLBACK. * * @param string $sql_query SQL query(s) @@ -1853,37 +1642,25 @@ function PMA_handleRollbackRequest($sql_query) */ function PMA_checkIfRollbackPossible($sql_query) { - // Supported queries. - $supported_queries = array( - 'INSERT', - 'UPDATE', - 'DELETE', - 'REPLACE' - ); + $parser = new SqlParser\Parser($sql_query); - // Parse and Analyze the query. - $parsed_sql = PMA_SQP_parse($sql_query); - $analyzed_sql = PMA_SQP_analyze($parsed_sql); - $analyzed_sql_results = array( - 'parsed_sql' => $parsed_sql, - 'analyzed_sql' => $analyzed_sql - ); + if (empty($parser->statements[0])) { + return false; + } - // Get the query type. - $query_type = (isset($analyzed_sql_results['analyzed_sql'][0]['querytype'])) - ? $analyzed_sql_results['analyzed_sql'][0]['querytype'] - : ''; + $statement = $parser->statements[0]; // Check if query is supported. - if (! in_array($query_type, $supported_queries)) { + if (!(($statement instanceof SqlParser\Statements\InsertStatement) + || ($statement instanceof SqlParser\Statements\UpdateStatement) + || ($statement instanceof SqlParser\Statements\DeleteStatement) + || ($statement instanceof SqlParser\Statements\ReplaceStatement)) + ) { return false; } // Get table_references from the query. - $table_references = PMA_getTableReferences($analyzed_sql_results); - $table_references = $table_references ? $table_references : ''; - // Get table names from table_references. - $tables = PMA_getTableNamesFromTableReferences($table_references); + $tables = SqlParser\Utils\Query::getTables($statement); // Check if each table is 'InnoDB'. foreach ($tables as $table) { diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50000.php b/libraries/sql-parser/src/Contexts/ContextMySql50000.php index dc2759a30b..7db5fc3a76 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50000.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50000.php @@ -146,7 +146,7 @@ class ContextMySql50000 extends Context 'GROUP BY' => 7, 'NOT NULL' => 7, 'ORDER BY' => 7, 'SET NULL' => 7, 'IF EXISTS' => 7, 'NO ACTION' => 7, 'ON DELETE' => 7, 'ON UPDATE' => 7, 'OR REPLACE' => 7, - 'SQL SECURITY' => 7, + 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50100.php b/libraries/sql-parser/src/Contexts/ContextMySql50100.php index 82db6843ed..a5c4bef0bf 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50100.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50100.php @@ -157,7 +157,7 @@ class ContextMySql50100 extends Context 'GROUP BY' => 7, 'NOT NULL' => 7, 'ORDER BY' => 7, 'SET NULL' => 7, 'IF EXISTS' => 7, 'NO ACTION' => 7, 'ON DELETE' => 7, 'ON UPDATE' => 7, 'OR REPLACE' => 7, - 'SQL SECURITY' => 7, + 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50500.php b/libraries/sql-parser/src/Contexts/ContextMySql50500.php index e338232eb0..3faf785315 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50500.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50500.php @@ -162,7 +162,7 @@ class ContextMySql50500 extends Context 'GROUP BY' => 7, 'NOT NULL' => 7, 'ORDER BY' => 7, 'SET NULL' => 7, 'IF EXISTS' => 7, 'NO ACTION' => 7, 'ON DELETE' => 7, 'ON UPDATE' => 7, 'OR REPLACE' => 7, - 'SQL SECURITY' => 7, + 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50600.php b/libraries/sql-parser/src/Contexts/ContextMySql50600.php index 780ea5208b..6a1af74921 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50600.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50600.php @@ -168,7 +168,7 @@ class ContextMySql50600 extends Context 'GROUP BY' => 7, 'NOT NULL' => 7, 'ORDER BY' => 7, 'SET NULL' => 7, 'IF EXISTS' => 7, 'NO ACTION' => 7, 'ON DELETE' => 7, 'ON UPDATE' => 7, 'OR REPLACE' => 7, - 'SQL SECURITY' => 7, + 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50700.php b/libraries/sql-parser/src/Contexts/ContextMySql50700.php index 6a3cfd638b..ac81e8a8d8 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50700.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50700.php @@ -176,7 +176,7 @@ class ContextMySql50700 extends Context 'GROUP BY' => 7, 'NOT NULL' => 7, 'ORDER BY' => 7, 'SET NULL' => 7, 'IF EXISTS' => 7, 'NO ACTION' => 7, 'ON DELETE' => 7, 'ON UPDATE' => 7, 'OR REPLACE' => 7, - 'SQL SECURITY' => 7, + 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, diff --git a/libraries/sql-parser/src/Fragments/AlterFragment.php b/libraries/sql-parser/src/Fragments/AlterFragment.php index 54338f9dab..b5c66e81e9 100644 --- a/libraries/sql-parser/src/Fragments/AlterFragment.php +++ b/libraries/sql-parser/src/Fragments/AlterFragment.php @@ -160,7 +160,7 @@ class AlterFragment extends Fragment ); if ($ret->field === null) { // No field was read. We go back one token so the next - // iteration will parse the same on, but in state 2. + // iteration will parse the same token, but in state 2. --$list->idx; } $state = 2; diff --git a/libraries/sql-parser/src/Fragments/FieldDefFragment.php b/libraries/sql-parser/src/Fragments/FieldDefFragment.php index 9f43fcc2fe..a4c21d79a1 100644 --- a/libraries/sql-parser/src/Fragments/FieldDefFragment.php +++ b/libraries/sql-parser/src/Fragments/FieldDefFragment.php @@ -221,7 +221,6 @@ class FieldDefFragment extends Fragment --$list->idx; return $ret; - } /** diff --git a/libraries/sql-parser/src/Fragments/IntoKeyword.php b/libraries/sql-parser/src/Fragments/IntoKeyword.php index 0b515319e3..cf65da6e5a 100644 --- a/libraries/sql-parser/src/Fragments/IntoKeyword.php +++ b/libraries/sql-parser/src/Fragments/IntoKeyword.php @@ -33,11 +33,11 @@ class IntoKeyword extends Fragment public $type; /** - * The name of the table or file. + * The destination, which can be a table or a file. * - * @var string + * @var string|FieldFragment */ - public $name; + public $dest; /** * The name of the columns. @@ -102,7 +102,15 @@ class IntoKeyword extends Fragment } if ($state === 0) { - $ret->name = $token->value; + $ret->dest = FieldFragment::parse( + $parser, + $list, + array( + 'noAlias' => true, + 'noBrackets' => true, + 'skipColumn' => true, + ) + ); $state = 1; } elseif ($state === 1) { if (($token->type === Token::TYPE_OPERATOR) && ($token->value === '(')) { @@ -111,7 +119,7 @@ class IntoKeyword extends Fragment } break; } elseif ($state === 2) { - $ret->name = $token->value; + $ret->dest = $token->value; ++$list->idx; break; } diff --git a/libraries/sql-parser/src/Fragments/ParamDefFragment.php b/libraries/sql-parser/src/Fragments/ParamDefFragment.php index 583c3abbcd..23b84e3220 100644 --- a/libraries/sql-parser/src/Fragments/ParamDefFragment.php +++ b/libraries/sql-parser/src/Fragments/ParamDefFragment.php @@ -135,6 +135,27 @@ class ParamDefFragment extends Fragment --$list->idx; return $ret; + } + /** + * @param ParamDefFragment[] $fragment The fragment to be built. + * + * @return string + */ + public static function build($fragment) + { + $ret = array(); + foreach ($fragment as $f) { + $tmp = ''; + if (!empty($f->inOut)) { + $tmp .= $f->inOut . ' '; + } + + $ret[] = trim( + $tmp . Context::escape($f->name) . ' ' . + DataTypeFragment::build($f->type) + ); + } + return '(' . implode(', ', $ret) . ')'; } } diff --git a/libraries/sql-parser/src/Fragments/RenameKeyword.php b/libraries/sql-parser/src/Fragments/RenameKeyword.php index 077982aafb..de81848584 100644 --- a/libraries/sql-parser/src/Fragments/RenameKeyword.php +++ b/libraries/sql-parser/src/Fragments/RenameKeyword.php @@ -26,16 +26,16 @@ class RenameKeyword extends Fragment { /** - * The old name. + * The old table name. * - * @var string + * @var FieldFragment */ public $old; /** - * The new name. + * The new table name. * - * @var string + * @var FieldFragment */ public $new; @@ -110,10 +110,26 @@ class RenameKeyword extends Fragment } if ($state == 0) { - $expr->old = $token->value; + $expr->old = FieldFragment::parse( + $parser, + $list, + array( + 'noAlias' => true, + 'noBrackets' => true, + 'skipColumn' => true, + ) + ); $state = 1; } elseif ($state == 2) { - $expr->new = $token->value; + $expr->new = FieldFragment::parse( + $parser, + $list, + array( + 'noBrackets' => true, + 'skipColumn' => true, + 'noAlias' => true, + ) + ); $state = 3; } diff --git a/libraries/sql-parser/src/Fragments/SetKeyword.php b/libraries/sql-parser/src/Fragments/SetKeyword.php index bced45fb43..713a179c84 100644 --- a/libraries/sql-parser/src/Fragments/SetKeyword.php +++ b/libraries/sql-parser/src/Fragments/SetKeyword.php @@ -90,24 +90,29 @@ class SetKeyword extends Fragment if ($token->type === Token::TYPE_OPERATOR) { if ($token->value === ',') { + $expr->column = trim($expr->column); + $expr->value = trim($expr->value); $ret[] = $expr; $expr = new SetKeyword(); $state = 0; continue; } elseif ($token->value === '=') { $state = 1; + continue; } } if ($state === 0) { - $expr->column .= $token->value; + $expr->column .= $token->token; } else { // } else if ($state === 1) { - $expr->value = $token->value; + $expr->value .= $token->token; } } // Last iteration was not saved. if (!empty($expr->column)) { + $expr->column = trim($expr->column); + $expr->value = trim($expr->value); $ret[] = $expr; } diff --git a/libraries/sql-parser/src/Parser.php b/libraries/sql-parser/src/Parser.php index ff55f5f7d1..3c574063d1 100644 --- a/libraries/sql-parser/src/Parser.php +++ b/libraries/sql-parser/src/Parser.php @@ -54,7 +54,7 @@ class Parser 'CREATE' => 'SqlParser\\Statements\\CreateStatement', 'DROP' => 'SqlParser\\Statements\\DropStatement', 'RENAME' => 'SqlParser\\Statements\\RenameStatement', - 'TRUNCATE' => '', + 'TRUNCATE' => 'SqlParser\\Statements\\TruncateStatement', // Data Manipulation Statements. // https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html @@ -185,9 +185,14 @@ class Parser 'class' => 'SqlParser\\Fragments\\FieldListFragment', 'field' => 'expr', ), + 'TRUNCATE' => array( + 'class' => 'SqlParser\\Fragments\\FieldFragment', + 'field' => 'table', + 'options' => array('skipColumn' => true), + ), 'UPDATE' => array( 'class' => 'SqlParser\\Fragments\\FieldListFragment', - 'field' => 'from', + 'field' => 'tables', 'options' => array('skipColumn' => true), ), 'VALUE' => array( diff --git a/libraries/sql-parser/src/Statements/CheckStatement.php b/libraries/sql-parser/src/Statements/CheckStatement.php index 38764ff869..2e0e5a37b7 100644 --- a/libraries/sql-parser/src/Statements/CheckStatement.php +++ b/libraries/sql-parser/src/Statements/CheckStatement.php @@ -40,11 +40,4 @@ class CheckStatement extends MaintenanceStatement 'EXTENDED' => 6, 'CHANGED' => 7, ); - - /** - * Checked tables. - * - * @var FieldFragment[] - */ - public $tables; } diff --git a/libraries/sql-parser/src/Statements/ChecksumStatement.php b/libraries/sql-parser/src/Statements/ChecksumStatement.php index e2c6371c12..0495932702 100644 --- a/libraries/sql-parser/src/Statements/ChecksumStatement.php +++ b/libraries/sql-parser/src/Statements/ChecksumStatement.php @@ -36,11 +36,4 @@ class ChecksumStatement extends MaintenanceStatement 'QUICK' => 2, 'EXTENDED' => 3, ); - - /** - * Checked tables. - * - * @var FieldFragment[] - */ - public $tables; } diff --git a/libraries/sql-parser/src/Statements/CreateStatement.php b/libraries/sql-parser/src/Statements/CreateStatement.php index 306bfebceb..71557c7e10 100644 --- a/libraries/sql-parser/src/Statements/CreateStatement.php +++ b/libraries/sql-parser/src/Statements/CreateStatement.php @@ -101,15 +101,28 @@ class CreateStatement extends Statement * @var array */ public static $FUNC_OPTIONS = array( - 'COMMENT' => array(1, 'var'), - 'LANGUAGE SQL' => 2, - 'DETERMINISTIC' => 3, - 'NOT DETERMINISTIC' => 3, - 'CONSTAINS SQL' => 4, - 'NO SQL' => 4, - 'READS SQL DATA' => 4, - 'MODIFIES SQL DATA' => 4, - 'SQL SEQURITY DEFINER' => array(5, 'var'), + 'COMMENT' => array(1, 'var'), + 'LANGUAGE SQL' => 2, + 'DETERMINISTIC' => 3, + 'NOT DETERMINISTIC' => 3, + 'CONSTAINS SQL' => 4, + 'NO SQL' => 4, + 'READS SQL DATA' => 4, + 'MODIFIES SQL DATA' => 4, + 'SQL SEQURITY DEFINER' => array(5, 'var'), + ); + + /** + * All trigger options. + * + * @var array + */ + public static $TRIGGER_OPTIONS = array( + 'BEFORE' => 1, + 'AFTER' => 1, + 'INSERT' => 2, + 'UPDATE' => 2, + 'DELETE' => 2, ); /** @@ -130,6 +143,7 @@ class CreateStatement extends Statement * * @see static::$TABLE_OPTIONS * @see static::$FUNC_OPTIONS + * @see static::$TRIGGER_OPTIONS */ public $entityOptions; @@ -144,6 +158,15 @@ class CreateStatement extends Statement public $fields; /** + * If `CREATE TRIGGER` the name of the table. + * + * Used by `CREATE TRIGGER`. + * + * @var FieldFragment + */ + public $table; + + /** * The return data type of this routine. * * Used by `CREATE FUNCTION`. @@ -167,7 +190,7 @@ class CreateStatement extends Statement * * Used by `CREATE FUNCTION`, `CREATE PROCEDURE` and `CREATE VIEW`. * - * @var Token[] + * @var Token[]|string */ public $body = array(); @@ -176,20 +199,43 @@ class CreateStatement extends Statement */ public function build() { - $tmp = ''; if ($this->options->has('TABLE')) { - $tmp = FieldDefFragment::build($this->fields); + return 'CREATE ' + . OptionsFragment::build($this->options) . ' ' + . FieldFragment::build($this->name) . ' ' + . FieldDefFragment::build($this->fields) . ' ' + . OptionsFragment::build($this->entityOptions); } elseif ($this->options->has('VIEW')) { + $tmp = ''; if (!empty($this->fields)) { - $tmp .= ArrayFragment::build($this->fields) . ' '; + $tmp = ArrayFragment::build($this->fields); + } + return 'CREATE ' + . OptionsFragment::build($this->options) . ' ' + . FieldFragment::build($this->name) . ' ' + . $tmp . ' AS ' . TokensList::build($this->body) . ' ' + . OptionsFragment::build($this->entityOptions); + } elseif ($this->options->has('TRIGGER')) { + return 'CREATE ' + . OptionsFragment::build($this->options) . ' ' + . FieldFragment::build($this->name) . ' ' + . OptionsFragment::build($this->entityOptions) . ' ' + . 'ON ' . FieldFragment::build($this->table) . ' ' + . 'FOR EACH ROW ' . TokensList::build($this->body); + } elseif (($this->options->has('PROCEDURE')) + || ($this->options->has('FUNCTION')) + ) { + $tmp = ''; + if ($this->options->has('FUNCTION')) { + $tmp = 'RETURNS ' . DataTypeFragment::build($this->return); } - $tmp .= 'AS ' . TokensList::build($this->body); + return 'CREATE ' + . OptionsFragment::build($this->options) . ' ' + . FieldFragment::build($this->name) . ' ' + . ParamDefFragment::build($this->parameters) . ' ' + . $tmp . ' ' . TokensList::build($this->body); } - return 'CREATE ' - . OptionsFragment::build($this->options) . ' ' - . FieldFragment::build($this->name) . ' ' - . $tmp . ' ' - . OptionsFragment::build($this->entityOptions); + return ''; } /** @@ -221,6 +267,7 @@ class CreateStatement extends Statement if ($this->options->has('TABLE')) { $this->fields = FieldDefFragment::parse($parser, $list); ++$list->idx; + $this->entityOptions = OptionsFragment::parse( $parser, $list, @@ -246,13 +293,14 @@ class CreateStatement extends Statement } } ++$list->idx; + $this->entityOptions = OptionsFragment::parse( $parser, $list, static::$FUNC_OPTIONS ); ++$list->idx; - $this->body = array(); + for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; $this->body[] = $token; @@ -263,7 +311,7 @@ class CreateStatement extends Statement } } } else if ($this->options->has('VIEW')) { - $token = $list->getNext(); + $token = $list->getNext(); // Skipping whitespaces and comments. // Parsing columns list. if (($token->type === Token::TYPE_OPERATOR) && ($token->value === '(')) { @@ -281,6 +329,42 @@ class CreateStatement extends Statement } $this->body[] = $token; } + } else if ($this->options->has('TRIGGER')) { + // Parsing the time and the event. + $this->entityOptions = OptionsFragment::parse( + $parser, + $list, + static::$TRIGGER_OPTIONS + ); + ++$list->idx; + + $list->getNextOfTypeAndValue(Token::TYPE_KEYWORD, 'ON'); + ++$list->idx; // Skipping `ON`. + + // Parsing the name of the table. + $this->fields = FieldFragment::parse( + $parser, + $list, + array( + 'skipColumn' => true, + 'noAlias' => true, + 'noBrackets' => true, + ) + ); + ++$list->idx; + + $list->getNextOfTypeAndValue(Token::TYPE_KEYWORD, 'FOR EACH ROW'); + ++$list->idx; // Skipping `FOR EACH ROW`. + + for (; $list->idx < $list->count; ++$list->idx) { + $token = $list->tokens[$list->idx]; + $this->body[] = $token; + if (($token->type === Token::TYPE_KEYWORD) + && ($token->value === 'END') + ) { + break; + } + } } } } diff --git a/libraries/sql-parser/src/Statements/DeleteStatement.php b/libraries/sql-parser/src/Statements/DeleteStatement.php index 9016da37de..04208fc094 100644 --- a/libraries/sql-parser/src/Statements/DeleteStatement.php +++ b/libraries/sql-parser/src/Statements/DeleteStatement.php @@ -40,6 +40,24 @@ class DeleteStatement extends Statement ); /** + * The clauses of this statement, in order. + * + * @see Statement::$CLAUSES + * + * @var array + */ + public static $CLAUSES = array( + 'DELETE' => array('DELETE', 2), + // Used for options. + '_OPTIONS' => array('_OPTIONS', 1), + 'FROM' => array('FROM', 3), + 'PARTITION' => array('PARTITION', 3), + 'WHERE' => array('WHERE', 3), + 'ORDER BY' => array('ORDER BY', 3), + 'LIMIT' => array('LIMIT', 3), + ); + + /** * Tables used as sources for this statement. * * @var FieldFragment[] diff --git a/libraries/sql-parser/src/Statements/RepairStatement.php b/libraries/sql-parser/src/Statements/RepairStatement.php index 933d2c0d08..e340dd12e0 100644 --- a/libraries/sql-parser/src/Statements/RepairStatement.php +++ b/libraries/sql-parser/src/Statements/RepairStatement.php @@ -42,11 +42,4 @@ class RepairStatement extends MaintenanceStatement 'EXTENDED' => 5, 'USE_FRM' => 6, ); - - /** - * Repaired tables. - * - * @var FieldFragment[] - */ - public $tables; } diff --git a/libraries/sql-parser/src/Statements/RestoreStatement.php b/libraries/sql-parser/src/Statements/RestoreStatement.php index 996b41eb0b..1c03e29df1 100644 --- a/libraries/sql-parser/src/Statements/RestoreStatement.php +++ b/libraries/sql-parser/src/Statements/RestoreStatement.php @@ -35,11 +35,4 @@ class RestoreStatement extends MaintenanceStatement 'FROM' => array(2, 'var'), ); - - /** - * Restored tables. - * - * @var FieldFragment[] - */ - public $tables; } diff --git a/libraries/sql-parser/src/Statements/SelectStatement.php b/libraries/sql-parser/src/Statements/SelectStatement.php index e376b47a0a..b5d0b0a840 100644 --- a/libraries/sql-parser/src/Statements/SelectStatement.php +++ b/libraries/sql-parser/src/Statements/SelectStatement.php @@ -78,7 +78,7 @@ class SelectStatement extends Statement 'SELECT' => array('SELECT', 2), // Used for options. '_OPTIONS' => array('_OPTIONS', 1), - // Used for select expressions. + // Used for selected expressions. '_SELECT' => array('SELECT', 1), 'FROM' => array('FROM', 3), 'PARTITION' => array('PARTITION', 3), diff --git a/libraries/sql-parser/src/Statements/TruncateStatement.php b/libraries/sql-parser/src/Statements/TruncateStatement.php new file mode 100644 index 0000000000..9313204b5e --- /dev/null +++ b/libraries/sql-parser/src/Statements/TruncateStatement.php @@ -0,0 +1,49 @@ +<?php + +/** + * `TRUNCATE` statement. + * + * @package SqlParser + * @subpackage Statements + */ +namespace SqlParser\Statements; + +use SqlParser\Parser; +use SqlParser\Statement; +use SqlParser\Token; +use SqlParser\TokensList; +use SqlParser\Fragments\ArrayFragment; +use SqlParser\Fragments\DataTypeFragment; +use SqlParser\Fragments\FieldDefFragment; +use SqlParser\Fragments\FieldFragment; +use SqlParser\Fragments\OptionsFragment; +use SqlParser\Fragments\ParamDefFragment; + +/** + * `TRUNCATE` statement. + * + * @category Statements + * @package SqlParser + * @subpackage Statements + * @author Dan Ungureanu <udan1107@gmail.com> + * @license http://opensource.org/licenses/GPL-2.0 GNU Public License + */ +class TruncateStatement extends Statement +{ + + /** + * Options for `TRUNCATE` statements. + * + * @var array + */ + public static $OPTIONS = array( + 'TABLE' => 1, + ); + + /** + * The name of the truncated table. + * + * @var FieldFragment + */ + public $table; +} diff --git a/libraries/sql-parser/src/Statements/UpdateStatement.php b/libraries/sql-parser/src/Statements/UpdateStatement.php index d100560b30..ab445dc34a 100644 --- a/libraries/sql-parser/src/Statements/UpdateStatement.php +++ b/libraries/sql-parser/src/Statements/UpdateStatement.php @@ -45,11 +45,30 @@ class UpdateStatement extends Statement ); /** + * The clauses of this statement, in order. + * + * @see Statement::$CLAUSES + * + * @var array + */ + public static $CLAUSES = array( + 'UPDATE' => array('UPDATE', 2), + // Used for options. + '_OPTIONS' => array('_OPTIONS', 1), + // Used for updated tables. + '_UPDATE' => array('UPDATE', 1), + 'SET' => array('SET', 3), + 'WHERE' => array('WHERE', 3), + 'ORDER BY' => array('ORDER BY', 3), + 'LIMIT' => array('LIMIT', 3), + ); + + /** * Tables used as sources for this statement. * * @var FieldFragment[] */ - public $from; + public $tables; /** * The updated values. diff --git a/libraries/sql-parser/src/TokensList.php b/libraries/sql-parser/src/TokensList.php index 229bebcec4..be9124c0de 100644 --- a/libraries/sql-parser/src/TokensList.php +++ b/libraries/sql-parser/src/TokensList.php @@ -57,15 +57,20 @@ class TokensList implements \ArrayAccess /** * Builds an array of tokens by merging their raw value. * - * @param array $tokens + * @param string|array|TokensList $tokens * * @return string */ public static function build($list) { + if (is_string($list)) { + return $list; + } + if ($list instanceof TokensList) { $list = $list->tokens; } + $ret = ''; if (is_array($list)) { foreach ($list as $tok) { diff --git a/libraries/sql-parser/src/Utils/Query.php b/libraries/sql-parser/src/Utils/Query.php index 5c4553d2ab..d4c01afa64 100644 --- a/libraries/sql-parser/src/Utils/Query.php +++ b/libraries/sql-parser/src/Utils/Query.php @@ -12,6 +12,7 @@ use SqlParser\Lexer; use SqlParser\Parser; use SqlParser\Statement; use SqlParser\Token; +use SqlParser\Fragments\FieldFragment; use SqlParser\Statements\AlterStatement; use SqlParser\Statements\AnalyzeStatement; use SqlParser\Statements\CallStatement; @@ -23,10 +24,12 @@ use SqlParser\Statements\DropStatement; use SqlParser\Statements\ExplainStatement; use SqlParser\Statements\InsertStatement; use SqlParser\Statements\OptimizeStatement; +use SqlParser\Statements\RenameStatement; use SqlParser\Statements\RepairStatement; use SqlParser\Statements\ReplaceStatement; use SqlParser\Statements\SelectStatement; use SqlParser\Statements\ShowStatement; +use SqlParser\Statements\TruncateStatement; use SqlParser\Statements\UpdateStatement; /** @@ -437,6 +440,54 @@ class Query } /** + * Gets a list of all tables used in this statement. + * + * @param Statement $statement Statement to be scanned. + * + * @return array + */ + public static function getTables($statement) + { + $fields = array(); + + if (($statement instanceof InsertStatement) + || ($statement instanceof ReplaceStatement) + ) { + $fields = array($statement->into->dest); + } elseif ($statement instanceof UpdateStatement) { + $fields = $statement->tables; + } elseif (($statement instanceof SelectStatement) + || ($statement instanceof DeleteStatement) + ) { + $fields = $statement->from; + } elseif (($statement instanceof AlterStatement) + || ($statement instanceof TruncateStatement) + ) { + $fields = array($statement->table); + } elseif ($statement instanceof DropStatement) { + if (!$statement->options->has('TABLE')) { + // No tables are dropped. + return array(); + } + $fields = $statement->fields; + } elseif ($statement instanceof RenameStatement) { + foreach ($statement->renames as $rename) { + $fields[] = $rename->old; + } + } + + $ret = array(); + foreach ($fields as $field) { + if (!empty($field->table)) { + $field->expr = null; // Force rebuild. + $field->alias = null; // Aliases are not required. + $ret[] = FieldFragment::build($field); + } + } + return $ret; + } + + /** * Gets a specific clause. * * @param Statement $statement The parsed query that has to be modified. diff --git a/test/libraries/PMA_import_test.php b/test/libraries/PMA_import_test.php index d47e11caa6..96472e08e2 100644 --- a/test/libraries/PMA_import_test.php +++ b/test/libraries/PMA_import_test.php @@ -329,32 +329,6 @@ class PMA_Import_Test extends PHPUnit_Framework_TestCase } /** - * Test for PMA_getTableReferences - * - * @return void - */ - function testPMAGetTableReferences() - { - $sql_query = 'UPDATE `table_1` AS t1, `table_2` t2, `table_3` AS t3 ' - . 'SET `table_1`.`id` = `table_2`.`id` ' - . 'WHERE 1'; - - $parsed_sql = PMA_SQP_parse($sql_query); - $analyzed_sql = PMA_SQP_analyze($parsed_sql); - $analyzed_sql_results = array( - 'parsed_sql' => $parsed_sql, - 'analyzed_sql' => $analyzed_sql - ); - - $table_references = PMA_getTableReferences($analyzed_sql_results); - - $this->assertEquals( - ' `table_1` AS t1 , `table_2` t2 , `table_3` AS t3', - $table_references - ); - } - - /** * Test for PMA_getMatchedRows. * * @return void @@ -370,10 +344,11 @@ class PMA_Import_Test extends PHPUnit_Framework_TestCase $update_query = 'UPDATE `table_1` ' . 'SET `id` = 20 ' . 'WHERE `id` > 10'; - $simulated_update_query = 'SELECT `id` FROM `table_1` WHERE `id` > 10 '; + $simulated_update_query = 'SELECT `id` FROM `table_1` WHERE `id` > 10 AND (`id` <> 20)'; + $delete_query = 'DELETE FROM `table_1` ' . 'WHERE `id` > 10'; - $simulated_delete_query = 'SELECT * FROM `table_1` WHERE `id` > 10 '; + $simulated_delete_query = 'SELECT * FROM `table_1` WHERE `id` > 10'; $dbi->expects($this->any()) ->method('numRows') @@ -411,11 +386,11 @@ class PMA_Import_Test extends PHPUnit_Framework_TestCase */ function simulatedQueryTest($sql_query, $simulated_query) { - $parsed_sql = PMA_SQP_parse($sql_query); - $analyzed_sql = PMA_SQP_analyze($parsed_sql); + $parser = new SqlParser\Parser($sql_query); $analyzed_sql_results = array( - 'parsed_sql' => $parsed_sql, - 'analyzed_sql' => $analyzed_sql + 'query' => $sql_query, + 'parser' => $parser, + 'statement' => $parser->statements[0], ); $simulated_data = PMA_getMatchedRows($analyzed_sql_results); @@ -430,7 +405,7 @@ class PMA_Import_Test extends PHPUnit_Framework_TestCase $this->assertEquals( array( 'sql_query' => PMA_Util::formatSql( - $analyzed_sql_results['parsed_sql']['raw'] + $analyzed_sql_results['query'] ), 'matched_rows' => 2, 'matched_rows_url' => $matched_rows_url |