MergeTest.php 9.95 KB
Newer Older
1 2
<?php

3
namespace Drupal\KernelTests\Core\Database;
4 5 6 7 8

use Drupal\Core\Database\Query\Merge;
use Drupal\Core\Database\Query\InvalidMergeQueryException;

/**
9
 * Tests the MERGE query builder.
10 11
 *
 * @group Database
12 13 14 15
 */
class MergeTest extends DatabaseTestBase {

  /**
16
   * Confirms that we can merge-insert a record successfully.
17
   */
18
  public function testMergeInsert() {
19
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
20

21
    $result = $this->connection->merge('test_people')
22
      ->key('job', 'Presenter')
23
      ->fields([
24 25
        'age' => 31,
        'name' => 'Tiffany',
26
      ])
27 28
      ->execute();

29
    $this->assertEqual($result, Merge::STATUS_INSERT, 'Insert status returned.');
30

31
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
32
    $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
33

34
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Presenter'])->fetch();
35 36 37
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 31, 'Age set correctly.');
    $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
38 39 40
  }

  /**
41
   * Confirms that we can merge-update a record successfully.
42
   */
43
  public function testMergeUpdate() {
44
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
45

46
    $result = $this->connection->merge('test_people')
47
      ->key('job', 'Speaker')
48
      ->fields([
49 50
        'age' => 31,
        'name' => 'Tiffany',
51
      ])
52 53
      ->execute();

54
    $this->assertEqual($result, Merge::STATUS_UPDATE, 'Update status returned.');
55

56
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
57
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
58

59
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
60 61 62
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 31, 'Age set correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
63 64 65
  }

  /**
66 67 68 69
   * Confirms that we can merge-update a record successfully.
   *
   * This test varies from the previous test because it manually defines which
   * fields are inserted, and which fields are updated.
70
   */
71
  public function testMergeUpdateExcept() {
72
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
73

74
    $this->connection->merge('test_people')
75
      ->key('job', 'Speaker')
76 77
      ->insertFields(['age' => 31])
      ->updateFields(['name' => 'Tiffany'])
78 79
      ->execute();

80
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
81
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
82

83
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
84 85 86
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
87 88 89
  }

  /**
90
   * Confirms that we can merge-update a record, with alternate replacement.
91
   */
92
  public function testMergeUpdateExplicit() {
93
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
94

95
    $this->connection->merge('test_people')
96
      ->key('job', 'Speaker')
97
      ->insertFields([
98 99
        'age' => 31,
        'name' => 'Tiffany',
100 101
      ])
      ->updateFields([
102
        'name' => 'Joe',
103
      ])
104 105
      ->execute();

106
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
107
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
108

109
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
110 111 112
    $this->assertEqual($person->name, 'Joe', 'Name set correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
113 114 115
  }

  /**
116
   * Confirms that we can merge-update a record successfully, with expressions.
117
   */
118
  public function testMergeUpdateExpression() {
119
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
120

121
    $age_before = $this->connection->query('SELECT [age] FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetchField();
122 123 124 125 126 127

    // This is a very contrived example, as I have no idea why you'd want to
    // change age this way, but that's beside the point.
    // Note that we are also double-setting age here, once as a literal and
    // once as an expression. This test will only pass if the expression wins,
    // which is what is supposed to happen.
128
    $this->connection->merge('test_people')
129
      ->key('job', 'Speaker')
130 131 132
      ->fields(['name' => 'Tiffany'])
      ->insertFields(['age' => 31])
      ->expression('age', 'age + :age', [':age' => 4])
133 134
      ->execute();

135
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
136
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
137

138
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
139 140 141
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, $age_before + 4, 'Age updated correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
142 143 144
  }

  /**
145
   * Tests that we can merge-insert without any update fields.
146
   */
147
  public function testMergeInsertWithoutUpdate() {
148
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
149

150
    $this->connection->merge('test_people')
151
      ->key('job', 'Presenter')
152 153
      ->execute();

154
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
155
    $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
156

157
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Presenter'])->fetch();
158 159 160
    $this->assertEqual($person->name, '', 'Name set correctly.');
    $this->assertEqual($person->age, 0, 'Age set correctly.');
    $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
161 162 163
  }

  /**
164
   * Confirms that we can merge-update without any update fields.
165
   */
166
  public function testMergeUpdateWithoutUpdate() {
167
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
168

169
    $this->connection->merge('test_people')
170
      ->key('job', 'Speaker')
171 172
      ->execute();

173
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
174
    $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
175

176
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
177 178 179
    $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
180

181
    $this->connection->merge('test_people')
182
      ->key('job', 'Speaker')
183
      ->insertFields(['age' => 31])
184 185
      ->execute();

186
    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {test_people}')->fetchField();
187
    $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
188

189
    $person = $this->connection->query('SELECT * FROM {test_people} WHERE [job] = :job', [':job' => 'Speaker'])->fetch();
190 191 192
    $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
193 194 195
  }

  /**
196
   * Tests that an invalid merge query throws an exception.
197
   */
198
  public function testInvalidMerge() {
199
    try {
200
      // This query will fail because there is no key field specified.
201
      // Normally it would throw an exception but we are suppressing it with
202 203
      // the throw_exception option.
      $options['throw_exception'] = FALSE;
204
      $this->connection->merge('test_people', $options)
205
        ->fields([
206 207
          'age' => 31,
          'name' => 'Tiffany',
208
        ])
209 210 211 212 213 214 215 216 217 218
        ->execute();
      $this->pass('$options[\'throw_exception\'] is FALSE, no InvalidMergeQueryException thrown.');
    }
    catch (InvalidMergeQueryException $e) {
      $this->fail('$options[\'throw_exception\'] is FALSE, but InvalidMergeQueryException thrown for invalid query.');
      return;
    }

    try {
      // This query will fail because there is no key field specified.
219
      $this->connection->merge('test_people')
220
        ->fields([
221 222
          'age' => 31,
          'name' => 'Tiffany',
223
        ])
224 225 226
        ->execute();
    }
    catch (InvalidMergeQueryException $e) {
227
      $this->pass('InvalidMergeQueryException thrown for invalid query.');
228 229
      return;
    }
230
    $this->fail('No InvalidMergeQueryException thrown');
231
  }
232

233 234 235 236 237 238 239 240 241 242 243 244 245
  /**
   * Tests that we can merge-insert with reserved keywords.
   */
  public function testMergeWithReservedWords() {
    $num_records_before = $this->connection->query('SELECT COUNT(*) FROM {select}')->fetchField();

    $this->connection->merge('select')
      ->key('id', 2)
      ->execute();

    $num_records_after = $this->connection->query('SELECT COUNT(*) FROM {select}')->fetchField();
    $this->assertEquals($num_records_before + 1, $num_records_after, 'Merge inserted properly.');

246
    $person = $this->connection->query('SELECT * FROM {select} WHERE [id] = :id', [':id' => 2])->fetch();
247 248 249 250
    $this->assertEquals('', $person->update);
    $this->assertEquals('2', $person->id);
  }

251
}