Commit | Line | Data |
---|---|---|
dd4d51af | 1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7d61e75f | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
dd4d51af | 5 | | | |
7d61e75f TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
dd4d51af | 9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
dd4d51af | 12 | /** |
13 | * Class CRM_Core_BAO_SchemaHandlerTest. | |
7181119b | 14 | * |
15 | * These tests create and drop indexes on the civicrm_uf_join table. The indexes | |
16 | * being added and dropped we assume will never exist. | |
138b4c4c | 17 | * |
acb109b7 | 18 | * @group headless |
dd4d51af | 19 | */ |
20 | class CRM_Core_BAO_SchemaHandlerTest extends CiviUnitTestCase { | |
21 | ||
138b4c4c | 22 | /** |
23 | * Ensure any removed indices are put back. | |
24 | * | |
25 | * @throws \CRM_Core_Exception | |
26 | */ | |
594a9328 | 27 | public function tearDown(): void { |
138b4c4c | 28 | parent::tearDown(); |
29 | $this->callAPISuccess('System', 'updateindexes', []); | |
30 | } | |
31 | ||
dd4d51af | 32 | /** |
33 | * Test creating an index. | |
34 | * | |
35 | * We want to be sure it creates an index and exits gracefully if the index | |
36 | * already exists. | |
37 | */ | |
38 | public function testCreateIndex() { | |
9099cab3 | 39 | $tables = ['civicrm_uf_join' => ['weight']]; |
dd4d51af | 40 | CRM_Core_BAO_SchemaHandler::createIndexes($tables); |
41 | CRM_Core_BAO_SchemaHandler::createIndexes($tables); | |
8ad7a9b4 | 42 | $dao = CRM_Core_DAO::executeQuery('SHOW INDEX FROM civicrm_uf_join'); |
dd4d51af | 43 | $count = 0; |
44 | ||
45 | while ($dao->fetch()) { | |
8ad7a9b4 | 46 | if ($dao->Column_name === 'weight') { |
dd4d51af | 47 | $count++; |
8ad7a9b4 | 48 | CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $dao->Key_name); |
dd4d51af | 49 | } |
50 | } | |
51 | $this->assertEquals(1, $count); | |
52 | } | |
53 | ||
49186f94 AS |
54 | /** |
55 | * Test CRM_Core_BAO_SchemaHandler::getIndexes() function | |
56 | */ | |
57 | public function testGetIndexes() { | |
9099cab3 | 58 | $indexes = CRM_Core_BAO_SchemaHandler::getIndexes(['civicrm_contact']); |
49186f94 AS |
59 | $this->assertTrue(array_key_exists('index_contact_type', $indexes['civicrm_contact'])); |
60 | } | |
61 | ||
7181119b | 62 | /** |
63 | * Test creating an index. | |
64 | * | |
65 | * We want to be sure it creates an index and exits gracefully if the index | |
66 | * already exists. | |
67 | */ | |
68 | public function testCombinedIndex() { | |
9099cab3 | 69 | $tables = ['civicrm_uf_join' => ['weight']]; |
7181119b | 70 | CRM_Core_BAO_SchemaHandler::createIndexes($tables); |
71 | ||
9099cab3 | 72 | $tables = ['civicrm_uf_join' => [['weight', 'module']]]; |
7181119b | 73 | CRM_Core_BAO_SchemaHandler::createIndexes($tables); |
8ad7a9b4 | 74 | $dao = CRM_Core_DAO::executeQuery('SHOW INDEX FROM civicrm_uf_join'); |
7181119b | 75 | $weightCount = 0; |
9099cab3 | 76 | $indexes = []; |
7181119b | 77 | |
78 | while ($dao->fetch()) { | |
8ad7a9b4 | 79 | if ($dao->Column_name === 'weight') { |
7181119b | 80 | $weightCount++; |
81 | $indexes[$dao->Key_name] = $dao->Key_name; | |
82 | } | |
8ad7a9b4 | 83 | if ($dao->Column_name === 'module') { |
7181119b | 84 | $this->assertArrayHasKey($dao->Key_name, $indexes); |
85 | } | |
86 | ||
87 | } | |
88 | foreach (array_keys($indexes) as $index) { | |
8ad7a9b4 | 89 | CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $index); |
7181119b | 90 | } |
91 | $this->assertEquals(2, $weightCount); | |
92 | } | |
93 | ||
50969d52 | 94 | /** |
95 | * Test the drop index if exists function for a non-existent index. | |
96 | */ | |
fe891bc6 | 97 | public function testCheckIndexNotExists() { |
50969d52 | 98 | $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'magic_button')); |
99 | } | |
100 | ||
101 | /** | |
102 | * Test the drop index if exists function for a non-existent index. | |
103 | */ | |
fe891bc6 | 104 | public function testCheckIndexExists() { |
50969d52 | 105 | $this->assertTrue(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'index_hash')); |
106 | } | |
107 | ||
108 | /** | |
109 | * Test the drop index if exists function for a non-existent index. | |
110 | */ | |
111 | public function testDropIndexNoneExists() { | |
112 | CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_contact', 'magic_button'); | |
113 | } | |
114 | ||
115 | /** | |
116 | * Test the drop index if exists function. | |
117 | */ | |
118 | public function testDropIndexExists() { | |
119 | CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_contact', 'index_hash'); | |
120 | $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'index_hash')); | |
121 | ||
122 | // Recreate it to clean up after the test. | |
9099cab3 | 123 | CRM_Core_BAO_SchemaHandler::createIndexes(['civicrm_contact' => ['hash']]); |
50969d52 | 124 | } |
125 | ||
82f5a856 SL |
126 | /** |
127 | * @return array | |
128 | */ | |
129 | public function columnTests() { | |
9099cab3 CW |
130 | $columns = []; |
131 | $columns[] = ['civicrm_contribution', 'total_amount']; | |
132 | $columns[] = ['civicrm_contact', 'first_name']; | |
133 | $columns[] = ['civicrm_contact', 'xxxx']; | |
82f5a856 SL |
134 | return $columns; |
135 | } | |
136 | ||
137 | /** | |
138 | * @param $tableName | |
139 | * @param $columnName | |
140 | * | |
141 | * @dataProvider columnTests | |
142 | */ | |
143 | public function testCheckIfColumnExists($tableName, $columnName) { | |
8ad7a9b4 | 144 | if ($columnName === 'xxxx') { |
82f5a856 SL |
145 | $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName)); |
146 | } | |
147 | else { | |
148 | $this->assertTrue(CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName)); | |
149 | } | |
150 | } | |
151 | ||
9cd5a579 SL |
152 | /** |
153 | * @return array | |
154 | */ | |
155 | public function foreignKeyTests() { | |
9099cab3 CW |
156 | $keys = []; |
157 | $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id']; | |
158 | $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_id']; | |
9cd5a579 SL |
159 | return $keys; |
160 | } | |
161 | ||
169475b7 SL |
162 | /** |
163 | * Test to see if we can drop foreign key | |
164 | * | |
9cd5a579 | 165 | * @dataProvider foreignKeyTests |
8ad7a9b4 | 166 | * |
167 | * @param string $tableName | |
168 | * @param string $key | |
169475b7 | 169 | */ |
9cd5a579 | 170 | public function testSafeDropForeignKey($tableName, $key) { |
8ad7a9b4 | 171 | if ($key === 'FK_civicrm_mailing_recipients_id') { |
9cd5a579 SL |
172 | $this->assertFalse(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key)); |
173 | } | |
174 | else { | |
175 | $this->assertTrue(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key)); | |
169475b7 SL |
176 | } |
177 | } | |
178 | ||
49186f94 AS |
179 | /** |
180 | * Check there are no missing indices | |
181 | */ | |
182 | public function testGetMissingIndices() { | |
3d4602c3 | 183 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); |
a5e6535c | 184 | $this->assertEmpty($missingIndices); |
49186f94 AS |
185 | } |
186 | ||
187 | /** | |
188 | * Test that missing indices are correctly created | |
189 | */ | |
190 | public function testCreateMissingIndices() { | |
9099cab3 CW |
191 | $indices = [ |
192 | 'test_table' => [ | |
193 | 'test_index1' => [ | |
49186f94 | 194 | 'name' => 'test_index1', |
9099cab3 | 195 | 'field' => [ |
49186f94 | 196 | 'title', |
9099cab3 | 197 | ], |
49186f94 | 198 | 'unique' => FALSE, |
9099cab3 CW |
199 | ], |
200 | 'test_index2' => [ | |
49186f94 | 201 | 'name' => 'test_index2', |
9099cab3 | 202 | 'field' => [ |
49186f94 | 203 | 'title', |
9099cab3 | 204 | ], |
49186f94 | 205 | 'unique' => TRUE, |
9099cab3 CW |
206 | ], |
207 | 'test_index3' => [ | |
49186f94 | 208 | 'name' => 'test_index3', |
9099cab3 | 209 | 'field' => [ |
49186f94 AS |
210 | 'title(3)', |
211 | 'name', | |
9099cab3 | 212 | ], |
49186f94 | 213 | 'unique' => FALSE, |
9099cab3 CW |
214 | ], |
215 | ], | |
216 | ]; | |
49186f94 | 217 | CRM_Core_DAO::executeQuery('DROP table if exists `test_table`'); |
387cf360 | 218 | CRM_Core_DAO::executeQuery('CREATE table `test_table` (`title` varchar(255), `name` varchar(255)) ROW_FORMAT=DYNAMIC'); |
49186f94 | 219 | CRM_Core_BAO_SchemaHandler::createMissingIndices($indices); |
9099cab3 | 220 | $actualIndices = CRM_Core_BAO_SchemaHandler::getIndexes(['test_table']); |
49186f94 AS |
221 | $this->assertEquals($actualIndices, $indices); |
222 | } | |
223 | ||
224 | /** | |
225 | * Check there are no missing indices | |
a5e6535c | 226 | * |
227 | * @throws \CRM_Core_Exception | |
49186f94 AS |
228 | */ |
229 | public function testReconcileMissingIndices() { | |
230 | CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name'); | |
3d4602c3 | 231 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); |
a5e6535c | 232 | // Check the api also retrieves them. |
233 | $missingIndicesAPI = $this->callAPISuccess('System', 'getmissingindices', [])['values']; | |
234 | $this->assertEquals($missingIndices, $missingIndicesAPI); | |
9099cab3 CW |
235 | $this->assertEquals([ |
236 | 'civicrm_contact' => [ | |
237 | [ | |
63809327 | 238 | 'name' => 'index_sort_name', |
9099cab3 | 239 | 'field' => ['sort_name'], |
63809327 | 240 | 'localizable' => FALSE, |
241 | 'sig' => 'civicrm_contact::0::sort_name', | |
9099cab3 CW |
242 | ], |
243 | ], | |
244 | ], $missingIndices); | |
245 | $this->callAPISuccess('System', 'updateindexes', []); | |
3d4602c3 | 246 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); |
a5e6535c | 247 | $this->assertEmpty($missingIndices); |
49186f94 AS |
248 | } |
249 | ||
138b4c4c | 250 | /** |
251 | * Check there are no missing indices | |
252 | * | |
253 | * @throws \CRM_Core_Exception | |
254 | */ | |
255 | public function testGetMissingIndicesWithTableFilter() { | |
256 | CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name'); | |
257 | CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contribution DROP INDEX index_total_amount_receive_date'); | |
258 | $missingIndices = $this->callAPISuccess('System', 'getmissingindices', [])['values']; | |
259 | $expected = [ | |
260 | 'civicrm_contact' => [ | |
261 | [ | |
262 | 'name' => 'index_sort_name', | |
263 | 'field' => ['sort_name'], | |
264 | 'localizable' => FALSE, | |
265 | 'sig' => 'civicrm_contact::0::sort_name', | |
266 | ], | |
267 | ], | |
268 | 'civicrm_contribution' => [ | |
269 | [ | |
270 | 'name' => 'index_total_amount_receive_date', | |
271 | 'field' => ['total_amount', 'receive_date'], | |
272 | 'localizable' => FALSE, | |
273 | 'sig' => 'civicrm_contribution::0::total_amount::receive_date', | |
274 | ], | |
275 | ], | |
276 | ]; | |
277 | $this->assertEquals($expected, $missingIndices); | |
278 | $missingIndices = $this->callAPISuccess('System', 'getmissingindices', ['tables' => ['civicrm_contact']])['values']; | |
279 | $this->assertEquals(['civicrm_contact' => $expected['civicrm_contact']], $missingIndices); | |
8e9480bb | 280 | $this->callAPISuccess('System', 'updateindexes', ['tables' => 'civicrm_contribution']); |
281 | $missingIndices = $this->callAPISuccess('System', 'getmissingindices', [])['values']; | |
282 | $this->assertEquals(['civicrm_contact' => $expected['civicrm_contact']], $missingIndices); | |
138b4c4c | 283 | } |
284 | ||
d7367cef JP |
285 | /** |
286 | * Check for partial indices | |
287 | */ | |
288 | public function testPartialIndices() { | |
9099cab3 | 289 | $tables = [ |
3d4602c3 JP |
290 | 'index_all' => 'civicrm_prevnext_cache', |
291 | 'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag', | |
9099cab3 | 292 | ]; |
3d4602c3 | 293 | CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_prevnext_cache', 'index_all'); |
d7367cef | 294 | //Missing Column `is_selected`. |
783b4b21 | 295 | CRM_Core_DAO::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)'); |
3d4602c3 JP |
296 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); |
297 | $this->assertNotEmpty($missingIndices); | |
d7367cef | 298 | |
3d4602c3 | 299 | CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id'); |
d7367cef JP |
300 | //Test incorrect Ordering(correct order defined is entity_id and then entity_table, tag_id). |
301 | CRM_Core_DAO::executeQuery('CREATE INDEX UI_entity_id_entity_table_tag_id ON civicrm_entity_tag (entity_table, entity_id, tag_id)'); | |
3d4602c3 JP |
302 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(TRUE); |
303 | $this->assertNotEmpty($missingIndices); | |
304 | $this->assertEquals(array_values($tables), array_keys($missingIndices)); | |
d7367cef | 305 | |
3d4602c3 JP |
306 | //Check if both indices are deleted. |
307 | $indices = CRM_Core_BAO_SchemaHandler::getIndexes($tables); | |
308 | foreach ($tables as $index => $tableName) { | |
c3f7ab62 | 309 | $this->assertFalse(array_key_exists($index, $indices[$tableName])); |
3d4602c3 | 310 | } |
d7367cef | 311 | //Drop false index and create again. |
d7367cef JP |
312 | CRM_Core_BAO_SchemaHandler::createMissingIndices($missingIndices); |
313 | //Both vars should be empty now. | |
3d4602c3 | 314 | $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); |
d7367cef | 315 | $this->assertEmpty($missingIndices); |
d7367cef JP |
316 | } |
317 | ||
49186f94 AS |
318 | /** |
319 | * Test index signatures are added correctly | |
320 | */ | |
321 | public function testAddIndexSignatures() { | |
9099cab3 CW |
322 | $indices = [ |
323 | 'one' => [ | |
324 | 'field' => ['id', 'name(3)'], | |
49186f94 | 325 | 'unique' => TRUE, |
9099cab3 CW |
326 | ], |
327 | 'two' => [ | |
328 | 'field' => ['title'], | |
329 | ], | |
330 | ]; | |
49186f94 AS |
331 | CRM_Core_BAO_SchemaHandler::addIndexSignature('my_table', $indices); |
332 | $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)'); | |
333 | $this->assertEquals($indices['two']['sig'], 'my_table::0::title'); | |
334 | } | |
335 | ||
dfcc817d PF |
336 | /** |
337 | * Test that columns are dropped | |
338 | */ | |
4b208509 | 339 | public function testDropColumn(): void { |
dfcc817d PF |
340 | CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS `civicrm_test_drop_column`'); |
341 | CRM_Core_DAO::executeQuery('CREATE TABLE `civicrm_test_drop_column` (`id` int(10), `col1` varchar(255), `col2` varchar(255))'); | |
342 | ||
343 | // test with logging enabled to ensure log triggers don't break anything | |
4b208509 | 344 | Civi::settings()->set('logging', TRUE); |
dfcc817d PF |
345 | |
346 | $alterParams = [ | |
347 | 'table_name' => 'civicrm_test_drop_column', | |
348 | 'operation' => 'delete', | |
349 | 'name' => 'col1', | |
350 | 'type' => 'varchar(255)', | |
351 | 'required' => FALSE, | |
352 | 'searchable' => FALSE, | |
353 | ]; | |
354 | ||
355 | // drop col1 | |
3111965c | 356 | CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE)); |
dfcc817d | 357 | |
8ad7a9b4 | 358 | $create_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE civicrm_test_drop_column'); |
dfcc817d | 359 | while ($create_table->fetch()) { |
275686a3 SL |
360 | $this->assertStringNotContainsString('col1', $create_table->Create_Table); |
361 | $this->assertStringContainsString('col2', $create_table->Create_Table); | |
dfcc817d PF |
362 | } |
363 | ||
364 | // drop col2 | |
365 | $alterParams['name'] = 'col2'; | |
3111965c | 366 | CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE)); |
dfcc817d | 367 | |
8ad7a9b4 | 368 | $create_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE civicrm_test_drop_column'); |
dfcc817d | 369 | while ($create_table->fetch()) { |
275686a3 | 370 | $this->assertStringNotContainsString('col2', $create_table->Create_Table); |
dfcc817d PF |
371 | } |
372 | } | |
373 | ||
d992b2f5 | 374 | /** |
375 | * Tests the function that generates sql to modify fields. | |
376 | */ | |
377 | public function testBuildFieldChangeSql() { | |
378 | $params = [ | |
ba9c74ab | 379 | 'table_name' => 'civicrm_contact', |
d992b2f5 | 380 | 'operation' => 'add', |
381 | 'name' => 'big_bob', | |
382 | 'type' => 'text', | |
383 | ]; | |
384 | $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE); | |
ba9c74ab | 385 | $this->assertEquals('ALTER TABLE civicrm_contact |
8ad7a9b4 | 386 | ADD COLUMN `big_bob` text', trim($sql)); |
d992b2f5 | 387 | |
388 | $params['operation'] = 'modify'; | |
389 | $params['comment'] = 'super big'; | |
ba9c74ab | 390 | $params['fkName'] = CRM_Core_BAO_SchemaHandler::getIndexName('civicrm_contact', 'big_bob'); |
d992b2f5 | 391 | $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE); |
ba9c74ab | 392 | $this->assertEquals("ALTER TABLE civicrm_contact |
d992b2f5 | 393 | MODIFY `big_bob` text COMMENT 'super big'", trim($sql)); |
394 | ||
395 | $params['operation'] = 'delete'; | |
396 | $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE); | |
ba9c74ab | 397 | $this->assertEquals('ALTER TABLE civicrm_contact DROP COLUMN `big_bob`', trim($sql)); |
d992b2f5 | 398 | } |
399 | ||
dd4d51af | 400 | } |