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