Subversion Repositories eFlore/Applications.del

-- -----------------------------------------------------

-- Table de données calculées pour le service d'export

-- PlantNet

--

-- Comporte la date dernière mise à jour d'une obs et

-- des données liées

-- -----------------------------------------------------

CREATE TABLE del_observation_modif_date

(

  id_observation BIGINT NOT NULL,

  modif_date datetime,

  UNIQUE (id_observation)

)

-- -----------------------------------------------------

-- Initialisation des dates de dernière màj d'une obs et

-- de ses données liées

-- -----------------------------------------------------

INSERT INTO del_observation_modif_date (id_observation, modif_date)

SELECT DISTINCT p.id_observation,

       GREATEST(IFNULL(p.date_creation, '1900-01-01'), IFNULL(p.date_modification, '1900-01-01'), IFNULL(MAX(image_tag.date), '1900-01-01'), IFNULL(MAX(image_tag.date_modification), '1900-01-01'), IFNULL(MAX(image_vote.date), '1900-01-01'), IFNULL(MAX(commentaire.date), '1900-01-01'), IFNULL(MAX(commentaire_vote.date), '1900-01-01')) AS modif_date

FROM del_plantnet AS p

LEFT JOIN del_image_vote AS image_vote ON (id_image = image_vote.ce_image

                                   AND image_vote.ce_protocole = 3)

LEFT JOIN del_image_tag AS image_tag ON (id_image = image_tag.ce_image

                                  AND image_tag.actif = 1)

LEFT JOIN del_commentaire AS commentaire ON (id_observation = commentaire.ce_observation)

LEFT JOIN del_commentaire_vote AS commentaire_vote ON (commentaire.id_commentaire = commentaire_vote.ce_proposition)

GROUP BY id_observation

HAVING MAX(p.date_creation) >= '1900-01-01'

OR MAX(p.date_modification) >= '1900-01-01'

OR MAX(image_tag.date) >= '1900-01-01'

OR MAX(image_tag.date_modification) >= '1900-01-01'

OR MAX(image_vote.date) >= '1900-01-01'

OR MAX(commentaire.date) >= '1900-01-01'

OR MAX(commentaire_vote.date) >= '1900-01-01'

-- -----------------------------------------------------

-- Triggers pour garder à jour les dates de dernière màj

-- d'une obs et de ses données liées

-- -----------------------------------------------------

-- cel_obs INSERT trigger --

DROP TRIGGER IF EXISTS tb_cel.TRIGGER_celObs_dateModif_INSERT

CREATE TRIGGER tb_cel.TRIGGER_celObs_dateModif_INSERT

AFTER INSERT ON tb_cel.cel_obs FOR EACH ROW

INSERT INTO tb_del.del_observation_modif_date (id_observation, modif_date)

VALUES (NEW.id_observation, NEW.date_creation);

-- cel_obs UPDATE trigger --

CREATE TRIGGER tb_cel.TRIGGER_celObs_dateModif_UPDATE

AFTER UPDATE ON tb_cel.cel_obs FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.date_modification

WHERE id_observation = NEW.id_observation;

-- del_commentaire INSERT trigger --

CREATE TRIGGER tb_del.TRIGGER_delCommentaire_dateModif_INSERT

AFTER INSERT ON tb_del.del_commentaire FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = NEW.ce_observation;

-- del_commentaire UPDATE trigger --

CREATE TRIGGER tb_del.TRIGGER_delCommentaire_dateModif_UPDATE

AFTER UPDATE ON tb_del.del_commentaire FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = NEW.ce_observation;

-- del_commentaire_vote INSERT trigger --

CREATE TRIGGER tb_del.TRIGGER_delCommentaireVote_dateModif_INSERT

AFTER INSERT ON tb_del.del_commentaire_vote FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_del.del_commentaire WHERE id_commentaire = NEW.ce_proposition);

-- del_commentaire_vote UPDATE trigger --

CREATE TRIGGER tb_del.TRIGGER_delCommentaireVote_dateModif_UPDATE

AFTER UPDATE ON tb_del.del_commentaire_vote FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_del.del_commentaire WHERE id_commentaire = NEW.ce_proposition);

-- del_image_vote INSERT trigger --

CREATE TRIGGER tb_del.TRIGGER_delImageVote_dateModif_INSERT

AFTER INSERT ON tb_del.del_image_vote FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_cel.cel_images WHERE id_image = NEW.ce_image);

-- del_image_vote UPDATE trigger --

CREATE TRIGGER tb_del.TRIGGER_delImageVote_dateModif_UPDATE

AFTER UPDATE ON tb_del.del_image_vote FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_cel.cel_images WHERE id_image = NEW.ce_image);

-- del_image_tag INSERT trigger --

CREATE TRIGGER tb_del.TRIGGER_delImageTag_dateModif_INSERT

AFTER INSERT ON tb_del.del_image_tag FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_cel.cel_images WHERE id_image = NEW.ce_image);

-- del_image_tag UPDATE trigger --

CREATE TRIGGER tb_del.TRIGGER_delImageTag_dateModif_UPDATE

AFTER UPDATE ON tb_del.del_image_tag FOR EACH ROW

UPDATE tb_del.del_observation_modif_date SET modif_date = NEW.`date`

WHERE id_observation = (SELECT ce_observation FROM tb_cel.cel_images WHERE id_image = NEW.ce_image);

-- -----------------------------------------------------

-- Fin des triggers pour garder à jour les dates de

-- dernière màj d'une obs et de ses données liées

-- -----------------------------------------------------