cleanup blubber and delete correctly, fixes #4097

Closes #4097

Merge request studip/studip!2943

1 files changed, 46 insertions, 0 deletions

diff --git a/db/migrations/5.1.56_cleanup_blubber.php b/db/migrations/5.1.56_cleanup_blubber.php
new file mode 100644
index 0000000..86348bc
--- /dev/null
+++ b/db/migrations/5.1.56_cleanup_blubber.php
@@ -0,0 +1,46 @@
+<?php
+/**
+ * @see https://gitlab.studip.de/studip/studip/-/issues/4097
+ */
+return new class extends Migration
+{
+    public function description()
+    {
+        return 'Removes orhpaned blubber entries';
+    }
+
+    protected function up()
+    {
+        $query = "DELETE bt, ouv
+                  FROM `blubber_threads` AS bt
+                  LEFT JOIN `object_user_visits` AS ouv
+                    ON (bt.`thread_id` = ouv.`object_id`)
+                  WHERE bt.`external_contact` = 0
+                    AND bt.`user_id` NOT IN (
+                        SELECT `user_id` FROM `auth_user_md5`
+                    )";
+        DBManager::get()->exec($query);
+
+        $query = "DELETE FROM `blubber_comments`
+                  WHERE (
+                      `external_contact` = 0
+                      AND `user_id` NOT IN (
+                          SELECT `user_id` FROM `auth_user_md5`
+                      )
+                    ) OR `thread_id` NOT IN (
+                      SELECT `thread_id` FROM `blubber_threads`
+                    )";
+        DBManager::get()->exec($query);
+
+        $query = "DELETE FROM `blubber_mentions`
+                  WHERE (
+                      `external_contact` = 0
+                      AND `user_id` NOT IN (
+                          SELECT `user_id` FROM `auth_user_md5`
+                      )
+                    ) OR `thread_id` NOT IN (
+                    SELECT `thread_id` FROM `blubber_threads`
+                  )";
+        DBManager::get()->exec($query);
+    }
+};