You are here

class MergeQuery_sqlsrv in Drupal driver for SQL Server and SQL Azure 7.3

Same name and namespace in other branches
  1. 7 sqlsrv/query.inc \MergeQuery_sqlsrv
  2. 7.2 sqlsrv/query.inc \MergeQuery_sqlsrv

SQL Server-specific implementation of the MERGE operation.

Tested to be at least 50% faster than parent's implementation.

Hierarchy

Expanded class hierarchy of MergeQuery_sqlsrv

File

sqlsrv/query.inc, line 356

View source
class MergeQuery_sqlsrv extends MergeQuery {
  public function execute() {
    if (!count($this->condition)) {
      throw new InvalidMergeQueryException(t('Invalid merge query: no conditions'));
    }

    // Retrieve query options.
    $options = $this->queryOptions;

    // Keep a reference to the blobs.
    $blobs = array();

    // Fetch the list of blobs and sequences used on that table.
    $columnInformation = $this->connection
      ->schema()
      ->queryColumnInformation($this->table);

    // Find out if there is an identity field set in this insert.
    $this->setIdentity = !empty($columnInformation['identity']) && in_array($columnInformation['identity'], array_keys($this->insertFields));

    // Initialize placeholder count.
    $max_placeholder = 0;

    // Build the query.
    $stmt = $this->connection
      ->prepareQuery((string) $this);

    // Build the arguments: 1. condition.
    $arguments = $this->condition
      ->arguments();
    DatabaseUtils::BindArguments($stmt, $arguments);

    // 2. When matched part.
    $fields = $this->updateFields;
    DatabaseUtils::BindExpressions($stmt, $this->expressionFields, $fields);
    DatabaseUtils::BindValues($stmt, $fields, $blobs, ':db_merge_placeholder_', $columnInformation, $max_placeholder);

    // 3. When not matched part.
    DatabaseUtils::BindValues($stmt, $this->insertFields, $blobs, ':db_merge_placeholder_', $columnInformation, $max_placeholder);

    // 4. Run the query, this will return UPDATE or INSERT
    // MERGE queries should be atomic, yet you can run into concurrency
    // issues, so implement some retry logic. This is more elaborate and generic
    // in the 8.x-2.x version of the driver, just a quick workaround here.
    try {
      $this->connection
        ->query($stmt, [], $options);
    } catch (\PDOException $e) {
      if (in_array((string) $e
        ->getCode(), [
        '23000',
      ])) {

        // Try again...
        $this->connection
          ->query($stmt, [], $options);
      }
      else {

        // Rethrow.
        throw $e;
      }
    }
    $result = NULL;
    foreach ($stmt as $value) {
      $result = $value->{'$action'};
    }
    switch ($result) {
      case 'UPDATE':
        return static::STATUS_UPDATE;
      case 'INSERT':
        return static::STATUS_INSERT;
    }
  }
  public function __toString() {

    // Initialize placeholder count.
    $max_placeholder = 0;
    $max_placeholder_conditions = 0;
    $query = array();

    // Enable direct insertion to identity columns if necessary.
    if (!empty($this->setIdentity)) {
      $query[] = 'SET IDENTITY_INSERT {' . $this->table . '} ON;';
    }
    $query[] = 'MERGE INTO {' . $this->table . '} _target';

    // 1. Condition part.
    $this->condition
      ->compile($this->connection, $this);
    $key_conditions = array();
    $template_item = array();
    $conditions = $this
      ->conditions();
    unset($conditions['#conjunction']);
    foreach ($conditions as $condition) {
      $key_conditions[] = '_target.' . $this->connection
        ->escapeField($condition['field']) . ' = ' . '_source.' . $this->connection
        ->escapeField($condition['field']);
      $template_item[] = ':db_condition_placeholder_' . $max_placeholder_conditions++ . ' AS ' . $this->connection
        ->escapeField($condition['field']);
    }
    $query[] = 'USING (SELECT ' . implode(', ', $template_item) . ') _source ' . PHP_EOL . 'ON ' . implode(' AND ', $key_conditions);

    // 2. "When matched" part.
    // Expressions take priority over literal fields, so we process those first
    // and remove any literal fields that conflict.
    $fields = $this->updateFields;
    $update_fields = array();
    foreach ($this->expressionFields as $field => $data) {
      $update_fields[] = $field . '=' . $data['expression'];
      unset($fields[$field]);
    }
    foreach ($fields as $field => $value) {
      $update_fields[] = $this->connection
        ->quoteIdentifier($field) . '=:db_merge_placeholder_' . $max_placeholder++;
    }
    if (!empty($update_fields)) {
      $query[] = 'WHEN MATCHED THEN UPDATE SET ' . implode(', ', $update_fields);
    }

    // 3. "When not matched" part.
    if ($this->insertFields) {

      // Build the list of placeholders.
      $placeholders = array();
      for ($i = 0; $i < count($this->insertFields); ++$i) {
        $placeholders[] = ':db_merge_placeholder_' . $max_placeholder++;
      }
      $query[] = 'WHEN NOT MATCHED THEN INSERT (' . implode(', ', $this->connection
        ->quoteIdentifiers(array_keys($this->insertFields))) . ') VALUES (' . implode(', ', $placeholders) . ')';
    }
    else {
      $query[] = 'WHEN NOT MATCHED THEN INSERT DEFAULT VALUES';
    }

    // Return information about the query.
    $query[] = 'OUTPUT $action;';
    return implode(PHP_EOL, $query);
  }

}

Members

Namesort descending Modifiers Type Description Overrides
MergeQuery::$conditionTable protected property The table or subquery to be used for the condition.
MergeQuery::$defaultFields protected property An array of fields which should be set to their database-defined defaults.
MergeQuery::$expressionFields protected property Array of fields to update to an expression in case of a duplicate record.
MergeQuery::$insertFields protected property An array of fields on which to insert.
MergeQuery::$insertValues protected property An array of values to be inserted.
MergeQuery::$needsUpdate protected property Flag indicating whether an UPDATE is necessary.
MergeQuery::$table protected property The table to be used for INSERT and UPDATE.
MergeQuery::$updateFields protected property An array of fields that will be updated.
MergeQuery::arguments public function Implements QueryConditionInterface::arguments(). Overrides QueryConditionInterface::arguments
MergeQuery::compile public function Implements QueryConditionInterface::compile(). Overrides QueryConditionInterface::compile
MergeQuery::compiled public function Implements QueryConditionInterface::compiled(). Overrides QueryConditionInterface::compiled
MergeQuery::condition public function Implements QueryConditionInterface::condition(). Overrides QueryConditionInterface::condition
MergeQuery::conditions public function Implements QueryConditionInterface::conditions(). Overrides QueryConditionInterface::conditions
MergeQuery::conditionTable protected function Sets the table or subquery to be used for the condition.
MergeQuery::exists public function Implements QueryConditionInterface::exists(). Overrides QueryConditionInterface::exists
MergeQuery::expression public function Specifies fields to be updated as an expression.
MergeQuery::fields public function Sets common field-value pairs in the INSERT and UPDATE query parts.
MergeQuery::insertFields public function Adds a set of field->value pairs to be inserted.
MergeQuery::isNotNull public function Implements QueryConditionInterface::isNotNull(). Overrides QueryConditionInterface::isNotNull
MergeQuery::isNull public function Implements QueryConditionInterface::isNull(). Overrides QueryConditionInterface::isNull
MergeQuery::key public function Sets the key field(s) to be used as conditions for this query.
MergeQuery::notExists public function Implements QueryConditionInterface::notExists(). Overrides QueryConditionInterface::notExists
MergeQuery::STATUS_INSERT constant Returned by execute() if an INSERT query has been executed.
MergeQuery::STATUS_UPDATE constant Returned by execute() if an UPDATE query has been executed.
MergeQuery::updateFields public function Adds a set of field->value pairs to be updated.
MergeQuery::useDefaults public function Specifies fields for which the database-defaults should be used.
MergeQuery::where public function Implements QueryConditionInterface::where(). Overrides QueryConditionInterface::where
MergeQuery::__construct public function Constructs a MergeQuery object. Overrides Query::__construct
MergeQuery_sqlsrv::execute public function Runs the query against the database. Overrides MergeQuery::execute
MergeQuery_sqlsrv::__toString public function Implements PHP magic __toString method to convert the query to a string. Overrides MergeQuery::__toString
Query::$comments protected property An array of comments that can be prepended to a query.
Query::$connection protected property The connection object on which to run this query.
Query::$connectionKey protected property The key of the connection object.
Query::$connectionTarget protected property The target of the connection object.
Query::$nextPlaceholder protected property The placeholder counter.
Query::$queryOptions protected property The query options to pass on to the connection object.
Query::$uniqueIdentifier protected property A unique identifier for this query object.
Query::comment public function Adds a comment to the query.
Query::getComments public function Returns a reference to the comments array for the query.
Query::nextPlaceholder public function Gets the next placeholder value for this query object. Overrides QueryPlaceholderInterface::nextPlaceholder
Query::uniqueIdentifier public function Returns a unique identifier for this object. Overrides QueryPlaceholderInterface::uniqueIdentifier
Query::__clone public function Implements the magic __clone function. 1
Query::__sleep public function Implements the magic __sleep function to disconnect from the database.
Query::__wakeup public function Implements the magic __wakeup function to reconnect to the database.