What is the easiest way to implement cascading database upgrade for my plugin?

1 comment

1. Ok, to resolve these issues, let’s implement the cascading upgrade process which will handle both use cases.

   First of all lets implement our plugin activation hook, which will be our entry point:

   // define current plugin version
   define( 'WPSE8170_PLUGIN_VERSION', '2.0.0' );
   // define our database table name
   define( 'WPSE8170_DB_TABLE', $GLOBALS['wpdb']->prefix . 'wpse8170_test_table' );
   
   add_action( 'init', 'wpse8170_plugin_upgrade' ); // check database on init action, to be confident that our plugin database is up-to-date
   register_activation_hook  ( __FILE__, 'wpse8170_plugin_upgrade' );
   function wpse8170_plugin_upgrade() {
       $filter = 'wpse8170_upgrade_db';
       $option = 'wpse8170_db_version';
   
       // get current database version
       $db_version = get_option( $option );
   
       // if database version is not exists, lets create new and set it to '0.0.0'
       if ( $db_version === false ) {
           $db_version = '0.0.0';
           add_option( $option, $db_version, '', 'yes' );
       }
   
       // check database version, if it equals to current plugin version, then no upgrades are required
       if ( version_compare( $db_version, WPSE8170_PLUGIN_VERSION, '=' ) ) {
           return;
       }
   
       // define our upgrade hooks, which will be called to upgrade database to a certain version
       add_filter( $filter, 'wpse8170_upgrade_to_10000' ); // upgrade db to version 1.0
       add_filter( $filter, 'wpse8170_upgrade_to_11000' ); // upgrade db to version 1.1
       add_filter( $filter, 'wpse8170_upgrade_to_20000' ); // upgrade db to version 2.0
   
       // apply our upgrade filter and update database version 
       update_option( $option, apply_filters( $filter, $db_version ) );
   }
   

   Before start looking at upgrade hooks, lets create a helper function which will help us to execute a set of sql queries:

   function wpse8179_execute_upgrade_queries( array $queries ) {
       global $wpdb;
       foreach ( $queries as $query ) {
           $wpdb->query( $query );
       }
   }
   

   Finally, lets see our upgrade hooks. Upgrade database from 0.0.0 to 1.0.0:

   function wpse8170_upgrade_to_10000( $current_version ) {
       // define version of current upgrade hook
       $this_version = '1.0.0';
   
       // if the version of current upgrade hook is less or equals to current database version, return it without any changes
       if ( version_compare( $current_version, $this_version, '>=' ) ) {
           return $current_version;
       }
   
       // execute all required queries to make database corresponding to current upgrade version
       wpse8179_execute_upgrade_queries( array(
           sprintf( "CREATE TABLE IF NOT EXISTS `%s` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `type` VARCHAR(15) NOT NULL, `data` TEXT NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM", WPSE8170_DB_TABLE ),
           // above queries could be merged into one, but added as an example
           sprintf( "ALTER TABLE `%s` CHARACTER SET = utf8, COLLATE = utf8_general_ci;", WPSE8170_DB_TABLE ),
           sprintf( "ALTER TABLE `%s` CHANGE COLUMN `data` `data` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL;", WPSE8170_DB_TABLE ),
           sprintf( "ALTER TABLE `%s` ADD INDEX `gchart_idx_type` (`type` ASC)", WPSE8170_DB_TABLE ),
       ) );
   
       // return current upgrade version, which is equals to 1.0.0
       return $this_version;
   }
   

   Upgrade database from 1.0.0 to 1.1.0:

   function wpse8170_upgrade_to_11000( $current_version ) {
       // define version of current upgrade hook
       $this_version = '1.1.0';
   
       // if the version of current upgrade hook is less or equals to current database version, return it without any changes
       if ( version_compare( $current_version, $this_version, '>=' ) ) {
           return $current_version;
       }
   
       // execute all required queries to make database corresponding to current upgrade version
       wpse8179_execute_upgrade_queries( array(
           // update table column type
           sprintf( "ALTER TABLE `%s` CHANGE COLUMN `data` `data` MEDIUMTEXT NOT NULL", WPSE8170_DB_TABLE ),
           // update table data
           sprintf( "UPDATE `%s` SET ... WHERE ...", WPSE8170_DB_TABLE ),
       ) );
   
       // return current upgrade version, which is equals to 1.1.0
       return $this_version;
   }
   

   Upgrade database from 1.1.0 to 2.0.0:

   function wpse8170_upgrade_to_20000( $current_version ) {
       // define version of current upgrade hook
       $this_version = '2.0.0';
   
       // if the version of current upgrade hook is less or equals to current database version, return it without any changes
       if ( version_compare( $current_version, $this_version, '>=' ) ) {
           return $current_version;
       }
   
       // execute all required queries to make database corresponding to current upgrade version
       wpse8179_execute_upgrade_queries( array(
           // update table by adding two new columns
           sprintf( "ALTER TABLE `%s` ...", WPSE8170_DB_TABLE ),
           // update table data by splitting data from old column into two new
           sprintf( "UPDATE `%s` SET ... WHERE ...", WPSE8170_DB_TABLE ),
           // delete deprecated column from the table
           sprintf( "ALTER TABLE `%s` ...", WPSE8170_DB_TABLE ),
       ) );
   
       // return current upgrade version, which is equals to 2.0.0
       return $this_version;
   }
   

   Lets see how our approach handle both use cases:

   1. After upgrading from version 1.0.0 to version 2.0.0, we will skip hook wpse8170_upgrade_to_10000 as we already have database version equals to 1.0.0 and pass through hooks wpse8170_upgrade_to_11000 and wpse8170_upgrade_to_20000 upgrading our database to the latest version including missed version 1.1.0
   2. After installing the latest version of the plugin on a blank WP instance, we will pass all upgrade hooks and build our database table with all changes included in versions 1.0.0 and 1.1.0

   Log in to Reply