| 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 | /** |
| 54 | * @deprecated |
| 55 | * The system will attempt to use the same as your other tables, and |
| 56 | * if you really need something else then use createWithColumns and |
| 57 | * specify it per-column there. |
| 58 | */ |
| 59 | const UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'; |
| 60 | |
| 61 | const CATEGORY_LENGTH = 12; |
| 62 | const CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;'; |
| 63 | // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37 |
| 64 | const ID_LENGTH = 37; |
| 65 | const ID_REGEXP = ';^[a-zA-Z0-9_]+$;'; |
| 66 | const INNODB = 'ENGINE=InnoDB'; |
| 67 | const MEMORY = 'ENGINE=MEMORY'; |
| 68 | |
| 69 | /** |
| 70 | * @var bool |
| 71 | */ |
| 72 | protected $durable; |
| 73 | |
| 74 | /** |
| 75 | * @var bool |
| 76 | */ |
| 77 | protected $utf8; |
| 78 | |
| 79 | protected $category; |
| 80 | |
| 81 | protected $id; |
| 82 | |
| 83 | protected $autodrop; |
| 84 | |
| 85 | protected $memory; |
| 86 | |
| 87 | protected $createSql; |
| 88 | |
| 89 | /** |
| 90 | * @return CRM_Utils_SQL_TempTable |
| 91 | */ |
| 92 | public static function build() { |
| 93 | $t = new CRM_Utils_SQL_TempTable(); |
| 94 | $t->category = NULL; |
| 95 | $t->id = md5(uniqid('', TRUE)); |
| 96 | // The constant CIVICRM_TEMP_FORCE_DURABLE is for local debugging. |
| 97 | $t->durable = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_DURABLE', FALSE); |
| 98 | $t->utf8 = TRUE; |
| 99 | $t->autodrop = FALSE; |
| 100 | $t->memory = FALSE; |
| 101 | return $t; |
| 102 | } |
| 103 | |
| 104 | public function __destruct() { |
| 105 | if ($this->autodrop) { |
| 106 | $this->drop(); |
| 107 | } |
| 108 | } |
| 109 | |
| 110 | /** |
| 111 | * Determine the full table name. |
| 112 | * |
| 113 | * @return string |
| 114 | * Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234' |
| 115 | */ |
| 116 | public function getName() { |
| 117 | $parts = ['civicrm', 'tmp']; |
| 118 | $parts[] = ($this->durable ? 'd' : 'e'); |
| 119 | $parts[] = $this->category ? $this->category : 'dflt'; |
| 120 | $parts[] = $this->id ? $this->id : 'dflt'; |
| 121 | return implode('_', $parts); |
| 122 | } |
| 123 | |
| 124 | /** |
| 125 | * Create the table using results from a SELECT query. |
| 126 | * |
| 127 | * @param string|CRM_Utils_SQL_Select $selectQuery |
| 128 | * @return CRM_Utils_SQL_TempTable |
| 129 | */ |
| 130 | public function createWithQuery($selectQuery) { |
| 131 | $sql = sprintf('%s %s %s AS %s', |
| 132 | $this->toSQL('CREATE'), |
| 133 | $this->memory ? self::MEMORY : self::INNODB, |
| 134 | $this->getUtf8String(), |
| 135 | ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery) |
| 136 | ); |
| 137 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
| 138 | $this->createSql = $sql; |
| 139 | return $this; |
| 140 | } |
| 141 | |
| 142 | /** |
| 143 | * Get the utf8 string for the table. |
| 144 | * |
| 145 | * Our tables are either utf8_unicode_ci OR utf8mb4_unicode_ci - check the contact table |
| 146 | * to see which & use the matching one. Or early adopters may have switched |
| 147 | * switched to other collations e.g. utf8mb4_0900_ai_ci (the default in mysql |
| 148 | * 8). |
| 149 | * |
| 150 | * @return string |
| 151 | */ |
| 152 | public function getUtf8String() { |
| 153 | return $this->utf8 ? ('COLLATE ' . CRM_Core_BAO_SchemaHandler::getInUseCollation()) : ''; |
| 154 | } |
| 155 | |
| 156 | /** |
| 157 | * Create the empty table. |
| 158 | * |
| 159 | * @parma string $columns |
| 160 | * SQL column listing. |
| 161 | * Ex: 'id int(10), name varchar(64)'. |
| 162 | * @return CRM_Utils_SQL_TempTable |
| 163 | */ |
| 164 | public function createWithColumns($columns) { |
| 165 | $sql = sprintf('%s (%s) %s %s', |
| 166 | $this->toSQL('CREATE'), |
| 167 | $columns, |
| 168 | $this->memory ? self::MEMORY : self::INNODB, |
| 169 | $this->getUtf8String() |
| 170 | ); |
| 171 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
| 172 | $this->createSql = $sql; |
| 173 | return $this; |
| 174 | } |
| 175 | |
| 176 | /** |
| 177 | * Drop the table. |
| 178 | * |
| 179 | * @return CRM_Utils_SQL_TempTable |
| 180 | */ |
| 181 | public function drop() { |
| 182 | $sql = $this->toSQL('DROP', 'IF EXISTS'); |
| 183 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
| 184 | return $this; |
| 185 | } |
| 186 | |
| 187 | /** |
| 188 | * @param string $action |
| 189 | * Ex: 'CREATE', 'DROP' |
| 190 | * @param string|null $ifne |
| 191 | * Ex: 'IF EXISTS', 'IF NOT EXISTS'. |
| 192 | * @return string |
| 193 | * Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`' |
| 194 | * Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`' |
| 195 | */ |
| 196 | private function toSQL($action, $ifne = NULL) { |
| 197 | $parts = []; |
| 198 | $parts[] = $action; |
| 199 | if (!$this->durable) { |
| 200 | $parts[] = 'TEMPORARY'; |
| 201 | } |
| 202 | $parts[] = 'TABLE'; |
| 203 | if ($ifne) { |
| 204 | $parts[] = $ifne; |
| 205 | } |
| 206 | $parts[] = '`' . $this->getName() . '`'; |
| 207 | return implode(' ', $parts); |
| 208 | } |
| 209 | |
| 210 | /** |
| 211 | * @return string|NULL |
| 212 | */ |
| 213 | public function getCategory() { |
| 214 | return $this->category; |
| 215 | } |
| 216 | |
| 217 | /** |
| 218 | * @return string|NULL |
| 219 | */ |
| 220 | public function getId() { |
| 221 | return $this->id; |
| 222 | } |
| 223 | |
| 224 | /** |
| 225 | * @return string|NULL |
| 226 | */ |
| 227 | public function getCreateSql() { |
| 228 | return $this->createSql; |
| 229 | } |
| 230 | |
| 231 | /** |
| 232 | * @return bool |
| 233 | */ |
| 234 | public function isAutodrop() { |
| 235 | return $this->autodrop; |
| 236 | } |
| 237 | |
| 238 | /** |
| 239 | * @return bool |
| 240 | */ |
| 241 | public function isDurable() { |
| 242 | return $this->durable; |
| 243 | } |
| 244 | |
| 245 | /** |
| 246 | * @return bool |
| 247 | */ |
| 248 | public function isMemory() { |
| 249 | return $this->memory; |
| 250 | } |
| 251 | |
| 252 | /** |
| 253 | * @deprecated |
| 254 | * @return bool |
| 255 | */ |
| 256 | public function isUtf8() { |
| 257 | CRM_Core_Error::deprecatedFunctionWarning('your own charset/collation per column with createWithColumns if you really need latin1'); |
| 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 | CRM_Core_Error::deprecatedFunctionWarning('your own charset/collation per column with createWithColumns if you really need latin1'); |
| 337 | $this->utf8 = $value; |
| 338 | return $this; |
| 339 | } |
| 340 | |
| 341 | } |