summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-02-01 18:36:27 +0100
committerYorhel <git@yorhel.nl>2010-02-01 18:37:45 +0100
commit4d9254ff709e1925211e62cee480cf287d52a74a (patch)
treeef73e5eb98f69b4f4d45b54cd670026a71481ee3 /util
parentc4afecd1c5135d10a36a3a663837f5b83083bdd2 (diff)
Added notification for database deletions to users who contributed
Complex queries...
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql3
-rw-r--r--util/sql/func.sql33
-rw-r--r--util/updates/update_2.11.sql12
3 files changed, 44 insertions, 4 deletions
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 06c1ca7f..e4f66d91 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -63,6 +63,9 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags
CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
-- Sequences used for ID generation of items not in the DB
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 9675097e..1dcdd496 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -618,3 +618,36 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
+
+-- called on UPDATE vn / producers / releases
+CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$
+BEGIN
+ -- item is deleted?
+ IF OLD.latest IS DISTINCT FROM NEW.latest AND NOT OLD.hidden AND NEW.hidden THEN
+ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+ SELECT DISTINCT 'dbdel'::notification_ntype,
+ (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
+ c.requester, NEW.id, c2.rev, x.title, c2.requester
+ -- look for changes of the deleted entry
+ -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
+ FROM changes c
+ JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr
+ JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
+ WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
+ UNION SELECT rr.id, rr2.title FROM releases_rev rr
+ JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest
+ WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id
+ UNION SELECT pr.id, pr2.name FROM producers_rev pr
+ JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
+ WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
+ ) x(id, title) ON c.id = x.id
+ -- join info about the deletion itself
+ JOIN changes c2 ON c2.id = NEW.latest
+ WHERE c.requester <> 1 -- exclude Multi
+ -- exclude the user who deleted the entry
+ AND c.requester <> c2.requester;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql
index 0a077bc1..27b90c8f 100644
--- a/util/updates/update_2.11.sql
+++ b/util/updates/update_2.11.sql
@@ -1,7 +1,7 @@
-CREATE TYPE notification_ntype AS ENUM ('pm');
-CREATE TYPE notification_ltype AS ENUM ('t');
+CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel');
+CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
CREATE TABLE notifications (
id serial PRIMARY KEY NOT NULL,
@@ -39,8 +39,6 @@ CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn
CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock();
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-
CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$
BEGIN
@@ -90,3 +88,9 @@ ALTER TABLE sessions RENAME COLUMN expiration TO added;
UPDATE sessions SET added = added - '1 year'::interval;
+CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
+-- make sure to add these triggers AFTER performing the batch edit above
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel();
+