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