diff options
8 files changed, 95 insertions, 3 deletions
diff --git a/apps/dav/lib/carddav/carddavbackend.php b/apps/dav/lib/carddav/carddavbackend.php index 56fa652d798..61bdec52479 100644 --- a/apps/dav/lib/carddav/carddavbackend.php +++ b/apps/dav/lib/carddav/carddavbackend.php @@ -776,7 +776,7 @@ class CardDavBackend implements BackendInterface, SyncSupport { $query2->orWhere( $query2->expr()->andX( $query2->expr()->eq('cp.name', $query->createNamedParameter($property)), - $query2->expr()->like('cp.value', $query->createNamedParameter('%' . $this->db->escapeLikeParameter($pattern) . '%')) + $query2->expr()->ilike('cp.value', $query->createNamedParameter('%' . $this->db->escapeLikeParameter($pattern) . '%')) ) ); } diff --git a/apps/dav/tests/unit/carddav/carddavbackendtest.php b/apps/dav/tests/unit/carddav/carddavbackendtest.php index f920eb47b6e..401041d6e39 100644 --- a/apps/dav/tests/unit/carddav/carddavbackendtest.php +++ b/apps/dav/tests/unit/carddav/carddavbackendtest.php @@ -547,8 +547,8 @@ class CardDavBackendTest extends TestCase { ['John', ['FN'], ['John Doe', 'John M. Doe']], ['M. Doe', ['FN'], ['John M. Doe']], ['Do', ['FN'], ['John Doe', 'John M. Doe']], - // check if duplicates are handled correctly - ['John', ['FN', 'CLOUD'], ['John Doe', 'John M. Doe']], + 'check if duplicates are handled correctly' => ['John', ['FN', 'CLOUD'], ['John Doe', 'John M. Doe']], + 'case insensitive' => ['john', ['FN'], ['John Doe', 'John M. Doe']] ]; } diff --git a/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php index 7ab4c03d97c..6fb58ac3c9d 100644 --- a/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php +++ b/lib/private/db/querybuilder/expressionbuilder/expressionbuilder.php @@ -277,6 +277,23 @@ class ExpressionBuilder implements IExpressionBuilder { } /** + * Creates a ILIKE() comparison expression with the given arguments. + * + * @param string $x Field in string format to be inspected by ILIKE() comparison. + * @param mixed $y Argument to be used in ILIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + * @since 9.0.0 + */ + public function iLike($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison("LOWER($x)", 'LIKE', "LOWER($y)"); + } + + /** * Creates a NOT LIKE() comparison expression with the given arguments. * * @param string $x Field in string format to be inspected by NOT LIKE() comparison. diff --git a/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php new file mode 100644 index 00000000000..8164d9623b8 --- /dev/null +++ b/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php @@ -0,0 +1,39 @@ +<?php +/** + * @author Joas Schilling <nickvergessen@owncloud.com> + * + * @copyright Copyright (c) 2016, ownCloud, Inc. + * @license AGPL-3.0 + * + * This code is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License, version 3, + * as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License, version 3, + * along with this program. If not, see <http://www.gnu.org/licenses/> + * + */ + +namespace OC\DB\QueryBuilder\ExpressionBuilder; + + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryBuilder; + +class MySqlExpressionBuilder extends ExpressionBuilder { + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison($x, ' COLLATE utf8_general_ci LIKE', $y); + } + +} diff --git a/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php index 6a6d0f455f6..bd7daa23911 100644 --- a/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php +++ b/lib/private/db/querybuilder/expressionbuilder/ociexpressionbuilder.php @@ -149,4 +149,13 @@ class OCIExpressionBuilder extends ExpressionBuilder { return parent::castColumn($column, $type); } + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return new QueryFunction('REGEXP_LIKE('.$x.', \'^\' || REPLACE('.$y.', \'%\', \'.*\') || \'$\', \'i\')'); + } } diff --git a/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php index 8a0b68db998..ac2d7bf2421 100644 --- a/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php +++ b/lib/private/db/querybuilder/expressionbuilder/pgsqlexpressionbuilder.php @@ -42,4 +42,14 @@ class PgSqlExpressionBuilder extends ExpressionBuilder { return parent::castColumn($column, $type); } + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null) { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison($x, 'ILIKE', $y); + } + } diff --git a/lib/private/db/querybuilder/querybuilder.php b/lib/private/db/querybuilder/querybuilder.php index ff31ffbc043..de803116dc4 100644 --- a/lib/private/db/querybuilder/querybuilder.php +++ b/lib/private/db/querybuilder/querybuilder.php @@ -21,9 +21,11 @@ namespace OC\DB\QueryBuilder; +use Doctrine\DBAL\Platforms\MySqlPlatform; use Doctrine\DBAL\Platforms\PostgreSqlPlatform; use OC\DB\OracleConnection; use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; +use OC\DB\QueryBuilder\ExpressionBuilder\MySqlExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\OCIExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\PgSqlExpressionBuilder; use OCP\DB\QueryBuilder\IQueryBuilder; @@ -91,6 +93,8 @@ class QueryBuilder implements IQueryBuilder { return new OCIExpressionBuilder($this->connection); } else if ($this->connection->getDatabasePlatform() instanceof PostgreSqlPlatform) { return new PgSqlExpressionBuilder($this->connection); + } else if ($this->connection->getDatabasePlatform() instanceof MySqlPlatform) { + return new MySqlExpressionBuilder($this->connection); } else { return new ExpressionBuilder($this->connection); } diff --git a/lib/public/db/querybuilder/iexpressionbuilder.php b/lib/public/db/querybuilder/iexpressionbuilder.php index 4b53a0e0b8b..0ed15bf4398 100644 --- a/lib/public/db/querybuilder/iexpressionbuilder.php +++ b/lib/public/db/querybuilder/iexpressionbuilder.php @@ -264,6 +264,19 @@ interface IExpressionBuilder { public function notLike($x, $y, $type = null); /** + * Creates a ILIKE() comparison expression with the given arguments. + * + * @param string $x Field in string format to be inspected by ILIKE() comparison. + * @param mixed $y Argument to be used in ILIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + * @since 9.0.0 + */ + public function iLike($x, $y, $type = null); + + /** * Creates a IN () comparison expression with the given arguments. * * @param string $x The field in string format to be inspected by IN() comparison. |