CRM/Core/BAO/SchemaHandler.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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-2016
  32  */
  33
  34 /**
  35  *  This file contains functions for creating and altering CiviCRM-tables structure.
  36  *
  37  * $table = array(
  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  *  ));
  55  */
  56 class CRM_Core_BAO_SchemaHandler {
  57
  58   /**
  59    * Create a CiviCRM-table
  60    *
  61    * @param array $params
  62    *
  63    * @return bool
  64    *   TRUE if successfully created, FALSE otherwise
  65    *
  66    */
  67   public static function createTable(&$params) {
  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
  76       $logging = new CRM_Logging_Schema();
  77       $logging->fixSchemaDifferencesFor($params['name'], NULL, FALSE);
  78     }
  79
  80     // always do a trigger rebuild for this table
  81     CRM_Core_DAO::triggerRebuild($params['name']);
  82
  83     return TRUE;
  84   }
  85
  86   /**
  87    * @param array $params
  88    *
  89    * @return string
  90    */
  91   public static function buildTableSQL(&$params) {
  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
 121   /**
 122    * @param array $params
 123    * @param $separator
 124    * @param $prefix
 125    *
 126    * @return string
 127    */
 128   public static function buildFieldSQL(&$params, $separator, $prefix) {
 129     $sql = '';
 130     $sql .= $separator;
 131     $sql .= str_repeat(' ', 8);
 132     $sql .= $prefix;
 133     $sql .= "`{$params['name']}` {$params['type']}";
 134
 135     if (!empty($params['required'])) {
 136       $sql .= " NOT NULL";
 137     }
 138
 139     if (!empty($params['attributes'])) {
 140       $sql .= " {$params['attributes']}";
 141     }
 142
 143     if (!empty($params['default']) &&
 144       $params['type'] != 'text'
 145     ) {
 146       $sql .= " DEFAULT {$params['default']}";
 147     }
 148
 149     if (!empty($params['comment'])) {
 150       $sql .= " COMMENT '{$params['comment']}'";
 151     }
 152
 153     return $sql;
 154   }
 155
 156   /**
 157    * @param array $params
 158    * @param $separator
 159    * @param $prefix
 160    *
 161    * @return NULL|string
 162    */
 163   public static function buildPrimaryKeySQL(&$params, $separator, $prefix) {
 164     $sql = NULL;
 165     if (!empty($params['primary'])) {
 166       $sql .= $separator;
 167       $sql .= str_repeat(' ', 8);
 168       $sql .= $prefix;
 169       $sql .= "PRIMARY KEY ( {$params['name']} )";
 170     }
 171     return $sql;
 172   }
 173
 174   /**
 175    * @param array $params
 176    * @param $separator
 177    * @param $prefix
 178    * @param bool $indexExist
 179    *
 180    * @return NULL|string
 181    */
 182   public static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) {
 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.
 193     if (!empty($params['searchable']) && !$indexExist) {
 194       $sql .= $separator;
 195       $sql .= str_repeat(' ', 8);
 196       $sql .= $prefix;
 197       $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
 198     }
 199     elseif (empty($params['searchable']) && $indexExist) {
 200       $sql .= $separator;
 201       $sql .= str_repeat(' ', 8);
 202       $sql .= "DROP INDEX INDEX_{$params['name']}";
 203     }
 204     return $sql;
 205   }
 206
 207   /**
 208    * @param array $params
 209    * @param $separator
 210    * @param $prefix
 211    *
 212    * @return string
 213    */
 214   public static function buildIndexSQL(&$params, $separator, $prefix) {
 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
 240   /**
 241    * @param string $tableName
 242    * @param string $fkTableName
 243    *
 244    * @return bool
 245    */
 246   public static function changeFKConstraint($tableName, $fkTableName) {
 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
 268   /**
 269    * @param array $params
 270    * @param $separator
 271    * @param $prefix
 272    * @param string $tableName
 273    *
 274    * @return NULL|string
 275    */
 276   public static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) {
 277     $sql = NULL;
 278     if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) {
 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
 293   /**
 294    * @param array $params
 295    * @param bool $indexExist
 296    * @param bool $triggerRebuild
 297    *
 298    * @return bool
 299    */
 300   public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) {
 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']}`";
 328         if (!empty($params['primary'])) {
 329           $sql .= ", DROP PRIMARY KEY";
 330         }
 331         if (!empty($params['fk_table_name'])) {
 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) {
 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') {
 347         $logging = new CRM_Logging_Schema();
 348         $logging->fixSchemaDifferencesFor($params['table_name'], array(trim($prefix) => array($params['name'])), FALSE);
 349       }
 350     }
 351
 352     if ($triggerRebuild) {
 353       CRM_Core_DAO::triggerRebuild($params['table_name']);
 354     }
 355
 356     return TRUE;
 357   }
 358
 359   /**
 360    * Delete a CiviCRM-table
 361    *
 362    * @param string $tableName
 363    *   Name of the table to be created.
 364    */
 365   public static function dropTable($tableName) {
 366     $sql = "DROP TABLE $tableName";
 367     $dao = CRM_Core_DAO::executeQuery($sql);
 368   }
 369
 370   /**
 371    * @param string $tableName
 372    * @param string $columnName
 373    */
 374   public static function dropColumn($tableName, $columnName) {
 375     if (self::checkIfFieldExists($tableName, $columnName)) {
 376       $sql = "ALTER TABLE $tableName DROP COLUMN $columnName";
 377       CRM_Core_DAO::executeQuery($sql);
 378     }
 379   }
 380
 381   /**
 382    * @param string $tableName
 383    * @param bool $dropUnique
 384    */
 385   public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
 386     if ($dropUnique) {
 387       $sql = "ALTER TABLE $tableName
 388 DROP INDEX `unique_entity_id` ,
 389 ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
 390     }
 391     else {
 392       $sql = " ALTER TABLE $tableName
 393 DROP INDEX `FK_{$tableName}_entity_id` ,
 394 ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
 395     }
 396     $dao = CRM_Core_DAO::executeQuery($sql);
 397   }
 398
 399   /**
 400    * Create indexes.
 401    *
 402    * @param $tables
 403    *   Tables to create index for in the format:
 404    *     array('civicrm_entity_table' => 'entity_id')
 405    *     OR
 406    *     array('civicrm_entity_table' => array('entity_id', 'entity_table'))
 407    *   The latter will create a combined index on the 2 keys (in order).
 408    *
 409    *  Side note - when creating combined indexes the one with the most variation
 410    *  goes first  - so entity_table always goes after entity_id.
 411    *
 412    *  It probably makes sense to consider more sophisticated options at some point
 413    *  but at the moment this is only being as enhanced as fast as the test is.
 414    *
 415    * @todo add support for length & multilingual on combined keys.
 416    *
 417    * @param string $createIndexPrefix
 418    * @param array $substrLenghts
 419    */
 420   public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLenghts = array()) {
 421     $queries = array();
 422     $domain = new CRM_Core_DAO_Domain();
 423     $domain->find(TRUE);
 424     $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
 425
 426     // if we're multilingual, cache the information on internationalised fields
 427     static $columns = NULL;
 428     if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
 429       $columns = CRM_Core_I18n_SchemaStructure::columns();
 430     }
 431
 432     foreach ($tables as $table => $fields) {
 433       $query = "SHOW INDEX FROM $table";
 434       $dao = CRM_Core_DAO::executeQuery($query);
 435
 436       $currentIndexes = array();
 437       while ($dao->fetch()) {
 438         $currentIndexes[] = $dao->Key_name;
 439       }
 440
 441       // now check for all fields if the index exists
 442       foreach ($fields as $field) {
 443         $fieldName = implode('_', (array) $field);
 444
 445         if (is_array($field)) {
 446           // No support for these for combined indexes as yet - add a test when you
 447           // want to add that.
 448           $lengthName = '';
 449           $lengthSize = '';
 450         }
 451         else {
 452           // handle indices over substrings, CRM-6245
 453           // $lengthName is appended to index name, $lengthSize is the field size modifier
 454           $lengthName = isset($substrLenghts[$table][$fieldName]) ? "_{$substrLenghts[$table][$fieldName]}" : '';
 455           $lengthSize = isset($substrLenghts[$table][$fieldName]) ? "({$substrLenghts[$table][$fieldName]})" : '';
 456         }
 457
 458         $names = array(
 459           "index_{$fieldName}{$lengthName}",
 460           "FK_{$table}_{$fieldName}{$lengthName}",
 461           "UI_{$fieldName}{$lengthName}",
 462           "{$createIndexPrefix}_{$fieldName}{$lengthName}",
 463         );
 464
 465         // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
 466         foreach ($names as $name) {
 467           $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
 468           if (preg_grep($regex, $currentIndexes)) {
 469             continue 2;
 470           }
 471         }
 472
 473         // the index doesn't exist, so create it
 474         // if we're multilingual and the field is internationalised, do it for every locale
 475         // @todo remove is_array check & add multilingual support for combined indexes and add a test.
 476         // Note combined indexes currently using this function are on fields like
 477         // entity_id + entity_table which are not multilingual.
 478         if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) {
 479           foreach ($locales as $locale) {
 480             $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})";
 481           }
 482         }
 483         else {
 484           $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})";
 485         }
 486       }
 487     }
 488
 489     // run the queries without i18n-rewriting
 490     $dao = new CRM_Core_DAO();
 491     foreach ($queries as $query) {
 492       $dao->query($query, FALSE);
 493     }
 494   }
 495
 496   /**
 497    * Drop an index if one by that name exists.
 498    *
 499    * @param string $tableName
 500    * @param string $indexName
 501    */
 502   public static function dropIndexIfExists($tableName, $indexName) {
 503     if (self::checkIfIndexExists($tableName, $indexName)) {
 504       CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName");
 505     }
 506   }
 507
 508   /**
 509    * @param int $customFieldID
 510    * @param string $tableName
 511    * @param string $columnName
 512    * @param $length
 513    *
 514    * @throws Exception
 515    */
 516   public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
 517     // first update the custom field tables
 518     $sql = "
 519 UPDATE civicrm_custom_field
 520 SET    text_length = %1
 521 WHERE  id = %2
 522 ";
 523     $params = array(
 524       1 => array($length, 'Integer'),
 525       2 => array($customFieldID, 'Integer'),
 526     );
 527     CRM_Core_DAO::executeQuery($sql, $params);
 528
 529     $sql = "
 530 SELECT is_required, default_value
 531 FROM   civicrm_custom_field
 532 WHERE  id = %2
 533 ";
 534     $dao = CRM_Core_DAO::executeQuery($sql, $params);
 535
 536     if ($dao->fetch()) {
 537       $clause = '';
 538
 539       if ($dao->is_required) {
 540         $clause = " NOT NULL";
 541       }
 542
 543       if (!empty($dao->default_value)) {
 544         $clause .= " DEFAULT '{$dao->default_value}'";
 545       }
 546       // now modify the column
 547       $sql = "
 548 ALTER TABLE {$tableName}
 549 MODIFY      {$columnName} varchar( $length )
 550             $clause
 551 ";
 552       CRM_Core_DAO::executeQuery($sql);
 553     }
 554     else {
 555       CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3',
 556         array(
 557           1 => $tableName,
 558           2 => $columnName,
 559           3 => $customFieldID,
 560         )
 561       ));
 562     }
 563   }
 564
 565   /**
 566    * Check if the table has an index matching the name.
 567    *
 568    * @param string $tableName
 569    * @param array $indexName
 570    *
 571    * @return \CRM_Core_DAO|object
 572    */
 573   public static function checkIfIndexExists($tableName, $indexName) {
 574     $result = CRM_Core_DAO::executeQuery(
 575       "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1",
 576       array(1 => array($indexName, 'String'))
 577     );
 578     if ($result->fetch()) {
 579       return TRUE;
 580     }
 581     return FALSE;
 582   }
 583
 584   /**
 585    * Check if the table has a specified column
 586    *
 587    * @param string $tableName
 588    * @param string $columnName
 589    *
 590    * @return \CRM_Core_DAO|object
 591    */
 592   public static function checkIfFieldExists($tableName, $columnName) {
 593     $result = CRM_Core_DAO::executeQuery(
 594       "SHOW COLUMNS FROM $tableName LIKE %1",
 595       array(1 => array($columnName, 'String'))
 596     );
 597     if ($result->fetch()) {
 598       return TRUE;
 599     }
 600     return FALSE;
 601   }
 602
 603   /**
 604    * Remove a foreign key from a table if it exists
 605    *
 606    * @param $table_name
 607    * @param $constraint_name
 608    */
 609   public static function safeRemoveFK($table_name, $constraint_name) {
 610
 611     $config = CRM_Core_Config::singleton();
 612     $dbUf = DB::parseDSN($config->dsn);
 613     $query = "
 614       SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 615       WHERE TABLE_SCHEMA = %1
 616       AND TABLE_NAME = %2
 617       AND CONSTRAINT_NAME = %3
 618       AND CONSTRAINT_TYPE = 'FOREIGN KEY'
 619     ";
 620     $params = array(
 621       1 => array($dbUf['database'], 'String'),
 622       2 => array($table_name, 'String'),
 623       3 => array($constraint_name, 'String'),
 624     );
 625     $dao = CRM_Core_DAO::executeQuery($query, $params);
 626
 627     if ($dao->fetch()) {
 628       CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", array());
 629       return TRUE;
 630     }
 631     return FALSE;
 632   }
 633
 634 }