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