CustomOrderTable
3 years ago
MetaToCustomTableMigrator.php
3 years ago
MetaToMetaTableMigrator.php
3 years ago
MigrationHelper.php
4 years ago
TableMigrator.php
3 years ago
MetaToMetaTableMigrator.php
367 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 | * Migrate a batch of entities from the posts table to the corresponding table. |
| 65 | * |
| 66 | * @param array $entity_ids Ids of entities ro migrate. |
| 67 | */ |
| 68 | protected function process_migration_batch_for_ids_core( array $entity_ids ): void { |
| 69 | $to_migrate = $this->fetch_data_for_migration_for_ids( $entity_ids ); |
| 70 | if ( empty( $to_migrate ) ) { |
| 71 | return; |
| 72 | } |
| 73 | |
| 74 | $already_migrated = $this->get_already_migrated_records( array_keys( $to_migrate ) ); |
| 75 | |
| 76 | $data = $this->classify_update_insert_records( $to_migrate, $already_migrated ); |
| 77 | $to_insert = $data[0]; |
| 78 | $to_update = $data[1]; |
| 79 | |
| 80 | if ( ! empty( $to_insert ) ) { |
| 81 | $insert_queries = $this->generate_insert_sql_for_batch( $to_insert ); |
| 82 | $processed_rows_count = $this->db_query( $insert_queries ); |
| 83 | $this->maybe_add_insert_or_update_error( 'insert', $processed_rows_count ); |
| 84 | } |
| 85 | |
| 86 | if ( ! empty( $to_update ) ) { |
| 87 | $update_queries = $this->generate_update_sql_for_batch( $to_update ); |
| 88 | $processed_rows_count = $this->db_query( $update_queries ); |
| 89 | $this->maybe_add_insert_or_update_error( 'update', $processed_rows_count ); |
| 90 | } |
| 91 | } |
| 92 | |
| 93 | /** |
| 94 | * Generate update SQL for given batch. |
| 95 | * |
| 96 | * @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. |
| 97 | * |
| 98 | * @return string Query to update batch records. |
| 99 | */ |
| 100 | private function generate_update_sql_for_batch( array $batch ): string { |
| 101 | global $wpdb; |
| 102 | |
| 103 | $table = $this->schema_config['destination']['meta']['table_name']; |
| 104 | $meta_id_column = $this->schema_config['destination']['meta']['meta_id_column']; |
| 105 | $meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 106 | $meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 107 | $entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 108 | $columns = array( $meta_id_column, $entity_id_column, $meta_key_column, $meta_value_column ); |
| 109 | $columns_sql = implode( '`, `', $columns ); |
| 110 | |
| 111 | $entity_id_column_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 112 | $placeholder_string = "%d, $entity_id_column_placeholder, %s, %s"; |
| 113 | $values = array(); |
| 114 | foreach ( $batch as $entity_id => $rows ) { |
| 115 | foreach ( $rows as $meta_key => $meta_details ) { |
| 116 | |
| 117 | // phpcs:disable WordPress.DB.PreparedSQL, WordPress.DB.PreparedSQLPlaceholders |
| 118 | $values[] = $wpdb->prepare( |
| 119 | "( $placeholder_string )", |
| 120 | array( $meta_details['id'], $entity_id, $meta_key, $meta_details['meta_value'] ) |
| 121 | ); |
| 122 | // phpcs:enable |
| 123 | } |
| 124 | } |
| 125 | $value_sql = implode( ',', $values ); |
| 126 | |
| 127 | $on_duplicate_key_clause = MigrationHelper::generate_on_duplicate_statement_clause( $columns ); |
| 128 | |
| 129 | return "INSERT INTO $table ( `$columns_sql` ) VALUES $value_sql $on_duplicate_key_clause"; |
| 130 | } |
| 131 | |
| 132 | /** |
| 133 | * Generate insert sql queries for batches. |
| 134 | * |
| 135 | * @param array $batch Data to generate queries for. |
| 136 | * |
| 137 | * @return string Insert SQL query. |
| 138 | */ |
| 139 | private function generate_insert_sql_for_batch( array $batch ): string { |
| 140 | global $wpdb; |
| 141 | |
| 142 | $table = $this->schema_config['destination']['meta']['table_name']; |
| 143 | $meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 144 | $meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 145 | $entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 146 | $column_sql = "(`$entity_id_column`, `$meta_key_column`, `$meta_value_column`)"; |
| 147 | |
| 148 | $entity_id_column_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 149 | $placeholder_string = "$entity_id_column_placeholder, %s, %s"; |
| 150 | $values = array(); |
| 151 | foreach ( $batch as $entity_id => $rows ) { |
| 152 | foreach ( $rows as $meta_key => $meta_values ) { |
| 153 | foreach ( $meta_values as $meta_value ) { |
| 154 | $query_params = array( |
| 155 | $entity_id, |
| 156 | $meta_key, |
| 157 | $meta_value, |
| 158 | ); |
| 159 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders |
| 160 | $value_sql = $wpdb->prepare( "$placeholder_string", $query_params ); |
| 161 | $values[] = $value_sql; |
| 162 | } |
| 163 | } |
| 164 | } |
| 165 | |
| 166 | $values_sql = implode( '), (', $values ); |
| 167 | |
| 168 | return "INSERT IGNORE INTO $table $column_sql VALUES ($values_sql)"; |
| 169 | } |
| 170 | |
| 171 | /** |
| 172 | * Fetch data for migration. |
| 173 | * |
| 174 | * @param array $entity_ids Array of IDs to fetch data for. |
| 175 | * |
| 176 | * @return array[] Data, will of the form: |
| 177 | * array( |
| 178 | * 'id_1' => array( 'column1' => array( value1_1, value1_2...), 'column2' => array(value2_1, value2_2...), ...), |
| 179 | * ..., |
| 180 | * ) |
| 181 | */ |
| 182 | private function fetch_data_for_migration_for_ids( array $entity_ids ): array { |
| 183 | if ( empty( $entity_ids ) ) { |
| 184 | return array(); |
| 185 | } |
| 186 | |
| 187 | $meta_query = $this->build_meta_table_query( $entity_ids ); |
| 188 | |
| 189 | $meta_data_rows = $this->db_get_results( $meta_query ); |
| 190 | if ( empty( $meta_data_rows ) ) { |
| 191 | return array(); |
| 192 | } |
| 193 | |
| 194 | foreach ( $meta_data_rows as $migrate_row ) { |
| 195 | if ( ! isset( $to_migrate[ $migrate_row->entity_id ] ) ) { |
| 196 | $to_migrate[ $migrate_row->entity_id ] = array(); |
| 197 | } |
| 198 | |
| 199 | if ( ! isset( $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ] ) ) { |
| 200 | // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key |
| 201 | $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ] = array(); |
| 202 | } |
| 203 | |
| 204 | $to_migrate[ $migrate_row->entity_id ][ $migrate_row->meta_key ][] = $migrate_row->meta_value; |
| 205 | } |
| 206 | |
| 207 | return $to_migrate; |
| 208 | } |
| 209 | |
| 210 | /** |
| 211 | * Helper method to get already migrated records. Will be used to find prevent migration of already migrated records. |
| 212 | * |
| 213 | * @param array $entity_ids List of entity ids to check for. |
| 214 | * |
| 215 | * @return array Already migrated records. |
| 216 | */ |
| 217 | private function get_already_migrated_records( array $entity_ids ): array { |
| 218 | global $wpdb; |
| 219 | |
| 220 | $destination_table_name = $this->schema_config['destination']['meta']['table_name']; |
| 221 | $destination_id_column = $this->schema_config['destination']['meta']['meta_id_column']; |
| 222 | $destination_entity_id_column = $this->schema_config['destination']['meta']['entity_id_column']; |
| 223 | $destination_meta_key_column = $this->schema_config['destination']['meta']['meta_key_column']; |
| 224 | $destination_meta_value_column = $this->schema_config['destination']['meta']['meta_value_column']; |
| 225 | |
| 226 | $entity_id_type_placeholder = MigrationHelper::get_wpdb_placeholder_for_type( $this->schema_config['destination']['meta']['entity_id_type'] ); |
| 227 | $entity_ids_placeholder = implode( ',', array_fill( 0, count( $entity_ids ), $entity_id_type_placeholder ) ); |
| 228 | |
| 229 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare |
| 230 | $data_already_migrated = $this->db_get_results( |
| 231 | $wpdb->prepare( |
| 232 | " |
| 233 | SELECT |
| 234 | $destination_id_column meta_id, |
| 235 | $destination_entity_id_column entity_id, |
| 236 | $destination_meta_key_column meta_key, |
| 237 | $destination_meta_value_column meta_value |
| 238 | FROM $destination_table_name destination |
| 239 | WHERE destination.$destination_entity_id_column in ( $entity_ids_placeholder ) ORDER BY destination.$destination_entity_id_column |
| 240 | ", |
| 241 | $entity_ids |
| 242 | ) |
| 243 | ); |
| 244 | // phpcs:enable |
| 245 | |
| 246 | $already_migrated = array(); |
| 247 | |
| 248 | foreach ( $data_already_migrated as $migrate_row ) { |
| 249 | if ( ! isset( $already_migrated[ $migrate_row->entity_id ] ) ) { |
| 250 | $already_migrated[ $migrate_row->entity_id ] = array(); |
| 251 | } |
| 252 | |
| 253 | // phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key, WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 254 | if ( ! isset( $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ] ) ) { |
| 255 | $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ] = array(); |
| 256 | } |
| 257 | |
| 258 | $already_migrated[ $migrate_row->entity_id ][ $migrate_row->meta_key ][] = array( |
| 259 | 'id' => $migrate_row->meta_id, |
| 260 | 'meta_value' => $migrate_row->meta_value, |
| 261 | ); |
| 262 | // phpcs:enable |
| 263 | } |
| 264 | |
| 265 | return $already_migrated; |
| 266 | } |
| 267 | |
| 268 | /** |
| 269 | * Classify each record on whether to migrate or update. |
| 270 | * |
| 271 | * @param array $to_migrate Records to migrate. |
| 272 | * @param array $already_migrated Records already migrated. |
| 273 | * |
| 274 | * @return array[] Returns two arrays, first for records to migrate, and second for records to upgrade. |
| 275 | */ |
| 276 | private function classify_update_insert_records( array $to_migrate, array $already_migrated ): array { |
| 277 | $to_update = array(); |
| 278 | $to_insert = array(); |
| 279 | |
| 280 | foreach ( $to_migrate as $entity_id => $rows ) { |
| 281 | foreach ( $rows as $meta_key => $meta_values ) { |
| 282 | // If there is no corresponding record in the destination table then insert. |
| 283 | // If there is single value in both already migrated and current then update. |
| 284 | // If there are multiple values in either already_migrated records or in to_migrate_records, then insert instead of updating. |
| 285 | if ( ! isset( $already_migrated[ $entity_id ][ $meta_key ] ) ) { |
| 286 | if ( ! isset( $to_insert[ $entity_id ] ) ) { |
| 287 | $to_insert[ $entity_id ] = array(); |
| 288 | } |
| 289 | $to_insert[ $entity_id ][ $meta_key ] = $meta_values; |
| 290 | } else { |
| 291 | if ( 1 === count( $meta_values ) && 1 === count( $already_migrated[ $entity_id ][ $meta_key ] ) ) { |
| 292 | if ( $meta_values[0] === $already_migrated[ $entity_id ][ $meta_key ][0]['meta_value'] ) { |
| 293 | continue; |
| 294 | } |
| 295 | if ( ! isset( $to_update[ $entity_id ] ) ) { |
| 296 | $to_update[ $entity_id ] = array(); |
| 297 | } |
| 298 | $to_update[ $entity_id ][ $meta_key ] = array( |
| 299 | 'id' => $already_migrated[ $entity_id ][ $meta_key ][0]['id'], |
| 300 | // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 301 | 'meta_value' => $meta_values[0], |
| 302 | ); |
| 303 | continue; |
| 304 | } |
| 305 | |
| 306 | // There are multiple meta entries, let's find the unique entries and insert. |
| 307 | $unique_meta_values = array_diff( $meta_values, array_column( $already_migrated[ $entity_id ][ $meta_key ], 'meta_value' ) ); |
| 308 | if ( 0 === count( $unique_meta_values ) ) { |
| 309 | continue; |
| 310 | } |
| 311 | if ( ! isset( $to_insert[ $entity_id ] ) ) { |
| 312 | $to_insert[ $entity_id ] = array(); |
| 313 | } |
| 314 | $to_insert[ $entity_id ][ $meta_key ] = $unique_meta_values; |
| 315 | } |
| 316 | } |
| 317 | } |
| 318 | |
| 319 | return array( $to_insert, $to_update ); |
| 320 | } |
| 321 | |
| 322 | /** |
| 323 | * Helper method to build query used to fetch data from source meta table. |
| 324 | * |
| 325 | * @param array $entity_ids List of entity IDs to build meta query for. |
| 326 | * |
| 327 | * @return string Query that can be used to fetch data. |
| 328 | */ |
| 329 | private function build_meta_table_query( array $entity_ids ): string { |
| 330 | global $wpdb; |
| 331 | $source_meta_table = $this->schema_config['source']['meta']['table_name']; |
| 332 | $source_meta_key_column = $this->schema_config['source']['meta']['meta_key_column']; |
| 333 | $source_meta_value_column = $this->schema_config['source']['meta']['meta_value_column']; |
| 334 | $source_entity_id_column = $this->schema_config['source']['meta']['entity_id_column']; |
| 335 | $order_by = "source.$source_entity_id_column ASC"; |
| 336 | |
| 337 | $where_clause = "source.`$source_entity_id_column` IN (" . implode( ', ', array_fill( 0, count( $entity_ids ), '%d' ) ) . ')'; |
| 338 | |
| 339 | $entity_table = $this->schema_config['source']['entity']['table_name']; |
| 340 | $entity_id_column = $this->schema_config['source']['entity']['id_column']; |
| 341 | $entity_meta_id_mapping_column = $this->schema_config['source']['entity']['source_id_column']; |
| 342 | |
| 343 | if ( $this->schema_config['source']['excluded_keys'] ) { |
| 344 | $key_placeholder = implode( ',', array_fill( 0, count( $this->schema_config['source']['excluded_keys'] ), '%s' ) ); |
| 345 | // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $source_meta_key_column is escaped for backticks, $key_placeholder is hardcoded. |
| 346 | $exclude_clause = $wpdb->prepare( "source.$source_meta_key_column NOT IN ( $key_placeholder )", $this->schema_config['source']['excluded_keys'] ); |
| 347 | $where_clause = "$where_clause AND $exclude_clause"; |
| 348 | } |
| 349 | |
| 350 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare |
| 351 | return $wpdb->prepare( |
| 352 | " |
| 353 | SELECT |
| 354 | source.`$source_entity_id_column` as source_entity_id, |
| 355 | entity.`$entity_id_column` as entity_id, |
| 356 | source.`$source_meta_key_column` as meta_key, |
| 357 | source.`$source_meta_value_column` as meta_value |
| 358 | FROM `$source_meta_table` source |
| 359 | JOIN `$entity_table` entity ON entity.`$entity_meta_id_mapping_column` = source.`$source_entity_id_column` |
| 360 | WHERE $where_clause ORDER BY $order_by |
| 361 | ", |
| 362 | $entity_ids |
| 363 | ); |
| 364 | // phpcs:enable |
| 365 | } |
| 366 | } |
| 367 |