Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Lets assume following situation:

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

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?
share|improve this question
    
Also of interest: wp.tutsplus.com/tutorials/plugins/… by Stephen Harris. –  Geert Feb 6 '13 at 13:20
add comment

1 Answer

up vote 2 down vote accepted

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
share|improve this answer
    
What if after a while you end up with a lot of database versions and somebody does a fresh install? Instead of a single CREATE TABLE query you end up with a lot of queries to ALTER, UPDATE, ADD INDEX, etc. Have you considered dbDelta()? –  Geert Feb 6 '13 at 13:16
    
@Geert dbDelta gives you more questions than answers. It won't resolve all issues, and you will have to run extra queries to perform all changes. Furthermore you have to understand that the upgrade/installation script will be run only once. So there won't be any issues with running ALTER, UPDATE, ADD INDEX, etc queries only once at all. –  Eugene Manuilov Feb 6 '13 at 13:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.