Updating tables: hook_update_N() functions

Update database tables for new versions using a hook_update_N() function. Note that when you create a hook_update_N() function, you should add a documentation header to the function, whose first line concisely describes the update(s) to be performed. This documentation will be displayed to the user when running update.php.

Adding a new column

Using Schema API, you perform the same two steps:

1. Add newcol to the table definition array in mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with the Schema API function db_add_field():
   

   /**
    * Add newcol field to {mytable1} table.
    */
   function mymodule_update_7100() {
     $spec = array(
       'type' => 'varchar',
       'description' => "New Col",
       'length' => 20,
       'not null' => FALSE,
     ); 
     db_add_field('mytable1', 'newcol', $spec);
   }
   

The only difference from D6 is the returned value, which is no longer the result of update_sql(), but an optional translated string.

Adding a new column to a custom field

Using Field API, you've already created a new field and it works great. You want to add a new form element to that field.

The original schema, using hook_field_schema():

/**
 * Implements hook_field_schema().
 */
function mymodule_field_schema($field) {
  return array(
    'columns' => array(
      'fid' => array(
        'description' => 'The {file_managed}.fid being referenced in this field.',
        'type' => 'int',
        'not null' => FALSE,
        'unsigned' => TRUE,
      ),
      'title' => array(
        'description' => "Photo title text",
        'type' => 'varchar',
        'length' => 128,
        'not null' => FALSE,
      ),
    ),
    'indexes' => array(
      'fid' => array('fid'),
    ),
    'foreign keys' => array(
      'fid' => array(
        'table' => 'file_managed',
        'columns' => array('fid' => 'fid'),
      ),
    ),
  );
}

To add a new column, do the following two steps:

1. Add newcol to the table definition array in mymodule_field_schema() in mymodule.install. When you do this, the field configuration is automatically updated as field_read_fields invokes the updated schema in the install file.
2. Create a mymodule_update_N() function to add newcol to existing mytable1 tables with the Schema API function db_add_field(), but also add it to the revision table:
   

   /**
    * Add new field to field data and revision tables.
    */
   function mymodule_update_7100(&$sandbox) {
     $spec = array(
       'type' => 'varchar',
       'description' => "New Col",
       'length' => 20,
       'not null' => FALSE,
     );
     $data_table_name = 'field_data_field_myfield';
     $revision_table_name = 'field_revision_field_myfield';
     $field_name = 'field_myfield_newfield';
   
     db_add_field($data_table_name, $field_name, $spec);
     db_add_field($revision_table_name, $field_name, $spec); 
   }
   

Updating tables and columns belonging to a custom field

If you want to update a custom field, say to change the precision of the underlying column (or columns) from single to double, you often face the problem of not knowing what its associated table and column names are, as they're based on the machine name as entered by the user in the "Manage Fields" section of the entity in question. hook_field_schema() doesn't have all the information and neither does hook_field_info(). However hook_field_info() does contain the field type and so the fields created of your module's type can be found and their associated database tables and columns targeted. Start with something along these lines:

/**
  * Returns all fields created on the system of the type defined in mymodule.
  */ 
function mymodule_get_mymodule_fields() {
  $types = array_keys(mymodule_field_info()); // field types defined in mymodule
  $fields = array();
  foreach (field_info_fields() as $field) {
    if (in_array($field['type'], $types)) {
      $fields[] = $field;
    }
  }
  return $fields;
}

Armed with the above helper function, we can now update the associated database columns like so

/**
 * Convert column(s) to double precision.
 */
function mymodule_update_7101() {
  $fields = mymodule_get_mymodule_fields();

  foreach ($fields as $field) {
    $table_prefixes = array(
      _field_sql_storage_tablename($field), 
      _field_sql_storage_revision_tablename($field)
    );
    foreach ($table_prefixes as $table_prefix) {

      $field_name = $field['field_name']; // eg 'field_dimensions' ;
      $table = $table_prefix . $field_name;

      // Convert two db columns from float to double precision
      $column1 = $field_name . '_suffix1_as_used_in_hook_field_schema';
      $column2 = $field_name . '_suffix2_as_used_in_hook_field_schema';
      $spec = array('type' => 'float', 'size' => 'big', 'default' => 0.0);

      db_change_field($table, $column1, $column1, $spec); // old and new col are same
      db_change_field($table, $column2, $column2, $spec); // old and new col are same
    }
  }
  return t('Database columns converted to double precision.');
}

In addition to this, you may want to re-calculate and re-store existing data as double precision numbers. This means you'll have to query and update all existing rows in all tables associated with the fields by the type defined in your mymodule_field_info(). An example of this can be found here: http://drupal.org/node/1699326#comment-6809784
All in all quite a bit of work. I'd love to stand corrected and find an easier way!

Adding a new table

Similarly, suppose that for version 7.x-1.6 mymodule now needs a completely new table called mytable2. You perform the same two steps:

1. Add the new table to mymodule_schema() in mymodule.install.
2. Create a mymodule_update_N() function to create mytable 2 with the Schema API function db_create_table():

hook_schema()

/**
 * Implements hook_schema().
 */
function mymodule_schema() {
  $schema['mytable2'] = array(
    'description' => 'My description',
    'fields' => array(
      'myfield1' => array(
        'description' => 'Myfield1 description.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'myfield2' => array(
        'description' => 'Myfield2 description',
        'type' => 'text',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('myfield1'),
  );

  return $schema;
}

hook_update_N()

/**
 * Create new database table {mytable2}.
 */
function mymodule_update_7102() {

  // Define the new table schema.
  // It is important to duplicate the code from hook_schema() here,
  // rather than load the schema from mymodule_schema().
  // See https://www.drupal.org/node/150220
  $schema['mytable2'] = array(
    // table definition array goes here
  );

  // Pass the table name and schema for that table to db_create_table()
  db_create_table('mytable2', $schema['mytable2']);
}

Adding keys

And as for adding a unique, or a primary key, one can now use dedicated API functions:

/**
 * Add primary key to table {mytable2}.
 */
function mymodule_update_7103() {
  $ret = array();
  db_add_unique_key($ret, 'mytable2', 'mykey', array('field1', 'field2'));  
  return $ret;
}

/**
 * Adding primary key to table {mytable2}.
 */
function mymodule_update_7104() {
  $ret = array();
  db_add_primary_key($ret, 'mytable2', array('nid'));
  return $ret;
}

Important note: You may be tempted to pass a table definition from your own hook_schema function directly to db_create_table(). Please read why you cannot use hook_schema from within hook_update_N().

Updating a table

This simple example shows how to change a modules weight using an sql UPDATE with the function db_query()

/**
 * Change module weight for the 'mymodule' module.
 */
function mymodule_update_7105() {
  $ret = array();
  db_query("UPDATE {system} SET weight = [your_preferred_weight] WHERE type = 'module' AND name = '[your_module_name]'");
}

Adding multiple new fields to a table

Here is an example how to add multiple new fields in an existing DB table with hook_update_N() (Drupal 7)

/**
 * Add new fields to 'mytable' table.
 */
function MYMODULE_update_7106() {
  $fields = array(
    'field_name_1' => array(
      'description' => 'Field name 1',
      'type' => 'varchar',
      'length' => '32',
      'not null' => FALSE,
      'default' => '',
    ),
    'field_name_2'=> array(
      'description' => 'Field name 2',
      'type' => 'varchar',
      'length' => '32',
      'not null' => FALSE,
      'default' => '',
    ),
    'field_name_3' => array(
      'description' => 'Field name 3',
      'type' => 'varchar',
      'length' => '128',
      'not null' => FALSE,
      'default' => '',
    ),
  );

  foreach ($fields as $key => $field)  {
    if (!db_field_exists('mytable', $key)) {
      db_add_field('mytable', $key, $field);
    }
  }
}