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       $locales = CRM_Core_I18n::getMultilingual();
 339       if ($locales) {
 340         CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL, $isUpgradeMode);
 341       }
 342     }
 343   }
 344
 345   /**
 346    * @param string $tableName
 347    * @param bool $dropUnique
 348    */
 349   public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
 350     if ($dropUnique) {
 351       $sql = "ALTER TABLE $tableName
 352 DROP INDEX `unique_entity_id` ,
 353 ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
 354     }
 355     else {
 356       $sql = " ALTER TABLE $tableName
 357 DROP INDEX `FK_{$tableName}_entity_id` ,
 358 ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
 359     }
 360     CRM_Core_DAO::executeQuery($sql);
 361   }
 362
 363   /**
 364    * Create indexes.
 365    *
 366    * @param $tables
 367    *   Tables to create index for in the format:
 368    *     ['civicrm_entity_table' => ['entity_id']]
 369    *     OR
 370    *     array['civicrm_entity_table' => array['entity_id', 'entity_table']]
 371    *   The latter will create a combined index on the 2 keys (in order).
 372    *
 373    *  Side note - when creating combined indexes the one with the most variation
 374    *  goes first  - so entity_table always goes after entity_id.
 375    *
 376    *  It probably makes sense to consider more sophisticated options at some point
 377    *  but at the moment this is only being as enhanced as fast as the test is.
 378    *
 379    * @todo add support for length & multilingual on combined keys.
 380    *
 381    * @param string $createIndexPrefix
 382    * @param array $substrLengths
 383    */
 384   public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLengths = []) {
 385     $queries = [];
 386     $locales = CRM_Core_I18n::getMultilingual();
 387
 388     // if we're multilingual, cache the information on internationalised fields
 389     static $columns = NULL;
 390     if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
 391       $columns = CRM_Core_I18n_SchemaStructure::columns();
 392     }
 393
 394     foreach ($tables as $table => $fields) {
 395       $query = "SHOW INDEX FROM $table";
 396       $dao = CRM_Core_DAO::executeQuery($query);
 397
 398       $currentIndexes = [];
 399       while ($dao->fetch()) {
 400         $currentIndexes[] = $dao->Key_name;
 401       }
 402
 403       // now check for all fields if the index exists
 404       foreach ($fields as $field) {
 405         $fieldName = implode('_', (array) $field);
 406
 407         if (is_array($field)) {
 408           // No support for these for combined indexes as yet - add a test when you
 409           // want to add that.
 410           $lengthName = '';
 411           $lengthSize = '';
 412         }
 413         else {
 414           // handle indices over substrings, CRM-6245
 415           // $lengthName is appended to index name, $lengthSize is the field size modifier
 416           $lengthName = isset($substrLengths[$table][$fieldName]) ? "_{$substrLengths[$table][$fieldName]}" : '';
 417           $lengthSize = isset($substrLengths[$table][$fieldName]) ? "({$substrLengths[$table][$fieldName]})" : '';
 418         }
 419
 420         $names = [
 421           "index_{$fieldName}{$lengthName}",
 422           "FK_{$table}_{$fieldName}{$lengthName}",
 423           "UI_{$fieldName}{$lengthName}",
 424           "{$createIndexPrefix}_{$fieldName}{$lengthName}",
 425         ];
 426
 427         // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
 428         foreach ($names as $name) {
 429           $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
 430           if (preg_grep($regex, $currentIndexes)) {
 431             continue 2;
 432           }
 433         }
 434
 435         // the index doesn't exist, so create it
 436         // if we're multilingual and the field is internationalised, do it for every locale
 437         // @todo remove is_array check & add multilingual support for combined indexes and add a test.
 438         // Note combined indexes currently using this function are on fields like
 439         // entity_id + entity_table which are not multilingual.
 440         if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) {
 441           foreach ($locales as $locale) {
 442             $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})";
 443           }
 444         }
 445         else {
 446           $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})";
 447         }
 448       }
 449     }
 450
 451     // run the queries without i18n-rewriting
 452     $dao = new CRM_Core_DAO();
 453     foreach ($queries as $query) {
 454       $dao->query($query, FALSE);
 455     }
 456   }
 457
 458   /**
 459    * Get indexes for tables
 460    * @param array $tables
 461    *   array of table names to find indexes for
 462    *
 463    * @return array('tableName' => array('index1', 'index2'))
 464    */
 465   public static function getIndexes($tables) {
 466     $indexes = [];
 467     foreach ($tables as $table) {
 468       $query = "SHOW INDEX FROM $table";
 469       $dao = CRM_Core_DAO::executeQuery($query);
 470
 471       $tableIndexes = [];
 472       while ($dao->fetch()) {
 473         $tableIndexes[$dao->Key_name]['name'] = $dao->Key_name;
 474         $tableIndexes[$dao->Key_name]['field'][] = $dao->Column_name .
 475          ($dao->Sub_part ? '(' . $dao->Sub_part . ')' : '');
 476         $tableIndexes[$dao->Key_name]['unique'] = ($dao->Non_unique == 0 ? 1 : 0);
 477       }
 478       $indexes[$table] = $tableIndexes;
 479     }
 480     return $indexes;
 481   }
 482
 483   /**
 484    * Drop an index if one by that name exists.
 485    *
 486    * @param string $tableName
 487    * @param string $indexName
 488    */
 489   public static function dropIndexIfExists($tableName, $indexName) {
 490     if (self::checkIfIndexExists($tableName, $indexName)) {
 491       CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName");
 492     }
 493   }
 494
 495   /**
 496    * @param int $customFieldID
 497    * @param string $tableName
 498    * @param string $columnName
 499    * @param $length
 500    *
 501    * @throws CRM_Core_Exception
 502    */
 503   public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
 504     // first update the custom field tables
 505     $sql = "
 506 UPDATE civicrm_custom_field
 507 SET    text_length = %1
 508 WHERE  id = %2
 509 ";
 510     $params = [
 511       1 => [$length, 'Integer'],
 512       2 => [$customFieldID, 'Integer'],
 513     ];
 514     CRM_Core_DAO::executeQuery($sql, $params);
 515
 516     $sql = "
 517 SELECT is_required, default_value
 518 FROM   civicrm_custom_field
 519 WHERE  id = %2
 520 ";
 521     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 522
 523     if ($dao->fetch()) {
 524       $clause = '';
 525
 526       if ($dao->is_required) {
 527         $clause = " NOT NULL";
 528       }
 529
 530       if (!empty($dao->default_value)) {
 531         $clause .= " DEFAULT '{$dao->default_value}'";
 532       }
 533       // now modify the column
 534       $sql = "
 535 ALTER TABLE {$tableName}
 536 MODIFY      {$columnName} varchar( $length )
 537             $clause
 538 ";
 539       CRM_Core_DAO::executeQuery($sql);
 540     }
 541     else {
 542       throw new CRM_Core_Exception(ts('Could Not Find Custom Field Details for %1, %2, %3',
 543         [
 544           1 => $tableName,
 545           2 => $columnName,
 546           3 => $customFieldID,
 547         ]
 548       ));
 549     }
 550   }
 551
 552   /**
 553    * Check if the table has an index matching the name.
 554    *
 555    * @param string $tableName
 556    * @param string $indexName
 557    *
 558    * @return bool
 559    */
 560   public static function checkIfIndexExists($tableName, $indexName) {
 561     $result = CRM_Core_DAO::executeQuery(
 562       "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1",
 563       [1 => [$indexName, 'String']]
 564     );
 565     if ($result->fetch()) {
 566       return TRUE;
 567     }
 568     return FALSE;
 569   }
 570
 571   /**
 572    * Check if the table has a specified column.
 573    *
 574    * @param string $tableName
 575    * @param string $columnName
 576    * @param bool $i18nRewrite
 577    *   Whether to rewrite the query on multilingual setups.
 578    *
 579    * @return bool
 580    */
 581   public static function checkIfFieldExists($tableName, $columnName, $i18nRewrite = TRUE) {
 582     $query = "SHOW COLUMNS FROM $tableName LIKE '%1'";
 583     $dao = CRM_Core_DAO::executeQuery($query, [1 => [$columnName, 'Alphanumeric']], TRUE, NULL, FALSE, $i18nRewrite);
 584     return (bool) $dao->fetch();
 585   }
 586
 587   /**
 588    * Check if a foreign key Exists
 589    * @param string $table_name
 590    * @param string $constraint_name
 591    * @return bool TRUE if FK is found
 592    */
 593   public static function checkFKExists($table_name, $constraint_name) {
 594     $config = CRM_Core_Config::singleton();
 595     $dsn = CRM_Utils_SQL::autoSwitchDSN($config->dsn);
 596     $dbUf = DB::parseDSN($dsn);
 597     $query = "
 598       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 599       WHERE TABLE_SCHEMA = %1
 600       AND TABLE_NAME = %2
 601       AND CONSTRAINT_NAME = %3
 602       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 603     ";
 604     $params = [
 605       1 => [$dbUf['database'], 'String'],
 606       2 => [$table_name, 'String'],
 607       3 => [$constraint_name, 'String'],
 608     ];
 609     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 610
 611     if ($dao->fetch()) {
 612       return TRUE;
 613     }
 614     return FALSE;
 615   }
 616
 617   /**
 618    * Remove a foreign key from a table if it exists.
 619    *
 620    * @param $table_name
 621    * @param $constraint_name
 622    *
 623    * @return bool
 624    */
 625   public static function safeRemoveFK($table_name, $constraint_name) {
 626     if (self::checkFKExists($table_name, $constraint_name)) {
 627       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", [], TRUE, NULL, FALSE, FALSE);
 628       return TRUE;
 629     }
 630     return FALSE;
 631   }
 632
 633   /**
 634    * Add index signature hash to DAO file calculation.
 635    *
 636    * @param string $table table name
 637    * @param array $indices index array spec
 638    */
 639   public static function addIndexSignature($table, &$indices) {
 640     foreach ($indices as $indexName => $index) {
 641       $indices[$indexName]['sig'] = $table . "::" .
 642         (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" .
 643         implode("::", $index['field']);
 644     }
 645   }
 646
 647   /**
 648    * Compare the indices specified in the XML files with those in the DB.
 649    *
 650    * @param bool $dropFalseIndices
 651    *  If set - this function deletes false indices present in the DB which mismatches the expected
 652    *  values of xml file so that civi re-creates them with correct values using createMissingIndices() function.
 653    * @param array|FALSE $tables
 654    *   An optional array of tables - if provided the results will be restricted to these tables.
 655    *
 656    * @return array
 657    *   index specifications
 658    */
 659   public static function getMissingIndices($dropFalseIndices = FALSE, $tables = FALSE) {
 660     $requiredSigs = $existingSigs = [];
 661     // Get the indices defined (originally) in the xml files
 662     $requiredIndices = CRM_Core_DAO_AllCoreTables::indices();
 663     $reqSigs = [];
 664     if ($tables !== FALSE) {
 665       $requiredIndices = array_intersect_key($requiredIndices, array_fill_keys($tables, TRUE));
 666     }
 667     foreach ($requiredIndices as $table => $indices) {
 668       $reqSigs[] = CRM_Utils_Array::collect('sig', $indices);
 669     }
 670     CRM_Utils_Array::flatten($reqSigs, $requiredSigs);
 671
 672     // Get the indices in the database
 673     $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices));
 674     $extSigs = [];
 675     foreach ($existingIndices as $table => $indices) {
 676       CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices);
 677       $extSigs[] = CRM_Utils_Array::collect('sig', $indices);
 678     }
 679     CRM_Utils_Array::flatten($extSigs, $existingSigs);
 680
 681     // Compare
 682     $missingSigs = array_diff($requiredSigs, $existingSigs);
 683
 684     //CRM-20774 - Drop index key which exist in db but the value varies.
 685     $existingKeySigs = array_intersect_key($missingSigs, $existingSigs);
 686     if ($dropFalseIndices && !empty($existingKeySigs)) {
 687       foreach ($existingKeySigs as $sig) {
 688         $sigParts = explode('::', $sig);
 689         foreach ($requiredIndices[$sigParts[0]] as $index) {
 690           if ($index['sig'] == $sig && !empty($index['name'])) {
 691             self::dropIndexIfExists($sigParts[0], $index['name']);
 692             continue;
 693           }
 694         }
 695       }
 696     }
 697
 698     // Get missing indices
 699     $missingIndices = [];
 700     foreach ($missingSigs as $sig) {
 701       $sigParts = explode('::', $sig);
 702       if (array_key_exists($sigParts[0], $requiredIndices)) {
 703         foreach ($requiredIndices[$sigParts[0]] as $index) {
 704           if ($index['sig'] == $sig) {
 705             $missingIndices[$sigParts[0]][] = $index;
 706             continue;
 707           }
 708         }
 709       }
 710     }
 711     return $missingIndices;
 712   }
 713
 714   /**
 715    * Create missing indices.
 716    *
 717    * @param array $missingIndices as returned by getMissingIndices()
 718    */
 719   public static function createMissingIndices($missingIndices) {
 720     $queries = [];
 721     foreach ($missingIndices as $table => $indexList) {
 722       foreach ($indexList as $index) {
 723         $queries[] = "CREATE " .
 724         (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') .
 725         "INDEX {$index['name']} ON {$table} (" .
 726           implode(", ", $index['field']) .
 727         ")";
 728       }
 729     }
 730
 731     /* FIXME potential problem if index name already exists, so check before creating */
 732     $dao = new CRM_Core_DAO();
 733     foreach ($queries as $query) {
 734       $dao->query($query, FALSE);
 735     }
 736   }
 737
 738   /**
 739    * Build the sql to alter the field.
 740    *
 741    * @param array $params
 742    * @param bool $indexExist
 743    *
 744    * @return string
 745    */
 746   public static function buildFieldChangeSql($params, $indexExist) {
 747     $sql = str_repeat(' ', 8);
 748     $sql .= "ALTER TABLE {$params['table_name']}";
 749     return $sql . self::getFieldAlterSQL($params, $indexExist);
 750   }
 751
 752   /**
 753    * Get the sql to alter an individual field.
 754    *
 755    * This will need to have an ALTER TABLE statement appended but by getting
 756    * by individual field we can do one or many.
 757    *
 758    * @param array $params
 759    * @param bool $indexExist
 760    *
 761    * @return string
 762    */
 763   public static function getFieldAlterSQL($params, $indexExist) {
 764     $sql = '';
 765     switch ($params['operation']) {
 766       case 'add':
 767         $separator = "\n";
 768         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 769         $separator = ",\n";
 770         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 771         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 772         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 773         break;
 774
 775       case 'modify':
 776         $separator = "\n";
 777         $sql .= self::buildFieldSQL($params, $separator, "MODIFY ");
 778         $separator = ",\n";
 779         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
 780         break;
 781
 782       case 'delete':
 783         $sql .= " DROP COLUMN `{$params['name']}`";
 784         if (!empty($params['primary'])) {
 785           $sql .= ", DROP PRIMARY KEY";
 786         }
 787         if (!empty($params['fk_table_name'])) {
 788           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 789         }
 790         break;
 791     }
 792     return $sql;
 793   }
 794
 795   /**
 796    * Performs the utf8mb4 migration.
 797    *
 798    * @param bool $revert
 799    *   Being able to revert if primarily for unit testing.
 800    * @param array $patterns
 801    *   Defaults to ['civicrm\_%'] but can be overridden to specify any pattern. eg ['civicrm\_%', 'civi%\_%', 'veda%\_%'].
 802    * @param array $databaseList
 803    *   Allows you to specify an alternative database to the configured CiviCRM database.
 804    *
 805    * @return bool
 806    */
 807   public static function migrateUtf8mb4($revert = FALSE, $patterns = ['civicrm\_%'], $databaseList = NULL) {
 808     $newCharSet = $revert ? 'utf8' : 'utf8mb4';
 809     $newCollation = $revert ? 'utf8_unicode_ci' : 'utf8mb4_unicode_ci';
 810     $newBinaryCollation = $revert ? 'utf8_bin' : 'utf8mb4_bin';
 811     $tables = [];
 812     $dao = new CRM_Core_DAO();
 813     $databases = $databaseList ?? [$dao->_database];
 814
 815     $tableNameLikePatterns = [];
 816     $logTableNameLikePatterns = [];
 817
 818     foreach ($patterns as $pattern) {
 819       $pattern = CRM_Utils_Type::escape($pattern, 'String');
 820       $tableNameLikePatterns[] = "Name LIKE '{$pattern}'";
 821       $logTableNameLikePatterns[] = "Name LIKE 'log\_{$pattern}'";
 822     }
 823
 824     foreach ($databases as $database) {
 825       CRM_Core_DAO::executeQuery("ALTER DATABASE $database CHARACTER SET = $newCharSet COLLATE = $newCollation");
 826       $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$database}` WHERE Engine = 'InnoDB' AND (" . implode(' OR ', $tableNameLikePatterns) . ")");
 827       while ($dao->fetch()) {
 828         $tables["{$database}.{$dao->Name}"] = [
 829           'Engine' => $dao->Engine,
 830         ];
 831       }
 832     }
 833     // If we specified a list of databases assume the user knows what they are doing.
 834     // If they specify the database they should also specify the pattern.
 835     if (!$databaseList) {
 836       $dsn = defined('CIVICRM_LOGGING_DSN') ? CRM_Utils_SQL::autoSwitchDSN(CIVICRM_LOGGING_DSN) : CRM_Utils_SQL::autoSwitchDSN(CIVICRM_DSN);
 837       $dsn = DB::parseDSN($dsn);
 838       $logging_database = $dsn['database'];
 839       $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$logging_database}` WHERE Engine <> 'MyISAM' AND (" . implode(' OR ', $logTableNameLikePatterns) . ")");
 840       while ($dao->fetch()) {
 841         $tables["{$logging_database}.{$dao->Name}"] = [
 842           'Engine' => $dao->Engine,
 843         ];
 844       }
 845     }
 846     foreach ($tables as $table => $param) {
 847       $query = "ALTER TABLE $table";
 848       $dao = CRM_Core_DAO::executeQuery("SHOW FULL COLUMNS FROM $table", [], TRUE, NULL, FALSE, FALSE);
 849       $index = 0;
 850       $params = [];
 851       $tableCollation = $newCollation;
 852       while ($dao->fetch()) {
 853         if (!$dao->Collation || $dao->Collation === $newCollation || $dao->Collation === $newBinaryCollation) {
 854           continue;
 855         }
 856         if (strpos($dao->Collation, 'utf8') !== 0) {
 857           continue;
 858         }
 859
 860         if (strpos($dao->Collation, '_bin') !== FALSE) {
 861           $tableCollation = $newBinaryCollation;
 862         }
 863         else {
 864           $tableCollation = $newCollation;
 865         }
 866         if ($dao->Null === 'YES') {
 867           $null = 'NULL';
 868         }
 869         else {
 870           $null = 'NOT NULL';
 871         }
 872         $default = '';
 873         if ($dao->Default !== NULL) {
 874           $index++;
 875           $default = "DEFAULT %$index";
 876           $params[$index] = [$dao->Default, 'String'];
 877         }
 878         elseif ($dao->Null === 'YES') {
 879           $default = 'DEFAULT NULL';
 880         }
 881         $index++;
 882         $params[$index] = [$dao->Comment, 'String'];
 883         $query .= " MODIFY `{$dao->Field}` {$dao->Type} CHARACTER SET $newCharSet COLLATE $tableCollation $null $default {$dao->Extra} COMMENT %$index,";
 884       }
 885       $query .= " CHARACTER SET = $newCharSet COLLATE = $tableCollation";
 886       if ($param['Engine'] === 'InnoDB') {
 887         $query .= ' ROW_FORMAT = Dynamic KEY_BLOCK_SIZE = 0';
 888       }
 889       // Disable i18n rewrite.
 890       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 891     }
 892     return TRUE;
 893   }
 894
 895   /**
 896    * Get the database collation.
 897    *
 898    * @return string
 899    */
 900   public static function getDBCollation() {
 901     return CRM_Core_DAO::singleValueQuery('SELECT @@collation_database');
 902   }
 903
 904   /**
 905    * Get the collation actually being used by the tables in the database.
 906    *
 907    * The db collation may not match the collation used by the tables, get what is
 908    * set on the tables (represented by civicrm_contact).
 909    *
 910    * @return string
 911    */
 912   public static function getInUseCollation() {
 913     if (!isset(\Civi::$statics[__CLASS__][__FUNCTION__])) {
 914       $dao = CRM_Core_DAO::executeQuery('SHOW TABLE STATUS LIKE \'civicrm_contact\'');
 915       $dao->fetch();
 916       \Civi::$statics[__CLASS__][__FUNCTION__] = $dao->Collation;
 917     }
 918     return \Civi::$statics[__CLASS__][__FUNCTION__];
 919   }
 920
 921   /**
 922    * Get the database collation.
 923    *
 924    * @return string
 925    */
 926   public static function getDBCharset() {
 927     return CRM_Core_DAO::singleValueQuery('SELECT @@character_set_database');
 928   }
 929
 930 }