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(',')
                    )
                );
                break;
            case 'oci8':
            case 'pdo_oracle':
                throw new \RuntimeException(
                    'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
                    1459696680
                );
                break;
            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
                    );
                }

                return $this->comparison(
                    implode('||', [
                        $this->literal(','),
                        $this->connection->quoteIdentifier($fieldName),
                        $this->literal(','),
                    ]),
                    'LIKE',
                    $this->literal(
                        '%,' . $this->unquoteLiteral($value) . ',%'
                    )
                );
                break;
            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;
    }
}