3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
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}"
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()`.
44 * Example 1: Just create a table name. You'll be responsible for CREATE/DROP actions.
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();
50 * Example 2: Create a temp table using the results of a SELECT query.
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'));
55 * Example 3: Create an empty temp table with list of columns.
57 * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->createWithColumns('id int(10, name varchar(64)');
59 * Example 4: Drop a table that you previously created.
63 * Example 5: Auto-drop a temp table when $tmpTbl falls out of scope
65 * $tmpTbl->setAutodrop();
68 class CRM_Utils_SQL_TempTable
{
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 // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37
75 const ID_REGEXP
= ';^[a-zA-Z0-9_]+$;';
76 const INNODB
= 'ENGINE=InnoDB';
77 const MEMORY
= 'ENGINE=MEMORY';
100 * @return CRM_Utils_SQL_TempTable
102 public static function build() {
103 $t = new CRM_Utils_SQL_TempTable();
105 $t->id
= md5(uniqid('', TRUE));
106 // The constant CIVICRM_TEMP_FORCE_DURABLE is for local debugging.
107 $t->durable
= CRM_Utils_Constant
::value('CIVICRM_TEMP_FORCE_DURABLE', FALSE);
109 $t->autodrop
= FALSE;
114 public function __destruct() {
115 if ($this->autodrop
) {
121 * Determine the full table name.
124 * Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234'
126 public function getName() {
127 $parts = ['civicrm', 'tmp'];
128 $parts[] = ($this->durable ?
'd' : 'e');
129 $parts[] = $this->category ?
$this->category
: 'dflt';
130 $parts[] = $this->id ?
$this->id
: 'dflt';
131 return implode('_', $parts);
135 * Create the table using results from a SELECT query.
137 * @param string|CRM_Utils_SQL_Select $selectQuery
138 * @return CRM_Utils_SQL_TempTable
140 public function createWithQuery($selectQuery) {
141 $sql = sprintf('%s %s %s AS %s',
142 $this->toSQL('CREATE'),
143 $this->memory ? self
::MEMORY
: self
::INNODB
,
144 $this->utf8 ? self
::UTF8
: '',
145 ($selectQuery instanceof CRM_Utils_SQL_Select ?
$selectQuery->toSQL() : $selectQuery)
147 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
148 $this->createSql
= $sql;
153 * Create the empty table.
155 * @parma string $columns
156 * SQL column listing.
157 * Ex: 'id int(10), name varchar(64)'.
158 * @return CRM_Utils_SQL_TempTable
160 public function createWithColumns($columns) {
161 $sql = sprintf('%s (%s) %s %s',
162 $this->toSQL('CREATE'),
164 $this->memory ? self
::MEMORY
: self
::INNODB
,
165 $this->utf8 ? self
::UTF8
: ''
167 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
168 $this->createSql
= $sql;
175 * @return CRM_Utils_SQL_TempTable
177 public function drop() {
178 $sql = $this->toSQL('DROP', 'IF EXISTS');
179 CRM_Core_DAO
::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE);
184 * @param string $action
185 * Ex: 'CREATE', 'DROP'
186 * @param string|NULL $ifne
187 * Ex: 'IF EXISTS', 'IF NOT EXISTS'.
189 * Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`'
190 * Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`'
192 private function toSQL($action, $ifne = NULL) {
195 if (!$this->durable
) {
196 $parts[] = 'TEMPORARY';
202 $parts[] = '`' . $this->getName() . '`';
203 return implode(' ', $parts);
207 * @return string|NULL
209 public function getCategory() {
210 return $this->category
;
214 * @return string|NULL
216 public function getId() {
221 * @return string|NULL
223 public function getCreateSql() {
224 return $this->createSql
;
230 public function isAutodrop() {
231 return $this->autodrop
;
237 public function isDurable() {
238 return $this->durable
;
244 public function isMemory() {
245 return $this->memory
;
251 public function isUtf8() {
256 * @param bool $autodrop
257 * @return CRM_Utils_SQL_TempTable
259 public function setAutodrop($autodrop = TRUE) {
260 $this->autodrop
= $autodrop;
265 * @param string|NULL $category
267 * @return CRM_Utils_SQL_TempTable
269 public function setCategory($category) {
270 if ($category && !preg_match(self
::CATEGORY_REGEXP
, $category) ||
strlen($category) > self
::CATEGORY_LENGTH
) {
271 throw new \
RuntimeException("Malformed temp table category");
273 $this->category
= $category;
278 * Set whether the table should be durable.
280 * Durable tables are not TEMPORARY in the mysql sense.
282 * @param bool $durable
284 * @return CRM_Utils_SQL_TempTable
286 public function setDurable($durable = TRUE) {
287 $this->durable
= $durable;
296 * @return CRM_Utils_SQL_TempTable
298 public function setId($id) {
299 if ($id && !preg_match(self
::ID_REGEXP
, $id) ||
strlen($id) > self
::ID_LENGTH
) {
300 throw new \
RuntimeException("Malformed temp table id");
307 * Set table engine to MEMORY.
313 public function setMemory($value = TRUE) {
314 $this->memory
= $value;
319 * Set table collation to UTF8.
321 * @deprecated This method is deprecated as tables should be assumed to have
322 * UTF-8 as the default character set and collation; some other character set
323 * or collation may be specified in the column definition.
329 public function setUtf8($value = TRUE) {
330 $this->utf8
= $value;