sqlite-database-integration / wp-includes / database / sqlite / class-wp-sqlite-information-schema-reconstructor.php

class-wp-sqlite-information-schema-reconstructor.php in SQLite Database Integration 2.2.21, at wp-includes/database/sqlite/class-wp-sqlite-information-schema-reconstructor.php

798 lines 27.5 KB

Raw Download Zip

1	<?php
2
3	/*
4	* The SQLite driver uses PDO. Enable PDO function calls:
5	* phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
6	*/
7
8	/**
9	* SQLite information schema recconstructor for MySQL.
10	*
11	* This class checks and reconstructs the MySQL INFORMATION_SCHEMA data in SQLite
12	* when it becomes out of sync with the actual SQLite database schema.
13	*
14	* Currently, it reconstructs schema infromation for missing tables, and removes
15	* stale data for tables that no longer exist. When used with WordPress, it uses
16	* the "wp_get_db_schema()" function to reconstruct WordPress table information.
17	*/
18	class WP_SQLite_Information_Schema_Reconstructor {
19	/**
20	* The SQLite driver instance.
21	*
22	* @var WP_PDO_MySQL_On_SQLite
23	*/
24	private $driver;
25
26	/**
27	* An instance of the SQLite connection.
28	*
29	* @var WP_SQLite_Connection
30	*/
31	private $connection;
32
33	/**
34	* A service for managing MySQL INFORMATION_SCHEMA tables in SQLite.
35	*
36	* @var WP_SQLite_Information_Schema_Builder
37	*/
38	private $schema_builder;
39
40	/**
41	* Constructor.
42	*
43	* @param WP_PDO_MySQL_On_SQLite $driver The SQLite driver instance.
44	* @param WP_SQLite_Information_Schema_Builder $schema_builder The information schema builder instance.
45	*/
46	public function __construct(
47	$driver,
48	WP_SQLite_Information_Schema_Builder $schema_builder
49	) {
50	$this->driver = $driver;
51	$this->connection = $driver->get_connection();
52	$this->schema_builder = $schema_builder;
53	}
54
55	/**
56	* Ensure that the MySQL INFORMATION_SCHEMA data in SQLite is correct.
57	*
58	* This method checks if the MySQL INFORMATION_SCHEMA data in SQLite is correct,
59	* and if it is not, it will reconstruct missing data and remove stale values.
60	*/
61	public function ensure_correct_information_schema(): void {
62	$sqlite_tables = $this->get_sqlite_table_names();
63	$information_schema_tables = $this->get_information_schema_table_names();
64
65	// In WordPress, use "wp_get_db_schema()" to reconstruct WordPress tables.
66	$wp_tables = $this->get_wp_create_table_statements();
67
68	// Reconstruct information schema records for tables that don't have them.
69	foreach ( $sqlite_tables as $table ) {
70	if ( ! in_array( $table, $information_schema_tables, true ) ) {
71	if ( isset( $wp_tables[ $table ] ) ) {
72	// WordPress core table (as returned by "wp_get_db_schema()").
73	$ast = $wp_tables[ $table ];
74	} else {
75	// Other table (a WordPress plugin or unrelated to WordPress).
76	$sql = $this->generate_create_table_statement( $table );
77	$ast = $this->driver->create_parser( $sql )->parse();
78	if ( null === $ast ) {
79	throw new WP_SQLite_Driver_Exception( $this->driver, 'Failed to parse the MySQL query.' );
80	}
81	}
82
83	/*
84	* First, let's make sure we clean up all related data. This fixes
85	* partial data corruption, such as when a table record is missing,
86	* but some related column, index, or constraint records are stored.
87	*/
88	$this->record_drop_table( $table );
89
90	$this->schema_builder->record_create_table( $ast );
91	}
92	}
93
94	// Remove information schema records for tables that don't exist.
95	foreach ( $information_schema_tables as $table ) {
96	if ( ! in_array( $table, $sqlite_tables, true ) ) {
97	$this->record_drop_table( $table );
98	}
99	}
100	}
101
102	/**
103	* Record a DROP TABLE statement in the information schema.
104	*
105	* This removes a table record from the information schema, as well as all
106	* column, index, and constraint records that are related to the table.
107	*
108	* @param string $table_name The name of the table to drop.
109	*/
110	private function record_drop_table( string $table_name ): void {
111	$sql = sprintf( 'DROP TABLE %s', $this->connection->quote_identifier( $table_name ) ); // TODO: mysql quote
112	$ast = $this->driver->create_parser( $sql )->parse();
113	if ( null === $ast ) {
114	throw new WP_SQLite_Driver_Exception( $this->driver, 'Failed to parse the MySQL query.' );
115	}
116	$this->schema_builder->record_drop_table(
117	$ast->get_first_descendant_node( 'dropStatement' )
118	);
119	}
120
121	/**
122	* Get the names of all existing tables in the SQLite database.
123	*
124	* @return string[] The names of tables in the SQLite database.
125	*/
126	private function get_sqlite_table_names(): array {
127	return $this->driver->execute_sqlite_query(
128	"
129	SELECT name
130	FROM sqlite_master
131	WHERE type = 'table'
132	AND name != ?
133	AND name NOT LIKE ? ESCAPE '\'
134	AND name NOT LIKE ? ESCAPE '\'
135	ORDER BY name
136	",
137	array(
138	'_mysql_data_types_cache',
139	'sqlite\_%',
140	str_replace( '_', '\_', WP_PDO_MySQL_On_SQLite::RESERVED_PREFIX ) . '%',
141	)
142	)->fetchAll( PDO::FETCH_COLUMN );
143	}
144
145	/**
146	* Get the names of all tables recorded in the information schema.
147	*
148	* @return string[] The names of tables in the information schema.
149	*/
150	private function get_information_schema_table_names(): array {
151	$tables_table = $this->schema_builder->get_table_name( false, 'tables' );
152	return $this->driver->execute_sqlite_query(
153	sprintf(
154	'SELECT table_name FROM %s ORDER BY table_name',
155	$this->connection->quote_identifier( $tables_table )
156	)
157	)->fetchAll( PDO::FETCH_COLUMN );
158	}
159
160	/**
161	* Get a map of parsed CREATE TABLE statements for WordPress tables.
162	*
163	* When reconstructing the information schema data for WordPress tables, we
164	* can use the "wp_get_db_schema()" function to get accurate CREATE TABLE
165	* statements. This method parses the result of "wp_get_db_schema()" into
166	* an array of parsed CREATE TABLE statements indexed by the table names.
167	*
168	* @return array<string, WP_Parser_Node> The WordPress CREATE TABLE statements.
169	*/
170	private function get_wp_create_table_statements(): array {
171	// Bail out when not in a WordPress environment.
172	if ( ! defined( 'ABSPATH' ) ) {
173	return array();
174	}
175
176	/*
177	* In WP CLI, $wpdb may not be set. In that case, we can't load the schema.
178	* We need to bail out and use the standard non-WordPress-specific behavior.
179	*/
180	global $wpdb;
181	if ( ! isset( $wpdb ) ) {
182	// Outside of WP CLI, let's trigger a warning.
183	if ( ! defined( 'WP_CLI' ) \|\| ! WP_CLI ) {
184	trigger_error( 'The $wpdb global is not initialized.', E_USER_WARNING );
185	}
186	return array();
187	}
188
189	// Ensure the "wp_get_db_schema()" function is defined.
190	if ( file_exists( ABSPATH . 'wp-admin/includes/schema.php' ) ) {
191	require_once ABSPATH . 'wp-admin/includes/schema.php';
192	}
193	if ( ! function_exists( 'wp_get_db_schema' ) ) {
194	throw new Exception( 'The "wp_get_db_schema()" function was not defined.' );
195	}
196
197	/*
198	* At this point, WPDB may not yet be initialized, as we're configuring
199	* the database connection. Let's only populate the table names using
200	* the "$table_prefix" global so we can get correct table names.
201	*/
202	global $table_prefix;
203	$wpdb->set_prefix( $table_prefix );
204
205	// Get schema for global tables.
206	$schema = wp_get_db_schema( 'global' );
207
208	// For multisite installs, add schema definitions for all sites.
209	if ( is_multisite() ) {
210	/*
211	* We need to use a database query over the "get_sites()" function,
212	* as WPDB may not yet initialized. Moreover, we need to get the IDs
213	* of all existing blogs, independent of any filters and actions that
214	* could possibly alter the results of a "get_sites()" call.
215	*/
216	$blog_ids = $this->driver->execute_sqlite_query(
217	sprintf(
218	'SELECT blog_id FROM %s',
219	$this->connection->quote_identifier( $wpdb->blogs )
220	)
221	)->fetchAll( PDO::FETCH_COLUMN );
222	foreach ( $blog_ids as $blog_id ) {
223	$schema .= wp_get_db_schema( 'blog', (int) $blog_id );
224	}
225	} else {
226	// For single site installs, add schema for the main site.
227	$schema .= wp_get_db_schema( 'blog' );
228	}
229
230	// Parse the schema.
231	$parser = $this->driver->create_parser( $schema );
232	$wp_tables = array();
233	while ( $parser->next_query() ) {
234	$ast = $parser->get_query_ast();
235	if ( null === $ast ) {
236	throw new WP_SQLite_Driver_Exception( $this->driver, 'Failed to parse the MySQL query.' );
237	}
238
239	$create_node = $ast->get_first_descendant_node( 'createStatement' );
240	if ( $create_node && $create_node->has_child_node( 'createTable' ) ) {
241	$name_node = $create_node->get_first_descendant_node( 'tableName' );
242	$name = $this->unquote_mysql_identifier(
243	substr( $schema, $name_node->get_start(), $name_node->get_length() )
244	);
245
246	$wp_tables[ $name ] = $create_node;
247	}
248	}
249	return $wp_tables;
250	}
251
252	/**
253	* Generate a MySQL CREATE TABLE statement from an SQLite table definition.
254	*
255	* @param string $table_name The name of the table.
256	* @return string The CREATE TABLE statement.
257	*/
258	private function generate_create_table_statement( string $table_name ): string {
259	// Columns.
260	$columns = $this->driver->execute_sqlite_query(
261	sprintf(
262	'PRAGMA table_xinfo(%s)',
263	$this->connection->quote_identifier( $table_name )
264	)
265	)->fetchAll( PDO::FETCH_ASSOC );
266
267	$definitions = array();
268	$column_types = array();
269	foreach ( $columns as $column ) {
270	$mysql_type = $this->get_cached_mysql_data_type( $table_name, $column['name'] );
271	if ( null === $mysql_type ) {
272	$mysql_type = $this->get_mysql_column_type( $column['type'] );
273	}
274	$definitions[] = $this->generate_column_definition( $table_name, $column );
275	$column_types[ $column['name'] ] = $mysql_type;
276	}
277
278	// Primary key.
279	$pk_columns = array();
280	foreach ( $columns as $column ) {
281	// A position of the column in the primary key, starting from index 1.
282	// A value of 0 means that the column is not part of the primary key.
283	$pk_position = (int) $column['pk'];
284	if ( 0 !== $pk_position ) {
285	$pk_columns[ $pk_position ] = $column['name'];
286	}
287	}
288
289	// Sort the columns by their position in the primary key.
290	ksort( $pk_columns );
291
292	if ( count( $pk_columns ) > 0 ) {
293	$quoted_pk_columns = array();
294	foreach ( $pk_columns as $pk_column ) {
295	$quoted_pk_columns[] = $this->connection->quote_identifier( $pk_column );
296	}
297	$definitions[] = sprintf( 'PRIMARY KEY (%s)', implode( ', ', $quoted_pk_columns ) );
298	}
299
300	// Indexes and keys.
301	$keys = $this->driver->execute_sqlite_query(
302	sprintf(
303	'PRAGMA index_list(%s)',
304	$this->connection->quote_identifier( $table_name )
305	)
306	)->fetchAll( PDO::FETCH_ASSOC );
307
308	foreach ( $keys as $key ) {
309	// Skip the internal index that SQLite may create for a primary key.
310	// In MySQL, no explicit index needs to be defined for a primary key.
311	if ( 'pk' === $key['origin'] ) {
312	continue;
313	}
314	$definitions[] = $this->generate_key_definition( $table_name, $key, $column_types );
315	}
316
317	return sprintf(
318	"CREATE TABLE %s (\n %s\n)",
319	$this->connection->quote_identifier( $table_name ),
320	implode( ",\n ", $definitions )
321	);
322	}
323
324	/**
325	* Generate a MySQL column definition from an SQLite column information.
326	*
327	* This method generates a MySQL column definition from SQLite column data.
328	*
329	* @param string $table_name The name of the table.
330	* @param array $column_info The SQLite column information.
331	* @return string The MySQL column definition.
332	*/
333	private function generate_column_definition( string $table_name, array $column_info ): string {
334	$definition = array();
335	$definition[] = $this->connection->quote_identifier( $column_info['name'] );
336
337	// Data type.
338	$mysql_type = $this->get_cached_mysql_data_type( $table_name, $column_info['name'] );
339	if ( null === $mysql_type ) {
340	$mysql_type = $this->get_mysql_column_type( $column_info['type'] );
341	}
342
343	/**
344	* Correct some column types based on their default values:
345	* 1. In MySQL, non-datetime columns can't have a timestamp default.
346	* Let's use DATETIME when default is set to CURRENT_TIMESTAMP.
347	* 2. In MySQL, TEXT and BLOB columns can't have a default value.
348	* Let's use VARCHAR(65535) and VARBINARY(65535) when default is set.
349	*/
350	$default = $this->generate_column_default( $mysql_type, $column_info['dflt_value'] );
351	if ( 'CURRENT_TIMESTAMP' === $default ) {
352	$mysql_type = 'datetime';
353	} elseif ( 'text' === $mysql_type && null !== $default ) {
354	$mysql_type = 'varchar(65535)';
355	} elseif ( 'blob' === $mysql_type && null !== $default ) {
356	$mysql_type = 'varbinary(65535)';
357	}
358
359	$definition[] = $mysql_type;
360
361	// NULL/NOT NULL.
362	if ( '1' === $column_info['notnull'] ) {
363	$definition[] = 'NOT NULL';
364	}
365
366	// Auto increment.
367	$is_auto_increment = false;
368	if ( '0' !== $column_info['pk'] ) {
369	$is_auto_increment = $this->driver->execute_sqlite_query(
370	'SELECT 1 FROM sqlite_master WHERE tbl_name = ? AND sql LIKE ?',
371	array( $table_name, '%AUTOINCREMENT%' )
372	)->fetchColumn();
373
374	if ( $is_auto_increment ) {
375	$definition[] = 'AUTO_INCREMENT';
376	}
377	}
378
379	// Default value.
380	if ( null !== $default && ! $is_auto_increment ) {
381	$definition[] = 'DEFAULT ' . $default;
382	}
383
384	return implode( ' ', $definition );
385	}
386
387	/**
388	* Generate a MySQL key definition from an SQLite key information.
389	*
390	* This method generates a MySQL key definition from SQLite key data.
391	*
392	* @param string $table_name The name of the table.
393	* @param array $key_info The SQLite key information.
394	* @param array $column_types The MySQL data types of the columns.
395	* @return string The MySQL key definition.
396	*/
397	private function generate_key_definition( string $table_name, array $key_info, array $column_types ): string {
398	$definition = array();
399
400	// Key type.
401	$cached_type = $this->get_cached_mysql_data_type( $table_name, $key_info['name'] );
402	if ( 'FULLTEXT' === $cached_type ) {
403	$definition[] = 'FULLTEXT KEY';
404	} elseif ( 'SPATIAL' === $cached_type ) {
405	$definition[] = 'SPATIAL KEY';
406	} elseif ( 'UNIQUE' === $cached_type \|\| '1' === $key_info['unique'] ) {
407	$definition[] = 'UNIQUE KEY';
408	} else {
409	$definition[] = 'KEY';
410	}
411
412	// Key name.
413	$name = $key_info['name'];
414
415	/*
416	* The SQLite driver prefixes index names with "{$table_name}__" to avoid
417	* naming conflicts among tables in SQLite. We need to remove the prefix.
418	*/
419	if ( str_starts_with( $name, "{$table_name}__" ) ) {
420	$name = substr( $name, strlen( "{$table_name}__" ) );
421	}
422
423	/**
424	* SQLite creates automatic internal indexes for primary and unique keys,
425	* naming them in format "sqlite_autoindex_{$table_name}_{$index_id}".
426	* For these internal indexes, we need to skip their name, so that in
427	* the generated MySQL definition, they follow implicit MySQL naming.
428	*/
429	if ( ! str_starts_with( $name, 'sqlite_autoindex_' ) ) {
430	$definition[] = $this->connection->quote_identifier( $name );
431	}
432
433	// Key columns.
434	$key_columns = $this->driver->execute_sqlite_query(
435	sprintf(
436	'PRAGMA index_info(%s)',
437	$this->connection->quote_identifier( $key_info['name'] )
438	)
439	)->fetchAll( PDO::FETCH_ASSOC );
440	$cols = array();
441	foreach ( $key_columns as $column ) {
442	/*
443	* Extract type and length from column data type definition.
444	*
445	* This is required when the column data type is inferred from the
446	* '_mysql_data_types_cache' table, which stores the data type in
447	* the format "type(length)", such as "varchar(255)".
448	*/
449	$max_prefix_length = 100;
450	$type = strtolower( $column_types[ $column['name'] ] );
451	$parts = explode( '(', $type );
452	$column_type = $parts[0];
453	$column_length = isset( $parts[1] ) ? (int) $parts[1] : null;
454
455	/*
456	* Add an index column prefix length, if needed.
457	*
458	* This is required for "text" and "blob" types for columns inferred
459	* directly from the SQLite schema, and for the following types for
460	* columns inferred from the '_mysql_data_types_cache' table:
461	* char, varchar
462	* text, tinytext, mediumtext, longtext
463	* blob, tinyblob, mediumblob, longblob
464	* varbinary
465	*/
466	if (
467	str_ends_with( $column_type, 'char' )
468	\|\| str_ends_with( $column_type, 'text' )
469	\|\| str_ends_with( $column_type, 'blob' )
470	\|\| str_starts_with( $column_type, 'var' )
471	) {
472	$cols[] = sprintf(
473	'%s(%d)',
474	$this->connection->quote_identifier( $column['name'] ),
475	min( $column_length ?? $max_prefix_length, $max_prefix_length )
476	);
477	} else {
478	$cols[] = $this->connection->quote_identifier( $column['name'] );
479	}
480	}
481
482	$definition[] = '(' . implode( ', ', $cols ) . ')';
483	return implode( ' ', $definition );
484	}
485
486	/**
487	* Generate a MySQL default value from an SQLite default value.
488	*
489	* @param string $mysql_type The MySQL data type of the column.
490	* @param string\|null $default_value The default value of the SQLite column.
491	* @return string\|null The default value, or null if the column has no default value.
492	*/
493	private function generate_column_default( string $mysql_type, ?string $default_value ): ?string {
494	if ( null === $default_value \|\| '' === $default_value ) {
495	return null;
496	}
497	$mysql_type = strtolower( $mysql_type );
498
499	/*
500	* In MySQL, geometry columns can't have a default value.
501	*
502	* Geometry columns are saved as TEXT in SQLite, and in an older version
503	* of the SQLite driver, TEXT columns were assigned a default value of ''.
504	*/
505	if ( 'geomcollection' === $mysql_type \|\| 'geometrycollection' === $mysql_type ) {
506	return null;
507	}
508
509	/*
510	* In MySQL, date/time columns can't have a default value of ''.
511	*
512	* Date/time columns are saved as TEXT in SQLite, and in an older version
513	* of the SQLite driver, TEXT columns were assigned a default value of ''.
514	*/
515	if (
516	"''" === $default_value
517	&& in_array( $mysql_type, array( 'datetime', 'date', 'time', 'timestamp', 'year' ), true )
518	) {
519	return null;
520	}
521
522	/**
523	* Convert SQLite default values to MySQL default values.
524	*
525	* See:
526	* - https://www.sqlite.org/syntax/column-constraint.html
527	* - https://www.sqlite.org/syntax/literal-value.html
528	* - https://www.sqlite.org/lang_expr.html#literal_values_constants_
529	*/
530
531	// Quoted string literal. E.g.: 'abc', "abc", `abc`
532	$first_byte = $default_value[0] ?? null;
533	if ( '"' === $first_byte \|\| "'" === $first_byte \|\| '`' === $first_byte ) {
534	$value = substr( $default_value, 1, -1 );
535	$value = str_replace( $first_byte . $first_byte, $first_byte, $value );
536	return $this->quote_mysql_utf8_string_literal( $value );
537	}
538
539	// Normalize the default value for easier comparison.
540	$uppercase_default_value = strtoupper( $default_value );
541
542	// NULL, TRUE, FALSE.
543	if ( 'NULL' === $uppercase_default_value ) {
544	// DEFAULT NULL is the same as no default value.
545	return null;
546	} elseif ( 'TRUE' === $uppercase_default_value ) {
547	return '1';
548	} elseif ( 'FALSE' === $uppercase_default_value ) {
549	return '0';
550	}
551
552	// Date/time values.
553	if ( 'CURRENT_TIMESTAMP' === $uppercase_default_value ) {
554	return 'CURRENT_TIMESTAMP';
555	} elseif ( 'CURRENT_DATE' === $uppercase_default_value ) {
556	return null; // Not supported in MySQL.
557	} elseif ( 'CURRENT_TIME' === $uppercase_default_value ) {
558	return null; // Not supported in MySQL.
559	}
560
561	// SQLite supports underscores in all numeric literals.
562	$no_underscore_default_value = str_replace( '_', '', $default_value );
563
564	// Numeric literals. E.g.: 123, 1.23, -1.23, 1e3, 1.2e-3
565	if ( is_numeric( $no_underscore_default_value ) ) {
566	return $no_underscore_default_value;
567	}
568
569	// HEX literals (numeric). E.g.: 0x1a2f, 0X1A2F
570	$value = filter_var( $no_underscore_default_value, FILTER_VALIDATE_INT, FILTER_FLAG_ALLOW_HEX );
571	if ( false !== $value ) {
572	return $value;
573	}
574
575	// BLOB literals (string). E.g.: x'1a2f', X'1A2F'
576	// Checking the prefix is enough as SQLite doesn't allow malformed values.
577	if ( str_starts_with( $uppercase_default_value, "X'" ) ) {
578	// Convert the hex string to ASCII bytes.
579	return "'" . pack( 'H*', substr( $default_value, 2, -1 ) ) . "'";
580	}
581
582	// Unquoted string literal. E.g.: abc
583	return $this->quote_mysql_utf8_string_literal( $default_value );
584	}
585
586	/**
587	* Get a MySQL column or index data type from legacy data types cache table.
588	*
589	* This method retrieves MySQL column or index data types from a special table
590	* that was used by an old version of the SQLite driver and that is otherwise
591	* no longer needed. This is more precise than direct inference from SQLite.
592	*
593	* For columns, it returns full column type, including prefix length, e.g.:
594	* int(11), bigint(20) unsigned, varchar(255), longtext
595	*
596	* For indexes, it returns one of:
597	* KEY, PRIMARY, UNIQUE, FULLTEXT, SPATIAL
598	*
599	* @param string $table_name The table name.
600	* @param string $column_or_index_name The column or index name.
601	* @return string\|null The MySQL definition, or null when not found.
602	*/
603	private function get_cached_mysql_data_type( string $table_name, string $column_or_index_name ): ?string {
604	try {
605	$mysql_type = $this->driver->execute_sqlite_query(
606	'SELECT mysql_type FROM _mysql_data_types_cache
607	WHERE `table` = ? COLLATE NOCASE
608	AND (
609	-- The old SQLite driver stored the MySQL data types in multiple
610	-- formats - lowercase, uppercase, and, sometimes, with backticks.
611	column_or_index = ? COLLATE NOCASE
612	OR column_or_index = ? COLLATE NOCASE
613	)',
614	array( $table_name, $column_or_index_name, "`$column_or_index_name`" )
615	)->fetchColumn();
616	} catch ( PDOException $e ) {
617	if ( str_contains( $e->getMessage(), 'no such table' ) ) {
618	return null;
619	}
620	throw $e;
621	}
622	if ( false === $mysql_type ) {
623	return null;
624	}
625
626	/**
627	* Check whether the stored type value is a valid MySQL column type.
628	*
629	* Some older versions of the legacy SQLite driver might have stored
630	* invalid MySQL column types in some scenarios:
631	*
632	* 1. Before https://github.com/WordPress/sqlite-database-integration/pull/126,
633	* the legacy SQLite driver incorrectly stored MySQL column types
634	* for columns with multiple type arguments.
635	*
636	* E.g., a column definition like "col_name decimal(26, 8)" would
637	* be stored with invalid type "decimal(26,".
638	*
639	* 2. Before https://github.com/WordPress/sqlite-database-integration/commit/b5a9fbaed4d0d843f792aaa959e3d00f193ff1ee
640	* (see also https://github.com/Automattic/sqlite-database-integration/pull/2),
641	* the legacy SQLite driver incorrectly recognized indexes on columns
642	* with type keywords as additional table column definitions.
643	*
644	* E.g., an index definition like "KEY timestamp (timestamp)" would
645	* be stored as column "KEY" with invalid type "timestamp(timestamp)".
646	*
647	* To address these issues, we need to check whether the stored type looks
648	* like a valid MySQL column type definition.
649	*/
650	$open_par_index = strpos( $mysql_type, '(' );
651	$close_par_index = strpos( $mysql_type, ')' );
652	if ( false !== $open_par_index ) {
653	$end = false !== $close_par_index ? $close_par_index : strlen( $mysql_type );
654	$parts = explode( '(', substr( $mysql_type, 0, $end ) );
655	$type = strtolower( trim( $parts[0] ) );
656	$args = array();
657	foreach ( explode( ',', $parts[1] ) as $arg ) {
658	$args[] = strtolower( trim( $arg ) );
659	}
660
661	// WooCommerce uses decimal(26,8), decimal(19,4), and decimal(3,2)
662	// column types, so we can can fix the invalid column definitions.
663	$looks_like_wc_table = str_contains( $table_name, 'wc_' ) \|\| str_contains( $table_name, 'woocommerce_' );
664	$is_invalid_decimal = 'decimal' === $type && count( $args ) === 2 && '' === $args[1];
665	if ( $looks_like_wc_table && $is_invalid_decimal ) {
666	if ( '26' === $args[0] ) {
667	// Fix "decimal(26,".
668	return 'decimal(26,8)';
669	} elseif ( '19' === $args[0] ) {
670	// Fix "decimal(19,".
671	return 'decimal(19,4)';
672	} elseif ( '3' === $args[0] ) {
673	// Fix "decimal(3,".
674	return 'decimal(3,2)';
675	}
676	}
677
678	// Only numeric arguments are allowed for MySQL column types.
679	// This handles the incorrectly stored index definition case.
680	foreach ( $args as $arg ) {
681	if ( ! is_numeric( $arg ) ) {
682	return null;
683	}
684	}
685
686	// If there is no closing parenthesis, the type is invalid.
687	if ( false === $close_par_index ) {
688	return null;
689	}
690	}
691
692	// Normalize index type for backward compatibility. Some older versions
693	// of the SQLite driver stored index types with a " KEY" suffix, e.g.,
694	// "PRIMARY KEY" or "UNIQUE KEY". More recent versions omit the suffix.
695	if ( str_ends_with( $mysql_type, ' KEY' ) ) {
696	$mysql_type = substr( $mysql_type, 0, strlen( $mysql_type ) - strlen( ' KEY' ) );
697	}
698	return $mysql_type;
699	}
700
701	/**
702	* Get a MySQL column type from an SQLite column type.
703	*
704	* This method converts an SQLite column type to a MySQL column type as per
705	* the SQLite column type affinity rules:
706	* https://sqlite.org/datatype3.html#determination_of_column_affinity
707	*
708	* @param string $column_type The SQLite column type.
709	* @return string The MySQL column type.
710	*/
711	private function get_mysql_column_type( string $column_type ): string {
712	$type = strtoupper( $column_type );
713
714	/*
715	* Following the rules of column affinity:
716	* https://sqlite.org/datatype3.html#determination_of_column_affinity
717	*/
718
719	// 1. If the declared type contains the string "INT" then it is assigned
720	// INTEGER affinity.
721	if ( str_contains( $type, 'INT' ) ) {
722	return 'int';
723	}
724
725	// 2. If the declared type of the column contains any of the strings
726	// "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity.
727	if ( str_contains( $type, 'TEXT' ) \|\| str_contains( $type, 'CHAR' ) \|\| str_contains( $type, 'CLOB' ) ) {
728	return 'text';
729	}
730
731	// 3. If the declared type for a column contains the string "BLOB" or
732	// if no type is specified then the column has affinity BLOB.
733	if ( str_contains( $type, 'BLOB' ) \|\| '' === $type ) {
734	return 'blob';
735	}
736
737	// 4. If the declared type for a column contains any of the strings
738	// "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
739	if ( str_contains( $type, 'REAL' ) \|\| str_contains( $type, 'FLOA' ) ) {
740	return 'float';
741	}
742	if ( str_contains( $type, 'DOUB' ) ) {
743	return 'double';
744	}
745
746	/**
747	* 5. Otherwise, the affinity is NUMERIC.
748	*
749	* While SQLite defaults to a NUMERIC column affinity, it's better to use
750	* TEXT in this case, because numeric SQLite columns in non-strict tables
751	* can contain any text data as well, when it is not a well-formed number.
752	*
753	* See: https://sqlite.org/datatype3.html#type_affinity
754	*/
755	return 'text';
756	}
757
758	/**
759	* Format a MySQL UTF-8 string literal for output in a CREATE TABLE statement.
760	*
761	* See WP_PDO_MySQL_On_SQLite::quote_mysql_utf8_string_literal().
762	*
763	* TODO: This is a copy of WP_PDO_MySQL_On_SQLite::quote_mysql_utf8_string_literal().
764	* We may consider extracing it to reusable MySQL helpers.
765	*
766	* @param string $utf8_literal The UTF-8 string literal to escape.
767	* @return string The escaped string literal.
768	*/
769	private function quote_mysql_utf8_string_literal( string $utf8_literal ): string {
770	$backslash = chr( 92 );
771	$replacements = array(
772	"'" => "''", // A single quote character (').
773	$backslash => $backslash . $backslash, // A backslash character (\).
774	chr( 0 ) => $backslash . '0', // An ASCII NULL character (\0).
775	chr( 10 ) => $backslash . 'n', // A newline (linefeed) character (\n).
776	chr( 13 ) => $backslash . 'r', // A carriage return character (\r).
777	);
778	return "'" . strtr( $utf8_literal, $replacements ) . "'";
779	}
780
781	/**
782	* Unquote a quoted MySQL identifier.
783	*
784	* Remove bounding quotes and replace escaped quotes with their values.
785	*
786	* @param string $quoted_identifier The quoted identifier value.
787	* @return string The unquoted identifier value.
788	*/
789	private function unquote_mysql_identifier( string $quoted_identifier ): string {
790	$first_byte = $quoted_identifier[0] ?? null;
791	if ( '"' === $first_byte \|\| '`' === $first_byte ) {
792	$unquoted = substr( $quoted_identifier, 1, -1 );
793	return str_replace( $first_byte . $first_byte, $first_byte, $unquoted );
794	}
795	return $quoted_identifier;
796	}
797	}
798