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

Target case is photos app: when visiting the tags category, all systemtags
of the whole cloud are retrieved. In subequent steps the next tag is
requested until the browser view is filled with tag tiles (i.e. previews
are requested just as well).

With this approach, we incorpoate the dav search and look for user related
tags that are used by them, and already returns the statistics (number of
files tagged with the respective tag) as well as a file id for the purpose
to load the preview. This defaults to the file with the highest id.

Call:
curl -s -u 'user:password' \
  'https://my.nc.srv/remote.php/dav/systemtags-current' \
  -X PROPFIND -H 'Accept: text/plain' \
  -H 'Accept-Language: en-US,en;q=0.5'  -H 'Depth: 1' \
  -H 'Content-Type: text/plain;charset=UTF-8' \
  --data @/home/doe/request-systemtag-props.xml

With request-systemtag-props.xml:
<?xml version="1.0" encoding="UTF-8"?>
<d:propfind xmlns:d="DAV:">
        <d:prop xmlns:oc="http://owncloud.org/ns" xmlns:nc="http://nextcloud.org/ns">
                <oc:id/>
                <oc:display-name/>
                <oc:user-visible/>
                <oc:user-assignable/>
                <oc:can-assign/>
                <nc:files-assigned/>
                <nc:reference-fileid/>
        </d:prop>
</d:propfind>

Example output:
  …
  <d:response>
    <d:href>/master/remote.php/dav/systemtags/84</d:href>
    <d:propstat>
      <d:prop>
        <oc:id>84</oc:id>
        <oc:display-name>Computer</oc:display-name>
        <oc:user-visible>true</oc:user-visible>
        <oc:user-assignable>true</oc:user-assignable>
        <oc:can-assign>true</oc:can-assign>
        <nc:files-assigned>42</nc:files-assigned>
        <nc:reference-fileid>924022</nc:reference-fileid>
      </d:prop>
      <d:status>HTTP/1.1 200 OK</d:status>
    </d:propstat>
  </d:response>
  <d:response>
    <d:href>/remote.php/dav/systemtags/97</d:href>
    <d:propstat>
      <d:prop>
        <oc:id>97</oc:id>
        <oc:display-name>Bear</oc:display-name>
        <oc:user-visible>true</oc:user-visible>
        <oc:user-assignable>true</oc:user-assignable>
        <oc:can-assign>true</oc:can-assign>
        <nc:files-assigned>1</nc:files-assigned>
        <nc:reference-fileid>923422</nc:reference-fileid>
      </d:prop>
      <d:status>HTTP/1.1 200 OK</d:status>
    </d:propstat>
  </d:response>
  …

Signed-off-by: Arthur Schiwon <blizzz@arthur-schiwon.de>

3 files changed, 98 insertions, 46 deletions

diff --git a/lib/private/Files/Cache/CacheQueryBuilder.php b/lib/private/Files/Cache/CacheQueryBuilder.php
index 496a8361d77..c5563750c4d 100644
--- a/lib/private/Files/Cache/CacheQueryBuilder.php
+++ b/lib/private/Files/Cache/CacheQueryBuilder.php
@@ -41,8 +41,28 @@ 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))
+			))
+			->where($this->expr()->like('systemtag.name', $this->createNamedParameter('_%')))
+			->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..c2eed5688b5 100644
--- a/lib/private/Files/Cache/QuerySearchHelper.php
+++ b/lib/private/Files/Cache/QuerySearchHelper.php
@@ -74,6 +74,41 @@ 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());
+		}
+	}
+
+	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 +162,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 +174,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) {
diff --git a/lib/private/Files/Node/Folder.php b/lib/private/Files/Node/Folder.php
index 2c376fe5885..e649e1efc28 100644
--- a/lib/private/Files/Node/Folder.php
+++ b/lib/private/Files/Node/Folder.php
@@ -204,7 +204,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,36 +212,17 @@ 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);
 	}
 
 	/**
-	 * search for files with the name matching $query
-	 *
-	 * @param string|ISearchQuery $query
-	 * @return \OC\Files\Node\Node[]
+	 * @psalm-return list{0: array<string, \OCP\Files\Cache\ICache>, 1: array<string, \OCP\Files\Mount\IMountPoint>}
 	 */
-	public function search($query) {
-		if (is_string($query)) {
-			$query = $this->queryFromOperator(new SearchComparison(ISearchComparison::COMPARE_LIKE, 'name', '%' . $query . '%'));
-		}
-
-		// search is handled by a single query covering all caches that this folder contains
-		// this is done by collect
-
-		$limitToHome = $query->limitToHome();
-		if ($limitToHome && count(explode('/', $this->path)) !== 3) {
-			throw new \InvalidArgumentException('searching by owner is only allowed in the users home folder');
-		}
-
+	protected function getCachesAndMountpointsForSearch(bool $limitToHome = false): array {
 		$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)];
@@ -262,12 +243,36 @@ class Folder extends Node implements \OCP\Files\Folder {
 			}
 		}
 
+		return [$caches, $mountByMountPoint];
+	}
+
+	/**
+	 * search for files with the name matching $query
+	 *
+	 * @param string|ISearchQuery $query
+	 * @return \OC\Files\Node\Node[]
+	 */
+	public function search($query) {
+		if (is_string($query)) {
+			$query = $this->queryFromOperator(new SearchComparison(ISearchComparison::COMPARE_LIKE, 'name', '%' . $query . '%'));
+		}
+
+		// search is handled by a single query covering all caches that this folder contains
+		// this is done by collect
+
+		$limitToHome = $query->limitToHome();
+		if ($limitToHome && count(explode('/', $this->path)) !== 3) {
+			throw new \InvalidArgumentException('searching by owner is only allowed in the users home folder');
+		}
+
+		[$caches, $mountByMountPoint] = $this->getCachesAndMountpointsForSearch($limitToHome);
+
 		/** @var QuerySearchHelper $searchHelper */
 		$searchHelper = \OC::$server->get(QuerySearchHelper::class);
 		$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);
@@ -333,6 +338,17 @@ class Folder extends Node implements \OCP\Files\Folder {
 	}
 
 	/**
+	 * @return Node[]
+	 */
+	public function getSystemTags(string $mediaType, int $limit = 0, int $offset = 0): array {
+		$query = $this->queryFromOperator(new SearchComparison(ISearchComparison::COMPARE_LIKE, 'mimetype', $mediaType . '/%'), null, $limit, $offset);
+		[$caches, ] = $this->getCachesAndMountpointsForSearch();
+		/** @var QuerySearchHelper $searchHelper */
+		$searchHelper = \OCP\Server::get(QuerySearchHelper::class);
+		return $searchHelper->findUsedTagsInCaches($query, $caches);
+	}
+
+	/**
 	 * @param int $id
 	 * @return \OC\Files\Node\Node[]
 	 */