From 4537eaac41dc9ddf7e73302935a61219d60b8f52 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Thomas=20M=C3=BCller?= Date: Mon, 22 Feb 2016 19:46:37 +0100 Subject: [PATCH] Add ILIKE support to querybuilder --- .../expressionbuilder/expressionbuilder.php | 17 ++++++++ .../mysqlexpressionbuilder.php | 39 +++++++++++++++++++ .../ociexpressionbuilder.php | 9 +++++ .../pgsqlexpressionbuilder.php | 10 +++++ lib/private/db/querybuilder/querybuilder.php | 4 ++ .../db/querybuilder/iexpressionbuilder.php | 13 +++++++ 6 files changed, 92 insertions(+) create mode 100644 lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php 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 @@ -276,6 +276,23 @@ class ExpressionBuilder implements IExpressionBuilder { return $this->expressionBuilder->like($x, $y); } + /** + * 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. * diff --git a/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php b/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php new file mode 100644 index 00000000000..df5f1a4b701 --- /dev/null +++ b/lib/private/db/querybuilder/expressionbuilder/mysqlexpressionbuilder.php @@ -0,0 +1,39 @@ + + * + * @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 + * + */ + +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->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..a508ba140b5 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 '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..fecfaeb698f 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->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 @@ -263,6 +263,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. * -- 2.39.5