Merge pull request #23601 from nextcloud/techdebt/noid/comments-manager-improvements

Comments manager improvements/extensions needed by Talk

3 files changed, 192 insertions, 14 deletions

diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 3c9be9828d1..1830fb00f3c 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -31,6 +31,7 @@ namespace OC\Comments;
 use Doctrine\DBAL\Exception\DriverException;
 use Doctrine\DBAL\Exception\InvalidFieldNameException;
 use OCA\Comments\AppInfo\Application;
+use OCP\AppFramework\Utility\ITimeFactory;
 use OCP\Comments\CommentsEvent;
 use OCP\Comments\IComment;
 use OCP\Comments\ICommentsEventHandler;
@@ -55,6 +56,9 @@ class Manager implements ICommentsManager {
 	/** @var IConfig */
 	protected $config;
 
+	/** @var ITimeFactory */
+	protected $timeFactory;
+
 	/** @var IInitialStateService */
 	protected $initialStateService;
 
@@ -73,10 +77,12 @@ class Manager implements ICommentsManager {
 	public function __construct(IDBConnection $dbConn,
 								LoggerInterface $logger,
 								IConfig $config,
+								ITimeFactory $timeFactory,
 								IInitialStateService $initialStateService) {
 		$this->dbConn = $dbConn;
 		$this->logger = $logger;
 		$this->config = $config;
+		$this->timeFactory = $timeFactory;
 		$this->initialStateService = $initialStateService;
 	}
 
@@ -395,6 +401,7 @@ class Manager implements ICommentsManager {
 	 * @param string $sortDirection direction of the comments (`asc` or `desc`)
 	 * @param int $limit optional, number of maximum comments to be returned. if
 	 * set to 0, all comments are returned.
+	 * @param bool $includeLastKnown
 	 * @return IComment[]
 	 * @return array
 	 */
@@ -403,7 +410,8 @@ class Manager implements ICommentsManager {
 		string $objectId,
 		int $lastKnownCommentId,
 		string $sortDirection = 'asc',
-		int $limit = 30
+		int $limit = 30,
+		bool $includeLastKnown = false
 	): array {
 		$comments = [];
 
@@ -427,6 +435,11 @@ class Manager implements ICommentsManager {
 		if ($lastKnownComment instanceof IComment) {
 			$lastKnownCommentDateTime = $lastKnownComment->getCreationDateTime();
 			if ($sortDirection === 'desc') {
+				if ($includeLastKnown) {
+					$idComparison = $query->expr()->lte('id', $query->createNamedParameter($lastKnownCommentId));
+				} else {
+					$idComparison = $query->expr()->lt('id', $query->createNamedParameter($lastKnownCommentId));
+				}
 				$query->andWhere(
 					$query->expr()->orX(
 						$query->expr()->lt(
@@ -440,11 +453,16 @@ class Manager implements ICommentsManager {
 								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
 								IQueryBuilder::PARAM_DATE
 							),
-							$query->expr()->lt('id', $query->createNamedParameter($lastKnownCommentId))
+							$idComparison
 						)
 					)
 				);
 			} else {
+				if ($includeLastKnown) {
+					$idComparison = $query->expr()->gte('id', $query->createNamedParameter($lastKnownCommentId));
+				} else {
+					$idComparison = $query->expr()->gt('id', $query->createNamedParameter($lastKnownCommentId));
+				}
 				$query->andWhere(
 					$query->expr()->orX(
 						$query->expr()->gt(
@@ -458,7 +476,7 @@ class Manager implements ICommentsManager {
 								$query->createNamedParameter($lastKnownCommentDateTime, IQueryBuilder::PARAM_DATE),
 								IQueryBuilder::PARAM_DATE
 							),
-							$query->expr()->gt('id', $query->createNamedParameter($lastKnownCommentId))
+							$idComparison
 						)
 					)
 				);
@@ -517,6 +535,25 @@ class Manager implements ICommentsManager {
 	 * @return IComment[]
 	 */
 	public function search(string $search, string $objectType, string $objectId, string $verb, int $offset, int $limit = 50): array {
+		$objectIds = [];
+		if ($objectId) {
+			$objectIds[] = $objectIds;
+		}
+		return $this->searchForObjects($search, $objectType, $objectIds, $verb, $offset, $limit);
+	}
+
+	/**
+	 * Search for comments on one or more objects with a given content
+	 *
+	 * @param string $search content to search for
+	 * @param string $objectType Limit the search by object type
+	 * @param array $objectIds Limit the search by object ids
+	 * @param string $verb Limit the verb of the comment
+	 * @param int $offset
+	 * @param int $limit
+	 * @return IComment[]
+	 */
+	public function searchForObjects(string $search, string $objectType, array $objectIds, string $verb, int $offset, int $limit = 50): array {
 		$query = $this->dbConn->getQueryBuilder();
 
 		$query->select('*')
@@ -531,8 +568,8 @@ class Manager implements ICommentsManager {
 		if ($objectType !== '') {
 			$query->andWhere($query->expr()->eq('object_type', $query->createNamedParameter($objectType)));
 		}
-		if ($objectId !== '') {
-			$query->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)));
+		if (!empty($objectIds)) {
+			$query->andWhere($query->expr()->in('object_id', $query->createNamedParameter($objectIds, IQueryBuilder::PARAM_STR_ARRAY)));
 		}
 		if ($verb !== '') {
 			$query->andWhere($query->expr()->eq('verb', $query->createNamedParameter($verb)));
@@ -588,6 +625,100 @@ class Manager implements ICommentsManager {
 	}
 
 	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param int $lastRead
+	 * @param string $verb
+	 * @return int
+	 * @since 21.0.0
+	 */
+	public function getNumberOfCommentsForObjectSinceComment(string $objectType, string $objectId, int $lastRead, string $verb = ''): int {
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select($query->func()->count('id', 'num_messages'))
+			->from('comments')
+			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
+			->andWhere($query->expr()->gt('id', $query->createNamedParameter($lastRead)));
+
+		if ($verb !== '') {
+			$query->andWhere($query->expr()->eq('verb', $query->createNamedParameter($verb)));
+		}
+
+		$result = $query->execute();
+		$data = $result->fetch();
+		$result->closeCursor();
+
+		return (int) ($data['num_messages'] ?? 0);
+	}
+
+	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param \DateTime $beforeDate
+	 * @param string $verb
+	 * @return int
+	 * @since 21.0.0
+	 */
+	public function getLastCommentBeforeDate(string $objectType, string $objectId, \DateTime $beforeDate, string $verb = ''): int {
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('id')
+			->from('comments')
+			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
+			->andWhere($query->expr()->lt('creation_timestamp', $query->createNamedParameter($beforeDate, IQueryBuilder::PARAM_DATE)))
+			->orderBy('creation_timestamp', 'desc');
+
+		if ($verb !== '') {
+			$query->andWhere($query->expr()->eq('verb', $query->createNamedParameter($verb)));
+		}
+
+		$result = $query->execute();
+		$data = $result->fetch();
+		$result->closeCursor();
+
+		return (int) ($data['id'] ?? 0);
+	}
+
+	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param string $verb
+	 * @param string $actorType
+	 * @param string[] $actors
+	 * @return \DateTime[] Map of "string actor" => "\DateTime most recent comment date"
+	 * @psalm-return array<string, \DateTime>
+	 * @since 21.0.0
+	 */
+	public function getLastCommentDateByActor(
+		string $objectType,
+		string $objectId,
+		string $verb,
+		string $actorType,
+		array $actors
+	): array {
+		$lastComments = [];
+
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('actor_id')
+			->selectAlias($query->createFunction('MAX(' . $query->getColumnName('creation_timestamp') . ')'), 'last_comment')
+			->from('comments')
+			->where($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->andWhere($query->expr()->eq('object_id', $query->createNamedParameter($objectId)))
+			->andWhere($query->expr()->eq('verb', $query->createNamedParameter($verb)))
+			->andWhere($query->expr()->eq('actor_type', $query->createNamedParameter($actorType)))
+			->andWhere($query->expr()->in('actor_id', $query->createNamedParameter($actors, IQueryBuilder::PARAM_STR_ARRAY)))
+			->groupBy('actor_id');
+
+		$result = $query->execute();
+		while ($row = $result->fetch()) {
+			$lastComments[$row['actor_id']] = $this->timeFactory->getDateTime($row['last_comment']);
+		}
+		$result->closeCursor();
+
+		return $lastComments;
+	}
+
+	/**
 	 * Get the number of unread comments for all files in a folder
 	 *
 	 * @param int $folderId
diff --git a/lib/private/Comments/ManagerFactory.php b/lib/private/Comments/ManagerFactory.php
index 44edf32cf9c..67b006bdb63 100644
--- a/lib/private/Comments/ManagerFactory.php
+++ b/lib/private/Comments/ManagerFactory.php
@@ -27,9 +27,7 @@ namespace OC\Comments;
 
 use OCP\Comments\ICommentsManager;
 use OCP\Comments\ICommentsManagerFactory;
-use OCP\IInitialStateService;
 use OCP\IServerContainer;
-use Psr\Log\LoggerInterface;
 
 class ManagerFactory implements ICommentsManagerFactory {
 
@@ -56,11 +54,6 @@ class ManagerFactory implements ICommentsManagerFactory {
 	 * @since 9.0.0
 	 */
 	public function getManager() {
-		return new Manager(
-			$this->serverContainer->getDatabaseConnection(),
-			$this->serverContainer->get(LoggerInterface::class),
-			$this->serverContainer->getConfig(),
-			$this->serverContainer->get(IInitialStateService::class)
-		);
+		return $this->serverContainer->get(Manager::class);
 	}
 }
diff --git a/lib/public/Comments/ICommentsManager.php b/lib/public/Comments/ICommentsManager.php
index 95f0d2ce300..b179b8a7464 100644
--- a/lib/public/Comments/ICommentsManager.php
+++ b/lib/public/Comments/ICommentsManager.php
@@ -128,6 +128,7 @@ interface ICommentsManager {
 	 * @param string $sortDirection direction of the comments (`asc` or `desc`)
 	 * @param int $limit optional, number of maximum comments to be returned. if
 	 * set to 0, all comments are returned.
+	 * @param bool $includeLastKnown
 	 * @return IComment[]
 	 * @since 14.0.0
 	 */
@@ -136,7 +137,8 @@ interface ICommentsManager {
 		string $objectId,
 		int $lastKnownCommentId,
 		string $sortDirection = 'asc',
-		int $limit = 30
+		int $limit = 30,
+		bool $includeLastKnown = false
 	): array;
 
 	/**
@@ -154,6 +156,20 @@ interface ICommentsManager {
 	public function search(string $search, string $objectType, string $objectId, string $verb, int $offset, int $limit = 50): array;
 
 	/**
+	 * Search for comments on one or more objects with a given content
+	 *
+	 * @param string $search content to search for
+	 * @param string $objectType Limit the search by object type
+	 * @param array $objectIds Limit the search by object ids
+	 * @param string $verb Limit the verb of the comment
+	 * @param int $offset
+	 * @param int $limit
+	 * @return IComment[]
+	 * @since 21.0.0
+	 */
+	public function searchForObjects(string $search, string $objectType, array $objectIds, string $verb, int $offset, int $limit = 50): array;
+
+	/**
 	 * @param $objectType string the object type, e.g. 'files'
 	 * @param $objectId string the id of the object
 	 * @param \DateTime|null $notOlderThan optional, timestamp of the oldest comments
@@ -165,6 +181,44 @@ interface ICommentsManager {
 	public function getNumberOfCommentsForObject($objectType, $objectId, \DateTime $notOlderThan = null, $verb = '');
 
 	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param int $lastRead
+	 * @param string $verb
+	 * @return int
+	 * @since 21.0.0
+	 */
+	public function getNumberOfCommentsForObjectSinceComment(string $objectType, string $objectId, int $lastRead, string $verb = ''): int;
+
+	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param \DateTime $beforeDate
+	 * @param string $verb
+	 * @return int
+	 * @since 21.0.0
+	 */
+	public function getLastCommentBeforeDate(string $objectType, string $objectId, \DateTime $beforeDate, string $verb = ''): int;
+
+	/**
+	 * @param string $objectType
+	 * @param string $objectId
+	 * @param string $verb
+	 * @param string $actorType
+	 * @param string[] $actors
+	 * @return \DateTime[] Map of "string actor" => "\DateTime most recent comment date"
+	 * @psalm-return array<string, \DateTime>
+	 * @since 21.0.0
+	 */
+	public function getLastCommentDateByActor(
+		string $objectType,
+		string $objectId,
+		string $verb,
+		string $actorType,
+		array $actors
+	): array;
+
+	/**
 	 * Get the number of unread comments for all files in a folder
 	 *
 	 * @param int $folderId