1 4 7 3 vues
Schémas relationnel 3/3 : les vues¶
Supports complémentaires
Une requête SQL produit toujours une table. Cela suggère la possibilité d'ajouter au schéma des tables calculées, qui ne sont rien d'autre que le résultat de requêtes stockées. De telles tables sont nommées des vues dans la terminologie relationnelle. On peut interroger des vues comme des tables stockées et, dans certaines limites, faire des mises à jour des tables stockées au travers de vues.
Une vue n'induit aucun stockage puisqu'elle n'existe pas physiquement. Elle permet d'obtenir une représentation différente des tables sur lesquelles elle est basée avec deux grands avantages :
- on peut faciliter l'interrogation de la base en fournissant sous forme de vues des requêtes prédéfinies ;
- on peut masquer certaines informations en créant des vues et en forçant par des droits d'accès l'utilisateur à passer par ces vues pour accéder à la base.
Les vues constituent donc un moyen complémentaire de contribuer à la sécurité (par restriction d'accès) et à la facilité d'utilisation (en offrant une "schéma virtuel" simplifié).
Création et interrogation d'une vue¶
Une vue est en tout point comparable à une table : en particulier on peut l'interroger par SQL. La grande différence est qu'une vue est le résultat d'une requête avec la caractéristique essentielle que ce résultat est réévalué à chaque fois que l'on accède à la vue. En d'autres termes une vue est dynamique : elle donne une représentation fidèle de la base au moment de l'évaluation de la requête.
Une vue est essentiellement une requête à laquelle on a donné un nom. La syntaxe de création d'une vue est très simple :
create view nomvue ([listeattributs])
as requete
[with check option]
Voici une vue sur la table Immeuble montrant uniquement le Koudalou.
create view Koudalou as
select nom, adresse, count(*) as nb_apparts
from Immeuble as i, Appart as a
where i.nom='Koudalou'
and i.id=a.idImmeuble
group by i.id, nom, adresse
La destruction d'une vue a évidemment beaucoup moins de conséquences que pour une table puisqu'on supprime uniquement la définition de la vue pas son contenu.
On interroge la vue comme n'importe quelle table.
select * from Koudalou
nom | adresse | nb_apparts |
---|---|---|
Koudalou | 3 rue des Martyrs | 5 |
La vue fait maintenant partie du schéma. On ne peut d'ailleurs évidemment pas créer une vue avec le même nom qu'une table (ou vue) existante. La définition d'une vue peut consister en un requête SQL aussi complexe que nécessaire, avec jointures, regroupements, tris.
Allons un peu plus loin en définissant sous forme de vues un accès aux informations de notre base Immeuble, mais restreint uniquement à tout ce qui concerne l'immeuble Koudalou. On va en profiter pour offrir dans ces vues un accès plus facile à l'information. La vue sur les appartements, par exemple, va contenir contrairement à la table Appart le nom et l'adresse de l'immeuble et le nom de son occupant.
create or replace view AppartKoudalou as
select no, surface, niveau, i.nom as immeuble, adresse,
concat(p.prénom, ' ', p.nom) as occupant
from Immeuble as i, Appart as a, Personne as p
where i.id=a.idImmeuble
and a.id=p.idAppart
and i.id=1
On voit bien sur cet exemple que l'un des intérêts des vues est de donner une représentation "dénormalisée" de la base en regroupant des informations par des jointures. Le contenu étant virtuel, il n'y a ici aucun inconvénient à "voir" la redondance du nom de l'immeuble et de son adresse. Le bénéfice, en revanche, est la possibilité d'obtenir très simplement toutes les informations utiles.
select * from AppartKoudalou
no | surface | niveau | immeuble | adresse | occupant |
---|---|---|---|---|---|
1 | 150 | 14 | Koudalou | 3 rue des Martyrs | Léonie Atchoum |
51 | 200 | 2 | Koudalou | 3 rue des Martyrs | Barnabé Simplet |
52 | 50 | 5 | Koudalou | 3 rue des Martyrs | Alice Grincheux |
43 | 75 | 3 | Koudalou | 3 rue des Martyrs | Brandon Timide |
Le nom des attributs de la vue est celui des expressions de la requête
associée. On peut également donner ces noms après le create view
à
condition qu'il y ait correspondance univoque entre un nom et une
expression du select
. On peut ensuite donner des droits en lecture sur
cette vue pour que cette information limitée soit disponible à tous.
grant select on Immeuble.Koudalou, Immeuble.AppartKoudalou to adminKoudalou
Pour peu que cet utilisateur n'ait aucun droit de lecture sur les tables de la base Immeuble, on obtient un moyen simple de masquer et restructurer l'information.
Mise à jour d'une vue¶
L'idée de modifier une vue peut sembler étrange puisqu'une vue n'a
pas de contenu. En fait il s'agit bien entendu de modifier la table qui
sert de support à la vue. Il existe de sévères restrictions sur les
droits d'insérer ou de mettre à jour des tables au travers des vues. Un
exemple suffit pour comprendre le problème. Imaginons que l'on souhaite
insérer un nuplet dans la vue AppartKoudalou
.
insert into AppartKoudalou (no, surface, niveau, immeuble, adresse, occupant)
values (1, 12, 4, 'Globe', '2 Avenue Leclerc', 'Palamède')
Le système rejettera cette requête (par exemple, pour MySQL, avec le
message
Can not modify more than one base table through a join view 'Immeuble.AppartKoudalou'
).
Cet ordre s'adresse à une vue issue de trois tables. Il n'y a
clairement pas assez d'information pour alimenter ces tables de manière
cohérente et l'insertion n'est pas possible (de même que toute mise à
jour). De telles vues sont dites non modifiables. Les règles
définissant les vues modifiables sont assez strictes et difficiles à
résumer simplement d'autant qu'elles varient selon l'opération
(update
, delete
, ou insert
). En première approximation on peut
retenir les points suivants qui donnent lieu à quelques exceptions sur
lesquelles nous reviendrons ensuite.
- La vue doit être basée sur une seule table ;
- toute colonne non référencée dans la vue doit pouvoir être mise à
null
ou disposer d'une valeur par défaut ; - on ne peut pas mettre à jour un attribut qui résulte d'un calcul ou d'une opération.
On ne peut donc pas insérer ou modifier la vue Koudalou à cause de la jointure et de l'attribut calculé. La requête suivante serait rejetée.
insert into Koudalou (nom, adresse)
values ('Globe', '2 Avenue Leclerc')
En revanche une vue portant sur une seule table avec un select *
est
modifiable.
create view PropriétaireAlice
as select * from Propriétaire
where idPersonne=2
insert into PropriétaireAlice values (2, 100, 20)
insert into PropriétaireAlice values (3, 100, 20)
Maintenant, si on fait :
select * from PropriétaireAlice
On obtient:
idPersonne | idAppart | quotePart |
---|---|---|
2 | 100 | 20 |
2 | 103 | 100 |
L'insertion précédente illustre une petite subtilité : on peut insérer dans une vue sans être en mesure de voir le nuplet inséré au travers de la vue par la suite ! On a en effet inséré dans la vue le propriétaire 3 qui est ensuite filtré quand on interroge la vue.
SQL propose l'option with check option
qui permet de garantir que
tout nuplet inséré dans la vue satisfait les critères de sélection de la
vue.
create view PropriétaireAlice
as select * from Propriétaire
where idPersonne=2
with check option
SQL permet également la modification de vues définies par des jointures.
Les restrictions sont essentiellement les mêmes que pour les vues
mono-tabulaires : on ne peut insérer que dans une des tables (il faut
donc préciser la liste des attributs) et tous les attributs not null
doivent avoir une valeur. Voici un exemple de vue modifiable basée sur
une jointure.
create or replace view ToutKoudalou
as select i.id as id_imm, nom, adresse, a.*
from Immeuble as i, Appart as a
where i.nom='oudalou'
and i.id=a.idImmeuble
with check option
Il est alors possible d'insérer à condition d'indiquer des attributs d'une seule des deux tables. La commande ci-dessous ajoute un nouvel appartement au Koudalou.
insert into ToutKoudalou (id, surface, niveau, idImmeuble, no)
values (104, 70, 12, 1, 65)
En conclusion, l'intérêt principal des vues est de permettre une restructuration du schéma en vue d'interroger et/ou de protéger des données. L'utilisation de vues pour des mises à jour devrait rester marginale.