[FEATURE] Add possibility to create TRIM expression with Doctrine DBAL 55/51255/5
authorOliver Hader <oliver@typo3.org>
Tue, 10 Jan 2017 22:01:00 +0000 (23:01 +0100)
committerSusanne Moog <susanne.moog@typo3.org>
Wed, 11 Jan 2017 19:58:35 +0000 (20:58 +0100)
This adds the possibility to create TRIM expressions using the
ExpressionBuilder like shown in the following example:

$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->trim($fieldName),
    ExpressionBuilder::EQ,
    $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
);

Resolves: #79262
Releases: master
Change-Id: I7813e8a1b0a38ec36797dfbb4fdb02ab78ebe8b2
Reviewed-on: https://review.typo3.org/51255
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Tested-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Reviewed-by: Susanne Moog <susanne.moog@typo3.org>
Tested-by: Susanne Moog <susanne.moog@typo3.org>
typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php
typo3/sysext/core/Documentation/Changelog/master/Feature-79262-AddPossibilityToCreateTRIMExpressionWithDoctrineDBAL.rst [new file with mode: 0644]
typo3/sysext/core/Tests/Unit/Database/Query/Expression/ExpressionBuilderTest.php

index ee1fac7..a75cbaf 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Core\Database\Query\Expression;
  * The TYPO3 project - inspiring people to share!
  */
 
+use Doctrine\DBAL\Platforms\AbstractPlatform;
 use TYPO3\CMS\Core\Database\Connection;
 
 /**
@@ -435,7 +436,7 @@ class ExpressionBuilder
     }
 
     /**
-     * Create a SQL aggregrate function.
+     * Create a SQL aggregate function.
      *
      * @param string $aggregateName
      * @param string $fieldName
@@ -458,6 +459,23 @@ class ExpressionBuilder
     }
 
     /**
+     * Creates a TRIM expression for the given field.
+     *
+     * @param string $fieldName Field name to build expression for
+     * @param int $position Either constant out of LEADING, TRAILING, BOTH
+     * @param string $char Character to be trimmed (defaults to space)
+     * @return string
+     */
+    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))
+        );
+    }
+
+    /**
      * Quotes a given input parameter.
      *
      * @param mixed $input The parameter to be quoted.
diff --git a/typo3/sysext/core/Documentation/Changelog/master/Feature-79262-AddPossibilityToCreateTRIMExpressionWithDoctrineDBAL.rst b/typo3/sysext/core/Documentation/Changelog/master/Feature-79262-AddPossibilityToCreateTRIMExpressionWithDoctrineDBAL.rst
new file mode 100644 (file)
index 0000000..5fe7b8c
--- /dev/null
@@ -0,0 +1,36 @@
+.. include:: ../../Includes.txt
+
+==============================================================================
+Feature: #79262 - Add possibility to create TRIM expression with Doctrine DBAL
+==============================================================================
+
+See :issue:`79262`
+
+Description
+===========
+
+The possibility to create TRIM expressions using Doctrine DBAL has been integrated.
+However, when using this in comparisons, ExpressionBuilder::comparison() has to be
+invoked explicitly - otherwise the created TRIM expression would be quoted if e.g.
+used with ExpressionBuilder::eq().
+
+.. code-block:: php
+
+    $queryBuilder->expr()->comparison(
+        $queryBuilder->expr()->trim($fieldName),
+        ExpressionBuilder::EQ,
+        $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
+    );
+
+The call to :php:`$queryBuilder->expr()-trim()` can be one of the following:
+
+* :php:`trim('fieldName')`
+  results in :sql:`TRIM("tableName"."fieldName")`
+* :php:`trim('fieldName', AbstractPlatform::TRIM_LEADING, 'x')`
+  results in :sql:`TRIM(LEADING "x" FROM "tableName"."fieldName")`
+* :php:`trim('fieldName', AbstractPlatform::TRIM_TRAILING, 'x')`
+  results in :sql:`TRIM(TRAILING "x" FROM "tableName"."fieldName")`
+* :php:`trim('fieldName', AbstractPlatform::TRIM_BOTH, 'x')`
+  results in :sql:`TRIM(BOTH "x" FROM "tableName"."fieldName")`
+
+.. index:: Database
\ No newline at end of file
index 17c286c..9845228 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Core\Tests\Unit\Database\Query;
  * The TYPO3 project - inspiring people to share!
  */
 
+use Doctrine\DBAL\Platforms\AbstractPlatform;
 use Prophecy\Argument;
 use Prophecy\Prophecy\ObjectProphecy;
 use TYPO3\CMS\Core\Database\Connection;
@@ -471,6 +472,93 @@ class ExpressionBuilderTest extends \TYPO3\CMS\Components\TestingFramework\Core\
     /**
      * @test
      */
+    public function trimQuotesIdentifierWithDefaultValues()
+    {
+        $platform = new MockPlatform();
+        $this->connectionProphet->getDatabasePlatform(Argument::cetera())
+            ->shouldBeCalled()
+            ->willReturn($platform);
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())
+            ->shouldBeCalled()
+            ->will(
+                function ($args) use ($platform) {
+                    return $platform->quoteIdentifier($args[0]);
+                }
+            );
+
+        $this->assertSame(
+            'TRIM("tableName"."fieldName")',
+            $this->subject->trim('tableName.fieldName')
+        );
+    }
+
+    /**
+     * @return array
+     */
+    public function trimQuotesIdentifierDataProvider()
+    {
+        return  [
+            'trim leading character' => [
+                AbstractPlatform::TRIM_LEADING,
+                'x',
+                'TRIM(LEADING "x" FROM "tableName"."fieldName")'
+            ],
+            'trim trailing character' => [
+                AbstractPlatform::TRIM_TRAILING,
+                'x',
+                'TRIM(TRAILING "x" FROM "tableName"."fieldName")',
+            ],
+            'trim character' => [
+                AbstractPlatform::TRIM_BOTH,
+                'x',
+                'TRIM(BOTH "x" FROM "tableName"."fieldName")',
+            ],
+            'trim space' => [
+                AbstractPlatform::TRIM_BOTH,
+                ' ',
+                'TRIM(BOTH " " FROM "tableName"."fieldName")',
+            ]
+        ];
+    }
+
+    /**
+     * @param int $position
+     * @param string $char
+     * @param string $expected
+     *
+     * @test
+     * @dataProvider trimQuotesIdentifierDataProvider
+     */
+    public function trimQuotesIdentifier(int $position, string $char, string $expected)
+    {
+        $platform = new MockPlatform();
+        $this->connectionProphet->getDatabasePlatform(Argument::cetera())
+            ->shouldBeCalled()
+            ->willReturn($platform);
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())
+            ->shouldBeCalled()
+            ->will(
+                function ($args) use ($platform) {
+                    return $platform->quoteIdentifier($args[0]);
+                }
+            );
+        $this->connectionProphet->quote(Argument::cetera())
+            ->shouldBeCalled()
+            ->will(
+                function ($args) {
+                    return '"' . $args[0] . '"';
+                }
+            );
+
+        $this->assertSame(
+            $expected,
+            $this->subject->trim('tableName.fieldName', $position, $char)
+        );
+    }
+
+    /**
+     * @test
+     */
     public function literalQuotesValue()
     {
         $this->connectionProphet->quote('aField', 'Doctrine\DBAL\Types\StringType')