packages/sql-semantics/tests/Unit/Core/Ast/IdentifiersTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Core\Ast;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\Medium;
10use PHPUnit\Framework\TestCase;
11use SqlSemantics\Core\Binder;
12use SqlSemantics\Core\Dialect;
13use SqlSemantics\Core\SchemaBuilder;
14use SqlSemantics\Core\SemanticException;
15use SqlSemantics\Platform\MySql\Dialect as MySqlDialect;
16use SqlSemantics\Platform\PostgreSql\Dialect as PostgreSqlDialect;
17use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
18
19#[CoversClass(\SqlSemantics\Core\Ast\Identifiers::class)]
20#[CoversClass(\SqlSemantics\Core\Binding\ExpressionBinder::class)]
21#[CoversClass(\SqlSemantics\Core\Binding\ExpressionRules::class)]
22#[CoversClass(\SqlSemantics\Core\Binding\FromBinder::class)]
23#[CoversClass(\SqlSemantics\Core\Binding\LiteralBinder::class)]
24#[CoversClass(\SqlSemantics\Core\Binding\NullFacts::class)]
25#[CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::class)]
26#[CoversClass(\SqlSemantics\Core\Binding\SelectBinder::class)]
27#[CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
28#[CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
29#[CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
30#[CoversClass(Binder::class)]
31#[CoversClass(SchemaBuilder::class)]
32#[CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
33#[CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
34#[CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
35#[CoversClass(\SqlSemantics\Core\Ast\SchemaReader::class)]
36#[CoversClass(\SqlSemantics\Core\Ast\StatementList::class)]
37#[CoversClass(\SqlSemantics\Core\Ast\TokenGroups::class)]
38#[CoversClass(\SqlSemantics\Core\Ast\Tree::class)]
39#[CoversClass(\SqlSemantics\Core\Ast\TypeReader::class)]
40#[CoversClass(\SqlSemantics\Core\Binding\BoundRelation::class)]
41#[CoversClass(\SqlSemantics\Core\Binding\IdentitySequence::class)]
42#[CoversClass(\SqlSemantics\Core\Binding\Scope::class)]
43#[CoversClass(\SqlSemantics\Core\Binding\TableResolver::class)]
44#[CoversClass(\SqlSemantics\Core\Model\ColumnBinding::class)]
45#[CoversClass(\SqlSemantics\Core\Model\Expression::class)]
46#[CoversClass(\SqlSemantics\Core\Model\Join::class)]
47#[CoversClass(\SqlSemantics\Core\Model\Ordering::class)]
48#[CoversClass(\SqlSemantics\Core\Model\OutputColumn::class)]
49#[CoversClass(\SqlSemantics\Core\Model\BoundSelect::class)]
50#[CoversClass(\SqlSemantics\Core\Model\TableUse::class)]
51#[CoversClass(\SqlSemantics\Core\Schema::class)]
52#[CoversClass(\SqlSemantics\Core\Schema\ColumnDefinition::class)]
53#[CoversClass(\SqlSemantics\Core\Schema\TableConstraint::class)]
54#[CoversClass(\SqlSemantics\Core\Schema\TableDefinition::class)]
55#[CoversClass(SemanticException::class)]
56#[CoversClass(\SqlSemantics\Core\Type\TypeDescriptor::class)]
57#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Policy\SyntaxRules::class)]
58#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\QueryRules::class)]
59#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\Platform::class)]
60#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\TypeRules::class)]
61#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\NameRules::class)]
62#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\SchemaRules::class)]
63#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\QueryRules::class)]
64#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\Platform::class)]
65#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\TypeRules::class)]
66#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\NameRules::class)]
67#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\SchemaRules::class)]
68#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\QueryRules::class)]
69#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\Platform::class)]
70#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\TypeRules::class)]
71#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\NameRules::class)]
72#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\SchemaRules::class)]
73#[Medium]
74final class IdentifiersTest extends TestCase
75{
76 public function testNamePreservesQuotedPostgresCaseAndFoldsUnquotedNames(): void
77 {
78 $builder = new SchemaBuilder(PostgreSqlDialect::PostgreSql);
79 $schema = $builder->build('CREATE TABLE "Users" ("Id" INTEGER NOT NULL, SCORE INTEGER)');
80 $statement = (new Binder($schema))->bind('SELECT "Id", score FROM "Users"');
81 self::assertSame(['Id', 'score'], array_column($statement->outputs, 'name'));
82 }
83
84 public function testEqualDistinguishesQuotedPostgresCase(): void
85 {
86 $builder = new SchemaBuilder(PostgreSqlDialect::PostgreSql);
87 $schema = $builder->build('CREATE TABLE "Users" ("Id" INTEGER)');
88 $this->expectException(SemanticException::class);
89 (new Binder($schema))->bind('SELECT id FROM "Users"');
90 }
91
92 public function testPartsDoesNotSplitDotsInsideQuotedIdentifiers(): void
93 {
94 $builder = new SchemaBuilder(PostgreSqlDialect::PostgreSql);
95 $schema = $builder->build('CREATE TABLE "a.b" ("c.d" INTEGER)');
96 $statement = (new Binder($schema))->bind('SELECT "a.b"."c.d" FROM "a.b"');
97 self::assertSame('a.b', $statement->relations[0]->declaration->name);
98 self::assertSame('c.d', $statement->outputs[0]->name);
99 }
100
101 public function testRelationEqualModelsMysqlTableAliasesSeparatelyFromColumns(): void
102 {
103 $names = new \SqlSemantics\Core\Ast\Identifiers(MySqlDialect::MySql);
104 self::assertTrue($names->equal('Score', 'score'));
105 self::assertFalse($names->relationEqual('Child', 'child'));
106 $schema = (new SchemaBuilder(MySqlDialect::MySql))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
107 $this->expectException(SemanticException::class);
108 (new Binder($schema))->bind('SELECT CHILD.id FROM users child');
109 }
110
111 #[DataProvider('providerIdentifiers')]
112 public function testNameUnquotesEscapedDelimiters(Dialect $dialect, string $text, string $expected): void
113 {
114 self::assertSame($expected, (new \SqlSemantics\Core\Ast\Identifiers($dialect))->name(new \SqlParser\Lexer\Token(1, 'ID', $text, 0)));
115 }
116
117 /**
118 * @return iterable<string, array{Dialect, string, string}>
119 */
120 public static function providerIdentifiers(): iterable
121 {
122 yield 'postgres unquoted' => [PostgreSqlDialect::PostgreSql, 'USERS', 'users'];
123 yield 'postgres quoted' => [PostgreSqlDialect::PostgreSql, '"Users"', 'Users'];
124 yield 'escaped double quote' => [PostgreSqlDialect::PostgreSql, '"a""b"', 'a"b'];
125 yield 'mysql unquoted' => [MySqlDialect::MySql, 'USERS', 'USERS'];
126 yield 'mysql backtick' => [MySqlDialect::MySql, '`a``b`', 'a`b'];
127 yield 'sqlite bracket' => [SqliteDialect::Sqlite, '[a b]', 'a b'];
128 }
129}
130