diff options
Diffstat (limited to 'sql/triggers.sql')
-rw-r--r-- | sql/triggers.sql | 173 |
1 files changed, 109 insertions, 64 deletions
diff --git a/sql/triggers.sql b/sql/triggers.sql index b7ade0c4..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 +-- NOTIFY on insert into changes/posts/reviews CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ BEGIN @@ -199,10 +186,8 @@ 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; RETURN NULL; END; @@ -210,67 +195,97 @@ $$ 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 notifications for new posts. -CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS 'BEGIN NOTIFY vnsearch; RETURN NULL; END;' LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$ +BEGIN + 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 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 TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_post(); --- Add a notification when someone posts in someone's board. +-- Create notifications for new review comments. -CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ +CREATE OR REPLACE FUNCTION notify_comment() RETURNS trigger AS $$ BEGIN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'pm', 't', 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.ntype = 'pm' - 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_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); +CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE notify_comment(); --- Add a notification when a thread is created in /t/an +-- Create notifications for new reviews. -CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ +CREATE OR REPLACE FUNCTION notify_review() RETURNS trigger AS $$ BEGIN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', 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.id, NULL, 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_review AFTER INSERT ON reviews FOR EACH ROW EXECUTE PROCEDURE notify_review(); + + + + +-- Update threads.c_count and c_lastnum + +CREATE OR REPLACE FUNCTION update_threads_cache() RETURNS trigger AS $$ +BEGIN + UPDATE threads + 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 +$$ LANGUAGE plpgsql; + +CREATE TRIGGER update_threads_cache AFTER INSERT OR UPDATE OR DELETE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_threads_cache(); + + + + +-- Update reviews.c_count and c_lastnum + +CREATE OR REPLACE FUNCTION update_reviews_cache() RETURNS trigger AS $$ +BEGIN + UPDATE reviews + 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 +$$ LANGUAGE plpgsql; + +CREATE TRIGGER update_reviews_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE update_reviews_cache(); + + + + +-- 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(); @@ -285,4 +300,34 @@ 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(); + + + + +-- Call update_reviews_votes_cache() for every change on reviews_votes + +CREATE OR REPLACE FUNCTION update_reviews_votes_cache() RETURNS trigger AS $$ +BEGIN + PERFORM update_reviews_votes_cache(id) FROM (SELECT OLD.id UNION SELECT NEW.id) AS x(id) WHERE id IS NOT NULL; + RETURN NULL; +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(); |