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