Merge pull request #30379 from nextcloud/feature/add-comments-reactions

Add comments reactions

12 files changed, 810 insertions, 15 deletions

diff --git a/apps/dav/tests/unit/Comments/CommentsNodeTest.php b/apps/dav/tests/unit/Comments/CommentsNodeTest.php
index 1738e1b53f9..f085ace9d89 100644
--- a/apps/dav/tests/unit/Comments/CommentsNodeTest.php
+++ b/apps/dav/tests/unit/Comments/CommentsNodeTest.php
@@ -404,6 +404,7 @@ class CommentsNodeTest extends \Test\TestCase {
 			$ns . 'objectId' => '1848',
 			$ns . 'referenceId' => 'ref',
 			$ns . 'isUnread' => null,
+			$ns . 'reactions' => [],
 		];
 
 		$this->commentsManager->expects($this->exactly(2))
diff --git a/build/psalm-baseline.xml b/build/psalm-baseline.xml
index 2c85f506325..80198e702ea 100644
--- a/build/psalm-baseline.xml
+++ b/build/psalm-baseline.xml
@@ -4382,10 +4382,6 @@
     </TypeDoesNotContainType>
   </file>
   <file src="lib/private/Repair/RemoveLinkShares.php">
-    <ImplicitToStringCast occurrences="2">
-      <code>$query-&gt;createFunction('(' . $subQuery-&gt;getSQL() . ')')</code>
-      <code>$subQuery-&gt;createFunction('(' . $subSubQuery-&gt;getSQL() . ')')</code>
-    </ImplicitToStringCast>
     <InvalidPropertyAssignmentValue occurrences="1">
       <code>$this-&gt;userToNotify</code>
     </InvalidPropertyAssignmentValue>
diff --git a/core/Migrations/Version24000Date20211222112246.php b/core/Migrations/Version24000Date20211222112246.php
new file mode 100644
index 00000000000..a265bebcd86
--- /dev/null
+++ b/core/Migrations/Version24000Date20211222112246.php
@@ -0,0 +1,96 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright 2021 Vitor Mattos <vitor@php.rio>
+ *
+ * @author Vitor Mattos <vitor@php.rio>
+ *
+ * @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 <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OC\Core\Migrations;
+
+use Closure;
+use OCP\DB\ISchemaWrapper;
+use OCP\DB\Types;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+class Version24000Date20211222112246 extends SimpleMigrationStep {
+	private const TABLE_NAME = 'reactions';
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		/** @var ISchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		$comments = $schema->getTable('comments');
+		if (!$comments->hasColumn('reactions')) {
+			$comments->addColumn('reactions', Types::STRING, [
+				'notnull' => false,
+				'length' => 4000,
+			]);
+		}
+
+		if (!$schema->hasTable(self::TABLE_NAME)) {
+			$table = $schema->createTable(self::TABLE_NAME);
+			$table->addColumn('id', Types::BIGINT, [
+				'autoincrement' => true,
+				'notnull' => true,
+				'length' => 11,
+				'unsigned' => true,
+			]);
+			$table->addColumn('parent_id', Types::BIGINT, [
+				'notnull' => true,
+				'length' => 11,
+				'unsigned' => true,
+			]);
+			$table->addColumn('message_id', Types::BIGINT, [
+				'notnull' => true,
+				'length' => 11,
+				'unsigned' => true,
+			]);
+			$table->addColumn('actor_type', Types::STRING, [
+				'notnull' => true,
+				'length' => 64,
+				'default' => '',
+			]);
+			$table->addColumn('actor_id', Types::STRING, [
+				'notnull' => true,
+				'length' => 64,
+				'default' => '',
+			]);
+			$table->addColumn('reaction', Types::STRING, [
+				'notnull' => true,
+				'length' => 32,
+			]);
+			$table->setPrimaryKey(['id']);
+			$table->addIndex(['reaction'], 'comment_reaction');
+			$table->addIndex(['parent_id'], 'comment_reaction_parent_id');
+			$table->addUniqueIndex(['parent_id', 'actor_type', 'actor_id', 'reaction'], 'comment_reaction_unique');
+			return $schema;
+		}
+		return null;
+	}
+}
diff --git a/lib/composer/composer/autoload_classmap.php b/lib/composer/composer/autoload_classmap.php
index d8d8dc0fb6b..73aaa10f731 100644
--- a/lib/composer/composer/autoload_classmap.php
+++ b/lib/composer/composer/autoload_classmap.php
@@ -992,6 +992,7 @@ return array(
     'OC\\Core\\Migrations\\Version23000Date20211203110726' => $baseDir . '/core/Migrations/Version23000Date20211203110726.php',
     'OC\\Core\\Migrations\\Version23000Date20211213203940' => $baseDir . '/core/Migrations/Version23000Date20211213203940.php',
     'OC\\Core\\Migrations\\Version24000Date20211210141942' => $baseDir . '/core/Migrations/Version24000Date20211210141942.php',
+    'OC\\Core\\Migrations\\Version24000Date20211222112246' => $baseDir . '/core/Migrations/Version24000Date20211222112246.php',
     'OC\\Core\\Migrations\\Version24000Date20211230140012' => $baseDir . '/core/Migrations/Version24000Date20211230140012.php',
     'OC\\Core\\Notification\\CoreNotifier' => $baseDir . '/core/Notification/CoreNotifier.php',
     'OC\\Core\\Service\\LoginFlowV2Service' => $baseDir . '/core/Service/LoginFlowV2Service.php',
diff --git a/lib/composer/composer/autoload_static.php b/lib/composer/composer/autoload_static.php
index a9ce260d050..96e70bed1a3 100644
--- a/lib/composer/composer/autoload_static.php
+++ b/lib/composer/composer/autoload_static.php
@@ -1021,6 +1021,7 @@ class ComposerStaticInit53792487c5a8370acc0b06b1a864ff4c
         'OC\\Core\\Migrations\\Version23000Date20211203110726' => __DIR__ . '/../../..' . '/core/Migrations/Version23000Date20211203110726.php',
         'OC\\Core\\Migrations\\Version23000Date20211213203940' => __DIR__ . '/../../..' . '/core/Migrations/Version23000Date20211213203940.php',
         'OC\\Core\\Migrations\\Version24000Date20211210141942' => __DIR__ . '/../../..' . '/core/Migrations/Version24000Date20211210141942.php',
+        'OC\\Core\\Migrations\\Version24000Date20211222112246' => __DIR__ . '/../../..' . '/core/Migrations/Version24000Date20211222112246.php',
         'OC\\Core\\Migrations\\Version24000Date20211230140012' => __DIR__ . '/../../..' . '/core/Migrations/Version24000Date20211230140012.php',
         'OC\\Core\\Notification\\CoreNotifier' => __DIR__ . '/../../..' . '/core/Notification/CoreNotifier.php',
         'OC\\Core\\Service\\LoginFlowV2Service' => __DIR__ . '/../../..' . '/core/Service/LoginFlowV2Service.php',
diff --git a/lib/private/Comments/Comment.php b/lib/private/Comments/Comment.php
index 5cf04092101..2b338efc75f 100644
--- a/lib/private/Comments/Comment.php
+++ b/lib/private/Comments/Comment.php
@@ -44,6 +44,7 @@ class Comment implements IComment {
 		'referenceId' => null,
 		'creationDT' => null,
 		'latestChildDT' => null,
+		'reactions' => null,
 	];
 
 	/**
@@ -431,6 +432,21 @@ class Comment implements IComment {
 	}
 
 	/**
+	 * @inheritDoc
+	 */
+	public function getReactions(): array {
+		return $this->data['reactions'] ?? [];
+	}
+
+	/**
+	 * @inheritDoc
+	 */
+	public function setReactions(?array $reactions): IComment {
+		$this->data['reactions'] = $reactions;
+		return $this;
+	}
+
+	/**
 	 * sets the comment data based on an array with keys as taken from the
 	 * database.
 	 *
diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index d62db2926fb..e87ac5cd5cc 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -41,6 +41,7 @@ use OCP\IConfig;
 use OCP\IDBConnection;
 use OCP\IUser;
 use OCP\IInitialStateService;
+use OCP\PreConditionNotMetException;
 use OCP\Util;
 use Psr\Log\LoggerInterface;
 
@@ -102,6 +103,20 @@ class Manager implements ICommentsManager {
 		}
 		$data['children_count'] = (int)$data['children_count'];
 		$data['reference_id'] = $data['reference_id'] ?? null;
+		if ($this->supportReactions()) {
+			$list = json_decode($data['reactions'], true);
+			// Ordering does not work on the database with group concat and Oracle,
+			// So we simply sort on the output.
+			if (is_array($list)) {
+				uasort($list, static function ($a, $b) {
+					if ($a === $b) {
+						return 0;
+					}
+					return ($a > $b) ? -1 : 1;
+				});
+			}
+			$data['reactions'] = $list;
+		}
 		return $data;
 	}
 
@@ -133,6 +148,10 @@ class Manager implements ICommentsManager {
 			throw new \UnexpectedValueException('Actor, Object and Verb information must be provided for saving');
 		}
 
+		if ($comment->getVerb() === 'reaction' && mb_strlen($comment->getMessage()) > 2) {
+			throw new \UnexpectedValueException('Reactions cannot be longer than 2 chars (emoji with skin tone have two chars)');
+		}
+
 		if ($comment->getId() === '') {
 			$comment->setChildrenCount(0);
 			$comment->setLatestChildDateTime(new \DateTime('0000-00-00 00:00:00', new \DateTimeZone('UTC')));
@@ -899,12 +918,166 @@ class Manager implements ICommentsManager {
 		}
 
 		if ($affectedRows > 0 && $comment instanceof IComment) {
+			if ($comment->getVerb() === 'reaction_deleted') {
+				$this->deleteReaction($comment);
+			}
 			$this->sendEvent(CommentsEvent::EVENT_DELETE, $comment);
 		}
 
 		return ($affectedRows > 0);
 	}
 
+	private function deleteReaction(IComment $reaction): void {
+		$qb = $this->dbConn->getQueryBuilder();
+		$qb->delete('reactions')
+			->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($reaction->getParentId())))
+			->andWhere($qb->expr()->eq('message_id', $qb->createNamedParameter($reaction->getId())))
+			->executeStatement();
+		$this->sumReactions($reaction->getParentId());
+	}
+
+	/**
+	 * Get comment related with user reaction
+	 *
+	 * Throws PreConditionNotMetException when the system haven't the minimum requirements to
+	 * use reactions
+	 *
+	 * @param integer $parentId
+	 * @param string $actorType
+	 * @param string $actorId
+	 * @param string $reaction
+	 * @return IComment
+	 * @throws NotFoundException
+	 * @throws PreConditionNotMetException
+	 * @since 24.0.0
+	 */
+	public function getReactionComment(int $parentId, string $actorType, string $actorId, string $reaction): IComment {
+		$this->throwIfNotSupportReactions();
+		$qb = $this->dbConn->getQueryBuilder();
+		$messageId = $qb
+			->select('message_id')
+			->from('reactions')
+			->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($parentId)))
+			->andWhere($qb->expr()->eq('actor_type', $qb->createNamedParameter($actorType)))
+			->andWhere($qb->expr()->eq('actor_id', $qb->createNamedParameter($actorId)))
+			->andWhere($qb->expr()->eq('reaction', $qb->createNamedParameter($reaction)))
+			->executeQuery()
+			->fetchOne();
+		if (!$messageId) {
+			throw new NotFoundException('Comment related with reaction not found');
+		}
+		return $this->get($messageId);
+	}
+
+	/**
+	 * Retrieve all reactions with specific reaction of a message
+	 *
+	 * @param integer $parentId
+	 * @param string $reaction
+	 * @return IComment[]
+	 * @since 24.0.0
+	 */
+	public function retrieveAllReactionsWithSpecificReaction(int $parentId, string $reaction): ?array {
+		$this->throwIfNotSupportReactions();
+		$qb = $this->dbConn->getQueryBuilder();
+		$result = $qb
+			->select('message_id')
+			->from('reactions')
+			->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($parentId)))
+			->andWhere($qb->expr()->eq('reaction', $qb->createNamedParameter($reaction)))
+			->executeQuery();
+
+		$commentIds = [];
+		while ($data = $result->fetch()) {
+			$commentIds[] = $data['message_id'];
+		}
+		$comments = [];
+		if ($commentIds) {
+			$comments = $this->getCommentsById($commentIds);
+		}
+
+		return $comments;
+	}
+
+	/**
+	 * Support reactions
+	 *
+	 * @return boolean
+	 * @since 24.0.0
+	 */
+	public function supportReactions(): bool {
+		return $this->dbConn->supports4ByteText();
+	}
+
+	/**
+	 * @throws PreConditionNotMetException
+	 * @since 24.0.0
+	 */
+	private function throwIfNotSupportReactions() {
+		if (!$this->supportReactions()) {
+			throw new PreConditionNotMetException('The database does not support reactions');
+		}
+	}
+
+	/**
+	 * Retrieve all reactions of a message
+	 *
+	 * Throws PreConditionNotMetException when the system haven't the minimum requirements to
+	 * use reactions
+	 *
+	 * @param integer $parentId
+	 * @param string $reaction
+	 * @throws PreConditionNotMetException
+	 * @return IComment[]
+	 * @since 24.0.0
+	 */
+	public function retrieveAllReactions(int $parentId): array {
+		$this->throwIfNotSupportReactions();
+		$qb = $this->dbConn->getQueryBuilder();
+		$result = $qb
+			->select('message_id')
+			->from('reactions')
+			->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($parentId)))
+			->executeQuery();
+
+		$commentIds = [];
+		while ($data = $result->fetch()) {
+			$commentIds[] = $data['message_id'];
+		}
+
+		return $this->getCommentsById($commentIds);
+	}
+
+	/**
+	 * Get all comments on list
+	 *
+	 * @param integer[] $commentIds
+	 * @return IComment[]
+	 * @since 24.0.0
+	 */
+	private function getCommentsById(array $commentIds): array {
+		if (!$commentIds) {
+			return [];
+		}
+		$query = $this->dbConn->getQueryBuilder();
+
+		$query->select('*')
+			->from('comments')
+			->where($query->expr()->in('id', $query->createNamedParameter($commentIds, IQueryBuilder::PARAM_STR_ARRAY)))
+			->orderBy('creation_timestamp', 'DESC')
+			->addOrderBy('id', 'DESC');
+
+		$comments = [];
+		$result = $query->executeQuery();
+		while ($data = $result->fetch()) {
+			$comment = $this->getCommentFromData($data);
+			$this->cache($comment);
+			$comments[] = $comment;
+		}
+		$result->closeCursor();
+		return $comments;
+	}
+
 	/**
 	 * saves the comment permanently
 	 *
@@ -916,12 +1089,20 @@ class Manager implements ICommentsManager {
 	 * Throws NotFoundException when a comment that is to be updated does not
 	 * exist anymore at this point of time.
 	 *
+	 * Throws PreConditionNotMetException when the system haven't the minimum requirements to
+	 * use reactions
+	 *
 	 * @param IComment $comment
 	 * @return bool
 	 * @throws NotFoundException
+	 * @throws PreConditionNotMetException
 	 * @since 9.0.0
 	 */
 	public function save(IComment $comment) {
+		if ($comment->getVerb() === 'reaction') {
+			$this->throwIfNotSupportReactions();
+		}
+
 		if ($this->prepareCommentForDatabaseWrite($comment)->getId() === '') {
 			$result = $this->insert($comment);
 		} else {
@@ -988,12 +1169,88 @@ class Manager implements ICommentsManager {
 
 		if ($affectedRows > 0) {
 			$comment->setId((string)$qb->getLastInsertId());
+			if ($comment->getVerb() === 'reaction') {
+				$this->addReaction($comment);
+			}
 			$this->sendEvent(CommentsEvent::EVENT_ADD, $comment);
 		}
 
 		return $affectedRows > 0;
 	}
 
+	private function addReaction(IComment $reaction): void {
+		// Prevent violate constraint
+		$qb = $this->dbConn->getQueryBuilder();
+		$qb->select($qb->func()->count('*'))
+			->from('reactions')
+			->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($reaction->getParentId())))
+			->andWhere($qb->expr()->eq('actor_type', $qb->createNamedParameter($reaction->getActorType())))
+			->andWhere($qb->expr()->eq('actor_id', $qb->createNamedParameter($reaction->getActorId())))
+			->andWhere($qb->expr()->eq('reaction', $qb->createNamedParameter($reaction->getMessage())));
+		$result = $qb->executeQuery();
+		$exists = (int) $result->fetchOne();
+		if (!$exists) {
+			$qb = $this->dbConn->getQueryBuilder();
+			try {
+				$qb->insert('reactions')
+					->values([
+						'parent_id' => $qb->createNamedParameter($reaction->getParentId()),
+						'message_id' => $qb->createNamedParameter($reaction->getId()),
+						'actor_type' => $qb->createNamedParameter($reaction->getActorType()),
+						'actor_id' => $qb->createNamedParameter($reaction->getActorId()),
+						'reaction' => $qb->createNamedParameter($reaction->getMessage()),
+					])
+					->executeStatement();
+			} catch (\Exception $e) {
+				$this->logger->error($e->getMessage(), [
+					'exception' => $e,
+					'app' => 'core_comments',
+				]);
+			}
+		}
+		$this->sumReactions($reaction->getParentId());
+	}
+
+	private function sumReactions(string $parentId): void {
+		$qb = $this->dbConn->getQueryBuilder();
+
+		$totalQuery = $this->dbConn->getQueryBuilder();
+		$totalQuery
+			->selectAlias(
+				$totalQuery->func()->concat(
+					$totalQuery->expr()->literal('"'),
+					'reaction',
+					$totalQuery->expr()->literal('":'),
+					$totalQuery->func()->count('id')
+				),
+				'colonseparatedvalue'
+			)
+			->selectAlias($totalQuery->func()->count('id'), 'total')
+			->from('reactions', 'r')
+			->where($totalQuery->expr()->eq('r.parent_id', $qb->createNamedParameter($parentId)))
+			->groupBy('r.reaction')
+			->orderBy('total', 'DESC')
+			->setMaxResults(20);
+
+		$jsonQuery = $this->dbConn->getQueryBuilder();
+		$jsonQuery
+			->selectAlias(
+				$jsonQuery->func()->concat(
+					$jsonQuery->expr()->literal('{'),
+					$jsonQuery->func()->groupConcat('colonseparatedvalue'),
+					$jsonQuery->expr()->literal('}')
+				),
+				'json'
+			)
+			->from($jsonQuery->createFunction('(' . $totalQuery->getSQL() . ')'), 'json');
+
+		$qb
+			->update('comments')
+			->set('reactions', $jsonQuery->createFunction('(' . $jsonQuery->getSQL() . ')'))
+			->where($qb->expr()->eq('id', $qb->createNamedParameter($parentId)))
+			->executeStatement();
+	}
+
 	/**
 	 * updates a Comment data row
 	 *
@@ -1015,6 +1272,10 @@ class Manager implements ICommentsManager {
 			$result = $this->updateQuery($comment, false);
 		}
 
+		if ($comment->getVerb() === 'reaction_deleted') {
+			$this->deleteReaction($comment);
+		}
+
 		$this->sendEvent(CommentsEvent::EVENT_UPDATE, $comment);
 
 		return $result;
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index a362ff8016e..de326a2a317 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -694,7 +694,7 @@ class QueryBuilder implements IQueryBuilder {
 	 *         ->from('users', 'u')
 	 * </code>
 	 *
-	 * @param string $from The table.
+	 * @param string|IQueryFunction $from The table.
 	 * @param string|null $alias The alias of the table.
 	 *
 	 * @return $this This QueryBuilder instance.
@@ -1303,7 +1303,7 @@ class QueryBuilder implements IQueryBuilder {
 	/**
 	 * Returns the table name quoted and with database prefix as needed by the implementation
 	 *
-	 * @param string $table
+	 * @param string|IQueryFunction $table
 	 * @return string
 	 */
 	public function getTableName($table) {
diff --git a/lib/public/Comments/IComment.php b/lib/public/Comments/IComment.php
index b9747aefb5b..8465eaf49f4 100644
--- a/lib/public/Comments/IComment.php
+++ b/lib/public/Comments/IComment.php
@@ -278,4 +278,25 @@ interface IComment {
 	 * @since 19.0.0
 	 */
 	public function setReferenceId(?string $referenceId): IComment;
+
+	/**
+	 * Returns the reactions array if exists
+	 *
+	 * The keys is the emoji of reaction and the value is the total.
+	 *
+	 * @return array<string, integer> e.g. ["👍":1]
+	 * @since 24.0.0
+	 */
+	public function getReactions(): array;
+
+	/**
+	 * Set summarized array of reactions by reaction type
+	 *
+	 * The keys is the emoji of reaction and the value is the total.
+	 *
+	 * @param array<string, integer>|null $reactions e.g. ["👍":1]
+	 * @return IComment
+	 * @since 24.0.0
+	 */
+	public function setReactions(?array $reactions): IComment;
 }
diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php
index 7829696970c..669003246d9 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -470,7 +470,7 @@ interface IQueryBuilder {
 	 *         ->from('users', 'u')
 	 * </code>
 	 *
-	 * @param string $from The table.
+	 * @param string|IQueryFunction $from The table.
 	 * @param string|null $alias The alias of the table.
 	 *
 	 * @return $this This QueryBuilder instance.
@@ -994,7 +994,7 @@ interface IQueryBuilder {
 	/**
 	 * Returns the table name quoted and with database prefix as needed by the implementation
 	 *
-	 * @param string $table
+	 * @param string|IQueryFunction $table
 	 * @return string
 	 * @since 9.0.0
 	 */
diff --git a/tests/lib/Comments/ManagerTest.php b/tests/lib/Comments/ManagerTest.php
index 355b1af1347..23a9346909a 100644
--- a/tests/lib/Comments/ManagerTest.php
+++ b/tests/lib/Comments/ManagerTest.php
@@ -32,6 +32,8 @@ class ManagerTest extends TestCase {
 
 		$sql = $this->connection->getDatabasePlatform()->getTruncateTableSQL('`*PREFIX*comments`');
 		$this->connection->prepare($sql)->execute();
+		$sql = $this->connection->getDatabasePlatform()->getTruncateTableSQL('`*PREFIX*reactions`');
+		$this->connection->prepare($sql)->execute();
 	}
 
 	protected function addDatabaseEntry($parentId, $topmostParentId, $creationDT = null, $latestChildDT = null, $objectId = null) {
@@ -469,14 +471,21 @@ class ManagerTest extends TestCase {
 		$manager->get($id);
 	}
 
-	public function testSaveNew() {
+	/**
+	 * @dataProvider providerTestSave
+	 */
+	public function testSave(string $message, string $actorId, string $verb, ?string $parentId, ?string $id = ''): IComment {
 		$manager = $this->getManager();
 		$comment = new Comment();
 		$comment
-			->setActor('users', 'alice')
+			->setId($id)
+			->setActor('users', $actorId)
 			->setObject('files', 'file64')
-			->setMessage('very beautiful, I am impressed!')
-			->setVerb('comment');
+			->setMessage($message)
+			->setVerb($verb);
+		if ($parentId) {
+			$comment->setParentId($parentId);
+		}
 
 		$saveSuccessful = $manager->save($comment);
 		$this->assertTrue($saveSuccessful);
@@ -487,6 +496,13 @@ class ManagerTest extends TestCase {
 		$loadedComment = $manager->get($comment->getId());
 		$this->assertSame($comment->getMessage(), $loadedComment->getMessage());
 		$this->assertEquals($comment->getCreationDateTime()->getTimestamp(), $loadedComment->getCreationDateTime()->getTimestamp());
+		return $comment;
+	}
+
+	public function providerTestSave(): array {
+		return [
+			['very beautiful, I am impressed!', 'alice', 'comment', null]
+		];
 	}
 
 	public function testSaveUpdate() {
@@ -859,6 +875,77 @@ class ManagerTest extends TestCase {
 		$this->assertTrue(is_string($manager->resolveDisplayName('planet', 'neptune')));
 	}
 
+	private function skipIfNotSupport4ByteUTF() {
+		if (!$this->getManager()->supportReactions()) {
+			$this->markTestSkipped('MySQL doesn\'t support 4 byte UTF-8');
+		}
+	}
+
+	/**
+	 * @dataProvider providerTestReactionAddAndDelete
+	 *
+	 * @param IComment[] $comments
+	 * @param array $reactionsExpected
+	 * @return void
+	 */
+	public function testReactionAddAndDelete(array $comments, array $reactionsExpected) {
+		$this->skipIfNotSupport4ByteUTF();
+		$manager = $this->getManager();
+
+		$processedComments = $this->proccessComments($comments);
+		$comment = end($processedComments);
+		if ($comment->getParentId()) {
+			$parent = $manager->get($comment->getParentId());
+			$this->assertEqualsCanonicalizing($reactionsExpected, $parent->getReactions());
+		}
+	}
+
+	public function providerTestReactionAddAndDelete(): array {
+		return[
+			[
+				[
+					['message', 'alice', 'comment', null],
+				], [],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+				], ['👍' => 1],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'alice', 'reaction', 'message#alice'],
+				], ['👍' => 1],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+				], ['👍' => 2],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction_deleted', 'message#alice'],
+				], ['👍' => 1],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+					['👍', 'alice', 'reaction_deleted', 'message#alice'],
+					['👍', 'frank', 'reaction_deleted', 'message#alice'],
+				], [],
+			],
+		];
+	}
 
 	public function testResolveDisplayNameInvalidType() {
 		$this->expectException(\InvalidArgumentException::class);
@@ -872,4 +959,305 @@ class ManagerTest extends TestCase {
 		$manager->registerDisplayNameResolver('planet', $planetClosure);
 		$this->assertTrue(is_string($manager->resolveDisplayName(1337, 'neptune')));
 	}
+
+	/**
+	 * @param array $data
+	 * @return IComment[]
+	 */
+	private function proccessComments(array $data): array {
+		/** @var IComment[] */
+		$comments = [];
+		foreach ($data as $comment) {
+			[$message, $actorId, $verb, $parentText] = $comment;
+			$parentId = null;
+			if ($parentText) {
+				$parentId = (string) $comments[$parentText]->getId();
+			}
+			$id = '';
+			if ($verb === 'reaction_deleted') {
+				$id = $comments[$message . '#' . $actorId]->getId();
+			}
+			$comment = $this->testSave($message, $actorId, $verb, $parentId, $id);
+			$comments[$comment->getMessage() . '#' . $comment->getActorId()] = $comment;
+		}
+		return $comments;
+	}
+
+	/**
+	 * @dataProvider providerTestRetrieveAllReactions
+	 */
+	public function testRetrieveAllReactions(array $comments, array $expected) {
+		$this->skipIfNotSupport4ByteUTF();
+		$manager = $this->getManager();
+
+		$processedComments = $this->proccessComments($comments);
+		$comment = reset($processedComments);
+		$all = $manager->retrieveAllReactions($comment->getId());
+		$actual = array_map(function ($row) {
+			return [
+				'message' => $row->getMessage(),
+				'actorId' => $row->getActorId(),
+			];
+		}, $all);
+		$this->assertEqualsCanonicalizing($expected, $actual);
+	}
+
+	public function providerTestRetrieveAllReactions(): array {
+		return [
+			[
+				[
+					['message', 'alice', 'comment', null],
+				],
+				[],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+				],
+				[
+					['👍', 'alice'],
+					['👍', 'frank'],
+				],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+				],
+				[
+					['👍', 'alice'],
+					['👍', 'frank'],
+				],
+			],
+		];
+	}
+
+	/**
+	 * @dataProvider providerTestRetrieveAllReactionsWithSpecificReaction
+	 */
+	public function testRetrieveAllReactionsWithSpecificReaction(array $comments, string $reaction, array $expected) {
+		$this->skipIfNotSupport4ByteUTF();
+		$manager = $this->getManager();
+
+		$processedComments = $this->proccessComments($comments);
+		$comment = reset($processedComments);
+		$all = $manager->retrieveAllReactionsWithSpecificReaction($comment->getId(), $reaction);
+		$actual = array_map(function ($row) {
+			return [
+				'message' => $row->getMessage(),
+				'actorId' => $row->getActorId(),
+			];
+		}, $all);
+		$this->assertEqualsCanonicalizing($expected, $actual);
+	}
+
+	public function providerTestRetrieveAllReactionsWithSpecificReaction(): array {
+		return [
+			[
+				[
+					['message', 'alice', 'comment', null],
+				],
+				'👎',
+				[],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+				],
+				'👍',
+				[
+					['👍', 'alice'],
+					['👍', 'frank'],
+				],
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+					['👎', 'alice', 'reaction', 'message#alice'],
+					['👍', 'frank', 'reaction', 'message#alice'],
+				],
+				'👎',
+				[
+					['👎', 'alice'],
+				],
+			],
+		];
+	}
+
+	/**
+	 * @dataProvider providerTestGetReactionComment
+	 */
+	public function testGetReactionComment(array $comments, array $expected, bool $notFound) {
+		$this->skipIfNotSupport4ByteUTF();
+		$manager = $this->getManager();
+
+		$processedComments = $this->proccessComments($comments);
+
+		$keys = ['message', 'actorId', 'verb', 'parent'];
+		$expected = array_combine($keys, $expected);
+
+		if ($notFound) {
+			$this->expectException(\OCP\Comments\NotFoundException::class);
+		}
+		$comment = $processedComments[$expected['message'] . '#' . $expected['actorId']];
+		$actual = $manager->getReactionComment($comment->getParentId(), $comment->getActorType(), $comment->getActorId(), $comment->getMessage());
+		if (!$notFound) {
+			$this->assertEquals($expected['message'], $actual->getMessage());
+			$this->assertEquals($expected['actorId'], $actual->getActorId());
+			$this->assertEquals($expected['verb'], $actual->getVerb());
+			$this->assertEquals($processedComments[$expected['parent']]->getId(), $actual->getParentId());
+		}
+	}
+
+	public function providerTestGetReactionComment(): array {
+		return [
+			[
+				[
+					['message', 'Matthew', 'comment', null],
+					['👍', 'Matthew', 'reaction', 'message#Matthew'],
+					['👍', 'Mark', 'reaction', 'message#Matthew'],
+					['👍', 'Luke', 'reaction', 'message#Matthew'],
+					['👍', 'John', 'reaction', 'message#Matthew'],
+				],
+				['👍', 'Matthew', 'reaction', 'message#Matthew'],
+				false,
+			],
+			[
+				[
+					['message', 'Matthew', 'comment', null],
+					['👍', 'Matthew', 'reaction', 'message#Matthew'],
+					['👍', 'Mark', 'reaction', 'message#Matthew'],
+					['👍', 'Luke', 'reaction', 'message#Matthew'],
+					['👍', 'John', 'reaction', 'message#Matthew'],
+				],
+				['👍', 'Mark', 'reaction', 'message#Matthew'],
+				false,
+			],
+			[
+				[
+					['message', 'Matthew', 'comment', null],
+					['👎', 'Matthew', 'reaction', 'message#Matthew'],
+				],
+				['👎', 'Matthew', 'reaction', 'message#Matthew'],
+				false,
+			],
+			[
+				[
+					['message', 'Matthew', 'comment', null],
+					['👎', 'Matthew', 'reaction', 'message#Matthew'],
+					['👎', 'Matthew', 'reaction_deleted', 'message#Matthew'],
+				],
+				['👎', 'Matthew', 'reaction', 'message#Matthew'],
+				true,
+			],
+		];
+	}
+
+	/**
+	 * @dataProvider providerTestReactionMessageSize
+	 */
+	public function testReactionMessageSize($reactionString, $valid) {
+		$this->skipIfNotSupport4ByteUTF();
+		if (!$valid) {
+			$this->expectException(\UnexpectedValueException::class);
+		}
+
+		$manager = $this->getManager();
+		$comment = new Comment();
+		$comment->setMessage($reactionString)
+			->setVerb('reaction')
+			->setActor('users', 'alice')
+			->setObject('files', 'file64');
+		$status = $manager->save($comment);
+		$this->assertTrue($status);
+	}
+
+	public function providerTestReactionMessageSize(): array {
+		return [
+			['a', true],
+			['1', true],
+			['12', true],
+			['123', false],
+			['👍', true],
+			['👍👍', true],
+			['👍🏽', true],
+			['👍🏽👍', false],
+			['👍🏽👍🏽', false],
+		];
+	}
+
+	/**
+	 * @dataProvider providerTestReactionsSummarizeOrdered
+	 */
+	public function testReactionsSummarizeOrdered(array $comments, array $expected, bool $isFullMatch) {
+		$this->skipIfNotSupport4ByteUTF();
+		$manager = $this->getManager();
+
+
+		$processedComments = $this->proccessComments($comments);
+		$comment = end($processedComments);
+		$actual = $manager->get($comment->getParentId());
+
+		if ($isFullMatch) {
+			$this->assertSame($expected, $actual->getReactions());
+		} else {
+			$subResult = array_slice($actual->getReactions(), 0, count($expected));
+			$this->assertSame($expected, $subResult);
+		}
+	}
+
+	public function providerTestReactionsSummarizeOrdered(): array {
+		return [
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👍', 'alice', 'reaction', 'message#alice'],
+				],
+				['👍' => 1],
+				true,
+			],
+			[
+				[
+					['message', 'alice', 'comment', null],
+					['👎', 'John', 'reaction', 'message#alice'],
+					['💼', 'Luke', 'reaction', 'message#alice'],
+					['📋', 'Luke', 'reaction', 'message#alice'],
+					['🚀', 'Luke', 'reaction', 'message#alice'],
+					['🖤', 'Luke', 'reaction', 'message#alice'],
+					['😜', 'Luke', 'reaction', 'message#alice'],
+					['🌖', 'Luke', 'reaction', 'message#alice'],
+					['💖', 'Luke', 'reaction', 'message#alice'],
+					['📥', 'Luke', 'reaction', 'message#alice'],
+					['🐉', 'Luke', 'reaction', 'message#alice'],
+					['☕', 'Luke', 'reaction', 'message#alice'],
+					['🐄', 'Luke', 'reaction', 'message#alice'],
+					['🐕', 'Luke', 'reaction', 'message#alice'],
+					['🐈', 'Luke', 'reaction', 'message#alice'],
+					['🛂', 'Luke', 'reaction', 'message#alice'],
+					['🕸', 'Luke', 'reaction', 'message#alice'],
+					['🏰', 'Luke', 'reaction', 'message#alice'],
+					['⚙️', 'Luke', 'reaction', 'message#alice'],
+					['🚨', 'Luke', 'reaction', 'message#alice'],
+					['👥', 'Luke', 'reaction', 'message#alice'],
+					['👍', 'Paul', 'reaction', 'message#alice'],
+					['👍', 'Peter', 'reaction', 'message#alice'],
+					['💜', 'Matthew', 'reaction', 'message#alice'],
+					['💜', 'Mark', 'reaction', 'message#alice'],
+					['💜', 'Luke', 'reaction', 'message#alice'],
+				],
+				[
+					'💜' => 3,
+					'👍' => 2,
+				],
+				false,
+			],
+		];
+	}
 }
diff --git a/tests/lib/DB/QueryBuilder/QueryBuilderTest.php b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php
index 19278504707..fab080eec86 100644
--- a/tests/lib/DB/QueryBuilder/QueryBuilderTest.php
+++ b/tests/lib/DB/QueryBuilder/QueryBuilderTest.php
@@ -28,6 +28,7 @@ use OC\DB\QueryBuilder\Literal;
 use OC\DB\QueryBuilder\Parameter;
 use OC\DB\QueryBuilder\QueryBuilder;
 use OC\SystemConfig;
+use OCP\DB\QueryBuilder\IQueryFunction;
 use OCP\IDBConnection;
 use OCP\ILogger;
 
@@ -506,7 +507,13 @@ class QueryBuilderTest extends \Test\TestCase {
 	}
 
 	public function dataFrom() {
+		$config = $this->createMock(SystemConfig::class);
+		$logger = $this->createMock(ILogger::class);
+		$qb = new QueryBuilder(\OC::$server->getDatabaseConnection(), $config, $logger);
 		return [
+			[$qb->createFunction('(' . $qb->select('*')->from('test')->getSQL() . ')'), 'q', null, null, [
+				['table' => '(SELECT * FROM `*PREFIX*test`)', 'alias' => '`q`']
+			], '(SELECT * FROM `*PREFIX*test`) `q`'],
 			['data', null, null, null, [['table' => '`*PREFIX*data`', 'alias' => null]], '`*PREFIX*data`'],
 			['data', 't', null, null, [['table' => '`*PREFIX*data`', 'alias' => '`t`']], '`*PREFIX*data` `t`'],
 			['data1', null, 'data2', null, [
@@ -523,9 +530,9 @@ class QueryBuilderTest extends \Test\TestCase {
 	/**
 	 * @dataProvider dataFrom
 	 *
-	 * @param string $table1Name
+	 * @param string|IQueryFunction $table1Name
 	 * @param string $table1Alias
-	 * @param string $table2Name
+	 * @param string|IQueryFunction $table2Name
 	 * @param string $table2Alias
 	 * @param array $expectedQueryPart
 	 * @param string $expectedQuery
@@ -1204,6 +1211,9 @@ class QueryBuilderTest extends \Test\TestCase {
 	}
 
 	public function dataGetTableName() {
+		$config = $this->createMock(SystemConfig::class);
+		$logger = $this->createMock(ILogger::class);
+		$qb = new QueryBuilder(\OC::$server->getDatabaseConnection(), $config, $logger);
 		return [
 			['*PREFIX*table', null, '`*PREFIX*table`'],
 			['*PREFIX*table', true, '`*PREFIX*table`'],
@@ -1212,13 +1222,17 @@ class QueryBuilderTest extends \Test\TestCase {
 			['table', null, '`*PREFIX*table`'],
 			['table', true, '`*PREFIX*table`'],
 			['table', false, '`table`'],
+
+			[$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), null, '(SELECT * FROM `*PREFIX*table`)'],
+			[$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), true, '(SELECT * FROM `*PREFIX*table`)'],
+			[$qb->createFunction('(' . $qb->select('*')->from('table')->getSQL() . ')'), false, '(SELECT * FROM `*PREFIX*table`)'],
 		];
 	}
 
 	/**
 	 * @dataProvider dataGetTableName
 	 *
-	 * @param string $tableName
+	 * @param string|IQueryFunction $tableName
 	 * @param bool $automatic
 	 * @param string $expected
 	 */