BEGIN
TRUNCATE VM_Revenus_annee_espece;
INSERT INTO VM_Revenus_annee_espece
SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id,
SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;
END |
DELIMITER ;
La commande TRUNCATE nom_table; a le même effet que DELETE FROM nom_table; (sans clause WHERE) ; elle
supprime toutes les lignes de la table. Cependant, TRUNCATE est un peu différent de DELETE FROM.
TRUNCATE ne supprime pas les lignes une à une : TRUNCATE supprime la table, puis la recrée (sans les données).
Par conséquent, TRUNCATE ne traite pas les clés étrangères : on ne peut pas faire un TRUNCATE sur une table dont une
colonne est référencée par une clé étrangère, sauf si celle-ci est dans la même table ; en outre les options ON DELETE
ne sont pas traitées lorsque des données sont supprimées avec cette commande.
TRUNCATE valide implicitement les transactions, et ne peut pas être annulé par un rollback.
Pour toutes ces raisons, TRUNCATE est beaucoup plus rapide que DELETE FROM (en particulier si le nombre de ligne à
supprimer est important).
Dans le cas de VM_Revenus_annee_espece, on peut sans problème utiliser TRUNCATE.
Pour mettre à jour la vue matérialisée, il suffit donc d'exécuter la procédure stockée.
Code : SQL
CALL maj_vm_revenus();
Ce type de mise à jour est généralement utilisé pour des données qu'il n'est pas impératif d'avoir à jour en permanence. La plupart
du temps, la procédure est appelée par un script qui tourne périodiquement (une fois par jour ou une fois par semaine par
exemple).
Mise à jour automatique
Si à l'inverse, on veut que les données soient toujours à jour par rapport aux derniers changements de la base de données, on
utilisera plutôt les triggers pour mettre à jour la vue matérialisée.
Dans le cas de VM_Revenus_annee_espece, la vue matérialisée doit être mise à jour en cas de modification de la table Adoption.
Une insertion provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption insérée (majoration
du total des prix et du nombre d'adoptions), ou insérera une nouvelle ligne si elle n'existe pas encore.
Une suppression provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption supprimée, ou la
suppression de celle-ci s'il s'agissait de la seule adoption correspondante.
Une modification sera un mix de la suppression et de l'insertion.
En ce qui concerne la colonne espece_id de la vue matérialisée, il vaut mieux lui ajouter une clé étrangère, avec l'option ON
DELETE CASCADE. En principe, Espece.id ne devrait jamais être modifiée, mais en mettant cette clé étrangère, on s'assure que
la correspondance entre les tables existera toujours.
On va également ajouter une clé primaire : (annee, espece_id ), afin de simplifier nos triggers. Ça permettra d'utiliser la commande
INSERT INTO ... ON DUPLICATE KEY UPDATE.
Code : SQL
ALTER TABLE VM_Revenus_annee_espece
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
368/414
www.openclassrooms.com
TRUNCATE VM_Revenus_annee_espece;
INSERT INTO VM_Revenus_annee_espece
SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id,
SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;
END |
DELIMITER ;
La commande TRUNCATE nom_table; a le même effet que DELETE FROM nom_table; (sans clause WHERE) ; elle
supprime toutes les lignes de la table. Cependant, TRUNCATE est un peu différent de DELETE FROM.
TRUNCATE ne supprime pas les lignes une à une : TRUNCATE supprime la table, puis la recrée (sans les données).
Par conséquent, TRUNCATE ne traite pas les clés étrangères : on ne peut pas faire un TRUNCATE sur une table dont une
colonne est référencée par une clé étrangère, sauf si celle-ci est dans la même table ; en outre les options ON DELETE
ne sont pas traitées lorsque des données sont supprimées avec cette commande.
TRUNCATE valide implicitement les transactions, et ne peut pas être annulé par un rollback.
Pour toutes ces raisons, TRUNCATE est beaucoup plus rapide que DELETE FROM (en particulier si le nombre de ligne à
supprimer est important).
Dans le cas de VM_Revenus_annee_espece, on peut sans problème utiliser TRUNCATE.
Pour mettre à jour la vue matérialisée, il suffit donc d'exécuter la procédure stockée.
Code : SQL
CALL maj_vm_revenus();
Ce type de mise à jour est généralement utilisé pour des données qu'il n'est pas impératif d'avoir à jour en permanence. La plupart
du temps, la procédure est appelée par un script qui tourne périodiquement (une fois par jour ou une fois par semaine par
exemple).
Mise à jour automatique
Si à l'inverse, on veut que les données soient toujours à jour par rapport aux derniers changements de la base de données, on
utilisera plutôt les triggers pour mettre à jour la vue matérialisée.
Dans le cas de VM_Revenus_annee_espece, la vue matérialisée doit être mise à jour en cas de modification de la table Adoption.
Une insertion provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption insérée (majoration
du total des prix et du nombre d'adoptions), ou insérera une nouvelle ligne si elle n'existe pas encore.
Une suppression provoquera la mise à jour de la ligne correspondant à l'année et à l'espèce de l'adoption supprimée, ou la
suppression de celle-ci s'il s'agissait de la seule adoption correspondante.
Une modification sera un mix de la suppression et de l'insertion.
En ce qui concerne la colonne espece_id de la vue matérialisée, il vaut mieux lui ajouter une clé étrangère, avec l'option ON
DELETE CASCADE. En principe, Espece.id ne devrait jamais être modifiée, mais en mettant cette clé étrangère, on s'assure que
la correspondance entre les tables existera toujours.
On va également ajouter une clé primaire : (annee, espece_id ), afin de simplifier nos triggers. Ça permettra d'utiliser la commande
INSERT INTO ... ON DUPLICATE KEY UPDATE.
Code : SQL
ALTER TABLE VM_Revenus_annee_espece
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
368/414
www.openclassrooms.com