From 9efbc9c1d533fb970a5cd89f154e3a4fb7ff882d Mon Sep 17 00:00:00 2001 From: Vitor Mattos Date: Sun, 19 Dec 2021 18:11:35 -0300 Subject: [PATCH] Add comments reactions Signed-off-by: Vitor Mattos --- .../Version24000Date20211222112246.php | 96 +++++++++ lib/private/Comments/Comment.php | 16 ++ lib/private/Comments/Manager.php | 183 ++++++++++++++++++ lib/public/Comments/IComment.php | 21 ++ 4 files changed, 316 insertions(+) create mode 100644 core/Migrations/Version24000Date20211222112246.php diff --git a/core/Migrations/Version24000Date20211222112246.php b/core/Migrations/Version24000Date20211222112246.php new file mode 100644 index 00000000000..3a5bb2712b0 --- /dev/null +++ b/core/Migrations/Version24000Date20211222112246.php @@ -0,0 +1,96 @@ + + * + * @author Vitor Mattos + * + * @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 . + * + */ + +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', 'string', [ + 'notnull' => true, + 'length' => 64, + 'default' => '', + ]); + $table->addColumn('actor_id', 'string', [ + 'notnull' => true, + 'length' => 255, + 'default' => '', + ]); + $table->addColumn('reaction', Types::STRING, [ + 'notnull' => true, + 'length' => 2, + ]); + $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/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, ]; /** @@ -430,6 +431,21 @@ class Comment implements IComment { return $this; } + /** + * @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..7af905c43c9 100644 --- a/lib/private/Comments/Manager.php +++ b/lib/private/Comments/Manager.php @@ -102,6 +102,7 @@ class Manager implements ICommentsManager { } $data['children_count'] = (int)$data['children_count']; $data['reference_id'] = $data['reference_id'] ?? null; + $data['reactions'] = json_decode($data['reactions'], true); return $data; } @@ -899,12 +900,136 @@ 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 $comment): void { + $qb = $this->dbConn->getQueryBuilder(); + $qb->delete('reactions') + ->where($qb->expr()->eq('parent_id', $qb->createNamedParameter($comment->getParentId()))) + ->andWhere($qb->expr()->eq('message_id', $qb->createNamedParameter($comment->getId()))) + ->executeStatement(); + $this->sumReactions($comment); + } + + /** + * Get comment related with user reaction + * + * @param integer $parentId + * @param string $actorType + * @param string $actorId + * @param string $reaction + * @return IComment + * @throws NotFoundException + * @since 24.0.0 + */ + public function getReactionComment(int $parentId, string $actorType, string $actorId, string $reaction): IComment { + $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 { + $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->getCommentsOnList($commentIds); + } + + return $comments; + } + + /** + * Retrieve all reactions of a message + * + * @param integer $parentId + * @param string $reaction + * @return IComment[] + * @since 24.0.0 + */ + public function retrieveAllReactions(int $parentId): array { + $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']; + } + $comments = []; + if ($commentIds) { + $comments = $this->getCommentsOnList($commentIds); + } + + return $comments; + } + + /** + * Get all comments on list + * + * @param integer[] $objectIds + * @return IComment[] + * @since 24.0.0 + */ + private function getCommentsOnList(array $objectIds): array { + $query = $this->dbConn->getQueryBuilder(); + + $query->select('*') + ->from('comments') + ->where($query->expr()->in('id', $query->createNamedParameter($objectIds, 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 * @@ -988,12 +1113,66 @@ 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 $comment): void { + $qb = $this->dbConn->getQueryBuilder(); + $qb->insert('reactions') + ->values([ + 'parent_id' => $qb->createNamedParameter($comment->getParentId()), + 'message_id' => $qb->createNamedParameter($comment->getId()), + 'actor_type' => $qb->createNamedParameter($comment->getActorType()), + 'actor_id' => $qb->createNamedParameter($comment->getActorId()), + 'reaction' => $qb->createNamedParameter($comment->getMessage()), + ]) + ->executeStatement(); + $this->sumReactions($comment); + } + + private function sumReactions(IComment $comment): 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') + ), + 'total' + ) + ->from('reactions', 'r') + ->where($totalQuery->expr()->eq('r.parent_id', $qb->createNamedParameter($comment->getParentId()))) + ->groupBy('r.reaction'); + + $jsonQuery = $this->dbConn->getQueryBuilder(); + $jsonQuery + ->selectAlias( + $totalQuery->func()->concat( + $jsonQuery->expr()->literal('{'), + $jsonQuery->func()->groupConcat('total'), + $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($comment->getParentId()))) + ->executeStatement(); + } + /** * updates a Comment data row * @@ -1015,6 +1194,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/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 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|null $reactions e.g. ["👍":1] + * @return IComment + * @since 24.0.0 + */ + public function setReactions(?array $reactions): IComment; } -- 2.39.5