/**
* Test creating an index.
*
* We want to be sure it creates an index and exits gracefully if the index
* already exists.
*/
public function testCreateIndex()
{
$tables = array('civicrm_uf_join' => array('weight'));
CRM_Core_BAO_SchemaHandler::createIndexes($tables);
CRM_Core_BAO_SchemaHandler::createIndexes($tables);
$dao = CRM_Core_DAO::executeQuery("SHOW INDEX FROM civicrm_uf_join");
$count = 0;
while ($dao->fetch()) {
if ($dao->Column_name == 'weight') {
$count++;
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $dao->Key_name);
}
}
$this->assertEquals(1, $count);
}
/**
* Switch database from single-lang to multi (by adding
* the first language and dropping the original columns).
*
* @param string $locale
* the first locale to create (migrate to).
*/
public static function makeMultilingual($locale)
{
$domain = new CRM_Core_DAO_Domain();
$domain->find(TRUE);
// break early if the db is already multi-lang
if ($domain->locales) {
return;
}
$dao = new CRM_Core_DAO();
// build the column-adding SQL queries
$columns = CRM_Core_I18n_SchemaStructure::columns();
$indices = CRM_Core_I18n_SchemaStructure::indices();
$queries = array();
foreach ($columns as $table => $hash) {
// drop old indices
if (isset($indices[$table])) {
foreach ($indices[$table] as $index) {
if (CRM_Core_BAO_SchemaHandler::checkIfIndexExists($table, $index['name'])) {
$queries[] = "DROP INDEX {$index['name']} ON {$table}";
}
}
}
// deal with columns
foreach ($hash as $column => $type) {
$queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
if (CRM_Core_BAO_SchemaHandler::checkIfFieldExists($table, $column)) {
$queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}";
$queries[] = "ALTER TABLE {$table} DROP {$column}";
}
}
// add view
$queries[] = self::createViewQuery($locale, $table, $dao);
// add new indices
$queries = array_merge($queries, array_values(self::createIndexQueries($locale, $table)));
}
// execute the queries without i18n rewriting
foreach ($queries as $query) {
$dao->query($query, FALSE);
}
// update civicrm_domain.locales
$domain->locales = $locale;
$domain->save();
}
static function createField($field, $operation, $indexExist = false)
{
require_once 'CRM/Core/BAO/CustomValueTable.php';
$tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $field->custom_group_id, 'table_name');
$params = array('table_name' => $tableName, 'operation' => $operation, 'name' => $field->column_name, 'type' => CRM_Core_BAO_CustomValueTable::fieldToSQLType($field->data_type, $field->text_length), 'required' => $field->is_required, 'searchable' => $field->is_searchable);
if ($operation == 'delete') {
$fkName = "{$tableName}_{$field->column_name}";
if (strlen($fkName) >= 48) {
$fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
}
$params['fkName'] = $fkName;
}
if ($field->data_type == 'Country' && $field->html_type == 'Select Country') {
$params['fk_table_name'] = 'civicrm_country';
$params['fk_field_name'] = 'id';
$params['fk_attributes'] = 'ON DELETE SET NULL';
} else {
if ($field->data_type == 'Country' && $field->html_type == 'Multi-Select Country') {
$params['type'] = 'varchar(255)';
} else {
if ($field->data_type == 'StateProvince' && $field->html_type == 'Select State/Province') {
$params['fk_table_name'] = 'civicrm_state_province';
$params['fk_field_name'] = 'id';
$params['fk_attributes'] = 'ON DELETE SET NULL';
} else {
if ($field->data_type == 'StateProvince' && $field->html_type == 'Multi-Select State/Province') {
$params['type'] = 'varchar(255)';
} else {
if ($field->data_type == 'File') {
$params['fk_table_name'] = 'civicrm_file';
$params['fk_field_name'] = 'id';
$params['fk_attributes'] = 'ON DELETE SET NULL';
} else {
if ($field->data_type == 'ContactReference') {
$params['fk_table_name'] = 'civicrm_contact';
$params['fk_field_name'] = 'id';
$params['fk_attributes'] = 'ON DELETE SET NULL';
}
}
}
}
}
}
if ($field->default_value) {
$params['default'] = "'{$field->default_value}'";
}
require_once 'CRM/Core/BAO/SchemaHandler.php';
CRM_Core_BAO_SchemaHandler::alterFieldSQL($params, $indexExist);
}
/**
* @param $group
*/
public static function createTable($group)
{
$params = array('name' => $group->table_name, 'is_multiple' => $group->is_multiple ? 1 : 0, 'extends_name' => self::mapTableName($group->extends));
$tableParams = CRM_Core_BAO_CustomField::defaultCustomTableSchema($params);
CRM_Core_BAO_SchemaHandler::createTable($tableParams);
}
/**
* Test the drop index if exists function.
*/
public function testDropIndexExists()
{
CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_contact', 'index_hash');
$this->assertFalse(CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_contact', 'index_hash'));
// Recreate it to clean up after the test.
CRM_Core_BAO_SchemaHandler::createIndexes(array('civicrm_contact' => array('hash')));
}
static function createTable($group)
{
$params = array('name' => $group->table_name, 'is_multiple' => $group->is_multiple ? 1 : 0, 'extends_name' => self::mapTableName($group->extends));
require_once 'CRM/Core/BAO/CustomField.php';
$tableParams =& CRM_Core_BAO_CustomField::defaultCustomTableSchema($params);
require_once 'CRM/Core/BAO/SchemaHandler.php';
CRM_Core_BAO_SchemaHandler::createTable($tableParams);
}
/**
* Process the form submission.
*
*
* @return void
*/
public function postProcess()
{
$values = $this->exportValues();
//FIXME: Handle logic to replace is_default column by usage
// reset used column to General (since there can only
// be one 'Supervised' or 'Unsupervised' rule)
if ($values['used'] != 'General') {
$query = "\nUPDATE civicrm_dedupe_rule_group\n SET used = 'General'\n WHERE contact_type = %1\n AND used = %2";
$queryParams = array(1 => array($this->_contactType, 'String'), 2 => array($values['used'], 'String'));
CRM_Core_DAO::executeQuery($query, $queryParams);
}
$rgDao = new CRM_Dedupe_DAO_RuleGroup();
if ($this->_action & CRM_Core_Action::UPDATE) {
$rgDao->id = $this->_rgid;
}
$rgDao->title = $values['title'];
$rgDao->is_reserved = CRM_Utils_Array::value('is_reserved', $values, FALSE);
$rgDao->used = $values['used'];
$rgDao->contact_type = $this->_contactType;
$rgDao->threshold = $values['threshold'];
$rgDao->save();
// make sure name is set only during insert
if ($this->_action & CRM_Core_Action::ADD) {
// generate name based on title
$rgDao->name = CRM_Utils_String::titleToVar($values['title']) . "_{$rgDao->id}";
$rgDao->save();
}
// lets skip updating of fields for reserved dedupe group
if ($rgDao->is_reserved) {
CRM_Core_Session::setStatus(ts("The rule '%1' has been saved.", array(1 => $rgDao->title)), ts('Saved'), 'success');
return;
}
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->delete();
$ruleDao->free();
$substrLenghts = array();
$tables = array();
$daoObj = new CRM_Core_DAO();
$database = $daoObj->database();
for ($count = 0; $count < self::RULES_COUNT; $count++) {
if (empty($values["where_{$count}"])) {
continue;
}
list($table, $field) = explode('.', CRM_Utils_Array::value("where_{$count}", $values));
$length = !empty($values["length_{$count}"]) ? CRM_Utils_Array::value("length_{$count}", $values) : NULL;
$weight = $values["weight_{$count}"];
if ($table and $field) {
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->rule_table = $table;
$ruleDao->rule_field = $field;
$ruleDao->rule_length = $length;
$ruleDao->rule_weight = $weight;
$ruleDao->save();
$ruleDao->free();
if (!array_key_exists($table, $tables)) {
$tables[$table] = array();
}
$tables[$table][] = $field;
}
// CRM-6245: we must pass table/field/length triples to the createIndexes() call below
if ($length) {
if (!isset($substrLenghts[$table])) {
$substrLenghts[$table] = array();
}
//CRM-13417 to avoid fatal error "Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys, 1089"
$schemaQuery = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS\n WHERE TABLE_SCHEMA = '{$database}' AND\n TABLE_NAME = '{$table}' AND COLUMN_NAME = '{$field}';";
$dao = CRM_Core_DAO::executeQuery($schemaQuery);
if ($dao->fetch()) {
// set the length to null for all the fields where prefix length is not supported. eg. int,tinyint,date,enum etc dataTypes.
if ($dao->COLUMN_NAME == $field && !in_array($dao->DATA_TYPE, array('char', 'varchar', 'binary', 'varbinary', 'text', 'blob'))) {
$length = NULL;
} elseif ($dao->COLUMN_NAME == $field && !empty($dao->CHARACTER_MAXIMUM_LENGTH) && $length > $dao->CHARACTER_MAXIMUM_LENGTH) {
//set the length to CHARACTER_MAXIMUM_LENGTH in case the length provided by the user is greater than the limit
$length = $dao->CHARACTER_MAXIMUM_LENGTH;
}
}
$substrLenghts[$table][$field] = $length;
}
}
// also create an index for this dedupe rule
// CRM-3837
CRM_Utils_Hook::dupeQuery($ruleDao, 'dedupeIndexes', $tables);
CRM_Core_BAO_SchemaHandler::createIndexes($tables, 'dedupe_index', $substrLenghts);
//need to clear cache of deduped contacts
//based on the previous rule
$cacheKey = "merge {$this->_contactType}_{$this->_rgid}_%";
CRM_Core_BAO_PrevNextCache::deleteItem(NULL, $cacheKey);
CRM_Core_Session::setStatus(ts("The rule '%1' has been saved.", array(1 => $rgDao->title)), ts('Saved'), 'success');
}
/**
* CRM-17999 Add index to civicrm_contribution.source.
*
* @param \CRM_Queue_TaskContext $ctx
*
* @return bool
*/
public function addIndexContributionSource(CRM_Queue_TaskContext $ctx)
{
CRM_Core_BAO_SchemaHandler::createIndexes(array('civicrm_contribution' => array('source')));
return TRUE;
}
/**
* CRM-18651 Add DataType column to Option Group Table
* @return bool
*/
public static function addDataTypeColumnToOptionGroupTable()
{
if (!CRM_Core_BAO_SchemaHandler::checkIfFieldExists('civicrm_option_group', 'data_type')) {
CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_option_group` ADD COLUMN `data_type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL comment 'Data Type of Option Group.'", 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);
}
CRM_Core_DAO::executeQuery("UPDATE `civicrm_option_group` SET `data_type` = 'Integer'\n WHERE name IN ('activity_type', 'gender', 'payment_instrument', 'participant_role', 'event_type')");
return TRUE;
}
/**
* CRM-18124 Add index to civicrm_contribution.total_amount.
*
* Note that I made this a combined index with receive_date because the issue included
* both criteria and they seemed likely to be used in conjunction to me in other cases.
*
* @param \CRM_Queue_TaskContext $ctx
*
* @return bool
*/
public function addIndexContributionAmount(CRM_Queue_TaskContext $ctx)
{
CRM_Core_BAO_SchemaHandler::createIndexes(array('civicrm_contribution' => array(array('total_amount', 'receive_date'))));
return TRUE;
}
/**
* CRM-17775 Add correct index for table civicrm_financial_item.
*
* Note that the entity ID should always precede the entity_table as
* it is more unique. This is better for performance and does not cause fallback
* to no index if table it omitted.
*
* @return bool
*/
public function addCombinedIndexFinancialItemEntityIDEntityType()
{
CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_financial_item', 'UI_id');
CRM_Core_BAO_SchemaHandler::dropIndexIfExists('civicrm_financial_item', 'IX_Entity');
CRM_Core_BAO_SchemaHandler::createIndexes(array('civicrm_financial_item' => array(array('entity_id', 'entity_table'))));
return TRUE;
}
/**
* Test to see if we can drop foreign key
*
* @dataProvider foreignKeyTests
*/
public function testSafeDropForeignKey($tableName, $key)
{
if ($key == 'FK_civicrm_mailing_recipients_id') {
$this->assertFalse(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key));
} else {
$this->assertTrue(CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_mailing_recipients', $key));
}
}
/**
* CRM-17882 Add index to civicrm_contribution.credit_note_id.
*
* @param \CRM_Queue_TaskContext $ctx
*
* @return bool
*/
public function addIndexContributionCreditNoteID(CRM_Queue_TaskContext $ctx)
{
$tables = array('civicrm_contribution' => array('creditnote_id'));
CRM_Core_BAO_SchemaHandler::createIndexes($tables);
return TRUE;
}
/**
* CRM-19372 Add field to store accepted credit credit cards for a payment processor.
* @return bool
*/
public static function addAcceptedCardTypesField()
{
if (!CRM_Core_BAO_SchemaHandler::checkIfFieldExists('civicrm_payment_processor', 'accepted_credit_cards')) {
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_payment_processor ADD COLUMN `accepted_credit_cards` text DEFAULT NULL COMMENT 'array of accepted credit card types'");
}
return TRUE;
}
/**
* Function to process the form
*
* @access public
* @return None
*/
public function postProcess()
{
$values = $this->exportValues();
$isDefault = CRM_Utils_Array::value('is_default', $values, false);
// reset defaults
if ($isDefault) {
$query = "\nUPDATE civicrm_dedupe_rule_group \n SET is_default = 0\n WHERE contact_type = %1 \n AND level = %2";
$queryParams = array(1 => array($this->_contactType, 'String'), 2 => array($values['level'], 'String'));
CRM_Core_DAO::executeQuery($query, $queryParams);
}
$rgDao = new CRM_Dedupe_DAO_RuleGroup();
if ($this->_action & CRM_Core_Action::UPDATE) {
$rgDao->id = $this->_rgid;
}
$rgDao->threshold = $values['threshold'];
$rgDao->name = $values['name'];
$rgDao->level = $values['level'];
$rgDao->contact_type = $this->_contactType;
$rgDao->is_default = $isDefault;
$rgDao->save();
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->delete();
$ruleDao->free();
$substrLenghts = array();
$tables = array();
for ($count = 0; $count < self::RULES_COUNT; $count++) {
if (!CRM_Utils_Array::value("where_{$count}", $values)) {
continue;
}
list($table, $field) = explode('.', CRM_Utils_Array::value("where_{$count}", $values));
$length = CRM_Utils_Array::value("length_{$count}", $values) ? CRM_Utils_Array::value("length_{$count}", $values) : null;
$weight = $values["weight_{$count}"];
if ($table and $field) {
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->rule_table = $table;
$ruleDao->rule_field = $field;
$ruleDao->rule_length = $length;
$ruleDao->rule_weight = $weight;
$ruleDao->save();
$ruleDao->free();
if (!array_key_exists($table, $tables)) {
$tables[$table] = array();
}
$tables[$table][] = $field;
}
// CRM-6245: we must pass table/field/length triples to the createIndexes() call below
if ($length) {
if (!isset($substrLenghts[$table])) {
$substrLenghts[$table] = array();
}
$substrLenghts[$table][$field] = $length;
}
}
// also create an index for this dedupe rule
// CRM-3837
require_once 'CRM/Core/BAO/SchemaHandler.php';
CRM_Core_BAO_SchemaHandler::createIndexes($tables, 'dedupe_index', $substrLenghts);
}
/**
* Function to process the form
*
* @access public
* @return None
*/
public function postProcess()
{
$values = $this->exportValues();
$rgDao =& new CRM_Dedupe_DAO_RuleGroup();
if ($this->_action & CRM_Core_Action::UPDATE) {
$rgDao->id = $this->_rgid;
}
$rgDao->threshold = $values['threshold'];
$rgDao->name = $values['name'];
$rgDao->level = $values['level'];
$rgDao->contact_type = $this->_contactType;
$rgDao->save();
$ruleDao =& new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->delete();
$ruleDao->free();
$tables = array();
for ($count = 0; $count < self::RULES_COUNT; $count++) {
if (!CRM_Utils_Array::value("where_{$count}", $values)) {
continue;
}
list($table, $field) = explode('.', CRM_Utils_Array::value("where_{$count}", $values));
$length = CRM_Utils_Array::value("length_{$count}", $values) ? CRM_Utils_Array::value("length_{$count}", $values) : null;
$weight = $values["weight_{$count}"];
if ($table and $field) {
$ruleDao =& new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->rule_table = $table;
$ruleDao->rule_field = $field;
$ruleDao->rule_length = $length;
$ruleDao->rule_weight = $weight;
$ruleDao->save();
$ruleDao->free();
if (!array_key_exists($table, $tables)) {
$tables[$table] = array();
}
$tables[$table][] = $field;
}
}
// also create an index for this dedupe rule
// CRM-3837
require_once 'CRM/Core/BAO/SchemaHandler.php';
CRM_Core_BAO_SchemaHandler::createIndexes($tables, 'dedupe_index');
}
/**
* Function to process the form
*
* @access public
*
* @return None
*/
public function postProcess()
{
$values = $this->exportValues();
$isDefault = CRM_Utils_Array::value('is_default', $values, FALSE);
// reset defaults
if ($isDefault) {
$query = "\nUPDATE civicrm_dedupe_rule_group \n SET is_default = 0\n WHERE contact_type = %1 \n AND level = %2";
$queryParams = array(1 => array($this->_contactType, 'String'), 2 => array($values['level'], 'String'));
CRM_Core_DAO::executeQuery($query, $queryParams);
}
$rgDao = new CRM_Dedupe_DAO_RuleGroup();
if ($this->_action & CRM_Core_Action::UPDATE) {
$rgDao->id = $this->_rgid;
}
$rgDao->title = $values['title'];
$rgDao->is_reserved = CRM_Utils_Array::value('is_reserved', $values, FALSE);
$rgDao->is_default = $isDefault;
$rgDao->level = $values['level'];
$rgDao->contact_type = $this->_contactType;
$rgDao->threshold = $values['threshold'];
$rgDao->save();
// make sure name is set only during insert
if ($this->_action & CRM_Core_Action::ADD) {
// generate name based on title
$rgDao->name = CRM_Utils_String::titleToVar($values['title']) . "_{$rgDao->id}";
$rgDao->save();
}
// lets skip updating of fields for reserved dedupe group
if ($rgDao->is_reserved) {
CRM_Core_Session::setStatus(ts('The rule \'%1\' has been saved.', array(1 => $rgDao->title)));
return;
}
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->delete();
$ruleDao->free();
$substrLenghts = array();
$tables = array();
for ($count = 0; $count < self::RULES_COUNT; $count++) {
if (!CRM_Utils_Array::value("where_{$count}", $values)) {
continue;
}
list($table, $field) = explode('.', CRM_Utils_Array::value("where_{$count}", $values));
$length = CRM_Utils_Array::value("length_{$count}", $values) ? CRM_Utils_Array::value("length_{$count}", $values) : NULL;
$weight = $values["weight_{$count}"];
if ($table and $field) {
$ruleDao = new CRM_Dedupe_DAO_Rule();
$ruleDao->dedupe_rule_group_id = $rgDao->id;
$ruleDao->rule_table = $table;
$ruleDao->rule_field = $field;
$ruleDao->rule_length = $length;
$ruleDao->rule_weight = $weight;
$ruleDao->save();
$ruleDao->free();
if (!array_key_exists($table, $tables)) {
$tables[$table] = array();
}
$tables[$table][] = $field;
}
// CRM-6245: we must pass table/field/length triples to the createIndexes() call below
if ($length) {
if (!isset($substrLenghts[$table])) {
$substrLenghts[$table] = array();
}
$substrLenghts[$table][$field] = $length;
}
}
// also create an index for this dedupe rule
// CRM-3837
CRM_Core_BAO_SchemaHandler::createIndexes($tables, 'dedupe_index', $substrLenghts);
//need to clear cache of deduped contacts
//based on the previous rule
$cacheKey = "merge {$this->_contactType}_{$this->_rgid}_%";
CRM_Core_BAO_PrevNextCache::deleteItem(NULL, $cacheKey);
CRM_Core_Session::setStatus(ts('The rule \'%1\' has been saved.', array(1 => $rgDao->title)));
}