QueryTest::testGetClause() - Code Metrics - Inspection of "366c169c53272926f629490c8e5a20dcfebee2a7" - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — master (366c16)

by William

created 2020-08-16 13:39 UTC

QueryTest::testGetClause() B

↳ Parent: QueryTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	102
Code Lines	73

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	1	Features	0

Metric	Value
cc	1
eloc	73
c	2
b	1
f	0
nc	1
nop	0
dl	0
loc	102
rs	8.589

How to fix Long Method

<?php

declare(strict_types=1);

namespace PhpMyAdmin\SqlParser\Tests\Utils;

use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Tests\TestCase;
use PhpMyAdmin\SqlParser\Utils\Query;
use function array_merge;

class QueryTest extends TestCase
{
    /**
     * @param mixed $query
     * @param mixed $expected
     *
     * @dataProvider getFlagsProvider
     */
    public function testGetFlags($query, $expected)
    {
        $parser = new Parser($query);
        $this->assertEquals(
            $expected,
            Query::getFlags($parser->statements[0])
        );
    }

    public function getFlagsProvider()
    {
        return [
            [
                'ALTER TABLE DROP col',
                [
                    'reload' => true,
                    'querytype' => 'ALTER',
                ],
            ],
            [
                'CALL test()',
                [
                    'is_procedure' => true,
                    'querytype' => 'CALL',
                ],
            ],
            [
                'CREATE TABLE tbl (id INT)',
                [
                    'reload' => true,
                    'querytype' => 'CREATE',
                ],
            ],
            [
                'CHECK TABLE tbl',
                [
                    'is_maint' => true,
                    'querytype' => 'CHECK',
                ],
            ],
            [
                'DELETE FROM tbl',
                [
                    'is_affected' => true,
                    'is_delete' => true,
                    'querytype' => 'DELETE',
                ],
            ],
            [
                'DROP VIEW v',
                [
                    'reload' => true,
                    'querytype' => 'DROP',
                ],
            ],
            [
                'DROP DATABASE db',
                [
                    'drop_database' => true,
                    'reload' => true,
                    'querytype' => 'DROP',
                ],
            ],
            [
                'EXPLAIN tbl',
                [
                    'is_explain' => true,
                    'querytype' => 'EXPLAIN',
                ],
            ],
            [
                'LOAD DATA INFILE \'/tmp/test.txt\' INTO TABLE test',
                [
                    'is_affected' => true,
                    'is_insert' => true,
                    'querytype' => 'LOAD',
                ],
            ],
            [
                'INSERT INTO tbl VALUES (1)',
                [
                    'is_affected' => true,
                    'is_insert' => true,
                    'querytype' => 'INSERT',
                ],
            ],
            [
                'REPLACE INTO tbl VALUES (2)',
                [
                    'is_affected' => true,
                    'is_replace' => true,
                    'is_insert' => true,
                    'querytype' => 'REPLACE',
                ],
            ],
            [
                'SELECT 1',
                [
                    'is_select' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM tbl',
                [
                    'is_select' => true,
                    'select_from' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT DISTINCT * FROM tbl LIMIT 0, 10 ORDER BY id',
                [
                    'distinct' => true,
                    'is_select' => true,
                    'select_from' => true,
                    'limit' => true,
                    'order' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM actor GROUP BY actor_id',
                [
                    'is_group' => true,
                    'is_select' => true,
                    'select_from' => true,
                    'group' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);',
                [
                    'is_analyse' => true,
                    'is_select' => true,
                    'select_from' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM tbl INTO OUTFILE "/tmp/export.txt"',
                [
                    'is_export' => true,
                    'is_select' => true,
                    'select_from' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT COUNT(id), SUM(id) FROM tbl',
                [
                    'is_count' => true,
                    'is_func' => true,
                    'is_select' => true,
                    'select_from' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT (SELECT "foo")',
                [
                    'is_select' => true,
                    'is_subquery' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM customer HAVING store_id = 2;',
                [
                    'is_select' => true,
                    'select_from' => true,
                    'is_group' => true,
                    'having' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;',
                [
                    'is_select' => true,
                    'select_from' => true,
                    'join' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SHOW CREATE TABLE tbl',
                [
                    'is_show' => true,
                    'querytype' => 'SHOW',
                ],
            ],
            [
                'UPDATE tbl SET id = 1',
                [
                    'is_affected' => true,
                    'querytype' => 'UPDATE',
                ],
            ],
            [
                'ANALYZE TABLE tbl',
                [
                    'is_maint' => true,
                    'querytype' => 'ANALYZE',
                ],
            ],
            [
                'CHECKSUM TABLE tbl',
                [
                    'is_maint' => true,
                    'querytype' => 'CHECKSUM',
                ],
            ],
            [
                'OPTIMIZE TABLE tbl',
                [
                    'is_maint' => true,
                    'querytype' => 'OPTIMIZE',
                ],
            ],
            [
                'REPAIR TABLE tbl',
                [
                    'is_maint' => true,
                    'querytype' => 'REPAIR',
                ],
            ],
            [
                '(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) ' .
                'UNION ' .
                '(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);',
                [
                    'is_select' => true,
                    'select_from' => true,
                    'limit' => true,
                    'order' => true,
                    'union' => true,
                    'querytype' => 'SELECT',
                ],
            ],
            [
                'SELECT * FROM orders AS ord WHERE 1',
                [
                    'querytype' => 'SELECT',
                    'is_select' => true,
                    'select_from' => true,
                ],
            ],
            [
                'SET NAMES \'latin\'',
                ['querytype' => 'SET'],
            ],
        ];
    }

    public function testGetAll()
    {
        $this->assertEquals(
            [
                'distinct' => false,
                'drop_database' => false,
                'group' => false,
                'having' => 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,
                'limit' => false,
                'offset' => false,
                'order' => false,
                'querytype' => false,
                'reload' => false,
                'select_from' => false,
                'union' => false,
            ],
            Query::getAll('')
        );

        $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),
                [
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => ['*'],
                    'select_tables' => [
                        [
                            'actor',
                            null,
                        ],
                        [
                            'film',
                            'sakila2',
                        ],
                    ],
                ]
            ),
            Query::getAll($query)
        );

        $query = 'SELECT * FROM sakila.actor, film';
        $parser = new Parser($query);
        $this->assertEquals(
            array_merge(
                Query::getFlags($parser->statements[0], true),
                [
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => ['*'],
                    'select_tables' => [
                        [
                            'actor',
                            'sakila',
                        ],
                        [
                            'film',
                            null,
                        ],
                    ],
                ]
            ),
            Query::getAll($query)
        );

        $query = 'SELECT a.actor_id FROM sakila.actor AS a, film';
        $parser = new Parser($query);
        $this->assertEquals(
            array_merge(
                Query::getFlags($parser->statements[0], true),
                [
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => [],
                    'select_tables' => [
                        [
                            'actor',
                            'sakila',
                        ],
                    ],
                ]
            ),
            Query::getAll($query)
        );

        $query = 'SELECT CASE WHEN 2 IS NULL THEN "this is true" ELSE "this is false" END';
        $parser = new Parser($query);
        $this->assertEquals(
            array_merge(
                Query::getFlags($parser->statements[0], true),
                [
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => ['CASE WHEN 2 IS NULL THEN "this is true" ELSE "this is false" END'],
                    'select_tables' => [],
                ]
            ),
            Query::getAll($query)
        );
    }

    /**
     * @param mixed $query
     * @param mixed $expected
     *
     * @dataProvider getTablesProvider
     */
    public function testGetTables($query, $expected)
    {
        $parser = new Parser($query);
        $this->assertEquals(
            $expected,
            Query::getTables($parser->statements[0])
        );
    }

    public function getTablesProvider()
    {
        return [
            [
                'INSERT INTO tbl(`id`, `name`) VALUES (1, "Name")',
                ['`tbl`'],
            ],
            [
                'INSERT INTO 0tbl(`id`, `name`) VALUES (1, "Name")',
                ['`0tbl`'],
            ],
            [
                'UPDATE tbl SET id = 0',
                ['`tbl`'],
            ],
            [
                'UPDATE 0tbl SET id = 0',
                ['`0tbl`'],
            ],
            [
                'DELETE FROM tbl WHERE id < 10',
                ['`tbl`'],
            ],
            [
                'DELETE FROM 0tbl WHERE id < 10',
                ['`0tbl`'],
            ],
            [
                'TRUNCATE tbl',
                ['`tbl`'],
            ],
            [
                'DROP VIEW v',
                [],
            ],
            [
                'DROP TABLE tbl1, tbl2',
                [
                    '`tbl1`',
                    '`tbl2`',
                ],
            ],
            [
                'RENAME TABLE a TO b, c TO d',
                [
                    '`a`',
                    '`c`',
                ],
            ],
        ];
    }

    public function testGetClause()
    {
        /* Assertion 1 */
        $parser = new Parser(
            'SELECT c.city_id, c.country_id ' .
            'FROM `city` ' .
            'WHERE city_id < 1 /* test */' .
            'ORDER BY city_id ASC ' .
            'LIMIT 0, 1 ' .
            'INTO OUTFILE "/dev/null"'
        );
        $this->assertEquals(
            '0, 1 INTO OUTFILE "/dev/null"',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'LIMIT',
                0
            )
        );
        // Assert it returns all clauses between FROM and LIMIT
        $this->assertEquals(
            'WHERE city_id < 1 ORDER BY city_id ASC',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'FROM',
                'LIMIT'
            )
        );
        // Assert it returns all clauses between SELECT and LIMIT
        $this->assertEquals(
            'FROM `city` WHERE city_id < 1 ORDER BY city_id ASC',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'LIMIT',
                'SELECT'
            )
        );

        /* Assertion 2 */
        $parser = new Parser(
            'DELETE FROM `renewal` ' .
            'WHERE number = "1DB" AND actionDate <= CURRENT_DATE() ' .
            'ORDER BY id ASC ' .
            'LIMIT 1'
        );
        $this->assertEquals(
            'number = "1DB" AND actionDate <= CURRENT_DATE()',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'WHERE'
            )
        );
        $this->assertEquals(
            '1',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'LIMIT'
            )
        );
        $this->assertEquals(
            'id ASC',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'ORDER BY'
            )
        );

        /* Assertion 3 */
        $parser = new Parser(
            'UPDATE `renewal` SET `some_column` = 1 ' .
            'WHERE number = "1DB" AND actionDate <= CURRENT_DATE() ' .
            'ORDER BY id ASC ' .
            'LIMIT 1'
        );
        $this->assertEquals(
            'number = "1DB" AND actionDate <= CURRENT_DATE()',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'WHERE'
            )
        );
        $this->assertEquals(
            '1',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'LIMIT'
            )
        );
        $this->assertEquals(
            'id ASC',
            Query::getClause(
                $parser->statements[0],
                $parser->list,
                'ORDER BY'
            )
        );
    }

    public function testReplaceClause()
    {
        $parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10;');
        $this->assertEquals(
            'SELECT *, (SELECT 1) FROM film WHERE film_id > 0 LIMIT 0, 10',
            Query::replaceClause(
                $parser->statements[0],
                $parser->list,
                'WHERE film_id > 0'
            )
        );

        $parser = new Parser(
            'select supplier.city, supplier.id from supplier '
            . 'union select customer.city, customer.id from customer'
        );
        $this->assertEquals(
            'select supplier.city, supplier.id from supplier '
            . 'union select customer.city, customer.id from customer'
            . ' ORDER BY city ',
            Query::replaceClause(
                $parser->statements[0],
                $parser->list,
                'ORDER BY city'
            )
        );
    }

    public function testReplaceClauseOnlyKeyword()
    {
        $parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10');
        $this->assertEquals(
            ' SELECT SQL_CALC_FOUND_ROWS *, (SELECT 1) FROM film LIMIT 0, 10',
            Query::replaceClause(
                $parser->statements[0],
                $parser->list,
                'SELECT SQL_CALC_FOUND_ROWS',
                null,
                true
            )
        );
    }

    public function testReplaceNonExistingPart()
    {
        $parser = new Parser('ALTER TABLE `sale_mast` OPTIMIZE PARTITION p3');
        $this->assertEquals(
            '  ALTER TABLE `sale_mast` OPTIMIZE PARTITION p3',
            Query::replaceClause(
                $parser->statements[0],
                $parser->list,
                'ORDER BY',
                ''
            )
        );
    }

    public function testReplaceClauses()
    {
        $this->assertEquals('', Query::replaceClauses(null, null, []));

        $parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10;');
        $this->assertEquals(
            'SELECT *, (SELECT 1) FROM film WHERE film_id > 0 LIMIT 0, 10',
            Query::replaceClauses(
                $parser->statements[0],
                $parser->list,
                [
                    [
                        'WHERE',
                        'WHERE film_id > 0',
                    ],
                ]
            )
        );

        $parser = new Parser(
            'SELECT c.city_id, c.country_id ' .
            'INTO OUTFILE "/dev/null" ' .
            'FROM `city` ' .
            'WHERE city_id < 1 ' .
            'ORDER BY city_id ASC ' .
            'LIMIT 0, 1 '
        );
        $this->assertEquals(
            'SELECT c.city_id, c.country_id ' .
            'INTO OUTFILE "/dev/null" ' .
            'FROM city AS c   ' .
            'ORDER BY city_id ASC ' .
            'LIMIT 0, 10 ',
            Query::replaceClauses(
                $parser->statements[0],
                $parser->list,
                [
                    [
                        'FROM',
                        'FROM city AS c',
                    ],
                    [
                        'WHERE',
                        '',
                    ],
                    [
                        'LIMIT',
                        'LIMIT 0, 10',
                    ],
                ]
            )
        );
    }

    public function testGetFirstStatement()
    {
        $query = 'USE saki';
        $delimiter = null;
        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertNull($statement);
        $this->assertEquals('USE saki', $query);

        $query = 'USE sakila; ' .
            '/*test comment*/' .
            'SELECT * FROM actor; ' .
            'DELIMITER $$ ' .
            'UPDATE actor SET last_name = "abc"$$' .
            '/*!SELECT * FROM actor WHERE last_name = "abc"*/$$';
        $delimiter = null;

        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertEquals('USE sakila;', $statement);

        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertEquals('SELECT * FROM actor;', $statement);

        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertEquals('DELIMITER $$', $statement);
        $this->assertEquals('$$', $delimiter);

        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertEquals('UPDATE actor SET last_name = "abc"$$', $statement);

        [$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
        $this->assertEquals('SELECT * FROM actor WHERE last_name = "abc"$$', $statement);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpMyAdmin\SqlParser\Tests\Utils;
6
7			use PhpMyAdmin\SqlParser\Parser;
8			use PhpMyAdmin\SqlParser\Tests\TestCase;
9			use PhpMyAdmin\SqlParser\Utils\Query;
10			use function array_merge;
11
12			class QueryTest extends TestCase
13			{
14			/**
15			* @param mixed $query
16			* @param mixed $expected
17			*
18			* @dataProvider getFlagsProvider
19			*/
20			public function testGetFlags($query, $expected)
21			{
22			$parser = new Parser($query);
23			$this->assertEquals(
24			$expected,
25			Query::getFlags($parser->statements[0])
26			);
27			}
28
29			public function getFlagsProvider()
30			{
31			return [
32			[
33			'ALTER TABLE DROP col',
34			[
35			'reload' => true,
36			'querytype' => 'ALTER',
37			],
38			],
39			[
40			'CALL test()',
41			[
42			'is_procedure' => true,
43			'querytype' => 'CALL',
44			],
45			],
46			[
47			'CREATE TABLE tbl (id INT)',
48			[
49			'reload' => true,
50			'querytype' => 'CREATE',
51			],
52			],
53			[
54			'CHECK TABLE tbl',
55			[
56			'is_maint' => true,
57			'querytype' => 'CHECK',
58			],
59			],
60			[
61			'DELETE FROM tbl',
62			[
63			'is_affected' => true,
64			'is_delete' => true,
65			'querytype' => 'DELETE',
66			],
67			],
68			[
69			'DROP VIEW v',
70			[
71			'reload' => true,
72			'querytype' => 'DROP',
73			],
74			],
75			[
76			'DROP DATABASE db',
77			[
78			'drop_database' => true,
79			'reload' => true,
80			'querytype' => 'DROP',
81			],
82			],
83			[
84			'EXPLAIN tbl',
85			[
86			'is_explain' => true,
87			'querytype' => 'EXPLAIN',
88			],
89			],
90			[
91			'LOAD DATA INFILE \'/tmp/test.txt\' INTO TABLE test',
92			[
93			'is_affected' => true,
94			'is_insert' => true,
95			'querytype' => 'LOAD',
96			],
97			],
98			[
99			'INSERT INTO tbl VALUES (1)',
100			[
101			'is_affected' => true,
102			'is_insert' => true,
103			'querytype' => 'INSERT',
104			],
105			],
106			[
107			'REPLACE INTO tbl VALUES (2)',
108			[
109			'is_affected' => true,
110			'is_replace' => true,
111			'is_insert' => true,
112			'querytype' => 'REPLACE',
113			],
114			],
115			[
116			'SELECT 1',
117			[
118			'is_select' => true,
119			'querytype' => 'SELECT',
120			],
121			],
122			[
123			'SELECT * FROM tbl',
124			[
125			'is_select' => true,
126			'select_from' => true,
127			'querytype' => 'SELECT',
128			],
129			],
130			[
131			'SELECT DISTINCT * FROM tbl LIMIT 0, 10 ORDER BY id',
132			[
133			'distinct' => true,
134			'is_select' => true,
135			'select_from' => true,
136			'limit' => true,
137			'order' => true,
138			'querytype' => 'SELECT',
139			],
140			],
141			[
142			'SELECT * FROM actor GROUP BY actor_id',
143			[
144			'is_group' => true,
145			'is_select' => true,
146			'select_from' => true,
147			'group' => true,
148			'querytype' => 'SELECT',
149			],
150			],
151			[
152			'SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);',
153			[
154			'is_analyse' => true,
155			'is_select' => true,
156			'select_from' => true,
157			'querytype' => 'SELECT',
158			],
159			],
160			[
161			'SELECT * FROM tbl INTO OUTFILE "/tmp/export.txt"',
162			[
163			'is_export' => true,
164			'is_select' => true,
165			'select_from' => true,
166			'querytype' => 'SELECT',
167			],
168			],
169			[
170			'SELECT COUNT(id), SUM(id) FROM tbl',
171			[
172			'is_count' => true,
173			'is_func' => true,
174			'is_select' => true,
175			'select_from' => true,
176			'querytype' => 'SELECT',
177			],
178			],
179			[
180			'SELECT (SELECT "foo")',
181			[
182			'is_select' => true,
183			'is_subquery' => true,
184			'querytype' => 'SELECT',
185			],
186			],
187			[
188			'SELECT * FROM customer HAVING store_id = 2;',
189			[
190			'is_select' => true,
191			'select_from' => true,
192			'is_group' => true,
193			'having' => true,
194			'querytype' => 'SELECT',
195			],
196			],
197			[
198			'SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;',
199			[
200			'is_select' => true,
201			'select_from' => true,
202			'join' => true,
203			'querytype' => 'SELECT',
204			],
205			],
206			[
207			'SHOW CREATE TABLE tbl',
208			[
209			'is_show' => true,
210			'querytype' => 'SHOW',
211			],
212			],
213			[
214			'UPDATE tbl SET id = 1',
215			[
216			'is_affected' => true,
217			'querytype' => 'UPDATE',
218			],
219			],
220			[
221			'ANALYZE TABLE tbl',
222			[
223			'is_maint' => true,
224			'querytype' => 'ANALYZE',
225			],
226			],
227			[
228			'CHECKSUM TABLE tbl',
229			[
230			'is_maint' => true,
231			'querytype' => 'CHECKSUM',
232			],
233			],
234			[
235			'OPTIMIZE TABLE tbl',
236			[
237			'is_maint' => true,
238			'querytype' => 'OPTIMIZE',
239			],
240			],
241			[
242			'REPAIR TABLE tbl',
243			[
244			'is_maint' => true,
245			'querytype' => 'REPAIR',
246			],
247			],
248			[
249			'(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) ' .
250			'UNION ' .
251			'(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);',
252			[
253			'is_select' => true,
254			'select_from' => true,
255			'limit' => true,
256			'order' => true,
257			'union' => true,
258			'querytype' => 'SELECT',
259			],
260			],
261			[
262			'SELECT * FROM orders AS ord WHERE 1',
263			[
264			'querytype' => 'SELECT',
265			'is_select' => true,
266			'select_from' => true,
267			],
268			],
269			[
270			'SET NAMES \'latin\'',
271			['querytype' => 'SET'],
272			],
273			];
274			}
275
276			public function testGetAll()
277			{
278			$this->assertEquals(
279			[
280			'distinct' => false,
281			'drop_database' => false,
282			'group' => false,
283			'having' => false,
284			'is_affected' => false,
285			'is_analyse' => false,
286			'is_count' => false,
287			'is_delete' => false,
288			'is_explain' => false,
289			'is_export' => false,
290			'is_func' => false,
291			'is_group' => false,
292			'is_insert' => false,
293			'is_maint' => false,
294			'is_procedure' => false,
295			'is_replace' => false,
296			'is_select' => false,
297			'is_show' => false,
298			'is_subquery' => false,
299			'join' => false,
300			'limit' => false,
301			'offset' => false,
302			'order' => false,
303			'querytype' => false,
304			'reload' => false,
305			'select_from' => false,
306			'union' => false,
307			],
308			Query::getAll('')
309			);
310
311			$query = 'SELECT , actor.actor_id, sakila2.film.
312			FROM sakila2.city, sakila2.film, actor';
313			$parser = new Parser($query);
314			$this->assertEquals(
315			array_merge(
316			Query::getFlags($parser->statements[0], true),
317			[
318			'parser' => $parser,
319			'statement' => $parser->statements[0],
320			'select_expr' => ['*'],
321			'select_tables' => [
322			[
323			'actor',
324			null,
325			],
326			[
327			'film',
328			'sakila2',
329			],
330			],
331			]
332			),
333			Query::getAll($query)
334			);
335
336			$query = 'SELECT * FROM sakila.actor, film';
337			$parser = new Parser($query);
338			$this->assertEquals(
339			array_merge(
340			Query::getFlags($parser->statements[0], true),
341			[
342			'parser' => $parser,
343			'statement' => $parser->statements[0],
344			'select_expr' => ['*'],
345			'select_tables' => [
346			[
347			'actor',
348			'sakila',
349			],
350			[
351			'film',
352			null,
353			],
354			],
355			]
356			),
357			Query::getAll($query)
358			);
359
360			$query = 'SELECT a.actor_id FROM sakila.actor AS a, film';
361			$parser = new Parser($query);
362			$this->assertEquals(
363			array_merge(
364			Query::getFlags($parser->statements[0], true),
365			[
366			'parser' => $parser,
367			'statement' => $parser->statements[0],
368			'select_expr' => [],
369			'select_tables' => [
370			[
371			'actor',
372			'sakila',
373			],
374			],
375			]
376			),
377			Query::getAll($query)
378			);
379
380			$query = 'SELECT CASE WHEN 2 IS NULL THEN "this is true" ELSE "this is false" END';
381			$parser = new Parser($query);
382			$this->assertEquals(
383			array_merge(
384			Query::getFlags($parser->statements[0], true),
385			[
386			'parser' => $parser,
387			'statement' => $parser->statements[0],
388			'select_expr' => ['CASE WHEN 2 IS NULL THEN "this is true" ELSE "this is false" END'],
389			'select_tables' => [],
390			]
391			),
392			Query::getAll($query)
393			);
394			}
395
396			/**
397			* @param mixed $query
398			* @param mixed $expected
399			*
400			* @dataProvider getTablesProvider
401			*/
402			public function testGetTables($query, $expected)
403			{
404			$parser = new Parser($query);
405			$this->assertEquals(
406			$expected,
407			Query::getTables($parser->statements[0])
408			);
409			}
410
411			public function getTablesProvider()
412			{
413			return [
414			[
415			'INSERT INTO tbl(`id`, `name`) VALUES (1, "Name")',
416			['`tbl`'],
417			],
418			[
419			'INSERT INTO 0tbl(`id`, `name`) VALUES (1, "Name")',
420			['`0tbl`'],
421			],
422			[
423			'UPDATE tbl SET id = 0',
424			['`tbl`'],
425			],
426			[
427			'UPDATE 0tbl SET id = 0',
428			['`0tbl`'],
429			],
430			[
431			'DELETE FROM tbl WHERE id < 10',
432			['`tbl`'],
433			],
434			[
435			'DELETE FROM 0tbl WHERE id < 10',
436			['`0tbl`'],
437			],
438			[
439			'TRUNCATE tbl',
440			['`tbl`'],
441			],
442			[
443			'DROP VIEW v',
444			[],
445			],
446			[
447			'DROP TABLE tbl1, tbl2',
448			[
449			'`tbl1`',
450			'`tbl2`',
451			],
452			],
453			[
454			'RENAME TABLE a TO b, c TO d',
455			[
456			'`a`',
457			'`c`',
458			],
459			],
460			];
461			}
462
463			public function testGetClause()
464			{
465			/* Assertion 1 */
466			$parser = new Parser(
467			'SELECT c.city_id, c.country_id ' .
468			'FROM `city` ' .
469			'WHERE city_id < 1 /* test */' .
470			'ORDER BY city_id ASC ' .
471			'LIMIT 0, 1 ' .
472			'INTO OUTFILE "/dev/null"'
473			);
474			$this->assertEquals(
475			'0, 1 INTO OUTFILE "/dev/null"',
476			Query::getClause(
477			$parser->statements[0],
478			$parser->list,
479			'LIMIT',
480			0
481			)
482			);
483			// Assert it returns all clauses between FROM and LIMIT
484			$this->assertEquals(
485			'WHERE city_id < 1 ORDER BY city_id ASC',
486			Query::getClause(
487			$parser->statements[0],
488			$parser->list,
489			'FROM',
490			'LIMIT'
491			)
492			);
493			// Assert it returns all clauses between SELECT and LIMIT
494			$this->assertEquals(
495			'FROM `city` WHERE city_id < 1 ORDER BY city_id ASC',
496			Query::getClause(
497			$parser->statements[0],
498			$parser->list,
499			'LIMIT',
500			'SELECT'
501			)
502			);
503
504			/* Assertion 2 */
505			$parser = new Parser(
506			'DELETE FROM `renewal` ' .
507			'WHERE number = "1DB" AND actionDate <= CURRENT_DATE() ' .
508			'ORDER BY id ASC ' .
509			'LIMIT 1'
510			);
511			$this->assertEquals(
512			'number = "1DB" AND actionDate <= CURRENT_DATE()',
513			Query::getClause(
514			$parser->statements[0],
515			$parser->list,
516			'WHERE'
517			)
518			);
519			$this->assertEquals(
520			'1',
521			Query::getClause(
522			$parser->statements[0],
523			$parser->list,
524			'LIMIT'
525			)
526			);
527			$this->assertEquals(
528			'id ASC',
529			Query::getClause(
530			$parser->statements[0],
531			$parser->list,
532			'ORDER BY'
533			)
534			);
535
536			/* Assertion 3 */
537			$parser = new Parser(
538			'UPDATE `renewal` SET `some_column` = 1 ' .
539			'WHERE number = "1DB" AND actionDate <= CURRENT_DATE() ' .
540			'ORDER BY id ASC ' .
541			'LIMIT 1'
542			);
543			$this->assertEquals(
544			'number = "1DB" AND actionDate <= CURRENT_DATE()',
545			Query::getClause(
546			$parser->statements[0],
547			$parser->list,
548			'WHERE'
549			)
550			);
551			$this->assertEquals(
552			'1',
553			Query::getClause(
554			$parser->statements[0],
555			$parser->list,
556			'LIMIT'
557			)
558			);
559			$this->assertEquals(
560			'id ASC',
561			Query::getClause(
562			$parser->statements[0],
563			$parser->list,
564			'ORDER BY'
565			)
566			);
567			}
568
569			public function testReplaceClause()
570			{
571			$parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10;');
572			$this->assertEquals(
573			'SELECT *, (SELECT 1) FROM film WHERE film_id > 0 LIMIT 0, 10',
574			Query::replaceClause(
575			$parser->statements[0],
576			$parser->list,
577			'WHERE film_id > 0'
578			)
579			);
580
581			$parser = new Parser(
582			'select supplier.city, supplier.id from supplier '
583			. 'union select customer.city, customer.id from customer'
584			);
585			$this->assertEquals(
586			'select supplier.city, supplier.id from supplier '
587			. 'union select customer.city, customer.id from customer'
588			. ' ORDER BY city ',
589			Query::replaceClause(
590			$parser->statements[0],
591			$parser->list,
592			'ORDER BY city'
593			)
594			);
595			}
596
597			public function testReplaceClauseOnlyKeyword()
598			{
599			$parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10');
600			$this->assertEquals(
601			' SELECT SQL_CALC_FOUND_ROWS *, (SELECT 1) FROM film LIMIT 0, 10',
602			Query::replaceClause(
603			$parser->statements[0],
604			$parser->list,
605			'SELECT SQL_CALC_FOUND_ROWS',
606			null,
607			true
608			)
609			);
610			}
611
612			public function testReplaceNonExistingPart()
613			{
614			$parser = new Parser('ALTER TABLE `sale_mast` OPTIMIZE PARTITION p3');
615			$this->assertEquals(
616			' ALTER TABLE `sale_mast` OPTIMIZE PARTITION p3',
617			Query::replaceClause(
618			$parser->statements[0],
619			$parser->list,
620			'ORDER BY',
621			''
622			)
623			);
624			}
625
626			public function testReplaceClauses()
627			{
628			$this->assertEquals('', Query::replaceClauses(null, null, []));
629
630			$parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10;');
631			$this->assertEquals(
632			'SELECT *, (SELECT 1) FROM film WHERE film_id > 0 LIMIT 0, 10',
633			Query::replaceClauses(
634			$parser->statements[0],
635			$parser->list,
636			[
637			[
638			'WHERE',
639			'WHERE film_id > 0',
640			],
641			]
642			)
643			);
644
645			$parser = new Parser(
646			'SELECT c.city_id, c.country_id ' .
647			'INTO OUTFILE "/dev/null" ' .
648			'FROM `city` ' .
649			'WHERE city_id < 1 ' .
650			'ORDER BY city_id ASC ' .
651			'LIMIT 0, 1 '
652			);
653			$this->assertEquals(
654			'SELECT c.city_id, c.country_id ' .
655			'INTO OUTFILE "/dev/null" ' .
656			'FROM city AS c ' .
657			'ORDER BY city_id ASC ' .
658			'LIMIT 0, 10 ',
659			Query::replaceClauses(
660			$parser->statements[0],
661			$parser->list,
662			[
663			[
664			'FROM',
665			'FROM city AS c',
666			],
667			[
668			'WHERE',
669			'',
670			],
671			[
672			'LIMIT',
673			'LIMIT 0, 10',
674			],
675			]
676			)
677			);
678			}
679
680			public function testGetFirstStatement()
681			{
682			$query = 'USE saki';
683			$delimiter = null;
684			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
685			$this->assertNull($statement);
686			$this->assertEquals('USE saki', $query);
687
688			$query = 'USE sakila; ' .
689			'/test comment/' .
690			'SELECT * FROM actor; ' .
691			'DELIMITER $$ ' .
692			'UPDATE actor SET last_name = "abc"$$' .
693			'/!SELECT FROM actor WHERE last_name = "abc"*/$$';
694			$delimiter = null;
695
696			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
697			$this->assertEquals('USE sakila;', $statement);
698
699			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
700			$this->assertEquals('SELECT * FROM actor;', $statement);
701
702			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
703			$this->assertEquals('DELIMITER $$', $statement);
704			$this->assertEquals('$$', $delimiter);
705
706			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
707			$this->assertEquals('UPDATE actor SET last_name = "abc"$$', $statement);
708
709			[$statement, $query, $delimiter] = Query::getFirstStatement($query, $delimiter);
710			$this->assertEquals('SELECT * FROM actor WHERE last_name = "abc"$$', $statement);
711			}
712			}
713

phpmyadmin / sql-parser

Branch — master (366c16)

QueryTest::testGetClause() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like