sqlite-database-integration / wp-includes / database / sqlite / class-wp-pdo-mysql-on-sqlite.php

class-wp-pdo-mysql-on-sqlite.php in SQLite Database Integration 2.2.21, at wp-includes/database/sqlite/class-wp-pdo-mysql-on-sqlite.php

6,785 lines 237.8 KB

Raw Download Zip

1	<?php
2
3	/*
4	* The SQLite driver uses PDO. Enable PDO function calls:
5	* phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
6	*/
7
8	/**
9	* SQLite driver for MySQL.
10	*
11	* This class emulates a MySQL database server on top of an SQLite database.
12	* It translates queries written in MySQL SQL dialect to an SQLite SQL dialect,
13	* maintains necessary metadata, and executes the translated queries in SQLite.
14	*
15	* The driver requires PDO with the SQLite driver, and the PCRE engine.
16	*/
17	class WP_PDO_MySQL_On_SQLite extends PDO {
18	/**
19	* The path to the MySQL SQL grammar file.
20	*/
21	const MYSQL_GRAMMAR_PATH = __DIR__ . '/../mysql/mysql-grammar.php';
22
23	/**
24	* The minimum required version of SQLite.
25	*
26	* Currently, we require SQLite >= 3.37.0 due to the STRICT table support:
27	* https://www.sqlite.org/stricttables.html
28	*/
29	const MINIMUM_SQLITE_VERSION = '3.37.0';
30
31	/**
32	* An identifier prefix for internal database objects.
33	*
34	* @TODO: Do not allow accessing objects with this prefix.
35	*/
36	const RESERVED_PREFIX = '_wp_sqlite_';
37
38	/**
39	* The name of a global variables table.
40	*
41	* This special table is used to emulate MySQL global variables and to store
42	* some internal configuration values.
43	*/
44	const GLOBAL_VARIABLES_TABLE_NAME = self::RESERVED_PREFIX . 'global_variables';
45
46	/**
47	* The name of the SQLite driver version variable.
48	*
49	* This internal variable is used to store the latest version of the SQLite
50	* driver that was used to initialize and configure the SQLite database.
51	*/
52	const DRIVER_VERSION_VARIABLE_NAME = self::RESERVED_PREFIX . 'driver_version';
53
54	/**
55	* A map of MySQL tokens to SQLite data types.
56	*
57	* This is used to translate a MySQL data type to an SQLite data type.
58	*/
59	const DATA_TYPE_MAP = array(
60	// Numeric data types:
61	WP_MySQL_Lexer::BIT_SYMBOL => 'INTEGER',
62	WP_MySQL_Lexer::BOOL_SYMBOL => 'INTEGER',
63	WP_MySQL_Lexer::BOOLEAN_SYMBOL => 'INTEGER',
64	WP_MySQL_Lexer::TINYINT_SYMBOL => 'INTEGER',
65	WP_MySQL_Lexer::SMALLINT_SYMBOL => 'INTEGER',
66	WP_MySQL_Lexer::MEDIUMINT_SYMBOL => 'INTEGER',
67	WP_MySQL_Lexer::INT_SYMBOL => 'INTEGER',
68	WP_MySQL_Lexer::INTEGER_SYMBOL => 'INTEGER',
69	WP_MySQL_Lexer::BIGINT_SYMBOL => 'INTEGER',
70	WP_MySQL_Lexer::FLOAT_SYMBOL => 'REAL',
71	WP_MySQL_Lexer::DOUBLE_SYMBOL => 'REAL',
72	WP_MySQL_Lexer::REAL_SYMBOL => 'REAL',
73	WP_MySQL_Lexer::DECIMAL_SYMBOL => 'REAL',
74	WP_MySQL_Lexer::DEC_SYMBOL => 'REAL',
75	WP_MySQL_Lexer::FIXED_SYMBOL => 'REAL',
76	WP_MySQL_Lexer::NUMERIC_SYMBOL => 'REAL',
77
78	// String data types:
79	WP_MySQL_Lexer::CHAR_SYMBOL => 'TEXT',
80	WP_MySQL_Lexer::VARCHAR_SYMBOL => 'TEXT',
81	WP_MySQL_Lexer::NCHAR_SYMBOL => 'TEXT',
82	WP_MySQL_Lexer::NVARCHAR_SYMBOL => 'TEXT',
83	WP_MySQL_Lexer::TINYTEXT_SYMBOL => 'TEXT',
84	WP_MySQL_Lexer::TEXT_SYMBOL => 'TEXT',
85	WP_MySQL_Lexer::MEDIUMTEXT_SYMBOL => 'TEXT',
86	WP_MySQL_Lexer::LONGTEXT_SYMBOL => 'TEXT',
87	WP_MySQL_Lexer::ENUM_SYMBOL => 'TEXT',
88
89	// Date and time data types:
90	WP_MySQL_Lexer::DATE_SYMBOL => 'TEXT',
91	WP_MySQL_Lexer::TIME_SYMBOL => 'TEXT',
92	WP_MySQL_Lexer::DATETIME_SYMBOL => 'TEXT',
93	WP_MySQL_Lexer::TIMESTAMP_SYMBOL => 'TEXT',
94	WP_MySQL_Lexer::YEAR_SYMBOL => 'TEXT',
95
96	// Binary data types:
97	WP_MySQL_Lexer::BINARY_SYMBOL => 'BLOB',
98	WP_MySQL_Lexer::VARBINARY_SYMBOL => 'BLOB',
99	WP_MySQL_Lexer::TINYBLOB_SYMBOL => 'BLOB',
100	WP_MySQL_Lexer::BLOB_SYMBOL => 'BLOB',
101	WP_MySQL_Lexer::MEDIUMBLOB_SYMBOL => 'BLOB',
102	WP_MySQL_Lexer::LONGBLOB_SYMBOL => 'BLOB',
103
104	// Spatial data types:
105	WP_MySQL_Lexer::GEOMETRY_SYMBOL => 'TEXT',
106	WP_MySQL_Lexer::POINT_SYMBOL => 'TEXT',
107	WP_MySQL_Lexer::LINESTRING_SYMBOL => 'TEXT',
108	WP_MySQL_Lexer::POLYGON_SYMBOL => 'TEXT',
109	WP_MySQL_Lexer::MULTIPOINT_SYMBOL => 'TEXT',
110	WP_MySQL_Lexer::MULTILINESTRING_SYMBOL => 'TEXT',
111	WP_MySQL_Lexer::MULTIPOLYGON_SYMBOL => 'TEXT',
112	WP_MySQL_Lexer::GEOMCOLLECTION_SYMBOL => 'TEXT',
113	WP_MySQL_Lexer::GEOMETRYCOLLECTION_SYMBOL => 'TEXT',
114
115	// SERIAL, SET, and JSON types are handled in the translation process.
116	);
117
118	/**
119	* A map of normalized MySQL data types to SQLite data types.
120	*
121	* This is used to generate SQLite CREATE TABLE statements from the MySQL
122	* INFORMATION_SCHEMA tables. They keys are MySQL data types normalized
123	* as they appear in the INFORMATION_SCHEMA. Values are SQLite data types.
124	*/
125	const DATA_TYPE_STRING_MAP = array(
126	// Numeric data types:
127	'bit' => 'INTEGER',
128	'bool' => 'INTEGER',
129	'boolean' => 'INTEGER',
130	'tinyint' => 'INTEGER',
131	'smallint' => 'INTEGER',
132	'mediumint' => 'INTEGER',
133	'int' => 'INTEGER',
134	'integer' => 'INTEGER',
135	'bigint' => 'INTEGER',
136	'float' => 'REAL',
137	'double' => 'REAL',
138	'real' => 'REAL',
139	'decimal' => 'REAL',
140	'dec' => 'REAL',
141	'fixed' => 'REAL',
142	'numeric' => 'REAL',
143
144	// String data types:
145	'char' => 'TEXT',
146	'varchar' => 'TEXT',
147	'nchar' => 'TEXT',
148	'nvarchar' => 'TEXT',
149	'tinytext' => 'TEXT',
150	'text' => 'TEXT',
151	'mediumtext' => 'TEXT',
152	'longtext' => 'TEXT',
153	'enum' => 'TEXT',
154	'set' => 'TEXT',
155	'json' => 'TEXT',
156
157	// Date and time data types:
158	'date' => 'TEXT',
159	'time' => 'TEXT',
160	'datetime' => 'TEXT',
161	'timestamp' => 'TEXT',
162	'year' => 'TEXT',
163
164	// Binary data types:
165	'binary' => 'BLOB',
166	'varbinary' => 'BLOB',
167	'tinyblob' => 'BLOB',
168	'blob' => 'BLOB',
169	'mediumblob' => 'BLOB',
170	'longblob' => 'BLOB',
171
172	// Spatial data types:
173	'geometry' => 'TEXT',
174	'point' => 'TEXT',
175	'linestring' => 'TEXT',
176	'polygon' => 'TEXT',
177	'multipoint' => 'TEXT',
178	'multilinestring' => 'TEXT',
179	'multipolygon' => 'TEXT',
180	'geomcollection' => 'TEXT',
181	'geometrycollection' => 'TEXT',
182	);
183
184	/**
185	* A map of MySQL to SQLite date format translation.
186	*
187	* It maps MySQL DATE_FORMAT() formats to SQLite STRFTIME() formats.
188	*
189	* For MySQL formats, see:
190	* https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
191	*
192	* For SQLite formats, see:
193	* https://www.sqlite.org/lang_datefunc.html
194	* https://strftime.org/
195	*/
196	const MYSQL_DATE_FORMAT_TO_SQLITE_STRFTIME_MAP = array(
197	'%a' => '%D',
198	'%b' => '%M',
199	'%c' => '%n',
200	'%D' => '%jS',
201	'%d' => '%d',
202	'%e' => '%j',
203	'%H' => '%H',
204	'%h' => '%h',
205	'%I' => '%h',
206	'%i' => '%M',
207	'%j' => '%z',
208	'%k' => '%G',
209	'%l' => '%g',
210	'%M' => '%F',
211	'%m' => '%m',
212	'%p' => '%A',
213	'%r' => '%h:%i:%s %A',
214	'%S' => '%s',
215	'%s' => '%s',
216	'%T' => '%H:%i:%s',
217	'%U' => '%W',
218	'%u' => '%W',
219	'%V' => '%W',
220	'%v' => '%W',
221	'%W' => '%l',
222	'%w' => '%w',
223	'%X' => '%Y',
224	'%x' => '%o',
225	'%Y' => '%Y',
226	'%y' => '%y',
227	);
228
229	/**
230	* A map of MySQL data types to implicit default values for non-strict mode.
231	*
232	* In MySQL, when STRICT_TRANS_TABLES and STRICT_ALL_TABLES modes are disabled,
233	* columns get IMPLICIT DEFAULT values that are used under some circumstances.
234	*
235	* See:
236	* https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html#data-type-defaults-implicit
237	*/
238	const DATA_TYPE_IMPLICIT_DEFAULT_MAP = array(
239	// Numeric data types:
240	'bit' => '0',
241	'bool' => '0',
242	'boolean' => '0',
243	'tinyint' => '0',
244	'smallint' => '0',
245	'mediumint' => '0',
246	'int' => '0',
247	'integer' => '0',
248	'bigint' => '0',
249	'float' => '0',
250	'double' => '0',
251	'real' => '0',
252	'decimal' => '0',
253	'dec' => '0',
254	'fixed' => '0',
255	'numeric' => '0',
256
257	// String data types:
258	'char' => '',
259	'varchar' => '',
260	'nchar' => '',
261	'nvarchar' => '',
262	'tinytext' => '',
263	'text' => '',
264	'mediumtext' => '',
265	'longtext' => '',
266	'enum' => '', // TODO: Implement (first enum value).
267	'set' => '',
268	'json' => 'null', // String value 'null' (valid JSON)
269
270	// Date and time data types:
271	'date' => '0000-00-00',
272	'time' => '00:00:00',
273	'datetime' => '0000-00-00 00:00:00',
274	'timestamp' => '0000-00-00 00:00:00',
275	'year' => '0000',
276
277	// Binary data types:
278	'binary' => '',
279	'varbinary' => '',
280	'tinyblob' => '',
281	'blob' => '',
282	'mediumblob' => '',
283	'longblob' => '',
284
285	// Spatial data types (no implicit defaults):
286	'geometry' => null,
287	'point' => null,
288	'linestring' => null,
289	'polygon' => null,
290	'multipoint' => null,
291	'multilinestring' => null,
292	'multipolygon' => null,
293	'geomcollection' => null,
294	'geometrycollection' => null,
295	);
296
297	/**
298	* A map of MySQL column data types to native types in MySQL column meta.
299	*
300	* This maps normalized MySQL column data types (as per information schema)
301	* to MySQL "PDOStatement::getColumnMeta()" data types in the "native_type"
302	* field, as well as the "len" and "precision" fields, where applicable:
303	*
304	* <mysql-column-type> => array( <native_type>, <mysqli_type>, <len>, <precision> )
305	*
306	* This is used to compute the column metadata from the information schema.
307	*/
308	const COLUMN_INFO_MYSQL_TO_NATIVE_TYPES_MAP = array(
309	// Numeric data types:
310	'bit' => array( 'BIT', 16, 1, 0 ),
311	'tinyint' => array( 'TINY', 1, 4, 0 ),
312	'smallint' => array( 'SHORT', 2, 6, 0 ),
313	'mediumint' => array( 'INT24', 9, 9, 0 ),
314	'int' => array( 'LONG', 3, 11, 0 ),
315	'bigint' => array( 'LONGLONG', 8, 20, 0 ),
316	'float' => array( 'FLOAT', 4, 12, 31 ),
317	'double' => array( 'DOUBLE', 5, 22, 31 ),
318	'decimal' => array( 'NEWDECIMAL', 246, null, null ),
319
320	// String data types:
321	'char' => array( 'STRING', 254, null, 0 ),
322	'varchar' => array( 'VAR_STRING', 253, null, 0 ),
323	'tinytext' => array( 'BLOB', 252, null, 0 ),
324	'text' => array( 'BLOB', 252, null, 0 ),
325	'mediumtext' => array( 'BLOB', 252, null, 0 ),
326	'longtext' => array( 'BLOB', 252, null, 0 ),
327	'enum' => array( 'STRING', 254, null, 0 ),
328	'set' => array( 'STRING', 254, null, 0 ),
329	'json' => array( 'BLOB', 245, 4294967295, 0 ),
330
331	// Date and time data types:
332	'date' => array( 'DATE', 10, 10, 0 ),
333	'time' => array( 'TIME', 11, 10, 0 ),
334	'datetime' => array( 'DATETIME', 12, 19, 0 ),
335	'timestamp' => array( 'TIMESTAMP', 7, 19, 0 ),
336	'year' => array( 'YEAR', 13, 4, 0 ),
337
338	// Binary data types:
339	'binary' => array( 'BLOB', 254, null, 0 ),
340	'varbinary' => array( 'BLOB', 253, null, 0 ),
341	'tinyblob' => array( 'BLOB', 252, null, 0 ),
342	'blob' => array( 'BLOB', 252, null, 0 ),
343	'mediumblob' => array( 'BLOB', 252, null, 0 ),
344	'longblob' => array( 'BLOB', 252, null, 0 ),
345
346	// Spatial data types:
347	'geometry' => array( 'GEOMETRY', 255, 4294967295, 0 ),
348	'point' => array( 'GEOMETRY', 255, 4294967295, 0 ),
349	'linestring' => array( 'GEOMETRY', 255, 4294967295, 0 ),
350	'polygon' => array( 'GEOMETRY', 255, 4294967295, 0 ),
351	'multipoint' => array( 'GEOMETRY', 255, 4294967295, 0 ),
352	'multilinestring' => array( 'GEOMETRY', 255, 4294967295, 0 ),
353	'multipolygon' => array( 'GEOMETRY', 255, 4294967295, 0 ),
354	'geomcollection' => array( 'GEOMETRY', 255, 4294967295, 0 ),
355	);
356
357	/**
358	* A map of SQLite column definition data types and SQLite column meta data
359	* types to native types in MySQL column meta.
360	*
361	* This maps both SQLite column definition data types and SQLite column meta
362	* data types (as per "PDOStatement::getColumnMeta()") to MySQL column meta
363	* "native_type" field, as per "PDOStatement::getColumnMeta()", as well as
364	* the "len" and "precision" fields, where applicable:
365	*
366	* <sqlite-column-definition-type> => array( <native_type>, <mysqli_type>, <len>, <precision> )
367	* <sqlite-column-meta-type> => array( <native_type>, <mysqli_type>, <len>, <precision> )
368	*
369	* This is used to compute the MySQL column metadata for non-column fields
370	* that have no records in the information schema (i.e., expressions).
371	*/
372	const COLUMN_INFO_SQLITE_TO_NATIVE_TYPES_MAP = array(
373	'NULL' => array( 'NULL', 6, 0, 0 ),
374	'INT' => array( 'LONGLONG', 8, 21, 0 ),
375	'INTEGER' => array( 'LONGLONG', 8, 21, 0 ),
376	'STRING' => array( 'VAR_STRING', 253, 65535, 31 ),
377	'TEXT' => array( 'BLOB', 252, null, 0 ),
378	'REAL' => array( 'DOUBLE', 5, 22, 31 ),
379	'DOUBLE' => array( 'DOUBLE', 5, 23, 31 ),
380	'BLOB' => array( 'BLOB', 252, null, 0 ),
381	);
382
383	/**
384	* The version of the MySQL server that the driver is configured for.
385	*
386	* @var int
387	*/
388	private $mysql_version;
389
390	/**
391	* The SQLite engine version.
392	*
393	* This is a mysqli-like property that is needed to avoid a PHP warning in
394	* the WordPress health info. The "WP_Debug_Data::get_wp_database()" method
395	* calls "$wpdb->dbh->client_info" - a mysqli-specific abstraction leak.
396	*
397	* @TODO: This should be fixed in WordPress core.
398	*
399	* See:
400	* https://github.com/WordPress/wordpress-develop/blob/bcdca3f9925f1d3eca7b78d231837c0caf0c8c24/src/wp-admin/includes/class-wp-debug-data.php#L1579
401	*
402	* @var string
403	*/
404	public $client_info;
405
406	/**
407	* A MySQL query parser grammar.
408	*
409	* @var WP_Parser_Grammar
410	*/
411	private static $mysql_grammar;
412
413	/**
414	* The main database name.
415	*
416	* The name of the main database that is used by the driver.
417	*
418	* @var string\|null
419	*/
420	private $main_db_name;
421
422	/**
423	* The name of the current database in use.
424	*
425	* This can be set with the USE statement. At the moment, we support only
426	* the main driver database and the INFORMATION_SCHEMA database.
427	*
428	* @var string
429	*/
430	private $db_name;
431
432	/**
433	* An instance of the SQLite connection.
434	*
435	* @var WP_SQLite_Connection
436	*/
437	private $connection;
438
439	/**
440	* A service for managing MySQL INFORMATION_SCHEMA tables in SQLite.
441	*
442	* @var WP_SQLite_Information_Schema_Builder
443	*/
444	private $information_schema_builder;
445
446	/**
447	* Last executed MySQL query.
448	*
449	* @var string
450	*/
451	private $last_mysql_query;
452
453	/**
454	* A list of SQLite queries executed for the last MySQL query.
455	*
456	* @var array{ sql: string, params: array }[]
457	*/
458	private $last_sqlite_queries = array();
459
460	/**
461	* A PDO SQLite statement that represents the result of the last emulated query.
462	*
463	* @var PDOStatement\|null
464	*/
465	private $last_result_statement;
466
467	/**
468	* Override for the number of affected rows by the last emulated query.
469	*
470	* By default, the number of affected rows is carried by the row count value
471	* of "$this->last_result_statement". This property serves as an override for
472	* when the row count of the emulated query and statement don't match.
473	*
474	* @var int\|null
475	*/
476	private $last_affected_rows;
477
478	/**
479	* SQLite column metadata for the last emulated query.
480	*
481	* @var array
482	*/
483	private $last_column_meta = array();
484
485	/**
486	* Data for emulating the "FOUND_ROWS()" function.
487	*
488	* When "SQL_CALC_FOUND_ROWS" is used, the appropriate value is stored here.
489	* Otherwise, it's used to store the last number of found rows, or a query
490	* that returns the rows that need to be counted for usage in "FOUND_ROWS()".
491	*
492	* From MySQL documentation:
493	* In the absence of the SQL_CALC_FOUND_ROWS option in the most recent
494	* successful SELECT statement, FOUND_ROWS() returns the number of rows
495	* in the result set returned by that statement.
496	*
497	* In reality, this applies to SHOW and DESCRIBE statements as well.
498	*
499	* The value can be:
500	* - integer: The number of rows to be directly returned by "FOUND_ROWS()".
501	* - string: A SQLite query whose result set rows need to be counted.
502	* - array: A tuple of a SQLite query and its parameters whose result
503	* set rows need to be counted.
504	*
505	* @var int\|string\|array{0: string, 1: array}
506	*/
507	private $found_rows = 0;
508
509	/**
510	* Whether the current MySQL query is read-only.
511	*
512	* @var bool
513	*/
514	private $is_readonly;
515
516	/**
517	* Type of wrapper transaction that is active for the MySQL query emulation.
518	*
519	* Possible values:
520	* - null: No wrapper transaction is active.
521	* - 'transaction': A top-level transaction is active.
522	* - 'savepoint': A nested savepoint is active.
523	*
524	* @var null\|'transaction'\|'savepoint'
525	*/
526	private $wrapper_transaction_type = null;
527
528	/**
529	* Whether an SQLite transaction is active in the current session.
530	*
531	* This is a polyfill of the "PDO::inTransaction()" method for PHP < 8.4,
532	* where the "PDO::inTransaction()" method is not reliable with SQLite.
533	*
534	* @see https://bugs.php.net/bug.php?id=81227
535	* @see https://github.com/php/php-src/pull/14268
536	*
537	* @var bool
538	*/
539	private $in_transaction = false;
540
541	/**
542	* Whether a MySQL table lock is active.
543	*
544	* Set to "true" when a lock is acquired using the MySQL LOCK statement.
545	* Set to "false" when locks are released using the MySQL UNLOCK statement.
546	*
547	* @var bool
548	*/
549	private $table_lock_active = false;
550
551	/**
552	* The PDO fetch mode used for the emulated query.
553	*
554	* @var mixed
555	*/
556	private $pdo_fetch_mode;
557
558	/**
559	* The currently active MySQL SQL modes.
560	*
561	* The default value reflects the default SQL modes for MySQL 8.0.
562	*
563	* TODO: This may be represented using a temporary table in the future,
564	* together with GLOBAL SQL mode (a non-temporary table).
565	*
566	* @var string[]
567	*/
568	private $active_sql_modes = array(
569	'ERROR_FOR_DIVISION_BY_ZERO',
570	'NO_ENGINE_SUBSTITUTION',
571	'NO_ZERO_DATE',
572	'NO_ZERO_IN_DATE',
573	'ONLY_FULL_GROUP_BY',
574	'STRICT_TRANS_TABLES',
575	);
576
577	/**
578	* A name-to-value map of MySQL system variables for the current session.
579	*
580	* MySQL session system variables are session-specific, so we can store them
581	* in-memory. In SQL queries, they are combined with global system variables.
582	*
583	* See:
584	* https://dev.mysql.com/doc/refman/8.4/en/using-system-variables.html
585	*
586	* @var array<string, string>
587	*/
588	private $session_system_variables = array();
589
590	/**
591	* A name-to-value map of MySQL user variables.
592	*
593	* MySQL user variables are session-specific, so we can store them in-memory.
594	*
595	* See:
596	* https://dev.mysql.com/doc/refman/8.4/en/user-variables.html
597	*
598	* @var array<string, string>
599	*/
600	private $user_variables = array();
601
602	/**
603	* PDO API: Constructor.
604	*
605	* Set up an SQLite connection and the MySQL-on-SQLite driver.
606	*
607	* @param WP_SQLite_Connection $connection A SQLite database connection.
608	* @param string $db_name The database name.
609	*
610	* @throws WP_SQLite_Driver_Exception When the driver initialization fails.
611	*/
612	public function __construct(
613	string $dsn,
614	?string $username = null,
615	?string $password = null,
616	array $options = array()
617	) {
618	// PDO DSN can't include "\0" bytes; parsing stops at the first one.
619	$first_null_byte_index = strpos( $dsn, "\0" );
620	if ( false !== $first_null_byte_index ) {
621	$dsn = substr( $dsn, 0, $first_null_byte_index );
622	}
623
624	// Parse the DSN.
625	$dsn_parts = explode( ':', $dsn, 2 );
626	if ( count( $dsn_parts ) < 2 ) {
627	throw new PDOException( 'invalid data source name' );
628	}
629
630	$driver = $dsn_parts[0];
631	if ( 'mysql-on-sqlite' !== $driver ) {
632	throw new PDOException( 'could not find driver' );
633	}
634
635	// PDO DSN supports semicolon escaping using double semicolon sequences.
636	// Replace ";;" with "\0" to preserve escaped semicolons in "explode()".
637	$args_string = str_replace( ';;', "\0", $dsn_parts[1] );
638	$args = array();
639	foreach ( explode( ';', $args_string ) as $arg ) {
640	// Restore escaped semicolons that were replaced with "\0".
641	$arg = str_replace( "\0", ';', $arg );
642
643	// PDO DSN allows whitespace before argument name. Trim characters
644	// as per the "isspace()" C function (in the default "C" locale).
645	$arg = ltrim( $arg, " \n\r\t\v\f" );
646
647	if ( '' === $arg ) {
648	continue;
649	}
650	$arg_parts = explode( '=', $arg, 2 );
651	$args[ $arg_parts[0] ] = $arg_parts[1] ?? null;
652	}
653
654	$path = $args['path'] ?? ':memory:';
655	$db_name = $args['dbname'] ?? 'sqlite_database';
656
657	// Create a new SQLite connection.
658	if ( isset( $options['pdo'] ) ) {
659	$this->connection = new WP_SQLite_Connection( array( 'pdo' => $options['pdo'] ) );
660	} else {
661	$this->connection = new WP_SQLite_Connection( array( 'path' => $path ) );
662	}
663
664	$this->mysql_version = $options['mysql_version'] ?? 80038;
665	$this->main_db_name = $db_name;
666	$this->db_name = $db_name;
667
668	// Check the database name.
669	if ( '' === $this->db_name ) {
670	throw $this->new_driver_exception( 'The database name cannot be empty.' );
671	}
672
673	// Check the SQLite version.
674	$sqlite_version = $this->get_sqlite_version();
675	if ( version_compare( $sqlite_version, self::MINIMUM_SQLITE_VERSION, '<' ) ) {
676	if ( defined( 'WP_SQLITE_UNSAFE_ENABLE_UNSUPPORTED_VERSIONS' ) && WP_SQLITE_UNSAFE_ENABLE_UNSUPPORTED_VERSIONS ) {
677	// When "WP_SQLITE_UNSAFE_ENABLE_UNSUPPORTED_VERSIONS" is enabled,
678	// allow using legacy SQLite versions, but not older than 3.27.0.
679	if ( version_compare( $sqlite_version, '3.27.0', '<' ) ) {
680	throw $this->new_driver_exception(
681	sprintf(
682	'The SQLite version %s is not supported. Minimum required version is %s.'
683	. ' With "WP_SQLITE_UNSAFE_ENABLE_UNSUPPORTED_VERSIONS" enabled, you must use 3.27.0 or newer.',
684	$sqlite_version,
685	self::MINIMUM_SQLITE_VERSION
686	)
687	);
688	}
689
690	/*
691	* SQLite versions prior to 3.37.0 do not support STRICT tables.
692	*
693	* However, a database created with SQLite >= 3.37.0 can be used
694	* with SQLite versions < 3.37.0 when "PRAGMA writable_schema" is
695	* set to "ON", which also enables error-tolerant schema parsing.
696	*
697	* This is an unsafe opt-in feature for special back compatibility
698	* use cases, as it can corrupt the database by allowing incorrect
699	* types into STRICT tables. Additionally, depending on the legacy
700	* SQLite version used, there is no guarantee that all features of
701	* the SQLite driver will work as expected. Use this with caution.
702	*
703	* See: https://www.sqlite.org/stricttables.html#accessing_strict_tables_in_earlier_versions_of_sqlite
704	*
705	* TODO: Remove this flag when we drop support for PHP 8.0.
706	* From PHP 8.1, SQLite 3.46.1 is used by default.
707	*/
708	$this->execute_sqlite_query( 'PRAGMA writable_schema=ON' );
709	} else {
710	throw $this->new_driver_exception(
711	sprintf(
712	'The SQLite version %s is not supported. Minimum required version is %s.',
713	$sqlite_version,
714	self::MINIMUM_SQLITE_VERSION
715	)
716	);
717	}
718	}
719
720	// Load SQLite version to a property used by WordPress health info.
721	$this->client_info = $sqlite_version;
722
723	// Enable foreign keys. By default, they are off.
724	$this->connection->query( 'PRAGMA foreign_keys = ON' );
725
726	// Register SQLite functions.
727	WP_SQLite_PDO_User_Defined_Functions::register_for( $this->connection->get_pdo() );
728
729	// Load MySQL grammar.
730	if ( null === self::$mysql_grammar ) {
731	self::$mysql_grammar = new WP_Parser_Grammar( require self::MYSQL_GRAMMAR_PATH );
732	}
733
734	// Initialize information schema builder.
735	$this->information_schema_builder = new WP_SQLite_Information_Schema_Builder(
736	self::RESERVED_PREFIX,
737	$this->connection
738	);
739
740	// Ensure that the database is configured.
741	$migrator = new WP_SQLite_Configurator( $this, $this->information_schema_builder );
742	$migrator->ensure_database_configured();
743
744	$this->connection->set_query_logger(
745	function ( string $sql, array $params ) {
746	$this->last_sqlite_queries[] = array(
747	'sql' => $sql,
748	'params' => $params,
749	);
750	}
751	);
752	}
753
754	/**
755	* PDO API: Translate and execute a MySQL query in SQLite.
756	*
757	* A single MySQL query can be translated into zero or more SQLite queries.
758	*
759	* @param string $query Full SQL statement string.
760	* @param int $fetch_mode PDO fetch mode. Default is PDO::FETCH_OBJ.
761	* @param array ...$fetch_mode_args Additional fetch mode arguments.
762	*
763	* @return mixed Return value, depending on the query type.
764	*
765	* @throws WP_SQLite_Driver_Exception When the query execution fails.
766	*/
767	#[ReturnTypeWillChange]
768	public function query( string $query, ?int $fetch_mode = null, ...$fetch_mode_args ) {
769	// Validate and parse the fetch mode and arguments.
770	$arg_count = func_num_args();
771	$arg_colno = 0;
772	$arg_class = null;
773	$arg_constructor_args = array();
774	$arg_into = null;
775
776	$get_type = function ( $value ) {
777	$type = gettype( $value );
778	if ( 'boolean' === $type ) {
779	return 'bool';
780	} elseif ( 'integer' === $type ) {
781	return 'int';
782	} elseif ( 'double' === $type ) {
783	return 'float';
784	}
785	return $type;
786	};
787
788	if ( null === $fetch_mode ) {
789	if ( PHP_VERSION_ID < 80100 && func_num_args() > 1 ) {
790	trigger_error(
791	'PDO::query(): SQLSTATE[HY000]: General error: mode must be an integer',
792	E_USER_WARNING
793	);
794	return false;
795	}
796
797	// When the default FETCH_BOTH is not set explicitly, additional
798	// arguments are ignored, and the argument count is not validated.
799	$fetch_mode = $this->connection->get_pdo()->getAttribute( PDO::ATTR_DEFAULT_FETCH_MODE );
800	$fetch_mode_args = array();
801	} elseif ( PDO::FETCH_COLUMN === $fetch_mode ) {
802	if ( 3 !== $arg_count ) {
803	throw new ArgumentCountError(
804	sprintf( 'PDO::query() expects exactly 3 arguments for the fetch mode provided, %d given', $arg_count )
805	);
806	}
807	if ( ! is_int( $fetch_mode_args[0] ) ) {
808	throw new TypeError(
809	sprintf( 'PDO::query(): Argument #3 must be of type int, %s given', $get_type( $fetch_mode_args[0] ) )
810	);
811	}
812	$arg_colno = $fetch_mode_args[0];
813	} elseif ( PDO::FETCH_CLASS === $fetch_mode ) {
814	if ( $arg_count < 3 ) {
815	throw new ArgumentCountError(
816	sprintf( 'PDO::query() expects at least 3 arguments for the fetch mode provided, %d given', $arg_count )
817	);
818	}
819	if ( $arg_count > 4 ) {
820	throw new ArgumentCountError(
821	sprintf( 'PDO::query() expects at most 4 arguments for the fetch mode provided, %d given', $arg_count )
822	);
823	}
824	if ( ! is_string( $fetch_mode_args[0] ) ) {
825	throw new TypeError(
826	sprintf( 'PDO::query(): Argument #3 must be of type string, %s given', $get_type( $fetch_mode_args[0] ) )
827	);
828	}
829	if ( ! class_exists( $fetch_mode_args[0] ) ) {
830	throw new TypeError( 'PDO::query(): Argument #3 must be a valid class' );
831	}
832	if ( 4 === $arg_count && ! is_array( $fetch_mode_args[1] ) ) {
833	throw new TypeError(
834	sprintf( 'PDO::query(): Argument #4 must be of type ?array, %s given', $get_type( $fetch_mode_args[1] ) )
835	);
836	}
837	$arg_class = $fetch_mode_args[0];
838	$arg_constructor_args = $fetch_mode_args[1] ?? array();
839	} elseif ( PDO::FETCH_INTO === $fetch_mode ) {
840	if ( 3 !== $arg_count ) {
841	throw new ArgumentCountError(
842	sprintf( 'PDO::query() expects exactly 3 arguments for the fetch mode provided, %d given', $arg_count )
843	);
844	}
845	if ( ! is_object( $fetch_mode_args[0] ) ) {
846	throw new TypeError(
847	sprintf( 'PDO::query(): Argument #3 must be of type object, %s given', $get_type( $fetch_mode_args[0] ) )
848	);
849	}
850	$arg_into = $fetch_mode_args[0];
851	} elseif ( $arg_count > 2 ) {
852	throw new ArgumentCountError(
853	sprintf( 'PDO::query() expects exactly 2 arguments for the fetch mode provided, %d given', $arg_count )
854	);
855	}
856
857	$this->flush();
858	$this->last_mysql_query = $query;
859
860	try {
861	// Parse the MySQL query.
862	$parser = $this->create_parser( $query );
863	$parser->next_query();
864	$ast = $parser->get_query_ast();
865	if ( null === $ast ) {
866	throw $this->new_driver_exception( 'Failed to parse the MySQL query.' );
867	}
868
869	if ( $parser->next_query() ) {
870	throw $this->new_driver_exception( 'Multi-query is not supported.' );
871	}
872
873	/*
874	* Determine if we need to wrap the translated queries in a transaction.
875	*
876	* [GRAMMAR]
877	* query:
878	* EOF
879	* \| (simpleStatement \| beginWork) (SEMICOLON_SYMBOL EOF? \| EOF)
880	*/
881	$child_node = $ast->get_first_child_node();
882	if (
883	null === $child_node
884	\|\| 'beginWork' === $child_node->rule_name
885	\|\| $child_node->has_child_node( 'transactionOrLockingStatement' )
886	) {
887	$wrap_in_transaction = false;
888	} else {
889	$wrap_in_transaction = true;
890	}
891
892	if ( $wrap_in_transaction ) {
893	$this->begin_wrapper_transaction();
894	}
895
896	$this->execute_mysql_query( $ast );
897
898	if ( $wrap_in_transaction ) {
899	$this->commit_wrapper_transaction();
900	}
901
902	if ( null === $this->last_result_statement ) {
903	$this->last_result_statement = $this->create_result_statement_from_data( array(), array() );
904	}
905
906	$stmt = new WP_PDO_Proxy_Statement( $this->last_result_statement, $this->last_affected_rows );
907	$stmt->setFetchMode( $fetch_mode, ...$fetch_mode_args );
908	return $stmt;
909	} catch ( Throwable $e ) {
910	try {
911	$this->rollback_user_transaction();
912	$this->table_lock_active = false;
913	} catch ( Throwable $rollback_exception ) {
914	// Ignore rollback errors.
915	}
916	if ( $e instanceof WP_SQLite_Driver_Exception ) {
917	throw $e;
918	} elseif ( $e instanceof WP_SQLite_Information_Schema_Exception ) {
919	throw $this->convert_information_schema_exception( $e );
920	}
921	throw $this->new_driver_exception( $e->getMessage(), $e->getCode(), $e );
922	} finally {
923	// A query that doesn't return any rows or fails sets found rows to 0.
924	if ( ! $this->is_readonly \|\| isset( $e ) ) {
925	$this->found_rows = 0;
926	}
927	}
928	}
929
930	/**
931	* PDO API: Execute a MySQL statement and return the number of affected rows.
932	*
933	* @return int\|false The number of affected rows or false on failure.
934	*/
935	#[ReturnTypeWillChange]
936	public function exec( $query ) {
937	$stmt = $this->query( $query );
938	return $stmt->rowCount();
939	}
940
941	/**
942	* PDO API: Begin a transaction.
943	*
944	* @return bool True on success, false on failure.
945	*/
946	// phpcs:ignore WordPress.NamingConventions.ValidFunctionName.MethodNameInvalid
947	public function beginTransaction(): bool {
948	if ( $this->inTransaction() ) {
949	throw $this->new_driver_exception( 'There is already an active transaction' );
950	}
951	$this->begin_user_transaction();
952	return true;
953	}
954
955	/**
956	* A temporary alias for back compatibility.
957	*
958	* @see self::beginTransaction()
959	*/
960	public function begin_transaction(): void {
961	$this->beginTransaction();
962	}
963
964	/**
965	* PDO API: Commit a transaction.
966	*
967	* @return bool True on success, false on failure.
968	*/
969	public function commit(): bool {
970	if ( ! $this->inTransaction() ) {
971	throw $this->new_driver_exception( 'There is no active transaction' );
972	}
973	$this->commit_user_transaction();
974	return true;
975	}
976
977	/**
978	* PDO API: Rollback a transaction.
979	*
980	* @return bool True on success, false on failure.
981	*/
982	// phpcs:ignore WordPress.NamingConventions.ValidFunctionName.MethodNameInvalid
983	public function rollBack(): bool {
984	if ( ! $this->inTransaction() ) {
985	throw $this->new_driver_exception( 'There is no active transaction' );
986	}
987	$this->rollback_user_transaction();
988	return true;
989	}
990
991	/**
992	* PDO API: Check if a transaction is active.
993	*
994	* @return bool True if a transaction is active, false otherwise.
995	*/
996	// phpcs:ignore WordPress.NamingConventions.ValidFunctionName.MethodNameInvalid
997	public function inTransaction(): bool {
998	if ( PHP_VERSION_ID < 80400 ) {
999	/*
1000	* On PHP < 8.4, the "PDO::inTransaction()" method is not reliable.
1001	*
1002	* @see https://bugs.php.net/bug.php?id=81227
1003	* @see https://github.com/php/php-src/pull/14268
1004	*/
1005	return $this->in_transaction;
1006	}
1007	return $this->connection->get_pdo()->inTransaction();
1008	}
1009
1010	/**
1011	* PDO API: Set a PDO attribute.
1012	*
1013	* TODO: Evaluate whether we should pass all PDO attributes to the PDO SQLite
1014	* instance, or whether some of them require special handling.
1015	* See: https://github.com/php/php-src/blob/b391c28f903536e3bc6a0021ae0976ddbc2745f8/ext/pdo/php_pdo_driver.h#L103
1016	*
1017	* @param int $attribute The attribute to set.
1018	* @param mixed $value The value of the attribute.
1019	* @return bool True on success, false on failure.
1020	*/
1021	public function setAttribute( $attribute, $value ): bool {
1022	return $this->connection->get_pdo()->setAttribute( $attribute, $value );
1023	}
1024
1025	/**
1026	* PDO API: Get a PDO attribute.
1027	*
1028	* TODO: Evaluate whether we should get all PDO attributes from the PDO SQLite
1029	* instance, or whether some of them require special handling.
1030	* See: https://github.com/php/php-src/blob/b391c28f903536e3bc6a0021ae0976ddbc2745f8/ext/pdo/php_pdo_driver.h#L103
1031	*
1032	* @param int $attribute The attribute to get.
1033	* @return mixed The value of the attribute.
1034	*/
1035	#[ReturnTypeWillChange]
1036	public function getAttribute( $attribute ) {
1037	return $this->connection->get_pdo()->getAttribute( $attribute );
1038	}
1039
1040	/**
1041	* Get the SQLite connection instance.
1042	*
1043	* @return WP_SQLite_Connection
1044	*/
1045	public function get_connection(): WP_SQLite_Connection {
1046	return $this->connection;
1047	}
1048
1049	/**
1050	* Get the version of the SQLite engine.
1051	*
1052	* @return string SQLite engine version as a string.
1053	*/
1054	public function get_sqlite_version(): string {
1055	return $this->connection->get_pdo()->getAttribute( PDO::ATTR_SERVER_VERSION );
1056	}
1057
1058	/**
1059	* Get the SQLite driver version saved in the database.
1060	*
1061	* The saved driver version corresponds to the latest version of the SQLite
1062	* driver that was used to initialize and configure the SQLite database.
1063	*
1064	* @return string SQLite driver version as a string.
1065	* @throws PDOException When the query execution fails.
1066	*/
1067	public function get_saved_driver_version(): string {
1068	$default_version = '0.0.0';
1069	try {
1070	$stmt = $this->execute_sqlite_query(
1071	sprintf(
1072	'SELECT value FROM %s WHERE name = ?',
1073	$this->quote_sqlite_identifier( self::GLOBAL_VARIABLES_TABLE_NAME )
1074	),
1075	array( self::DRIVER_VERSION_VARIABLE_NAME )
1076	);
1077	return $stmt->fetchColumn() ?? $default_version;
1078	} catch ( PDOException $e ) {
1079	if ( str_contains( $e->getMessage(), 'no such table' ) ) {
1080	return $default_version;
1081	}
1082	throw $e;
1083	}
1084	}
1085
1086	/**
1087	* Check if a specific SQL mode is active.
1088	*
1089	* @param string $mode The SQL mode to check.
1090	* @return bool True if the SQL mode is active, false otherwise.
1091	*/
1092	public function is_sql_mode_active( string $mode ): bool {
1093	return in_array( strtoupper( $mode ), $this->active_sql_modes, true );
1094	}
1095
1096	/**
1097	* Get the last executed MySQL query.
1098	*
1099	* @return string\|null
1100	*/
1101	public function get_last_mysql_query(): ?string {
1102	return $this->last_mysql_query;
1103	}
1104
1105	/**
1106	* Get SQLite queries executed for the last MySQL query.
1107	*
1108	* @return array{ sql: string, params: array }[]
1109	*/
1110	public function get_last_sqlite_queries(): array {
1111	return $this->last_sqlite_queries;
1112	}
1113
1114	/**
1115	* Get the auto-increment value generated for the last query.
1116	*
1117	* @return int\|string
1118	*/
1119	public function get_insert_id() {
1120	$last_insert_id = $this->connection->get_last_insert_id();
1121	if ( is_numeric( $last_insert_id ) ) {
1122	$last_insert_id = (int) $last_insert_id;
1123	}
1124	return $last_insert_id;
1125	}
1126
1127	/**
1128	* Tokenize a MySQL query and initialize a parser.
1129	*
1130	* @param string $query The MySQL query to parse.
1131	* @return WP_MySQL_Parser A parser initialized for the MySQL query.
1132	*/
1133	public function create_parser( string $query ): WP_MySQL_Parser {
1134	$lexer = new WP_MySQL_Lexer(
1135	$query,
1136	80038,
1137	$this->active_sql_modes
1138	);
1139	$tokens = $lexer->remaining_tokens();
1140	return new WP_MySQL_Parser( self::$mysql_grammar, $tokens );
1141	}
1142
1143	/**
1144	* Get the number of columns returned by the last emulated query.
1145	*
1146	* @return int
1147	*/
1148	public function get_last_column_count(): int {
1149	return count( $this->last_column_meta );
1150	}
1151
1152	/**
1153	* Get column metadata for results of the last emulated query.
1154	*
1155	* @return array
1156	*/
1157	public function get_last_column_meta(): array {
1158	// Build the column metadata as per "PDOStatement::getColumnMeta()".
1159	$column_meta = array();
1160	foreach ( $this->last_column_meta as $meta ) {
1161	$table = $meta['table'] ?? null;
1162	$name = $meta['name'];
1163	$type = strtoupper( $meta['sqlite:decl_type'] ?? $meta['native_type'] ?? '' );
1164
1165	// When table is known, we can get data from the information schema.
1166	$column_info = null;
1167	if ( null !== $table ) {
1168	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table );
1169	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
1170	$column_info = $this->execute_sqlite_query(
1171	sprintf(
1172	'
1173	SELECT
1174	IS_NULLABLE,
1175	DATA_TYPE,
1176	COLUMN_TYPE,
1177	COLUMN_KEY,
1178	CHARACTER_MAXIMUM_LENGTH,
1179	NUMERIC_PRECISION,
1180	NUMERIC_SCALE
1181	FROM %s
1182	WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
1183	',
1184	$this->quote_sqlite_identifier( $columns_table )
1185	),
1186	array( $this->get_saved_db_name(), $table, $name )
1187	)->fetch( PDO::FETCH_ASSOC );
1188
1189	if ( false === $column_info ) {
1190	$column_info = null;
1191	}
1192	}
1193
1194	// If we have information schema data, we can use it.
1195	if ( null !== $column_info ) {
1196	$type_info = self::COLUMN_INFO_MYSQL_TO_NATIVE_TYPES_MAP[ $column_info['DATA_TYPE'] ] ?? null;
1197	if ( null === $type_info ) {
1198	$type_info = self::COLUMN_INFO_SQLITE_TO_NATIVE_TYPES_MAP[ $type ] ?? null;
1199	}
1200	$native_type = $type_info[0];
1201	$mysqli_type = $type_info[1];
1202	$len = $type_info[2];
1203	$precision = $type_info[3];
1204
1205	if ( 'tinyint(1)' === $column_info['COLUMN_TYPE'] ) {
1206	$len = 1;
1207	}
1208
1209	if ( 'decimal' === $column_info['DATA_TYPE'] ) {
1210	$len = (int) $column_info['NUMERIC_PRECISION'] + (int) $column_info['NUMERIC_SCALE'];
1211	$precision = (int) $column_info['NUMERIC_SCALE'];
1212	}
1213
1214	if (
1215	str_contains( $column_info['COLUMN_TYPE'], 'unsigned' )
1216	&& ! str_contains( $column_info['COLUMN_TYPE'], 'bigint' )
1217	) {
1218	$len -= 1;
1219	}
1220
1221	// If set, lenght can be taken from the information schema.
1222	if ( isset( $column_info['CHARACTER_MAXIMUM_LENGTH'] ) ) {
1223	$len = (int) $column_info['CHARACTER_MAXIMUM_LENGTH'];
1224	}
1225
1226	// For string types, the length is multiplied by the maximum number
1227	// of bytes per character for the used connection encoding. In our
1228	// case, it's always "utf8mb4" and therefore 4 bytes per character.
1229	if (
1230	str_contains( $column_info['DATA_TYPE'], 'text' )
1231	\|\| str_contains( $column_info['DATA_TYPE'], 'char' )
1232	\|\| 'enum' === $column_info['DATA_TYPE']
1233	\|\| 'set' === $column_info['DATA_TYPE']
1234	) {
1235	// Except for "longtext" - this might be a MySQL bug.
1236	if ( 'longtext' !== $column_info['DATA_TYPE'] ) {
1237	$len = 4 * $len;
1238	}
1239	}
1240
1241	// Flags.
1242	$flags = array();
1243	if ( 'NO' === $column_info['IS_NULLABLE'] ) {
1244	$flags[] = 'not_null';
1245	}
1246	if ( 'PRI' === $column_info['COLUMN_KEY'] ) {
1247	$flags[] = 'primary_key';
1248	} elseif ( 'UNI' === $column_info['COLUMN_KEY'] ) {
1249	$flags[] = 'unique_key';
1250	} elseif ( 'MUL' === $column_info['COLUMN_KEY'] ) {
1251	$flags[] = 'multiple_key';
1252	}
1253	} else {
1254	$type_info = self::COLUMN_INFO_SQLITE_TO_NATIVE_TYPES_MAP[ $type ];
1255	$native_type = $type_info[0];
1256	$mysqli_type = $type_info[1];
1257	$len = $type_info[2] ?? 0;
1258	$precision = $type_info[3];
1259
1260	// Flags.
1261	$flags = array();
1262	if ( 'NULL' !== $type ) {
1263	$flags[] = 'not_null';
1264	}
1265	}
1266
1267	if ( 'BLOB' === $native_type \|\| 'GEOMETRY' === $native_type ) {
1268	$flags[] = 'blob';
1269	}
1270
1271	// PDO type.
1272	if ( 'INT' === $type \|\| 'INTEGER' === $type ) {
1273	$pdo_type = PDO::PARAM_INT;
1274	} else {
1275	$pdo_type = PDO::PARAM_STR;
1276	}
1277
1278	// MySQLi charset number.
1279	$is_string = 'STRING' === $type \|\| 'TEXT' === $type;
1280	$is_binary = 'BLOB' === $type \|\| 'GEOMETRY' === $native_type;
1281	$is_datetime = str_contains( $native_type, 'DATE' ) \|\| str_contains( $native_type, 'TIME' ) \|\| 'YEAR' === $native_type;
1282	if ( $is_string && ! $is_binary && ! $is_datetime ) {
1283	$mysqli_charsetnr = 255; // utf8mb4_0900_ai_ci
1284	} else {
1285	$mysqli_charsetnr = 63; // binary
1286	}
1287
1288	$column_meta[] = array(
1289	'native_type' => $native_type,
1290	'pdo_type' => $pdo_type,
1291	'flags' => $flags,
1292	'table' => $meta['table'] ?? '',
1293	'name' => $meta['name'],
1294	'len' => $len,
1295	'precision' => $precision,
1296	'sqlite:decl_type' => $meta['sqlite:decl_type'] ?? '',
1297
1298	/*
1299	* The MySQLi PHP extension exposes more MySQL column metadata than PDO.
1300	* We'll add the data here for use cases such as "wpdb::get_col_info()".
1301	*/
1302	'mysqli:orgname' => $meta['name'], // TODO: Use correct original name when alias is used.
1303	'mysqli:orgtable' => $meta['table'] ?? '', // TODO: Use correct original name when table alias is used.
1304	'mysqli:db' => $this->db_name, // TODO: Use correct DB for queries to information schema.
1305	'mysqli:charsetnr' => $mysqli_charsetnr,
1306	'mysqli:flags' => 0, // TODO: We can compute correct MySQL flags.
1307	'mysqli:type' => $mysqli_type,
1308	);
1309	}
1310	return $column_meta;
1311	}
1312
1313	/**
1314	* Execute a query in SQLite.
1315	*
1316	* @param string $sql The query to execute.
1317	* @param array $params The query parameters.
1318	* @throws PDOException When the query execution fails.
1319	* @return PDOStatement The PDO statement object.
1320	*/
1321	public function execute_sqlite_query( string $sql, array $params = array() ): PDOStatement {
1322	return $this->connection->query( $sql, $params );
1323	}
1324
1325	/**
1326	* Translate and execute a MySQL query in SQLite.
1327	*
1328	* @param WP_Parser_Node $node The "query" AST node with "simpleStatement" child.
1329	* @throws WP_SQLite_Driver_Exception When the query is not supported.
1330	*/
1331	private function execute_mysql_query( WP_Parser_Node $node ): void {
1332	if ( 'query' !== $node->rule_name ) {
1333	throw $this->new_driver_exception(
1334	sprintf( 'Expected "query" node, got: "%s"', $node->rule_name )
1335	);
1336	}
1337
1338	/*
1339	* [GRAMMAR]
1340	* query:
1341	* EOF
1342	* \| (simpleStatement \| beginWork) (SEMICOLON_SYMBOL EOF? \| EOF)
1343	*/
1344	$children = $node->get_child_nodes();
1345	if ( count( $children ) !== 1 ) {
1346	throw $this->new_driver_exception(
1347	sprintf( 'Expected 1 child node, got: %d', count( $children ) )
1348	);
1349	}
1350
1351	if ( 'beginWork' === $children[0]->rule_name ) {
1352	$this->begin_user_transaction();
1353	return;
1354	}
1355
1356	if ( 'simpleStatement' !== $children[0]->rule_name ) {
1357	throw $this->new_driver_exception(
1358	sprintf( 'Expected "simpleStatement" node, got: "%s"', $children[0]->rule_name )
1359	);
1360	}
1361
1362	// Process the "simpleStatement" AST node.
1363	$node = $children[0]->get_first_child_node();
1364	switch ( $node->rule_name ) {
1365	case 'transactionOrLockingStatement':
1366	$this->execute_transaction_or_locking_statement( $node );
1367	break;
1368	case 'selectStatement':
1369	$this->is_readonly = true;
1370	$this->execute_select_statement( $node );
1371	break;
1372	case 'insertStatement':
1373	case 'replaceStatement':
1374	$this->execute_insert_or_replace_statement( $node );
1375	break;
1376	case 'updateStatement':
1377	$this->execute_update_statement( $node );
1378	break;
1379	case 'deleteStatement':
1380	$this->execute_delete_statement( $node );
1381	break;
1382	case 'createStatement':
1383	$subtree = $node->get_first_child_node();
1384	switch ( $subtree->rule_name ) {
1385	case 'createDatabase':
1386	/*
1387	* TODO:
1388	* We could support this by creating a new SQLite database
1389	* file (e.g., $slugified_db_name.sqlite).
1390	*
1391	* Alternatively, it could be a no-op, in combination with
1392	* DROP DATABASE deleting the data file and recreating it.
1393	*/
1394	case 'createTable':
1395	$this->execute_create_table_statement( $node );
1396	break;
1397	case 'createIndex':
1398	$this->execute_create_index_statement( $node );
1399	break;
1400	default:
1401	throw $this->new_not_supported_exception(
1402	sprintf(
1403	'statement type: "%s" > "%s"',
1404	$node->rule_name,
1405	$subtree->rule_name
1406	)
1407	);
1408	}
1409	break;
1410	case 'alterStatement':
1411	$subtree = $node->get_first_child_node();
1412	switch ( $subtree->rule_name ) {
1413	case 'alterTable':
1414	$this->execute_alter_table_statement( $node );
1415	break;
1416	default:
1417	throw $this->new_not_supported_exception(
1418	sprintf(
1419	'statement type: "%s" > "%s"',
1420	$node->rule_name,
1421	$subtree->rule_name
1422	)
1423	);
1424	}
1425	break;
1426	case 'dropStatement':
1427	$subtree = $node->get_first_child_node();
1428	switch ( $subtree->rule_name ) {
1429	case 'dropTable':
1430	$this->execute_drop_table_statement( $node );
1431	break;
1432	case 'dropIndex':
1433	$this->execute_drop_index_statement( $node );
1434	break;
1435	default:
1436	$query = $this->translate( $node );
1437	$this->last_result_statement = $this->execute_sqlite_query( $query );
1438	}
1439	break;
1440	case 'truncateTableStatement':
1441	$this->execute_truncate_table_statement( $node );
1442	break;
1443	case 'setStatement':
1444	$this->execute_set_statement( $node );
1445	break;
1446	case 'showStatement':
1447	$this->is_readonly = true;
1448	$this->execute_show_statement( $node );
1449	break;
1450	case 'utilityStatement':
1451	$subtree = $node->get_first_child_node();
1452	switch ( $subtree->rule_name ) {
1453	case 'describeStatement':
1454	$this->is_readonly = true;
1455	$this->execute_describe_statement( $subtree );
1456	break;
1457	case 'useCommand':
1458	$this->execute_use_statement( $subtree );
1459	break;
1460	default:
1461	throw $this->new_not_supported_exception(
1462	sprintf(
1463	'statement type: "%s" > "%s"',
1464	$node->rule_name,
1465	$subtree->rule_name
1466	)
1467	);
1468	}
1469	break;
1470	case 'tableAdministrationStatement':
1471	$this->execute_administration_statement( $node );
1472	break;
1473	default:
1474	throw $this->new_not_supported_exception(
1475	sprintf( 'statement type: "%s"', $node->rule_name )
1476	);
1477	}
1478	}
1479
1480	/**
1481	* Begin a wrapper transaction.
1482	*
1483	* A wrapper transaction is used to ensure consistency by encapsulating SQLite
1484	* statements that are executed during a single MySQL query emulation process.
1485	*
1486	* TOP-LEVEL TRANSACTION vs. SAVEPOINT:
1487	*
1488	* When no transaction is active, we can use a top-level TRANSACTION to wrap
1489	* the emulated MySQL statement. However, if a transaction is already active,
1490	* we must use a SAVEPOINT, as SQLite doesn't support transaction nesting.
1491	*
1492	* BEGIN vs. BEGIN IMMEDIATE:
1493	*
1494	* When we're executing a statement that will need to write to the database,
1495	* we must use "BEGIN IMMEDIATE" to immediately open a write transaction.
1496	*
1497	* This is needed to avoid the "database is locked" error (SQLITE_BUSY) when
1498	* SQLite can't upgrade a read transaction to a write transaction, because
1499	* another connection is already modifying the database.
1500	*
1501	* From the SQLite documentation:
1502	*
1503	* ## Read transactions versus write transactions
1504	*
1505	* If a write statement occurs while a read transaction is active,
1506	* then the read transaction is upgraded to a write transaction if
1507	* possible. If some other database connection has already modified
1508	* the database or is already in the process of modifying the database,
1509	* then upgrading to a write transaction is not possible and the write
1510	* statement will fail with SQLITE_BUSY.
1511	*
1512	* ## DEFERRED, IMMEDIATE, and EXCLUSIVE transactions
1513	*
1514	* Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. The default
1515	* transaction behavior is DEFERRED.
1516	*
1517	* DEFERRED means that the transaction does not actually start until
1518	* the database is first accessed.
1519	*
1520	* IMMEDIATE causes the database connection to start a new write
1521	* immediately, without waiting for a write statement. The BEGIN
1522	* IMMEDIATE might fail with SQLITE_BUSY if another write transaction
1523	* is already active on another database connection.
1524	*
1525	* See:
1526	* - https://www.sqlite.org/lang_transaction.html
1527	* - https://www.sqlite.org/rescode.html#busy
1528	*
1529	* For better performance, we could also consider opening the write
1530	* transaction later in the session - just before the first write.
1531	*/
1532	private function begin_wrapper_transaction(): void {
1533	if ( null !== $this->wrapper_transaction_type ) {
1534	return;
1535	}
1536
1537	$wrapper_transaction_type = $this->wrapper_transaction_type;
1538	if ( $this->inTransaction() ) {
1539	$savepoint_name = $this->get_internal_savepoint_name( 'wrapper' );
1540	$stmt = $this->connection->prepare( sprintf( 'SAVEPOINT %s', $savepoint_name ) );
1541	$wrapper_transaction_type = 'savepoint';
1542	} else {
1543	// For write transactions, we must use "BEGIN IMMEDIATE".
1544	// @see self::begin_user_transaction() method comments.
1545	$stmt = $this->connection->prepare( $this->is_readonly ? 'BEGIN' : 'BEGIN IMMEDIATE' );
1546	$wrapper_transaction_type = 'transaction';
1547	}
1548
1549	if ( ! $stmt->execute() ) {
1550	throw $this->new_driver_exception( 'Failed to begin wrapper transaction.' );
1551	}
1552	$this->wrapper_transaction_type = $wrapper_transaction_type;
1553	$this->in_transaction = true;
1554	}
1555
1556	/**
1557	* Commit a wrapper transaction.
1558	*/
1559	private function commit_wrapper_transaction(): void {
1560	if ( null === $this->wrapper_transaction_type ) {
1561	return;
1562	}
1563
1564	$in_transaction = $this->in_transaction;
1565	if ( 'savepoint' === $this->wrapper_transaction_type ) {
1566	$savepoint_name = $this->get_internal_savepoint_name( 'wrapper' );
1567	$stmt = $this->connection->prepare( sprintf( 'RELEASE SAVEPOINT %s', $savepoint_name ) );
1568	} else {
1569	$stmt = $this->connection->prepare( 'COMMIT' );
1570	$in_transaction = false;
1571	}
1572
1573	if ( ! $stmt->execute() ) {
1574	throw $this->new_driver_exception( 'Failed to commit wrapper transaction.' );
1575	}
1576	$this->wrapper_transaction_type = null;
1577	$this->in_transaction = $in_transaction;
1578	}
1579
1580	/**
1581	* Execute the "BEGIN" or "START TRANSACTION" MySQL statement in SQLite.
1582	*/
1583	private function begin_user_transaction(): void {
1584	// MySQL implicitly commits previous transaction when starting a new one.
1585	if ( $this->inTransaction() ) {
1586	$this->commit_user_transaction();
1587	}
1588
1589	/*
1590	* Since we don't know whether the user will write to the database, we
1591	* must use "BEGIN IMMEDIATE" to immediately open a write transaction.
1592	*
1593	* This is needed to avoid the "database is locked" error (SQLITE_BUSY)
1594	* when SQLite can't upgrade a read transaction to a write transaction,
1595	* because another connection is already modifying the database.
1596	*
1597	* @see self::begin_wrapper_transaction()
1598	*/
1599	$this->connection->query( 'BEGIN IMMEDIATE' );
1600	$this->in_transaction = true;
1601	}
1602
1603	/**
1604	* Execute the "COMMIT" MySQL statement in SQLite.
1605	*/
1606	private function commit_user_transaction(): void {
1607	// MySQL doesn't throw an error if there is no active transaction.
1608	if ( ! $this->inTransaction() ) {
1609	return;
1610	}
1611	$this->connection->query( 'COMMIT' );
1612	$this->in_transaction = false;
1613	}
1614
1615	/**
1616	* Execute the "ROLLBACK" MySQL statement in SQLite.
1617	*/
1618	private function rollback_user_transaction(): void {
1619	// MySQL doesn't throw an error if there is no active transaction.
1620	if ( ! $this->inTransaction() ) {
1621	return;
1622	}
1623	$this->connection->query( 'ROLLBACK' );
1624	$this->in_transaction = false;
1625	}
1626
1627	/**
1628	* Execute a MySQL transaction or locking statement in SQLite.
1629	*
1630	* @param WP_Parser_Node $node The "transactionOrLockingStatement" AST node.
1631	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1632	*/
1633	private function execute_transaction_or_locking_statement( WP_Parser_Node $node ): void {
1634	$subnode = $node->get_first_child_node();
1635	$token = $node->get_first_descendant_token();
1636
1637	switch ( $subnode->rule_name ) {
1638	case 'transactionStatement':
1639	// START TRANSACTION.
1640	if ( WP_MySQL_Lexer::START_SYMBOL === $token->id ) {
1641	$this->begin_user_transaction();
1642	return;
1643	}
1644
1645	// COMMIT.
1646	if ( WP_MySQL_Lexer::COMMIT_SYMBOL === $token->id ) {
1647	$this->commit_user_transaction();
1648	return;
1649	}
1650
1651	break;
1652	case 'savepointStatement':
1653	$savepoint_name = $this->translate( $subnode->get_first_child_node( 'identifier' ) );
1654
1655	// ROLLBACK/ROLLBACK TO SAVEPOINT <identifier>.
1656	if ( WP_MySQL_Lexer::ROLLBACK_SYMBOL === $token->id ) {
1657	if ( null === $savepoint_name ) {
1658	$this->rollback_user_transaction();
1659	} else {
1660	$this->execute_sqlite_query( sprintf( 'ROLLBACK TO SAVEPOINT %s', $savepoint_name ) );
1661	}
1662	return;
1663	}
1664
1665	// SAVEPOINT.
1666	if ( WP_MySQL_Lexer::SAVEPOINT_SYMBOL === $token->id ) {
1667	$this->execute_sqlite_query( sprintf( 'SAVEPOINT %s', $savepoint_name ) );
1668	return;
1669	}
1670
1671	// RELEASE SAVEPOINT.
1672	if ( WP_MySQL_Lexer::RELEASE_SYMBOL === $token->id ) {
1673	$this->execute_sqlite_query( sprintf( 'RELEASE SAVEPOINT %s', $savepoint_name ) );
1674	return;
1675	}
1676
1677	break;
1678	case 'lockStatement':
1679	// LOCK TABLE/LOCK TABLES.
1680	if (
1681	WP_MySQL_Lexer::LOCK_SYMBOL === $token->id
1682	&& $subnode->has_child_node( 'lockItem' )
1683	) {
1684	// Check if the table(s) exists.
1685	$lock_items = $subnode->get_child_nodes( 'lockItem' );
1686	foreach ( $lock_items as $lock_item ) {
1687	$table_ref = $lock_item->get_first_child_node( 'tableRef' );
1688	$database = $this->get_database_name( $table_ref );
1689	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
1690	if ( 'information_schema' === strtolower( $database ) ) {
1691	throw $this->new_access_denied_to_information_schema_exception();
1692	}
1693
1694	try {
1695	/*
1696	* Attempt to query the table directly rather than checking
1697	* SQLite schema or information schema tables, so that we
1698	* can handle persistent and temporary tables in one query.
1699	*/
1700	$this->execute_sqlite_query(
1701	sprintf( 'SELECT 1 FROM %s LIMIT 0', $table_name )
1702	);
1703	} catch ( PDOException $e ) {
1704	throw $this->new_driver_exception(
1705	sprintf( "Table '%s.%s' doesn't exist", $this->db_name, $table_name ),
1706	'42S02'
1707	);
1708	}
1709	}
1710
1711	$this->begin_user_transaction();
1712	$this->table_lock_active = true;
1713	return;
1714	}
1715
1716	// UNLOCK TABLES/UNLOCK TABLE.
1717	if (
1718	WP_MySQL_Lexer::UNLOCK_SYMBOL === $token->id
1719	&& (
1720	$subnode->has_child_token( WP_MySQL_Lexer::TABLE_SYMBOL )
1721	\|\| $subnode->has_child_token( WP_MySQL_Lexer::TABLES_SYMBOL )
1722	)
1723	) {
1724	// Commit the transaction when created by the LOCK statement.
1725	if ( $this->table_lock_active && $this->inTransaction() ) {
1726	$this->commit_user_transaction();
1727	$this->table_lock_active = false;
1728	}
1729	return;
1730	}
1731
1732	break;
1733	}
1734
1735	throw $this->new_not_supported_exception(
1736	sprintf(
1737	'statement type: "%s" > "%s"',
1738	$node->rule_name,
1739	$subnode->rule_name
1740	)
1741	);
1742	}
1743
1744	/**
1745	* Translate and execute a MySQL SELECT statement in SQLite.
1746	*
1747	* @param WP_Parser_Node $node The "selectStatement" AST node.
1748	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1749	*/
1750	private function execute_select_statement( WP_Parser_Node $node ): void {
1751	/*
1752	* [GRAMMAR]
1753	* selectStatement:
1754	* queryExpression lockingClauseList?
1755	* \| selectStatementWithInto
1756	*/
1757
1758	// First, translate the query, before we modify last found rows count.
1759	$query = $this->translate( $node->get_first_child() );
1760
1761	$has_sql_calc_found_rows = null !== $node->get_first_descendant_token(
1762	WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL
1763	);
1764
1765	// Handle SQL_CALC_FOUND_ROWS.
1766	if ( true === $has_sql_calc_found_rows ) {
1767	// Recursively find a query expression with the first LIMIT or SELECT.
1768	$query_expr = $node->get_first_descendant_node( 'queryExpression' );
1769	while ( true ) {
1770	if ( $query_expr->has_child_node( 'limitClause' ) ) {
1771	break;
1772	}
1773
1774	$query_expr_parens = $query_expr->get_first_child_node( 'queryExpressionParens' );
1775	if ( null !== $query_expr_parens ) {
1776	$query_expr = $query_expr_parens->get_first_child_node( 'queryExpression' );
1777	continue;
1778	}
1779
1780	$query_expr_body = $query_expr->get_first_child_node( 'queryExpressionBody' );
1781	if ( count( $query_expr_body->get_children() ) > 1 ) {
1782	break;
1783	}
1784
1785	$query_term = $query_expr_body->get_first_child_node( 'queryTerm' );
1786	if (
1787	count( $query_term->get_children() ) === 1
1788	&& $query_term->has_child_node( 'queryExpressionParens' )
1789	) {
1790	$query_expr = $query_term->get_first_child_node( 'queryExpressionParens' )->get_first_child_node( 'queryExpression' );
1791	continue;
1792	}
1793
1794	break;
1795	}
1796
1797	// Exclude the limit clause from the expression.
1798	$count_expr = new WP_Parser_Node( $query_expr->rule_id, $query_expr->rule_name );
1799	foreach ( $query_expr->get_children() as $child ) {
1800	if ( ! ( $child instanceof WP_Parser_Node && 'limitClause' === $child->rule_name ) ) {
1801	$count_expr->append_child( $child );
1802	}
1803	}
1804
1805	// Get count of all the rows.
1806	$result = $this->execute_sqlite_query(
1807	'SELECT COUNT(*) AS cnt FROM (' . $this->translate( $count_expr ) . ')'
1808	);
1809
1810	$this->found_rows = (int) $result->fetchColumn();
1811	} else {
1812	$this->found_rows = $query;
1813	}
1814
1815	// Execute the query.
1816	$stmt = $this->execute_sqlite_query( $query );
1817
1818	// Store column meta info. This must be done before fetching data, which
1819	// seems to erase type information for expressions in the SELECT clause.
1820	$this->store_last_column_meta_from_statement( $stmt );
1821	$this->last_result_statement = $stmt;
1822	}
1823
1824	/**
1825	* Translate and execute a MySQL INSERT or REPLACE statement in SQLite.
1826	*
1827	* @param WP_Parser_Node $node The "insertStatement" or "replaceStatement" AST node.
1828	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1829	*/
1830	private function execute_insert_or_replace_statement( WP_Parser_Node $node ): void {
1831	$parts = array();
1832	$on_conflict_update_list = null;
1833	foreach ( $node->get_children() as $child ) {
1834	$is_token = $child instanceof WP_MySQL_Token;
1835	$is_node = $child instanceof WP_Parser_Node;
1836
1837	if ( $child instanceof WP_Parser_Node && 'tableRef' === $child->rule_name ) {
1838	$database = $this->get_database_name( $child );
1839	if ( 'information_schema' === strtolower( $database ) ) {
1840	throw $this->new_access_denied_to_information_schema_exception();
1841	}
1842	}
1843
1844	// Skip the SET keyword in "INSERT INTO ... SET ..." syntax.
1845	if ( $is_token && WP_MySQL_Lexer::SET_SYMBOL === $child->id ) {
1846	continue;
1847	}
1848
1849	if ( $is_token && WP_MySQL_Lexer::IGNORE_SYMBOL === $child->id ) {
1850	// Translate "UPDATE IGNORE" to "UPDATE OR IGNORE".
1851	$parts[] = 'OR IGNORE';
1852	} elseif (
1853	$is_node
1854	&& (
1855	'insertFromConstructor' === $child->rule_name
1856	\|\| 'insertQueryExpression' === $child->rule_name
1857	\|\| 'updateList' === $child->rule_name
1858	)
1859	) {
1860	$table_ref = $node->get_first_child_node( 'tableRef' );
1861	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
1862	$parts[] = $this->translate_insert_or_replace_body( $table_name, $child );
1863	} elseif ( $is_node && 'insertUpdateList' === $child->rule_name ) {
1864	/*
1865	* Translate "ON DUPLICATE KEY UPDATE" to "ON CONFLICT DO UPDATE SET".
1866	*
1867	* For SQLite versions older than 3.35.0, we need to handle the
1868	* ON CONFLICT clause differently, and at this stage, we only
1869	* save the translated update list to a variable.
1870	*
1871	* See bellow at "Handle ON CONFLICT clause for SQLite < 3.35.0".
1872	*/
1873	$sqlite_version = $this->get_sqlite_version();
1874	if ( version_compare( $sqlite_version, '3.35.0', '<' ) ) {
1875	$on_conflict_update_list = $this->translate_update_list( $table_name, $child );
1876	} else {
1877	$parts[] = 'ON CONFLICT DO UPDATE SET ';
1878	$parts[] = $this->translate_update_list( $table_name, $child );
1879	}
1880	} else {
1881	$parts[] = $this->translate( $child );
1882	}
1883	}
1884
1885	$query = implode( ' ', $parts );
1886
1887	/*
1888	* Handle ON CONFLICT clause for SQLite < 3.35.0.
1889	*
1890	* If and "$on_conflict_update_list" was saved, we are on SQLite version
1891	* older than 3.35.0 and an ON CONFLICT clause was used in the query.
1892	*
1893	* SQLite supports a generic ON CONFLICT clause without an explicit column
1894	* list only from version 3.35.0.
1895	*
1896	* For older versions, we need to work around this limitation:
1897	* 1. Save the ON CONFLICT update list to a variable.
1898	* 2. Execute the query without the ON CONFLICT clause.
1899	* 3. If a constraint violation error occurs, parse the names of the
1900	* columns that caused the violation from the error message.
1901	* 4. Execute the query again, appending the ON CONFLICT clause with
1902	* the column names parsed from the error message.
1903	*/
1904	if ( null !== $on_conflict_update_list ) {
1905	try {
1906	$this->last_result_statement = $this->execute_sqlite_query( $query );
1907	} catch ( PDOException $e ) {
1908	$unique_key_violation_prefix = 'SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: ';
1909	if ( '23000' === $e->getCode() && str_contains( $e->getMessage(), $unique_key_violation_prefix ) ) {
1910	/*
1911	* Parse column names from the constraint violation error.
1912	*
1913	* The error message is in the following format:
1914	* <prefix>: <table>.<col1>, <table>.<col2>, ...
1915	*
1916	* The table and column names in the message are not quoted.
1917	* To be on the safe side, we first strip the error message
1918	* prefix and the "<table>." part for the first column, and
1919	* then split the rest of the list by ", <table>." sequence.
1920	*/
1921	$column_list = substr( $e->getMessage(), strlen( $unique_key_violation_prefix ) + strlen( $table_name ) + 1 );
1922	$column_names = explode( ", $table_name.", $column_list );
1923	$quoted_column_names = array_map(
1924	function ( $column ) {
1925	return $this->quote_sqlite_identifier( $column );
1926	},
1927	$column_names
1928	);
1929	$this->last_result_statement = $this->execute_sqlite_query(
1930	$query . sprintf(
1931	' ON CONFLICT(%s) DO UPDATE SET %s',
1932	implode( ', ', $quoted_column_names ),
1933	$on_conflict_update_list
1934	)
1935	);
1936	} else {
1937	throw $e;
1938	}
1939	}
1940	return;
1941	}
1942
1943	$this->last_result_statement = $this->execute_sqlite_query( $query );
1944	}
1945
1946	/**
1947	* Translate and execute a MySQL UPDATE statement in SQLite.
1948	*
1949	* @param WP_Parser_Node $node The "updateStatement" AST node.
1950	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1951	*/
1952	private function execute_update_statement( WP_Parser_Node $node ): void {
1953	// @TODO: Add support for UPDATE with multiple tables and JOINs.
1954	// SQLite supports them in the FROM clause.
1955
1956	$has_order = $node->has_child_node( 'orderClause' );
1957	$has_limit = $node->has_child_node( 'simpleLimitClause' );
1958
1959	/*
1960	* SQLite doesn't support UPDATE with ORDER BY/LIMIT.
1961	* We need to use a subquery to emulate this behavior.
1962	*
1963	* For instance, the following query:
1964	* UPDATE t SET c = 1 WHERE c = 2 LIMIT 1;
1965	* Will be rewritten to:
1966	* UPDATE t SET c = 1 WHERE rowid IN ( SELECT rowid FROM t WHERE c = 2 LIMIT 1 );
1967	*/
1968	$where_subquery = null;
1969	if ( $has_order \|\| $has_limit ) {
1970	$where_subquery = 'SELECT rowid FROM ' . $this->translate_sequence(
1971	array(
1972	$node->get_first_child_node( 'tableReferenceList' ),
1973	$node->get_first_child_node( 'whereClause' ),
1974	$node->get_first_child_node( 'orderClause' ),
1975	$node->get_first_child_node( 'simpleLimitClause' ),
1976	)
1977	);
1978	}
1979
1980	/*
1981	* Translate the UPDATE statement parts.
1982	*
1983	* [GRAMMAR]
1984	* updateStatement:
1985	* withClause? UPDATE_SYMBOL LOW_PRIORITY_SYMBOL? IGNORE_SYMBOL? tableReferenceList
1986	* SET_SYMBOL updateList whereClause? orderClause? simpleLimitClause?
1987	*/
1988
1989	// Collect all tables used in the UPDATE clause (e.g, UPDATE t1, t2 JOIN t3).
1990	$table_alias_map = $this->create_table_reference_map(
1991	$node->get_first_child_node( 'tableReferenceList' )
1992	);
1993
1994	/*
1995	* Deny UPDATE for information schema tables.
1996	*
1997	* This basic approach is rather restrictive, as it blocks the usage
1998	* of information schema tables anywhere in the UPDATE statement.
1999	*
2000	* TODO: Implement support for UPDATE statements like:
2001	* UPDATE t, information_schema.columns c SET t.column = c.column ...
2002	*/
2003	foreach ( $table_alias_map as $alias => $data ) {
2004	if ( 'information_schema' === strtolower( $data['database'] ?? '' ) ) {
2005	throw $this->new_access_denied_to_information_schema_exception();
2006	}
2007	}
2008
2009	// Determine whether the UPDATE statement modifies multiple tables.
2010	$update_list_node = $node->get_first_child_node( 'updateList' );
2011	$update_target = null;
2012	$updates_multiple_tables = false;
2013	if ( count( $table_alias_map ) > 1 ) {
2014	foreach ( $update_list_node->get_child_nodes( 'updateElement' ) as $update_element ) {
2015	$column_ref = $update_element->get_first_child_node( 'columnRef' );
2016	$column_ref_parts = $column_ref->get_descendant_nodes( 'identifier' );
2017	$table_or_alias = count( $column_ref_parts ) > 1
2018	? $this->unquote_sqlite_identifier( $this->translate( $column_ref_parts[0] ) )
2019	: null;
2020
2021	// When the SET column reference is not qualified, we need to
2022	// verify whether the column is used in multiple tables.
2023	if ( null === $table_or_alias ) {
2024	$persistent_table_names = array();
2025	$temporary_table_names = array();
2026	foreach ( array_filter( array_column( $table_alias_map, 'table_name' ) ) as $table_name ) {
2027	$is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2028	$quoted_table_name = $this->quote_sqlite_value( $table_name );
2029	if ( $is_temporary ) {
2030	$temporary_table_names[] = $quoted_table_name;
2031	} else {
2032	$persistent_table_names[] = $quoted_table_name;
2033	}
2034	}
2035
2036	$column_name = $this->unquote_sqlite_identifier(
2037	$this->translate( end( $column_ref_parts ) )
2038	);
2039
2040	$matched_temporary_tables = array();
2041	if ( count( $temporary_table_names ) > 0 ) {
2042	$matched_temporary_tables = $this->execute_sqlite_query(
2043	sprintf(
2044	'SELECT table_name FROM %s WHERE table_schema = ? AND table_name IN ( %s ) AND column_name = ?',
2045	$this->quote_sqlite_identifier(
2046	$this->information_schema_builder->get_table_name( true, 'columns' )
2047	),
2048	implode( ', ', $temporary_table_names )
2049	),
2050	array( $this->get_saved_db_name(), $column_name )
2051	)->fetchAll( PDO::FETCH_COLUMN );
2052	}
2053
2054	$matched_persistent_tables = array();
2055	if ( count( $persistent_table_names ) > 0 ) {
2056	$matched_persistent_tables = $this->execute_sqlite_query(
2057	sprintf(
2058	'SELECT table_name FROM %s WHERE table_schema = ? AND table_name IN ( %s ) AND column_name = ?',
2059	$this->quote_sqlite_identifier(
2060	$this->information_schema_builder->get_table_name( false, 'columns' )
2061	),
2062	implode( ', ', $persistent_table_names )
2063	),
2064	array( $this->get_saved_db_name(), $column_name )
2065	)->fetchAll( PDO::FETCH_COLUMN );
2066	}
2067
2068	$matched_tables = array_merge( $matched_temporary_tables, $matched_persistent_tables );
2069	$updates_multiple_tables = count( $matched_tables ) > 1;
2070	if ( 1 === count( $matched_tables ) ) {
2071	$table_or_alias = $matched_tables[0];
2072	} else {
2073	break;
2074	}
2075	}
2076
2077	if ( null === $update_target ) {
2078	$update_target = $table_or_alias;
2079	}
2080
2081	if ( $update_target !== $table_or_alias ) {
2082	$updates_multiple_tables = true;
2083	break;
2084	}
2085	}
2086	} else {
2087	$update_target = array_keys( $table_alias_map )[0];
2088	}
2089
2090	// TODO: Support UPDATE that modifies multiple tables.
2091	// This is non-trivial and likely requires temporary tables.
2092	// E.g.: UPDATE t1, t2 SET t1.id = t2.id, t2.id = t1.id;
2093	if ( $updates_multiple_tables ) {
2094	throw $this->new_not_supported_exception( 'UPDATE statement modifying multiple tables' );
2095	}
2096
2097	// Translate WITH clause.
2098	$with = $this->translate( $node->get_first_child_node( 'withClause' ) );
2099
2100	// Translate "UPDATE IGNORE" to "UPDATE OR IGNORE".
2101	$or_ignore = $node->has_child_token( WP_MySQL_Lexer::IGNORE_SYMBOL )
2102	? 'OR IGNORE'
2103	: null;
2104
2105	// Compose the update target clause.
2106	$update_target_table = $table_alias_map[ $update_target ]['table_name'] ?? $update_target;
2107	$update_target_clause = $this->quote_sqlite_identifier( $update_target_table );
2108	if ( $update_target !== $update_target_table ) {
2109	$update_target_clause .= ' AS ' . $this->quote_sqlite_identifier( $update_target );
2110	}
2111
2112	// Compose the FROM clause using all tables except the one being updated.
2113	// UPDATE with FROM in SQLite is equivalent to UPDATE with JOIN in MySQL.
2114	$from_items = array();
2115	foreach ( $table_alias_map as $alias => $data ) {
2116	if ( $alias === $update_target ) {
2117	continue;
2118	}
2119
2120	$table_name = $data['table_name'];
2121
2122	// Derived table.
2123	if ( null === $table_name ) {
2124	$from_item = $data['table_expr'] . ' AS ' . $this->quote_sqlite_identifier( $alias );
2125	$from_items[] = $from_item;
2126	continue;
2127	}
2128
2129	// Regular table.
2130	$from_item = $this->quote_sqlite_identifier( $table_name );
2131	if ( $alias !== $table_name ) {
2132	$from_item .= ' AS ' . $this->quote_sqlite_identifier( $alias );
2133	}
2134	$from_items[] = $from_item;
2135	}
2136
2137	$from = null;
2138	if ( count( $from_items ) > 0 ) {
2139	$from = 'FROM ' . implode( ', ', $from_items );
2140	}
2141
2142	// Translate UPDATE list, applying relevant type casting and IMPLICIT DEFAULT values.
2143	$update_list = $this->translate_update_list( $update_target_table, $node );
2144
2145	// Translate WHERE, ORDER BY, and LIMIT clauses.
2146	if ( $where_subquery ) {
2147	// When using a subquery, skip the original WHERE, ORDER BY, and LIMIT.
2148	$where_clause = ' WHERE rowid IN ( ' . $where_subquery . ' )';
2149	$order_clause = null;
2150	$limit_clause = null;
2151	} else {
2152	$where_clause = $this->translate( $node->get_first_child_node( 'whereClause' ) );
2153	$order_clause = $this->translate( $node->get_first_child_node( 'orderClause' ) );
2154	$limit_clause = $this->translate( $node->get_first_child_node( 'simpleLimitClause' ) );
2155	}
2156
2157	// With JOINs, we need to use the JOIN expressions in the WHERE clause.
2158	$join_exprs = array_filter( array_column( $table_alias_map, 'join_expr' ) );
2159	if ( count( $join_exprs ) > 0 ) {
2160	$where_clause .= $where_clause ? ' AND ' : ' WHERE ';
2161	$where_clause .= implode( ' AND ', $join_exprs );
2162	}
2163
2164	// Compose the UPDATE query.
2165	$parts = array(
2166	$with,
2167	'UPDATE',
2168	$or_ignore,
2169	$update_target_clause,
2170	'SET',
2171	$update_list,
2172	$from,
2173	$where_clause,
2174	$order_clause,
2175	$limit_clause,
2176	);
2177	$query = implode( ' ', array_filter( $parts ) );
2178
2179	$this->last_result_statement = $this->execute_sqlite_query( $query );
2180	}
2181
2182	/**
2183	* Translate and execute a MySQL DELETE statement in SQLite.
2184	*
2185	* @param WP_Parser_Node $node The "deleteStatement" AST node.
2186	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2187	*/
2188	private function execute_delete_statement( WP_Parser_Node $node ): void {
2189	/*
2190	* Multi-table DELETE.
2191	*
2192	* MySQL supports multi-table DELETE statements that don't work in SQLite.
2193	* These statements can have the following two flavours:
2194	* 1. "DELETE t1, t2 FROM ... JOIN ... WHERE ..."
2195	* 2. "DELETE FROM t1, t2 USING ... JOIN ... WHERE ..."
2196	*
2197	* We will rewrite such statements into a SELECT to fetch the ROWIDs of
2198	* the rows to delete and then execute a DELETE statement for each table.
2199	*/
2200	$alias_ref_list = $node->get_first_child_node( 'tableAliasRefList' );
2201	if ( null !== $alias_ref_list ) {
2202	// 1. Get table aliases targeted by the DELETE statement.
2203	$table_aliases = array();
2204	foreach ( $alias_ref_list->get_child_nodes() as $alias_ref ) {
2205	$table_aliases[] = $this->unquote_sqlite_identifier(
2206	$this->translate( $alias_ref )
2207	);
2208	}
2209
2210	// 2. Create an alias to table name map.
2211	$alias_map = array();
2212	$table_ref_list = $node->get_first_child_node( 'tableReferenceList' );
2213	foreach ( $table_ref_list->get_descendant_nodes( 'singleTable' ) as $single_table ) {
2214	$table_ref = $single_table->get_first_child_node( 'tableRef' );
2215	$alias_node = $single_table->get_first_child_node( 'tableAlias' );
2216	if ( $alias_node ) {
2217	$alias = $this->unquote_sqlite_identifier( $this->translate( $alias_node ) );
2218	} else {
2219	$alias = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2220	}
2221
2222	// For an information schema table, check if is a DELETE target.
2223	$database = $this->get_database_name( $table_ref );
2224	if (
2225	'information_schema' === strtolower( $database )
2226	&& in_array( $alias, $table_aliases, true )
2227	) {
2228	throw $this->new_access_denied_to_information_schema_exception();
2229	}
2230
2231	$alias_map[ $alias ] = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2232	}
2233
2234	// 3. Compose the SELECT query to fetch ROWIDs to delete.
2235	$where_clause = $node->get_first_child_node( 'whereClause' );
2236	if ( null !== $where_clause ) {
2237	$where = $this->translate( $where_clause->get_first_child_node( 'expr' ) );
2238	}
2239
2240	$select_list = array();
2241	foreach ( $table_aliases as $table ) {
2242	$select_list[] = sprintf(
2243	'%s.rowid AS %s',
2244	$this->quote_sqlite_identifier( $table ),
2245	$this->quote_sqlite_identifier( $table . '_rowid' )
2246	);
2247	}
2248
2249	$ids = $this->execute_sqlite_query(
2250	sprintf(
2251	'SELECT %s FROM %s %s',
2252	implode( ', ', $select_list ),
2253	$this->translate( $table_ref_list ),
2254	isset( $where ) ? "WHERE $where" : ''
2255	)
2256	)->fetchAll( PDO::FETCH_ASSOC );
2257
2258	// 4. Execute DELETE statements for each table.
2259	$affected_rows = 0;
2260	if ( count( $ids ) > 0 ) {
2261	foreach ( $table_aliases as $table ) {
2262	$stmt = $this->execute_sqlite_query(
2263	sprintf(
2264	'DELETE FROM %s AS %s WHERE rowid IN ( %s )',
2265	$this->quote_sqlite_identifier( $alias_map[ $table ] ),
2266	$this->quote_sqlite_identifier( $table ),
2267	implode( ', ', array_column( $ids, "{$table}_rowid" ) )
2268	)
2269	);
2270	$affected_rows += $stmt->rowCount();
2271	}
2272	}
2273
2274	$this->last_result_statement = $this->create_result_statement_from_data( array(), array() );
2275	$this->last_affected_rows = $affected_rows;
2276	return;
2277	}
2278
2279	// @TODO: Translate DELETE with JOIN to use a subquery.
2280
2281	$table_ref = $node->get_first_child_node( 'tableRef' );
2282	$database = $this->get_database_name( $table_ref );
2283	if ( 'information_schema' === strtolower( $database ) ) {
2284	throw $this->new_access_denied_to_information_schema_exception();
2285	}
2286
2287	$query = $this->translate( $node );
2288	$this->last_result_statement = $this->execute_sqlite_query( $query );
2289	}
2290
2291	/**
2292	* Translate and execute a MySQL CREATE TABLE statement in SQLite.
2293	*
2294	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
2295	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2296	*/
2297	private function execute_create_table_statement( WP_Parser_Node $node ): void {
2298	$subnode = $node->get_first_child_node();
2299
2300	// Handle TEMPORARY keyword.
2301	$table_is_temporary = $subnode->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
2302
2303	// Handle CREATE TABLE ... [AS] SELECT.
2304	$element_list = $subnode->get_first_child_node( 'tableElementList' );
2305	if ( null === $element_list ) {
2306	/*
2307	* While SQLite supports CREATE TABLE ... AS SELECT statements,
2308	* we need to somehow implement information schema support for
2309	* the tables created in this way.
2310	*
2311	* TODO: Implement information schema support for CREATE TABLE ... AS SELECT.
2312	*/
2313	throw $this->new_not_supported_exception(
2314	'CREATE TABLE ... [AS] SELECT is currently not supported'
2315	);
2316	}
2317
2318	// Get table name.
2319	$table_name_node = $subnode->get_first_child_node( 'tableName' );
2320	$database = $this->get_database_name( $table_name_node );
2321	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_name_node ) );
2322
2323	if ( 'information_schema' === strtolower( $database ) ) {
2324	throw $this->new_access_denied_to_information_schema_exception();
2325	}
2326
2327	// Handle IF NOT EXISTS.
2328	if ( $subnode->has_child_node( 'ifNotExists' ) ) {
2329	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
2330	$table_exists = $this->execute_sqlite_query(
2331	sprintf(
2332	'SELECT 1 FROM %s WHERE table_schema = ? AND table_name = ?',
2333	$this->quote_sqlite_identifier( $tables_table )
2334	),
2335	array( $this->get_saved_db_name(), $table_name )
2336	)->fetchColumn();
2337
2338	if ( $table_exists ) {
2339	$this->last_result_statement = $this->create_result_statement_from_data( array(), array() );
2340	return;
2341	}
2342	}
2343
2344	// Save information to information schema tables.
2345	$this->information_schema_builder->record_create_table( $node );
2346
2347	// Generate CREATE TABLE statement from the information schema tables.
2348	$queries = $this->get_sqlite_create_table_statement( $table_is_temporary, $table_name );
2349	$create_table_query = $queries[0];
2350	$constraint_queries = array_slice( $queries, 1 );
2351
2352	$this->execute_sqlite_query( $create_table_query );
2353
2354	foreach ( $constraint_queries as $query ) {
2355	$this->execute_sqlite_query( $query );
2356	}
2357	}
2358
2359	/**
2360	* Translate and execute a MySQL ALTER TABLE statement in SQLite.
2361	*
2362	* @param WP_Parser_Node $node The "alterStatement" AST node with "alterTable" child.
2363	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2364	*/
2365	private function execute_alter_table_statement( WP_Parser_Node $node ): void {
2366	$table_ref = $node->get_first_descendant_node( 'tableRef' );
2367	$database = $this->get_database_name( $table_ref );
2368	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2369	if ( 'information_schema' === strtolower( $database ) ) {
2370	throw $this->new_access_denied_to_information_schema_exception();
2371	}
2372
2373	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2374
2375	// Save all column names from the original table.
2376	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
2377	$column_names = $this->execute_sqlite_query(
2378	sprintf(
2379	'SELECT
2380	COLUMN_NAME,
2381	LOWER(COLUMN_NAME) AS COLUMN_NAME_LOWERCASE
2382	FROM %s WHERE table_schema = ? AND table_name = ?',
2383	$this->quote_sqlite_identifier( $columns_table )
2384	),
2385	array( $this->get_saved_db_name( $database ), $table_name )
2386	)->fetchAll( PDO::FETCH_ASSOC );
2387
2388	// Track column renames and removals.
2389	$column_map = array_combine(
2390	array_column( $column_names, 'COLUMN_NAME_LOWERCASE' ),
2391	array_column( $column_names, 'COLUMN_NAME' )
2392	);
2393	foreach ( $node->get_descendant_nodes( 'alterListItem' ) as $action ) {
2394	$first_token = $action->get_first_child_token();
2395
2396	switch ( $first_token->id ) {
2397	case WP_MySQL_Lexer::DROP_SYMBOL:
2398	$name = $this->translate( $action->get_first_child_node( 'fieldIdentifier' ) );
2399	if ( null !== $name ) {
2400	$name = $this->unquote_sqlite_identifier( $name );
2401	unset( $column_map[ strtolower( $name ) ] );
2402	}
2403	break;
2404	case WP_MySQL_Lexer::CHANGE_SYMBOL:
2405	$old_name = $this->unquote_sqlite_identifier(
2406	$this->translate( $action->get_first_child_node( 'fieldIdentifier' ) )
2407	);
2408	$new_name = $this->unquote_sqlite_identifier(
2409	$this->translate( $action->get_first_child_node( 'identifier' ) )
2410	);
2411
2412	$column_map[ strtolower( $old_name ) ] = $new_name;
2413	break;
2414	case WP_MySQL_Lexer::RENAME_SYMBOL:
2415	$column_ref = $action->get_first_child_node( 'fieldIdentifier' );
2416	if ( null !== $column_ref ) {
2417	$old_name = $this->unquote_sqlite_identifier(
2418	$this->translate( $column_ref )
2419	);
2420	$new_name = $this->unquote_sqlite_identifier(
2421	$this->translate( $action->get_first_child_node( 'identifier' ) )
2422	);
2423
2424	$column_map[ strtolower( $old_name ) ] = $new_name;
2425	}
2426	break;
2427	}
2428	}
2429
2430	$this->information_schema_builder->record_alter_table( $node );
2431	$this->recreate_table_from_information_schema( $table_is_temporary, $table_name, $column_map );
2432
2433	// @TODO: Consider using a "fast path" for ALTER TABLE statements that
2434	// consist only of operations that SQLite's ALTER TABLE supports.
2435	}
2436
2437	/**
2438	* Translate and execute a MySQL DROP TABLE statement in SQLite.
2439	*
2440	* @param WP_Parser_Node $node The "dropStatement" AST node with "dropTable" child.
2441	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2442	*/
2443	private function execute_drop_table_statement( WP_Parser_Node $node ): void {
2444	// Record the changes in the information schema.
2445	$this->information_schema_builder->record_drop_table( $node );
2446
2447	// MySQL supports removing multiple tables in a single query DROP query.
2448	// In SQLite, we need to execute each DROP TABLE statement separately.
2449	$child_node = $node->get_first_child_node();
2450	$table_refs = $child_node->get_first_child_node( 'tableRefList' )->get_child_nodes();
2451	$table_is_temporary = $child_node->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
2452	$queries = array();
2453	foreach ( $table_refs as $table_ref ) {
2454	$database = $this->get_database_name( $table_ref );
2455	if ( 'information_schema' === strtolower( $database ) ) {
2456	throw $this->new_access_denied_to_information_schema_exception();
2457	}
2458
2459	$parts = array();
2460	foreach ( $child_node->get_children() as $child ) {
2461	$is_token = $child instanceof WP_MySQL_Token;
2462
2463	// Skip the TEMPORARY keyword.
2464	if ( $is_token && WP_MySQL_Lexer::TEMPORARY_SYMBOL === $child->id ) {
2465	continue;
2466	}
2467
2468	// Replace table list with the current table reference.
2469	if ( ! $is_token && 'tableRefList' === $child->rule_name ) {
2470	// Add a "temp." schema prefix for temporary tables.
2471	$prefix = $table_is_temporary ? '`temp`.' : '';
2472	$part = $prefix . $this->translate( $table_ref );
2473	} else {
2474	$part = $this->translate( $child );
2475	}
2476
2477	if ( null !== $part ) {
2478	$parts[] = $part;
2479	}
2480	}
2481	$queries[] = 'DROP ' . implode( ' ', $parts );
2482	}
2483
2484	foreach ( $queries as $query ) {
2485	$this->execute_sqlite_query( $query );
2486	}
2487	}
2488
2489	/**
2490	* Translate and execute a MySQL TRUNCATE TABLE statement in SQLite.
2491	*
2492	* @param WP_Parser_Node $node The "truncateTableStatement" AST node.
2493	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2494	*/
2495	private function execute_truncate_table_statement( WP_Parser_Node $node ): void {
2496	$table_ref = $node->get_first_child_node( 'tableRef' );
2497	$database = $this->get_database_name( $table_ref );
2498	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2499	if ( 'information_schema' === strtolower( $database ) ) {
2500	throw $this->new_access_denied_to_information_schema_exception();
2501	}
2502
2503	$this->execute_sqlite_query(
2504	sprintf( 'DELETE FROM %s', $this->quote_sqlite_identifier( $table_name ) )
2505	);
2506	try {
2507	$this->last_result_statement = $this->execute_sqlite_query(
2508	'DELETE FROM sqlite_sequence WHERE name = ?',
2509	array( $table_name )
2510	);
2511	} catch ( PDOException $e ) {
2512	if ( str_contains( $e->getMessage(), 'no such table' ) ) {
2513	// The table might not exist if no sequences are used in the DB.
2514	} else {
2515	throw $e;
2516	}
2517	}
2518	}
2519
2520	/**
2521	* Translate and execute a MySQL CREATE INDEX statement in SQLite.
2522	*
2523	* @param WP_Parser_Node $node The "createStatement" AST node with "createIndex" child.
2524	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2525	*/
2526	private function execute_create_index_statement( WP_Parser_Node $node ): void {
2527	$create_index = $node->get_first_child_node( 'createIndex' );
2528	$target = $create_index->get_first_child_node( 'createIndexTarget' );
2529	$table_ref = $target->get_first_child_node( 'tableRef' );
2530	$database = $this->get_database_name( $table_ref );
2531	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2532
2533	if ( 'information_schema' === strtolower( $database ) ) {
2534	throw $this->new_access_denied_to_information_schema_exception();
2535	}
2536
2537	$this->information_schema_builder->record_create_index( $node );
2538
2539	$index_name = $this->unquote_sqlite_identifier(
2540	$this->translate( $create_index->get_first_child_node( 'indexName' ) )
2541	);
2542	$is_unique = $create_index->has_child_token( WP_MySQL_Lexer::UNIQUE_SYMBOL );
2543
2544	// Get the key parts.
2545	$key_list_variants = $target->get_first_child_node( 'keyListVariants' );
2546	$key_list_nodes = $key_list_variants->get_first_child_node()->get_child_nodes();
2547	foreach ( $key_list_nodes as $key_list_node ) {
2548	if ( 'keyPartOrExpression' === $key_list_node->rule_name ) {
2549	$key_part_node = $key_list_node->get_first_child();
2550	} else {
2551	$key_part_node = $key_list_node;
2552	}
2553
2554	if ( 'keyPart' === $key_part_node->rule_name ) {
2555	$key_part = $this->translate( $key_part_node->get_first_child_node( 'identifier' ) );
2556	$direction = $key_part_node->get_first_child_node( 'direction' );
2557	if ( null !== $direction ) {
2558	$key_part .= ' ' . $this->translate( $direction );
2559	}
2560	} else {
2561	$key_part = $this->translate( $key_part_node );
2562	}
2563	$key_parts[] = $key_part;
2564	}
2565
2566	$sqlite_index_name = $this->get_sqlite_index_name( $table_name, $index_name );
2567	$this->execute_sqlite_query(
2568	sprintf(
2569	'CREATE %sINDEX %s ON %s (%s)',
2570	$is_unique ? 'UNIQUE ' : '',
2571	$this->quote_sqlite_identifier( $sqlite_index_name ),
2572	$this->translate( $target->get_first_child_node( 'tableRef' ) ),
2573	implode( ', ', $key_parts )
2574	)
2575	);
2576	}
2577
2578	/**
2579	* Translate and execute a MySQL DROP INDEX statement in SQLite.
2580	*
2581	* @param WP_Parser_Node $node The "dropStatement" AST node with "dropIndex" child.
2582	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2583	*/
2584	private function execute_drop_index_statement( WP_Parser_Node $node ): void {
2585	$drop_index = $node->get_first_child_node( 'dropIndex' );
2586	$table_ref = $drop_index->get_first_child_node( 'tableRef' );
2587	$database = $this->get_database_name( $table_ref );
2588	if ( 'information_schema' === strtolower( $database ) ) {
2589	throw $this->new_access_denied_to_information_schema_exception();
2590	}
2591
2592	$this->information_schema_builder->record_drop_index( $node );
2593
2594	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2595	$index_name = $this->unquote_sqlite_identifier(
2596	$this->translate( $drop_index->get_first_child_node( 'indexRef' ) )
2597	);
2598
2599	/*
2600	* In MySQL, "DROP INDEX `PRIMARY` ON <table>" removes the PRIMARY KEY.
2601	* This is not supported in SQLite, so in such cases, we need to recreate
2602	* the table without the PRIMARY KEY using the updated information schema.
2603	*/
2604	if ( 'PRIMARY' === strtoupper( $index_name ) ) {
2605	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2606	$this->recreate_table_from_information_schema( $table_is_temporary, $table_name );
2607	return;
2608	}
2609
2610	$sqlite_index_name = $this->get_sqlite_index_name( $table_name, $index_name );
2611	$this->execute_sqlite_query(
2612	sprintf(
2613	'DROP INDEX %s',
2614	$this->quote_sqlite_identifier( $sqlite_index_name )
2615	)
2616	);
2617	}
2618
2619	/**
2620	* Translate and execute a MySQL SHOW statement in SQLite.
2621	*
2622	* @param WP_Parser_Node $node The "showStatement" AST node.
2623	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2624	*/
2625	private function execute_show_statement( WP_Parser_Node $node ): void {
2626	$tokens = $node->get_child_tokens();
2627	$keyword1 = $tokens[1];
2628	$keyword2 = $tokens[2] ?? null;
2629
2630	switch ( $keyword1->id ) {
2631	case WP_MySQL_Lexer::COLLATION_SYMBOL:
2632	$this->execute_show_collation_statement( $node );
2633	return;
2634	case WP_MySQL_Lexer::DATABASES_SYMBOL:
2635	$this->execute_show_databases_statement( $node );
2636	return;
2637	case WP_MySQL_Lexer::COLUMNS_SYMBOL:
2638	case WP_MySQL_Lexer::FIELDS_SYMBOL:
2639	$this->execute_show_columns_statement( $node );
2640	return;
2641	case WP_MySQL_Lexer::CREATE_SYMBOL:
2642	if ( WP_MySQL_Lexer::TABLE_SYMBOL === $keyword2->id ) {
2643	$table_ref = $node->get_first_child_node( 'tableRef' );
2644	$database = $this->get_database_name( $table_ref );
2645	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2646
2647	// Refuse SHOW CREATE TABLE for information schema tables,
2648	// as we don't have the table definitions at the moment.
2649	if ( 'information_schema' === strtolower( $database ) ) {
2650	throw $this->new_driver_exception(
2651	sprintf( "SHOW command denied to user 'sqlite'@'%%' for table '%s'", $table_name ),
2652	'42000'
2653	);
2654	}
2655
2656	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2657
2658	$sql = $this->get_mysql_create_table_statement( $table_is_temporary, $table_name );
2659
2660	$this->last_column_meta = array(
2661	array(
2662	'native_type' => 'STRING',
2663	'pdo_type' => PDO::PARAM_STR,
2664	'flags' => array( 'not_null' ),
2665	'table' => '',
2666	'name' => 'Table',
2667	'len' => 256,
2668	'precision' => 31,
2669	),
2670	array(
2671	'native_type' => 'STRING',
2672	'pdo_type' => PDO::PARAM_STR,
2673	'flags' => array( 'not_null' ),
2674	'table' => '',
2675	'name' => 'Create Table',
2676	'len' => strlen( $sql ?? '' ),
2677	'precision' => 31,
2678	),
2679	);
2680
2681	$this->last_result_statement = $this->create_result_statement_from_data(
2682	array_column( $this->last_column_meta, 'name' ),
2683	null === $sql ? array() : array( array( $table_name, $sql ) )
2684	);
2685	$this->found_rows = null === $sql ? 0 : 1;
2686	return;
2687	}
2688	break;
2689	case WP_MySQL_Lexer::INDEX_SYMBOL:
2690	case WP_MySQL_Lexer::INDEXES_SYMBOL:
2691	case WP_MySQL_Lexer::KEYS_SYMBOL:
2692	$this->execute_show_index_statement( $node );
2693	return;
2694	case WP_MySQL_Lexer::GRANTS_SYMBOL:
2695	$this->last_result_statement = $this->create_result_statement_from_data(
2696	array( 'Grants for root@%' ),
2697	array( array( 'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO `root`@`localhost` WITH GRANT OPTION' ) )
2698	);
2699	$this->last_column_meta = array(
2700	array(
2701	'native_type' => 'STRING',
2702	'pdo_type' => PDO::PARAM_STR,
2703	'flags' => array( 'not_null' ),
2704	'table' => '',
2705	'name' => 'Grants for root@%',
2706	'len' => 4096,
2707	'precision' => 31,
2708	),
2709	);
2710	$this->found_rows = 1;
2711	return;
2712	case WP_MySQL_Lexer::TABLE_SYMBOL:
2713	$this->execute_show_table_status_statement( $node );
2714	return;
2715	case WP_MySQL_Lexer::TABLES_SYMBOL:
2716	$this->execute_show_tables_statement( $node );
2717	return;
2718	case WP_MySQL_Lexer::VARIABLES_SYMBOL:
2719	$this->last_column_meta = array(
2720	array(
2721	'native_type' => 'STRING',
2722	'pdo_type' => PDO::PARAM_STR,
2723	'flags' => array( 'not_null' ),
2724	'table' => 'session_variables',
2725	'name' => 'Variable_name',
2726	'len' => 256,
2727	'precision' => 0,
2728	),
2729	array(
2730	'native_type' => 'STRING',
2731	'pdo_type' => PDO::PARAM_STR,
2732	'flags' => array(),
2733	'table' => 'session_variables',
2734	'name' => 'Value',
2735	'len' => 4096,
2736	'precision' => 0,
2737	),
2738	);
2739	$this->last_result_statement = $this->create_result_statement_from_data(
2740	array_column( $this->last_column_meta, 'name' ),
2741	array()
2742	);
2743	$this->found_rows = 0;
2744	return;
2745	}
2746
2747	throw $this->new_not_supported_exception(
2748	sprintf(
2749	'statement type: "%s" > "%s"',
2750	$node->rule_name,
2751	$keyword1->get_value()
2752	)
2753	);
2754	}
2755
2756	/**
2757	* Translate and execute a MySQL SHOW COLLATION statement in SQLite.
2758	*
2759	* @param WP_Parser_Node $node The "showStatement" AST node.
2760	*/
2761	private function execute_show_collation_statement( WP_Parser_Node $node ): void {
2762	$definition = $this->information_schema_builder
2763	->get_computed_information_schema_table_definition( 'collations' );
2764
2765	// LIKE and WHERE clauses.
2766	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
2767	if ( $like_or_where ) {
2768	$condition = $this->translate_show_like_or_where_condition( $like_or_where, 'collation_name' );
2769	}
2770
2771	$query = sprintf(
2772	'SELECT
2773	COLLATION_NAME AS `Collation`,
2774	CHARACTER_SET_NAME AS `Charset`,
2775	ID AS `Id`,
2776	IS_DEFAULT AS `Default`,
2777	IS_COMPILED AS `Compiled`,
2778	SORTLEN AS `Sortlen`,
2779	PAD_ATTRIBUTE AS `Pad_attribute`
2780	FROM (%s)
2781	WHERE TRUE %s',
2782	$definition,
2783	$condition ?? ''
2784	);
2785	$stmt = $this->execute_sqlite_query( $query );
2786	$this->store_last_column_meta_from_statement( $stmt );
2787	$this->last_result_statement = $stmt;
2788	$this->found_rows = $query;
2789	}
2790
2791	/**
2792	* Translate and execute a MySQL SHOW DATABASES statement in SQLite.
2793	*
2794	* @param WP_Parser_Node $node The "showStatement" AST node.
2795	*/
2796	private function execute_show_databases_statement( WP_Parser_Node $node ): void {
2797	$schemata_table = $this->information_schema_builder->get_table_name( false, 'schemata' );
2798
2799	// LIKE and WHERE clauses.
2800	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
2801	if ( $like_or_where ) {
2802	$condition = $this->translate_show_like_or_where_condition( $like_or_where, 'schema_name' );
2803	}
2804	$query = sprintf(
2805	'SELECT SCHEMA_NAME AS Database
2806	FROM (
2807	SELECT CASE WHEN SCHEMA_NAME = ? THEN ? ELSE SCHEMA_NAME END AS SCHEMA_NAME
2808	FROM %s
2809	ORDER BY SCHEMA_NAME
2810	)%s',
2811	$this->quote_sqlite_identifier( $schemata_table ),
2812	isset( $condition ) ? ( ' WHERE TRUE ' . $condition ) : ''
2813	);
2814	$params = array(
2815	$this->get_saved_db_name(),
2816	$this->main_db_name,
2817	);
2818
2819	$stmt = $this->execute_sqlite_query( $query, $params );
2820	$this->store_last_column_meta_from_statement( $stmt );
2821	$this->last_result_statement = $stmt;
2822	$this->found_rows = array( $query, $params );
2823	}
2824
2825	/**
2826	* Translate and execute a MySQL SHOW INDEX statement in SQLite.
2827	*
2828	* @param WP_Parser_Node $node The "showStatement" AST node.
2829	*/
2830	private function execute_show_index_statement( WP_Parser_Node $node ): void {
2831	// Get database and table name.
2832	$table_ref = $node->get_first_child_node( 'tableRef' );
2833	$in_db = $node->get_first_child_node( 'inDb' );
2834	if ( $in_db ) {
2835	// FROM/IN database.
2836	$database = $this->get_database_name( $in_db );
2837	} else {
2838	$database = $this->get_database_name( $table_ref );
2839	}
2840	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2841
2842	// WHERE clause.
2843	$where = $node->get_first_child_node( 'whereClause' );
2844	if ( null !== $where ) {
2845	$value = $this->translate( $where->get_first_child_node( 'expr' ) );
2846	$condition = sprintf( 'AND %s', $value );
2847	} else {
2848	$condition = '';
2849	}
2850
2851	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2852
2853	/*
2854	* TODO: Index naming.
2855	*
2856	* From the old driver:
2857	*
2858	* SQLite automatically assigns names to some indexes.
2859	* However, dbDelta in WordPress expects the name to be
2860	* the same as in the original CREATE TABLE. Let's
2861	* translate the name back.
2862	*
2863	* The old driver does the two following conversions:
2864	* 1)
2865	* $mysql_key_name = substr( $mysql_key_name, strlen( 'sqlite_autoindex_' ) );
2866	* $mysql_key_name = preg_replace( '/_[0-9]+$/', '', $mysql_key_name );
2867	* 2)
2868	* $mysql_key_name = substr( $mysql_key_name, strlen( "{$table_name}__" ) );
2869	*/
2870
2871	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
2872	$query = sprintf(
2873	"
2874	SELECT
2875	TABLE_NAME AS `Table`,
2876	NON_UNIQUE AS `Non_unique`,
2877	INDEX_NAME AS `Key_name`,
2878	SEQ_IN_INDEX AS `Seq_in_index`,
2879	COLUMN_NAME AS `Column_name`,
2880	COLLATION AS `Collation`,
2881	CARDINALITY AS `Cardinality`,
2882	SUB_PART AS `Sub_part`,
2883	PACKED AS `Packed`,
2884	NULLABLE AS `Null`,
2885	INDEX_TYPE AS `Index_type`,
2886	COMMENT AS `Comment`,
2887	INDEX_COMMENT AS `Index_comment`,
2888	IS_VISIBLE AS `Visible`,
2889	EXPRESSION AS `Expression`
2890	FROM %s
2891	WHERE table_schema = ?
2892	AND table_name = ?
2893	%s
2894	ORDER BY
2895	INDEX_NAME = 'PRIMARY' DESC,
2896	NON_UNIQUE = '0' DESC,
2897	INDEX_TYPE = 'SPATIAL' DESC,
2898	INDEX_TYPE = 'BTREE' DESC,
2899	INDEX_TYPE = 'FULLTEXT' DESC,
2900	ROWID,
2901	SEQ_IN_INDEX
2902	",
2903	$this->quote_sqlite_identifier( $statistics_table ),
2904	$condition
2905	);
2906	$params = array(
2907	$this->get_saved_db_name( $database ),
2908	$table_name,
2909	);
2910
2911	$stmt = $this->execute_sqlite_query( $query, $params );
2912	$this->store_last_column_meta_from_statement( $stmt );
2913	$this->last_result_statement = $stmt;
2914	$this->found_rows = array( $query, $params );
2915	}
2916
2917	/**
2918	* Translate and execute a MySQL SHOW TABLE STATUS statement in SQLite.
2919	*
2920	* @param WP_Parser_Node $node The "showStatement" AST node.
2921	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2922	*/
2923	private function execute_show_table_status_statement( WP_Parser_Node $node ): void {
2924	// FROM/IN database.
2925	$in_db = $node->get_first_child_node( 'inDb' );
2926	if ( null === $in_db ) {
2927	$database = $this->db_name;
2928	} else {
2929	$database = $this->unquote_sqlite_identifier(
2930	$this->translate( $in_db->get_first_child_node( 'identifier' ) )
2931	);
2932	}
2933
2934	// LIKE and WHERE clauses.
2935	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
2936	if ( null !== $like_or_where ) {
2937	$condition = $this->translate_show_like_or_where_condition( $like_or_where, 'table_name' );
2938	}
2939
2940	// Fetch table information.
2941	$tables_tables = $this->information_schema_builder->get_table_name(
2942	false, // SHOW TABLE STATUS lists only non-temporary tables.
2943	'tables'
2944	);
2945	$query = sprintf(
2946	'SELECT
2947	table_name AS `Name`,
2948	engine AS `Engine`,
2949	version AS `Version`,
2950	row_format AS `Row_format`,
2951	table_rows AS `Rows`,
2952	avg_row_length AS `Avg_row_length`,
2953	data_length AS `Data_length`,
2954	max_data_length AS `Max_data_length`,
2955	index_length AS `Index_length`,
2956	data_free AS `Data_free`,
2957	auto_increment AS `Auto_increment`,
2958	create_time AS `Create_time`,
2959	update_time AS `Update_time`,
2960	check_time AS `Check_time`,
2961	table_collation AS `Collation`,
2962	checksum AS `Checksum`,
2963	create_options AS `Create_options`,
2964	table_comment AS `Comment`
2965	FROM %s
2966	WHERE table_schema = ? %s
2967	ORDER BY table_name',
2968	$this->quote_sqlite_identifier( $tables_tables ),
2969	$condition ?? ''
2970	);
2971	$params = array(
2972	$this->get_saved_db_name( $database ),
2973	);
2974
2975	$stmt = $this->execute_sqlite_query( $query, $params );
2976	$this->store_last_column_meta_from_statement( $stmt );
2977	$this->last_result_statement = $stmt;
2978	$this->found_rows = array( $query, $params );
2979	}
2980
2981	/**
2982	* Translate and execute a MySQL SHOW TABLES statement in SQLite.
2983	*
2984	* @param WP_Parser_Node $node The "showStatement" AST node.
2985	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2986	*/
2987	private function execute_show_tables_statement( WP_Parser_Node $node ): void {
2988	// FROM/IN database.
2989	$in_db = $node->get_first_child_node( 'inDb' );
2990	if ( null === $in_db ) {
2991	$database = $this->db_name;
2992	} else {
2993	$database = $this->unquote_sqlite_identifier(
2994	$this->translate( $in_db->get_first_child_node( 'identifier' ) )
2995	);
2996	}
2997
2998	// LIKE and WHERE clauses.
2999	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
3000	if ( null !== $like_or_where ) {
3001	$condition = $this->translate_show_like_or_where_condition( $like_or_where, 'table_name' );
3002	}
3003
3004	// Handle the FULL keyword.
3005	$command_type = $node->get_first_child_node( 'showCommandType' );
3006	$is_full = $command_type && $command_type->has_child_token( WP_MySQL_Lexer::FULL_SYMBOL );
3007
3008	// Fetch table information.
3009	$table_tables = $this->information_schema_builder->get_table_name(
3010	false, // SHOW TABLES lists only non-temporary tables.
3011	'tables'
3012	);
3013	$query = sprintf(
3014	'SELECT %s FROM %s WHERE table_schema = ? %s ORDER BY table_name',
3015	$is_full
3016	? sprintf( 'table_name AS `Tables_in_%s`, table_type AS `Table_type`', $database )
3017	: sprintf( 'table_name AS `Tables_in_%s`', $database ),
3018	$this->quote_sqlite_identifier( $table_tables ),
3019	$condition ?? ''
3020	);
3021	$params = array(
3022	$this->get_saved_db_name( $database ),
3023	);
3024
3025	$stmt = $this->execute_sqlite_query( $query, $params );
3026	$this->store_last_column_meta_from_statement( $stmt );
3027	$this->last_result_statement = $stmt;
3028	$this->found_rows = array( $query, $params );
3029	}
3030
3031	/**
3032	* Translate and execute a MySQL SHOW COLUMNS statement in SQLite.
3033	*
3034	* @param WP_Parser_Node $node The "showStatement" AST node.
3035	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3036	* @throws PDOException When given table doesn't exist.
3037	*/
3038	private function execute_show_columns_statement( WP_Parser_Node $node ): void {
3039	// TODO: EXTENDED, FULL
3040
3041	// Get database and table name.
3042	$table_ref = $node->get_first_child_node( 'tableRef' );
3043	$in_db = $node->get_first_child_node( 'inDb' );
3044	if ( $in_db ) {
3045	// FROM/IN database.
3046	$database = $this->get_database_name( $in_db );
3047	} else {
3048	$database = $this->get_database_name( $table_ref );
3049	}
3050	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
3051	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
3052
3053	// Check if the table exists.
3054	$tables_tables = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
3055	$table_exists = $this->execute_sqlite_query(
3056	sprintf(
3057	'SELECT 1 FROM %s WHERE table_schema = ? AND table_name = ?',
3058	$this->quote_sqlite_identifier( $tables_tables )
3059	),
3060	array( $this->get_saved_db_name( $database ), $table_name )
3061	)->fetchColumn();
3062
3063	if ( ! $table_exists ) {
3064	throw $this->new_driver_exception(
3065	sprintf( "Table '%s.%s' doesn't exist", $database, $table_name ),
3066	'42S02'
3067	);
3068	}
3069
3070	// LIKE and WHERE clauses.
3071	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
3072	if ( null !== $like_or_where ) {
3073	$condition = $this->translate_show_like_or_where_condition( $like_or_where, 'column_name' );
3074	}
3075
3076	// Handle the FULL keyword.
3077	$command_type = $node->get_first_child_node( 'showCommandType' );
3078	$is_full = $command_type && $command_type->has_child_token( WP_MySQL_Lexer::FULL_SYMBOL );
3079
3080	// Fetch column information.
3081	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
3082
3083	if ( $is_full ) {
3084	$fields = '
3085	column_name AS `Field`,
3086	column_type AS `Type`,
3087	collation_name AS `Collation`,
3088	is_nullable AS `Null`,
3089	column_key AS `Key`,
3090	column_default AS `Default`,
3091	extra AS `Extra`,
3092	privileges AS `Privileges`,
3093	column_comment AS `Comment`
3094	';
3095	} else {
3096	$fields = '
3097	column_name AS `Field`,
3098	column_type AS `Type`,
3099	is_nullable AS `Null`,
3100	column_key AS `Key`,
3101	column_default AS `Default`,
3102	extra AS `Extra`
3103	';
3104	}
3105
3106	$query = sprintf(
3107	'SELECT %s
3108	FROM %s
3109	WHERE table_schema = ? AND table_name = ? %s
3110	ORDER BY ordinal_position',
3111	$fields,
3112	$this->quote_sqlite_identifier( $columns_table ),
3113	$condition ?? ''
3114	);
3115	$params = array(
3116	$this->get_saved_db_name( $database ),
3117	$table_name,
3118	);
3119
3120	$stmt = $this->execute_sqlite_query( $query, $params );
3121	$this->store_last_column_meta_from_statement( $stmt );
3122	$this->last_result_statement = $stmt;
3123	$this->found_rows = array( $query, $params );
3124	}
3125
3126	/**
3127	* Translate and execute a MySQL DESCRIBE statement in SQLite.
3128	*
3129	* @param WP_Parser_Node $node The "describeStatement" AST node.
3130	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3131	*/
3132	private function execute_describe_statement( WP_Parser_Node $node ): void {
3133	$table_ref = $node->get_first_child_node( 'tableRef' );
3134	$database = $this->get_database_name( $table_ref );
3135	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
3136
3137	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
3138
3139	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
3140	$query = sprintf(
3141	'SELECT
3142	column_name AS `Field`,
3143	column_type AS `Type`,
3144	is_nullable AS `Null`,
3145	column_key AS `Key`,
3146	column_default AS `Default`,
3147	extra AS `Extra`
3148	FROM %s
3149	WHERE table_schema = ?
3150	AND table_name = ?
3151	ORDER BY ordinal_position',
3152	$this->quote_sqlite_identifier( $columns_table )
3153	);
3154	$params = array(
3155	$this->get_saved_db_name( $database ),
3156	$table_name,
3157	);
3158
3159	$stmt = $this->execute_sqlite_query( $query, $params );
3160	$this->store_last_column_meta_from_statement( $stmt );
3161	$this->last_result_statement = $stmt;
3162	$this->found_rows = array( $query, $params );
3163	}
3164
3165	/**
3166	* Translate and execute a MySQL USE statement in SQLite.
3167	*
3168	* @param WP_Parser_Node $node The "useStatement" AST node.
3169	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3170	*/
3171	private function execute_use_statement( WP_Parser_Node $node ): void {
3172	$database_name = $this->unquote_sqlite_identifier(
3173	$this->translate( $node->get_first_child_node( 'identifier' ) )
3174	);
3175	$database_name = strtolower( $database_name );
3176
3177	if ( $this->main_db_name === $database_name \|\| 'information_schema' === $database_name ) {
3178	$this->db_name = $database_name;
3179	} else {
3180	throw $this->new_not_supported_exception(
3181	sprintf(
3182	"can't use schema '%s', only '%s' and 'information_schema' are supported",
3183	$database_name,
3184	$this->db_name
3185	)
3186	);
3187	}
3188	}
3189
3190	/**
3191	* Translate and execute a MySQL SET statement in SQLite.
3192	*
3193	* @param WP_Parser_Node $node The "setStatement" AST node.
3194	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3195	*/
3196	private function execute_set_statement( WP_Parser_Node $node ): void {
3197	/*
3198	* 1. Flatten the SET statement into a single array of definitions.
3199	*
3200	* The grammar is non-trivial, and supports multi-statements like:
3201	* SET @var = '...', SESSION sql_mode = '...', @@GLOBAL.time_zone = '...', @@debug = '...', ...
3202	*
3203	* This will be flattened into a single array of grammar node lists:
3204	* [
3205	* [ <userVariable>, <equal>, <expr> ],
3206	* [ <optionType>, <internalVariableName>, <equal>, <setExprOrDefault> ],
3207	* [ <setSystemVariable>, <equal>, <setExprOrDefault> ],
3208	* [ <setSystemVariable>, <equal>, <setExprOrDefault> ],
3209	* ]
3210	*/
3211	$subnode = $node->get_first_child_node();
3212	if ( $subnode->has_child_node( 'optionValueNoOptionType' ) ) {
3213	$start_node = $subnode->get_first_child_node( 'optionValueNoOptionType' );
3214	$definitions = array( $start_node->get_children() );
3215	} elseif ( $subnode->has_child_node( 'startOptionValueListFollowingOptionType' ) ) {
3216	$start_node = $subnode
3217	->get_first_child_node( 'startOptionValueListFollowingOptionType' )
3218	->get_first_child_node( 'optionValueFollowingOptionType' ) ?? $node;
3219	$definitions = array(
3220	array_merge(
3221	array( $subnode->get_first_child_node( 'optionType' ) ),
3222	$start_node->get_children()
3223	),
3224	);
3225	} else {
3226	$definitions = array( $subnode->get_children() );
3227	}
3228
3229	$continue_node = $subnode->get_first_child_node( 'optionValueListContinued' );
3230	if ( $continue_node ) {
3231	foreach ( $continue_node->get_child_nodes( 'optionValue' ) as $child ) {
3232	$node = $child->get_first_child_node( 'optionValueNoOptionType' ) ?? $child;
3233	$definitions[] = $node->get_child_nodes();
3234	}
3235	}
3236
3237	/*
3238	* 2. Iterate and process the SET definitions.
3239	*
3240	* When an "optionType" node is encountered (such as "SESSION var = ..."),
3241	* it's value is used for all following system variable assignments that
3242	* have no type keyword specified, until the next "optionType" is found.
3243	*
3244	* This doesn't apply to "@@" type prefixes (such as "@@SESSION.var_name"),
3245	* which always impact only the immediately following system variable.
3246	*/
3247	$default_type = WP_MySQL_Lexer::SESSION_SYMBOL;
3248	foreach ( $definitions as $definition ) {
3249	// Check if the definition starts with an "optionType" node with
3250	// one of the SESSION, GLOBAL, PERSIST, or PERSIST_ONLY tokens.
3251	$part = array_shift( $definition );
3252	if ( $part instanceof WP_Parser_Node && 'optionType' === $part->rule_name ) {
3253	$default_type = $part->get_first_child_token()->id;
3254	$part = array_shift( $definition );
3255	}
3256
3257	if (
3258	$part instanceof WP_MySQL_Token
3259	&& WP_MySQL_Lexer::NAMES_SYMBOL === $part->id
3260	) {
3261	// "SET NAMES ..." is a no-op for now.
3262	// TODO: Validate charset compatibility with UTF-8.
3263	// See: https://github.com/WordPress/sqlite-database-integration/issues/192
3264	} elseif (
3265	$part instanceof WP_Parser_Node
3266	&& 'charsetClause' === $part->rule_name
3267	) {
3268	// "SET CHARACTER SET ..." is a no-op for now.
3269	// TODO: Validate charset compatibility with UTF-8.
3270	// See: https://github.com/WordPress/sqlite-database-integration/issues/192
3271	} elseif (
3272	$part instanceof WP_Parser_Node
3273	&& (
3274	'internalVariableName' === $part->rule_name
3275	\|\| 'setSystemVariable' === $part->rule_name
3276	)
3277	) {
3278	// Set a system variable.
3279	array_shift( $definition ); // Remove the '='.
3280	$value = array_shift( $definition );
3281	$this->execute_set_system_variable_statement( $part, $value, $default_type );
3282	} elseif (
3283	$part instanceof WP_Parser_Node
3284	&& 'userVariable' === $part->rule_name
3285	) {
3286	// Set a user variable.
3287	array_shift( $definition ); // Remove the '='.
3288	$value = array_shift( $definition );
3289	$this->execute_set_user_variable_statement( $part, $value );
3290	} else {
3291	throw $this->new_not_supported_exception(
3292	sprintf( 'SET statement: %s', $node->rule_name )
3293	);
3294	}
3295	}
3296
3297	$this->last_result_statement = $this->create_result_statement_from_data( array(), array() );
3298	}
3299
3300	/**
3301	* Translate and execute a MySQL SET statement for system variables.
3302	*
3303	* @param WP_Parser_Node $set_var_node The "internalVariableName" or "setSystemVariable" AST node.
3304	* @param WP_Parser_Node $value_node The "setExprOrDefault" AST node.
3305	* @param int $default_type The currently active default variable type.
3306	* One of the SESSION, GLOBAL, PERSIST, PERSIST_ONLY tokens.
3307	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3308	*/
3309	private function execute_set_system_variable_statement(
3310	WP_Parser_Node $set_var_node,
3311	WP_Parser_Node $value_node,
3312	int $default_type
3313	): void {
3314	// Get the variable name.
3315	$internal_variable_name = 'setSystemVariable' === $set_var_node->rule_name
3316	? $set_var_node->get_first_child_node( 'internalVariableName' )
3317	: $set_var_node;
3318
3319	$name = strtolower(
3320	$this->unquote_sqlite_identifier(
3321	$this->translate( $internal_variable_name )
3322	)
3323	);
3324
3325	// Get the type attribute (one of SESSION, GLOBAL, PERSIST, PERSIST_ONLY).
3326	$type = $default_type;
3327	if ( $set_var_node->has_child_node( 'setVarIdentType' ) ) {
3328	$var_ident_type = $set_var_node->get_first_child_node( 'setVarIdentType' );
3329	$type = $var_ident_type->get_first_child_token()->id;
3330	}
3331
3332	/*
3333	* Some MySQL system variables values can be set using an unquoted pure
3334	* identifier rather than a string literal. This includes non-reserved
3335	* keywords. This is equivalent to using a corresponding string literal.
3336	*
3337	* For example, the following statement pairs are equivalent:
3338	*
3339	* SET default_storage_engine = InnoDB
3340	* SET default_storage_engine = 'InnoDB'
3341	*
3342	* SET default_collation_for_utf8mb4 = utf8mb4_0900_ai_ci
3343	* SET default_collation_for_utf8mb4 = 'utf8mb4_0900_ai_ci'
3344	*
3345	* In this cases, we need to use the value directly without attempting
3346	* to evaluate the expression, as that would result in a query error.
3347	* In the grammar, unquoted identifiers are captured by "columnRef".
3348	*/
3349	$identifier = $this->translate( $value_node->get_first_descendant_node( 'columnRef' ) );
3350	if ( $identifier && $identifier === $this->translate( $value_node ) ) {
3351	$value = $this->unquote_sqlite_identifier( $identifier );
3352	} elseif ( ! $value_node->has_child_node( 'expr' ) ) {
3353	$value = $this->unquote_sqlite_identifier( $this->translate( $value_node ) );
3354	} else {
3355	$value = $this->evaluate_expression( $value_node );
3356	}
3357
3358	/*
3359	* Handle ON/OFF values. They are accepted as both strings and keywords.
3360	*
3361	* @TODO: This is actually variable-specific and depends on the its type.
3362	* For example:
3363	* SET autocommit = OFF; SELECT @@autocommit; -> 0
3364	* SET autocommit = false; SELECT @@autocommit; -> 0
3365	* SET session_track_gtids = OFF; SELECT @@session_track_gtids; -> OFF
3366	* SET session_track_gtids = false; SELECT @@session_track_gtids; -> OFF
3367	* SET updatable_views_with_limit = OFF; ERROR 1231 (42000)
3368	* SET updatable_views_with_limit = false; SELECT @@updatable_views_with_limit; -> NO
3369	*/
3370	$lowercase_value = null === $value ? null : strtolower( $value );
3371	if ( 'on' === $lowercase_value \|\| 'off' === $lowercase_value ) {
3372	$value = 'on' === $lowercase_value ? 1 : 0;
3373	}
3374
3375	if ( WP_MySQL_Lexer::SESSION_SYMBOL === $type ) {
3376	if ( 'sql_mode' === $name ) {
3377	$modes = explode( ',', strtoupper( $value ) );
3378	$this->active_sql_modes = $modes;
3379	} else {
3380	$this->session_system_variables[ $name ] = $value;
3381	}
3382	} elseif ( WP_MySQL_Lexer::GLOBAL_SYMBOL === $type ) {
3383	throw $this->new_not_supported_exception( "SET statement type: 'GLOBAL'" );
3384	} elseif ( WP_MySQL_Lexer::PERSIST_SYMBOL === $type ) {
3385	throw $this->new_not_supported_exception( "SET statement type: 'PERSIST'" );
3386	} elseif ( WP_MySQL_Lexer::PERSIST_ONLY_SYMBOL === $type ) {
3387	throw $this->new_not_supported_exception( "SET statement type: 'PERSIST_ONLY'" );
3388	}
3389
3390	// TODO: Handle GLOBAL, PERSIST, and PERSIST_ONLY types.
3391	}
3392
3393	/**
3394	* Translate and execute a MySQL SET statement for user variables.
3395	*
3396	* @param WP_Parser_Node $user_variable The "userVariable" AST node.
3397	* @param WP_Parser_Node $expr The "expr" AST node.
3398	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3399	*/
3400	private function execute_set_user_variable_statement(
3401	WP_Parser_Node $user_variable,
3402	WP_Parser_Node $expr
3403	): void {
3404	$name = $this->unquote_sqlite_identifier(
3405	$this->translate( $user_variable->get_first_child() )
3406	);
3407	$name = strtolower( substr( $name, 1 ) ); // Remove '@', normalize case.
3408	$value = $this->evaluate_expression( $expr );
3409
3410	$this->user_variables[ $name ] = $value;
3411	}
3412
3413	/**
3414	* Translate and execute a MySQL administration statement in SQLite.
3415	*
3416	* This emulates the following MySQL statements:
3417	* - ANALYZE TABLE
3418	* - CHECK TABLE
3419	* - OPTIMIZE TABLE
3420	* - REPAIR TABLE
3421	*
3422	* @param WP_Parser_Node $node A "tableAdministrationStatement" AST node.
3423	* @throws WP_SQLite_Driver_Exception When the query execution fails.
3424	*/
3425	private function execute_administration_statement( WP_Parser_Node $node ): void {
3426	$first_token = $node->get_first_child_token();
3427	$table_ref_list = $node->get_first_child_node( 'tableRefList' );
3428	$results = array();
3429	foreach ( $table_ref_list->get_child_nodes( 'tableRef' ) as $table_ref ) {
3430	$database = $this->get_database_name( $table_ref );
3431	if ( 'information_schema' === strtolower( $database ) ) {
3432	throw $this->new_access_denied_to_information_schema_exception();
3433	}
3434
3435	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
3436	$quoted_table_name = $this->quote_sqlite_identifier( $table_name );
3437	try {
3438	switch ( $first_token->id ) {
3439	case WP_MySQL_Lexer::ANALYZE_SYMBOL:
3440	$stmt = $this->execute_sqlite_query( sprintf( 'ANALYZE %s', $quoted_table_name ) );
3441	$errors = $stmt->fetchAll( PDO::FETCH_COLUMN );
3442	break;
3443	case WP_MySQL_Lexer::CHECK_SYMBOL:
3444	$stmt = $this->execute_sqlite_query(
3445	sprintf( 'PRAGMA integrity_check(%s)', $quoted_table_name )
3446	);
3447	$errors = $stmt->fetchAll( PDO::FETCH_COLUMN );
3448	if ( 'ok' === $errors[0] ) {
3449	array_shift( $errors );
3450	}
3451	break;
3452	case WP_MySQL_Lexer::OPTIMIZE_SYMBOL:
3453	case WP_MySQL_Lexer::REPAIR_SYMBOL:
3454	/*
3455	* SQLite doesn't support OPTIMIZE and REPAIR TABLE commands.
3456	* We will recreate the table and copy the data instead.
3457	* This corresponds to older MySQL OPTIMIZE TABLE behavior
3458	* and still applies to some storage engines in some cases.
3459	*/
3460	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
3461	$this->recreate_table_from_information_schema( $table_is_temporary, $table_name );
3462	$errors = array();
3463	break;
3464	default:
3465	throw $this->new_not_supported_exception(
3466	sprintf(
3467	'statement type: "%s" > "%s"',
3468	$node->rule_name,
3469	$first_token->get_value()
3470	)
3471	);
3472	}
3473	} catch ( PDOException $e ) {
3474	if ( 'HY000' === $e->getCode() ) {
3475	$errors = array( "Table '$table_name' doesn't exist" );
3476	} else {
3477	$errors = array( $e->getMessage() );
3478	}
3479	}
3480
3481	$operation = strtolower( $first_token->get_value() );
3482	foreach ( $errors as $error ) {
3483	$results[] = array(
3484	'Table' => $this->db_name . '.' . $table_name,
3485	'Op' => $operation,
3486	'Msg_type' => 'Error',
3487	'Msg_text' => $error,
3488	);
3489	}
3490	$results[] = array(
3491	'Table' => $this->db_name . '.' . $table_name,
3492	'Op' => $operation,
3493	'Msg_type' => 'status',
3494	'Msg_text' => count( $errors ) > 0 ? 'Operation failed' : 'OK',
3495	);
3496	}
3497
3498	$this->last_column_meta = array(
3499	array(
3500	'native_type' => 'STRING',
3501	'pdo_type' => PDO::PARAM_STR,
3502	'flags' => array(),
3503	'table' => '',
3504	'name' => 'Table',
3505	'len' => 512,
3506	'precision' => 31,
3507	),
3508	array(
3509	'native_type' => 'STRING',
3510	'pdo_type' => PDO::PARAM_STR,
3511	'flags' => array(),
3512	'table' => '',
3513	'name' => 'Op',
3514	'len' => 40,
3515	'precision' => 31,
3516	),
3517	array(
3518	'native_type' => 'STRING',
3519	'pdo_type' => PDO::PARAM_STR,
3520	'flags' => array(),
3521	'table' => '',
3522	'name' => 'Msg_type',
3523	'len' => 40,
3524	'precision' => 31,
3525	),
3526	array(
3527	'native_type' => 'TEXT',
3528	'pdo_type' => PDO::PARAM_STR,
3529	'flags' => array(),
3530	'table' => '',
3531	'name' => 'Msg_text',
3532	'len' => 1572864,
3533	'precision' => 31,
3534	),
3535	);
3536	$this->last_result_statement = $this->create_result_statement_from_data(
3537	array_column( $this->last_column_meta, 'name' ),
3538	$results
3539	);
3540	}
3541
3542	/**
3543	* Evaluate an expression and return the value, preserving its type.
3544	*
3545	* This is used to support expressions in SET statements for MySQL variables.
3546	*
3547	* @param WP_Parser_Node $node The "expr" AST node.
3548	* @return mixed The value of the expression.
3549	*/
3550	public function evaluate_expression( WP_Parser_Node $node ) {
3551	// To support expressions, we'll use a SQLite query.
3552	$stmt = $this->execute_sqlite_query(
3553	sprintf( 'SELECT %s', $this->translate( $node ) )
3554	);
3555
3556	// MySQL variables are typed, so we need to preserve the value type.
3557	$value = $stmt->fetchColumn();
3558	$type = $stmt->getColumnMeta( 0 )['native_type'];
3559	if ( 'null' === $type ) {
3560	return null;
3561	} elseif ( 'integer' === $type ) {
3562	return (int) $value;
3563	} elseif ( 'double' === $type ) {
3564	return (float) $value;
3565	}
3566	return $value;
3567	}
3568
3569	/**
3570	* Translate a MySQL AST node or token to an SQLite query fragment.
3571	*
3572	* @param WP_Parser_Node\|WP_MySQL_Token $node The AST node to translate.
3573	* @return string\|null The translated query fragment.
3574	* @throws WP_SQLite_Driver_Exception When the translation fails.
3575	*/
3576	private function translate( $node ): ?string {
3577	if ( null === $node ) {
3578	return null;
3579	}
3580
3581	if ( $node instanceof WP_MySQL_Token ) {
3582	return $this->translate_token( $node );
3583	}
3584
3585	if ( ! $node instanceof WP_Parser_Node ) {
3586	throw $this->new_driver_exception(
3587	sprintf(
3588	'Expected a WP_Parser_Node or WP_MySQL_Token instance, got: %s',
3589	gettype( $node )
3590	)
3591	);
3592	}
3593
3594	$rule_name = $node->rule_name;
3595	switch ( $rule_name ) {
3596	case 'queryExpression':
3597	return $this->translate_query_expression( $node );
3598	case 'querySpecification':
3599	return $this->translate_query_specification( $node );
3600	case 'tableRef':
3601	return $this->translate_table_ref( $node );
3602	case 'qualifiedIdentifier':
3603	case 'tableRefWithWildcard':
3604	$parts = $node->get_descendant_nodes( 'identifier' );
3605	if ( count( $parts ) === 2 ) {
3606	return $this->translate_qualified_identifier( $parts[0], $parts[1] );
3607	}
3608	return $this->translate_qualified_identifier( null, $parts[0] );
3609	case 'fieldIdentifier':
3610	case 'simpleIdentifier':
3611	$parts = $node->get_descendant_nodes( 'identifier' );
3612	if ( count( $parts ) === 3 ) {
3613	return $this->translate_qualified_identifier( $parts[0], $parts[1], $parts[2] );
3614	} elseif ( count( $parts ) === 2 ) {
3615	return $this->translate_qualified_identifier( null, $parts[0], $parts[1] );
3616	}
3617	return $this->translate_qualified_identifier( null, null, $parts[0] );
3618	case 'tableWild':
3619	$parts = $node->get_descendant_nodes( 'identifier' );
3620	if ( count( $parts ) === 2 ) {
3621	return $this->translate_qualified_identifier( $parts[0], $parts[1] ) . '.*';
3622	}
3623	return $this->translate_qualified_identifier( null, $parts[0] ) . '.*';
3624	case 'dotIdentifier':
3625	return $this->translate_sequence( $node->get_children(), '' );
3626	case 'identifierKeyword':
3627	return '`' . $this->translate( $node->get_first_child() ) . '`';
3628	case 'pureIdentifier':
3629	$value = $this->translate_pure_identifier( $node );
3630
3631	/*
3632	* At the moment, we only support ASCII bytes in all identifiers.
3633	* This is because SQLite doesn't support case-insensitive Unicode
3634	* character matching: https://sqlite.org/faq.html#q18
3635	*/
3636	for ( $i = 0; $i < strlen( $value ); $i++ ) {
3637	if ( ord( $value[ $i ] ) > 127 ) {
3638	throw $this->new_driver_exception(
3639	'The SQLite driver only supports ASCII characters in identifiers.'
3640	);
3641	}
3642	}
3643	return $value;
3644	case 'textStringLiteral':
3645	return $this->translate_string_literal( $node );
3646	case 'dataType':
3647	case 'nchar':
3648	$child = $node->get_first_child();
3649	if ( $child instanceof WP_Parser_Node ) {
3650	return $this->translate( $child );
3651	}
3652
3653	// Handle optional prefixes (data type is the second token):
3654	// 1. LONG VARCHAR, LONG CHAR(ACTER) VARYING, LONG VARBINARY.
3655	// 2. NATIONAL CHAR, NATIONAL VARCHAR, NATIONAL CHAR(ACTER) VARYING.
3656	if ( WP_MySQL_Lexer::LONG_SYMBOL === $child->id ) {
3657	$child = $node->get_child_tokens()[1] ?? null;
3658	} elseif ( WP_MySQL_Lexer::NATIONAL_SYMBOL === $child->id ) {
3659	$child = $node->get_child_tokens()[1] ?? null;
3660	}
3661
3662	if ( null === $child ) {
3663	throw $this->new_invalid_input_exception();
3664	}
3665
3666	$type_token = self::DATA_TYPE_MAP[ $child->id ] ?? null;
3667	if ( null !== $type_token ) {
3668	return $type_token;
3669	}
3670
3671	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
3672	if ( WP_MySQL_Lexer::SERIAL_SYMBOL === $child->id ) {
3673	return 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE';
3674	}
3675
3676	// @TODO: Handle SET and JSON.
3677	throw $this->new_not_supported_exception(
3678	sprintf( 'data type: %s', $child->get_value() )
3679	);
3680	case 'selectItem':
3681	return $this->translate_select_item( $node );
3682	case 'fromClause':
3683	// FROM DUAL is MySQL-specific syntax that means "FROM no tables"
3684	// and it is equivalent to omitting the FROM clause entirely.
3685	if ( $node->has_child_token( WP_MySQL_Lexer::DUAL_SYMBOL ) ) {
3686	return null;
3687	}
3688	return $this->translate_sequence( $node->get_children() );
3689	case 'simpleExpr':
3690	return $this->translate_simple_expr( $node );
3691	case 'predicateOperations':
3692	$token = $node->get_first_child_token();
3693	if ( WP_MySQL_Lexer::LIKE_SYMBOL === $token->id ) {
3694	return $this->translate_like( $node );
3695	} elseif ( WP_MySQL_Lexer::REGEXP_SYMBOL === $token->id ) {
3696	return $this->translate_regexp_functions( $node );
3697	}
3698	return $this->translate_sequence( $node->get_children() );
3699	case 'runtimeFunctionCall':
3700	return $this->translate_runtime_function_call( $node );
3701	case 'functionCall':
3702	return $this->translate_function_call( $node );
3703	case 'substringFunction':
3704	$nodes = $node->get_child_nodes();
3705	if ( count( $nodes ) === 2 ) {
3706	return sprintf(
3707	'SUBSTR(%s, %s)',
3708	$this->translate( $nodes[0] ),
3709	$this->translate( $nodes[1] )
3710	);
3711	} else {
3712	return sprintf(
3713	'SUBSTR(%s, %s, %s)',
3714	$this->translate( $nodes[0] ),
3715	$this->translate( $nodes[1] ),
3716	$this->translate( $nodes[2] )
3717	);
3718	}
3719	case 'systemVariable':
3720	$var_ident_type = $node->get_first_child_node( 'varIdentType' );
3721	$type_token = $var_ident_type ? $var_ident_type->get_first_child_token() : null;
3722	$original_name = $this->unquote_sqlite_identifier(
3723	$this->translate( $node->get_first_child_node( 'textOrIdentifier' ) )
3724	);
3725
3726	$name = strtolower( $original_name );
3727	$type = $type_token ? $type_token->id : WP_MySQL_Lexer::SESSION_SYMBOL;
3728	if ( 'sql_mode' === $name ) {
3729	$value = implode( ',', $this->active_sql_modes );
3730	} elseif ( 'version' === $name ) {
3731	$version = (string) $this->mysql_version;
3732	$value = sprintf(
3733	'%d.%d.%d',
3734	$version[0],
3735	substr( $version, 1, 2 ),
3736	substr( $version, 3, 2 )
3737	);
3738	} elseif ( 'version_comment' === $name ) {
3739	$value = 'MySQL Community Server - GPL';
3740	} elseif ( WP_MySQL_Lexer::SESSION_SYMBOL === $type ) {
3741	$value = $this->session_system_variables[ $name ] ?? null;
3742	} else {
3743	// When we have no value, it's reasonable to use NULL.
3744	$value = null;
3745	}
3746
3747	// @TODO: Emulate more system variables, or use reasonable defaults.
3748	// See: https://dev.mysql.com/doc/refman/8.4/en/server-system-variable-reference.html
3749	// See: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html
3750	if ( null === $value ) {
3751	return 'NULL';
3752	}
3753	if ( is_string( $value ) ) {
3754	return $this->quote_sqlite_value( $value );
3755	}
3756	return (string) $value;
3757	case 'userVariable':
3758	$name = $this->unquote_sqlite_identifier( $this->translate( $node->get_first_child() ) );
3759	$name = strtolower( substr( $name, 1 ) ); // Remove '@', normalize case.
3760	$value = $this->user_variables[ $name ] ?? null;
3761	if ( null === $value ) {
3762	return 'NULL';
3763	}
3764	if ( is_string( $value ) ) {
3765	return $this->quote_sqlite_value( $value );
3766	}
3767	return (string) $value;
3768	case 'castType':
3769	$first_child = $node->get_first_child();
3770	if ( $first_child instanceof WP_Parser_Node ) {
3771	$first_token = $first_child->get_first_child_token();
3772	} else {
3773	$first_token = $first_child;
3774	}
3775	switch ( $first_token->id ) {
3776	case WP_MySQL_Lexer::BINARY_SYMBOL:
3777	return 'BLOB';
3778	case WP_MySQL_Lexer::CHAR_SYMBOL:
3779	case WP_MySQL_Lexer::NCHAR_SYMBOL:
3780	case WP_MySQL_Lexer::NATIONAL_SYMBOL:
3781	case WP_MySQL_Lexer::DATE_SYMBOL:
3782	case WP_MySQL_Lexer::TIME_SYMBOL:
3783	case WP_MySQL_Lexer::DATETIME_SYMBOL:
3784	case WP_MySQL_Lexer::JSON_SYMBOL:
3785	return 'TEXT';
3786	case WP_MySQL_Lexer::SIGNED_SYMBOL:
3787	case WP_MySQL_Lexer::UNSIGNED_SYMBOL:
3788	return 'INTEGER';
3789	case WP_MySQL_Lexer::DECIMAL_SYMBOL:
3790	case WP_MySQL_Lexer::FLOAT_SYMBOL:
3791	case WP_MySQL_Lexer::REAL_SYMBOL:
3792	case WP_MySQL_Lexer::DOUBLE_SYMBOL:
3793	return 'REAL';
3794	default:
3795	throw $this->new_not_supported_exception(
3796	sprintf( 'cast type: %s', $first_child->get_value() )
3797	);
3798	}
3799	case 'defaultCollation':
3800	// @TODO: Check and save in information schema.
3801	return null;
3802	case 'duplicateAsQueryExpression':
3803	// @TODO: How to handle IGNORE/REPLACE?
3804
3805	// The "AS" keyword is optional in MySQL, but required in SQLite.
3806	return 'AS ' . $this->translate( $node->get_first_child_node() );
3807	case 'indexHint':
3808	case 'indexHintList':
3809	return null;
3810	case 'lockingClause':
3811	// SQLite doesn't support locking clauses (SELECT ... FOR UPDATE).
3812	// They are not needed in SQLite due to the database file locking.
3813	return null;
3814	default:
3815	return $this->translate_sequence( $node->get_children() );
3816	}
3817	}
3818
3819	/**
3820	* Translate a MySQL token to SQLite.
3821	*
3822	* @param WP_MySQL_Token $token The MySQL token to translate.
3823	* @return string\|null The translated value.
3824	*/
3825	private function translate_token( WP_MySQL_Token $token ): ?string {
3826	switch ( $token->id ) {
3827	case WP_MySQL_Lexer::EOF:
3828	return null;
3829	case WP_MySQL_Lexer::BIN_NUMBER:
3830	/*
3831	* There are no binary literals in SQLite. We need to convert all
3832	* MySQL binary string values to HEX strings in SQLite (x'...').
3833	*/
3834	$value = $token->get_value();
3835	if ( '0' === $value[0] ) {
3836	// 0b...
3837	$value = substr( $value, 2 );
3838	} else {
3839	// b'...' or B'...'
3840	$value = substr( $value, 2, -1 );
3841	}
3842
3843	// Convert the binary string to HEX.
3844	$hex = base_convert( $value, 2, 16 );
3845
3846	/*
3847	* The "base_convert()" function doesn't add or preserve padding.
3848	* Let's compute how many bytes we expect and pad the HEX value
3849	* to full bytes (SQLite requires HEX strings of even length).
3850	*/
3851	$byte_count = (int) ceil( strlen( $value ) / 8 );
3852	$hex = str_pad( $hex, $byte_count * 2, '0', STR_PAD_LEFT );
3853	return sprintf( "x'%s'", $hex );
3854	case WP_MySQL_Lexer::HEX_NUMBER:
3855	/*
3856	* In MySQL, "0x" prefixed values represent binary literal values,
3857	* while in SQLite, that would be a hexadecimal number. Therefore,
3858	* we need to convert the 0x... syntax to x'...'.
3859	*/
3860	$value = $token->get_value();
3861	if ( '0' === $value[0] && 'x' === $value[1] ) {
3862	return sprintf( "x'%s'", substr( $value, 2 ) );
3863	}
3864	return $value;
3865	case WP_MySQL_Lexer::AUTO_INCREMENT_SYMBOL:
3866	return 'AUTOINCREMENT';
3867	case WP_MySQL_Lexer::BINARY_SYMBOL:
3868	/*
3869	* There is no "BINARY expr" equivalent in SQLite. We look for the
3870	* keyword from a higher level to respect it in particular cases
3871	* (REGEXP, LIKE, etc.) and then remove it from the output here.
3872	*/
3873	return null;
3874	case WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL:
3875	/*
3876	* The "SQL_CALC_FOUND_ROWS" keyword is implemented in the select
3877	* statement translation and then removed from the output here.
3878	*/
3879	return null;
3880	default:
3881	return $token->get_value();
3882	}
3883	}
3884
3885	/**
3886	* Translate a sequence of MySQL AST nodes to SQLite.
3887	*
3888	* @param array<WP_Parser_Node\|WP_MySQL_Token> $nodes The MySQL token to translate.
3889	* @param string $separator The separator to use between fragments.
3890	* @return string\|null The translated value.
3891	* @throws WP_SQLite_Driver_Exception When the translation fails.
3892	*/
3893	private function translate_sequence( array $nodes, string $separator = ' ' ): ?string {
3894	$parts = array();
3895	foreach ( $nodes as $node ) {
3896	if ( null === $node ) {
3897	continue;
3898	}
3899
3900	$translated = $this->translate( $node );
3901	if ( null === $translated ) {
3902	continue;
3903	}
3904	$parts[] = $translated;
3905	}
3906	if ( 0 === count( $parts ) ) {
3907	return null;
3908	}
3909	return implode( $separator, $parts );
3910	}
3911
3912	/**
3913	* Translate a MySQL string literal to SQLite.
3914	*
3915	* @param WP_Parser_Node $node The "textStringLiteral" AST node.
3916	* @return string The translated value.
3917	*/
3918	private function translate_string_literal( WP_Parser_Node $node ): string {
3919	$token = $node->get_first_child_token();
3920	$value = $token->get_value();
3921
3922	/*
3923	* Translate datetime literals.
3924	*
3925	* Process only strings that could possibly represent a datetime
3926	* literal ("YYYY-MM-DDTHH:MM:SS", "YYYY-MM-DDTHH:MM:SSZ", etc.).
3927	*/
3928	if ( strlen( $value ) >= 19 && is_numeric( $value[0] ) ) {
3929	$value = $this->translate_datetime_literal( $value );
3930	}
3931
3932	/*
3933	* Handle null characters.
3934	*
3935	* SQLite doesn't fully support null characters (\u0000) in strings.
3936	* However, it can store them and read them, with some limitations.
3937	*
3938	* In PHP, null bytes are often produced by the serialize() function.
3939	* Removing them would damage the serialized data.
3940	*
3941	* There is no way to store null bytes using a string literal, so we
3942	* need to pass the value as a HEX string and cast it back to TEXT.
3943	* This will convert literals will null bytes to expressions.
3944	*
3945	* Alternatively, we could replace string literals with parameters and
3946	* pass them using prepared statements. However, that's not universally
3947	* applicable for all string literals (e.g., in default column values).
3948	*
3949	* We can't use the "part1 \|\| CHAR(0) \|\| part2 \|\| ..." syntax, because
3950	* with a large number of null bytes, SQLite throws the following error:
3951	*
3952	* SQLSTATE[HY000]:
3953	* General error: 1 Expression tree is too large (maximum depth 1000)
3954	*
3955	* See:
3956	* https://www.sqlite.org/nulinstr.html
3957	*/
3958	if ( strpos( $value, "\0" ) !== false ) {
3959	return sprintf( "CAST(x'%s' AS TEXT)", bin2hex( $value ) );
3960	}
3961	return $this->quote_sqlite_value( $value );
3962	}
3963
3964	/**
3965	* Translate a MySQL pure identifier to SQLite.
3966	*
3967	* @param WP_Parser_Node $node The "pureIdentifier" AST node.
3968	* @return string The translated value.
3969	*/
3970	private function translate_pure_identifier( WP_Parser_Node $node ): string {
3971	$token = $node->get_first_child_token();
3972	$value = $token->get_value();
3973
3974	if ( str_starts_with( $value, self::RESERVED_PREFIX ) ) {
3975	throw $this->new_driver_exception(
3976	sprintf(
3977	"Invalid identifier '%s', prefix '%s' is reserved",
3978	$value,
3979	self::RESERVED_PREFIX
3980	)
3981	);
3982	}
3983
3984	return '`' . str_replace( '`', '``', $value ) . '`';
3985	}
3986
3987	/**
3988	* Translate a qualified MySQL identifier to SQLite.
3989	*
3990	* The identifier can be composed of 1 to 3 parts (schema, object, child).
3991	*
3992	* @param WP_Parser_Node\|null $schema_node An identifier node representing a schema name (database).
3993	* @param WP_Parser_Node\|null $object_node An identifier node representing a database-level object name
3994	* (table, view, procedure, trigger, etc.).
3995	* @param WP_Parser_Node\|null $child_node An identifier node representing an object child name (column, index, etc.).
3996	* @return string The translated value.
3997	* @throws WP_SQLite_Driver_Exception When the translation fails.
3998	*/
3999	private function translate_qualified_identifier(
4000	?WP_Parser_Node $schema_node,
4001	?WP_Parser_Node $object_node = null,
4002	?WP_Parser_Node $child_node = null
4003	): string {
4004	$parts = array();
4005
4006	// Database name.
4007	$is_information_schema = 'information_schema' === $this->db_name;
4008	if ( null !== $schema_node ) {
4009	$schema_name = $this->unquote_sqlite_identifier(
4010	$this->translate_sequence( $schema_node->get_children() )
4011	);
4012	if ( 'information_schema' === strtolower( $schema_name ) ) {
4013	$is_information_schema = true;
4014	} elseif ( $this->main_db_name === $schema_name ) {
4015	$is_information_schema = false;
4016	} else {
4017	throw $this->new_not_supported_exception(
4018	sprintf(
4019	"can't use schema '%s', only '%s' and 'information_schema' are supported",
4020	$schema_name,
4021	$this->db_name
4022	)
4023	);
4024	}
4025	}
4026
4027	// Database-level object name (table, view, procedure, trigger, etc.).
4028	if ( null !== $object_node ) {
4029	$parts[] = $this->translate( $object_node );
4030	}
4031
4032	// Object child name (column, index, etc.).
4033	if ( null !== $child_node ) {
4034	$parts[] = $this->translate( $child_node );
4035	}
4036
4037	return implode( '.', $parts );
4038	}
4039
4040	/**
4041	* Translate a MySQL query expression to SQLite.
4042	*
4043	* @param WP_Parser_Node $node The "queryExpression" AST node.
4044	* @return string The translated value.
4045	* @throws WP_SQLite_Driver_Exception When the translation fails.
4046	*/
4047	private function translate_query_expression( WP_Parser_Node $node ): string {
4048	// Get the query expression subnode under which we need to look for the
4049	// SELECT item list node. This prevents searching under "withClause".
4050	$query_expr_main = (
4051	$node->get_first_child_node( 'queryExpressionBody' )
4052	?? $node->get_first_child_node( 'queryExpressionParens' )
4053	);
4054	$query_term = $query_expr_main->get_first_descendant_node( 'queryTerm' );
4055	$has_union = $query_expr_main->has_child_token( WP_MySQL_Lexer::UNION_SYMBOL );
4056	$has_except = $query_expr_main->has_child_token( WP_MySQL_Lexer::EXCEPT_SYMBOL );
4057	$has_intersect = $query_term->has_child_token( WP_MySQL_Lexer::INTERSECT_SYMBOL );
4058
4059	/*
4060	* When the ORDER BY clause is present, we need to disambiguate the item
4061	* list and make sure they don't cause an "ambiguous column name" error.
4062	*
4063	* @see WP_SQLite_Driver::disambiguate_item()
4064	*/
4065	$disambiguated_order_list = array();
4066	$order_clause = $node->get_first_child_node( 'orderClause' );
4067	if ( $order_clause && ! $has_union && ! $has_except && ! $has_intersect ) {
4068	/*
4069	* [GRAMMAR]
4070	* queryExpression: (withClause)? (
4071	* queryExpressionBody orderClause? limitClause?
4072	* \| queryExpressionParens orderClause? limitClause?
4073	* ) (procedureAnalyseClause)?
4074	*/
4075
4076	// Create the SELECT item disambiguation map.
4077	$select_item_list = $query_expr_main->get_first_descendant_node( 'selectItemList' );
4078	$disambiguation_map = $this->create_select_item_disambiguation_map( $select_item_list );
4079
4080	// For each "orderList" item, search for a matching SELECT item.
4081	$disambiguated_order_list = array();
4082	$order_list = $order_clause->get_first_child_node( 'orderList' );
4083	foreach ( $order_list->get_child_nodes() as $order_item ) {
4084	/*
4085	* [GRAMMAR]
4086	* orderExpression: expr direction?
4087	*/
4088	$order_expr = $order_item->get_first_child_node( 'expr' );
4089	$order_direction = $order_item->get_first_child_node( 'direction' );
4090	$disambiguated_item = $this->disambiguate_item( $disambiguation_map, $order_expr );
4091
4092	$disambiguated_order_list[] = sprintf(
4093	'%s%s',
4094	$disambiguated_item ?? $this->translate( $order_expr ),
4095	null !== $order_direction ? ( ' ' . $this->translate( $order_direction ) ) : ''
4096	);
4097	}
4098
4099	// Translate the query expression, replacing the ORDER BY list with
4100	// the one that was constructed using the disambiguation algorithm.
4101	$parts = array();
4102	foreach ( $node->get_children() as $child ) {
4103	if ( $child instanceof WP_Parser_Node && 'orderClause' === $child->rule_name ) {
4104	$parts[] = 'ORDER BY ' . implode( ', ', $disambiguated_order_list );
4105	} else {
4106	$parts[] = $this->translate( $child );
4107	}
4108	}
4109	return implode( ' ', $parts );
4110	}
4111
4112	return $this->translate_sequence( $node->get_children() );
4113	}
4114
4115	/**
4116	* Translate a MySQL query specification node to SQLite.
4117	*
4118	* @param WP_Parser_Node $node The "querySpecification" AST node.
4119	* @return string The translated value.
4120	* @throws WP_SQLite_Driver_Exception When the translation fails.
4121	* @return string\|null
4122	*/
4123	private function translate_query_specification( WP_Parser_Node $node ): string {
4124	$group_by = $node->get_first_child_node( 'groupByClause' );
4125	$having = $node->get_first_child_node( 'havingClause' );
4126
4127	/*
4128	* When the GROUP BY or HAVING clause is present, we need to disambiguate
4129	* the items to ensure they don't cause an "ambiguous column name" error.
4130	*
4131	* @see WP_SQLite_Driver::disambiguate_item()
4132	*/
4133	$group_by_clause = null;
4134	$having_clause = null;
4135	if ( $group_by \|\| $having ) {
4136	// Build a SELECT list disambiguation map for both GROUP BY and HAVING.
4137	$select_item_list = $node->get_first_child_node( 'selectItemList' );
4138	$disambiguation_map = $this->create_select_item_disambiguation_map( $select_item_list );
4139
4140	// Disambiguate the GROUP BY clause column references.
4141	$disambiguated_group_by_list = array();
4142	if ( $group_by ) {
4143	/*
4144	* [GRAMMAR]
4145	* groupByClause: GROUP_SYMBOL BY_SYMBOL orderList olapOption?
4146	*/
4147	$group_by_list = $group_by->get_first_child_node( 'orderList' );
4148	foreach ( $group_by_list->get_child_nodes() as $group_by_item ) {
4149	$group_by_expr = $group_by_item->get_first_child_node( 'expr' );
4150	$disambiguated_item = $this->disambiguate_item( $disambiguation_map, $group_by_expr );
4151	$disambiguated_group_by_list[] = $disambiguated_item ?? $this->translate( $group_by_expr );
4152	}
4153	$group_by_clause = 'GROUP BY ' . implode( ', ', $disambiguated_group_by_list );
4154	}
4155
4156	// Disambiguate the HAVING clause column references.
4157	$disambiguated_having_list = array();
4158	if ( $having ) {
4159	/*
4160	* [GRAMMAR]
4161	* havingClause: HAVING_SYMBOL expr
4162	*/
4163	$having_expr = $having->get_first_child_node();
4164	$having_expr_children = $having_expr->get_children();
4165	foreach ( $having_expr_children as $having_item ) {
4166	if ( $having_item instanceof WP_Parser_Node ) {
4167	$disambiguated_item = $this->disambiguate_item( $disambiguation_map, $having_item );
4168	$disambiguated_having_list[] = $disambiguated_item ?? $this->translate( $having_item );
4169	} else {
4170	$disambiguated_having_list[] = $this->translate( $having_item );
4171	}
4172	}
4173	$having_clause = 'HAVING ' . implode( ' ', $disambiguated_having_list );
4174	}
4175
4176	// Translate the query specification, replacing the ORDER BY/HAVING
4177	// items with the ones that were disambiguated using the SELECT list.
4178	$parts = array();
4179	foreach ( $node->get_children() as $child ) {
4180	if ( $child instanceof WP_Parser_Node && 'groupByClause' === $child->rule_name ) {
4181	$parts[] = $group_by_clause;
4182	} elseif ( $child instanceof WP_Parser_Node && 'havingClause' === $child->rule_name ) {
4183	// SQLite doesn't allow using the "HAVING" clause without "GROUP BY".
4184	// In such cases, let's prefix the "HAVING" clause with "GROUP BY 1".
4185	if ( ! $group_by ) {
4186	$parts[] = 'GROUP BY 1';
4187	}
4188	$parts[] = $having_clause;
4189	} else {
4190	$part = $this->translate( $child );
4191	if ( null !== $part ) {
4192	$parts[] = $part;
4193	}
4194	}
4195	}
4196	return implode( ' ', $parts );
4197	}
4198	return $this->translate_sequence( $node->get_children() );
4199	}
4200
4201	/**
4202	* Translate a MySQL simple expression to SQLite.
4203	*
4204	* @param WP_Parser_Node $node The "simpleExpr" AST node.
4205	* @return string The translated value.
4206	* @throws WP_SQLite_Driver_Exception When the translation fails.
4207	*/
4208	private function translate_simple_expr( WP_Parser_Node $node ): string {
4209	$token = $node->get_first_child_token();
4210
4211	// Translate "VALUES(col)" to "excluded.col" in ON DUPLICATE KEY UPDATE.
4212	if ( null !== $token && WP_MySQL_Lexer::VALUES_SYMBOL === $token->id ) {
4213	return sprintf(
4214	'`excluded`.%s',
4215	$this->translate( $node->get_first_child_node( 'simpleIdentifier' ) )
4216	);
4217	}
4218
4219	return $this->translate_sequence( $node->get_children() );
4220	}
4221
4222	/**
4223	* Translate a MySQL LIKE expression to SQLite.
4224	*
4225	* @param WP_Parser_Node $node The "predicateOperations" AST node.
4226	* @return string The translated value.
4227	* @throws WP_SQLite_Driver_Exception When the translation fails.
4228	*/
4229	private function translate_like( WP_Parser_Node $node ): string {
4230	$tokens = $node->get_descendant_tokens();
4231	$is_binary = isset( $tokens[1] ) && WP_MySQL_Lexer::BINARY_SYMBOL === $tokens[1]->id;
4232
4233	if ( true === $is_binary ) {
4234	$children = $node->get_children();
4235	return sprintf(
4236	'GLOB _helper_like_to_glob_pattern(%s)',
4237	$this->translate( $children[1] )
4238	);
4239	}
4240
4241	/*
4242	* @TODO: Implement the ESCAPE '...' clause.
4243	*/
4244
4245	/*
4246	* @TODO: Implement more correct LIKE behavior.
4247	*
4248	* While SQLite supports the LIKE operator, it seems to differ from the
4249	* MySQL behavior in some ways:
4250	*
4251	* 1. In SQLite, LIKE is case-insensitive only for ASCII characters
4252	* ('a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE)
4253	* 2. In MySQL, LIKE interprets some escape sequences. See the contents
4254	* of the "_helper_like_to_glob_pattern" function.
4255	*
4256	* We'll probably need to overload the like() function:
4257	* https://www.sqlite.org/lang_corefunc.html#like
4258	*/
4259	$statement = $this->translate_sequence( $node->get_children() );
4260	if ( $this->is_sql_mode_active( 'NO_BACKSLASH_ESCAPES' ) ) {
4261	return $statement;
4262	}
4263	return $statement . " ESCAPE '\\'";
4264	}
4265
4266	/**
4267	* Translate MySQL REGEXP expression to SQLite.
4268	*
4269	* @param WP_Parser_Node $node The "predicateOperations" AST node.
4270	* @return string The translated value.
4271	* @throws WP_SQLite_Driver_Exception When the translation fails.
4272	*/
4273	private function translate_regexp_functions( WP_Parser_Node $node ): string {
4274	$tokens = $node->get_descendant_tokens();
4275	$is_binary = isset( $tokens[1] ) && WP_MySQL_Lexer::BINARY_SYMBOL === $tokens[1]->id;
4276
4277	/*
4278	* If the query says REGEXP BINARY, the comparison is byte-by-byte
4279	* and letter casing matters – lowercase and uppercase letters are
4280	* represented using different byte codes.
4281	*
4282	* The REGEXP function can't be easily made to accept two
4283	* parameters, so we'll have to use a hack to get around this.
4284	*
4285	* If the first character of the pattern is a null byte, we'll
4286	* remove it and make the comparison case-sensitive. This should
4287	* be reasonably safe since PHP does not allow null bytes in
4288	* regular expressions anyway.
4289	*/
4290	if ( true === $is_binary ) {
4291	return 'REGEXP CHAR(0) \|\| ' . $this->translate( $node->get_first_child_node() );
4292	}
4293	return 'REGEXP ' . $this->translate( $node->get_first_child_node() );
4294	}
4295
4296	/**
4297	* Translate a MySQL runtime function call to SQLite.
4298	*
4299	* @param WP_Parser_Node $node The "runtimeFunctionCall" AST node.
4300	* @return string The translated value.
4301	* @throws WP_SQLite_Driver_Exception When the translation fails.
4302	*/
4303	private function translate_runtime_function_call( WP_Parser_Node $node ): string {
4304	$child = $node->get_first_child();
4305	if ( $child instanceof WP_Parser_Node ) {
4306	return $this->translate( $child );
4307	}
4308
4309	switch ( $child->id ) {
4310	case WP_MySQL_Lexer::DATABASE_SYMBOL:
4311	return $this->quote_sqlite_value( $this->db_name );
4312	case WP_MySQL_Lexer::CURRENT_TIMESTAMP_SYMBOL:
4313	case WP_MySQL_Lexer::NOW_SYMBOL:
4314	/*
4315	* 1) SQLite doesn't support CURRENT_TIMESTAMP() with parentheses.
4316	* 2) In MySQL, CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are an
4317	* alias of NOW(). In SQLite, there is no NOW() function.
4318	*/
4319	return 'CURRENT_TIMESTAMP';
4320	case WP_MySQL_Lexer::DATE_ADD_SYMBOL:
4321	case WP_MySQL_Lexer::DATE_SUB_SYMBOL:
4322	$nodes = $node->get_child_nodes();
4323	$value = $this->translate( $nodes[1] );
4324	$unit = $this->translate( $nodes[2] );
4325	if ( 'WEEK' === $unit ) {
4326	$unit = 'DAY';
4327	$value = 7 * $value;
4328	}
4329	return sprintf(
4330	"DATETIME(%s, '%s' \|\| %s \|\| ' %s')",
4331	$this->translate( $nodes[0] ),
4332	WP_MySQL_Lexer::DATE_SUB_SYMBOL === $child->id ? '-' : '+',
4333	$value,
4334	$unit
4335	);
4336	case WP_MySQL_Lexer::LEFT_SYMBOL:
4337	$nodes = $node->get_child_nodes();
4338	return sprintf(
4339	'SUBSTR(%s, 1, %s)',
4340	$this->translate( $nodes[0] ),
4341	$this->translate( $nodes[1] )
4342	);
4343	default:
4344	return $this->translate_sequence( $node->get_children() );
4345	}
4346	}
4347
4348	/**
4349	* Translate a MySQL function call to SQLite.
4350	*
4351	* @param WP_Parser_Node $node The "functionCall" AST node.
4352	* @return string The translated value.
4353	* @throws WP_SQLite_Driver_Exception When the translation fails.
4354	*/
4355	private function translate_function_call( WP_Parser_Node $node ): string {
4356	$nodes = $node->get_child_nodes();
4357	$name = strtoupper(
4358	$this->unquote_sqlite_identifier( $this->translate( $nodes[0] ) )
4359	);
4360
4361	$args = array();
4362	if ( isset( $nodes[1] ) ) {
4363	foreach ( $nodes[1]->get_child_nodes() as $child ) {
4364	$args[] = $this->translate( $child );
4365	}
4366	}
4367
4368	switch ( $name ) {
4369	case 'DATE_FORMAT':
4370	list ( $date, $mysql_format ) = $args;
4371
4372	$format = strtr( $mysql_format, self::MYSQL_DATE_FORMAT_TO_SQLITE_STRFTIME_MAP );
4373	if ( ! $format ) {
4374	throw $this->new_driver_exception(
4375	sprintf(
4376	'Could not translate a DATE_FORMAT() format to STRFTIME format (%s)',
4377	$mysql_format
4378	)
4379	);
4380	}
4381
4382	/*
4383	* MySQL supports comparing strings and floats, e.g.
4384	*
4385	* > SELECT '00.42' = 0.4200
4386	* 1
4387	*
4388	* SQLite does not support that. At the same time,
4389	* WordPress likes to filter dates by comparing numeric
4390	* outputs of DATE_FORMAT() to floats, e.g.:
4391	*
4392	* -- Filter by hour and minutes
4393	* DATE_FORMAT(
4394	* STR_TO_DATE('2014-10-21 00:42:29', '%Y-%m-%d %H:%i:%s'),
4395	* '%H.%i'
4396	* ) = 0.4200;
4397	*
4398	* Let's cast the STRFTIME() output to a float if
4399	* the date format is typically used for string
4400	* to float comparisons.
4401	*
4402	* In the future, let's update WordPress to avoid comparing
4403	* strings and floats.
4404	*/
4405	$cast_to_float = "'%H.%i'" === $mysql_format;
4406	if ( true === $cast_to_float ) {
4407	return sprintf( 'CAST(STRFTIME(%s, %s) AS FLOAT)', $format, $date );
4408	}
4409	return sprintf( 'STRFTIME(%s, %s)', $format, $date );
4410	case 'CHAR_LENGTH':
4411	// @TODO LENGTH and CHAR_LENGTH aren't always the same in MySQL for utf8 characters.
4412	return 'LENGTH(' . $args[0] . ')';
4413	case 'CONCAT':
4414	return '(' . implode( ' \|\| ', $args ) . ')';
4415	case 'FOUND_ROWS':
4416	$found_rows = $this->found_rows;
4417	if ( is_int( $found_rows ) ) {
4418	return $found_rows;
4419	} elseif ( is_string( $found_rows ) ) {
4420	return (int) $this->execute_sqlite_query(
4421	sprintf( 'SELECT COUNT(*) FROM (%s)', $found_rows )
4422	)->fetchColumn()[0];
4423	} elseif ( is_array( $found_rows ) && isset( $found_rows[0] ) ) {
4424	return (int) $this->execute_sqlite_query(
4425	sprintf( 'SELECT COUNT(*) FROM (%s)', $found_rows[0] ),
4426	$found_rows[1] ?? array()
4427	)->fetchColumn()[0];
4428	} else {
4429	return 0;
4430	}
4431	case 'VERSION':
4432	$version = (string) $this->mysql_version;
4433	$value = sprintf(
4434	'%d.%d.%d',
4435	$version[0],
4436	substr( $version, 1, 2 ),
4437	substr( $version, 3, 2 )
4438	);
4439	return $this->quote_sqlite_value( $value );
4440	default:
4441	return $this->translate_sequence( $node->get_children() );
4442	}
4443	}
4444
4445	/**
4446	* Translate a MySQL datetime literal to SQLite.
4447	*
4448	* @param string $value The MySQL datetime literal.
4449	* @return string The translated value.
4450	*/
4451	private function translate_datetime_literal( string $value ): string {
4452	/*
4453	* The code below converts the date format to one preferred by SQLite.
4454	*
4455	* MySQL accepts ISO 8601 date strings: 'YYYY-MM-DDTHH:MM:SSZ'
4456	* SQLite prefers a slightly different format: 'YYYY-MM-DD HH:MM:SS'
4457	*
4458	* SQLite date and time functions can understand the ISO 8601 notation, but
4459	* lookups don't. To keep the lookups working, we need to store all dates
4460	* in UTC without the "T" and "Z" characters.
4461	*
4462	* Caveat: It will adjust every string that matches the pattern, not just dates.
4463	*
4464	* In theory, we could only adjust semantic dates, e.g. the data inserted
4465	* to a date column or compared against a date column.
4466	*
4467	* In practice, this is hard because dates are just text – SQLite has no separate
4468	* datetime field. We'd need to cache the MySQL data type from the original
4469	* CREATE TABLE query and then keep refreshing the cache after each ALTER TABLE query.
4470	*
4471	* That's a lot of complexity that's perhaps not worth it. Let's just convert
4472	* everything for now. The regexp assumes "Z" is always at the end of the string,
4473	* which is true in the unit test suite, but there could also be a timezone offset
4474	* like "+00:00" or "+01:00". We could add support for that later if needed.
4475	*/
4476	if ( 1 === preg_match( '/^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})Z$/', $value, $matches ) ) {
4477	$value = $matches[1] . ' ' . $matches[2];
4478	}
4479
4480	/*
4481	* Mimic MySQL's behavior and truncate invalid dates.
4482	*
4483	* "2020-12-41 14:15:27" becomes "0000-00-00 00:00:00"
4484	*
4485	* WARNING: We have no idea whether the truncated value should
4486	* be treated as a date in the first place.
4487	* In SQLite dates are just strings. This could be a perfectly
4488	* valid string that just happens to contain a date-like value.
4489	*
4490	* At the same time, WordPress seems to rely on MySQL's behavior
4491	* and even tests for it in Tests_Post_wpInsertPost::test_insert_empty_post_date.
4492	* Let's truncate the dates for now.
4493	*
4494	* In the future, let's update WordPress to do its own date validation
4495	* and stop relying on this MySQL feature,
4496	*/
4497	if ( 1 === preg_match( '/^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})$/', $value, $matches ) ) {
4498	/*
4499	* Calling strtotime("0000-00-00 00:00:00") in 32-bit environments triggers
4500	* an "out of integer range" warning – let's avoid that call for the popular
4501	* case of "zero" dates.
4502	*/
4503	if ( '0000-00-00 00:00:00' !== $value && false === strtotime( $value ) ) {
4504	/*
4505	* Check for dates with zero month/day parts (e.g. '2020-00-15 00:00:00').
4506	*
4507	* When the NO_ZERO_IN_DATE SQL mode is not active, MySQL accepts dates
4508	* where the year is nonzero but the month or day is zero. We must
4509	* preserve these values so that cast_value_for_saving() can handle
4510	* them correctly at the column level.
4511	*
4512	* See: https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_no_zero_in_date
4513	*/
4514	$has_zero_in_date = (
4515	( '00' === $matches[2] \|\| '00' === $matches[3] ) &&
4516	'0000' !== $matches[1]
4517	);
4518	if ( ! $has_zero_in_date \|\| $this->is_sql_mode_active( 'NO_ZERO_IN_DATE' ) ) {
4519	$value = '0000-00-00 00:00:00';
4520	}
4521	}
4522	}
4523	return $value;
4524	}
4525
4526	/**
4527	* Translate a select item to SQLite.
4528	*
4529	* In some cases, an explicit alias will be added to the select item, so that
4530	* the returned column name is always the same as it would be in MySQL.
4531	*
4532	* @param WP_Parser_Node $node The "selectItem" AST node.
4533	* @return string The translated expression.
4534	*/
4535	public function translate_select_item( WP_Parser_Node $node ): string {
4536	/*
4537	* First, let's translate the select item subtree.
4538	*
4539	* [GRAMMAR]
4540	* selectItem: tableWild \| (expr selectAlias?)
4541	*/
4542	$item = $this->translate_sequence( $node->get_children() );
4543
4544	// A table wildcard (e.g., "SELECT , t., ...") never has an alias.
4545	if ( $node->has_child_node( 'tableWild' ) ) {
4546	return $item;
4547	}
4548
4549	// When an explicit alias is provided, we can use it as is.
4550	$alias = $node->get_first_child_node( 'selectAlias' );
4551	if ( $alias ) {
4552	return $item;
4553	}
4554
4555	/*
4556	* When the select item contains only a column definition, we need to use
4557	* it without change, so that the returned column name reflects the real
4558	* column name in all cases, including when using a fully qualified name.
4559	*
4560	* For example, for "SELECT t.id", the column name in the result set will
4561	* only be "id", not "t.id", as it may appear based on the original query.
4562	*
4563	* In this case, SQLite uses the same logic as MySQL, so using the value
4564	* as is without adding an explicit alias will produce the correct result.
4565	*/
4566	$column_ref = $node->get_first_descendant_node( 'columnRef' );
4567	$is_column_ref = $column_ref && $item === $this->translate( $column_ref );
4568	if ( $is_column_ref ) {
4569	return $item;
4570	}
4571
4572	/*
4573	* When the select item is a text string literal, we need to use an alias
4574	* to ensure that the column name is the same as it would be in MySQL.
4575	* In MySQL, the column name is the original text string literal value
4576	* without quotes and escaping, but in SQLite, it is the quoted value.
4577	*
4578	* For example, for "SELECT 'abc'", the resulting column name is "abc"
4579	* in MySQL, but would be "'abc'" in SQLite if an alias was not used.
4580	*/
4581	$text_string_literal = $node->get_first_descendant_node( 'textStringLiteral' );
4582	$is_text_string_literal = $text_string_literal && $item === $this->translate( $text_string_literal );
4583	if ( $is_text_string_literal ) {
4584	$alias = $text_string_literal->get_first_child_token()->get_value();
4585
4586	// When the literal value contains a NULL byte, MySQL truncates the
4587	// resulting identifier at the position of the first one of them.
4588	$fist_null_byte_pos = strpos( $alias, "\0" );
4589	if ( false !== $fist_null_byte_pos ) {
4590	$alias = substr( $alias, 0, $fist_null_byte_pos );
4591	}
4592	return sprintf( '%s AS %s', $item, $this->quote_sqlite_identifier( $alias ) );
4593	}
4594
4595	/*
4596	* When the select item has no explicit alias, we need to ensure that the
4597	* returned column name is equivalent to what MySQL infers from the input.
4598	*
4599	* For example, if we translate "CONCAT('a', 'b')" to "('a' \|\| 'b')", we
4600	* need to use the original "CONCAT('a', 'b')" string as the column name.
4601	* To achieve this, the select item will be translated as follows:
4602	*
4603	* SELECT CONCAT('a', 'b') -> SELECT ('a' \|\| 'b') AS `CONCAT('a', 'b')`
4604	*/
4605	$raw_alias = substr( $this->last_mysql_query, $node->get_start(), $node->get_length() );
4606	$alias = $this->quote_sqlite_identifier( $raw_alias );
4607	if ( $alias === $item \|\| $raw_alias === $item ) {
4608	// For the simple case of selecting only columns ("SELECT id FROM t"),
4609	// let's avoid unnecessary aliases ("SELECT `id` AS `id` FROM t").
4610	return $item;
4611	}
4612	return sprintf( '%s AS %s', $item, $alias );
4613	}
4614
4615	/**
4616	* Translate a MySQL table reference to SQLite.
4617	*
4618	* When the table reference targets an information schema table, we replace
4619	* it with a subquery, injecting the configured database name dynamically.
4620	*
4621	* For example, the following query:
4622	*
4623	* SELECT , t., t.table_schema FROM information_schema.tables t
4624	*
4625	* Will be translated to:
4626	*
4627	* SELECT , `t`., `t`.`table_schema` FROM (
4628	* SELECT
4629	* `TABLE_CATALOG`,
4630	* CASE WHEN `TABLE_SCHEMA` = 'information_schema' THEN `TABLE_SCHEMA` ELSE 'database_name' END AS `TABLE_SCHEMA`,
4631	* `TABLE_NAME`,
4632	* ...
4633	* FROM `_wp_sqlite_mysql_information_schema_tables` AS `tables`
4634	* ) `t`
4635	*
4636	* The same logic will be applied to table references in JOIN clauses as well.
4637	*
4638	* @param WP_Parser_Node $node The "tableRef" AST node.
4639	* @return string The translated value.
4640	* @throws WP_SQLite_Driver_Exception When the translation fails.
4641	*/
4642	public function translate_table_ref( WP_Parser_Node $node ): string {
4643	// The table reference is in "<schema>.<table>" or "<table>" format.
4644	$parts = $node->get_descendant_nodes( 'identifier' );
4645	$table = array_pop( $parts );
4646	$schema = array_pop( $parts );
4647
4648	$schema_name = $schema ? $this->unquote_sqlite_identifier( $this->translate( $schema ) ) : null;
4649	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table ) );
4650
4651	// When the table reference targets an information schema table,
4652	// we need to inject the configured database name dynamically.
4653	if (
4654	( null === $schema_name && 'information_schema' === $this->db_name )
4655	\|\| ( null !== $schema_name && 'information_schema' === strtolower( $schema_name ) )
4656	) {
4657	$table_name = strtolower( $table_name );
4658
4659	// Some information schema tables can be computed on the fly.
4660	if ( 'character_sets' === $table_name \|\| 'collations' === $table_name ) {
4661	$table_definition = $this->information_schema_builder
4662	->get_computed_information_schema_table_definition( $table_name );
4663	if ( null !== $table_definition ) {
4664	return sprintf( '(%s)', $table_definition );
4665	}
4666	}
4667
4668	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
4669	$sqlite_table_name = $this->information_schema_builder->get_table_name( $table_is_temporary, $table_name );
4670
4671	// We need to fetch the SQLite column information, because the information
4672	// schema tables don't contain records for the information schema itself.
4673	$columns = $this->execute_sqlite_query(
4674	'SELECT name FROM pragma_table_info(?)',
4675	array( $sqlite_table_name )
4676	)->fetchAll( PDO::FETCH_COLUMN );
4677
4678	if ( count( $columns ) === 0 ) {
4679	return $this->translate_sequence( $node->get_children() );
4680	}
4681
4682	// List all columns in the table, replacing columns targeting database
4683	// name columns with the configured database name.
4684	static $information_schema_db_column_map = array(
4685	'SCHEMA_NAME' => true,
4686	'TABLE_SCHEMA' => true,
4687	'VIEW_SCHEMA' => true,
4688	'INDEX_SCHEMA' => true,
4689	'CONSTRAINT_SCHEMA' => true,
4690	'UNIQUE_CONSTRAINT_SCHEMA' => true,
4691	'REFERENCED_TABLE_SCHEMA' => true,
4692	'TRIGGER_SCHEMA' => true,
4693	);
4694
4695	$expanded_list = array();
4696	foreach ( $columns as $column ) {
4697	$quoted_column = $this->quote_sqlite_identifier( $column );
4698	if ( isset( $information_schema_db_column_map[ strtoupper( $column ) ] ) ) {
4699	$expanded_list[] = sprintf(
4700	"CASE WHEN %s = 'information_schema' THEN %s ELSE %s END AS %s",
4701	$quoted_column,
4702	$quoted_column,
4703	$this->quote_sqlite_value( $this->main_db_name ),
4704	strtoupper( $quoted_column )
4705	);
4706	} else {
4707	$expanded_list[] = $quoted_column;
4708	}
4709	}
4710	$column_list = implode( ', ', $expanded_list );
4711
4712	// Compose information schema subquery.
4713	return sprintf(
4714	'(SELECT %s FROM %s AS %s)',
4715	$column_list,
4716	$this->quote_sqlite_identifier( $sqlite_table_name ),
4717	$this->quote_sqlite_identifier( $table_name )
4718	);
4719	}
4720	return $this->translate_sequence( $node->get_children() );
4721	}
4722
4723	/**
4724	* Recreate an existing table using data in the information schema.
4725	*
4726	* This is used for a generic support of ALTER TABLE queries, as well as
4727	* for some other statements like OPTIMIZE TABLE and REPAIR TABLE.
4728	*
4729	* See:
4730	* https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes
4731	*
4732	* @param bool $table_is_temporary Whether the table is temporary.
4733	* @param string $table_name The name of the table to recreate.
4734	* @param array $column_map Optional. A map of column names (old name -> new name)
4735	* to use when copying data from the original table.
4736	* When not provided, all columns are copied without renaming.
4737	* @throws WP_SQLite_Driver_Exception
4738	*/
4739	private function recreate_table_from_information_schema(
4740	bool $table_is_temporary,
4741	string $table_name,
4742	?array $column_map = null
4743	): void {
4744	if ( null === $column_map ) {
4745	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
4746	$column_names = $this->execute_sqlite_query(
4747	sprintf(
4748	'SELECT COLUMN_NAME FROM %s WHERE table_schema = ? AND table_name = ?',
4749	$this->quote_sqlite_identifier( $columns_table )
4750	),
4751	array( $this->get_saved_db_name(), $table_name )
4752	)->fetchAll( PDO::FETCH_COLUMN );
4753	$column_map = array_combine( $column_names, $column_names );
4754	}
4755
4756	// Preserve ROWIDs.
4757	// This also addresses a special case when all original columns are dropped
4758	// and there is nothing to copy. We'll always have at least the ROWID column.
4759	$column_map = array( 'rowid' => 'rowid' ) + $column_map;
4760
4761	/*
4762	* See:
4763	* https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes
4764	*/
4765
4766	// 1. If foreign key constraints are enabled, disable them.
4767	$pragma_foreign_keys = $this->execute_sqlite_query( 'PRAGMA foreign_keys' )->fetchColumn();
4768	$this->execute_sqlite_query( 'PRAGMA foreign_keys = OFF' );
4769
4770	// 2. Create a new table with the new schema.
4771	$tmp_table_name = self::RESERVED_PREFIX . "tmp_{$table_name}_" . uniqid();
4772	$quoted_table_name = $this->quote_sqlite_identifier( $table_name );
4773	$quoted_tmp_table_name = $this->quote_sqlite_identifier( $tmp_table_name );
4774	$queries = $this->get_sqlite_create_table_statement( $table_is_temporary, $table_name, $tmp_table_name );
4775	$create_table_query = $queries[0];
4776	$constraint_queries = array_slice( $queries, 1 );
4777	$this->execute_sqlite_query( $create_table_query );
4778
4779	// 3. Copy data from the original table to the new table.
4780	$this->execute_sqlite_query(
4781	sprintf(
4782	'INSERT INTO %s (%s) SELECT %s FROM %s',
4783	$quoted_tmp_table_name,
4784	implode(
4785	', ',
4786	array_map( array( $this, 'quote_sqlite_identifier' ), $column_map )
4787	),
4788	implode(
4789	', ',
4790	array_map( array( $this, 'quote_sqlite_identifier' ), array_keys( $column_map ) )
4791	),
4792	$quoted_table_name
4793	)
4794	);
4795
4796	// 4. Drop the original table.
4797	$this->execute_sqlite_query( sprintf( 'DROP TABLE %s', $quoted_table_name ) );
4798
4799	// 5. Rename the new table to the original table name.
4800	$this->execute_sqlite_query(
4801	sprintf(
4802	'ALTER TABLE %s RENAME TO %s',
4803	$quoted_tmp_table_name,
4804	$quoted_table_name
4805	)
4806	);
4807
4808	// 6. Reconstruct indexes, triggers, and views.
4809	foreach ( $constraint_queries as $query ) {
4810	$this->execute_sqlite_query( $query );
4811	}
4812
4813	// 7. If foreign key constraints were enabled, verify and enable them.
4814	if ( '1' === $pragma_foreign_keys ) {
4815	$this->execute_sqlite_query( 'PRAGMA foreign_key_check' );
4816	$this->execute_sqlite_query( 'PRAGMA foreign_keys = ON' );
4817	}
4818
4819	// @TODO: Triggers and views.
4820	}
4821
4822	/**
4823	* Translate a MySQL SHOW LIKE ... or SHOW WHERE ... condition to SQLite.
4824	*
4825	* @param WP_Parser_Node $like_or_where The "likeOrWhere" AST node.
4826	* @param string $like_column The column name to use in the LIKE clause ("table_name", "column_name", etc.).
4827	* @return string The translated value.
4828	* @throws WP_SQLite_Driver_Exception When the translation fails.
4829	*/
4830	private function translate_show_like_or_where_condition( WP_Parser_Node $like_or_where, string $like_column ): string {
4831	$like_clause = $like_or_where->get_first_child_node( 'likeClause' );
4832	if ( null !== $like_clause ) {
4833	$value = $this->translate(
4834	$like_clause->get_first_child_node( 'textStringLiteral' )
4835	);
4836	return sprintf(
4837	"AND %s LIKE %s ESCAPE '\\'",
4838	$this->quote_sqlite_identifier( $like_column ),
4839	$value
4840	);
4841	}
4842
4843	$where_clause = $like_or_where->get_first_child_node( 'whereClause' );
4844	if ( null !== $where_clause ) {
4845	$value = $this->translate(
4846	$where_clause->get_first_child_node( 'expr' )
4847	);
4848	return sprintf( 'AND %s', $value );
4849	}
4850
4851	return '';
4852	}
4853
4854	/**
4855	* Translate INSERT or REPLACE statement body to SQLite, while emulating
4856	* MySQL column type casting and implicit default values when saving data.
4857	*
4858	* This method rewrites an INSERT or REPLACE statement body from:
4859	* INSERT INTO table (optionally some columns) <select-or-values>
4860	* To a statement body with the following structure:
4861	* INSERT INTO table (table columns)
4862	* SELECT <adjusted-values> FROM (<select-or-values>) WHERE true
4863	*
4864	* In MySQL, the behavior of INSERT and UPDATE statements depends on whether
4865	* the STRICT_TRANS_TABLES (InnoDB) or STRICT_ALL_TABLES SQL mode is enabled.
4866	*
4867	* This method applies relevant type casting and emulates IMPLICIT DEFAULT
4868	* value behavior as follows:
4869	* 1. In STRICT mode:
4870	* - Apply relevant type casting based on the column data type.
4871	* 2. In non-STRICT mode:
4872	* - Apply relevant type casting based on the column data type.
4873	* - Replace invalid values with IMPLICIT DEFAULTs.
4874	* - Replace missing values without defaults with IMPLICIT DEFAULTs.
4875	*
4876	* The strict SQL modes can be set per session, and can be changed at runtime.
4877	* In SQLite, we can emulate this using the knowledge of the table structure.
4878	*
4879	* -----
4880	*
4881	* Here's a summary of the strict vs. non-strict IMPLICIT DEFAULT behavior:
4882	*
4883	* When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled:
4884	* 1. NULL + NO DEFAULT: No value saves NULL, NULL saves NULL, DEFAULT saves NULL.
4885	* 2. NULL + DEFAULT: No value saves DEFAULT, NULL saves NULL, DEFAULT saves DEFAULT.
4886	* 3. NOT NULL + NO DEFAULT: No value is rejected, NULL is rejected, DEFAULT is rejected.
4887	* 4. NOT NULL + DEFAULT: No value saves DEFAULT, NULL is rejected, DEFAULT saves DEFAULT.
4888	*
4889	* When STRICT_TRANS_TABLES and STRICT_ALL_TABLES are disabled:
4890	* 1. NULL + NO DEFAULT: No value saves NULL, NULL saves NULL, DEFAULT saves NULL.
4891	* 2. NULL + DEFAULT: No value saves DEFAULT, NULL saves NULL, DEFAULT saves DEFAULT.
4892	* 3. NOT NULL + NO DEFAULT: No value saves IMPLICIT DEFAULT.
4893	* NULL is rejected on INSERT, but saves IMPLICIT DEFAULT on UPDATE.
4894	* DEFAULT saves IMPLICIT DEFAULT.
4895	* 4. NOT NULL + DEFAULT: No value saves DEFAULT.
4896	* NULL is rejected on INSERT, but saves IMPLICIT DEFAULT on UPDATE.
4897	* DEFAULT saves DEFAULT.
4898	*
4899	* For more information about STRICT mode in MySQL, see:
4900	* https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-strict
4901	*
4902	* For more information about IMPLICIT DEFAULT values in MySQL, see:
4903	* https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html#data-type-defaults-implicit
4904	*
4905	* @param string $table_name The name of the target table.
4906	* @param WP_Parser_Node $node The "insertQueryExpression" or "insertValues" AST node.
4907	* @return string The translated INSERT query body.
4908	*/
4909	private function translate_insert_or_replace_body(
4910	string $table_name,
4911	WP_Parser_Node $node
4912	): string {
4913	// This method is always used with the main database.
4914	$database = $this->get_saved_db_name( $this->main_db_name );
4915
4916	// Check if strict mode is enabled.
4917	$is_strict_mode = (
4918	$this->is_sql_mode_active( 'STRICT_TRANS_TABLES' )
4919	\|\| $this->is_sql_mode_active( 'STRICT_ALL_TABLES' )
4920	);
4921
4922	// Get column metadata for the target table from the information schema.
4923	$is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
4924	$columns_table = $this->information_schema_builder->get_table_name( $is_temporary, 'columns' );
4925	$columns = $this->execute_sqlite_query(
4926	'
4927	SELECT LOWER(column_name) AS COLUMN_NAME, is_nullable, column_default, data_type, extra
4928	FROM ' . $this->quote_sqlite_identifier( $columns_table ) . '
4929	WHERE table_schema = ?
4930	AND table_name = ?
4931	ORDER BY ordinal_position
4932	',
4933	array( $database, $table_name )
4934	)->fetchAll( PDO::FETCH_ASSOC );
4935
4936	// Check if the table exists.
4937	if ( 0 === count( $columns ) ) {
4938	throw $this->new_driver_exception(
4939	sprintf(
4940	"SQLSTATE[42S02]: Base table or view not found: 1146 Table '%s' doesn't exist",
4941	$table_name
4942	),
4943	'42S02'
4944	);
4945	}
4946
4947	// Get a list of columns that are targeted by the INSERT or REPLACE query.
4948	// This is either an explicit column list, or all columns of the table.
4949	$insert_list = array();
4950	$fields_node = $node->get_first_child_node( 'fields' );
4951	if ( $fields_node ) {
4952	// "INSERT INTO ... (column1, column2, ...)"
4953	foreach ( $fields_node->get_child_nodes() as $field ) {
4954	$column_name = $this->unquote_sqlite_identifier( $this->translate( $field ) );
4955	$insert_list[] = strtolower( $column_name );
4956	}
4957	} elseif ( 'updateList' === $node->rule_name ) {
4958	// "INSERT INTO ... SET column1 = value1, column2 = value2, ..."
4959	foreach ( $node->get_child_nodes( 'updateElement' ) as $update_element ) {
4960	$column_ref = $update_element->get_first_child_node( 'columnRef' );
4961	$column_name = $this->unquote_sqlite_identifier( $this->translate( $column_ref ) );
4962	$insert_list[] = strtolower( $column_name );
4963	}
4964	} else {
4965	// "INSERT INTO ... VALUES(...)" or "INSERT INTO ... SELECT ..."
4966	// No explicit column list is provided; we need to list all columns.
4967	foreach ( array_column( $columns, 'COLUMN_NAME' ) as $column_name ) {
4968	$insert_list[] = strtolower( $column_name );
4969	}
4970	}
4971
4972	// Check if all listed columns exist.
4973	$unknown_columns = array_diff( $insert_list, array_column( $columns, 'COLUMN_NAME' ) );
4974	if ( count( $unknown_columns ) > 0 ) {
4975	throw $this->new_driver_exception(
4976	sprintf(
4977	"SQLSTATE[42S22]: Column not found: 1054 Unknown column '%s' in 'field list'",
4978	$unknown_columns[0]
4979	),
4980	'42S22'
4981	);
4982	}
4983
4984	// Prepare a helper map of columns that are included in the INSERT list.
4985	$insert_map = array_combine( $insert_list, $insert_list );
4986
4987	/*
4988	* Filter out columns that were omitted in the INSERT list:
4989	* 1. In strict mode, filter out all omitted columns.
4990	* 2. In non-strict mode, filter out omitted columns that will get a
4991	* value from the SQLite engine. That is, nullable columns, columns
4992	* with defaults, and generated columns.
4993	*/
4994	$columns = array_values(
4995	array_filter(
4996	$columns,
4997	function ( $column ) use ( $is_strict_mode, $insert_map ) {
4998	$is_omitted = ! isset( $insert_map[ $column['COLUMN_NAME'] ] );
4999	if ( ! $is_omitted ) {
5000	return true;
5001	}
5002	if ( $is_strict_mode ) {
5003	return false;
5004	}
5005	$is_nullable = 'YES' === $column['IS_NULLABLE'];
5006	$has_default = $column['COLUMN_DEFAULT'];
5007	$is_generated = str_contains( $column['EXTRA'], 'auto_increment' );
5008	return ! ( $is_nullable \|\| $has_default \|\| $is_generated );
5009	}
5010	)
5011	);
5012
5013	/*
5014	* Get a list of column names for the INSERT or REPLACE values clause.
5015	* These are the columns that will be used in a SELECT statement when
5016	* the values clause is wrapped in a subquery:
5017	*
5018	* INSERT INTO ... SELECT <select-list> FROM (<values-from-original-query>)
5019	*/
5020	$select_list = array();
5021	if ( 'insertQueryExpression' === $node->rule_name ) {
5022	// When inserting from a SELECT query, we don't know the column names.
5023	// Let's wrap the query with a "SELECT (...) LIMIT 0" to obtain them.
5024	$expr = $node->get_first_child_node( 'queryExpressionOrParens' );
5025	$stmt = $this->execute_sqlite_query(
5026	'SELECT * FROM (' . $this->translate( $expr ) . ') LIMIT 1'
5027	);
5028	$stmt->execute();
5029
5030	for ( $i = 0; $i < $stmt->columnCount(); $i++ ) {
5031	/*
5032	* Workaround for PHP PDO SQLite bug (#79664) in PHP < 7.3.
5033	* See also: https://github.com/php/php-src/pull/5654
5034	*/
5035	if ( PHP_VERSION_ID < 70300 ) {
5036	try {
5037	$column_meta = $stmt->getColumnMeta( $i );
5038	} catch ( Throwable $e ) {
5039	$column_meta = false;
5040	}
5041	if ( false === $column_meta ) {
5042	// Due to a PDO bug in PHP < 7.3, we get no column metadata
5043	// when no rows are returned. In that case, no data will be
5044	// inserted, so we can bail out using a simple translation.
5045	return $this->translate( $node );
5046	}
5047	}
5048	$select_list[] = $stmt->getColumnMeta( $i )['name'];
5049	}
5050	} else {
5051	// When inserting from a VALUES list, SQLite uses a "columnN" naming.
5052	// This also applies to the SET syntax, which is converted to VALUES.
5053	foreach ( array_keys( $insert_list ) as $position ) {
5054	$select_list[] = 'column' . ( $position + 1 );
5055	}
5056	}
5057
5058	// Compose a new INSERT column list with all columns from the table.
5059	$fragment = '(';
5060	foreach ( $columns as $i => $column ) {
5061	$fragment .= $i > 0 ? ', ' : '';
5062	$fragment .= $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
5063	}
5064	$fragment .= ')';
5065
5066	// Compose a wrapper SELECT statement emulating MySQL-like type casting,
5067	// and, in non-strict mode, IMPLICIT DEFAULT values for omitted columns.
5068	$fragment .= ' SELECT ';
5069	foreach ( $columns as $i => $column ) {
5070	$is_omitted = ! isset( $insert_map[ $column['COLUMN_NAME'] ] );
5071	$fragment .= $i > 0 ? ', ' : '';
5072	if ( $is_omitted ) {
5073	/*
5074	* This path only applies to non-strict mode. In strict mode,
5075	* omitted columns get no IMPLICIT DEFAULT values, and they were
5076	* previously filtered out from the columns list.
5077	*
5078	* When a column is omitted from the INSERT list, we need to use
5079	* an IMPLICIT DEFAULT value. Note that at this point, all omitted
5080	* columns that will not get an implicit default are filtered out.
5081	* (That is, nullable, generated, and columns with true defaults.)
5082	*/
5083	$default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $column['DATA_TYPE'] ] ?? null;
5084	$fragment .= null === $default ? 'NULL' : $this->quote_sqlite_value( $default );
5085	} else {
5086	// When a column value is included, we need to apply type casting.
5087	$position = array_search( $column['COLUMN_NAME'], $insert_list, true );
5088	$identifier = $this->quote_sqlite_identifier( $select_list[ $position ] );
5089	$value = $this->cast_value_for_saving( $column['DATA_TYPE'], $identifier );
5090
5091	/*
5092	* In MySQL non-STRICT mode, when inserting from a SELECT query:
5093	*
5094	* When a column is declared as NOT NULL, inserting a NULL value
5095	* saves an IMPLICIT DEFAULT value instead. This behavior only
5096	* applies to the INSERT ... SELECT syntax (not VALUES or SET).
5097	*/
5098	$is_insert_from_select = 'insertQueryExpression' === $node->rule_name;
5099	if ( ! $is_strict_mode && $is_insert_from_select && 'NO' === $column['IS_NULLABLE'] ) {
5100	$implicit_default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $column['DATA_TYPE'] ] ?? null;
5101	if ( null !== $implicit_default ) {
5102	$value = sprintf( 'COALESCE(%s, %s)', $value, $this->quote_sqlite_value( $implicit_default ) );
5103	}
5104	}
5105	$fragment .= $value;
5106	}
5107	}
5108
5109	// Wrap the original insert VALUES, SELECT, or SET list in a FROM clause.
5110	if ( 'insertFromConstructor' === $node->rule_name ) {
5111	// VALUES (...)
5112	$insert_values = $node->get_first_child_node( 'insertValues' );
5113	$from = $this->translate( $insert_values );
5114
5115	/**
5116	* The automatic "columnN" naming for VALUES lists is supported only
5117	* from SQLite 3.33.0. For older versions, we need to emulate it by
5118	* prepending a dummy VALUES list header via the UNION ALL operator:
5119	*
5120	* SELECT
5121	* NULL AS `column1`, NULL AS `column2`, ... WHERE FALSE
5122	* UNION ALL
5123	* VALUES (value1, value2, ...)
5124	*/
5125	$is_values_naming_supported = version_compare( $this->get_sqlite_version(), '3.33.0', '>=' );
5126	if ( ! $is_values_naming_supported ) {
5127	$values_list = $insert_values->get_first_child_node( 'valueList' );
5128	$values = $values_list->get_first_child_node( 'values' );
5129	$value_count = (
5130	count( $values->get_child_nodes( 'expr' ) )
5131	+ count( $values->get_child_nodes( WP_MySQL_Lexer::DEFAULT_SYMBOL ) )
5132	);
5133
5134	$columns_list = '';
5135	for ( $i = 1; $i <= $value_count; $i++ ) {
5136	$columns_list .= $i > 1 ? ', ' : '';
5137	$columns_list .= 'NULL AS ' . $this->quote_sqlite_identifier( 'column' . $i );
5138	}
5139	$from = 'SELECT ' . $columns_list . ' WHERE FALSE UNION ALL ' . $from;
5140	}
5141	} elseif ( 'insertQueryExpression' === $node->rule_name ) {
5142	// SELECT ...
5143	$from = $this->translate(
5144	$node->get_first_child_node( 'queryExpressionOrParens' )
5145	);
5146	} else {
5147	// SET c1 = v1, c2 = v2, ...
5148	$values = array();
5149	foreach ( $node->get_child_nodes( 'updateElement' ) as $update_element ) {
5150	$values[] = $this->translate( $update_element->get_first_child_node( 'expr' ) );
5151	}
5152	$from = 'VALUES (' . implode( ', ', $values ) . ')';
5153	}
5154
5155	/*
5156	* The "WHERE true" suffix is used to avoid parsing ambiguity in SQLite.
5157	* When an "ON CONFLICT" clause is used and there is no "WHERE", SQLite
5158	* doesn't know if "ON" belongs to a "JOIN" or an "ON CONFLICT" clause.
5159	*
5160	* See: https://www.sqlite.org/lang_insert.html
5161	*/
5162	$fragment .= ' FROM (' . $from . ') WHERE true';
5163
5164	return $fragment;
5165	}
5166
5167	/**
5168	* Translate UPDATE statement SET value list to SQLite, while emulating
5169	* MySQL column type casting and implicit default values when saving data.
5170	*
5171	* Rewrites an UPDATE statement list in the following form:
5172	* UPDATE table SET <column> = <value>
5173	* To a list with the following structure:
5174	* UPDATE table SET <column> = <adjusted-value>
5175	*
5176	* In MySQL, the behavior of INSERT and UPDATE statements depends on whether
5177	* the STRICT_TRANS_TABLES (InnoDB) or STRICT_ALL_TABLES SQL mode is enabled.
5178	*
5179	* This method applies relevant type casting and emulates IMPLICIT DEFAULT
5180	* value behavior as follows:
5181	* 1. In STRICT mode:
5182	* - Apply relevant type casting based on the column data type.
5183	* 2. In NON-STRICT mode:
5184	* - Apply relevant type casting based on the column data type.
5185	* - Replace invalid values with IMPLICIT DEFAULTs.
5186	* - Replace NULL values without defaults with IMPLICIT DEFAULTs.
5187	* (Updating a NOT NULL column to NULL saves as an IMPLICIT DEFAULT.)
5188	*
5189	* The strict SQL modes can be set per session, and can be changed at runtime.
5190	* In SQLite, we can emulate this using the knowledge of the table structure.
5191	*
5192	* For more information about STRICT mode in MySQL, see:
5193	* https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-strict
5194	*
5195	* For more information about IMPLICIT DEFAULT values in MySQL, see:
5196	* https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html#data-type-defaults-implicit
5197	*
5198	* @param string $table_name The name of the target table.
5199	* @param WP_Parser_Node $parent_node The "updateList" AST node parent node.
5200	* @return string The translated UPDATE list.
5201	*/
5202	private function translate_update_list( string $table_name, WP_Parser_Node $parent_node ): string {
5203	$node = $parent_node->get_first_child_node( 'updateList' );
5204
5205	// This method is always used with the main database.
5206	$database = $this->get_saved_db_name( $this->main_db_name );
5207
5208	// Check if strict mode is enabled.
5209	$is_strict_mode = (
5210	$this->is_sql_mode_active( 'STRICT_TRANS_TABLES' )
5211	\|\| $this->is_sql_mode_active( 'STRICT_ALL_TABLES' )
5212	);
5213
5214	// Get column metadata from the information schema.
5215	$is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
5216	$columns_table = $this->information_schema_builder->get_table_name( $is_temporary, 'columns' );
5217	$columns = $this->execute_sqlite_query(
5218	'
5219	SELECT LOWER(column_name) AS COLUMN_NAME, is_nullable, data_type, column_default
5220	FROM ' . $this->quote_sqlite_identifier( $columns_table ) . '
5221	WHERE table_schema = ?
5222	AND table_name = ?
5223	',
5224	array( $database, $table_name )
5225	)->fetchAll( PDO::FETCH_ASSOC );
5226
5227	// Check if the table exists.
5228	if ( 0 === count( $columns ) ) {
5229	throw $this->new_driver_exception(
5230	sprintf(
5231	"SQLSTATE[42S02]: Base table or view not found: 1146 Table '%s' doesn't exist",
5232	$table_name
5233	),
5234	'42S02'
5235	);
5236	}
5237
5238	$column_map = array_combine( array_column( $columns, 'COLUMN_NAME' ), $columns );
5239
5240	// Translate the UPDATE list, emulating IMPLICIT DEFAULTs for NULL values.
5241	$fragment = '';
5242	foreach ( $node->get_child_nodes() as $i => $update_element ) {
5243	$column_ref = $update_element->get_first_child_node( 'columnRef' );
5244	$column_ref_parts = $column_ref->get_descendant_nodes( 'identifier' );
5245	$expr = $update_element->get_first_child_node( 'expr' );
5246
5247	// Get column info.
5248	$column_name = $this->unquote_sqlite_identifier( $this->translate( end( $column_ref_parts ) ) );
5249	$column_info = $column_map[ strtolower( $column_name ) ] ?? null;
5250	if ( ! $column_info ) {
5251	throw $this->new_driver_exception(
5252	sprintf(
5253	"SQLSTATE[42S22]: Column not found: 1054 Unknown column '%s' in 'field list'",
5254	$column_name
5255	),
5256	'42S22'
5257	);
5258	}
5259
5260	$data_type = $column_info['DATA_TYPE'];
5261	$is_nullable = 'YES' === $column_info['IS_NULLABLE'];
5262	$default = $column_info['COLUMN_DEFAULT'];
5263
5264	// Get the UPDATE value. It's either an expression or a DEFAULT keyword.
5265	if ( null === $expr ) {
5266	// Emulate "column = DEFAULT".
5267	$value = null === $default ? 'NULL' : $this->quote_sqlite_value( $default );
5268	} else {
5269	$value = $this->translate( $expr );
5270	}
5271
5272	// Apply type casting.
5273	$value = $this->cast_value_for_saving( $data_type, $value );
5274
5275	/*
5276	* In MySQL non-STRICT mode, when a column is declared as NOT NULL,
5277	* updating to a NULL value saves an IMPLICIT DEFAULT value instead.
5278	* This behavior does not apply to ON DUPLICATE KEY UPDATE clauses.
5279	*/
5280	$is_on_duplicate_key_update = 'insertUpdateList' === $parent_node->rule_name;
5281	if ( ! $is_strict_mode && ! $is_nullable && ! $is_on_duplicate_key_update ) {
5282	$implicit_default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $data_type ] ?? null;
5283	if ( null !== $implicit_default ) {
5284	$value = sprintf( 'COALESCE(%s, %s)', $value, $this->quote_sqlite_value( $implicit_default ) );
5285	}
5286	}
5287
5288	// Compose the UPDATE list item.
5289	$fragment .= $i > 0 ? ', ' : '';
5290	$fragment .= $this->translate( end( $column_ref_parts ) );
5291	$fragment .= ' = ';
5292	$fragment .= $value;
5293	}
5294	return $fragment;
5295	}
5296
5297	/**
5298	* Store column metadata for the last SQLite statement.
5299	*
5300	* This function stores the original SQLite column metadata as-is, without
5301	* converting it into MySQL column metadata. That is done only when needed.
5302	*
5303	* @param PDOStatement $stmt The PDOStatement object containing the SQLite column metadata.
5304	*/
5305	private function store_last_column_meta_from_statement( PDOStatement $stmt ): void {
5306	$this->last_column_meta = array();
5307	for ( $i = 0; $i < $stmt->columnCount(); $i++ ) {
5308	/*
5309	* Workaround for PHP PDO SQLite bug (#79664) in PHP < 7.3.
5310	* See also: https://github.com/php/php-src/pull/5654
5311	*/
5312	if ( PHP_VERSION_ID < 70300 ) {
5313	try {
5314	$this->last_column_meta[] = $stmt->getColumnMeta( $i );
5315	} catch ( Throwable $e ) {
5316	$this->last_column_meta[] = array(
5317	'native_type' => 'null',
5318	'pdo_type' => PDO::PARAM_NULL,
5319	'flags' => array(),
5320	'table' => '',
5321	'name' => '',
5322	'len' => -1,
5323	'precision' => 0,
5324	);
5325	}
5326	continue;
5327	}
5328
5329	$this->last_column_meta[] = $stmt->getColumnMeta( $i );
5330	}
5331	}
5332
5333	/**
5334	* Unnest parenthesized MySQL expression node.
5335	*
5336	* In MySQL, extra parentheses around simple expressions are not considered.
5337	*
5338	* For example, the "SELECT (((id)))" clause is equivalent to "SELECT id".
5339	* This means that the "(((id)))" part will behave as a column name rather
5340	* than as an expression, and the resulting column name will be just "id".
5341	*
5342	* @param WP_Parser_Node $node The expression AST node.
5343	* @return WP_Parser_Node The unnested expression.
5344	*/
5345	private function unnest_parenthesized_expression( WP_Parser_Node $node ): WP_Parser_Node {
5346	$children = $node->get_children();
5347
5348	// Descend the "expr -> boolPri -> predicate -> bitExpr -> simpleExpr" tree,
5349	// when on each level we have only a single child node (expression nesting).
5350	if (
5351	1 === count( $children )
5352	&& $children[0] instanceof WP_Parser_Node
5353	&& in_array( $children[0]->rule_name, array( 'expr', 'boolPri', 'predicate', 'bitExpr', 'simpleExpr' ), true )
5354	) {
5355	$unnested = $this->unnest_parenthesized_expression( $children[0] );
5356	return $unnested === $children[0] ? $node : $unnested;
5357	}
5358
5359	// Unnest "OPEN_PAR_SYMBOL exprList CLOSE_PAR_SYMBOL" to "exprList".
5360	if (
5361	count( $children ) === 3
5362	&& $children[0] instanceof WP_MySQL_Token && WP_MySQL_Lexer::OPEN_PAR_SYMBOL === $children[0]->id
5363	&& $children[1] instanceof WP_Parser_Node && 'exprList' === $children[1]->rule_name
5364	&& $children[2] instanceof WP_MySQL_Token && WP_MySQL_Lexer::CLOSE_PAR_SYMBOL === $children[2]->id
5365	&& 1 === count( $children[1]->get_children() )
5366	) {
5367	return $this->unnest_parenthesized_expression( $children[1] );
5368	}
5369
5370	return $node;
5371	}
5372
5373	/**
5374	* Disambiguate and translate an expression with a simple or parenthesized
5375	* column reference for use within an ORDER BY, GROUP BY, or HAVING clause.
5376	*
5377	* In SQLite, columns that exist in multiple tables used within a query must
5378	* be fully qualified when used in the ORDER BY, GROUP BY, or HAVING clause.
5379	* In MySQL, these can be disambiguated using the SELECT item list.
5380	*
5381	* For example, when tables "t1" and "t2" both have a column called "name",
5382	* the following query will cause an "ambiguous column name" error in SQLite,
5383	* but it will succeed in MySQL, using the "t1.name" from the SELECT clause:
5384	*
5385	* SELECT t1.name FROM t1 JOIN t2 ON t2.t1_id = t1.id ORDER BY name
5386	*
5387	* This is because MySQL primarily considers the "name" column that was used
5388	* in the SELECT list - when it is unambiguous, it will be used in ORDER BY.
5389	*
5390	* To emulate this behavior in SQLite, we will search for unqualified column
5391	* references in the ORDER BY, GROUP BY, or HAVING item expression, and try
5392	* to qualify them using the SELECT item list.
5393	*
5394	* In other words, the above query will be rewritten as follows:
5395	*
5396	* SELECT t1.name FROM t1 JOIN t2 ON t2.t1_id = t1.id ORDER BY t1.name
5397	*
5398	* Note that the ORDER BY column was rewritten from "name" to "t1.name".
5399	*
5400	* @TODO: When multi-database support is implemented, we'll also need to
5401	* consider column references in forms like "db.table.column".
5402	*
5403	* @param array $disambiguation_map The SELECT item disambiguation map (column name => array of select items).
5404	* @see WP_SQLite_Driver::create_select_item_disambiguation_map()
5405	* @param WP_Parser_Node $expr The expression AST node or subnode.
5406	* @return string\|null The disambiguated and translated expression;
5407	* null when the expression cannot be disambiguated.
5408	*/
5409	private function disambiguate_item( array $disambiguation_map, WP_Parser_Node $expr ) {
5410	// Skip when there is no column in the expression (no "columnRef" node),
5411	// or when the column is already qualified (has a "dotIdentifier" node).
5412	$column_ref = $expr->get_first_descendant_node( 'columnRef' );
5413	if ( ! $column_ref \|\| $column_ref->get_first_descendant_node( 'dotIdentifier' ) ) {
5414	return null;
5415	}
5416
5417	// Support also parenthesized column references (e.g. "(id)").
5418	$expr = $this->unnest_parenthesized_expression( $expr );
5419
5420	// Consider only simple and parenthesized column references (as per MySQL).
5421	$expr_value = $this->translate( $expr );
5422	$column_value = $this->translate( $column_ref );
5423	if ( $expr_value !== $column_value ) {
5424	return null;
5425	}
5426
5427	// Look for SELECT items that match the column reference.
5428	$column_name = $this->translate( $column_ref );
5429	$select_item_matches = $disambiguation_map[ $column_name ] ?? array();
5430
5431	// When we find exactly one matching SELECT list item, we can disambiguate
5432	// the column reference. Otherwise, fall back to the original expression.
5433	if ( 1 === count( $select_item_matches ) ) {
5434	return $select_item_matches[0];
5435	}
5436	return null;
5437	}
5438
5439	/**
5440	* Create a SELECT item disambiguation map from a SELECT item list for use
5441	* with the ORDER BY, GROUP BY, and HAVING clause disambiguation algorithm.
5442	*
5443	* @see WP_SQLite_Driver::disambiguate_item()
5444	*
5445	* @param WP_Parser_Node $select_item_list The "selectItemList" AST node.
5446	* @return array The SELECT item disambiguation map (column name => array of select items).
5447	*/
5448	private function create_select_item_disambiguation_map( WP_Parser_Node $select_item_list ): array {
5449	// Create a map of SELECT item column names to their qualified values.
5450	$disambiguation_map = array();
5451	foreach ( $select_item_list->get_child_nodes() as $select_item ) {
5452	/*
5453	* [GRAMMAR]
5454	* selectItem: tableWild \| (expr selectAlias?)
5455	*/
5456
5457	// Skip when a "tableWild" node is used (no "expr" node).
5458	$select_item_expr = $select_item->get_first_child_node( 'expr' );
5459	if ( ! $select_item_expr ) {
5460	continue;
5461	}
5462
5463	// A SELECT item alias always needs to be preserved as-is.
5464	$alias = $select_item->get_first_child_node( 'selectAlias' );
5465	if ( $alias ) {
5466	$alias_value = $this->translate( $alias->get_first_child_node() );
5467	$disambiguation_map[ $alias_value ] = array( $alias_value );
5468	continue;
5469	}
5470
5471	// Skip when there is no column listed (no "columnRef" node).
5472	$select_column_ref = $select_item_expr->get_first_descendant_node( 'columnRef' );
5473	if ( ! $select_column_ref ) {
5474	continue;
5475	}
5476
5477	// Skip when the column reference is not qualified (no "dotIdentifier" node).
5478	$dot_identifiers = $select_column_ref->get_descendant_nodes( 'dotIdentifier' );
5479	if ( 0 === count( $dot_identifiers ) ) {
5480	continue;
5481	}
5482
5483	// Support also parenthesized column references (e.g. "(t.id)").
5484	$select_item_expr = $this->unnest_parenthesized_expression( $select_item_expr );
5485
5486	// Consider only simple and parenthesized column references (as per MySQL).
5487	$expr_value = $this->translate( $select_item_expr );
5488	$column_value = $this->translate( $select_column_ref );
5489	if ( $expr_value !== $column_value ) {
5490	continue;
5491	}
5492
5493	// The column name is the last "dotIdentifier" node.
5494	$key = $this->translate( end( $dot_identifiers )->get_first_child_node() );
5495
5496	$disambiguation_map[ $key ] = $disambiguation_map[ $key ] ?? array();
5497	$disambiguation_map[ $key ][] = $column_value;
5498	}
5499	return $disambiguation_map;
5500	}
5501
5502	/**
5503	* Analyze a "tableReferenceList" AST node and extract table data.
5504	*
5505	* This method extracts table data for all tables that are used at the root
5506	* level of a given query, including tables that are referenced using JOINs.
5507	*
5508	* The returned array maps table aliases to table names and additional data:
5509	* - key: table alias, or name if no alias is used
5510	* - value: an array of table data
5511	* - database: the database name of the table (null for derived tables)
5512	* - table_name: the real name of the table (null for derived tables)
5513	* - table_expr: the table expression for a derived table (null for regular tables)
5514	* - join_expr: the join expression used for the table (null when no join is used)
5515	*
5516	* MySQL has a non-stand ardsyntax extension where a comma-separated list of
5517	* table references is allowed as a table reference in itself, for instance:
5518	* SELECT * FROM (t1, t2) JOIN t3 ON 1
5519	*
5520	* Which is equivalent to:
5521	* SELECT * FROM (t1 CROSS JOIN t2) JOIN t3 ON 1
5522	*
5523	* @param WP_Parser_Node $node The "tableReferenceList" AST node.
5524	* @return array The table reference map (table alias => array of table data).
5525	*/
5526	private function create_table_reference_map( WP_Parser_Node $node ): array {
5527	$table_map = array();
5528
5529	// Collect all table references, including the ones used in JOINs.
5530	$table_refs = array();
5531	foreach ( $node->get_child_nodes( 'tableReference' ) as $table_ref ) {
5532	$table_refs[] = $table_ref;
5533	foreach ( $table_ref->get_child_nodes( 'joinedTable' ) as $joined_table ) {
5534	$table_refs[] = $joined_table;
5535	}
5536	}
5537
5538	// Process each table reference, extracting table data.
5539	foreach ( $table_refs as $table_ref ) {
5540	$table_factor = $table_ref->get_first_descendant_node( 'tableFactor' );
5541	$join_expr = $table_ref->get_first_child_node( 'expr' );
5542	$child = $table_factor->get_first_child_node();
5543
5544	// Descend all "singleTableParens" nodes to get the "singleTable" node.
5545	if ( 'singleTableParens' === $child->rule_name ) {
5546	$child = $child->get_first_descendant_node( 'singleTable' );
5547	}
5548
5549	if ( 'singleTable' === $child->rule_name ) {
5550	// Extract data from the "singleTable" node.
5551	$table_ref = $child->get_first_child_node( 'tableRef' );
5552	$name = $this->translate( $table_ref );
5553	$alias_node = $child->get_first_child_node( 'tableAlias' );
5554	$alias = $alias_node ? $this->translate( $alias_node->get_first_child_node( 'identifier' ) ) : null;
5555
5556	$table_map[ $this->unquote_sqlite_identifier( $alias ?? $name ) ] = array(
5557	'database' => $this->get_database_name( $table_ref ),
5558	'table_name' => $this->unquote_sqlite_identifier( $name ),
5559	'table_expr' => null,
5560	'join_expr' => $this->translate( $join_expr ),
5561	);
5562	} elseif ( 'derivedTable' === $child->rule_name ) {
5563	// Extract data from the "derivedTable" node.
5564	$subquery = $child->get_first_descendant_node( 'subquery' );
5565	$alias_node = $child->get_first_child_node( 'tableAlias' );
5566	$alias = $alias_node ? $this->translate( $alias_node->get_first_child_node( 'identifier' ) ) : null;
5567
5568	$table_map[ $this->unquote_sqlite_identifier( $alias ) ] = array(
5569	'database' => null,
5570	'table_name' => null,
5571	'table_expr' => $this->translate( $subquery ),
5572	'join_expr' => $this->translate( $join_expr ),
5573	);
5574	} elseif ( 'tableReferenceListParens' === $child->rule_name ) {
5575	// Recursively process the "tableReferenceListParens" node.
5576	$table_ref_list = $child->get_first_descendant_node( 'tableReferenceList' );
5577	$table_map = array_merge( $table_map, $this->create_table_reference_map( $table_ref_list ) );
5578	}
5579	}
5580	return $table_map;
5581	}
5582
5583	/**
5584	* Emulate MySQL type casting for values to be saved to the database
5585	* using INSERT, REPLACE, or UPDATE statements.
5586	*
5587	* @param string $mysql_data_type The MySQL data type.
5588	* @param string $translated_value The original translated value.
5589	* @return string The translated value.
5590	*/
5591	private function cast_value_for_saving(
5592	string $mysql_data_type,
5593	string $translated_value
5594	): string {
5595	// TODO: This is also a good place to implement checks for maximum column
5596	// lengths with truncating or bailing out depending on the SQL mode.
5597
5598	// Check if strict mode is enabled.
5599	$is_strict_mode = (
5600	$this->is_sql_mode_active( 'STRICT_TRANS_TABLES' )
5601	\|\| $this->is_sql_mode_active( 'STRICT_ALL_TABLES' )
5602	);
5603
5604	$mysql_data_type = strtolower( $mysql_data_type );
5605	$sqlite_data_type = self::DATA_TYPE_STRING_MAP[ $mysql_data_type ];
5606
5607	/*
5608	* In MySQL, when saving a value via INSERT or UPDATE in non-strict mode,
5609	* 1. MySQL attempts to cast the value to the target column data type.
5610	* 2. When casting can't be done, MySQL saves an IMPLICIT DEFAULT.
5611	*/
5612	switch ( $mysql_data_type ) {
5613	case 'date':
5614	case 'time':
5615	case 'datetime':
5616	case 'timestamp':
5617	case 'year':
5618	/*
5619	* MySQL supports date and time components without a zero padding,
5620	* but that doesn't work with date and time functions in SQLite.
5621	* E.g.: "2025-3-7 9:5:2" is a valid datetime/timestamp value in
5622	* in MySQL, but SQLite requires it to be "2025-03-07 09:05:02".
5623	*
5624	* A solution to this would need to be done on the SQL level to
5625	* address computed values, and it should be done for the strict
5626	* mode as well. This may require a user-defined function.
5627	*
5628	* TODO: Handle zero padding for date and time functions, while
5629	* supporting both strict and non-strict modes.
5630	*/
5631
5632	if ( 'date' === $mysql_data_type ) {
5633	$function_call = sprintf( 'DATE(%s)', $translated_value );
5634	} elseif ( 'time' === $mysql_data_type ) {
5635	$function_call = sprintf( 'TIME(%s)', $translated_value );
5636	} elseif ( 'datetime' === $mysql_data_type \|\| 'timestamp' === $mysql_data_type ) {
5637	$function_call = sprintf( 'DATETIME(%s)', $translated_value );
5638	} elseif ( 'year' === $mysql_data_type ) {
5639	/*
5640	* The YEAR type in MySQL only uses 1 byte and therefore
5641	* covers only 256 values from 1901 to 2155 included.
5642	* Additionally:
5643	* - Numbers from 0 to 69 correspond to years 2000 to 2069.
5644	* - Numbers from 70 to 99 correspond to years 1970 to 1999.
5645	*/
5646	return sprintf(
5647	"(
5648	SELECT CASE
5649	WHEN value IS NULL THEN NULL
5650	WHEN value = 0 THEN '0000'
5651	WHEN value BETWEEN 1901 AND 2155 THEN value
5652	WHEN value BETWEEN 1 AND 69 THEN 2000 + value
5653	WHEN value BETWEEN 70 AND 99 THEN 1900 + value
5654	ELSE %s
5655	END
5656	FROM (SELECT CAST(%s AS INTEGER) AS value)
5657	)",
5658	$is_strict_mode
5659	? sprintf( "THROW('Out of range value: ''' \|\| %s \|\| '''')", $translated_value )
5660	: "'0000'",
5661	$translated_value
5662	);
5663	}
5664
5665	// In strict mode, invalid date/time values are rejected.
5666	// In non-strict mode, they get an IMPLICIT DEFAULT value.
5667	if ( $is_strict_mode ) {
5668	$fallback = sprintf(
5669	"THROW('Incorrect %s value: ''' \|\| %s \|\| '''')",
5670	$mysql_data_type,
5671	$translated_value
5672	);
5673	} else {
5674	$implicit_default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $mysql_data_type ] ?? null;
5675	$fallback = null === $implicit_default
5676	? 'NULL'
5677	: $this->quote_sqlite_value( $implicit_default );
5678	}
5679
5680	/*
5681	* Build the CASE expression for date/time validation.
5682	*
5683	* SQLite's DATE()/DATETIME() functions return NULL for zero
5684	* dates, so the CASE includes explicit checks controlled by
5685	* the NO_ZERO_DATE and NO_ZERO_IN_DATE SQL modes.
5686	*
5687	* In MySQL, the behavior of zero dates depends on these modes:
5688	*
5689	* NO_ZERO_DATE (see https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_no_zero_date):
5690	* - Disabled: '0000-00-00' is permitted and produces no warning.
5691	* - Enabled without strict mode: '0000-00-00' is permitted but produces a warning.
5692	* - Enabled with strict mode: '0000-00-00' is not permitted and produces an error.
5693	*
5694	* NO_ZERO_IN_DATE (see https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_no_zero_in_date):
5695	* - Disabled: dates with zero month/day parts (e.g. '2020-00-15') are permitted.
5696	* - Enabled without strict mode: zero-part dates produce a warning and are stored as '0000-00-00'.
5697	* - Enabled with strict mode: zero-part dates produce an error.
5698	*/
5699	return strtr(
5700	"CASE
5701	WHEN {value} IS NULL THEN NULL
5702	WHEN {value} IN ('0000-00-00', '0000-00-00 00:00:00') AND NOT {reject_zero_date} THEN {zero_date_value}
5703	WHEN SUBSTR({value}, 1, 4) != '0000' AND (SUBSTR({value}, 6, 2) = '00' OR SUBSTR({value}, 9, 2) = '00') AND NOT {reject_zero_in_date} THEN {value}
5704	WHEN {function_call} > '0' THEN {function_call}
5705	ELSE {fallback}
5706	END",
5707	array(
5708	'{value}' => $translated_value,
5709	'{reject_zero_date}' => (
5710	$this->is_sql_mode_active( 'NO_ZERO_DATE' ) && $is_strict_mode
5711	) ? 1 : 0,
5712	'{zero_date_value}' => 'date' === $mysql_data_type
5713	? "'0000-00-00'"
5714	: "'0000-00-00 00:00:00'",
5715	'{reject_zero_in_date}' => $this->is_sql_mode_active( 'NO_ZERO_IN_DATE' ) ? 1 : 0,
5716	'{function_call}' => $function_call,
5717	'{fallback}' => $fallback,
5718	)
5719	);
5720	default:
5721	/*
5722	* For all other data types, cast to the SQLite types as follows:
5723	* 1. In strict mode, cast only values for TEXT and BLOB columns.
5724	* Numeric types accept string notation in SQLite as well.
5725	* 2. In non-strict mode, cast all values.
5726	*
5727	* TODO: While close to MySQL behavior, this doesn't exactly match
5728	* all special cases. We may improve this further to accept
5729	* BLOBs for numeric types, and other special behaviors.
5730	*/
5731	if ( ! $is_strict_mode \|\| 'TEXT' === $sqlite_data_type \|\| 'BLOB' === $sqlite_data_type ) {
5732	return sprintf( 'CAST(%s AS %s)', $translated_value, $sqlite_data_type );
5733	}
5734	return $translated_value;
5735	}
5736	}
5737
5738	/**
5739	* Get the database name as it is saved in the information schema tables.
5740	*
5741	* @param string\|null $db_name Optional. The database name to use. Defaults to the current database name.
5742	* @return string The database name as it is saved in the information schema tables.
5743	*/
5744	private function get_saved_db_name( ?string $db_name = null ): string {
5745	if ( null === $db_name ) {
5746	$db_name = $this->db_name;
5747	}
5748	return $this->main_db_name === $db_name
5749	? WP_SQLite_Information_Schema_Builder::SAVED_DATABASE_NAME
5750	: $db_name;
5751	}
5752
5753	/**
5754	* Get the database name from one of fully-qualified name AST nodes.
5755	*
5756	* @param WP_Parser_Node $node The AST node. One of "tableName", "tableRef", or "inDb".
5757	* @return string The database name.
5758	*/
5759	private function get_database_name( WP_Parser_Node $node ): string {
5760	if ( 'tableName' === $node->rule_name \|\| 'tableRef' === $node->rule_name ) {
5761	$parts = $node->get_descendant_nodes( 'identifier' );
5762	if ( count( $parts ) > 1 ) {
5763	return $this->unquote_sqlite_identifier( $this->translate( $parts[0] ) );
5764	} else {
5765	return $this->db_name;
5766	}
5767	} elseif ( 'inDb' === $node->rule_name ) {
5768	return $this->unquote_sqlite_identifier(
5769	$this->translate( $node->get_first_child_node( 'identifier' ) )
5770	);
5771	}
5772
5773	throw $this->new_driver_exception(
5774	sprintf( 'Could not get database name from node: %s', $node->rule_name )
5775	);
5776	}
5777
5778	/**
5779	* Generate a SQLite CREATE TABLE statement from information schema data.
5780	*
5781	* @param bool $table_is_temporary Whether the table is temporary.
5782	* @param string $table_name The name of the table to create.
5783	* @param string\|null $new_table_name Override the original table name for ALTER TABLE emulation.
5784	* @return string[] Queries to create the table, indexes, and constraints.
5785	* @throws WP_SQLite_Driver_Exception When the table information is missing.
5786	*/
5787	private function get_sqlite_create_table_statement(
5788	bool $table_is_temporary,
5789	string $table_name,
5790	?string $new_table_name = null
5791	): array {
5792	// This method is always used with the main database.
5793	$database = $this->get_saved_db_name( $this->main_db_name );
5794
5795	// 1. Get table info.
5796	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
5797	$table_info = $this->execute_sqlite_query(
5798	'
5799	SELECT *
5800	FROM ' . $this->quote_sqlite_identifier( $tables_table ) . "
5801	WHERE table_type = 'BASE TABLE'
5802	AND table_schema = ?
5803	AND table_name = ?
5804	",
5805	array( $database, $table_name )
5806	)->fetch( PDO::FETCH_ASSOC );
5807
5808	if ( false === $table_info ) {
5809	throw $this->new_driver_exception(
5810	sprintf( "Table '%s' doesn't exist", $table_name ),
5811	'42S02'
5812	);
5813	}
5814
5815	// 2. Get column info.
5816	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
5817	$column_info = $this->execute_sqlite_query(
5818	sprintf(
5819	'SELECT * FROM %s WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position',
5820	$this->quote_sqlite_identifier( $columns_table )
5821	),
5822	array( $database, $table_name )
5823	)->fetchAll( PDO::FETCH_ASSOC );
5824
5825	// 3. Get index info, grouped by index name.
5826	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
5827	$constraint_info = $this->execute_sqlite_query(
5828	sprintf(
5829	"
5830	SELECT *
5831	FROM %s
5832	WHERE table_schema = ?
5833	AND table_name = ?
5834	ORDER BY
5835	INDEX_NAME = 'PRIMARY' DESC,
5836	NON_UNIQUE = '0' DESC,
5837	INDEX_TYPE = 'SPATIAL' DESC,
5838	INDEX_TYPE = 'BTREE' DESC,
5839	INDEX_TYPE = 'FULLTEXT' DESC,
5840	ROWID,
5841	SEQ_IN_INDEX
5842	",
5843	$this->quote_sqlite_identifier( $statistics_table )
5844	),
5845	array( $database, $table_name )
5846	)->fetchAll( PDO::FETCH_ASSOC );
5847
5848	$grouped_constraints = array();
5849	foreach ( $constraint_info as $constraint ) {
5850	$name = $constraint['INDEX_NAME'];
5851	$seq = $constraint['SEQ_IN_INDEX'];
5852	$grouped_constraints[ $name ][ $seq ] = $constraint;
5853	}
5854
5855	// 4. Get foreign key info.
5856	$referential_constraints_table = $this->information_schema_builder
5857	->get_table_name( $table_is_temporary, 'referential_constraints' );
5858	$referential_constraints_info = $this->execute_sqlite_query(
5859	sprintf(
5860	'SELECT * FROM %s WHERE constraint_schema = ? AND table_name = ? ORDER BY constraint_name',
5861	$this->quote_sqlite_identifier( $referential_constraints_table )
5862	),
5863	array( $database, $table_name )
5864	)->fetchAll( PDO::FETCH_ASSOC );
5865
5866	$key_column_usage_map = array();
5867	if ( count( $referential_constraints_info ) > 0 ) {
5868	$key_column_usage_table = $this->information_schema_builder
5869	->get_table_name( $table_is_temporary, 'key_column_usage' );
5870	$key_column_usage_info = $this->execute_sqlite_query(
5871	sprintf(
5872	'SELECT * FROM %s WHERE table_schema = ? AND table_name = ? AND referenced_column_name IS NOT NULL',
5873	$this->quote_sqlite_identifier( $key_column_usage_table )
5874	),
5875	array( $database, $table_name )
5876	)->fetchAll( PDO::FETCH_ASSOC );
5877
5878	$key_column_usage_map = array();
5879	foreach ( $key_column_usage_info as $key_column_usage ) {
5880	$constraint_name = $key_column_usage['CONSTRAINT_NAME'];
5881	if ( ! isset( $key_column_usage_map[ $constraint_name ] ) ) {
5882	$key_column_usage_map[ $constraint_name ] = array();
5883	}
5884	$key_column_usage_map[ $constraint_name ][] = array(
5885	$key_column_usage['COLUMN_NAME'],
5886	$key_column_usage['REFERENCED_COLUMN_NAME'],
5887	);
5888	}
5889	}
5890
5891	// 5. Get CHECK constraint info.
5892	$table_constraints_table = $this->information_schema_builder
5893	->get_table_name( $table_is_temporary, 'table_constraints' );
5894	$check_constraints_table = $this->information_schema_builder
5895	->get_table_name( $table_is_temporary, 'check_constraints' );
5896	$check_constraints_info = $this->execute_sqlite_query(
5897	sprintf(
5898	'SELECT tc.*, cc.check_clause
5899	FROM %s tc
5900	JOIN %s cc ON cc.constraint_name = tc.constraint_name
5901	WHERE tc.constraint_schema = ?
5902	AND tc.table_name = ?
5903	ORDER BY tc.constraint_name',
5904	$this->quote_sqlite_identifier( $table_constraints_table ),
5905	$this->quote_sqlite_identifier( $check_constraints_table )
5906	),
5907	array( $database, $table_name )
5908	)->fetchAll( PDO::FETCH_ASSOC );
5909
5910	// 6. Generate CREATE TABLE statement columns.
5911	$rows = array();
5912	$on_update_queries = array();
5913	$has_autoincrement = false;
5914	foreach ( $column_info as $column ) {
5915	$query = ' ';
5916	$query .= $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
5917
5918	$type = self::DATA_TYPE_STRING_MAP[ $column['DATA_TYPE'] ];
5919
5920	/*
5921	* In SQLite, there is a PRIMARY KEY quirk for backward compatibility.
5922	* This applies to ROWID tables and single-column primary keys only:
5923	* 1. "INTEGER PRIMARY KEY" creates an alias of ROWID.
5924	* 2. "INT PRIMARY KEY" will not alias of ROWID.
5925	*
5926	* Therefore, we want to:
5927	* 1. Use "INT PRIMARY KEY" when we have a single-column integer
5928	* PRIMARY KEY without AUTOINCREMENT (to avoid the ROWID alias).
5929	* 2. Use "INTEGER PRIMARY KEY" otherwise.
5930	*
5931	* In SQLite, "AUTOINCREMENT" is only allowed on "INTEGER PRIMARY KEY",
5932	* and setting it changes the automatic ROWID assignment algorithm to
5933	* prevent the reuse of ROWIDs. Using "INT PRIMARY KEY" is not allowed.
5934	*
5935	* See:
5936	* - https://www.sqlite.org/autoinc.html
5937	* - https://www.sqlite.org/lang_createtable.html
5938	*/
5939	if (
5940	'INTEGER' === $type
5941	&& 'PRI' === $column['COLUMN_KEY']
5942	&& 'auto_increment' !== $column['EXTRA']
5943	&& count( $grouped_constraints['PRIMARY'] ) === 1
5944	) {
5945	$type = 'INT';
5946	}
5947
5948	$query .= ' ' . $type;
5949
5950	// In MySQL, text fields are case-insensitive by default.
5951	// COLLATE NOCASE emulates the same behavior in SQLite.
5952	// @TODO: Respect the actual column and index collation.
5953	if ( 'TEXT' === $type ) {
5954	$query .= ' COLLATE NOCASE';
5955	}
5956	if ( 'NO' === $column['IS_NULLABLE'] ) {
5957	$query .= ' NOT NULL';
5958	}
5959	if ( 'auto_increment' === $column['EXTRA'] ) {
5960	$has_autoincrement = true;
5961	$query .= ' PRIMARY KEY AUTOINCREMENT';
5962	}
5963	if ( null !== $column['COLUMN_DEFAULT'] ) {
5964	// Handle DEFAULT CURRENT_TIMESTAMP. This works only with timestamp
5965	// and datetime columns. For other column types, it's just a string.
5966	if (
5967	'CURRENT_TIMESTAMP' === $column['COLUMN_DEFAULT']
5968	&& ( 'timestamp' === $column['DATA_TYPE'] \|\| 'datetime' === $column['DATA_TYPE'] )
5969	) {
5970	$query .= ' DEFAULT CURRENT_TIMESTAMP';
5971	} elseif ( str_contains( $column['EXTRA'], 'DEFAULT_GENERATED' ) ) {
5972	// Handle DEFAULT values with expressions (DEFAULT_GENERATED).
5973	// Translate the default clause from MySQL to SQLite.
5974	$ast = $this->create_parser( 'SELECT ' . $column['COLUMN_DEFAULT'] )->parse();
5975	$expr = $ast->get_first_descendant_node( 'selectItem' )->get_first_child_node();
5976	$default_clause = $this->translate( $expr );
5977	$query .= sprintf( ' DEFAULT (%s)', $default_clause );
5978	} else {
5979	$query .= ' DEFAULT ' . $this->quote_sqlite_value( $column['COLUMN_DEFAULT'] );
5980	}
5981	}
5982	$rows[] = $query;
5983
5984	if ( 'on update CURRENT_TIMESTAMP' === $column['EXTRA'] ) {
5985	$on_update_queries[] = $this->get_column_on_update_trigger_query(
5986	$table_name,
5987	$column['COLUMN_NAME']
5988	);
5989	}
5990	}
5991
5992	// 6. Generate CREATE TABLE statement constraints, collect indexes.
5993	$create_index_queries = array();
5994	foreach ( $grouped_constraints as $constraint ) {
5995	ksort( $constraint );
5996	$info = $constraint[1];
5997
5998	$column_list = array_map(
5999	function ( $column ) {
6000	$fragment = $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
6001	if ( 'D' === $column['COLLATION'] ) {
6002	$fragment .= ' DESC';
6003	}
6004	return $fragment;
6005	},
6006	$constraint
6007	);
6008
6009	if ( 'PRIMARY' === $info['INDEX_NAME'] ) {
6010	if ( $has_autoincrement ) {
6011	/*
6012	* In MySQL, a compound PRIMARY KEY can have an AUTO_INCREMENT
6013	* column, when it is the first column in the key.
6014	*
6015	* SQLite doesn't support this, but we can emulate it as follows:
6016	* 1. Keep only the first column as a PRIMARY KEY.
6017	* Since this is the column that also has AUTO_INCREMENT,
6018	* it reasonable to assume that its values are unique.
6019	* 2. Create a UNIQUE key for all the PRIMARY KEY columns.
6020	* This is to preserve the index of the compound key.
6021	*/
6022	if ( count( $constraint ) > 1 ) {
6023	$sqlite_index_name = $this->get_sqlite_index_name( $table_name, 'primary' );
6024	$create_index_queries[] = sprintf(
6025	'CREATE UNIQUE INDEX %s ON %s (%s)',
6026	self::RESERVED_PREFIX . $sqlite_index_name,
6027	$this->quote_sqlite_identifier( $table_name ),
6028	implode( ', ', $column_list )
6029	);
6030	}
6031
6032	/*
6033	* The PRIMARY KEY was already generated with AUTOINCREMENT,
6034	* as required by SQLite column constraint syntax.
6035	*
6036	* @see https://www.sqlite.org/syntax/column-constraint.html
6037	*/
6038	continue;
6039	}
6040	$rows[] = sprintf( ' PRIMARY KEY (%s)', implode( ', ', $column_list ) );
6041	} else {
6042	$is_unique = '0' === $info['NON_UNIQUE'];
6043
6044	// Prefix the original index name with the table name.
6045	// This is to avoid conflicting index names in SQLite.
6046	$sqlite_index_name = $this->get_sqlite_index_name( $table_name, $info['INDEX_NAME'] );
6047
6048	$create_index_queries[] = sprintf(
6049	'CREATE %sINDEX %s ON %s (%s)',
6050	$is_unique ? 'UNIQUE ' : '',
6051	$this->quote_sqlite_identifier( $sqlite_index_name ),
6052	$this->quote_sqlite_identifier( $table_name ),
6053	implode( ', ', $column_list )
6054	);
6055	}
6056	}
6057
6058	// 8. Add foreign key constraints.
6059	foreach ( $referential_constraints_info as $referential_constraint ) {
6060	$column_names = array();
6061	$referenced_column_names = array();
6062	foreach ( $key_column_usage_map[ $referential_constraint['CONSTRAINT_NAME'] ] as $info ) {
6063	$column_names[] = $this->quote_sqlite_identifier( $info[0] );
6064	$referenced_column_names[] = $this->quote_sqlite_identifier( $info[1] );
6065	}
6066	$query = sprintf(
6067	' CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
6068	$this->quote_sqlite_identifier( $referential_constraint['CONSTRAINT_NAME'] ),
6069	implode( ', ', $column_names ),
6070	$this->quote_sqlite_identifier( $referential_constraint['REFERENCED_TABLE_NAME'] ),
6071	implode( ', ', $referenced_column_names )
6072	);
6073
6074	// ON DELETE
6075	$delete_rule = $referential_constraint['DELETE_RULE'];
6076	if ( 'NO ACTION' === $delete_rule ) {
6077	// In MySQL, NO ACTION is equivalent to RESTRICT with InnoDB.
6078	$delete_rule = 'RESTRICT';
6079	}
6080	$query .= sprintf( ' ON DELETE %s', $delete_rule );
6081
6082	// ON UPDATE
6083	$update_rule = $referential_constraint['UPDATE_RULE'];
6084	if ( 'NO ACTION' === $update_rule ) {
6085	// In MySQL, NO ACTION is equivalent to RESTRICT with InnoDB.
6086	$update_rule = 'RESTRICT';
6087	}
6088	$query .= sprintf( ' ON UPDATE %s', $update_rule );
6089
6090	$rows[] = $query;
6091	}
6092
6093	// 9. Add CHECK constraints.
6094	foreach ( $check_constraints_info as $check_constraint ) {
6095	if ( 'NO' === $check_constraint['ENFORCED'] ) {
6096	continue;
6097	}
6098
6099	// Translate the check clause from MySQL to SQLite.
6100	$ast = $this->create_parser( 'SELECT ' . $check_constraint['CHECK_CLAUSE'] )->parse();
6101	$expr = $ast->get_first_descendant_node( 'selectItem' )->get_first_child_node();
6102	$check_clause = $this->translate( $expr );
6103
6104	$sql = sprintf(
6105	' CONSTRAINT %s CHECK (%s)',
6106	$this->quote_sqlite_identifier( $check_constraint['CONSTRAINT_NAME'] ),
6107	$check_clause
6108	);
6109	$rows[] = $sql;
6110	}
6111
6112	// 10. Compose the CREATE TABLE statement.
6113	$create_table_query = sprintf(
6114	"CREATE %sTABLE %s (\n",
6115	$table_is_temporary ? 'TEMPORARY ' : '',
6116	$this->quote_sqlite_identifier( $new_table_name ?? $table_name )
6117	);
6118	$create_table_query .= implode( ",\n", $rows );
6119	$create_table_query .= "\n)";
6120
6121	if ( version_compare( $this->get_sqlite_version(), '3.37.0', '>=' ) ) {
6122	$create_table_query .= ' STRICT';
6123	}
6124
6125	return array_merge( array( $create_table_query ), $create_index_queries, $on_update_queries );
6126	}
6127
6128	/**
6129	* Generate a MySQL CREATE TABLE statement from information schema data.
6130	*
6131	* @param bool $table_is_temporary Whether the table is temporary.
6132	* @param string $table_name The name of the table to create.
6133	* @return string The CREATE TABLE statement.
6134	*/
6135	private function get_mysql_create_table_statement( bool $table_is_temporary, string $table_name ): ?string {
6136	// This method is always used with the main database.
6137	$database = $this->get_saved_db_name( $this->main_db_name );
6138
6139	// 1. Get table info.
6140	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
6141	$table_info = $this->execute_sqlite_query(
6142	'
6143	SELECT *
6144	FROM ' . $this->quote_sqlite_identifier( $tables_table ) . "
6145	WHERE table_type = 'BASE TABLE'
6146	AND table_schema = ?
6147	AND table_name = ?
6148	",
6149	array( $database, $table_name )
6150	)->fetch( PDO::FETCH_ASSOC );
6151
6152	if ( false === $table_info ) {
6153	return null;
6154	}
6155
6156	// 2. Get column info.
6157	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
6158	$column_info = $this->execute_sqlite_query(
6159	sprintf(
6160	'
6161	SELECT *
6162	FROM %s
6163	WHERE table_schema = ?
6164	AND table_name = ?
6165	ORDER BY ordinal_position
6166	',
6167	$this->quote_sqlite_identifier( $columns_table )
6168	),
6169	array( $database, $table_name )
6170	)->fetchAll( PDO::FETCH_ASSOC );
6171
6172	// 3. Get index info, grouped by index name.
6173	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
6174	$constraint_info = $this->execute_sqlite_query(
6175	sprintf(
6176	"
6177	SELECT *
6178	FROM %s
6179	WHERE table_schema = ?
6180	AND table_name = ?
6181	ORDER BY
6182	INDEX_NAME = 'PRIMARY' DESC,
6183	NON_UNIQUE = '0' DESC,
6184	INDEX_TYPE = 'SPATIAL' DESC,
6185	INDEX_TYPE = 'BTREE' DESC,
6186	INDEX_TYPE = 'FULLTEXT' DESC,
6187	ROWID,
6188	SEQ_IN_INDEX
6189	",
6190	$this->quote_sqlite_identifier( $statistics_table )
6191	),
6192	array( $database, $table_name )
6193	)->fetchAll( PDO::FETCH_ASSOC );
6194
6195	$grouped_constraints = array();
6196	foreach ( $constraint_info as $constraint ) {
6197	$name = $constraint['INDEX_NAME'];
6198	$seq = $constraint['SEQ_IN_INDEX'];
6199	$grouped_constraints[ $name ][ $seq ] = $constraint;
6200	}
6201
6202	// 4. Get foreign key info.
6203	$referential_constraints_table = $this->information_schema_builder
6204	->get_table_name( $table_is_temporary, 'referential_constraints' );
6205	$referential_constraints_info = $this->execute_sqlite_query(
6206	sprintf(
6207	'SELECT * FROM %s WHERE constraint_schema = ? AND table_name = ? ORDER BY constraint_name',
6208	$this->quote_sqlite_identifier( $referential_constraints_table )
6209	),
6210	array( $database, $table_name )
6211	)->fetchAll( PDO::FETCH_ASSOC );
6212
6213	$key_column_usage_map = array();
6214	if ( count( $referential_constraints_info ) > 0 ) {
6215	$key_column_usage_table = $this->information_schema_builder
6216	->get_table_name( $table_is_temporary, 'key_column_usage' );
6217	$key_column_usage_info = $this->execute_sqlite_query(
6218	sprintf(
6219	'SELECT * FROM %s WHERE table_schema = ? AND table_name = ? AND referenced_column_name IS NOT NULL',
6220	$this->quote_sqlite_identifier( $key_column_usage_table )
6221	),
6222	array( $database, $table_name )
6223	)->fetchAll( PDO::FETCH_ASSOC );
6224
6225	$key_column_usage_map = array();
6226	foreach ( $key_column_usage_info as $key_column_usage ) {
6227	$constraint_name = $key_column_usage['CONSTRAINT_NAME'];
6228	if ( ! isset( $key_column_usage_map[ $constraint_name ] ) ) {
6229	$key_column_usage_map[ $constraint_name ] = array();
6230	}
6231	$key_column_usage_map[ $constraint_name ][] = array(
6232	$key_column_usage['COLUMN_NAME'],
6233	$key_column_usage['REFERENCED_COLUMN_NAME'],
6234	);
6235	}
6236	}
6237
6238	// 5. Get CHECK constraint info.
6239	$table_constraints_table = $this->information_schema_builder
6240	->get_table_name( $table_is_temporary, 'table_constraints' );
6241	$check_constraints_table = $this->information_schema_builder
6242	->get_table_name( $table_is_temporary, 'check_constraints' );
6243	$check_constraints_info = $this->execute_sqlite_query(
6244	sprintf(
6245	'SELECT tc.*, cc.check_clause
6246	FROM %s tc
6247	JOIN %s cc ON cc.constraint_name = tc.constraint_name
6248	WHERE tc.constraint_schema = ?
6249	AND tc.table_name = ?
6250	ORDER BY tc.constraint_name',
6251	$this->quote_sqlite_identifier( $table_constraints_table ),
6252	$this->quote_sqlite_identifier( $check_constraints_table )
6253	),
6254	array( $database, $table_name )
6255	)->fetchAll( PDO::FETCH_ASSOC );
6256
6257	// 6. Generate CREATE TABLE statement columns.
6258	$rows = array();
6259	foreach ( $column_info as $column ) {
6260	$sql = ' ';
6261	$sql .= $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
6262	$sql .= ' ' . $column['COLUMN_TYPE'];
6263	if ( 'NO' === $column['IS_NULLABLE'] ) {
6264	$sql .= ' NOT NULL';
6265	} elseif ( 'timestamp' === $column['COLUMN_TYPE'] ) {
6266	// Nullable "timestamp" columns dump NULL explicitly.
6267	$sql .= ' NULL';
6268	}
6269	if ( 'auto_increment' === $column['EXTRA'] ) {
6270	$sql .= ' AUTO_INCREMENT';
6271	}
6272
6273	// Handle DEFAULT CURRENT_TIMESTAMP. This works only with timestamp
6274	// and datetime columns. For other column types, it's just a string.
6275	if (
6276	'CURRENT_TIMESTAMP' === $column['COLUMN_DEFAULT']
6277	&& ( 'timestamp' === $column['DATA_TYPE'] \|\| 'datetime' === $column['DATA_TYPE'] )
6278	) {
6279	$sql .= ' DEFAULT CURRENT_TIMESTAMP';
6280	} elseif ( null !== $column['COLUMN_DEFAULT'] ) {
6281	if ( str_contains( $column['EXTRA'], 'DEFAULT_GENERATED' ) ) {
6282	$sql .= sprintf( ' DEFAULT (%s)', $column['COLUMN_DEFAULT'] );
6283	} else {
6284	$sql .= ' DEFAULT ' . $this->quote_mysql_utf8_string_literal( $column['COLUMN_DEFAULT'] );
6285	}
6286	} elseif ( 'YES' === $column['IS_NULLABLE'] ) {
6287	$sql .= ' DEFAULT NULL';
6288	}
6289
6290	// Handle ON UPDATE CURRENT_TIMESTAMP.
6291	if ( str_contains( $column['EXTRA'], 'on update CURRENT_TIMESTAMP' ) ) {
6292	$sql .= ' ON UPDATE CURRENT_TIMESTAMP';
6293	}
6294
6295	if ( '' !== $column['COLUMN_COMMENT'] ) {
6296	$sql .= sprintf(
6297	' COMMENT %s',
6298	$this->quote_mysql_utf8_string_literal( $column['COLUMN_COMMENT'] )
6299	);
6300	}
6301
6302	$rows[] = $sql;
6303	}
6304
6305	// 7. Generate CREATE TABLE statement constraints, collect indexes.
6306	foreach ( $grouped_constraints as $constraint ) {
6307	ksort( $constraint );
6308	$info = $constraint[1];
6309
6310	if ( 'PRIMARY' === $info['INDEX_NAME'] ) {
6311	$sql = ' PRIMARY KEY (';
6312	$sql .= implode(
6313	', ',
6314	array_map(
6315	function ( $column ) {
6316	return $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
6317	},
6318	$constraint
6319	)
6320	);
6321	$sql .= ')';
6322	} else {
6323	$is_unique = '0' === $info['NON_UNIQUE'];
6324
6325	$sql = sprintf(
6326	' %s%s%sKEY ',
6327	$is_unique ? 'UNIQUE ' : '',
6328	'FULLTEXT' === $info['INDEX_TYPE'] ? 'FULLTEXT ' : '',
6329	'SPATIAL' === $info['INDEX_TYPE'] ? 'SPATIAL ' : ''
6330	);
6331	$sql .= $this->quote_mysql_identifier( $info['INDEX_NAME'] );
6332	$sql .= ' (';
6333	$sql .= implode(
6334	', ',
6335	array_map(
6336	function ( $column ) {
6337	$definition = $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
6338	if ( null !== $column['SUB_PART'] ) {
6339	$definition .= sprintf( '(%d)', $column['SUB_PART'] );
6340	}
6341	if ( 'D' === $column['COLLATION'] ) {
6342	$definition .= ' DESC';
6343	}
6344	return $definition;
6345	},
6346	$constraint
6347	)
6348	);
6349	$sql .= ')';
6350	}
6351
6352	if ( '' !== $info['INDEX_COMMENT'] ) {
6353	$sql .= sprintf(
6354	' COMMENT %s',
6355	$this->quote_mysql_utf8_string_literal( $info['INDEX_COMMENT'] )
6356	);
6357	}
6358
6359	$rows[] = $sql;
6360	}
6361
6362	// 8. Add foreign key constraints.
6363	foreach ( $referential_constraints_info as $referential_constraint ) {
6364	$column_names = array();
6365	$referenced_column_names = array();
6366	foreach ( $key_column_usage_map[ $referential_constraint['CONSTRAINT_NAME'] ] as $info ) {
6367	$column_names[] = $this->quote_mysql_identifier( $info[0] );
6368	$referenced_column_names[] = $this->quote_mysql_identifier( $info[1] );
6369	}
6370	$sql = sprintf(
6371	' CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
6372	$this->quote_mysql_identifier( $referential_constraint['CONSTRAINT_NAME'] ),
6373	implode( ', ', $column_names ),
6374	$this->quote_mysql_identifier( $referential_constraint['REFERENCED_TABLE_NAME'] ),
6375	implode( ', ', $referenced_column_names )
6376	);
6377	if ( 'NO ACTION' !== $referential_constraint['DELETE_RULE'] ) {
6378	$sql .= sprintf( ' ON DELETE %s', $referential_constraint['DELETE_RULE'] );
6379	}
6380	if ( 'NO ACTION' !== $referential_constraint['UPDATE_RULE'] ) {
6381	$sql .= sprintf( ' ON UPDATE %s', $referential_constraint['UPDATE_RULE'] );
6382	}
6383	$rows[] = $sql;
6384	}
6385
6386	// 9. Add CHECK constraints.
6387	foreach ( $check_constraints_info as $check_constraint ) {
6388	$sql = sprintf(
6389	' CONSTRAINT %s CHECK (%s)%s',
6390	$this->quote_mysql_identifier( $check_constraint['CONSTRAINT_NAME'] ),
6391	$check_constraint['CHECK_CLAUSE'],
6392	'NO' === $check_constraint['ENFORCED'] ? ' /!80016 NOT ENFORCED /' : ''
6393	);
6394	$rows[] = $sql;
6395	}
6396
6397	// 10. Compose the CREATE TABLE statement.
6398	$collation = $table_info['TABLE_COLLATION'];
6399	$charset = substr( $collation, 0, strpos( $collation, '_' ) );
6400
6401	$sql = sprintf(
6402	"CREATE %sTABLE %s (\n",
6403	$table_is_temporary ? 'TEMPORARY ' : '',
6404	$this->quote_mysql_identifier( $table_name )
6405	);
6406	$sql .= implode( ",\n", $rows );
6407	$sql .= "\n)";
6408	$sql .= sprintf( ' ENGINE=%s', $table_info['ENGINE'] );
6409	$sql .= sprintf( ' DEFAULT CHARSET=%s', $charset );
6410	$sql .= sprintf( ' COLLATE=%s', $collation );
6411	if ( '' !== $table_info['TABLE_COMMENT'] ) {
6412	$sql .= sprintf(
6413	' COMMENT=%s',
6414	$this->quote_mysql_utf8_string_literal( $table_info['TABLE_COMMENT'] )
6415	);
6416	}
6417	return $sql;
6418	}
6419
6420	/**
6421	* Get an unique SQLite index name from a MySQL table name and index name.
6422	*
6423	* @param string $table_name The MySQL table name.
6424	* @param string $index_name The MySQL index name.
6425	* @return string The SQLite index name.
6426	*/
6427	private function get_sqlite_index_name( string $mysql_table_name, string $mysql_index_name ): string {
6428	// Prefix the original index name with the table name.
6429	// This is to avoid conflicting index names in SQLite.
6430	return $mysql_table_name . '__' . $mysql_index_name;
6431	}
6432
6433	/**
6434	* Get an internal savepoint name.
6435	*
6436	* Internal savepoints are used to emulate MySQL transactions that are run
6437	* inside a wrapping SQLite transaction, as transactions can't be nested.
6438	*
6439	* @param string $name The name of the savepoint.
6440	* @return string The internal savepoint name.
6441	*/
6442	private function get_internal_savepoint_name( string $name ): string {
6443	return sprintf( '%ssavepoint_%s', self::RESERVED_PREFIX, $name );
6444	}
6445
6446	/**
6447	* Get an SQLite query to emulate MySQL "ON UPDATE CURRENT_TIMESTAMP".
6448	*
6449	* In SQLite, "ON UPDATE CURRENT_TIMESTAMP" is not supported. We need to
6450	* create a trigger to emulate this behavior.
6451	*
6452	* @param string $table The table name.
6453	* @param string $column The column name.
6454	*/
6455	private function get_column_on_update_trigger_query( string $table, string $column ): string {
6456	// The trigger wouldn't work for virtual and "WITHOUT ROWID" tables,
6457	// but currently that can't happen as we're not creating such tables.
6458	// See: https://www.sqlite.org/rowidtable.html
6459	$trigger_name = self::RESERVED_PREFIX . "{$table}_{$column}_on_update";
6460	return sprintf(
6461	'
6462	CREATE TRIGGER %s
6463	AFTER UPDATE ON %s
6464	FOR EACH ROW
6465	BEGIN
6466	UPDATE %s SET %s = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
6467	END
6468	',
6469	$this->quote_sqlite_identifier( $trigger_name ),
6470	$this->quote_sqlite_identifier( $table ),
6471	$this->quote_sqlite_identifier( $table ),
6472	$this->quote_sqlite_identifier( $column )
6473	);
6474	}
6475
6476	/**
6477	* Unquote a quoted SQLite identifier.
6478	*
6479	* Remove bounding quotes and replace escaped quotes with their values.
6480	*
6481	* @param string $quoted_identifier The quoted identifier value.
6482	* @return string The unquoted identifier value.
6483	*/
6484	private function unquote_sqlite_identifier( string $quoted_identifier ): string {
6485	$first_byte = $quoted_identifier[0] ?? null;
6486	if ( '"' === $first_byte \|\| '`' === $first_byte ) {
6487	$unquoted = substr( $quoted_identifier, 1, -1 );
6488	return str_replace( $first_byte . $first_byte, $first_byte, $unquoted );
6489	}
6490	return $quoted_identifier;
6491	}
6492
6493	/**
6494	* Quote an identifier for use in an SQLite query.
6495	*
6496	* @param string $unquoted_identifier The unquoted identifier value.
6497	* @return string The quoted identifier value.
6498	*/
6499	private function quote_sqlite_identifier( string $unquoted_identifier ): string {
6500	return $this->connection->quote_identifier( $unquoted_identifier );
6501	}
6502
6503	/**
6504	* Quote a value for use in an SQLite query.
6505	*
6506	* @param mixed $value The value to quote.
6507	* @return string The quoted value.
6508	*/
6509	private function quote_sqlite_value( $value ): string {
6510	return $this->connection->quote( $value );
6511	}
6512
6513	/**
6514	* Quote an identifier for use in a MySQL query.
6515	*
6516	* Wrap the identifier in backticks and escape backtick values within.
6517	*
6518	* @param string $unquoted_identifier The unquoted identifier value.
6519	* @return string The quoted identifier value.
6520	*/
6521	private function quote_mysql_identifier( string $unquoted_identifier ): string {
6522	return '`' . str_replace( '`', '``', $unquoted_identifier ) . '`';
6523	}
6524
6525	/**
6526	* Format a MySQL UTF-8 string literal for output in a CREATE TABLE statement.
6527	*
6528	* We expect UTF-8 strings coming from SQLite. The only characters that must
6529	* be escaped in a single-quoted string for a UTF-8 MySQL dump are ' and \.
6530	*
6531	* MySQL SHOW CREATE TABLE command additionally escapes "\0", "\n", and "\r",
6532	* for the mysql CLI, logs, and better readability. This applies to column
6533	* default values, and table, column, and index comments. Other values, such
6534	* as identifiers, don't have these extra characters escaped in the output.
6535	*
6536	* See:
6537	* - https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql/sql_show.cc#L1799
6538	* - https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql/table.cc#L3525
6539	*
6540	* Unfortunately, SQLite doesn't validate the UTF-8 encoding, so other byte
6541	* sequences may come from SQLite as well: https://www.sqlite.org/invalidutf.html
6542	*
6543	* TODO: We may consider stripping invalid UTF-8 characters, but that's likely
6544	* to be a bigger project, as these can appear also in other contexts.
6545	*
6546	* @param string $utf8_literal The UTF-8 string literal to escape.
6547	* @return string The escaped string literal.
6548	*/
6549	private function quote_mysql_utf8_string_literal( string $utf8_literal ): string {
6550	/*
6551	* We can't use "addcslashes()" here, because it has an unusual handling
6552	* of the ASCII NULL character, escaping it to "\000" instead of "\0".
6553	*
6554	* It is important to use "strtr()" and not "str_replace()", because
6555	* "str_replace()" applies replacements one after another, modifying
6556	* intermediate changes rather than just the original string:
6557	*
6558	* - str_replace( [ 'a', 'b' ], [ 'b', 'c' ], 'ab' ); // 'cc' (bad)
6559	* - strtr( 'ab', [ 'a' => 'b', 'b' => 'c' ] ); // 'bc' (good)
6560	*/
6561	$backslash = chr( 92 );
6562	$replacements = array(
6563	"'" => "''", // A single quote character (').
6564	$backslash => $backslash . $backslash, // A backslash character (\).
6565	chr( 0 ) => $backslash . '0', // An ASCII NULL character (\0).
6566	chr( 10 ) => $backslash . 'n', // A newline (linefeed) character (\n).
6567	chr( 13 ) => $backslash . 'r', // A carriage return character (\r).
6568	);
6569	return "'" . strtr( $utf8_literal, $replacements ) . "'";
6570	}
6571
6572	/**
6573	* Clear the state of the driver.
6574	*/
6575	private function flush(): void {
6576	$this->last_mysql_query = '';
6577	$this->last_sqlite_queries = array();
6578	$this->last_result_statement = null;
6579	$this->last_affected_rows = null;
6580	$this->last_column_meta = array();
6581	$this->is_readonly = false;
6582	$this->wrapper_transaction_type = null;
6583	}
6584
6585	/**
6586	* Create a PDO SQLite statement from the specified columns and rows.
6587	*
6588	* Some emulated MySQL queries don't have an SQLite counterpart and their
6589	* result data may be generated without a corresponding SQLite statement.
6590	* In such cases, we can generate a simple SQLite SELECT query that will
6591	* provide us with the PDOStatement API for the given column and row data.
6592	*
6593	* @param array $columns The columns of the result set.
6594	* @param array $rows The rows of the result set.
6595	* @return PDOStatement The corresponding PDO SQLite statement.
6596	*/
6597	private function create_result_statement_from_data( array $columns, array $rows ): PDOStatement {
6598	$pdo = $this->connection->get_pdo();
6599
6600	/*
6601	* With 0 columns, we need to create a PDO statement that has no columns.
6602	* This can be done using a noop INSERT statement that modifies no data.
6603	*/
6604	if ( 0 === count( $columns ) ) {
6605	return $pdo->query(
6606	sprintf(
6607	'INSERT INTO %s (rowid) SELECT NULL WHERE FALSE',
6608	$this->quote_sqlite_identifier( self::GLOBAL_VARIABLES_TABLE_NAME )
6609	)
6610	);
6611	}
6612
6613	/*
6614	* Create an SQLite statement that returns the specified columns and rows.
6615	* This can be done using a SELECT statement in the following form:
6616	*
6617	* -- A dummy header row to assign correct column names.
6618	* SELECT NULL AS `col1`, NULL AS `col2`, ... WHERE FALSE
6619	*
6620	* UNION ALL
6621	*
6622	* -- The actual data rows.
6623	* VALUES
6624	* (val11, val12, ...),
6625	* (val21, val22, ...),
6626	* ...
6627	*/
6628
6629	// Construct column header row ("SELECT <column-list> WHERE FALSE").
6630	$query = 'SELECT ';
6631	foreach ( $columns as $i => $column ) {
6632	$query .= $i > 0 ? ', ' : '';
6633	$query .= 'NULL AS ' . $pdo->quote( $column );
6634	}
6635	$query .= ' WHERE FALSE';
6636
6637	// UNION ALL
6638	if ( count( $rows ) > 0 ) {
6639	$query .= ' UNION ALL VALUES ';
6640	}
6641
6642	// Construct data rows ("VALUES <row-list>").
6643	foreach ( $rows as $i => $row ) {
6644	$query .= $i > 0 ? ', ' : '';
6645	$query .= '(';
6646	foreach ( array_values( $row ) as $j => $value ) {
6647	$query .= $j > 0 ? ', ' : '';
6648	if ( null === $value ) {
6649	$query .= 'NULL';
6650	} elseif ( is_string( $value ) && strpos( $value, "\0" ) !== false ) {
6651	// Handle null characters; see self::translate_string_literal().
6652	$query .= sprintf( "CAST(x'%s' AS TEXT)", bin2hex( $value ) );
6653	} elseif ( is_string( $value ) ) {
6654	$query .= $pdo->quote( $value );
6655	} else {
6656	$query .= $value;
6657	}
6658	}
6659	$query .= ')';
6660	}
6661	return $pdo->query( $query );
6662	}
6663
6664	/**
6665	* Create a new SQLite driver exception.
6666	*
6667	* @param string $message The exception message.
6668	* @param int\|string $code The exception code. For PDO errors, a string representing SQLSTATE.
6669	* @param Throwable\|null $previous The previous exception.
6670	* @return WP_SQLite_Driver_Exception
6671	*/
6672	private function new_driver_exception(
6673	string $message,
6674	$code = 0,
6675	?Throwable $previous = null
6676	): WP_SQLite_Driver_Exception {
6677	return new WP_SQLite_Driver_Exception( $this, $message, $code, $previous );
6678	}
6679
6680	/**
6681	* Create a new invalid input exception.
6682	*
6683	* This exception can be used to mark cases that should never occur according
6684	* to the MySQL grammar. It may serve as an assertion that should never fail.
6685	*
6686	* @return WP_SQLite_Driver_Exception
6687	*/
6688	private function new_invalid_input_exception(): WP_SQLite_Driver_Exception {
6689	return new WP_SQLite_Driver_Exception( $this, 'MySQL query syntax error.' );
6690	}
6691
6692	/**
6693	* Create a new not supported exception.
6694	*
6695	* This exception can be used to mark MySQL constructs that are not supported.
6696	*
6697	* @param string $cause The cause, indicating which construct is not supported.
6698	* @return WP_SQLite_Driver_Exception
6699	*/
6700	private function new_not_supported_exception( string $cause ): WP_SQLite_Driver_Exception {
6701	return new WP_SQLite_Driver_Exception(
6702	$this,
6703	sprintf( 'MySQL query not supported. Cause: %s', $cause )
6704	);
6705	}
6706
6707	/**
6708	* Create a new access denied exception for the information schema database.
6709	*
6710	* @return WP_SQLite_Driver_Exception
6711	*/
6712	private function new_access_denied_to_information_schema_exception(): WP_SQLite_Driver_Exception {
6713	return $this->new_driver_exception(
6714	"Access denied for user 'root'@'%' to database 'information_schema'",
6715	'42000'
6716	);
6717	}
6718
6719	/**
6720	* Convert an information schema exception to a MySQL-like driver exception.
6721	*
6722	* This method is used to convert some information schema exceptions to the
6723	* corresponding MySQL exceptions, as they would be generated by PDO MySQL.
6724	* This conversion mirrors PDO's error messages and SQLSTATE codes.
6725	*
6726	* @param WP_SQLite_Information_Schema_Exception $e The information schema exception.
6727	* @return Throwable The converted exception, or the original
6728	* exception if no conversion was done.
6729	*/
6730	private function convert_information_schema_exception( WP_SQLite_Information_Schema_Exception $e ): Throwable {
6731	switch ( $e->get_type() ) {
6732	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_TABLE_NAME:
6733	return $this->new_driver_exception(
6734	sprintf(
6735	"SQLSTATE[42S01]: Base table or view already exists: 1050 Table '%s' already exists",
6736	$e->get_data()['table_name']
6737	),
6738	'42S01'
6739	);
6740	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_COLUMN_NAME:
6741	return $this->new_driver_exception(
6742	sprintf(
6743	"SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name '%s'",
6744	$e->get_data()['column_name']
6745	),
6746	'42S21'
6747	);
6748	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_KEY_NAME:
6749	return $this->new_driver_exception(
6750	sprintf(
6751	"SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name '%s'",
6752	$e->get_data()['key_name']
6753	),
6754	'42S21'
6755	);
6756	case WP_SQLite_Information_Schema_Exception::TYPE_KEY_COLUMN_NOT_FOUND:
6757	return $this->new_driver_exception(
6758	sprintf(
6759	"SQLSTATE[42000]: Syntax error or access violation: 1072 Key column '%s' doesn't exist in table",
6760	$e->get_data()['column_name']
6761	),
6762	'42000'
6763	);
6764	case WP_SQLite_Information_Schema_Exception::TYPE_CONSTRAINT_DOES_NOT_EXIST:
6765	return $this->new_driver_exception(
6766	sprintf(
6767	"SQLSTATE[HY000]: General error: 3940 Constraint '%s' does not exist.",
6768	$e->get_data()['name']
6769	),
6770	'HY000'
6771	);
6772	case WP_SQLite_Information_Schema_Exception::TYPE_MULTIPLE_CONSTRAINTS_WITH_NAME:
6773	return $this->new_driver_exception(
6774	sprintf(
6775	"SQLSTATE[HY000]: General error: 3939 Table has multiple constraints with the name '%s'. Please use constraint specific 'DROP' clause.",
6776	$e->get_data()['name']
6777	),
6778	'HY000'
6779	);
6780	default:
6781	return $e;
6782	}
6783	}
6784	}
6785