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