Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
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 | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | ||
18 | /** | |
db01bf2f | 19 | * This file contains functions for creating and altering CiviCRM-tables structure. |
6a488035 TO |
20 | * |
21 | * $table = array( | |
db01bf2f | 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 | * )); | |
6a488035 TO |
39 | */ |
40 | class CRM_Core_BAO_SchemaHandler { | |
41 | ||
42 | /** | |
c490a46a | 43 | * Create a CiviCRM-table |
6a488035 | 44 | * |
c490a46a | 45 | * @param array $params |
6a488035 | 46 | * |
72b3a70c CW |
47 | * @return bool |
48 | * TRUE if successfully created, FALSE otherwise | |
6a488035 | 49 | * |
6a488035 | 50 | */ |
6477f387 | 51 | public static function createTable($params) { |
6a488035 TO |
52 | $sql = self::buildTableSQL($params); |
53 | // do not i18n-rewrite | |
be2fb01f | 54 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE); |
6a488035 | 55 | |
6477f387 | 56 | if (CRM_Core_Config::singleton()->logging) { |
6a488035 | 57 | // logging support |
8d7a9d07 | 58 | $logging = new CRM_Logging_Schema(); |
fcc20cec | 59 | $logging->fixSchemaDifferencesFor($params['name']); |
6a488035 TO |
60 | } |
61 | ||
62 | // always do a trigger rebuild for this table | |
dfcc817d | 63 | Civi::service('sql_triggers')->rebuild($params['name'], TRUE); |
6a488035 TO |
64 | |
65 | return TRUE; | |
66 | } | |
67 | ||
b5c2afd0 | 68 | /** |
c490a46a | 69 | * @param array $params |
b5c2afd0 EM |
70 | * |
71 | * @return string | |
72 | */ | |
6477f387 | 73 | public static function buildTableSQL($params) { |
6a488035 TO |
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 | ||
b5c2afd0 | 103 | /** |
c490a46a | 104 | * @param array $params |
b5c2afd0 EM |
105 | * @param $separator |
106 | * @param $prefix | |
107 | * | |
108 | * @return string | |
109 | */ | |
4f89ebb0 | 110 | public static function buildFieldSQL($params, $separator, $prefix) { |
6a488035 TO |
111 | $sql = ''; |
112 | $sql .= $separator; | |
113 | $sql .= str_repeat(' ', 8); | |
114 | $sql .= $prefix; | |
115 | $sql .= "`{$params['name']}` {$params['type']}"; | |
116 | ||
a7488080 | 117 | if (!empty($params['required'])) { |
6a488035 TO |
118 | $sql .= " NOT NULL"; |
119 | } | |
120 | ||
a7488080 | 121 | if (!empty($params['attributes'])) { |
6a488035 TO |
122 | $sql .= " {$params['attributes']}"; |
123 | } | |
124 | ||
a7488080 | 125 | if (!empty($params['default']) && |
6a488035 TO |
126 | $params['type'] != 'text' |
127 | ) { | |
128 | $sql .= " DEFAULT {$params['default']}"; | |
129 | } | |
130 | ||
a7488080 | 131 | if (!empty($params['comment'])) { |
6a488035 TO |
132 | $sql .= " COMMENT '{$params['comment']}'"; |
133 | } | |
134 | ||
135 | return $sql; | |
136 | } | |
137 | ||
b5c2afd0 | 138 | /** |
c490a46a | 139 | * @param array $params |
b5c2afd0 EM |
140 | * @param $separator |
141 | * @param $prefix | |
142 | * | |
e60f24eb | 143 | * @return NULL|string |
b5c2afd0 | 144 | */ |
4f89ebb0 | 145 | public static function buildPrimaryKeySQL($params, $separator, $prefix) { |
6a488035 | 146 | $sql = NULL; |
a7488080 | 147 | if (!empty($params['primary'])) { |
6a488035 TO |
148 | $sql .= $separator; |
149 | $sql .= str_repeat(' ', 8); | |
150 | $sql .= $prefix; | |
151 | $sql .= "PRIMARY KEY ( {$params['name']} )"; | |
152 | } | |
153 | return $sql; | |
154 | } | |
155 | ||
b5c2afd0 | 156 | /** |
c490a46a | 157 | * @param array $params |
b5c2afd0 EM |
158 | * @param $separator |
159 | * @param $prefix | |
160 | * @param bool $indexExist | |
161 | * | |
e60f24eb | 162 | * @return NULL|string |
b5c2afd0 | 163 | */ |
4f89ebb0 | 164 | public static function buildSearchIndexSQL($params, $separator, $prefix, $indexExist = FALSE) { |
6a488035 TO |
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, | |
7ab8180f MM |
174 | //drop index only if field is no longer searchable and it does not reference |
175 | //a forgein key (and indexExist is true) | |
a7488080 | 176 | if (!empty($params['searchable']) && !$indexExist) { |
6a488035 TO |
177 | $sql .= $separator; |
178 | $sql .= str_repeat(' ', 8); | |
179 | $sql .= $prefix; | |
180 | $sql .= "INDEX_{$params['name']} ( {$params['name']} )"; | |
181 | } | |
7ab8180f | 182 | elseif (empty($params['searchable']) && empty($params['fk_table_name']) && $indexExist) { |
6a488035 TO |
183 | $sql .= $separator; |
184 | $sql .= str_repeat(' ', 8); | |
185 | $sql .= "DROP INDEX INDEX_{$params['name']}"; | |
186 | } | |
187 | return $sql; | |
188 | } | |
189 | ||
b5c2afd0 | 190 | /** |
c490a46a | 191 | * @param array $params |
b5c2afd0 EM |
192 | * @param $separator |
193 | * @param $prefix | |
194 | * | |
195 | * @return string | |
196 | */ | |
00be9182 | 197 | public static function buildIndexSQL(&$params, $separator, $prefix) { |
6a488035 TO |
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 | ||
b5c2afd0 | 223 | /** |
100fef9d CW |
224 | * @param string $tableName |
225 | * @param string $fkTableName | |
b5c2afd0 EM |
226 | * |
227 | * @return bool | |
228 | */ | |
00be9182 | 229 | public static function changeFKConstraint($tableName, $fkTableName) { |
6a488035 TO |
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 | ||
fccb50a6 | 238 | CRM_Core_DAO::executeQuery($dropFKSql); |
6a488035 TO |
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 | |
be2fb01f | 244 | CRM_Core_DAO::executeQuery($addFKSql, [], TRUE, NULL, FALSE, FALSE); |
6a488035 TO |
245 | |
246 | return TRUE; | |
247 | } | |
248 | ||
b5c2afd0 | 249 | /** |
c490a46a | 250 | * @param array $params |
b5c2afd0 EM |
251 | * @param $separator |
252 | * @param $prefix | |
100fef9d | 253 | * @param string $tableName |
b5c2afd0 | 254 | * |
e60f24eb | 255 | * @return NULL|string |
b5c2afd0 | 256 | */ |
4f89ebb0 | 257 | public static function buildForeignKeySQL($params, $separator, $prefix, $tableName) { |
6a488035 | 258 | $sql = NULL; |
8cc574cf | 259 | if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) { |
6a488035 TO |
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 | ||
6a488035 | 274 | /** |
49186f94 | 275 | * Delete a CiviCRM-table. |
6a488035 | 276 | * |
d3e86119 | 277 | * @param string $tableName |
6a0b768e | 278 | * Name of the table to be created. |
6a488035 | 279 | */ |
00be9182 | 280 | public static function dropTable($tableName) { |
6a488035 | 281 | $sql = "DROP TABLE $tableName"; |
49186f94 | 282 | CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
283 | } |
284 | ||
b5c2afd0 | 285 | /** |
100fef9d CW |
286 | * @param string $tableName |
287 | * @param string $columnName | |
3abab0f8 | 288 | * @param bool $l18n |
41ace555 | 289 | * @param bool $isUpgradeMode |
87568e34 | 290 | * |
b5c2afd0 | 291 | */ |
41ace555 | 292 | public static function dropColumn($tableName, $columnName, $l18n = FALSE, $isUpgradeMode = FALSE) { |
242055d3 CW |
293 | if (self::checkIfFieldExists($tableName, $columnName)) { |
294 | $sql = "ALTER TABLE $tableName DROP COLUMN $columnName"; | |
3abab0f8 | 295 | if ($l18n) { |
87568e34 SL |
296 | CRM_Core_DAO::executeQuery($sql); |
297 | } | |
298 | else { | |
be2fb01f | 299 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE); |
87568e34 | 300 | } |
394d18d3 CW |
301 | $locales = CRM_Core_I18n::getMultilingual(); |
302 | if ($locales) { | |
41ace555 | 303 | CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL, $isUpgradeMode); |
1285e488 | 304 | } |
242055d3 | 305 | } |
6a488035 TO |
306 | } |
307 | ||
b5c2afd0 | 308 | /** |
100fef9d | 309 | * @param string $tableName |
b5c2afd0 EM |
310 | * @param bool $dropUnique |
311 | */ | |
00be9182 | 312 | public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) { |
6a488035 TO |
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 | } | |
49186f94 | 323 | CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
324 | } |
325 | ||
b5c2afd0 | 326 | /** |
7181119b | 327 | * Create indexes. |
328 | * | |
b5c2afd0 | 329 | * @param $tables |
7181119b | 330 | * Tables to create index for in the format: |
d551d3d6 | 331 | * ['civicrm_entity_table' => ['entity_id']] |
7181119b | 332 | * OR |
d551d3d6 | 333 | * array['civicrm_entity_table' => array['entity_id', 'entity_table']] |
7181119b | 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 | * | |
b5c2afd0 | 344 | * @param string $createIndexPrefix |
fe891bc6 | 345 | * @param array $substrLengths |
b5c2afd0 | 346 | */ |
be2fb01f CW |
347 | public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLengths = []) { |
348 | $queries = []; | |
394d18d3 | 349 | $locales = CRM_Core_I18n::getMultilingual(); |
6a488035 TO |
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 | ||
be2fb01f | 361 | $currentIndexes = []; |
6a488035 TO |
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) { | |
7181119b | 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 | |
fe891bc6 AS |
379 | $lengthName = isset($substrLengths[$table][$fieldName]) ? "_{$substrLengths[$table][$fieldName]}" : ''; |
380 | $lengthSize = isset($substrLengths[$table][$fieldName]) ? "({$substrLengths[$table][$fieldName]})" : ''; | |
7181119b | 381 | } |
6a488035 | 382 | |
be2fb01f | 383 | $names = [ |
7181119b | 384 | "index_{$fieldName}{$lengthName}", |
385 | "FK_{$table}_{$fieldName}{$lengthName}", | |
386 | "UI_{$fieldName}{$lengthName}", | |
387 | "{$createIndexPrefix}_{$fieldName}{$lengthName}", | |
be2fb01f | 388 | ]; |
6a488035 TO |
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 | ||
8a493ab9 CW |
398 | $indexType = $createIndexPrefix === 'UI' ? 'UNIQUE' : ''; |
399 | ||
6a488035 TO |
400 | // the index doesn't exist, so create it |
401 | // if we're multilingual and the field is internationalised, do it for every locale | |
7181119b | 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])) { | |
6a488035 | 406 | foreach ($locales as $locale) { |
8a493ab9 | 407 | $queries[] = "CREATE $indexType INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})"; |
6a488035 TO |
408 | } |
409 | } | |
410 | else { | |
8a493ab9 | 411 | $queries[] = "CREATE $indexType INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})"; |
6a488035 TO |
412 | } |
413 | } | |
414 | } | |
415 | ||
416 | // run the queries without i18n-rewriting | |
8d7a9d07 | 417 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
418 | foreach ($queries as $query) { |
419 | $dao->query($query, FALSE); | |
420 | } | |
421 | } | |
422 | ||
49186f94 AS |
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) { | |
be2fb01f | 431 | $indexes = []; |
49186f94 AS |
432 | foreach ($tables as $table) { |
433 | $query = "SHOW INDEX FROM $table"; | |
434 | $dao = CRM_Core_DAO::executeQuery($query); | |
435 | ||
be2fb01f | 436 | $tableIndexes = []; |
49186f94 AS |
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; | |
49186f94 AS |
444 | } |
445 | return $indexes; | |
446 | } | |
447 | ||
50969d52 | 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 | ||
b5c2afd0 | 460 | /** |
100fef9d CW |
461 | * @param int $customFieldID |
462 | * @param string $tableName | |
463 | * @param string $columnName | |
b5c2afd0 EM |
464 | * @param $length |
465 | * | |
ac15829d | 466 | * @throws CRM_Core_Exception |
b5c2afd0 | 467 | */ |
00be9182 | 468 | public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) { |
6a488035 TO |
469 | // first update the custom field tables |
470 | $sql = " | |
471 | UPDATE civicrm_custom_field | |
472 | SET text_length = %1 | |
473 | WHERE id = %2 | |
474 | "; | |
be2fb01f CW |
475 | $params = [ |
476 | 1 => [$length, 'Integer'], | |
477 | 2 => [$customFieldID, 'Integer'], | |
478 | ]; | |
6a488035 TO |
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 { | |
ac15829d | 507 | throw new CRM_Core_Exception(ts('Could Not Find Custom Field Details for %1, %2, %3', |
be2fb01f | 508 | [ |
353ffa53 TO |
509 | 1 => $tableName, |
510 | 2 => $columnName, | |
511 | 3 => $customFieldID, | |
be2fb01f | 512 | ] |
353ffa53 | 513 | )); |
6a488035 TO |
514 | } |
515 | } | |
96025800 | 516 | |
50969d52 | 517 | /** |
518 | * Check if the table has an index matching the name. | |
519 | * | |
520 | * @param string $tableName | |
6a8758a6 | 521 | * @param string $indexName |
50969d52 | 522 | * |
49186f94 | 523 | * @return bool |
50969d52 | 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", | |
be2fb01f | 528 | [1 => [$indexName, 'String']] |
50969d52 | 529 | ); |
530 | if ($result->fetch()) { | |
531 | return TRUE; | |
532 | } | |
533 | return FALSE; | |
534 | } | |
535 | ||
82f5a856 | 536 | /** |
49186f94 | 537 | * Check if the table has a specified column. |
82f5a856 SL |
538 | * |
539 | * @param string $tableName | |
540 | * @param string $columnName | |
eed7e803 CW |
541 | * @param bool $i18nRewrite |
542 | * Whether to rewrite the query on multilingual setups. | |
82f5a856 | 543 | * |
49186f94 | 544 | * @return bool |
82f5a856 | 545 | */ |
eed7e803 | 546 | public static function checkIfFieldExists($tableName, $columnName, $i18nRewrite = TRUE) { |
5e282b34 CW |
547 | $query = "SHOW COLUMNS FROM $tableName LIKE '%1'"; |
548 | $dao = CRM_Core_DAO::executeQuery($query, [1 => [$columnName, 'Alphanumeric']], TRUE, NULL, FALSE, $i18nRewrite); | |
63d76404 | 549 | return (bool) $dao->fetch(); |
82f5a856 SL |
550 | } |
551 | ||
169475b7 | 552 | /** |
f5078cc0 SL |
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 | |
169475b7 | 557 | */ |
f5078cc0 | 558 | public static function checkFKExists($table_name, $constraint_name) { |
169475b7 | 559 | $config = CRM_Core_Config::singleton(); |
58d1e21e SL |
560 | $dsn = CRM_Utils_SQL::autoSwitchDSN($config->dsn); |
561 | $dbUf = DB::parseDSN($dsn); | |
169475b7 SL |
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 | "; | |
be2fb01f CW |
569 | $params = [ |
570 | 1 => [$dbUf['database'], 'String'], | |
571 | 2 => [$table_name, 'String'], | |
572 | 3 => [$constraint_name, 'String'], | |
573 | ]; | |
1494e986 | 574 | $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE); |
169475b7 SL |
575 | |
576 | if ($dao->fetch()) { | |
27e82c24 | 577 | return TRUE; |
f5078cc0 | 578 | } |
27e82c24 | 579 | return FALSE; |
f5078cc0 SL |
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) { | |
27e82c24 | 591 | if (self::checkFKExists($table_name, $constraint_name)) { |
1494e986 | 592 | CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", [], TRUE, NULL, FALSE, FALSE); |
9cd5a579 | 593 | return TRUE; |
169475b7 | 594 | } |
9cd5a579 | 595 | return FALSE; |
169475b7 SL |
596 | } |
597 | ||
6b86d84f AS |
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 | ||
49186f94 AS |
612 | /** |
613 | * Compare the indices specified in the XML files with those in the DB. | |
614 | * | |
3d4602c3 JP |
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. | |
138b4c4c | 618 | * @param array|FALSE $tables |
619 | * An optional array of tables - if provided the results will be restricted to these tables. | |
3d4602c3 | 620 | * |
49186f94 AS |
621 | * @return array |
622 | * index specifications | |
623 | */ | |
138b4c4c | 624 | public static function getMissingIndices($dropFalseIndices = FALSE, $tables = FALSE) { |
be2fb01f | 625 | $requiredSigs = $existingSigs = []; |
49186f94 AS |
626 | // Get the indices defined (originally) in the xml files |
627 | $requiredIndices = CRM_Core_DAO_AllCoreTables::indices(); | |
be2fb01f | 628 | $reqSigs = []; |
138b4c4c | 629 | if ($tables !== FALSE) { |
630 | $requiredIndices = array_intersect_key($requiredIndices, array_fill_keys($tables, TRUE)); | |
631 | } | |
49186f94 AS |
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)); | |
be2fb01f | 639 | $extSigs = []; |
49186f94 AS |
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); | |
f4f835ed | 648 | |
3d4602c3 | 649 | //CRM-20774 - Drop index key which exist in db but the value varies. |
f4f835ed | 650 | $existingKeySigs = array_intersect_key($missingSigs, $existingSigs); |
3d4602c3 | 651 | if ($dropFalseIndices && !empty($existingKeySigs)) { |
f4f835ed JP |
652 | foreach ($existingKeySigs as $sig) { |
653 | $sigParts = explode('::', $sig); | |
654 | foreach ($requiredIndices[$sigParts[0]] as $index) { | |
3d4602c3 JP |
655 | if ($index['sig'] == $sig && !empty($index['name'])) { |
656 | self::dropIndexIfExists($sigParts[0], $index['name']); | |
f4f835ed JP |
657 | continue; |
658 | } | |
659 | } | |
660 | } | |
661 | } | |
662 | ||
49186f94 | 663 | // Get missing indices |
be2fb01f | 664 | $missingIndices = []; |
49186f94 AS |
665 | foreach ($missingSigs as $sig) { |
666 | $sigParts = explode('::', $sig); | |
f3490f40 | 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 | } | |
49186f94 AS |
673 | } |
674 | } | |
675 | } | |
3d4602c3 | 676 | return $missingIndices; |
49186f94 AS |
677 | } |
678 | ||
679 | /** | |
680 | * Create missing indices. | |
681 | * | |
682 | * @param array $missingIndices as returned by getMissingIndices() | |
683 | */ | |
684 | public static function createMissingIndices($missingIndices) { | |
be2fb01f | 685 | $queries = []; |
49186f94 AS |
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 | } | |
49186f94 AS |
701 | } |
702 | ||
d992b2f5 | 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']}"; | |
8e2949c4 | 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 = ''; | |
d992b2f5 | 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 | ||
a0a5d4da | 760 | /** |
761 | * Performs the utf8mb4 migration. | |
762 | * | |
763 | * @param bool $revert | |
764 | * Being able to revert if primarily for unit testing. | |
aedfc3ed MW |
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. | |
a0a5d4da | 769 | * |
770 | * @return bool | |
771 | */ | |
aedfc3ed | 772 | public static function migrateUtf8mb4($revert = FALSE, $patterns = ['civicrm\_%'], $databaseList = NULL) { |
a0a5d4da | 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(); | |
aedfc3ed MW |
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) { | |
58d1e21e SL |
801 | $dsn = defined('CIVICRM_LOGGING_DSN') ? CRM_Utils_SQL::autoSwitchDSN(CIVICRM_LOGGING_DSN) : CRM_Utils_SQL::autoSwitchDSN(CIVICRM_DSN); |
802 | $dsn = DB::parseDSN($dsn); | |
aedfc3ed MW |
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 | } | |
a0a5d4da | 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') { | |
41a26a7c | 852 | $query .= ' ROW_FORMAT = Dynamic KEY_BLOCK_SIZE = 0'; |
a0a5d4da | 853 | } |
854 | // Disable i18n rewrite. | |
855 | CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE); | |
856 | } | |
586cb32c | 857 | // Rebuild triggers and other schema reconciliation if needed. |
858 | $logging = new CRM_Logging_Schema(); | |
859 | $logging->fixSchemaDifferences(); | |
a0a5d4da | 860 | return TRUE; |
861 | } | |
862 | ||
25374ca7 | 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 | ||
8f719ee0 | 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 | ||
25374ca7 | 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 | ||
6a488035 | 898 | } |