3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
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}"
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()`.
27 * Example 1: Just create a table name. You'll be responsible for CREATE/DROP actions.
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();
33 * Example 2: Create a temp table using the results of a SELECT query.
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'));
38 * Example 3: Create an empty temp table with list of columns.
40 * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->createWithColumns('id int(10, name varchar(64)');
42 * Example 4: Drop a table that you previously created.
46 * Example 5: Auto-drop a temp table when $tmpTbl falls out of scope
48 * $tmpTbl->setAutodrop();
51 class CRM_Utils_SQL_TempTable
{
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.
59 const UTF8
= 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci';
61 const CATEGORY_LENGTH
= 12;
62 const CATEGORY_REGEXP
= ';^[a-zA-Z0-9]+$;';
63 // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37
65 const ID_REGEXP
= ';^[a-zA-Z0-9_]+$;';
66 const INNODB
= 'ENGINE=InnoDB';
67 const MEMORY
= 'ENGINE=MEMORY';
90 * @return CRM_Utils_SQL_TempTable
92 public static function build() {
93 $t = new CRM_Utils_SQL_TempTable();
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);
104 public function __destruct() {
105 if ($this->autodrop
) {
111 * Determine the full table name.
114 * Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234'
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);
125 * Create the table using results from a SELECT query.
127 * @param string|CRM_Utils_SQL_Select $selectQuery
128 * @return CRM_Utils_SQL_TempTable
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)
137 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
138 $this->createSql
= $sql;
143 * Get the utf8 string for the table.
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
152 public function getUtf8String() {
153 return $this->utf8 ?
('COLLATE ' . CRM_Core_BAO_SchemaHandler
::getInUseCollation()) : '';
157 * Create the empty table.
159 * @parma string $columns
160 * SQL column listing.
161 * Ex: 'id int(10), name varchar(64)'.
162 * @return CRM_Utils_SQL_TempTable
164 public function createWithColumns($columns) {
165 $sql = sprintf('%s (%s) %s %s',
166 $this->toSQL('CREATE'),
168 $this->memory ? self
::MEMORY
: self
::INNODB
,
169 $this->getUtf8String()
171 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
172 $this->createSql
= $sql;
179 * @return CRM_Utils_SQL_TempTable
181 public function drop() {
182 $sql = $this->toSQL('DROP', 'IF EXISTS');
183 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
188 * @param string $action
189 * Ex: 'CREATE', 'DROP'
190 * @param string|NULL $ifne
191 * Ex: 'IF EXISTS', 'IF NOT EXISTS'.
193 * Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`'
194 * Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`'
196 private function toSQL($action, $ifne = NULL) {
199 if (!$this->durable
) {
200 $parts[] = 'TEMPORARY';
206 $parts[] = '`' . $this->getName() . '`';
207 return implode(' ', $parts);
211 * @return string|NULL
213 public function getCategory() {
214 return $this->category
;
218 * @return string|NULL
220 public function getId() {
225 * @return string|NULL
227 public function getCreateSql() {
228 return $this->createSql
;
234 public function isAutodrop() {
235 return $this->autodrop
;
241 public function isDurable() {
242 return $this->durable
;
248 public function isMemory() {
249 return $this->memory
;
256 public function isUtf8() {
257 CRM_Core_Error
::deprecatedFunctionWarning('your own charset/collation per column with createWithColumns if you really need latin1');
262 * @param bool $autodrop
263 * @return CRM_Utils_SQL_TempTable
265 public function setAutodrop($autodrop = TRUE) {
266 $this->autodrop
= $autodrop;
271 * @param string|NULL $category
273 * @return CRM_Utils_SQL_TempTable
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");
279 $this->category
= $category;
284 * Set whether the table should be durable.
286 * Durable tables are not TEMPORARY in the mysql sense.
288 * @param bool $durable
290 * @return CRM_Utils_SQL_TempTable
292 public function setDurable($durable = TRUE) {
293 $this->durable
= $durable;
302 * @return CRM_Utils_SQL_TempTable
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");
313 * Set table engine to MEMORY.
319 public function setMemory($value = TRUE) {
320 $this->memory
= $value;
325 * Set table collation to UTF8.
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.
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;