Perfectionnez votre maîtrise de SQL

Après vos récents succès sur la mise en place des contraintes d'intégrité, Rodolphe, toujours aussi satisfait, vous partage une nouvelle mission. Avec Sofia, la CEO de DVD Rental, ils ont remarqué que certaines requêtes étaient régulièrement exécutées, et que ces opérations devenaient lourdes et répétitives pour leurs équipes. Pour alléger cette charge et rendre les données plus facilement accessibles à tout le monde, ils souhaitent que vous exploitiez une fonctionnalité de PostgreSQL : les vues.

Encapsulez une requête SQL dans une entité simple et réutilisable

Sofia a expliqué à Rodolphe que certaines équipes passent beaucoup de temps à écrire les mêmes requêtes pour obtenir des résultats similaires. Pour rendre le tout plus fluide, vous allez apprendre à encapsuler ces requêtes dans une vue, permettant de réutiliser le résultat d'une requête comme s'il s'agissait d'une simple table.

Comprenez les avantages et les cas d’utilisation d’une vue

Les vues jouent un rôle clé dans l'optimisation des requêtes SQL et la gestion des données.

Voici quelques raisons pour lesquelles elles sont si utiles :

• Simplicité : Les vues encapsulent des requêtes complexes sous forme de "tables virtuelles", rendant leur résultat accessible via une simple instructionSELECT. Cela permet de masquer des jointures et calculs complexes derrière une interface simple.

• Réutilisabilité : Une fois créées, les vues peuvent être appelées à plusieurs reprises sans avoir à réécrire la requête d'origine, ce qui réduit le risque d'erreurs et facilite la maintenance des requêtes fréquemment utilisées.

• Sécurité : Les vues peuvent restreindre l'accès aux données sensibles en affichant seulement les colonnes ou lignes nécessaires à certains utilisateurs. Sofia, par exemple, voit cela comme un excellent moyen de contrôler quelles informations sont visibles pour chaque utilisateur.

• Optimisation des performances : En transformant une vue en une vue matérialisée, vous pouvez stocker le résultat d’une requête complexe et éviter de recalculer les données à chaque nouvelle exécution, ce qui est particulièrement utile pour des données consultées fréquemment.

Créez votre première vue

Imaginez que vous devez afficher régulièrement la liste des films actuellement en location avec les informations des clients associés. Sans l'utilisation d'une vue, cela impliquerait d'écrire une requête complexe à chaque fois, comme celle-ci :

SELECT
c.customer_id,
c.first_name,
c.last_name
r.rental_date,
f.title
FROM
customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN film f ON r.film_id = f.film_id
WHERE
r.return_date IS NULL;

Cette requête permet de récupérer les informations des clients (ID, prénom, nom), la date de location, et le titre du film. Mais la réécrire à chaque fois peut rapidement devenir fastidieux et source d'erreurs, surtout si la requête doit être légèrement modifiée ou optimisée.

Pour simplifier ce processus, vous pouvez encapsuler cette requête dans une vue. Cela permet de la rendre réutilisable sans avoir à réécrire la requête complète.

Voici comment créer une vue avec l'instructionCREATE VIEW:

CREATE VIEW current_rentals AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
r.rental_date,
f.title
FROM
customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN inventory i on i.inventory_id = r.inventory_id
JOIN film f ON i.film_id = f.film_id
WHERE
r.return_date IS NULL;

Tout à fait, une fois la vue créée, son appel est aussi simple que d'interroger une table ordinaire avec unSELECT. Cela rend son usage très pratique et vous permet d'éviter de réécrire des requêtes complexes.

SELECT * FROM current_rentals;

Maintenant que nous avons vu comment créer une première vue simple, il est temps d'aller plus loin et d'explorer comment les vues peuvent être utilisées pour abstraire les données sous-jacentes.

Développez de nouvelles vues pour abstraire les données sous-jacentes

Au-delà de la réutilisation, les vues peuvent servir à restreindre l’accès à certaines données sensibles ou à n’afficher que les informations pertinentes selon le rôle des utilisateurs. Sofia, en tant que CEO, ne veut pas que tout le monde ait accès à l’intégralité des informations clients, notamment des données comme les adresses ou numéros de téléphone.

Créez une vue pour masquer les données sensibles

Vous pouvez créer des vues pour cacher les informations sensibles des clients, telles que les adresses ou numéros de téléphone, dans le but de respecter la confidentialité des données tout en permettant une utilisation plus sécurisée.

Les vues vous permettent de présenter uniquement les informations nécessaires tout en garantissant un accès sécurisé aux données. Voici comment procéder :

CREATE VIEW safe_customer_info AS
SELECT
c.customer_id,
c.first_name,
c.last_name,
cty.city,
SUBSTRING(c.email FROM 1 FOR 3) || '***' AS obscured_email,
SUBSTRING(a.phone FROM 1 FOR 3) || '***' AS obscured_phone
FROM
customer c
JOIN address a ON c.address_id = a.address_id
JOIN city cty ON cty.city_id = a.city_id;

Dans cette vue, nous avons utilisé deux méthodes pour protéger les informations sensibles :

1. Sélection de colonnes : Dans cet exemple, nous affichons uniquement les colonnes customer_id, first_name, last_name et city, permettant d'accéder à des informations de base sans exposer des détails sensibles.

2. Obfuscation des données : Les colonnes d'email et de téléphone sont partiellement masquées. L'email est présenté sous la forme de trois premiers caractères suivis de trois astérisques (***), tout comme le numéro de téléphone.

Une fois la vue créée, exécutez-la pour avoir un aperçu de l’affichage !

Après avoir vu comment masquer les données sensibles pour protéger la confidentialité des informations, passons maintenant à une utilisation plus avancée des vues : filtrer les droits utilisateurs afin de ne montrer que les données auxquelles ils ont accès en fonction de leur rôle

Créez une vue pour filtrer les droits utilisateurs

Pour renforcer la sécurité des données, il est essentiel de s'assurer que chaque utilisateur n'accède qu'aux informations qui les concernent. Nous allons donc créer une vue qui permettra de filtrer les données en fonction de l'utilisateur.

Avant de créer notre vue, il est essentiel de comprendre la fonctioncurrent_user:

• Cette fonction est intégrée à PostgreSQL et retourne le nom de l'utilisateur actuellement connecté à la base de données. Cela est particulièrement utile pour implémenter des contrôles d'accès basés sur l'identité de l'utilisateur. 

• En utilisantcurrent_user, vous pouvez restreindre l'accès aux données, permettant ainsi à chaque utilisateur de voir uniquement les informations qui le concernent, grâce aux vues.

• Cependant, pour pouvoir utiliser cette fonction efficacement, il est nécessaire de créer des rôles spécifiques (autres que le rôle par défaut postgres) pour chaque utilisateur dans votre système. 

• Ces rôles permettent de gérer les permissions et d'identifier les utilisateurs qui se connectent à la base de données. 

Imaginons maintenant que vous souhaitiez créer une vue qui affiche uniquement les clients associés à l'utilisateur actuellement connecté (l’utilisateur étant stocké dans la table staff). Cela permet de personnaliser l'expérience utilisateur tout en gardant la sécurité en tête.

CREATE VIEW user_customers AS
SELECT
c.customer_id,
c.first_name AS customer_first_name,
c.last_name AS customer_last_name
FROM
customer c
WHERE
c.customer_id IN (
SELECT r.customer_id
FROM rental r
JOIN staff s ON r.staff_id = s.staff_id
WHERE s.username = current_user
)
ORDER BY
c.customer_id;

Dans cette vue, nous effectuons les actions suivantes :

• Sélection des informations des clients : Nous choisissons de montrer le customer_id, le prénom et le nom des clients.

• Filtrage basé sur l'utilisateur connecté : La clause WHERE utilise une sous-requête pour sélectionner uniquement les clients qui ont des locations associées à l'utilisateur connecté (current_user). Cela garantit que chaque utilisateur ne voit que les informations pertinentes pour lui.

Dans notre base, Jon et Mike ont exactement les mêmes clients, mais la vue s'adapte bien selon le profil de l'utilisateur connecté.

Après avoir vu comment créer des vues pour filtrer les données et garantir une expérience utilisateur personnalisée, il est important de considérer l'efficacité de ces vues. En effet, chaque fois qu’une vue est interrogée, PostgreSQL doit recalculer la requête sous-jacente, ce qui peut s’avérer coûteux en ressources, surtout lorsque la vue repose sur des jointures complexes ou est fréquemment utilisée. Pour pallier ce problème et améliorer les performances, les vues matérialisées se présentent comme une solution intéressante.

Évitez de recalculer ses vues à chaque fois en les matérialisant

Une vue matérialisée stocke physiquement les résultats de la requête dans la base de données, au lieu de la recalculer à chaque fois. Cela permet de gagner en performance lorsque les données sous-jacentes ne changent pas fréquemment.

Voyons ensemble comment créer une vue matérialisée dans la vidéo ci-dessous :

Dans cette vidéo, vous avez vu :

• Comment créer une vue matérialisée.

• Comment rafraîchir la vue pour mettre à jour les données.

À vous de jouer

Contexte

Sofia et Rodolphe, après plusieurs discussions avec les équipes de DVD Rental, ont remarqué que certaines requêtes SQL sont régulièrement exécutées pour obtenir des résultats similaires. Afin de rendre l'accès à ces informations plus simple et plus performant, ils vous demandent de créer des vues et des vues matérialisées pour les requêtes récurrentes. Ces vues doivent être accessibles par les équipes marketing et service client, tout en filtrant les informations selon leurs besoins respectifs.

Consignes

Vous devez mettre en place deux vues pour répondre aux besoins suivants :

• Vue pour l’équipe marketing : Créez une vue nommée marketing_customers qui affiche les colonnes suivantes pour chaque client : first_name, last_name, email, city, country.

• Vue pour l’équipe service client : Créez une vue nommée customer_rentals qui affiche pour chaque client son first_name, last_name, et les titres des films qu’ils ont loués il y a moins de 6 mois (sans les films en cours).

• Transformez ensuite la vue customer_rentals en une vue matérialisée nommée customer_rentals_mat. Exécutez la commande SQL pour rafraîchir la vue matérialisée après avoir ajouté de nouvelles locations dans la table rental.

En résumé

• Vous avez vu comment créer des vues pour simplifier l'accès aux données en encapsulant des requêtes SQL, rendant ainsi les informations plus accessibles. 

• Vous connaissez l'importance de filtrer les données sensibles et de personnaliser l'affichage en utilisant des fonctions telles queSUBSTRING pour obscurcir les informations comme les adresses email et les numéros de téléphone et la fonctioncurrent_userpour connaître l’utilisateur connecté.

• Vous avez créé des vues matérialisées pour optimiser les performances en stockant les résultats de requêtes fréquentes, réduisant ainsi le besoin de recalculer ces données à chaque fois.

Après avoir exploré les vues et leur rôle crucial dans la simplification de l'accès aux données, il est temps de passer à un niveau supérieur en découvrant comment mettre en place des procédures et fonctions stockées.