Civi/Core/SqlTrigger/TimestampTriggers.php

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