diff options
Diffstat (limited to 'lib/private/DB/QueryBuilder/FunctionBuilder')
4 files changed, 279 insertions, 0 deletions
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php new file mode 100644 index 00000000000..48dc1da6330 --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php @@ -0,0 +1,108 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB\QueryBuilder\FunctionBuilder; + +use OC\DB\Connection; +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|Connection */ + protected $connection; + + /** @var IQueryBuilder */ + protected $queryBuilder; + + /** @var QuoteHelper */ + protected $helper; + + public function __construct(IDBConnection $connection, IQueryBuilder $queryBuilder, QuoteHelper $helper) { + $this->connection = $connection; + $this->queryBuilder = $queryBuilder; + $this->helper = $helper; + } + + public function md5($input): IQueryFunction { + return new QueryFunction('MD5(' . $this->helper->quoteColumnName($input) . ')'); + } + + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('CONCAT(%s)', implode(', ', $list))); + } + + 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) . ')'); + } else { + return new QueryFunction('SUBSTR(' . $this->helper->quoteColumnName($input) . ', ' . $this->helper->quoteColumnName($start) . ')'); + } + } + + public function sum($field): IQueryFunction { + return new QueryFunction('SUM(' . $this->helper->quoteColumnName($field) . ')'); + } + + public function lower($field): IQueryFunction { + return new QueryFunction('LOWER(' . $this->helper->quoteColumnName($field) . ')'); + } + + public function add($x, $y): IQueryFunction { + return new QueryFunction($this->helper->quoteColumnName($x) . ' + ' . $this->helper->quoteColumnName($y)); + } + + public function subtract($x, $y): IQueryFunction { + return new QueryFunction($this->helper->quoteColumnName($x) . ' - ' . $this->helper->quoteColumnName($y)); + } + + public function count($count = '', $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $count === '' ? '*' : $this->helper->quoteColumnName($count); + 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('OCTET_LENGTH(' . $quotedName . ')' . $alias); + } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('CHAR_LENGTH(' . $quotedName . ')' . $alias); + } + + public function max($field): IQueryFunction { + return new QueryFunction('MAX(' . $this->helper->quoteColumnName($field) . ')'); + } + + public function min($field): IQueryFunction { + return new QueryFunction('MIN(' . $this->helper->quoteColumnName($field) . ')'); + } + + public function greatest($x, $y): IQueryFunction { + return new QueryFunction('GREATEST(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); + } + + public function least($x, $y): IQueryFunction { + return new QueryFunction('LEAST(' . $this->helper->quoteColumnName($x) . ', ' . $this->helper->quoteColumnName($y) . ')'); + } +} diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php new file mode 100644 index 00000000000..47a8eaa6fd0 --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php @@ -0,0 +1,92 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB\QueryBuilder\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; +use OCP\DB\QueryBuilder\IQueryFunction; + +class OCIFunctionBuilder extends FunctionBuilder { + public function md5($input): IQueryFunction { + if (version_compare($this->connection->getServerVersion(), '20', '>=')) { + return new QueryFunction('LOWER(STANDARD_HASH(' . $this->helper->quoteColumnName($input) . ", 'MD5'))"); + } + return new QueryFunction('LOWER(DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw(' . $this->helper->quoteColumnName($input) . ')))'); + } + + /** + * As per https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm + * Oracle uses the first value to cast the rest or the values. So when the + * first value is a literal, plain value or column, instead of doing the + * math, it will cast the expression to int and continue with a "0". So when + * the second parameter is a function or column, we have to put that as + * first parameter. + * + * @param string|ILiteral|IParameter|IQueryFunction $x + * @param string|ILiteral|IParameter|IQueryFunction $y + * @return IQueryFunction + */ + public function greatest($x, $y): IQueryFunction { + if (is_string($y) || $y instanceof IQueryFunction) { + return parent::greatest($y, $x); + } + + return parent::greatest($x, $y); + } + + /** + * As per https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm + * Oracle uses the first value to cast the rest or the values. So when the + * first value is a literal, plain value or column, instead of doing the + * math, it will cast the expression to int and continue with a "0". So when + * the second parameter is a function or column, we have to put that as + * first parameter. + * + * @param string|ILiteral|IParameter|IQueryFunction $x + * @param string|ILiteral|IParameter|IQueryFunction $y + * @return IQueryFunction + */ + public function least($x, $y): IQueryFunction { + if (is_string($y) || $y instanceof IQueryFunction) { + return parent::least($y, $x); + } + + return parent::least($x, $y); + } + + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); + } + + 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); + } + + public function octetLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('COALESCE(LENGTHB(' . $quotedName . '), 0)' . $alias); + } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('COALESCE(LENGTH(' . $quotedName . '), 0)' . $alias); + } +} diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php new file mode 100644 index 00000000000..354a2b126d7 --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php @@ -0,0 +1,33 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +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, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->queryBuilder->expr()->castColumn($item, IQueryBuilder::PARAM_STR); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); + } + + 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 new file mode 100644 index 00000000000..53aa530054b --- /dev/null +++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php @@ -0,0 +1,46 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB\QueryBuilder\FunctionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryFunction; + +class SqliteFunctionBuilder extends FunctionBuilder { + public function concat($x, ...$expr): IQueryFunction { + $args = func_get_args(); + $list = []; + foreach ($args as $item) { + $list[] = $this->helper->quoteColumnName($item); + } + return new QueryFunction(sprintf('(%s)', implode(' || ', $list))); + } + + 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) . ')'); + } + + 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); + } + + public function charLength($field, $alias = ''): IQueryFunction { + $alias = $alias ? (' AS ' . $this->helper->quoteColumnName($alias)) : ''; + $quotedName = $this->helper->quoteColumnName($field); + return new QueryFunction('LENGTH(' . $quotedName . ')' . $alias); + } +} |