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

Lets assume following situation:

I develop a plugin. The plugin requires custom database table to store some kind of information.

Read More

From month to month I release new versions of my plugin. Different versions could have their own sets of changes of the table structure and table’s data. For instance:

  • version 1.0.0 has initial setup for table structure
  • version 1.1.0 has changes to one column type and requires to perform update operation over data of this column
  • version 2.0.0 requires creation of two new columns, splitting data from old column to new columns and removal of old/deprecated column

The problem:

Lets consider two use cases:

  1. An user downloaded version 1.0.0, skipped update to version 1.1.0 and decided to update the plugin when the version 2.0.0 was released. How to organize database upgrade process, which will handle upgrade from version 1.0.0 to version 2.0.0 including changes from version 1.1.0?
  2. An user downloaded version 2.0.0 and installed it on a blank instance of WordPress. How to perform the latest version of database installation, which will include all changes from all versions compliance with DRY principles?

Related posts

Leave a Reply

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