From 7dc3eb1e9e9a2cf6b52dc17986b4bbca395f0998 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=B4me=20Chilliet?= Date: Thu, 24 Mar 2022 11:12:12 +0100 Subject: Use getLengthExpression to measure field length instead of like MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Côme Chilliet --- apps/user_ldap/lib/Migration/Version1120Date20210917155206.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php index d2b440c4dfe..87a8f07fbe1 100644 --- a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php +++ b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php @@ -127,9 +127,10 @@ class Version1120Date20210917155206 extends SimpleMigrationStep { protected function getSelectQuery(string $table): IQueryBuilder { $qb = $this->dbc->getQueryBuilder(); + $lengthExpr = $this->dbc->getDatabasePlatform()->getLengthExpression('owncloud_name'); $qb->select('owncloud_name', 'directory_uuid') ->from($table) - ->where($qb->expr()->like('owncloud_name', $qb->createNamedParameter(str_repeat('_', 65) . '%'), Types::STRING)); + ->where($qb->expr()->gt($qb->createFunction($lengthExpr), '64', IQueryBuilder::PARAM_INT)); return $qb; } -- cgit v1.2.3 From efebbacca4d5a5045271eeecd209c8a0b4253598 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=B4me=20Chilliet?= Date: Thu, 24 Mar 2022 16:17:37 +0100 Subject: Add octetLength and charLength to function builder, and tests MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Côme Chilliet --- .../Migration/Version1120Date20210917155206.php | 3 +- .../FunctionBuilder/FunctionBuilder.php | 12 ++++++ .../FunctionBuilder/SqliteFunctionBuilder.php | 6 +++ lib/public/DB/QueryBuilder/IFunctionBuilder.php | 18 +++++++++ tests/lib/DB/QueryBuilder/FunctionBuilderTest.php | 46 ++++++++++++++++++++++ 5 files changed, 83 insertions(+), 2 deletions(-) diff --git a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php index 87a8f07fbe1..bca390441d7 100644 --- a/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php +++ b/apps/user_ldap/lib/Migration/Version1120Date20210917155206.php @@ -127,10 +127,9 @@ class Version1120Date20210917155206 extends SimpleMigrationStep { protected function getSelectQuery(string $table): IQueryBuilder { $qb = $this->dbc->getQueryBuilder(); - $lengthExpr = $this->dbc->getDatabasePlatform()->getLengthExpression('owncloud_name'); $qb->select('owncloud_name', 'directory_uuid') ->from($table) - ->where($qb->expr()->gt($qb->createFunction($lengthExpr), '64', IQueryBuilder::PARAM_INT)); + ->where($qb->expr()->gt($qb->func()->octetLength('owncloud_name'), '64', IQueryBuilder::PARAM_INT)); return $qb; } diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index 03630ea14a3..c0f07d37406 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -94,6 +94,18 @@ class FunctionBuilder implements IFunctionBuilder { return new QueryFunction('COUNT(' . $quotedName . ')' . $alias); } + public function octetLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTHB(' . $quotedName . ')' . $alias); + } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias); + } + public function max($field): IQueryFunction { return new QueryFunction('MAX(' . $this->helper->quoteColumnName($field) . ')'); } diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php index fe700075a82..4e9e83a49d2 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -48,4 +48,10 @@ class SqliteFunctionBuilder extends FunctionBuilder { public function least($x, $y): IQueryFunction { return new QueryFunction('MIN(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); } + + public function octetLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTH(CAST(' . $quotedName . ' as BLOB))' . $alias); + } } diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php index 04c5cbd07bd..d4edc8ea9f8 100644 --- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php +++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php @@ -123,6 +123,24 @@ interface IFunctionBuilder { */ public function count($count = '', $alias = ''): IQueryFunction; + /** + * @param string|ILiteral|IParameter|IQueryFunction $field The input to be measured + * @param string $alias Alias for the length + * + * @return IQueryFunction + * @since 24.0.0 + */ + public function octetLength($field, $alias = ''): IQueryFunction; + + /** + * @param string|ILiteral|IParameter|IQueryFunction $field The input to be measured + * @param string $alias Alias for the length + * + * @return IQueryFunction + * @since 24.0.0 + */ + public function charLength($field, $alias = ''): IQueryFunction; + /** * Takes the maximum of all rows in a column * diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php index 3f4b8bb7524..6b13051c2c1 100644 --- a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php +++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php @@ -336,6 +336,52 @@ class FunctionBuilderTest extends TestCase { $this->assertGreaterThan(1, $column); } + public function octetLengthProvider() { + return [ + ['', 0], + ['foobar', 6], + ['fé', 3], + ['šđčćž', 10], + ]; + } + + /** + * @dataProvider octetLengthProvider + */ + public function testOctetLength(string $str, int $bytes) { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->octetLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($bytes, $column); + } + + public function charLengthProvider() { + return [ + ['', 0], + ['foobar', 6], + ['fé', 2], + ['šđčćž', 5], + ]; + } + + /** + * @dataProvider charLengthProvider + */ + public function testCharLength(string $str, int $bytes) { + $query = $this->connection->getQueryBuilder(); + + $query->select($query->func()->charLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + + $result = $query->execute(); + $column = $result->fetchOne(); + $result->closeCursor(); + $this->assertEquals($bytes, $column); + } + private function setUpMinMax($value) { $query = $this->connection->getQueryBuilder(); -- cgit v1.2.3 From bfd09a0669ff4c87218fdeea33b0cd464692b805 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=B4me=20Chilliet?= Date: Thu, 24 Mar 2022 17:02:27 +0100 Subject: Avoid select with no FROM clause for Oracle in tests MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Côme Chilliet --- tests/lib/DB/QueryBuilder/FunctionBuilderTest.php | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php index 6b13051c2c1..08392b09d8d 100644 --- a/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php +++ b/tests/lib/DB/QueryBuilder/FunctionBuilderTest.php @@ -352,6 +352,8 @@ class FunctionBuilderTest extends TestCase { $query = $this->connection->getQueryBuilder(); $query->select($query->func()->octetLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + $query->from('appconfig') + ->setMaxResults(1); $result = $query->execute(); $column = $result->fetchOne(); @@ -375,6 +377,8 @@ class FunctionBuilderTest extends TestCase { $query = $this->connection->getQueryBuilder(); $query->select($query->func()->charLength($query->createNamedParameter($str, IQueryBuilder::PARAM_STR))); + $query->from('appconfig') + ->setMaxResults(1); $result = $query->execute(); $column = $result->fetchOne(); -- cgit v1.2.3 From 25af856ed26d0a4ef542b9d621b5e3512198ad99 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=B4me=20Chilliet?= Date: Thu, 24 Mar 2022 17:22:41 +0100 Subject: Use OCTET_LENGTH which is more common than LENGTHB MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Côme Chilliet --- lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php | 2 +- lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php | 6 ++++++ 2 files changed, 7 insertions(+), 1 deletion(-) diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index c0f07d37406..e121374a763 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -97,7 +97,7 @@ class FunctionBuilder implements IFunctionBuilder { public function octetLength($field, $alias = ''): IQueryFunction { $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; $quotedName = $this->helper->quoteColumnName($field); - return new QueryFunction('LENGTHB(' . $quotedName . ')' . $alias); + return new QueryFunction('OCTET_LENGTH(' . $quotedName . ')' . $alias); } public function charLength($field, $alias = ''): IQueryFunction { diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php index 43ecf599eba..1a9eae3c21f 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -91,4 +91,10 @@ class OCIFunctionBuilder extends FunctionBuilder { $separator = $this->connection->quote($separator); return new QueryFunction('LISTAGG(' . $this->helper->quoteColumnName($expr) . ', ' . $separator . ')' . $orderByClause); } + + public function octetLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTHB(' . $quotedName . ')' . $alias); + } } -- cgit v1.2.3 From 7407a324d94afa7009a4efa3a8fbe71734e6ea85 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=B4me=20Chilliet?= Date: Mon, 28 Mar 2022 00:01:17 +0200 Subject: Fix LENGTH function name across databases MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Côme Chilliet --- lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php | 2 +- lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php | 6 ++++++ .../DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php | 6 ++++++ 3 files changed, 13 insertions(+), 1 deletion(-) diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index e121374a763..e0a7549a0ad 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -103,7 +103,7 @@ class FunctionBuilder implements IFunctionBuilder { public function charLength($field, $alias = ''): IQueryFunction { $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; $quotedName = $this->helper->quoteColumnName($field); - return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias); + return new QueryFunction('CHAR_LENGTH(' . $quotedName . ')' . $alias); } public function max($field): IQueryFunction { diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php index 1a9eae3c21f..a9844ec3373 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -97,4 +97,10 @@ class OCIFunctionBuilder extends FunctionBuilder { $quotedName = $this->helper->quoteColumnName($field); return new QueryFunction('LENGTHB(' . $quotedName . ')' . $alias); } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias); + } } diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php index 4e9e83a49d2..bb97d2e29f9 100644 --- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -54,4 +54,10 @@ class SqliteFunctionBuilder extends FunctionBuilder { $quotedName = $this->helper->quoteColumnName($field); return new QueryFunction('LENGTH(CAST(' . $quotedName . ' as BLOB))' . $alias); } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias); + } } -- cgit v1.2.3