packages/sql-semantics/tests/Unit/Facade/SchemaTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Facade;
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\Facade\Schema;
17use SqlSemantics\Platform\MySql\Dialect as MySqlDialect;
18use SqlSemantics\Platform\PostgreSql\Dialect as PostgreSqlDialect;
19use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
20use SqlSemantics\Statement\Writer;
21
22#[CoversClass(Binder::class)]
23#[CoversClass(SchemaBuilder::class)]
24#[CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
25#[CoversClass(\SqlSemantics\Core\Binding\ExpressionBinder::class)]
26#[CoversClass(\SqlSemantics\Core\Binding\ExpressionRules::class)]
27#[CoversClass(\SqlSemantics\Core\Binding\FromBinder::class)]
28#[CoversClass(\SqlSemantics\Core\Binding\LiteralBinder::class)]
29#[CoversClass(\SqlSemantics\Core\Binding\NullFacts::class)]
30#[CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::class)]
31#[CoversClass(\SqlSemantics\Core\Binding\SelectBinder::class)]
32#[CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
33#[CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
34#[CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
35#[CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
36#[CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
37#[CoversClass(\SqlSemantics\Core\Ast\Identifiers::class)]
38#[CoversClass(\SqlSemantics\Core\Ast\SchemaReader::class)]
39#[CoversClass(\SqlSemantics\Core\Ast\StatementList::class)]
40#[CoversClass(\SqlSemantics\Core\Ast\TokenGroups::class)]
41#[CoversClass(\SqlSemantics\Core\Ast\Tree::class)]
42#[CoversClass(\SqlSemantics\Core\Ast\TypeReader::class)]
43#[CoversClass(\SqlSemantics\Core\Binding\BoundRelation::class)]
44#[CoversClass(\SqlSemantics\Core\Binding\IdentitySequence::class)]
45#[CoversClass(\SqlSemantics\Core\Binding\Scope::class)]
46#[CoversClass(\SqlSemantics\Core\Binding\TableResolver::class)]
47#[CoversClass(\SqlSemantics\Core\Model\ColumnBinding::class)]
48#[CoversClass(\SqlSemantics\Core\Model\Expression::class)]
49#[CoversClass(\SqlSemantics\Core\Model\Join::class)]
50#[CoversClass(\SqlSemantics\Core\Model\Ordering::class)]
51#[CoversClass(\SqlSemantics\Core\Model\OutputColumn::class)]
52#[CoversClass(\SqlSemantics\Core\Model\BoundSelect::class)]
53#[CoversClass(\SqlSemantics\Core\Model\TableUse::class)]
54#[CoversClass(\SqlSemantics\Core\Schema::class)]
55#[CoversClass(\SqlSemantics\Core\Schema\ColumnDefinition::class)]
56#[CoversClass(\SqlSemantics\Core\Schema\TableConstraint::class)]
57#[CoversClass(\SqlSemantics\Core\Schema\TableDefinition::class)]
58#[CoversClass(SemanticException::class)]
59#[CoversClass(\SqlSemantics\Core\Type\TypeDescriptor::class)]
60#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Policy\SyntaxRules::class)]
61#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\QueryRules::class)]
62#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\Platform::class)]
63#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\TypeRules::class)]
64#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\NameRules::class)]
65#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\SchemaRules::class)]
66#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\QueryRules::class)]
67#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\Platform::class)]
68#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\TypeRules::class)]
69#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\NameRules::class)]
70#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\SchemaRules::class)]
71#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\QueryRules::class)]
72#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\Platform::class)]
73#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\TypeRules::class)]
74#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\NameRules::class)]
75#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\SchemaRules::class)]
76#[Medium]
77#[CoversClass(\SqlSemantics\Core\Analysis\SchemaAnalyzer::class)]
78#[CoversClass(\SqlSemantics\Core\Analysis\ValueReader::class)]
79#[CoversClass(\SqlSemantics\Core\Ast\ColumnProperties::class)]
80#[CoversClass(\SqlSemantics\Core\Ast\SchemaChanges::class)]
81#[CoversClass(\SqlSemantics\Core\Schema\ColumnGeneration::class)]
82#[CoversClass(\SqlSemantics\Core\Schema\Invariant::class)]
83#[CoversClass(Schema::class)]
84#[CoversClass(\SqlSemantics\Statement\ImmutableGraph::class)]
85#[CoversClass(Writer::class)]
86#[CoversClass(\SqlSemantics\Statement\Assertion::class)]
87final class SchemaTest extends TestCase
88{
89 #[TestWith([MySqlDialect::MySql, 'CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, label VARCHAR(30) COLLATE utf8mb4_bin, doubled INT GENERATED ALWAYS AS (id * 2) STORED) ENGINE=InnoDB', 'COLLATE utf8mb4_bin'])]
90 #[TestWith([PostgreSqlDialect::PostgreSql, 'CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, label TEXT COLLATE "C", doubled INT GENERATED ALWAYS AS (id * 2) STORED) WITH (fillfactor=70)', 'COLLATE "C"'])]
91 #[TestWith([SqliteDialect::Sqlite, 'CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT COLLATE NOCASE, doubled INT AS (id * 2) STORED) STRICT', 'COLLATE NOCASE'])]
92 public function testAnalyzePreservesStateProperties(Dialect $dialect, string $sql, string $collation): void
93 {
94 $state = (new Schema($dialect))->analyze('DROP TABLE IF EXISTS t; ' . $sql . ';');
95 $table = $state->tables[0];
96 self::assertSame(['id', 'label', 'doubled'], array_column($table->columns, 'name'));
97 self::assertSame(Nullability::NotNull, $table->columns[0]->nullability);
98 self::assertNotNull($table->columns[1]->collation);
99 self::assertSame($collation, Writer::render($table->columns[1]->collation));
100 self::assertNotNull($table->columns[2]->generation);
101 self::assertSame('stored', $table->columns[2]->generation->kind->value);
102 self::assertNotNull($table->columns[2]->generation->expression);
103 self::assertSame('id * 2', Writer::render($table->columns[2]->generation->expression));
104 self::assertNotEmpty($table->options);
105 self::assertStringNotContainsString('SqlParser', serialize($state));
106 self::assertSame(['id', 'label', 'doubled'], array_column((new Binder($state))->bind('SELECT * FROM t')->outputs, 'name'));
107 }
108
109 public function testAnalyzeIsIndependentAcrossCalls(): void
110 {
111 $reader = new Schema(PostgreSqlDialect::PostgreSql, 'app');
112 $first = $reader->analyze('CREATE TABLE t (id INT)');
113 self::assertSame('app', $first->tables[0]->schema);
114 self::assertSame([], $reader->analyze()->tables);
115 self::assertCount(1, $first->tables);
116 }
117
118 public function testAnalyzeDistinguishesInvalidSyntaxFromStateConflicts(): void
119 {
120 $this->expectException(\SqlSemantics\Core\AnalysisException::class);
121 (new Schema(SqliteDialect::Sqlite))->analyze('CREATE TABLE');
122 }
123
124 public function testAnalyzeRejectsDuplicateStateIdentities(): void
125 {
126 $this->expectException(SemanticException::class);
127 $this->expectExceptionMessage('Duplicate table');
128 (new Schema(PostgreSqlDialect::PostgreSql))->analyze('CREATE TABLE t (id INT)', 'CREATE TABLE t (id INT)');
129 }
130
131 public function testAnalyzePreservesEmptyQuotedNames(): void
132 {
133 $state = (new Schema(SqliteDialect::Sqlite))->analyze('CREATE TABLE "" ("" INTEGER PRIMARY KEY)');
134 self::assertSame('', $state->tables[0]->name);
135 self::assertSame('', $state->tables[0]->columns[0]->name);
136 self::assertSame([''], $state->tables[0]->constraints[0]->columns);
137 }
138 public function testAnalyzePreservesAnExplicitZeroColumnTable(): void
139 {
140 $table = (new Schema(PostgreSqlDialect::PostgreSql))->analyze('CREATE TABLE empty_table ()')->tables[0];
141 self::assertSame('empty_table', $table->name);
142 self::assertSame([], $table->columns);
143 }
144 #[TestWith(['', 'numeric'])]
145 #[TestWith([' STRICT', 'blob'])]
146 #[TestWith([' STRICT', 'blob', '"ANY"'])]
147 public function testAnalyzeResolvesAnyAffinityFromTableOptions(string $options, string $affinity, string $declaredType = 'ANY'): void
148 {
149 $state = (new Schema(SqliteDialect::Sqlite))->analyze('CREATE TABLE t (value ' . $declaredType . ')' . $options);
150 self::assertSame('any', $state->tables[0]->columns[0]->type->name);
151 self::assertSame($affinity, $state->tables[0]->columns[0]->type->affinity);
152 self::assertSame($affinity, (new Binder($state))->bind('SELECT value FROM t')->outputs[0]->expression->type->affinity);
153 }
154
155}
156