diff options
6 files changed, 87 insertions, 15 deletions
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index 37518bda3ec..03630ea14a3 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -50,8 +50,13 @@ class FunctionBuilder implements IFunctionBuilder { return new QueryFunction('MD5(' . $this->helper->quoteColumnName($input) . ')'); } - public function concat($x, $y): IQueryFunction { - return new QueryFunction('CONCAT(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('CONCAT(%s)', implode(', ', $list))); } public function groupConcat($expr, ?string $separator = ','): IQueryFunction { diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php index 3871070a583..43ecf599eba 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -73,6 +73,15 @@ class OCIFunctionBuilder extends FunctionBuilder { return parent::least($x, $y); } + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); + } + public function groupConcat($expr, ?string $separator = ','): IQueryFunction { $orderByClause = ' WITHIN GROUP(ORDER BY NULL)'; if (is_null($separator)) { diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php index 0b22775acc8..444f6aa83a4 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php @@ -28,8 +28,13 @@ use OCP\DB\QueryBuilder\IQueryBuilder; use OCP\DB\QueryBuilder\IQueryFunction; class PgSqlFunctionBuilder extends FunctionBuilder { - public function concat($x, $y): IQueryFunction { - return new QueryFunction('(' . $this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y) . ')'); + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->queryBuilder->expr()->castColumn($item, IQueryBuilder::PARAM_STR); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); } public function groupConcat($expr, ?string $separator = ','): IQueryFunction { diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php index 1067f8f0925..fe700075a82 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -27,8 +27,13 @@ use OC\DB\QueryBuilder\QueryFunction; use OCP\DB\QueryBuilder\IQueryFunction; class SqliteFunctionBuilder extends FunctionBuilder { - public function concat($x, $y): IQueryFunction { - return new QueryFunction('(' . $this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y) . ')'); + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); } public function groupConcat($expr, ?string $separator = ','): IQueryFunction { diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php index 1e8d8c89f81..04c5cbd07bd 100644 --- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php +++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php @@ -43,13 +43,13 @@ interface IFunctionBuilder { /** * Combines two input strings * - * @param string|ILiteral|IParameter|IQueryFunction $x The first input string - * @param string|ILiteral|IParameter|IQueryFunction $y The second input string + * @param string|ILiteral|IParameter|IQueryFunction $x Expressions or literal strings + * @param string|ILiteral|IParameter|IQueryFunction ...$exprs Expressions or literal strings * * @return IQueryFunction * @since 12.0.0 */ - public function concat($x, $y): IQueryFunction; + public function concat($x, ...$expr): IQueryFunction; /** * Returns a string which is the concatenation of all non-NULL values of X diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php index 7ff26f7e144..3f4b8bb7524 100644 --- a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php +++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php @@ -41,17 +41,62 @@ class FunctionBuilderTest extends TestCase { $this->connection = \OC::$server->getDatabaseConnection(); } - public function testConcat() { + /** + * @dataProvider providerTestConcatString + */ + public function testConcatString($closure) { $query = $this->connection->getQueryBuilder(); + [$real, $arguments, $return] = $closure($query); + if ($real) { + $this->addDummyData(); + $query->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + } - $query->select($query->func()->concat($query->createNamedParameter('foo'), new Literal("'bar'"))); + $query->select($query->func()->concat(...$arguments)); $query->from('appconfig') ->setMaxResults(1); $result = $query->execute(); $column = $result->fetchOne(); $result->closeCursor(); - $this->assertEquals('foobar', $column); + $this->assertEquals($return, $column); + } + + public function providerTestConcatString(): array { + return [ + '1 column: string param unicode' => + [function ($q) { + return [false, [$q->createNamedParameter('👍')], '👍']; + }], + '2 columns: string param and string param' => + [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->createNamedParameter('bar')], 'foobar']; + }], + '2 columns: string param and int literal' => + [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->expr()->literal(1)], 'foo1']; + }], + '2 columns: string param and string literal' => + [function ($q) { + return [false, [$q->createNamedParameter('foo'), $q->expr()->literal('bar')], 'foobar']; + }], + '2 columns: string real and int literal' => + [function ($q) { + return [true, ['configkey', $q->expr()->literal(2)], '12']; + }], + '4 columns: string literal' => + [function ($q) { + return [false, [$q->expr()->literal('foo'), $q->expr()->literal('bar'), $q->expr()->literal('foo'), $q->expr()->literal('bar')], 'foobarfoobar']; + }], + '4 columns: int literal' => + [function ($q) { + return [false, [$q->expr()->literal(1), $q->expr()->literal(2), $q->expr()->literal(3), $q->expr()->literal(4)], '1234']; + }], + '5 columns: string param with special chars used in the function' => + [function ($q) { + return [false, [$q->createNamedParameter("b"), $q->createNamedParameter("'"), $q->createNamedParameter('||'), $q->createNamedParameter(','), $q->createNamedParameter('a')], "b'||,a"]; + }], + ]; } protected function clearDummyData(): void { @@ -90,7 +135,6 @@ class FunctionBuilderTest extends TestCase { $result = $query->execute(); $column = $result->fetchOne(); $result->closeCursor(); - $this->assertEquals('1,2,3', $column); $this->assertStringContainsString(',', $column); $actual = explode(',', $column); $this->assertEqualsCanonicalizing([1,2,3], $actual); @@ -123,7 +167,9 @@ class FunctionBuilderTest extends TestCase { $result = $query->execute(); $column = $result->fetchOne(); $result->closeCursor(); - $this->assertEquals('1\'2\'3', $column); + $this->assertStringContainsString("'", $column); + $actual = explode("'", $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); } public function testGroupConcatWithDoubleQuoteSeparator(): void { @@ -137,7 +183,9 @@ class FunctionBuilderTest extends TestCase { $result = $query->execute(); $column = $result->fetchOne(); $result->closeCursor(); - $this->assertEquals('1"2"3', $column); + $this->assertStringContainsString('"', $column); + $actual = explode('"', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); } protected function clearIntDummyData(): void { |