Page MenuHomePhabricator
Paste P8058

Comment table triggers for T215445
ActivePublic

Authored by Anomie on Wed, Feb 6, 8:12 PM.
delimiter //
CREATE FUNCTION wmcsCommentShow(id BIGINT) RETURNS INT NOT DETERMINISTIC READS SQL DATA
BEGIN
RETURN COALESCE(
( SELECT 1 FROM image WHERE img_description_id = id LIMIT 1 ) OR
( SELECT 1 FROM filearchive WHERE fa_deleted_reason_id = id LIMIT 1 ) OR
( SELECT 1 FROM filearchive WHERE fa_description_id = id AND fa_deleted&2 = 0 LIMIT 1 ) OR
( SELECT 1 FROM ipblocks WHERE ipb_reason_id = id and ipb_deleted = 0 LIMIT 1 ) OR
( SELECT 1 FROM oldimage WHERE oi_description_id = id AND oi_deleted&2 = 0 LIMIT 1 ) OR
( SELECT 1 FROM protected_titles WHERE pt_reason_id = id LIMIT 1 ) OR
( SELECT 1 FROM recentchanges WHERE rc_comment_id = id AND rc_deleted&2 = 0 LIMIT 1 ) OR
( SELECT 1 FROM revision JOIN revision_comment_temp ON(revcomment_rev = rev_id) WHERE revcomment_comment_id = id AND rev_deleted&2 = 0 LIMIT 1 ) OR
( SELECT 1 FROM logging WHERE log_comment_id = id AND log_deleted&2 = 0 AND log_type IN ($LOG_TYPE_LIST$) LIMIT 1 )
, 0);
END //
delimiter ;
delimiter //
CREATE PROCEDURE wmcsCommentOnInsert(id BIGINT, del TINYINT) NOT DETERMINISTIC MODIFIES SQL DATA
BEGIN
IF NOT del THEN
UPDATE comment SET wmcs_is_visible = 1 WHERE comment_id = id;
END IF;
END //
delimiter ;
delimiter //
CREATE PROCEDURE wmcsCommentOnDelete(id BIGINT, del TINYINT) NOT DETERMINISTIC MODIFIES SQL DATA
BEGIN
IF NOT del AND NOT wmcsCommentShow(id) THEN
UPDATE comment SET wmcs_is_visible = 0 WHERE comment_id = id;
END IF;
END //
delimiter ;
delimiter //
CREATE PROCEDURE wmcsCommentOnUpdate(oldId BIGINT, oldDel TINYINT, newId BIGINT, newDel TINYINT) NOT DETERMINISTIC MODIFIES SQL DATA
BEGIN
IF oldId != newId OR (NOT oldDel) != (NOT newDel) THEN
CALL wmcsCommentOnDelete(oldId, oldDel);
CALL wmcsCommentOnInsert(newId, newDel);
END IF;
END //
delimiter ;
CREATE TRIGGER image_wmcsCommentOnInsert AFTER INSERT ON image
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.img_description_id, 0 );
CREATE TRIGGER image_wmcsCommentOnUpdate AFTER UPDATE ON image
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.img_description_id, 0, NEW.img_description_id, 0 );
CREATE TRIGGER image_wmcsCommentOnDelete AFTER DELETE ON image
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.img_description_id, 0 );
CREATE TRIGGER filearchive_wmcsCommentOnInsert_reason AFTER INSERT ON filearchive
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.fa_deleted_reason_id, 0 );
CREATE TRIGGER filearchive_wmcsCommentOnUpdate_reason AFTER UPDATE ON filearchive
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.fa_deleted_reason_id, 0, NEW.fa_deleted_reason_id, 0 );
CREATE TRIGGER filearchive_wmcsCommentOnDelete_reason AFTER DELETE ON filearchive
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.fa_deleted_reason_id, 0 );
CREATE TRIGGER filearchive_wmcsCommentOnInsert_description AFTER INSERT ON filearchive
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.fa_description_id, NEW.fa_deleted&2 );
CREATE TRIGGER filearchive_wmcsCommentOnUpdate_description AFTER UPDATE ON filearchive
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.fa_description_id, OLD.fa_deleted&2, NEW.fa_description_id, NEW.fa_deleted&2 );
CREATE TRIGGER filearchive_wmcsCommentOnDelete_description AFTER DELETE ON filearchive
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.fa_description_id, OLD.fa_deleted&2 );
CREATE TRIGGER ipblocks_wmcsCommentOnInsert AFTER INSERT ON ipblocks
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.ipb_reason_id, NEW.ipb_deleted );
CREATE TRIGGER ipblocks_wmcsCommentOnUpdate AFTER UPDATE ON ipblocks
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.ipb_reason_id, OLD.ipb_deleted, NEW.ipb_reason_id, NEW.ipb_deleted );
CREATE TRIGGER ipblocks_wmcsCommentOnDelete AFTER DELETE ON ipblocks
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.ipb_reason_id, OLD.ipb_deleted );
CREATE TRIGGER oldimage_wmcsCommentOnInsert AFTER INSERT ON oldimage
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.oi_description_id, NEW.oi_deleted&2 );
CREATE TRIGGER oldimage_wmcsCommentOnUpdate AFTER UPDATE ON oldimage
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.oi_description_id, OLD.oi_deleted&2, NEW.oi_description_id, NEW.oi_deleted&2 );
CREATE TRIGGER oldimage_wmcsCommentOnDelete AFTER DELETE ON oldimage
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.oi_description_id, OLD.oi_deleted&2 );
CREATE TRIGGER protected_titles_wmcsCommentOnInsert AFTER INSERT ON protected_titles
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.pt_reason_id, 0 );
CREATE TRIGGER protected_titles_wmcsCommentOnUpdate AFTER UPDATE ON protected_titles
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.pt_reason_id, 0, NEW.pt_reason_id, 0 );
CREATE TRIGGER protected_titles_wmcsCommentOnDelete AFTER DELETE ON protected_titles
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.pt_reason_id, 0 );
CREATE TRIGGER recentchanges_wmcsCommentOnInsert AFTER INSERT ON recentchanges
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.rc_comment_id, NEW.rc_deleted&2 );
CREATE TRIGGER recentchanges_wmcsCommentOnUpdate AFTER UPDATE ON recentchanges
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.rc_comment_id, OLD.rc_deleted&2, NEW.rc_comment_id, NEW.rc_deleted&2 );
CREATE TRIGGER recentchanges_wmcsCommentOnDelete AFTER DELETE ON recentchanges
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.rc_comment_id, OLD.rc_deleted&2 );
CREATE TRIGGER revision_comment_temp_wmcsCommentOnInsert AFTER INSERT ON revision_comment_temp
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.revcomment_comment_id, (SELECT rev_deleted&2 FROM revision WHERE rev_id=NEW.revcomment_rev) );
CREATE TRIGGER revision_comment_temp_wmcsCommentOnUpdate AFTER UPDATE ON revision_comment_temp
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.revcomment_comment_id, (SELECT rev_deleted&2 FROM revision WHERE rev_id=OLD.revcomment_rev), NEW.revcomment_comment_id, (SELECT rev_deleted&2 FROM revision WHERE rev_id=NEW.revcomment_rev) );
CREATE TRIGGER revision_comment_temp_wmcsCommentOnDelete AFTER DELETE ON revision_comment_temp
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.revcomment_comment_id, (SELECT rev_deleted&2 FROM revision WHERE rev_id=OLD.revcomment_rev) );
CREATE TRIGGER revision_wmcsCommentOnInsert AFTER INSERT ON revision
FOR EACH ROW CALL wmcsCommentOnInsert( (SELECT revcomment_comment_id FROM revision_comment_temp WHERE revcomment_rev=NEW.rev_id), NEW.rev_deleted&2 );
CREATE TRIGGER revision_wmcsCommentOnUpdate AFTER UPDATE ON revision
FOR EACH ROW CALL wmcsCommentOnUpdate( (SELECT revcomment_comment_id FROM revision_comment_temp WHERE revcomment_rev=OLD.rev_id), OLD.rev_deleted&2, (SELECT revcomment_comment_id FROM revision_comment_temp WHERE revcomment_rev=NEW.rev_id), NEW.rev_deleted&2 );
CREATE TRIGGER revision_wmcsCommentOnDelete AFTER DELETE ON revision
FOR EACH ROW CALL wmcsCommentOnDelete( (SELECT revcomment_comment_id FROM revision_comment_temp WHERE revcomment_rev=OLD.rev_id), OLD.rev_deleted&2 );
CREATE TRIGGER logging_wmcsCommentOnInsert AFTER INSERT ON logging
FOR EACH ROW CALL wmcsCommentOnInsert( NEW.log_comment_id, NOT ( NEW.log_deleted&2 = 0 AND NEW.log_type IN ($LOG_TYPE_LIST$) ) );
CREATE TRIGGER logging_wmcsCommentOnUpdate AFTER UPDATE ON logging
FOR EACH ROW CALL wmcsCommentOnUpdate( OLD.log_comment_id, NOT ( OLD.log_deleted&2 = 0 AND OLD.log_type IN ($LOG_TYPE_LIST$) ), NEW.log_comment_id, NOT ( NEW.log_deleted&2 = 0 AND NEW.log_type IN ($LOG_TYPE_LIST$) ) );
CREATE TRIGGER logging_wmcsCommentOnDelete AFTER DELETE ON logging
FOR EACH ROW CALL wmcsCommentOnDelete( OLD.log_comment_id, NOT ( OLD.log_deleted&2 = 0 AND OLD.log_type IN ($LOG_TYPE_LIST$) ) );

Event Timeline

Anomie created this paste.Wed, Feb 6, 8:12 PM
Anomie edited the content of this paste. (Show Details)
Anomie edited the content of this paste. (Show Details)Wed, Feb 6, 8:15 PM