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  * $select = new CRM_Utils_SQL_Select('civicrm_activity act');
   8  * $select
   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  *
  20  * Design principles:
  21  *  - Portable
  22  *    - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
  23  *    - No knowledge of the underlying data model
  24  *    - Single file
  25  *  - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  26  *  - Variable escaping is concise and controllable based on prefixes, eg
  27  *    - similar to Drupal's t()
  28  *    - use "@varname" to insert the escaped value
  29  *    - use "!varname" to insert raw (unescaped) values
  30  *    - use "#varname" to insert a numerical value (these are validated but not escaped)
  31  *    - to disable any preprocessing, simply omit the variable list
  32  *  - Variables may be individual values or arrays; arrays are imploded with commas
  33  *  - Conditionals are AND'd; if you need OR's, do it yourself
  34  *  - Use classes/functions with documentation (rather than undocumented array-trees)
  35  */
  36 class CRM_Utils_SQL_Select {
  37   private $selects = array();
  38   private $from;
  39   private $joins = array();
  40   private $wheres = array();
  41   private $groupBys = array();
  42   private $havings = array();
  43   private $orderBys = array();
  44   private $limit = NULL;
  45   private $offset = NULL;
  46
  47   /**
  48    * Create a new SELECT query
  49    *
  50    * @param string $from table-name and optional alias
  51    * @return CRM_Utils_SQL_Select
  52    */
  53   public static function from($from) {
  54     return new self($from);
  55   }
  56
  57   /**
  58    * Create a new SELECT query
  59    *
  60    * @param string $from table-name and optional alias
  61    */
  62   public function __construct($from) {
  63     $this->from = $from;
  64   }
  65
  66   /**
  67    * Add a new JOIN clause
  68    *
  69    * @param string $name the effective alias of the joined table
  70    * @param string $expr the complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id")
  71    * @param array|null $args
  72    * @return CRM_Utils_SQL_Select
  73    */
  74   public function join($name, $expr, $args = NULL) {
  75     $this->joins[$name] = $this->interpolate($expr, $args);
  76     return $this;
  77   }
  78
  79   /**
  80    * Specify the column(s)/value(s) to return by adding to the SELECT clause
  81    *
  82    * @param string|array $exprs list of SQL expressions
  83    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
  84    * @return CRM_Utils_SQL_Select
  85    */
  86   public function select($exprs, $args = NULL) {
  87     $exprs = (array) $exprs;
  88     foreach ($exprs as $expr) {
  89       $this->selects[$expr] = $this->interpolate($expr, $args);
  90     }
  91     return $this;
  92   }
  93
  94   /**
  95    * Limit results by adding extra condition(s) to the WHERE clause
  96    *
  97    * @param string|array $exprs list of SQL expressions
  98    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
  99    * @return CRM_Utils_SQL_Select
 100    */
 101   public function where($exprs, $args = NULL) {
 102     $exprs = (array) $exprs;
 103     foreach ($exprs as $expr) {
 104       $this->wheres[$expr] = $this->interpolate($expr, $args);
 105     }
 106     return $this;
 107   }
 108
 109   /**
 110    * Group results by adding extra items to the GROUP BY clause
 111    *
 112    * @param string|array $exprs list of SQL expressions
 113    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 114    * @return CRM_Utils_SQL_Select
 115    */
 116   public function groupBy($exprs, $args = NULL) {
 117     $exprs = (array) $exprs;
 118     foreach ($exprs as $expr) {
 119       $this->groupBys[$expr] = $this->interpolate($expr, $args);
 120     }
 121     return $this;
 122   }
 123
 124   /**
 125    * Limit results by adding extra condition(s) to the HAVING clause
 126    *
 127    * @param string|array $exprs list of SQL expressions
 128    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 129    * @return CRM_Utils_SQL_Select
 130    */
 131   public function having($exprs, $args = NULL) {
 132     $exprs = (array) $exprs;
 133     foreach ($exprs as $expr) {
 134       $this->havings[$expr] = $this->interpolate($expr, $args);
 135     }
 136     return $this;
 137   }
 138
 139   /**
 140    * Sort results by adding extra items to the ORDER BY clause
 141    *
 142    * @param string|array $exprs list of SQL expressions
 143    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 144    * @return CRM_Utils_SQL_Select
 145    */
 146   public function orderBy($exprs, $args = NULL) {
 147     $exprs = (array) $exprs;
 148     foreach ($exprs as $expr) {
 149       $this->orderBys[$expr] = $this->interpolate($expr, $args);
 150     }
 151     return $this;
 152   }
 153
 154   /**
 155    * Set a limit on the number of records to return
 156    *
 157    * @param int $limit
 158    * @param int $offset
 159    * @return CRM_Utils_SQL_Select
 160    * @throws CRM_Core_Exception
 161    */
 162   public function limit($limit, $offset = 0) {
 163     if ($limit !== NULL && !is_numeric($limit)) {
 164       throw new CRM_Core_Exception("Illegal limit");
 165     }
 166     if ($offset !== NULL && !is_numeric($offset)) {
 167       throw new CRM_Core_Exception("Illegal offset");
 168     }
 169     $this->limit = $limit;
 170     $this->offset = $offset;
 171     return $this;
 172   }
 173
 174   /**
 175    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
 176    *
 177    * @param string SQL expression
 178    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
 179    *   prefix '@' => escape SQL
 180    *   prefix '#' => literal number, skip escaping but do validation
 181    *   prefix '!' => literal, skip escaping and validation
 182    *   if a value is an array, then it will be imploded
 183    *
 184    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
 185    *
 186    * @return string SQL expression
 187    */
 188   public function interpolate($expr, $args) {
 189     if ($args === NULL) {
 190       return $expr;
 191     }
 192     else {
 193       foreach (array_keys($args) as $key) {
 194         $values = is_array($args[$key]) ? $args[$key] : array($args[$key]);
 195         if ($key{0} == '@') {
 196           $parts = array_map(array($this, 'escapeString'), $values);
 197           $args[$key] = implode(', ', $parts);
 198         }
 199         elseif ($key{0} == '!') {
 200           $args[$key] = implode(', ', $values);
 201         }
 202         elseif ($key{0} == '#') {
 203           foreach ($values as $valueKey => $value) {
 204             if ($value === NULL) {
 205               $values[$valueKey] = 'NULL';
 206             }
 207             elseif (!is_numeric($value)) {
 208               //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE));
 209               throw new CRM_Core_Exception("Failed encoding non-numeric value");
 210             }
 211           }
 212           $args[$key] = implode(', ', $values);
 213         }
 214         else {
 215           throw new CRM_Core_Exception("Bad SQL parameter key: $key");
 216         }
 217       }
 218       return strtr($expr, $args);
 219     }
 220   }
 221
 222   /**
 223    * @param string|NULL $value
 224    * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
 225    */
 226   protected function escapeString($value) {
 227     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 228   }
 229
 230   /**
 231    * @return string SQL statement
 232    */
 233   public function toSQL() {
 234     if ($this->selects) {
 235       $sql = 'SELECT ' . implode(', ', $this->selects) . "\n";
 236     }
 237     else {
 238       $sql = 'SELECT *' . "\n";
 239     }
 240     $sql .= 'FROM ' . $this->from . "\n";
 241     foreach ($this->joins as $join) {
 242       $sql .= $join . "\n";
 243     }
 244     if ($this->wheres) {
 245       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 246     }
 247     if ($this->groupBys) {
 248       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 249     }
 250     if ($this->havings) {
 251       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 252     }
 253     if ($this->orderBys) {
 254       $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n";
 255     }
 256     if ($this->limit !== NULL) {
 257       $sql .= 'LIMIT ' . $this->limit . "\n";
 258       if ($this->offset !== NULL) {
 259         $sql .= 'OFFSET ' . $this->offset . "\n";
 260       }
 261     }
 262     return $sql;
 263   }
 264 }