Commit | Line | Data |
---|---|---|
e2b5e5b1 TO |
1 | <?php |
2 | ||
3 | /** | |
4 | * Dear God Why Do I Have To Write This (Dumb SQL Builder) | |
5 | * | |
6 | * Usage: | |
c5458931 TO |
7 | * @code |
8 | * $select = CRM_Utils_SQL_Select::from('civicrm_activity act') | |
e2b5e5b1 | 9 | * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id') |
c5458931 TO |
10 | * ->where('activity_type_id = #type', array('type' => 234)) |
11 | * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3)) | |
12 | * ->where('subject like @subj', array('subj' => '%hello%')) | |
13 | * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)')) | |
e2b5e5b1 | 14 | * ->where('!column = @value', array( |
c5458931 TO |
15 | * 'column' => $customField->column_name, |
16 | * 'value' => $form['foo'] | |
e2b5e5b1 TO |
17 | * )) |
18 | * echo $select->toSQL(); | |
c5458931 | 19 | * @endcode |
e2b5e5b1 TO |
20 | * |
21 | * Design principles: | |
22 | * - Portable | |
23 | * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString) | |
24 | * - No knowledge of the underlying data model | |
25 | * - Single file | |
26 | * - SQL clauses correspond to PHP functions ($select->where("foo_id=123")) | |
27 | * - Variable escaping is concise and controllable based on prefixes, eg | |
28 | * - similar to Drupal's t() | |
29 | * - use "@varname" to insert the escaped value | |
30 | * - use "!varname" to insert raw (unescaped) values | |
31 | * - use "#varname" to insert a numerical value (these are validated but not escaped) | |
32 | * - to disable any preprocessing, simply omit the variable list | |
c5458931 | 33 | * - control characters (@!#) are mandatory in expressions but optional in arg-keys |
e2b5e5b1 TO |
34 | * - Variables may be individual values or arrays; arrays are imploded with commas |
35 | * - Conditionals are AND'd; if you need OR's, do it yourself | |
c3a8e3e5 | 36 | * - Use classes/functions with documentation (rather than undocumented array-trees) |
c5458931 TO |
37 | * - For any given string, interpolation is only performed once. After an interpolation, |
38 | * a string may never again be subjected to interpolation. | |
39 | * | |
40 | * The "interpolate-once" principle can be enforced by either interpolating on input | |
41 | * xor output. The notations for input and output interpolation are a bit different, | |
42 | * and they may not be mixed. | |
43 | * | |
44 | * @code | |
45 | * // Interpolate on input. Set params when using them. | |
46 | * $select->where('activity_type_id = #type', array( | |
47 | * 'type' => 234, | |
48 | * )); | |
49 | * | |
50 | * // Interpolate on output. Set params independently. | |
51 | * $select | |
52 | * ->where('activity_type_id = #type') | |
53 | * ->param('type', 234), | |
54 | * @endcode | |
e2b5e5b1 | 55 | */ |
c5458931 TO |
56 | class CRM_Utils_SQL_Select implements ArrayAccess { |
57 | ||
58 | /** | |
59 | * Interpolate values as soon as they are passed in (where(), join(), etc). | |
60 | * | |
61 | * Default. | |
62 | * | |
63 | * Pro: Every clause has its own unique namespace for parameters. | |
64 | * Con: Probably slower. | |
65 | * Advice: Use this when aggregating SQL fragments from agents who | |
66 | * maintained by different parties. | |
67 | */ | |
68 | const INTERPOLATE_INPUT = 'in'; | |
69 | ||
70 | /** | |
71 | * Interpolate values when rendering SQL output (toSQL()). | |
72 | * | |
73 | * Pro: Probably faster. | |
74 | * Con: Must maintain an aggregated list of all parameters. | |
75 | * Advice: Use this when you have control over the entire query. | |
76 | */ | |
77 | const INTERPOLATE_OUTPUT = 'out'; | |
78 | ||
79 | /** | |
80 | * Determine mode automatically. When the first attempt is made | |
81 | * to use input-interpolation (eg `where(..., array(...))`) or | |
82 | * output-interpolation (eg `param(...)`), the mode will be | |
83 | * set. Subsequent calls will be validated using the same mode. | |
84 | */ | |
85 | const INTERPOLATE_AUTO = 'auto'; | |
86 | ||
87 | private $mode = NULL; | |
88 | private $insertInto = NULL; | |
89 | private $insertIntoFields = array(); | |
e2b5e5b1 TO |
90 | private $selects = array(); |
91 | private $from; | |
92 | private $joins = array(); | |
93 | private $wheres = array(); | |
94 | private $groupBys = array(); | |
95 | private $havings = array(); | |
96 | private $orderBys = array(); | |
c3a8e3e5 TO |
97 | private $limit = NULL; |
98 | private $offset = NULL; | |
c5458931 TO |
99 | private $params = array(); |
100 | ||
101 | // Public to work-around PHP 5.3 limit. | |
102 | public $strict = NULL; | |
e2b5e5b1 TO |
103 | |
104 | /** | |
fe482240 | 105 | * Create a new SELECT query. |
e2b5e5b1 | 106 | * |
77855840 TO |
107 | * @param string $from |
108 | * Table-name and optional alias. | |
c5458931 TO |
109 | * @param array $options |
110 | * @return CRM_Utils_SQL_Select | |
111 | */ | |
112 | public static function from($from, $options = array()) { | |
113 | return new self($from, $options); | |
114 | } | |
115 | ||
116 | /** | |
117 | * Create a partial SELECT query. | |
118 | * | |
119 | * @param array $options | |
e2b5e5b1 TO |
120 | * @return CRM_Utils_SQL_Select |
121 | */ | |
c5458931 TO |
122 | public static function fragment($options = array()) { |
123 | return new self(NULL, $options); | |
e2b5e5b1 TO |
124 | } |
125 | ||
c3a8e3e5 | 126 | /** |
fe482240 | 127 | * Create a new SELECT query. |
c3a8e3e5 | 128 | * |
77855840 TO |
129 | * @param string $from |
130 | * Table-name and optional alias. | |
c5458931 | 131 | * @param array $options |
c3a8e3e5 | 132 | */ |
c5458931 | 133 | public function __construct($from, $options = array()) { |
e2b5e5b1 | 134 | $this->from = $from; |
c5458931 TO |
135 | $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO; |
136 | } | |
137 | ||
138 | /** | |
139 | * Make a new copy of this query. | |
140 | * | |
141 | * @return CRM_Utils_SQL_Select | |
142 | */ | |
143 | public function copy() { | |
144 | return clone $this; | |
145 | } | |
146 | ||
147 | /** | |
148 | * @param CRM_Utils_SQL_Select $other | |
149 | * @param array|NULL $parts | |
150 | * ex: 'joins', 'wheres' | |
151 | * @return $this | |
152 | */ | |
153 | public function merge($other, $parts = NULL) { | |
154 | if ($other === NULL) { | |
155 | return $this; | |
156 | } | |
157 | ||
158 | if ($this->mode === self::INTERPOLATE_AUTO) { | |
159 | $this->mode = $other->mode; | |
160 | } | |
161 | elseif ($other->mode === self::INTERPOLATE_AUTO) { | |
162 | // Noop. | |
163 | } | |
164 | elseif ($this->mode !== $other->mode) { | |
165 | // Mixing modes will lead to someone getting an expected substitution. | |
166 | throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode})."); | |
167 | } | |
168 | ||
169 | $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params'); | |
170 | foreach ($arrayFields as $f) { | |
171 | if ($parts === NULL || in_array($f, $parts)) { | |
172 | $this->{$f} = array_merge($this->{$f}, $other->{$f}); | |
173 | } | |
174 | } | |
175 | ||
176 | $flatFields = array('insertInto', 'from', 'limit', 'offset'); | |
177 | foreach ($flatFields as $f) { | |
178 | if ($parts === NULL || in_array($f, $parts)) { | |
179 | if ($other->{$f} !== NULL) { | |
180 | $this->{$f} = $other->{$f}; | |
181 | } | |
182 | } | |
183 | } | |
184 | ||
185 | return $this; | |
e2b5e5b1 TO |
186 | } |
187 | ||
c3a8e3e5 | 188 | /** |
fe482240 | 189 | * Add a new JOIN clause. |
c3a8e3e5 | 190 | * |
c5458931 TO |
191 | * Note: To add multiple JOINs at once, use $name===NULL and |
192 | * pass an array of $exprs. | |
193 | * | |
194 | * @param string|NULL $name | |
77855840 | 195 | * The effective alias of the joined table. |
c5458931 | 196 | * @param string|array $exprs |
77855840 | 197 | * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id"). |
c3a8e3e5 TO |
198 | * @param array|null $args |
199 | * @return CRM_Utils_SQL_Select | |
200 | */ | |
c5458931 TO |
201 | public function join($name, $exprs, $args = NULL) { |
202 | if ($name !== NULL) { | |
203 | $this->joins[$name] = $this->interpolate($exprs, $args); | |
204 | } | |
205 | else { | |
206 | foreach ($exprs as $name => $expr) { | |
207 | $this->joins[$name] = $this->interpolate($expr, $args); | |
208 | } | |
209 | return $this; | |
210 | } | |
e2b5e5b1 TO |
211 | return $this; |
212 | } | |
213 | ||
214 | /** | |
c3a8e3e5 TO |
215 | * Specify the column(s)/value(s) to return by adding to the SELECT clause |
216 | * | |
e2b5e5b1 TO |
217 | * @param string|array $exprs list of SQL expressions |
218 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable | |
219 | * @return CRM_Utils_SQL_Select | |
220 | */ | |
221 | public function select($exprs, $args = NULL) { | |
222 | $exprs = (array) $exprs; | |
223 | foreach ($exprs as $expr) { | |
224 | $this->selects[$expr] = $this->interpolate($expr, $args); | |
225 | } | |
226 | return $this; | |
227 | } | |
228 | ||
229 | /** | |
c3a8e3e5 TO |
230 | * Limit results by adding extra condition(s) to the WHERE clause |
231 | * | |
e2b5e5b1 TO |
232 | * @param string|array $exprs list of SQL expressions |
233 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable | |
234 | * @return CRM_Utils_SQL_Select | |
235 | */ | |
236 | public function where($exprs, $args = NULL) { | |
237 | $exprs = (array) $exprs; | |
238 | foreach ($exprs as $expr) { | |
239 | $this->wheres[$expr] = $this->interpolate($expr, $args); | |
240 | } | |
241 | return $this; | |
242 | } | |
243 | ||
244 | /** | |
fe482240 | 245 | * Group results by adding extra items to the GROUP BY clause. |
c3a8e3e5 | 246 | * |
e2b5e5b1 TO |
247 | * @param string|array $exprs list of SQL expressions |
248 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable | |
249 | * @return CRM_Utils_SQL_Select | |
250 | */ | |
251 | public function groupBy($exprs, $args = NULL) { | |
252 | $exprs = (array) $exprs; | |
253 | foreach ($exprs as $expr) { | |
254 | $this->groupBys[$expr] = $this->interpolate($expr, $args); | |
255 | } | |
256 | return $this; | |
257 | } | |
258 | ||
259 | /** | |
c3a8e3e5 TO |
260 | * Limit results by adding extra condition(s) to the HAVING clause |
261 | * | |
e2b5e5b1 TO |
262 | * @param string|array $exprs list of SQL expressions |
263 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable | |
264 | * @return CRM_Utils_SQL_Select | |
265 | */ | |
266 | public function having($exprs, $args = NULL) { | |
267 | $exprs = (array) $exprs; | |
268 | foreach ($exprs as $expr) { | |
269 | $this->havings[$expr] = $this->interpolate($expr, $args); | |
270 | } | |
271 | return $this; | |
272 | } | |
273 | ||
274 | /** | |
fe482240 | 275 | * Sort results by adding extra items to the ORDER BY clause. |
c3a8e3e5 | 276 | * |
e2b5e5b1 TO |
277 | * @param string|array $exprs list of SQL expressions |
278 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable | |
279 | * @return CRM_Utils_SQL_Select | |
280 | */ | |
281 | public function orderBy($exprs, $args = NULL) { | |
282 | $exprs = (array) $exprs; | |
283 | foreach ($exprs as $expr) { | |
284 | $this->orderBys[$expr] = $this->interpolate($expr, $args); | |
285 | } | |
286 | return $this; | |
287 | } | |
288 | ||
c5458931 TO |
289 | /** |
290 | * Set one (or multiple) parameters to interpolate into the query. | |
291 | * | |
292 | * @param array|string $keys | |
293 | * Key name, or an array of key-value pairs. | |
294 | * @param null|mixed $value | |
295 | * @return $this | |
296 | */ | |
297 | public function param($keys, $value = NULL) { | |
298 | if ($this->mode === self::INTERPOLATE_AUTO) { | |
299 | $this->mode = self::INTERPOLATE_OUTPUT; | |
300 | } | |
301 | elseif ($this->mode !== self::INTERPOLATE_OUTPUT) { | |
302 | throw new RuntimeException("Select::param() only makes sense when interpolating on output."); | |
303 | } | |
304 | ||
305 | if (is_array($keys)) { | |
306 | foreach ($keys as $k => $v) { | |
307 | $this->params[$k] = $v; | |
308 | } | |
309 | } | |
310 | else { | |
311 | $this->params[$keys] = $value; | |
312 | } | |
313 | return $this; | |
314 | } | |
315 | ||
c3a8e3e5 | 316 | /** |
fe482240 | 317 | * Set a limit on the number of records to return. |
c3a8e3e5 TO |
318 | * |
319 | * @param int $limit | |
320 | * @param int $offset | |
321 | * @return CRM_Utils_SQL_Select | |
322 | * @throws CRM_Core_Exception | |
323 | */ | |
324 | public function limit($limit, $offset = 0) { | |
325 | if ($limit !== NULL && !is_numeric($limit)) { | |
326 | throw new CRM_Core_Exception("Illegal limit"); | |
327 | } | |
328 | if ($offset !== NULL && !is_numeric($offset)) { | |
329 | throw new CRM_Core_Exception("Illegal offset"); | |
330 | } | |
331 | $this->limit = $limit; | |
332 | $this->offset = $offset; | |
333 | return $this; | |
334 | } | |
335 | ||
c5458931 TO |
336 | /** |
337 | * Insert the results of the SELECT query into another | |
338 | * table. | |
339 | * | |
340 | * @param string $table | |
341 | * The name of the other table (which receives new data). | |
342 | * @param array $fields | |
343 | * The fields to fill in the other table (in order). | |
344 | * @return $this | |
345 | * @see insertIntoField | |
346 | */ | |
347 | public function insertInto($table, $fields = array()) { | |
348 | $this->insertInto = $table; | |
349 | $this->insertIntoField($fields); | |
350 | return $this; | |
351 | } | |
352 | ||
353 | /** | |
354 | * @param array $fields | |
355 | * The fields to fill in the other table (in order). | |
356 | * @return $this | |
357 | */ | |
358 | public function insertIntoField($fields) { | |
359 | $fields = (array) $fields; | |
360 | foreach ($fields as $field) { | |
361 | $this->insertIntoFields[] = $field; | |
362 | } | |
363 | return $this; | |
364 | } | |
365 | ||
366 | /** | |
367 | * @param array|NULL $parts | |
368 | * List of fields to check (e.g. 'selects', 'joins'). | |
369 | * Defaults to all. | |
370 | * @return bool | |
371 | */ | |
372 | public function isEmpty($parts = NULL) { | |
373 | $empty = TRUE; | |
374 | $fields = array( | |
375 | 'insertInto', | |
376 | 'insertIntoFields', | |
377 | 'selects', | |
378 | 'from', | |
379 | 'joins', | |
380 | 'wheres', | |
381 | 'groupBys', | |
382 | 'havings', | |
383 | 'orderBys', | |
384 | 'limit', | |
385 | 'offset', | |
386 | ); | |
387 | if ($parts !== NULL) { | |
388 | $fields = array_intersect($fields, $parts); | |
389 | } | |
390 | foreach ($fields as $field) { | |
391 | if (!empty($this->{$field})) { | |
392 | $empty = FALSE; | |
393 | } | |
394 | } | |
395 | return $empty; | |
396 | } | |
397 | ||
398 | /** | |
399 | * Enable (or disable) strict mode. | |
400 | * | |
401 | * In strict mode, unknown variables will generate exceptions. | |
402 | * | |
403 | * @param bool $strict | |
404 | * @return $this | |
405 | */ | |
406 | public function strict($strict = TRUE) { | |
407 | $this->strict = $strict; | |
408 | return $this; | |
409 | } | |
410 | ||
e2b5e5b1 TO |
411 | /** |
412 | * Given a string like "field_name = @value", replace "@value" with an escaped SQL string | |
413 | * | |
acb1052e | 414 | * @param $expr SQL expression |
e2b5e5b1 TO |
415 | * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: |
416 | * prefix '@' => escape SQL | |
417 | * prefix '#' => literal number, skip escaping but do validation | |
418 | * prefix '!' => literal, skip escaping and validation | |
419 | * if a value is an array, then it will be imploded | |
420 | * | |
421 | * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. | |
422 | * | |
77b97be7 | 423 | * @throws CRM_Core_Exception |
a6c01b45 CW |
424 | * @return string |
425 | * SQL expression | |
e2b5e5b1 | 426 | */ |
c5458931 | 427 | public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) { |
e2b5e5b1 TO |
428 | if ($args === NULL) { |
429 | return $expr; | |
430 | } | |
431 | else { | |
c5458931 TO |
432 | if ($this->mode === self::INTERPOLATE_AUTO) { |
433 | $this->mode = $activeMode; | |
434 | } | |
435 | elseif ($activeMode !== $this->mode) { | |
436 | throw new RuntimeException("Cannot mix interpolation modes."); | |
437 | } | |
438 | ||
439 | $select = $this; | |
440 | return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) { | |
441 | if (isset($args[$m[2]])) { | |
442 | $values = $args[$m[2]]; | |
e2b5e5b1 | 443 | } |
c5458931 TO |
444 | elseif (isset($args[$m[1] . $m[2]])) { |
445 | // Backward compat. Keys in $args look like "#myNumber" or "@myString". | |
446 | $values = $args[$m[1] . $m[2]]; | |
e2b5e5b1 | 447 | } |
c5458931 TO |
448 | elseif ($select->strict) { |
449 | throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.'); | |
e2b5e5b1 TO |
450 | } |
451 | else { | |
c5458931 TO |
452 | // Unrecognized variables are ignored. Mitigate risk of accidents. |
453 | return $m[0]; | |
e2b5e5b1 | 454 | } |
c5458931 TO |
455 | $values = is_array($values) ? $values : array($values); |
456 | switch ($m[1]) { | |
457 | case '@': | |
458 | $parts = array_map(array($select, 'escapeString'), $values); | |
459 | return implode(', ', $parts); | |
460 | ||
461 | case '!': | |
462 | return implode(', ', $values); | |
463 | ||
464 | case '#': | |
465 | foreach ($values as $valueKey => $value) { | |
466 | if ($value === NULL) { | |
467 | $values[$valueKey] = 'NULL'; | |
468 | } | |
469 | elseif (!is_numeric($value)) { | |
470 | //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE)); | |
471 | throw new CRM_Core_Exception("Failed encoding non-numeric value"); | |
472 | } | |
473 | } | |
474 | return implode(', ', $values); | |
475 | ||
476 | default: | |
477 | throw new CRM_Core_Exception("Unrecognized prefix"); | |
478 | } | |
479 | }, $expr); | |
e2b5e5b1 TO |
480 | } |
481 | } | |
482 | ||
c3a8e3e5 TO |
483 | /** |
484 | * @param string|NULL $value | |
a6c01b45 CW |
485 | * @return string |
486 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
c3a8e3e5 | 487 | */ |
c5458931 | 488 | public function escapeString($value) { |
e2b5e5b1 TO |
489 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; |
490 | } | |
491 | ||
492 | /** | |
a6c01b45 CW |
493 | * @return string |
494 | * SQL statement | |
e2b5e5b1 TO |
495 | */ |
496 | public function toSQL() { | |
c5458931 TO |
497 | $sql = ''; |
498 | if ($this->insertInto) { | |
499 | $sql .= 'INSERT INTO ' . $this->insertInto . ' ('; | |
500 | $sql .= implode(', ', $this->insertIntoFields); | |
501 | $sql .= ")\n"; | |
502 | } | |
e2b5e5b1 | 503 | if ($this->selects) { |
c5458931 | 504 | $sql .= 'SELECT ' . implode(', ', $this->selects) . "\n"; |
e2b5e5b1 TO |
505 | } |
506 | else { | |
c5458931 TO |
507 | $sql .= 'SELECT *' . "\n"; |
508 | } | |
509 | if ($this->from !== NULL) { | |
510 | $sql .= 'FROM ' . $this->from . "\n"; | |
e2b5e5b1 | 511 | } |
e2b5e5b1 TO |
512 | foreach ($this->joins as $join) { |
513 | $sql .= $join . "\n"; | |
514 | } | |
515 | if ($this->wheres) { | |
516 | $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n"; | |
517 | } | |
518 | if ($this->groupBys) { | |
519 | $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n"; | |
520 | } | |
521 | if ($this->havings) { | |
522 | $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n"; | |
523 | } | |
524 | if ($this->orderBys) { | |
525 | $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n"; | |
526 | } | |
c3a8e3e5 TO |
527 | if ($this->limit !== NULL) { |
528 | $sql .= 'LIMIT ' . $this->limit . "\n"; | |
529 | if ($this->offset !== NULL) { | |
530 | $sql .= 'OFFSET ' . $this->offset . "\n"; | |
531 | } | |
532 | } | |
c5458931 TO |
533 | if ($this->mode === self::INTERPOLATE_OUTPUT) { |
534 | $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT); | |
535 | } | |
e2b5e5b1 TO |
536 | return $sql; |
537 | } | |
96025800 | 538 | |
c5458931 TO |
539 | public function offsetExists($offset) { |
540 | return isset($this->params[$offset]); | |
541 | } | |
542 | ||
543 | public function offsetGet($offset) { | |
544 | return $this->params[$offset]; | |
545 | } | |
546 | ||
547 | public function offsetSet($offset, $value) { | |
548 | $this->param($offset, $value); | |
549 | } | |
550 | ||
551 | public function offsetUnset($offset) { | |
552 | unset($this->params[$offset]); | |
553 | } | |
554 | ||
e2b5e5b1 | 555 | } |