CustomOrderTable
2 years ago
MetaToCustomTableMigrator.php
2 years ago
MetaToMetaTableMigrator.php
2 years ago
MigrationHelper.php
3 years ago
TableMigrator.php
2 years ago
MetaToMetaTableMigrator.php
411 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Generic Migration class to move any meta data associated to an entity, to a different meta table associated with a custom entity table. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Database\Migrations; |
| 7 | |
| 8 | /** |
| 9 | * Base class for implementing migrations from the standard WordPress meta table |
| 10 | * to custom meta (key-value pairs) tables. |
| 11 | * |
| 12 | * @package Automattic\WooCommerce\Database\Migrations\CustomOrderTable |
| 13 | */ |
| 14 | abstract class MetaToMetaTableMigrator extends TableMigrator { |
| 15 | |
| 16 | /** |
| 17 | * Schema config, see __construct for more details. |
| 18 | * |
| 19 | * @var array |
| 20 | */ |
| 21 | private $schema_config; |
| 22 | |
| 23 | /** |
| 24 | * Returns config for the migration. |
| 25 | * |
| 26 | * @return array Meta config, must be in following format: |
| 27 | * array( |
| 28 | * 'source' => array( |
| 29 | * 'meta' => array( |
| 30 | * 'table_name' => source_meta_table_name, |
| 31 | * 'entity_id_column' => entity_id column name in source meta table, |
| 32 | * 'meta_key_column' => meta_key column', |
| 33 | * 'meta_value_column' => meta_value column', |
| 34 | * ), |
| 35 | * 'entity' => array( |
| 36 | * 'table_name' => entity table name for the meta table, |
| 37 | * 'source_id_column' => column name in entity table which maps to meta table, |
| 38 | * 'id_column' => id column in entity table, |
| 39 | * ), |
| 40 | * 'excluded_keys' => array of keys to exclude, |
| 41 | * ), |
| 42 | * 'destination' => array( |
| 43 | * 'meta' => array( |
| 44 | * 'table_name' => destination meta table name, |
| 45 | * 'entity_id_column' => entity_id column in meta table, |
| 46 | * 'meta_key_column' => meta key column, |
| 47 | * 'meta_value_column' => meta_value column, |
| 48 | * 'entity_id_type' => data type of entity id, |
| 49 | * 'meta_id_column' => id column in meta table, |
| 50 | * ), |
| 51 | * ), |
| 52 | * ) |
| 53 | */ |
| 54 | abstract protected function get_meta_config(): array; |
| 55 | |
| 56 | /** |
| 57 | * MetaToMetaTableMigrator constructor. |
| 58 | */ |
| 59 | public function __construct() { |
| 60 | $this->schema_config = $this->get_meta_config(); |
| 61 | } |
| 62 | |
| 63 | /** |
| 64 | * Return data to be migrated for a batch of entities. |
| 65 | * |
| 66 | * @param array $entity_ids Ids of entities to migrate. |
| 67 | * |
| 68 | * @return array[] Data to be migrated. Would be of the form: array( 'data' => array( ... ), 'errors' => array( ... ) ). |
| 69 | */ |
| 70 | public function fetch_sanitized_migration_data( $entity_ids ) { |
| 71 | $this->clear_errors(); |
| 72 | $to_migrate = $this->fetch_data_for_migration_for_ids( $entity_ids ); |
| 73 | if ( empty( $to_migrate ) ) { |
| 74 | return array( |
| 75 | 'data' => array(), |
| 76 | 'errors' => array(), |
| 77 | ); |
| 78 | } |
| 79 | |
| 80 | $already_migrated = $this->get_already_migrated_records( array_keys( $to_migrate ) ); |
| 81 | |
| 82 | return array( |
| 83 | 'data' => $this->classify_update_insert_records( $to_migrate, $already_migrated ), |
| 84 | 'errors' => $this->get_errors(), |
| 85 | ); |
| 86 | } |
| 87 | |
| 88 | /** |
| 89 | * Migrate a batch of entities from the posts table to the corresponding table. |
| 90 | * |
| 91 | * @param array $entity_ids Ids of entities ro migrate. |
| 92 | */ |
| 93 | protected function process_migration_batch_for_ids_core( array $entity_ids ): void { |
| 94 | $sanitized_data = $this->fetch_sanitized_migration_data( $entity_ids ); |
| 95 | $this->process_migration_data( $sanitized_data ); |
| 96 | } |
| 97 | |
| 98 | /** |
| 99 | * Process migration data for a batch of entities. |
| 100 | * |
| 101 | * @param array $data Data to be migrated. Should be of the form: array( 'data' => array( ... ) ) as returned by the `fetch_sanitized_migration_data` method. |
| 102 | * |
| 103 | * @return array Array of errors and exception if any. |
| 104 | */ |
| 105 | public function process_migration_data( array $data ) { |
| 106 | if ( isset( $data['data'] ) ) { |
| 107 | $data = $data['data']; |
| 108 | } |
| 109 | $this->clear_errors(); |
| 110 | $exception = null; |
| 111 | |
| 112 | $to_insert = $data[0]; |
| 113 | $to_update = $data[1]; |
| 114 | |
| 115 | try { |
| 116 | if ( ! empty( $to_insert ) ) { |
| 117 | $insert_queries = $this->generate_insert_sql_for_batch( $to_insert ); |
| 118 | $processed_rows_count = $this->db_query( $insert_queries ); |
| 119 | $this->maybe_add_insert_or_update_error( 'insert', $processed_rows_count ); |
| 120 | } |
| 121 | |
| 122 | if ( ! empty( $to_update ) ) { |
| 123 | $update_queries = $this->generate_update_sql_for_batch( $to_update ); |
| 124 | $processed_rows_count = $this->db_query( $update_queries ); |
| 125 | $this->maybe_add_insert_or_update_error( 'update', $processed_rows_count ); |
| 126 | } |
| 127 | } catch ( \Exception $e ) { |
| 128 | $exception = $e; |
| 129 | } |
| 130 | |
| 131 | return array( |
| 132 | 'errors' => $this->get_errors(), |
| 133 | 'exception' => $exception, |
| 134 | ); |
| 135 | } |
| 136 | |
| 137 | /** |
| 138 | * Generate update SQL for given batch. |
| 139 | * |
| 140 | * @param array $batch List of data to generate update SQL for. Should be in same format as output of $this->fetch_data_for_migration_for_ids. |
| 141 | * |
| 142 | * @return string Query to update batch records. |
| 143 | */ |
| 144 | private function generate_update_sql_for_batch( array $batch ): string { |
| 145 | global $wpdb; |
| 146 | |
| 147 | $table = $this->schema_config['destination']['meta']['table_name']; |
| 148 | $meta_id_column = $this->schema_config['destination']['meta']['meta_id_column']; |
| 149 | $meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 150 | $meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 151 | $entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 152 | $columns = array( $meta_id_column, $entity_id_column, $meta_key_column, $meta_value_column ); |
| 153 | $columns_sql = implode( '`, `', $columns ); |
| 154 | |
| 155 | $entity_id_column_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 156 | $placeholder_string = "%d, $entity_id_column_placeholder, %s, %s"; |
| 157 | $values = array(); |
| 158 | foreach ( $batch as $entity_id => $rows ) { |
| 159 | foreach ( $rows as $meta_key => $meta_details ) { |
| 160 | |
| 161 | // phpcs:disable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders |
| 162 | $values[] = $wpdb->prepare( |
| 163 | "( $placeholder_string )", |
| 164 | array( $meta_details['id'], $entity_id, $meta_key, $meta_details['meta_value'] ) |
| 165 | ); |
| 166 | // phpcs:enable |
| 167 | } |
| 168 | } |
| 169 | $value_sql = implode( ',', $values ); |
| 170 | |
| 171 | $on_duplicate_key_clause = MigrationHelper::generate_on_duplicate_statement_clause( $columns ); |
| 172 | |
| 173 | return "INSERT INTO $table ( `$columns_sql` ) VALUES $value_sql $on_duplicate_key_clause"; |
| 174 | } |
| 175 | |
| 176 | /** |
| 177 | * Generate insert sql queries for batches. |
| 178 | * |
| 179 | * @param array $batch Data to generate queries for. |
| 180 | * |
| 181 | * @return string Insert SQL query. |
| 182 | */ |
| 183 | private function generate_insert_sql_for_batch( array $batch ): string { |
| 184 | global $wpdb; |
| 185 | |
| 186 | $table = $this->schema_config['destination']['meta']['table_name']; |
| 187 | $meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 188 | $meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 189 | $entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 190 | $column_sql = "(`$entity_id_column`, `$meta_key_column`, `$meta_value_column`)"; |
| 191 | |
| 192 | $entity_id_column_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 193 | $placeholder_string = "$entity_id_column_placeholder, %s, %s"; |
| 194 | $values = array(); |
| 195 | foreach ( $batch as $entity_id => $rows ) { |
| 196 | foreach ( $rows as $meta_key => $meta_values ) { |
| 197 | foreach ( $meta_values as $meta_value ) { |
| 198 | $query_params = array( |
| 199 | $entity_id, |
| 200 | $meta_key, |
| 201 | $meta_value, |
| 202 | ); |
| 203 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders |
| 204 | $value_sql = $wpdb->prepare( "$placeholder_string", $query_params ); |
| 205 | $values[] = $value_sql; |
| 206 | } |
| 207 | } |
| 208 | } |
| 209 | |
| 210 | $values_sql = implode( '), (', $values ); |
| 211 | |
| 212 | return "INSERT IGNORE INTO $table $column_sql VALUES ($values_sql)"; |
| 213 | } |
| 214 | |
| 215 | /** |
| 216 | * Fetch data for migration. |
| 217 | * |
| 218 | * @param array $entity_ids Array of IDs to fetch data for. |
| 219 | * |
| 220 | * @return array[] Data, will of the form: |
| 221 | * array( |
| 222 | * 'id_1' => array( 'column1' => array( value1_1, value1_2...), 'column2' => array(value2_1, value2_2...), ...), |
| 223 | * ..., |
| 224 | * ) |
| 225 | */ |
| 226 | public function fetch_data_for_migration_for_ids( array $entity_ids ): array { |
| 227 | if ( empty( $entity_ids ) ) { |
| 228 | return array(); |
| 229 | } |
| 230 | |
| 231 | $meta_query = $this->build_meta_table_query( $entity_ids ); |
| 232 | |
| 233 | $meta_data_rows = $this->db_get_results( $meta_query ); |
| 234 | if ( ! is_array( $meta_data_rows ) || empty( $meta_data_rows ) ) { |
| 235 | return array(); |
| 236 | } |
| 237 | |
| 238 | foreach ( $meta_data_rows as $migrate_row ) { |
| 239 | if ( ! isset( $to_migrate[ $migrate_row->entity_id ] ) ) { |
| 240 | $to_migrate[ $migrate_row->entity_id ] = array(); |
| 241 | } |
| 242 | |
| 243 | if ( ! isset( $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ] ) ) { |
| 244 | // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key |
| 245 | $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ] = array(); |
| 246 | } |
| 247 | |
| 248 | $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ][] = $migrate_row->meta_value; |
| 249 | } |
| 250 | |
| 251 | return $to_migrate; |
| 252 | } |
| 253 | |
| 254 | /** |
| 255 | * Helper method to get already migrated records. Will be used to find prevent migration of already migrated records. |
| 256 | * |
| 257 | * @param array $entity_ids List of entity ids to check for. |
| 258 | * |
| 259 | * @return array Already migrated records. |
| 260 | */ |
| 261 | private function get_already_migrated_records( array $entity_ids ): array { |
| 262 | global $wpdb; |
| 263 | |
| 264 | $destination_table_name = $this->schema_config['destination']['meta']['table_name']; |
| 265 | $destination_id_column = $this->schema_config['destination']['meta']['meta_id_column']; |
| 266 | $destination_entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 267 | $destination_meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 268 | $destination_meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 269 | |
| 270 | $entity_id_type_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 271 | $entity_ids_placeholder = implode( ',', array_fill( 0, count( $entity_ids ), $entity_id_type_placeholder ) ); |
| 272 | |
| 273 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare |
| 274 | $data_already_migrated = $this->db_get_results( |
| 275 | $wpdb->prepare( |
| 276 | " |
| 277 | SELECT |
| 278 | $destination_id_column meta_id, |
| 279 | $destination_entity_id_column entity_id, |
| 280 | $destination_meta_key_column meta_key, |
| 281 | $destination_meta_value_column meta_value |
| 282 | FROM $destination_table_name destination |
| 283 | WHERE destination.$destination_entity_id_column in ( $entity_ids_placeholder ) ORDER BY destination.$destination_entity_id_column |
| 284 | ", |
| 285 | $entity_ids |
| 286 | ) |
| 287 | ); |
| 288 | // phpcs:enable |
| 289 | |
| 290 | $already_migrated = array(); |
| 291 | |
| 292 | foreach ( $data_already_migrated as $migrate_row ) { |
| 293 | if ( ! isset( $already_migrated[ $migrate_row->entity_id ] ) ) { |
| 294 | $already_migrated[ $migrate_row->entity_id ] = array(); |
| 295 | } |
| 296 | |
| 297 | // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 298 | if ( ! isset( $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ] ) ) { |
| 299 | $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ] = array(); |
| 300 | } |
| 301 | |
| 302 | $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ][] = array( |
| 303 | 'id' => $migrate_row->meta_id, |
| 304 | 'meta_value' => $migrate_row->meta_value, |
| 305 | ); |
| 306 | // phpcs:enable |
| 307 | } |
| 308 | |
| 309 | return $already_migrated; |
| 310 | } |
| 311 | |
| 312 | /** |
| 313 | * Classify each record on whether to migrate or update. |
| 314 | * |
| 315 | * @param array $to_migrate Records to migrate. |
| 316 | * @param array $already_migrated Records already migrated. |
| 317 | * |
| 318 | * @return array[] Returns two arrays, first for records to migrate, and second for records to upgrade. |
| 319 | */ |
| 320 | private function classify_update_insert_records( array $to_migrate, array $already_migrated ): array { |
| 321 | $to_update = array(); |
| 322 | $to_insert = array(); |
| 323 | |
| 324 | foreach ( $to_migrate as $entity_id => $rows ) { |
| 325 | foreach ( $rows as $meta_key => $meta_values ) { |
| 326 | // If there is no corresponding record in the destination table then insert. |
| 327 | // If there is single value in both already migrated and current then update. |
| 328 | // If there are multiple values in either already_migrated records or in to_migrate_records, then insert instead of updating. |
| 329 | if ( ! isset( $already_migrated[ $entity_id ][ $meta_key ] ) ) { |
| 330 | if ( ! isset( $to_insert[ $entity_id ] ) ) { |
| 331 | $to_insert[ $entity_id ] = array(); |
| 332 | } |
| 333 | $to_insert[ $entity_id ][ $meta_key ] = $meta_values; |
| 334 | } else { |
| 335 | if ( 1 === count( $meta_values ) && 1 === count( $already_migrated[ $entity_id ][ $meta_key ] ) ) { |
| 336 | if ( $meta_values[0] === $already_migrated[ $entity_id ][ $meta_key ][0]['meta_value'] ) { |
| 337 | continue; |
| 338 | } |
| 339 | if ( ! isset( $to_update[ $entity_id ] ) ) { |
| 340 | $to_update[ $entity_id ] = array(); |
| 341 | } |
| 342 | $to_update[ $entity_id ][ $meta_key ] = array( |
| 343 | 'id' => $already_migrated[ $entity_id ][ $meta_key ][0]['id'], |
| 344 | // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 345 | 'meta_value' => $meta_values[0], |
| 346 | ); |
| 347 | continue; |
| 348 | } |
| 349 | |
| 350 | // There are multiple meta entries, let's find the unique entries and insert. |
| 351 | $unique_meta_values = array_diff( $meta_values, array_column( $already_migrated[ $entity_id ][ $meta_key ], 'meta_value' ) ); |
| 352 | if ( 0 === count( $unique_meta_values ) ) { |
| 353 | continue; |
| 354 | } |
| 355 | if ( ! isset( $to_insert[ $entity_id ] ) ) { |
| 356 | $to_insert[ $entity_id ] = array(); |
| 357 | } |
| 358 | $to_insert[ $entity_id ][ $meta_key ] = $unique_meta_values; |
| 359 | } |
| 360 | } |
| 361 | } |
| 362 | |
| 363 | return array( $to_insert, $to_update ); |
| 364 | } |
| 365 | |
| 366 | /** |
| 367 | * Helper method to build query used to fetch data from source meta table. |
| 368 | * |
| 369 | * @param array $entity_ids List of entity IDs to build meta query for. |
| 370 | * |
| 371 | * @return string Query that can be used to fetch data. |
| 372 | */ |
| 373 | private function build_meta_table_query( array $entity_ids ): string { |
| 374 | global $wpdb; |
| 375 | $source_meta_table = $this->schema_config['source']['meta']['table_name']; |
| 376 | $source_meta_key_column = $this->schema_config['source']['meta']['meta_key_column']; |
| 377 | $source_meta_value_column = $this->schema_config['source']['meta']['meta_value_column']; |
| 378 | $source_entity_id_column = $this->schema_config['source']['meta']['entity_id_column']; |
| 379 | $order_by = "source.$source_entity_id_column ASC"; |
| 380 | |
| 381 | $where_clause = "source.`$source_entity_id_column` IN (" . implode( ', ', array_fill( 0, count( $entity_ids ), '%d' ) ) . ')'; |
| 382 | |
| 383 | $entity_table = $this->schema_config['source']['entity']['table_name']; |
| 384 | $entity_id_column = $this->schema_config['source']['entity']['id_column']; |
| 385 | $entity_meta_id_mapping_column = $this->schema_config['source']['entity']['source_id_column']; |
| 386 | |
| 387 | if ( isset( $this->schema_config['source']['excluded_keys'] ) && is_array( $this->schema_config['source']['excluded_keys'] ) ) { |
| 388 | $key_placeholder = implode( ',', array_fill( 0, count( $this->schema_config['source']['excluded_keys'] ), '%s' ) ); |
| 389 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $source_meta_key_column is escaped for backticks, $key_placeholder is hardcoded. |
| 390 | $exclude_clause = $wpdb->prepare( "source.$source_meta_key_column NOT IN ( $key_placeholder )", $this->schema_config['source']['excluded_keys'] ); |
| 391 | $where_clause = "$where_clause AND $exclude_clause"; |
| 392 | } |
| 393 | |
| 394 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare |
| 395 | return $wpdb->prepare( |
| 396 | " |
| 397 | SELECT |
| 398 | source.`$source_entity_id_column` as source_entity_id, |
| 399 | entity.`$entity_id_column` as entity_id, |
| 400 | source.`$source_meta_key_column` as meta_key, |
| 401 | source.`$source_meta_value_column` as meta_value |
| 402 | FROM `$source_meta_table` source |
| 403 | JOIN `$entity_table` entity ON entity.`$entity_meta_id_mapping_column` = source.`$source_entity_id_column` |
| 404 | WHERE $where_clause ORDER BY $order_by |
| 405 | ", |
| 406 | $entity_ids |
| 407 | ); |
| 408 | // phpcs:enable |
| 409 | } |
| 410 | } |
| 411 |