Checks if the types of returned expressions are compatible with the documented types.
1 | <?php |
||
2 | |||
3 | namespace Elgg\Database; |
||
4 | |||
5 | use DatabaseException; |
||
6 | use Doctrine\DBAL\Connection; |
||
7 | use Doctrine\DBAL\Query\Expression\CompositeExpression; |
||
8 | use Doctrine\DBAL\Query\QueryBuilder as DbalQueryBuilder; |
||
9 | use Elgg\Database\Clauses\Clause; |
||
10 | use Elgg\Database\Clauses\ComparisonClause; |
||
11 | use Elgg\Database\Clauses\JoinClause; |
||
12 | use Elgg\Database\Clauses\WhereClause; |
||
13 | |||
14 | /** |
||
15 | * Database abstraction query builder |
||
16 | */ |
||
17 | abstract class QueryBuilder extends DbalQueryBuilder { |
||
18 | |||
19 | const TABLE_ENTITIES = 'entities'; |
||
20 | const TABLE_METADATA = 'metadata'; |
||
21 | const TABLE_ANNOTATIONS = 'annotations'; |
||
22 | const TABLE_RELATIONSHIPS = 'entity_relationships'; |
||
23 | const TABLE_PRIVATE_SETTINGS = 'private_settings'; |
||
24 | |||
25 | static $calculations = [ |
||
26 | 'avg', |
||
27 | 'count', |
||
28 | 'greatest', |
||
29 | 'least', |
||
30 | 'max', |
||
31 | 'min', |
||
32 | 'sum', |
||
33 | ]; |
||
34 | |||
35 | /** |
||
36 | * @var array |
||
37 | */ |
||
38 | protected $joins = []; |
||
39 | |||
40 | /** |
||
41 | * @var int |
||
42 | */ |
||
43 | protected $join_index = 0; |
||
44 | |||
45 | /** |
||
46 | * @var string |
||
47 | */ |
||
48 | protected $table_name; |
||
49 | |||
50 | /** |
||
51 | * @var string |
||
52 | */ |
||
53 | protected $table_alias; |
||
54 | |||
55 | /** |
||
56 | * Creates a new SelectQueryBuilder for join/where subqueries using the DB connection of the primary QueryBuilder |
||
57 | * |
||
58 | * @param string $table Main table name |
||
59 | * @param string $alias Select alias |
||
60 | * |
||
61 | * @return Select |
||
62 | */ |
||
63 | 1 | public function subquery($table, $alias = null) { |
|
64 | 1 | $qb = new Select($this->getConnection()); |
|
65 | 1 | $qb->from($table, $alias); |
|
66 | |||
67 | 1 | return $qb; |
|
68 | } |
||
69 | |||
70 | /** |
||
71 | * Apply clause to this instance |
||
72 | * |
||
73 | * @param Clause $clause Clause |
||
74 | * @param string $alias Table alias |
||
75 | * |
||
76 | * @return static |
||
77 | */ |
||
78 | 1178 | public function addClause(Clause $clause, $alias = null) { |
|
79 | 1178 | if (!isset($alias)) { |
|
80 | 1141 | $alias = $this->getTableAlias(); |
|
81 | } |
||
82 | 1178 | $expr = $clause->prepare($this, $alias); |
|
83 | 1175 | if ($clause instanceof WhereClause && ($expr instanceof CompositeExpression || is_string($expr))) { |
|
84 | 1151 | $this->andWhere($expr); |
|
85 | } |
||
86 | |||
87 | 1175 | return $this; |
|
88 | } |
||
89 | |||
90 | /** |
||
91 | * Prefixes the table name with installation DB prefix |
||
92 | * |
||
93 | * @param string $table |
||
94 | * |
||
95 | * @return string |
||
96 | */ |
||
97 | 1465 | public function prefix($table) { |
|
98 | 1465 | $prefix = _elgg_services()->db->prefix; |
|
99 | 1465 | if (strpos($table, $prefix) !== 0) { |
|
100 | 1465 | return "{$prefix}{$table}"; |
|
101 | } |
||
102 | |||
103 | 1236 | return $table; |
|
104 | } |
||
105 | |||
106 | /** |
||
107 | * Returns the name of the primary table |
||
108 | * |
||
109 | * @return string |
||
110 | */ |
||
111 | 5 | public function getTableName() { |
|
112 | 5 | return $this->table_name; |
|
113 | } |
||
114 | |||
115 | /** |
||
116 | * Returns the alias of the primary table |
||
117 | * @return string |
||
118 | */ |
||
119 | 1145 | public function getTableAlias() { |
|
120 | 1145 | return $this->table_alias; |
|
121 | } |
||
122 | |||
123 | /** |
||
124 | * Sets a new parameter assigning it a unique parameter key/name if none provided |
||
125 | * Returns the name of the new parameter |
||
126 | * |
||
127 | * @param mixed $value Parameter value |
||
128 | * @param string $type Parameter type |
||
129 | * @param string $key Parameter key/index |
||
130 | * |
||
131 | * @return string |
||
132 | */ |
||
133 | 1407 | public function param($value, $type = null, $key = null) { |
|
134 | 1407 | if (!$key) { |
|
135 | 1406 | $parameters = $this->getParameters(); |
|
136 | 1406 | $key = ":qb" . (count($parameters) + 1); |
|
137 | } |
||
138 | |||
139 | 1407 | if (is_array($value)) { |
|
140 | 161 | if ($type === ELGG_VALUE_INTEGER) { |
|
141 | 108 | $type = Connection::PARAM_INT_ARRAY; |
|
142 | 60 | } else if ($type === ELGG_VALUE_STRING) { |
|
143 | 60 | $type = Connection::PARAM_STR_ARRAY; |
|
144 | } |
||
145 | } |
||
146 | |||
147 | 1407 | $this->setParameter($key, $value, $type); |
|
148 | |||
149 | 1407 | return $key; |
|
150 | } |
||
151 | |||
152 | /** |
||
153 | * {@inheritdoc} |
||
154 | * |
||
155 | * @access private Use create() method on the extending class |
||
156 | */ |
||
157 | 1424 | public function from($table, $alias = null) { |
|
158 | 1424 | $this->table_name = $table; |
|
159 | 1424 | $this->table_alias = $alias; |
|
160 | |||
161 | 1424 | return parent::from($this->prefix($table), $alias); |
|
162 | } |
||
163 | |||
164 | /** |
||
165 | * {@inheritdoc} |
||
166 | * |
||
167 | * @access private Use create() method on the extending class |
||
168 | */ |
||
169 | 1057 | public function insert($insert = null) { |
|
170 | 1057 | $this->table_name = $insert; |
|
171 | |||
172 | 1057 | return parent::insert($this->prefix($insert)); |
|
173 | } |
||
174 | |||
175 | /** |
||
176 | * {@inheritdoc} |
||
177 | * |
||
178 | * @access private Use create() method on the extending class |
||
179 | */ |
||
180 | 744 | public function update($table = null, $alias = null) { |
|
181 | 744 | $this->table_name = $table; |
|
182 | 744 | $this->table_alias = $alias; |
|
183 | |||
184 | 744 | return parent::update($this->prefix($table), $alias); |
|
185 | } |
||
186 | |||
187 | /** |
||
188 | * {@inheritdoc} |
||
189 | * |
||
190 | * @access private Use create() method on the extending class |
||
191 | */ |
||
192 | 841 | public function delete($table = null, $alias = null) { |
|
193 | 841 | $this->table_name = $table; |
|
194 | 841 | $this->table_alias = $alias; |
|
195 | |||
196 | 841 | return parent::delete($this->prefix($table), $alias); |
|
197 | } |
||
198 | |||
199 | /** |
||
200 | * {@inheritdoc} |
||
201 | */ |
||
202 | 1235 | public function join($fromAlias, $join, $alias, $condition = null) { |
|
203 | 1235 | return parent::join($fromAlias, $this->prefix($join), $alias, $condition); |
|
204 | } |
||
205 | |||
206 | /** |
||
207 | * {@inheritdoc} |
||
208 | */ |
||
209 | 1235 | public function innerJoin($fromAlias, $join, $alias, $condition = null) { |
|
210 | 1235 | return parent::innerJoin($fromAlias, $this->prefix($join), $alias, $condition); |
|
211 | } |
||
212 | |||
213 | /** |
||
214 | * {@inheritdoc} |
||
215 | */ |
||
216 | 420 | public function leftJoin($fromAlias, $join, $alias, $condition = null) { |
|
217 | 420 | return parent::leftJoin($fromAlias, $this->prefix($join), $alias, $condition); |
|
218 | } |
||
219 | |||
220 | /** |
||
221 | * {@inheritdoc} |
||
222 | */ |
||
223 | 2 | public function rightJoin($fromAlias, $join, $alias, $condition = null) { |
|
224 | 2 | return parent::rightJoin($fromAlias, $this->prefix($join), $alias, $condition); // TODO: Change the autogenerated stub |
|
225 | } |
||
226 | |||
227 | /** |
||
228 | * Merges multiple composite expressions with a boolean |
||
229 | * |
||
230 | * @param mixed $parts Composite expression(s) or string(s) |
||
231 | * @param string $boolean AND|OR |
||
232 | * |
||
233 | * @return CompositeExpression|string |
||
234 | */ |
||
235 | 1338 | public function merge($parts = null, $boolean = 'AND') { |
|
236 | 1338 | if (empty($parts)) { |
|
237 | 1276 | return; |
|
238 | } |
||
239 | |||
240 | 1338 | $parts = (array) $parts; |
|
241 | |||
242 | 1338 | $parts = array_filter($parts, function ($e) { |
|
243 | 1338 | if (empty($e)) { |
|
244 | 1301 | return false; |
|
245 | } |
||
246 | 1329 | if (!$e instanceof CompositeExpression && !is_string($e)) { |
|
247 | return false; |
||
248 | } |
||
249 | |||
250 | 1329 | return true; |
|
251 | 1338 | }); |
|
252 | 1338 | if (empty($parts)) { |
|
253 | 736 | return; |
|
254 | } |
||
255 | |||
256 | 1329 | if (count($parts) === 1) { |
|
257 | 1294 | return array_shift($parts); |
|
258 | } |
||
259 | |||
260 | 1281 | if (strtoupper($boolean) === 'OR') { |
|
261 | 273 | return $this->expr()->orX()->addMultiple($parts); |
|
262 | } else { |
||
263 | 1272 | return $this->expr()->andX()->addMultiple($parts); |
|
264 | } |
||
265 | } |
||
266 | |||
267 | /** |
||
268 | * Build value comparison clause |
||
269 | * |
||
270 | * @param string $x Comparison value (e.g. prefixed column name) |
||
271 | * @param string $comparison Comparison operator |
||
272 | * @param mixed $y Value to compare against |
||
273 | * If the value is an array, comparisons will be performed in such as a way as |
||
274 | * to ensure that either all or none of the elements of the array meet the criteria, |
||
275 | * e.g. in case of LIKE will return results where at least one element matches the |
||
276 | * criteria, where as with NOT LIKE will return results where none of the criteria |
||
277 | * are met |
||
278 | * @param string $type Value type for sanitization/casting |
||
279 | * @param bool $case_sensitive Use case sensitive comparison for strings |
||
280 | * |
||
281 | * @return CompositeExpression|null |
||
282 | */ |
||
283 | 1424 | public function compare($x, $comparison, $y = null, $type = null, $case_sensitive = null) { |
|
284 | 1424 | return (new ComparisonClause($x, $comparison, $y, $type, $case_sensitive))->prepare($this); |
|
0 ignored issues
–
show
Bug
Best Practice
introduced
by
Loading history...
|
|||
285 | } |
||
286 | |||
287 | /** |
||
288 | * Build a between clause |
||
289 | * |
||
290 | * @param string $x Comparison value (e.g. prefixed column name) |
||
291 | * @param mixed $lower Lower bound |
||
292 | * @param mixed $upper Upper bound |
||
293 | * |
||
294 | * @return CompositeExpression|null |
||
295 | */ |
||
296 | 1277 | public function between($x, $lower = null, $upper = null, $type = null) { |
|
297 | 1277 | $wheres = []; |
|
298 | 1277 | if ($lower) { |
|
299 | 16 | $wheres[] = $this->compare($x, '>=', $lower, $type); |
|
300 | } |
||
301 | 1277 | if ($upper) { |
|
302 | 17 | $wheres[] = $this->compare($x, '<=', $upper, $type); |
|
303 | } |
||
304 | |||
305 | 1277 | return $this->merge($wheres); |
|
306 | } |
||
307 | |||
308 | /** |
||
309 | * Get an index of the next available join alias |
||
310 | * @return string |
||
311 | */ |
||
312 | 256 | public function getNextJoinAlias() { |
|
313 | 256 | $this->join_index++; |
|
314 | |||
315 | 256 | return "qbt{$this->join_index}"; |
|
316 | } |
||
317 | |||
318 | /** |
||
319 | * Join entity table from alias and return joined table alias |
||
320 | * |
||
321 | * @param string $from_alias Main table alias |
||
322 | * @param string $from_column Guid column name in the main table |
||
323 | * @param string $join_type JOIN type |
||
324 | * @param string $joined_alias Joined table alias |
||
325 | * |
||
326 | * @return string |
||
327 | */ |
||
328 | 688 | public function joinEntitiesTable($from_alias = '', $from_column = 'guid', $join_type = 'inner', $joined_alias = null) { |
|
329 | 688 | if (in_array($joined_alias, $this->joins)) { |
|
330 | 3 | return $joined_alias; |
|
331 | } |
||
332 | |||
333 | 688 | if ($from_alias) { |
|
334 | 688 | $from_column = "$from_alias.$from_column"; |
|
335 | } |
||
336 | |||
337 | 688 | $hash = sha1(serialize([ |
|
338 | 688 | $join_type, |
|
339 | 688 | self::TABLE_ENTITIES, |
|
340 | 688 | $from_column, |
|
341 | ])); |
||
342 | |||
343 | 688 | if (!isset($joined_alias) && !empty($this->joins[$hash])) { |
|
344 | 1 | return $this->joins[$hash]; |
|
345 | } |
||
346 | |||
347 | 688 | $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column) { |
|
348 | 688 | return $qb->compare("$joined_alias.guid", '=', $from_column); |
|
349 | 688 | }; |
|
350 | |||
351 | 688 | $clause = new JoinClause(self::TABLE_ENTITIES, $joined_alias, $condition, $join_type); |
|
352 | 688 | $joined_alias = $clause->prepare($this, $from_alias); |
|
353 | |||
354 | 688 | $this->joins[$hash] = $joined_alias; |
|
355 | |||
356 | 688 | return $joined_alias; |
|
357 | } |
||
358 | |||
359 | /** |
||
360 | * Join metadata table from alias and return joined table alias |
||
361 | * |
||
362 | * @param string $from_alias Alias of the main table |
||
363 | * @param string $from_column Guid column name in the main table |
||
364 | * @param null $name Metadata name(s) |
||
365 | * @param string $join_type JOIN type |
||
366 | * @param string $joined_alias Joined table alias |
||
367 | * |
||
368 | * @return string |
||
369 | */ |
||
370 | 1163 | public function joinMetadataTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) { |
|
371 | 1163 | if (in_array($joined_alias, $this->joins)) { |
|
372 | 5 | return $joined_alias; |
|
373 | } |
||
374 | |||
375 | 1163 | if ($from_alias) { |
|
376 | 1163 | $from_column = "$from_alias.$from_column"; |
|
377 | } |
||
378 | |||
379 | 1163 | $hash = sha1(serialize([ |
|
380 | 1163 | $join_type, |
|
381 | 1163 | self::TABLE_METADATA, |
|
382 | 1163 | $from_column, |
|
383 | 1163 | (array) $name, |
|
384 | ])); |
||
385 | |||
386 | 1163 | if (!isset($joined_alias) && !empty($this->joins[$hash])) { |
|
387 | 4 | return $this->joins[$hash]; |
|
388 | } |
||
389 | |||
390 | 1163 | $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) { |
|
391 | 1163 | return $qb->merge([ |
|
392 | 1163 | $qb->compare("$joined_alias.entity_guid", '=', $from_column), |
|
393 | 1163 | $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING), |
|
394 | ]); |
||
395 | 1163 | }; |
|
396 | |||
397 | 1163 | $clause = new JoinClause(self::TABLE_METADATA, $joined_alias, $condition, $join_type); |
|
398 | |||
399 | 1163 | $joined_alias = $clause->prepare($this, $from_alias); |
|
400 | |||
401 | 1163 | $this->joins[$hash] = $joined_alias; |
|
402 | |||
403 | 1163 | return $joined_alias; |
|
404 | } |
||
405 | |||
406 | /** |
||
407 | * Join annotations table from alias and return joined table alias |
||
408 | * |
||
409 | * @param string $from_alias Main table alias |
||
410 | * @param string $from_column Guid column name in the main table |
||
411 | * @param null $name Annotation name |
||
412 | * @param string $join_type JOIN type |
||
413 | * @param string $joined_alias Joined table alias |
||
414 | * |
||
415 | * @return string |
||
416 | */ |
||
417 | 89 | public function joinAnnotationTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) { |
|
418 | 89 | if (in_array($joined_alias, $this->joins)) { |
|
419 | 3 | return $joined_alias; |
|
420 | } |
||
421 | |||
422 | 89 | if ($from_alias) { |
|
423 | 89 | $from_column = "$from_alias.$from_column"; |
|
424 | } |
||
425 | |||
426 | 89 | $hash = sha1(serialize([ |
|
427 | 89 | $join_type, |
|
428 | 89 | self::TABLE_ANNOTATIONS, |
|
429 | 89 | $from_column, |
|
430 | 89 | (array) $name, |
|
431 | ])); |
||
432 | |||
433 | 89 | if (!isset($joined_alias) && !empty($this->joins[$hash])) { |
|
434 | 4 | return $this->joins[$hash]; |
|
435 | } |
||
436 | |||
437 | 89 | $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) { |
|
438 | 89 | return $qb->merge([ |
|
439 | 89 | $qb->compare("$joined_alias.entity_guid", '=', $from_column), |
|
440 | 89 | $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING), |
|
441 | ]); |
||
442 | 89 | }; |
|
443 | |||
444 | 89 | $clause = new JoinClause(self::TABLE_ANNOTATIONS, $joined_alias, $condition, $join_type); |
|
445 | |||
446 | 89 | $joined_alias = $clause->prepare($this, $from_alias); |
|
447 | |||
448 | 89 | $this->joins[$hash] = $joined_alias; |
|
449 | |||
450 | 89 | return $joined_alias; |
|
451 | } |
||
452 | |||
453 | /** |
||
454 | * Join private settings table from alias and return joined table alias |
||
455 | * |
||
456 | * @param string $from_alias Main table alias |
||
457 | * @param string $from_column Guid column name in the main table |
||
458 | * @param null $name Private setting name |
||
459 | * @param string $join_type JOIN type |
||
460 | * @param string $joined_alias Joined table alias |
||
461 | * |
||
462 | * @return string |
||
463 | */ |
||
464 | 154 | public function joinPrivateSettingsTable($from_alias = '', $from_column = 'guid', $name = null, $join_type = 'inner', $joined_alias = null) { |
|
465 | 154 | if (in_array($joined_alias, $this->joins)) { |
|
466 | 3 | return $joined_alias; |
|
467 | } |
||
468 | |||
469 | 154 | if ($from_alias) { |
|
470 | 154 | $from_column = "$from_alias.$from_column"; |
|
471 | } |
||
472 | |||
473 | 154 | $hash = sha1(serialize([ |
|
474 | 154 | $join_type, |
|
475 | 154 | self::TABLE_PRIVATE_SETTINGS, |
|
476 | 154 | $from_column, |
|
477 | 154 | (array) $name, |
|
478 | ])); |
||
479 | |||
480 | 154 | if (!isset($joined_alias) && !empty($this->joins[$hash])) { |
|
481 | 7 | return $this->joins[$hash]; |
|
482 | } |
||
483 | |||
484 | 154 | $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name) { |
|
485 | 154 | return $qb->merge([ |
|
486 | 154 | $qb->compare("$joined_alias.entity_guid", '=', $from_column), |
|
487 | 154 | $qb->compare("$joined_alias.name", '=', $name, ELGG_VALUE_STRING), |
|
488 | ]); |
||
489 | 154 | }; |
|
490 | |||
491 | 154 | $clause = new JoinClause(self::TABLE_PRIVATE_SETTINGS, $joined_alias, $condition, $join_type); |
|
492 | |||
493 | 154 | $joined_alias = $clause->prepare($this, $from_alias); |
|
494 | |||
495 | 154 | $this->joins[$hash] = $joined_alias; |
|
496 | |||
497 | 154 | return $joined_alias; |
|
498 | } |
||
499 | |||
500 | /** |
||
501 | * Join relationship table from alias and return joined table alias |
||
502 | * |
||
503 | * @param string $from_alias Main table alias |
||
504 | * @param string $from_column Guid column name in the main table |
||
505 | * @param string $name Relationship name |
||
506 | * @param bool $inverse Join on guid_two column |
||
507 | * @param string $join_type JOIN type |
||
508 | * @param string $joined_alias Joined table alias |
||
509 | * |
||
510 | * @return string |
||
511 | * @throws \InvalidParameterException |
||
512 | */ |
||
513 | 37 | public function joinRelationshipTable($from_alias = '', $from_column = 'guid', $name = null, $inverse = false, $join_type = 'inner', $joined_alias = null) { |
|
514 | 37 | if (in_array($joined_alias, $this->joins)) { |
|
515 | 1 | return $joined_alias; |
|
516 | } |
||
517 | |||
518 | 37 | if ($from_alias) { |
|
519 | 37 | $from_column = "$from_alias.$from_column"; |
|
520 | } |
||
521 | |||
522 | 37 | $hash = sha1(serialize([ |
|
523 | 37 | $join_type, |
|
524 | 37 | self::TABLE_RELATIONSHIPS, |
|
525 | 37 | $from_column, |
|
526 | 37 | $inverse, |
|
527 | 37 | (array) $name, |
|
528 | ])); |
||
529 | |||
530 | 37 | if (!isset($joined_alias) && !empty($this->joins[$hash])) { |
|
531 | 1 | return $this->joins[$hash]; |
|
532 | } |
||
533 | |||
534 | 37 | $condition = function (QueryBuilder $qb, $joined_alias) use ($from_column, $name, $inverse) { |
|
535 | 37 | $parts = []; |
|
536 | 37 | if ($inverse) { |
|
537 | 26 | $parts[] = $qb->compare("$joined_alias.guid_one", '=', $from_column); |
|
538 | } else { |
||
539 | 17 | $parts[] = $qb->compare("$joined_alias.guid_two", '=', $from_column); |
|
540 | } |
||
541 | 37 | $parts[] = $qb->compare("$joined_alias.relationship", '=', $name, ELGG_VALUE_STRING); |
|
542 | 37 | return $qb->merge($parts); |
|
543 | 37 | }; |
|
544 | |||
545 | $clause = new JoinClause(self::TABLE_RELATIONSHIPS, $joined_alias, $condition, $join_type); |
||
546 | |||
547 | $joined_alias = $clause->prepare($this, $from_alias); |
||
548 | |||
549 | $this->joins[$hash] = $joined_alias; |
||
550 | |||
551 | return $joined_alias; |
||
552 | } |
||
553 | } |
||
554 |