CRM/Core/BAO/SchemaHandler.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  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    *
 375    */
 376   public static function dropColumn($tableName, $columnName, $l18n = FALSE) {
 377     if (self::checkIfFieldExists($tableName, $columnName)) {
 378       $sql = "ALTER TABLE $tableName DROP COLUMN $columnName";
 379       if ($l18n) {
 380         CRM_Core_DAO::executeQuery($sql);
 381       }
 382       else {
 383         CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
 384       }
 385       $domain = new CRM_Core_DAO_Domain();
 386       $domain->find(TRUE);
 387       if ($domain->locales) {
 388         $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 389         CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL);
 390       }
 391     }
 392   }
 393
 394   /**
 395    * @param string $tableName
 396    * @param bool $dropUnique
 397    */
 398   public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
 399     if ($dropUnique) {
 400       $sql = "ALTER TABLE $tableName
 401 DROP INDEX `unique_entity_id` ,
 402 ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
 403     }
 404     else {
 405       $sql = " ALTER TABLE $tableName
 406 DROP INDEX `FK_{$tableName}_entity_id` ,
 407 ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
 408     }
 409     CRM_Core_DAO::executeQuery($sql);
 410   }
 411
 412   /**
 413    * Create indexes.
 414    *
 415    * @param $tables
 416    *   Tables to create index for in the format:
 417    *     array('civicrm_entity_table' => 'entity_id')
 418    *     OR
 419    *     array('civicrm_entity_table' => array('entity_id', 'entity_table'))
 420    *   The latter will create a combined index on the 2 keys (in order).
 421    *
 422    *  Side note - when creating combined indexes the one with the most variation
 423    *  goes first  - so entity_table always goes after entity_id.
 424    *
 425    *  It probably makes sense to consider more sophisticated options at some point
 426    *  but at the moment this is only being as enhanced as fast as the test is.
 427    *
 428    * @todo add support for length & multilingual on combined keys.
 429    *
 430    * @param string $createIndexPrefix
 431    * @param array $substrLengths
 432    */
 433   public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLengths = array()) {
 434     $queries = array();
 435     $domain = new CRM_Core_DAO_Domain();
 436     $domain->find(TRUE);
 437     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 438
 439     // if we're multilingual, cache the information on internationalised fields
 440     static $columns = NULL;
 441     if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
 442       $columns = CRM_Core_I18n_SchemaStructure::columns();
 443     }
 444
 445     foreach ($tables as $table => $fields) {
 446       $query = "SHOW INDEX FROM $table";
 447       $dao = CRM_Core_DAO::executeQuery($query);
 448
 449       $currentIndexes = array();
 450       while ($dao->fetch()) {
 451         $currentIndexes[] = $dao->Key_name;
 452       }
 453
 454       // now check for all fields if the index exists
 455       foreach ($fields as $field) {
 456         $fieldName = implode('_', (array) $field);
 457
 458         if (is_array($field)) {
 459           // No support for these for combined indexes as yet - add a test when you
 460           // want to add that.
 461           $lengthName = '';
 462           $lengthSize = '';
 463         }
 464         else {
 465           // handle indices over substrings, CRM-6245
 466           // $lengthName is appended to index name, $lengthSize is the field size modifier
 467           $lengthName = isset($substrLengths[$table][$fieldName]) ? "_{$substrLengths[$table][$fieldName]}" : '';
 468           $lengthSize = isset($substrLengths[$table][$fieldName]) ? "({$substrLengths[$table][$fieldName]})" : '';
 469         }
 470
 471         $names = array(
 472           "index_{$fieldName}{$lengthName}",
 473           "FK_{$table}_{$fieldName}{$lengthName}",
 474           "UI_{$fieldName}{$lengthName}",
 475           "{$createIndexPrefix}_{$fieldName}{$lengthName}",
 476         );
 477
 478         // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
 479         foreach ($names as $name) {
 480           $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
 481           if (preg_grep($regex, $currentIndexes)) {
 482             continue 2;
 483           }
 484         }
 485
 486         // the index doesn't exist, so create it
 487         // if we're multilingual and the field is internationalised, do it for every locale
 488         // @todo remove is_array check & add multilingual support for combined indexes and add a test.
 489         // Note combined indexes currently using this function are on fields like
 490         // entity_id + entity_table which are not multilingual.
 491         if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) {
 492           foreach ($locales as $locale) {
 493             $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})";
 494           }
 495         }
 496         else {
 497           $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})";
 498         }
 499       }
 500     }
 501
 502     // run the queries without i18n-rewriting
 503     $dao = new CRM_Core_DAO();
 504     foreach ($queries as $query) {
 505       $dao->query($query, FALSE);
 506     }
 507   }
 508
 509   /**
 510    * Get indexes for tables
 511    * @param array $tables
 512    *   array of table names to find indexes for
 513    *
 514    * @return array('tableName' => array('index1', 'index2'))
 515    */
 516   public static function getIndexes($tables) {
 517     $indexes = array();
 518     foreach ($tables as $table) {
 519       $query = "SHOW INDEX FROM $table";
 520       $dao = CRM_Core_DAO::executeQuery($query);
 521
 522       $tableIndexes = array();
 523       while ($dao->fetch()) {
 524         $tableIndexes[$dao->Key_name]['name'] = $dao->Key_name;
 525         $tableIndexes[$dao->Key_name]['field'][] = $dao->Column_name .
 526          ($dao->Sub_part ? '(' . $dao->Sub_part . ')' : '');
 527         $tableIndexes[$dao->Key_name]['unique'] = ($dao->Non_unique == 0 ? 1 : 0);
 528       }
 529       $indexes[$table] = $tableIndexes;
 530       $dao->free();
 531     }
 532     return $indexes;
 533   }
 534
 535   /**
 536    * Drop an index if one by that name exists.
 537    *
 538    * @param string $tableName
 539    * @param string $indexName
 540    */
 541   public static function dropIndexIfExists($tableName, $indexName) {
 542     if (self::checkIfIndexExists($tableName, $indexName)) {
 543       CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName");
 544     }
 545   }
 546
 547   /**
 548    * @param int $customFieldID
 549    * @param string $tableName
 550    * @param string $columnName
 551    * @param $length
 552    *
 553    * @throws Exception
 554    */
 555   public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
 556     // first update the custom field tables
 557     $sql = "
 558 UPDATE civicrm_custom_field
 559 SET    text_length = %1
 560 WHERE  id = %2
 561 ";
 562     $params = array(
 563       1 => array($length, 'Integer'),
 564       2 => array($customFieldID, 'Integer'),
 565     );
 566     CRM_Core_DAO::executeQuery($sql, $params);
 567
 568     $sql = "
 569 SELECT is_required, default_value
 570 FROM   civicrm_custom_field
 571 WHERE  id = %2
 572 ";
 573     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 574
 575     if ($dao->fetch()) {
 576       $clause = '';
 577
 578       if ($dao->is_required) {
 579         $clause = " NOT NULL";
 580       }
 581
 582       if (!empty($dao->default_value)) {
 583         $clause .= " DEFAULT '{$dao->default_value}'";
 584       }
 585       // now modify the column
 586       $sql = "
 587 ALTER TABLE {$tableName}
 588 MODIFY      {$columnName} varchar( $length )
 589             $clause
 590 ";
 591       CRM_Core_DAO::executeQuery($sql);
 592     }
 593     else {
 594       CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3',
 595         array(
 596           1 => $tableName,
 597           2 => $columnName,
 598           3 => $customFieldID,
 599         )
 600       ));
 601     }
 602   }
 603
 604   /**
 605    * Check if the table has an index matching the name.
 606    *
 607    * @param string $tableName
 608    * @param array $indexName
 609    *
 610    * @return bool
 611    */
 612   public static function checkIfIndexExists($tableName, $indexName) {
 613     $result = CRM_Core_DAO::executeQuery(
 614       "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1",
 615       array(1 => array($indexName, 'String'))
 616     );
 617     if ($result->fetch()) {
 618       return TRUE;
 619     }
 620     return FALSE;
 621   }
 622
 623   /**
 624    * Check if the table has a specified column.
 625    *
 626    * @param string $tableName
 627    * @param string $columnName
 628    *
 629    * @return bool
 630    */
 631   public static function checkIfFieldExists($tableName, $columnName) {
 632     $result = CRM_Core_DAO::executeQuery(
 633       "SHOW COLUMNS FROM $tableName LIKE %1",
 634       array(1 => array($columnName, 'String'))
 635     );
 636     if ($result->fetch()) {
 637       return TRUE;
 638     }
 639     return FALSE;
 640   }
 641
 642   /**
 643    * Check if a foreign key Exists
 644    * @param string $table_name
 645    * @param string $constraint_name
 646    * @return bool TRUE if FK is found
 647    */
 648   public static function checkFKExists($table_name, $constraint_name) {
 649     $config = CRM_Core_Config::singleton();
 650     $dbUf = DB::parseDSN($config->dsn);
 651     $query = "
 652       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 653       WHERE TABLE_SCHEMA = %1
 654       AND TABLE_NAME = %2
 655       AND CONSTRAINT_NAME = %3
 656       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 657     ";
 658     $params = array(
 659       1 => array($dbUf['database'], 'String'),
 660       2 => array($table_name, 'String'),
 661       3 => array($constraint_name, 'String'),
 662     );
 663     $dao = CRM_Core_DAO::executeQuery($query, $params);
 664
 665     if ($dao->fetch()) {
 666       return TRUE;
 667     }
 668     return FALSE;
 669   }
 670
 671   /**
 672    * Remove a foreign key from a table if it exists.
 673    *
 674    * @param $table_name
 675    * @param $constraint_name
 676    *
 677    * @return bool
 678    */
 679   public static function safeRemoveFK($table_name, $constraint_name) {
 680     if (self::checkFKExists($table_name, $constraint_name)) {
 681       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", array());
 682       return TRUE;
 683     }
 684     return FALSE;
 685   }
 686
 687   /**
 688    * Add index signature hash to DAO file calculation.
 689    *
 690    * @param string $table table name
 691    * @param array $indices index array spec
 692    */
 693   public static function addIndexSignature($table, &$indices) {
 694     foreach ($indices as $indexName => $index) {
 695       $indices[$indexName]['sig'] = $table . "::" .
 696         (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" .
 697         implode("::", $index['field']);
 698     }
 699   }
 700
 701   /**
 702    * Compare the indices specified in the XML files with those in the DB.
 703    *
 704    * @return array
 705    *   index specifications
 706    */
 707   public static function getMissingIndices() {
 708     $requiredSigs = $existingSigs = array();
 709     // Get the indices defined (originally) in the xml files
 710     $requiredIndices = CRM_Core_DAO_AllCoreTables::indices();
 711     foreach ($requiredIndices as $table => $indices) {
 712       $reqSigs[] = CRM_Utils_Array::collect('sig', $indices);
 713     }
 714     CRM_Utils_Array::flatten($reqSigs, $requiredSigs);
 715
 716     // Get the indices in the database
 717     $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices));
 718     foreach ($existingIndices as $table => $indices) {
 719       CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices);
 720       $extSigs[] = CRM_Utils_Array::collect('sig', $indices);
 721     }
 722     CRM_Utils_Array::flatten($extSigs, $existingSigs);
 723
 724     // Compare
 725     $missingSigs = array_diff($requiredSigs, $existingSigs);
 726     // Get missing indices
 727     $missingIndices = array();
 728     foreach ($missingSigs as $sig) {
 729       $sigParts = explode('::', $sig);
 730       foreach ($requiredIndices[$sigParts[0]] as $index) {
 731         if ($index['sig'] == $sig) {
 732           $missingIndices[$sigParts[0]][] = $index;
 733           continue;
 734         }
 735       }
 736     }
 737     return $missingIndices;
 738   }
 739
 740   /**
 741    * Create missing indices.
 742    *
 743    * @param array $missingIndices as returned by getMissingIndices()
 744    */
 745   public static function createMissingIndices($missingIndices) {
 746     $queries = array();
 747     foreach ($missingIndices as $table => $indexList) {
 748       foreach ($indexList as $index) {
 749         $queries[] = "CREATE " .
 750         (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') .
 751         "INDEX {$index['name']} ON {$table} (" .
 752           implode(", ", $index['field']) .
 753         ")";
 754       }
 755     }
 756
 757     /* FIXME potential problem if index name already exists, so check before creating */
 758     $dao = new CRM_Core_DAO();
 759     foreach ($queries as $query) {
 760       $dao->query($query, FALSE);
 761     }
 762     $dao->free();
 763   }
 764
 765 }