From 76ace447f4ce133fc3a1c02c810ef4eaa7c8f8b3 Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Fri, 19 May 2023 21:09:09 -0400
Subject: [PATCH] SchemaHandler - Fix nonstandard index names + code cleanup

---
 CRM/Core/BAO/CustomGroup.php                  |  2 +-
 CRM/Core/BAO/SchemaHandler.php                | 37 +++++++++----------
 .../phpunit/CRM/Core/BAO/CustomFieldTest.php  |  2 +-
 3 files changed, 19 insertions(+), 22 deletions(-)

diff --git a/CRM/Core/BAO/CustomGroup.php b/CRM/Core/BAO/CustomGroup.php
index fedf435938..5ce0d84311 100644
--- a/CRM/Core/BAO/CustomGroup.php
+++ b/CRM/Core/BAO/CustomGroup.php
@@ -188,7 +188,7 @@ class CRM_Core_BAO_CustomGroup extends CRM_Core_DAO_CustomGroup implements \Civi
       self::createTable($group);
     }
     elseif ($tableNameNeedingIndexUpdate) {
-      CRM_Core_BAO_SchemaHandler::changeUniqueToIndex($tableNameNeedingIndexUpdate, CRM_Utils_Array::value('is_multiple', $params));
+      CRM_Core_BAO_SchemaHandler::changeUniqueToIndex($tableNameNeedingIndexUpdate, !empty($params['is_multiple']));
     }
 
     if (CRM_Utils_Array::value('overrideFKConstraint', $params) == 1) {
diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index ad36d906f1..f8b3d9d547 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -156,46 +156,47 @@ class CRM_Core_BAO_SchemaHandler {
    * @param array $params
    * @param string $separator
    * @param string $prefix
-   * @param string|null $existingIndex
+   * @param string $existingIndex
    *
    * @return NULL|string
    */
-  public static function buildSearchIndexSQL($params, $separator, $prefix = '', $existingIndex = NULL) {
-    $sql = NULL;
+  public static function buildSearchIndexSQL($params, $separator, $prefix = '', $existingIndex = '') {
+    $sql = '';
 
-    // dont index blob
+    // Don't index blob
     if ($params['type'] == 'text') {
-      return $sql;
+      return NULL;
     }
 
+    // Perform case-insensitive match to see if index name begins with "index_" or "INDEX_"
+    // (for legacy reasons it could be either)
+    $searchIndexExists = stripos($existingIndex ?? '', 'index_') === 0;
+
     // Add index if field is searchable if it does not reference a foreign key
     // (skip indexing FK fields because it would be redundant to have 2 indexes)
-    if (!empty($params['searchable']) && empty($params['fk_table_name']) && substr($existingIndex ?? '', 0, 5) !== 'INDEX') {
+    if (!empty($params['searchable']) && empty($params['fk_table_name']) && !$searchIndexExists) {
       $sql .= $separator;
       $sql .= str_repeat(' ', 8);
       $sql .= $prefix;
-      $sql .= "INDEX_{$params['name']} ( {$params['name']} )";
+      $sql .= "index_{$params['name']} ( {$params['name']} )";
     }
     // Drop search index if field is no longer searchable
-    elseif (empty($params['searchable']) && substr($existingIndex ?? '', 0, 5) === 'INDEX') {
+    elseif (empty($params['searchable']) && $searchIndexExists) {
       $sql .= $separator;
       $sql .= str_repeat(' ', 8);
       $sql .= "DROP INDEX $existingIndex";
     }
-    return $sql;
+    return $sql ?: NULL;
   }
 
   /**
    * @param array $params
-   * @param $separator
-   * @param $prefix
+   * @param string $separator
    *
    * @return string
    */
-  public static function buildIndexSQL(&$params, $separator) {
-    $sql = '';
-    $sql .= $separator;
-    $sql .= str_repeat(' ', 8);
+  public static function buildIndexSQL($params, $separator = ''): string {
+    $sql = $separator . str_repeat(' ', 8);
     if ($params['unique']) {
       $sql .= 'UNIQUE INDEX';
       $indexName = 'unique';
@@ -221,10 +222,8 @@ class CRM_Core_BAO_SchemaHandler {
   /**
    * @param string $tableName
    * @param string $fkTableName
-   *
-   * @return bool
    */
-  public static function changeFKConstraint($tableName, $fkTableName) {
+  public static function changeFKConstraint($tableName, $fkTableName): void {
     $fkName = "{$tableName}_entity_id";
     if (strlen($fkName) >= 48) {
       $fkName = substr($fkName, 0, 32) . "_" . substr(md5($fkName), 0, 16);
@@ -240,8 +239,6 @@ ALTER TABLE {$tableName}
       ADD CONSTRAINT `FK_{$fkName}` FOREIGN KEY (`entity_id`) REFERENCES {$fkTableName} (`id`) ON DELETE CASCADE;";
     // CRM-7007: do not i18n-rewrite this query
     CRM_Core_DAO::executeQuery($addFKSql, [], TRUE, NULL, FALSE, FALSE);
-
-    return TRUE;
   }
 
   /**
diff --git a/tests/phpunit/CRM/Core/BAO/CustomFieldTest.php b/tests/phpunit/CRM/Core/BAO/CustomFieldTest.php
index 52bf39c037..0ca6ea7229 100644
--- a/tests/phpunit/CRM/Core/BAO/CustomFieldTest.php
+++ b/tests/phpunit/CRM/Core/BAO/CustomFieldTest.php
@@ -975,7 +975,7 @@ class CRM_Core_BAO_CustomFieldTest extends CiviUnitTestCase {
     $dao = CRM_Core_DAO::executeQuery(('SHOW CREATE TABLE ' . $customGroup['values'][$customGroup['id']]['table_name']));
     $dao->fetch();
     $this->assertStringContainsString('`test_link_2` varchar(255) COLLATE ' . CRM_Core_BAO_SchemaHandler::getInUseCollation() . ' DEFAULT NULL', $dao->Create_Table);
-    $this->assertStringContainsString('KEY `INDEX_my_text` (`my_text`)', $dao->Create_Table);
+    $this->assertStringContainsString('KEY `index_my_text` (`my_text`)', $dao->Create_Table);
   }
 
   /**
-- 
2.25.1