1 files changed, 57 insertions, 121 deletions
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 2fd34f1c..dc03feb5 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -36,24 +36,14 @@ CREATE TRIGGER users_imgvotes_update AFTER INSERT OR DELETE ON image_votes FOR E
 -- the stats_cache table
 
 CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
-DECLARE
-  unhidden boolean;
-  hidden boolean;
 BEGIN
   IF TG_OP = 'INSERT' THEN
     UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
 
   ELSIF TG_OP = 'UPDATE' THEN
-    IF TG_TABLE_NAME IN('tags', 'traits') THEN
-      unhidden := OLD.state <> 2 AND NEW.state = 2;
-      hidden := OLD.state = 2 AND NEW.state <> 2;
-    ELSE
-      unhidden := OLD.hidden AND NOT NEW.hidden;
-      hidden := NEW.hidden AND NOT OLD.hidden;
-    END IF;
-    IF unhidden THEN
+    IF OLD.hidden AND NOT NEW.hidden THEN
       UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
-    ELSIF hidden THEN
+    ELSIF NEW.hidden AND NOT OLD.hidden THEN
       UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
     END IF;
   END IF;
@@ -71,10 +61,10 @@ CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON chars         FOR EAC
 CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON chars         FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
 CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON staff         FOR EACH ROW WHEN (NEW.hidden = FALSE)                     EXECUTE PROCEDURE update_stats_cache();
 CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON staff         FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON tags          FOR EACH ROW WHEN (NEW.state = 2)                          EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON tags          FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state)   EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON traits        FOR EACH ROW WHEN (NEW.state = 2)                          EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON traits        FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state)   EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON tags          FOR EACH ROW WHEN (NEW.hidden = FALSE)                     EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON tags          FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_new  AFTER  INSERT           ON traits        FOR EACH ROW WHEN (NEW.hidden = FALSE)                     EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER stats_cache_edit AFTER  UPDATE           ON traits        FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
 
 
 
@@ -177,21 +167,18 @@ CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PR
 
 CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
 BEGIN
-    IF NEW.vote IS NULL THEN
-        DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
-    ELSE
-        INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
-    END IF;
-    RETURN NULL;
+    NEW.labels := CASE WHEN NEW.vote IS NULL THEN array_remove(NEW.labels, 7) ELSE array_set(NEW.labels, 7) END;
+    RETURN NEW;
 END
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE TRIGGER ulist_voted_label_ins BEFORE INSERT ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE TRIGGER ulist_voted_label_upd BEFORE UPDATE ON ulist_vns FOR EACH ROW WHEN ((OLD.vote IS NULL) <> (NEW.vote IS NULL)) EXECUTE PROCEDURE ulist_voted_label();
 
 
 
 
--- NOTIFY on insert into changes/posts/tags/trait/reviews
+-- NOTIFY on insert into changes/posts/reviews
 
 CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
 BEGIN
@@ -199,10 +186,6 @@ BEGIN
     NOTIFY newrevision;
   ELSIF TG_TABLE_NAME = 'threads_posts' THEN
     NOTIFY newpost;
-  ELSIF TG_TABLE_NAME = 'tags' THEN
-    NOTIFY newtag;
-  ELSIF TG_TABLE_NAME = 'traits' THEN
-    NOTIFY newtrait;
   ELSIF TG_TABLE_NAME = 'reviews' THEN
     NOTIFY newreview;
   END IF;
@@ -212,125 +195,49 @@ $$ LANGUAGE plpgsql;
 
 CREATE TRIGGER insert_notify AFTER INSERT ON changes       FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON tags          FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON traits        FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 CREATE TRIGGER insert_notify AFTER INSERT ON reviews       FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 
 
 
 
--- Send a vnsearch notification when the c_search column is set to NULL.
-
-CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS 'BEGIN NOTIFY vnsearch; RETURN NULL; END;' LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
-
-
-
-
--- 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();
-
-
+-- Create notifications for new posts.
 
-
--- Add a notification when a thread is created in /t/an
-
-CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
+CREATE OR REPLACE FUNCTION notify_post() 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;
+  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;
 
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_post();
 
 
 
 
--- Add a notification on new posts
+-- Create notifications for new review comments.
 
-CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$
+CREATE OR REPLACE FUNCTION notify_comment() 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.id, NEW.num, NEW.uid) n;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_post();
+CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE notify_comment();
 
 
 
 
--- Add a notification on new comment to review
+-- Create notifications for new reviews.
 
-CREATE OR REPLACE FUNCTION notify_comment() RETURNS trigger AS $$
+CREATE OR REPLACE FUNCTION notify_review() 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, NULL, NEW.uid) n;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE notify_comment();
+CREATE TRIGGER notify_review AFTER INSERT ON reviews FOR EACH ROW EXECUTE PROCEDURE notify_review();
 
 
 
@@ -340,8 +247,8 @@ CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE
 CREATE OR REPLACE FUNCTION update_threads_cache() RETURNS trigger AS $$
 BEGIN
   UPDATE threads
-     SET c_count   = (SELECT COUNT(*) FROM threads_posts WHERE NOT hidden AND tid = threads.id)
-       , c_lastnum = (SELECT MAX(num) FROM threads_posts WHERE NOT hidden AND tid = threads.id)
+     SET c_count   = (SELECT COUNT(*) FROM threads_posts WHERE hidden IS NULL AND tid = threads.id)
+       , c_lastnum = (SELECT MAX(num) FROM threads_posts WHERE hidden IS NULL AND tid = threads.id)
    WHERE id IN(OLD.tid,NEW.tid);
   RETURN NULL;
 END
@@ -357,8 +264,8 @@ CREATE TRIGGER update_threads_cache AFTER INSERT OR UPDATE OR DELETE ON threads_
 CREATE OR REPLACE FUNCTION update_reviews_cache() RETURNS trigger AS $$
 BEGIN
   UPDATE reviews
-     SET c_count   = COALESCE((SELECT COUNT(*) FROM reviews_posts WHERE NOT hidden AND id = reviews.id), 0)
-       , c_lastnum = (SELECT MAX(num) FROM reviews_posts WHERE NOT hidden AND id = reviews.id)
+     SET c_count   = COALESCE((SELECT COUNT(*) FROM reviews_posts WHERE hidden IS NULL AND id = reviews.id), 0)
+       , c_lastnum = (SELECT MAX(num) FROM reviews_posts WHERE hidden IS NULL AND id = reviews.id)
    WHERE id IN(OLD.id,NEW.id);
   RETURN NULL;
 END
@@ -369,6 +276,19 @@ CREATE TRIGGER update_reviews_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_
 
 
 
+-- Call update_vn_length_cache() for every change on vn_length_votes
+
+CREATE OR REPLACE FUNCTION update_vn_length_cache() RETURNS trigger AS $$
+BEGIN
+  PERFORM update_vn_length_cache(id) FROM (SELECT OLD.vid UNION SELECT NEW.vid) AS x(id) WHERE id IS NOT NULL;
+  RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER vn_length_cache AFTER INSERT OR UPDATE OR DELETE ON vn_length_votes FOR EACH ROW EXECUTE PROCEDURE update_vn_length_cache();
+
+
+
 
 -- Call update_images_cache() for every change on image_votes
 
@@ -380,7 +300,7 @@ END
 $$ LANGUAGE plpgsql;
 
 CREATE TRIGGER image_votes_cache1 AFTER INSERT OR DELETE ON image_votes FOR EACH ROW EXECUTE PROCEDURE update_images_cache();
-CREATE TRIGGER image_votes_cache2 AFTER UPDATE ON image_votes FOR EACH ROW WHEN ((OLD.id, OLD.sexual, OLD.violence, OLD.ignore) IS DISTINCT FROM (NEW.id, NEW.sexual, NEW.violence, NEW.ignore)) EXECUTE PROCEDURE update_images_cache();
+CREATE TRIGGER image_votes_cache2 AFTER UPDATE ON image_votes FOR EACH ROW WHEN (OLD.id <> NEW.id OR (OLD.sexual, OLD.violence, OLD.ignore) IS DISTINCT FROM (NEW.sexual, NEW.violence, NEW.ignore)) EXECUTE PROCEDURE update_images_cache();
 
 
 
@@ -395,3 +315,19 @@ END
 $$ LANGUAGE plpgsql;
 
 CREATE TRIGGER reviews_votes_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_votes FOR EACH ROW EXECUTE PROCEDURE update_reviews_votes_cache();
+
+
+
+
+-- Update quotes.score on every change to quotes_votes
+
+CREATE OR REPLACE FUNCTION update_quotes_votes_cache() RETURNS trigger AS $$
+BEGIN
+  UPDATE quotes
+     SET score = COALESCE((SELECT SUM(vote) FROM quotes_votes WHERE quotes_votes.id = quotes.id), 0)
+   WHERE id IN(OLD.id, NEW.id);
+  RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER quotes_votes_cache AFTER INSERT OR UPDATE OR DELETE ON quotes_votes FOR EACH ROW EXECUTE PROCEDURE update_quotes_votes_cache();