How to run alter query to modify mysql database using hook_schema?

Question

I want to change data-type of a field in for a particular table in database. I want to use hook_schema_alter() to do so. The ALTER query is:

ALTER TABLE `abcd` CHANGE `session_number` `session_number` INT( 255 ) NOT NULL COMMENT 'Session number to get number.'

Is it possible to implement such query using hook ? If yes please guide me. Tonnes of thanx in advance.

Answer 1

As you mentioned you are having single Drupal code base for multiple sites. I assume that you only want one of the sites having schema change. So you need a separate module for altering schema.

You need to implement hook_schema_alter() and hook_enable() in .install file of your custom module.

/**
 * Implements hook_enable().
 */
function mymodule_enable() {
  // other_module is the module name which define the schema of 'abcd' table.
  $schema = drupal_get_schema_unprocessed('other_module', 'abcd');
  db_change_field('abcd', 'session_number', 'session_number', $schema['fields']['session_number']);
}

/**
 * Implements hook_schema_alter().
 */
function mymodule_schema_alter(&$schema) {
  $schema['abcd']['fields']['session_number'] = array(
    'type' => 'int',
    'size' => 'tiny',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Session number to get number.',
  );
}

Please note that 'size' => 'tiny' means 1 byte size for integer type. More details can be found here.

Answer 2

Changes to the schema are typically handled with hook_update_N and you will normally locate this file in the module.install file.

For example with the update from Drupal 6.x-7.x the some of the base permissions were rename:

/**
 * Rename blog and forum permissions to be consistent with other content types.
 */
function system_update_7000() {
  $result = db_query("SELECT rid, perm FROM {permission} ORDER BY rid");
  foreach ($result as $role) {
    $renamed_permission = $role->perm;
    $renamed_permission = preg_replace('/(?<=^|,\ )create\ blog\ entries(?=,|$)/', 'create blog content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )edit\ own\ blog\ entries(?=,|$)/', 'edit own blog content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )edit\ any\ blog\ entry(?=,|$)/', 'edit any blog content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )delete\ own\ blog\ entries(?=,|$)/', 'delete own blog content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )delete\ any\ blog\ entry(?=,|$)/', 'delete any blog content', $renamed_permission);

    $renamed_permission = preg_replace('/(?<=^|,\ )create\ forum\ topics(?=,|$)/', 'create forum content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )delete\ any\ forum\ topic(?=,|$)/', 'delete any forum content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )delete\ own\ forum\ topics(?=,|$)/', 'delete own forum content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )edit\ any\ forum\ topic(?=,|$)/', 'edit any forum content', $renamed_permission);
    $renamed_permission = preg_replace('/(?<=^|,\ )edit\ own\ forum\ topics(?=,|$)/', 'edit own forum content', $renamed_permission);

    if ($renamed_permission != $role->perm) {
      db_update('permission')
        ->fields(array('perm' => $renamed_permission))
        ->condition('rid', $role->rid)
        ->execute();
    }
  }
}