Civi/Core/SqlTrigger/TimestampTriggers.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | Copyright CiviCRM LLC. All rights reserved.                        |
   6  |                                                                    |
   7  | This work is published under the GNU AGPLv3 license with some      |
   8  | permitted exceptions and without any warranty. For full license    |
   9  | and copyright information, see https://civicrm.org/licensing       |
  10  +--------------------------------------------------------------------+
  11  */
  12
  13 namespace Civi\Core\SqlTrigger;
  14
  15 /**
  16  * Build a set of SQL triggers for tracking timestamps on an entity.
  17  *
  18  * This class is a generalization of CRM-10554 with the aim of enabling CRM-20958.
  19  *
  20  * @package CRM
  21  * @copyright CiviCRM LLC https://civicrm.org/licensing
  22  */
  23 class TimestampTriggers {
  24
  25   /**
  26    * @var string
  27    *   SQL table name.
  28    *   Ex: 'civicrm_contact', 'civicrm_activity'.
  29    */
  30   private $tableName;
  31
  32   /**
  33    * @var string
  34    *   An entity name (from civicrm_custom_group.extends).
  35    *   Ex: 'Contact', 'Activity'.
  36    */
  37   private $customDataEntity;
  38
  39   /**
  40    * @var string
  41    *   SQL column name.
  42    *   Ex: 'created_date'.
  43    */
  44   private $createdDate;
  45
  46   /**
  47    * @var string
  48    *   SQL column name.
  49    *   Ex: 'modified_date'.
  50    */
  51   private $modifiedDate;
  52
  53   /**
  54    * @var array
  55    *   Ex: $relations[0] == array('table' => 'civicrm_bar', 'column' => 'foo_id');
  56    */
  57   private $relations;
  58
  59   /**
  60    * @param string $tableName
  61    *   SQL table name.
  62    *   Ex: 'civicrm_contact', 'civicrm_activity'.
  63    * @param string $customDataEntity
  64    *   An entity name (from civicrm_custom_group.extends).
  65    *   Ex: 'Contact', 'Activity'.
  66    * @return TimestampTriggers
  67    */
  68   public static function create($tableName, $customDataEntity) {
  69     return new static($tableName, $customDataEntity);
  70   }
  71
  72   /**
  73    * TimestampTriggers constructor.
  74    *
  75    * @param string $tableName
  76    *   SQL table name.
  77    *   Ex: 'civicrm_contact', 'civicrm_activity'.
  78    * @param string $customDataEntity
  79    *   An entity name (from civicrm_custom_group.extends).
  80    *   Ex: 'Contact', 'Activity'.
  81    * @param string $createdDate
  82    *   SQL column name.
  83    *   Ex: 'created_date'.
  84    * @param string $modifiedDate
  85    *   SQL column name.
  86    *   Ex: 'modified_date'.
  87    * @param array $relations
  88    *   Ex: $relations[0] == array('table' => 'civicrm_bar', 'column' => 'foo_id');
  89    */
  90   public function __construct(
  91     $tableName,
  92     $customDataEntity,
  93     $createdDate = 'created_date',
  94     $modifiedDate = 'modified_date',
  95     $relations = []
  96   ) {
  97     $this->tableName = $tableName;
  98     $this->customDataEntity = $customDataEntity;
  99     $this->createdDate = $createdDate;
 100     $this->modifiedDate = $modifiedDate;
 101     $this->relations = $relations;
 102   }
 103
 104   /**
 105    * Add our list of triggers to the global list.
 106    *
 107    * @param \Civi\Core\Event\GenericHookEvent $e
 108    * @see \CRM_Utils_Hook::triggerInfo
 109    */
 110   public function onTriggerInfo($e) {
 111     $this->alterTriggerInfo($e->info, $e->tableName);
 112   }
 113
 114   /**
 115    * Add our list of triggers to the global list.
 116    *
 117    * @see \CRM_Utils_Hook::triggerInfo
 118    * @see \CRM_Core_DAO::triggerRebuild
 119    *
 120    * @param array $info
 121    *   See hook_civicrm_triggerInfo.
 122    * @param string|NULL $tableFilter
 123    *   See hook_civicrm_triggerInfo.
 124    */
 125   public function alterTriggerInfo(&$info, $tableFilter = NULL) {
 126     // If we haven't upgraded yet, then the created_date/modified_date may not exist.
 127     // In the past, this was a version-based check, but checkFieldExists()
 128     // seems more robust.
 129     if (\CRM_Core_Config::isUpgradeMode()) {
 130       if (!\CRM_Core_BAO_SchemaHandler::checkIfFieldExists($this->getTableName(),
 131         $this->getCreatedDate())
 132       ) {
 133         return;
 134       }
 135     }
 136
 137     if ($tableFilter == NULL || $tableFilter == $this->getTableName()) {
 138       $info[] = [
 139         'table' => [$this->getTableName()],
 140         'when' => 'BEFORE',
 141         'event' => ['INSERT'],
 142         'sql' => "\nSET NEW.{$this->getCreatedDate()} = CURRENT_TIMESTAMP;\n",
 143       ];
 144     }
 145
 146     // Update timestamp when modifying closely related tables
 147     $relIdx = \CRM_Utils_Array::index(
 148       ['column', 'table'],
 149       $this->getAllRelations()
 150     );
 151     foreach ($relIdx as $column => $someRelations) {
 152       $this->generateTimestampTriggers($info, $tableFilter,
 153         array_keys($someRelations), $column);
 154     }
 155   }
 156
 157   /**
 158    * Generate triggers to update the timestamp.
 159    *
 160    * The corresponding civicrm_FOO row is updated on insert/update/delete
 161    * to a table that extends civicrm_FOO.
 162    * Don't regenerate triggers for all such tables if only asked for one table.
 163    *
 164    * @param array $info
 165    *   Reference to the array where generated trigger information is being stored
 166    * @param string|null $tableFilter
 167    *   Name of the table for which triggers are being generated, or NULL if all tables
 168    * @param array $relatedTableNames
 169    *   Array of all core or all custom table names extending civicrm_FOO
 170    * @param string $contactRefColumn
 171    *   'contact_id' if processing core tables, 'entity_id' if processing custom tables
 172    *
 173    * @link https://issues.civicrm.org/jira/browse/CRM-15602
 174    * @see triggerInfo
 175    */
 176   public function generateTimestampTriggers(
 177     &$info,
 178     $tableFilter,
 179     $relatedTableNames,
 180     $contactRefColumn
 181   ) {
 182     // Safety
 183     $contactRefColumn = \CRM_Core_DAO::escapeString($contactRefColumn);
 184
 185     // If specific related table requested, just process that one.
 186     // (Reply: This feels fishy.)
 187     if (in_array($tableFilter, $relatedTableNames)) {
 188       $relatedTableNames = [$tableFilter];
 189     }
 190
 191     // If no specific table requested (include all related tables),
 192     // or a specific related table requested (as matched above)
 193     if (empty($tableFilter) || isset($relatedTableNames[$tableFilter])) {
 194       $info[] = [
 195         'table' => $relatedTableNames,
 196         'when' => 'AFTER',
 197         'event' => ['INSERT', 'UPDATE'],
 198         'sql' => "\nUPDATE {$this->getTableName()} SET {$this->getModifiedDate()} = CURRENT_TIMESTAMP WHERE id = NEW.$contactRefColumn;\n",
 199       ];
 200       $info[] = [
 201         'table' => $relatedTableNames,
 202         'when' => 'AFTER',
 203         'event' => ['DELETE'],
 204         'sql' => "\nUPDATE {$this->getTableName()} SET {$this->getModifiedDate()} = CURRENT_TIMESTAMP WHERE id = OLD.$contactRefColumn;\n",
 205       ];
 206     }
 207   }
 208
 209   /**
 210    * @return string
 211    */
 212   public function getTableName() {
 213     return $this->tableName;
 214   }
 215
 216   /**
 217    * @param string $tableName
 218    * @return TimestampTriggers
 219    */
 220   public function setTableName($tableName) {
 221     $this->tableName = $tableName;
 222     return $this;
 223   }
 224
 225   /**
 226    * @return string
 227    */
 228   public function getCustomDataEntity() {
 229     return $this->customDataEntity;
 230   }
 231
 232   /**
 233    * @param string $customDataEntity
 234    * @return TimestampTriggers
 235    */
 236   public function setCustomDataEntity($customDataEntity) {
 237     $this->customDataEntity = $customDataEntity;
 238     return $this;
 239   }
 240
 241   /**
 242    * @return string
 243    */
 244   public function getCreatedDate() {
 245     return $this->createdDate;
 246   }
 247
 248   /**
 249    * @param string $createdDate
 250    * @return TimestampTriggers
 251    */
 252   public function setCreatedDate($createdDate) {
 253     $this->createdDate = $createdDate;
 254     return $this;
 255   }
 256
 257   /**
 258    * @return string
 259    */
 260   public function getModifiedDate() {
 261     return $this->modifiedDate;
 262   }
 263
 264   /**
 265    * @param string $modifiedDate
 266    * @return TimestampTriggers
 267    */
 268   public function setModifiedDate($modifiedDate) {
 269     $this->modifiedDate = $modifiedDate;
 270     return $this;
 271   }
 272
 273   /**
 274    * @return array
 275    *   Each item is an array('table' => string, 'column' => string)
 276    */
 277   public function getRelations() {
 278     return $this->relations;
 279   }
 280
 281   /**
 282    * @param array $relations
 283    * @return TimestampTriggers
 284    */
 285   public function setRelations($relations) {
 286     $this->relations = $relations;
 287     return $this;
 288   }
 289
 290   /**
 291    * Get a list of all tracked relations.
 292    *
 293    * This is basically the curated list (`$this->relations`) plus any custom data.
 294    *
 295    * @return array
 296    *   Each item is an array('table' => string, 'column' => string)
 297    */
 298   public function getAllRelations() {
 299     $relations = $this->getRelations();
 300
 301     if ($this->getCustomDataEntity()) {
 302       $customGroupDAO = \CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity($this->getCustomDataEntity());
 303       $customGroupDAO->is_multiple = 0;
 304       $customGroupDAO->find();
 305       while ($customGroupDAO->fetch()) {
 306         $relations[] = [
 307           'table' => $customGroupDAO->table_name,
 308           'column' => 'entity_id',
 309         ];
 310       }
 311     }
 312
 313     return $relations;
 314   }
 315
 316 }