From 4279b13270630ec1d49e91f0d4a4cbefb915a32d Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Mon, 16 Jan 2017 15:31:04 +0100 Subject: Add function builder to the query builder Signed-off-by: Robin Appelman --- .../FunctionBuilder/FunctionBuilder.php | 55 ++++++++++++++++++++++ .../FunctionBuilder/OCIFunctionBuilder.php | 29 ++++++++++++ .../FunctionBuilder/PgSqlFunctionBuilder.php | 29 ++++++++++++ .../FunctionBuilder/SqliteFunctionBuilder.php | 29 ++++++++++++ lib/private/DB/QueryBuilder/QueryBuilder.php | 30 ++++++++++++ 5 files changed, 172 insertions(+) create mode 100644 lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php create mode 100644 lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php create mode 100644 lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php create mode 100644 lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php (limited to 'lib/private/DB') diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php new file mode 100644 index 00000000000..61d32d09a62 --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -0,0 +1,55 @@ + + * + * @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\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OC\DB\QueryBuilder\QuoteHelper; +use OCP\DB\QueryBuilder\IFunctionBuilder; + +class FunctionBuilder implements IFunctionBuilder { + /** @var QuoteHelper */ + protected $helper; + + /** + * ExpressionBuilder constructor. + * + * @param QuoteHelper $helper + */ + public function __construct(QuoteHelper $helper) { + $this->helper = $helper; + } + + public function md5($input) { + return new QueryFunction('MD5(' . $this->helper->quoteColumnName($input) . ')'); + } + + public function concat($x, $y) { + return new QueryFunction('CONCAT(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); + } + + public function substring($input, $start, $length = null) { + if ($length) { + return new QueryFunction('SUBSTR(' . $this->helper->quoteColumnName($input) . ', ' . $this->helper->quoteColumnName($start) . ', ' . $this->helper->quoteColumnName($length) . ')'); + } else { + return new QueryFunction('SUBSTR(' . $this->helper->quoteColumnName($input) . ', ' . $this->helper->quoteColumnName($start) . ')'); + } + } +} diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php new file mode 100644 index 00000000000..0877d4781ce --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -0,0 +1,29 @@ + + * + * @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\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; + +class OCIFunctionBuilder extends FunctionBuilder { + public function md5($input) { + return new QueryFunction('LOWER(DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw(' . $this->helper->quoteColumnName($input) .')))'); + } +} diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php new file mode 100644 index 00000000000..b6a7dce46eb --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php @@ -0,0 +1,29 @@ + + * + * @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\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; + +class PgSqlFunctionBuilder extends FunctionBuilder { + public function concat($x, $y) { + return new QueryFunction($this->helper->quoteColumnName($x) . ' || ' . $this->helper->quoteColumnName($y)); + } +} diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php new file mode 100644 index 00000000000..20b30d0ab3a --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -0,0 +1,29 @@ + + * + * @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\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; + +class SqliteFunctionBuilder extends FunctionBuilder { + public function concat($x, $y) { + return new QueryFunction($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 a434140806e..3ff31de19a3 100644 --- a/lib/private/DB/QueryBuilder/QueryBuilder.php +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -34,6 +34,8 @@ use OC\DB\QueryBuilder\ExpressionBuilder\OCIExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\PgSqlExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\SqliteExpressionBuilder; use OC\DB\QueryBuilder\FunctionBuilder\FunctionBuilder; +use OC\DB\QueryBuilder\FunctionBuilder\OCIFunctionBuilder; +use OC\DB\QueryBuilder\FunctionBuilder\PgSqlFunctionBuilder; use OC\DB\QueryBuilder\FunctionBuilder\SqliteFunctionBuilder; use OC\SystemConfig; use OCP\DB\QueryBuilder\IQueryBuilder; @@ -121,6 +123,34 @@ class QueryBuilder implements IQueryBuilder { } } + /** + * Gets an FunctionBuilder used for object-oriented construction of query functions. + * This producer method is intended for convenient inline usage. Example: + * + * + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where($qb->fun()->md5('u.id')); + * + * + * For more complex function construction, consider storing the function + * builder object in a local variable. + * + * @return \OCP\DB\QueryBuilder\IFunctionBuilder + */ + public function fun() { + if ($this->connection instanceof OracleConnection) { + return new OCIFunctionBuilder($this->helper); + } else if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { + return new SqliteFunctionBuilder($this->helper); + } else if ($this->connection->getDatabasePlatform() instanceof PostgreSqlPlatform) { + return new PgSqlFunctionBuilder($this->helper); + } else { + return new FunctionBuilder($this->helper); + } + } + /** * Gets the type of the currently built query. * -- cgit v1.2.3