Subversion Repositories eFlore/Applications.del

Compare Revisions

Ignore whitespace Rev 2147 → Rev 2148

/trunk/doc/bdd/sql/del.sql
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;