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 string $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     return (bool) $dao->fetch();
 589   }
 590
 591   /**
 592    * Check if a foreign key Exists
 593    * @param string $table_name
 594    * @param string $constraint_name
 595    * @return bool TRUE if FK is found
 596    */
 597   public static function checkFKExists($table_name, $constraint_name) {
 598     $config = CRM_Core_Config::singleton();
 599     $dbUf = DB::parseDSN($config->dsn);
 600     $query = "
 601       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 602       WHERE TABLE_SCHEMA = %1
 603       AND TABLE_NAME = %2
 604       AND CONSTRAINT_NAME = %3
 605       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 606     ";
 607     $params = [
 608       1 => [$dbUf['database'], 'String'],
 609       2 => [$table_name, 'String'],
 610       3 => [$constraint_name, 'String'],
 611     ];
 612     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 613
 614     if ($dao->fetch()) {
 615       return TRUE;
 616     }
 617     return FALSE;
 618   }
 619
 620   /**
 621    * Remove a foreign key from a table if it exists.
 622    *
 623    * @param $table_name
 624    * @param $constraint_name
 625    *
 626    * @return bool
 627    */
 628   public static function safeRemoveFK($table_name, $constraint_name) {
 629     if (self::checkFKExists($table_name, $constraint_name)) {
 630       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", [], TRUE, NULL, FALSE, FALSE);
 631       return TRUE;
 632     }
 633     return FALSE;
 634   }
 635
 636   /**
 637    * Add index signature hash to DAO file calculation.
 638    *
 639    * @param string $table table name
 640    * @param array $indices index array spec
 641    */
 642   public static function addIndexSignature($table, &$indices) {
 643     foreach ($indices as $indexName => $index) {
 644       $indices[$indexName]['sig'] = $table . "::" .
 645         (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" .
 646         implode("::", $index['field']);
 647     }
 648   }
 649
 650   /**
 651    * Compare the indices specified in the XML files with those in the DB.
 652    *
 653    * @param bool $dropFalseIndices
 654    *  If set - this function deletes false indices present in the DB which mismatches the expected
 655    *  values of xml file so that civi re-creates them with correct values using createMissingIndices() function.
 656    * @param array|FALSE $tables
 657    *   An optional array of tables - if provided the results will be restricted to these tables.
 658    *
 659    * @return array
 660    *   index specifications
 661    */
 662   public static function getMissingIndices($dropFalseIndices = FALSE, $tables = FALSE) {
 663     $requiredSigs = $existingSigs = [];
 664     // Get the indices defined (originally) in the xml files
 665     $requiredIndices = CRM_Core_DAO_AllCoreTables::indices();
 666     $reqSigs = [];
 667     if ($tables !== FALSE) {
 668       $requiredIndices = array_intersect_key($requiredIndices, array_fill_keys($tables, TRUE));
 669     }
 670     foreach ($requiredIndices as $table => $indices) {
 671       $reqSigs[] = CRM_Utils_Array::collect('sig', $indices);
 672     }
 673     CRM_Utils_Array::flatten($reqSigs, $requiredSigs);
 674
 675     // Get the indices in the database
 676     $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices));
 677     $extSigs = [];
 678     foreach ($existingIndices as $table => $indices) {
 679       CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices);
 680       $extSigs[] = CRM_Utils_Array::collect('sig', $indices);
 681     }
 682     CRM_Utils_Array::flatten($extSigs, $existingSigs);
 683
 684     // Compare
 685     $missingSigs = array_diff($requiredSigs, $existingSigs);
 686
 687     //CRM-20774 - Drop index key which exist in db but the value varies.
 688     $existingKeySigs = array_intersect_key($missingSigs, $existingSigs);
 689     if ($dropFalseIndices && !empty($existingKeySigs)) {
 690       foreach ($existingKeySigs as $sig) {
 691         $sigParts = explode('::', $sig);
 692         foreach ($requiredIndices[$sigParts[0]] as $index) {
 693           if ($index['sig'] == $sig && !empty($index['name'])) {
 694             self::dropIndexIfExists($sigParts[0], $index['name']);
 695             continue;
 696           }
 697         }
 698       }
 699     }
 700
 701     // Get missing indices
 702     $missingIndices = [];
 703     foreach ($missingSigs as $sig) {
 704       $sigParts = explode('::', $sig);
 705       if (array_key_exists($sigParts[0], $requiredIndices)) {
 706         foreach ($requiredIndices[$sigParts[0]] as $index) {
 707           if ($index['sig'] == $sig) {
 708             $missingIndices[$sigParts[0]][] = $index;
 709             continue;
 710           }
 711         }
 712       }
 713     }
 714     return $missingIndices;
 715   }
 716
 717   /**
 718    * Create missing indices.
 719    *
 720    * @param array $missingIndices as returned by getMissingIndices()
 721    */
 722   public static function createMissingIndices($missingIndices) {
 723     $queries = [];
 724     foreach ($missingIndices as $table => $indexList) {
 725       foreach ($indexList as $index) {
 726         $queries[] = "CREATE " .
 727         (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') .
 728         "INDEX {$index['name']} ON {$table} (" .
 729           implode(", ", $index['field']) .
 730         ")";
 731       }
 732     }
 733
 734     /* FIXME potential problem if index name already exists, so check before creating */
 735     $dao = new CRM_Core_DAO();
 736     foreach ($queries as $query) {
 737       $dao->query($query, FALSE);
 738     }
 739   }
 740
 741   /**
 742    * Build the sql to alter the field.
 743    *
 744    * @param array $params
 745    * @param bool $indexExist
 746    *
 747    * @return string
 748    */
 749   public static function buildFieldChangeSql($params, $indexExist) {
 750     $sql = str_repeat(' ', 8);
 751     $sql .= "ALTER TABLE {$params['table_name']}";
 752     return $sql . self::getFieldAlterSQL($params, $indexExist);
 753   }
 754
 755   /**
 756    * Get the sql to alter an individual field.
 757    *
 758    * This will need to have an ALTER TABLE statement appended but by getting
 759    * by individual field we can do one or many.
 760    *
 761    * @param array $params
 762    * @param bool $indexExist
 763    *
 764    * @return string
 765    */
 766   public static function getFieldAlterSQL($params, $indexExist) {
 767     $sql = '';
 768     switch ($params['operation']) {
 769       case 'add':
 770         $separator = "\n";
 771         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 772         $separator = ",\n";
 773         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 774         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 775         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 776         break;
 777
 778       case 'modify':
 779         $separator = "\n";
 780         $sql .= self::buildFieldSQL($params, $separator, "MODIFY ");
 781         $separator = ",\n";
 782         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
 783         break;
 784
 785       case 'delete':
 786         $sql .= " DROP COLUMN `{$params['name']}`";
 787         if (!empty($params['primary'])) {
 788           $sql .= ", DROP PRIMARY KEY";
 789         }
 790         if (!empty($params['fk_table_name'])) {
 791           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 792         }
 793         break;
 794     }
 795     return $sql;
 796   }
 797
 798   /**
 799    * Performs the utf8mb4 migration.
 800    *
 801    * @param bool $revert
 802    *   Being able to revert if primarily for unit testing.
 803    *
 804    * @return bool
 805    */
 806   public static function migrateUtf8mb4($revert = FALSE) {
 807     $newCharSet = $revert ? 'utf8' : 'utf8mb4';
 808     $newCollation = $revert ? 'utf8_unicode_ci' : 'utf8mb4_unicode_ci';
 809     $newBinaryCollation = $revert ? 'utf8_bin' : 'utf8mb4_bin';
 810     $tables = [];
 811     $dao = new CRM_Core_DAO();
 812     $database = $dao->_database;
 813     CRM_Core_DAO::executeQuery("ALTER DATABASE $database CHARACTER SET = $newCharSet COLLATE = $newCollation");
 814     $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS WHERE Engine = 'InnoDB' AND Name LIKE 'civicrm\_%'");
 815     while ($dao->fetch()) {
 816       $tables[$dao->Name] = [
 817         'Engine' => $dao->Engine,
 818       ];
 819     }
 820     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
 821     $logging_database = $dsn['database'];
 822     $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `$logging_database` WHERE Engine <> 'MyISAM' AND Name LIKE 'log\_civicrm\_%'");
 823     while ($dao->fetch()) {
 824       $tables["$logging_database.{$dao->Name}"] = [
 825         'Engine' => $dao->Engine,
 826       ];
 827     }
 828     foreach ($tables as $table => $param) {
 829       $query = "ALTER TABLE $table";
 830       $dao = CRM_Core_DAO::executeQuery("SHOW FULL COLUMNS FROM $table", [], TRUE, NULL, FALSE, FALSE);
 831       $index = 0;
 832       $params = [];
 833       $tableCollation = $newCollation;
 834       while ($dao->fetch()) {
 835         if (!$dao->Collation || $dao->Collation === $newCollation || $dao->Collation === $newBinaryCollation) {
 836           continue;
 837         }
 838         if (strpos($dao->Collation, 'utf8') !== 0) {
 839           continue;
 840         }
 841
 842         if (strpos($dao->Collation, '_bin') !== FALSE) {
 843           $tableCollation = $newBinaryCollation;
 844         }
 845         else {
 846           $tableCollation = $newCollation;
 847         }
 848         if ($dao->Null === 'YES') {
 849           $null = 'NULL';
 850         }
 851         else {
 852           $null = 'NOT NULL';
 853         }
 854         $default = '';
 855         if ($dao->Default !== NULL) {
 856           $index++;
 857           $default = "DEFAULT %$index";
 858           $params[$index] = [$dao->Default, 'String'];
 859         }
 860         elseif ($dao->Null === 'YES') {
 861           $default = 'DEFAULT NULL';
 862         }
 863         $index++;
 864         $params[$index] = [$dao->Comment, 'String'];
 865         $query .= " MODIFY `{$dao->Field}` {$dao->Type} CHARACTER SET $newCharSet COLLATE $tableCollation $null $default {$dao->Extra} COMMENT %$index,";
 866       }
 867       $query .= " CHARACTER SET = $newCharSet COLLATE = $tableCollation";
 868       if ($param['Engine'] === 'InnoDB') {
 869         $query .= ' ROW_FORMAT = Dynamic';
 870       }
 871       // Disable i18n rewrite.
 872       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 873     }
 874     return TRUE;
 875   }
 876
 877   /**
 878    * Get the database collation.
 879    *
 880    * @return string
 881    */
 882   public static function getDBCollation() {
 883     return CRM_Core_DAO::singleValueQuery('SELECT @@collation_database');
 884   }
 885
 886   /**
 887    * Get the database collation.
 888    *
 889    * @return string
 890    */
 891   public static function getDBCharset() {
 892     return CRM_Core_DAO::singleValueQuery('SELECT @@character_set_database');
 893   }
 894
 895 }