packages/sql-catalog/tests/Unit/Reporter/Html/Page/TablePageTest.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\TablePage;
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(TablePage::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(Palette::class)]
50#[UsesClass(ReportSite::class)]
51#[UsesClass(Resolution::class)]
52#[UsesClass(Scope::class)]
53#[UsesClass(Severity::class)]
54#[UsesClass(SqlHighlighter::class)]
55#[UsesClass(StatementKind::class)]
56#[UsesClass(StatementList::class)]
57#[UsesClass(StatementPart::class)]
58#[UsesClass(StatementRow::class)]
59#[UsesClass(TableName::class)]
60#[UsesClass(LiteralText::class)]
61#[UsesClass(TextPattern::class)]
62#[UsesClass(AnalysisProblem::class)]
63#[UsesClass(Placeholder::class)]
64#[UsesClass(ValueDomain::class)]
65#[UsesClass(SqlFormatter::class)]
66#[UsesClass(Origin::class)]
67#[UsesClass(TextHole::class)]
68#[UsesClass(TypeShape::class)]
69final class TablePageTest extends TestCase
70{
71 public function testRenderSaysWhatUsesTheTableAndListsItsStatementsByWhatTheyDo(): void
72 {
73 $catalog = new Catalog([
74 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1 FROM users'), ['users', 'posts'], [], new CallSite('a.php', 1, 'App\\R::find', 'pdo.query'), []),
75 new CatalogEntry('a2', StatementKind::Insert, TextPattern::fromText('INSERT INTO users'), ['users'], [], new CallSite('a.php', 2, 'helper', 'pdo.query'), []),
76 ]);
77 $page = (new TablePage())->render(new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()), 'users');
78
79 self::assertStringContainsString('<h1><code>users</code><span class="count">2 statements</span></h1>', $page);
80 self::assertStringContainsString('read by 1 statement, written by 1 statement.', $page);
81 self::assertStringContainsString('<h2 id="used-from">Used from<span class="count">2 functions</span></h2>', $page);
82 self::assertStringContainsString('<h2 id="alongside">Named alongside</h2>', $page);
83 self::assertStringContainsString('<section class="group" id="writes"><h3>Writes<span class="count">1</span></h3>', $page);
84 self::assertStringContainsString('<section class="group" id="reads"><h3>Reads<span class="count">1</span></h3>', $page);
85 self::assertStringContainsString('href="../statements/a2.html"', $page);
86 }
87
88 public function testSummaryMentionsWhatNeedsAttention(): void
89 {
90 $summary = (new TablePage())->summary(new TableName('{$}'), ['reads' => 2, 'writes' => 0, 'schema' => 1, 'other' => 0, 'attention' => 1]);
91
92 self::assertSame('A table whose name the analysis could not pin down, read by 2 statements, altered by 1 statement. 1 statement carry a finding worth looking at.', $summary);
93 }
94
95 public function testUsedFromLinksEachFunctionAndSaysWhatItDoes(): void
96 {
97 $entries = [
98 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users'], [], new CallSite('src/a.php', 1, 'App\\R::find', 'pdo.query'), []),
99 new CatalogEntry('a2', StatementKind::Delete, TextPattern::fromText('DELETE'), ['users'], [], new CallSite('src/a.php', 2, 'App\\R::find', 'pdo.query'), []),
100 ];
101 $site = new ReportSite(new Catalog($entries), formatter: \SqlCatalog\Facade\Builtins::sqlFormatter());
102
103 self::assertStringContainsString(
104 '<tr><td><a class="mono" href="../classes/app-r.html#fn-app-r-find">R::find</a></td><td><a class="muted" href="../files/src-a-php.html">src/a.php</a></'
105 . 'td><td class="num">2</td><td class="tight">reads, writes</td></tr>',
106 (new TablePage())->usedFrom($site, $entries),
107 );
108 }
109
110 public function testVerbsNameEveryUseFound(): void
111 {
112 self::assertSame('reads, alters, other', (new TablePage())->verbs(['reads' => 1, 'writes' => 0, 'schema' => 2, 'other' => 1, 'attention' => 0]));
113 }
114
115 public function testAlongsideIsSilentWhenTheTableIsNamedAlone(): void
116 {
117 $catalog = new Catalog([
118 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
119 ]);
120
121 self::assertSame('', (new TablePage())->alongside(new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()), 'users'));
122 }
123
124 public function testGroupsListOnlyTheUsesPresent(): void
125 {
126 $entries = [
127 new CatalogEntry('a1', StatementKind::Show, TextPattern::fromText('SHOW TABLES'), ['users'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
128 ];
129 $groups = (new TablePage())->groups(new ReportSite(new Catalog($entries), formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()), 'users', $entries);
130
131 self::assertStringStartsWith('<section class="group" id="other"><h3>Other statements<span class="count">1</span></h3>', $groups);
132 self::assertStringNotContainsString('id="reads"', $groups);
133 }
134
135 public function testAnchorsFollowTheSectionsPresent(): void
136 {
137 $catalog = new Catalog([
138 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users', 'posts'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
139 new CatalogEntry('a2', StatementKind::Update, TextPattern::fromText('UPDATE'), ['users'], [], new CallSite('a.php', 2, 'f', 'pdo.query'), []),
140 ]);
141
142 self::assertSame(
143 [['Used from', 'used-from'], ['Named alongside', 'alongside'], ['Writes', 'writes'], ['Reads', 'reads']],
144 (new TablePage())->anchors(new CatalogIndex($catalog), 'users'),
145 );
146 }
147
148
149 public function testSummaryIsSilentAboutFindingsWhenThereAreNone(): void
150 {
151 self::assertSame(
152 'This table is read by 1 statement.',
153 (new TablePage())->summary(new TableName('users'), ['reads' => 1, 'writes' => 0, 'schema' => 0, 'other' => 0, 'attention' => 0]),
154 );
155 }
156
157 public function testContextListsTheSectionsAndEveryOtherTable(): void
158 {
159 $catalog = new Catalog([
160 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromText('SELECT 1'), ['users', 'posts'], [], new CallSite('a.php', 1, 'f', 'pdo.query'), []),
161 new CatalogEntry('a2', StatementKind::Select, TextPattern::fromText('SELECT 2'), ['users'], [], new CallSite('a.php', 2, 'f', 'pdo.query'), []),
162 ]);
163
164 self::assertSame(
165 [
166 ['On this page', [['Used from', '#used-from', null, false], ['Named alongside', '#alongside', null, false], ['Reads', '#reads', null, false]], null],
167 ['Tables', [['users', 'tables/users.html', 2, false], ['posts', 'tables/posts.html', 1, true]], 'tables.html'],
168 ],
169 (new TablePage())->context(new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter()), 'posts'),
170 );
171 }
172
173 public function testRenderIsWrittenExactly(): void
174 {
175 $entries = [
176 new CatalogEntry('a1', StatementKind::Select, TextPattern::fromSegments([
177 new LiteralText('SELECT id FROM posts WHERE slug = '),
178 new TextHole(Origin::External, TypeShape::unknown(), '$_GET["s"]'),
179 ]), ['posts'], [new Placeholder('?', 0, null, new ValueDomain('int', [7], true, []))], new CallSite('src/a.php', 4, 'App\\R::find', 'pdo.query'), [
180 Finding::of(FindingRule::ExternalInput, 'spliced'),
181 ], false, ['App\\R::run', 'App\\R::find']),
182 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'), []),
183 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'), [
184 Finding::of(FindingRule::PlaceholderCountMismatch, 'one bound'),
185 ]),
186 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'), []),
187 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'), []),
188 new CatalogEntry('c1', StatementKind::Select, TextPattern::fromHole(new TextHole(Origin::Budget, TypeShape::unknown())), [], [], new CallSite('lib/c.php', 2, 'helper', 'mysqli.query'), [
189 Finding::of(FindingRule::AnalysisIncomplete, 'stopped'),
190 ], true, [], true),
191 new CatalogEntry('c2', StatementKind::Unknown, TextPattern::fromHole(new TextHole(Origin::Unreached, TypeShape::unknown(), '$db->query($sql)')), [], [], new CallSite('lib/c.php', 6, '{main}', 'unmatched'), [
192 Finding::of(FindingRule::CallNotAnalyzed, 'unseen'),
193 ]),
194 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'), [
195 Finding::of(FindingRule::DynamicSql, 'dyn'),
196 ]),
197 new CatalogEntry('e1', StatementKind::Show, TextPattern::fromText('SHOW TABLES'), [], [], new CallSite('src/e.php', 1, 'App\\R::find', 'pdo.query'), []),
198 new CatalogEntry('f1', StatementKind::Select, TextPattern::fromText('SELECT 1 FROM t1'), ['t1'], [], new CallSite('src/f.php', 1, 'App\\F::a', 'pdo.query'), []),
199 new CatalogEntry('f2', StatementKind::Select, TextPattern::fromText('SELECT 2 FROM t2'), ['t2'], [], new CallSite('src/f.php', 2, 'App\\F::b', 'pdo.query'), []),
200 new CatalogEntry('f3', StatementKind::Select, TextPattern::fromText('SELECT 3 FROM t3'), ['t3'], [], new CallSite('src/f.php', 3, 'App\\G::a', 'pdo.query'), []),
201 new CatalogEntry('f4', StatementKind::Select, TextPattern::fromText('SELECT 4 FROM t4'), ['t4'], [], new CallSite('src/f.php', 4, 'App\\H::a', 'pdo.query'), []),
202 new CatalogEntry('f5', StatementKind::Select, TextPattern::fromText('SELECT 5 FROM t5'), ['t5'], [], new CallSite('src/g.php', 1, 'App\\I::a', 'pdo.query'), []),
203 new CatalogEntry('f6', StatementKind::Select, TextPattern::fromText('SELECT 6 FROM t6'), ['t6'], [], new CallSite('src/h.php', 1, 'App\\J::a', 'pdo.query'), []),
204 new CatalogEntry('f7', StatementKind::Select, TextPattern::fromText('SELECT 7 FROM t7'), ['t7'], [], new CallSite('src/i.php', 1, 'App\\K::a', 'pdo.query'), [
205 Finding::of(FindingRule::DynamicSql, 'dyn'),
206 ]),
207 new CatalogEntry('f8', StatementKind::Select, TextPattern::fromText('SELECT 8 FROM t8'), ['t8'], [], new CallSite('src/j.php', 1, 'App\\L::a', 'pdo.query'), [
208 Finding::of(FindingRule::DynamicSql, 'dyn'),
209 ]),
210 new CatalogEntry('f9', StatementKind::Select, TextPattern::fromText('SELECT 9 FROM t9'), ['t9'], [], new CallSite('src/k.php', 1, 'App\\M::a', 'pdo.query'), [
211 Finding::of(FindingRule::DynamicSql, 'dyn'),
212 ]),
213 ];
214 $catalog = new Catalog($entries, [new AnalysisProblem('src/broken.php', 'broken')]);
215 $site = new ReportSite($catalog, formatter: \SqlCatalog\Facade\Builtins::sqlFormatter());
216
217 self::assertSame(
218 '<h1><code>posts</code><span class="count">4 statements</span></h1><p class="lede">This table is read by 2 statements, written by 2 statement'
219 . 's. 3 statements carry a finding worth looking at.</p><h2 id="used-from">Used from<span class="count">2 functions</span></h2><div class="tabl'
220 . 'e-wrap"><table class="sortable" data-dd-sortable><thead><tr><th scope="col" data-dd-sort="text">Function</th><th scope="col" data-dd-sort="t'
221 . 'ext">File</th><th scope="col" class="num" data-dd-sort="number">Statements</th><th scope="col" class="tight">Does</th></tr></thead><tbody><t'
222 . 'r><td><a class="mono" href="../classes/app-r.html#fn-app-r-add">R::add</a></td><td><a class="muted" href="../files/src-a-php.html">src/a.php'
223 . '</a></td><td class="num">2</td><td class="tight">writes</td></tr><tr><td><a class="mono" href="../classes/app-r.html#fn-app-r-find">R::find<'
224 . '/a></td><td><a class="muted" href="../files/src-a-php.html">src/a.php</a></td><td class="num">2</td><td class="tight">reads</td></tr></tbody'
225 . '></table></div><h2 id="alongside">Named alongside</h2><p class="lede">Tables that appear in the same statements, usually through a join.</p>'
226 . '<div class="chips"><a class="chip chip-ghost" href="../tables/users.html">users<span class="facet-count">1</span></a></div><h2 id="statement'
227 . 's">Statements</h2><div data-narrowable><div class="facets" role="group" aria-label="Narrow the listing"><input type="search" name="narrow" c'
228 . 'lass="input facet-search" placeholder="Narrow by text…" aria-label="Narrow by text" autocomplete="off" spellcheck="false"><span class="fac'
229 . 'et-group"><button type="button" class="chip facet tone-blue" data-facet="kind" data-value="select" aria-pressed="false">SELECT<span class="f'
230 . 'acet-count">2</span></button><button type="button" class="chip facet tone-teal" data-facet="kind" data-value="insert" aria-pressed="false">I'
231 . 'NSERT<span class="facet-count">1</span></button><button type="button" class="chip facet tone-violet" data-facet="kind" data-value="update" a'
232 . 'ria-pressed="false">UPDATE<span class="facet-count">1</span></button></span><span class="facet-group"><button type="button" class="chip face'
233 . 't tone-ok" data-facet="resolution" data-value="resolved" aria-pressed="false">resolved<span class="facet-count">3</span></button><button typ'
234 . 'e="button" class="chip facet tone-danger" data-facet="resolution" data-value="external-input" aria-pressed="false">external-input<span class'
235 . '="facet-count">1</span></button></span><span class="facet-group"><button type="button" class="chip facet tone-warn" data-facet="severity" da'
236 . 'ta-value="medium" aria-pressed="false">medium<span class="facet-count">2</span></button><button type="button" class="chip facet tone-danger"'
237 . ' data-facet="severity" data-value="high" aria-pressed="false">high<span class="facet-count">1</span></button></span><span class="facet-shown'
238 . '" aria-live="polite"></span><button type="button" class="btn facet-clear" hidden>Clear</button></div><section class="group" id="writes"><h3>'
239 . 'Writes<span class="count">2</span></h3><ol class="rows"><li class="row" data-kind="insert" data-resolution="resolved" data-severity="" data-'
240 . 'rule="" data-sink="pdo.query" data-open="" data-table="posts" data-namespace="App" data-class="App\\R" data-function="App\\R::add" data-file="'
241 . 'src/a.php"><a class="row-main" href="../statements/a2.html"><span class="chip tone-teal">INSERT</span><pre class="code"><span class="tok-kw"'
242 . '>INSERT</span> <span class="tok-kw">INTO</span>
243 posts (id)
244<span class="tok-kw">VALUES</span>
245 (<span class="tok-num">1</span>)</pre>'
246 . '</a><p class="row-meta"><a href="../files/src-a-php.html">src/a.php:9</a><a href="../classes/app-r.html#fn-app-r-add">R::add</a><a class="ch'
247 . 'ip chip-ghost" href="../tables/posts.html">posts</a></p></li><li class="row" data-kind="update" data-resolution="resolved" data-severity="me'
248 . 'dium" data-rule="placeholder-count-mismatch" data-sink="pdo.prepare" data-open="" data-table="posts" data-namespace="App" data-class="App\\R"'
249 . ' data-function="App\\R::add" data-file="src/a.php"><a class="row-main" href="../statements/a3.html"><span class="chip tone-violet">UPDATE</sp'
250 . 'an><pre class="code"><span class="tok-kw">UPDATE</span>
251 posts
252<span class="tok-kw">SET</span>
253 title = <span class="tok-var">?</span>'
254 . '
255<span class="tok-kw">WHERE</span>
256 id = <span class="tok-var">?</span></pre></a><p class="row-meta"><a href="../files/src-a-php.html">sr'
257 . 'c/a.php:14</a><a href="../classes/app-r.html#fn-app-r-add">R::add</a><a class="chip chip-ghost" href="../tables/posts.html">posts</a><span c'
258 . 'lass="chip tone-warn" title="The most serious finding on this statement">medium</span></p></li></ol></section><section class="group" id="rea'
259 . 'ds"><h3>Reads<span class="count">2</span></h3><ol class="rows"><li class="row" data-kind="select" data-resolution="external-input" data-seve'
260 . 'rity="high" data-rule="external-input" data-sink="pdo.query" data-open="" data-table="posts" data-namespace="App" data-class="App\\R" data-fu'
261 . 'nction="App\\R::find" data-file="src/a.php"><a class="row-main" href="../statements/a1.html"><span class="chip tone-blue">SELECT</span><pre c'
262 . 'lass="code"><span class="tok-kw">SELECT</span>
263 id
264<span class="tok-kw">FROM</span>
265 posts
266<span class="tok-kw">WHERE</span>
267 slug '
268 . '= <span class="hole tone-danger" title="This is a gap: external input fills it. Written as $_GET["s"].">{$}</span></pre></a><p cla'
269 . 'ss="row-meta"><a href="../files/src-a-php.html">src/a.php:4</a><a href="../classes/app-r.html#fn-app-r-find">R::find</a><a class="chip chip-'
270 . 'ghost" href="../tables/posts.html">posts</a><span class="chip tone-danger" title="The values were followed to runtime input, so the text can'
271 . 'not be fixed.">external-input</span><span class="chip tone-danger" title="The most serious finding on this statement">high</span></p></li><l'
272 . 'i class="row" data-kind="select" data-resolution="resolved" data-severity="medium" data-rule="dynamic-sql" data-sink="pdo.query" data-open="'
273 . '" data-table="posts users" data-namespace="App" data-class="App\\R" data-function="App\\R::find" data-file="src/d.php"><a class="row-main" hre'
274 . 'f="../statements/d1.html"><span class="chip tone-blue">SELECT</span><pre class="code"><span class="tok-kw">SELECT</span>
275 *
276<span class="'
277 . 'tok-kw">FROM</span>
278 posts p
279<span class="tok-kw">JOIN</span>
280 users u
281<span class="tok-kw">ON</span>
282 u.id = p.author</pre></a><p '
283 . 'class="row-meta"><a href="../files/src-d-php.html">src/d.php:1</a><a href="../classes/app-r.html#fn-app-r-find">R::find</a><a class="chip ch'
284 . 'ip-ghost" href="../tables/posts.html">posts</a><a class="chip chip-ghost" href="../tables/users.html">users</a><span class="chip tone-warn" '
285 . 'title="The most serious finding on this statement">medium</span></p></li></ol></section></div>',
286 (new TablePage())->render($site, 'posts'),
287 );
288 }
289}
290