From 7eb7a3e073107bb5ad6ec20ae18f296bde33b4da Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 7 Oct 2020 17:13:45 +0200 Subject: 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. --- util/updates/wip-notifications.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'util') 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'; -- cgit v1.2.3