InvalidDataTest.php 5.01 KB
Newer Older
1 2
<?php

3
namespace Drupal\KernelTests\Core\Database;
4

5
use Drupal\Core\Database\Database;
6
use Drupal\Core\Database\IntegrityConstraintViolationException;
7 8

/**
9 10 11
 * Tests handling of some invalid data.
 *
 * @group Database
12 13 14
 */
class InvalidDataTest extends DatabaseTestBase {
  /**
15
   * Tests aborting of traditional SQL database systems with invalid data.
16
   */
17
  public function testInsertDuplicateData() {
18 19 20
    // Try to insert multiple records where at least one has bad data.
    try {
      db_insert('test')
21 22
        ->fields(['name', 'age', 'job'])
        ->values([
23 24 25
          'name' => 'Elvis',
          'age' => 63,
          'job' => 'Singer',
26
        ])->values([
27 28 29
          'name' => 'John', // <-- Duplicate value on unique field.
          'age' => 17,
          'job' => 'Consultant',
30 31
        ])
        ->values([
32 33 34
          'name' => 'Frank',
          'age' => 75,
          'job' => 'Singer',
35
        ])
36
        ->execute();
37
      $this->fail('Insert succeeded when it should not have.');
38
    }
39
    catch (IntegrityConstraintViolationException $e) {
40
      // Check if the first record was inserted.
41
      $name = db_query('SELECT name FROM {test} WHERE age = :age', [':age' => 63])->fetchField();
42 43 44 45 46 47 48

      if ($name == 'Elvis') {
        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.
49
          $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");
50 51
        }
        else {
52
          $this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
53 54 55
        }
      }
      else {
56
        $this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
57 58 59 60
      }

      // Ensure the other values were not inserted.
      $record = db_select('test')
61 62
        ->fields('test', ['name', 'age'])
        ->condition('age', [17, 75], 'IN')
63 64
        ->execute()->fetchObject();

65
      $this->assertFalse($record, 'The rest of the insert aborted as expected.');
66 67 68
    }
  }

69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
  /**
   * Tests inserting with invalid data from a select query.
   */
  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.
    db_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 = db_select('test_people', 'tp')
        ->fields('tp', ['name', 'age', 'job'])
        ->orderBy('name');

      // Try inserting from the subselect.
      db_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 = db_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 = db_select('test')
        ->fields('test', ['name', 'age'])
        ->condition('age', [17, 30], 'IN')
        ->execute()->fetchObject();

      $this->assertFalse($record, 'The rest of the insert aborted as expected.');
    }
  }

146
}