ExpressionBuilder.php

Go to the documentation of this file.

<?php
declare(strict_types = 1);
namespace ‪TYPO3\CMS\Core\Database\Query\Expression;
 
/*
 * 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!
 */
 
use Doctrine\DBAL\Platforms\AbstractPlatform;
use ‪TYPO3\CMS\Core\Database\Connection;
 
class ‪ExpressionBuilder
{
    const ‪EQ = '=';
    const ‪NEQ = '<>';
    const ‪LT = '<';
    const ‪LTE = '<=';
    const ‪GT = '>';
    const ‪GTE = '>=';
 
    const ‪QUOTE_NOTHING = 0;
    const ‪QUOTE_IDENTIFIER = 1;
    const ‪QUOTE_PARAMETER = 2;
 
    protected ‪$connection;
 
    public function ‪__construct(‪Connection ‪$connection)
    {
        $this->connection = ‪$connection;
    }
 
    public function ‪andX(...$expressions): ‪CompositeExpression
    {
        return new ‪CompositeExpression(CompositeExpression::TYPE_AND, $expressions);
    }
 
    public function ‪orX(...$expressions): CompositeExpression
    {
        return new CompositeExpression(CompositeExpression::TYPE_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
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), 'LIKE', $value);
    }
 
    public function ‪notLike(string $fieldName, $value): string
    {
        return $this->‪comparison($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value);
    }
 
    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 (strpos($value, ',') !== false) {
            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 'sqlsrv':
            case 'pdo_sqlsrv':
            case 'mssql':
                // See unit and functional tests for details
                if ($isColumn) {
                    $expression = $this->‪orX(
                        $this->‪eq($fieldName, $value),
                        $this->‪like($fieldName, $value . ' + \',%\''),
                        $this->‪like($fieldName, '\'%,\' + ' . $value),
                        $this->‪like($fieldName, '\'%,\' + ' . $value . ' + \',%\'')
                    );
                } else {
                    $likeEscapedValue = str_replace(
                        ['[', '%'],
                        ['[[]', '[%]'],
                        $this->‪unquoteLiteral($value)
                    );
                    $expression = $this->‪orX(
                        $this->‪eq($fieldName, $this->‪literal($this->‪unquoteLiteral((string)$value))),
                        $this->‪like($fieldName, $this->‪literal($likeEscapedValue . ',%')),
                        $this->‪like($fieldName, $this->‪literal('%,' . $likeEscapedValue)),
                        $this->‪like($fieldName, $this->‪literal('%,' . $likeEscapedValue . ',%'))
                    );
                }
                return (string)$expression;
            case 'sqlite':
            case 'sqlite3':
            case 'pdo_sqlite':
                if (strpos($value, ':') === 0 || $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 ‪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 = AbstractPlatform::TRIM_UNSPECIFIED, string $char = null)
    {
        return $this->connection->getDatabasePlatform()->getTrimExpression(
            $this->connection->quoteIdentifier($fieldName),
            $position,
            ($char === null ? false : $this->literal($char))
        );
    }
 
    public function ‪literal($input, string $type = null)
    {
        return $this->connection->quote($input, $type);
    }
 
    protected function ‪unquoteLiteral(string $value): string
    {
        $quoteChar = $this->connection
            ->getDatabasePlatform()
            ->getStringLiteralQuoteCharacter();
 
        $isQuoted = strpos($value, $quoteChar) === 0 && strpos(strrev($value), $quoteChar) === 0;
 
        if ($isQuoted) {
            return str_replace($quoteChar . $quoteChar, $quoteChar, substr($value, 1, -1));
        }
 
        return $value;
    }
}