[TASK] Implement DBAL inSet() for SQLite 06/50006/3
authorOliver Hader <oliver@typo3.org>
Thu, 22 Sep 2016 16:29:53 +0000 (18:29 +0200)
committerChristian Kuhn <lolli@schwarzbu.ch>
Sun, 9 Oct 2016 12:38:33 +0000 (14:38 +0200)
DBAL's ExpressionBuilder::inSet() currently does not support FIND_IN_SET
for SQLite which is also not supported by the DBMS natively. To achieve
the behavior the field's value is surrounded by commas and then compared
likewise to the accordant value expansion.

Resolves: #78045
Releases: master
Change-Id: Idceab827c06e4290bf5a68fb672a43110364af7d
Reviewed-on: https://review.typo3.org/50006
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: MichaƂ Jankiewicz <michal.jankiewicz@gmail.com>
Reviewed-by: Anja Leichsenring <aleichsenring@ab-softlab.de>
Tested-by: Anja Leichsenring <aleichsenring@ab-softlab.de>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php
typo3/sysext/core/Tests/Unit/Database/Query/Expression/ExpressionBuilderTest.php

index 3ac0b67..52ba984 100644 (file)
@@ -317,6 +317,21 @@ class ExpressionBuilder
                     1459696681
                 );
                 break;
+            case 'sqlite':
+            case 'sqlite3':
+            case 'pdo_sqlite':
+                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)',
@@ -447,4 +462,21 @@ class ExpressionBuilder
     {
         return $this->connection->quote($input, $type);
     }
+
+    /**
+     * Unquote a string literal. Used to unquote values for internal platform adjustments.
+     *
+     * @param string $value The value to be unquoted
+     * @return string The unquoted value
+     */
+    protected function unquoteLiteral(string $value): string
+    {
+        $quoteChar = $this->connection
+            ->getDatabasePlatform()
+            ->getStringLiteralQuoteCharacter();
+
+        $unquotedValue = trim($value, $quoteChar);
+
+        return str_replace($quoteChar . $quoteChar, $quoteChar, $unquotedValue);
+    }
 }
index 93899d3..c2e30fd 100644 (file)
@@ -275,6 +275,27 @@ class ExpressionBuilderTest extends UnitTestCase
     /**
      * @test
      */
+    public function inSetForSQLite()
+    {
+        $databasePlatform = $this->prophesize(MockPlatform::class);
+        $databasePlatform->getName()->willReturn('sqlite');
+        $databasePlatform->getStringLiteralQuoteCharacter()->willReturn("'");
+
+        $this->connectionProphet->quote(',', Argument::cetera())->shouldBeCalled()->willReturn("','");
+        $this->connectionProphet->quote('%,1,%', Argument::cetera())->shouldBeCalled()->willReturn("'%,1,%'");
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            return '"' . $args[0] . '"';
+        });
+
+        $this->connectionProphet->getDatabasePlatform()->willReturn($databasePlatform->reveal());
+
+        $result = $this->subject->inSet('aField', "'1'");
+
+        $this->assertSame('\',\'||"aField"||\',\' LIKE \'%,1,%\'', $result);
+    }
+    /**
+     * @test
+     */
     public function defaultBitwiseAnd()
     {
         $databasePlatform = $this->prophesize(MockPlatform::class);