public function Schema::addUniqueKey in Drupal driver for SQL Server and SQL Azure 8.2

Same name and namespace in other branches

1. 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php \Drupal\Driver\Database\sqlsrv\Schema::addUniqueKey()
2. 3.0.x drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php \Drupal\Driver\Database\sqlsrv\Schema::addUniqueKey()

Override DatabaseSchema::addUniqueKey().

Why are we not simply adding a UNIQUE index on the columns? Because on MySQL you can have a NULL value on one of these columns, but not on MSSQL. So we build the UNIQUE constraint on top of a HASH.

@status tested

Overrides Schema::addUniqueKey

2 calls to Schema::addUniqueKey()

Schema::createTable in drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php

{@Inheritdoc}

Schema::recreateTableKeys in drivers/lib/Drupal/Driver/Database/sqlsrv/Schema.php

Re-create keys associated to a table.

File

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

Definition of Drupal\Driver\Database\sqlsrv\Schema

Class

Schema

Namespace

Drupal\Driver\Database\sqlsrv

Code

public function addUniqueKey($table, $name, $fields) {
  if (!$this
    ->tableExists($table)) {
    throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot add unique key %name to table %table: table doesn't exist.", array(
      '%table' => $table,
      '%name' => $name,
    )));
  }
  if ($this
    ->uniqueKeyExists($table, $name)) {
    throw new DatabaseSchemaObjectExistsException(t("Cannot add unique key %name to table %table: unique key already exists.", array(
      '%table' => $table,
      '%name' => $name,
    )));
  }

  // The ANSI standard says that unique constraints should allow
  // multiple nulls. Check al columns in the constraint, if any of them is nullable,
  // make the constraint depend on the technical primary key.
  $info = $this
    ->getColumnIntrospection($table, $fields);
  $has_nullables = false;
  foreach ($info as $column) {

    // is_nullable has a 0 or 1 integer values.
    if ($column['is_nullable'] == true) {
      $has_nullables = true;
      break;
    }
  }

  // If there is a nullable column in the unique constraint
  // we need some workaround to emulate the ANSI behaviour.
  if ($has_nullables) {
    $this
      ->createTechnicalPrimaryColumn($table);

    // Then, build a expression based on the columns.
    $column_expression = [];
    foreach ($fields as $field) {
      if (is_array($field)) {
        $column_expression[] = "SUBSTRING(CAST([{$field[0]}] AS varbinary(max)), 1, [{$field[1]}])";
      }
      else {
        $column_expression[] = "CAST([{$field}] AS varbinary(max))";
      }
    }
    $column_expression = implode(' + ', $column_expression);

    // Build a computed column based on the expression that replaces NULL
    // values with the globally unique identifier generated previously.
    // This is (very) unlikely to result in a collision with any actual value
    // in the columns of the unique key.
    $this->connection
      ->query("ALTER TABLE {{$table}} ADD __unique_{$name} AS CAST(HashBytes('MD4', COALESCE({$column_expression}, CAST({$this->TECHNICAL_PK_COLUMN_NAME} AS varbinary(max)))) AS varbinary(16))");
    $this->connection
      ->query("CREATE UNIQUE INDEX {$name}_unique ON [{{$table}}] (__unique_{$name})");
  }
  else {
    $column_expression = [];
    foreach ($fields as $field) {
      if (is_array($field)) {
        $column_expression[] = $field[0];
      }
      else {
        $column_expression[] = $field;
      }
    }
    array_walk($column_expression, [
      $this->connection,
      'escapeField',
    ]);
    $column_expression = implode(',', $column_expression);
    $this->connection
      ->query("CREATE UNIQUE INDEX {$name}_unique ON [{{$table}}] ({$column_expression})");
  }
}