packages/sql-catalog/tests/Unit/Reporter/Html/Page/TableIndexPageTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Reporter\Html\Page;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use SqlCatalog\Core\Catalog\AnalysisProblem;
11use SqlCatalog\Core\Catalog\CallSite;
12use SqlCatalog\Core\Catalog\Catalog;
13use SqlCatalog\Core\Catalog\CatalogEntry;
14use SqlCatalog\Core\Catalog\Finding;
15use SqlCatalog\Core\Catalog\FindingRule;
16use SqlCatalog\Core\Catalog\Placeholder;
17use SqlCatalog\Core\Catalog\Resolution;
18use SqlCatalog\Core\Catalog\Severity;
19use SqlCatalog\Core\Catalog\StatementPart;
20use SqlCatalog\Core\Catalog\ValueDomain;
21use SqlCatalog\Core\Sql\StatementKind;
22use SqlCatalog\Core\Text\LiteralText;
23use SqlCatalog\Core\Text\Origin;
24use SqlCatalog\Core\Text\TextHole;
25use SqlCatalog\Core\Text\TextPattern;
26use SqlCatalog\Core\Type\TypeShape;
27use SqlCatalog\Reporter\Html\CatalogIndex;
28use SqlCatalog\Reporter\Html\CatalogStatistics;
29use SqlCatalog\Reporter\Html\HtmlText;
30use SqlCatalog\Reporter\Html\Page\TableIndexPage;
31use SqlCatalog\Reporter\Html\Palette;
32use SqlCatalog\Reporter\Html\ReportSite;
33use SqlCatalog\Reporter\Html\Scope;
34use SqlCatalog\Reporter\Html\SqlFormatter;
35use SqlCatalog\Reporter\Html\SqlHighlighter;
36use SqlCatalog\Reporter\Html\StatementList;
37use SqlCatalog\Reporter\Html\StatementRow;
38use SqlCatalog\Reporter\Html\TableName;
39
40#[CoversClass(TableIndexPage::class)]
41#[UsesClass(CallSite::class)]
42#[UsesClass(Catalog::class)]
43#[UsesClass(CatalogEntry::class)]
44#[UsesClass(CatalogIndex::class)]
45#[UsesClass(CatalogStatistics::class)]
46#[UsesClass(Finding::class)]
47#[UsesClass(FindingRule::class)]
48#[UsesClass(HtmlText::class)]
49#[UsesClass(ReportSite::class)]
50#[UsesClass(Scope::class)]
51#[UsesClass(Severity::class)]
52#[UsesClass(TableName::class)]
53#[UsesClass(StatementKind::class)]
54#[UsesClass(LiteralText::class)]
55#[UsesClass(TextPattern::class)]
56#[UsesClass(AnalysisProblem::class)]
57#[UsesClass(Placeholder::class)]
58#[UsesClass(ValueDomain::class)]
59#[UsesClass(Resolution::class)]
60#[UsesClass(StatementPart::class)]
61#[UsesClass(Palette::class)]
62#[UsesClass(SqlFormatter::class)]
63#[UsesClass(SqlHighlighter::class)]
64#[UsesClass(StatementList::class)]
65#[UsesClass(StatementRow::class)]
66#[UsesClass(Origin::class)]
67#[UsesClass(TextHole::class)]
68#[UsesClass(TypeShape::class)]
69final class TableIndexPageTest extends TestCase
70{
71 public function testRenderGroupsTablesBySchemaWhenAnyIsQualified(): void
72 {
73 $catalog = new Catalog([
74 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users', 'app.orders'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
75 ]);
76 $page = (new TableIndexPage())->render(new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()));
77
78 self::assertStringContainsString('<h1>Tables<span class="count">2 tables</span></h1>', $page);
79 self::assertStringContainsString('<h2 id="schema-unqualified">Unqualified<span class="count">1 table</span></h2>', $page);
80 self::assertStringContainsString('<h2 id="schema-app">app<span class="count">1 table</span></h2>', $page);
81 self::assertStringContainsString('No statement names a table.', (new TableIndexPage())->render(new ReportSite(new Catalog(), formatter: \SqlCatalog\Facade\Builtins::sqlFormatter())));
82 }
83
84 public function testRenderDoesNotGroupWhenNoTableIsQualified(): void
85 {
86 $catalog = new Catalog([
87 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
88 ]);
89
90 self::assertStringNotContainsString('Unqualified', (new TableIndexPage())->render(new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter())));
91 }
92
93 public function testBySchemaPutsUnqualifiedTablesUnderAnEmptyName(): void
94 {
95 self::assertSame(['', 'app'], array_keys((new TableIndexPage())->bySchema(['app.orders' => [], 'users' => []])));
96 }
97
98 public function testTableIsSortableByEveryColumn(): void
99 {
100 $table = (new TableIndexPage())->table(new ReportSite(new Catalog(), formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()), []);
101
102 self::assertStringContainsString('<table class="sortable filter-target" data-dd-sortable><thead><tr><th scope="col" data-dd-sort="text">Table</th><th scope="col" class="num" data-dd-sort="number">Statements</th>', $table);
103 }
104
105 public function testRowCountsHowTheTableIsUsedAndMarksAGapInItsName(): void
106 {
107 $entries = [
108 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['{$}users'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), [Finding::of(FindingRule::DynamicSql, 'x')]),
109 new CatalogEntry('a2', StatementKind::Alter, TextPattern::fromText('ALTER'), ['{$}users'], [], new CallSite('a.php', 2, 'g', 'pdo.query'), []),
110 ];
111 $site = new ReportSite(new Catalog($entries), formatter: \SqlCatalog\Facade\Builtins::sqlFormatter());
112
113 self::assertSame(
114 '<tr><td><a class="mono" href="tables/users.html"><span class="hole tone-warn" title="A part of this name the analysis could not pin down">{$}</span>us'
115 . 'ers</a></td><td class="num">2</td><td class="num">1</td><td class="num"><span class="none">0</span></td><td class="num">1</td><td class="num">1</td><t'
116 . 'd class="num">2</td></tr>',
117 (new TableIndexPage())->row($site, '{$}users', $entries),
118 );
119 }
120
121 public function testRenderIsWrittenExactly(): void
122 {
123 $entries = [
124 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromSegments([
125 new LiteralText('SELECT id FROM posts WHERE slug = '),
126 new TextHole(Origin::External, TypeShape::unknown(), '$_GET["s"]'),
127 ]), ['posts'], [new Placeholder('?', 0, null, new ValueDomain('int', [7], true, []))], new CallSite('src/a.php', 4, 'App\\R::find', 'pdo.query'), [
128 Finding::of(FindingRule::ExternalInput, 'spliced'),
129 ], false, ['App\\R::run', 'App\\R::find']),
130 new CatalogEntry('a2', StatementKind::Insert, TextPattern::fromText('INSERT INTO posts (id) VALUES (1)'), ['posts'], [], new CallSite('src/a.php', 9, 'App\\R::add', 'pdo.query'), []),
131 new CatalogEntry('a3', StatementKind::Update, TextPattern::fromText('UPDATE posts SET title = ? WHERE id = ?'), ['posts'], [], new CallSite('src/a.php', 14, 'App\\R::add', 'pdo.prepare'), [
132 Finding::of(FindingRule::PlaceholderCountMismatch, 'one bound'),
133 ]),
134 new CatalogEntry('b1', StatementKind::Delete, TextPattern::fromText('DELETE FROM users WHERE id = 1'), ['users'], [], new CallSite('src/b.php', 3, 'App\\Admin\\U::drop', 'pdo.query'), []),
135 new CatalogEntry('b2', StatementKind::Alter, TextPattern::fromText('ALTER TABLE users ADD x INT'), ['users'], [], new CallSite('src/b.php', 8, 'App\\Admin\\U::migrate', 'pdo.exec'), []),
136 new CatalogEntry('c1', StatementKind::Select, TextPattern::fromHole(new TextHole(Origin::Budget, TypeShape::unknown())), [], [], new CallSite('lib/c.php', 2, 'helper', 'mysqli.query'), [
137 Finding::of(FindingRule::AnalysisIncomplete, 'stopped'),
138 ], true, [], true),
139 new CatalogEntry('c2', StatementKind::Unknown, TextPattern::fromHole(new TextHole(Origin::Unreached, TypeShape::unknown(), '$db->query($sql)')), [], [], new CallSite('lib/c.php', 6, '{main}', 'unmatched'), [
140 Finding::of(FindingRule::CallNotAnalyzed, 'unseen'),
141 ]),
142 new CatalogEntry('d1', StatementKind::Select, TextPattern::fromText('SELECT * FROM posts p JOIN users u ON u.id = p.author'), ['posts', 'users'], [], new CallSite('src/d.php', 1, 'App\\R::find', 'pdo.query'), [
143 Finding::of(FindingRule::DynamicSql, 'dyn'),
144 ]),
145 new CatalogEntry('e1', StatementKind::Show, TextPattern::fromText('SHOW TABLES'), [], [], new CallSite('src/e.php', 1, 'App\\R::find', 'pdo.query'), []),
146 new CatalogEntry('f1', StatementKind::Select, TextPattern::fromText('SELECT 1 FROM t1'), ['t1'], [], new CallSite('src/f.php', 1, 'App\\F::a', 'pdo.query'), []),
147 new CatalogEntry('f2', StatementKind::Select, TextPattern::fromText('SELECT 2 FROM t2'), ['t2'], [], new CallSite('src/f.php', 2, 'App\\F::b', 'pdo.query'), []),
148 new CatalogEntry('f3', StatementKind::Select, TextPattern::fromText('SELECT 3 FROM t3'), ['t3'], [], new CallSite('src/f.php', 3, 'App\\G::a', 'pdo.query'), []),
149 new CatalogEntry('f4', StatementKind::Select, TextPattern::fromText('SELECT 4 FROM t4'), ['t4'], [], new CallSite('src/f.php', 4, 'App\\H::a', 'pdo.query'), []),
150 new CatalogEntry('f5', StatementKind::Select, TextPattern::fromText('SELECT 5 FROM t5'), ['t5'], [], new CallSite('src/g.php', 1, 'App\\I::a', 'pdo.query'), []),
151 new CatalogEntry('f6', StatementKind::Select, TextPattern::fromText('SELECT 6 FROM t6'), ['t6'], [], new CallSite('src/h.php', 1, 'App\\J::a', 'pdo.query'), []),
152 new CatalogEntry('f7', StatementKind::Select, TextPattern::fromText('SELECT 7 FROM t7'), ['t7'], [], new CallSite('src/i.php', 1, 'App\\K::a', 'pdo.query'), [
153 Finding::of(FindingRule::DynamicSql, 'dyn'),
154 ]),
155 new CatalogEntry('f8', StatementKind::Select, TextPattern::fromText('SELECT 8 FROM t8'), ['t8'], [], new CallSite('src/j.php', 1, 'App\\L::a', 'pdo.query'), [
156 Finding::of(FindingRule::DynamicSql, 'dyn'),
157 ]),
158 new CatalogEntry('f9', StatementKind::Select, TextPattern::fromText('SELECT 9 FROM t9'), ['t9'], [], new CallSite('src/k.php', 1, 'App\\M::a', 'pdo.query'), [
159 Finding::of(FindingRule::DynamicSql, 'dyn'),
160 ]),
161 ];
162 $catalog = new Catalog($entries, [new AnalysisProblem('src/broken.php', 'broken')]);
163 $site = new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter());
164
165 self::assertSame(
166 '<h1>Tables<span class="count">11 tables</span></h1><p class="lede">Every table the statements name, most named first. A join counts for each of its ta'
167 . 'bles. Open a table to see what reads, writes and alters it, and from where.</p><input type="search" name="filter" class="input input-block" data-filte'
168 . 'r-rows placeholder="Narrow by table name…" aria-label="Narrow by table name" autocomplete="off" spellcheck="false"><div class="table-wrap"><table clas'
169 . 's="sortable filter-target" data-dd-sortable><thead><tr><th scope="col" data-dd-sort="text">Table</th><th scope="col" class="num" data-dd-sort="number"'
170 . '>Statements</th><th scope="col" class="num" data-dd-sort="number">Reads</th><th scope="col" class="num" data-dd-sort="number">Writes</th><th scope="co'
171 . 'l" class="num" data-dd-sort="number">Schema</th><th scope="col" class="num" data-dd-sort="number">Attention</th><th scope="col" class="num" data-dd-so'
172 . 'rt="number">Functions</th></tr></thead><tbody><tr><td><a class="mono" href="tables/posts.html">posts</a></td><td class="num">4</td><td class="num">2</'
173 . 'td><td class="num">2</td><td class="num"><span class="none">0</span></td><td class="num">3</td><td class="num">2</td></tr><tr><td><a class="mono" href'
174 . '="tables/users.html">users</a></td><td class="num">3</td><td class="num">1</td><td class="num">1</td><td class="num">1</td><td class="num">1</td><td c'
175 . 'lass="num">3</td></tr><tr><td><a class="mono" href="tables/t1.html">t1</a></td><td class="num">1</td><td class="num">1</td><td class="num"><span class'
176 . '="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num">1</td></tr><tr><t'
177 . 'd><a class="mono" href="tables/t2.html">t2</a></td><td class="num">1</td><td class="num">1</td><td class="num"><span class="none">0</span></td><td cla'
178 . 'ss="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num">1</td></tr><tr><td><a class="mono" href="tabl'
179 . 'es/t3.html">t3</a></td><td class="num">1</td><td class="num">1</td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">'
180 . '0</span></td><td class="num"><span class="none">0</span></td><td class="num">1</td></tr><tr><td><a class="mono" href="tables/t4.html">t4</a></td><td c'
181 . 'lass="num">1</td><td class="num">1</td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num"'
182 . '><span class="none">0</span></td><td class="num">1</td></tr><tr><td><a class="mono" href="tables/t5.html">t5</a></td><td class="num">1</td><td class="'
183 . 'num">1</td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span>'
184 . '</td><td class="num">1</td></tr><tr><td><a class="mono" href="tables/t6.html">t6</a></td><td class="num">1</td><td class="num">1</td><td class="num"><'
185 . 'span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num">1</td><'
186 . '/tr><tr><td><a class="mono" href="tables/t7.html">t7</a></td><td class="num">1</td><td class="num">1</td><td class="num"><span class="none">0</span></'
187 . 'td><td class="num"><span class="none">0</span></td><td class="num">1</td><td class="num">1</td></tr><tr><td><a class="mono" href="tables/t8.html">t8</'
188 . 'a></td><td class="num">1</td><td class="num">1</td><td class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td'
189 . ' class="num">1</td><td class="num">1</td></tr><tr><td><a class="mono" href="tables/t9.html">t9</a></td><td class="num">1</td><td class="num">1</td><td'
190 . ' class="num"><span class="none">0</span></td><td class="num"><span class="none">0</span></td><td class="num">1</td><td class="num">1</td></tr></tbody>'
191 . '</table></div>',
192 (new TableIndexPage())->render($site),
193 );
194 }
195}
196