sqlite-database-integration / wp-includes / sqlite / class-wp-sqlite-translator.php

class-wp-sqlite-translator.php in SQLite Database Integration 2.1.15, at wp-includes/sqlite/class-wp-sqlite-translator.php

4,429 lines 127.1 KB

Raw Download Zip

1	<?php
2	/**
3	* The queries translator.
4	*
5	* @package wp-sqlite-integration
6	* @see https://github.com/phpmyadmin/sql-parser
7	*/
8
9	/**
10	* The queries translator class.
11	*/
12	class WP_SQLite_Translator {
13
14	const SQLITE_BUSY = 5;
15	const SQLITE_LOCKED = 6;
16
17	const DATA_TYPES_CACHE_TABLE = '_mysql_data_types_cache';
18
19	const CREATE_DATA_TYPES_CACHE_TABLE = 'CREATE TABLE IF NOT EXISTS _mysql_data_types_cache (
20	`table` TEXT NOT NULL,
21	`column_or_index` TEXT NOT NULL,
22	`mysql_type` TEXT NOT NULL,
23	PRIMARY KEY(`table`, `column_or_index`)
24	);';
25
26	/**
27	* We use the ASCII SUB character to escape LIKE literal _ and %
28	*/
29	const LIKE_ESCAPE_CHAR = "\x1a";
30
31	/**
32	* Class variable to reference to the PDO instance.
33	*
34	* @access private
35	*
36	* @var PDO object
37	*/
38	private $pdo;
39
40	/**
41	* The database version.
42	*
43	* This is used here to avoid PHP warnings in the health screen.
44	*
45	* @var string
46	*/
47	public $client_info = '';
48
49	/**
50	* How to translate field types from MySQL to SQLite.
51	*
52	* @var array
53	*/
54	private $field_types_translation = array(
55	'bit' => 'integer',
56	'bool' => 'integer',
57	'boolean' => 'integer',
58	'tinyint' => 'integer',
59	'smallint' => 'integer',
60	'mediumint' => 'integer',
61	'int' => 'integer',
62	'integer' => 'integer',
63	'bigint' => 'integer',
64	'float' => 'real',
65	'double' => 'real',
66	'decimal' => 'real',
67	'dec' => 'real',
68	'enum' => 'text',
69	'numeric' => 'real',
70	'fixed' => 'real',
71	'date' => 'text',
72	'datetime' => 'text',
73	'timestamp' => 'text',
74	'time' => 'text',
75	'year' => 'text',
76	'char' => 'text',
77	'varchar' => 'text',
78	'binary' => 'integer',
79	'varbinary' => 'blob',
80	'tinyblob' => 'blob',
81	'tinytext' => 'text',
82	'blob' => 'blob',
83	'text' => 'text',
84	'mediumblob' => 'blob',
85	'mediumtext' => 'text',
86	'longblob' => 'blob',
87	'longtext' => 'text',
88	'geomcollection' => 'text',
89	'geometrycollection' => 'text',
90	);
91
92	/**
93	* The MySQL to SQLite date formats translation.
94	*
95	* Maps MySQL formats to SQLite strftime() formats.
96	*
97	* For MySQL formats, see:
98	* * https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
99	*
100	* For SQLite formats, see:
101	* * https://www.sqlite.org/lang_datefunc.html
102	* * https://strftime.org/
103	*
104	* @var array
105	*/
106	private $mysql_date_format_to_sqlite_strftime = array(
107	'%a' => '%D',
108	'%b' => '%M',
109	'%c' => '%n',
110	'%D' => '%jS',
111	'%d' => '%d',
112	'%e' => '%j',
113	'%H' => '%H',
114	'%h' => '%h',
115	'%I' => '%h',
116	'%i' => '%M',
117	'%j' => '%z',
118	'%k' => '%G',
119	'%l' => '%g',
120	'%M' => '%F',
121	'%m' => '%m',
122	'%p' => '%A',
123	'%r' => '%h:%i:%s %A',
124	'%S' => '%s',
125	'%s' => '%s',
126	'%T' => '%H:%i:%s',
127	'%U' => '%W',
128	'%u' => '%W',
129	'%V' => '%W',
130	'%v' => '%W',
131	'%W' => '%l',
132	'%w' => '%w',
133	'%X' => '%Y',
134	'%x' => '%o',
135	'%Y' => '%Y',
136	'%y' => '%y',
137	);
138
139	/**
140	* Number of rows found by the last SELECT query.
141	*
142	* @var int
143	*/
144	private $last_select_found_rows;
145
146	/**
147	* Number of rows found by the last SQL_CALC_FOUND_ROW query.
148	*
149	* @var int integer
150	*/
151	private $last_sql_calc_found_rows = null;
152
153	/**
154	* The query rewriter.
155	*
156	* @var WP_SQLite_Query_Rewriter
157	*/
158	private $rewriter;
159
160	/**
161	* Last executed MySQL query.
162	*
163	* @var string
164	*/
165	public $mysql_query;
166
167	/**
168	* A list of executed SQLite queries.
169	*
170	* @var array
171	*/
172	public $executed_sqlite_queries = array();
173
174	/**
175	* The affected table name.
176	*
177	* @var array
178	*/
179	private $table_name = array();
180
181	/**
182	* The type of the executed query (SELECT, INSERT, etc).
183	*
184	* @var array
185	*/
186	private $query_type = array();
187
188	/**
189	* The columns to insert.
190	*
191	* @var array
192	*/
193	private $insert_columns = array();
194
195	/**
196	* Class variable to store the result of the query.
197	*
198	* @access private
199	*
200	* @var array reference to the PHP object
201	*/
202	private $results = null;
203
204	/**
205	* Class variable to check if there is an error.
206	*
207	* @var boolean
208	*/
209	public $is_error = false;
210
211	/**
212	* Class variable to store the file name and function to cause error.
213	*
214	* @access private
215	*
216	* @var array
217	*/
218	private $errors;
219
220	/**
221	* Class variable to store the error messages.
222	*
223	* @access private
224	*
225	* @var array
226	*/
227	private $error_messages = array();
228
229	/**
230	* Class variable to store the affected row id.
231	*
232	* @var int integer
233	* @access private
234	*/
235	private $last_insert_id;
236
237	/**
238	* Class variable to store the number of rows affected.
239	*
240	* @var int integer
241	*/
242	private $affected_rows;
243
244	/**
245	* Class variable to store the queried column info.
246	*
247	* @var array
248	*/
249	private $column_data;
250
251	/**
252	* Variable to emulate MySQL affected row.
253	*
254	* @var integer
255	*/
256	private $num_rows;
257
258	/**
259	* Return value from query().
260	*
261	* Each query has its own return value.
262	*
263	* @var mixed
264	*/
265	private $return_value;
266
267	/**
268	* Variable to keep track of nested transactions level.
269	*
270	* @var int
271	*/
272	private $transaction_level = 0;
273
274	/**
275	* Value returned by the last exec().
276	*
277	* @var mixed
278	*/
279	private $last_exec_returned;
280
281	/**
282	* The PDO fetch mode passed to query().
283	*
284	* @var mixed
285	*/
286	private $pdo_fetch_mode;
287
288	/**
289	* The last reserved keyword seen in an SQL query.
290	*
291	* @var mixed
292	*/
293	private $last_reserved_keyword;
294
295	/**
296	* True if a VACUUM operation should be done on shutdown,
297	* to handle OPTIMIZE TABLE and similar operations.
298	*
299	* @var bool
300	*/
301	private $vacuum_requested = false;
302
303	/**
304	* True if the present query is metadata
305	*
306	* @var bool
307	*/
308	private $is_information_schema_query = false;
309
310	/**
311	* True if a GROUP BY clause is detected.
312	*
313	* @var bool
314	*/
315	private $has_group_by = false;
316
317	/**
318	* 0 if no LIKE is in progress, otherwise counts nested parentheses.
319	*
320	* @todo A generic stack of expression would scale better. There's already a call_stack in WP_SQLite_Query_Rewriter.
321	* @var int
322	*/
323	private $like_expression_nesting = 0;
324
325	/**
326	* 0 if no LIKE is in progress, otherwise counts nested parentheses.
327	*
328	* @var int
329	*/
330	private $like_escape_count = 0;
331
332	/**
333	* Associative array with list of system (non-WordPress) tables.
334	*
335	* @var array [tablename => tablename]
336	*/
337	private $sqlite_system_tables = array();
338
339	/**
340	* The last error message from SQLite.
341	*
342	* @var string
343	*/
344	private $last_sqlite_error;
345
346	/**
347	* Constructor.
348	*
349	* Create PDO object, set user defined functions and initialize other settings.
350	* Don't use parent::__construct() because this class does not only returns
351	* PDO instance but many others jobs.
352	*
353	* @param PDO $pdo The PDO object.
354	*/
355	public function __construct( $pdo = null ) {
356	if ( ! $pdo ) {
357	if ( ! is_file( FQDB ) ) {
358	$this->prepare_directory();
359	}
360
361	$locked = false;
362	$status = 0;
363	$err_message = '';
364	do {
365	try {
366	$options = array(
367	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
368	PDO::ATTR_STRINGIFY_FETCHES => true,
369	PDO::ATTR_TIMEOUT => 5,
370	);
371
372	$dsn = 'sqlite:' . FQDB;
373	$pdo = new PDO( $dsn, null, null, $options ); // phpcs:ignore WordPress.DB.RestrictedClasses
374	} catch ( PDOException $ex ) {
375	$status = $ex->getCode();
376	if ( self::SQLITE_BUSY === $status \|\| self::SQLITE_LOCKED === $status ) {
377	$locked = true;
378	} else {
379	$err_message = $ex->getMessage();
380	}
381	}
382	} while ( $locked );
383
384	if ( $status > 0 ) {
385	$message = sprintf(
386	'<p>%s</p><p>%s</p><p>%s</p>',
387	'Database initialization error!',
388	"Code: $status",
389	"Error Message: $err_message"
390	);
391	$this->is_error = true;
392	$this->error_messages[] = $message;
393	return;
394	}
395	}
396
397	new WP_SQLite_PDO_User_Defined_Functions( $pdo );
398
399	// MySQL data comes across stringified by default.
400	$pdo->setAttribute( PDO::ATTR_STRINGIFY_FETCHES, true ); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
401	$pdo->query( WP_SQLite_Translator::CREATE_DATA_TYPES_CACHE_TABLE );
402
403	/*
404	* A list of system tables lets us emulate information_schema
405	* queries without returning extra tables.
406	*/
407	$this->sqlite_system_tables ['sqlite_sequence'] = 'sqlite_sequence';
408	$this->sqlite_system_tables [ self::DATA_TYPES_CACHE_TABLE ] = self::DATA_TYPES_CACHE_TABLE;
409
410	$this->pdo = $pdo;
411
412	// Fixes a warning in the site-health screen.
413	$this->client_info = SQLite3::version()['versionString'];
414
415	register_shutdown_function( array( $this, '__destruct' ) );
416
417	// WordPress happens to use no foreign keys.
418	$statement = $this->pdo->query( 'PRAGMA foreign_keys' );
419	// phpcs:ignore Universal.Operators.StrictComparisons.LooseEqual
420	if ( $statement->fetchColumn( 0 ) == '0' ) {
421	$this->pdo->query( 'PRAGMA foreign_keys = ON' );
422	}
423	$this->pdo->query( 'PRAGMA encoding="UTF-8";' );
424
425	$valid_journal_modes = array( 'DELETE', 'TRUNCATE', 'PERSIST', 'MEMORY', 'WAL', 'OFF' );
426	if ( defined( 'SQLITE_JOURNAL_MODE' ) && in_array( SQLITE_JOURNAL_MODE, $valid_journal_modes, true ) ) {
427	$this->pdo->query( 'PRAGMA journal_mode = ' . SQLITE_JOURNAL_MODE );
428	}
429	}
430
431	/**
432	* Destructor
433	*
434	* If SQLITE_MEM_DEBUG constant is defined, append information about
435	* memory usage into database/mem_debug.txt.
436	*
437	* This definition is changed since version 1.7.
438	*/
439	public function __destruct() {
440	if ( defined( 'SQLITE_MEM_DEBUG' ) && SQLITE_MEM_DEBUG ) {
441	$max = ini_get( 'memory_limit' );
442	if ( is_null( $max ) ) {
443	$message = sprintf(
444	'[%s] Memory_limit is not set in php.ini file.',
445	gmdate( 'Y-m-d H:i:s', $_SERVER['REQUEST_TIME'] )
446	);
447	error_log( $message );
448	return;
449	}
450	if ( stripos( $max, 'M' ) !== false ) {
451	$max = (int) $max * MB_IN_BYTES;
452	}
453	$peak = memory_get_peak_usage( true );
454	$used = round( (int) $peak / (int) $max * 100, 2 );
455	if ( $used > 90 ) {
456	$message = sprintf(
457	"[%s] Memory peak usage warning: %s %% used. (max: %sM, now: %sM)\n",
458	gmdate( 'Y-m-d H:i:s', $_SERVER['REQUEST_TIME'] ),
459	$used,
460	$max,
461	$peak
462	);
463	error_log( $message );
464	}
465	}
466	}
467
468	/**
469	* Get the PDO object.
470	*
471	* @return PDO
472	*/
473	public function get_pdo() {
474	return $this->pdo;
475	}
476
477	/**
478	* Method to return inserted row id.
479	*/
480	public function get_insert_id() {
481	return $this->last_insert_id;
482	}
483
484	/**
485	* Method to return the number of rows affected.
486	*/
487	public function get_affected_rows() {
488	return $this->affected_rows;
489	}
490
491	/**
492	* This method makes database directory and .htaccess file.
493	*
494	* It is executed only once when the installation begins.
495	*/
496	private function prepare_directory() {
497	global $wpdb;
498	$u = umask( 0000 );
499	if ( ! is_dir( FQDBDIR ) ) {
500	if ( ! @mkdir( FQDBDIR, 0704, true ) ) {
501	umask( $u );
502	wp_die( 'Unable to create the required directory! Please check your server settings.', 'Error!' );
503	}
504	}
505	if ( ! is_writable( FQDBDIR ) ) {
506	umask( $u );
507	$message = 'Unable to create a file in the directory! Please check your server settings.';
508	wp_die( $message, 'Error!' );
509	}
510	if ( ! is_file( FQDBDIR . '.htaccess' ) ) {
511	$fh = fopen( FQDBDIR . '.htaccess', 'w' );
512	if ( ! $fh ) {
513	umask( $u );
514	echo 'Unable to create a file in the directory! Please check your server settings.';
515
516	return false;
517	}
518	fwrite( $fh, 'DENY FROM ALL' );
519	fclose( $fh );
520	}
521	if ( ! is_file( FQDBDIR . 'index.php' ) ) {
522	$fh = fopen( FQDBDIR . 'index.php', 'w' );
523	if ( ! $fh ) {
524	umask( $u );
525	echo 'Unable to create a file in the directory! Please check your server settings.';
526
527	return false;
528	}
529	fwrite( $fh, '<?php // Silence is gold. ?>' );
530	fclose( $fh );
531	}
532	umask( $u );
533
534	return true;
535	}
536
537	/**
538	* Method to execute query().
539	*
540	* Divide the query types into seven different ones. That is to say:
541	*
542	* 1. SELECT SQL_CALC_FOUND_ROWS
543	* 2. INSERT
544	* 3. CREATE TABLE(INDEX)
545	* 4. ALTER TABLE
546	* 5. SHOW VARIABLES
547	* 6. DROP INDEX
548	* 7. THE OTHERS
549	*
550	* #1 is just a tricky play. See the private function handle_sql_count() in query.class.php.
551	* From #2 through #5 call different functions respectively.
552	* #6 call the ALTER TABLE query.
553	* #7 is a normal process: sequentially call prepare_query() and execute_query().
554	*
555	* #1 process has been changed since version 1.5.1.
556	*
557	* @param string $statement Full SQL statement string.
558	* @param int $mode Not used.
559	* @param array ...$fetch_mode_args Not used.
560	*
561	* @see PDO::query()
562	*
563	* @throws Exception If the query could not run.
564	* @throws PDOException If the translated query could not run.
565	*
566	* @return mixed according to the query type
567	*/
568	public function query( $statement, $mode = PDO::FETCH_OBJ, ...$fetch_mode_args ) { // phpcs:ignore WordPress.DB.RestrictedClasses
569	$this->flush();
570	if ( function_exists( 'apply_filters' ) ) {
571	/**
572	* Filters queries before they are translated and run.
573	*
574	* Return a non-null value to cause query() to return early with that result.
575	* Use this filter to intercept queries that don't work correctly in SQLite.
576	*
577	* From within the filter you can do
578	* function filter_sql ($result, $translator, $statement, $mode, $fetch_mode_args) {
579	* if ( intercepting this query ) {
580	* return $translator->execute_sqlite_query( $statement );
581	* }
582	* return $result;
583	* }
584	*
585	* @param null\|array $result Default null to continue with the query.
586	* @param object $translator The translator object. You can call $translator->execute_sqlite_query().
587	* @param string $statement The statement passed.
588	* @param int $mode Fetch mode: PDO::FETCH_OBJ, PDO::FETCH_CLASS, etc.
589	* @param array $fetch_mode_args Variable arguments passed to query.
590	*
591	* @returns null\|array Null to proceed, or an array containing a resultset.
592	* @since 2.1.0
593	*/
594	$pre = apply_filters( 'pre_query_sqlite_db', null, $this, $statement, $mode, $fetch_mode_args );
595	if ( null !== $pre ) {
596	return $pre;
597	}
598	}
599	$this->pdo_fetch_mode = $mode;
600	$this->mysql_query = $statement;
601	if (
602	preg_match( '/^\s*START TRANSACTION/i', $statement )
603	\|\| preg_match( '/^\s*BEGIN/i', $statement )
604	) {
605	return $this->begin_transaction();
606	}
607	if ( preg_match( '/^\s*COMMIT/i', $statement ) ) {
608	return $this->commit();
609	}
610	if ( preg_match( '/^\s*ROLLBACK/i', $statement ) ) {
611	return $this->rollback();
612	}
613
614	try {
615	// Perform all the queries in a nested transaction.
616	$this->begin_transaction();
617
618	do {
619	$error = null;
620	try {
621	$this->execute_mysql_query(
622	$statement
623	);
624	} catch ( PDOException $error ) {
625	if ( $error->getCode() !== self::SQLITE_BUSY ) {
626	throw $error;
627	}
628	}
629	} while ( $error );
630
631	if ( function_exists( 'do_action' ) ) {
632	/**
633	* Notifies that a query has been translated and executed.
634	*
635	* @param string $query The executed SQL query.
636	* @param string $query_type The type of the SQL query (e.g. SELECT, INSERT, UPDATE, DELETE).
637	* @param string $table_name The name of the table affected by the SQL query.
638	* @param array $insert_columns The columns affected by the INSERT query (if applicable).
639	* @param int $last_insert_id The ID of the last inserted row (if applicable).
640	* @param int $affected_rows The number of affected rows (if applicable).
641	*
642	* @since 0.1.0
643	*/
644	do_action(
645	'sqlite_translated_query_executed',
646	$this->mysql_query,
647	$this->query_type,
648	$this->table_name,
649	$this->insert_columns,
650	$this->last_insert_id,
651	$this->affected_rows
652	);
653	}
654
655	// Commit the nested transaction.
656	$this->commit();
657
658	return $this->return_value;
659	} catch ( Exception $err ) {
660	// Rollback the nested transaction.
661	$this->rollback();
662	if ( defined( 'PDO_DEBUG' ) && PDO_DEBUG === true ) {
663	throw $err;
664	}
665	return $this->handle_error( $err );
666	}
667	}
668
669	/**
670	* Method to return the queried column names.
671	*
672	* These data are meaningless for SQLite. So they are dummy emulating
673	* MySQL columns data.
674	*
675	* @return array\|null of the object
676	*/
677	public function get_columns() {
678	if ( ! empty( $this->results ) ) {
679	$primary_key = array(
680	'meta_id',
681	'comment_ID',
682	'link_ID',
683	'option_id',
684	'blog_id',
685	'option_name',
686	'ID',
687	'term_id',
688	'object_id',
689	'term_taxonomy_id',
690	'umeta_id',
691	'id',
692	);
693	$unique_key = array( 'term_id', 'taxonomy', 'slug' );
694	$data = array(
695	'name' => '', // Column name.
696	'table' => '', // Table name.
697	'max_length' => 0, // Max length of the column.
698	'not_null' => 1, // 1 if not null.
699	'primary_key' => 0, // 1 if column has primary key.
700	'unique_key' => 0, // 1 if column has unique key.
701	'multiple_key' => 0, // 1 if column doesn't have unique key.
702	'numeric' => 0, // 1 if column has numeric value.
703	'blob' => 0, // 1 if column is blob.
704	'type' => '', // Type of the column.
705	'int' => 0, // 1 if column is int integer.
706	'zerofill' => 0, // 1 if column is zero-filled.
707	);
708	$table_name = '';
709	$sql = '';
710	$query = end( $this->executed_sqlite_queries );
711	if ( $query ) {
712	$sql = $query['sql'];
713	}
714	if ( preg_match( '/\sFROM\s(.)?\s/i', $sql, $match ) ) {
715	$table_name = trim( $match[1] );
716	}
717	foreach ( $this->results[0] as $key => $value ) {
718	$data['name'] = $key;
719	$data['table'] = $table_name;
720	if ( in_array( $key, $primary_key, true ) ) {
721	$data['primary_key'] = 1;
722	} elseif ( in_array( $key, $unique_key, true ) ) {
723	$data['unique_key'] = 1;
724	} else {
725	$data['multiple_key'] = 1;
726	}
727	$this->column_data[] = json_decode( json_encode( $data ) );
728
729	// Reset data for next iteration.
730	$data['name'] = '';
731	$data['table'] = '';
732	$data['primary_key'] = 0;
733	$data['unique_key'] = 0;
734	$data['multiple_key'] = 0;
735	}
736
737	return $this->column_data;
738	}
739	return null;
740	}
741
742	/**
743	* Method to return the queried result data.
744	*
745	* @return mixed
746	*/
747	public function get_query_results() {
748	return $this->results;
749	}
750
751	/**
752	* Method to return the number of rows from the queried result.
753	*/
754	public function get_num_rows() {
755	return $this->num_rows;
756	}
757
758	/**
759	* Method to return the queried results according to the query types.
760	*
761	* @return mixed
762	*/
763	public function get_return_value() {
764	return $this->return_value;
765	}
766
767	/**
768	* Executes a MySQL query in SQLite.
769	*
770	* @param string $query The query.
771	*
772	* @throws Exception If the query is not supported.
773	*/
774	private function execute_mysql_query( $query ) {
775	$tokens = ( new WP_SQLite_Lexer( $query ) )->tokens;
776
777	// SQLite does not support CURRENT_TIMESTAMP() calls with parentheses.
778	// Since CURRENT_TIMESTAMP() can appear in most types of SQL queries,
779	// let's remove the parentheses globally before further processing.
780	foreach ( $tokens as $i => $token ) {
781	if ( WP_SQLite_Token::TYPE_KEYWORD === $token->type && 'CURRENT_TIMESTAMP' === $token->keyword ) {
782	$paren_open = $tokens[ $i + 1 ] ?? null;
783	$paren_close = $tokens[ $i + 2 ] ?? null;
784	if ( WP_SQLite_Token::TYPE_OPERATOR === $paren_open->type && '(' === $paren_open->value
785	&& WP_SQLite_Token::TYPE_OPERATOR === $paren_close->type && ')' === $paren_close->value ) {
786	unset( $tokens[ $i + 1 ], $tokens[ $i + 2 ] );
787	}
788	}
789	}
790	$tokens = array_values( $tokens );
791
792	$this->rewriter = new WP_SQLite_Query_Rewriter( $tokens );
793	$this->query_type = $this->rewriter->peek()->value;
794
795	switch ( $this->query_type ) {
796	case 'ALTER':
797	$this->execute_alter();
798	break;
799
800	case 'CREATE':
801	$this->execute_create();
802	break;
803
804	case 'SELECT':
805	$this->execute_select();
806	break;
807
808	case 'INSERT':
809	case 'REPLACE':
810	$this->execute_insert_or_replace();
811	break;
812
813	case 'UPDATE':
814	$this->execute_update();
815	break;
816
817	case 'DELETE':
818	$this->execute_delete();
819	break;
820
821	case 'CALL':
822	case 'SET':
823	/*
824	* It would be lovely to support at least SET autocommit,
825	* but I don't think that is even possible with SQLite.
826	*/
827	$this->results = 0;
828	break;
829
830	case 'TRUNCATE':
831	$this->execute_truncate();
832	break;
833
834	case 'BEGIN':
835	case 'START TRANSACTION':
836	$this->results = $this->begin_transaction();
837	break;
838
839	case 'COMMIT':
840	$this->results = $this->commit();
841	break;
842
843	case 'ROLLBACK':
844	$this->results = $this->rollback();
845	break;
846
847	case 'DROP':
848	$this->execute_drop();
849	break;
850
851	case 'SHOW':
852	$this->execute_show();
853	break;
854
855	case 'DESCRIBE':
856	$this->execute_describe();
857	break;
858
859	case 'CHECK':
860	$this->execute_check();
861	break;
862
863	case 'OPTIMIZE':
864	case 'REPAIR':
865	case 'ANALYZE':
866	$this->execute_optimize( $this->query_type );
867	break;
868
869	default:
870	throw new Exception( 'Unknown query type: ' . $this->query_type );
871	}
872	}
873
874	/**
875	* Executes a MySQL CREATE TABLE query in SQLite.
876	*
877	* @throws Exception If the query is not supported.
878	*/
879	private function execute_create_table() {
880	$table = $this->parse_create_table();
881
882	$definitions = array();
883	$on_updates = array();
884	foreach ( $table->fields as $field ) {
885	/*
886	* Do not include the inline PRIMARY KEY definition
887	* if there is more than one primary key.
888	*/
889	if ( $field->primary_key && count( $table->primary_key ) > 1 ) {
890	$field->primary_key = false;
891	}
892	if ( $field->auto_increment && count( $table->primary_key ) > 1 ) {
893	throw new Exception( 'Cannot combine AUTOINCREMENT and multiple primary keys in SQLite' );
894	}
895
896	$definitions[] = $this->make_sqlite_field_definition( $field );
897	if ( $field->on_update ) {
898	$on_updates[ $field->name ] = $field->on_update;
899	}
900
901	$this->update_data_type_cache(
902	$table->name,
903	$field->name,
904	$field->mysql_data_type
905	);
906	}
907
908	if ( count( $table->primary_key ) > 1 ) {
909	$definitions[] = 'PRIMARY KEY ("' . implode( '", "', $table->primary_key ) . '")';
910	}
911
912	$create_query = (
913	$table->create_table .
914	'"' . $table->name . '" (' . "\n" .
915	implode( ",\n", $definitions ) .
916	')'
917	);
918
919	$if_not_exists = preg_match( '/\bIF\s+NOT\s+EXISTS\b/i', $create_query ) ? 'IF NOT EXISTS' : '';
920
921	$this->execute_sqlite_query( $create_query );
922	$this->results = $this->last_exec_returned;
923	$this->return_value = $this->results;
924
925	foreach ( $table->constraints as $constraint ) {
926	$index_type = $this->mysql_index_type_to_sqlite_type( $constraint->value );
927	$unique = '';
928	if ( 'UNIQUE INDEX' === $index_type ) {
929	$unique = 'UNIQUE ';
930	}
931	$index_name = $this->generate_index_name( $table->name, $constraint->name );
932	$this->execute_sqlite_query(
933	"CREATE $unique INDEX $if_not_exists \"$index_name\" ON \"{$table->name}\" (\"" . implode( '", "', $constraint->columns ) . '")'
934	);
935	$this->update_data_type_cache(
936	$table->name,
937	$index_name,
938	$constraint->value
939	);
940	}
941
942	foreach ( $on_updates as $column => $on_update ) {
943	$this->add_column_on_update_current_timestamp( $table->name, $column );
944	}
945	}
946
947	/**
948	* Parse the CREATE TABLE query.
949	*
950	* @return stdClass Structured data.
951	*/
952	private function parse_create_table() {
953	$this->rewriter = clone $this->rewriter;
954	$result = new stdClass();
955	$result->create_table = null;
956	$result->name = null;
957	$result->fields = array();
958	$result->constraints = array();
959	$result->primary_key = array();
960
961	/*
962	* The query starts with CREATE TABLE [IF NOT EXISTS].
963	* Consume everything until the table name.
964	*/
965	while ( true ) {
966	$token = $this->rewriter->consume();
967	if ( ! $token ) {
968	break;
969	}
970	// The table name is the first non-keyword token.
971	if ( WP_SQLite_Token::TYPE_KEYWORD !== $token->type ) {
972	// Store the table name for later.
973	$result->name = $this->normalize_column_name( $token->value );
974
975	// Drop the table name and store the CREATE TABLE command.
976	$this->rewriter->drop_last();
977	$result->create_table = $this->rewriter->get_updated_query();
978	break;
979	}
980	}
981
982	/*
983	* Move to the opening parenthesis:
984	* CREATE TABLE wp_options (
985	* ^ here.
986	*/
987	$this->rewriter->skip(
988	array(
989	'type' => WP_SQLite_Token::TYPE_OPERATOR,
990	'value' => '(',
991	)
992	);
993
994	/*
995	* We're in the table definition now.
996	* Read everything until the closing parenthesis.
997	*/
998	$declarations_depth = $this->rewriter->depth;
999	do {
1000	/*
1001	* We want to capture a rewritten line of the query.
1002	* Let's clear any data we might have captured so far.
1003	*/
1004	$this->rewriter->replace_all( array() );
1005
1006	/*
1007	* Decide how to parse the current line. We expect either:
1008	*
1009	* Field definition, e.g.:
1010	* `my_field` varchar(255) NOT NULL DEFAULT 'foo'
1011	* Constraint definition, e.g.:
1012	* PRIMARY KEY (`my_field`)
1013	*
1014	* Lexer does not seem to reliably understand whether the
1015	* first token is a field name or a reserved keyword, so
1016	* instead we'll check whether the second non-whitespace
1017	* token is a data type.
1018	*/
1019	$second_token = $this->rewriter->peek_nth( 2 );
1020
1021	if ( $second_token->matches(
1022	WP_SQLite_Token::TYPE_KEYWORD,
1023	WP_SQLite_Token::FLAG_KEYWORD_DATA_TYPE
1024	) ) {
1025	$result->fields[] = $this->parse_mysql_create_table_field();
1026	} else {
1027	$result->constraints[] = $this->parse_mysql_create_table_constraint();
1028	}
1029
1030	/*
1031	* If we're back at the initial depth, we're done.
1032	* Also, MySQL supports a trailing comma – if we see one,
1033	* then we're also done.
1034	*/
1035	} while (
1036	$token
1037	&& $this->rewriter->depth >= $declarations_depth
1038	&& $this->rewriter->peek()->token !== ')'
1039	);
1040
1041	// Merge all the definitions of the primary key.
1042	foreach ( $result->constraints as $k => $constraint ) {
1043	if ( 'PRIMARY' === $constraint->value ) {
1044	$result->primary_key = array_merge(
1045	$result->primary_key,
1046	$constraint->columns
1047	);
1048	unset( $result->constraints[ $k ] );
1049	}
1050	}
1051
1052	// Inline primary key in a field definition.
1053	foreach ( $result->fields as $k => $field ) {
1054	if ( $field->primary_key ) {
1055	$result->primary_key[] = $field->name;
1056	} elseif ( in_array( $field->name, $result->primary_key, true ) ) {
1057	$field->primary_key = true;
1058	}
1059	}
1060
1061	// Remove duplicates.
1062	$result->primary_key = array_unique( $result->primary_key );
1063
1064	return $result;
1065	}
1066
1067	/**
1068	* Parses a CREATE TABLE query.
1069	*
1070	* @throws Exception If the query is not supported.
1071	*
1072	* @return stdClass
1073	*/
1074	private function parse_mysql_create_table_field() {
1075	$result = new stdClass();
1076	$result->name = '';
1077	$result->sqlite_data_type = '';
1078	$result->not_null = false;
1079	$result->default = false;
1080	$result->auto_increment = false;
1081	$result->primary_key = false;
1082	$result->on_update = false;
1083
1084	$field_name_token = $this->rewriter->skip(); // Field name.
1085	$this->rewriter->add( new WP_SQLite_Token( "\n", WP_SQLite_Token::TYPE_WHITESPACE ) );
1086	$result->name = $this->normalize_column_name( $field_name_token->value );
1087
1088	$definition_depth = $this->rewriter->depth;
1089
1090	$skip_mysql_data_type_parts = $this->skip_mysql_data_type();
1091	$result->sqlite_data_type = $skip_mysql_data_type_parts[0];
1092	$result->mysql_data_type = $skip_mysql_data_type_parts[1];
1093
1094	// Look for the NOT NULL, PRIMARY KEY, DEFAULT, and AUTO_INCREMENT flags.
1095	while ( true ) {
1096	$token = $this->rewriter->skip();
1097	if ( ! $token ) {
1098	break;
1099	}
1100	if ( $token->matches(
1101	WP_SQLite_Token::TYPE_KEYWORD,
1102	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1103	array( 'NOT NULL' )
1104	) ) {
1105	$result->not_null = true;
1106	continue;
1107	}
1108
1109	if ( $token->matches(
1110	WP_SQLite_Token::TYPE_KEYWORD,
1111	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1112	array( 'PRIMARY KEY' )
1113	) ) {
1114	$result->primary_key = true;
1115	continue;
1116	}
1117
1118	if ( $token->matches(
1119	WP_SQLite_Token::TYPE_KEYWORD,
1120	null,
1121	array( 'AUTO_INCREMENT' )
1122	) ) {
1123	$result->primary_key = true;
1124	$result->auto_increment = true;
1125	continue;
1126	}
1127
1128	if ( $token->matches(
1129	WP_SQLite_Token::TYPE_KEYWORD,
1130	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
1131	array( 'DEFAULT' )
1132	) ) {
1133	$result->default = $this->rewriter->consume()->token;
1134	continue;
1135	}
1136
1137	if (
1138	$token->matches(
1139	WP_SQLite_Token::TYPE_KEYWORD,
1140	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1141	array( 'ON UPDATE' )
1142	) && $this->rewriter->peek()->matches(
1143	WP_SQLite_Token::TYPE_KEYWORD,
1144	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1145	array( 'CURRENT_TIMESTAMP' )
1146	)
1147	) {
1148	$this->rewriter->skip();
1149	$result->on_update = true;
1150	continue;
1151	}
1152
1153	if ( $this->is_create_table_field_terminator( $token, $definition_depth ) ) {
1154	$this->rewriter->add( $token );
1155	break;
1156	}
1157	}
1158
1159	return $result;
1160	}
1161
1162	/**
1163	* Translate field definitions.
1164	*
1165	* @param stdClass $field Field definition.
1166	*
1167	* @return string
1168	*/
1169	private function make_sqlite_field_definition( $field ) {
1170	$definition = '"' . $field->name . '" ' . $field->sqlite_data_type;
1171	if ( $field->auto_increment ) {
1172	$definition .= ' PRIMARY KEY AUTOINCREMENT';
1173	} elseif ( $field->primary_key ) {
1174	$definition .= ' PRIMARY KEY ';
1175	}
1176	if ( $field->not_null ) {
1177	$definition .= ' NOT NULL';
1178	}
1179	/**
1180	* WPDB removes the STRICT_TRANS_TABLES mode from MySQL queries.
1181	* This mode allows the use of `NULL` when NOT NULL is set on a column that falls back to DEFAULT.
1182	* SQLite does not support this behavior, so we need to add the `ON CONFLICT REPLACE` clause to the column definition.
1183	*/
1184	if ( $field->not_null ) {
1185	$definition .= ' ON CONFLICT REPLACE';
1186	}
1187	/**
1188	* The value of DEFAULT can be NULL. PHP would print this as an empty string, so we need a special case for it.
1189	*/
1190	if ( null === $field->default ) {
1191	$definition .= ' DEFAULT NULL';
1192	} elseif ( false !== $field->default ) {
1193	$definition .= ' DEFAULT ' . $field->default;
1194	} elseif ( $field->not_null ) {
1195	/**
1196	* If the column is NOT NULL, we need to provide a default value to match WPDB behavior caused by removing the STRICT_TRANS_TABLES mode.
1197	*/
1198	if ( 'text' === $field->sqlite_data_type ) {
1199	$definition .= ' DEFAULT \'\'';
1200	} elseif ( in_array( $field->sqlite_data_type, array( 'integer', 'real' ), true ) ) {
1201	$definition .= ' DEFAULT 0';
1202	}
1203	}
1204
1205	/*
1206	* In MySQL, text fields are case-insensitive by default.
1207	* COLLATE NOCASE emulates the same behavior in SQLite.
1208	*/
1209	if ( 'text' === $field->sqlite_data_type ) {
1210	$definition .= ' COLLATE NOCASE';
1211	}
1212	return $definition;
1213	}
1214
1215	/**
1216	* Parses a CREATE TABLE constraint.
1217	*
1218	* @throws Exception If the query is not supported.
1219	*
1220	* @return stdClass
1221	*/
1222	private function parse_mysql_create_table_constraint() {
1223	$result = new stdClass();
1224	$result->name = '';
1225	$result->value = '';
1226	$result->columns = array();
1227
1228	$definition_depth = $this->rewriter->depth;
1229	$constraint = $this->rewriter->peek();
1230	if ( ! $constraint->matches( WP_SQLite_Token::TYPE_KEYWORD ) ) {
1231	/*
1232	* Not a constraint declaration, but we're not finished
1233	* with the table declaration yet.
1234	*/
1235	throw new Exception( 'Unexpected token in MySQL query: ' . $this->rewriter->peek()->value );
1236	}
1237
1238	$result->value = $this->normalize_mysql_index_type( $constraint->value );
1239	if ( $result->value ) {
1240	$this->rewriter->skip(); // Constraint type.
1241
1242	$name = $this->rewriter->peek();
1243	if ( '(' !== $name->token && 'PRIMARY' !== $result->value ) {
1244	$result->name = $this->rewriter->skip()->value;
1245	}
1246
1247	$constraint_depth = $this->rewriter->depth;
1248	$this->rewriter->skip(); // `(`
1249	do {
1250	$result->columns[] = $this->normalize_column_name( $this->rewriter->skip()->value );
1251	$paren_maybe = $this->rewriter->peek();
1252	if ( $paren_maybe && '(' === $paren_maybe->token ) {
1253	$this->rewriter->skip();
1254	$this->rewriter->skip();
1255	$this->rewriter->skip();
1256	}
1257	$this->rewriter->skip(); // `,` or `)`
1258	} while ( $this->rewriter->depth > $constraint_depth );
1259
1260	if ( empty( $result->name ) ) {
1261	$result->name = implode( '_', $result->columns );
1262	}
1263	}
1264
1265	do {
1266	$token = $this->rewriter->skip();
1267	} while ( ! $this->is_create_table_field_terminator( $token, $definition_depth ) );
1268
1269	return $result;
1270	}
1271
1272	/**
1273	* Checks if the current token is the terminator of a CREATE TABLE field.
1274	*
1275	* @param WP_SQLite_Token $token The current token.
1276	* @param int $definition_depth The initial depth.
1277	* @param int\|null $current_depth The current depth.
1278	*
1279	* @return bool
1280	*/
1281	private function is_create_table_field_terminator( $token, $definition_depth, $current_depth = null ) {
1282	if ( null === $current_depth ) {
1283	$current_depth = $this->rewriter->depth;
1284	}
1285	return (
1286	// Reached the end of the query.
1287	null === $token
1288
1289	// The field-terminating ",".
1290	\|\| (
1291	$current_depth === $definition_depth &&
1292	WP_SQLite_Token::TYPE_OPERATOR === $token->type &&
1293	',' === $token->value
1294	)
1295
1296	// The definitions-terminating ")".
1297	\|\| $current_depth === $definition_depth - 1
1298
1299	// The query-terminating ";".
1300	\|\| (
1301	WP_SQLite_Token::TYPE_DELIMITER === $token->type &&
1302	';' === $token->value
1303	)
1304	);
1305	}
1306
1307	/**
1308	* Executes a DELETE statement.
1309	*
1310	* @throws Exception If the table could not be found.
1311	*/
1312	private function execute_delete() {
1313	$this->rewriter->consume(); // DELETE.
1314
1315	// Process expressions and extract bound parameters.
1316	$params = array();
1317	while ( true ) {
1318	$token = $this->rewriter->peek();
1319	if ( ! $token ) {
1320	break;
1321	}
1322
1323	$this->remember_last_reserved_keyword( $token );
1324
1325	if (
1326	$this->extract_bound_parameter( $token, $params )
1327	\|\| $this->translate_expression( $token )
1328	) {
1329	continue;
1330	}
1331
1332	$this->rewriter->consume();
1333	}
1334	$this->rewriter->consume_all();
1335
1336	$updated_query = $this->rewriter->get_updated_query();
1337
1338	// Perform DELETE-specific translations.
1339
1340	// Naive rewriting of DELETE JOIN query.
1341	// @TODO: Actually rewrite the query instead of using a hardcoded workaround.
1342	if ( str_contains( $updated_query, ' JOIN ' ) ) {
1343	$table_prefix = isset( $GLOBALS['table_prefix'] ) ? $GLOBALS['table_prefix'] : 'wp_';
1344	$this->execute_sqlite_query(
1345	"DELETE FROM {$table_prefix}options WHERE option_id IN (SELECT MIN(option_id) FROM {$table_prefix}options GROUP BY option_name HAVING COUNT(*) > 1)"
1346	);
1347	$this->set_result_from_affected_rows();
1348	return;
1349	}
1350
1351	$rewriter = new WP_SQLite_Query_Rewriter( $this->rewriter->output_tokens );
1352
1353	$comma = $rewriter->peek(
1354	array(
1355	'type' => WP_SQLite_Token::TYPE_OPERATOR,
1356	'value' => ',',
1357	)
1358	);
1359	$from = $rewriter->peek(
1360	array(
1361	'type' => WP_SQLite_Token::TYPE_KEYWORD,
1362	'value' => 'FROM',
1363	)
1364	);
1365	// The DELETE query targets a single table if there's no comma before the FROM.
1366	if ( ! $comma \|\| ! $from \|\| $comma->position >= $from->position ) {
1367	$this->execute_sqlite_query(
1368	$updated_query,
1369	$params
1370	);
1371	$this->set_result_from_affected_rows();
1372	return;
1373	}
1374
1375	// The DELETE query targets multiple tables – rewrite it into a
1376	// SELECT to fetch the IDs of the rows to delete, then delete them
1377	// using a separate DELETE query.
1378
1379	$this->table_name = $rewriter->skip()->value;
1380	$rewriter->add( new WP_SQLite_Token( 'SELECT', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ) );
1381
1382	/*
1383	* Get table name.
1384	*/
1385	$from = $rewriter->peek(
1386	array(
1387	'type' => WP_SQLite_Token::TYPE_KEYWORD,
1388	'value' => 'FROM',
1389	)
1390	);
1391	$index = array_search( $from, $rewriter->input_tokens, true );
1392	for ( $i = $index + 1; $i < $rewriter->max; $i++ ) {
1393	// Assume the table name is the first token after FROM.
1394	if ( ! $rewriter->input_tokens[ $i ]->is_semantically_void() ) {
1395	$this->table_name = $rewriter->input_tokens[ $i ]->value;
1396	break;
1397	}
1398	}
1399	if ( ! $this->table_name ) {
1400	throw new Exception( 'Could not find table name for dual delete query.' );
1401	}
1402
1403	/*
1404	* Now, let's figure out the primary key name.
1405	* This assumes that all listed table names are the same.
1406	*/
1407	$q = $this->execute_sqlite_query( 'SELECT l.name FROM pragma_table_info("' . $this->table_name . '") as l WHERE l.pk = 1;' );
1408	$pk_name = $q->fetch()['name'];
1409
1410	/*
1411	* Good, we can finally create the SELECT query.
1412	* Let's rewrite DELETE a, b FROM ... to SELECT a.id, b.id FROM ...
1413	*/
1414	$alias_nb = 0;
1415	while ( true ) {
1416	$token = $rewriter->consume();
1417	if ( WP_SQLite_Token::TYPE_KEYWORD === $token->type && 'FROM' === $token->value ) {
1418	break;
1419	}
1420
1421	/*
1422	* Between DELETE and FROM we only expect commas and table aliases.
1423	* If it's not a comma, it must be a table alias.
1424	*/
1425	if ( ',' !== $token->value ) {
1426	// Insert .id AS id_1 after the table alias.
1427	$rewriter->add_many(
1428	array(
1429	new WP_SQLite_Token( '.', WP_SQLite_Token::TYPE_OPERATOR, WP_SQLite_Token::FLAG_OPERATOR_SQL ),
1430	new WP_SQLite_Token( $pk_name, WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
1431	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1432	new WP_SQLite_Token( 'AS', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
1433	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1434	new WP_SQLite_Token( 'id_' . $alias_nb, WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
1435	)
1436	);
1437	++$alias_nb;
1438	}
1439	}
1440	$rewriter->consume_all();
1441
1442	// Select the IDs to delete.
1443	$select = $rewriter->get_updated_query();
1444	$stmt = $this->execute_sqlite_query( $select );
1445	$stmt->execute( $params );
1446	$rows = $stmt->fetchAll();
1447	$ids_to_delete = array();
1448	foreach ( $rows as $id ) {
1449	$ids_to_delete[] = $id['id_0'];
1450	$ids_to_delete[] = $id['id_1'];
1451	}
1452
1453	$query = (
1454	count( $ids_to_delete )
1455	? "DELETE FROM {$this->table_name} WHERE {$pk_name} IN (" . implode( ',', $ids_to_delete ) . ')'
1456	: "DELETE FROM {$this->table_name} WHERE 0=1"
1457	);
1458	$this->execute_sqlite_query( $query );
1459	$this->set_result_from_affected_rows(
1460	count( $ids_to_delete )
1461	);
1462	}
1463
1464	/**
1465	* Executes a SELECT statement.
1466	*/
1467	private function execute_select() {
1468	$this->rewriter->consume(); // SELECT.
1469
1470	$params = array();
1471	$table_name = null;
1472	$has_sql_calc_found_rows = false;
1473
1474	// Consume and record the table name.
1475	while ( true ) {
1476	$token = $this->rewriter->peek();
1477	if ( ! $token ) {
1478	break;
1479	}
1480
1481	$this->remember_last_reserved_keyword( $token );
1482
1483	if ( ! $table_name ) {
1484	$this->table_name = $this->peek_table_name( $token );
1485	$table_name = $this->peek_table_name( $token );
1486	}
1487
1488	if ( $this->skip_sql_calc_found_rows( $token ) ) {
1489	$has_sql_calc_found_rows = true;
1490	continue;
1491	}
1492
1493	if (
1494	$this->extract_bound_parameter( $token, $params )
1495	\|\| $this->translate_expression( $token )
1496	) {
1497	continue;
1498	}
1499
1500	if ( $this->skip_index_hint() ) {
1501	continue;
1502	}
1503
1504	$this->rewriter->consume();
1505	}
1506	$this->rewriter->consume_all();
1507
1508	$updated_query = $this->rewriter->get_updated_query();
1509
1510	if ( $table_name && str_starts_with( strtolower( $table_name ), 'information_schema' ) ) {
1511	$this->is_information_schema_query = true;
1512	$updated_query = $this->get_information_schema_query( $updated_query );
1513	$params = array();
1514	} elseif (
1515	// Examples: @@SESSION.sql_mode, @@GLOBAL.max_allowed_packet, @@character_set_client
1516	preg_match( '/@@((SESSION\|GLOBAL)\s\.\s)?\w+\b/i', $updated_query ) === 1 \|\|
1517	strpos( $updated_query, 'CONVERT( ' ) !== false
1518	) {
1519	/*
1520	* If the query contains a function that is not supported by SQLite,
1521	* return a dummy select. This check must be done after the query
1522	* has been rewritten to use parameters to avoid false positives
1523	* on queries such as `SELECT * FROM table WHERE field='CONVERT('`.
1524	*/
1525	$updated_query = 'SELECT 1=0';
1526	$params = array();
1527	} elseif ( $has_sql_calc_found_rows ) {
1528	// Emulate SQL_CALC_FOUND_ROWS for now.
1529	$query = $updated_query;
1530	// We make the data for next SELECT FOUND_ROWS() statement.
1531	$unlimited_query = preg_replace( '/\\bLIMIT\\s\d+(?:\s,\s\d+)?$/imsx', '', $query );
1532	$stmt = $this->execute_sqlite_query( $unlimited_query );
1533	$stmt->execute( $params );
1534	$this->last_sql_calc_found_rows = count( $stmt->fetchAll() );
1535	}
1536
1537	// Emulate FOUND_ROWS() by counting the rows in the result set.
1538	if ( strpos( $updated_query, 'FOUND_ROWS(' ) !== false ) {
1539	$last_found_rows = ( $this->last_sql_calc_found_rows ? $this->last_sql_calc_found_rows : 0 ) . '';
1540	$updated_query = "SELECT {$last_found_rows} AS `FOUND_ROWS()`";
1541	}
1542
1543	$stmt = $this->execute_sqlite_query( $updated_query, $params );
1544	if ( $this->is_information_schema_query ) {
1545	$this->set_results_from_fetched_data(
1546	$this->strip_sqlite_system_tables(
1547	$stmt->fetchAll( $this->pdo_fetch_mode )
1548	)
1549	);
1550	} else {
1551	$this->set_results_from_fetched_data(
1552	$stmt->fetchAll( $this->pdo_fetch_mode )
1553	);
1554	}
1555	}
1556
1557	/**
1558	* Ignores the FORCE INDEX clause
1559	*
1560	* USE {INDEX\|KEY}
1561	* [FOR {JOIN\|ORDER BY\|GROUP BY}] ([index_list])
1562	* \| {IGNORE\|FORCE} {INDEX\|KEY}
1563	* [FOR {JOIN\|ORDER BY\|GROUP BY}] (index_list)
1564	*
1565	* @see https://dev.mysql.com/doc/refman/8.3/en/index-hints.html
1566	* @return bool
1567	*/
1568	private function skip_index_hint() {
1569	$force = $this->rewriter->peek();
1570	if ( ! $force \|\| ! $force->matches(
1571	WP_SQLite_Token::TYPE_KEYWORD,
1572	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1573	array( 'USE', 'FORCE', 'IGNORE' )
1574	) ) {
1575	return false;
1576	}
1577
1578	$index = $this->rewriter->peek_nth( 2 );
1579	if ( ! $index \|\| ! $index->matches(
1580	WP_SQLite_Token::TYPE_KEYWORD,
1581	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1582	array( 'INDEX', 'KEY' )
1583	) ) {
1584	return false;
1585	}
1586
1587	$this->rewriter->skip(); // USE, FORCE, IGNORE.
1588	$this->rewriter->skip(); // INDEX, KEY.
1589
1590	$maybe_for = $this->rewriter->peek();
1591	if ( $maybe_for && $maybe_for->matches(
1592	WP_SQLite_Token::TYPE_KEYWORD,
1593	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1594	array( 'FOR' )
1595	) ) {
1596	$this->rewriter->skip(); // FOR.
1597
1598	$token = $this->rewriter->peek();
1599	if ( $token && $token->matches(
1600	WP_SQLite_Token::TYPE_KEYWORD,
1601	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
1602	array( 'JOIN', 'ORDER', 'GROUP' )
1603	) ) {
1604	$this->rewriter->skip(); // JOIN, ORDER, GROUP.
1605	if ( 'BY' === strtoupper( $this->rewriter->peek()->value ?? '' ) ) {
1606	$this->rewriter->skip(); // BY.
1607	}
1608	}
1609	}
1610
1611	// Skip everything until the closing parenthesis.
1612	$this->rewriter->skip(
1613	array(
1614	'type' => WP_SQLite_Token::TYPE_OPERATOR,
1615	'value' => ')',
1616	)
1617	);
1618
1619	return true;
1620	}
1621
1622	/**
1623	* Executes a TRUNCATE statement.
1624	*/
1625	private function execute_truncate() {
1626	$this->rewriter->skip(); // TRUNCATE.
1627	if ( 'TABLE' === strtoupper( $this->rewriter->peek()->value ?? '' ) ) {
1628	$this->rewriter->skip(); // TABLE.
1629	}
1630	$this->rewriter->add( new WP_SQLite_Token( 'DELETE', WP_SQLite_Token::TYPE_KEYWORD ) );
1631	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
1632	$this->rewriter->add( new WP_SQLite_Token( 'FROM', WP_SQLite_Token::TYPE_KEYWORD ) );
1633	$this->rewriter->consume_all();
1634	$this->execute_sqlite_query( $this->rewriter->get_updated_query() );
1635	$this->results = true;
1636	$this->return_value = true;
1637	}
1638
1639	/**
1640	* Executes a DESCRIBE statement.
1641	*
1642	* @throws PDOException When the table is not found.
1643	*/
1644	private function execute_describe() {
1645	$this->rewriter->skip();
1646	$this->table_name = $this->rewriter->consume()->value;
1647	$this->set_results_from_fetched_data(
1648	$this->describe( $this->table_name )
1649	);
1650	if ( ! $this->results ) {
1651	throw new PDOException( 'Table not found' );
1652	}
1653	}
1654
1655	/**
1656	* Executes a SELECT statement.
1657	*
1658	* @param string $table_name The table name.
1659	*
1660	* @return array
1661	*/
1662	private function describe( $table_name ) {
1663	return $this->execute_sqlite_query(
1664	"SELECT
1665	`name` as `Field`,
1666	(
1667	CASE `notnull`
1668	WHEN 0 THEN 'YES'
1669	WHEN 1 THEN 'NO'
1670	END
1671	) as `Null`,
1672	COALESCE(
1673	d.`mysql_type`,
1674	(
1675	CASE `type`
1676	WHEN 'INTEGER' THEN 'int'
1677	WHEN 'TEXT' THEN 'text'
1678	WHEN 'BLOB' THEN 'blob'
1679	WHEN 'REAL' THEN 'real'
1680	ELSE `type`
1681	END
1682	)
1683	) as `Type`,
1684	TRIM(`dflt_value`, \"'\") as `Default`,
1685	'' as Extra,
1686	(
1687	CASE `pk`
1688	WHEN 0 THEN ''
1689	ELSE 'PRI'
1690	END
1691	) as `Key`
1692	FROM pragma_table_info(\"$table_name\") p
1693	LEFT JOIN " . self::DATA_TYPES_CACHE_TABLE . " d
1694	ON d.`table` = \"$table_name\"
1695	AND d.`column_or_index` = p.`name`
1696	;
1697	"
1698	)
1699	->fetchAll( $this->pdo_fetch_mode );
1700	}
1701
1702	/**
1703	* Executes an UPDATE statement.
1704	* Supported syntax:
1705	*
1706	* UPDATE [LOW_PRIORITY] [IGNORE] table_reference
1707	* SET assignment_list
1708	* [WHERE where_condition]
1709	* [ORDER BY ...]
1710	* [LIMIT row_count]
1711	*
1712	* @see https://dev.mysql.com/doc/refman/8.0/en/update.html
1713	*/
1714	private function execute_update() {
1715	$this->rewriter->consume(); // Consume the UPDATE keyword.
1716	$has_where = false;
1717	$needs_closing_parenthesis = false;
1718	$params = array();
1719	while ( true ) {
1720	$token = $this->rewriter->peek();
1721	if ( ! $token ) {
1722	break;
1723	}
1724
1725	/*
1726	* If the query contains a WHERE clause,
1727	* we need to rewrite the query to use a nested SELECT statement.
1728	* eg:
1729	* - UPDATE table SET column = value WHERE condition LIMIT 1;
1730	* will be rewritten to:
1731	* - UPDATE table SET column = value WHERE rowid IN (SELECT rowid FROM table WHERE condition LIMIT 1);
1732	*/
1733	if ( 0 === $this->rewriter->depth ) {
1734	if ( ( 'LIMIT' === $token->value \|\| 'ORDER' === $token->value ) && ! $has_where ) {
1735	$this->rewriter->add(
1736	new WP_SQLite_Token( 'WHERE', WP_SQLite_Token::TYPE_KEYWORD )
1737	);
1738	$needs_closing_parenthesis = true;
1739	$this->preface_where_clause_with_a_subquery();
1740	} elseif ( 'WHERE' === $token->value ) {
1741	$has_where = true;
1742	$needs_closing_parenthesis = true;
1743	$this->rewriter->consume();
1744	$this->preface_where_clause_with_a_subquery();
1745	$this->rewriter->add(
1746	new WP_SQLite_Token( 'WHERE', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED )
1747	);
1748	}
1749	}
1750
1751	// Ignore the semicolon in case of rewritten query as it breaks the query.
1752	if ( ';' === $this->rewriter->peek()->value && $this->rewriter->peek()->type === WP_SQLite_Token::TYPE_DELIMITER ) {
1753	break;
1754	}
1755
1756	// Record the table name.
1757	if (
1758	! $this->table_name &&
1759	! $token->matches(
1760	WP_SQLite_Token::TYPE_KEYWORD,
1761	WP_SQLite_Token::FLAG_KEYWORD_RESERVED
1762	)
1763	) {
1764	$this->table_name = $token->value;
1765	}
1766
1767	$this->remember_last_reserved_keyword( $token );
1768
1769	if (
1770	$this->extract_bound_parameter( $token, $params )
1771	\|\| $this->translate_expression( $token )
1772	) {
1773	continue;
1774	}
1775
1776	$this->rewriter->consume();
1777	}
1778
1779	// Wrap up the WHERE clause with the nested SELECT statement.
1780	if ( $needs_closing_parenthesis ) {
1781	$this->rewriter->add( new WP_SQLite_Token( ')', WP_SQLite_Token::TYPE_OPERATOR ) );
1782	}
1783
1784	$this->rewriter->consume_all();
1785
1786	$updated_query = $this->rewriter->get_updated_query();
1787	$this->execute_sqlite_query( $updated_query, $params );
1788	$this->set_result_from_affected_rows();
1789	}
1790
1791	/**
1792	* Injects `rowid IN (SELECT rowid FROM table WHERE ...` into the WHERE clause at the current
1793	* position in the query.
1794	*
1795	* This is necessary to emulate the behavior of MySQL's UPDATE LIMIT and DELETE LIMIT statement
1796	* as SQLite does not support LIMIT in UPDATE and DELETE statements.
1797	*
1798	* The WHERE clause is wrapped in a subquery that selects the rowid of the rows that match the original
1799	* WHERE clause.
1800	*
1801	* @return void
1802	*/
1803	private function preface_where_clause_with_a_subquery() {
1804	$this->rewriter->add_many(
1805	array(
1806	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1807	new WP_SQLite_Token( 'rowid', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
1808	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1809	new WP_SQLite_Token( 'IN', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
1810	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1811	new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ),
1812	new WP_SQLite_Token( 'SELECT', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
1813	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1814	new WP_SQLite_Token( 'rowid', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
1815	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1816	new WP_SQLite_Token( 'FROM', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
1817	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1818	new WP_SQLite_Token( $this->table_name, WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
1819	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
1820	)
1821	);
1822	}
1823
1824	/**
1825	* Executes a INSERT or REPLACE statement.
1826	*/
1827	private function execute_insert_or_replace() {
1828	$params = array();
1829	$is_in_duplicate_section = false;
1830
1831	$this->rewriter->consume(); // INSERT or REPLACE.
1832
1833	// Consume the query type.
1834	if ( 'IGNORE' === $this->rewriter->peek()->value ) {
1835	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
1836	$this->rewriter->add( new WP_SQLite_Token( 'OR', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ) );
1837	$this->rewriter->consume(); // IGNORE.
1838	}
1839
1840	// Consume and record the table name.
1841	$this->insert_columns = array();
1842	$this->rewriter->consume(); // INTO.
1843	$this->table_name = $this->rewriter->consume()->value; // Table name.
1844
1845	/*
1846	* A list of columns is given if the opening parenthesis
1847	* is earlier than the VALUES keyword.
1848	*/
1849	$paren = $this->rewriter->peek(
1850	array(
1851	'type' => WP_SQLite_Token::TYPE_OPERATOR,
1852	'value' => '(',
1853	)
1854	);
1855	$values = $this->rewriter->peek(
1856	array(
1857	'type' => WP_SQLite_Token::TYPE_KEYWORD,
1858	'value' => 'VALUES',
1859	)
1860	);
1861	if ( $paren && $values && $paren->position <= $values->position ) {
1862	$this->rewriter->consume(
1863	array(
1864	'type' => WP_SQLite_Token::TYPE_OPERATOR,
1865	'value' => '(',
1866	)
1867	);
1868	while ( true ) {
1869	$token = $this->rewriter->consume();
1870	if ( $token->matches( WP_SQLite_Token::TYPE_OPERATOR, null, array( ')' ) ) ) {
1871	break;
1872	}
1873	if ( ! $token->matches( WP_SQLite_Token::TYPE_OPERATOR ) ) {
1874	$this->insert_columns[] = $token->value;
1875	}
1876	}
1877	}
1878
1879	while ( true ) {
1880	$token = $this->rewriter->peek();
1881	if ( ! $token ) {
1882	break;
1883	}
1884
1885	$this->remember_last_reserved_keyword( $token );
1886
1887	if (
1888	( $is_in_duplicate_section && $this->translate_values_function( $token ) )
1889	\|\| $this->extract_bound_parameter( $token, $params )
1890	\|\| $this->translate_expression( $token )
1891	) {
1892	continue;
1893	}
1894
1895	if ( $token->matches(
1896	WP_SQLite_Token::TYPE_KEYWORD,
1897	null,
1898	array( 'DUPLICATE' )
1899	)
1900	) {
1901	$is_in_duplicate_section = true;
1902	$this->translate_on_duplicate_key( $this->table_name );
1903	continue;
1904	}
1905
1906	$this->rewriter->consume();
1907	}
1908
1909	$this->rewriter->consume_all();
1910
1911	$updated_query = $this->rewriter->get_updated_query();
1912	$this->execute_sqlite_query( $updated_query, $params );
1913	$this->set_result_from_affected_rows();
1914	$this->last_insert_id = $this->pdo->lastInsertId();
1915	if ( is_numeric( $this->last_insert_id ) ) {
1916	$this->last_insert_id = (int) $this->last_insert_id;
1917	}
1918
1919	if ( function_exists( 'apply_filters' ) ) {
1920	$this->last_insert_id = apply_filters( 'sqlite_last_insert_id', $this->last_insert_id, $this->table_name );
1921	}
1922	}
1923
1924	/**
1925	* Preprocesses a string literal.
1926	*
1927	* @param string $value The string literal.
1928	*
1929	* @return string The preprocessed string literal.
1930	*/
1931	private function preprocess_string_literal( $value ) {
1932	/*
1933	* The code below converts the date format to one preferred by SQLite.
1934	*
1935	* MySQL accepts ISO 8601 date strings: 'YYYY-MM-DDTHH:MM:SSZ'
1936	* SQLite prefers a slightly different format: 'YYYY-MM-DD HH:MM:SS'
1937	*
1938	* SQLite date and time functions can understand the ISO 8601 notation, but
1939	* lookups don't. To keep the lookups working, we need to store all dates
1940	* in UTC without the "T" and "Z" characters.
1941	*
1942	* Caveat: It will adjust every string that matches the pattern, not just dates.
1943	*
1944	* In theory, we could only adjust semantic dates, e.g. the data inserted
1945	* to a date column or compared against a date column.
1946	*
1947	* In practice, this is hard because dates are just text – SQLite has no separate
1948	* datetime field. We'd need to cache the MySQL data type from the original
1949	* CREATE TABLE query and then keep refreshing the cache after each ALTER TABLE query.
1950	*
1951	* That's a lot of complexity that's perhaps not worth it. Let's just convert
1952	* everything for now. The regexp assumes "Z" is always at the end of the string,
1953	* which is true in the unit test suite, but there could also be a timezone offset
1954	* like "+00:00" or "+01:00". We could add support for that later if needed.
1955	*/
1956	if ( 1 === preg_match( '/^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})Z$/', $value, $matches ) ) {
1957	$value = $matches[1] . ' ' . $matches[2];
1958	}
1959
1960	/*
1961	* Mimic MySQL's behavior and truncate invalid dates.
1962	*
1963	* "2020-12-41 14:15:27" becomes "0000-00-00 00:00:00"
1964	*
1965	* WARNING: We have no idea whether the truncated value should
1966	* be treated as a date in the first place.
1967	* In SQLite dates are just strings. This could be a perfectly
1968	* valid string that just happens to contain a date-like value.
1969	*
1970	* At the same time, WordPress seems to rely on MySQL's behavior
1971	* and even tests for it in Tests_Post_wpInsertPost::test_insert_empty_post_date.
1972	* Let's truncate the dates for now.
1973	*
1974	* In the future, let's update WordPress to do its own date validation
1975	* and stop relying on this MySQL feature,
1976	*/
1977	if ( 1 === preg_match( '/^(\d{4}-\d{2}-\d{2}) (\d{2}:\d{2}:\d{2})$/', $value, $matches ) ) {
1978	/*
1979	* Calling strtotime("0000-00-00 00:00:00") in 32-bit environments triggers
1980	* an "out of integer range" warning – let's avoid that call for the popular
1981	* case of "zero" dates.
1982	*/
1983	if ( '0000-00-00 00:00:00' !== $value && false === strtotime( $value ) ) {
1984	$value = '0000-00-00 00:00:00';
1985	}
1986	}
1987	return $value;
1988	}
1989
1990	/**
1991	* Preprocesses a LIKE expression.
1992	*
1993	* @param WP_SQLite_Token $token The token to preprocess.
1994	* @return string
1995	*/
1996	private function preprocess_like_expr( &$token ) {
1997	/*
1998	* This code handles escaped wildcards in LIKE clauses.
1999	* If we are within a LIKE experession, we look for \_ and \%, the
2000	* escaped LIKE wildcards, the ones where we want a literal, not a
2001	* wildcard match. We change the \ escape for an ASCII \x1a (SUB) character,
2002	* so the \ characters won't get munged.
2003	* These \_ and \% escape sequences are in the token name, because
2004	* the lexer has already done stripcslashes on the value.
2005	*/
2006	if ( $this->like_expression_nesting > 0 ) {
2007	/* Remove the quotes around the name. */
2008	$unescaped_value = mb_substr( $token->token, 1, -1, 'UTF-8' );
2009	if ( str_contains( $unescaped_value, '\_' ) \|\| str_contains( $unescaped_value, '\%' ) ) {
2010	++$this->like_escape_count;
2011	return str_replace(
2012	array( '\_', '\%' ),
2013	array( self::LIKE_ESCAPE_CHAR . '_', self::LIKE_ESCAPE_CHAR . '%' ),
2014	$unescaped_value
2015	);
2016	}
2017	}
2018	return $token->value;
2019	}
2020	/**
2021	* Translate CAST() function when we want to cast to BINARY.
2022	*
2023	* @param WP_SQLite_Token $token The token to translate.
2024	*
2025	* @return bool
2026	*/
2027	private function translate_cast_as_binary( $token ) {
2028	if ( ! $token->matches(
2029	WP_SQLite_Token::TYPE_KEYWORD,
2030	WP_SQLite_Token::FLAG_KEYWORD_DATA_TYPE,
2031	array( 'BINARY' )
2032	)
2033	) {
2034	return false;
2035	}
2036
2037	$call_parent = $this->rewriter->last_call_stack_element();
2038	if (
2039	! $call_parent
2040	\|\| 'CAST' !== $call_parent['function']
2041	) {
2042	return false;
2043	}
2044
2045	// Rewrite AS BINARY to AS BLOB inside CAST() calls.
2046	$this->rewriter->skip();
2047	$this->rewriter->add( new WP_SQLite_Token( 'BLOB', $token->type, $token->flags ) );
2048	return true;
2049	}
2050
2051	/**
2052	* Translates an expression in an SQL statement if the token is the start of an expression.
2053	*
2054	* @param WP_SQLite_Token $token The first token of an expression.
2055	*
2056	* @return bool True if the expression was translated successfully, false otherwise.
2057	*/
2058	private function translate_expression( $token ) {
2059	return (
2060	$this->skip_from_dual( $token )
2061	\|\| $this->translate_concat_function( $token )
2062	\|\| $this->translate_concat_comma_to_pipes( $token )
2063	\|\| $this->translate_function_aliases( $token )
2064	\|\| $this->translate_cast_as_binary( $token )
2065	\|\| $this->translate_date_add_sub( $token )
2066	\|\| $this->translate_date_format( $token )
2067	\|\| $this->translate_interval( $token )
2068	\|\| $this->translate_regexp_functions( $token )
2069	\|\| $this->capture_group_by( $token )
2070	\|\| $this->translate_ungrouped_having( $token )
2071	\|\| $this->translate_like_binary( $token )
2072	\|\| $this->translate_like_escape( $token )
2073	\|\| $this->translate_left_function( $token )
2074	);
2075	}
2076
2077	/**
2078	* Skips the `FROM DUAL` clause in the SQL statement.
2079	*
2080	* @param WP_SQLite_Token $token The token to check for the `FROM DUAL` clause.
2081	*
2082	* @return bool True if the `FROM DUAL` clause was skipped, false otherwise.
2083	*/
2084	private function skip_from_dual( $token ) {
2085	if (
2086	! $token->matches(
2087	WP_SQLite_Token::TYPE_KEYWORD,
2088	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
2089	array( 'FROM' )
2090	)
2091	) {
2092	return false;
2093	}
2094	$from_table = $this->rewriter->peek_nth( 2 )->value;
2095	if ( 'DUAL' !== strtoupper( $from_table ?? '' ) ) {
2096	return false;
2097	}
2098
2099	// FROM DUAL is a MySQLism that means "no tables".
2100	$this->rewriter->skip();
2101	$this->rewriter->skip();
2102	return true;
2103	}
2104
2105	/**
2106	* Peeks at the table name in the SQL statement.
2107	*
2108	* @param WP_SQLite_Token $token The token to check for the table name.
2109	*
2110	* @return string\|bool The table name if it was found, false otherwise.
2111	*/
2112	private function peek_table_name( $token ) {
2113	if (
2114	! $token->matches(
2115	WP_SQLite_Token::TYPE_KEYWORD,
2116	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
2117	array( 'FROM' )
2118	)
2119	) {
2120	return false;
2121	}
2122	$table_name = $this->rewriter->peek_nth( 2 )->value;
2123	if ( 'dual' === strtolower( $table_name ) ) {
2124	return false;
2125	}
2126	return $table_name;
2127	}
2128
2129	/**
2130	* Skips the `SQL_CALC_FOUND_ROWS` keyword in the SQL statement.
2131	*
2132	* @param WP_SQLite_Token $token The token to check for the `SQL_CALC_FOUND_ROWS` keyword.
2133	*
2134	* @return bool True if the `SQL_CALC_FOUND_ROWS` keyword was skipped, false otherwise.
2135	*/
2136	private function skip_sql_calc_found_rows( $token ) {
2137	if (
2138	! $token->matches(
2139	WP_SQLite_Token::TYPE_KEYWORD,
2140	null,
2141	array( 'SQL_CALC_FOUND_ROWS' )
2142	)
2143	) {
2144	return false;
2145	}
2146	$this->rewriter->skip();
2147	return true;
2148	}
2149
2150	/**
2151	* Remembers the last reserved keyword encountered in the SQL statement.
2152	*
2153	* @param WP_SQLite_Token $token The token to check for the reserved keyword.
2154	*/
2155	private function remember_last_reserved_keyword( $token ) {
2156	if (
2157	$token->matches(
2158	WP_SQLite_Token::TYPE_KEYWORD,
2159	WP_SQLite_Token::FLAG_KEYWORD_RESERVED
2160	)
2161	) {
2162	$this->last_reserved_keyword = $token->value;
2163	}
2164	}
2165
2166	/**
2167	* Extracts the bound parameter from the given token and adds it to the `$params` array.
2168	*
2169	* @param WP_SQLite_Token $token The token to extract the bound parameter from.
2170	* @param array $params An array of parameters to be bound to the SQL statement.
2171	*
2172	* @return bool True if the parameter was extracted successfully, false otherwise.
2173	*/
2174	private function extract_bound_parameter( $token, &$params ) {
2175	if ( ! $token->matches(
2176	WP_SQLite_Token::TYPE_STRING,
2177	WP_SQLite_Token::FLAG_STRING_SINGLE_QUOTES
2178	)
2179	\|\| 'AS' === $this->last_reserved_keyword
2180	) {
2181	return false;
2182	}
2183
2184	$param_name = ':param' . count( $params );
2185	$value = $this->preprocess_like_expr( $token );
2186	$value = $this->preprocess_string_literal( $value );
2187	$params[ $param_name ] = $value;
2188	$this->rewriter->skip();
2189	$this->rewriter->add( new WP_SQLite_Token( $param_name, WP_SQLite_Token::TYPE_STRING, WP_SQLite_Token::FLAG_STRING_SINGLE_QUOTES ) );
2190	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2191	return true;
2192	}
2193
2194	/**
2195	* Translate CONCAT() function.
2196	*
2197	* @param WP_SQLite_Token $token The token to translate.
2198	*
2199	* @return bool
2200	*/
2201	private function translate_concat_function( $token ) {
2202	if (
2203	! $token->matches(
2204	WP_SQLite_Token::TYPE_KEYWORD,
2205	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2206	array( 'CONCAT' )
2207	)
2208	) {
2209	return false;
2210	}
2211
2212	/*
2213	* Skip the CONCAT function but leave the parentheses.
2214	* There is another code block below that replaces the
2215	* , operators between the CONCAT arguments with \|\|.
2216	*/
2217	$this->rewriter->skip();
2218	return true;
2219	}
2220
2221	/**
2222	* Translate CONCAT() function arguments.
2223	*
2224	* @param WP_SQLite_Token $token The token to translate.
2225	*
2226	* @return bool
2227	*/
2228	private function translate_concat_comma_to_pipes( $token ) {
2229	if ( ! $token->matches(
2230	WP_SQLite_Token::TYPE_OPERATOR,
2231	WP_SQLite_Token::FLAG_OPERATOR_SQL,
2232	array( ',' )
2233	)
2234	) {
2235	return false;
2236	}
2237
2238	$call_parent = $this->rewriter->last_call_stack_element();
2239	if (
2240	! $call_parent
2241	\|\| 'CONCAT' !== $call_parent['function']
2242	) {
2243	return false;
2244	}
2245
2246	// Rewrite commas to \|\| in CONCAT() calls.
2247	$this->rewriter->skip();
2248	$this->rewriter->add( new WP_SQLite_Token( '\|\|', WP_SQLite_Token::TYPE_OPERATOR ) );
2249	return true;
2250	}
2251
2252	/**
2253	* Translate DATE_ADD() and DATE_SUB() functions.
2254	*
2255	* @param WP_SQLite_Token $token The token to translate.
2256	*
2257	* @return bool
2258	*/
2259	private function translate_date_add_sub( $token ) {
2260	if (
2261	! $token->matches(
2262	WP_SQLite_Token::TYPE_KEYWORD,
2263	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2264	array( 'DATE_ADD', 'DATE_SUB' )
2265	)
2266	) {
2267	return false;
2268	}
2269
2270	$this->rewriter->skip();
2271	$this->rewriter->add( new WP_SQLite_Token( 'DATETIME', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_FUNCTION ) );
2272	return true;
2273	}
2274
2275	/**
2276	* Translate the LEFT() function.
2277	*
2278	* > Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
2279	*
2280	* https://dev.mysql.com/doc/refman/8.3/en/string-functions.html#function_left
2281	*
2282	* @param WP_SQLite_Token $token The token to translate.
2283	*
2284	* @return bool
2285	*/
2286	private function translate_left_function( $token ) {
2287	if (
2288	! $token->matches(
2289	WP_SQLite_Token::TYPE_KEYWORD,
2290	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2291	array( 'LEFT' )
2292	)
2293	) {
2294	return false;
2295	}
2296
2297	$this->rewriter->skip();
2298	$this->rewriter->add( new WP_SQLite_Token( 'SUBSTRING', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_FUNCTION ) );
2299	$this->rewriter->consume(
2300	array(
2301	'type' => WP_SQLite_Token::TYPE_OPERATOR,
2302	'value' => ',',
2303	)
2304	);
2305	$this->rewriter->add( new WP_SQLite_Token( 1, WP_SQLite_Token::TYPE_NUMBER ) );
2306	$this->rewriter->add( new WP_SQLite_Token( ',', WP_SQLite_Token::TYPE_OPERATOR ) );
2307	return true;
2308	}
2309
2310	/**
2311	* Convert function aliases.
2312	*
2313	* @param object $token The current token.
2314	*
2315	* @return bool False when no match, true when this function consumes the token.
2316	*
2317	* @todo LENGTH and CHAR_LENGTH aren't always the same in MySQL for utf8 characters. They are in SQLite.
2318	*/
2319	private function translate_function_aliases( $token ) {
2320	if ( ! $token->matches(
2321	WP_SQLite_Token::TYPE_KEYWORD,
2322	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2323	array( 'SUBSTRING', 'CHAR_LENGTH' )
2324	)
2325	) {
2326	return false;
2327	}
2328	switch ( $token->value ) {
2329	case 'SUBSTRING':
2330	$name = 'SUBSTR';
2331	break;
2332	case 'CHAR_LENGTH':
2333	$name = 'LENGTH';
2334	break;
2335	default:
2336	$name = $token->value;
2337	break;
2338	}
2339	$this->rewriter->skip();
2340	$this->rewriter->add( new WP_SQLite_Token( $name, $token->type, $token->flags ) );
2341
2342	return true;
2343	}
2344
2345	/**
2346	* Translate VALUES() function.
2347	*
2348	* @param WP_SQLite_Token $token The token to translate.
2349	*
2350	* @return bool
2351	*/
2352	private function translate_values_function( $token ) {
2353	if (
2354	! $token->matches(
2355	WP_SQLite_Token::TYPE_KEYWORD,
2356	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2357	array( 'VALUES' )
2358	)
2359	) {
2360	return false;
2361	}
2362
2363	/*
2364	* Rewrite: VALUES(`option_name`)
2365	* to: excluded.option_name
2366	*/
2367	$this->rewriter->skip();
2368	$this->rewriter->add( new WP_SQLite_Token( 'excluded', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ) );
2369	$this->rewriter->add( new WP_SQLite_Token( '.', WP_SQLite_Token::TYPE_OPERATOR ) );
2370
2371	$this->rewriter->skip(); // Skip the opening `(`.
2372	// Consume the column name.
2373	$this->rewriter->consume(
2374	array(
2375	'type' => WP_SQLite_Token::TYPE_OPERATOR,
2376	'value' => ')',
2377	)
2378	);
2379	// Drop the consumed ')' token.
2380	$this->rewriter->drop_last();
2381	return true;
2382	}
2383
2384	/**
2385	* Translate DATE_FORMAT() function.
2386	*
2387	* @param WP_SQLite_Token $token The token to translate.
2388	*
2389	* @throws Exception If the token is not a DATE_FORMAT() function.
2390	*
2391	* @return bool
2392	*/
2393	private function translate_date_format( $token ) {
2394	if (
2395	! $token->matches(
2396	WP_SQLite_Token::TYPE_KEYWORD,
2397	WP_SQLite_Token::FLAG_KEYWORD_FUNCTION,
2398	array( 'DATE_FORMAT' )
2399	)
2400	) {
2401	return false;
2402	}
2403
2404	// Rewrite DATE_FORMAT( `post_date`, '%Y-%m-%d' ) to STRFTIME( '%Y-%m-%d', `post_date` ).
2405
2406	// Skip the DATE_FORMAT function name.
2407	$this->rewriter->skip();
2408	// Skip the opening `(`.
2409	$this->rewriter->skip();
2410
2411	// Skip the first argument so we can read the second one.
2412	$first_arg = $this->rewriter->skip_and_return_all(
2413	array(
2414	'type' => WP_SQLite_Token::TYPE_OPERATOR,
2415	'value' => ',',
2416	)
2417	);
2418
2419	// Make sure we actually found the comma.
2420	$comma = array_pop( $first_arg );
2421	if ( ',' !== $comma->value ) {
2422	throw new Exception( 'Could not parse the DATE_FORMAT() call' );
2423	}
2424
2425	// Skip the second argument but capture the token.
2426	$format = $this->rewriter->skip()->value;
2427	$new_format = strtr( $format, $this->mysql_date_format_to_sqlite_strftime );
2428	if ( ! $new_format ) {
2429	throw new Exception( "Could not translate a DATE_FORMAT() format to STRFTIME format ($format)" );
2430	}
2431
2432	/*
2433	* MySQL supports comparing strings and floats, e.g.
2434	*
2435	* > SELECT '00.42' = 0.4200
2436	* 1
2437	*
2438	* SQLite does not support that. At the same time,
2439	* WordPress likes to filter dates by comparing numeric
2440	* outputs of DATE_FORMAT() to floats, e.g.:
2441	*
2442	* -- Filter by hour and minutes
2443	* DATE_FORMAT(
2444	* STR_TO_DATE('2014-10-21 00:42:29', '%Y-%m-%d %H:%i:%s'),
2445	* '%H.%i'
2446	* ) = 0.4200;
2447	*
2448	* Let's cast the STRFTIME() output to a float if
2449	* the date format is typically used for string
2450	* to float comparisons.
2451	*
2452	* In the future, let's update WordPress to avoid comparing
2453	* strings and floats.
2454	*/
2455	$cast_to_float = '%H.%i' === $format;
2456	if ( $cast_to_float ) {
2457	$this->rewriter->add( new WP_SQLite_Token( 'CAST', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_FUNCTION ) );
2458	$this->rewriter->add( new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ) );
2459	}
2460
2461	$this->rewriter->add( new WP_SQLite_Token( 'STRFTIME', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_FUNCTION ) );
2462	$this->rewriter->add( new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ) );
2463	$this->rewriter->add( new WP_SQLite_Token( "'$new_format'", WP_SQLite_Token::TYPE_STRING ) );
2464	$this->rewriter->add( new WP_SQLite_Token( ',', WP_SQLite_Token::TYPE_OPERATOR ) );
2465
2466	// Add the buffered tokens back to the stream.
2467	$this->rewriter->add_many( $first_arg );
2468
2469	// Consume the closing ')'.
2470	$this->rewriter->consume(
2471	array(
2472	'type' => WP_SQLite_Token::TYPE_OPERATOR,
2473	'value' => ')',
2474	)
2475	);
2476
2477	if ( $cast_to_float ) {
2478	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2479	$this->rewriter->add( new WP_SQLite_Token( 'as', WP_SQLite_Token::TYPE_OPERATOR ) );
2480	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2481	$this->rewriter->add( new WP_SQLite_Token( 'FLOAT', WP_SQLite_Token::TYPE_KEYWORD ) );
2482	$this->rewriter->add( new WP_SQLite_Token( ')', WP_SQLite_Token::TYPE_OPERATOR ) );
2483	}
2484
2485	return true;
2486	}
2487
2488	/**
2489	* Translate INTERVAL keyword with DATE_ADD() and DATE_SUB().
2490	*
2491	* @param WP_SQLite_Token $token The token to translate.
2492	*
2493	* @return bool
2494	*/
2495	private function translate_interval( $token ) {
2496	if (
2497	! $token->matches(
2498	WP_SQLite_Token::TYPE_KEYWORD,
2499	null,
2500	array( 'INTERVAL' )
2501	)
2502	) {
2503	return false;
2504	}
2505	// Skip the INTERVAL keyword from the output stream.
2506	$this->rewriter->skip();
2507
2508	$num = $this->rewriter->skip()->value;
2509	$unit = $this->rewriter->skip()->value;
2510
2511	/*
2512	* In MySQL, we say:
2513	* DATE_ADD(d, INTERVAL 1 YEAR)
2514	* DATE_SUB(d, INTERVAL 1 YEAR)
2515	*
2516	* In SQLite, we say:
2517	* DATE(d, '+1 YEAR')
2518	* DATE(d, '-1 YEAR')
2519	*
2520	* The sign of the interval is determined by the date_* function
2521	* that is closest in the call stack.
2522	*
2523	* Let's find it.
2524	*/
2525	$interval_op = '+'; // Default to adding.
2526	for ( $j = count( $this->rewriter->call_stack ) - 1; $j >= 0; $j-- ) {
2527	$call = $this->rewriter->call_stack[ $j ];
2528	if ( 'DATE_ADD' === $call['function'] ) {
2529	$interval_op = '+';
2530	break;
2531	}
2532	if ( 'DATE_SUB' === $call['function'] ) {
2533	$interval_op = '-';
2534	break;
2535	}
2536	}
2537
2538	$this->rewriter->add( new WP_SQLite_Token( "'{$interval_op}$num $unit'", WP_SQLite_Token::TYPE_STRING ) );
2539	return true;
2540	}
2541
2542	/**
2543	* Translate REGEXP and RLIKE keywords.
2544	*
2545	* @param WP_SQLite_Token $token The token to translate.
2546	*
2547	* @return bool
2548	*/
2549	private function translate_regexp_functions( $token ) {
2550	if (
2551	! $token->matches(
2552	WP_SQLite_Token::TYPE_KEYWORD,
2553	null,
2554	array( 'REGEXP', 'RLIKE' )
2555	)
2556	) {
2557	return false;
2558	}
2559	$this->rewriter->skip();
2560	$this->rewriter->add( new WP_SQLite_Token( 'REGEXP', WP_SQLite_Token::TYPE_KEYWORD ) );
2561
2562	$next = $this->rewriter->peek();
2563
2564	/*
2565	* If the query says REGEXP BINARY, the comparison is byte-by-byte
2566	* and letter casing matters – lowercase and uppercase letters are
2567	* represented using different byte codes.
2568	*
2569	* The REGEXP function can't be easily made to accept two
2570	* parameters, so we'll have to use a hack to get around this.
2571	*
2572	* If the first character of the pattern is a null byte, we'll
2573	* remove it and make the comparison case-sensitive. This should
2574	* be reasonably safe since PHP does not allow null bytes in
2575	* regular expressions anyway.
2576	*/
2577	if ( $next->matches( WP_SQLite_Token::TYPE_KEYWORD, null, array( 'BINARY' ) ) ) {
2578	// Skip the "BINARY" keyword.
2579	$this->rewriter->skip();
2580	// Prepend a null byte to the pattern.
2581	$this->rewriter->add_many(
2582	array(
2583	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
2584	new WP_SQLite_Token( 'char', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_FUNCTION ),
2585	new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ),
2586	new WP_SQLite_Token( '0', WP_SQLite_Token::TYPE_NUMBER ),
2587	new WP_SQLite_Token( ')', WP_SQLite_Token::TYPE_OPERATOR ),
2588	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
2589	new WP_SQLite_Token( '\|\|', WP_SQLite_Token::TYPE_OPERATOR ),
2590	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
2591	)
2592	);
2593	}
2594	return true;
2595	}
2596	/**
2597	* Translate LIKE BINARY to SQLite equivalent using GLOB.
2598	*
2599	* @param WP_SQLite_Token $token The token to translate.
2600	*
2601	* @return bool
2602	*/
2603	private function translate_like_binary( $token ): bool {
2604	if ( ! $token->matches( WP_SQLite_Token::TYPE_KEYWORD, null, array( 'LIKE' ) ) ) {
2605	return false;
2606	}
2607
2608	$next = $this->rewriter->peek_nth( 2 );
2609	if ( ! $next \|\| ! $next->matches( WP_SQLite_Token::TYPE_KEYWORD, null, array( 'BINARY' ) ) ) {
2610	return false;
2611	}
2612
2613	$this->rewriter->skip(); // Skip 'LIKE'
2614	$this->rewriter->skip(); // Skip 'BINARY'
2615
2616	$pattern_token = $this->rewriter->peek();
2617	$this->rewriter->skip(); // Skip the pattern token
2618
2619	$this->rewriter->add( new WP_SQLite_Token( 'GLOB', WP_SQLite_Token::TYPE_KEYWORD ) );
2620	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2621
2622	$escaped_pattern = $this->escape_like_to_glob( $pattern_token->value );
2623	$this->rewriter->add( new WP_SQLite_Token( $escaped_pattern, WP_SQLite_Token::TYPE_STRING ) );
2624	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2625
2626	return true;
2627	}
2628
2629	/**
2630	* Escape LIKE pattern to GLOB pattern.
2631	*
2632	* @param string $pattern The LIKE pattern.
2633	* @return string The escaped GLOB pattern.
2634	*/
2635	private function escape_like_to_glob( $pattern ) {
2636	// Remove surrounding quotes
2637	$pattern = trim( $pattern, "'\"" );
2638
2639	$pattern = str_replace( '%', '*', $pattern );
2640	$pattern = str_replace( '_', '?', $pattern );
2641
2642	// No need to escape special characters in this case
2643	// because GLOB doesn't require escaping in the same way LIKE does
2644	// Return the pattern wrapped in single quotes
2645	return "'" . $pattern . "'";
2646	}
2647
2648	/**
2649	* Detect GROUP BY.
2650	*
2651	* @todo edgecase Fails on a statement with GROUP BY nested in an outer HAVING without GROUP BY.
2652	*
2653	* @param WP_SQLite_Token $token The token to translate.
2654	*
2655	* @return bool
2656	*/
2657	private function capture_group_by( $token ) {
2658	if (
2659	! $token->matches(
2660	WP_SQLite_Token::TYPE_KEYWORD,
2661	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
2662	array( 'GROUP BY' )
2663	)
2664	) {
2665	return false;
2666	}
2667
2668	$this->has_group_by = true;
2669
2670	return false;
2671	}
2672
2673	/**
2674	* Translate HAVING without GROUP BY to GROUP BY 1 HAVING.
2675	*
2676	* @param WP_SQLite_Token $token The token to translate.
2677	*
2678	* @return bool
2679	*/
2680	private function translate_ungrouped_having( $token ) {
2681	if (
2682	! $token->matches(
2683	WP_SQLite_Token::TYPE_KEYWORD,
2684	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
2685	array( 'HAVING' )
2686	)
2687	) {
2688	return false;
2689	}
2690	if ( $this->has_group_by ) {
2691	return false;
2692	}
2693
2694	// GROUP BY is missing, add "GROUP BY 1" before the HAVING clause.
2695	$having = $this->rewriter->skip();
2696	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2697	$this->rewriter->add( new WP_SQLite_Token( 'GROUP BY', WP_SQLite_Token::TYPE_KEYWORD ) );
2698	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2699	$this->rewriter->add( new WP_SQLite_Token( '1', WP_SQLite_Token::TYPE_NUMBER ) );
2700	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2701	$this->rewriter->add( $having );
2702
2703	return true;
2704	}
2705
2706	/**
2707	* Rewrite LIKE '\_whatever' as LIKE '\_whatever' ESCAPE '\' .
2708	*
2709	* We look for keyword LIKE. On seeing it we set a flag.
2710	* If the flag is set, we emit ESCAPE '\' before the next keyword.
2711	*
2712	* @param WP_SQLite_Token $token The token to translate.
2713	*
2714	* @return bool
2715	*/
2716	private function translate_like_escape( $token ) {
2717
2718	if ( 0 === $this->like_expression_nesting ) {
2719	$is_like = $token->matches( WP_SQLite_Token::TYPE_KEYWORD, null, array( 'LIKE' ) );
2720	/* is this the LIKE keyword? If so set the flag. */
2721	if ( $is_like ) {
2722	$this->like_expression_nesting = 1;
2723	}
2724	} else {
2725	/* open parenthesis during LIKE parameter, count it. */
2726	if ( $token->matches( WP_SQLite_Token::TYPE_OPERATOR, null, array( '(' ) ) ) {
2727	++$this->like_expression_nesting;
2728
2729	return false;
2730	}
2731
2732	/* close parenthesis matching open parenthesis during LIKE parameter, count it. */
2733	if ( $this->like_expression_nesting > 1 && $token->matches( WP_SQLite_Token::TYPE_OPERATOR, null, array( ')' ) ) ) {
2734	--$this->like_expression_nesting;
2735
2736	return false;
2737	}
2738
2739	/* a keyword, a commo, a semicolon, the end of the statement, or a close parenthesis */
2740	$is_like_finished = $token->matches( WP_SQLite_Token::TYPE_KEYWORD )
2741	\|\| $token->matches( WP_SQLite_Token::TYPE_DELIMITER, null, array( ';' ) ) \|\| ( WP_SQLite_Token::TYPE_DELIMITER === $token->type && null === $token->value )
2742	\|\| $token->matches( WP_SQLite_Token::TYPE_OPERATOR, null, array( ')', ',' ) );
2743
2744	if ( $is_like_finished ) {
2745	/*
2746	* Here we have another keyword encountered with the LIKE in progress.
2747	* Emit the ESCAPE clause.
2748	*/
2749	if ( $this->like_escape_count > 0 ) {
2750	/* If we need the ESCAPE clause emit it. */
2751	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2752	$this->rewriter->add( new WP_SQLite_Token( 'ESCAPE', WP_SQLite_Token::TYPE_KEYWORD ) );
2753	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2754	$this->rewriter->add( new WP_SQLite_Token( "'" . self::LIKE_ESCAPE_CHAR . "'", WP_SQLite_Token::TYPE_STRING ) );
2755	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_DELIMITER ) );
2756	}
2757	$this->like_escape_count = 0;
2758	$this->like_expression_nesting = 0;
2759	}
2760	}
2761
2762	return false;
2763	}
2764
2765	/**
2766	* Rewrite a query from the MySQL information_schema.
2767	*
2768	* @param string $updated_query The query to rewrite.
2769	*
2770	* @return string The query for use by SQLite
2771	*/
2772	private function get_information_schema_query( $updated_query ) {
2773	// @TODO: Actually rewrite the columns.
2774	$normalized_query = preg_replace( '/\s+/', ' ', strtolower( $updated_query ) );
2775	if ( str_contains( $normalized_query, 'bytes' ) ) {
2776	// Count rows per table.
2777	$tables =
2778	$this->execute_sqlite_query( "SELECT name as `table_name` FROM sqlite_master WHERE type='table' ORDER BY name" )->fetchAll();
2779	$tables = $this->strip_sqlite_system_tables( $tables );
2780
2781	$rows = '(CASE ';
2782	foreach ( $tables as $table ) {
2783	$table_name = $table['table_name'];
2784	$count = $this->execute_sqlite_query( "SELECT COUNT(1) as `count` FROM $table_name" )->fetch();
2785	$rows .= " WHEN name = '$table_name' THEN {$count['count']} ";
2786	}
2787	$rows .= 'ELSE 0 END) ';
2788	$updated_query =
2789	"SELECT name as `table_name`, $rows as `rows`, 0 as `bytes` FROM sqlite_master WHERE type='table' ORDER BY name";
2790	} elseif ( str_contains( $normalized_query, 'count(*)' ) && ! str_contains( $normalized_query, 'table_name =' ) ) {
2791	// @TODO This is a guess that the caller wants a count of tables.
2792	$list = array();
2793	foreach ( $this->sqlite_system_tables as $system_table => $name ) {
2794	$list [] = "'" . $system_table . "'";
2795	}
2796	$list = implode( ', ', $list );
2797	$sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT IN ($list)";
2798	$table_count = $this->execute_sqlite_query( $sql )->fetch();
2799	$updated_query = 'SELECT ' . $table_count[0] . ' AS num';
2800
2801	$this->is_information_schema_query = false;
2802	} else {
2803	$updated_query =
2804	"SELECT name as `table_name`, 'myisam' as `engine`, 0 as `data_length`, 0 as `index_length`, 0 as `data_free` FROM sqlite_master WHERE type='table' ORDER BY name";
2805	}
2806
2807	return $updated_query;
2808	}
2809
2810	/**
2811	* Remove system table rows from resultsets of information_schema tables.
2812	*
2813	* @param array $tables The result set.
2814	*
2815	* @return array The filtered result set.
2816	*/
2817	private function strip_sqlite_system_tables( $tables ) {
2818	return array_values(
2819	array_filter(
2820	$tables,
2821	function ( $table ) {
2822	$table_name = false;
2823	if ( is_array( $table ) ) {
2824	if ( isset( $table['Name'] ) ) {
2825	$table_name = $table['Name'];
2826	} elseif ( isset( $table['table_name'] ) ) {
2827	$table_name = $table['table_name'];
2828	}
2829	} elseif ( is_object( $table ) ) {
2830	$table_name = property_exists( $table, 'Name' )
2831	? $table->Name // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
2832	: $table->table_name;
2833	}
2834
2835	return $table_name && ! array_key_exists( $table_name, $this->sqlite_system_tables );
2836	},
2837	ARRAY_FILTER_USE_BOTH
2838	)
2839	);
2840	}
2841
2842	/**
2843	* Translate the ON DUPLICATE KEY UPDATE clause.
2844	*
2845	* @param string $table_name The table name.
2846	*
2847	* @return void
2848	*/
2849	private function translate_on_duplicate_key( $table_name ) {
2850	/*
2851	* Rewrite:
2852	* ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`)
2853	* to:
2854	* ON CONFLICT(ip) DO UPDATE SET option_name = excluded.option_name
2855	*/
2856
2857	// Find the conflicting column.
2858	$pk_columns = array();
2859	foreach ( $this->get_primary_keys( $table_name ) as $row ) {
2860	$pk_columns[] = $row['name'];
2861	}
2862
2863	$unique_columns = array();
2864	foreach ( $this->get_keys( $table_name, true ) as $row ) {
2865	foreach ( $row['columns'] as $column ) {
2866	$unique_columns[] = $column['name'];
2867	}
2868	}
2869
2870	// Guess the conflict column based on the query details.
2871
2872	// 1. Listed INSERT columns that are either PK or UNIQUE.
2873	$conflict_columns = array_intersect(
2874	$this->insert_columns,
2875	array_merge( $pk_columns, $unique_columns )
2876	);
2877	// 2. Composite Primary Key columns.
2878	if ( ! $conflict_columns && count( $pk_columns ) > 1 ) {
2879	$conflict_columns = $pk_columns;
2880	}
2881	// 3. The first unique column.
2882	if ( ! $conflict_columns && count( $unique_columns ) > 0 ) {
2883	$conflict_columns = array( $unique_columns[0] );
2884	}
2885	// 4. Regular Primary Key column.
2886	if ( ! $conflict_columns ) {
2887	$conflict_columns = $pk_columns;
2888	}
2889
2890	/*
2891	* If we still haven't found any conflict column, we
2892	* can't rewrite the ON DUPLICATE KEY statement.
2893	* Let's default to a regular INSERT to mimic MySQL
2894	* which would still insert the row without throwing
2895	* an error.
2896	*/
2897	if ( ! $conflict_columns ) {
2898	// Drop the consumed "ON".
2899	$this->rewriter->drop_last();
2900	// Skip over "DUPLICATE", "KEY", and "UPDATE".
2901	$this->rewriter->skip();
2902	$this->rewriter->skip();
2903	$this->rewriter->skip();
2904	while ( $this->rewriter->skip() ) {
2905	// Skip over the rest of the query.
2906	}
2907	return;
2908	}
2909
2910	// Skip over "DUPLICATE", "KEY", and "UPDATE".
2911	$this->rewriter->skip();
2912	$this->rewriter->skip();
2913	$this->rewriter->skip();
2914
2915	// Add the CONFLICT keyword.
2916	$this->rewriter->add( new WP_SQLite_Token( 'CONFLICT', WP_SQLite_Token::TYPE_KEYWORD ) );
2917
2918	// Add "( <columns list> ) DO UPDATE SET ".
2919	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2920	$this->rewriter->add( new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ) );
2921
2922	$max = count( $conflict_columns );
2923	$i = 0;
2924	foreach ( $conflict_columns as $conflict_column ) {
2925	$this->rewriter->add( new WP_SQLite_Token( '"' . $conflict_column . '"', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ) );
2926	if ( ++$i < $max ) {
2927	$this->rewriter->add( new WP_SQLite_Token( ',', WP_SQLite_Token::TYPE_OPERATOR ) );
2928	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2929	}
2930	}
2931	$this->rewriter->add( new WP_SQLite_Token( ')', WP_SQLite_Token::TYPE_OPERATOR ) );
2932	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2933	$this->rewriter->add( new WP_SQLite_Token( 'DO', WP_SQLite_Token::TYPE_KEYWORD ) );
2934	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2935	$this->rewriter->add( new WP_SQLite_Token( 'UPDATE', WP_SQLite_Token::TYPE_KEYWORD ) );
2936	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2937	$this->rewriter->add( new WP_SQLite_Token( 'SET', WP_SQLite_Token::TYPE_KEYWORD ) );
2938	$this->rewriter->add( new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ) );
2939	}
2940
2941	/**
2942	* Get the primary keys for a table.
2943	*
2944	* @param string $table_name Table name.
2945	*
2946	* @return array
2947	*/
2948	private function get_primary_keys( $table_name ) {
2949	$stmt = $this->execute_sqlite_query( 'SELECT * FROM pragma_table_info(:table_name) as l WHERE l.pk > 0;' );
2950	$stmt->execute( array( 'table_name' => $table_name ) );
2951	return $stmt->fetchAll();
2952	}
2953
2954	/**
2955	* Get the keys for a table.
2956	*
2957	* @param string $table_name Table name.
2958	* @param bool $only_unique Only return unique keys.
2959	*
2960	* @return array
2961	*/
2962	private function get_keys( $table_name, $only_unique = false ) {
2963	$query = $this->execute_sqlite_query( 'SELECT * FROM pragma_index_list("' . $table_name . '") as l;' );
2964	$indices = $query->fetchAll();
2965	$results = array();
2966	foreach ( $indices as $index ) {
2967	if ( ! $only_unique \|\| '1' === $index['unique'] ) {
2968	$query = $this->execute_sqlite_query( 'SELECT * FROM pragma_index_info("' . $index['name'] . '") as l;' );
2969	$results[] = array(
2970	'index' => $index,
2971	'columns' => $query->fetchAll(),
2972	);
2973	}
2974	}
2975	return $results;
2976	}
2977
2978	/**
2979	* Get the CREATE TABLE statement for a table.
2980	*
2981	* @param string $table_name Table name.
2982	*
2983	* @return string
2984	*/
2985	private function get_sqlite_create_table( $table_name ) {
2986	$stmt = $this->execute_sqlite_query( 'SELECT sql FROM sqlite_master WHERE type="table" AND name=:table' );
2987	$stmt->execute( array( ':table' => $table_name ) );
2988	$create_table = '';
2989	foreach ( $stmt->fetchAll() as $row ) {
2990	$create_table .= $row['sql'] . "\n";
2991	}
2992	return $create_table;
2993	}
2994
2995	/**
2996	* Translate ALTER query.
2997	*
2998	* @throws Exception If the subject is not 'table', or we're performing an unknown operation.
2999	*/
3000	private function execute_alter() {
3001	$this->rewriter->consume();
3002	$subject = strtolower( $this->rewriter->consume()->token );
3003	if ( 'table' !== $subject ) {
3004	throw new Exception( 'Unknown subject: ' . $subject );
3005	}
3006
3007	$this->table_name = $this->normalize_column_name( $this->rewriter->consume()->token );
3008	do {
3009	/*
3010	* This loop may be executed multiple times if there are multiple operations in the ALTER query.
3011	* Let's reset the initial state on each pass.
3012	*/
3013	$this->rewriter->replace_all(
3014	array(
3015	new WP_SQLite_Token( 'ALTER', WP_SQLite_Token::TYPE_KEYWORD ),
3016	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3017	new WP_SQLite_Token( 'TABLE', WP_SQLite_Token::TYPE_KEYWORD ),
3018	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3019	new WP_SQLite_Token( $this->table_name, WP_SQLite_Token::TYPE_KEYWORD ),
3020	)
3021	);
3022	$op_type = strtoupper( $this->rewriter->consume()->token ?? '' );
3023	$op_raw_subject = $this->rewriter->consume()->token ?? '';
3024	$op_subject = strtoupper( $op_raw_subject );
3025	$mysql_index_type = $this->normalize_mysql_index_type( $op_subject );
3026	$is_index_op = (bool) $mysql_index_type;
3027	$on_update = false;
3028
3029	if ( 'ADD' === $op_type && ! $is_index_op ) {
3030	if ( 'COLUMN' === $op_subject ) {
3031	$column_name = $this->rewriter->consume()->value;
3032	} else {
3033	$column_name = $op_subject;
3034	}
3035
3036	$skip_mysql_data_type_parts = $this->skip_mysql_data_type();
3037	$sqlite_data_type = $skip_mysql_data_type_parts[0];
3038	$mysql_data_type = $skip_mysql_data_type_parts[1];
3039
3040	$this->rewriter->add(
3041	new WP_SQLite_Token(
3042	$sqlite_data_type,
3043	WP_SQLite_Token::TYPE_KEYWORD,
3044	WP_SQLite_Token::FLAG_KEYWORD_DATA_TYPE
3045	)
3046	);
3047
3048	$comma = $this->rewriter->peek(
3049	array(
3050	'type' => WP_SQLite_Token::TYPE_OPERATOR,
3051	'value' => ',',
3052	)
3053	);
3054
3055	// Handle "ON UPDATE CURRENT_TIMESTAMP".
3056	$on_update_token = $this->rewriter->peek(
3057	array(
3058	'type' => WP_SQLite_Token::TYPE_KEYWORD,
3059	'value' => array( 'ON UPDATE' ),
3060	)
3061	);
3062
3063	if ( $on_update_token && ( ! $comma \|\| $on_update_token->position < $comma->position ) ) {
3064	$this->rewriter->consume(
3065	array(
3066	'type' => WP_SQLite_Token::TYPE_KEYWORD,
3067	'value' => array( 'ON UPDATE' ),
3068	)
3069	);
3070	if ( $this->rewriter->peek()->matches(
3071	WP_SQLite_Token::TYPE_KEYWORD,
3072	WP_SQLite_Token::FLAG_KEYWORD_RESERVED,
3073	array( 'CURRENT_TIMESTAMP' )
3074	) ) {
3075	$this->rewriter->drop_last();
3076	$this->rewriter->skip();
3077	$on_update = $column_name;
3078	}
3079	}
3080
3081	// Drop "FIRST" and "AFTER <another-column>", as these are not supported in SQLite.
3082	$column_position = $this->rewriter->peek(
3083	array(
3084	'type' => WP_SQLite_Token::TYPE_KEYWORD,
3085	'value' => array( 'FIRST', 'AFTER' ),
3086	)
3087	);
3088
3089	if ( $column_position && ( ! $comma \|\| $column_position->position < $comma->position ) ) {
3090	$this->rewriter->consume(
3091	array(
3092	'type' => WP_SQLite_Token::TYPE_KEYWORD,
3093	'value' => array( 'FIRST', 'AFTER' ),
3094	)
3095	);
3096	$this->rewriter->drop_last();
3097	if ( 'AFTER' === strtoupper( $column_position->value ) ) {
3098	$this->rewriter->skip();
3099	}
3100	}
3101
3102	$this->update_data_type_cache(
3103	$this->table_name,
3104	$column_name,
3105	$mysql_data_type
3106	);
3107	} elseif ( 'DROP' === $op_type && ! $is_index_op ) {
3108	$this->rewriter->consume_all();
3109	} elseif ( 'CHANGE' === $op_type ) {
3110	// Parse the new column definition.
3111	$raw_from_name = 'COLUMN' === $op_subject ? $this->rewriter->skip()->token : $op_raw_subject;
3112	$from_name = $this->normalize_column_name( $raw_from_name );
3113	$new_field = $this->parse_mysql_create_table_field();
3114	$alter_terminator = end( $this->rewriter->output_tokens );
3115	$this->update_data_type_cache(
3116	$this->table_name,
3117	$new_field->name,
3118	$new_field->mysql_data_type
3119	);
3120
3121	// Drop ON UPDATE trigger by the old column name.
3122	$on_update_trigger_name = $this->get_column_on_update_current_timestamp_trigger_name( $this->table_name, $from_name );
3123	$this->execute_sqlite_query( "DROP TRIGGER IF EXISTS \"$on_update_trigger_name\"" );
3124
3125	/*
3126	* In SQLite, there is no direct equivalent to the CHANGE COLUMN
3127	* statement from MySQL. We need to do a bit of work to emulate it.
3128	*
3129	* The idea is to:
3130	* 1. Get the existing table schema.
3131	* 2. Adjust the column definition.
3132	* 3. Copy the data out of the old table.
3133	* 4. Drop the old table to free up the indexes names.
3134	* 5. Create a new table from the updated schema.
3135	* 6. Copy the data from step 3 to the new table.
3136	* 7. Drop the old table copy.
3137	* 8. Restore any indexes that were dropped in step 4.
3138	*/
3139
3140	// 1. Get the existing table schema.
3141	$old_schema = $this->get_sqlite_create_table( $this->table_name );
3142	$old_indexes = $this->get_keys( $this->table_name, false );
3143
3144	// 2. Adjust the column definition.
3145
3146	// First, tokenize the old schema.
3147	$tokens = ( new WP_SQLite_Lexer( $old_schema ) )->tokens;
3148	$create_table = new WP_SQLite_Query_Rewriter( $tokens );
3149
3150	// Now, replace every reference to the old column name with the new column name.
3151	while ( true ) {
3152	$token = $create_table->consume();
3153	if ( ! $token ) {
3154	break;
3155	}
3156	if ( WP_SQLite_Token::TYPE_STRING !== $token->type
3157	\|\| $from_name !== $this->normalize_column_name( $token->value ) ) {
3158	continue;
3159	}
3160
3161	// We found the old column name, let's remove it.
3162	$create_table->drop_last();
3163
3164	// If the next token is a data type, we're dealing with a column definition.
3165	$is_column_definition = $create_table->peek()->matches(
3166	WP_SQLite_Token::TYPE_KEYWORD,
3167	WP_SQLite_Token::FLAG_KEYWORD_DATA_TYPE
3168	);
3169	if ( $is_column_definition ) {
3170	// Skip the old field definition.
3171	$field_depth = $create_table->depth;
3172	do {
3173	$field_terminator = $create_table->skip();
3174	} while (
3175	! $this->is_create_table_field_terminator(
3176	$field_terminator,
3177	$field_depth,
3178	$create_table->depth
3179	)
3180	);
3181
3182	// Add an updated field definition.
3183	$definition = $this->make_sqlite_field_definition( $new_field );
3184	// Technically it's not a token, but it's fine to cheat a little bit.
3185	$create_table->add( new WP_SQLite_Token( $definition, WP_SQLite_Token::TYPE_KEYWORD ) );
3186	// Restore the terminating "," or ")" token.
3187	$create_table->add( $field_terminator );
3188	} else {
3189	// Otherwise, just add the new name in place of the old name we dropped.
3190	$create_table->add(
3191	new WP_SQLite_Token(
3192	"`$new_field->name`",
3193	WP_SQLite_Token::TYPE_KEYWORD
3194	)
3195	);
3196	}
3197	}
3198
3199	// 3. Copy the data out of the old table
3200	$cache_table_name = "_tmp__{$this->table_name}_" . rand( 10000000, 99999999 );
3201	$this->execute_sqlite_query(
3202	"CREATE TABLE `$cache_table_name` as SELECT * FROM `$this->table_name`"
3203	);
3204
3205	// 4. Drop the old table to free up the indexes names
3206	$this->execute_sqlite_query( "DROP TABLE `$this->table_name`" );
3207
3208	// 5. Create a new table from the updated schema
3209	$this->execute_sqlite_query( $create_table->get_updated_query() );
3210
3211	// 6. Copy the data from step 3 to the new table
3212	$this->execute_sqlite_query( "INSERT INTO {$this->table_name} SELECT * FROM $cache_table_name" );
3213
3214	// 7. Drop the old table copy
3215	$this->execute_sqlite_query( "DROP TABLE `$cache_table_name`" );
3216
3217	// 8. Restore any indexes that were dropped in step 4
3218	foreach ( $old_indexes as $row ) {
3219	/*
3220	* Skip indexes prefixed with sqlite_autoindex_
3221	* (these are automatically created by SQLite).
3222	*/
3223	if ( str_starts_with( $row['index']['name'], 'sqlite_autoindex_' ) ) {
3224	continue;
3225	}
3226
3227	$columns = array();
3228	foreach ( $row['columns'] as $column ) {
3229	$columns[] = ( $column['name'] === $from_name )
3230	? '`' . $new_field->name . '`'
3231	: '`' . $column['name'] . '`';
3232	}
3233
3234	$unique = '1' === $row['index']['unique'] ? 'UNIQUE' : '';
3235
3236	/*
3237	* Use IF NOT EXISTS to avoid collisions with indexes that were
3238	* a part of the CREATE TABLE statement
3239	*/
3240	$this->execute_sqlite_query(
3241	"CREATE $unique INDEX IF NOT EXISTS `{$row['index']['name']}` ON $this->table_name (" . implode( ', ', $columns ) . ')'
3242	);
3243	}
3244
3245	// Add the ON UPDATE trigger if needed.
3246	if ( $new_field->on_update ) {
3247	$this->add_column_on_update_current_timestamp( $this->table_name, $new_field->name );
3248	}
3249
3250	if ( ',' === $alter_terminator->token ) {
3251	/*
3252	* If the terminator was a comma,
3253	* we need to continue processing the rest of the ALTER query.
3254	*/
3255	$comma = true;
3256	continue;
3257	}
3258	// We're done.
3259	break;
3260	} elseif ( 'ADD' === $op_type && $is_index_op ) {
3261	$key_name = $this->rewriter->consume()->value;
3262	$sqlite_index_type = $this->mysql_index_type_to_sqlite_type( $mysql_index_type );
3263	$sqlite_index_name = $this->generate_index_name( $this->table_name, $key_name );
3264	$this->rewriter->replace_all(
3265	array(
3266	new WP_SQLite_Token( 'CREATE', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
3267	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3268	new WP_SQLite_Token( $sqlite_index_type, WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
3269	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3270	new WP_SQLite_Token( "\"$sqlite_index_name\"", WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
3271	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3272	new WP_SQLite_Token( 'ON', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
3273	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3274	new WP_SQLite_Token( '"' . $this->table_name . '"', WP_SQLite_Token::TYPE_STRING, WP_SQLite_Token::FLAG_STRING_DOUBLE_QUOTES ),
3275	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3276	new WP_SQLite_Token( '(', WP_SQLite_Token::TYPE_OPERATOR ),
3277	)
3278	);
3279	$this->update_data_type_cache(
3280	$this->table_name,
3281	$sqlite_index_name,
3282	$mysql_index_type
3283	);
3284
3285	$token = $this->rewriter->consume(
3286	array(
3287	WP_SQLite_Token::TYPE_OPERATOR,
3288	null,
3289	'(',
3290	)
3291	);
3292	$this->rewriter->drop_last();
3293
3294	// Consume all the fields, skip the sizes like `(20)` in `varchar(20)`.
3295	while ( true ) {
3296	$token = $this->rewriter->consume();
3297	if ( ! $token ) {
3298	break;
3299	}
3300	// $token is field name.
3301	if ( ! $token->matches( WP_SQLite_Token::TYPE_OPERATOR ) ) {
3302	$token->token = '`' . $this->normalize_column_name( $token->token ) . '`';
3303	$token->value = '`' . $this->normalize_column_name( $token->token ) . '`';
3304	}
3305
3306	/*
3307	* Optionally, it may be followed by a size like `(20)`.
3308	* Let's skip it.
3309	*/
3310	$paren_maybe = $this->rewriter->peek();
3311	if ( $paren_maybe && '(' === $paren_maybe->token ) {
3312	$this->rewriter->skip();
3313	$this->rewriter->skip();
3314	$this->rewriter->skip();
3315	}
3316	if ( ')' === $token->value ) {
3317	break;
3318	}
3319	}
3320	} elseif ( 'DROP' === $op_type && $is_index_op ) {
3321	$key_name = $this->rewriter->consume()->value;
3322	$this->rewriter->replace_all(
3323	array(
3324	new WP_SQLite_Token( 'DROP', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
3325	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3326	new WP_SQLite_Token( 'INDEX', WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_RESERVED ),
3327	new WP_SQLite_Token( ' ', WP_SQLite_Token::TYPE_WHITESPACE ),
3328	new WP_SQLite_Token( "\"{$this->table_name}__$key_name\"", WP_SQLite_Token::TYPE_KEYWORD, WP_SQLite_Token::FLAG_KEYWORD_KEY ),
3329	)
3330	);
3331	} else {
3332	throw new Exception( 'Unknown operation: ' . $op_type );
3333	}
3334	$comma = $this->rewriter->consume(
3335	array(
3336	'type' => WP_SQLite_Token::TYPE_OPERATOR,
3337	'value' => ',',
3338	)
3339	);
3340	$this->rewriter->drop_last();
3341
3342	$this->execute_sqlite_query(
3343	$this->rewriter->get_updated_query()
3344	);
3345
3346	if ( $on_update ) {
3347	$this->add_column_on_update_current_timestamp( $this->table_name, $on_update );
3348	}
3349	} while ( $comma );
3350
3351	$this->results = 1;
3352	$this->return_value = $this->results;
3353	}
3354
3355	/**
3356	* Translates a CREATE query.
3357	*
3358	* @throws Exception If the query is an unknown create type.
3359	*/
3360	private function execute_create() {
3361	$this->rewriter->consume();
3362	$what = $this->rewriter->consume()->token;
3363
3364	/**
3365	* Technically it is possible to support temporary tables as follows:
3366	* ATTACH '' AS 'tempschema';
3367	* CREATE TABLE tempschema.<name>(...)...;
3368	* However, for now, let's just ignore the TEMPORARY keyword.
3369	*/
3370	if ( 'TEMPORARY' === $what ) {
3371	$this->rewriter->drop_last();
3372	$what = $this->rewriter->consume()->token;
3373	}
3374
3375	switch ( $what ) {
3376	case 'TABLE':
3377	$this->execute_create_table();
3378	break;
3379
3380	case 'PROCEDURE':
3381	case 'DATABASE':
3382	$this->results = true;
3383	break;
3384
3385	default:
3386	throw new Exception( 'Unknown create type: ' . $what );
3387	}
3388	}
3389
3390	/**
3391	* Translates a DROP query.
3392	*
3393	* @throws Exception If the query is an unknown drop type.
3394	*/
3395	private function execute_drop() {
3396	$this->rewriter->consume();
3397	$what = $this->rewriter->consume()->token;
3398
3399	/*
3400	* Technically it is possible to support temporary tables as follows:
3401	* ATTACH '' AS 'tempschema';
3402	* CREATE TABLE tempschema.<name>(...)...;
3403	* However, for now, let's just ignore the TEMPORARY keyword.
3404	*/
3405	if ( 'TEMPORARY' === $what ) {
3406	$this->rewriter->drop_last();
3407	$what = $this->rewriter->consume()->token;
3408	}
3409
3410	switch ( $what ) {
3411	case 'TABLE':
3412	$this->rewriter->consume_all();
3413	$this->execute_sqlite_query( $this->rewriter->get_updated_query() );
3414	$this->results = $this->last_exec_returned;
3415	break;
3416
3417	case 'PROCEDURE':
3418	case 'DATABASE':
3419	$this->results = true;
3420	return;
3421
3422	default:
3423	throw new Exception( 'Unknown drop type: ' . $what );
3424	}
3425	}
3426
3427	/**
3428	* Translates a SHOW query.
3429	*
3430	* @throws Exception If the query is an unknown show type.
3431	*/
3432	private function execute_show() {
3433	$this->rewriter->skip();
3434	$what1 = strtoupper( $this->rewriter->consume()->token ?? '' );
3435	$what2 = strtoupper( $this->rewriter->consume()->token ?? '' );
3436	$what = $what1 . ' ' . $what2;
3437	switch ( $what ) {
3438	case 'CREATE PROCEDURE':
3439	$this->results = true;
3440	return;
3441
3442	case 'GRANTS FOR':
3443	$this->set_results_from_fetched_data(
3444	array(
3445	(object) array(
3446	'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',
3447	),
3448	)
3449	);
3450	return;
3451
3452	case 'FULL COLUMNS':
3453	$this->rewriter->consume();
3454	// Fall through.
3455	case 'COLUMNS FROM':
3456	$table_name = $this->rewriter->consume()->token;
3457
3458	$this->set_results_from_fetched_data( $this->get_columns_from( $table_name ) );
3459	return;
3460
3461	case 'INDEX FROM':
3462	$table_name = $this->rewriter->consume()->token;
3463	$results = array();
3464
3465	foreach ( $this->get_primary_keys( $table_name ) as $row ) {
3466	$results[] = array(
3467	'Table' => $table_name,
3468	'Non_unique' => '0',
3469	'Key_name' => 'PRIMARY',
3470	'Column_name' => $row['name'],
3471	);
3472	}
3473	foreach ( $this->get_keys( $table_name ) as $row ) {
3474	foreach ( $row['columns'] as $k => $column ) {
3475	$results[] = array(
3476	'Table' => $table_name,
3477	'Non_unique' => '1' === $row['index']['unique'] ? '0' : '1',
3478	'Key_name' => $row['index']['name'],
3479	'Column_name' => $column['name'],
3480	);
3481	}
3482	}
3483	for ( $i = 0;$i < count( $results );$i++ ) {
3484	$sqlite_key_name = $results[ $i ]['Key_name'];
3485	$mysql_key_name = $sqlite_key_name;
3486
3487	/*
3488	* SQLite automatically assigns names to some indexes.
3489	* However, dbDelta in WordPress expects the name to be
3490	* the same as in the original CREATE TABLE. Let's
3491	* translate the name back.
3492	*/
3493	if ( str_starts_with( $mysql_key_name, 'sqlite_autoindex_' ) ) {
3494	$mysql_key_name = substr( $mysql_key_name, strlen( 'sqlite_autoindex_' ) );
3495	$mysql_key_name = preg_replace( '/_[0-9]+$/', '', $mysql_key_name );
3496	}
3497	if ( str_starts_with( $mysql_key_name, "{$table_name}__" ) ) {
3498	$mysql_key_name = substr( $mysql_key_name, strlen( "{$table_name}__" ) );
3499	}
3500
3501	$mysql_type = $this->get_cached_mysql_data_type( $table_name, $sqlite_key_name );
3502	if ( 'FULLTEXT' !== $mysql_type && 'SPATIAL' !== $mysql_type ) {
3503	$mysql_type = 'BTREE';
3504	}
3505
3506	$results[ $i ] = (object) array_merge(
3507	$results[ $i ],
3508	array(
3509	'Seq_in_index' => 0,
3510	'Key_name' => $mysql_key_name,
3511	'Index_type' => $mysql_type,
3512
3513	/*
3514	* Many of these details are not available in SQLite,
3515	* so we just shim them with dummy values.
3516	*/
3517	'Collation' => 'A',
3518	'Cardinality' => '0',
3519	'Sub_part' => null,
3520	'Packed' => null,
3521	'Null' => '',
3522	'Comment' => '',
3523	'Index_comment' => '',
3524	)
3525	);
3526	}
3527	$this->set_results_from_fetched_data(
3528	$results
3529	);
3530
3531	return;
3532
3533	case 'CREATE TABLE':
3534	$this->generate_create_statement();
3535	return;
3536
3537	case 'TABLE STATUS': // FROM `database`.
3538	// Match the optional [{FROM \| IN} db_name].
3539	$database_expression = $this->rewriter->consume();
3540	if ( 'FROM' === $database_expression->token \|\| 'IN' === $database_expression->token ) {
3541	$this->rewriter->consume();
3542	$database_expression = $this->rewriter->consume();
3543	}
3544
3545	$pattern = '%';
3546	// [LIKE 'pattern' \| WHERE expr]
3547	if ( 'LIKE' === $database_expression->token ) {
3548	$pattern = $this->rewriter->consume()->value;
3549	} elseif ( 'WHERE' === $database_expression->token ) {
3550	// @TODO Support me please.
3551	} elseif ( ';' !== $database_expression->token ) {
3552	throw new Exception( 'Syntax error: Unexpected token ' . $database_expression->token . ' in query ' . $this->mysql_query );
3553	}
3554
3555	$database_expression = $this->rewriter->skip();
3556	$stmt = $this->execute_sqlite_query(
3557	"SELECT
3558	name as `Name`,
3559	'myisam' as `Engine`,
3560	10 as `Version`,
3561	'Fixed' as `Row_format`,
3562	0 as `Rows`,
3563	0 as `Avg_row_length`,
3564	0 as `Data_length`,
3565	0 as `Max_data_length`,
3566	0 as `Index_length`,
3567	0 as `Data_free` ,
3568	0 as `Auto_increment`,
3569	'2024-03-20 15:33:20' as `Create_time`,
3570	'2024-03-20 15:33:20' as `Update_time`,
3571	null as `Check_time`,
3572	null as `Collation`,
3573	null as `Checksum`,
3574	'' as `Create_options`,
3575	'' as `Comment`
3576	FROM sqlite_master
3577	WHERE
3578	type='table'
3579	AND name LIKE :pattern
3580	ORDER BY name",
3581	array(
3582	':pattern' => $pattern,
3583	)
3584	);
3585	$tables = $this->strip_sqlite_system_tables( $stmt->fetchAll( $this->pdo_fetch_mode ) );
3586	foreach ( $tables as $table ) {
3587	$table_name = $table->Name; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
3588	$stmt = $this->execute_sqlite_query( "SELECT COUNT(1) as `Rows` FROM $table_name" );
3589	$rows = $stmt->fetchall( $this->pdo_fetch_mode );
3590	$table->Rows = $rows[0]->Rows; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
3591	}
3592
3593	$this->set_results_from_fetched_data(
3594	$this->strip_sqlite_system_tables( $tables )
3595	);
3596
3597	return;
3598
3599	case 'TABLES LIKE':
3600	$table_expression = $this->rewriter->skip();
3601	$stmt = $this->execute_sqlite_query(
3602	"SELECT `name` as `Tables_in_db` FROM `sqlite_master` WHERE `type`='table' AND `name` LIKE :param;",
3603	array(
3604	':param' => $table_expression->value,
3605	)
3606	);
3607
3608	$this->set_results_from_fetched_data(
3609	$stmt->fetchAll( $this->pdo_fetch_mode )
3610	);
3611	return;
3612
3613	default:
3614	switch ( $what1 ) {
3615	case 'TABLES':
3616	$stmt = $this->execute_sqlite_query(
3617	"SELECT name FROM sqlite_master WHERE type='table'"
3618	);
3619	$this->set_results_from_fetched_data(
3620	$stmt->fetchAll( $this->pdo_fetch_mode )
3621	);
3622	return;
3623
3624	case 'VARIABLE':
3625	case 'VARIABLES':
3626	$this->results = true;
3627	return;
3628
3629	default:
3630	throw new Exception( 'Unknown show type: ' . $what );
3631	}
3632	}
3633	}
3634
3635	/**
3636	* Generates a MySQL compatible create statement for a SHOW CREATE TABLE query.
3637	*
3638	* @return void
3639	*/
3640	private function generate_create_statement() {
3641	$table_name = $this->rewriter->consume()->value;
3642	$columns = $this->get_table_columns( $table_name );
3643
3644	if ( empty( $columns ) ) {
3645	$this->set_results_from_fetched_data( array() );
3646	return;
3647	}
3648
3649	$column_definitions = $this->get_column_definitions( $table_name, $columns );
3650	$key_definitions = $this->get_key_definitions( $table_name, $columns );
3651	$pk_definition = $this->get_primary_key_definition( $columns );
3652
3653	if ( $pk_definition ) {
3654	array_unshift( $key_definitions, $pk_definition );
3655	}
3656
3657	$sql_parts = array(
3658	"CREATE TABLE `$table_name` (",
3659	"\t" . implode( ",\n\t", array_merge( $column_definitions, $key_definitions ) ),
3660	');',
3661	);
3662
3663	$this->set_results_from_fetched_data(
3664	array(
3665	(object) array(
3666	'Create Table' => implode( "\n", $sql_parts ),
3667	),
3668	)
3669	);
3670	}
3671
3672	/**
3673	* Get raw columns details from pragma table info for the given table.
3674	*
3675	* @param string $table_name
3676	*
3677	* @return stdClass[]
3678	*/
3679	protected function get_table_columns( $table_name ) {
3680	return $this->execute_sqlite_query( "PRAGMA table_info(\"$table_name\");" )
3681	->fetchAll( $this->pdo_fetch_mode );
3682	}
3683
3684	/**
3685	* Get the column definitions for a create statement
3686	*
3687	* @param string $table_name
3688	* @param array $columns
3689	*
3690	* @return array An array of column definitions
3691	*/
3692	protected function get_column_definitions( $table_name, $columns ) {
3693	$auto_increment_column = $this->get_autoincrement_column( $table_name );
3694	$column_definitions = array();
3695	foreach ( $columns as $column ) {
3696	$is_auto_incr = $auto_increment_column && strtolower( $auto_increment_column ) === strtolower( $column->name );
3697	$definition = array();
3698	$definition[] = '`' . $column->name . '`';
3699	$definition[] = $this->get_cached_mysql_data_type( $table_name, $column->name ) ?? $column->name;
3700
3701	if ( '1' === $column->notnull ) {
3702	$definition[] = 'NOT NULL';
3703	}
3704
3705	if ( null !== $column->dflt_value && '' !== $column->dflt_value && ! $is_auto_incr ) {
3706	$definition[] = 'DEFAULT ' . $column->dflt_value;
3707	}
3708
3709	if ( $is_auto_incr ) {
3710	$definition[] = 'AUTO_INCREMENT';
3711	}
3712	$column_definitions[] = implode( ' ', $definition );
3713	}
3714
3715	return $column_definitions;
3716	}
3717
3718	/**
3719	* Get the key definitions for a create statement
3720	*
3721	* @param string $table_name
3722	* @param array $columns
3723	*
3724	* @return array An array of key definitions
3725	*/
3726	private function get_key_definitions( $table_name, $columns ) {
3727	$key_definitions = array();
3728
3729	$pks = array();
3730	foreach ( $columns as $column ) {
3731	if ( '0' !== $column->pk ) {
3732	$pks[] = $column->name;
3733	}
3734	}
3735
3736	foreach ( $this->get_keys( $table_name ) as $key ) {
3737	// If the PK columns are the same as the unique key columns, skip the key.
3738	// This is because the PK is already unique in MySQL.
3739	$key_equals_pk = ! array_diff( $pks, array_column( $key['columns'], 'name' ) );
3740	$is_auto_index = strpos( $key['index']['name'], 'sqlite_autoindex_' ) === 0;
3741	if ( $is_auto_index && $key['index']['unique'] && $key_equals_pk ) {
3742	continue;
3743	}
3744
3745	$key_definition = array();
3746	if ( $key['index']['unique'] ) {
3747	$key_definition[] = 'UNIQUE';
3748	}
3749
3750	$key_definition[] = 'KEY';
3751
3752	// Remove the prefix from the index name if there is any. We use __ as a separator.
3753	$index_name = explode( '__', $key['index']['name'], 2 )[1] ?? $key['index']['name'];
3754
3755	$key_definition[] = sprintf( '`%s`', $index_name );
3756
3757	$cols = array_map(
3758	function ( $column ) {
3759	return sprintf( '`%s`', $column['name'] );
3760	},
3761	$key['columns']
3762	);
3763
3764	$key_definition[] = '(' . implode( ', ', $cols ) . ')';
3765
3766	$key_definitions[] = implode( ' ', $key_definition );
3767	}
3768
3769	return $key_definitions;
3770	}
3771
3772	/**
3773	* Get the definition for the primary key(s) of a table.
3774	*
3775	* @param array $columns result from PRAGMA table_info() query
3776	*
3777	* @return string\|null definition for the primary key(s)
3778	*/
3779	private function get_primary_key_definition( $columns ) {
3780	$primary_keys = array();
3781
3782	// Sort the columns by primary key order.
3783	usort(
3784	$columns,
3785	function ( $a, $b ) {
3786	return $a->pk - $b->pk;
3787	}
3788	);
3789
3790	foreach ( $columns as $column ) {
3791	if ( '0' !== $column->pk ) {
3792	$primary_keys[] = sprintf( '`%s`', $column->name );
3793	}
3794	}
3795
3796	return ! empty( $primary_keys )
3797	? sprintf( 'PRIMARY KEY (%s)', implode( ', ', $primary_keys ) )
3798	: null;
3799	}
3800
3801	/**
3802	* Get the auto-increment column from a table.
3803	*
3804	* @param $table_name
3805	*
3806	* @return string\|null
3807	*/
3808	private function get_autoincrement_column( $table_name ) {
3809	preg_match(
3810	'/"([^"]+)"\s+integer\s+primary\s+key\s+autoincrement/i',
3811	$this->get_sqlite_create_table( $table_name ),
3812	$matches
3813	);
3814
3815	return $matches[1] ?? null;
3816	}
3817
3818	/**
3819	* Gets the columns from a table for the SHOW COLUMNS query.
3820	*
3821	* The output is identical to the output of the MySQL `SHOW COLUMNS` query.
3822	*
3823	* @param string $table_name The table name.
3824	*
3825	* @return array The columns.
3826	*/
3827	private function get_columns_from( $table_name ) {
3828	/* @todo we may need to add the Extra column if anybdy needs it. 'auto_increment' is the value */
3829	$name_map = array(
3830	'name' => 'Field',
3831	'type' => 'Type',
3832	'dflt_value' => 'Default',
3833	'cid' => null,
3834	'notnull' => null,
3835	'pk' => null,
3836	);
3837
3838	return array_map(
3839	function ( $row ) use ( $name_map ) {
3840	$new = array();
3841	$is_object = is_object( $row );
3842	$row = $is_object ? (array) $row : $row;
3843	foreach ( $row as $k => $v ) {
3844	$k = array_key_exists( $k, $name_map ) ? $name_map [ $k ] : $k;
3845	if ( $k ) {
3846	$new[ $k ] = $v;
3847	}
3848	}
3849	if ( array_key_exists( 'notnull', $row ) ) {
3850	$new['Null'] = ( '1' === $row ['notnull'] ) ? 'NO' : 'YES';
3851	}
3852	if ( array_key_exists( 'pk', $row ) ) {
3853	$new['Key'] = ( '1' === $row ['pk'] ) ? 'PRI' : '';
3854	}
3855	return $is_object ? (object) $new : $new;
3856	},
3857	$this->get_table_columns( $table_name )
3858	);
3859	}
3860
3861	/**
3862	* Consumes data types from the query.
3863	*
3864	* @throws Exception If the data type cannot be translated.
3865	*
3866	* @return array The data types.
3867	*/
3868	private function skip_mysql_data_type() {
3869	$type = $this->rewriter->skip();
3870	if ( ! $type->matches(
3871	WP_SQLite_Token::TYPE_KEYWORD,
3872	WP_SQLite_Token::FLAG_KEYWORD_DATA_TYPE
3873	) ) {
3874	throw new Exception( 'Data type expected in MySQL query, unknown token received: ' . $type->value );
3875	}
3876
3877	$mysql_data_type = strtolower( $type->value );
3878	if ( ! isset( $this->field_types_translation[ $mysql_data_type ] ) ) {
3879	throw new Exception( 'MySQL field type cannot be translated to SQLite: ' . $mysql_data_type );
3880	}
3881
3882	$sqlite_data_type = $this->field_types_translation[ $mysql_data_type ];
3883
3884	// Skip the type modifier, e.g. (20) for varchar(20) or (10,2) for decimal(10,2).
3885	$paren_maybe = $this->rewriter->peek();
3886	if ( $paren_maybe && '(' === $paren_maybe->token ) {
3887	$mysql_data_type .= $this->rewriter->skip()->token; // Skip '(' and add it to the data type
3888
3889	// Loop to capture everything until the closing parenthesis ')'
3890	while ( $token = $this->rewriter->skip() ) {
3891	$mysql_data_type .= $token->token;
3892	if ( ')' === $token->token ) {
3893	break;
3894	}
3895	}
3896	}
3897
3898	// Skip the int keyword.
3899	$int_maybe = $this->rewriter->peek();
3900	if ( $int_maybe && $int_maybe->matches(
3901	WP_SQLite_Token::TYPE_KEYWORD,
3902	null,
3903	array( 'UNSIGNED' )
3904	)
3905	) {
3906	$mysql_data_type .= ' ' . $this->rewriter->skip()->token;
3907	}
3908	return array(
3909	$sqlite_data_type,
3910	$mysql_data_type,
3911	);
3912	}
3913
3914	/**
3915	* Updates the data type cache.
3916	*
3917	* @param string $table The table name.
3918	* @param string $column_or_index The column or index name.
3919	* @param string $mysql_data_type The MySQL data type.
3920	*
3921	* @return void
3922	*/
3923	private function update_data_type_cache( $table, $column_or_index, $mysql_data_type ) {
3924	$this->execute_sqlite_query(
3925	'INSERT INTO ' . self::DATA_TYPES_CACHE_TABLE . ' (`table`, `column_or_index`, `mysql_type`)
3926	VALUES (:table, :column, :datatype)
3927	ON CONFLICT(`table`, `column_or_index`) DO UPDATE SET `mysql_type` = :datatype
3928	',
3929	array(
3930	':table' => $table,
3931	':column' => $column_or_index,
3932	':datatype' => $mysql_data_type,
3933	)
3934	);
3935	}
3936
3937	/**
3938	* Gets the cached MySQL data type.
3939	*
3940	* @param string $table The table name.
3941	* @param string $column_or_index The column or index name.
3942	*
3943	* @return string The MySQL data type.
3944	*/
3945	private function get_cached_mysql_data_type( $table, $column_or_index ) {
3946	$stmt = $this->execute_sqlite_query(
3947	'SELECT d.`mysql_type` FROM ' . self::DATA_TYPES_CACHE_TABLE . ' d
3948	WHERE `table`=:table
3949	AND `column_or_index` = :index',
3950	array(
3951	':table' => $table,
3952	':index' => $column_or_index,
3953	)
3954	);
3955	$mysql_type = $stmt->fetchColumn( 0 );
3956	if ( str_ends_with( $mysql_type, ' KEY' ) ) {
3957	$mysql_type = substr( $mysql_type, 0, strlen( $mysql_type ) - strlen( ' KEY' ) );
3958	}
3959	return $mysql_type;
3960	}
3961
3962	/**
3963	* Normalizes a column name.
3964	*
3965	* @param string $column_name The column name.
3966	*
3967	* @return string The normalized column name.
3968	*/
3969	private function normalize_column_name( $column_name ) {
3970	return trim( $column_name, '`\'"' );
3971	}
3972
3973	/**
3974	* Normalizes an index type.
3975	*
3976	* @param string $index_type The index type.
3977	*
3978	* @return string\|null The normalized index type, or null if the index type is not supported.
3979	*/
3980	private function normalize_mysql_index_type( $index_type ) {
3981	$index_type = strtoupper( $index_type );
3982	$index_type = preg_replace( '/INDEX$/', 'KEY', $index_type );
3983	$index_type = preg_replace( '/ KEY$/', '', $index_type );
3984	if (
3985	'KEY' === $index_type
3986	\|\| 'PRIMARY' === $index_type
3987	\|\| 'UNIQUE' === $index_type
3988	\|\| 'FULLTEXT' === $index_type
3989	\|\| 'SPATIAL' === $index_type
3990	) {
3991	return $index_type;
3992	}
3993	return null;
3994	}
3995
3996	/**
3997	* Converts an index type to a SQLite index type.
3998	*
3999	* @param string\|null $normalized_mysql_index_type The normalized index type.
4000	*
4001	* @return string\|null The SQLite index type, or null if the index type is not supported.
4002	*/
4003	private function mysql_index_type_to_sqlite_type( $normalized_mysql_index_type ) {
4004	if ( null === $normalized_mysql_index_type ) {
4005	return null;
4006	}
4007	if ( 'PRIMARY' === $normalized_mysql_index_type ) {
4008	return 'PRIMARY KEY';
4009	}
4010	if ( 'UNIQUE' === $normalized_mysql_index_type ) {
4011	return 'UNIQUE INDEX';
4012	}
4013	return 'INDEX';
4014	}
4015
4016	/**
4017	* Executes a CHECK statement.
4018	*/
4019	private function execute_check() {
4020	$this->rewriter->skip(); // CHECK.
4021	$this->rewriter->skip(); // TABLE.
4022	$table_name = $this->rewriter->consume()->value; // Τable_name.
4023
4024	$tables =
4025	$this->execute_sqlite_query(
4026	"SELECT name as `table_name` FROM sqlite_master WHERE type='table' AND name = :table_name ORDER BY name",
4027	array( $table_name )
4028	)->fetchAll();
4029
4030	if ( is_array( $tables ) && 1 === count( $tables ) && $table_name === $tables[0]['table_name'] ) {
4031
4032	$this->set_results_from_fetched_data(
4033	array(
4034	(object) array(
4035	'Table' => $table_name,
4036	'Op' => 'check',
4037	'Msg_type' => 'status',
4038	'Msg_text' => 'OK',
4039	),
4040	)
4041	);
4042	} else {
4043
4044	$this->set_results_from_fetched_data(
4045	array(
4046	(object) array(
4047	'Table' => $table_name,
4048	'Op' => 'check',
4049	'Msg_type' => 'Error',
4050	'Msg_text' => "Table '$table_name' doesn't exist",
4051	),
4052	(object) array(
4053	'Table' => $table_name,
4054	'Op' => 'check',
4055	'Msg_type' => 'status',
4056	'Msg_text' => 'Operation failed',
4057	),
4058	)
4059	);
4060	}
4061	}
4062
4063	/**
4064	* Handle an OPTIMIZE / REPAIR / ANALYZE TABLE statement, by using VACUUM just once, at shutdown.
4065	*
4066	* @param string $query_type The query type.
4067	*/
4068	private function execute_optimize( $query_type ) {
4069	// OPTIMIZE TABLE tablename.
4070	$this->rewriter->skip();
4071	$this->rewriter->skip();
4072	$table_name = $this->rewriter->skip()->value;
4073	$status = '';
4074
4075	if ( ! $this->vacuum_requested ) {
4076	$this->vacuum_requested = true;
4077	if ( function_exists( 'add_action' ) ) {
4078	$status = "SQLite does not support $query_type, doing VACUUM instead";
4079	add_action(
4080	'shutdown',
4081	function () {
4082	$this->execute_sqlite_query( 'VACUUM' );
4083	}
4084	);
4085	} else {
4086	/* add_action isn't available in the unit test environment, and we're deep in a transaction. */
4087	$status = "SQLite unit testing does not support $query_type.";
4088	}
4089	}
4090	$resultset = array(
4091	(object) array(
4092	'Table' => $table_name,
4093	'Op' => strtolower( $query_type ),
4094	'Msg_type' => 'note',
4095	'Msg_text' => $status,
4096	),
4097	(object) array(
4098	'Table' => $table_name,
4099	'Op' => strtolower( $query_type ),
4100	'Msg_type' => 'status',
4101	'Msg_text' => 'OK',
4102	),
4103	);
4104
4105	$this->set_results_from_fetched_data( $resultset );
4106	}
4107
4108	/**
4109	* Error handler.
4110	*
4111	* @param Exception $err Exception object.
4112	*
4113	* @return bool Always false.
4114	*/
4115	private function handle_error( Exception $err ) {
4116	$message = $err->getMessage();
4117	$this->set_error( __LINE__, __FUNCTION__, $message );
4118	$this->return_value = false;
4119	return false;
4120	}
4121
4122	/**
4123	* Method to format the error messages and put out to the file.
4124	*
4125	* When $wpdb::suppress_errors is set to true or $wpdb::show_errors is set to false,
4126	* the error messages are ignored.
4127	*
4128	* @param string $line Where the error occurred.
4129	* @param string $function_name Indicate the function name where the error occurred.
4130	* @param string $message The message.
4131	*
4132	* @return boolean\|void
4133	*/
4134	private function set_error( $line, $function_name, $message ) {
4135	$this->errors[] = array(
4136	'line' => $line,
4137	'function' => $function_name,
4138	);
4139	$this->error_messages[] = $message;
4140	$this->is_error = true;
4141	}
4142
4143	/**
4144	* PDO has no explicit close() method.
4145	*
4146	* This is because PHP may choose to reuse the same
4147	* connection for the next request. The PHP manual
4148	* states the PDO object can only be unset:
4149	*
4150	* https://www.php.net/manual/en/pdo.connections.php#114822
4151	*/
4152	public function close() {
4153	$this->pdo = null;
4154	}
4155
4156	/**
4157	* Method to return error messages.
4158	*
4159	* @throws Exception If error is found.
4160	*
4161	* @return string
4162	*/
4163	public function get_error_message() {
4164	if ( count( $this->error_messages ) === 0 ) {
4165	$this->is_error = false;
4166	$this->error_messages = array();
4167	return '';
4168	}
4169
4170	if ( false === $this->is_error ) {
4171	return '';
4172	}
4173
4174	$output = '<div style="clear:both"> </div>' . PHP_EOL;
4175	$output .= '<div class="queries" style="clear:both;margin-bottom:2px;border:red dotted thin;">' . PHP_EOL;
4176	$output .= '<p>MySQL query:</p>' . PHP_EOL;
4177	$output .= '<p>' . $this->mysql_query . '</p>' . PHP_EOL;
4178	$output .= '<p>Queries made or created this session were:</p>' . PHP_EOL;
4179	$output .= '<ol>' . PHP_EOL;
4180	foreach ( $this->executed_sqlite_queries as $q ) {
4181	$message = "Executing: {$q['sql']} \| " . ( $q['params'] ? 'parameters: ' . implode( ', ', $q['params'] ) : '(no parameters)' );
4182
4183	$output .= '<li>' . htmlspecialchars( $message ) . '</li>' . PHP_EOL;
4184	}
4185	$output .= '</ol>' . PHP_EOL;
4186	$output .= '</div>' . PHP_EOL;
4187	foreach ( $this->error_messages as $num => $m ) {
4188	$output .= '<div style="clear:both;margin-bottom:2px;border:red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">' . PHP_EOL;
4189	$output .= sprintf(
4190	'Error occurred at line %1$d in Function %2$s. Error message was: %3$s.',
4191	(int) $this->errors[ $num ]['line'],
4192	'<code>' . htmlspecialchars( $this->errors[ $num ]['function'] ) . '</code>',
4193	$m
4194	) . PHP_EOL;
4195	$output .= '</div>' . PHP_EOL;
4196	}
4197
4198	try {
4199	throw new Exception();
4200	} catch ( Exception $e ) {
4201	$output .= '<p>Backtrace:</p>' . PHP_EOL;
4202	$output .= '<pre>' . $e->getTraceAsString() . '</pre>' . PHP_EOL;
4203	}
4204
4205	return $output;
4206	}
4207
4208	/**
4209	* Executes a query in SQLite.
4210	*
4211	* @param mixed $sql The query to execute.
4212	* @param mixed $params The parameters to bind to the query.
4213	* @throws PDOException If the query could not be executed.
4214	* @return object {
4215	* The result of the query.
4216	*
4217	* @type PDOStatement $stmt The executed statement
4218	* @type * $result The value returned by $stmt.
4219	* }
4220	*/
4221	public function execute_sqlite_query( $sql, $params = array() ) {
4222	$this->executed_sqlite_queries[] = array(
4223	'sql' => $sql,
4224	'params' => $params,
4225	);
4226
4227	$stmt = $this->pdo->prepare( $sql );
4228	if ( false === $stmt \|\| null === $stmt ) {
4229	$this->last_exec_returned = null;
4230	$info = $this->pdo->errorInfo();
4231	$this->last_sqlite_error = $info[0] . ' ' . $info[2];
4232	throw new PDOException( implode( ' ', array( 'Error:', $info[0], $info[2], 'SQLite:', $sql ) ), $info[1] );
4233	}
4234	$returned = $stmt->execute( $params );
4235	$this->last_exec_returned = $returned;
4236	if ( ! $returned ) {
4237	$info = $stmt->errorInfo();
4238	$this->last_sqlite_error = $info[0] . ' ' . $info[2];
4239	throw new PDOException( implode( ' ', array( 'Error:', $info[0], $info[2], 'SQLite:', $sql ) ), $info[1] );
4240	}
4241
4242	return $stmt;
4243	}
4244
4245	/**
4246	* Method to set the results from the fetched data.
4247	*
4248	* @param array $data The data to set.
4249	*/
4250	private function set_results_from_fetched_data( $data ) {
4251	if ( null === $this->results ) {
4252	$this->results = $data;
4253	}
4254	if ( is_array( $this->results ) ) {
4255	$this->num_rows = count( $this->results );
4256	$this->last_select_found_rows = count( $this->results );
4257	}
4258	$this->return_value = $this->results;
4259	}
4260
4261	/**
4262	* Method to set the results from the affected rows.
4263	*
4264	* @param int\|null $override Override the affected rows.
4265	*/
4266	private function set_result_from_affected_rows( $override = null ) {
4267	/*
4268	* SELECT CHANGES() is a workaround for the fact that
4269	* $stmt->rowCount() returns "0" (zero) with the
4270	* SQLite driver at all times.
4271	* Source: https://www.php.net/manual/en/pdostatement.rowcount.php
4272	*/
4273	if ( null === $override ) {
4274	$this->affected_rows = (int) $this->execute_sqlite_query( 'select changes()' )->fetch()[0];
4275	} else {
4276	$this->affected_rows = $override;
4277	}
4278	$this->return_value = $this->affected_rows;
4279	$this->num_rows = $this->affected_rows;
4280	$this->results = $this->affected_rows;
4281	}
4282
4283	/**
4284	* Method to clear previous data.
4285	*/
4286	private function flush() {
4287	$this->mysql_query = '';
4288	$this->results = null;
4289	$this->last_exec_returned = null;
4290	$this->table_name = null;
4291	$this->last_insert_id = null;
4292	$this->affected_rows = null;
4293	$this->insert_columns = array();
4294	$this->column_data = array();
4295	$this->num_rows = null;
4296	$this->return_value = null;
4297	$this->error_messages = array();
4298	$this->is_error = false;
4299	$this->executed_sqlite_queries = array();
4300	$this->like_expression_nesting = 0;
4301	$this->like_escape_count = 0;
4302	$this->is_information_schema_query = false;
4303	$this->has_group_by = false;
4304	}
4305
4306	/**
4307	* Begin a new transaction or nested transaction.
4308	*
4309	* @return boolean
4310	*/
4311	public function begin_transaction() {
4312	$success = false;
4313	try {
4314	if ( 0 === $this->transaction_level ) {
4315	$this->execute_sqlite_query( 'BEGIN' );
4316	} else {
4317	$this->execute_sqlite_query( 'SAVEPOINT LEVEL' . $this->transaction_level );
4318	}
4319	$success = $this->last_exec_returned;
4320	} finally {
4321	if ( $success ) {
4322	++$this->transaction_level;
4323	if ( function_exists( 'do_action' ) ) {
4324	/**
4325	* Notifies that a transaction-related query has been translated and executed.
4326	*
4327	* @param string $command The SQL statement (one of "START TRANSACTION", "COMMIT", "ROLLBACK").
4328	* @param bool $success Whether the SQL statement was successful or not.
4329	* @param int $nesting_level The nesting level of the transaction.
4330	*
4331	* @since 0.1.0
4332	*/
4333	do_action( 'sqlite_transaction_query_executed', 'START TRANSACTION', (bool) $this->last_exec_returned, $this->transaction_level - 1 );
4334	}
4335	}
4336	}
4337	return $success;
4338	}
4339
4340	/**
4341	* Commit the current transaction or nested transaction.
4342	*
4343	* @return boolean True on success, false on failure.
4344	*/
4345	public function commit() {
4346	if ( 0 === $this->transaction_level ) {
4347	return false;
4348	}
4349
4350	--$this->transaction_level;
4351	if ( 0 === $this->transaction_level ) {
4352	$this->execute_sqlite_query( 'COMMIT' );
4353	} else {
4354	$this->execute_sqlite_query( 'RELEASE SAVEPOINT LEVEL' . $this->transaction_level );
4355	}
4356
4357	if ( function_exists( 'do_action' ) ) {
4358	do_action( 'sqlite_transaction_query_executed', 'COMMIT', (bool) $this->last_exec_returned, $this->transaction_level );
4359	}
4360	return $this->last_exec_returned;
4361	}
4362
4363	/**
4364	* Rollback the current transaction or nested transaction.
4365	*
4366	* @return boolean True on success, false on failure.
4367	*/
4368	public function rollback() {
4369	if ( 0 === $this->transaction_level ) {
4370	return false;
4371	}
4372
4373	--$this->transaction_level;
4374	if ( 0 === $this->transaction_level ) {
4375	$this->execute_sqlite_query( 'ROLLBACK' );
4376	} else {
4377	$this->execute_sqlite_query( 'ROLLBACK TO SAVEPOINT LEVEL' . $this->transaction_level );
4378	}
4379	if ( function_exists( 'do_action' ) ) {
4380	do_action( 'sqlite_transaction_query_executed', 'ROLLBACK', (bool) $this->last_exec_returned, $this->transaction_level );
4381	}
4382	return $this->last_exec_returned;
4383	}
4384
4385	/**
4386	* Create an index name consisting of table name and original index name.
4387	* This is to avoid duplicate index names in SQLite.
4388	*
4389	* @param $table
4390	* @param $original_index_name
4391	*
4392	* @return string
4393	*/
4394	private function generate_index_name( $table, $original_index_name ) {
4395	// Strip the occurrences of 2 or more consecutive underscores from the table name
4396	// to allow easier splitting on __ later.
4397	return preg_replace( '/_{2,}/', '_', $table ) . '__' . $original_index_name;
4398	}
4399
4400	/**
4401	* @param string $table
4402	* @param string $column
4403	*/
4404	private function add_column_on_update_current_timestamp( $table, $column ) {
4405	$trigger_name = $this->get_column_on_update_current_timestamp_trigger_name( $table, $column );
4406
4407	// The trigger wouldn't work for virtual and "WITHOUT ROWID" tables,
4408	// but currently that can't happen as we're not creating such tables.
4409	// See: https://www.sqlite.org/rowidtable.html
4410	$this->execute_sqlite_query(
4411	"CREATE TRIGGER \"$trigger_name\"
4412	AFTER UPDATE ON \"$table\"
4413	FOR EACH ROW
4414	BEGIN
4415	UPDATE \"$table\" SET \"$column\" = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
4416	END"
4417	);
4418	}
4419
4420	/**
4421	* @param string $table
4422	* @param string $column
4423	* @return string
4424	*/
4425	private function get_column_on_update_current_timestamp_trigger_name( $table, $column ) {
4426	return "__{$table}_{$column}_on_update__";
4427	}
4428	}
4429