Function to Maybe Convert Tables to InnoDB

[bhubbard]

We already have a function called maybe_convert_table_to_utf8mb4(), so I think it might make sense to have one to convert the table engine to InnoDB. This could be really useful for developer who want to convert tables they have added, and core can use it on core tables during upgrades.

We can have checks in place for making sure the install is running MySQL 5.6.4 or higher.

maybe_convert_table_to_innodb()

[swissspidy]

and core can use it on core tables during upgrades.

Note that WordPress doesn't specify a database engine and just uses MySQL's default-storage-engine setting.

[bhubbard]

<?php
function maybe_convert_table_to_innodb($table_name) {
  global $wpdb;
  
  // Get the current storage engine for the table
  $current_engine = $wpdb->get_var(
    $wpdb->prepare('SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = %s', $table_name)
  );
  
  // Check if the table is already using InnoDB
  if ($current_engine === 'InnoDB') {
    return; // Nothing to do here
  }
  
  // Convert the table to InnoDB
  $wpdb->query(
    $wpdb->prepare('ALTER TABLE %s ENGINE=InnoDB', $table_name)
  );
}

class ConvertTableToInnoDBTest extends WP_UnitTestCase {
  
  public function setUp() {
    parent::setUp();
    
    // Create a test table using MyISAM engine
    global $wpdb;
    $wpdb->query("
      CREATE TABLE IF NOT EXISTS `wp_test_table` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(50) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    ");
  }
  
  public function tearDown() {
    parent::tearDown();
    
    // Drop the test table
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS `wp_test_table`");
  }
  
  public function test_table_not_converted_if_already_using_innodb() {
    // Arrange
    global $wpdb;
    $table_name = 'wp_test_table';
    
    // Change the engine to InnoDB
    $wpdb->query("ALTER TABLE $table_name ENGINE=InnoDB");
    
    // Act
    maybe_convert_table_to_innodb($table_name);
    
    // Assert
    $this->assertEquals('InnoDB', $wpdb->get_var("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table_name'"));
  }
  
  public function test_table_is_converted_to_innodb() {
    // Arrange
    global $wpdb;
    $table_name = 'wp_test_table';
    
    // Act
    maybe_convert_table_to_innodb($table_name);
    
    // Assert
    $this->assertEquals('InnoDB', $wpdb->get_var("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table_name'"));
  }
  
}

In this example, we create a test table with the MyISAM engine in the setUp method and drop it in the tearDown method. We then write two tests: one to check if the function correctly detects that the table is already using InnoDB and does nothing, and another to check if the function converts the table to InnoDB when needed.

We use global $wpdb object to execute SQL queries to create and manipulate tables and to run our assertions.

[alexcumbers]

What does this request actually do / how does it benefit ?