packages/sql-fixture/tests/Unit/Platform/MySql/MySqlSchemaParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Platform\MySql;
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;
12use SqlFixture\Schema\ColumnDefinition;
13use SqlFixture\Schema\SchemaParseException;
14use SqlFixture\Schema\TableSchema;
15
16#[CoversClass(MySqlSchemaParser::class)]
17#[UsesClass(ColumnDefinition::class)]
18#[UsesClass(TableSchema::class)]
19#[UsesClass(SchemaParseException::class)]
20#[CoversClass(\SqlFixture\Platform\MySql\Schema\ColumnParser::class)]
21#[CoversClass(\SqlFixture\Platform\MySql\Schema\DefaultExpression::class)]
22#[CoversClass(\SqlFixture\Platform\MySql\Schema\DefinitionIntegrity::class)]
23#[CoversClass(\SqlFixture\Platform\MySql\Schema\TableDefinition::class)]
24#[CoversClass(\SqlFixture\Platform\MySql\Schema\TypeParameters::class)]
25#[UsesClass(\SqlFixture\Schema\SchemaParserInterface::class)]
26#[UsesClass(\SqlFixture\Schema\TypeShape::class)]
27#[UsesClass(\SqlFixture\Platform\MySql\Schema\TableDefinitionInput::class)]
28#[UsesClass(\SqlFixture\Schema\Exception\InvalidSqlException::class)]
29#[UsesClass(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class)]
30#[UsesClass(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class)]
31final class MySqlSchemaParserTest extends TestCase
32{
33 #[Test]
34 public function testParseSimpleTable(): void
35 {
36 $sql = 'CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL)';
37 $schema = (new MySqlSchemaParser())->parse($sql);
38
39 self::assertSame('users', $schema->tableName);
40 self::assertCount(2, $schema->columns);
41 self::assertSame('INT', $schema->columns['id']->type);
42 self::assertSame('VARCHAR', $schema->columns['name']->type);
43 self::assertSame(255, $schema->columns['name']->length);
44 self::assertFalse($schema->columns['id']->nullable);
45 self::assertFalse($schema->columns['name']->nullable);
46 }
47
48 #[Test]
49 public function testParseAllNumericTypes(): void
50 {
51 $sql = <<<'SQL'
52 CREATE TABLE nums (
53 col_tinyint TINYINT,
54 col_smallint SMALLINT,
55 col_mediumint MEDIUMINT,
56 col_int INT,
57 col_integer INTEGER,
58 col_bigint BIGINT,
59 col_float FLOAT,
60 col_double DOUBLE,
61 col_real REAL,
62 col_decimal DECIMAL(10,2),
63 col_numeric NUMERIC(8,3),
64 col_bit BIT(16)
65 )
66 SQL;
67
68 $schema = (new MySqlSchemaParser())->parse($sql);
69
70 self::assertSame('TINYINT', $schema->columns['col_tinyint']->type);
71 self::assertSame('SMALLINT', $schema->columns['col_smallint']->type);
72 self::assertSame('MEDIUMINT', $schema->columns['col_mediumint']->type);
73 self::assertSame('INT', $schema->columns['col_int']->type);
74 self::assertSame('INTEGER', $schema->columns['col_integer']->type);
75 self::assertSame('BIGINT', $schema->columns['col_bigint']->type);
76 self::assertSame('FLOAT', $schema->columns['col_float']->type);
77 self::assertSame('DOUBLE', $schema->columns['col_double']->type);
78 self::assertSame('REAL', $schema->columns['col_real']->type);
79 self::assertSame('DECIMAL', $schema->columns['col_decimal']->type);
80 self::assertSame(10, $schema->columns['col_decimal']->precision);
81 self::assertSame(2, $schema->columns['col_decimal']->scale);
82 self::assertSame('NUMERIC', $schema->columns['col_numeric']->type);
83 self::assertSame(8, $schema->columns['col_numeric']->precision);
84 self::assertSame(3, $schema->columns['col_numeric']->scale);
85 self::assertSame('BIT', $schema->columns['col_bit']->type);
86 self::assertSame(16, $schema->columns['col_bit']->length);
87 }
88
89 #[Test]
90 public function testParseAllStringTypes(): void
91 {
92 $sql = <<<'SQL'
93 CREATE TABLE strs (
94 col_char CHAR(10),
95 col_varchar VARCHAR(255),
96 col_tinytext TINYTEXT,
97 col_text TEXT,
98 col_mediumtext MEDIUMTEXT,
99 col_longtext LONGTEXT
100 )
101 SQL;
102
103 $schema = (new MySqlSchemaParser())->parse($sql);
104
105 self::assertSame('CHAR', $schema->columns['col_char']->type);
106 self::assertSame(10, $schema->columns['col_char']->length);
107 self::assertSame('VARCHAR', $schema->columns['col_varchar']->type);
108 self::assertSame(255, $schema->columns['col_varchar']->length);
109 self::assertSame('TINYTEXT', $schema->columns['col_tinytext']->type);
110 self::assertSame('TEXT', $schema->columns['col_text']->type);
111 self::assertSame('MEDIUMTEXT', $schema->columns['col_mediumtext']->type);
112 self::assertSame('LONGTEXT', $schema->columns['col_longtext']->type);
113 }
114
115 #[Test]
116 public function testParseUnsignedInTypeOptions(): void
117 {
118 $sql = 'CREATE TABLE test (id INT UNSIGNED NOT NULL, age TINYINT UNSIGNED)';
119 $schema = (new MySqlSchemaParser())->parse($sql);
120
121 self::assertTrue($schema->columns['id']->unsigned);
122 self::assertTrue($schema->columns['age']->unsigned);
123 }
124
125 #[Test]
126 public function testParseAutoIncrementWithPrimaryKey(): void
127 {
128 $sql = 'CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))';
129 $schema = (new MySqlSchemaParser())->parse($sql);
130
131 self::assertTrue($schema->columns['id']->autoIncrement);
132 self::assertFalse($schema->columns['id']->nullable);
133 self::assertSame(['id'], $schema->primaryKeys);
134 }
135
136 #[Test]
137 public function testParseEnumValues(): void
138 {
139 $sql = "CREATE TABLE test (status ENUM('active','inactive','pending') NOT NULL)";
140 $schema = (new MySqlSchemaParser())->parse($sql);
141
142 self::assertSame('ENUM', $schema->columns['status']->type);
143 self::assertSame(['active', 'inactive', 'pending'], $schema->columns['status']->enumValues);
144 }
145
146 #[Test]
147 public function testParseSetValues(): void
148 {
149 $sql = "CREATE TABLE test (perms SET('read','write','delete'))";
150 $schema = (new MySqlSchemaParser())->parse($sql);
151
152 self::assertSame('SET', $schema->columns['perms']->type);
153 self::assertSame(['read', 'write', 'delete'], $schema->columns['perms']->enumValues);
154 }
155
156 #[Test]
157 public function testParseCompositePrimaryKeyWithBackticks(): void
158 {
159 $sql = 'CREATE TABLE test (`a` INT, `b` INT, PRIMARY KEY (`a`, `b`))';
160 $schema = (new MySqlSchemaParser())->parse($sql);
161
162 self::assertSame(['a', 'b'], $schema->primaryKeys);
163 self::assertFalse($schema->columns['a']->nullable);
164 self::assertFalse($schema->columns['b']->nullable);
165 }
166
167 #[Test]
168 public function testThrowsOnEmptyInput(): void
169 {
170 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
171 (new MySqlSchemaParser())->parse('');
172 }
173
174 #[Test]
175 public function testThrowsOnNonCreateTableStatement(): void
176 {
177 $this->expectException(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class);
178 (new MySqlSchemaParser())->parse('INSERT INTO users VALUES (1, "test")');
179 }
180
181 #[Test]
182 public function testParseDefaultStringValue(): void
183 {
184 $sql = "CREATE TABLE test (name VARCHAR(255) DEFAULT 'hello')";
185 $schema = (new MySqlSchemaParser())->parse($sql);
186
187 self::assertSame('hello', $schema->columns['name']->default);
188 }
189
190 #[Test]
191 public function testParseDefaultIntegerValue(): void
192 {
193 $sql = 'CREATE TABLE test (count INT DEFAULT 42)';
194 $schema = (new MySqlSchemaParser())->parse($sql);
195
196 self::assertSame(42, $schema->columns['count']->default);
197 }
198
199 #[Test]
200 public function testParseDefaultFloatValue(): void
201 {
202 $sql = 'CREATE TABLE test (price DECIMAL(10,2) DEFAULT 9.99)';
203 $schema = (new MySqlSchemaParser())->parse($sql);
204
205 self::assertSame(9.99, $schema->columns['price']->default);
206 }
207
208 #[Test]
209 public function testParseDefaultNullValue(): void
210 {
211 $sql = 'CREATE TABLE test (name VARCHAR(255) DEFAULT NULL)';
212 $schema = (new MySqlSchemaParser())->parse($sql);
213
214 self::assertNull($schema->columns['name']->default);
215 }
216
217 #[Test]
218 public function testParseDefaultBooleanTrueValue(): void
219 {
220 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT TRUE)';
221 $schema = (new MySqlSchemaParser())->parse($sql);
222
223 self::assertTrue($schema->columns['active']->default);
224 }
225
226 #[Test]
227 public function testParseDefaultBooleanFalseValue(): void
228 {
229 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT FALSE)';
230 $schema = (new MySqlSchemaParser())->parse($sql);
231
232 self::assertFalse($schema->columns['active']->default);
233 }
234
235 #[Test]
236 public function testParseNoDefaultReturnsNull(): void
237 {
238 $sql = 'CREATE TABLE test (name VARCHAR(255) NOT NULL)';
239 $schema = (new MySqlSchemaParser())->parse($sql);
240
241 self::assertNull($schema->columns['name']->default);
242 }
243
244 #[Test]
245 public function testParseGeneratedColumn(): void
246 {
247 $sql = 'CREATE TABLE test (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) STORED)';
248 $schema = (new MySqlSchemaParser())->parse($sql);
249
250 self::assertTrue($schema->columns['c']->generated);
251 self::assertFalse($schema->columns['a']->generated);
252 }
253
254 #[Test]
255 public function testParseNullableDefaults(): void
256 {
257 $sql = 'CREATE TABLE test (id INT NOT NULL, name VARCHAR(255), notes TEXT)';
258 $schema = (new MySqlSchemaParser())->parse($sql);
259
260 self::assertFalse($schema->columns['id']->nullable);
261 self::assertTrue($schema->columns['name']->nullable);
262 self::assertTrue($schema->columns['notes']->nullable);
263 }
264
265 #[Test]
266 public function testParseBacktickedColumnNames(): void
267 {
268 $sql = 'CREATE TABLE `test` (`my_id` INT PRIMARY KEY, `my_name` VARCHAR(255))';
269 $schema = (new MySqlSchemaParser())->parse($sql);
270
271 self::assertSame('test', $schema->tableName);
272 self::assertArrayHasKey('my_id', $schema->columns);
273 self::assertArrayHasKey('my_name', $schema->columns);
274 }
275
276 #[Test]
277 public function testParseUnsignedInColumnOptions(): void
278 {
279 $sql = 'CREATE TABLE test (id INT UNSIGNED PRIMARY KEY)';
280 $schema = (new MySqlSchemaParser())->parse($sql);
281
282 self::assertTrue($schema->columns['id']->unsigned);
283 self::assertFalse($schema->columns['id']->nullable);
284 }
285
286 #[Test]
287 public function testParseDecimalWithoutParameters(): void
288 {
289 $sql = 'CREATE TABLE test (amount DECIMAL)';
290 $schema = (new MySqlSchemaParser())->parse($sql);
291
292 self::assertSame('DECIMAL', $schema->columns['amount']->type);
293 }
294
295 #[Test]
296 public function testParsePrimaryKeyColumnDefinitionLevel(): void
297 {
298 $sql = 'CREATE TABLE test (id INT PRIMARY KEY)';
299 $schema = (new MySqlSchemaParser())->parse($sql);
300
301 self::assertFalse($schema->columns['id']->nullable);
302 }
303
304 #[Test]
305 public function testParseTableLevelPrimaryKeyMakesColumnsNonNullable(): void
306 {
307 $sql = 'CREATE TABLE test (a INT, b INT, PRIMARY KEY (a))';
308 $schema = (new MySqlSchemaParser())->parse($sql);
309
310 self::assertFalse($schema->columns['a']->nullable);
311 }
312
313 #[Test]
314 public function testParseAutoIncrementWithoutPrimaryKeyInDefinition(): void
315 {
316 $sql = 'CREATE TABLE test (id INT AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY (id))';
317 $schema = (new MySqlSchemaParser())->parse($sql);
318
319 self::assertTrue($schema->columns['id']->autoIncrement);
320 }
321
322 #[Test]
323 public function testParseDefaultNonStringValue(): void
324 {
325 $sql = 'CREATE TABLE test (val VARCHAR(255) DEFAULT CURRENT_TIMESTAMP)';
326 $schema = (new MySqlSchemaParser())->parse($sql);
327
328 self::assertSame('CURRENT_TIMESTAMP', $schema->columns['val']->default);
329 }
330
331 #[Test]
332 public function testParseBitDefaultLength(): void
333 {
334 $sql = 'CREATE TABLE test (flag BIT(1))';
335 $schema = (new MySqlSchemaParser())->parse($sql);
336
337 self::assertSame('BIT', $schema->columns['flag']->type);
338 self::assertSame(1, $schema->columns['flag']->length);
339 }
340
341 #[Test]
342 public function testParseDecimalAllVariants(): void
343 {
344 $sql = <<<'SQL'
345 CREATE TABLE test (
346 col_decimal DECIMAL(10,2),
347 col_numeric NUMERIC(8,3),
348 col_dec DEC(5,1),
349 col_fixed FIXED(6,2)
350 )
351 SQL;
352
353 $schema = (new MySqlSchemaParser())->parse($sql);
354
355 self::assertSame('DECIMAL', $schema->columns['col_decimal']->type);
356 self::assertSame(10, $schema->columns['col_decimal']->precision);
357 self::assertSame(2, $schema->columns['col_decimal']->scale);
358
359 self::assertSame('NUMERIC', $schema->columns['col_numeric']->type);
360 self::assertSame(8, $schema->columns['col_numeric']->precision);
361 self::assertSame(3, $schema->columns['col_numeric']->scale);
362
363 self::assertSame('DEC', $schema->columns['col_dec']->type);
364 self::assertSame(5, $schema->columns['col_dec']->precision);
365 self::assertSame(1, $schema->columns['col_dec']->scale);
366
367 self::assertSame('FIXED', $schema->columns['col_fixed']->type);
368 self::assertSame(6, $schema->columns['col_fixed']->precision);
369 self::assertSame(2, $schema->columns['col_fixed']->scale);
370 }
371
372 #[Test]
373 public function testParseNonAutoIncrementColumn(): void
374 {
375 $sql = 'CREATE TABLE test (id INT, name VARCHAR(255))';
376 $schema = (new MySqlSchemaParser())->parse($sql);
377
378 self::assertFalse($schema->columns['id']->autoIncrement);
379 self::assertFalse($schema->columns['name']->autoIncrement);
380 }
381
382 #[Test]
383 public function testParseNonGeneratedColumn(): void
384 {
385 $sql = 'CREATE TABLE test (id INT, name VARCHAR(255))';
386 $schema = (new MySqlSchemaParser())->parse($sql);
387
388 self::assertFalse($schema->columns['id']->generated);
389 self::assertFalse($schema->columns['name']->generated);
390 }
391
392 #[Test]
393 public function testParseNonUnsignedColumn(): void
394 {
395 $sql = 'CREATE TABLE test (id INT, name VARCHAR(255))';
396 $schema = (new MySqlSchemaParser())->parse($sql);
397
398 self::assertFalse($schema->columns['id']->unsigned);
399 }
400
401 #[Test]
402 public function testParseLowercaseDefaultNull(): void
403 {
404 $sql = 'CREATE TABLE test (name VARCHAR(255) DEFAULT null)';
405 $schema = (new MySqlSchemaParser())->parse($sql);
406
407 self::assertNull($schema->columns['name']->default);
408 }
409
410 #[Test]
411 public function testParseLowercaseDefaultTrue(): void
412 {
413 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT true)';
414 $schema = (new MySqlSchemaParser())->parse($sql);
415
416 self::assertTrue($schema->columns['active']->default);
417 }
418
419 #[Test]
420 public function testParseLowercaseDefaultFalse(): void
421 {
422 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT false)';
423 $schema = (new MySqlSchemaParser())->parse($sql);
424
425 self::assertFalse($schema->columns['active']->default);
426 }
427
428 #[Test]
429 public function testParseLowercaseTypeName(): void
430 {
431 $sql = 'CREATE TABLE test (id int primary key auto_increment, name varchar(255))';
432 $schema = (new MySqlSchemaParser())->parse($sql);
433
434 self::assertSame('INT', $schema->columns['id']->type);
435 self::assertSame('VARCHAR', $schema->columns['name']->type);
436 }
437
438 #[Test]
439 public function testParseDecimalWithPrecisionOnly(): void
440 {
441 $sql = 'CREATE TABLE test (amount DECIMAL(5))';
442 $schema = (new MySqlSchemaParser())->parse($sql);
443
444 self::assertSame('DECIMAL', $schema->columns['amount']->type);
445 self::assertSame(5, $schema->columns['amount']->precision);
446 self::assertSame(0, $schema->columns['amount']->scale);
447 }
448
449 #[Test]
450 public function testParseBitWithoutLength(): void
451 {
452 $sql = 'CREATE TABLE test (flag BIT)';
453 $schema = (new MySqlSchemaParser())->parse($sql);
454
455 self::assertSame('BIT', $schema->columns['flag']->type);
456 }
457
458 #[Test]
459 public function testParseNumericWithPrecisionOnly(): void
460 {
461 $sql = 'CREATE TABLE test (val NUMERIC(7))';
462 $schema = (new MySqlSchemaParser())->parse($sql);
463
464 self::assertSame('NUMERIC', $schema->columns['val']->type);
465 self::assertSame(7, $schema->columns['val']->precision);
466 self::assertSame(0, $schema->columns['val']->scale);
467 }
468
469 #[Test]
470 public function testParseFixedWithPrecisionOnly(): void
471 {
472 $sql = 'CREATE TABLE test (val FIXED(4))';
473 $schema = (new MySqlSchemaParser())->parse($sql);
474
475 self::assertSame('FIXED', $schema->columns['val']->type);
476 self::assertSame(4, $schema->columns['val']->precision);
477 self::assertSame(0, $schema->columns['val']->scale);
478 }
479
480 #[Test]
481 public function testParseDecWithPrecisionOnly(): void
482 {
483 $sql = 'CREATE TABLE test (val DEC(3))';
484 $schema = (new MySqlSchemaParser())->parse($sql);
485
486 self::assertSame('DEC', $schema->columns['val']->type);
487 self::assertSame(3, $schema->columns['val']->precision);
488 self::assertSame(0, $schema->columns['val']->scale);
489 }
490
491 #[Test]
492 public function testParseConstraintBeforeColumn(): void
493 {
494 $sql = 'CREATE TABLE test (id INT, PRIMARY KEY (id), name VARCHAR(255) NOT NULL)';
495 $schema = (new MySqlSchemaParser())->parse($sql);
496
497 self::assertCount(2, $schema->columns);
498 self::assertArrayHasKey('id', $schema->columns);
499 self::assertArrayHasKey('name', $schema->columns);
500 self::assertSame('VARCHAR', $schema->columns['name']->type);
501 self::assertFalse($schema->columns['name']->nullable);
502 }
503
504 #[Test]
505 public function testParseMultipleConstraintsBetweenColumns(): void
506 {
507 $sql = <<<'SQL'
508 CREATE TABLE test (
509 id INT,
510 PRIMARY KEY (id),
511 UNIQUE KEY (id),
512 name VARCHAR(255) NOT NULL,
513 email VARCHAR(100),
514 INDEX idx_name (name),
515 age INT
516 )
517 SQL;
518
519 $schema = (new MySqlSchemaParser())->parse($sql);
520
521 self::assertCount(4, $schema->columns);
522 self::assertArrayHasKey('name', $schema->columns);
523 self::assertArrayHasKey('email', $schema->columns);
524 self::assertArrayHasKey('age', $schema->columns);
525 self::assertSame('INT', $schema->columns['age']->type);
526 }
527
528 #[Test]
529 public function testParseBacktickedTableNameStripped(): void
530 {
531 $sql = 'CREATE TABLE `my_table` (`id` INT PRIMARY KEY)';
532 $schema = (new MySqlSchemaParser())->parse($sql);
533
534 self::assertSame('my_table', $schema->tableName);
535 self::assertArrayHasKey('id', $schema->columns);
536 }
537
538 #[Test]
539 public function testParseBacktickedColumnNamesStripped(): void
540 {
541 $sql = 'CREATE TABLE test (`col_a` INT, `col_b` VARCHAR(50))';
542 $schema = (new MySqlSchemaParser())->parse($sql);
543
544 self::assertArrayHasKey('col_a', $schema->columns);
545 self::assertArrayHasKey('col_b', $schema->columns);
546 }
547
548 #[Test]
549 public function testParseBacktickedPrimaryKeyColumnsStripped(): void
550 {
551 $sql = 'CREATE TABLE test (`x` INT, `y` INT, PRIMARY KEY (`x`, `y`))';
552 $schema = (new MySqlSchemaParser())->parse($sql);
553
554 self::assertSame(['x', 'y'], $schema->primaryKeys);
555 self::assertFalse($schema->columns['x']->nullable);
556 self::assertFalse($schema->columns['y']->nullable);
557 }
558
559 #[Test]
560 public function testParseBitTypeIsBit(): void
561 {
562 $sql = 'CREATE TABLE test (flags BIT(4) NOT NULL)';
563 $schema = (new MySqlSchemaParser())->parse($sql);
564
565 self::assertSame('BIT', $schema->columns['flags']->type);
566 self::assertSame(4, $schema->columns['flags']->length);
567 }
568
569 #[Test]
570 public function testParseDefaultStringWithQuotes(): void
571 {
572 $sql = "CREATE TABLE test (name VARCHAR(100) DEFAULT 'hello world')";
573 $schema = (new MySqlSchemaParser())->parse($sql);
574
575 self::assertSame('hello world', $schema->columns['name']->default);
576 }
577
578 #[Test]
579 public function testParseDefaultWithNoDefaultReturnsNull(): void
580 {
581 $sql = 'CREATE TABLE test (id INT NOT NULL, name VARCHAR(100) NOT NULL)';
582 $schema = (new MySqlSchemaParser())->parse($sql);
583
584 self::assertNull($schema->columns['id']->default);
585 self::assertNull($schema->columns['name']->default);
586 }
587
588 #[Test]
589 public function testParseEnumOrSetExtractsValues(): void
590 {
591 $sql = "CREATE TABLE test (status ENUM('a','b','c'), tags SET('x','y'))";
592 $schema = (new MySqlSchemaParser())->parse($sql);
593
594 self::assertSame(['a', 'b', 'c'], $schema->columns['status']->enumValues);
595 self::assertSame(['x', 'y'], $schema->columns['tags']->enumValues);
596 }
597
598 #[Test]
599 public function testParseNonEnumTypeHasNullEnumValues(): void
600 {
601 $sql = 'CREATE TABLE test (name VARCHAR(100))';
602 $schema = (new MySqlSchemaParser())->parse($sql);
603
604 self::assertNull($schema->columns['name']->enumValues);
605 }
606
607 #[Test]
608 public function testDdlTheParserCannotFinishReadingIsRejected(): void
609 {
610 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
611 $this->expectExceptionMessage('A comma or a closing bracket was expected.');
612
613 (new MySqlSchemaParser())->parse(
614 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)'
615 );
616 }
617
618 #[Test]
619 public function testAnUnknownKeywordDoesNotSilentlyDropLaterColumns(): void
620 {
621 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
622 $this->expectExceptionMessage('A comma or a closing bracket was expected.');
623
624 (new MySqlSchemaParser())->parse('CREATE TABLE test (id INT WOMBAT, name TEXT NOT NULL)');
625 }
626
627 #[Test]
628 public function testAnUnclosedDefinitionListIsRejected(): void
629 {
630 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
631
632 (new MySqlSchemaParser())->parse('CREATE TABLE test (id INT,');
633 }
634
635 #[Test]
636 public function testATypeTheParserCallsUnrecognisedIsStillReadWhenNothingIsLost(): void
637 {
638 $schema = (new MySqlSchemaParser())->parse('CREATE TABLE test (a DEC(5,1), b INT)');
639
640 self::assertSame(['a', 'b'], $schema->getColumnNames());
641 }
642
643 #[Test]
644 public function testCommasInsideStringsAndEnumsAreNotCountedAsSeparators(): void
645 {
646 $schema = (new MySqlSchemaParser())->parse(
647 "CREATE TABLE test (a ENUM('x','y','z') NOT NULL, b VARCHAR(9) DEFAULT 'p,q', c INT)"
648 );
649
650 self::assertSame(['a', 'b', 'c'], $schema->getColumnNames());
651 }
652
653 #[Test]
654 public function testTableConstraintsAndOptionsDoNotLookLikeLostColumns(): void
655 {
656 $schema = (new MySqlSchemaParser())->parse(
657 'CREATE TABLE test (a INT, b INT, PRIMARY KEY (a), KEY idx_b (b)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
658 );
659
660 self::assertSame(['a', 'b'], $schema->getColumnNames());
661 self::assertSame(['a'], $schema->primaryKeys);
662 }
663
664 #[Test]
665 public function testTruncationIsFoundEvenAfterAColumnWithItsOwnBrackets(): void
666 {
667 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
668 $this->expectExceptionMessage('A comma or a closing bracket was expected.');
669
670 (new MySqlSchemaParser())->parse('CREATE TABLE test (a VARCHAR(9) WOMBAT, b INT)');
671 }
672
673 #[Test]
674 public function testACreateTableWithNoDefinitionListIsReportedAsHavingNoColumns(): void
675 {
676 $this->expectException(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class);
677 $this->expectExceptionMessage('No columns found in table: a');
678
679 (new MySqlSchemaParser())->parse('CREATE TABLE a LIKE b');
680 }
681
682 #[Test]
683 public function testASingleColumnTableIsNotMistakenForATruncatedOne(): void
684 {
685 $schema = (new MySqlSchemaParser())->parse('CREATE TABLE test (a DEC(5,1))');
686
687 self::assertSame(['a'], $schema->getColumnNames());
688 }
689 public function testParsePartitionedCreateAsValuesReadsTheExplicitColumn(): void
690 {
691 $schema = (new MySqlSchemaParser())->parse('CREATE TABLE t (value VARBINARY(1)) STATS_SAMPLE_PAGES 1 PARTITION BY KEY () REPLACE AS VALUES ROW (NULL)');
692 self::assertSame('t', $schema->tableName);
693 self::assertSame(['value'], array_keys($schema->columns));
694 self::assertSame('VARBINARY', $schema->columns['value']->type);
695 self::assertSame(1, $schema->columns['value']->length);
696 }
697}
698