1 files changed, 37 insertions, 20 deletions

diff --git a/sql/triggers.sql b/sql/triggers.sql
index 74a6b413..dc03feb5 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -167,16 +167,13 @@ 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();
 
 
 
@@ -203,15 +200,6 @@ CREATE TRIGGER insert_notify AFTER INSERT ON reviews       FOR EACH STATEMENT EX
 
 
 
--- 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();
-
-
-
-
 -- Create notifications for new posts.
 
 CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$
@@ -259,8 +247,8 @@ CREATE TRIGGER notify_review AFTER INSERT ON reviews FOR EACH ROW EXECUTE PROCED
 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
@@ -276,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
@@ -288,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
 
@@ -314,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();