From cdc711f2833db79aae7d3a798c406b0501dce233 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 12 Jan 2015 10:50:44 +0100 Subject: staff: Add notifications for staff edits/deletes --- util/sql/func.sql | 10 ++++++++-- util/sql/staff.sql | 4 ++++ 2 files changed, 12 insertions(+), 2 deletions(-) diff --git a/util/sql/func.sql b/util/sql/func.sql index a7e2acf8..f7125607 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -848,7 +848,7 @@ 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' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' 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 @@ -865,6 +865,9 @@ BEGIN 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 + UNION SELECT sr.id, sa.name FROM staff_rev sr + JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id + WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id ) x(id, title) ON h.id = x.id -- join info about the deletion itself JOIN changes h2 ON h2.id = NEW.latest @@ -912,7 +915,7 @@ 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' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype, h.requester, NEW.id, h2.rev, x.title, h2.requester -- look for changes of the edited entry FROM changes h @@ -928,6 +931,9 @@ BEGIN 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 + UNION SELECT sr.id, sa.name FROM staff_rev sr + JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id + WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id ) x(id, title) ON h.id = x.id -- join info about the deletion itself JOIN changes h2 ON h2.id = NEW.latest diff --git a/util/sql/staff.sql b/util/sql/staff.sql index 5f43158f..cf452bcc 100644 --- a/util/sql/staff.sql +++ b/util/sql/staff.sql @@ -1,6 +1,7 @@ -- database schema for staff/seiyuu ALTER TYPE dbentry_type ADD VALUE 's'; +ALTER TYPE notification_ltype ADD VALUE 's'; CREATE TYPE credit_type AS ENUM ('script', 'chardesign', 'music', 'director', 'art', 'songs', 'staff'); CREATE TABLE staff ( @@ -60,3 +61,6 @@ CREATE INDEX vn_staff_vid ON vn_staff (vid); CREATE INDEX vn_staff_aid ON vn_staff (aid); CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); + +CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); -- cgit v1.2.3