packages/sql-fixture/tests/Unit/Schema/MySqlSchemaParserTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Schema;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\Test;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use SqlFixture\Platform\MySql\MySqlSchemaParser as PlatformMySqlSchemaParser;
12use SqlFixture\Schema\ColumnDefinition;
13use SqlFixture\Schema\MySqlSchemaParser;
14use SqlFixture\Schema\SchemaParseException;
15use SqlFixture\Schema\TableSchema;
16
17#[CoversClass(MySqlSchemaParser::class)]
18#[UsesClass(ColumnDefinition::class)]
19#[UsesClass(TableSchema::class)]
20#[UsesClass(SchemaParseException::class)]
21#[UsesClass(PlatformMySqlSchemaParser::class)]
22#[UsesClass(\SqlFixture\Schema\SchemaParserInterface::class)]
23#[UsesClass(\SqlFixture\Platform\MySql\Schema\ColumnParser::class)]
24#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefaultExpression::class)]
25#[UsesClass(\SqlFixture\Platform\MySql\Schema\DefinitionIntegrity::class)]
26#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinition::class)]
27#[UsesClass(\SqlFixture\Platform\MySql\Schema\TypeParameters::class)]
28#[UsesClass(\SqlFixture\Schema\TypeShape::class)]
29#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinitionInput::class)]
30#[UsesClass(\SqlFixture\Schema\Exception\InvalidSqlException::class)]
31#[UsesClass(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class)]
32#[UsesClass(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class)]
33final class MySqlSchemaParserTest extends TestCase
34{
35    #[Test]
36    public function testParseSimpleTable(): void
37    {
38        $sql = 'CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255))';
39        $schema = (new MySqlSchemaParser())->parse($sql);
40
41        self::assertSame('users', $schema->tableName);
42        self::assertCount(2, $schema->columns);
43        self::assertArrayHasKey('id', $schema->columns);
44        self::assertArrayHasKey('name', $schema->columns);
45        self::assertSame(['id'], $schema->primaryKeys);
46    }
47
48    #[Test]
49    public function testParseColumnTypes(): void
50    {
51        $sql = <<<'SQL'
52            CREATE TABLE test (
53                col_int INT,
54                col_varchar VARCHAR(100),
55                col_decimal DECIMAL(10,2)
56            )
57            SQL;
58
59        $schema = (new MySqlSchemaParser())->parse($sql);
60
61        self::assertSame('INT', $schema->columns['col_int']->type);
62        self::assertSame('VARCHAR', $schema->columns['col_varchar']->type);
63        self::assertSame(100, $schema->columns['col_varchar']->length);
64        self::assertSame('DECIMAL', $schema->columns['col_decimal']->type);
65        self::assertSame(10, $schema->columns['col_decimal']->precision);
66        self::assertSame(2, $schema->columns['col_decimal']->scale);
67    }
68
69    #[Test]
70    public function testParseNotNullConstraint(): void
71    {
72        $sql = 'CREATE TABLE test (id INT NOT NULL, name VARCHAR(255))';
73        $schema = (new MySqlSchemaParser())->parse($sql);
74
75        self::assertFalse($schema->columns['id']->nullable);
76        self::assertTrue($schema->columns['name']->nullable);
77    }
78
79    #[Test]
80    public function testParseUnsigned(): void
81    {
82        $sql = 'CREATE TABLE test (id INT UNSIGNED, age TINYINT UNSIGNED)';
83        $schema = (new MySqlSchemaParser())->parse($sql);
84
85        self::assertTrue($schema->columns['id']->unsigned);
86        self::assertTrue($schema->columns['age']->unsigned);
87    }
88
89    #[Test]
90    public function testParseAutoIncrement(): void
91    {
92        $sql = 'CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT)';
93        $schema = (new MySqlSchemaParser())->parse($sql);
94
95        self::assertTrue($schema->columns['id']->autoIncrement);
96    }
97
98    #[Test]
99    public function testParseEnum(): void
100    {
101        $sql = "CREATE TABLE test (status ENUM('active','inactive','pending'))";
102        $schema = (new MySqlSchemaParser())->parse($sql);
103
104        self::assertSame('ENUM', $schema->columns['status']->type);
105        self::assertSame(['active', 'inactive', 'pending'], $schema->columns['status']->enumValues);
106    }
107
108    #[Test]
109    public function testParseSet(): void
110    {
111        $sql = "CREATE TABLE test (permissions SET('read','write','delete'))";
112        $schema = (new MySqlSchemaParser())->parse($sql);
113
114        self::assertSame('SET', $schema->columns['permissions']->type);
115        self::assertSame(['read', 'write', 'delete'], $schema->columns['permissions']->enumValues);
116    }
117
118    #[Test]
119    public function testParseCompositePrimaryKey(): void
120    {
121        $sql = 'CREATE TABLE test (a INT, b INT, PRIMARY KEY (a, b))';
122        $schema = (new MySqlSchemaParser())->parse($sql);
123
124        self::assertSame(['a', 'b'], $schema->primaryKeys);
125    }
126
127    #[Test]
128    public function testParsePrimaryKeyImpliesNotNull(): void
129    {
130        $sql = 'CREATE TABLE test (id INT PRIMARY KEY)';
131        $schema = (new MySqlSchemaParser())->parse($sql);
132
133        self::assertFalse($schema->columns['id']->nullable);
134    }
135
136    #[Test]
137    public function testThrowsExceptionForInvalidSql(): void
138    {
139        $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
140        (new MySqlSchemaParser())->parse('NOT A VALID SQL STATEMENT');
141    }
142
143    #[Test]
144    public function testThrowsExceptionForNonCreateTable(): void
145    {
146        $this->expectException(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class);
147        (new MySqlSchemaParser())->parse('SELECT * FROM users');
148    }
149
150    #[Test]
151    public function testParseDefaultString(): void
152    {
153        $sql = "CREATE TABLE test (name VARCHAR(255) DEFAULT 'default_value')";
154        $schema = (new MySqlSchemaParser())->parse($sql);
155
156        self::assertSame('default_value', $schema->columns['name']->default);
157    }
158
159    #[Test]
160    public function testParseDefaultNull(): void
161    {
162        $sql = 'CREATE TABLE test (name VARCHAR(255) DEFAULT NULL)';
163        $schema = (new MySqlSchemaParser())->parse($sql);
164
165        self::assertNull($schema->columns['name']->default);
166    }
167
168    #[Test]
169    public function testParseDefaultInteger(): void
170    {
171        $sql = 'CREATE TABLE test (count INT DEFAULT 42)';
172        $schema = (new MySqlSchemaParser())->parse($sql);
173
174        self::assertSame(42, $schema->columns['count']->default);
175    }
176
177    #[Test]
178    public function testParseDecimalPrecisionAndScale(): void
179    {
180        $sql = 'CREATE TABLE test (price DECIMAL(10,2))';
181        $schema = (new MySqlSchemaParser())->parse($sql);
182
183        self::assertSame(10, $schema->columns['price']->precision);
184        self::assertSame(2, $schema->columns['price']->scale);
185    }
186
187    #[Test]
188    public function testParseBitLength(): void
189    {
190        $sql = 'CREATE TABLE test (flags BIT(8))';
191        $schema = (new MySqlSchemaParser())->parse($sql);
192
193        self::assertSame(8, $schema->columns['flags']->length);
194    }
195
196    #[Test]
197    public function testParseTableWithQuotedNames(): void
198    {
199        $sql = 'CREATE TABLE `my_table` (`my_column` INT PRIMARY KEY)';
200        $schema = (new MySqlSchemaParser())->parse($sql);
201
202        self::assertSame('my_table', $schema->tableName);
203        self::assertArrayHasKey('my_column', $schema->columns);
204    }
205
206    #[Test]
207    public function testParseDefaultBoolean(): void
208    {
209        $sql = 'CREATE TABLE test (active TINYINT(1) DEFAULT TRUE)';
210        $schema = (new MySqlSchemaParser())->parse($sql);
211
212        self::assertTrue($schema->columns['active']->default);
213    }
214
215    #[Test]
216    public function testParseDefaultFalseBoolean(): void
217    {
218        $sql = 'CREATE TABLE test (active TINYINT(1) DEFAULT FALSE)';
219        $schema = (new MySqlSchemaParser())->parse($sql);
220
221        self::assertFalse($schema->columns['active']->default);
222    }
223
224    #[Test]
225    public function testParseDefaultFloat(): void
226    {
227        $sql = 'CREATE TABLE test (price DECIMAL(10,2) DEFAULT 9.99)';
228        $schema = (new MySqlSchemaParser())->parse($sql);
229
230        self::assertSame(9.99, $schema->columns['price']->default);
231    }
232
233    #[Test]
234    public function testParseGeneratedColumn(): void
235    {
236        $sql = 'CREATE TABLE test (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) STORED)';
237        $schema = (new MySqlSchemaParser())->parse($sql);
238
239        self::assertTrue($schema->columns['c']->generated);
240        self::assertFalse($schema->columns['a']->generated);
241    }
242
243    #[Test]
244    public function testParseJsonType(): void
245    {
246        $sql = 'CREATE TABLE test (data JSON)';
247        $schema = (new MySqlSchemaParser())->parse($sql);
248
249        self::assertSame('JSON', $schema->columns['data']->type);
250        self::assertTrue($schema->columns['data']->nullable);
251    }
252
253    #[Test]
254    public function testParseSpatialTypes(): void
255    {
256        $sql = <<<'SQL'
257            CREATE TABLE geo (
258                col_point POINT,
259                col_linestring LINESTRING,
260                col_polygon POLYGON,
261                col_multipoint MULTIPOINT,
262                col_geometry GEOMETRY,
263                col_geometrycollection GEOMETRYCOLLECTION
264            )
265            SQL;
266
267        $schema = (new MySqlSchemaParser())->parse($sql);
268
269        self::assertSame('POINT', $schema->columns['col_point']->type);
270        self::assertSame('LINESTRING', $schema->columns['col_linestring']->type);
271        self::assertSame('POLYGON', $schema->columns['col_polygon']->type);
272        self::assertSame('MULTIPOINT', $schema->columns['col_multipoint']->type);
273        self::assertSame('GEOMETRY', $schema->columns['col_geometry']->type);
274        self::assertSame('GEOMETRYCOLLECTION', $schema->columns['col_geometrycollection']->type);
275    }
276
277    #[Test]
278    public function testParseBinaryTypes(): void
279    {
280        $sql = <<<'SQL'
281            CREATE TABLE bins (
282                col_binary BINARY(16),
283                col_varbinary VARBINARY(255),
284                col_blob BLOB,
285                col_tinyblob TINYBLOB,
286                col_mediumblob MEDIUMBLOB,
287                col_longblob LONGBLOB
288            )
289            SQL;
290
291        $schema = (new MySqlSchemaParser())->parse($sql);
292
293        self::assertSame('BINARY', $schema->columns['col_binary']->type);
294        self::assertSame(16, $schema->columns['col_binary']->length);
295        self::assertSame('VARBINARY', $schema->columns['col_varbinary']->type);
296        self::assertSame(255, $schema->columns['col_varbinary']->length);
297        self::assertSame('BLOB', $schema->columns['col_blob']->type);
298    }
299
300    #[Test]
301    public function testParseMultipleDataTypes(): void
302    {
303        $sql = <<<'SQL'
304            CREATE TABLE all_types (
305                col_tinyint TINYINT,
306                col_smallint SMALLINT,
307                col_mediumint MEDIUMINT,
308                col_bigint BIGINT,
309                col_float FLOAT,
310                col_double DOUBLE,
311                col_date DATE,
312                col_time TIME,
313                col_datetime DATETIME,
314                col_timestamp TIMESTAMP NULL,
315                col_year YEAR,
316                col_json JSON,
317                col_point POINT,
318                col_polygon POLYGON
319            )
320            SQL;
321
322        $schema = (new MySqlSchemaParser())->parse($sql);
323
324        self::assertSame('TINYINT', $schema->columns['col_tinyint']->type);
325        self::assertSame('SMALLINT', $schema->columns['col_smallint']->type);
326        self::assertSame('MEDIUMINT', $schema->columns['col_mediumint']->type);
327        self::assertSame('BIGINT', $schema->columns['col_bigint']->type);
328        self::assertSame('FLOAT', $schema->columns['col_float']->type);
329        self::assertSame('DOUBLE', $schema->columns['col_double']->type);
330        self::assertSame('DATE', $schema->columns['col_date']->type);
331        self::assertSame('TIME', $schema->columns['col_time']->type);
332        self::assertSame('DATETIME', $schema->columns['col_datetime']->type);
333        self::assertSame('TIMESTAMP', $schema->columns['col_timestamp']->type);
334        self::assertSame('YEAR', $schema->columns['col_year']->type);
335        self::assertSame('JSON', $schema->columns['col_json']->type);
336        self::assertSame('POINT', $schema->columns['col_point']->type);
337        self::assertSame('POLYGON', $schema->columns['col_polygon']->type);
338    }
339}
340