[wp-trac] [WordPress Trac] #43416: Function to Maybe Convert Tables to InnoDB

[WordPress Trac]

#43416: Function to Maybe Convert Tables to InnoDB
-----------------------------+------------------------------
 Reporter:  bhubbard         |       Owner:  (none)
     Type:  feature request  |      Status:  new
 Priority:  normal           |   Milestone:  Awaiting Review
Component:  Upgrade/Install  |     Version:
 Severity:  normal           |  Resolution:
 Keywords:                   |     Focuses:  performance
-----------------------------+------------------------------

Comment (by bhubbard):

 {{{#!php
 <?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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/43416#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform