diff options
| -rw-r--r-- | src/Contexts/ContextMySql50000.php | 1 | ||||
| -rw-r--r-- | src/Contexts/ContextMySql50100.php | 1 | ||||
| -rw-r--r-- | src/Contexts/ContextMySql50500.php | 1 | ||||
| -rw-r--r-- | src/Contexts/ContextMySql50600.php | 1 | ||||
| -rw-r--r-- | src/Contexts/ContextMySql50700.php | 1 | ||||
| -rw-r--r-- | src/Utils/Query.php | 88 | ||||
| -rw-r--r-- | tests/data/parseRestore.out | 2 | ||||
| -rw-r--r-- | tests/utils/QueryTest.php | 88 |
8 files changed, 148 insertions, 35 deletions
diff --git a/src/Contexts/ContextMySql50000.php b/src/Contexts/ContextMySql50000.php index d4b80e1..eedb59c 100644 --- a/src/Contexts/ContextMySql50000.php +++ b/src/Contexts/ContextMySql50000.php @@ -258,7 +258,6 @@ class ContextMySql50000 extends Context 'CURRENT_TIMESTAMP' => 35, 'NOT IN' => 37, - 'PROCEDURE ANALYSE' => 37, 'DATE' => 41, 'TIME' => 41, 'YEAR' => 41, 'TIMESTAMP' => 41, diff --git a/src/Contexts/ContextMySql50100.php b/src/Contexts/ContextMySql50100.php index c6db3d5..5f70976 100644 --- a/src/Contexts/ContextMySql50100.php +++ b/src/Contexts/ContextMySql50100.php @@ -280,7 +280,6 @@ class ContextMySql50100 extends Context 'CURRENT_TIMESTAMP' => 35, 'NOT IN' => 37, - 'PROCEDURE ANALYSE' => 37, 'DATE' => 41, 'TIME' => 41, 'YEAR' => 41, 'TIMESTAMP' => 41, diff --git a/src/Contexts/ContextMySql50500.php b/src/Contexts/ContextMySql50500.php index 26aa40e..db53f5e 100644 --- a/src/Contexts/ContextMySql50500.php +++ b/src/Contexts/ContextMySql50500.php @@ -284,7 +284,6 @@ class ContextMySql50500 extends Context 'CURRENT_TIMESTAMP' => 35, 'NOT IN' => 37, - 'PROCEDURE ANALYSE' => 37, 'DATE' => 41, 'TIME' => 41, 'YEAR' => 41, 'TIMESTAMP' => 41, diff --git a/src/Contexts/ContextMySql50600.php b/src/Contexts/ContextMySql50600.php index 39521ef..48fd6b2 100644 --- a/src/Contexts/ContextMySql50600.php +++ b/src/Contexts/ContextMySql50600.php @@ -315,7 +315,6 @@ class ContextMySql50600 extends Context 'CURRENT_TIMESTAMP' => 35, 'NOT IN' => 37, - 'PROCEDURE ANALYSE' => 37, 'DATE' => 41, 'TIME' => 41, 'YEAR' => 41, 'TIMESTAMP' => 41, diff --git a/src/Contexts/ContextMySql50700.php b/src/Contexts/ContextMySql50700.php index 99dae9e..ac55a6c 100644 --- a/src/Contexts/ContextMySql50700.php +++ b/src/Contexts/ContextMySql50700.php @@ -327,7 +327,6 @@ class ContextMySql50700 extends Context 'CURRENT_TIMESTAMP' => 35, 'NOT IN' => 37, - 'PROCEDURE ANALYSE' => 37, 'DATE' => 41, 'TIME' => 41, 'YEAR' => 41, 'TIMESTAMP' => 41, diff --git a/src/Utils/Query.php b/src/Utils/Query.php index 0bd56d8..c308344 100644 --- a/src/Utils/Query.php +++ b/src/Utils/Query.php @@ -2,10 +2,11 @@ namespace SqlParser\Utils; +use SqlParser\Parser; use SqlParser\Statement; use SqlParser\Statements\AlterStatement; use SqlParser\Statements\AnalyzeStatement; -use SqlParser\Statement\CallStatement; +use SqlParser\Statements\CallStatement; use SqlParser\Statements\CheckStatement; use SqlParser\Statements\ChecksumStatement; use SqlParser\Statements\CreateStatement; @@ -45,29 +46,54 @@ class Query * Gets an array with flags this statement has. * * @param Statement $statement + * @param bool $all If `false`, false values will not be included. * * @return array */ - public static function getFlags($statement) + public static function getFlags($statement, $all = false) { $flags = array(); - // TODO: 'union', 'join', 'offset' + if ($all) { + $flags = array( + 'distinct' => false, + 'drop_database' => false, + 'is_affected' => false, + 'is_analyse' => false, + 'is_count' => false, + 'is_delete' => false, + 'is_explain' => false, + 'is_export' => false, + 'is_func' => false, + 'is_group' => false, + 'is_insert' => false, + 'is_maint' => false, + 'is_procedure' => false, + 'is_replace' => false, + 'is_select' => false, + 'is_show' => false, + 'is_subquery' => false, + 'join' => false, + 'offset' => false, + 'reload' => false, + 'select_from' => false, + 'union' => false + ); + } + // TODO: 'union', 'join', 'offset' if (($statement instanceof AlterStatement) || ($statement instanceof CreateStatement) ) { $flags['reload'] = true; - } else if ($statement instanceof AnalyzeStatement) { - $flags['is_maint'] = true; - $flags['is_analyze'] = true; - } else if ($statement instanceof CallStatement) { - $flags['is_procedure'] = true; - } else if (($statement instanceof CheckStatement) + } else if (($statement instanceof AnalyzeStatement) + || ($statement instanceof CheckStatement) || ($statement instanceof ChecksumStatement) || ($statement instanceof OptimizeStatement) || ($statement instanceof RepairStatement) ) { $flags['is_maint'] = true; + } else if ($statement instanceof CallStatement) { + $flags['is_procedure'] = true; } else if ($statement instanceof DeleteStatement) { $flags['is_delete'] = true; $flags['is_affected'] = true; @@ -88,6 +114,7 @@ class Query $flags['is_affected'] = true; $flags['is_replace'] = true; } else if ($statement instanceof SelectStatement) { + $flags['is_select'] = true; if (!empty($statement->from)) { $flags['select_from'] = true; @@ -119,6 +146,12 @@ class Query $flags['is_subquery'] = true; } } + + if ((!empty($statement->procedure)) + && ($statement->procedure->name === 'ANALYSE') + ) { + $flags['is_analyse'] = true; + } } else if ($statement instanceof ShowStatement) { $flags['is_show'] = true; } else if ($statement instanceof UpdateStatement) { @@ -128,4 +161,41 @@ class Query return $flags; } + /** + * Parses a query and gets all information about it. + * + * @param string $query + * + * @return array + */ + public static function getAll($query) + { + $parser = new Parser($query); + + if (!isset($parser->statements[0])) { + return array(); + } + + $statement = $parser->statements[0]; + + $ret = static::getFlags($statement, true); + + $ret['parser'] = $parser; + $ret['statement'] = $statement; + + if ($statement instanceof SelectStatement) { + $ret['tables'] = array(); + foreach ($statement->expr as $expr) { + if (!empty($expr->table)) { + $ret['tables'][] = array( + $expr->table, + !empty($expr->database) ? $expr->database : null + ); + } + } + } + + return $ret; + } + } diff --git a/tests/data/parseRestore.out b/tests/data/parseRestore.out index 13c230c..4ef83c8 100644 --- a/tests/data/parseRestore.out +++ b/tests/data/parseRestore.out @@ -1 +1 @@ -a:2:{s:6:"parser";O:16:"SqlParser\Parser":4:{s:4:"list";O:20:"SqlParser\TokensList":3:{s:6:"tokens";a:10:{i:0;O:15:"SqlParser\Token":5:{s:5:"token";s:7:"RESTORE";s:5:"value";s:7:"RESTORE";s:4:"type";i:1;s:5:"flags";i:1;s:8:"position";i:0;}i:1;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:7;}i:2;O:15:"SqlParser\Token":5:{s:5:"token";s:5:"TABLE";s:5:"value";s:5:"TABLE";s:4:"type";i:1;s:5:"flags";i:3;s:8:"position";i:8;}i:3;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:13;}i:4;O:15:"SqlParser\Token":5:{s:5:"token";s:8:"my_table";s:5:"value";s:8:"my_table";s:4:"type";i:0;s:5:"flags";i:0;s:8:"position";i:14;}i:5;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:22;}i:6;O:15:"SqlParser\Token":5:{s:5:"token";s:4:"FROM";s:5:"value";s:4:"FROM";s:4:"type";i:1;s:5:"flags";i:3;s:8:"position";i:23;}i:7;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:27;}i:8;O:15:"SqlParser\Token":5:{s:5:"token";s:27:""/path/to/backup/directory"";s:5:"value";s:25:"/path/to/backup/directory";s:4:"type";i:7;s:5:"flags";i:2;s:8:"position";i:28;}i:9;O:15:"SqlParser\Token":5:{s:5:"token";N;s:5:"value";N;s:4:"type";i:9;s:5:"flags";i:0;s:8:"position";N;}}s:5:"count";i:10;s:3:"idx";i:10;}s:6:"strict";b:0;s:6:"errors";a:0:{}s:10:"statements";a:1:{i:0;O:37:"SqlParser\Statements\RestoreStatement":4:{s:6:"tables";a:1:{i:0;O:33:"SqlParser\Fragments\FieldFragment":7:{s:8:"database";N;s:5:"table";s:8:"my_table";s:6:"column";N;s:4:"expr";s:8:"my_table";s:5:"alias";N;s:8:"function";N;s:8:"subquery";N;}}s:5:"first";N;s:4:"last";i:8;s:7:"options";O:35:"SqlParser\Fragments\OptionsFragment":1:{s:7:"options";a:2:{i:0;s:5:"TABLE";i:1;a:2:{s:4:"name";s:4:"FROM";s:5:"value";s:25:"/path/to/backup/directory";}}}}}}s:6:"errors";a:0:{}}
\ No newline at end of file +a:2:{s:6:"parser";O:16:"SqlParser\Parser":4:{s:4:"list";O:20:"SqlParser\TokensList":3:{s:6:"tokens";a:10:{i:0;O:15:"SqlParser\Token":5:{s:5:"token";s:7:"RESTORE";s:5:"value";s:7:"RESTORE";s:4:"type";i:1;s:5:"flags";i:1;s:8:"position";i:0;}i:1;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:7;}i:2;O:15:"SqlParser\Token":5:{s:5:"token";s:5:"TABLE";s:5:"value";s:5:"TABLE";s:4:"type";i:1;s:5:"flags";i:3;s:8:"position";i:8;}i:3;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:13;}i:4;O:15:"SqlParser\Token":5:{s:5:"token";s:8:"my_table";s:5:"value";s:8:"my_table";s:4:"type";i:0;s:5:"flags";i:0;s:8:"position";i:14;}i:5;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:22;}i:6;O:15:"SqlParser\Token":5:{s:5:"token";s:4:"FROM";s:5:"value";s:4:"FROM";s:4:"type";i:1;s:5:"flags";i:3;s:8:"position";i:23;}i:7;O:15:"SqlParser\Token":5:{s:5:"token";s:1:" ";s:5:"value";s:1:" ";s:4:"type";i:3;s:5:"flags";i:0;s:8:"position";i:27;}i:8;O:15:"SqlParser\Token":5:{s:5:"token";s:27:""/path/to/backup/directory"";s:5:"value";s:25:"/path/to/backup/directory";s:4:"type";i:7;s:5:"flags";i:2;s:8:"position";i:28;}i:9;O:15:"SqlParser\Token":5:{s:5:"token";N;s:5:"value";N;s:4:"type";i:9;s:5:"flags";i:0;s:8:"position";N;}}s:5:"count";i:10;s:3:"idx";i:10;}s:6:"strict";b:0;s:6:"errors";a:0:{}s:10:"statements";a:1:{i:0;O:37:"SqlParser\Statements\RestoreStatement":4:{s:7:"options";O:35:"SqlParser\Fragments\OptionsFragment":1:{s:7:"options";a:2:{i:0;s:5:"TABLE";i:1;a:2:{s:4:"name";s:4:"FROM";s:5:"value";s:25:"/path/to/backup/directory";}}}s:6:"tables";a:1:{i:0;O:33:"SqlParser\Fragments\FieldFragment":7:{s:8:"database";N;s:5:"table";s:8:"my_table";s:6:"column";N;s:4:"expr";s:8:"my_table";s:5:"alias";N;s:8:"function";N;s:8:"subquery";N;}}s:5:"first";N;s:4:"last";i:8;}}}s:6:"errors";a:0:{}}
\ No newline at end of file diff --git a/tests/utils/QueryTest.php b/tests/utils/QueryTest.php index 021339e..b933649 100644 --- a/tests/utils/QueryTest.php +++ b/tests/utils/QueryTest.php @@ -30,15 +30,12 @@ class QueryTest extends TestCase array('reload' => true) ), array( - 'CREATE TABLE tbl (id INT)', - array('reload' => true) + 'CALL test()', + array('is_procedure' => true) ), array( - 'ANALYZE TABLE tbl', - array( - 'is_maint' => true, - 'is_analyze' => true - ) + 'CREATE TABLE tbl (id INT)', + array('reload' => true) ), array( 'CHECK TABLE tbl', @@ -47,8 +44,8 @@ class QueryTest extends TestCase array( 'DELETE FROM tbl', array( - 'is_delete' => true, - 'is_affected' => true + 'is_affected' => true, + 'is_delete' => true ), ), array( @@ -58,8 +55,8 @@ class QueryTest extends TestCase array( 'DROP DATABASE db', array( - 'reload' => true, - 'drop_database' => true + 'drop_database' => true, + 'reload' => true ) ), array( @@ -82,37 +79,62 @@ class QueryTest extends TestCase ), array( 'SELECT 1', - array() + array('is_select' => true) ), array( 'SELECT * FROM tbl', - array('select_from' => true) + array( + 'is_select' => true, + 'select_from' => true + ) ), array( 'SELECT DISTINCT * FROM tbl', array( - 'select_from' => true, - 'distinct' => true + 'distinct' => true, + 'is_select' => true, + 'select_from' => true + ) + ), + array( + 'SELECT * FROM actor GROUP BY actor_id', + array( + 'is_group' => true, + 'is_select' => true, + 'select_from' => true + ) + ), + array( + 'SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);', + array( + 'is_analyse' => true, + 'is_select' => true, + 'select_from' => true ) ), array( 'SELECT * FROM tbl INTO OUTFILE "/tmp/export.txt"', array( - 'select_from' => true, - 'is_export' => true + 'is_export' => true, + 'is_select' => true, + 'select_from' => true ) ), array( 'SELECT COUNT(id), SUM(id) FROM tbl', array( - 'select_from' => true, + 'is_count' => true, 'is_func' => true, - 'is_count' => true + 'is_select' => true, + 'select_from' => true ) ), array( 'SELECT (SELECT "foo")', - array('is_subquery' => true) + array( + 'is_select' => true, + 'is_subquery' => true + ) ), array( 'SHOW CREATE TABLE tbl', @@ -125,4 +147,30 @@ class QueryTest extends TestCase ); } + public function testGetAll() + { + $query = 'SELECT *, actor.actor_id, sakila2.film.* + FROM sakila2.city, sakila2.film, actor'; + $parser = new Parser($query); + $this->assertEquals( + array_merge( + Query::getFlags($parser->statements[0], true), + array( + 'parser' => $parser, + 'statement' => $parser->statements[0], + 'tables' => array( + array('actor', null), + array('film', 'sakila2') + ) + ) + ), + Query::getAll($query) + ); + } + + public function testGetAllEmpty() + { + $this->assertEquals(array(), Query::getAll('')); + } + } |