Semaines 7-8 : SGBD et procédures stockées

Objectifs des semaines 7 et 8

À la fin de ces deux semaines, vous saurez ce qu'un SGBD prend en charge pour vous (persistance, fiabilité, intégrité, concurrence), programmer une transaction qui respecte les propriétés ACID, définir vos tables et leurs contraintes d'intégrité en DDL, créer une vue pour les statistiques, et confier au serveur lui-même la surveillance des données avec une procédure stockée et ses déclencheurs, en pgSQL.

Déroulement

1. Sous le capot : les services de qualité (persistance, fiabilité, sécurité) et de gestion (fichiers, concurrence, langages) d'un SGBD.
2. ACID et concurrence : le compte bancaire conjoint, le dîner des philosophes, puis la transaction avec BEGIN, COMMIT et ROLLBACK.
3. L'intégrité dans la base : le DDL (CREATE, ALTER, DROP), les clés serial, les contraintes NOT NULL, UNIQUE, CHECK et FOREIGN KEY.
4. Les vues : une requête précalculée qui porte un nom, pour les statistiques sans redondance réelle.
5. Procédures stockées et déclencheurs : le langage pgSQL, la fonction journaliser() et ses trois déclencheurs. L'atelier du 27 octobre est à présence obligatoire.

Trois squelettes à garder sous la main

Les formes exactes enseignées en classe, prêtes à coller dans pgAdmin. Les variables prédéfinies d'un déclencheur : TG_OP (l'opération), NEW (la rangée après) et OLD (la rangée avant).

BEGIN;
UPDATE compte SET solde = solde - 100
WHERE id = 1;
UPDATE compte SET solde = solde + 100
WHERE id = 2;
COMMIT;
-- en cas de problème, ROLLBACK; annule tout

CREATE VIEW statistique_troupeau AS
SELECT id_troupeau, COUNT(*) AS nombre_moutons
FROM mouton
GROUP BY id_troupeau;

SELECT * FROM statistique_troupeau;

CREATE OR REPLACE FUNCTION journaliser()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO journal(operation, moment)
        VALUES('EFFACER', now());
        RETURN OLD;
    END IF;
    INSERT INTO journal(operation, moment)
    VALUES(TG_OP, now());
    RETURN NEW;
END
$$;

CREATE TRIGGER evenementChangementArticle
    BEFORE UPDATE ON public.article
    FOR EACH ROW EXECUTE PROCEDURE public.journaliser();

Notes de cours

Les diaporamas des semaines 7 et 8, en version web (diapositives d'origine, exactes) et en PDF :

Le SGBD et ses qualités

Intégrité (en 2 volets complémentaires : Architecture Système puis Pratique PostgreSQL)

Procédures stockées en pgSQL

Ressources

Évaluations en cours

Ces deux semaines nourrissent directement la dernière livraison du Contrat Auteurs (15 %) : une vue SQL pour les statistiques, la journalisation automatique des éditions et des suppressions dans une table secrète par une procédure stockée, et des statistiques quotidiennes. Le Projet Armada (25 %, semaines 4 à 7) se termine en parallèle.

• Atelier procédures stockées27 octobre 2026 - présence obligatoire : la première table et le cron se font en classe
• LIVRAISON_SURVEILLANCERemise le 27 octobre 2026 en soirée - procédures stockées 5 %