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

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

4,488 lines 128.4 KB

Raw Download Zip

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