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

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

4,544 lines 132.0 KB

Raw Download Zip

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