Rappel du problème

• Besoin de réorganiser une application pour des raisons de performance ou d’évolution
• Objectif : effectuer ces changements sans modifier le comportement observable
• Principe : indépendance fonctionnelle entre les niveaux (analogie avec la programmation objet)

Les trois niveaux des bases de données

• Logique : tables relationnelles et contraintes
• Physique : structures de stockage, index, organisation sur disque
• Externe : vues et programmes utilisateurs
• Modèle ANSI/SPARC (1978) – toujours utilisé comme référence conceptuelle

Architecture à trois niveaux

Illustration de la séparation entre les niveaux externe, logique et physique.

Motivation : Exemple 1 – Performances

• Les performances d’une requête dépendent du stockage physique des données.
• Cette organisation peut évoluer sans modifier la structure logique.
• Le niveau logique doit rester découplé du niveau physique pour permettre des optimisations indépendantes.

Motivation : Exemple 2 – Évolutivité

• Les fonctionnalités évoluent, impliquant des modifications du schéma logique.
• Les applications clientes doivent rester fonctionnelles sans modification.
• Le niveau externe doit demeurer indépendant du niveau logique.

Outils fournis par le SGBD

• Vues : assurent l’indépendance entre niveaux externe et logique.
• Vues matérialisées : permettent la persistance des résultats d’une requête.
• Catalogue système : maintient la correspondance entre niveaux logique et physique.

Exemple d’utilisation d’une vue

• Situation :
  • Table : ticket(client, destination, prix)
  • Tâche : la secrétaire gère les clients pour Saint-Tropez
  • Requête simplifiée :

    SELECT client
    FROM ticket
    WHERE destination ILIKE 'St Trop%';

Problèmes avec la requête directe

• Confort : l’utilisateur maîtrise mal SQL et la structure des tables.
• Volume : la requête est réexécutée à chaque usage.
• Confidentialité : exposition de colonnes inutiles.
• Indépendance : la requête échoue si les tables sont réorganisées.

Principe de solution : vue nommée

• Le développeur crée une vue correspondant à la requête utile.
• L’utilisateur n’interroge plus les tables directement mais la vue définie.
• La vue masque la complexité et protège la structure interne.

Définition des vues

• Une vue est une table virtuelle dont le contenu est défini par une requête SQL.
• Son contenu peut être :
  • Recalculé à chaque accès (CREATE VIEW)
  • Stocké physiquement et rafraîchi (CREATE MATERIALIZED VIEW)

Création d’une vue

CREATE VIEW billetsainttrop AS
SELECT client
FROM ticket
WHERE destination ILIKE 'St Trop%';

(Pour stocker le résultat : CREATE MATERIALIZED VIEW)

Gestion des droits :

REVOKE SELECT ON ticket FROM toto;

Conséquences de l’utilisation des vues

• L’utilisateur interroge simplement la vue :

SELECT * FROM billetsainttrop;

• Confort : la requête complexe est cachée.
• Performance : gain si vue matérialisée.
• Confidentialité : seules les colonnes exposées par la vue sont visibles.
• Indépendance : la vue reste valide même si les tables sont réorganisées.

Avantages supplémentaires des vues

• Maintenance :
  • Factorisation des requêtes récurrentes via la vue.
  • Suppression de la duplication de code SQL dans les applications.

Vocabulaire clé

• Le niveau externe correspond aux programmes utilisateurs et aux vues.
• Ce niveau doit rester indépendant du niveau logique (tables et contraintes).
• Les modifications du schéma logique ne doivent pas casser les vues externes existantes.

Remarque sur les mises à jour du schéma

• Une mise à jour du schéma est sans perte d’information si elle préserve toutes les données nécessaires aux vues existantes.
• Exemple : la suppression d’une colonne utilisée dans une vue la rendrait invalide :

ALTER TABLE ticket DROP COLUMN destination;

Indépendance des niveaux

• L’indépendance entre niveaux est assurée si les modifications du schéma conservent les informations nécessaires.
• La stabilité des vues et des programmes utilisateurs dépend de cette propriété.

Mises à jour des vues

• Les vues simples peuvent être modifiées directement.
• Les vues complexes (jointures, agrégats, DISTINCT) ne sont pas directement modifiables.
• PostgreSQL permet d’ajouter des INSTEAD OF TRIGGER pour gérer les mises à jour indirectes.
• Exemple :

INSERT INTO billetsainttrop VALUES (...);

Résumé : Vues

• Les vues permettent de gérer :
  • Indépendance des niveaux
  • Confidentialité
  • Performance (vues matérialisées)
• Elles facilitent la maintenance et la factorisation du code SQL.

Le catalogue système

• Le catalogue système conserve toutes les métadonnées de la base.
• Il sert à :
  • Traduire entre les niveaux logique et physique.
  • Répertorier tables, vues, index, contraintes, utilisateurs, rôles.
  • Permettre l’inspection via les vues pg_catalog et information_schema.

Exemples de requêtes sur le catalogue

Accéder aux métadonnées de la base PostgreSQL :

-- Tables visibles de l'utilisateur courant
SELECT * FROM pg_catalog.pg_tables;

-- Tables du schéma public
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

-- Tables appartenant à un propriétaire donné
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE tableowner = 'phparis';

Indépendance du niveau physique

• Les modifications de structure logique n’impliquent pas forcément de réorganisation disque.
• Le catalogue système assure la correspondance entre logique et physique.
• Outils de maintenance associés :
  • VACUUM, ANALYZE, REINDEX

Indépendance du niveau logique

• Le niveau logique (tables, contraintes) reste indépendant du niveau externe (vues, programmes).
• Les changements internes ne doivent pas rompre les interfaces exposées.

Récapitulatif

• Indépendance des niveaux :
  • Externe ↔ Logique : assurée par les vues et vues matérialisées.
  • Logique ↔ Physique : assurée par le catalogue système (pg_catalog).
• PostgreSQL offre des outils modernes (vues, triggers, catalogues) pour préserver cette indépendance.

Compétences à acquérir

• Compréhension :
  • Identifier les situations où l’indépendance des niveaux s’applique.
  • Diagnostiquer les impacts d’une modification de schéma sur les vues existantes.
• Conception :
  • Mettre en œuvre l’indépendance des niveaux à l’aide des vues et du catalogue système.
  • Utiliser les tables pg_catalog et information_schema pour explorer la métabase PostgreSQL.