db_change_field

• Drupal 6
• Drupal 7

Versions

6

db_change_field(&$ret, $table, $field, $field_new, $spec, $new_keys = array())

7

db_change_field($table, $field, $field_new, $spec, $keys_new = array())

Change a field definition.

IMPORTANT NOTE: To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field.

That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). To recreate the keys and indices, pass the key definitions as the optional $new_keys argument directly to db_change_field().

For example, suppose you have:

<?php

$schema['foo'] = array(
'fields' => array(
'bar' => array('type' => 'int', 'not null' => TRUE)
),
'primary key' => array('bar')
);

?>

and you want to change foo.bar to be type serial, leaving it as the primary key. The correct sequence is:

<?php

db_drop_primary_key($ret, 'foo');
db_change_field($ret, 'foo', 'bar', 'bar',
array('type' => 'serial', 'not null' => TRUE),
array('primary key' => array('bar')));

?>

The reasons for this are due to the different database engines:

On PostgreSQL, changing a field definition involves adding a new field and dropping an old one which* causes any indices, primary keys and sequences (from serial-type fields) that use the changed field to be dropped.

On MySQL, all type 'serial' fields must be part of at least one key or index as soon as they are created. You cannot use db_add_{primary_key,unique_key,index}() for this purpose because the ALTER TABLE command will fail to add the column without a key or index specification. The solution is to use the optional $new_keys argument to create the key or index at the same time as field.

You could use db_add_{primary_key,unique_key,index}() in all cases unless you are converting a field to be type serial. You can use the $new_keys argument in all cases.

Parameters

$ret Array to which query results will be added.

$table Name of the table.

$field Name of the field to change.

$field_new New name for the field (set to the same as $field if you don't want to change the name).

$spec The field specification for the new field.

$new_keys Optional keys and indexes specification to be created on the table along with changing the field. The format is the same as a table specification but without the 'fields' element.

Related topics

Schema API

A Drupal schema definition is an array structure representing one or more tables and their related keys and indexes. A schema is defined by hook_schema(), which usually lives in a modulename.install file.

Code

includes/database.pgsql.inc, line 907

<?php
function db_change_field(&$ret, $table, $field, $field_new, $spec, $new_keys = array()) {
  $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME "'. $field .'" TO "'. $field .'_old"');
  $not_null = isset($spec['not null']) ? $spec['not null'] : FALSE;
  unset($spec['not null']);

  if (!array_key_exists('size', $spec)) {
    $spec['size'] = 'normal';
  }
  db_add_field($ret, $table, "$field_new", $spec);

  // We need to type cast the new column to best transfer the data
  // db_type_map will return possiblities that are not 'cast-able'
  // such as serial - they must be made 'int' instead.
  $map =  db_type_map();
  $typecast = $map[$spec['type'] .':'. $spec['size']];
  if (in_array($typecast, array('serial', 'bigserial', 'numeric'))) {
    $typecast = 'int';
  }
  $ret[] = update_sql('UPDATE {'. $table .'} SET '. $field_new .' = CAST('. $field .'_old AS '. $typecast .')');

  if ($not_null) {
    $ret[] = update_sql("ALTER TABLE {". $table ."} ALTER $field_new SET NOT NULL");
  }

  db_drop_field($ret, $table, $field .'_old');

  if (isset($new_keys)) {
    _db_create_keys($ret, $table, $new_keys);
  }
}
?>

Login or register to post comments