function InvalidDataTest::testInsertDuplicateDataFromSelect

Tests inserting with invalid data from a select query.

File

core/tests/Drupal/KernelTests/Core/Database/InvalidDataTest.php, line 74

Class

InvalidDataTest

Tests handling of some invalid data.

Namespace

Code

public function testInsertDuplicateDataFromSelect() {
    // Insert multiple records in 'test_people' where one has bad data
    // (duplicate key). A 'Meredith' record has already been inserted
    // in ::setUp.
    $this->connection
        ->insert('test_people')
        ->fields([
        'name',
        'age',
        'job',
    ])
        ->values([
        'name' => 'Elvis',
        'age' => 63,
        'job' => 'Singer',
    ])
        ->values([
        // Duplicate value on unique field 'name' for later INSERT in 'test'
        // table.
'name' => 'John',
        'age' => 17,
        'job' => 'Consultant',
    ])
        ->values([
        'name' => 'Frank',
        'age' => 75,
        'job' => 'Bass',
    ])
        ->execute();
    try {
        // Define the subselect query. Add ORDER BY to ensure we have consistent
        // order in results. Will return:
        // 0 => [name] => Elvis, [age] => 63, [job] => Singer
        // 1 => [name] => Frank, [age] => 75, [job] => Bass
        // 2 => [name] => John, [age] => 17, [job] => Consultant
        // 3 => [name] => Meredith, [age] => 30, [job] => Speaker
        // Records 0 and 1 should pass, record 2 should lead to integrity
        // constraint violation.
        $query = $this->connection
            ->select('test_people', 'tp')
            ->fields('tp', [
            'name',
            'age',
            'job',
        ])
            ->orderBy('name');
        // Try inserting from the subselect.
        $this->connection
            ->insert('test')
            ->from($query)
            ->execute();
        $this->fail('Insert succeeded when it should not have.');
    } catch (IntegrityConstraintViolationException $e) {
        // Check if the second record was inserted.
        $name = $this->connection
            ->query('SELECT name FROM {test} WHERE age = :age', [
            ':age' => 75,
        ])
            ->fetchField();
        if ($name == 'Frank') {
            if (!Database::getConnection()->supportsTransactions()) {
                // This is an expected fail.
                // Database engines that don't support transactions can leave partial
                // inserts in place when an error occurs. This is the case for MySQL
                // when running on a MyISAM table.
                $this->pass("The whole transaction has not been rolled-back when a duplicate key insert occurs, this is expected because the database doesn't support transactions");
            }
            else {
                $this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
            }
        }
        else {
            $this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
        }
        // Ensure the values for records 2 and 3 were not inserted.
        $record = $this->connection
            ->select('test')
            ->fields('test', [
            'name',
            'age',
        ])
            ->condition('age', [
            17,
            30,
        ], 'IN')
            ->execute()
            ->fetchObject();
        $this->assertFalse($record, 'The rest of the insert aborted as expected.');
    }
}