CRM/Core/BAO/SchemaHandler.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  */
  33
  34 /**
  35  *  This file contains functions for creating and altering CiviCRM-tables structure.
  36  *
  37  * $table = array(
  38  *  'name'  => TABLE_NAME,
  39  *  'attributes' => ATTRIBUTES,
  40  *  'fields' => array(
  41  *    array(
  42  *      'name' => FIELD_NAME,
  43  *      // can be field, index, constraint
  44  *      'type' => FIELD_SQL_TYPE,
  45  *      'class'         => FIELD_CLASS_TYPE,
  46  *      'primary'       => BOOLEAN,
  47  *      'required'      => BOOLEAN,
  48  *      'searchable'    => TRUE,
  49  *      'fk_table_name' => FOREIGN_KEY_TABLE_NAME,
  50  *      'fk_field_name' => FOREIGN_KEY_FIELD_NAME,
  51  *      'comment'       => COMMENT,
  52  *      'default'       => DEFAULT, )
  53  *      ...
  54  *  ));
  55  */
  56 class CRM_Core_BAO_SchemaHandler {
  57
  58   /**
  59    * Create a CiviCRM-table
  60    *
  61    * @param array $params
  62    *
  63    * @return bool
  64    *   TRUE if successfully created, FALSE otherwise
  65    *
  66    */
  67   public static function createTable(&$params) {
  68     $sql = self::buildTableSQL($params);
  69     // do not i18n-rewrite
  70     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
  71     $dao->free();
  72
  73     $config = CRM_Core_Config::singleton();
  74     if ($config->logging) {
  75       // logging support
  76       $logging = new CRM_Logging_Schema();
  77       $logging->fixSchemaDifferencesFor($params['name'], NULL, FALSE);
  78     }
  79
  80     // always do a trigger rebuild for this table
  81     CRM_Core_DAO::triggerRebuild($params['name']);
  82
  83     return TRUE;
  84   }
  85
  86   /**
  87    * @param array $params
  88    *
  89    * @return string
  90    */
  91   public static function buildTableSQL(&$params) {
  92     $sql = "CREATE TABLE {$params['name']} (";
  93     if (isset($params['fields']) &&
  94       is_array($params['fields'])
  95     ) {
  96       $separator = "\n";
  97       $prefix = NULL;
  98       foreach ($params['fields'] as $field) {
  99         $sql .= self::buildFieldSQL($field, $separator, $prefix);
 100         $separator = ",\n";
 101       }
 102       foreach ($params['fields'] as $field) {
 103         $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix);
 104       }
 105       foreach ($params['fields'] as $field) {
 106         $sql .= self::buildSearchIndexSQL($field, $separator, $prefix);
 107       }
 108       if (isset($params['indexes'])) {
 109         foreach ($params['indexes'] as $index) {
 110           $sql .= self::buildIndexSQL($index, $separator, $prefix);
 111         }
 112       }
 113       foreach ($params['fields'] as $field) {
 114         $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']);
 115       }
 116     }
 117     $sql .= "\n) {$params['attributes']};";
 118     return $sql;
 119   }
 120
 121   /**
 122    * @param array $params
 123    * @param $separator
 124    * @param $prefix
 125    *
 126    * @return string
 127    */
 128   public static function buildFieldSQL(&$params, $separator, $prefix) {
 129     $sql = '';
 130     $sql .= $separator;
 131     $sql .= str_repeat(' ', 8);
 132     $sql .= $prefix;
 133     $sql .= "`{$params['name']}` {$params['type']}";
 134
 135     if (!empty($params['required'])) {
 136       $sql .= " NOT NULL";
 137     }
 138
 139     if (!empty($params['attributes'])) {
 140       $sql .= " {$params['attributes']}";
 141     }
 142
 143     if (!empty($params['default']) &&
 144       $params['type'] != 'text'
 145     ) {
 146       $sql .= " DEFAULT {$params['default']}";
 147     }
 148
 149     if (!empty($params['comment'])) {
 150       $sql .= " COMMENT '{$params['comment']}'";
 151     }
 152
 153     return $sql;
 154   }
 155
 156   /**
 157    * @param array $params
 158    * @param $separator
 159    * @param $prefix
 160    *
 161    * @return NULL|string
 162    */
 163   public static function buildPrimaryKeySQL(&$params, $separator, $prefix) {
 164     $sql = NULL;
 165     if (!empty($params['primary'])) {
 166       $sql .= $separator;
 167       $sql .= str_repeat(' ', 8);
 168       $sql .= $prefix;
 169       $sql .= "PRIMARY KEY ( {$params['name']} )";
 170     }
 171     return $sql;
 172   }
 173
 174   /**
 175    * @param array $params
 176    * @param $separator
 177    * @param $prefix
 178    * @param bool $indexExist
 179    *
 180    * @return NULL|string
 181    */
 182   public static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) {
 183     $sql = NULL;
 184
 185     // dont index blob
 186     if ($params['type'] == 'text') {
 187       return $sql;
 188     }
 189
 190     //create index only for searchable fields during ADD,
 191     //create index only if field is become searchable during MODIFY,
 192     //drop index only if field is no more searchable and index was exist.
 193     if (!empty($params['searchable']) && !$indexExist) {
 194       $sql .= $separator;
 195       $sql .= str_repeat(' ', 8);
 196       $sql .= $prefix;
 197       $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
 198     }
 199     elseif (empty($params['searchable']) && $indexExist) {
 200       $sql .= $separator;
 201       $sql .= str_repeat(' ', 8);
 202       $sql .= "DROP INDEX INDEX_{$params['name']}";
 203     }
 204     return $sql;
 205   }
 206
 207   /**
 208    * @param array $params
 209    * @param $separator
 210    * @param $prefix
 211    *
 212    * @return string
 213    */
 214   public static function buildIndexSQL(&$params, $separator, $prefix) {
 215     $sql = '';
 216     $sql .= $separator;
 217     $sql .= str_repeat(' ', 8);
 218     if ($params['unique']) {
 219       $sql .= 'UNIQUE INDEX';
 220       $indexName = 'unique';
 221     }
 222     else {
 223       $sql .= 'INDEX';
 224       $indexName = 'index';
 225     }
 226     $indexFields = NULL;
 227
 228     foreach ($params as $name => $value) {
 229       if (substr($name, 0, 11) == 'field_name_') {
 230         $indexName .= "_{$value}";
 231         $indexFields .= " $value,";
 232       }
 233     }
 234     $indexFields = substr($indexFields, 0, -1);
 235
 236     $sql .= " $indexName ( $indexFields )";
 237     return $sql;
 238   }
 239
 240   /**
 241    * @param string $tableName
 242    * @param string $fkTableName
 243    *
 244    * @return bool
 245    */
 246   public static function changeFKConstraint($tableName, $fkTableName) {
 247     $fkName = "{$tableName}_entity_id";
 248     if (strlen($fkName) >= 48) {
 249       $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 250     }
 251     $dropFKSql = "
 252 ALTER TABLE {$tableName}
 253       DROP FOREIGN KEY `FK_{$fkName}`;";
 254
 255     $dao = CRM_Core_DAO::executeQuery($dropFKSql);
 256     $dao->free();
 257
 258     $addFKSql = "
 259 ALTER TABLE {$tableName}
 260       ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;";
 261     // CRM-7007: do not i18n-rewrite this query
 262     $dao = CRM_Core_DAO::executeQuery($addFKSql, array(), TRUE, NULL, FALSE, FALSE);
 263     $dao->free();
 264
 265     return TRUE;
 266   }
 267
 268   /**
 269    * @param array $params
 270    * @param $separator
 271    * @param $prefix
 272    * @param string $tableName
 273    *
 274    * @return NULL|string
 275    */
 276   public static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) {
 277     $sql = NULL;
 278     if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) {
 279       $sql .= $separator;
 280       $sql .= str_repeat(' ', 8);
 281       $sql .= $prefix;
 282       $fkName = "{$tableName}_{$params['name']}";
 283       if (strlen($fkName) >= 48) {
 284         $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 285       }
 286
 287       $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) ";
 288       $sql .= CRM_Utils_Array::value('fk_attributes', $params);
 289     }
 290     return $sql;
 291   }
 292
 293   /**
 294    * @param array $params
 295    * @param bool $indexExist
 296    * @param bool $triggerRebuild
 297    *
 298    * @return bool
 299    */
 300   public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) {
 301     $sql = str_repeat(' ', 8);
 302     $sql .= "ALTER TABLE {$params['table_name']}";
 303
 304     // lets suppress the required flag, since that can cause sql issue
 305     $params['required'] = FALSE;
 306
 307     switch ($params['operation']) {
 308       case 'add':
 309         $separator = "\n";
 310         $prefix = "ADD ";
 311         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 312         $separator = ",\n";
 313         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 314         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 315         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 316         break;
 317
 318       case 'modify':
 319         $separator = "\n";
 320         $prefix = "MODIFY ";
 321         $sql .= self::buildFieldSQL($params, $separator, $prefix);
 322         $separator = ",\n";
 323         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
 324         break;
 325
 326       case 'delete':
 327         $sql .= " DROP COLUMN `{$params['name']}`";
 328         if (!empty($params['primary'])) {
 329           $sql .= ", DROP PRIMARY KEY";
 330         }
 331         if (!empty($params['fk_table_name'])) {
 332           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 333         }
 334         break;
 335     }
 336
 337     // CRM-7007: do not i18n-rewrite this query
 338     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
 339     $dao->free();
 340
 341     $config = CRM_Core_Config::singleton();
 342     if ($config->logging) {
 343       // CRM-16717 not sure why this was originally limited to add.
 344       // For example custom tables can have field length changes - which need to flow through to logging.
 345       // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?)
 346       if ($params['operation'] == 'add' || $params['operation'] == 'modify') {
 347         $logging = new CRM_Logging_Schema();
 348         $logging->fixSchemaDifferencesFor($params['table_name'], array(trim($prefix) => array($params['name'])), FALSE);
 349       }
 350     }
 351
 352     if ($triggerRebuild) {
 353       CRM_Core_DAO::triggerRebuild($params['table_name']);
 354     }
 355
 356     return TRUE;
 357   }
 358
 359   /**
 360    * Delete a CiviCRM-table.
 361    *
 362    * @param string $tableName
 363    *   Name of the table to be created.
 364    */
 365   public static function dropTable($tableName) {
 366     $sql = "DROP TABLE $tableName";
 367     CRM_Core_DAO::executeQuery($sql);
 368   }
 369
 370   /**
 371    * @param string $tableName
 372    * @param string $columnName
 373    * @param bool $l18n
 374    * @param bool $isUpgradeMode
 375    *
 376    */
 377   public static function dropColumn($tableName, $columnName, $l18n = FALSE, $isUpgradeMode = FALSE) {
 378     if (self::checkIfFieldExists($tableName, $columnName)) {
 379       $sql = "ALTER TABLE $tableName DROP COLUMN $columnName";
 380       if ($l18n) {
 381         CRM_Core_DAO::executeQuery($sql);
 382       }
 383       else {
 384         CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
 385       }
 386       $domain = new CRM_Core_DAO_Domain();
 387       $domain->find(TRUE);
 388       if ($domain->locales) {
 389         $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 390         CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL, $isUpgradeMode);
 391       }
 392     }
 393   }
 394
 395   /**
 396    * @param string $tableName
 397    * @param bool $dropUnique
 398    */
 399   public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
 400     if ($dropUnique) {
 401       $sql = "ALTER TABLE $tableName
 402 DROP INDEX `unique_entity_id` ,
 403 ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
 404     }
 405     else {
 406       $sql = " ALTER TABLE $tableName
 407 DROP INDEX `FK_{$tableName}_entity_id` ,
 408 ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
 409     }
 410     CRM_Core_DAO::executeQuery($sql);
 411   }
 412
 413   /**
 414    * Create indexes.
 415    *
 416    * @param $tables
 417    *   Tables to create index for in the format:
 418    *     array('civicrm_entity_table' => 'entity_id')
 419    *     OR
 420    *     array('civicrm_entity_table' => array('entity_id', 'entity_table'))
 421    *   The latter will create a combined index on the 2 keys (in order).
 422    *
 423    *  Side note - when creating combined indexes the one with the most variation
 424    *  goes first  - so entity_table always goes after entity_id.
 425    *
 426    *  It probably makes sense to consider more sophisticated options at some point
 427    *  but at the moment this is only being as enhanced as fast as the test is.
 428    *
 429    * @todo add support for length & multilingual on combined keys.
 430    *
 431    * @param string $createIndexPrefix
 432    * @param array $substrLengths
 433    */
 434   public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLengths = array()) {
 435     $queries = array();
 436     $domain = new CRM_Core_DAO_Domain();
 437     $domain->find(TRUE);
 438     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 439
 440     // if we're multilingual, cache the information on internationalised fields
 441     static $columns = NULL;
 442     if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
 443       $columns = CRM_Core_I18n_SchemaStructure::columns();
 444     }
 445
 446     foreach ($tables as $table => $fields) {
 447       $query = "SHOW INDEX FROM $table";
 448       $dao = CRM_Core_DAO::executeQuery($query);
 449
 450       $currentIndexes = array();
 451       while ($dao->fetch()) {
 452         $currentIndexes[] = $dao->Key_name;
 453       }
 454
 455       // now check for all fields if the index exists
 456       foreach ($fields as $field) {
 457         $fieldName = implode('_', (array) $field);
 458
 459         if (is_array($field)) {
 460           // No support for these for combined indexes as yet - add a test when you
 461           // want to add that.
 462           $lengthName = '';
 463           $lengthSize = '';
 464         }
 465         else {
 466           // handle indices over substrings, CRM-6245
 467           // $lengthName is appended to index name, $lengthSize is the field size modifier
 468           $lengthName = isset($substrLengths[$table][$fieldName]) ? "_{$substrLengths[$table][$fieldName]}" : '';
 469           $lengthSize = isset($substrLengths[$table][$fieldName]) ? "({$substrLengths[$table][$fieldName]})" : '';
 470         }
 471
 472         $names = array(
 473           "index_{$fieldName}{$lengthName}",
 474           "FK_{$table}_{$fieldName}{$lengthName}",
 475           "UI_{$fieldName}{$lengthName}",
 476           "{$createIndexPrefix}_{$fieldName}{$lengthName}",
 477         );
 478
 479         // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
 480         foreach ($names as $name) {
 481           $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
 482           if (preg_grep($regex, $currentIndexes)) {
 483             continue 2;
 484           }
 485         }
 486
 487         // the index doesn't exist, so create it
 488         // if we're multilingual and the field is internationalised, do it for every locale
 489         // @todo remove is_array check & add multilingual support for combined indexes and add a test.
 490         // Note combined indexes currently using this function are on fields like
 491         // entity_id + entity_table which are not multilingual.
 492         if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) {
 493           foreach ($locales as $locale) {
 494             $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})";
 495           }
 496         }
 497         else {
 498           $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})";
 499         }
 500       }
 501     }
 502
 503     // run the queries without i18n-rewriting
 504     $dao = new CRM_Core_DAO();
 505     foreach ($queries as $query) {
 506       $dao->query($query, FALSE);
 507     }
 508   }
 509
 510   /**
 511    * Get indexes for tables
 512    * @param array $tables
 513    *   array of table names to find indexes for
 514    *
 515    * @return array('tableName' => array('index1', 'index2'))
 516    */
 517   public static function getIndexes($tables) {
 518     $indexes = array();
 519     foreach ($tables as $table) {
 520       $query = "SHOW INDEX FROM $table";
 521       $dao = CRM_Core_DAO::executeQuery($query);
 522
 523       $tableIndexes = array();
 524       while ($dao->fetch()) {
 525         $tableIndexes[$dao->Key_name]['name'] = $dao->Key_name;
 526         $tableIndexes[$dao->Key_name]['field'][] = $dao->Column_name .
 527          ($dao->Sub_part ? '(' . $dao->Sub_part . ')' : '');
 528         $tableIndexes[$dao->Key_name]['unique'] = ($dao->Non_unique == 0 ? 1 : 0);
 529       }
 530       $indexes[$table] = $tableIndexes;
 531       $dao->free();
 532     }
 533     return $indexes;
 534   }
 535
 536   /**
 537    * Drop an index if one by that name exists.
 538    *
 539    * @param string $tableName
 540    * @param string $indexName
 541    */
 542   public static function dropIndexIfExists($tableName, $indexName) {
 543     if (self::checkIfIndexExists($tableName, $indexName)) {
 544       CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName");
 545     }
 546   }
 547
 548   /**
 549    * @param int $customFieldID
 550    * @param string $tableName
 551    * @param string $columnName
 552    * @param $length
 553    *
 554    * @throws Exception
 555    */
 556   public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
 557     // first update the custom field tables
 558     $sql = "
 559 UPDATE civicrm_custom_field
 560 SET    text_length = %1
 561 WHERE  id = %2
 562 ";
 563     $params = array(
 564       1 => array($length, 'Integer'),
 565       2 => array($customFieldID, 'Integer'),
 566     );
 567     CRM_Core_DAO::executeQuery($sql, $params);
 568
 569     $sql = "
 570 SELECT is_required, default_value
 571 FROM   civicrm_custom_field
 572 WHERE  id = %2
 573 ";
 574     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 575
 576     if ($dao->fetch()) {
 577       $clause = '';
 578
 579       if ($dao->is_required) {
 580         $clause = " NOT NULL";
 581       }
 582
 583       if (!empty($dao->default_value)) {
 584         $clause .= " DEFAULT '{$dao->default_value}'";
 585       }
 586       // now modify the column
 587       $sql = "
 588 ALTER TABLE {$tableName}
 589 MODIFY      {$columnName} varchar( $length )
 590             $clause
 591 ";
 592       CRM_Core_DAO::executeQuery($sql);
 593     }
 594     else {
 595       CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3',
 596         array(
 597           1 => $tableName,
 598           2 => $columnName,
 599           3 => $customFieldID,
 600         )
 601       ));
 602     }
 603   }
 604
 605   /**
 606    * Check if the table has an index matching the name.
 607    *
 608    * @param string $tableName
 609    * @param array $indexName
 610    *
 611    * @return bool
 612    */
 613   public static function checkIfIndexExists($tableName, $indexName) {
 614     $result = CRM_Core_DAO::executeQuery(
 615       "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1",
 616       array(1 => array($indexName, 'String'))
 617     );
 618     if ($result->fetch()) {
 619       return TRUE;
 620     }
 621     return FALSE;
 622   }
 623
 624   /**
 625    * Check if the table has a specified column.
 626    *
 627    * @param string $tableName
 628    * @param string $columnName
 629    *
 630    * @return bool
 631    */
 632   public static function checkIfFieldExists($tableName, $columnName) {
 633     $result = CRM_Core_DAO::executeQuery(
 634       "SHOW COLUMNS FROM $tableName LIKE %1",
 635       array(1 => array($columnName, 'String'))
 636     );
 637     if ($result->fetch()) {
 638       return TRUE;
 639     }
 640     return FALSE;
 641   }
 642
 643   /**
 644    * Check if a foreign key Exists
 645    * @param string $table_name
 646    * @param string $constraint_name
 647    * @return bool TRUE if FK is found
 648    */
 649   public static function checkFKExists($table_name, $constraint_name) {
 650     $config = CRM_Core_Config::singleton();
 651     $dbUf = DB::parseDSN($config->dsn);
 652     $query = "
 653       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 654       WHERE TABLE_SCHEMA = %1
 655       AND TABLE_NAME = %2
 656       AND CONSTRAINT_NAME = %3
 657       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 658     ";
 659     $params = array(
 660       1 => array($dbUf['database'], 'String'),
 661       2 => array($table_name, 'String'),
 662       3 => array($constraint_name, 'String'),
 663     );
 664     $dao = CRM_Core_DAO::executeQuery($query, $params);
 665
 666     if ($dao->fetch()) {
 667       return TRUE;
 668     }
 669     return FALSE;
 670   }
 671
 672   /**
 673    * Remove a foreign key from a table if it exists.
 674    *
 675    * @param $table_name
 676    * @param $constraint_name
 677    *
 678    * @return bool
 679    */
 680   public static function safeRemoveFK($table_name, $constraint_name) {
 681     if (self::checkFKExists($table_name, $constraint_name)) {
 682       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", array());
 683       return TRUE;
 684     }
 685     return FALSE;
 686   }
 687
 688   /**
 689    * Add index signature hash to DAO file calculation.
 690    *
 691    * @param string $table table name
 692    * @param array $indices index array spec
 693    */
 694   public static function addIndexSignature($table, &$indices) {
 695     foreach ($indices as $indexName => $index) {
 696       $indices[$indexName]['sig'] = $table . "::" .
 697         (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" .
 698         implode("::", $index['field']);
 699     }
 700   }
 701
 702   /**
 703    * Compare the indices specified in the XML files with those in the DB.
 704    *
 705    * @param bool $dropFalseIndices
 706    *  If set - this function deletes false indices present in the DB which mismatches the expected
 707    *  values of xml file so that civi re-creates them with correct values using createMissingIndices() function.
 708    *
 709    * @return array
 710    *   index specifications
 711    */
 712   public static function getMissingIndices($dropFalseIndices = FALSE) {
 713     $requiredSigs = $existingSigs = array();
 714     // Get the indices defined (originally) in the xml files
 715     $requiredIndices = CRM_Core_DAO_AllCoreTables::indices();
 716     $reqSigs = array();
 717     foreach ($requiredIndices as $table => $indices) {
 718       $reqSigs[] = CRM_Utils_Array::collect('sig', $indices);
 719     }
 720     CRM_Utils_Array::flatten($reqSigs, $requiredSigs);
 721
 722     // Get the indices in the database
 723     $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices));
 724     $extSigs = array();
 725     foreach ($existingIndices as $table => $indices) {
 726       CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices);
 727       $extSigs[] = CRM_Utils_Array::collect('sig', $indices);
 728     }
 729     CRM_Utils_Array::flatten($extSigs, $existingSigs);
 730
 731     // Compare
 732     $missingSigs = array_diff($requiredSigs, $existingSigs);
 733
 734     //CRM-20774 - Drop index key which exist in db but the value varies.
 735     $existingKeySigs = array_intersect_key($missingSigs, $existingSigs);
 736     if ($dropFalseIndices && !empty($existingKeySigs)) {
 737       foreach ($existingKeySigs as $sig) {
 738         $sigParts = explode('::', $sig);
 739         foreach ($requiredIndices[$sigParts[0]] as $index) {
 740           if ($index['sig'] == $sig && !empty($index['name'])) {
 741             self::dropIndexIfExists($sigParts[0], $index['name']);
 742             continue;
 743           }
 744         }
 745       }
 746     }
 747
 748     // Get missing indices
 749     $missingIndices = array();
 750     foreach ($missingSigs as $sig) {
 751       $sigParts = explode('::', $sig);
 752       if (array_key_exists($sigParts[0], $requiredIndices)) {
 753         foreach ($requiredIndices[$sigParts[0]] as $index) {
 754           if ($index['sig'] == $sig) {
 755             $missingIndices[$sigParts[0]][] = $index;
 756             continue;
 757           }
 758         }
 759       }
 760     }
 761     return $missingIndices;
 762   }
 763
 764   /**
 765    * Create missing indices.
 766    *
 767    * @param array $missingIndices as returned by getMissingIndices()
 768    */
 769   public static function createMissingIndices($missingIndices) {
 770     $queries = array();
 771     foreach ($missingIndices as $table => $indexList) {
 772       foreach ($indexList as $index) {
 773         $queries[] = "CREATE " .
 774         (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') .
 775         "INDEX {$index['name']} ON {$table} (" .
 776           implode(", ", $index['field']) .
 777         ")";
 778       }
 779     }
 780
 781     /* FIXME potential problem if index name already exists, so check before creating */
 782     $dao = new CRM_Core_DAO();
 783     foreach ($queries as $query) {
 784       $dao->query($query, FALSE);
 785     }
 786     $dao->free();
 787   }
 788
 789 }