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