Création de vues SQLite

Introduction

Dans ce laboratoire, nous explorerons la création de vues SQLite, en nous concentrant sur la construction de vues complexes, l'interrogation (querying) à travers celles-ci, la mise à jour via des vues modifiables (updatable views), et la suppression des vues obsolètes.

Nous commencerons par comprendre les vues comme des tables virtuelles basées sur les ensembles de résultats d'instructions SQL, utiles pour simplifier les requêtes et contrôler l'accès aux données. Le laboratoire vous guidera à travers la création de vues simples à partir de tables uniques, comme une table employees, puis progressera vers des vues plus complexes impliquant des jointures (joins) entre les tables, telles que la jointure des tables employees et departments. Vous apprendrez à interroger ces vues comme s'il s'agissait de tables régulières et explorerez les possibilités de mise à jour des données via des vues modifiables, ainsi que la manière de supprimer correctement les vues lorsqu'elles ne sont plus nécessaires.

Créer les tables : employés et départements

Dans cette étape, vous allez créer deux tables, employees et departments, et insérer des exemples de données. Ces tables seront utilisées pour créer et interroger (query) des vues dans les étapes suivantes.

Tout d'abord, ouvrez l'interpréteur de commandes (shell) SQLite en exécutant la commande suivante dans le terminal :

sqlite3 /home/labex/project/employees.db

Cette commande ouvre l'interpréteur de commandes SQLite et se connecte à la base de données employees.db. Si le fichier de base de données n'existe pas, SQLite le créera.

Maintenant, créez la table employees avec l'instruction SQL suivante :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

Cette instruction SQL crée une table nommée employees avec cinq colonnes : id, first_name, last_name, department et salary. La colonne id est la clé primaire (primary key) de la table.

Ensuite, insérez des exemples de données dans la table employees :

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

Cette instruction SQL insère quatre lignes dans la table employees.

Maintenant, créez la table departments avec l'instruction SQL suivante :

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

Cette instruction SQL crée une table nommée departments avec trois colonnes : id, name et location. La colonne id est la clé primaire de la table.

Ensuite, insérez des exemples de données dans la table departments :

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

Cette instruction SQL insère trois lignes dans la table departments.

Vous pouvez vérifier la création de la table et l'insertion des données en interrogeant les tables :

SELECT * FROM employees;
SELECT * FROM departments;

Ces commandes afficheront le contenu des tables employees et departments, respectivement.

Créer une vue simple

Dans cette étape, vous allez créer une vue simple appelée employee_info qui sélectionne des colonnes spécifiques de la table employees.

Une vue est une table virtuelle basée sur l'ensemble de résultats d'une instruction SQL. Elle simplifie les requêtes complexes et fournit un niveau d'abstraction.

Pour créer la vue employee_info, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Cette instruction SQL crée une vue nommée employee_info qui sélectionne les colonnes id, first_name, last_name et department de la table employees.

Vous pouvez maintenant interroger (query) la vue comme s'il s'agissait d'une table :

SELECT * FROM employee_info;

Cette commande affichera le contenu de la vue employee_info, qui est un sous-ensemble des colonnes de la table employees.

Créer une vue complexe avec des jointures

Dans cette étape, vous allez créer une vue plus complexe appelée employee_department_info qui joint (join) les tables employees et departments.

Joindre des tables vous permet de combiner des données provenant de plusieurs tables en fonction d'une colonne liée.

Pour créer la vue employee_department_info, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

Cette instruction SQL crée une vue nommée employee_department_info qui joint les tables employees et departments sur la colonne department. Elle sélectionne le prénom (first name) de l'employé, son nom (last name), son département et l'emplacement (location) du département.

Vous pouvez maintenant interroger (query) la vue pour voir les données combinées :

SELECT * FROM employee_department_info;

Cette commande affichera le contenu de la vue employee_department_info, montrant les informations sur les employés ainsi que l'emplacement de leur département.

Créer une vue avec des fonctions d'agrégation

Dans cette étape, vous allez créer une vue appelée department_salary_stats qui utilise des fonctions d'agrégation pour calculer le salaire moyen (average salary) pour chaque département.

Les fonctions d'agrégation effectuent des calculs sur un ensemble de valeurs et renvoient un seul résultat.

Pour créer la vue department_salary_stats, exécutez l'instruction SQL suivante dans l'interpréteur de commandes SQLite (SQLite shell) :

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

Cette instruction SQL crée une vue nommée department_salary_stats qui calcule le salaire moyen pour chaque département en utilisant la fonction AVG et regroupe les résultats par département en utilisant la clause GROUP BY.

Vous pouvez maintenant interroger (query) la vue pour voir le salaire moyen pour chaque département :

SELECT * FROM department_salary_stats;

Cette commande affichera le contenu de la vue department_salary_stats, montrant le salaire moyen pour chaque département.

Mise à jour de données via une vue modifiable

Dans cette étape, vous allez découvrir les limitations des vues SQLite et comment créer des vues véritablement modifiables en utilisant des déclencheurs INSTEAD OF.

Important : Par défaut, les vues SQLite sont en lecture seule. Vous ne pouvez pas directement UPDATE, INSERT ou DELETE des données via une vue. Cependant, vous pouvez créer des vues modifiables en utilisant des déclencheurs INSTEAD OF.

Tout d'abord, comprenons la limitation en tentant une mise à jour directe sur notre vue existante :

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Cela échouera avec une erreur car les vues SQLite sont en lecture seule par défaut.

Pour créer une vue véritablement modifiable, nous devons utiliser des déclencheurs INSTEAD OF. Recréons la vue employee_info et ajoutons un déclencheur INSTEAD OF pour les mises à jour :

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Maintenant, créez un déclencheur INSTEAD OF pour gérer les opérations UPDATE :

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

Vous pouvez maintenant mettre à jour les données via la vue :

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Cette instruction UPDATE fonctionnera maintenant car le déclencheur INSTEAD OF redirige la mise à jour vers la table sous-jacente employees.

Vous pouvez vérifier la mise à jour en interrogeant directement la table employees :

SELECT * FROM employees WHERE id = 1;

Cette commande affichera la ligne dans la table employees avec id = 1, et vous devriez voir que la colonne department a été mise à jour à 'HR'.

Suppression des vues et déclencheurs obsolètes

Dans cette étape, vous allez supprimer les vues et les déclencheurs que vous avez créés lors des étapes précédentes.

Au fur et à mesure que votre base de données évolue, certaines vues et certains déclencheurs peuvent devenir obsolètes ou inutiles. Il est important de supprimer ces objets pour maintenir un schéma de base de données propre et efficace.

Tout d'abord, supprimez le déclencheur INSTEAD OF :

DROP TRIGGER IF EXISTS update_employee_info;

Ensuite, supprimez les vues. Pour supprimer une vue, utilisez l'instruction DROP VIEW. Par exemple, pour supprimer la vue employee_info, exécutez la commande suivante :

DROP VIEW IF EXISTS employee_info;

La clause IF EXISTS empêche une erreur si la vue n'existe pas.

Supprimez également les autres vues :

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

Vous pouvez vérifier que les vues ont été supprimées en interrogeant la table sqlite_master :

SELECT name FROM sqlite_master WHERE type='view';

Cette commande devrait renvoyer un résultat vide, indiquant qu'il n'y a aucune vue dans la base de données.

Enfin, quittez l'interpréteur de commandes SQLite :

.exit

Cette commande ferme la connexion à la base de données employees.db et vous ramène au terminal Linux.

Résumé

Dans ce laboratoire, vous avez appris à créer, interroger, mettre à jour et supprimer des vues dans SQLite. Vous avez commencé par créer des vues simples basées sur une seule table, puis vous avez progressé vers des vues plus complexes impliquant des jointures (joins) et des fonctions d'agrégation. Vous avez découvert que les vues SQLite sont en lecture seule par défaut, mais vous avez appris à créer des vues réellement modifiables en utilisant des déclencheurs (triggers) INSTEAD OF. Vous avez également appris à supprimer correctement les vues et les déclencheurs obsolètes afin de maintenir un schéma de base de données propre. Ces compétences sont essentielles pour simplifier les requêtes complexes, contrôler l'accès aux données et gérer efficacement vos bases de données SQLite.