<?php
namespace WPForms\Tasks\Actions;
use WPForms\Tasks\Task;
use WPForms\Tasks\Tasks;
use WPForms_Entry_Handler;
use WPForms_Entry_Meta_Handler;
/**
* Class Migration175Task.
*
* @since 1.7.5
*/
class Migration175Task extends Task {
/**
* Action name for this task.
*
* @since 1.7.5
*/
const ACTION = 'wpforms_process_migration_175';
/**
* Status option name.
*
* @since 1.7.5
*/
const STATUS = 'wpforms_process_migration_175_status';
/**
* Start status.
*
* @since 1.7.5
*/
const START = 'start';
/**
* In progress status.
*
* @since 1.7.5
*/
const IN_PROGRESS = 'in progress';
/**
* Completed status.
*
* @since 1.7.5
*/
const COMPLETED = 'completed';
/**
* Chunk size to use.
* Specifies how many entries to convert in one db request.
*
* @since 1.7.5
*/
const CHUNK_SIZE = 5000;
/**
* Chunk size of the migration task.
* Specifies how many entry ids to load at once for further conversion.
*
* @since 1.7.5
*/
const TASK_CHUNK_SIZE = self::CHUNK_SIZE * 10;
/**
* Entry handler.
*
* @since 1.7.5
*
* @var WPForms_Entry_Handler
*/
private $entry_handler;
/**
* Entry meta handler.
*
* @since 1.7.5
*
* @var WPForms_Entry_Meta_Handler
*/
private $entry_meta_handler;
/**
* Temporary table name.
*
* @since 1.7.5
*
* @var string
*/
private $temp_table_name;
/**
* Class constructor.
*
* @since 1.7.5
*/
public function __construct() {
parent::__construct( self::ACTION );
}
/**
* Initialize the task with all the proper checks.
*
* @since 1.7.5
*/
public function init() {
global $wpdb;
$this->entry_handler = wpforms()->obj( 'entry' );
$this->entry_meta_handler = wpforms()->obj( 'entry_meta' );
$this->temp_table_name = "{$wpdb->prefix}wpforms_temp_entry_ids";
if ( ! $this->entry_handler || ! $this->entry_meta_handler ) {
return;
}
// Bail out if migration is not started or completed.
$status = get_option( self::STATUS );
if ( ! $status || $status === self::COMPLETED ) {
return;
}
$this->hooks();
if ( $status === self::START ) {
// Mark that migration is in progress.
update_option( self::STATUS, self::IN_PROGRESS );
// Alter entry meta table.
$this->alter_entry_meta_table();
// Init migration.
$this->init_migration();
}
}
/**
* Modify field in the entry meta table.
*
* @since 1.7.5
*/
private function alter_entry_meta_table() {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$wpdb->query( "ALTER TABLE {$this->entry_meta_handler->table_name} MODIFY type VARCHAR(255)" );
}
/**
* Add index to a table.
*
* @since 1.7.5
*
* @param string $table_name Table.
* @param string $index_name Index name.
* @param string $key_part Key part.
*
* @return void
*/
private function add_index( $table_name, $index_name, $key_part ) {
global $wpdb;
// Check id index already exists.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
$result = $wpdb->get_var(
"SELECT COUNT(1) IndexIsThere
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = DATABASE()
AND table_name = '$table_name'
AND index_name = '$index_name'"
);
if ( $result === '1' ) {
return;
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
// Change the column length for the wp_wpforms_entry_meta.type column to 255 and add an index.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
$wpdb->query( "CREATE INDEX $index_name ON $table_name ( $key_part )" );
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
}
/**
* Add hooks.
*
* @since 1.7.5
*/
private function hooks() {
// Register the migrate action.
add_action( self::ACTION, [ $this, 'migrate' ] );
// Register after process queue action.
add_action( 'action_scheduler_after_process_queue', [ $this, 'after_process_queue' ] );
}
/**
* Migrate an entry.
*
* @param int $action_index Action index.
*
* @since 1.7.5
*/
public function migrate( $action_index ) {
global $wpdb;
$db_indexes = [
- 3 => [
'table_name' => $this->entry_meta_handler->table_name,
'index_name' => 'form_id',
'key_part' => 'form_id',
],
- 2 => [
'table_name' => $this->entry_meta_handler->table_name,
'index_name' => 'type',
'key_part' => 'type',
],
- 1 => [
'table_name' => $this->entry_meta_handler->table_name,
'index_name' => 'data',
'key_part' => 'data(32)',
],
];
// We create indexes in the background as it could take significant time on a big database.
if ( array_key_exists( $action_index, $db_indexes ) ) {
$this->add_index(
$db_indexes[ $action_index ]['table_name'],
$db_indexes[ $action_index ]['index_name'],
$db_indexes[ $action_index ]['key_part']
);
return;
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
// The query length in migrate_payment_data() is about 500 chars for 1 entry (7 metas).
// The length of the query is defined by MAX_ALLOWED_PACKET variable, which defaults to 4 MB on MySQL 5.7.
// We increase MAX_ALLOWED_PACKET variable to fit the number of entries specified in self::CHUNK_SIZE.
$new_max_allowed_packet = 500 * self::CHUNK_SIZE;
$max_allowed_packet = (int) $wpdb->get_var( "SHOW VARIABLES LIKE 'MAX_ALLOWED_PACKET'", 1 );
if ( $new_max_allowed_packet > $max_allowed_packet ) {
$wpdb->query( "SET MAX_ALLOWED_PACKET = $new_max_allowed_packet" );
}
// Using OFFSET makes a way longer request, as MySQL has to access all rows before OFFSET.
// We follow very fast way with indexed column (id > $action_index).
$entry_ids = $wpdb->get_col(
$wpdb->prepare(
"SELECT entry_id FROM $this->temp_table_name
WHERE id > %d LIMIT %d",
$action_index,
self::TASK_CHUNK_SIZE
)
);
$i = 0;
$entry_ids_count = count( $entry_ids );
// This cycle is twice less memory consuming than array_chunk( $entry_ids ).
while ( $i < $entry_ids_count ) {
$entry_ids_chunk = array_slice( $entry_ids, $i, self::CHUNK_SIZE );
$this->migrate_payment_data( implode( ',', $entry_ids_chunk ) );
$i += self::CHUNK_SIZE;
}
if ( $new_max_allowed_packet > $max_allowed_packet ) {
$wpdb->query( "SET MAX_ALLOWED_PACKET = $max_allowed_packet" );
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
}
/**
* After process queue action.
* Set status as completed.
*
* @since 1.7.5
*/
public function after_process_queue() {
$tasks = wpforms()->obj( 'tasks' );
if ( ! $tasks || $tasks->is_scheduled( self::ACTION ) ) {
return;
}
$this->drop_temp_table();
// Mark that migration is finished.
update_option( self::STATUS, self::COMPLETED );
}
/**
* Init migration.
*
* @since 1.7.5
* @noinspection PhpUndefinedFunctionInspection
*/
private function init_migration() {
// Get all payment entries.
$count = $this->get_unprocessed_payment_entry_ids();
if ( ! $count ) {
$this->drop_temp_table();
}
// We need 3 preliminary steps to create indexes.
$index = - 3;
while ( $index < $count ) {
// We do not use Task class here as we do not need meta. So, we reduce the number of DB requests.
as_enqueue_async_action(
self::ACTION,
[ $index ],
Tasks::GROUP
);
$index = $index < 0 ? $index + 1 : $index + self::CHUNK_SIZE;
}
}
/**
* Migrate payment data to the correct table.
*
* @param string $entry_ids_list List of entry ids.
*
* @since 1.7.5
*/
private function migrate_payment_data( $entry_ids_list ) {
global $wpdb;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
$wpdb->query(
"SELECT entry_id, form_id, user_id, status, meta, date
FROM {$this->entry_handler->table_name}
WHERE entry_id IN ( $entry_ids_list )"
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
$values = [];
foreach ( $wpdb->last_result as $entry ) {
$meta = json_decode( $entry->meta, true );
if ( ! is_array( $meta ) ) {
continue;
}
foreach ( $meta as $meta_key => $meta_value ) {
// If meta_key doesn't begin with `payment_`, prefix it.
$meta_key = strpos( $meta_key, 'payment_' ) === 0 ? $meta_key : "payment_$meta_key";
// We do not use $wpdb->prepare here, as it is 5 times slower.
// Prepare takes 1.3 sec to prepare 1000 entries (6000 meta records).
// It is incomparable with the two queries here.
// With sprintf, the total processing time of this method is 0.15 sec for 1000 entries.
$values[] = sprintf(
"( %d, %d, %d, '%s', '%s', '%s', '%s' )",
$entry->entry_id,
$entry->form_id,
$entry->user_id,
$entry->status,
$meta_key,
$meta_value,
$entry->date
);
}
}
// Bail out if there is no found payment meta.
if ( empty( $values ) ) {
return;
}
$values = implode( ', ', $values );
// The following query length is about 500 chars for 1 entry (7 metas).
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
$wpdb->query(
"INSERT INTO {$this->entry_meta_handler->table_name}
( entry_id, form_id, user_id, status, type, data, date )
VALUES $values"
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
}
/**
* Get entry ids which do not have relevant entry field records.
* Store them in a temporary table.
*
* @since 1.7.5
*
* @return int
*/
private function get_unprocessed_payment_entry_ids() {
global $wpdb;
$this->drop_temp_table();
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$wpdb->query(
"CREATE TABLE $this->temp_table_name
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
entry_id BIGINT NOT NULL
)"
);
$wpdb->query(
"INSERT INTO $this->temp_table_name (entry_id)
SELECT entry_id
FROM {$this->entry_handler->table_name}
WHERE type = 'payment'
AND entry_id NOT IN
(SELECT entry_id FROM {$this->entry_meta_handler->table_name} WHERE type LIKE 'payment_%')"
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $wpdb->rows_affected;
}
/**
* Drop a temporary table.
*
* @since 1.7.5
*/
private function drop_temp_table() {
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
$wpdb->query( "DROP TABLE IF EXISTS $this->temp_table_name" );
}
}