3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
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 +--------------------------------------------------------------------+
29 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
33 * $select = CRM_Utils_SQL_Select::from('civicrm_activity act')
34 * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
35 * ->where('activity_type_id = #type', array('type' => 234))
36 * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
37 * ->where('subject like @subj', array('subj' => '%hello%'))
38 * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
39 * ->where('!column = @value', array(
40 * 'column' => $customField->column_name,
41 * 'value' => $form['foo']
43 * echo $select->toSQL();
48 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
49 * - No knowledge of the underlying data model
51 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
52 * - Variable escaping is concise and controllable based on prefixes, eg
53 * - similar to Drupal's t()
54 * - use "@varname" to insert the escaped value
55 * - use "!varname" to insert raw (unescaped) values
56 * - use "#varname" to insert a numerical value (these are validated but not escaped)
57 * - to disable any preprocessing, simply omit the variable list
58 * - control characters (@!#) are mandatory in expressions but optional in arg-keys
59 * - Variables may be individual values or arrays; arrays are imploded with commas
60 * - Conditionals are AND'd; if you need OR's, do it yourself
61 * - Use classes/functions with documentation (rather than undocumented array-trees)
62 * - For any given string, interpolation is only performed once. After an interpolation,
63 * a string may never again be subjected to interpolation.
65 * The "interpolate-once" principle can be enforced by either interpolating on input
66 * xor output. The notations for input and output interpolation are a bit different,
67 * and they may not be mixed.
70 * // Interpolate on input. Set params when using them.
71 * $select->where('activity_type_id = #type', array(
75 * // Interpolate on output. Set params independently.
77 * ->where('activity_type_id = #type')
78 * ->param('type', 234),
82 * @copyright CiviCRM LLC (c) 2004-2018
84 class CRM_Utils_SQL_Select
implements ArrayAccess
{
87 * Interpolate values as soon as they are passed in (where(), join(), etc).
91 * Pro: Every clause has its own unique namespace for parameters.
92 * Con: Probably slower.
93 * Advice: Use this when aggregating SQL fragments from agents who
94 * maintained by different parties.
96 const INTERPOLATE_INPUT
= 'in';
99 * Interpolate values when rendering SQL output (toSQL()).
101 * Pro: Probably faster.
102 * Con: Must maintain an aggregated list of all parameters.
103 * Advice: Use this when you have control over the entire query.
105 const INTERPOLATE_OUTPUT
= 'out';
108 * Determine mode automatically. When the first attempt is made
109 * to use input-interpolation (eg `where(..., array(...))`) or
110 * output-interpolation (eg `param(...)`), the mode will be
111 * set. Subsequent calls will be validated using the same mode.
113 const INTERPOLATE_AUTO
= 'auto';
115 private $mode = NULL;
116 private $insertInto = NULL;
117 private $insertVerb = 'INSERT INTO ';
118 private $insertIntoFields = array();
119 private $selects = array();
121 private $joins = array();
122 private $wheres = array();
123 private $groupBys = array();
124 private $havings = array();
125 private $orderBys = array();
126 private $limit = NULL;
127 private $offset = NULL;
128 private $params = array();
129 private $distinct = NULL;
131 // Public to work-around PHP 5.3 limit.
132 public $strict = NULL;
135 * Create a new SELECT query.
137 * @param string $from
138 * Table-name and optional alias.
139 * @param array $options
140 * @return CRM_Utils_SQL_Select
142 public static function from($from, $options = array()) {
143 return new self($from, $options);
147 * Create a partial SELECT query.
149 * @param array $options
150 * @return CRM_Utils_SQL_Select
152 public static function fragment($options = array()) {
153 return new self(NULL, $options);
157 * Create a new SELECT query.
159 * @param string $from
160 * Table-name and optional alias.
161 * @param array $options
163 public function __construct($from, $options = array()) {
165 $this->mode
= isset($options['mode']) ?
$options['mode'] : self
::INTERPOLATE_AUTO
;
169 * Make a new copy of this query.
171 * @return CRM_Utils_SQL_Select
173 public function copy() {
178 * Merge something or other.
180 * @param CRM_Utils_SQL_Select $other
181 * @param array|NULL $parts
182 * ex: 'joins', 'wheres'
183 * @return CRM_Utils_SQL_Select
185 public function merge($other, $parts = NULL) {
186 if ($other === NULL) {
190 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
191 $this->mode
= $other->mode
;
193 elseif ($other->mode
=== self
::INTERPOLATE_AUTO
) {
196 elseif ($this->mode
!== $other->mode
) {
197 // Mixing modes will lead to someone getting an expected substitution.
198 throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
201 $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params');
202 foreach ($arrayFields as $f) {
203 if ($parts === NULL ||
in_array($f, $parts)) {
204 $this->{$f} = array_merge($this->{$f}, $other->{$f});
208 $flatFields = array('insertInto', 'from', 'limit', 'offset');
209 foreach ($flatFields as $f) {
210 if ($parts === NULL ||
in_array($f, $parts)) {
211 if ($other->{$f} !== NULL) {
212 $this->{$f} = $other->{$f};
221 * Add a new JOIN clause.
223 * Note: To add multiple JOINs at once, use $name===NULL and
224 * pass an array of $exprs.
226 * @param string|NULL $name
227 * The effective alias of the joined table.
228 * @param string|array $exprs
229 * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
230 * @param array|null $args
231 * @return CRM_Utils_SQL_Select
233 public function join($name, $exprs, $args = NULL) {
234 if ($name !== NULL) {
235 $this->joins
[$name] = $this->interpolate($exprs, $args);
238 foreach ($exprs as $name => $expr) {
239 $this->joins
[$name] = $this->interpolate($expr, $args);
247 * Specify the column(s)/value(s) to return by adding to the SELECT clause
249 * @param string|array $exprs list of SQL expressions
250 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
251 * @return CRM_Utils_SQL_Select
253 public function select($exprs, $args = NULL) {
254 $exprs = (array) $exprs;
255 foreach ($exprs as $expr) {
256 $this->selects
[] = $this->interpolate($expr, $args);
262 * Return only distinct values
264 * @param bool $isDistinct allow DISTINCT select or not
265 * @return CRM_Utils_SQL_Select
267 public function distinct($isDistinct = TRUE) {
269 $this->distinct
= 'DISTINCT ';
275 * Limit results by adding extra condition(s) to the WHERE clause
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
281 public function where($exprs, $args = NULL) {
282 $exprs = (array) $exprs;
283 foreach ($exprs as $expr) {
284 $evaluatedExpr = $this->interpolate($expr, $args);
285 $this->wheres
[$evaluatedExpr] = $evaluatedExpr;
291 * Group results by adding extra items to the GROUP BY clause.
293 * @param string|array $exprs list of SQL expressions
294 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
295 * @return CRM_Utils_SQL_Select
297 public function groupBy($exprs, $args = NULL) {
298 $exprs = (array) $exprs;
299 foreach ($exprs as $expr) {
300 $evaluatedExpr = $this->interpolate($expr, $args);
301 $this->groupBys
[$evaluatedExpr] = $evaluatedExpr;
307 * Limit results by adding extra condition(s) to the HAVING clause
309 * @param string|array $exprs list of SQL expressions
310 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
311 * @return CRM_Utils_SQL_Select
313 public function having($exprs, $args = NULL) {
314 $exprs = (array) $exprs;
315 foreach ($exprs as $expr) {
316 $evaluatedExpr = $this->interpolate($expr, $args);
317 $this->havings
[$evaluatedExpr] = $evaluatedExpr;
323 * Sort results by adding extra items to the ORDER BY clause.
325 * @param string|array $exprs list of SQL expressions
326 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
328 * @return \CRM_Utils_SQL_Select
330 public function orderBy($exprs, $args = NULL, $weight = 0) {
332 $exprs = (array) $exprs;
333 foreach ($exprs as $expr) {
334 $evaluatedExpr = $this->interpolate($expr, $args);
335 $this->orderBys
[$evaluatedExpr] = array('value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++
);
341 * Set one (or multiple) parameters to interpolate into the query.
343 * @param array|string $keys
344 * Key name, or an array of key-value pairs.
345 * @param null|mixed $value
346 * The new value of the parameter.
347 * Values may be strings, ints, or arrays thereof -- provided that the
348 * SQL query uses appropriate prefix (e.g. "@", "!", "#").
349 * @return \CRM_Utils_SQL_Select
351 public function param($keys, $value = NULL) {
352 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
353 $this->mode
= self
::INTERPOLATE_OUTPUT
;
355 elseif ($this->mode
!== self
::INTERPOLATE_OUTPUT
) {
356 throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
359 if (is_array($keys)) {
360 foreach ($keys as $k => $v) {
361 $this->params
[$k] = $v;
365 $this->params
[$keys] = $value;
371 * Set a limit on the number of records to return.
375 * @return CRM_Utils_SQL_Select
376 * @throws CRM_Core_Exception
378 public function limit($limit, $offset = 0) {
379 if ($limit !== NULL && !is_numeric($limit)) {
380 throw new CRM_Core_Exception("Illegal limit");
382 if ($offset !== NULL && !is_numeric($offset)) {
383 throw new CRM_Core_Exception("Illegal offset");
385 $this->limit
= $limit;
386 $this->offset
= $offset;
391 * Insert the results of the SELECT query into another
394 * @param string $table
395 * The name of the other table (which receives new data).
396 * @param array $fields
397 * The fields to fill in the other table (in order).
398 * @return CRM_Utils_SQL_Select
399 * @see insertIntoField
401 public function insertInto($table, $fields = array()) {
402 $this->insertInto
= $table;
403 $this->insertIntoField($fields);
408 * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
410 * @param string $table
411 * The name of the other table (which receives new data).
412 * @param array $fields
413 * The fields to fill in the other table (in order).
414 * @return CRM_Utils_SQL_Select
416 public function insertIgnoreInto($table, $fields = array()) {
417 $this->insertVerb
= "INSERT IGNORE INTO ";
418 return $this->insertInto($table, $fields);
422 * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
424 * @param string $table
425 * The name of the other table (which receives new data).
426 * @param array $fields
427 * The fields to fill in the other table (in order).
429 public function replaceInto($table, $fields = array()) {
430 $this->insertVerb
= "REPLACE INTO ";
431 return $this->insertInto($table, $fields);
436 * @param array $fields
437 * The fields to fill in the other table (in order).
438 * @return CRM_Utils_SQL_Select
440 public function insertIntoField($fields) {
441 $fields = (array) $fields;
442 foreach ($fields as $field) {
443 $this->insertIntoFields
[] = $field;
449 * @param array|NULL $parts
450 * List of fields to check (e.g. 'selects', 'joins').
454 public function isEmpty($parts = NULL) {
469 if ($parts !== NULL) {
470 $fields = array_intersect($fields, $parts);
472 foreach ($fields as $field) {
473 if (!empty($this->{$field})) {
481 * Enable (or disable) strict mode.
483 * In strict mode, unknown variables will generate exceptions.
485 * @param bool $strict
486 * @return CRM_Utils_SQL_Select
488 public function strict($strict = TRUE) {
489 $this->strict
= $strict;
494 * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
496 * @param string $expr SQL expression
497 * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
498 * prefix '@' => escape SQL
499 * prefix '#' => literal number, skip escaping but do validation
500 * prefix '!' => literal, skip escaping and validation
501 * if a value is an array, then it will be imploded
503 * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
505 * @param string $activeMode
509 public function interpolate($expr, $args, $activeMode = self
::INTERPOLATE_INPUT
) {
510 if ($args === NULL) {
514 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
515 $this->mode
= $activeMode;
517 elseif ($activeMode !== $this->mode
) {
518 throw new RuntimeException("Cannot mix interpolation modes.");
522 return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) {
523 if (isset($args[$m[2]])) {
524 $values = $args[$m[2]];
526 elseif (isset($args[$m[1] . $m[2]])) {
527 // Backward compat. Keys in $args look like "#myNumber" or "@myString".
528 $values = $args[$m[1] . $m[2]];
530 elseif ($select->strict
) {
531 throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
534 // Unrecognized variables are ignored. Mitigate risk of accidents.
537 $values = is_array($values) ?
$values : array($values);
540 $parts = array_map(array($select, 'escapeString'), $values);
541 return implode(', ', $parts);
543 // TODO: ensure all uses of this un-escaped literal are safe
545 return implode(', ', $values);
548 foreach ($values as $valueKey => $value) {
549 if ($value === NULL) {
550 $values[$valueKey] = 'NULL';
552 elseif (!is_numeric($value)) {
553 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE));
554 throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")");
557 return implode(', ', $values);
560 throw new CRM_Core_Exception("Unrecognized prefix");
567 * @param string|NULL $value
569 * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
571 public function escapeString($value) {
572 return $value === NULL ?
'NULL' : '"' . CRM_Core_DAO
::escapeString($value) . '"';
579 public function toSQL() {
581 if ($this->insertInto
) {
582 $sql .= $this->insertVerb
. $this->insertInto
. ' (';
583 $sql .= implode(', ', $this->insertIntoFields
);
587 if ($this->selects
) {
588 $sql .= 'SELECT ' . $this->distinct
. implode(', ', $this->selects
) . "\n";
591 $sql .= 'SELECT *' . "\n";
593 if ($this->from
!== NULL) {
594 $sql .= 'FROM ' . $this->from
. "\n";
596 foreach ($this->joins
as $join) {
597 $sql .= $join . "\n";
600 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
602 if ($this->groupBys
) {
603 $sql .= 'GROUP BY ' . implode(', ', $this->groupBys
) . "\n";
605 if ($this->havings
) {
606 $sql .= 'HAVING (' . implode(') AND (', $this->havings
) . ")\n";
608 if ($this->orderBys
) {
609 $orderBys = CRM_Utils_Array
::crmArraySortByField($this->orderBys
,
610 array('weight', 'guid'));
611 $orderBys = CRM_Utils_Array
::collect('value', $orderBys);
612 $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
614 if ($this->limit
!== NULL) {
615 $sql .= 'LIMIT ' . $this->limit
. "\n";
616 if ($this->offset
!== NULL) {
617 $sql .= 'OFFSET ' . $this->offset
. "\n";
620 if ($this->mode
=== self
::INTERPOLATE_OUTPUT
) {
621 $sql = $this->interpolate($sql, $this->params
, self
::INTERPOLATE_OUTPUT
);
629 * To examine the results, use a function like `fetch()`, `fetchAll()`,
630 * `fetchValue()`, or `fetchMap()`.
632 * @param string|NULL $daoName
633 * The return object should be an instance of this class.
634 * Ex: 'CRM_Contact_BAO_Contact'.
635 * @param bool $i18nRewrite
636 * If the system has multilingual features, should the field/table
637 * names be rewritten?
638 * @return CRM_Core_DAO
639 * @see CRM_Core_DAO::executeQuery
640 * @see CRM_Core_I18n_Schema::rewriteQuery
642 public function execute($daoName = NULL, $i18nRewrite = TRUE) {
643 // Don't pass through $params. toSQL() handles interpolation.
646 // Don't pass through $abort, $trapException. Just use straight-up exceptions.
648 $trapException = FALSE;
649 $errorScope = CRM_Core_TemporaryErrorScope
::useException();
651 // Don't pass through freeDAO. You can do it yourself.
654 return CRM_Core_DAO
::executeQuery($this->toSQL(), $params, $abort, $daoName,
655 $freeDAO, $i18nRewrite, $trapException);
659 * Has an offset been set.
661 * @param string $offset
665 public function offsetExists($offset) {
666 return isset($this->params
[$offset]);
670 * Get the value of a SQL parameter.
673 * $select['cid'] = 123;
674 * $select->where('contact.id = #cid');
675 * echo $select['cid'];
678 * @param string $offset
681 * @see ArrayAccess::offsetGet
683 public function offsetGet($offset) {
684 return $this->params
[$offset];
688 * Set the value of a SQL parameter.
691 * $select['cid'] = 123;
692 * $select->where('contact.id = #cid');
693 * echo $select['cid'];
696 * @param string $offset
697 * @param mixed $value
698 * The new value of the parameter.
699 * Values may be strings, ints, or arrays thereof -- provided that the
700 * SQL query uses appropriate prefix (e.g. "@", "!", "#").
702 * @see ArrayAccess::offsetSet
704 public function offsetSet($offset, $value) {
705 $this->param($offset, $value);
709 * Unset the value of a SQL parameter.
711 * @param string $offset
713 * @see ArrayAccess::offsetUnset
715 public function offsetUnset($offset) {
716 unset($this->params
[$offset]);