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

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

3,235 lines 117.8 KB

Raw Download Zip

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