CRM/Utils/SQL/Select.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  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 = [];
  88   private $selects = [];
  89   private $from;
  90   private $joins = [];
  91   private $wheres = [];
  92   private $groupBys = [];
  93   private $havings = [];
  94   private $orderBys = [];
  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 = []) {
 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 = []) {
 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 = []) {
 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 array|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 (is_array($other)) {
 156       foreach ($other as $fragment) {
 157         $this->merge($fragment, $parts);
 158       }
 159       return $this;
 160     }
 161
 162     if ($this->mode === self::INTERPOLATE_AUTO) {
 163       $this->mode = $other->mode;
 164     }
 165     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 166       // Noop.
 167     }
 168     elseif ($this->mode !== $other->mode) {
 169       // Mixing modes will lead to someone getting an expected substitution.
 170       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 171     }
 172
 173     $arrayFields = ['insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params'];
 174     foreach ($arrayFields as $f) {
 175       if ($parts === NULL || in_array($f, $parts)) {
 176         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 177       }
 178     }
 179
 180     $flatFields = ['insertInto', 'from', 'limit', 'offset'];
 181     foreach ($flatFields as $f) {
 182       if ($parts === NULL || in_array($f, $parts)) {
 183         if ($other->{$f} !== NULL) {
 184           $this->{$f} = $other->{$f};
 185         }
 186       }
 187     }
 188
 189     return $this;
 190   }
 191
 192   /**
 193    * Add a new JOIN clause.
 194    *
 195    * Note: To add multiple JOINs at once, use $name===NULL and
 196    * pass an array of $exprs.
 197    *
 198    * @param string|NULL $name
 199    *   The effective alias of the joined table.
 200    * @param string|array $exprs
 201    *   The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
 202    * @param array|null $args
 203    * @return CRM_Utils_SQL_Select
 204    */
 205   public function join($name, $exprs, $args = NULL) {
 206     if ($name !== NULL) {
 207       $this->joins[$name] = $this->interpolate($exprs, $args);
 208     }
 209     else {
 210       foreach ($exprs as $name => $expr) {
 211         $this->joins[$name] = $this->interpolate($expr, $args);
 212       }
 213       return $this;
 214     }
 215     return $this;
 216   }
 217
 218   /**
 219    * Specify the column(s)/value(s) to return by adding to the SELECT clause
 220    *
 221    * @param string|array $exprs list of SQL expressions
 222    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 223    * @return CRM_Utils_SQL_Select
 224    */
 225   public function select($exprs, $args = NULL) {
 226     $exprs = (array) $exprs;
 227     foreach ($exprs as $expr) {
 228       $this->selects[] = $this->interpolate($expr, $args);
 229     }
 230     return $this;
 231   }
 232
 233   /**
 234    * Return only distinct values
 235    *
 236    * @param bool $isDistinct allow DISTINCT select or not
 237    * @return CRM_Utils_SQL_Select
 238    */
 239   public function distinct($isDistinct = TRUE) {
 240     if ($isDistinct) {
 241       $this->distinct = 'DISTINCT ';
 242     }
 243     return $this;
 244   }
 245
 246   /**
 247    * Limit results by adding extra condition(s) to the WHERE clause
 248    *
 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
 252    */
 253   public function where($exprs, $args = NULL) {
 254     $exprs = (array) $exprs;
 255     foreach ($exprs as $expr) {
 256       $evaluatedExpr = $this->interpolate($expr, $args);
 257       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 258     }
 259     return $this;
 260   }
 261
 262   /**
 263    * Group results by adding extra items to the GROUP BY clause.
 264    *
 265    * @param string|array $exprs list of SQL expressions
 266    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 267    * @return CRM_Utils_SQL_Select
 268    */
 269   public function groupBy($exprs, $args = NULL) {
 270     $exprs = (array) $exprs;
 271     foreach ($exprs as $expr) {
 272       $evaluatedExpr = $this->interpolate($expr, $args);
 273       $this->groupBys[$evaluatedExpr] = $evaluatedExpr;
 274     }
 275     return $this;
 276   }
 277
 278   /**
 279    * Limit results by adding extra condition(s) to the HAVING clause
 280    *
 281    * @param string|array $exprs list of SQL expressions
 282    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 283    * @return CRM_Utils_SQL_Select
 284    */
 285   public function having($exprs, $args = NULL) {
 286     $exprs = (array) $exprs;
 287     foreach ($exprs as $expr) {
 288       $evaluatedExpr = $this->interpolate($expr, $args);
 289       $this->havings[$evaluatedExpr] = $evaluatedExpr;
 290     }
 291     return $this;
 292   }
 293
 294   /**
 295    * Sort results by adding extra items to the ORDER BY clause.
 296    *
 297    * @param string|array $exprs list of SQL expressions
 298    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 299    * @param int $weight
 300    * @return \CRM_Utils_SQL_Select
 301    */
 302   public function orderBy($exprs, $args = NULL, $weight = 0) {
 303     static $guid = 0;
 304     $exprs = (array) $exprs;
 305     foreach ($exprs as $expr) {
 306       $evaluatedExpr = $this->interpolate($expr, $args);
 307       $this->orderBys[$evaluatedExpr] = ['value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++];
 308     }
 309     return $this;
 310   }
 311
 312   /**
 313    * Set one (or multiple) parameters to interpolate into the query.
 314    *
 315    * @param array|string $keys
 316    *   Key name, or an array of key-value pairs.
 317    * @param null|mixed $value
 318    *   The new value of the parameter.
 319    *   Values may be strings, ints, or arrays thereof -- provided that the
 320    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 321    * @return \CRM_Utils_SQL_Select
 322    */
 323   public function param($keys, $value = NULL) {
 324     // Why bother with an override? To provide bett er type-hinting in `@return`.
 325     return parent::param($keys, $value);
 326   }
 327
 328   /**
 329    * Set a limit on the number of records to return.
 330    *
 331    * @param int $limit
 332    * @param int $offset
 333    * @return CRM_Utils_SQL_Select
 334    * @throws CRM_Core_Exception
 335    */
 336   public function limit($limit, $offset = 0) {
 337     if ($limit !== NULL && !is_numeric($limit)) {
 338       throw new CRM_Core_Exception("Illegal limit");
 339     }
 340     if ($offset !== NULL && !is_numeric($offset)) {
 341       throw new CRM_Core_Exception("Illegal offset");
 342     }
 343     $this->limit = $limit;
 344     $this->offset = $offset;
 345     return $this;
 346   }
 347
 348   /**
 349    * Insert the results of the SELECT query into another
 350    * table.
 351    *
 352    * @param string $table
 353    *   The name of the other table (which receives new data).
 354    * @param array $fields
 355    *   The fields to fill in the other table (in order).
 356    * @return CRM_Utils_SQL_Select
 357    * @see insertIntoField
 358    */
 359   public function insertInto($table, $fields = []) {
 360     $this->insertInto = $table;
 361     $this->insertIntoField($fields);
 362     return $this;
 363   }
 364
 365   /**
 366    * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
 367    *
 368    * @param string $table
 369    *   The name of the other table (which receives new data).
 370    * @param array $fields
 371    *   The fields to fill in the other table (in order).
 372    * @return CRM_Utils_SQL_Select
 373    */
 374   public function insertIgnoreInto($table, $fields = []) {
 375     $this->insertVerb = "INSERT IGNORE INTO ";
 376     return $this->insertInto($table, $fields);
 377   }
 378
 379   /**
 380    * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
 381    *
 382    * @param string $table
 383    *   The name of the other table (which receives new data).
 384    * @param array $fields
 385    *   The fields to fill in the other table (in order).
 386    */
 387   public function replaceInto($table, $fields = []) {
 388     $this->insertVerb = "REPLACE INTO ";
 389     return $this->insertInto($table, $fields);
 390   }
 391
 392   /**
 393    * @param array $fields
 394    *   The fields to fill in the other table (in order).
 395    * @return CRM_Utils_SQL_Select
 396    */
 397   public function insertIntoField($fields) {
 398     $fields = (array) $fields;
 399     foreach ($fields as $field) {
 400       $this->insertIntoFields[] = $field;
 401     }
 402     return $this;
 403   }
 404
 405   /**
 406    * @param array|NULL $parts
 407    *   List of fields to check (e.g. 'selects', 'joins').
 408    *   Defaults to all.
 409    * @return bool
 410    */
 411   public function isEmpty($parts = NULL) {
 412     $empty = TRUE;
 413     $fields = [
 414       'insertInto',
 415       'insertIntoFields',
 416       'selects',
 417       'from',
 418       'joins',
 419       'wheres',
 420       'groupBys',
 421       'havings',
 422       'orderBys',
 423       'limit',
 424       'offset',
 425     ];
 426     if ($parts !== NULL) {
 427       $fields = array_intersect($fields, $parts);
 428     }
 429     foreach ($fields as $field) {
 430       if (!empty($this->{$field})) {
 431         $empty = FALSE;
 432       }
 433     }
 434     return $empty;
 435   }
 436
 437   /**
 438    * @return string
 439    *   SQL statement
 440    */
 441   public function toSQL() {
 442     $sql = '';
 443     if ($this->insertInto) {
 444       $sql .= $this->insertVerb . $this->insertInto . ' (';
 445       $sql .= implode(', ', $this->insertIntoFields);
 446       $sql .= ")\n";
 447     }
 448
 449     if ($this->selects) {
 450       $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n";
 451     }
 452     else {
 453       $sql .= 'SELECT *' . "\n";
 454     }
 455     if ($this->from !== NULL) {
 456       $sql .= 'FROM ' . $this->from . "\n";
 457     }
 458     foreach ($this->joins as $join) {
 459       $sql .= $join . "\n";
 460     }
 461     if ($this->wheres) {
 462       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 463     }
 464     if ($this->groupBys) {
 465       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 466     }
 467     if ($this->havings) {
 468       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 469     }
 470     if ($this->orderBys) {
 471       $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys,
 472         ['weight', 'guid']);
 473       $orderBys = CRM_Utils_Array::collect('value', $orderBys);
 474       $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
 475     }
 476     if ($this->limit !== NULL) {
 477       $sql .= 'LIMIT ' . $this->limit . "\n";
 478       if ($this->offset !== NULL) {
 479         $sql .= 'OFFSET ' . $this->offset . "\n";
 480       }
 481     }
 482     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 483       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 484     }
 485     return $sql;
 486   }
 487
 488   /**
 489    * Execute the query.
 490    *
 491    * To examine the results, use a function like `fetch()`, `fetchAll()`,
 492    * `fetchValue()`, or `fetchMap()`.
 493    *
 494    * @param string|NULL $daoName
 495    *   The return object should be an instance of this class.
 496    *   Ex: 'CRM_Contact_BAO_Contact'.
 497    * @param bool $i18nRewrite
 498    *   If the system has multilingual features, should the field/table
 499    *   names be rewritten?
 500    * @return CRM_Core_DAO
 501    * @see CRM_Core_DAO::executeQuery
 502    * @see CRM_Core_I18n_Schema::rewriteQuery
 503    */
 504   public function execute($daoName = NULL, $i18nRewrite = TRUE) {
 505     // Don't pass through $params. toSQL() handles interpolation.
 506     $params = [];
 507
 508     // Don't pass through $abort, $trapException. Just use straight-up exceptions.
 509     $abort = TRUE;
 510     $trapException = FALSE;
 511     $errorScope = CRM_Core_TemporaryErrorScope::useException();
 512
 513     // Don't pass through freeDAO. You can do it yourself.
 514     $freeDAO = FALSE;
 515
 516     return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName,
 517       $freeDAO, $i18nRewrite, $trapException);
 518   }
 519
 520 }