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