From 3949fd776db49a5c835bf5c3c44dae233c5af5b7 Mon Sep 17 00:00:00 2001
From: demeritcowboy <demeritcowboy@hotmail.com>
Date: Wed, 26 Jul 2023 22:54:37 -0400
Subject: [PATCH] fix dangerous delete cascade

---
 CRM/Activity/DAO/Activity.php                 |  2 +-
 CRM/Upgrade/Incremental/php/FiveSixtyFour.php | 36 +++++++++++++++++++
 .../Incremental/sql/5.64.beta1.mysql.tpl      |  1 +
 xml/schema/Activity/Activity.xml              |  2 +-
 4 files changed, 39 insertions(+), 2 deletions(-)
 create mode 100644 CRM/Upgrade/Incremental/sql/5.64.beta1.mysql.tpl

diff --git a/CRM/Activity/DAO/Activity.php b/CRM/Activity/DAO/Activity.php
index bf37517561..544e93db8e 100644
--- a/CRM/Activity/DAO/Activity.php
+++ b/CRM/Activity/DAO/Activity.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Activity/Activity.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:e1adbd9104050ebb0d274cf885a2de67)
+ * (GenCodeChecksum:5e64c1c4489e9c640f15d3a4877f7331)
  */
 
 /**
diff --git a/CRM/Upgrade/Incremental/php/FiveSixtyFour.php b/CRM/Upgrade/Incremental/php/FiveSixtyFour.php
index 177270d03d..6c7492f65f 100644
--- a/CRM/Upgrade/Incremental/php/FiveSixtyFour.php
+++ b/CRM/Upgrade/Incremental/php/FiveSixtyFour.php
@@ -21,6 +21,20 @@
  */
 class CRM_Upgrade_Incremental_php_FiveSixtyFour extends CRM_Upgrade_Incremental_Base {
 
+  /**
+   * How many activities before the queries used here are slow. Guessing.
+   */
+  const ACTIVITY_THRESHOLD = 1000000;
+
+  public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
+    if ($rev === '5.64.beta1') {
+      // The ON DELETE constraint drop+recreate can be slow, so tell people what to do at their convenience if db is large.
+      if (CRM_Core_DAO::singleValueQuery('SELECT MAX(id) FROM civicrm_activity') >= self::ACTIVITY_THRESHOLD) {
+        $preUpgradeMessage .= '<p>' . ts('<strong>ACTION REQUIRED</strong>: You will need to apply a <strong>manual update</strong> because your civicrm_activity table is large and the update will run slowly. Please read about <a %1>how to apply this update manually</a>.', [1 => 'target="_blank" href="https://civicrm.org/redirect/activities-parentid-cascade"']) . '</p>';
+      }
+    }
+  }
+
   /**
    * Upgrade step; adds tasks including 'runSql'.
    *
@@ -36,6 +50,19 @@ class CRM_Upgrade_Incremental_php_FiveSixtyFour extends CRM_Upgrade_Incremental_
     $this->addTask('Fix double json encoding of accepted_credit_cards field in payment processor table', 'fixDoubleEscapingPaymentProcessorCreditCards');
   }
 
+  /**
+   * Upgrade step; adds tasks including 'runSql'.
+   *
+   * @param string $rev
+   *   The version number matching this function name
+   */
+  public function upgrade_5_64_beta1($rev): void {
+    $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
+    if (CRM_Core_DAO::singleValueQuery('SELECT MAX(id) FROM civicrm_activity') < self::ACTIVITY_THRESHOLD) {
+      $this->addTask('Fix dangerous delete cascade', 'fixDeleteCascade');
+    }
+  }
+
   public static function updateLogging($ctx): bool {
     if (\Civi::settings()->get('logging')) {
       $dsn = defined('CIVICRM_LOGGING_DSN') ? CRM_Utils_SQL::autoSwitchDSN(CIVICRM_LOGGING_DSN) : CRM_Utils_SQL::autoSwitchDSN(CIVICRM_DSN);
@@ -78,4 +105,13 @@ CHANGE `cancel_URL` `cancel_url` varchar(255) DEFAULT NULL COMMENT 'Redirect to
     return TRUE;
   }
 
+  /**
+   * Fix DELETE CASCADE that can lead to loss of data.
+   */
+  public static function fixDeleteCascade($ctx): bool {
+    CRM_Core_BAO_SchemaHandler::safeRemoveFK('civicrm_activity', 'FK_civicrm_activity_parent_id');
+    CRM_Core_DAO::executeQuery('ALTER TABLE `civicrm_activity` ADD CONSTRAINT `FK_civicrm_activity_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE SET NULL');
+    return TRUE;
+  }
+
 }
diff --git a/CRM/Upgrade/Incremental/sql/5.64.beta1.mysql.tpl b/CRM/Upgrade/Incremental/sql/5.64.beta1.mysql.tpl
new file mode 100644
index 0000000000..543a06eb41
--- /dev/null
+++ b/CRM/Upgrade/Incremental/sql/5.64.beta1.mysql.tpl
@@ -0,0 +1 @@
+{* file to handle db changes in 5.64.beta1 during upgrade *}
diff --git a/xml/schema/Activity/Activity.xml b/xml/schema/Activity/Activity.xml
index aff1261ef9..bc55111df0 100644
--- a/xml/schema/Activity/Activity.xml
+++ b/xml/schema/Activity/Activity.xml
@@ -231,7 +231,7 @@
     <table>civicrm_activity</table>
     <key>id</key>
     <add>1.1</add>
-    <onDelete>CASCADE</onDelete>
+    <onDelete>SET NULL</onDelete>
   </foreignKey>
   <field>
     <name>is_test</name>
-- 
2.25.1