You are here

public function DatabaseSchema_sqlsrv::changeField in Drupal driver for SQL Server and SQL Azure 7.2

Same name and namespace in other branches
  1. 7.3 sqlsrv/schema.inc \DatabaseSchema_sqlsrv::changeField()
  2. 7 sqlsrv/schema.inc \DatabaseSchema_sqlsrv::changeField()

Override DatabaseSchema::changeField().

@status complete

Overrides DatabaseSchema::changeField

File

sqlsrv/schema.inc, line 1100
Database schema code for Microsoft SQL Server database servers.

Class

DatabaseSchema_sqlsrv

Code

public function changeField($table, $field, $field_new, $spec, $new_keys = array()) {
  if (!$this
    ->fieldExists($table, $field)) {
    throw new DatabaseSchemaObjectDoesNotExistException(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 DatabaseSchemaObjectExistsException(t("Cannot rename field %table.%name to %name_new: target field already exists.", array(
      '%table' => $table,
      '%name' => $field,
      '%name_new' => $field_new,
    )));
  }

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

  /** @var DatabaseTransaction_sqlsrv $transaction */
  $transaction = $this->connection
    ->startTransaction(NULL, DatabaseTransactionSettings::GetDDLCompatibleDefaults());

  // 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 have 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 new_keys 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.
  $this
    ->queryColumnInformationInvalidate($table);
  $primary_key_fields = $this
    ->introspectPrimaryKeyFields($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);

  // Use already prefixed table name.
  $table_prefixed = $this->connection
    ->prefixTables('{' . $table . '}');

  // Start by renaming the current column.
  $this->connection
    ->query_direct('EXEC sp_rename :old, :new, :type', array(
    ':old' => "[{$table_prefixed}].{$field}",
    ':new' => $field . '_old',
    ':type' => 'COLUMN',
  ));

  // 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 (isset($spec['not null']) && $spec['not null'] == TRUE) {
    $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') {

    // Migrate the data over.
    // Explicitly cast the old value to the new value to avoid conversion errors.
    $collate_expression = isset($spec['sqlsrv_collation']) ? "COLLATE {$spec['sqlsrv_collation']}" : "";
    $sql = "UPDATE [{{$table}}] SET [{$field_new}] = CAST([{$field}_old] {$collate_expression} AS {$spec['sqlsrv_type_full']})";
    $this->connection
      ->query_direct($sql);
    $this
      ->queryColumnInformationInvalidate(TRUE);
  }

  // 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 (isset($spec['default'])) {
      $default_expression = $this
        ->defaultValueExpression($spec['sqlsrv_type'], $spec['default']);
      $sql = "UPDATE [{$table_prefixed}] SET [{$field_new}] = {$default_expression} WHERE [{$field_new}] IS NULL";
      $this->connection
        ->query_direct($sql, array(), array(
        'prefix_tables' => FALSE,
      ));
    }

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

  // Initialize new keys.
  if (!isset($new_keys)) {
    $new_keys = array(
      'unique keys' => array(),
      'primary keys' => array(),
    );
  }

  // 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($new_keys['primary keys']) || empty($new_keys['primary keys']))) {

    // The new primary key needs to have
    // the new column name.
    unset($primary_key_fields[$field]);
    $primary_key_fields[$field_new] = $field_new;
    $new_keys['primary key'] = $primary_key_fields;
  }

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

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

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

  // Refresh introspection for this table.
  $this
    ->queryColumnInformationInvalidate($table);

  // Commit.
  $transaction
    ->commit();
}