MySQL 5.0 : Les vues

• Par Emacs
• 7 commentaires
• 44661 lectures
• RSS -  Atom

Le langage SQL acronyme de Structured Query Language (Langage Structuré de Requêtes), a été conçu pour gérer les données dans un SGBDR. A l'aide des DML (Data Manipulation Language ie les requêtes SELECT, INSERT, UPDATE, DELETE) il est possible de manipuler ces données qui sont stockées dans des tables.

SQL nous propose une autre interface pour accéder à cette information: les vues.
Dans cet article, nous verrons comment créer et se servir des vues, puis avec quelques exemples pratiques, nous allons voir comment les utiliser le mieux possible.

Qu'est-ce qu'une vue ?

Les vues sont des tables virtuelles issues de l'assemblage d'autres tables en fonction de critères. Techniquement les vues sont créées à l'aide d'une requête SELECT. Elles ne stockent pas les données qu'elles contiennent mais conservent juste la requête permettant de les créer.

La requête SELECT qui génère la vue référence une ou plusieurs tables. La vue peut donc être, par exemple, une jointure entre différentes tables, l'aggrégation ou l'extraction de certaines colonnes d'une table. Elle peut également être créée à partir d'une autre vue.

Les vues sont souvent en lecture seule et ne permettent donc que de lire des données. Cependant MySQL permet la création de vues modifiables sous certaines conditions :

• La requête qui génère la vue doit permettre à MySQL de retrouver la trace de l'enregistrement à modifier dans la ou les tables sous-jacentes ainsi que celle de toutes les valeurs de chaque colonne. La requête SELECT créant la vue ne doit donc pas contenir de clause DISTINCT, GROUP BY, HAVING... et autres fonctions d'aggrégation. La liste complète est disponible dans la documentation de MySQL.
• L'autre condition est que sa clause ALGORITHM ne doit pas être de valeur TEMPTABLE. Nous reviendrons sur ce point.

A quoi servent les vues ?

Les vues peuvent être utilisées pour différentes raisons. Elles permettent de :

• Contrôler l'intégrité en restreignant l'accès aux données pour améliorer la confidentialité.
  • Partitionnement vertical et/ou horizontal pour cacher des champs aux utilisateurs, ce qui permet de personnaliser l'affichage des informations suivant le type d'utilisateur.
• Masquer la complexité du schéma.
  • Indépendance logique des données, utile pour donner aux utilisateurs l’accès à un ensemble de relations représentées sous la forme d'une table. Les données de la vue sont alors des champs de différentes tables regroupées, ou des résultats d’opérations sur ces champs.
• Modifier automatiquement des données sélectionnées (sum(), avg(), max(),...).
  • Manipuler des valeurs calculées à partir d'autres valeurs du schéma.
• Conserver la structure d'une table si elle doit être modifiée.
  • Le schéma peut ainsi être modifié sans qu'il ne soit nécessaire de changer les requêtes du côté applicatif.

Les droits nécessaires

Pour créer une vue l'utilisateur doit avoir le droit CREATE VIEW. Il faut également avoir la permission de sélectionner toutes les colonnes qui apparaissent dans la commande SELECT spécifiant ce qu'est la vue.

De plus si la clause REPLACE est utilisée, le droit DROP est également nécessaire.
Le droit SHOW VIEW donne la possibilité d'exécuter la commande SHOW CREATE VIEW. Cette commande permet d'obtenir les informations de création d'une vue. Une autre façon d'obtenir ces informations est d'interroger la table view du schéma information_schema. Cette information sera exhaustive seulement pour les vues que vous avez créé.

Accorder les droits sur une vue pour un utilisateur
GRANT 
  SELECT, 
  DROP, 
  CREATE VIEW, 
  SHOW VIEW 
ON `projet`.* TO 'secretaire'@'localhost';

Syntaxe d'une vue

CREATE VIEW

La commande MySQL pour créer une vue est assez proche de la syntaxe du standard SQL.

Syntaxe de création d'une vue sous MySQL
CREATE VIEW nom_de_la_vue AS requête_select

Création d'une vue pour la relation "etudiant"
CREATE TABLE etudiant 
(
  id_etudiant INT UNSIGNED PRIMARY KEY,
  nom CHAR(30), 
  prenom CHAR(30), 
  age TINYINT UNSIGNED, 
  cursus ENUM('Licence', 'Master', 'Doctorat')
);
 
 
CREATE VIEW v_etudiant_liste AS SELECT nom, prenom FROM etudiant;

Après avoir créé la table etudiant, on crée la vue v_etudiant_liste qui contient le nom et le prénom des étudiants.

Il est possible d'ajouter d'autres informations lors de la création de la vue :

Syntaxe d'une vue MySQL
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW nom_de_la_vue [(colonne(s))]
    AS requête_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Voici dans le détail les différentes clauses.

OR REPLACE : si une vue du même nom existe, elle est alors supprimée et remplacée par la nouvelle.

ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} : clause non standard, qui prend les valeurs suivantes :

• UNDEFINED : C'est la valeur par défaut. MySQL décide lui-même quel algorithme choisir entre MERGE et TEMPTABLE.
• MERGE utilise la requête SQL ayant servi à la création de la vue comme base d'opération. En d'autres termes, faire une requête sur la vue revient à faire la même requête sur la ou les tables sous-jacentes.
• TEMPTABLE utilise une table temporaire créée pour stocker (temporairement) les résultats. Un intérêt de cet algorithme est de libérer plus rapidement les verrous sur les tables sous-jacentes. Les autres requêtes sont alors moins pénalisées.

Il faut noter également qu'une vue avec pour valeur MERGE sera modifiable alors qu'avec la valeur TEMPTABLE elle ne le sera pas.

DEFINER = { user | CURRENT_USER } : clause non standard qui permet d'assigner un créateur à la vue. Par défaut, le créateur de la vue est DEFINER = current_user, c'est-à-dire, l'utilisateur qui exécute la commande CREATE VIEW. Il est cependant possible d'assigner la vue à un autre compte utilisateur, à condition d'avoir le droit SUPER.

SQL SECURITY { DEFINER | INVOKER } : clause non standard encore qui permet de définir quels seront les droits de l'utilisateur lors de l'exécution de la vue. Deux valeurs sont possibles :

• DEFINER qui permet d'exécuter la vue avec les droits du créateur. C'est la valeur par défaut.
• INVOKER qui permet d'exécuter la vue avec ses propres droits.

WITH [CASCADED | LOCAL] CHECK OPTION : permet de vérifier les contraintes spécifiées dans la clause WHERE d'une vue modifiable lorsque l'on y modifie ses données. Deux valeurs sont possibles :

• CASCADED, la valeur par défaut. Elle permet de vérifier la contrainte pour la vue ainsi que pour les vues sous-jacentes dont elle dérive.
• LOCAL qui permet de vérifier seulement la contrainte de la vue.

ALTER VIEW

Une fois la vue créée, il est bien évidement possible de la modifier avec la commande ALTER VIEW.

Editer une vue à l'ordre ALTER
ALTER definer='secretaire'@'localhost' 
VIEW v_etudiant_liste AS SELECT nom, prenom, cursus FROM etudiant;

Cette commande modifie la clause DEFINER en lui assignant le compte secretaire@localhost et modifie la définition de la vue en rajoutant le champ cursus.

DROP VIEW

L'ordre DROP VIEW permet d'effacer une vue.

Supprimer des vues à partir de l'ordre DROP VIEW
DROP VIEW v_etudiant_liste, v_prof_liste;

Supprime les vues v_etudiant_liste et v_prof_liste. Il est possible d'ajouter la clause IF EXISTS qui retourne un avertissement au lieu d'une erreur si la vue à effacer n'existe pas.

Restrictions

Lors de la création d'une vue, certaines contraintes doivent être prises en compte :

• Il n'est pas possible de créer un index sur une vue.
• La vue ne peut pas contenir de sous-requêtes dans la clause FROM du SELECT.
• Il n'est pas possible d'utiliser de variables dans une vue.
• Les objets (tables et vues) nécessaires à la création de la vue doivent exister avant de la créer.
• Si un objet référencé par la vue est effacé, la vue n'est alors plus accessible.
• Une vue ne peut référencer une table temporaire (TEMPORARY TABLE).
• Il n'est pas possible de créer des vues temporaires.
• Il n'est pas possible d'associer un trigger à une vue.
• La définition d'une vue est "gelée" dans une requête préparée.

Par exemple :

mysql> CREATE VIEW ma_vue AS SELECT 'première valeur';
Query OK, 0 rows affected (0.24 sec)
 
mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| FIELD           | Type        | NULL | Key | DEFAULT | Extra |
+-----------------+-------------+------+-----+---------+-------+
| première valeur | VARCHAR(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row IN SET (0.50 sec)
 
mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
 
mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row IN SET (0.01 sec)
 
ALTER VIEW ma_vue AS SELECT 'deuxième valeur';
Query OK, 0 rows affected (0.05 sec)
 
mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| FIELD           | Type        | NULL | Key | DEFAULT | Extra |
+-----------------+-------------+------+-----+---------+-------+
| deuxième valeur | VARCHAR(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row IN SET (0.00 sec)
 
mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row IN SET (0.00 sec)

Il faut en fait recréer la requête préparée :

Utiliser une vue dans une requête préparée
mysql> DEALLOCATE PREPARE req_prepare;
Query OK, 0 rows affected (0.00 sec)
 
mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
 
mysql> EXECUTE req_prepare;
+-----------------+
| deuxième valeur |
+-----------------+
| deuxième valeur |
+-----------------+
1 row IN SET (0.00 sec)

Utiliser les vues

Voici quelques exemples pratiques très simples pour illustrer les différents besoins que peuvent combler les vues. On aura ici, une vue administrateur de base de données. Les objets créés ne seront pas utilisés directement par les utilisateurs mais aux travers d'une application.

Contrôler l'intégrité en restreignant l'accès aux données pour améliorer la confidentialité

La table employe de mon application, contient toutes les informations sur les employées.

Structure de la table " employe "
CREATE TABLE `employe`
(
  `id_employe` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` CHAR(45) NOT NULL,
  `prenom` CHAR(45) NOT NULL,
  `tel_perso` CHAR(10) NOT NULL,
  `tel_bureau` CHAR(10) NOT NULL,
  `statut` CHAR(45) NOT NULL,
  `ville` CHAR(45) NOT NULL,
  `salaire` DECIMAL(7,2) NOT NULL,
  PRIMARY KEY (`id_employe`)
);

Toutes les informations présentes dans cette table ne sont pas pertinentes pour les trois types d'utilisateurs suivant : le comptable, la secrétaire pour Paris et la secrétaire pour le reste de la France.

Une solution est donc de créer une vue par type.

Pour le comptable, il faut avoir accès aux champs nom, prénom, téléphone du bureau, statut et salaire de chaque employé. On fait donc un partitionnement vertical de la table employe. La vue correspondante est la suivante :

Création de la vue pour le comptable
CREATE ALGORITHM=MERGE  SQL SECURITY DEFINER VIEW `v_comptable` AS 
SELECT nom, prenom, tel_bureau, statut, salaire FROM employe;

Le profil "secrétaire pour Paris", n'a pas besoin de l'identifiant et il ne doit surtout pas avoir accès aux salaires, cette information étant confidentielle. Autre restriction, ce profil ne gère que les employés de la filiale de Paris. Le partitionnement est vertical et horizontal.

Création de la vue pour la secrétaire de Paris
CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_paris` AS 
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville = 'Paris';

Notre troisième vue est très proche de la deuxième. La seule différence vient du fait que là, on veut les employés qui ne travaillent pas à Paris.

Création de la vue pour les employés de Province
CREATE ALGORITHM= MERGE SQL SECURITY DEFINER VIEW `v_secretaire_autre` AS 
SELECT nom, prenom, tel_perso, tel_bureau, statut FROM employe
WHERE ville <> 'Paris';

Masquer la complexité du schéma

L'équipe de développement doit écrire un moteur de recherches pour une application de commerce électronique. Voici un extrait des tables de la base de données impliquées dans la recherche des produits du site.

La difficulté est de générer la bonne requête avec les bonnes jointures (plus d'une dizaine), à chaque recherche. Une solution pour faciliter le travail des développeurs est de créer une vue qui fait référence à toutes les tables impliquées dans la recherche des produits. Les recherches se feront donc sur cette vue, avec des requêtes plus simples à écrire.

La vue est créée avec l'algorithme TEMPTABLE, les verrous sur les tables sous-jacentes seront libérés plus rapidement ce qui permettra de moins pénaliser les autres requêtes. TEMPTABLE a la particularité de rendre la vue non modifiable, mais cela ne gêne pas du tout, car la vue n'est accédée qu'en lecture.

Extrait de la vue " moteur de recherches "
CREATE ALGORITHM = TEMPTABLE
VIEW moteur_de_recherche
AS
SELECT
   s.product_name as nom_produit,
   ... (les champs nécessaires)
FROM product s
 LEFT JOIN product_files sf ON s.product_id=sf.file_product_id
 LEFT JOIN ..... (toutes les tables impliquées)
WHERE ... ;

Cette astuce permet de "simplifier" de façon assez significative le schéma.

Un dernier mot pour noter que cette solution ne permet pas d'améliorer les performances de la recherche, dans le sens où la requête qui génère la vue est lancée à chaque appel de cette dernière.

Modifier automatiquement des données sélectionnées

Pour ce troisième exemple, nous allons nous intéresser au schéma (là encore très simplifié) d'une application qui permet de vendre des produits en France et au Royaume-Uni, en euro, livres et dollars. Cette application possède une table produit, qui contient le produit (son identifiant) et son prix hors taxe en euro.

Structure de la table " produit "
CREATE TABLE produit 
(
  id_produit MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  prix_ht DECIMAL(6,2) DEFAULT NULL,
  PRIMARY KEY (id_produit)
);

Nous disposons également des tables devise et tva qui gèrent respectivement le taux de change des devises et la TVA de différents pays.

Structures des tables " devise " et " tva "
# Table devise
CREATE TABLE devise 
(
  devise ENUM('Euro', 'Dollar', 'Livre') NOT NULL,
  valeur DECIMAL(6,5) DEFAULT NULL,
  PRIMARY KEY (devise)
);
INSERT INTO devise VALUES ('Livre',0.66017);
INSERT INTO devise VALUES ('Dollar',1.29852);
INSERT INTO devise VALUES ('Euro',1);
 
# Table tva
CREATE TABLE tva 
(
  pays ENUM('France', 'Royaume-Uni') NOT NULL,
  normal DECIMAL(3,1) DEFAULT NULL,
  reduit DECIMAL(3,1) DEFAULT NULL,
  PRIMARY KEY (pays)
);
INSERT INTO tva VALUES ('Royaume-Uni',17.5,5.0);
INSERT INTO tva VALUES ('France',19.6,5.5);

Le besoin est le suivant : disposer simplement des prix TTC pour chaque pays.
On va donc créer deux vues par pays qui nous permettrons de disposer des prix TTC en fonction de la devise.

La vue produit_france, contient les produits, le prix TTC et le prix TTC réduit qui correspond à l'ajout de la TVA réduite. Les prix sont en euros.

Création de la vue " produit_france "
CREATE VIEW `produit_france` AS 
SELECT `produit`.`id_produit` AS `produit`,
  ROUND(((((`produit`.`prix_ht` * `tva`.`normal`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Prix_ttc_€`,
  ROUND(((((`produit`.`prix_ht`* `tva`.`reduit`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Prix_ttc_reduit_€` 
FROM ((`produit` JOIN `tva`) JOIN `devise`) 
WHERE ((`tva`.`pays` = 'France') AND (`devise`.`devise` = 'Euro'));

Certains clients préférant la monnaie de l'oncle Sam, une deuxième vue, produit_france_dollar, est nécessaire pour avoir les prix en dollar.

Création de la vue " produit_france_dollar "
CREATE VIEW `produit_france_dollar` AS 
SELECT `produit`.`id_produit` AS `produit`,
  ROUND(((((`produit`.`prix_ht` * `tva`.`normal`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Prix_ttc_$`,
  ROUND(((((`produit`.`prix_ht`* `tva`.`reduit`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Prix_ttc_reduit_$` 
FROM ((`produit` JOIN `tva`) JOIN `devise`) 
WHERE ((`tva`.`pays` = 'France') AND (`devise`.`devise` = 'Dollar'));

Même principe pour le Royaume-Uni :

Création de la vue " produit_royaume_uni "
CREATE VIEW `produit_royaume_uni` AS 
SELECT `produit`.`id_produit` AS `produit`,
  ROUND(((((`produit`.`prix_ht` * `tva`.`normal`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Net_price_£`,
  ROUND(((((`produit`.`prix_ht`* `tva`.`reduit`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `reduced_ Net_price_£` 
FROM ((`produit` JOIN `tva`) JOIN `devise`) 
WHERE ((`tva`.`pays` = 'Royaume-Uni') AND (`devise`.`devise` = 'Livre'));

Avec les prix en dollars :

Création de la vue " produit_royaux_uni_dollar "
CREATE VIEW `produit_royaume_uni_dollar` AS 
SELECT `produit`.`id_produit` AS `produit`,
  ROUND(((((`produit`.`prix_ht` * `tva`.`normal`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `Net_price_$`,
  ROUND(((((`produit`.`prix_ht`* `tva`.`reduit`) / 100) + `produit`.`prix_ht`) * `devise`.`valeur`),2) AS `reduced_ Net_price_$` 
FROM ((`produit` JOIN `tva`) JOIN `devise`) 
WHERE ((`tva`.`pays` = 'Royaume-Uni') AND (`devise`.`devise` = 'Dollar'));

Conserver la structure d'une table si elle doit être modifiée

La problématique est de mettre à jour le schéma de l'application en changeant la structure de certaines tables.

Changer le schéma a comme principal impact d'obliger de modifier les requêtes de l'application. Il sera donc nécessaire de les identifier pour les mettre à jour à leur tour, ce qui peut rapidement devenir fastidieux. Au travers de l'exemple qui suit, nous allons créer une vue qui va masquer le changement de table ce qui nous évite de modifier les requêtes applicatives. Une nouvelle version de l'application pourra utiliser la nouvelle table sans être obligée d'utiliser la vue, on assure ainsi la compatibilité ascendante.

Ma table de départ est la table livre.

Structure de la table " livre "
CREATE TABLE `livre` 
(
  `id_livre` CHAR(17) NOT NULL,
  `auteur` CHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id_livre`)
);

Les requêtes, du coté de l'application, sont les suivantes:

Requêtes applicatives de la table " livre "
SELECT id_livre FROM livre;
SELECT auteur FROM livre;
SELECT * FROM livre;

De cette structure où je ne peux gérer que des livres, j'en crée une autre qui m'offre plus de souplesse, la table produit :

Structure de la table " produit "
CREATE TABLE `produit` 
(
  `id_produit` MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
  `isbn` CHAR(17) DEFAULT NULL,
  `auteur` CHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id_produit`),
  UNIQUE KEY `isbn` (`isbn`)
);

Les seuls produits disponibles sont mes livres, je remplis donc ma table produit avec le contenu de la table livre :

Import de données dans la table " produit "
INSERT INTO produit (isbn, auteur) SELECT id_livre, auteur FROM livre;

La dernière phase consiste à créer la vue « livre », il me faut donc au préalable effacer la table du même nom. Les vues et les tables partageant le même espace de nom.

Création de la vue " livre "
DROP TABLE livre;
 
CREATE VIEW livre AS SELECT isbn AS id_livre, auteur FROM produit;

Les changements sont transparents pour les trois requêtes de mon application.

Conclusion

Voici un petit tour d'horizon sur les vues, qui nous l'espérons aura contribué à affiner votre vision sur ce sujet. Il est certain que ces tables virtuelles amènent une certaine souplesse au schéma et il serait dommage de ne pas en profiter. Cependant, ce n'est pas non plus une solution miracle, car ajouter des objets peut rapidement rendre le schéma complexe. Maintenant à vous de voir dans quels cas les vues pourront vous être utiles. Pour nous, c'est tout... vu.

Auteurs

Olivier DASINI est formateur officiel et certifié MySQL. Responsable du pôle SGBDR chez Anaska, il est également l'auteur de nombreux articles sur MySQL.

Cyril PIERRE de GEYER est Directeur Technique adjoint chez Anaska (www.anaska.com). Anaska est un organisme de formation spécialisé dans l'Open Source. Militant pour la philosophie OpenSource Cyril PIERRE de GEYER est également l'auteur du livre « PHP5 Avancé », membre du club mandriva, de l'ASS2L et du club Sénat.