function UpsertTest::testUpsertWithUpsertUpdateExpression

Confirms that we can upsert using UpsertUpdateExpression.

File

core/tests/Drupal/KernelTests/Core/Database/UpsertTest.php, line 69

Class

UpsertTest

Tests the Upsert query builder.

Namespace

Code

public function testUpsertWithUpsertUpdateExpression() : void {
  $this->assertSame('1', $this->connection
    ->query('SELECT COUNT(*) FROM {test_people}')
    ->fetchField());
  $upsert = $this->connection
    ->upsert('test_people')
    ->key('job')
    ->fields([
    'job',
    // Use two placeholder arguments to cover the argument binding done by
    // each driver.
'age' => new UpsertUpdateExpression('{test_people}.[age] + :one + :two', [
      ':one' => 2,
      ':two' => 3,
    ]),
    'name',
  ]);
  // Insert a new row.
  $upsert->values([
    'job' => 'Presenter',
    'age' => 31,
    'name' => 'Tiffany',
  ]);
  // Update an existing row.
  $upsert->values([
    'job' => 'Speaker',
    // The initial age was 30; the new age should be 35 given the
    // UpsertUpdateExpression, and the value of 32 passed in discarded.
'age' => 32,
    'name' => 'Meredith',
  ]);
  $upsert->execute();
  $this->assertSame('2', $this->connection
    ->query('SELECT COUNT(*) FROM {test_people}')
    ->fetchField());
  $person = $this->connection
    ->query('SELECT * FROM {test_people} WHERE [job] = :job', [
    ':job' => 'Presenter',
  ])
    ->fetch();
  $this->assertSame('Presenter', $person->job);
  $this->assertSame('31', $person->age);
  $this->assertSame('Tiffany', $person->name);
  $person = $this->connection
    ->query('SELECT * FROM {test_people} WHERE [job] = :job', [
    ':job' => 'Speaker',
  ])
    ->fetch();
  $this->assertSame('Speaker', $person->job);
  $this->assertSame('35', $person->age, 'Age should have been updated to 35.');
  $this->assertSame('Meredith', $person->name);
  // An associative $fields array cannot mix insert values with update
  // expressions, because the values cannot be told apart from column names.
  try {
    $this->connection
      ->upsert('test_people')
      ->key('job')
      ->fields([
      'job' => 'Speaker',
      'age' => new UpsertUpdateExpression('{test_people}.[age] + 1'),
    ]);
    $this->fail('Expected exception when mixing insert values with an update expression.');
  } catch (\InvalidArgumentException $e) {
    $this->assertStringContainsString('The entry for "job"', $e->getMessage());
  }
  // An update expression needs a column name key.
  try {
    $this->connection
      ->upsert('test_people')
      ->key('job')
      ->fields([
      'job',
      new UpsertUpdateExpression('{test_people}.[age] + 1'),
    ]);
    $this->fail('Expected exception for an update expression without a column name key.');
  } catch (\InvalidArgumentException $e) {
    $this->assertStringContainsString('must be keyed by its column name', $e->getMessage());
  }
}