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

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

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