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  * @group headless
  18  */
  19 class CRM_Core_BAO_SchemaHandlerTest extends CiviUnitTestCase {
  20
  21   /**
  22    * Test creating an index.
  23    *
  24    * We want to be sure it creates an index and exits gracefully if the index
  25    * already exists.
  26    */
  27   public function testCreateIndex() {
  28     $tables = ['civicrm_uf_join' => ['weight']];
  29     CRM_Core_BAO_SchemaHandler::createIndexes($tables);
  30     CRM_Core_BAO_SchemaHandler::createIndexes($tables);
  31     $dao = CRM_Core_DAO::executeQuery("SHOW INDEX FROM civicrm_uf_join");
  32     $count = 0;
  33
  34     while ($dao->fetch()) {
  35       if ($dao->Column_name == 'weight') {
  36         $count++;
  37         CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $dao->Key_name);
  38       }
  39     }
  40     $this->assertEquals(1, $count);
  41   }
  42
  43   /**
  44    * Test CRM_Core_BAO_SchemaHandler::getIndexes() function
  45    */
  46   public function testGetIndexes() {
  47     $indexes = CRM_Core_BAO_SchemaHandler::getIndexes(['civicrm_contact']);
  48     $this->assertTrue(array_key_exists('index_contact_type', $indexes['civicrm_contact']));
  49   }
  50
  51   /**
  52    * Test creating an index.
  53    *
  54    * We want to be sure it creates an index and exits gracefully if the index
  55    * already exists.
  56    */
  57   public function testCombinedIndex() {
  58     $tables = ['civicrm_uf_join' => ['weight']];
  59     CRM_Core_BAO_SchemaHandler::createIndexes($tables);
  60
  61     $tables = ['civicrm_uf_join' => [['weight', 'module']]];
  62     CRM_Core_BAO_SchemaHandler::createIndexes($tables);
  63     $dao = CRM_Core_DAO::executeQuery("SHOW INDEX FROM civicrm_uf_join");
  64     $weightCount = 0;
  65     $combinedCount = 0;
  66     $indexes = [];
  67
  68     while ($dao->fetch()) {
  69       if ($dao->Column_name == 'weight') {
  70         $weightCount++;
  71         $indexes[$dao->Key_name] = $dao->Key_name;
  72       }
  73       if ($dao->Column_name == 'module') {
  74         $combinedCount++;
  75         $this->assertArrayHasKey($dao->Key_name, $indexes);
  76       }
  77
  78     }
  79     foreach (array_keys($indexes) as $index) {
  80       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $index);
  81     }
  82     $this->assertEquals(2, $weightCount);
  83   }
  84
  85   /**
  86    * Test the drop index if exists function for a non-existent index.
  87    */
  88   public function testCheckIndexNotExists() {
  89     $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'magic_button'));
  90   }
  91
  92   /**
  93    * Test the drop index if exists function for a non-existent index.
  94    */
  95   public function testCheckIndexExists() {
  96     $this->assertTrue(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'index_hash'));
  97   }
  98
  99   /**
 100    * Test the drop index if exists function for a non-existent index.
 101    */
 102   public function testDropIndexNoneExists() {
 103     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_contact', 'magic_button');
 104   }
 105
 106   /**
 107    * Test the drop index if exists function.
 108    */
 109   public function testDropIndexExists() {
 110     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_contact', 'index_hash');
 111     $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'index_hash'));
 112
 113     // Recreate it to clean up after the test.
 114     CRM_Core_BAO_SchemaHandler::createIndexes(['civicrm_contact' => ['hash']]);
 115   }
 116
 117   /**
 118    * @return array
 119    */
 120   public function columnTests() {
 121     $columns = [];
 122     $columns[] = ['civicrm_contribution', 'total_amount'];
 123     $columns[] = ['civicrm_contact', 'first_name'];
 124     $columns[] = ['civicrm_contact', 'xxxx'];
 125     return $columns;
 126   }
 127
 128   /**
 129    * @param $tableName
 130    * @param $columnName
 131    *
 132    * @dataProvider columnTests
 133    */
 134   public function testCheckIfColumnExists($tableName, $columnName) {
 135     if ($columnName == 'xxxx') {
 136       $this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName));
 137     }
 138     else {
 139       $this->assertTrue(CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName));
 140     }
 141   }
 142
 143   /**
 144    * @return array
 145    */
 146   public function foreignKeyTests() {
 147     $keys = [];
 148     $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id'];
 149     $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_id'];
 150     return $keys;
 151   }
 152
 153   /**
 154    * Test to see if we can drop foreign key
 155    *
 156    * @dataProvider foreignKeyTests
 157    */
 158   public function testSafeDropForeignKey($tableName, $key) {
 159     if ($key == 'FK_civicrm_mailing_recipients_id') {
 160       $this->assertFalse(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key));
 161     }
 162     else {
 163       $this->assertTrue(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key));
 164     }
 165   }
 166
 167   /**
 168    * Check there are no missing indices
 169    */
 170   public function testGetMissingIndices() {
 171     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 172     $this->assertEmpty($missingIndices);
 173   }
 174
 175   /**
 176    * Test that missing indices are correctly created
 177    */
 178   public function testCreateMissingIndices() {
 179     $indices = [
 180       'test_table' => [
 181         'test_index1' => [
 182           'name' => 'test_index1',
 183           'field' => [
 184             'title',
 185           ],
 186           'unique' => FALSE,
 187         ],
 188         'test_index2' => [
 189           'name' => 'test_index2',
 190           'field' => [
 191             'title',
 192           ],
 193           'unique' => TRUE,
 194         ],
 195         'test_index3' => [
 196           'name' => 'test_index3',
 197           'field' => [
 198             'title(3)',
 199             'name',
 200           ],
 201           'unique' => FALSE,
 202         ],
 203       ],
 204     ];
 205     CRM_Core_DAO::executeQuery('DROP table if exists `test_table`');
 206     CRM_Core_DAO::executeQuery('CREATE table `test_table` (`title` varchar(255), `name` varchar(255))');
 207     CRM_Core_BAO_SchemaHandler::createMissingIndices($indices);
 208     $actualIndices = CRM_Core_BAO_SchemaHandler::getIndexes(['test_table']);
 209     $this->assertEquals($actualIndices, $indices);
 210   }
 211
 212   /**
 213    * Check there are no missing indices
 214    *
 215    * @throws \CRM_Core_Exception
 216    */
 217   public function testReconcileMissingIndices() {
 218     CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name');
 219     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 220     // Check the api also retrieves them.
 221     $missingIndicesAPI = $this->callAPISuccess('System', 'getmissingindices', [])['values'];
 222     $this->assertEquals($missingIndices, $missingIndicesAPI);
 223     $this->assertEquals([
 224       'civicrm_contact' => [
 225         [
 226           'name' => 'index_sort_name',
 227           'field' => ['sort_name'],
 228           'localizable' => FALSE,
 229           'sig' => 'civicrm_contact::0::sort_name',
 230         ],
 231       ],
 232     ], $missingIndices);
 233     $this->callAPISuccess('System', 'updateindexes', []);
 234     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 235     $this->assertEmpty($missingIndices);
 236   }
 237
 238   /**
 239    * Check for partial indices
 240    */
 241   public function testPartialIndices() {
 242     $tables = [
 243       'index_all' => 'civicrm_prevnext_cache',
 244       'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
 245     ];
 246     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_prevnext_cache', 'index_all');
 247     //Missing Column `is_selected`.
 248     CRM_Core_DAO::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)');
 249     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 250     $this->assertNotEmpty($missingIndices);
 251
 252     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id');
 253     //Test incorrect Ordering(correct order defined is entity_id and then entity_table, tag_id).
 254     CRM_Core_DAO::executeQuery('CREATE INDEX UI_entity_id_entity_table_tag_id ON civicrm_entity_tag (entity_table, entity_id, tag_id)');
 255     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(TRUE);
 256     $this->assertNotEmpty($missingIndices);
 257     $this->assertEquals(array_values($tables), array_keys($missingIndices));
 258
 259     //Check if both indices are deleted.
 260     $indices = CRM_Core_BAO_SchemaHandler::getIndexes($tables);
 261     foreach ($tables as $index => $tableName) {
 262       $this->assertFalse(in_array($index, array_keys($indices[$tableName])));
 263     }
 264     //Drop false index and create again.
 265     CRM_Core_BAO_SchemaHandler::createMissingIndices($missingIndices);
 266     //Both vars should be empty now.
 267     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 268     $this->assertEmpty($missingIndices);
 269   }
 270
 271   /**
 272    * Test index signatures are added correctly
 273    */
 274   public function testAddIndexSignatures() {
 275     $indices = [
 276       'one' => [
 277         'field' => ['id', 'name(3)'],
 278         'unique' => TRUE,
 279       ],
 280       'two' => [
 281         'field' => ['title'],
 282       ],
 283     ];
 284     CRM_Core_BAO_SchemaHandler::addIndexSignature('my_table', $indices);
 285     $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)');
 286     $this->assertEquals($indices['two']['sig'], 'my_table::0::title');
 287   }
 288
 289   /**
 290    * Test that columns are dropped
 291    */
 292   public function testDropColumn() {
 293     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS `civicrm_test_drop_column`');
 294     CRM_Core_DAO::executeQuery('CREATE TABLE `civicrm_test_drop_column` (`id` int(10), `col1` varchar(255), `col2` varchar(255))');
 295
 296     // test with logging enabled to ensure log triggers don't break anything
 297     $schema = new CRM_Logging_Schema();
 298     $schema->enableLogging();
 299
 300     $alterParams = [
 301       'table_name' => 'civicrm_test_drop_column',
 302       'operation'  => 'delete',
 303       'name'       => 'col1',
 304       'type'       => 'varchar(255)',
 305       'required'   => FALSE,
 306       'searchable' => FALSE,
 307     ];
 308
 309     // drop col1
 310     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 311
 312     $create_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
 313     while ($create_table->fetch()) {
 314       $this->assertNotContains('col1', $create_table->Create_Table);
 315       $this->assertContains('col2', $create_table->Create_Table);
 316     }
 317
 318     // drop col2
 319     $alterParams['name'] = 'col2';
 320     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 321
 322     $create_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
 323     while ($create_table->fetch()) {
 324       $this->assertNotContains('col2', $create_table->Create_Table);
 325     }
 326   }
 327
 328   /**
 329    * Tests the function that generates sql to modify fields.
 330    */
 331   public function testBuildFieldChangeSql() {
 332     $params = [
 333       'table_name' => 'big_table',
 334       'operation' => 'add',
 335       'name' => 'big_bob',
 336       'type' => 'text',
 337     ];
 338     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 339     $this->assertEquals("ALTER TABLE big_table
 340         ADD COLUMN `big_bob` text", trim($sql));
 341
 342     $params['operation'] = 'modify';
 343     $params['comment'] = 'super big';
 344     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 345     $this->assertEquals("ALTER TABLE big_table
 346         MODIFY `big_bob` text COMMENT 'super big'", trim($sql));
 347
 348     $params['operation'] = 'delete';
 349     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 350     $this->assertEquals('ALTER TABLE big_table DROP COLUMN `big_bob`', trim($sql));
 351   }
 352
 353 }