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