function upgrade()
{
$currentDir = dirname(__FILE__);
// 1. remove domain_ids from the entire db
$sqlFile = implode(DIRECTORY_SEPARATOR, array($currentDir, '../sql', 'domain_ids.mysql'));
$this->source($sqlFile);
// 2. remove domain ids from custom tables
$query = "SELECT table_name FROM civicrm_custom_group";
$dao = CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
while ($dao->fetch()) {
$query = "ALTER TABLE {$dao->table_name}";
$constraint = false;
if ($constraint = CRM_Core_DAO::checkConstraintExists($dao->table_name, "FK_{$dao->table_name}_domain_id")) {
$query .= " DROP FOREIGN KEY FK_{$dao->table_name}_domain_id";
}
if (CRM_Core_DAO::checkConstraintExists($dao->table_name, "unique_domain_id_entity_id")) {
if ($constraint) {
$query .= ", ";
}
$query .= " DROP INDEX unique_domain_id_entity_id";
$constraint = true;
}
if ($constraint) {
CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
}
$query = "\nALTER TABLE {$dao->table_name}\nADD UNIQUE unique_entity_id (entity_id), \nDROP domain_id;";
CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray);
}
$this->setVersion('2.01');
}
public function task_4_3_alpha1_checkDBConstraints()
{
//checking whether the foreign key exists before dropping it CRM-11260
$config = CRM_Core_Config::singleton();
$dbUf = DB::parseDSN($config->dsn);
$tables = array('autorenewal_msg_id' => array('tableName' => 'civicrm_membership_type', 'fkey' => 'FK_civicrm_membership_autorenewal_msg_id'), 'to_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_2'), 'from_account_id' => array('tableName' => 'civicrm_financial_trxn', 'constraintName' => 'civicrm_financial_trxn_ibfk_1'), 'contribution_type_id' => array('tableName' => 'civicrm_contribution_recur', 'fkey' => 'FK_civicrm_contribution_recur_contribution_type_id'));
$query = "\nSELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\nWHERE table_name = 'civicrm_contribution_recur'\nAND constraint_name = 'FK_civicrm_contribution_recur_contribution_type_id'\nAND TABLE_SCHEMA = %1\n";
$params = array(1 => array($dbUf['database'], 'String'));
$dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
foreach ($tables as $columnName => $value) {
if ($value['tableName'] == 'civicrm_membership_type' || $value['tableName'] == 'civicrm_contribution_recur') {
$foreignKeyExists = CRM_Core_DAO::checkConstraintExists($value['tableName'], $value['fkey']);
$fKey = $value['fkey'];
} else {
$foreignKeyExists = CRM_Core_DAO::checkFKConstraintInFormat($value['tableName'], $columnName);
$fKey = "`FK_{$value['tableName']}_{$columnName}`";
}
if ($foreignKeyExists || $value['tableName'] == 'civicrm_financial_trxn') {
if ($value['tableName'] != 'civicrm_contribution_recur' || $value['tableName'] == 'civicrm_contribution_recur' && $dao->N) {
$constraintName = $foreignKeyExists ? $fKey : $value['constraintName'];
$query = "ALTER TABLE {$value['tableName']} DROP FOREIGN KEY {$constraintName}";
CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
}
$query = "ALTER TABLE {$value['tableName']} DROP INDEX {$fKey}";
CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
}
}
// check if column contact_id is present or not in civicrm_financial_account
$fieldExists = CRM_Core_DAO::checkFieldExists('civicrm_financial_account', 'contact_id', FALSE);
if (!$fieldExists) {
$query = "\nALTER TABLE civicrm_financial_account\n ADD contact_id int(10) unsigned DEFAULT NULL COMMENT 'Version identifier of financial_type' AFTER name,\n ADD CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id);\n";
CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
}
}
/**
* @param $rev
*/
public function upgrade_4_2_alpha1($rev)
{
//checking whether the foreign key exists before dropping it
//drop foreign key queries of CRM-9850
$params = array();
$tables = array('civicrm_contribution_page' => 'FK_civicrm_contribution_page_payment_processor_id', 'civicrm_event' => 'FK_civicrm_event_payment_processor_id', 'civicrm_group' => 'FK_civicrm_group_saved_search_id');
foreach ($tables as $tableName => $fKey) {
$foreignKeyExists = CRM_Core_DAO::checkConstraintExists($tableName, $fKey);
if ($foreignKeyExists) {
CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP FOREIGN KEY {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
}
}
// Drop index UI_title for civicrm_price_set
$domain = new CRM_Core_DAO_Domain();
$domain->find(TRUE);
if ($domain->locales) {
$locales = explode(CRM_Core_DAO::VALUE_SEPARATOR, $domain->locales);
foreach ($locales as $locale) {
$query = "SHOW KEYS FROM `civicrm_price_set` WHERE key_name = 'UI_title_{$locale}'";
$dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, FALSE);
if ($dao->N) {
CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_price_set` DROP INDEX `UI_title_{$locale}`", $params, TRUE, NULL, FALSE, FALSE);
}
}
} else {
$query = "SHOW KEYS FROM `civicrm_price_set` WHERE key_name = 'UI_title'";
$dao = CRM_Core_DAO::executeQuery($query);
if ($dao->N) {
CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_price_set` DROP INDEX `UI_title`");
}
}
// Some steps take a long time, so we break them up into separate
// tasks and enqueue them separately.
$this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.2.alpha1')), 'task_4_2_x_runSql', $rev);
$this->addTask(ts('Upgrade DB to 4.2.alpha1: Price Sets'), 'task_4_2_alpha1_createPriceSets', $rev);
self::convertContribution();
$this->addTask(ts('Upgrade DB to 4.2.alpha1: Event Profile'), 'task_4_2_alpha1_eventProfile');
}
/**
* Fix misconfigured constraints created in 4.4.0. To distinguish the good
* and bad configurations, we change the constraint name from "UI_find"
* (the original name in 4.4.0) to "UI_domain_find" (the new name in
* 4.4.1).
*
* @param CRM_Queue_TaskContext $ctx
* @param $rev
*
* @return bool
* TRUE for success
* @see http://issues.civicrm.org/jira/browse/CRM-13655
*/
public static function wordReplacements_patch(CRM_Queue_TaskContext $ctx, $rev)
{
if (CRM_Core_DAO::checkConstraintExists('civicrm_word_replacement', 'UI_find')) {
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP FOREIGN KEY FK_civicrm_word_replacement_domain_id;");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP KEY FK_civicrm_word_replacement_domain_id;");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement DROP KEY UI_find;");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement MODIFY COLUMN `find_word` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which need to be replaced';");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement MODIFY COLUMN `replace_word` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'Word which will replace the word in find';");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement ADD CONSTRAINT UI_domain_find UNIQUE KEY `UI_domain_find` (`domain_id`,`find_word`);");
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_word_replacement ADD CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);");
}
return TRUE;
}
/**
* CREATE INDEX queries for a given locale and table
*
* @param $locale string locale for which the queries should be created (null to create original indices)
* @param $table string table for which the queries should be created
* @param $class string schema structure class to use
*
* @return array array of CREATE INDEX queries
*/
private static function createIndexQueries($locale, $table, $class = 'CRM_Core_I18n_SchemaStructure')
{
$indices =& $class::indices();
$columns =& $class::columns();
if (!isset($indices[$table])) {
return array();
}
$queries = array();
foreach ($indices[$table] as $index) {
$unique = isset($index['unique']) && $index['unique'] ? 'UNIQUE' : '';
foreach ($index['field'] as $i => $col) {
// if a given column is localizable, extend its name with the locale
if ($locale and isset($columns[$table][$col])) {
$index['field'][$i] = "{$col}_{$locale}";
}
}
$cols = implode(', ', $index['field']);
$name = $index['name'];
if ($locale) {
$name .= '_' . $locale;
}
// CRM-7854: skip existing indices
if (CRM_Core_DAO::checkConstraintExists($table, $name)) {
continue;
}
$queries[$index['name']] = "CREATE {$unique} INDEX {$name} ON {$table} ({$cols})";
}
return $queries;
}
