diff options
Diffstat (limited to 'lib/private/DB')
62 files changed, 9041 insertions, 0 deletions
diff --git a/lib/private/DB/Adapter.php b/lib/private/DB/Adapter.php new file mode 100644 index 00000000000..8f1b8e6d75f --- /dev/null +++ b/lib/private/DB/Adapter.php @@ -0,0 +1,133 @@ +<?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; + +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Exception\UniqueConstraintViolationException; +use OC\DB\Exceptions\DbalException; + +/** + * This handles the way we use to write queries, into something that can be + * handled by the database abstraction layer. + */ +class Adapter { + /** + * @var \OC\DB\Connection $conn + */ + protected $conn; + + public function __construct($conn) { + $this->conn = $conn; + } + + /** + * @param string $table name + * + * @return int id of last insert statement + * @throws Exception + */ + public function lastInsertId($table) { + return (int)$this->conn->realLastInsertId($table); + } + + /** + * @param string $statement that needs to be changed so the db can handle it + * @return string changed statement + */ + public function fixupStatement($statement) { + return $statement; + } + + /** + * Create an exclusive read+write lock on a table + * + * @throws Exception + * @since 9.1.0 + */ + public function lockTable(string $tableName) { + $this->conn->beginTransaction(); + $this->conn->executeUpdate('LOCK TABLE `' . $tableName . '` IN EXCLUSIVE MODE'); + } + + /** + * Release a previous acquired lock again + * + * @throws Exception + * @since 9.1.0 + */ + public function unlockTable() { + $this->conn->commit(); + } + + /** + * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance + * it is needed that there is also a unique constraint on the values. Then this method will + * catch the exception and return 0. + * + * @param string $table The table name (will replace *PREFIX* with the actual prefix) + * @param array $input data that should be inserted into the table (column name => value) + * @param array|null $compare List of values that should be checked for "if not exists" + * If this is null or an empty array, all keys of $input will be compared + * Please note: text fields (clob) must not be used in the compare array + * @return int number of inserted rows + * @throws Exception + * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371 + */ + public function insertIfNotExist($table, $input, ?array $compare = null) { + $compare = $compare ?: array_keys($input); + + // Prepare column names and generate placeholders + $columns = '`' . implode('`,`', array_keys($input)) . '`'; + $placeholders = implode(', ', array_fill(0, count($input), '?')); + + $query = 'INSERT INTO `' . $table . '` (' . $columns . ') ' + . 'SELECT ' . $placeholders . ' ' + . 'FROM `' . $table . '` WHERE '; + + $inserts = array_values($input); + foreach ($compare as $key) { + $query .= '`' . $key . '`'; + if (is_null($input[$key])) { + $query .= ' IS NULL AND '; + } else { + $inserts[] = $input[$key]; + $query .= ' = ? AND '; + } + } + $query = substr($query, 0, -5); + $query .= ' HAVING COUNT(*) = 0'; + + try { + return $this->conn->executeUpdate($query, $inserts); + } catch (UniqueConstraintViolationException $e) { + // This exception indicates a concurrent insert happened between + // the insert and the sub-select in the insert, which is safe to ignore. + // More details: https://github.com/nextcloud/server/pull/12315 + return 0; + } + } + + /** + * @throws \OCP\DB\Exception + */ + public function insertIgnoreConflict(string $table, array $values) : int { + try { + $builder = $this->conn->getQueryBuilder(); + $builder->insert($table); + foreach ($values as $key => $value) { + $builder->setValue($key, $builder->createNamedParameter($value)); + } + return $builder->executeStatement(); + } catch (DbalException $e) { + if ($e->getReason() === \OCP\DB\Exception::REASON_UNIQUE_CONSTRAINT_VIOLATION) { + return 0; + } + throw $e; + } + } +} diff --git a/lib/private/DB/AdapterMySQL.php b/lib/private/DB/AdapterMySQL.php new file mode 100644 index 00000000000..63c75607379 --- /dev/null +++ b/lib/private/DB/AdapterMySQL.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; + +class AdapterMySQL extends Adapter { + /** @var string */ + protected $collation; + + /** + * @param string $tableName + */ + public function lockTable($tableName) { + $this->conn->executeUpdate('LOCK TABLES `' . $tableName . '` WRITE'); + } + + public function unlockTable() { + $this->conn->executeUpdate('UNLOCK TABLES'); + } + + public function fixupStatement($statement) { + $statement = str_replace(' ILIKE ', ' COLLATE ' . $this->getCollation() . ' LIKE ', $statement); + return $statement; + } + + protected function getCollation(): string { + if (!$this->collation) { + $params = $this->conn->getParams(); + $this->collation = $params['collation'] ?? (($params['charset'] ?? 'utf8') . '_general_ci'); + } + + return $this->collation; + } + + public function insertIgnoreConflict(string $table, array $values): int { + $builder = $this->conn->getQueryBuilder(); + $builder->insert($table); + $updates = []; + foreach ($values as $key => $value) { + $builder->setValue($key, $builder->createNamedParameter($value)); + } + + /* + * We can't use ON DUPLICATE KEY UPDATE here because Nextcloud use the CLIENT_FOUND_ROWS flag + * With this flag the MySQL returns the number of selected rows + * instead of the number of affected/modified rows + * It's impossible to change this behaviour at runtime or for a single query + * Then, the result is 1 if a row is inserted and also 1 if a row is updated with same or different values + * + * With INSERT IGNORE, the result is 1 when a row is inserted, 0 otherwise + * + * Risk: it can also ignore other errors like type mismatch or truncated data… + */ + $res = $this->conn->executeStatement( + preg_replace('/^INSERT/i', 'INSERT IGNORE', $builder->getSQL()), + $builder->getParameters(), + $builder->getParameterTypes() + ); + + return $res; + } +} diff --git a/lib/private/DB/AdapterOCI8.php b/lib/private/DB/AdapterOCI8.php new file mode 100644 index 00000000000..0a509090bca --- /dev/null +++ b/lib/private/DB/AdapterOCI8.php @@ -0,0 +1,31 @@ +<?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; + +class AdapterOCI8 extends Adapter { + public function lastInsertId($table) { + if (is_null($table)) { + throw new \InvalidArgumentException('Oracle requires a table name to be passed into lastInsertId()'); + } + if ($table !== null) { + $suffix = '_SEQ'; + $table = '"' . $table . $suffix . '"'; + } + return $this->conn->realLastInsertId($table); + } + + public const UNIX_TIMESTAMP_REPLACEMENT = "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400"; + + public function fixupStatement($statement) { + $statement = preg_replace('/`(\w+)` ILIKE \?/', 'REGEXP_LIKE(`$1`, \'^\' || REPLACE(?, \'%\', \'.*\') || \'$\', \'i\')', $statement); + $statement = str_replace('`', '"', $statement); + $statement = str_ireplace('NOW()', 'CURRENT_TIMESTAMP', $statement); + $statement = str_ireplace('UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $statement); + return $statement; + } +} diff --git a/lib/private/DB/AdapterPgSql.php b/lib/private/DB/AdapterPgSql.php new file mode 100644 index 00000000000..db48c81c2c5 --- /dev/null +++ b/lib/private/DB/AdapterPgSql.php @@ -0,0 +1,37 @@ +<?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; + +class AdapterPgSql extends Adapter { + + public function lastInsertId($table) { + $result = $this->conn->executeQuery('SELECT lastval()'); + $val = $result->fetchOne(); + $result->free(); + return (int)$val; + } + + public const UNIX_TIMESTAMP_REPLACEMENT = 'cast(extract(epoch from current_timestamp) as integer)'; + public function fixupStatement($statement) { + $statement = str_replace('`', '"', $statement); + $statement = str_ireplace('UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $statement); + return $statement; + } + + public function insertIgnoreConflict(string $table, array $values) : int { + // "upsert" is only available since PgSQL 9.5, but the generic way + // would leave error logs in the DB. + $builder = $this->conn->getQueryBuilder(); + $builder->insert($table); + foreach ($values as $key => $value) { + $builder->setValue($key, $builder->createNamedParameter($value)); + } + $queryString = $builder->getSQL() . ' ON CONFLICT DO NOTHING'; + return $this->conn->executeUpdate($queryString, $builder->getParameters(), $builder->getParameterTypes()); + } +} diff --git a/lib/private/DB/AdapterSqlite.php b/lib/private/DB/AdapterSqlite.php new file mode 100644 index 00000000000..aeadf55ecf7 --- /dev/null +++ b/lib/private/DB/AdapterSqlite.php @@ -0,0 +1,94 @@ +<?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; + +use Doctrine\DBAL\Exception\UniqueConstraintViolationException; + +class AdapterSqlite extends Adapter { + /** + * @param string $tableName + */ + public function lockTable($tableName) { + $this->conn->executeUpdate('BEGIN EXCLUSIVE TRANSACTION'); + } + + public function unlockTable() { + $this->conn->executeUpdate('COMMIT TRANSACTION'); + } + + public function fixupStatement($statement) { + $statement = preg_replace('/`(\w+)` ILIKE \?/', 'LOWER($1) LIKE LOWER(?)', $statement); + $statement = str_replace('`', '"', $statement); + $statement = str_ireplace('NOW()', 'datetime(\'now\')', $statement); + $statement = str_ireplace('GREATEST(', 'MAX(', $statement); + $statement = str_ireplace('UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $statement); + return $statement; + } + + /** + * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance + * it is needed that there is also a unique constraint on the values. Then this method will + * catch the exception and return 0. + * + * @param string $table The table name (will replace *PREFIX* with the actual prefix) + * @param array $input data that should be inserted into the table (column name => value) + * @param array|null $compare List of values that should be checked for "if not exists" + * If this is null or an empty array, all keys of $input will be compared + * Please note: text fields (clob) must not be used in the compare array + * @return int number of inserted rows + * @throws \Doctrine\DBAL\Exception + * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371 + */ + public function insertIfNotExist($table, $input, ?array $compare = null) { + if (empty($compare)) { + $compare = array_keys($input); + } + $fieldList = '`' . implode('`,`', array_keys($input)) . '`'; + $query = "INSERT INTO `$table` ($fieldList) SELECT " + . str_repeat('?,', count($input) - 1) . '? ' + . " WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE "; + + $inserts = array_values($input); + foreach ($compare as $key) { + $query .= '`' . $key . '`'; + if (is_null($input[$key])) { + $query .= ' IS NULL AND '; + } else { + $inserts[] = $input[$key]; + $query .= ' = ? AND '; + } + } + $query = substr($query, 0, -5); + $query .= ')'; + + try { + return $this->conn->executeUpdate($query, $inserts); + } catch (UniqueConstraintViolationException $e) { + // if this is thrown then a concurrent insert happened between the insert and the sub-select in the insert, that should have avoided it + // it's fine to ignore this then + // + // more discussions about this can be found at https://github.com/nextcloud/server/pull/12315 + return 0; + } + } + + public function insertIgnoreConflict(string $table, array $values): int { + $builder = $this->conn->getQueryBuilder(); + $builder->insert($table); + $updates = []; + foreach ($values as $key => $value) { + $builder->setValue($key, $builder->createNamedParameter($value)); + } + + return $this->conn->executeStatement( + $builder->getSQL() . ' ON CONFLICT DO NOTHING', + $builder->getParameters(), + $builder->getParameterTypes() + ); + } +} diff --git a/lib/private/DB/ArrayResult.php b/lib/private/DB/ArrayResult.php new file mode 100644 index 00000000000..b567ad23d57 --- /dev/null +++ b/lib/private/DB/ArrayResult.php @@ -0,0 +1,74 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +namespace OC\DB; + +use OCP\DB\IResult; +use PDO; + +/** + * Wrap an array or rows into a result interface + */ +class ArrayResult implements IResult { + protected int $count; + + public function __construct( + protected array $rows, + ) { + $this->count = count($this->rows); + } + + public function closeCursor(): bool { + // noop + return true; + } + + public function fetch(int $fetchMode = PDO::FETCH_ASSOC) { + $row = array_shift($this->rows); + if (!$row) { + return false; + } + return match ($fetchMode) { + PDO::FETCH_ASSOC => $row, + PDO::FETCH_NUM => array_values($row), + PDO::FETCH_COLUMN => current($row), + default => throw new \InvalidArgumentException('Fetch mode not supported for array result'), + }; + + } + + public function fetchAll(int $fetchMode = PDO::FETCH_ASSOC): array { + return match ($fetchMode) { + PDO::FETCH_ASSOC => $this->rows, + PDO::FETCH_NUM => array_map(function ($row) { + return array_values($row); + }, $this->rows), + PDO::FETCH_COLUMN => array_map(function ($row) { + return current($row); + }, $this->rows), + default => throw new \InvalidArgumentException('Fetch mode not supported for array result'), + }; + } + + public function fetchColumn() { + return $this->fetchOne(); + } + + public function fetchOne() { + $row = $this->fetch(); + if ($row) { + return current($row); + } else { + return false; + } + } + + public function rowCount(): int { + return $this->count; + } +} diff --git a/lib/private/DB/BacktraceDebugStack.php b/lib/private/DB/BacktraceDebugStack.php new file mode 100644 index 00000000000..4afd3ce6a13 --- /dev/null +++ b/lib/private/DB/BacktraceDebugStack.php @@ -0,0 +1,20 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2022 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB; + +use Doctrine\DBAL\Logging\DebugStack; + +class BacktraceDebugStack extends DebugStack { + public function startQuery($sql, ?array $params = null, ?array $types = null) { + parent::startQuery($sql, $params, $types); + $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); + $this->queries[$this->currentQuery]['backtrace'] = $backtrace; + $this->queries[$this->currentQuery]['start'] = $this->start; + } +} diff --git a/lib/private/DB/Connection.php b/lib/private/DB/Connection.php new file mode 100644 index 00000000000..f86cbc341a4 --- /dev/null +++ b/lib/private/DB/Connection.php @@ -0,0 +1,970 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only + */ +namespace OC\DB; + +use Doctrine\Common\EventManager; +use Doctrine\DBAL\Cache\QueryCacheProfile; +use Doctrine\DBAL\Configuration; +use Doctrine\DBAL\Connections\PrimaryReadReplicaConnection; +use Doctrine\DBAL\Driver; +use Doctrine\DBAL\Driver\ServerInfoAwareConnection; +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Exception\ConnectionLost; +use Doctrine\DBAL\Platforms\MariaDBPlatform; +use Doctrine\DBAL\Platforms\MySQLPlatform; +use Doctrine\DBAL\Platforms\OraclePlatform; +use Doctrine\DBAL\Platforms\PostgreSQLPlatform; +use Doctrine\DBAL\Platforms\SqlitePlatform; +use Doctrine\DBAL\Result; +use Doctrine\DBAL\Schema\Schema; +use Doctrine\DBAL\Statement; +use OC\DB\QueryBuilder\Partitioned\PartitionedQueryBuilder; +use OC\DB\QueryBuilder\Partitioned\PartitionSplit; +use OC\DB\QueryBuilder\QueryBuilder; +use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler; +use OC\DB\QueryBuilder\Sharded\CrossShardMoveHelper; +use OC\DB\QueryBuilder\Sharded\RoundRobinShardMapper; +use OC\DB\QueryBuilder\Sharded\ShardConnectionManager; +use OC\DB\QueryBuilder\Sharded\ShardDefinition; +use OC\SystemConfig; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\DB\QueryBuilder\Sharded\IShardMapper; +use OCP\Diagnostics\IEventLogger; +use OCP\ICacheFactory; +use OCP\IDBConnection; +use OCP\ILogger; +use OCP\IRequestId; +use OCP\PreConditionNotMetException; +use OCP\Profiler\IProfiler; +use OCP\Security\ISecureRandom; +use OCP\Server; +use Psr\Clock\ClockInterface; +use Psr\Log\LoggerInterface; +use function count; +use function in_array; + +class Connection extends PrimaryReadReplicaConnection { + /** @var string */ + protected $tablePrefix; + + /** @var \OC\DB\Adapter $adapter */ + protected $adapter; + + /** @var SystemConfig */ + private $systemConfig; + + private ClockInterface $clock; + + private LoggerInterface $logger; + + protected $lockedTable = null; + + /** @var int */ + protected $queriesBuilt = 0; + + /** @var int */ + protected $queriesExecuted = 0; + + /** @var DbDataCollector|null */ + protected $dbDataCollector = null; + private array $lastConnectionCheck = []; + + protected ?float $transactionActiveSince = null; + + /** @var array<string, int> */ + protected $tableDirtyWrites = []; + + protected bool $logDbException = false; + private ?array $transactionBacktrace = null; + + protected bool $logRequestId; + protected string $requestId; + + /** @var array<string, list<string>> */ + protected array $partitions; + /** @var ShardDefinition[] */ + protected array $shards = []; + protected ShardConnectionManager $shardConnectionManager; + protected AutoIncrementHandler $autoIncrementHandler; + protected bool $isShardingEnabled; + + public const SHARD_PRESETS = [ + 'filecache' => [ + 'companion_keys' => [ + 'file_id', + ], + 'companion_tables' => [ + 'filecache_extended', + 'files_metadata', + ], + 'primary_key' => 'fileid', + 'shard_key' => 'storage', + 'table' => 'filecache', + ], + ]; + + /** + * Initializes a new instance of the Connection class. + * + * @throws \Exception + */ + public function __construct( + private array $params, + Driver $driver, + ?Configuration $config = null, + ?EventManager $eventManager = null, + ) { + if (!isset($params['adapter'])) { + throw new \Exception('adapter not set'); + } + if (!isset($params['tablePrefix'])) { + throw new \Exception('tablePrefix not set'); + } + /** + * @psalm-suppress InternalMethod + */ + parent::__construct($params, $driver, $config, $eventManager); + $this->adapter = new $params['adapter']($this); + $this->tablePrefix = $params['tablePrefix']; + $this->isShardingEnabled = isset($this->params['sharding']) && !empty($this->params['sharding']); + + if ($this->isShardingEnabled) { + /** @psalm-suppress InvalidArrayOffset */ + $this->shardConnectionManager = $this->params['shard_connection_manager'] ?? Server::get(ShardConnectionManager::class); + /** @psalm-suppress InvalidArrayOffset */ + $this->autoIncrementHandler = $this->params['auto_increment_handler'] ?? new AutoIncrementHandler( + Server::get(ICacheFactory::class), + $this->shardConnectionManager, + ); + } + $this->systemConfig = \OC::$server->getSystemConfig(); + $this->clock = Server::get(ClockInterface::class); + $this->logger = Server::get(LoggerInterface::class); + + $this->logRequestId = $this->systemConfig->getValue('db.log_request_id', false); + $this->logDbException = $this->systemConfig->getValue('db.log_exceptions', false); + $this->requestId = Server::get(IRequestId::class)->getId(); + + /** @var \OCP\Profiler\IProfiler */ + $profiler = Server::get(IProfiler::class); + if ($profiler->isEnabled()) { + $this->dbDataCollector = new DbDataCollector($this); + $profiler->add($this->dbDataCollector); + $debugStack = new BacktraceDebugStack(); + $this->dbDataCollector->setDebugStack($debugStack); + $this->_config->setSQLLogger($debugStack); + } + + /** @var array<string, array{shards: array[], mapper: ?string, from_primary_key: ?int, from_shard_key: ?int}> $shardConfig */ + $shardConfig = $this->params['sharding'] ?? []; + $shardNames = array_keys($shardConfig); + $this->shards = array_map(function (array $config, string $name) { + if (!isset(self::SHARD_PRESETS[$name])) { + throw new \Exception("Shard preset $name not found"); + } + + $shardMapperClass = $config['mapper'] ?? RoundRobinShardMapper::class; + $shardMapper = Server::get($shardMapperClass); + if (!$shardMapper instanceof IShardMapper) { + throw new \Exception("Invalid shard mapper: $shardMapperClass"); + } + return new ShardDefinition( + self::SHARD_PRESETS[$name]['table'], + self::SHARD_PRESETS[$name]['primary_key'], + self::SHARD_PRESETS[$name]['companion_keys'], + self::SHARD_PRESETS[$name]['shard_key'], + $shardMapper, + self::SHARD_PRESETS[$name]['companion_tables'], + $config['shards'], + $config['from_primary_key'] ?? 0, + $config['from_shard_key'] ?? 0, + ); + }, $shardConfig, $shardNames); + $this->shards = array_combine($shardNames, $this->shards); + $this->partitions = array_map(function (ShardDefinition $shard) { + return array_merge([$shard->table], $shard->companionTables); + }, $this->shards); + + $this->setNestTransactionsWithSavepoints(true); + } + + /** + * @return IDBConnection[] + */ + public function getShardConnections(): array { + $connections = []; + if ($this->isShardingEnabled) { + foreach ($this->shards as $shardDefinition) { + foreach ($shardDefinition->getAllShards() as $shard) { + if ($shard !== ShardDefinition::MIGRATION_SHARD) { + /** @var ConnectionAdapter $connection */ + $connections[] = $this->shardConnectionManager->getConnection($shardDefinition, $shard); + } + } + } + } + return $connections; + } + + /** + * @throws Exception + */ + public function connect($connectionName = null) { + try { + if ($this->_conn) { + $this->reconnectIfNeeded(); + /** @psalm-suppress InternalMethod */ + return parent::connect(); + } + + // Only trigger the event logger for the initial connect call + $eventLogger = Server::get(IEventLogger::class); + $eventLogger->start('connect:db', 'db connection opened'); + /** @psalm-suppress InternalMethod */ + $status = parent::connect(); + $eventLogger->end('connect:db'); + + $this->lastConnectionCheck[$this->getConnectionName()] = time(); + + return $status; + } catch (Exception $e) { + // throw a new exception to prevent leaking info from the stacktrace + throw new Exception('Failed to connect to the database: ' . $e->getMessage(), $e->getCode()); + } + } + + protected function performConnect(?string $connectionName = null): bool { + if (($connectionName ?? 'replica') === 'replica' + && count($this->params['replica']) === 1 + && $this->params['primary'] === $this->params['replica'][0]) { + return parent::performConnect('primary'); + } + return parent::performConnect($connectionName); + } + + public function getStats(): array { + return [ + 'built' => $this->queriesBuilt, + 'executed' => $this->queriesExecuted, + ]; + } + + /** + * Returns a QueryBuilder for the connection. + */ + public function getQueryBuilder(): IQueryBuilder { + $this->queriesBuilt++; + + $builder = new QueryBuilder( + new ConnectionAdapter($this), + $this->systemConfig, + $this->logger + ); + if ($this->isShardingEnabled && count($this->partitions) > 0) { + $builder = new PartitionedQueryBuilder( + $builder, + $this->shards, + $this->shardConnectionManager, + $this->autoIncrementHandler, + ); + foreach ($this->partitions as $name => $tables) { + $partition = new PartitionSplit($name, $tables); + $builder->addPartition($partition); + } + return $builder; + } else { + return $builder; + } + } + + /** + * Gets the QueryBuilder for the connection. + * + * @return \Doctrine\DBAL\Query\QueryBuilder + * @deprecated 8.0.0 please use $this->getQueryBuilder() instead + */ + public function createQueryBuilder() { + $backtrace = $this->getCallerBacktrace(); + $this->logger->debug('Doctrine QueryBuilder retrieved in {backtrace}', ['app' => 'core', 'backtrace' => $backtrace]); + $this->queriesBuilt++; + return parent::createQueryBuilder(); + } + + /** + * Gets the ExpressionBuilder for the connection. + * + * @return \Doctrine\DBAL\Query\Expression\ExpressionBuilder + * @deprecated 8.0.0 please use $this->getQueryBuilder()->expr() instead + */ + public function getExpressionBuilder() { + $backtrace = $this->getCallerBacktrace(); + $this->logger->debug('Doctrine ExpressionBuilder retrieved in {backtrace}', ['app' => 'core', 'backtrace' => $backtrace]); + $this->queriesBuilt++; + return parent::getExpressionBuilder(); + } + + /** + * Get the file and line that called the method where `getCallerBacktrace()` was used + * + * @return string + */ + protected function getCallerBacktrace() { + $traces = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2); + + // 0 is the method where we use `getCallerBacktrace` + // 1 is the target method which uses the method we want to log + if (isset($traces[1])) { + return $traces[1]['file'] . ':' . $traces[1]['line']; + } + + return ''; + } + + /** + * @return string + */ + public function getPrefix() { + return $this->tablePrefix; + } + + /** + * Prepares an SQL statement. + * + * @param string $statement The SQL statement to prepare. + * @param int|null $limit + * @param int|null $offset + * + * @return Statement The prepared statement. + * @throws Exception + */ + public function prepare($sql, $limit = null, $offset = null): Statement { + if ($limit === -1 || $limit === null) { + $limit = null; + } else { + $limit = (int)$limit; + } + if ($offset !== null) { + $offset = (int)$offset; + } + if (!is_null($limit)) { + $platform = $this->getDatabasePlatform(); + $sql = $platform->modifyLimitQuery($sql, $limit, $offset); + } + $statement = $this->finishQuery($sql); + + return parent::prepare($statement); + } + + /** + * Executes an, optionally parametrized, SQL query. + * + * If the query is parametrized, a prepared statement is used. + * If an SQLLogger is configured, the execution is logged. + * + * @param string $sql The SQL query to execute. + * @param array $params The parameters to bind to the query, if any. + * @param array $types The types the previous parameters are in. + * @param \Doctrine\DBAL\Cache\QueryCacheProfile|null $qcp The query cache profile, optional. + * + * @return Result The executed statement. + * + * @throws \Doctrine\DBAL\Exception + */ + public function executeQuery(string $sql, array $params = [], $types = [], ?QueryCacheProfile $qcp = null): Result { + $tables = $this->getQueriedTables($sql); + $now = $this->clock->now()->getTimestamp(); + $dirtyTableWrites = []; + foreach ($tables as $table) { + $lastAccess = $this->tableDirtyWrites[$table] ?? 0; + // Only very recent writes are considered dirty + if ($lastAccess >= ($now - 3)) { + $dirtyTableWrites[] = $table; + } + } + if ($this->isTransactionActive()) { + // Transacted queries go to the primary. The consistency of the primary guarantees that we can not run + // into a dirty read. + } elseif (count($dirtyTableWrites) === 0) { + // No tables read that could have been written already in the same request and no transaction active + // so we can switch back to the replica for reading as long as no writes happen that switch back to the primary + // We cannot log here as this would log too early in the server boot process + $this->ensureConnectedToReplica(); + } else { + // Read to a table that has been written to previously + // While this might not necessarily mean that we did a read after write it is an indication for a code path to check + $this->logger->log( + (int)($this->systemConfig->getValue('loglevel_dirty_database_queries', null) ?? 0), + 'dirty table reads: ' . $sql, + [ + 'tables' => array_keys($this->tableDirtyWrites), + 'reads' => $tables, + 'exception' => new \Exception('dirty table reads: ' . $sql), + ], + ); + // To prevent a dirty read on a replica that is slightly out of sync, we + // switch back to the primary. This is detrimental for performance but + // safer for consistency. + $this->ensureConnectedToPrimary(); + } + + $sql = $this->finishQuery($sql); + $this->queriesExecuted++; + $this->logQueryToFile($sql, $params); + try { + return parent::executeQuery($sql, $params, $types, $qcp); + } catch (\Exception $e) { + $this->logDatabaseException($e); + throw $e; + } + } + + /** + * Helper function to get the list of tables affected by a given query + * used to track dirty tables that received a write with the current request + */ + private function getQueriedTables(string $sql): array { + $re = '/(\*PREFIX\*\w+)/mi'; + preg_match_all($re, $sql, $matches); + return array_map([$this, 'replaceTablePrefix'], $matches[0] ?? []); + } + + /** + * @throws Exception + */ + public function executeUpdate(string $sql, array $params = [], array $types = []): int { + return $this->executeStatement($sql, $params, $types); + } + + /** + * Executes an SQL INSERT/UPDATE/DELETE query with the given parameters + * and returns the number of affected rows. + * + * This method supports PDO binding types as well as DBAL mapping types. + * + * @param string $sql The SQL query. + * @param array $params The query parameters. + * @param array $types The parameter types. + * + * @return int The number of affected rows. + * + * @throws \Doctrine\DBAL\Exception + */ + public function executeStatement($sql, array $params = [], array $types = []): int { + $tables = $this->getQueriedTables($sql); + foreach ($tables as $table) { + $this->tableDirtyWrites[$table] = $this->clock->now()->getTimestamp(); + } + $sql = $this->finishQuery($sql); + $this->queriesExecuted++; + $this->logQueryToFile($sql, $params); + try { + return (int)parent::executeStatement($sql, $params, $types); + } catch (\Exception $e) { + $this->logDatabaseException($e); + throw $e; + } + } + + protected function logQueryToFile(string $sql, array $params): void { + $logFile = $this->systemConfig->getValue('query_log_file'); + if ($logFile !== '' && is_writable(dirname($logFile)) && (!file_exists($logFile) || is_writable($logFile))) { + $prefix = ''; + if ($this->systemConfig->getValue('query_log_file_requestid') === 'yes') { + $prefix .= Server::get(IRequestId::class)->getId() . "\t"; + } + + $postfix = ''; + if ($this->systemConfig->getValue('query_log_file_parameters') === 'yes') { + $postfix .= '; ' . json_encode($params); + } + + if ($this->systemConfig->getValue('query_log_file_backtrace') === 'yes') { + $trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); + array_pop($trace); + $postfix .= '; ' . json_encode($trace); + } + + // FIXME: Improve to log the actual target db host + $isPrimary = $this->connections['primary'] === $this->_conn; + $prefix .= ' ' . ($isPrimary === true ? 'primary' : 'replica') . ' '; + $prefix .= ' ' . $this->getTransactionNestingLevel() . ' '; + + file_put_contents( + $this->systemConfig->getValue('query_log_file', ''), + $prefix . $sql . $postfix . "\n", + FILE_APPEND + ); + } + } + + /** + * Returns the ID of the last inserted row, or the last value from a sequence object, + * depending on the underlying driver. + * + * Note: This method may not return a meaningful or consistent result across different drivers, + * because the underlying database may not even support the notion of AUTO_INCREMENT/IDENTITY + * columns or sequences. + * + * @param string $seqName Name of the sequence object from which the ID should be returned. + * + * @return int the last inserted ID. + * @throws Exception + */ + public function lastInsertId($name = null): int { + if ($name) { + $name = $this->replaceTablePrefix($name); + } + return $this->adapter->lastInsertId($name); + } + + /** + * @internal + * @throws Exception + */ + public function realLastInsertId($seqName = null) { + return parent::lastInsertId($seqName); + } + + /** + * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance + * it is needed that there is also a unique constraint on the values. Then this method will + * catch the exception and return 0. + * + * @param string $table The table name (will replace *PREFIX* with the actual prefix) + * @param array $input data that should be inserted into the table (column name => value) + * @param array|null $compare List of values that should be checked for "if not exists" + * If this is null or an empty array, all keys of $input will be compared + * Please note: text fields (clob) must not be used in the compare array + * @return int number of inserted rows + * @throws \Doctrine\DBAL\Exception + * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371 + */ + public function insertIfNotExist($table, $input, ?array $compare = null) { + try { + return $this->adapter->insertIfNotExist($table, $input, $compare); + } catch (\Exception $e) { + $this->logDatabaseException($e); + throw $e; + } + } + + public function insertIgnoreConflict(string $table, array $values) : int { + try { + return $this->adapter->insertIgnoreConflict($table, $values); + } catch (\Exception $e) { + $this->logDatabaseException($e); + throw $e; + } + } + + private function getType($value) { + if (is_bool($value)) { + return IQueryBuilder::PARAM_BOOL; + } elseif (is_int($value)) { + return IQueryBuilder::PARAM_INT; + } else { + return IQueryBuilder::PARAM_STR; + } + } + + /** + * Insert or update a row value + * + * @param string $table + * @param array $keys (column name => value) + * @param array $values (column name => value) + * @param array $updatePreconditionValues ensure values match preconditions (column name => value) + * @return int number of new rows + * @throws \OCP\DB\Exception + * @throws PreConditionNotMetException + */ + public function setValues(string $table, array $keys, array $values, array $updatePreconditionValues = []): int { + try { + $insertQb = $this->getQueryBuilder(); + $insertQb->insert($table) + ->values( + array_map(function ($value) use ($insertQb) { + return $insertQb->createNamedParameter($value, $this->getType($value)); + }, array_merge($keys, $values)) + ); + return $insertQb->executeStatement(); + } catch (\OCP\DB\Exception $e) { + if (!in_array($e->getReason(), [ + \OCP\DB\Exception::REASON_CONSTRAINT_VIOLATION, + \OCP\DB\Exception::REASON_UNIQUE_CONSTRAINT_VIOLATION, + ]) + ) { + throw $e; + } + + // value already exists, try update + $updateQb = $this->getQueryBuilder(); + $updateQb->update($table); + foreach ($values as $name => $value) { + $updateQb->set($name, $updateQb->createNamedParameter($value, $this->getType($value))); + } + $where = []; + $whereValues = array_merge($keys, $updatePreconditionValues); + foreach ($whereValues as $name => $value) { + if ($value === '') { + $where[] = $updateQb->expr()->emptyString( + $name + ); + } else { + $where[] = $updateQb->expr()->eq( + $name, + $updateQb->createNamedParameter($value, $this->getType($value)), + $this->getType($value) + ); + } + } + $updateQb->where($updateQb->expr()->andX(...$where)); + $affected = $updateQb->executeStatement(); + + if ($affected === 0 && !empty($updatePreconditionValues)) { + throw new PreConditionNotMetException(); + } + + return 0; + } + } + + /** + * Create an exclusive read+write lock on a table + * + * @param string $tableName + * + * @throws \BadMethodCallException When trying to acquire a second lock + * @throws Exception + * @since 9.1.0 + */ + public function lockTable($tableName) { + if ($this->lockedTable !== null) { + throw new \BadMethodCallException('Can not lock a new table until the previous lock is released.'); + } + + $tableName = $this->tablePrefix . $tableName; + $this->lockedTable = $tableName; + $this->adapter->lockTable($tableName); + } + + /** + * Release a previous acquired lock again + * + * @throws Exception + * @since 9.1.0 + */ + public function unlockTable() { + $this->adapter->unlockTable(); + $this->lockedTable = null; + } + + /** + * returns the error code and message as a string for logging + * works with DoctrineException + * @return string + */ + public function getError() { + $msg = $this->errorCode() . ': '; + $errorInfo = $this->errorInfo(); + if (!empty($errorInfo)) { + $msg .= 'SQLSTATE = ' . $errorInfo[0] . ', '; + $msg .= 'Driver Code = ' . $errorInfo[1] . ', '; + $msg .= 'Driver Message = ' . $errorInfo[2]; + } + return $msg; + } + + public function errorCode() { + return -1; + } + + public function errorInfo() { + return []; + } + + /** + * Drop a table from the database if it exists + * + * @param string $table table name without the prefix + * + * @throws Exception + */ + public function dropTable($table) { + $table = $this->tablePrefix . trim($table); + $schema = $this->createSchemaManager(); + if ($schema->tablesExist([$table])) { + $schema->dropTable($table); + } + } + + /** + * Truncate a table data if it exists + * + * @param string $table table name without the prefix + * @param bool $cascade whether to truncate cascading + * + * @throws Exception + */ + public function truncateTable(string $table, bool $cascade) { + $this->executeStatement($this->getDatabasePlatform() + ->getTruncateTableSQL($this->tablePrefix . trim($table), $cascade)); + } + + /** + * Check if a table exists + * + * @param string $table table name without the prefix + * + * @return bool + * @throws Exception + */ + public function tableExists($table) { + $table = $this->tablePrefix . trim($table); + $schema = $this->createSchemaManager(); + return $schema->tablesExist([$table]); + } + + protected function finishQuery(string $statement): string { + $statement = $this->replaceTablePrefix($statement); + $statement = $this->adapter->fixupStatement($statement); + if ($this->logRequestId) { + return $statement . ' /* reqid: ' . $this->requestId . ' */'; + } else { + return $statement; + } + } + + // internal use + /** + * @param string $statement + * @return string + */ + protected function replaceTablePrefix($statement) { + return str_replace('*PREFIX*', $this->tablePrefix, $statement); + } + + /** + * Check if a transaction is active + * + * @return bool + * @since 8.2.0 + */ + public function inTransaction() { + return $this->getTransactionNestingLevel() > 0; + } + + /** + * Escape a parameter to be used in a LIKE query + * + * @param string $param + * @return string + */ + public function escapeLikeParameter($param) { + return addcslashes($param, '\\_%'); + } + + /** + * Check whether or not the current database support 4byte wide unicode + * + * @return bool + * @since 11.0.0 + */ + public function supports4ByteText() { + if (!$this->getDatabasePlatform() instanceof MySQLPlatform) { + return true; + } + return $this->getParams()['charset'] === 'utf8mb4'; + } + + + /** + * Create the schema of the connected database + * + * @return Schema + * @throws Exception + */ + public function createSchema() { + $migrator = $this->getMigrator(); + return $migrator->createSchema(); + } + + /** + * Migrate the database to the given schema + * + * @param Schema $toSchema + * @param bool $dryRun If true, will return the sql queries instead of running them. + * + * @throws Exception + * + * @return string|null Returns a string only if $dryRun is true. + */ + public function migrateToSchema(Schema $toSchema, bool $dryRun = false) { + $migrator = $this->getMigrator(); + + if ($dryRun) { + return $migrator->generateChangeScript($toSchema); + } else { + $migrator->migrate($toSchema); + foreach ($this->getShardConnections() as $shardConnection) { + $shardConnection->migrateToSchema($toSchema); + } + } + } + + private function getMigrator() { + // TODO properly inject those dependencies + $random = \OC::$server->get(ISecureRandom::class); + $platform = $this->getDatabasePlatform(); + $config = \OC::$server->getConfig(); + $dispatcher = Server::get(\OCP\EventDispatcher\IEventDispatcher::class); + if ($platform instanceof SqlitePlatform) { + return new SQLiteMigrator($this, $config, $dispatcher); + } elseif ($platform instanceof OraclePlatform) { + return new OracleMigrator($this, $config, $dispatcher); + } else { + return new Migrator($this, $config, $dispatcher); + } + } + + public function beginTransaction() { + if (!$this->inTransaction()) { + $this->transactionBacktrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); + $this->transactionActiveSince = microtime(true); + } + return parent::beginTransaction(); + } + + public function commit() { + $result = parent::commit(); + if ($this->getTransactionNestingLevel() === 0) { + $timeTook = microtime(true) - $this->transactionActiveSince; + $this->transactionBacktrace = null; + $this->transactionActiveSince = null; + if ($timeTook > 1) { + $logLevel = match (true) { + $timeTook > 20 * 60 => ILogger::ERROR, + $timeTook > 5 * 60 => ILogger::WARN, + $timeTook > 10 => ILogger::INFO, + default => ILogger::DEBUG, + }; + $this->logger->log( + $logLevel, + 'Transaction took ' . $timeTook . 's', + [ + 'exception' => new \Exception('Transaction took ' . $timeTook . 's'), + 'timeSpent' => $timeTook, + ] + ); + } + } + return $result; + } + + public function rollBack() { + $result = parent::rollBack(); + if ($this->getTransactionNestingLevel() === 0) { + $timeTook = microtime(true) - $this->transactionActiveSince; + $this->transactionBacktrace = null; + $this->transactionActiveSince = null; + if ($timeTook > 1) { + $logLevel = match (true) { + $timeTook > 20 * 60 => ILogger::ERROR, + $timeTook > 5 * 60 => ILogger::WARN, + $timeTook > 10 => ILogger::INFO, + default => ILogger::DEBUG, + }; + $this->logger->log( + $logLevel, + 'Transaction rollback took longer than 1s: ' . $timeTook, + [ + 'exception' => new \Exception('Long running transaction rollback'), + 'timeSpent' => $timeTook, + ] + ); + } + } + return $result; + } + + private function reconnectIfNeeded(): void { + if ( + !isset($this->lastConnectionCheck[$this->getConnectionName()]) + || time() <= $this->lastConnectionCheck[$this->getConnectionName()] + 30 + || $this->isTransactionActive() + ) { + return; + } + + try { + $this->_conn->query($this->getDriver()->getDatabasePlatform()->getDummySelectSQL()); + $this->lastConnectionCheck[$this->getConnectionName()] = time(); + } catch (ConnectionLost|\Exception $e) { + $this->logger->warning('Exception during connectivity check, closing and reconnecting', ['exception' => $e]); + $this->close(); + } + } + + private function getConnectionName(): string { + return $this->isConnectedToPrimary() ? 'primary' : 'replica'; + } + + /** + * @return IDBConnection::PLATFORM_MYSQL|IDBConnection::PLATFORM_ORACLE|IDBConnection::PLATFORM_POSTGRES|IDBConnection::PLATFORM_SQLITE|IDBConnection::PLATFORM_MARIADB + */ + public function getDatabaseProvider(bool $strict = false): string { + $platform = $this->getDatabasePlatform(); + if ($strict && $platform instanceof MariaDBPlatform) { + return IDBConnection::PLATFORM_MARIADB; + } elseif ($platform instanceof MySQLPlatform) { + return IDBConnection::PLATFORM_MYSQL; + } elseif ($platform instanceof OraclePlatform) { + return IDBConnection::PLATFORM_ORACLE; + } elseif ($platform instanceof PostgreSQLPlatform) { + return IDBConnection::PLATFORM_POSTGRES; + } elseif ($platform instanceof SqlitePlatform) { + return IDBConnection::PLATFORM_SQLITE; + } else { + throw new \Exception('Database ' . $platform::class . ' not supported'); + } + } + + /** + * @internal Should only be used inside the QueryBuilder, ExpressionBuilder and FunctionBuilder + * All apps and API code should not need this and instead use provided functionality from the above. + */ + public function getServerVersion(): string { + /** @var ServerInfoAwareConnection $this->_conn */ + return $this->_conn->getServerVersion(); + } + + /** + * Log a database exception if enabled + * + * @param \Exception $exception + * @return void + */ + public function logDatabaseException(\Exception $exception): void { + if ($this->logDbException) { + if ($exception instanceof Exception\UniqueConstraintViolationException) { + $this->logger->info($exception->getMessage(), ['exception' => $exception, 'transaction' => $this->transactionBacktrace]); + } else { + $this->logger->error($exception->getMessage(), ['exception' => $exception, 'transaction' => $this->transactionBacktrace]); + } + } + } + + public function getShardDefinition(string $name): ?ShardDefinition { + return $this->shards[$name] ?? null; + } + + public function getCrossShardMoveHelper(): CrossShardMoveHelper { + return new CrossShardMoveHelper($this->shardConnectionManager); + } +} diff --git a/lib/private/DB/ConnectionAdapter.php b/lib/private/DB/ConnectionAdapter.php new file mode 100644 index 00000000000..d9ccb3c54f2 --- /dev/null +++ b/lib/private/DB/ConnectionAdapter.php @@ -0,0 +1,265 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2020 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Platforms\AbstractPlatform; +use Doctrine\DBAL\Schema\Schema; +use OC\DB\Exceptions\DbalException; +use OC\DB\QueryBuilder\Sharded\CrossShardMoveHelper; +use OC\DB\QueryBuilder\Sharded\ShardDefinition; +use OCP\DB\IPreparedStatement; +use OCP\DB\IResult; +use OCP\DB\QueryBuilder\IQueryBuilder; +use OCP\IDBConnection; + +/** + * Adapts the public API to our internal DBAL connection wrapper + */ +class ConnectionAdapter implements IDBConnection { + /** @var Connection */ + private $inner; + + public function __construct(Connection $inner) { + $this->inner = $inner; + } + + public function getQueryBuilder(): IQueryBuilder { + return $this->inner->getQueryBuilder(); + } + + public function prepare($sql, $limit = null, $offset = null): IPreparedStatement { + try { + return new PreparedStatement( + $this->inner->prepare($sql, $limit, $offset) + ); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function executeQuery(string $sql, array $params = [], $types = []): IResult { + try { + return new ResultAdapter( + $this->inner->executeQuery($sql, $params, $types) + ); + } catch (Exception $e) { + throw DbalException::wrap($e, '', $sql); + } + } + + public function executeUpdate(string $sql, array $params = [], array $types = []): int { + try { + return $this->inner->executeUpdate($sql, $params, $types); + } catch (Exception $e) { + throw DbalException::wrap($e, '', $sql); + } + } + + public function executeStatement($sql, array $params = [], array $types = []): int { + try { + return $this->inner->executeStatement($sql, $params, $types); + } catch (Exception $e) { + throw DbalException::wrap($e, '', $sql); + } + } + + public function lastInsertId(string $table): int { + try { + return $this->inner->lastInsertId($table); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function insertIfNotExist(string $table, array $input, ?array $compare = null) { + try { + return $this->inner->insertIfNotExist($table, $input, $compare); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function insertIgnoreConflict(string $table, array $values): int { + try { + return $this->inner->insertIgnoreConflict($table, $values); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function setValues($table, array $keys, array $values, array $updatePreconditionValues = []): int { + try { + return $this->inner->setValues($table, $keys, $values, $updatePreconditionValues); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function lockTable($tableName): void { + try { + $this->inner->lockTable($tableName); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function unlockTable(): void { + try { + $this->inner->unlockTable(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function beginTransaction(): void { + try { + $this->inner->beginTransaction(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function inTransaction(): bool { + return $this->inner->inTransaction(); + } + + public function commit(): void { + try { + $this->inner->commit(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function rollBack(): void { + try { + $this->inner->rollBack(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function getError(): string { + return $this->inner->getError(); + } + + public function errorCode() { + return $this->inner->errorCode(); + } + + public function errorInfo() { + return $this->inner->errorInfo(); + } + + public function connect(): bool { + try { + return $this->inner->connect(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function close(): void { + $this->inner->close(); + } + + public function quote($input, $type = IQueryBuilder::PARAM_STR) { + return $this->inner->quote($input, $type); + } + + /** + * @todo we are leaking a 3rdparty type here + */ + public function getDatabasePlatform(): AbstractPlatform { + return $this->inner->getDatabasePlatform(); + } + + public function dropTable(string $table): void { + try { + $this->inner->dropTable($table); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function truncateTable(string $table, bool $cascade): void { + try { + $this->inner->truncateTable($table, $cascade); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function tableExists(string $table): bool { + try { + return $this->inner->tableExists($table); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function escapeLikeParameter(string $param): string { + return $this->inner->escapeLikeParameter($param); + } + + public function supports4ByteText(): bool { + return $this->inner->supports4ByteText(); + } + + /** + * @todo leaks a 3rdparty type + */ + public function createSchema(): Schema { + try { + return $this->inner->createSchema(); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function migrateToSchema(Schema $toSchema): void { + try { + $this->inner->migrateToSchema($toSchema); + } catch (Exception $e) { + throw DbalException::wrap($e); + } + } + + public function getInner(): Connection { + return $this->inner; + } + + /** + * @return self::PLATFORM_MYSQL|self::PLATFORM_ORACLE|self::PLATFORM_POSTGRES|self::PLATFORM_SQLITE|self::PLATFORM_MARIADB + */ + public function getDatabaseProvider(bool $strict = false): string { + return $this->inner->getDatabaseProvider($strict); + } + + /** + * @internal Should only be used inside the QueryBuilder, ExpressionBuilder and FunctionBuilder + * All apps and API code should not need this and instead use provided functionality from the above. + */ + public function getServerVersion(): string { + return $this->inner->getServerVersion(); + } + + public function logDatabaseException(\Exception $exception) { + $this->inner->logDatabaseException($exception); + } + + public function getShardDefinition(string $name): ?ShardDefinition { + return $this->inner->getShardDefinition($name); + } + + public function getCrossShardMoveHelper(): CrossShardMoveHelper { + return $this->inner->getCrossShardMoveHelper(); + } +} diff --git a/lib/private/DB/ConnectionFactory.php b/lib/private/DB/ConnectionFactory.php new file mode 100644 index 00000000000..d9b80b81992 --- /dev/null +++ b/lib/private/DB/ConnectionFactory.php @@ -0,0 +1,276 @@ +<?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; + +use Doctrine\Common\EventManager; +use Doctrine\DBAL\Configuration; +use Doctrine\DBAL\DriverManager; +use Doctrine\DBAL\Event\Listeners\OracleSessionInit; +use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler; +use OC\DB\QueryBuilder\Sharded\ShardConnectionManager; +use OC\SystemConfig; +use OCP\ICacheFactory; +use OCP\Server; + +/** + * Takes care of creating and configuring Doctrine connections. + */ +class ConnectionFactory { + /** @var string default database name */ + public const DEFAULT_DBNAME = 'owncloud'; + + /** @var string default database table prefix */ + public const DEFAULT_DBTABLEPREFIX = 'oc_'; + + /** + * @var array + * + * Array mapping DBMS type to default connection parameters passed to + * \Doctrine\DBAL\DriverManager::getConnection(). + */ + protected $defaultConnectionParams = [ + 'mysql' => [ + 'adapter' => AdapterMySQL::class, + 'charset' => 'UTF8', + 'driver' => 'pdo_mysql', + 'wrapperClass' => Connection::class, + ], + 'oci' => [ + 'adapter' => AdapterOCI8::class, + 'charset' => 'AL32UTF8', + 'driver' => 'oci8', + 'wrapperClass' => OracleConnection::class, + ], + 'pgsql' => [ + 'adapter' => AdapterPgSql::class, + 'driver' => 'pdo_pgsql', + 'wrapperClass' => Connection::class, + ], + 'sqlite3' => [ + 'adapter' => AdapterSqlite::class, + 'driver' => 'pdo_sqlite', + 'wrapperClass' => Connection::class, + ], + ]; + + private ShardConnectionManager $shardConnectionManager; + private ICacheFactory $cacheFactory; + + public function __construct( + private SystemConfig $config, + ?ICacheFactory $cacheFactory = null, + ) { + if ($this->config->getValue('mysql.utf8mb4', false)) { + $this->defaultConnectionParams['mysql']['charset'] = 'utf8mb4'; + } + $collationOverride = $this->config->getValue('mysql.collation', null); + if ($collationOverride) { + $this->defaultConnectionParams['mysql']['collation'] = $collationOverride; + } + $this->shardConnectionManager = new ShardConnectionManager($this->config, $this); + $this->cacheFactory = $cacheFactory ?? Server::get(ICacheFactory::class); + } + + /** + * @brief Get default connection parameters for a given DBMS. + * @param string $type DBMS type + * @throws \InvalidArgumentException If $type is invalid + * @return array Default connection parameters. + */ + public function getDefaultConnectionParams($type) { + $normalizedType = $this->normalizeType($type); + if (!isset($this->defaultConnectionParams[$normalizedType])) { + throw new \InvalidArgumentException("Unsupported type: $type"); + } + $result = $this->defaultConnectionParams[$normalizedType]; + // \PDO::MYSQL_ATTR_FOUND_ROWS may not be defined, e.g. when the MySQL + // driver is missing. In this case, we won't be able to connect anyway. + if ($normalizedType === 'mysql' && defined('\PDO::MYSQL_ATTR_FOUND_ROWS')) { + $result['driverOptions'] = [ + \PDO::MYSQL_ATTR_FOUND_ROWS => true, + ]; + } + return $result; + } + + /** + * @brief Get default connection parameters for a given DBMS. + * @param string $type DBMS type + * @param array $additionalConnectionParams Additional connection parameters + * @return \OC\DB\Connection + */ + public function getConnection(string $type, array $additionalConnectionParams): Connection { + $normalizedType = $this->normalizeType($type); + $eventManager = new EventManager(); + $eventManager->addEventSubscriber(new SetTransactionIsolationLevel()); + $connectionParams = $this->createConnectionParams('', $additionalConnectionParams, $type); + switch ($normalizedType) { + case 'pgsql': + // pg_connect used by Doctrine DBAL does not support URI notation (enclosed in brackets) + $matches = []; + if (preg_match('/^\[([^\]]+)\]$/', $connectionParams['host'], $matches)) { + // Host variable carries a port or socket. + $connectionParams['host'] = $matches[1]; + } + break; + + case 'oci': + $eventManager->addEventSubscriber(new OracleSessionInit); + $connectionParams = $this->forceConnectionStringOracle($connectionParams); + $connectionParams['primary'] = $this->forceConnectionStringOracle($connectionParams['primary']); + $connectionParams['replica'] = array_map([$this, 'forceConnectionStringOracle'], $connectionParams['replica']); + break; + + case 'sqlite3': + $journalMode = $connectionParams['sqlite.journal_mode']; + $connectionParams['platform'] = new OCSqlitePlatform(); + $eventManager->addEventSubscriber(new SQLiteSessionInit(true, $journalMode)); + break; + } + /** @var Connection $connection */ + $connection = DriverManager::getConnection( + $connectionParams, + new Configuration(), + $eventManager + ); + return $connection; + } + + /** + * @brief Normalize DBMS type + * @param string $type DBMS type + * @return string Normalized DBMS type + */ + public function normalizeType($type) { + return $type === 'sqlite' ? 'sqlite3' : $type; + } + + /** + * Checks whether the specified DBMS type is valid. + * + * @param string $type + * @return bool + */ + public function isValidType($type) { + $normalizedType = $this->normalizeType($type); + return isset($this->defaultConnectionParams[$normalizedType]); + } + + /** + * Create the connection parameters for the config + */ + public function createConnectionParams(string $configPrefix = '', array $additionalConnectionParams = [], ?string $type = null) { + // use provided type or if null use type from config + $type = $type ?? $this->config->getValue('dbtype', 'sqlite'); + + $connectionParams = array_merge($this->getDefaultConnectionParams($type), [ + 'user' => $this->config->getValue($configPrefix . 'dbuser', $this->config->getValue('dbuser', '')), + 'password' => $this->config->getValue($configPrefix . 'dbpassword', $this->config->getValue('dbpassword', '')), + ]); + $name = $this->config->getValue($configPrefix . 'dbname', $this->config->getValue('dbname', self::DEFAULT_DBNAME)); + + if ($this->normalizeType($type) === 'sqlite3') { + $dataDir = $this->config->getValue('datadirectory', \OC::$SERVERROOT . '/data'); + $connectionParams['path'] = $dataDir . '/' . $name . '.db'; + } else { + $host = $this->config->getValue($configPrefix . 'dbhost', $this->config->getValue('dbhost', '')); + $connectionParams = array_merge($connectionParams, $this->splitHostFromPortAndSocket($host)); + $connectionParams['dbname'] = $name; + } + + $connectionParams['tablePrefix'] = $this->config->getValue('dbtableprefix', self::DEFAULT_DBTABLEPREFIX); + $connectionParams['sqlite.journal_mode'] = $this->config->getValue('sqlite.journal_mode', 'WAL'); + + //additional driver options, eg. for mysql ssl + $driverOptions = $this->config->getValue($configPrefix . 'dbdriveroptions', $this->config->getValue('dbdriveroptions', null)); + if ($driverOptions) { + $connectionParams['driverOptions'] = $driverOptions; + } + + // set default table creation options + $connectionParams['defaultTableOptions'] = [ + 'collate' => 'utf8_bin', + 'tablePrefix' => $connectionParams['tablePrefix'] + ]; + + if ($type === 'mysql' && $this->config->getValue('mysql.utf8mb4', false)) { + $connectionParams['defaultTableOptions'] = [ + 'collate' => 'utf8mb4_bin', + 'charset' => 'utf8mb4', + 'tablePrefix' => $connectionParams['tablePrefix'] + ]; + } + + if ($this->config->getValue('dbpersistent', false)) { + $connectionParams['persistent'] = true; + } + + $connectionParams['sharding'] = $this->config->getValue('dbsharding', []); + if (!empty($connectionParams['sharding'])) { + $connectionParams['shard_connection_manager'] = $this->shardConnectionManager; + $connectionParams['auto_increment_handler'] = new AutoIncrementHandler( + $this->cacheFactory, + $this->shardConnectionManager, + ); + } else { + // just in case only the presence could lead to funny behaviour + unset($connectionParams['sharding']); + } + + $connectionParams = array_merge($connectionParams, $additionalConnectionParams); + + $replica = $this->config->getValue($configPrefix . 'dbreplica', $this->config->getValue('dbreplica', [])) ?: [$connectionParams]; + return array_merge($connectionParams, [ + 'primary' => $connectionParams, + 'replica' => $replica, + ]); + } + + /** + * @param string $host + * @return array + */ + protected function splitHostFromPortAndSocket($host): array { + $params = [ + 'host' => $host, + ]; + + $matches = []; + if (preg_match('/^(.*):([^\]:]+)$/', $host, $matches)) { + // Host variable carries a port or socket. + $params['host'] = $matches[1]; + if (is_numeric($matches[2])) { + $params['port'] = (int)$matches[2]; + } else { + $params['unix_socket'] = $matches[2]; + } + } + + return $params; + } + + protected function forceConnectionStringOracle(array $connectionParams): array { + // the driverOptions are unused in dbal and need to be mapped to the parameters + if (isset($connectionParams['driverOptions'])) { + $connectionParams = array_merge($connectionParams, $connectionParams['driverOptions']); + } + $host = $connectionParams['host']; + $port = $connectionParams['port'] ?? null; + $dbName = $connectionParams['dbname']; + + // we set the connect string as dbname and unset the host to coerce doctrine into using it as connect string + if ($host === '') { + $connectionParams['dbname'] = $dbName; // use dbname as easy connect name + } else { + $connectionParams['dbname'] = '//' . $host . (!empty($port) ? ":{$port}" : '') . '/' . $dbName; + } + unset($connectionParams['host']); + + return $connectionParams; + } +} diff --git a/lib/private/DB/DbDataCollector.php b/lib/private/DB/DbDataCollector.php new file mode 100644 index 00000000000..e3c7cd35855 --- /dev/null +++ b/lib/private/DB/DbDataCollector.php @@ -0,0 +1,136 @@ +<?php + +declare(strict_types = 1); +/** + * SPDX-FileCopyrightText: 2022 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ + +namespace OC\DB; + +use Doctrine\DBAL\Types\ConversionException; +use Doctrine\DBAL\Types\Type; +use OC\AppFramework\Http\Request; +use OCP\AppFramework\Http\Response; + +class DbDataCollector extends \OCP\DataCollector\AbstractDataCollector { + protected ?BacktraceDebugStack $debugStack = null; + private Connection $connection; + + /** + * DbDataCollector constructor. + */ + public function __construct(Connection $connection) { + $this->connection = $connection; + } + + public function setDebugStack(BacktraceDebugStack $debugStack, $name = 'default'): void { + $this->debugStack = $debugStack; + } + + /** + * @inheritDoc + */ + public function collect(Request $request, Response $response, ?\Throwable $exception = null): void { + $queries = $this->sanitizeQueries($this->debugStack->queries); + + $this->data = [ + 'queries' => $queries, + ]; + } + + public function getName(): string { + return 'db'; + } + + public function getQueries(): array { + return $this->data['queries']; + } + + private function sanitizeQueries(array $queries): array { + foreach ($queries as $i => $query) { + $queries[$i] = $this->sanitizeQuery($query); + } + + return $queries; + } + + private function sanitizeQuery(array $query): array { + $query['explainable'] = true; + $query['runnable'] = true; + if ($query['params'] === null) { + $query['params'] = []; + } + if (!\is_array($query['params'])) { + $query['params'] = [$query['params']]; + } + if (!\is_array($query['types'])) { + $query['types'] = []; + } + foreach ($query['params'] as $j => $param) { + $e = null; + if (isset($query['types'][$j])) { + // Transform the param according to the type + $type = $query['types'][$j]; + if (\is_string($type)) { + $type = Type::getType($type); + } + if ($type instanceof Type) { + $query['types'][$j] = $type->getBindingType(); + try { + $param = $type->convertToDatabaseValue($param, $this->connection->getDatabasePlatform()); + } catch (\TypeError $e) { + } catch (ConversionException $e) { + } + } + } + + [$query['params'][$j], $explainable, $runnable] = $this->sanitizeParam($param, $e); + if (!$explainable) { + $query['explainable'] = false; + } + + if (!$runnable) { + $query['runnable'] = false; + } + } + + return $query; + } + + /** + * Sanitizes a param. + * + * The return value is an array with the sanitized value and a boolean + * indicating if the original value was kept (allowing to use the sanitized + * value to explain the query). + */ + private function sanitizeParam($var, ?\Throwable $error): array { + if (\is_object($var)) { + return [$o = new ObjectParameter($var, $error), false, $o->isStringable() && !$error]; + } + + if ($error) { + return ['⚠' . $error->getMessage(), false, false]; + } + + if (\is_array($var)) { + $a = []; + $explainable = $runnable = true; + foreach ($var as $k => $v) { + [$value, $e, $r] = $this->sanitizeParam($v, null); + $explainable = $explainable && $e; + $runnable = $runnable && $r; + $a[$k] = $value; + } + + return [$a, $explainable, $runnable]; + } + + if (\is_resource($var)) { + return [sprintf('/* Resource(%s) */', get_resource_type($var)), false, false]; + } + + return [$var, true, true]; + } +} diff --git a/lib/private/DB/Exceptions/DbalException.php b/lib/private/DB/Exceptions/DbalException.php new file mode 100644 index 00000000000..2ce6ddf80a6 --- /dev/null +++ b/lib/private/DB/Exceptions/DbalException.php @@ -0,0 +1,130 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2021 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB\Exceptions; + +use Doctrine\DBAL\ConnectionException; +use Doctrine\DBAL\Exception\ConstraintViolationException; +use Doctrine\DBAL\Exception\DatabaseObjectExistsException; +use Doctrine\DBAL\Exception\DatabaseObjectNotFoundException; +use Doctrine\DBAL\Exception\DeadlockException; +use Doctrine\DBAL\Exception\DriverException; +use Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException; +use Doctrine\DBAL\Exception\InvalidArgumentException; +use Doctrine\DBAL\Exception\InvalidFieldNameException; +use Doctrine\DBAL\Exception\LockWaitTimeoutException; +use Doctrine\DBAL\Exception\NonUniqueFieldNameException; +use Doctrine\DBAL\Exception\NotNullConstraintViolationException; +use Doctrine\DBAL\Exception\RetryableException; +use Doctrine\DBAL\Exception\ServerException; +use Doctrine\DBAL\Exception\SyntaxErrorException; +use Doctrine\DBAL\Exception\UniqueConstraintViolationException; +use OCP\DB\Exception; + +/** + * Wrapper around the raw dbal exception, so we can pass it to apps that catch + * our OCP db exception + * + * @psalm-immutable + */ +class DbalException extends Exception { + /** @var \Doctrine\DBAL\Exception */ + private $original; + public readonly ?string $query; + + /** + * @param \Doctrine\DBAL\Exception $original + * @param int $code + * @param string $message + */ + private function __construct(\Doctrine\DBAL\Exception $original, int $code, string $message, ?string $query = null) { + parent::__construct( + $message, + $code, + $original + ); + $this->original = $original; + $this->query = $query; + } + + public static function wrap(\Doctrine\DBAL\Exception $original, string $message = '', ?string $query = null): self { + return new self( + $original, + is_int($original->getCode()) ? $original->getCode() : 0, + empty($message) ? $original->getMessage() : $message, + $query, + ); + } + + public function isRetryable(): bool { + return $this->original instanceof RetryableException; + } + + public function getReason(): ?int { + /** + * Constraint errors + */ + if ($this->original instanceof ForeignKeyConstraintViolationException) { + return parent::REASON_FOREIGN_KEY_VIOLATION; + } + if ($this->original instanceof NotNullConstraintViolationException) { + return parent::REASON_NOT_NULL_CONSTRAINT_VIOLATION; + } + if ($this->original instanceof UniqueConstraintViolationException) { + return parent::REASON_UNIQUE_CONSTRAINT_VIOLATION; + } + // The base exception comes last + if ($this->original instanceof ConstraintViolationException) { + return parent::REASON_CONSTRAINT_VIOLATION; + } + + /** + * Other server errors + */ + if ($this->original instanceof LockWaitTimeoutException) { + return parent::REASON_LOCK_WAIT_TIMEOUT; + } + if ($this->original instanceof DatabaseObjectExistsException) { + return parent::REASON_DATABASE_OBJECT_EXISTS; + } + if ($this->original instanceof DatabaseObjectNotFoundException) { + return parent::REASON_DATABASE_OBJECT_NOT_FOUND; + } + if ($this->original instanceof DeadlockException) { + return parent::REASON_DEADLOCK; + } + if ($this->original instanceof InvalidFieldNameException) { + return parent::REASON_INVALID_FIELD_NAME; + } + if ($this->original instanceof NonUniqueFieldNameException) { + return parent::REASON_NON_UNIQUE_FIELD_NAME; + } + if ($this->original instanceof SyntaxErrorException) { + return parent::REASON_SYNTAX_ERROR; + } + // The base server exception class comes last + if ($this->original instanceof ServerException) { + return parent::REASON_SERVER; + } + + /** + * Generic errors + */ + if ($this->original instanceof ConnectionException) { + return parent::REASON_CONNECTION_LOST; + } + if ($this->original instanceof InvalidArgumentException) { + return parent::REASON_INVALID_ARGUMENT; + } + if ($this->original instanceof DriverException) { + return parent::REASON_DRIVER; + } + + return null; + } +} diff --git a/lib/private/DB/MigrationException.php b/lib/private/DB/MigrationException.php new file mode 100644 index 00000000000..5b50f8ed3a4 --- /dev/null +++ b/lib/private/DB/MigrationException.php @@ -0,0 +1,24 @@ +<?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; + +class MigrationException extends \Exception { + private $table; + + public function __construct($table, $message) { + $this->table = $table; + parent::__construct($message); + } + + /** + * @return string + */ + public function getTable() { + return $this->table; + } +} diff --git a/lib/private/DB/MigrationService.php b/lib/private/DB/MigrationService.php new file mode 100644 index 00000000000..40579c7a898 --- /dev/null +++ b/lib/private/DB/MigrationService.php @@ -0,0 +1,736 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2017 ownCloud GmbH + * SPDX-License-Identifier: AGPL-3.0-only + */ +namespace OC\DB; + +use Doctrine\DBAL\Schema\Index; +use Doctrine\DBAL\Schema\Schema; +use Doctrine\DBAL\Schema\SchemaException; +use Doctrine\DBAL\Schema\Sequence; +use Doctrine\DBAL\Schema\Table; +use OC\App\InfoParser; +use OC\IntegrityCheck\Helpers\AppLocator; +use OC\Migration\SimpleOutput; +use OCP\AppFramework\App; +use OCP\AppFramework\QueryException; +use OCP\DB\ISchemaWrapper; +use OCP\DB\Types; +use OCP\IDBConnection; +use OCP\Migration\IMigrationStep; +use OCP\Migration\IOutput; +use OCP\Server; +use Psr\Log\LoggerInterface; + +class MigrationService { + private bool $migrationTableCreated; + private array $migrations; + private string $migrationsPath; + private string $migrationsNamespace; + private IOutput $output; + private LoggerInterface $logger; + private Connection $connection; + private string $appName; + private bool $checkOracle; + + /** + * @throws \Exception + */ + public function __construct(string $appName, Connection $connection, ?IOutput $output = null, ?AppLocator $appLocator = null, ?LoggerInterface $logger = null) { + $this->appName = $appName; + $this->connection = $connection; + if ($logger === null) { + $this->logger = Server::get(LoggerInterface::class); + } else { + $this->logger = $logger; + } + if ($output === null) { + $this->output = new SimpleOutput($this->logger, $appName); + } else { + $this->output = $output; + } + + if ($appName === 'core') { + $this->migrationsPath = \OC::$SERVERROOT . '/core/Migrations'; + $this->migrationsNamespace = 'OC\\Core\\Migrations'; + $this->checkOracle = true; + } else { + if ($appLocator === null) { + $appLocator = new AppLocator(); + } + $appPath = $appLocator->getAppPath($appName); + $namespace = App::buildAppNamespace($appName); + $this->migrationsPath = "$appPath/lib/Migration"; + $this->migrationsNamespace = $namespace . '\\Migration'; + + $infoParser = new InfoParser(); + $info = $infoParser->parse($appPath . '/appinfo/info.xml'); + if (!isset($info['dependencies']['database'])) { + $this->checkOracle = true; + } else { + $this->checkOracle = false; + foreach ($info['dependencies']['database'] as $database) { + if (\is_string($database) && $database === 'oci') { + $this->checkOracle = true; + } elseif (\is_array($database) && isset($database['@value']) && $database['@value'] === 'oci') { + $this->checkOracle = true; + } + } + } + } + $this->migrationTableCreated = false; + } + + /** + * Returns the name of the app for which this migration is executed + */ + public function getApp(): string { + return $this->appName; + } + + /** + * @codeCoverageIgnore - this will implicitly tested on installation + */ + private function createMigrationTable(): bool { + if ($this->migrationTableCreated) { + return false; + } + + if ($this->connection->tableExists('migrations') && \OC::$server->getConfig()->getAppValue('core', 'vendor', '') !== 'owncloud') { + $this->migrationTableCreated = true; + return false; + } + + $schema = new SchemaWrapper($this->connection); + + /** + * We drop the table when it has different columns or the definition does not + * match. E.g. ownCloud uses a length of 177 for app and 14 for version. + */ + try { + $table = $schema->getTable('migrations'); + $columns = $table->getColumns(); + + if (count($columns) === 2) { + try { + $column = $table->getColumn('app'); + $schemaMismatch = $column->getLength() !== 255; + + if (!$schemaMismatch) { + $column = $table->getColumn('version'); + $schemaMismatch = $column->getLength() !== 255; + } + } catch (SchemaException $e) { + // One of the columns is missing + $schemaMismatch = true; + } + + if (!$schemaMismatch) { + // Table exists and schema matches: return back! + $this->migrationTableCreated = true; + return false; + } + } + + // Drop the table, when it didn't match our expectations. + $this->connection->dropTable('migrations'); + + // Recreate the schema after the table was dropped. + $schema = new SchemaWrapper($this->connection); + } catch (SchemaException $e) { + // Table not found, no need to panic, we will create it. + } + + $table = $schema->createTable('migrations'); + $table->addColumn('app', Types::STRING, ['length' => 255]); + $table->addColumn('version', Types::STRING, ['length' => 255]); + $table->setPrimaryKey(['app', 'version']); + + $this->connection->migrateToSchema($schema->getWrappedSchema()); + + $this->migrationTableCreated = true; + + return true; + } + + /** + * Returns all versions which have already been applied + * + * @return list<string> + * @codeCoverageIgnore - no need to test this + */ + public function getMigratedVersions() { + $this->createMigrationTable(); + $qb = $this->connection->getQueryBuilder(); + + $qb->select('version') + ->from('migrations') + ->where($qb->expr()->eq('app', $qb->createNamedParameter($this->getApp()))) + ->orderBy('version'); + + $result = $qb->executeQuery(); + $rows = $result->fetchAll(\PDO::FETCH_COLUMN); + $result->closeCursor(); + + usort($rows, $this->sortMigrations(...)); + + return $rows; + } + + /** + * Returns all versions which are available in the migration folder + * @return list<string> + */ + public function getAvailableVersions(): array { + $this->ensureMigrationsAreLoaded(); + $versions = array_map('strval', array_keys($this->migrations)); + usort($versions, $this->sortMigrations(...)); + return $versions; + } + + protected function sortMigrations(string $a, string $b): int { + preg_match('/(\d+)Date(\d+)/', basename($a), $matchA); + preg_match('/(\d+)Date(\d+)/', basename($b), $matchB); + if (!empty($matchA) && !empty($matchB)) { + $versionA = (int)$matchA[1]; + $versionB = (int)$matchB[1]; + if ($versionA !== $versionB) { + return ($versionA < $versionB) ? -1 : 1; + } + return strnatcmp($matchA[2], $matchB[2]); + } + return strnatcmp(basename($a), basename($b)); + } + + /** + * @return array<string, string> + */ + protected function findMigrations(): array { + $directory = realpath($this->migrationsPath); + if ($directory === false || !file_exists($directory) || !is_dir($directory)) { + return []; + } + + $iterator = new \RegexIterator( + new \RecursiveIteratorIterator( + new \RecursiveDirectoryIterator($directory, \FilesystemIterator::SKIP_DOTS), + \RecursiveIteratorIterator::LEAVES_ONLY + ), + '#^.+\\/Version[^\\/]{1,255}\\.php$#i', + \RegexIterator::GET_MATCH); + + $files = array_keys(iterator_to_array($iterator)); + usort($files, $this->sortMigrations(...)); + + $migrations = []; + + foreach ($files as $file) { + $className = basename($file, '.php'); + $version = (string)substr($className, 7); + if ($version === '0') { + throw new \InvalidArgumentException( + "Cannot load a migrations with the name '$version' because it is a reserved number" + ); + } + $migrations[$version] = sprintf('%s\\%s', $this->migrationsNamespace, $className); + } + + return $migrations; + } + + /** + * @param string $to + * @return string[] + */ + private function getMigrationsToExecute($to) { + $knownMigrations = $this->getMigratedVersions(); + $availableMigrations = $this->getAvailableVersions(); + + $toBeExecuted = []; + foreach ($availableMigrations as $v) { + if ($to !== 'latest' && ($this->sortMigrations($v, $to) > 0)) { + continue; + } + if ($this->shallBeExecuted($v, $knownMigrations)) { + $toBeExecuted[] = $v; + } + } + + return $toBeExecuted; + } + + /** + * @param string $m + * @param string[] $knownMigrations + * @return bool + */ + private function shallBeExecuted($m, $knownMigrations) { + if (in_array($m, $knownMigrations)) { + return false; + } + + return true; + } + + /** + * @param string $version + */ + private function markAsExecuted($version) { + $this->connection->insertIfNotExist('*PREFIX*migrations', [ + 'app' => $this->appName, + 'version' => $version + ]); + } + + /** + * Returns the name of the table which holds the already applied versions + * + * @return string + */ + public function getMigrationsTableName() { + return $this->connection->getPrefix() . 'migrations'; + } + + /** + * Returns the namespace of the version classes + * + * @return string + */ + public function getMigrationsNamespace() { + return $this->migrationsNamespace; + } + + /** + * Returns the directory which holds the versions + * + * @return string + */ + public function getMigrationsDirectory() { + return $this->migrationsPath; + } + + /** + * Return the explicit version for the aliases; current, next, prev, latest + * + * @return mixed|null|string + */ + public function getMigration(string $alias) { + switch ($alias) { + case 'current': + return $this->getCurrentVersion(); + case 'next': + return $this->getRelativeVersion($this->getCurrentVersion(), 1); + case 'prev': + return $this->getRelativeVersion($this->getCurrentVersion(), -1); + case 'latest': + $this->ensureMigrationsAreLoaded(); + + $migrations = $this->getAvailableVersions(); + return @end($migrations); + } + return '0'; + } + + private function getRelativeVersion(string $version, int $delta): ?string { + $this->ensureMigrationsAreLoaded(); + + $versions = $this->getAvailableVersions(); + array_unshift($versions, '0'); + /** @var int $offset */ + $offset = array_search($version, $versions, true); + if ($offset === false || !isset($versions[$offset + $delta])) { + // Unknown version or delta out of bounds. + return null; + } + + return (string)$versions[$offset + $delta]; + } + + private function getCurrentVersion(): string { + $m = $this->getMigratedVersions(); + if (count($m) === 0) { + return '0'; + } + $migrations = array_values($m); + return @end($migrations); + } + + /** + * @throws \InvalidArgumentException + */ + private function getClass(string $version): string { + $this->ensureMigrationsAreLoaded(); + + if (isset($this->migrations[$version])) { + return $this->migrations[$version]; + } + + throw new \InvalidArgumentException("Version $version is unknown."); + } + + /** + * Allows to set an IOutput implementation which is used for logging progress and messages + */ + public function setOutput(IOutput $output): void { + $this->output = $output; + } + + /** + * Applies all not yet applied versions up to $to + * @throws \InvalidArgumentException + */ + public function migrate(string $to = 'latest', bool $schemaOnly = false): void { + if ($schemaOnly) { + $this->output->debug('Migrating schema only'); + $this->migrateSchemaOnly($to); + return; + } + + // read known migrations + $toBeExecuted = $this->getMigrationsToExecute($to); + foreach ($toBeExecuted as $version) { + try { + $this->executeStep($version, $schemaOnly); + } catch (\Exception $e) { + // The exception itself does not contain the name of the migration, + // so we wrap it here, to make debugging easier. + throw new \Exception('Database error when running migration ' . $version . ' for app ' . $this->getApp() . PHP_EOL . $e->getMessage(), 0, $e); + } + } + } + + /** + * Applies all not yet applied versions up to $to + * @throws \InvalidArgumentException + */ + public function migrateSchemaOnly(string $to = 'latest'): void { + // read known migrations + $toBeExecuted = $this->getMigrationsToExecute($to); + + if (empty($toBeExecuted)) { + return; + } + + $toSchema = null; + foreach ($toBeExecuted as $version) { + $this->output->debug('- Reading ' . $version); + $instance = $this->createInstance($version); + + $toSchema = $instance->changeSchema($this->output, function () use ($toSchema): ISchemaWrapper { + return $toSchema ?: new SchemaWrapper($this->connection); + }, ['tablePrefix' => $this->connection->getPrefix()]) ?: $toSchema; + } + + if ($toSchema instanceof SchemaWrapper) { + $this->output->debug('- Checking target database schema'); + $targetSchema = $toSchema->getWrappedSchema(); + $this->ensureUniqueNamesConstraints($targetSchema, true); + if ($this->checkOracle) { + $beforeSchema = $this->connection->createSchema(); + $this->ensureOracleConstraints($beforeSchema, $targetSchema, strlen($this->connection->getPrefix())); + } + + $this->output->debug('- Migrate database schema'); + $this->connection->migrateToSchema($targetSchema); + $toSchema->performDropTableCalls(); + } + + $this->output->debug('- Mark migrations as executed'); + foreach ($toBeExecuted as $version) { + $this->markAsExecuted($version); + } + } + + /** + * Get the human readable descriptions for the migration steps to run + * + * @param string $to + * @return string[] [$name => $description] + */ + public function describeMigrationStep($to = 'latest') { + $toBeExecuted = $this->getMigrationsToExecute($to); + $description = []; + foreach ($toBeExecuted as $version) { + $migration = $this->createInstance($version); + if ($migration->name()) { + $description[$migration->name()] = $migration->description(); + } + } + return $description; + } + + /** + * @param string $version + * @return IMigrationStep + * @throws \InvalidArgumentException + */ + public function createInstance($version) { + $class = $this->getClass($version); + try { + $s = \OCP\Server::get($class); + + if (!$s instanceof IMigrationStep) { + throw new \InvalidArgumentException('Not a valid migration'); + } + } catch (QueryException $e) { + if (class_exists($class)) { + $s = new $class(); + } else { + throw new \InvalidArgumentException("Migration step '$class' is unknown"); + } + } + + return $s; + } + + /** + * Executes one explicit version + * + * @param string $version + * @param bool $schemaOnly + * @throws \InvalidArgumentException + */ + public function executeStep($version, $schemaOnly = false) { + $instance = $this->createInstance($version); + + if (!$schemaOnly) { + $instance->preSchemaChange($this->output, function (): ISchemaWrapper { + return new SchemaWrapper($this->connection); + }, ['tablePrefix' => $this->connection->getPrefix()]); + } + + $toSchema = $instance->changeSchema($this->output, function (): ISchemaWrapper { + return new SchemaWrapper($this->connection); + }, ['tablePrefix' => $this->connection->getPrefix()]); + + if ($toSchema instanceof SchemaWrapper) { + $targetSchema = $toSchema->getWrappedSchema(); + $this->ensureUniqueNamesConstraints($targetSchema, $schemaOnly); + if ($this->checkOracle) { + $sourceSchema = $this->connection->createSchema(); + $this->ensureOracleConstraints($sourceSchema, $targetSchema, strlen($this->connection->getPrefix())); + } + $this->connection->migrateToSchema($targetSchema); + $toSchema->performDropTableCalls(); + } + + if (!$schemaOnly) { + $instance->postSchemaChange($this->output, function (): ISchemaWrapper { + return new SchemaWrapper($this->connection); + }, ['tablePrefix' => $this->connection->getPrefix()]); + } + + $this->markAsExecuted($version); + } + + /** + * Naming constraints: + * - Tables names must be 30 chars or shorter (27 + oc_ prefix) + * - Column names must be 30 chars or shorter + * - Index names must be 30 chars or shorter + * - Sequence names must be 30 chars or shorter + * - Primary key names must be set or the table name 23 chars or shorter + * + * Data constraints: + * - Tables need a primary key (Not specific to Oracle, but required for performant clustering support) + * - Columns with "NotNull" can not have empty string as default value + * - Columns with "NotNull" can not have number 0 as default value + * - Columns with type "bool" (which is in fact integer of length 1) can not be "NotNull" as it can not store 0/false + * - Columns with type "string" can not be longer than 4.000 characters, use "text" instead + * + * @see https://github.com/nextcloud/documentation/blob/master/developer_manual/basics/storage/database.rst + * + * @param Schema $sourceSchema + * @param Schema $targetSchema + * @param int $prefixLength + * @throws \Doctrine\DBAL\Exception + */ + public function ensureOracleConstraints(Schema $sourceSchema, Schema $targetSchema, int $prefixLength) { + $sequences = $targetSchema->getSequences(); + + foreach ($targetSchema->getTables() as $table) { + try { + $sourceTable = $sourceSchema->getTable($table->getName()); + } catch (SchemaException $e) { + if (\strlen($table->getName()) - $prefixLength > 27) { + throw new \InvalidArgumentException('Table name "' . $table->getName() . '" is too long.'); + } + $sourceTable = null; + } + + foreach ($table->getColumns() as $thing) { + // If the table doesn't exist OR if the column doesn't exist in the table + if (!$sourceTable instanceof Table || !$sourceTable->hasColumn($thing->getName())) { + if (\strlen($thing->getName()) > 30) { + throw new \InvalidArgumentException('Column name "' . $table->getName() . '"."' . $thing->getName() . '" is too long.'); + } + + if ($thing->getNotnull() && $thing->getDefault() === '' + && $sourceTable instanceof Table && !$sourceTable->hasColumn($thing->getName())) { + throw new \InvalidArgumentException('Column "' . $table->getName() . '"."' . $thing->getName() . '" is NotNull, but has empty string or null as default.'); + } + + if ($thing->getNotnull() && $thing->getType()->getName() === Types::BOOLEAN) { + throw new \InvalidArgumentException('Column "' . $table->getName() . '"."' . $thing->getName() . '" is type Bool and also NotNull, so it can not store "false".'); + } + + $sourceColumn = null; + } else { + $sourceColumn = $sourceTable->getColumn($thing->getName()); + } + + // If the column was just created OR the length changed OR the type changed + // we will NOT detect invalid length if the column is not modified + if (($sourceColumn === null || $sourceColumn->getLength() !== $thing->getLength() || $sourceColumn->getType()->getName() !== Types::STRING) + && $thing->getLength() > 4000 && $thing->getType()->getName() === Types::STRING) { + throw new \InvalidArgumentException('Column "' . $table->getName() . '"."' . $thing->getName() . '" is type String, but exceeding the 4.000 length limit.'); + } + } + + foreach ($table->getIndexes() as $thing) { + if ((!$sourceTable instanceof Table || !$sourceTable->hasIndex($thing->getName())) && \strlen($thing->getName()) > 30) { + throw new \InvalidArgumentException('Index name "' . $table->getName() . '"."' . $thing->getName() . '" is too long.'); + } + } + + foreach ($table->getForeignKeys() as $thing) { + if ((!$sourceTable instanceof Table || !$sourceTable->hasForeignKey($thing->getName())) && \strlen($thing->getName()) > 30) { + throw new \InvalidArgumentException('Foreign key name "' . $table->getName() . '"."' . $thing->getName() . '" is too long.'); + } + } + + $primaryKey = $table->getPrimaryKey(); + if ($primaryKey instanceof Index && (!$sourceTable instanceof Table || !$sourceTable->hasPrimaryKey())) { + $indexName = strtolower($primaryKey->getName()); + $isUsingDefaultName = $indexName === 'primary'; + + if ($this->connection->getDatabaseProvider() === IDBConnection::PLATFORM_POSTGRES) { + $defaultName = $table->getName() . '_pkey'; + $isUsingDefaultName = strtolower($defaultName) === $indexName; + + if ($isUsingDefaultName) { + $sequenceName = $table->getName() . '_' . implode('_', $primaryKey->getColumns()) . '_seq'; + $sequences = array_filter($sequences, function (Sequence $sequence) use ($sequenceName) { + return $sequence->getName() !== $sequenceName; + }); + } + } elseif ($this->connection->getDatabaseProvider() === IDBConnection::PLATFORM_ORACLE) { + $defaultName = $table->getName() . '_seq'; + $isUsingDefaultName = strtolower($defaultName) === $indexName; + } + + if (!$isUsingDefaultName && \strlen($indexName) > 30) { + throw new \InvalidArgumentException('Primary index name on "' . $table->getName() . '" is too long.'); + } + if ($isUsingDefaultName && \strlen($table->getName()) - $prefixLength >= 23) { + throw new \InvalidArgumentException('Primary index name on "' . $table->getName() . '" is too long.'); + } + } elseif (!$primaryKey instanceof Index && !$sourceTable instanceof Table) { + /** @var LoggerInterface $logger */ + $logger = \OC::$server->get(LoggerInterface::class); + $logger->error('Table "' . $table->getName() . '" has no primary key and therefor will not behave sane in clustered setups. This will throw an exception and not be installable in a future version of Nextcloud.'); + // throw new \InvalidArgumentException('Table "' . $table->getName() . '" has no primary key and therefor will not behave sane in clustered setups.'); + } + } + + foreach ($sequences as $sequence) { + if (!$sourceSchema->hasSequence($sequence->getName()) && \strlen($sequence->getName()) > 30) { + throw new \InvalidArgumentException('Sequence name "' . $sequence->getName() . '" is too long.'); + } + } + } + + /** + * Ensure naming constraints + * + * Naming constraints: + * - Index, sequence and primary key names must be unique within a Postgres Schema + * + * Only on installation we want to break hard, so that all developers notice + * the bugs when installing the app on any database or CI, and can work on + * fixing their migrations before releasing a version incompatible with Postgres. + * + * In case of updates we might be running on production instances and the + * administrators being faced with the error would not know how to resolve it + * anyway. This can also happen with instances, that had the issue before the + * current update, so we don't want to make their life more complicated + * than needed. + * + * @param Schema $targetSchema + * @param bool $isInstalling + */ + public function ensureUniqueNamesConstraints(Schema $targetSchema, bool $isInstalling): void { + $constraintNames = []; + $sequences = $targetSchema->getSequences(); + + foreach ($targetSchema->getTables() as $table) { + foreach ($table->getIndexes() as $thing) { + $indexName = strtolower($thing->getName()); + if ($indexName === 'primary' || $thing->isPrimary()) { + continue; + } + + if (isset($constraintNames[$thing->getName()])) { + if ($isInstalling) { + throw new \InvalidArgumentException('Index name "' . $thing->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $this->logErrorOrWarning('Index name "' . $thing->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $constraintNames[$thing->getName()] = $table->getName(); + } + + foreach ($table->getForeignKeys() as $thing) { + if (isset($constraintNames[$thing->getName()])) { + if ($isInstalling) { + throw new \InvalidArgumentException('Foreign key name "' . $thing->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $this->logErrorOrWarning('Foreign key name "' . $thing->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $constraintNames[$thing->getName()] = $table->getName(); + } + + $primaryKey = $table->getPrimaryKey(); + if ($primaryKey instanceof Index) { + $indexName = strtolower($primaryKey->getName()); + if ($indexName === 'primary') { + continue; + } + + if (isset($constraintNames[$indexName])) { + if ($isInstalling) { + throw new \InvalidArgumentException('Primary index name "' . $indexName . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $this->logErrorOrWarning('Primary index name "' . $indexName . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $constraintNames[$indexName] = $table->getName(); + } + } + + foreach ($sequences as $sequence) { + if (isset($constraintNames[$sequence->getName()])) { + if ($isInstalling) { + throw new \InvalidArgumentException('Sequence name "' . $sequence->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $this->logErrorOrWarning('Sequence name "' . $sequence->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".'); + } + $constraintNames[$sequence->getName()] = 'sequence'; + } + } + + protected function logErrorOrWarning(string $log): void { + if ($this->output instanceof SimpleOutput) { + $this->output->warning($log); + } else { + $this->logger->error($log); + } + } + + private function ensureMigrationsAreLoaded() { + if (empty($this->migrations)) { + $this->migrations = $this->findMigrations(); + } + } +} diff --git a/lib/private/DB/Migrator.php b/lib/private/DB/Migrator.php new file mode 100644 index 00000000000..40f8ad9676a --- /dev/null +++ b/lib/private/DB/Migrator.php @@ -0,0 +1,173 @@ +<?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; + +use Doctrine\DBAL\Connection; +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Platforms\MySQLPlatform; +use Doctrine\DBAL\Schema\AbstractAsset; +use Doctrine\DBAL\Schema\Schema; +use Doctrine\DBAL\Schema\SchemaDiff; +use Doctrine\DBAL\Types\StringType; +use Doctrine\DBAL\Types\Type; +use OCP\EventDispatcher\IEventDispatcher; +use OCP\IConfig; +use function preg_match; + +class Migrator { + /** @var Connection */ + protected $connection; + + /** @var IConfig */ + protected $config; + + private ?IEventDispatcher $dispatcher; + + /** @var bool */ + private $noEmit = false; + + public function __construct(Connection $connection, + IConfig $config, + ?IEventDispatcher $dispatcher = null) { + $this->connection = $connection; + $this->config = $config; + $this->dispatcher = $dispatcher; + } + + /** + * @throws Exception + */ + public function migrate(Schema $targetSchema) { + $this->noEmit = true; + $this->applySchema($targetSchema); + } + + /** + * @return string + */ + public function generateChangeScript(Schema $targetSchema) { + $schemaDiff = $this->getDiff($targetSchema, $this->connection); + + $script = ''; + $sqls = $this->connection->getDatabasePlatform()->getAlterSchemaSQL($schemaDiff); + foreach ($sqls as $sql) { + $script .= $this->convertStatementToScript($sql); + } + + return $script; + } + + /** + * @throws Exception + */ + public function createSchema() { + $this->connection->getConfiguration()->setSchemaAssetsFilter(function ($asset) { + /** @var string|AbstractAsset $asset */ + $filterExpression = $this->getFilterExpression(); + if ($asset instanceof AbstractAsset) { + return preg_match($filterExpression, $asset->getName()) === 1; + } + return preg_match($filterExpression, $asset) === 1; + }); + return $this->connection->createSchemaManager()->introspectSchema(); + } + + /** + * @return SchemaDiff + */ + protected function getDiff(Schema $targetSchema, Connection $connection) { + // Adjust STRING columns with a length higher than 4000 to TEXT (clob) + // for consistency between the supported databases and + // old vs. new installations. + foreach ($targetSchema->getTables() as $table) { + foreach ($table->getColumns() as $column) { + if ($column->getType() instanceof StringType) { + if ($column->getLength() > 4000) { + $column->setType(Type::getType('text')); + $column->setLength(null); + } + } + } + } + + $this->connection->getConfiguration()->setSchemaAssetsFilter(function ($asset) { + /** @var string|AbstractAsset $asset */ + $filterExpression = $this->getFilterExpression(); + if ($asset instanceof AbstractAsset) { + return preg_match($filterExpression, $asset->getName()) === 1; + } + return preg_match($filterExpression, $asset) === 1; + }); + $sourceSchema = $connection->createSchemaManager()->introspectSchema(); + + // remove tables we don't know about + foreach ($sourceSchema->getTables() as $table) { + if (!$targetSchema->hasTable($table->getName())) { + $sourceSchema->dropTable($table->getName()); + } + } + // remove sequences we don't know about + foreach ($sourceSchema->getSequences() as $table) { + if (!$targetSchema->hasSequence($table->getName())) { + $sourceSchema->dropSequence($table->getName()); + } + } + + $comparator = $connection->createSchemaManager()->createComparator(); + return $comparator->compareSchemas($sourceSchema, $targetSchema); + } + + /** + * @throws Exception + */ + protected function applySchema(Schema $targetSchema, ?Connection $connection = null) { + if (is_null($connection)) { + $connection = $this->connection; + } + + $schemaDiff = $this->getDiff($targetSchema, $connection); + + if (!$connection->getDatabasePlatform() instanceof MySQLPlatform) { + $connection->beginTransaction(); + } + $sqls = $connection->getDatabasePlatform()->getAlterSchemaSQL($schemaDiff); + $step = 0; + foreach ($sqls as $sql) { + $this->emit($sql, $step++, count($sqls)); + $connection->executeStatement($sql); + } + if (!$connection->getDatabasePlatform() instanceof MySQLPlatform) { + $connection->commit(); + } + } + + /** + * @param $statement + * @return string + */ + protected function convertStatementToScript($statement) { + $script = $statement . ';'; + $script .= PHP_EOL; + $script .= PHP_EOL; + return $script; + } + + protected function getFilterExpression() { + return '/^' . preg_quote($this->config->getSystemValueString('dbtableprefix', 'oc_'), '/') . '/'; + } + + protected function emit(string $sql, int $step, int $max): void { + if ($this->noEmit) { + return; + } + if (is_null($this->dispatcher)) { + return; + } + $this->dispatcher->dispatchTyped(new MigratorExecuteSqlEvent($sql, $step, $max)); + } +} diff --git a/lib/private/DB/MigratorExecuteSqlEvent.php b/lib/private/DB/MigratorExecuteSqlEvent.php new file mode 100644 index 00000000000..340cd636300 --- /dev/null +++ b/lib/private/DB/MigratorExecuteSqlEvent.php @@ -0,0 +1,39 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2022 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ +namespace OC\DB; + +use OCP\EventDispatcher\Event; + +class MigratorExecuteSqlEvent extends Event { + private string $sql; + private int $current; + private int $max; + + public function __construct( + string $sql, + int $current, + int $max, + ) { + $this->sql = $sql; + $this->current = $current; + $this->max = $max; + } + + public function getSql(): string { + return $this->sql; + } + + public function getCurrentStep(): int { + return $this->current; + } + + public function getMaxStep(): int { + return $this->max; + } +} diff --git a/lib/private/DB/MissingColumnInformation.php b/lib/private/DB/MissingColumnInformation.php new file mode 100644 index 00000000000..5e8402d394e --- /dev/null +++ b/lib/private/DB/MissingColumnInformation.php @@ -0,0 +1,24 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2020 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +class MissingColumnInformation { + private array $listOfMissingColumns = []; + + public function addHintForMissingColumn(string $tableName, string $columnName): void { + $this->listOfMissingColumns[] = [ + 'tableName' => $tableName, + 'columnName' => $columnName, + ]; + } + + public function getListOfMissingColumns(): array { + return $this->listOfMissingColumns; + } +} diff --git a/lib/private/DB/MissingIndexInformation.php b/lib/private/DB/MissingIndexInformation.php new file mode 100644 index 00000000000..99a81782858 --- /dev/null +++ b/lib/private/DB/MissingIndexInformation.php @@ -0,0 +1,24 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2018 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +class MissingIndexInformation { + private array $listOfMissingIndices = []; + + public function addHintForMissingIndex(string $tableName, string $indexName): void { + $this->listOfMissingIndices[] = [ + 'tableName' => $tableName, + 'indexName' => $indexName + ]; + } + + public function getListOfMissingIndices(): array { + return $this->listOfMissingIndices; + } +} diff --git a/lib/private/DB/MissingPrimaryKeyInformation.php b/lib/private/DB/MissingPrimaryKeyInformation.php new file mode 100644 index 00000000000..355ce86423a --- /dev/null +++ b/lib/private/DB/MissingPrimaryKeyInformation.php @@ -0,0 +1,23 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2018 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +class MissingPrimaryKeyInformation { + private array $listOfMissingPrimaryKeys = []; + + public function addHintForMissingPrimaryKey(string $tableName): void { + $this->listOfMissingPrimaryKeys[] = [ + 'tableName' => $tableName, + ]; + } + + public function getListOfMissingPrimaryKeys(): array { + return $this->listOfMissingPrimaryKeys; + } +} diff --git a/lib/private/DB/MySqlTools.php b/lib/private/DB/MySqlTools.php new file mode 100644 index 00000000000..3413be43417 --- /dev/null +++ b/lib/private/DB/MySqlTools.php @@ -0,0 +1,52 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 ownCloud GmbH + * SPDX-License-Identifier: AGPL-3.0-only + */ +namespace OC\DB; + +use OCP\IDBConnection; + +/** + * Various MySQL specific helper functions. + */ +class MySqlTools { + /** + * @param IDBConnection $connection + * @return bool + */ + public function supports4ByteCharset(IDBConnection $connection) { + $variables = ['innodb_file_per_table' => 'ON']; + if (!$this->isMariaDBWithLargePrefix($connection)) { + $variables['innodb_file_format'] = 'Barracuda'; + $variables['innodb_large_prefix'] = 'ON'; + } + + foreach ($variables as $var => $val) { + $result = $connection->executeQuery("SHOW VARIABLES LIKE '$var'"); + $row = $result->fetch(); + $result->closeCursor(); + if ($row === false) { + return false; + } + if (strcasecmp($row['Value'], $val) !== 0) { + return false; + } + } + return true; + } + + protected function isMariaDBWithLargePrefix(IDBConnection $connection) { + $result = $connection->executeQuery('SELECT VERSION()'); + $row = strtolower($result->fetchColumn()); + $result->closeCursor(); + + if ($row === false) { + return false; + } + + return str_contains($row, 'maria') && version_compare($row, '10.3', '>=') + || !str_contains($row, 'maria') && version_compare($row, '8.0', '>='); + } +} diff --git a/lib/private/DB/OCSqlitePlatform.php b/lib/private/DB/OCSqlitePlatform.php new file mode 100644 index 00000000000..3e2b10e6e40 --- /dev/null +++ b/lib/private/DB/OCSqlitePlatform.php @@ -0,0 +1,11 @@ +<?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; + +class OCSqlitePlatform extends \Doctrine\DBAL\Platforms\SqlitePlatform { +} diff --git a/lib/private/DB/ObjectParameter.php b/lib/private/DB/ObjectParameter.php new file mode 100644 index 00000000000..1b013734c95 --- /dev/null +++ b/lib/private/DB/ObjectParameter.php @@ -0,0 +1,47 @@ +<?php + +declare(strict_types = 1); +/** + * This file is part of the Symfony package. + * + * SPDX-FileCopyrightText: 2022 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2022 Fabien Potencier <fabien@symfony.com> + * SPDX-License-Identifier: AGPL-3.0-or-later AND MIT + */ +namespace OC\DB; + +final class ObjectParameter { + private $object; + private $error; + private $stringable; + private $class; + + /** + * @param object $object + */ + public function __construct($object, ?\Throwable $error) { + $this->object = $object; + $this->error = $error; + $this->stringable = \is_callable([$object, '__toString']); + $this->class = \get_class($object); + } + + /** + * @return object + */ + public function getObject() { + return $this->object; + } + + public function getError(): ?\Throwable { + return $this->error; + } + + public function isStringable(): bool { + return $this->stringable; + } + + public function getClass(): string { + return $this->class; + } +} diff --git a/lib/private/DB/OracleConnection.php b/lib/private/DB/OracleConnection.php new file mode 100644 index 00000000000..5ffb65d801d --- /dev/null +++ b/lib/private/DB/OracleConnection.php @@ -0,0 +1,89 @@ +<?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; + +class OracleConnection extends Connection { + /** + * Quote the keys of the array + * @param array<string, string> $data + * @return array<string, string> + */ + private function quoteKeys(array $data) { + $return = []; + $c = $this->getDatabasePlatform()->getIdentifierQuoteCharacter(); + foreach ($data as $key => $value) { + if ($key[0] !== $c) { + $return[$this->quoteIdentifier($key)] = $value; + } else { + $return[$key] = $value; + } + } + return $return; + } + + /** + * {@inheritDoc} + */ + public function insert($table, array $data, array $types = []) { + if ($table[0] !== $this->getDatabasePlatform()->getIdentifierQuoteCharacter()) { + $table = $this->quoteIdentifier($table); + } + $data = $this->quoteKeys($data); + return parent::insert($table, $data, $types); + } + + /** + * {@inheritDoc} + */ + public function update($table, array $data, array $criteria, array $types = []) { + if ($table[0] !== $this->getDatabasePlatform()->getIdentifierQuoteCharacter()) { + $table = $this->quoteIdentifier($table); + } + $data = $this->quoteKeys($data); + $criteria = $this->quoteKeys($criteria); + return parent::update($table, $data, $criteria, $types); + } + + /** + * {@inheritDoc} + */ + public function delete($table, array $criteria, array $types = []) { + if ($table[0] !== $this->getDatabasePlatform()->getIdentifierQuoteCharacter()) { + $table = $this->quoteIdentifier($table); + } + $criteria = $this->quoteKeys($criteria); + return parent::delete($table, $criteria); + } + + /** + * Drop a table from the database if it exists + * + * @param string $table table name without the prefix + */ + public function dropTable($table) { + $table = $this->tablePrefix . trim($table); + $table = $this->quoteIdentifier($table); + $schema = $this->createSchemaManager(); + if ($schema->tablesExist([$table])) { + $schema->dropTable($table); + } + } + + /** + * Check if a table exists + * + * @param string $table table name without the prefix + * @return bool + */ + public function tableExists($table) { + $table = $this->tablePrefix . trim($table); + $table = $this->quoteIdentifier($table); + $schema = $this->createSchemaManager(); + return $schema->tablesExist([$table]); + } +} diff --git a/lib/private/DB/OracleMigrator.php b/lib/private/DB/OracleMigrator.php new file mode 100644 index 00000000000..c5a7646dbb2 --- /dev/null +++ b/lib/private/DB/OracleMigrator.php @@ -0,0 +1,139 @@ +<?php + +declare(strict_types=1); +/** + * SPDX-FileCopyrightText: 2023 Nextcloud GmbH and Nextcloud contributors + * SPDX-FileCopyrightText: 2016 ownCloud, Inc. + * SPDX-License-Identifier: AGPL-3.0-only + */ +namespace OC\DB; + +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Schema\Schema; + +class OracleMigrator extends Migrator { + /** + * @param Schema $targetSchema + * @param \Doctrine\DBAL\Connection $connection + * @return \Doctrine\DBAL\Schema\SchemaDiff + * @throws Exception + */ + protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection): \Doctrine\DBAL\Schema\SchemaDiff { + // oracle forces us to quote the identifiers + $quotedSchema = new Schema(); + foreach ($targetSchema->getTables() as $table) { + $quotedTable = $quotedSchema->createTable( + $this->connection->quoteIdentifier($table->getName()), + ); + + foreach ($table->getColumns() as $column) { + $newColumn = $quotedTable->addColumn( + $this->connection->quoteIdentifier($column->getName()), + $column->getType()->getTypeRegistry()->lookupName($column->getType()), + ); + $newColumn->setAutoincrement($column->getAutoincrement()); + $newColumn->setColumnDefinition($column->getColumnDefinition()); + $newColumn->setComment($column->getComment()); + $newColumn->setDefault($column->getDefault()); + $newColumn->setFixed($column->getFixed()); + $newColumn->setLength($column->getLength()); + $newColumn->setNotnull($column->getNotnull()); + $newColumn->setPrecision($column->getPrecision()); + $newColumn->setScale($column->getScale()); + $newColumn->setUnsigned($column->getUnsigned()); + $newColumn->setPlatformOptions($column->getPlatformOptions()); + } + + foreach ($table->getIndexes() as $index) { + if ($index->isPrimary()) { + $quotedTable->setPrimaryKey( + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $index->getColumns()), + //TODO migrate existing uppercase indexes, then $this->connection->quoteIdentifier($index->getName()), + $index->getName(), + ); + } elseif ($index->isUnique()) { + $quotedTable->addUniqueIndex( + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $index->getColumns()), + //TODO migrate existing uppercase indexes, then $this->connection->quoteIdentifier($index->getName()), + $index->getName(), + $index->getOptions(), + ); + } else { + $quotedTable->addIndex( + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $index->getColumns()), + //TODO migrate existing uppercase indexes, then $this->connection->quoteIdentifier($index->getName()), + $index->getName(), + $index->getFlags(), + $index->getOptions(), + ); + } + } + + foreach ($table->getUniqueConstraints() as $constraint) { + $quotedTable->addUniqueConstraint( + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $constraint->getColumns()), + $this->connection->quoteIdentifier($constraint->getName()), + $constraint->getFlags(), + $constraint->getOptions(), + ); + } + + foreach ($table->getForeignKeys() as $foreignKey) { + $quotedTable->addForeignKeyConstraint( + $this->connection->quoteIdentifier($foreignKey->getForeignTableName()), + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $foreignKey->getLocalColumns()), + array_map(function ($columnName) { + return $this->connection->quoteIdentifier($columnName); + }, $foreignKey->getForeignColumns()), + $foreignKey->getOptions(), + $this->connection->quoteIdentifier($foreignKey->getName()), + ); + } + + foreach ($table->getOptions() as $option => $value) { + $quotedTable->addOption( + $option, + $value, + ); + } + } + + foreach ($targetSchema->getSequences() as $sequence) { + $quotedSchema->createSequence( + $sequence->getName(), + $sequence->getAllocationSize(), + $sequence->getInitialValue(), + ); + } + + return parent::getDiff($quotedSchema, $connection); + } + + /** + * @param $statement + * @return string + */ + protected function convertStatementToScript($statement) { + if (str_ends_with($statement, ';')) { + return $statement . PHP_EOL . '/' . PHP_EOL; + } + $script = $statement . ';'; + $script .= PHP_EOL; + $script .= PHP_EOL; + return $script; + } + + protected function getFilterExpression() { + return '/^"' . preg_quote($this->config->getSystemValueString('dbtableprefix', 'oc_')) . '/'; + } +} diff --git a/lib/private/DB/PgSqlTools.php b/lib/private/DB/PgSqlTools.php new file mode 100644 index 00000000000..d529cb26b09 --- /dev/null +++ b/lib/private/DB/PgSqlTools.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; + +use Doctrine\DBAL\Schema\AbstractAsset; +use OCP\IConfig; +use function preg_match; +use function preg_quote; + +/** + * Various PostgreSQL specific helper functions. + */ +class PgSqlTools { + /** @var \OCP\IConfig */ + private $config; + + /** + * @param \OCP\IConfig $config + */ + public function __construct(IConfig $config) { + $this->config = $config; + } + + /** + * @brief Resynchronizes all sequences of a database after using INSERTs + * without leaving out the auto-incremented column. + * @param \OC\DB\Connection $conn + * @return null + */ + public function resynchronizeDatabaseSequences(Connection $conn) { + $databaseName = $conn->getDatabase(); + $conn->getConfiguration()->setSchemaAssetsFilter(function ($asset) { + /** @var string|AbstractAsset $asset */ + $filterExpression = '/^' . preg_quote($this->config->getSystemValueString('dbtableprefix', 'oc_')) . '/'; + if ($asset instanceof AbstractAsset) { + return preg_match($filterExpression, $asset->getName()) !== false; + } + return preg_match($filterExpression, $asset) !== false; + }); + + foreach ($conn->createSchemaManager()->listSequences() as $sequence) { + $sequenceName = $sequence->getName(); + $sqlInfo = 'SELECT table_schema, table_name, column_name + FROM information_schema.columns + WHERE column_default = ? AND table_catalog = ?'; + $result = $conn->executeQuery($sqlInfo, [ + "nextval('$sequenceName'::regclass)", + $databaseName + ]); + $sequenceInfo = $result->fetchAssociative(); + $result->free(); + /** @var string $tableName */ + $tableName = $sequenceInfo['table_name']; + /** @var string $columnName */ + $columnName = $sequenceInfo['column_name']; + $sqlMaxId = "SELECT MAX($columnName) FROM $tableName"; + $sqlSetval = "SELECT setval('$sequenceName', ($sqlMaxId))"; + $conn->executeQuery($sqlSetval); + } + } +} diff --git a/lib/private/DB/PreparedStatement.php b/lib/private/DB/PreparedStatement.php new file mode 100644 index 00000000000..54561ed96cd --- /dev/null +++ b/lib/private/DB/PreparedStatement.php @@ -0,0 +1,83 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2021 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\ParameterType; +use Doctrine\DBAL\Statement; +use OCP\DB\IPreparedStatement; +use OCP\DB\IResult; +use PDO; + +/** + * Adapts our public API to what doctrine/dbal exposed with 2.6 + * + * The old dbal statement had stateful methods e.g. to fetch data from an executed + * prepared statement. To provide backwards compatibility to apps we need to make + * this class stateful. As soon as those now deprecated exposed methods are gone, + * we can limit the API of this adapter to the methods that map to the direct dbal + * methods without much magic. + */ +class PreparedStatement implements IPreparedStatement { + /** @var Statement */ + private $statement; + + /** @var IResult|null */ + private $result; + + public function __construct(Statement $statement) { + $this->statement = $statement; + } + + public function closeCursor(): bool { + $this->getResult()->closeCursor(); + + return true; + } + + public function fetch(int $fetchMode = PDO::FETCH_ASSOC) { + return $this->getResult()->fetch($fetchMode); + } + + public function fetchAll(int $fetchMode = PDO::FETCH_ASSOC): array { + return $this->getResult()->fetchAll($fetchMode); + } + + public function fetchColumn() { + return $this->getResult()->fetchOne(); + } + + public function fetchOne() { + return $this->getResult()->fetchOne(); + } + + public function bindValue($param, $value, $type = ParameterType::STRING): bool { + return $this->statement->bindValue($param, $value, $type); + } + + public function bindParam($param, &$variable, $type = ParameterType::STRING, $length = null): bool { + return $this->statement->bindParam($param, $variable, $type, $length); + } + + public function execute($params = null): IResult { + return ($this->result = new ResultAdapter($this->statement->execute($params))); + } + + public function rowCount(): int { + return $this->getResult()->rowCount(); + } + + private function getResult(): IResult { + if ($this->result !== null) { + return $this->result; + } + + throw new Exception('You have to execute the prepared statement before accessing the results'); + } +} 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(); + } + } + + +} diff --git a/lib/private/DB/ResultAdapter.php b/lib/private/DB/ResultAdapter.php new file mode 100644 index 00000000000..95a7620e0ff --- /dev/null +++ b/lib/private/DB/ResultAdapter.php @@ -0,0 +1,61 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2021 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +use Doctrine\DBAL\Result; +use OCP\DB\IResult; +use PDO; + +/** + * Adapts DBAL 2.6 API for DBAL 3.x for backwards compatibility of a leaked type + */ +class ResultAdapter implements IResult { + /** @var Result */ + private $inner; + + public function __construct(Result $inner) { + $this->inner = $inner; + } + + public function closeCursor(): bool { + $this->inner->free(); + + return true; + } + + public function fetch(int $fetchMode = PDO::FETCH_ASSOC) { + return match ($fetchMode) { + PDO::FETCH_ASSOC => $this->inner->fetchAssociative(), + PDO::FETCH_NUM => $this->inner->fetchNumeric(), + PDO::FETCH_COLUMN => $this->inner->fetchOne(), + default => throw new \Exception('Fetch mode needs to be assoc, num or column.'), + }; + } + + public function fetchAll(int $fetchMode = PDO::FETCH_ASSOC): array { + return match ($fetchMode) { + PDO::FETCH_ASSOC => $this->inner->fetchAllAssociative(), + PDO::FETCH_NUM => $this->inner->fetchAllNumeric(), + PDO::FETCH_COLUMN => $this->inner->fetchFirstColumn(), + default => throw new \Exception('Fetch mode needs to be assoc, num or column.'), + }; + } + + public function fetchColumn($columnIndex = 0) { + return $this->inner->fetchOne(); + } + + public function fetchOne() { + return $this->inner->fetchOne(); + } + + public function rowCount(): int { + return $this->inner->rowCount(); + } +} diff --git a/lib/private/DB/SQLiteMigrator.php b/lib/private/DB/SQLiteMigrator.php new file mode 100644 index 00000000000..6be17625476 --- /dev/null +++ b/lib/private/DB/SQLiteMigrator.php @@ -0,0 +1,30 @@ +<?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; + +use Doctrine\DBAL\Schema\Schema; + +class SQLiteMigrator extends Migrator { + /** + * @param Schema $targetSchema + * @param \Doctrine\DBAL\Connection $connection + * @return \Doctrine\DBAL\Schema\SchemaDiff + */ + protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection) { + foreach ($targetSchema->getTables() as $table) { + foreach ($table->getColumns() as $column) { + // column comments are not supported on SQLite + if ($column->getComment() !== null) { + $column->setComment(null); + } + } + } + + return parent::getDiff($targetSchema, $connection); + } +} diff --git a/lib/private/DB/SQLiteSessionInit.php b/lib/private/DB/SQLiteSessionInit.php new file mode 100644 index 00000000000..5fe0cb3abf6 --- /dev/null +++ b/lib/private/DB/SQLiteSessionInit.php @@ -0,0 +1,53 @@ +<?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; + +use Doctrine\Common\EventSubscriber; +use Doctrine\DBAL\Event\ConnectionEventArgs; +use Doctrine\DBAL\Events; + +class SQLiteSessionInit implements EventSubscriber { + /** + * @var bool + */ + private $caseSensitiveLike; + + /** + * @var string + */ + private $journalMode; + + /** + * Configure case sensitive like for each connection + * + * @param bool $caseSensitiveLike + * @param string $journalMode + */ + public function __construct($caseSensitiveLike, $journalMode) { + $this->caseSensitiveLike = $caseSensitiveLike; + $this->journalMode = $journalMode; + } + + /** + * @param ConnectionEventArgs $args + * @return void + */ + public function postConnect(ConnectionEventArgs $args) { + $sensitive = $this->caseSensitiveLike ? 'true' : 'false'; + $args->getConnection()->executeUpdate('PRAGMA case_sensitive_like = ' . $sensitive); + $args->getConnection()->executeUpdate('PRAGMA journal_mode = ' . $this->journalMode); + /** @var \Doctrine\DBAL\Driver\PDO\Connection $connection */ + $connection = $args->getConnection()->getWrappedConnection(); + $pdo = $connection->getWrappedConnection(); + $pdo->sqliteCreateFunction('md5', 'md5', 1); + } + + public function getSubscribedEvents() { + return [Events::postConnect]; + } +} diff --git a/lib/private/DB/SchemaWrapper.php b/lib/private/DB/SchemaWrapper.php new file mode 100644 index 00000000000..0d5b2040513 --- /dev/null +++ b/lib/private/DB/SchemaWrapper.php @@ -0,0 +1,134 @@ +<?php + +/** + * SPDX-FileCopyrightText: 2017 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +use Doctrine\DBAL\Exception; +use Doctrine\DBAL\Platforms\AbstractPlatform; +use Doctrine\DBAL\Schema\Schema; +use OCP\DB\ISchemaWrapper; + +class SchemaWrapper implements ISchemaWrapper { + /** @var Connection */ + protected $connection; + + /** @var Schema */ + protected $schema; + + /** @var array */ + protected $tablesToDelete = []; + + public function __construct(Connection $connection, ?Schema $schema = null) { + $this->connection = $connection; + if ($schema) { + $this->schema = $schema; + } else { + $this->schema = $this->connection->createSchema(); + } + } + + public function getWrappedSchema() { + return $this->schema; + } + + public function performDropTableCalls() { + foreach ($this->tablesToDelete as $tableName => $true) { + $this->connection->dropTable($tableName); + foreach ($this->connection->getShardConnections() as $shardConnection) { + $shardConnection->dropTable($tableName); + } + unset($this->tablesToDelete[$tableName]); + } + } + + /** + * Gets all table names + * + * @return array + */ + public function getTableNamesWithoutPrefix() { + $tableNames = $this->schema->getTableNames(); + return array_map(function ($tableName) { + if (str_starts_with($tableName, $this->connection->getPrefix())) { + return substr($tableName, strlen($this->connection->getPrefix())); + } + + return $tableName; + }, $tableNames); + } + + // Overwritten methods + + /** + * @return array + */ + public function getTableNames() { + return $this->schema->getTableNames(); + } + + /** + * @param string $tableName + * + * @return \Doctrine\DBAL\Schema\Table + * @throws \Doctrine\DBAL\Schema\SchemaException + */ + public function getTable($tableName) { + return $this->schema->getTable($this->connection->getPrefix() . $tableName); + } + + /** + * Does this schema have a table with the given name? + * + * @param string $tableName + * + * @return boolean + */ + public function hasTable($tableName) { + return $this->schema->hasTable($this->connection->getPrefix() . $tableName); + } + + /** + * Creates a new table. + * + * @param string $tableName + * @return \Doctrine\DBAL\Schema\Table + */ + public function createTable($tableName) { + unset($this->tablesToDelete[$tableName]); + return $this->schema->createTable($this->connection->getPrefix() . $tableName); + } + + /** + * Drops a table from the schema. + * + * @param string $tableName + * @return \Doctrine\DBAL\Schema\Schema + */ + public function dropTable($tableName) { + $this->tablesToDelete[$tableName] = true; + return $this->schema->dropTable($this->connection->getPrefix() . $tableName); + } + + /** + * Gets all tables of this schema. + * + * @return \Doctrine\DBAL\Schema\Table[] + */ + public function getTables() { + return $this->schema->getTables(); + } + + /** + * Gets the DatabasePlatform for the database. + * + * @return AbstractPlatform + * + * @throws Exception + */ + public function getDatabasePlatform() { + return $this->connection->getDatabasePlatform(); + } +} diff --git a/lib/private/DB/SetTransactionIsolationLevel.php b/lib/private/DB/SetTransactionIsolationLevel.php new file mode 100644 index 00000000000..cd18c4255e3 --- /dev/null +++ b/lib/private/DB/SetTransactionIsolationLevel.php @@ -0,0 +1,36 @@ +<?php + +declare(strict_types=1); + +/** + * SPDX-FileCopyrightText: 2019 Nextcloud GmbH and Nextcloud contributors + * SPDX-License-Identifier: AGPL-3.0-or-later + */ +namespace OC\DB; + +use Doctrine\Common\EventSubscriber; +use Doctrine\DBAL\Connections\PrimaryReadReplicaConnection; +use Doctrine\DBAL\Event\ConnectionEventArgs; +use Doctrine\DBAL\Events; +use Doctrine\DBAL\Platforms\MySQLPlatform; +use Doctrine\DBAL\TransactionIsolationLevel; + +class SetTransactionIsolationLevel implements EventSubscriber { + /** + * @param ConnectionEventArgs $args + * @return void + */ + public function postConnect(ConnectionEventArgs $args) { + $connection = $args->getConnection(); + if ($connection instanceof PrimaryReadReplicaConnection && $connection->isConnectedToPrimary()) { + $connection->setTransactionIsolation(TransactionIsolationLevel::READ_COMMITTED); + if ($connection->getDatabasePlatform() instanceof MySQLPlatform) { + $connection->executeStatement('SET SESSION AUTOCOMMIT=1'); + } + } + } + + public function getSubscribedEvents() { + return [Events::postConnect]; + } +} |