CRM/Contact/BAO/RelationshipCache.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Class CRM_Contact_BAO_RelationshipCache.
  14  */
  15 class CRM_Contact_BAO_RelationshipCache extends CRM_Contact_DAO_RelationshipCache implements \Civi\Core\HookInterface {
  16
  17   /**
  18    * The "mappings" array defines the values to put into `civicrm_relationship_cache`
  19    * using data from `civicrm_relationship rel` and `civicrm_relationship_type reltype`.
  20    *
  21    * @var array
  22    *   Array(string $intoColumn => string $selectValue)
  23    */
  24   private static $mappings = [
  25     'a_b' => [
  26       'relationship_id' => 'rel.id',
  27       'relationship_type_id' => 'rel.relationship_type_id',
  28       'orientation' => '"a_b"',
  29       'near_contact_id' => 'rel.contact_id_a',
  30       'near_relation' => 'reltype.name_a_b',
  31       'far_contact_id' => 'rel.contact_id_b',
  32       'far_relation' => 'reltype.name_b_a',
  33       'start_date' => 'rel.start_date',
  34       'end_date' => 'rel.end_date',
  35       'is_active' => 'rel.is_active',
  36       'case_id' => 'rel.case_id',
  37     ],
  38     'b_a' => [
  39       'relationship_id' => 'rel.id',
  40       'relationship_type_id' => 'rel.relationship_type_id',
  41       'orientation' => '"b_a"',
  42       'near_contact_id' => 'rel.contact_id_b',
  43       'near_relation' => 'reltype.name_b_a',
  44       'far_contact_id' => 'rel.contact_id_a',
  45       'far_relation' => 'reltype.name_a_b',
  46       'start_date' => 'rel.start_date',
  47       'end_date' => 'rel.end_date',
  48       'is_active' => 'rel.is_active',
  49       'case_id' => 'rel.case_id',
  50     ],
  51   ];
  52
  53   /**
  54    * A list of fields which uniquely identify a row.
  55    *
  56    * @var array
  57    */
  58   private static $keyFields = ['relationship_id', 'orientation'];
  59
  60   /**
  61    * A list of of fields in `civicrm_relationship_type` which (if changed)
  62    * will necessitate an update to the cache.
  63    *
  64    * @var array
  65    */
  66   private static $relTypeWatchFields = ['name_a_b', 'name_b_a'];
  67
  68   /**
  69    * Add our list of triggers to the global list.
  70    *
  71    * @param \Civi\Core\Event\GenericHookEvent $e
  72    * @see \CRM_Utils_Hook::triggerInfo
  73    */
  74   public static function on_hook_civicrm_triggerInfo($e): void {
  75     $relUpdates = self::createInsertUpdateQueries();
  76     // Use utf8mb4_bin or utf8_bin, depending on what's in use.
  77     $collation = preg_replace('/^(utf8(?:mb4)?)_.*$/', '$1_bin', CRM_Core_BAO_SchemaHandler::getInUseCollation());
  78
  79     foreach ($relUpdates as $relUpdate) {
  80       /**
  81        * This trigger runs whenever a "civicrm_relationship" record is inserted or updated.
  82        *
  83        * Goal: Ensure that every relationship record has two corresponding entries in the
  84        * cache, the forward relationship (A=>B) and reverse relationship (B=>A).
  85        */
  86       $triggers[] = [
  87         'table' => 'civicrm_relationship',
  88         'when' => 'AFTER',
  89         'event' => ['INSERT', 'UPDATE'],
  90         'sql' => $relUpdate->copy()->where('rel.id = NEW.id')->toSQL() . ";\n",
  91       ];
  92
  93       $triggers[] = [
  94         /**
  95          * This trigger runs whenever a "civicrm_relationship_type" record is updated.
  96          *
  97          * Goal: Ensure that the denormalized fields ("name_b_a"/"name_a_b" <=> "relation") remain current.
  98          */
  99         'table' => 'civicrm_relationship_type',
 100         'when' => 'AFTER',
 101         'event' => ['UPDATE'],
 102         'sql' => sprintf("\nIF (%s) THEN\n %s;\n END IF;\n",
 103
 104           // Condition
 105           implode(' OR ', array_map(function ($col) use ($collation) {
 106             return "(OLD.$col != NEW.$col COLLATE $collation)";
 107           }, self::$relTypeWatchFields)),
 108
 109           // Action
 110           $relUpdate->copy()->where('rel.relationship_type_id = NEW.id')->toSQL()
 111         ),
 112       ];
 113     }
 114
 115     // Note: We do not need a DELETE trigger to maintain `civicrm_relationship_cache` because it uses `<onDelete>CASCADE</onDelete>`.
 116
 117     $st = new \Civi\Core\SqlTrigger\StaticTriggers($triggers);
 118     $st->onTriggerInfo($e);
 119   }
 120
 121   /**
 122    * Read all records from civicrm_relationship and populate the cache.
 123    * Each ordinary relationship in `civicrm_relationship` becomes two
 124    * distinct records in the cache (one for A=>B relations; and one for B=>A).
 125    *
 126    * This method is primarily written (a) for manual testing and (b) in case
 127    * a broken DBMS, screwy import, buggy code, etc causes a corruption.
 128    *
 129    * NOTE: This is closely related to FiveTwentyNine::populateRelationshipCache(),
 130    * except that the upgrader users pagination.
 131    */
 132   public static function rebuild() {
 133     $relUpdates = self::createInsertUpdateQueries();
 134
 135     CRM_Core_DAO::executeQuery('TRUNCATE civicrm_relationship_cache');
 136     foreach ($relUpdates as $relUpdate) {
 137       $relUpdate->execute();
 138     }
 139   }
 140
 141   /**
 142    * Prepare a list of SQL queries that map data from civicrm_relationship
 143    * to civicrm_relationship_cache.
 144    *
 145    * @return CRM_Utils_SQL_Select[]
 146    *   A list of SQL queries - one for each mapping.
 147    */
 148   public static function createInsertUpdateQueries() {
 149     $queries = [];
 150     foreach (self::$mappings as $name => $mapping) {
 151       $queries[$name] = CRM_Utils_SQL_Select::from('civicrm_relationship rel')
 152         ->join('reltype', 'INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id')
 153         ->syncInto('civicrm_relationship_cache', self::$keyFields, $mapping);
 154     }
 155     return $queries;
 156   }
 157
 158   /**
 159    * @return array
 160    */
 161   public function addSelectWhereClause() {
 162     // Permission for this entity depends on access to the two related contacts.
 163     $contactClause = CRM_Utils_SQL::mergeSubquery('Contact');
 164     $clauses = [
 165       'near_contact_id' => $contactClause,
 166       'far_contact_id' => $contactClause,
 167     ];
 168     CRM_Utils_Hook::selectWhereClause($this, $clauses);
 169     return $clauses;
 170   }
 171
 172 }