Skip to content

1 4 8 3 requetes

Une étude de cas ⅗ : requêtes

Supports complémentaires

Pour commencer, nous devons peupler la base. Essayons de créer l´instance illustrée par la figure58 . Les commandes qui suivent correspondent aux deux premiers messages, les autres sont laissés à titre d´exercice.

Il nous faut d´abord au moins deux contacts.

insert into Contact (idContact, prénom, nom,  email)
  values (1, 'Serge', 'A.', 'serge.a@inria.fr');
insert into Contact (idContact, prénom, nom,  email)
  values (4, 'Philippe', 'R.', 'philippe.r@cnam.fr');

L´insertion du premier message suppose connue l´identifiant de l´emetteur. Ici, c´est Philippe R., dont l´identifiant est 4. Les messages eux-mêmes sont (comme les contacts) identifiés par un numéro séquentiel.

insert into Message (idMessage, contenu, idEmetteur)
values (1, 'Hello Serge', 4);

Attention, la contrainte d´intégrité référentielle sur la clé étrangère implique que l´émetteur (Philippe) doit exister au moment de l´insertion du message. Les insertions ci-dessus dans un ordre différent entraineraient une erreur.

Note
Laisser l´utilisateur fournir lui-même l´identifiant n´est pas du tout pratique. Il faudrait mettre en place un mécanisme de séquence, dont le détail dépend (malheureusement) du SGBD.

Et la définition du destinataire.

insert into Envoi (idMessage, idDestinataire) values (1, 1);

La date d´envoi n´est pas encore spécifiée (et donc laissée à null) puisque la création du message dans la base ne signifie pas qu´il a été envoyé. Ce sera l´objet des prochaines sessions.

Nous pouvons maintenant insérer le second message, qui est une réponse au premier et doit donc référencer ce dernier comme prédécesseur. Cela suppose, encore une fois, de connaître son identifiant.

insert into Message (idMessage, contenu, idEmetteur, idPrédecesseur)
values (2, 'Coucou Philippe', 1, 1);

On voit que la plupart des données fournies sont des identifiants divers, ce qui rend les insertions par expression directe de requêtes SQL assez pénibles et surtout sujettes à erreur. Dans le cadre d´une véritable application, ces insertions se font après saisie via une interface graphique qui réduit considérablement ces difficultés.

Nous n´avons plus qu´à désigner le destinataire de ce deuxième message.

insert into Envoi (idMessage, idDestinataire) 
values (2, 4);

Bien malin qui, en regardant ce nuplet, pourrait deviner de quoi et de qui on parle. Il s´agit purement de la définition d´un lien entre un message et un contact.

Voici maintenant quelques exemples de requêtes sur notre base. Commençons par chercher les messages et leur émetteur.

select idMessage, contenu, prénom, nom
from Message as m,  Contact as c
where m.idEmetteur = c.idContact

Comme souvent, la jointure associe la clé primaire (de Contact) et la clé étrangère (dans le message). La jointure est l´opération inverse de la normalisation: elle regroupe, là où la normalisation décompose.

On obtient le résultat suivant (en supposant que la base correspond à l´instance de la instance-messagerie{.interpreted-text role="numref"}).

idMessage contenu prénom nom
1 Hello Serge Philippe R
2 Coucou Philippe Serge A
3 Philippe a dit ... Serge A
4 Serge a dit ... Philippe R

Cherchons maintenant les messages et leur prédécesseur.

select m1.contenu as 'Contenu', m2.contenu as 'Prédecesseur'
from Message as m1,  Message as m2
where m1.idPrédecesseur = m2.idMessage

Ce qui donne:

Contenu Prédecesseur
Coucou Philippe Hello Serge
Philippe a dit ... Hello Serge
Serge a dit ... Coucou Philippe

Quelle est la requête (si elle existe ...) qui donnerait la liste complète des prédécesseurs d´un message? Réflechissez-y, la question est épineuse et fera l´objet d´un travail complémentaire.

Et voici une requête d´agrégation: on veut tous les messages envoyés à plus d´un contact.

select m.idMessage, contenu, count(*) as 'nbEnvois'
from Message as m, Envoi as e
where m.idMessage = e.idMessage
group by idMessage, contenu
having nbEnvois > 1

Si une requête est un tant soit peu compliquée et est amenée à être exécutée souvent, ou encore si le résultat de cette requête est amené à servir de base à des requêtes complémentaires, on peut envisager de créer une vue.

create view EnvoisMultiples as
select m.idMessage, contenu, count(*) as 'nbEnvois'
from Message as m, Envoi as e
where m.idMessage = e.idMessage
group by idMessage, contenu
having nbEnvois > 1

Pour finir, un exemple de mise à jour: on veut supprimer les messages anciens, disons ceux antérieurs à 2015.

delete from Message; where year(dateEnvoi) < 2015

Malheureusement, le système nous informe qu´il a supprimé tous les messages:

All messages deleted. Table message is now empty..

Que s´est-il passé? Un point virgule mal placé (vérifiez). Est-ce que tout est perdu? Non, réfléchissez et trouvez le bon réflexe. Cela dit, les mises à jour et destructions devraient être toujours effectuées dans un cadre très contrôlé, et donc par l´intermédiaire d´une application.