Drupal 7 Database Updates tricks

mymodule.md

This document contains a common database updates in Drupal 7 projects.

Change a field type from textarea to textfield (wipe out its data)

Let's suppose we want to change field_article_summary from text to textarea, but we do not care about loosing its current data.

Start by removing the field through the Admin Interface in your local environment. Then, add the field with the new configuration and recreate the feature where it was exported. Finally, add the following database update:

/**
 * Recreate field_article_summary since it changed from text to textarea.
 */
function mymodule_update_7010(&$sandbox) {
  // Mark all field instances and field base to be deleted.
  field_delete_field('field_article_summary');
  // Actually perform the deletion.
  field_purge_batch(1000);
  // Revert the feature so the field is added as a textarea.
  features_revert(array('pub_article', 'field:node-article-field_article_excerpt'));
}

Change text field size keeping its data

So this time we want to change the size of a text field. Drupal's Admin Interface won't let us do so so here is how can you achieve it through code.

Start by writing the following database update:

/**
 * Changes a textfield size.
 *
 * Based on Based on http://nathan.rambeck.org/blog/42-modify-drupal-7-text-field-maximum-length
 */
function mymodule_update_7010(&$sandbox) {
  $field_name = 'my_field_name';
  $new_size = 250;

  // First, update the field_data table configuration.
  db_change_field('field_data_' . $field_name, $field_name . '_value', $field_name . '_value', array(
    'type' => 'varchar',
    'length' => $field_length,
  ));

  // Next, update the field revisions table configuration.
  db_change_field('field_revision_' . $field_name, $field_name . '_value', $field_name . '_value', array(
    'type' => 'varchar',
    'length' => $field_length,
  ));

  // Finally, update the field_config table.
  $result = db_query("SELECT CAST(`data` AS CHAR(10000) CHARACTER SET utf8) FROM {field_config} WHERE field_name = :field_name", array(':field_name' => $field_name);
  $config = $result->fetchField();
  $config_array = unserialize($config);
  $config_array['settings']['max_length'] = $field_length;
  $config = serialize($config_array);
  db_update('field_config')
    ->fields(array('data' => $config))
    ->condition('field_name', $field_name)
    ->execute();
}

Next, get a fresh copy of production's database and run database updates. The above code will run and change your field configuration. Finally, recreate the feature(s) where this field is exported and push your changes to Git.

Uninstall a feature module and remove its content types, fields and taxonomies.

Uninstalling a feature module won't delete its components from the database. Here is a sample database update to remove its content types, fields and taxonomies.

/**
 * Uninstalls a module and its content types, features and taxonomies.
 *
 * Based on http://drupal.stackexchange.com/questions/44547/unable-to-delete-feature-content-type-after-uninstalling-feature.
 */
function mymodule_update_7010(&$sandbox) {
  $module = 'module_name';
  $content_types = array('content_type_a', 'content_type_b');
  $taxonomies = array('taxonomy_a', 'taxonomy_b');

  // Start by uninstalling the module.
  module_disable(array($module));
  drupal_uninstall_modules(array($module));

  // Make its content types fields deletable.
  db_query("UPDATE {node_type} SET custom = 1, locked = 0 WHERE type IN (:ctype)", array(':ctype' => $content_types));
  drupal_flush_all_caches();
  // Actually remove the content type and its fields,
  node_type_delete($content_type);

  // Remove its taxonomies.
  foreach ($taxonomies as $taxonomy) {
    $taxonomy = taxonomy_vocabulary_machine_name_load($machine_name);
    if (is_object($taxonomy)) {
      taxonomy_vocabulary_delete($taxonomy->vid);
    }
  }
}

/**
 * Deletes field instances of a given list of modules.
 *
 * Note: caches needs to be cleared and features should be
 * reverted after this.
 *
 * @param string $field_name
 *   The name of the field instance to delete.
 * @param array $content_types
 *   The list of content types in which this field has to be deleted.
 */
function mymodule_kill_field($field_name, $content_types) {
  $field = field_info_field($field_name);
  if (isset($field['bundles'])) {
    foreach ($field['bundles'] as $entity_type => $bundles) {
      foreach ($bundles as $bundle) {
        // Keep the Issue field at Sub Issue and Sub-issue field at Topic.
        if (!in_array($bundle, $content_types)) {
          $instance = field_info_instance($entity_type, $field_instance, $bundle);
          field_delete_instance($instance, FALSE);
        }
      }
    }
  }
}

/**
 * Install and revert a feature module in order to work with it.
 *
 * If your feature module includes fields, it has to be reverted before you
 * can work with them.
 */
function mymodule_update_7000(&$sandbox) {
  module_enable(array('my_feature_module'));
  features_revert_module('a_feature_module');
  // Now all the exported configuration is active and we can access its fields, etc.
}

/**
 * Deletes a field base and all of its instances.
 *
 * Note, if this field is exported into a feature, you need to recreate the feature
 * previously removing the field base and instances from it. You can do this through
 * the Features UI. This update will take care of removing the data and tables from
 * the database.
 */
function mymodule_update_7000(&$sandbox) {
  field_delete_field('field_fieldname');
  field_purge_batch(1000);
}

Here's a slightly more fool-proof method to extend the size of a textfield.

/*
 * Utility function to change the max length of a text field.
 */
function example_textfield_max_length($field_name, $new_length) {
  // Update the field column.
  $spec = array(
    'type' => 'varchar',
    'not null' => FALSE,
    'length' => $new_length,
  );
  $field = field_read_field($field_name, TRUE);
  if ($new_length >= $field['columns']['value']['length']) {
    if ($field['deleted']) {
      $table = "field_deleted_data_{$field['id']}";
      $revision_table = "field_deleted_revision_{$field['id']}";
    }
    else {
      $table = "field_data_{$field['field_name']}";
      $revision_table = "field_revision_{$field['field_name']}";
    }
    $column = $field['field_name'] . '_' . 'value';
    db_change_field($table, $column, $column, $spec);
    db_change_field($revision_table, $column, $column, $spec);

    // Update the field config.
    $result = db_select('field_config', 'fc')
      ->fields('fc', array('data'))
      ->condition('field_name', $field_name)
      ->execute()
      ->fetchField();
    $config = unserialize($result);
    $config['settings']['max_length'] = $new_length;
    db_update('field_config')
      ->fields(array('data' => serialize($config)))
      ->condition('field_name', $field_name)
      ->execute();
  }
  else {
    throw new DrupalUpdateException(t('New field length (@new_length) is smaller than the existing value (@existing).', array(
      '@new_length' => $new_length,
      '@existing' => $field['columns']['value']['length'],
    )));
  }
}

https://www.drupal.org/project/drupal_helpers handles a lot of these and more

For the section: "Change text field size keeping its data"

• missing a right parens in the db query statement
• the var names aren't matching for $new_size and $field_length, which I assume are supposed to be the same