packages/sql-semantics/tests/Unit/Core/BinderTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Core;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\Medium;
9use PHPUnit\Framework\Attributes\TestWith;
10use PHPUnit\Framework\TestCase;
11use SqlSemantics\Core\Binder;
12use SqlSemantics\Core\Dialect;
13use SqlSemantics\Core\SchemaBuilder;
14use SqlSemantics\Core\SemanticException;
15use SqlSemantics\Core\Type\Nullability;
16use SqlSemantics\Platform\MySql\Dialect as MySqlDialect;
17use SqlSemantics\Platform\PostgreSql\Dialect as PostgreSqlDialect;
18use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
19
20#[CoversClass(Binder::class)]
21#[CoversClass(SchemaBuilder::class)]
22#[CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
23#[CoversClass(\SqlSemantics\Core\Binding\ExpressionBinder::class)]
24#[CoversClass(\SqlSemantics\Core\Binding\ExpressionRules::class)]
25#[CoversClass(\SqlSemantics\Core\Binding\FromBinder::class)]
26#[CoversClass(\SqlSemantics\Core\Binding\LiteralBinder::class)]
27#[CoversClass(\SqlSemantics\Core\Binding\NullFacts::class)]
28#[CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::class)]
29#[CoversClass(\SqlSemantics\Core\Binding\SelectBinder::class)]
30#[CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
31#[CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
32#[CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
33#[CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
34#[CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
35#[CoversClass(\SqlSemantics\Core\Ast\Identifiers::class)]
36#[CoversClass(\SqlSemantics\Core\Ast\SchemaReader::class)]
37#[CoversClass(\SqlSemantics\Core\Ast\StatementList::class)]
38#[CoversClass(\SqlSemantics\Core\Ast\TokenGroups::class)]
39#[CoversClass(\SqlSemantics\Core\Ast\Tree::class)]
40#[CoversClass(\SqlSemantics\Core\Ast\TypeReader::class)]
41#[CoversClass(\SqlSemantics\Core\Binding\BoundRelation::class)]
42#[CoversClass(\SqlSemantics\Core\Binding\IdentitySequence::class)]
43#[CoversClass(\SqlSemantics\Core\Binding\Scope::class)]
44#[CoversClass(\SqlSemantics\Core\Binding\TableResolver::class)]
45#[CoversClass(\SqlSemantics\Core\Model\ColumnBinding::class)]
46#[CoversClass(\SqlSemantics\Core\Model\Expression::class)]
47#[CoversClass(\SqlSemantics\Core\Model\Join::class)]
48#[CoversClass(\SqlSemantics\Core\Model\Ordering::class)]
49#[CoversClass(\SqlSemantics\Core\Model\OutputColumn::class)]
50#[CoversClass(\SqlSemantics\Core\Model\BoundSelect::class)]
51#[CoversClass(\SqlSemantics\Core\Model\TableUse::class)]
52#[CoversClass(\SqlSemantics\Core\Schema::class)]
53#[CoversClass(\SqlSemantics\Core\Schema\ColumnDefinition::class)]
54#[CoversClass(\SqlSemantics\Core\Schema\TableConstraint::class)]
55#[CoversClass(\SqlSemantics\Core\Schema\TableDefinition::class)]
56#[CoversClass(SemanticException::class)]
57#[CoversClass(\SqlSemantics\Core\Type\TypeDescriptor::class)]
58#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Policy\SyntaxRules::class)]
59#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\QueryRules::class)]
60#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\Platform::class)]
61#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\TypeRules::class)]
62#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\NameRules::class)]
63#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\SchemaRules::class)]
64#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\QueryRules::class)]
65#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\Platform::class)]
66#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\TypeRules::class)]
67#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\NameRules::class)]
68#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\SchemaRules::class)]
69#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\QueryRules::class)]
70#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\Platform::class)]
71#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\TypeRules::class)]
72#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\NameRules::class)]
73#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\SchemaRules::class)]
74#[Medium]
75final class BinderTest extends TestCase
76{
77 #[TestWith([PostgreSqlDialect::PostgreSql])]
78 #[TestWith([MySqlDialect::MySql])]
79 #[TestWith([SqliteDialect::Sqlite])]
80 public function testBindSelfJoinPreservesOccurrenceIdentityAndNullProvenance(Dialect $dialect): void
81 {
82 $schema = (new SchemaBuilder($dialect))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
83 $statement = (new Binder($schema))->bind('SELECT child.id, parent.score AS parent_score, COALESCE(parent.score, 0) AS effective_score FROM users AS child LEFT JOIN users AS parent ON child.parent_id = parent.id');
84 self::assertSame('s0', $statement->scopeId);
85 self::assertSame(['r0', 'r1'], array_column($statement->relations, 'id'));
86 self::assertSame($statement->relations[0]->declaration, $statement->relations[1]->declaration);
87 self::assertSame(['id', 'parent_score', 'effective_score'], array_column($statement->outputs, 'name'));
88 self::assertSame(Nullability::NotNull, $statement->outputs[0]->expression->nullability);
89 self::assertSame(Nullability::MaybeNull, $statement->outputs[1]->expression->nullability);
90 self::assertSame(['j0'], $statement->outputs[1]->expression->nullExtendedBy);
91 self::assertSame('integer', $statement->outputs[1]->expression->type->name);
92 self::assertSame(Nullability::NotNull, $statement->outputs[2]->expression->nullability);
93 self::assertSame([], $statement->outputs[2]->expression->nullExtendedBy);
94 self::assertSame(['j0'], $statement->outputs[2]->expression->operands[0]->nullExtendedBy);
95 self::assertSame('r1', $statement->outputs[2]->expression->lineage()[0]->relationId);
96 self::assertSame(Nullability::NotNull, $statement->relations[1]->declaration->columns[2]->nullability);
97 }
98
99 public function testBindPreservesSourceTextAndExpressionNodeIdentity(): void
100 {
101 $schema = (new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build('CREATE TABLE users (score INTEGER)');
102 $sql = '/* source */ SELECT score FROM users';
103 $statement = (new Binder($schema))->bind($sql);
104 self::assertSame($sql, $statement->source->toString());
105 self::assertSame($statement->source->find('columnref')[0], $statement->outputs[0]->expression->source);
106 self::assertSame($schema->tables[0], $statement->relations[0]->declaration);
107 }
108
109 #[TestWith([PostgreSqlDialect::PostgreSql])]
110 #[TestWith([MySqlDialect::MySql])]
111 #[TestWith([SqliteDialect::Sqlite])]
112 public function testBindResetsStatementIdentitiesWhenReusingTheBinder(Dialect $dialect): void
113 {
114 $schema = (new SchemaBuilder($dialect))->build('CREATE TABLE users (id INTEGER)');
115 $binder = new Binder($schema);
116 $first = $binder->bind('SELECT a.id FROM users a LEFT JOIN users b ON a.id=b.id');
117 $second = $binder->bind('SELECT id FROM users');
118 self::assertSame(['r0', 'r1'], array_column($first->relations, 'id'));
119 self::assertSame(['r0'], array_column($second->relations, 'id'));
120 self::assertSame($schema->tables[0], $second->relations[0]->declaration);
121 }
122
123 #[TestWith([PostgreSqlDialect::PostgreSql])]
124 #[TestWith([MySqlDialect::MySql])]
125 #[TestWith([SqliteDialect::Sqlite])]
126 public function testBindUsesTheSchemasDefaultNamespace(Dialect $dialect): void
127 {
128 $schema = (new SchemaBuilder($dialect, 'app'))->build('CREATE TABLE users (id INTEGER)');
129 $statement = (new Binder($schema))->bind('SELECT id FROM users');
130 self::assertSame('app', $statement->relations[0]->declaration->schema);
131 self::assertSame($dialect, $statement->outputs[0]->expression->type->dialect);
132 }
133
134 #[TestWith([PostgreSqlDialect::PostgreSql])]
135 #[TestWith([MySqlDialect::MySql])]
136 #[TestWith([SqliteDialect::Sqlite])]
137 public function testBindPropagatesSyntaxErrors(Dialect $dialect): void
138 {
139 $schema = (new SchemaBuilder($dialect))->build();
140 $this->expectException(\SqlParser\Parser\SyntaxException::class);
141 (new Binder($schema))->bind('SELECT FROM');
142 }
143
144 public function testBindRejectsMissingDeclarations(): void
145 {
146 $binder = new Binder((new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build());
147 self::assertSame('1', $binder->bind('SELECT 1')->outputs[0]->expression->symbol);
148 $this->expectException(SemanticException::class);
149 $this->expectExceptionMessage('Cannot resolve table');
150 $binder->bind('SELECT id FROM missing');
151 }
152}
153