diff options
Diffstat (limited to 'util/sql/func.sql')
| -rw-r--r-- | util/sql/func.sql | 38 |
1 files changed, 22 insertions, 16 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index 75e5ecad..b0dbfff2 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -802,16 +802,16 @@ END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / producers / releases when (NOT OLD.hidden AND NEW.hidden) +-- called on UPDATE vn / producers / releases / chars when (NOT OLD.hidden AND NEW.hidden) CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ BEGIN 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 + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + h.requester, NEW.id, h2.rev, x.title, h2.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 + FROM changes h 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 @@ -821,12 +821,15 @@ BEGIN 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 + UNION SELECT cr.id, cr2.name FROM chars_rev cr + JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest + WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id + ) x(id, title) ON h.id = x.id -- join info about the deletion itself - JOIN changes c2 ON c2.id = NEW.latest - WHERE c.requester <> 1 -- exclude Multi + JOIN changes h2 ON h2.id = NEW.latest + WHERE h.requester <> 1 -- exclude Multi -- exclude the user who deleted the entry - AND c.requester <> c2.requester; + AND h.requester <> h2.requester; RETURN NULL; END; $$ LANGUAGE plpgsql; @@ -862,16 +865,16 @@ END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / producers / releases when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) +-- called on UPDATE vn / producers / releases / chars when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) -- this trigger is very similar to notify_dbdel() CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$ BEGIN INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) SELECT DISTINCT 'dbedit'::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 + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + h.requester, NEW.id, h2.rev, x.title, h2.requester -- look for changes of the edited entry - FROM changes c + FROM changes h 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 @@ -881,13 +884,16 @@ BEGIN 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 + UNION SELECT cr.id, cr2.name FROM chars_rev cr + JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest + WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id + ) x(id, title) ON h.id = x.id -- join info about the deletion itself - JOIN changes c2 ON c2.id = NEW.latest + JOIN changes h2 ON h2.id = NEW.latest -- exclude the user who edited the entry - WHERE c.requester <> c2.requester + WHERE h.requester <> h2.requester -- exclude users who don't want this notify - AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = c.requester AND key = 'notify_nodbedit'); + AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit'); RETURN NULL; END; $$ LANGUAGE plpgsql; |