Ch. 6-8 — Vues, DCL, index

La vue : une requête stockée

Une vue est une fenêtre sur la base, qui permet à chacun de voir les données comme il le souhaite. Sa définition est simplement un SELECT sauvegardé :

CREATE VIEW vuefournisseur AS
SELECT f.nom, f.adresse, SUM(c.qute) AS somme
FROM fournisseurs f, cmds c
WHERE f.fno = c.fno
GROUP BY f.nom, f.adresse;

SELECT * FROM vuefournisseur WHERE nom = 'Dupont';

Le point capital : la vue ne contient pas de données. Elles restent stockées dans les tables d'origine, et la vue est recalculée dynamiquement à chaque requête qui y fait référence. Une vue peut porter sur plusieurs tables, plusieurs vues sur une même table, et une vue s'utilise dans n'importe quel SELECT comme une table.

Pourquoi créer des vues

• Sécurité : restreindre l'accès à un sous-ensemble de colonnes et de lignes. Deux vues fourniss_lille et fourniss_lyon font qu'aucun commercial ne voit ni les tables réelles, ni les données qui ne lui sont pas destinées.
• Indépendance des programmes : si les 10 000 programmes de l'entreprise s'appuient sur la vue vuePersonne et non sur la table, redécomposer la table normalisée ne coûte que la redéfinition de la vue — les programmes refonctionnent sans retouche.
• Simplicité : rassembler des données éparpillées ; décortiquer les sous-requêtes complexes (une vue par sous-requête).

Mettre à jour à travers une vue

Une vue n'est mise à jour que si sa définition reste simple : une seule table dans le FROM, pas de DISTINCT, ni de fonction sur colonne, ni de GROUP BY/HAVING, ni d'opérateur ensembliste, ni d'agrégat. WITH CHECK OPTION force le test de validité. Sinon, Postgres permet de décrire la traduction souhaitée par une règle de réécriture (CREATE RULE … AS ON INSERT TO la_vue DO INSTEAD INSERT INTO la_table …) — solution non normalisée, uniquement quand plusieurs traductions sont possibles. ALTER VIEW et DROP VIEW [IF EXISTS] [CASCADE | RESTRICT] complètent la panoplie.

Le DCL : qui peut faire quoi

Plusieurs personnes travaillent sur la même base : il faut gérer la confidentialité (« qui peut faire quoi ») et les accès concurrents. Trois niveaux d'utilisateurs : le DBA a les droits sur tout ; le créateur d'un objet a tous les droits sur cet objet ; tout autre utilisateur doit obtenir les droits. Le SGBD vérifie ces droits dans des tables systèmes — la méta-base. Deux ordres suffisent : GRANT pour attribuer, REVOKE pour retirer. Les privilèges principaux : CREATE, SELECT, INSERT, UPDATE, DELETE, REFERENCES (référencer une table dans une contrainte) — plus RULE, TRIGGER, USAGE.

GRANT SELECT, INSERT ON fournisseurs, produits TO pierre, paul;
GRANT SELECT ON matable TO PUBLIC;              -- tout le monde
GRANT UPDATE ON cmds TO anne WITH GRANT OPTION; -- Anne peut transmettre le droit
REVOKE UPDATE ON cmds FROM anne;
REVOKE ALL ON cmds FROM PUBLIC;

Un utilisateur possède la somme des droits qui lui ont été donnés directement, des droits d'un rôle dont il est membre et des droits donnés à PUBLIC. Les cas complexes viennent de la transmission : on ne révoque que les droits qu'on a donnés directement ; le défaut RESTRICT refuse le REVOKE s'il existe des droits dépendants, et CASCADE supprime aussi la chaîne des droits transmis — mais un bénéficiaire final peut garder le droit si un autre chemin de transmission existe. Retirer un droit à PUBLIC, utilisateur particulier, ne retire pas les droits donnés individuellement.

Le couplage vues-droits est l'outil de sécurité de base : on peut avoir des droits sur une vue sans avoir les droits sur la table associée — lire vueFournisseur sans pouvoir lire Fournisseur, voire écrire dans la vue sans aucun accès à la table.

Les index

Un index a pour unique objectif d'améliorer les recherches. Sans index, retrouver une ligne parmi n demande en moyenne n/2 comparaisons (parcours séquentiel) ; avec un index en B-arbre, quelques comparaisons suffisent quelle que soit la taille.

CREATE INDEX i_fourniss ON fournisseurs (nom ASC, prenom DESC);
REINDEX i_fourniss;
DROP INDEX i_fourniss;

Le revers : l'index est modifié à chaque mise à jour de la table, prend de la place, ralentit la saisie — et il se dégrade au fil des mises à jour, d'où une réindexation régulière (REINDEX équivaut à DROP INDEX puis CREATE INDEX). Une clé primaire implique la création automatique d'un index ; plusieurs index sont possibles par table ; on ne crée pas d'index sur une vue ; et on évite les index sur des champs à beaucoup de doublons.

Le catalogue : la méta-base

Le SGBD gère ses propres objets dans un catalogue de tables — table des tables, des colonnes, des utilisateurs… Chaque ordre du DDL et du DCL n'est qu'un raccourci vers des mises à jour du catalogue : CREATE TABLE test … exécute en réalité des INSERT INTO tables … et INSERT INTO colonnes …, et GRANT UPDATE … TO paul insère une ligne dans la table des droits. Un SGBD fonctionne donc avec un catalogue et les quatre ordres du DML — DDL et DCL ne sont qu'une illusion bien commode.

Sous Postgres, 148 tables et vues préfixées pg_ (\dS les liste) : pg_class (tables, index, vues), pg_attribute (colonnes), pg_constraint, pg_authid/pg_roles (droits) — et des vues facilitatrices comme pg_tables, dont \d+ pg_tables révèle la définition. Le catalogue information_schema est normalisé mais incomplet (aucune notion d'utilisateur) ; quand on peut s'en servir, c'est mieux, car plus portable.## Importer et exporter des données

La méta-commande \copy de psql charge un fichier dans une table — et l'inverse pour l'export :

\copy import FROM data.csv delimiter ';'
\copy candidats TO 'candidats.csv';

La méthode du TP 29, sur les vraies données électorales de 2022 (105 colonnes, encodage iso-8859-1) : étudier le format brut (file, head, wc), réencoder avec iconv -f iso-8859-1 -t utf-8, importer dans une table temporaire à colonnes génériques n1 … n105, puis ventiler vers les tables normalisées (candidats, departements, communes, bureaux, votes) par CREATE TABLE … AS SELECT DISTINCT … et des UNION ALL — avant d'ajouter clés primaires et étrangères, et de mesurer le gain avec \timing.

A retenir

• une vue stocke une requête, pas des données ; recalculée à chaque usage, une condition sur un agrégat s'y traduit en HAVING ;
• vues + GRANT/REVOKE = la politique de sécurité : droits sur une vue sans droits sur la table ; après un REVOKE, toujours vérifier qui garde quoi ;
• l'index accélère la lecture mais taxe les mises à jour et se dégrade : REINDEX régulier ;
• le DDL et le DCL ne sont que des raccourcis vers des INSERT/UPDATE/DELETE dans la méta-base.