From 8416d9e2bf984ea0d08d8bddc75dcb806ad730d0 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Tue, 30 Jun 2020 23:43:06 -0700 Subject: [PATCH] RelationshipVortex - Define MySQL trigger to keep data sync'd. Add test. Use syncInto(). --- CRM/Contact/BAO/RelationshipVortex.php | 155 ++++++++++++++++++ Civi/Core/Container.php | 1 + .../Contact/BAO/RelationshipVortexTest.php | 72 ++++++++ 3 files changed, 228 insertions(+) create mode 100644 CRM/Contact/BAO/RelationshipVortex.php create mode 100644 tests/phpunit/CRM/Contact/BAO/RelationshipVortexTest.php diff --git a/CRM/Contact/BAO/RelationshipVortex.php b/CRM/Contact/BAO/RelationshipVortex.php new file mode 100644 index 0000000000..fe6f889c24 --- /dev/null +++ b/CRM/Contact/BAO/RelationshipVortex.php @@ -0,0 +1,155 @@ + string $selectValue) + */ + private static $mappings = [ + 'a_b' => [ + 'relationship_id' => 'rel.id', + 'relationship_type_id' => 'rel.relationship_type_id', + 'orientation' => '"a_b"', + 'near_contact_id' => 'rel.contact_id_a', + 'near_relation' => 'reltype.name_a_b', + 'far_contact_id' => 'rel.contact_id_b', + 'far_relation' => 'reltype.name_b_a', + 'start_date' => 'rel.start_date', + 'end_date' => 'rel.end_date', + 'is_active' => 'rel.is_active', + 'case_id' => 'rel.case_id', + ], + 'b_a' => [ + 'relationship_id' => 'rel.id', + 'relationship_type_id' => 'rel.relationship_type_id', + 'orientation' => '"b_a"', + 'near_contact_id' => 'rel.contact_id_b', + 'near_relation' => 'reltype.name_b_a', + 'far_contact_id' => 'rel.contact_id_a', + 'far_relation' => 'reltype.name_a_b', + 'start_date' => 'rel.start_date', + 'end_date' => 'rel.end_date', + 'is_active' => 'rel.is_active', + 'case_id' => 'rel.case_id', + ], + ]; + + /** + * A list of fields which uniquely identify a row. + * + * @var array + */ + private static $keyFields = ['relationship_id', 'orientation']; + + /** + * A list of of fields in `civicrm_relationship_type` which (if changed) + * will necessitate an update to the vortex. + * + * @var array + */ + private static $relTypeWatchFields = ['name_a_b', 'name_b_a']; + + /** + * Add our list of triggers to the global list. + * + * @param \Civi\Core\Event\GenericHookEvent $e + * @see \CRM_Utils_Hook::triggerInfo + */ + public static function onHookTriggerInfo($e) { + $relUpdates = self::createInsertUpdateQueries(); + foreach ($relUpdates as $relUpdate) { + /** + * This trigger runs whenever a "civicrm_relationship" record is inserted or updated. + * + * Goal: Ensure that every relationship record has two corresponding entries in the + * vortex, the forward relationship (A=>B) and reverse relationship (B=>A). + */ + $triggers[] = [ + 'table' => 'civicrm_relationship', + 'when' => 'AFTER', + 'event' => ['INSERT', 'UPDATE'], + 'sql' => $relUpdate->copy()->where('rel.id = NEW.id')->toSQL() . ";\n", + ]; + + $triggers[] = [ + /** + * This trigger runs whenever a "civicrm_relationship_type" record is updated. + * + * Goal: Ensure that the denormalized fields ("name_b_a"/"name_a_b" <=> "relation") remain current. + */ + 'table' => 'civicrm_relationship_type', + 'when' => 'AFTER', + 'event' => ['UPDATE'], + 'sql' => sprintf("\nIF (%s) THEN\n %s;\n END IF;\n", + + // Condition + implode(' OR ', array_map(function ($col) { + return "(OLD.$col != NEW.$col COLLATE utf8_bin)"; + }, self::$relTypeWatchFields)), + + // Action + $relUpdate->copy()->where('rel.relationship_type_id = NEW.id')->toSQL() + ), + ]; + } + + // Note: We do not need a DELETE trigger to maintain `civicrm_relationship_vtx` because it uses `CASCADE`. + + $st = new \Civi\Core\SqlTrigger\StaticTriggers($triggers); + $st->onTriggerInfo($e); + } + + /** + * Read all records from civicrm_relationship and populate the vortex. + * Each ordinary relationship in `civicrm_relationship` becomes two + * distinct records in the vortex (one for A=>B relations; and one for B=>A). + * + * This method is primarily written (a) for manual testing and (b) in case + * a broken DBMS, screwy import, buggy code, etc causes a corruption. + * + * NOTE: This is closely related to FiveTwentyNine::populateRelationshipVortex(), + * except that the upgrader users pagination. + */ + public static function rebuild() { + $relUpdates = self::createInsertUpdateQueries(); + + CRM_Core_DAO::executeQuery('TRUNCATE civicrm_relationship_vtx'); + foreach ($relUpdates as $relUpdate) { + $relUpdate->execute(); + } + } + + /** + * Prepare a list of SQL queries that map data from civicrm_relationship + * to civicrm_relationship_vtx. + * + * @return CRM_Utils_SQL_Select[] + * A list of SQL queries - one for each mapping. + */ + public static function createInsertUpdateQueries() { + $queries = []; + foreach (self::$mappings as $name => $mapping) { + $queries[$name] = CRM_Utils_SQL_Select::from('civicrm_relationship rel') + ->join('reltype', 'INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id') + ->syncInto('civicrm_relationship_vtx', self::$keyFields, $mapping); + } + return $queries; + } + +} diff --git a/Civi/Core/Container.php b/Civi/Core/Container.php index 15c7860a9d..624e8659fa 100644 --- a/Civi/Core/Container.php +++ b/Civi/Core/Container.php @@ -355,6 +355,7 @@ class Container { $dispatcher->addListener('hook_civicrm_coreResourceList', ['\CRM_Utils_System', 'appendCoreResources']); $dispatcher->addListener('hook_civicrm_getAssetUrl', ['\CRM_Utils_System', 'alterAssetUrl']); $dispatcher->addListener('hook_civicrm_alterExternUrl', ['\CRM_Utils_System', 'migrateExternUrl'], 1000); + $dispatcher->addListener('hook_civicrm_triggerInfo', ['\CRM_Contact_BAO_RelationshipVortex', 'onHookTriggerInfo']); $dispatcher->addListener('civi.dao.postInsert', ['\CRM_Core_BAO_RecurringEntity', 'triggerInsert']); $dispatcher->addListener('civi.dao.postUpdate', ['\CRM_Core_BAO_RecurringEntity', 'triggerUpdate']); $dispatcher->addListener('civi.dao.postDelete', ['\CRM_Core_BAO_RecurringEntity', 'triggerDelete']); diff --git a/tests/phpunit/CRM/Contact/BAO/RelationshipVortexTest.php b/tests/phpunit/CRM/Contact/BAO/RelationshipVortexTest.php new file mode 100644 index 0000000000..e3c654c38f --- /dev/null +++ b/tests/phpunit/CRM/Contact/BAO/RelationshipVortexTest.php @@ -0,0 +1,72 @@ +useTransaction(TRUE); + parent::setUp(); + } + + /** + * Whenever one `Relationship` is created, there should be two corresponding + * `RelationshipVortex` records. + */ + public function testRelationshipVortex() { + // add a new type + $relationship_type_id_1 = $this->relationshipTypeCreate([ + 'name_a_b' => 'Praegustator is', + 'name_b_a' => 'Praegustator for', + 'contact_type_a' => 'Individual', + 'contact_type_b' => 'Individual', + ]); + + // add some people + $contact_id_1 = $this->individualCreate(); + $contact_id_2 = $this->individualCreate([], 1); + + // create new relationship (using BAO) + $params = [ + 'relationship_type_id' => $relationship_type_id_1, + 'contact_id_a' => $contact_id_1, + 'contact_id_b' => $contact_id_2, + ]; + $relationshipObj = CRM_Contact_BAO_Relationship::add($params); + + // Let's make sure the vortex records were created! + $vtxs = CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_relationship_vtx WHERE relationship_id = %1', [ + 1 => [$relationshipObj->id, 'Positive'], + ])->fetchAll(); + + // There should be two records - the a_b record and the b_a record. + $this->assertCount(2, $vtxs); + $idx = CRM_Utils_Array::index(['orientation'], $vtxs); + + $this->assertEquals($relationship_type_id_1, $idx['a_b']['relationship_type_id']); + $this->assertEquals($contact_id_1, $idx['a_b']['near_contact_id']); + $this->assertEquals('Praegustator is', $idx['a_b']['near_relation']); + $this->assertEquals($contact_id_2, $idx['a_b']['far_contact_id']); + $this->assertEquals('Praegustator for', $idx['a_b']['far_relation']); + + $this->assertEquals($relationship_type_id_1, $idx['b_a']['relationship_type_id']); + $this->assertEquals($contact_id_2, $idx['b_a']['near_contact_id']); + $this->assertEquals('Praegustator for', $idx['b_a']['near_relation']); + $this->assertEquals($contact_id_1, $idx['b_a']['far_contact_id']); + $this->assertEquals('Praegustator is', $idx['b_a']['far_relation']); + } + +} -- 2.25.1