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

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