packages/sql-semantics-mysql/tests/Unit/PlatformTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit;
6
7use PHPUnit\Framework\TestCase;
8use SqlParser\Lexer\Token;
9use SqlSemantics\Core\Binder;
10use SqlSemantics\Core\Model\Expression;
11use SqlSemantics\Core\Model\ExpressionKind;
12use SqlSemantics\Core\SchemaBuilder;
13use SqlSemantics\Core\Type\Nullability;
14use SqlSemantics\Core\Type\TypeDescriptor;
15use SqlSemantics\Platform\MySql\Dialect;
16
17#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\SemanticException::class)]
18#[\PHPUnit\Framework\Attributes\CoversClass(SchemaBuilder::class)]
19#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Schema::class)]
20#[\PHPUnit\Framework\Attributes\CoversClass(Binder::class)]
21#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\NullFacts::class)]
22#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
23#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\IdentitySequence::class)]
24#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
25#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\Scope::class)]
26#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\BoundRelation::class)]
27#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\FromBinder::class)]
28#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
29#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\TableResolver::class)]
30#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\SelectBinder::class)]
31#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::class)]
32#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\ExpressionRules::class)]
33#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\ExpressionBinder::class)]
34#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Binding\LiteralBinder::class)]
35#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Schema\ColumnDefinition::class)]
36#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Schema\TableDefinition::class)]
37#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Schema\ConstraintKind::class)]
38#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Schema\TableConstraint::class)]
39#[\PHPUnit\Framework\Attributes\CoversClass(Nullability::class)]
40#[\PHPUnit\Framework\Attributes\CoversClass(TypeDescriptor::class)]
41#[\PHPUnit\Framework\Attributes\CoversClass(Expression::class)]
42#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\Join::class)]
43#[\PHPUnit\Framework\Attributes\CoversClass(ExpressionKind::class)]
44#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\BoundSelect::class)]
45#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\TableUse::class)]
46#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\ColumnBinding::class)]
47#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\Ordering::class)]
48#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\OutputColumn::class)]
49#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Model\JoinKind::class)]
50#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\TypeReader::class)]
51#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
52#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\TokenGroups::class)]
53#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\Tree::class)]
54#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
55#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\SchemaReader::class)]
56#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
57#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\Identifiers::class)]
58#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Ast\StatementList::class)]
59#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Core\Policy\SyntaxRules::class)]
60#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Platform\MySql\QueryRules::class)]
61#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Platform\MySql\Platform::class)]
62#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Platform\MySql\TypeRules::class)]
63#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Platform\MySql\NameRules::class)]
64#[\PHPUnit\Framework\Attributes\CoversClass(\SqlSemantics\Platform\MySql\SchemaRules::class)]
65#[\PHPUnit\Framework\Attributes\CoversClass(Dialect::class)]
66#[\PHPUnit\Framework\Attributes\Medium]
67#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Analysis\ValueReader::class)]
68#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Statement\Statement::class)]
69#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Statement\Writer::class)]
70#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Statement\Element::class)]
71#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Facade\Semantics::class)]
72#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Analysis\Analyzer::class)]
73#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\AnalysisException::class)]
74final class PlatformTest extends TestCase
75{
76 public function testParserPreservesTheSelectedVersion(): void
77 {
78 $parser = Dialect::MySql->platform()->parser();
79 self::assertNotSame('', $parser->version());
80 self::assertSame('SELECT 1', $parser->parse('SELECT 1')->toString());
81 }
82
83 public function testDefaultSchemaUsesTheLanguageNamespace(): void
84 {
85 self::assertSame('', Dialect::MySql->platform()->defaultSchema());
86 }
87
88 public function testStatementNamesIdentifyTheParserRoot(): void
89 {
90 self::assertSame(Dialect::MySql->platform()->statementNames()[0], Dialect::MySql->platform()->parser()->parse('SELECT 1')->name);
91 }
92
93 public function testSyntaxRecognizesSelectBody(): void
94 {
95 $tree = Dialect::MySql->platform()->parser()->parse('SELECT 1');
96 self::assertNotEmpty(\SqlSemantics\Core\Ast\Tree::outer($tree, Dialect::MySql->platform()->syntax()->nodes('selectBody')));
97 }
98
99 public function testNamesDecodeQuotedIdentifiers(): void
100 {
101 self::assertSame('Mixed', Dialect::MySql->platform()->names()->name(new Token(1, 'ID', '"Mixed"', 0)));
102 }
103
104 public function testTypesRetainDialectIdentity(): void
105 {
106 self::assertSame(Dialect::MySql, Dialect::MySql->platform()->types()->boolean()->dialect);
107 }
108
109 public function testSchemaKeepsDeclarations(): void
110 {
111 $schema = (new SchemaBuilder(Dialect::MySql))->build('CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT)');
112 self::assertCount(2, $schema->tables[0]->columns);
113 }
114
115 public function testQueryKeepsProjectionOrder(): void
116 {
117 $schema = (new SchemaBuilder(Dialect::MySql))->build('CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT)');
118 $bound = (new Binder($schema))->bind('SELECT label, id FROM items');
119 self::assertSame(['label', 'id'], array_column($bound->outputs, 'name'));
120 }
121 public function testValuesReconstructsUsingTheParserRelease(): void
122 {
123 $platform = Dialect::MySql->platform();
124 $parser = $platform->parser();
125 $value = $platform->values($parser->version())->read($parser->parse('SELECT 42'));
126 self::assertInstanceOf(\SqlSemantics\Statement\Command::class, $value);
127 self::assertSame('SELECT 42', (new \SqlSemantics\Statement\Statement($value))->toString());
128 }
129
130 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'CREATE LOGFILE GROUP logs ADD UNDOFILE \'undo.dat\''])]
131 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'SET DEFAULT .some_variable = DEFAULT'])]
132 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'SHOW COUNT( * ) ERRORS'])]
133 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'CREATE TABLE count (id INT)'])]
134 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'SELECT count (1), COUNT(*) FROM count'])]
135 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'CREATE PROCEDURE p() BEGIN DECLARE n INT DEFAULT 1; WHILE n < 3 DO SET n = n + 1; END WHILE; END'])]
136 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n<4) SELECT SUM(n) OVER (ORDER BY n) FROM t'])]
137 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'INSERT INTO t (id, name) VALUES (1, \'a\') ON DUPLICATE KEY UPDATE name = VALUES(name)'])]
138 #[\PHPUnit\Framework\Attributes\TestWith([Dialect::MySql, 'SELECT `select`.id, @@global.sql_mode FROM db.`from`'])]
139 public function testAnalyzeRoundTripsCompleteStatements(Dialect $dialect, string $sql): void
140 {
141 $statement = (new \SqlSemantics\Facade\Semantics($dialect))->analyze($sql);
142 $formatter = new \SqlFormatter\Facade\Formatter($dialect->platform()->parser(), new \SqlFormatter\Core\FormatOptions(\SqlFormatter\Core\Style::Compact));
143 self::assertSame($formatter->format($sql), $formatter->format($statement->toString()));
144 }
145
146 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.6.51'])]
147 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.7.44'])]
148 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-8.0.44'])]
149 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-8.1.0'])]
150 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-8.2.0'])]
151 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-8.3.0'])]
152 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-8.4.7'])]
153 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-9.0.1'])]
154 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-9.1.0'])]
155 public function testAnalyzeWithEveryGrammarRelease(string $version): void
156 {
157 $statement = (new \SqlSemantics\Facade\Semantics(Dialect::MySql, $version))->analyze('DELETE FROM absent_table WHERE id = 1');
158 self::assertSame('DELETE FROM absent_table WHERE id = 1', $statement->toString());
159 }
160
161 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.6.51', 'ALTER PROCEDURE ACTION .some_name'])]
162 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.7.44', 'ALTER DEFINER = \'text\' EVENT SQL_AFTER_GTIDS .some_name RENAME TO ACTION'])]
163 public function testAnalyzePreservesKeywordNamesBeforeDots(string $version, string $sql): void
164 {
165 $statement = (new \SqlSemantics\Facade\Semantics(Dialect::MySql, $version))->analyze($sql);
166 $formatter = new \SqlFormatter\Facade\Formatter(Dialect::MySql->platform()->parser($version), new \SqlFormatter\Core\FormatOptions(\SqlFormatter\Core\Style::Compact));
167 self::assertSame($formatter->format($sql), $formatter->format($statement->toString()));
168 }
169
170 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.6.51', 'statement', "\x18\x00\x00\x00\x00\x00\x15\x00\x01\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x03\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x00"])]
171 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-9.1.0', 'simple_statement_or_begin', "\x18\x00\x00\x00\x00\x00\x15\x00\x01\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x03\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x02\x00\x00\x00\x00\x00\x00\x00\x00"])]
172 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.6.51', 'statement', "\x9e\x06\x00\x00\xeb\x25\x4b\xc3\xea\xf2\xf0\x9e\xea\x25\xaa\x39\x51\x9f\xcc\x54\xff\x01\xfc\xb4\x51\x47\x87\x7b\xbb\xc2\x02\xcd\x5f\x6a\xad\xb6\x44\xdd\x96\xe3\xcf\x41\x38\x8f\x2c\x08\x5e\x0f\xe3\xad\xf7\x0c\x1c\x33\x23\x51\x3a\x6d\x8e\x94\x93\xe0\x44\xee\xb4\xcf\x91\x7b\x27\x3f\x2d\xf4\x55\x47\x69\x9a\xf7\x95\x32\xfe\xc4\xb5\x4c\x6d\xb4\x26\x26\x34\xe0\xc3\x55\xa4\xef\xe2\x05\x62\x35\xc2\x72\x64\xfe\x16\x23\x2d\xe6\x0d\x5a\xbf\xa8\x33\xa9\xd0\x84\x33\xe8\x4a\x6c\x20\x39\x62\x48\x97\x0c\xf0\xb6\xae\x15\xe4\x5a\xb4\xbb\x17"])]
173 #[\PHPUnit\Framework\Attributes\TestWith(['mysql-5.6.51', 'statement', "\x95\x00\x00\x00\x99\x5f\xa8\xe1\x87\x89\xea\xd8\xbc\x44\x06\xa3\x59\x3f\xf3\xeb\xb7\xea\xab\xb0\x5d\xbd\xd4\x75\xe0\xb2\x2a\xe5\x99\xa4\x47\x4e\x6e\xd9\xa1\x1b\x14\xef\x57\xc5\x27\x38\x7c\xf3\x27\x77\x4d\x0f\xd5\xb6\xee\x61\x0e\xfe\x39\xf8\xe4\xbc\x82\x41\x5e\xa8\xff\xb5\x02\x1c\x9d\x65\xf3\x77\xf0\xe7\x24\xc5\xf1\x0e\x89\x11\xd6\x37\xe9\xf9\x97\x0a\x18\x87\xc1\x94\x27\x74\x94\xd1\x05\x33\xaf\xf3\x0d\x85\xa0\x74\x95\x1f\x97\xe2\x5b\xaa\x35\x7f\x6c\x72\xf9\x8d\x19\x13\x6b\x15\x9f\x84\x69\x8d\xe8\x42\xfd\x02\x8e\x5b\xf3\x81\x73\xc6\x60\xeb\xa8\x34\xf6\xc9\xd1\xf8\x42\x1e\xe6\xbe\xf4\x77\x21\x24\x73\xed\xf9\x16\x68\x10\xf7\xcd\xe8\x1f\x44\xae\x3c\x3d\xb1\x42\x17\x58\x24\xa6\x67\xee\xaf\x90\x79\xb3\x29\xf1\x5e\x89\x9f\xae\x3e\x19\x3a\x48\x0e\x03\xdd\x6f\xcc\xbd\xe0\x7c\xef\xe5\x19\x0e\x84\x0d\x48\x63\xf9\xd9\x02\x4a\x96\xf9\x23\x1c\xb3\xca\xd4\xb2\x2e\xc1\xbe\xf5\x42\x93\xfa\x1a\xe0\xa8\x2b\xb6\xbb\x1c\xcc\x6f\x6a\x90\x70\x46\x43\x3b\x0a\x2e\x3b\x18\x1d\x40\xa5\x79\xaf\x63\x62\xb1\x28\x85\xef\x8f\xef\x6e\x84\xdf\x8a\xac\xf2\x60"])]
174 public function testAnalyzeFakerRegressions(string $version, string $root, string $input): void
175 {
176 $provider = new \SqlFaker\MySql\MySqlProvider(\Faker\Factory::create(), $version);
177 $constraints = \SqlFaker\Generation\Plan\GenerationPlan::fromRule($root)->requiringNonEmpty();
178 $plan = (new \SqlFaker\Generation\Choice\BytePlanCompiler())->compile($input, $provider->planner(), $constraints);
179 $sql = $provider->generate($plan);
180 $statement = (new \SqlSemantics\Facade\Semantics(Dialect::MySql, $version))->analyze($sql);
181 $formatter = new \SqlFormatter\Facade\Formatter(Dialect::MySql->platform()->parser($version), new \SqlFormatter\Core\FormatOptions(\SqlFormatter\Core\Style::Compact));
182 self::assertSame($formatter->format($sql), $formatter->format($statement->toString()));
183 }
184}
185