Merge.php 12.7 KB
Newer Older
1 2
<?php

3
namespace Drupal\Core\Database\Query;
4

5 6
use Drupal\Core\Database\Database;
use Drupal\Core\Database\Connection;
7
use Drupal\Core\Database\IntegrityConstraintViolationException;
Crell's avatar
Crell committed
8

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
/**
 * General class for an abstracted MERGE query operation.
 *
 * An ANSI SQL:2003 compatible database would run the following query:
 *
 * @code
 * MERGE INTO table_name_1 USING table_name_2 ON (condition)
 *   WHEN MATCHED THEN
 *   UPDATE SET column1 = value1 [, column2 = value2 ...]
 *   WHEN NOT MATCHED THEN
 *   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
 * @endcode
 *
 * Other databases (most notably MySQL, PostgreSQL and SQLite) will emulate
 * this statement by running a SELECT and then INSERT or UPDATE.
 *
 * By default, the two table names are identical and they are passed into the
 * the constructor. table_name_2 can be specified by the
 * MergeQuery::conditionTable() method. It can be either a string or a
 * subquery.
 *
 * The condition is built exactly like SelectQuery or UpdateQuery conditions,
 * the UPDATE query part is built similarly like an UpdateQuery and finally the
 * INSERT query part is built similarly like an InsertQuery. However, both
 * UpdateQuery and InsertQuery has a fields method so
 * MergeQuery::updateFields() and MergeQuery::insertFields() needs to be called
 * instead. MergeQuery::fields() can also be called which calls both of these
 * methods as the common case is to use the same column-value pairs for both
37
 * INSERT and UPDATE. However, this is not mandatory. Another convenient
38 39 40 41 42 43 44 45 46
 * wrapper is MergeQuery::key() which adds the same column-value pairs to the
 * condition and the INSERT query part.
 *
 * Several methods (key(), fields(), insertFields()) can be called to set a
 * key-value pair for the INSERT query part. Subsequent calls for the same
 * fields override the earlier ones. The same is true for UPDATE and key(),
 * fields() and updateFields().
 */
class Merge extends Query implements ConditionInterface {
47 48 49

  use QueryConditionTrait;

50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
  /**
   * Returned by execute() if an INSERT query has been executed.
   */
  const STATUS_INSERT = 1;

  /**
   * Returned by execute() if an UPDATE query has been executed.
   */
  const STATUS_UPDATE = 2;

  /**
   * The table to be used for INSERT and UPDATE.
   *
   * @var string
   */
  protected $table;

  /**
   * The table or subquery to be used for the condition.
69 70
   *
   * @var string
71 72 73 74 75 76 77 78
   */
  protected $conditionTable;

  /**
   * An array of fields on which to insert.
   *
   * @var array
   */
79
  protected $insertFields = [];
80 81 82 83 84 85 86 87

  /**
   * An array of fields which should be set to their database-defined defaults.
   *
   * Used on INSERT.
   *
   * @var array
   */
88
  protected $defaultFields = [];
89 90 91 92 93 94

  /**
   * An array of values to be inserted.
   *
   * @var string
   */
95
  protected $insertValues = [];
96 97 98 99 100 101

  /**
   * An array of fields that will be updated.
   *
   * @var array
   */
102
  protected $updateFields = [];
103 104 105 106 107 108 109 110 111 112 113 114 115 116

  /**
   * Array of fields to update to an expression in case of a duplicate record.
   *
   * This variable is a nested array in the following format:
   * @code
   * <some field> => array(
   *  'condition' => <condition to execute, as a string>,
   *  'arguments' => <array of arguments for condition, or NULL for none>,
   * );
   * @endcode
   *
   * @var array
   */
117
  protected $expressionFields = [];
118 119 120 121

  /**
   * Flag indicating whether an UPDATE is necessary.
   *
122
   * @var bool
123 124 125 126
   */
  protected $needsUpdate = FALSE;

  /**
127 128 129 130 131 132 133 134 135
   * Constructs a Merge object.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   A Connection object.
   * @param string $table
   *   Name of the table to associate with this query.
   * @param array $options
   *   Array of database options.
   */
136
  public function __construct(Connection $connection, $table, array $options = []) {
137 138 139 140
    $options['return'] = Database::RETURN_AFFECTED;
    parent::__construct($connection, $options);
    $this->table = $table;
    $this->conditionTable = $table;
141
    $this->condition = $this->connection->condition('AND');
142 143 144 145 146 147
  }

  /**
   * Sets the table or subquery to be used for the condition.
   *
   * @param $table
148
   *   The table name or the subquery to be used. Use a Select query object to
149 150
   *   pass in a subquery.
   *
151
   * @return $this
152 153 154 155 156 157 158 159 160 161 162 163 164 165
   *   The called object.
   */
  protected function conditionTable($table) {
    $this->conditionTable = $table;
    return $this;
  }

  /**
   * Adds a set of field->value pairs to be updated.
   *
   * @param $fields
   *   An associative array of fields to write into the database. The array keys
   *   are the field names and the values are the values to which to set them.
   *
166
   * @return $this
167 168 169 170 171 172 173 174 175 176 177 178
   *   The called object.
   */
  public function updateFields(array $fields) {
    $this->updateFields = $fields;
    $this->needsUpdate = TRUE;
    return $this;
  }

  /**
   * Specifies fields to be updated as an expression.
   *
   * Expression fields are cases such as counter = counter + 1. This method
179
   * takes precedence over MergeQuery::updateFields() and its wrappers,
180 181 182 183 184 185 186 187 188 189 190
   * MergeQuery::key() and MergeQuery::fields().
   *
   * @param $field
   *   The field to set.
   * @param $expression
   *   The field will be set to the value of this expression. This parameter
   *   may include named placeholders.
   * @param $arguments
   *   If specified, this is an array of key/value pairs for named placeholders
   *   corresponding to the expression.
   *
191
   * @return $this
192 193 194
   *   The called object.
   */
  public function expression($field, $expression, array $arguments = NULL) {
195
    $this->expressionFields[$field] = [
196 197
      'expression' => $expression,
      'arguments' => $arguments,
198
    ];
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
    $this->needsUpdate = TRUE;
    return $this;
  }

  /**
   * Adds a set of field->value pairs to be inserted.
   *
   * @param $fields
   *   An array of fields on which to insert. This array may be indexed or
   *   associative. If indexed, the array is taken to be the list of fields.
   *   If associative, the keys of the array are taken to be the fields and
   *   the values are taken to be corresponding values to insert. If a
   *   $values argument is provided, $fields must be indexed.
   * @param $values
   *   An array of fields to insert into the database. The values must be
   *   specified in the same order as the $fields array.
   *
216
   * @return $this
217 218
   *   The called object.
   */
219
  public function insertFields(array $fields, array $values = []) {
220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242
    if ($values) {
      $fields = array_combine($fields, $values);
    }
    $this->insertFields = $fields;
    return $this;
  }

  /**
   * Specifies fields for which the database-defaults should be used.
   *
   * If you want to force a given field to use the database-defined default,
   * not NULL or undefined, use this method to instruct the database to use
   * default values explicitly. In most cases this will not be necessary
   * unless you are inserting a row that is all default values, as you cannot
   * specify no values in an INSERT query.
   *
   * Specifying a field both in fields() and in useDefaults() is an error
   * and will not execute.
   *
   * @param $fields
   *   An array of values for which to use the default values
   *   specified in the table definition.
   *
243
   * @return $this
244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268
   *   The called object.
   */
  public function useDefaults(array $fields) {
    $this->defaultFields = $fields;
    return $this;
  }

  /**
   * Sets common field-value pairs in the INSERT and UPDATE query parts.
   *
   * This method should only be called once. It may be called either
   * with a single associative array or two indexed arrays. If called
   * with an associative array, the keys are taken to be the fields
   * and the values are taken to be the corresponding values to set.
   * If called with two arrays, the first array is taken as the fields
   * and the second array is taken as the corresponding values.
   *
   * @param $fields
   *   An array of fields to insert, or an associative array of fields and
   *   values. The keys of the array are taken to be the fields and the values
   *   are taken to be corresponding values to insert.
   * @param $values
   *   An array of values to set into the database. The values must be
   *   specified in the same order as the $fields array.
   *
269
   * @return $this
270 271
   *   The called object.
   */
272
  public function fields(array $fields, array $values = []) {
273 274 275 276 277 278 279 280 281 282 283 284
    if ($values) {
      $fields = array_combine($fields, $values);
    }
    foreach ($fields as $key => $value) {
      $this->insertFields[$key] = $value;
      $this->updateFields[$key] = $value;
    }
    $this->needsUpdate = TRUE;
    return $this;
  }

  /**
285
   * Sets the key fields to be used as conditions for this query.
286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302
   *
   * This method should only be called once. It may be called either
   * with a single associative array or two indexed arrays. If called
   * with an associative array, the keys are taken to be the fields
   * and the values are taken to be the corresponding values to set.
   * If called with two arrays, the first array is taken as the fields
   * and the second array is taken as the corresponding values.
   *
   * The fields are copied to the condition of the query and the INSERT part.
   * If no other method is called, the UPDATE will become a no-op.
   *
   * @param $fields
   *   An array of fields to set, or an associative array of fields and values.
   * @param $values
   *   An array of values to set into the database. The values must be
   *   specified in the same order as the $fields array.
   *
303
   * @return $this
304
   */
305
  public function keys(array $fields, array $values = []) {
306 307 308 309 310 311 312 313
    if ($values) {
      $fields = array_combine($fields, $values);
    }
    foreach ($fields as $key => $value) {
      $this->insertFields[$key] = $value;
      $this->condition($key, $value);
    }
    return $this;
314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333
  }

  /**
   * Sets a single key field to be used as condition for this query.
   *
   * Same as \Drupal\Core\Database\Query\Merge::keys() but offering a signature
   * that is more natural for the case of a single key.
   *
   * @param string $field
   *   The name of the field to set.
   * @param mixed $value
   *   The value to set into the database.
   *
   * @return $this
   *
   * @see \Drupal\Core\Database\Query\Merge::keys()
   */
  public function key($field, $value = NULL) {
    // @todo D9: Remove this backwards-compatibility shim.
    if (is_array($field)) {
334
      $this->keys($field, isset($value) ? $value : []);
335 336
    }
    else {
337
      $this->keys([$field => $value]);
338 339
    }
    return $this;
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
  }

  /**
   * Implements PHP magic __toString method to convert the query to a string.
   *
   * In the degenerate case, there is no string-able query as this operation
   * is potentially two queries.
   *
   * @return string
   *   The prepared query statement.
   */
  public function __toString() {
  }

  public function execute() {
355 356 357

    try {
      if (!count($this->condition)) {
358
        throw new InvalidMergeQueryException('Invalid merge query: no conditions');
359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
      }
      $select = $this->connection->select($this->conditionTable)
        ->condition($this->condition);
      $select->addExpression('1');
      if (!$select->execute()->fetchField()) {
        try {
          $insert = $this->connection->insert($this->table)->fields($this->insertFields);
          if ($this->defaultFields) {
            $insert->useDefaults($this->defaultFields);
          }
          $insert->execute();
          return self::STATUS_INSERT;
        }
        catch (IntegrityConstraintViolationException $e) {
          // The insert query failed, maybe it's because a racing insert query
          // beat us in inserting the same row. Retry the select query, if it
          // returns a row, ignore the error and continue with the update
          // query below.
          if (!$select->execute()->fetchField()) {
            throw $e;
          }
380 381
        }
      }
382 383 384 385 386 387 388 389
      if ($this->needsUpdate) {
        $update = $this->connection->update($this->table)
          ->fields($this->updateFields)
          ->condition($this->condition);
        if ($this->expressionFields) {
          foreach ($this->expressionFields as $field => $data) {
            $update->expression($field, $data['expression'], $data['arguments']);
          }
390
        }
391 392
        $update->execute();
        return self::STATUS_UPDATE;
393 394
      }
    }
395
    catch (\Exception $e) {
396 397 398 399 400 401 402
      // @todo 'throw_exception' option is deprecated. Remove in D10.
      // @see https://www.drupal.org/project/drupal/issues/3210310
      if (array_key_exists('throw_exception', $this->queryOptions)) {
        @trigger_error('Passing a \'throw_exception\' option to ' . __METHOD__ . ' is deprecated in drupal:9.2.0 and is removed in drupal:10.0.0. Always catch exceptions. See https://www.drupal.org/node/3201187', E_USER_DEPRECATED);
        if (!($this->queryOptions['throw_exception'])) {
          return NULL;
        }
403
      }
404
      throw $e;
405 406
    }
  }
407

408
}