CreateStatementTest::testBuilderRoutine() - Code Metrics - Inspection of "Fixing BufferedQuery when has odd number of backsl..." - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#340)

unknown

created 2021-08-11 18:04 UTC

CreateStatementTest::testBuilderRoutine() A

↳ Parent: CreateStatementTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	20
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	16
nc	1
nop	0
dl	0
loc	20
rs	9.7333
c	0
b	0
f	0

<?php

namespace PhpMyAdmin\SqlParser\Tests\Builder;

use PhpMyAdmin\SqlParser\Components\CreateDefinition;
use PhpMyAdmin\SqlParser\Components\DataType;
use PhpMyAdmin\SqlParser\Components\Expression;
use PhpMyAdmin\SqlParser\Components\Key;
use PhpMyAdmin\SqlParser\Components\OptionsArray;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Statements\CreateStatement;
use PhpMyAdmin\SqlParser\Tests\TestCase;

class CreateStatementTest extends TestCase
{
    public function testBuilder()
    {
        $parser = new Parser(
            'CREATE USER "jeffrey"@"localhost" IDENTIFIED BY "mypass"'
        );
        $stmt = $parser->statements[0];
        $this->assertEquals(
            'CREATE USER "jeffrey"@"localhost" IDENTIFIED BY "mypass"',
            $stmt->build()
        );
    }

    public function testBuilderDatabase()
    {
        // CREATE DATABASE ...
        $parser = new Parser(
            'CREATE DATABASE `mydb` ' .
            'DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_general_ci'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE DATABASE `mydb` ' .
            'DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci',
            $stmt->build()
        );


        // CREATE SCHEMA ...
        $parser = new Parser(
            'CREATE SCHEMA `mydb` ' .
            'DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_general_ci'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE SCHEMA `mydb` ' .
            'DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci',
            $stmt->build()
        );
    }

    public function testBuilderDefaultInt()
    {
        $parser = new Parser(
            'CREATE TABLE IF NOT EXISTS t1 (' .
            " c1 int(11) NOT NULL DEFAULT '0' COMMENT 'xxx'" .
            ') ENGINE=MyISAM'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            "CREATE TABLE IF NOT EXISTS t1 (\n" .
            "  `c1` int(11) NOT NULL DEFAULT '0' COMMENT 'xxx'\n" .
            ') ENGINE=MyISAM',
            $stmt->build()
        );
    }

    public function testBuilderCollate()
    {
        $parser = new Parser(
            'CREATE TABLE IF NOT EXISTS t1 (' .
            " c1 varchar(11) NOT NULL DEFAULT '0' COLLATE 'utf8_czech_ci' COMMENT 'xxx'" .
            ') ENGINE=MyISAM'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            "CREATE TABLE IF NOT EXISTS t1 (\n" .
            "  `c1` varchar(11) NOT NULL DEFAULT '0' COLLATE 'utf8_czech_ci' COMMENT 'xxx'\n" .
            ') ENGINE=MyISAM',
            $stmt->build()
        );
    }

    public function testBuilderDefaultComment()
    {
        $parser = new Parser(
            'CREATE TABLE `wp_audio` (' .
            " `somedata` int(11) DEFAULT NULL COMMENT 'ma data', " .
            " `someinfo` int(11) DEFAULT NULL COMMENT 'ma info' " .
            ' )'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            "CREATE TABLE `wp_audio` (\n" .
            "  `somedata` int(11) DEFAULT NULL COMMENT 'ma data',\n" .
            "  `someinfo` int(11) DEFAULT NULL COMMENT 'ma info'\n" .
            ') ',
            $stmt->build()
        );
    }

    public function testBuilderTable()
    {
        /* Assertion 1 */
        $stmt = new CreateStatement();

        $stmt->name = new Expression('', 'test', '');
        $stmt->options = new OptionsArray(array('TABLE'));
        $stmt->fields = array(
            new CreateDefinition(
                'id',
                new OptionsArray(array('NOT NULL', 'AUTO_INCREMENT')),
                new DataType('INT', array(11), new OptionsArray(array('UNSIGNED')))
            ),
            new CreateDefinition(
                '',
                null,
                new Key('', array(array('name' => 'id')), 'PRIMARY KEY')
            )
        );

        $this->assertEquals(
            "CREATE TABLE `test` (\n" .
            "  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,\n" .
            "  PRIMARY KEY (`id`)\n" .
            ') ',
            $stmt->build()
        );

        /* Assertion 2 */
        $query =
            "CREATE TABLE `jos_core_acl_aro` (\n" .
            "  `id` int(11) NOT NULL,\n" .
            "  `section_value` varchar(240) NOT NULL DEFAULT '0',\n" .
            "  `value` varchar(240) NOT NULL DEFAULT '',\n" .
            "  `order_value` int(11) NOT NULL DEFAULT '0',\n" .
            "  `name` varchar(255) NOT NULL DEFAULT '',\n" .
            "  `hidden` int(11) NOT NULL DEFAULT '0',\n" .
            "  PRIMARY KEY (`id`),\n" .
            "  UNIQUE KEY `jos_section_value_value_aro` (`section_value`(100),`value`(15)) USING BTREE,\n" .
            "  KEY `jos_gacl_hidden_aro` (`hidden`)\n" .
            ') ENGINE=InnoDB DEFAULT CHARSET=latin1';
        $parser = new Parser($query);
        $this->assertEquals($query, $parser->statements[0]->build());

        /* Assertion 3 */
        $query = 'CREATE TABLE `table_copy` LIKE `old_table`';
        $parser = new Parser($query);
        $this->assertEquals($query, $parser->statements[0]->build());

        /* Assertion 4 */
        $query =
            "CREATE TABLE `aa` (\n" .
            "  `id` int(11) NOT NULL,\n" .
            "  `rTime` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000' ON UPDATE CURRENT_TIMESTAMP(3),\n" .
            "  PRIMARY KEY (`id`)\n" .
            ') ENGINE=InnoDB DEFAULT CHARSET=latin1';
        $parser = new Parser($query);
        $this->assertEquals($query, $parser->statements[0]->build());
    }

    public function testBuilderPartitions()
    {
        /* Assertion 1 */
        $query = 'CREATE TABLE ts (' . "\n"
            . '  `id` int,' . "\n"
            . '  `purchased` date' . "\n"
            . ') ' . "\n"
            . 'PARTITION BY RANGE(YEAR(purchased))' . "\n"
            . 'PARTITIONS 3' . "\n"
            . 'SUBPARTITION BY HASH(TO_DAYS(purchased))' . "\n"
            . 'SUBPARTITIONS 2' . "\n"
            . '(' . "\n"
            . 'PARTITION p0 VALUES LESS THAN (1990)  (' . "\n"
            . 'SUBPARTITION s0,' . "\n"
            . 'SUBPARTITION s1' . "\n"
            . '),' . "\n"
            . 'PARTITION p1 VALUES LESS THAN (2000)  (' . "\n"
            . 'SUBPARTITION s2,' . "\n"
            . 'SUBPARTITION s3' . "\n"
            . '),' . "\n"
            . 'PARTITION p2 VALUES LESS THAN MAXVALUE  (' . "\n"
            . 'SUBPARTITION s4,' . "\n"
            . 'SUBPARTITION s5' . "\n"
            . ')' . "\n"
            . ')';
        $parser = new Parser($query);
        $this->assertEquals($query, $parser->statements[0]->build());

        /* Assertion 2 */
        $query = 'CREATE TABLE `pma_test` (' . "\n"
            . '  `test_id` int(32) NOT NULL,' . "\n"
            . '  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP' . "\n"
            . ') ENGINE=InnoDB DEFAULT CHARSET=utf8' . "\n"
            . 'PARTITION BY RANGE (test_id)' . "\n"
            . '(' . "\n"
            . 'PARTITION p0 VALUES LESS THAN (250000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p1 VALUES LESS THAN (500000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p2 VALUES LESS THAN (750000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p3 VALUES LESS THAN (1000000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p4 VALUES LESS THAN (1250000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p5 VALUES LESS THAN (1500000) ENGINE=InnoDB,' . "\n"
            . 'PARTITION p6 VALUES LESS THAN MAXVALUE ENGINE=InnoDB' . "\n"
            . ')';
        $parser = new Parser($query);
        $this->assertEquals($query, $parser->statements[0]->build());
    }

    public function partitionQueries()
    {
        return array(
            array(
                'subparts' => <<<EOT
CREATE TABLE `ts` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
(
PARTITION p0 VALUES LESS THAN (1990)  (
SUBPARTITION s0 ENGINE=InnoDB,
SUBPARTITION s1 ENGINE=InnoDB
),
PARTITION p1 VALUES LESS THAN (2000)  (
SUBPARTITION s2 ENGINE=InnoDB,
SUBPARTITION s3 ENGINE=InnoDB
),
PARTITION p2 VALUES LESS THAN MAXVALUE  (
SUBPARTITION s4 ENGINE=InnoDB,
SUBPARTITION s5 ENGINE=InnoDB
)
)
EOT
            ),
            array(
                'parts' => <<<EOT
CREATE TABLE ptest (
  `event_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
PARTITION BY HASH (TO_DAYS(event_date))
(
PARTITION p0 ENGINE=InnoDB,
PARTITION p1 ENGINE=InnoDB,
PARTITION p2 ENGINE=InnoDB,
PARTITION p3 ENGINE=InnoDB,
PARTITION p4 ENGINE=InnoDB
)
EOT
            )
        );
    }

    /**
     * @dataProvider partitionQueries
     *
     * @param string $query
     */
    public function testBuilderPartitionsEngine($query)
    {
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

        $this->assertEquals($query, $stmt->build());
    }

    public function testBuilderView()
    {
        $parser = new Parser(
            'CREATE VIEW myView (vid, vfirstname) AS ' .
            'SELECT id, first_name FROM employee WHERE id = 1'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE VIEW myView (vid, vfirstname) AS  ' .
            'SELECT id, first_name FROM employee WHERE id = 1 ',
            $stmt->build()
        );

        $parser = new Parser(
            'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
            'SELECT id, first_name FROM employee WHERE id = 1'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS  ' .
            'SELECT id, first_name FROM employee WHERE id = 1 ',
            $stmt->build()
        );

        // Assert the builder can build wrong syntax select expressions
        $parser = new Parser(
            'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
            'SELECT id, first_name, FROMzz employee WHERE id = 1'
        );
        $stmt = $parser->statements[0];
        $this->assertEquals(
            'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS  ' .
            'SELECT id, first_name, FROMzz employee WHERE id = 1 ',
            $stmt->build()
        );
    }

    public function testBuilderTrigger()
    {
        $stmt = new CreateStatement();

        $stmt->options = new OptionsArray(array('TRIGGER'));
        $stmt->name = new Expression('ins_sum');
        $stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT'));
        $stmt->table = new Expression('account');
        $stmt->body = 'SET @sum = @sum + NEW.amount';

        $this->assertEquals(
            'CREATE TRIGGER ins_sum BEFORE INSERT ON account ' .
            'FOR EACH ROW SET @sum = @sum + NEW.amount',
            $stmt->build()
        );
    }

    public function testBuilderRoutine()
    {
        $parser = new Parser(
            'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
            'BEGIN ' .
            'DECLARE name VARCHAR DEFAULT ""; ' .
            'SELECT name INTO name FROM employees WHERE id = i; ' .
            'RETURN name; ' .
            'END'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
            'BEGIN ' .
            'DECLARE name VARCHAR DEFAULT ""; ' .
            'SELECT name INTO name FROM employees WHERE id = i; ' .
            'RETURN name; ' .
            'END',
            $stmt->build()
        );
    }

    public function testBuildSelect()
    {
        $parser = new Parser(
            'CREATE TABLE new_tbl SELECT * FROM orig_tbl'
        );
        $this->assertEquals(
            'CREATE TABLE new_tbl SELECT * FROM orig_tbl',
            $parser->statements[0]->build()
        );
    }
}


1			<?php
2
3			namespace PhpMyAdmin\SqlParser\Tests\Builder;
4
5			use PhpMyAdmin\SqlParser\Components\CreateDefinition;
6			use PhpMyAdmin\SqlParser\Components\DataType;
7			use PhpMyAdmin\SqlParser\Components\Expression;
8			use PhpMyAdmin\SqlParser\Components\Key;
9			use PhpMyAdmin\SqlParser\Components\OptionsArray;
10			use PhpMyAdmin\SqlParser\Parser;
11			use PhpMyAdmin\SqlParser\Statements\CreateStatement;
12			use PhpMyAdmin\SqlParser\Tests\TestCase;
13
14			class CreateStatementTest extends TestCase
15			{
16			public function testBuilder()
17			{
18			$parser = new Parser(
19			'CREATE USER "jeffrey"@"localhost" IDENTIFIED BY "mypass"'
20			);
21			$stmt = $parser->statements[0];
22			$this->assertEquals(
23			'CREATE USER "jeffrey"@"localhost" IDENTIFIED BY "mypass"',
24			$stmt->build()
25			);
26			}
27
28			public function testBuilderDatabase()
29			{
30			// CREATE DATABASE ...
31			$parser = new Parser(
32			'CREATE DATABASE `mydb` ' .
33			'DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_general_ci'
34			);
35			$stmt = $parser->statements[0];
36
37			$this->assertEquals(
38			'CREATE DATABASE `mydb` ' .
39			'DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci',
40			$stmt->build()
41			);
42
43
44			// CREATE SCHEMA ...
45			$parser = new Parser(
46			'CREATE SCHEMA `mydb` ' .
47			'DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_general_ci'
48			);
49			$stmt = $parser->statements[0];
50
51			$this->assertEquals(
52			'CREATE SCHEMA `mydb` ' .
53			'DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci',
54			$stmt->build()
55			);
56			}
57
58			public function testBuilderDefaultInt()
59			{
60			$parser = new Parser(
61			'CREATE TABLE IF NOT EXISTS t1 (' .
62			" c1 int(11) NOT NULL DEFAULT '0' COMMENT 'xxx'" .
63			') ENGINE=MyISAM'
64			);
65			$stmt = $parser->statements[0];
66
67			$this->assertEquals(
68			"CREATE TABLE IF NOT EXISTS t1 (\n" .
69			" `c1` int(11) NOT NULL DEFAULT '0' COMMENT 'xxx'\n" .
70			') ENGINE=MyISAM',
71			$stmt->build()
72			);
73			}
74
75			public function testBuilderCollate()
76			{
77			$parser = new Parser(
78			'CREATE TABLE IF NOT EXISTS t1 (' .
79			" c1 varchar(11) NOT NULL DEFAULT '0' COLLATE 'utf8_czech_ci' COMMENT 'xxx'" .
80			') ENGINE=MyISAM'
81			);
82			$stmt = $parser->statements[0];
83
84			$this->assertEquals(
85			"CREATE TABLE IF NOT EXISTS t1 (\n" .
86			" `c1` varchar(11) NOT NULL DEFAULT '0' COLLATE 'utf8_czech_ci' COMMENT 'xxx'\n" .
87			') ENGINE=MyISAM',
88			$stmt->build()
89			);
90			}
91
92			public function testBuilderDefaultComment()
93			{
94			$parser = new Parser(
95			'CREATE TABLE `wp_audio` (' .
96			" `somedata` int(11) DEFAULT NULL COMMENT 'ma data', " .
97			" `someinfo` int(11) DEFAULT NULL COMMENT 'ma info' " .
98			' )'
99			);
100			$stmt = $parser->statements[0];
101
102			$this->assertEquals(
103			"CREATE TABLE `wp_audio` (\n" .
104			" `somedata` int(11) DEFAULT NULL COMMENT 'ma data',\n" .
105			" `someinfo` int(11) DEFAULT NULL COMMENT 'ma info'\n" .
106			') ',
107			$stmt->build()
108			);
109			}
110
111			public function testBuilderTable()
112			{
113			/* Assertion 1 */
114			$stmt = new CreateStatement();
115
116			$stmt->name = new Expression('', 'test', '');
117			$stmt->options = new OptionsArray(array('TABLE'));
118			$stmt->fields = array(
119			new CreateDefinition(
120			'id',
121			new OptionsArray(array('NOT NULL', 'AUTO_INCREMENT')),
122			new DataType('INT', array(11), new OptionsArray(array('UNSIGNED')))
123			),
124			new CreateDefinition(
125			'',
126			null,
127			new Key('', array(array('name' => 'id')), 'PRIMARY KEY')
128			)
129			);
130
131			$this->assertEquals(
132			"CREATE TABLE `test` (\n" .
133			" `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,\n" .
134			" PRIMARY KEY (`id`)\n" .
135			') ',
136			$stmt->build()
137			);
138
139			/* Assertion 2 */
140			$query =
141			"CREATE TABLE `jos_core_acl_aro` (\n" .
142			" `id` int(11) NOT NULL,\n" .
143			" `section_value` varchar(240) NOT NULL DEFAULT '0',\n" .
144			" `value` varchar(240) NOT NULL DEFAULT '',\n" .
145			" `order_value` int(11) NOT NULL DEFAULT '0',\n" .
146			" `name` varchar(255) NOT NULL DEFAULT '',\n" .
147			" `hidden` int(11) NOT NULL DEFAULT '0',\n" .
148			" PRIMARY KEY (`id`),\n" .
149			" UNIQUE KEY `jos_section_value_value_aro` (`section_value`(100),`value`(15)) USING BTREE,\n" .
150			" KEY `jos_gacl_hidden_aro` (`hidden`)\n" .
151			') ENGINE=InnoDB DEFAULT CHARSET=latin1';
152			$parser = new Parser($query);
153			$this->assertEquals($query, $parser->statements[0]->build());
154
155			/* Assertion 3 */
156			$query = 'CREATE TABLE `table_copy` LIKE `old_table`';
157			$parser = new Parser($query);
158			$this->assertEquals($query, $parser->statements[0]->build());
159
160			/* Assertion 4 */
161			$query =
162			"CREATE TABLE `aa` (\n" .
163			" `id` int(11) NOT NULL,\n" .
164			" `rTime` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000' ON UPDATE CURRENT_TIMESTAMP(3),\n" .
165			" PRIMARY KEY (`id`)\n" .
166			') ENGINE=InnoDB DEFAULT CHARSET=latin1';
167			$parser = new Parser($query);
168			$this->assertEquals($query, $parser->statements[0]->build());
169			}
170
171			public function testBuilderPartitions()
172			{
173			/* Assertion 1 */
174			$query = 'CREATE TABLE ts (' . "\n"
175			. ' `id` int,' . "\n"
176			. ' `purchased` date' . "\n"
177			. ') ' . "\n"
178			. 'PARTITION BY RANGE(YEAR(purchased))' . "\n"
179			. 'PARTITIONS 3' . "\n"
180			. 'SUBPARTITION BY HASH(TO_DAYS(purchased))' . "\n"
181			. 'SUBPARTITIONS 2' . "\n"
182			. '(' . "\n"
183			. 'PARTITION p0 VALUES LESS THAN (1990) (' . "\n"
184			. 'SUBPARTITION s0,' . "\n"
185			. 'SUBPARTITION s1' . "\n"
186			. '),' . "\n"
187			. 'PARTITION p1 VALUES LESS THAN (2000) (' . "\n"
188			. 'SUBPARTITION s2,' . "\n"
189			. 'SUBPARTITION s3' . "\n"
190			. '),' . "\n"
191			. 'PARTITION p2 VALUES LESS THAN MAXVALUE (' . "\n"
192			. 'SUBPARTITION s4,' . "\n"
193			. 'SUBPARTITION s5' . "\n"
194			. ')' . "\n"
195			. ')';
196			$parser = new Parser($query);
197			$this->assertEquals($query, $parser->statements[0]->build());
198
199			/* Assertion 2 */
200			$query = 'CREATE TABLE `pma_test` (' . "\n"
201			. ' `test_id` int(32) NOT NULL,' . "\n"
202			. ' `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP' . "\n"
203			. ') ENGINE=InnoDB DEFAULT CHARSET=utf8' . "\n"
204			. 'PARTITION BY RANGE (test_id)' . "\n"
205			. '(' . "\n"
206			. 'PARTITION p0 VALUES LESS THAN (250000) ENGINE=InnoDB,' . "\n"
207			. 'PARTITION p1 VALUES LESS THAN (500000) ENGINE=InnoDB,' . "\n"
208			. 'PARTITION p2 VALUES LESS THAN (750000) ENGINE=InnoDB,' . "\n"
209			. 'PARTITION p3 VALUES LESS THAN (1000000) ENGINE=InnoDB,' . "\n"
210			. 'PARTITION p4 VALUES LESS THAN (1250000) ENGINE=InnoDB,' . "\n"
211			. 'PARTITION p5 VALUES LESS THAN (1500000) ENGINE=InnoDB,' . "\n"
212			. 'PARTITION p6 VALUES LESS THAN MAXVALUE ENGINE=InnoDB' . "\n"
213			. ')';
214			$parser = new Parser($query);
215			$this->assertEquals($query, $parser->statements[0]->build());
216			}
217
218			public function partitionQueries()
219			{
220			return array(
221			array(
222			'subparts' => <<<EOT
223			CREATE TABLE `ts` (
224			`id` int(11) DEFAULT NULL,
225			`purchased` date DEFAULT NULL
226			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
227			PARTITION BY RANGE (YEAR(purchased))
228			SUBPARTITION BY HASH (TO_DAYS(purchased))
229			(
230			PARTITION p0 VALUES LESS THAN (1990) (
231			SUBPARTITION s0 ENGINE=InnoDB,
232			SUBPARTITION s1 ENGINE=InnoDB
233			),
234			PARTITION p1 VALUES LESS THAN (2000) (
235			SUBPARTITION s2 ENGINE=InnoDB,
236			SUBPARTITION s3 ENGINE=InnoDB
237			),
238			PARTITION p2 VALUES LESS THAN MAXVALUE (
239			SUBPARTITION s4 ENGINE=InnoDB,
240			SUBPARTITION s5 ENGINE=InnoDB
241			)
242			)
243			EOT
244			),
245			array(
246			'parts' => <<<EOT
247			CREATE TABLE ptest (
248			`event_date` date NOT NULL
249			) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
250			PARTITION BY HASH (TO_DAYS(event_date))
251			(
252			PARTITION p0 ENGINE=InnoDB,
253			PARTITION p1 ENGINE=InnoDB,
254			PARTITION p2 ENGINE=InnoDB,
255			PARTITION p3 ENGINE=InnoDB,
256			PARTITION p4 ENGINE=InnoDB
257			)
258			EOT
259			)
260			);
261			}
262
263			/**
264			* @dataProvider partitionQueries
265			*
266			* @param string $query
267			*/
268			public function testBuilderPartitionsEngine($query)
269			{
270			$parser = new Parser($query);
271			$stmt = $parser->statements[0];
272
273			$this->assertEquals($query, $stmt->build());
274			}
275
276			public function testBuilderView()
277			{
278			$parser = new Parser(
279			'CREATE VIEW myView (vid, vfirstname) AS ' .
280			'SELECT id, first_name FROM employee WHERE id = 1'
281			);
282			$stmt = $parser->statements[0];
283
284			$this->assertEquals(
285			'CREATE VIEW myView (vid, vfirstname) AS ' .
286			'SELECT id, first_name FROM employee WHERE id = 1 ',
287			$stmt->build()
288			);
289
290			$parser = new Parser(
291			'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
292			'SELECT id, first_name FROM employee WHERE id = 1'
293			);
294			$stmt = $parser->statements[0];
295
296			$this->assertEquals(
297			'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
298			'SELECT id, first_name FROM employee WHERE id = 1 ',
299			$stmt->build()
300			);
301
302			// Assert the builder can build wrong syntax select expressions
303			$parser = new Parser(
304			'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
305			'SELECT id, first_name, FROMzz employee WHERE id = 1'
306			);
307			$stmt = $parser->statements[0];
308			$this->assertEquals(
309			'CREATE OR REPLACE VIEW myView (vid, vfirstname) AS ' .
310			'SELECT id, first_name, FROMzz employee WHERE id = 1 ',
311			$stmt->build()
312			);
313			}
314
315			public function testBuilderTrigger()
316			{
317			$stmt = new CreateStatement();
318
319			$stmt->options = new OptionsArray(array('TRIGGER'));
320			$stmt->name = new Expression('ins_sum');
321			$stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT'));
322			$stmt->table = new Expression('account');
323			$stmt->body = 'SET @sum = @sum + NEW.amount';
324
325			$this->assertEquals(
326			'CREATE TRIGGER ins_sum BEFORE INSERT ON account ' .
327			'FOR EACH ROW SET @sum = @sum + NEW.amount',
328			$stmt->build()
329			);
330			}
331
332			public function testBuilderRoutine()
333			{
334			$parser = new Parser(
335			'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
336			'BEGIN ' .
337			'DECLARE name VARCHAR DEFAULT ""; ' .
338			'SELECT name INTO name FROM employees WHERE id = i; ' .
339			'RETURN name; ' .
340			'END'
341			);
342			$stmt = $parser->statements[0];
343
344			$this->assertEquals(
345			'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
346			'BEGIN ' .
347			'DECLARE name VARCHAR DEFAULT ""; ' .
348			'SELECT name INTO name FROM employees WHERE id = i; ' .
349			'RETURN name; ' .
350			'END',
351			$stmt->build()
352			);
353			}
354
355			public function testBuildSelect()
356			{
357			$parser = new Parser(
358			'CREATE TABLE new_tbl SELECT * FROM orig_tbl'
359			);
360			$this->assertEquals(
361			'CREATE TABLE new_tbl SELECT * FROM orig_tbl',
362			$parser->statements[0]->build()
363			);
364			}
365			}
366

phpmyadmin / sql-parser

Pull Request — master (#340)

CreateStatementTest::testBuilderRoutine() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like