packages/sql-catalog/tests/Unit/Extension/Laravel/ClausesTest.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\Clauses;
23use SqlCatalog\Extension\Laravel\Grammar;
24use SqlCatalog\Extension\Laravel\QueryState;
25
26#[CoversClass(Clauses::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 ClausesTest extends TestCase
42{
43 public function testApplyDispatchesModifiersAndRejectsUnknownOperations(): void
44 {
45 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
46 self::assertTrue($c->apply(new QueryState(), 'distinct', [])?->get('distinct')->soleLiteral()?->value);
47 self::assertFalse($c->apply(new QueryState(), 'distinct', [Domain::literal(true)])?->get('problem')->isExact());
48 self::assertNull($c->apply(new QueryState(), 'macro', []));
49 self::assertSame(2, $c->apply(new QueryState(), 'take', [Domain::literal(2)])?->get('limit')->soleLiteral()?->value);
50 }
51
52 public function testSelectResetsBindingsAndAddSelectDeduplicatesColumns(): void
53 {
54 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
55 $state = $c->raw(new QueryState(), [Domain::literal('? as x'), QueryState::list([Domain::literal(1)])], 'columns', 'selectBindings');
56 $state = $c->select($state, [Domain::literal('id')]);
57 $state = $c->select($state, [Domain::literal('id'), Domain::literal('name')], true);
58 self::assertSame(['"id"', '"name"'], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $state->items('columns')));
59 self::assertSame([], $state->items('selectBindings'));
60 self::assertFalse($c->select(new QueryState(), [QueryState::list([])])->get('problem')->isExact());
61 }
62
63 public function testColumnsRequiresCompletePositionalArrays(): void
64 {
65 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
66 $id = Domain::literal('id');
67 self::assertSame([$id], $c->columns([QueryState::list([$id])]));
68 $partial = Domain::of(new ArrayTerm([], false));
69 self::assertSame([$partial], $c->columns([$partial]));
70 self::assertNull($c->columns([Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('alias'), $id)]))]));
71 self::assertSame([$id, $id], $c->columns([$id, $id]));
72 }
73
74 public function testRawKeepsBindingsInTheirComponent(): void
75 {
76 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
77 $state = $c->raw(new QueryState(), [Domain::literal('id + ?'), QueryState::list([Domain::literal(2)])], 'orders', 'orderBindings');
78 self::assertSame(2, $state->items('orderBindings')[0]->soleLiteral()?->value);
79 self::assertSame('id + ?', $state->items('orders')[0]->soleLiteral()?->value);
80 self::assertFalse($c->raw(new QueryState(), [], 'orders', 'orderBindings')->get('problem')->isExact());
81 }
82
83 public function testOrderValidatesDirectionAndHonorsDescending(): void
84 {
85 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
86 self::assertSame('`id` desc', $c->order(new QueryState(), [Domain::literal('id')], true)->items('orders')[0]->soleLiteral()?->value);
87 self::assertSame('`id` asc', $c->order(new QueryState(), [Domain::literal('id'), Domain::literal('ASC')], false)->items('orders')[0]->soleLiteral()?->value);
88 self::assertFalse($c->order(new QueryState(), [Domain::literal('id'), Domain::literal('random')], false)->get('problem')->isExact());
89 $open = $c->order(new QueryState(), [Domain::literal('id'), Domain::unknown()], false);
90 self::assertArrayNotHasKey('problem', $open->fields);
91 self::assertFalse($open->items('orders')[0]->isExact());
92 self::assertFalse($c->order(new QueryState(), [Domain::literal('id'), QueryState::list([])], false)->get('problem')->isExact());
93 self::assertFalse($c->order(new QueryState(), [], false)->get('problem')->isExact());
94 }
95
96 public function testGroupAppendsIdentifiersAndRetainsUnknownColumns(): void
97 {
98 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
99 self::assertSame('"team"', $c->group(new QueryState(), [Domain::literal('team')])->items('groups')[0]->soleLiteral()?->value);
100 $open = $c->group(new QueryState(), [Domain::of(new ArrayTerm([], false))]);
101 self::assertArrayNotHasKey('problem', $open->fields);
102 self::assertFalse($open->items('groups')[0]->isExact());
103 self::assertFalse($c->group(new QueryState(), [Domain::of(new ArrayTerm([new ArrayEntry(Domain::literal('alias'), Domain::literal('id'))]))])->get('problem')->isExact());
104 }
105
106 public function testNumberKeepsUnknownLimitsOpenAndIgnoresNegativeOnes(): void
107 {
108 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
109 self::assertSame(0, $c->number(new QueryState(), [Domain::literal(0)], 'limit')->get('limit')->soleLiteral()?->value);
110 self::assertSame(10, $c->number(new QueryState(), [Domain::literal('10')], 'limit')->get('limit')->soleLiteral()?->value);
111 $kept = $c->number(new QueryState(['limit' => Domain::literal(5)]), [Domain::literal(-1)], 'limit');
112 self::assertSame(5, $kept->get('limit')->soleLiteral()?->value);
113 self::assertNull($c->number($kept, [Domain::literal(null)], 'limit')->get('limit')->soleLiteral()?->value);
114 self::assertSame(0, $c->number(new QueryState(), [Domain::literal(null)], 'offset')->get('offset')->soleLiteral()?->value);
115 self::assertSame(0, $c->number(new QueryState(['offset' => Domain::literal(4)]), [Domain::literal(-3)], 'offset')->get('offset')->soleLiteral()?->value);
116 self::assertSame(3, $c->number(new QueryState(), [Domain::literal('3')], 'offset')->get('offset')->soleLiteral()?->value);
117 $open = $c->number(new QueryState(), [Domain::unknown()], 'limit');
118 self::assertArrayNotHasKey('problem', $open->fields);
119 self::assertFalse($open->get('limit')->isExact());
120 self::assertFalse($c->number(new QueryState(), [Domain::literal('ten')], 'limit')->get('problem')->isExact());
121 self::assertFalse($c->number(new QueryState(), [QueryState::list([])], 'limit')->get('problem')->isExact());
122 self::assertFalse($c->number(new QueryState(), [], 'limit')->get('problem')->isExact());
123 }
124
125 public function testJoinQuotesBothColumnOperandsWithoutBindings(): void
126 {
127 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
128 $state = $c->join(new QueryState(), array_map(Domain::literal(...), ['posts as p', 'p.user_id', '=', 'u.id']), 'leftjoin');
129 self::assertSame('left join "posts" as "p" on "p"."user_id" = "u"."id"', $state->items('joins')[0]->soleLiteral()?->value);
130 self::assertFalse($c->join(new QueryState(), [], 'join')->get('problem')->isExact());
131 self::assertFalse($c->join(new QueryState(), array_map(Domain::literal(...), ['p', 'a', 'bad', 'b']), 'join')->get('problem')->isExact());
132 }
133
134 /**
135 * @param list<string|int> $arguments
136 */
137 #[\PHPUnit\Framework\Attributes\DataProvider('providerClauseDispatch')]
138 public function testApplySelectsTheCorrectClause(string $method, array $arguments, string $field, string|int $expected): void
139 {
140 $state = (new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite'))))->apply(new QueryState(), $method, array_map(Domain::literal(...), $arguments));
141 self::assertNotNull($state);
142 $value = $state->get($field);
143 self::assertSame($expected, ($value->soleArray()?->positional()[0] ?? $value)->soleLiteral()?->value);
144 self::assertArrayNotHasKey('problem', $state->fields);
145 }
146
147 /**
148 * @return iterable<array{string, list<string|int>, string, string|int}>
149 */
150 public static function providerClauseDispatch(): iterable
151 {
152 yield ['select', ['id'], 'columns', '"id"'];
153 yield ['addselect', ['id'], 'columns', '"id"'];
154 yield ['selectraw', ['id + 1'], 'columns', 'id + 1'];
155 yield ['orderby', ['id'], 'orders', '"id" asc'];
156 yield ['orderbydesc', ['id'], 'orders', '"id" desc'];
157 yield ['orderbyraw', ['id desc'], 'orders', 'id desc'];
158 yield ['groupby', ['id'], 'groups', '"id"'];
159 yield ['havingraw', ['count(*) > 0'], 'having', 'count(*) > 0'];
160 yield ['orhavingraw', ['count(*) > 0'], 'having', 'count(*) > 0'];
161 yield ['having', ['total', '>', 1], 'having', '"total" > ?'];
162 yield ['orhaving', ['total', 1], 'having', '"total" = ?'];
163 yield ['latest', [], 'orders', '"created_at" desc'];
164 yield ['oldest', ['id'], 'orders', '"id" asc'];
165 yield ['from', ['posts'], 'table', 'posts'];
166 yield ['limit', [0], 'limit', 0];
167 yield ['take', [2], 'limit', 2];
168 yield ['offset', [0], 'offset', 0];
169 yield ['skip', [2], 'offset', 2];
170 yield ['join', ['p', 'p.id', '=', 'u.id'], 'joins', 'inner join "p" on "p"."id" = "u"."id"'];
171 yield ['leftjoin', ['p', 'p.id', '=', 'u.id'], 'joins', 'left join "p" on "p"."id" = "u"."id"'];
172 yield ['rightjoin', ['p', 'p.id', '=', 'u.id'], 'joins', 'right join "p" on "p"."id" = "u"."id"'];
173 }
174
175
176 public function testFromSetsTheTableWithAnOptionalAlias(): void
177 {
178 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('sqlite')));
179 self::assertSame('posts', $c->from(new QueryState(), [Domain::literal('posts')])->get('table')->soleLiteral()?->value);
180 self::assertSame('posts as p', $c->from(new QueryState(), [Domain::literal('posts'), Domain::literal('p')])->get('table')->soleLiteral()?->value);
181 self::assertSame('posts', $c->from(new QueryState(), [Domain::literal('posts'), Domain::literal(null)])->get('table')->soleLiteral()?->value);
182 self::assertFalse($c->from(new QueryState(), [Domain::literal('posts'), Domain::unknown()])->get('table')->isExact());
183 self::assertFalse($c->from(new QueryState(), [])->get('problem')->isExact());
184 self::assertFalse($c->from(new QueryState(), [Domain::of(new ObjectTerm('Builder'))])->get('problem')->isExact());
185 self::assertFalse($c->apply(new QueryState(), 'latest', [Domain::literal('a'), Domain::literal('b')])?->get('problem')->isExact());
186 }
187
188 public function testHavingCompilesComparisonsWithTheirBooleans(): void
189 {
190 $c = new Clauses(new Grammar(\SqlCatalog\Facade\Builtins::dialects()->find('mysql')));
191 $state = $c->having(new QueryState(), [Domain::literal('total'), Domain::literal('>'), Domain::literal(100)], 'and');
192 $state = $c->having($state, [Domain::literal('count'), Domain::literal(null)], 'or');
193 self::assertSame(['`total` > ?', 'or `count` = ?'], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $state->items('having')));
194 self::assertSame([100, null], array_map(static fn (Domain $v): mixed => $v->soleLiteral()?->value, $state->items('havingBindings')));
195 self::assertFalse($c->having(new QueryState(), [], 'and')->get('problem')->isExact());
196 self::assertFalse($c->having(new QueryState(), [QueryState::list([Domain::literal(1), Domain::literal(2)])], 'and')->get('problem')->isExact());
197 }
198}
199