From 1dba397e0d58835478ed2be71ea392e6e2eefd2d Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Tue, 12 Nov 2019 09:15:24 +1100 Subject: [PATCH] dev/core#183 Convert Temporary Table creation in CRM_Dedupe_BAO_RuleGroup to CRM_Utils_SQL_TemporaryTable and remove unused function internalOptimized --- .../QueryBuilder/IndividualUnsupervised.php | 41 ------------------- CRM/Dedupe/BAO/RuleGroup.php | 25 +++++++---- 2 files changed, 16 insertions(+), 50 deletions(-) diff --git a/CRM/Dedupe/BAO/QueryBuilder/IndividualUnsupervised.php b/CRM/Dedupe/BAO/QueryBuilder/IndividualUnsupervised.php index e450f99fc4..86571064ba 100644 --- a/CRM/Dedupe/BAO/QueryBuilder/IndividualUnsupervised.php +++ b/CRM/Dedupe/BAO/QueryBuilder/IndividualUnsupervised.php @@ -71,45 +71,4 @@ class CRM_Dedupe_BAO_QueryBuilder_IndividualUnsupervised extends CRM_Dedupe_BAO_ return ["civicrm_contact.{$rg->name}.{$rg->threshold}" => $query]; } - /** - * An alternative version which might perform a lot better - * than the above. Will need to do some testing - * - * @param string $rg - * - * @return array - */ - public static function internalOptimized($rg) { - $sql = " -CREATE TEMPORARY TABLE emails ( - email varchar(255), - contact_id1 int, - contact_id2 int, - INDEX(contact_id1), - INDEX(contact_id2) - ) ENGINE=InnoDB -"; - CRM_Core_DAO::executeQuery($sql); - - $sql = " -INSERT INTO emails - SELECT email1.email as email, email1.contact_id as contact_id1, email2.contact_id as contact_id2 - FROM civicrm_email as email1 - JOIN civicrm_email as email2 USING (email) - WHERE email1.contact_id < email2.contact_id - AND " . self::internalFilters($rg, "email1.contact_id", "email2.contact_id"); - CRM_Core_DAO::executeQuery($sql); - - $query = " -SELECT contact_id1 as id1, contact_id2 as id2, {$rg->threshold} as weight -FROM emails -JOIN civicrm_contact as contact1 on contact1.id=contact_id1 -JOIN civicrm_contact as contact2 on contact2.id=contact_id2 -WHERE contact1.contact_type='Individual' -AND contact2.contact_type='Individual' -AND " . self::internalFilters($rg); - - return ["civicrm_contact.{$rg->name}.{$rg->threshold}" => $query]; - } - } diff --git a/CRM/Dedupe/BAO/RuleGroup.php b/CRM/Dedupe/BAO/RuleGroup.php index a5135a5c77..ea98ab26aa 100644 --- a/CRM/Dedupe/BAO/RuleGroup.php +++ b/CRM/Dedupe/BAO/RuleGroup.php @@ -122,7 +122,7 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { // Note that most of the fields available come from 'importable fields' - // I thought about making this field 'importable' but it felt like there might be unknown consequences // so I opted for just adding it in & securing it with a unit test. - // Example usage of sort_name - It is possible to alter sort name via hook so 2 organization names might differ as in + /// Example usage of sort_name - It is possible to alter sort name via hook so 2 organization names might differ as in // Justice League vs The Justice League but these could have the same sort_name if 'the the' // exension is installed (https://github.com/eileenmcnaughton/org.wikimedia.thethe) $fields[$ctype]['civicrm_contact']['sort_name'] = ts('Sort Name'); @@ -209,17 +209,23 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { $this->temporaryTables['dedupe'] = CRM_Utils_SQL_TempTable::build() ->setCategory('dedupe') ->createWithColumns("id1 int, weight int, UNIQUE UI_id1 (id1)")->getName(); + $dedupeCopyTemporaryTableObject = CRM_Utils_SQL_TempTable::build() + ->setCategory('dedupe'); + $this->temporaryTables['dedupe_copy'] = $dedupeCopyTemporaryTableObject->getName(); $insertClause = "INSERT INTO {$this->temporaryTables['dedupe']} (id1, weight)"; $groupByClause = "GROUP BY id1, weight"; - $dupeCopyJoin = " JOIN dedupe_copy ON dedupe_copy.id1 = t1.column WHERE "; + $dupeCopyJoin = " JOIN {$this->temporaryTables['dedupe_copy']} ON {$this->temporaryTables['dedupe_copy']}.id1 = t1.column WHERE "; } else { $this->temporaryTables['dedupe'] = CRM_Utils_SQL_TempTable::build() ->setCategory('dedupe') ->createWithColumns("id1 int, id2 int, weight int, UNIQUE UI_id1_id2 (id1, id2)")->getName(); + $dedupeCopyTemporaryTableObject = CRM_Utils_SQL_TempTable::build() + ->setCategory('dedupe'); + $this->temporaryTables['dedupe_copy'] = $dedupeCopyTemporaryTableObject->getName(); $insertClause = "INSERT INTO {$this->temporaryTables['dedupe']} (id1, id2, weight)"; $groupByClause = "GROUP BY id1, id2, weight"; - $dupeCopyJoin = " JOIN dedupe_copy ON dedupe_copy.id1 = t1.column AND dedupe_copy.id2 = t2.column WHERE "; + $dupeCopyJoin = " JOIN {$this->temporaryTables['dedupe_copy']} ON {$this->temporaryTables['dedupe_copy']}.id1 = t1.column AND {$this->temporaryTables['dedupe_copy']}.id2 = t2.column WHERE "; } $patternColumn = '/t1.(\w+)/'; $exclWeightSum = []; @@ -245,21 +251,22 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { if ($searchWithinDupes) { // get prepared to search within already found dupes if $searchWithinDupes flag is set - $dao->query("DROP TEMPORARY TABLE IF EXISTS dedupe_copy"); - $dao->query("CREATE TEMPORARY TABLE dedupe_copy SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}"); + $dedupeCopyTemporaryTableObject->createWithQuery("SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}"); preg_match($patternColumn, $query, $matches); $query = str_replace(' WHERE ', str_replace('column', $matches[1], $dupeCopyJoin), $query); // CRM-19612: If there's a union, there will be two WHEREs, and you // can't use the temp table twice. - if (preg_match('/dedupe_copy[\S\s]*(union)[\S\s]*dedupe_copy/i', $query, $matches, PREG_OFFSET_CAPTURE)) { + if (preg_match('/' . $this->temporaryTables['dedupe_copy'] . '[\S\s]*(union)[\S\s]*' . $this->temporaryTables['dedupe_copy'] . '/i', $query, $matches, PREG_OFFSET_CAPTURE)) { // Make a second temp table: - $dao->query("DROP TEMPORARY TABLE IF EXISTS dedupe_copy_2"); - $dao->query("CREATE TEMPORARY TABLE dedupe_copy_2 SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}"); + $this->temporaryTables['dedupe_copy_2'] = CRM_Utils_SQL_TempTable::build() + ->setCategory('dedupe') + ->createWithQuery("SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}") + ->getName(); // After the union, use that new temp table: $part1 = substr($query, 0, $matches[1][1]); - $query = $part1 . str_replace('dedupe_copy', 'dedupe_copy_2', substr($query, $matches[1][1])); + $query = $part1 . str_replace($this->temporaryTables['dedupe_copy'], $this->temporaryTables['dedupe_copy_2'], substr($query, $matches[1][1])); } } $searchWithinDupes = 1; -- 2.25.1