ExpressionBuilder.php

Go to the documentation of this file.

<?php
 
declare(strict_types=1);
 
/*
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */
 
namespace ‪TYPO3\CMS\Core\Database\Query\Expression;
 
use Doctrine\DBAL\Connection as DoctrineConnection;
use Doctrine\DBAL\Platforms\MariaDBPlatform as DoctrineMariaDBPlatform;
use Doctrine\DBAL\Platforms\MySQLPlatform as DoctrineMySQLPlatform;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform as DoctrinePostgreSQLPlatform;
use Doctrine\DBAL\Platforms\SQLitePlatform as DoctrineSQLitePlatform;
use Doctrine\DBAL\Platforms\TrimMode;
use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder;
 
class ‪ExpressionBuilder extends DoctrineExpressionBuilder
{
    public function ‪__construct(protected readonly DoctrineConnection $connection)
    {
        // parent::__construct() skipped by intention, otherwise the private property
        // nature of the parent constructor will prevent access in extended methods.
    }
 
    public function ‪and(
        ‪CompositeExpression|\Doctrine\DBAL\Query\Expression\‪CompositeExpression|string|null ...$expressions,
    ): ‪CompositeExpression {
        return ‪CompositeExpression::and(...$expressions);
    }
 
    public function ‪or(‪CompositeExpression|\Doctrine\DBAL\Query\Expression\‪CompositeExpression|string|null ...$expressions): ‪CompositeExpression
    {
        return ‪CompositeExpression::or(...$expressions);
    }
 
    public function ‪comparison($leftExpression, string $operator, $rightExpression): string
    {
        return $leftExpression . ' ' . $operator . ' ' . $rightExpression;
    }
 
    public function ‪eq(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::EQ, $value);
    }
 
    public function ‪neq(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::NEQ, $value);
    }
 
    public function ‪lt(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::LT, $value);
    }
 
    public function ‪lte(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::LTE, $value);
    }
 
    public function ‪gt(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::GT, $value);
    }
 
    public function ‪gte(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), static::GTE, $value);
    }
 
    public function ‪isNull(string $fieldName): string
    {
        return $this->connection->quoteIdentifier($fieldName) . ' IS NULL';
    }
 
    public function ‪isNotNull(string $fieldName): string
    {
        return $this->connection->quoteIdentifier($fieldName) . ' IS NOT NULL';
    }
 
    public function ‪like(string $fieldName, mixed $value, ?string $escapeChar = null): string
    {
        $fieldName = $this->connection->quoteIdentifier($fieldName);
        $platform = $this->connection->getDatabasePlatform();
        $escapeChar ??= '\\';
        if ($escapeChar !== null) {
            $escapeChar = $this->connection->quote($escapeChar);
        }
        if ($platform instanceof DoctrinePostgreSQLPlatform) {
            // Use ILIKE to mimic case-insensitive search like most people are trained from MySQL/MariaDB.
            return $this->‪comparison($fieldName, 'ILIKE', $value);
        }
        // Note: SQLite does not properly work with non-ascii letters as search word for case-insensitive
        //       matching, UPPER() and LOWER() have the same issue, it only works with ascii letters.
        //       See: https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt
        return $this->‪comparison($fieldName, 'LIKE', $value)
            . ($escapeChar !== null && $escapeChar !== '' ? sprintf(' ESCAPE %s', $escapeChar) : '');
    }
 
    public function ‪notLike(string $fieldName, mixed $value, ?string $escapeChar = null): string
    {
        $fieldName = $this->connection->quoteIdentifier($fieldName);
        $platform = $this->connection->getDatabasePlatform();
        $escapeChar ??= '\\';
        if ($escapeChar !== null) {
            $escapeChar = $this->connection->quote($escapeChar);
        }
        if ($platform instanceof DoctrinePostgreSQLPlatform) {
            // Use ILIKE to mimic case-insensitive search like most people are trained from MySQL/MariaDB.
            return $this->‪comparison($fieldName, 'NOT ILIKE', $value);
        }
        // Note: SQLite does not properly work with non-ascii letters as search word for case-insensitive
        //       matching, UPPER() and LOWER() have the same issue, it only works with ascii letters.
        //       See: https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt
        return $this->‪comparison($fieldName, 'NOT LIKE', $value)
            . ($escapeChar !== null && $escapeChar !== '' ? sprintf(' ESCAPE %s', $escapeChar) : '');
    }
 
    public function ‪in(string $fieldName, $value): string
    {
        if ($value === []) {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::in() can not be used with an empty array value.',
                1701857902
            );
        }
        if ($value === '') {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::in() can not be used with an empty string value.',
                1701857903
            );
        }
        return $this->‪comparison(
            $this->connection->quoteIdentifier($fieldName),
            'IN',
            '(' . implode(', ', (array)$value) . ')'
        );
    }
 
    public function ‪notIn(string $fieldName, $value): string
    {
        if ($value === []) {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::notIn() can not be used with an empty array value.',
                1701857904
            );
        }
        if ($value === '') {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::notIn() can not be used with an empty string value.',
                1701857905
            );
        }
        return $this->‪comparison(
            $this->connection->quoteIdentifier($fieldName),
            'NOT IN',
            '(' . implode(', ', (array)$value) . ')'
        );
    }
 
    public function ‪inSet(string $fieldName, string $value, bool $isColumn = false): string
    {
        if ($value === '') {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::inSet() can not be used with an empty string value.',
                1459696089
            );
        }
        if (str_contains($value, ',')) {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").',
                1459696090
            );
        }
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrinePostgreSQLPlatform) {
            return $this->‪comparison(
                $isColumn ? $value . '::text' : $this->‪literal($this->‪unquoteLiteral($value)),
                self::EQ,
                sprintf(
                    'ANY(string_to_array(%s, %s))',
                    $this->connection->quoteIdentifier($fieldName) . '::text',
                    $this->literal(',')
                )
            );
        }
        if ($platform instanceof DoctrineSQLitePlatform) {
            if (str_starts_with($value, ':') || $value === '?') {
                throw new \InvalidArgumentException(
                    'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
                    1476029421
                );
            }
            return sprintf(
                'instr(%s, %s)',
                implode(
                    '||',
                    [
                        $this->‪literal(','),
                        $this->connection->quoteIdentifier($fieldName),
                        $this->literal(','),
                    ]
                ),
                $isColumn
                    ? implode(
                        '||',
                        [
                            $this->‪literal(','),
                            // do not explicitly quote value as it is expected to be
                            // quoted by the caller
                            'cast(' . $value . ' as text)',
                            $this->‪literal(','),
                        ]
                    )
                    : $this->‪literal(
                        ',' . $this->‪unquoteLiteral($value) . ','
                    )
            );
        }
        if ($platform instanceof DoctrineMariaDBPlatform || $platform instanceof DoctrineMySQLPlatform) {
            return sprintf(
                'FIND_IN_SET(%s, %s)',
                $value,
                $this->connection->quoteIdentifier($fieldName)
            );
        }
        throw new \RuntimeException(
            sprintf('FIND_IN_SET support for database platform "%s" not yet implemented.', $platform::class),
            1459696680
        );
    }
 
    public function ‪notInSet(string $fieldName, string $value, bool $isColumn = false): string
    {
        if ($value === '') {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::notInSet() can not be used with an empty string value.',
                1627573099
            );
        }
        if (str_contains($value, ',')) {
            throw new \InvalidArgumentException(
                'ExpressionBuilder::notInSet() can not be used with values that contain a comma (",").',
                1627573100
            );
        }
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrinePostgreSQLPlatform) {
            return $this->‪comparison(
                $isColumn ? $value . '::text' : $this->‪literal($this->‪unquoteLiteral($value)),
                self::NEQ,
                sprintf(
                    'ALL(string_to_array(%s, %s))',
                    $this->connection->quoteIdentifier($fieldName) . '::text',
                    $this->literal(',')
                )
            );
        }
        if ($platform instanceof DoctrineSQLitePlatform) {
            if (str_starts_with($value, ':') || $value === '?') {
                throw new \InvalidArgumentException(
                    'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
                    1627573103
                );
            }
            return sprintf(
                'instr(%s, %s) = 0',
                implode(
                    '||',
                    [
                        $this->‪literal(','),
                        $this->connection->quoteIdentifier($fieldName),
                        $this->literal(','),
                    ]
                ),
                $isColumn
                    ? implode(
                        '||',
                        [
                            $this->‪literal(','),
                            // do not explicitly quote value as it is expected to be
                            // quoted by the caller
                            'cast(' . $value . ' as text)',
                            $this->‪literal(','),
                        ]
                    )
                    : $this->‪literal(
                        ',' . $this->‪unquoteLiteral($value) . ','
                    )
            );
        }
        if ($platform instanceof DoctrineMariaDBPlatform || $platform instanceof DoctrineMySQLPlatform) {
            return sprintf(
                'NOT FIND_IN_SET(%s, %s)',
                $value,
                $this->connection->quoteIdentifier($fieldName)
            );
        }
        throw new \RuntimeException(
            sprintf('negative FIND_IN_SET support for database platform "%s" not yet implemented.', $platform::class),
            1627573101
        );
    }
 
    public function ‪bitAnd(string $fieldName, int $value): string
    {
        return $this->‪comparison(
            $this->connection->quoteIdentifier($fieldName),
            '&',
            $value
        );
    }
 
    public function ‪min(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('MIN', $fieldName, $alias);
    }
 
    public function ‪max(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('MAX', $fieldName, $alias);
    }
 
    public function ‪avg(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('AVG', $fieldName, $alias);
    }
 
    public function ‪sum(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('SUM', $fieldName, $alias);
    }
 
    public function ‪count(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('COUNT', $fieldName, $alias);
    }
 
    public function ‪length(string $fieldName, string $alias = null): string
    {
        return $this->‪calculation('LENGTH', $fieldName, $alias);
    }
 
    public function ‪as(string $expression, string $asIdentifier = ''): string
    {
        if (‪trim($this->‪trimIdentifierQuotes(‪trim($expression))) === '') {
            throw new \InvalidArgumentException(
                sprintf('Value or expression must be provided as first argument for "%s"', __METHOD__),
                1709826333
            );
        }
        $asIdentifier = ‪trim($this->‪trimIdentifierQuotes(‪trim($this->‪unquoteLiteral($asIdentifier))));
        if ($asIdentifier !== '') {
            $asIdentifier = ' AS ' . $this->connection->quoteIdentifier($asIdentifier);
        }
        return $expression . $asIdentifier;
    }
 
    public function ‪concat(string ...$parts): string
    {
        return $this->connection->getDatabasePlatform()->getConcatExpression(...$parts);
    }
 
    public function ‪castVarchar(string $value, int $length = 255, string $asIdentifier = ''): string
    {
        $platform = $this->connection->getDatabasePlatform();
        $pattern = match (true) {
            $platform instanceof DoctrinePostgreSQLPlatform => '(%s::%s(%s))',
            default => '(CAST(%s AS %s(%s)))'
        };
        $type = match (true) {
            // MariaDB added VARCHAR as alias for CHAR to the CAST function, therefore we
            // need to use CHAR here - albeit this still creates a VARCHAR type as long as
            // length is not ZERO.
            // https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
            $platform instanceof DoctrineMySQLPlatform => 'CHAR',
            default => 'VARCHAR'
        };
        return $this->‪as(sprintf($pattern, $value, $type, $length), $asIdentifier);
    }
 
    public function ‪castInt(string $value, string $asIdentifier = ''): string
    {
        // @todo Consider to add a flag to allow unsigned integer casting, except for PostgresSQL
        //       which does not support unsigned integer type at all.
        $type = 'SIGNED INTEGER';
        $pattern = '(CAST(%s AS %s))';
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrinePostgreSQLPlatform) {
            $pattern = '%s::%s';
            $type = 'INTEGER';
        }
        return $this->‪as(sprintf($pattern, $value, $type), $asIdentifier);
    }
 
    protected function ‪calculation(string $aggregateName, string $fieldName, string $alias = null): string
    {
        $aggregateSQL = sprintf(
            '%s(%s)',
            $aggregateName,
            $this->connection->quoteIdentifier($fieldName)
        );
 
        if (!empty($alias)) {
            $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias);
        }
 
        return $aggregateSQL;
    }
 
    public function ‪trim(string $fieldName, TrimMode $position = TrimMode::UNSPECIFIED, ?string $char = null): string
    {
        return $this->connection->getDatabasePlatform()->getTrimExpression(
            $this->connection->quoteIdentifier($fieldName),
            $position,
            ($char === null ? null : $this->literal($char))
        );
    }
 
    public function ‪repeat(int|string $numberOfRepeats, string $value, string $asIdentifier = ''): string
    {
        $numberOfRepeats = $this->‪castInt((string)$numberOfRepeats);
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineSQLitePlatform) {
            $pattern = "replace(printf('%%.' || %s || 'c', '/'),'/', %s)";
            return $this->‪as(
                sprintf($pattern, $numberOfRepeats, $value),
                $asIdentifier
            );
        }
        $pattern = 'REPEAT(%s, %s)';
        return $this->‪as(
            sprintf($pattern, $value, $numberOfRepeats),
            $asIdentifier,
        );
    }
 
    public function ‪space(int|string $numberOfSpaces, string $asIdentifier = ''): string
    {
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineMariaDBPlatform || $platform instanceof DoctrineMySQLPlatform) {
            // Use `SPACE()` method supported by MySQL and MariaDB
            $pattern = 'SPACE(%s)';
            $numberOfSpaces = $this->‪castInt((string)$numberOfSpaces);
            return $this->‪as(
                sprintf($pattern, $numberOfSpaces),
                $asIdentifier,
            );
        }
        // Emulate `SPACE()` by using the `repeat()` expression.
        return $this->‪repeat($numberOfSpaces, $this->connection->quote(' '), $asIdentifier);
    }
 
    public function ‪left(int|string $length, string $value, string $asIdentifier = ''): string
    {
        $length = (is_string($length)) ? $this->‪castInt($length) : (string)$length;
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineSQLitePlatform) {
            // SQLite does not support `LEFT()`, use `SUBSTRING()` instead. Weirdly, we need to increment the length by
            // one to get the correct substring length.
            return $this->‪as(
                $platform->getSubstringExpression($value, '0', $length . ' + 1'),
                $asIdentifier,
            );
        }
        return $this->‪as(
            sprintf('LEFT(%s, %s)', $value, $length),
            $asIdentifier,
        );
    }
 
    public function ‪right(int|string $length, string $value, string $asIdentifier = ''): string
    {
        if ($asIdentifier !== '') {
            $asIdentifier = ' AS ' . $this->connection->quoteIdentifier($this->‪unquoteLiteral($this->‪trimIdentifierQuotes($asIdentifier)));
        }
        $length = (is_string($length)) ? $this->‪castInt($length) : (string)$length;
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineSQLitePlatform) {
            // SQLite does not support `RIGHT()`, use `SUBSTRING()` instead.
            return $this->connection->getDatabasePlatform()
                ->getSubstringExpression($value, $length . ' * -1') . $asIdentifier;
        }
        return sprintf('RIGHT(%s, %s)', $value, $length) . $asIdentifier;
    }
 
    public function ‪leftPad(string $value, int|string $length, string $paddingValue, string $asIdentifier = ''): string
    {
        if (‪trim($this->‪unquoteLiteral($paddingValue), ' ') === '') {
            throw new \InvalidArgumentException(
                sprintf('Empty $paddingValue provided for "%s".', __METHOD__),
                1709658914
            );
        }
        if (strlen(‪trim($this->‪unquoteLiteral($paddingValue), ' ')) > 1) {
            throw new \InvalidArgumentException(
                sprintf('Invalid $paddingValue "%s" provided for "%s". Exactly one char allowed.', $paddingValue, __METHOD__),
                1709659006
            );
        }
        // PostgresSQL is really picky about types when calling functions, therefore we ensure that the value or
        // expression result is ensured to be string-typed by casting it to a varchar result for all platforms.
        $value = $this->‪castVarchar($value);
        $paddingValue = $this->connection->quote($this->‪unquoteLiteral($paddingValue));
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineSQLitePlatform) {
            // SQLite does not support `LPAD()`, therefore we need to build up a generic nested method construct to
            // mimic that method for now. Basically, the length is checked and either the substring up to the length
            // returned OR the substring from the right side up to the length on the concentrated repeated-value with
            // the value to cut of the overlapped prefixed repeated value.
            $repeat = $this->‪repeat(
                $length,
                $paddingValue
            );
            $pattern = 'IIF(LENGTH(%s) >= %s, %s, %s)';
            return $this->‪as(sprintf(
                $pattern,
                // Value and length for the length check to consider which part to use.
                $value,
                $this->‪castInt((string)$length),
                // Return substring with $length from left side to mimic `LPAD()` behaviour of other platforms.
                $platform->getSubstringExpression($value, '0', $this->castInt((string)$length) . ' + 1'),
                // Concatenate `repeat + value` and fetch the substring with length from the right side,
                // so we cut of overlapping prefixed repeat placeholders.
                $this->right($length, $this->concat($repeat, $value))
            ), $asIdentifier);
        }
        return $this->‪as(sprintf('LPAD(%s, %s, %s)', $value, $this->‪castInt((string)$length), $paddingValue), $asIdentifier);
    }
 
    public function ‪rightPad(string $value, int|string $length, string $paddingValue, string $asIdentifier = ''): string
    {
        if (‪trim($this->‪unquoteLiteral($paddingValue), ' ') === '') {
            throw new \InvalidArgumentException(
                sprintf('Empty $paddingValue provided for "%s".', __METHOD__),
                1709664589
            );
        }
        if (strlen(‪trim($this->‪unquoteLiteral($paddingValue), ' ')) > 1) {
            throw new \InvalidArgumentException(
                sprintf('Invalid $paddingValue "%s" provided for "%s". Exactly one char allowed.', $paddingValue, __METHOD__),
                1709664598
            );
        }
        // PostgresSQL is really picky about types when calling functions, therefore we ensure that the value or
        // expression result is ensured to be string-type by casting it to a varchar result for all platforms.
        $value = $this->‪castVarchar($value);
        $paddingValue = $this->connection->quote($this->‪unquoteLiteral($paddingValue));
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof DoctrineSQLitePlatform) {
            $repeat = $this->‪repeat(
                $length,
                $paddingValue
            );
            $pattern = 'IIF(LENGTH(%s) >= %s, %s, %s)';
            return $this->‪as(sprintf(
                $pattern,
                // Value and length for the length check to consider which part to use.
                $value,
                $this->‪castInt((string)$length),
                // Return substring with $length from left side to mimic `RPAD()` behaviour of other platforms.
                // Note: `RPAD()` cuts the value from the left like LPAD(), which is brain melt. Therefore,
                // this is adopted here to be concise with this behaviour.
                $this->‪left($length, $value),
                // Concatenate `repeat + value` and fetch the substring with length from the right side, so we
                // cut off overlapping prefixed repeat placeholders.
                $this->‪left($length, $this->‪castVarchar($this->‪concat($value, $this->‪castVarchar($repeat))))
            ), $asIdentifier);
        }
        return $this->‪as(sprintf('RPAD(%s, %s, %s)', $value, $this->‪castInt((string)$length), $paddingValue), $asIdentifier);
    }
 
    public function ‪literal(string $input): string
    {
        return $this->connection->quote($input);
    }
 
    protected function ‪unquoteLiteral(string $value): string
    {
        if (str_starts_with($value, "'") && str_ends_with($value, "'")) {
            $map = [
                "''" => "'",
            ];
            if ($this->connection->getDatabasePlatform() instanceof DoctrineMySQLPlatform) {
                // MySQL needs escaped backslashes for quoted value, which we need to revert in case of unquoting.
                $map['\\\\'] = '\\';
            }
            return str_replace(array_keys($map), array_values($map), substr($value, 1, -1));
        }
        return $value;
    }
 
    private function ‪trimIdentifierQuotes(string ‪$identifier): string
    {
        return str_replace(['`', '"', '[', ']'], '', ‪$identifier);
    }
}