From 887c0ec28a36bbd450b263247bac983e4e1d77ef Mon Sep 17 00:00:00 2001 From: eileen Date: Wed, 20 Mar 2019 16:43:08 +1300 Subject: [PATCH] Use tempTable class in creation of dedupe table. The is covered by unit tests ContactTest::testDuplicateTest and DedupeFinderTest::testUnsupervisedDupes There are some others in this file but I focussed on keeping the scope to a single table replacement. A couple of thoughts 1) I passed the indexes in as 'columns' - works, is tested so we'd know if anything broke it but it feels a bit ug 2) I think it makes sense to add a descontruct on the class to drop the temp tables 3) the longer syntax for referring to the table is a bit wordy - but harmless I guess & reformatting would help --- CRM/Dedupe/BAO/RuleGroup.php | 35 +++++++++++++++++++---------------- 1 file changed, 19 insertions(+), 16 deletions(-) diff --git a/CRM/Dedupe/BAO/RuleGroup.php b/CRM/Dedupe/BAO/RuleGroup.php index ba6f020293..e48aac6f41 100644 --- a/CRM/Dedupe/BAO/RuleGroup.php +++ b/CRM/Dedupe/BAO/RuleGroup.php @@ -63,6 +63,8 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { */ var $noRules = FALSE; + protected $temporaryTables = []; + /** * Return a structure holding the supported tables, fields and their titles * @@ -194,24 +196,25 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { $tableQueries = $this->tableQuery(); if ($this->params && !$this->noRules) { - $tempTableQuery = "CREATE TEMPORARY TABLE dedupe (id1 int, weight int, UNIQUE UI_id1 (id1)) ENGINE=InnoDB"; - $insertClause = "INSERT INTO dedupe (id1, weight)"; + $this->temporaryTables['dedupe'] = CRM_Utils_SQL_TempTable::build() + ->setCategory('dedupe') + ->createWithColumns("id1 int, weight int, UNIQUE UI_id1 (id1)")->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 "; } else { - $tempTableQuery = "CREATE TEMPORARY TABLE dedupe (id1 int, id2 int, weight int, UNIQUE UI_id1_id2 (id1, id2)) ENGINE=InnoDB"; - $insertClause = "INSERT INTO dedupe (id1, id2, weight)"; + $this->temporaryTables['dedupe'] = CRM_Utils_SQL_TempTable::build() + ->setCategory('dedupe') + ->createWithColumns("id1 int, id2 int, weight int, UNIQUE UI_id1_id2 (id1, id2)")->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 "; } $patternColumn = '/t1.(\w+)/'; $exclWeightSum = array(); - // create temp table $dao = new CRM_Core_DAO(); - $dao->query($tempTableQuery); - CRM_Utils_Hook::dupeQuery($this, 'table', $tableQueries); while (!empty($tableQueries)) { @@ -233,7 +236,7 @@ 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 dedupe WHERE weight >= {$weightSum}"); + $dao->query("CREATE TEMPORARY TABLE dedupe_copy SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}"); $dao->free(); preg_match($patternColumn, $query, $matches); @@ -244,7 +247,7 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { if (preg_match('/dedupe_copy[\S\s]*(union)[\S\s]*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 dedupe WHERE weight >= {$weightSum}"); + $dao->query("CREATE TEMPORARY TABLE dedupe_copy_2 SELECT * FROM {$this->temporaryTables['dedupe']} WHERE weight >= {$weightSum}"); $dao->free(); // After the union, use that new temp table: $part1 = substr($query, 0, $matches[1][1]); @@ -378,8 +381,8 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause('civicrm_contact'); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; } - $query = "SELECT dedupe.id1 as id - FROM dedupe JOIN civicrm_contact ON dedupe.id1 = civicrm_contact.id {$this->_aclFrom} + $query = "SELECT {$this->temporaryTables['dedupe']}.id1 as id + FROM {$this->temporaryTables['dedupe']} JOIN civicrm_contact ON {$this->temporaryTables['dedupe']}.id1 = civicrm_contact.id {$this->_aclFrom} WHERE contact_type = '{$this->contact_type}' {$this->_aclWhere} AND weight >= {$this->threshold}"; } @@ -389,11 +392,11 @@ class CRM_Dedupe_BAO_RuleGroup extends CRM_Dedupe_DAO_RuleGroup { list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause(array('c1', 'c2')); $this->_aclWhere = $this->_aclWhere ? "AND {$this->_aclWhere}" : ''; } - $query = "SELECT IF(dedupe.id1 < dedupe.id2, dedupe.id1, dedupe.id2) as id1, - IF(dedupe.id1 < dedupe.id2, dedupe.id2, dedupe.id1) as id2, dedupe.weight - FROM dedupe JOIN civicrm_contact c1 ON dedupe.id1 = c1.id - JOIN civicrm_contact c2 ON dedupe.id2 = c2.id {$this->_aclFrom} - LEFT JOIN civicrm_dedupe_exception exc ON dedupe.id1 = exc.contact_id1 AND dedupe.id2 = exc.contact_id2 + $query = "SELECT IF({$this->temporaryTables['dedupe']}.id1 < {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id1, {$this->temporaryTables['dedupe']}.id2) as id1, + IF({$this->temporaryTables['dedupe']}.id1 < {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id2, {$this->temporaryTables['dedupe']}.id1) as id2, {$this->temporaryTables['dedupe']}.weight + FROM {$this->temporaryTables['dedupe']} JOIN civicrm_contact c1 ON {$this->temporaryTables['dedupe']}.id1 = c1.id + JOIN civicrm_contact c2 ON {$this->temporaryTables['dedupe']}.id2 = c2.id {$this->_aclFrom} + LEFT JOIN civicrm_dedupe_exception exc ON {$this->temporaryTables['dedupe']}.id1 = exc.contact_id1 AND {$this->temporaryTables['dedupe']}.id2 = exc.contact_id2 WHERE c1.contact_type = '{$this->contact_type}' AND c2.contact_type = '{$this->contact_type}' {$this->_aclWhere} AND weight >= {$this->threshold} AND exc.contact_id1 IS NULL"; -- 2.25.1