2 files changed, 370 insertions, 0 deletions
diff --git a/lib/private/FilesMetadata/Service/IndexRequestService.php b/lib/private/FilesMetadata/Service/IndexRequestService.php
new file mode 100644
index 00000000000..91bd9f0b11e
--- /dev/null
+++ b/lib/private/FilesMetadata/Service/IndexRequestService.php
@@ -0,0 +1,178 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * SPDX-FileCopyrightText: 2023 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\FilesMetadata\Service;
+
+use OCP\DB\Exception as DbException;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\FilesMetadata\Exceptions\FilesMetadataNotFoundException;
+use OCP\FilesMetadata\Exceptions\FilesMetadataTypeException;
+use OCP\FilesMetadata\Model\IFilesMetadata;
+use OCP\FilesMetadata\Model\IMetadataValueWrapper;
+use OCP\IDBConnection;
+use Psr\Log\LoggerInterface;
+
+/**
+ * manage sql request to the metadata_index table
+ */
+class IndexRequestService {
+	public const TABLE_METADATA_INDEX = 'files_metadata_index';
+
+	public function __construct(
+		private IDBConnection $dbConnection,
+		private LoggerInterface $logger,
+	) {
+	}
+
+	/**
+	 * update the index for a specific metadata key
+	 *
+	 * @param IFilesMetadata $filesMetadata metadata
+	 * @param string $key metadata key to update
+	 *
+	 * @throws DbException
+	 */
+	public function updateIndex(IFilesMetadata $filesMetadata, string $key): void {
+		$fileId = $filesMetadata->getFileId();
+		try {
+			$metadataType = $filesMetadata->getType($key);
+		} catch (FilesMetadataNotFoundException $e) {
+			return;
+		}
+
+		/**
+		 * might look harsh, but a lot simpler than comparing current indexed data, as we can expect
+		 * conflict with a change of types.
+		 * We assume that each time one random metadata were modified we can drop all index for this
+		 * key and recreate them.
+		 * To make it slightly cleaner, we'll use transaction
+		 */
+		$this->dbConnection->beginTransaction();
+		try {
+			$this->dropIndex($fileId, $key);
+			match ($metadataType) {
+				IMetadataValueWrapper::TYPE_STRING => $this->insertIndexString($fileId, $key, $filesMetadata->getString($key)),
+				IMetadataValueWrapper::TYPE_INT => $this->insertIndexInt($fileId, $key, $filesMetadata->getInt($key)),
+				IMetadataValueWrapper::TYPE_BOOL => $this->insertIndexBool($fileId, $key, $filesMetadata->getBool($key)),
+				IMetadataValueWrapper::TYPE_STRING_LIST => $this->insertIndexStringList($fileId, $key, $filesMetadata->getStringList($key)),
+				IMetadataValueWrapper::TYPE_INT_LIST => $this->insertIndexIntList($fileId, $key, $filesMetadata->getIntList($key))
+			};
+		} catch (FilesMetadataNotFoundException|FilesMetadataTypeException|DbException $e) {
+			$this->dbConnection->rollBack();
+			$this->logger->warning('issue while updateIndex', ['exception' => $e, 'fileId' => $fileId, 'key' => $key]);
+		}
+
+		$this->dbConnection->commit();
+	}
+
+	/**
+	 * insert a new entry in the metadata_index table for a string value
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 * @param string $value metadata value
+	 *
+	 * @throws DbException
+	 */
+	private function insertIndexString(int $fileId, string $key, string $value): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_METADATA_INDEX)
+			->setValue('meta_key', $qb->createNamedParameter($key))
+			->setValue('meta_value_string', $qb->createNamedParameter($value))
+			->setValue('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT));
+		$qb->executeStatement();
+	}
+
+	/**
+	 * insert a new entry in the metadata_index table for an int value
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 * @param int $value metadata value
+	 *
+	 * @throws DbException
+	 */
+	public function insertIndexInt(int $fileId, string $key, int $value): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_METADATA_INDEX)
+			->setValue('meta_key', $qb->createNamedParameter($key))
+			->setValue('meta_value_int', $qb->createNamedParameter($value, IQueryBuilder::PARAM_INT))
+			->setValue('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT));
+		$qb->executeStatement();
+	}
+
+	/**
+	 * insert a new entry in the metadata_index table for a bool value
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 * @param bool $value metadata value
+	 *
+	 * @throws DbException
+	 */
+	public function insertIndexBool(int $fileId, string $key, bool $value): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_METADATA_INDEX)
+			->setValue('meta_key', $qb->createNamedParameter($key))
+			->setValue('meta_value_int', $qb->createNamedParameter(($value) ? '1' : '0', IQueryBuilder::PARAM_INT))
+			->setValue('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT));
+		$qb->executeStatement();
+	}
+
+	/**
+	 * insert entries in the metadata_index table for list of string
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 * @param string[] $values metadata values
+	 *
+	 * @throws DbException
+	 */
+	public function insertIndexStringList(int $fileId, string $key, array $values): void {
+		foreach ($values as $value) {
+			$this->insertIndexString($fileId, $key, $value);
+		}
+	}
+
+	/**
+	 * insert entries in the metadata_index table for list of int
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 * @param int[] $values metadata values
+	 *
+	 * @throws DbException
+	 */
+	public function insertIndexIntList(int $fileId, string $key, array $values): void {
+		foreach ($values as $value) {
+			$this->insertIndexInt($fileId, $key, $value);
+		}
+	}
+
+	/**
+	 * drop indexes related to a file id
+	 * if a key is specified, only drop entries related to it
+	 *
+	 * @param int $fileId file id
+	 * @param string $key metadata key
+	 *
+	 * @throws DbException
+	 */
+	public function dropIndex(int $fileId, string $key = ''): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$expr = $qb->expr();
+		$qb->delete(self::TABLE_METADATA_INDEX)
+			->where($expr->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
+
+		if ($key !== '') {
+			$qb->andWhere($expr->eq('meta_key', $qb->createNamedParameter($key)));
+		}
+
+		$qb->executeStatement();
+	}
+}
diff --git a/lib/private/FilesMetadata/Service/MetadataRequestService.php b/lib/private/FilesMetadata/Service/MetadataRequestService.php
new file mode 100644
index 00000000000..c308ae1c9c8
--- /dev/null
+++ b/lib/private/FilesMetadata/Service/MetadataRequestService.php
@@ -0,0 +1,192 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * SPDX-FileCopyrightText: 2023 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\FilesMetadata\Service;
+
+use OC\FilesMetadata\Model\FilesMetadata;
+use OCP\DB\Exception;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\FilesMetadata\Exceptions\FilesMetadataNotFoundException;
+use OCP\FilesMetadata\Model\IFilesMetadata;
+use OCP\IDBConnection;
+use Psr\Log\LoggerInterface;
+
+/**
+ * manage sql request to the metadata table
+ */
+class MetadataRequestService {
+	public const TABLE_METADATA = 'files_metadata';
+
+	public function __construct(
+		private IDBConnection $dbConnection,
+		private LoggerInterface $logger,
+	) {
+	}
+
+	private function getStorageId(IFilesMetadata $filesMetadata): int {
+		if ($filesMetadata instanceof FilesMetadata) {
+			$storage = $filesMetadata->getStorageId();
+			if ($storage) {
+				return $storage;
+			}
+		}
+		// all code paths that lead to saving metadata *should* have the storage id set
+		// this fallback is there just in case
+		$query = $this->dbConnection->getQueryBuilder();
+		$query->select('storage')
+			->from('filecache')
+			->where($query->expr()->eq('fileid', $query->createNamedParameter($filesMetadata->getFileId(), IQueryBuilder::PARAM_INT)));
+		$storageId = $query->executeQuery()->fetchColumn();
+
+		if ($filesMetadata instanceof FilesMetadata) {
+			$filesMetadata->setStorageId($storageId);
+		}
+		return $storageId;
+	}
+
+	/**
+	 * store metadata into database
+	 *
+	 * @param IFilesMetadata $filesMetadata
+	 *
+	 * @throws Exception
+	 */
+	public function store(IFilesMetadata $filesMetadata): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->insert(self::TABLE_METADATA)
+			->hintShardKey('storage', $this->getStorageId($filesMetadata))
+			->setValue('file_id', $qb->createNamedParameter($filesMetadata->getFileId(), IQueryBuilder::PARAM_INT))
+			->setValue('json', $qb->createNamedParameter(json_encode($filesMetadata->jsonSerialize())))
+			->setValue('sync_token', $qb->createNamedParameter($this->generateSyncToken()))
+			->setValue('last_update', (string)$qb->createFunction('NOW()'));
+		$qb->executeStatement();
+	}
+
+	/**
+	 * returns metadata for a file id
+	 *
+	 * @param int $fileId file id
+	 *
+	 * @return IFilesMetadata
+	 * @throws FilesMetadataNotFoundException if no metadata are found in database
+	 */
+	public function getMetadataFromFileId(int $fileId): IFilesMetadata {
+		try {
+			$qb = $this->dbConnection->getQueryBuilder();
+			$qb->select('json', 'sync_token')->from(self::TABLE_METADATA);
+			$qb->where($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
+			$result = $qb->executeQuery();
+			$data = $result->fetch();
+			$result->closeCursor();
+		} catch (Exception $e) {
+			$this->logger->warning('exception while getMetadataFromDatabase()', ['exception' => $e, 'fileId' => $fileId]);
+			throw new FilesMetadataNotFoundException();
+		}
+
+		if ($data === false) {
+			throw new FilesMetadataNotFoundException();
+		}
+
+		$metadata = new FilesMetadata($fileId);
+		$metadata->importFromDatabase($data);
+
+		return $metadata;
+	}
+
+	/**
+	 * returns metadata for multiple file ids
+	 *
+	 * @param array $fileIds file ids
+	 *
+	 * @return array File ID is the array key, files without metadata are not returned in the array
+	 * @psalm-return array<int, IFilesMetadata>
+	 */
+	public function getMetadataFromFileIds(array $fileIds): array {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->select('file_id', 'json', 'sync_token')->from(self::TABLE_METADATA);
+		$qb->where($qb->expr()->in('file_id', $qb->createNamedParameter($fileIds, IQueryBuilder::PARAM_INT_ARRAY)));
+
+		$list = [];
+		$result = $qb->executeQuery();
+		while ($data = $result->fetch()) {
+			$fileId = (int)$data['file_id'];
+			$metadata = new FilesMetadata($fileId);
+			try {
+				$metadata->importFromDatabase($data);
+			} catch (FilesMetadataNotFoundException) {
+				continue;
+			}
+			$list[$fileId] = $metadata;
+		}
+		$result->closeCursor();
+
+		return $list;
+	}
+
+	/**
+	 * drop metadata related to a file id
+	 *
+	 * @param int $fileId file id
+	 *
+	 * @return void
+	 * @throws Exception
+	 */
+	public function dropMetadata(int $fileId): void {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$qb->delete(self::TABLE_METADATA)
+			->where($qb->expr()->eq('file_id', $qb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
+		$qb->executeStatement();
+	}
+
+	/**
+	 * update metadata in the database
+	 *
+	 * @param IFilesMetadata $filesMetadata metadata
+	 *
+	 * @return int number of affected rows
+	 * @throws Exception
+	 */
+	public function updateMetadata(IFilesMetadata $filesMetadata): int {
+		$qb = $this->dbConnection->getQueryBuilder();
+		$expr = $qb->expr();
+
+		$qb->update(self::TABLE_METADATA)
+			->hintShardKey('files_metadata', $this->getStorageId($filesMetadata))
+			->set('json', $qb->createNamedParameter(json_encode($filesMetadata->jsonSerialize())))
+			->set('sync_token', $qb->createNamedParameter($this->generateSyncToken()))
+			->set('last_update', $qb->createFunction('NOW()'))
+			->where(
+				$expr->andX(
+					$expr->eq('file_id', $qb->createNamedParameter($filesMetadata->getFileId(), IQueryBuilder::PARAM_INT)),
+					$expr->eq('sync_token', $qb->createNamedParameter($filesMetadata->getSyncToken()))
+				)
+			);
+
+		return $qb->executeStatement();
+	}
+
+	/**
+	 * generate a random token
+	 * @return string
+	 */
+	private function generateSyncToken(): string {
+		$chars = 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM1234567890';
+
+		$str = '';
+		$max = strlen($chars);
+		for ($i = 0; $i < 7; $i++) {
+			try {
+				$str .= $chars[random_int(0, $max - 2)];
+			} catch (\Exception $e) {
+				$this->logger->warning('exception during generateSyncToken', ['exception' => $e]);
+			}
+		}
+
+		return $str;
+	}
+}