sqlite-database-integration / wp-includes / database / sqlite / class-wp-sqlite-information-schema-builder.php

class-wp-sqlite-information-schema-builder.php in SQLite Database Integration 3.0.1, at wp-includes/database/sqlite/class-wp-sqlite-information-schema-builder.php

3,234 lines 117.8 KB

Raw Download Zip

1	<?php
2
3	/**
4	* SQLite information schema builder for MySQL.
5	*
6	* This class builds and maintains MySQL INFORMATION_SCHEMA tables in SQLite.
7	* It consumes the AST of MySQL DDL queries and records the schema information
8	* in SQLite tables that emulate the MySQL INFORMATION_SCHEMA.
9	*
10	* @access private
11	*/
12	class WP_SQLite_Information_Schema_Builder {
13	/**
14	* The name of the database that is saved in the information schema tables.
15	*
16	* The SQLite driver injects the configured database name dynamically,
17	* but we need to store some value in the information schema tables.
18	* This database name will also be visible in SQLite admin tools.
19	*
20	* @var string
21	*/
22	const SAVED_DATABASE_NAME = 'sqlite_database';
23
24	/**
25	* SQL definitions for tables that emulate MySQL "information_schema".
26	*
27	* The full MySQL information schema comprises a large number of tables:
28	* https://dev.mysql.com/doc/refman/8.4/en/information-schema-table-reference.html
29	*
30	* We only implement a limited subset that is necessary for a database schema
31	* introspection and representation, currently covering the following tables:
32	*
33	* - SCHEMATA
34	* - TABLES
35	* - COLUMNS
36	* - STATISTICS (indexes)
37	* - TABLE_CONSTRAINTS
38	* - CHECK_CONSTRAINTS
39	*
40	* TODO (not yet implemented):
41	* - VIEWS
42	* - TRIGGERS
43	*/
44	const INFORMATION_SCHEMA_TABLE_DEFINITIONS = array(
45	// INFORMATION_SCHEMA.SCHEMATA
46	'schemata' => "
47	CATALOG_NAME TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
48	SCHEMA_NAME TEXT NOT NULL COLLATE NOCASE, -- database name
49	DEFAULT_CHARACTER_SET_NAME TEXT NOT NULL COLLATE NOCASE, -- default character set
50	DEFAULT_COLLATION_NAME TEXT NOT NULL COLLATE NOCASE, -- default collation
51	SQL_PATH TEXT NULL COLLATE NOCASE, -- always NULL
52	DEFAULT_ENCRYPTION TEXT NOT NULL DEFAULT 'NO' COLLATE NOCASE, -- not implemented
53	PRIMARY KEY (SCHEMA_NAME)
54	",
55
56	// INFORMATION_SCHEMA.TABLES
57	'tables' => "
58	TABLE_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
59	TABLE_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- database name
60	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
61	TABLE_TYPE TEXT NOT NULL COLLATE BINARY, -- 'BASE TABLE', 'VIEW', or 'SYSTEM VIEW'
62	ENGINE TEXT NOT NULL COLLATE NOCASE, -- storage engine
63	VERSION INTEGER NOT NULL DEFAULT 10, -- unused, in MySQL 8 hardcoded to 10
64	ROW_FORMAT TEXT NOT NULL COLLATE BINARY, -- row storage format @TODO - implement
65	TABLE_ROWS INTEGER NOT NULL DEFAULT 0, -- not implemented
66	AVG_ROW_LENGTH INTEGER NOT NULL DEFAULT 0, -- not implemented
67	DATA_LENGTH INTEGER NOT NULL DEFAULT 0, -- not implemented
68	MAX_DATA_LENGTH INTEGER NOT NULL DEFAULT 0, -- not implemented
69	INDEX_LENGTH INTEGER NOT NULL DEFAULT 0, -- not implemented
70	DATA_FREE INTEGER NOT NULL DEFAULT 0, -- not implemented
71	AUTO_INCREMENT INTEGER, -- not implemented
72	CREATE_TIME TEXT NOT NULL -- table creation timestamp
73	DEFAULT CURRENT_TIMESTAMP,
74	UPDATE_TIME TEXT, -- table update time
75	CHECK_TIME TEXT, -- not implemented
76	TABLE_COLLATION TEXT NOT NULL COLLATE NOCASE, -- table collation
77	CHECKSUM INTEGER, -- not implemented
78	CREATE_OPTIONS TEXT NOT NULL DEFAULT '' COLLATE NOCASE, -- extra CREATE TABLE options
79	TABLE_COMMENT TEXT NOT NULL DEFAULT '' COLLATE NOCASE, -- comment
80	PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME)
81	",
82
83	// INFORMATION_SCHEMA.COLUMNS
84	'columns' => "
85	TABLE_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
86	TABLE_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- database name
87	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
88	COLUMN_NAME TEXT NOT NULL COLLATE NOCASE, -- column name
89	ORDINAL_POSITION INTEGER NOT NULL, -- column position
90	COLUMN_DEFAULT TEXT COLLATE BINARY, -- default value, NULL for both NULL and none
91	IS_NULLABLE TEXT NOT NULL COLLATE NOCASE, -- 'YES' or 'NO'
92	DATA_TYPE TEXT NOT NULL COLLATE BINARY, -- data type (without length, precision, etc.)
93	CHARACTER_MAXIMUM_LENGTH INTEGER, -- max length for string columns in characters
94	CHARACTER_OCTET_LENGTH INTEGER, -- max length for string columns in bytes
95	NUMERIC_PRECISION INTEGER, -- number precision for numeric columns
96	NUMERIC_SCALE INTEGER, -- number scale for numeric columns
97	DATETIME_PRECISION INTEGER, -- fractional seconds precision for temporal columns
98	CHARACTER_SET_NAME TEXT COLLATE NOCASE, -- charset for string columns
99	COLLATION_NAME TEXT COLLATE NOCASE, -- collation for string columns
100	COLUMN_TYPE TEXT NOT NULL COLLATE BINARY, -- full data type (with length, precision, etc.)
101	COLUMN_KEY TEXT NOT NULL DEFAULT '' COLLATE BINARY, -- if column is indexed ('', 'PRI', 'UNI', 'MUL')
102	EXTRA TEXT NOT NULL DEFAULT '' COLLATE NOCASE, -- AUTO_INCREMENT, VIRTUAL, STORED, etc.
103	PRIVILEGES TEXT NOT NULL COLLATE NOCASE, -- not implemented
104	COLUMN_COMMENT TEXT NOT NULL DEFAULT '' COLLATE BINARY, -- comment
105	GENERATION_EXPRESSION TEXT NOT NULL DEFAULT '' COLLATE BINARY, -- expression for generated columns
106	SRS_ID INTEGER, -- not implemented
107	PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
108	",
109
110	// INFORMATION_SCHEMA.STATISTICS (indexes)
111	'statistics' => "
112	TABLE_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
113	TABLE_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- database name
114	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
115	NON_UNIQUE INTEGER NOT NULL, -- 0 for unique indexes, 1 otherwise
116	INDEX_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- index database name
117	INDEX_NAME TEXT NOT NULL COLLATE NOCASE, -- index name, for PKs always 'PRIMARY'
118	SEQ_IN_INDEX INTEGER NOT NULL, -- column position in index (from 1)
119	COLUMN_NAME TEXT COLLATE NOCASE, -- column name (NULL for functional indexes)
120	COLLATION TEXT COLLATE NOCASE, -- column sort in the index ('A', 'D', or NULL)
121	CARDINALITY INTEGER, -- not implemented
122	SUB_PART INTEGER, -- number of indexed chars, NULL for full column
123	PACKED TEXT, -- not implemented
124	NULLABLE TEXT NOT NULL COLLATE NOCASE, -- 'YES' if column can contain NULL, '' otherwise
125	INDEX_TYPE TEXT NOT NULL COLLATE BINARY, -- 'BTREE', 'FULLTEXT', 'SPATIAL'
126	COMMENT TEXT NOT NULL DEFAULT '' COLLATE NOCASE, -- not implemented
127	INDEX_COMMENT TEXT NOT NULL DEFAULT '' COLLATE BINARY, -- index comment
128	IS_VISIBLE TEXT NOT NULL DEFAULT 'YES' COLLATE NOCASE, -- 'NO' if column is hidden, 'YES' otherwise
129	EXPRESSION TEXT COLLATE BINARY, -- expression for functional indexes
130	PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX),
131	UNIQUE (INDEX_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX)
132	",
133
134	// INFORMATION_SCHEMA.TABLE_CONSTRAINTS
135	'table_constraints' => "
136	CONSTRAINT_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
137	CONSTRAINT_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- constraint database name
138	CONSTRAINT_NAME TEXT NOT NULL COLLATE NOCASE, -- constraint name
139	TABLE_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- table database name
140	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
141	CONSTRAINT_TYPE TEXT NOT NULL COLLATE BINARY, -- constraint type ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY', 'CHECK')
142	ENFORCED TEXT NOT NULL DEFAULT 'YES' COLLATE BINARY, -- 'YES' if constraint is enforced, 'NO' otherwise
143
144	-- Constraint names are unique per type in each table.
145	-- A MySQL table can have a PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK
146	-- constraints with the same name, but the name must be unique per type.
147	-- CHECK and FOREIGN KEY constraint names must also be unique per schema.
148	PRIMARY KEY (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME),
149	UNIQUE (CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME)
150	",
151
152	// INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
153	'referential_constraints' => "
154	CONSTRAINT_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
155	CONSTRAINT_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- constraint database name
156	CONSTRAINT_NAME TEXT NOT NULL COLLATE NOCASE, -- constraint name
157	UNIQUE_CONSTRAINT_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
158	UNIQUE_CONSTRAINT_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- referenced unique constraint database name
159	UNIQUE_CONSTRAINT_NAME TEXT COLLATE NOCASE, -- referenced unique constraint name or NULL
160	MATCH_OPTION TEXT NOT NULL COLLATE NOCASE DEFAULT 'NONE', -- always 'NONE'
161	UPDATE_RULE TEXT NOT NULL COLLATE NOCASE, -- 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'
162	DELETE_RULE TEXT NOT NULL COLLATE NOCASE, -- 'CASCADE', 'SET NULL', 'SET DEFAULT', 'RESTRICT', 'NO ACTION'
163	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
164	REFERENCED_TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- referenced table name
165	PRIMARY KEY (CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
166	",
167
168	// INFORMATION_SCHEMA.KEY_COLUMN_USAGE
169	'key_column_usage' => "
170	CONSTRAINT_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
171	CONSTRAINT_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- constraint database name
172	CONSTRAINT_NAME TEXT NOT NULL COLLATE NOCASE, -- constraint name
173	TABLE_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
174	TABLE_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- table database name
175	TABLE_NAME TEXT NOT NULL COLLATE NOCASE, -- table name
176	COLUMN_NAME TEXT NOT NULL COLLATE NOCASE, -- column name
177	ORDINAL_POSITION INTEGER NOT NULL, -- column position
178	POSITION_IN_UNIQUE_CONSTRAINT INTEGER, -- column position in referenced unique constraint
179	REFERENCED_TABLE_SCHEMA TEXT COLLATE NOCASE, -- referenced table database name
180	REFERENCED_TABLE_NAME TEXT COLLATE NOCASE, -- referenced table name
181	REFERENCED_COLUMN_NAME TEXT COLLATE NOCASE, -- referenced column name
182	UNIQUE (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA)
183	",
184
185	// INFORMATION_SCHEMA.CHECK_CONSTRAINTS
186	'check_constraints' => "
187	CONSTRAINT_CATALOG TEXT NOT NULL DEFAULT 'def' COLLATE NOCASE, -- always 'def'
188	CONSTRAINT_SCHEMA TEXT NOT NULL COLLATE NOCASE, -- constraint database name
189	CONSTRAINT_NAME TEXT NOT NULL COLLATE NOCASE, -- constraint name
190	CHECK_CLAUSE TEXT NOT NULL COLLATE BINARY, -- check clause
191	PRIMARY KEY (CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
192	",
193	);
194
195	/**
196	* A mapping of MySQL tokens to normalized MySQL data types.
197	* This is used to store column data types in the information schema.
198	*/
199	const TOKEN_TO_TYPE_MAP = array(
200	WP_MySQL_Lexer::INT_SYMBOL => 'int',
201	WP_MySQL_Lexer::TINYINT_SYMBOL => 'tinyint',
202	WP_MySQL_Lexer::SMALLINT_SYMBOL => 'smallint',
203	WP_MySQL_Lexer::MEDIUMINT_SYMBOL => 'mediumint',
204	WP_MySQL_Lexer::BIGINT_SYMBOL => 'bigint',
205	WP_MySQL_Lexer::REAL_SYMBOL => 'double',
206	WP_MySQL_Lexer::DOUBLE_SYMBOL => 'double',
207	WP_MySQL_Lexer::FLOAT_SYMBOL => 'float',
208	WP_MySQL_Lexer::DECIMAL_SYMBOL => 'decimal',
209	WP_MySQL_Lexer::NUMERIC_SYMBOL => 'decimal',
210	WP_MySQL_Lexer::FIXED_SYMBOL => 'decimal',
211	WP_MySQL_Lexer::BIT_SYMBOL => 'bit',
212	WP_MySQL_Lexer::BOOL_SYMBOL => 'tinyint',
213	WP_MySQL_Lexer::BOOLEAN_SYMBOL => 'tinyint',
214	WP_MySQL_Lexer::BINARY_SYMBOL => 'binary',
215	WP_MySQL_Lexer::VARBINARY_SYMBOL => 'varbinary',
216	WP_MySQL_Lexer::YEAR_SYMBOL => 'year',
217	WP_MySQL_Lexer::DATE_SYMBOL => 'date',
218	WP_MySQL_Lexer::TIME_SYMBOL => 'time',
219	WP_MySQL_Lexer::TIMESTAMP_SYMBOL => 'timestamp',
220	WP_MySQL_Lexer::DATETIME_SYMBOL => 'datetime',
221	WP_MySQL_Lexer::TINYBLOB_SYMBOL => 'tinyblob',
222	WP_MySQL_Lexer::BLOB_SYMBOL => 'blob',
223	WP_MySQL_Lexer::MEDIUMBLOB_SYMBOL => 'mediumblob',
224	WP_MySQL_Lexer::LONGBLOB_SYMBOL => 'longblob',
225	WP_MySQL_Lexer::TINYTEXT_SYMBOL => 'tinytext',
226	WP_MySQL_Lexer::TEXT_SYMBOL => 'text',
227	WP_MySQL_Lexer::MEDIUMTEXT_SYMBOL => 'mediumtext',
228	WP_MySQL_Lexer::LONGTEXT_SYMBOL => 'longtext',
229	WP_MySQL_Lexer::ENUM_SYMBOL => 'enum',
230	WP_MySQL_Lexer::SET_SYMBOL => 'set',
231	WP_MySQL_Lexer::SERIAL_SYMBOL => 'bigint',
232	WP_MySQL_Lexer::GEOMETRY_SYMBOL => 'geometry',
233	WP_MySQL_Lexer::GEOMETRYCOLLECTION_SYMBOL => 'geomcollection',
234	WP_MySQL_Lexer::POINT_SYMBOL => 'point',
235	WP_MySQL_Lexer::MULTIPOINT_SYMBOL => 'multipoint',
236	WP_MySQL_Lexer::LINESTRING_SYMBOL => 'linestring',
237	WP_MySQL_Lexer::MULTILINESTRING_SYMBOL => 'multilinestring',
238	WP_MySQL_Lexer::POLYGON_SYMBOL => 'polygon',
239	WP_MySQL_Lexer::MULTIPOLYGON_SYMBOL => 'multipolygon',
240	WP_MySQL_Lexer::JSON_SYMBOL => 'json',
241	);
242
243	/**
244	* The default collation for each MySQL charset.
245	* This is needed as collation is not always specified in a query.
246	*/
247	const CHARSET_DEFAULT_COLLATION_MAP = array(
248	'armscii8' => 'armscii8_general_ci',
249	'ascii' => 'ascii_general_ci',
250	'big5' => 'big5_chinese_ci',
251	'binary' => 'binary',
252	'cp1250' => 'cp1250_general_ci',
253	'cp1251' => 'cp1251_general_ci',
254	'cp1256' => 'cp1256_general_ci',
255	'cp1257' => 'cp1257_general_ci',
256	'cp850' => 'cp850_general_ci',
257	'cp852' => 'cp852_general_ci',
258	'cp866' => 'cp866_general_ci',
259	'cp932' => 'cp932_japanese_ci',
260	'dec8' => 'dec8_swedish_ci',
261	'eucjpms' => 'eucjpms_japanese_ci',
262	'euckr' => 'euckr_korean_ci',
263	'gb18030' => 'gb18030_chinese_ci',
264	'gb2312' => 'gb2312_chinese_ci',
265	'gbk' => 'gbk_chinese_ci',
266	'geostd8' => 'geostd8_general_ci',
267	'greek' => 'greek_general_ci',
268	'hebrew' => 'hebrew_general_ci',
269	'hp8' => 'hp8_english_ci',
270	'keybcs2' => 'keybcs2_general_ci',
271	'koi8r' => 'koi8r_general_ci',
272	'koi8u' => 'koi8u_general_ci',
273	'latin1' => 'latin1_swedish_ci',
274	'latin2' => 'latin2_general_ci',
275	'latin5' => 'latin5_turkish_ci',
276	'latin7' => 'latin7_general_ci',
277	'macce' => 'macce_general_ci',
278	'macroman' => 'macroman_general_ci',
279	'sjis' => 'sjis_japanese_ci',
280	'swe7' => 'swe7_swedish_ci',
281	'tis620' => 'tis620_thai_ci',
282	'ucs2' => 'ucs2_general_ci',
283	'ujis' => 'ujis_japanese_ci',
284	'utf16' => 'utf16_general_ci',
285	'utf16le' => 'utf16le_general_ci',
286	'utf32' => 'utf32_general_ci',
287	'utf8' => 'utf8_general_ci',
288	'utf8mb4' => 'utf8mb4_0900_ai_ci', // @TODO: This should probably be version-dependent.
289	// Before MySQL 8, the default was different.
290	);
291
292	/**
293	* Maximum number of bytes per character for each charset.
294	* The map contains only multi-byte charsets.
295	* Charsets that are not included are single-byte.
296	*/
297	const CHARSET_MAX_BYTES_MAP = array(
298	'big5' => 2,
299	'cp932' => 2,
300	'eucjpms' => 3,
301	'euckr' => 2,
302	'gb18030' => 4,
303	'gb2312' => 2,
304	'gbk' => 2,
305	'sjis' => 2,
306	'ucs2' => 2,
307	'ujis' => 3,
308	'utf16' => 4,
309	'utf16le' => 4,
310	'utf32' => 4,
311	'utf8' => 3,
312	'utf8mb4' => 4,
313	);
314
315	/**
316	* A prefix for information schema table names.
317	*
318	* @var string
319	*/
320	private $table_prefix;
321
322	/**
323	* A prefix for information schema table names for temporary tables.
324	*
325	* This is needed because for temporary tables, we store the information
326	* schema tables as temporary tables as well, and temporary tables with
327	* the same name as regular tables would override the regular tables.
328	*
329	* @var string
330	*/
331	private $temporary_table_prefix;
332
333	/**
334	* Whether the information schema for temporary tables was already created.
335	*
336	* This is used to avoid trying to create a temporary information schema
337	* for each CREATE TEMPORARY TABLE statement during a single session.
338	*
339	* @var bool
340	*/
341	private $temporary_information_schema_exists = false;
342
343	/**
344	* An instance of the SQLite connection.
345	*
346	* @var WP_SQLite_Connection
347	*/
348	private $connection;
349
350	/**
351	* Constructor.
352	*
353	* @param string $reserved_prefix An identifier prefix for internal database objects.
354	* @param WP_SQLite_Connection $connection An instance of the SQLite connection.
355	*/
356	public function __construct( string $reserved_prefix, WP_SQLite_Connection $connection ) {
357	$this->connection = $connection;
358	$this->table_prefix = $reserved_prefix . 'mysql_information_schema_';
359	$this->temporary_table_prefix = $reserved_prefix . 'mysql_information_schema_tmp_';
360	}
361
362	/**
363	* Get SQLite table name for the given MySQL information schema table name.
364	*
365	* @param bool $table_is_temporary Whether a temporary table information schema is requested.
366	* @param string $information_schema_table_name The MySQL information schema table name.
367	* @return string The SQLite table name.
368	*/
369	public function get_table_name( bool $table_is_temporary, string $information_schema_table_name ): string {
370	$prefix = $table_is_temporary ? $this->temporary_table_prefix : $this->table_prefix;
371	return $prefix . $information_schema_table_name;
372	}
373
374	/**
375	* Check if a temporary table exists in the SQLite database.
376	*
377	* @param string $table_name The temporary table name.
378	* @return bool True if the temporary table exists, false otherwise.
379	*/
380	public function temporary_table_exists( string $table_name ): bool {
381	/*
382	* We could search in the "{$this->temporary_table_prefix}tables" table,
383	* but it may not exist yet, so using "sqlite_temp_master" is simpler.
384	*/
385	$stmt = $this->connection->query(
386	"SELECT 1 FROM sqlite_temp_master WHERE type = 'table' AND name = ?",
387	array( $table_name )
388	);
389	return $stmt->fetchColumn() === '1';
390	}
391
392	/**
393	* Ensure that the information schema tables exist in the SQLite
394	* database. Tables that are missing will be created.
395	*/
396	public function ensure_information_schema_tables(): void {
397	$sqlite_version = $this->connection->get_pdo()->getAttribute( PDO::ATTR_SERVER_VERSION ); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
398	$supports_strict_tables = version_compare( $sqlite_version, '3.37.0', '>=' );
399	foreach ( self::INFORMATION_SCHEMA_TABLE_DEFINITIONS as $table_name => $table_body ) {
400	$this->connection->query(
401	sprintf(
402	'CREATE TABLE IF NOT EXISTS %s%s (%s)%s',
403	$this->table_prefix,
404	$table_name,
405	$table_body,
406	$supports_strict_tables ? ' STRICT' : ''
407	)
408	);
409	}
410	}
411
412	/**
413	* Get the definition and data of a computed information schema table.
414	*
415	* Some information schema tables can be computed on the fly when they are
416	* referenced in a query. This method provides their definitions and data.
417	*
418	* @param string $table_name The table name.
419	* @return string\|null The table definition and data, or null if
420	* the table is not a computed table.
421	*/
422	public function get_computed_information_schema_table_definition( string $table_name ): ?string {
423	switch ( strtolower( $table_name ) ) {
424	case 'character_sets':
425	return "SELECT
426	column1 AS CHARACTER_SET_NAME,
427	column2 AS DEFAULT_COLLATE_NAME,
428	column3 AS DESCRIPTION,
429	column4 AS MAXLEN
430	FROM (
431	VALUES
432	('binary', 'binary', 'Binary pseudo charset', 1),
433	('utf8', 'utf8_general_ci', 'UTF-8 Unicode', 3),
434	('utf8mb4', 'utf8mb4_0900_ai_ci', 'UTF-8 Unicode', 4)
435	)";
436	case 'collations':
437	return "SELECT
438	column1 AS COLLATION_NAME,
439	column2 AS CHARACTER_SET_NAME,
440	column3 AS ID,
441	column4 AS IS_DEFAULT,
442	column5 AS IS_COMPILED,
443	column6 AS SORTLEN,
444	column7 AS PAD_ATTRIBUTE
445	FROM (
446	VALUES
447	('binary', 'binary', 63, 'Yes', 'Yes', 1, 'NO PAD'),
448	('utf8_bin', 'utf8', 83, '', 'Yes', 1, 'PAD SPACE'),
449	('utf8_general_ci', 'utf8', 33, 'Yes', 'Yes', 1, 'PAD SPACE'),
450	('utf8_unicode_ci', 'utf8', 192, '', 'Yes', 8, 'PAD SPACE'),
451	('utf8mb4_bin', 'utf8mb4', 46, '', 'Yes', 1, 'PAD SPACE'),
452	('utf8mb4_unicode_ci', 'utf8mb4', 224, '', 'Yes', 8, 'PAD SPACE'),
453	('utf8mb4_0900_ai_ci', 'utf8mb4', 255, 'Yes', 'Yes', 0, 'NO PAD')
454	)";
455	default:
456	return null;
457	}
458	}
459
460	/**
461	* Ensure that the temporary information schema tables exist in
462	* the SQLite database. Tables that are missing will be created.
463	*/
464	public function ensure_temporary_information_schema_tables(): void {
465	$sqlite_version = $this->connection->get_pdo()->getAttribute( PDO::ATTR_SERVER_VERSION ); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
466	$supports_strict_tables = version_compare( $sqlite_version, '3.37.0', '>=' );
467	foreach ( self::INFORMATION_SCHEMA_TABLE_DEFINITIONS as $table_name => $table_body ) {
468	// Skip the "schemata" table; MySQL doesn't support temporary databases.
469	if ( 'schemata' === $table_name ) {
470	continue;
471	}
472
473	$this->connection->query(
474	sprintf(
475	'CREATE TEMPORARY TABLE IF NOT EXISTS %s%s (%s)%s',
476	$this->temporary_table_prefix,
477	$table_name,
478	$table_body,
479	$supports_strict_tables ? ' STRICT' : ''
480	)
481	);
482	}
483	$this->temporary_information_schema_exists = true;
484	}
485
486	/**
487	* Analyze CREATE TABLE statement and record data in the information schema.
488	*
489	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
490	*/
491	public function record_create_table( WP_Parser_Node $node ): void {
492	$table_name_node = $node->get_first_descendant_node( 'tableName' );
493	$table_name = $this->get_table_name_from_node( $table_name_node );
494	$table_engine = $this->get_table_engine( $node );
495	$table_row_format = 'MyISAM' === $table_engine ? 'Fixed' : 'Dynamic';
496	$table_collation = $this->get_table_collation( $node );
497	$table_comment = $this->get_table_comment( $node );
498
499	/*
500	* When creating a temporary table:
501	* 1. Track that we're processing a temporary table.
502	* 2. Ensure that the temporary information schema tables exist.
503	*/
504	$subnode = $node->get_first_child_node();
505	$table_is_temporary = $subnode->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
506	if ( $table_is_temporary && ! $this->temporary_information_schema_exists ) {
507	$this->ensure_temporary_information_schema_tables();
508	}
509
510	// 1. Table.
511	$tables_table_name = $this->get_table_name( $table_is_temporary, 'tables' );
512	$table_data = array(
513	'table_schema' => self::SAVED_DATABASE_NAME,
514	'table_name' => $table_name,
515	'table_type' => 'BASE TABLE',
516	'engine' => $table_engine,
517	'row_format' => $table_row_format,
518	'table_collation' => $table_collation,
519	'table_comment' => $table_comment,
520	);
521
522	try {
523	$this->insert_values( $tables_table_name, $table_data );
524	} catch ( PDOException $e ) {
525	/*
526	* Even though we keep track of whether the temporary information
527	* schema tables already exist, there is a special case in which
528	* the tracked information may be incorrect.
529	*
530	* This can happen when the query is in a transaction that is later
531	* rolled back. In that case, let's ensure the schema, and try again.
532	*/
533	if ( $table_is_temporary && str_contains( $e->getMessage(), 'no such table' ) ) {
534	$this->ensure_temporary_information_schema_tables();
535	try {
536	$e = null;
537	$this->insert_values( $tables_table_name, $table_data );
538	} catch ( PDOException $retry_exception ) {
539	$e = $retry_exception;
540	}
541	}
542
543	if ( $e ) {
544	if ( '23000' === $e->getCode() ) {
545	throw WP_SQLite_Information_Schema_Exception::duplicate_table_name( $table_name );
546	} else {
547	throw $e;
548	}
549	}
550	}
551
552	// 2. Columns.
553	$column_position = 1;
554	foreach ( $node->get_descendant_nodes( 'columnDefinition' ) as $column_node ) {
555	$column_name = $this->get_value( $column_node->get_first_child_node( 'fieldIdentifier' ) );
556
557	// Column definition.
558	$column_data = $this->extract_column_data(
559	$table_name,
560	$column_name,
561	$column_node,
562	$column_position
563	);
564
565	try {
566	$this->insert_values(
567	$this->get_table_name( $table_is_temporary, 'columns' ),
568	$column_data
569	);
570	} catch ( PDOException $e ) {
571	if ( '23000' === $e->getCode() ) {
572	throw WP_SQLite_Information_Schema_Exception::duplicate_column_name( $column_name );
573	}
574	throw $e;
575	}
576
577	// Extract inline column constraints and indexes.
578	$index_data = $this->extract_column_statistics_data(
579	$table_name,
580	$column_name,
581	$column_node,
582	'YES' === $column_data['is_nullable']
583	);
584	$constraint_data = $this->extract_table_constraint_data(
585	$column_node,
586	$table_name,
587	$index_data['index_name'] ?? null
588	);
589	$referential_constraint_data = $this->extract_referential_constraint_data(
590	$column_node,
591	$table_name
592	);
593	$key_column_usage_data = $this->extract_key_column_usage_data(
594	$column_node,
595	$table_name,
596	$index_data['index_name'] ?? null
597	);
598	$check_constraint_data = $this->extract_check_constraint_data(
599	$column_node,
600	$table_name
601	);
602
603	// Save inline column constraints and indexes.
604	if ( null !== $index_data ) {
605	$this->insert_values(
606	$this->get_table_name( $table_is_temporary, 'statistics' ),
607	$index_data
608	);
609	}
610	if ( null !== $constraint_data ) {
611	$this->insert_values(
612	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
613	$constraint_data
614	);
615	}
616	if ( null !== $referential_constraint_data ) {
617	$this->insert_values(
618	$this->get_table_name( $table_is_temporary, 'referential_constraints' ),
619	$referential_constraint_data
620	);
621	}
622	foreach ( $key_column_usage_data as $key_column_usage_item ) {
623	$this->insert_values(
624	$this->get_table_name( $table_is_temporary, 'key_column_usage' ),
625	$key_column_usage_item
626	);
627	}
628	if ( null !== $check_constraint_data ) {
629	$this->insert_values(
630	$this->get_table_name( $table_is_temporary, 'check_constraints' ),
631	$check_constraint_data
632	);
633	}
634
635	$column_position += 1;
636	}
637
638	// 3. Constraints and indexes.
639	foreach ( $node->get_descendant_nodes( 'tableConstraintDef' ) as $constraint_node ) {
640	$this->record_add_constraint_or_index( $table_is_temporary, $table_name, $constraint_node );
641	}
642	}
643
644	/**
645	* Analyze ALTER TABLE statement and record data in the information schema.
646	*
647	* @param WP_Parser_Node $node The "alterStatement" AST node with "alterTable" child.
648	*/
649	public function record_alter_table( WP_Parser_Node $node ): void {
650	$table_ref = $node->get_first_descendant_node( 'tableRef' );
651	$table_name = $this->get_table_name_from_node( $table_ref );
652	$actions = $node->get_descendant_nodes( 'alterListItem' );
653
654	// Check if a temporary table with the given name exists.
655	$table_is_temporary = $this->temporary_table_exists( $table_name );
656
657	foreach ( $actions as $action ) {
658	$first_token = $action->get_first_child_token();
659
660	// ADD
661	if ( WP_MySQL_Lexer::ADD_SYMBOL === $first_token->id ) {
662	// ADD [COLUMN] (...[, ...])
663	$column_definitions = $action->get_descendant_nodes( 'columnDefinition' );
664	if ( count( $column_definitions ) > 0 ) {
665	foreach ( $column_definitions as $column_definition ) {
666	$name = $this->get_value( $column_definition->get_first_child_node( 'identifier' ) );
667	$this->record_add_column( $table_is_temporary, $table_name, $name, $column_definition );
668	}
669	continue;
670	}
671
672	// ADD [COLUMN] ...
673	$field_definition = $action->get_first_descendant_node( 'fieldDefinition' );
674	if ( null !== $field_definition ) {
675	$name = $this->get_value( $action->get_first_child_node( 'identifier' ) );
676	$this->record_add_column( $table_is_temporary, $table_name, $name, $field_definition );
677	// @TODO: Handle FIRST/AFTER.
678	continue;
679	}
680
681	// ADD constraint or index.
682	$constraint = $action->get_first_descendant_node( 'tableConstraintDef' );
683	if ( null !== $constraint ) {
684	$this->record_add_constraint_or_index( $table_is_temporary, $table_name, $constraint );
685	continue;
686	}
687
688	throw new \Exception( sprintf( 'Unsupported ALTER TABLE ADD action: %s', $first_token->get_value() ) );
689	}
690
691	// CHANGE [COLUMN]
692	if ( WP_MySQL_Lexer::CHANGE_SYMBOL === $first_token->id ) {
693	$old_name = $this->get_value( $action->get_first_child_node( 'fieldIdentifier' ) );
694	$new_name = $this->get_value( $action->get_first_child_node( 'identifier' ) );
695	$this->record_change_column(
696	$table_is_temporary,
697	$table_name,
698	$old_name,
699	$new_name,
700	$action->get_first_descendant_node( 'fieldDefinition' )
701	);
702	continue;
703	}
704
705	// MODIFY [COLUMN]
706	if ( WP_MySQL_Lexer::MODIFY_SYMBOL === $first_token->id ) {
707	$name = $this->get_value( $action->get_first_child_node( 'fieldIdentifier' ) );
708	$this->record_modify_column(
709	$table_is_temporary,
710	$table_name,
711	$name,
712	$action->get_first_descendant_node( 'fieldDefinition' )
713	);
714	continue;
715	}
716
717	// DROP
718	if ( WP_MySQL_Lexer::DROP_SYMBOL === $first_token->id ) {
719	// DROP CONSTRAINT
720	if ( $action->has_child_token( WP_MySQL_Lexer::CONSTRAINT_SYMBOL ) ) {
721	$name = $this->get_value( $action->get_first_child_node( 'identifier' ) );
722	$this->record_drop_constraint( $table_is_temporary, $table_name, $name );
723	continue;
724	}
725
726	// DROP PRIMARY KEY
727	if ( $action->has_child_token( WP_MySQL_Lexer::PRIMARY_SYMBOL ) ) {
728	$this->record_drop_key( $table_is_temporary, $table_name, 'PRIMARY' );
729	continue;
730	}
731
732	// DROP FOREIGN KEY
733	if ( $action->has_child_token( WP_MySQL_Lexer::FOREIGN_SYMBOL ) ) {
734	$field_identifier = $action->get_first_child_node( 'fieldIdentifier' );
735	$identifiers = $field_identifier->get_descendant_nodes( 'identifier' );
736	$name = $this->get_value( end( $identifiers ) );
737	$this->record_drop_foreign_key( $table_is_temporary, $table_name, $name );
738	continue;
739	}
740
741	// DROP CHECK
742	if ( $action->has_child_token( WP_MySQL_Lexer::CHECK_SYMBOL ) ) {
743	$name = $this->get_value( $action->get_first_child_node( 'identifier' ) );
744	$this->record_drop_check_constraint( $table_is_temporary, $table_name, $name );
745	continue;
746	}
747
748	// DROP [COLUMN]
749	$column_ref = $action->get_first_child_node( 'fieldIdentifier' );
750	if ( null !== $column_ref ) {
751	$name = $this->get_value( $column_ref );
752	$this->record_drop_column( $table_is_temporary, $table_name, $name );
753	continue;
754	}
755
756	// DROP INDEX
757	if ( $action->has_child_node( 'keyOrIndex' ) ) {
758	$name = $this->get_value( $action->get_first_child_node( 'indexRef' ) );
759	$this->record_drop_index_data( $table_is_temporary, $table_name, $name );
760	continue;
761	}
762	}
763	}
764	}
765
766	/**
767	* Analyze DROP TABLE statement and record data in the information schema.
768	*
769	* @param WP_Parser_Node $node The "dropStatement" AST node with "dropTable" child.
770	*/
771	public function record_drop_table( WP_Parser_Node $node ): void {
772	$child_node = $node->get_first_child_node();
773
774	$has_temporary_keyword = $child_node->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
775
776	$table_refs = $child_node->get_first_child_node( 'tableRefList' )->get_child_nodes();
777	foreach ( $table_refs as $table_ref ) {
778	$table_name = $this->get_table_name_from_node( $table_ref );
779	$table_is_temporary = $has_temporary_keyword \|\| $this->temporary_table_exists( $table_name );
780
781	$this->delete_values(
782	$this->get_table_name( $table_is_temporary, 'tables' ),
783	array(
784	'table_schema' => self::SAVED_DATABASE_NAME,
785	'table_name' => $table_name,
786	)
787	);
788	$this->delete_values(
789	$this->get_table_name( $table_is_temporary, 'columns' ),
790	array(
791	'table_schema' => self::SAVED_DATABASE_NAME,
792	'table_name' => $table_name,
793	)
794	);
795	$this->delete_values(
796	$this->get_table_name( $table_is_temporary, 'statistics' ),
797	array(
798	'table_schema' => self::SAVED_DATABASE_NAME,
799	'table_name' => $table_name,
800	)
801	);
802	$this->delete_values(
803	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
804	array(
805	'table_schema' => self::SAVED_DATABASE_NAME,
806	'table_name' => $table_name,
807	)
808	);
809	}
810
811	// @TODO: RESTRICT vs. CASCADE
812	}
813
814	/**
815	* Analyze CREATE INDEX definition and record data in the information schema.
816	*
817	* @param WP_Parser_Node $node The "createStatement" AST node with "createIndex" child.
818	*/
819	public function record_create_index( WP_Parser_Node $node ): void {
820	$create_index = $node->get_first_child_node( 'createIndex' );
821	$target = $create_index->get_first_child_node( 'createIndexTarget' );
822	$table_ref = $target->get_first_child_node( 'tableRef' );
823	$table_name = $this->get_table_name_from_node( $table_ref );
824
825	$table_is_temporary = $this->temporary_table_exists( $table_name );
826	$this->record_add_index( $table_is_temporary, $table_name, $create_index );
827	}
828
829	/**
830	* Analyze DROP INDEX definition and record data in the information schema.
831	*
832	* @param WP_Parser_Node $node The "dropStatement" AST node with "dropIndex" child.
833	*/
834	public function record_drop_index( WP_Parser_Node $node ): void {
835	$drop_index = $node->get_first_child_node( 'dropIndex' );
836	$table_ref = $drop_index->get_first_child_node( 'tableRef' );
837	$table_name = $this->get_table_name_from_node( $table_ref );
838	$index_name = $this->get_value( $drop_index->get_first_child_node( 'indexRef' ) );
839	$table_is_temporary = $this->temporary_table_exists( $table_name );
840	$this->record_drop_index_data( $table_is_temporary, $table_name, $index_name );
841	}
842
843	/**
844	* Analyze ADD COLUMN definition and record data in the information schema.
845	*
846	* @param bool $table_is_temporary Whether the table is temporary.
847	* @param string $table_name The table name.
848	* @param string $column_name The column name.
849	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
850	*/
851	private function record_add_column(
852	bool $table_is_temporary,
853	string $table_name,
854	string $column_name,
855	WP_Parser_Node $node
856	): void {
857	$columns_table_name = $this->get_table_name( $table_is_temporary, 'columns' );
858	$position = $this->connection->query(
859	'
860	SELECT MAX(ordinal_position)
861	FROM ' . $this->connection->quote_identifier( $columns_table_name ) . '
862	WHERE table_schema = ?
863	AND table_name = ?
864	',
865	array( self::SAVED_DATABASE_NAME, $table_name )
866	)->fetchColumn();
867
868	$column_data = $this->extract_column_data( $table_name, $column_name, $node, (int) $position + 1 );
869	try {
870	$this->insert_values(
871	$this->get_table_name( $table_is_temporary, 'columns' ),
872	$column_data
873	);
874	} catch ( PDOException $e ) {
875	if ( '23000' === $e->getCode() ) {
876	throw WP_SQLite_Information_Schema_Exception::duplicate_column_name( $column_name );
877	}
878	throw $e;
879	}
880
881	$index_data = $this->extract_column_statistics_data( $table_name, $column_name, $node, true );
882	if ( null !== $index_data ) {
883	$this->insert_values(
884	$this->get_table_name( $table_is_temporary, 'statistics' ),
885	$index_data
886	);
887	}
888
889	$constraint_data = $this->extract_table_constraint_data(
890	$node,
891	$table_name,
892	$index_data['index_name'] ?? null
893	);
894	if ( null !== $constraint_data ) {
895	$this->insert_values(
896	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
897	$constraint_data
898	);
899	}
900	}
901
902	/**
903	* Analyze CHANGE COLUMN definition and record data in the information schema.
904	*
905	* @param bool $table_is_temporary Whether the table is temporary.
906	* @param string $table_name The table name.
907	* @param string $column_name The column name.
908	* @param string $new_column_name The new column name when the column is renamed.
909	* @param WP_Parser_Node $node The "fieldDefinition" AST node.
910	*/
911	private function record_change_column(
912	bool $table_is_temporary,
913	string $table_name,
914	string $column_name,
915	string $new_column_name,
916	WP_Parser_Node $node
917	): void {
918	$column_data = $this->extract_column_data( $table_name, $new_column_name, $node, 0 );
919	unset( $column_data['ordinal_position'] );
920	$this->update_values(
921	$this->get_table_name( $table_is_temporary, 'columns' ),
922	$column_data,
923	array(
924	'table_schema' => self::SAVED_DATABASE_NAME,
925	'table_name' => $table_name,
926	'column_name' => $column_name,
927	)
928	);
929
930	// Update column name in statistics, if it has changed.
931	if ( $new_column_name !== $column_name ) {
932	$this->update_values(
933	$this->get_table_name( $table_is_temporary, 'statistics' ),
934	array(
935	'column_name' => $new_column_name,
936	),
937	array(
938	'table_schema' => self::SAVED_DATABASE_NAME,
939	'table_name' => $table_name,
940	'column_name' => $column_name,
941	)
942	);
943	}
944
945	// Handle inline constraints. When inline constraint is defined, MySQL
946	// always adds a new constraint rather than replacing an existing one.
947	$index_data = $this->extract_column_statistics_data(
948	$table_name,
949	$new_column_name,
950	$node,
951	'YES' === $column_data['is_nullable']
952	);
953	if ( null !== $index_data ) {
954	$this->insert_values(
955	$this->get_table_name( $table_is_temporary, 'statistics' ),
956	$index_data
957	);
958	$this->sync_column_key_info( $table_is_temporary, $table_name );
959	}
960
961	$constraint_data = $this->extract_table_constraint_data(
962	$node,
963	$table_name,
964	$index_data['index_name'] ?? null
965	);
966	if ( null !== $constraint_data ) {
967	$this->insert_values(
968	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
969	$constraint_data
970	);
971	}
972	}
973
974	/**
975	* Analyze MODIFY COLUMN definition and record data in the information schema.
976	*
977	* @param bool $table_is_temporary Whether the table is temporary.
978	* @param string $table_name The table name.
979	* @param string $column_name The column name.
980	* @param WP_Parser_Node $node The "fieldDefinition" AST node.
981	*/
982	private function record_modify_column(
983	bool $table_is_temporary,
984	string $table_name,
985	string $column_name,
986	WP_Parser_Node $node
987	): void {
988	$this->record_change_column( $table_is_temporary, $table_name, $column_name, $column_name, $node );
989	}
990
991	/**
992	* Record DROP COLUMN data in the information schema.
993	*
994	* @param bool $table_is_temporary Whether the table is temporary.
995	* @param string $table_name The table name.
996	* @param string $column_name The column name.
997	*/
998	private function record_drop_column(
999	bool $table_is_temporary,
1000	string $table_name,
1001	string $column_name
1002	): void {
1003	// Delete the column record from the columns table.
1004	$this->delete_values(
1005	$this->get_table_name( $table_is_temporary, 'columns' ),
1006	array(
1007	'table_schema' => self::SAVED_DATABASE_NAME,
1008	'table_name' => $table_name,
1009	'column_name' => $column_name,
1010	)
1011	);
1012
1013	/*
1014	* When a column is dropped, we need to reflect the effects of the change
1015	* on the existing indexes and constraints that the column was part of.
1016	*
1017	* This means:
1018	*
1019	* 1. Remove the column records from the statistics table.
1020	* 2. Renumber SEQ_IN_INDEX values in the statistics table so that
1021	* there are no sequence gaps caused by the removed column.
1022	* 3. Recompute column key information in the statistics table.
1023	* 4. Delete the table constraint records for no longer existing indexes.
1024	*
1025	* From MySQL documentation:
1026	*
1027	* If columns are dropped from a table, the columns are also removed
1028	* from any index of which they are a part. If all columns that make up
1029	* an index are dropped, the index is dropped as well.
1030	*
1031	* This means we need to remove the records from the STATISTICS table,
1032	* renumber the SEQ_IN_INDEX values, and resync the column key info.
1033	*
1034	* See:
1035	* - https://dev.mysql.com/doc/refman/8.4/en/alter-table.html
1036	*/
1037	$statistics_table = $this->get_table_name( $table_is_temporary, 'statistics' );
1038	$constraints_table = $this->get_table_name( $table_is_temporary, 'table_constraints' );
1039
1040	/*
1041	* 1. Delete the column records from the statistics table.
1042	*
1043	* In MySQL, when a column is dropped, it is removed from all indexes
1044	* that it was part of. An index is dropped when it has no more columns.
1045	*/
1046	$this->delete_values(
1047	$statistics_table,
1048	array(
1049	'table_schema' => self::SAVED_DATABASE_NAME,
1050	'table_name' => $table_name,
1051	'column_name' => $column_name,
1052	)
1053	);
1054
1055	/*
1056	* 2. Renumber SEQ_IN_INDEX values in the statistics table.
1057	*
1058	* When a column is removed from a multi-column index, it can leave a gap
1059	* in the numeric sequence of SEQ_IN_INDEX values in the statistics table.
1060	*/
1061	$this->connection->query(
1062	sprintf(
1063	'WITH renumbered AS (
1064	SELECT
1065	rowid,
1066	row_number() OVER (PARTITION BY index_name ORDER BY seq_in_index) AS seq_in_index
1067	FROM %s
1068	WHERE table_schema = ?
1069	AND table_name = ?
1070	)
1071	UPDATE %s AS statistics
1072	SET seq_in_index = (SELECT seq_in_index FROM renumbered WHERE rowid = statistics.rowid)
1073	WHERE statistics.rowid IN (SELECT rowid FROM renumbered)',
1074	$this->connection->quote_identifier( $statistics_table ),
1075	$this->connection->quote_identifier( $statistics_table )
1076	),
1077	array( self::SAVED_DATABASE_NAME, $table_name )
1078	);
1079
1080	/*
1081	* 3. Recompute column key data in the statistics table.
1082	*
1083	* When a column is removed from a multi-column index, it can cause the
1084	* value of COLUMN_KEY in the statistics for other columns to change.
1085	*/
1086	$this->sync_column_key_info( $table_is_temporary, $table_name );
1087
1088	/*
1089	* 4. Delete the table constraint records for no longer existing indexes.
1090	*
1091	* If there are no more columns left in an index the column was part of,
1092	* we need to make sure that the associated table constraint records are
1093	* deleted as well. Therefore, remove all index-specific table constraint
1094	* records that have no index data associated with them for a given table.
1095	*/
1096	$this->connection->query(
1097	sprintf(
1098	"DELETE FROM %s
1099	WHERE table_schema = ?
1100	AND table_name = ?
1101	AND constraint_type IN ('PRIMARY KEY', 'UNIQUE')
1102	AND constraint_name NOT IN (
1103	SELECT DISTINCT index_name FROM %s WHERE table_schema = ? AND table_name = ?
1104	)",
1105	$this->connection->quote_identifier( $constraints_table ),
1106	$this->connection->quote_identifier( $statistics_table )
1107	),
1108	array( self::SAVED_DATABASE_NAME, $table_name, self::SAVED_DATABASE_NAME, $table_name )
1109	);
1110	}
1111
1112	/**
1113	* Analyze ADD "tableConstraintDef" and record data in the information schema.
1114	*
1115	* @param bool $table_is_temporary Whether the table is temporary.
1116	* @param string $table_name The table name.
1117	* @param WP_Parser_Node $node The "tableConstraintDef" AST node.
1118	*/
1119	private function record_add_constraint_or_index(
1120	bool $table_is_temporary,
1121	string $table_name,
1122	WP_Parser_Node $node
1123	): void {
1124	$child = $node->get_first_child();
1125	$first_child_token_id = $child instanceof WP_MySQL_Token ? $child->id : null;
1126	if (
1127	WP_MySQL_Lexer::KEY_SYMBOL === $first_child_token_id
1128	\|\| WP_MySQL_Lexer::INDEX_SYMBOL === $first_child_token_id
1129	\|\| WP_MySQL_Lexer::FULLTEXT_SYMBOL === $first_child_token_id
1130	\|\| WP_MySQL_Lexer::SPATIAL_SYMBOL === $first_child_token_id
1131	) {
1132	$this->record_add_index( $table_is_temporary, $table_name, $node );
1133	} else {
1134	$this->record_add_constraint( $table_is_temporary, $table_name, $node );
1135	}
1136	}
1137
1138	/**
1139	* Analyze index definition and record data in the information schema.
1140	*
1141	* This serves both "ALTER TABLE ... ADD ..." and "CREATE INDEX" statements.
1142	*
1143	* @param bool $table_is_temporary Whether the table is temporary.
1144	* @param string $table_name The table name.
1145	* @param WP_Parser_Node $node The "tableConstraintDef" or "createIndex" AST node.
1146	*/
1147	private function record_add_index(
1148	bool $table_is_temporary,
1149	string $table_name,
1150	WP_Parser_Node $node
1151	): void {
1152	$statistics_data = $this->extract_index_statistics_data( $table_is_temporary, $table_name, $node );
1153	$index_name = $statistics_data[0]['index_name'];
1154	foreach ( $statistics_data as $index_data ) {
1155	try {
1156	$this->insert_values(
1157	$this->get_table_name( $table_is_temporary, 'statistics' ),
1158	$index_data
1159	);
1160	} catch ( PDOException $e ) {
1161	if ( '23000' === $e->getCode() ) {
1162	throw WP_SQLite_Information_Schema_Exception::duplicate_key_name( $index_name );
1163	}
1164	throw $e;
1165	}
1166	}
1167
1168	// Sync column info from index data.
1169	$this->sync_column_key_info( $table_is_temporary, $table_name );
1170
1171	// For UNIQUE index, save also constraint data.
1172	if ( $node->has_child_token( WP_MySQL_Lexer::UNIQUE_SYMBOL ) ) {
1173	$constraint_data = $this->extract_table_constraint_data(
1174	$node,
1175	$table_name,
1176	$index_name
1177	);
1178
1179	if ( null !== $constraint_data ) {
1180	$this->insert_values(
1181	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1182	$constraint_data
1183	);
1184	}
1185	}
1186	}
1187
1188	/**
1189	* Record DROP INDEX data in the information schema.
1190	*
1191	* @param bool $table_is_temporary Whether the table is temporary.
1192	* @param string $table_name The table name.
1193	* @param string $index_name The index name.
1194	*/
1195	private function record_drop_index_data(
1196	bool $table_is_temporary,
1197	string $table_name,
1198	string $index_name
1199	): void {
1200	// Delete index data.
1201	$this->delete_values(
1202	$this->get_table_name( $table_is_temporary, 'statistics' ),
1203	array(
1204	'table_schema' => self::SAVED_DATABASE_NAME,
1205	'table_name' => $table_name,
1206	'index_name' => $index_name,
1207	)
1208	);
1209
1210	/*
1211	* Delete associated table constraint data.
1212	*
1213	* A table constraint record is saved for PRIMARY KEY and UNIQUE indexes.
1214	* We don't need to read the schema to get the constraint type, because:
1215	*
1216	* 1. In MySQL, all primary keys are named "PRIMARY", and no other
1217	* indexes can be named so. This way we can identify primary keys.
1218	* 2. In MySQL, all indexes in a table must have distinct names, no
1219	* matter the index type. Therefore, if a table constraint record
1220	* exists for a given index name, we know it is a unique index.
1221	*/
1222	$constraint_type =
1223	strtoupper( $index_name ) === 'PRIMARY' ? 'PRIMARY KEY' : 'UNIQUE';
1224
1225	$this->delete_values(
1226	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1227	array(
1228	'table_schema' => self::SAVED_DATABASE_NAME,
1229	'table_name' => $table_name,
1230	'constraint_name' => $index_name,
1231	'constraint_type' => $constraint_type,
1232	)
1233	);
1234
1235	// Sync column info from constraint data.
1236	$this->sync_column_key_info( $table_is_temporary, $table_name );
1237	}
1238
1239	/**
1240	* Analyze ADD CONSTRAINT definition and record data in the information schema.
1241	*
1242	* @param bool $table_is_temporary Whether the table is temporary.
1243	* @param string $table_name The table name.
1244	* @param WP_Parser_Node $node The "tableConstraintDef" AST node.
1245	*/
1246	private function record_add_constraint(
1247	bool $table_is_temporary,
1248	string $table_name,
1249	WP_Parser_Node $node
1250	): void {
1251	// Get first constraint keyword.
1252	$children = $node->get_children();
1253	if ( $children[0] instanceof WP_Parser_Node && 'constraintName' === $children[0]->rule_name ) {
1254	$keyword = $children[1];
1255	} else {
1256	$keyword = $children[0];
1257	}
1258	if ( ! $keyword instanceof WP_MySQL_Token ) {
1259	$keyword = $keyword->get_first_child_token();
1260	}
1261
1262	// PRIMARY KEY and UNIQUE require an index.
1263	if (
1264	WP_MySQL_Lexer::PRIMARY_SYMBOL === $keyword->id
1265	\|\| WP_MySQL_Lexer::UNIQUE_SYMBOL === $keyword->id
1266	) {
1267	$statistics_data = $this->extract_index_statistics_data( $table_is_temporary, $table_name, $node );
1268	$index_name = $statistics_data[0]['index_name'];
1269	foreach ( $statistics_data as $index_data ) {
1270	try {
1271	$this->insert_values(
1272	$this->get_table_name( $table_is_temporary, 'statistics' ),
1273	$index_data
1274	);
1275	} catch ( PDOException $e ) {
1276	if ( '23000' === $e->getCode() ) {
1277	throw WP_SQLite_Information_Schema_Exception::duplicate_key_name( $index_name );
1278	}
1279	throw $e;
1280	}
1281	}
1282
1283	// Sync column info from index data.
1284	$this->sync_column_key_info( $table_is_temporary, $table_name );
1285	} else {
1286	$index_name = null;
1287	}
1288
1289	// Extract constraint data.
1290	$constraint_data = $this->extract_table_constraint_data( $node, $table_name, $index_name );
1291	$referential_constraint_data = $this->extract_referential_constraint_data( $node, $table_name );
1292	$key_column_usage_data = $this->extract_key_column_usage_data( $node, $table_name, $index_name );
1293	$check_constraint_data = $this->extract_check_constraint_data( $node, $table_name );
1294
1295	// Save constraint data.
1296	if ( null !== $constraint_data ) {
1297	$this->insert_values(
1298	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1299	$constraint_data
1300	);
1301	}
1302
1303	if ( null !== $referential_constraint_data ) {
1304	$this->insert_values(
1305	$this->get_table_name( $table_is_temporary, 'referential_constraints' ),
1306	$referential_constraint_data
1307	);
1308	}
1309
1310	foreach ( $key_column_usage_data as $key_column_usage_item ) {
1311	$this->insert_values(
1312	$this->get_table_name( $table_is_temporary, 'key_column_usage' ),
1313	$key_column_usage_item
1314	);
1315	}
1316
1317	if ( null !== $check_constraint_data ) {
1318	$this->insert_values(
1319	$this->get_table_name( $table_is_temporary, 'check_constraints' ),
1320	$check_constraint_data
1321	);
1322	}
1323	}
1324
1325	/**
1326	* Analyze DROP CONSTRAINT statement and record data in the information schema.
1327	*
1328	* @param bool $table_is_temporary Whether the table is temporary.
1329	* @param string $table_name The table name.
1330	* @param string $name The constraint name.
1331	*/
1332	private function record_drop_constraint(
1333	bool $table_is_temporary,
1334	string $table_name,
1335	string $name
1336	): void {
1337	$constraint_types = $this->connection->query(
1338	sprintf(
1339	'SELECT constraint_type FROM %s WHERE table_schema = ? AND table_name = ? AND constraint_name = ?',
1340	$this->connection->quote_identifier( $this->get_table_name( $table_is_temporary, 'table_constraints' ) )
1341	),
1342	array(
1343	self::SAVED_DATABASE_NAME,
1344	$table_name,
1345	$name,
1346	)
1347	)->fetchAll(
1348	PDO::FETCH_COLUMN // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
1349	);
1350
1351	if ( 0 === count( $constraint_types ) ) {
1352	throw WP_SQLite_Information_Schema_Exception::constraint_does_not_exist( $name );
1353	}
1354
1355	// MySQL doesn't allow a generic DELETE CONSTRAINT clause when the target
1356	// is ambiguous, i.e., when multiple constraints with the same name exist.
1357	if ( count( $constraint_types ) > 1 ) {
1358	throw WP_SQLite_Information_Schema_Exception::multiple_constraints_with_name( $name );
1359	}
1360
1361	$constraint_type = $constraint_types[0];
1362	if ( 'PRIMARY KEY' === $constraint_type ) {
1363	$this->record_drop_key( $table_is_temporary, $table_name, 'PRIMARY' );
1364	} elseif ( 'UNIQUE' === $constraint_type ) {
1365	$this->record_drop_key( $table_is_temporary, $table_name, $name );
1366	} elseif ( 'FOREIGN KEY' === $constraint_type ) {
1367	$this->record_drop_foreign_key( $table_is_temporary, $table_name, $name );
1368	} elseif ( 'CHECK' === $constraint_type ) {
1369	$this->record_drop_check_constraint( $table_is_temporary, $table_name, $name );
1370	} else {
1371	throw new \Exception(
1372	"DROP CONSTRAINT for constraint type '$constraint_type' is not supported."
1373	);
1374	}
1375	}
1376
1377	/**
1378	* Analyze DROP PRIMARY KEY or DROP UNIQUE statement and record data
1379	* in the information schema.
1380	*
1381	* @param bool $table_is_temporary Whether the table is temporary.
1382	* @param string $table_name The table name.
1383	* @param mixed $name The constraint name.
1384	*/
1385	private function record_drop_key(
1386	bool $table_is_temporary,
1387	string $table_name,
1388	string $name
1389	): void {
1390	$this->delete_values(
1391	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1392	array(
1393	'TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1394	'TABLE_NAME' => $table_name,
1395	'CONSTRAINT_NAME' => $name,
1396	)
1397	);
1398
1399	$this->delete_values(
1400	$this->get_table_name( $table_is_temporary, 'statistics' ),
1401	array(
1402	'TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1403	'TABLE_NAME' => $table_name,
1404	'INDEX_NAME' => $name,
1405	)
1406	);
1407
1408	$this->delete_values(
1409	$this->get_table_name( $table_is_temporary, 'key_column_usage' ),
1410	array(
1411	'TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1412	'TABLE_NAME' => $table_name,
1413	'CONSTRAINT_NAME' => $name,
1414
1415	// Remove only PRIMARY/UNIQUE key records; not FOREIGN KEY data.
1416	'REFERENCED_TABLE_SCHEMA' => null,
1417	)
1418	);
1419
1420	// Sync column info from constraint data.
1421	$this->sync_column_key_info( $table_is_temporary, $table_name );
1422	}
1423
1424	/**
1425	* Analyze DROP FOREIGN KEY statement and record data in the information schema.
1426	*
1427	* @param bool $table_is_temporary Whether the table is temporary.
1428	* @param string $table_name The table name.
1429	* @param string $name The foreign key name.
1430	*/
1431	private function record_drop_foreign_key(
1432	bool $table_is_temporary,
1433	string $table_name,
1434	string $name
1435	): void {
1436	$this->delete_values(
1437	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1438	array(
1439	'TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1440	'TABLE_NAME' => $table_name,
1441	'CONSTRAINT_NAME' => $name,
1442	)
1443	);
1444
1445	$this->delete_values(
1446	$this->get_table_name( $table_is_temporary, 'referential_constraints' ),
1447	array(
1448	'CONSTRAINT_SCHEMA' => self::SAVED_DATABASE_NAME,
1449	'TABLE_NAME' => $table_name,
1450	'CONSTRAINT_NAME' => $name,
1451	)
1452	);
1453
1454	$this->delete_values(
1455	$this->get_table_name( $table_is_temporary, 'key_column_usage' ),
1456	array(
1457	'TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1458	'TABLE_NAME' => $table_name,
1459	'CONSTRAINT_NAME' => $name,
1460
1461	// Remove only FOREIGN KEY records; not PRIMARY/UNIQUE KEY data.
1462	'REFERENCED_TABLE_SCHEMA' => self::SAVED_DATABASE_NAME,
1463	)
1464	);
1465	}
1466
1467	/**
1468	* Analyze DROP CHECK statement and record data in the information schema.
1469	*
1470	* @param bool $table_is_temporary Whether the table is temporary.
1471	* @param string $table_name The table name.
1472	* @param string $name The check constraint name.
1473	*/
1474	private function record_drop_check_constraint(
1475	bool $table_is_temporary,
1476	string $table_name,
1477	string $name
1478	): void {
1479	$this->delete_values(
1480	$this->get_table_name( $table_is_temporary, 'table_constraints' ),
1481	array(
1482	'CONSTRAINT_SCHEMA' => self::SAVED_DATABASE_NAME,
1483	'TABLE_NAME' => $table_name,
1484	'CONSTRAINT_TYPE' => 'CHECK',
1485	'CONSTRAINT_NAME' => $name,
1486	)
1487	);
1488
1489	$this->delete_values(
1490	$this->get_table_name( $table_is_temporary, 'check_constraints' ),
1491	array(
1492	'CONSTRAINT_SCHEMA' => self::SAVED_DATABASE_NAME,
1493	'CONSTRAINT_NAME' => $name,
1494	)
1495	);
1496	}
1497
1498	/**
1499	* Analyze "columnDefinition" or "fieldDefinition" AST node and extract column data.
1500	*
1501	* @param string $table_name The table name.
1502	* @param string $column_name The column name.
1503	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
1504	* @param int $position The ordinal position of the column in the table.
1505	* @return array Column data for the information schema.
1506	*/
1507	private function extract_column_data( string $table_name, string $column_name, WP_Parser_Node $node, int $position ): array {
1508	list ( $data_type, $column_type ) = $this->get_column_data_types( $node );
1509
1510	$default = $this->get_column_default( $node, $data_type, $column_name );
1511	$nullable = $this->get_column_nullable( $node );
1512	$key = $this->get_column_key( $node );
1513	$extra = $this->get_column_extra( $node );
1514	$comment = $this->get_column_comment( $node );
1515
1516	list ( $charset, $collation ) = $this->get_column_charset_and_collation( $node, $data_type );
1517	list ( $char_length, $octet_length ) = $this->get_column_lengths( $node, $data_type, $charset );
1518	list ( $precision, $scale ) = $this->get_column_numeric_attributes( $node, $data_type );
1519	$datetime_precision = $this->get_column_datetime_precision( $node, $data_type );
1520	$generation_expression = $this->get_column_generation_expression( $node );
1521
1522	return array(
1523	'table_schema' => self::SAVED_DATABASE_NAME,
1524	'table_name' => $table_name,
1525	'column_name' => $column_name,
1526	'ordinal_position' => $position,
1527	'column_default' => $default,
1528	'is_nullable' => $nullable,
1529	'data_type' => $data_type,
1530	'character_maximum_length' => $char_length,
1531	'character_octet_length' => $octet_length,
1532	'numeric_precision' => $precision,
1533	'numeric_scale' => $scale,
1534	'datetime_precision' => $datetime_precision,
1535	'character_set_name' => $charset,
1536	'collation_name' => $collation,
1537	'column_type' => $column_type,
1538	'column_key' => $key,
1539	'extra' => $extra,
1540	'privileges' => 'select,insert,update,references',
1541	'column_comment' => $comment,
1542	'generation_expression' => $generation_expression,
1543	'srs_id' => null, // not implemented
1544	);
1545	}
1546
1547	/**
1548	* Analyze "columnDefinition" or "fieldDefinition" AST node and extract constraint data.
1549	*
1550	* @param string $table_name The table name.
1551	* @param string $column_name The column name.
1552	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
1553	* @param bool $nullable Whether the column is nullable.
1554	* @return array\|null Column statistics data for the information schema.
1555	*/
1556	private function extract_column_statistics_data(
1557	string $table_name,
1558	string $column_name,
1559	WP_Parser_Node $node,
1560	bool $nullable
1561	): ?array {
1562	// Handle inline PRIMARY KEY and UNIQUE constraints.
1563	$has_inline_primary_key = null !== $node->get_first_descendant_token( WP_MySQL_Lexer::KEY_SYMBOL );
1564	$has_inline_unique_key = null !== $node->get_first_descendant_token( WP_MySQL_Lexer::UNIQUE_SYMBOL );
1565	if ( $has_inline_primary_key \|\| $has_inline_unique_key ) {
1566	$index_name = $has_inline_primary_key ? 'PRIMARY' : $column_name;
1567	return array(
1568	'table_schema' => self::SAVED_DATABASE_NAME,
1569	'table_name' => $table_name,
1570	'non_unique' => 0,
1571	'index_schema' => self::SAVED_DATABASE_NAME,
1572	'index_name' => $index_name,
1573	'seq_in_index' => 1,
1574	'column_name' => $column_name,
1575	'collation' => 'A',
1576	'cardinality' => 0, // not implemented
1577	'sub_part' => null,
1578	'packed' => null, // not implemented
1579	'nullable' => true === $nullable ? 'YES' : '',
1580	'index_type' => 'BTREE',
1581	'comment' => '', // not implemented
1582	'index_comment' => '', // @TODO
1583	'is_visible' => 'YES', // @TODO: Save actual visibility value.
1584	'expression' => null, // @TODO
1585	);
1586	}
1587	return null;
1588	}
1589
1590	/**
1591	* Analyze "tableConstraintDef" or "createIndex" AST node and extract index data.
1592	*
1593	* @param bool $table_is_temporary Whether the table is temporary.
1594	* @param string $table_name The table name.
1595	* @param WP_Parser_Node $node The "tableConstraintDef" or "createIndex" AST node.
1596	* @return array Index statistics data for the information schema.
1597	*/
1598	private function extract_index_statistics_data(
1599	bool $table_is_temporary,
1600	string $table_name,
1601	WP_Parser_Node $node
1602	): array {
1603	// Get first keyword.
1604	$children = $node->get_children();
1605	$keyword = $children[0] instanceof WP_MySQL_Token ? $children[0] : $children[1];
1606	if ( ! $keyword instanceof WP_MySQL_Token ) {
1607	$keyword = $keyword->get_first_child_token();
1608	}
1609
1610	// Get key parts.
1611	$key_list = $node->get_first_descendant_node( 'keyListVariants' )->get_first_child();
1612	if ( 'keyListWithExpression' === $key_list->rule_name ) {
1613	$key_parts = array();
1614	foreach ( $key_list->get_descendant_nodes( 'keyPartOrExpression' ) as $key_part ) {
1615	$key_parts[] = $key_part->get_first_child();
1616	}
1617	} else {
1618	$key_parts = $key_list->get_descendant_nodes( 'keyPart' );
1619	}
1620
1621	// Get index column names.
1622	$key_part_column_names = array();
1623	foreach ( $key_parts as $key_part ) {
1624	$key_part_column_names[] = $this->get_index_column_name( $key_part );
1625	}
1626
1627	// Fetch column info.
1628	$column_names = array_filter( $key_part_column_names );
1629	if ( count( $column_names ) > 0 ) {
1630	$columns_table_name = $this->get_table_name( $table_is_temporary, 'columns' );
1631	$column_info = $this->connection->query(
1632	'
1633	SELECT column_name, data_type, is_nullable, character_maximum_length
1634	FROM ' . $this->connection->quote_identifier( $columns_table_name ) . '
1635	WHERE table_schema = ?
1636	AND table_name = ?
1637	AND column_name IN (' . implode( ',', array_fill( 0, count( $column_names ), '?' ) ) . ')
1638	',
1639	array_merge( array( self::SAVED_DATABASE_NAME, $table_name ), $column_names )
1640	)->fetchAll(
1641	PDO::FETCH_ASSOC // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
1642	);
1643	} else {
1644	$column_info = array();
1645	}
1646
1647	$column_info_map = array_combine(
1648	array_column( $column_info, 'COLUMN_NAME' ),
1649	$column_info
1650	);
1651
1652	// Get first index column data type (needed for index type).
1653	$first_column_name = $this->get_index_column_name( $key_parts[0] );
1654	$first_column_type = $column_info_map[ $first_column_name ]['DATA_TYPE'] ?? null;
1655	$has_spatial_column = null !== $first_column_type && $this->is_spatial_data_type( $first_column_type );
1656
1657	$non_unique = $this->get_index_non_unique( $keyword );
1658	$index_name = $this->get_index_name( $node, $table_name );
1659	$index_type = $this->get_index_type( $node, $keyword, $has_spatial_column );
1660	$index_comment = $this->get_index_comment( $node );
1661	$seq_in_index = 1;
1662	$statistics_data = array();
1663	foreach ( $key_parts as $i => $key_part ) {
1664	$column_name = $key_part_column_names[ $i ];
1665	$collation = $this->get_index_column_collation( $key_part, $index_type );
1666	$column_info = $column_info_map[ $column_name ] ?? null;
1667
1668	if ( null === $column_info ) {
1669	throw WP_SQLite_Information_Schema_Exception::key_column_not_found( $column_name );
1670	}
1671
1672	if (
1673	'PRIMARY' === $index_name
1674	\|\| 'NO' === $column_info_map[ $column_name ]['IS_NULLABLE']
1675	) {
1676	$nullable = '';
1677	} else {
1678	$nullable = 'YES';
1679	}
1680
1681	$sub_part = $this->get_index_column_sub_part(
1682	$key_part,
1683	$column_info_map[ $column_name ]['CHARACTER_MAXIMUM_LENGTH'],
1684	$has_spatial_column
1685	);
1686
1687	$statistics_data[] = array(
1688	'table_schema' => self::SAVED_DATABASE_NAME,
1689	'table_name' => $table_name,
1690	'non_unique' => $non_unique,
1691	'index_schema' => self::SAVED_DATABASE_NAME,
1692	'index_name' => $index_name,
1693	'seq_in_index' => $seq_in_index,
1694	'column_name' => $column_name,
1695	'collation' => $collation,
1696	'cardinality' => 0, // not implemented
1697	'sub_part' => $sub_part,
1698	'packed' => null, // not implemented
1699	'nullable' => $nullable,
1700	'index_type' => $index_type,
1701	'comment' => '', // not implemented
1702	'index_comment' => $index_comment,
1703	'is_visible' => 'YES', // @TODO: Save actual visibility value.
1704	'expression' => null, // @TODO
1705	);
1706
1707	$seq_in_index += 1;
1708	}
1709	return $statistics_data;
1710	}
1711
1712	/**
1713	* Extract table constraint data from the "tableConstraintDef" or "columnDefinition" AST node.
1714	*
1715	* @param WP_Parser_Node $node The "tableConstraintDef" or "columnDefinition" AST node.
1716	* @param string $table_name The table name.
1717	* @param string $column_name The column name.
1718	* @return array\|null Table constraint data for the information schema.
1719	*/
1720	public function extract_table_constraint_data(
1721	WP_Parser_Node $node,
1722	string $table_name,
1723	?string $index_name = null
1724	): ?array {
1725	$type = $this->get_table_constraint_type( $node );
1726	if ( null === $type ) {
1727	return null;
1728	}
1729
1730	// Index name always takes precedence over constraint name.
1731	$name = $index_name ?? $this->get_table_constraint_name( $node, $table_name );
1732
1733	// Constraint enforcement.
1734	$constraint_enforcement = $node->get_first_descendant_node( 'constraintEnforcement' );
1735	if ( $constraint_enforcement && $constraint_enforcement->has_child_token( WP_MySQL_Lexer::NOT_SYMBOL ) ) {
1736	$enforced = 'NO';
1737	} else {
1738	$enforced = 'YES';
1739	}
1740
1741	return array(
1742	'table_schema' => self::SAVED_DATABASE_NAME,
1743	'table_name' => $table_name,
1744	'constraint_schema' => self::SAVED_DATABASE_NAME,
1745	'constraint_name' => $name,
1746	'constraint_type' => $type,
1747	'enforced' => $enforced,
1748	);
1749	}
1750
1751	/**
1752	* Extract referential constraint data from the "tableConstraintDef" AST node.
1753	*
1754	* @param WP_Parser_Node $node The "tableConstraintDef" AST node.
1755	* @param string $table_name The table name.
1756	* @return array\|null The referential constraint data as stored in information schema.
1757	*/
1758	private function extract_referential_constraint_data( WP_Parser_Node $node, string $table_name ): ?array {
1759	$references = $node->get_first_descendant_node( 'references' );
1760	if ( null === $references ) {
1761	return null;
1762	}
1763
1764	// Referenced table name.
1765	$referenced_table = $references->get_first_child_node( 'tableRef' );
1766	$referenced_table_name = $this->get_table_name_from_node( $referenced_table );
1767
1768	// Referenced column names.
1769	$reference_parts = $references->get_first_child_node( 'identifierListWithParentheses' )
1770	->get_first_child_node( 'identifierList' )
1771	->get_child_nodes( 'identifier' );
1772
1773	// ON UPDATE and ON DELETE both use the "deleteOption" node.
1774	$actions = $this->get_foreign_key_actions( $references );
1775	$on_update = $actions['on_update'];
1776	$on_delete = $actions['on_delete'];
1777
1778	// Find PRIMARY and UNIQUE constraints in the referenced table.
1779	$table_is_temporary = false;
1780	$statistics_table_name = $this->get_table_name( $table_is_temporary, 'statistics' );
1781	$statistics = $this->connection->query(
1782	'
1783	SELECT index_name, column_name
1784	FROM ' . $this->connection->quote_identifier( $statistics_table_name ) . "
1785	WHERE table_schema = ?
1786	AND table_name = ?
1787	AND non_unique = 0
1788	ORDER BY index_name = 'PRIMARY' DESC, index_name, seq_in_index
1789	",
1790	array( self::SAVED_DATABASE_NAME, $referenced_table_name )
1791	)->fetchAll(
1792	PDO::FETCH_ASSOC // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
1793	);
1794
1795	// Group index columns to a map.
1796	$index_columns_map = array();
1797	foreach ( $statistics as $statistics_item ) {
1798	$index_columns_map[ $statistics_item['INDEX_NAME'] ][] = $statistics_item['COLUMN_NAME'];
1799	}
1800
1801	// Find which index includes referenced column names as a prefix.
1802	$unique_constraint_name = null;
1803	foreach ( $index_columns_map as $index_name => $index_columns ) {
1804	$is_prefix = true;
1805	foreach ( $reference_parts as $i => $reference_part ) {
1806	if ( $index_columns[ $i ] !== $this->get_value( $reference_part ) ) {
1807	$is_prefix = false;
1808	break;
1809	}
1810	}
1811	if ( $is_prefix ) {
1812	$unique_constraint_name = $index_name;
1813	break;
1814	}
1815	}
1816
1817	$name = $this->get_table_constraint_name( $node, $table_name );
1818	return array(
1819	'constraint_schema' => self::SAVED_DATABASE_NAME,
1820	'constraint_name' => $name,
1821	'unique_constraint_schema' => self::SAVED_DATABASE_NAME,
1822	'unique_constraint_name' => $unique_constraint_name,
1823	'update_rule' => $on_update,
1824	'delete_rule' => $on_delete,
1825	'table_name' => $table_name,
1826	'referenced_table_name' => $referenced_table_name,
1827	);
1828	}
1829
1830	/**
1831	* Extract key column usage data from the "tableConstraintDef" AST node.
1832	*
1833	* @param WP_Parser_Node $node The "tableConstraintDef" AST node.
1834	* @param string $table_name The table name.
1835	* @param string $index_name The index name, when the constraint uses an index.
1836	* @return array The key column usage data as stored in information schema.
1837	*/
1838	private function extract_key_column_usage_data(
1839	WP_Parser_Node $node,
1840	string $table_name,
1841	?string $index_name = null
1842	): array {
1843	$is_primary = $node->get_first_descendant_token( WP_MySQL_Lexer::PRIMARY_SYMBOL );
1844	$is_unique = $node->get_first_descendant_token( WP_MySQL_Lexer::UNIQUE_SYMBOL );
1845	$references = $node->get_first_descendant_node( 'references' );
1846	if ( null === $references && ! $is_primary && ! $is_unique ) {
1847	return array();
1848	}
1849
1850	// Referenced table name and column names.
1851	if ( $references ) {
1852	$referenced_table = $references->get_first_child_node( 'tableRef' );
1853	$referenced_identifiers = $referenced_table->get_descendant_nodes( 'identifier' );
1854	$referenced_table_schema = count( $referenced_identifiers ) > 1
1855	? $this->get_value( $referenced_identifiers[0] )
1856	: self::SAVED_DATABASE_NAME;
1857	$referenced_table_name = $this->get_table_name_from_node( $referenced_table );
1858	$referenced_columns = $references->get_first_child_node( 'identifierListWithParentheses' )
1859	->get_first_child_node( 'identifierList' )
1860	->get_child_nodes( 'identifier' );
1861	} else {
1862	$referenced_table_schema = null;
1863	$referenced_table_name = null;
1864	$referenced_columns = array();
1865	}
1866
1867	// Constraint name.
1868	$name = $index_name ?? $this->get_table_constraint_name( $node, $table_name );
1869
1870	// Key parts.
1871	if ( 'columnDefinition' === $node->rule_name ) {
1872	$identifiers = $node
1873	->get_first_descendant_node( 'fieldIdentifier' )
1874	->get_descendant_nodes( 'identifier' );
1875	$key_parts = array( end( $identifiers ) );
1876	} else {
1877	$key_parts = array();
1878	foreach ( $node->get_descendant_nodes( 'keyPart' ) as $key_part ) {
1879	$key_parts[] = $key_part->get_first_child_node( 'identifier' );
1880	}
1881	}
1882
1883	$rows = array();
1884	foreach ( $key_parts as $i => $key_part ) {
1885	$column_name = $this->get_value( $key_part );
1886	$position = $i + 1;
1887
1888	$rows[] = array(
1889	'constraint_schema' => self::SAVED_DATABASE_NAME,
1890	'constraint_name' => $name,
1891	'table_schema' => self::SAVED_DATABASE_NAME,
1892	'table_name' => $table_name,
1893	'column_name' => $column_name,
1894	'ordinal_position' => $position,
1895	'position_in_unique_constraint' => $references ? $position : null,
1896	'referenced_table_schema' => $referenced_table_schema,
1897	'referenced_table_name' => $referenced_table_name,
1898	'referenced_column_name' => $referenced_columns ? $this->get_value( $referenced_columns[ $i ] ) : null,
1899	);
1900	}
1901	return $rows;
1902	}
1903
1904	/**
1905	* Extract check constraint data from the "tableConstraintDef" AST node.
1906	*
1907	* @param WP_Parser_Node $node The "tableConstraintDef" AST node.
1908	* @param string $table_name The table name.
1909	* @return array\|null The check constraint data as stored in information schema.
1910	*/
1911	private function extract_check_constraint_data( WP_Parser_Node $node, string $table_name ): ?array {
1912	$check_constraint = $node->get_first_descendant_node( 'checkConstraint' );
1913	if ( null === $check_constraint ) {
1914	return null;
1915	}
1916
1917	$expr = $check_constraint->get_first_child_node( 'exprWithParentheses' );
1918	$check_clause = $this->serialize_mysql_expression( $expr );
1919
1920	return array(
1921	'constraint_schema' => self::SAVED_DATABASE_NAME,
1922	'constraint_name' => $this->get_table_constraint_name( $node, $table_name ),
1923	'check_clause' => $check_clause,
1924	);
1925	}
1926
1927	/**
1928	* Update column info from constraint data in the statistics table.
1929	*
1930	* When constraints are added or removed, we need to reflect the changes
1931	* in the "COLUMN_KEY" and "IS_NULLABLE" columns of the "COLUMNS" table.
1932	*
1933	* A) COLUMN_KEY (priority from 1 to 4):
1934	* 1. "PRI": Column is any component of a PRIMARY KEY.
1935	* 2. "UNI": Column is the first column of a UNIQUE KEY.
1936	* 3. "MUL": Column is the first column of a non-unique index.
1937	* 4. "": Column is not indexed.
1938	*
1939	* B) IS_NULLABLE: In COLUMNS, "YES"/"NO". In STATISTICS, "YES"/"".
1940	*
1941	* @param bool $table_is_temporary Whether the table is temporary.
1942	* @param string $table_name The table name.
1943	*/
1944	private function sync_column_key_info( bool $table_is_temporary, string $table_name ): void {
1945	// @TODO: Consider listing only affected columns.
1946	$columns_table_name = $this->get_table_name( $table_is_temporary, 'columns' );
1947	$statistics_table_name = $this->get_table_name( $table_is_temporary, 'statistics' );
1948	$this->connection->query(
1949	'
1950	UPDATE ' . $this->connection->quote_identifier( $columns_table_name ) . " AS c
1951	SET (column_key, is_nullable) = (
1952	SELECT
1953	CASE
1954	WHEN MAX(s.index_name = 'PRIMARY') THEN 'PRI'
1955	WHEN MAX(s.non_unique = 0 AND s.seq_in_index = 1) THEN 'UNI'
1956	WHEN MAX(s.seq_in_index = 1) THEN 'MUL'
1957	ELSE ''
1958	END,
1959	CASE
1960	WHEN MAX(s.index_name = 'PRIMARY') THEN 'NO'
1961	ELSE c.is_nullable
1962	END
1963	FROM " . $this->connection->quote_identifier( $statistics_table_name ) . ' AS s
1964	WHERE s.table_schema = c.table_schema
1965	AND s.table_name = c.table_name
1966	AND s.column_name = c.column_name
1967	)
1968	WHERE c.table_schema = ?
1969	AND c.table_name = ?
1970	',
1971	array( self::SAVED_DATABASE_NAME, $table_name )
1972	);
1973	}
1974
1975	/**
1976	* Extract table name from one of fully-qualified name AST nodes.
1977	*
1978	* @param WP_Parser_Node $node The AST node. One of "tableName" or "tableRef".
1979	* @return string The table name.
1980	*/
1981	private function get_table_name_from_node( WP_Parser_Node $node ): string {
1982	if ( 'tableRef' === $node->rule_name \|\| 'tableName' === $node->rule_name ) {
1983	$parts = $node->get_descendant_nodes( 'identifier' );
1984	return $this->get_value( end( $parts ) );
1985	}
1986
1987	throw new Exception(
1988	sprintf( 'Could not get table name from node: %s', $node->rule_name )
1989	);
1990	}
1991
1992	/**
1993	* Extract table engine value from the "createStatement" AST node.
1994	*
1995	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
1996	* @return string The table engine as stored in information schema.
1997	*/
1998	private function get_table_engine( WP_Parser_Node $node ): string {
1999	$engine_node = $node->get_first_descendant_node( 'engineRef' );
2000	if ( null === $engine_node ) {
2001	return 'InnoDB';
2002	}
2003
2004	$engine = strtoupper( $this->get_value( $engine_node ) );
2005	if ( 'INNODB' === $engine ) {
2006	return 'InnoDB';
2007	} elseif ( 'MYISAM' === $engine ) {
2008	return 'MyISAM';
2009	}
2010	return $engine;
2011	}
2012
2013	/**
2014	* Extract table collation value from the "createStatement" AST node.
2015	*
2016	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
2017	* @return string The table collation as stored in information schema.
2018	*/
2019	private function get_table_collation( WP_Parser_Node $node ): string {
2020	$collate_node = $node->get_first_descendant_node( 'collationName' );
2021	if ( null === $collate_node ) {
2022	// @TODO: Use default DB collation or DB_CHARSET & DB_COLLATE.
2023	return 'utf8mb4_0900_ai_ci';
2024	}
2025	return strtolower( $this->get_value( $collate_node ) );
2026	}
2027
2028	/**
2029	* Extract table comment from the "createStatement" AST node.
2030	*
2031	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
2032	* @return string The table comment as stored in information schema.
2033	*/
2034	private function get_table_comment( WP_Parser_Node $node ): string {
2035	foreach ( $node->get_descendant_nodes( 'createTableOption' ) as $attr ) {
2036	if ( $attr->has_child_token( WP_MySQL_Lexer::COMMENT_SYMBOL ) ) {
2037	return $this->get_value( $attr->get_first_child_node( 'textStringLiteral' ) );
2038	}
2039	}
2040	return '';
2041	}
2042
2043	/**
2044	* Extract column default value from the "columnDefinition" or "fieldDefinition" AST node.
2045	*
2046	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2047	* @param string $data_type The column data type as stored in information schema.
2048	* @param string $column_name The column name.
2049	* @return string\|null The column default as stored in information schema.
2050	*/
2051	private function get_column_default( WP_Parser_Node $node, string $data_type, string $column_name ): ?string {
2052	$default_attr = null;
2053	foreach ( $node->get_descendant_nodes( 'columnAttribute' ) as $attr ) {
2054	if ( $attr->has_child_token( WP_MySQL_Lexer::DEFAULT_SYMBOL ) ) {
2055	$default_attr = $attr;
2056	}
2057	}
2058
2059	if ( null === $default_attr ) {
2060	return null;
2061	}
2062
2063	/*
2064	* [GRAMMAR]
2065	* DEFAULT_SYMBOL (
2066	* signedLiteral
2067	* \| NOW_SYMBOL timeFunctionParameters?
2068	* \| {serverVersion >= 80013}? exprWithParentheses
2069	* )
2070	*/
2071
2072	// DEFAULT NOW()
2073	if ( $default_attr->has_child_token( WP_MySQL_Lexer::NOW_SYMBOL ) ) {
2074	return 'CURRENT_TIMESTAMP';
2075	}
2076
2077	// DEFAULT signedLiteral
2078	$signed_literal = $default_attr->get_first_child_node( 'signedLiteral' );
2079	if ( $signed_literal ) {
2080	$literal = $signed_literal->get_first_child_node( 'literal' );
2081	if ( null === $literal ) {
2082	// A signed number, such as "-5", has no "literal" child node.
2083	return $this->get_value( $signed_literal );
2084	}
2085	return $this->get_literal_default( $literal, $data_type, $column_name );
2086	}
2087
2088	// DEFAULT (expression) - MySQL 8.0.13+ supports exprWithParentheses
2089	$expr_with_parens = $default_attr->get_first_child_node( 'exprWithParentheses' );
2090	if ( $expr_with_parens ) {
2091	return $this->serialize_mysql_expression( $expr_with_parens );
2092	}
2093
2094	throw new Exception( 'DEFAULT value of this type is not supported.' );
2095	}
2096
2097	/**
2098	* Extract and normalize a literal default value.
2099	*
2100	* @param WP_Parser_Node $literal The "literal" AST node.
2101	* @param string $data_type The column data type as stored in information schema.
2102	* @param string $column_name The column name.
2103	* @return string\|null The default value as stored in information schema.
2104	*/
2105	private function get_literal_default( WP_Parser_Node $literal, string $data_type, string $column_name ): ?string {
2106	// DEFAULT NULL
2107	if ( $literal->has_child_node( 'nullLiteral' ) ) {
2108	return null;
2109	}
2110
2111	// DEFAULT TRUE or DEFAULT FALSE
2112	if ( $literal->has_child_node( 'boolLiteral' ) ) {
2113	$bool_literal = $literal->get_first_child_node( 'boolLiteral' );
2114	$bool_value = $bool_literal->has_child_token( WP_MySQL_Lexer::TRUE_SYMBOL ) ? '1' : '0';
2115	return 'bit' === $data_type ? "b'{$bool_value}'" : $bool_value;
2116	}
2117
2118	$default = $this->get_value( $literal );
2119
2120	if ( 'bit' === $data_type ) {
2121	/*
2122	* @TODO: Validate and normalize defaults from their AST and the full
2123	* column definition before storing them in the information schema.
2124	*/
2125	$bit_default = $this->get_bit_default( $default );
2126	if ( null === $bit_default ) {
2127	throw WP_SQLite_Information_Schema_Exception::invalid_default_value( $column_name );
2128	}
2129	return $bit_default;
2130	}
2131
2132	/*
2133	* @TODO: Non-BIT literal defaults are currently stored verbatim. To
2134	* match MySQL, they should be normalized. For example:
2135	* - BINARY/VARBINARY defaults stored as 0x hex literals.
2136	* - Hex and bit literals coerced to raw bytes for text columns and to
2137	* their decimal value for numeric columns.
2138	* - The charset introducer dropped from text-literal defaults.
2139	* - Numeric defaults normalized, e.g. 1.0 to 1 or 1e3 to 1000.
2140	*/
2141	return $default;
2142	}
2143
2144	/**
2145	* Normalize a BIT default value to a MySQL bit literal.
2146	*
2147	* The value may be a bit literal, a hex literal, or a decimal.
2148	*
2149	* @param string $default_value The BIT default value.
2150	* @return string\|null The default as a bit literal, e.g. "b'101'", or null when not a bit value.
2151	*/
2152	private function get_bit_default( string $default_value ): ?string {
2153	$value = strtolower( $default_value );
2154
2155	// An empty string coerces to zero.
2156	if ( '' === $value ) {
2157	return "b'0'";
2158	}
2159
2160	// Bit literal, e.g. b'101' or 0b101.
2161	if (
2162	preg_match( "/\Ab'([01]*)'\z/", $value, $matches )
2163	\|\| preg_match( '/\A0b([01]+)\z/', $value, $matches )
2164	) {
2165	$bits = ltrim( $matches[1], '0' );
2166	return "b'" . ( '' === $bits ? '0' : $bits ) . "'";
2167	}
2168
2169	// Hex literal, e.g. x'05' or 0x05.
2170	if (
2171	preg_match( "/\Ax'([0-9a-f]*)'\z/", $value, $matches )
2172	\|\| preg_match( '/\A0x([0-9a-f]+)\z/', $value, $matches )
2173	) {
2174	return "b'" . decbin( hexdec( $matches[1] ) ) . "'";
2175	}
2176
2177	// Decimal, e.g. 5, or a numeric string literal such as '0'.
2178	if ( is_numeric( $value ) ) {
2179	return "b'" . decbin( (int) $value ) . "'";
2180	}
2181
2182	return null;
2183	}
2184
2185	/**
2186	* Extract column nullability from the "columnDefinition" or "fieldDefinition" AST node.
2187	*
2188	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2189	* @return string The column nullability as stored in information schema.
2190	*/
2191	private function get_column_nullable( WP_Parser_Node $node ): string {
2192	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
2193	$data_type = $node->get_first_descendant_node( 'dataType' );
2194	if ( null !== $data_type->get_first_descendant_token( WP_MySQL_Lexer::SERIAL_SYMBOL ) ) {
2195	return 'NO';
2196	}
2197
2198	foreach ( $node->get_descendant_nodes( 'columnAttribute' ) as $attr ) {
2199	// PRIMARY KEY columns are always NOT NULL.
2200	if ( $attr->has_child_token( WP_MySQL_Lexer::KEY_SYMBOL ) ) {
2201	return 'NO';
2202	}
2203
2204	// Check for NOT NULL attribute.
2205	if (
2206	$attr->has_child_token( WP_MySQL_Lexer::NOT_SYMBOL )
2207	&& $attr->has_child_node( 'nullLiteral' )
2208	) {
2209	return 'NO';
2210	}
2211	}
2212	return 'YES';
2213	}
2214
2215	/**
2216	* Extract column key info from the "columnDefinition" or "fieldDefinition" AST node.
2217	*
2218	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2219	* @return string The column key info as stored in information schema.
2220	*/
2221	private function get_column_key( WP_Parser_Node $node ): string {
2222	// 1. PRI: Column is a primary key or its any component.
2223	if (
2224	null !== $node->get_first_descendant_token( WP_MySQL_Lexer::KEY_SYMBOL )
2225	) {
2226	return 'PRI';
2227	}
2228
2229	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
2230	$data_type = $node->get_first_descendant_node( 'dataType' );
2231	if ( null !== $data_type->get_first_descendant_token( WP_MySQL_Lexer::SERIAL_SYMBOL ) ) {
2232	return 'PRI';
2233	}
2234
2235	// 2. UNI: Column has UNIQUE constraint.
2236	if ( null !== $node->get_first_descendant_token( WP_MySQL_Lexer::UNIQUE_SYMBOL ) ) {
2237	return 'UNI';
2238	}
2239
2240	// 3. MUL: Column has INDEX.
2241	if ( null !== $node->get_first_descendant_token( WP_MySQL_Lexer::INDEX_SYMBOL ) ) {
2242	return 'MUL';
2243	}
2244
2245	return '';
2246	}
2247
2248	/**
2249	* Extract column extra from the "columnDefinition" or "fieldDefinition" AST node.
2250	*
2251	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2252	* @return string The column extra as stored in information schema.
2253	*/
2254	private function get_column_extra( WP_Parser_Node $node ): string {
2255	$extras = array();
2256	$attributes = $node->get_descendant_nodes( 'columnAttribute' );
2257
2258	// SERIAL
2259	$data_type = $node->get_first_descendant_node( 'dataType' );
2260	if ( null !== $data_type->get_first_descendant_token( WP_MySQL_Lexer::SERIAL_SYMBOL ) ) {
2261	return 'auto_increment';
2262	}
2263
2264	// AUTO_INCREMENT columns can't have a DEFAULT value.
2265	foreach ( $attributes as $attr ) {
2266	if ( $attr->has_child_token( WP_MySQL_Lexer::AUTO_INCREMENT_SYMBOL ) ) {
2267	return 'auto_increment';
2268	}
2269	}
2270
2271	// Check whether DEFAULT value is generated.
2272	foreach ( $attributes as $attr ) {
2273	if (
2274	$attr->has_child_token( WP_MySQL_Lexer::DEFAULT_SYMBOL )
2275	&& (
2276	$attr->has_child_node( 'exprWithParentheses' )
2277	\|\| $attr->has_child_token( WP_MySQL_Lexer::NOW_SYMBOL )
2278	)
2279	) {
2280	$extras[] = 'DEFAULT_GENERATED';
2281	}
2282	}
2283
2284	// Check for ON UPDATE CURRENT_TIMESTAMP.
2285	foreach ( $attributes as $attr ) {
2286	if (
2287	$attr->has_child_token( WP_MySQL_Lexer::ON_SYMBOL )
2288	&& $attr->has_child_token( WP_MySQL_Lexer::UPDATE_SYMBOL )
2289	) {
2290	$extras[] = 'on update CURRENT_TIMESTAMP';
2291	}
2292	}
2293
2294	// Check for generated columns.
2295	if ( $node->get_first_descendant_token( WP_MySQL_Lexer::VIRTUAL_SYMBOL ) ) {
2296	$extras[] = 'VIRTUAL GENERATED';
2297	} elseif ( $node->get_first_descendant_token( WP_MySQL_Lexer::STORED_SYMBOL ) ) {
2298	$extras[] = 'STORED GENERATED';
2299	}
2300	return implode( ' ', $extras );
2301	}
2302
2303	/**
2304	* Extract column comment from the "columnDefinition" or "fieldDefinition" AST node.
2305	*
2306	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2307	* @return string The column comment as stored in information schema.
2308	*/
2309	private function get_column_comment( WP_Parser_Node $node ): string {
2310	foreach ( $node->get_descendant_nodes( 'columnAttribute' ) as $attr ) {
2311	if ( $attr->has_child_token( WP_MySQL_Lexer::COMMENT_SYMBOL ) ) {
2312	return $this->get_value( $attr->get_first_child_node( 'textLiteral' ) );
2313	}
2314	}
2315	return '';
2316	}
2317
2318	/**
2319	* Extract column data type from the "columnDefinition" or "fieldDefinition" AST node.
2320	*
2321	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2322	* @return array{ string, string } The data type and column type as stored in information schema.
2323	*/
2324	private function get_column_data_types( WP_Parser_Node $node ): array {
2325	$type_node = $node->get_first_descendant_node( 'dataType' );
2326	$type = $type_node->get_descendant_tokens();
2327	$token = $type[0];
2328
2329	// Normalize types.
2330	if ( isset( self::TOKEN_TO_TYPE_MAP[ $token->id ] ) ) {
2331	$type = self::TOKEN_TO_TYPE_MAP[ $token->id ];
2332	} elseif (
2333	// VARCHAR/NVARCHAR
2334	// NCHAR/NATIONAL VARCHAR
2335	// CHAR/CHARACTER/NCHAR VARYING
2336	// NATIONAL CHAR/CHARACTER VARYING
2337	WP_MySQL_Lexer::VARCHAR_SYMBOL === $token->id
2338	\|\| WP_MySQL_Lexer::NVARCHAR_SYMBOL === $token->id
2339	\|\| ( isset( $type[1] ) && WP_MySQL_Lexer::VARCHAR_SYMBOL === $type[1]->id )
2340	\|\| ( isset( $type[1] ) && WP_MySQL_Lexer::VARYING_SYMBOL === $type[1]->id )
2341	\|\| ( isset( $type[2] ) && WP_MySQL_Lexer::VARYING_SYMBOL === $type[2]->id )
2342	) {
2343	$type = 'varchar';
2344	} elseif (
2345	// CHAR, NCHAR, NATIONAL CHAR
2346	WP_MySQL_Lexer::CHAR_SYMBOL === $token->id
2347	\|\| WP_MySQL_Lexer::NCHAR_SYMBOL === $token->id
2348	\|\| isset( $type[1] ) && WP_MySQL_Lexer::CHAR_SYMBOL === $type[1]->id
2349	) {
2350	$type = 'char';
2351	} elseif (
2352	// LONG VARBINARY
2353	WP_MySQL_Lexer::LONG_SYMBOL === $token->id
2354	&& isset( $type[1] ) && WP_MySQL_Lexer::VARBINARY_SYMBOL === $type[1]->id
2355	) {
2356	$type = 'mediumblob';
2357	} elseif (
2358	// LONG CHAR/CHARACTER, LONG CHAR/CHARACTER VARYING
2359	WP_MySQL_Lexer::LONG_SYMBOL === $token->id
2360	&& isset( $type[1] ) && WP_MySQL_Lexer::CHAR_SYMBOL === $type[1]->id
2361	) {
2362	$type = 'mediumtext';
2363	} elseif (
2364	// LONG VARCHAR
2365	WP_MySQL_Lexer::LONG_SYMBOL === $token->id
2366	&& isset( $type[1] ) && WP_MySQL_Lexer::VARCHAR_SYMBOL === $type[1]->id
2367	) {
2368	$type = 'mediumtext';
2369	} else {
2370	throw new \RuntimeException( 'Unknown data type: ' . $token->get_value() );
2371	}
2372
2373	// Get full type.
2374	$full_type = $type;
2375	if ( 'enum' === $type \|\| 'set' === $type ) {
2376	$string_list = $type_node->get_first_descendant_node( 'stringList' );
2377	$values = $string_list->get_child_nodes( 'textString' );
2378	foreach ( $values as $i => $value ) {
2379	$values[ $i ] = "'" . str_replace( "'", "''", $this->get_value( $value ) ) . "'";
2380	}
2381	$full_type .= '(' . implode( ',', $values ) . ')';
2382	}
2383
2384	$field_length = $type_node->get_first_descendant_node( 'fieldLength' );
2385	if ( null !== $field_length ) {
2386	if ( 'decimal' === $type \|\| 'float' === $type \|\| 'double' === $type ) {
2387	$full_type .= rtrim( $this->get_value( $field_length ), ')' ) . ',0)';
2388	} else {
2389	$full_type .= $this->get_value( $field_length );
2390	}
2391	/*
2392	* As of MySQL 8.0.17, the display width attribute is deprecated for
2393	* integer types (tinyint, smallint, mediumint, int/integer, bigint)
2394	* and is not stored anymore. However, it may be important for older
2395	* versions and WP's dbDelta, so it is safer to keep it at the moment.
2396	* @TODO: Investigate if it is important to keep this.
2397	*/
2398	}
2399
2400	$precision = $type_node->get_first_descendant_node( 'precision' );
2401	if ( null !== $precision ) {
2402	$full_type .= $this->get_value( $precision );
2403	}
2404
2405	$datetime_precision = $type_node->get_first_descendant_node( 'typeDatetimePrecision' );
2406	if ( null !== $datetime_precision ) {
2407	$full_type .= $this->get_value( $datetime_precision );
2408	}
2409
2410	if (
2411	WP_MySQL_Lexer::BOOL_SYMBOL === $token->id
2412	\|\| WP_MySQL_Lexer::BOOLEAN_SYMBOL === $token->id
2413	) {
2414	$full_type .= '(1)'; // Add length for booleans.
2415	}
2416
2417	if ( null === $field_length && null === $precision ) {
2418	if ( 'decimal' === $type ) {
2419	$full_type .= '(10,0)'; // Add default precision for decimals.
2420	} elseif ( 'char' === $type \|\| 'bit' === $type \|\| 'binary' === $type ) {
2421	$full_type .= '(1)'; // Add default length for char, bit, binary.
2422	}
2423	}
2424
2425	// UNSIGNED.
2426	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
2427	if (
2428	$type_node->get_first_descendant_token( WP_MySQL_Lexer::UNSIGNED_SYMBOL )
2429	\|\| $type_node->get_first_descendant_token( WP_MySQL_Lexer::SERIAL_SYMBOL )
2430	) {
2431	$full_type .= ' unsigned';
2432	}
2433
2434	// ZEROFILL.
2435	if ( $type_node->get_first_descendant_token( WP_MySQL_Lexer::ZEROFILL_SYMBOL ) ) {
2436	$full_type .= ' zerofill';
2437	}
2438
2439	return array( $type, $full_type );
2440	}
2441
2442	/**
2443	* Extract column charset and collation from the "columnDefinition" or "fieldDefinition" AST node.
2444	*
2445	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2446	* @param string $data_type The column data type as stored in information schema.
2447	* @return array{ string\|null, string\|null } The column charset and collation as stored in information schema.
2448	*/
2449	private function get_column_charset_and_collation( WP_Parser_Node $node, string $data_type ): array {
2450	if ( ! (
2451	'char' === $data_type
2452	\|\| 'varchar' === $data_type
2453	\|\| 'tinytext' === $data_type
2454	\|\| 'text' === $data_type
2455	\|\| 'mediumtext' === $data_type
2456	\|\| 'longtext' === $data_type
2457	\|\| 'enum' === $data_type
2458	\|\| 'set' === $data_type
2459	) ) {
2460	return array( null, null );
2461	}
2462
2463	$charset = null;
2464	$collation = null;
2465	$is_binary = false;
2466
2467	// Charset.
2468	$charset_node = $node->get_first_descendant_node( 'charsetWithOptBinary' );
2469	if ( null !== $charset_node ) {
2470	$charset_name_node = $charset_node->get_first_child_node( 'charsetName' );
2471	if ( null !== $charset_name_node ) {
2472	$charset = strtolower( $this->get_value( $charset_name_node ) );
2473	} elseif ( $charset_node->has_child_token( WP_MySQL_Lexer::ASCII_SYMBOL ) ) {
2474	$charset = 'latin1';
2475	} elseif ( $charset_node->has_child_token( WP_MySQL_Lexer::UNICODE_SYMBOL ) ) {
2476	$charset = 'ucs2';
2477	} elseif ( $charset_node->has_child_token( WP_MySQL_Lexer::BYTE_SYMBOL ) ) {
2478	// @TODO: This changes varchar to varbinary.
2479	}
2480
2481	// @TODO: "DEFAULT"
2482
2483	if ( $charset_node->has_child_token( WP_MySQL_Lexer::BINARY_SYMBOL ) ) {
2484	$is_binary = true;
2485	}
2486	} else {
2487	// National charsets (in MySQL, it's "utf8").
2488	$data_type_node = $node->get_first_descendant_node( 'dataType' );
2489	if (
2490	$data_type_node->has_child_node( 'nchar' )
2491	\|\| $data_type_node->has_child_token( WP_MySQL_Lexer::NCHAR_SYMBOL )
2492	\|\| $data_type_node->has_child_token( WP_MySQL_Lexer::NATIONAL_SYMBOL )
2493	\|\| $data_type_node->has_child_token( WP_MySQL_Lexer::NVARCHAR_SYMBOL )
2494	) {
2495	$charset = 'utf8';
2496	}
2497	}
2498
2499	// Normalize charset.
2500	if ( 'utf8mb3' === $charset ) {
2501	$charset = 'utf8';
2502	}
2503
2504	// Collation.
2505	$collation_node = $node->get_first_descendant_node( 'collationName' );
2506	if ( null !== $collation_node ) {
2507	$collation = strtolower( $this->get_value( $collation_node ) );
2508	}
2509
2510	// Defaults.
2511	// @TODO: These are hardcoded now. We should get them from table/DB.
2512	if ( null === $charset && null === $collation ) {
2513	$charset = 'utf8mb4';
2514	// @TODO: "BINARY" (seems to change varchar to varbinary).
2515	// @TODO: "DEFAULT"
2516	}
2517
2518	// If only one of charset/collation is set, the other one is derived.
2519	if ( null === $collation ) {
2520	if ( $is_binary ) {
2521	$collation = $charset . '_bin';
2522	} elseif ( isset( self::CHARSET_DEFAULT_COLLATION_MAP[ $charset ] ) ) {
2523	$collation = self::CHARSET_DEFAULT_COLLATION_MAP[ $charset ];
2524	} else {
2525	$collation = $charset . '_general_ci';
2526	}
2527	} elseif ( null === $charset ) {
2528	$charset = substr( $collation, 0, strpos( $collation, '_' ) );
2529	}
2530
2531	return array( $charset, $collation );
2532	}
2533
2534	/**
2535	* Extract column length info from the "columnDefinition" or "fieldDefinition" AST node.
2536	*
2537	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2538	* @param string $data_type The column data type as stored in information schema.
2539	* @param string\|null $charset The column charset as stored in information schema.
2540	* @return array{ int\|null, int\|null } The column char length and octet length as stored in information schema.
2541	*/
2542	private function get_column_lengths( WP_Parser_Node $node, string $data_type, ?string $charset ): array {
2543	// Text and blob types.
2544	if ( 'tinytext' === $data_type \|\| 'tinyblob' === $data_type ) {
2545	return array( 255, 255 );
2546	} elseif ( 'text' === $data_type \|\| 'blob' === $data_type ) {
2547	return array( 65535, 65535 );
2548	} elseif ( 'mediumtext' === $data_type \|\| 'mediumblob' === $data_type ) {
2549	return array( 16777215, 16777215 );
2550	} elseif ( 'longtext' === $data_type \|\| 'longblob' === $data_type ) {
2551	return array( 4294967295, 4294967295 );
2552	}
2553
2554	// For CHAR, VARCHAR, BINARY, VARBINARY, we need to check the field length.
2555	if (
2556	'char' === $data_type
2557	\|\| 'binary' === $data_type
2558	\|\| 'varchar' === $data_type
2559	\|\| 'varbinary' === $data_type
2560	) {
2561	$field_length = $node->get_first_descendant_node( 'fieldLength' );
2562	if ( null === $field_length ) {
2563	$length = 1;
2564	} else {
2565	$length = (int) trim( $this->get_value( $field_length ), '()' );
2566	}
2567
2568	if ( 'char' === $data_type \|\| 'varchar' === $data_type ) {
2569	$max_bytes_per_char = self::CHARSET_MAX_BYTES_MAP[ $charset ] ?? 1;
2570	return array( $length, $max_bytes_per_char * $length );
2571	} else {
2572	return array( $length, $length );
2573	}
2574	}
2575
2576	// For ENUM and SET, we need to check the longest value.
2577	if ( 'enum' === $data_type \|\| 'set' === $data_type ) {
2578	$string_list = $node->get_first_descendant_node( 'stringList' );
2579	$values = $string_list->get_child_nodes( 'textString' );
2580	$length = 0;
2581	foreach ( $values as $value ) {
2582	if ( 'enum' === $data_type ) {
2583	$length = max( $length, strlen( $this->get_value( $value ) ) );
2584	} else {
2585	$length += strlen( $this->get_value( $value ) );
2586	}
2587	}
2588	if ( 'set' === $data_type ) {
2589	if ( 2 === count( $values ) ) {
2590	$length += 1;
2591	} elseif ( count( $values ) > 2 ) {
2592	$length += 2;
2593	}
2594	}
2595	$max_bytes_per_char = self::CHARSET_MAX_BYTES_MAP[ $charset ] ?? 1;
2596	return array( $length, $max_bytes_per_char * $length );
2597	}
2598
2599	return array( null, null );
2600	}
2601
2602	/**
2603	* Extract column precision and scale from the "columnDefinition" or "fieldDefinition" AST node.
2604	*
2605	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2606	* @param string $data_type The column data type as stored in information schema.
2607	* @return array{ int\|null, int\|null } The column precision and scale as stored in information schema.
2608	*/
2609	private function get_column_numeric_attributes( WP_Parser_Node $node, string $data_type ): array {
2610	if ( 'tinyint' === $data_type ) {
2611	return array( 3, 0 );
2612	} elseif ( 'smallint' === $data_type ) {
2613	return array( 5, 0 );
2614	} elseif ( 'mediumint' === $data_type ) {
2615	return array( 7, 0 );
2616	} elseif ( 'int' === $data_type ) {
2617	return array( 10, 0 );
2618	} elseif ( 'bigint' === $data_type ) {
2619	if ( null !== $node->get_first_descendant_token( WP_MySQL_Lexer::UNSIGNED_SYMBOL ) ) {
2620	return array( 20, 0 );
2621	}
2622
2623	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
2624	$data_type = $node->get_first_descendant_node( 'dataType' );
2625	if ( null !== $data_type->get_first_descendant_token( WP_MySQL_Lexer::SERIAL_SYMBOL ) ) {
2626	return array( 20, 0 );
2627	}
2628
2629	return array( 19, 0 );
2630	}
2631
2632	// For bit columns, we need to check the precision.
2633	if ( 'bit' === $data_type ) {
2634	$field_length = $node->get_first_descendant_node( 'fieldLength' );
2635	if ( null === $field_length ) {
2636	return array( 1, null );
2637	}
2638	return array( (int) trim( $this->get_value( $field_length ), '()' ), null );
2639	}
2640
2641	// For floating point numbers, we need to check the precision and scale.
2642	$precision = null;
2643	$scale = null;
2644	$precision_node = $node->get_first_descendant_node( 'precision' );
2645	if ( null !== $precision_node ) {
2646	$values = $precision_node->get_descendant_tokens( WP_MySQL_Lexer::INT_NUMBER );
2647	$precision = (int) $values[0]->get_value();
2648	$scale = (int) $values[1]->get_value();
2649	}
2650
2651	if ( 'float' === $data_type ) {
2652	return array( $precision ?? 12, $scale );
2653	} elseif ( 'double' === $data_type ) {
2654	return array( $precision ?? 22, $scale );
2655	} elseif ( 'decimal' === $data_type ) {
2656	if ( null === $precision ) {
2657	// Only precision can be specified ("fieldLength" in the grammar).
2658	$field_length = $node->get_first_descendant_node( 'fieldLength' );
2659	if ( null !== $field_length ) {
2660	$precision = (int) trim( $this->get_value( $field_length ), '()' );
2661	}
2662	}
2663	return array( $precision ?? 10, $scale ?? 0 );
2664	}
2665
2666	return array( null, null );
2667	}
2668
2669	/**
2670	* Extract column date/time precision from the "columnDefinition" or "fieldDefinition" AST node.
2671	*
2672	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2673	* @param string $data_type The column data type as stored in information schema.
2674	* @return int\|null The date/time precision as stored in information schema.
2675	*/
2676	private function get_column_datetime_precision( WP_Parser_Node $node, string $data_type ): ?int {
2677	if ( 'time' === $data_type \|\| 'datetime' === $data_type \|\| 'timestamp' === $data_type ) {
2678	$precision = $node->get_first_descendant_node( 'typeDatetimePrecision' );
2679	if ( null === $precision ) {
2680	return 0;
2681	} else {
2682	return (int) $this->get_value( $precision );
2683	}
2684	}
2685	return null;
2686	}
2687
2688	/**
2689	* Extract column generation expression from the "columnDefinition" or "fieldDefinition" AST node.
2690	*
2691	* @param WP_Parser_Node $node The "columnDefinition" or "fieldDefinition" AST node.
2692	* @return string The column generation expression as stored in information schema.
2693	*/
2694	private function get_column_generation_expression( WP_Parser_Node $node ): string {
2695	if ( null !== $node->get_first_descendant_token( WP_MySQL_Lexer::GENERATED_SYMBOL ) ) {
2696	$expr = $node->get_first_descendant_node( 'exprWithParentheses' );
2697	return $this->get_value( $expr );
2698	}
2699	return '';
2700	}
2701
2702	/**
2703	* Extract table constraint name from the "tableConstraintDef" or "columnDefinition" AST node.
2704	*
2705	* @param WP_Parser_Node $node The "tableConstraintDef" or "columnDefinition" AST node.
2706	* @param string $table_name The table name.
2707	* @return string\|null The table constraint name.
2708	*/
2709	public function get_table_constraint_name( WP_Parser_Node $node, string $table_name ): ?string {
2710	$name_node = $node->get_first_child_node( 'constraintName' );
2711	if ( null !== $name_node ) {
2712	return $this->get_value( $name_node->get_first_child_node( 'identifier' ) );
2713	}
2714
2715	$foreign_key = $node->get_first_descendant_node( 'references' );
2716	$check_constraint = $node->get_first_descendant_node( 'checkConstraint' );
2717
2718	// FOREIGN KEY and CHECK constraints without a name get a generated name.
2719	if ( $foreign_key \|\| $check_constraint ) {
2720	$type = $check_constraint ? 'chk' : 'ibfk';
2721
2722	// Get the highest existing name in format "<table_name>_<type>_<number>".
2723	$existing_names = $this->connection->query(
2724	sprintf(
2725	"SELECT DISTINCT constraint_name
2726	FROM %s
2727	WHERE table_schema = ?
2728	AND table_name = ?
2729	AND (constraint_name LIKE ? ESCAPE '\\')",
2730	$this->connection->quote_identifier(
2731	$this->get_table_name(
2732	$this->temporary_table_exists( $table_name ),
2733	'table_constraints'
2734	)
2735	)
2736	),
2737	array(
2738	self::SAVED_DATABASE_NAME,
2739	$table_name,
2740	str_replace( array( '_', '%' ), array( '\\_', '\\%' ), $table_name ) . "\\_{$type}\\_%",
2741	)
2742	)->fetchAll(
2743	PDO::FETCH_COLUMN // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
2744	);
2745
2746	$last_name_index = 0;
2747	foreach ( $existing_names as $existing_name ) {
2748	$parts = explode( '_', $existing_name );
2749	$last_part = end( $parts );
2750	if ( strlen( $last_part ) === strspn( $last_part, '0123456789' ) ) {
2751	$last_name_index = (int) max( $last_name_index, (int) $last_part );
2752	}
2753	}
2754	return $table_name . "_{$type}_" . ( $last_name_index + 1 );
2755	}
2756
2757	return null;
2758	}
2759
2760	/**
2761	* Extract table constraint type from the "tableConstraintDef" or "columnDefinition" AST node.
2762	*
2763	* @param WP_Parser_Node $node The "tableConstraintDef" or "columnDefinition" AST node.
2764	* @return string\|null The table constraint type as stored in information schema.
2765	*/
2766	private function get_table_constraint_type( WP_Parser_Node $node ): ?string {
2767	if ( $node->get_first_descendant_token( WP_MySQL_Lexer::PRIMARY_SYMBOL ) ) {
2768	return 'PRIMARY KEY';
2769	}
2770	if ( $node->get_first_descendant_token( WP_MySQL_Lexer::UNIQUE_SYMBOL ) ) {
2771	return 'UNIQUE';
2772	}
2773	if ( $node->get_first_descendant_node( 'references' ) ) {
2774	return 'FOREIGN KEY';
2775	}
2776	if ( $node->get_first_descendant_node( 'checkConstraint' ) ) {
2777	return 'CHECK';
2778	}
2779	return null;
2780	}
2781
2782	/**
2783	* Extract index name from the "tableConstraintDef" AST node.
2784	*
2785	* @param WP_Parser_Node $node The "tableConstraintDef" or "createIndex" AST node.
2786	* @param string $table_name The table name.
2787	* @return string The index name as stored in information schema.
2788	*/
2789	private function get_index_name( WP_Parser_Node $node, string $table_name ): string {
2790	if ( $node->get_first_descendant_token( WP_MySQL_Lexer::PRIMARY_SYMBOL ) ) {
2791	return 'PRIMARY';
2792	}
2793
2794	/*
2795	* Get index name.
2796	*
2797	* When both index and constraint name are defined, the index name will
2798	* be used. E.g., in "CONSTRAINT c UNIQUE u (id)", the name will be "u".
2799	*/
2800	$name_node = $node->get_first_descendant_node( 'indexName' );
2801	if ( null === $name_node && $node->has_child_node( 'constraintName' ) ) {
2802	$name_node = $node
2803	->get_first_child_node( 'constraintName' )
2804	->get_first_child_node( 'identifier' );
2805	}
2806
2807	if ( null === $name_node ) {
2808	/*
2809	* In MySQL, the default index name equals the first column name.
2810	* If any part is an expression, the name will be "functional_index".
2811	* If the name is already used, we need to append a number.
2812	*/
2813	$subnode = $node->get_first_child_node( 'keyListVariants' )->get_first_child_node();
2814	if ( null !== $subnode->get_first_descendant_node( 'exprWithParentheses' ) ) {
2815	$name = 'functional_index';
2816	} else {
2817	$name = $this->get_value( $subnode->get_first_descendant_node( 'identifier' ) );
2818	}
2819
2820	// Check if the name is already used.
2821	$existing_indices = $this->connection->query(
2822	sprintf(
2823	"SELECT DISTINCT index_name
2824	FROM %s
2825	WHERE table_schema = ?
2826	AND table_name = ?
2827	AND (index_name = ? OR index_name LIKE ? ESCAPE '\\')",
2828	$this->connection->quote_identifier(
2829	$this->get_table_name(
2830	$this->temporary_table_exists( $table_name ),
2831	'statistics'
2832	)
2833	)
2834	),
2835	array(
2836	self::SAVED_DATABASE_NAME,
2837	$table_name,
2838	$name,
2839	str_replace( array( '_', '%' ), array( '\\_', '\\%' ), $name ) . '\\_%',
2840	)
2841	)->fetchAll(
2842	PDO::FETCH_COLUMN // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
2843	);
2844
2845	// The name is not used - we can use it as-is.
2846	if ( count( $existing_indices ) === 0 ) {
2847	return $name;
2848	}
2849
2850	// The name is used - find the first unused name.
2851	$new_name = $name;
2852	$suffix = 2;
2853	while ( in_array( $new_name, $existing_indices, true ) ) {
2854	$new_name = $name . '_' . $suffix;
2855	$suffix += 1;
2856	}
2857	return $new_name;
2858	}
2859	return $this->get_value( $name_node );
2860	}
2861
2862	/**
2863	* Extract index non-unique value from the "tableConstraintDef" AST node.
2864	*
2865	* @param WP_MySQL_Token $token The first constraint keyword.
2866	* @return int The value of non-unique as stored in information schema.
2867	*/
2868	private function get_index_non_unique( WP_MySQL_Token $token ): int {
2869	if (
2870	WP_MySQL_Lexer::PRIMARY_SYMBOL === $token->id
2871	\|\| WP_MySQL_Lexer::UNIQUE_SYMBOL === $token->id
2872	) {
2873	return 0;
2874	}
2875	return 1;
2876	}
2877
2878	/**
2879	* Extract index type from the "tableConstraintDef" AST node.
2880	*
2881	* @param WP_Parser_Node $node The "tableConstraintDef" or "createIndex" AST node.
2882	* @param WP_MySQL_Token $token The first constraint keyword.
2883	* @param bool $has_spatial_column Whether the index contains a spatial column.
2884	* @return string The index type as stored in information schema.
2885	*/
2886	private function get_index_type(
2887	WP_Parser_Node $node,
2888	WP_MySQL_Token $token,
2889	bool $has_spatial_column
2890	): string {
2891	// Handle "USING ..." clause.
2892	$index_type_node = $node->get_first_descendant_node( 'indexType' );
2893	if ( null !== $index_type_node ) {
2894	$index_type = strtoupper( $this->get_value( $index_type_node ) );
2895	if ( 'RTREE' === $index_type ) {
2896	return 'SPATIAL';
2897	} elseif ( 'HASH' === $index_type ) {
2898	// InnoDB uses BTREE even when HASH is specified.
2899	return 'BTREE';
2900	}
2901	return $index_type;
2902	}
2903
2904	// Derive index type from its definition.
2905	if ( WP_MySQL_Lexer::FULLTEXT_SYMBOL === $token->id ) {
2906	return 'FULLTEXT';
2907	} elseif ( WP_MySQL_Lexer::SPATIAL_SYMBOL === $token->id ) {
2908	return 'SPATIAL';
2909	}
2910
2911	// Spatial indexes are also derived from column data type.
2912	if ( $has_spatial_column ) {
2913	return 'SPATIAL';
2914	}
2915
2916	return 'BTREE';
2917	}
2918
2919	/**
2920	* Extract index comment from the "tableConstraintDef" AST node.
2921	*
2922	* @param WP_Parser_Node $node The "tableConstraintDef" or "createIndex" AST node.
2923	* @return string The index comment as stored in information schema.
2924	*/
2925	public function get_index_comment( WP_Parser_Node $node ): string {
2926	foreach ( $node->get_descendant_nodes( 'commonIndexOption' ) as $attr ) {
2927	if ( $attr->has_child_token( WP_MySQL_Lexer::COMMENT_SYMBOL ) ) {
2928	return $this->get_value( $attr->get_first_child_node( 'textLiteral' ) );
2929	}
2930	}
2931	return '';
2932	}
2933
2934	/**
2935	* Extract index column name from the "keyPart" AST node.
2936	*
2937	* @param WP_Parser_Node $node The "keyPart" AST node.
2938	* @return string The index column name as stored in information schema.
2939	*/
2940	private function get_index_column_name( WP_Parser_Node $node ): ?string {
2941	if ( 'keyPart' !== $node->rule_name ) {
2942	return null;
2943	}
2944	return $this->get_value( $node->get_first_descendant_node( 'identifier' ) );
2945	}
2946
2947	/**
2948	* Extract index column name from the "keyPart" AST node.
2949	*
2950	* @param WP_Parser_Node $node The "keyPart" AST node.
2951	* @param string $index_type The index type as stored in information schema.
2952	* @return string The index column name as stored in information schema.
2953	*/
2954	private function get_index_column_collation( WP_Parser_Node $node, string $index_type ): ?string {
2955	if ( 'FULLTEXT' === $index_type ) {
2956	return null;
2957	}
2958
2959	$collate_node = $node->get_first_descendant_node( 'direction' );
2960	if ( null === $collate_node ) {
2961	return 'A';
2962	}
2963	$collate = strtoupper( $this->get_value( $collate_node ) );
2964	return 'DESC' === $collate ? 'D' : 'A';
2965	}
2966
2967	/**
2968	* Extract index column sub-part value from the "keyPart" AST node.
2969	*
2970	* @param WP_Parser_Node $node The "keyPart" AST node.
2971	* @param int\|null $max_length The maximum character length of the index column.
2972	* @param bool $is_spatial Whether the index column is a spatial column.
2973	* @return int\|null The index column sub-part value as stored in information schema.
2974	*/
2975	private function get_index_column_sub_part(
2976	WP_Parser_Node $node,
2977	?int $max_length,
2978	bool $is_spatial
2979	): ?int {
2980	$field_length = $node->get_first_descendant_node( 'fieldLength' );
2981	if ( null === $field_length ) {
2982	if ( $is_spatial ) {
2983	return 32;
2984	}
2985	return null;
2986	}
2987
2988	$value = (int) trim( $this->get_value( $field_length ), '()' );
2989	if ( null !== $max_length && $value >= $max_length ) {
2990	return $max_length;
2991	}
2992	return $value;
2993	}
2994
2995	/**
2996	* Extract foreign key UPDATE and DELETE actions from the "references" AST node.
2997	*
2998	* @param WP_Parser_Node $node The "references" AST node.
2999	* @return array<string, string> The foreign key actions as stored in information schema.
3000	*/
3001	private function get_foreign_key_actions( WP_Parser_Node $node ): array {
3002	$children = $node->get_children();
3003
3004	// ON UPDATE and ON DELETE both use the "deleteOption" node.
3005	$update_option = null;
3006	$delete_option = null;
3007	foreach ( $children as $i => $child ) {
3008	if ( $child instanceof WP_MySQL_Token && WP_MySQL_Lexer::UPDATE_SYMBOL === $child->id ) {
3009	$update_option = $children[ $i + 1 ];
3010	} elseif ( $child instanceof WP_MySQL_Token && WP_MySQL_Lexer::DELETE_SYMBOL === $child->id ) {
3011	$delete_option = $children[ $i + 1 ];
3012	}
3013	}
3014
3015	$result = array(
3016	'on_update' => 'NO ACTION',
3017	'on_delete' => 'NO ACTION',
3018	);
3019	foreach ( array( 'on_update', 'on_delete' ) as $action ) {
3020	$option = 'on_update' === $action ? $update_option : $delete_option;
3021	if ( null === $option ) {
3022	continue;
3023	}
3024
3025	$tokens = $option->get_descendant_tokens();
3026	$token1_id = isset( $tokens[0] ) ? $tokens[0]->id : null;
3027	$token2_id = isset( $tokens[1] ) ? $tokens[1]->id : null;
3028	if ( WP_MySQL_Lexer::NO_SYMBOL === $token1_id ) {
3029	$result[ $action ] = 'NO ACTION';
3030	} elseif ( WP_MySQL_Lexer::RESTRICT_SYMBOL === $token1_id ) {
3031	$result[ $action ] = 'RESTRICT';
3032	} elseif ( WP_MySQL_Lexer::CASCADE_SYMBOL === $token1_id ) {
3033	$result[ $action ] = 'CASCADE';
3034	} elseif ( WP_MySQL_Lexer::SET_SYMBOL === $token1_id && WP_MySQL_Lexer::NULL_SYMBOL === $token2_id ) {
3035	$result[ $action ] = 'SET NULL';
3036	} elseif ( WP_MySQL_Lexer::SET_SYMBOL === $token1_id && WP_MySQL_Lexer::DEFAULT_SYMBOL === $token2_id ) {
3037	$result[ $action ] = 'SET DEFAULT';
3038	} else {
3039	throw new \Exception( sprintf( 'Unsupported foreign key action: %s', $option->get_value() ) );
3040	}
3041	}
3042	return $result;
3043	}
3044
3045	/**
3046	* Determine whether the column data type is a spatial data type.
3047	*
3048	* @param string $data_type The column data type as stored in information schema.
3049	* @return bool Whether the column data type is a spatial data type.
3050	*/
3051	private function is_spatial_data_type( string $data_type ): bool {
3052	return 'geometry' === $data_type
3053	\|\| 'geomcollection' === $data_type
3054	\|\| 'point' === $data_type
3055	\|\| 'multipoint' === $data_type
3056	\|\| 'linestring' === $data_type
3057	\|\| 'multilinestring' === $data_type
3058	\|\| 'polygon' === $data_type
3059	\|\| 'multipolygon' === $data_type;
3060	}
3061
3062	/**
3063	* This is a helper function to get the full unescaped value of a node.
3064	*
3065	* @TODO: This should be done in a more correct way, for names maybe allowing
3066	* descending only a single-child hierarchy, such as these:
3067	* identifier -> pureIdentifier -> IDENTIFIER
3068	* identifier -> pureIdentifier -> BACKTICK_QUOTED_ID
3069	* identifier -> pureIdentifier -> DOUBLE_QUOTED_TEXT
3070	* etc.
3071	*
3072	* For saving "DEFAULT ..." in column definitions, we actually need to
3073	* serialize the whole node, in the case of expressions. This may mean
3074	* implementing an MySQL AST -> string printer.
3075	*
3076	* @param WP_Parser_Node $node The AST node that needs to be serialized.
3077	* @return string The serialized value of the node.
3078	*/
3079	private function get_value( WP_Parser_Node $node ): string {
3080	$full_value = '';
3081	foreach ( $node->get_children() as $child ) {
3082	if ( $child instanceof WP_Parser_Node ) {
3083	$value = $this->get_value( $child );
3084
3085	/*
3086	* At the moment, we only support ASCII bytes in all identifiers.
3087	* This is because SQLite doesn't support case-insensitive Unicode
3088	* character matching: https://sqlite.org/faq.html#q18
3089	*/
3090	if ( 'pureIdentifier' === $child->rule_name ) {
3091	for ( $i = 0; $i < strlen( $value ); $i++ ) {
3092	if ( ord( $value[ $i ] ) > 127 ) {
3093	throw new Exception( 'The SQLite driver only supports ASCII characters in identifiers.' );
3094	}
3095	}
3096	}
3097	} else {
3098	$value = $child->get_value();
3099	}
3100	$full_value .= $value;
3101	}
3102	return $full_value;
3103	}
3104
3105	/**
3106	* Serialize a MySQL expression for storing in the information schema.
3107	*
3108	* This is used for storing DEFAULT and CHECK expressions in the database.
3109	*
3110	* The current implementation is using a naive approach based on directly
3111	* joining the original expression token bytes. This is safe, beacuase the
3112	* original tokens must comprise a valid expression. While functionally
3113	* equivalent, it is not strictly identical to what MySQL stores, because
3114	* MySQL normalizes and prints the expression in a specific format.
3115	*
3116	* TODO: Consider implementing a MySQL expression node -> string formatter
3117	* that would produce results that are identical to MySQL formatting.
3118	* This gets tricky from MySQL 8, where a double-escaping regression
3119	* was introduced, storing strings like "_utf8mb4\'abc\'" instead of
3120	* "_utf8mb4'abc'", but displaying them correctly in SHOW statements.
3121	* @see https://bugs.mysql.com/bug.php?id=100607
3122	*
3123	* @param WP_Parser_Node $node The AST node that needs to be serialized.
3124	* @return string The serialized value of the node.
3125	*/
3126	private function serialize_mysql_expression( WP_Parser_Node $node ): string {
3127	// The wrapping parentheses are generally not stored, although in MySQL,
3128	// this varies by expression type as per the expression formatter logic.
3129	if ( 'exprWithParentheses' === $node->rule_name ) {
3130	return $this->serialize_mysql_expression( $node->get_first_child_node( 'expr' ) );
3131	}
3132
3133	$value = '';
3134	$last_token_id = null;
3135	foreach ( $node->get_descendant_tokens() as $i => $token ) {
3136	// Do not insert whitespace around parentheses. This is primarily to
3137	// avoid inserting whitespace before '(', which may break function
3138	// calls, depending on the value of the "IGNORE_SPACE" SQL mode.
3139	if (
3140	0 === $i
3141	\|\| WP_MySQL_Lexer::OPEN_PAR_SYMBOL === $token->id
3142	\|\| WP_MySQL_Lexer::CLOSE_PAR_SYMBOL === $token->id
3143	\|\| WP_MySQL_Lexer::OPEN_PAR_SYMBOL === $last_token_id
3144	\|\| WP_MySQL_Lexer::CLOSE_PAR_SYMBOL === $last_token_id
3145	) {
3146	$value .= $token->get_bytes();
3147	} else {
3148	$value .= ' ' . $token->get_bytes();
3149	}
3150	$last_token_id = $token->id;
3151	}
3152	return $value;
3153	}
3154
3155	/**
3156	* Insert values into an SQLite table.
3157	*
3158	* @param string $table_name The name of the table.
3159	* @param array<string, string> $data The data to insert (key is column name, value is column value).
3160	*/
3161	private function insert_values( string $table_name, array $data ): void {
3162	$insert_columns = array();
3163	foreach ( $data as $column => $value ) {
3164	$insert_columns[] = $this->connection->quote_identifier( $column );
3165	}
3166
3167	$this->connection->query(
3168	sprintf(
3169	'INSERT INTO %s (%s) VALUES (%s)',
3170	$this->connection->quote_identifier( $table_name ),
3171	implode( ', ', $insert_columns ),
3172	implode( ', ', array_fill( 0, count( $data ), '?' ) )
3173	),
3174	array_values( $data )
3175	);
3176	}
3177
3178	/**
3179	* Update values in an SQLite table.
3180	*
3181	* @param string $table_name The name of the table.
3182	* @param array<string, string> $data The data to update (key is column name, value is column value).
3183	* @param array<string, string> $where The WHERE clause conditions (key is column name, value is column value).
3184	*/
3185	private function update_values( string $table_name, array $data, array $where ): void {
3186	$set_statements = array();
3187	foreach ( $data as $column => $value ) {
3188	$set_statements[] = $this->connection->quote_identifier( $column ) . ' = ?';
3189	}
3190
3191	$where_statements = array();
3192	foreach ( $where as $column => $value ) {
3193	$where_statements[] = $this->connection->quote_identifier( $column ) . ' = ?';
3194	}
3195
3196	$this->connection->query(
3197	sprintf(
3198	'UPDATE %s SET %s WHERE %s',
3199	$this->connection->quote_identifier( $table_name ),
3200	implode( ', ', $set_statements ),
3201	implode( ' AND ', $where_statements )
3202	),
3203	array_merge( array_values( $data ), array_values( $where ) )
3204	);
3205	}
3206
3207	/**
3208	* Delete values from an SQLite table.
3209	*
3210	* @param string $table_name The name of the table.
3211	* @param array<string, string> $where The WHERE clause conditions (key is column name, value is column value).
3212	*/
3213	private function delete_values( string $table_name, array $where ): void {
3214	$where_statements = array();
3215	foreach ( $where as $column => $value ) {
3216	if ( null === $value ) {
3217	$where_statements[] = $this->connection->quote_identifier( $column ) . ' IS NULL';
3218	unset( $where[ $column ] );
3219	} else {
3220	$where_statements[] = $this->connection->quote_identifier( $column ) . ' = ?';
3221	}
3222	}
3223
3224	$this->connection->query(
3225	sprintf(
3226	'DELETE FROM %s WHERE %s',
3227	$this->connection->quote_identifier( $table_name ),
3228	implode( ' AND ', $where_statements )
3229	),
3230	array_values( $where )
3231	);
3232	}
3233	}
3234