CRM/Utils/SQL/TempTable.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  *
  33  * Table naming rules:
  34  *   - MySQL imposes a 64 char limit.
  35  *   - All temp tables start with "civicrm_tmp".
  36  *   - Durable temp tables: "civicrm_tmp_d_{12}_{32}"
  37  *   - Ephemeral temp tables: "civicrm_tmp_e_{12}_{32}"
  38  *
  39  * To use `TempTable`:
  40  *   - Begin by calling `CRM_Utils_SQL_TempTable::build()`.
  41  *   - Optionally, describe the table with `setDurable()`, `setCategory()`, `setId()`.
  42  *   - Finally, call `getName()` or `createWithQuery()` or `createWithColumns()`.
  43  *
  44  * Example 1: Just create a table name. You'll be responsible for CREATE/DROP actions.
  45  *
  46  * $name = CRM_Utils_SQL_TempTable::build()->getName();
  47  * $name = CRM_Utils_SQL_TempTable::build()->setDurable()->getName();
  48  * $name = CRM_Utils_SQL_TempTable::build()->setCategory('contactstats')->setId($contact['id'])->getName();
  49  *
  50  * Example 2: Create a temp table using the results of a SELECT query.
  51  *
  52  * $tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery('SELECT id, display_name FROM civicrm_contact');
  53  * $tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery(CRM_Utils_SQL_Select::from('civicrm_contact')->select('display_name'));
  54  *
  55  * Example 3: Create an empty temp table with list of columns.
  56  *
  57  * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->setUtf8()->createWithColumns('id int(10, name varchar(64)');
  58  *
  59  * Example 4: Drop a table that you previously created.
  60  *
  61  * $tmpTbl->drop();
  62  *
  63  * Example 5: Auto-drop a temp table when $tmpTbl falls out of scope
  64  *
  65  * $tmpTbl->setAutodrop();
  66  *
  67  */
  68 class CRM_Utils_SQL_TempTable {
  69
  70   const UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci';
  71   const CATEGORY_LENGTH = 12;
  72   const CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;';
  73   const ID_LENGTH = 37; // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37
  74   const ID_REGEXP = ';^[a-zA-Z0-9_]+$;';
  75   const INNODB = 'ENGINE=InnoDB';
  76   const MEMORY = 'ENGINE=MEMORY';
  77
  78   /**
  79    * @var bool
  80    */
  81   protected $durable, $utf8;
  82
  83   protected $category;
  84
  85   protected $id;
  86
  87   protected $autodrop;
  88
  89   protected $memory;
  90
  91   /**
  92    * @return CRM_Utils_SQL_TempTable
  93    */
  94   public static function build() {
  95     $t = new CRM_Utils_SQL_TempTable();
  96     $t->category = NULL;
  97     $t->id = md5(uniqid('', TRUE));
  98     // The constant CIVICRM_TEMP_FORCE_DURABLE is for local debugging.
  99     $t->durable = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_DURABLE', FALSE);
 100     // @deprecated This constant is deprecated and will be removed.
 101     $t->utf8 = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_UTF8', TRUE);
 102     $t->autodrop = FALSE;
 103     $t->memory = FALSE;
 104     return $t;
 105   }
 106
 107   public function __destruct() {
 108     if ($this->autodrop) {
 109       $this->drop();
 110     }
 111   }
 112
 113   /**
 114    * Determine the full table name.
 115    *
 116    * @return string
 117    *   Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234'
 118    */
 119   public function getName() {
 120     $parts = ['civicrm', 'tmp'];
 121     $parts[] = ($this->durable ? 'd' : 'e');
 122     $parts[] = $this->category ? $this->category : 'dflt';
 123     $parts[] = $this->id ? $this->id : 'dflt';
 124     return implode('_', $parts);
 125   }
 126
 127   /**
 128    * Create the table using results from a SELECT query.
 129    *
 130    * @param string|CRM_Utils_SQL_Select $selectQuery
 131    * @return CRM_Utils_SQL_TempTable
 132    */
 133   public function createWithQuery($selectQuery) {
 134     $sql = sprintf('%s %s %s AS %s',
 135       $this->toSQL('CREATE'),
 136       $this->memory ? self::MEMORY : self::INNODB,
 137       $this->utf8 ? self::UTF8 : '',
 138       ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery)
 139     );
 140     CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE);
 141     return $this;
 142   }
 143
 144   /**
 145    * Create the empty table.
 146    *
 147    * @parma string $columns
 148    *   SQL column listing.
 149    *   Ex: 'id int(10), name varchar(64)'.
 150    * @return CRM_Utils_SQL_TempTable
 151    */
 152   public function createWithColumns($columns) {
 153     $sql = sprintf('%s (%s) %s %s',
 154       $this->toSQL('CREATE'),
 155       $columns,
 156       $this->memory ? self::MEMORY : self::INNODB,
 157       $this->utf8 ? self::UTF8 : ''
 158     );
 159     CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE);
 160     return $this;
 161   }
 162
 163   /**
 164    * Drop the table.
 165    *
 166    * @return CRM_Utils_SQL_TempTable
 167    */
 168   public function drop() {
 169     $sql = $this->toSQL('DROP', 'IF EXISTS');
 170     CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE);
 171     return $this;
 172   }
 173
 174   /**
 175    * @param string $action
 176    *   Ex: 'CREATE', 'DROP'
 177    * @param string|NULL $ifne
 178    *   Ex: 'IF EXISTS', 'IF NOT EXISTS'.
 179    * @return string
 180    *   Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`'
 181    *   Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`'
 182    */
 183   private function toSQL($action, $ifne = NULL) {
 184     $parts = [];
 185     $parts[] = $action;
 186     if (!$this->durable) {
 187       $parts[] = 'TEMPORARY';
 188     }
 189     $parts[] = 'TABLE';
 190     if ($ifne) {
 191       $parts[] = $ifne;
 192     }
 193     $parts[] = '`' . $this->getName() . '`';
 194     return implode(' ', $parts);
 195   }
 196
 197   /**
 198    * @return string|NULL
 199    */
 200   public function getCategory() {
 201     return $this->category;
 202   }
 203
 204   /**
 205    * @return string|NULL
 206    */
 207   public function getId() {
 208     return $this->id;
 209   }
 210
 211   /**
 212    * @return bool
 213    */
 214   public function isAutodrop() {
 215     return $this->autodrop;
 216   }
 217
 218   /**
 219    * @return bool
 220    */
 221   public function isDurable() {
 222     return $this->durable;
 223   }
 224
 225   /**
 226    * @return bool
 227    */
 228   public function isMemory() {
 229     return $this->memory;
 230   }
 231
 232   /**
 233    * @return bool
 234    */
 235   public function isUtf8() {
 236     return $this->utf8;
 237   }
 238
 239   /**
 240    * @param bool $autodrop
 241    * @return CRM_Utils_SQL_TempTable
 242    */
 243   public function setAutodrop($autodrop = TRUE) {
 244     $this->autodrop = $autodrop;
 245     return $this;
 246   }
 247
 248   /**
 249    * @param string|NULL $category
 250    *
 251    * @return CRM_Utils_SQL_TempTable
 252    */
 253   public function setCategory($category) {
 254     if ($category && !preg_match(self::CATEGORY_REGEXP, $category) || strlen($category) > self::CATEGORY_LENGTH) {
 255       throw new \RuntimeException("Malformed temp table category");
 256     }
 257     $this->category = $category;
 258     return $this;
 259   }
 260
 261   /**
 262    * Set whether the table should be durable.
 263    *
 264    * Durable tables are not TEMPORARY in the mysql sense.
 265    *
 266    * @param bool $durable
 267    *
 268    * @return CRM_Utils_SQL_TempTable
 269    */
 270   public function setDurable($durable = TRUE) {
 271     $this->durable = $durable;
 272     return $this;
 273   }
 274
 275   /**
 276    * Setter for id
 277    *
 278    * @param mixed $id
 279    *
 280    * @return CRM_Utils_SQL_TempTable
 281    */
 282   public function setId($id) {
 283     if ($id && !preg_match(self::ID_REGEXP, $id) || strlen($id) > self::ID_LENGTH) {
 284       throw new \RuntimeException("Malformed temp table id");
 285     }
 286     $this->id = $id;
 287     return $this;
 288   }
 289
 290   /**
 291    * Set table engine to MEMORY.
 292    *
 293    * @param bool $value
 294    *
 295    * @return $this
 296    */
 297   public function setMemory($value = TRUE) {
 298     $this->memory = $value;
 299     return $this;
 300   }
 301
 302   /**
 303    * Set table collation to UTF8.
 304    *
 305    * This would make sense as a default but cautiousness during phasing in has made it opt-in.
 306    *
 307    * @param bool $value
 308    *
 309    * @return $this
 310    */
 311   public function setUtf8($value = TRUE) {
 312     $this->utf8 = $value;
 313     return $this;
 314   }
 315
 316 }