Créer des vues dans SQLite (CREATE VIEW)

Les vues sont des tables virtuelles basées sur le résultat d’une requête SQL. Elles permettent de simplifier des requêtes complexes, de masquer la complexité sous-jacente des tables et d’offrir un niveau d’abstraction supplémentaire. Dans ce tutoriel, nous allons explorer comment créer et utiliser des vues dans SQLite.

Qu’est-ce qu’une vue dans SQLite ?

Une vue dans SQLite est une table virtuelle qui ne stocke pas physiquement des données mais est définie par une instruction SELECT. Lorsque vous interrogez une vue, SQLite exécute la requête définissant la vue et renvoie les résultats. Les vues vous permettent de :

• Simplifier des requêtes complexes
• Masquer des colonnes sensibles
• Présenter des données de manière plus intuitive
• Centraliser la logique métier dans la base de données
• Réutiliser les mêmes requêtes sans avoir à les réécrire

NOTE

Une vue se comporte comme une table normale lors de l’exécution de requêtes, mais elle ne stocke pas réellement de données. Le résultat est calculé dynamiquement à chaque fois que vous interrogez la vue.

Syntaxe de CREATE VIEW

La syntaxe de base pour créer une vue dans SQLite est la suivante :

CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] nom_vue [(nom_colonne, ...)]
AS
   select_statement;

Où :

• TEMP ou TEMPORARY : rend la vue temporaire (elle disparaît à la fin de la session de la base de données)
• IF NOT EXISTS : crée la vue seulement si elle n’existe pas déjà
• nom_vue : le nom que vous souhaitez donner à votre vue
• nom_colonne, ... : optionnel, liste des noms de colonnes pour la vue
• select_statement : l’instruction SELECT qui définit la vue

Exemples de création de vues

Supposons que nous avons une base de données d’un magasin en ligne avec les tables suivantes :

-- Table des clients
CREATE TABLE clients (
    client_id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    prenom TEXT NOT NULL,
    email TEXT UNIQUE,
    date_inscription DATE
);

-- Table des produits
CREATE TABLE produits (
    produit_id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    description TEXT,
    prix REAL NOT NULL,
    stock INTEGER NOT NULL
);

-- Table des commandes
CREATE TABLE commandes (
    commande_id INTEGER PRIMARY KEY,
    client_id INTEGER,
    date_commande DATE NOT NULL,
    statut TEXT NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients (client_id)
);

-- Table des détails de commande
CREATE TABLE details_commande (
    detail_id INTEGER PRIMARY KEY,
    commande_id INTEGER,
    produit_id INTEGER,
    quantite INTEGER NOT NULL,
    prix_unitaire REAL NOT NULL,
    FOREIGN KEY (commande_id) REFERENCES commandes (commande_id),
    FOREIGN KEY (produit_id) REFERENCES produits (produit_id)
);

Exemple 1 : Vue simple sur une seule table

Créons une vue pour afficher uniquement les produits en stock :

CREATE VIEW produits_disponibles AS
SELECT produit_id, nom, description, prix
FROM produits
WHERE stock > 0;

Cette vue retourne tous les produits dont le stock est supérieur à 0. Vous pouvez maintenant interroger cette vue comme s’il s’agissait d’une table normale :

SELECT * FROM produits_disponibles;

Exemple 2 : Vue combinant plusieurs tables

Créons une vue qui présente les détails des commandes avec les noms des clients et des produits :

CREATE VIEW details_commandes_complets AS
SELECT
    dc.commande_id,
    c.nom || ' ' || c.prenom AS client,
    p.nom AS produit,
    dc.quantite,
    dc.prix_unitaire,
    (dc.quantite * dc.prix_unitaire) AS total
FROM
    details_commande dc
JOIN
    commandes cmd ON dc.commande_id = cmd.commande_id
JOIN
    clients c ON cmd.client_id = c.client_id
JOIN
    produits p ON dc.produit_id = p.produit_id;

Cette vue combine des données de quatre tables pour fournir une vision complète des commandes. Vous pouvez l’interroger comme suit :

SELECT * FROM details_commandes_complets WHERE commande_id = 123;

Exemple 3 : Vue avec agrégation

Voici un exemple de vue qui calcule des statistiques de vente par produit :

CREATE VIEW statistiques_produits AS
SELECT
    p.produit_id,
    p.nom,
    COUNT(dc.detail_id) AS nombre_ventes,
    SUM(dc.quantite) AS quantite_totale_vendue,
    AVG(dc.prix_unitaire) AS prix_moyen,
    SUM(dc.quantite * dc.prix_unitaire) AS chiffre_affaires
FROM
    produits p
LEFT JOIN
    details_commande dc ON p.produit_id = dc.produit_id
GROUP BY
    p.produit_id, p.nom;

Cette vue calcule plusieurs métriques pour chaque produit, comme le nombre de ventes, la quantité totale vendue, etc.

Nommage des colonnes dans une vue

Si vous souhaitez donner des noms personnalisés aux colonnes de votre vue, vous pouvez les spécifier entre parenthèses après le nom de la vue :

CREATE VIEW top_clients (id, nom_complet, total_achats, nombre_commandes) AS
SELECT
    c.client_id,
    c.nom || ' ' || c.prenom,
    SUM(dc.quantite * dc.prix_unitaire),
    COUNT(DISTINCT cmd.commande_id)
FROM
    clients c
JOIN
    commandes cmd ON c.client_id = cmd.client_id
JOIN
    details_commande dc ON cmd.commande_id = dc.commande_id
GROUP BY
    c.client_id
ORDER BY
    total_achats DESC;

Dans cet exemple, nous spécifions explicitement les noms des colonnes de notre vue : id, nom_complet, total_achats et nombre_commandes.

Créer une vue temporaire

Si vous avez besoin d’une vue uniquement pour la session en cours, vous pouvez créer une vue temporaire qui sera automatiquement supprimée à la fermeture de la connexion :

CREATE TEMPORARY VIEW produits_recents AS
SELECT * FROM produits
WHERE date_ajout > date('now', '-30 days');

Cette vue temporaire affiche les produits ajoutés au cours des 30 derniers jours. Elle ne sera plus disponible lorsque vous fermerez la connexion à la base de données.

Vérifier l’existence d’une vue avant création

Pour éviter les erreurs lors de la création d’une vue qui pourrait déjà exister, utilisez la clause IF NOT EXISTS :

CREATE VIEW IF NOT EXISTS clients_actifs AS
SELECT * FROM clients
WHERE derniere_commande > date('now', '-90 days');

Avantages et inconvénients des vues

Avantages

1. Simplicité : Les vues permettent de simplifier des requêtes complexes en les encapsulant.
2. Sécurité : Elles peuvent être utilisées pour limiter l’accès à certaines colonnes ou lignes.
3. Abstraction : Les vues masquent la complexité de la structure sous-jacente des tables.
4. Cohérence : Elles garantissent que tous les utilisateurs accèdent aux données de la même manière.
5. Maintenance : La modification du schéma des tables sous-jacentes n’affecte pas nécessairement les applications qui utilisent les vues.

Inconvénients

1. Performance : Les vues peuvent être moins performantes que les requêtes directes sur les tables, surtout pour les vues complexes.
2. Limites des mises à jour : Les vues dans SQLite ont des limitations concernant les opérations UPDATE, INSERT et DELETE.
3. Pas d’indexation : Vous ne pouvez pas créer d’index directement sur une vue.

Limites des vues dans SQLite

Dans SQLite, les vues sont généralement en lecture seule. Cela signifie que vous ne pouvez pas effectuer d’opérations de modification (INSERT, UPDATE, DELETE) directement sur la plupart des vues.

De plus, contrairement à certains autres systèmes de gestion de bases de données, SQLite ne prend pas en charge les vues matérialisées (des vues dont les résultats sont stockés physiquement pour améliorer les performances).

Cas d’utilisation pratiques

1. Rapports et tableaux de bord

Les vues sont parfaites pour créer des rapports et des tableaux de bord car elles peuvent agréger et présenter des données de manière significative :

CREATE VIEW tableau_bord_ventes AS
SELECT
    strftime('%Y-%m', date_commande) AS mois,
    COUNT(*) AS nombre_commandes,
    SUM(dc.quantite * dc.prix_unitaire) AS revenu_total,
    COUNT(DISTINCT cmd.client_id) AS nombre_clients
FROM
    commandes cmd
JOIN
    details_commande dc ON cmd.commande_id = dc.commande_id
GROUP BY
    mois
ORDER BY
    mois DESC;

2. Abstraction de la logique métier

Les vues peuvent encapsuler la logique métier complexe et offrir une interface simplifiée aux développeurs :

CREATE VIEW produits_recommandes AS
SELECT p.*
FROM produits p
WHERE p.categorie_id IN (
    SELECT DISTINCT p2.categorie_id
    FROM produits p2
    JOIN details_commande dc ON p2.produit_id = dc.produit_id
    JOIN commandes cmd ON dc.commande_id = cmd.commande_id
    WHERE cmd.client_id = :client_id
)
AND p.produit_id NOT IN (
    SELECT DISTINCT dc.produit_id
    FROM details_commande dc
    JOIN commandes cmd ON dc.commande_id = cmd.commande_id
    WHERE cmd.client_id = :client_id
)
ORDER BY p.popularite DESC
LIMIT 10;

Cette vue pourrait être utilisée pour générer des recommandations de produits basées sur l’historique d’achat d’un client.

3. Contrôle d’accès aux données

Les vues peuvent être utilisées pour limiter l’accès à certaines données sensibles :

CREATE VIEW clients_info_publique AS
SELECT client_id, nom, prenom, ville, pays
FROM clients;

Cette vue omet les informations sensibles comme l’adresse e-mail, le numéro de téléphone, etc.