From 2cbdd085119a6e935cb2fef9be8b4b5abd3cec5e Mon Sep 17 00:00:00 2001 From: JKingsnorth Date: Tue, 10 May 2016 11:13:14 +0100 Subject: [PATCH] CRM-18526: Fix upgrade code for foreign keys from CRM-18345 --- CRM/Upgrade/Incremental/php/FourSeven.php | 100 ++++++++++++++++++++ CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl | 41 -------- 2 files changed, 100 insertions(+), 41 deletions(-) diff --git a/CRM/Upgrade/Incremental/php/FourSeven.php b/CRM/Upgrade/Incremental/php/FourSeven.php index bd1c17560a..e374016f07 100644 --- a/CRM/Upgrade/Incremental/php/FourSeven.php +++ b/CRM/Upgrade/Incremental/php/FourSeven.php @@ -187,6 +187,16 @@ class CRM_Upgrade_Incremental_php_FourSeven extends CRM_Upgrade_Incremental_Base $this->addTask('Add Contact Deleted by Merge Activity Type', 'addDeletedByMergeActivityType'); } + /** + * Upgrade function. + * + * @param string $rev + */ + public function upgrade_4_7_5($rev) { + $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => $rev)), 'runSql', $rev); + $this->addTask('Upgrade mailing foreign key constraints', 'upgradeMailingFKs'); + } + /** * Upgrade function. * @@ -590,4 +600,94 @@ FROM `civicrm_dashboard_contact` JOIN `civicrm_contact` WHERE civicrm_dashboard_ return TRUE; } + /** + * Remove a foreign key from a table if it exists + * + * @param $table_name + * @param $constraint_name + */ + public 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()); + } + + } + + /** + * CRM-18345 Don't delete mailing data on email/phone deletion + * Implemented here in CRM-18526 + * + * @param \CRM_Queue_TaskContext $ctx + * + * @return bool + */ + public function upgradeMailingFKs(CRM_Queue_TaskContext $ctx) { + + // Safely drop the foreign keys + CRM_Upgrade_Incremental_php_FourSeven::safeRemoveFK('civicrm_mailing_event_queue', 'FK_civicrm_mailing_event_queue_email_id'); + CRM_Upgrade_Incremental_php_FourSeven::safeRemoveFK('civicrm_mailing_event_queue', 'FK_civicrm_mailing_event_queue_phone_id'); + CRM_Upgrade_Incremental_php_FourSeven::safeRemoveFK('civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id'); + CRM_Upgrade_Incremental_php_FourSeven::safeRemoveFK('civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_phone_id'); + + // Set up the new foreign keys + CRM_Core_DAO::executeQuery("SET FOREIGN_KEY_CHECKS = 0;"); + + CRM_Core_DAO::executeQuery(" + ALTER TABLE `civicrm_mailing_event_queue` + ADD CONSTRAINT `FK_civicrm_mailing_event_queue_email_id` + FOREIGN KEY (`email_id`) + REFERENCES `civicrm_email`(`id`) + ON DELETE SET NULL + ON UPDATE RESTRICT; + "); + + CRM_Core_DAO::executeQuery(" + ALTER TABLE `civicrm_mailing_event_queue` + ADD CONSTRAINT `FK_civicrm_mailing_event_queue_phone_id` + FOREIGN KEY (`phone_id`) + REFERENCES `civicrm_phone`(`id`) + ON DELETE SET NULL + ON UPDATE RESTRICT; + "); + + CRM_Core_DAO::executeQuery(" + ALTER TABLE `civicrm_mailing_recipients` + ADD CONSTRAINT `FK_civicrm_mailing_recipients_email_id` + FOREIGN KEY (`email_id`) + REFERENCES `civicrm_email`(`id`) + ON DELETE SET NULL + ON UPDATE RESTRICT; + "); + + CRM_Core_DAO::executeQuery(" + ALTER TABLE `civicrm_mailing_recipients` + ADD CONSTRAINT `FK_civicrm_mailing_recipients_phone_id` + FOREIGN KEY (`phone_id`) + REFERENCES `civicrm_phone`(`id`) + ON DELETE SET NULL + ON UPDATE RESTRICT; + "); + + CRM_Core_DAO::executeQuery("SET FOREIGN_KEY_CHECKS = 1;"); + + return TRUE; + } + } diff --git a/CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl index acb2898c55..5138ace125 100644 --- a/CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.7.5.mysql.tpl @@ -31,44 +31,3 @@ WHERE ov.name = 'Contact Deleted by Merge' AND og.name = 'activity_type'; {else} ALTER TABLE civicrm_option_value CHANGE label label varchar( 512 ) DEFAULT NULL ; {/if} - --- CRM-18345: Don't delete mailing records when email address / phone is deleted -ALTER TABLE `civicrm_mailing_event_queue` - DROP FOREIGN KEY `FK_civicrm_mailing_event_queue_email_id`; - -ALTER TABLE `civicrm_mailing_event_queue` - ADD CONSTRAINT `FK_civicrm_mailing_event_queue_email_id` - FOREIGN KEY (`email_id`) - REFERENCES `civicrm_email`(`id`) - ON DELETE SET NULL - ON UPDATE RESTRICT; - -ALTER TABLE `civicrm_mailing_event_queue` - DROP FOREIGN KEY `FK_civicrm_mailing_event_queue_phone_id`; - -ALTER TABLE `civicrm_mailing_event_queue` - ADD CONSTRAINT `FK_civicrm_mailing_event_queue_phone_id` - FOREIGN KEY (`phone_id`) - REFERENCES `civicrm_phone`(`id`) - ON DELETE SET NULL - ON UPDATE RESTRICT; - -ALTER TABLE `civicrm_mailing_recipients` - DROP FOREIGN KEY `FK_civicrm_mailing_recipients_email_id`; - -ALTER TABLE `civicrm_mailing_recipients` - ADD CONSTRAINT `FK_civicrm_mailing_recipients_email_id` - FOREIGN KEY (`email_id`) - REFERENCES `civicrm_email`(`id`) - ON DELETE SET NULL - ON UPDATE RESTRICT; - -ALTER TABLE `civicrm_mailing_recipients` - DROP FOREIGN KEY `FK_civicrm_mailing_recipients_phone_id`; - -ALTER TABLE `civicrm_mailing_recipients` - ADD CONSTRAINT `FK_civicrm_mailing_recipients_phone_id` - FOREIGN KEY (`phone_id`) - REFERENCES `civicrm_phone`(`id`) - ON DELETE SET NULL - ON UPDATE RESTRICT; -- 2.25.1