From 6050e9ad11556cf2f67cbe5b0faaee374633c2d8 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Wed, 26 Jul 2017 18:08:20 -0700 Subject: [PATCH] CRM-20958 - Extract helper Civi\Core\SqlTrigger\TimestampTriggers == Before == * SQL triggers to populate `civicrm_contact.created_date` and `civicrm_contact.modified_date` are generate via `CRM_Contact_BAO_Contact::triggerInfo($info, $tableName)` == After == * `CRM_Contact_BAO_Contact::triggerInfo` calls a helper `TimestampTriggers` * The helper `TimestampTriggers` accepts arguments describing the names of the tables/columns which needed for the timestamp triggers. == Comments == To test, I used this command to update and dump the schema: ``` cv api system.flush triggers=1 && mysqldump --triggers ... ``` The schema was identical before and after. (Notably, by alternately hacking the code, I was able to validate the test was capable of revealing discrepencies.) --- CRM/Contact/BAO/Contact.php | 87 +----- Civi/Core/SqlTrigger/TimestampTriggers.php | 337 +++++++++++++++++++++ 2 files changed, 348 insertions(+), 76 deletions(-) create mode 100644 Civi/Core/SqlTrigger/TimestampTriggers.php diff --git a/CRM/Contact/BAO/Contact.php b/CRM/Contact/BAO/Contact.php index df8eaf5929..9c829b29c1 100644 --- a/CRM/Contact/BAO/Contact.php +++ b/CRM/Contact/BAO/Contact.php @@ -3277,51 +3277,6 @@ LEFT JOIN civicrm_address add2 ON ( add1.master_id = add2.id ) } } - /** - * Generate triggers to update the timestamp. - * - * The corresponding civicrm_contact row is updated on insert/update/delete - * to a table that extends civicrm_contact. - * Don't regenerate triggers for all such tables if only asked for one table. - * - * @param array $info - * Reference to the array where generated trigger information is being stored - * @param string|null $reqTableName - * Name of the table for which triggers are being generated, or NULL if all tables - * @param array $relatedTableNames - * Array of all core or all custom table names extending civicrm_contact - * @param string $contactRefColumn - * 'contact_id' if processing core tables, 'entity_id' if processing custom tables - * - * @link https://issues.civicrm.org/jira/browse/CRM-15602 - * @see triggerInfo - */ - public static function generateTimestampTriggers(&$info, $reqTableName, $relatedTableNames, $contactRefColumn) { - // Safety - $contactRefColumn = CRM_Core_DAO::escapeString($contactRefColumn); - // If specific related table requested, just process that one - if (in_array($reqTableName, $relatedTableNames)) { - $relatedTableNames = array($reqTableName); - } - - // If no specific table requested (include all related tables), - // or a specific related table requested (as matched above) - if (empty($reqTableName) || in_array($reqTableName, $relatedTableNames)) { - $info[] = array( - 'table' => $relatedTableNames, - 'when' => 'AFTER', - 'event' => array('INSERT', 'UPDATE'), - 'sql' => "\nUPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = NEW.$contactRefColumn;\n", - ); - $info[] = array( - 'table' => $relatedTableNames, - 'when' => 'AFTER', - 'event' => array('DELETE'), - 'sql' => "\nUPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = OLD.$contactRefColumn;\n", - ); - } - } - /** * Get a list of triggers for the contact table. * @@ -3343,37 +3298,17 @@ LEFT JOIN civicrm_address add2 ON ( add1.master_id = add2.id ) } } - // Update timestamp for civicrm_contact itself - if ($tableName == NULL || $tableName == self::getTableName()) { - $info[] = array( - 'table' => array(self::getTableName()), - 'when' => 'BEFORE', - 'event' => array('INSERT'), - 'sql' => "\nSET NEW.created_date = CURRENT_TIMESTAMP;\n", - ); - } - - // Update timestamp when modifying closely related core tables - $relatedTables = array( - 'civicrm_address', - 'civicrm_email', - 'civicrm_im', - 'civicrm_phone', - 'civicrm_website', - ); - self::generateTimestampTriggers($info, $tableName, $relatedTables, 'contact_id'); - - // Update timestamp when modifying related custom-data tables - $customGroupTables = array(); - $customGroupDAO = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity('Contact'); - $customGroupDAO->is_multiple = 0; - $customGroupDAO->find(); - while ($customGroupDAO->fetch()) { - $customGroupTables[] = $customGroupDAO->table_name; - } - if (!empty($customGroupTables)) { - self::generateTimestampTriggers($info, $tableName, $customGroupTables, 'entity_id'); - } + // Modifications to these records should update the contact timestamps. + \Civi\Core\SqlTrigger\TimestampTriggers::create('civicrm_contact', 'Contact') + ->setRelations(array( + array('table' => 'civicrm_address', 'column' => 'contact_id'), + array('table' => 'civicrm_email', 'column' => 'contact_id'), + array('table' => 'civicrm_im', 'column' => 'contact_id'), + array('table' => 'civicrm_phone', 'column' => 'contact_id'), + array('table' => 'civicrm_website', 'column' => 'contact_id'), + ) + ) + ->alterTriggerInfo($info, $tableName); // Update phone table to populate phone_numeric field if (!$tableName || $tableName == 'civicrm_phone') { diff --git a/Civi/Core/SqlTrigger/TimestampTriggers.php b/Civi/Core/SqlTrigger/TimestampTriggers.php new file mode 100644 index 0000000000..e204332b77 --- /dev/null +++ b/Civi/Core/SqlTrigger/TimestampTriggers.php @@ -0,0 +1,337 @@ + 'civicrm_bar', 'column' => 'foo_id'); + */ + private $relations; + + /** + * @param string $tableName + * SQL table name. + * Ex: 'civicrm_contact', 'civicrm_activity'. + * @param string $customDataEntity + * An entity name (from civicrm_custom_group.extends). + * Ex: 'Contact', 'Activity'. + * @return TimestampTriggers + */ + public static function create($tableName, $customDataEntity) { + return new static($tableName, $customDataEntity); + } + + /** + * TimestampTriggers constructor. + * + * @param string $tableName + * SQL table name. + * Ex: 'civicrm_contact', 'civicrm_activity'. + * @param string $customDataEntity + * An entity name (from civicrm_custom_group.extends). + * Ex: 'Contact', 'Activity'. + * @param string $createdDate + * SQL column name. + * Ex: 'created_date'. + * @param string $modifiedDate + * SQL column name. + * Ex: 'modified_date'. + * @param array $relations + * Ex: $relations[0] == array('table' => 'civicrm_bar', 'column' => 'foo_id'); + */ + public function __construct( + $tableName, + $customDataEntity, + $createdDate = 'created_date', + $modifiedDate = 'modified_date', + $relations = array() + ) { + $this->tableName = $tableName; + $this->customDataEntity = $customDataEntity; + $this->createdDate = $createdDate; + $this->modifiedDate = $modifiedDate; + $this->relations = $relations; + } + + /** + * Add our list of triggers to the global list. + * + * @param \Civi\Core\Event\GenericHookEvent $e + * @see \CRM_Utils_Hook::triggerInfo + */ + public function onTriggerInfo($e) { + $this->alterTriggerInfo($e->info, $e->tableName); + } + + /** + * Add our list of triggers to the global list. + * + * @see \CRM_Utils_Hook::triggerInfo + * @see \CRM_Core_DAO::triggerRebuild + * + * @param array $info + * See hook_civicrm_triggerInfo. + * @param string|NULL $tableFilter + * See hook_civicrm_triggerInfo. + */ + public function alterTriggerInfo(&$info, $tableFilter = NULL) { + //during upgrade, first check for valid version and then create triggers + //i.e the columns created_date and modified_date are introduced in 4.3.alpha1 so dont create triggers for older version + if (\CRM_Core_Config::isUpgradeMode()) { + if (!\CRM_Core_DAO::checkFieldExists($this->getTableName(), + $this->getCreatedDate()) + ) { + return; + } + // $currentVer = CRM_Core_BAO_Domain::version(TRUE); + // if (version_compare($currentVer, '4.3.alpha1') < 0) { + // return; + // } + } + + if ($tableFilter == NULL || $tableFilter == $this->getTableName()) { + $info[] = array( + 'table' => array($this->getTableName()), + 'when' => 'BEFORE', + 'event' => array('INSERT'), + 'sql' => "\nSET NEW.{$this->getCreatedDate()} = CURRENT_TIMESTAMP;\n", + ); + } + + // Update timestamp when modifying closely related tables + $relIdx = \CRM_Utils_Array::index( + array('column', 'table'), + $this->getAllRelations() + ); + foreach ($relIdx as $column => $someRelations) { + $this->generateTimestampTriggers($info, $tableFilter, + array_keys($someRelations), $column); + } + } + + /** + * Generate triggers to update the timestamp. + * + * The corresponding civicrm_FOO row is updated on insert/update/delete + * to a table that extends civicrm_FOO. + * Don't regenerate triggers for all such tables if only asked for one table. + * + * @param array $info + * Reference to the array where generated trigger information is being stored + * @param string|null $tableFilter + * Name of the table for which triggers are being generated, or NULL if all tables + * @param array $relatedTableNames + * Array of all core or all custom table names extending civicrm_FOO + * @param string $contactRefColumn + * 'contact_id' if processing core tables, 'entity_id' if processing custom tables + * + * @link https://issues.civicrm.org/jira/browse/CRM-15602 + * @see triggerInfo + */ + public function generateTimestampTriggers( + &$info, + $tableFilter, + $relatedTableNames, + $contactRefColumn + ) { + // Safety + $contactRefColumn = \CRM_Core_DAO::escapeString($contactRefColumn); + + // If specific related table requested, just process that one. + // (Reply: This feels fishy.) + if (in_array($tableFilter, $relatedTableNames)) { + $relatedTableNames = array($tableFilter); + } + + // If no specific table requested (include all related tables), + // or a specific related table requested (as matched above) + if (empty($tableFilter) || isset($relatedTableNames[$tableFilter])) { + $info[] = array( + 'table' => $relatedTableNames, + 'when' => 'AFTER', + 'event' => array('INSERT', 'UPDATE'), + 'sql' => "\nUPDATE {$this->getTableName()} SET {$this->getModifiedDate()} = CURRENT_TIMESTAMP WHERE id = NEW.$contactRefColumn;\n", + ); + $info[] = array( + 'table' => $relatedTableNames, + 'when' => 'AFTER', + 'event' => array('DELETE'), + 'sql' => "\nUPDATE {$this->getTableName()} SET {$this->getModifiedDate()} = CURRENT_TIMESTAMP WHERE id = OLD.$contactRefColumn;\n", + ); + } + } + + /** + * @return string + */ + public function getTableName() { + return $this->tableName; + } + + /** + * @param string $tableName + * @return TimestampTriggers + */ + public function setTableName($tableName) { + $this->tableName = $tableName; + return $this; + } + + /** + * @return string + */ + public function getCustomDataEntity() { + return $this->customDataEntity; + } + + /** + * @param string $customDataEntity + * @return TimestampTriggers + */ + public function setCustomDataEntity($customDataEntity) { + $this->customDataEntity = $customDataEntity; + return $this; + } + + /** + * @return string + */ + public function getCreatedDate() { + return $this->createdDate; + } + + /** + * @param string $createdDate + * @return TimestampTriggers + */ + public function setCreatedDate($createdDate) { + $this->createdDate = $createdDate; + return $this; + } + + /** + * @return string + */ + public function getModifiedDate() { + return $this->modifiedDate; + } + + /** + * @param string $modifiedDate + * @return TimestampTriggers + */ + public function setModifiedDate($modifiedDate) { + $this->modifiedDate = $modifiedDate; + return $this; + } + + /** + * @return array + * Each item is an array('table' => string, 'column' => string) + */ + public function getRelations() { + return $this->relations; + } + + /** + * @param array $relations + * @return TimestampTriggers + */ + public function setRelations($relations) { + $this->relations = $relations; + return $this; + } + + /** + * Get a list of all tracked relations. + * + * This is basically the curated list (`$this->relations`) plus any custom data. + * + * @return array + * Each item is an array('table' => string, 'column' => string) + */ + public function getAllRelations() { + $relations = $this->getRelations(); + + if ($this->getCustomDataEntity()) { + $customGroupDAO = \CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity($this->getCustomDataEntity()); + $customGroupDAO->is_multiple = 0; + $customGroupDAO->find(); + while ($customGroupDAO->fetch()) { + $relations[] = array( + 'table' => $customGroupDAO->table_name, + 'column' => 'entity_id', + ); + } + } + + return $relations; + } + +} -- 2.25.1