QueryTest::testGetFirstStatement() - Code Metrics - Inspection of "GH-310: Adds select validation to create view" - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#311)

by William

created 2020-08-27 08:25 UTC

QueryTest::testGetFirstStatement() A

↳ Parent: QueryTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	37
Code Lines	29

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	29
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	37
rs	9.456

<?php

namespace PhpMyAdmin\SqlParser\Tests\Utils;

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

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

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

    public function testGetAll()
    {
        $this->assertEquals(
            array(
                '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),
                array(
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => array('*'),
                    'select_tables' => array(
                        array(
                            'actor',
                            null,
                        ),
                        array(
                            '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),
                array(
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => array('*'),
                    'select_tables' => array(
                        array(
                            'actor',
                            'sakila',
                        ),
                        array(
                            '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),
                array(
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => array(),
                    'select_tables' => array(
                        array(
                            '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),
                array(
                    'parser' => $parser,
                    'statement' => $parser->statements[0],
                    'select_expr' => array(
                        'CASE WHEN 2 IS NULL THEN "this is true" ELSE "this is false" END',
                    ),
                    'select_tables' => array(),
                )
            ),
            Query::getAll($query)
        );
    }

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

    public function getTablesProvider()
    {
        return array(
            array(
                'INSERT INTO tbl(`id`, `name`) VALUES (1, "Name")',
                array('`tbl`')
            ),
            array(
                'INSERT INTO 0tbl(`id`, `name`) VALUES (1, "Name")',
                array('`0tbl`')
            ),
            array(
                'UPDATE tbl SET id = 0',
                array('`tbl`')
            ),
            array(
                'UPDATE 0tbl SET id = 0',
                array('`0tbl`')
            ),
            array(
                'DELETE FROM tbl WHERE id < 10',
                array('`tbl`')
            ),
            array(
                'DELETE FROM 0tbl WHERE id < 10',
                array('`0tbl`')
            ),
            array(
                'TRUNCATE tbl',
                array('`tbl`')
            ),
            array(
                'DROP VIEW v',
                array()
            ),
            array(
                'DROP TABLE tbl1, tbl2',
                array(
                    '`tbl1`',
                    '`tbl2`',
                ),
            ),
            array(
                'RENAME TABLE a TO b, c TO d',
                array(
                    '`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, array()));

        $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,
                array(
                    array(
                        '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,
                array(
                    array(
                        'FROM',
                        'FROM city AS c',
                    ),
                    array(
                        'WHERE',
                        '',
                    ),
                    array(
                        'LIMIT',
                        'LIMIT 0, 10',
                    )
                )
            )
        );
    }

    public function testGetFirstStatement()
    {
        $query = 'USE saki';
        $delimiter = null;
        list($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;

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

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

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

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

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


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

phpmyadmin / sql-parser

Pull Request — master (#311)

QueryTest::testGetFirstStatement() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like