Correction TD7 — Intégration des BD dans les Applications
Table of Contents
A. Préparation SQL minimale (côté BD)
Exercice A1 – Vérification du schéma
Énoncé résumé Dans la base issue des blocs 1 à 6, vérifier l’existence et la cohérence des tables et contraintes utilisées pour l’application “séjours de vacances” (client, village, sejour, ArchiveSejour, contraintes de chevauchement, etc.).
1. Vérifier l’existence des tables
Dans psql :
\d
On doit voir notamment :
clientvillagesejourarchivesejour(ouarchive_sejourselon ton choix de nommage)- éventuellement : vues et autres tables ajoutées dans les TD précédents.
Pour inspecter la structure d’une table :
\d client
\d village
\d sejour
\d archivesejour
Correction attendue : Les tables correspondent (à peu de chose près) au cahier des charges :
client(idc, nom, age, ...)
village(idv, ville, activite, prix, capacite, ...)
sejour(ids, idc, idv, debut, fin, ...)
archivesejour(ids, idc, idv, debut, fin, date_archivage, ...)
2. Vérifier les contraintes posées (bloc 2 et suivants)
a) NOT NULL et CHECK
Pour lister les contraintes d’une table :
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'client'::regclass;
Même chose pour village, sejour.
Ce qu’on doit retrouver (d’après le TD2 sur les contraintes) :
client.nomNOT NULL,client.ageNOT NULL.village.villeNOT NULL,village.activiteNOT NULL,village.prixNOT NULL,village.capaciteNOT NULL.sejour.idc,sejour.idv,sejour.debut,sejour.finNOT NULL.CHECK métiers typiques :
client.age BETWEEN 0 AND 120village.prix >= 0village.capacite >= 0sejour.fin >= sejour.debut
Exemple de résultat attendu pour village (forme possible) :
ck_village_prix_nonneg CHECK ((prix >= 0))
ck_village_capacite_nonneg CHECK ((capacite >= 0))
b) Clés primaires et étrangères
Toujours via pg_constraint ou simplement via \d sejour :
PK :
client(idc),village(idv),sejour(ids)FK :
sejour.idc→client.idc(souventON DELETE CASCADE)sejour.idv→village.idv(souventON DELETE RESTRICTouNO ACTION)
Exemple de résultat possible avec \d sejour :
Foreign-key constraints:
"fk_sejour_idc" FOREIGN KEY (idc) REFERENCES client(idc) ON DELETE CASCADE
"fk_sejour_idv" FOREIGN KEY (idv) REFERENCES village(idv) ON DELETE RESTRICT
c) Contrainte d’exclusion (chevauchement de séjours)
Dans TD2 (partie EXCLUDE) on a normalement défini quelque chose comme :
ALTER TABLE sejour
ADD CONSTRAINT sejour_no_overlap_per_client
EXCLUDE USING gist (
idc WITH =,
periode WITH &&
)
DEFERRABLE INITIALLY IMMEDIATE;
où periode est une colonne générée :
ALTER TABLE sejour
ADD COLUMN periode daterange
GENERATED ALWAYS AS (daterange(debut, fin, '[]')) STORED;
Pour vérifier :
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'sejour'::regclass
AND contype = 'x'; -- x = exclusion
Attendu : une contrainte d’exclusion sur (idc, periode) avec &&.
3. Comment ça impacte l’application
- Les NOT NULL et CHECK garantissent que les données invalides sont rejetées avant d’entrer en base : l’application doit gérer les erreurs de type
23502(NOT NULL),23514(CHECK). - Les PK/FK garantissent l’intégrité des références : l’application ne peut pas supprimer un
clientou unvillagesans se soucier dessejourassociés (comportementON DELETEdéterminé par la contrainte). - La contrainte d’exclusion sur
(idc, periode)empêche un client d’avoir deux séjours qui se chevauchent : si l’application tente de le faire, la requête d’INSERT échoue avec une erreur (SQLSTATE de violation d’exclusion). - L’application doit donc interpréter ces erreurs (SQLSTATE) plutôt que coder elle-même toutes les vérifications métier.
Exercice A2 – Préparer l’API SQL minimale
Énoncé résumé Créer (ou vérifier) un petit ensemble de fonctions / procédures stockées qui serviront d’“API SQL” pour l’application :
- inscription client,
- consultation de l’offre,
- disponible sur période,
- réservation (version stub ou simplifiée).
C’est aligné sur les fonctionnalités 1–4 du cahier des charges.
A2.1 Fonction / procédure d’inscription (inscrire_client)
Variante 1 – Fonction SQL simple avec RETURNING
CREATE OR REPLACE FUNCTION inscrire_client(p_nom text, p_age int)
RETURNS bigint AS $$
DECLARE
v_idc bigint;
BEGIN
INSERT INTO client(nom, age)
VALUES (p_nom, p_age)
RETURNING idc INTO v_idc;
RETURN v_idc;
END;
$$ LANGUAGE plpgsql;
Test :
SELECT inscrire_client('Alice', 30);
Variante 2 – Procédure + SELECT dans l’app
Si on préfère une procédure (pour CALL direct) :
CREATE OR REPLACE PROCEDURE inscrire_client(p_nom text, p_age int, OUT p_idc bigint)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO client(nom, age)
VALUES (p_nom, p_age)
RETURNING idc INTO p_idc;
END;
$$;
Test :
CALL inscrire_client('Bob', 42, NULL);
(Le OUT sera récupéré côté programme en utilisant CallableStatement / équivalent.)
A2.2 Fonction “offre par ville” (fonctionnalité 2)
Fonction simple qui encapsule :
Entrée : ville Sortie : {idv, ville, activité, prix}
CREATE OR REPLACE FUNCTION offre_ville(p_ville text)
RETURNS TABLE (idv bigint, ville text, activite text, prix numeric) AS $$
BEGIN
RETURN QUERY
SELECT v.idv, v.ville, v.activite, v.prix
FROM village v
WHERE v.ville = p_ville
ORDER BY v.prix DESC, v.idv ASC;
END;
$$ LANGUAGE plpgsql;
Test :
SELECT * FROM offre_ville('Rio');
A2.3 Fonction “disponibilités” simplifiée (fonctionnalité 3)
Spécification (version simplifiée) :
Entrées : ville, début, fin Sortie : {idv, activité, prix} des villages de cette ville (sans gestion fine de la capacité ici – elle viendra avec les curseurs / contrôles)
CREATE OR REPLACE FUNCTION disponibilite(p_ville text, p_debut date, p_fin date)
RETURNS TABLE (idv bigint, activite text, prix numeric) AS $$
BEGIN
-- Version minimaliste : "tous les villages de la ville"
RETURN QUERY
SELECT v.idv, v.activite, v.prix
FROM village v
WHERE v.ville = p_ville;
END;
$$ LANGUAGE plpgsql;
Test :
SELECT * FROM disponibilite('Rio', '2025-08-01', '2025-08-10');
Dans les blocs sur les curseurs et la capacité (Bloc 4), cette fonction pourra être enrichie pour vérifier la contrainte de capacité village par village.
A2.4 Procédure de réservation “stub” (fonctionnalité 4)
Cahier des charges (simplifié) :
Entrée : idc, ville, début, fin Règle : parmi les villages disponibles, choisir le plus cher ; Sortie : ids, idv, activité (et erreurs si rien de disponible).
Étape 1 – Version stub (sans capacité / chevauchement)
CREATE OR REPLACE PROCEDURE reserver(
p_idc bigint,
p_ville text,
p_debut date,
p_fin date,
OUT p_ids bigint,
OUT p_idv bigint,
OUT p_activite text
)
LANGUAGE plpgsql
AS $$
BEGIN
-- 1. Choisir le village le plus cher dans la ville
SELECT v.idv, v.activite
INTO p_idv, p_activite
FROM village v
WHERE v.ville = p_ville
ORDER BY v.prix DESC, v.idv ASC
LIMIT 1;
IF NOT FOUND THEN
RAISE EXCEPTION 'Aucun village disponible pour la ville %', p_ville
USING ERRCODE = 'P0001';
END IF;
-- 2. Créer le séjour
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (p_idc, p_idv, p_debut, p_fin)
RETURNING ids INTO p_ids;
END;
$$;
Test :
CALL reserver(1, 'Rio', '2025-08-01', '2025-08-10', NULL, NULL, NULL);
Cette procédure bénéficiera automatiquement :
- des contraintes de chevauchement (EXCLUDE),
- des CHECK sur
fin >= debut, - des FK sur idc / idv,
- des triggers d’archivage ou de contrôle si on les a posés.
La gestion d’erreur côté application (SQLSTATE, message) viendra dans les parties B/C/D.
B. Appels programmés – primitives techniques
Exercice B1 – Connexion simple
1. Python (psycopg 3)
a) Code
import psycopg
def main():
# Paramètres de connexion
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
try:
# Connexion
conn = psycopg.connect(**conninfo)
print("Connexion PostgreSQL OK")
# Bloc with = gestion auto commit/rollback + fermeture
with conn, conn.cursor() as cur:
cur.execute("SELECT 1")
(val,) = cur.fetchone()
print("Résultat de SELECT 1 :", val)
except psycopg.Error as e:
# Erreur psycopg : connexion ou exécution
print("Erreur psycopg :", e.__class__.__name__)
print("Détails :", e)
except Exception as e:
# Autre erreur Python
print("Erreur inattendue :", e)
if __name__ == "__main__":
main()
Points importants :
psycopg.connect(**conninfo)ouvre la connexion ; si host/port/user/password/dbname sont faux → exception.with conn, conn.cursor() as cur:- gère la transaction (
conn), - ferme le curseur en sortie, même en cas d’erreur.
- gère la transaction (
SELECT 1retourne une ligne, une colonne →fetchone()renvoie un tuple(1,).Gestion d’erreur :
psycopg.Errorpour les erreurs PostgreSQL (connexion, requête),Exceptionpour tout le reste.
2. Java (JDBC)
a) Dépendance côté projet
Driver PostgreSQL (org.postgresql:postgresql) dans votre classpath (Maven/Gradle ou jar).
b) Code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestConnexion {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
// Optionnel depuis JDBC 4+, le driver est auto-détecté
// try {
// Class.forName("org.postgresql.Driver");
// } catch (ClassNotFoundException e) {
// System.err.println("Driver PostgreSQL introuvable");
// return;
// }
try (Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("Connexion PostgreSQL OK");
String sql = "SELECT 1";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
int val = rs.getInt(1);
System.out.println("Résultat de SELECT 1 : " + val);
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL / connexion : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
System.err.println("Code fournisseur : " + e.getErrorCode());
}
}
}
Points importants :
URL JDBC :
jdbc:postgresql://host:port/dbname.try (Connection conn = ...)= fermeture automatique.Statementsuffit pourSELECT 1(pas besoin dePreparedStatementici).ResultSet:rs.next()pour passer à la première ligne,rs.getInt(1)récupère la 1re colonne.
Gestion d’erreurs :
SQLExceptionfournitgetMessage(),getSQLState(),getErrorCode().
3. PHP (PDO + driver pgsql)
a) Configuration minimale
- Extension
pdo_pgsqlactivée dans PHP.
b) Code
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
try {
// Connexion PDO
$pdo = new PDO($dsn, $user, $password);
// Mode erreur : Exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion PostgreSQL OK\n";
// Requête simple
$sql = "SELECT 1";
$stmt = $pdo->query($sql);
$row = $stmt->fetch(PDO::FETCH_NUM);
if ($row !== false) {
echo "Résultat de SELECT 1 : " . $row[0] . "\n";
} else {
echo "Aucun résultat\n";
}
} catch (PDOException $e) {
// Erreur connexion / requête
echo "Erreur PDO : " . $e->getMessage() . "\n";
// Si disponible : SQLSTATE, code, etc.
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points importants :
DSN PostgreSQL :
pgsql:host=...;port=...;dbname=...;.PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTIONpour éviter les retours silencieux.query("SELECT 1")renvoie unPDOStatement.fetch(PDO::FETCH_NUM)donne un tableau indexé ([0 => 1]).Gestion d’erreurs :
PDOException;errorInfo[0]= SQLSTATE (si disponible).
Exercice B2 – Requête paramétrée
But de l’exercice Écrire un programme qui :
lit un nom (fourni en dur ou en argument) ;
exécute une requête paramétrée :
SELECT idc, nom, age FROM client WHERE nom = ?affiche toutes les lignes retournées.
1. Python (psycopg 3)
a) Code
import psycopg
def rechercher_clients_par_nom(nom_recherche: str) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
try:
with psycopg.connect(**conninfo) as conn:
print("Connexion OK")
sql = """
SELECT idc, nom, age
FROM client
WHERE nom = %s
ORDER BY idc
"""
with conn.cursor() as cur:
cur.execute(sql, (nom_recherche,)) # paramètre passé séparément
rows = cur.fetchall()
if not rows:
print("Aucun client trouvé pour le nom :", nom_recherche)
else:
for (idc, nom, age) in rows:
print(f"{idc} | {nom} | {age}")
except psycopg.Error as e:
print("Erreur psycopg :", e.__class__.__name__)
print("Détails :", e)
if __name__ == "__main__":
rechercher_clients_par_nom("Alice")
b) Points importants
Paramètre dans
execute: deuxième argument(nom_recherche,)→ jamais de concaténation de chaînes"WHERE nom = '" + nom_recherche + "'".psycopgse charge :- de l’échappement correct,
- du typage,
- de la protection contre l’injection SQL.
On itère sur
rowspour afficher toutes les lignes.
2. Java (JDBC)
a) Code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class RechercherClientParNom {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
String nomRecherche = "Alice"; // ou lire depuis args / scanner
String sql = """
SELECT idc, nom, age
FROM client
WHERE nom = ?
ORDER BY idc
""";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
// Bind du paramètre
ps.setString(1, nomRecherche);
try (ResultSet rs = ps.executeQuery()) {
boolean found = false;
while (rs.next()) {
long idc = rs.getLong("idc");
String nom = rs.getString("nom");
int age = rs.getInt("age");
System.out.println(idc + " | " + nom + " | " + age);
found = true;
}
if (!found) {
System.out.println("Aucun client trouvé pour le nom : " + nomRecherche);
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL / connexion : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
b) Points importants
Utilisation d’un
PreparedStatementavec?dans la requête.ps.setString(1, nomRecherche);→ 1 = premier?.Jamais :
String sql = "SELECT ... WHERE nom = '" + nomRecherche + "'";On boucle sur
ResultSet; on teste si on a récupéré au moins une ligne pour afficher un message “aucun résultat”.
3. PHP (PDO)
a) Code
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
$nomRecherche = "Alice"; // ou $_GET / readline / etc.
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$sql = "
SELECT idc, nom, age
FROM client
WHERE nom = :nom
ORDER BY idc
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':nom' => $nomRecherche]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!$rows) {
echo "Aucun client trouvé pour le nom : $nomRecherche\n";
} else {
foreach ($rows as $row) {
echo $row['idc'] . " | " . $row['nom'] . " | " . $row['age'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
b) Points importants
Requête paramétrée avec placeholder nommé
:nom.preparepuisexecute([':nom' => $nomRecherche]):- le paramètre est passé séparément,
- PDO fait l’échappement, typage, etc.
À éviter absolument :
$sql = "SELECT ... WHERE nom = '$nomRecherche'"; $stmt = $pdo->query($sql);→ vulnérable à l’injection.
Exercice B3 – INSERT + RETURNING
But : depuis un programme, insérer un client dans la table client(idc, nom, age, …) et récupérer l’idc généré.
1. Python (psycopg 3)
a) Version simple (une insertion, commit implicite)
import psycopg
def inserer_client(nom: str, age: int) -> int:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
nouveau_idc = None
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
sql = """
INSERT INTO client(nom, age)
VALUES (%s, %s)
RETURNING idc;
"""
cur.execute(sql, (nom, age))
(nouveau_idc,) = cur.fetchone()
print(f"Nouveau client inséré, idc = {nouveau_idc}")
# bloc with conn → commit automatique si pas d’exception
except psycopg.Error as e:
print("Erreur psycopg :", e)
# bloc with conn → rollback automatique si exception
raise
return nouveau_idc
if __name__ == "__main__":
inserer_client("Charlie", 27)
Points clés :
Requête paramétrée avec
%s; les valeurs(nom, age)sont passées séparément.RETURNING idcrenvoie la clé générée →fetchone()renvoie un tuple(idc,).Avec
with conn:- si tout va bien → commit,
- si exception psycopg → rollback.
2. Java (JDBC)
Deux approches possibles pour récupérer l’ID :
RETURNING idc+prepareStatementclassique,- ou API JDBC
RETURN_GENERATED_KEYS.
Dans PostgreSQL, le plus simple est souvent d’utiliser le RETURNING.
a) Version avec RETURNING idc
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class InsertClientReturning {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
String nom = "Charlie";
int age = 27;
String sql = """
INSERT INTO client(nom, age)
VALUES (?, ?)
RETURNING idc
""";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setString(1, nom);
ps.setInt(2, age);
try (ResultSet rs = ps.executeQuery()) { // attention : executeQuery (car RETURNING)
if (rs.next()) {
long idc = rs.getLong("idc");
System.out.println("Nouveau client inséré, idc = " + idc);
} else {
System.out.println("INSERT effectué, mais aucun id retourné.");
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL / connexion : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
- On utilise
executeQuery()(et nonexecuteUpdate()) car PostgreSQL renvoie un ResultSet grâce auRETURNING. - On lit l’ID soit par index (
rs.getLong(1)), soit par nom de colonne ("idc").
b) Variante avec RETURN_GENERATED_KEYS (sans RETURNING explicite)
Pour mémoire (pas obligatoire si on reste cohérent avec le RETURNING) :
String sql = "INSERT INTO client(nom, age) VALUES (?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, nom);
ps.setInt(2, age);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
long idc = rs.getLong(1);
System.out.println("Nouveau client, idc = " + idc);
}
}
}
Mais pédagogiquement, la version RETURNING est plus transparente ici.
3. PHP (PDO)
a) Version avec RETURNING idc
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
$nom = "Charlie";
$age = 27;
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$sql = "
INSERT INTO client(nom, age)
VALUES (:nom, :age)
RETURNING idc
";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':nom' => $nom,
':age' => $age,
]);
// SELECT RETURNING → fetch obligatoire
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row !== false) {
$idc = $row['idc'];
echo "Nouveau client inséré, idc = $idc\n";
} else {
echo "INSERT effectué, mais aucun id retourné.\n";
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
- Paramètres nommés
:nom,:age. RETURNING idcfait queexecute()renvoie un statement sur lequel il faut faire unfetch.- On récupère la colonne
idcdans$row['idc'].
b) Variante sans RETURNING : lastInsertId (moins SQL-portable mais courant)
Avec PostgreSQL, si la PK est une identity/sequence, on peut utiliser :
$sql = "INSERT INTO client(nom, age) VALUES (:nom, :age)";
$stmt = $pdo->prepare($sql);
$stmt->execute([':nom' => $nom, ':age' => $age]);
$idc = $pdo->lastInsertId(); // utilisation de la séquence par défaut
echo "Nouveau client, idc = $idc\n";
Mais ça dépend de la configuration des séquences ; avec generated always as identity, RETURNING reste plus explicite et plus proche de ce qu’on a vu en SQL.
Synthèse pédagogique pour B3
Les trois versions illustrent la même idée :
Toujours utiliser des requêtes paramétrées (pas de concaténation).
Utiliser
RETURNING idccôté SQL pour récupérer la clé générée immédiatement.L’ID généré est :
- un tuple
(idc,)en Python, - une colonne dans un
ResultSeten Java, - une colonne du
fetchen PHP.
- un tuple
Ce patron sera réutilisé tel quel pour :
inscrire_client,reserver(retourids),- création de villages, etc.
Exercice B4 – Appel de fonction SQL
Hypothèse : fonction déjà créée côté BD (Bloc 3 / Exercice 1)
CREATE OR REPLACE FUNCTION compter_clients()
RETURNS integer AS $$
DECLARE
n integer;
BEGIN
SELECT COUNT(*) INTO n FROM client;
RETURN n;
END;
$$ LANGUAGE plpgsql;
Objectif : écrire un programme qui appelle compter_clients() et affiche le résultat.
Paramètres de connexion identiques aux exos précédents.
1. Python (psycopg 3)
import psycopg
def compter_clients():
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
sql = "SELECT compter_clients()"
cur.execute(sql)
(n_clients,) = cur.fetchone()
print("Nombre de clients :", n_clients)
return n_clients
except psycopg.Error as e:
print("Erreur psycopg :", e)
raise
if __name__ == "__main__":
compter_clients()
Points clés :
- Appel sous forme de
SELECT fonction(). - Résultat = une ligne, une colonne → tuple
(n,).
2. Java (JDBC)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AppelerCompterClients {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
String sql = "SELECT compter_clients()";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
System.out.println("Connexion OK");
if (rs.next()) {
int n = rs.getInt(1); // ou rs.getInt("compter_clients")
System.out.println("Nombre de clients : " + n);
} else {
System.out.println("La fonction n'a rien retourné.");
}
} catch (SQLException e) {
System.err.println("Erreur SQL / connexion : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
Statementsuffit (pas de paramètres).- Lecture par index (
1) ou par nom de colonne ("compter_clients").
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$sql = "SELECT compter_clients() AS nb";
$stmt = $pdo->query($sql);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row !== false) {
echo "Nombre de clients : " . $row['nb'] . "\n";
} else {
echo "La fonction n'a rien retourné.\n";
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
- Alias
AS nbpour obtenir un nom de colonne propre côté programme. query()suffisant ici (pas de paramètres).
Schéma conceptuel identique dans les trois langages :
SELECT compter_clients()
→ une ligne, une colonne
→ lire la valeur et l’utiliser côté application
Exercice B5 – Appel de procédure SQL
Hypothèse : côté BD on a déjà une procédure de type (Bloc 3 / 5) :
CREATE OR REPLACE PROCEDURE inscrire_client(p_nom text, p_age int)
LANGUAGE plpgsql
AS $$
BEGIN
IF p_age < 0 THEN
RAISE EXCEPTION 'Âge négatif interdit: %', p_age
USING ERRCODE = '22003';
END IF;
INSERT INTO client(nom, age)
VALUES (p_nom, p_age);
END;
$$;
Objectif de l’exercice B5 :
Appeler cette procédure depuis Python, Java et PHP avec CALL inscrire_client(?, ?) et montrer comment :
- voir si ça réussit ;
- récupérer les erreurs (EXCEPTION en PL/pgSQL → erreur côté programme, avec SQLSTATE).
1. Python (psycopg 3)
import psycopg
def appeler_inscrire_client(nom: str, age: int) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
sql = "CALL inscrire_client(%s, %s)"
cur.execute(sql, (nom, age))
# Pas de résultat, juste un effet de bord (INSERT)
print(f"Procédure inscrite avec succès : {nom}, {age}")
except psycopg.Error as e:
# Erreur PostgreSQL (y compris RAISE EXCEPTION)
# Selon la version de psycopg : e.sqlstate ou e.pgcode
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur psycopg :", e)
print("SQLSTATE :", sqlstate)
if __name__ == "__main__":
# Cas 1 : OK
appeler_inscrire_client("Alice", 30)
# Cas 2 : provoque l'exception (âge négatif)
appeler_inscrire_client("Bob", -5)
Points de correction :
CALL inscrire_client(%s, %s)→ syntaxe PostgreSQL côté SQL.- Les paramètres sont passés dans le tuple
(nom, age)→ pas de concaténation de chaînes. - Si
RAISE EXCEPTIONest déclenché en PL/pgSQL,psycopg.Errorest levée, avec un SQLSTATE (e.sqlstate/e.pgcode).
2. Java (JDBC)
Deux choix possibles pour l’appel de procédure :
PreparedStatementavec"CALL ..."- ou
CallableStatement(plus idiomatique pour procédures).
Version avec CallableStatement
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class AppelerInscrireClient {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
appelerInscrireClient(url, user, password, "Alice", 30);
appelerInscrireClient(url, user, password, "Bob", -5); // doit déclencher l'EXCEPTION PL/pgSQL
}
private static void appelerInscrireClient(String url, String user, String password,
String nom, int age) {
String sql = "CALL inscrire_client(?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, password);
CallableStatement cs = conn.prepareCall(sql)) {
System.out.println("Connexion OK");
cs.setString(1, nom);
cs.setInt(2, age);
cs.execute(); // pas de ResultSet, juste exécution
System.out.println("Procédure exécutée avec succès : " + nom + ", " + age);
} catch (SQLException e) {
System.err.println("Erreur lors de l'appel de inscrire_client(" + nom + ", " + age + ")");
System.err.println("Message : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
System.err.println("ErrorCode : " + e.getErrorCode());
}
}
}
Points de correction :
CallableStatement cs = conn.prepareCall("CALL inscrire_client(?, ?)");cs.execute()suffit, puisqu’il n’y a pas de valeur de retour.En cas de
RAISE EXCEPTIONen PL/pgSQL, uneSQLExceptionest levée avec :getSQLState()= code d’erreur PostgreSQL (ex.'22003','P0001', etc.),getMessage()= message détaillé.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
function appeler_inscrire_client(PDO $pdo, string $nom, int $age): void {
$sql = "CALL inscrire_client(:nom, :age)";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([
':nom' => $nom,
':age' => $age,
]);
echo "Procédure exécutée avec succès : $nom, $age\n";
} catch (PDOException $e) {
echo "Erreur lors de l'appel de inscrire_client($nom, $age)\n";
echo "Message : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
}
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
// Cas 1 : OK
appeler_inscrire_client($pdo, "Alice", 30);
// Cas 2 : doit déclencher l'EXCEPTION PL/pgSQL (âge négatif)
appeler_inscrire_client($pdo, "Bob", -5);
} catch (PDOException $e) {
echo "Erreur de connexion : " . $e->getMessage() . "\n";
}
Points de correction :
- Requête paramétrée avec
:nom,:age, etCALLcôté SQL. PDO::ERRMODE_EXCEPTIONpermet que tout échec (y comprisRAISE EXCEPTION) remonte enPDOException.errorInfo[0]contient le SQLSTATE renvoyé par PostgreSQL.
Schéma général à retenir :
Côté BD : une procédure PL/pgSQL avec
RAISE EXCEPTIONen cas de violation métier.Côté application :
CALLavec paramètres, et capture de l’exception :- Python :
psycopg.Error+e.sqlstate/pgcode. - Java :
SQLException+getSQLState(). - PHP :
PDOException+$e->errorInfo[0].
- Python :
C’est ce patron qui sera réutilisé pour toutes les procédures métier (réservation, annulation, purge, etc.).
Exercice B6 – Transactions
Hypothèse : on a une contrainte UNIQUE sur une colonne de client, par exemple :
ALTER TABLE client
ADD CONSTRAINT client_nom_unique UNIQUE (nom);
(adaptez la colonne à votre vraie contrainte : email, (nom, age), etc.)
1. Python (psycopg 3)
a) Code
import psycopg
def transaction_clients_unique():
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
try:
conn = psycopg.connect(**conninfo)
print("Connexion OK")
# Désactiver l'autocommit pour gérer la transaction manuellement
conn.autocommit = False
try:
with conn.cursor() as cur:
# 1. Premier INSERT
cur.execute(
"""
INSERT INTO client(nom, age)
VALUES (%s, %s)
""",
("Alice", 30),
)
print("INSERT 1 OK")
# 2. Second INSERT avec le même 'nom' -> viole la contrainte UNIQUE
cur.execute(
"""
INSERT INTO client(nom, age)
VALUES (%s, %s)
""",
("Alice", 40), # même nom -> erreur UNIQUE
)
print("INSERT 2 OK (ne devrait PAS s'afficher)")
# Si on arrive ici, tout s'est bien passé
conn.commit()
print("COMMIT")
except psycopg.Error as e:
# Erreur dans la transaction -> rollback
print("Erreur dans la transaction :", e)
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("SQLSTATE :", sqlstate)
conn.rollback()
print("ROLLBACK")
# Vérification : aucun des deux clients ne doit être présent
with conn.cursor() as cur:
cur.execute(
"SELECT idc, nom, age FROM client WHERE nom = %s",
("Alice",),
)
rows = cur.fetchall()
print("Lignes trouvées pour 'Alice' après transaction :", rows)
finally:
conn.close()
if __name__ == "__main__":
transaction_clients_unique()
Ce qu’on doit observer :
- Une erreur psycopg sur le second INSERT (violation UNIQUE, SQLSTATE
23505). - Le bloc
except psycopg.Errorexécuteconn.rollback(). - Le SELECT final ne renvoie aucune ligne pour
"Alice".
2. Java (JDBC)
a) Code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TransactionUniqueJava {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
String sqlInsert = "INSERT INTO client(nom, age) VALUES (?, ?)";
String sqlSelect = "SELECT idc, nom, age FROM client WHERE nom = ?";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("Connexion OK");
// Désactiver l'autocommit pour contrôler la transaction
conn.setAutoCommit(false);
try (PreparedStatement psInsert = conn.prepareStatement(sqlInsert)) {
// 1. Premier INSERT
psInsert.setString(1, "Alice");
psInsert.setInt(2, 30);
psInsert.executeUpdate();
System.out.println("INSERT 1 OK");
// 2. Second INSERT avec le même 'nom' -> viole UNIQUE
psInsert.setString(1, "Alice");
psInsert.setInt(2, 40);
psInsert.executeUpdate();
System.out.println("INSERT 2 OK (ne devrait PAS s'afficher)");
// Si on arrive ici, tout va bien
conn.commit();
System.out.println("COMMIT");
} catch (SQLException e) {
System.err.println("Erreur dans la transaction : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
System.err.println("Rollback en cours...");
conn.rollback();
System.out.println("ROLLBACK terminé");
}
// Vérification après la transaction
try (PreparedStatement psSelect = conn.prepareStatement(sqlSelect)) {
psSelect.setString(1, "Alice");
try (ResultSet rs = psSelect.executeQuery()) {
System.out.println("Lignes trouvées pour 'Alice' après transaction :");
boolean found = false;
while (rs.next()) {
long idc = rs.getLong("idc");
String nom = rs.getString("nom");
int age = rs.getInt("age");
System.out.println(idc + " | " + nom + " | " + age);
found = true;
}
if (!found) {
System.out.println("(aucune ligne)");
}
}
}
// Remettre l'autocommit si besoin
conn.setAutoCommit(true);
} catch (SQLException e) {
System.err.println("Erreur de connexion ou de transaction : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Observation attendue :
- Sur le second
executeUpdate()→SQLExceptionavec SQLSTATE23505. - Le
catchfaitconn.rollback(). - Le SELECT final ne renvoie aucune
Alice.
3. PHP (PDO)
a) Code
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
$sqlInsert = "INSERT INTO client(nom, age) VALUES (:nom, :age)";
$sqlSelect = "SELECT idc, nom, age FROM client WHERE nom = :nom";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
// Début de transaction
$pdo->beginTransaction();
try {
$stmtInsert = $pdo->prepare($sqlInsert);
// 1. Premier INSERT
$stmtInsert->execute([
':nom' => 'Alice',
':age' => 30,
]);
echo "INSERT 1 OK\n";
// 2. Second INSERT avec même 'nom' -> viole UNIQUE
$stmtInsert->execute([
':nom' => 'Alice',
':age' => 40,
]);
echo "INSERT 2 OK (ne devrait PAS s'afficher)\n";
// Si tout va bien
$pdo->commit();
echo "COMMIT\n";
} catch (PDOException $e) {
echo "Erreur dans la transaction : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
echo "ROLLBACK en cours...\n";
$pdo->rollBack();
echo "ROLLBACK terminé\n";
}
// Vérification : aucun Alice ne doit être présent
$stmtSelect = $pdo->prepare($sqlSelect);
$stmtSelect->execute([':nom' => 'Alice']);
$rows = $stmtSelect->fetchAll(PDO::FETCH_ASSOC);
echo "Lignes trouvées pour 'Alice' après transaction :\n";
if (!$rows) {
echo "(aucune ligne)\n";
} else {
foreach ($rows as $row) {
echo $row['idc'] . " | " . $row['nom'] . " | " . $row['age'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur connexion / transaction : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Observation attendue :
- Exception PDO lors du deuxième
execute()(violation UNIQUE). rollBack()annule les deux insertions.- Le SELECT final ne trouve aucune ligne pour
nom = 'Alice'.
Retenez que :
- Transaction = bloc
BEGIN … COMMITcontrôlé par l’application. - Une erreur (UNIQUE, CHECK, FK, EXCLUDE, RAISE EXCEPTION, trigger) invalide toute la transaction → rollback explicite.
- Après rollback, l’état de la BD = comme si les deux
INSERTn’avaient jamais été tentés.
C. Cas métier complets (fonctionnalités 1→5 & 6→11)
Hypothèse (cohérente avec A2) : on a côté BD une fonction simple qui insère le client et renvoie l’id créé :
CREATE OR REPLACE FUNCTION inscrire_client(p_nom text, p_age int)
RETURNS bigint AS $$
DECLARE
v_idc bigint;
BEGIN
INSERT INTO client(nom, age)
VALUES (p_nom, p_age)
RETURNING idc INTO v_idc;
RETURN v_idc;
END;
$$ LANGUAGE plpgsql;
Exercice C1 – Inscription client (fonctionnalité 1)
Objectif de l’exercice C1 :
Depuis un programme, demander nom et age, appeler inscrire_client, récupérer l’idc retourné, l’afficher.
1. Python (psycopg 3)
import psycopg
def inscrire_client(nom: str, age: int) -> int:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
sql = "SELECT inscrire_client(%s, %s)"
cur.execute(sql, (nom, age))
(idc,) = cur.fetchone()
return idc # commit automatique si pas d'exception
def main():
nom = input("Nom du client : ").strip()
age = int(input("Âge du client : ").strip())
try:
idc = inscrire_client(nom, age)
print(f"Client créé : idc = {idc}, nom = {nom}, age = {age}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
if __name__ == "__main__":
main()
Points clés :
SELECT inscrire_client(%s, %s)→ appel de la fonction.- Paramètres passés séparément → pas de concaténation de chaînes.
(idc,) = cur.fetchone()récupère la valeur retournée.- En cas de violation de contrainte / RAISE EXCEPTION dans la fonction, on tombe dans le
except psycopg.Error.
2. Java (JDBC)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class InscrireClient {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Nom du client : ");
String nom = scanner.nextLine().trim();
System.out.print("Âge du client : ");
int age = Integer.parseInt(scanner.nextLine().trim());
String sql = "SELECT inscrire_client(?, ?) AS idc";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setString(1, nom);
ps.setInt(2, age);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
long idc = rs.getLong("idc");
System.out.println("Client créé : idc = " + idc +
", nom = " + nom +
", age = " + age);
} else {
System.out.println("La fonction n'a rien retourné.");
}
}
} catch (SQLException e) {
System.err.println("Erreur lors de l'inscription du client");
System.err.println("Message : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
- Appel paramétré :
SELECT inscrire_client(?, ?) AS idc. PreparedStatement+setString/setInt.- Lecture du résultat via alias
idc. - Toute erreur PL/pgSQL (constraints, RAISE EXCEPTION) remonte en
SQLException.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
// Lecture simple depuis l'entrée standard (CLI)
echo "Nom du client : ";
$nom = trim(fgets(STDIN));
echo "Âge du client : ";
$age = (int) trim(fgets(STDIN));
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$sql = "SELECT inscrire_client(:nom, :age) AS idc";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':nom' => $nom,
':age' => $age,
]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row !== false) {
$idc = $row['idc'];
echo "Client créé : idc = $idc, nom = $nom, age = $age\n";
} else {
echo "La fonction n'a rien retourné.\n";
}
} catch (PDOException $e) {
echo "Erreur lors de l'inscription du client : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
- Requête paramétrée avec placeholders nommés
:nom,:age. SELECT inscrire_client(:nom, :age) AS idc.fetch(PDO::FETCH_ASSOC)pour récupérer l’idc.- Gestion d’erreur standard
PDOExceptionavec SQLSTATE dans$e->errorInfo[0].
Exercice C2 – Consulter l’offre d’une ville (fonctionnalité 2)
But : depuis un programme, demander une ville, récupérer et afficher la liste des villages/activités correspondants.
Requête de base :
SELECT idv, ville, activite, prix
FROM village
WHERE ville = ?
ORDER BY prix DESC, idv;
(ou bien SELECT * FROM offre_ville(?) si on utilise la fonction enveloppe.)
1. Python (psycopg 3)
import psycopg
def consulter_offre_ville(ville: str) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = """
SELECT idv, ville, activite, prix
FROM village
WHERE ville = %s
ORDER BY prix DESC, idv
"""
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute(sql, (ville,))
rows = cur.fetchall()
if not rows:
print(f"Aucune offre pour la ville : {ville}")
return
print(f"Offres pour la ville : {ville}")
for (idv, v_ville, activite, prix) in rows:
print(f"{idv} | {v_ville} | {activite} | {prix}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
def main():
ville = input("Ville : ").strip()
consulter_offre_ville(ville)
if __name__ == "__main__":
main()
Variantes possibles :
- remplacer le SQL par
SELECT * FROM offre_ville(%s)si on a la fonctionoffre_ville.
2. Java (JDBC)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class ConsulterOffreVille {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Ville : ");
String ville = scanner.nextLine().trim();
String sql = """
SELECT idv, ville, activite, prix
FROM village
WHERE ville = ?
ORDER BY prix DESC, idv
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setString(1, ville);
try (ResultSet rs = ps.executeQuery()) {
boolean found = false;
System.out.println("Offres pour la ville : " + ville);
while (rs.next()) {
long idv = rs.getLong("idv");
String vVille = rs.getString("ville");
String activite = rs.getString("activite");
double prix = rs.getDouble("prix");
System.out.println(idv + " | " + vVille + " | " + activite + " | " + prix);
found = true;
}
if (!found) {
System.out.println("(aucune offre)");
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Variante avec API SQL :
String sql = "SELECT * FROM offre_ville(?)";
et même logique derrière.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Ville : ";
$ville = trim(fgets(STDIN));
$sql = "
SELECT idv, ville, activite, prix
FROM village
WHERE ville = :ville
ORDER BY prix DESC, idv
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([':ville' => $ville]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!$rows) {
echo "Aucune offre pour la ville : $ville\n";
} else {
echo "Offres pour la ville : $ville\n";
foreach ($rows as $row) {
echo $row['idv'] . " | "
. $row['ville'] . " | "
. $row['activite'] . " | "
. $row['prix'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Variante API SQL :
$sql = "SELECT * FROM offre_ville(:ville)";
Même pattern d’appel.
Point conceptuelv:
- requête paramétrée, jamais de concaténation,
- logique “lecture seule” → pas besoin de transaction explicite,
- une API SQL de type
offre_villepermet de figer côté BD les règles de tri / filtrage si on le souhaite.
Exercice C3 – Disponibilités (fonctionnalité 3)
But : depuis un programme, demander ville, date de début, date de fin, appeler la BD pour récupérer les villages disponibles sur cette période, puis afficher les résultats.
Deux variantes côté SQL, à adapter selon ce que on a réellement en BD :
Sans API SQL (direct sur la table) :
SELECT idv, activite, prix FROM village WHERE ville = ? ORDER BY prix DESC, idv;Avec API SQL (fonction déjà écrite en A2) :
SELECT * FROM disponibilite(?, ?, ?);
On prend la variante API SQL dans les exemples ; si on pourrait rester sur la table directe, il suffit de remplacer la requête.
1. Python (psycopg 3)
import psycopg
from datetime import date
def consulter_disponibilites(ville: str, debut: date, fin: date) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = """
SELECT idv, activite, prix
FROM disponibilite(%s, %s, %s)
ORDER BY prix DESC, idv
"""
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute(sql, (ville, debut, fin))
rows = cur.fetchall()
print(f"Disponibilités pour {ville} du {debut} au {fin}")
if not rows:
print("(aucune offre disponible)")
return
for (idv, activite, prix) in rows:
print(f"{idv} | {activite} | {prix}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
def main():
ville = input("Ville : ").strip()
debut_str = input("Date de début (YYYY-MM-DD) : ").strip()
fin_str = input("Date de fin (YYYY-MM-DD) : ").strip()
debut = date.fromisoformat(debut_str)
fin = date.fromisoformat(fin_str)
consulter_disponibilites(ville, debut, fin)
if __name__ == "__main__":
main()
Points pédagogiques :
- Conversion des chaînes en
datecôté Python (fromisoformat). - Paramètres passés séparément (
(ville, debut, fin)), jamais de concaténation.
2. Java (JDBC)
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class ConsulterDisponibilites {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Ville : ");
String ville = scanner.nextLine().trim();
System.out.print("Date de début (YYYY-MM-DD) : ");
String debutStr = scanner.nextLine().trim();
System.out.print("Date de fin (YYYY-MM-DD) : ");
String finStr = scanner.nextLine().trim();
Date debut = Date.valueOf(debutStr); // java.sql.Date
Date fin = Date.valueOf(finStr);
String sql = """
SELECT idv, activite, prix
FROM disponibilite(?, ?, ?)
ORDER BY prix DESC, idv
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setString(1, ville);
ps.setDate(2, debut);
ps.setDate(3, fin);
try (ResultSet rs = ps.executeQuery()) {
System.out.println("Disponibilités pour " + ville +
" du " + debut + " au " + fin);
boolean found = false;
while (rs.next()) {
long idv = rs.getLong("idv");
String activite = rs.getString("activite");
double prix = rs.getDouble("prix");
System.out.println(idv + " | " + activite + " | " + prix);
found = true;
}
if (!found) {
System.out.println("(aucune offre disponible)");
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points pédagogiques :
java.sql.Date.valueOf("YYYY-MM-DD")pour construire les dates.ps.setDate(2, debut)/ps.setDate(3, fin)→ mapping propre type SQL / type Java.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Ville : ";
$ville = trim(fgets(STDIN));
echo "Date de début (YYYY-MM-DD) : ";
$debut = trim(fgets(STDIN));
echo "Date de fin (YYYY-MM-DD) : ";
$fin = trim(fgets(STDIN));
$sql = "
SELECT idv, activite, prix
FROM disponibilite(:ville, :debut, :fin)
ORDER BY prix DESC, idv
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':ville' => $ville,
':debut' => $debut, // chaîne 'YYYY-MM-DD', PDO la passera en date
':fin' => $fin,
]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Disponibilités pour $ville du $debut au $fin\n";
if (!$rows) {
echo "(aucune offre disponible)\n";
} else {
foreach ($rows as $row) {
echo $row['idv'] . " | "
. $row['activite'] . " | "
. $row['prix'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points pédagogiques :
- Paramètres
:ville,:debut,:fin→ pas de concaténation. - On laisse PDO gérer la conversion de la chaîne
'YYYY-MM-DD'en type date PostgreSQL.
Retenez que :
L’application ne recalcule pas les règles de disponibilité.
Elle délègue à la BD via une API SQL (
disponibilite) et se contente de :- récupérer les paramètres utilisateur,
- appeler la fonction avec requête paramétrée,
- afficher le résultat.
Exercice C4 – Réservation simple (fonctionnalité 4)
Hypothèse côté BD (version cohérente avec A2/C1–C3) : procédure PL/pgSQL déjà définie :
CREATE OR REPLACE PROCEDURE reserver(
p_idc bigint,
p_ville text,
p_debut date,
p_fin date,
OUT p_ids bigint,
OUT p_idv bigint,
OUT p_activite text
)
LANGUAGE plpgsql
AS $$
BEGIN
-- 1. Choisir le village le plus cher dans la ville
SELECT v.idv, v.activite
INTO p_idv, p_activite
FROM village v
WHERE v.ville = p_ville
ORDER BY v.prix DESC, v.idv ASC
LIMIT 1;
IF NOT FOUND THEN
RAISE EXCEPTION 'Aucun village disponible pour la ville %', p_ville
USING ERRCODE = 'P0001';
END IF;
-- 2. Créer le séjour
INSERT INTO sejour(idc, idv, debut, fin)
VALUES (p_idc, p_idv, p_debut, p_fin)
RETURNING ids INTO p_ids;
END;
$$;
PostgreSQL renvoie les paramètres OUT comme une ligne de résultat quand on fait CALL reserver(...).
Objectif C4 : programme qui :
- lit
idc,ville,début,fin; - appelle
reserver; - affiche
ids,idv,activite; - gère propres messages en cas d’erreur (ville inconnue, chevauchement, etc.).
1. Python (psycopg 3)
import psycopg
from datetime import date
def reserver_sejour(idc: int, ville: str, debut: date, fin: date) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = "CALL reserver(%s, %s, %s, %s)"
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute(sql, (idc, ville, debut, fin))
# PostgreSQL retourne une ligne avec les OUT
row = cur.fetchone()
if row is None:
print("La procédure n'a rien retourné (cas anormal).")
return
ids, idv, activite = row
print("Séjour réservé :")
print(f" ids = {ids}")
print(f" idv = {idv}")
print(f" ville = {ville}")
print(f" activité = {activite}")
print(f" période = {debut} → {fin}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
# Interprétation possible :
# P0001 : aucun village disponible
# 23xxx : contrainte (UNIQUE, CHECK, EXCLUDE, FK...)
if sqlstate == "P0001":
print("Aucune offre disponible pour cette ville / période.")
elif sqlstate and sqlstate.startswith("23"):
print("Problème d'intégrité (chevauchement, capacité, etc.).")
def main():
idc_str = input("Id client (idc) : ").strip()
ville = input("Ville : ").strip()
debut_str = input("Date de début (YYYY-MM-DD) : ").strip()
fin_str = input("Date de fin (YYYY-MM-DD) : ").strip()
idc = int(idc_str)
debut = date.fromisoformat(debut_str)
fin = date.fromisoformat(fin_str)
reserver_sejour(idc, ville, debut, fin)
if __name__ == "__main__":
main()
Points essentiels :
CALL reserver(%s, %s, %s, %s)→ les 4 IN.fetchone()récupère la ligne des OUT :(ids, idv, activite).- Gestion d’erreurs via SQLSTATE (
P0001pour l’exception métier,23xxxpour contraintes).
2. Java (JDBC)
Ici, on montre l’usage “orthodoxe” de CallableStatement avec OUT.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
public class ReserverSejour {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Id client (idc) : ");
long idc = Long.parseLong(scanner.nextLine().trim());
System.out.print("Ville : ");
String ville = scanner.nextLine().trim();
System.out.print("Date de début (YYYY-MM-DD) : ");
Date debut = Date.valueOf(scanner.nextLine().trim());
System.out.print("Date de fin (YYYY-MM-DD) : ");
Date fin = Date.valueOf(scanner.nextLine().trim());
reserverSejour(idc, ville, debut, fin);
}
private static void reserverSejour(long idc, String ville, Date debut, Date fin) {
// 4 paramètres IN + 3 OUT (ids, idv, activité)
String callSql = "{ call reserver(?, ?, ?, ?, ?, ?, ?) }";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
CallableStatement cs = conn.prepareCall(callSql)) {
System.out.println("Connexion OK");
// IN
cs.setLong(1, idc);
cs.setString(2, ville);
cs.setDate(3, debut);
cs.setDate(4, fin);
// OUT
cs.registerOutParameter(5, java.sql.Types.BIGINT); // p_ids
cs.registerOutParameter(6, java.sql.Types.BIGINT); // p_idv
cs.registerOutParameter(7, java.sql.Types.VARCHAR); // p_activite
cs.execute();
long ids = cs.getLong(5);
long idv = cs.getLong(6);
String activite = cs.getString(7);
System.out.println("Séjour réservé :");
System.out.println(" ids = " + ids);
System.out.println(" idv = " + idv);
System.out.println(" ville = " + ville);
System.out.println(" activité = " + activite);
System.out.println(" période = " + debut + " → " + fin);
} catch (SQLException e) {
System.err.println("Erreur lors de la réservation : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
// Interprétations possibles
if ("P0001".equals(e.getSQLState())) {
System.err.println("Aucune offre disponible pour cette ville / période.");
} else if (e.getSQLState() != null && e.getSQLState().startsWith("23")) {
System.err.println("Problème d'intégrité (chevauchement, capacité, etc.).");
}
}
}
}
Points essentiels :
Syntaxe JDBC standard :
"{ call reserver(?, ?, ?, ?, ?, ?, ?) }".- 1–4 :
IN, - 5–7 :
OUT.
- 1–4 :
registerOutParameter+getLong/getStringpour récupérer les OUT.Gestion d’erreur par
SQLException.getSQLState().
3. PHP (PDO)
Avec PostgreSQL + OUT, on peut utiliser CALL et simplement fetch() la ligne retournée.
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Id client (idc) : ";
$idc = (int) trim(fgets(STDIN));
echo "Ville : ";
$ville = trim(fgets(STDIN));
echo "Date de début (YYYY-MM-DD) : ";
$debut = trim(fgets(STDIN));
echo "Date de fin (YYYY-MM-DD) : ";
$fin = trim(fgets(STDIN));
$sql = "CALL reserver(:idc, :ville, :debut, :fin)";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':idc' => $idc,
':ville' => $ville,
':debut' => $debut,
':fin' => $fin,
]);
// PostgreSQL renvoie une ligne contenant les OUT
$row = $stmt->fetch(PDO::FETCH_NUM);
if ($row === false) {
echo "La procédure n'a rien retourné (cas anormal).\n";
exit;
}
list($ids, $idv, $activite) = $row;
echo "Séjour réservé :\n";
echo " ids = $ids\n";
echo " idv = $idv\n";
echo " ville = $ville\n";
echo " activité = $activite\n";
echo " période = $debut → $fin\n";
} catch (PDOException $e) {
echo "Erreur lors de la réservation : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
$sqlstate = $e->errorInfo[0];
echo "SQLSTATE : $sqlstate\n";
if ($sqlstate === 'P0001') {
echo "Aucune offre disponible pour cette ville / période.\n";
} elseif (str_starts_with($sqlstate, '23')) {
echo "Problème d'intégrité (chevauchement, capacité, etc.).\n";
}
}
}
Points essentiels :
CALL reserver(:idc, :ville, :debut, :fin)avec placeholders nommés.fetch(PDO::FETCH_NUM)→ tuple des OUT (dans l’ordre de la signature).PDOExceptionavecerrorInfo[0]pour le SQLSTATE.
Retenez que :
Toute la logique métier de réservation est côté BD (procédure
reserver+ contraintes).Les programmes ne font que :
- collecter les entrées utilisateur,
- appeler la procédure avec des paramètres correctement typés,
- lire les OUT,
- interpréter proprement les erreurs via SQLSTATE.
Exercice C5 – Consulter ses séjours (fonctionnalité 5)
But : depuis un programme, demander l’idc (ou le nom, mais on va rester sur idc), puis afficher tous les séjours de ce client, avec info village.
Requête typique :
SELECT s.ids, s.idv, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN village v ON s.idv = v.idv
WHERE s.idc = ?
ORDER BY s.debut DESC;
1. Python (psycopg 3)
import psycopg
def consulter_sejours_client(idc: int) -> None:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = """
SELECT s.ids, s.idv, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN village v ON s.idv = v.idv
WHERE s.idc = %s
ORDER BY s.debut DESC
"""
try:
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute(sql, (idc,))
rows = cur.fetchall()
print(f"Séjours du client idc = {idc}")
if not rows:
print("(aucun séjour)")
return
for (ids, idv, ville, activite, debut, fin) in rows:
print(f"{ids} | {idv} | {ville} | {activite} | {debut} → {fin}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
def main():
idc_str = input("Id client (idc) : ").strip()
idc = int(idc_str)
consulter_sejours_client(idc)
if __name__ == "__main__":
main()
Points clés :
- Jointure
sejour–villagepour récupérer ville + activité. - Paramétrage avec
(idc,).
2. Java (JDBC)
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class ConsulterSejoursClient {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Id client (idc) : ");
long idc = Long.parseLong(scanner.nextLine().trim());
consulterSejoursClient(idc);
}
private static void consulterSejoursClient(long idc) {
String sql = """
SELECT s.ids, s.idv, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN village v ON s.idv = v.idv
WHERE s.idc = ?
ORDER BY s.debut DESC
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setLong(1, idc);
try (ResultSet rs = ps.executeQuery()) {
System.out.println("Séjours du client idc = " + idc);
boolean found = false;
while (rs.next()) {
long ids = rs.getLong("ids");
long idv = rs.getLong("idv");
String ville = rs.getString("ville");
String activite = rs.getString("activite");
Date debut = rs.getDate("debut");
Date fin = rs.getDate("fin");
System.out.println(
ids + " | " + idv + " | " +
ville + " | " + activite + " | " +
debut + " → " + fin
);
found = true;
}
if (!found) {
System.out.println("(aucun séjour)");
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
PreparedStatement+setLong(1, idc).- Lecture des dates en
java.sql.Date.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Id client (idc) : ";
$idc = (int) trim(fgets(STDIN));
$sql = "
SELECT s.ids, s.idv, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN village v ON s.idv = v.idv
WHERE s.idc = :idc
ORDER BY s.debut DESC
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([':idc' => $idc]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Séjours du client idc = $idc\n";
if (!$rows) {
echo "(aucun séjour)\n";
} else {
foreach ($rows as $row) {
echo $row['ids'] . " | "
. $row['idv'] . " | "
. $row['ville'] . " | "
. $row['activite'] . " | "
. $row['debut'] . " → " . $row['fin'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
- Placeholder nommé
:idc. fetchAll(PDO::FETCH_ASSOC)pour simplifier l’affichage.
Retenez que :
Pour les “consultations”, la logique reste côté SQL (jointures, tri).
L’application ne fait que :
- passer un identifiant client,
- exécuter une requête paramétrée,
- formatter le résultat.
Exercice C6 – Créer un village (fonctionnalité 6)
But : depuis un programme, saisir les infos d’un village (ville, activité, prix, capacité), insérer dans la table village et récupérer l’idv.
Requête SQL de base :
INSERT INTO village(ville, activite, prix, capacite)
VALUES (?, ?, ?, ?)
RETURNING idv;
Adaptez les noms de colonnes à votre schéma exact.
1. Python (psycopg 3)
import psycopg
from decimal import Decimal
def creer_village(ville: str, activite: str, prix: Decimal, capacite: int) -> int:
conninfo = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = """
INSERT INTO village(ville, activite, prix, capacite)
VALUES (%s, %s, %s, %s)
RETURNING idv;
"""
with psycopg.connect(**conninfo) as conn:
with conn.cursor() as cur:
cur.execute(sql, (ville, activite, prix, capacite))
(idv,) = cur.fetchone()
return idv # commit auto si pas d’exception
def main():
ville = input("Ville : ").strip()
activite = input("Activité : ").strip()
prix_str = input("Prix (ex: 199.99) : ").strip()
capacite_str = input("Capacité : ").strip()
prix = Decimal(prix_str)
capacite = int(capacite_str)
try:
idv = creer_village(ville, activite, prix, capacite)
print(f"Village créé : idv = {idv}, {ville}, {activite}, {prix}, cap={capacite}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
if __name__ == "__main__":
main()
Points clés :
RETURNING idv+fetchone()pour récupérer l’identifiant.- Paramètres passés dans le tuple
(ville, activite, prix, capacite).
2. Java (JDBC)
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class CreerVillage {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Ville : ");
String ville = scanner.nextLine().trim();
System.out.print("Activité : ");
String activite = scanner.nextLine().trim();
System.out.print("Prix (ex: 199.99) : ");
BigDecimal prix = new BigDecimal(scanner.nextLine().trim());
System.out.print("Capacité : ");
int capacite = Integer.parseInt(scanner.nextLine().trim());
creerVillage(ville, activite, prix, capacite);
}
private static void creerVillage(String ville, String activite,
BigDecimal prix, int capacite) {
String sql = """
INSERT INTO village(ville, activite, prix, capacite)
VALUES (?, ?, ?, ?)
RETURNING idv
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql)) {
System.out.println("Connexion OK");
ps.setString(1, ville);
ps.setString(2, activite);
ps.setBigDecimal(3, prix);
ps.setInt(4, capacite);
try (ResultSet rs = ps.executeQuery()) { // RETURNING → ResultSet
if (rs.next()) {
long idv = rs.getLong("idv");
System.out.println("Village créé : idv = " + idv +
", ville = " + ville +
", activité = " + activite +
", prix = " + prix +
", capacité = " + capacite);
} else {
System.out.println("INSERT effectué, mais aucun idv retourné.");
}
}
} catch (SQLException e) {
System.err.println("Erreur lors de la création du village");
System.err.println("Message : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
PreparedStatementavecRETURNING idv→executeQuery().BigDecimalpour mapper unNUMERIC/DECIMAL.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Ville : ";
$ville = trim(fgets(STDIN));
echo "Activité : ";
$activite = trim(fgets(STDIN));
echo "Prix (ex: 199.99) : ";
$prix = trim(fgets(STDIN));
echo "Capacité : ";
$capacite = (int) trim(fgets(STDIN));
$sql = "
INSERT INTO village(ville, activite, prix, capacite)
VALUES (:ville, :activite, :prix, :capacite)
RETURNING idv
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':ville' => $ville,
':activite' => $activite,
':prix' => $prix,
':capacite' => $capacite,
]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row !== false) {
$idv = $row['idv'];
echo "Village créé : idv = $idv, ville = $ville, activité = $activite, prix = $prix, capacité = $capacite\n";
} else {
echo "INSERT effectué, mais aucun idv retourné.\n";
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
RETURNING idv+fetch()pour récupérer la clé.- Paramètres nommés
:ville,:activite,:prix,:capacite.
Ce patron est réutilisable pour toute création d’entité (client, sejour, etc.).
Exercice C7 – Modifier un village (fonctionnalité 7)
But : depuis un programme, pour un idv donné :
- lire l’état courant du village ;
- demander/modifier certains champs autorisés (ex.
prix,capacite) ; - faire l’
UPDATE; - relire et afficher l’état final.
Requête de base :
SELECT idv, ville, activite, prix, capacite
FROM village
WHERE idv = ?;
UPDATE village
SET prix = ?, capacite = ?
WHERE idv = ?;
1. Python (psycopg 3)
import psycopg
from decimal import Decimal
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
def charger_village(idv: int):
sql = """
SELECT idv, ville, activite, prix, capacite
FROM village
WHERE idv = %s
"""
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
cur.execute(sql, (idv,))
row = cur.fetchone()
return row # None si absent
def modifier_village(idv: int, nouveau_prix: Decimal, nouvelle_capacite: int):
sql = """
UPDATE village
SET prix = %s, capacite = %s
WHERE idv = %s
"""
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
cur.execute(sql, (nouveau_prix, nouvelle_capacite, idv))
if cur.rowcount == 0:
print("Aucun village mis à jour (idv inconnu ?).")
return
print("Village mis à jour.")
def main():
idv = int(input("Id village (idv) : ").strip())
# 1. État initial
row = charger_village(idv)
if row is None:
print(f"Aucun village avec idv = {idv}")
return
idv0, ville, activite, prix, capacite = row
print("État initial :")
print(f" idv = {idv0}")
print(f" ville = {ville}")
print(f" activité = {activite}")
print(f" prix = {prix}")
print(f" capacité = {capacite}")
# 2. Demander nouvelles valeurs
prix_str = input(f"Nouveau prix (actuel {prix}) : ").strip()
cap_str = input(f"Nouvelle capacité (actuelle {capacite}) : ").strip()
nouveau_prix = Decimal(prix_str) if prix_str else prix
nouvelle_cap = int(cap_str) if cap_str else capacite
try:
modifier_village(idv, nouveau_prix, nouvelle_cap)
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
return
# 3. État final
row = charger_village(idv)
if row:
_, ville, activite, prix2, capacite2 = row
print("État final :")
print(f" idv = {idv}")
print(f" ville = {ville}")
print(f" activité = {activite}")
print(f" prix = {prix2}")
print(f" capacité = {capacite2}")
if __name__ == "__main__":
main()
2. Java (JDBC)
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class ModifierVillage {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.print("Id village (idv) : ");
long idv = Long.parseLong(scanner.nextLine().trim());
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
System.out.println("Connexion OK");
// 1. État initial
Village v = chargerVillage(conn, idv);
if (v == null) {
System.out.println("Aucun village avec idv = " + idv);
return;
}
System.out.println("État initial :");
System.out.println(" idv = " + v.idv);
System.out.println(" ville = " + v.ville);
System.out.println(" activité = " + v.activite);
System.out.println(" prix = " + v.prix);
System.out.println(" capacité = " + v.capacite);
// 2. Demander nouvelles valeurs
System.out.print("Nouveau prix (actuel " + v.prix + ") : ");
String prixStr = scanner.nextLine().trim();
System.out.print("Nouvelle capacité (actuelle " + v.capacite + ") : ");
String capStr = scanner.nextLine().trim();
BigDecimal nouveauPrix = prixStr.isEmpty() ? v.prix : new BigDecimal(prixStr);
int nouvelleCapacite = capStr.isEmpty() ? v.capacite : Integer.parseInt(capStr);
// 3. UPDATE
modifierVillage(conn, idv, nouveauPrix, nouvelleCapacite);
// 4. État final
Village v2 = chargerVillage(conn, idv);
if (v2 != null) {
System.out.println("État final :");
System.out.println(" idv = " + v2.idv);
System.out.println(" ville = " + v2.ville);
System.out.println(" activité = " + v2.activite);
System.out.println(" prix = " + v2.prix);
System.out.println(" capacité = " + v2.capacite);
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
// Structure simple pour manipuler un village
private static class Village {
long idv;
String ville;
String activite;
BigDecimal prix;
int capacite;
}
private static Village chargerVillage(Connection conn, long idv) throws SQLException {
String sql = """
SELECT idv, ville, activite, prix, capacite
FROM village
WHERE idv = ?
""";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, idv);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
return null;
}
Village v = new Village();
v.idv = rs.getLong("idv");
v.ville = rs.getString("ville");
v.activite = rs.getString("activite");
v.prix = rs.getBigDecimal("prix");
v.capacite = rs.getInt("capacite");
return v;
}
}
}
private static void modifierVillage(Connection conn, long idv,
BigDecimal nouveauPrix, int nouvelleCapacite)
throws SQLException {
String sql = """
UPDATE village
SET prix = ?, capacite = ?
WHERE idv = ?
""";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setBigDecimal(1, nouveauPrix);
ps.setInt(2, nouvelleCapacite);
ps.setLong(3, idv);
int lignes = ps.executeUpdate();
if (lignes == 0) {
System.out.println("Aucun village mis à jour (idv inconnu ?).");
} else {
System.out.println("Village mis à jour.");
}
}
}
}
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Id village (idv) : ";
$idv = (int) trim(fgets(STDIN));
$sqlSelect = "
SELECT idv, ville, activite, prix, capacite
FROM village
WHERE idv = :idv
";
$sqlUpdate = "
UPDATE village
SET prix = :prix, capacite = :capacite
WHERE idv = :idv
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
// 1. État initial
$stmt = $pdo->prepare($sqlSelect);
$stmt->execute([':idv' => $idv]);
$village = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$village) {
echo "Aucun village avec idv = $idv\n";
exit;
}
echo "État initial :\n";
echo " idv = {$village['idv']}\n";
echo " ville = {$village['ville']}\n";
echo " activité = {$village['activite']}\n";
echo " prix = {$village['prix']}\n";
echo " capacité = {$village['capacite']}\n";
// 2. Nouvelles valeurs
echo "Nouveau prix (actuel {$village['prix']}) : ";
$prixStr = trim(fgets(STDIN));
echo "Nouvelle capacité (actuelle {$village['capacite']}) : ";
$capStr = trim(fgets(STDIN));
$nouveauPrix = $prixStr === '' ? $village['prix'] : $prixStr;
$nouvelleCap = $capStr === '' ? $village['capacite'] : (int) $capStr;
// 3. UPDATE
$stmtUp = $pdo->prepare($sqlUpdate);
$stmtUp->execute([
':prix' => $nouveauPrix,
':capacite' => $nouvelleCap,
':idv' => $idv,
]);
if ($stmtUp->rowCount() === 0) {
echo "Aucun village mis à jour (idv inconnu ?)\n";
} else {
echo "Village mis à jour.\n";
}
// 4. État final
$stmt = $pdo->prepare($sqlSelect);
$stmt->execute([':idv' => $idv]);
$village2 = $stmt->fetch(PDO::FETCH_ASSOC);
echo "État final :\n";
echo " idv = {$village2['idv']}\n";
echo " ville = {$village2['ville']}\n";
echo " activité = {$village2['activite']}\n";
echo " prix = {$village2['prix']}\n";
echo " capacité = {$village2['capacite']}\n";
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Idée que ça doit fixer : lecture initiale → modification contrôlée des seuls champs autorisés → update paramétré → relecture pour vérification.
Exercice C8 – Consulter les séjours filtrés (fonctionnalité 8)
Requête cible (logique) :
SELECT s.ids, c.idc, c.nom, v.idv, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN client c ON s.idc = c.idc
JOIN village v ON s.idv = v.idv
WHERE 1 = 1
[AND v.ville = :ville]
[AND s.debut >= :debut]
[AND s.fin <= :fin]
[AND s.idc = :idc]
ORDER BY s.debut DESC;
Les crochets indiquent des filtres facultatifs.
1. Python (psycopg 3)
import psycopg
from datetime import date
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
def consulter_sejours(ville=None, debut=None, fin=None, idc=None):
base_sql = """
SELECT s.ids, c.idc, c.nom, v.idv, v.ville, v.activite,
s.debut, s.fin
FROM sejour s
JOIN client c ON s.idc = c.idc
JOIN village v ON s.idv = v.idv
WHERE 1 = 1
"""
conditions = []
params = []
if ville is not None:
conditions.append("AND v.ville = %s")
params.append(ville)
if debut is not None:
conditions.append("AND s.debut >= %s")
params.append(debut)
if fin is not None:
conditions.append("AND s.fin <= %s")
params.append(fin)
if idc is not None:
conditions.append("AND s.idc = %s")
params.append(idc)
sql = base_sql + "\n" + "\n".join(conditions) + "\nORDER BY s.debut DESC"
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
cur.execute(sql, params)
rows = cur.fetchall()
print("Séjours filtrés :")
if not rows:
print("(aucun résultat)")
return
for (ids, idc, nom, idv, v_ville, activite, d_debut, d_fin) in rows:
print(f"{ids} | {idc} ({nom}) | {idv} | {v_ville} | "
f"{activite} | {d_debut} → {d_fin}")
def main():
ville = input("Ville (laisser vide pour ignorer) : ").strip() or None
debut_str = input("Date début (YYYY-MM-DD, vide pour ignorer) : ").strip()
debut = date.fromisoformat(debut_str) if debut_str else None
fin_str = input("Date fin (YYYY-MM-DD, vide pour ignorer) : ").strip()
fin = date.fromisoformat(fin_str) if fin_str else None
idc_str = input("Id client (idc, vide pour ignorer) : ").strip()
idc = int(idc_str) if idc_str else None
consulter_sejours(ville=ville, debut=debut, fin=fin, idc=idc)
if __name__ == "__main__":
main()
2. Java (JDBC)
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class ConsulterSejoursFiltres {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("Ville (vide = ignorer) : ");
String ville = sc.nextLine().trim();
if (ville.isEmpty()) ville = null;
System.out.print("Date début (YYYY-MM-DD, vide = ignorer) : ");
String debutStr = sc.nextLine().trim();
Date debut = debutStr.isEmpty() ? null : Date.valueOf(debutStr);
System.out.print("Date fin (YYYY-MM-DD, vide = ignorer) : ");
String finStr = sc.nextLine().trim();
Date fin = finStr.isEmpty() ? null : Date.valueOf(finStr);
System.out.print("Id client (idc, vide = ignorer) : ");
String idcStr = sc.nextLine().trim();
Long idc = idcStr.isEmpty() ? null : Long.parseLong(idcStr);
consulterSejours(ville, debut, fin, idc);
}
private static void consulterSejours(String ville, Date debut, Date fin, Long idc) {
String baseSql = """
SELECT s.ids, c.idc, c.nom, v.idv, v.ville, v.activite,
s.debut, s.fin
FROM sejour s
JOIN client c ON s.idc = c.idc
JOIN village v ON s.idv = v.idv
WHERE 1 = 1
""";
StringBuilder sql = new StringBuilder(baseSql);
List<Object> params = new ArrayList<>();
if (ville != null) {
sql.append(" AND v.ville = ?\n");
params.add(ville);
}
if (debut != null) {
sql.append(" AND s.debut >= ?\n");
params.add(debut);
}
if (fin != null) {
sql.append(" AND s.fin <= ?\n");
params.add(fin);
}
if (idc != null) {
sql.append(" AND s.idc = ?\n");
params.add(idc);
}
sql.append(" ORDER BY s.debut DESC");
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps = conn.prepareStatement(sql.toString())) {
System.out.println("Connexion OK");
int index = 1;
for (Object param : params) {
if (param instanceof String) {
ps.setString(index, (String) param);
} else if (param instanceof Date) {
ps.setDate(index, (Date) param);
} else if (param instanceof Long) {
ps.setLong(index, (Long) param);
} else {
throw new IllegalArgumentException("Type paramètre non géré : " + param);
}
index++;
}
try (ResultSet rs = ps.executeQuery()) {
System.out.println("Séjours filtrés :");
boolean found = false;
while (rs.next()) {
long ids = rs.getLong("ids");
long idcRes = rs.getLong("idc");
String nom = rs.getString("nom");
long idv = rs.getLong("idv");
String vVille = rs.getString("ville");
String activite= rs.getString("activite");
Date dDebut = rs.getDate("debut");
Date dFin = rs.getDate("fin");
System.out.println(
ids + " | " + idcRes + " (" + nom + ") | " +
idv + " | " + vVille + " | " + activite +
" | " + dDebut + " → " + dFin
);
found = true;
}
if (!found) {
System.out.println("(aucun résultat)");
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Ville (vide = ignorer) : ";
$ville = trim(fgets(STDIN));
$ville = $ville === '' ? null : $ville;
echo "Date début (YYYY-MM-DD, vide = ignorer) : ";
$debut = trim(fgets(STDIN));
$debut = $debut === '' ? null : $debut;
echo "Date fin (YYYY-MM-DD, vide = ignorer) : ";
$fin = trim(fgets(STDIN));
$fin = $fin === '' ? null : $fin;
echo "Id client (idc, vide = ignorer) : ";
$idc = trim(fgets(STDIN));
$idc = $idc === '' ? null : (int) $idc;
$baseSql = "
SELECT s.ids, c.idc, c.nom, v.idv, v.ville, v.activite,
s.debut, s.fin
FROM sejour s
JOIN client c ON s.idc = c.idc
JOIN village v ON s.idv = v.idv
WHERE 1 = 1
";
$conditions = [];
$params = [];
if ($ville !== null) {
$conditions[] = "AND v.ville = :ville";
$params[':ville'] = $ville;
}
if ($debut !== null) {
$conditions[] = "AND s.debut >= :debut";
$params[':debut'] = $debut;
}
if ($fin !== null) {
$conditions[] = "AND s.fin <= :fin";
$params[':fin'] = $fin;
}
if ($idc !== null) {
$conditions[] = "AND s.idc = :idc";
$params[':idc'] = $idc;
}
$sql = $baseSql . "\n" . implode("\n", $conditions) . "\nORDER BY s.debut DESC";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Séjours filtrés :\n";
if (!$rows) {
echo "(aucun résultat)\n";
} else {
foreach ($rows as $row) {
echo $row['ids'] . " | "
. $row['idc'] . " (" . $row['nom'] . ") | "
. $row['idv'] . " | "
. $row['ville'] . " | "
. $row['activite'] . " | "
. $row['debut'] . " → " . $row['fin'] . "\n";
}
}
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Exercice C9 – Purge des séjours (fonctionnalité 9)
Hypothèse côté BD : on a une procédure de purge du type :
CREATE OR REPLACE PROCEDURE purge_sejours(p_date_limite date, OUT p_nb_suppr integer)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM sejour
WHERE fin < p_date_limite;
GET DIAGNOSTICS p_nb_suppr = ROW_COUNT;
END;
$$;
Objectif C9 : depuis un programme, demander une date limite, appeler purge_sejours, afficher le nombre de séjours supprimés.
1. Python (psycopg 3)
import psycopg
from datetime import date
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
def purge_sejours(date_limite: date) -> int:
sql = "CALL purge_sejours(%s)"
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
# PostgreSQL renvoie la valeur OUT comme une ligne
cur.execute(sql, (date_limite,))
row = cur.fetchone()
if row is None:
# par définition, ce cas ne devrait pas arriver
print("La procédure n'a rien retourné.")
return 0
(nb_suppr,) = row
return nb_suppr # commit automatique
def main():
limite_str = input("Date limite (YYYY-MM-DD) : ").strip()
limite = date.fromisoformat(limite_str)
try:
nb = purge_sejours(limite)
print(f"Séjours supprimés avant {limite} : {nb}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
if __name__ == "__main__":
main()
Points clés :
CALL purge_sejours(%s)avec la date comme paramètre.fetchone()pour lire le paramètre OUT (nombre supprimé).
2. Java (JDBC)
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
public class PurgeSejours {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("Date limite (YYYY-MM-DD) : ");
String limiteStr = sc.nextLine().trim();
Date dateLimite = Date.valueOf(limiteStr);
purgeSejours(dateLimite);
}
private static void purgeSejours(Date dateLimite) {
// 1 paramètre IN (date_limite) + 1 OUT (p_nb_suppr)
String sql = "{ call purge_sejours(?, ?) }";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
CallableStatement cs = conn.prepareCall(sql)) {
System.out.println("Connexion OK");
// IN
cs.setDate(1, dateLimite);
// OUT
cs.registerOutParameter(2, java.sql.Types.INTEGER);
cs.execute();
int nbSuppr = cs.getInt(2);
System.out.println("Séjours supprimés avant " + dateLimite + " : " + nbSuppr);
} catch (SQLException e) {
System.err.println("Erreur lors de la purge des séjours : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points clés :
CallableStatementavec{ call purge_sejours(?, ?) }.- Paramètre IN : date ; paramètre OUT :
INTEGER. cs.getInt(2)récupère le nombre supprimé.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Date limite (YYYY-MM-DD) : ";
$limite = trim(fgets(STDIN));
$sql = "CALL purge_sejours(:limite)";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
$stmt = $pdo->prepare($sql);
$stmt->execute([':limite' => $limite]);
// PostgreSQL renvoie le OUT comme une ligne
$row = $stmt->fetch(PDO::FETCH_NUM);
if ($row === false) {
echo "La procédure n'a rien retourné.\n";
exit;
}
$nbSuppr = $row[0];
echo "Séjours supprimés avant $limite : $nbSuppr\n";
} catch (PDOException $e) {
echo "Erreur lors de la purge des séjours : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points clés :
- Appel avec
CALL purge_sejours(:limite)et paramètre nommé. fetch(PDO::FETCH_NUM)pour récupérer le OUT (première colonne).
Idée à fixer avec C9 :
- Opération de maintenance / admin encapsulée en procédure BD.
- L’application ne fait pas les
DELETEà la main ; elle appelle l’API SQL. - Le résultat métier (nombre de lignes supprimées) est renvoyé par un OUT et affiché côté appli.
Exercice C10 – Annulation administrative (fonctionnalité 10)
Objectif :
Depuis un programme, pour un ids donné :
- appeler la procédure d’annulation administrative (avec un motif),
- vérifier qu’une ligne correspondante existe dans
ArchiveSejour(ou table équivalente), - afficher ce qui a été archivé.
Hypothèse côté BD (à adapter à votre vrai schéma comme d’habitude) :
CREATE OR REPLACE PROCEDURE annuler_sejour(p_ids bigint, p_motif text)
LANGUAGE plpgsql
AS $$
BEGIN
-- 1. Copier le séjour dans ArchiveSejour
INSERT INTO archivesejour(ids, idc, idv, debut, fin, date_archivage, motif)
SELECT s.ids, s.idc, s.idv, s.debut, s.fin, now(), p_motif
FROM sejour s
WHERE s.ids = p_ids;
IF NOT FOUND THEN
RAISE EXCEPTION 'Séjour % introuvable', p_ids
USING ERRCODE = 'P0001';
END IF;
-- 2. Supprimer le séjour de la table principale
DELETE FROM sejour WHERE ids = p_ids;
END;
$$;
Requête de vérification côté appli :
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = ?
ORDER BY date_archivage DESC
LIMIT 1;
1. Python (psycopg 3)
import psycopg
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
def annuler_sejour(ids: int, motif: str) -> None:
try:
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
# 1. Appel de la procédure d'annulation
sql_call = "CALL annuler_sejour(%s, %s)"
cur.execute(sql_call, (ids, motif))
print(f"Annulation administrative du séjour {ids} effectuée.")
# 2. Vérification dans ArchiveSejour
with conn.cursor() as cur:
sql_check = """
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = %s
ORDER BY date_archivage DESC
LIMIT 1
"""
cur.execute(sql_check, (ids,))
row = cur.fetchone()
if row is None:
print("Attention : aucune trace dans ArchiveSejour pour ids =", ids)
return
(ids_a, idc, idv, debut, fin, date_archivage, motif_arch) = row
print("Ligne archivée :")
print(f" ids = {ids_a}")
print(f" idc = {idc}")
print(f" idv = {idv}")
print(f" période = {debut} → {fin}")
print(f" date archivage = {date_archivage}")
print(f" motif = {motif_arch}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
if sqlstate == "P0001":
print("Séjour introuvable (ids inexistant).")
def main():
ids_str = input("Id séjour (ids) à annuler : ").strip()
motif = input("Motif d'annulation : ").strip() or "Annulation administrative"
annuler_sejour(int(ids_str), motif)
if __name__ == "__main__":
main()
Points :
CALL annuler_sejour(%s, %s)avec paramètres séparés.- Deuxième requête pour vérifier la présence dans
archivesejour. - Gestion de l’erreur métier
P0001(séjour introuvable).
2. Java (JDBC)
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Scanner;
public class AnnulerSejour {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("Id séjour (ids) à annuler : ");
long ids = Long.parseLong(sc.nextLine().trim());
System.out.print("Motif d'annulation : ");
String motif = sc.nextLine().trim();
if (motif.isEmpty()) {
motif = "Annulation administrative";
}
annulerSejour(ids, motif);
}
private static void annulerSejour(long ids, String motif) {
String callSql = "{ call annuler_sejour(?, ?) }";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
System.out.println("Connexion OK");
// 1. Appel de la procédure
try (CallableStatement cs = conn.prepareCall(callSql)) {
cs.setLong(1, ids);
cs.setString(2, motif);
cs.execute();
System.out.println("Annulation administrative du séjour " + ids + " effectuée.");
} catch (SQLException e) {
System.err.println("Erreur lors de l'appel d'annuler_sejour");
System.err.println("Message : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
if ("P0001".equals(e.getSQLState())) {
System.err.println("Séjour introuvable (ids inexistant).");
}
return;
}
// 2. Vérification dans ArchiveSejour
String checkSql = """
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = ?
ORDER BY date_archivage DESC
LIMIT 1
""";
try (PreparedStatement ps = conn.prepareStatement(checkSql)) {
ps.setLong(1, ids);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
System.out.println("Attention : aucune trace dans ArchiveSejour pour ids = " + ids);
return;
}
long idsA = rs.getLong("ids");
long idc = rs.getLong("idc");
long idv = rs.getLong("idv");
Date debut = rs.getDate("debut");
Date fin = rs.getDate("fin");
Timestamp archive = rs.getTimestamp("date_archivage");
String motifArch = rs.getString("motif");
System.out.println("Ligne archivée :");
System.out.println(" ids = " + idsA);
System.out.println(" idc = " + idc);
System.out.println(" idv = " + idv);
System.out.println(" période = " + debut + " → " + fin);
System.out.println(" date archivage = " + archive);
System.out.println(" motif = " + motifArch);
}
}
} catch (SQLException e) {
System.err.println("Erreur globale SQL : " + e.getMessage());
System.err.println("SQLState : " + e.getSQLState());
}
}
}
Points :
CallableStatementpourannuler_sejour.SQLException.getSQLState()pour interpréterP0001.- Deuxième requête
SELECTsurarchivesejourpour confirmer l’archivage.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Id séjour (ids) à annuler : ";
$ids = (int) trim(fgets(STDIN));
echo "Motif d'annulation : ";
$motif = trim(fgets(STDIN));
if ($motif === '') {
$motif = 'Annulation administrative';
}
$sqlCall = "CALL annuler_sejour(:ids, :motif)";
$sqlCheck = "
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = :ids
ORDER BY date_archivage DESC
LIMIT 1
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
// 1. Appel de la procédure d'annulation
try {
$stmtCall = $pdo->prepare($sqlCall);
$stmtCall->execute([
':ids' => $ids,
':motif' => $motif,
]);
echo "Annulation administrative du séjour $ids effectuée.\n";
} catch (PDOException $e) {
echo "Erreur lors de l'appel d'annuler_sejour : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
$sqlstate = $e->errorInfo[0];
echo "SQLSTATE : $sqlstate\n";
if ($sqlstate === 'P0001') {
echo "Séjour introuvable (ids inexistant).\n";
}
}
exit;
}
// 2. Vérification dans ArchiveSejour
$stmtCheck = $pdo->prepare($sqlCheck);
$stmtCheck->execute([':ids' => $ids]);
$row = $stmtCheck->fetch(PDO::FETCH_ASSOC);
if (!$row) {
echo "Attention : aucune trace dans ArchiveSejour pour ids = $ids\n";
exit;
}
echo "Ligne archivée :\n";
echo " ids = {$row['ids']}\n";
echo " idc = {$row['idc']}\n";
echo " idv = {$row['idv']}\n";
echo " période = {$row['debut']} → {$row['fin']}\n";
echo " date archivage = {$row['date_archivage']}\n";
echo " motif = {$row['motif']}\n";
} catch (PDOException $e) {
echo "Erreur PDO globale : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Points :
CALL annuler_sejour(:ids, :motif)paramétré.- En cas d’
EXCEPTION 'Séjour introuvable'→PDOExceptionavec SQLSTATEP0001. - Vérification via un
SELECTsurarchivesejourpour la dernière ligne correspondant àids.
Exercice C11 – Archivage automatique (fonctionnalité 11)
Hypothèse côté BD :
- un trigger
AFTER DELETE ON sejourexiste et insère dansArchiveSejour(ids, idc, idv, debut, fin, date_archivage, motif).
1. Python (psycopg 3)
import psycopg
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
def supprimer_sejour_et_verifier_archivage(ids: int) -> None:
try:
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
# 1. Vérifier que le séjour existe encore
cur.execute(
"SELECT ids, idc, idv, debut, fin FROM sejour WHERE ids = %s",
(ids,),
)
row = cur.fetchone()
if row is None:
print(f"Aucun séjour avec ids = {ids} dans sejour.")
return
print("Séjour avant suppression :")
print(row)
# 2. Supprimer le séjour (déclenche le trigger d'archivage)
cur.execute("DELETE FROM sejour WHERE ids = %s", (ids,))
print(f"Lignes supprimées : {cur.rowcount}")
# 3. Vérifier l'archivage dans ArchiveSejour
with conn.cursor() as cur:
cur.execute(
"""
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = %s
ORDER BY date_archivage DESC
LIMIT 1
""",
(ids,),
)
arch = cur.fetchone()
if arch is None:
print("Attention : aucune ligne trouvée dans ArchiveSejour pour ids =", ids)
return
(ids_a, idc, idv, debut, fin, date_archivage, motif) = arch
print("Ligne archivée (via trigger) :")
print(f" ids = {ids_a}")
print(f" idc = {idc}")
print(f" idv = {idv}")
print(f" période = {debut} → {fin}")
print(f" date archivage = {date_archivage}")
print(f" motif = {motif}")
except psycopg.Error as e:
sqlstate = getattr(e, "sqlstate", None) or getattr(e, "pgcode", None)
print("Erreur PostgreSQL :", e)
print("SQLSTATE :", sqlstate)
def main():
ids_str = input("Id du séjour (ids) à supprimer : ").strip()
supprimer_sejour_et_verifier_archivage(int(ids_str))
if __name__ == "__main__":
main()
Points pédagogiques :
- On illustre que le programme ne fait qu’un
DELETE; c’est le trigger qui gère l’archivage. - Vérification explicite dans
ArchiveSejourpour fermer la boucle.
2. Java (JDBC)
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Scanner;
public class SupprimerSejourEtArchiver {
private static final String URL = "jdbc:postgresql://localhost:5432/bd2_demo";
private static final String USER = "bd2_user";
private static final String PASSWORD = "bd2_password";
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.print("Id du séjour (ids) à supprimer : ");
long ids = Long.parseLong(sc.nextLine().trim());
supprimerSejourEtVerifierArchivage(ids);
}
private static void supprimerSejourEtVerifierArchivage(long ids) {
String sqlSelectSejour = """
SELECT ids, idc, idv, debut, fin
FROM sejour
WHERE ids = ?
""";
String sqlDelete = "DELETE FROM sejour WHERE ids = ?";
String sqlCheckArchive = """
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = ?
ORDER BY date_archivage DESC
LIMIT 1
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
System.out.println("Connexion OK");
// 1. Vérifier l'existence du séjour
try (PreparedStatement psSel = conn.prepareStatement(sqlSelectSejour)) {
psSel.setLong(1, ids);
try (ResultSet rs = psSel.executeQuery()) {
if (!rs.next()) {
System.out.println("Aucun séjour avec ids = " + ids + " dans sejour.");
return;
}
long idsS = rs.getLong("ids");
long idc = rs.getLong("idc");
long idv = rs.getLong("idv");
Date debut = rs.getDate("debut");
Date fin = rs.getDate("fin");
System.out.println("Séjour avant suppression :");
System.out.println(" ids = " + idsS);
System.out.println(" idc = " + idc);
System.out.println(" idv = " + idv);
System.out.println(" période = " + debut + " → " + fin);
}
}
// 2. DELETE (déclenche le trigger)
try (PreparedStatement psDel = conn.prepareStatement(sqlDelete)) {
psDel.setLong(1, ids);
int rows = psDel.executeUpdate();
System.out.println("DELETE : lignes affectées = " + rows);
if (rows == 0) {
System.out.println("Rien n'a été supprimé (séjour déjà supprimé ?).");
return;
}
}
// 3. Vérifier dans ArchiveSejour
try (PreparedStatement psArch = conn.prepareStatement(sqlCheckArchive)) {
psArch.setLong(1, ids);
try (ResultSet rsArch = psArch.executeQuery()) {
if (!rsArch.next()) {
System.out.println("Attention : aucune entrée dans ArchiveSejour pour ids = " + ids);
return;
}
long idsA = rsArch.getLong("ids");
long idcA = rsArch.getLong("idc");
long idvA = rsArch.getLong("idv");
Date debutA = rsArch.getDate("debut");
Date finA = rsArch.getDate("fin");
Timestamp archTime = rsArch.getTimestamp("date_archivage");
String motifArch = rsArch.getString("motif");
System.out.println("Ligne archivée (via trigger) :");
System.out.println(" ids = " + idsA);
System.out.println(" idc = " + idcA);
System.out.println(" idv = " + idvA);
System.out.println(" période = " + debutA + " → " + finA);
System.out.println(" date archivage = " + archTime);
System.out.println(" motif = " + motifArch);
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLSTATE : " + e.getSQLState());
}
}
}
Idée forte : côté Java, l’archivage n’apparaît nulle part → test clair du rôle du trigger.
3. PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
echo "Id du séjour (ids) à supprimer : ";
$ids = (int) trim(fgets(STDIN));
$sqlSelect = "
SELECT ids, idc, idv, debut, fin
FROM sejour
WHERE ids = :ids
";
$sqlDelete = "DELETE FROM sejour WHERE ids = :ids";
$sqlCheckArchive = "
SELECT ids, idc, idv, debut, fin, date_archivage, motif
FROM archivesejour
WHERE ids = :ids
ORDER BY date_archivage DESC
LIMIT 1
";
try {
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connexion OK\n";
// 1. Vérifier que le séjour existe
$stmtSel = $pdo->prepare($sqlSelect);
$stmtSel->execute([':ids' => $ids]);
$sejour = $stmtSel->fetch(PDO::FETCH_ASSOC);
if (!$sejour) {
echo "Aucun séjour avec ids = $ids dans sejour.\n";
exit;
}
echo "Séjour avant suppression :\n";
print_r($sejour);
// 2. DELETE (déclenche le trigger d'archivage)
$stmtDel = $pdo->prepare($sqlDelete);
$stmtDel->execute([':ids' => $ids]);
$rows = $stmtDel->rowCount();
echo "DELETE : lignes affectées = $rows\n";
if ($rows === 0) {
echo "Rien n'a été supprimé (séjour déjà supprimé ?).\n";
exit;
}
// 3. Vérifier dans ArchiveSejour
$stmtArch = $pdo->prepare($sqlCheckArchive);
$stmtArch->execute([':ids' => $ids]);
$archive = $stmtArch->fetch(PDO::FETCH_ASSOC);
if (!$archive) {
echo "Attention : aucune entrée dans ArchiveSejour pour ids = $ids\n";
exit;
}
echo "Ligne archivée (via trigger) :\n";
print_r($archive);
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
Ce que l’exercice fait vraiment travailler :
Différence annulation via procédure (C10) vs suppression brute (C11) :
- C10 : logique d’annulation dans une procédure explicite.
- C11 : suppression simple, logique d’archivage complètement prise en charge par un trigger.
Même pattern applicatif dans les trois langages :
SELECTde contrôle →DELETEparamétré →SELECTdansArchiveSejour.
D. Tests d’erreurs – SQLSTATE et rollback
Exercice D1 – Violation UNIQUE
1. Variante 1 — SQL brut dans le langage
1.1 Java (JDBC)
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
String sqlInsert = """
INSERT INTO client (nom, age)
VALUES (?, ?)
RETURNING idc
""";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement ps = conn.prepareStatement(sqlInsert)) {
ps.setString(1, "Alice");
ps.setInt(2, 30);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
long idc = rs.getLong("idc");
System.out.println("Client créé, idc = " + idc);
}
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLSTATE : " + e.getSQLState());
}
Points clés :
PreparedStatement+ paramètres → protection contre injection.RETURNING idcrécupéré directement.
1.2 PHP (PDO)
<?php
$host = 'localhost';
$port = 5432;
$dbname = 'bd2_demo';
$user = 'bd2_user';
$password = 'bd2_password';
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname;";
$sql = "
INSERT INTO client (nom, age)
VALUES (:nom, :age)
RETURNING idc
";
try {
$pdo = new PDO($dsn, $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare($sql);
$stmt->execute([
':nom' => 'Alice',
':age' => 30,
]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$idc = $row['idc'] ?? null;
echo "Client créé, idc = $idc\n";
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
if ($e->errorInfo) {
echo "SQLSTATE : " . $e->errorInfo[0] . "\n";
}
}
1.3 Python (psycopg 3)
import psycopg
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
sql = """
INSERT INTO client (nom, age)
VALUES (%s, %s)
RETURNING idc
"""
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
cur.execute(sql, ("Alice", 30))
(idc,) = cur.fetchone()
print("Client créé, idc =", idc)
Bilan variante 1 :
- Contrôle fin du SQL.
- Simple à comprendre si le schéma est stable.
- – Répétition du SQL dans tout le code.
- – Logique métier dispersée entre BD et application.
2. Variante 2 — Appel de procédure stockée api_creer_client
On suppose que côté BD on a :
CREATE OR REPLACE PROCEDURE api_creer_client(
p_nom text,
p_age int,
INOUT p_idc bigint
) LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO client(nom, age)
VALUES (p_nom, p_age)
RETURNING idc INTO p_idc;
END;
$$;
On encapsule donc la logique métier dans PL/pgSQL.
2.1 Java (CallableStatement)
String url = "jdbc:postgresql://localhost:5432/bd2_demo";
String user = "bd2_user";
String password = "bd2_password";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
String call = "{ call api_creer_client(?, ?, ?) }";
try (CallableStatement cs = conn.prepareCall(call)) {
cs.setString(1, "Bob");
cs.setInt(2, 25);
// p_idc INOUT
cs.setLong(3, 0L);
cs.registerOutParameter(3, java.sql.Types.BIGINT);
cs.execute();
long idc = cs.getLong(3);
System.out.println("Client créé via API, idc = " + idc);
}
} catch (SQLException e) {
System.err.println("Erreur SQL : " + e.getMessage());
System.err.println("SQLSTATE : " + e.getSQLState());
}
2.2 PHP (PDO, CALL)
<?php
$dsn = "pgsql:host=localhost;port=5432;dbname=bd2_demo;";
$user = 'bd2_user';
$password = 'bd2_password';
try {
$pdo = new PDO($dsn, $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
// Pour simplifier : wrapper via fonction SQL
// CREATE FUNCTION api_creer_client_f(p_nom text, p_age int)
// RETURNS bigint AS $$ ... $$ LANGUAGE plpgsql;
//
// Ici on appelle directement la procédure et on relit ensuite.
$nom = 'Bob';
$age = 25;
// Appel procédure (sans OUT direct standard côté PDO) :
$stmt = $pdo->prepare("CALL api_creer_client(:nom, :age, :idc)");
$idc = 0;
$stmt->bindParam(':nom', $nom);
$stmt->bindParam(':age', $age, PDO::PARAM_INT);
$stmt->bindParam(':idc', $idc, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 32);
$stmt->execute();
echo "Client créé via API, idc = $idc\n";
} catch (PDOException $e) {
echo "Erreur PDO : " . $e->getMessage() . "\n";
}
Remarque : selon les versions de PDO/PostgreSQL, la gestion des paramètres OUT/INOUT est plus ou moins ergonomique. Une alternative plus robuste en pratique est de passer par une fonction RETURNS bigint et un SELECT simple.
2.3 Python (psycopg 3, CALL avec INOUT)
PostgreSQL ne renvoie pas « naturellement » les OUT/INOUT en resultset pour CALL. Une solution pédagogique plus simple : faire une fonction api_creer_client_f qui retourne directement idc :
CREATE OR REPLACE FUNCTION api_creer_client_f(p_nom text, p_age int)
RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE
v_idc bigint;
BEGIN
INSERT INTO client(nom, age)
VALUES (p_nom, p_age)
RETURNING idc INTO v_idc;
RETURN v_idc;
END;
$$;
Côté Python :
import psycopg
CONNINFO = {
"host": "localhost",
"port": 5432,
"dbname": "bd2_demo",
"user": "bd2_user",
"password": "bd2_password",
}
with psycopg.connect(**CONNINFO) as conn:
with conn.cursor() as cur:
cur.execute("SELECT api_creer_client_f(%s, %s)", ("Bob", 25))
(idc,) = cur.fetchone()
print("Client créé via API, idc =", idc)
Conceptuellement, c’est la même idée : l’application appelle une API SQL (fonction/procédure) plutôt que manipuler directement les tables.
Bilan variante 2 :
- Logique métier centralisée dans PL/pgSQL (cohérence, réutilisation).
- Meilleure maîtrise des contraintes, triggers, erreurs, RLS côté BD.
- – Besoin de maintenir deux couches (SQL/PLpgSQL + langage).
- – Couplage fort à PostgreSQL (procédures spécifiques).
3. Variante 3 — Utilisation d’un ORM
Objectif : montrer l’idée, pas de code complet de config.
3.1 Java — Hibernate / JPA
Entité :
import jakarta.persistence.*;
@Entity
@Table(name = "client")
public class Client {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long idc;
private String nom;
private Integer age;
// getters / setters
}
Création :
// Avec EntityManager (JPA)
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Client c = new Client();
c.setNom("Alice");
c.setAge(30);
em.persist(c); // INSERT généré automatiquement
em.getTransaction().commit();
System.out.println("Client créé, idc = " + c.getIdc());
em.close();
3.2 Python — SQLAlchemy ORM
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, Session
Base = declarative_base()
class Client(Base):
__tablename__ = "client"
idc = Column(Integer, primary_key=True, autoincrement=True)
nom = Column(String)
age = Column(Integer)
engine = create_engine("postgresql+psycopg://bd2_user:bd2_password@localhost:5432/bd2_demo", echo=False)
Base.metadata.create_all(engine) # si nécessaire
with Session(engine) as session:
c = Client(nom="Alice", age=30)
session.add(c)
session.commit()
print("Client créé, idc =", c.idc)
3.3 PHP — Doctrine ORM (pseudo-code simplifié)
Entité (annotations / attributs, version simplifiée) :
<?php
/**
* @Entity
* @Table(name="client")
*/
class Client
{
/**
* @Id
* @Column(type="integer")
* @GeneratedValue
*/
private $idc;
/** @Column(type="string") */
private $nom;
/** @Column(type="integer") */
private $age;
// getters / setters...
}
Création :
<?php
// $entityManager déjà configuré
$client = new Client();
$client->setNom('Alice');
$client->setAge(30);
$entityManager->persist($client);
$entityManager->flush();
echo "Client créé, idc = " . $client->getIdc() . PHP_EOL;
Bilan variante 3 :
- Très peu de SQL explicite → productivité sur CRUD.
- Modèle objet central ; facile à manipuler dans du code métier OO.
- Gestion automatique des relations, transactions, etc. (selon l’ORM).
- – Moins de contrôle sur le SQL généré (perf, plans).
- – ORM a sa propre complexité (mappings, cache, lifecycle).
- – Risque de dé-synchronisation entre modèle objet et schéma BD.
4. Comparaison synthétique (réponse à la question 2)
SQL brut :
Avantages :
- contrôle maximal sur la requête et les performances ;
- aucune dépendance externe (ORM) ;
- bon pour requêtes complexes / rapports.
Limites :
- beaucoup de duplication de code (CRUD répétitif) ;
- logique métier dispersée entre code et BD.
API SQL (fonctions / procédures, vues + RLS) :
Avantages :
- centralisation de la logique métier côté BD ;
- meilleure cohérence/atomicité (transactions, contraintes, triggers) ;
- l’application appelle une « API SQL » stable → indépendance vis-à-vis du schéma interne.
Limites :
- nécessite une bonne maîtrise PL/pgSQL ;
- code plus difficile à tester avec les outils classiques du langage ;
- fort couplage au SGBD.
ORM :
Avantages :
- boost de productivité sur les opérations simples ;
- code applicatif plus lisible (objets plutôt que
ResultSet/ tableaux associatifs) ; - intègre souvent migrations, validations, etc.
Limites :
- mauvais choix pour requêtes analytiques lourdes / spécifiques ;
- performance parfois difficile à diagnostiquer ;
- courbe d’apprentissage (ORM + SQL + BD).
Cas d’usage typiques :
Appli classique de gestion (CRUD majoritairement simple, schéma relativement stable) :
- ORM + quelques requêtes SQL brutes pour les cas spéciaux.
Cœur métier très “data-centric”, contraintes fortes, multi-applications sur la même BD :
- API SQL + triggers + RLS, les applis restent fines.
Reporting, analytique, batchs lourds :
- SQL brut optimisé, éventuellement encapsulé dans des vues/fonctions dédiées.
Exercice D2 – Violation CHECK
1. Tableau de synthèse par scénario
Scénario 1 – Inscription d’un client (CRUD simple)
- Besoins : INSERT simple dans
client, avec 2–3 colonnes, éventuellement un contrôle “âge ≥ 18”. - Approches :
| Approche | Pertinence | Justification synthétique |
|---|---|---|
| SQL brut | ✔✔ | Très simple, peu de code, contrôle direct sur l’INSERT. |
API SQL (procédure inscrire_client) | ✔✔ | Centralise la règle métier (âge mini, logs…). |
| ORM | ✔ | OK si l’application est déjà largement en ORM. |
Correction attendue Deux bons candidats :
- SQL brut si on veut garder le contrôle côté appli,
- API SQL si la règle métier doit être partagée par plusieurs applis. L’ORM est acceptable mais pas indispensable pour une opération aussi simple.
Scénario 2 – Consultation des séjours d’un client (liste, filtres simples)
Ex. fonctionnalité “Mes séjours” : SELECT avec jointures client / sejour / village, filtres sur dates.
| Approche | Pertinence | Justification |
|---|---|---|
| SQL brut | ✔✔ | Requête claire, peu de colonnes, facile à optimiser. |
API SQL (fonction sejours_client(p_idc)) | ✔✔ | API uniforme, réutilisable par d’autres frontends. |
| ORM | ✔✔ | Cas typique pour ORM : client.sejours, pagination, etc. |
Correction Les trois approches sont défendables :
- ORM très naturel pour du “browse/filter” dans une appli web,
- SQL brut ou fonction stockée si on veut des vues de synthèse plus complexes ou des perfs contrôlées.
Scénario 3 – Rapport analytique lourd (bilan par ville, agrégats, filtres avancés)
Ex. “bilan annuel” avec GROUP BY, HAVING, agrégats, éventuellement CTE, fenêtres.
| Approche | Pertinence | Justification |
|---|---|---|
| SQL brut | ✔✔✔ | Requêtes complexes, besoin de contrôler le plan d’exécution. |
| API SQL (vue matérialisée, fonction) | ✔✔✔ | Centralise la complexité, permet de matérialiser / indexer. |
| ORM | ✖ / (faible) | ORM génère souvent du SQL sous-optimal, difficile à tweaker finement. |
Correction Attendu : éviter l’ORM pour ce type de requête, privilégier :
- une vue matérialisée ou une fonction SQL/PLpgSQL, appelée depuis le code,
- ou du SQL brut soigneusement écrit.
Scénario 4 – Opération de maintenance / batch (purge des séjours, archivage, recalcul de colonnes)
Ex. fonction 9–11 : purge, archivage, recomptage des séjours.
| Approche | Pertinence | Justification |
|---|---|---|
| SQL brut | ✔ | Possible, mais la logique devient vite verbeuse côté appli. |
API SQL (procédure purge_sejours, triggers) | ✔✔✔ | Idéal : opérations massives et atomiques côté serveur. |
| ORM | ✖ | Inadapté : trop de round-trips, mauvais sur des DELETE massifs. |
Correction Réponse attendue : API SQL (procédures, triggers, éventuellement jobs planifiés) clairement meilleure :
- moins de trafic réseau,
- meilleure atomicité,
- logique centralisée.
Scénario 5 – Formulaire web classique avec beaucoup de CRUD “plats”
Ex. interface employé pour gérer clients/villages, changer un prix, corriger un nom, etc.
| Approche | Pertinence | Justification |
|---|---|---|
| SQL brut | ✔ | Viable, mais répétitif (beaucoup de petits INSERT/UPDATE). |
| API SQL | ✔ | Possible mais risque de multiplier les petites procédures. |
| ORM | ✔✔✔ | Cas d’usage typique : mapping entités ⇔ formulaires CRUD. |
- Correction Attendu : ORM (ou micro ORM / query builder) recommandé ici, avec éventuellement quelques procédures pour les cas “sensibles” (sécurité, intégrité forte).
Scénario 6 – Sécurité / confidentialité forte (RLS, vues, encapsulation)
Ex. rôle secretaire qui ne doit voir que certains clients, ou employe qui ne peut insérer que via une procédure SECURITY DEFINER.
| Approche | Pertinence | Justification |
|---|---|---|
| SQL brut | ✔ | Fonctionne, mais toute la sécurité doit être ré-implémentée côté appli. |
| API SQL + vues + RLS | ✔✔✔ | Le SGBD applique les règles quelles que soient les applis clientes. |
| ORM | ✔ / (complément) | Utile comme façade, mais ne remplace pas les mécanismes BD. |
Correction Réponse raisonnable :
- la sécurité doit être dans la BD (vues, RLS, procédures
SECURITY DEFINER) - les ORM / SQL bruts ne sont qu’un moyen d’appeler cette API “sécurisée”.
- la sécurité doit être dans la BD (vues, RLS, procédures
2. Grille de conclusion à retenir
ORM
- Productivité forte pour CRUD standard, formulaires, relations simples.
- – Mauvais pour : analytique, lots, requêtes SQL très spécifiques.
SQL brut dans le langage
- Contrôle maximal sur les requêtes et les perfs.
- Indispensable pour toute requête non triviale ou spécifique à PostgreSQL.
- – Beaucoup de code répétitif, logique métier éclatée.
API SQL (vues, fonctions, procédures, triggers, RLS)
- Centralise la logique métier et la sécurité “près des données”.
- Permet d’exposer une API stable au-dessus d’un schéma interne qui peut évoluer.
- – Demande une vraie conception côté BD, outils de tests adaptés, dépendance forte à PostgreSQL.
Exercice D3 – Violation FK
1. Le problème : N+1 requêtes
1.1 Java – Hibernate / JPA (code “naïf”)
// Pseudo-code typique
List<Client> clients = em.createQuery("SELECT c FROM Client c", Client.class)
.getResultList();
for (Client c : clients) {
System.out.println("Client : " + c.getNom());
for (Sejour s : c.getSejours()) {
// Accès paresseux : déclenche une requête par client
System.out.println(" Séjour " + s.getIds() + " du " + s.getDebut() + " au " + s.getFin());
}
}
Problème :
- La première requête charge tous les clients : 1 requête.
- Pour chaque client, l’accès à
c.getSejours()(lazy) déclenche une requête supplémentaire. - Si on a N clients → N+1 requêtes contre la BD.
- Sur un jeu de données réel (ex. 1000 clients) → 1001 requêtes, latence importante, surcharge réseau.
Même schéma en Python / SQLAlchemy :
1.2 Python – SQLAlchemy (code “naïf”)
from sqlalchemy.orm import Session
from models import Client # Client.sejours = relationship(..., lazy="select")
with Session(engine) as session:
clients = session.query(Client).all() # 1 requête
for c in clients:
print("Client :", c.nom)
for s in c.sejours: # déclenche 1 requête SELECT sejour WHERE idc = c.idc
print(" Séjour", s.ids, "du", s.debut, "au", s.fin)
Même effet : N+1 SELECT.
2. Correction côté ORM : chargement groupé
2.1 Java – Hibernate : JOIN FETCH (ou @EntityGraph)
Version corrigée avec JOIN FETCH :
// Charger clients + séjours en une seule requête (ou très peu)
List<Client> clients = em.createQuery(
"SELECT DISTINCT c FROM Client c " +
"LEFT JOIN FETCH c.sejours s", Client.class)
.getResultList();
for (Client c : clients) {
System.out.println("Client : " + c.getNom());
for (Sejour s : c.getSejours()) {
System.out.println(" Séjour " + s.getIds() + " du " + s.getDebut() + " au " + s.getFin());
}
}
Effet :
- Une requête SQL avec JOIN
client–sejour. - Hibernate reconstruit les objets et liaisons en mémoire.
- Plus de N+1, on passe à 1 requête (ou 2–3 avec d’autres relations).
Autre option : @BatchSize ou EntityGraph, mais JOIN FETCH suffit pédagogiquement.
2.2 Python – SQLAlchemy : selectinload ou joinedload
Version avec selectinload (deux requêtes au total) :
from sqlalchemy.orm import Session, selectinload
from models import Client
with Session(engine) as session:
clients = (
session.query(Client)
.options(selectinload(Client.sejours))
.all()
)
for c in clients:
print("Client :", c.nom)
for s in c.sejours: # plus de requêtes supplémentaires
print(" Séjour", s.ids, "du", s.debut, "au", s.fin)
Effet :
- 1 requête pour les clients.
- 1 requête pour tous les séjours concernés (avec
IN (liste des idc)). - Total ≈ 2 requêtes, quel que soit N.
Avec joinedload :
from sqlalchemy.orm import joinedload
clients = (
session.query(Client)
.options(joinedload(Client.sejours))
.all()
)
→ une seule requête avec JOIN.
2.3 PHP – Doctrine ORM : fetch join
Pseudo-code :
// Client avec relation OneToMany "sejours"
$dql = "
SELECT c, s
FROM App\Entity\Client c
LEFT JOIN FETCH c.sejours s
";
$query = $entityManager->createQuery($dql);
$clients = $query->getResult();
foreach ($clients as $c) {
echo "Client : " . $c->getNom() . PHP_EOL;
foreach ($c->getSejours() as $s) {
echo " Séjour " . $s->getIds()
. " du " . $s->getDebut()->format('Y-m-d')
. " au " . $s->getFin()->format('Y-m-d') . PHP_EOL;
}
}
Doctrine génère alors un seul SELECT avec JOIN.
3. Équivalent en SQL brut
Requête SQL qui capture l’intention :
SELECT c.idc,
c.nom,
s.ids,
s.debut,
s.fin
FROM client c
LEFT JOIN sejour s ON s.idc = c.idc
ORDER BY c.idc, s.debut DESC;
C’est exactement ce que les ORM génèrent avec JOIN FETCH / joinedload.
Côté code Java (JDBC) :
String sql = """
SELECT c.idc, c.nom,
s.ids, s.debut, s.fin
FROM client c
LEFT JOIN sejour s ON s.idc = c.idc
ORDER BY c.idc, s.debut DESC
""";
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
Long lastIdc = null;
while (rs.next()) {
long idc = rs.getLong("idc");
String nom = rs.getString("nom");
if (!Long.valueOf(idc).equals(lastIdc)) {
System.out.println("Client : " + idc + " - " + nom);
lastIdc = idc;
}
Long ids = (Long) rs.getObject("ids"); // peut être NULL si aucun séjour
if (ids != null) {
Date debut = rs.getDate("debut");
Date fin = rs.getDate("fin");
System.out.println(" Séjour " + ids + " du " + debut + " au " + fin);
}
}
}
Même logique possible en Python/PHP.
4. Variante API SQL : vue ou fonction
On peut encapsuler ce pattern dans la BD :
4.1 Vue
CREATE VIEW v_clients_sejours AS
SELECT c.idc,
c.nom,
s.ids,
s.debut,
s.fin
FROM client c
LEFT JOIN sejour s ON s.idc = c.idc;
Puis, depuis n’importe quelle appli :
SELECT * FROM v_clients_sejours ORDER BY idc, debut DESC;
Ou fonction paramétrable :
4.2 Fonction
CREATE OR REPLACE FUNCTION sejours_tous_clients()
RETURNS TABLE (
idc bigint,
nom text,
ids bigint,
debut date,
fin date
) AS $$
BEGIN
RETURN QUERY
SELECT c.idc, c.nom, s.ids, s.debut, s.fin
FROM client c
LEFT JOIN sejour s ON s.idc = c.idc
ORDER BY c.idc, s.debut DESC;
END;
$$ LANGUAGE plpgsql;
Côté appli :
cur.execute("SELECT * FROM sejours_tous_clients()")
5. A retenir
Nom du problème : – “N+1 requêtes” (ou “N+1 SELECT”) – pattern classique d’inefficacité avec les associations
OneToManyen ORM.Symptômes :
- beaucoup de requêtes toutes semblables dans les logs,
- performances qui se dégradent linéairement avec le nombre d’objets.
Stratégies de correction :
- côté ORM :
JOIN FETCH,joinedload,selectinload, batch fetching ; - côté SQL brut : écrire une requête bien formée avec JOIN ;
- côté BD : encapsuler dans une vue ou une fonction (API SQL).
- côté ORM :
Lien avec D1/D2 :
- ORM = utile, mais pas magique ;
- parfois il faut reprendre la main : soit en réglant l’ORM, soit en revenant au SQL explicite / API SQL.
Exercice D4 – Violation exclusion (chevauchement)
1. Intérêt de garder la procédure reserver au lieu de tout faire en ORM
Points attendus :
Logique métier “atomique” côté BD
- choix du village, vérification de la capacité, contrôle de chevauchement, respect de contraintes, etc., peuvent être codés une fois en PL/pgSQL.
- on garanti que toute appli (Java, PHP, Python, script ad hoc…) qui appelle
CALL reserver(...)bénéficie de la même logique.
Intégrité forte / contraintes avancées
- certaines règles sont plus naturelles à exprimer côté BD (constraints, EXCLUDE, verrous, RAISE EXCEPTION).
- la procédure contrôle la transaction au plus près des données.
Multi-clients, multi-langages
- on n’a pas à dupliquer la logique de réservation dans chaque backend ou outil qui consomme la BD.
- l’ORM devient juste une façade au-dessus d’une API SQL plus riche.
Sécurité / encapsulation
- on peut donner au rôle applicatif seulement le droit d’appeler
reserver, pas de manipuler directementsejour. - possibilité d’utiliser
SECURITY DEFINER+ RLS pour faire respecter des politiques d’accès.
- on peut donner au rôle applicatif seulement le droit d’appeler
En résumé : pour une opération métier critique (réservation), il est souvent préférable de garder un noyau transactionnel côté BD, et d’utiliser l’ORM uniquement pour la gestion “quotidienne” des entités.
2. Appeler reserver depuis l’ORM, dans la même transaction
2.1 Java + Hibernate (JPA) + procédure reserver
Hypothèse :
entités ORM
Client,Sejour,Villagedéjà mappées ;idsest la PK deSejour;on veut :
- appeler
CALL reserver(?, ?, ?, ?)depuis une transaction JPA, - récupérer
idsretourné, - recharger l’entité
Sejourvia l’EntityManager.
- appeler
Code :
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Types;
public class ServiceReservation {
private final EntityManagerFactory emf;
public ServiceReservation(EntityManagerFactory emf) {
this.emf = emf;
}
public Sejour reserver(long idc, String ville, Date debut, Date fin) {
EntityManager em = emf.createEntityManager();
Sejour sejour;
try {
em.getTransaction().begin();
// 1. Récupérer la connexion JDBC utilisée par Hibernate
Connection conn = em.unwrap(Connection.class);
long ids;
long idv;
String activite;
// 2. Appel de la procédure reserver dans la même transaction
try (CallableStatement cs =
conn.prepareCall("{ call reserver(?, ?, ?, ?, ?, ?, ?) }")) {
// IN
cs.setLong(1, idc);
cs.setString(2, ville);
cs.setDate(3, debut);
cs.setDate(4, fin);
// OUT
cs.registerOutParameter(5, Types.BIGINT); // p_ids
cs.registerOutParameter(6, Types.BIGINT); // p_idv
cs.registerOutParameter(7, Types.VARCHAR); // p_activite
cs.execute();
ids = cs.getLong(5);
idv = cs.getLong(6);
activite = cs.getString(7);
}
// 3. Recharger l'entité Sejour via l’ORM
sejour = em.find(Sejour.class, ids);
// (optionnel) vérifier que le village corresponde
Village village = em.find(Village.class, idv);
// 4. Commit transaction JPA
em.getTransaction().commit();
return sejour;
} catch (Exception e) {
if (em.getTransaction().isActive()) {
em.getTransaction().rollback();
}
throw e;
} finally {
em.close();
}
}
}
Points importants :
em.unwrap(Connection.class)permet d’obtenir laConnectionHibernate/JPA dans la même transaction.- La procédure fait le boulot BD (insertion dans
sejour, validation des règles). - Ensuite on re-synchronise le monde ORM avec
em.find(Sejour.class, ids).
2.2 Python + SQLAlchemy ORM
Même idée :
- entité ORM
Sejour; - on veut appeler la procédure
reserverdans la transaction ORM, puis relireSejour.
from datetime import date
from sqlalchemy import text
from sqlalchemy.orm import Session
from models import Sejour, engine
def reserver(session: Session, idc: int, ville: str, debut: date, fin: date) -> Sejour:
# 1. Appel de la procédure reserver dans la transaction SQLAlchemy
# On suppose que la proc renvoie une ligne (ids, idv, activite)
stmt = text("""
CALL reserver(:idc, :ville, :debut, :fin)
""")
# Certains drivers ne renvoient pas les OUT directement.
# Variante pédagogique : wrapper par une fonction qui RETURN RECORD :
#
# SELECT * FROM reserver_f(:idc, :ville, :debut, :fin);
#
# mais on garde ici CALL + fetchone() si votre driver le permet.
result = session.execute(
stmt,
{"idc": idc, "ville": ville, "debut": debut, "fin": fin},
)
row = result.fetchone()
if row is None:
raise RuntimeError("La procédure reserver n'a rien retourné.")
ids, idv, activite = row # ordre des OUT
# 2. Recharger l'entité Sejour via l'ORM
sejour = session.get(Sejour, ids)
# 3. Retourner l'entité ; le commit se fait à l'extérieur
return sejour
# Usage
with Session(engine) as session:
try:
sejour = reserver(session, idc=1, ville="Nice",
debut=date(2025, 7, 1),
fin=date(2025, 7, 10))
session.commit()
print("Séjour réservé, ids =", sejour.ids)
except Exception:
session.rollback()
raise
Si votre driver ne gère pas proprement les OUT via CALL, variante robuste :
- faire une fonction
reserver_f(...) RETURNS RECORD, - l’appeler par un
SELECT * FROM reserver_f(:idc, ...), ce que SQLAlchemy gère très bien.
3. Risques classiques quand on mélange ORM et SQL/PLpgSQL
Cache ORM / état du contexte vs. modifications SQL brutes
l’ORM garde un cache de 1er niveau (EntityManager / Session).
si on fait un
UPDATE/INSERT/DELETEvia unCallableStatement/text()en dehors de ce qu’il connaît, le cache peut être désynchronisé tant qu’on ne recharge pas les entités.il faut :
- soit reloader (
find/get/refresh), - soit vider le contexte (
clear), selon le cas.
- soit reloader (
Transactions doublées / non alignées
- erreur classique : créer une transaction JDBC “à la main” (auto-commit off,
conn.commit) en plus de la transaction ORM. - résultat : état incohérent, exceptions, verrouillages bizarres.
- règle : une seule source de vérité pour la transaction : celle de l’ORM, en réutilisant sa connexion.
- erreur classique : créer une transaction JDBC “à la main” (auto-commit off,
Erreur silencieuse sur contraintes / triggers
en SQL brut, on peut oublier la gestion fine des SQLSTATE / messages.
côté ORM, tout remonte en exceptions génériques.
besoin de convention :
RAISE EXCEPTION ... USING ERRCODE = 'P0001'pour erreurs métier,- mapping côté langage (catch / interprétation du SQLSTATE).
Perfs : ORM + procédures mal utilisées
- faire N appels à une procédure dans une boucle au lieu d’une seule procédure qui traite un lot.
- laisser l’ORM charger des graphes d’objets inutiles avant ou après l’appel d’API SQL.
Schémas divergents
la procédure peut évoluer (ajout de colonnes, changement de comportement) sans mise à jour des mappings ORM → bugs subtils.
nécessité de synchroniser :
- migrations SQL,
- définition PL/pgSQL,
- entités ORM.
Exercice D5 – Appel de procédure qui lève EXCEPTION
1. Architecture proposée (vue globale)
On veut une BD centrale PostgreSQL exposant une API SQL stable, utilisée par :
- un backend web “front-office” (Java/PHP/Python + éventuellement ORM),
- un backend web “back-office” (idem),
- des scripts batch (Python/psycopg, éventuellement sans ORM).
Proposition synthétique :
- Cœur métier critique / sensible (réservation, annulation, purge, archivage) → API SQL / PLpgSQL (fonctions + procédures + triggers).
- CRUD “simples” des entités (client, village) → ORM dans les applis web, avec un peu de SQL brut pour les cas spéciaux.
- Analytique / reporting / batchs → SQL brut + vues (matérialisées) + fonctions set-returning (pas d’ORM).
On peut résumer par un tableau :
| Type de fonctionnalité | Front-office (web) | Back-office (web) | Batch / analytique | Couche BD centrale |
|---|---|---|---|---|
| Inscription client | ORM ou SQL brut simple | idem si besoin | — | contraintes + fonction éventuelle |
| Réservation / annulation / purge | APIs PL/pgSQL appelées depuis code | idem | idem (scripts d’admin) | procédures, triggers, RLS |
| Consultation “Mes séjours”, liste client/village | ORM (requêtes join + pagination) | ORM + SQL brut pour filtres complexes | SQL brut | vues / fonctions de lecture |
| Gestion villages / prix | ORM CRUD + quelques UPDATE SQL | ORM principalement | — | contraintes, triggers |
| Rapports / stats | REST vers backend (qui fait SQL/API) | idem | SQL brut, vues matérialisées | vues/fonctions analytiques |
2. Justification par bloc fonctionnel
2.1 Inscription client
Front / Back office :
- opération CRUD très simple (INSERT dans
client), - peut se faire en ORM (D1, variante 3) ou en SQL brut paramétré.
- opération CRUD très simple (INSERT dans
Batch :
- peu pertinent (rarement de l’inscription en batch).
BD centrale :
- contraintes (
CHECK age >= 18), uniqueness (UNIQUE(email)), éventuellement une petite fonctioninscrire_clientsi la règle se complexifie.
- contraintes (
Pourquoi :
- ne nécessite pas de procédure complexe ; ORM/SQL brut suffisent.
2.2 Réservation / Annulation / Purge
Front-office :
- le backend ne doit pas recoder la logique (disponibilités, conflit de dates, capacité…),
- il appelle une procédure
reserver(...)(D4) + procédures d’annulation / purge.
Back-office :
- annulations administratives, purges manuelles -> mêmes procédures.
Batch :
- purge régulière, nettoyage des données → scripts Python qui appellent
CALL purge_sejours(...), etc. (C9, C10).
- purge régulière, nettoyage des données → scripts Python qui appellent
BD :
cœur métier centralisé :
reserver,annuler_sejour,purge_sejours,- triggers d’archivage,
- éventuelles policies RLS.
Pourquoi :
- opérations critiques, fortement contraintes, potentiellement utilisées par plusieurs applis → API SQL obligatoire.
- ORM seul serait fragile et redonderait la logique dans plusieurs services.
2.3 Consultation “Mes séjours” / listes avec filtres
Front-office :
- typiquement : affichage paginé des séjours d’un client,
- ORM très adapté :
client.sejours, filtres sur dates, statut, etc. - attention à éviter N+1 (D3 →
JOIN FETCH,selectinload).
Back-office :
- listes de séjours d’un client, d’une ville, etc.
- ORM + SQL brut pour les filtres plus complexes (agrégats, fenêtres).
Batch :
- si simplement lire toutes les lignes, SQL brut (SELECT avec JOIN).
BD :
- vues (
v_clients_sejours) et fonctions (sejours_client(idc)) éventuellement pour exposer des vues pré-packagées.
- vues (
Pourquoi :
- lecture non destructive, peu critique → ORM OK,
- pour des listes lourdes / agrégées, revenir au SQL brut / API SQL.
2.4 Gestion villages / prix (back-office)
Back-office :
- création / modification de villages, prix, capacités → cas typique de CRUD administratifs.
- ORM recommandé : formulaires + validations simples.
Front-office :
- n’y touche pas (lecture uniquement via APIs).
Batch :
- éventuellement petits scripts SQL pour changer massivement les prix → SQL brut / procédures ponctuelles.
BD :
- contraintes (CHECK, FK),
- triggers éventuels (log des changements de prix).
Pourquoi :
- c’est du CRUD classique → ORM + contraintes BD suffisent.
2.5 Analytique / reporting
Batch :
bilans mensuels/annuels, agrégats multi-tables, fenêtrage, CTE récursives possibles.
ORM déconseillé :
- difficile de générer un SQL optimal,
- debugging de perfs compliqué.
Préférer :
- SQL brut (scripts Python),
- vues matérialisées + fonctions SQL.
Front/Back :
- consomment ces rapports via une API REST exposée par un backend qui utilise SQL brut / vues.
BD :
- vues (
v_stats_ville_mois), vues matérialisées, fonctionsstats_par_ville(année).
- vues (
Pourquoi :
- aligné avec D2/D3 : l’ORM n’est pas un bon outil pour de l’analytique.
3. Éléments centralisés dans la BD (communs aux trois contextes)
Ce que la correction attend qu’ils listent explicitement :
Contraintes d’intégrité
- PK, FK, UNIQUE, CHECK, EXCLUDE (pour interdire des chevauchements),
- ces contraintes sont indispensables quel que soit le client (front, back, batch).
Triggers métier
- archivage automatique (C11), mise à jour de compteurs, logs.
- offrent des garanties indépendamment du code applicatif.
API SQL (vues, fonctions, procédures)
reserver,annuler_sejour,purge_sejours,disponibilite,sejours_client, vues synthétiques.- utilisées par les trois types de clients.
Politique de sécurité / RLS
- RLS par rôle (
client,employe,admin) pour restreindre les lignes visibles, - vues d’exposition restreintes (
v_sejours_client, etc.).
- RLS par rôle (
Schéma stable / contrat
- l’API SQL joue le rôle de contrat : les applis peuvent évoluer, l’ORM peut changer, mais le contrat BD reste stable à travers le temps.
E. Intégration applicative finale – pipeline complet
Exercice E1 – Scénario “client complet”
Propositions de réponses acceptables :
Indépendance des niveaux Capacité d’un SGBD à faire évoluer un niveau (physique ou logique : index, partitionnement, structure interne) sans modifier les interfaces exposées au niveau supérieur (vues, programmes clients).
Confidentialité Propriété qui garantit que chaque utilisateur ou rôle n’accède qu’aux données et opérations auxquelles il est explicitement autorisé, en fonction de ses droits et de son contexte.
Encapsulation Principe qui consiste à interdire l’accès direct aux tables métier et à imposer le passage par une API SQL contrôlée (vues, fonctions, procédures, RLS) pour lire ou modifier les données.
Exercice E2 – Scénario “employé complet”
1. Ajouter un index B-tree sur sejour(idc, debut)
- Niveau impacté : physique.
- Impact sur les niveaux supérieurs : aucun.
- Justification : amélioration purement interne de l’accès, même schéma logique, même interfaces.
2. Renommer la colonne prix en tarif
- Niveau impacté : logique (structure du schéma conceptuel).
- Impact sur les niveaux supérieurs : oui → vues, ORM, SQL applicatif doivent être mis à jour.
- Justification : le contrat logique change.
3. Ajouter une vue v_sejours_client(idc)
- Niveau impacté : externe (nouvelle interface exposée).
- Impact sur les niveaux supérieurs : optionnel, uniquement si l’application l’utilise.
- Justification : on crée une nouvelle “vision” des données sans changer le schéma logique.
4. Ajouter une règle RLS pour filtrer sejour
- Niveau impacté : externe (politique de visibilité).
- Impact sur les niveaux supérieurs : normalement aucun côté SQL ; le filtrage est silencieux.
- Remarque : peut entraîner des surprises applicatives si le code s’attendait à “tout voir”.
5. Changer l’algorithme d’accès disque (heap → zheap)
- Niveau impacté : physique.
- Impact sur les niveaux supérieurs : aucun.
- Justification : optimisation interne, transparence totale pour vues / schéma / ORM.
6. Modifier la procédure reserver(...) en ajoutant un paramètre
- Niveau impacté : externe (contrat API SQL), et éventuellement logique si la procédure encode la logique métier.
- Impact sur les niveaux supérieurs : oui si les appels de l’application utilisent cette signature.
- Justification : rupture de contrat ABI entre BD et application.
7. Partitionner la table sejour par année
- Niveau impacté : physique, parfois présenté comme un raffinement du niveau logique.
- Impact sur les niveaux supérieurs : aucun si le partitionnement est transparent (même table, même colonnes).
- Justification : PostgreSQL maintient l’illusion d’une seule table.
8. Modifier l’ordre des colonnes dans une table
Niveau impacté : logique.
Impact sur les niveaux supérieurs :
- aucun si les accès se font par noms de colonnes,
- oui si l’application utilise des
SELECT *mappés positionnellement (mauvaise pratique).
Justification : l’ordre fait partie de la structure logique, même si peu exploité.
9. Ajouter un trigger d’archivage AFTER DELETE
- Niveau impacté : logique (comportement associé aux tables).
- Impact sur les niveaux supérieurs : normalement aucun si la signature des tables ne change pas.
- Remarque : peut rendre des DELETE plus coûteux, mais c’est transparent pour l’application.
10. Modifier la structure d’une vue consommée par une application
- Niveau impacté : externe.
- Impact sur les niveaux supérieurs : oui, puisque la vue sert de contrat d’accès.
- Justification : changer une vue revient à changer une API publique.
Synthèse E2 à retenir
Physique : index, partitionnement, structures internes. → aucun impact sur le code applicatif.
Logique : tables, colonnes, contraintes, triggers. → impact direct si on modifie colonnes / signatures / contraintes.
Externe : vues, procédures, fonctions, RLS, API SQL. → impact systématique si une appli consomme ces interfaces.
Ce que l’exercice devait ancrer : la capacité à identifier où se situe un changement et qui peut continuer à fonctionner sans modification.
Exercice E3 – Programme de test automatique
1. Créer un client
- Pas critique.
- Opération CRUD simple.
- Possible directement via INSERT.
- Encapsulation utile seulement si règles supplémentaires (âge minimum, unicité forte, logs).
- Décision : optionnel.
2. Créer / supprimer un village
- Opération d’administration.
- Pas de logique métier lourde, mais cohérence à maintenir (FK, capacités).
- Peut être fait en SQL direct depuis un rôle admin.
- Décision : pas d’API obligatoire, sauf si plusieurs frontends doivent harmoniser la logique.
3. Réserver un séjour
- Contrainte de chevauchement, disponibilité, capacité, cohérence des dates.
- Plusieurs inserts potentiels + validations.
- Transaction métier atomique.
- Ne doit jamais être codée côté application.
- Décision : procédure PL/pgSQL obligatoire (
reserver(...)).
4. Annuler un séjour
- Dépend de conditions métier (délais, frais, statut).
- Effets en cascade possibles (archivage, remboursement).
- Doit être centralisé dans la BD, identique pour toutes les applis.
- Décision : procédure PL/pgSQL (
annuler_sejour(...)).
5. Purger les séjours expirés
- Opération potentiellement massive.
- Doit être atomique, rapide, et protégée.
- Ne doit pas être faite en ORM.
- Décision : procédure PL/pgSQL (
purge_sejours()), appelée par scripts batch.
6. Consulter “Mes séjours”
- Lecture simple.
- Pas de logique métier critique.
- ORM acceptable côté application.
- Possible aussi via une vue si exposition uniforme nécessaire.
- Décision : lecture simple → pas d’API obligatoire.
7. Rapports analytiques
- Agrégats, GROUP BY, CTE.
- ORM inadapté.
- Les requêtes sont statiques dans le temps.
- Décision : vues ou vues matérialisées, appelées en SQL brut.
8. Archivage automatique lors d’un DELETE
- Ce n’est pas une API.
- Comportement interne.
- Doit être transparent pour les applications.
- Décision : trigger obligatoire (
AFTER DELETE).
9. Sécurité par client (ne voir que ses propres séjours)
- L’application ne doit pas gérer la sécurité.
- Le filtrage doit être centralisé.
- Décision : RLS obligatoire, éventuellement vues sécurisées.
10. Mise à jour du prix d’un séjour
- CRUD simple.
- Peut être fait via ORM.
- Si règles métier (historisation, validation), alors API SQL.
- Décision : direct sauf logique supplémentaire.
Synthèse E3
Doit être encapsulé (procédure / vue / RLS)
- Réservation
- Annulation
- Purge
- Archivage (trigger)
- Filtrage sécurité (RLS)
- Requêtes analytiques (vues)
Peut rester en SQL direct / ORM
- CRUD clients
- CRUD villages
- Consultations simples
- Mise à jour basique des attributs
→ API SQL dès qu’il y a logique métier, sécurité, transactions complexes, performances, ou besoin de cohérence inter-applications.
Exercice E4 – Mini-ORM artisanal
1. Architecture de rôles
Idée : séparer les rôles “techniques” (NOLOGIN) des comptes réels.
-- Rôles techniques (groupes)
CREATE ROLE role_client NOLOGIN;
CREATE ROLE role_employe NOLOGIN;
CREATE ROLE role_admin NOLOGIN;
-- Comptes applicatifs (utilisés par l’appli)
CREATE ROLE app_client LOGIN PASSWORD '...';
CREATE ROLE app_employe LOGIN PASSWORD '...';
CREATE ROLE app_admin LOGIN PASSWORD '...';
-- Affectation des groupes
GRANT role_client TO app_client;
GRANT role_employe TO app_employe;
GRANT role_admin TO app_admin;
Règle :
- on ne met des droits que sur les rôles techniques (
role_*), - les comptes (
app_*) héritent de ces droits.
2. GRANT principaux
Hypothèse tables : client, sejour, village, API SQL reserver, annuler_sejour, etc.
2.1 Droits de base sur les tables
-- Par défaut, personne n’a de droits
REVOKE ALL ON client, sejour, village FROM PUBLIC;
-- role_client : pas d’accès direct aux tables
REVOKE ALL ON client, sejour, village FROM role_client;
-- role_employe : CRUD sur les tables métier
GRANT SELECT, INSERT, UPDATE, DELETE ON client TO role_employe;
GRANT SELECT, INSERT, UPDATE, DELETE ON sejour TO role_employe;
GRANT SELECT, INSERT, UPDATE, DELETE ON village TO role_employe;
-- role_admin : tout sur tout
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role_admin;
Pour client / sejour côté “client” :
role_clientn’a pas de droits directs surclient/sejour→ obligé de passer par API SQL / vues.
3. RLS sur sejour pour les clients
On active RLS sur sejour et on filtre par client courant.
3.1 Activer RLS
ALTER TABLE sejour ENABLE ROW LEVEL SECURITY;
3.2 Politique pour les clients
On suppose que l’appli fait, à l’ouverture de session :
SET app.current_idc = '42'; -- idc du client connecté
Politique :
CREATE POLICY sejour_client_policy ON sejour
FOR SELECT, UPDATE, DELETE
TO role_client
USING (idc = current_setting('app.current_idc')::bigint)
WITH CHECK (idc = current_setting('app.current_idc')::bigint);
Interprétation :
USING: lignes visibles sisejour.idc = idc courant.WITH CHECK: un client ne peut insérer / modifier qu’un séjour qui lui appartient.
Pour éviter tout oubli :
ALTER TABLE sejour FORCE ROW LEVEL SECURITY;
Ainsi, même si on fait GRANT SELECT à role_client, RLS continue de filtrer.
4. Encapsulation via API SQL (vues / fonctions / procédures)
4.1 Vues pour la consultation client
Vue :
CREATE VIEW v_mes_sejours AS
SELECT s.ids, s.idc, v.ville, v.activite, s.debut, s.fin
FROM sejour s
JOIN village v ON v.idv = s.idv;
Droits :
GRANT SELECT ON v_mes_sejours TO role_client;
Avec RLS sur sejour, un client qui fait :
SELECT * FROM v_mes_sejours;
ne verra que ses propres séjours, sans logique côté application.
4.2 Procédures de réservation / annulation
On encapsule les opérations critiques :
GRANT EXECUTE ON PROCEDURE reserver(bigint, text, date, date)
TO role_client, role_employe;
GRANT EXECUTE ON PROCEDURE annuler_sejour(bigint, text)
TO role_employe; -- par exemple, pas aux clients
Et on ne donne aucun droit INSERT/UPDATE/DELETE sur sejour à role_client :
REVOKE INSERT, UPDATE, DELETE ON sejour FROM role_client;
Donc :
- un client ne peut pas manipuler
sejouren direct, - il doit obligatoirement passer par
CALL reserver(...), - et RLS continue de filtrer toutes les lectures.
5. Ce qui doit rester caché aux applis
Tables internes (
sejour,clientcomplet,archivesejour) non exposées aux clients :- pas de GRANT direct,
- seulement des vues restreintes (
v_mes_sejours,v_client_public).
Fonctionnalités d’admin (purge, archivage, modifications massives) :
- exposées seulement à
role_employe/role_admin, - via procédures (
purge_sejours,annuler_sejour_admin, etc.).
- exposées seulement à
Colonnes sensibles (
email,phone, données de carte) :- retirées des vues publiques,
- visibles uniquement par des vues/admins spécifiques.
Pierre-Henri Paris
Associate Professor in Artificial Intelligence
My research interests include Knowlegde Graphs, Information Extraction, and NLP.