3 namespace Drupal\KernelTests\Core\Database;
5 use Drupal\Core\Database\Query\Merge;
6 use Drupal\Core\Database\Query\InvalidMergeQueryException;
9 * Tests the MERGE query builder.
13 class MergeTest extends DatabaseTestBase {
16 * Confirms that we can merge-insert a record successfully.
18 public function testMergeInsert() {
19 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
21 $result = db_merge('test_people')
22 ->key('job', 'Presenter')
29 $this->assertEqual($result, Merge::STATUS_INSERT, 'Insert status returned.');
31 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
32 $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
34 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Presenter'])->fetch();
35 $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
36 $this->assertEqual($person->age, 31, 'Age set correctly.');
37 $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
41 * Confirms that we can merge-update a record successfully.
43 public function testMergeUpdate() {
44 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
46 $result = db_merge('test_people')
47 ->key('job', 'Speaker')
54 $this->assertEqual($result, Merge::STATUS_UPDATE, 'Update status returned.');
56 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
57 $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
59 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
60 $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
61 $this->assertEqual($person->age, 31, 'Age set correctly.');
62 $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
66 * Confirms that we can merge-update a record successfully.
68 * This test varies from the previous test because it manually defines which
69 * fields are inserted, and which fields are updated.
71 public function testMergeUpdateExcept() {
72 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
74 db_merge('test_people')
75 ->key('job', 'Speaker')
76 ->insertFields(['age' => 31])
77 ->updateFields(['name' => 'Tiffany'])
80 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
81 $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
83 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
84 $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
85 $this->assertEqual($person->age, 30, 'Age skipped correctly.');
86 $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
90 * Confirms that we can merge-update a record, with alternate replacement.
92 public function testMergeUpdateExplicit() {
93 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
95 db_merge('test_people')
96 ->key('job', 'Speaker')
106 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
107 $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
109 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
110 $this->assertEqual($person->name, 'Joe', 'Name set correctly.');
111 $this->assertEqual($person->age, 30, 'Age skipped correctly.');
112 $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
116 * Confirms that we can merge-update a record successfully, with expressions.
118 public function testMergeUpdateExpression() {
119 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
121 $age_before = db_query('SELECT age FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetchField();
123 // This is a very contrived example, as I have no idea why you'd want to
124 // change age this way, but that's beside the point.
125 // Note that we are also double-setting age here, once as a literal and
126 // once as an expression. This test will only pass if the expression wins,
127 // which is what is supposed to happen.
128 db_merge('test_people')
129 ->key('job', 'Speaker')
130 ->fields(['name' => 'Tiffany'])
131 ->insertFields(['age' => 31])
132 ->expression('age', 'age + :age', [':age' => 4])
135 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
136 $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
138 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
139 $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
140 $this->assertEqual($person->age, $age_before + 4, 'Age updated correctly.');
141 $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
145 * Tests that we can merge-insert without any update fields.
147 public function testMergeInsertWithoutUpdate() {
148 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
150 db_merge('test_people')
151 ->key('job', 'Presenter')
154 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
155 $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
157 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Presenter'])->fetch();
158 $this->assertEqual($person->name, '', 'Name set correctly.');
159 $this->assertEqual($person->age, 0, 'Age set correctly.');
160 $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
164 * Confirms that we can merge-update without any update fields.
166 public function testMergeUpdateWithoutUpdate() {
167 $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
169 db_merge('test_people')
170 ->key('job', 'Speaker')
173 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
174 $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
176 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
177 $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
178 $this->assertEqual($person->age, 30, 'Age skipped correctly.');
179 $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
181 db_merge('test_people')
182 ->key('job', 'Speaker')
183 ->insertFields(['age' => 31])
186 $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
187 $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
189 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', [':job' => 'Speaker'])->fetch();
190 $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
191 $this->assertEqual($person->age, 30, 'Age skipped correctly.');
192 $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
196 * Tests that an invalid merge query throws an exception.
198 public function testInvalidMerge() {
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 // the throw_exception option.
203 $options['throw_exception'] = FALSE;
204 db_merge('test_people', $options)
210 $this->pass('$options[\'throw_exception\'] is FALSE, no InvalidMergeQueryException thrown.');
212 catch (InvalidMergeQueryException $e) {
213 $this->fail('$options[\'throw_exception\'] is FALSE, but InvalidMergeQueryException thrown for invalid query.');
218 // This query will fail because there is no key field specified.
219 db_merge('test_people')
226 catch (InvalidMergeQueryException $e) {
227 $this->pass('InvalidMergeQueryException thrown for invalid query.');
230 $this->fail('No InvalidMergeQueryException thrown');