CRM/Core/BAO/SchemaHandler.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   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    */
  73   public static function createTable(&$params) {
  74     $sql = self::buildTableSQL($params);
  75     // do not i18n-rewrite
  76     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
  77     $dao->free();
  78
  79     $config = CRM_Core_Config::singleton();
  80     if ($config->logging) {
  81       // logging support
  82       $logging = new CRM_Logging_Schema;
  83       $logging->fixSchemaDifferencesFor($params['name'], NULL, FALSE);
  84     }
  85
  86     // always do a trigger rebuild for this table
  87     CRM_Core_DAO::triggerRebuild($params['name']);
  88
  89     return TRUE;
  90   }
  91
  92   /**
  93    * @param array $params
  94    *
  95    * @return string
  96    */
  97   public static function buildTableSQL(&$params) {
  98     $sql = "CREATE TABLE {$params['name']} (";
  99     if (isset($params['fields']) &&
 100       is_array($params['fields'])
 101     ) {
 102       $separator = "\n";
 103       $prefix = NULL;
 104       foreach ($params['fields'] as $field) {
 105         $sql .= self::buildFieldSQL($field, $separator, $prefix);
 106         $separator = ",\n";
 107       }
 108       foreach ($params['fields'] as $field) {
 109         $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix);
 110       }
 111       foreach ($params['fields'] as $field) {
 112         $sql .= self::buildSearchIndexSQL($field, $separator, $prefix);
 113       }
 114       if (isset($params['indexes'])) {
 115         foreach ($params['indexes'] as $index) {
 116           $sql .= self::buildIndexSQL($index, $separator, $prefix);
 117         }
 118       }
 119       foreach ($params['fields'] as $field) {
 120         $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']);
 121       }
 122     }
 123     $sql .= "\n) {$params['attributes']};";
 124     return $sql;
 125   }
 126
 127   /**
 128    * @param array $params
 129    * @param $separator
 130    * @param $prefix
 131    *
 132    * @return string
 133    */
 134   public static function buildFieldSQL(&$params, $separator, $prefix) {
 135     $sql = '';
 136     $sql .= $separator;
 137     $sql .= str_repeat(' ', 8);
 138     $sql .= $prefix;
 139     $sql .= "`{$params['name']}` {$params['type']}";
 140
 141     if (!empty($params['required'])) {
 142       $sql .= " NOT NULL";
 143     }
 144
 145     if (!empty($params['attributes'])) {
 146       $sql .= " {$params['attributes']}";
 147     }
 148
 149     if (!empty($params['default']) &&
 150       $params['type'] != 'text'
 151     ) {
 152       $sql .= " DEFAULT {$params['default']}";
 153     }
 154
 155     if (!empty($params['comment'])) {
 156       $sql .= " COMMENT '{$params['comment']}'";
 157     }
 158
 159     return $sql;
 160   }
 161
 162   /**
 163    * @param array $params
 164    * @param $separator
 165    * @param $prefix
 166    *
 167    * @return NULL|string
 168    */
 169   public static function buildPrimaryKeySQL(&$params, $separator, $prefix) {
 170     $sql = NULL;
 171     if (!empty($params['primary'])) {
 172       $sql .= $separator;
 173       $sql .= str_repeat(' ', 8);
 174       $sql .= $prefix;
 175       $sql .= "PRIMARY KEY ( {$params['name']} )";
 176     }
 177     return $sql;
 178   }
 179
 180   /**
 181    * @param array $params
 182    * @param $separator
 183    * @param $prefix
 184    * @param bool $indexExist
 185    *
 186    * @return NULL|string
 187    */
 188   public static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) {
 189     $sql = NULL;
 190
 191     // dont index blob
 192     if ($params['type'] == 'text') {
 193       return $sql;
 194     }
 195
 196     //create index only for searchable fields during ADD,
 197     //create index only if field is become searchable during MODIFY,
 198     //drop index only if field is no more searchable and index was exist.
 199     if (!empty($params['searchable']) && !$indexExist) {
 200       $sql .= $separator;
 201       $sql .= str_repeat(' ', 8);
 202       $sql .= $prefix;
 203       $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
 204     }
 205     elseif (empty($params['searchable']) && $indexExist) {
 206       $sql .= $separator;
 207       $sql .= str_repeat(' ', 8);
 208       $sql .= "DROP INDEX INDEX_{$params['name']}";
 209     }
 210     return $sql;
 211   }
 212
 213   /**
 214    * @param array $params
 215    * @param $separator
 216    * @param $prefix
 217    *
 218    * @return string
 219    */
 220   public static function buildIndexSQL(&$params, $separator, $prefix) {
 221     $sql = '';
 222     $sql .= $separator;
 223     $sql .= str_repeat(' ', 8);
 224     if ($params['unique']) {
 225       $sql .= 'UNIQUE INDEX';
 226       $indexName = 'unique';
 227     }
 228     else {
 229       $sql .= 'INDEX';
 230       $indexName = 'index';
 231     }
 232     $indexFields = NULL;
 233
 234     foreach ($params as $name => $value) {
 235       if (substr($name, 0, 11) == 'field_name_') {
 236         $indexName .= "_{$value}";
 237         $indexFields .= " $value,";
 238       }
 239     }
 240     $indexFields = substr($indexFields, 0, -1);
 241
 242     $sql .= " $indexName ( $indexFields )";
 243     return $sql;
 244   }
 245
 246   /**
 247    * @param string $tableName
 248    * @param string $fkTableName
 249    *
 250    * @return bool
 251    */
 252   public static function changeFKConstraint($tableName, $fkTableName) {
 253     $fkName = "{$tableName}_entity_id";
 254     if (strlen($fkName) >= 48) {
 255       $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 256     }
 257     $dropFKSql = "
 258 ALTER TABLE {$tableName}
 259       DROP FOREIGN KEY `FK_{$fkName}`;";
 260
 261     $dao = CRM_Core_DAO::executeQuery($dropFKSql);
 262     $dao->free();
 263
 264     $addFKSql = "
 265 ALTER TABLE {$tableName}
 266       ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;";
 267     // CRM-7007: do not i18n-rewrite this query
 268     $dao = CRM_Core_DAO::executeQuery($addFKSql, array(), TRUE, NULL, FALSE, FALSE);
 269     $dao->free();
 270
 271     return TRUE;
 272   }
 273
 274   /**
 275    * @param array $params
 276    * @param $separator
 277    * @param $prefix
 278    * @param string $tableName
 279    *
 280    * @return NULL|string
 281    */
 282   public static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) {
 283     $sql = NULL;
 284     if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) {
 285       $sql .= $separator;
 286       $sql .= str_repeat(' ', 8);
 287       $sql .= $prefix;
 288       $fkName = "{$tableName}_{$params['name']}";
 289       if (strlen($fkName) >= 48) {
 290         $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
 291       }
 292
 293       $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) ";
 294       $sql .= CRM_Utils_Array::value('fk_attributes', $params);
 295     }
 296     return $sql;
 297   }
 298
 299   /**
 300    * @param array $params
 301    * @param bool $indexExist
 302    * @param bool $triggerRebuild
 303    *
 304    * @return bool
 305    */
 306   public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) {
 307     $sql = str_repeat(' ', 8);
 308     $sql .= "ALTER TABLE {$params['table_name']}";
 309
 310     // lets suppress the required flag, since that can cause sql issue
 311     $params['required'] = FALSE;
 312
 313     switch ($params['operation']) {
 314       case 'add':
 315         $separator = "\n";
 316         $prefix = "ADD ";
 317         $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
 318         $separator = ",\n";
 319         $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
 320         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
 321         $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
 322         break;
 323
 324       case 'modify':
 325         $separator = "\n";
 326         $prefix = "MODIFY ";
 327         $sql .= self::buildFieldSQL($params, $separator, $prefix);
 328         $separator = ",\n";
 329         $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
 330         break;
 331
 332       case 'delete':
 333         $sql .= " DROP COLUMN `{$params['name']}`";
 334         if (!empty($params['primary'])) {
 335           $sql .= ", DROP PRIMARY KEY";
 336         }
 337         if (!empty($params['fk_table_name'])) {
 338           $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
 339         }
 340         break;
 341     }
 342
 343     // CRM-7007: do not i18n-rewrite this query
 344     $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
 345     $dao->free();
 346
 347     $config = CRM_Core_Config::singleton();
 348     if ($config->logging) {
 349       // logging support: if we’re adding a column (but only then!) make sure the potential relevant log table gets a column as well
 350       if ($params['operation'] == 'add') {
 351         $logging = new CRM_Logging_Schema;
 352         $logging->fixSchemaDifferencesFor($params['table_name'], array('ADD' => array($params['name'])), FALSE);
 353       }
 354     }
 355
 356     if ($triggerRebuild) {
 357       CRM_Core_DAO::triggerRebuild($params['table_name']);
 358     }
 359
 360     return TRUE;
 361   }
 362
 363   /**
 364    * Delete a civiCRM-table
 365    *
 366    * @param string $tableName
 367    *   Name of the table to be created.
 368    *
 369    * @return bool
 370    *   TRUE if successfully deleted, FALSE otherwise.
 371    *
 372    * @static
 373    */
 374   public 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   public 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   public 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   public 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   public 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 }