From b53dc556fe2f8d22bade71d4a0919e3001cf6626 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Tue, 30 Jun 2020 23:44:08 -0700 Subject: [PATCH] RelationshipVortex - Create and populate table during upgrade (5.29) --- .../Incremental/php/FiveTwentyNine.php | 69 +++++++++++++++++++ .../Incremental/sql/5.29.alpha1.mysql.tpl | 27 ++++++++ 2 files changed, 96 insertions(+) diff --git a/CRM/Upgrade/Incremental/php/FiveTwentyNine.php b/CRM/Upgrade/Incremental/php/FiveTwentyNine.php index 37a4600e20..bb2aa19ca8 100644 --- a/CRM/Upgrade/Incremental/php/FiveTwentyNine.php +++ b/CRM/Upgrade/Incremental/php/FiveTwentyNine.php @@ -60,6 +60,18 @@ class CRM_Upgrade_Incremental_php_FiveTwentyNine extends CRM_Upgrade_Incremental public function upgrade_5_29_alpha1($rev) { $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev); $this->addTask('Install eventcart extension', 'installEventCart'); + + list($minId, $maxId) = CRM_Core_DAO::executeQuery("SELECT coalesce(min(id),0), coalesce(max(id),0) + FROM civicrm_relationship ")->getDatabaseResult()->fetchRow(); + for ($startId = $minId; $startId <= $maxId; $startId += self::BATCH_SIZE) { + $endId = $startId + self::BATCH_SIZE - 1; + $title = ts("Upgrade DB to %1: Fill civicrm_relationship_vtx (%2 => %3)", [ + 1 => $rev, + 2 => $startId, + 3 => $endId, + ]); + $this->addTask($title, 'populateRelationshipVortex', $startId, $endId); + } } /** @@ -90,6 +102,63 @@ class CRM_Upgrade_Incremental_php_FiveTwentyNine extends CRM_Upgrade_Incremental 'is_active' => 1, ]); CRM_Core_DAO::executeQuery($insert->usingReplace()->toSQL()); + + return TRUE; + } + + /** + * @param \CRM_Queue_TaskContext $ctx + * @param int $startId + * The lowest relationship ID that should be updated. + * @param int $endId + * The highest relationship ID that should be updated. + * @return bool + * TRUE on success + */ + public static function populateRelationshipVortex(CRM_Queue_TaskContext $ctx, $startId, $endId) { + // NOTE: We duplicate CRM_Contact_BAO_RelationshipVortex::$mappings in case + // the schema evolves over multiple releases. + $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', + ], + ]; + $keyFields = ['relationship_id', 'orientation']; + + foreach ($mappings as $mapping) { + $query = 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', $keyFields, $mapping) + ->where('rel.id >= #START AND rel.id <= #END', [ + '#START' => $startId, + '#END' => $endId, + ]); + $query->execute(); + } + return TRUE; } diff --git a/CRM/Upgrade/Incremental/sql/5.29.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/5.29.alpha1.mysql.tpl index 29714937ac..2fefa417eb 100644 --- a/CRM/Upgrade/Incremental/sql/5.29.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/5.29.alpha1.mysql.tpl @@ -1 +1,28 @@ {* file to handle db changes in 5.29.alpha1 during upgrade *} + +-- The RelationshipVortex is a high-level index/cache for querying relationships. +DROP TABLE IF EXISTS `civicrm_relationship_vtx`; +CREATE TABLE `civicrm_relationship_vtx` ( + `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relationship Vortex ID', + `relationship_id` int unsigned NOT NULL COMMENT 'id of the relationship (FK to civicrm_relationship.id)', + `relationship_type_id` int unsigned NOT NULL COMMENT 'id of the relationship type', + `orientation` char(3) NOT NULL COMMENT 'The vortex record is a permutation of the original relationship record. The orientation indicates whether it is forward (a_b) or reverse (b_a) relationship.', + `near_contact_id` int unsigned NOT NULL COMMENT 'id of the first contact', + `near_relation` varchar(64) COMMENT 'name for relationship of near_contact to far_contact.', + `far_contact_id` int unsigned NOT NULL COMMENT 'id of the second contact', + `far_relation` varchar(64) COMMENT 'name for relationship of far_contact to near_contact.', + `is_active` tinyint DEFAULT 1 COMMENT 'is the relationship active ?', + `start_date` date COMMENT 'date when the relationship started', + `end_date` date COMMENT 'date when the relationship ended', + `case_id` int unsigned DEFAULT NULL COMMENT 'FK to civicrm_case', + PRIMARY KEY (`id`), + UNIQUE INDEX `UI_relationship`(relationship_id, orientation), + INDEX `index_nearid_nearrelation`(near_contact_id, near_relation), + INDEX `index_nearid_farrelation`(near_contact_id, far_relation), + INDEX `index_near_relation`(near_relation), + CONSTRAINT FK_civicrm_relationship_vtx_relationship_id FOREIGN KEY (`relationship_id`) REFERENCES `civicrm_relationship`(`id`) ON DELETE CASCADE, + CONSTRAINT FK_civicrm_relationship_vtx_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`) ON DELETE CASCADE, + CONSTRAINT FK_civicrm_relationship_vtx_near_contact_id FOREIGN KEY (`near_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, + CONSTRAINT FK_civicrm_relationship_vtx_far_contact_id FOREIGN KEY (`far_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE, + CONSTRAINT FK_civicrm_relationship_vtx_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; -- 2.25.1