| 317,4 → 317,123 |
|---|
| INSERT INTO `del_image_protocole` (`id_protocole`, `intitule`, `descriptif`, `tag`, `mots_cles`) VALUES (4, 'Défi Photo', 'Choisissez les lauréats du défi photo', '', '', '1'); |
| INSERT INTO `del_image_protocole` (`id_protocole`, `intitule`, `descriptif`, `tag`, `mots_cles`) VALUES (5, 'Enquête Gentiane-Azuré', 'Participez à la localisation des Gentianes Croisette, témoins et actrices du cycle de vie du papillon Azuré de la Croisette\r\n', 'GentianeAzure', '', '0'); |
| |
| |
| -- ----------------------------------------------------- |
| -- 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 |
| -- ----------------------------------------------------- |
| |
| |
| COMMIT; |