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

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

3,900 lines 130.4 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	* The SQLite engine version.
299	*
300	* This is a mysqli-like property that is needed to avoid a PHP warning in
301	* the WordPress health info. The "WP_Debug_Data::get_wp_database()" method
302	* calls "$wpdb->dbh->client_info" - a mysqli-specific abstraction leak.
303	*
304	* @TODO: This should be fixed in WordPress core.
305	*
306	* See:
307	* https://github.com/WordPress/wordpress-develop/blob/bcdca3f9925f1d3eca7b78d231837c0caf0c8c24/src/wp-admin/includes/class-wp-debug-data.php#L1579
308	*
309	* @var string
310	*/
311	public $client_info;
312
313	/**
314	* A MySQL query parser grammar.
315	*
316	* @var WP_Parser_Grammar
317	*/
318	private static $mysql_grammar;
319
320	/**
321	* The main database name.
322	*
323	* The name of the main database that is used by the driver.
324	*
325	* @var string\|null
326	*/
327	private $main_db_name;
328
329	/**
330	* The name of the current database in use.
331	*
332	* This can be set with the USE statement. At the moment, we support only
333	* the main driver database and the INFORMATION_SCHEMA database.
334	*
335	* @var string
336	*/
337	private $db_name;
338
339	/**
340	* An instance of the SQLite connection.
341	*
342	* @var WP_SQLite_Connection
343	*/
344	private $connection;
345
346	/**
347	* A service for managing MySQL INFORMATION_SCHEMA tables in SQLite.
348	*
349	* @var WP_SQLite_Information_Schema_Builder
350	*/
351	private $information_schema_builder;
352
353	/**
354	* Last executed MySQL query.
355	*
356	* @var string
357	*/
358	private $last_mysql_query;
359
360	/**
361	* A list of SQLite queries executed for the last MySQL query.
362	*
363	* @var array{ sql: string, params: array }[]
364	*/
365	private $last_sqlite_queries = array();
366
367	/**
368	* Results of the last emulated query.
369	*
370	* @var array\|null
371	*/
372	private $last_result;
373
374	/**
375	* Return value of the last emulated query.
376	*
377	* @var mixed
378	*/
379	private $last_return_value;
380
381	/**
382	* Number of rows found by the last SQL_CALC_FOUND_ROW query.
383	*
384	* @var int
385	*/
386	private $last_sql_calc_found_rows = null;
387
388	/**
389	* Whether the current MySQL query is read-only.
390	*
391	* @var bool
392	*/
393	private $is_readonly;
394
395	/**
396	* Transaction nesting level of the executed SQLite queries.
397	*
398	* @var int
399	*/
400	private $transaction_level = 0;
401
402	/**
403	* The PDO fetch mode used for the emulated query.
404	*
405	* @var mixed
406	*/
407	private $pdo_fetch_mode;
408
409	/**
410	* The currently active MySQL SQL modes.
411	*
412	* The default value reflects the default SQL modes for MySQL 8.0.
413	*
414	* TODO: This may be represented using a temporary table in the future,
415	* together with GLOBAL SQL mode (a non-temporary table).
416	*
417	* @var string[]
418	*/
419	private $active_sql_modes = array(
420	'ERROR_FOR_DIVISION_BY_ZERO',
421	'NO_ENGINE_SUBSTITUTION',
422	'NO_ZERO_DATE',
423	'NO_ZERO_IN_DATE',
424	'ONLY_FULL_GROUP_BY',
425	'STRICT_TRANS_TABLES',
426	);
427
428	/**
429	* Constructor.
430	*
431	* Set up an SQLite connection and the MySQL-on-SQLite driver.
432	*
433	* @param WP_SQLite_Connection $connection A SQLite database connection.
434	* @param string $database The database name.
435	*
436	* @throws WP_SQLite_Driver_Exception When the driver initialization fails.
437	*/
438	public function __construct( WP_SQLite_Connection $connection, string $database ) {
439	$this->connection = $connection;
440	$this->main_db_name = $database;
441	$this->db_name = $database;
442
443	// Check the SQLite version.
444	$sqlite_version = $this->get_sqlite_version();
445	if ( version_compare( $sqlite_version, self::MINIMUM_SQLITE_VERSION, '<' ) ) {
446	throw $this->new_driver_exception(
447	sprintf(
448	'The SQLite version %s is not supported. Minimum required version is %s.',
449	$sqlite_version,
450	self::MINIMUM_SQLITE_VERSION
451	)
452	);
453	}
454
455	// Load SQLite version to a property used by WordPress health info.
456	$this->client_info = $sqlite_version;
457
458	// Enable foreign keys. By default, they are off.
459	$this->connection->query( 'PRAGMA foreign_keys = ON' );
460
461	// Register SQLite functions.
462	WP_SQLite_PDO_User_Defined_Functions::register_for( $this->connection->get_pdo() );
463
464	// Load MySQL grammar.
465	if ( null === self::$mysql_grammar ) {
466	self::$mysql_grammar = new WP_Parser_Grammar( require self::MYSQL_GRAMMAR_PATH );
467	}
468
469	// Initialize information schema builder.
470	$this->information_schema_builder = new WP_SQLite_Information_Schema_Builder(
471	$this->main_db_name,
472	self::RESERVED_PREFIX,
473	$this->connection
474	);
475
476	// Ensure that the database is configured.
477	$migrator = new WP_SQLite_Configurator( $this, $this->information_schema_builder );
478	$migrator->ensure_database_configured();
479
480	$this->connection->set_query_logger(
481	function ( string $sql, array $params ) {
482	$this->last_sqlite_queries[] = array(
483	'sql' => $sql,
484	'params' => $params,
485	);
486	}
487	);
488	}
489
490	/**
491	* Get the SQLite connection instance.
492	*
493	* @return WP_SQLite_Connection
494	*/
495	public function get_connection(): WP_SQLite_Connection {
496	return $this->connection;
497	}
498
499	/**
500	* Get the version of the SQLite engine.
501	*
502	* @return string SQLite engine version as a string.
503	*/
504	public function get_sqlite_version(): string {
505	return $this->connection->query( 'SELECT SQLITE_VERSION()' )->fetchColumn();
506	}
507
508	/**
509	* Get the SQLite driver version saved in the database.
510	*
511	* The saved driver version corresponds to the latest version of the SQLite
512	* driver that was used to initialize and configure the SQLite database.
513	*
514	* @return string SQLite driver version as a string.
515	* @throws PDOException When the query execution fails.
516	*/
517	public function get_saved_driver_version(): string {
518	$default_version = '0.0.0';
519	try {
520	$stmt = $this->execute_sqlite_query(
521	sprintf(
522	'SELECT value FROM %s WHERE name = ?',
523	$this->quote_sqlite_identifier( self::GLOBAL_VARIABLES_TABLE_NAME )
524	),
525	array( self::DRIVER_VERSION_VARIABLE_NAME )
526	);
527	return $stmt->fetchColumn() ?? $default_version;
528	} catch ( PDOException $e ) {
529	if ( str_contains( $e->getMessage(), 'no such table' ) ) {
530	return $default_version;
531	}
532	throw $e;
533	}
534	}
535
536	/**
537	* Check if a specific SQL mode is active.
538	*
539	* @param string $mode The SQL mode to check.
540	* @return bool True if the SQL mode is active, false otherwise.
541	*/
542	public function is_sql_mode_active( string $mode ): bool {
543	return in_array( strtoupper( $mode ), $this->active_sql_modes, true );
544	}
545
546	/**
547	* Get the last executed MySQL query.
548	*
549	* @return string\|null
550	*/
551	public function get_last_mysql_query(): ?string {
552	return $this->last_mysql_query;
553	}
554
555	/**
556	* Get SQLite queries executed for the last MySQL query.
557	*
558	* @return array{ sql: string, params: array }[]
559	*/
560	public function get_last_sqlite_queries(): array {
561	return $this->last_sqlite_queries;
562	}
563
564	/**
565	* Get the auto-increment value generated for the last query.
566	*
567	* @return int\|string
568	*/
569	public function get_insert_id() {
570	$last_insert_id = $this->connection->get_last_insert_id();
571	if ( is_numeric( $last_insert_id ) ) {
572	$last_insert_id = (int) $last_insert_id;
573	}
574	return $last_insert_id;
575	}
576
577	/**
578	* Translate and execute a MySQL query in SQLite.
579	*
580	* A single MySQL query can be translated into zero or more SQLite queries.
581	*
582	* @param string $query Full SQL statement string.
583	* @param int $fetch_mode PDO fetch mode. Default is PDO::FETCH_OBJ.
584	* @param array ...$fetch_mode_args Additional fetch mode arguments.
585	*
586	* @return mixed Return value, depending on the query type.
587	*
588	* @throws WP_SQLite_Driver_Exception When the query execution fails.
589	*
590	* TODO:
591	* The API of this function is not final.
592	* We should also add support for parametrized queries.
593	* See: https://github.com/Automattic/sqlite-database-integration/issues/7
594	*/
595	public function query( string $query, $fetch_mode = PDO::FETCH_OBJ, ...$fetch_mode_args ) {
596	$this->flush();
597	$this->pdo_fetch_mode = $fetch_mode;
598	$this->last_mysql_query = $query;
599
600	try {
601	// Parse the MySQL query.
602	$parser = $this->create_parser( $query );
603	$parser->next_query();
604	$ast = $parser->get_query_ast();
605	if ( null === $ast ) {
606	throw $this->new_driver_exception( 'Failed to parse the MySQL query.' );
607	}
608
609	if ( $parser->next_query() ) {
610	throw $this->new_driver_exception( 'Multi-query is not supported.' );
611	}
612
613	// Handle transaction commands.
614
615	/*
616	* [GRAMMAR]
617	* beginWork: BEGIN_SYMBOL WORK_SYMBOL?
618	*/
619	$child = $ast->get_first_child();
620	if ( $child instanceof WP_Parser_Node && 'beginWork' === $child->rule_name ) {
621	$this->begin_transaction();
622	return true;
623	}
624
625	if ( $child instanceof WP_Parser_Node && 'simpleStatement' === $child->rule_name ) {
626	/*
627	* [GRAMMAR]
628	* transactionOrLockingStatement:
629	* transactionStatement \| savepointStatement \| lockStatement \| xaStatement
630	*/
631	$subchild = $child->get_first_child_node( 'transactionOrLockingStatement' );
632	if ( null !== $subchild ) {
633	$tokens = $subchild->get_descendant_tokens();
634	$token1 = $tokens[0];
635	$token2 = $tokens[1] ?? null;
636	if (
637	WP_MySQL_Lexer::START_SYMBOL === $token1->id
638	&& WP_MySQL_Lexer::TRANSACTION_SYMBOL === $token2->id
639	) {
640	$this->begin_transaction();
641	return true;
642	}
643
644	if (
645	WP_MySQL_Lexer::BEGIN_SYMBOL === $token1->id
646	) {
647	$this->begin_transaction();
648	return true;
649	}
650
651	if (
652	WP_MySQL_Lexer::COMMIT_SYMBOL === $token1->id
653	) {
654	$this->commit();
655	return true;
656	}
657
658	if (
659	WP_MySQL_Lexer::ROLLBACK_SYMBOL === $token1->id
660	) {
661	$this->rollback();
662	return true;
663	}
664	}
665	}
666
667	// Perform all the queries in a nested transaction.
668	$this->begin_transaction();
669	$this->execute_mysql_query( $ast );
670	$this->commit();
671	return $this->last_return_value;
672	} catch ( Throwable $e ) {
673	try {
674	$this->rollback();
675	} catch ( Throwable $rollback_exception ) {
676	// Ignore rollback errors.
677	}
678	if ( $e instanceof WP_SQLite_Driver_Exception ) {
679	throw $e;
680	} elseif ( $e instanceof WP_SQLite_Information_Schema_Exception ) {
681	throw $this->convert_information_schema_exception( $e );
682	}
683	throw $this->new_driver_exception( $e->getMessage(), $e->getCode(), $e );
684	}
685	}
686
687	/**
688	* Tokenize a MySQL query and initialize a parser.
689	*
690	* @param string $query The MySQL query to parse.
691	* @return WP_MySQL_Parser A parser initialized for the MySQL query.
692	*/
693	public function create_parser( string $query ): WP_MySQL_Parser {
694	$lexer = new WP_MySQL_Lexer(
695	$query,
696	80038,
697	$this->active_sql_modes
698	);
699	$tokens = $lexer->remaining_tokens();
700	return new WP_MySQL_Parser( self::$mysql_grammar, $tokens );
701	}
702
703	/**
704	* Get results of the last query.
705	*
706	* @return mixed
707	*/
708	public function get_query_results() {
709	return $this->last_result;
710	}
711
712	/**
713	* Get return value of the last query() function call.
714	*
715	* @return mixed
716	*/
717	public function get_last_return_value() {
718	return $this->last_return_value;
719	}
720
721	/**
722	* Execute a query in SQLite.
723	*
724	* @param string $sql The query to execute.
725	* @param array $params The query parameters.
726	* @throws PDOException When the query execution fails.
727	* @return PDOStatement The PDO statement object.
728	*/
729	public function execute_sqlite_query( string $sql, array $params = array() ): PDOStatement {
730	return $this->connection->query( $sql, $params );
731	}
732
733	/**
734	* Begin a new transaction or nested transaction.
735	*/
736	public function begin_transaction(): void {
737	if ( 0 === $this->transaction_level ) {
738	/*
739	* When we're executing a statement that will write to the database,
740	* we need to use "BEGIN IMMEDIATE" to open a write transaction.
741	*
742	* This is needed to avoid the "database is locked" error (SQLITE_BUSY)
743	* when SQLite can't upgrade a read transaction to a write transaction,
744	* because another connection is modifying the database.
745	*
746	* From the SQLite documentation:
747	*
748	* ## Read transactions versus write transactions
749	*
750	* If a write statement occurs while a read transaction is active,
751	* then the read transaction is upgraded to a write transaction if
752	* possible. If some other database connection has already modified
753	* the database or is already in the process of modifying the database,
754	* then upgrading to a write transaction is not possible and the write
755	* statement will fail with SQLITE_BUSY.
756	*
757	* ## DEFERRED, IMMEDIATE, and EXCLUSIVE transactions
758	*
759	* Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. The default
760	* transaction behavior is DEFERRED.
761	*
762	* DEFERRED means that the transaction does not actually start until
763	* the database is first accessed.
764	*
765	* IMMEDIATE causes the database connection to start a new write
766	* immediately, without waiting for a write statement. The BEGIN
767	* IMMEDIATE might fail with SQLITE_BUSY if another write transaction
768	* is already active on another database connection.
769	*
770	* See:
771	* - https://www.sqlite.org/lang_transaction.html
772	* - https://www.sqlite.org/rescode.html#busy
773	*
774	* For better performance, we could also consider opening the write
775	* transaction later in the session - just before the first write.
776	*/
777	$this->execute_sqlite_query( $this->is_readonly ? 'BEGIN' : 'BEGIN IMMEDIATE' );
778	} else {
779	$this->execute_sqlite_query( 'SAVEPOINT LEVEL' . $this->transaction_level );
780	}
781	++$this->transaction_level;
782	}
783
784	/**
785	* Commit the current transaction or nested transaction.
786	*/
787	public function commit(): void {
788	if ( 0 === $this->transaction_level ) {
789	return;
790	}
791
792	--$this->transaction_level;
793	if ( 0 === $this->transaction_level ) {
794	$this->execute_sqlite_query( 'COMMIT' );
795	} else {
796	$this->execute_sqlite_query( 'RELEASE SAVEPOINT LEVEL' . $this->transaction_level );
797	}
798	}
799
800	/**
801	* Rollback the current transaction or nested transaction.
802	*/
803	public function rollback(): void {
804	if ( 0 === $this->transaction_level ) {
805	return;
806	}
807
808	--$this->transaction_level;
809	if ( 0 === $this->transaction_level ) {
810	$this->execute_sqlite_query( 'ROLLBACK' );
811	} else {
812	$this->execute_sqlite_query( 'ROLLBACK TO SAVEPOINT LEVEL' . $this->transaction_level );
813	}
814	}
815
816	/**
817	* Translate and execute a MySQL query in SQLite.
818	*
819	* @param WP_Parser_Node $node The "query" AST node with "simpleStatement" child.
820	* @throws WP_SQLite_Driver_Exception When the query is not supported.
821	*/
822	private function execute_mysql_query( WP_Parser_Node $node ): void {
823	if ( 'query' !== $node->rule_name ) {
824	throw $this->new_driver_exception(
825	sprintf( 'Expected "query" node, got: "%s"', $node->rule_name )
826	);
827	}
828
829	/*
830	* [GRAMMAR]
831	* query:
832	* EOF
833	* \| (simpleStatement \| beginWork) (SEMICOLON_SYMBOL EOF? \| EOF)
834	*/
835	$children = $node->get_child_nodes();
836	if ( count( $children ) !== 1 ) {
837	throw $this->new_driver_exception(
838	sprintf( 'Expected 1 child node, got: %d', count( $children ) )
839	);
840	}
841
842	if ( 'simpleStatement' !== $children[0]->rule_name ) {
843	throw $this->new_driver_exception(
844	sprintf( 'Expected "simpleStatement" node, got: "%s"', $children[0]->rule_name )
845	);
846	}
847
848	// Process the "simpleStatement" AST node.
849	$node = $children[0]->get_first_child_node();
850	switch ( $node->rule_name ) {
851	case 'selectStatement':
852	$this->is_readonly = true;
853	$this->execute_select_statement( $node );
854	break;
855	case 'insertStatement':
856	case 'replaceStatement':
857	$this->execute_insert_or_replace_statement( $node );
858	break;
859	case 'updateStatement':
860	$this->execute_update_statement( $node );
861	break;
862	case 'deleteStatement':
863	$this->execute_delete_statement( $node );
864	break;
865	case 'createStatement':
866	$subtree = $node->get_first_child_node();
867	switch ( $subtree->rule_name ) {
868	case 'createDatabase':
869	/*
870	* TODO:
871	* We could support this by creating a new SQLite database
872	* file (e.g., $slugified_db_name.sqlite).
873	*
874	* Alternatively, it could be a no-op, in combination with
875	* DROP DATABASE deleting the data file and recreating it.
876	*/
877	case 'createTable':
878	$this->execute_create_table_statement( $node );
879	break;
880	case 'createIndex':
881	// TODO: SQLite has a CREATE INDEX statement. We should support it.
882	default:
883	throw $this->new_not_supported_exception(
884	sprintf(
885	'statement type: "%s" > "%s"',
886	$node->rule_name,
887	$subtree->rule_name
888	)
889	);
890	}
891	break;
892	case 'alterStatement':
893	$subtree = $node->get_first_child_node();
894	switch ( $subtree->rule_name ) {
895	case 'alterTable':
896	$this->execute_alter_table_statement( $node );
897	break;
898	default:
899	throw $this->new_not_supported_exception(
900	sprintf(
901	'statement type: "%s" > "%s"',
902	$node->rule_name,
903	$subtree->rule_name
904	)
905	);
906	}
907	break;
908	case 'dropStatement':
909	$subtree = $node->get_first_child_node();
910	switch ( $subtree->rule_name ) {
911	case 'dropTable':
912	$this->execute_drop_table_statement( $node );
913	break;
914	default:
915	$query = $this->translate( $node );
916	$this->execute_sqlite_query( $query );
917	$this->set_result_from_affected_rows();
918	}
919	break;
920	case 'truncateTableStatement':
921	$this->execute_truncate_table_statement( $node );
922	break;
923	case 'setStatement':
924	$this->execute_set_statement( $node );
925	break;
926	case 'showStatement':
927	$this->is_readonly = true;
928	$this->execute_show_statement( $node );
929	break;
930	case 'utilityStatement':
931	$subtree = $node->get_first_child_node();
932	switch ( $subtree->rule_name ) {
933	case 'describeStatement':
934	$this->is_readonly = true;
935	$this->execute_describe_statement( $subtree );
936	break;
937	case 'useCommand':
938	$this->execute_use_statement( $subtree );
939	break;
940	default:
941	throw $this->new_not_supported_exception(
942	sprintf(
943	'statement type: "%s" > "%s"',
944	$node->rule_name,
945	$subtree->rule_name
946	)
947	);
948	}
949	break;
950	case 'tableAdministrationStatement':
951	$this->execute_administration_statement( $node );
952	break;
953	default:
954	throw $this->new_not_supported_exception(
955	sprintf( 'statement type: "%s"', $node->rule_name )
956	);
957	}
958	}
959
960	/**
961	* Translate and execute a MySQL SELECT statement in SQLite.
962	*
963	* @param WP_Parser_Node $node The "selectStatement" AST node.
964	* @throws WP_SQLite_Driver_Exception When the query execution fails.
965	*/
966	private function execute_select_statement( WP_Parser_Node $node ): void {
967	/*
968	* [GRAMMAR]
969	* selectStatement:
970	* queryExpression lockingClauseList?
971	* \| selectStatementWithInto
972	*/
973
974	// First, translate the query, before we modify last found rows count.
975	$query = $this->translate( $node->get_first_child() );
976
977	$has_sql_calc_found_rows = null !== $node->get_first_descendant_token(
978	WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL
979	);
980
981	// Handle SQL_CALC_FOUND_ROWS.
982	if ( true === $has_sql_calc_found_rows ) {
983	// Recursively find a query expression with the first LIMIT or SELECT.
984	$query_expr = $node->get_first_descendant_node( 'queryExpression' );
985	while ( true ) {
986	if ( $query_expr->has_child_node( 'limitClause' ) ) {
987	break;
988	}
989
990	$query_expr_parens = $query_expr->get_first_child_node( 'queryExpressionParens' );
991	if ( null !== $query_expr_parens ) {
992	$query_expr = $query_expr_parens->get_first_child_node( 'queryExpression' );
993	continue;
994	}
995
996	$query_expr_body = $query_expr->get_first_child_node( 'queryExpressionBody' );
997	if ( count( $query_expr_body->get_children() ) > 1 ) {
998	break;
999	}
1000
1001	$query_term = $query_expr_body->get_first_child_node( 'queryTerm' );
1002	if (
1003	count( $query_term->get_children() ) === 1
1004	&& $query_term->has_child_node( 'queryExpressionParens' )
1005	) {
1006	$query_expr = $query_term->get_first_child_node( 'queryExpressionParens' )->get_first_child_node( 'queryExpression' );
1007	continue;
1008	}
1009
1010	break;
1011	}
1012
1013	// Exclude the limit clause from the expression.
1014	$count_expr = new WP_Parser_Node( $query_expr->rule_id, $query_expr->rule_name );
1015	foreach ( $query_expr->get_children() as $child ) {
1016	if ( ! ( $child instanceof WP_Parser_Node && 'limitClause' === $child->rule_name ) ) {
1017	$count_expr->append_child( $child );
1018	}
1019	}
1020
1021	// Get count of all the rows.
1022	$result = $this->execute_sqlite_query(
1023	'SELECT COUNT(*) AS cnt FROM (' . $this->translate( $count_expr ) . ')'
1024	);
1025
1026	$this->last_sql_calc_found_rows = $result->fetchColumn();
1027	} else {
1028	$this->last_sql_calc_found_rows = null;
1029	}
1030
1031	// Execute the query.
1032	$stmt = $this->execute_sqlite_query( $query );
1033	$this->set_results_from_fetched_data(
1034	$stmt->fetchAll( $this->pdo_fetch_mode )
1035	);
1036	}
1037
1038	/**
1039	* Translate and execute a MySQL INSERT or REPLACE statement in SQLite.
1040	*
1041	* @param WP_Parser_Node $node The "insertStatement" or "replaceStatement" AST node.
1042	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1043	*/
1044	private function execute_insert_or_replace_statement( WP_Parser_Node $node ): void {
1045	// Check if strict mode is disabled.
1046	$is_non_strict_mode = (
1047	! $this->is_sql_mode_active( 'STRICT_TRANS_TABLES' )
1048	&& ! $this->is_sql_mode_active( 'STRICT_ALL_TABLES' )
1049	);
1050
1051	$parts = array();
1052	foreach ( $node->get_children() as $child ) {
1053	if ( $child instanceof WP_MySQL_Token && WP_MySQL_Lexer::IGNORE_SYMBOL === $child->id ) {
1054	// Translate "UPDATE IGNORE" to "UPDATE OR IGNORE".
1055	$parts[] = 'OR IGNORE';
1056	} elseif (
1057	$is_non_strict_mode
1058	&& $child instanceof WP_Parser_Node
1059	&& ( 'insertFromConstructor' === $child->rule_name \|\| 'insertQueryExpression' === $child->rule_name )
1060	) {
1061	$table_ref = $node->get_first_child_node( 'tableRef' );
1062	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
1063	$parts[] = $this->translate_insert_or_replace_body_in_non_strict_mode( $table_name, $child );
1064	} else {
1065	$parts[] = $this->translate( $child );
1066	}
1067	}
1068	$query = implode( ' ', $parts );
1069	$this->execute_sqlite_query( $query );
1070	$this->set_result_from_affected_rows();
1071	}
1072
1073	/**
1074	* Translate and execute a MySQL UPDATE statement in SQLite.
1075	*
1076	* @param WP_Parser_Node $node The "updateStatement" AST node.
1077	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1078	*/
1079	private function execute_update_statement( WP_Parser_Node $node ): void {
1080	// @TODO: Add support for UPDATE with multiple tables and JOINs.
1081	// SQLite supports them in the FROM clause.
1082
1083	$has_order = $node->has_child_node( 'orderClause' );
1084	$has_limit = $node->has_child_node( 'simpleLimitClause' );
1085
1086	/*
1087	* SQLite doesn't support UPDATE with ORDER BY/LIMIT.
1088	* We need to use a subquery to emulate this behavior.
1089	*
1090	* For instance, the following query:
1091	* UPDATE t SET c = 1 WHERE c = 2 LIMIT 1;
1092	* Will be rewritten to:
1093	* UPDATE t SET c = 1 WHERE rowid IN ( SELECT rowid FROM t WHERE c = 2 LIMIT 1 );
1094	*/
1095	$where_subquery = null;
1096	if ( $has_order \|\| $has_limit ) {
1097	$where_subquery = 'SELECT rowid FROM ' . $this->translate_sequence(
1098	array(
1099	$node->get_first_child_node( 'tableReferenceList' ),
1100	$node->get_first_child_node( 'whereClause' ),
1101	$node->get_first_child_node( 'orderClause' ),
1102	$node->get_first_child_node( 'simpleLimitClause' ),
1103	)
1104	);
1105	}
1106
1107	// Check if strict mode is disabled.
1108	$is_non_strict_mode = (
1109	! $this->is_sql_mode_active( 'STRICT_TRANS_TABLES' )
1110	&& ! $this->is_sql_mode_active( 'STRICT_ALL_TABLES' )
1111	);
1112
1113	// Iterate and translate the update statement children.
1114	$parts = array();
1115	foreach ( $node->get_children() as $child ) {
1116	if ( $child instanceof WP_MySQL_Token && WP_MySQL_Lexer::IGNORE_SYMBOL === $child->id ) {
1117	// Translate "UPDATE IGNORE" to "UPDATE OR IGNORE".
1118	$parts[] = 'OR IGNORE';
1119	} elseif (
1120	$is_non_strict_mode
1121	&& $child instanceof WP_Parser_Node
1122	&& 'updateList' === $child->rule_name
1123	) {
1124	$table_ref = $node->get_first_child_node( 'tableReferenceList' )->get_first_child_node( 'tableReference' );
1125	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
1126	$parts[] = $this->translate_update_list_in_non_strict_mode( $table_name, $child );
1127	} else {
1128	$parts[] = $this->translate( $child );
1129	}
1130
1131	// When using a subquery, skip WHERE, ORDER BY, and LIMIT.
1132	if (
1133	null !== $where_subquery
1134	&& $child instanceof WP_Parser_Node
1135	&& 'updateList' === $child->rule_name
1136	) {
1137	// We can stop here, as the update statement grammar is:
1138	// ... updateList whereClause? orderClause? simpleLimitClause?
1139	break;
1140	}
1141	}
1142
1143	// Compose the update query.
1144	$query = implode( ' ', $parts );
1145	if ( null !== $where_subquery ) {
1146	$query .= ' WHERE rowid IN ( ' . $where_subquery . ' )';
1147	}
1148
1149	$this->execute_sqlite_query( $query );
1150	$this->set_result_from_affected_rows();
1151	}
1152
1153	/**
1154	* Translate and execute a MySQL DELETE statement in SQLite.
1155	*
1156	* @param WP_Parser_Node $node The "deleteStatement" AST node.
1157	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1158	*/
1159	private function execute_delete_statement( WP_Parser_Node $node ): void {
1160	/*
1161	* Multi-table DELETE.
1162	*
1163	* MySQL supports multi-table DELETE statements that don't work in SQLite.
1164	* These statements can have the following two flavours:
1165	* 1. "DELETE t1, t2 FROM ... JOIN ... WHERE ..."
1166	* 2. "DELETE FROM t1, t2 USING ... JOIN ... WHERE ..."
1167	*
1168	* We will rewrite such statements into a SELECT to fetch the ROWIDs of
1169	* the rows to delete and then execute a DELETE statement for each table.
1170	*/
1171	$alias_ref_list = $node->get_first_child_node( 'tableAliasRefList' );
1172	if ( null !== $alias_ref_list ) {
1173	// 1. Get table aliases targeted by the DELETE statement.
1174	$table_aliases = array();
1175	foreach ( $alias_ref_list->get_child_nodes() as $alias_ref ) {
1176	$table_aliases[] = $this->unquote_sqlite_identifier(
1177	$this->translate( $alias_ref )
1178	);
1179	}
1180
1181	// 2. Create an alias to table name map.
1182	$alias_map = array();
1183	$table_ref_list = $node->get_first_child_node( 'tableReferenceList' );
1184	foreach ( $table_ref_list->get_descendant_nodes( 'singleTable' ) as $single_table ) {
1185	$alias = $this->unquote_sqlite_identifier(
1186	$this->translate( $single_table->get_first_child_node( 'tableAlias' ) )
1187	);
1188	$ref = $this->unquote_sqlite_identifier(
1189	$this->translate( $single_table->get_first_child_node( 'tableRef' ) )
1190	);
1191
1192	$alias_map[ $alias ] = $ref;
1193	}
1194
1195	// 3. Compose the SELECT query to fetch ROWIDs to delete.
1196	$where_clause = $node->get_first_child_node( 'whereClause' );
1197	if ( null !== $where_clause ) {
1198	$where = $this->translate( $where_clause->get_first_child_node( 'expr' ) );
1199	}
1200
1201	$select_list = array();
1202	foreach ( $table_aliases as $table ) {
1203	$select_list[] = sprintf(
1204	'%s.rowid AS %s',
1205	$this->quote_sqlite_identifier( $table ),
1206	$this->quote_sqlite_identifier( $table . '_rowid' )
1207	);
1208	}
1209
1210	$ids = $this->execute_sqlite_query(
1211	sprintf(
1212	'SELECT %s FROM %s %s',
1213	implode( ', ', $select_list ),
1214	$this->translate( $table_ref_list ),
1215	isset( $where ) ? "WHERE $where" : ''
1216	)
1217	)->fetchAll( PDO::FETCH_ASSOC );
1218
1219	// 4. Execute DELETE statements for each table.
1220	$rows = 0;
1221	if ( count( $ids ) > 0 ) {
1222	foreach ( $table_aliases as $table ) {
1223	$this->execute_sqlite_query(
1224	sprintf(
1225	'DELETE FROM %s AS %s WHERE rowid IN ( %s )',
1226	$this->quote_sqlite_identifier( $alias_map[ $table ] ),
1227	$this->quote_sqlite_identifier( $table ),
1228	implode( ', ', array_column( $ids, "{$table}_rowid" ) )
1229	)
1230	);
1231	$this->set_result_from_affected_rows();
1232	$rows += $this->last_result;
1233	}
1234	}
1235
1236	$this->set_result_from_affected_rows( $rows );
1237	return;
1238	}
1239
1240	// @TODO: Translate DELETE with JOIN to use a subquery.
1241
1242	$query = $this->translate( $node );
1243	$this->execute_sqlite_query( $query );
1244	$this->set_result_from_affected_rows();
1245	}
1246
1247	/**
1248	* Translate and execute a MySQL CREATE TABLE statement in SQLite.
1249	*
1250	* @param WP_Parser_Node $node The "createStatement" AST node with "createTable" child.
1251	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1252	*/
1253	private function execute_create_table_statement( WP_Parser_Node $node ): void {
1254	$subnode = $node->get_first_child_node();
1255
1256	// Handle TEMPORARY keyword.
1257	$table_is_temporary = $subnode->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
1258
1259	// Handle CREATE TABLE ... [AS] SELECT.
1260	$element_list = $subnode->get_first_child_node( 'tableElementList' );
1261	if ( null === $element_list ) {
1262	/*
1263	* While SQLite supports CREATE TABLE ... AS SELECT statements,
1264	* we need to somehow implement information schema support for
1265	* the tables created in this way.
1266	*
1267	* TODO: Implement information schema support for CREATE TABLE ... AS SELECT.
1268	*/
1269	throw $this->new_not_supported_exception(
1270	'CREATE TABLE ... [AS] SELECT is currently not supported'
1271	);
1272	}
1273
1274	// Get table name.
1275	$table_name = $this->unquote_sqlite_identifier(
1276	$this->translate( $subnode->get_first_child_node( 'tableName' ) )
1277	);
1278
1279	// Handle IF NOT EXISTS.
1280	if ( $subnode->has_child_node( 'ifNotExists' ) ) {
1281	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
1282	$table_exists = $this->execute_sqlite_query(
1283	sprintf(
1284	'SELECT 1 FROM %s WHERE table_schema = ? AND table_name = ?',
1285	$this->quote_sqlite_identifier( $tables_table )
1286	),
1287	array( $this->db_name, $table_name )
1288	)->fetchColumn();
1289
1290	if ( $table_exists ) {
1291	$this->set_result_from_affected_rows( 0 );
1292	return;
1293	}
1294	}
1295
1296	// Save information to information schema tables.
1297	$this->information_schema_builder->record_create_table( $node );
1298
1299	// Generate CREATE TABLE statement from the information schema tables.
1300	$queries = $this->get_sqlite_create_table_statement( $table_is_temporary, $table_name );
1301	$create_table_query = $queries[0];
1302	$constraint_queries = array_slice( $queries, 1 );
1303
1304	$this->execute_sqlite_query( $create_table_query );
1305
1306	foreach ( $constraint_queries as $query ) {
1307	$this->execute_sqlite_query( $query );
1308	}
1309	}
1310
1311	/**
1312	* Translate and execute a MySQL ALTER TABLE statement in SQLite.
1313	*
1314	* @param WP_Parser_Node $node The "alterStatement" AST node with "alterTable" child.
1315	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1316	*/
1317	private function execute_alter_table_statement( WP_Parser_Node $node ): void {
1318	$table_name = $this->unquote_sqlite_identifier(
1319	$this->translate( $node->get_first_descendant_node( 'tableRef' ) )
1320	);
1321
1322	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
1323
1324	// Save all column names from the original table.
1325	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
1326	$column_names = $this->execute_sqlite_query(
1327	sprintf(
1328	'SELECT
1329	COLUMN_NAME,
1330	LOWER(COLUMN_NAME) AS COLUMN_NAME_LOWERCASE
1331	FROM %s WHERE table_schema = ? AND table_name = ?',
1332	$this->quote_sqlite_identifier( $columns_table )
1333	),
1334	array( $this->db_name, $table_name )
1335	)->fetchAll( PDO::FETCH_ASSOC );
1336
1337	// Track column renames and removals.
1338	$column_map = array_combine(
1339	array_column( $column_names, 'COLUMN_NAME_LOWERCASE' ),
1340	array_column( $column_names, 'COLUMN_NAME' )
1341	);
1342	foreach ( $node->get_descendant_nodes( 'alterListItem' ) as $action ) {
1343	$first_token = $action->get_first_child_token();
1344
1345	switch ( $first_token->id ) {
1346	case WP_MySQL_Lexer::DROP_SYMBOL:
1347	$name = $this->translate( $action->get_first_child_node( 'fieldIdentifier' ) );
1348	if ( null !== $name ) {
1349	$name = $this->unquote_sqlite_identifier( $name );
1350	unset( $column_map[ strtolower( $name ) ] );
1351	}
1352	break;
1353	case WP_MySQL_Lexer::CHANGE_SYMBOL:
1354	$old_name = $this->unquote_sqlite_identifier(
1355	$this->translate( $action->get_first_child_node( 'fieldIdentifier' ) )
1356	);
1357	$new_name = $this->unquote_sqlite_identifier(
1358	$this->translate( $action->get_first_child_node( 'identifier' ) )
1359	);
1360
1361	$column_map[ strtolower( $old_name ) ] = $new_name;
1362	break;
1363	case WP_MySQL_Lexer::RENAME_SYMBOL:
1364	$column_ref = $action->get_first_child_node( 'fieldIdentifier' );
1365	if ( null !== $column_ref ) {
1366	$old_name = $this->unquote_sqlite_identifier(
1367	$this->translate( $column_ref )
1368	);
1369	$new_name = $this->unquote_sqlite_identifier(
1370	$this->translate( $action->get_first_child_node( 'identifier' ) )
1371	);
1372
1373	$column_map[ strtolower( $old_name ) ] = $new_name;
1374	}
1375	break;
1376	}
1377	}
1378
1379	$this->information_schema_builder->record_alter_table( $node );
1380	$this->recreate_table_from_information_schema( $table_is_temporary, $table_name, $column_map );
1381
1382	// @TODO: Consider using a "fast path" for ALTER TABLE statements that
1383	// consist only of operations that SQLite's ALTER TABLE supports.
1384	}
1385
1386	/**
1387	* Translate and execute a MySQL DROP TABLE statement in SQLite.
1388	*
1389	* @param WP_Parser_Node $node The "dropStatement" AST node with "dropTable" child.
1390	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1391	*/
1392	private function execute_drop_table_statement( WP_Parser_Node $node ): void {
1393	// Record the changes in the information schema.
1394	$this->information_schema_builder->record_drop_table( $node );
1395
1396	// MySQL supports removing multiple tables in a single query DROP query.
1397	// In SQLite, we need to execute each DROP TABLE statement separately.
1398	$child_node = $node->get_first_child_node();
1399	$table_refs = $child_node->get_first_child_node( 'tableRefList' )->get_child_nodes();
1400	$table_is_temporary = $child_node->has_child_token( WP_MySQL_Lexer::TEMPORARY_SYMBOL );
1401	$queries = array();
1402	foreach ( $table_refs as $table_ref ) {
1403	$parts = array();
1404	foreach ( $child_node->get_children() as $child ) {
1405	$is_token = $child instanceof WP_MySQL_Token;
1406
1407	// Skip the TEMPORARY keyword.
1408	if ( $is_token && WP_MySQL_Lexer::TEMPORARY_SYMBOL === $child->id ) {
1409	continue;
1410	}
1411
1412	// Replace table list with the current table reference.
1413	if ( ! $is_token && 'tableRefList' === $child->rule_name ) {
1414	// Add a "temp." schema prefix for temporary tables.
1415	$prefix = $table_is_temporary ? '`temp`.' : '';
1416	$part = $prefix . $this->translate( $table_ref );
1417	} else {
1418	$part = $this->translate( $child );
1419	}
1420
1421	if ( null !== $part ) {
1422	$parts[] = $part;
1423	}
1424	}
1425	$queries[] = 'DROP ' . implode( ' ', $parts );
1426	}
1427
1428	foreach ( $queries as $query ) {
1429	$this->execute_sqlite_query( $query );
1430	}
1431	}
1432
1433	/**
1434	* Translate and execute a MySQL TRUNCATE TABLE statement in SQLite.
1435	*
1436	* @param WP_Parser_Node $node The "truncateTableStatement" AST node.
1437	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1438	*/
1439	private function execute_truncate_table_statement( WP_Parser_Node $node ): void {
1440	$table_name = $this->unquote_sqlite_identifier(
1441	$this->translate( $node->get_first_child_node( 'tableRef' ) )
1442	);
1443
1444	$this->execute_sqlite_query(
1445	sprintf( 'DELETE FROM %s', $this->quote_sqlite_identifier( $table_name ) )
1446	);
1447	try {
1448	$this->execute_sqlite_query( 'DELETE FROM sqlite_sequence WHERE name = ?', array( $table_name ) );
1449	} catch ( PDOException $e ) {
1450	if ( str_contains( $e->getMessage(), 'no such table' ) ) {
1451	// The table might not exist if no sequences are used in the DB.
1452	} else {
1453	throw $e;
1454	}
1455	}
1456	$this->set_result_from_affected_rows();
1457	}
1458
1459	/**
1460	* Translate and execute a MySQL SHOW statement in SQLite.
1461	*
1462	* @param WP_Parser_Node $node The "showStatement" AST node.
1463	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1464	*/
1465	private function execute_show_statement( WP_Parser_Node $node ): void {
1466	$tokens = $node->get_child_tokens();
1467	$keyword1 = $tokens[1];
1468	$keyword2 = $tokens[2] ?? null;
1469
1470	switch ( $keyword1->id ) {
1471	case WP_MySQL_Lexer::COLUMNS_SYMBOL:
1472	case WP_MySQL_Lexer::FIELDS_SYMBOL:
1473	$this->execute_show_columns_statement( $node );
1474	break;
1475	case WP_MySQL_Lexer::CREATE_SYMBOL:
1476	if ( WP_MySQL_Lexer::TABLE_SYMBOL === $keyword2->id ) {
1477	$table_name = $this->unquote_sqlite_identifier(
1478	$this->translate( $node->get_first_child_node( 'tableRef' ) )
1479	);
1480
1481	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
1482
1483	$sql = $this->get_mysql_create_table_statement( $table_is_temporary, $table_name );
1484	if ( null === $sql ) {
1485	$this->set_results_from_fetched_data( array() );
1486	} else {
1487	$this->set_results_from_fetched_data(
1488	array(
1489	(object) array(
1490	'Create Table' => $sql,
1491	),
1492	)
1493	);
1494	}
1495	return;
1496	}
1497	// Fall through to default.
1498	case WP_MySQL_Lexer::INDEX_SYMBOL:
1499	case WP_MySQL_Lexer::INDEXES_SYMBOL:
1500	case WP_MySQL_Lexer::KEYS_SYMBOL:
1501	$table_name = $this->unquote_sqlite_identifier(
1502	$this->translate( $node->get_first_child_node( 'tableRef' ) )
1503	);
1504	$this->execute_show_index_statement( $table_name );
1505	break;
1506	case WP_MySQL_Lexer::GRANTS_SYMBOL:
1507	$this->set_results_from_fetched_data(
1508	array(
1509	(object) array(
1510	'Grants for root@localhost' => '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',
1511	),
1512	)
1513	);
1514	return;
1515	case WP_MySQL_Lexer::TABLE_SYMBOL:
1516	$this->execute_show_table_status_statement( $node );
1517	break;
1518	case WP_MySQL_Lexer::TABLES_SYMBOL:
1519	$this->execute_show_tables_statement( $node );
1520	break;
1521	case WP_MySQL_Lexer::VARIABLES_SYMBOL:
1522	$this->last_result = true;
1523	return;
1524	default:
1525	throw $this->new_not_supported_exception(
1526	sprintf(
1527	'statement type: "%s" > "%s"',
1528	$node->rule_name,
1529	$keyword1->get_value()
1530	)
1531	);
1532	}
1533	}
1534
1535	/**
1536	* Translate and execute a MySQL SHOW INDEX statement in SQLite.
1537	*
1538	* @param string $table_name The table name to show indexes for.
1539	*/
1540	private function execute_show_index_statement( string $table_name ): void {
1541	// TODO: FROM/IN (multiple)
1542	// TODO: WHERE
1543
1544	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
1545
1546	/*
1547	* TODO: Index naming.
1548	*
1549	* From the old driver:
1550	*
1551	* SQLite automatically assigns names to some indexes.
1552	* However, dbDelta in WordPress expects the name to be
1553	* the same as in the original CREATE TABLE. Let's
1554	* translate the name back.
1555	*
1556	* The old driver does the two following conversions:
1557	* 1)
1558	* $mysql_key_name = substr( $mysql_key_name, strlen( 'sqlite_autoindex_' ) );
1559	* $mysql_key_name = preg_replace( '/_[0-9]+$/', '', $mysql_key_name );
1560	* 2)
1561	* $mysql_key_name = substr( $mysql_key_name, strlen( "{$table_name}__" ) );
1562	*/
1563
1564	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
1565	$index_info = $this->execute_sqlite_query(
1566	'
1567	SELECT
1568	TABLE_NAME AS `Table`,
1569	NON_UNIQUE AS `Non_unique`,
1570	INDEX_NAME AS `Key_name`,
1571	SEQ_IN_INDEX AS `Seq_in_index`,
1572	COLUMN_NAME AS `Column_name`,
1573	COLLATION AS `Collation`,
1574	CARDINALITY AS `Cardinality`,
1575	SUB_PART AS `Sub_part`,
1576	PACKED AS `Packed`,
1577	NULLABLE AS `Null`,
1578	INDEX_TYPE AS `Index_type`,
1579	COMMENT AS `Comment`,
1580	INDEX_COMMENT AS `Index_comment`,
1581	IS_VISIBLE AS `Visible`,
1582	EXPRESSION AS `Expression`
1583	FROM ' . $this->quote_sqlite_identifier( $statistics_table ) . "
1584	WHERE table_schema = ?
1585	AND table_name = ?
1586	ORDER BY
1587	INDEX_NAME = 'PRIMARY' DESC,
1588	NON_UNIQUE = '0' DESC,
1589	INDEX_TYPE = 'SPATIAL' DESC,
1590	INDEX_TYPE = 'BTREE' DESC,
1591	INDEX_TYPE = 'FULLTEXT' DESC,
1592	ROWID,
1593	SEQ_IN_INDEX
1594	",
1595	array( $this->db_name, $table_name )
1596	)->fetchAll( PDO::FETCH_OBJ );
1597
1598	$this->set_results_from_fetched_data( $index_info );
1599	}
1600
1601	/**
1602	* Translate and execute a MySQL SHOW TABLE STATUS statement in SQLite.
1603	*
1604	* @param WP_Parser_Node $node The "showStatement" AST node.
1605	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1606	*/
1607	private function execute_show_table_status_statement( WP_Parser_Node $node ): void {
1608	// FROM/IN database.
1609	$in_db = $node->get_first_child_node( 'inDb' );
1610	if ( null === $in_db ) {
1611	$database = $this->db_name;
1612	} else {
1613	$database = $this->unquote_sqlite_identifier(
1614	$this->translate( $in_db->get_first_child_node( 'identifier' ) )
1615	);
1616	}
1617
1618	// LIKE and WHERE clauses.
1619	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
1620	if ( null !== $like_or_where ) {
1621	$condition = $this->translate_show_like_or_where_condition( $like_or_where );
1622	}
1623
1624	// Fetch table information.
1625	$tables_tables = $this->information_schema_builder->get_table_name(
1626	false, // SHOW TABLE STATUS lists only non-temporary tables.
1627	'tables'
1628	);
1629	$table_info = $this->execute_sqlite_query(
1630	sprintf(
1631	'SELECT * FROM %s WHERE table_schema = ? %s ORDER BY table_name',
1632	$this->quote_sqlite_identifier( $tables_tables ),
1633	$condition ?? ''
1634	),
1635	array( $database )
1636	)->fetchAll( PDO::FETCH_ASSOC );
1637
1638	if ( false === $table_info ) {
1639	$this->set_results_from_fetched_data( array() );
1640	}
1641
1642	// Format the results.
1643	$tables = array();
1644	foreach ( $table_info as $value ) {
1645	$tables[] = (object) array(
1646	'Name' => $value['TABLE_NAME'],
1647	'Engine' => $value['ENGINE'],
1648	'Version' => $value['VERSION'],
1649	'Row_format' => $value['ROW_FORMAT'],
1650	'Rows' => $value['TABLE_ROWS'],
1651	'Avg_row_length' => $value['AVG_ROW_LENGTH'],
1652	'Data_length' => $value['DATA_LENGTH'],
1653	'Max_data_length' => $value['MAX_DATA_LENGTH'],
1654	'Index_length' => $value['INDEX_LENGTH'],
1655	'Data_free' => $value['DATA_FREE'],
1656	'Auto_increment' => $value['AUTO_INCREMENT'],
1657	'Create_time' => $value['CREATE_TIME'],
1658	'Update_time' => $value['UPDATE_TIME'],
1659	'Check_time' => $value['CHECK_TIME'],
1660	'Collation' => $value['TABLE_COLLATION'],
1661	'Checksum' => $value['CHECKSUM'],
1662	'Create_options' => $value['CREATE_OPTIONS'],
1663	'Comment' => $value['TABLE_COMMENT'],
1664	);
1665	}
1666
1667	$this->set_results_from_fetched_data( $tables );
1668	}
1669
1670	/**
1671	* Translate and execute a MySQL SHOW TABLES statement in SQLite.
1672	*
1673	* @param WP_Parser_Node $node The "showStatement" AST node.
1674	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1675	*/
1676	private function execute_show_tables_statement( WP_Parser_Node $node ): void {
1677	// FROM/IN database.
1678	$in_db = $node->get_first_child_node( 'inDb' );
1679	if ( null === $in_db ) {
1680	$database = $this->db_name;
1681	} else {
1682	$database = $this->unquote_sqlite_identifier(
1683	$this->translate( $in_db->get_first_child_node( 'identifier' ) )
1684	);
1685	}
1686
1687	// LIKE and WHERE clauses.
1688	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
1689	if ( null !== $like_or_where ) {
1690	$condition = $this->translate_show_like_or_where_condition( $like_or_where );
1691	}
1692
1693	// Fetch table information.
1694	$table_tables = $this->information_schema_builder->get_table_name(
1695	false, // SHOW TABLES lists only non-temporary tables.
1696	'tables'
1697	);
1698	$table_info = $this->execute_sqlite_query(
1699	sprintf(
1700	'SELECT * FROM %s WHERE table_schema = ? %s ORDER BY table_name',
1701	$this->quote_sqlite_identifier( $table_tables ),
1702	$condition ?? ''
1703	),
1704	array( $database )
1705	)->fetchAll( PDO::FETCH_ASSOC );
1706
1707	if ( false === $table_info ) {
1708	$this->set_results_from_fetched_data( array() );
1709	}
1710
1711	// Handle the FULL keyword.
1712	$command_type = $node->get_first_child_node( 'showCommandType' );
1713	$is_full = $command_type && $command_type->has_child_token( WP_MySQL_Lexer::FULL_SYMBOL );
1714
1715	// Format the results.
1716	$tables = array();
1717	foreach ( $table_info as $value ) {
1718	$table = array(
1719	"Tables_in_$database" => $value['TABLE_NAME'],
1720	);
1721	if ( true === $is_full ) {
1722	$table['Table_type'] = $value['TABLE_TYPE'];
1723	}
1724	$tables[] = (object) $table;
1725	}
1726
1727	$this->set_results_from_fetched_data( $tables );
1728	}
1729
1730	/**
1731	* Translate and execute a MySQL SHOW COLUMNS statement in SQLite.
1732	*
1733	* @param WP_Parser_Node $node The "showStatement" AST node.
1734	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1735	* @throws PDOException When given table doesn't exist.
1736	*/
1737	private function execute_show_columns_statement( WP_Parser_Node $node ): void {
1738	// TODO: EXTENDED, FULL
1739	$table_name = $this->unquote_sqlite_identifier(
1740	$this->translate( $node->get_first_child_node( 'tableRef' ) )
1741	);
1742
1743	// FROM/IN database.
1744	$in_db = $node->get_first_child_node( 'inDb' );
1745	if ( null === $in_db ) {
1746	$database = $this->db_name;
1747	} else {
1748	$database = $this->unquote_sqlite_identifier(
1749	$this->translate( $in_db->get_first_child_node( 'identifier' ) )
1750	);
1751	}
1752
1753	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
1754
1755	// Check if the table exists.
1756	$tables_tables = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
1757	$table_exists = $this->execute_sqlite_query(
1758	sprintf(
1759	'SELECT 1 FROM %s WHERE table_schema = ? AND table_name = ?',
1760	$this->quote_sqlite_identifier( $tables_tables )
1761	),
1762	array( $this->db_name, $table_name )
1763	)->fetchColumn();
1764
1765	if ( ! $table_exists ) {
1766	throw $this->new_driver_exception(
1767	sprintf( "Table '%s.%s' doesn't exist", $database, $table_name ),
1768	'42S02'
1769	);
1770	}
1771
1772	// LIKE and WHERE clauses.
1773	$like_or_where = $node->get_first_child_node( 'likeOrWhere' );
1774	if ( null !== $like_or_where ) {
1775	$condition = $this->translate_show_like_or_where_condition( $like_or_where );
1776	}
1777
1778	// Fetch column information.
1779	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
1780	$column_info = $this->execute_sqlite_query(
1781	sprintf(
1782	'SELECT * FROM %s WHERE table_schema = ? AND table_name = ? %s ORDER BY ordinal_position',
1783	$this->quote_sqlite_identifier( $columns_table ),
1784	$condition ?? ''
1785	),
1786	array( $database, $table_name )
1787	)->fetchAll( PDO::FETCH_ASSOC );
1788
1789	if ( false === $column_info ) {
1790	$this->set_results_from_fetched_data( array() );
1791	}
1792
1793	// Format the results.
1794	$columns = array();
1795	foreach ( $column_info as $value ) {
1796	$column = array(
1797	'Field' => $value['COLUMN_NAME'],
1798	'Type' => $value['COLUMN_TYPE'],
1799	'Null' => $value['IS_NULLABLE'],
1800	'Key' => $value['COLUMN_KEY'],
1801	'Default' => $value['COLUMN_DEFAULT'],
1802	'Extra' => $value['EXTRA'],
1803	);
1804	$columns[] = (object) $column;
1805	}
1806
1807	$this->set_results_from_fetched_data( $columns );
1808	}
1809
1810	/**
1811	* Translate and execute a MySQL DESCRIBE statement in SQLite.
1812	*
1813	* @param WP_Parser_Node $node The "describeStatement" AST node.
1814	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1815	*/
1816	private function execute_describe_statement( WP_Parser_Node $node ): void {
1817	$table_name = $this->unquote_sqlite_identifier(
1818	$this->translate( $node->get_first_child_node( 'tableRef' ) )
1819	);
1820
1821	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
1822
1823	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
1824	$column_info = $this->execute_sqlite_query(
1825	'
1826	SELECT
1827	column_name AS `Field`,
1828	column_type AS `Type`,
1829	is_nullable AS `Null`,
1830	column_key AS `Key`,
1831	column_default AS `Default`,
1832	extra AS Extra
1833	FROM ' . $this->quote_sqlite_identifier( $columns_table ) . '
1834	WHERE table_schema = ?
1835	AND table_name = ?
1836	ORDER BY ordinal_position
1837	',
1838	array( $this->db_name, $table_name )
1839	)->fetchAll( PDO::FETCH_OBJ );
1840
1841	$this->set_results_from_fetched_data( $column_info );
1842	}
1843
1844	/**
1845	* Translate and execute a MySQL USE statement in SQLite.
1846	*
1847	* @param WP_Parser_Node $node The "useStatement" AST node.
1848	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1849	*/
1850	private function execute_use_statement( WP_Parser_Node $node ): void {
1851	$database_name = $this->unquote_sqlite_identifier(
1852	$this->translate( $node->get_first_child_node( 'identifier' ) )
1853	);
1854
1855	if ( 'information_schema' === strtolower( $database_name ) ) {
1856	$this->db_name = 'information_schema';
1857	} elseif ( $this->db_name === $database_name ) {
1858	$this->db_name = $database_name;
1859	} else {
1860	throw $this->new_not_supported_exception(
1861	sprintf(
1862	"can't use schema '%s', only '%s' and 'information_schema' are supported",
1863	$database_name,
1864	$this->db_name
1865	)
1866	);
1867	}
1868	}
1869
1870	/**
1871	* Translate and execute a MySQL SET statement in SQLite.
1872	*
1873	* @param WP_Parser_Node $node The "setStatement" AST node.
1874	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1875	*/
1876	private function execute_set_statement( WP_Parser_Node $node ): void {
1877	/*
1878	* 1. Flatten the SET statement into a single array of definitions.
1879	*
1880	* The grammar is non-trivial, and supports multi-statements like:
1881	* SET @var = '...', SESSION sql_mode = '...', @@GLOBAL.time_zone = '...', @@debug = '...', ...
1882	*
1883	* This will be flattened into a single array of grammar node lists:
1884	* [
1885	* [ <userVariable>, <equal>, <expr> ],
1886	* [ <optionType>, <internalVariableName>, <equal>, <setExprOrDefault> ],
1887	* [ <setSystemVariable>, <equal>, <setExprOrDefault> ],
1888	* [ <setSystemVariable>, <equal>, <setExprOrDefault> ],
1889	* ]
1890	*/
1891	$subnode = $node->get_first_child_node();
1892	if ( $subnode->has_child_node( 'optionValueNoOptionType' ) ) {
1893	$start_node = $subnode->get_first_child_node( 'optionValueNoOptionType' );
1894	$definitions = array( $start_node->get_children() );
1895	} elseif ( $subnode->has_child_node( 'startOptionValueListFollowingOptionType' ) ) {
1896	$start_node = $subnode
1897	->get_first_child_node( 'startOptionValueListFollowingOptionType' )
1898	->get_first_child_node( 'optionValueFollowingOptionType' ) ?? $node;
1899	$definitions = array(
1900	array_merge(
1901	array( $subnode->get_first_child_node( 'optionType' ) ),
1902	$start_node->get_children()
1903	),
1904	);
1905	} else {
1906	$definitions = array( $subnode->get_children() );
1907	}
1908
1909	$continue_node = $subnode->get_first_child_node( 'optionValueListContinued' );
1910	if ( $continue_node ) {
1911	foreach ( $continue_node->get_child_nodes( 'optionValue' ) as $child ) {
1912	$node = $child->get_first_child_node( 'optionValueNoOptionType' ) ?? $child;
1913	$definitions[] = $node->get_child_nodes();
1914	}
1915	}
1916
1917	/*
1918	* 2. Iterate and process the SET definitions.
1919	*
1920	* When an "optionType" node is encountered (such as "SESSION var = ..."),
1921	* it's value is used for all following system variable assignments that
1922	* have no type keyword specified, until the next "optionType" is found.
1923	*
1924	* This doesn't apply to "@@" type prefixes (such as "@@SESSION.var_name"),
1925	* which always impact only the immediately following system variable.
1926	*/
1927	$default_type = WP_MySQL_Lexer::SESSION_SYMBOL;
1928	foreach ( $definitions as $definition ) {
1929	// Check if the definition starts with an "optionType" node with
1930	// one of the SESSION, GLOBAL, PERSIST, or PERSIST_ONLY tokens.
1931	$part = array_shift( $definition );
1932	if ( $part instanceof WP_Parser_Node && 'optionType' === $part->rule_name ) {
1933	$default_type = $part->get_first_child_token()->id;
1934	$part = array_shift( $definition );
1935	}
1936
1937	if (
1938	$part instanceof WP_Parser_Node
1939	&& (
1940	'internalVariableName' === $part->rule_name
1941	\|\| 'setSystemVariable' === $part->rule_name
1942	)
1943	) {
1944	array_shift( $definition ); // Remove the '='.
1945	$value = array_shift( $definition );
1946	$this->execute_set_system_variable_statement( $part, $value, $default_type );
1947	} else {
1948	// TODO: Support user variables (in-memory or a temporary table).
1949	throw $this->new_not_supported_exception(
1950	sprintf( 'SET statement: %s', $node->rule_name )
1951	);
1952	}
1953	}
1954
1955	$this->last_result = 0;
1956	}
1957
1958	/**
1959	* Translate and execute a MySQL SET statement for system variables.
1960	*
1961	* @param WP_Parser_Node $set_var_node The "internalVariableName" or "setSystemVariable" AST node.
1962	* @param WP_Parser_Node $value_node The "setExprOrDefault" AST node.
1963	* @param int $default_type The currently active default variable type.
1964	* One of the SESSION, GLOBAL, PERSIST, PERSIST_ONLY tokens.
1965	* @throws WP_SQLite_Driver_Exception When the query execution fails.
1966	*/
1967	private function execute_set_system_variable_statement(
1968	WP_Parser_Node $set_var_node,
1969	WP_Parser_Node $value_node,
1970	int $default_type
1971	): void {
1972	// Get the variable name.
1973	$internal_variable_name = 'setSystemVariable' === $set_var_node->rule_name
1974	? $set_var_node->get_first_child_node( 'internalVariableName' )
1975	: $set_var_node;
1976
1977	$name = strtolower(
1978	$this->unquote_sqlite_identifier(
1979	$this->translate( $internal_variable_name )
1980	)
1981	);
1982
1983	// Get the type attribute (one of SESSION, GLOBAL, PERSIST, PERSIST_ONLY).
1984	$type = $default_type;
1985	if ( $set_var_node->has_child_node( 'setVarIdentType' ) ) {
1986	$var_ident_type = $set_var_node->get_first_child_node( 'setVarIdentType' );
1987	$type = $var_ident_type->get_first_child_token()->id;
1988	}
1989
1990	// Get the variable value.
1991	$value = $this->translate( $value_node );
1992	$value = str_replace( "''", "'", $value );
1993	$value = substr( $value, 1, -1 );
1994
1995	if ( WP_MySQL_Lexer::SESSION_SYMBOL === $type ) {
1996	if ( 'sql_mode' === $name ) {
1997	$modes = explode( ',', strtoupper( $value ) );
1998	$this->active_sql_modes = $modes;
1999	}
2000	} elseif ( WP_MySQL_Lexer::GLOBAL_SYMBOL === $type ) {
2001	throw $this->new_not_supported_exception( "SET statement type: 'GLOBAL'" );
2002	} elseif ( WP_MySQL_Lexer::PERSIST_SYMBOL === $type ) {
2003	throw $this->new_not_supported_exception( "SET statement type: 'PERSIST'" );
2004	} elseif ( WP_MySQL_Lexer::PERSIST_ONLY_SYMBOL === $type ) {
2005	throw $this->new_not_supported_exception( "SET statement type: 'PERSIST_ONLY'" );
2006	}
2007
2008	// TODO: Handle GLOBAL, PERSIST, and PERSIST_ONLY types.
2009	}
2010
2011	/**
2012	* Translate and execute a MySQL administration statement in SQLite.
2013	*
2014	* This emulates the following MySQL statements:
2015	* - ANALYZE TABLE
2016	* - CHECK TABLE
2017	* - OPTIMIZE TABLE
2018	* - REPAIR TABLE
2019	*
2020	* @param WP_Parser_Node $node A "tableAdministrationStatement" AST node.
2021	* @throws WP_SQLite_Driver_Exception When the query execution fails.
2022	*/
2023	private function execute_administration_statement( WP_Parser_Node $node ): void {
2024	$first_token = $node->get_first_child_token();
2025	$table_ref_list = $node->get_first_child_node( 'tableRefList' );
2026	$results = array();
2027	foreach ( $table_ref_list->get_child_nodes( 'tableRef' ) as $table_ref ) {
2028	$table_name = $this->unquote_sqlite_identifier( $this->translate( $table_ref ) );
2029	$quoted_table_name = $this->quote_sqlite_identifier( $table_name );
2030	try {
2031	switch ( $first_token->id ) {
2032	case WP_MySQL_Lexer::ANALYZE_SYMBOL:
2033	$stmt = $this->execute_sqlite_query( sprintf( 'ANALYZE %s', $quoted_table_name ) );
2034	$errors = $stmt->fetchAll( PDO::FETCH_COLUMN );
2035	break;
2036	case WP_MySQL_Lexer::CHECK_SYMBOL:
2037	$stmt = $this->execute_sqlite_query(
2038	sprintf( 'PRAGMA integrity_check(%s)', $quoted_table_name )
2039	);
2040	$errors = $stmt->fetchAll( PDO::FETCH_COLUMN );
2041	if ( 'ok' === $errors[0] ) {
2042	array_shift( $errors );
2043	}
2044	break;
2045	case WP_MySQL_Lexer::OPTIMIZE_SYMBOL:
2046	case WP_MySQL_Lexer::REPAIR_SYMBOL:
2047	/*
2048	* SQLite doesn't support OPTIMIZE and REPAIR TABLE commands.
2049	* We will recreate the table and copy the data instead.
2050	* This corresponds to older MySQL OPTIMIZE TABLE behavior
2051	* and still applies to some storage engines in some cases.
2052	*/
2053	$table_is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2054	$this->recreate_table_from_information_schema( $table_is_temporary, $table_name );
2055	$errors = array();
2056	break;
2057	default:
2058	throw $this->new_not_supported_exception(
2059	sprintf(
2060	'statement type: "%s" > "%s"',
2061	$node->rule_name,
2062	$first_token->get_value()
2063	)
2064	);
2065	}
2066	} catch ( PDOException $e ) {
2067	if ( 'HY000' === $e->getCode() ) {
2068	$errors = array( "Table '$table_name' doesn't exist" );
2069	} else {
2070	$errors = array( $e->getMessage() );
2071	}
2072	}
2073
2074	$operation = strtolower( $first_token->get_value() );
2075	foreach ( $errors as $error ) {
2076	$results[] = (object) array(
2077	'Table' => $this->db_name . '.' . $table_name,
2078	'Op' => $operation,
2079	'Msg_type' => 'Error',
2080	'Msg_text' => $error,
2081	);
2082	}
2083	$results[] = (object) array(
2084	'Table' => $this->db_name . '.' . $table_name,
2085	'Op' => $operation,
2086	'Msg_type' => 'status',
2087	'Msg_text' => count( $errors ) > 0 ? 'Operation failed' : 'OK',
2088	);
2089	}
2090	$this->set_results_from_fetched_data( $results );
2091	}
2092
2093	/**
2094	* Translate a MySQL AST node or token to an SQLite query fragment.
2095	*
2096	* @param WP_Parser_Node\|WP_MySQL_Token $node The AST node to translate.
2097	* @return string\|null The translated query fragment.
2098	* @throws WP_SQLite_Driver_Exception When the translation fails.
2099	*/
2100	private function translate( $node ): ?string {
2101	if ( null === $node ) {
2102	return null;
2103	}
2104
2105	if ( $node instanceof WP_MySQL_Token ) {
2106	return $this->translate_token( $node );
2107	}
2108
2109	if ( ! $node instanceof WP_Parser_Node ) {
2110	throw $this->new_driver_exception(
2111	sprintf(
2112	'Expected a WP_Parser_Node or WP_MySQL_Token instance, got: %s',
2113	gettype( $node )
2114	)
2115	);
2116	}
2117
2118	$rule_name = $node->rule_name;
2119	switch ( $rule_name ) {
2120	case 'querySpecification':
2121	// Translate "HAVING ..." without "GROUP BY ..." to "GROUP BY 1 HAVING ...".
2122	if ( $node->has_child_node( 'havingClause' ) && ! $node->has_child_node( 'groupByClause' ) ) {
2123	$parts = array();
2124	foreach ( $node->get_children() as $child ) {
2125	if ( $child instanceof WP_Parser_Node && 'havingClause' === $child->rule_name ) {
2126	$parts[] = 'GROUP BY 1';
2127	}
2128	$part = $this->translate( $child );
2129	if ( null !== $part ) {
2130	$parts[] = $part;
2131	}
2132	}
2133	return implode( ' ', $parts );
2134	}
2135	return $this->translate_sequence( $node->get_children() );
2136	case 'qualifiedIdentifier':
2137	case 'tableRefWithWildcard':
2138	$parts = $node->get_descendant_nodes( 'identifier' );
2139	if ( count( $parts ) === 2 ) {
2140	return $this->translate_qualified_identifier( $parts[0], $parts[1] );
2141	}
2142	return $this->translate_qualified_identifier( null, $parts[0] );
2143	case 'fieldIdentifier':
2144	case 'simpleIdentifier':
2145	$parts = $node->get_descendant_nodes( 'identifier' );
2146	if ( count( $parts ) === 3 ) {
2147	return $this->translate_qualified_identifier( $parts[0], $parts[1], $parts[2] );
2148	} elseif ( count( $parts ) === 2 ) {
2149	return $this->translate_qualified_identifier( null, $parts[0], $parts[1] );
2150	}
2151	return $this->translate_qualified_identifier( null, null, $parts[0] );
2152	case 'tableWild':
2153	$parts = $node->get_descendant_nodes( 'identifier' );
2154	if ( count( $parts ) === 2 ) {
2155	return $this->translate_qualified_identifier( $parts[0], $parts[1] ) . '.*';
2156	}
2157	return $this->translate_qualified_identifier( null, $parts[0] ) . '.*';
2158	case 'dotIdentifier':
2159	return $this->translate_sequence( $node->get_children(), '' );
2160	case 'identifierKeyword':
2161	return '`' . $this->translate( $node->get_first_child() ) . '`';
2162	case 'pureIdentifier':
2163	$value = $this->translate_pure_identifier( $node );
2164
2165	/*
2166	* At the moment, we only support ASCII bytes in all identifiers.
2167	* This is because SQLite doesn't support case-insensitive Unicode
2168	* character matching: https://sqlite.org/faq.html#q18
2169	*/
2170	for ( $i = 0; $i < strlen( $value ); $i++ ) {
2171	if ( ord( $value[ $i ] ) > 127 ) {
2172	throw $this->new_driver_exception(
2173	'The SQLite driver only supports ASCII characters in identifiers.'
2174	);
2175	}
2176	}
2177	return $value;
2178	case 'textStringLiteral':
2179	return $this->translate_string_literal( $node );
2180	case 'dataType':
2181	case 'nchar':
2182	$child = $node->get_first_child();
2183	if ( $child instanceof WP_Parser_Node ) {
2184	return $this->translate( $child );
2185	}
2186
2187	// Handle optional prefixes (data type is the second token):
2188	// 1. LONG VARCHAR, LONG CHAR(ACTER) VARYING, LONG VARBINARY.
2189	// 2. NATIONAL CHAR, NATIONAL VARCHAR, NATIONAL CHAR(ACTER) VARYING.
2190	if ( WP_MySQL_Lexer::LONG_SYMBOL === $child->id ) {
2191	$child = $node->get_child_tokens()[1] ?? null;
2192	} elseif ( WP_MySQL_Lexer::NATIONAL_SYMBOL === $child->id ) {
2193	$child = $node->get_child_tokens()[1] ?? null;
2194	}
2195
2196	if ( null === $child ) {
2197	throw $this->new_invalid_input_exception();
2198	}
2199
2200	$type_token = self::DATA_TYPE_MAP[ $child->id ] ?? null;
2201	if ( null !== $type_token ) {
2202	return $type_token;
2203	}
2204
2205	// SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
2206	if ( WP_MySQL_Lexer::SERIAL_SYMBOL === $child->id ) {
2207	return 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE';
2208	}
2209
2210	// @TODO: Handle SET and JSON.
2211	throw $this->new_not_supported_exception(
2212	sprintf( 'data type: %s', $child->get_value() )
2213	);
2214	case 'fromClause':
2215	// FROM DUAL is MySQL-specific syntax that means "FROM no tables"
2216	// and it is equivalent to omitting the FROM clause entirely.
2217	if ( $node->has_child_token( WP_MySQL_Lexer::DUAL_SYMBOL ) ) {
2218	return null;
2219	}
2220	return $this->translate_sequence( $node->get_children() );
2221	case 'insertUpdateList':
2222	// Translate "ON DUPLICATE KEY UPDATE" to "ON CONFLICT DO UPDATE SET".
2223	return sprintf(
2224	'ON CONFLICT DO UPDATE SET %s',
2225	$this->translate( $node->get_first_child_node( 'updateList' ) )
2226	);
2227	case 'simpleExpr':
2228	return $this->translate_simple_expr( $node );
2229	case 'predicateOperations':
2230	$token = $node->get_first_child_token();
2231	if ( WP_MySQL_Lexer::LIKE_SYMBOL === $token->id ) {
2232	return $this->translate_like( $node );
2233	} elseif ( WP_MySQL_Lexer::REGEXP_SYMBOL === $token->id ) {
2234	return $this->translate_regexp_functions( $node );
2235	}
2236	return $this->translate_sequence( $node->get_children() );
2237	case 'runtimeFunctionCall':
2238	return $this->translate_runtime_function_call( $node );
2239	case 'functionCall':
2240	return $this->translate_function_call( $node );
2241	case 'systemVariable':
2242	$var_ident_type = $node->get_first_child_node( 'varIdentType' );
2243	$type_token = $var_ident_type ? $var_ident_type->get_first_child_token() : null;
2244	$original_name = $this->unquote_sqlite_identifier(
2245	$this->translate( $node->get_first_child_node( 'textOrIdentifier' ) )
2246	);
2247
2248	$name = strtolower( $original_name );
2249	$type = $type_token ? $type_token->id : WP_MySQL_Lexer::SESSION_SYMBOL;
2250	if ( 'sql_mode' === $name ) {
2251	$value = $this->connection->quote( implode( ',', $this->active_sql_modes ) );
2252	} else {
2253	// When we have no value, it's reasonable to use NULL.
2254	$value = 'NULL';
2255	}
2256
2257	// @TODO: Emulate more system variables, or use reasonable defaults.
2258	// See: https://dev.mysql.com/doc/refman/8.4/en/server-system-variable-reference.html
2259	// See: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html
2260
2261	// TODO: Original name should come from the original MySQL input,
2262	// exactly as it was written by the user, and not translated.
2263
2264	// TODO: The '% AS %' syntax is compatible with SELECT lists only.
2265	// We need to translate it differently when used as a value.
2266	return sprintf(
2267	'%s AS %s',
2268	$value,
2269	$this->quote_sqlite_identifier(
2270	'@@' . ( $type_token ? "{$type_token->get_value()}." : '' ) . $original_name
2271	)
2272	);
2273	case 'castType':
2274	// Translate "CAST(... AS BINARY)" to "CAST(... AS BLOB)".
2275	if ( $node->has_child_token( WP_MySQL_Lexer::BINARY_SYMBOL ) ) {
2276	return 'BLOB';
2277	}
2278	return $this->translate_sequence( $node->get_children() );
2279	case 'defaultCollation':
2280	// @TODO: Check and save in information schema.
2281	return null;
2282	case 'duplicateAsQueryExpression':
2283	// @TODO: How to handle IGNORE/REPLACE?
2284
2285	// The "AS" keyword is optional in MySQL, but required in SQLite.
2286	return 'AS ' . $this->translate( $node->get_first_child_node() );
2287	case 'indexHint':
2288	case 'indexHintList':
2289	return null;
2290	default:
2291	return $this->translate_sequence( $node->get_children() );
2292	}
2293	}
2294
2295	/**
2296	* Translate a MySQL token to SQLite.
2297	*
2298	* @param WP_MySQL_Token $token The MySQL token to translate.
2299	* @return string\|null The translated value.
2300	*/
2301	private function translate_token( WP_MySQL_Token $token ): ?string {
2302	switch ( $token->id ) {
2303	case WP_MySQL_Lexer::EOF:
2304	return null;
2305	case WP_MySQL_Lexer::AUTO_INCREMENT_SYMBOL:
2306	return 'AUTOINCREMENT';
2307	case WP_MySQL_Lexer::BINARY_SYMBOL:
2308	/*
2309	* There is no "BINARY expr" equivalent in SQLite. We look for the
2310	* keyword from a higher level to respect it in particular cases
2311	* (REGEXP, LIKE, etc.) and then remove it from the output here.
2312	*/
2313	return null;
2314	case WP_MySQL_Lexer::SQL_CALC_FOUND_ROWS_SYMBOL:
2315	/*
2316	* The "SQL_CALC_FOUND_ROWS" keyword is implemented in the select
2317	* statement translation and then removed from the output here.
2318	*/
2319	return null;
2320	default:
2321	return $token->get_value();
2322	}
2323	}
2324
2325	/**
2326	* Translate a sequence of MySQL AST nodes to SQLite.
2327	*
2328	* @param array<WP_Parser_Node\|WP_MySQL_Token> $nodes The MySQL token to translate.
2329	* @param string $separator The separator to use between fragments.
2330	* @return string\|null The translated value.
2331	* @throws WP_SQLite_Driver_Exception When the translation fails.
2332	*/
2333	private function translate_sequence( array $nodes, string $separator = ' ' ): ?string {
2334	$parts = array();
2335	foreach ( $nodes as $node ) {
2336	if ( null === $node ) {
2337	continue;
2338	}
2339
2340	$translated = $this->translate( $node );
2341	if ( null === $translated ) {
2342	continue;
2343	}
2344	$parts[] = $translated;
2345	}
2346	if ( 0 === count( $parts ) ) {
2347	return null;
2348	}
2349	return implode( $separator, $parts );
2350	}
2351
2352	/**
2353	* Translate a MySQL string literal to SQLite.
2354	*
2355	* @param WP_Parser_Node $node The "textStringLiteral" AST node.
2356	* @return string The translated value.
2357	*/
2358	private function translate_string_literal( WP_Parser_Node $node ): string {
2359	$token = $node->get_first_child_token();
2360	$value = $token->get_value();
2361
2362	/*
2363	* 5. Translate datetime literals.
2364	*
2365	* Process only strings that could possibly represent a datetime
2366	* literal ("YYYY-MM-DDTHH:MM:SS", "YYYY-MM-DDTHH:MM:SSZ", etc.).
2367	*/
2368	if ( strlen( $value ) >= 19 && is_numeric( $value[0] ) ) {
2369	$value = $this->translate_datetime_literal( $value );
2370	}
2371
2372	/*
2373	* 6. Handle null characters.
2374	*
2375	* SQLite doesn't fully support null characters (\u0000) in strings.
2376	* However, it can store them and read them, with some limitations.
2377	*
2378	* In PHP, null bytes are often produced by the serialize() function.
2379	* Removing them would damage the serialized data.
2380	*
2381	* There is no way to store null bytes using a string literal, so we
2382	* need to split the string and concatenate null bytes with its parts.
2383	* This will convert literals will null bytes to expressions.
2384	*
2385	* Alternatively, we could replace string literals with parameters and
2386	* pass them using prepared statements. However, that's not universally
2387	* applicable for all string literals (e.g., in default column values).
2388	*
2389	* See:
2390	* https://www.sqlite.org/nulinstr.html
2391	*/
2392	$parts = array();
2393	foreach ( explode( "\0", $value ) as $segment ) {
2394	// Escape and quote each segment.
2395	$parts[] = "'" . str_replace( "'", "''", $segment ) . "'";
2396	}
2397	if ( count( $parts ) > 1 ) {
2398	return '(' . implode( ' \|\| CHAR(0) \|\| ', $parts ) . ')';
2399	}
2400	return $parts[0];
2401	}
2402
2403	/**
2404	* Translate a MySQL pure identifier to SQLite.
2405	*
2406	* @param WP_Parser_Node $node The "pureIdentifier" AST node.
2407	* @return string The translated value.
2408	*/
2409	private function translate_pure_identifier( WP_Parser_Node $node ): string {
2410	$token = $node->get_first_child_token();
2411	$value = $token->get_value();
2412	return '`' . str_replace( '`', '``', $value ) . '`';
2413	}
2414
2415	/**
2416	* Translate a qualified MySQL identifier to SQLite.
2417	*
2418	* The identifier can be composed of 1 to 3 parts (schema, object, child).
2419	*
2420	* @param WP_Parser_Node\|null $schema_node An identifier node representing a schema name (database).
2421	* @param WP_Parser_Node\|null $object_node An identifier node representing a database-level object name
2422	* (table, view, procedure, trigger, etc.).
2423	* @param WP_Parser_Node\|null $child_node An identifier node representing an object child name (column, index, etc.).
2424	* @return string The translated value.
2425	* @throws WP_SQLite_Driver_Exception When the translation fails.
2426	*/
2427	private function translate_qualified_identifier(
2428	?WP_Parser_Node $schema_node,
2429	?WP_Parser_Node $object_node = null,
2430	?WP_Parser_Node $child_node = null
2431	): string {
2432	$parts = array();
2433	$uses_reserved_prefix = false;
2434
2435	// Database name.
2436	$is_information_schema = 'information_schema' === $this->db_name;
2437	if ( null !== $schema_node ) {
2438	$schema_name = $this->unquote_sqlite_identifier(
2439	$this->translate_sequence( $schema_node->get_children() )
2440	);
2441	if ( 'information_schema' === strtolower( $schema_name ) ) {
2442	$is_information_schema = true;
2443	} elseif ( $this->db_name === $schema_name ) {
2444	$is_information_schema = false;
2445	} else {
2446	throw $this->new_not_supported_exception(
2447	sprintf(
2448	"can't use schema '%s', only '%s' and 'information_schema' are supported",
2449	$schema_name,
2450	$this->db_name
2451	)
2452	);
2453	}
2454	}
2455
2456	/*
2457	* Make the 'information_schema' database read-only.
2458	*
2459	* This basic approach is rather restrictive, as it blocks the usage
2460	* of information schema tables in all data-modifying statements.
2461	*
2462	* Some of these statements can be valid, when the schema is only read:
2463	* DELETE t FROM t JOIN information_schema.columns c ON ...
2464	*
2465	* If needed, a more granular approach can be implemented in the future.
2466	*/
2467	if ( true === $is_information_schema && false === $this->is_readonly ) {
2468	throw $this->new_driver_exception(
2469	"Access denied for user 'sqlite'@'%' to database 'information_schema'",
2470	'42000'
2471	);
2472	}
2473
2474	// Database-level object name (table, view, procedure, trigger, etc.).
2475	if ( null !== $object_node ) {
2476	if ( $is_information_schema ) {
2477	$object_name = $this->unquote_sqlite_identifier(
2478	$this->translate_sequence( $object_node->get_children() )
2479	);
2480	$parts[] = $this->information_schema_builder->get_table_name( false, $object_name );
2481	} else {
2482	$quoted_object_name = $this->translate( $object_node );
2483	$object_name = $this->unquote_sqlite_identifier( $quoted_object_name );
2484	if ( str_starts_with( $object_name, self::RESERVED_PREFIX ) ) {
2485	$uses_reserved_prefix = true;
2486	}
2487	$parts[] = $quoted_object_name;
2488	}
2489	}
2490
2491	// Object child name (column, index, etc.).
2492	if ( null !== $child_node ) {
2493	$quoted_object_name = $this->translate( $child_node );
2494	$object_name = $this->unquote_sqlite_identifier( $quoted_object_name );
2495	if ( str_starts_with( $object_name, self::RESERVED_PREFIX ) ) {
2496	$uses_reserved_prefix = true;
2497	}
2498	$parts[] = $quoted_object_name;
2499	}
2500
2501	$identifier = implode( '.', $parts );
2502
2503	if ( true === $uses_reserved_prefix ) {
2504	throw $this->new_driver_exception(
2505	sprintf(
2506	"Invalid identifier %s, prefix '%s' is reserved",
2507	$identifier,
2508	self::RESERVED_PREFIX
2509	)
2510	);
2511	}
2512
2513	return $identifier;
2514	}
2515
2516	/**
2517	* Translate a MySQL simple expression to SQLite.
2518	*
2519	* @param WP_Parser_Node $node The "simpleExpr" AST node.
2520	* @return string The translated value.
2521	* @throws WP_SQLite_Driver_Exception When the translation fails.
2522	*/
2523	private function translate_simple_expr( WP_Parser_Node $node ): string {
2524	$token = $node->get_first_child_token();
2525
2526	// Translate "VALUES(col)" to "excluded.col" in ON DUPLICATE KEY UPDATE.
2527	if ( null !== $token && WP_MySQL_Lexer::VALUES_SYMBOL === $token->id ) {
2528	return sprintf(
2529	'`excluded`.%s',
2530	$this->translate( $node->get_first_child_node( 'simpleIdentifier' ) )
2531	);
2532	}
2533
2534	return $this->translate_sequence( $node->get_children() );
2535	}
2536
2537	/**
2538	* Translate a MySQL LIKE expression to SQLite.
2539	*
2540	* @param WP_Parser_Node $node The "predicateOperations" AST node.
2541	* @return string The translated value.
2542	* @throws WP_SQLite_Driver_Exception When the translation fails.
2543	*/
2544	private function translate_like( WP_Parser_Node $node ): string {
2545	$tokens = $node->get_descendant_tokens();
2546	$is_binary = isset( $tokens[1] ) && WP_MySQL_Lexer::BINARY_SYMBOL === $tokens[1]->id;
2547
2548	if ( true === $is_binary ) {
2549	$children = $node->get_children();
2550	return sprintf(
2551	'GLOB _helper_like_to_glob_pattern(%s)',
2552	$this->translate( $children[1] )
2553	);
2554	}
2555
2556	/*
2557	* @TODO: Implement the ESCAPE '...' clause.
2558	*/
2559
2560	/*
2561	* @TODO: Implement more correct LIKE behavior.
2562	*
2563	* While SQLite supports the LIKE operator, it seems to differ from the
2564	* MySQL behavior in some ways:
2565	*
2566	* 1. In SQLite, LIKE is case-insensitive only for ASCII characters
2567	* ('a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE)
2568	* 2. In MySQL, LIKE interprets some escape sequences. See the contents
2569	* of the "_helper_like_to_glob_pattern" function.
2570	*
2571	* We'll probably need to overload the like() function:
2572	* https://www.sqlite.org/lang_corefunc.html#like
2573	*/
2574	$statement = $this->translate_sequence( $node->get_children() );
2575	if ( $this->is_sql_mode_active( 'NO_BACKSLASH_ESCAPES' ) ) {
2576	return $statement;
2577	}
2578	return $statement . " ESCAPE '\\'";
2579	}
2580
2581	/**
2582	* Translate MySQL REGEXP expression to SQLite.
2583	*
2584	* @param WP_Parser_Node $node The "predicateOperations" AST node.
2585	* @return string The translated value.
2586	* @throws WP_SQLite_Driver_Exception When the translation fails.
2587	*/
2588	private function translate_regexp_functions( WP_Parser_Node $node ): string {
2589	$tokens = $node->get_descendant_tokens();
2590	$is_binary = isset( $tokens[1] ) && WP_MySQL_Lexer::BINARY_SYMBOL === $tokens[1]->id;
2591
2592	/*
2593	* If the query says REGEXP BINARY, the comparison is byte-by-byte
2594	* and letter casing matters – lowercase and uppercase letters are
2595	* represented using different byte codes.
2596	*
2597	* The REGEXP function can't be easily made to accept two
2598	* parameters, so we'll have to use a hack to get around this.
2599	*
2600	* If the first character of the pattern is a null byte, we'll
2601	* remove it and make the comparison case-sensitive. This should
2602	* be reasonably safe since PHP does not allow null bytes in
2603	* regular expressions anyway.
2604	*/
2605	if ( true === $is_binary ) {
2606	return 'REGEXP CHAR(0) \|\| ' . $this->translate( $node->get_first_child_node() );
2607	}
2608	return 'REGEXP ' . $this->translate( $node->get_first_child_node() );
2609	}
2610
2611	/**
2612	* Translate a MySQL runtime function call to SQLite.
2613	*
2614	* @param WP_Parser_Node $node The "runtimeFunctionCall" AST node.
2615	* @return string The translated value.
2616	* @throws WP_SQLite_Driver_Exception When the translation fails.
2617	*/
2618	private function translate_runtime_function_call( WP_Parser_Node $node ): string {
2619	$child = $node->get_first_child();
2620	if ( $child instanceof WP_Parser_Node ) {
2621	return $this->translate( $child );
2622	}
2623
2624	switch ( $child->id ) {
2625	case WP_MySQL_Lexer::CURRENT_TIMESTAMP_SYMBOL:
2626	case WP_MySQL_Lexer::NOW_SYMBOL:
2627	/*
2628	* 1) SQLite doesn't support CURRENT_TIMESTAMP() with parentheses.
2629	* 2) In MySQL, CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are an
2630	* alias of NOW(). In SQLite, there is no NOW() function.
2631	*/
2632	return 'CURRENT_TIMESTAMP';
2633	case WP_MySQL_Lexer::DATE_ADD_SYMBOL:
2634	case WP_MySQL_Lexer::DATE_SUB_SYMBOL:
2635	$nodes = $node->get_child_nodes();
2636	$value = $this->translate( $nodes[1] );
2637	$unit = $this->translate( $nodes[2] );
2638	if ( 'WEEK' === $unit ) {
2639	$unit = 'DAY';
2640	$value = 7 * $value;
2641	}
2642	return sprintf(
2643	"DATETIME(%s, '%s' \|\| %s \|\| ' %s')",
2644	$this->translate( $nodes[0] ),
2645	WP_MySQL_Lexer::DATE_SUB_SYMBOL === $child->id ? '-' : '+',
2646	$value,
2647	$unit
2648	);
2649	case WP_MySQL_Lexer::LEFT_SYMBOL:
2650	$nodes = $node->get_child_nodes();
2651	return sprintf(
2652	'SUBSTRING(%s, 1, %s)',
2653	$this->translate( $nodes[0] ),
2654	$this->translate( $nodes[1] )
2655	);
2656	default:
2657	return $this->translate_sequence( $node->get_children() );
2658	}
2659	}
2660
2661	/**
2662	* Translate a MySQL function call to SQLite.
2663	*
2664	* @param WP_Parser_Node $node The "functionCall" AST node.
2665	* @return string The translated value.
2666	* @throws WP_SQLite_Driver_Exception When the translation fails.
2667	*/
2668	private function translate_function_call( WP_Parser_Node $node ): string {
2669	$nodes = $node->get_child_nodes();
2670	$name = strtoupper(
2671	$this->unquote_sqlite_identifier( $this->translate( $nodes[0] ) )
2672	);
2673
2674	$args = array();
2675	if ( isset( $nodes[1] ) ) {
2676	foreach ( $nodes[1]->get_child_nodes() as $child ) {
2677	$args[] = $this->translate( $child );
2678	}
2679	}
2680
2681	switch ( $name ) {
2682	case 'DATE_FORMAT':
2683	list ( $date, $mysql_format ) = $args;
2684
2685	$format = strtr( $mysql_format, self::MYSQL_DATE_FORMAT_TO_SQLITE_STRFTIME_MAP );
2686	if ( ! $format ) {
2687	throw $this->new_driver_exception(
2688	sprintf(
2689	'Could not translate a DATE_FORMAT() format to STRFTIME format (%s)',
2690	$mysql_format
2691	)
2692	);
2693	}
2694
2695	/*
2696	* MySQL supports comparing strings and floats, e.g.
2697	*
2698	* > SELECT '00.42' = 0.4200
2699	* 1
2700	*
2701	* SQLite does not support that. At the same time,
2702	* WordPress likes to filter dates by comparing numeric
2703	* outputs of DATE_FORMAT() to floats, e.g.:
2704	*
2705	* -- Filter by hour and minutes
2706	* DATE_FORMAT(
2707	* STR_TO_DATE('2014-10-21 00:42:29', '%Y-%m-%d %H:%i:%s'),
2708	* '%H.%i'
2709	* ) = 0.4200;
2710	*
2711	* Let's cast the STRFTIME() output to a float if
2712	* the date format is typically used for string
2713	* to float comparisons.
2714	*
2715	* In the future, let's update WordPress to avoid comparing
2716	* strings and floats.
2717	*/
2718	$cast_to_float = "'%H.%i'" === $mysql_format;
2719	if ( true === $cast_to_float ) {
2720	return sprintf( 'CAST(STRFTIME(%s, %s) AS FLOAT)', $format, $date );
2721	}
2722	return sprintf( 'STRFTIME(%s, %s)', $format, $date );
2723	case 'CHAR_LENGTH':
2724	// @TODO LENGTH and CHAR_LENGTH aren't always the same in MySQL for utf8 characters.
2725	return 'LENGTH(' . $args[0] . ')';
2726	case 'CONCAT':
2727	return '(' . implode( ' \|\| ', $args ) . ')';
2728	case 'FOUND_ROWS':
2729	// @TODO: The following implementation with an alias assumes
2730	// that the function is used in the SELECT field list.
2731	// For compatibility with more complex use cases, it may
2732	// be better to register it as a custom SQLite function.
2733	$found_rows = $this->last_sql_calc_found_rows;
2734	if ( null === $found_rows && is_array( $this->last_result ) ) {
2735	$found_rows = count( $this->last_result );
2736	}
2737	return sprintf( "(SELECT %d) AS 'FOUND_ROWS()'", $found_rows );
2738	default:
2739	return $this->translate_sequence( $node->get_children() );
2740	}
2741	}
2742
2743	/**
2744	* Translate a MySQL datetime literal to SQLite.
2745	*
2746	* @param string $value The MySQL datetime literal.
2747	* @return string The translated value.
2748	*/
2749	private function translate_datetime_literal( string $value ): string {
2750	/*
2751	* The code below converts the date format to one preferred by SQLite.
2752	*
2753	* MySQL accepts ISO 8601 date strings: 'YYYY-MM-DDTHH:MM:SSZ'
2754	* SQLite prefers a slightly different format: 'YYYY-MM-DD HH:MM:SS'
2755	*
2756	* SQLite date and time functions can understand the ISO 8601 notation, but
2757	* lookups don't. To keep the lookups working, we need to store all dates
2758	* in UTC without the "T" and "Z" characters.
2759	*
2760	* Caveat: It will adjust every string that matches the pattern, not just dates.
2761	*
2762	* In theory, we could only adjust semantic dates, e.g. the data inserted
2763	* to a date column or compared against a date column.
2764	*
2765	* In practice, this is hard because dates are just text – SQLite has no separate
2766	* datetime field. We'd need to cache the MySQL data type from the original
2767	* CREATE TABLE query and then keep refreshing the cache after each ALTER TABLE query.
2768	*
2769	* That's a lot of complexity that's perhaps not worth it. Let's just convert
2770	* everything for now. The regexp assumes "Z" is always at the end of the string,
2771	* which is true in the unit test suite, but there could also be a timezone offset
2772	* like "+00:00" or "+01:00". We could add support for that later if needed.
2773	*/
2774	if ( 1 === preg_match( '/^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})Z$/', $value, $matches ) ) {
2775	$value = $matches[1] . ' ' . $matches[2];
2776	}
2777
2778	/*
2779	* Mimic MySQL's behavior and truncate invalid dates.
2780	*
2781	* "2020-12-41 14:15:27" becomes "0000-00-00 00:00:00"
2782	*
2783	* WARNING: We have no idea whether the truncated value should
2784	* be treated as a date in the first place.
2785	* In SQLite dates are just strings. This could be a perfectly
2786	* valid string that just happens to contain a date-like value.
2787	*
2788	* At the same time, WordPress seems to rely on MySQL's behavior
2789	* and even tests for it in Tests_Post_wpInsertPost::test_insert_empty_post_date.
2790	* Let's truncate the dates for now.
2791	*
2792	* In the future, let's update WordPress to do its own date validation
2793	* and stop relying on this MySQL feature,
2794	*/
2795	if ( 1 === preg_match( '/^(\d{4}-\d{2}-\d{2}) (\d{2}:\d{2}:\d{2})$/', $value, $matches ) ) {
2796	/*
2797	* Calling strtotime("0000-00-00 00:00:00") in 32-bit environments triggers
2798	* an "out of integer range" warning – let's avoid that call for the popular
2799	* case of "zero" dates.
2800	*/
2801	if ( '0000-00-00 00:00:00' !== $value && false === strtotime( $value ) ) {
2802	$value = '0000-00-00 00:00:00';
2803	}
2804	}
2805	return $value;
2806	}
2807
2808	/**
2809	* Recreate an existing table using data in the information schema.
2810	*
2811	* This is used for a generic support of ALTER TABLE queries, as well as
2812	* for some other statements like OPTIMIZE TABLE and REPAIR TABLE.
2813	*
2814	* See:
2815	* https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes
2816	*
2817	* @param bool $table_is_temporary Whether the table is temporary.
2818	* @param string $table_name The name of the table to recreate.
2819	* @param array $column_map Optional. A map of column names (old name -> new name)
2820	* to use when copying data from the original table.
2821	* When not provided, all columns are copied without renaming.
2822	* @throws WP_SQLite_Driver_Exception
2823	*/
2824	private function recreate_table_from_information_schema(
2825	bool $table_is_temporary,
2826	string $table_name,
2827	?array $column_map = null
2828	): void {
2829	if ( null === $column_map ) {
2830	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
2831	$column_names = $this->execute_sqlite_query(
2832	sprintf(
2833	'SELECT COLUMN_NAME FROM %s WHERE table_schema = ? AND table_name = ?',
2834	$this->quote_sqlite_identifier( $columns_table )
2835	),
2836	array( $this->db_name, $table_name )
2837	)->fetchAll( PDO::FETCH_COLUMN );
2838	$column_map = array_combine( $column_names, $column_names );
2839	}
2840
2841	// Preserve ROWIDs.
2842	// This also addresses a special case when all original columns are dropped
2843	// and there is nothing to copy. We'll always have at least the ROWID column.
2844	$column_map = array( 'rowid' => 'rowid' ) + $column_map;
2845
2846	/*
2847	* See:
2848	* https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes
2849	*/
2850
2851	// 1. If foreign key constraints are enabled, disable them.
2852	$pragma_foreign_keys = $this->execute_sqlite_query( 'PRAGMA foreign_keys' )->fetchColumn();
2853	$this->execute_sqlite_query( 'PRAGMA foreign_keys = OFF' );
2854
2855	// 2. Create a new table with the new schema.
2856	$tmp_table_name = self::RESERVED_PREFIX . "tmp_{$table_name}_" . uniqid();
2857	$quoted_table_name = $this->quote_sqlite_identifier( $table_name );
2858	$quoted_tmp_table_name = $this->quote_sqlite_identifier( $tmp_table_name );
2859	$queries = $this->get_sqlite_create_table_statement( $table_is_temporary, $table_name, $tmp_table_name );
2860	$create_table_query = $queries[0];
2861	$constraint_queries = array_slice( $queries, 1 );
2862	$this->execute_sqlite_query( $create_table_query );
2863
2864	// 3. Copy data from the original table to the new table.
2865	$this->execute_sqlite_query(
2866	sprintf(
2867	'INSERT INTO %s (%s) SELECT %s FROM %s',
2868	$quoted_tmp_table_name,
2869	implode(
2870	', ',
2871	array_map( array( $this, 'quote_sqlite_identifier' ), $column_map )
2872	),
2873	implode(
2874	', ',
2875	array_map( array( $this, 'quote_sqlite_identifier' ), array_keys( $column_map ) )
2876	),
2877	$quoted_table_name
2878	)
2879	);
2880
2881	// 4. Drop the original table.
2882	$this->execute_sqlite_query( sprintf( 'DROP TABLE %s', $quoted_table_name ) );
2883
2884	// 5. Rename the new table to the original table name.
2885	$this->execute_sqlite_query(
2886	sprintf(
2887	'ALTER TABLE %s RENAME TO %s',
2888	$quoted_tmp_table_name,
2889	$quoted_table_name
2890	)
2891	);
2892
2893	// 6. Reconstruct indexes, triggers, and views.
2894	foreach ( $constraint_queries as $query ) {
2895	$this->execute_sqlite_query( $query );
2896	}
2897
2898	// 7. If foreign key constraints were enabled, verify and enable them.
2899	if ( '1' === $pragma_foreign_keys ) {
2900	$this->execute_sqlite_query( 'PRAGMA foreign_key_check' );
2901	$this->execute_sqlite_query( 'PRAGMA foreign_keys = ON' );
2902	}
2903
2904	// @TODO: Triggers and views.
2905	}
2906
2907	/**
2908	* Translate a MySQL SHOW LIKE ... or SHOW WHERE ... condition to SQLite.
2909	*
2910	* @param WP_Parser_Node $like_or_where The "likeOrWhere" AST node.
2911	* @return string The translated value.
2912	* @throws WP_SQLite_Driver_Exception When the translation fails.
2913	*/
2914	private function translate_show_like_or_where_condition( WP_Parser_Node $like_or_where ): string {
2915	$like_clause = $like_or_where->get_first_child_node( 'likeClause' );
2916	if ( null !== $like_clause ) {
2917	$value = $this->translate(
2918	$like_clause->get_first_child_node( 'textStringLiteral' )
2919	);
2920	return sprintf( "AND table_name LIKE %s ESCAPE '\\'", $value );
2921	}
2922
2923	$where_clause = $like_or_where->get_first_child_node( 'whereClause' );
2924	if ( null !== $where_clause ) {
2925	$value = $this->translate(
2926	$where_clause->get_first_child_node( 'expr' )
2927	);
2928	return sprintf( 'AND %s', $value );
2929	}
2930
2931	return '';
2932	}
2933
2934	/**
2935	* Translate INSERT or REPLACE statement body to SQLite, while emulating
2936	* the behavior of MySQL implicit default values in non-strict mode.
2937	*
2938	* Rewrites a statement body in the following form:
2939	* INSERT INTO table (optionally some columns) <select-or-values>
2940	* To a statement body with the following structure:
2941	* INSERT INTO table (all table columns)
2942	* SELECT <non-strict-mode-adjusted-values> FROM (<select-or-values>) WHERE true
2943	*
2944	* In MySQL, the behavior of INSERT and UPDATE statements depends on whether
2945	* the STRICT_TRANS_TABLES (InnoDB) or STRICT_ALL_TABLES SQL mode is enabled.
2946	*
2947	* By default, STRICT_TRANS_TABLES is enabled, which makes the InnoDB table
2948	* behavior correspond to the natural behavior of SQLite tables. However,
2949	* some applications, including WordPress, disable strict mode altogether.
2950	*
2951	* The strict SQL modes can be set per session, and can be changed at runtime.
2952	* In SQLite, we can emulate this using the knowledge of the table structure:
2953	* 1. Explicitly passed INSERT statement values are used without change.
2954	* 2. Values omitted from the INSERT statement are replaced with the column
2955	* DEFAULT or an IMPLICIT DEFAULT value based on their data type.
2956	*
2957	* Here's a summary of the strict vs. non-strict behaviors in MySQL:
2958	*
2959	* When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled:
2960	* 1. NULL + NO DEFAULT: No value saves NULL, NULL saves NULL, DEFAULT saves NULL.
2961	* 2. NULL + DEFAULT: No value saves DEFAULT, NULL saves NULL, DEFAULT saves DEFAULT.
2962	* 3. NOT NULL + NO DEFAULT: No value is rejected, NULL is rejected, DEFAULT is rejected.
2963	* 4. NOT NULL + DEFAULT: No value saves DEFAULT, NULL is rejected, DEFAULT saves DEFAULT.
2964	*
2965	* When STRICT_TRANS_TABLES and STRICT_ALL_TABLES are disabled:
2966	* 1. NULL + NO DEFAULT: No value saves NULL, NULL saves NULL, DEFAULT saves NULL.
2967	* 2. NULL + DEFAULT: No value saves DEFAULT, NULL saves NULL, DEFAULT saves DEFAULT.
2968	* 3. NOT NULL + NO DEFAULT: No value saves IMPLICIT DEFAULT.
2969	* NULL is rejected on INSERT, but saves IMPLICIT DEFAULT on UPDATE.
2970	* DEFAULT saves IMPLICIT DEFAULT.
2971	* 4. NOT NULL + DEFAULT: No value saves DEFAULT.
2972	* NULL is rejected on INSERT, but saves IMPLICIT DEFAULT on UPDATE.
2973	* DEFAULT saves DEFAULT.
2974	*
2975	* For more information about IMPLICIT DEFAULT values in MySQL, see:
2976	* https://dev.mysql.com/doc/refman/8.4/en/data-type-defaults.html#data-type-defaults-implicit
2977	*
2978	* @param string $table_name The name of the target table.
2979	* @param WP_Parser_Node $node The "insertQueryExpression" or "insertValues" AST node.
2980	* @return string The translated INSERT query body.
2981	*/
2982	private function translate_insert_or_replace_body_in_non_strict_mode(
2983	string $table_name,
2984	WP_Parser_Node $node
2985	): string {
2986	// 1. Get column metadata from information schema.
2987	$is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
2988	$columns_table = $this->information_schema_builder->get_table_name( $is_temporary, 'columns' );
2989	$columns = $this->execute_sqlite_query(
2990	'
2991	SELECT column_name, is_nullable, column_default, data_type, extra
2992	FROM ' . $this->quote_sqlite_identifier( $columns_table ) . '
2993	WHERE table_schema = ?
2994	AND table_name = ?
2995	ORDER BY ordinal_position
2996	',
2997	array( $this->db_name, $table_name )
2998	)->fetchAll( PDO::FETCH_ASSOC );
2999
3000	// 2. Get the list of fields explicitly defined in the INSERT statement.
3001	$insert_list = array();
3002	$fields_node = $node->get_first_child_node( 'fields' );
3003	if ( $fields_node ) {
3004	// This is the optional "INSERT INTO ... (field1, field2, ...)" list.
3005	foreach ( $fields_node->get_child_nodes() as $field ) {
3006	$insert_list[] = $this->unquote_sqlite_identifier( $this->translate( $field ) );
3007	}
3008	} else {
3009	// When no explicit field list is provided, all columns are required.
3010	foreach ( array_column( $columns, 'COLUMN_NAME' ) as $column_name ) {
3011	$insert_list[] = $column_name;
3012	}
3013	}
3014
3015	// 3. Filter out omitted columns that will get a value from the SQLite engine.
3016	// That is, nullable columns, columns with defaults, and generated columns.
3017	$columns = array_values(
3018	array_filter(
3019	$columns,
3020	function ( $column ) use ( $insert_list ) {
3021	$is_omitted = ! in_array( $column['COLUMN_NAME'], $insert_list, true );
3022	if ( ! $is_omitted ) {
3023	return true;
3024	}
3025	$is_nullable = 'YES' === $column['IS_NULLABLE'];
3026	$has_default = $column['COLUMN_DEFAULT'];
3027	$is_generated = str_contains( $column['EXTRA'], 'auto_increment' );
3028	return ! ( $is_nullable \|\| $has_default \|\| $is_generated );
3029	}
3030	)
3031	);
3032
3033	// 4. Get the list of column names returned by VALUES or SELECT clause.
3034	$select_list = array();
3035	if ( 'insertQueryExpression' === $node->rule_name ) {
3036	// When inserting from a SELECT query, we don't know the column names.
3037	// Let's wrap the query with a SELECT (...) LIMIT 0 to get obtain them.
3038	$expr = $node->get_first_child_node( 'queryExpressionOrParens' );
3039	$stmt = $this->execute_sqlite_query(
3040	'SELECT * FROM (' . $this->translate( $expr ) . ') LIMIT 1'
3041	);
3042	$stmt->execute();
3043
3044	for ( $i = 0; $i < $stmt->columnCount(); $i++ ) {
3045	$select_list[] = $stmt->getColumnMeta( $i )['name'];
3046	}
3047	} else {
3048	// When inserting from a VALUES list, SQLite uses "columnN" naming.
3049	foreach ( array_keys( $insert_list ) as $position ) {
3050	$select_list[] = 'column' . ( $position + 1 );
3051	}
3052	}
3053
3054	// 5. Compose a new INSERT field list with all columns from the table.
3055	$fragment = '(';
3056	foreach ( $columns as $i => $column ) {
3057	$fragment .= $i > 0 ? ', ' : '';
3058	$fragment .= $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
3059	}
3060	$fragment .= ')';
3061
3062	// 6. Compose a wrapper SELECT statement emulating IMPLICIT DEFAULT values.
3063	$fragment .= ' SELECT ';
3064	foreach ( $columns as $i => $column ) {
3065	$is_omitted = ! in_array( $column['COLUMN_NAME'], $insert_list, true );
3066	$fragment .= $i > 0 ? ', ' : '';
3067	if ( $is_omitted ) {
3068	/*
3069	* When a column is omitted from the INSERT list, we need to use
3070	* an IMPLICIT DEFAULT value. Note that at this point, all omitted
3071	* columns that will not get an implicit default are filtered out.
3072	* (That is, nullable, generated, and columns with true defaults.)
3073	*/
3074	$default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $column['DATA_TYPE'] ] ?? null;
3075	$fragment .= null === $default ? 'NULL' : $this->connection->quote( $default );
3076	} else {
3077	// When a column value is included, we need to apply type casting.
3078	$position = array_search( $column['COLUMN_NAME'], $insert_list, true );
3079	$identifier = $this->quote_sqlite_identifier( $select_list[ $position ] );
3080	$fragment .= sprintf(
3081	'%s AS %s',
3082	$this->cast_value_in_non_strict_mode( $column['DATA_TYPE'], $identifier ),
3083	$identifier
3084	);
3085	}
3086	}
3087
3088	// 6. Wrap the original insert VALUES or SELECT expression in a FROM clause.
3089	$values = 'insertFromConstructor' === $node->rule_name
3090	? $node->get_first_child_node( 'insertValues' )
3091	: $node->get_first_child_node( 'queryExpressionOrParens' );
3092
3093	/*
3094	* The "WHERE true" suffix is used to avoid parsing ambiguity in SQLite.
3095	* When an "ON CONFLICT" clause is used and there is no "WHERE", SQLite
3096	* doesn't know if "ON" belongs to a "JOIN" or an "ON CONFLICT" clause.
3097	*
3098	* See: https://www.sqlite.org/lang_insert.html
3099	*/
3100	$fragment .= ' FROM (' . $this->translate( $values ) . ') WHERE true';
3101
3102	return $fragment;
3103	}
3104
3105	/**
3106	* Translate UPDATE list, emulating MySQL implicit defaults in non-strict mode.
3107	*
3108	* Rewrites an UPDATE statement list in the following form:
3109	* UPDATE table SET <non-null-column> = <value>
3110	* To a list with the following structure:
3111	* UPDATE table SET <non-null-column> = COALESCE(<value>, <implicit-default>)
3112	*
3113	* In MySQL, the behavior of INSERT and UPDATE statements depends on whether
3114	* the STRICT_TRANS_TABLES (InnoDB) or STRICT_ALL_TABLES SQL mode is enabled.
3115	*
3116	* When the strict mode is not enabled, executing an UPDATE statement that
3117	* sets a NOT NULL column value to NULL saves an IMPLICIT DEFAULT instead.
3118	*
3119	* @param string $table_name The name of the target table.
3120	* @param WP_Parser_Node $node The "updateList" AST node.
3121	* @return string The translated UPDATE list.
3122	*/
3123	private function translate_update_list_in_non_strict_mode( string $table_name, WP_Parser_Node $node ): string {
3124	// 1. Get column metadata from information schema.
3125	$is_temporary = $this->information_schema_builder->temporary_table_exists( $table_name );
3126	$columns_table = $this->information_schema_builder->get_table_name( $is_temporary, 'columns' );
3127	$columns = $this->execute_sqlite_query(
3128	'
3129	SELECT LOWER(column_name) AS COLUMN_NAME, is_nullable, data_type, column_default
3130	FROM ' . $this->quote_sqlite_identifier( $columns_table ) . '
3131	WHERE table_schema = ?
3132	AND table_name = ?
3133	',
3134	array( $this->db_name, $table_name )
3135	)->fetchAll( PDO::FETCH_ASSOC );
3136	$column_map = array_combine( array_column( $columns, 'COLUMN_NAME' ), $columns );
3137
3138	// 2. Translate UPDATE list, emulating implicit defaults for NULLs values.
3139	$fragment = '';
3140	foreach ( $node->get_child_nodes() as $i => $update_element ) {
3141	$column_ref = $update_element->get_first_child_node( 'columnRef' );
3142	$expr = $update_element->get_first_child_node( 'expr' );
3143
3144	// Get column info.
3145	$column_name = $this->unquote_sqlite_identifier( $this->translate( $column_ref ) );
3146	$column_info = $column_map[ strtolower( $column_name ) ];
3147	$data_type = $column_info['DATA_TYPE'];
3148	$is_nullable = 'YES' === $column_info['IS_NULLABLE'];
3149	$default = $column_info['COLUMN_DEFAULT'];
3150
3151	// Get the UPDATE value. It's either an expression or a DEFAULT keyword.
3152	if ( null === $expr ) {
3153	// Emulate "column = DEFAULT".
3154	$value = null === $default ? 'NULL' : $this->connection->quote( $default );
3155	} else {
3156	$value = $this->translate( $expr );
3157	}
3158
3159	// Apply type casting.
3160	$value = $this->cast_value_in_non_strict_mode( $data_type, $value );
3161
3162	// If the column is NOT NULL, a NULL value resolves to implicit default.
3163	$implicit_default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $data_type ] ?? null;
3164	if ( ! $is_nullable && null !== $implicit_default ) {
3165	$value = sprintf( 'COALESCE(%s, %s)', $value, $this->connection->quote( $implicit_default ) );
3166	}
3167
3168	// Compose the UPDATE list item.
3169	$fragment .= $i > 0 ? ', ' : '';
3170	$fragment .= $this->translate( $column_ref );
3171	$fragment .= ' = ';
3172	$fragment .= $value;
3173	}
3174	return $fragment;
3175	}
3176
3177	/**
3178	* Emulate MySQL type casting for INSERT or UPDATE value in non-strict mode.
3179	*
3180	* @param string $mysql_data_type The MySQL data type.
3181	* @param string $translated_value The original translated value.
3182	* @return string The translated value.
3183	*/
3184	private function cast_value_in_non_strict_mode(
3185	string $mysql_data_type,
3186	string $translated_value
3187	): string {
3188	$sqlite_data_type = self::DATA_TYPE_STRING_MAP[ $mysql_data_type ];
3189
3190	// Get and quote the IMPLICIT DEFAULT value.
3191	$implicit_default = self::DATA_TYPE_IMPLICIT_DEFAULT_MAP[ $mysql_data_type ] ?? null;
3192	$quoted_implicit_default = null === $implicit_default
3193	? 'NULL'
3194	: $this->connection->quote( $implicit_default );
3195
3196	/*
3197	* In MySQL, when saving a value via INSERT or UPDATE in non-strict mode,
3198	* 1. MySQL attempts to cast the value to the target column data type.
3199	* 2. When casting can't be done, MySQL saves an IMPLICIT DEFAULT.
3200	*/
3201	switch ( $mysql_data_type ) {
3202	case 'date':
3203	case 'time':
3204	case 'datetime':
3205	case 'timestamp':
3206	case 'year':
3207	/*
3208	* MySQL supports date and time components without a zero padding,
3209	* but that doesn't work with date and time functions in SQLite.
3210	* E.g.: "2025-3-7 9:5:2" is a valid datetime/timestamp value in
3211	* in MySQL, but SQLite requires it to be "2025-03-07 09:05:02".
3212	*
3213	* A solution to this would need to be done on the SQL level to
3214	* address computed values, and it should be done for the strict
3215	* mode as well. This may require a user-defined function.
3216	*
3217	* TODO: Handle zero padding for date and time functions, while
3218	* supporting both strict and non-strict modes.
3219	*/
3220
3221	if ( 'date' === $mysql_data_type ) {
3222	$function_call = sprintf( 'DATE(%s)', $translated_value );
3223	} elseif ( 'time' === $mysql_data_type ) {
3224	$function_call = sprintf( 'TIME(%s)', $translated_value );
3225	} elseif ( 'datetime' === $mysql_data_type \|\| 'timestamp' === $mysql_data_type ) {
3226	$function_call = sprintf( 'DATETIME(%s)', $translated_value );
3227	} elseif ( 'year' === $mysql_data_type ) {
3228	$function_call = sprintf( "STRFTIME('%%Y', %s)", $translated_value );
3229	}
3230
3231	// When the function call evaluates to NULL (invalid date/time),
3232	// we need to fallback to the IMPLICIT DEFAULT value.
3233	return sprintf(
3234	'IIF(%s IS NULL, NULL, COALESCE(%s, %s))',
3235	$translated_value,
3236	$function_call,
3237	$quoted_implicit_default
3238	);
3239	default:
3240	// For all other data types, use SQLite-native CAST expression.
3241	$mysql_data_type = strtolower( $mysql_data_type );
3242	return sprintf( 'CAST(%s AS %s)', $translated_value, $sqlite_data_type );
3243	}
3244	}
3245
3246	/**
3247	* Generate a SQLite CREATE TABLE statement from information schema data.
3248	*
3249	* @param bool $table_is_temporary Whether the table is temporary.
3250	* @param string $table_name The name of the table to create.
3251	* @param string\|null $new_table_name Override the original table name for ALTER TABLE emulation.
3252	* @return string[] Queries to create the table, indexes, and constraints.
3253	* @throws WP_SQLite_Driver_Exception When the table information is missing.
3254	*/
3255	private function get_sqlite_create_table_statement(
3256	bool $table_is_temporary,
3257	string $table_name,
3258	?string $new_table_name = null
3259	): array {
3260	// 1. Get table info.
3261	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
3262	$table_info = $this->execute_sqlite_query(
3263	'
3264	SELECT *
3265	FROM ' . $this->quote_sqlite_identifier( $tables_table ) . "
3266	WHERE table_type = 'BASE TABLE'
3267	AND table_schema = ?
3268	AND table_name = ?
3269	",
3270	array( $this->db_name, $table_name )
3271	)->fetch( PDO::FETCH_ASSOC );
3272
3273	if ( false === $table_info ) {
3274	throw $this->new_driver_exception(
3275	sprintf( "Table '%s' doesn't exist", $table_name ),
3276	'42S02'
3277	);
3278	}
3279
3280	// 2. Get column info.
3281	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
3282	$column_info = $this->execute_sqlite_query(
3283	sprintf(
3284	'SELECT * FROM %s WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position',
3285	$this->quote_sqlite_identifier( $columns_table )
3286	),
3287	array( $this->db_name, $table_name )
3288	)->fetchAll( PDO::FETCH_ASSOC );
3289
3290	// 3. Get index info, grouped by index name.
3291	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
3292	$constraint_info = $this->execute_sqlite_query(
3293	sprintf(
3294	"
3295	SELECT *
3296	FROM %s
3297	WHERE table_schema = ?
3298	AND table_name = ?
3299	ORDER BY
3300	INDEX_NAME = 'PRIMARY' DESC,
3301	NON_UNIQUE = '0' DESC,
3302	INDEX_TYPE = 'SPATIAL' DESC,
3303	INDEX_TYPE = 'BTREE' DESC,
3304	INDEX_TYPE = 'FULLTEXT' DESC,
3305	ROWID,
3306	SEQ_IN_INDEX
3307	",
3308	$this->quote_sqlite_identifier( $statistics_table )
3309	),
3310	array( $this->db_name, $table_name )
3311	)->fetchAll( PDO::FETCH_ASSOC );
3312
3313	$grouped_constraints = array();
3314	foreach ( $constraint_info as $constraint ) {
3315	$name = $constraint['INDEX_NAME'];
3316	$seq = $constraint['SEQ_IN_INDEX'];
3317	$grouped_constraints[ $name ][ $seq ] = $constraint;
3318	}
3319
3320	// 4. Generate CREATE TABLE statement columns.
3321	$rows = array();
3322	$on_update_queries = array();
3323	$has_autoincrement = false;
3324	foreach ( $column_info as $column ) {
3325	$query = ' ';
3326	$query .= $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
3327
3328	$type = self::DATA_TYPE_STRING_MAP[ $column['DATA_TYPE'] ];
3329
3330	/*
3331	* In SQLite, there is a PRIMARY KEY quirk for backward compatibility.
3332	* This applies to ROWID tables and single-column primary keys only:
3333	* 1. "INTEGER PRIMARY KEY" creates an alias of ROWID.
3334	* 2. "INT PRIMARY KEY" will not alias of ROWID.
3335	*
3336	* Therefore, we want to:
3337	* 1. Use "INT PRIMARY KEY" when we have a single-column integer
3338	* PRIMARY KEY without AUTOINCREMENT (to avoid the ROWID alias).
3339	* 2. Use "INTEGER PRIMARY KEY" otherwise.
3340	*
3341	* In SQLite, "AUTOINCREMENT" is only allowed on "INTEGER PRIMARY KEY",
3342	* and setting it changes the automatic ROWID assignment algorithm to
3343	* prevent the reuse of ROWIDs. Using "INT PRIMARY KEY" is not allowed.
3344	*
3345	* See:
3346	* - https://www.sqlite.org/autoinc.html
3347	* - https://www.sqlite.org/lang_createtable.html
3348	*/
3349	if (
3350	'INTEGER' === $type
3351	&& 'PRI' === $column['COLUMN_KEY']
3352	&& 'auto_increment' !== $column['EXTRA']
3353	&& count( $grouped_constraints['PRIMARY'] ) === 1
3354	) {
3355	$type = 'INT';
3356	}
3357
3358	$query .= ' ' . $type;
3359
3360	// In MySQL, text fields are case-insensitive by default.
3361	// COLLATE NOCASE emulates the same behavior in SQLite.
3362	// @TODO: Respect the actual column and index collation.
3363	if ( 'TEXT' === $type ) {
3364	$query .= ' COLLATE NOCASE';
3365	}
3366	if ( 'NO' === $column['IS_NULLABLE'] ) {
3367	$query .= ' NOT NULL';
3368	}
3369	if ( 'auto_increment' === $column['EXTRA'] ) {
3370	$has_autoincrement = true;
3371	$query .= ' PRIMARY KEY AUTOINCREMENT';
3372	}
3373	if ( null !== $column['COLUMN_DEFAULT'] ) {
3374	// @TODO: Handle defaults with expression values (DEFAULT_GENERATED).
3375
3376	// Handle DEFAULT CURRENT_TIMESTAMP. This works only with timestamp
3377	// and datetime columns. For other column types, it's just a string.
3378	if (
3379	'CURRENT_TIMESTAMP' === $column['COLUMN_DEFAULT']
3380	&& ( 'timestamp' === $column['DATA_TYPE'] \|\| 'datetime' === $column['DATA_TYPE'] )
3381	) {
3382	$query .= ' DEFAULT CURRENT_TIMESTAMP';
3383	} else {
3384	$query .= ' DEFAULT ' . $this->connection->quote( $column['COLUMN_DEFAULT'] );
3385	}
3386	}
3387	$rows[] = $query;
3388
3389	if ( 'on update CURRENT_TIMESTAMP' === $column['EXTRA'] ) {
3390	$on_update_queries[] = $this->get_column_on_update_trigger_query(
3391	$table_name,
3392	$column['COLUMN_NAME']
3393	);
3394	}
3395	}
3396
3397	// 5. Generate CREATE TABLE statement constraints, collect indexes.
3398	$create_index_queries = array();
3399	foreach ( $grouped_constraints as $constraint ) {
3400	ksort( $constraint );
3401	$info = $constraint[1];
3402
3403	if ( 'PRIMARY' === $info['INDEX_NAME'] ) {
3404	if ( $has_autoincrement ) {
3405	if ( count( $constraint ) > 1 ) {
3406	throw $this->new_driver_exception(
3407	'Cannot combine AUTOINCREMENT and multiple primary keys in SQLite'
3408	);
3409	}
3410	continue;
3411	}
3412	$query = ' PRIMARY KEY (';
3413	$query .= implode(
3414	', ',
3415	array_map(
3416	function ( $column ) {
3417	return $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
3418	},
3419	$constraint
3420	)
3421	);
3422	$query .= ')';
3423	$rows[] = $query;
3424	} else {
3425	$is_unique = '0' === $info['NON_UNIQUE'];
3426
3427	// Prefix the original index name with the table name.
3428	// This is to avoid conflicting index names in SQLite.
3429	$index_name = $this->quote_sqlite_identifier(
3430	$table_name . '__' . $info['INDEX_NAME']
3431	);
3432
3433	$query = sprintf(
3434	'CREATE %sINDEX %s ON %s (',
3435	$is_unique ? 'UNIQUE ' : '',
3436	$index_name,
3437	$this->quote_sqlite_identifier( $table_name )
3438	);
3439	$query .= implode(
3440	', ',
3441	array_map(
3442	function ( $column ) {
3443	return $this->quote_sqlite_identifier( $column['COLUMN_NAME'] );
3444	},
3445	$constraint
3446	)
3447	);
3448	$query .= ')';
3449
3450	$create_index_queries[] = $query;
3451	}
3452	}
3453
3454	// 6. Compose the CREATE TABLE statement.
3455	$create_table_query = sprintf(
3456	"CREATE %sTABLE %s (\n",
3457	$table_is_temporary ? 'TEMPORARY ' : '',
3458	$this->quote_sqlite_identifier( $new_table_name ?? $table_name )
3459	);
3460	$create_table_query .= implode( ",\n", $rows );
3461	$create_table_query .= "\n) STRICT";
3462	return array_merge( array( $create_table_query ), $create_index_queries, $on_update_queries );
3463	}
3464
3465	/**
3466	* Generate a MySQL CREATE TABLE statement from information schema data.
3467	*
3468	* @param bool $table_is_temporary Whether the table is temporary.
3469	* @param string $table_name The name of the table to create.
3470	* @return string The CREATE TABLE statement.
3471	*/
3472	private function get_mysql_create_table_statement( bool $table_is_temporary, string $table_name ): ?string {
3473	// 1. Get table info.
3474	$tables_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'tables' );
3475	$table_info = $this->execute_sqlite_query(
3476	'
3477	SELECT *
3478	FROM ' . $this->quote_sqlite_identifier( $tables_table ) . "
3479	WHERE table_type = 'BASE TABLE'
3480	AND table_schema = ?
3481	AND table_name = ?
3482	",
3483	array( $this->db_name, $table_name )
3484	)->fetch( PDO::FETCH_ASSOC );
3485
3486	if ( false === $table_info ) {
3487	return null;
3488	}
3489
3490	// 2. Get column info.
3491	$columns_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'columns' );
3492	$column_info = $this->execute_sqlite_query(
3493	sprintf(
3494	'
3495	SELECT *
3496	FROM %s
3497	WHERE table_schema = ?
3498	AND table_name = ?
3499	ORDER BY ordinal_position
3500	',
3501	$this->quote_sqlite_identifier( $columns_table )
3502	),
3503	array( $this->db_name, $table_name )
3504	)->fetchAll( PDO::FETCH_ASSOC );
3505
3506	// 3. Get index info, grouped by index name.
3507	$statistics_table = $this->information_schema_builder->get_table_name( $table_is_temporary, 'statistics' );
3508	$constraint_info = $this->execute_sqlite_query(
3509	sprintf(
3510	"
3511	SELECT *
3512	FROM %s
3513	WHERE table_schema = ?
3514	AND table_name = ?
3515	ORDER BY
3516	INDEX_NAME = 'PRIMARY' DESC,
3517	NON_UNIQUE = '0' DESC,
3518	INDEX_TYPE = 'SPATIAL' DESC,
3519	INDEX_TYPE = 'BTREE' DESC,
3520	INDEX_TYPE = 'FULLTEXT' DESC,
3521	ROWID,
3522	SEQ_IN_INDEX
3523	",
3524	$this->quote_sqlite_identifier( $statistics_table )
3525	),
3526	array( $this->db_name, $table_name )
3527	)->fetchAll( PDO::FETCH_ASSOC );
3528
3529	$grouped_constraints = array();
3530	foreach ( $constraint_info as $constraint ) {
3531	$name = $constraint['INDEX_NAME'];
3532	$seq = $constraint['SEQ_IN_INDEX'];
3533	$grouped_constraints[ $name ][ $seq ] = $constraint;
3534	}
3535
3536	// 4. Generate CREATE TABLE statement columns.
3537	$rows = array();
3538	foreach ( $column_info as $column ) {
3539	$sql = ' ';
3540	$sql .= $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
3541	$sql .= ' ' . $column['COLUMN_TYPE'];
3542	if ( 'NO' === $column['IS_NULLABLE'] ) {
3543	$sql .= ' NOT NULL';
3544	} elseif ( 'timestamp' === $column['COLUMN_TYPE'] ) {
3545	// Nullable "timestamp" columns dump NULL explicitly.
3546	$sql .= ' NULL';
3547	}
3548	if ( 'auto_increment' === $column['EXTRA'] ) {
3549	$sql .= ' AUTO_INCREMENT';
3550	}
3551
3552	// Handle DEFAULT CURRENT_TIMESTAMP. This works only with timestamp
3553	// and datetime columns. For other column types, it's just a string.
3554	if (
3555	'CURRENT_TIMESTAMP' === $column['COLUMN_DEFAULT']
3556	&& ( 'timestamp' === $column['DATA_TYPE'] \|\| 'datetime' === $column['DATA_TYPE'] )
3557	) {
3558	$sql .= ' DEFAULT CURRENT_TIMESTAMP';
3559	} elseif ( null !== $column['COLUMN_DEFAULT'] ) {
3560	$sql .= ' DEFAULT ' . $this->quote_mysql_utf8_string_literal( $column['COLUMN_DEFAULT'] );
3561	} elseif ( 'YES' === $column['IS_NULLABLE'] ) {
3562	$sql .= ' DEFAULT NULL';
3563	}
3564
3565	// Handle ON UPDATE CURRENT_TIMESTAMP.
3566	if ( str_contains( $column['EXTRA'], 'on update CURRENT_TIMESTAMP' ) ) {
3567	$sql .= ' ON UPDATE CURRENT_TIMESTAMP';
3568	}
3569
3570	if ( '' !== $column['COLUMN_COMMENT'] ) {
3571	$sql .= sprintf(
3572	' COMMENT %s',
3573	$this->quote_mysql_utf8_string_literal( $column['COLUMN_COMMENT'] )
3574	);
3575	}
3576
3577	$rows[] = $sql;
3578	}
3579
3580	// 4. Generate CREATE TABLE statement constraints, collect indexes.
3581	foreach ( $grouped_constraints as $constraint ) {
3582	ksort( $constraint );
3583	$info = $constraint[1];
3584
3585	if ( 'PRIMARY' === $info['INDEX_NAME'] ) {
3586	$sql = ' PRIMARY KEY (';
3587	$sql .= implode(
3588	', ',
3589	array_map(
3590	function ( $column ) {
3591	return $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
3592	},
3593	$constraint
3594	)
3595	);
3596	$sql .= ')';
3597	} else {
3598	$is_unique = '0' === $info['NON_UNIQUE'];
3599
3600	$sql = sprintf(
3601	' %s%s%sKEY ',
3602	$is_unique ? 'UNIQUE ' : '',
3603	'FULLTEXT' === $info['INDEX_TYPE'] ? 'FULLTEXT ' : '',
3604	'SPATIAL' === $info['INDEX_TYPE'] ? 'SPATIAL ' : ''
3605	);
3606	$sql .= $this->quote_mysql_identifier( $info['INDEX_NAME'] );
3607	$sql .= ' (';
3608	$sql .= implode(
3609	', ',
3610	array_map(
3611	function ( $column ) {
3612	$definition = $this->quote_mysql_identifier( $column['COLUMN_NAME'] );
3613	if ( null !== $column['SUB_PART'] ) {
3614	$definition .= sprintf( '(%d)', $column['SUB_PART'] );
3615	}
3616	return $definition;
3617	},
3618	$constraint
3619	)
3620	);
3621	$sql .= ')';
3622	}
3623
3624	if ( '' !== $info['INDEX_COMMENT'] ) {
3625	$sql .= sprintf(
3626	' COMMENT %s',
3627	$this->quote_mysql_utf8_string_literal( $info['INDEX_COMMENT'] )
3628	);
3629	}
3630
3631	$rows[] = $sql;
3632	}
3633
3634	// 5. Compose the CREATE TABLE statement.
3635	$collation = $table_info['TABLE_COLLATION'];
3636	$charset = substr( $collation, 0, strpos( $collation, '_' ) );
3637
3638	$sql = sprintf(
3639	"CREATE %sTABLE %s (\n",
3640	$table_is_temporary ? 'TEMPORARY ' : '',
3641	$this->quote_mysql_identifier( $table_name )
3642	);
3643	$sql .= implode( ",\n", $rows );
3644	$sql .= "\n)";
3645	$sql .= sprintf( ' ENGINE=%s', $table_info['ENGINE'] );
3646	$sql .= sprintf( ' DEFAULT CHARSET=%s', $charset );
3647	$sql .= sprintf( ' COLLATE=%s', $collation );
3648	if ( '' !== $table_info['TABLE_COMMENT'] ) {
3649	$sql .= sprintf(
3650	' COMMENT=%s',
3651	$this->quote_mysql_utf8_string_literal( $table_info['TABLE_COMMENT'] )
3652	);
3653	}
3654	return $sql;
3655	}
3656
3657
3658	/**
3659	* Get an SQLite query to emulate MySQL "ON UPDATE CURRENT_TIMESTAMP".
3660	*
3661	* In SQLite, "ON UPDATE CURRENT_TIMESTAMP" is not supported. We need to
3662	* create a trigger to emulate this behavior.
3663	*
3664	* @param string $table The table name.
3665	* @param string $column The column name.
3666	*/
3667	private function get_column_on_update_trigger_query( string $table, string $column ): string {
3668	// The trigger wouldn't work for virtual and "WITHOUT ROWID" tables,
3669	// but currently that can't happen as we're not creating such tables.
3670	// See: https://www.sqlite.org/rowidtable.html
3671	$trigger_name = self::RESERVED_PREFIX . "{$table}_{$column}_on_update";
3672	return sprintf(
3673	'
3674	CREATE TRIGGER %s
3675	AFTER UPDATE ON %s
3676	FOR EACH ROW
3677	BEGIN
3678	UPDATE %s SET %s = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
3679	END
3680	',
3681	$this->quote_sqlite_identifier( $trigger_name ),
3682	$this->quote_sqlite_identifier( $table ),
3683	$this->quote_sqlite_identifier( $table ),
3684	$this->quote_sqlite_identifier( $column )
3685	);
3686	}
3687
3688	/**
3689	* Unquote a quoted SQLite identifier.
3690	*
3691	* Remove bounding quotes and replace escaped quotes with their values.
3692	*
3693	* @param string $quoted_identifier The quoted identifier value.
3694	* @return string The unquoted identifier value.
3695	*/
3696	private function unquote_sqlite_identifier( string $quoted_identifier ): string {
3697	$first_byte = $quoted_identifier[0] ?? null;
3698	if ( '"' === $first_byte \|\| '`' === $first_byte ) {
3699	$unquoted = substr( $quoted_identifier, 1, -1 );
3700	return str_replace( $first_byte . $first_byte, $first_byte, $unquoted );
3701	}
3702	return $quoted_identifier;
3703	}
3704
3705	/**
3706	* Quote an SQLite identifier.
3707	*
3708	* @param string $unquoted_identifier The unquoted identifier value.
3709	* @return string The quoted identifier value.
3710	*/
3711	private function quote_sqlite_identifier( string $unquoted_identifier ): string {
3712	return $this->connection->quote_identifier( $unquoted_identifier );
3713	}
3714
3715	/**
3716	* Quote a MySQL identifier.
3717	*
3718	* Wrap the identifier in backticks and escape backtick values within.
3719	*
3720	* @param string $unquoted_identifier The unquoted identifier value.
3721	* @return string The quoted identifier value.
3722	*/
3723	private function quote_mysql_identifier( string $unquoted_identifier ): string {
3724	return '`' . str_replace( '`', '``', $unquoted_identifier ) . '`';
3725	}
3726
3727	/**
3728	* Format a MySQL UTF-8 string literal for output in a CREATE TABLE statement.
3729	*
3730	* We expect UTF-8 strings coming from SQLite. The only characters that must
3731	* be escaped in a single-quoted string for a UTF-8 MySQL dump are ' and \.
3732	*
3733	* MySQL SHOW CREATE TABLE command additionally escapes "\0", "\n", and "\r",
3734	* for the mysql CLI, logs, and better readability. This applies to column
3735	* default values, and table, column, and index comments. Other values, such
3736	* as identifiers, don't have these extra characters escaped in the output.
3737	*
3738	* See:
3739	* - https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql/sql_show.cc#L1799
3740	* - https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql/table.cc#L3525
3741	*
3742	* Unfortunately, SQLite doesn't validate the UTF-8 encoding, so other byte
3743	* sequences may come from SQLite as well: https://www.sqlite.org/invalidutf.html
3744	*
3745	* TODO: We may consider stripping invalid UTF-8 characters, but that's likely
3746	* to be a bigger project, as these can appear also in other contexts.
3747	*
3748	* @param string $utf8_literal The UTF-8 string literal to escape.
3749	* @return string The escaped string literal.
3750	*/
3751	private function quote_mysql_utf8_string_literal( string $utf8_literal ): string {
3752	/*
3753	* We can't use "addcslashes()" here, because it has an unusual handling
3754	* of the ASCII NULL character, escaping it to "\000" instead of "\0".
3755	*
3756	* It is important to use "strtr()" and not "str_replace()", because
3757	* "str_replace()" applies replacements one after another, modifying
3758	* intermediate changes rather than just the original string:
3759	*
3760	* - str_replace( [ 'a', 'b' ], [ 'b', 'c' ], 'ab' ); // 'cc' (bad)
3761	* - strtr( 'ab', [ 'a' => 'b', 'b' => 'c' ] ); // 'bc' (good)
3762	*/
3763	$backslash = chr( 92 );
3764	$replacements = array(
3765	"'" => "''", // A single quote character (').
3766	$backslash => $backslash . $backslash, // A backslash character (\).
3767	chr( 0 ) => $backslash . '0', // An ASCII NULL character (\0).
3768	chr( 10 ) => $backslash . 'n', // A newline (linefeed) character (\n).
3769	chr( 13 ) => $backslash . 'r', // A carriage return character (\r).
3770	);
3771	return "'" . strtr( $utf8_literal, $replacements ) . "'";
3772	}
3773
3774	/**
3775	* Clear the state of the driver.
3776	*/
3777	private function flush(): void {
3778	$this->last_mysql_query = '';
3779	$this->last_sqlite_queries = array();
3780	$this->last_result = null;
3781	$this->last_return_value = null;
3782	$this->is_readonly = false;
3783	}
3784
3785	/**
3786	* Set results of a query() call using fetched data.
3787	*
3788	* @param array $data The data to set.
3789	*/
3790	private function set_results_from_fetched_data( array $data ): void {
3791	$this->last_result = $data;
3792	$this->last_return_value = $this->last_result;
3793	}
3794
3795	/**
3796	* Set results of a query() call using the number of affected rows.
3797	*
3798	* @param int\|null $override Override the affected rows.
3799	*/
3800	private function set_result_from_affected_rows( ?int $override = null ): void {
3801	/*
3802	* SELECT CHANGES() is a workaround for the fact that $stmt->rowCount()
3803	* returns "0" (zero) with the SQLite driver at all times.
3804	* See: https://www.php.net/manual/en/pdostatement.rowcount.php
3805	*/
3806	if ( null === $override ) {
3807	$affected_rows = (int) $this->execute_sqlite_query( 'SELECT CHANGES()' )->fetch()[0];
3808	} else {
3809	$affected_rows = $override;
3810	}
3811	$this->last_result = $affected_rows;
3812	$this->last_return_value = $affected_rows;
3813	}
3814
3815	/**
3816	* Create a new SQLite driver exception.
3817	*
3818	* @param string $message The exception message.
3819	* @param int\|string $code The exception code. For PDO errors, a string representing SQLSTATE.
3820	* @param Throwable\|null $previous The previous exception.
3821	* @return WP_SQLite_Driver_Exception
3822	*/
3823	private function new_driver_exception(
3824	string $message,
3825	$code = 0,
3826	?Throwable $previous = null
3827	): WP_SQLite_Driver_Exception {
3828	return new WP_SQLite_Driver_Exception( $this, $message, $code, $previous );
3829	}
3830
3831	/**
3832	* Create a new invalid input exception.
3833	*
3834	* This exception can be used to mark cases that should never occur according
3835	* to the MySQL grammar. It may serve as an assertion that should never fail.
3836	*
3837	* @return WP_SQLite_Driver_Exception
3838	*/
3839	private function new_invalid_input_exception(): WP_SQLite_Driver_Exception {
3840	return new WP_SQLite_Driver_Exception( $this, 'MySQL query syntax error.' );
3841	}
3842
3843	/**
3844	* Create a new not supported exception.
3845	*
3846	* This exception can be used to mark MySQL constructs that are not supported.
3847	*
3848	* @param string $cause The cause, indicating which construct is not supported.
3849	* @return WP_SQLite_Driver_Exception
3850	*/
3851	private function new_not_supported_exception( string $cause ): WP_SQLite_Driver_Exception {
3852	return new WP_SQLite_Driver_Exception(
3853	$this,
3854	sprintf( 'MySQL query not supported. Cause: %s', $cause )
3855	);
3856	}
3857
3858	/**
3859	* Convert an information schema exception to a MySQL-like driver exception.
3860	*
3861	* This method is used to convert some information schema exceptions to the
3862	* corresponding MySQL exceptions, as they would be generated by PDO MySQL.
3863	* This conversion mirrors PDO's error messages and SQLSTATE codes.
3864	*
3865	* @param WP_SQLite_Information_Schema_Exception $e The information schema exception.
3866	* @return Throwable The converted exception, or the original
3867	* exception if no conversion was done.
3868	*/
3869	private function convert_information_schema_exception( WP_SQLite_Information_Schema_Exception $e ): Throwable {
3870	switch ( $e->get_type() ) {
3871	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_TABLE_NAME:
3872	return $this->new_driver_exception(
3873	sprintf(
3874	"SQLSTATE[42S01]: Base table or view already exists: 1050 Table '%s' already exists",
3875	$e->get_data()['table_name']
3876	),
3877	'42S01'
3878	);
3879	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_COLUMN_NAME:
3880	return $this->new_driver_exception(
3881	sprintf(
3882	"SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name '%s'",
3883	$e->get_data()['column_name']
3884	),
3885	'42S21'
3886	);
3887	case WP_SQLite_Information_Schema_Exception::TYPE_DUPLICATE_KEY_NAME:
3888	return $this->new_driver_exception(
3889	sprintf(
3890	"SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name '%s'",
3891	$e->get_data()['key_name']
3892	),
3893	'42S21'
3894	);
3895	default:
3896	return $e;
3897	}
3898	}
3899	}
3900