summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-10-07 17:13:45 +0200
committerYorhel <git@yorhel.nl>2020-10-07 17:14:25 +0200
commit7eb7a3e073107bb5ad6ec20ae18f296bde33b4da (patch)
treef9ee217a77f3908abc786c14cb4ed5c4a2a24191 /util
parentb43423e9ab6543c30660368e128d88a78b1aed39 (diff)
notifications: Mark as read when opening page + delete when deleting posts/reviews
The mark as read functionality was already present in some form for threads, but is now made consistent among all notification types. This removes the need for the redirect from the notification listing. The deletion of notifications is intended to avoid pointless notifications, especially in case of spam.
Diffstat (limited to 'util')
-rw-r--r--util/updates/wip-notifications.sql13
1 files changed, 13 insertions, 0 deletions
diff --git a/util/updates/wip-notifications.sql b/util/updates/wip-notifications.sql
index 1a817b33..ef0f574b 100644
--- a/util/updates/wip-notifications.sql
+++ b/util/updates/wip-notifications.sql
@@ -13,6 +13,19 @@ ALTER TABLE notifications ALTER COLUMN ntype TYPE notification_ntype[] USING ARR
ALTER TABLE notifications DROP COLUMN c_title;
ALTER TABLE notifications DROP COLUMN c_byuser;
+DROP INDEX notifications_uid;
+CREATE INDEX notifications_uid_iid ON notifications (uid,iid);
+
+-- Merge duplicate notifications (dbdel & listdel could cause duplicates)
+UPDATE notifications n SET ntype = ntype || ARRAY['dbdel'::notification_ntype]
+ WHERE ntype = ARRAY['listdel'::notification_ntype]
+ AND EXISTS(SELECT 1 FROM notifications m WHERE m.id <> n.id AND m.uid = n.uid AND m.iid = n.iid AND m.num IS NOT DISTINCT FROM n.num AND m.ntype = ARRAY['dbdel'::notification_ntype]);
+DELETE FROM notifications n
+ WHERE ntype = ARRAY['dbdel'::notification_ntype]
+ AND EXISTS(SELECT 1 FROM notifications m WHERE m.id <> n.id AND m.uid = n.uid AND m.iid = n.iid AND m.num IS NOT DISTINCT FROM n.num AND m.ntype = ARRAY['listdel'::notification_ntype,'dbdel']);
+-- For some reason a few notifications from 2014 were duplicated, let's just get rid of those.
+DELETE FROM notifications n WHERE EXISTS(SELECT 1 FROM notifications m WHERE m.id <> n.id AND m.uid = n.uid AND m.iid = n.iid AND m.num IS NOT DISTINCT FROM n.num AND m.id > n.id);
+
-- Subscriptions
ALTER TYPE notification_ntype ADD VALUE 'subpost' AFTER 'comment';
ALTER TYPE notification_ntype ADD VALUE 'subedit' AFTER 'subpost';