Skip to content

Commit 4ab1342

Browse files
udan11udan11
committed
Improved query's utilities.
Fixed an issue in contexts. Updated tests.
1 parent 1254db4 commit 4ab1342

8 files changed

Lines changed: 148 additions & 35 deletions

File tree

‎src/Contexts/ContextMySql50000.php‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -258,7 +258,6 @@ class ContextMySql50000 extends Context
258258
'CURRENT_TIMESTAMP' => 35,
259259

260260
'NOT IN' => 37,
261-
'PROCEDURE ANALYSE' => 37,
262261

263262
'DATE' => 41, 'TIME' => 41, 'YEAR' => 41,
264263
'TIMESTAMP' => 41,

‎src/Contexts/ContextMySql50100.php‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -280,7 +280,6 @@ class ContextMySql50100 extends Context
280280
'CURRENT_TIMESTAMP' => 35,
281281

282282
'NOT IN' => 37,
283-
'PROCEDURE ANALYSE' => 37,
284283

285284
'DATE' => 41, 'TIME' => 41, 'YEAR' => 41,
286285
'TIMESTAMP' => 41,

‎src/Contexts/ContextMySql50500.php‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -284,7 +284,6 @@ class ContextMySql50500 extends Context
284284
'CURRENT_TIMESTAMP' => 35,
285285

286286
'NOT IN' => 37,
287-
'PROCEDURE ANALYSE' => 37,
288287

289288
'DATE' => 41, 'TIME' => 41, 'YEAR' => 41,
290289
'TIMESTAMP' => 41,

‎src/Contexts/ContextMySql50600.php‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -315,7 +315,6 @@ class ContextMySql50600 extends Context
315315
'CURRENT_TIMESTAMP' => 35,
316316

317317
'NOT IN' => 37,
318-
'PROCEDURE ANALYSE' => 37,
319318

320319
'DATE' => 41, 'TIME' => 41, 'YEAR' => 41,
321320
'TIMESTAMP' => 41,

‎src/Contexts/ContextMySql50700.php‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -327,7 +327,6 @@ class ContextMySql50700 extends Context
327327
'CURRENT_TIMESTAMP' => 35,
328328

329329
'NOT IN' => 37,
330-
'PROCEDURE ANALYSE' => 37,
331330

332331
'DATE' => 41, 'TIME' => 41, 'YEAR' => 41,
333332
'TIMESTAMP' => 41,

‎src/Utils/Query.php‎

Lines changed: 79 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2,10 +2,11 @@
22

33
namespace SqlParser\Utils;
44

5+
use SqlParser\Parser;
56
use SqlParser\Statement;
67
use SqlParser\Statements\AlterStatement;
78
use SqlParser\Statements\AnalyzeStatement;
8-
use SqlParser\Statement\CallStatement;
9+
use SqlParser\Statements\CallStatement;
910
use SqlParser\Statements\CheckStatement;
1011
use SqlParser\Statements\ChecksumStatement;
1112
use SqlParser\Statements\CreateStatement;
@@ -45,29 +46,54 @@ class Query
4546
* Gets an array with flags this statement has.
4647
*
4748
* @param Statement $statement
49+
* @param bool $all If `false`, false values will not be included.
4850
*
4951
* @return array
5052
*/
51-
public static function getFlags($statement)
53+
public static function getFlags($statement, $all = false)
5254
{
5355
$flags = array();
54-
// TODO: 'union', 'join', 'offset'
56+
if ($all) {
57+
$flags = array(
58+
'distinct' => false,
59+
'drop_database' => false,
60+
'is_affected' => false,
61+
'is_analyse' => false,
62+
'is_count' => false,
63+
'is_delete' => false,
64+
'is_explain' => false,
65+
'is_export' => false,
66+
'is_func' => false,
67+
'is_group' => false,
68+
'is_insert' => false,
69+
'is_maint' => false,
70+
'is_procedure' => false,
71+
'is_replace' => false,
72+
'is_select' => false,
73+
'is_show' => false,
74+
'is_subquery' => false,
75+
'join' => false,
76+
'offset' => false,
77+
'reload' => false,
78+
'select_from' => false,
79+
'union' => false
80+
);
81+
}
5582

83+
// TODO: 'union', 'join', 'offset'
5684
if (($statement instanceof AlterStatement)
5785
|| ($statement instanceof CreateStatement)
5886
) {
5987
$flags['reload'] = true;
60-
} else if ($statement instanceof AnalyzeStatement) {
61-
$flags['is_maint'] = true;
62-
$flags['is_analyze'] = true;
63-
} else if ($statement instanceof CallStatement) {
64-
$flags['is_procedure'] = true;
65-
} else if (($statement instanceof CheckStatement)
88+
} else if (($statement instanceof AnalyzeStatement)
89+
|| ($statement instanceof CheckStatement)
6690
|| ($statement instanceof ChecksumStatement)
6791
|| ($statement instanceof OptimizeStatement)
6892
|| ($statement instanceof RepairStatement)
6993
) {
7094
$flags['is_maint'] = true;
95+
} else if ($statement instanceof CallStatement) {
96+
$flags['is_procedure'] = true;
7197
} else if ($statement instanceof DeleteStatement) {
7298
$flags['is_delete'] = true;
7399
$flags['is_affected'] = true;
@@ -88,6 +114,7 @@ public static function getFlags($statement)
88114
$flags['is_affected'] = true;
89115
$flags['is_replace'] = true;
90116
} else if ($statement instanceof SelectStatement) {
117+
$flags['is_select'] = true;
91118

92119
if (!empty($statement->from)) {
93120
$flags['select_from'] = true;
@@ -119,6 +146,12 @@ public static function getFlags($statement)
119146
$flags['is_subquery'] = true;
120147
}
121148
}
149+
150+
if ((!empty($statement->procedure))
151+
&& ($statement->procedure->name === 'ANALYSE')
152+
) {
153+
$flags['is_analyse'] = true;
154+
}
122155
} else if ($statement instanceof ShowStatement) {
123156
$flags['is_show'] = true;
124157
} else if ($statement instanceof UpdateStatement) {
@@ -128,4 +161,41 @@ public static function getFlags($statement)
128161
return $flags;
129162
}
130163

164+
/**
165+
* Parses a query and gets all information about it.
166+
*
167+
* @param string $query
168+
*
169+
* @return array
170+
*/
171+
public static function getAll($query)
172+
{
173+
$parser = new Parser($query);
174+
175+
if (!isset($parser->statements[0])) {
176+
return array();
177+
}
178+
179+
$statement = $parser->statements[0];
180+
181+
$ret = static::getFlags($statement, true);
182+
183+
$ret['parser'] = $parser;
184+
$ret['statement'] = $statement;
185+
186+
if ($statement instanceof SelectStatement) {
187+
$ret['tables'] = array();
188+
foreach ($statement->expr as $expr) {
189+
if (!empty($expr->table)) {
190+
$ret['tables'][] = array(
191+
$expr->table,
192+
!empty($expr->database) ? $expr->database : null
193+
);
194+
}
195+
}
196+
}
197+
198+
return $ret;
199+
}
200+
131201
}

‎tests/data/parseRestore.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +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:{}}
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: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:{}}

‎tests/utils/QueryTest.php‎

Lines changed: 68 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -30,15 +30,12 @@ public function testGetFlagsProvider()
3030
array('reload' => true)
3131
),
3232
array(
33-
'CREATE TABLE tbl (id INT)',
34-
array('reload' => true)
33+
'CALL test()',
34+
array('is_procedure' => true)
3535
),
3636
array(
37-
'ANALYZE TABLE tbl',
38-
array(
39-
'is_maint' => true,
40-
'is_analyze' => true
41-
)
37+
'CREATE TABLE tbl (id INT)',
38+
array('reload' => true)
4239
),
4340
array(
4441
'CHECK TABLE tbl',
@@ -47,8 +44,8 @@ public function testGetFlagsProvider()
4744
array(
4845
'DELETE FROM tbl',
4946
array(
50-
'is_delete' => true,
51-
'is_affected' => true
47+
'is_affected' => true,
48+
'is_delete' => true
5249
),
5350
),
5451
array(
@@ -58,8 +55,8 @@ public function testGetFlagsProvider()
5855
array(
5956
'DROP DATABASE db',
6057
array(
61-
'reload' => true,
62-
'drop_database' => true
58+
'drop_database' => true,
59+
'reload' => true
6360
)
6461
),
6562
array(
@@ -82,37 +79,62 @@ public function testGetFlagsProvider()
8279
),
8380
array(
8481
'SELECT 1',
85-
array()
82+
array('is_select' => true)
8683
),
8784
array(
8885
'SELECT * FROM tbl',
89-
array('select_from' => true)
86+
array(
87+
'is_select' => true,
88+
'select_from' => true
89+
)
9090
),
9191
array(
9292
'SELECT DISTINCT * FROM tbl',
9393
array(
94-
'select_from' => true,
95-
'distinct' => true
94+
'distinct' => true,
95+
'is_select' => true,
96+
'select_from' => true
97+
)
98+
),
99+
array(
100+
'SELECT * FROM actor GROUP BY actor_id',
101+
array(
102+
'is_group' => true,
103+
'is_select' => true,
104+
'select_from' => true
105+
)
106+
),
107+
array(
108+
'SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);',
109+
array(
110+
'is_analyse' => true,
111+
'is_select' => true,
112+
'select_from' => true
96113
)
97114
),
98115
array(
99116
'SELECT * FROM tbl INTO OUTFILE "/tmp/export.txt"',
100117
array(
101-
'select_from' => true,
102-
'is_export' => true
118+
'is_export' => true,
119+
'is_select' => true,
120+
'select_from' => true
103121
)
104122
),
105123
array(
106124
'SELECT COUNT(id), SUM(id) FROM tbl',
107125
array(
108-
'select_from' => true,
126+
'is_count' => true,
109127
'is_func' => true,
110-
'is_count' => true
128+
'is_select' => true,
129+
'select_from' => true
111130
)
112131
),
113132
array(
114133
'SELECT (SELECT "foo")',
115-
array('is_subquery' => true)
134+
array(
135+
'is_select' => true,
136+
'is_subquery' => true
137+
)
116138
),
117139
array(
118140
'SHOW CREATE TABLE tbl',
@@ -125,4 +147,30 @@ public function testGetFlagsProvider()
125147
);
126148
}
127149

150+
public function testGetAll()
151+
{
152+
$query = 'SELECT *, actor.actor_id, sakila2.film.*
153+
FROM sakila2.city, sakila2.film, actor';
154+
$parser = new Parser($query);
155+
$this->assertEquals(
156+
array_merge(
157+
Query::getFlags($parser->statements[0], true),
158+
array(
159+
'parser' => $parser,
160+
'statement' => $parser->statements[0],
161+
'tables' => array(
162+
array('actor', null),
163+
array('film', 'sakila2')
164+
)
165+
)
166+
),
167+
Query::getAll($query)
168+
);
169+
}
170+
171+
public function testGetAllEmpty()
172+
{
173+
$this->assertEquals(array(), Query::getAll(''));
174+
}
175+
128176
}

0 commit comments

Comments
 (0)