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

by

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