packages/sql-catalog/tests/Unit/Extension/Laravel/WriteCompilerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Extension\Laravel;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use SqlCatalog\Core\Evaluation\ArrayEntry;
11use SqlCatalog\Core\Evaluation\ArrayTerm;
12use SqlCatalog\Core\Evaluation\Domain;
13use SqlCatalog\Core\Evaluation\LiteralTerm;
14use SqlCatalog\Core\Evaluation\ObjectTerm;
15use SqlCatalog\Core\Evaluation\OpaqueTerm;
16use SqlCatalog\Core\Evaluation\PatternTerm;
17use SqlCatalog\Core\Text\LiteralText;
18use SqlCatalog\Core\Text\TextGeneralization;
19use SqlCatalog\Core\Text\TextHole;
20use SqlCatalog\Core\Text\TextPattern;
21use SqlCatalog\Core\Type\TypeShape;
22use SqlCatalog\Extension\Laravel\Grammar;
23use SqlCatalog\Extension\Laravel\QueryState;
24use SqlCatalog\Extension\Laravel\WriteCompiler;
25
26#[CoversClass(WriteCompiler::class)]
27#[UsesClass(Domain::class)]
28#[UsesClass(ArrayTerm::class)]
29#[UsesClass(ArrayEntry::class)]
30#[UsesClass(ObjectTerm::class)]
31#[UsesClass(LiteralTerm::class)]
32#[UsesClass(OpaqueTerm::class)]
33#[UsesClass(PatternTerm::class)]
34#[UsesClass(TextPattern::class)]
35#[UsesClass(TextHole::class)]
36#[UsesClass(LiteralText::class)]
37#[UsesClass(TextGeneralization::class)]
38#[UsesClass(TypeShape::class)]
39#[UsesClass(QueryState::class)]
40#[UsesClass(Grammar::class)]
41final class WriteCompilerTest extends TestCase
42{
43 public function testCompileDeletesOnlyTheDerivedPredicateAndRejectsModifiers(): void
44 {
45 $c = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
46 $state = new QueryState(['table' => Domain::literal('users'), 'where' => QueryState::list([Domain::literal('id = ?')]), 'whereBindings' => QueryState::list([Domain::literal(1)])]);
47 [$sql, $bindings] = $c->compile($state, 'delete', []);
48 self::assertSame('delete from "users" where id = ?', $sql->soleLiteral()?->value);
49 self::assertSame(1, $bindings->soleArray()?->positional()[0]->soleLiteral()?->value);
50 self::assertFalse($c->compile($state->with('limit', Domain::literal(1)), 'delete', [])[0]->isExact());
51 self::assertFalse($c->compile($state, 'upsert', [])[0]->isExact());
52 }
53
54 public function testUpdateOrdersAssignmentsBeforePredicateBindings(): void
55 {
56 $c = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
57 $row = new ArrayTerm([new ArrayEntry(Domain::literal('name'), Domain::literal('Ada'))]);
58 $state = new QueryState(['table' => Domain::literal('users'), 'where' => QueryState::list([Domain::literal('id = ?')]), 'whereBindings' => QueryState::list([Domain::literal(7)])]);
59 [$sql, $bindings] = $c->update($state, $row);
60 self::assertSame('update `users` set `name` = ? where id = ?', $sql->soleLiteral()?->value);
61 self::assertSame(['Ada', 7], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
62 self::assertFalse($c->update($state, new ArrayTerm([]))[0]->isExact());
63 }
64
65 #[\PHPUnit\Framework\Attributes\DataProvider('providerInsertUsesEachDialectsIgnoreSyntaxAndPreservesColumnOrder')]
66 public function testInsertUsesEachDialectsIgnoreSyntaxAndPreservesColumnOrder(string $dialect, string $expected): void
67 {
68 $state = new QueryState(['table' => Domain::literal('users')]);
69 $row = new ArrayTerm([new ArrayEntry(Domain::literal('name'), Domain::literal('Ada')), new ArrayEntry(Domain::literal('id'), Domain::literal(7))]);
70 [$sql, $bindings] = (new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find($dialect))))->insert($state, $row, true);
71 self::assertSame($expected, $sql->soleLiteral()?->value);
72 self::assertSame(['Ada', 7], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
73 }
74
75 /**
76 * @return iterable<array{string, string}>
77 */
78 public static function providerInsertUsesEachDialectsIgnoreSyntaxAndPreservesColumnOrder(): iterable
79 {
80 foreach (['mysql' => 'insert ignore into `users` (`name`, `id`) values (?, ?)', 'sqlite' => 'insert or ignore into "users" ("name", "id") values (?, ?)', 'pgsql' => 'insert into "users" ("name", "id") values (?, ?) on conflict do nothing'] as $dialect => $expected) {
81 yield [$dialect, $expected];
82 }
83 }
84
85 public function testRowsSortsBulkKeysAndRejectsIncompleteRows(): void
86 {
87 $c = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
88 $row = Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('z'), Domain::literal(1)), new ArrayEntry(Domain::literal('a'), Domain::literal(2))]));
89 self::assertSame(['a', 'z'], array_keys($c->rows(new ArrayTerm([new ArrayEntry(null, $row)]))[0]));
90 self::assertSame([], $c->rows(new ArrayTerm([new ArrayEntry(null, Domain::unknown())])));
91 }
92
93 public function testWhereDoesNotInventAnEmptyPredicate(): void
94 {
95 self::assertSame('', (new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->where(new QueryState())->soleLiteral()?->value);
96 }
97
98 public function testUnknownProducesAnIncompleteSqlDomain(): void
99 {
100 [$sql, $bindings] = (new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->unknown('custom write');
101 self::assertFalse($sql->isExact());
102 self::assertNull($bindings->soleArray());
103 }
104
105 #[\PHPUnit\Framework\Attributes\DataProvider('providerWriteDispatch')]
106 public function testCompileSelectsTheWriteOperation(string $method, string $expected): void
107 {
108 $row = Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('id'), Domain::literal(7))]));
109 [$sql, $bindings] = (new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->compile(new QueryState(['table' => Domain::literal('users')]), $method, [$row]);
110 self::assertSame($expected, $sql->soleLiteral()?->value);
111 self::assertSame(7, $bindings->soleArray()?->positional()[0]->soleLiteral()?->value);
112 }
113
114 /**
115 * @return iterable<array{string, string}>
116 */
117 public static function providerWriteDispatch(): iterable
118 {
119 yield ['insert', 'insert into "users" ("id") values (?)'];
120 yield ['insertorignore', 'insert or ignore into "users" ("id") values (?)'];
121 yield ['update', 'update "users" set "id" = ?'];
122 }
123
124 public function testCompileDoesNotInterpretACompleteRowAsAnUnknownWriteOperation(): void
125 {
126 $row = Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('id'), Domain::literal(7))]));
127 $state = new QueryState(['table' => Domain::literal('users')]);
128 $compiler = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
129 self::assertFalse($compiler->compile($state, 'upsert', [$row])[0]->isExact());
130 self::assertFalse($compiler->compile($state, 'insert', [$row, Domain::literal(1)])[0]->isExact());
131 self::assertFalse($compiler->compile($state, 'update', [Domain::of(new ArrayTerm([], false))])[0]->isExact());
132 self::assertFalse($compiler->compile($state->with('joins', QueryState::list([Domain::literal('join x')])), 'delete', [])[0]->isExact());
133 self::assertFalse($compiler->compile($state->with('orders', QueryState::list([Domain::literal('id')])), 'delete', [])[0]->isExact());
134 }
135
136 public function testInsertRejectsBulkRowsWithDifferentColumns(): void
137 {
138 $rows = new ArrayTerm([new ArrayEntry(null, Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('a'), Domain::literal(1))]))), new ArrayEntry(null, Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('b'), Domain::literal(2))])))]);
139 self::assertFalse((new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->insert(new QueryState(['table' => Domain::literal('users')]), $rows, false)[0]->isExact());
140 }
141
142 public function testInsertSortsBothColumnNamesAndValuesAcrossBulkRows(): void
143 {
144 $row = Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('z'), Domain::literal(1)), new ArrayEntry(Domain::literal('a'), Domain::literal(2))]));
145 $rows = new ArrayTerm([new ArrayEntry(null, $row), new ArrayEntry(null, $row)]);
146 [$sql, $bindings] = (new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->insert(new QueryState(['table' => Domain::literal('users')]), $rows, false);
147 self::assertSame('insert into "users" ("a", "z") values (?, ?), (?, ?)', $sql->soleLiteral()?->value);
148 self::assertSame([2, 1, 2, 1], array_map(static fn (Domain $value): mixed => $value->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
149 }
150
151
152 public function testStepWritesLaravelsRawArithmeticBeforeExtraColumns(): void
153 {
154 $compiler = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
155 $state = new QueryState(['table' => Domain::literal('users'), 'where' => QueryState::list([Domain::literal('`id` = ?')]), 'whereBindings' => QueryState::list([Domain::literal(7)])]);
156 $extra = Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('seen'), Domain::literal('now'))]));
157 [$sql, $bindings] = $compiler->step($state, 'increment', [Domain::literal('logins'), Domain::literal(2), $extra]);
158 self::assertSame('update `users` set `logins` = `logins` + 2, `seen` = ? where `id` = ?', $sql->soleLiteral()?->value);
159 self::assertSame(['now', 7], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
160 self::assertSame('update `users` set `logins` = `logins` - 1 where `id` = ?', $compiler->step($state, 'decrement', [Domain::literal('logins')])[0]->soleLiteral()?->value);
161 self::assertSame('update `users` set `logins` = `logins` + {$} where `id` = ?', $compiler->step($state, 'increment', [Domain::literal('logins'), Domain::unknown()])[0]->patterns()[0]->display());
162 self::assertFalse($compiler->step($state, 'increment', [Domain::unknown()])[0]->isExact());
163 self::assertFalse($compiler->step($state, 'increment', [Domain::literal('logins'), QueryState::list([])])[0]->isExact());
164 self::assertFalse($compiler->compile($state, 'increment', [Domain::literal('logins'), Domain::literal(1), Domain::unknown()])[0]->isExact());
165 }
166
167 public function testInsertGetIdReturnsTheKeyOnlyWhereTheDialectAsksForIt(): void
168 {
169 $state = new QueryState(['table' => Domain::literal('users')]);
170 $row = new ArrayTerm([new ArrayEntry(Domain::literal('name'), Domain::literal('Ada'))]);
171 $pgsql = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('pgsql')));
172 self::assertSame('insert into "users" ("name") values (?) returning "id"', $pgsql->insertGetId($state, $row, Domain::literal(null))[0]->soleLiteral()?->value);
173 self::assertSame('insert into "users" ("name") values (?) returning "uid"', $pgsql->insertGetId($state, $row, Domain::literal('uid'))[0]->soleLiteral()?->value);
174 $mysql = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
175 self::assertSame('insert into `users` (`name`) values (?)', $mysql->compile($state, 'insertgetid', [Domain::of($row)])[0]->soleLiteral()?->value);
176 self::assertSame(['Ada'], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $mysql->insertGetId($state, $row, Domain::literal(null))[1]->soleArray()?->positional() ?? []));
177 self::assertFalse($mysql->insertGetId($state, new ArrayTerm([new ArrayEntry(null, Domain::of($row))]), Domain::literal(null))[0]->isExact());
178 }
179
180 public function testCompileDeletesOneRowByItsKey(): void
181 {
182 $compiler = new WriteCompiler(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
183 $state = new QueryState(['table' => Domain::literal('users'), 'key' => Domain::literal('id')]);
184 [$sql, $bindings] = $compiler->compile($state, 'delete', [Domain::literal(7)]);
185 self::assertSame('delete from "users" where "users"."id" = ?', $sql->soleLiteral()?->value);
186 self::assertSame([7], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $bindings->soleArray()?->positional() ?? []));
187 self::assertFalse($compiler->compile($state, 'delete', [QueryState::list([])])[0]->isExact());
188 }
189}
190