[civicrm-core.git] / CRM / Core / BAO / SchemaHandler.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.7                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2016                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
 */

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2016
 */

/**
 *  This file contains functions for creating and altering CiviCRM-tables structure.
 *
 * $table = array(
 *  'name'  => TABLE_NAME,
 *  'attributes' => ATTRIBUTES,
 *  'fields' => array(
 *    array(
 *      'name' => FIELD_NAME,
 *      // can be field, index, constraint
 *      'type' => FIELD_SQL_TYPE,
 *      'class'         => FIELD_CLASS_TYPE,
 *      'primary'       => BOOLEAN,
 *      'required'      => BOOLEAN,
 *      'searchable'    => TRUE,
 *      'fk_table_name' => FOREIGN_KEY_TABLE_NAME,
 *      'fk_field_name' => FOREIGN_KEY_FIELD_NAME,
 *      'comment'       => COMMENT,
 *      'default'       => DEFAULT, )
 *      ...
 *  ));
 */
class CRM_Core_BAO_SchemaHandler {

  /**
   * Create a CiviCRM-table
   *
   * @param array $params
   *
   * @return bool
   *   TRUE if successfully created, FALSE otherwise
   *
   */
  public static function createTable(&$params) {
    $sql = self::buildTableSQL($params);
    // do not i18n-rewrite
    $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
    $dao->free();

    $config = CRM_Core_Config::singleton();
    if ($config->logging) {
      // logging support
      $logging = new CRM_Logging_Schema();
      $logging->fixSchemaDifferencesFor($params['name'], NULL, FALSE);
    }

    // always do a trigger rebuild for this table
    CRM_Core_DAO::triggerRebuild($params['name']);

    return TRUE;
  }

  /**
   * @param array $params
   *
   * @return string
   */
  public static function buildTableSQL(&$params) {
    $sql = "CREATE TABLE {$params['name']} (";
    if (isset($params['fields']) &&
      is_array($params['fields'])
    ) {
      $separator = "\n";
      $prefix = NULL;
      foreach ($params['fields'] as $field) {
        $sql .= self::buildFieldSQL($field, $separator, $prefix);
        $separator = ",\n";
      }
      foreach ($params['fields'] as $field) {
        $sql .= self::buildPrimaryKeySQL($field, $separator, $prefix);
      }
      foreach ($params['fields'] as $field) {
        $sql .= self::buildSearchIndexSQL($field, $separator, $prefix);
      }
      if (isset($params['indexes'])) {
        foreach ($params['indexes'] as $index) {
          $sql .= self::buildIndexSQL($index, $separator, $prefix);
        }
      }
      foreach ($params['fields'] as $field) {
        $sql .= self::buildForeignKeySQL($field, $separator, $prefix, $params['name']);
      }
    }
    $sql .= "\n) {$params['attributes']};";
    return $sql;
  }

  /**
   * @param array $params
   * @param $separator
   * @param $prefix
   *
   * @return string
   */
  public static function buildFieldSQL(&$params, $separator, $prefix) {
    $sql = '';
    $sql .= $separator;
    $sql .= str_repeat(' ', 8);
    $sql .= $prefix;
    $sql .= "`{$params['name']}` {$params['type']}";

    if (!empty($params['required'])) {
      $sql .= " NOT NULL";
    }

    if (!empty($params['attributes'])) {
      $sql .= " {$params['attributes']}";
    }

    if (!empty($params['default']) &&
      $params['type'] != 'text'
    ) {
      $sql .= " DEFAULT {$params['default']}";
    }

    if (!empty($params['comment'])) {
      $sql .= " COMMENT '{$params['comment']}'";
    }

    return $sql;
  }

  /**
   * @param array $params
   * @param $separator
   * @param $prefix
   *
   * @return NULL|string
   */
  public static function buildPrimaryKeySQL(&$params, $separator, $prefix) {
    $sql = NULL;
    if (!empty($params['primary'])) {
      $sql .= $separator;
      $sql .= str_repeat(' ', 8);
      $sql .= $prefix;
      $sql .= "PRIMARY KEY ( {$params['name']} )";
    }
    return $sql;
  }

  /**
   * @param array $params
   * @param $separator
   * @param $prefix
   * @param bool $indexExist
   *
   * @return NULL|string
   */
  public static function buildSearchIndexSQL(&$params, $separator, $prefix, $indexExist = FALSE) {
    $sql = NULL;

    // dont index blob
    if ($params['type'] == 'text') {
      return $sql;
    }

    //create index only for searchable fields during ADD,
    //create index only if field is become searchable during MODIFY,
    //drop index only if field is no more searchable and index was exist.
    if (!empty($params['searchable']) && !$indexExist) {
      $sql .= $separator;
      $sql .= str_repeat(' ', 8);
      $sql .= $prefix;
      $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
    }
    elseif (empty($params['searchable']) && $indexExist) {
      $sql .= $separator;
      $sql .= str_repeat(' ', 8);
      $sql .= "DROP INDEX INDEX_{$params['name']}";
    }
    return $sql;
  }

  /**
   * @param array $params
   * @param $separator
   * @param $prefix
   *
   * @return string
   */
  public static function buildIndexSQL(&$params, $separator, $prefix) {
    $sql = '';
    $sql .= $separator;
    $sql .= str_repeat(' ', 8);
    if ($params['unique']) {
      $sql .= 'UNIQUE INDEX';
      $indexName = 'unique';
    }
    else {
      $sql .= 'INDEX';
      $indexName = 'index';
    }
    $indexFields = NULL;

    foreach ($params as $name => $value) {
      if (substr($name, 0, 11) == 'field_name_') {
        $indexName .= "_{$value}";
        $indexFields .= " $value,";
      }
    }
    $indexFields = substr($indexFields, 0, -1);

    $sql .= " $indexName ( $indexFields )";
    return $sql;
  }

  /**
   * @param string $tableName
   * @param string $fkTableName
   *
   * @return bool
   */
  public static function changeFKConstraint($tableName, $fkTableName) {
    $fkName = "{$tableName}_entity_id";
    if (strlen($fkName) >= 48) {
      $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
    }
    $dropFKSql = "
ALTER TABLE {$tableName}
      DROP FOREIGN KEY `FK_{$fkName}`;";

    $dao = CRM_Core_DAO::executeQuery($dropFKSql);
    $dao->free();

    $addFKSql = "
ALTER TABLE {$tableName}
      ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;";
    // CRM-7007: do not i18n-rewrite this query
    $dao = CRM_Core_DAO::executeQuery($addFKSql, array(), TRUE, NULL, FALSE, FALSE);
    $dao->free();

    return TRUE;
  }

  /**
   * @param array $params
   * @param $separator
   * @param $prefix
   * @param string $tableName
   *
   * @return NULL|string
   */
  public static function buildForeignKeySQL(&$params, $separator, $prefix, $tableName) {
    $sql = NULL;
    if (!empty($params['fk_table_name']) && !empty($params['fk_field_name'])) {
      $sql .= $separator;
      $sql .= str_repeat(' ', 8);
      $sql .= $prefix;
      $fkName = "{$tableName}_{$params['name']}";
      if (strlen($fkName) >= 48) {
        $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
      }

      $sql .= "CONSTRAINT FK_$fkName FOREIGN KEY ( `{$params['name']}` ) REFERENCES {$params['fk_table_name']} ( {$params['fk_field_name']} ) ";
      $sql .= CRM_Utils_Array::value('fk_attributes', $params);
    }
    return $sql;
  }

  /**
   * @param array $params
   * @param bool $indexExist
   * @param bool $triggerRebuild
   *
   * @return bool
   */
  public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) {
    $sql = str_repeat(' ', 8);
    $sql .= "ALTER TABLE {$params['table_name']}";

    // lets suppress the required flag, since that can cause sql issue
    $params['required'] = FALSE;

    switch ($params['operation']) {
      case 'add':
        $separator = "\n";
        $prefix = "ADD ";
        $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN ");
        $separator = ",\n";
        $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY ");
        $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ");
        $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']);
        break;

      case 'modify':
        $separator = "\n";
        $prefix = "MODIFY ";
        $sql .= self::buildFieldSQL($params, $separator, $prefix);
        $separator = ",\n";
        $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist);
        break;

      case 'delete':
        $sql .= " DROP COLUMN `{$params['name']}`";
        if (!empty($params['primary'])) {
          $sql .= ", DROP PRIMARY KEY";
        }
        if (!empty($params['fk_table_name'])) {
          $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}";
        }
        break;
    }

    // CRM-7007: do not i18n-rewrite this query
    $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
    $dao->free();

    $config = CRM_Core_Config::singleton();
    if ($config->logging) {
      // CRM-16717 not sure why this was originally limited to add.
      // For example custom tables can have field length changes - which need to flow through to logging.
      // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?)
      if ($params['operation'] == 'add' || $params['operation'] == 'modify') {
        $logging = new CRM_Logging_Schema();
        $logging->fixSchemaDifferencesFor($params['table_name'], array(trim($prefix) => array($params['name'])), FALSE);
      }
    }

    if ($triggerRebuild) {
      CRM_Core_DAO::triggerRebuild($params['table_name']);
    }

    return TRUE;
  }

  /**
   * Delete a CiviCRM-table
   *
   * @param string $tableName
   *   Name of the table to be created.
   */
  public static function dropTable($tableName) {
    $sql = "DROP TABLE $tableName";
    $dao = CRM_Core_DAO::executeQuery($sql);
  }

  /**
   * @param string $tableName
   * @param string $columnName
   * @param bool $l18n
   *
   */
  public static function dropColumn($tableName, $columnName, $l18n = FALSE) {
    if (self::checkIfFieldExists($tableName, $columnName)) {
      $sql = "ALTER TABLE $tableName DROP COLUMN $columnName";
      if ($l18n) {
        CRM_Core_DAO::executeQuery($sql);
      }
      else {
        CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE);
      }
      $domain = new CRM_Core_DAO_Domain();
      $domain->find(TRUE);
      if ($domain->locales) {
        $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
        CRM_Core_I18n_Schema::rebuildMultilingualSchema($locales, NULL);
      }
    }
  }

  /**
   * @param string $tableName
   * @param bool $dropUnique
   */
  public static function changeUniqueToIndex($tableName, $dropUnique = TRUE) {
    if ($dropUnique) {
      $sql = "ALTER TABLE $tableName
DROP INDEX `unique_entity_id` ,
ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )";
    }
    else {
      $sql = " ALTER TABLE $tableName
DROP INDEX `FK_{$tableName}_entity_id` ,
ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
    }
    $dao = CRM_Core_DAO::executeQuery($sql);
  }

  /**
   * Create indexes.
   *
   * @param $tables
   *   Tables to create index for in the format:
   *     array('civicrm_entity_table' => 'entity_id')
   *     OR
   *     array('civicrm_entity_table' => array('entity_id', 'entity_table'))
   *   The latter will create a combined index on the 2 keys (in order).
   *
   *  Side note - when creating combined indexes the one with the most variation
   *  goes first  - so entity_table always goes after entity_id.
   *
   *  It probably makes sense to consider more sophisticated options at some point
   *  but at the moment this is only being as enhanced as fast as the test is.
   *
   * @todo add support for length & multilingual on combined keys.
   *
   * @param string $createIndexPrefix
   * @param array $substrLenghts
   */
  public static function createIndexes($tables, $createIndexPrefix = 'index', $substrLenghts = array()) {
    $queries = array();
    $domain = new CRM_Core_DAO_Domain();
    $domain->find(TRUE);
    $locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);

    // if we're multilingual, cache the information on internationalised fields
    static $columns = NULL;
    if (!CRM_Utils_System::isNull($locales) and $columns === NULL) {
      $columns = CRM_Core_I18n_SchemaStructure::columns();
    }

    foreach ($tables as $table => $fields) {
      $query = "SHOW INDEX FROM $table";
      $dao = CRM_Core_DAO::executeQuery($query);

      $currentIndexes = array();
      while ($dao->fetch()) {
        $currentIndexes[] = $dao->Key_name;
      }

      // now check for all fields if the index exists
      foreach ($fields as $field) {
        $fieldName = implode('_', (array) $field);

        if (is_array($field)) {
          // No support for these for combined indexes as yet - add a test when you
          // want to add that.
          $lengthName = '';
          $lengthSize = '';
        }
        else {
          // handle indices over substrings, CRM-6245
          // $lengthName is appended to index name, $lengthSize is the field size modifier
          $lengthName = isset($substrLenghts[$table][$fieldName]) ? "_{$substrLenghts[$table][$fieldName]}" : '';
          $lengthSize = isset($substrLenghts[$table][$fieldName]) ? "({$substrLenghts[$table][$fieldName]})" : '';
        }

        $names = array(
          "index_{$fieldName}{$lengthName}",
          "FK_{$table}_{$fieldName}{$lengthName}",
          "UI_{$fieldName}{$lengthName}",
          "{$createIndexPrefix}_{$fieldName}{$lengthName}",
        );

        // skip to the next $field if one of the above $names exists; handle multilingual for CRM-4126
        foreach ($names as $name) {
          $regex = '/^' . preg_quote($name) . '(_[a-z][a-z]_[A-Z][A-Z])?$/';
          if (preg_grep($regex, $currentIndexes)) {
            continue 2;
          }
        }

        // the index doesn't exist, so create it
        // if we're multilingual and the field is internationalised, do it for every locale
        // @todo remove is_array check & add multilingual support for combined indexes and add a test.
        // Note combined indexes currently using this function are on fields like
        // entity_id + entity_table which are not multilingual.
        if (!is_array($field) && !CRM_Utils_System::isNull($locales) and isset($columns[$table][$fieldName])) {
          foreach ($locales as $locale) {
            $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName}_{$locale} ON {$table} ({$fieldName}_{$locale}{$lengthSize})";
          }
        }
        else {
          $queries[] = "CREATE INDEX {$createIndexPrefix}_{$fieldName}{$lengthName} ON {$table} (" . implode(',', (array) $field) . "{$lengthSize})";
        }
      }
    }

    // run the queries without i18n-rewriting
    $dao = new CRM_Core_DAO();
    foreach ($queries as $query) {
      $dao->query($query, FALSE);
    }
  }

  /**
   * Drop an index if one by that name exists.
   *
   * @param string $tableName
   * @param string $indexName
   */
  public static function dropIndexIfExists($tableName, $indexName) {
    if (self::checkIfIndexExists($tableName, $indexName)) {
      CRM_Core_DAO::executeQuery("DROP INDEX $indexName ON $tableName");
    }
  }

  /**
   * @param int $customFieldID
   * @param string $tableName
   * @param string $columnName
   * @param $length
   *
   * @throws Exception
   */
  public static function alterFieldLength($customFieldID, $tableName, $columnName, $length) {
    // first update the custom field tables
    $sql = "
UPDATE civicrm_custom_field
SET    text_length = %1
WHERE  id = %2
";
    $params = array(
      1 => array($length, 'Integer'),
      2 => array($customFieldID, 'Integer'),
    );
    CRM_Core_DAO::executeQuery($sql, $params);

    $sql = "
SELECT is_required, default_value
FROM   civicrm_custom_field
WHERE  id = %2
";
    $dao = CRM_Core_DAO::executeQuery($sql, $params);

    if ($dao->fetch()) {
      $clause = '';

      if ($dao->is_required) {
        $clause = " NOT NULL";
      }

      if (!empty($dao->default_value)) {
        $clause .= " DEFAULT '{$dao->default_value}'";
      }
      // now modify the column
      $sql = "
ALTER TABLE {$tableName}
MODIFY      {$columnName} varchar( $length )
            $clause
";
      CRM_Core_DAO::executeQuery($sql);
    }
    else {
      CRM_Core_Error::fatal(ts('Could Not Find Custom Field Details for %1, %2, %3',
        array(
          1 => $tableName,
          2 => $columnName,
          3 => $customFieldID,
        )
      ));
    }
  }

  /**
   * Check if the table has an index matching the name.
   *
   * @param string $tableName
   * @param array $indexName
   *
   * @return \CRM_Core_DAO|object
   */
  public static function checkIfIndexExists($tableName, $indexName) {
    $result = CRM_Core_DAO::executeQuery(
      "SHOW INDEX FROM $tableName WHERE key_name = %1 AND seq_in_index = 1",
      array(1 => array($indexName, 'String'))
    );
    if ($result->fetch()) {
      return TRUE;
    }
    return FALSE;
  }

  /**
   * Check if the table has a specified column
   *
   * @param string $tableName
   * @param string $columnName
   *
   * @return \CRM_Core_DAO|object
   */
  public static function checkIfFieldExists($tableName, $columnName) {
    $result = CRM_Core_DAO::executeQuery(
      "SHOW COLUMNS FROM $tableName LIKE %1",
      array(1 => array($columnName, 'String'))
    );
    if ($result->fetch()) {
      return TRUE;
    }
    return FALSE;
  }

  /**
   * Remove a foreign key from a table if it exists
   *
   * @param $table_name
   * @param $constraint_name
   */
  public static function safeRemoveFK($table_name, $constraint_name) {

    $config = CRM_Core_Config::singleton();
    $dbUf = DB::parseDSN($config->dsn);
    $query = "
      SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = %1
      AND TABLE_NAME = %2
      AND CONSTRAINT_NAME = %3
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ";
    $params = array(
      1 => array($dbUf['database'], 'String'),
      2 => array($table_name, 'String'),
      3 => array($constraint_name, 'String'),
    );
    $dao = CRM_Core_DAO::executeQuery($query, $params);

    if ($dao->fetch()) {
      CRM_Core_DAO::executeQuery("ALTER TABLE {$table_name} DROP FOREIGN KEY {$constraint_name}", array());
      return TRUE;
    }
    return FALSE;
  }

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