404-solution / includes / database / DatabaseCollationHelper.php

DatabaseCollationHelper.php in 404 Solution trunk, at includes/database/DatabaseCollationHelper.php

427 lines 18.1 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Resolves and reconciles MySQL collation for plugin tables and columns.
9	*
10	* Extracted from DatabaseCore as part of the (3/6) DatabaseCore decomposition.
11	* Owns four cohesive responsibilities that all turn on knowing the effective
12	* collation of plugin tables/columns so cross-collation comparisons do not
13	* blow up plugin queries:
14	*
15	* 1. Sanitizing raw collation identifiers (strip non-word characters so they
16	* are safe to interpolate in SQL).
17	* 2. Discovering the effective table-level and column-level collation via
18	* SHOW CREATE TABLE and information_schema, with safe fallbacks.
19	* 3. Resolving the preferred utf8mb4 collation from the wpdb connection.
20	* 4. Responding to a query-time collation mismatch by scheduling a
21	* schema-wide correction outside the foreground request.
22	*
23	* This class holds no DatabaseCore back-reference. It receives:
24	* - a query-runner callable bound over DatabaseCore::queryAndGetResults
25	* (signature: function(string, array<string,mixed>): array<string,mixed>);
26	* - a DDL reader callable bound over the table-name resolver
27	* (signature: function(string): string);
28	* - getter/setter callables for runtime flags
29	* (signatures: function(string): mixed, function(string, mixed, int): void);
30	* - the plugin logger and an optional clock.
31	*
32	* The recursion guard is a static property on this class (it must survive across
33	* helper invocations within a single request) and is functionally identical to
34	* the former DatabaseCore::$collationRecoveryInProgress.
35	*/
36	class ABJ_404_Solution_DatabaseCollationHelper {
37
38	/** @var int Cooldown after a collation-recovery attempt (seconds). */
39	const COLLATION_RECOVERY_COOLDOWN_SECONDS = 3600;
40
41	/**
42	* @var string Collation used whenever the site's own cannot be honoured.
43	* Present on every MySQL 5.5.3+ and MariaDB build the plugin supports.
44	*/
45	const DEFAULT_UTF8MB4_COLLATION = 'utf8mb4_unicode_ci';
46
47	/** @var bool Prevent recursive collation-repair scheduling within one request. */
48	private static $collationSchedulingInProgress = false;
49
50	/** @var callable(string, array<string,mixed>): array<string,mixed> */
51	private $queryRunner;
52
53	/** @var callable(string): string */
54	private $ddlReader;
55
56	/** @var callable(string): mixed */
57	private $runtimeFlagGetter;
58
59	/** @var callable(string, mixed, int): void */
60	private $runtimeFlagSetter;
61
62	/** @var ABJ_404_Solution_Logging */
63	private $logger;
64
65	/** @var ABJ_404_Solution_Clock\|null */
66	private $clock;
67
68	/**
69	* @param callable(string, array<string,mixed>): array<string,mixed> $queryRunner
70	* Runs a SQL query through the centralized error-handling pipeline and
71	* returns its result array. Bound by DatabaseCore over queryAndGetResults().
72	* @param callable(string): string $ddlReader
73	* Returns the SHOW CREATE TABLE output for the given table name (or '').
74	* @param callable(string): mixed $runtimeFlagGetter
75	* Returns the current value of a runtime flag (transient with option fallback).
76	* @param callable(string, mixed, int): void $runtimeFlagSetter
77	* Persists a runtime-flag value with a TTL.
78	* @param ABJ_404_Solution_Logging $logger
79	* @param ABJ_404_Solution_Clock\|null $clock Optional; lazily resolved when null.
80	*/
81	public function __construct(
82	callable $queryRunner,
83	callable $ddlReader,
84	callable $runtimeFlagGetter,
85	callable $runtimeFlagSetter,
86	$logger,
87	$clock = null
88	) {
89	$this->queryRunner = $queryRunner;
90	$this->ddlReader = $ddlReader;
91	$this->runtimeFlagGetter = $runtimeFlagGetter;
92	$this->runtimeFlagSetter = $runtimeFlagSetter;
93	$this->logger = $logger;
94	$this->clock = $clock;
95	}
96
97	/**
98	* Reset the static recursion guard. Intended for test setUp/tearDown only.
99	*
100	* @return void
101	*/
102	public static function resetRecursionGuardForTests(): void {
103	self::$collationSchedulingInProgress = false;
104	}
105
106	/**
107	* Sanitize a raw collation identifier so it is safe to interpolate in SQL.
108	*
109	* Strips every character that is not [A-Za-z0-9_].
110	*
111	* @param string $collation
112	* @return string
113	*/
114	public function sanitizeCollationIdentifier($collation): string {
115	return self::sanitizeCollationName($collation);
116	}
117
118	/**
119	* Sanitize a raw collation identifier (pure; no connection required).
120	*
121	* The instance method above delegates here so producers that own no
122	* DatabaseCollationHelper -- the SQL template resolver and the admin table
123	* query policy -- reach the same sanitizer instead of copying the regex.
124	*
125	* @param mixed $collation Raw identifier from wpdb, information_schema or DDL.
126	* @return string Sanitized identifier, or '' when nothing survives.
127	*/
128	public static function sanitizeCollationName($collation): string {
129	if (!is_string($collation) \|\| $collation === '') {
130	return '';
131	}
132	$sanitized = preg_replace('/[^A-Za-z0-9_]/', '', $collation);
133	return $sanitized !== null ? $sanitized : '';
134	}
135
136	/**
137	* A collation guaranteed valid for CHARACTER SET utf8mb4.
138	*
139	* The one derivation every caller that CONVERTs (or CASTs) an expression to
140	* a hard-coded utf8mb4 must use. MySQL rejects the statement outright --
141	* "COLLATION 'x' is not valid for CHARACTER SET 'utf8mb4'", errno 1253 --
142	* when the two halves name different families, and $wpdb->collate carries
143	* whatever DB_COLLATE says, which on installs whose wp-config predates
144	* utf8mb4 is routinely a latin1 or utf8mb3 collation.
145	*
146	* A site collation already in the utf8mb4 family is kept, so ordering and
147	* comparison semantics stay the site's own; anything else is replaced,
148	* because there is no way to honour it under a utf8mb4 expression at all.
149	*
150	* @param mixed $rawCollation Typically $wpdb->collate.
151	* @return string A utf8mb4_* collation name, safe to interpolate.
152	*/
153	public static function utf8mb4CollationOrFallback($rawCollation): string {
154	$sanitized = self::sanitizeCollationName($rawCollation);
155	if (self::isUtf8mb4Collation($sanitized)) {
156	return $sanitized;
157	}
158	return self::DEFAULT_UTF8MB4_COLLATION;
159	}
160
161	/**
162	* Whether a collation belongs to the utf8mb4 family.
163	*
164	* The sole owner of that rule. Callers that need the question answered but
165	* keep their own control flow around the answer (pick this collation, or
166	* fall back to a different source; emit this clause, or a simpler one) ask
167	* here instead of re-testing the name, because a second copy of the rule is
168	* how errno 1253 reached production in the first place.
169	*
170	* MySQL collation names are `<charset>_<...>`, so family membership is a
171	* PREFIX test. A substring test also accepts a name that merely contains
172	* "utf8mb4", which would pair a foreign collation with a hard-coded
173	* `CHARACTER SET utf8mb4` -- the precise statement the engine rejects.
174	*
175	* @param mixed $collation Raw identifier from wpdb, information_schema or DDL.
176	* @return bool
177	*/
178	public static function isUtf8mb4Collation($collation): bool {
179	$sanitized = self::sanitizeCollationName($collation);
180	return $sanitized !== '' && stripos($sanitized, 'utf8mb4') === 0;
181	}
182
183	/**
184	* The charset a collation belongs to, returned together with it.
185	*
186	* The mirror of {@see utf8mb4CollationOrFallback} for callers that must
187	* honour a specific collation (a column's own, so a JOIN against it stays
188	* sargable) and therefore cannot choose the charset independently. Returning
189	* the pair rather than the charset alone is the point: a caller physically
190	* cannot take one half from here and the other from somewhere else.
191	*
192	* MySQL collation names are `<charset>_<...>`, so the charset is the segment
193	* before the first underscore; `binary` names both and has no underscore,
194	* which the same rule handles. An unusable input falls back to the utf8mb4
195	* pair rather than to a half-formed one.
196	*
197	* @param mixed $rawCollation Collation to honour (column, table or wpdb).
198	* @return array{charset: string, collation: string} Consistent pair.
199	*/
200	public static function charsetCollationPair($rawCollation): array {
201	$collation = self::sanitizeCollationName($rawCollation);
202	$charset = $collation === '' ? '' : self::sanitizeCollationName(explode('_', $collation, 2)[0]);
203	if ($charset === '') {
204	return array('charset' => 'utf8mb4', 'collation' => self::DEFAULT_UTF8MB4_COLLATION);
205	}
206	return array('charset' => $charset, 'collation' => $collation);
207	}
208
209	/**
210	* Get the table-level default collation for a given table.
211	*
212	* Queries SHOW CREATE TABLE for the COLLATE clause; falls back to
213	* information_schema.TABLES.TABLE_COLLATION; then to utf8mb4_unicode_ci.
214	* Result is validated through sanitizeCollationIdentifier().
215	*
216	* @param string $tableName Fully-qualified table name (including prefix).
217	* @return string
218	*/
219	public function getTableCollationString(string $tableName): string {
220	$fallback = 'utf8mb4_unicode_ci';
221	$ddl = ($this->ddlReader)($tableName);
222
223	// The TABLE-level collation is a table option, written after the
224	// closing paren of the body. A column may carry a COLLATE of its own
225	// and columns come first, so reading the first COLLATE anywhere in the
226	// statement answers a different question than the one asked -- and this
227	// method's answer decides the collation every cross-collation
228	// comparison is coerced to. The plugin's own staging templates now
229	// state a per-column COLLATE, so the two are not hypothetically
230	// distinguishable, they routinely differ.
231	$tableDefault = ABJ_404_Solution_CreateTableOptionsParser::tableCharsetAndCollation($ddl);
232	$declaredCollation = ($tableDefault === null) ? null : $tableDefault['collation'];
233	if ($declaredCollation !== null) {
234	$sanitized = $this->sanitizeCollationIdentifier($declaredCollation);
235	return $sanitized !== '' ? $sanitized : $fallback;
236	}
237	global $wpdb;
238	if (isset($wpdb) && method_exists($wpdb, 'prepare')) {
239	/** @var wpdb $wpdb */
240	$sql = $wpdb->prepare(
241	"SELECT TABLE_COLLATION FROM information_schema.TABLES "
242	. "WHERE TABLE_SCHEMA = DATABASE() "
243	. "AND TABLE_NAME = %s "
244	. "LIMIT 1",
245	$tableName
246	);
247	if (is_string($sql) && $sql !== '') {
248	$result = ($this->queryRunner)($sql, array('log_errors' => false));
249	$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
250	if (!empty($rows) && is_array($rows[0])) {
251	$row = array_change_key_case($rows[0]);
252	$collation = $row['table_collation'] ?? '';
253	if (is_string($collation) && $collation !== '') {
254	$sanitized = $this->sanitizeCollationIdentifier($collation);
255	return $sanitized !== '' ? $sanitized : $fallback;
256	}
257	}
258	}
259	}
260	return $fallback;
261	}
262
263	/**
264	* Get the column-level collation for a specific column in a table.
265	*
266	* Queries information_schema.COLUMNS for the COLLATION_NAME. Falls back
267	* to getTableCollationString() if the column query fails, then ultimately
268	* to utf8mb4_unicode_ci. Result is validated through
269	* sanitizeCollationIdentifier().
270	*
271	* @param string $tableName Fully-qualified table name (including prefix).
272	* @param string $columnName Column name to look up.
273	* @return string
274	*/
275	public function getColumnCollationString(string $tableName, string $columnName): string {
276	$fallback = 'utf8mb4_unicode_ci';
277	global $wpdb;
278	if (!isset($wpdb) \|\| !method_exists($wpdb, 'prepare')) {
279	return $this->getTableCollationString($tableName);
280	}
281	/** @var wpdb $wpdb */
282	$sql = $wpdb->prepare(
283	"SELECT COLLATION_NAME FROM information_schema.COLUMNS "
284	. "WHERE TABLE_SCHEMA = DATABASE() "
285	. "AND TABLE_NAME = %s "
286	. "AND COLUMN_NAME = %s "
287	. "LIMIT 1",
288	$tableName,
289	$columnName
290	);
291	if (!is_string($sql) \|\| $sql === '') {
292	return $this->getTableCollationString($tableName);
293	}
294	$result = ($this->queryRunner)($sql, array('log_errors' => false));
295	$rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
296	if (empty($rows) \|\| !is_array($rows[0])) {
297	return $this->getTableCollationString($tableName);
298	}
299	$row = array_change_key_case($rows[0]);
300	$collation = $row['collation_name'] ?? '';
301	if (!is_string($collation) \|\| $collation === '') {
302	return $this->getTableCollationString($tableName);
303	}
304	$sanitized = $this->sanitizeCollationIdentifier($collation);
305	return $sanitized !== '' ? $sanitized : $fallback;
306	}
307
308	/**
309	* Coerce a SQL expression to the charset and collation of an indexed
310	* comparison column without wrapping that indexed column.
311	*
312	* Mixed-collation sites are common during upgrades and partial restores.
313	* Applying CONVERT/COLLATE only to the non-indexed operand makes the
314	* equality deterministic while leaving the target column sargable.
315	* Callers must supply an internally constructed SQL expression; this
316	* method sanitizes metadata identifiers, not arbitrary SQL text.
317	*
318	* @param string $expression SQL expression used opposite the target column.
319	* @param array{table: string, column: string} $targetColumn Indexed target column metadata.
320	* @return string
321	*/
322	public function coerceExpressionToColumnCollation(string $expression, array $targetColumn): string {
323	$tableName = $targetColumn['table'] ?? '';
324	$columnName = $targetColumn['column'] ?? '';
325	if ($tableName === '' \|\| $columnName === '') {
326	throw new InvalidArgumentException('Target table and column are required for a collation-safe SQL comparison.');
327	}
328
329	$rawCollation = $this->getColumnCollationString($tableName, $columnName);
330	if (self::sanitizeCollationName($rawCollation) === '') {
331	throw new InvalidArgumentException('Target column collation could not be converted to a safe SQL identifier.');
332	}
333	$pair = self::charsetCollationPair($rawCollation);
334
335	return 'CONVERT(' . $expression . ' USING ' . $pair['charset'] . ') COLLATE ' . $pair['collation'];
336	}
337
338	/**
339	* Return the preferred utf8mb4 collation for this wpdb connection.
340	*
341	* If wpdb->collate already names a utf8mb4_* collation, use it; otherwise
342	* fall back to utf8mb4_unicode_ci.
343	*
344	* @return string
345	*/
346	public function getPreferredUtf8mb4Collation(): string {
347	global $wpdb;
348	$rawCollation = (isset($wpdb) && isset($wpdb->collate) && is_scalar($wpdb->collate))
349	? (string)$wpdb->collate : '';
350	return self::utf8mb4CollationOrFallback($rawCollation);
351	}
352
353	/**
354	* Schedule broad collation correction outside the foreground request.
355	*
356	* correctCollations() discovers every plugin table and may issue ALTER
357	* TABLE ... CONVERT for each drifted table. Running that work inline can
358	* exhaust an admin AJAX request on large sites, so the query that detected
359	* the mismatch keeps its normal degraded error result while a dedicated,
360	* deduplicated WP-Cron event performs the repair. Scheduler failures remain
361	* retryable and are logged with the adapter's underlying failure detail.
362	*
363	* @return void
364	*/
365	public function scheduleCollationRecovery(): void {
366	if (self::$collationSchedulingInProgress) {
367	return;
368	}
369
370	$cooldownKey = 'abj404_collation_recovery_cooldown';
371	$cooldownUntil = ($this->runtimeFlagGetter)($cooldownKey);
372	$onCooldown = is_scalar($cooldownUntil) && (int)$cooldownUntil > $this->clock()->now();
373
374	if ($onCooldown) {
375	return;
376	}
377
378	self::$collationSchedulingInProgress = true;
379	try {
380	$scheduler = abj_cron_scheduler();
381	$scheduled = $scheduler->scheduleSingleIfMissing(
382	ABJ_404_Solution_CronScheduler::HOOK_REPAIR_COLLATIONS,
383	1
384	);
385	if ($scheduled) {
386	($this->runtimeFlagSetter)(
387	$cooldownKey,
388	$this->clock()->now() + self::COLLATION_RECOVERY_COOLDOWN_SECONDS,
389	self::COLLATION_RECOVERY_COOLDOWN_SECONDS
390	);
391	$this->logger->infoMessage(
392	'Collation mismatch detected: schema correction scheduled for background repair.'
393	);
394	return;
395	}
396	$this->logger->warn(
397	'Could not schedule background collation repair: ' . $scheduler->lastFailureDetail()
398	);
399	} catch (Throwable $e) {
400	$this->logger->warn(
401	'Could not schedule background collation repair: ' . $e->getMessage()
402	);
403	} finally {
404	self::$collationSchedulingInProgress = false;
405	}
406	}
407
408	/**
409	* Lazily resolve the clock instance.
410	*
411	* @return ABJ_404_Solution_Clock
412	*/
413	private function clock() {
414	if ($this->clock === null) {
415	if (class_exists('ABJ_404_Solution_ServiceContainer')) {
416	$resolved = ABJ_404_Solution_ServiceContainer::safeGet('clock');
417	if ($resolved instanceof ABJ_404_Solution_Clock) {
418	$this->clock = $resolved;
419	return $this->clock;
420	}
421	}
422	$this->clock = new ABJ_404_Solution_SystemClock();
423	}
424	return $this->clock;
425	}
426	}
427