tests/phpunit/CRM/Core/BAO/SchemaHandlerTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Class CRM_Core_BAO_SchemaHandlerTest.
  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.
  17  *
  18  * @group headless
  19  */
  20 class CRM_Core_BAO_SchemaHandlerTest extends CiviUnitTestCase {
  21
  22   /**
  23    * Ensure any removed indices are put back.
  24    *
  25    * @throws \CRM_Core_Exception
  26    */
  27   public function tearDown() {
  28     parent::tearDown();
  29     $this->callAPISuccess('System', 'updateindexes', []);
  30   }
  31
  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() {
  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');
  43     $count = 0;
  44
  45     while ($dao->fetch()) {
  46       if ($dao->Column_name === 'weight') {
  47         $count++;
  48         CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $dao->Key_name);
  49       }
  50     }
  51     $this->assertEquals(1, $count);
  52   }
  53
  54   /**
  55    * Test CRM_Core_BAO_SchemaHandler::getIndexes() function
  56    */
  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']));
  60   }
  61
  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() {
  69     $tables = ['civicrm_uf_join' => ['weight']];
  70     CRM_Core_BAO_SchemaHandler::createIndexes($tables);
  71
  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');
  75     $weightCount = 0;
  76     $indexes = [];
  77
  78     while ($dao->fetch()) {
  79       if ($dao->Column_name === 'weight') {
  80         $weightCount++;
  81         $indexes[$dao->Key_name] = $dao->Key_name;
  82       }
  83       if ($dao->Column_name === 'module') {
  84         $this->assertArrayHasKey($dao->Key_name, $indexes);
  85       }
  86
  87     }
  88     foreach (array_keys($indexes) as $index) {
  89       CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_uf_join DROP INDEX ' . $index);
  90     }
  91     $this->assertEquals(2, $weightCount);
  92   }
  93
  94   /**
  95    * Test the drop index if exists function for a non-existent index.
  96    */
  97   public function testCheckIndexNotExists() {
  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    */
 104   public function testCheckIndexExists() {
 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.
 123     CRM_Core_BAO_SchemaHandler::createIndexes(['civicrm_contact' => ['hash']]);
 124   }
 125
 126   /**
 127    * @return array
 128    */
 129   public function columnTests() {
 130     $columns = [];
 131     $columns[] = ['civicrm_contribution', 'total_amount'];
 132     $columns[] = ['civicrm_contact', 'first_name'];
 133     $columns[] = ['civicrm_contact', 'xxxx'];
 134     return $columns;
 135   }
 136
 137   /**
 138    * @param $tableName
 139    * @param $columnName
 140    *
 141    * @dataProvider columnTests
 142    */
 143   public function testCheckIfColumnExists($tableName, $columnName) {
 144     if ($columnName === 'xxxx') {
 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
 152   /**
 153    * @return array
 154    */
 155   public function foreignKeyTests() {
 156     $keys = [];
 157     $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id'];
 158     $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_id'];
 159     return $keys;
 160   }
 161
 162   /**
 163    * Test to see if we can drop foreign key
 164    *
 165    * @dataProvider foreignKeyTests
 166    *
 167    * @param string $tableName
 168    * @param string $key
 169    */
 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));
 173     }
 174     else {
 175       $this->assertTrue(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key));
 176     }
 177   }
 178
 179   /**
 180    * Check there are no missing indices
 181    */
 182   public function testGetMissingIndices() {
 183     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 184     $this->assertEmpty($missingIndices);
 185   }
 186
 187   /**
 188    * Test that missing indices are correctly created
 189    */
 190   public function testCreateMissingIndices() {
 191     $indices = [
 192       'test_table' => [
 193         'test_index1' => [
 194           'name' => 'test_index1',
 195           'field' => [
 196             'title',
 197           ],
 198           'unique' => FALSE,
 199         ],
 200         'test_index2' => [
 201           'name' => 'test_index2',
 202           'field' => [
 203             'title',
 204           ],
 205           'unique' => TRUE,
 206         ],
 207         'test_index3' => [
 208           'name' => 'test_index3',
 209           'field' => [
 210             'title(3)',
 211             'name',
 212           ],
 213           'unique' => FALSE,
 214         ],
 215       ],
 216     ];
 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);
 222   }
 223
 224   /**
 225    * Check there are no missing indices
 226    *
 227    * @throws \CRM_Core_Exception
 228    */
 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' => [
 237         [
 238           'name' => 'index_sort_name',
 239           'field' => ['sort_name'],
 240           'localizable' => FALSE,
 241           'sig' => 'civicrm_contact::0::sort_name',
 242         ],
 243       ],
 244     ], $missingIndices);
 245     $this->callAPISuccess('System', 'updateindexes', []);
 246     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 247     $this->assertEmpty($missingIndices);
 248   }
 249
 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);
 280     $this->callAPISuccess('System', 'updateindexes', ['tables' => 'civicrm_contribution']);
 281     $missingIndices = $this->callAPISuccess('System', 'getmissingindices', [])['values'];
 282     $this->assertEquals(['civicrm_contact' => $expected['civicrm_contact']], $missingIndices);
 283   }
 284
 285   /**
 286    * Check for partial indices
 287    */
 288   public function testPartialIndices() {
 289     $tables = [
 290       'index_all' => 'civicrm_prevnext_cache',
 291       'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
 292     ];
 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);
 298
 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));
 305
 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])));
 310     }
 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);
 316   }
 317
 318   /**
 319    * Test index signatures are added correctly
 320    */
 321   public function testAddIndexSignatures() {
 322     $indices = [
 323       'one' => [
 324         'field' => ['id', 'name(3)'],
 325         'unique' => TRUE,
 326       ],
 327       'two' => [
 328         'field' => ['title'],
 329       ],
 330     ];
 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
 336   /**
 337    * Test that columns are dropped
 338    */
 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))');
 342
 343     // test with logging enabled to ensure log triggers don't break anything
 344     $schema = new CRM_Logging_Schema();
 345     $schema->enableLogging();
 346
 347     $alterParams = [
 348       'table_name' => 'civicrm_test_drop_column',
 349       'operation'  => 'delete',
 350       'name'       => 'col1',
 351       'type'       => 'varchar(255)',
 352       'required'   => FALSE,
 353       'searchable' => FALSE,
 354     ];
 355
 356     // drop col1
 357     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 358
 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);
 363     }
 364
 365     // drop col2
 366     $alterParams['name'] = 'col2';
 367     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 368
 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);
 372     }
 373   }
 374
 375   /**
 376    * Tests the function that generates sql to modify fields.
 377    */
 378   public function testBuildFieldChangeSql() {
 379     $params = [
 380       'table_name' => 'big_table',
 381       'operation' => 'add',
 382       'name' => 'big_bob',
 383       'type' => 'text',
 384     ];
 385     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 386     $this->assertEquals('ALTER TABLE big_table
 387         ADD COLUMN `big_bob` text', trim($sql));
 388
 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));
 394
 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));
 398   }
 399
 400 }