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 2.2.22, at wp-includes/database/sqlite/class-wp-sqlite-information-schema-builder.php

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