CRM/Core/BAO/SchemaHandler.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35
  36 /**
  37  *  This file contains functions for creating and altering CiviCRM-tables.
  38  */
  39
  40 /**
  41  * structure, similar to what is used in GenCode.php
  42  *
  43  * $table = array(
  44     'name'       => TABLE_NAME,
  45  *                'attributes' => ATTRIBUTES,
  46  *                'fields'     => array(
  47  *                                      array(
  48     'name'          => FIELD_NAME,
  49  *                                             'type'          => FIELD_SQL_TYPE,
  50  // can be field, index, constraint
  51  *                                             'class'         => FIELD_CLASS_TYPE,
  52  *                                             'primary'       => BOOLEAN,
  53  *                                             'required'      => BOOLEAN,
  54  *                                             'searchable'    => true,
  55  *                                             'fk_table_name' => FOREIGN_KEY_TABLE_NAME,
  56  *                                             'fk_field_name' => FOREIGN_KEY_FIELD_NAME,
  57  *                                             'comment'       => COMMENT,
  58  *                                             'default'       => DEFAULT, )
  59  *                                      ...
  60  *                                      ) );
  61  */
  62 class CRM_Core_BAO_SchemaHandler {
  63
  64   /**
  65    * Create a CiviCRM-table
  66    *
  67    * @param array $params
  68    *
  69    * @return true if successfully created, false otherwise
  70    *
  71    * @static
  72    * @access public
  73    */
  74   static function createTable(&$params) {
  75     $sql = self::buildTableSQL($params);
  76     // do not i18n-rewrite
  77     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
  78     $dao->free();
  79
  80     $config = CRM_Core_Config::singleton();
  81     if ($config->logging) {
  82       // logging support
  83       $logging = new CRM_Logging_Schema;
  84       $logging->fixSchemaDifferencesFor($params['name'], null, FALSE);
  85     }
  86
  87     // always do a trigger rebuild for this table
  88     CRM_Core_DAO::triggerRebuild($params['name']);
  89
  90     return TRUE;
  91   }
  92
  93   /**
  94    * @param array $params
  95    *
  96    * @return string
  97    */
  98   static function buildTableSQL(&$params) {
  99     $sql = "CREATE TABLE {$params['name']} (";
 100     if (isset($params['fields']) &&
 101       is_array($params['fields'])
 102     ) {
 103       $separator = "\n";
 104       $prefix = NULL;
 105       foreach ($params['fields'] as $field) {
 106         $sql .= self::buildFieldSQL($field, $separator, $prefix);
 107         $separator = ",\n";
 108       }
 109       foreach ($params['fields'] as $field) {
 110         $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix);
 111       }
 112       foreach ($params['fields'] as $field) {
 113         $sql .= self::buildSearchIndexSQL($field, $separator, $prefix);
 114       }
 115       if (isset($params['indexes'])) {
 116         foreach ($params['indexes'] as $index) {
 117           $sql .= self::buildIndexSQL($index, $separator, $prefix);
 118         }
 119       }
 120       foreach ($params['fields'] as $field) {
 121         $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']);
 122       }
 123     }
 124     $sql .= "\n) {$params['attributes']};";
 125     return $sql;
 126   }
 127
 128   /**
 129    * @param array $params
 130    * @param $separator
 131    * @param $prefix
 132    *
 133    * @return string
 134    */
 135   static function buildFieldSQL(&$params, $separator, $prefix) {
 136     $sql = '';
 137     $sql .= $separator;
 138     $sql .= str_repeat(' ', 8);
 139     $sql .= $prefix;
 140     $sql .= "`{$params['name']}` {$params['type']}";
 141
 142     if (!empty($params['required'])) {
 143       $sql .= " NOT NULL";
 144     }
 145
 146     if (!empty($params['attributes'])) {
 147       $sql .= " {$params['attributes']}";
 148     }
 149
 150     if (!empty($params['default']) &&
 151       $params['type'] != 'text'
 152     ) {
 153       $sql .= " DEFAULT {$params['default']}";
 154     }
 155
 156     if (!empty($params['comment'])) {
 157       $sql .= " COMMENT '{$params['comment']}'";
 158     }
 159
 160     return $sql;
 161   }
 162
 163   /**
 164    * @param array $params
 165    * @param $separator
 166    * @param $prefix
 167    *
 168    * @return null|string
 169    */
 170   static function buildPrimaryKeySQL(&$params, $separator, $prefix) {
 171     $sql = NULL;
 172     if (!empty($params['primary'])) {
 173       $sql .= $separator;
 174       $sql .= str_repeat(' ', 8);
 175       $sql .= $prefix;
 176       $sql .= "PRIMARY KEY ( {$params['name']} )";
 177     }
 178     return $sql;
 179   }
 180
 181   /**
 182    * @param array $params
 183    * @param $separator
 184    * @param $prefix
 185    * @param bool $indexExist
 186    *
 187    * @return null|string
 188    */
 189   static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) {
 190     $sql = NULL;
 191
 192     // dont index blob
 193     if ($params['type'] == 'text') {
 194       return $sql;
 195     }
 196
 197     //create index only for searchable fields during ADD,
 198     //create index only if field is become searchable during MODIFY,
 199     //drop index only if field is no more searchable and index was exist.
 200     if (!empty($params['searchable']) && !$indexExist) {
 201       $sql .= $separator;
 202       $sql .= str_repeat(' ', 8);
 203       $sql .= $prefix;
 204       $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
 205     }
 206     elseif (empty($params['searchable']) && $indexExist) {
 207       $sql .= $separator;
 208       $sql .= str_repeat(' ', 8);
 209       $sql .= "DROP INDEX INDEX_{$params['name']}";
 210     }
 211     return $sql;
 212   }
 213
 214   /**
 215    * @param array $params
 216    * @param $separator
 217    * @param $prefix
 218    *
 219    * @return string
 220    */
 221   static function buildIndexSQL(&$params, $separator, $prefix) {
 222     $sql = '';
 223     $sql .= $separator;
 224     $sql .= str_repeat(' ', 8);
 225     if ($params['unique']) {
 226       $sql .= 'UNIQUE INDEX';
 227       $indexName = 'unique';
 228     }
 229     else {
 230       $sql .= 'INDEX';
 231       $indexName = 'index';
 232     }
 233     $indexFields = NULL;
 234
 235     foreach ($params as $name => $value) {
 236       if (substr($name, 0, 11) == 'field_name_') {
 237         $indexName .= "_{$value}";
 238         $indexFields .= " $value,";
 239       }
 240     }
 241     $indexFields = substr($indexFields, 0, -1);
 242
 243     $sql .= " $indexName ( $indexFields )";
 244     return $sql;
 245   }
 246
 247   /**
 248    * @param string $tableName
 249    * @param string $fkTableName
 250    *
 251    * @return bool
 252    */
 253   static function changeFKConstraint($tableName, $fkTableName) {
 254     $fkName = "{$tableName}_entity_id";
 255     if (strlen($fkName) >= 48) {
 256       $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 257     }
 258     $dropFKSql = "
 259 ALTER TABLE {$tableName}
 260       DROP FOREIGN KEY `FK_{$fkName}`;";
 261
 262     $dao = CRM_Core_DAO::executeQuery($dropFKSql);
 263     $dao->free();
 264
 265     $addFKSql = "
 266 ALTER TABLE {$tableName}
 267       ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;";
 268     // CRM-7007: do not i18n-rewrite this query
 269     $dao = CRM_Core_DAO::executeQuery($addFKSql, array(), TRUE, NULL, FALSE, FALSE);
 270     $dao->free();
 271
 272     return TRUE;
 273   }
 274
 275   /**
 276    * @param array $params
 277    * @param $separator
 278    * @param $prefix
 279    * @param string $tableName
 280    *
 281    * @return null|string
 282    */
 283   static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) {
 284     $sql = NULL;
 285     if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) {
 286       $sql .= $separator;
 287       $sql .= str_repeat(' ', 8);
 288       $sql .= $prefix;
 289       $fkName = "{$tableName}_{$params['name']}";
 290       if (strlen($fkName) >= 48) {
 291         $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 292       }
 293
 294       $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) ";
 295       $sql .= CRM_Utils_Array::value('fk_attributes', $params);
 296     }
 297     return $sql;
 298   }
 299
 300   /**
 301    * @param array $params
 302    * @param bool $indexExist
 303    * @param bool $triggerRebuild
 304    *
 305    * @return bool
 306    */
 307   static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) {
 308     $sql = str_repeat(' ', 8);
 309     $sql .= "ALTER TABLE {$params['table_name']}";
 310
 311     // lets suppress the required flag, since that can cause sql issue
 312     $params['required'] = FALSE;
 313
 314     switch ($params['operation']) {
 315       case 'add':
 316         $separator = "\n";
 317         $prefix = "ADD ";
 318         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 319         $separator = ",\n";
 320         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 321         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 322         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 323         break;
 324
 325       case 'modify':
 326         $separator = "\n";
 327         $prefix = "MODIFY ";
 328         $sql .= self::buildFieldSQL($params, $separator, $prefix);
 329         $separator = ",\n";
 330         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
 331         break;
 332
 333       case 'delete':
 334         $sql .= " DROP COLUMN `{$params['name']}`";
 335         if (!empty($params['primary'])) {
 336           $sql .= ", DROP PRIMARY KEY";
 337         }
 338         if (!empty($params['fk_table_name'])) {
 339           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 340         }
 341         break;
 342     }
 343
 344     // CRM-7007: do not i18n-rewrite this query
 345     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
 346     $dao->free();
 347
 348     $config = CRM_Core_Config::singleton();
 349     if ($config->logging) {
 350       // logging support: if we’re adding a column (but only then!) make sure the potential relevant log table gets a column as well
 351       if ($params['operation'] == 'add') {
 352         $logging = new CRM_Logging_Schema;
 353         $logging->fixSchemaDifferencesFor($params['table_name'], array('ADD' => array($params['name'])), FALSE);
 354       }
 355     }
 356
 357     if($triggerRebuild) {
 358       CRM_Core_DAO::triggerRebuild($params['table_name']);
 359     }
 360
 361     return TRUE;
 362   }
 363
 364   /**
 365    * Delete a civiCRM-table
 366    *
 367    * @param  String  $tableName   name of the table to be created.
 368    *
 369    * @return true if successfully deleted, false otherwise
 370    *
 371    * @static
 372    * @access public
 373    */
 374   static function dropTable($tableName) {
 375     $sql = "DROP TABLE $tableName";
 376     $dao = CRM_Core_DAO::executeQuery($sql);
 377   }
 378
 379   /**
 380    * @param string $tableName
 381    * @param string $columnName
 382    */
 383   static function dropColumn($tableName, $columnName) {
 384     $sql = "ALTER TABLE $tableName DROP COLUMN $columnName";
 385     $dao = CRM_Core_DAO::executeQuery($sql);
 386   }
 387
 388   /**
 389    * @param string $tableName
 390    * @param bool $dropUnique
 391    */
 392   static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
 393     if ($dropUnique) {
 394       $sql = "ALTER TABLE $tableName
 395 DROP INDEX `unique_entity_id` ,
 396 ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
 397     }
 398     else {
 399       $sql = " ALTER TABLE $tableName
 400 DROP INDEX `FK_{$tableName}_entity_id` ,
 401 ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
 402     }
 403     $dao = CRM_Core_DAO::executeQuery($sql);
 404   }
 405
 406   /**
 407    * @param $tables
 408    * @param string $createIndexPrefix
 409    * @param array $substrLenghts
 410    */
 411   static function createIndexes(&$tables, $createIndexPrefix = 'index', $substrLenghts = array()) {
 412     $queries = array();
 413     require_once 'CRM/Core/DAO/Domain.php';
 414     $domain = new CRM_Core_DAO_Domain;
 415     $domain->find(TRUE);
 416     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 417
 418     // if we're multilingual, cache the information on internationalised fields
 419     static $columns = NULL;
 420     if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
 421       $columns = CRM_Core_I18n_SchemaStructure::columns();
 422     }
 423
 424     foreach ($tables as $table => $fields) {
 425       $query = "SHOW INDEX FROM $table";
 426       $dao = CRM_Core_DAO::executeQuery($query);
 427
 428       $currentIndexes = array();
 429       while ($dao->fetch()) {
 430         $currentIndexes[] = $dao->Key_name;
 431       }
 432
 433       // now check for all fields if the index exists
 434       foreach ($fields as $field) {
 435         // handle indices over substrings, CRM-6245
 436         // $lengthName is appended to index name, $lengthSize is the field size modifier
 437         $lengthName = isset($substrLenghts[$table][$field]) ? "_{$substrLenghts[$table][$field]}" : '';
 438         $lengthSize = isset($substrLenghts[$table][$field]) ? "({$substrLenghts[$table][$field]})" : '';
 439
 440         $names = array("index_{$field}{$lengthName}", "FK_{$table}_{$field}{$lengthName}", "UI_{$field}{$lengthName}", "{$createIndexPrefix}_{$field}{$lengthName}");
 441
 442         // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
 443         foreach ($names as $name) {
 444           $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
 445           if (preg_grep($regex, $currentIndexes)) {
 446             continue 2;
 447           }
 448         }
 449
 450         // the index doesn't exist, so create it
 451         // if we're multilingual and the field is internationalised, do it for every locale
 452         if (!CRM_Utils_System::isNull($locales) and isset($columns[$table][$field])) {
 453           foreach ($locales as $locale) {
 454             $queries[] = "CREATE INDEX {$createIndexPrefix}_{$field}{$lengthName}_{$locale} ON {$table} ({$field}_{$locale}{$lengthSize})";
 455           }
 456         }
 457         else {
 458           $queries[] = "CREATE INDEX {$createIndexPrefix}_{$field}{$lengthName} ON {$table} ({$field}{$lengthSize})";
 459         }
 460       }
 461     }
 462
 463     // run the queries without i18n-rewriting
 464     $dao = new CRM_Core_DAO;
 465     foreach ($queries as $query) {
 466       $dao->query($query, FALSE);
 467     }
 468   }
 469
 470   /**
 471    * @param int $customFieldID
 472    * @param string $tableName
 473    * @param string $columnName
 474    * @param $length
 475    *
 476    * @throws Exception
 477    */
 478   static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
 479     // first update the custom field tables
 480     $sql = "
 481 UPDATE civicrm_custom_field
 482 SET    text_length = %1
 483 WHERE  id = %2
 484 ";
 485     $params = array(1 => array($length, 'Integer'),
 486       2 => array($customFieldID, 'Integer'),
 487     );
 488     CRM_Core_DAO::executeQuery($sql, $params);
 489
 490     $sql = "
 491 SELECT is_required, default_value
 492 FROM   civicrm_custom_field
 493 WHERE  id = %2
 494 ";
 495     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 496
 497     if ($dao->fetch()) {
 498       $clause = '';
 499
 500       if ($dao->is_required) {
 501         $clause = " NOT NULL";
 502       }
 503
 504       if (!empty($dao->default_value)) {
 505         $clause .= " DEFAULT '{$dao->default_value}'";
 506       }
 507       // now modify the column
 508       $sql = "
 509 ALTER TABLE {$tableName}
 510 MODIFY      {$columnName} varchar( $length )
 511             $clause
 512 ";
 513       CRM_Core_DAO::executeQuery($sql);
 514     }
 515     else {
 516       CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3',
 517           array(
 518             1 => $tableName,
 519             2 => $columnName,
 520             3 => $customFieldID
 521           )
 522         ));
 523     }
 524   }
 525 }
 526