CRM/Utils/SQL/Select.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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  * Dear God Why Do I Have To Write This (Dumb SQL Builder)
  30  *
  31  * Usage:
  32  * @code
  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']
  42  *      ))
  43  * echo $select->toSQL();
  44  * @endcode
  45  *
  46  * Design principles:
  47  *  - Portable
  48  *    - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
  49  *    - No knowledge of the underlying data model
  50  *  - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  51  *  - Variable escaping is concise and controllable based on prefixes, eg
  52  *    - similar to Drupal's t()
  53  *    - use "@varname" to insert the escaped value
  54  *    - use "!varname" to insert raw (unescaped) values
  55  *    - use "#varname" to insert a numerical value (these are validated but not escaped)
  56  *    - to disable any preprocessing, simply omit the variable list
  57  *    - control characters (@!#) are mandatory in expressions but optional in arg-keys
  58  *  - Variables may be individual values or arrays; arrays are imploded with commas
  59  *  - Conditionals are AND'd; if you need OR's, do it yourself
  60  *  - Use classes/functions with documentation (rather than undocumented array-trees)
  61  *  - For any given string, interpolation is only performed once. After an interpolation,
  62  *    a string may never again be subjected to interpolation.
  63  *
  64  * The "interpolate-once" principle can be enforced by either interpolating on input
  65  * xor output. The notations for input and output interpolation are a bit different,
  66  * and they may not be mixed.
  67  *
  68  * @code
  69  * // Interpolate on input. Set params when using them.
  70  * $select->where('activity_type_id = #type', array(
  71  *   'type' => 234,
  72  * ));
  73  *
  74  * // Interpolate on output. Set params independently.
  75  * $select
  76  *     ->where('activity_type_id = #type')
  77  *     ->param('type', 234),
  78  * @endcode
  79  *
  80  * @package CRM
  81  * @copyright CiviCRM LLC (c) 2004-2018
  82  */
  83 class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
  84
  85   private $insertInto = NULL;
  86   private $insertVerb = 'INSERT INTO ';
  87   private $insertIntoFields = array();
  88   private $selects = array();
  89   private $from;
  90   private $joins = array();
  91   private $wheres = array();
  92   private $groupBys = array();
  93   private $havings = array();
  94   private $orderBys = array();
  95   private $limit = NULL;
  96   private $offset = NULL;
  97   private $distinct = NULL;
  98
  99   /**
 100    * Create a new SELECT query.
 101    *
 102    * @param string $from
 103    *   Table-name and optional alias.
 104    * @param array $options
 105    * @return CRM_Utils_SQL_Select
 106    */
 107   public static function from($from, $options = array()) {
 108     return new self($from, $options);
 109   }
 110
 111   /**
 112    * Create a partial SELECT query.
 113    *
 114    * @param array $options
 115    * @return CRM_Utils_SQL_Select
 116    */
 117   public static function fragment($options = array()) {
 118     return new self(NULL, $options);
 119   }
 120
 121   /**
 122    * Create a new SELECT query.
 123    *
 124    * @param string $from
 125    *   Table-name and optional alias.
 126    * @param array $options
 127    */
 128   public function __construct($from, $options = array()) {
 129     $this->from = $from;
 130     $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO;
 131   }
 132
 133   /**
 134    * Make a new copy of this query.
 135    *
 136    * @return CRM_Utils_SQL_Select
 137    */
 138   public function copy() {
 139     return clone $this;
 140   }
 141
 142   /**
 143    * Merge something or other.
 144    *
 145    * @param CRM_Utils_SQL_Select $other
 146    * @param array|NULL $parts
 147    *   ex: 'joins', 'wheres'
 148    * @return CRM_Utils_SQL_Select
 149    */
 150   public function merge($other, $parts = NULL) {
 151     if ($other === NULL) {
 152       return $this;
 153     }
 154
 155     if ($this->mode === self::INTERPOLATE_AUTO) {
 156       $this->mode = $other->mode;
 157     }
 158     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 159       // Noop.
 160     }
 161     elseif ($this->mode !== $other->mode) {
 162       // Mixing modes will lead to someone getting an expected substitution.
 163       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 164     }
 165
 166     $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params');
 167     foreach ($arrayFields as $f) {
 168       if ($parts === NULL || in_array($f, $parts)) {
 169         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 170       }
 171     }
 172
 173     $flatFields = array('insertInto', 'from', 'limit', 'offset');
 174     foreach ($flatFields as $f) {
 175       if ($parts === NULL || in_array($f, $parts)) {
 176         if ($other->{$f} !== NULL) {
 177           $this->{$f} = $other->{$f};
 178         }
 179       }
 180     }
 181
 182     return $this;
 183   }
 184
 185   /**
 186    * Add a new JOIN clause.
 187    *
 188    * Note: To add multiple JOINs at once, use $name===NULL and
 189    * pass an array of $exprs.
 190    *
 191    * @param string|NULL $name
 192    *   The effective alias of the joined table.
 193    * @param string|array $exprs
 194    *   The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
 195    * @param array|null $args
 196    * @return CRM_Utils_SQL_Select
 197    */
 198   public function join($name, $exprs, $args = NULL) {
 199     if ($name !== NULL) {
 200       $this->joins[$name] = $this->interpolate($exprs, $args);
 201     }
 202     else {
 203       foreach ($exprs as $name => $expr) {
 204         $this->joins[$name] = $this->interpolate($expr, $args);
 205       }
 206       return $this;
 207     }
 208     return $this;
 209   }
 210
 211   /**
 212    * Specify the column(s)/value(s) to return by adding to the SELECT clause
 213    *
 214    * @param string|array $exprs list of SQL expressions
 215    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 216    * @return CRM_Utils_SQL_Select
 217    */
 218   public function select($exprs, $args = NULL) {
 219     $exprs = (array) $exprs;
 220     foreach ($exprs as $expr) {
 221       $this->selects[] = $this->interpolate($expr, $args);
 222     }
 223     return $this;
 224   }
 225
 226   /**
 227    * Return only distinct values
 228    *
 229    * @param bool $isDistinct allow DISTINCT select or not
 230    * @return CRM_Utils_SQL_Select
 231    */
 232   public function distinct($isDistinct = TRUE) {
 233     if ($isDistinct) {
 234       $this->distinct = 'DISTINCT ';
 235     }
 236     return $this;
 237   }
 238
 239   /**
 240    * Limit results by adding extra condition(s) to the WHERE clause
 241    *
 242    * @param string|array $exprs list of SQL expressions
 243    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 244    * @return CRM_Utils_SQL_Select
 245    */
 246   public function where($exprs, $args = NULL) {
 247     $exprs = (array) $exprs;
 248     foreach ($exprs as $expr) {
 249       $evaluatedExpr = $this->interpolate($expr, $args);
 250       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 251     }
 252     return $this;
 253   }
 254
 255   /**
 256    * Group results by adding extra items to the GROUP BY clause.
 257    *
 258    * @param string|array $exprs list of SQL expressions
 259    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 260    * @return CRM_Utils_SQL_Select
 261    */
 262   public function groupBy($exprs, $args = NULL) {
 263     $exprs = (array) $exprs;
 264     foreach ($exprs as $expr) {
 265       $evaluatedExpr = $this->interpolate($expr, $args);
 266       $this->groupBys[$evaluatedExpr] = $evaluatedExpr;
 267     }
 268     return $this;
 269   }
 270
 271   /**
 272    * Limit results by adding extra condition(s) to the HAVING clause
 273    *
 274    * @param string|array $exprs list of SQL expressions
 275    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 276    * @return CRM_Utils_SQL_Select
 277    */
 278   public function having($exprs, $args = NULL) {
 279     $exprs = (array) $exprs;
 280     foreach ($exprs as $expr) {
 281       $evaluatedExpr = $this->interpolate($expr, $args);
 282       $this->havings[$evaluatedExpr] = $evaluatedExpr;
 283     }
 284     return $this;
 285   }
 286
 287   /**
 288    * Sort results by adding extra items to the ORDER BY clause.
 289    *
 290    * @param string|array $exprs list of SQL expressions
 291    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 292    * @param int $weight
 293    * @return \CRM_Utils_SQL_Select
 294    */
 295   public function orderBy($exprs, $args = NULL, $weight = 0) {
 296     static $guid = 0;
 297     $exprs = (array) $exprs;
 298     foreach ($exprs as $expr) {
 299       $evaluatedExpr = $this->interpolate($expr, $args);
 300       $this->orderBys[$evaluatedExpr] = array('value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++);
 301     }
 302     return $this;
 303   }
 304
 305   /**
 306    * Set one (or multiple) parameters to interpolate into the query.
 307    *
 308    * @param array|string $keys
 309    *   Key name, or an array of key-value pairs.
 310    * @param null|mixed $value
 311    *   The new value of the parameter.
 312    *   Values may be strings, ints, or arrays thereof -- provided that the
 313    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 314    * @return \CRM_Utils_SQL_Select
 315    */
 316   public function param($keys, $value = NULL) {
 317     // Why bother with an override? To provide bett er type-hinting in `@return`.
 318     return parent::param($keys, $value);
 319   }
 320
 321   /**
 322    * Set a limit on the number of records to return.
 323    *
 324    * @param int $limit
 325    * @param int $offset
 326    * @return CRM_Utils_SQL_Select
 327    * @throws CRM_Core_Exception
 328    */
 329   public function limit($limit, $offset = 0) {
 330     if ($limit !== NULL && !is_numeric($limit)) {
 331       throw new CRM_Core_Exception("Illegal limit");
 332     }
 333     if ($offset !== NULL && !is_numeric($offset)) {
 334       throw new CRM_Core_Exception("Illegal offset");
 335     }
 336     $this->limit = $limit;
 337     $this->offset = $offset;
 338     return $this;
 339   }
 340
 341   /**
 342    * Insert the results of the SELECT query into another
 343    * table.
 344    *
 345    * @param string $table
 346    *   The name of the other table (which receives new data).
 347    * @param array $fields
 348    *   The fields to fill in the other table (in order).
 349    * @return CRM_Utils_SQL_Select
 350    * @see insertIntoField
 351    */
 352   public function insertInto($table, $fields = array()) {
 353     $this->insertInto = $table;
 354     $this->insertIntoField($fields);
 355     return $this;
 356   }
 357
 358   /**
 359    * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
 360    *
 361    * @param string $table
 362    *   The name of the other table (which receives new data).
 363    * @param array $fields
 364    *   The fields to fill in the other table (in order).
 365    * @return CRM_Utils_SQL_Select
 366    */
 367   public function insertIgnoreInto($table, $fields = array()) {
 368     $this->insertVerb = "INSERT IGNORE INTO ";
 369     return $this->insertInto($table, $fields);
 370   }
 371
 372   /**
 373    * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
 374    *
 375    * @param string $table
 376    *   The name of the other table (which receives new data).
 377    * @param array $fields
 378    *   The fields to fill in the other table (in order).
 379    */
 380   public function replaceInto($table, $fields = array()) {
 381     $this->insertVerb = "REPLACE INTO ";
 382     return $this->insertInto($table, $fields);
 383   }
 384
 385
 386   /**
 387    * @param array $fields
 388    *   The fields to fill in the other table (in order).
 389    * @return CRM_Utils_SQL_Select
 390    */
 391   public function insertIntoField($fields) {
 392     $fields = (array) $fields;
 393     foreach ($fields as $field) {
 394       $this->insertIntoFields[] = $field;
 395     }
 396     return $this;
 397   }
 398
 399   /**
 400    * @param array|NULL $parts
 401    *   List of fields to check (e.g. 'selects', 'joins').
 402    *   Defaults to all.
 403    * @return bool
 404    */
 405   public function isEmpty($parts = NULL) {
 406     $empty = TRUE;
 407     $fields = array(
 408       'insertInto',
 409       'insertIntoFields',
 410       'selects',
 411       'from',
 412       'joins',
 413       'wheres',
 414       'groupBys',
 415       'havings',
 416       'orderBys',
 417       'limit',
 418       'offset',
 419     );
 420     if ($parts !== NULL) {
 421       $fields = array_intersect($fields, $parts);
 422     }
 423     foreach ($fields as $field) {
 424       if (!empty($this->{$field})) {
 425         $empty = FALSE;
 426       }
 427     }
 428     return $empty;
 429   }
 430
 431   /**
 432    * @return string
 433    *   SQL statement
 434    */
 435   public function toSQL() {
 436     $sql = '';
 437     if ($this->insertInto) {
 438       $sql .= $this->insertVerb . $this->insertInto . ' (';
 439       $sql .= implode(', ', $this->insertIntoFields);
 440       $sql .= ")\n";
 441     }
 442
 443     if ($this->selects) {
 444       $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n";
 445     }
 446     else {
 447       $sql .= 'SELECT *' . "\n";
 448     }
 449     if ($this->from !== NULL) {
 450       $sql .= 'FROM ' . $this->from . "\n";
 451     }
 452     foreach ($this->joins as $join) {
 453       $sql .= $join . "\n";
 454     }
 455     if ($this->wheres) {
 456       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 457     }
 458     if ($this->groupBys) {
 459       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 460     }
 461     if ($this->havings) {
 462       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 463     }
 464     if ($this->orderBys) {
 465       $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys,
 466         array('weight', 'guid'));
 467       $orderBys = CRM_Utils_Array::collect('value', $orderBys);
 468       $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
 469     }
 470     if ($this->limit !== NULL) {
 471       $sql .= 'LIMIT ' . $this->limit . "\n";
 472       if ($this->offset !== NULL) {
 473         $sql .= 'OFFSET ' . $this->offset . "\n";
 474       }
 475     }
 476     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 477       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 478     }
 479     return $sql;
 480   }
 481
 482   /**
 483    * Execute the query.
 484    *
 485    * To examine the results, use a function like `fetch()`, `fetchAll()`,
 486    * `fetchValue()`, or `fetchMap()`.
 487    *
 488    * @param string|NULL $daoName
 489    *   The return object should be an instance of this class.
 490    *   Ex: 'CRM_Contact_BAO_Contact'.
 491    * @param bool $i18nRewrite
 492    *   If the system has multilingual features, should the field/table
 493    *   names be rewritten?
 494    * @return CRM_Core_DAO
 495    * @see CRM_Core_DAO::executeQuery
 496    * @see CRM_Core_I18n_Schema::rewriteQuery
 497    */
 498   public function execute($daoName = NULL, $i18nRewrite = TRUE) {
 499     // Don't pass through $params. toSQL() handles interpolation.
 500     $params = array();
 501
 502     // Don't pass through $abort, $trapException. Just use straight-up exceptions.
 503     $abort = TRUE;
 504     $trapException = FALSE;
 505     $errorScope = CRM_Core_TemporaryErrorScope::useException();
 506
 507     // Don't pass through freeDAO. You can do it yourself.
 508     $freeDAO = FALSE;
 509
 510     return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName,
 511       $freeDAO, $i18nRewrite, $trapException);
 512   }
 513
 514 }