Limiter la complexité du code applicatif grâce au SGBD

Alain Defrance

Dans cet article, il sera démontré comment simplifier le code applicatif en exploitant les possibilités du SGBD grâce aux contraintes. ?

- Titre : Limiter la complexité du code applicatif grâce au SGBD

- Auteur : Alain Defrance

- Parution : 1 mars 2008

- Mise à jour : 12 août 2008

- Licence : Copyright ® 2008 Alain DEFRANCE. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Exemple de code sans contrainte

- II-A. Le SQL de création

- II-B. Code php

- II-C. Pourquoi ce programme est-il source d'erreurs ?

- III. Les contraintes

- III-A. Propriétés des champs (NULL/NOT NULL et/ou DEFAULT)

- III-A-1. NULL/NOT NULL

- III-A-2. DEFAULT

- III-A-3. Un exemple

- III-B. Où écrire la contrainte ?

- III-C. Les contraintes à la création

- III-D. Les ajouts et suppressions de contraintes

- IV. Le nouveau code

- IV-A. Le SQL de création modifié pour gérer les contraintes

- IV-B. Code php allégé

- IV-C. Précautions à prendre avec ce type de développement

- V. Remerciements

I - Introduction

Beaucoup de programmeurs pensent que pour développer un programme, il suffit d'enchaîner les lignes de codes qui vont effectuer tous les traitements et tous les contrôles.
Le Système de Gestion de Base de Données, ou SGBD, est souvent sous-estimés alors qu'il se révèle être un excellent moyen de faire ces traitements à la place du code produit par le programmeur.
Le gain de temps est conséquent et évite par ailleurs les erreurs de programmation.

Cet article a pour but de donner des exemples concrets de simplification de code en utilisant des contraintes sur la base de données.
On utilisera Php/MySQL, mais ce type d'utilisation est valable avec tous les SGBDR.

II - Exemple de code sans contrainte

II-A - Le SQL de création

Voici un code SQL créant une petite base de données sans aucun contrôle.

CREATE TABLE `Clients`
(
`numClient` INTEGER,
`nomClient` VARCHAR(20),
`prenomClient` VARCHAR(20),
`adresseClient` VARCHAR(100),
`cpClient` VARCHAR(5),
`villeClient` VARCHAR(30),
`dateInscription` TIMESTAMP
);

CREATE TABLE `Produits`
(
`numProduit` INTEGER,
`libelleProduit` VARCHAR(50),
`prixProduit` FLOAT
);

CREATE TABLE `Commandes`
(
`numCommande` INTEGER,
`dateCommande` TIMESTAMP,
`numClient` INTEGER
);

CREATE TABLE `Concerner`
(
`numProduit` INTEGER,
`numCommandes` INTEGER,
`nombreProduit` INTEGER
);

II-B - Code php

Cette base de données créée au-dessus est bien sûr exploitable, mais il faudra effectuer tous les contrôles de saisie.

Voici l'exemple de deux fonctions :

<?php
//Ajoute un nouveau tuple ayant pour information les paramètres.
function addClient($nom, $pnom, $adr, $cp, $ville)
{
//Saisie obligatoire du nom
if(!empty($nom))
{
//Saisie obligatoire du prénom
if(!empty($pnom))
{
//On récupère la date courante.
$date = time();

//On récupère le dernier identifiant de client
$rs = mysql_query("SELECT MAX(numClient) as ancienID FROM `Clients`");
$data = mysql_fetch_array($rs);
$newID = $data['ancienID'];

//On calcule le nouvel identifiant
if($newID == NULL)
{
$newID = 1;
}
else
{
$newID++;
}

//On ajoute en base de données les données venant d'être testées.
mysql_query("INSERT INTO `Clients` VALUES('".$newID."', '".$nom."', '".$pnom."', '".$adr."', '".$cp."', '".$ville."', '".time()."');");
}
else
{
echo '[Erreur] Le prénom doit être saisi';
}
}
else
{
echo '[Erreur] Le nom doit être saisi';
}
}

// Supprime toute trace du client ayant le numéro passé en paramètre.
function delClient($num)
{
//On supprime le client en question.
mysql_query("DELETE FROM `Clients` WHERE numClient=".$num);

//On supprime toutes les associations entre les produits et les commandes du client puisqu'on va supprimer ses commandes.
mysql_query("
DELETE
FROM `Concerner`
WHERE `numCommande` IN
(SELECT `numCommande` FROM `Commandes` WHERE numClient=".$num.")");

//On supprime les commandes anciennement passées par le client que l'on vient de supprimer.
mysql_query("DELETE FROM `Commandes` WHERE numClient=".$num);
}
?>

II-C - Pourquoi ce programme est-il source d'erreurs ?

Lors de la manipulation des données saisies par un utilisateur, plusieurs risques nous menacent. Le premier est que très souvent, nous comptons sur la saisie de l'utilisateur, alors qu'il est fort probable qu'il se trompe. Le deuxième risque concerne la sécurité, en particulier sur le web. Ainsi s'il n'y a pas de contrôle de saisie, la possibilité est laissée à l'utilisateur de saisir volontairement des valeurs erronées afin de provoquer un comportement anormal du programme.

Quant au troisième, ce n'est pas tant un risque qu'une action de vérification supplémentaire. En effet, lorsqu'il y aura action sur les données stockées, même s'il y a cohérence, il y aura une étape de vérification afin d'être sûr que la modification n'en engendre pas d'autres. Et ce, afin de garder une intégrité entre les données. Par exemple, ici il faut faire trois actions DELETE pour supprimer définitivement un client de la base de données.

Tout ceci représente beaucoup de travail. Ce qui vient en plus du but premier de l'application. L'idéal serait de pouvoir se décharger de ces différents contrôles, ce qui représente souvent plus de la moitié du code, pour se concentrer sur l'application elle-même.

III - Les contraintes

Les contraintes sont des solutions faciles à mettre en place et qui permettent au SGBD d'être autonome dans sa gestion de l'intégrité des données. Ces contraintes sont une modification de la structure des tables, et se rajoutent en SQL. Cependant attention, la diversité des SGBD est grande, et en fonction de celui utilisé une contrainte peut ne pas exister. En effet, même si elle est acceptée par le SGBD elle ne sera pas forcement appliquée par celui-ci.
Il convient donc de vérifier son existence dans la documentation officielle du SGBD utilisé.
Pour cet article, le SGBD utilisé est MySQL. Il dispose de plusieurs moteurs proposant différentes fonctionnalités. Celui par défaut s'appelle MyISAM. Afin de mieux gérer l'intégrité, nous allons utiliser un moteur plus adapté : InnoDB.
Pour demander à MySQL son utilisation, la syntaxe suivante doit être utilisée :

CREATE TABLE `laTable` ( ... ) ENGINE = `innoDB`;

III-A - Propriétés des champs (NULL/NOT NULL et/ou DEFAULT)

Ce ne sont pas véritablement des contraintes, ce sont des propriétés sur les champs de la base de données.

III-A-1 - NULL/NOT NULL

L'attribut NULL permet d'autoriser la valeur non saisie, le SGBD acceptera qu'il n'y ait pas de donnée assignée à ce champ (donnée nulle)
En ce qui concerne l'attribut NOT NULL, le SGBD ne peut accepter un tuple que si une valeur est saisie et donnée pour ce champ.

Si on ne précise rien, NULL est utilisé par défaut, mais il est conseillé de le spécifier.

III-A-2 - DEFAULT

L'attribut DEFAULT permet, comme son nom l'indique, de définir une valeur par défaut. Si une valeur n'est pas précisée lors d'un INSERT INTO ou d'un UPDATE, la valeur définie par défaut sera insérée. On peut utiliser un DEFAULT avec un NULL ou NOT NULL. En effet un DEFAULT définissant une valeur par défaut, il évite un refus de la part du SGBD en cas de non-saisie d'un champ NOT NULL.
On peut demander que le champ ne soit pas vide, et si ce n'est pas le cas, au lieu de refuser l'enregistrement, on insère la valeur par défaut.

Un attribut DEFAULT particulier est l'AUTO_INCREMENT. Il permet de créer un entier toujours plus grand que celui généré précédemment. Il permet de nous affranchir de la création des identifiants de tables.

III-A-3 - Un exemple

CREATE TABLE `laTable`
(
`ident` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, --PRIMARY KEY est indispensable pour mette AUTO_INCREMENT, nous verrons plus tard son rôle exact
`uneChaineObligatoire` VARCHAR(20) NOT NULL,
`unPrix` FLOAT NOT NULL DEFAULT 0,
`dateTuple` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = `innoDB`;

III-B - Où écrire la contrainte ?

Nous allons commencer à parler de véritables contraintes. Les contraintes peuvent être nommées (et donc déclarées) à la fin du champ (comme les attributs NULL, NOT NULL et DEFAULT) grâce au mot clé CONSTRAINT. S'il est placé en fin de champ, dans ce cas, la contrainte porte sur le champ à la fin duquel elle est placée.

Une contrainte peut être écrite en dehors de toute définition de champs. Le mot clé CONSTRAINT sera alors utilisé après les définitions de champs.

Attention il serait faux de penser que le mot clé CONSTRAINT permet de créer une contrainte, il permet seulement de lui donner un nom. Si ce n'est pas obligatoire de nommer la contrainte, il est conseillé de le faire. Si ce n'est pas fait, le choix est laissé au SGDB et il sera très difficile de s'y retrouver par la suite.

exemple

CREATE TABLE `laTable`
(
`ident` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
CONSTRAINT `nomContrainte` ..., -- contrainte sur ident
`uneChaineObligatoire` VARCHAR(20) NOT NULL,
`unPrix` FLOAT NOT NULL DEFAULT 0,
`dateTuple` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT `nomContrainte2` ... -- contrainte sur n'importe quel champ que l'on précisera
) ENGINE = `innoDB`;

Ici les contraintes sont placées à la fin des champs et les exemples suivants seront développés de la même manière.

III-C - Les contraintes à la création

structure de contrainte sur une table

CONSTRAINT `nomContrainte`
{
{UNIQUE | PRIMARY KEY | FOREIGN KEY}
[ REFERENCES `nomTable` (`nomChamp`) [ON DELETE action] [ON UPDATE action] ]
| CHECK (operation booléene)

Les contraintes
UNIQUE : demande au SGBD de vérifier que ce champ soit unique, on peut être sûr que tous les enregistrements d'un champ unique seront différents (utile pour les login par exemple).
PRIMARY KEY : on spécifie au SGBD la clé primaire de la table, elle sera par ailleurs unique et une seule contrainte PRIMARY KEY sera autorisée par table (ce qui ne veut pas dire qu'elle ne peut pas être composée de plusieurs champs).
FOREIGN KEY : on spécifie au SGBD une clé étrangère. Elle se rattache donc à un champ d'une autre table, et elle doit faire référence à un champ unique ou une clé primaire.
REFERENCES : définit la référence d'une clé étrangère (FOREIGN KEY).
CHECK : effectue une opération booléenne qui doit être vérifiée sur l'ensemble des tuples déjà présents.

Les actions
ON DELETE action : déclenche une action qui sera effectuée à la suppression d'un tuple référence.
ON UPDATE action : déclenche une action qui sera effectuée à la modification d'un tuple référence.
NO ACTION : demande par défaut, au SGBD de ne rien faire, il retournera donc une erreur si la cohérence des données n'est plus respectée.
CASCADE : répercute les suppressions/modifications.
SET NULL : quand une référence est supprimée ou modifiée, la valeur est marquée à NULL, c'est-à-dire l'absence de valeur.
SET DEFAULT : quand une référence est supprimée ou modifiée, la valeur est remise par défaut.

Attention le CHECK est accepté par MySQL, mais pas appliqué.

Exemple

CREATE TABLE `laTable1`
(
`identTable1` INTEGER NOT NULL AUTO_INCREMENT,
`uneChaineObligatoire` VARCHAR(20) NOT NULL,
`unPrix` FLOAT NOT NULL DEFAULT 0,
`dateTuple` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT `PK_laTable1` PRIMARY KEY (`identTable1`)
) ENGINE = `innoDB`;

CREATE TABLE `laTable2`
(
`identTable2` INTEGER NOT NULL AUTO_INCREMENT,
`uneInformation` VARCHAR(30) NULL,
`idTable1` INTEGER NOT NULL,
CONSTRAINT `PK_laTable2` PRIMARY KEY (`identTable2`),
CONSTRAINT `FK_laTable2_laTable1` FOREIGN KEY (`idTable1`) REFERENCES laTable1(`identTable1`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = `innoDB`;

III-D - Les ajouts et suppressions de contraintes

Il est possible d'ajouter ou de supprimer des contraintes. Voici la syntaxe :

Syntaxe

ALTER TABLE `laTable`
ADD CONSTRAINT <la contrainte>;

ALTER TABLE `laTable`
DROP CONSTRAINT <nomContrainte>;

Exemple

CREATE TABLE `laTable1`
(
`identTable1` INTEGER NOT NULL,
`uneChaineObligatoire` VARCHAR(20) NOT NULL,
`unPrix` FLOAT NOT NULL DEFAULT 0,
`dateTuple` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = `innoDB`;

CREATE TABLE `laTable2`
(
`identTable2` INTEGER NOT NULL,
`uneInformation` VARCHAR(30) NULL,
`idTable1` INTEGER NOT NULL
) ENGINE = `innoDB`;

ALTER TABLE `laTable1`
ADD CONSTRAINT `PK_laTable1` PRIMARY KEY (`identTable1`);

ALTER TABLE `laTable2`
ADD CONSTRAINT `PK_laTable2` PRIMARY KEY (`identTable2`),
ADD CONSTRAINT `FK_laTable2_laTable1` FOREIGN KEY (`identTable2`) REFERENCES `laTable1` (`identTable1`);

ALTER TABLE `laTable2`
DROP PRIMARY KEY,
DROP FOREIGN KEY `FK_laTable2_laTable1`;

ALTER TABLE `laTable1`
DROP PRIMARY KEY;

Souvent beaucoup de générateurs de scripts SQL, et de programmeurs commencent par créer leurs tables, puis posent les contraintes juste après.

Ceci a pour but d'éviter que le SGBD gêne à la création des tables.

IV - Le nouveau code

IV-A - Le SQL de création modifié pour gérer les contraintes

Nouvelle base de données

CREATE TABLE `Clients`
(
`numClient` INTEGER AUTO_INCREMENT,
`nomClient` VARCHAR(20) NOT NULL,
`prenomClient` VARCHAR(20) NOT NULL,
`adresseClient` VARCHAR(100) NULL,
`cpClient` VARCHAR(5) NULL,
`villeClient` VARCHAR(30) NULL,
`dateInscription` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT PRIMARY KEY (`numClient`)
) ENGINE = `innoDB`;

CREATE TABLE `Produits`
(
`numProduit` INTEGER AUTO_INCREMENT,
`libelleProduit` VARCHAR(50) NOT NULL,
`prixProduit` FLOAT NOT NULL,
CONSTRAINT PRIMARY KEY (`numProduit`)
) ENGINE = `innoDB`;

CREATE TABLE `Commandes`
(
`numCommande` INTEGER AUTO_INCREMENT,
`dateCommande` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`numClient` INTEGER NOT NULL,
CONSTRAINT PRIMARY KEY (`numCommande`)
) ENGINE = `innoDB`;

CREATE TABLE `Concerner`
(
`numProduit` INTEGER NOT NULL,
`numCommande` INTEGER NOT NULL,
`nombreProduit` INTEGER NOT NULL DEFAULT 0,
CONSTRAINT PRIMARY KEY (`numProduit`,`numCommande`)
) ENGINE = `innoDB`;

ALTER TABLE `Commandes`
ADD CONSTRAINT `FK_Commandes_Clients` FOREIGN KEY (`numClient`) REFERENCES `Clients` (`numClient`)
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE `Concerner`
ADD CONSTRAINT `FK_Concerner_Produits` FOREIGN KEY ( `numProduit` ) REFERENCES `Produits` (`numProduit`)
ON UPDATE CASCADE
ON DELETE CASCADE,
ADD CONSTRAINT `FK_Concerner_Commandes` FOREIGN KEY ( `numCommande` ) REFERENCES `Commandes` (`numCommande`)
ON UPDATE CASCADE
ON DELETE CASCADE;

IV-B - Code php allégé

<?php
//Ajoute un nouveau tuple ayant pour information les paramètres.
function addClient($nom, $pnom, $adr, $cp, $ville)
{
$sqlNom = (!empty($nom))?'\''.$nom.'\'':'NULL';
$sqlPnom = (!empty($pnom))?'\''.$pnom.'\'':'NULL';

//On ajoute en base de données les données non vérifiées en ne spécifiant ni l'identifiant, ni la date.
mysql_query("INSERT INTO `Clients` VALUES('', ".$sqlNom.", ".$sqlPnom.", '".$adr."', '".$cp."', '".$ville."', NULL);");
// Le SGBD contrôlera lui-même les données

// On récupère les retours d'erreurs du SGBD
if(mysql_errno() == 1048) // un champ est vide
{
$msg = explode('\'', mysql_error(), 3);
switch ($msg[1])
{
case 'prenomClient':
echo '[Erreur] Le prénom doit être saisi';
break;
case 'nomClient':
echo '[Erreur] Le nom doit être saisi';
break;
}
}
}

// Supprime toute trace du client ayant le numéro passé en paramètre.
function delClient($num)
{
//On supprime le client en question.
mysql_query("DELETE FROM `Clients` WHERE numClient=".$num);
/* Plus rien d'autre à faire, le SGBD a vu que les tuples rattachés à ce
client n'étaient plus utiles, il les a donc supprimés grâce à la foreing key
et au ON DELETE CASCADE */
}
?>

IV-C - Précautions à prendre avec ce type de développement

Il est très avantageux de laisser le contrôle de cohérence de données au SGBD. Cependant, il faut faire très attention de bien modéliser sa base de données, car si elle n'est pas assez précise, il se peut que l'application soit faillible ou bien même boguée.

Les contraintes de base que nous venons de voir ne permettent pas de faire des miracles. Toutefois, il est possible d'aller plus loin en utilisant des procédures stockées et des trigger.
La philosophie de programmation reste la même, on réduit la ligne de code afin de reporter le travail sur la base de données.
L'utilisation du SQL procédural permet une gestion poussée des erreurs liées au domaine de gestion, c'est-à-dire l'aspect métier de notre application.

V - Remerciements

Merci à yiannis, xave, Fleur-Anne.Blain et ced pour les relectures.