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  */
  35 class CRM_Utils_SQL_Select {
  36   private $selects = array();
  37   private $from;
  38   private $joins = array();
  39   private $wheres = array();
  40   private $groupBys = array();
  41   private $havings = array();
  42   private $orderBys = array();
  43
  44   /**
  45    * Create a new SELECT query
  46    *
  47    * @param $from
  48    * @return CRM_Utils_SQL_Select
  49    */
  50   public static function from($from) {
  51     return new self($from);
  52   }
  53
  54   public function __construct($from) {
  55     $this->from = $from;
  56   }
  57
  58   public function join($name, $expr, $args = NULL) {
  59     $this->joins[$name] = $this->interpolate($expr, $args);
  60     return $this;
  61   }
  62
  63   /**
  64    * @param string|array $exprs list of SQL expressions
  65    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
  66    * @return CRM_Utils_SQL_Select
  67    */
  68   public function select($exprs, $args = NULL) {
  69     $exprs = (array) $exprs;
  70     foreach ($exprs as $expr) {
  71       $this->selects[$expr] = $this->interpolate($expr, $args);
  72     }
  73     return $this;
  74   }
  75
  76   /**
  77    * @param string|array $exprs list of SQL expressions
  78    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
  79    * @return CRM_Utils_SQL_Select
  80    */
  81   public function where($exprs, $args = NULL) {
  82     $exprs = (array) $exprs;
  83     foreach ($exprs as $expr) {
  84       $this->wheres[$expr] = $this->interpolate($expr, $args);
  85     }
  86     return $this;
  87   }
  88
  89   /**
  90    * @param string|array $exprs list of SQL expressions
  91    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
  92    * @return CRM_Utils_SQL_Select
  93    */
  94   public function groupBy($exprs, $args = NULL) {
  95     $exprs = (array) $exprs;
  96     foreach ($exprs as $expr) {
  97       $this->groupBys[$expr] = $this->interpolate($expr, $args);
  98     }
  99     return $this;
 100   }
 101
 102   /**
 103    * @param string|array $exprs list of SQL expressions
 104    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 105    * @return CRM_Utils_SQL_Select
 106    */
 107   public function having($exprs, $args = NULL) {
 108     $exprs = (array) $exprs;
 109     foreach ($exprs as $expr) {
 110       $this->havings[$expr] = $this->interpolate($expr, $args);
 111     }
 112     return $this;
 113   }
 114
 115   /**
 116    * @param string|array $exprs list of SQL expressions
 117    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 118    * @return CRM_Utils_SQL_Select
 119    */
 120   public function orderBy($exprs, $args = NULL) {
 121     $exprs = (array) $exprs;
 122     foreach ($exprs as $expr) {
 123       $this->orderBys[$expr] = $this->interpolate($expr, $args);
 124     }
 125     return $this;
 126   }
 127
 128   /**
 129    * Given a string like "field_name = @value", replace "@value" with an escaped SQL string
 130    *
 131    * @param string SQL expression
 132    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
 133    *   prefix '@' => escape SQL
 134    *   prefix '#' => literal number, skip escaping but do validation
 135    *   prefix '!' => literal, skip escaping and validation
 136    *   if a value is an array, then it will be imploded
 137    *
 138    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
 139    *
 140    * @return string SQL expression
 141    */
 142   public function interpolate($expr, $args) {
 143     if ($args === NULL) {
 144       return $expr;
 145     }
 146     else {
 147       foreach (array_keys($args) as $key) {
 148         $values = is_array($args[$key]) ? $args[$key] : array($args[$key]);
 149         if ($key{0} == '@') {
 150           $parts = array_map(array($this, 'escapeString'), $values);
 151           $args[$key] = implode(', ', $parts);
 152         }
 153         elseif ($key{0} == '!') {
 154           $args[$key] = implode(', ', $values);
 155         }
 156         elseif ($key{0} == '#') {
 157           foreach ($values as $valueKey => $value) {
 158             if ($value === NULL) {
 159               $values[$valueKey] = 'NULL';
 160             }
 161             elseif (!is_numeric($value)) {
 162               //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE));
 163               throw new CRM_Core_Exception("Failed encoding non-numeric value");
 164             }
 165           }
 166           $args[$key] = implode(', ', $values);
 167         }
 168         else {
 169           throw new CRM_Core_Exception("Bad SQL parameter key: $key");
 170         }
 171       }
 172       return strtr($expr, $args);
 173     }
 174   }
 175
 176   protected function escapeString($value) {
 177     return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
 178   }
 179
 180   /**
 181    * @return string SQL statement
 182    */
 183   public function toSQL() {
 184     if ($this->selects) {
 185       $sql = 'SELECT ' . implode(', ', $this->selects) . "\n";
 186     }
 187     else {
 188       $sql = 'SELECT *' . "\n";
 189     }
 190     $sql .= 'FROM ' . $this->from . "\n";
 191     foreach ($this->joins as $join) {
 192       $sql .= $join . "\n";
 193     }
 194     if ($this->wheres) {
 195       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 196     }
 197     if ($this->groupBys) {
 198       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 199     }
 200     if ($this->havings) {
 201       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 202     }
 203     if ($this->orderBys) {
 204       $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n";
 205     }
 206     return $sql;
 207   }
 208 }