Looking for Computer Science & Information Technology online
courses ?
Check my new web site: https://www.yesik.it !
Il y a quelques temps, j'ai dû évaluer le travail d'un étudiant. Et en observant son code de plus près, j'ai découvert une horreur absolue du point de vue des bases de données.
Avant d'en dire plus, voyons si vous arrivez à distinguer ce qui est choquant.
Sommaire
Le programme incriminé
Bien sûr, je ne vais pas reproduire ici l'intégralité du programme en cause. J'en ai extrait la partie sur laquelle je souhaite insister. Ce programme était une sorte d'application web sociale dans laquel un utilisateur s'inscrivait – et par la suite était connu des autres utilisateurs du système via son avatar. Dans la base de données, deux tables avaient été crées: une contenant les informations de l'utilisateur telles que son nom réel et la validité de son abonnement au site. L'autre contenait les données de l'avatar: pseudonyme, icône, etc. Un utilisateur ne peut avoir qu'un seul avatar. Il existe donc une relation un-à-un entre ces deux tables.
Après investigation, voici peu ou prou la manière dont les choses se sont passées.
Jour 1: tout va bien
Dans cette version, l'étudiant a créé la base de données et la table Utilisateur. Ce qui ressemble à ceci en utilisant Derby:
ij> CONNECT 'jdbc:derby:meetthem;create=true';
ij> CREATE TABLE Utilisateur (
> Id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
> Nom VARCHAR(40) NOT NULL,
> Prénom VARCHAR(40) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO Utilisateur (Nom, Prénom) VALUES
> ('Dupont', 'Jean'),
> ('Durand', 'Marc'),
> ('Martin', 'Pauline');
3 rows inserted/updated/deleted
Et voici son programme Java initial. Celui-ci se contente d'afficher la liste des utilisateurs connus du système. Par souci d'exhaustivité, je donne ici le code complet, mais la partie intéressante est la méthode showMembers:
package fr.chicoree.ap.meetthem; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MeetThem { public MeetThem() throws Exception { Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); conn = DriverManager.getConnection("jdbc:derby:meetthem"); } public void close() throws SQLException { if (conn != null) conn.close(); } /** * Affiche la liste des membres inscrits et leur avatar * @throws SQLException */ public void showMembers() throws SQLException { Statement users = conn.createStatement(); ResultSet rs = users.executeQuery("SELECT * FROM Utilisateur"); int counter = 0; while(rs.next()) { System.out.printf("%02d: %s, %s%n", ++counter, rs.getString("nom"), rs.getString("prénom")); } users.close(); } private Connection conn = null; public static void main(String[] args) throws Exception { MeetThem prog = new MeetThem(); prog.showMembers(); prog.close(); } }
Le programme fonctionne très bien en l'état et une fois compilé et exécuté on obtient le résultat suivant:
01: Dupont, Jean 02: Durand, Marc 03: Martin, Pauline
Rien de choquant pour l'instant.
Jour 2: rien ne va plus
Ici, les choses vont se corser. L'étudiant a tout d'abord ajouté la table des avatars:
ij> CONNECT 'jdbc:derby:meetthem';
ij> CREATE TABLE Avatar (
> Id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
> Pseudo VARCHAR(18) NOT NULL UNIQUE,
> Icône VARCHAR(40));
0 rows inserted/updated/deleted
ij> INSERT INTO Avatar (Pseudo, Icône) VALUES
> ('jean-jean', 'sexy_boy.jpg'),
> ('marc56', NULL),
> ('chouchou', 'princesse.jpg');
3 rows inserted/updated/deleted
Et dans la volée, le code source de la méthode showMembers a été modifié ainsi pour afficher le nom de l'avatar en face du nom de chaque membre:
public void showMembers() throws SQLException { /* ... */ public void showMembers() throws SQLException { Statement users = conn.createStatement(); ResultSet rsu = users.executeQuery("SELECT * FROM Utilisateur"); Statement avatars = conn.createStatement(); ResultSet rsa = avatars.executeQuery("SELECT * FROM Avatar"); int counter = 0; while(rsu.next() && rsa.next()) { System.out.printf("%02d: %s, %s (%s)%n", ++counter, rsu.getString("nom"), rsu.getString("prénom"), rsa.getString("Pseudo")); } users.close(); avatars.close(); } /* ... */
Et le résutat donne:
01: Dupont, Jean (jean-jean) 02: Durand, Marc (marc56) 03: Martin, Pauline (chouchou)
Ce qui semble en valider le fonctionnement. Sauf que ce programme est faux. Complètement faux. Archi faux.
Ce qui ne va pas
Le problème ici est une méconnaissance des principes fondamentaux d'une base de données relationnelle. Ici, l'étudiant s'est dit que le SELECT allait renvoyer les utilisateurs dans l'ordre dans lequel ils ont été rentrés, et les avatars dans l'ordre de leur création. En outre, puisque les utilisateurs et les avatars sont créés dans le même ordre, il lui a semblé logique que la première ligne récupérée dans rsa corresponde à la première ligne de rsu. Et ainsi de suite. En plus, le test du programme semble prouver que ce raisonnement est exact. Mais il ne l'est pas!
Une des caractéristiques fondamentales des bases de données relationnelles est que les tables sont des ensembles. Et en algèbre relationnelle – tout comme dans le reste des mathématiques – l'ordre des éléments dans un ensemble n'a pas d'importance.
Ainsi en l'absence d'une clause ORDER BY l'ordre des lignes dans le résultat d'une requête SELECT est indéterminé. Autrement dit, non seulement rien ne garantit que les données soient renvoyées dans l'ordre de leur insertion dans la base, mais en plus, d'un SELECT à l'autre, il se peut très bien que les lignes ne soient pas renvoyées dans le même ordre!
Le piège est que les serveurs de bases de données ont tendance à laisser croire le contraire. Surtout que l'aspect aléatoire de l'ordre des résultats d'une requête SELECT n'apparaît surtout qu'en cas d'accès concurrents à la base ou lorsque celle-ci a déjà vécue et subie de nombreuses modifications ou suppressions de données. Or, lors du développement, c'est souvent sur une base "propre et neuve" que sont testés les programmes.
Néanmoins, je vais quand même tâcher de mettre en évidence le problème dans notre cas. Un avertissement cependant: je ne saurais garantir que votre serveur de bases de données exhibera le même comportement que le mien. Un autre SGBD-R que Derby présentera peut-être un autre comportement. Voire même une autre version de Derby.
Preuve par l'exemple
Vous voulez donc une preuve qu'un SELECT ne renvoie pas les données dans l'ordre de leur saisie? Et bien voici deux requêtes SELECT dénuées de clause ORDER BY. Et comme vous le verrez, l'ordre du résultat est clairement diffèrent!
ij> SELECT * FROM Avatar; ID |PSEUDO |ICÔNE ----------------------------------------------------------------------- 1 |jean-jean |sexy_boy.jpg 2 |marc56 |NULL 3 |chouchou |princesse.jpg 3 rows selected ij> SELECT pseudo FROM Avatar; PSEUDO ------------------ chouchou jean-jean marc56 3 rows selected
Pourquoi? La réponse formelle est qu'il n'y a pas besoin de raison! En l'absence d'une clause ORDER BY, un SELECT ne garantit pas l'ordre des résultats. Un point c'est tout.
Si vous voulez une explication technique, la voici: SELECT * récupère toutes les données d'une table. Derby retrouve donc les données tout simplement dans l'ordre où elles sont enregistrées dans le fichier qui correspond à la table. SELECT pseudo récupère uniquement le champ pseudo. Or ce champ a été marqué UNIQUE lors de la création de la table. Derby y a donc associé un index. Les données sont récupérées dans l'ordre de cet index. Mais à nouveau je répète: ces informations sont données à titre indicatif parce que j'ai une idée du fonctionnement interne de Derby. Rien ne garantit que cela reste valide. Se reposer sur l'ordre observé ici reste donc une erreur!
Qu'est ce qu'il faut faire
La bonne solution ici est de rajouter dans une des tables un champ qui fait référence à la ligne correspondante dans l'autre table. C'est ce que l'on appelle une clé étrangère. C'est par l'intermédiaire de cette clé que les deux tables vont être associées. Nous avons deux options possibles:
- ajouter à la table Utilisateur un champ qui référence la table Avatar
- ou ajouter à la table Avatar un champ qui référence la table Utilisateur
Comme nous sommes dans le cas d'une relation un-à-un, du point de vue de l'algèbre relationnel, il n'y a aucune différence entre les deux options. Ici, j'ai choisi (arbitrairement) la seconde. Pour l'instant, voici nos deux tables:
ij> SELECT * FROM Utilisateur; ID |NOM |PRÉNOM --------------------------------------------------------------------------------------------- 1 |Dupont |Jean 2 |Durand |Marc 3 |Martin |Pauline 3 rows selected ij> SELECT * FROM Avatar; ID |PSEUDO |ICÔNE ----------------------------------------------------------------------- 1 |jean-jean |sexy_boy.jpg 2 |marc56 |NULL 3 |chouchou |princesse.jpg 3 rows selected
Piège:
Je dois bel et bien rajouter un champ à une des tables. A nouveau contrairement aux apparences, je ne peux pas utiliser les champs id existants pour effectuer l'association entre les lignes correspondantes des deux tables.
Ici, il se trouve que par chance, cela fonctionnerait pour les données déjà rentrées. Mais rien ne le garantit. En effet, même si dans le cas de Derby les valeurs générées automatiquement (GENERATED ALWAYS AS IDENTITY) sont sensées être générées séquentiellement, il existe quand même des cas où il peut apparaître des trous dans ces valeurs. Notamment, lorsqu'une transaction ayant modifié une des deux tables est annulée (rollback).
Plus simplement, une insertion peut aussi échouer sur une contrainte (par exemple en cas de pseudo dupliqué):
ij> INSERT INTO Utilisateur(nom, prénom) VALUES ('Dupont', 'Jeanne');
1 row inserted/updated/deleted
ij> INSERT INTO Avatar(pseudo) VALUES ('chouchou');
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'SQL090510194816971' defined on 'AVATAR'.
ij> INSERT INTO Avatar(pseudo) VALUES ('chouchou_2');
1 row inserted/updated/deleted
ij> SELECT * FROM Utilisateur;
ID |NOM |PRÉNOM
---------------------------------------------------------------------------------------------
1 |Dupont |Jean
2 |Durand |Marc
3 |Martin |Pauline
4 |Dupont |Jeanne
4 rows selected
ij> SELECT * FROM Avatar;
ID |PSEUDO |ICÔNE
-----------------------------------------------------------------------
1 |jean-jean |sexy_boy.jpg
2 |marc56 |NULL
3 |chouchou |princesse.jpg
5 |chouchou_2 |NULL
4 rows selected
Comme vous le voyez, à partir de maintenant les id des utilisateurs et des avatars ne sont plus synchronisés...
J'ajoute donc une colonne à la table Avatar:
ij> ALTER TABLE Avatar ADD COLUMN utilisateur INT;
Remarque:
Je ne mets pour l'instant aucune contrainte sur la colonne utilisateur. Pourtant, normalement, elle devrait être NOT NULL et référencer (FOREIGN KEY...) une ligne de la table Utilisateur. Mais à ce stade, je ne peux pas imposer ces contraintes. En effet, il y a déjà des données dans les tables. Et avant de pouvoir imposer les contraintes nécessaires, je dois d'abord initialiser les valeurs de cette nouvelle colonne.
Si vous ne me croyez pas, faites l'expérience:
ij> ALTER TABLE Avatar ADD COLUMN utilisateur INT NOT NULL; ERROR 42601: In an ALTER TABLE statement, the column 'UTILISATEUR' has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.
Et en l'occurrence, ça ne m'aurait servi à rien de mettre une valeur par défaut. Autant patienter quelques secondes avant d'imposer la contrainte.
Reste à peupler cette nouvelle colonne, dont les valeurs sont pour l'instant désespérément nulles:
UPDATE Avatar SET utilisateur = (SELECT id FROM Utilisateur WHERE nom = 'Dupont' AND prénom = 'Jean') WHERE pseudo = 'jean-jean'; UPDATE Avatar SET utilisateur = (SELECT id FROM Utilisateur WHERE nom = 'Durand' AND prénom = 'Marc') WHERE pseudo = 'marc56'; UPDATE Avatar SET utilisateur = (SELECT id FROM Utilisateur WHERE nom = 'Martin' AND prénom = 'Pauline') WHERE pseudo = 'chouchou';
ij> SELECT * FROM Avatar; ID |PSEUDO |ICÔNE |UTILISATEUR ----------------------------------------------------------------------------------- 1 |jean-jean |sexy_boy.jpg |1 2 |marc56 |NULL |2 3 |chouchou |princesse.jpg |3 3 rows selected
Maintenant que les données sont dans la base, je peux ajouter les contraintes. Ici elles sont trois:
- Pas de valeur nulle dans la colonne Avatar.utilisateur;
- La colonne Avatar.utilisateur doit forcément contenir une valeur présente dans la colonne Utilisateur.id;
- Le contenu de la colonne Avatar.utilisateur doit être unique. Autrement dit, il ne peut pas avoir deux avatars correspondants au même utilisateur.
ij> ALTER TABLE Avatar ALTER COLUMN utilisateur NOT NULL; 0 rows inserted/updated/deleted ij> ALTER TABLE Avatar ADD FOREIGN KEY(utilisateur) REFERENCES Utilisateur(id); 0 rows inserted/updated/deleted ij> ALTER TABLE Avatar ADD UNIQUE(utilisateur); 0 rows inserted/updated/deleted
Et quand aux requêtes SELECT pour extraire les noms des utilisateurs et leur pseudo, celles-ci ne se feront plus comme deux requêtes distinctes, mais comme une jointure. C'est à dire une requête qui fera correspondre des lignes de deux tables en fonction d'un certain critère. Ici, notre critère de correspondance sera bien évidemment l'id de l'utilisateur puisque c'est la seule donnée présente à la fois dans la table utilisateur et avatar:
ij> SELECT Nom, Prénom, Pseudo
> FROM Utilisateur
> INNER JOIN Avatar ON Utilisateur.id = Avatar.utilisateur;
NOM |PRÉNOM |PSEUDO
----------------------------------------------------------------------------------------------------
Dupont |Jean |jean-jean
Durand |Marc |marc56
Martin |Pauline |chouchou
3 rows selected
Remarque:
A nouveau, rien ne garantit l'ordre des lignes renvoyées par la réquête SELECT. Par contre, ce qui est garantit, c'est que sur une ligne donnée, le pseudo, le nom et le prénom appartiennent au même utilisateur.
Bien entendu, le programme Java peut être modifié en conséquence:
/* ... */ public void showMembers() throws SQLException { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT nom, prénom, pseudo FROM Utilisateur INNER JOIN Avatar ON Utilisateur.id = Avatar.utilisateur"); int counter = 0; while(rs.next()) { System.out.printf("%02d: %s, %s (%s)%n", ++counter, rs.getString("nom"), rs.getString("prénom"), rs.getString("Pseudo")); } stmt.close(); }
Conclusion
Je pense que maintenant le message est clairement passé: La seule manière valide d'associer des données provenant de tables différentes dans le modèle relationnel est en utilisant une jointure. Et surtout pas en faisant des suppositions hasardeuses sur l'ordre des données renvoyées par la requête SELECT.
D'autant plus que comme vous l'avez vu, utiliser une jointure n'a rien de très compliqué. Alors, gare à vous si je vous y reprend...
