From cc06bec0e5142e58af80c3fab841b4f8ec08138e Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Tue, 12 Nov 2019 09:13:06 +1100 Subject: [PATCH] dev/core#183 Convert creating of temporary tables in CRM/Mailing files to using CRM_Utils_SQL_TempTable --- CRM/Mailing/BAO/Mailing.php | 20 ++++++-------------- CRM/Mailing/BAO/Recipients.php | 16 ++++++++-------- CRM/Mailing/Event/BAO/Delivered.php | 17 ++++++++--------- 3 files changed, 22 insertions(+), 31 deletions(-) diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index 593928c253..81fc795891 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -190,13 +190,8 @@ class CRM_Mailing_BAO_Mailing extends CRM_Mailing_DAO_Mailing { } // Create a temp table for contact exclusion. - $excludeTempTablename = "excluded_recipients_temp" . substr(sha1(rand()), 0, 4); - $includedTempTablename = "included_recipients_temp" . substr(sha1(rand()), 0, 4); - $mailingGroup->query( - "CREATE TEMPORARY TABLE $excludeTempTablename - (contact_id int primary key) - ENGINE=HEAP" - ); + $excludeTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('exrecipient')->setMemory()->createWithColumns('contact_id int primary key'); + $excludeTempTablename = $excludeTempTable->getName(); // populate exclude temp-table with recipients to be excluded from the list // on basis of selected recipients groups and/or previous mailing if (!empty($recipientsGroup['Exclude'])) { @@ -237,11 +232,8 @@ class CRM_Mailing_BAO_Mailing extends CRM_Mailing_DAO_Mailing { $entityColumn = $isSMSmode ? 'phone_id' : 'email_id'; $entityTable = $isSMSmode ? CRM_Core_DAO_Phone::getTableName() : CRM_Core_DAO_Email::getTableName(); // Get all the group contacts we want to include. - $mailingGroup->query( - "CREATE TEMPORARY TABLE $includedTempTablename - (contact_id int primary key, $entityColumn int) - ENGINE=HEAP" - ); + $includedTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('inrecipient')->setMemory()->createWithColumns('contact_id int primary key, ' . $entityColumn . ' int'); + $includedTempTablename = $includedTempTable->getName(); if ($isSMSmode) { $criteria = [ @@ -405,8 +397,8 @@ class CRM_Mailing_BAO_Mailing extends CRM_Mailing_DAO_Mailing { // Delete the temp table. $mailingGroup->reset(); - $mailingGroup->query(" DROP TEMPORARY TABLE $excludeTempTablename "); - $mailingGroup->query(" DROP TEMPORARY TABLE $includedTempTablename "); + $excludeTempTable->drop(); + $includedTempTable->drop(); CRM_Utils_Hook::alterMailingRecipients($mailingObj, $criteria, 'post'); } diff --git a/CRM/Mailing/BAO/Recipients.php b/CRM/Mailing/BAO/Recipients.php index 70693f8866..2dbba9e44a 100644 --- a/CRM/Mailing/BAO/Recipients.php +++ b/CRM/Mailing/BAO/Recipients.php @@ -115,14 +115,13 @@ WHERE r.mailing_id = %1 if ($totalLimit) { $limitString = "LIMIT 0, $totalLimit"; } - CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS srcMailing_$sourceMailingId"); + $temporaryTable = CRM_Utils_SQL_TempTable::build() + ->setCategory('srcmailing' . $sourceMailingId) + ->setMemory() + ->createWithColumns("mailing_recipient_id int unsigned, id int PRIMARY KEY AUTO_INCREMENT, INDEX(mailing_recipient_id)"); + $temporaryTableName = $temporaryTable->getName(); $sql = " -CREATE TEMPORARY TABLE srcMailing_$sourceMailingId - (mailing_recipient_id int unsigned, id int PRIMARY KEY AUTO_INCREMENT, INDEX(mailing_recipient_id)) - ENGINE=HEAP"; - CRM_Core_DAO::executeQuery($sql); - $sql = " -INSERT INTO srcMailing_$sourceMailingId (mailing_recipient_id) +INSERT INTO {$temporaryTableName} (mailing_recipient_id) SELECT mr.id FROM civicrm_mailing_recipients mr WHERE mr.mailing_id = $sourceMailingId @@ -132,10 +131,11 @@ $limitString CRM_Core_DAO::executeQuery($sql); $sql = " UPDATE civicrm_mailing_recipients mr -INNER JOIN srcMailing_$sourceMailingId temp_mr ON temp_mr.mailing_recipient_id = mr.id +INNER JOIN {$temporaryTableName} temp_mr ON temp_mr.mailing_recipient_id = mr.id SET mr.mailing_id = $newMailingID "; CRM_Core_DAO::executeQuery($sql); + $temporaryTable->drop(); } /** diff --git a/CRM/Mailing/Event/BAO/Delivered.php b/CRM/Mailing/Event/BAO/Delivered.php index 5b2e20ac31..37dae70dc1 100644 --- a/CRM/Mailing/Event/BAO/Delivered.php +++ b/CRM/Mailing/Event/BAO/Delivered.php @@ -282,16 +282,14 @@ class CRM_Mailing_Event_BAO_Delivered extends CRM_Mailing_Event_DAO_Delivered { public static function updateEmailResetDate($minDays = 3, $maxDays = 7) { $dao = new CRM_Core_DAO(); - $query = " -CREATE TEMPORARY TABLE civicrm_email_temp_values ( - id int primary key, - reset_date datetime -) ENGINE = HEAP; -"; - CRM_Core_DAO::executeQuery($query); + $temporaryTable = CRM_Utils_SQL_TempTable::build() + ->setCategory('mailingemail') + ->setMemory() + ->createWithColumns('id int primary key, reset_date datetime'); + $temporaryTableName = $temporaryTable->getName(); $query = " - INSERT INTO civicrm_email_temp_values (id, reset_date) + INSERT INTO {$temporaryTableName} (id, reset_date) SELECT civicrm_email.id as email_id, max(civicrm_mailing_event_delivered.time_stamp) as reset_date FROM civicrm_mailing_event_queue @@ -309,12 +307,13 @@ CREATE TEMPORARY TABLE civicrm_email_temp_values ( $query = " UPDATE civicrm_email e -INNER JOIN civicrm_email_temp_values et ON e.id = et.id +INNER JOIN {$temporaryTableName} et ON e.id = et.id SET e.on_hold = 0, e.hold_date = NULL, e.reset_date = et.reset_date "; CRM_Core_DAO::executeQuery($query); + $temporaryTable->drop(); } } -- 2.25.1