From 8506cda62cd97fd1a7ceb8334135037ef99f65a6 Mon Sep 17 00:00:00 2001
From: "deb.monish" <monish.deb@jmaconsulting.biz>
Date: Mon, 13 Aug 2018 13:10:47 +0530
Subject: [PATCH] dev/core#321: Prevent duplicate entries in
 civicrm_entity_file

---
 CRM/Case/Form/Activity.php               |  2 +-
 CRM/Core/BAO/SchemaHandler.php           | 11 ++++++++
 CRM/Core/DAO/EntityFile.php              |  9 ++++---
 CRM/Upgrade/Incremental/php/FiveFive.php | 34 ++++++++++++++++++++++++
 xml/schema/Core/EntityFile.xml           |  3 ++-
 5 files changed, 53 insertions(+), 6 deletions(-)

diff --git a/CRM/Case/Form/Activity.php b/CRM/Case/Form/Activity.php
index 7fd6f54d7d..629dab3f33 100644
--- a/CRM/Case/Form/Activity.php
+++ b/CRM/Case/Form/Activity.php
@@ -533,7 +533,7 @@ class CRM_Case_Form_Activity extends CRM_Activity_Form_Activity {
       }
       // copy files attached to old activity if any, to new one,
       // as long as users have not selected the 'delete attachment' option.
-      if (empty($newActParams['is_delete_attachment'])) {
+      if (empty($newActParams['is_delete_attachment']) && ($this->_activityId != $activity->id)) {
         CRM_Core_BAO_File::copyEntityFile('civicrm_activity', $this->_activityId,
           'civicrm_activity', $activity->id
         );
diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index 1a926044ff..b9befac3dd 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -411,6 +411,17 @@ ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )";
     CRM_Core_DAO::executeQuery($sql);
   }
 
+  /**
+   * @param string $tableName
+   * @param array $fields
+   * @param string $prefix
+   */
+  public static function createUniqueIndex($tableName, $fields, $prefix = 'UI') {
+    $uniqueIndexName = sprintf('%s_%s', $prefix, implode('_', $fields));
+    $sql = sprintf(" ALTER TABLE %s ADD UNIQUE INDEX `%s` (%s)", $tableName, $uniqueIndexName, implode(', ', $fields));
+    CRM_Core_DAO::executeQuery($sql);
+  }
+
   /**
    * Create indexes.
    *
diff --git a/CRM/Core/DAO/EntityFile.php b/CRM/Core/DAO/EntityFile.php
index 818b94e3ad..32efe86298 100644
--- a/CRM/Core/DAO/EntityFile.php
+++ b/CRM/Core/DAO/EntityFile.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Core/EntityFile.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:354c22131251fde259f5b796e102fccf)
+ * (GenCodeChecksum:d2d6205c8973c5ad7a989ecb674b9f94)
  */
 
 /**
@@ -213,15 +213,16 @@ class CRM_Core_DAO_EntityFile extends CRM_Core_DAO {
         'localizable' => FALSE,
         'sig' => 'civicrm_entity_file::0::entity_table::entity_id',
       ],
-      'index_entity_file_id' => [
-        'name' => 'index_entity_file_id',
+      'UI_entity_table_entity_id_file_id' => [
+        'name' => 'UI_entity_table_entity_id_file_id',
         'field' => [
           0 => 'entity_table',
           1 => 'entity_id',
           2 => 'file_id',
         ],
         'localizable' => FALSE,
-        'sig' => 'civicrm_entity_file::0::entity_table::entity_id::file_id',
+        'unique' => TRUE,
+        'sig' => 'civicrm_entity_file::1::entity_table::entity_id::file_id',
       ],
     ];
     return ($localize && !empty($indices)) ? CRM_Core_DAO_AllCoreTables::multilingualize(__CLASS__, $indices) : $indices;
diff --git a/CRM/Upgrade/Incremental/php/FiveFive.php b/CRM/Upgrade/Incremental/php/FiveFive.php
index 3db8c87526..e563ebe6c6 100644
--- a/CRM/Upgrade/Incremental/php/FiveFive.php
+++ b/CRM/Upgrade/Incremental/php/FiveFive.php
@@ -46,6 +46,40 @@ class CRM_Upgrade_Incremental_php_FiveFive extends CRM_Upgrade_Incremental_Base
     // }
   }
 
+  /**
+   * Upgrade function.
+   *
+   * @param string $rev
+   */
+  public function upgrade_5_5_alpha1($rev) {
+    $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => $rev)), 'runSql', $rev);
+    $this->addTask('Entity File schema updates', 'entityFileSchemaUpdate');
+  }
+
+  /**
+   * dev/core#321 - Prevent duplicate entries in civicrm_entity_file
+   *
+   * @param \CRM_Queue_TaskContext $ctx
+   *
+   * @return bool
+   */
+  public static function entityFileSchemaUpdate(CRM_Queue_TaskContext $ctx) {
+    if (CRM_Core_BAO_SchemaHandler::checkIfIndexExists('civicrm_entity_file', 'index_entity_file_id')) {
+      // Delete any stray duplicate rows and add unique index to prevent new dupes and enable INSERT/UPDATE combo query
+      CRM_Core_DAO::executeQuery('
+        DELETE ef1 FROM civicrm_entity_file ef1, civicrm_entity_file ef2
+          WHERE ef1.entity_table = ef2.entity_table AND
+           ef1.entity_id = ef2.entity_id AND
+           ef1.file_id = ef2.file_id AND
+           ef1.id > ef2.id
+      ');
+      CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_entity_file DROP INDEX `index_entity_file_id`');
+      CRM_Core_BAO_SchemaHandler::createUniqueIndex('civicrm_entity_file', ['entity_table', 'entity_id', 'file_id']);
+    }
+
+    return TRUE;
+  }
+
   /**
    * Compute any messages which should be displayed after upgrade.
    *
diff --git a/xml/schema/Core/EntityFile.xml b/xml/schema/Core/EntityFile.xml
index b4a0f2387b..e4b06ffd25 100644
--- a/xml/schema/Core/EntityFile.xml
+++ b/xml/schema/Core/EntityFile.xml
@@ -60,10 +60,11 @@
     <add>1.5</add>
   </foreignKey>
   <index>
-    <name>index_entity_file_id</name>
+    <name>UI_entity_table_entity_id_file_id</name>
     <fieldName>entity_table</fieldName>
     <fieldName>entity_id</fieldName>
     <fieldName>file_id</fieldName>
+    <unique>true</unique>
     <add>1.1</add>
   </index>
 </table>
-- 
2.25.1