Manipulating Your Drupal 7 Database

You know the old saying: “This is how the world ends: not with a bang, but with a misplaced DROP TABLE.” Working directly with Drupal 7’s database is an arduous task at best.  It’s a sprawling relational system and it uses many space and memory saving tricks to be as speedy as possible.  Thankfully, there is a robust system of functions built into Drupal to help you change almost any setting from code–perfect if you want to automate changes upstream and features doesn’t do it for you.  Let’s go over a situation in which you may have been utilizing some of these functions.

Let’s say you finished your product (congratulations!), launched, and are onto fixing bugs and planning exciting new features for the future.  You’re knocking out bugs left and right like some high-flying Drupal ninja and you discover that using a field collection with conditional fields causes the field collection data to not save and all of your metadata is getting erased when certain conditions are fired.  With Cthulhu’s hot breath on your neck, you talk to the client and realize a ray of hope: you don’t actually need a field collection there, a normal set of Drupal fields will do.  How do we go about creating the new fields, copying existing data, and deleting the old fields?

The first thing we do is create the new fields and attach them.  For this, we’ll need two functions: ‘field_create_field()’ and ‘field_create_instance()’.  Both of these take an array of settings: field_name and type are we need for creating the field (also cardinality if you want to have multiple values for the field), field_name, entity_type, and bundle are required for creating the instances, though you will likely also want label, or it will otherwise default to the machine name.  So, we should have something that looks like this:

$name = [
  ‘field_name’ => 'photographer_name',
  ‘type’ => ‘text’,
];
field_create_field($name);

$instance = array(
  'field_name' => $name['field_name'],
  'entity_type' => node,
  'bundle' => article,
  'label' => 'Name',
);

field_create_instance($instance);

If you go check out node/add/article, you should see your new text field there.  Congrats!  Next, we need to get the data from the old fields and copy it into our new field.  For this, we’ll rely on the nifty function 'entity_load()'.  This takes two arguments, bundle name and an array of ids.  Since we are getting field collection items, we know the bundle name is ‘field_collection_item’.  We’ll need the IDs, but we’ll also need the field collection value that references the fields in each collection for later, so we’ll get them both at once.  It might be tempting to use 'entity_load()' to get them, but in this case you are quite safe using straight SQL, which also happens to be significantly faster.  That looks like this:

$entity_ids = array();
$field_collection_ids = array();
// Select the field collection id and the attached entity id from the db.
$query = db_query('SELECT field_producer_value, entity_id FROM field_data_field_producer');
$results = $query->fetchAll();
// Separate the ids
foreach ($results as $result) {
  $field_collection_ids[] = $result->field_scald_producer_value;
  // We need to reference the entity ID by the field collection value for simplicity later
  $entity_ids[$result->field_scald_producer_value] = $result->entity_id;
}
// It’s possible that you might get duplicate Field Collection IDs, so we make sure they are all unique
$field_collection_ids = array_unique($field_collection_ids);
// Load all of the field collection entities.
$field_collection_results = entity_load('field_collection_item', $field_collection_ids);

Now that we have all of the entity ids and field collection ids, we can get to the fun part: copying data! (You know you have been doing this too long when that is exciting.) What we want to do is loop through the field collection ids, load the the entity (that has the new field on it) by the id associated with the collection, copy the data from the collection to the new field, and save.  It seems like a lot, but it’s fairly simple:

foreach ($field_collection_ids as $field_collection_id) {
  // Load the entity the field collection is attached to
  $entity = entity_load('node', array($entity_ids[$field_collection_id]));
  // Copy the data from the collection field to the new field
  $entity[$entity_ids[$field_collection_id]]->photographer_name['und'][0]['value'] =
  $field_collection_results[$field_collection_id]->field_producer_name['und'][0]['value'];
  // Save!
  entity_save('node', $entity[$entity_ids[$field_collection_id]]);
}

A word of warning: depending on how many entities you are processing, this could take a long time.  As of Drupal 7.34, there is a memory leak in entity_save()--this means that each save will take slightly longer than the last. This is not a problem if you have only a few hundred fields, but when you get up into five and six digits, this script will take many hours. At that point, unless you have the time (and/or can run the script as a process in the background), you might want to consider investigating other options.

Okay, so the data is copied, the nodes are saved, and the elder gods have hit the snooze button.  Last thing you have to do is delete the old field.  We’re not going to do that, at least not yet. Instead, we’re going to delete the instances of the fields.  This will preserve the old field collection data, but remove the fields from the edit forms. This way, if something goes wrong, you don’t lose the data in the old fields and can try again if needed. You can go back at a later time, if you wish, after you have confirmed that everything is correct and delete the fields. Luckily, this is the easy part:

$instance = array(
  'field_name' => 'field_scald_producer',
  'entity_type' => node,
  'bundle' => article
);
field_delete_instance($instance);

And that’s it, crisis averted!  You no longer lose data and no longer have to worry about supernatural madness and death!  All you need to do now is run your script upstream with 'drush php-script' and watch the magic.

This sort of scripting can be daunting at first glance, but Drupal’s rich entity API can keep you from pulling out your hair or inadvertently causing an otherworldly alien intelligence from rising from the deep.  There are many more functions to take advantage of, and just about anything you can set with a click in the interface you can set in code, perfect for automation or locked down production environments.

Happy Drupaling !