404-solution / includes / database / CreateTableOptionsParser.php

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

291 lines 11.6 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Parses the TABLE OPTIONS of a CREATE TABLE statement -- the engine, charset
9	* and collation an engine writes after the closing paren of the body -- out of
10	* either one of the plugin's own create*Table.sql templates or the text an
11	* engine hands back from SHOW CREATE TABLE.
12	*
13	* The third member of the family alongside
14	* {@see ABJ_404_Solution_CreateTableColumnParser} and
15	* {@see ABJ_404_Solution_CreateTableIndexParser}: pure text in, structure out,
16	* no database connection and no knowledge of what any engine currently reports.
17	* Between them the three cover the whole statement -- the body's column entries,
18	* the body's index entries, and everything after the body.
19	*
20	* Why this splits the statement instead of scanning it. A CREATE TABLE
21	* statement has exactly two regions, and the charset/collation question has a
22	* DIFFERENT answer in each. Inside the body,
23	*
24	* `url` varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
25	*
26	* is one column's override. After the body,
27	*
28	* ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
29	*
30	* is the table's default. No pattern applied to the concatenation of the two
31	* can tell which region it landed in, and columns come first in real engine
32	* output, so a scan of the whole statement reports the first COLUMN's charset
33	* as the table's. Searching harder -- taking the last match, excluding a
34	* keyword, requiring a preceding DEFAULT -- only moves which inputs it gets
35	* wrong. Finding the boundary first makes the confusion impossible rather than
36	* unlikely.
37	*
38	* That misread is not a near-miss a caller can second-guess. The collation
39	* drift path reads "utf8mb3" off a utf8mb4 table as drift off the canonical
40	* target and issues ALTER TABLE ... CONVERT against a table that never drifted,
41	* on exactly the utf8mb3-to-utf8mb4 population already on record for errno 1253.
42	*
43	* Every method returns null for a statement with no balanced body (truncated
44	* DDL, a partial read) rather than guessing. Callers have an exact source to
45	* fall through to -- information_schema -- and a wrong answer is worse than no
46	* answer for all of them.
47	*/
48	class ABJ_404_Solution_CreateTableOptionsParser {
49
50	/**
51	* The table-options section: everything after the close paren that matches
52	* the body's opening paren, with quoted runs and SQL comments blanked out.
53	*
54	* Public because the section is the boundary itself, and a caller reading a
55	* table option this class does not name yet should scope its own read to
56	* the section rather than reinventing the split.
57	*
58	* The scan starts at the CREATE TABLE keyword rather than at the start of
59	* the input, so a balanced paren pair that precedes it cannot be mistaken
60	* for the body. Text that is not a CREATE TABLE statement at all has no
61	* body and no options: an engine error string carries parens of its own
62	* ("ERROR 1146 (42S02): Table ... doesn't exist"), and reading ": Table ...
63	* doesn't exist" as this table's options is exactly the confident-wrong
64	* answer the class exists to rule out.
65	*
66	* @param string $createTableSql Raw SHOW CREATE TABLE output or a DDL template.
67	* @return string\|null The table-options text, or null when there is no balanced body.
68	*/
69	public static function tableOptionsSection($createTableSql): ?string {
70	if (!is_string($createTableSql) \|\| $createTableSql === '') {
71	return null;
72	}
73
74	$sql = self::blankQuotedRunsAndComments($createTableSql);
75
76	$statementStart = array();
77	if (!preg_match('/\bCREATE\s+(?:TEMPORARY\s+)?TABLE\b/i', $sql, $statementStart, PREG_OFFSET_CAPTURE)) {
78	return null;
79	}
80
81	$length = strlen($sql);
82	$depth = 0;
83	$bodyOpened = false;
84
85	for ($i = (int)$statementStart[0][1]; $i < $length; $i++) {
86	$char = $sql[$i];
87
88	if ($char === '(') {
89	$bodyOpened = true;
90	$depth++;
91	continue;
92	}
93
94	if ($char === ')' && $bodyOpened) {
95	$depth--;
96	if ($depth === 0) {
97	return substr($sql, $i + 1);
98	}
99	}
100	}
101
102	return null;
103	}
104
105	/**
106	* The table's default charset and collation, as the statement declares them.
107	*
108	* Either half may be null: a statement is free to state one and leave the
109	* other implicit. Deriving the missing half is the CALLER's decision, not
110	* this parser's -- the answer depends on what the caller is going to do with
111	* it, and one of them must additionally keep the pair inside a single
112	* charset family (errno 1253). This returns what is written, nothing more.
113	*
114	* Matches the several spellings MySQL and MariaDB emit across versions:
115	* CHARSET=x, DEFAULT CHARSET=x, CHARACTER SET=x, DEFAULT CHARACTER SET x,
116	* CHARSET = x, and the same shapes for COLLATE.
117	*
118	* @param string $createTableSql Raw SHOW CREATE TABLE output or a DDL template.
119	* @return array{charset: string\|null, collation: string\|null}\|null
120	* Null when the statement has no readable table-options section.
121	*/
122	public static function tableCharsetAndCollation($createTableSql): ?array {
123	$options = self::tableOptionsSection($createTableSql);
124	if ($options === null) {
125	return null;
126	}
127
128	$charsetMatch = array();
129	$collationMatch = array();
130
131	// (?:\s=\s\|\s+) requires either "=" (with optional surrounding
132	// spaces) or at least one space, so "CHARSETX" cannot match.
133	preg_match(
134	'/(?:DEFAULT\s+)?(?:CHARSET\|CHARACTER\s+SET)(?:\s=\s\|\s+)([\w\d]+)/i',
135	$options,
136	$charsetMatch
137	);
138	preg_match(
139	'/(?:DEFAULT\s+)?COLLATE(?:\s=\s\|\s+)([\w\d_]+)/i',
140	$options,
141	$collationMatch
142	);
143
144	return array(
145	'charset' => isset($charsetMatch[1]) ? $charsetMatch[1] : null,
146	'collation' => isset($collationMatch[1]) ? $collationMatch[1] : null,
147	);
148	}
149
150	/**
151	* Whether the statement declares a charset or a collation AS A TABLE
152	* OPTION, i.e. whether it already carries a table-level default.
153	*
154	* The question a producer asks before appending one of its own, and it has
155	* to be asked of the options section alone. A per-column
156	* `CHARACTER SET x COLLATE y` inside the body answers a DIFFERENT question,
157	* so a whole-statement scan reads one column's override as proof the whole
158	* table is covered and skips the default the table still needs. The plugin's
159	* own staging templates carry per-column charsets, so the two are not
160	* hypothetically distinguishable -- they routinely differ.
161	*
162	* A statement with no readable table-options section declares no table-level
163	* default, which is the answer a producer needs: append one.
164	*
165	* @param string $createTableSql Raw SHOW CREATE TABLE output or a DDL template.
166	* @return bool
167	*/
168	public static function declaresTableCharsetOrCollation($createTableSql): bool {
169	$declared = self::tableCharsetAndCollation($createTableSql);
170
171	return $declared !== null
172	&& ($declared['charset'] !== null \|\| $declared['collation'] !== null);
173	}
174
175	/**
176	* The storage engine the statement declares, or null when it declares none
177	* (or the statement has no readable table-options section).
178	*
179	* @param string $createTableSql Raw SHOW CREATE TABLE output or a DDL template.
180	* @return string\|null
181	*/
182	public static function tableEngine($createTableSql): ?string {
183	$options = self::tableOptionsSection($createTableSql);
184	if ($options === null) {
185	return null;
186	}
187
188	$engineMatch = array();
189	preg_match('/ENGINE(?:\s=\s\|\s+)([\w]+)/i', $options, $engineMatch);
190
191	return isset($engineMatch[1]) ? $engineMatch[1] : null;
192	}
193
194	/**
195	* Replace every quoted run and every SQL comment with an equal number of
196	* spaces, leaving the rest of the statement untouched.
197	*
198	* Quoted runs go because a table COMMENT is free text that may quote a
199	* charset the table no longer uses, and table options are order-independent
200	* in SQL, so that COMMENT is free to sit before the real ones. Comments go
201	* because prose is not SQL: the header of the plugin's own
202	* createLookupTable.sql ends "...(Armed Forces Europe, Middle East, &
203	* Canada)." -- an opening paren that would otherwise open the "body" before
204	* the real one does.
205	*
206	* Blanking rather than deleting keeps every remaining character at its
207	* original offset, so the paren scan reads the same structure the engine
208	* wrote.
209	*
210	* @param string $sql
211	* @return string Same length as the input.
212	*/
213	private static function blankQuotedRunsAndComments($sql) {
214	$sql = (string)$sql;
215	$length = strlen($sql);
216	$out = '';
217
218	for ($i = 0; $i < $length; $i++) {
219	$char = $sql[$i];
220	$end = null;
221
222	if ($char === '`' \|\| $char === "'" \|\| $char === '"') {
223	$end = self::endOfQuotedRun($sql, $i);
224
225	} else if ($char === '/' && $i + 1 < $length && $sql[$i + 1] === '*') {
226	// Block comment, including the /! ... / version-gated form an
227	// engine can emit in SHOW CREATE TABLE output.
228	$close = strpos($sql, '*/', $i + 2);
229	$end = ($close === false) ? $length - 1 : $close + 1;
230
231	} else if ($char === '#'
232	\|\| ($char === '-' && $i + 1 < $length && $sql[$i + 1] === '-'
233	&& ($i + 2 >= $length \|\| preg_match('/\s/', $sql[$i + 2]) === 1))) {
234	// A line comment ends AT the newline; the break itself still
235	// separates what follows. MySQL requires whitespace (or end of
236	// input) after the double dash, which is what keeps it apart
237	// from a subtraction.
238	$end = max($i, $i + strcspn($sql, "\r\n", $i) - 1);
239	}
240
241	if ($end === null) {
242	$out .= $char;
243	continue;
244	}
245
246	$out .= str_repeat(' ', $end - $i + 1);
247	$i = $end;
248	}
249
250	return $out;
251	}
252
253	/**
254	* The offset of the closing quote of the quoted run that STARTS at $start.
255	*
256	* A run that is never closed (DDL truncated mid-string) ends at the last
257	* character rather than being reported as an error: such a statement has no
258	* balanced body either, so the caller's answer is already "unreadable" and
259	* there is nothing after the run left to misread.
260	*
261	* @param string $sql
262	* @param int $start Offset of the opening quote.
263	* @return int
264	*/
265	private static function endOfQuotedRun($sql, $start) {
266	$quote = $sql[$start];
267	$length = strlen($sql);
268
269	for ($i = $start + 1; $i < $length; $i++) {
270	$char = $sql[$i];
271
272	// Backticks take no backslash escapes; a backslash inside one is an
273	// ordinary character.
274	if ($char === '\\' && $quote !== '`' && $i + 1 < $length) {
275	$i++;
276	continue;
277	}
278	if ($char === $quote) {
279	// A doubled quote is an escaped quote, not the end of the run.
280	if ($i + 1 < $length && $sql[$i + 1] === $quote) {
281	$i++;
282	continue;
283	}
284	return $i;
285	}
286	}
287
288	return $length - 1;
289	}
290	}
291