3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
13 * Class CRM_Core_BAO_SchemaHandlerTest.
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.
20 class CRM_Core_BAO_SchemaHandlerTest
extends CiviUnitTestCase
{
23 * Ensure any removed indices are put back.
25 * @throws \CRM_Core_Exception
27 public function tearDown() {
29 $this->callAPISuccess('System', 'updateindexes', []);
33 * Test creating an index.
35 * We want to be sure it creates an index and exits gracefully if the index
38 public function testCreateIndex() {
39 $tables = ['civicrm_uf_join' => ['weight']];
40 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
41 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
42 $dao = CRM_Core_DAO
::executeQuery('SHOW INDEX FROM civicrm_uf_join');
45 while ($dao->fetch()) {
46 if ($dao->Column_name
=== 'weight') {
48 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $dao->Key_name
);
51 $this->assertEquals(1, $count);
55 * Test CRM_Core_BAO_SchemaHandler::getIndexes() function
57 public function testGetIndexes() {
58 $indexes = CRM_Core_BAO_SchemaHandler
::getIndexes(['civicrm_contact']);
59 $this->assertTrue(array_key_exists('index_contact_type', $indexes['civicrm_contact']));
63 * Test creating an index.
65 * We want to be sure it creates an index and exits gracefully if the index
68 public function testCombinedIndex() {
69 $tables = ['civicrm_uf_join' => ['weight']];
70 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
72 $tables = ['civicrm_uf_join' => [['weight', 'module']]];
73 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
74 $dao = CRM_Core_DAO
::executeQuery('SHOW INDEX FROM civicrm_uf_join');
78 while ($dao->fetch()) {
79 if ($dao->Column_name
=== 'weight') {
81 $indexes[$dao->Key_name
] = $dao->Key_name
;
83 if ($dao->Column_name
=== 'module') {
84 $this->assertArrayHasKey($dao->Key_name
, $indexes);
88 foreach (array_keys($indexes) as $index) {
89 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $index);
91 $this->assertEquals(2, $weightCount);
95 * Test the drop index if exists function for a non-existent index.
97 public function testCheckIndexNotExists() {
98 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::checkIfIndexExists('civicrm_contact', 'magic_button'));
102 * Test the drop index if exists function for a non-existent index.
104 public function testCheckIndexExists() {
105 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::checkIfIndexExists('civicrm_contact', 'index_hash'));
109 * Test the drop index if exists function for a non-existent index.
111 public function testDropIndexNoneExists() {
112 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_contact', 'magic_button');
116 * Test the drop index if exists function.
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'));
122 // Recreate it to clean up after the test.
123 CRM_Core_BAO_SchemaHandler
::createIndexes(['civicrm_contact' => ['hash']]);
129 public function columnTests() {
131 $columns[] = ['civicrm_contribution', 'total_amount'];
132 $columns[] = ['civicrm_contact', 'first_name'];
133 $columns[] = ['civicrm_contact', 'xxxx'];
141 * @dataProvider columnTests
143 public function testCheckIfColumnExists($tableName, $columnName) {
144 if ($columnName === 'xxxx') {
145 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($tableName, $columnName));
148 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($tableName, $columnName));
155 public function foreignKeyTests() {
157 $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id'];
158 $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_id'];
163 * Test to see if we can drop foreign key
165 * @dataProvider foreignKeyTests
167 * @param string $tableName
170 public function testSafeDropForeignKey($tableName, $key) {
171 if ($key === 'FK_civicrm_mailing_recipients_id') {
172 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::safeRemoveFK('civicrm_mailing_recipients', $key));
175 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::safeRemoveFK('civicrm_mailing_recipients', $key));
180 * Check there are no missing indices
182 public function testGetMissingIndices() {
183 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
184 $this->assertEmpty($missingIndices);
188 * Test that missing indices are correctly created
190 public function testCreateMissingIndices() {
194 'name' => 'test_index1',
201 'name' => 'test_index2',
208 'name' => 'test_index3',
217 CRM_Core_DAO
::executeQuery('DROP table if exists `test_table`');
218 CRM_Core_DAO
::executeQuery('CREATE table `test_table` (`title` varchar(255), `name` varchar(255))');
219 CRM_Core_BAO_SchemaHandler
::createMissingIndices($indices);
220 $actualIndices = CRM_Core_BAO_SchemaHandler
::getIndexes(['test_table']);
221 $this->assertEquals($actualIndices, $indices);
225 * Check there are no missing indices
227 * @throws \CRM_Core_Exception
229 public function testReconcileMissingIndices() {
230 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name');
231 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
232 // Check the api also retrieves them.
233 $missingIndicesAPI = $this->callAPISuccess('System', 'getmissingindices', [])['values'];
234 $this->assertEquals($missingIndices, $missingIndicesAPI);
235 $this->assertEquals([
236 'civicrm_contact' => [
238 'name' => 'index_sort_name',
239 'field' => ['sort_name'],
240 'localizable' => FALSE,
241 'sig' => 'civicrm_contact::0::sort_name',
245 $this->callAPISuccess('System', 'updateindexes', []);
246 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
247 $this->assertEmpty($missingIndices);
251 * Check there are no missing indices
253 * @throws \CRM_Core_Exception
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'];
260 'civicrm_contact' => [
262 'name' => 'index_sort_name',
263 'field' => ['sort_name'],
264 'localizable' => FALSE,
265 'sig' => 'civicrm_contact::0::sort_name',
268 'civicrm_contribution' => [
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',
277 $this->assertEquals($expected, $missingIndices);
278 $missingIndices = $this->callAPISuccess('System', 'getmissingindices', ['tables' => ['civicrm_contact']])['values'];
279 $this->assertEquals(['civicrm_contact' => $expected['civicrm_contact']], $missingIndices);
280 $this->callAPISuccess('System', 'updateindexes', ['tables' => 'civicrm_contribution']);
281 $missingIndices = $this->callAPISuccess('System', 'getmissingindices', [])['values'];
282 $this->assertEquals(['civicrm_contact' => $expected['civicrm_contact']], $missingIndices);
286 * Check for partial indices
288 public function testPartialIndices() {
290 'index_all' => 'civicrm_prevnext_cache',
291 'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
293 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_prevnext_cache', 'index_all');
294 //Missing Column `is_selected`.
295 CRM_Core_DAO
::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)');
296 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
297 $this->assertNotEmpty($missingIndices);
299 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id');
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)');
302 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices(TRUE);
303 $this->assertNotEmpty($missingIndices);
304 $this->assertEquals(array_values($tables), array_keys($missingIndices));
306 //Check if both indices are deleted.
307 $indices = CRM_Core_BAO_SchemaHandler
::getIndexes($tables);
308 foreach ($tables as $index => $tableName) {
309 $this->assertFalse(in_array($index, array_keys($indices[$tableName])));
311 //Drop false index and create again.
312 CRM_Core_BAO_SchemaHandler
::createMissingIndices($missingIndices);
313 //Both vars should be empty now.
314 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
315 $this->assertEmpty($missingIndices);
319 * Test index signatures are added correctly
321 public function testAddIndexSignatures() {
324 'field' => ['id', 'name(3)'],
328 'field' => ['title'],
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');
337 * Test that columns are dropped
339 public function testDropColumn() {
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))');
343 // test with logging enabled to ensure log triggers don't break anything
344 $schema = new CRM_Logging_Schema();
345 $schema->enableLogging();
348 'table_name' => 'civicrm_test_drop_column',
349 'operation' => 'delete',
351 'type' => 'varchar(255)',
353 'searchable' => FALSE,
357 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
359 $create_table = CRM_Core_DAO
::executeQuery('SHOW CREATE TABLE civicrm_test_drop_column');
360 while ($create_table->fetch()) {
361 $this->assertNotContains('col1', $create_table->Create_Table
);
362 $this->assertContains('col2', $create_table->Create_Table
);
366 $alterParams['name'] = 'col2';
367 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
369 $create_table = CRM_Core_DAO
::executeQuery('SHOW CREATE TABLE civicrm_test_drop_column');
370 while ($create_table->fetch()) {
371 $this->assertNotContains('col2', $create_table->Create_Table
);
376 * Tests the function that generates sql to modify fields.
378 public function testBuildFieldChangeSql() {
380 'table_name' => 'big_table',
381 'operation' => 'add',
385 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
386 $this->assertEquals('ALTER TABLE big_table
387 ADD COLUMN `big_bob` text', trim($sql));
389 $params['operation'] = 'modify';
390 $params['comment'] = 'super big';
391 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
392 $this->assertEquals("ALTER TABLE big_table
393 MODIFY `big_bob` text COMMENT 'super big'", trim($sql));
395 $params['operation'] = 'delete';
396 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
397 $this->assertEquals('ALTER TABLE big_table DROP COLUMN `big_bob`', trim($sql));