You are here

Home » API reference » Drupal driver for SQL Server and SQL Azure 3.0.x » Schema.php » Schema

public function Schema::changeField in Drupal driver for SQL Server and SQL Azure 3.0.x

Same name and namespace in other branches
  1. 8.2 drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php \Drupal\Driver\Database\sqlsrv\Schema::changeField()
  2. 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php \Drupal\Driver\Database\sqlsrv\Schema::changeField()

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 Schema::dropPrimaryKey(), Schema::dropUniqueKey(), or Schema::dropIndex() before calling ::changeField(). To recreate the keys and indices, pass the key definitions as the optional $keys_new argument directly to ::changeField().

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:

$injected_database
  ->schema()
  ->dropPrimaryKey('foo');
$injected_database
  ->schema()
  ->changeField('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 Schema::addPrimaryKey, Schema::addUniqueKey(), or Schema::addIndex() 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 Schema::addPrimaryKey, Schema::addUniqueKey(), or Schema::addIndex() 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

drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php, line 621

Class

Schema

Namespace

Drupal\Driver\Database\sqlsrv

Code

public function changeField($table, $field, $field_new, $spec, $keys_new = []) {
  if (!$this
    ->fieldExists($table, $field)) {
    throw new SchemaObjectDoesNotExistException(t("Cannot change the definition of field %table.%name: field doesn't exist.", [
      '%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.", [
      '%table' => $table,
      '%name' => $field,
      '%name_new' => $field_new,
    ]));
  }
  if (isset($keys_new['primary key']) && in_array($field_new, $keys_new['primary key'], TRUE)) {
    $this
      ->ensureNotNullPrimaryKey($keys_new['primary key'], [
      $field_new => $spec,
    ]);
  }

  // Check if we need to drop field comments.
  $drop_field_comment = FALSE;
  if ($this
    ->getComment($table, $field) !== FALSE) {
    $drop_field_comment = TRUE;
  }

  // SQL Server supports transactional DDL, so we can just start a transaction
  // here and pray for the best.

  /** @var Transaction $transaction */
  $transaction = $this->connection
    ->startTransaction();

  // Prepare the specifications.
  $spec = $this
    ->processField($spec);

  /*
   * 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 ohave to drop all affected keys and indexes with
   * db_drop_{primary_key,unique_key,index}() before calling
   * db_change_field().
   *
   * @see https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_change_field/7
   *
   * What we are going to do in the SQL Server Driver is a best-effort try to
   * preserve original keys if they do not conflict with the keys_new
   * parameter, and if the callee has done it's job (droping constraints/keys)
   * then they will of course not be recreated.
   *
   * Introspect the schema and save the current primary key if the column
   * we are modifying is part of it. Make sure the schema is FRESH.
   */
  $primary_key_fields = $this
    ->findPrimaryKeyColumns($table);
  if (in_array($field, $primary_key_fields)) {

    // Let's drop the PK.
    $this
      ->cleanUpPrimaryKey($table);
  }

  // If there is a generated unique key for this field, we will need to
  // add it back in when we are done.
  $unique_key = $this
    ->uniqueKeyExists($table, $field);

  // Drop the related objects.
  $this
    ->dropFieldRelatedObjects($table, $field);
  if ($drop_field_comment) {
    $this->connection
      ->queryDirect($this
      ->deleteCommentSql($table, $field));
  }
  $prefixInfo = $this
    ->getPrefixInfo($table, TRUE);

  // Start by renaming the current column.
  $this->connection
    ->queryDirect('EXEC sp_rename :old, :new, :type', [
    ':old' => $prefixInfo['table'] . '.' . $field,
    ':new' => $field . '_old',
    ':type' => 'COLUMN',
  ]);
  $this
    ->resetColumnInformation($table);

  // If the new column does not allow nulls, we need to
  // create it first as nullable, then either migrate
  // data from previous column or populate default values.
  $fixnull = FALSE;
  if (!empty($spec['not null'])) {
    $fixnull = TRUE;
    $spec['not null'] = FALSE;
  }

  // Create a new field.
  $this
    ->addField($table, $field_new, $spec);

  // Don't need to do this if there is no data
  // Cannot do this it column is serial.
  if ($spec['type'] != 'serial') {
    $new_data_type = $this
      ->createDataType($table, $field_new, $spec);

    // Migrate the data over.
    // Explicitly cast the old value to the new value to avoid conversion
    // errors.
    $sql = "UPDATE {{$table}} SET {$field_new}=CAST({$field}_old AS {$new_data_type})";
    $this->connection
      ->queryDirect($sql);
    $this
      ->resetColumnInformation($table);
  }

  // Switch to NOT NULL now.
  if ($fixnull === TRUE) {

    // There is no warranty that the old data did not have NULL values, we
    // need to populate nulls with the default value because this won't be
    // done by MSSQL by default.
    if (!empty($spec['default'])) {
      $default_expression = $this
        ->defaultValueExpression($spec['sqlsrv_type'], $spec['default']);
      $sql = "UPDATE {{$table}} SET {$field_new} = {$default_expression} WHERE {$field_new} IS NULL";
      $this->connection
        ->queryDirect($sql);
      $this
        ->resetColumnInformation($table);
    }

    // Now it's time to make this non-nullable.
    $spec['not null'] = TRUE;
    $field_sql = $this
      ->createFieldSql($table, $field_new, $spec, TRUE);
    $sql = "ALTER TABLE {{$table}} ALTER COLUMN {$field_sql}";
    $this->connection
      ->queryDirect($sql);
    $this
      ->resetColumnInformation($table);
  }

  // Recreate the primary key if no new primary key has been sent along with
  // the change field.
  if (in_array($field, $primary_key_fields) && (!isset($keys_new['primary keys']) || empty($keys_new['primary keys']))) {

    // The new primary key needs to have the new column name, and be in the
    // same order.
    if ($field !== $field_new) {
      $primary_key_fields[array_search($field, $primary_key_fields)] = $field_new;
    }
    $keys_new['primary key'] = $primary_key_fields;
  }

  // Recreate the unique constraint if it existed.
  if ($unique_key && (!isset($keys_new['unique keys']) || !in_array($field_new, $keys_new['unique keys']))) {
    $keys_new['unique keys'][$field] = [
      $field_new,
    ];
  }

  // Drop the old field.
  $this
    ->dropField($table, $field . '_old');

  // Add the new keys.
  $this
    ->recreateTableKeys($table, $keys_new);
}