Designed to
delight
we design & develop
exceptional Drupal websites
The Beatles
 
Designed to
inform
we design & develop
exceptional Drupal websites
 
Designed to
unite
we design & develop
exceptional Drupal websites
SGI UK
 
Designed to
inform
we design & develop
exceptional Drupal websites
 
Designed to
delight
we design & develop
exceptional Drupal websites
 
Designed to
entice
we design & develop
exceptional Drupal websites
 
Designed to
engage
we design & develop
exceptional Drupal websites
 
Designed to
convert
we design & develop
exceptional Drupal websites
 
Designed to
motivate
we design & develop
exceptional Drupal websites
 
Designed to
delight
we design & develop
exceptional Drupal websites
 
Designed to
influence
we design & develop
exceptional Drupal websites
 
Designed to
delight
we design & develop
exceptional Drupal websites
 
Designed to
inform
we design & develop
exceptional Drupal websites
 
Designed to
convert
we design & develop
exceptional Drupal websites
Clovis Canopies
 
Designed to
network
we design & develop
exceptional Drupal websites
 
Designed to
motivate
we design & develop
exceptional Drupal websites
 

Missing database fields and the importance of updating Drupal's schema correctly

Today I was presented with a problem. Data wasn't being saved to one particular field in a Drupal custom module's table. Looking into the client's custom module code everything looked OK. Looking at MySQL the table matched OK too.

<?php
  $record
= array(
   
"uid" => $user->uid,
   
"nid" => $node->nid,
   
"fid" => $file['fid'],
   
"created" => time(),
   
"type" => $type,
  );
 
drupal_write_record("example_table_name", $record);
?>

So I dug into the drupal_write_record function and added some debugging. The returned schema was missing this field. I'm guessing that the original developers of this module had changed the database directly by adding in the extra "type" field. Unfortunately the mistake hadn't been spotted until now and a few months of reports are missing the information that should have been saved in this field.

The drupal_write_record function is great but ONLY if you use schema API correctly.

Here's a better way to add a field to a database which conforms to schema API and avoids this type of problem: -

  • Add the new field definition to the existing hook_schema function found in the module's install file
  • Add the new field to a hook_update_N function, again in the modules' install file
<?php
 
function example_schema() {
   
$schema['example_table_name'] = array(
     
'fields' => array(
       
'repid'      => array('type' => 'int', 'unsigned' => TRUE'not null' => TRUE, 'default' => 0),
       
'nid'        => array('type' => 'int', 'unsigned' => TRUE'not null' => TRUE, 'default' => 0),
       
'uid'        => array('type' => 'int', 'unsigned' => TRUE'not null' => TRUE, 'default' => 0),
       
'fid'        => array('type' => 'int', 'unsigned' => TRUE'not null' => TRUE, 'default' => 0),
       
'created'    => array('type' => 'int', 'unsigned' => TRUE'not null' => TRUE, 'default' => 0),
       
// THIS IS THE NEW LINE THAT WAS MISSED
       
'type'       => array('type' => 'text'),
      ),
   
'primary key' => array('repid'),
    );
    return
$schema;
  }
 
// Add the new field
 
function example_update_7001() {
   
$new_field = array(
     
'type' => 'text',
    );
   
db_add_field('example_table_name', 'type', $new_field);
  }
?>

Sometimes we need to work on the database directly. If this is the case you can always check if the field exists first. Just use the db_field_exists function.

Of course everything worked perfectly fine when we added the missing schema api code and ran Drupal's database update script.