CRM/Core/BAO/SchemaHandler.php

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