From e11f2dc3eaddcdde1dc49c9ae5e41100ec8df85e Mon Sep 17 00:00:00 2001
From: Matthew Wire <mjw@mjwconsult.co.uk>
Date: Wed, 6 Dec 2023 12:14:22 +0000
Subject: [PATCH] Add unique constraint on ufmatch user ID

---
 CRM/Core/BAO/UFMatch.php          | 28 ++++++++++++++++++++++++++++
 CRM/Core/DAO/UFMatch.php          | 11 ++++++++++-
 CRM/Utils/Check/Component/Cms.php | 27 +++++++++++++++++++++++++++
 xml/schema/Core/UFMatch.xml       |  6 ++++++
 4 files changed, 71 insertions(+), 1 deletion(-)

diff --git a/CRM/Core/BAO/UFMatch.php b/CRM/Core/BAO/UFMatch.php
index 6290430fd0..3d874a6d65 100644
--- a/CRM/Core/BAO/UFMatch.php
+++ b/CRM/Core/BAO/UFMatch.php
@@ -627,4 +627,32 @@ AND    domain_id    = %4
     return $clauses;
   }
 
+  /**
+   * This checks and adds a unique index on (uf_id,domain_id)
+   *
+   * @return bool
+   * @throws \Civi\Core\Exception\DBQueryException
+   */
+  public static function tryToAddUniqueIndexOnUfId(): bool {
+    if (!CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_uf_match', 'UI_uf_match_uf_id_domain_id')) {
+      // Run a query to check if we have duplicates
+      $query = 'SELECT COUNT(*) FROM civicrm_uf_match
+GROUP BY uf_id,domain_id
+HAVING COUNT(*) > 1';
+      $dao = CRM_Core_DAO::executeQuery($query);
+      if ($dao->fetch()) {
+        // Tell the user they need to fix it manually
+        \Civi::log()->error('You have multiple records with the same uf_id in civicrm_uf_match. You need to manually fix this in the database so that uf_id is unique.');
+        return FALSE;
+      }
+      else {
+        // Add the unique index
+        CRM_Core_DAO::executeQuery("
+        ALTER TABLE civicrm_uf_match ADD UNIQUE INDEX UI_uf_match_uf_id_domain_id (uf_id,domain_id);
+      ");
+      }
+    }
+    return TRUE;
+  }
+
 }
diff --git a/CRM/Core/DAO/UFMatch.php b/CRM/Core/DAO/UFMatch.php
index aaa71a8278..58948427a7 100644
--- a/CRM/Core/DAO/UFMatch.php
+++ b/CRM/Core/DAO/UFMatch.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Core/UFMatch.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:ddf0ceeb22715a1ee0b7b93c90df31f2)
+ * (GenCodeChecksum:19280813191bcf496c8e43eb9778157a)
  */
 
 /**
@@ -336,6 +336,15 @@ class CRM_Core_DAO_UFMatch extends CRM_Core_DAO {
         'localizable' => FALSE,
         'sig' => 'civicrm_uf_match::0::uf_id',
       ],
+      'UI_uf_match_uf_id_domain_id' => [
+        'name' => 'UI_uf_match_uf_id_domain_id',
+        'field' => [
+          0 => 'uf_id',
+          1 => 'domain_id',
+        ],
+        'localizable' => FALSE,
+        'sig' => 'civicrm_uf_match::0::uf_id::domain_id',
+      ],
       'UI_uf_name_domain_id' => [
         'name' => 'UI_uf_name_domain_id',
         'field' => [
diff --git a/CRM/Utils/Check/Component/Cms.php b/CRM/Utils/Check/Component/Cms.php
index d69083c462..68e737c463 100644
--- a/CRM/Utils/Check/Component/Cms.php
+++ b/CRM/Utils/Check/Component/Cms.php
@@ -200,4 +200,31 @@ class CRM_Utils_Check_Component_Cms extends CRM_Utils_Check_Component {
     return (int) ($basePage->post_status == 'publish');
   }
 
+  /**
+   * Check if we created unique index on civicrm_uf_match (uf_id,domain_id)
+   *
+   * @return CRM_Utils_Check_Message[]
+   */
+  public static function checkUfMatchUnique(): array {
+    $checks = [];
+
+    if (CRM_Core_BAO_UFMatch::tryToAddUniqueIndexOnUfId()) {
+      // Already done. Success!
+      return $checks;
+    }
+
+    // Your DB has multiple uf_match records! Bad
+    $checks[] = new CRM_Utils_Check_Message(
+      __FUNCTION__,
+      ts('You have multiple records with the same uf_id in civicrm_uf_match. You need to manually fix this in the database so that uf_id is unique') .
+      ' ' .
+      CRM_Utils_System::docURL2('sysadmin/upgrade/todo/#todo'),
+      ts('Duplicate records in UFMatch'),
+      \Psr\Log\LogLevel::ERROR,
+      'fa-database'
+    );
+
+    return $checks;
+  }
+
 }
diff --git a/xml/schema/Core/UFMatch.xml b/xml/schema/Core/UFMatch.xml
index 6133aab373..ef10a14b75 100644
--- a/xml/schema/Core/UFMatch.xml
+++ b/xml/schema/Core/UFMatch.xml
@@ -58,6 +58,12 @@
     <fieldName>uf_id</fieldName>
     <add>3.3</add>
   </index>
+  <index>
+    <name>UI_uf_match_uf_id_domain_id</name>
+    <fieldName>uf_id</fieldName>
+    <fieldName>domain_id</fieldName>
+    <add>5.69</add>
+  </index>
   <field>
     <name>uf_name</name>
     <title>CMS Unique Identifier</title>
-- 
2.25.1