summaryrefslogtreecommitdiff
path: root/sql/triggers.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/triggers.sql')
-rw-r--r--sql/triggers.sql87
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;