function UpsertTest::testCompositeKeyUpsert

Confirms that we can upsert records with composite keys successfully.

File

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

Class

UpsertTest

Tests the Upsert query builder.

Namespace

Code

public function testCompositeKeyUpsert() : void {
  $connection = Database::getConnection();
  $this->installSchema('database_test', [
    'test_composite_primary',
  ]);
  // Add some initial test data.
  $connection->insert('test_composite_primary')
    ->fields([
    'name',
    'age',
    'job',
  ])
    ->values([
    'name' => 'Tiffany',
    'age' => 31,
    'job' => 'Presenter',
  ])
    ->values([
    'name' => 'Meredith',
    'age' => 30,
    'job' => 'Speaker',
  ])
    ->execute();
  $num_records_before = $connection->query('SELECT COUNT(*) FROM {test_composite_primary}')
    ->fetchField();
  $upsert = $connection->upsert('test_composite_primary')
    ->key([
    'name',
    'age',
  ])
    ->fields([
    'name' => 'Kate',
    'age' => 25,
    'job' => 'Volunteer',
  ]);
  // Add a new row.
  $upsert->values([
    'name' => 'Karen',
    'age' => 35,
    'job' => 'Manager',
  ]);
  // Update an existing row.
  $upsert->values([
    'name' => 'Meredith',
    'age' => 30,
    // The initial job was 'Speaker'.
'job' => 'Organizer',
  ]);
  // Add a new row by reusing a name but with a different age. This won't
  // match the composite primary key constraint.
  $upsert->values([
    'name' => 'Meredith',
    'age' => 40,
    'job' => 'Supervisor',
  ]);
  $upsert->execute();
  $num_records_after = $connection->query('SELECT COUNT(*) FROM {test_composite_primary}')
    ->fetchField();
  $this->assertEquals($num_records_before + 3, $num_records_after, 'Rows were inserted and updated properly.');
  $person = $connection->query('SELECT * FROM {test_composite_primary} WHERE [job] = :job', [
    ':job' => 'Volunteer',
  ])
    ->fetch();
  $this->assertEquals('Volunteer', $person->job, 'Job set correctly.');
  $this->assertEquals(25, $person->age, 'Age set correctly.');
  $this->assertEquals('Kate', $person->name, 'Name set correctly.');
  $person = $connection->query('SELECT * FROM {test_composite_primary} WHERE [job] = :job', [
    ':job' => 'Manager',
  ])
    ->fetch();
  $this->assertEquals('Manager', $person->job, 'Job set correctly.');
  $this->assertEquals(35, $person->age, 'Age set correctly.');
  $this->assertEquals('Karen', $person->name, 'Name set correctly.');
  $person = $connection->query('SELECT * FROM {test_composite_primary} WHERE [job] = :job', [
    ':job' => 'Organizer',
  ])
    ->fetch();
  $this->assertEquals('Organizer', $person->job, 'Job set correctly.');
  $this->assertEquals(30, $person->age, 'Age set correctly.');
  $this->assertEquals('Meredith', $person->name, 'Name set correctly.');
  $person = $connection->query('SELECT * FROM {test_composite_primary} WHERE [job] = :job', [
    ':job' => 'Supervisor',
  ])
    ->fetch();
  $this->assertEquals('Supervisor', $person->job, 'Job set correctly.');
  $this->assertEquals(40, $person->age, 'Age set correctly.');
  $this->assertEquals('Meredith', $person->name, 'Name set correctly.');
}