CRM/Utils/SQL/TempTable.php

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