public function Schema::changeField

public Schema::changeField($table, $field, $field_new, $spec, $new_keys = 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 $keys_new argument directly to db_change_field().

For example, suppose you have:

$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:

db_drop_primary_key('foo');
db_change_field('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 $keys_new 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 $keys_new argument in all cases.

Parameters

$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.

$keys_new: (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.

Throws

\Drupal\Core\Database\SchemaObjectDoesNotExistException If the specified table or source field doesn't exist.

\Drupal\Core\Database\SchemaObjectExistsException If the specified destination field already exists.

Overrides Schema::changeField

File

core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php, line 684

Class

Schema

PostgreSQL implementation of \Drupal\Core\Database\Schema.

Namespace

Drupal\Core\Database\Driver\pgsql

Code

public function changeField($table, $field, $field_new, $spec, $new_keys = array()) {
  if (!$this->fieldExists($table, $field)) {
    throw new SchemaObjectDoesNotExistException(t("Cannot change the definition of field @table.@name: field doesn't exist.", array('@table' => $table, '@name' => $field)));
  }
  if (($field != $field_new) && $this->fieldExists($table, $field_new)) {
    throw new SchemaObjectExistsException(t("Cannot rename field @table.@name to @name_new: target field already exists.", array('@table' => $table, '@name' => $field, '@name_new' => $field_new)));
  }

  $spec = $this->processField($spec);

  // Type 'serial' is known to PostgreSQL, but only during table creation,
  // not when altering. Because of that, we create it here as an 'int'. After
  // we create it we manually re-apply the sequence.
  if (in_array($spec['pgsql_type'], array('serial', 'bigserial'))) {
    $field_def = 'int';
  }
  else {
    $field_def = $spec['pgsql_type'];
  }

  if (in_array($spec['pgsql_type'], array('varchar', 'character', 'text')) && isset($spec['length'])) {
    $field_def .= '(' . $spec['length'] . ')';
  }
  elseif (isset($spec['precision']) && isset($spec['scale'])) {
    $field_def .= '(' . $spec['precision'] . ', ' . $spec['scale'] . ')';
  }

  // Remove old check constraints.
  $field_info = $this->queryFieldInformation($table, $field);

  foreach ($field_info as $check) {
    $this->connection->query('ALTER TABLE {' . $table . '} DROP CONSTRAINT "' . $check . '"');
  }

  // Remove old default.
  $this->fieldSetNoDefault($table, $field);

  // Convert field type.
  // Usually, we do this via a simple typecast 'USING fieldname::type'. But
  // the typecast does not work for conversions to bytea.
  // @see http://www.postgresql.org/docs/current/static/datatype-binary.html
  $table_information = $this->queryTableInformation($table);
  $is_bytea = !empty($table_information->blob_fields[$field]);
  if ($spec['pgsql_type'] != 'bytea') {
    if ($is_bytea) {
      $this->connection->query('ALTER TABLE {' . $table . '} ALTER "' . $field . '" TYPE ' . $field_def . ' USING convert_from("' . $field . '"' . ", 'UTF8')");
    }
    else {
      $this->connection->query('ALTER TABLE {' . $table . '} ALTER "' . $field . '" TYPE ' . $field_def . ' USING "' . $field . '"::' . $field_def);
    }
  }
  else {
    // Do not attempt to convert a field that is bytea already.
    if (!$is_bytea) {
      // Convert to a bytea type by using the SQL replace() function to
      // convert any single backslashes in the field content to double
      // backslashes ('\' to '\\').
      $this->connection->query('ALTER TABLE {' . $table . '} ALTER "' . $field . '" TYPE ' . $field_def . ' USING decode(replace("' . $field . '"' . ", E'\\\\', E'\\\\\\\\'), 'escape');");
    }
  }

  if (isset($spec['not null'])) {
    if ($spec['not null']) {
      $nullaction = 'SET NOT NULL';
    }
    else {
      $nullaction = 'DROP NOT NULL';
    }
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER "' . $field . '" ' . $nullaction);
  }

  if (in_array($spec['pgsql_type'], array('serial', 'bigserial'))) {
    // Type "serial" is known to PostgreSQL, but *only* during table creation,
    // not when altering. Because of that, the sequence needs to be created
    // and initialized by hand.
    $seq = "{" . $table . "}_" . $field_new . "_seq";
    $this->connection->query("CREATE SEQUENCE " . $seq);
    // Set sequence to maximal field value to not conflict with existing
    // entries.
    $this->connection->query("SELECT setval('" . $seq . "', MAX(\"" . $field . '")) FROM {' . $table . "}");
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER ' . $field . ' SET DEFAULT nextval(' . $this->connection->quote($seq) . ')');
  }

  // Rename the column if necessary.
  if ($field != $field_new) {
    $this->connection->query('ALTER TABLE {' . $table . '} RENAME "' . $field . '" TO "' . $field_new . '"');
  }

  // Add unsigned check if necessary.
  if (!empty($spec['unsigned'])) {
    $this->connection->query('ALTER TABLE {' . $table . '} ADD CHECK ("' . $field_new . '" >= 0)');
  }

  // Add default if necessary.
  if (isset($spec['default'])) {
    $this->fieldSetDefault($table, $field_new, $spec['default']);
  }

  // Change description if necessary.
  if (!empty($spec['description'])) {
    $this->connection->query('COMMENT ON COLUMN {' . $table . '}."' . $field_new . '" IS ' . $this->prepareComment($spec['description']));
  }

  if (isset($new_keys)) {
    $this->_createKeys($table, $new_keys);
  }
  $this->resetTableInformation($table);
}