From f0197a3d0be92152413807b97b1aafb2e765fe2e Mon Sep 17 00:00:00 2001
From: eileen <emcnaughton@wikimedia.org>
Date: Wed, 14 Nov 2018 15:13:05 +1300
Subject: [PATCH] Lybunt report - improve developer support for debugging this
 report

By using the createTemporaryTable to create our temporaray table we
help developers out by
a) adding the query to the developer tab
b) supporting CIVICRM_TEMP_FORCE_DURABLE
(see debugging section in dev docs)

This fix updates a function used by reports that have been marked with

groupFilterNotOptimised = FALSE

The reports with optimised group filtering construct a temp table
of contacts in the groups & use those to inner join / limit
the contacts in the report.

They are tested via a bunch of tests in api_v3_ReportTemplateTest such as

testReportsWithNonSmartGroupFilter

m

Change-Id: I1102b43a643760320a4b011c7a11146c8d4f380f
---
 CRM/Report/Form.php                   | 21 +++++++++------------
 CRM/Report/Form/Contribute/Lybunt.php | 11 +++++------
 CRM/Utils/SQL/TempTable.php           | 20 +++++++++++++++++++-
 3 files changed, 33 insertions(+), 19 deletions(-)

diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php
index 83b1572880..8286f49d9e 100644
--- a/CRM/Report/Form.php
+++ b/CRM/Report/Form.php
@@ -1145,19 +1145,20 @@ class CRM_Report_Form extends CRM_Core_Form {
    *
    * This function creates a table AND adds the details to the developer tab & $this->>temporary tables.
    *
-   * @todo improve presentation on the developer tab since CREATE TEMPORARY is removed.
-   *
    * @param string $identifier
    * @param $sql
-   * @param bool $isTrueTemporary
-   *   Is this a mysql temporary table or temporary in a less technical sense.
    *
    * @return string
    */
-  public function createTemporaryTable($identifier, $sql, $isTrueTemporary = TRUE) {
+  public function createTemporaryTable($identifier, $sql) {
+    $tempTable = CRM_Utils_SQL_TempTable::build()->setUtf8(TRUE)->createWithQuery($sql);
+    $name = $tempTable->getName();
+    // Developers may force tables to be durable to assist in debugging so lets check.
+    $isNotTrueTemporary = $tempTable->isDurable();
+    // The TempTable build routine adds the next line - we output it to help developers see what has happened.
+    $sql = 'CREATE ' . ($isNotTrueTemporary ? '' : 'TEMPORARY ') . "TABLE $name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci " . $sql;
     $this->addToDeveloperTab($sql);
-    $name = CRM_Utils_SQL_TempTable::build()->setUtf8(TRUE)->setDurable($isTrueTemporary)->createWithQuery($sql)->getName();
-    $this->temporaryTables[$identifier] = ['temporary' => $isTrueTemporary, 'name' => $name];
+    $this->temporaryTables[$identifier] = ['temporary' => !$isNotTrueTemporary, 'name' => $name];
     return $name;
   }
 
@@ -3692,11 +3693,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND
         WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
     }
 
-    $this->groupTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('rptgrp')->setId(date('Ymd_') . uniqid())->getName();
-    $this->executeReportQuery("
-      CREATE TEMPORARY TABLE $this->groupTempTable $this->_databaseAttributes
-      $query
-    ");
+    $this->groupTempTable = $this->createTemporaryTable('rptgrp', $query);
     CRM_Core_DAO::executeQuery("ALTER TABLE $this->groupTempTable ADD INDEX i_id(id)");
   }
 
diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php
index e5aeb0fcba..a7aeaee903 100644
--- a/CRM/Report/Form/Contribute/Lybunt.php
+++ b/CRM/Report/Form/Contribute/Lybunt.php
@@ -567,13 +567,12 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form {
     // @todo this acl has no test coverage and is very hard to test manually so could be fragile.
     $this->resetFormSqlAndWhereHavingClauses();
 
-    $this->contactTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('rptlybunt')->setId(date('Ymd_') . uniqid())->getName();
+    $this->contactTempTable = $this->createTemporaryTable('rptlybunt', "
+      SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from}
+      {$this->_where}
+      GROUP BY {$this->_aliases['civicrm_contact']}.id"
+    );
     $this->limit();
-    $getContacts = "
-      CREATE TEMPORARY TABLE $this->contactTempTable {$this->_databaseAttributes}
-      SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where}
-      GROUP BY {$this->_aliases['civicrm_contact']}.id";
-    $this->executeReportQuery($getContacts);
     if (empty($this->_params['charts'])) {
       $this->setPager();
     }
diff --git a/CRM/Utils/SQL/TempTable.php b/CRM/Utils/SQL/TempTable.php
index cedc46c816..1a2e2faf36 100644
--- a/CRM/Utils/SQL/TempTable.php
+++ b/CRM/Utils/SQL/TempTable.php
@@ -233,6 +233,7 @@ class CRM_Utils_SQL_TempTable {
 
   /**
    * @param string|NULL $category
+   *
    * @return CRM_Utils_SQL_TempTable
    */
   public function setCategory($category) {
@@ -244,7 +245,12 @@ class CRM_Utils_SQL_TempTable {
   }
 
   /**
-   * @parma bool $value
+   * Set whether the table should be durable.
+   *
+   * Durable tables are not TEMPORARY in the mysql sense.
+   *
+   * @param bool $durable
+   *
    * @return CRM_Utils_SQL_TempTable
    */
   public function setDurable($durable = TRUE) {
@@ -253,7 +259,10 @@ class CRM_Utils_SQL_TempTable {
   }
 
   /**
+   * Setter for id
+   *
    * @param mixed $id
+   *
    * @return CRM_Utils_SQL_TempTable
    */
   public function setId($id) {
@@ -264,6 +273,15 @@ class CRM_Utils_SQL_TempTable {
     return $this;
   }
 
+  /**
+   * Set table collation to UTF8.
+   *
+   * This would make sense as a default but cautiousness during phasing in has made it opt-in.
+   *
+   * @param bool $value
+   *
+   * @return $this
+   */
   public function setUtf8($value = TRUE) {
     $this->utf8 = $value;
     return $this;
-- 
2.25.1