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 | * | |
c3a8e3e5 | 50 | * @param string $from table-name and optional alias |
e2b5e5b1 TO |
51 | * @return CRM_Utils_SQL_Select |
52 | */ | |
53 | public static function from($from) { | |
54 | return new self($from); | |
55 | } | |
56 | ||
c3a8e3e5 TO |
57 | /** |
58 | * Create a new SELECT query | |
59 | * | |
60 | * @param string $from table-name and optional alias | |
61 | */ | |
e2b5e5b1 TO |
62 | public function __construct($from) { |
63 | $this->from = $from; | |
64 | } | |
65 | ||
c3a8e3e5 TO |
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 | */ | |
e2b5e5b1 TO |
74 | public function join($name, $expr, $args = NULL) { |
75 | $this->joins[$name] = $this->interpolate($expr, $args); | |
76 | return $this; | |
77 | } | |
78 | ||
79 | /** | |
c3a8e3e5 TO |
80 | * Specify the column(s)/value(s) to return by adding to the SELECT clause |
81 | * | |
e2b5e5b1 TO |
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 | /** | |
c3a8e3e5 TO |
95 | * Limit results by adding extra condition(s) to the WHERE clause |
96 | * | |
e2b5e5b1 TO |
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 | /** | |
c3a8e3e5 TO |
110 | * Group results by adding extra items to the GROUP BY clause |
111 | * | |
e2b5e5b1 TO |
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 | /** | |
c3a8e3e5 TO |
125 | * Limit results by adding extra condition(s) to the HAVING clause |
126 | * | |
e2b5e5b1 TO |
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 | /** | |
c3a8e3e5 TO |
140 | * Sort results by adding extra items to the ORDER BY clause |
141 | * | |
e2b5e5b1 TO |
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 | ||
c3a8e3e5 TO |
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 | ||
e2b5e5b1 TO |
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 | ||
c3a8e3e5 TO |
222 | /** |
223 | * @param string|NULL $value | |
224 | * @return string SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) | |
225 | */ | |
e2b5e5b1 TO |
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 | } | |
c3a8e3e5 TO |
256 | if ($this->limit !== NULL) { |
257 | $sql .= 'LIMIT ' . $this->limit . "\n"; | |
258 | if ($this->offset !== NULL) { | |
259 | $sql .= 'OFFSET ' . $this->offset . "\n"; | |
260 | } | |
261 | } | |
e2b5e5b1 TO |
262 | return $sql; |
263 | } | |
264 | } |