Over the lifetime of your custom table you will probably find that you need to make changes to what it stores, or how it stores it. This may be in response to a need to store more (or less) data. It may be that the initial design of your database wasn’t intended to deal (efficiently) with what your user-base is now demanding. Either way, we need to know how to adapt our table to meet our new needs. This is what we’ll be looking at in this tutorial, and we’ll primarily focused on the function dbDelta() that we first met in part one.

dbDeta

Fortunately most of the legwork in handling database changes is done by the WordPress function dbDelta(). We used this function in part one to create our table, but it actually does a lot more than that: prior to performing the query we gave it, it checks if the table already exists. If not, it creates the table, but if it does exist – it compares the difference (hence the name) and makes some changes. This is why in part one we didn’t manually check if the table already existed.

If the table already exists, but is different to the table given by the SQL (for example the existing table has a missing column, or a different column collation), then dbDelta() automatically applies these updates. In this way we can release a new version of our plug-in which alters our table by simply applying `dbDelta()` with the altered SQL. Almost.

Unfortunately, dbDelta() doesn’t apply all changes. Lets suppose that in our latest plug-in release we have no need for a column, and we want to remove it. So we remove it from the SQL query in part one, and in the upgrade routine call wptuts_create_tables(). After upgrading we’ll find that the column is still there. Worse than that: users upgrading from the old version to the new version will then have a structurally different table than those who start with the new version.

So what does dbDelta() actually do?

Let’s remind ourselves of the SQL query we pass to dbDelta() when creating the table:

$sql_create_table = "CREATE TABLE ". $wpdb->wptuts_activity_log." (
          log_id bigint(20) unsigned NOT NULL auto_increment,
          user_id bigint(20) unsigned NOT NULL default '0',
          activity varchar(20) NOT NULL default 'updated',
          object_id bigint(20) unsigned NOT NULL default '0',
          object_type varchar(20) NOT NULL default 'post',
          activity_date datetime NOT NULL default '0000-00-00 00:00:00',
          PRIMARY KEY  (log_id),
          KEY user_id (user_id)
     ) $charset_collate; ";
dbDelta($sql_create_table);

First it extracts all the CREATE TABLE queries (you can pass multiple queries to dbDelta() at once, separating them by a ';', but to improve readability I prefer not to). From this it takes the table name, $table, and runs

 $wpdb->get_results("DESCRIBE {$table};");

This returns an array of existing columns – each column is actually an object containing information pertaining to that column (its name, type, default value etc). For example our log_id column looks like:

stdClass Object (
            [Field] => log_id
            [Type] => bigint(20) unsigned
            [Null] => NO
            [Key] => PRI
            [Default] =>
            [Extra] => auto_increment
        )

If the table doesn’t exist, then an empty array is returned and the table is created. Otherwise dbDelta() then goes through each line of the passed query, extracts the columns and stores them in an array $cfields. It does likewise with each of the keys (including primary).

Next it goes through each of the existing columns. If they are present in the above array, $cfields, they are removed. It then compares their type, if they do not match, it automatically generates a corresponding ALTER TABLE query to be performed later. After its done this, the only columns left in $cfields are the ones that do not already exist. From this it generates further ALTER TABLE queries to create these columns.

It then performs an almost identical procedure for keys.

Be Careful

The ability of dbDelta() to do all this analysis comes at a cost: its fussiness of what it’ll accept (or correctly interpret). For instance:

• Each part of the query (e.g. each column and key declaration) must have its own line. For example

   user_id bigint(20) unsigned NOT NULL default ’0′,activity varchar(20) NOT NULL default ‘updated’,

  will act as if the activity column is not present. The correct format is:

        user_id bigint(20) unsigned NOT NULL default '0',
        activity varchar(20) NOT NULL default 'updated',

• You must use KEY rather than its synonym INDEX.
• Any KEYs must be given a name. For example, do not write

  KEY (user_id)]

  instead it should be

  KEY user_id (user_id)

  (though the name does not have to be the same as the column).

• The PRIMARY KEY should not be given a name, but rather there must be two spaces between PRIMARY KEY and the column declaration: (log_id). For example,

   PRIMARY KEY (log_id),

  will cause an error. The correct format is:

        PRIMARY KEY  (log_id),

This isn’t a complete list, as general rule you should avoid extra spaces around and between keywords, such as CREATE and TABLE and there should be no extra spaces around columns. The internals of dbDelta() rely on using preg_match() to extract information from the passed SQL statement – and as such things can go wrong fairly easily if that statement isn’t suitably formatted.

Some of these errors will occur silently (for instance if you do not give a KEY a name, dbDelta() will keep duplicating it). For this reason it’s important that you inspect your table manually (using phpMyAdmin or similar) to check that your code is functioning correctly.

Adding or Changing Columns

With dbDelta(), this is really simple – let’s suppose we want to make object_id an index, add an additional column user_ip to store the user’s IP address and change the type of the activity column to varchar(30), we simply replace the original SQL Query with:

$sql_create_table = "CREATE TABLE ". $wpdb->wptuts_activity_log." (
          log_id bigint(20) unsigned NOT NULL auto_increment,
          user_id bigint(20) unsigned NOT NULL default '0',
          user_ip varchar(15),
          activity varchar(30) NOT NULL default 'updated',
          object_id bigint(20) unsigned NOT NULL default '0',
          object_type varchar(20) NOT NULL default 'post',
          activity_date datetime NOT NULL default '0000-00-00 00:00:00',
          PRIMARY KEY  (log_id),
          KEY user_id (user_id),
          KEY object_id (object_id),
     ) $charset_collate; ";

Then we just ensure we call wptuts_create_tables() in the upgrade routine, and the changes will take effect.

Removing Columns

Since dbDelta() will not remove columns, simply removing the appropriate line from the query will not suffice (it is still necessary though). Instead we need to do things manually.

First, extract an array of existing colums:

 $existing_columns = $wpdb->get_col("DESC {$wpdb->wptuts_activity_log}", 0);

Then, if the columns we wish to remove are present, we can remove them with an ALTER TABLE query:

 $remove_columns = array('object_id' ); //Array of columns to remove
 $remove_columns = array_intersect($remove_columns, $existing_columns);

 if( !empty($remove_columns) )
     $wpdb->query("ALTER TABLE {$wpdb->wptuts_activity_log} DROP COLUMN ".implode(', DROP COLUMN ',$remove_columns).';');

Removing Keys

Just as we did with columns, first get an array of indexes:

$existing_keys = $wpdb->get_col("SHOW INDEX FROM {$wpdb->wptuts_activity_log} WHERE Key_name != 'PRIMARY';",2);

Then, if the keys we wish to remove exists, we can remove them just as above, but now using DROP INDEX

$remove_keys = array('user_id');//Array of keys to remove
$remove_keys = array_intersect($remove_keys, $existing_keys);

if( !empty($remove_keys) )
    $wpdb->query("ALTER TABLE {$wpdb->wptuts_activity_log} DROP INDEX ".implode(', DROP INDEX ',$remove_keys).';');

Upgrade Routine

Now that we know how to upgrade our database – lets look at how we should handle this in our plug-in. We’ll store all our upgrade handling inside the function: wptuts_activity_log_upgradecheck(). Note that the plug-in activation hook will not be triggered when updating a plug-in: in order to ensure our upgrade routine does its job, we’ll hook onto admin_init.

To check which upgrade routines we need to perform, we’ll store the plug-in version in the database. We’ll compare this version (the installed version) to the current (activated) version of the plug-in:

• If there is no version in the database, it’s a fresh install and we’ll just add the current version
• If there is a version in the database, and it’s the current version, we do nothing
• Otherwise it’s an older version, so we’ll go through all the necessary upgrade routines.

add_action('admin_init', 'wptuts_activity_log_upgradecheck');
function wptuts_activity_log_upgradecheck(){
   //Version of currently activated plugin
   $current_version = '1.3';
   //Database version - this may need upgrading.
   $installed_version = get_option('wptuts_activity_log_version');

   if( !$installed_version ){
       //No installed version - we'll assume its just been freshly installed
       add_option('wptuts_activity_log_version', $current_version);

   }elseif( $installed_version != $current_version ){
         /* 
          * If this is an old version, perform some updates.
          */
   
         //Installed version is before 1.1 - upgrade to 1.1
         if( version_compare('1.1', $installed_version) ){
             //Code to upgrade to version 1.1
         }

         //Installed version is before 1.3 - upgrade to 1.3
         if( version_compare('1.3', $installed_version) ){
             //Code to upgrade to version 1.3
         }

         //Database is now up to date: update installed version to latest version
         update_option('wptuts_activity_log_version', $current_version);
   }
}

Each of the individual upgrade routines should should ensure the database is ‘up to date’ by using the code discussed in the earlier sections. Importantly, if we make any changes to the CREATE TABLE SQL, you must remember to run that query through dbDelta() (in our example, by calling wptuts_create_tables() as part of the upgrade routine) for the changes to take effect.

Be careful with how you handle updates when using dbDelta. Remember that some users might be upgrading across two or more updates. So if such changes cannot be made in parallel - then you'll need to upgrade in stages, calling `dbDelta()` several times, making the appropriate changes for that stage.

Uninstall Routine

While we’re at it, let’s look at cleaning up after ourselves when the plug-in uninstalled. These are generally very simple routines: just remove the database table, any options saved and any cron jobs your plug-in may have activated. We hook our routine onto the uninstall hook using register uninstall hook()

register uninstall hook(__FILE__,'wptuts_uninstall_plugin');
function wptuts_uninstall_plugin(){
    global $wpdb;
    //Remove our table (if it exists)
    $wpdb->query("DROP TABLE IF EXISTS $wpdb->wptuts_activity_log");

    //Remove the database version
    delete_option('wptuts_activity_log_version');

    /*Remove any other options your plug-in installed and clear any plug-in cron jobs */
}