Commit | Line | Data |
---|---|---|
40c8b829 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
40c8b829 | 5 | | | |
bc77d7c0 TO |
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 | | |
40c8b829 TO |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
40c8b829 TO |
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 | * | |
0038409f TO |
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. | |
40c8b829 TO |
28 | * |
29 | * $name = CRM_Utils_SQL_TempTable::build()->getName(); | |
30 | * $name = CRM_Utils_SQL_TempTable::build()->setDurable()->getName(); | |
0038409f | 31 | * $name = CRM_Utils_SQL_TempTable::build()->setCategory('contactstats')->setId($contact['id'])->getName(); |
40c8b829 TO |
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 | * | |
0033aaaf | 40 | * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->createWithColumns('id int(10, name varchar(64)'); |
40c8b829 TO |
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 | ||
537dacb1 | 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 | */ | |
40c8b829 | 59 | const UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'; |
537dacb1 | 60 | |
40c8b829 TO |
61 | const CATEGORY_LENGTH = 12; |
62 | const CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;'; | |
6714d8d2 SL |
63 | // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37 |
64 | const ID_LENGTH = 37; | |
40c8b829 | 65 | const ID_REGEXP = ';^[a-zA-Z0-9_]+$;'; |
8ccee4bf | 66 | const INNODB = 'ENGINE=InnoDB'; |
67 | const MEMORY = 'ENGINE=MEMORY'; | |
40c8b829 TO |
68 | |
69 | /** | |
70 | * @var bool | |
71 | */ | |
6714d8d2 SL |
72 | protected $durable; |
73 | ||
74 | /** | |
75 | * @var bool | |
76 | */ | |
77 | protected $utf8; | |
40c8b829 TO |
78 | |
79 | protected $category; | |
80 | ||
81 | protected $id; | |
82 | ||
83 | protected $autodrop; | |
84 | ||
8ccee4bf | 85 | protected $memory; |
86 | ||
90e5222f | 87 | protected $createSql; |
88 | ||
40c8b829 TO |
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); | |
0033aaaf | 98 | $t->utf8 = TRUE; |
40c8b829 | 99 | $t->autodrop = FALSE; |
8ccee4bf | 100 | $t->memory = FALSE; |
40c8b829 TO |
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) { | |
8ccee4bf | 131 | $sql = sprintf('%s %s %s AS %s', |
40c8b829 | 132 | $this->toSQL('CREATE'), |
8ccee4bf | 133 | $this->memory ? self::MEMORY : self::INNODB, |
25374ca7 | 134 | $this->getUtf8String(), |
40c8b829 TO |
135 | ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery) |
136 | ); | |
be2fb01f | 137 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
90e5222f | 138 | $this->createSql = $sql; |
40c8b829 TO |
139 | return $this; |
140 | } | |
141 | ||
25374ca7 | 142 | /** |
143 | * Get the utf8 string for the table. | |
144 | * | |
537dacb1 | 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). | |
25374ca7 | 149 | * |
150 | * @return string | |
151 | */ | |
152 | public function getUtf8String() { | |
8f719ee0 | 153 | return $this->utf8 ? ('COLLATE ' . CRM_Core_BAO_SchemaHandler::getInUseCollation()) : ''; |
25374ca7 | 154 | } |
155 | ||
40c8b829 TO |
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) { | |
8ccee4bf | 165 | $sql = sprintf('%s (%s) %s %s', |
40c8b829 TO |
166 | $this->toSQL('CREATE'), |
167 | $columns, | |
8ccee4bf | 168 | $this->memory ? self::MEMORY : self::INNODB, |
253cc262 | 169 | $this->getUtf8String() |
40c8b829 | 170 | ); |
be2fb01f | 171 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
90e5222f | 172 | $this->createSql = $sql; |
40c8b829 TO |
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'); | |
be2fb01f | 183 | CRM_Core_DAO::executeQuery($sql, [], TRUE, NULL, TRUE, FALSE); |
40c8b829 TO |
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 | ||
90e5222f | 224 | /** |
225 | * @return string|NULL | |
226 | */ | |
227 | public function getCreateSql() { | |
228 | return $this->createSql; | |
229 | } | |
230 | ||
40c8b829 TO |
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 | ||
8ccee4bf | 245 | /** |
246 | * @return bool | |
247 | */ | |
248 | public function isMemory() { | |
249 | return $this->memory; | |
250 | } | |
251 | ||
40c8b829 | 252 | /** |
537dacb1 | 253 | * @deprecated |
40c8b829 TO |
254 | * @return bool |
255 | */ | |
256 | public function isUtf8() { | |
537dacb1 | 257 | CRM_Core_Error::deprecatedFunctionWarning('your own charset/collation per column with createWithColumns if you really need latin1'); |
40c8b829 TO |
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 | |
f0197a3d | 272 | * |
40c8b829 TO |
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) { | |
f5c0a346 | 277 | throw new \RuntimeException("Malformed temp table category $category"); |
40c8b829 TO |
278 | } |
279 | $this->category = $category; | |
280 | return $this; | |
281 | } | |
282 | ||
283 | /** | |
f0197a3d | 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 | * | |
40c8b829 TO |
290 | * @return CRM_Utils_SQL_TempTable |
291 | */ | |
292 | public function setDurable($durable = TRUE) { | |
293 | $this->durable = $durable; | |
294 | return $this; | |
295 | } | |
296 | ||
297 | /** | |
f0197a3d | 298 | * Setter for id |
299 | * | |
40c8b829 | 300 | * @param mixed $id |
f0197a3d | 301 | * |
40c8b829 TO |
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 | ||
8ccee4bf | 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 | ||
f0197a3d | 324 | /** |
325 | * Set table collation to UTF8. | |
326 | * | |
0033aaaf | 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. | |
f0197a3d | 330 | * |
331 | * @param bool $value | |
332 | * | |
333 | * @return $this | |
334 | */ | |
40c8b829 | 335 | public function setUtf8($value = TRUE) { |
537dacb1 | 336 | CRM_Core_Error::deprecatedFunctionWarning('your own charset/collation per column with createWithColumns if you really need latin1'); |
40c8b829 TO |
337 | $this->utf8 = $value; |
338 | return $this; | |
339 | } | |
340 | ||
341 | } |