diff options
Diffstat (limited to 'lib/private/DB/QueryBuilder')
31 files changed, 4965 insertions, 0 deletions
diff --git a/lib/private/DB/QueryBuilder/CompositeExpression.php b/lib/private/DB/QueryBuilder/CompositeExpression.php new file mode 100644 index 00000000000..122998036ae --- /dev/null +++ b/lib/private/DB/QueryBuilder/CompositeExpression.php @@ -0,0 +1,92 @@ +<?php + +/** + * 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 OCP\DB\QueryBuilder\ICompositeExpression; + +class CompositeExpression implements ICompositeExpression, \Countable { + public const TYPE_AND = 'AND'; + public const TYPE_OR = 'OR'; + + public function __construct( + private string $type, + private array $parts = [], + ) { + } + + /** + * Adds multiple parts to composite expression. + * + * @param array $parts + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function addMultiple(array $parts = []): ICompositeExpression { + foreach ($parts as $part) { + $this->add($part); + } + + return $this; + } + + /** + * Adds an expression to composite expression. + * + * @param mixed $part + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function add($part): ICompositeExpression { + if ($part === null) { + return $this; + } + + if ($part instanceof self && count($part) === 0) { + return $this; + } + + $this->parts[] = $part; + + return $this; + } + + /** + * Retrieves the amount of expressions on composite expression. + * + * @return integer + */ + public function count(): int { + return count($this->parts); + } + + /** + * Returns the type of this composite expression (AND/OR). + * + * @return string + */ + public function getType(): string { + return $this->type; + } + + /** + * Retrieves the string representation of this composite expression. + * + * @return string + */ + public function __toString(): string { + if ($this->count() === 1) { + return (string)$this->parts[0]; + } + return '(' . implode(') ' . $this->type . ' (', $this->parts) . ')'; + } + + public function getParts(): array { + return $this->parts; + } +} diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php new file mode 100644 index 00000000000..b922c861630 --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/ExpressionBuilder.php @@ -0,0 +1,431 @@ +<?php + +/** + * 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\ExpressionBuilder; + +use Doctrine\DBAL\Query\Expression\ExpressionBuilder as DoctrineExpressionBuilder; +use OC\DB\ConnectionAdapter; +use OC\DB\QueryBuilder\CompositeExpression; +use OC\DB\QueryBuilder\FunctionBuilder\FunctionBuilder; +use OC\DB\QueryBuilder\Literal; +use OC\DB\QueryBuilder\QueryFunction; +use OC\DB\QueryBuilder\QuoteHelper; +use OCP\DB\QueryBuilder\ICompositeExpression; +use OCP\DB\QueryBuilder\IExpressionBuilder; +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 ExpressionBuilder implements IExpressionBuilder { + /** @var \Doctrine\DBAL\Query\Expression\ExpressionBuilder */ + protected $expressionBuilder; + + /** @var QuoteHelper */ + protected $helper; + + /** @var IDBConnection */ + protected $connection; + + /** @var LoggerInterface */ + protected $logger; + + /** @var FunctionBuilder */ + protected $functionBuilder; + + public function __construct(ConnectionAdapter $connection, IQueryBuilder $queryBuilder, LoggerInterface $logger) { + $this->connection = $connection; + $this->logger = $logger; + $this->helper = new QuoteHelper(); + $this->expressionBuilder = new DoctrineExpressionBuilder($connection->getInner()); + $this->functionBuilder = $queryBuilder->func(); + } + + /** + * Creates a conjunction of the given boolean expressions. + * + * Example: + * + * [php] + * // (u.type = ?) AND (u.role = ?) + * $expr->andX('u.type = ?', 'u.role = ?')); + * + * @param mixed ...$x Optional clause. Defaults = null, but requires + * at least one defined when converting to string. + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function andX(...$x): ICompositeExpression { + if (empty($x)) { + $this->logger->debug('Calling ' . IQueryBuilder::class . '::' . __FUNCTION__ . ' without parameters is deprecated and will throw soon.', ['exception' => new \Exception('No parameters in call to ' . __METHOD__)]); + } + return new CompositeExpression(CompositeExpression::TYPE_AND, $x); + } + + /** + * Creates a disjunction of the given boolean expressions. + * + * Example: + * + * [php] + * // (u.type = ?) OR (u.role = ?) + * $qb->where($qb->expr()->orX('u.type = ?', 'u.role = ?')); + * + * @param mixed ...$x Optional clause. Defaults = null, but requires + * at least one defined when converting to string. + * + * @return \OCP\DB\QueryBuilder\ICompositeExpression + */ + public function orX(...$x): ICompositeExpression { + if (empty($x)) { + $this->logger->debug('Calling ' . IQueryBuilder::class . '::' . __FUNCTION__ . ' without parameters is deprecated and will throw soon.', ['exception' => new \Exception('No parameters in call to ' . __METHOD__)]); + } + return new CompositeExpression(CompositeExpression::TYPE_OR, $x); + } + + /** + * Creates a comparison expression. + * + * @param mixed $x The left expression. + * @param string $operator One of the IExpressionBuilder::* constants. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function comparison($x, string $operator, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->comparison($x, $operator, $y); + } + + /** + * Creates an equality comparison expression with the given arguments. + * + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> = <right expr>. Example: + * + * [php] + * // u.id = ? + * $expr->eq('u.id', '?'); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function eq($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->eq($x, $y); + } + + /** + * Creates a non equality comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> <> <right expr>. Example: + * + * [php] + * // u.id <> 1 + * $q->where($q->expr()->neq('u.id', '1')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function neq($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->neq($x, $y); + } + + /** + * Creates a lower-than comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> < <right expr>. Example: + * + * [php] + * // u.id < ? + * $q->where($q->expr()->lt('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function lt($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->lt($x, $y); + } + + /** + * Creates a lower-than-equal comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> <= <right expr>. Example: + * + * [php] + * // u.id <= ? + * $q->where($q->expr()->lte('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function lte($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->lte($x, $y); + } + + /** + * Creates a greater-than comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> > <right expr>. Example: + * + * [php] + * // u.id > ? + * $q->where($q->expr()->gt('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function gt($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->gt($x, $y); + } + + /** + * Creates a greater-than-equal comparison expression with the given arguments. + * First argument is considered the left expression and the second is the right expression. + * When converted to string, it will generated a <left expr> >= <right expr>. Example: + * + * [php] + * // u.id >= ? + * $q->where($q->expr()->gte('u.id', '?')); + * + * @param mixed $x The left expression. + * @param mixed $y The right expression. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function gte($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + return $this->expressionBuilder->gte($x, $y); + } + + /** + * Creates an IS NULL expression with the given arguments. + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be restricted by IS NULL. + * + * @return string + */ + public function isNull($x): string { + $x = $this->helper->quoteColumnName($x); + return $this->expressionBuilder->isNull($x); + } + + /** + * Creates an IS NOT NULL expression with the given arguments. + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be restricted by IS NOT NULL. + * + * @return string + */ + public function isNotNull($x): string { + $x = $this->helper->quoteColumnName($x); + return $this->expressionBuilder->isNotNull($x); + } + + /** + * Creates a LIKE() comparison expression with the given arguments. + * + * @param ILiteral|IParameter|IQueryFunction|string $x Field in string format to be inspected by LIKE() comparison. + * @param mixed $y Argument to be used in LIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function like($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + 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): string { + return $this->expressionBuilder->like($this->functionBuilder->lower($x), $this->functionBuilder->lower($y)); + } + + /** + * Creates a NOT LIKE() comparison expression with the given arguments. + * + * @param ILiteral|IParameter|IQueryFunction|string $x Field in string format to be inspected by NOT LIKE() comparison. + * @param mixed $y Argument to be used in NOT LIKE() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function notLike($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->notLike($x, $y); + } + + /** + * Creates a IN () comparison expression with the given arguments. + * + * @param ILiteral|IParameter|IQueryFunction|string $x The field in string format to be inspected by IN() comparison. + * @param ILiteral|IParameter|IQueryFunction|string|array $y The placeholder or the array of values to be used by IN() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function in($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnNames($y); + return $this->expressionBuilder->in($x, $y); + } + + /** + * Creates a NOT IN () comparison expression with the given arguments. + * + * @param ILiteral|IParameter|IQueryFunction|string $x The field in string format to be inspected by NOT IN() comparison. + * @param ILiteral|IParameter|IQueryFunction|string|array $y The placeholder or the array of values to be used by NOT IN() comparison. + * @param mixed|null $type one of the IQueryBuilder::PARAM_* constants + * required when comparing text fields for oci compatibility + * + * @return string + */ + public function notIn($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnNames($y); + return $this->expressionBuilder->notIn($x, $y); + } + + /** + * Creates a $x = '' statement, because Oracle needs a different check + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be inspected by the comparison. + * @return string + * @since 13.0.0 + */ + public function emptyString($x): string { + return $this->eq($x, $this->literal('', IQueryBuilder::PARAM_STR)); + } + + /** + * Creates a `$x <> ''` statement, because Oracle needs a different check + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be inspected by the comparison. + * @return string + * @since 13.0.0 + */ + public function nonEmptyString($x): string { + return $this->neq($x, $this->literal('', IQueryBuilder::PARAM_STR)); + } + + /** + * Binary AND Operator copies a bit to the result if it exists in both operands. + * + * @param string|ILiteral $x The field or value to check + * @param int $y Bitmap that must be set + * @return IQueryFunction + * @since 12.0.0 + */ + public function bitwiseAnd($x, int $y): IQueryFunction { + return new QueryFunction($this->connection->getDatabasePlatform()->getBitAndComparisonExpression( + $this->helper->quoteColumnName($x), + $y + )); + } + + /** + * Binary OR Operator copies a bit if it exists in either operand. + * + * @param string|ILiteral $x The field or value to check + * @param int $y Bitmap that must be set + * @return IQueryFunction + * @since 12.0.0 + */ + public function bitwiseOr($x, int $y): IQueryFunction { + return new QueryFunction($this->connection->getDatabasePlatform()->getBitOrComparisonExpression( + $this->helper->quoteColumnName($x), + $y + )); + } + + /** + * Quotes a given input parameter. + * + * @param mixed $input The parameter to be quoted. + * @param int $type One of the IQueryBuilder::PARAM_* constants + * + * @return ILiteral + */ + public function literal($input, $type = IQueryBuilder::PARAM_STR): ILiteral { + return new Literal($this->expressionBuilder->literal($input, $type)); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string|IQueryFunction $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @psalm-param IQueryBuilder::PARAM_* $type + * @return IQueryFunction + */ + public function castColumn($column, $type): IQueryFunction { + return new QueryFunction( + $this->helper->quoteColumnName($column) + ); + } + + /** + * @param mixed $column + * @param mixed|null $type + * @return array|IQueryFunction|string + */ + protected function prepareColumn($column, $type) { + return $this->helper->quoteColumnNames($column); + } +} diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php new file mode 100644 index 00000000000..7216fd8807b --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php @@ -0,0 +1,51 @@ +<?php + +/** + * 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\ExpressionBuilder; + +use OC\DB\ConnectionAdapter; +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; +use Psr\Log\LoggerInterface; + +class MySqlExpressionBuilder extends ExpressionBuilder { + protected string $collation; + + public function __construct(ConnectionAdapter $connection, IQueryBuilder $queryBuilder, LoggerInterface $logger) { + parent::__construct($connection, $queryBuilder, $logger); + + $params = $connection->getInner()->getParams(); + $this->collation = $params['collation'] ?? (($params['charset'] ?? 'utf8') . '_general_ci'); + } + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison($x, ' COLLATE ' . $this->collation . ' LIKE', $y); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string|IQueryFunction $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @psalm-param IQueryBuilder::PARAM_* $type + * @return IQueryFunction + */ + public function castColumn($column, $type): IQueryFunction { + switch ($type) { + case IQueryBuilder::PARAM_STR: + return new QueryFunction('CAST(' . $this->helper->quoteColumnName($column) . ' AS CHAR)'); + default: + return parent::castColumn($column, $type); + } + } +} diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php new file mode 100644 index 00000000000..542e8d62ede --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/OCIExpressionBuilder.php @@ -0,0 +1,106 @@ +<?php + +/** + * 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\ExpressionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; + +class OCIExpressionBuilder extends ExpressionBuilder { + /** + * @param mixed $column + * @param mixed|null $type + * @return array|IQueryFunction|string + */ + protected function prepareColumn($column, $type) { + if ($type === IQueryBuilder::PARAM_STR && !is_array($column) && !($column instanceof IParameter) && !($column instanceof ILiteral)) { + $column = $this->castColumn($column, $type); + } + + return parent::prepareColumn($column, $type); + } + + /** + * @inheritdoc + */ + public function in($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->in($x, $y); + } + + /** + * @inheritdoc + */ + public function notIn($x, $y, $type = null): string { + $x = $this->prepareColumn($x, $type); + $y = $this->prepareColumn($y, $type); + + return $this->expressionBuilder->notIn($x, $y); + } + + /** + * Creates a $x = '' statement, because Oracle needs a different check + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be inspected by the comparison. + * @return string + * @since 13.0.0 + */ + public function emptyString($x): string { + return $this->isNull($x); + } + + /** + * Creates a `$x <> ''` statement, because Oracle needs a different check + * + * @param string|ILiteral|IParameter|IQueryFunction $x The field in string format to be inspected by the comparison. + * @return string + * @since 13.0.0 + */ + public function nonEmptyString($x): string { + return $this->isNotNull($x); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string|IQueryFunction $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @psalm-param IQueryBuilder::PARAM_* $type + * @return IQueryFunction + */ + public function castColumn($column, $type): IQueryFunction { + if ($type === IQueryBuilder::PARAM_STR) { + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('to_char(' . $column . ')'); + } + if ($type === IQueryBuilder::PARAM_INT) { + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('to_number(to_char(' . $column . '))'); + } + + return parent::castColumn($column, $type); + } + + /** + * @inheritdoc + */ + public function like($x, $y, $type = null): string { + return parent::like($x, $y, $type) . " ESCAPE '\\'"; + } + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null): string { + return $this->like($this->functionBuilder->lower($x), $this->functionBuilder->lower($y)); + } +} diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/PgSqlExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/PgSqlExpressionBuilder.php new file mode 100644 index 00000000000..53a566a7eb6 --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/PgSqlExpressionBuilder.php @@ -0,0 +1,42 @@ +<?php + +/** + * 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\ExpressionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; + +class PgSqlExpressionBuilder extends ExpressionBuilder { + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string|IQueryFunction $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @psalm-param IQueryBuilder::PARAM_* $type + * @return IQueryFunction + */ + public function castColumn($column, $type): IQueryFunction { + switch ($type) { + case IQueryBuilder::PARAM_INT: + return new QueryFunction('CAST(' . $this->helper->quoteColumnName($column) . ' AS BIGINT)'); + case IQueryBuilder::PARAM_STR: + return new QueryFunction('CAST(' . $this->helper->quoteColumnName($column) . ' AS TEXT)'); + default: + return parent::castColumn($column, $type); + } + } + + /** + * @inheritdoc + */ + public function iLike($x, $y, $type = null): string { + $x = $this->helper->quoteColumnName($x); + $y = $this->helper->quoteColumnName($y); + return $this->expressionBuilder->comparison($x, 'ILIKE', $y); + } +} diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php new file mode 100644 index 00000000000..52f82db2232 --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/SqliteExpressionBuilder.php @@ -0,0 +1,71 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB\QueryBuilder\ExpressionBuilder; + +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; + +class SqliteExpressionBuilder extends ExpressionBuilder { + /** + * @inheritdoc + */ + public function like($x, $y, $type = null): string { + return parent::like($x, $y, $type) . " ESCAPE '\\'"; + } + + public function iLike($x, $y, $type = null): string { + return $this->like($this->functionBuilder->lower($x), $this->functionBuilder->lower($y), $type); + } + + /** + * @param mixed $column + * @param mixed|null $type + * @return array|IQueryFunction|string + */ + protected function prepareColumn($column, $type) { + if ($type !== null + && !is_array($column) + && !($column instanceof IParameter) + && !($column instanceof ILiteral) + && (str_starts_with($type, 'date') || str_starts_with($type, 'time'))) { + return $this->castColumn($column, $type); + } + + return parent::prepareColumn($column, $type); + } + + /** + * Returns a IQueryFunction that casts the column to the given type + * + * @param string $column + * @param mixed $type One of IQueryBuilder::PARAM_* + * @return IQueryFunction + */ + public function castColumn($column, $type): IQueryFunction { + switch ($type) { + case IQueryBuilder::PARAM_DATE_MUTABLE: + case IQueryBuilder::PARAM_DATE_IMMUTABLE: + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('DATE(' . $column . ')'); + case IQueryBuilder::PARAM_DATETIME_MUTABLE: + case IQueryBuilder::PARAM_DATETIME_IMMUTABLE: + case IQueryBuilder::PARAM_DATETIME_TZ_MUTABLE: + case IQueryBuilder::PARAM_DATETIME_TZ_IMMUTABLE: + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('DATETIME(' . $column . ')'); + case IQueryBuilder::PARAM_TIME_MUTABLE: + case IQueryBuilder::PARAM_TIME_IMMUTABLE: + $column = $this->helper->quoteColumnName($column); + return new QueryFunction('TIME(' . $column . ')'); + } + + return parent::castColumn($column, $type); + } +} diff --git a/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php b/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php new file mode 100644 index 00000000000..f928132a123 --- /dev/null +++ b/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php @@ -0,0 +1,309 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder; + +use OC\DB\Exceptions\DbalException; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; + +/** + * Base class for creating classes that extend the builtin query builder + */ +abstract class ExtendedQueryBuilder implements IQueryBuilder { + public function __construct( + protected IQueryBuilder $builder, + ) { + } + + public function automaticTablePrefix($enabled) { + $this->builder->automaticTablePrefix($enabled); + return $this; + } + + public function expr() { + return $this->builder->expr(); + } + + public function func() { + return $this->builder->func(); + } + + public function getType() { + return $this->builder->getType(); + } + + public function getConnection() { + return $this->builder->getConnection(); + } + + public function getState() { + return $this->builder->getState(); + } + + 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(); + throw $previous; + } + } + + public function getSQL() { + return $this->builder->getSQL(); + } + + public function setParameter($key, $value, $type = null) { + $this->builder->setParameter($key, $value, $type); + return $this; + } + + public function setParameters(array $params, array $types = []) { + $this->builder->setParameters($params, $types); + return $this; + } + + public function getParameters() { + return $this->builder->getParameters(); + } + + public function getParameter($key) { + return $this->builder->getParameter($key); + } + + public function getParameterTypes() { + return $this->builder->getParameterTypes(); + } + + public function getParameterType($key) { + return $this->builder->getParameterType($key); + } + + public function setFirstResult($firstResult) { + $this->builder->setFirstResult($firstResult); + return $this; + } + + public function getFirstResult() { + return $this->builder->getFirstResult(); + } + + public function setMaxResults($maxResults) { + $this->builder->setMaxResults($maxResults); + return $this; + } + + public function getMaxResults() { + return $this->builder->getMaxResults(); + } + + public function select(...$selects) { + $this->builder->select(...$selects); + return $this; + } + + public function selectAlias($select, $alias) { + $this->builder->selectAlias($select, $alias); + return $this; + } + + public function selectDistinct($select) { + $this->builder->selectDistinct($select); + return $this; + } + + public function addSelect(...$select) { + $this->builder->addSelect(...$select); + return $this; + } + + public function delete($delete = null, $alias = null) { + $this->builder->delete($delete, $alias); + return $this; + } + + public function update($update = null, $alias = null) { + $this->builder->update($update, $alias); + return $this; + } + + public function insert($insert = null) { + $this->builder->insert($insert); + return $this; + } + + public function from($from, $alias = null) { + $this->builder->from($from, $alias); + return $this; + } + + public function join($fromAlias, $join, $alias, $condition = null) { + $this->builder->join($fromAlias, $join, $alias, $condition); + return $this; + } + + public function innerJoin($fromAlias, $join, $alias, $condition = null) { + $this->builder->innerJoin($fromAlias, $join, $alias, $condition); + return $this; + } + + public function leftJoin($fromAlias, $join, $alias, $condition = null) { + $this->builder->leftJoin($fromAlias, $join, $alias, $condition); + return $this; + } + + public function rightJoin($fromAlias, $join, $alias, $condition = null) { + $this->builder->rightJoin($fromAlias, $join, $alias, $condition); + return $this; + } + + public function set($key, $value) { + $this->builder->set($key, $value); + return $this; + } + + public function where(...$predicates) { + $this->builder->where(...$predicates); + return $this; + } + + public function andWhere(...$where) { + $this->builder->andWhere(...$where); + return $this; + } + + public function orWhere(...$where) { + $this->builder->orWhere(...$where); + return $this; + } + + public function groupBy(...$groupBys) { + $this->builder->groupBy(...$groupBys); + return $this; + } + + public function addGroupBy(...$groupBy) { + $this->builder->addGroupBy(...$groupBy); + return $this; + } + + public function setValue($column, $value) { + $this->builder->setValue($column, $value); + return $this; + } + + public function values(array $values) { + $this->builder->values($values); + return $this; + } + + public function having(...$having) { + $this->builder->having(...$having); + return $this; + } + + public function andHaving(...$having) { + $this->builder->andHaving(...$having); + return $this; + } + + public function orHaving(...$having) { + $this->builder->orHaving(...$having); + return $this; + } + + public function orderBy($sort, $order = null) { + $this->builder->orderBy($sort, $order); + return $this; + } + + public function addOrderBy($sort, $order = null) { + $this->builder->addOrderBy($sort, $order); + return $this; + } + + public function getQueryPart($queryPartName) { + return $this->builder->getQueryPart($queryPartName); + } + + public function getQueryParts() { + return $this->builder->getQueryParts(); + } + + public function resetQueryParts($queryPartNames = null) { + $this->builder->resetQueryParts($queryPartNames); + return $this; + } + + public function resetQueryPart($queryPartName) { + $this->builder->resetQueryPart($queryPartName); + return $this; + } + + public function createNamedParameter($value, $type = self::PARAM_STR, $placeHolder = null) { + return $this->builder->createNamedParameter($value, $type, $placeHolder); + } + + public function createPositionalParameter($value, $type = self::PARAM_STR) { + return $this->builder->createPositionalParameter($value, $type); + } + + public function createParameter($name) { + return $this->builder->createParameter($name); + } + + public function createFunction($call) { + return $this->builder->createFunction($call); + } + + public function getLastInsertId(): int { + return $this->builder->getLastInsertId(); + } + + public function getTableName($table) { + return $this->builder->getTableName($table); + } + + public function getColumnName($column, $tableAlias = '') { + return $this->builder->getColumnName($column, $tableAlias); + } + + public function executeQuery(?IDBConnection $connection = null): IResult { + return $this->builder->executeQuery($connection); + } + + public function executeStatement(?IDBConnection $connection = null): int { + return $this->builder->executeStatement($connection); + } + + public function hintShardKey(string $column, mixed $value, bool $overwrite = false): self { + $this->builder->hintShardKey($column, $value, $overwrite); + return $this; + } + + public function runAcrossAllShards(): self { + $this->builder->runAcrossAllShards(); + return $this; + } + + public function getOutputColumns(): array { + return $this->builder->getOutputColumns(); + } + + public function prefixTableName(string $table): string { + return $this->builder->prefixTableName($table); + } +} 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); + } +} diff --git a/lib/private/DB/QueryBuilder/Literal.php b/lib/private/DB/QueryBuilder/Literal.php new file mode 100644 index 00000000000..3fb897328e5 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Literal.php @@ -0,0 +1,23 @@ +<?php + +/** + * 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 OCP\DB\QueryBuilder\ILiteral; + +class Literal implements ILiteral { + /** @var mixed */ + protected $literal; + + public function __construct($literal) { + $this->literal = $literal; + } + + public function __toString(): string { + return (string)$this->literal; + } +} diff --git a/lib/private/DB/QueryBuilder/Parameter.php b/lib/private/DB/QueryBuilder/Parameter.php new file mode 100644 index 00000000000..a272c744d62 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Parameter.php @@ -0,0 +1,23 @@ +<?php + +/** + * 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 OCP\DB\QueryBuilder\IParameter; + +class Parameter implements IParameter { + /** @var mixed */ + protected $name; + + public function __construct($name) { + $this->name = $name; + } + + public function __toString(): string { + return (string)$this->name; + } +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/InvalidPartitionedQueryException.php b/lib/private/DB/QueryBuilder/Partitioned/InvalidPartitionedQueryException.php new file mode 100644 index 00000000000..3a5aa2f3e0e --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/InvalidPartitionedQueryException.php @@ -0,0 +1,79 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +/** + * Partitioned queries impose limitations that queries have to follow: + * + * 1. Any reference to columns not in the "main table" (the table referenced by "FROM"), needs to explicitly include the + * table or alias the column belongs to. + * + * For example: + * ``` + * $query->select("mount_point", "mimetype") + * ->from("mounts", "m") + * ->innerJoin("m", "filecache", "f", $query->expr()->eq("root_id", "fileid")); + * ``` + * will not work, as the query builder doesn't know that the `mimetype` column belongs to the "filecache partition". + * Instead, you need to do + * ``` + * $query->select("mount_point", "f.mimetype") + * ->from("mounts", "m") + * ->innerJoin("m", "filecache", "f", $query->expr()->eq("m.root_id", "f.fileid")); + * ``` + * + * 2. The "ON" condition for the join can only perform a comparison between both sides of the join once. + * + * For example: + * ``` + * $query->select("mount_point", "mimetype") + * ->from("mounts", "m") + * ->innerJoin("m", "filecache", "f", $query->expr()->andX($query->expr()->eq("m.root_id", "f.fileid"), $query->expr()->eq("m.storage_id", "f.storage"))); + * ``` + * will not work. + * + * 3. An "OR" expression in the "WHERE" cannot mention both sides of the join, this does not apply to "AND" expressions. + * + * For example: + * ``` + * $query->select("mount_point", "mimetype") + * ->from("mounts", "m") + * ->innerJoin("m", "filecache", "f", $query->expr()->eq("m.root_id", "f.fileid"))) + * ->where($query->expr()->orX( + * $query->expr()-eq("m.user_id", $query->createNamedParameter("test"))), + * $query->expr()-eq("f.name", $query->createNamedParameter("test"))), + * )); + * ``` + * will not work, but. + * ``` + * $query->select("mount_point", "mimetype") + * ->from("mounts", "m") + * ->innerJoin("m", "filecache", "f", $query->expr()->eq("m.root_id", "f.fileid"))) + * ->where($query->expr()->andX( + * $query->expr()-eq("m.user_id", $query->createNamedParameter("test"))), + * $query->expr()-eq("f.name", $query->createNamedParameter("test"))), + * )); + * ``` + * will. + * + * 4. Queries that join cross-partition cannot use position parameters, only named parameters are allowed + * 5. The "ON" condition of a join cannot contain and "OR" expression. + * 6. Right-joins are not allowed. + * 7. Update, delete and insert statements aren't allowed to contain cross-partition joins. + * 8. Queries that "GROUP BY" a column from the joined partition are not allowed. + * 9. Any `join` call needs to be made before any `where` call. + * 10. Queries that join cross-partition with an "INNER JOIN" or "LEFT JOIN" with a condition on the left side + * cannot use "LIMIT" or "OFFSET" in queries. + * + * The part of the query running on the sharded table has some additional limitations, + * see the `InvalidShardedQueryException` documentation for more information. + */ +class InvalidPartitionedQueryException extends \Exception { + +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/JoinCondition.php b/lib/private/DB/QueryBuilder/Partitioned/JoinCondition.php new file mode 100644 index 00000000000..a08858d1d6b --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/JoinCondition.php @@ -0,0 +1,173 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +use OC\DB\QueryBuilder\CompositeExpression; +use OC\DB\QueryBuilder\QueryFunction; +use OCP\DB\QueryBuilder\IQueryFunction; + +/** + * Utility class for working with join conditions + */ +class JoinCondition { + public function __construct( + public string|IQueryFunction $fromColumn, + public ?string $fromAlias, + public string|IQueryFunction $toColumn, + public ?string $toAlias, + public array $fromConditions, + public array $toConditions, + ) { + if (is_string($this->fromColumn) && str_starts_with($this->fromColumn, '(')) { + $this->fromColumn = new QueryFunction($this->fromColumn); + } + if (is_string($this->toColumn) && str_starts_with($this->toColumn, '(')) { + $this->toColumn = new QueryFunction($this->toColumn); + } + } + + /** + * @param JoinCondition[] $conditions + * @return JoinCondition + */ + public static function merge(array $conditions): JoinCondition { + $fromColumn = ''; + $toColumn = ''; + $fromAlias = null; + $toAlias = null; + $fromConditions = []; + $toConditions = []; + foreach ($conditions as $condition) { + if (($condition->fromColumn && $fromColumn) || ($condition->toColumn && $toColumn)) { + throw new InvalidPartitionedQueryException("Can't join from {$condition->fromColumn} to {$condition->toColumn} as it already join froms {$fromColumn} to {$toColumn}"); + } + if ($condition->fromColumn) { + $fromColumn = $condition->fromColumn; + } + if ($condition->toColumn) { + $toColumn = $condition->toColumn; + } + if ($condition->fromAlias) { + $fromAlias = $condition->fromAlias; + } + if ($condition->toAlias) { + $toAlias = $condition->toAlias; + } + $fromConditions = array_merge($fromConditions, $condition->fromConditions); + $toConditions = array_merge($toConditions, $condition->toConditions); + } + return new JoinCondition($fromColumn, $fromAlias, $toColumn, $toAlias, $fromConditions, $toConditions); + } + + /** + * @param null|string|CompositeExpression $condition + * @param string $join + * @param string $alias + * @param string $fromAlias + * @return JoinCondition + * @throws InvalidPartitionedQueryException + */ + public static function parse($condition, string $join, string $alias, string $fromAlias): JoinCondition { + if ($condition === null) { + throw new InvalidPartitionedQueryException("Can't join on $join without a condition"); + } + + $result = self::parseSubCondition($condition, $join, $alias, $fromAlias); + if (!$result->fromColumn || !$result->toColumn) { + throw new InvalidPartitionedQueryException("No join condition found from $fromAlias to $alias"); + } + return $result; + } + + private static function parseSubCondition($condition, string $join, string $alias, string $fromAlias): JoinCondition { + if ($condition instanceof CompositeExpression) { + if ($condition->getType() === CompositeExpression::TYPE_OR) { + throw new InvalidPartitionedQueryException("Cannot join on $join with an OR expression"); + } + return self::merge(array_map(function ($subCondition) use ($join, $alias, $fromAlias) { + return self::parseSubCondition($subCondition, $join, $alias, $fromAlias); + }, $condition->getParts())); + } + + $condition = (string)$condition; + $isSubCondition = self::isExtraCondition($condition); + if ($isSubCondition) { + if (self::mentionsAlias($condition, $fromAlias)) { + return new JoinCondition('', null, '', null, [$condition], []); + } else { + return new JoinCondition('', null, '', null, [], [$condition]); + } + } + + $condition = str_replace('`', '', $condition); + + // expect a condition in the form of 'alias1.column1 = alias2.column2' + if (!str_contains($condition, ' = ')) { + throw new InvalidPartitionedQueryException("Can only join on $join with an `eq` condition"); + } + $parts = explode(' = ', $condition, 2); + $parts = array_map(function (string $part) { + return self::clearConditionPart($part); + }, $parts); + + if (!self::isSingleCondition($parts[0]) || !self::isSingleCondition($parts[1])) { + throw new InvalidPartitionedQueryException("Can only join on $join with a single condition"); + } + + + if (self::mentionsAlias($parts[0], $fromAlias)) { + return new JoinCondition($parts[0], self::getAliasForPart($parts[0]), $parts[1], self::getAliasForPart($parts[1]), [], []); + } elseif (self::mentionsAlias($parts[1], $fromAlias)) { + return new JoinCondition($parts[1], self::getAliasForPart($parts[1]), $parts[0], self::getAliasForPart($parts[0]), [], []); + } else { + throw new InvalidPartitionedQueryException("join condition for $join needs to explicitly refer to the table by alias"); + } + } + + private static function isSingleCondition(string $condition): bool { + return !(str_contains($condition, ' OR ') || str_contains($condition, ' AND ')); + } + + private static function getAliasForPart(string $part): ?string { + if (str_contains($part, ' ')) { + return uniqid('join_alias_'); + } else { + return null; + } + } + + private static function clearConditionPart(string $part): string { + if (str_starts_with($part, 'CAST(')) { + // pgsql/mysql cast + $part = substr($part, strlen('CAST(')); + [$part] = explode(' AS ', $part); + } elseif (str_starts_with($part, 'to_number(to_char(')) { + // oracle cast to int + $part = substr($part, strlen('to_number(to_char('), -2); + } elseif (str_starts_with($part, 'to_number(to_char(')) { + // oracle cast to string + $part = substr($part, strlen('to_char('), -1); + } + return $part; + } + + /** + * Check that a condition is an extra limit on the from/to part, and not the join condition + * + * This is done by checking that only one of the halves of the condition references a column + */ + private static function isExtraCondition(string $condition): bool { + $parts = explode(' ', $condition, 2); + return str_contains($parts[0], '`') xor str_contains($parts[1], '`'); + } + + private static function mentionsAlias(string $condition, string $alias): bool { + return str_contains($condition, "$alias."); + } +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/PartitionQuery.php b/lib/private/DB/QueryBuilder/Partitioned/PartitionQuery.php new file mode 100644 index 00000000000..a5024b478d3 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/PartitionQuery.php @@ -0,0 +1,75 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +use OCP\DB\QueryBuilder\IQueryBuilder; + +/** + * A sub-query from a partitioned join + */ +class PartitionQuery { + public const JOIN_MODE_INNER = 'inner'; + public const JOIN_MODE_LEFT = 'left'; + // left-join where the left side IS NULL + public const JOIN_MODE_LEFT_NULL = 'left_null'; + + public const JOIN_MODE_RIGHT = 'right'; + + public function __construct( + public IQueryBuilder $query, + public string $joinFromColumn, + public string $joinToColumn, + public string $joinMode, + ) { + if ($joinMode !== self::JOIN_MODE_LEFT && $joinMode !== self::JOIN_MODE_INNER) { + throw new InvalidPartitionedQueryException("$joinMode joins aren't allowed in partitioned queries"); + } + } + + public function mergeWith(array $rows): array { + if (empty($rows)) { + return []; + } + // strip table/alias from column names + $joinFromColumn = preg_replace('/\w+\./', '', $this->joinFromColumn); + $joinToColumn = preg_replace('/\w+\./', '', $this->joinToColumn); + + $joinFromValues = array_map(function (array $row) use ($joinFromColumn) { + return $row[$joinFromColumn]; + }, $rows); + $joinFromValues = array_filter($joinFromValues, function ($value) { + return $value !== null; + }); + $this->query->andWhere($this->query->expr()->in($this->joinToColumn, $this->query->createNamedParameter($joinFromValues, IQueryBuilder::PARAM_STR_ARRAY, ':' . uniqid()))); + + $s = $this->query->getSQL(); + $partitionedRows = $this->query->executeQuery()->fetchAll(); + + $columns = $this->query->getOutputColumns(); + $nullResult = array_combine($columns, array_fill(0, count($columns), null)); + + $partitionedRowsByKey = []; + foreach ($partitionedRows as $partitionedRow) { + $partitionedRowsByKey[$partitionedRow[$joinToColumn]][] = $partitionedRow; + } + $result = []; + foreach ($rows as $row) { + if (isset($partitionedRowsByKey[$row[$joinFromColumn]])) { + if ($this->joinMode !== self::JOIN_MODE_LEFT_NULL) { + foreach ($partitionedRowsByKey[$row[$joinFromColumn]] as $partitionedRow) { + $result[] = array_merge($row, $partitionedRow); + } + } + } elseif ($this->joinMode === self::JOIN_MODE_LEFT || $this->joinMode === self::JOIN_MODE_LEFT_NULL) { + $result[] = array_merge($nullResult, $row); + } + } + return $result; + } +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/PartitionSplit.php b/lib/private/DB/QueryBuilder/Partitioned/PartitionSplit.php new file mode 100644 index 00000000000..ad4c0fab055 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/PartitionSplit.php @@ -0,0 +1,74 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +/** + * Information about a database partition, containing the tables in the partition and any active alias + */ +class PartitionSplit { + /** @var array<string, string> */ + public array $aliases = []; + + /** + * @param string[] $tables + */ + public function __construct( + public string $name, + public array $tables, + ) { + } + + public function addAlias(string $table, string $alias): void { + if ($this->containsTable($table)) { + $this->aliases[$alias] = $table; + } + } + + public function addTable(string $table): void { + if (!$this->containsTable($table)) { + $this->tables[] = $table; + } + } + + public function containsTable(string $table): bool { + return in_array($table, $this->tables); + } + + public function containsAlias(string $alias): bool { + return array_key_exists($alias, $this->aliases); + } + + private function getTablesAndAliases(): array { + return array_keys($this->aliases) + $this->tables; + } + + /** + * Check if a query predicate mentions a table or alias from this partition + * + * @param string $predicate + * @return bool + */ + public function checkPredicateForTable(string $predicate): bool { + foreach ($this->getTablesAndAliases() as $name) { + if (str_contains($predicate, "`$name`.`")) { + return true; + } + } + return false; + } + + public function isColumnInPartition(string $column): bool { + foreach ($this->getTablesAndAliases() as $name) { + if (str_starts_with($column, "$name.")) { + return true; + } + } + return false; + } +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/PartitionedQueryBuilder.php b/lib/private/DB/QueryBuilder/Partitioned/PartitionedQueryBuilder.php new file mode 100644 index 00000000000..d748c791321 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/PartitionedQueryBuilder.php @@ -0,0 +1,462 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +use OC\DB\QueryBuilder\CompositeExpression; +use OC\DB\QueryBuilder\QuoteHelper; +use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler; +use OC\DB\QueryBuilder\Sharded\ShardConnectionManager; +use OC\DB\QueryBuilder\Sharded\ShardedQueryBuilder; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\IQueryFunction; +use OCP\IDBConnection; + +/** + * A special query builder that automatically splits queries that span across multiple database partitions[1]. + * + * This is done by inspecting the query as it's being built, and when a cross-partition join is detected, + * the part of the query that touches the partition is split of into a different sub-query. + * Then, when the query is executed, the results from the sub-queries are automatically merged. + * + * This whole process is intended to be transparent to any code using the query builder, however it does impose some extra + * limitation for queries that work cross-partition. See the documentation from `InvalidPartitionedQueryException` for more details. + * + * When a join is created in the query, this builder checks if it belongs to the same partition as the table from the + * original FROM/UPDATE/DELETE/INSERT and if not, creates a new "sub query" for the partition. + * Then for every part that is added the query, the part is analyzed to determine which partition the query part is referencing + * and the query part is added to the sub query for that partition. + * + * [1]: A set of tables which can't be queried together with the rest of the tables, such as when sharding is used. + */ +class PartitionedQueryBuilder extends ShardedQueryBuilder { + /** @var array<string, PartitionQuery> $splitQueries */ + private array $splitQueries = []; + /** @var list<PartitionSplit> */ + private array $partitions = []; + + /** @var array{'select': string|array, 'alias': ?string}[] */ + private array $selects = []; + private ?PartitionSplit $mainPartition = null; + private bool $hasPositionalParameter = false; + private QuoteHelper $quoteHelper; + private ?int $limit = null; + private ?int $offset = null; + + public function __construct( + IQueryBuilder $builder, + array $shardDefinitions, + ShardConnectionManager $shardConnectionManager, + AutoIncrementHandler $autoIncrementHandler, + ) { + parent::__construct($builder, $shardDefinitions, $shardConnectionManager, $autoIncrementHandler); + $this->quoteHelper = new QuoteHelper(); + } + + private function newQuery(): IQueryBuilder { + // get a fresh, non-partitioning query builder + $builder = $this->builder->getConnection()->getQueryBuilder(); + if ($builder instanceof PartitionedQueryBuilder) { + $builder = $builder->builder; + } + + return new ShardedQueryBuilder( + $builder, + $this->shardDefinitions, + $this->shardConnectionManager, + $this->autoIncrementHandler, + ); + } + + // we need to save selects until we know all the table aliases + public function select(...$selects) { + if (count($selects) === 1 && is_array($selects[0])) { + $selects = $selects[0]; + } + $this->selects = []; + $this->addSelect(...$selects); + return $this; + } + + public function addSelect(...$select) { + $select = array_map(function ($select) { + return ['select' => $select, 'alias' => null]; + }, $select); + $this->selects = array_merge($this->selects, $select); + return $this; + } + + public function selectAlias($select, $alias) { + $this->selects[] = ['select' => $select, 'alias' => $alias]; + return $this; + } + + /** + * Ensure that a column is being selected by the query + * + * This is mainly used to ensure that the returned rows from both sides of a partition contains the columns of the join predicate + * + * @param string|IQueryFunction $column + * @return void + */ + private function ensureSelect(string|IQueryFunction $column, ?string $alias = null): void { + $checkColumn = $alias ?: $column; + if (str_contains($checkColumn, '.')) { + [$table, $checkColumn] = explode('.', $checkColumn); + $partition = $this->getPartition($table); + } else { + $partition = null; + } + foreach ($this->selects as $select) { + $select = $select['select']; + if (!is_string($select)) { + continue; + } + + if (str_contains($select, '.')) { + [$table, $select] = explode('.', $select); + $selectPartition = $this->getPartition($table); + } else { + $selectPartition = null; + } + if ( + ($select === $checkColumn || $select === '*') + && $selectPartition === $partition + ) { + return; + } + } + if ($alias) { + $this->selectAlias($column, $alias); + } else { + $this->addSelect($column); + } + } + + /** + * Distribute the select statements to the correct partition + * + * This is done at the end instead of when the `select` call is made, because the `select` calls are generally done + * before we know what tables are involved in the query + * + * @return void + */ + private function applySelects(): void { + foreach ($this->selects as $select) { + foreach ($this->partitions as $partition) { + if (is_string($select['select']) && ( + $select['select'] === '*' + || $partition->isColumnInPartition($select['select'])) + ) { + if (isset($this->splitQueries[$partition->name])) { + if ($select['alias']) { + $this->splitQueries[$partition->name]->query->selectAlias($select['select'], $select['alias']); + } else { + $this->splitQueries[$partition->name]->query->addSelect($select['select']); + } + if ($select['select'] !== '*') { + continue 2; + } + } + } + } + + if ($select['alias']) { + parent::selectAlias($select['select'], $select['alias']); + } else { + parent::addSelect($select['select']); + } + } + $this->selects = []; + } + + + public function addPartition(PartitionSplit $partition): void { + $this->partitions[] = $partition; + } + + private function getPartition(string $table): ?PartitionSplit { + foreach ($this->partitions as $partition) { + if ($partition->containsTable($table) || $partition->containsAlias($table)) { + return $partition; + } + } + return null; + } + + public function from($from, $alias = null) { + if (is_string($from) && $partition = $this->getPartition($from)) { + $this->mainPartition = $partition; + if ($alias) { + $this->mainPartition->addAlias($from, $alias); + } + } + return parent::from($from, $alias); + } + + public function innerJoin($fromAlias, $join, $alias, $condition = null): self { + return $this->join($fromAlias, $join, $alias, $condition); + } + + public function leftJoin($fromAlias, $join, $alias, $condition = null): self { + return $this->join($fromAlias, (string)$join, $alias, $condition, PartitionQuery::JOIN_MODE_LEFT); + } + + public function join($fromAlias, $join, $alias, $condition = null, $joinMode = PartitionQuery::JOIN_MODE_INNER): self { + $partition = $this->getPartition($join); + $fromPartition = $this->getPartition($fromAlias); + if ($partition && $partition !== $this->mainPartition) { + // join from the main db to a partition + + $joinCondition = JoinCondition::parse($condition, $join, $alias, $fromAlias); + $partition->addAlias($join, $alias); + + if (!isset($this->splitQueries[$partition->name])) { + $this->splitQueries[$partition->name] = new PartitionQuery( + $this->newQuery(), + $joinCondition->fromAlias ?? $joinCondition->fromColumn, $joinCondition->toAlias ?? $joinCondition->toColumn, + $joinMode + ); + $this->splitQueries[$partition->name]->query->from($join, $alias); + $this->ensureSelect($joinCondition->fromColumn, $joinCondition->fromAlias); + $this->ensureSelect($joinCondition->toColumn, $joinCondition->toAlias); + } else { + $query = $this->splitQueries[$partition->name]->query; + if ($partition->containsAlias($fromAlias)) { + $query->innerJoin($fromAlias, $join, $alias, $condition); + } else { + throw new InvalidPartitionedQueryException("Can't join across partition boundaries more than once"); + } + } + $this->splitQueries[$partition->name]->query->andWhere(...$joinCondition->toConditions); + parent::andWhere(...$joinCondition->fromConditions); + return $this; + } elseif ($fromPartition && $fromPartition !== $partition) { + // join from partition, to the main db + + $joinCondition = JoinCondition::parse($condition, $join, $alias, $fromAlias); + if (str_starts_with($fromPartition->name, 'from_')) { + $partitionName = $fromPartition->name; + } else { + $partitionName = 'from_' . $fromPartition->name; + } + + if (!isset($this->splitQueries[$partitionName])) { + $newPartition = new PartitionSplit($partitionName, [$join]); + $newPartition->addAlias($join, $alias); + $this->partitions[] = $newPartition; + + $this->splitQueries[$partitionName] = new PartitionQuery( + $this->newQuery(), + $joinCondition->fromAlias ?? $joinCondition->fromColumn, $joinCondition->toAlias ?? $joinCondition->toColumn, + $joinMode + ); + $this->ensureSelect($joinCondition->fromColumn, $joinCondition->fromAlias); + $this->ensureSelect($joinCondition->toColumn, $joinCondition->toAlias); + $this->splitQueries[$partitionName]->query->from($join, $alias); + $this->splitQueries[$partitionName]->query->andWhere(...$joinCondition->toConditions); + parent::andWhere(...$joinCondition->fromConditions); + } else { + $fromPartition->addTable($join); + $fromPartition->addAlias($join, $alias); + + $query = $this->splitQueries[$partitionName]->query; + $query->innerJoin($fromAlias, $join, $alias, $condition); + } + return $this; + } else { + // join within the main db or a partition + if ($joinMode === PartitionQuery::JOIN_MODE_INNER) { + return parent::innerJoin($fromAlias, $join, $alias, $condition); + } elseif ($joinMode === PartitionQuery::JOIN_MODE_LEFT) { + return parent::leftJoin($fromAlias, $join, $alias, $condition); + } elseif ($joinMode === PartitionQuery::JOIN_MODE_RIGHT) { + return parent::rightJoin($fromAlias, $join, $alias, $condition); + } else { + throw new \InvalidArgumentException("Invalid join mode: $joinMode"); + } + } + } + + /** + * Flatten a list of predicates by merging the parts of any "AND" expression into the list of predicates + * + * @param array $predicates + * @return array + */ + private function flattenPredicates(array $predicates): array { + $result = []; + foreach ($predicates as $predicate) { + if ($predicate instanceof CompositeExpression && $predicate->getType() === CompositeExpression::TYPE_AND) { + $result = array_merge($result, $this->flattenPredicates($predicate->getParts())); + } else { + $result[] = $predicate; + } + } + return $result; + } + + /** + * Split an array of predicates (WHERE query parts) by the partition they reference + * + * @param array $predicates + * @return array<string, array> + */ + private function splitPredicatesByParts(array $predicates): array { + $predicates = $this->flattenPredicates($predicates); + + $partitionPredicates = []; + foreach ($predicates as $predicate) { + $partition = $this->getPartitionForPredicate((string)$predicate); + if ($this->mainPartition === $partition) { + $partitionPredicates[''][] = $predicate; + } elseif ($partition) { + $partitionPredicates[$partition->name][] = $predicate; + } else { + $partitionPredicates[''][] = $predicate; + } + } + return $partitionPredicates; + } + + public function where(...$predicates) { + return $this->andWhere(...$predicates); + } + + public function andWhere(...$where) { + if ($where) { + foreach ($this->splitPredicatesByParts($where) as $alias => $predicates) { + if (isset($this->splitQueries[$alias])) { + // when there is a condition on a table being left-joined it starts to behave as if it's an inner join + // since any joined column that doesn't have the left part will not match the condition + // when there the condition is `$joinToColumn IS NULL` we instead mark the query as excluding the left half + if ($this->splitQueries[$alias]->joinMode === PartitionQuery::JOIN_MODE_LEFT) { + $this->splitQueries[$alias]->joinMode = PartitionQuery::JOIN_MODE_INNER; + + $column = $this->quoteHelper->quoteColumnName($this->splitQueries[$alias]->joinToColumn); + foreach ($predicates as $predicate) { + if ((string)$predicate === "$column IS NULL") { + $this->splitQueries[$alias]->joinMode = PartitionQuery::JOIN_MODE_LEFT_NULL; + } else { + $this->splitQueries[$alias]->query->andWhere($predicate); + } + } + } else { + $this->splitQueries[$alias]->query->andWhere(...$predicates); + } + } else { + parent::andWhere(...$predicates); + } + } + } + return $this; + } + + + private function getPartitionForPredicate(string $predicate): ?PartitionSplit { + foreach ($this->partitions as $partition) { + + if (str_contains($predicate, '?')) { + $this->hasPositionalParameter = true; + } + if ($partition->checkPredicateForTable($predicate)) { + return $partition; + } + } + return null; + } + + public function update($update = null, $alias = null) { + return parent::update($update, $alias); + } + + public function insert($insert = null) { + return parent::insert($insert); + } + + public function delete($delete = null, $alias = null) { + return parent::delete($delete, $alias); + } + + public function setMaxResults($maxResults) { + if ($maxResults > 0) { + $this->limit = (int)$maxResults; + } + return parent::setMaxResults($maxResults); + } + + public function setFirstResult($firstResult) { + if ($firstResult > 0) { + $this->offset = (int)$firstResult; + } + return parent::setFirstResult($firstResult); + } + + public function executeQuery(?IDBConnection $connection = null): IResult { + $this->applySelects(); + if ($this->splitQueries && $this->hasPositionalParameter) { + throw new InvalidPartitionedQueryException("Partitioned queries aren't allowed to to positional arguments"); + } + foreach ($this->splitQueries as $split) { + $split->query->setParameters($this->getParameters(), $this->getParameterTypes()); + } + if (count($this->splitQueries) > 0) { + $hasNonLeftJoins = array_reduce($this->splitQueries, function (bool $hasNonLeftJoins, PartitionQuery $query) { + return $hasNonLeftJoins || $query->joinMode !== PartitionQuery::JOIN_MODE_LEFT; + }, false); + if ($hasNonLeftJoins) { + if (is_int($this->limit)) { + throw new InvalidPartitionedQueryException('Limit is not allowed in partitioned queries'); + } + if (is_int($this->offset)) { + throw new InvalidPartitionedQueryException('Offset is not allowed in partitioned queries'); + } + } + } + + $s = $this->getSQL(); + $result = parent::executeQuery($connection); + if (count($this->splitQueries) > 0) { + return new PartitionedResult($this->splitQueries, $result); + } else { + return $result; + } + } + + public function executeStatement(?IDBConnection $connection = null): int { + if (count($this->splitQueries)) { + throw new InvalidPartitionedQueryException("Partitioning write queries isn't supported"); + } + return parent::executeStatement($connection); + } + + public function getSQL() { + $this->applySelects(); + return parent::getSQL(); + } + + public function getPartitionCount(): int { + return count($this->splitQueries) + 1; + } + + public function hintShardKey(string $column, mixed $value, bool $overwrite = false): self { + if (str_contains($column, '.')) { + [$alias, $column] = explode('.', $column); + $partition = $this->getPartition($alias); + if ($partition) { + $this->splitQueries[$partition->name]->query->hintShardKey($column, $value, $overwrite); + } else { + parent::hintShardKey($column, $value, $overwrite); + } + } else { + parent::hintShardKey($column, $value, $overwrite); + } + return $this; + } +} diff --git a/lib/private/DB/QueryBuilder/Partitioned/PartitionedResult.php b/lib/private/DB/QueryBuilder/Partitioned/PartitionedResult.php new file mode 100644 index 00000000000..b3b59e26298 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Partitioned/PartitionedResult.php @@ -0,0 +1,61 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB\QueryBuilder\Partitioned; + +use OC\DB\ArrayResult; +use OCP\DB\IResult; +use PDO; + +/** + * Combine the results of multiple join parts into a single result + */ +class PartitionedResult extends ArrayResult { + private bool $fetched = false; + + /** + * @param PartitionQuery[] $splitOfParts + * @param IResult $result + */ + public function __construct( + private array $splitOfParts, + private IResult $result, + ) { + parent::__construct([]); + } + + public function closeCursor(): bool { + return $this->result->closeCursor(); + } + + public function fetch(int $fetchMode = PDO::FETCH_ASSOC) { + $this->fetchRows(); + return parent::fetch($fetchMode); + } + + public function fetchAll(int $fetchMode = PDO::FETCH_ASSOC): array { + $this->fetchRows(); + return parent::fetchAll($fetchMode); + } + + public function rowCount(): int { + $this->fetchRows(); + return parent::rowCount(); + } + + private function fetchRows(): void { + if (!$this->fetched) { + $this->fetched = true; + $this->rows = $this->result->fetchAll(); + foreach ($this->splitOfParts as $part) { + $this->rows = $part->mergeWith($this->rows); + } + $this->count = count($this->rows); + } + } +} diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php new file mode 100644 index 00000000000..1d44c049793 --- /dev/null +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -0,0 +1,1399 @@ +<?php + +/** + * 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\Query\QueryException; +use OC\DB\ConnectionAdapter; +use OC\DB\Exceptions\DbalException; +use OC\DB\QueryBuilder\ExpressionBuilder\MySqlExpressionBuilder; +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\IResult; +use OCP\DB\QueryBuilder\ICompositeExpression; +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 { + /** @var ConnectionAdapter */ + private $connection; + + /** @var SystemConfig */ + private $systemConfig; + + private LoggerInterface $logger; + + /** @var \Doctrine\DBAL\Query\QueryBuilder */ + private $queryBuilder; + + /** @var QuoteHelper */ + private $helper; + + /** @var bool */ + private $automaticTablePrefix = true; + private bool $nonEmptyWhere = false; + + /** @var string */ + protected $lastInsertedTable; + private array $selectedColumns = []; + + /** + * Initializes a new QueryBuilder. + * + * @param ConnectionAdapter $connection + * @param SystemConfig $systemConfig + */ + public function __construct(ConnectionAdapter $connection, SystemConfig $systemConfig, LoggerInterface $logger) { + $this->connection = $connection; + $this->systemConfig = $systemConfig; + $this->logger = $logger; + $this->queryBuilder = new \Doctrine\DBAL\Query\QueryBuilder($this->connection->getInner()); + $this->helper = new QuoteHelper(); + } + + /** + * 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. + * @since 8.2.0 + */ + public function automaticTablePrefix($enabled) { + $this->automaticTablePrefix = (bool)$enabled; + } + + /** + * Gets an ExpressionBuilder used for object-oriented construction of query expressions. + * This producer method is intended for convenient inline usage. Example: + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where($qb->expr()->eq('u.id', 1)); + * </code> + * + * For more complex expression construction, consider storing the expression + * builder object in a local variable. + * + * @return \OCP\DB\QueryBuilder\IExpressionBuilder + */ + public function expr() { + 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), + }; + } + + /** + * Gets an FunctionBuilder used for object-oriented construction of query functions. + * This producer method is intended for convenient inline usage. Example: + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where($qb->fun()->md5('u.id')); + * </code> + * + * For more complex function construction, consider storing the function + * builder object in a local variable. + * + * @return \OCP\DB\QueryBuilder\IFunctionBuilder + */ + public function func() { + 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), + }; + } + + /** + * Gets the type of the currently built query. + * + * @return integer + */ + public function getType() { + return $this->queryBuilder->getType(); + } + + /** + * Gets the associated DBAL Connection for this query builder. + * + * @return \OCP\IDBConnection + */ + public function getConnection() { + return $this->connection; + } + + /** + * Gets the state of this query builder instance. + * + * @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(); + } + + private function prepareForExecute() { + if ($this->systemConfig->getValue('log_query', false)) { + try { + $params = []; + foreach ($this->getParameters() as $placeholder => $value) { + if ($value instanceof \DateTimeInterface) { + $params[] = $placeholder . ' => DateTime:\'' . $value->format('c') . '\''; + } elseif (is_array($value)) { + $params[] = $placeholder . ' => (\'' . implode('\', \'', $value) . '\')'; + } else { + $params[] = $placeholder . ' => \'' . $value . '\''; + } + } + if (empty($params)) { + $this->logger->debug('DB QueryBuilder: \'{query}\'', [ + 'query' => $this->getSQL(), + 'app' => 'core', + ]); + } else { + $this->logger->debug('DB QueryBuilder: \'{query}\' with parameters: {params}', [ + 'query' => $this->getSQL(), + 'params' => implode(', ', $params), + 'app' => 'core', + ]); + } + } catch (\Error $e) { + // likely an error during conversion of $value to string + $this->logger->error('DB QueryBuilder: error trying to log SQL query', ['exception' => $e]); + } + } + + // 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) { + if (is_array($parameter)) { + $count = count($parameter); + $numberOfParameters += $count; + $hasTooLargeArrayParameter = $hasTooLargeArrayParameter || ($count > 1000); + } + } + + if ($hasTooLargeArrayParameter) { + $exception = new QueryException('More than 1000 expressions in a list are not allowed on Oracle.'); + $this->logger->error($exception->getMessage(), [ + 'query' => $this->getSQL(), + 'app' => 'core', + 'exception' => $exception, + ]); + } + + if ($numberOfParameters > 65535) { + $exception = new QueryException('The number of parameters must not exceed 65535. Restriction by PostgreSQL.'); + $this->logger->error($exception->getMessage(), [ + 'query' => $this->getSQL(), + 'app' => 'core', + '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(); + + throw $previous; + } + } + + public function executeQuery(?IDBConnection $connection = null): IResult { + if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) { + throw new \RuntimeException('Invalid query type, expected SELECT query'); + } + + $this->prepareForExecute(); + if (!$connection) { + $connection = $this->connection; + } + + return $connection->executeQuery( + $this->getSQL(), + $this->getParameters(), + $this->getParameterTypes(), + ); + } + + 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'); + } + + $this->prepareForExecute(); + if (!$connection) { + $connection = $this->connection; + } + + return $connection->executeStatement( + $this->getSQL(), + $this->getParameters(), + $this->getParameterTypes(), + ); + } + + + /** + * Gets the complete SQL string formed by the current specifications of this QueryBuilder. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('User', 'u') + * echo $qb->getSQL(); // SELECT u FROM User u + * </code> + * + * @return string The SQL query string. + */ + public function getSQL() { + return $this->queryBuilder->getSQL(); + } + + /** + * Sets a query parameter for the query being constructed. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where('u.id = :user_id') + * ->setParameter(':user_id', 1); + * </code> + * + * @param string|integer $key The parameter position or name. + * @param mixed $value The parameter value. + * @param string|null|int $type One of the IQueryBuilder::PARAM_* constants. + * + * @return $this This QueryBuilder instance. + */ + public function setParameter($key, $value, $type = null) { + $this->queryBuilder->setParameter($key, $value, $type); + + return $this; + } + + /** + * Sets a collection of query parameters for the query being constructed. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where('u.id = :user_id1 OR u.id = :user_id2') + * ->setParameters(array( + * ':user_id1' => 1, + * ':user_id2' => 2 + * )); + * </code> + * + * @param array $params The query parameters to set. + * @param array $types The query parameters types to set. + * + * @return $this This QueryBuilder instance. + */ + public function setParameters(array $params, array $types = []) { + $this->queryBuilder->setParameters($params, $types); + + return $this; + } + + /** + * Gets all defined query parameters for the query being constructed indexed by parameter index or name. + * + * @return array The currently defined query parameters indexed by parameter index or name. + */ + public function getParameters() { + return $this->queryBuilder->getParameters(); + } + + /** + * Gets a (previously set) query parameter of the query being constructed. + * + * @param mixed $key The key (index or name) of the bound parameter. + * + * @return mixed The value of the bound parameter. + */ + public function getParameter($key) { + return $this->queryBuilder->getParameter($key); + } + + /** + * Gets all defined query parameter types for the query being constructed indexed by parameter index or name. + * + * @return array The currently defined query parameter types indexed by parameter index or name. + */ + public function getParameterTypes() { + return $this->queryBuilder->getParameterTypes(); + } + + /** + * Gets a (previously set) query parameter type of the query being constructed. + * + * @param mixed $key The key (index or name) of the bound parameter type. + * + * @return mixed The value of the bound parameter type. + */ + public function getParameterType($key) { + return $this->queryBuilder->getParameterType($key); + } + + /** + * Sets the position of the first result to retrieve (the "offset"). + * + * @param int $firstResult The first result to return. + * + * @return $this This QueryBuilder instance. + */ + public function setFirstResult($firstResult) { + $this->queryBuilder->setFirstResult((int)$firstResult); + + return $this; + } + + /** + * Gets the position of the first result the query object was set to retrieve (the "offset"). + * Returns 0 if {@link setFirstResult} was not applied to this QueryBuilder. + * + * @return int The position of the first result. + */ + public function getFirstResult() { + return $this->queryBuilder->getFirstResult(); + } + + /** + * Sets the maximum number of results to retrieve (the "limit"). + * + * NOTE: Setting max results to "0" will cause mixed behaviour. While most + * of the databases will just return an empty result set, Oracle will return + * all entries. + * + * @param int|null $maxResults The maximum number of results to retrieve. + * + * @return $this This QueryBuilder instance. + */ + public function setMaxResults($maxResults) { + if ($maxResults === null) { + $this->queryBuilder->setMaxResults($maxResults); + } else { + $this->queryBuilder->setMaxResults((int)$maxResults); + } + + return $this; + } + + /** + * Gets the maximum number of results the query object was set to retrieve (the "limit"). + * Returns NULL if {@link setMaxResults} was not applied to this query builder. + * + * @return int|null The maximum number of results. + */ + public function getMaxResults() { + return $this->queryBuilder->getMaxResults(); + } + + /** + * Specifies an item that is to be returned in the query result. + * Replaces any previously specified selections, if any. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.id', 'p.id') + * ->from('users', 'u') + * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id'); + * </code> + * + * @param mixed ...$selects The selection expressions. + * + * '@return $this This QueryBuilder instance. + */ + public function select(...$selects) { + if (count($selects) === 1 && is_array($selects[0])) { + $selects = $selects[0]; + } + $this->addOutputColumns($selects); + + $this->queryBuilder->select( + $this->helper->quoteColumnNames($selects) + ); + + return $this; + } + + /** + * Specifies an item that is to be returned with a different name in the query result. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->selectAlias('u.id', 'user_id') + * ->from('users', 'u') + * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id'); + * </code> + * + * @param mixed $select The selection expressions. + * @param string $alias The column alias used in the constructed query. + * + * @return $this This QueryBuilder instance. + */ + public function selectAlias($select, $alias) { + $this->queryBuilder->addSelect( + $this->helper->quoteColumnName($select) . ' AS ' . $this->helper->quoteColumnName($alias) + ); + $this->addOutputColumns([$alias]); + + return $this; + } + + /** + * Specifies an item that is to be returned uniquely in the query result. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->selectDistinct('type') + * ->from('users'); + * </code> + * + * @param mixed $select The selection expressions. + * + * @return $this This QueryBuilder instance. + */ + public function selectDistinct($select) { + if (!is_array($select)) { + $select = [$select]; + } + $this->addOutputColumns($select); + + $quotedSelect = $this->helper->quoteColumnNames($select); + + $this->queryBuilder->addSelect( + 'DISTINCT ' . implode(', ', $quotedSelect) + ); + + return $this; + } + + /** + * Adds an item that is to be returned in the query result. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.id') + * ->addSelect('p.id') + * ->from('users', 'u') + * ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id'); + * </code> + * + * @param mixed ...$selects The selection expression. + * + * @return $this This QueryBuilder instance. + */ + public function addSelect(...$selects) { + if (count($selects) === 1 && is_array($selects[0])) { + $selects = $selects[0]; + } + $this->addOutputColumns($selects); + + $this->queryBuilder->addSelect( + $this->helper->quoteColumnNames($selects) + ); + + 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. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->delete('users', 'u') + * ->where('u.id = :user_id'); + * ->setParameter(':user_id', 1); + * </code> + * + * @param string $delete The table whose rows are subject to the deletion. + * @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 + ); + + return $this; + } + + /** + * Turns the query being built into a bulk update query that ranges over + * a certain table + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->update('users', 'u') + * ->set('u.password', md5('password')) + * ->where('u.id = ?'); + * </code> + * + * @param string $update The table whose rows are subject to the update. + * @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 + ); + + return $this; + } + + /** + * Turns the query being built into an insert query that inserts into + * a certain table + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->insert('users') + * ->values( + * array( + * 'name' => '?', + * 'password' => '?' + * ) + * ); + * </code> + * + * @param string $insert The table into which the rows should be inserted. + * + * @return $this This QueryBuilder instance. + */ + public function insert($insert = null) { + $this->queryBuilder->insert( + $this->getTableName($insert) + ); + + $this->lastInsertedTable = $insert; + + return $this; + } + + /** + * Creates and adds a query root corresponding to the table identified by the + * given alias, forming a cartesian product with any existing query roots. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.id') + * ->from('users', 'u') + * </code> + * + * @param string|IQueryFunction $from The table. + * @param string|null $alias The alias of the table. + * + * @return $this This QueryBuilder instance. + */ + public function from($from, $alias = null) { + $this->queryBuilder->from( + $this->getTableName($from), + $this->quoteAlias($alias) + ); + + return $this; + } + + /** + * Creates and adds a join to the query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1'); + * </code> + * + * @param string $fromAlias The alias that points to a from clause. + * @param string $join The table name to join. + * @param string $alias The alias of the join table. + * @param string|ICompositeExpression|null $condition The condition for the join. + * + * @return $this This QueryBuilder instance. + */ + public function join($fromAlias, $join, $alias, $condition = null) { + $this->queryBuilder->join( + $this->quoteAlias($fromAlias), + $this->getTableName($join), + $this->quoteAlias($alias), + $condition + ); + + return $this; + } + + /** + * Creates and adds a join to the query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); + * </code> + * + * @param string $fromAlias The alias that points to a from clause. + * @param string $join The table name to join. + * @param string $alias The alias of the join table. + * @param string|ICompositeExpression|null $condition The condition for the join. + * + * @return $this This QueryBuilder instance. + */ + public function innerJoin($fromAlias, $join, $alias, $condition = null) { + $this->queryBuilder->innerJoin( + $this->quoteAlias($fromAlias), + $this->getTableName($join), + $this->quoteAlias($alias), + $condition + ); + + return $this; + } + + /** + * Creates and adds a left join to the query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); + * </code> + * + * @param string $fromAlias The alias that points to a from clause. + * @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. + * + * @return $this This QueryBuilder instance. + */ + public function leftJoin($fromAlias, $join, $alias, $condition = null) { + $this->queryBuilder->leftJoin( + $this->quoteAlias($fromAlias), + $this->getTableName($join), + $this->quoteAlias($alias), + $condition + ); + + return $this; + } + + /** + * Creates and adds a right join to the query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); + * </code> + * + * @param string $fromAlias The alias that points to a from clause. + * @param string $join The table name to join. + * @param string $alias The alias of the join table. + * @param string|ICompositeExpression|null $condition The condition for the join. + * + * @return $this This QueryBuilder instance. + */ + public function rightJoin($fromAlias, $join, $alias, $condition = null) { + $this->queryBuilder->rightJoin( + $this->quoteAlias($fromAlias), + $this->getTableName($join), + $this->quoteAlias($alias), + $condition + ); + + return $this; + } + + /** + * Sets a new value for a column in a bulk update query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->update('users', 'u') + * ->set('u.password', md5('password')) + * ->where('u.id = ?'); + * </code> + * + * @param string $key The column to set. + * @param ILiteral|IParameter|IQueryFunction|string $value The value, expression, placeholder, etc. + * + * @return $this This QueryBuilder instance. + */ + public function set($key, $value) { + $this->queryBuilder->set( + $this->helper->quoteColumnName($key), + $this->helper->quoteColumnName($value) + ); + + return $this; + } + + /** + * Specifies one or more restrictions to the query result. + * Replaces any previously specified restrictions, if any. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->where('u.id = ?'); + * + * // You can optionally programmatically build and/or expressions + * $qb = $conn->getQueryBuilder(); + * + * $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')) + * ->where($or); + * </code> + * + * @param mixed ...$predicates The restriction predicates. + * + * @return $this This QueryBuilder instance. + */ + public function where(...$predicates) { + 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 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 + ); + + return $this; + } + + /** + * Adds one or more restrictions to the query results, forming a logical + * conjunction with any previously specified restrictions. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u') + * ->from('users', 'u') + * ->where('u.username LIKE ?') + * ->andWhere('u.is_active = 1'); + * </code> + * + * @param mixed ...$where The query restrictions. + * + * @return $this This QueryBuilder instance. + * + * @see where() + */ + public function andWhere(...$where) { + $this->nonEmptyWhere = true; + call_user_func_array( + [$this->queryBuilder, 'andWhere'], + $where + ); + + return $this; + } + + /** + * Adds one or more restrictions to the query results, forming a logical + * disjunction with any previously specified restrictions. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->where('u.id = 1') + * ->orWhere('u.id = 2'); + * </code> + * + * @param mixed ...$where The WHERE statement. + * + * @return $this This QueryBuilder instance. + * + * @see where() + */ + public function orWhere(...$where) { + $this->nonEmptyWhere = true; + call_user_func_array( + [$this->queryBuilder, 'orWhere'], + $where + ); + + return $this; + } + + /** + * Specifies a grouping over the results of the query. + * Replaces any previously specified groupings, if any. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->groupBy('u.id'); + * </code> + * + * @param mixed ...$groupBys The grouping expression. + * + * @return $this This QueryBuilder instance. + */ + public function groupBy(...$groupBys) { + if (count($groupBys) === 1 && is_array($groupBys[0])) { + $groupBys = $groupBys[0]; + } + + call_user_func_array( + [$this->queryBuilder, 'groupBy'], + $this->helper->quoteColumnNames($groupBys) + ); + + return $this; + } + + /** + * Adds a grouping expression to the query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->select('u.name') + * ->from('users', 'u') + * ->groupBy('u.lastLogin'); + * ->addGroupBy('u.createdAt') + * </code> + * + * @param mixed ...$groupBy The grouping expression. + * + * @return $this This QueryBuilder instance. + */ + public function addGroupBy(...$groupBy) { + call_user_func_array( + [$this->queryBuilder, 'addGroupBy'], + $this->helper->quoteColumnNames($groupBy) + ); + + return $this; + } + + /** + * Sets a value for a column in an insert query. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->insert('users') + * ->values( + * array( + * 'name' => '?' + * ) + * ) + * ->setValue('password', '?'); + * </code> + * + * @param string $column The column into which the value should be inserted. + * @param IParameter|string $value The value that should be inserted into the column. + * + * @return $this This QueryBuilder instance. + */ + public function setValue($column, $value) { + $this->queryBuilder->setValue( + $this->helper->quoteColumnName($column), + (string)$value + ); + + return $this; + } + + /** + * Specifies values for an insert query indexed by column names. + * Replaces any previous values, if any. + * + * <code> + * $qb = $conn->getQueryBuilder() + * ->insert('users') + * ->values( + * array( + * 'name' => '?', + * 'password' => '?' + * ) + * ); + * </code> + * + * @param array $values The values to specify for the insert query indexed by column names. + * + * @return $this This QueryBuilder instance. + */ + public function values(array $values) { + $quotedValues = []; + foreach ($values as $key => $value) { + $quotedValues[$this->helper->quoteColumnName($key)] = $value; + } + + $this->queryBuilder->values($quotedValues); + + return $this; + } + + /** + * Specifies a restriction over the groups of the query. + * Replaces any previous having restrictions, if any. + * + * @param mixed ...$having The restriction over the groups. + * + * @return $this This QueryBuilder instance. + */ + public function having(...$having) { + call_user_func_array( + [$this->queryBuilder, 'having'], + $having + ); + + return $this; + } + + /** + * Adds a restriction over the groups of the query, forming a logical + * conjunction with any existing having restrictions. + * + * @param mixed ...$having The restriction to append. + * + * @return $this This QueryBuilder instance. + */ + public function andHaving(...$having) { + call_user_func_array( + [$this->queryBuilder, 'andHaving'], + $having + ); + + return $this; + } + + /** + * Adds a restriction over the groups of the query, forming a logical + * disjunction with any existing having restrictions. + * + * @param mixed ...$having The restriction to add. + * + * @return $this This QueryBuilder instance. + */ + public function orHaving(...$having) { + call_user_func_array( + [$this->queryBuilder, 'orHaving'], + $having + ); + + return $this; + } + + /** + * Specifies an ordering for the query results. + * Replaces any previously specified orderings, if any. + * + * @param string|IQueryFunction|ILiteral|IParameter $sort The ordering expression. + * @param string $order The ordering direction. + * + * @return $this This QueryBuilder instance. + */ + public function orderBy($sort, $order = null) { + $this->queryBuilder->orderBy( + $this->helper->quoteColumnName($sort), + $order + ); + + return $this; + } + + /** + * Adds an ordering to the query results. + * + * @param string|ILiteral|IParameter|IQueryFunction $sort The ordering expression. + * @param string $order The ordering direction. + * + * @return $this This QueryBuilder instance. + */ + public function addOrderBy($sort, $order = null) { + $this->queryBuilder->addOrderBy( + $this->helper->quoteColumnName($sort), + $order + ); + + return $this; + } + + /** + * Gets a query part by its name. + * + * @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); + } + + /** + * 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(); + } + + /** + * Resets SQL parts. + * + * @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; + } + + /** + * Resets a single SQL part. + * + * @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; + } + + /** + * Creates a new named parameter and bind the value $value to it. + * + * This method provides a shortcut for PDOStatement::bindValue + * when using prepared statements. + * + * The parameter $value specifies the value that you want to bind. If + * $placeholder is not provided bindValue() will automatically create a + * placeholder for you. An automatic placeholder will be of the name + * ':dcValue1', ':dcValue2' etc. + * + * For more information see {@link https://www.php.net/pdostatement-bindparam} + * + * Example: + * <code> + * $value = 2; + * $q->eq( 'id', $q->bindValue( $value ) ); + * $stmt = $q->executeQuery(); // executed with 'id = 2' + * </code> + * + * @license New BSD License + * @link http://www.zetacomponents.org + * + * @param mixed $value + * @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. + */ + public function createNamedParameter($value, $type = IQueryBuilder::PARAM_STR, $placeHolder = null) { + return new Parameter($this->queryBuilder->createNamedParameter($value, $type, $placeHolder)); + } + + /** + * Creates a new positional parameter and bind the given value to it. + * + * Attention: If you are using positional parameters with the query builder you have + * to be very careful to bind all parameters in the order they appear in the SQL + * statement , otherwise they get bound in the wrong order which can lead to serious + * bugs in your code. + * + * Example: + * <code> + * $qb = $conn->getQueryBuilder(); + * $qb->select('u.*') + * ->from('users', 'u') + * ->where('u.username = ' . $qb->createPositionalParameter('Foo', IQueryBuilder::PARAM_STR)) + * ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', IQueryBuilder::PARAM_STR)) + * </code> + * + * @param mixed $value + * @param IQueryBuilder::PARAM_* $type + * + * @return IParameter + */ + public function createPositionalParameter($value, $type = IQueryBuilder::PARAM_STR) { + return new Parameter($this->queryBuilder->createPositionalParameter($value, $type)); + } + + /** + * Creates a new parameter + * + * Example: + * <code> + * $qb = $conn->getQueryBuilder(); + * $qb->select('u.*') + * ->from('users', 'u') + * ->where('u.username = ' . $qb->createParameter('name')) + * ->setParameter('name', 'Bar', IQueryBuilder::PARAM_STR)) + * </code> + * + * @param string $name + * + * @return IParameter + */ + public function createParameter($name) { + return new Parameter(':' . $name); + } + + /** + * Creates a new function + * + * Attention: Column names inside the call have to be quoted before hand + * + * Example: + * <code> + * $qb = $conn->getQueryBuilder(); + * $qb->select($qb->createFunction('COUNT(*)')) + * ->from('users', 'u') + * echo $qb->getSQL(); // SELECT COUNT(*) FROM `users` u + * </code> + * <code> + * $qb = $conn->getQueryBuilder(); + * $qb->select($qb->createFunction('COUNT(`column`)')) + * ->from('users', 'u') + * echo $qb->getSQL(); // SELECT COUNT(`column`) FROM `users` u + * </code> + * + * @param string $call + * + * @return IQueryFunction + */ + public function createFunction($call) { + return new QueryFunction($call); + } + + /** + * Used to get the id of the last inserted element + * @return int + * @throws \BadMethodCallException When being called before an insert query has been run. + */ + public function getLastInsertId(): int { + if ($this->getType() === \Doctrine\DBAL\Query\QueryBuilder::INSERT && $this->lastInsertedTable) { + // lastInsertId() needs the prefix but no quotes + $table = $this->prefixTableName($this->lastInsertedTable); + return $this->connection->lastInsertId($table); + } + + throw new \BadMethodCallException('Invalid call to getLastInsertId without using insert() before.'); + } + + /** + * Returns the table name quoted and with database prefix as needed by the implementation + * + * @param string|IQueryFunction $table + * @return string + */ + public function getTableName($table) { + if ($table instanceof IQueryFunction) { + return (string)$table; + } + + $table = $this->prefixTableName($table); + return $this->helper->quoteColumnName($table); + } + + /** + * Returns the table name with database prefix as needed by the implementation + * + * Was protected until version 30. + * + * @param string $table + * @return string + */ + public function prefixTableName(string $table): string { + if ($this->automaticTablePrefix === false || str_starts_with($table, '*PREFIX*')) { + return $table; + } + + return '*PREFIX*' . $table; + } + + /** + * Returns the column name quoted and with table alias prefix as needed by the implementation + * + * @param string $column + * @param string $tableAlias + * @return string + */ + public function getColumnName($column, $tableAlias = '') { + if ($tableAlias !== '') { + $tableAlias .= '.'; + } + + return $this->helper->quoteColumnName($tableAlias . $column); + } + + /** + * Returns the column name quoted and with table alias prefix as needed by the implementation + * + * @param string $alias + * @return string + */ + public function quoteAlias($alias) { + if ($alias === '' || $alias === null) { + return $alias; + } + + 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; + } + +} diff --git a/lib/private/DB/QueryBuilder/QueryFunction.php b/lib/private/DB/QueryBuilder/QueryFunction.php new file mode 100644 index 00000000000..7f2ab584a73 --- /dev/null +++ b/lib/private/DB/QueryBuilder/QueryFunction.php @@ -0,0 +1,23 @@ +<?php + +/** + * 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 OCP\DB\QueryBuilder\IQueryFunction; + +class QueryFunction implements IQueryFunction { + /** @var string */ + protected $function; + + public function __construct($function) { + $this->function = $function; + } + + public function __toString(): string { + return (string)$this->function; + } +} diff --git a/lib/private/DB/QueryBuilder/QuoteHelper.php b/lib/private/DB/QueryBuilder/QuoteHelper.php new file mode 100644 index 00000000000..7ce4b359638 --- /dev/null +++ b/lib/private/DB/QueryBuilder/QuoteHelper.php @@ -0,0 +1,66 @@ +<?php + +/** + * 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 OCP\DB\QueryBuilder\ILiteral; +use OCP\DB\QueryBuilder\IParameter; +use OCP\DB\QueryBuilder\IQueryFunction; + +class QuoteHelper { + /** + * @param array|string|ILiteral|IParameter|IQueryFunction $strings string, Literal or Parameter + * @return array|string + */ + public function quoteColumnNames($strings) { + if (!is_array($strings)) { + return $this->quoteColumnName($strings); + } + + $return = []; + foreach ($strings as $string) { + $return[] = $this->quoteColumnName($string); + } + + return $return; + } + + /** + * @param string|ILiteral|IParameter|IQueryFunction $string string, Literal or Parameter + * @return string + */ + public function quoteColumnName($string) { + if ($string instanceof IParameter || $string instanceof ILiteral || $string instanceof IQueryFunction) { + return (string)$string; + } + + if ($string === null || $string === 'null' || $string === '*') { + return $string; + } + + if (!is_string($string)) { + throw new \InvalidArgumentException('Only strings, Literals and Parameters are allowed'); + } + + $string = str_replace(' AS ', ' as ', $string); + if (substr_count($string, ' as ')) { + return implode(' as ', array_map([$this, 'quoteColumnName'], explode(' as ', $string, 2))); + } + + if (substr_count($string, '.')) { + [$alias, $columnName] = explode('.', $string, 2); + + if ($columnName === '*') { + return '`' . $alias . '`.*'; + } + + return '`' . $alias . '`.`' . $columnName . '`'; + } + + return '`' . $string . '`'; + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/AutoIncrementHandler.php b/lib/private/DB/QueryBuilder/Sharded/AutoIncrementHandler.php new file mode 100644 index 00000000000..3a230ea544d --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/AutoIncrementHandler.php @@ -0,0 +1,155 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OCP\ICacheFactory; +use OCP\IMemcache; +use OCP\IMemcacheTTL; + +/** + * A helper to atomically determine the next auto increment value for a sharded table + * + * Since we can't use the database's auto-increment (since each db doesn't know about the keys in the other shards) + * we need external logic for doing the auto increment + */ +class AutoIncrementHandler { + public const MIN_VALID_KEY = 1000; + public const TTL = 365 * 24 * 60 * 60; + + private ?IMemcache $cache = null; + + public function __construct( + private ICacheFactory $cacheFactory, + private ShardConnectionManager $shardConnectionManager, + ) { + if (PHP_INT_SIZE < 8) { + throw new \Exception('sharding is only supported with 64bit php'); + } + } + + private function getCache(): IMemcache { + if (is_null($this->cache)) { + $cache = $this->cacheFactory->createDistributed('shared_autoincrement'); + if ($cache instanceof IMemcache) { + $this->cache = $cache; + } else { + throw new \Exception('Distributed cache ' . get_class($cache) . ' is not suitable'); + } + } + return $this->cache; + } + + /** + * Get the next value for the given shard definition + * + * The returned key is unique and incrementing, but not sequential. + * The shard id is encoded in the first byte of the returned value + * + * @param ShardDefinition $shardDefinition + * @return int + * @throws \Exception + */ + public function getNextPrimaryKey(ShardDefinition $shardDefinition, int $shard): int { + $retries = 0; + while ($retries < 5) { + $next = $this->getNextInner($shardDefinition); + if ($next !== null) { + if ($next > ShardDefinition::MAX_PRIMARY_KEY) { + throw new \Exception('Max primary key of ' . ShardDefinition::MAX_PRIMARY_KEY . ' exceeded'); + } + // we encode the shard the primary key was originally inserted into to allow guessing the shard by primary key later on + return ($next << 8) | $shard; + } else { + $retries++; + } + } + throw new \Exception('Failed to get next primary key'); + } + + /** + * auto increment logic without retry + * + * @param ShardDefinition $shardDefinition + * @return int|null either the next primary key or null if the call needs to be retried + */ + private function getNextInner(ShardDefinition $shardDefinition): ?int { + $cache = $this->getCache(); + // because this function will likely be called concurrently from different requests + // the implementation needs to ensure that the cached value can be cleared, invalidated or re-calculated at any point between our cache calls + // care must be taken that the logic remains fully resilient against race conditions + + // in the ideal case, the last primary key is stored in the cache and we can just do an `inc` + // if that is not the case we find the highest used id in the database increment it, and save it in the cache + + // prevent inc from returning `1` if the key doesn't exist by setting it to a non-numeric value + $cache->add($shardDefinition->table, 'empty-placeholder', self::TTL); + $next = $cache->inc($shardDefinition->table); + + if ($cache instanceof IMemcacheTTL) { + $cache->setTTL($shardDefinition->table, self::TTL); + } + + // the "add + inc" trick above isn't strictly atomic, so as a safety we reject any result that to small + // to handle the edge case of the stored value disappearing between the add and inc + if (is_int($next) && $next >= self::MIN_VALID_KEY) { + return $next; + } elseif (is_int($next)) { + // we hit the edge case, so invalidate the cached value + if (!$cache->cas($shardDefinition->table, $next, 'empty-placeholder')) { + // someone else is changing the value concurrently, give up and retry + return null; + } + } + + // discard the encoded initial shard + $current = $this->getMaxFromDb($shardDefinition); + $next = max($current, self::MIN_VALID_KEY) + 1; + if ($cache->cas($shardDefinition->table, 'empty-placeholder', $next)) { + return $next; + } + + // another request set the cached value before us, so we should just be able to inc + $next = $cache->inc($shardDefinition->table); + if (is_int($next) && $next >= self::MIN_VALID_KEY) { + return $next; + } elseif (is_int($next)) { + // key got cleared, invalidate and retry + $cache->cas($shardDefinition->table, $next, 'empty-placeholder'); + return null; + } else { + // cleanup any non-numeric value other than the placeholder if that got stored somehow + $cache->ncad($shardDefinition->table, 'empty-placeholder'); + // retry + return null; + } + } + + /** + * Get the maximum primary key value from the shards, note that this has already stripped any embedded shard id + */ + private function getMaxFromDb(ShardDefinition $shardDefinition): int { + $max = $shardDefinition->fromFileId; + $query = $this->shardConnectionManager->getConnection($shardDefinition, 0)->getQueryBuilder(); + $query->select($shardDefinition->primaryKey) + ->from($shardDefinition->table) + ->orderBy($shardDefinition->primaryKey, 'DESC') + ->setMaxResults(1); + foreach ($shardDefinition->getAllShards() as $shard) { + $connection = $this->shardConnectionManager->getConnection($shardDefinition, $shard); + $result = $query->executeQuery($connection)->fetchOne(); + if ($result) { + if ($result > $shardDefinition->fromFileId) { + $result = $result >> 8; + } + $max = max($max, $result); + } + } + return $max; + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/CrossShardMoveHelper.php b/lib/private/DB/QueryBuilder/Sharded/CrossShardMoveHelper.php new file mode 100644 index 00000000000..81530b56725 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/CrossShardMoveHelper.php @@ -0,0 +1,162 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; + +/** + * Utility methods for implementing logic that moves data across shards + */ +class CrossShardMoveHelper { + public function __construct( + private ShardConnectionManager $connectionManager, + ) { + } + + public function getConnection(ShardDefinition $shardDefinition, int $shardKey): IDBConnection { + return $this->connectionManager->getConnection($shardDefinition, $shardDefinition->getShardForKey($shardKey)); + } + + /** + * Update the shard key of a set of rows, moving them to a different shard if needed + * + * @param ShardDefinition $shardDefinition + * @param string $table + * @param string $shardColumn + * @param int $sourceShardKey + * @param int $targetShardKey + * @param string $primaryColumn + * @param int[] $primaryKeys + * @return void + */ + public function moveCrossShards(ShardDefinition $shardDefinition, string $table, string $shardColumn, int $sourceShardKey, int $targetShardKey, string $primaryColumn, array $primaryKeys): void { + $sourceShard = $shardDefinition->getShardForKey($sourceShardKey); + $targetShard = $shardDefinition->getShardForKey($targetShardKey); + $sourceConnection = $this->connectionManager->getConnection($shardDefinition, $sourceShard); + if ($sourceShard === $targetShard) { + $this->updateItems($sourceConnection, $table, $shardColumn, $targetShardKey, $primaryColumn, $primaryKeys); + + return; + } + $targetConnection = $this->connectionManager->getConnection($shardDefinition, $targetShard); + + $sourceItems = $this->loadItems($sourceConnection, $table, $primaryColumn, $primaryKeys); + foreach ($sourceItems as &$sourceItem) { + $sourceItem[$shardColumn] = $targetShardKey; + } + if (!$sourceItems) { + return; + } + + $sourceConnection->beginTransaction(); + $targetConnection->beginTransaction(); + try { + $this->saveItems($targetConnection, $table, $sourceItems); + $this->deleteItems($sourceConnection, $table, $primaryColumn, $primaryKeys); + + $targetConnection->commit(); + $sourceConnection->commit(); + } catch (\Exception $e) { + $sourceConnection->rollback(); + $targetConnection->rollback(); + throw $e; + } + } + + /** + * Load rows from a table to move + * + * @param IDBConnection $connection + * @param string $table + * @param string $primaryColumn + * @param int[] $primaryKeys + * @return array[] + */ + public function loadItems(IDBConnection $connection, string $table, string $primaryColumn, array $primaryKeys): array { + $query = $connection->getQueryBuilder(); + $query->select('*') + ->from($table) + ->where($query->expr()->in($primaryColumn, $query->createParameter('keys'))); + + $chunks = array_chunk($primaryKeys, 1000); + + $results = []; + foreach ($chunks as $chunk) { + $query->setParameter('keys', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $results = array_merge($results, $query->execute()->fetchAll()); + } + + return $results; + } + + /** + * Save modified rows + * + * @param IDBConnection $connection + * @param string $table + * @param array[] $items + * @return void + */ + public function saveItems(IDBConnection $connection, string $table, array $items): void { + if (count($items) === 0) { + return; + } + $query = $connection->getQueryBuilder(); + $query->insert($table); + foreach ($items[0] as $column => $value) { + $query->setValue($column, $query->createParameter($column)); + } + + foreach ($items as $item) { + foreach ($item as $column => $value) { + if (is_int($column)) { + $query->setParameter($column, $value, IQueryBuilder::PARAM_INT); + } else { + $query->setParameter($column, $value); + } + } + $query->executeStatement(); + } + } + + /** + * @param IDBConnection $connection + * @param string $table + * @param string $primaryColumn + * @param int[] $primaryKeys + * @return void + */ + public function updateItems(IDBConnection $connection, string $table, string $shardColumn, int $targetShardKey, string $primaryColumn, array $primaryKeys): void { + $query = $connection->getQueryBuilder(); + $query->update($table) + ->set($shardColumn, $query->createNamedParameter($targetShardKey, IQueryBuilder::PARAM_INT)) + ->where($query->expr()->in($primaryColumn, $query->createNamedParameter($primaryKeys, IQueryBuilder::PARAM_INT_ARRAY))); + $query->executeQuery()->fetchAll(); + } + + /** + * @param IDBConnection $connection + * @param string $table + * @param string $primaryColumn + * @param int[] $primaryKeys + * @return void + */ + public function deleteItems(IDBConnection $connection, string $table, string $primaryColumn, array $primaryKeys): void { + $query = $connection->getQueryBuilder(); + $query->delete($table) + ->where($query->expr()->in($primaryColumn, $query->createParameter('keys'))); + $chunks = array_chunk($primaryKeys, 1000); + + foreach ($chunks as $chunk) { + $query->setParameter('keys', $chunk, IQueryBuilder::PARAM_INT_ARRAY); + $query->executeStatement(); + } + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/HashShardMapper.php b/lib/private/DB/QueryBuilder/Sharded/HashShardMapper.php new file mode 100644 index 00000000000..af778489a2d --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/HashShardMapper.php @@ -0,0 +1,21 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OCP\DB\QueryBuilder\Sharded\IShardMapper; + +/** + * Map string key to an int-range by hashing the key + */ +class HashShardMapper implements IShardMapper { + public function getShardForKey(int $key, int $count): int { + $int = unpack('L', substr(md5((string)$key, true), 0, 4))[1]; + return $int % $count; + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/InvalidShardedQueryException.php b/lib/private/DB/QueryBuilder/Sharded/InvalidShardedQueryException.php new file mode 100644 index 00000000000..733a6acaf9d --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/InvalidShardedQueryException.php @@ -0,0 +1,29 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB\QueryBuilder\Sharded; + +/** + * Queries on sharded table has the following limitations: + * + * 1. Either the shard key (e.g. "storage") or primary key (e.g. "fileid") must be mentioned in the query. + * Or the query must be explicitly marked as running across all shards. + * + * For queries where it isn't possible to set one of these keys in the query normally, you can set it using `hintShardKey` + * + * 2. Insert statements must always explicitly set the shard key + * 3. A query on a sharded table is not allowed to join on the same table + * 4. Right joins are not allowed on sharded tables + * 5. Updating the shard key where the new shard key maps to a different shard is not allowed + * + * Moving rows to a different shard needs to be implemented manually. `CrossShardMoveHelper` provides + * some tools to help make this easier. + */ +class InvalidShardedQueryException extends \Exception { + +} diff --git a/lib/private/DB/QueryBuilder/Sharded/RoundRobinShardMapper.php b/lib/private/DB/QueryBuilder/Sharded/RoundRobinShardMapper.php new file mode 100644 index 00000000000..a5694b06507 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/RoundRobinShardMapper.php @@ -0,0 +1,20 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OCP\DB\QueryBuilder\Sharded\IShardMapper; + +/** + * Map string key to an int-range by hashing the key + */ +class RoundRobinShardMapper implements IShardMapper { + public function getShardForKey(int $key, int $count): int { + return $key % $count; + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/ShardConnectionManager.php b/lib/private/DB/QueryBuilder/Sharded/ShardConnectionManager.php new file mode 100644 index 00000000000..74358e3ca96 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/ShardConnectionManager.php @@ -0,0 +1,52 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OC\DB\ConnectionAdapter; +use OC\DB\ConnectionFactory; +use OC\SystemConfig; +use OCP\IDBConnection; + +/** + * Keeps track of the db connections to the various shards + */ +class ShardConnectionManager { + /** @var array<string, IDBConnection> */ + private array $connections = []; + + public function __construct( + private SystemConfig $config, + private ConnectionFactory $factory, + ) { + } + + public function getConnection(ShardDefinition $shardDefinition, int $shard): IDBConnection { + $connectionKey = $shardDefinition->table . '_' . $shard; + + if (isset($this->connections[$connectionKey])) { + return $this->connections[$connectionKey]; + } + + if ($shard === ShardDefinition::MIGRATION_SHARD) { + $this->connections[$connectionKey] = \OC::$server->get(IDBConnection::class); + } elseif (isset($shardDefinition->shards[$shard])) { + $this->connections[$connectionKey] = $this->createConnection($shardDefinition->shards[$shard]); + } else { + throw new \InvalidArgumentException("invalid shard key $shard only " . count($shardDefinition->shards) . ' configured'); + } + + return $this->connections[$connectionKey]; + } + + private function createConnection(array $shardConfig): IDBConnection { + $shardConfig['sharding'] = []; + $type = $this->config->getValue('dbtype', 'sqlite'); + return new ConnectionAdapter($this->factory->getConnection($type, $shardConfig)); + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/ShardDefinition.php b/lib/private/DB/QueryBuilder/Sharded/ShardDefinition.php new file mode 100644 index 00000000000..4f98079d92d --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/ShardDefinition.php @@ -0,0 +1,80 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OCP\DB\QueryBuilder\Sharded\IShardMapper; + +/** + * Configuration for a shard setup + */ +class ShardDefinition { + // we reserve the bottom byte of the primary key for the initial shard, so the total shard count is limited to what we can fit there + // additionally, shard id 255 is reserved for migration purposes + public const MAX_SHARDS = 255; + public const MIGRATION_SHARD = 255; + + public const PRIMARY_KEY_MASK = 0x7F_FF_FF_FF_FF_FF_FF_00; + public const PRIMARY_KEY_SHARD_MASK = 0x00_00_00_00_00_00_00_FF; + // since we reserve 1 byte for the shard index, we only have 56 bits of primary key space + public const MAX_PRIMARY_KEY = PHP_INT_MAX >> 8; + + /** + * @param string $table + * @param string $primaryKey + * @param string $shardKey + * @param string[] $companionKeys + * @param IShardMapper $shardMapper + * @param string[] $companionTables + * @param array $shards + */ + public function __construct( + public string $table, + public string $primaryKey, + public array $companionKeys, + public string $shardKey, + public IShardMapper $shardMapper, + public array $companionTables, + public array $shards, + public int $fromFileId, + public int $fromStorageId, + ) { + if (count($this->shards) >= self::MAX_SHARDS) { + throw new \Exception('Only allowed maximum of ' . self::MAX_SHARDS . ' shards allowed'); + } + } + + public function hasTable(string $table): bool { + if ($this->table === $table) { + return true; + } + return in_array($table, $this->companionTables); + } + + public function getShardForKey(int $key): int { + if ($key < $this->fromStorageId) { + return self::MIGRATION_SHARD; + } + return $this->shardMapper->getShardForKey($key, count($this->shards)); + } + + /** + * @return list<int> + */ + public function getAllShards(): array { + if ($this->fromStorageId !== 0) { + return array_merge(array_keys($this->shards), [self::MIGRATION_SHARD]); + } else { + return array_keys($this->shards); + } + } + + public function isKey(string $column): bool { + return $column === $this->primaryKey || in_array($column, $this->companionKeys); + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/ShardQueryRunner.php b/lib/private/DB/QueryBuilder/Sharded/ShardQueryRunner.php new file mode 100644 index 00000000000..25e2a3d5f2d --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/ShardQueryRunner.php @@ -0,0 +1,200 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OC\DB\ArrayResult; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; + +/** + * Logic for running a query across a number of shards, combining the results + */ +class ShardQueryRunner { + public function __construct( + private ShardConnectionManager $shardConnectionManager, + private ShardDefinition $shardDefinition, + ) { + } + + /** + * Get the shards for a specific query or null if the shards aren't known in advance + * + * @param bool $allShards + * @param int[] $shardKeys + * @return null|int[] + */ + public function getShards(bool $allShards, array $shardKeys): ?array { + if ($allShards) { + return $this->shardDefinition->getAllShards(); + } + $allConfiguredShards = $this->shardDefinition->getAllShards(); + if (count($allConfiguredShards) === 1) { + return $allConfiguredShards; + } + if (empty($shardKeys)) { + return null; + } + $shards = array_map(function ($shardKey) { + return $this->shardDefinition->getShardForKey((int)$shardKey); + }, $shardKeys); + return array_values(array_unique($shards)); + } + + /** + * Try to get the shards that the keys are likely to be in, based on the shard the row was created + * + * @param int[] $primaryKeys + * @return int[] + */ + private function getLikelyShards(array $primaryKeys): array { + $shards = []; + foreach ($primaryKeys as $primaryKey) { + if ($primaryKey < $this->shardDefinition->fromFileId && !in_array(ShardDefinition::MIGRATION_SHARD, $shards)) { + $shards[] = ShardDefinition::MIGRATION_SHARD; + } + $encodedShard = $primaryKey & ShardDefinition::PRIMARY_KEY_SHARD_MASK; + if ($encodedShard < count($this->shardDefinition->shards) && !in_array($encodedShard, $shards)) { + $shards[] = $encodedShard; + } + } + return $shards; + } + + /** + * Execute a SELECT statement across the configured shards + * + * @param IQueryBuilder $query + * @param bool $allShards + * @param int[] $shardKeys + * @param int[] $primaryKeys + * @param array{column: string, order: string}[] $sortList + * @param int|null $limit + * @param int|null $offset + * @return IResult + */ + public function executeQuery( + IQueryBuilder $query, + bool $allShards, + array $shardKeys, + array $primaryKeys, + ?array $sortList = null, + ?int $limit = null, + ?int $offset = null, + ): IResult { + $shards = $this->getShards($allShards, $shardKeys); + $results = []; + if ($shards && count($shards) === 1) { + // trivial case + return $query->executeQuery($this->shardConnectionManager->getConnection($this->shardDefinition, $shards[0])); + } + // we have to emulate limit and offset, so we select offset+limit from all shards to ensure we have enough rows + // and then filter them down after we merged the results + if ($limit !== null && $offset !== null) { + $query->setMaxResults($limit + $offset); + } + + if ($shards) { + // we know exactly what shards we need to query + foreach ($shards as $shard) { + $shardConnection = $this->shardConnectionManager->getConnection($this->shardDefinition, $shard); + $subResult = $query->executeQuery($shardConnection); + $results = array_merge($results, $subResult->fetchAll()); + $subResult->closeCursor(); + } + } else { + // we don't know for sure what shards we need to query, + // we first try the shards that are "likely" to have the rows we want, based on the shard that the row was + // originally created in. If we then still haven't found all rows we try the rest of the shards + $likelyShards = $this->getLikelyShards($primaryKeys); + $unlikelyShards = array_diff($this->shardDefinition->getAllShards(), $likelyShards); + $shards = array_merge($likelyShards, $unlikelyShards); + + foreach ($shards as $shard) { + $shardConnection = $this->shardConnectionManager->getConnection($this->shardDefinition, $shard); + $subResult = $query->executeQuery($shardConnection); + $rows = $subResult->fetchAll(); + $results = array_merge($results, $rows); + $subResult->closeCursor(); + + if (count($rows) >= count($primaryKeys)) { + // we have all the rows we're looking for + break; + } + } + } + + if ($sortList) { + usort($results, function ($a, $b) use ($sortList) { + foreach ($sortList as $sort) { + $valueA = $a[$sort['column']] ?? null; + $valueB = $b[$sort['column']] ?? null; + $cmp = $valueA <=> $valueB; + if ($cmp === 0) { + continue; + } + if ($sort['order'] === 'DESC') { + $cmp = -$cmp; + } + return $cmp; + } + }); + } + + if ($limit !== null && $offset !== null) { + $results = array_slice($results, $offset, $limit); + } elseif ($limit !== null) { + $results = array_slice($results, 0, $limit); + } elseif ($offset !== null) { + $results = array_slice($results, $offset); + } + + return new ArrayResult($results); + } + + /** + * Execute an UPDATE or DELETE statement + * + * @param IQueryBuilder $query + * @param bool $allShards + * @param int[] $shardKeys + * @param int[] $primaryKeys + * @return int + * @throws \OCP\DB\Exception + */ + public function executeStatement(IQueryBuilder $query, bool $allShards, array $shardKeys, array $primaryKeys): int { + if ($query->getType() === \Doctrine\DBAL\Query\QueryBuilder::INSERT) { + throw new \Exception('insert queries need special handling'); + } + + $shards = $this->getShards($allShards, $shardKeys); + $maxCount = count($primaryKeys); + if ($shards && count($shards) === 1) { + return $query->executeStatement($this->shardConnectionManager->getConnection($this->shardDefinition, $shards[0])); + } elseif ($shards) { + $maxCount = PHP_INT_MAX; + } else { + // sort the likely shards before the rest, similar logic to `self::executeQuery` + $likelyShards = $this->getLikelyShards($primaryKeys); + $unlikelyShards = array_diff($this->shardDefinition->getAllShards(), $likelyShards); + $shards = array_merge($likelyShards, $unlikelyShards); + } + + $count = 0; + + foreach ($shards as $shard) { + $shardConnection = $this->shardConnectionManager->getConnection($this->shardDefinition, $shard); + $count += $query->executeStatement($shardConnection); + + if ($count >= $maxCount) { + break; + } + } + return $count; + } +} diff --git a/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php b/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php new file mode 100644 index 00000000000..04082f76ae8 --- /dev/null +++ b/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php @@ -0,0 +1,407 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Robin Appelman <robin@icewind.nl> + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB\QueryBuilder\Sharded; + +use OC\DB\QueryBuilder\CompositeExpression; +use OC\DB\QueryBuilder\ExtendedQueryBuilder; +use OC\DB\QueryBuilder\Parameter; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; + +/** + * A special query builder that automatically distributes queries over multiple database shards. + * + * This relies on `PartitionedQueryBuilder` to handle splitting of parts of the query that touch the sharded tables + * from the non-sharded tables. So the query build here should only either touch only sharded table or only non-sharded tables. + * + * Most of the logic in this class is concerned with extracting either the shard key (e.g. "storage") or primary key (e.g. "fileid") + * from the query. The logic for actually running the query across the shards is mostly delegated to `ShardQueryRunner`. + */ +class ShardedQueryBuilder extends ExtendedQueryBuilder { + private array $shardKeys = []; + private array $primaryKeys = []; + private ?ShardDefinition $shardDefinition = null; + /** @var bool Run the query across all shards */ + private bool $allShards = false; + private ?string $insertTable = null; + private mixed $lastInsertId = null; + private ?IDBConnection $lastInsertConnection = null; + private ?int $updateShardKey = null; + private ?int $limit = null; + private ?int $offset = null; + /** @var array{column: string, order: string}[] */ + private array $sortList = []; + private string $mainTable = ''; + + public function __construct( + IQueryBuilder $builder, + protected array $shardDefinitions, + protected ShardConnectionManager $shardConnectionManager, + protected AutoIncrementHandler $autoIncrementHandler, + ) { + parent::__construct($builder); + } + + public function getShardKeys(): array { + return $this->getKeyValues($this->shardKeys); + } + + public function getPrimaryKeys(): array { + return $this->getKeyValues($this->primaryKeys); + } + + private function getKeyValues(array $keys): array { + $values = []; + foreach ($keys as $key) { + $values = array_merge($values, $this->getKeyValue($key)); + } + return array_values(array_unique($values)); + } + + private function getKeyValue($value): array { + if ($value instanceof Parameter) { + $value = (string)$value; + } + if (is_string($value) && str_starts_with($value, ':')) { + $param = $this->getParameter(substr($value, 1)); + if (is_array($param)) { + return $param; + } else { + return [$param]; + } + } elseif ($value !== null) { + return [$value]; + } else { + return []; + } + } + + public function where(...$predicates) { + return $this->andWhere(...$predicates); + } + + public function andWhere(...$where) { + if ($where) { + foreach ($where as $predicate) { + $this->tryLoadShardKey($predicate); + } + parent::andWhere(...$where); + } + return $this; + } + + private function tryLoadShardKey($predicate): void { + if (!$this->shardDefinition) { + return; + } + if ($keys = $this->tryExtractShardKeys($predicate, $this->shardDefinition->shardKey)) { + $this->shardKeys += $keys; + } + if ($keys = $this->tryExtractShardKeys($predicate, $this->shardDefinition->primaryKey)) { + $this->primaryKeys += $keys; + } + foreach ($this->shardDefinition->companionKeys as $companionKey) { + if ($keys = $this->tryExtractShardKeys($predicate, $companionKey)) { + $this->primaryKeys += $keys; + } + } + } + + /** + * @param $predicate + * @param string $column + * @return string[] + */ + private function tryExtractShardKeys($predicate, string $column): array { + if ($predicate instanceof CompositeExpression) { + $values = []; + foreach ($predicate->getParts() as $part) { + $partValues = $this->tryExtractShardKeys($part, $column); + // for OR expressions, we can only rely on the predicate if all parts contain the comparison + if ($predicate->getType() === CompositeExpression::TYPE_OR && !$partValues) { + return []; + } + $values = array_merge($values, $partValues); + } + return $values; + } + $predicate = (string)$predicate; + // expect a condition in the form of 'alias1.column1 = placeholder' or 'alias1.column1 in placeholder' + if (substr_count($predicate, ' ') > 2) { + return []; + } + if (str_contains($predicate, ' = ')) { + $parts = explode(' = ', $predicate); + if ($parts[0] === "`{$column}`" || str_ends_with($parts[0], "`.`{$column}`")) { + return [$parts[1]]; + } else { + return []; + } + } + + if (str_contains($predicate, ' IN ')) { + $parts = explode(' IN ', $predicate); + if ($parts[0] === "`{$column}`" || str_ends_with($parts[0], "`.`{$column}`")) { + return [trim(trim($parts[1], '('), ')')]; + } else { + return []; + } + } + + return []; + } + + public function set($key, $value) { + if ($this->shardDefinition && $key === $this->shardDefinition->shardKey) { + $updateShardKey = $value; + } + return parent::set($key, $value); + } + + public function setValue($column, $value) { + if ($this->shardDefinition) { + if ($this->shardDefinition->isKey($column)) { + $this->primaryKeys[] = $value; + } + if ($column === $this->shardDefinition->shardKey) { + $this->shardKeys[] = $value; + } + } + return parent::setValue($column, $value); + } + + public function values(array $values) { + foreach ($values as $column => $value) { + $this->setValue($column, $value); + } + return $this; + } + + private function actOnTable(string $table): void { + $this->mainTable = $table; + foreach ($this->shardDefinitions as $shardDefinition) { + if ($shardDefinition->hasTable($table)) { + $this->shardDefinition = $shardDefinition; + } + } + } + + public function from($from, $alias = null) { + if (is_string($from) && $from) { + $this->actOnTable($from); + } + return parent::from($from, $alias); + } + + public function update($update = null, $alias = null) { + if (is_string($update) && $update) { + $this->actOnTable($update); + } + return parent::update($update, $alias); + } + + public function insert($insert = null) { + if (is_string($insert) && $insert) { + $this->insertTable = $insert; + $this->actOnTable($insert); + } + return parent::insert($insert); + } + + public function delete($delete = null, $alias = null) { + if (is_string($delete) && $delete) { + $this->actOnTable($delete); + } + return parent::delete($delete, $alias); + } + + private function checkJoin(string $table): void { + if ($this->shardDefinition) { + if ($table === $this->mainTable) { + throw new InvalidShardedQueryException("Sharded query on {$this->mainTable} isn't allowed to join on itself"); + } + if (!$this->shardDefinition->hasTable($table)) { + // this generally shouldn't happen as the partitioning logic should prevent this + // but the check is here just in case + throw new InvalidShardedQueryException("Sharded query on {$this->shardDefinition->table} isn't allowed to join on $table"); + } + } + } + + public function innerJoin($fromAlias, $join, $alias, $condition = null) { + $this->checkJoin($join); + return parent::innerJoin($fromAlias, $join, $alias, $condition); + } + + public function leftJoin($fromAlias, $join, $alias, $condition = null) { + $this->checkJoin($join); + return parent::leftJoin($fromAlias, $join, $alias, $condition); + } + + public function rightJoin($fromAlias, $join, $alias, $condition = null) { + if ($this->shardDefinition) { + throw new InvalidShardedQueryException("Sharded query on {$this->shardDefinition->table} isn't allowed to right join"); + } + return parent::rightJoin($fromAlias, $join, $alias, $condition); + } + + public function join($fromAlias, $join, $alias, $condition = null) { + return $this->innerJoin($fromAlias, $join, $alias, $condition); + } + + public function setMaxResults($maxResults) { + if ($maxResults > 0) { + $this->limit = (int)$maxResults; + } + return parent::setMaxResults($maxResults); + } + + public function setFirstResult($firstResult) { + if ($firstResult > 0) { + $this->offset = (int)$firstResult; + } + if ($this->shardDefinition && count($this->shardDefinition->shards) > 1) { + // we have to emulate offset + return $this; + } else { + return parent::setFirstResult($firstResult); + } + } + + public function addOrderBy($sort, $order = null) { + $this->registerOrder((string)$sort, (string)$order ?? 'ASC'); + return parent::addOrderBy($sort, $order); + } + + public function orderBy($sort, $order = null) { + $this->sortList = []; + $this->registerOrder((string)$sort, (string)$order ?? 'ASC'); + return parent::orderBy($sort, $order); + } + + private function registerOrder(string $column, string $order): void { + // handle `mime + 0` and similar by just sorting on the first part of the expression + [$column] = explode(' ', $column); + $column = trim($column, '`'); + $this->sortList[] = [ + 'column' => $column, + 'order' => strtoupper($order), + ]; + } + + public function hintShardKey(string $column, mixed $value, bool $overwrite = false): self { + if ($overwrite) { + $this->primaryKeys = []; + $this->shardKeys = []; + } + if ($this->shardDefinition?->isKey($column)) { + $this->primaryKeys[] = $value; + } + if ($column === $this->shardDefinition?->shardKey) { + $this->shardKeys[] = $value; + } + return $this; + } + + public function runAcrossAllShards(): self { + $this->allShards = true; + return $this; + } + + /** + * @throws InvalidShardedQueryException + */ + public function validate(): void { + if ($this->shardDefinition && $this->insertTable) { + if ($this->allShards) { + throw new InvalidShardedQueryException("Can't insert across all shards"); + } + if (empty($this->getShardKeys())) { + throw new InvalidShardedQueryException("Can't insert without shard key"); + } + } + if ($this->shardDefinition && !$this->allShards) { + if (empty($this->getShardKeys()) && empty($this->getPrimaryKeys())) { + throw new InvalidShardedQueryException('No shard key or primary key set for query'); + } + } + if ($this->shardDefinition && $this->updateShardKey) { + $newShardKey = $this->getKeyValue($this->updateShardKey); + $oldShardKeys = $this->getShardKeys(); + if (count($newShardKey) !== 1) { + throw new InvalidShardedQueryException("Can't set shard key to an array"); + } + $newShardKey = current($newShardKey); + if (empty($oldShardKeys)) { + throw new InvalidShardedQueryException("Can't update without shard key"); + } + $oldShards = array_values(array_unique(array_map(function ($shardKey) { + return $this->shardDefinition->getShardForKey((int)$shardKey); + }, $oldShardKeys))); + $newShard = $this->shardDefinition->getShardForKey((int)$newShardKey); + if ($oldShards === [$newShard]) { + throw new InvalidShardedQueryException('Update statement would move rows to a different shard'); + } + } + } + + public function executeQuery(?IDBConnection $connection = null): IResult { + $this->validate(); + if ($this->shardDefinition) { + $runner = new ShardQueryRunner($this->shardConnectionManager, $this->shardDefinition); + return $runner->executeQuery($this->builder, $this->allShards, $this->getShardKeys(), $this->getPrimaryKeys(), $this->sortList, $this->limit, $this->offset); + } + return parent::executeQuery($connection); + } + + public function executeStatement(?IDBConnection $connection = null): int { + $this->validate(); + if ($this->shardDefinition) { + $runner = new ShardQueryRunner($this->shardConnectionManager, $this->shardDefinition); + if ($this->insertTable) { + $shards = $runner->getShards($this->allShards, $this->getShardKeys()); + if (!$shards) { + throw new InvalidShardedQueryException("Can't insert without shard key"); + } + $count = 0; + foreach ($shards as $shard) { + $shardConnection = $this->shardConnectionManager->getConnection($this->shardDefinition, $shard); + if (!$this->primaryKeys && $this->shardDefinition->table === $this->insertTable) { + $id = $this->autoIncrementHandler->getNextPrimaryKey($this->shardDefinition, $shard); + parent::setValue($this->shardDefinition->primaryKey, $this->createParameter('__generated_primary_key')); + $this->setParameter('__generated_primary_key', $id, self::PARAM_INT); + $this->lastInsertId = $id; + } + $count += parent::executeStatement($shardConnection); + + $this->lastInsertConnection = $shardConnection; + } + return $count; + } else { + return $runner->executeStatement($this->builder, $this->allShards, $this->getShardKeys(), $this->getPrimaryKeys()); + } + } + return parent::executeStatement($connection); + } + + public function getLastInsertId(): int { + if ($this->lastInsertId) { + return $this->lastInsertId; + } + if ($this->lastInsertConnection) { + $table = $this->builder->prefixTableName($this->insertTable); + return $this->lastInsertConnection->lastInsertId($table); + } else { + return parent::getLastInsertId(); + } + } + + +} |