From aedfc3ed14e5a999a2c7c284a3478229841e9342 Mon Sep 17 00:00:00 2001
From: Matthew Wire <mjw@mjwconsult.co.uk>
Date: Wed, 22 Apr 2020 10:48:36 +0100
Subject: [PATCH] Support more table names for utf8mb4 conversions and database
 name

---
 CRM/Core/BAO/SchemaHandler.php | 52 +++++++++++++++++++++++-----------
 api/v3/System.php              | 18 +++++++++++-
 2 files changed, 53 insertions(+), 17 deletions(-)

diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index 5523858d6a..519b152cfd 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -796,30 +796,50 @@ MODIFY      {$columnName} varchar( $length )
    *
    * @param bool $revert
    *   Being able to revert if primarily for unit testing.
+   * @param array $patterns
+   *   Defaults to ['civicrm\_%'] but can be overridden to specify any pattern. eg ['civicrm\_%', 'civi%\_%', 'veda%\_%'].
+   * @param array $databaseList
+   *   Allows you to specify an alternative database to the configured CiviCRM database.
    *
    * @return bool
    */
-  public static function migrateUtf8mb4($revert = FALSE) {
+  public static function migrateUtf8mb4($revert = FALSE, $patterns = ['civicrm\_%'], $databaseList = NULL) {
     $newCharSet = $revert ? 'utf8' : 'utf8mb4';
     $newCollation = $revert ? 'utf8_unicode_ci' : 'utf8mb4_unicode_ci';
     $newBinaryCollation = $revert ? 'utf8_bin' : 'utf8mb4_bin';
     $tables = [];
     $dao = new CRM_Core_DAO();
-    $database = $dao->_database;
-    CRM_Core_DAO::executeQuery("ALTER DATABASE $database CHARACTER SET = $newCharSet COLLATE = $newCollation");
-    $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS WHERE Engine = 'InnoDB' AND Name LIKE 'civicrm\_%'");
-    while ($dao->fetch()) {
-      $tables[$dao->Name] = [
-        'Engine' => $dao->Engine,
-      ];
-    }
-    $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
-    $logging_database = $dsn['database'];
-    $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `$logging_database` WHERE Engine <> 'MyISAM' AND Name LIKE 'log\_civicrm\_%'");
-    while ($dao->fetch()) {
-      $tables["$logging_database.{$dao->Name}"] = [
-        'Engine' => $dao->Engine,
-      ];
+    $databases = $databaseList ?? [$dao->_database];
+
+    $tableNameLikePatterns = [];
+    $logTableNameLikePatterns = [];
+
+    foreach ($patterns as $pattern) {
+      $pattern = CRM_Utils_Type::escape($pattern, 'String');
+      $tableNameLikePatterns[] = "Name LIKE '{$pattern}'";
+      $logTableNameLikePatterns[] = "Name LIKE 'log\_{$pattern}'";
+    }
+
+    foreach ($databases as $database) {
+      CRM_Core_DAO::executeQuery("ALTER DATABASE $database CHARACTER SET = $newCharSet COLLATE = $newCollation");
+      $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$database}` WHERE Engine = 'InnoDB' AND (" . implode(' OR ', $tableNameLikePatterns) . ")");
+      while ($dao->fetch()) {
+        $tables["{$database}.{$dao->Name}"] = [
+          'Engine' => $dao->Engine,
+        ];
+      }
+    }
+    // If we specified a list of databases assume the user knows what they are doing.
+    // If they specify the database they should also specify the pattern.
+    if (!$databaseList) {
+      $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
+      $logging_database = $dsn['database'];
+      $dao = CRM_Core_DAO::executeQuery("SHOW TABLE STATUS FROM `{$logging_database}` WHERE Engine <> 'MyISAM' AND (" . implode(' OR ', $logTableNameLikePatterns) . ")");
+      while ($dao->fetch()) {
+        $tables["{$logging_database}.{$dao->Name}"] = [
+          'Engine' => $dao->Engine,
+        ];
+      }
     }
     foreach ($tables as $table => $param) {
       $query = "ALTER TABLE $table";
diff --git a/api/v3/System.php b/api/v3/System.php
index b597c48793..c207ac9e13 100644
--- a/api/v3/System.php
+++ b/api/v3/System.php
@@ -397,7 +397,14 @@ function civicrm_api3_system_updatelogtables($params) {
  * @throws \API_Exception
  */
 function civicrm_api3_system_utf8conversion($params) {
-  if (CRM_Core_BAO_SchemaHandler::migrateUtf8mb4($params['is_revert'])) {
+  $params['patterns'] = explode(',', $params['patterns']);
+  $params['databases'] = empty($params['databases']) ? NULL : explode(',', $params['databases']);
+  if (CRM_Core_BAO_SchemaHandler::migrateUtf8mb4(
+    $params['is_revert'],
+    $params['patterns'],
+    $params['databases']
+    )
+  ) {
     return civicrm_api3_create_success(1);
   }
   throw new API_Exception('Conversion failed');
@@ -414,6 +421,15 @@ function _civicrm_api3_system_utf8conversion_spec(&$params) {
     'type' => CRM_Utils_Type::T_BOOLEAN,
     'api.default' => FALSE,
   ];
+  $params['patterns'] = [
+    'title' => ts('CSV list of table patterns (defaults to "civicrm\_%")'),
+    'type' => CRM_Utils_Type::T_STRING,
+    'api.default' => 'civicrm\_%',
+  ];
+  $params['databases'] = [
+    'title' => ts('CSV list of database names (defaults to CiviCRM database)'),
+    'type' => CRM_Utils_Type::T_STRING,
+  ];
 }
 
 /**
-- 
2.25.1