Create triggers to track counts on tags and albums for an OpenPhoto site.

openphoto-triggers

# If you added a prefix to your tables you'll need to add those manually before executing this SQL #

#######################
# Set the delimiter so we can use ;'s in the trigger
#######################
DELIMITER |

#######################
# update counts when a photo is removed from an album
#######################
CREATE TRIGGER update_album_counts_on_delete AFTER DELETE ON elementAlbum
FOR EACH ROW
BEGIN
  SET @countPublic=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=OLD.owner AND ea.album=OLD.album AND p.owner=OLD.owner AND p.permission='1');
  SET @countPrivate=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=OLD.owner AND ea.album=OLD.album AND p.owner=OLD.owner);
  UPDATE album SET countPublic=@countPublic, countPrivate=@countPrivate WHERE owner=OLD.owner AND id=OLD.album;
END;
|

#######################
# update counts when a photo is added to an album
#######################
CREATE TRIGGER update_album_counts_on_insert AFTER INSERT ON elementAlbum
FOR EACH ROW
BEGIN
  SET @countPublic=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=NEW.owner AND ea.album=NEW.album AND p.owner=NEW.owner AND p.permission='1');
  SET @countPrivate=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementAlbum AS ea ON p.id = ea.element WHERE ea.owner=NEW.owner AND ea.album=NEW.album AND p.owner=NEW.owner);
  UPDATE album SET countPublic=@countPublic, countPrivate=@countPrivate WHERE owner=NEW.owner AND id=NEW.album;
END
|

#######################
# update counts when a tag is added to a photo
#######################
CREATE TRIGGER update_tag_counts_on_insert AFTER INSERT ON elementTag
FOR EACH ROW
BEGIN
  SET @countPublic=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementTag AS et ON p.id = et.element WHERE et.owner=NEW.owner AND et.tag=NEW.tag AND p.owner=NEW.owner AND p.permission='1');
  SET @countPrivate=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementTag AS et ON p.id = et.element WHERE et.owner=NEW.owner AND et.tag=NEW.tag AND p.owner=NEW.owner);
  UPDATE tag SET countPublic=@countPublic, countPrivate=@countPrivate WHERE owner=NEW.owner AND id=NEW.tag;
END;
|

#######################
# update counts when a tag is removed from a photo
#######################
CREATE TRIGGER update_tag_counts_on_delete AFTER DELETE ON elementTag
FOR EACH ROW
BEGIN
  SET @countPublic=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementTag AS et ON p.id = et.element WHERE et.owner=OLD.owner AND et.tag=OLD.tag AND p.owner=OLD.owner AND p.permission='1');
  SET @countPrivate=(SELECT COUNT(*) FROM photo AS p INNER JOIN elementTag AS et ON p.id = et.element WHERE et.owner=OLD.owner AND et.tag=OLD.tag AND p.owner=OLD.owner);
  UPDATE tag SET countPublic=@countPublic, countPrivate=@countPrivate WHERE owner=OLD.owner AND id=OLD.tag;
END
|

#######################
# reset the delimiter to its default
#######################
DELIMITER ;