diff options
Diffstat (limited to 'lib/private/DB/QueryBuilder/QueryBuilder.php')
| -rw-r--r-- | lib/private/DB/QueryBuilder/QueryBuilder.php | 332 |
1 files changed, 186 insertions, 146 deletions
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php index 43ed68f5616..1d44c049793 100644 --- a/lib/private/DB/QueryBuilder/QueryBuilder.php +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -1,42 +1,15 @@ <?php + /** - * @copyright Copyright (c) 2016, ownCloud, Inc. - * - * @author Arthur Schiwon <blizzz@arthur-schiwon.de> - * @author Christoph Wurst <christoph@winzerhof-wurst.at> - * @author Daniel Kesselberg <mail@danielkesselberg.de> - * @author J0WI <J0WI@users.noreply.github.com> - * @author Joas Schilling <coding@schilljs.com> - * @author Lukas Reschke <lukas@statuscode.ch> - * @author Robin Appelman <robin@icewind.nl> - * @author Roeland Jago Douma <roeland@famdouma.nl> - * @author Thomas Müller <thomas.mueller@tmit.eu> - * @author Vincent Petry <vincent@nextcloud.com> - * - * @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/> - * + * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only */ namespace OC\DB\QueryBuilder; -use Doctrine\DBAL\Platforms\MySQLPlatform; -use Doctrine\DBAL\Platforms\OraclePlatform; -use Doctrine\DBAL\Platforms\PostgreSQL94Platform; -use Doctrine\DBAL\Platforms\SqlitePlatform; use Doctrine\DBAL\Query\QueryException; use OC\DB\ConnectionAdapter; -use OC\DB\QueryBuilder\ExpressionBuilder\ExpressionBuilder; +use OC\DB\Exceptions\DbalException; use OC\DB\QueryBuilder\ExpressionBuilder\MySqlExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\OCIExpressionBuilder; use OC\DB\QueryBuilder\ExpressionBuilder\PgSqlExpressionBuilder; @@ -45,7 +18,6 @@ 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\DB\ResultAdapter; use OC\SystemConfig; use OCP\DB\IResult; use OCP\DB\QueryBuilder\ICompositeExpression; @@ -53,6 +25,7 @@ use OCP\DB\QueryBuilder\ILiteral; use OCP\DB\QueryBuilder\IParameter; use OCP\DB\QueryBuilder\IQueryBuilder; use OCP\DB\QueryBuilder\IQueryFunction; +use OCP\IDBConnection; use Psr\Log\LoggerInterface; class QueryBuilder implements IQueryBuilder { @@ -72,9 +45,11 @@ class QueryBuilder implements IQueryBuilder { /** @var bool */ private $automaticTablePrefix = true; + private bool $nonEmptyWhere = false; /** @var string */ protected $lastInsertedTable; + private array $selectedColumns = []; /** * Initializes a new QueryBuilder. @@ -94,11 +69,11 @@ class QueryBuilder implements IQueryBuilder { * Enable/disable automatic prefixing of table names with the oc_ prefix * * @param bool $enabled If set to true table names will be prefixed with the - * owncloud database prefix automatically. + * owncloud database prefix automatically. * @since 8.2.0 */ public function automaticTablePrefix($enabled) { - $this->automaticTablePrefix = (bool) $enabled; + $this->automaticTablePrefix = (bool)$enabled; } /** @@ -118,20 +93,13 @@ class QueryBuilder implements IQueryBuilder { * @return \OCP\DB\QueryBuilder\IExpressionBuilder */ public function expr() { - if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) { - return new OCIExpressionBuilder($this->connection, $this); - } - if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) { - return new PgSqlExpressionBuilder($this->connection, $this); - } - if ($this->connection->getDatabasePlatform() instanceof MySQLPlatform) { - return new MySqlExpressionBuilder($this->connection, $this); - } - if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { - return new SqliteExpressionBuilder($this->connection, $this); - } - - return new ExpressionBuilder($this->connection, $this); + return match($this->connection->getDatabaseProvider()) { + IDBConnection::PLATFORM_ORACLE => new OCIExpressionBuilder($this->connection, $this, $this->logger), + IDBConnection::PLATFORM_POSTGRES => new PgSqlExpressionBuilder($this->connection, $this, $this->logger), + IDBConnection::PLATFORM_MARIADB, + IDBConnection::PLATFORM_MYSQL => new MySqlExpressionBuilder($this->connection, $this, $this->logger), + IDBConnection::PLATFORM_SQLITE => new SqliteExpressionBuilder($this->connection, $this, $this->logger), + }; } /** @@ -151,17 +119,13 @@ class QueryBuilder implements IQueryBuilder { * @return \OCP\DB\QueryBuilder\IFunctionBuilder */ public function func() { - if ($this->connection->getDatabasePlatform() instanceof OraclePlatform) { - return new OCIFunctionBuilder($this->connection, $this, $this->helper); - } - if ($this->connection->getDatabasePlatform() instanceof SqlitePlatform) { - return new SqliteFunctionBuilder($this->connection, $this, $this->helper); - } - if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) { - return new PgSqlFunctionBuilder($this->connection, $this, $this->helper); - } - - return new FunctionBuilder($this->connection, $this, $this->helper); + return match($this->connection->getDatabaseProvider()) { + IDBConnection::PLATFORM_ORACLE => new OCIFunctionBuilder($this->connection, $this, $this->helper), + IDBConnection::PLATFORM_POSTGRES => new PgSqlFunctionBuilder($this->connection, $this, $this->helper), + IDBConnection::PLATFORM_MARIADB, + IDBConnection::PLATFORM_MYSQL => new FunctionBuilder($this->connection, $this, $this->helper), + IDBConnection::PLATFORM_SQLITE => new SqliteFunctionBuilder($this->connection, $this, $this->helper), + }; } /** @@ -185,26 +149,21 @@ class QueryBuilder implements IQueryBuilder { /** * Gets the state of this query builder instance. * - * @return integer Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN. + * @return int Always returns 0 which is former `QueryBuilder::STATE_DIRTY` + * @deprecated 30.0.0 This function is going to be removed with the next Doctrine/DBAL update + * and we can not fix this in our wrapper. */ public function getState() { + $this->logger->debug(IQueryBuilder::class . '::' . __FUNCTION__ . ' is deprecated and will be removed soon.', ['exception' => new \Exception('Deprecated call to ' . __METHOD__)]); return $this->queryBuilder->getState(); } - /** - * Executes this query using the bound parameters and their types. - * - * Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate} - * for insert, update and delete statements. - * - * @return IResult|int - */ - public function execute() { + private function prepareForExecute() { if ($this->systemConfig->getValue('log_query', false)) { try { $params = []; foreach ($this->getParameters() as $placeholder => $value) { - if ($value instanceof \DateTime) { + if ($value instanceof \DateTimeInterface) { $params[] = $placeholder . ' => DateTime:\'' . $value->format('c') . '\''; } elseif (is_array($value)) { $params[] = $placeholder . ' => (\'' . implode('\', \'', $value) . '\')'; @@ -230,25 +189,42 @@ class QueryBuilder implements IQueryBuilder { } } - if (!empty($this->getQueryPart('select'))) { - $select = $this->getQueryPart('select'); - $hasSelectAll = array_filter($select, static function ($s) { - return $s === '*'; - }); - $hasSelectSpecific = array_filter($select, static function ($s) { - return $s !== '*'; - }); - - if (empty($hasSelectAll) === empty($hasSelectSpecific)) { - $exception = new QueryException('Query is selecting * and specific values in the same query. This is not supported in Oracle.'); - $this->logger->error($exception->getMessage(), [ - 'query' => $this->getSQL(), - 'app' => 'core', - 'exception' => $exception, - ]); + // if (!empty($this->getQueryPart('select'))) { + // $select = $this->getQueryPart('select'); + // $hasSelectAll = array_filter($select, static function ($s) { + // return $s === '*'; + // }); + // $hasSelectSpecific = array_filter($select, static function ($s) { + // return $s !== '*'; + // }); + + // if (empty($hasSelectAll) === empty($hasSelectSpecific)) { + // $exception = new QueryException('Query is selecting * and specific values in the same query. This is not supported in Oracle.'); + // $this->logger->error($exception->getMessage(), [ + // 'query' => $this->getSQL(), + // 'app' => 'core', + // 'exception' => $exception, + // ]); + // } + // } + + $tooLongOutputColumns = []; + foreach ($this->getOutputColumns() as $column) { + if (strlen($column) > 30) { + $tooLongOutputColumns[] = $column; } } + if (!empty($tooLongOutputColumns)) { + $exception = new QueryException('More than 30 characters for an output column name are not allowed on Oracle.'); + $this->logger->error($exception->getMessage(), [ + 'query' => $this->getSQL(), + 'columns' => $tooLongOutputColumns, + 'app' => 'core', + 'exception' => $exception, + ]); + } + $numberOfParameters = 0; $hasTooLargeArrayParameter = false; foreach ($this->getParameters() as $parameter) { @@ -276,63 +252,64 @@ class QueryBuilder implements IQueryBuilder { 'exception' => $exception, ]); } + } + + /** + * Executes this query using the bound parameters and their types. + * + * Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate} + * for insert, update and delete statements. + * + * @return IResult|int + */ + public function execute(?IDBConnection $connection = null) { + try { + if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::SELECT) { + return $this->executeQuery($connection); + } else { + return $this->executeStatement($connection); + } + } catch (DBALException $e) { + // `IQueryBuilder->execute` never wrapped the exception, but `executeQuery` and `executeStatement` do + /** @var \Doctrine\DBAL\Exception $previous */ + $previous = $e->getPrevious(); - $result = $this->queryBuilder->execute(); - if (is_int($result)) { - return $result; + throw $previous; } - return new ResultAdapter($result); } - public function executeQuery(): IResult { + public function executeQuery(?IDBConnection $connection = null): IResult { if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) { throw new \RuntimeException('Invalid query type, expected SELECT query'); } - try { - $result = $this->execute(); - } catch (\Doctrine\DBAL\Exception $e) { - throw \OC\DB\Exceptions\DbalException::wrap($e); - } - - if ($result instanceof IResult) { - return $result; + $this->prepareForExecute(); + if (!$connection) { + $connection = $this->connection; } - throw new \RuntimeException('Invalid return type for query'); - } - - /** - * Monkey-patched compatibility layer for apps that were adapted for Nextcloud 22 before - * the first beta, where executeStatement was named executeUpdate. - * - * Static analysis should catch those misuses, but until then let's try to keep things - * running. - * - * @internal - * @deprecated - * @todo drop ASAP - */ - public function executeUpdate(): int { - return $this->executeStatement(); + return $connection->executeQuery( + $this->getSQL(), + $this->getParameters(), + $this->getParameterTypes(), + ); } - public function executeStatement(): int { + public function executeStatement(?IDBConnection $connection = null): int { if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::SELECT) { throw new \RuntimeException('Invalid query type, expected INSERT, DELETE or UPDATE statement'); } - try { - $result = $this->execute(); - } catch (\Doctrine\DBAL\Exception $e) { - throw \OC\DB\Exceptions\DbalException::wrap($e); - } - - if (!is_int($result)) { - throw new \RuntimeException('Invalid return type for statement'); + $this->prepareForExecute(); + if (!$connection) { + $connection = $this->connection; } - return $result; + return $connection->executeStatement( + $this->getSQL(), + $this->getParameters(), + $this->getParameterTypes(), + ); } @@ -448,7 +425,7 @@ class QueryBuilder implements IQueryBuilder { * @return $this This QueryBuilder instance. */ public function setFirstResult($firstResult) { - $this->queryBuilder->setFirstResult((int) $firstResult); + $this->queryBuilder->setFirstResult((int)$firstResult); return $this; } @@ -478,7 +455,7 @@ class QueryBuilder implements IQueryBuilder { if ($maxResults === null) { $this->queryBuilder->setMaxResults($maxResults); } else { - $this->queryBuilder->setMaxResults((int) $maxResults); + $this->queryBuilder->setMaxResults((int)$maxResults); } return $this; @@ -513,6 +490,7 @@ class QueryBuilder implements IQueryBuilder { if (count($selects) === 1 && is_array($selects[0])) { $selects = $selects[0]; } + $this->addOutputColumns($selects); $this->queryBuilder->select( $this->helper->quoteColumnNames($selects) @@ -540,6 +518,7 @@ class QueryBuilder implements IQueryBuilder { $this->queryBuilder->addSelect( $this->helper->quoteColumnName($select) . ' AS ' . $this->helper->quoteColumnName($alias) ); + $this->addOutputColumns([$alias]); return $this; } @@ -561,6 +540,7 @@ class QueryBuilder implements IQueryBuilder { if (!is_array($select)) { $select = [$select]; } + $this->addOutputColumns($select); $quotedSelect = $this->helper->quoteColumnNames($select); @@ -590,6 +570,7 @@ class QueryBuilder implements IQueryBuilder { if (count($selects) === 1 && is_array($selects[0])) { $selects = $selects[0]; } + $this->addOutputColumns($selects); $this->queryBuilder->addSelect( $this->helper->quoteColumnNames($selects) @@ -598,6 +579,26 @@ class QueryBuilder implements IQueryBuilder { return $this; } + private function addOutputColumns(array $columns): void { + foreach ($columns as $column) { + if (is_array($column)) { + $this->addOutputColumns($column); + } elseif (is_string($column) && !str_contains($column, '*')) { + if (str_contains(strtolower($column), ' as ')) { + [, $column] = preg_split('/ as /i', $column); + } + if (str_contains($column, '.')) { + [, $column] = explode('.', $column); + } + $this->selectedColumns[] = $column; + } + } + } + + public function getOutputColumns(): array { + return array_unique($this->selectedColumns); + } + /** * Turns the query being built into a bulk delete query that ranges over * a certain table. @@ -613,8 +614,13 @@ class QueryBuilder implements IQueryBuilder { * @param string $alias The table alias used in the constructed query. * * @return $this This QueryBuilder instance. + * @since 30.0.0 Alias is deprecated and will no longer be used with the next Doctrine/DBAL update */ public function delete($delete = null, $alias = null) { + if ($alias !== null) { + $this->logger->debug('DELETE queries with alias are no longer supported and the provided alias is ignored', ['exception' => new \InvalidArgumentException('Table alias provided for DELETE query')]); + } + $this->queryBuilder->delete( $this->getTableName($delete), $alias @@ -638,8 +644,13 @@ class QueryBuilder implements IQueryBuilder { * @param string $alias The table alias used in the constructed query. * * @return $this This QueryBuilder instance. + * @since 30.0.0 Alias is deprecated and will no longer be used with the next Doctrine/DBAL update */ public function update($update = null, $alias = null) { + if ($alias !== null) { + $this->logger->debug('UPDATE queries with alias are no longer supported and the provided alias is ignored', ['exception' => new \InvalidArgumentException('Table alias provided for UPDATE query')]); + } + $this->queryBuilder->update( $this->getTableName($update), $alias @@ -768,7 +779,7 @@ class QueryBuilder implements IQueryBuilder { * </code> * * @param string $fromAlias The alias that points to a from clause. - * @param string $join The table name to join. + * @param string|IQueryFunction $join The table name or sub-query to join. * @param string $alias The alias of the join table. * @param string|ICompositeExpression|null $condition The condition for the join. * @@ -850,9 +861,10 @@ class QueryBuilder implements IQueryBuilder { * // You can optionally programmatically build and/or expressions * $qb = $conn->getQueryBuilder(); * - * $or = $qb->expr()->orx(); - * $or->add($qb->expr()->eq('u.id', 1)); - * $or->add($qb->expr()->eq('u.id', 2)); + * $or = $qb->expr()->orx( + * $qb->expr()->eq('u.id', 1), + * $qb->expr()->eq('u.id', 2), + * ); * * $qb->update('users', 'u') * ->set('u.password', md5('password')) @@ -864,12 +876,14 @@ class QueryBuilder implements IQueryBuilder { * @return $this This QueryBuilder instance. */ public function where(...$predicates) { - if ($this->getQueryPart('where') !== null && $this->systemConfig->getValue('debug', false)) { + if ($this->nonEmptyWhere && $this->systemConfig->getValue('debug', false)) { // Only logging a warning, not throwing for now. - $e = new QueryException('Using where() on non-empty WHERE part, please verify it is intentional to not call whereAnd() or whereOr() instead. Otherwise consider creating a new query builder object or call resetQueryPart(\'where\') first.'); + $e = new QueryException('Using where() on non-empty WHERE part, please verify it is intentional to not call andWhere() or orWhere() instead. Otherwise consider creating a new query builder object or call resetQueryPart(\'where\') first.'); $this->logger->warning($e->getMessage(), ['exception' => $e]); } + $this->nonEmptyWhere = true; + call_user_func_array( [$this->queryBuilder, 'where'], $predicates @@ -897,6 +911,7 @@ class QueryBuilder implements IQueryBuilder { * @see where() */ public function andWhere(...$where) { + $this->nonEmptyWhere = true; call_user_func_array( [$this->queryBuilder, 'andWhere'], $where @@ -924,6 +939,7 @@ class QueryBuilder implements IQueryBuilder { * @see where() */ public function orWhere(...$where) { + $this->nonEmptyWhere = true; call_user_func_array( [$this->queryBuilder, 'orWhere'], $where @@ -975,14 +991,10 @@ class QueryBuilder implements IQueryBuilder { * * @return $this This QueryBuilder instance. */ - public function addGroupBy(...$groupBys) { - if (count($groupBys) === 1 && is_array($groupBys[0])) { - $$groupBys = $groupBys[0]; - } - + public function addGroupBy(...$groupBy) { call_user_func_array( [$this->queryBuilder, 'addGroupBy'], - $this->helper->quoteColumnNames($groupBys) + $this->helper->quoteColumnNames($groupBy) ); return $this; @@ -1010,7 +1022,7 @@ class QueryBuilder implements IQueryBuilder { public function setValue($column, $value) { $this->queryBuilder->setValue( $this->helper->quoteColumnName($column), - (string) $value + (string)$value ); return $this; @@ -1138,8 +1150,11 @@ class QueryBuilder implements IQueryBuilder { * @param string $queryPartName * * @return mixed + * @deprecated 30.0.0 This function is going to be removed with the next Doctrine/DBAL update + * and we can not fix this in our wrapper. Please track the details you need, outside the object. */ public function getQueryPart($queryPartName) { + $this->logger->debug(IQueryBuilder::class . '::' . __FUNCTION__ . ' is deprecated and will be removed soon.', ['exception' => new \Exception('Deprecated call to ' . __METHOD__)]); return $this->queryBuilder->getQueryPart($queryPartName); } @@ -1147,8 +1162,11 @@ class QueryBuilder implements IQueryBuilder { * Gets all query parts. * * @return array + * @deprecated 30.0.0 This function is going to be removed with the next Doctrine/DBAL update + * and we can not fix this in our wrapper. Please track the details you need, outside the object. */ public function getQueryParts() { + $this->logger->debug(IQueryBuilder::class . '::' . __FUNCTION__ . ' is deprecated and will be removed soon.', ['exception' => new \Exception('Deprecated call to ' . __METHOD__)]); return $this->queryBuilder->getQueryParts(); } @@ -1158,8 +1176,11 @@ class QueryBuilder implements IQueryBuilder { * @param array|null $queryPartNames * * @return $this This QueryBuilder instance. + * @deprecated 30.0.0 This function is going to be removed with the next Doctrine/DBAL update + * and we can not fix this in our wrapper. Please create a new IQueryBuilder instead. */ public function resetQueryParts($queryPartNames = null) { + $this->logger->debug(IQueryBuilder::class . '::' . __FUNCTION__ . ' is deprecated and will be removed soon.', ['exception' => new \Exception('Deprecated call to ' . __METHOD__)]); $this->queryBuilder->resetQueryParts($queryPartNames); return $this; @@ -1171,8 +1192,11 @@ class QueryBuilder implements IQueryBuilder { * @param string $queryPartName * * @return $this This QueryBuilder instance. + * @deprecated 30.0.0 This function is going to be removed with the next Doctrine/DBAL update + * and we can not fix this in our wrapper. Please create a new IQueryBuilder instead. */ public function resetQueryPart($queryPartName) { + $this->logger->debug(IQueryBuilder::class . '::' . __FUNCTION__ . ' is deprecated and will be removed soon.', ['exception' => new \Exception('Deprecated call to ' . __METHOD__)]); $this->queryBuilder->resetQueryPart($queryPartName); return $this; @@ -1202,7 +1226,7 @@ class QueryBuilder implements IQueryBuilder { * @link http://www.zetacomponents.org * * @param mixed $value - * @param mixed $type + * @param IQueryBuilder::PARAM_* $type * @param string $placeHolder The name to bind with. The string must start with a colon ':'. * * @return IParameter the placeholder name used. @@ -1229,7 +1253,7 @@ class QueryBuilder implements IQueryBuilder { * </code> * * @param mixed $value - * @param integer $type + * @param IQueryBuilder::PARAM_* $type * * @return IParameter */ @@ -1307,7 +1331,7 @@ class QueryBuilder implements IQueryBuilder { */ public function getTableName($table) { if ($table instanceof IQueryFunction) { - return (string) $table; + return (string)$table; } $table = $this->prefixTableName($table); @@ -1317,11 +1341,13 @@ class QueryBuilder implements IQueryBuilder { /** * Returns the table name with database prefix as needed by the implementation * + * Was protected until version 30. + * * @param string $table * @return string */ - protected function prefixTableName($table) { - if ($this->automaticTablePrefix === false || strpos($table, '*PREFIX*') === 0) { + public function prefixTableName(string $table): string { + if ($this->automaticTablePrefix === false || str_starts_with($table, '*PREFIX*')) { return $table; } @@ -1356,4 +1382,18 @@ class QueryBuilder implements IQueryBuilder { return $this->helper->quoteColumnName($alias); } + + public function escapeLikeParameter(string $parameter): string { + return $this->connection->escapeLikeParameter($parameter); + } + + public function hintShardKey(string $column, mixed $value, bool $overwrite = false): self { + return $this; + } + + public function runAcrossAllShards(): self { + // noop + return $this; + } + } |