Gestion des vues

Conception de bases de données

Lis Sulmont

Curriculum Manager

Création de vues plus complexes

Agrégation : SUM(), AVG(), COUNT(), MIN(), MAX(), GROUP BY, etc.
Jointures : INNER JOIN, LEFT JOIN. RIGHT JOIN, FULL JOIN
Conditionnels : WHERE, HAVING, UNIQUE, NOT NULL, AND, OR,>,<, etc.

Accorder et révoquer l'accès à une vue

GRANT privilege(s) ou REVOKE privilege(s)

ON object

TO role ou FROM role

Privilèges : SELECT, INSERT, UPDATE, DELETE, etc.
Objets : tableau, vue, schéma, etc.
Rôles : un utilisateur ou un groupe d'utilisateurs de base de données

Exemple d'octroi et de révocation

$$\nsql\nGRANT UPDATE ON ratings TO PUBLIC; \n\n$$\nsql\nREVOKE INSERT ON films FROM db_user;\n\n

Mise à jour d'une vue

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Toutes les vues ne sont pas modifiables

La vue est constituée d'un seul tableau
N'utilise pas de fonction fenêtre ou agrégative

¹ https://www.postgresql.org/docs/9.5/sql-update.html

Insertion dans une vue

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Toutes les vues ne sont pas insérables

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

Insertion dans une vue

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

Toutes les vues ne sont pas insérables

À retenir : veuillez éviter de modifier les données via les vues

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

Suppression d'une vue

DROP VIEW view_name [ CASCADE | RESTRICT ];

RESTRICT (par défaut) : renvoie une erreur s'il existe des objets qui dépendent de la vue
CASCADE : supprime la vue et tout objet qui dépend de cette vue

Redéfinition d’une vue

CREATE OR REPLACE VIEW view_name AS new_query

Si une vue avec view_name existe, elle est remplacée
new_query doit générer les mêmes noms de colonnes, le même ordre et les mêmes types de données que l'ancienne requête
Le résultat de la colonne peut être différent
De nouvelles colonnes peuvent être ajoutées à la fin

Si ces critères ne peuvent être remplis, veuillez supprimer la vue existante et en créer une nouvelle

¹ https://www.postgresql.org/docs/9.2/sql-createview.html

Modification d'une vue

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

¹ https://www.postgresql.org/docs/9.2/sql-alterview.html

Passons à la pratique !

Conception de bases de données