Commit | Line | Data |
---|---|---|
40c8b829 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
4 | | CiviCRM version 5 | | |
5 | +--------------------------------------------------------------------+ | |
6b83d5bd | 6 | | Copyright CiviCRM LLC (c) 2004-2019 | |
40c8b829 TO |
7 | +--------------------------------------------------------------------+ |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
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. | | |
13 | | | | |
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. | | |
18 | | | | |
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 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
6b83d5bd | 31 | * @copyright CiviCRM LLC (c) 2004-2019 |
40c8b829 TO |
32 | * |
33 | * Table naming rules: | |
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}" | |
38 | * | |
0038409f TO |
39 | * To use `TempTable`: |
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()`. | |
43 | * | |
44 | * Example 1: Just create a table name. You'll be responsible for CREATE/DROP actions. | |
40c8b829 TO |
45 | * |
46 | * $name = CRM_Utils_SQL_TempTable::build()->getName(); | |
47 | * $name = CRM_Utils_SQL_TempTable::build()->setDurable()->getName(); | |
0038409f | 48 | * $name = CRM_Utils_SQL_TempTable::build()->setCategory('contactstats')->setId($contact['id'])->getName(); |
40c8b829 TO |
49 | * |
50 | * Example 2: Create a temp table using the results of a SELECT query. | |
51 | * | |
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')); | |
54 | * | |
55 | * Example 3: Create an empty temp table with list of columns. | |
56 | * | |
57 | * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->setUtf8()->createWithColumns('id int(10, name varchar(64)'); | |
58 | * | |
59 | * Example 4: Drop a table that you previously created. | |
60 | * | |
61 | * $tmpTbl->drop(); | |
62 | * | |
63 | * Example 5: Auto-drop a temp table when $tmpTbl falls out of scope | |
64 | * | |
65 | * $tmpTbl->setAutodrop(); | |
66 | * | |
67 | */ | |
68 | class CRM_Utils_SQL_TempTable { | |
69 | ||
70 | const UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'; | |
71 | const CATEGORY_LENGTH = 12; | |
72 | const CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;'; | |
6714d8d2 SL |
73 | // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37 |
74 | const ID_LENGTH = 37; | |
40c8b829 | 75 | const ID_REGEXP = ';^[a-zA-Z0-9_]+$;'; |
8ccee4bf | 76 | const INNODB = 'ENGINE=InnoDB'; |
77 | const MEMORY = 'ENGINE=MEMORY'; | |
40c8b829 TO |
78 | |
79 | /** | |
80 | * @var bool | |
81 | */ | |
6714d8d2 SL |
82 | protected $durable; |
83 | ||
84 | /** | |
85 | * @var bool | |
86 | */ | |
87 | protected $utf8; | |
40c8b829 TO |
88 | |
89 | protected $category; | |
90 | ||
91 | protected $id; | |
92 | ||
93 | protected $autodrop; | |
94 | ||
8ccee4bf | 95 | protected $memory; |
96 | ||
90e5222f | 97 | protected $createSql; |
98 | ||
40c8b829 TO |
99 | /** |
100 | * @return CRM_Utils_SQL_TempTable | |
101 | */ | |
102 | public static function build() { | |
103 | $t = new CRM_Utils_SQL_TempTable(); | |
104 | $t->category = NULL; | |
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); | |
9c714a15 | 108 | // @deprecated This constant is deprecated and will be removed. |
109 | $t->utf8 = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_UTF8', TRUE); | |
40c8b829 | 110 | $t->autodrop = FALSE; |
8ccee4bf | 111 | $t->memory = FALSE; |
40c8b829 TO |
112 | return $t; |
113 | } | |
114 | ||
115 | public function __destruct() { | |
116 | if ($this->autodrop) { | |
117 | $this->drop(); | |
118 | } | |
119 | } | |
120 | ||
121 | /** | |
122 | * Determine the full table name. | |
123 | * | |
124 | * @return string | |
125 | * Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234' | |
126 | */ | |
127 | public function getName() { | |
128 | $parts = ['civicrm', 'tmp']; | |
129 | $parts[] = ($this->durable ? 'd' : 'e'); | |
130 | $parts[] = $this->category ? $this->category : 'dflt'; | |
131 | $parts[] = $this->id ? $this->id : 'dflt'; | |
132 | return implode('_', $parts); | |
133 | } | |
134 | ||
135 | /** | |
136 | * Create the table using results from a SELECT query. | |
137 | * | |
138 | * @param string|CRM_Utils_SQL_Select $selectQuery | |
139 | * @return CRM_Utils_SQL_TempTable | |
140 | */ | |
141 | public function createWithQuery($selectQuery) { | |
8ccee4bf | 142 | $sql = sprintf('%s %s %s AS %s', |
40c8b829 | 143 | $this->toSQL('CREATE'), |
8ccee4bf | 144 | $this->memory ? self::MEMORY : self::INNODB, |
40c8b829 TO |
145 | $this->utf8 ? self::UTF8 : '', |
146 | ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery) | |
147 | ); | |
be2fb01f | 148 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
90e5222f | 149 | $this->createSql = $sql; |
40c8b829 TO |
150 | return $this; |
151 | } | |
152 | ||
153 | /** | |
154 | * Create the empty table. | |
155 | * | |
156 | * @parma string $columns | |
157 | * SQL column listing. | |
158 | * Ex: 'id int(10), name varchar(64)'. | |
159 | * @return CRM_Utils_SQL_TempTable | |
160 | */ | |
161 | public function createWithColumns($columns) { | |
8ccee4bf | 162 | $sql = sprintf('%s (%s) %s %s', |
40c8b829 TO |
163 | $this->toSQL('CREATE'), |
164 | $columns, | |
8ccee4bf | 165 | $this->memory ? self::MEMORY : self::INNODB, |
40c8b829 TO |
166 | $this->utf8 ? self::UTF8 : '' |
167 | ); | |
be2fb01f | 168 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
90e5222f | 169 | $this->createSql = $sql; |
40c8b829 TO |
170 | return $this; |
171 | } | |
172 | ||
173 | /** | |
174 | * Drop the table. | |
175 | * | |
176 | * @return CRM_Utils_SQL_TempTable | |
177 | */ | |
178 | public function drop() { | |
179 | $sql = $this->toSQL('DROP', 'IF EXISTS'); | |
be2fb01f | 180 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
40c8b829 TO |
181 | return $this; |
182 | } | |
183 | ||
184 | /** | |
185 | * @param string $action | |
186 | * Ex: 'CREATE', 'DROP' | |
187 | * @param string|NULL $ifne | |
188 | * Ex: 'IF EXISTS', 'IF NOT EXISTS'. | |
189 | * @return string | |
190 | * Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`' | |
191 | * Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`' | |
192 | */ | |
193 | private function toSQL($action, $ifne = NULL) { | |
194 | $parts = []; | |
195 | $parts[] = $action; | |
196 | if (!$this->durable) { | |
197 | $parts[] = 'TEMPORARY'; | |
198 | } | |
199 | $parts[] = 'TABLE'; | |
200 | if ($ifne) { | |
201 | $parts[] = $ifne; | |
202 | } | |
203 | $parts[] = '`' . $this->getName() . '`'; | |
204 | return implode(' ', $parts); | |
205 | } | |
206 | ||
207 | /** | |
208 | * @return string|NULL | |
209 | */ | |
210 | public function getCategory() { | |
211 | return $this->category; | |
212 | } | |
213 | ||
214 | /** | |
215 | * @return string|NULL | |
216 | */ | |
217 | public function getId() { | |
218 | return $this->id; | |
219 | } | |
220 | ||
90e5222f | 221 | /** |
222 | * @return string|NULL | |
223 | */ | |
224 | public function getCreateSql() { | |
225 | return $this->createSql; | |
226 | } | |
227 | ||
40c8b829 TO |
228 | /** |
229 | * @return bool | |
230 | */ | |
231 | public function isAutodrop() { | |
232 | return $this->autodrop; | |
233 | } | |
234 | ||
235 | /** | |
236 | * @return bool | |
237 | */ | |
238 | public function isDurable() { | |
239 | return $this->durable; | |
240 | } | |
241 | ||
8ccee4bf | 242 | /** |
243 | * @return bool | |
244 | */ | |
245 | public function isMemory() { | |
246 | return $this->memory; | |
247 | } | |
248 | ||
40c8b829 TO |
249 | /** |
250 | * @return bool | |
251 | */ | |
252 | public function isUtf8() { | |
253 | return $this->utf8; | |
254 | } | |
255 | ||
256 | /** | |
257 | * @param bool $autodrop | |
258 | * @return CRM_Utils_SQL_TempTable | |
259 | */ | |
260 | public function setAutodrop($autodrop = TRUE) { | |
261 | $this->autodrop = $autodrop; | |
262 | return $this; | |
263 | } | |
264 | ||
265 | /** | |
266 | * @param string|NULL $category | |
f0197a3d | 267 | * |
40c8b829 TO |
268 | * @return CRM_Utils_SQL_TempTable |
269 | */ | |
270 | public function setCategory($category) { | |
271 | if ($category && !preg_match(self::CATEGORY_REGEXP, $category) || strlen($category) > self::CATEGORY_LENGTH) { | |
272 | throw new \RuntimeException("Malformed temp table category"); | |
273 | } | |
274 | $this->category = $category; | |
275 | return $this; | |
276 | } | |
277 | ||
278 | /** | |
f0197a3d | 279 | * Set whether the table should be durable. |
280 | * | |
281 | * Durable tables are not TEMPORARY in the mysql sense. | |
282 | * | |
283 | * @param bool $durable | |
284 | * | |
40c8b829 TO |
285 | * @return CRM_Utils_SQL_TempTable |
286 | */ | |
287 | public function setDurable($durable = TRUE) { | |
288 | $this->durable = $durable; | |
289 | return $this; | |
290 | } | |
291 | ||
292 | /** | |
f0197a3d | 293 | * Setter for id |
294 | * | |
40c8b829 | 295 | * @param mixed $id |
f0197a3d | 296 | * |
40c8b829 TO |
297 | * @return CRM_Utils_SQL_TempTable |
298 | */ | |
299 | public function setId($id) { | |
300 | if ($id && !preg_match(self::ID_REGEXP, $id) || strlen($id) > self::ID_LENGTH) { | |
301 | throw new \RuntimeException("Malformed temp table id"); | |
302 | } | |
303 | $this->id = $id; | |
304 | return $this; | |
305 | } | |
306 | ||
8ccee4bf | 307 | /** |
308 | * Set table engine to MEMORY. | |
309 | * | |
310 | * @param bool $value | |
311 | * | |
312 | * @return $this | |
313 | */ | |
314 | public function setMemory($value = TRUE) { | |
315 | $this->memory = $value; | |
316 | return $this; | |
317 | } | |
318 | ||
f0197a3d | 319 | /** |
320 | * Set table collation to UTF8. | |
321 | * | |
322 | * This would make sense as a default but cautiousness during phasing in has made it opt-in. | |
323 | * | |
324 | * @param bool $value | |
325 | * | |
326 | * @return $this | |
327 | */ | |
40c8b829 TO |
328 | public function setUtf8($value = TRUE) { |
329 | $this->utf8 = $value; | |
330 | return $this; | |
331 | } | |
332 | ||
333 | } |