VI. Base de données : Stockage des informations▲
VI-A. Introduction▲
- Le fichier est dans un format géré en interne par le SGBD et dont les détails ne nous importent pas ;
- Le document est à destination d'un langage de script ;
- L'outil est le SGBD et son API associée.
Je vous préviens, vous allez apprendre du vocabulaire !
Les bases de données (BDD) désignent en fait tous les systèmes de stockage qui permettent de conserver des informations (c'est-à -dire des données) dans un lieu sûr, à l'écart de l'application. Il peut s'agir de fichiers de texte, de fichiers au format XML, de systèmes de gestion de bases de données (SGBD), etc.
Vous avez certainement entendu l'un des noms suivants : Oracle, SQL Server, Access, MySQL ? Ce sont tous des SGBDR (le "R" signifie "relationnelles"). MS Office Excel permet également de faire des BDD mais pas au même niveau que les SGBDR que je viens de mentionner.
Une base de données relationnelle permet d'enregistrer les informations sans avoir aucun duplicata, ce qui réduit l'espace de stockage utilisé ainsi que les temps d'accès, tout en simplifiant la mise à jour. Les données (informations) y sont en relation les unes avec les autres (c'est ce qui évite les doublons).
- Le SGBD contient des bases de données (exemples : "livre-d-or") : dans Excel, ce sont les "fichiers" ;
- Chaque base de données est structurée en tables (exemples : "user", "message", "sujet") : dans Excel, ce sont les "feuilles" ;
- Chaque table regroupe tous les champs d'une même entité (exemples : "id", "login") : dans Excel, ce sont les "colonnes" ;
- Un champ est une valeur (exemples : "8480", "BrYs", "30724", "Yogui") : dans Excel, ce sont les "cellules".
Vous trouverez plus bas des exemples de bases de données.
VI-B. Outils nécessaires▲
Il est parfois utile d'avoir sous la main un outil permettant de dessiner les schémas de l'analyse. Vous pouvez vous tourner vers l'un des outils conseillés par SQLProPetit guide d'analyse des données à l'aide de la méthode MERISE.
Pour le SGBD (le programme lui-même), vous avez un choix très large. En effet, vous pouvez opter pour n'importe lequel des noms cités en introduction (ou un autre). Prenez simplement garde d'installer à la fois le service et le client. Vous pouvez vous aider de notre comparatifComparatif : Quel SGBD choisir ?.
- D'un outil pour développer votre code SQL (certains IDEs comme Zend Studio, PHPEdit et PHPEclipse intègrent cette fonctionnalité) ;
- D'un outil de gestion de base de données (le SGBD, cf. notre comparatif) ;
- D'un outil d'administration (sauf si celui qui est fourni en standard avec votre SGBD vous convient) : phpMyAdmin (MySQL), phpPgAdmin (PostgreSQL), TOAD pour Oracle ou MySQL, etc.
VI-C. Votre première BDD▲
En général, la construction d'une base de données commence par une phase d'analyse. Cela se fait au moyen d'une méthode d'analyse : historiquement, les français préfèrent la méthode Merise, tandis que le reste du monde utilise la méthode UML (qui peut également servir pour la programmation). Cette analyse conceptuelle permet de se représenter le fonctionnement du système d'informations pour lequel on cherche à construire une base de données.
Je vais vous proposer d'utiliser la méthode Merise car je la trouve simple d'approche et très performante.
VI-C-1. L'analyse (conception de la structure)▲
Modèle Conceptuel des Données (MCD)▲
Dans Merise, un MCD décrit le fonctionnement d'un système d'information d'un point de vue fonctionnel, sans aucune considération technique.
Voici comment un livre d'or est représenté selon une analyse conceptuelle avec la méthode Merise (pour rappel, un livre d'or est simplement une suite de messages écrits par des utilisateurs) :
Pour dessiner ce schéma, j'ai utilisé AnalyseSIOutil gratuit de conception Merise, gratuit et très simple à utiliser.
Sans entrer dans les détails, il s'agit ici de deux entités "user" et "message" liées par une association "écrit". Chacune des deux entités est composée de champs, certains d'eux jouant le rôle d'identifiants (ils sont généralement soulignés). Notez que les entités sont représentées par des noms communs au singulier et que la relation est un verbe conjugué.
Modèle Logique des Données (MLD)▲
Suite à l'étape de modélisation conceptuelle ci-dessus, la méthode Merise fournit un procédé permettant d'aboutir à la structure finale de la base de données. C'est le Modèle Logique des Données (MLD), qui décrit comment sont organisées les données.
Le passage au MLD détermine la liste des champs de chacune des tables du système d'information décrit par le MCD. C'est une étape intermédiaire semi technique : les relations du MCD sont devenues des tables et des champs, mais aucun choix technique n'est encore fait.
- user (id, login, password, website)
- message (id, #user_id, title, text)
À chaque ligne du MLD est définie une table ainsi que la liste de ses champs. Les champs soulignés permettent de différencier un enregistrement d'un autre au sein d'une table, ils forment donc la clef primaire. Les champs marqués d'un signe dièse (#) sont des informations permettant de relier un enregistrement d'une table avec un enregistrement d'une autre table (lorsqu'ils ont la même valeur), et donc de faire référence à une clef primaire d'une autre table, c'est pourquoi on appelle ces champs des clefs étrangères (le champ "user_id" dans la table "message" est une clef étrangère pour le champ "id" dans la table "user").
La clef primaire est identifiée dès le MCD. Elle permet de récupérer un seul enregistrement dans une table.
La clef étrangère est identifiée à partir du MLD. Elle permet, à partir d'un enregistrement d'une table, de retrouver l'enregistrement correspondant dans une autre table. Dans notre exemple, le numéro d'un message permet de retrouver le nom de son auteur.
Modèle Physique des Données (MPD)▲
La dernière étape de la méthode Merise est la conversion du Modèle Logique en Modèle Physique des Données (MPD). En fait, le MPD peut ête composé soit de tableaux décrivant le schéma, soit du code SQL de création du schéma : le code SQL est une déduction directe du MPD (en fonction du SGBD choisi et de sa version).
Il s'agit simplement de décrire le type (chaîne de caractères, numérique...) de chacun des champs des tables. C'est très proche du SQL de création de la base de données.
Voici un MPD simplifié pour le MLD précédent :
| Nom | Type | Clef |
|---|---|---|
| id | numérique | primaire |
| login | chaîne | unique |
| password | chaîne |  |
| website | chaîne |  |
| Nom | Type | Clef |
|---|---|---|
| id | numérique | primaire |
| user_id | numérique | étrangère |
| title | chaîne |  |
| body | texte | Â |
VI-C-2. Le SQL (création et mise à jour de la structure, consultation et mise à jour des informations)▲
Le SQL (Structured Query Language) permet de manipuler la structure des informations ainsi que les informations elles-mêmes.
Pour créer les tables de notre analyse (la structure de notre base de données), on peut utiliser le code SQL suivant :
CREATE TABLE guestbook.user (
id INT(8) UNSIGNED NOT NULL auto_increment,
login VARCHAR(50) NOT NULL,
password VARCHAR(16) NOT NULL,
website VARCHAR(255) DEFAULT '',
created_at DATETIME DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE guestbook.message (
id INT(8) unsigned NOT NULL auto_increment,
user_id INT(10) unsigned NOT NULL,
title VARCHAR(50) NOT NULL,
body TEXT NOT NULL,
created_at DATETIME DEFAULT NULL,
PRIMARY KEY (id)
);
ALTER TABLE guestbook.user
ADD CONSTRAINT uk_user_login UNIQUE (login);
ALTER TABLE guestbook.message
ADD CONSTRAINT fk_message_user FOREIGN KEY (user_id)
REFERENCES guestbook.user (id) ON DELETE CASCADE ON UPDATE CASCADE;Ici, nous voyons qu'un champ user_id a été ajouté à la table message depuis l'analyse : c'est dû au fait qu'un message ne peut appartenir qu'à un utilisateur. Ainsi, nous pouvons déterminer qui a écrit quel message. L'ID de l'auteur est une information propre au message.
Une fois la structure du schéma prête, nous pouvons envoyer des requêtes pour en modifier ou pour en lire les données.
Voici comment remplir le schéma avec des informations :
INSERT INTO user (login, password, website, created_at)
VALUES ("BrYs", "1234", "brys.developpez.com", NOW());
INSERT INTO user (login, password, website, created_at)
VALUES ("Yogui", "4321", "g-rossolini.developpez.com", NOW());INSERT INTO message (user_id, title, body, created_at)
VALUES (1, "Bonjour", "Un bonjour de Paris ;)", NOW());
INSERT INTO message (user_id, title, body, created_at)
VALUES (2, "Super site", "J'apprécie ton site, continue ainsi !", NOW());
INSERT INTO message (user_id, title, body, created_at)
VALUES (2, "Merci pour tout", "J'oubliais de dire que ton site m'a beaucoup servi !", NOW());La recherche (lecture) se fait au moyen de l'instruction SELECT.
SELECT *
FROM user;L'instruction WHERE permet de filtrer les données retournées par la requête SQL.
SELECT *
FROM user
WHERE login = 'Yogui';L'instruction JOIN permet de mettre en relation deux tables qui ont un champ en commun : dans cet exemple, il s'agit de user.id et message.user_id. Notez qu'il est préférable de réutiliser le nom des tables pour éviter les conflits : indiquer seulement id porterait à confusion entre l'ID du message et l'ID de l'utilisateur.
SELECT user.login, message.title, message.body
FROM message
INNER JOIN user ON message.user_id = user.id
WHERE user.login = 'Yogui';Ici, nous pouvons voir un exemple de la relation entre les tables user et message : nous utilisons le numéro de l'utilisateur pour dire à qui appartient chaque message. De cette manière, nous ne répétons pas les informations contenues dans la table "users", ce qui réduit l'espace disque utilisé et simplifie la mise à jour des informations des utilisateurs : il suffit de modifier le contenu de la table "users".
Le SQL est prévu pour être simple à lire et relativement intuitif à comprendre. Cependant, il existe mille manières d'arriver à un seul résultat. Je vous recommande de longuement l'étudier avant de vous lancer dans des schémas complexes, vous gagnerez en efficacité. C'est ce qui s'appelle la normalisation.
VI-C-3. Exemples concrets▲
Je vais utiliser le modèle conceptuel présenté ci-dessus pour vous donner un exemple de BDD avec plusieurs systèmes : XML, Excel, Access et MySQL.
Cette suite d'exemples est principalement destinée à vous donner une vision d'ensemble, de vous montrer qu'il est possible d'obtenir le même résultat de différentes manières.
XML▲
XML est simplement un format de stockage des données dans un fichier texte. On peut donc visualiser les données avec n'importe quel éditeur de texte, mais il faut développer une application avec un langage de programmation pour pouvoir effectuer des consultations complexes. Il existe un langage pour parcourir un arbre XML : XPath.
<?xml version="1.0" encoding="UTF-8" ?>
<users>
<user>
<id>1</id>
<login>BrYs</login>
<password>1234</password>
<website>brys.developpez.com</website>
<created_at>2010-01-16 23:04:46</created_at>
</user>
<user>
<id>2</id>
<login>Yogui</login>
<password>4321</password>
<website>g-rossolini.developpez.com</website>
<created_at>2010-01-16 23:04:47</created_at>
</user>
</users><?xml version="1.0" encoding="UTF-8" ?>
<messages>
<message>
<id>1</id>
<user_id>1</user_id>
<title>Bonjour</title>
<body>Un bonjour de Paris ;)</body>
<created_at>2010-01-16 23:04:48</created_at>
</message>
<message>
<id>2</id>
<user_id>2</user_id>
<title>Super site</title>
<body>J'apprécie ton site, continue ainsi !</body>
<created_at>2010-01-16 23:04:49</created_at>
</message>
<message>
<id>3</id>
<user_id>2</user_id>
<title>Merci pour tout</title>
<body>J'oubliais de dire que ton site m'a beaucoup servi !</body>
<created_at>2010-01-16 23:04:50</created_at>
</message>
</messages>Exemple d'application permettant de retrouver les messages de Yogui :
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
<title>Messages de Yogui (source : XML)</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
function display($string)
{
echo htmlentities(utf8_decode($string), ENT_QUOTES, 'ISO-8859-1');
}
$xml_users = file_get_contents("users.xml");
$xml_messages = file_get_contents("messages.xml");
$src_users = simplexml_load_string($xml_users);
$src_messages = simplexml_load_string($xml_messages);
$users = $src_users->xpath("/users/user[login = 'Yogui']");
$yogui = $users[0];
if($yogui)
{
?>
<table border="1">
<tr>
<th>login</th>
<th>title</th>
<th>text</th>
</tr>
<?php
$messages = $src_messages->xpath("/messages/message[user_id = '".$yogui->id."']");
foreach($messages as $message)
{
?>
<tr>
<td><?php display($yogui->login); ?></td>
<td><?php display($message->title); ?></td>
<td><?php display($message->text); ?></td>
</tr>
<?php
}
?>
</table>
<?php
}
?>
</body>
</html>Et voici le résultat dans le navigateur Web :
XML est un très bon moyen d'enregistrer des données mais il comporte un inconvénient majeur : il n'y a aucun automatisme. La cohérence des données n'est pas contrôlée par un SGBD, ainsi je peux modifier n'importe quelle valeur user.id du fichier users.xml sans me rendre compte qu'ensuite je ne pourrai probablement plus utiliser la liaison avec le champ message.user_id du fichier messages.xml.
MS Office Excel▲
Excel ne dispose pas du langage SQL mais il permet néanmoins de sauvegarder les données avec l'organisation hiérarchique adoptée par les autres SGBD. C'est en quelque sorte une alternative graphique à XML, il n'y a pas besoin de s'occuper de remplir les balises mais seulement de remplir les cellules. C'est plus simple à remplir, mais plus difficile à utiliser avec une application externe.
Excel est à la fois le moteur de stockage et l'outil de visualisation des données. Le fichier de données n'est pas lisible avec n'importe quel éditeur de texte mais Excel permet d'effectuer des consultations complexes sur les données.
Dans un classeur "Livre d'or", créons les feuilles "user" et "message" :
-
user :
- Identifiant automatique ;
- Pseudonyme ;
- Mot de passe ;
- Site Web.
-
message :
- Identifiant automatique ;
- Identifiant de l'auteur ;
- Titre ;
- Texte.
Excel permet de filtrer les données feuille par feuille, mais pas de mettre en relation plusieurs feuilles. Il m'est par exemple impossible d'avoir directement la liste des messages de l'utilisateur "Yogui".
Pour obtenir la liste des messages de "Yogui", il faut commencer par obtenir son ID (colonne A) en filtrant la feuille "user" par la colonne B ("login") :
En filtrant ensuite la feuille "message" sur la colonne B ("user_id") Ã l'aide de l'ID que nous venons d'obtenir, nous obtenons indirectement les messages de "Yogui" :
Cette méthode de recherche est fastidieuse, peu pratique. Il vaut mieux utiliser un SGBD dès lors que les feuilles sont liées les unes aux autres (même raison que pour XML).
MS Office Access▲
Access propose des fonctionnalités plus complètes qu'Excel pour la gestion de bases de données.
Access, tout comme Excel, est à la fois le moteur de stockage et l'outil de visualisation des données. Le fichier de données n'est pas lisible avec n'importe quel éditeur de texte. Access permet d'effectuer des consultations complexes sur les données. Il est difficile d'utiliser une application externe pour consulter les données.
Les données contenues dans les tables sont les mêmes que précédemment :
Access étant un SGBDR, il permet de relier les tables les unes aux autres. Cela nous permet donc d'obtenir en une opération la liste des messages de l'utilisateur "Yogui" à l'aide de la requête SQL proposée plus haut :
Access (et tous les équivalents des autres suites de bureautique, par exemple OpenOffice.org Base) est un très bon outil de gestion de BDD en bureautique mais il souffre de certaines lourdeurs. Une BDD Access ou Base est adaptée pour de petites applications pour un seul utilisateur sur son poste de travail, mais elle est impraticable en environnement Web.
MySQL▲
MySQL est un véritable SGBDR, c'est-à -dire qu'il permet principalement de conserver les données sur le disque dur et qu'il dispose d'une gestion interne des droits d'accès. Pour visualiser les données, il faut développer une application à l'aide d'un langage de programmation et du langage SQL. Des outils d'administration peuvent se substituer cette l'application externe pour les tâches d'administration de la base de données, mais l'application devra être développée à un moment ou à un autre afin de permettre aux utilisateurs d'utiliser la BDD. Dans le cas d'Internet, les utilisateurs sont les internautes, les visiteurs du site Web.
Nous pouvons par exemple utiliser MySQL Query Browser, un outil d'administration gratuit :
L'intérêt d'utiliser un SGBD comme MySQL est d'avoir la possibilité de choisir l'application "client", c'est-à -dire l'application qui permet de consulter et de mettre à jour la BDD. Ci-dessus, j'ai utilisé une application de bureau mais, en développement Web, nous utiliserons très peu ce genre d'outils. Il est préférable de développer des pages Web à l'aide d'un langage de script comme PHP.
Exemple avec l'outil phpMyAdmin :
Voici un exemple de script PHP donnant le même résultat :
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-US" lang="en-US">
<head>
<title>Messages de Yogui (source : MySQL)</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
function display($string)
{
echo htmlentities($string, ENT_QUOTES, 'ISO-8859-1');
}
mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('guestbook') or die(mysql_error());
$sql = "SELECT user.login, message.title, message.body
FROM message
INNER JOIN user ON message.user_id = user.id
WHERE user.login = 'Yogui';";
$result = mysql_query($sql) or die(mysql_error());
if($result)
{
?>
<table border="1">
<tr>
<th>login</th>
<th>title</th>
<th>text</th>
</tr>
<?php
while($message = mysql_fetch_assoc($result))
{
?>
<tr>
<td><?php display($message['login']); ?></td>
<td><?php display($message['title']); ?></td>
<td><?php display($message['text']); ?></td>
</tr>
<?php
}
?>
</table>
<?php
}
?>
</body>
</html>Et voici le résultat dans le navigateur Web :
C'est à ce dernier résultat que nous voulons parvenir : MySQL en stockage des données + des scripts PHP pour les consulter.
Il est possible de mettre en place des règles de gestion afin d'empêcher la mise à jour de certains champs, ou bien d'effectuer certaines actions lorsque des évènements précis ont lieu. Par exemple ici, nous avons défini une containte d'intégrité qui relie les champs user.id et message.user_id : lorsque nous modifions une valeur user.id, MySQL met automatiquement à jour les valeurs correspondantes dans message.user_id de telle manière que la base de données reste toujours cohérente.
VI-D. Formation▲
VI-D-1. Organisme de référence▲
Malgré l'existence d'une norme SQL, il faut consulter chaque SGBD pour obtenir une documentation fiable. Les organismes ANSIAmerican National Standards Institute et ISOInternational Organization for Standardization se sont chargés de la normalisation du langage.
VI-D-2. Nos ressources habituelles▲
- La FAQ MySQL et la FAQ PostgreSQL répondent à vos questions posées dans nos forums ;
- Les sources PostgreSQL vous donnent des codes permettant de résoudre des problématiques précises ;
- Les tutoriels MySQLLes meilleurs cours MySQL vous permettront d'apprendre tout ce dont vous avez besoin ;
- Les livres MySQLLes meilleurs livres MySQL critiqués par la Rédaction et les livres PostgreSQLLes meilleurs livres PostgreSQL critiqués par la Rédaction pour toujours avoir une référence sous le coude ;
- Le forum BDD, le forum MySQL et le forum PostgreSQL vous permettent de poser toutes vos questions qui ne trouvent pas réponse dans nos autres ressources.
VI-D-3. Notre sélection de cours▲
Conception d'une base de données▲
Cyril Gruau vous présente la conception de bases de données grâce à Merise. Laurent Audibert, pour sa part, vous propose UML2.
Quelques notions de manipulation de données : SQL▲
SQL fonctionne au moyen d'un serveur de bases de données. On y envoie des instructions au moyen d'un client : PHP jouera cet office dans vos applications Web.
J'ai mis plus haut un exemple de code utilisant une syntaxe "CREATE". C'est ce que l'on appelle le langage de définition de données (DDL) : il sert à manipuler la structure des informations de la base de données. Baptiste Wicht traite uniquement la création des tables, tandis que SQLPro est plus complet.
Après avoir créé vos tables avec le DDL, il peut être intéressant de les remplir avec vos informations : cela se fait au moyen du langage de manipulation de données (DML), vu ci-dessus par l'instruction "INSERT INTO". Elle sert à manipuler le contenu de la base de données, à savoir les informations elles-mêmes. Baptiste Wicht couvre simplement la manipulation de données, alors que SQLPro est là aussi exhaustif.
Enfin, le but ultime d'une base de données étant d'être consultée, vous pouvez utiliser l'instruction "SELECT" vue ci-dessus. De nouveau, Baptiste Wicht fait une présentation plus sommaire que SQLPro, fidèle à lui-même.
Utiliser une BDD avec PHP▲
En fait, SQL et PHP sont deux choses totalement différentes. L'un peu fonctionner sans l'autre mais ils peuvent également collaborer. Pour y parvenir, je vous recommande la lecture du cours "passer des requêtes MySQL en PHP" par Eusebius. Une fois que vous êtes un peu mieux préparés, je vous recommande le tutoriel de Pierre-Baptiste Naigeon sur la création d'un site dynamique.
Écrire du bon code SQL▲
Et bien sûr, Adrien Pellegrini revient avec son guide de style pour SQL.