Modifying an Existing Entity Column in hook_update_N

Question

This is a tricky problem but here is the jist of it. I have an entity that has a property called revision_id. Through hook_update_N I want to do 2 things.

• Change the db column name to node_id
• Convert the data in this db column from VIDs to NIDs

The code that takes care of this is...

function research_annotator_update_7100(&$sandbox) {

  // Change the db column.
  db_change_field(
    'research_annotator_annotation', 
    'revision_id', 
    'node_id', 
    array(
      'description' => 'The node the annotation belongs to.',
      'type' => 'int',
      'unsigned' => TRUE,
      'not null' => TRUE,
      'default' => 0,
    )
  );

  cache_clear_all();

  // Change revision ids into node ids.
  $arr_annotations = entity_load('research_annotation');

  foreach ($arr_annotations as $o_annotation) {
    $o_annotatedNode = node_load(NULL, $o_annotation->node_id);
    $o_annotation->node_id = $o_annotatedNode->nid;
    entity_save('research_annotation', $o_annotation);
  }
}

I also updated the entity property definition to represent the new node_id column...

research_annotator_entity_property_info() {

  $arr_propertyInfo = array();

  $arr_propertyInfo['research_annotation'] = array(
  'properties' => array(
    'node_id' => array(
      'label' => t('Revision'),
      'type' => 'integer',
      'description' => t('The revision id this annotation belongs to.'),
      'setter callback' => 'entity_property_verbatim_set',
      'required' => TRUE,
      'schema field' => 'node_id',
    ),
    ...snip

The problem is, when I run the update the first time, it gives me the following error...

Failed: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'base.revision_id' in 'field list' ...

In the database, the changes have been made to the column but the data has not been converted. If I clear all caches and run the update again then everything works as planned. I suspect that because the entity cache is not cleared after the db column change, the error appears.

If this is the case, how can I ensure the entity cache is cleared immediately after db_change_field is called in the update hook?

Answer 1

cache_clear_all() with no arguments clears cache_block and cache_page only; entity and schema cache both live in the cache table, so clearing that bin explicitly should do the trick:

cache_clear_all(NULL, 'cache');

Or if you want to clear the specific entries:

cache_clear_all('entity_info', 'cache', TRUE);
cache_clear_all('entity_property_info', 'cache', TRUE);
cache_clear_all('schema', 'cache', TRUE);

I'm assuming you've already updated your hook_schema() implementation to match the new scheme, or implemented hook_schema_alter() for the same if you're doing this on behalf of a different module.

Answer 2

For whatever reason,

cache_clear_all(NULL, 'cache'); 

does not work in update hooks.

Instead you will have to clear cache table in a much more direct manner.

db_truncate('cache')->execute();

Answer 3

Holy... don't use the entity API in update hooks. That won't lead to anything good. Just use a direct SQL query, this can be done nice and easy with UPDATE {whatevertable} SET node_id = (SELECT vid FROM {node} WHERE nid = node_id) boom!