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

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

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