crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/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       $evaluatedExpr = $this->interpolate($expr, $args);
 240       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 241     }
 242     return $this;
 243   }
 244
 245   /**
 246    * Group results by adding extra items to the GROUP BY 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 groupBy($exprs, $args = NULL) {
 253     $exprs = (array) $exprs;
 254     foreach ($exprs as $expr) {
 255       $this->groupBys[$expr] = $this->interpolate($expr, $args);
 256     }
 257     return $this;
 258   }
 259
 260   /**
 261    * Limit results by adding extra condition(s) to the HAVING clause
 262    *
 263    * @param string|array $exprs list of SQL expressions
 264    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 265    * @return CRM_Utils_SQL_Select
 266    */
 267   public function having($exprs, $args = NULL) {
 268     $exprs = (array) $exprs;
 269     foreach ($exprs as $expr) {
 270       $this->havings[$expr] = $this->interpolate($expr, $args);
 271     }
 272     return $this;
 273   }
 274
 275   /**
 276    * Sort results by adding extra items to the ORDER BY clause.
 277    *
 278    * @param string|array $exprs list of SQL expressions
 279    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 280    * @return CRM_Utils_SQL_Select
 281    */
 282   public function orderBy($exprs, $args = NULL) {
 283     $exprs = (array) $exprs;
 284     foreach ($exprs as $expr) {
 285       $this->orderBys[$expr] = $this->interpolate($expr, $args);
 286     }
 287     return $this;
 288   }
 289
 290   /**
 291    * Set one (or multiple) parameters to interpolate into the query.
 292    *
 293    * @param array|string $keys
 294    *   Key name, or an array of key-value pairs.
 295    * @param null|mixed $value
 296    * @return $this
 297    */
 298   public function param($keys, $value = NULL) {
 299     if ($this->mode === self::INTERPOLATE_AUTO) {
 300       $this->mode = self::INTERPOLATE_OUTPUT;
 301     }
 302     elseif ($this->mode !== self::INTERPOLATE_OUTPUT) {
 303       throw new RuntimeException("Select::param() only makes sense when interpolating on output.");
 304     }
 305
 306     if (is_array($keys)) {
 307       foreach ($keys as $k => $v) {
 308         $this->params[$k] = $v;
 309       }
 310     }
 311     else {
 312       $this->params[$keys] = $value;
 313     }
 314     return $this;
 315   }
 316
 317   /**
 318    * Set a limit on the number of records to return.
 319    *
 320    * @param int $limit
 321    * @param int $offset
 322    * @return CRM_Utils_SQL_Select
 323    * @throws CRM_Core_Exception
 324    */
 325   public function limit($limit, $offset = 0) {
 326     if ($limit !== NULL && !is_numeric($limit)) {
 327       throw new CRM_Core_Exception("Illegal limit");
 328     }
 329     if ($offset !== NULL && !is_numeric($offset)) {
 330       throw new CRM_Core_Exception("Illegal offset");
 331     }
 332     $this->limit = $limit;
 333     $this->offset = $offset;
 334     return $this;
 335   }
 336
 337   /**
 338    * Insert the results of the SELECT query into another
 339    * table.
 340    *
 341    * @param string $table
 342    *   The name of the other table (which receives new data).
 343    * @param array $fields
 344    *   The fields to fill in the other table (in order).
 345    * @return $this
 346    * @see insertIntoField
 347    */
 348   public function insertInto($table, $fields = array()) {
 349     $this->insertInto = $table;
 350     $this->insertIntoField($fields);
 351     return $this;
 352   }
 353
 354   /**
 355    * @param array $fields
 356    *   The fields to fill in the other table (in order).
 357    * @return $this
 358    */
 359   public function insertIntoField($fields) {
 360     $fields = (array) $fields;
 361     foreach ($fields as $field) {
 362       $this->insertIntoFields[] = $field;
 363     }
 364     return $this;
 365   }
 366
 367   /**
 368    * @param array|NULL $parts
 369    *   List of fields to check (e.g. 'selects', 'joins').
 370    *   Defaults to all.
 371    * @return bool
 372    */
 373   public function isEmpty($parts = NULL) {
 374     $empty = TRUE;
 375     $fields = array(
 376       'insertInto',
 377       'insertIntoFields',
 378       'selects',
 379       'from',
 380       'joins',
 381       'wheres',
 382       'groupBys',
 383       'havings',
 384       'orderBys',
 385       'limit',
 386       'offset',
 387     );
 388     if ($parts !== NULL) {
 389       $fields = array_intersect($fields, $parts);
 390     }
 391     foreach ($fields as $field) {
 392       if (!empty($this->{$field})) {
 393         $empty = FALSE;
 394       }
 395     }
 396     return $empty;
 397   }
 398
 399   /**
 400    * Enable (or disable) strict mode.
 401    *
 402    * In strict mode, unknown variables will generate exceptions.
 403    *
 404    * @param bool $strict
 405    * @return $this
 406    */
 407   public function strict($strict = TRUE) {
 408     $this->strict = $strict;
 409     return $this;
 410   }
 411
 412   /**
 413    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
 414    *
 415    * @param $expr SQL expression
 416    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
 417    *   prefix '@' => escape SQL
 418    *   prefix '#' => literal number, skip escaping but do validation
 419    *   prefix '!' => literal, skip escaping and validation
 420    *   if a value is an array, then it will be imploded
 421    *
 422    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
 423    *
 424    * @throws CRM_Core_Exception
 425    * @return string
 426    *   SQL expression
 427    */
 428   public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) {
 429     if ($args === NULL) {
 430       return $expr;
 431     }
 432     else {
 433       if ($this->mode === self::INTERPOLATE_AUTO) {
 434         $this->mode = $activeMode;
 435       }
 436       elseif ($activeMode !== $this->mode) {
 437         throw new RuntimeException("Cannot mix interpolation modes.");
 438       }
 439
 440       $select = $this;
 441       return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) {
 442         if (isset($args[$m[2]])) {
 443           $values = $args[$m[2]];
 444         }
 445         elseif (isset($args[$m[1] . $m[2]])) {
 446           // Backward compat. Keys in $args look like "#myNumber" or "@myString".
 447           $values = $args[$m[1] . $m[2]];
 448         }
 449         elseif ($select->strict) {
 450           throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.');
 451         }
 452         else {
 453           // Unrecognized variables are ignored. Mitigate risk of accidents.
 454           return $m[0];
 455         }
 456         $values = is_array($values) ? $values : array($values);
 457         switch ($m[1]) {
 458           case '@':
 459             $parts = array_map(array($select, 'escapeString'), $values);
 460             return implode(', ', $parts);
 461
 462           case '!':
 463             return implode(', ', $values);
 464
 465           case '#':
 466             foreach ($values as $valueKey => $value) {
 467               if ($value === NULL) {
 468                 $values[$valueKey] = 'NULL';
 469               }
 470               elseif (!is_numeric($value)) {
 471                 //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE));
 472                 throw new CRM_Core_Exception("Failed encoding non-numeric value");
 473               }
 474             }
 475             return implode(', ', $values);
 476
 477           default:
 478             throw new CRM_Core_Exception("Unrecognized prefix");
 479         }
 480       }, $expr);
 481     }
 482   }
 483
 484   /**
 485    * @param string|NULL $value
 486    * @return string
 487    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 488    */
 489   public function escapeString($value) {
 490     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 491   }
 492
 493   /**
 494    * @return string
 495    *   SQL statement
 496    */
 497   public function toSQL() {
 498     $sql = '';
 499     if ($this->insertInto) {
 500       $sql .= 'INSERT INTO ' . $this->insertInto . ' (';
 501       $sql .= implode(', ', $this->insertIntoFields);
 502       $sql .= ")\n";
 503     }
 504     if ($this->selects) {
 505       $sql .= 'SELECT ' . implode(', ', $this->selects) . "\n";
 506     }
 507     else {
 508       $sql .= 'SELECT *' . "\n";
 509     }
 510     if ($this->from !== NULL) {
 511       $sql .= 'FROM ' . $this->from . "\n";
 512     }
 513     foreach ($this->joins as $join) {
 514       $sql .= $join . "\n";
 515     }
 516     if ($this->wheres) {
 517       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 518     }
 519     if ($this->groupBys) {
 520       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 521     }
 522     if ($this->havings) {
 523       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 524     }
 525     if ($this->orderBys) {
 526       $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n";
 527     }
 528     if ($this->limit !== NULL) {
 529       $sql .= 'LIMIT ' . $this->limit . "\n";
 530       if ($this->offset !== NULL) {
 531         $sql .= 'OFFSET ' . $this->offset . "\n";
 532       }
 533     }
 534     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 535       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 536     }
 537     return $sql;
 538   }
 539
 540   public function offsetExists($offset) {
 541     return isset($this->params[$offset]);
 542   }
 543
 544   public function offsetGet($offset) {
 545     return $this->params[$offset];
 546   }
 547
 548   public function offsetSet($offset, $value) {
 549     $this->param($offset, $value);
 550   }
 551
 552   public function offsetUnset($offset) {
 553     unset($this->params[$offset]);
 554   }
 555
 556 }