Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
7e9e8871 | 4 | | CiviCRM version 4.7 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
fa938177 | 6 | | Copyright CiviCRM LLC (c) 2004-2016 | |
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 | |
fa938177 | 31 | * @copyright CiviCRM LLC (c) 2004-2016 |
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 | */ |
00be9182 | 67 | public static function createTable(&$params) { |
6a488035 TO |
68 | $sql = self::buildTableSQL($params); |
69 | // do not i18n-rewrite | |
70 | $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE); | |
71 | $dao->free(); | |
72 | ||
73 | $config = CRM_Core_Config::singleton(); | |
74 | if ($config->logging) { | |
75 | // logging support | |
8d7a9d07 | 76 | $logging = new CRM_Logging_Schema(); |
e60f24eb | 77 | $logging->fixSchemaDifferencesFor($params['name'], NULL, FALSE); |
6a488035 TO |
78 | } |
79 | ||
80 | // always do a trigger rebuild for this table | |
81 | CRM_Core_DAO::triggerRebuild($params['name']); | |
82 | ||
83 | return TRUE; | |
84 | } | |
85 | ||
b5c2afd0 | 86 | /** |
c490a46a | 87 | * @param array $params |
b5c2afd0 EM |
88 | * |
89 | * @return string | |
90 | */ | |
00be9182 | 91 | public static function buildTableSQL(&$params) { |
6a488035 TO |
92 | $sql = "CREATE TABLE {$params['name']} ("; |
93 | if (isset($params['fields']) && | |
94 | is_array($params['fields']) | |
95 | ) { | |
96 | $separator = "\n"; | |
97 | $prefix = NULL; | |
98 | foreach ($params['fields'] as $field) { | |
99 | $sql .= self::buildFieldSQL($field, $separator, $prefix); | |
100 | $separator = ",\n"; | |
101 | } | |
102 | foreach ($params['fields'] as $field) { | |
103 | $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix); | |
104 | } | |
105 | foreach ($params['fields'] as $field) { | |
106 | $sql .= self::buildSearchIndexSQL($field, $separator, $prefix); | |
107 | } | |
108 | if (isset($params['indexes'])) { | |
109 | foreach ($params['indexes'] as $index) { | |
110 | $sql .= self::buildIndexSQL($index, $separator, $prefix); | |
111 | } | |
112 | } | |
113 | foreach ($params['fields'] as $field) { | |
114 | $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']); | |
115 | } | |
116 | } | |
117 | $sql .= "\n) {$params['attributes']};"; | |
118 | return $sql; | |
119 | } | |
120 | ||
b5c2afd0 | 121 | /** |
c490a46a | 122 | * @param array $params |
b5c2afd0 EM |
123 | * @param $separator |
124 | * @param $prefix | |
125 | * | |
126 | * @return string | |
127 | */ | |
00be9182 | 128 | public static function buildFieldSQL(&$params, $separator, $prefix) { |
6a488035 TO |
129 | $sql = ''; |
130 | $sql .= $separator; | |
131 | $sql .= str_repeat(' ', 8); | |
132 | $sql .= $prefix; | |
133 | $sql .= "`{$params['name']}` {$params['type']}"; | |
134 | ||
a7488080 | 135 | if (!empty($params['required'])) { |
6a488035 TO |
136 | $sql .= " NOT NULL"; |
137 | } | |
138 | ||
a7488080 | 139 | if (!empty($params['attributes'])) { |
6a488035 TO |
140 | $sql .= " {$params['attributes']}"; |
141 | } | |
142 | ||
a7488080 | 143 | if (!empty($params['default']) && |
6a488035 TO |
144 | $params['type'] != 'text' |
145 | ) { | |
146 | $sql .= " DEFAULT {$params['default']}"; | |
147 | } | |
148 | ||
a7488080 | 149 | if (!empty($params['comment'])) { |
6a488035 TO |
150 | $sql .= " COMMENT '{$params['comment']}'"; |
151 | } | |
152 | ||
153 | return $sql; | |
154 | } | |
155 | ||
b5c2afd0 | 156 | /** |
c490a46a | 157 | * @param array $params |
b5c2afd0 EM |
158 | * @param $separator |
159 | * @param $prefix | |
160 | * | |
e60f24eb | 161 | * @return NULL|string |
b5c2afd0 | 162 | */ |
00be9182 | 163 | public static function buildPrimaryKeySQL(&$params, $separator, $prefix) { |
6a488035 | 164 | $sql = NULL; |
a7488080 | 165 | if (!empty($params['primary'])) { |
6a488035 TO |
166 | $sql .= $separator; |
167 | $sql .= str_repeat(' ', 8); | |
168 | $sql .= $prefix; | |
169 | $sql .= "PRIMARY KEY ( {$params['name']} )"; | |
170 | } | |
171 | return $sql; | |
172 | } | |
173 | ||
b5c2afd0 | 174 | /** |
c490a46a | 175 | * @param array $params |
b5c2afd0 EM |
176 | * @param $separator |
177 | * @param $prefix | |
178 | * @param bool $indexExist | |
179 | * | |
e60f24eb | 180 | * @return NULL|string |
b5c2afd0 | 181 | */ |
00be9182 | 182 | public static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) { |
6a488035 TO |
183 | $sql = NULL; |
184 | ||
185 | // dont index blob | |
186 | if ($params['type'] == 'text') { | |
187 | return $sql; | |
188 | } | |
189 | ||
190 | //create index only for searchable fields during ADD, | |
191 | //create index only if field is become searchable during MODIFY, | |
192 | //drop index only if field is no more searchable and index was exist. | |
a7488080 | 193 | if (!empty($params['searchable']) && !$indexExist) { |
6a488035 TO |
194 | $sql .= $separator; |
195 | $sql .= str_repeat(' ', 8); | |
196 | $sql .= $prefix; | |
197 | $sql .= "INDEX_{$params['name']} ( {$params['name']} )"; | |
198 | } | |
a7488080 | 199 | elseif (empty($params['searchable']) && $indexExist) { |
6a488035 TO |
200 | $sql .= $separator; |
201 | $sql .= str_repeat(' ', 8); | |
202 | $sql .= "DROP INDEX INDEX_{$params['name']}"; | |
203 | } | |
204 | return $sql; | |
205 | } | |
206 | ||
b5c2afd0 | 207 | /** |
c490a46a | 208 | * @param array $params |
b5c2afd0 EM |
209 | * @param $separator |
210 | * @param $prefix | |
211 | * | |
212 | * @return string | |
213 | */ | |
00be9182 | 214 | public static function buildIndexSQL(&$params, $separator, $prefix) { |
6a488035 TO |
215 | $sql = ''; |
216 | $sql .= $separator; | |
217 | $sql .= str_repeat(' ', 8); | |
218 | if ($params['unique']) { | |
219 | $sql .= 'UNIQUE INDEX'; | |
220 | $indexName = 'unique'; | |
221 | } | |
222 | else { | |
223 | $sql .= 'INDEX'; | |
224 | $indexName = 'index'; | |
225 | } | |
226 | $indexFields = NULL; | |
227 | ||
228 | foreach ($params as $name => $value) { | |
229 | if (substr($name, 0, 11) == 'field_name_') { | |
230 | $indexName .= "_{$value}"; | |
231 | $indexFields .= " $value,"; | |
232 | } | |
233 | } | |
234 | $indexFields = substr($indexFields, 0, -1); | |
235 | ||
236 | $sql .= " $indexName ( $indexFields )"; | |
237 | return $sql; | |
238 | } | |
239 | ||
b5c2afd0 | 240 | /** |
100fef9d CW |
241 | * @param string $tableName |
242 | * @param string $fkTableName | |
b5c2afd0 EM |
243 | * |
244 | * @return bool | |
245 | */ | |
00be9182 | 246 | public static function changeFKConstraint($tableName, $fkTableName) { |
6a488035 TO |
247 | $fkName = "{$tableName}_entity_id"; |
248 | if (strlen($fkName) >= 48) { | |
249 | $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16); | |
250 | } | |
251 | $dropFKSql = " | |
252 | ALTER TABLE {$tableName} | |
253 | DROP FOREIGN KEY `FK_{$fkName}`;"; | |
254 | ||
255 | $dao = CRM_Core_DAO::executeQuery($dropFKSql); | |
256 | $dao->free(); | |
257 | ||
258 | $addFKSql = " | |
259 | ALTER TABLE {$tableName} | |
260 | ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;"; | |
261 | // CRM-7007: do not i18n-rewrite this query | |
262 | $dao = CRM_Core_DAO::executeQuery($addFKSql, array(), TRUE, NULL, FALSE, FALSE); | |
263 | $dao->free(); | |
264 | ||
265 | return TRUE; | |
266 | } | |
267 | ||
b5c2afd0 | 268 | /** |
c490a46a | 269 | * @param array $params |
b5c2afd0 EM |
270 | * @param $separator |
271 | * @param $prefix | |
100fef9d | 272 | * @param string $tableName |
b5c2afd0 | 273 | * |
e60f24eb | 274 | * @return NULL|string |
b5c2afd0 | 275 | */ |
00be9182 | 276 | public static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) { |
6a488035 | 277 | $sql = NULL; |
8cc574cf | 278 | if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) { |
6a488035 TO |
279 | $sql .= $separator; |
280 | $sql .= str_repeat(' ', 8); | |
281 | $sql .= $prefix; | |
282 | $fkName = "{$tableName}_{$params['name']}"; | |
283 | if (strlen($fkName) >= 48) { | |
284 | $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16); | |
285 | } | |
286 | ||
287 | $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) "; | |
288 | $sql .= CRM_Utils_Array::value('fk_attributes', $params); | |
289 | } | |
290 | return $sql; | |
291 | } | |
292 | ||
b5c2afd0 | 293 | /** |
c490a46a | 294 | * @param array $params |
b5c2afd0 EM |
295 | * @param bool $indexExist |
296 | * @param bool $triggerRebuild | |
297 | * | |
298 | * @return bool | |
299 | */ | |
00be9182 | 300 | public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) { |
6a488035 TO |
301 | $sql = str_repeat(' ', 8); |
302 | $sql .= "ALTER TABLE {$params['table_name']}"; | |
303 | ||
304 | // lets suppress the required flag, since that can cause sql issue | |
305 | $params['required'] = FALSE; | |
306 | ||
307 | switch ($params['operation']) { | |
308 | case 'add': | |
309 | $separator = "\n"; | |
310 | $prefix = "ADD "; | |
311 | $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN "); | |
312 | $separator = ",\n"; | |
313 | $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY "); | |
314 | $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX "); | |
315 | $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']); | |
316 | break; | |
317 | ||
318 | case 'modify': | |
319 | $separator = "\n"; | |
320 | $prefix = "MODIFY "; | |
321 | $sql .= self::buildFieldSQL($params, $separator, $prefix); | |
322 | $separator = ",\n"; | |
323 | $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist); | |
324 | break; | |
325 | ||
326 | case 'delete': | |
327 | $sql .= " DROP COLUMN `{$params['name']}`"; | |
a7488080 | 328 | if (!empty($params['primary'])) { |
6a488035 TO |
329 | $sql .= ", DROP PRIMARY KEY"; |
330 | } | |
a7488080 | 331 | if (!empty($params['fk_table_name'])) { |
6a488035 TO |
332 | $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}"; |
333 | } | |
334 | break; | |
335 | } | |
336 | ||
337 | // CRM-7007: do not i18n-rewrite this query | |
338 | $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE); | |
339 | $dao->free(); | |
340 | ||
341 | $config = CRM_Core_Config::singleton(); | |
342 | if ($config->logging) { | |
721a43a4 EM |
343 | // CRM-16717 not sure why this was originally limited to add. |
344 | // For example custom tables can have field length changes - which need to flow through to logging. | |
345 | // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?) | |
346 | if ($params['operation'] == 'add' || $params['operation'] == 'modify') { | |
8d7a9d07 | 347 | $logging = new CRM_Logging_Schema(); |
5bfc940e | 348 | $logging->fixSchemaDifferencesFor($params['table_name'], array(trim($prefix) => array($params['name'])), FALSE); |
6a488035 TO |
349 | } |
350 | } | |
351 | ||
22e263ad | 352 | if ($triggerRebuild) { |
6a488035 TO |
353 | CRM_Core_DAO::triggerRebuild($params['table_name']); |
354 | } | |
355 | ||
356 | return TRUE; | |
357 | } | |
358 | ||
359 | /** | |
8d7a9d07 | 360 | * Delete a CiviCRM-table |
6a488035 | 361 | * |
d3e86119 | 362 | * @param string $tableName |
6a0b768e | 363 | * Name of the table to be created. |
6a488035 | 364 | */ |
00be9182 | 365 | public static function dropTable($tableName) { |
6a488035 TO |
366 | $sql = "DROP TABLE $tableName"; |
367 | $dao = CRM_Core_DAO::executeQuery($sql); | |
368 | } | |
369 | ||
b5c2afd0 | 370 | /** |
100fef9d CW |
371 | * @param string $tableName |
372 | * @param string $columnName | |
b5c2afd0 | 373 | */ |
00be9182 | 374 | public static function dropColumn($tableName, $columnName) { |
6a488035 | 375 | $sql = "ALTER TABLE $tableName DROP COLUMN $columnName"; |
50969d52 | 376 | CRM_Core_DAO::executeQuery($sql); |
6a488035 TO |
377 | } |
378 | ||
b5c2afd0 | 379 | /** |
100fef9d | 380 | * @param string $tableName |
b5c2afd0 EM |
381 | * @param bool $dropUnique |
382 | */ | |
00be9182 | 383 | public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) { |
6a488035 TO |
384 | if ($dropUnique) { |
385 | $sql = "ALTER TABLE $tableName | |
386 | DROP INDEX `unique_entity_id` , | |
387 | ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )"; | |
388 | } | |
389 | else { | |
390 | $sql = " ALTER TABLE $tableName | |
391 | DROP INDEX `FK_{$tableName}_entity_id` , | |
392 | ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )"; | |
393 | } | |
394 | $dao = CRM_Core_DAO::executeQuery($sql); | |
395 | } | |
396 | ||
b5c2afd0 | 397 | /** |
7181119b | 398 | * Create indexes. |
399 | * | |
b5c2afd0 | 400 | * @param $tables |
7181119b | 401 | * Tables to create index for in the format: |
402 | * array('civicrm_entity_table' => 'entity_id') | |
403 | * OR | |
404 | * array('civicrm_entity_table' => array('entity_id', 'entity_table')) | |
405 | * The latter will create a combined index on the 2 keys (in order). | |
406 | * | |
407 | * Side note - when creating combined indexes the one with the most variation | |
408 | * goes first - so entity_table always goes after entity_id. | |
409 | * | |
410 | * It probably makes sense to consider more sophisticated options at some point | |
411 | * but at the moment this is only being as enhanced as fast as the test is. | |
412 | * | |
413 | * @todo add support for length & multilingual on combined keys. | |
414 | * | |
b5c2afd0 EM |
415 | * @param string $createIndexPrefix |
416 | * @param array $substrLenghts | |
417 | */ | |
50969d52 | 418 | public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLenghts = array()) { |
6a488035 | 419 | $queries = array(); |
8d7a9d07 | 420 | $domain = new CRM_Core_DAO_Domain(); |
6a488035 TO |
421 | $domain->find(TRUE); |
422 | $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales); | |
423 | ||
424 | // if we're multilingual, cache the information on internationalised fields | |
425 | static $columns = NULL; | |
426 | if (!CRM_Utils_System::isNull($locales) and $columns === NULL) { | |
427 | $columns = CRM_Core_I18n_SchemaStructure::columns(); | |
428 | } | |
429 | ||
430 | foreach ($tables as $table => $fields) { | |
431 | $query = "SHOW INDEX FROM $table"; | |
432 | $dao = CRM_Core_DAO::executeQuery($query); | |
433 | ||
434 | $currentIndexes = array(); | |
435 | while ($dao->fetch()) { | |
436 | $currentIndexes[] = $dao->Key_name; | |
437 | } | |
438 | ||
439 | // now check for all fields if the index exists | |
440 | foreach ($fields as $field) { | |
7181119b | 441 | $fieldName = implode('_', (array) $field); |
442 | ||
443 | if (is_array($field)) { | |
444 | // No support for these for combined indexes as yet - add a test when you | |
445 | // want to add that. | |
446 | $lengthName = ''; | |
447 | $lengthSize = ''; | |
448 | } | |
449 | else { | |
450 | // handle indices over substrings, CRM-6245 | |
451 | // $lengthName is appended to index name, $lengthSize is the field size modifier | |
d23efff4 | 452 | $lengthName = isset($substrLenghts[$table][$fieldName]) ? "_{$substrLenghts[$table][$fieldName]}" : ''; |
7181119b | 453 | $lengthSize = isset($substrLenghts[$table][$fieldName]) ? "({$substrLenghts[$table][$fieldName]})" : ''; |
454 | } | |
6a488035 | 455 | |
353ffa53 | 456 | $names = array( |
7181119b | 457 | "index_{$fieldName}{$lengthName}", |
458 | "FK_{$table}_{$fieldName}{$lengthName}", | |
459 | "UI_{$fieldName}{$lengthName}", | |
460 | "{$createIndexPrefix}_{$fieldName}{$lengthName}", | |
353ffa53 | 461 | ); |
6a488035 TO |
462 | |
463 | // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126 | |
464 | foreach ($names as $name) { | |
465 | $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/'; | |
466 | if (preg_grep($regex, $currentIndexes)) { | |
467 | continue 2; | |
468 | } | |
469 | } | |
470 | ||
471 | // the index doesn't exist, so create it | |
472 | // if we're multilingual and the field is internationalised, do it for every locale | |
7181119b | 473 | // @todo remove is_array check & add multilingual support for combined indexes and add a test. |
474 | // Note combined indexes currently using this function are on fields like | |
475 | // entity_id + entity_table which are not multilingual. | |
476 | if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) { | |
6a488035 | 477 | foreach ($locales as $locale) { |
7181119b | 478 | $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})"; |
6a488035 TO |
479 | } |
480 | } | |
481 | else { | |
7181119b | 482 | $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})"; |
6a488035 TO |
483 | } |
484 | } | |
485 | } | |
486 | ||
487 | // run the queries without i18n-rewriting | |
8d7a9d07 | 488 | $dao = new CRM_Core_DAO(); |
6a488035 TO |
489 | foreach ($queries as $query) { |
490 | $dao->query($query, FALSE); | |
491 | } | |
492 | } | |
493 | ||
50969d52 | 494 | /** |
495 | * Drop an index if one by that name exists. | |
496 | * | |
497 | * @param string $tableName | |
498 | * @param string $indexName | |
499 | */ | |
500 | public static function dropIndexIfExists($tableName, $indexName) { | |
501 | if (self::checkIfIndexExists($tableName, $indexName)) { | |
502 | CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName"); | |
503 | } | |
504 | } | |
505 | ||
b5c2afd0 | 506 | /** |
100fef9d CW |
507 | * @param int $customFieldID |
508 | * @param string $tableName | |
509 | * @param string $columnName | |
b5c2afd0 EM |
510 | * @param $length |
511 | * | |
512 | * @throws Exception | |
513 | */ | |
00be9182 | 514 | public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) { |
6a488035 TO |
515 | // first update the custom field tables |
516 | $sql = " | |
517 | UPDATE civicrm_custom_field | |
518 | SET text_length = %1 | |
519 | WHERE id = %2 | |
520 | "; | |
6ea503d4 TO |
521 | $params = array( |
522 | 1 => array($length, 'Integer'), | |
6a488035 TO |
523 | 2 => array($customFieldID, 'Integer'), |
524 | ); | |
525 | CRM_Core_DAO::executeQuery($sql, $params); | |
526 | ||
527 | $sql = " | |
528 | SELECT is_required, default_value | |
529 | FROM civicrm_custom_field | |
530 | WHERE id = %2 | |
531 | "; | |
532 | $dao = CRM_Core_DAO::executeQuery($sql, $params); | |
533 | ||
534 | if ($dao->fetch()) { | |
535 | $clause = ''; | |
536 | ||
537 | if ($dao->is_required) { | |
538 | $clause = " NOT NULL"; | |
539 | } | |
540 | ||
541 | if (!empty($dao->default_value)) { | |
542 | $clause .= " DEFAULT '{$dao->default_value}'"; | |
543 | } | |
544 | // now modify the column | |
545 | $sql = " | |
546 | ALTER TABLE {$tableName} | |
547 | MODIFY {$columnName} varchar( $length ) | |
548 | $clause | |
549 | "; | |
550 | CRM_Core_DAO::executeQuery($sql); | |
551 | } | |
552 | else { | |
553 | CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3', | |
353ffa53 TO |
554 | array( |
555 | 1 => $tableName, | |
556 | 2 => $columnName, | |
557 | 3 => $customFieldID, | |
558 | ) | |
559 | )); | |
6a488035 TO |
560 | } |
561 | } | |
96025800 | 562 | |
50969d52 | 563 | /** |
564 | * Check if the table has an index matching the name. | |
565 | * | |
566 | * @param string $tableName | |
567 | * @param array $indexName | |
568 | * | |
569 | * @return \CRM_Core_DAO|object | |
570 | */ | |
571 | public static function checkIfIndexExists($tableName, $indexName) { | |
572 | $result = CRM_Core_DAO::executeQuery( | |
573 | "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1", | |
574 | array(1 => array($indexName, 'String')) | |
575 | ); | |
576 | if ($result->fetch()) { | |
577 | return TRUE; | |
578 | } | |
579 | return FALSE; | |
580 | } | |
581 | ||
82f5a856 SL |
582 | /** |
583 | * Check if the table has a specified column | |
584 | * | |
585 | * @param string $tableName | |
586 | * @param string $columnName | |
587 | * | |
588 | * @return \CRM_Core_DAO|object | |
589 | */ | |
590 | public static function checkIfFieldExists($tableName, $columnName) { | |
591 | $result = CRM_Core_DAO::executeQuery( | |
79e67687 | 592 | "SHOW COLUMNS FROM $tableName LIKE %1", |
4a77ea3a | 593 | array(1 => array($columnName, 'String')) |
82f5a856 SL |
594 | ); |
595 | if ($result->fetch()) { | |
596 | return TRUE; | |
597 | } | |
598 | return FALSE; | |
599 | } | |
600 | ||
169475b7 SL |
601 | /** |
602 | * Remove a foreign key from a table if it exists | |
603 | * | |
604 | * @param $table_name | |
605 | * @param $constraint_name | |
606 | */ | |
607 | public static function safeRemoveFK($table_name, $constraint_name) { | |
608 | ||
609 | $config = CRM_Core_Config::singleton(); | |
610 | $dbUf = DB::parseDSN($config->dsn); | |
611 | $query = " | |
612 | SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS | |
613 | WHERE TABLE_SCHEMA = %1 | |
614 | AND TABLE_NAME = %2 | |
615 | AND CONSTRAINT_NAME = %3 | |
616 | AND CONSTRAINT_TYPE = 'FOREIGN KEY' | |
617 | "; | |
618 | $params = array( | |
619 | 1 => array($dbUf['database'], 'String'), | |
620 | 2 => array($table_name, 'String'), | |
621 | 3 => array($constraint_name, 'String'), | |
622 | ); | |
623 | $dao = CRM_Core_DAO::executeQuery($query, $params); | |
624 | ||
625 | if ($dao->fetch()) { | |
626 | CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", array()); | |
9cd5a579 | 627 | return TRUE; |
169475b7 | 628 | } |
9cd5a579 | 629 | return FALSE; |
169475b7 SL |
630 | } |
631 | ||
6a488035 | 632 | } |