packages/sql-semantics/tests/Unit/Core/Binding/ProjectionBinderTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Core\Binding;
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\Platform\MySql\Dialect as MySqlDialect;
16use SqlSemantics\Platform\PostgreSql\Dialect as PostgreSqlDialect;
17use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
18
19#[CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::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\SelectBinder::class)]
26#[CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
27#[CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
28#[CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
29#[CoversClass(Binder::class)]
30#[CoversClass(SchemaBuilder::class)]
31#[CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
32#[CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
33#[CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
34#[CoversClass(\SqlSemantics\Core\Ast\Identifiers::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 ProjectionBinderTest extends TestCase
75{
76    #[TestWith([PostgreSqlDialect::PostgreSql])]
77    #[TestWith([MySqlDialect::MySql])]
78    #[TestWith([SqliteDialect::Sqlite])]
79    public function testItemExpandsStarsAndPreservesDuplicateNames(Dialect $dialect): void
80    {
81        $schema = (new SchemaBuilder($dialect))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
82        $statement = (new Binder($schema))->bind('SELECT a.*, b.score AS id FROM users a LEFT JOIN users b ON a.id=b.id');
83        self::assertSame(['id', 'parent_id', 'score', 'id'], array_column($statement->outputs, 'name'));
84        self::assertSame([0, 1, 2, 3], array_column($statement->outputs, 'ordinal'));
85    }
86
87    #[TestWith([PostgreSqlDialect::PostgreSql])]
88    #[TestWith([MySqlDialect::MySql])]
89    #[TestWith([SqliteDialect::Sqlite])]
90    public function testBindExpandsUnqualifiedStarInFromOrder(Dialect $dialect): void
91    {
92        $schema = (new SchemaBuilder($dialect))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
93        $statement = (new Binder($schema))->bind('SELECT * FROM users a, users b');
94        self::assertSame(['id', 'parent_id', 'score', 'id', 'parent_id', 'score'], array_column($statement->outputs, 'name'));
95        self::assertSame('r0', $statement->outputs[0]->expression->binding?->relationId);
96        self::assertSame('r1', $statement->outputs[3]->expression->binding?->relationId);
97    }
98
99    public function testStarRejectsAnUnknownQualifier(): void
100    {
101        $schema = (new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
102        $this->expectException(SemanticException::class);
103        (new Binder($schema))->bind('SELECT absent.* FROM users');
104    }
105
106    public function testItemResolvesBarePostgresUnknownLiteralsAsText(): void
107    {
108        $schema = (new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build();
109        $statement = (new Binder($schema))->bind("SELECT NULL, 'hello'");
110        self::assertSame('text', $statement->outputs[0]->expression->type->name);
111        self::assertSame('text', $statement->outputs[1]->expression->type->name);
112        self::assertSame(\SqlSemantics\Core\Type\Nullability::AlwaysNull, $statement->outputs[0]->expression->nullability);
113    }
114
115    public function testItemDecodesMysqlStringAliases(): void
116    {
117        $schema = (new SchemaBuilder(MySqlDialect::MySql))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
118        $statement = (new Binder($schema))->bind("SELECT score AS 'points' FROM users ORDER BY points");
119        self::assertSame('points', $statement->outputs[0]->name);
120        self::assertSame($statement->outputs[0]->expression, $statement->orderBy[0]->expression);
121    }
122}
123