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 {
  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     ],
  37     'b_a' => [
  38       'relationship_id' => 'rel.id',
  39       'relationship_type_id' => 'rel.relationship_type_id',
  40       'orientation' => '"b_a"',
  41       'near_contact_id' => 'rel.contact_id_b',
  42       'near_relation' => 'reltype.name_b_a',
  43       'far_contact_id' => 'rel.contact_id_a',
  44       'far_relation' => 'reltype.name_a_b',
  45       'start_date' => 'rel.start_date',
  46       'end_date' => 'rel.end_date',
  47       'is_active' => 'rel.is_active',
  48     ],
  49   ];
  50
  51   /**
  52    * A list of fields which uniquely identify a row.
  53    *
  54    * @var array
  55    */
  56   private static $keyFields = ['relationship_id', 'orientation'];
  57
  58   /**
  59    * A list of of fields in `civicrm_relationship_type` which (if changed)
  60    * will necessitate an update to the cache.
  61    *
  62    * @var array
  63    */
  64   private static $relTypeWatchFields = ['name_a_b', 'name_b_a'];
  65
  66   /**
  67    * Add our list of triggers to the global list.
  68    *
  69    * @param \Civi\Core\Event\GenericHookEvent $e
  70    * @see \CRM_Utils_Hook::triggerInfo
  71    */
  72   public static function onHookTriggerInfo($e) {
  73     $relUpdates = self::createInsertUpdateQueries();
  74     // Use utf8mb4_bin or utf8_bin, depending on what's in use.
  75     $collation = preg_replace('/^(utf8(?:mb4)?)_.*$/', '$1_bin', CRM_Core_BAO_SchemaHandler::getInUseCollation());
  76
  77     foreach ($relUpdates as $relUpdate) {
  78       /**
  79        * This trigger runs whenever a "civicrm_relationship" record is inserted or updated.
  80        *
  81        * Goal: Ensure that every relationship record has two corresponding entries in the
  82        * cache, the forward relationship (A=>B) and reverse relationship (B=>A).
  83        */
  84       $triggers[] = [
  85         'table' => 'civicrm_relationship',
  86         'when' => 'AFTER',
  87         'event' => ['INSERT', 'UPDATE'],
  88         'sql' => $relUpdate->copy()->where('rel.id = NEW.id')->toSQL() . ";\n",
  89       ];
  90
  91       $triggers[] = [
  92         /**
  93          * This trigger runs whenever a "civicrm_relationship_type" record is updated.
  94          *
  95          * Goal: Ensure that the denormalized fields ("name_b_a"/"name_a_b" <=> "relation") remain current.
  96          */
  97         'table' => 'civicrm_relationship_type',
  98         'when' => 'AFTER',
  99         'event' => ['UPDATE'],
 100         'sql' => sprintf("\nIF (%s) THEN\n %s;\n END IF;\n",
 101
 102           // Condition
 103           implode(' OR ', array_map(function ($col) use ($collation) {
 104             return "(OLD.$col != NEW.$col COLLATE $collation)";
 105           }, self::$relTypeWatchFields)),
 106
 107           // Action
 108           $relUpdate->copy()->where('rel.relationship_type_id = NEW.id')->toSQL()
 109         ),
 110       ];
 111     }
 112
 113     // Note: We do not need a DELETE trigger to maintain `civicrm_relationship_cache` because it uses `<onDelete>CASCADE</onDelete>`.
 114
 115     $st = new \Civi\Core\SqlTrigger\StaticTriggers($triggers);
 116     $st->onTriggerInfo($e);
 117   }
 118
 119   /**
 120    * Read all records from civicrm_relationship and populate the cache.
 121    * Each ordinary relationship in `civicrm_relationship` becomes two
 122    * distinct records in the cache (one for A=>B relations; and one for B=>A).
 123    *
 124    * This method is primarily written (a) for manual testing and (b) in case
 125    * a broken DBMS, screwy import, buggy code, etc causes a corruption.
 126    *
 127    * NOTE: This is closely related to FiveTwentyNine::populateRelationshipCache(),
 128    * except that the upgrader users pagination.
 129    */
 130   public static function rebuild() {
 131     $relUpdates = self::createInsertUpdateQueries();
 132
 133     CRM_Core_DAO::executeQuery('TRUNCATE civicrm_relationship_cache');
 134     foreach ($relUpdates as $relUpdate) {
 135       $relUpdate->execute();
 136     }
 137   }
 138
 139   /**
 140    * Prepare a list of SQL queries that map data from civicrm_relationship
 141    * to civicrm_relationship_cache.
 142    *
 143    * @return CRM_Utils_SQL_Select[]
 144    *   A list of SQL queries - one for each mapping.
 145    */
 146   public static function createInsertUpdateQueries() {
 147     $queries = [];
 148     foreach (self::$mappings as $name => $mapping) {
 149       $queries[$name] = CRM_Utils_SQL_Select::from('civicrm_relationship rel')
 150         ->join('reltype', 'INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id')
 151         ->syncInto('civicrm_relationship_cache', self::$keyFields, $mapping);
 152     }
 153     return $queries;
 154   }
 155
 156 }