From f210208f51615c88df5e22cc0acf358d37d837af Mon Sep 17 00:00:00 2001
From: Seamus Lee <seamuslee001@gmail.com>
Date: Tue, 7 Sep 2021 11:23:06 +1000
Subject: [PATCH] [REF] Add in upgrade step to align the relationship cache
 table collation and charset with the other tables

---
 .../Incremental/php/FiveFortyThree.php        | 38 +++++++++++--------
 .../CRM/Upgrade/Incremental/BaseTest.php      | 10 +++++
 2 files changed, 33 insertions(+), 15 deletions(-)

diff --git a/CRM/Upgrade/Incremental/php/FiveFortyThree.php b/CRM/Upgrade/Incremental/php/FiveFortyThree.php
index e3748696fa..15073618fa 100644
--- a/CRM/Upgrade/Incremental/php/FiveFortyThree.php
+++ b/CRM/Upgrade/Incremental/php/FiveFortyThree.php
@@ -55,21 +55,29 @@ class CRM_Upgrade_Incremental_php_FiveFortyThree extends CRM_Upgrade_Incremental
    * (change the x in the function name):
    */
 
-  //  /**
-  //   * Upgrade function.
-  //   *
-  //   * @param string $rev
-  //   */
-  //  public function upgrade_5_0_x($rev) {
-  //    $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
-  //    $this->addTask('Do the foo change', 'taskFoo', ...);
-  //    // Additional tasks here...
-  //    // Note: do not use ts() in the addTask description because it adds unnecessary strings to transifex.
-  //    // The above is an exception because 'Upgrade DB to %1: SQL' is generic & reusable.
-  //  }
+  /**
+   * Upgrade function.
+   *
+   * @param string $rev
+   */
+  public function upgrade_5_43_alpha1($rev) {
+    $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
+    $this->addTask('Fix DB Collation if needed on the relatonship cache table', 'fixRelationshipCacheTableCollation');
+  }
 
-  // public static function taskFoo(CRM_Queue_TaskContext $ctx, ...) {
-  //   return TRUE;
-  // }
+  public static function fixRelationshipCacheTableCollation():bool {
+    $contactTableCollation = CRM_Core_BAO_SchemaHandler::getInUseCollation();
+    $dao = CRM_Core_DAO::executeQuery('SHOW TABLE STATUS LIKE \'civicrm_relationship_cache\'');
+    $dao->fetch();
+    $relationshipCacheCollation = $dao->Collation;
+    $characterSet = 'utf8';
+    if (stripos($contactTableCollation, 'utf8mb4') !== FALSE) {
+      $characterSet = 'utf8mb4';
+    }
+    if ($contactTableCollation !== $relationshipCacheCollation) {
+      CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_relationship_cache ENGINE=InnoDB DEFAULT CHARACTER SET ' . $characterSet . ' COLLATE ' . $contactTableCollation);
+    }
+    return TRUE;
+  }
 
 }
diff --git a/tests/phpunit/CRM/Upgrade/Incremental/BaseTest.php b/tests/phpunit/CRM/Upgrade/Incremental/BaseTest.php
index f2965df67f..544f1be597 100644
--- a/tests/phpunit/CRM/Upgrade/Incremental/BaseTest.php
+++ b/tests/phpunit/CRM/Upgrade/Incremental/BaseTest.php
@@ -738,4 +738,14 @@ class CRM_Upgrade_Incremental_BaseTest extends CiviUnitTestCase {
     $this->callAPISuccess('ContactType', 'delete', ['id' => $contactType['id']]);
   }
 
+  public function testUpdateRelationshipCacheTable() {
+    CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_relationship_cache DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
+    CRM_Upgrade_Incremental_php_FiveFortyThree::fixRelationshipCacheTableCollation();
+    $contactTableCollation = CRM_Core_BAO_SchemaHandler::getInUseCollation();
+    $dao = CRM_Core_DAO::executeQuery('SHOW TABLE STATUS LIKE \'civicrm_relationship_cache\'');
+    $dao->fetch();
+    $relationshipCacheCollation = $dao->Collation;
+    $this->assertEquals($contactTableCollation, $relationshipCacheCollation);
+  }
+
 }
-- 
2.25.1