From a0efcd4d8502a7b95ed75818515fa9c88e1e8e65 Mon Sep 17 00:00:00 2001
From: Eileen McNaughton <emcnaughton@wikimedia.org>
Date: Wed, 12 Jan 2022 16:00:59 +1300
Subject: [PATCH] Add created_date & modified_date to civicrm_relationship

As with other tables we have done this to, knowing when we added a record to the
DB is useful (and differs from the relationship start_date).

I'm not sure yet if I will try to expose in the UI or whether search-kit
is all we need
---
 CRM/Contact/DAO/Relationship.php              | 45 ++++++++++++++++++-
 .../Incremental/php/FiveFortySeven.php        | 25 +++++++++++
 xml/schema/Contact/Relationship.xml           | 18 ++++++++
 3 files changed, 87 insertions(+), 1 deletion(-)

diff --git a/CRM/Contact/DAO/Relationship.php b/CRM/Contact/DAO/Relationship.php
index 0e00c29446..5ed913d219 100644
--- a/CRM/Contact/DAO/Relationship.php
+++ b/CRM/Contact/DAO/Relationship.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Contact/Relationship.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:4d689d3ccc5aa155c858aac2f24ddfab)
+ * (GenCodeChecksum:31dfa249bbf980b8094bce3ff7f70fc9)
  */
 
 /**
@@ -124,6 +124,20 @@ class CRM_Contact_DAO_Relationship extends CRM_Core_DAO {
    */
   public $case_id;
 
+  /**
+   * Relationship created date.
+   *
+   * @var timestamp
+   */
+  public $created_date;
+
+  /**
+   * Relationship last modified.
+   *
+   * @var timestamp
+   */
+  public $modified_date;
+
   /**
    * Class constructor.
    */
@@ -367,6 +381,35 @@ class CRM_Contact_DAO_Relationship extends CRM_Core_DAO {
           ],
           'add' => '2.2',
         ],
+        'created_date' => [
+          'name' => 'created_date',
+          'type' => CRM_Utils_Type::T_TIMESTAMP,
+          'title' => ts('Created Date'),
+          'description' => ts('Relationship created date.'),
+          'required' => TRUE,
+          'where' => 'civicrm_relationship.created_date',
+          'default' => 'CURRENT_TIMESTAMP',
+          'table_name' => 'civicrm_relationship',
+          'entity' => 'Relationship',
+          'bao' => 'CRM_Contact_BAO_Relationship',
+          'localizable' => 0,
+          'add' => '5.47',
+        ],
+        'modified_date' => [
+          'name' => 'modified_date',
+          'type' => CRM_Utils_Type::T_TIMESTAMP,
+          'title' => ts('Relationship Modified Date'),
+          'description' => ts('Relationship last modified.'),
+          'required' => TRUE,
+          'where' => 'civicrm_relationship.modified_date',
+          'default' => 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
+          'table_name' => 'civicrm_relationship',
+          'entity' => 'Relationship',
+          'bao' => 'CRM_Contact_BAO_Relationship',
+          'localizable' => 0,
+          'readonly' => TRUE,
+          'add' => '5.47',
+        ],
       ];
       CRM_Core_DAO_AllCoreTables::invoke(__CLASS__, 'fields_callback', Civi::$statics[__CLASS__]['fields']);
     }
diff --git a/CRM/Upgrade/Incremental/php/FiveFortySeven.php b/CRM/Upgrade/Incremental/php/FiveFortySeven.php
index cd4eb02287..6e5978bc8d 100644
--- a/CRM/Upgrade/Incremental/php/FiveFortySeven.php
+++ b/CRM/Upgrade/Incremental/php/FiveFortySeven.php
@@ -30,11 +30,36 @@ class CRM_Upgrade_Incremental_php_FiveFortySeven extends CRM_Upgrade_Incremental
   public function upgrade_5_47_alpha1($rev): void {
     $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
     $this->addTask('Migrate CiviGrant component to an extension', 'migrateCiviGrant');
+    $this->addTask('Add created_date to civicrm_relationship', 'addColumn', 'civicrm_relationship', 'created_date',
+      "timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'Relationship created date'"
+    );
+    $this->addTask('Add modified_date column to civicrm_relationship', 'addColumn',
+      'civicrm_relationship', 'modified_date',
+      "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Relationship last modified.'"
+    );
+    $this->addTask('Set initial value for relationship created_date and modified_date to start_date', 'updateRelationshipDates');
   }
 
   /**
    * @param \CRM_Queue_TaskContext $ctx
+   *
+   * @return bool
+   */
+  public static function updateRelationshipDates(CRM_Queue_TaskContext $ctx): bool {
+    CRM_Core_DAO::executeQuery('
+      UPDATE civicrm_relationship SET created_date = start_date, modified_date = start_date
+      WHERE start_date IS NOT NULL AND start_date > "1970-01-01"
+    ');
+    return TRUE;
+  }
+
+  /**
+   * @param \CRM_Queue_TaskContext $ctx
+   *
    * @return bool
+   * @throws \API_Exception
+   * @throws \CRM_Core_Exception
+   * @throws \Civi\API\Exception\NotImplementedException
    */
   public static function migrateCiviGrant(CRM_Queue_TaskContext $ctx): bool {
     $civiGrantEnabled = in_array('CiviGrant', Civi::settings()->get('enable_components'), TRUE);
diff --git a/xml/schema/Contact/Relationship.xml b/xml/schema/Contact/Relationship.xml
index 9151dc12e5..141ae932c6 100644
--- a/xml/schema/Contact/Relationship.xml
+++ b/xml/schema/Contact/Relationship.xml
@@ -178,6 +178,24 @@
     </html>
     <add>2.2</add>
   </field>
+  <field>
+    <name>created_date</name>
+    <type>timestamp</type>
+    <comment>Relationship created date.</comment>
+    <required>true</required>
+    <default>CURRENT_TIMESTAMP</default>
+    <add>5.47</add>
+  </field>
+  <field>
+    <name>modified_date</name>
+    <title>Relationship Modified Date</title>
+    <type>timestamp</type>
+    <required>true</required>
+    <comment>Relationship last modified.</comment>
+    <default>CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP</default>
+    <add>5.47</add>
+    <readonly>true</readonly>
+  </field>
   <foreignKey>
     <name>case_id</name>
     <table>civicrm_case</table>
-- 
2.25.1