CRM/Utils/SQL/Select.php

   1 <?php
   2
   3 /**
   4  * Dear God Why Do I Have To Write This (Dumb SQL Builder)
   5  *
   6  * Usage:
   7  * @code
   8  * $select = CRM_Utils_SQL_Select::from('civicrm_activity act')
   9  *     ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
  10  *     ->where('activity_type_id = #type', array('type' => 234))
  11  *     ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
  12  *     ->where('subject like @subj', array('subj' => '%hello%'))
  13  *     ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
  14  *     ->where('!column = @value', array(
  15  *        'column' => $customField->column_name,
  16  *        'value' => $form['foo']
  17  *      ))
  18  * echo $select->toSQL();
  19  * @endcode
  20  *
  21  * Design principles:
  22  *  - Portable
  23  *    - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
  24  *    - No knowledge of the underlying data model
  25  *    - Single file
  26  *  - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  27  *  - Variable escaping is concise and controllable based on prefixes, eg
  28  *    - similar to Drupal's t()
  29  *    - use "@varname" to insert the escaped value
  30  *    - use "!varname" to insert raw (unescaped) values
  31  *    - use "#varname" to insert a numerical value (these are validated but not escaped)
  32  *    - to disable any preprocessing, simply omit the variable list
  33  *    - control characters (@!#) are mandatory in expressions but optional in arg-keys
  34  *  - Variables may be individual values or arrays; arrays are imploded with commas
  35  *  - Conditionals are AND'd; if you need OR's, do it yourself
  36  *  - Use classes/functions with documentation (rather than undocumented array-trees)
  37  *  - For any given string, interpolation is only performed once. After an interpolation,
  38  *    a string may never again be subjected to interpolation.
  39  *
  40  * The "interpolate-once" principle can be enforced by either interpolating on input
  41  * xor output. The notations for input and output interpolation are a bit different,
  42  * and they may not be mixed.
  43  *
  44  * @code
  45  * // Interpolate on input. Set params when using them.
  46  * $select->where('activity_type_id = #type', array(
  47  *   'type' => 234,
  48  * ));
  49  *
  50  * // Interpolate on output. Set params independently.
  51  * $select
  52  *     ->where('activity_type_id = #type')
  53  *     ->param('type', 234),
  54  * @endcode
  55  */
  56 class CRM_Utils_SQL_Select implements ArrayAccess {
  57
  58   /**
  59    * Interpolate values as soon as they are passed in (where(), join(), etc).
  60    *
  61    * Default.
  62    *
  63    * Pro: Every clause has its own unique namespace for parameters.
  64    * Con: Probably slower.
  65    * Advice: Use this when aggregating SQL fragments from agents who
  66    *   maintained by different parties.
  67    */
  68   const INTERPOLATE_INPUT = 'in';
  69
  70   /**
  71    * Interpolate values when rendering SQL output (toSQL()).
  72    *
  73    * Pro: Probably faster.
  74    * Con: Must maintain an aggregated list of all parameters.
  75    * Advice: Use this when you have control over the entire query.
  76    */
  77   const INTERPOLATE_OUTPUT = 'out';
  78
  79   /**
  80    * Determine mode automatically. When the first attempt is made
  81    * to use input-interpolation (eg `where(..., array(...))`) or
  82    * output-interpolation (eg `param(...)`), the mode will be
  83    * set. Subsequent calls will be validated using the same mode.
  84    */
  85   const INTERPOLATE_AUTO = 'auto';
  86
  87   private $mode = NULL;
  88   private $insertInto = NULL;
  89   private $insertIntoFields = array();
  90   private $selects = array();
  91   private $from;
  92   private $joins = array();
  93   private $wheres = array();
  94   private $groupBys = array();
  95   private $havings = array();
  96   private $orderBys = array();
  97   private $limit = NULL;
  98   private $offset = NULL;
  99   private $params = array();
 100
 101   // Public to work-around PHP 5.3 limit.
 102   public $strict = NULL;
 103
 104   /**
 105    * Create a new SELECT query.
 106    *
 107    * @param string $from
 108    *   Table-name and optional alias.
 109    * @param array $options
 110    * @return CRM_Utils_SQL_Select
 111    */
 112   public static function from($from, $options = array()) {
 113     return new self($from, $options);
 114   }
 115
 116   /**
 117    * Create a partial SELECT query.
 118    *
 119    * @param array $options
 120    * @return CRM_Utils_SQL_Select
 121    */
 122   public static function fragment($options = array()) {
 123     return new self(NULL, $options);
 124   }
 125
 126   /**
 127    * Create a new SELECT query.
 128    *
 129    * @param string $from
 130    *   Table-name and optional alias.
 131    * @param array $options
 132    */
 133   public function __construct($from, $options = array()) {
 134     $this->from = $from;
 135     $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO;
 136   }
 137
 138   /**
 139    * Make a new copy of this query.
 140    *
 141    * @return CRM_Utils_SQL_Select
 142    */
 143   public function copy() {
 144     return clone $this;
 145   }
 146
 147   /**
 148    * @param CRM_Utils_SQL_Select $other
 149    * @param array|NULL $parts
 150    *   ex: 'joins', 'wheres'
 151    * @return $this
 152    */
 153   public function merge($other, $parts = NULL) {
 154     if ($other === NULL) {
 155       return $this;
 156     }
 157
 158     if ($this->mode === self::INTERPOLATE_AUTO) {
 159       $this->mode = $other->mode;
 160     }
 161     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 162       // Noop.
 163     }
 164     elseif ($this->mode !== $other->mode) {
 165       // Mixing modes will lead to someone getting an expected substitution.
 166       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 167     }
 168
 169     $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params');
 170     foreach ($arrayFields as $f) {
 171       if ($parts === NULL || in_array($f, $parts)) {
 172         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 173       }
 174     }
 175
 176     $flatFields = array('insertInto', 'from', 'limit', 'offset');
 177     foreach ($flatFields as $f) {
 178       if ($parts === NULL || in_array($f, $parts)) {
 179         if ($other->{$f} !== NULL) {
 180           $this->{$f} = $other->{$f};
 181         }
 182       }
 183     }
 184
 185     return $this;
 186   }
 187
 188   /**
 189    * Add a new JOIN clause.
 190    *
 191    * Note: To add multiple JOINs at once, use $name===NULL and
 192    * pass an array of $exprs.
 193    *
 194    * @param string|NULL $name
 195    *   The effective alias of the joined table.
 196    * @param string|array $exprs
 197    *   The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
 198    * @param array|null $args
 199    * @return CRM_Utils_SQL_Select
 200    */
 201   public function join($name, $exprs, $args = NULL) {
 202     if ($name !== NULL) {
 203       $this->joins[$name] = $this->interpolate($exprs, $args);
 204     }
 205     else {
 206       foreach ($exprs as $name => $expr) {
 207         $this->joins[$name] = $this->interpolate($expr, $args);
 208       }
 209       return $this;
 210     }
 211     return $this;
 212   }
 213
 214   /**
 215    * Specify the column(s)/value(s) to return by adding to the SELECT clause
 216    *
 217    * @param string|array $exprs list of SQL expressions
 218    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 219    * @return CRM_Utils_SQL_Select
 220    */
 221   public function select($exprs, $args = NULL) {
 222     $exprs = (array) $exprs;
 223     foreach ($exprs as $expr) {
 224       $this->selects[$expr] = $this->interpolate($expr, $args);
 225     }
 226     return $this;
 227   }
 228
 229   /**
 230    * Limit results by adding extra condition(s) to the WHERE clause
 231    *
 232    * @param string|array $exprs list of SQL expressions
 233    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 234    * @return CRM_Utils_SQL_Select
 235    */
 236   public function where($exprs, $args = NULL) {
 237     $exprs = (array) $exprs;
 238     foreach ($exprs as $expr) {
 239       $this->wheres[$expr] = $this->interpolate($expr, $args);
 240     }
 241     return $this;
 242   }
 243
 244   /**
 245    * Group results by adding extra items to the GROUP BY clause.
 246    *
 247    * @param string|array $exprs list of SQL expressions
 248    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 249    * @return CRM_Utils_SQL_Select
 250    */
 251   public function groupBy($exprs, $args = NULL) {
 252     $exprs = (array) $exprs;
 253     foreach ($exprs as $expr) {
 254       $this->groupBys[$expr] = $this->interpolate($expr, $args);
 255     }
 256     return $this;
 257   }
 258
 259   /**
 260    * Limit results by adding extra condition(s) to the HAVING clause
 261    *
 262    * @param string|array $exprs list of SQL expressions
 263    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 264    * @return CRM_Utils_SQL_Select
 265    */
 266   public function having($exprs, $args = NULL) {
 267     $exprs = (array) $exprs;
 268     foreach ($exprs as $expr) {
 269       $this->havings[$expr] = $this->interpolate($expr, $args);
 270     }
 271     return $this;
 272   }
 273
 274   /**
 275    * Sort results by adding extra items to the ORDER BY clause.
 276    *
 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
 280    */
 281   public function orderBy($exprs, $args = NULL) {
 282     $exprs = (array) $exprs;
 283     foreach ($exprs as $expr) {
 284       $this->orderBys[$expr] = $this->interpolate($expr, $args);
 285     }
 286     return $this;
 287   }
 288
 289   /**
 290    * Set one (or multiple) parameters to interpolate into the query.
 291    *
 292    * @param array|string $keys
 293    *   Key name, or an array of key-value pairs.
 294    * @param null|mixed $value
 295    * @return $this
 296    */
 297   public function param($keys, $value = NULL) {
 298     if ($this->mode === self::INTERPOLATE_AUTO) {
 299       $this->mode = self::INTERPOLATE_OUTPUT;
 300     }
 301     elseif ($this->mode !== self::INTERPOLATE_OUTPUT) {
 302       throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
 303     }
 304
 305     if (is_array($keys)) {
 306       foreach ($keys as $k => $v) {
 307         $this->params[$k] = $v;
 308       }
 309     }
 310     else {
 311       $this->params[$keys] = $value;
 312     }
 313     return $this;
 314   }
 315
 316   /**
 317    * Set a limit on the number of records to return.
 318    *
 319    * @param int $limit
 320    * @param int $offset
 321    * @return CRM_Utils_SQL_Select
 322    * @throws CRM_Core_Exception
 323    */
 324   public function limit($limit, $offset = 0) {
 325     if ($limit !== NULL && !is_numeric($limit)) {
 326       throw new CRM_Core_Exception("Illegal limit");
 327     }
 328     if ($offset !== NULL && !is_numeric($offset)) {
 329       throw new CRM_Core_Exception("Illegal offset");
 330     }
 331     $this->limit = $limit;
 332     $this->offset = $offset;
 333     return $this;
 334   }
 335
 336   /**
 337    * Insert the results of the SELECT query into another
 338    * table.
 339    *
 340    * @param string $table
 341    *   The name of the other table (which receives new data).
 342    * @param array $fields
 343    *   The fields to fill in the other table (in order).
 344    * @return $this
 345    * @see insertIntoField
 346    */
 347   public function insertInto($table, $fields = array()) {
 348     $this->insertInto = $table;
 349     $this->insertIntoField($fields);
 350     return $this;
 351   }
 352
 353   /**
 354    * @param array $fields
 355    *   The fields to fill in the other table (in order).
 356    * @return $this
 357    */
 358   public function insertIntoField($fields) {
 359     $fields = (array) $fields;
 360     foreach ($fields as $field) {
 361       $this->insertIntoFields[] = $field;
 362     }
 363     return $this;
 364   }
 365
 366   /**
 367    * @param array|NULL $parts
 368    *   List of fields to check (e.g. 'selects', 'joins').
 369    *   Defaults to all.
 370    * @return bool
 371    */
 372   public function isEmpty($parts = NULL) {
 373     $empty = TRUE;
 374     $fields = array(
 375       'insertInto',
 376       'insertIntoFields',
 377       'selects',
 378       'from',
 379       'joins',
 380       'wheres',
 381       'groupBys',
 382       'havings',
 383       'orderBys',
 384       'limit',
 385       'offset',
 386     );
 387     if ($parts !== NULL) {
 388       $fields = array_intersect($fields, $parts);
 389     }
 390     foreach ($fields as $field) {
 391       if (!empty($this->{$field})) {
 392         $empty = FALSE;
 393       }
 394     }
 395     return $empty;
 396   }
 397
 398   /**
 399    * Enable (or disable) strict mode.
 400    *
 401    * In strict mode, unknown variables will generate exceptions.
 402    *
 403    * @param bool $strict
 404    * @return $this
 405    */
 406   public function strict($strict = TRUE) {
 407     $this->strict = $strict;
 408     return $this;
 409   }
 410
 411   /**
 412    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
 413    *
 414    * @param $expr SQL expression
 415    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
 416    *   prefix '@' => escape SQL
 417    *   prefix '#' => literal number, skip escaping but do validation
 418    *   prefix '!' => literal, skip escaping and validation
 419    *   if a value is an array, then it will be imploded
 420    *
 421    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
 422    *
 423    * @throws CRM_Core_Exception
 424    * @return string
 425    *   SQL expression
 426    */
 427   public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) {
 428     if ($args === NULL) {
 429       return $expr;
 430     }
 431     else {
 432       if ($this->mode === self::INTERPOLATE_AUTO) {
 433         $this->mode = $activeMode;
 434       }
 435       elseif ($activeMode !== $this->mode) {
 436         throw new RuntimeException("Cannot mix interpolation modes.");
 437       }
 438
 439       $select = $this;
 440       return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) {
 441         if (isset($args[$m[2]])) {
 442           $values = $args[$m[2]];
 443         }
 444         elseif (isset($args[$m[1] . $m[2]])) {
 445           // Backward compat. Keys in $args look like "#myNumber" or "@myString".
 446           $values = $args[$m[1] . $m[2]];
 447         }
 448         elseif ($select->strict) {
 449           throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
 450         }
 451         else {
 452           // Unrecognized variables are ignored. Mitigate risk of accidents.
 453           return $m[0];
 454         }
 455         $values = is_array($values) ? $values : array($values);
 456         switch ($m[1]) {
 457           case '@':
 458             $parts = array_map(array($select, 'escapeString'), $values);
 459             return implode(', ', $parts);
 460
 461           case '!':
 462             return implode(', ', $values);
 463
 464           case '#':
 465             foreach ($values as $valueKey => $value) {
 466               if ($value === NULL) {
 467                 $values[$valueKey] = 'NULL';
 468               }
 469               elseif (!is_numeric($value)) {
 470                 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE));
 471                 throw new CRM_Core_Exception("Failed encoding non-numeric value");
 472               }
 473             }
 474             return implode(', ', $values);
 475
 476           default:
 477             throw new CRM_Core_Exception("Unrecognized prefix");
 478         }
 479       }, $expr);
 480     }
 481   }
 482
 483   /**
 484    * @param string|NULL $value
 485    * @return string
 486    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 487    */
 488   public function escapeString($value) {
 489     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 490   }
 491
 492   /**
 493    * @return string
 494    *   SQL statement
 495    */
 496   public function toSQL() {
 497     $sql = '';
 498     if ($this->insertInto) {
 499       $sql .= 'INSERT INTO ' . $this->insertInto . ' (';
 500       $sql .= implode(', ', $this->insertIntoFields);
 501       $sql .= ")\n";
 502     }
 503     if ($this->selects) {
 504       $sql .= 'SELECT ' . implode(', ', $this->selects) . "\n";
 505     }
 506     else {
 507       $sql .= 'SELECT *' . "\n";
 508     }
 509     if ($this->from !== NULL) {
 510       $sql .= 'FROM ' . $this->from . "\n";
 511     }
 512     foreach ($this->joins as $join) {
 513       $sql .= $join . "\n";
 514     }
 515     if ($this->wheres) {
 516       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 517     }
 518     if ($this->groupBys) {
 519       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 520     }
 521     if ($this->havings) {
 522       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 523     }
 524     if ($this->orderBys) {
 525       $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n";
 526     }
 527     if ($this->limit !== NULL) {
 528       $sql .= 'LIMIT ' . $this->limit . "\n";
 529       if ($this->offset !== NULL) {
 530         $sql .= 'OFFSET ' . $this->offset . "\n";
 531       }
 532     }
 533     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 534       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 535     }
 536     return $sql;
 537   }
 538
 539   public function offsetExists($offset) {
 540     return isset($this->params[$offset]);
 541   }
 542
 543   public function offsetGet($offset) {
 544     return $this->params[$offset];
 545   }
 546
 547   public function offsetSet($offset, $value) {
 548     $this->param($offset, $value);
 549   }
 550
 551   public function offsetUnset($offset) {
 552     unset($this->params[$offset]);
 553   }
 554
 555 }