diff options
Diffstat (limited to 'sql/triggers.sql')
-rw-r--r-- | sql/triggers.sql | 87 |
1 files changed, 4 insertions, 83 deletions
diff --git a/sql/triggers.sql b/sql/triggers.sql index 2fd34f1c..d05f18ba 100644 --- a/sql/triggers.sql +++ b/sql/triggers.sql @@ -228,76 +228,11 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_se --- Add a notification when someone posts in someone's board. - -CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ -BEGIN - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT 'pm', tb.iid, t.id, NEW.num, t.title, NEW.uid - FROM threads t - JOIN threads_boards tb ON tb.tid = t.id - WHERE t.id = NEW.tid - AND tb.type = 'u' - AND tb.iid <> NEW.uid -- don't notify when posting in your own board - AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet - SELECT 1 - FROM notifications n - WHERE n.uid = tb.iid - AND n.iid = t.id - AND n.read IS NULL - ); - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); - - - - --- Add a notification when a thread is created in /t/an - -CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ -BEGIN - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT 'announce', u.id, t.id, 1, t.title, NEW.uid - FROM threads t - JOIN threads_boards tb ON tb.tid = t.id - -- get the users who want this announcement - JOIN users u ON u.notify_announce - WHERE t.id = NEW.tid - AND tb.type = 'an' -- announcement board - AND NOT t.hidden; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); - - - - --- Add a notification on new posts +-- Create notifications for new posts. CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$ BEGIN - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT DISTINCT 'post'::notification_ntype, u.id, t.id, NEW.num, t.title, NEW.uid - FROM threads t - JOIN threads_posts tp ON tp.tid = t.id - JOIN users u ON tp.uid = u.id - WHERE t.id = NEW.tid - AND u.notify_post - AND u.id <> NEW.uid - AND NOT t.hidden - AND NOT t.private -- don't leak posts in private threads, these are handled by notify_pm anyway - AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet (also avoids double notification with notify_pm) - SELECT 1 - FROM notifications n - WHERE n.uid = u.id - AND n.iid = t.id - AND n.read IS NULL - ); + INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.tid, NEW.num, NEW.uid) n; RETURN NULL; END; $$ LANGUAGE plpgsql; @@ -307,25 +242,11 @@ CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PR --- Add a notification on new comment to review +-- Create notifications for new review comments. CREATE OR REPLACE FUNCTION notify_comment() RETURNS trigger AS $$ BEGIN - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT 'comment', u.id, w.id, NEW.num, v.title, NEW.uid - FROM reviews w - JOIN vn v ON v.id = w.vid - JOIN users u ON w.uid = u.id - WHERE w.id = NEW.id - AND u.notify_comment - AND u.id <> NEW.uid - AND NOT EXISTS( -- don't notify when you haven't read earlier comments yet - SELECT 1 - FROM notifications n - WHERE n.uid = u.id - AND n.iid = w.id - AND n.read IS NULL - ); + INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.id, NEW.num, NEW.uid) n; RETURN NULL; END; $$ LANGUAGE plpgsql; |