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->assertTrue(empty($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   public function testReconcileMissingIndices() {
 216     CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name');
 217     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 218     $this->assertEquals([
 219       'civicrm_contact' => [
 220         [
 221           'name' => 'index_sort_name',
 222           'field' => ['sort_name'],
 223           'localizable' => FALSE,
 224           'sig' => 'civicrm_contact::0::sort_name',
 225         ],
 226       ],
 227     ], $missingIndices);
 228     $this->callAPISuccess('System', 'updateindexes', []);
 229     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 230     $this->assertTrue(empty($missingIndices));
 231   }
 232
 233   /**
 234    * Check for partial indices
 235    */
 236   public function testPartialIndices() {
 237     $tables = [
 238       'index_all' => 'civicrm_prevnext_cache',
 239       'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
 240     ];
 241     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_prevnext_cache', 'index_all');
 242     //Missing Column `is_selected`.
 243     CRM_Core_DAO::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)');
 244     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 245     $this->assertNotEmpty($missingIndices);
 246
 247     CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id');
 248     //Test incorrect Ordering(correct order defined is entity_id and then entity_table, tag_id).
 249     CRM_Core_DAO::executeQuery('CREATE INDEX UI_entity_id_entity_table_tag_id ON civicrm_entity_tag (entity_table, entity_id, tag_id)');
 250     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(TRUE);
 251     $this->assertNotEmpty($missingIndices);
 252     $this->assertEquals(array_values($tables), array_keys($missingIndices));
 253
 254     //Check if both indices are deleted.
 255     $indices = CRM_Core_BAO_SchemaHandler::getIndexes($tables);
 256     foreach ($tables as $index => $tableName) {
 257       $this->assertFalse(in_array($index, array_keys($indices[$tableName])));
 258     }
 259     //Drop false index and create again.
 260     CRM_Core_BAO_SchemaHandler::createMissingIndices($missingIndices);
 261     //Both vars should be empty now.
 262     $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
 263     $this->assertEmpty($missingIndices);
 264   }
 265
 266   /**
 267    * Test index signatures are added correctly
 268    */
 269   public function testAddIndexSignatures() {
 270     $indices = [
 271       'one' => [
 272         'field' => ['id', 'name(3)'],
 273         'unique' => TRUE,
 274       ],
 275       'two' => [
 276         'field' => ['title'],
 277       ],
 278     ];
 279     CRM_Core_BAO_SchemaHandler::addIndexSignature('my_table', $indices);
 280     $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)');
 281     $this->assertEquals($indices['two']['sig'], 'my_table::0::title');
 282   }
 283
 284   /**
 285    * Test that columns are dropped
 286    */
 287   public function testDropColumn() {
 288     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS `civicrm_test_drop_column`');
 289     CRM_Core_DAO::executeQuery('CREATE TABLE `civicrm_test_drop_column` (`id` int(10), `col1` varchar(255), `col2` varchar(255))');
 290
 291     // test with logging enabled to ensure log triggers don't break anything
 292     $schema = new CRM_Logging_Schema();
 293     $schema->enableLogging();
 294
 295     $alterParams = [
 296       'table_name' => 'civicrm_test_drop_column',
 297       'operation'  => 'delete',
 298       'name'       => 'col1',
 299       'type'       => 'varchar(255)',
 300       'required'   => FALSE,
 301       'searchable' => FALSE,
 302     ];
 303
 304     // drop col1
 305     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 306
 307     $create_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
 308     while ($create_table->fetch()) {
 309       $this->assertNotContains('col1', $create_table->Create_Table);
 310       $this->assertContains('col2', $create_table->Create_Table);
 311     }
 312
 313     // drop col2
 314     $alterParams['name'] = 'col2';
 315     CRM_Core_DAO::executeQuery(CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($alterParams, FALSE));
 316
 317     $create_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
 318     while ($create_table->fetch()) {
 319       $this->assertNotContains('col2', $create_table->Create_Table);
 320     }
 321   }
 322
 323   /**
 324    * Tests the function that generates sql to modify fields.
 325    */
 326   public function testBuildFieldChangeSql() {
 327     $params = [
 328       'table_name' => 'big_table',
 329       'operation' => 'add',
 330       'name' => 'big_bob',
 331       'type' => 'text',
 332     ];
 333     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 334     $this->assertEquals("ALTER TABLE big_table
 335         ADD COLUMN `big_bob` text", trim($sql));
 336
 337     $params['operation'] = 'modify';
 338     $params['comment'] = 'super big';
 339     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 340     $this->assertEquals("ALTER TABLE big_table
 341         MODIFY `big_bob` text COMMENT 'super big'", trim($sql));
 342
 343     $params['operation'] = 'delete';
 344     $sql = CRM_Core_BAO_SchemaHandler::buildFieldChangeSql($params, FALSE);
 345     $this->assertEquals('ALTER TABLE big_table DROP COLUMN `big_bob`', trim($sql));
 346   }
 347
 348 }