[TASK] Add more fixers for php-cs-fixer
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Query / Expression / ExpressionBuilder.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Query\Expression;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use TYPO3\CMS\Core\Database\Connection;
19
20 /**
21 * ExpressionBuilder class is responsible to dynamically create SQL query parts.
22 *
23 * It takes care building query conditions while ensuring table and column names
24 * are quoted within the created expressions / SQL fragments. It is a facade to
25 * the actual Doctrine ExpressionBuilder.
26 *
27 * The ExpressionBuilder is used within the context of the QueryBuilder to ensure
28 * queries are being build based on the requirements of the database platform in
29 * use.
30 */
31 class ExpressionBuilder
32 {
33 const EQ = '=';
34 const NEQ = '<>';
35 const LT = '<';
36 const LTE = '<=';
37 const GT = '>';
38 const GTE = '>=';
39
40 const QUOTE_NOTHING = 0;
41 const QUOTE_IDENTIFIER = 1;
42 const QUOTE_PARAMETER = 2;
43
44 /**
45 * The DBAL Connection.
46 *
47 * @var Connection
48 */
49 protected $connection;
50
51 /**
52 * Initializes a new ExpressionBuilder
53 *
54 * @param Connection $connection
55 */
56 public function __construct(Connection $connection)
57 {
58 $this->connection = $connection;
59 }
60
61 /**
62 * Creates a conjunction of the given boolean expressions
63 *
64 * @param mixed,... $expressions Optional clause. Requires at least one defined when converting to string.
65 *
66 * @return CompositeExpression
67 */
68 public function andX(...$expressions): CompositeExpression
69 {
70 return new CompositeExpression(CompositeExpression::TYPE_AND, $expressions);
71 }
72
73 /**
74 * Creates a disjunction of the given boolean expressions.
75 *
76 * @param mixed,... $expressions Optional clause. Requires at least one defined when converting to string.
77 *
78 * @return CompositeExpression
79 */
80 public function orX(...$expressions): CompositeExpression
81 {
82 return new CompositeExpression(CompositeExpression::TYPE_OR, $expressions);
83 }
84
85 /**
86 * Creates a comparison expression.
87 *
88 * @param mixed $leftExpression The left expression.
89 * @param string $operator One of the ExpressionBuilder::* constants.
90 * @param mixed $rightExpression The right expression.
91 *
92 * @return string
93 */
94 public function comparison($leftExpression, string $operator, $rightExpression): string
95 {
96 return $leftExpression . ' ' . $operator . ' ' . $rightExpression;
97 }
98
99 /**
100 * Creates an equality comparison expression with the given arguments.
101 *
102 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
103 * @param mixed $value The value. No automatic quoting/escaping is done.
104 *
105 * @return string
106 */
107 public function eq(string $fieldName, $value): string
108 {
109 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::EQ, $value);
110 }
111
112 /**
113 * Creates a non equality comparison expression with the given arguments.
114 * First argument is considered the left expression and the second is the right expression.
115 * When converted to string, it will generated a <left expr> <> <right expr>. Example:
116 *
117 * [php]
118 * // u.id <> 1
119 * $q->where($q->expr()->neq('u.id', '1'));
120 *
121 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
122 * @param mixed $value The value. No automatic quoting/escaping is done.
123 *
124 * @return string
125 */
126 public function neq(string $fieldName, $value): string
127 {
128 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::NEQ, $value);
129 }
130
131 /**
132 * Creates a lower-than comparison expression with the given arguments.
133 *
134 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
135 * @param mixed $value The value. No automatic quoting/escaping is done.
136 *
137 * @return string
138 */
139 public function lt($fieldName, $value): string
140 {
141 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LT, $value);
142 }
143
144 /**
145 * Creates a lower-than-equal comparison expression with the given arguments.
146 *
147 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
148 * @param mixed $value The value. No automatic quoting/escaping is done.
149 *
150 * @return string
151 */
152 public function lte(string $fieldName, $value): string
153 {
154 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LTE, $value);
155 }
156
157 /**
158 * Creates a greater-than comparison expression with the given arguments.
159 *
160 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
161 * @param mixed $value The value. No automatic quoting/escaping is done.
162 *
163 * @return string
164 */
165 public function gt(string $fieldName, $value): string
166 {
167 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GT, $value);
168 }
169
170 /**
171 * Creates a greater-than-equal comparison expression with the given arguments.
172 *
173 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
174 * @param mixed $value The value. No automatic quoting/escaping is done.
175 *
176 * @return string
177 */
178 public function gte(string $fieldName, $value): string
179 {
180 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GTE, $value);
181 }
182
183 /**
184 * Creates an IS NULL expression with the given arguments.
185 *
186 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
187 *
188 * @return string
189 */
190 public function isNull(string $fieldName): string
191 {
192 return $this->connection->quoteIdentifier($fieldName) . ' IS NULL';
193 }
194
195 /**
196 * Creates an IS NOT NULL expression with the given arguments.
197 *
198 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
199 *
200 * @return string
201 */
202 public function isNotNull(string $fieldName): string
203 {
204 return $this->connection->quoteIdentifier($fieldName) . ' IS NOT NULL';
205 }
206
207 /**
208 * Creates a LIKE() comparison expression with the given arguments.
209 *
210 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
211 * @param mixed $value Argument to be used in LIKE() comparison. No automatic quoting/escaping is done.
212 *
213 * @return string
214 */
215 public function like(string $fieldName, $value): string
216 {
217 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'LIKE', $value);
218 }
219
220 /**
221 * Creates a NOT LIKE() comparison expression with the given arguments.
222 *
223 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
224 * @param mixed $value Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done.
225 *
226 * @return string
227 */
228 public function notLike(string $fieldName, $value): string
229 {
230 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value);
231 }
232
233 /**
234 * Creates a IN () comparison expression with the given arguments.
235 *
236 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
237 * @param string|array $value The placeholder or the array of values to be used by IN() comparison.
238 * No automatic quoting/escaping is done.
239 *
240 * @return string
241 */
242 public function in(string $fieldName, $value): string
243 {
244 return $this->comparison(
245 $this->connection->quoteIdentifier($fieldName),
246 'IN',
247 '(' . implode(', ', (array)$value) . ')'
248 );
249 }
250
251 /**
252 * Creates a NOT IN () comparison expression with the given arguments.
253 *
254 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
255 * @param string|array $value The placeholder or the array of values to be used by NOT IN() comparison.
256 * No automatic quoting/escaping is done.
257 *
258 * @return string
259 */
260 public function notIn(string $fieldName, $value): string
261 {
262 return $this->comparison(
263 $this->connection->quoteIdentifier($fieldName),
264 'NOT IN',
265 '(' . implode(', ', (array)$value) . ')'
266 );
267 }
268
269 /**
270 * Returns a comparison that can find a value in a list field (CSV).
271 *
272 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
273 * @param string $value Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
274 * @return string
275 * @throws \RuntimeException
276 */
277 public function inSet(string $fieldName, string $value): string
278 {
279 if ($value === '') {
280 throw new \InvalidArgumentException(
281 'ExpressionBuilder::inSet() can not be used with an empty string value.',
282 1459696089
283 );
284 }
285
286 if (strpos($value, ',') !== false) {
287 throw new \InvalidArgumentException(
288 'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").',
289 1459696090
290 );
291 }
292
293 switch ($this->connection->getDatabasePlatform()->getName()) {
294 case 'postgresql':
295 case 'pdo_postgresql':
296 return $this->comparison(
297 sprintf(
298 'any(string_to_array(%s, %s))',
299 $this->connection->quoteIdentifier($fieldName),
300 $this->literal(',')
301 ),
302 self::EQ,
303 $value
304 );
305 break;
306 case 'oci8':
307 case 'pdo_oracle':
308 throw new \RuntimeException(
309 'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
310 1459696680
311 );
312 break;
313 case 'sqlsrv':
314 case 'pdo_sqlsrv':
315 throw new \RuntimeException(
316 'FIND_IN_SET support for database platform "SQLServer" not yet implemented.',
317 1459696681
318 );
319 break;
320 default:
321 return sprintf(
322 'FIND_IN_SET(%s, %s)',
323 $value,
324 $this->connection->quoteIdentifier($fieldName)
325 );
326 }
327 }
328
329 /**
330 * Creates a bitwise AND expression with the given arguments.
331 *
332 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
333 * @param int $value Argument to be used in the bitwise AND operation
334 * @return string
335 */
336 public function bitAnd(string $fieldName, int $value): string
337 {
338 switch ($this->connection->getDatabasePlatform()->getName()) {
339 case 'oci8':
340 case 'pdo_oracle':
341 return sprintf(
342 'BITAND(%s, %s)',
343 $this->connection->quoteIdentifier($fieldName),
344 $value
345 );
346 default:
347 return $this->comparison(
348 $this->connection->quoteIdentifier($fieldName),
349 '&',
350 $value
351 );
352 }
353 }
354
355 /**
356 * Creates a MIN expression for the given field/alias.
357 *
358 * @param string $fieldName
359 * @param string|null $alias
360 * @return string
361 */
362 public function min(string $fieldName, string $alias = null): string
363 {
364 return $this->calculation('MIN', $fieldName, $alias);
365 }
366
367 /**
368 * Creates a MAX expression for the given field/alias.
369 *
370 * @param string $fieldName
371 * @param string|null $alias
372 * @return string
373 */
374 public function max(string $fieldName, string $alias = null): string
375 {
376 return $this->calculation('MAX', $fieldName, $alias);
377 }
378
379 /**
380 * Creates a AVG expression for the given field/alias.
381 *
382 * @param string $fieldName
383 * @param string|null $alias
384 * @return string
385 */
386 public function avg(string $fieldName, string $alias = null): string
387 {
388 return $this->calculation('AVG', $fieldName, $alias);
389 }
390
391 /**
392 * Creates a SUM expression for the given field/alias.
393 *
394 * @param string $fieldName
395 * @param string|null $alias
396 * @return string
397 */
398 public function sum(string $fieldName, string $alias = null): string
399 {
400 return $this->calculation('SUM', $fieldName, $alias);
401 }
402
403 /**
404 * Creates a COUNT expression for the given field/alias.
405 *
406 * @param string $fieldName
407 * @param string|null $alias
408 * @return string
409 */
410 public function count(string $fieldName, string $alias = null): string
411 {
412 return $this->calculation('COUNT', $fieldName, $alias);
413 }
414
415 /**
416 * Create a SQL aggregrate function.
417 *
418 * @param string $aggregateName
419 * @param string $fieldName
420 * @param string|null $alias
421 * @return string
422 */
423 protected function calculation(string $aggregateName, string $fieldName, string $alias = null): string
424 {
425 $aggregateSQL = sprintf(
426 '%s(%s)',
427 $aggregateName,
428 $this->connection->quoteIdentifier($fieldName)
429 );
430
431 if (!empty($alias)) {
432 $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias);
433 }
434
435 return $aggregateSQL;
436 }
437
438 /**
439 * Quotes a given input parameter.
440 *
441 * @param mixed $input The parameter to be quoted.
442 * @param string|null $type The type of the parameter.
443 *
444 * @return string
445 */
446 public function literal($input, string $type = null): string
447 {
448 return $this->connection->quote($input, $type);
449 }
450 }