Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
6b83d5bd | 6 | | Copyright CiviCRM LLC (c) 2004-2019 | |
6a488035 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License and the CiviCRM Licensing Exception along | | |
21 | | with this program; if not, contact CiviCRM LLC | | |
22 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
23 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
25 | +--------------------------------------------------------------------+ | |
d25dd0ee | 26 | */ |
6a488035 TO |
27 | |
28 | /** | |
29 | * | |
30 | * @package CRM | |
6b83d5bd | 31 | * @copyright CiviCRM LLC (c) 2004-2019 |
6a488035 TO |
32 | */ |
33 | ||
34 | /** | |
db01bf2f | 35 | * This file contains functions for creating and altering CiviCRM-tables structure. |
6a488035 TO |
36 | * |
37 | * $table = array( | |
db01bf2f | 38 | * 'name' => TABLE_NAME, |
39 | * 'attributes' => ATTRIBUTES, | |
40 | * 'fields' => array( | |
41 | * array( | |
42 | * 'name' => FIELD_NAME, | |
43 | * // can be field, index, constraint | |
44 | * 'type' => FIELD_SQL_TYPE, | |
45 | * 'class' => FIELD_CLASS_TYPE, | |
46 | * 'primary' => BOOLEAN, | |
47 | * 'required' => BOOLEAN, | |
48 | * 'searchable' => TRUE, | |
49 | * 'fk_table_name' => FOREIGN_KEY_TABLE_NAME, | |
50 | * 'fk_field_name' => FOREIGN_KEY_FIELD_NAME, | |
51 | * 'comment' => COMMENT, | |
52 | * 'default' => DEFAULT, ) | |
53 | * ... | |
54 | * )); | |
6a488035 TO |
55 | */ |
56 | class CRM_Core_BAO_SchemaHandler { | |
57 | ||
58 | /** | |
c490a46a | 59 | * Create a CiviCRM-table |
6a488035 | 60 | * |
c490a46a | 61 | * @param array $params |
6a488035 | 62 | * |
72b3a70c CW |
63 | * @return bool |
64 | * TRUE if successfully created, FALSE otherwise | |
6a488035 | 65 | * |
6a488035 | 66 | */ |
6477f387 | 67 | public static function createTable($params) { |
6a488035 TO |
68 | $sql = self::buildTableSQL($params); |
69 | // do not i18n-rewrite | |
be2fb01f | 70 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE); |
6a488035 | 71 | |
6477f387 | 72 | if (CRM_Core_Config::singleton()->logging) { |
6a488035 | 73 | // logging support |
8d7a9d07 | 74 | $logging = new CRM_Logging_Schema(); |
fcc20cec | 75 | $logging->fixSchemaDifferencesFor($params['name']); |
6a488035 TO |
76 | } |
77 | ||
78 | // always do a trigger rebuild for this table | |
dfcc817d | 79 | Civi::service('sql_triggers')->rebuild($params['name'], TRUE); |
6a488035 TO |
80 | |
81 | return TRUE; | |
82 | } | |
83 | ||
b5c2afd0 | 84 | /** |
c490a46a | 85 | * @param array $params |
b5c2afd0 EM |
86 | * |
87 | * @return string | |
88 | */ | |
6477f387 | 89 | public static function buildTableSQL($params) { |
6a488035 TO |
90 | $sql = "CREATE TABLE {$params['name']} ("; |
91 | if (isset($params['fields']) && | |
92 | is_array($params['fields']) | |
93 | ) { | |
94 | $separator = "\n"; | |
95 | $prefix = NULL; | |
96 | foreach ($params['fields'] as $field) { | |
97 | $sql .= self::buildFieldSQL($field, $separator, $prefix); | |
98 | $separator = ",\n"; | |
99 | } | |
100 | foreach ($params['fields'] as $field) { | |
101 | $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix); | |
102 | } | |
103 | foreach ($params['fields'] as $field) { | |
104 | $sql .= self::buildSearchIndexSQL($field, $separator, $prefix); | |
105 | } | |
106 | if (isset($params['indexes'])) { | |
107 | foreach ($params['indexes'] as $index) { | |
108 | $sql .= self::buildIndexSQL($index, $separator, $prefix); | |
109 | } | |
110 | } | |
111 | foreach ($params['fields'] as $field) { | |
112 | $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']); | |
113 | } | |
114 | } | |
115 | $sql .= "\n) {$params['attributes']};"; | |
116 | return $sql; | |
117 | } | |
118 | ||
b5c2afd0 | 119 | /** |
c490a46a | 120 | * @param array $params |
b5c2afd0 EM |
121 | * @param $separator |
122 | * @param $prefix | |
123 | * | |
124 | * @return string | |
125 | */ | |
4f89ebb0 | 126 | public static function buildFieldSQL($params, $separator, $prefix) { |
6a488035 TO |
127 | $sql = ''; |
128 | $sql .= $separator; | |
129 | $sql .= str_repeat(' ', 8); | |
130 | $sql .= $prefix; | |
131 | $sql .= "`{$params['name']}` {$params['type']}"; | |
132 | ||
a7488080 | 133 | if (!empty($params['required'])) { |
6a488035 TO |
134 | $sql .= " NOT NULL"; |
135 | } | |
136 | ||
a7488080 | 137 | if (!empty($params['attributes'])) { |
6a488035 TO |
138 | $sql .= " {$params['attributes']}"; |
139 | } | |
140 | ||
a7488080 | 141 | if (!empty($params['default']) && |
6a488035 TO |
142 | $params['type'] != 'text' |
143 | ) { | |
144 | $sql .= " DEFAULT {$params['default']}"; | |
145 | } | |
146 | ||
a7488080 | 147 | if (!empty($params['comment'])) { |
6a488035 TO |
148 | $sql .= " COMMENT '{$params['comment']}'"; |
149 | } | |
150 | ||
151 | return $sql; | |
152 | } | |
153 | ||
b5c2afd0 | 154 | /** |
c490a46a | 155 | * @param array $params |
b5c2afd0 EM |
156 | * @param $separator |
157 | * @param $prefix | |
158 | * | |
e60f24eb | 159 | * @return NULL|string |
b5c2afd0 | 160 | */ |
4f89ebb0 | 161 | public static function buildPrimaryKeySQL($params, $separator, $prefix) { |
6a488035 | 162 | $sql = NULL; |
a7488080 | 163 | if (!empty($params['primary'])) { |
6a488035 TO |
164 | $sql .= $separator; |
165 | $sql .= str_repeat(' ', 8); | |
166 | $sql .= $prefix; | |
167 | $sql .= "PRIMARY KEY ( {$params['name']} )"; | |
168 | } | |
169 | return $sql; | |
170 | } | |
171 | ||
b5c2afd0 | 172 | /** |
c490a46a | 173 | * @param array $params |
b5c2afd0 EM |
174 | * @param $separator |
175 | * @param $prefix | |
176 | * @param bool $indexExist | |
177 | * | |
e60f24eb | 178 | * @return NULL|string |
b5c2afd0 | 179 | */ |
4f89ebb0 | 180 | public static function buildSearchIndexSQL($params, $separator, $prefix, $indexExist = FALSE) { |
6a488035 TO |
181 | $sql = NULL; |
182 | ||
183 | // dont index blob | |
184 | if ($params['type'] == 'text') { | |
185 | return $sql; | |
186 | } | |
187 | ||
188 | //create index only for searchable fields during ADD, | |
189 | //create index only if field is become searchable during MODIFY, | |
7ab8180f MM |
190 | //drop index only if field is no longer searchable and it does not reference |
191 | //a forgein key (and indexExist is true) | |
a7488080 | 192 | if (!empty($params['searchable']) && !$indexExist) { |
6a488035 TO |
193 | $sql .= $separator; |
194 | $sql .= str_repeat(' ', 8); | |
195 | $sql .= $prefix; | |
196 | $sql .= "INDEX_{$params['name']} ( {$params['name']} )"; | |
197 | } | |
7ab8180f | 198 | elseif (empty($params['searchable']) && empty($params['fk_table_name']) && $indexExist) { |
6a488035 TO |
199 | $sql .= $separator; |
200 | $sql .= str_repeat(' ', 8); | |
201 | $sql .= "DROP INDEX INDEX_{$params['name']}"; | |
202 | } | |
203 | return $sql; | |
204 | } | |
205 | ||
b5c2afd0 | 206 | /** |
c490a46a | 207 | * @param array $params |
b5c2afd0 EM |
208 | * @param $separator |
209 | * @param $prefix | |
210 | * | |
211 | * @return string | |
212 | */ | |
00be9182 | 213 | public static function buildIndexSQL(&$params, $separator, $prefix) { |
6a488035 TO |
214 | $sql = ''; |
215 | $sql .= $separator; | |
216 | $sql .= str_repeat(' ', 8); | |
217 | if ($params['unique']) { | |
218 | $sql .= 'UNIQUE INDEX'; | |
219 | $indexName = 'unique'; | |
220 | } | |
221 | else { | |
222 | $sql .= 'INDEX'; | |
223 | $indexName = 'index'; | |
224 | } | |
225 | $indexFields = NULL; | |
226 | ||
227 | foreach ($params as $name => $value) { | |
228 | if (substr($name, 0, 11) == 'field_name_') { | |
229 | $indexName .= "_{$value}"; | |
230 | $indexFields .= " $value,"; | |
231 | } | |
232 | } | |
233 | $indexFields = substr($indexFields, 0, -1); | |
234 | ||
235 | $sql .= " $indexName ( $indexFields )"; | |
236 | return $sql; | |
237 | } | |
238 | ||
b5c2afd0 | 239 | /** |
100fef9d CW |
240 | * @param string $tableName |
241 | * @param string $fkTableName | |
b5c2afd0 EM |
242 | * |
243 | * @return bool | |
244 | */ | |
00be9182 | 245 | public static function changeFKConstraint($tableName, $fkTableName) { |
6a488035 TO |
246 | $fkName = "{$tableName}_entity_id"; |
247 | if (strlen($fkName) >= 48) { | |
248 | $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16); | |
249 | } | |
250 | $dropFKSql = " | |
251 | ALTER TABLE {$tableName} | |
252 | DROP FOREIGN KEY `FK_{$fkName}`;"; | |
253 | ||
fccb50a6 | 254 | CRM_Core_DAO::executeQuery($dropFKSql); |
6a488035 TO |
255 | |
256 | $addFKSql = " | |
257 | ALTER TABLE {$tableName} | |
258 | ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;"; | |
259 | // CRM-7007: do not i18n-rewrite this query | |
be2fb01f | 260 | CRM_Core_DAO::executeQuery($addFKSql, [], TRUE, NULL, FALSE, FALSE); |
6a488035 TO |
261 | |
262 | return TRUE; | |
263 | } | |
264 | ||
b5c2afd0 | 265 | /** |
c490a46a | 266 | * @param array $params |
b5c2afd0 EM |
267 | * @param $separator |
268 | * @param $prefix | |
100fef9d | 269 | * @param string $tableName |
b5c2afd0 | 270 | * |
e60f24eb | 271 | * @return NULL|string |
b5c2afd0 | 272 | */ |
4f89ebb0 | 273 | public static function buildForeignKeySQL($params, $separator, $prefix, $tableName) { |
6a488035 | 274 | $sql = NULL; |
8cc574cf | 275 | if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) { |
6a488035 TO |
276 | $sql .= $separator; |
277 | $sql .= str_repeat(' ', 8); | |
278 | $sql .= $prefix; | |
279 | $fkName = "{$tableName}_{$params['name']}"; | |
280 | if (strlen($fkName) >= 48) { | |
281 | $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16); | |
282 | } | |
283 | ||
284 | $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) "; | |
285 | $sql .= CRM_Utils_Array::value('fk_attributes', $params); | |
286 | } | |
287 | return $sql; | |
288 | } | |
289 | ||
b5c2afd0 | 290 | /** |
ead0c08f | 291 | * @deprecated |
292 | * | |
c490a46a | 293 | * @param array $params |
b5c2afd0 EM |
294 | * @param bool $indexExist |
295 | * @param bool $triggerRebuild | |
296 | * | |
297 | * @return bool | |
298 | */ | |
6477f387 | 299 | public static function alterFieldSQL($params, $indexExist = FALSE, $triggerRebuild = TRUE) { |
3111965c | 300 | CRM_Core_Error::deprecatedFunctionWarning('function no longer in use / supported'); |
6a488035 TO |
301 | // lets suppress the required flag, since that can cause sql issue |
302 | $params['required'] = FALSE; | |
303 | ||
d992b2f5 | 304 | $sql = self::buildFieldChangeSql($params, $indexExist); |
6a488035 TO |
305 | |
306 | // CRM-7007: do not i18n-rewrite this query | |
be2fb01f | 307 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE); |
6a488035 TO |
308 | |
309 | $config = CRM_Core_Config::singleton(); | |
310 | if ($config->logging) { | |
721a43a4 EM |
311 | // CRM-16717 not sure why this was originally limited to add. |
312 | // For example custom tables can have field length changes - which need to flow through to logging. | |
313 | // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?) | |
314 | if ($params['operation'] == 'add' || $params['operation'] == 'modify') { | |
8d7a9d07 | 315 | $logging = new CRM_Logging_Schema(); |
fcc20cec | 316 | $logging->fixSchemaDifferencesFor($params['table_name'], [trim(strtoupper($params['operation'])) => [$params['name']]]); |
6a488035 TO |
317 | } |
318 | } | |
319 | ||
22e263ad | 320 | if ($triggerRebuild) { |
dfcc817d | 321 | Civi::service('sql_triggers')->rebuild($params['table_name'], TRUE); |
6a488035 TO |
322 | } |
323 | ||
324 | return TRUE; | |
325 | } | |
326 | ||
327 | /** | |
49186f94 | 328 | * Delete a CiviCRM-table. |
6a488035 | 329 | * |
d3e86119 | 330 | * @param string $tableName |
6a0b768e | 331 | * Name of the table to be created. |
6a488035 | 332 | */ |
00be9182 | 333 | public static function dropTable($tableName) { |
6a488035 | 334 | $sql = "DROP TABLE $tableName"; |
49186f94 | 335 | CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
336 | } |
337 | ||
b5c2afd0 | 338 | /** |
100fef9d CW |
339 | * @param string $tableName |
340 | * @param string $columnName | |
3abab0f8 | 341 | * @param bool $l18n |
41ace555 | 342 | * @param bool $isUpgradeMode |
87568e34 | 343 | * |
b5c2afd0 | 344 | */ |
41ace555 | 345 | public static function dropColumn($tableName, $columnName, $l18n = FALSE, $isUpgradeMode = FALSE) { |
242055d3 CW |
346 | if (self::checkIfFieldExists($tableName, $columnName)) { |
347 | $sql = "ALTER TABLE $tableName DROP COLUMN $columnName"; | |
3abab0f8 | 348 | if ($l18n) { |
87568e34 SL |
349 | CRM_Core_DAO::executeQuery($sql); |
350 | } | |
351 | else { | |
be2fb01f | 352 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, FALSE, FALSE); |
87568e34 | 353 | } |
1285e488 SL |
354 | $domain = new CRM_Core_DAO_Domain(); |
355 | $domain->find(TRUE); | |
356 | if ($domain->locales) { | |
357 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
41ace555 | 358 | CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL, $isUpgradeMode); |
1285e488 | 359 | } |
242055d3 | 360 | } |
6a488035 TO |
361 | } |
362 | ||
b5c2afd0 | 363 | /** |
100fef9d | 364 | * @param string $tableName |
b5c2afd0 EM |
365 | * @param bool $dropUnique |
366 | */ | |
00be9182 | 367 | public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) { |
6a488035 TO |
368 | if ($dropUnique) { |
369 | $sql = "ALTER TABLE $tableName | |
370 | DROP INDEX `unique_entity_id` , | |
371 | ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )"; | |
372 | } | |
373 | else { | |
374 | $sql = " ALTER TABLE $tableName | |
375 | DROP INDEX `FK_{$tableName}_entity_id` , | |
376 | ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )"; | |
377 | } | |
49186f94 | 378 | CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
379 | } |
380 | ||
b5c2afd0 | 381 | /** |
7181119b | 382 | * Create indexes. |
383 | * | |
b5c2afd0 | 384 | * @param $tables |
7181119b | 385 | * Tables to create index for in the format: |
386 | * array('civicrm_entity_table' => 'entity_id') | |
387 | * OR | |
388 | * array('civicrm_entity_table' => array('entity_id', 'entity_table')) | |
389 | * The latter will create a combined index on the 2 keys (in order). | |
390 | * | |
391 | * Side note - when creating combined indexes the one with the most variation | |
392 | * goes first - so entity_table always goes after entity_id. | |
393 | * | |
394 | * It probably makes sense to consider more sophisticated options at some point | |
395 | * but at the moment this is only being as enhanced as fast as the test is. | |
396 | * | |
397 | * @todo add support for length & multilingual on combined keys. | |
398 | * | |
b5c2afd0 | 399 | * @param string $createIndexPrefix |
fe891bc6 | 400 | * @param array $substrLengths |
b5c2afd0 | 401 | */ |
be2fb01f CW |
402 | public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLengths = []) { |
403 | $queries = []; | |
8d7a9d07 | 404 | $domain = new CRM_Core_DAO_Domain(); |
6a488035 TO |
405 | $domain->find(TRUE); |
406 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
407 | ||
408 | // if we're multilingual, cache the information on internationalised fields | |
409 | static $columns = NULL; | |
410 | if (!CRM_Utils_System::isNull($locales) and $columns === NULL) { | |
411 | $columns = CRM_Core_I18n_SchemaStructure::columns(); | |
412 | } | |
413 | ||
414 | foreach ($tables as $table => $fields) { | |
415 | $query = "SHOW INDEX FROM $table"; | |
416 | $dao = CRM_Core_DAO::executeQuery($query); | |
417 | ||
be2fb01f | 418 | $currentIndexes = []; |
6a488035 TO |
419 | while ($dao->fetch()) { |
420 | $currentIndexes[] = $dao->Key_name; | |
421 | } | |
422 | ||
423 | // now check for all fields if the index exists | |
424 | foreach ($fields as $field) { | |
7181119b | 425 | $fieldName = implode('_', (array) $field); |
426 | ||
427 | if (is_array($field)) { | |
428 | // No support for these for combined indexes as yet - add a test when you | |
429 | // want to add that. | |
430 | $lengthName = ''; | |
431 | $lengthSize = ''; | |
432 | } | |
433 | else { | |
434 | // handle indices over substrings, CRM-6245 | |
435 | // $lengthName is appended to index name, $lengthSize is the field size modifier | |
fe891bc6 AS |
436 | $lengthName = isset($substrLengths[$table][$fieldName]) ? "_{$substrLengths[$table][$fieldName]}" : ''; |
437 | $lengthSize = isset($substrLengths[$table][$fieldName]) ? "({$substrLengths[$table][$fieldName]})" : ''; | |
7181119b | 438 | } |
6a488035 | 439 | |
be2fb01f | 440 | $names = [ |
7181119b | 441 | "index_{$fieldName}{$lengthName}", |
442 | "FK_{$table}_{$fieldName}{$lengthName}", | |
443 | "UI_{$fieldName}{$lengthName}", | |
444 | "{$createIndexPrefix}_{$fieldName}{$lengthName}", | |
be2fb01f | 445 | ]; |
6a488035 TO |
446 | |
447 | // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126 | |
448 | foreach ($names as $name) { | |
449 | $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/'; | |
450 | if (preg_grep($regex, $currentIndexes)) { | |
451 | continue 2; | |
452 | } | |
453 | } | |
454 | ||
455 | // the index doesn't exist, so create it | |
456 | // if we're multilingual and the field is internationalised, do it for every locale | |
7181119b | 457 | // @todo remove is_array check & add multilingual support for combined indexes and add a test. |
458 | // Note combined indexes currently using this function are on fields like | |
459 | // entity_id + entity_table which are not multilingual. | |
460 | if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) { | |
6a488035 | 461 | foreach ($locales as $locale) { |
7181119b | 462 | $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})"; |
6a488035 TO |
463 | } |
464 | } | |
465 | else { | |
7181119b | 466 | $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})"; |
6a488035 TO |
467 | } |
468 | } | |
469 | } | |
470 | ||
471 | // run the queries without i18n-rewriting | |
8d7a9d07 | 472 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
473 | foreach ($queries as $query) { |
474 | $dao->query($query, FALSE); | |
475 | } | |
476 | } | |
477 | ||
49186f94 AS |
478 | /** |
479 | * Get indexes for tables | |
480 | * @param array $tables | |
481 | * array of table names to find indexes for | |
482 | * | |
483 | * @return array('tableName' => array('index1', 'index2')) | |
484 | */ | |
485 | public static function getIndexes($tables) { | |
be2fb01f | 486 | $indexes = []; |
49186f94 AS |
487 | foreach ($tables as $table) { |
488 | $query = "SHOW INDEX FROM $table"; | |
489 | $dao = CRM_Core_DAO::executeQuery($query); | |
490 | ||
be2fb01f | 491 | $tableIndexes = []; |
49186f94 AS |
492 | while ($dao->fetch()) { |
493 | $tableIndexes[$dao->Key_name]['name'] = $dao->Key_name; | |
494 | $tableIndexes[$dao->Key_name]['field'][] = $dao->Column_name . | |
495 | ($dao->Sub_part ? '(' . $dao->Sub_part . ')' : ''); | |
496 | $tableIndexes[$dao->Key_name]['unique'] = ($dao->Non_unique == 0 ? 1 : 0); | |
497 | } | |
498 | $indexes[$table] = $tableIndexes; | |
49186f94 AS |
499 | } |
500 | return $indexes; | |
501 | } | |
502 | ||
50969d52 | 503 | /** |
504 | * Drop an index if one by that name exists. | |
505 | * | |
506 | * @param string $tableName | |
507 | * @param string $indexName | |
508 | */ | |
509 | public static function dropIndexIfExists($tableName, $indexName) { | |
510 | if (self::checkIfIndexExists($tableName, $indexName)) { | |
511 | CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName"); | |
512 | } | |
513 | } | |
514 | ||
b5c2afd0 | 515 | /** |
100fef9d CW |
516 | * @param int $customFieldID |
517 | * @param string $tableName | |
518 | * @param string $columnName | |
b5c2afd0 EM |
519 | * @param $length |
520 | * | |
521 | * @throws Exception | |
522 | */ | |
00be9182 | 523 | public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) { |
6a488035 TO |
524 | // first update the custom field tables |
525 | $sql = " | |
526 | UPDATE civicrm_custom_field | |
527 | SET text_length = %1 | |
528 | WHERE id = %2 | |
529 | "; | |
be2fb01f CW |
530 | $params = [ |
531 | 1 => [$length, 'Integer'], | |
532 | 2 => [$customFieldID, 'Integer'], | |
533 | ]; | |
6a488035 TO |
534 | CRM_Core_DAO::executeQuery($sql, $params); |
535 | ||
536 | $sql = " | |
537 | SELECT is_required, default_value | |
538 | FROM civicrm_custom_field | |
539 | WHERE id = %2 | |
540 | "; | |
541 | $dao = CRM_Core_DAO::executeQuery($sql, $params); | |
542 | ||
543 | if ($dao->fetch()) { | |
544 | $clause = ''; | |
545 | ||
546 | if ($dao->is_required) { | |
547 | $clause = " NOT NULL"; | |
548 | } | |
549 | ||
550 | if (!empty($dao->default_value)) { | |
551 | $clause .= " DEFAULT '{$dao->default_value}'"; | |
552 | } | |
553 | // now modify the column | |
554 | $sql = " | |
555 | ALTER TABLE {$tableName} | |
556 | MODIFY {$columnName} varchar( $length ) | |
557 | $clause | |
558 | "; | |
559 | CRM_Core_DAO::executeQuery($sql); | |
560 | } | |
561 | else { | |
562 | CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3', | |
be2fb01f | 563 | [ |
353ffa53 TO |
564 | 1 => $tableName, |
565 | 2 => $columnName, | |
566 | 3 => $customFieldID, | |
be2fb01f | 567 | ] |
353ffa53 | 568 | )); |
6a488035 TO |
569 | } |
570 | } | |
96025800 | 571 | |
50969d52 | 572 | /** |
573 | * Check if the table has an index matching the name. | |
574 | * | |
575 | * @param string $tableName | |
576 | * @param array $indexName | |
577 | * | |
49186f94 | 578 | * @return bool |
50969d52 | 579 | */ |
580 | public static function checkIfIndexExists($tableName, $indexName) { | |
581 | $result = CRM_Core_DAO::executeQuery( | |
582 | "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1", | |
be2fb01f | 583 | [1 => [$indexName, 'String']] |
50969d52 | 584 | ); |
585 | if ($result->fetch()) { | |
586 | return TRUE; | |
587 | } | |
588 | return FALSE; | |
589 | } | |
590 | ||
82f5a856 | 591 | /** |
49186f94 | 592 | * Check if the table has a specified column. |
82f5a856 SL |
593 | * |
594 | * @param string $tableName | |
595 | * @param string $columnName | |
eed7e803 CW |
596 | * @param bool $i18nRewrite |
597 | * Whether to rewrite the query on multilingual setups. | |
82f5a856 | 598 | * |
49186f94 | 599 | * @return bool |
82f5a856 | 600 | */ |
eed7e803 | 601 | public static function checkIfFieldExists($tableName, $columnName, $i18nRewrite = TRUE) { |
5e282b34 CW |
602 | $query = "SHOW COLUMNS FROM $tableName LIKE '%1'"; |
603 | $dao = CRM_Core_DAO::executeQuery($query, [1 => [$columnName, 'Alphanumeric']], TRUE, NULL, FALSE, $i18nRewrite); | |
eed7e803 | 604 | $result = $dao->fetch() ? TRUE : FALSE; |
eed7e803 | 605 | return $result; |
82f5a856 SL |
606 | } |
607 | ||
169475b7 | 608 | /** |
f5078cc0 SL |
609 | * Check if a foreign key Exists |
610 | * @param string $table_name | |
611 | * @param string $constraint_name | |
612 | * @return bool TRUE if FK is found | |
169475b7 | 613 | */ |
f5078cc0 | 614 | public static function checkFKExists($table_name, $constraint_name) { |
169475b7 SL |
615 | $config = CRM_Core_Config::singleton(); |
616 | $dbUf = DB::parseDSN($config->dsn); | |
617 | $query = " | |
618 | SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS | |
619 | WHERE TABLE_SCHEMA = %1 | |
620 | AND TABLE_NAME = %2 | |
621 | AND CONSTRAINT_NAME = %3 | |
622 | AND CONSTRAINT_TYPE = 'FOREIGN KEY' | |
623 | "; | |
be2fb01f CW |
624 | $params = [ |
625 | 1 => [$dbUf['database'], 'String'], | |
626 | 2 => [$table_name, 'String'], | |
627 | 3 => [$constraint_name, 'String'], | |
628 | ]; | |
169475b7 SL |
629 | $dao = CRM_Core_DAO::executeQuery($query, $params); |
630 | ||
631 | if ($dao->fetch()) { | |
27e82c24 | 632 | return TRUE; |
f5078cc0 | 633 | } |
27e82c24 | 634 | return FALSE; |
f5078cc0 SL |
635 | } |
636 | ||
637 | /** | |
638 | * Remove a foreign key from a table if it exists. | |
639 | * | |
640 | * @param $table_name | |
641 | * @param $constraint_name | |
642 | * | |
643 | * @return bool | |
644 | */ | |
645 | public static function safeRemoveFK($table_name, $constraint_name) { | |
27e82c24 | 646 | if (self::checkFKExists($table_name, $constraint_name)) { |
be2fb01f | 647 | CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", []); |
9cd5a579 | 648 | return TRUE; |
169475b7 | 649 | } |
9cd5a579 | 650 | return FALSE; |
169475b7 SL |
651 | } |
652 | ||
6b86d84f AS |
653 | /** |
654 | * Add index signature hash to DAO file calculation. | |
655 | * | |
656 | * @param string $table table name | |
657 | * @param array $indices index array spec | |
658 | */ | |
659 | public static function addIndexSignature($table, &$indices) { | |
660 | foreach ($indices as $indexName => $index) { | |
661 | $indices[$indexName]['sig'] = $table . "::" . | |
662 | (array_key_exists('unique', $index) ? $index['unique'] : 0) . "::" . | |
663 | implode("::", $index['field']); | |
664 | } | |
665 | } | |
666 | ||
49186f94 AS |
667 | /** |
668 | * Compare the indices specified in the XML files with those in the DB. | |
669 | * | |
3d4602c3 JP |
670 | * @param bool $dropFalseIndices |
671 | * If set - this function deletes false indices present in the DB which mismatches the expected | |
672 | * values of xml file so that civi re-creates them with correct values using createMissingIndices() function. | |
673 | * | |
49186f94 AS |
674 | * @return array |
675 | * index specifications | |
676 | */ | |
3d4602c3 | 677 | public static function getMissingIndices($dropFalseIndices = FALSE) { |
be2fb01f | 678 | $requiredSigs = $existingSigs = []; |
49186f94 AS |
679 | // Get the indices defined (originally) in the xml files |
680 | $requiredIndices = CRM_Core_DAO_AllCoreTables::indices(); | |
be2fb01f | 681 | $reqSigs = []; |
49186f94 AS |
682 | foreach ($requiredIndices as $table => $indices) { |
683 | $reqSigs[] = CRM_Utils_Array::collect('sig', $indices); | |
684 | } | |
685 | CRM_Utils_Array::flatten($reqSigs, $requiredSigs); | |
686 | ||
687 | // Get the indices in the database | |
688 | $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices)); | |
be2fb01f | 689 | $extSigs = []; |
49186f94 AS |
690 | foreach ($existingIndices as $table => $indices) { |
691 | CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices); | |
692 | $extSigs[] = CRM_Utils_Array::collect('sig', $indices); | |
693 | } | |
694 | CRM_Utils_Array::flatten($extSigs, $existingSigs); | |
695 | ||
696 | // Compare | |
697 | $missingSigs = array_diff($requiredSigs, $existingSigs); | |
f4f835ed | 698 | |
3d4602c3 | 699 | //CRM-20774 - Drop index key which exist in db but the value varies. |
f4f835ed | 700 | $existingKeySigs = array_intersect_key($missingSigs, $existingSigs); |
3d4602c3 | 701 | if ($dropFalseIndices && !empty($existingKeySigs)) { |
f4f835ed JP |
702 | foreach ($existingKeySigs as $sig) { |
703 | $sigParts = explode('::', $sig); | |
704 | foreach ($requiredIndices[$sigParts[0]] as $index) { | |
3d4602c3 JP |
705 | if ($index['sig'] == $sig && !empty($index['name'])) { |
706 | self::dropIndexIfExists($sigParts[0], $index['name']); | |
f4f835ed JP |
707 | continue; |
708 | } | |
709 | } | |
710 | } | |
711 | } | |
712 | ||
49186f94 | 713 | // Get missing indices |
be2fb01f | 714 | $missingIndices = []; |
49186f94 AS |
715 | foreach ($missingSigs as $sig) { |
716 | $sigParts = explode('::', $sig); | |
f3490f40 | 717 | if (array_key_exists($sigParts[0], $requiredIndices)) { |
718 | foreach ($requiredIndices[$sigParts[0]] as $index) { | |
719 | if ($index['sig'] == $sig) { | |
720 | $missingIndices[$sigParts[0]][] = $index; | |
721 | continue; | |
722 | } | |
49186f94 AS |
723 | } |
724 | } | |
725 | } | |
3d4602c3 | 726 | return $missingIndices; |
49186f94 AS |
727 | } |
728 | ||
729 | /** | |
730 | * Create missing indices. | |
731 | * | |
732 | * @param array $missingIndices as returned by getMissingIndices() | |
733 | */ | |
734 | public static function createMissingIndices($missingIndices) { | |
be2fb01f | 735 | $queries = []; |
49186f94 AS |
736 | foreach ($missingIndices as $table => $indexList) { |
737 | foreach ($indexList as $index) { | |
738 | $queries[] = "CREATE " . | |
739 | (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') . | |
740 | "INDEX {$index['name']} ON {$table} (" . | |
741 | implode(", ", $index['field']) . | |
742 | ")"; | |
743 | } | |
744 | } | |
745 | ||
746 | /* FIXME potential problem if index name already exists, so check before creating */ | |
747 | $dao = new CRM_Core_DAO(); | |
748 | foreach ($queries as $query) { | |
749 | $dao->query($query, FALSE); | |
750 | } | |
49186f94 AS |
751 | } |
752 | ||
d992b2f5 | 753 | /** |
754 | * Build the sql to alter the field. | |
755 | * | |
756 | * @param array $params | |
757 | * @param bool $indexExist | |
758 | * | |
759 | * @return string | |
760 | */ | |
761 | public static function buildFieldChangeSql($params, $indexExist) { | |
762 | $sql = str_repeat(' ', 8); | |
763 | $sql .= "ALTER TABLE {$params['table_name']}"; | |
8e2949c4 | 764 | return $sql . self::getFieldAlterSQL($params, $indexExist); |
765 | } | |
766 | ||
767 | /** | |
768 | * Get the sql to alter an individual field. | |
769 | * | |
770 | * This will need to have an ALTER TABLE statement appended but by getting | |
771 | * by individual field we can do one or many. | |
772 | * | |
773 | * @param array $params | |
774 | * @param bool $indexExist | |
775 | * | |
776 | * @return string | |
777 | */ | |
778 | public static function getFieldAlterSQL($params, $indexExist) { | |
779 | $sql = ''; | |
d992b2f5 | 780 | switch ($params['operation']) { |
781 | case 'add': | |
782 | $separator = "\n"; | |
783 | $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN "); | |
784 | $separator = ",\n"; | |
785 | $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY "); | |
786 | $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX "); | |
787 | $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']); | |
788 | break; | |
789 | ||
790 | case 'modify': | |
791 | $separator = "\n"; | |
792 | $sql .= self::buildFieldSQL($params, $separator, "MODIFY "); | |
793 | $separator = ",\n"; | |
794 | $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist); | |
795 | break; | |
796 | ||
797 | case 'delete': | |
798 | $sql .= " DROP COLUMN `{$params['name']}`"; | |
799 | if (!empty($params['primary'])) { | |
800 | $sql .= ", DROP PRIMARY KEY"; | |
801 | } | |
802 | if (!empty($params['fk_table_name'])) { | |
803 | $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}"; | |
804 | } | |
805 | break; | |
806 | } | |
807 | return $sql; | |
808 | } | |
809 | ||
6a488035 | 810 | } |