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     $dao = new CRM_Core_DAO();
 556     $query = "
 557       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 558       WHERE TABLE_SCHEMA = %1
 559       AND TABLE_NAME = %2
 560       AND CONSTRAINT_NAME = %3
 561       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 562     ";
 563     $params = [
 564       1 => [$dao->_database, 'String'],
 565       2 => [$table_name, 'String'],
 566       3 => [$constraint_name, 'String'],
 567     ];
 568     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 569
 570     if ($dao->fetch()) {
 571       return TRUE;
 572     }
 573     return FALSE;
 574   }
 575
 576   /**
 577    * Remove a foreign key from a table if it exists.
 578    *
 579    * @param $table_name
 580    * @param $constraint_name
 581    *
 582    * @return bool
 583    */
 584   public static function safeRemoveFK($table_name, $constraint_name) {
 585     if (self::checkFKExists($table_name, $constraint_name)) {
 586       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", [], TRUE, NULL, FALSE, FALSE);
 587       return TRUE;
 588     }
 589     return FALSE;
 590   }
 591
 592   /**
 593    * Add index signature hash to DAO file calculation.
 594    *
 595    * @param string $table table name
 596    * @param array $indices index array spec
 597    */
 598   public static function addIndexSignature($table, &$indices) {
 599     foreach ($indices as $indexName => $index) {
 600       $indices[$indexName]['sig'] = $table . "::" .
 601         (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" .
 602         implode("::", $index['field']);
 603     }
 604   }
 605
 606   /**
 607    * Compare the indices specified in the XML files with those in the DB.
 608    *
 609    * @param bool $dropFalseIndices
 610    *  If set - this function deletes false indices present in the DB which mismatches the expected
 611    *  values of xml file so that civi re-creates them with correct values using createMissingIndices() function.
 612    * @param array|false $tables
 613    *   An optional array of tables - if provided the results will be restricted to these tables.
 614    *
 615    * @return array
 616    *   index specifications
 617    */
 618   public static function getMissingIndices($dropFalseIndices = FALSE, $tables = FALSE) {
 619     $requiredSigs = $existingSigs = [];
 620     // Get the indices defined (originally) in the xml files
 621     $requiredIndices = CRM_Core_DAO_AllCoreTables::indices();
 622     $reqSigs = [];
 623     if ($tables !== FALSE) {
 624       $requiredIndices = array_intersect_key($requiredIndices, array_fill_keys($tables, TRUE));
 625     }
 626     foreach ($requiredIndices as $table => $indices) {
 627       $reqSigs[] = CRM_Utils_Array::collect('sig', $indices);
 628     }
 629     CRM_Utils_Array::flatten($reqSigs, $requiredSigs);
 630
 631     // Get the indices in the database
 632     $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices));
 633     $extSigs = [];
 634     foreach ($existingIndices as $table => $indices) {
 635       CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices);
 636       $extSigs[] = CRM_Utils_Array::collect('sig', $indices);
 637     }
 638     CRM_Utils_Array::flatten($extSigs, $existingSigs);
 639
 640     // Compare
 641     $missingSigs = array_diff($requiredSigs, $existingSigs);
 642
 643     //CRM-20774 - Drop index key which exist in db but the value varies.
 644     $existingKeySigs = array_intersect_key($missingSigs, $existingSigs);
 645     if ($dropFalseIndices && !empty($existingKeySigs)) {
 646       foreach ($existingKeySigs as $sig) {
 647         $sigParts = explode('::', $sig);
 648         foreach ($requiredIndices[$sigParts[0]] as $index) {
 649           if ($index['sig'] == $sig && !empty($index['name'])) {
 650             self::dropIndexIfExists($sigParts[0], $index['name']);
 651             continue;
 652           }
 653         }
 654       }
 655     }
 656
 657     // Get missing indices
 658     $missingIndices = [];
 659     foreach ($missingSigs as $sig) {
 660       $sigParts = explode('::', $sig);
 661       if (array_key_exists($sigParts[0], $requiredIndices)) {
 662         foreach ($requiredIndices[$sigParts[0]] as $index) {
 663           if ($index['sig'] == $sig) {
 664             $missingIndices[$sigParts[0]][] = $index;
 665             continue;
 666           }
 667         }
 668       }
 669     }
 670     return $missingIndices;
 671   }
 672
 673   /**
 674    * Create missing indices.
 675    *
 676    * @param array $missingIndices as returned by getMissingIndices()
 677    */
 678   public static function createMissingIndices($missingIndices) {
 679     $queries = [];
 680     foreach ($missingIndices as $table => $indexList) {
 681       foreach ($indexList as $index) {
 682         $queries[] = "CREATE " .
 683         (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') .
 684         "INDEX {$index['name']} ON {$table} (" .
 685           implode(", ", $index['field']) .
 686         ")";
 687       }
 688     }
 689
 690     /* FIXME potential problem if index name already exists, so check before creating */
 691     $dao = new CRM_Core_DAO();
 692     foreach ($queries as $query) {
 693       $dao->query($query, FALSE);
 694     }
 695   }
 696
 697   /**
 698    * Build the sql to alter the field.
 699    *
 700    * @param array $params
 701    *
 702    * @return string
 703    */
 704   public static function buildFieldChangeSql($params) {
 705     $sql = str_repeat(' ', 8);
 706     $sql .= "ALTER TABLE {$params['table_name']}";
 707     return $sql . self::getFieldAlterSQL($params);
 708   }
 709
 710   /**
 711    * Get the sql to alter an individual field.
 712    *
 713    * This will need to have an ALTER TABLE statement appended but by getting
 714    * by individual field we can do one or many.
 715    *
 716    * @param array $params
 717    *
 718    * @return string
 719    */
 720   public static function getFieldAlterSQL($params) {
 721     $sql = '';
 722     switch ($params['operation']) {
 723       case 'add':
 724         $separator = "\n";
 725         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 726         $separator = ",\n";
 727         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 728         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 729         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 730         break;
 731
 732       case 'modify':
 733         $separator = "\n";
 734         $existingIndex = NULL;
 735         $dao = CRM_Core_DAO::executeQuery("SHOW INDEX FROM `{$params['table_name']}` WHERE Column_name = '{$params['name']}'");
 736         if ($dao->fetch()) {
 737           $existingIndex = $dao->Key_name;
 738         }
 739         $fkSql = self::buildForeignKeySQL($params, ",\n", "ADD ", $params['table_name']);
 740         if (substr($existingIndex, 0, 2) === 'FK' && !$fkSql) {
 741           $sql .= "$separator DROP FOREIGN KEY {$existingIndex},\nDROP INDEX {$existingIndex}";
 742           $separator = ",\n";
 743         }
 744         $sql .= self::buildFieldSQL($params, $separator, "MODIFY ");
 745         $separator = ",\n";
 746         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $existingIndex);
 747         if (!$existingIndex && $fkSql) {
 748           $sql .= $fkSql;
 749         }
 750         break;
 751
 752       case 'delete':
 753         $sql .= " DROP COLUMN `{$params['name']}`";
 754         if (!empty($params['primary'])) {
 755           $sql .= ", DROP PRIMARY KEY";
 756         }
 757         if (!empty($params['fk_table_name'])) {
 758           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 759         }
 760         break;
 761     }
 762     return $sql;
 763   }
 764
 765   /**
 766    * Turns tableName + columnName into a safe & predictable index name
 767    *
 768    * @param $tableName
 769    * @param $columnName
 770    * @return string
 771    */
 772   public static function getIndexName($tableName, $columnName) {
 773     $indexName = "{$tableName}_{$columnName}";
 774     if (strlen($indexName) >= 48) {
 775       $indexName = substr($indexName, 0, 32) . "_" . substr(md5($indexName), 0, 16);
 776     }
 777     return $indexName;
 778   }
 779
 780   /**
 781    * Performs the utf8mb4 migration.
 782    *
 783    * @param bool $revert
 784    *   Being able to revert if primarily for unit testing.
 785    * @param array $patterns
 786    *   Defaults to ['civicrm\_%'] but can be overridden to specify any pattern. eg ['civicrm\_%', 'civi%\_%', 'veda%\_%'].
 787    * @param array $databaseList
 788    *   Allows you to specify an alternative database to the configured CiviCRM database.
 789    *
 790    * @return bool
 791    */
 792   public static function migrateUtf8mb4($revert = FALSE, $patterns = ['civicrm\_%'], $databaseList = NULL) {
 793     $newCharSet = $revert ? 'utf8' : 'utf8mb4';
 794     $newCollation = $revert ? 'utf8_unicode_ci' : 'utf8mb4_unicode_ci';
 795     $newBinaryCollation = $revert ? 'utf8_bin' : 'utf8mb4_bin';
 796     $tables = [];
 797     $dao = new CRM_Core_DAO();
 798     $databases = $databaseList ?? [$dao->_database];
 799
 800     $tableNameLikePatterns = [];
 801     $logTableNameLikePatterns = [];
 802
 803     foreach ($patterns as $pattern) {
 804       $pattern = CRM_Utils_Type::escape($pattern, 'String');
 805       $tableNameLikePatterns[] = "Name LIKE '{$pattern}'";
 806       $logTableNameLikePatterns[] = "Name LIKE 'log\_{$pattern}'";
 807     }
 808
 809     foreach ($databases as $database) {
 810       CRM_Core_DAO::executeQuery("ALTER DATABASE `{$database}` CHARACTER SET = $newCharSet COLLATE = $newCollation");
 811       $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$database}` WHERE Engine = 'InnoDB' AND (" . implode(' OR ', $tableNameLikePatterns) . ")");
 812       while ($dao->fetch()) {
 813         $tables["`{$database}`.`{$dao->Name}`"] = [
 814           'Engine' => $dao->Engine,
 815         ];
 816       }
 817     }
 818     // If we specified a list of databases assume the user knows what they are doing.
 819     // If they specify the database they should also specify the pattern.
 820     if (!$databaseList) {
 821       $dsn = defined('CIVICRM_LOGGING_DSN') ? CRM_Utils_SQL::autoSwitchDSN(CIVICRM_LOGGING_DSN) : CRM_Utils_SQL::autoSwitchDSN(CIVICRM_DSN);
 822       $dsn = DB::parseDSN($dsn);
 823       $logging_database = $dsn['database'];
 824       $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$logging_database}` WHERE Engine <> 'MyISAM' AND (" . implode(' OR ', $logTableNameLikePatterns) . ")");
 825       while ($dao->fetch()) {
 826         $tables["`{$logging_database}`.`{$dao->Name}`"] = [
 827           'Engine' => $dao->Engine,
 828         ];
 829       }
 830     }
 831     foreach ($tables as $table => $param) {
 832       $query = "ALTER TABLE $table";
 833       $dao = CRM_Core_DAO::executeQuery("SHOW FULL COLUMNS FROM $table", [], TRUE, NULL, FALSE, FALSE);
 834       $index = 0;
 835       $params = [];
 836       $tableCollation = $newCollation;
 837       while ($dao->fetch()) {
 838         if (!$dao->Collation || $dao->Collation === $newCollation || $dao->Collation === $newBinaryCollation) {
 839           continue;
 840         }
 841         if (strpos($dao->Collation, 'utf8') !== 0) {
 842           continue;
 843         }
 844
 845         if (strpos($dao->Collation, '_bin') !== FALSE) {
 846           $tableCollation = $newBinaryCollation;
 847         }
 848         else {
 849           $tableCollation = $newCollation;
 850         }
 851         if ($dao->Null === 'YES') {
 852           $null = 'NULL';
 853         }
 854         else {
 855           $null = 'NOT NULL';
 856         }
 857         $default = '';
 858         if ($dao->Default !== NULL) {
 859           $index++;
 860           $default = "DEFAULT %$index";
 861           $params[$index] = [$dao->Default, 'String'];
 862         }
 863         elseif ($dao->Null === 'YES') {
 864           $default = 'DEFAULT NULL';
 865         }
 866         $index++;
 867         $params[$index] = [$dao->Comment, 'String'];
 868         $query .= " MODIFY `{$dao->Field}` {$dao->Type} CHARACTER SET $newCharSet COLLATE $tableCollation $null $default {$dao->Extra} COMMENT %$index,";
 869       }
 870       $query .= " CHARACTER SET = $newCharSet COLLATE = $tableCollation";
 871       if ($param['Engine'] === 'InnoDB') {
 872         $query .= ' ROW_FORMAT = Dynamic KEY_BLOCK_SIZE = 0';
 873       }
 874       // Disable i18n rewrite.
 875       CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
 876     }
 877     // Rebuild triggers and other schema reconciliation if needed.
 878     $logging = new CRM_Logging_Schema();
 879     $logging->fixSchemaDifferences();
 880     return TRUE;
 881   }
 882
 883   /**
 884    * Get the database collation.
 885    *
 886    * @return string
 887    */
 888   public static function getDBCollation() {
 889     return CRM_Core_DAO::singleValueQuery('SELECT @@collation_database');
 890   }
 891
 892   /**
 893    * Get the collation actually being used by the tables in the database.
 894    *
 895    * The db collation may not match the collation used by the tables, get what is
 896    * set on the tables (represented by civicrm_contact).
 897    *
 898    * @return string
 899    */
 900   public static function getInUseCollation(): string {
 901     if (!isset(\Civi::$statics[__CLASS__][__FUNCTION__])) {
 902       $dao = CRM_Core_DAO::executeQuery('SHOW TABLE STATUS LIKE \'civicrm_contact\'');
 903       $dao->fetch();
 904       \Civi::$statics[__CLASS__][__FUNCTION__] = $dao->Collation;
 905     }
 906     return \Civi::$statics[__CLASS__][__FUNCTION__];
 907   }
 908
 909   /**
 910    * Does the database support utf8mb4.
 911    *
 912    * Utf8mb4 is required to support emojis but older databases may not have it enabled.
 913    *
 914    * This is aggressively cached despite just being a string function
 915    * as it is expected it might be called many times.
 916    *
 917    * @return bool
 918    */
 919   public static function databaseSupportsUTF8MB4(): bool {
 920     if (!isset(\Civi::$statics[__CLASS__][__FUNCTION__])) {
 921       \Civi::$statics[__CLASS__][__FUNCTION__] = stripos(self::getInUseCollation(), 'utf8mb4') === 0;
 922     }
 923     return \Civi::$statics[__CLASS__][__FUNCTION__];
 924   }
 925
 926   /**
 927    * Get the database collation.
 928    *
 929    * @return string
 930    */
 931   public static function getDBCharset() {
 932     return CRM_Core_DAO::singleValueQuery('SELECT @@character_set_database');
 933   }
 934
 935 }