packages/sql-catalog/tests/Unit/Reporter/Html/SqlFormatterTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Reporter\Html;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use SqlCatalog\Core\Catalog\StatementPart;
12use SqlCatalog\Reporter\Html\SqlFormatter;
13use SqlFormatter\Core\FormattingException;
14
15#[CoversClass(SqlFormatter::class)]
16#[UsesClass(StatementPart::class)]
17final class SqlFormatterTest extends TestCase
18{
19 /**
20 * @return list<array{string, string}>
21 */
22 public static function providerFormat(): array
23 {
24 return [
25 ['SELECT 1', "SELECT\n 1"],
26 ['SELECT id,name FROM users WHERE active=1 AND age>=18 ORDER BY name', "SELECT\n id,\n name\nFROM\n users\nWHERE\n active = 1\n AND age >= 18\nORDER BY\n name"],
27 ['UPDATE t SET a = 1, b = 2 WHERE id = 1', "UPDATE\n t\nSET\n a = 1,\n b = 2\nWHERE\n id = 1"],
28 ['SELECT * FROM users WHERE id = :id', "SELECT\n *\nFROM\n users\nWHERE\n id = :id"],
29 ['SELECT DISTINCT ON (id) id FROM users WHERE id=$1', "SELECT DISTINCT\n ON (id) id\nFROM\n users\nWHERE\n id = $1"],
30 ['INSERT OR IGNORE INTO t (id) VALUES (?)', "INSERT\n OR IGNORE\nINTO\n t (id)\nVALUES\n (?)"],
31 ];
32 }
33
34 #[DataProvider('providerFormat')]
35 public function testFormatExpandsSqlAcrossSupportedDialects(string $sql, string $expected): void
36 {
37 $formatter = \SqlCatalog\Facade\Builtins::sqlFormatter();
38 $parts = $formatter->format([new StatementPart($sql)]);
39
40 self::assertSame($expected, implode('', array_map(static fn (StatementPart $part): string => $part->text, $parts)));
41 self::assertEquals($parts, $formatter->format($parts));
42 }
43
44 public function testFormatPreservesGapIdentityAndMetadata(): void
45 {
46 $table = new StatementPart('', true, 'parameter', 'a parameter', '$table');
47 $id = new StatementPart('', true, 'call', 'a call result', 'readId()', '$id');
48 $parts = (\SqlCatalog\Facade\Builtins::sqlFormatter())->format([new StatementPart('SELECT * FROM '), $table, new StatementPart(' WHERE id = '), $id]);
49
50 self::assertSame("SELECT\n *\nFROM\n ", $parts[0]->text);
51 self::assertSame($table, $parts[1]);
52 self::assertSame("\nWHERE\n id = ", $parts[2]->text);
53 self::assertSame($id, $parts[3]);
54 self::assertCount(4, $parts);
55 }
56
57 public function testMaskPreservesLiteralMarkersAndRestoresGapsWithoutCollisions(): void
58 {
59 $gap = new StatementPart('', true, 'external', 'external input', '$value');
60 $parts = (\SqlCatalog\Facade\Builtins::sqlFormatter())->format([
61 new StatementPart("SELECT '__sql_catalog_gap_0__', '{\$}', 'prefix"),
62 $gap,
63 new StatementPart("suffix' FROM `table_"),
64 $gap,
65 new StatementPart('`'),
66 ]);
67
68 self::assertSame("SELECT\n '__sql_catalog_gap_0__',\n '{\$}',\n 'prefix", $parts[0]->text);
69 self::assertSame($gap, $parts[1]);
70 self::assertSame("suffix'\nFROM\n `table_", $parts[2]->text);
71 self::assertSame($gap, $parts[3]);
72 self::assertSame('`', $parts[4]->text);
73 }
74
75 public function testFormatPreservesLiteralWhitespaceAndLineCommentBoundaries(): void
76 {
77 $parts = (\SqlCatalog\Facade\Builtins::sqlFormatter())->format([new StatementPart("SELECT 'a b', 1 -- keep this\nFROM users WHERE id = ?")]);
78
79 self::assertStringContainsString("'a b'", $parts[0]->text);
80 self::assertStringContainsString("-- keep this\n", $parts[0]->text);
81 self::assertStringContainsString("WHERE\n id = ?", $parts[0]->text);
82 }
83
84 public function testFormatLeavesUnsupportedAndUnknownStatementsIntact(): void
85 {
86 $formatter = \SqlCatalog\Facade\Builtins::sqlFormatter();
87 $gap = new StatementPart('', true, 'call', 'a call result', 'sql()');
88 $invalid = [new StatementPart("SELECT (\n "), $gap];
89
90 self::assertSame($invalid, $formatter->format($invalid));
91 self::assertSame([$gap], $formatter->format([$gap]));
92 self::assertSame([], $formatter->format([]));
93 self::assertSame('', $formatter->format([new StatementPart('')])[0]->text);
94 }
95
96 public function testMaskParametersPreservesClientPlaceholdersAndPostgresCasts(): void
97 {
98 $formatter = \SqlCatalog\Facade\Builtins::sqlFormatter();
99 $sql = "SELECT :id::integer, '%s', :id FROM users WHERE name = %s";
100 [$masked, $replacements] = $formatter->maskParameters($sql, [], '__test_');
101
102 self::assertSame("SELECT __test_0__::integer, '%s', __test_1__ FROM users WHERE name = __test_2__", $masked);
103 self::assertSame($sql, implode('', array_map(static fn (StatementPart $part): string => $part->text, $formatter->restore($masked, $replacements))));
104 self::assertStringContainsString("FROM\n users\nWHERE\n name = %s", implode('', array_map(static fn (StatementPart $part): string => $part->text, $formatter->format([new StatementPart($sql)]))));
105 }
106
107 public function testRestoreRejectsLostGaps(): void
108 {
109 $this->expectException(FormattingException::class);
110
111 (\SqlCatalog\Facade\Builtins::sqlFormatter())->restore('SELECT 1', ['missing' => new StatementPart('', true)]);
112 }
113}
114