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

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

4,487 lines 128.3 KB

Raw Download Zip

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