CRM/Core/BAO/SchemaHandler.php

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