dasini.net – Journal d'un expert MySQL

(<- précédent)

Vues

Des vues pour faciliter la visibilité

Les vues sont la plupart du temps utiles pour donner aux utilisateurs l’accès à un ensemble de relations représentées sous la forme d’une table. Une vue est une table virtuelle ; les données de la vue sont en fait des champs de différentes tables regroupées, ou des résultats d’opérations sur ces champs.

Des vues pour améliorer la confidentialité

Une vue n’est pas forcément un regroupement de plusieurs tables mais peut être un sous ensemble d’une table (ou de plusieurs) ce qui permet de cacher des champs aux utilisateurs.

Par exemple il ne sera pas forcément utile à tout le monde d’accéder aux champs indiquant les bénéfices réalisés sur un projet dans votre base comptable. Vous pouvez donc créer une vue contenant tous les champs de la table projet sauf le champs bénéfice.

L’approche avec MySQL 5 sera donc plus souple car elle ne force plus un découpage de table pour gérer la confidentialité et les droits donnés aux utilisateurs. Les vues permettront de remplir ce rôle.

Les vues compliquent les mises à jour

Insérer ou modifier des données dans une vue n’est pas aussi simple que de faire un update dans une table. Pour pouvoir le faire il faut réfléchir de façon plus poussée au modèle conceptuel de données :

Une vue n’est pas forcément accessible en insertion / modification. Pour cela il faut qu’il n’y ait pas d’incompatibilité logique à ce qu’elle le soit.

• Tous les champs des tables possédant des contraintes d’intégrités (index unique, clés primaires ..) doivent être présent

• La vue ne doit pas posséder de regroupement ou d’exclusion ( GROUP BY , DISTINCT, UNION)

• Le plan d’exécution de la vue ne doit pas passer par une table temporaire

Les vues de MySQL 5 par la pratique

CREATE
[OR REPLACE]
VIEW view-name
[(column-list)]
AS select-statement

Créer une vue revient à appliquer un filtre à une ou plusieurs tables. Pour schématiser prenons une entreprise normale. Dans celle-ci il y a des employés regroupés sous le terme ‘personnel’. On regroupe ces employés par ‘catégorie’ en fonction de leur activité (administratif, informatique, commercial,…).

On peut créer une vue contenant l’ensemble des informaticiens avec le code suivant :

CREATE VIEW personnelinformatique

AS SELECT a.nom AS nom,a.prenom AS prenom,b.service AS service

from categorie b, personnel a

where a.fkCategorie = 1 and a.fkCategorie = b.pkCategorie;

Optimisation des vues de MySQL 5

CREATE

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW view-name

[(column-list)]

AS select-statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

• La clause facultative «ALGORITHM» n’est pas standard. Elle permet d’optimiser votre code.

• MERGE utilise la requête SQL ayant servie à la création de la vue comme base d’opération.

• TEMPTABLE utilise une table temporaire créée pour stocker les résultats.

Par défaut l’optimiseur MySQL décide lui-même quelle option choisir (UNDEFINED). Pour améliorer les performances, il est généralement plus intéressant de le définir statiquement afin d’éviter à l’optimiseur de le découvrir à chaque exécution.

«DEFINER» assigne un créateur à la vue.

«SQL SECURITY» définit quelles seront les droits de l’utilisateur, lors de l’exécution de la vue. Deux valeurs sont possibles:

– DEFINER permet d’exécuter la vue avec les droits du créateur.

– INVOKER permet d’exécuter la vue avec ses propres droits.

Pour finir, la clause facultative CHECK OPTION permet de ne modifier que la vue ou du moins des informations qui respectent les contraintes de la vue. Ainsi dans notre exemple, avec la clause CHECK OPTION, il ne sera pas possible de modifier au travers de la vue « personnelInformatique » une personne ne travaillant pas dans le service informatique.

Manque

Le seul léger bémol est que la version 5.0 de MySQL ne disposera pas des vues matérialisées. Les vues matérialisées sont des données physiquement dupliquées dans le SGDB. Par exemple le résultat d’un calcul n’est plus à refaire pour chaque accès. Leur utilité se fait particulièrement sentir lors de traitements de tables très volumineuses .

Comparaison avec d’autres SGBD

	MySQL	IBM DB2	Oracle	SQL Server
Basic	Oui	Oui	Oui	Oui
UNION ALL	Oui	Oui	Oui	Oui
JOINS	Oui	Oui	Oui	Oui
INSTEAD OF	Non	Oui	Oui	Oui
UPDATEABLE_KEY	Oui	Non	Non	Non

Possibilité des vues avec MySQL 5.0

UPDATEABLE_KEY est une fonctionnalité de MySQL permettant de modifier une clef primaire par le biais d’une vue.

(suite ->)

Filed under:
MySQL by Olivier DASINI