404-solution / includes / database / CreateTableColumnParser.php

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

430 lines 15.1 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) {
4	exit;
5	}
6
7	/**
8	* Parses the COLUMN declarations out of a CREATE TABLE statement -- either one
9	* of the plugin's own create*Table.sql templates or the text an engine hands
10	* back from SHOW CREATE TABLE -- into an ordered list of column definitions.
11	*
12	* The column-side counterpart of {@see ABJ_404_Solution_CreateTableIndexParser}:
13	* pure text in, structure out, no database connection and no knowledge of what
14	* any engine currently reports.
15	*
16	* Why this splits the statement instead of scanning it. A CREATE TABLE body is
17	* a comma-separated list in which only SOME entries are columns; the rest are
18	* table constraints and index declarations. A pattern that hunts for
19	* "<word> <word...>," anywhere in the text has no way to tell the two apart, so
20	* the tail of
21	*
22	* KEY `url` (`url`(190)) USING BTREE,
23	*
24	* reads as a column named `using` of type `btree`. The schema-diff path then
25	* finds no such column on the live table, asks for it to be created, and issues
26	* `ALTER TABLE ... ADD using btree` -- which fails, on every upgrade and every
27	* cron run, forever (report 286: www.ssr-nu.nl, plugin 4.3.3, MySQL 8.0.30).
28	*
29	* Splitting the body on top-level commas FIRST, and then rejecting an entry by
30	* the keyword it starts with, makes that whole class of misread impossible
31	* rather than teaching one pattern about one more keyword. Every non-column
32	* entry MySQL and MariaDB accept begins with one of a closed set of words, and
33	* a column name that collides with one of them has to be quoted to be legal --
34	* so the leading-keyword test is exact, not a heuristic.
35	*
36	* The parser is deliberately tolerant of input the production caller already
37	* cleans up (SQL comments, mixed case, absent backticks): correctness must not
38	* depend on a caller remembering to pre-process.
39	*/
40	class ABJ_404_Solution_CreateTableColumnParser {
41
42	/**
43	* The leading words that make a CREATE TABLE body entry something other
44	* than a column. Recognised unquoted only: a column legitimately named
45	* `key` or `check` has to be backtick-quoted for the engine to accept it,
46	* and a quoted entry is always a column.
47	*
48	* `period` covers MariaDB's `PERIOD FOR SYSTEM_TIME (...)`. `using` and
49	* `btree`/`hash` cannot begin a legal entry at all; they are listed so a
50	* body that somehow reaches this point mid-index-clause still refuses
51	* rather than inventing a column.
52	*
53	* @var array<int, string>
54	*/
55	private const NON_COLUMN_LEADING_WORDS = array(
56	'primary', 'unique', 'key', 'index', 'fulltext', 'spatial',
57	'constraint', 'foreign', 'check', 'period', 'using', 'btree', 'hash',
58	);
59
60	/**
61	* Extract the column definitions from a CREATE TABLE statement, in the
62	* order the statement declares them.
63	*
64	* @param string $createTableSql
65	* @return array<int, array{name: string, definition: string, type: string}>
66	* name: the column name with any quoting removed.
67	* definition: the whole entry as written, e.g. "`url` varchar(2048) not null".
68	* type: the entry with the leading name removed, e.g. "varchar(2048) not null".
69	*/
70	public static function fromCreateTableSql($createTableSql): array {
71	if (!is_string($createTableSql) \|\| $createTableSql === '') {
72	return array();
73	}
74
75	$columns = array();
76	$body = self::tableBody(self::stripComments($createTableSql));
77	foreach (self::splitTopLevel($body) as $entry) {
78	$definition = self::collapseWhitespace($entry);
79	if ($definition === '' \|\| self::isNonColumnEntry($definition)) {
80	continue;
81	}
82
83	$name = self::leadingIdentifier($definition);
84	if ($name === '') {
85	continue;
86	}
87
88	// A bare identifier with nothing after it is not a column
89	// definition; every column carries at least a type. This matches
90	// what the schema-diff path can actually act on -- it builds
91	// `ALTER TABLE ... ADD <definition>` out of the remainder.
92	$type = trim(substr($definition, strlen(self::leadingIdentifierText($definition))));
93	if ($type === '') {
94	continue;
95	}
96
97	$columns[] = array(
98	'name' => $name,
99	'definition' => $definition,
100	'type' => $type,
101	);
102	}
103
104	return $columns;
105	}
106
107	/**
108	* Just the column names a CREATE TABLE statement declares, in order.
109	*
110	* Lower-cased, because every caller compares them against names read out
111	* of another engine's DDL and identifier case is not significant to any
112	* comparison the plugin makes.
113	*
114	* @param string $createTableSql
115	* @return array<int, string>
116	*/
117	public static function columnNames($createTableSql): array {
118	$names = array();
119	foreach (self::fromCreateTableSql($createTableSql) as $column) {
120	$names[] = strtolower($column['name']);
121	}
122	return $names;
123	}
124
125	/**
126	* Remove every SQL comment from a statement, leaving comment-like text
127	* that happens to sit inside a quoted string exactly where it is.
128	*
129	* Comments have to go before anything else looks at the statement, because
130	* prose is not SQL and reads as whatever the reader expects. The header of
131	* createLookupTable.sql ends "...44 characters (Armed Forces Europe, Middle
132	* East, & Canada)." -- an opening paren followed by a comma-separated list,
133	* which is indistinguishable from a table body until the comment is gone.
134	*
135	* The caller strips comments too, for its own reasons (it compares column
136	* DDL text, and a COMMENT clause is noise in that comparison). This is not
137	* that: a parser that only works on pre-cleaned input is a parser whose
138	* correctness depends on every caller remembering.
139	*
140	* @param string $sql
141	* @return string
142	*/
143	private static function stripComments($sql) {
144	$sql = (string)$sql;
145	$length = strlen($sql);
146	$out = '';
147
148	for ($i = 0; $i < $length; $i++) {
149	$char = $sql[$i];
150
151	if (self::opensQuotedRun($char)) {
152	$end = self::endOfQuotedRun($sql, $i);
153	$out .= substr($sql, $i, $end - $i + 1);
154	$i = $end;
155	continue;
156	}
157
158	$commentEnd = self::endOfCommentRun($sql, $i);
159	if ($commentEnd !== null) {
160	// One space, not nothing: a comment sitting between two tokens
161	// is a separator, and closing the gap would join them.
162	$out .= ' ';
163	$i = $commentEnd;
164	continue;
165	}
166
167	$out .= $char;
168	}
169
170	return $out;
171	}
172
173	/**
174	* Whether this character opens a quoted run.
175	*
176	* @param string $char
177	* @return bool
178	*/
179	private static function opensQuotedRun($char) {
180	return $char === '`' \|\| $char === "'" \|\| $char === '"';
181	}
182
183	/**
184	* The index of the closing quote of the quoted run that STARTS at $start.
185	*
186	* A run that is never closed (DDL truncated mid-string) ends at the last
187	* character. That prevents any apparent closing parenthesis inside the
188	* broken string from balancing the table body; tableBody() then returns
189	* the fail-closed empty result the schema-diff caller recognizes.
190	*
191	* The single definition of what quoting means here. It used to be inlined
192	* in each of the three scanners below, which is three chances for the
193	* backslash and doubled-quote rules to drift apart.
194	*
195	* @param string $sql
196	* @param int $start Index of the opening quote.
197	* @return int
198	*/
199	private static function endOfQuotedRun($sql, $start) {
200	$quote = $sql[$start];
201	$length = strlen($sql);
202
203	for ($i = $start + 1; $i < $length; $i++) {
204	$char = $sql[$i];
205
206	// Backticks take no backslash escapes; a backslash inside one is
207	// an ordinary character.
208	if ($char === '\\' && $quote !== '`' && $i + 1 < $length) {
209	$i++;
210	continue;
211	}
212	if ($char === $quote) {
213	// A doubled quote is an escaped quote, not the end of the run.
214	if ($i + 1 < $length && $sql[$i + 1] === $quote) {
215	$i++;
216	continue;
217	}
218	return $i;
219	}
220	}
221
222	return $length - 1;
223	}
224
225	/**
226	* The index of the last character of the comment that STARTS at $start, or
227	* null when no comment starts there.
228	*
229	* @param string $sql
230	* @param int $start
231	* @return int\|null
232	*/
233	private static function endOfCommentRun($sql, $start) {
234	$length = strlen($sql);
235	$char = $sql[$start];
236
237	// Block comment, including the /! ... / version-gated form an engine
238	// can emit in SHOW CREATE TABLE output.
239	if ($char === '/' && $start + 1 < $length && $sql[$start + 1] === '*') {
240	$end = strpos($sql, '*/', $start + 2);
241	return ($end === false) ? $length - 1 : $end + 1;
242	}
243
244	// Line comment. MySQL requires whitespace (or end of input) after the
245	// double dash, which is what keeps it apart from a subtraction.
246	$isDoubleDash = ($char === '-' && $start + 1 < $length && $sql[$start + 1] === '-'
247	&& ($start + 2 >= $length \|\| preg_match('/\s/', $sql[$start + 2]) === 1));
248	if ($isDoubleDash \|\| $char === '#') {
249	// Stop ON the newline, not past it: the line break is not part of
250	// the comment and still separates what follows.
251	return $start + strcspn($sql, "\r\n", $start) - 1;
252	}
253
254	return null;
255	}
256
257	/**
258	* The parenthesised body of the CREATE TABLE statement -- the part between
259	* the opening paren that follows the table name and its matching close.
260	*
261	* A statement with no balanced close (truncated DDL, a partial read) yields
262	* nothing. There is no reliable way to prove that all column declarations
263	* arrived before the truncation point, and the schema-diff caller treats an
264	* empty list as the fail-closed "unparseable, do not touch this table"
265	* signal.
266	*
267	* @param string $createTableSql
268	* @return string
269	*/
270	private static function tableBody($createTableSql) {
271	$sql = (string)$createTableSql;
272	$length = strlen($sql);
273	$depth = 0;
274	$bodyStart = -1;
275
276	for ($i = 0; $i < $length; $i++) {
277	$char = $sql[$i];
278
279	if (self::opensQuotedRun($char)) {
280	// A paren inside a quoted identifier or string is content.
281	$i = self::endOfQuotedRun($sql, $i);
282	continue;
283	}
284
285	if ($char === '(') {
286	if ($bodyStart === -1) {
287	$bodyStart = $i + 1;
288	}
289	$depth++;
290	continue;
291	}
292
293	if ($char === ')' && $bodyStart !== -1) {
294	$depth--;
295	if ($depth === 0) {
296	return substr($sql, $bodyStart, $i - $bodyStart);
297	}
298	}
299	}
300
301	return '';
302	}
303
304	/**
305	* Split a CREATE TABLE body on the commas that separate its entries,
306	* leaving alone the commas inside parentheses (`decimal(5,2)`, an index's
307	* column list) and inside quoted text (a COMMENT string).
308	*
309	* @param string $body
310	* @return array<int, string>
311	*/
312	private static function splitTopLevel($body) {
313	$sql = (string)$body;
314	$length = strlen($sql);
315	$entries = array();
316	$current = '';
317	$depth = 0;
318
319	for ($i = 0; $i < $length; $i++) {
320	$char = $sql[$i];
321
322	if (self::opensQuotedRun($char)) {
323	// A comma or paren inside a COMMENT string is content, not a
324	// separator.
325	$end = self::endOfQuotedRun($sql, $i);
326	$current .= substr($sql, $i, $end - $i + 1);
327	$i = $end;
328	continue;
329	}
330
331	if ($char === '(') {
332	$depth++;
333	$current .= $char;
334	continue;
335	}
336
337	if ($char === ')') {
338	if ($depth > 0) {
339	$depth--;
340	}
341	$current .= $char;
342	continue;
343	}
344
345	if ($char === ',' && $depth === 0) {
346	$entries[] = $current;
347	$current = '';
348	continue;
349	}
350
351	$current .= $char;
352	}
353
354	$entries[] = $current;
355
356	return $entries;
357	}
358
359	/**
360	* Whether a body entry declares something other than a column.
361	*
362	* A quoted leading identifier is always a column: the engine requires the
363	* quoting precisely because the bare word would have been read as a
364	* keyword.
365	*
366	* @param string $definition
367	* @return bool
368	*/
369	private static function isNonColumnEntry($definition) {
370	$matches = array();
371	if (!preg_match('/^([A-Za-z_][A-Za-z0-9_$]*)/', (string)$definition, $matches)) {
372	return false;
373	}
374	return in_array(strtolower($matches[1]), self::NON_COLUMN_LEADING_WORDS, true);
375	}
376
377	/**
378	* The column name an entry starts with, unquoted, or '' when the entry does
379	* not start with an identifier at all.
380	*
381	* @param string $definition
382	* @return string
383	*/
384	private static function leadingIdentifier($definition) {
385	$matches = array();
386	if (preg_match('/^`((?:[^`]\|``)*)`/', (string)$definition, $matches)) {
387	return str_replace('``', '`', $matches[1]);
388	}
389	if (preg_match('/^([A-Za-z_][A-Za-z0-9_$]*)/', (string)$definition, $matches)) {
390	return $matches[1];
391	}
392	return '';
393	}
394
395	/**
396	* The leading identifier exactly as the entry spells it, quoting included,
397	* so the remainder of the entry can be taken by offset.
398	*
399	* @param string $definition
400	* @return string
401	*/
402	private static function leadingIdentifierText($definition) {
403	$matches = array();
404	if (preg_match('/^`(?:[^`]\|``)*`/', (string)$definition, $matches)) {
405	return $matches[0];
406	}
407	if (preg_match('/^[A-Za-z_][A-Za-z0-9_$]*/', (string)$definition, $matches)) {
408	return $matches[0];
409	}
410	return '';
411	}
412
413	/**
414	* One entry on one line: leading and trailing space removed, and every
415	* internal run of whitespace reduced to a single space.
416	*
417	* The two sides of a schema comparison are written by different authors --
418	* a .sql file the plugin ships and whatever SHOW CREATE TABLE emits -- and
419	* they indent and wrap differently. Normalising here means the comparison
420	* never sees a difference that is only layout.
421	*
422	* @param string $entry
423	* @return string
424	*/
425	private static function collapseWhitespace($entry) {
426	$collapsed = preg_replace('/\s+/', ' ', (string)$entry);
427	return trim($collapsed === null ? (string)$entry : $collapsed);
428	}
429	}
430