CreateStatementTest::testBuilderDefaultInt() - Code Metrics - Inspection of "Merge branch 'QA'" - phpmyadmin/sql-parser - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( b18edd...c70841 )

by William

created 2021-08-16 20:52 UTC

CreateStatementTest::testBuilderDefaultInt() A

↳ Parent: CreateStatementTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	14
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	10
nc	1
nop	0
dl	0
loc	14
rs	9.9332
c	0
b	0
f	0

<?php

declare(strict_types=1);

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(): void
    {
        $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(): void
    {
        // 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(): void
    {
        $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(): void
    {
        $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(): void
    {
        $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(): void
    {
        /* Assertion 1 */
        $stmt = new CreateStatement();

        $stmt->name = new Expression('', 'test', '');
        $stmt->options = new OptionsArray(['TABLE']);
        $stmt->fields = [
            new CreateDefinition(
                'id',
                new OptionsArray(['NOT NULL', 'AUTO_INCREMENT']),
                new DataType('INT', [11], new OptionsArray(['UNSIGNED']))
            ),
            new CreateDefinition(
                '',
                null,
                new Key('', [['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(): void
    {
        /* 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 partitionQueriesProvider(): array
    {
        return [
            [
                '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
            ,
            ],
            [
                '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 partitionQueriesProvider
     */
    public function testBuilderPartitionsEngine(string $query): void
    {
        $parser = new Parser($query);
        $stmt = $parser->statements[0];

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

    public function testBuilderView(): void
    {
        $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()
        );

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

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

    public function testBuilderViewComplex(): void
    {
        $parser = new Parser(
            'CREATE VIEW withclause AS' . "\n"
            . "\n"
            . 'WITH cte AS (' . "\n"
                . 'SELECT p.name, p.shape' . "\n"
                . 'FROM gis_all as p' . "\n"
            . ')' . "\n"
            . "\n"
            . 'SELECT cte.*' . "\n"
            . 'FROM cte' . "\n"
            . 'CROSS JOIN gis_all;'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE VIEW withclause  AS ' . "\n"
            . "\n"
            . 'WITH cte AS (' . "\n"
                . 'SELECT p.name, p.shape' . "\n"
                . 'FROM gis_all as p' . "\n"
            . ')' . "\n"
            . "\n"
            . 'SELECT cte.*' . "\n"
            . 'FROM cte' . "\n"
            . 'CROSS JOIN gis_all ',
            $stmt->build()
        );
        $parser = new Parser(
            'CREATE VIEW withclause2 AS' . "\n"
            . "\n"
            . 'WITH cte AS (' . "\n"
                . "\t" . 'SELECT p.name, p.shape' . "\n"
                . "\t" . 'FROM gis_all as p' . "\n"
            . '), cte2 AS (' . "\n"
                . "\t" . 'SELECT p.name as n2, p.shape as sh2' . "\n"
                . "\t" . 'FROM gis_all as p' . "\n"
            . ')' . "\n"
            . "\n"
            . 'SELECT cte.*,cte2.*' . "\n"
            . 'FROM cte,cte2' . "\n"
            . 'CROSS JOIN gis_all;'
        );
        $stmt = $parser->statements[0];

        $this->assertEquals(
            'CREATE VIEW withclause2  AS ' . "\n"
            . "\n"
            . 'WITH cte AS (' . "\n"
                . "\t" . 'SELECT p.name, p.shape' . "\n"
                . "\t" . 'FROM gis_all as p' . "\n"
            . '), cte2 AS (' . "\n"
                . "\t" . 'SELECT p.name as n2, p.shape as sh2' . "\n"
                . "\t" . 'FROM gis_all as p' . "\n"
            . ')' . "\n"
            . "\n"
            . 'SELECT cte.*,cte2.*' . "\n"
            . 'FROM cte,cte2' . "\n"
            . 'CROSS JOIN gis_all ',
            $stmt->build()
        );
    }

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

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

phpmyadmin / sql-parser

Push — master ( b18edd...c70841 )

CreateStatementTest::testBuilderDefaultInt() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like