CRM/Utils/SQL/Select.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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  *    - Single file
  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.
  64  *
  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.
  68  *
  69  * @code
  70  * // Interpolate on input. Set params when using them.
  71  * $select->where('activity_type_id = #type', array(
  72  *   'type' => 234,
  73  * ));
  74  *
  75  * // Interpolate on output. Set params independently.
  76  * $select
  77  *     ->where('activity_type_id = #type')
  78  *     ->param('type', 234),
  79  * @endcode
  80  *
  81  * @package CRM
  82  * @copyright CiviCRM LLC (c) 2004-2017
  83  */
  84 class CRM_Utils_SQL_Select implements ArrayAccess {
  85
  86   /**
  87    * Interpolate values as soon as they are passed in (where(), join(), etc).
  88    *
  89    * Default.
  90    *
  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.
  95    */
  96   const INTERPOLATE_INPUT = 'in';
  97
  98   /**
  99    * Interpolate values when rendering SQL output (toSQL()).
 100    *
 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.
 104    */
 105   const INTERPOLATE_OUTPUT = 'out';
 106
 107   /**
 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.
 112    */
 113   const INTERPOLATE_AUTO = 'auto';
 114
 115   private $mode = NULL;
 116   private $insertInto = NULL;
 117   private $insertIntoFields = array();
 118   private $selects = array();
 119   private $from;
 120   private $joins = array();
 121   private $wheres = array();
 122   private $groupBys = array();
 123   private $havings = array();
 124   private $orderBys = array();
 125   private $limit = NULL;
 126   private $offset = NULL;
 127   private $params = array();
 128   private $distinct = NULL;
 129
 130   // Public to work-around PHP 5.3 limit.
 131   public $strict = NULL;
 132
 133   /**
 134    * Create a new SELECT query.
 135    *
 136    * @param string $from
 137    *   Table-name and optional alias.
 138    * @param array $options
 139    * @return CRM_Utils_SQL_Select
 140    */
 141   public static function from($from, $options = array()) {
 142     return new self($from, $options);
 143   }
 144
 145   /**
 146    * Create a partial SELECT query.
 147    *
 148    * @param array $options
 149    * @return CRM_Utils_SQL_Select
 150    */
 151   public static function fragment($options = array()) {
 152     return new self(NULL, $options);
 153   }
 154
 155   /**
 156    * Create a new SELECT query.
 157    *
 158    * @param string $from
 159    *   Table-name and optional alias.
 160    * @param array $options
 161    */
 162   public function __construct($from, $options = array()) {
 163     $this->from = $from;
 164     $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO;
 165   }
 166
 167   /**
 168    * Make a new copy of this query.
 169    *
 170    * @return CRM_Utils_SQL_Select
 171    */
 172   public function copy() {
 173     return clone $this;
 174   }
 175
 176   /**
 177    * Merge something or other.
 178    *
 179    * @param CRM_Utils_SQL_Select $other
 180    * @param array|NULL $parts
 181    *   ex: 'joins', 'wheres'
 182    * @return CRM_Utils_SQL_Select
 183    */
 184   public function merge($other, $parts = NULL) {
 185     if ($other === NULL) {
 186       return $this;
 187     }
 188
 189     if ($this->mode === self::INTERPOLATE_AUTO) {
 190       $this->mode = $other->mode;
 191     }
 192     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 193       // Noop.
 194     }
 195     elseif ($this->mode !== $other->mode) {
 196       // Mixing modes will lead to someone getting an expected substitution.
 197       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 198     }
 199
 200     $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params');
 201     foreach ($arrayFields as $f) {
 202       if ($parts === NULL || in_array($f, $parts)) {
 203         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 204       }
 205     }
 206
 207     $flatFields = array('insertInto', 'from', 'limit', 'offset');
 208     foreach ($flatFields as $f) {
 209       if ($parts === NULL || in_array($f, $parts)) {
 210         if ($other->{$f} !== NULL) {
 211           $this->{$f} = $other->{$f};
 212         }
 213       }
 214     }
 215
 216     return $this;
 217   }
 218
 219   /**
 220    * Add a new JOIN clause.
 221    *
 222    * Note: To add multiple JOINs at once, use $name===NULL and
 223    * pass an array of $exprs.
 224    *
 225    * @param string|NULL $name
 226    *   The effective alias of the joined table.
 227    * @param string|array $exprs
 228    *   The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
 229    * @param array|null $args
 230    * @return CRM_Utils_SQL_Select
 231    */
 232   public function join($name, $exprs, $args = NULL) {
 233     if ($name !== NULL) {
 234       $this->joins[$name] = $this->interpolate($exprs, $args);
 235     }
 236     else {
 237       foreach ($exprs as $name => $expr) {
 238         $this->joins[$name] = $this->interpolate($expr, $args);
 239       }
 240       return $this;
 241     }
 242     return $this;
 243   }
 244
 245   /**
 246    * Specify the column(s)/value(s) to return by adding to the SELECT clause
 247    *
 248    * @param string|array $exprs list of SQL expressions
 249    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 250    * @return CRM_Utils_SQL_Select
 251    */
 252   public function select($exprs, $args = NULL) {
 253     $exprs = (array) $exprs;
 254     foreach ($exprs as $expr) {
 255       $this->selects[] = $this->interpolate($expr, $args);
 256     }
 257     return $this;
 258   }
 259
 260   /**
 261    * Return only distinct values
 262    *
 263    * @param bool $isDistinct allow DISTINCT select or not
 264    * @return CRM_Utils_SQL_Select
 265    */
 266   public function distinct($isDistinct = TRUE) {
 267     if ($isDistinct) {
 268       $this->distinct = 'DISTINCT ';
 269     }
 270     return $this;
 271   }
 272
 273   /**
 274    * Limit results by adding extra condition(s) to the WHERE clause
 275    *
 276    * @param string|array $exprs list of SQL expressions
 277    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 278    * @return CRM_Utils_SQL_Select
 279    */
 280   public function where($exprs, $args = NULL) {
 281     $exprs = (array) $exprs;
 282     foreach ($exprs as $expr) {
 283       $evaluatedExpr = $this->interpolate($expr, $args);
 284       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 285     }
 286     return $this;
 287   }
 288
 289   /**
 290    * Group results by adding extra items to the GROUP BY clause.
 291    *
 292    * @param string|array $exprs list of SQL expressions
 293    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 294    * @return CRM_Utils_SQL_Select
 295    */
 296   public function groupBy($exprs, $args = NULL) {
 297     $exprs = (array) $exprs;
 298     foreach ($exprs as $expr) {
 299       $evaluatedExpr = $this->interpolate($expr, $args);
 300       $this->groupBys[$evaluatedExpr] = $evaluatedExpr;
 301     }
 302     return $this;
 303   }
 304
 305   /**
 306    * Limit results by adding extra condition(s) to the HAVING clause
 307    *
 308    * @param string|array $exprs list of SQL expressions
 309    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 310    * @return CRM_Utils_SQL_Select
 311    */
 312   public function having($exprs, $args = NULL) {
 313     $exprs = (array) $exprs;
 314     foreach ($exprs as $expr) {
 315       $evaluatedExpr = $this->interpolate($expr, $args);
 316       $this->havings[$evaluatedExpr] = $evaluatedExpr;
 317     }
 318     return $this;
 319   }
 320
 321   /**
 322    * Sort results by adding extra items to the ORDER BY clause.
 323    *
 324    * @param string|array $exprs list of SQL expressions
 325    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 326    * @return CRM_Utils_SQL_Select
 327    */
 328   public function orderBy($exprs, $args = NULL) {
 329     $exprs = (array) $exprs;
 330     foreach ($exprs as $expr) {
 331       $evaluatedExpr = $this->interpolate($expr, $args);
 332       $this->orderBys[$evaluatedExpr] = $evaluatedExpr;
 333     }
 334     return $this;
 335   }
 336
 337   /**
 338    * Set one (or multiple) parameters to interpolate into the query.
 339    *
 340    * @param array|string $keys
 341    *   Key name, or an array of key-value pairs.
 342    * @param null|mixed $value
 343    *   The new value of the parameter.
 344    *   Values may be strings, ints, or arrays thereof -- provided that the
 345    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 346    * @return \CRM_Utils_SQL_Select
 347    */
 348   public function param($keys, $value = NULL) {
 349     if ($this->mode === self::INTERPOLATE_AUTO) {
 350       $this->mode = self::INTERPOLATE_OUTPUT;
 351     }
 352     elseif ($this->mode !== self::INTERPOLATE_OUTPUT) {
 353       throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
 354     }
 355
 356     if (is_array($keys)) {
 357       foreach ($keys as $k => $v) {
 358         $this->params[$k] = $v;
 359       }
 360     }
 361     else {
 362       $this->params[$keys] = $value;
 363     }
 364     return $this;
 365   }
 366
 367   /**
 368    * Set a limit on the number of records to return.
 369    *
 370    * @param int $limit
 371    * @param int $offset
 372    * @return CRM_Utils_SQL_Select
 373    * @throws CRM_Core_Exception
 374    */
 375   public function limit($limit, $offset = 0) {
 376     if ($limit !== NULL && !is_numeric($limit)) {
 377       throw new CRM_Core_Exception("Illegal limit");
 378     }
 379     if ($offset !== NULL && !is_numeric($offset)) {
 380       throw new CRM_Core_Exception("Illegal offset");
 381     }
 382     $this->limit = $limit;
 383     $this->offset = $offset;
 384     return $this;
 385   }
 386
 387   /**
 388    * Insert the results of the SELECT query into another
 389    * table.
 390    *
 391    * @param string $table
 392    *   The name of the other table (which receives new data).
 393    * @param array $fields
 394    *   The fields to fill in the other table (in order).
 395    * @return CRM_Utils_SQL_Select
 396    * @see insertIntoField
 397    */
 398   public function insertInto($table, $fields = array()) {
 399     $this->insertInto = $table;
 400     $this->insertIntoField($fields);
 401     return $this;
 402   }
 403
 404   /**
 405    * @param array $fields
 406    *   The fields to fill in the other table (in order).
 407    * @return CRM_Utils_SQL_Select
 408    */
 409   public function insertIntoField($fields) {
 410     $fields = (array) $fields;
 411     foreach ($fields as $field) {
 412       $this->insertIntoFields[] = $field;
 413     }
 414     return $this;
 415   }
 416
 417   /**
 418    * @param array|NULL $parts
 419    *   List of fields to check (e.g. 'selects', 'joins').
 420    *   Defaults to all.
 421    * @return bool
 422    */
 423   public function isEmpty($parts = NULL) {
 424     $empty = TRUE;
 425     $fields = array(
 426       'insertInto',
 427       'insertIntoFields',
 428       'selects',
 429       'from',
 430       'joins',
 431       'wheres',
 432       'groupBys',
 433       'havings',
 434       'orderBys',
 435       'limit',
 436       'offset',
 437     );
 438     if ($parts !== NULL) {
 439       $fields = array_intersect($fields, $parts);
 440     }
 441     foreach ($fields as $field) {
 442       if (!empty($this->{$field})) {
 443         $empty = FALSE;
 444       }
 445     }
 446     return $empty;
 447   }
 448
 449   /**
 450    * Enable (or disable) strict mode.
 451    *
 452    * In strict mode, unknown variables will generate exceptions.
 453    *
 454    * @param bool $strict
 455    * @return CRM_Utils_SQL_Select
 456    */
 457   public function strict($strict = TRUE) {
 458     $this->strict = $strict;
 459     return $this;
 460   }
 461
 462   /**
 463    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
 464    *
 465    * @param string $expr SQL expression
 466    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
 467    *   prefix '@' => escape SQL
 468    *   prefix '#' => literal number, skip escaping but do validation
 469    *   prefix '!' => literal, skip escaping and validation
 470    *   if a value is an array, then it will be imploded
 471    *
 472    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
 473    *
 474    * @param string $activeMode
 475    *
 476    * @return string
 477    */
 478   public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) {
 479     if ($args === NULL) {
 480       return $expr;
 481     }
 482     else {
 483       if ($this->mode === self::INTERPOLATE_AUTO) {
 484         $this->mode = $activeMode;
 485       }
 486       elseif ($activeMode !== $this->mode) {
 487         throw new RuntimeException("Cannot mix interpolation modes.");
 488       }
 489
 490       $select = $this;
 491       return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) {
 492         if (isset($args[$m[2]])) {
 493           $values = $args[$m[2]];
 494         }
 495         elseif (isset($args[$m[1] . $m[2]])) {
 496           // Backward compat. Keys in $args look like "#myNumber" or "@myString".
 497           $values = $args[$m[1] . $m[2]];
 498         }
 499         elseif ($select->strict) {
 500           throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
 501         }
 502         else {
 503           // Unrecognized variables are ignored. Mitigate risk of accidents.
 504           return $m[0];
 505         }
 506         $values = is_array($values) ? $values : array($values);
 507         switch ($m[1]) {
 508           case '@':
 509             $parts = array_map(array($select, 'escapeString'), $values);
 510             return implode(', ', $parts);
 511
 512           // TODO: ensure all uses of this un-escaped literal are safe
 513           case '!':
 514             return implode(', ', $values);
 515
 516           case '#':
 517             foreach ($values as $valueKey => $value) {
 518               if ($value === NULL) {
 519                 $values[$valueKey] = 'NULL';
 520               }
 521               elseif (!is_numeric($value)) {
 522                 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($m[0] => $values), TRUE));
 523                 throw new CRM_Core_Exception("Failed encoding non-numeric value (" . $m[0] . ")");
 524               }
 525             }
 526             return implode(', ', $values);
 527
 528           default:
 529             throw new CRM_Core_Exception("Unrecognized prefix");
 530         }
 531       }, $expr);
 532     }
 533   }
 534
 535   /**
 536    * @param string|NULL $value
 537    * @return string
 538    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 539    */
 540   public function escapeString($value) {
 541     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 542   }
 543
 544   /**
 545    * @return string
 546    *   SQL statement
 547    */
 548   public function toSQL() {
 549     $sql = '';
 550     if ($this->insertInto) {
 551       $sql .= 'INSERT INTO ' . $this->insertInto . ' (';
 552       $sql .= implode(', ', $this->insertIntoFields);
 553       $sql .= ")\n";
 554     }
 555     if ($this->selects) {
 556       $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n";
 557     }
 558     else {
 559       $sql .= 'SELECT *' . "\n";
 560     }
 561     if ($this->from !== NULL) {
 562       $sql .= 'FROM ' . $this->from . "\n";
 563     }
 564     foreach ($this->joins as $join) {
 565       $sql .= $join . "\n";
 566     }
 567     if ($this->wheres) {
 568       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 569     }
 570     if ($this->groupBys) {
 571       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 572     }
 573     if ($this->havings) {
 574       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 575     }
 576     if ($this->orderBys) {
 577       $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n";
 578     }
 579     if ($this->limit !== NULL) {
 580       $sql .= 'LIMIT ' . $this->limit . "\n";
 581       if ($this->offset !== NULL) {
 582         $sql .= 'OFFSET ' . $this->offset . "\n";
 583       }
 584     }
 585     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 586       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 587     }
 588     return $sql;
 589   }
 590
 591   /**
 592    * Has an offset been set.
 593    *
 594    * @param string $offset
 595    *
 596    * @return bool
 597    */
 598   public function offsetExists($offset) {
 599     return isset($this->params[$offset]);
 600   }
 601
 602   /**
 603    * Get the value of a SQL parameter.
 604    *
 605    * @code
 606    *   $select['cid'] = 123;
 607    *   $select->where('contact.id = #cid');
 608    *   echo $select['cid'];
 609    * @endCode
 610    *
 611    * @param string $offset
 612    * @return mixed
 613    * @see param()
 614    * @see ArrayAccess::offsetGet
 615    */
 616   public function offsetGet($offset) {
 617     return $this->params[$offset];
 618   }
 619
 620   /**
 621    * Set the value of a SQL parameter.
 622    *
 623    * @code
 624    *   $select['cid'] = 123;
 625    *   $select->where('contact.id = #cid');
 626    *   echo $select['cid'];
 627    * @endCode
 628    *
 629    * @param string $offset
 630    * @param mixed $value
 631    *   The new value of the parameter.
 632    *   Values may be strings, ints, or arrays thereof -- provided that the
 633    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 634    * @see param()
 635    * @see ArrayAccess::offsetSet
 636    */
 637   public function offsetSet($offset, $value) {
 638     $this->param($offset, $value);
 639   }
 640
 641   /**
 642    * Unset the value of a SQL parameter.
 643    *
 644    * @param string $offset
 645    * @see param()
 646    * @see ArrayAccess::offsetUnset
 647    */
 648   public function offsetUnset($offset) {
 649     unset($this->params[$offset]);
 650   }
 651
 652 }