diff options
7 files changed, 207 insertions, 11 deletions
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index dfc0d0a6d61..37518bda3ec 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -26,18 +26,23 @@ namespace OC\DB\QueryBuilder\FunctionBuilder; use OC\DB\QueryBuilder\QueryFunction; use OC\DB\QueryBuilder\QuoteHelper; use OCP\DB\QueryBuilder\IFunctionBuilder; +use OCP\DB\QueryBuilder\IQueryBuilder; use OCP\DB\QueryBuilder\IQueryFunction; +use OCP\IDBConnection; class FunctionBuilder implements IFunctionBuilder { + /** @var IDBConnection */ + protected $connection; + + /** @var IQueryBuilder */ + protected $queryBuilder; + /** @var QuoteHelper */ protected $helper; - /** - * ExpressionBuilder constructor. - * - * @param QuoteHelper $helper - */ - public function __construct(QuoteHelper $helper) { + public function __construct(IDBConnection $connection, IQueryBuilder $queryBuilder, QuoteHelper $helper) { + $this->connection = $connection; + $this->queryBuilder = $queryBuilder; $this->helper = $helper; } @@ -49,6 +54,11 @@ class FunctionBuilder implements IFunctionBuilder { return new QueryFunction('CONCAT(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); } + public function groupConcat($expr, ?string $separator = ','): IQueryFunction { + $separator = $this->connection->quote($separator); + return new QueryFunction('GROUP_CONCAT(' . $this->helper->quoteColumnName($expr) . ' SEPARATOR ' . $separator . ')'); + } + public function substring($input, $start, $length = null): IQueryFunction { if ($length) { return new QueryFunction('SUBSTR(' . $this->helper->quoteColumnName($input) . ', ' . $this->helper->quoteColumnName($start) . ', ' . $this->helper->quoteColumnName($length) . ')'); diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php index 5581207a166..3871070a583 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -72,4 +72,14 @@ class OCIFunctionBuilder extends FunctionBuilder { return parent::least($x, $y); } + + public function groupConcat($expr, ?string $separator = ','): IQueryFunction { + $orderByClause = ' WITHIN GROUP(ORDER BY NULL)'; + if (is_null($separator)) { + return new QueryFunction('LISTAGG(' . $this->helper->quoteColumnName($expr) . ')' . $orderByClause); + } + + $separator = $this->connection->quote($separator); + return new QueryFunction('LISTAGG(' . $this->helper->quoteColumnName($expr) . ', ' . $separator . ')' . $orderByClause); + } } diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php index 853c65ac32a..0b22775acc8 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php @@ -24,10 +24,22 @@ namespace OC\DB\QueryBuilder\FunctionBuilder; use OC\DB\QueryBuilder\QueryFunction; +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 groupConcat($expr, ?string $separator = ','): IQueryFunction { + $castedExpression = $this->queryBuilder->expr()->castColumn($expr, IQueryBuilder::PARAM_STR); + + if (is_null($separator)) { + return new QueryFunction('string_agg(' . $castedExpression . ')'); + } + + $separator = $this->connection->quote($separator); + return new QueryFunction('string_agg(' . $castedExpression . ', ' . $separator . ')'); + } } diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php index 19cff52546b..1067f8f0925 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -31,6 +31,11 @@ class SqliteFunctionBuilder extends FunctionBuilder { return new QueryFunction('(' . $this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y) . ')'); } + public function groupConcat($expr, ?string $separator = ','): IQueryFunction { + $separator = $this->connection->quote($separator); + return new QueryFunction('GROUP_CONCAT(' . $this->helper->quoteColumnName($expr) . ', ' . $separator . ')'); + } + public function greatest($x, $y): IQueryFunction { return new QueryFunction('MAX(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); } diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php index 89265c74fae..a362ff8016e 100644 --- a/lib/private/DB/QueryBuilder/QueryBuilder.php +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -155,16 +155,16 @@ class QueryBuilder implements IQueryBuilder { */ public function func() { if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) { - return new OCIFunctionBuilder($this->helper); + return new OCIFunctionBuilder($this->connection, $this, $this->helper); } if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { - return new SqliteFunctionBuilder($this->helper); + return new SqliteFunctionBuilder($this->connection, $this, $this->helper); } if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) { - return new PgSqlFunctionBuilder($this->helper); + return new PgSqlFunctionBuilder($this->connection, $this, $this->helper); } - return new FunctionBuilder($this->helper); + return new FunctionBuilder($this->connection, $this, $this->helper); } /** diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php index 33d0fbe3b1a..1e8d8c89f81 100644 --- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php +++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php @@ -44,7 +44,7 @@ interface IFunctionBuilder { * Combines two input strings * * @param string|ILiteral|IParameter|IQueryFunction $x The first input string - * @param string|ILiteral|IParameter|IQueryFunction $y The seccond input string + * @param string|ILiteral|IParameter|IQueryFunction $y The second input string * * @return IQueryFunction * @since 12.0.0 @@ -52,6 +52,22 @@ interface IFunctionBuilder { public function concat($x, $y): IQueryFunction; /** + * Returns a string which is the concatenation of all non-NULL values of X + * + * Usage examples: + * + * groupConcat('column') -- with comma as separator (default separator) + * + * groupConcat('column', ';') -- with different separator + * + * @param string|IQueryFunction $expr The expression to group + * @param string|null $separator The separator + * @return IQueryFunction + * @since 24.0.0 + */ + public function groupConcat($expr, ?string $separator = ','): IQueryFunction; + + /** * Takes a substring from the input string * * @param string|ILiteral|IParameter|IQueryFunction $input The input string diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php index 71ae3d5c7f6..7ff26f7e144 100644 --- a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php +++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php @@ -54,6 +54,149 @@ class FunctionBuilderTest extends TestCase { $this->assertEquals('foobar', $column); } + protected function clearDummyData(): void { + $delete = $this->connection->getQueryBuilder(); + + $delete->delete('appconfig') + ->where($delete->expr()->eq('appid', $delete->createNamedParameter('group_concat'))); + $delete->executeStatement(); + } + + protected function addDummyData(): void { + $this->clearDummyData(); + $insert = $this->connection->getQueryBuilder(); + + $insert->insert('appconfig') + ->setValue('appid', $insert->createNamedParameter('group_concat')) + ->setValue('configvalue', $insert->createNamedParameter('unittest')) + ->setValue('configkey', $insert->createParameter('value')); + + $insert->setParameter('value', '1'); + $insert->executeStatement(); + $insert->setParameter('value', '3'); + $insert->executeStatement(); + $insert->setParameter('value', '2'); + $insert->executeStatement(); + } + + public function testGroupConcatWithoutSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $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 testGroupConcatWithSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '#')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString('#', $column); + $actual = explode('#', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testGroupConcatWithSingleQuoteSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '\'')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals('1\'2\'3', $column); + } + + public function testGroupConcatWithDoubleQuoteSeparator(): void { + $this->addDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('configkey', '"')) + ->from('appconfig') + ->where($query->expr()->eq('appid', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals('1"2"3', $column); + } + + protected function clearIntDummyData(): void { + $delete = $this->connection->getQueryBuilder(); + + $delete->delete('systemtag') + ->where($delete->expr()->eq('name', $delete->createNamedParameter('group_concat'))); + $delete->executeStatement(); + } + + protected function addIntDummyData(): void { + $this->clearIntDummyData(); + $insert = $this->connection->getQueryBuilder(); + + $insert->insert('systemtag') + ->setValue('name', $insert->createNamedParameter('group_concat')) + ->setValue('visibility', $insert->createNamedParameter(1)) + ->setValue('editable', $insert->createParameter('value')); + + $insert->setParameter('value', 1); + $insert->executeStatement(); + $insert->setParameter('value', 2); + $insert->executeStatement(); + $insert->setParameter('value', 3); + $insert->executeStatement(); + } + + public function testIntGroupConcatWithoutSeparator(): void { + $this->addIntDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('editable')) + ->from('systemtag') + ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString(',', $column); + $actual = explode(',', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + + public function testIntGroupConcatWithSeparator(): void { + $this->addIntDummyData(); + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->groupConcat('editable', '#')) + ->from('systemtag') + ->where($query->expr()->eq('name', $query->createNamedParameter('group_concat'))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertStringContainsString('#', $column); + $actual = explode('#', $column); + $this->assertEqualsCanonicalizing([1,2,3], $actual); + } + public function testMd5() { $query = $this->connection->getQueryBuilder(); |