CRM/Utils/SQL/Select.php

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