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

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

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