From a6acc355a44a1894d8b50d9dbd4948a1a6cbe8b7 Mon Sep 17 00:00:00 2001
From: Matthew Wire <mjw@mjwconsult.co.uk>
Date: Fri, 14 Oct 2022 14:33:39 +0100
Subject: [PATCH] Make job ID accessible to searchkit for joblog

---
 CRM/Core/DAO/JobLog.php                   | 11 ++++++---
 CRM/Upgrade/Incremental/php/FiveSixty.php | 27 ++++++++++++++++++++++-
 xml/schema/Core/JobLog.xml                | 12 +++++++++-
 3 files changed, 45 insertions(+), 5 deletions(-)

diff --git a/CRM/Core/DAO/JobLog.php b/CRM/Core/DAO/JobLog.php
index 3b0e98bb13..c38d1e7770 100644
--- a/CRM/Core/DAO/JobLog.php
+++ b/CRM/Core/DAO/JobLog.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Core/JobLog.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:258a172d38252619a97c1d74663bdd87)
+ * (GenCodeChecksum:98cdc7ac1580bf4029404b0e69fa401c)
  */
 
 /**
@@ -58,7 +58,7 @@ class CRM_Core_DAO_JobLog extends CRM_Core_DAO {
   public $run_time;
 
   /**
-   * Pointer to job id - not a FK though, just for logging purposes
+   * Pointer to job id
    *
    * @var int|string|null
    *   (SQL type: int unsigned)
@@ -130,6 +130,7 @@ class CRM_Core_DAO_JobLog extends CRM_Core_DAO {
     if (!isset(Civi::$statics[__CLASS__]['links'])) {
       Civi::$statics[__CLASS__]['links'] = static::createReferenceColumns(__CLASS__);
       Civi::$statics[__CLASS__]['links'][] = new CRM_Core_Reference_Basic(self::getTableName(), 'domain_id', 'civicrm_domain', 'id');
+      Civi::$statics[__CLASS__]['links'][] = new CRM_Core_Reference_Basic(self::getTableName(), 'job_id', 'civicrm_job', 'id');
       CRM_Core_DAO_AllCoreTables::invoke(__CLASS__, 'links_callback', Civi::$statics[__CLASS__]['links']);
     }
     return Civi::$statics[__CLASS__]['links'];
@@ -198,12 +199,16 @@ class CRM_Core_DAO_JobLog extends CRM_Core_DAO {
           'name' => 'job_id',
           'type' => CRM_Utils_Type::T_INT,
           'title' => ts('Job ID'),
-          'description' => ts('Pointer to job id - not a FK though, just for logging purposes'),
+          'description' => ts('Pointer to job id'),
           'where' => 'civicrm_job_log.job_id',
           'table_name' => 'civicrm_job_log',
           'entity' => 'JobLog',
           'bao' => 'CRM_Core_DAO_JobLog',
           'localizable' => 0,
+          'FKClassName' => 'CRM_Core_DAO_Job',
+          'html' => [
+            'type' => 'Number',
+          ],
           'add' => '4.1',
         ],
         'name' => [
diff --git a/CRM/Upgrade/Incremental/php/FiveSixty.php b/CRM/Upgrade/Incremental/php/FiveSixty.php
index 70afe8046f..763a325d79 100644
--- a/CRM/Upgrade/Incremental/php/FiveSixty.php
+++ b/CRM/Upgrade/Incremental/php/FiveSixty.php
@@ -31,7 +31,7 @@ class CRM_Upgrade_Incremental_php_FiveSixty extends CRM_Upgrade_Incremental_Base
     $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
     $this->addTask('Add scheduled_reminder_smarty setting', 'addScheduledReminderSmartySetting');
     $this->addTask('Add column civicrm_custom_field.fk_entity', 'addColumn', 'civicrm_custom_field', 'fk_entity', "varchar(255) DEFAULT NULL COMMENT 'Name of entity being referenced.'");
-
+    $this->addTask('Add foreign key from civicrm_job_log to civicrm_job', 'addJobLogForeignKey');
   }
 
   public function setPostUpgradeMessage(&$postUpgradeMessage, $rev): void {
@@ -45,4 +45,29 @@ class CRM_Upgrade_Incremental_php_FiveSixty extends CRM_Upgrade_Incremental_Base
     return TRUE;
   }
 
+  /**
+   * Add FK to civicrm_job_log.job_id
+   *
+   * @return bool
+   */
+  public static function addJobLogForeignKey(): bool {
+    // Update the comment for the job_id column
+    $commentQuery = 'ALTER TABLE civicrm_job_log MODIFY COLUMN `job_id` int(10) unsigned DEFAULT NULL COMMENT \'FK to civicrm_job.id\'';
+    CRM_Core_DAO::executeQuery($commentQuery);
+
+    // Set job_id = NULL for any that don't have matching jobs (ie. job was deleted).
+    $updateQuery = 'UPDATE civicrm_job_log job_log LEFT JOIN civicrm_job job ON job.id = job_log.id SET job_id = NULL WHERE job.id IS NULL';
+
+    CRM_Core_DAO::executeQuery($updateQuery);
+    // Add the foreign key
+    $sql = CRM_Core_BAO_SchemaHandler::buildForeignKeySQL([
+      'fk_table_name' => 'civicrm_job',
+      'fk_field_name' => 'id',
+      'name' => 'job_id',
+      'fk_attributes' => ' ON DELETE SET NULL',
+    ], "\n", " ADD ", 'civicrm_job_log');
+    CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_job_log " . $sql, [], TRUE, NULL, FALSE, FALSE);
+    return TRUE;
+  }
+
 }
diff --git a/xml/schema/Core/JobLog.xml b/xml/schema/Core/JobLog.xml
index 9fb80bfdac..56609faf20 100644
--- a/xml/schema/Core/JobLog.xml
+++ b/xml/schema/Core/JobLog.xml
@@ -55,9 +55,19 @@
     <name>job_id</name>
     <title>Job ID</title>
     <type>int unsigned</type>
-    <comment>Pointer to job id - not a FK though, just for logging purposes</comment>
+    <comment>Pointer to job id</comment>
+    <html>
+      <type>Number</type>
+    </html>
     <add>4.1</add>
   </field>
+  <foreignKey>
+    <name>job_id</name>
+    <table>civicrm_job</table>
+    <key>id</key>
+    <add>5.60</add>
+    <onDelete>SET NULL</onDelete>
+  </foreignKey>
   <field>
     <name>name</name>
     <title>Job Name</title>
-- 
2.25.1