packages/ztd-query-mysql/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\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\MySql\Schema\MySqlSchemaParser;
11use ZtdQuery\Platform\MySql\Schema\Partition\MySqlPartitioningParser;
12use ZtdQuery\Platform\MySql\Sql\MySqlParser;
13use ZtdQuery\Schema\ColumnTypeFamily;
14use ZtdQuery\Schema\Key\IdentityGenerationStrategy;
15
16#[UsesClass(\ZtdQuery\Platform\MySql\Sql\OptionalInsertIntoNormalizer::class)]
17
18#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\DefinitionReader::class)]
19#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\TokenReader::class)]
20#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Partition\PredicateCompiler::class)]
21#[CoversClass(MySqlSchemaParser::class)]
22#[UsesClass(\ZtdQuery\Platform\MySql\Schema\MySqlColumnTypeMapper::class)]
23#[UsesClass(\ZtdQuery\Platform\MySql\Schema\Key\MySqlForeignKeyDefinitionParser::class)]
24#[UsesClass(MySqlParser::class)]
25#[UsesClass(MySqlPartitioningParser::class)]
26#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
27#[CoversClass(\ZtdQuery\Platform\MySql\Schema\DefinitionBuilder::class)]
28final class MySqlSchemaParserTest extends TestCase
29{
30 public function testParsesStoredAndVirtualGeneratedExpressions(): void
31 {
32 $definition = (new MySqlSchemaParser(new MySqlParser()))->parse(
33 'CREATE TABLE orders (qty INT, unit_price DECIMAL(10,2), '
34 . 'total DECIMAL(10,2) GENERATED ALWAYS AS (qty * unit_price) STORED, '
35 . 'label VARCHAR(255) AS (CONCAT(qty, unit_price)) VIRTUAL)',
36 );
37
38 self::assertNotNull($definition);
39 self::assertSame([
40 'total' => '(qty * unit_price)',
41 'label' => '(CONCAT(qty, unit_price))',
42 ], $definition->generatedExpressions);
43 }
44
45 public function testParsesPartitionMetadataWithTableSchema(): void
46 {
47 $definition = (new MySqlSchemaParser(new MySqlParser()))->parse(
48 'CREATE TABLE events (id INT, event_date DATE) '
49 . 'PARTITION BY RANGE (YEAR(event_date)) ('
50 . 'PARTITION p2024 VALUES LESS THAN (2025), '
51 . 'PARTITION pmax VALUES LESS THAN MAXVALUE)',
52 );
53
54 self::assertNotNull($definition);
55 self::assertSame(
56 ['(YEAR(event_date)) IS NULL OR (YEAR(event_date)) < 2025'],
57 $definition->partitioning?->predicatesFor(['p2024']),
58 );
59 }
60
61 public function testParseSimpleCreateTable(): void
62 {
63 $parser = new MySqlParser();
64 $schemaParser = new MySqlSchemaParser($parser);
65
66 $sql = 'CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL)';
67 $definition = $schemaParser->parse($sql);
68
69 self::assertNotNull($definition);
70 self::assertSame(['id', 'name'], $definition->columns);
71 self::assertSame(['id'], $definition->primaryKeys);
72 self::assertContains('id', $definition->notNullColumns);
73 }
74
75 public function testParseReturnsNullForNonCreateStatement(): void
76 {
77 $parser = new MySqlParser();
78 $schemaParser = new MySqlSchemaParser($parser);
79
80 $definition = $schemaParser->parse('SELECT 1');
81 self::assertNull($definition);
82 }
83
84 public function testParseReturnsNullForEmptySql(): void
85 {
86 $parser = new MySqlParser();
87 $schemaParser = new MySqlSchemaParser($parser);
88
89 $definition = $schemaParser->parse('');
90 self::assertNull($definition);
91 }
92
93 public function testParseCompositePrimaryKey(): void
94 {
95 $parser = new MySqlParser();
96 $schemaParser = new MySqlSchemaParser($parser);
97
98 $sql = 'CREATE TABLE order_items (order_id INT NOT NULL, product_id INT NOT NULL, quantity INT, PRIMARY KEY (order_id, product_id))';
99 $definition = $schemaParser->parse($sql);
100
101 self::assertNotNull($definition);
102 self::assertSame(['order_id', 'product_id'], $definition->primaryKeys);
103 }
104
105 public function testParseDoesNotTreatNamedForeignKeyAsColumn(): void
106 {
107 $parser = new MySqlParser();
108 $schemaParser = new MySqlSchemaParser($parser);
109 $sql = <<<'SQL'
110 CREATE TABLE child (
111 id INT PRIMARY KEY AUTO_INCREMENT,
112 parent_id INT NOT NULL,
113 label VARCHAR(50) NOT NULL,
114 CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id)
115 )
116 SQL;
117
118 $definition = $schemaParser->parse($sql);
119
120 self::assertNotNull($definition);
121 self::assertSame(['id', 'parent_id', 'label'], $definition->columns);
122 self::assertArrayNotHasKey('fk_parent', $definition->columnTypes);
123 self::assertArrayNotHasKey('fk_parent', $definition->typedColumns);
124 }
125
126 public function testParseDoesNotTreatNamedTableConstraintsAsColumns(): void
127 {
128 $parser = new MySqlParser();
129 $schemaParser = new MySqlSchemaParser($parser);
130 $sql = <<<'SQL'
131 CREATE TABLE child (
132 id INT,
133 parent_id INT,
134 CONSTRAINT pk_child PRIMARY KEY (id),
135 CONSTRAINT uq_parent UNIQUE (parent_id),
136 CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id),
137 CONSTRAINT ck_id CHECK (id > 0)
138 )
139 SQL;
140
141 $definition = $schemaParser->parse($sql);
142
143 self::assertNotNull($definition);
144 self::assertSame(['id', 'parent_id'], $definition->columns);
145 }
146
147 public function testParseUniqueConstraint(): void
148 {
149 $parser = new MySqlParser();
150 $schemaParser = new MySqlSchemaParser($parser);
151
152 $sql = 'CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE)';
153 $definition = $schemaParser->parse($sql);
154
155 self::assertNotNull($definition);
156 self::assertNotEmpty($definition->uniqueConstraints);
157 }
158
159 public function testParseColumnTypes(): void
160 {
161 $parser = new MySqlParser();
162 $schemaParser = new MySqlSchemaParser($parser);
163
164 $sql = 'CREATE TABLE t (a INT, b VARCHAR(255), c DECIMAL(10,2))';
165 $definition = $schemaParser->parse($sql);
166
167 self::assertNotNull($definition);
168 self::assertSame('INT', $definition->columnTypes['a']);
169 self::assertSame('VARCHAR(255)', $definition->columnTypes['b']);
170 self::assertSame('DECIMAL(10,2)', $definition->columnTypes['c']);
171 }
172
173 public function testParseTypedColumnsIntegerFamily(): void
174 {
175 $parser = new MySqlParser();
176 $schemaParser = new MySqlSchemaParser($parser);
177
178 $sql = 'CREATE TABLE t (a INT, b TINYINT, c SMALLINT, d MEDIUMINT, e BIGINT, f INTEGER)';
179 $definition = $schemaParser->parse($sql);
180
181 self::assertNotNull($definition);
182 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['a']->family);
183 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['b']->family);
184 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['c']->family);
185 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['d']->family);
186 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['e']->family);
187 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['f']->family);
188 }
189
190 public function testParseTypedColumnsDecimalFamily(): void
191 {
192 $parser = new MySqlParser();
193 $schemaParser = new MySqlSchemaParser($parser);
194
195 $sql = 'CREATE TABLE t (a DECIMAL(10,2), b NUMERIC(5,3))';
196 $definition = $schemaParser->parse($sql);
197
198 self::assertNotNull($definition);
199 self::assertSame(ColumnTypeFamily::DECIMAL, $definition->typedColumns['a']->family);
200 self::assertSame(ColumnTypeFamily::DECIMAL, $definition->typedColumns['b']->family);
201 }
202
203 public function testParseTypedColumnsFloatDoubleFamily(): void
204 {
205 $parser = new MySqlParser();
206 $schemaParser = new MySqlSchemaParser($parser);
207
208 $sql = 'CREATE TABLE t (a FLOAT, b DOUBLE, c REAL)';
209 $definition = $schemaParser->parse($sql);
210
211 self::assertNotNull($definition);
212 self::assertSame(ColumnTypeFamily::FLOAT, $definition->typedColumns['a']->family);
213 self::assertSame(ColumnTypeFamily::DOUBLE, $definition->typedColumns['b']->family);
214 self::assertSame(ColumnTypeFamily::DOUBLE, $definition->typedColumns['c']->family);
215 }
216
217 public function testParseTypedColumnsBooleanFamily(): void
218 {
219 $parser = new MySqlParser();
220 $schemaParser = new MySqlSchemaParser($parser);
221
222 $sql = 'CREATE TABLE t (a BOOL, b BOOLEAN)';
223 $definition = $schemaParser->parse($sql);
224
225 self::assertNotNull($definition);
226 self::assertSame(ColumnTypeFamily::BOOLEAN, $definition->typedColumns['a']->family);
227 self::assertSame(ColumnTypeFamily::BOOLEAN, $definition->typedColumns['b']->family);
228 }
229
230 public function testParseTypedColumnsDatetimeFamily(): void
231 {
232 $parser = new MySqlParser();
233 $schemaParser = new MySqlSchemaParser($parser);
234
235 $sql = 'CREATE TABLE t (a DATE, b TIME, c DATETIME, d TIMESTAMP)';
236 $definition = $schemaParser->parse($sql);
237
238 self::assertNotNull($definition);
239 self::assertSame(ColumnTypeFamily::DATE, $definition->typedColumns['a']->family);
240 self::assertSame(ColumnTypeFamily::TIME, $definition->typedColumns['b']->family);
241 self::assertSame(ColumnTypeFamily::DATETIME, $definition->typedColumns['c']->family);
242 self::assertSame(ColumnTypeFamily::TIMESTAMP, $definition->typedColumns['d']->family);
243 }
244
245 public function testParseTypedColumnsJsonFamily(): void
246 {
247 $parser = new MySqlParser();
248 $schemaParser = new MySqlSchemaParser($parser);
249
250 $sql = 'CREATE TABLE t (a JSON)';
251 $definition = $schemaParser->parse($sql);
252
253 self::assertNotNull($definition);
254 self::assertSame(ColumnTypeFamily::JSON, $definition->typedColumns['a']->family);
255 }
256
257 public function testParseTypedColumnsBinaryFamily(): void
258 {
259 $parser = new MySqlParser();
260 $schemaParser = new MySqlSchemaParser($parser);
261
262 $sql = 'CREATE TABLE t (a BINARY, b VARBINARY(255), c BLOB, d TINYBLOB, e MEDIUMBLOB, f LONGBLOB)';
263 $definition = $schemaParser->parse($sql);
264
265 self::assertNotNull($definition);
266 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['a']->family);
267 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['b']->family);
268 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['c']->family);
269 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['d']->family);
270 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['e']->family);
271 self::assertSame(ColumnTypeFamily::BINARY, $definition->typedColumns['f']->family);
272 }
273
274 public function testParseTypedColumnsStringFamily(): void
275 {
276 $parser = new MySqlParser();
277 $schemaParser = new MySqlSchemaParser($parser);
278
279 $sql = "CREATE TABLE t (a CHAR(10), b VARCHAR(255), c ENUM('a','b'), d SET('x','y'))";
280 $definition = $schemaParser->parse($sql);
281
282 self::assertNotNull($definition);
283 self::assertSame(ColumnTypeFamily::STRING, $definition->typedColumns['a']->family);
284 self::assertSame(ColumnTypeFamily::STRING, $definition->typedColumns['b']->family);
285 self::assertSame(ColumnTypeFamily::STRING, $definition->typedColumns['c']->family);
286 self::assertSame(ColumnTypeFamily::STRING, $definition->typedColumns['d']->family);
287 }
288
289 public function testParseTypedColumnsTextFamily(): void
290 {
291 $parser = new MySqlParser();
292 $schemaParser = new MySqlSchemaParser($parser);
293
294 $sql = 'CREATE TABLE t (a TEXT, b TINYTEXT, c MEDIUMTEXT, d LONGTEXT)';
295 $definition = $schemaParser->parse($sql);
296
297 self::assertNotNull($definition);
298 self::assertSame(ColumnTypeFamily::TEXT, $definition->typedColumns['a']->family);
299 self::assertSame(ColumnTypeFamily::TEXT, $definition->typedColumns['b']->family);
300 self::assertSame(ColumnTypeFamily::TEXT, $definition->typedColumns['c']->family);
301 self::assertSame(ColumnTypeFamily::TEXT, $definition->typedColumns['d']->family);
302 }
303
304 public function testParseTypedColumnsYearAndBit(): void
305 {
306 $parser = new MySqlParser();
307 $schemaParser = new MySqlSchemaParser($parser);
308
309 $sql = 'CREATE TABLE t (a YEAR, b BIT)';
310 $definition = $schemaParser->parse($sql);
311
312 self::assertNotNull($definition);
313 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['a']->family);
314 self::assertSame(ColumnTypeFamily::INTEGER, $definition->typedColumns['b']->family);
315 }
316
317 public function testParseTypedColumnsNativeType(): void
318 {
319 $parser = new MySqlParser();
320 $schemaParser = new MySqlSchemaParser($parser);
321
322 $sql = 'CREATE TABLE t (a DECIMAL(10,2))';
323 $definition = $schemaParser->parse($sql);
324
325 self::assertNotNull($definition);
326 self::assertSame('DECIMAL(10,2)', $definition->typedColumns['a']->nativeType);
327 }
328
329 public function testParseNotNullColumn(): void
330 {
331 $parser = new MySqlParser();
332 $schemaParser = new MySqlSchemaParser($parser);
333
334 $sql = 'CREATE TABLE t (a INT NOT NULL, b INT)';
335 $definition = $schemaParser->parse($sql);
336
337 self::assertNotNull($definition);
338 self::assertContains('a', $definition->notNullColumns);
339 self::assertNotContains('b', $definition->notNullColumns);
340 }
341
342 public function testParsePrimaryKeyImpliesNotNull(): void
343 {
344 $parser = new MySqlParser();
345 $schemaParser = new MySqlSchemaParser($parser);
346
347 $sql = 'CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(255))';
348 $definition = $schemaParser->parse($sql);
349
350 self::assertNotNull($definition);
351 self::assertContains('id', $definition->notNullColumns);
352 self::assertNotContains('name', $definition->notNullColumns);
353 }
354
355 public function testParseUniqueKeyConstraint(): void
356 {
357 $parser = new MySqlParser();
358 $schemaParser = new MySqlSchemaParser($parser);
359
360 $sql = 'CREATE TABLE t (id INT, email VARCHAR(255), UNIQUE KEY uk_email (email))';
361 $definition = $schemaParser->parse($sql);
362
363 self::assertNotNull($definition);
364 self::assertArrayHasKey('uk_email', $definition->uniqueConstraints);
365 self::assertSame(['email'], $definition->uniqueConstraints['uk_email']);
366 }
367
368 public function testParseUniqueWithoutName(): void
369 {
370 $parser = new MySqlParser();
371 $schemaParser = new MySqlSchemaParser($parser);
372
373 $sql = 'CREATE TABLE t (id INT, email VARCHAR(255), UNIQUE (email))';
374 $definition = $schemaParser->parse($sql);
375
376 self::assertNotNull($definition);
377 self::assertNotEmpty($definition->uniqueConstraints);
378 }
379
380 public function testParseUnknownTypeFamily(): void
381 {
382 $parser = new MySqlParser();
383 $schemaParser = new MySqlSchemaParser($parser);
384
385 $sql = 'CREATE TABLE t (a GEOMETRY)';
386 $definition = $schemaParser->parse($sql);
387
388 self::assertNotNull($definition);
389 self::assertSame(ColumnTypeFamily::UNKNOWN, $definition->typedColumns['a']->family);
390 }
391
392 public function testParseColumnTypeWithBackticks(): void
393 {
394 $parser = new MySqlParser();
395 $schemaParser = new MySqlSchemaParser($parser);
396
397 $sql = 'CREATE TABLE t (`id` INT, `name` VARCHAR(255))';
398 $definition = $schemaParser->parse($sql);
399
400 self::assertNotNull($definition);
401 self::assertSame(['id', 'name'], $definition->columns);
402 }
403
404 public function testParseNativeTypeIncludesParameters(): void
405 {
406 $parser = new MySqlParser();
407 $schemaParser = new MySqlSchemaParser($parser);
408
409 $sql = 'CREATE TABLE t (a VARCHAR(100), b INT)';
410 $definition = $schemaParser->parse($sql);
411
412 self::assertNotNull($definition);
413 self::assertSame('VARCHAR(100)', $definition->typedColumns['a']->nativeType);
414 self::assertSame('INT', $definition->typedColumns['b']->nativeType);
415 }
416
417 public function testParseColumnNameStripsBackticks(): void
418 {
419 $parser = new MySqlParser();
420 $schemaParser = new MySqlSchemaParser($parser);
421
422 $sql = 'CREATE TABLE t (`my_col` INT)';
423 $definition = $schemaParser->parse($sql);
424
425 self::assertNotNull($definition);
426 self::assertSame(['my_col'], $definition->columns);
427 self::assertArrayHasKey('my_col', $definition->columnTypes);
428 self::assertArrayHasKey('my_col', $definition->typedColumns);
429 }
430
431 public function testParsePrimaryKeyColumnNameStripsBackticks(): void
432 {
433 $parser = new MySqlParser();
434 $schemaParser = new MySqlSchemaParser($parser);
435
436 $sql = 'CREATE TABLE t (`id` INT, PRIMARY KEY (`id`))';
437 $definition = $schemaParser->parse($sql);
438
439 self::assertNotNull($definition);
440 self::assertContains('id', $definition->primaryKeys);
441 }
442
443 public function testParseUniqueKeyColumnNameStripsBackticks(): void
444 {
445 $parser = new MySqlParser();
446 $schemaParser = new MySqlSchemaParser($parser);
447
448 $sql = 'CREATE TABLE t (`id` INT, `email` VARCHAR(255), UNIQUE KEY uk (`email`))';
449 $definition = $schemaParser->parse($sql);
450
451 self::assertNotNull($definition);
452 self::assertNotEmpty($definition->uniqueConstraints);
453 $matches = array_filter($definition->uniqueConstraints, static fn (array $cols): bool => in_array('email', $cols, true));
454 self::assertNotEmpty($matches);
455 }
456
457 public function testParseUniqueColumnOption(): void
458 {
459 $parser = new MySqlParser();
460 $schemaParser = new MySqlSchemaParser($parser);
461
462 $sql = 'CREATE TABLE t (id INT PRIMARY KEY, code VARCHAR(10) UNIQUE)';
463 $definition = $schemaParser->parse($sql);
464
465 self::assertNotNull($definition);
466 $matches = array_filter($definition->uniqueConstraints, static fn (array $cols): bool => in_array('code', $cols, true));
467 self::assertNotEmpty($matches);
468 self::assertArrayHasKey('code_UNIQUE', $matches);
469 }
470
471 public function testParseNotNullWithPrimaryKeyImplied(): void
472 {
473 $parser = new MySqlParser();
474 $schemaParser = new MySqlSchemaParser($parser);
475
476 $sql = 'CREATE TABLE t (id INT PRIMARY KEY NOT NULL, name TEXT)';
477 $definition = $schemaParser->parse($sql);
478
479 self::assertNotNull($definition);
480 self::assertContains('id', $definition->notNullColumns);
481 $count = count(array_filter($definition->notNullColumns, static fn (string $c): bool => $c === 'id'));
482 self::assertSame(1, $count);
483 }
484
485 public function testParseTypeNameUppercased(): void
486 {
487 $parser = new MySqlParser();
488 $schemaParser = new MySqlSchemaParser($parser);
489
490 $sql = 'CREATE TABLE t (a int, b varchar(50))';
491 $definition = $schemaParser->parse($sql);
492
493 self::assertNotNull($definition);
494 self::assertSame('INT', $definition->columnTypes['a']);
495 self::assertSame('VARCHAR(50)', $definition->columnTypes['b']);
496 }
497
498 public function testParseMultipleUnnamedUniqueConstraints(): void
499 {
500 $parser = new MySqlParser();
501 $schemaParser = new MySqlSchemaParser($parser);
502
503 $sql = 'CREATE TABLE t (id INT, a INT, b INT, UNIQUE (a), UNIQUE (b))';
504 $definition = $schemaParser->parse($sql);
505
506 self::assertNotNull($definition);
507 self::assertCount(2, $definition->uniqueConstraints);
508 $keys = array_keys($definition->uniqueConstraints);
509 self::assertNotSame($keys[0], $keys[1]);
510 self::assertStringContainsString('unique_', $keys[0]);
511 self::assertStringContainsString('unique_', $keys[1]);
512 }
513
514 public function testParseCompositeUniqueKey(): void
515 {
516 $parser = new MySqlParser();
517 $schemaParser = new MySqlSchemaParser($parser);
518
519 $sql = 'CREATE TABLE t (a INT, b INT, UNIQUE KEY uq_ab (a, b))';
520 $definition = $schemaParser->parse($sql);
521
522 self::assertNotNull($definition);
523 self::assertArrayHasKey('uq_ab', $definition->uniqueConstraints);
524 self::assertSame(['a', 'b'], $definition->uniqueConstraints['uq_ab']);
525 }
526
527 public function testParsePrimaryKeyBacktickStripping(): void
528 {
529 $parser = new MySqlParser();
530 $schemaParser = new MySqlSchemaParser($parser);
531
532 $sql = 'CREATE TABLE t (`col_a` INT, `col_b` INT, PRIMARY KEY (`col_a`, `col_b`))';
533 $definition = $schemaParser->parse($sql);
534
535 self::assertNotNull($definition);
536 self::assertSame(['col_a', 'col_b'], $definition->primaryKeys);
537 self::assertNotContains('`col_a`', $definition->primaryKeys);
538 }
539
540 public function testParseUniqueKeyBacktickStripping(): void
541 {
542 $parser = new MySqlParser();
543 $schemaParser = new MySqlSchemaParser($parser);
544
545 $sql = 'CREATE TABLE t (`id` INT, `email` VARCHAR(255), UNIQUE KEY uk_email (`email`))';
546 $definition = $schemaParser->parse($sql);
547
548 self::assertNotNull($definition);
549 self::assertArrayHasKey('uk_email', $definition->uniqueConstraints);
550 self::assertSame(['email'], $definition->uniqueConstraints['uk_email']);
551 self::assertNotContains('`email`', $definition->uniqueConstraints['uk_email']);
552 }
553
554 public function testParseColumnNameBacktickStrippedFromColumnTypes(): void
555 {
556 $parser = new MySqlParser();
557 $schemaParser = new MySqlSchemaParser($parser);
558
559 $sql = 'CREATE TABLE t (`my_id` INT)';
560 $definition = $schemaParser->parse($sql);
561
562 self::assertNotNull($definition);
563 self::assertArrayHasKey('my_id', $definition->columnTypes);
564 self::assertArrayNotHasKey('`my_id`', $definition->columnTypes);
565 }
566
567 public function testParseTypeUppercasedInFamilyMapping(): void
568 {
569 $parser = new MySqlParser();
570 $schemaParser = new MySqlSchemaParser($parser);
571
572 $sql = 'CREATE TABLE t (a varchar(100))';
573 $definition = $schemaParser->parse($sql);
574
575 self::assertNotNull($definition);
576 self::assertSame(ColumnTypeFamily::STRING, $definition->typedColumns['a']->family);
577 self::assertSame('VARCHAR(100)', $definition->typedColumns['a']->nativeType);
578 }
579
580 public function testParseMultipleUnnamedUniqueConstraintsKeyNamesSequential(): void
581 {
582 $parser = new MySqlParser();
583 $schemaParser = new MySqlSchemaParser($parser);
584
585 $sql = 'CREATE TABLE t (id INT, a INT, b INT, UNIQUE (a), UNIQUE (b))';
586 $definition = $schemaParser->parse($sql);
587
588 self::assertNotNull($definition);
589 $keys = array_keys($definition->uniqueConstraints);
590 self::assertSame('unique_0', $keys[0]);
591 self::assertSame('unique_1', $keys[1]);
592 }
593
594 public function testConstraintKeywordPrefixesRemainColumnNames(): void
595 {
596 $schemaParser = new MySqlSchemaParser(new MySqlParser());
597 $sql = 'CREATE TABLE bookings (id INT, check_in TEXT, checkout_date TEXT, unique_value TEXT, constraint_name TEXT, foreign_key_id INT)';
598 $definition = $schemaParser->parse($sql);
599
600 self::assertNotNull($definition);
601 self::assertSame(
602 ['id', 'check_in', 'checkout_date', 'unique_value', 'constraint_name', 'foreign_key_id'],
603 $definition->columns,
604 );
605 }
606
607 public function testParsePreservesColumnDefaultExpressions(): void
608 {
609 $schemaParser = new MySqlSchemaParser(new MySqlParser());
610 $definition = $schemaParser->parse(
611 "CREATE TABLE t (id INT DEFAULT 7, status ENUM('new','done') DEFAULT 'new', label VARCHAR(20) DEFAULT (concat('a','b')))"
612 );
613
614 self::assertNotNull($definition);
615 self::assertSame([
616 'id' => '7',
617 'status' => "'new'",
618 'label' => "(concat('a','b'))",
619 ], $definition->columnDefaults);
620 }
621
622 public function testParseAutoIncrementIdentityStrategy(): void
623 {
624 $definition = (new MySqlSchemaParser(new MySqlParser()))->parse(
625 'CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))'
626 );
627
628 self::assertNotNull($definition);
629 self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
630 }
631
632 public function testParsePreservesCompositeForeignKeyActions(): void
633 {
634 $definition = (new MySqlSchemaParser(new MySqlParser()))->parse(
635 'CREATE TABLE child (tenant_id INT, parent_id INT, CONSTRAINT `fk_parent` '
636 . 'FOREIGN KEY (tenant_id, parent_id) REFERENCES `parents` (tenant_id, id) '
637 . 'ON DELETE CASCADE ON UPDATE CASCADE)'
638 );
639
640 self::assertNotNull($definition);
641 self::assertSame(['fk_parent'], array_keys($definition->foreignKeys));
642 self::assertSame(['tenant_id', 'parent_id'], $definition->foreignKeys['fk_parent']->columns);
643 self::assertSame(['tenant_id', 'id'], $definition->foreignKeys['fk_parent']->referencedColumns);
644 self::assertSame('CASCADE', $definition->foreignKeys['fk_parent']->onDelete->value);
645 self::assertSame('CASCADE', $definition->foreignKeys['fk_parent']->onUpdate->value);
646 }
647
648 public function testValidCreateTableReturnsNonNull(): void
649 {
650 $parser = new MySqlSchemaParser(new MySqlParser());
651 $result = $parser->parse(<<<'SQL'
652 CREATE TABLE users (
653 id INT NOT NULL AUTO_INCREMENT,
654 name VARCHAR(255) NOT NULL,
655 email VARCHAR(255) NOT NULL,
656 PRIMARY KEY (id),
657 UNIQUE KEY email_unique (email)
658 )
659 SQL);
660 self::assertNotNull($result);
661 }
662
663 public function testNonCreateTableReturnsNull(): void
664 {
665 $parser = new MySqlSchemaParser(new MySqlParser());
666 $result = $parser->parse('SELECT * FROM users WHERE id = 1');
667 self::assertNull($result);
668 }
669
670 public function testPrimaryKeysSubsetOfColumns(): void
671 {
672 $parser = new MySqlSchemaParser(new MySqlParser());
673 $definition = $parser->parse(<<<'SQL'
674 CREATE TABLE users (
675 id INT NOT NULL AUTO_INCREMENT,
676 name VARCHAR(255) NOT NULL,
677 email VARCHAR(255) NOT NULL,
678 PRIMARY KEY (id),
679 UNIQUE KEY email_unique (email)
680 )
681 SQL);
682 self::assertNotNull($definition);
683 self::assertSame([], array_diff($definition->primaryKeys, $definition->columns));
684 }
685
686 public function testNotNullSubsetOfColumns(): void
687 {
688 $parser = new MySqlSchemaParser(new MySqlParser());
689 $definition = $parser->parse(<<<'SQL'
690 CREATE TABLE users (
691 id INT NOT NULL AUTO_INCREMENT,
692 name VARCHAR(255) NOT NULL,
693 email VARCHAR(255) NOT NULL,
694 PRIMARY KEY (id),
695 UNIQUE KEY email_unique (email)
696 )
697 SQL);
698 self::assertNotNull($definition);
699 self::assertSame([], array_diff($definition->notNullColumns, $definition->columns));
700 }
701
702 public function testColumnTypesKeysSubsetOfColumns(): void
703 {
704 $parser = new MySqlSchemaParser(new MySqlParser());
705 $definition = $parser->parse(<<<'SQL'
706 CREATE TABLE users (
707 id INT NOT NULL AUTO_INCREMENT,
708 name VARCHAR(255) NOT NULL,
709 email VARCHAR(255) NOT NULL,
710 PRIMARY KEY (id),
711 UNIQUE KEY email_unique (email)
712 )
713 SQL);
714 self::assertNotNull($definition);
715 self::assertSame([], array_diff(array_keys($definition->columnTypes), $definition->columns));
716 }
717
718 public function testUniqueConstraintColumnsSubsetOfColumns(): void
719 {
720 $parser = new MySqlSchemaParser(new MySqlParser());
721 $definition = $parser->parse(<<<'SQL'
722 CREATE TABLE users (
723 id INT NOT NULL AUTO_INCREMENT,
724 name VARCHAR(255) NOT NULL,
725 email VARCHAR(255) NOT NULL,
726 PRIMARY KEY (id),
727 UNIQUE KEY email_unique (email)
728 )
729 SQL);
730 self::assertNotNull($definition);
731 self::assertSame(['email_unique' => ['email']], $definition->uniqueConstraints);
732 self::assertSame([], array_diff(array_merge(...array_values($definition->uniqueConstraints)), $definition->columns));
733 }
734
735 public function testParsedDefinitionHasNonEmptyColumns(): void
736 {
737 $parser = new MySqlSchemaParser(new MySqlParser());
738 $definition = $parser->parse(<<<'SQL'
739 CREATE TABLE users (
740 id INT NOT NULL AUTO_INCREMENT,
741 name VARCHAR(255) NOT NULL,
742 email VARCHAR(255) NOT NULL,
743 PRIMARY KEY (id),
744 UNIQUE KEY email_unique (email)
745 )
746 SQL);
747 self::assertNotNull($definition);
748 self::assertNotEmpty($definition->columns);
749 }
750
751 public function testParsedColumnsMatchExpected(): void
752 {
753 $parser = new MySqlSchemaParser(new MySqlParser());
754 $definition = $parser->parse(<<<'SQL'
755 CREATE TABLE users (
756 id INT NOT NULL AUTO_INCREMENT,
757 name VARCHAR(255) NOT NULL,
758 email VARCHAR(255) NOT NULL,
759 PRIMARY KEY (id),
760 UNIQUE KEY email_unique (email)
761 )
762 SQL);
763 self::assertNotNull($definition);
764 self::assertSame(['id', 'name', 'email'], $definition->columns, 'Parsed column names must match expected columns in order');
765 }
766
767 public function testParsedPrimaryKeysMatchExpected(): void
768 {
769 $parser = new MySqlSchemaParser(new MySqlParser());
770 $definition = $parser->parse(<<<'SQL'
771 CREATE TABLE users (
772 id INT NOT NULL AUTO_INCREMENT,
773 name VARCHAR(255) NOT NULL,
774 email VARCHAR(255) NOT NULL,
775 PRIMARY KEY (id),
776 UNIQUE KEY email_unique (email)
777 )
778 SQL);
779 self::assertNotNull($definition);
780 self::assertSame(['id'], $definition->primaryKeys, 'Parsed primary keys must match expected primary keys');
781 }
782
783 public function testParsedNotNullColumnsMatchExpected(): void
784 {
785 $parser = new MySqlSchemaParser(new MySqlParser());
786 $definition = $parser->parse(<<<'SQL'
787 CREATE TABLE users (
788 id INT NOT NULL AUTO_INCREMENT,
789 name VARCHAR(255) NOT NULL,
790 email VARCHAR(255) NOT NULL,
791 PRIMARY KEY (id),
792 UNIQUE KEY email_unique (email)
793 )
794 SQL);
795 self::assertNotNull($definition);
796 self::assertSame([], array_diff(['id', 'name', 'email'], $definition->notNullColumns));
797 }
798
799 public function testColumnCountMatchesExpected(): void
800 {
801 $parser = new MySqlSchemaParser(new MySqlParser());
802 $definition = $parser->parse(<<<'SQL'
803 CREATE TABLE users (
804 id INT NOT NULL AUTO_INCREMENT,
805 name VARCHAR(255) NOT NULL,
806 email VARCHAR(255) NOT NULL,
807 PRIMARY KEY (id),
808 UNIQUE KEY email_unique (email)
809 )
810 SQL);
811 self::assertNotNull($definition);
812 self::assertCount(3, $definition->columns, 'Column count must match expected');
813 }
814
815 public function testMalformedInputReturnsNull(): void
816 {
817 $parser = new MySqlSchemaParser(new MySqlParser());
818 $result = $parser->parse('NOT VALID SQL AT ALL %%%');
819 self::assertNull($result);
820 }
821
822 public function testTypedColumnsKeysSubsetOfColumns(): void
823 {
824 $parser = new MySqlSchemaParser(new MySqlParser());
825 $definition = $parser->parse(<<<'SQL'
826 CREATE TABLE users (
827 id INT NOT NULL AUTO_INCREMENT,
828 name VARCHAR(255) NOT NULL,
829 email VARCHAR(255) NOT NULL,
830 PRIMARY KEY (id),
831 UNIQUE KEY email_unique (email)
832 )
833 SQL);
834 self::assertNotNull($definition);
835 self::assertSame([], array_diff(array_keys($definition->typedColumns), $definition->columns));
836 }
837}
838