Passed
Push — master ( c0a3a7...3b84a4 )
by Jeroen
58:51
created

engine/classes/Elgg/Database/QueryBuilder.php (3 issues)

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);
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)
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $name is correct as it would always require null to be passed?
Loading history...
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
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $name is correct as it would always require null to be passed?
Loading history...
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
0 ignored issues
show
Documentation Bug introduced by
Are you sure the doc-type for parameter $name is correct as it would always require null to be passed?
Loading history...
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