Gérer les vues

Conception de bases de données

Lis Sulmont

Curriculum Manager

Créer des 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 : table, vue, schéma, etc.
Rôles : un utilisateur ou un groupe d'utilisateurs de la base de données

Exemple d'octroi et de révocation

GRANT UPDATE ON ratings TO PUBLIC;

REVOKE INSERT ON films FROM db_user;

Mettre à jour une vue

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

Toutes les vues ne sont pas modifiables

La vue est composée d'une seule table
N'utilise pas de fonction de fenêtrage ni d'agrégation

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

Insérer 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

Insérer 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 : évitez de modifier les données via des vues

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

Supprimer une vue

DROP VIEW view_name [ CASCADE | RESTRICT ];

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

Redéfinir une vue

CREATE OR REPLACE VIEW view_name AS new_query

Si une vue nommée view_name existe, elle est remplacée
new_query doit produire les mêmes noms de colonnes, le même ordre et les mêmes types que l'ancienne requête
La sortie des colonnes peut différer
De nouvelles colonnes peuvent être ajoutées à la fin

Si ces critères ne peuvent pas être respectés, supprimez la vue existante et créez-en une nouvelle

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

Modifier 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