[BUGFIX] Fix inSet implementation for sqlite
[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 Doctrine\DBAL\Platforms\AbstractPlatform;
19 use TYPO3\CMS\Core\Database\Connection;
20
21 /**
22 * ExpressionBuilder class is responsible to dynamically create SQL query parts.
23 *
24 * It takes care building query conditions while ensuring table and column names
25 * are quoted within the created expressions / SQL fragments. It is a facade to
26 * the actual Doctrine ExpressionBuilder.
27 *
28 * The ExpressionBuilder is used within the context of the QueryBuilder to ensure
29 * queries are being build based on the requirements of the database platform in
30 * use.
31 */
32 class ExpressionBuilder
33 {
34 const EQ = '=';
35 const NEQ = '<>';
36 const LT = '<';
37 const LTE = '<=';
38 const GT = '>';
39 const GTE = '>=';
40
41 const QUOTE_NOTHING = 0;
42 const QUOTE_IDENTIFIER = 1;
43 const QUOTE_PARAMETER = 2;
44
45 /**
46 * The DBAL Connection.
47 *
48 * @var Connection
49 */
50 protected $connection;
51
52 /**
53 * Initializes a new ExpressionBuilder
54 *
55 * @param Connection $connection
56 */
57 public function __construct(Connection $connection)
58 {
59 $this->connection = $connection;
60 }
61
62 /**
63 * Creates a conjunction of the given boolean expressions
64 *
65 * @param mixed,... $expressions Optional clause. Requires at least one defined when converting to string.
66 *
67 * @return CompositeExpression
68 */
69 public function andX(...$expressions): CompositeExpression
70 {
71 return new CompositeExpression(CompositeExpression::TYPE_AND, $expressions);
72 }
73
74 /**
75 * Creates a disjunction of the given boolean expressions.
76 *
77 * @param mixed,... $expressions Optional clause. Requires at least one defined when converting to string.
78 *
79 * @return CompositeExpression
80 */
81 public function orX(...$expressions): CompositeExpression
82 {
83 return new CompositeExpression(CompositeExpression::TYPE_OR, $expressions);
84 }
85
86 /**
87 * Creates a comparison expression.
88 *
89 * @param mixed $leftExpression The left expression.
90 * @param string $operator One of the ExpressionBuilder::* constants.
91 * @param mixed $rightExpression The right expression.
92 *
93 * @return string
94 */
95 public function comparison($leftExpression, string $operator, $rightExpression): string
96 {
97 return $leftExpression . ' ' . $operator . ' ' . $rightExpression;
98 }
99
100 /**
101 * Creates an equality comparison expression with the given arguments.
102 *
103 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
104 * @param mixed $value The value. No automatic quoting/escaping is done.
105 *
106 * @return string
107 */
108 public function eq(string $fieldName, $value): string
109 {
110 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::EQ, $value);
111 }
112
113 /**
114 * Creates a non equality comparison expression with the given arguments.
115 * First argument is considered the left expression and the second is the right expression.
116 * When converted to string, it will generated a <left expr> <> <right expr>. Example:
117 *
118 * [php]
119 * // u.id <> 1
120 * $q->where($q->expr()->neq('u.id', '1'));
121 *
122 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
123 * @param mixed $value The value. No automatic quoting/escaping is done.
124 *
125 * @return string
126 */
127 public function neq(string $fieldName, $value): string
128 {
129 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::NEQ, $value);
130 }
131
132 /**
133 * Creates a lower-than comparison expression with the given arguments.
134 *
135 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
136 * @param mixed $value The value. No automatic quoting/escaping is done.
137 *
138 * @return string
139 */
140 public function lt($fieldName, $value): string
141 {
142 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LT, $value);
143 }
144
145 /**
146 * Creates a lower-than-equal comparison expression with the given arguments.
147 *
148 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
149 * @param mixed $value The value. No automatic quoting/escaping is done.
150 *
151 * @return string
152 */
153 public function lte(string $fieldName, $value): string
154 {
155 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LTE, $value);
156 }
157
158 /**
159 * Creates a greater-than comparison expression with the given arguments.
160 *
161 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
162 * @param mixed $value The value. No automatic quoting/escaping is done.
163 *
164 * @return string
165 */
166 public function gt(string $fieldName, $value): string
167 {
168 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GT, $value);
169 }
170
171 /**
172 * Creates a greater-than-equal comparison expression with the given arguments.
173 *
174 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
175 * @param mixed $value The value. No automatic quoting/escaping is done.
176 *
177 * @return string
178 */
179 public function gte(string $fieldName, $value): string
180 {
181 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GTE, $value);
182 }
183
184 /**
185 * Creates an IS NULL expression with the given arguments.
186 *
187 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
188 *
189 * @return string
190 */
191 public function isNull(string $fieldName): string
192 {
193 return $this->connection->quoteIdentifier($fieldName) . ' IS NULL';
194 }
195
196 /**
197 * Creates an IS NOT NULL expression with the given arguments.
198 *
199 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
200 *
201 * @return string
202 */
203 public function isNotNull(string $fieldName): string
204 {
205 return $this->connection->quoteIdentifier($fieldName) . ' IS NOT NULL';
206 }
207
208 /**
209 * Creates a LIKE() comparison expression with the given arguments.
210 *
211 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
212 * @param mixed $value Argument to be used in LIKE() comparison. No automatic quoting/escaping is done.
213 *
214 * @return string
215 */
216 public function like(string $fieldName, $value): string
217 {
218 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'LIKE', $value);
219 }
220
221 /**
222 * Creates a NOT LIKE() comparison expression with the given arguments.
223 *
224 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
225 * @param mixed $value Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done.
226 *
227 * @return string
228 */
229 public function notLike(string $fieldName, $value): string
230 {
231 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value);
232 }
233
234 /**
235 * Creates a IN () comparison expression with the given arguments.
236 *
237 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
238 * @param string|array $value The placeholder or the array of values to be used by IN() comparison.
239 * No automatic quoting/escaping is done.
240 *
241 * @return string
242 */
243 public function in(string $fieldName, $value): string
244 {
245 return $this->comparison(
246 $this->connection->quoteIdentifier($fieldName),
247 'IN',
248 '(' . implode(', ', (array)$value) . ')'
249 );
250 }
251
252 /**
253 * Creates a NOT IN () comparison expression with the given arguments.
254 *
255 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
256 * @param string|array $value The placeholder or the array of values to be used by NOT IN() comparison.
257 * No automatic quoting/escaping is done.
258 *
259 * @return string
260 */
261 public function notIn(string $fieldName, $value): string
262 {
263 return $this->comparison(
264 $this->connection->quoteIdentifier($fieldName),
265 'NOT IN',
266 '(' . implode(', ', (array)$value) . ')'
267 );
268 }
269
270 /**
271 * Returns a comparison that can find a value in a list field (CSV).
272 *
273 * @param string $fieldName The field name. Will be quoted according to database platform automatically.
274 * @param string $value Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
275 * @param bool $isColumn Set when the value to compare is a column on a table to activate casting
276 * @return string
277 * @throws \InvalidArgumentException
278 * @throws \RuntimeException
279 */
280 public function inSet(string $fieldName, string $value, bool $isColumn = false): string
281 {
282 if ($value === '') {
283 throw new \InvalidArgumentException(
284 'ExpressionBuilder::inSet() can not be used with an empty string value.',
285 1459696089
286 );
287 }
288
289 if (strpos($value, ',') !== false) {
290 throw new \InvalidArgumentException(
291 'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").',
292 1459696090
293 );
294 }
295
296 switch ($this->connection->getDatabasePlatform()->getName()) {
297 case 'postgresql':
298 case 'pdo_postgresql':
299 return $this->comparison(
300 $isColumn ? $value . '::text' : $this->literal($this->unquoteLiteral((string)$value)),
301 self::EQ,
302 sprintf(
303 'ANY(string_to_array(%s, %s))',
304 $this->connection->quoteIdentifier($fieldName) . '::text',
305 $this->literal(',')
306 )
307 );
308 break;
309 case 'oci8':
310 case 'pdo_oracle':
311 throw new \RuntimeException(
312 'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
313 1459696680
314 );
315 break;
316 case 'sqlsrv':
317 case 'pdo_sqlsrv':
318 case 'mssql':
319 // See unit and functional tests for details
320 if ($isColumn) {
321 $expression = $this->orX(
322 $this->eq($fieldName, $value),
323 $this->like($fieldName, $value . ' + \',%\''),
324 $this->like($fieldName, '\'%,\' + ' . $value),
325 $this->like($fieldName, '\'%,\' + ' . $value . ' + \',%\'')
326 );
327 } else {
328 $likeEscapedValue = str_replace(
329 ['[', '%'],
330 ['[[]', '[%]'],
331 $this->unquoteLiteral($value)
332 );
333 $expression = $this->orX(
334 $this->eq($fieldName, $this->literal($this->unquoteLiteral((string)$value))),
335 $this->like($fieldName, $this->literal($likeEscapedValue . ',%')),
336 $this->like($fieldName, $this->literal('%,' . $likeEscapedValue)),
337 $this->like($fieldName, $this->literal('%,' . $likeEscapedValue . ',%'))
338 );
339 }
340 return (string)$expression;
341 case 'sqlite':
342 case 'sqlite3':
343 case 'pdo_sqlite':
344 if (strpos($value, ':') === 0 || $value === '?') {
345 throw new \InvalidArgumentException(
346 'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
347 1476029421
348 );
349 }
350 $comparison = sprintf(
351 'instr(%s, %s)',
352 implode(
353 '||',
354 [
355 $this->literal(','),
356 $this->connection->quoteIdentifier($fieldName),
357 $this->literal(','),
358 ]
359 ),
360 $isColumn ?
361 implode(
362 '||',
363 [
364 $this->literal(','),
365 // do not explicitly quote value as it is expected to be
366 // quoted by the caller
367 'cast(' . $value . ' as text)',
368 $this->literal(','),
369 ]
370 )
371 : $this->literal(
372 ',' . $this->unquoteLiteral($value) . ','
373 )
374 );
375 return $comparison;
376 break;
377 default:
378 return sprintf(
379 'FIND_IN_SET(%s, %s)',
380 $value,
381 $this->connection->quoteIdentifier($fieldName)
382 );
383 }
384 }
385
386 /**
387 * Creates a bitwise AND expression with the given arguments.
388 *
389 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically.
390 * @param int $value Argument to be used in the bitwise AND operation
391 * @return string
392 */
393 public function bitAnd(string $fieldName, int $value): string
394 {
395 switch ($this->connection->getDatabasePlatform()->getName()) {
396 case 'oci8':
397 case 'pdo_oracle':
398 return sprintf(
399 'BITAND(%s, %s)',
400 $this->connection->quoteIdentifier($fieldName),
401 $value
402 );
403 default:
404 return $this->comparison(
405 $this->connection->quoteIdentifier($fieldName),
406 '&',
407 $value
408 );
409 }
410 }
411
412 /**
413 * Creates a MIN expression for the given field/alias.
414 *
415 * @param string $fieldName
416 * @param string|null $alias
417 * @return string
418 */
419 public function min(string $fieldName, string $alias = null): string
420 {
421 return $this->calculation('MIN', $fieldName, $alias);
422 }
423
424 /**
425 * Creates a MAX expression for the given field/alias.
426 *
427 * @param string $fieldName
428 * @param string|null $alias
429 * @return string
430 */
431 public function max(string $fieldName, string $alias = null): string
432 {
433 return $this->calculation('MAX', $fieldName, $alias);
434 }
435
436 /**
437 * Creates a AVG expression for the given field/alias.
438 *
439 * @param string $fieldName
440 * @param string|null $alias
441 * @return string
442 */
443 public function avg(string $fieldName, string $alias = null): string
444 {
445 return $this->calculation('AVG', $fieldName, $alias);
446 }
447
448 /**
449 * Creates a SUM expression for the given field/alias.
450 *
451 * @param string $fieldName
452 * @param string|null $alias
453 * @return string
454 */
455 public function sum(string $fieldName, string $alias = null): string
456 {
457 return $this->calculation('SUM', $fieldName, $alias);
458 }
459
460 /**
461 * Creates a COUNT expression for the given field/alias.
462 *
463 * @param string $fieldName
464 * @param string|null $alias
465 * @return string
466 */
467 public function count(string $fieldName, string $alias = null): string
468 {
469 return $this->calculation('COUNT', $fieldName, $alias);
470 }
471
472 /**
473 * Creates a LENGTH expression for the given field/alias.
474 *
475 * @param string $fieldName
476 * @param string|null $alias
477 * @return string
478 */
479 public function length(string $fieldName, string $alias = null): string
480 {
481 return $this->calculation('LENGTH', $fieldName, $alias);
482 }
483
484 /**
485 * Create a SQL aggregate function.
486 *
487 * @param string $aggregateName
488 * @param string $fieldName
489 * @param string|null $alias
490 * @return string
491 */
492 protected function calculation(string $aggregateName, string $fieldName, string $alias = null): string
493 {
494 $aggregateSQL = sprintf(
495 '%s(%s)',
496 $aggregateName,
497 $this->connection->quoteIdentifier($fieldName)
498 );
499
500 if (!empty($alias)) {
501 $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias);
502 }
503
504 return $aggregateSQL;
505 }
506
507 /**
508 * Creates a TRIM expression for the given field.
509 *
510 * @param string $fieldName Field name to build expression for
511 * @param int $position Either constant out of LEADING, TRAILING, BOTH
512 * @param string $char Character to be trimmed (defaults to space)
513 * @return string
514 */
515 public function trim(string $fieldName, int $position = AbstractPlatform::TRIM_UNSPECIFIED, string $char = null)
516 {
517 return $this->connection->getDatabasePlatform()->getTrimExpression(
518 $this->connection->quoteIdentifier($fieldName),
519 $position,
520 ($char === null ? false : $this->literal($char))
521 );
522 }
523
524 /**
525 * Quotes a given input parameter.
526 *
527 * @param mixed $input The parameter to be quoted.
528 * @param string|null $type The type of the parameter.
529 *
530 * @return mixed Often string, but also int or float or similar depending on $input and platform
531 */
532 public function literal($input, string $type = null)
533 {
534 return $this->connection->quote($input, $type);
535 }
536
537 /**
538 * Unquote a string literal. Used to unquote values for internal platform adjustments.
539 *
540 * @param string $value The value to be unquoted
541 * @return string The unquoted value
542 */
543 protected function unquoteLiteral(string $value): string
544 {
545 $quoteChar = $this->connection
546 ->getDatabasePlatform()
547 ->getStringLiteralQuoteCharacter();
548
549 $isQuoted = strpos($value, $quoteChar) === 0 && strpos(strrev($value), $quoteChar) === 0;
550
551 if ($isQuoted) {
552 return str_replace($quoteChar . $quoteChar, $quoteChar, substr($value, 1, -1));
553 }
554
555 return $value;
556 }
557 }