packages/ztd-query-sqlite/tests/Unit/Sql/Value/SqliteCastRendererTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Value;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use ZtdQuery\Platform\Sqlite\Sql\Value\SqliteCastRenderer;
11use ZtdQuery\Schema\ColumnDeclaration;
12use ZtdQuery\Schema\ColumnTypeFamily;
13
14#[CoversClass(SqliteCastRenderer::class)]
15#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Value\CastTypeMapper::class)]
16final class SqliteCastRendererTest extends \PHPUnit\Framework\TestCase
17{
18    #[DataProvider('providerColumnTypes')]
19    public function testRenderCastReturnsNonEmptyString(ColumnTypeFamily $family, string $nativeType): void
20    {
21        $renderer = new SqliteCastRenderer();
22        $type = new ColumnDeclaration($family, $nativeType);
23        $result = $renderer->renderCast("'test'", $type);
24        self::assertNotEmpty($result, sprintf('renderCast returned empty string for family %s', $family->value));
25    }
26
27    #[DataProvider('providerColumnTypes')]
28    public function testRenderNullCastReturnsNonEmptyString(ColumnTypeFamily $family, string $nativeType): void
29    {
30        $renderer = new SqliteCastRenderer();
31        $type = new ColumnDeclaration($family, $nativeType);
32        $result = $renderer->renderNullCast($type);
33        self::assertNotEmpty($result, sprintf('renderNullCast returned empty string for family %s', $family->value));
34    }
35
36    #[DataProvider('providerColumnTypes')]
37    public function testRenderNullCastContainsCastKeyword(ColumnTypeFamily $family, string $nativeType): void
38    {
39        $renderer = new SqliteCastRenderer();
40        $type = new ColumnDeclaration($family, $nativeType);
41        $result = $renderer->renderNullCast($type);
42        self::assertStringContainsString('CAST(', strtoupper($result), sprintf('renderNullCast output for family %s does not contain CAST(', $family->value));
43    }
44
45    #[DataProvider('providerColumnTypes')]
46    public function testRenderNullCastContainsNullKeyword(ColumnTypeFamily $family, string $nativeType): void
47    {
48        $renderer = new SqliteCastRenderer();
49        $type = new ColumnDeclaration($family, $nativeType);
50        $result = $renderer->renderNullCast($type);
51        self::assertStringContainsString('NULL', strtoupper($result), sprintf('renderNullCast output for family %s does not contain NULL', $family->value));
52    }
53
54    public function testAllColumnTypeFamiliesHandled(): void
55    {
56        $renderer = new SqliteCastRenderer();
57        $intResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'TEXT'));
58        self::assertNotEmpty($intResult);
59        self::assertStringContainsString('CAST(', $intResult);
60        $floatResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::FLOAT, 'TEXT'));
61        self::assertNotEmpty($floatResult);
62        self::assertStringContainsString('CAST(', $floatResult);
63        $doubleResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::DOUBLE, 'TEXT'));
64        self::assertNotEmpty($doubleResult);
65        self::assertStringContainsString('CAST(', $doubleResult);
66        $decimalResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::DECIMAL, 'TEXT'));
67        self::assertNotEmpty($decimalResult);
68        self::assertStringContainsString('CAST(', $decimalResult);
69        $stringResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::STRING, 'TEXT'));
70        self::assertNotEmpty($stringResult);
71        self::assertStringContainsString('CAST(', $stringResult);
72        $textResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::TEXT, 'TEXT'));
73        self::assertNotEmpty($textResult);
74        self::assertStringContainsString('CAST(', $textResult);
75        $boolResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::BOOLEAN, 'TEXT'));
76        self::assertNotEmpty($boolResult);
77        self::assertStringContainsString('CAST(', $boolResult);
78        $dateResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::DATE, 'TEXT'));
79        self::assertNotEmpty($dateResult);
80        self::assertStringContainsString('CAST(', $dateResult);
81        $timeResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::TIME, 'TEXT'));
82        self::assertNotEmpty($timeResult);
83        self::assertStringContainsString('CAST(', $timeResult);
84        $datetimeResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::DATETIME, 'TEXT'));
85        self::assertNotEmpty($datetimeResult);
86        self::assertStringContainsString('CAST(', $datetimeResult);
87        $timestampResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::TIMESTAMP, 'TEXT'));
88        self::assertNotEmpty($timestampResult);
89        self::assertStringContainsString('CAST(', $timestampResult);
90        $binaryResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::BINARY, 'TEXT'));
91        self::assertNotEmpty($binaryResult);
92        self::assertStringContainsString('CAST(', $binaryResult);
93        $jsonResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::JSON, 'TEXT'));
94        self::assertNotEmpty($jsonResult);
95        self::assertStringContainsString('CAST(', $jsonResult);
96        $unknownResult = $renderer->renderNullCast(new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'TEXT'));
97        self::assertNotEmpty($unknownResult);
98        self::assertStringContainsString('CAST(', $unknownResult);
99    }
100
101    public function testRenderCastForIntegerProducesExactForm(): void
102    {
103        $renderer = new SqliteCastRenderer();
104        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
105        $result = $renderer->renderCast("'42'", $type);
106        self::assertMatchesRegularExpression('/^CAST\(\'42\'\s+AS\s+\S+\)$/i', $result, 'renderCast for INTEGER must produce CAST(expression AS type) format');
107    }
108
109    public function testRenderNullCastProducesExactForm(): void
110    {
111        $renderer = new SqliteCastRenderer();
112        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
113        $result = $renderer->renderNullCast($type);
114        self::assertMatchesRegularExpression('/^CAST\(NULL\s+AS\s+\S+\)$/i', $result, 'renderNullCast must produce CAST(NULL AS type) format');
115    }
116
117    public function testRenderCastForStringIncludesStringType(): void
118    {
119        $renderer = new SqliteCastRenderer();
120        $type = new ColumnDeclaration(ColumnTypeFamily::STRING, 'TEXT');
121        $result = $renderer->renderCast("'hello'", $type);
122        $upper = strtoupper($result);
123        self::assertTrue(str_contains($upper, 'VARCHAR') || str_contains($upper, 'TEXT') || str_contains($upper, 'CHAR'), sprintf('renderCast for STRING must include VARCHAR, TEXT, or CHAR, got: %s', $result));
124    }
125
126    public function testRenderCastIsDeterministic(): void
127    {
128        $renderer = new SqliteCastRenderer();
129        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
130        $result1 = $renderer->renderCast('42', $type);
131        $result2 = $renderer->renderCast('42', $type);
132        self::assertSame($result1, $result2);
133        $nullResult1 = $renderer->renderNullCast($type);
134        $nullResult2 = $renderer->renderNullCast($type);
135        self::assertSame($nullResult1, $nullResult2);
136    }
137
138    public function testRenderCastInteger(): void
139    {
140        $renderer = new SqliteCastRenderer();
141        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
142        $result = $renderer->renderCast('42', $type);
143        self::assertSame('CAST(42 AS INTEGER)', $result);
144    }
145
146    public function testRenderCastFloat(): void
147    {
148        $renderer = new SqliteCastRenderer();
149        $type = new ColumnDeclaration(ColumnTypeFamily::FLOAT, 'FLOAT');
150        $result = $renderer->renderCast('3.14', $type);
151        self::assertSame('CAST(3.14 AS REAL)', $result);
152    }
153
154    public function testRenderCastDouble(): void
155    {
156        $renderer = new SqliteCastRenderer();
157        $type = new ColumnDeclaration(ColumnTypeFamily::DOUBLE, 'DOUBLE');
158        $result = $renderer->renderCast('3.14', $type);
159        self::assertSame('CAST(3.14 AS REAL)', $result);
160    }
161
162    public function testRenderCastDecimal(): void
163    {
164        $renderer = new SqliteCastRenderer();
165        $type = new ColumnDeclaration(ColumnTypeFamily::DECIMAL, 'DECIMAL(10,2)');
166        $result = $renderer->renderCast("'123.45'", $type);
167        self::assertSame("CAST('123.45' AS NUMERIC)", $result);
168    }
169
170    public function testRenderCastString(): void
171    {
172        $renderer = new SqliteCastRenderer();
173        $type = new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)');
174        $result = $renderer->renderCast("'hello'", $type);
175        self::assertSame("CAST('hello' AS TEXT)", $result);
176    }
177
178    public function testRenderCastText(): void
179    {
180        $renderer = new SqliteCastRenderer();
181        $type = new ColumnDeclaration(ColumnTypeFamily::TEXT, 'TEXT');
182        $result = $renderer->renderCast("'hello'", $type);
183        self::assertSame("CAST('hello' AS TEXT)", $result);
184    }
185
186    public function testRenderCastBoolean(): void
187    {
188        $renderer = new SqliteCastRenderer();
189        $type = new ColumnDeclaration(ColumnTypeFamily::BOOLEAN, 'BOOLEAN');
190        $result = $renderer->renderCast('1', $type);
191        self::assertSame('CAST(1 AS INTEGER)', $result);
192    }
193
194    public function testRenderCastBinary(): void
195    {
196        $renderer = new SqliteCastRenderer();
197        $type = new ColumnDeclaration(ColumnTypeFamily::BINARY, 'BLOB');
198        $result = $renderer->renderCast("X'DEADBEEF'", $type);
199        self::assertSame("CAST(X'DEADBEEF' AS BLOB)", $result);
200    }
201
202    public function testRenderCastDate(): void
203    {
204        $renderer = new SqliteCastRenderer();
205        $type = new ColumnDeclaration(ColumnTypeFamily::DATE, 'DATE');
206        $result = $renderer->renderCast("'2024-01-01'", $type);
207        self::assertSame("CAST('2024-01-01' AS TEXT)", $result);
208    }
209
210    public function testRenderCastDatetime(): void
211    {
212        $renderer = new SqliteCastRenderer();
213        $type = new ColumnDeclaration(ColumnTypeFamily::DATETIME, 'DATETIME');
214        $result = $renderer->renderCast("'2024-01-01 12:00:00'", $type);
215        self::assertSame("CAST('2024-01-01 12:00:00' AS TEXT)", $result);
216    }
217
218    public function testRenderCastTimestamp(): void
219    {
220        $renderer = new SqliteCastRenderer();
221        $type = new ColumnDeclaration(ColumnTypeFamily::TIMESTAMP, 'TIMESTAMP');
222        $result = $renderer->renderCast("'2024-01-01 12:00:00'", $type);
223        self::assertSame("CAST('2024-01-01 12:00:00' AS TEXT)", $result);
224    }
225
226    public function testRenderCastTime(): void
227    {
228        $renderer = new SqliteCastRenderer();
229        $type = new ColumnDeclaration(ColumnTypeFamily::TIME, 'TIME');
230        $result = $renderer->renderCast("'12:00:00'", $type);
231        self::assertSame("CAST('12:00:00' AS TEXT)", $result);
232    }
233
234    public function testRenderCastJson(): void
235    {
236        $renderer = new SqliteCastRenderer();
237        $type = new ColumnDeclaration(ColumnTypeFamily::JSON, 'JSON');
238        $result = $renderer->renderCast("'{}'", $type);
239        self::assertSame("CAST('{}' AS TEXT)", $result);
240    }
241
242    public function testRenderNullCastInteger(): void
243    {
244        $renderer = new SqliteCastRenderer();
245        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
246        $result = $renderer->renderNullCast($type);
247        self::assertSame('CAST(NULL AS INTEGER)', $result);
248    }
249
250    public function testRenderNullCastText(): void
251    {
252        $renderer = new SqliteCastRenderer();
253        $type = new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(255)');
254        $result = $renderer->renderNullCast($type);
255        self::assertSame('CAST(NULL AS TEXT)', $result);
256    }
257
258    public function testRenderNullCastBoolean(): void
259    {
260        $renderer = new SqliteCastRenderer();
261        $type = new ColumnDeclaration(ColumnTypeFamily::BOOLEAN, 'BOOLEAN');
262        $result = $renderer->renderNullCast($type);
263        self::assertSame('CAST(NULL AS INTEGER)', $result);
264    }
265
266    public function testRenderCastUnknownFamilyWithNativeType(): void
267    {
268        $renderer = new SqliteCastRenderer();
269        $type = new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'INTEGER');
270        $result = $renderer->renderCast('42', $type);
271        self::assertSame('CAST(42 AS INTEGER)', $result);
272    }
273
274    public function testRenderCastUnknownFamilyWithBlobType(): void
275    {
276        $renderer = new SqliteCastRenderer();
277        $type = new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'BLOB');
278        $result = $renderer->renderNullCast($type);
279        self::assertSame('CAST(NULL AS BLOB)', $result);
280    }
281
282    public function testRenderCastUnknownFamilyWithUnknownNativeType(): void
283    {
284        $renderer = new SqliteCastRenderer();
285        $type = new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'CUSTOM_TYPE');
286        $result = $renderer->renderNullCast($type);
287        self::assertSame('CAST(NULL AS TEXT)', $result);
288    }
289
290    public function testRenderCastUnknownFamilyWithIntType(): void
291    {
292        $renderer = new SqliteCastRenderer();
293        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'INT'));
294        self::assertSame('CAST(1 AS INTEGER)', $result);
295    }
296
297    public function testRenderCastUnknownFamilyWithTinyintType(): void
298    {
299        $renderer = new SqliteCastRenderer();
300        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'TINYINT'));
301        self::assertSame('CAST(1 AS INTEGER)', $result);
302    }
303
304    public function testRenderCastUnknownFamilyWithSmallintType(): void
305    {
306        $renderer = new SqliteCastRenderer();
307        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'SMALLINT'));
308        self::assertSame('CAST(1 AS INTEGER)', $result);
309    }
310
311    public function testRenderCastUnknownFamilyWithMediumintType(): void
312    {
313        $renderer = new SqliteCastRenderer();
314        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'MEDIUMINT'));
315        self::assertSame('CAST(1 AS INTEGER)', $result);
316    }
317
318    public function testRenderCastUnknownFamilyWithBigintType(): void
319    {
320        $renderer = new SqliteCastRenderer();
321        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'BIGINT'));
322        self::assertSame('CAST(1 AS INTEGER)', $result);
323    }
324
325    public function testRenderCastUnknownFamilyWithBooleanType(): void
326    {
327        $renderer = new SqliteCastRenderer();
328        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'BOOLEAN'));
329        self::assertSame('CAST(1 AS INTEGER)', $result);
330    }
331
332    public function testRenderCastUnknownFamilyWithBoolType(): void
333    {
334        $renderer = new SqliteCastRenderer();
335        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'BOOL'));
336        self::assertSame('CAST(1 AS INTEGER)', $result);
337    }
338
339    public function testRenderCastUnknownFamilyWithRealType(): void
340    {
341        $renderer = new SqliteCastRenderer();
342        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'REAL'));
343        self::assertSame('CAST(1.0 AS REAL)', $result);
344    }
345
346    public function testRenderCastUnknownFamilyWithDoubleType(): void
347    {
348        $renderer = new SqliteCastRenderer();
349        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'DOUBLE'));
350        self::assertSame('CAST(1.0 AS REAL)', $result);
351    }
352
353    public function testRenderCastUnknownFamilyWithFloatType(): void
354    {
355        $renderer = new SqliteCastRenderer();
356        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'FLOAT'));
357        self::assertSame('CAST(1.0 AS REAL)', $result);
358    }
359
360    public function testRenderCastUnknownFamilyWithDecimalType(): void
361    {
362        $renderer = new SqliteCastRenderer();
363        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'DECIMAL'));
364        self::assertSame('CAST(1.0 AS NUMERIC)', $result);
365    }
366
367    public function testRenderCastUnknownFamilyWithNumericType(): void
368    {
369        $renderer = new SqliteCastRenderer();
370        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'NUMERIC'));
371        self::assertSame('CAST(1.0 AS NUMERIC)', $result);
372    }
373
374    public function testRenderCastUnknownFamilyWithLowercaseNativeType(): void
375    {
376        $renderer = new SqliteCastRenderer();
377        $result = $renderer->renderCast('1', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'integer'));
378        self::assertSame('CAST(1 AS INTEGER)', $result);
379    }
380
381    public function testRenderCastUnknownFamilyWithParenthesizedType(): void
382    {
383        $renderer = new SqliteCastRenderer();
384        $result = $renderer->renderCast('1.0', new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'DECIMAL(10,2)'));
385        self::assertSame('CAST(1.0 AS NUMERIC)', $result);
386    }
387
388    public function testDeterminism(): void
389    {
390        $renderer = new SqliteCastRenderer();
391        $type = new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER');
392        $result1 = $renderer->renderCast('42', $type);
393        $result2 = $renderer->renderCast('42', $type);
394        self::assertSame($result1, $result2);
395    }
396
397    public function testMapNativeTypeToCastTypeReturnsString(): void
398    {
399        $renderer = new SqliteCastRenderer();
400        $type = new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'INT(11)');
401        $result = $renderer->renderCast('1', $type);
402        self::assertSame('CAST(1 AS INTEGER)', $result);
403    }
404
405    public function testMapNativeTypeToCastTypeBaseTypeExtracted(): void
406    {
407        $renderer = new SqliteCastRenderer();
408        $type = new ColumnDeclaration(ColumnTypeFamily::UNKNOWN, 'VARCHAR(255)');
409        $result = $renderer->renderCast("'x'", $type);
410        self::assertSame("CAST('x' AS TEXT)", $result);
411    }
412
413    /**
414     * @return list<array{ColumnTypeFamily, string}>
415     */
416    public static function providerColumnTypes(): array
417    {
418        return [
419            [ColumnTypeFamily::INTEGER, 'INTEGER'],
420            [ColumnTypeFamily::FLOAT, 'REAL'],
421            [ColumnTypeFamily::DOUBLE, 'REAL'],
422            [ColumnTypeFamily::DECIMAL, 'NUMERIC'],
423            [ColumnTypeFamily::STRING, 'TEXT'],
424            [ColumnTypeFamily::TEXT, 'TEXT'],
425            [ColumnTypeFamily::BOOLEAN, 'INTEGER'],
426            [ColumnTypeFamily::DATE, 'TEXT'],
427            [ColumnTypeFamily::TIME, 'TEXT'],
428            [ColumnTypeFamily::DATETIME, 'TEXT'],
429            [ColumnTypeFamily::TIMESTAMP, 'TEXT'],
430            [ColumnTypeFamily::BINARY, 'BLOB'],
431            [ColumnTypeFamily::JSON, 'TEXT'],
432            [ColumnTypeFamily::UNKNOWN, 'TEXT'],
433        ];
434    }
435}
436