Merge pull request #37961 from nextcloud/poc/noid/systemtags-perf

SystemTags endpoint to return tags used by a user with meta data

4 files changed, 174 insertions, 54 deletions

diff --git a/lib/private/Files/Cache/CacheQueryBuilder.php b/lib/private/Files/Cache/CacheQueryBuilder.php
index 496a8361d77..34d2177b84e 100644
--- a/lib/private/Files/Cache/CacheQueryBuilder.php
+++ b/lib/private/Files/Cache/CacheQueryBuilder.php
@@ -41,8 +41,27 @@ class CacheQueryBuilder extends QueryBuilder {
 		parent::__construct($connection, $systemConfig, $logger);
 	}
 
+	public function selectTagUsage(): self {
+		$this
+			->select('systemtag.name', 'systemtag.id', 'systemtag.visibility', 'systemtag.editable')
+			->selectAlias($this->createFunction('COUNT(filecache.fileid)'), 'number_files')
+			->selectAlias($this->createFunction('MAX(filecache.fileid)'), 'ref_file_id')
+			->from('filecache', 'filecache')
+			->leftJoin('filecache', 'systemtag_object_mapping', 'systemtagmap', $this->expr()->andX(
+				$this->expr()->eq('filecache.fileid', $this->expr()->castColumn('systemtagmap.objectid', IQueryBuilder::PARAM_INT)),
+				$this->expr()->eq('systemtagmap.objecttype', $this->createNamedParameter('files'))
+			))
+			->leftJoin('systemtagmap', 'systemtag', 'systemtag', $this->expr()->andX(
+				$this->expr()->eq('systemtag.id', 'systemtagmap.systemtagid'),
+				$this->expr()->eq('systemtag.visibility', $this->createNamedParameter(true))
+			))
+			->groupBy('systemtag.name', 'systemtag.id', 'systemtag.visibility', 'systemtag.editable');
+
+		return $this;
+	}
+
 	public function selectFileCache(string $alias = null, bool $joinExtendedCache = true) {
-		$name = $alias ? $alias : 'filecache';
+		$name = $alias ?: 'filecache';
 		$this->select("$name.fileid", 'storage', 'path', 'path_hash', "$name.parent", "$name.name", 'mimetype', 'mimepart', 'size', 'mtime',
 			'storage_mtime', 'encrypted', 'etag', 'permissions', 'checksum', 'unencrypted_size')
 			->from('filecache', $name);
diff --git a/lib/private/Files/Cache/QuerySearchHelper.php b/lib/private/Files/Cache/QuerySearchHelper.php
index eba2aac927b..30b3c7225ac 100644
--- a/lib/private/Files/Cache/QuerySearchHelper.php
+++ b/lib/private/Files/Cache/QuerySearchHelper.php
@@ -25,13 +25,17 @@
  */
 namespace OC\Files\Cache;
 
+use OC\Files\Cache\Wrapper\CacheJail;
+use OC\Files\Node\Root;
 use OC\Files\Search\QueryOptimizer\QueryOptimizer;
 use OC\Files\Search\SearchBinaryOperator;
 use OC\SystemConfig;
 use OCP\DB\QueryBuilder\IQueryBuilder;
 use OCP\Files\Cache\ICache;
 use OCP\Files\Cache\ICacheEntry;
+use OCP\Files\Folder;
 use OCP\Files\IMimeTypeLoader;
+use OCP\Files\Mount\IMountPoint;
 use OCP\Files\Search\ISearchBinaryOperator;
 use OCP\Files\Search\ISearchQuery;
 use OCP\IDBConnection;
@@ -74,6 +78,45 @@ class QuerySearchHelper {
 		);
 	}
 
+	protected function applySearchConstraints(CacheQueryBuilder $query, ISearchQuery $searchQuery, array $caches): void {
+		$storageFilters = array_values(array_map(function (ICache $cache) {
+			return $cache->getQueryFilterForStorage();
+		}, $caches));
+		$storageFilter = new SearchBinaryOperator(ISearchBinaryOperator::OPERATOR_OR, $storageFilters);
+		$filter = new SearchBinaryOperator(ISearchBinaryOperator::OPERATOR_AND, [$searchQuery->getSearchOperation(), $storageFilter]);
+		$this->queryOptimizer->processOperator($filter);
+
+		$searchExpr = $this->searchBuilder->searchOperatorToDBExpr($query, $filter);
+		if ($searchExpr) {
+			$query->andWhere($searchExpr);
+		}
+
+		$this->searchBuilder->addSearchOrdersToQuery($query, $searchQuery->getOrder());
+
+		if ($searchQuery->getLimit()) {
+			$query->setMaxResults($searchQuery->getLimit());
+		}
+		if ($searchQuery->getOffset()) {
+			$query->setFirstResult($searchQuery->getOffset());
+		}
+	}
+
+
+	/**
+	 * @return array<array-key, array{id: int, name: string, visibility: int, editable: int, ref_file_id: int, number_files: int}>
+	 */
+	public function findUsedTagsInCaches(ISearchQuery $searchQuery, array $caches): array {
+		$query = $this->getQueryBuilder();
+		$query->selectTagUsage();
+
+		$this->applySearchConstraints($query, $searchQuery, $caches);
+
+		$result = $query->execute();
+		$tags = $result->fetchAll();
+		$result->closeCursor();
+		return $tags;
+	}
+
 	/**
 	 * Perform a file system search in multiple caches
 	 *
@@ -127,26 +170,7 @@ class QuerySearchHelper {
 				));
 		}
 
-		$storageFilters = array_values(array_map(function (ICache $cache) {
-			return $cache->getQueryFilterForStorage();
-		}, $caches));
-		$storageFilter = new SearchBinaryOperator(ISearchBinaryOperator::OPERATOR_OR, $storageFilters);
-		$filter = new SearchBinaryOperator(ISearchBinaryOperator::OPERATOR_AND, [$searchQuery->getSearchOperation(), $storageFilter]);
-		$this->queryOptimizer->processOperator($filter);
-
-		$searchExpr = $this->searchBuilder->searchOperatorToDBExpr($builder, $filter);
-		if ($searchExpr) {
-			$query->andWhere($searchExpr);
-		}
-
-		$this->searchBuilder->addSearchOrdersToQuery($query, $searchQuery->getOrder());
-
-		if ($searchQuery->getLimit()) {
-			$query->setMaxResults($searchQuery->getLimit());
-		}
-		if ($searchQuery->getOffset()) {
-			$query->setFirstResult($searchQuery->getOffset());
-		}
+		$this->applySearchConstraints($query, $searchQuery, $caches);
 
 		$result = $query->execute();
 		$files = $result->fetchAll();
@@ -158,7 +182,7 @@ class QuerySearchHelper {
 		$result->closeCursor();
 
 		// loop through all caches for each result to see if the result matches that storage
-		// results are grouped by the same array keys as the caches argument to allow the caller to distringuish the source of the results
+		// results are grouped by the same array keys as the caches argument to allow the caller to distinguish the source of the results
 		$results = array_fill_keys(array_keys($caches), []);
 		foreach ($rawEntries as $rawEntry) {
 			foreach ($caches as $cacheKey => $cache) {
@@ -170,4 +194,37 @@ class QuerySearchHelper {
 		}
 		return $results;
 	}
+
+	/**
+	 * @return array{array<string, ICache>, array<string, IMountPoint>}
+	 */
+	public function getCachesAndMountPointsForSearch(Root $root, string $path, bool $limitToHome = false): array {
+		$rootLength = strlen($path);
+		$mount = $root->getMount($path);
+		$storage = $mount->getStorage();
+		$internalPath = $mount->getInternalPath($path);
+
+		if ($internalPath !== '') {
+			// a temporary CacheJail is used to handle filtering down the results to within this folder
+			$caches = ['' => new CacheJail($storage->getCache(''), $internalPath)];
+		} else {
+			$caches = ['' => $storage->getCache('')];
+		}
+		$mountByMountPoint = ['' => $mount];
+
+		if (!$limitToHome) {
+			/** @var IMountPoint[] $mounts */
+			$mounts = $root->getMountsIn($path);
+			foreach ($mounts as $mount) {
+				$storage = $mount->getStorage();
+				if ($storage) {
+					$relativeMountPoint = ltrim(substr($mount->getMountPoint(), $rootLength), '/');
+					$caches[$relativeMountPoint] = $storage->getCache('');
+					$mountByMountPoint[$relativeMountPoint] = $mount;
+				}
+			}
+		}
+
+		return [$caches, $mountByMountPoint];
+	}
 }
diff --git a/lib/private/Files/Node/Folder.php b/lib/private/Files/Node/Folder.php
index 2c376fe5885..1d6d88bafe6 100644
--- a/lib/private/Files/Node/Folder.php
+++ b/lib/private/Files/Node/Folder.php
@@ -33,7 +33,6 @@ namespace OC\Files\Node;
 
 use OC\Files\Cache\QuerySearchHelper;
 use OC\Files\Search\SearchBinaryOperator;
-use OC\Files\Cache\Wrapper\CacheJail;
 use OC\Files\Search\SearchComparison;
 use OC\Files\Search\SearchOrder;
 use OC\Files\Search\SearchQuery;
@@ -204,7 +203,7 @@ class Folder extends Node implements \OCP\Files\Folder {
 		throw new NotPermittedException('No create permission for path "' . $path . '"');
 	}
 
-	private function queryFromOperator(ISearchOperator $operator, string $uid = null): ISearchQuery {
+	private function queryFromOperator(ISearchOperator $operator, string $uid = null, int $limit = 0, int $offset = 0): ISearchQuery {
 		if ($uid === null) {
 			$user = null;
 		} else {
@@ -212,7 +211,7 @@ class Folder extends Node implements \OCP\Files\Folder {
 			$userManager = \OC::$server->query(IUserManager::class);
 			$user = $userManager->get($uid);
 		}
-		return new SearchQuery($operator, 0, 0, [], $user);
+		return new SearchQuery($operator, $limit, $offset, [], $user);
 	}
 
 	/**
@@ -234,40 +233,13 @@ class Folder extends Node implements \OCP\Files\Folder {
 			throw new \InvalidArgumentException('searching by owner is only allowed in the users home folder');
 		}
 
-		$rootLength = strlen($this->path);
-		$mount = $this->root->getMount($this->path);
-		$storage = $mount->getStorage();
-		$internalPath = $mount->getInternalPath($this->path);
-
-		// collect all caches for this folder, indexed by their mountpoint relative to this folder
-		// and save the mount which is needed later to construct the FileInfo objects
-
-		if ($internalPath !== '') {
-			// a temporary CacheJail is used to handle filtering down the results to within this folder
-			$caches = ['' => new CacheJail($storage->getCache(''), $internalPath)];
-		} else {
-			$caches = ['' => $storage->getCache('')];
-		}
-		$mountByMountPoint = ['' => $mount];
-
-		if (!$limitToHome) {
-			$mounts = $this->root->getMountsIn($this->path);
-			foreach ($mounts as $mount) {
-				$storage = $mount->getStorage();
-				if ($storage) {
-					$relativeMountPoint = ltrim(substr($mount->getMountPoint(), $rootLength), '/');
-					$caches[$relativeMountPoint] = $storage->getCache('');
-					$mountByMountPoint[$relativeMountPoint] = $mount;
-				}
-			}
-		}
-
 		/** @var QuerySearchHelper $searchHelper */
 		$searchHelper = \OC::$server->get(QuerySearchHelper::class);
+		[$caches, $mountByMountPoint] = $searchHelper->getCachesAndMountPointsForSearch($this->root, $this->path, $limitToHome);
 		$resultsPerCache = $searchHelper->searchInCaches($query, $caches);
 
 		// loop through all results per-cache, constructing the FileInfo object from the CacheEntry and merge them all
-		$files = array_merge(...array_map(function (array $results, $relativeMountPoint) use ($mountByMountPoint) {
+		$files = array_merge(...array_map(function (array $results, string $relativeMountPoint) use ($mountByMountPoint) {
 			$mount = $mountByMountPoint[$relativeMountPoint];
 			return array_map(function (ICacheEntry $result) use ($relativeMountPoint, $mount) {
 				return $this->cacheEntryToFileInfo($mount, $relativeMountPoint, $result);
diff --git a/lib/private/SystemTag/SystemTagsInFilesDetector.php b/lib/private/SystemTag/SystemTagsInFilesDetector.php
new file mode 100644
index 00000000000..c9f26c58c02
--- /dev/null
+++ b/lib/private/SystemTag/SystemTagsInFilesDetector.php
@@ -0,0 +1,72 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright Copyright (c) 2023 Arthur Schiwon <blizzz@arthur-schiwon.de>
+ *
+ * @author Arthur Schiwon <blizzz@arthur-schiwon.de>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <https://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OC\SystemTag;
+
+use OC\Files\Cache\QuerySearchHelper;
+use OC\Files\Node\Root;
+use OC\Files\Search\SearchBinaryOperator;
+use OC\Files\Search\SearchComparison;
+use OC\Files\Search\SearchQuery;
+use OCP\Files\Folder;
+use OCP\Files\Search\ISearchBinaryOperator;
+use OCP\Files\Search\ISearchComparison;
+
+class SystemTagsInFilesDetector {
+	public function __construct(protected QuerySearchHelper $searchHelper) {
+	}
+
+	public function detectAssignedSystemTagsIn(
+		Folder $folder,
+		string $filteredMediaType = '',
+		int $limit = 0,
+		int $offset = 0
+	): array {
+		$operator = new SearchComparison(ISearchComparison::COMPARE_LIKE, 'systemtag', '%');
+		// Currently query has to have exactly one search condition. If no media type is provided,
+		// we fall back to the presence of a system tag.
+		if ($filteredMediaType !== '') {
+			$mimeOperator = new SearchComparison(ISearchComparison::COMPARE_LIKE, 'mimetype', $filteredMediaType . '/%');
+			$operator = new SearchBinaryOperator(ISearchBinaryOperator::OPERATOR_AND, [$operator, $mimeOperator]);
+		}
+
+		$query = new SearchQuery($operator, $limit, $offset, []);
+		[$caches, ] = $this->searchHelper->getCachesAndMountPointsForSearch(
+			$this->getRootFolder($folder),
+			$folder->getPath(),
+		);
+		return $this->searchHelper->findUsedTagsInCaches($query, $caches);
+	}
+
+	protected function getRootFolder(?Folder $folder): Root {
+		if ($folder instanceof Root) {
+			return $folder;
+		} elseif ($folder === null) {
+			throw new \LogicException('Could not climb up to root folder');
+		}
+		return $this->getRootFolder($folder->getParent());
+	}
+}