Resolve "Forum: Reactions können doppelt angelegt werden"

Closes #6025

Merge request studip/studip!4593

1 files changed, 27 insertions, 0 deletions

diff --git a/db/migrations/6.2.2_add_unique_constraint_to_forum_posting_reactions.php b/db/migrations/6.2.2_add_unique_constraint_to_forum_posting_reactions.php
new file mode 100644
index 0000000..4a73a9f
--- /dev/null
+++ b/db/migrations/6.2.2_add_unique_constraint_to_forum_posting_reactions.php
@@ -0,0 +1,27 @@
+<?php
+
+final class AddUniqueConstraintToForumPostingReactions extends Migration
+{
+    protected function up()
+    {
+        // remove duplicate reactions
+        DBManager::get()->exec("
+            DELETE t1
+            FROM forum_posting_reactions AS t1
+            JOIN forum_posting_reactions AS t2
+              ON t1.posting_id = t2.posting_id
+                AND t1.user_id   = t2.user_id
+                AND t1.emoji     = t2.emoji
+                AND t1.id        > t2.id;
+        ");
+
+        DBManager::get()->exec(
+            "ALTER TABLE forum_posting_reactions ADD CONSTRAINT unique_posting_user_emoji UNIQUE (posting_id, user_id, emoji)"
+        );
+    }
+
+    protected function down()
+    {
+        DBManager::get()->exec("ALTER TABLE forum_posting_reactions DROP INDEX unique_posting_user_emoji");
+    }
+}