Les nouveautés de MySQL 5.0

Date de publication : 03/11/2005

La dernière version de production de MySQL, sortie en Octobre 2005, représente certainement l'évolution la plus significative de ce SGBD depuis ses débuts. Cet article vous propose d'explorer ses principales nouvelles fonctionnalités de manière concrète.

Introduction
Un peu d'exercice
A. Les procédures stockées
1. Définition
2. En pratique
3. Restrictions
B. Les triggers
1. Définition
2. En pratique
3. Restrictions
C. Les vues
1. Définition
2. En pratique
3. Restrictions
D. Le dictionnaire INFORMATION_SCHEMA
1. Définition
2. En pratique
Conclusion

Introduction

Bien que MySQL 5 compte encore des lacunes importantes, les progrès effectués en termes de respect du standard SQL ne sont pas négligeables. En effet, le SGBD prend désormais en charge les fonctionnalités précieuses que sont les procédures stockées, les vues, ainsi que des triggers rudimentaires et dans une moindre mesure le dictionnaire de données information_schema.

Je vous propose d'aborder ces mécanismes à travers des définitions techniques et un cas pratique.

Un peu d'exercice

Pour illustrer tout cela, prenons un exemple concret et plutôt ludique : la gestion des matches de Ligue 1 du championnat de France de football. Site web de pronostics, portail sportif, jeu de foot, à vous d'imaginer le contexte...

Pour l'instant nous allons nous contenter de 2 tables simples : celle des equipes et celle des rencontres. Voici les ordres de création de la base :

Création de la base ligue1

CREATE DATABASE ligue1;

CREATE TABLE ligue1.equipe (
id_equipe int primary key auto_increment,
nom varchar(50) not null,
stade varchar(50) not null,
ville varchar(30) not null,
points int not null default 0,
buts int not null default 0
) ENGINE=MyISAM;

CREATE TABLE ligue1.rencontre (
id_domicile int,
id_visiteurs int,
date_match DATETIME,
score_domicile tinyint,
score_visiteurs tinyint,
arbitre varchar(80),
primary key (id_domicile, id_visiteurs, date_match)
) ENGINE=MyISAM;

Les données ressembleront à cela :

Equipes

Rencontres

Jusqu'ici, rien de bien sorcier. Plongeons-nous maintenant dans les possibilités offertes par MySQL 5.

A. Les procédures stockées

1. Définition

Les procédures et fonctions stockées (Stored Procedures, en abrégé SP) sont du code applicatif entreposé et exécuté au sein du serveur de base de données. Ce code combine commandes SQL, structures de contrôle, variables, tout comme une fonction ou méthode dans n'importe quel langage de programmation. Les procédures MySQL sont écrites selon la syntaxe standard SQL:2003.
Mais pourquoi confier au SGBD des tâches normalement dévolues à l'application cliente, me direz-vous ? Cela présente essentiellement 3 avantages :

Rapidité. La séquence des traitements contenus dans une procédure stockée est exécutée d'un bloc, au sein même du SGBD. Par rapport à un système classique, on fait l'économie d'un certain nombre d'aller-retours de données entre l'application cliente et le serveur, et donc de trafic réseau. Par ailleurs certaines étapes préalables comme l'analyse syntaxique des requêtes SQL sont effectuées à la création de la procédure et ne sont plus nécessaires lors de l'exécution, qui s'en trouve accélérée.

Sécurité. Les procédures stockées limitent la marge de manoeuvre de l'utilisateur et permettent de maîtriser ses agissements. Il ne fait qu'appeler la procédure sans opérer directement sur les tables sous-jacentes, ce qui réduit par conséquent le risque de mauvaises manipulations.

Facilité de maintenance et portabilité. Avec les procédures stockées, une partie de la logique applicative est centralisée sur le serveur de bases de données ; cet emplacement unique facilite la création et la maintenance du code. De plus, toutes les applications clientes accèdent aux procédures de manière uniforme, ce qui constitue un niveau d'abstraction appréciable. Il en découle qu'on peut changer de technologie client sans réécrire quoi que ce soit de cette partie du code.

Attention toutefois, les procédures stockées ne sont pas une formule miracle et l'intérêt de déporter la logique applicative sur le serveur de base de données fait parfois débat. Les SP ne conviennent pas dans tous les cas de figure, en particulier pas lorsqu'il s'agit d'effectuer des opérations très simples.
Pour plus d'informations, je vous renvoie aux nombreuses discussions disponibles sur le web à ce sujet.

2. En pratique

Revenons à notre terrain de jeu et créons dans la base ligue1 une première procédure stockée :

Procédure INIT_EQUIPE

DELIMITER //

CREATE PROCEDURE ligue1.INIT_EQUIPE (nom_eq varchar(50), stade_eq varchar(50), ville_eq varchar(30))
BEGIN
   INSERT INTO equipe (nom, stade, ville) VALUES (nom_eq, stade_eq, ville_eq);
END//

DELIMITER ;

Comme vous pouvez le voir, la syntaxe est proche de ce que peut être une fonction ou une méthode dans un langage de programmation. La procédure prend en entrée des paramètres d'un type donné, son corps est délimité par les instructions BEGIN et END à l'intérieur desquelles on effectue un certain nombre de traitements.
Ici en l'occurrence, on se contente d'insérer une nouvelle équipe dont le nom, le stade et la ville ont été passés en paramètre.

Vous aurez remarqué le changement de délimiteur de commandes (DELIMITER //). En effet les ordres SQL dans une procédure stockée sont obligatoirement terminés par un point-virgule. Or, c'est déjà le caractère par défaut pour délimiter les commandes dans le client MySQL lui-même. Il y a donc un conflit entre les deux, et on est obligé de remplacer temporairement le deuxième, par un double-slash par exemple.
C'est une opération à faire systématiquement lors de la création d'une nouvelle procédure stockée, considérez donc qu'elle figure de manière implicite dans les exemples qui suivent.

Bon, nous avons créé la procédure mais maintenant, comment s'en servir ? Rien de plus simple. On utilise l'instruction SQL CALL sur la SP en donnant les valeurs voulues en paramètre :

mysql> CALL INIT_EQUIPE('FC Girondins de Bordeaux', 'Chaban-Delmas', 'Bordeaux');

Query OK, 1 row affected (0.02 sec)

Laissons là cette procédure stockée qui n'a pas grande utilité et passons aux matches. En toute logique, on va les renseigner en deux temps :

En début de saison, insertion des rencontres avec leurs dates prévues.
Modification avec saisie du score à l'issue de chaque match.

La première opération peut être effectuée via la procédure stockée suivante :

Procédure SAISIR_RENCONTRE

CREATE PROCEDURE tutoriel.SAISIR_RENCONTRE (id_dom INTEGER, id_vis INTEGER, date_m DATETIME, arbitre_m VARCHAR(80))
BEGIN
	INSERT INTO rencontre (id_domicile, id_visiteurs, date_match, arbitre)
VALUES (id_dom, id_vis, date_m, arbitre_m);
END

Et appelée comme ceci :

mysql> CALL SAISIR_RENCONTRE(1, 2, '2006-02-04', 'Frank Sifflet');

La gestion de l'issue du match va quand à elle faire l'objet d'une procédure plus complexe puisque la mise à jour ne concerne pas seulement la table rencontre. Elle affecte aussi les points du championnat, donc la table equipe :

Procédure SAISIR_RESULTAT

CREATE PROCEDURE `tutoriel`.`SAISIR_RESULTAT`(id_dom INTEGER, id_vis INTEGER, score_dom TINYINT(4), score_vis TINYINT(4))
BEGIN
	UPDATE rencontre 
	SET score_domicile = score_dom, score_visiteurs = score_vis
	WHERE id_domicile=id_dom AND id_visiteurs=id_vis;

	IF score_dom = score_vis THEN
		UPDATE equipe SET points = points+1 
		WHERE id_equipe=id_dom OR id_equipe=id_vis;
	ELSEIF score_dom > score_vis THEN
		UPDATE equipe SET points = points+3 WHERE id_equipe = id_dom;
	ELSE	UPDATE equipe SET points = points+3 WHERE id_equipe = id_vis;
	END IF;
END

mysql> CALL SAISIR_RESULTAT(4, 6, 2, 1);

En clair : on met à jour la table rencontre avec les valeurs des scores passées en paramètre, puis on rajoute un point à chaque équipe en cas de match nul, ou sinon 3 points au vainqueur. Les développeurs ne seront pas dépaysés par l'utilisation de la condition IF - THEN - ELSE qui reste ici assez classique.

Il peut également être intéressant d'initialiser tous les matches dès le début de saison, avant de les renseigner. Concrètement, cela signifie insérer une rencontre pour chaque couple équipe domicile/équipe visiteurs distinct :

Procédure INIT_RENCONTRES

CREATE PROCEDURE `tutoriel`.`INIT_RENCONTRES`()
BEGIN
    DECLARE fini INT default 0;
    DECLARE domicile, visiteur INT;

    DECLARE cur_domicile CURSOR FOR SELECT id_equipe FROM equipe;
    DECLARE cur_visiteur CURSOR FOR SELECT id_equipe FROM equipe;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;

    OPEN cur_domicile;

    WHILE fini <> 1 DO
        FETCH cur_domicile INTO domicile;
        IF fini=0 THEN
        	OPEN cur_visiteur;
       		 WHILE fini <> 1 DO
            			FETCH cur_visiteur INTO visiteur;
            			IF domicile <> visiteur AND fini <> 1 THEN
                			INSERT INTO rencontre (id_domicile, id_visiteurs) VALUES (domicile, visiteur);
            			END IF;
        		END WHILE;
        	CLOSE cur_visiteur;
        SET fini=0;
	END IF;
    END WHILE;
    CLOSE cur_domicile;
END

Ce code mérite quelques explications. Tout d'abord remarquez que la procédure ne prend aucun paramètre, ce qui est normal puisque tout ce dont on a besoin pour l'initialisation des matches se trouve dans la table equipe.

L'instruction DECLARE sert, comme son nom l'indique, à déclarer une variable interne à la procédure. Les déclarations doivent être placées au début, avant les instructions de traitement. On peut déclarer des variables de type de base (INT, VARCHAR, etc.) comme dans les 2 premières lignes mais aussi des structures plus complexes comme les curseurs (CURSOR) et les HANDLERS.

Un curseur est un objet permettant de parcourir séquentiellement le jeu de résultats retourné par une requête SQL.
En l'occurrence, nous avons besoin de créer deux curseurs cur_domicile et cur_visiteur portant sur la même requête SELECT id_equipe FROM equipe. Le parcours du deuxième curseur est placé à l'intérieur du premier, de la même manière que nous aurions imbriqué deux boucles for dans un autre langage pour passer en revue toutes les combinaisons de couples (equipe1, equipe2).
Ainsi, pour chaque équipe domicile on parcourt toutes les équipes visiteuses qui vont jouer contre elle (c'est à dire toutes sauf elle-même) et on insère un nouveau match à chaque fois.

Le curseur est ouvert via l'ordre OPEN et fermé avec CLOSE. La syntaxe d'itération sur un curseur est un peu particulière ; elle repose sur une simple boucle WHILE dans laquelle on fait avancer le curseur (FETCH), et sur un HANDLER. Ce dernier sert à déterminer ce qui se passe lorsque le SQLSTATE NOT FOUND est atteint, c'est à dire quand le curseur est au bout du jeu de résultats. En général on sort de la boucle WHILE.

On remarquera que cette implémentation assez spéciale des curseurs nous oblige à effectuer certaines contorsions (manipulations alambiquées de la variable fini)... C'est en particulier dû au fait que malgré les deux curseurs il ne peut y avoir qu'un handler sur l'événement NOT FOUND et donc une seule variable témoignant successivement de l'état de l'un ou l'autre des deux curseurs. Cela oblige entre autres à réinitialiser cette variable avant qu'elle resserve pour le tour de boucle suivant.

Fonctions stockées : les fonctions se distinguent des procédures stockées par leur valeur de retour unique et obligatoire, et par la façon particulière de les exécuter.
En effet plus question de CALL, les fonctions sont utilisables directement à l'intérieur de requêtes SQL de DML (SELECT, INSERT, UPDATE...) ce qui leur confère une grande souplesse.

Une fonction bien utile pourrait par exemple être de savoir si une équipe passée en paramètre est dans les trois dernières du classement, et donc potentiellement relégable en ligue 2 :

Fonction EST_RELEGABLE

CREATE FUNCTION EST_RELEGABLE(idequipe INTEGER) RETURNS TINYINT
BEGIN
	DECLARE relegable TINYINT default 0;
	DECLARE id_courante INTEGER;
	DECLARE fini TINYINT DEFAULT 0;
	DECLARE cur1 CURSOR FOR SELECT id_equipe FROM equipe ORDER BY points ASC LIMIT 3;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET fini = 1;
	OPEN cur1;
    BOUCLE: REPEAT
          FETCH cur1 INTO id_courante;
          IF idequipe=id_courante THEN
              SET relegable=1;
              LEAVE BOUCLE;
          END IF;
    UNTIL fini END REPEAT BOUCLE;
    CLOSE cur1;
	RETURN relegable;
END

On peut désormais appeler la fonction là où ça nous chante :

SELECT * FROM equipe WHERE EST_RELEGABLE(id_equipe)=1

donne un aperçu des équipes relégables.

DELETE FROM equipe WHERE EST_RELEGABLE(id_equipe)=1

permet de supprimer, en fin de saison par exemple, les équipes relégables.

3. Restrictions

Le support des procédures stockées est sans aucun doute un grand pas pour MySQL, mais il faut dire qu'en la matière cette version 5 ne fait pas dans le pléthorique.

Il manque certaines structures comme la boucle FOR, les curseurs sont limités (lecture seule, non-scrollables) et peu pratiques d'utilisation comme on l'a vu avec les handlers. Plus gênant, on déplorera que les SP soient quasi-autistes vis-à -vis de leur environnement : pas de possibilité directe d'envoyer des messages système ni de gérer ou déclencher des erreurs, sans parler de fonctions plus avancées comme la manipulation de fichiers. Un certain nombre d'ordres de maintenance tels que CHECK, LOAD DATA ou OPTIMIZE y sont eux aussi prohibés.

En revanche bonne nouvelle, les ordres transactionnels (COMMIT, ROLLBACK) du moteur InnoDB sont autorisés à l'intérieur des SP MySQL.

B. Les triggers

1. Définition

Pour faire simple, les triggers (ou déclencheurs) sont des procédures qui s'activent automatiquement lorsqu'un événement donné se produit sur une table. En d'autres termes, quand une requête INSERT, UPDATE ou DELETE survient, on se réserve la posibilité d'agir avant ou après son exécution et éventuellement de modifier voire d'annuler son action.

Cet outil puissant a souvent pour rôle de vérifier certaines contraintes d'intégrité sophistiquées que les contraintes SQL basiques (PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT...) sont incapables de gérer.
Quelques exemples : un client peut avoir au maximum 5 comptes bancaires (relation de cardinalité 1,5) ; un achat peut être relié soit à une carte de fidélité soit à un coupon de réduction mais pas les deux ; il doit y avoir un intervalle de 15 minutes entre les horaires de décollage de deux avions sur la même piste ; etc. Tout ceci peut être contrôlé par des déclencheurs.

Les triggers peuvent aussi servir à mettre à jour automatiquement certaines tables en fonction des modifications qui sont faites, ou à formater des données comme nous allons le voir tout de suite.

2. En pratique

Créons un premier déclencheur qui va mettre la ville en majuscules lors de l'insertion d'une nouvelle équipe :

Trigger TGR_BI_EQUIPE

CREATE TRIGGER TGR_BI_EQUIPE BEFORE INSERT ON equipe FOR EACH ROW
BEGIN	
	SET new.ville = UPPER(new.ville);
END

Comme pour les procédures stockées, le code du trigger est placé dans un bloc BEGIN ... END. Le mot-clé BEFORE indique que l'on souhaite agir avant que l'insertion ne soit réalisée.
La table telle qu'elle sera après l'exécution du INSERT est désignée par l'alias new, ce qui veut dire qu'on accède aux valeurs qui vont être insérées via new.nom_colonne. Modifier la valeur de new.nom_colonne revient à transformer la requête INSERT d'origine selon nos exigences, c'est ce qu'on fait ici en forçant la mise en majuscules.
Voyons si ça fonctionne :

mysql> INSERT INTO equipe (nom, stade, ville) VALUES ('Racing Club Strasbourg', 'La Meinau', 'strasbourg');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT nom, stade, ville FROM equipe;

+--------------------------+-------------------------+---------------+
| nom                      | stade                   | ville         |
+--------------------------+-------------------------+---------------+
| ...                      |                ...      |      ...      |
| Racing Club Strasbourg   | La Meinau               | STRASBOURG    |
+--------------------------+-------------------------+---------------+

Notre trigger a donc bien fait son oeuvre.

Vous aurez peut-être remarqué que dans la procédure stockée SAISIR_RESULTAT nous ne nous sommes pas souciés de la colonne equipe.buts. En effet, comme cette donnée totalise les buts marqués par l'équipe durant le championnat, on peut tout à fait la mettre à jour à l'aide d'un déclencheur sur la table rencontre (qui sera donc activé par SAISIR_RESULTAT) :

Trigger TGR_BU_RENCONTRE

CREATE TRIGGER TGR_BU_RENCONTRE BEFORE UPDATE ON rencontre FOR EACH ROW
BEGIN	
	IF old.score_domicile IS NULL and new.score_domicile IS NOT NULL THEN
		UPDATE equipe SET buts = buts + new.score_domicile WHERE id_equipe=new.id_domicile;
	END IF;
	IF  old.score_visiteurs IS NULL and new.score_visiteurs IS NOT NULL THEN
		UPDATE equipe SET buts = buts + new.score_visiteurs WHERE id_equipe=new.id_visiteurs;
	END IF;
END

A l'inverse de new, old fait référence à la table dans l'état où elle était juste avant que le trigger se déclenche. Dans notre exemple, on s'en sert pour savoir si le score vient d'être rempli. En effet l'UPDATE pourrait simplement modifier le nom de l'arbitre ou la date, auquel cas equipe.buts ne doit pas être mis à jour. Vérifions maintenant l'efficacité du dispositif :

mysql> SELECT nom, stade, points, buts FROM equipe WHERE id_equipe=1 OR id_equipe=2;
+------------+-----------------+--------+------+
| nom        | stade           | points | buts |
+------------+-----------------+--------+------+
| AC Ajaccio | René-Coty       |      0 |    0 |
| AJ Auxerre | l'Abbé-Deschamp |      0 |    0 |
+------------+-----------------+--------+------+
2 rows in set (0.02 sec)

mysql> CALL SAISIR_RESULTAT(1, 2, 2, 1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT nom, stade, points, buts FROM equipe WHERE id_equipe=1 OR id_equipe=2;
+------------+-----------------+--------+------+
| nom        | stade           | points | buts |
+------------+-----------------+--------+------+
| AC Ajaccio | René-Coty       |      3 |    2 |
| AJ Auxerre | l'Abbé-Deschamp |      0 |    1 |
+------------+-----------------+--------+------+
2 rows in set (0.00 sec)

Là aussi, on constate bien que les buts ont été mis à jour en fonction du score qui vient d'être saisi.

3. Restrictions

Comme je vous le disais en introduction, les triggers de MySQL 5 sont assez rudimentaires et ont un intérêt limité au regard de leurs carences.

Tout d'abord vérifier des contraintes d'intégrité implique d'avoir le pouvoir de "dire non" à une requête lorsqu'elle les viole. Ceci se traduit en général par le déclenchement d'une exception système qui fait avorter la requête en cours, et par le retour d'un message d'erreur SQL personnalisé. Hélas, les procédures et déclencheurs MySQL ne permettent pour l'instant ni l'un ni l'autre. On ne peut pas non plus utiliser un ordre ROLLBACK dans le trigger pour annuler la transaction en cours.
Le seul palliatif à cela consiste à provoquer artificiellement une erreur, par exemple en exécutant une requête erronée comme SELECT FROM mon_message_d_erreur, ce qui n'est évidemment pas du tout catholique.
Dommage, car cette fonctionnalité est indispensable dans une grande partie des déclencheurs les plus utiles.

Un certain nombre d'autres limitations achèvent malheureusement de reléguer les "gâchettes" de la 5.0 au rang de pistolets en plastique :

Limitation à un seul trigger par table et par type d'événement.
Pas de possibilité de combiner plusieurs événements (INSERT OR UPDATE...)
Pas de clause WHEN.
Uniquement triggers FOR EACH ROW (déclencheurs niveau ligne).
Pas de déclencheurs sur des événements système autres que des requêtes SQL.
etc.

C. Les vues

1. Définition

D'un point de vue logique, une vue est une sorte de table virtuelle dont le contenu correspond au résultat d'une requête SELECT. Elle se comporte comme une table en ce qui concerne l'accès à ses données, c'est à dire qu'on peut exécuter des ordres SELECT, voire UPDATE ou DELETE dessus.

Les vues ont plusieurs utilités. D'une part elles constituent une commodité d'écriture pour l'utilisateur : une requête SELECT très complexe peut être manipulée à travers un nom de vue qui tient en un mot.
Par ailleurs, les vues sont une façon simple et pratique de considérer les données contenues dans les tables sous un angle différent et personnalisé. Ca permet aussi de définir des privilèges beaucoup plus fins que de simples GRANT sur les tables. Par exemple, on peut refuser les droits à un utilisateur sur une table, mais à travers une vue adéquate lui autoriser la consultation d'un nombre limité de colonnes ou de lignes issues de cette table.

Physiquement, on ne stocke pas durablement les données d'une vue mais juste sa définition. Lorsqu'un ordre SQL (a) va interroger une vue dont la définition est une requête (b), il y a deux possibilités sous MySQL 5.0 :

L'algorithme MERGE est utilisé. (a) et (b) sont combinées pour former une nouvelle requête qui sera exécutée de façon classique.
On opte pour l'algorithme TEMPTABLE. Une table temporaire est créée avec les résultats de (b). La requête (a) est exécutée en se basant sur cette table temporaire. Cela a l'avantage de moins solliciter les tables physiques sous-jacentes.

On peut spécifier un algorithme à la création de la vue (ALGORITHM = MERGE | TEMPTABLE) ou ne rien définir et laisser le moteur choisir au cas par cas.

2. En pratique

Comme un exemple vaut mieux qu'un long discours, voyons ce que cela peut apporter à notre base ligue1.

On peut tirer parti des vues pour avoir un aperçu de la dernière journée de championnat :

Vue V_DERNIERES_RENCONTRES

CREATE VIEW V_DERNIERES_RENCONTRES AS
	SELECT r.date_match, d.nom as nom_domicile, r.score_domicile, v.nom as nom_visiteurs, r.score_visiteurs, r.arbitre
  	FROM equipe d, rencontre r, equipe v
	WHERE r.id_domicile = d.id_equipe AND r.id_visiteurs = v.id_equipe
		AND r.date_match <= CURDATE()
	ORDER BY r.date_match DESC
	LIMIT 10

La syntaxe est relativement simple : CREATE VIEW NOM_VUE suivi de AS et de la requête sur laquelle on veut se baser. La vue peut être interrogée grâce à un simple SELECT, ce qui donne l'impression d'avoir une vraie table personnalisée contenant les 10 derniers matches :

mysql> SELECT * FROM V_DERNIERES_RENCONTRES;
+---------------------+--------------------------+----------------+----------------------+-----------------+---------------+
| date_match          | nom_domicile             | score_domicile | nom_visiteurs        | score_visiteurs | arbitre       |
+---------------------+--------------------------+----------------+----------------------+-----------------+---------------+
| 2005-10-15 00:00:00 | FC Girondins de Bordeaux |              1 | FC Sochaux          |               1 | René Carton |
| 2005-10-15 00:00:00 | AJ Auxerre               |              3 | Troyes ESTAC         |               0 |John Maninblack  |
| 2005-10-15 00:00:00 | Le Mans Union Club 72    |              1 | LOSC Lille M?tropole |               1 | Frank Sifflet |
|             ...     |                    ...   |            ... |               ...    |             ... |         ...   |
+---------------------+--------------------------+----------------+----------------------+-----------------+---------------+
10 rows in set (0.01 sec)

Notez qu'un UPDATE ou un DELETE sur cette vue déclenchera une erreur car elle met en jeu plusieurs tables avec des jointures. Le moteur serait bien en peine de savoir quoi modifier et dans quelles tables, quand bien même la modification aurait un sens.

Supposons maintenant que notre projet quel qu'il soit ait pris de l'importance et qu'on ait été amené à contacter les entraineurs de plusieurs clubs. On veut stocker leur numéro de téléphone personnel dans la table equipe :

ALTER TABLE equipe 
ADD entraineur varchar(100) default 'inconnu',
ADD tel_entraineur varchar(20) default 'inconnu';

UPDATE equipe SET entraineur='Ricardo GOMES', tel_entraineur='06-56-56-56-56' WHERE id_equipe=4;
UPDATE equipe SET entraineur='Gérard Houllier', tel_entraineur='06-57-57-57-57' WHERE id_equipe=9;

Cependant, ces numéros de téléphone sont des données confidentielles qui ne doivent pas être accessibles aux utilisateurs lambda. On va donc créer une vue où ne figure pas cette colonne :

Vue V_EQUIPE

CREATE VIEW V_EQUIPE AS
SELECT id_equipe, nom, stade, ville, points, buts, entraineur FROM equipe

Donnons les droits sur cette vue à un nouvel utilisateur :

GRANT SELECT, INSERT, DELETE, UPDATE ON V_EQUIPE
TO user1@localhost IDENTIFIED BY 'user1'

Et vérifions l'efficacité de notre système :

> mysql -u user1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31 to server version: 5.0.13-rc-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use ligue1;
Database changed
mysql> SELECT * FROM V_EQUIPE;

+-----------+--------------------------+-------------------------+---------------+--------+------+-----------------+
| id_equipe | nom                      | stade                   | ville         | points | buts | entraineur      |
+-----------+--------------------------+-------------------------+---------------+--------+------+-----------------+
|         1 | AC Ajaccio               | René-Coty               | Ajaccio       |      8 |    3 | inconnu         |
|         2 | AJ Auxerre               | l'Abbé-Deschamp         | Auxerre       |      7 |    2 | inconnu         |
|         3 | Le Mans Union Club 72    | Leon-Bollée             | Le Mans       |      6 |    3 | inconnu         |
|         4 | FC Girondins de Bordeaux | Chaban-Delmas           | Bordeaux      |      5 |    4 | Ricardo GOMES   |
|         5 | Troyes ESTAC             | Stade de l'Aube         | Troyes        |      5 |    0 | inconnu         |
|         6 | FC Sochaux               | Auguste Bonal           | Sochaux       |      4 |    0 | inconnu         |
|         7 | RC Lens                  | Félix-Bollaert          | Lens          |      6 |    0 | inconnu         |
|         8 | LOSC Lille Métropole     | Stadium Lille M?tropole | Lille         |      6 |    4 | inconnu         |
|         9 | Olympique lyonnais       | Gerland                 | Lyon          |      0 |    0 | Gérard Houllier |
|        10 | Olympique de Marseille   | Vélodrome               | Marseille     |      0 |    0 | inconnu         |
|        11 | FC Metz                  | Saint-Symphorien        | Metz          |      8 |    0 | inconnu         |
|        12 | AS Monaco                | Louis II                | Monaco        |      1 |    0 | inconnu         |
|        13 | FC Nantes                | La Beaujoire            | Nantes        |      0 |    0 | inconnu         |
|        16 | AS Saint-Etienne         | Geoffroy-Guichard       | SAINT-ETIENNE |      0 |    0 | inconnu         |
| ...
+-----------+--------------------------+-------------------------+---------------+--------+------+-----------------+
20 rows in set (0.00 sec)

mysql> SELECT * FROM equipe;
ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'equipe'

L'utilisateur user1 a donc seulement accès aux colonnes que l'administrateur a jugées utiles pour lui. Ce système amène une souplesse de contrôle très appréciable.

3. Restrictions

Les vues semblent être la nouvelle fonction la plus aboutie de cette version. On peut définir leur algorithme sous-jacent, les vues correspondant à une table unique sont updatables (avec possibilité de CHECK OPTION) et elles peuvent porter sur d'autres vues.
Par contre les vues ne peuvent pas :

Avoir d'index propres.
Utiliser d'index lorsque leur algorithme est TEMPTABLE. Seule une opération MERGE utilise les index des tables sous-jacentes.
Porter sur des tables temporaires.
Avoir de sous-requête dans la clause FROM de leur définition (SELECT * FROM (SELECT...) ).
Se voir associer des triggers.

A l'exception de la première, ces limitations ne devraient pas s'avérer trop gênantes.

D. Le dictionnaire INFORMATION_SCHEMA

1. Définition

La norme SQL propose d'accéder aux méta-données d'une base via le dictionnaire de données INFORMATION_SCHEMA. Dans MySQL 5.0 celui-ci est une base système à part entière au même titre que mysql. Il représente un double progrès par rapport aux versions précédentes : respect du standard contrairement aux commandes SHOW auparavant utilisées, et facilité d'interrogation via de simples requêtes SELECT.
Passons en revue les méta-informations qu'on y trouve (chaque catégorie correspond à une table):

SCHEMATA : informations sur les bases (ou schémas).
TABLES : les tables.
COLUMNS : leurs colonnes.
STATISTICS : les indexes.
USER_PRIVILEGES : privilèges globaux.
SCHEMA_PRIVILEGES : privilèges sur les bases.
TABLE_PRIVILEGES : privilèges sur les tables.
COLUMN_PRIVILEGES : privilèges sur les colonnes.
CHARACTER_SETS : jeux de caractères.
COLLATIONS : interclassements.
COLLATION_CHARACTER_SET_APPLICABILITY : correspondances interclassements / charsets.
TABLE_CONSTRAINTS : contraintes des tables.
KEY_COLUMN_USAGE : clés primaires et étrangères.
ROUTINES : les procédures et fonctions stockées.
VIEWS : les vues.
TRIGGERS : les déclencheurs.

2. En pratique

Voyons l'utilisation du dictionnaire de données à travers deux exemples.

Tout d'abord, il peut être appréciable pour un utilisateur du SGBD de connaître les privilèges qu'il a sur les différentes tables. C'est ce que notre user1 va faire :

mysql> use INFORMATION_SCHEMA;
Database changed
mysql> SELECT * FROM TABLE_PRIVILEGES;
+---------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE             | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------------------+---------------+--------------+------------+----------------+--------------+
| 'user1'@'localhost' | NULL          | tutoriel     | rencontre  | SELECT         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | rencontre  | INSERT         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | rencontre  | UPDATE         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | rencontre  | DELETE         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | V_EQUIPE   | SELECT         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | V_EQUIPE   | INSERT         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | V_EQUIPE   | UPDATE         | NO           |
| 'user1'@'localhost' | NULL          | tutoriel     | V_EQUIPE   | DELETE         | NO           |
+---------------------+---------------+--------------+------------+----------------+--------------+
8 rows in set (0.01 sec)

Il connait maintenant la totalité de ses privilèges sur les tables (et vues), ce qui peut être bon à savoir lorsque surviennent certains problèmes de droits.

Mais notre utilisateur se pose des questions sur la vue V_EQUIPE. Que contient-elle vraiment ? Et bien une simple requête sur INFORMATION_SCHEMA.VIEWS va le renseigner sur sa structure :

mysql> SELECT VIEW_DEFINITION FROM VIEWS WHERE TABLE_NAME = 'V_EQUIPE';
+-------------------------------------------------------------------------+
| VIEW_DEFINITION                                                         |
+-------------------------------------------------------------------------+
| select `tutoriel`.`equipe`.`id_equipe` AS   `id_equipe`,`tutoriel`.`equipe`.`nom` AS `nom`,
`tutoriel`.`equipe`.`stade` AS `stade`,`tutoriel`.`equipe`.`ville` AS `ville`,
`tutoriel`.`equipe`.`points` AS `points`,`tutoriel`.`equipe`.`buts` AS `buts`,
`tutoriel`.`equipe`.`entraineur` AS `entraineur` from `tutoriel`.`equipe` |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Pour résumer, INFORMATION_SCHEMA standardise les instructions d'accès aux méta-données qui existaient déjà et en apporte de nouvelles.

Conclusion

Grâce aux avancées que nous venons de voir, MySQL s'ouvre de nouvelles portes. Le SGBD libre parait désormais plus à même de répondre à des problématiques applicatives évoluées mettant en jeu des modèles de données complexes, même s'il lui reste du chemin à parcourir pour entrer dans la "cour des grands" des SGBD.

J'espère que cet article aura été pour vous un tour d'horizon instructif des nouveautés proposées par MySQL 5.0 et que cela pourra vous aider à tirer profit de ces possibilités. Sachez également que des évolutions tout aussi importantes (triggers améliorés, clés étrangères MyISAM, partitionnement de tables) sont prévues pour la future version 5.1.

MySQL 5.0 est téléchargeable ici. Sont inclus les moteurs de stockage MyISAM, InnoDB, merge, archive, BDB (avec mysql-max)... Les éléments décrits dans le tutoriel fonctionnent quel que soit le moteur utilisé.

Vous trouverez dans le manuel MySQL une référence détaillée des points abordés :

Je tiens à remercier Pedro204 pour sa relecture de l'article et Fadace pour ses suggestions.

Cette page constitue une oeuvre intellectuelle protégée par les droits d'auteurs. Copyright © 2005 Maximilian. Tous droits réservés. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 € de dommages et intérets. Cette page est déposée à la SACD.