From 44379fac1df46b58a38a6e7259efcc48a033931f Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Sun, 27 Oct 2013 09:05:40 -0700
Subject: [PATCH] CRM-13655 - Upgrade/.../FourFour.php - Setup correct schema
 for civicrm_word_replacement (4.4.0 => 4.4.1)

----------------------------------------
* CRM-13655: WordReplacement - Uniqueness key should allow different cases and domains
  http://issues.civicrm.org/jira/browse/CRM-13655
---
 CRM/Upgrade/Incremental/php/FourFour.php | 25 +++++++++++++++++++++++-
 xml/schema/Core/WordReplacement.xml      |  2 +-
 2 files changed, 25 insertions(+), 2 deletions(-)

diff --git a/CRM/Upgrade/Incremental/php/FourFour.php b/CRM/Upgrade/Incremental/php/FourFour.php
index b6a4d5264f..a74c71d922 100644
--- a/CRM/Upgrade/Incremental/php/FourFour.php
+++ b/CRM/Upgrade/Incremental/php/FourFour.php
@@ -141,6 +141,7 @@ class CRM_Upgrade_Incremental_php_FourFour {
     }
 
     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.4.1')), 'task_4_4_x_runSql', $rev);
+    $this->addTask('Patch word-replacement schema', 'wordReplacements_patch', $rev);
   }
 
   /**
@@ -261,7 +262,7 @@ CREATE TABLE IF NOT EXISTS `civicrm_word_replacement` (
      `match_type` enum('wildcardMatch', 'exactMatch')   DEFAULT 'wildcardMatch',
      `domain_id` int unsigned    COMMENT 'FK to Domain ID. This is for Domain specific word replacement',
     PRIMARY KEY ( `id` ),
-    UNIQUE INDEX `UI_find`(domain_id, find_word),
+    UNIQUE INDEX `UI_domain_find` (domain_id, find_word),
     CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
     ";
@@ -271,6 +272,28 @@ CREATE TABLE IF NOT EXISTS `civicrm_word_replacement` (
     return TRUE;
   }
 
+  /**
+   * 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).
+   *
+   * @return bool TRUE for success
+   * @see http://issues.civicrm.org/jira/browse/CRM-13655
+   */
+  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;
+  }
+
   /**
    * (Queue Task Callback)
    */
diff --git a/xml/schema/Core/WordReplacement.xml b/xml/schema/Core/WordReplacement.xml
index 2d5f23ec4c..296814fd80 100644
--- a/xml/schema/Core/WordReplacement.xml
+++ b/xml/schema/Core/WordReplacement.xml
@@ -41,7 +41,7 @@
     <add>4.4</add>
   </field>
   <index>
-    <name>UI_find</name>
+    <name>UI_domain_find</name>
     <fieldName>domain_id</fieldName>
     <fieldName>find_word</fieldName>
     <unique>true</unique>
-- 
2.25.1