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\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Platforms\TrimMode;
use ‪TYPO3\CMS\Core\Database\Connection;
 
class ‪ExpressionBuilder
{
    public const ‪EQ = '=';
    public const ‪NEQ = '<>';
    public const ‪LT = '<';
    public const ‪LTE = '<=';
    public const ‪GT = '>';
    public const ‪GTE = '>=';
 
    public const ‪QUOTE_NOTHING = 0;
    public const ‪QUOTE_IDENTIFIER = 1;
    public const ‪QUOTE_PARAMETER = 2;
 
    protected ‪$connection;
 
    public function ‪__construct(‪Connection ‪$connection)
    {
        $this->connection = ‪$connection;
    }
 
    public function ‪and(‪CompositeExpression|string|null ...$expressions): ‪CompositeExpression
    {
        return ‪CompositeExpression::and(...$expressions);
    }
 
    public function ‪or(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($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, $value): string
    {
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof PostgreSQLPlatform) {
            // Use ILIKE to mimic case-insensitive search like most people are trained from MySQL/MariaDB.
            return $this->‪comparison($this->connection->quoteIdentifier($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($this->connection->quoteIdentifier($fieldName), 'LIKE', $value)
            . sprintf(' ESCAPE %s', $this->connection->quote('\\'));
    }
 
    public function ‪notLike(string $fieldName, $value): string
    {
        $platform = $this->connection->getDatabasePlatform();
        if ($platform instanceof PostgreSQLPlatform) {
            // Use ILIKE to mimic case-insensitive search like most people are trained from MySQL/MariaDB.
            return $this->‪comparison($this->connection->quoteIdentifier($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($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value)
            . sprintf(' ESCAPE %s', $this->connection->quote('\\'));
    }
 
    public function ‪in(string $fieldName, $value): string
    {
        return $this->‪comparison(
            $this->connection->quoteIdentifier($fieldName),
            'IN',
            '(' . implode(', ', (array)$value) . ')'
        );
    }
 
    public function ‪notIn(string $fieldName, $value): string
    {
        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
            );
        }
 
        switch ($this->connection->getDatabasePlatform()->getName()) {
            case 'postgresql':
            case 'pdo_postgresql':
                return $this->‪comparison(
                    $isColumn ? $value . '::text' : $this->‪literal($this->‪unquoteLiteral((string)$value)),
                    self::EQ,
                    sprintf(
                        'ANY(string_to_array(%s, %s))',
                        $this->connection->quoteIdentifier($fieldName) . '::text',
                        $this->literal(',')
                    )
                );
            case 'oci8':
            case 'pdo_oracle':
                throw new \RuntimeException(
                    'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
                    1459696680
                );
            case 'sqlite':
            case 'sqlite3':
            case 'pdo_sqlite':
                if (str_starts_with($value, ':') || $value === '?') {
                    throw new \InvalidArgumentException(
                        'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
                        1476029421
                    );
                }
                $comparison = 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) . ','
                        )
                );
                return $comparison;
            default:
                return sprintf(
                    'FIND_IN_SET(%s, %s)',
                    $value,
                    $this->connection->quoteIdentifier($fieldName)
                );
        }
    }
 
    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
            );
        }
 
        switch ($this->connection->getDatabasePlatform()->getName()) {
            case 'postgresql':
            case 'pdo_postgresql':
                return $this->‪comparison(
                    $isColumn ? $value . '::text' : $this->‪literal($this->‪unquoteLiteral((string)$value)),
                    self::NEQ,
                    sprintf(
                        'ALL(string_to_array(%s, %s))',
                        $this->connection->quoteIdentifier($fieldName) . '::text',
                        $this->literal(',')
                    )
                );
            case 'oci8':
            case 'pdo_oracle':
                throw new \RuntimeException(
                    'negative FIND_IN_SET support for database platform "Oracle" not yet implemented.',
                    1627573101
                );
            case 'sqlite':
            case 'sqlite3':
            case 'pdo_sqlite':
                if (str_starts_with($value, ':') || $value === '?') {
                    throw new \InvalidArgumentException(
                        'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
                        1627573103
                    );
                }
                $comparison = 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) . ','
                        )
                );
                return $comparison;
            default:
                return sprintf(
                    'NOT FIND_IN_SET(%s, %s)',
                    $value,
                    $this->connection->quoteIdentifier($fieldName)
                );
        }
    }
 
    public function ‪bitAnd(string $fieldName, int $value): string
    {
        switch ($this->connection->getDatabasePlatform()->getName()) {
            case 'oci8':
            case 'pdo_oracle':
                return sprintf(
                    'BITAND(%s, %s)',
                    $this->connection->quoteIdentifier($fieldName),
                    $value
                );
            default:
                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);
    }
 
    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, int $position = TrimMode::UNSPECIFIED, string $char = null)
    {
        return $this->connection->getDatabasePlatform()->getTrimExpression(
            $this->connection->quoteIdentifier($fieldName),
            $position,
            ($char === null ? false : $this->literal($char))
        );
    }
 
    public function ‪literal($input, int $type = ‪Connection::PARAM_STR)
    {
        return $this->connection->quote($input, $type);
    }
 
    protected function ‪unquoteLiteral(string $value): string
    {
        $quoteChar = $this->connection
            ->getDatabasePlatform()
            ->getStringLiteralQuoteCharacter();
 
        $isQuoted = str_starts_with($value, $quoteChar) && str_ends_with($value, $quoteChar);
 
        if ($isQuoted) {
            return str_replace($quoteChar . $quoteChar, $quoteChar, substr($value, 1, -1));
        }
 
        return $value;
    }
}