From c348f26df9c2110e603dfec94ddbcd3bf048ef46 Mon Sep 17 00:00:00 2001
From: Dave Greenberg <dave@civicrm.org>
Date: Tue, 14 Apr 2015 16:51:20 -0700
Subject: [PATCH] CRM-16289 - Upgrade fix for log_civicrm_case.case_type_id.

----------------------------------------
* CRM-16289: Remove special characters from log_civicrm_case.case_type_id data during upgrade
  https://issues.civicrm.org/jira/browse/CRM-16289
---
 CRM/Upgrade/Incremental/php/FourSix.php | 37 +++++++++++++++++++++++++
 1 file changed, 37 insertions(+)

diff --git a/CRM/Upgrade/Incremental/php/FourSix.php b/CRM/Upgrade/Incremental/php/FourSix.php
index 227d47ab67..e3e2163bba 100644
--- a/CRM/Upgrade/Incremental/php/FourSix.php
+++ b/CRM/Upgrade/Incremental/php/FourSix.php
@@ -182,4 +182,41 @@ class CRM_Upgrade_Incremental_php_FourSix {
     return TRUE;
   }
 
+  /**
+   * Upgrade function.
+   *
+   * @param string $rev
+   */
+  public function upgrade_4_6_1($rev) {
+    // CRM-16289 - Fix invalid data in log_civicrm_case.case_type_id.
+    $this->addTask(ts('Cleanup case type id data in log table.'), 'fixCaseLog');
+  }
+  
+   /**
+    * Remove special characters from case_type_id column in log_civicrm_case.
+    *
+    * CRM-16289 - If logging enabled and upgrading from 4.4 or earlier, log_civicrm_case.case_type_id will contain special characters.
+    * This will cause ALTER TABLE to fail when changing this column to an INT
+    *
+    * @param \CRM_Queue_TaskContext $ctx
+    *
+    * @return bool
+    */
+  public static function fixCaseLog(CRM_Queue_TaskContext $ctx) {
+    $sql = "SELECT count(*) FROM information_schema.columns WHERE table_schema = database() AND table_name = 'log_civicrm_case'";
+    $res = CRM_Core_DAO::singleValueQuery($sql);
+
+    if ($res) {
+      // executeQuery doesn't like running multiple engine changes in one pass, so have to break it up. dgg 
+      $query = "ALTER TABLE `log_civicrm_case` ENGINE = InnoDB;";
+      CRM_Core_DAO::executeQuery($query);
+      $query = "UPDATE log_civicrm_case SET case_type_id = replace(case_type_id, 0x01, '');";
+      CRM_Core_DAO::executeQuery($query);
+      $query = "ALTER TABLE `log_civicrm_case` ENGINE = ARCHIVE;";
+      CRM_Core_DAO::executeQuery($query);
+      $query = "ALTER TABLE log_civicrm_case MODIFY `case_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_case_type.id';";
+      CRM_Core_DAO::executeQuery($query);      
+    }
+    return TRUE;
+  }
 }
-- 
2.25.1