Using .install Files to Update Database Schema

I just committed what will hopefully be the first in a series of updates to the NodeReview module. I actually use a customized version of this module over on DrupalModules.com, and now that Crell has made me a co-maintainer, I'm working on integrating those enhancements back into the official release.

This new update adds an editable description field to each axis. Here's a screenshot of the new feature in action. The changes have already been committed to CVS, and should be available in the dev snapshot later tonight.

Because this update required adding a new field to the nodereview_axes table, I had to learn some new .install file tricks, which I'll now explain in this article.

When making changes to a module's database schema, you need to provide an upgrade path for users who already have older versions of the module installed. This is done through the use of update functions.

Update functions provide the code that will be run when a user accesses update.php (which, of course, you're supposed to do after upgrading any installed module).

The update function should return an array of SQL commands. The goal is to bring the old database into synchronization with the expectations of the new module code.

Here's the update function I added to the nodereview.install file:

function nodereview_update_1() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysqli':
    case 'mysql':
      $ret[] = update_sql("ALTER TABLE {nodereview_axes} ADD COLUMN
description varchar(1024) NOT NULL default ''");
      break;
    case 'pgsql':
      db_add_column($ret, 'nodereview_axes', 'description',
'varchar(1024)', array('default' => '', 'not null' => TRUE));
      break;
  }
  return $ret;
}

In this case, I'm simply adding a new varchar field called "description" to the nodereview_axes table. It's also possible to do more complex database manipulations, like changing character sets or creating md5 hashes of fields, but none of that was required for my update.

I should mention, this is all Drupal 5 code, but the idea is the same for Drupal 6. More information about writing .install files can be found here.

Update: This code is now Postgres compatible. The previous version I posted only worked with MySQL. Thanks to Dave at Advomatic for the tip.

Posted by John on 2008-04-14