summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
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';