320,4 → 320,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; |