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
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 :
SELECT * FROM pg_catalog.pg_tables;
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
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 :
Maintenance et indépendance du niveau physique
- PostgreSQL réorganise le stockage sans exposer les détails aux niveaux supérieurs :
AUTOVACUUM, VACUUM, ANALYZE : nettoyage et mises à jour des
statistiques
REINDEX, CLUSTER, outils externes (ex. pg_repack)
- Les tables et vues conservent leur interface logique (nom, colonnes, contraintes).
- Les applications et vues continuent à fonctionner tant que l’interface logique est préservée.
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.
PostgreSQL aujourd’hui : mécanismes d’indépendance
- Niveau externe :
- Vues (
CREATE VIEW)
- Vues matérialisées (
CREATE MATERIALIZED VIEW)
- Fonctions / procédures
SECURITY DEFINER pour encapsuler les accès
- Niveau logique :
- Tables, contraintes, schémas logiques
- Règles de droits (
GRANT/REVOKE) et rôles
- Niveau physique :
- Catalogue système
pg_catalog et information_schema
- Structures de stockage, index, fichiers de tables
- Objectif : faire évoluer chaque niveau sans casser les autres.
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.