sqlite-database-integration / wp-includes / sqlite-ast / class-wp-sqlite-driver.php

class-wp-sqlite-driver.php in SQLite Database Integration 2.2.15, at wp-includes/sqlite-ast/class-wp-sqlite-driver.php

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