404-solution / includes / database / TableIndexDefinitions.php

TableIndexDefinitions.php in 404 Solution 4.3.3, at includes/database/TableIndexDefinitions.php

370 lines 18.3 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* The canonical definition of a table index -- its ordered column list, the
9	* per-column prefix lengths, and uniqueness -- expressed identically whether it
10	* was read from the live engine (SHOW INDEX) or parsed out of one of the
11	* plugin's create*Table.sql templates, so the two can actually be compared.
12	*
13	* This module owns the live-engine side: the SHOW INDEX probe, and the folding
14	* of the rows it returns into per-index definitions under the describability
15	* invariant below. What one ROW said, field by field, is a different job --
16	* coping with the case its keys arrive in and the several spellings each engine
17	* uses for the same number -- and lives in
18	* {@see ABJ_404_Solution_ShowIndexRowReader}. Its two neighbours own the other
19	* halves of the picture. Turning DDL SOURCE TEXT into
20	* a spec is a regex parser over SQL rather than a normalization of driver
21	* metadata, and lives in {@see ABJ_404_Solution_CreateTableIndexParser};
22	* reducing either representation to a comparable signature, and deciding
23	* whether the two agree, lives in
24	* {@see ABJ_404_Solution_IndexDefinitionComparator}. The dependency runs one
25	* way, from the comparator down to both producers, which is what lets this
26	* reader stay ignorant of the DDL parser entirely.
27	*
28	* Why this exists as its own module: the plugin used to hold those two halves
29	* in different places and never compared them. The upgrade path asked "is there
30	* an index with this NAME?" (SHOW INDEX ... WHERE Key_name = ...) and the admin
31	* read gate asked the same name-only question of its own SHOW INDEX probe.
32	* Neither looked at what the index actually contained. That is not a
33	* hypothetical gap: MySQL and MariaDB silently REMOVE a dropped column from
34	* every index that names it, keeping the index and its name (an index whose
35	* only column is dropped is dropped with it). So a table that ran a plugin
36	* build whose DDL predated a column -- a downgrade, a rolled-back beta -- comes
37	* back with, for example, `idx_status_disabled_logshits_id` still present but
38	* defined as (status, disabled, id). Every later upgrade saw the name, declared
39	* the index present, and moved on; the admin sort it was built for filesorted
40	* the whole table forever after. Having one place that answers what an index
41	* ACTUALLY CONTAINS is what makes comparing it the natural operation instead of
42	* an optional extra.
43	*
44	* Everything here is read-only: it reads schema metadata and normalizes it. It
45	* issues no DDL and makes no repair decisions -- that is
46	* {@see ABJ_404_Solution_DatabaseUpgradeIndexes}'s job.
47	*
48	* Normalization follows defensive philosophy #3 (normalize before comparing)
49	* and #5 (case-insensitive metadata access): SHOW INDEX column names come back
50	* in varying case depending on the driver, and index/column names are compared
51	* case-insensitively because MySQL identifiers are.
52	*
53	* The contract this reader owes its consumers: AN UNKNOWN STAYS UNKNOWN. Every
54	* field of an index's identity -- its column order, its prefix lengths, its
55	* uniqueness -- is either read or it is not, and a field that was not read
56	* makes the index undescribable rather than taking a default. An index left
57	* with no readable columns is not describable either, because there is no such
58	* index. {@see isDescribable()} is how that answer travels; the comparator
59	* refuses to produce a signature for anything it says no to, so the caller
60	* cannot end up comparing an index nobody described and reading the mismatch as
61	* a reason to rewrite the table.
62	*/
63	class ABJ_404_Solution_TableIndexDefinitions {
64
65	/** @var ABJ_404_Solution_DatabaseQueryInterface */
66	private $dbCore;
67
68	/**
69	* Error logging is intentionally delegated to queryAndGetResults (the
70	* centralized DAO error handler), so no logger dependency is held here.
71	*
72	* @param ABJ_404_Solution_DatabaseQueryInterface $dbCore
73	*/
74	public function __construct(ABJ_404_Solution_DatabaseQueryInterface $dbCore) {
75	$this->dbCore = $dbCore;
76	}
77
78	/**
79	* Every index the engine reports for a table, keyed by LOWERCASED index
80	* name, each with its columns in Seq_in_index order.
81	*
82	* Returns NULL when the probe could not be answered (missing table, denied
83	* permission, dead connection). That is deliberately distinct from an empty
84	* array: "I could not read the schema" and "this table has no indexes" call
85	* for opposite responses, and conflating them is how a repair pass would
86	* decide every index on an unreadable table is missing and start issuing
87	* DDL against it. Callers must handle null explicitly.
88	*
89	* @param string $tableName Fully-qualified table name.
90	* @return array<string, array{name: string, columns: array<int, array{column: string, prefix: int\|null}>, unique: bool}>\|null
91	*/
92	public function readLive(string $tableName) {
93	$quotedTableName = self::quoteIdentifier($tableName);
94	if ($quotedTableName === null) {
95	// Not a name we can safely put in a statement. Report it the same
96	// way an unanswerable probe is reported -- "unknown", not "no
97	// indexes" -- so no caller reads it as a table needing every index
98	// rebuilt.
99	return null;
100	}
101	$showIndexResult = $this->dbCore->queryAndGetResults("SHOW INDEX FROM " . $quotedTableName,
102	array('log_errors' => false));
103	$lastError = isset($showIndexResult['last_error']) && is_scalar($showIndexResult['last_error'])
104	? (string)$showIndexResult['last_error'] : '';
105	if ($lastError !== '' \|\| !is_array($showIndexResult['rows'] ?? null)) {
106	return null;
107	}
108	return self::fromShowIndexRows(array_values($showIndexResult['rows']));
109	}
110
111	/**
112	* A table name rendered as a quoted SQL identifier, or null when it is not
113	* one.
114	*
115	* `SHOW INDEX` takes an identifier, which cannot be a bound parameter, so
116	* the name is validated against the identifier grammar and then quoted
117	* per segment. Quoting the whole of "db.table" as one unit would name a
118	* table with a dot in it, which is why the split is not cosmetic.
119	* Unquoted MySQL identifiers are ASCII letters, digits, underscore and
120	* dollar, plus U+0080 and above; anything else (a backtick, a space, a
121	* semicolon) means this is not a plugin table name and the probe is
122	* refused rather than escaped into something plausible.
123	*
124	* Public because the column probe next to this one needs the same grammar:
125	* two reads of the same table that disagree about which names are safe to
126	* interpolate is how one of them ends up interpolating a name the other
127	* would have refused.
128	*
129	* @param string $tableName
130	* @return string\|null
131	*/
132	public static function quoteIdentifier(string $tableName): ?string {
133	if ($tableName === '') {
134	return null;
135	}
136
137	$segments = explode('.', $tableName);
138	$quoted = array();
139	foreach ($segments as $segment) {
140	if ($segment === '' \|\| !preg_match('/^[A-Za-z0-9_$\x{0080}-\x{FFFF}]+$/u', $segment)) {
141	return null;
142	}
143	$quoted[] = '`' . $segment . '`';
144	}
145
146	return implode('.', $quoted);
147	}
148
149	/**
150	* Whether a live definition was fully describable from the rows the engine
151	* reported.
152	*
153	* A false answer means "this index exists, but we cannot say what it
154	* contains" -- so it must be compared against nothing and repaired by
155	* nothing. Callers that treat an absent index as missing must consult this
156	* before concluding anything about an index that IS present.
157	*
158	* An absent answer is an unknown, not a yes. Every producer sets the flag
159	* today, so the default never decides anything -- but a default of TRUE
160	* means the first producer that ever forgets it gets a table rewrite rather
161	* than a skip, which is the one direction this flag exists to prevent.
162	*
163	* @param array{describable?: bool} $definition
164	* @return bool
165	*/
166	public static function isDescribable(array $definition): bool {
167	return isset($definition['describable']) && $definition['describable'] === true;
168	}
169
170	/**
171	* Assemble SHOW INDEX rows into per-index definitions.
172	*
173	* Split from readLive() so the assembly is exercisable against captured
174	* driver output without a live server, which is what lets the whole
175	* engine-variance matrix be tested at all: the row key case, and whether
176	* Sub_part arrives as null / '' / '0' / '190', differ across drivers and
177	* engines, and each row is read through
178	* {@see ABJ_404_Solution_ShowIndexRowReader} before it gets here.
179	*
180	* Returns NULL when a row cannot be read at all, which is a failed probe
181	* rather than a description of the table -- the same "unknown, not empty"
182	* contract readLive() carries, for the same reason.
183	*
184	* @param array<int, mixed> $rows Raw SHOW INDEX rows, associative.
185	* @return array<string, array{name: string, columns: array<int, array{column: string, prefix: int\|null}>, unique: bool, describable: bool}>\|null
186	*/
187	public static function fromShowIndexRows(array $rows) {
188	$names = array();
189	$unique = array();
190	$uniqueReported = array();
191	$bySeq = array();
192	$opaque = array();
193	foreach ($rows as $row) {
194	if (!is_array($row)) {
195	// A row shape we cannot read at all means this SHOW INDEX
196	// answer is not a description of the table. Returning the rest
197	// as if it were complete is what lets a real index be reported
198	// absent and re-created; the caller must be told the probe
199	// failed instead.
200	return null;
201	}
202	$fields = ABJ_404_Solution_ShowIndexRowReader::normalizedFields($row);
203	$name = isset($fields['key_name']) && is_scalar($fields['key_name'])
204	? (string)$fields['key_name'] : '';
205	$column = isset($fields['column_name']) && is_scalar($fields['column_name'])
206	? (string)$fields['column_name'] : '';
207	if ($name === '') {
208	// A row with no index name cannot be filed under any index, so
209	// some part of this table's definition is unaccounted for. Same
210	// reasoning as above: fail the probe rather than under-report.
211	return null;
212	}
213	$key = strtolower($name);
214	if (!isset($bySeq[$key])) {
215	$names[$key] = $name;
216	$bySeq[$key] = array();
217	// Placeholder only. It is meaningless while $opaque[$key] is
218	// set, and the block immediately below is what decides whether
219	// it ever becomes meaningful.
220	$unique[$key] = false;
221	}
222	// Uniqueness is part of an index's identity exactly as its column
223	// order and prefix lengths are, so it gets the same treatment they
224	// do: unreadable means undescribable, never a default. Reading a
225	// missing Non_unique as "not unique" made the three UNIQUE KEYs the
226	// plugin ships compare as drifted against their own DDL, and the
227	// repair path answers drift by emptying the spelling cache and
228	// rewriting the index -- destruction over metadata nobody read.
229	// Rows that contradict each other describe two different indexes,
230	// so taking the first one's word for it picks one at random.
231	$reportedUnique = ABJ_404_Solution_ShowIndexRowReader::readUniqueFlag($fields);
232	if ($reportedUnique === null
233	\|\| (isset($uniqueReported[$key]) && $uniqueReported[$key] !== $reportedUnique)) {
234	$opaque[$key] = true;
235	} else {
236	$uniqueReported[$key] = $reportedUnique;
237	$unique[$key] = $reportedUnique;
238	}
239	if ($column === '') {
240	// A MariaDB/MySQL functional index reports a NULL Column_name and
241	// carries the expression in Expression instead. The plugin ships
242	// none, and a definition we cannot describe must never be judged
243	// as drifted.
244	//
245	// Dropping the row is NOT how to achieve that: an index whose
246	// rows all vanish is absent from the returned map, and an absent
247	// index reads as MISSING to the repair path, which then issues
248	// CREATE INDEX for a name that already exists. Record the index
249	// as present and mark it undescribable instead, so comparison
250	// and repair both skip it.
251	$opaque[$key] = true;
252	continue;
253	}
254	$placement = ABJ_404_Solution_ShowIndexRowReader::readColumnPlacement($fields, $column);
255	if ($placement === null) {
256	$opaque[$key] = true;
257	continue;
258	}
259	$seq = $placement['position'];
260	$entry = $placement['entry'];
261	if (isset($bySeq[$key][$seq]) && $bySeq[$key][$seq] !== $entry) {
262	// Two rows disagreeing about which column sits at one position
263	// describe two different indexes, exactly as two rows
264	// disagreeing about uniqueness do. Letting the later row win
265	// silently drops a column, and an index reported with two
266	// columns and recorded with one compares as drift against its
267	// own DDL. An identical repeat contradicts nothing and is kept.
268	$opaque[$key] = true;
269	continue;
270	}
271	$bySeq[$key][$seq] = $entry;
272	}
273
274	$definitions = array();
275	foreach ($bySeq as $key => $columns) {
276	ksort($columns);
277	// SHOW INDEX numbers an index's columns 1..n, so a missing number
278	// is a row that never ARRIVED -- a truncated result, a row lost
279	// between server and client -- rather than one this version could
280	// not read. Nothing above catches that: every skip path marks the
281	// index opaque, but a row that was never delivered was never
282	// skipped, so the flag stays clean. What is left is a SUBSET of the
283	// index's columns in an order the index does not have, and a wrong
284	// order compares as drift exactly as a wrong column does.
285	// array_values() below discards the numbers, so this is the last
286	// point at which the gap can be seen at all.
287	$positionsComplete = array_keys($columns) === range(1, count($columns));
288	$definitions[$key] = array(
289	'name' => $names[$key],
290	'columns' => array_values($columns),
291	'unique' => $unique[$key],
292	// One undescribable row is enough to make the whole index
293	// unsafe to compare: with a row missing, the remaining column
294	// ORDER is not the index's real order, and a wrong order
295	// compares as drift and triggers a needless table rewrite.
296	//
297	// An index with no readable columns left is stated here rather
298	// than left to follow from the skips above. It follows today --
299	// every skip sets $opaque -- but "no columns" is a description
300	// of an index that cannot exist, and the invariant that such a
301	// thing is never handed out as comparable should not depend on
302	// a future skip path remembering to set the flag.
303	'describable' => !isset($opaque[$key]) && count($columns) > 0
304	&& $positionsComplete,
305	);
306	}
307	return $definitions;
308	}
309
310	/**
311	* Whether a live definition actually indexes the named column.
312	*
313	* This is the question the admin read gate has to ask before ordering by a
314	* narrow sort key: an index can be present under the right name and still
315	* not contain the column the sort needs, in which case ORDER BY on it
316	* filesorts the whole partition.
317	*
318	* @param array{columns?: array<int, array{column: string, prefix: int\|null}>, describable?: bool} $definition
319	* @param string $column
320	* @return bool
321	*/
322	public static function containsColumn(array $definition, string $column): bool {
323	$needle = strtolower($column);
324	if ($needle === '') {
325	return false;
326	}
327	if (!self::isDescribable($definition)) {
328	// The column list of an undescribable index is a SUBSET of its
329	// columns -- a row this version could not read, or one that never
330	// arrived, is simply absent from it. The sort-readiness gate reads
331	// a yes here as proof the index can serve an ORDER BY, and a wrong
332	// yes tells the read path a sort is index-ordered while it
333	// filesorts the whole captured partition. No is the same fallback
334	// that gate already takes when the probe itself is unreadable, and
335	// deciding it here means no future caller has to remember to.
336	return false;
337	}
338	$columns = isset($definition['columns']) && is_array($definition['columns'])
339	? $definition['columns'] : array();
340	foreach ($columns as $indexedColumn) {
341	if (isset($indexedColumn['column']) && strtolower((string)$indexedColumn['column']) === $needle) {
342	return true;
343	}
344	}
345	return false;
346	}
347
348	/**
349	* A human-readable rendering of a live definition, for log lines and
350	* diagnostic payloads: "status, disabled, logshits, id".
351	*
352	* @param array{columns?: array<int, array{column: string, prefix: int\|null}>} $definition
353	* @return string
354	*/
355	public static function describeColumns(array $definition): string {
356	$columns = isset($definition['columns']) && is_array($definition['columns'])
357	? $definition['columns'] : array();
358	$parts = array();
359	foreach ($columns as $column) {
360	$name = isset($column['column']) ? (string)$column['column'] : '';
361	if ($name === '') {
362	continue;
363	}
364	$parts[] = $name . (isset($column['prefix']) ? '(' . (int)$column['prefix'] . ')' : '');
365	}
366	return implode(', ', $parts);
367	}
368
369	}
370