Environnement Windows · pgAdmin · Docker Desktop
PostgreSQL est né à l’université de Californie à Berkeley en 1989, sous la direction de Michael Stonebraker. Le projet s’appelait alors POSTGRES, en référence à Ingres, une base de données développée dans le même laboratoire. Parrainé initialement par la DARPA et la NSF, il est devenu en trente ans l’un des SGBD open source les plus utilisés et les plus respectés.
En tant que développeur Java ou PHP, vous rencontrerez PostgreSQL dans la quasi-totalité des architectures modernes : Spring Boot avec Spring Data JPA, Laravel, Symfony, Hibernate… La maîtrise de ce SGBD est donc une compétence directement opérationnelle.
Nous allons utiliser Docker Desktop pour Windows, ce qui présente plusieurs avantages pour un développeur :
Deux conteneurs seront lancés :
5432
http://localhost:5050
docker --version docker compose version
Créez un dossier (par exemple C:\Dev\wouaf-wouaf) et placez-y le fichier suivant :
C:\Dev\wouaf-wouaf
version: '3.8' services: db: container_name: pg_wouaf image: postgres:16-alpine restart: always environment: POSTGRES_USER: devuser POSTGRES_PASSWORD: devpassword POSTGRES_DB: dst_db ports: - "5432:5432" volumes: - pg_data:/var/lib/postgresql/data pgadmin: container_name: pgadmin_wouaf image: dpage/pgadmin4 restart: always environment: PGADMIN_DEFAULT_EMAIL: admin@wouaf.fr PGADMIN_DEFAULT_PASSWORD: admin ports: - "5050:80" volumes: pg_data:
Sécurité : En production, les mots de passe ne doivent jamais être écrits en dur dans ce fichier. On utilise des variables d’environnement ou des secrets Docker/Kubernetes. Ici c’est acceptable pour un environnement de développement local.
Ouvrez PowerShell dans votre dossier, puis :
docker compose up -d
Vérifiez que les deux conteneurs tournent :
docker ps
Vous devez voir pg_wouaf et pgadmin_wouaf dans la liste avec le statut Up.
pg_wouaf
pgadmin_wouaf
Up
admin@wouaf.fr
admin
Wouaf-Wouaf
Host name/address : pg_wouaf (nom du conteneur Docker) Port : 5432 Username : devuser Password : devpassword
Le « Host name » est le nom du conteneur Docker (pg_wouaf), et non localhost, car pgAdmin tourne lui-même dans un conteneur et communique avec PostgreSQL via le réseau interne Docker.
localhost
Si vous préférez travailler en CLI (utile pour les scripts) :
# Entrer dans le conteneur PostgreSQL docker exec -it pg_wouaf bash # Se connecter à la base dst_db psql -h localhost -U devuser dst_db # Lister les bases \l # Quitter psql \q
Quatre bases de données (chenil_v1 à chenil_v4) vous sont fournies au format .sql. Pour les restaurer, procédez ainsi.
chenil_v1
chenil_v4
.sql
Créez d’abord les bases vides depuis psql (à l’intérieur du conteneur) :
CREATE DATABASE chenil_v1; CREATE DATABASE chenil_v2; CREATE DATABASE chenil_v3; CREATE DATABASE chenil_v4;
Puis, depuis PowerShell (hors du conteneur), dans le dossier contenant les fichiers .sql :
docker exec -i pg_wouaf psql -U devuser -d chenil_v1 < .\chenil_v1.sql docker exec -i pg_wouaf psql -U devuser -d chenil_v2 < .\chenil_v2.sql docker exec -i pg_wouaf psql -U devuser -d chenil_v3 < .\chenil_v3.sql docker exec -i pg_wouaf psql -U devuser -d chenil_v4 < .\chenil_v4.sql
Sous Windows, utilisez \ (antislash) pour les chemins dans PowerShell, et < pour la redirection d’entrée.
\
<
Vous êtes développeur dans une ESN. Votre première mission vous amène chez Nathalie, propriétaire de l’élevage canin Wouaf-Wouaf, situé au 45 rue du Bouquet, 69100 Lyon.
Nathalie gère actuellement ses 21 chiens sur un registre papier. Elle a constaté une baisse de son chiffre d’affaires liée à des problèmes de gestion. Elle souhaite informatiser son registre sous forme de base de données PostgreSQL.
Les clients disposent d’une période d’essai de 2 semaines pendant laquelle ils peuvent rendre le chien et être remboursés.
Voici un extrait du registre de Nathalie (table plate, toutes les données dans un seul tableau) :
À première vue ce registre semble fonctionnel. Mais au fil des situations que nous allons analyser, de nombreux problèmes vont émerger. Ce projet va nous permettre de comprendre, de façon concrète, pourquoi la conception d’une base de données nécessite une méthode.
Avant d’apprendre la méthode MERISE, nous allons vivre une série de situations concrètes qui mettent en évidence les problèmes d’une base de données mal conçue. Chaque situation introduit un concept clé.
Le registre de Nathalie contient la colonne AgeChien avec des valeurs comme « 1 Ans, 4 mois » ou « 6 Ans, 5 mois ».
AgeChien
⚠️ Règle fondamentale : ne jamais stocker un champ calculable.
L’âge est calculable à partir de la date de naissance et de la date du jour. Si on stocke l’âge, il sera faux dès le lendemain. La bonne solution est de stocker DateNaissanceChien et de calculer l’âge dans les requêtes.
DateNaissanceChien
En Java/Hibernate, cela correspond à un @Transient sur un champ calculé.
@Transient
Nathalie cherche un Poodle mâle disponible. La requête suivante ne retourne rien :
SELECT NomChien FROM Chiens WHERE RaceChien = 'Poodle' AND SexeChien = 'Mâle' AND NomClient IS NULL; -- 0 rows
Pourtant Whisky le Poodle existe bien ! En cherchant différemment :
SELECT NomChien, SexeChien FROM Chiens WHERE RaceChien = 'Poodle' AND SexeChien LIKE 'm%' AND NomClient IS NULL; -- Whisky | mâlle
Le sexe a été saisi « mâlle » au lieu de « Mâle ». Nathalie vient de rater une vente.
Solutions :
SexeChien
TRUE
FALSE
RaceChien
TatoueChien
La base chenil_v2 intègre ces corrections. Voici comment y accéder :
chenil_v2
\c chenil_v2 -- dans psql
💡 En Java, on évite ce type d’erreur en utilisant des enum. En SQL, on utilise des types booléens, des foreign keys vers des tables de référence, ou des contraintes CHECK.
enum
CHECK
La table contient deux colonnes liées au tatouage : TatoueChien (booléen) et NumTatouageChien (numéro). Il y a des incohérences :
NumTatouageChien
-- Chiens indiqués non tatoués mais avec un numéro de tatouage SELECT NomChien, TatoueChien, NumTatouageChien FROM Chiens WHERE TatoueChien = FALSE AND NumTatouageChien IS NOT NULL; -- Bibi | f | 61006 -- Lucy | f | 56352 -- Chien indiqué tatoué mais sans numéro SELECT NomChien FROM Chiens WHERE TatoueChien = TRUE AND NumTatouageChien IS NULL; -- Whisky
Redondance d’information = risque d’incohérence de données.
Solution : supprimer la colonne TatoueChien. Un chien est tatoué si et seulement si NumTatouageChien n’est pas NULL.
Nathalie adopte un chien à un client, qui le ramène au bout d’une semaine. Elle efface les données de l’ancien client pour saisir le nouveau propriétaire. La police se présente ensuite pour connaître le propriétaire à une date précise…
Absence de traçabilité : toute écriture écrase l’historique précédent.
Ce problème sera partiellement résolu par la séparation des tables (MLD). Une vraie traçabilité nécessiterait une table de transactions séparée.
Nathalie a deux clients José Hernandez (père et fils), vivant à des adresses différentes. Il est impossible de les distinguer sans un identifiant unique.
Solution : ajouter une clé primaire auto-incrémentée à chaque entité (IdClient, IdChien).
IdClient
IdChien
En JPA/Hibernate : @Id @GeneratedValue(strategy = GenerationType.IDENTITY). En Doctrine (PHP/Symfony) : #[ORM\Id] #[ORM\GeneratedValue].
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
#[ORM\Id] #[ORM\GeneratedValue]
L’adresse est stockée dans un seul champ : « 52 Rue des Pins, 35010, Rennes, France ». Nathalie veut faire des campagnes par ville. Elle ne peut pas filtrer sur la ville car l’adresse est un bloc monolithique.
2ème forme normale : atomiser les attributs composites.
Solution : décomposer en AdresseClient, CpClient, VilleClient, PaysClient.
AdresseClient
CpClient
VilleClient
PaysClient
Si un client achète 10 chiens, ses nom, prénom, adresse, téléphone et email apparaissent 10 fois dans la table. Si son adresse change, il faut modifier 10 lignes.
Redondance = risque d’incohérence et coût de maintenance.
Solution : séparer les données en deux tables (Chiens et Clients) reliées par une clé étrangère.
Chiens
Clients
MERISE est une méthode française d’analyse et de conception des systèmes d’information, née dans les années 1970. Elle repose sur la séparation des données et des traitements, et définit trois niveaux de modélisation.
Avant tout modèle, on recueille les règles de gestion auprès du client. Pour Wouaf-Wouaf :
Le MCD représente les entités (rectangles) et leurs associations (losanges). Chaque entité est caractérisée par des attributs.
IdClient#
IdChien#
IdRace#
Les cardinalités minimales expriment une contrainte métier. Le 0 côté chien signifie qu’un chien peut être en stock sans avoir été vendu. Le 1 côté client signifie qu’un client n’existe que s’il a acheté un chien.
0
1
Pour passer du MCD au MLD, on n’utilise que les cardinalités maximales.
La clé primaire de l’entité côté N descend dans l’entité côté 1 et devient clé étrangère.
Application : la relation Client (N) — Chien (1) implique que IdClient descend dans la table Chiens.
Chiens(IdChien#, NomChien, ..., IdClient*)
L’association se transforme en table intermédiaire. Les deux clés primaires y descendent.
Exemple : si un chien pouvait avoir plusieurs propriétaires simultanément, on créerait une table Adoptions(IdClient*, IdChien*, DateAdoption, PrixVente).
Adoptions(IdClient*, IdChien*, DateAdoption, PrixVente)
Résultat de l’application des règles de Merise à notre MCD :
Races (IdRace#, NomRace) Clients (IdClient#, NomClient, PrenomClient, AdresseClient, CpClient, VilleClient, PaysClient, TelClient, EmailClient) Chiens (IdChien#, NomChien, SexeChien, NumTatouageChien, DateNaissanceChien, CouleurPelageChien, PrixVente, DateVente, IdRace* → Races, IdClient* → Clients)
Notation : # = clé primaire, * = clé étrangère. C’est ce schéma que vous aurez sous les yeux pour écrire vos requêtes SQL.
#
*
Le MPD est l’adaptation du MLD au SGBD cible (ici PostgreSQL). On précise les types SQL, les contraintes NOT NULL, DEFAULT, etc.
SQL (Structured Query Language) est le langage standard pour interagir avec une base de données relationnelle. Il se compose de plusieurs sous-langages :
Toute transaction PostgreSQL respecte les propriétés ACID :
En Java avec Spring, @Transactional garantit le respect des propriétés ACID. En PHP/Symfony avec Doctrine, beginTransaction() / commit() / rollback() jouent le même rôle.
@Transactional
beginTransaction()
commit()
rollback()
Connectez-vous à la base chenil_v4 dans pgAdmin (clic droit sur la base → Query Tool) ou dans psql (\c chenil_v4).
\c chenil_v4
CREATE TABLE Races ( IdRace SERIAL PRIMARY KEY, NomRace VARCHAR(45) NOT NULL );
CREATE TABLE Clients ( IdClient SERIAL PRIMARY KEY, NomClient VARCHAR(45) NOT NULL, PrenomClient VARCHAR(45) NOT NULL, AdresseClient VARCHAR(100) NOT NULL, CpClient VARCHAR(10) NOT NULL, VilleClient VARCHAR(60) NOT NULL, PaysClient VARCHAR(45) NOT NULL, TelClient VARCHAR(20) DEFAULT NULL, EmailClient VARCHAR(100) DEFAULT NULL );
Utilisez VARCHAR plutôt que CHAR pour les chaînes variables (économie d’espace). Utilisez VARCHAR et non INT pour les codes postaux : 2A004 (Ajaccio, Corse) contient une lettre !
VARCHAR
CHAR
INT
2A004
CREATE TABLE Chiens ( IdChien SERIAL PRIMARY KEY, NomChien VARCHAR(45) NOT NULL, SexeChien BOOLEAN DEFAULT NULL, NumTatouageChien VARCHAR(45) DEFAULT NULL, DateNaissanceChien DATE NOT NULL, CouleurPelageChien VARCHAR(45) DEFAULT NULL, PrixVente DECIMAL(10,2) DEFAULT NULL, DateVente DATE DEFAULT NULL, IdRace INTEGER REFERENCES Races(IdRace), IdClient INTEGER REFERENCES Clients(IdClient) );
L’ordre de création est important : Races et Clients doivent exister avant Chiens, car Chiens référence leurs clés primaires.
Races
Dans psql :
\d+ Chiens
Dans pgAdmin : naviguez dans l’arbre Schemas → Tables → Chiens → Columns.
-- Ajouter une colonne ALTER TABLE Chiens ADD COLUMN Remarques TEXT; -- Supprimer une colonne ALTER TABLE Chiens DROP COLUMN Remarques; -- Modifier le type d'une colonne ALTER TABLE Chiens ALTER COLUMN NomChien TYPE VARCHAR(60);
Syntaxe générale :
INSERT INTO nom_table (col1, col2, col3) VALUES (val1, val2, val3);
Exemple — Nathalie accueille une nouvelle chienne Ciboulette :
-- 1. Vérifier / insérer la race INSERT INTO Races (NomRace) VALUES ('Chihuahua'); SELECT IdRace FROM Races WHERE NomRace = 'Chihuahua'; -- résultat : 3 -- 2. Insérer le chien INSERT INTO Chiens (NomChien, SexeChien, DateNaissanceChien, IdRace) VALUES ('Ciboulette', FALSE, '2024-02-01', 3);
SERIAL gère l’auto-incrémentation. Ne précisez jamais IdChien dans un INSERT.
SERIAL
INSERT
La clé étrangère IdRace doit correspondre à un IdRace existant dans Races. Sinon PostgreSQL lève une erreur de contrainte d’intégrité référentielle.
IdRace
-- Corriger le sexe et le nom d'un chien UPDATE Chiens SET NomChien = 'Paf', SexeChien = TRUE, NumTatouageChien = '256819' WHERE IdChien = 22;
JAMAIS d’UPDATE sans clause WHERE. Sans WHERE, toutes les lignes de la table seront modifiées !
-- Supprimer un chien spécifique DELETE FROM Chiens WHERE IdChien = 22;
JAMAIS de DELETE sans clause WHERE. Sans WHERE, toute la table sera vidée !
En développement, exécutez d’abord le SELECT correspondant avant un UPDATE ou DELETE, pour vérifier quelles lignes seront affectées.
SELECT
UPDATE
DELETE
Nous utilisons ici la base chenil_v4 qui contient les tables Races, Clients et Chiens correctement structurées avec des données.
-- Toutes les colonnes SELECT * FROM Chiens; -- Colonnes spécifiques SELECT NomChien, SexeChien, DateNaissanceChien FROM Chiens; -- Valeurs distinctes SELECT DISTINCT NomChien FROM Chiens; -- 14 noms distincts
=
WHERE VilleClient = 'Lyon'
<>
!=
WHERE CpClient != '75020'
> < >= <=
WHERE PrixVente <= 500
BETWEEN
WHERE PrixVente BETWEEN 400 AND 800
IN
WHERE VilleClient IN ('Lyon','Paris')
IS NULL
WHERE IdClient IS NULL
IS NOT NULL
WHERE IdClient IS NOT NULL
LIKE
WHERE NomChien LIKE 'B%'
ILIKE
WHERE NomChien ILIKE 'b%'
-- Chiens disponibles (non vendus) SELECT NomChien, DateNaissanceChien FROM Chiens WHERE IdClient IS NULL; -- Chiens dont le prix est entre 400 et 800 euros SELECT NomChien, PrixVente FROM Chiens WHERE PrixVente BETWEEN 400 AND 800; -- Clients de Lyon, Donzy ou Nice SELECT PrenomClient, NomClient, VilleClient FROM Clients WHERE VilleClient IN ('Lyon', 'Donzy', 'Nice'); >Attention à la priorité AND/OR (AND = multiplication, OR = addition) -- Incorrect : retourne 4 lignes au lieu de 2 SELECT * FROM Clients WHERE VilleClient = 'Lyon' OR VilleClient = 'Donzy' OR VilleClient = 'Nice' AND NomClient != 'Hernandez'; -- Correct : les parenthèses imposent l'ordre d'évaluation SELECT * FROM Clients WHERE (VilleClient = 'Lyon' OR VilleClient = 'Donzy' OR VilleClient = 'Nice') AND NomClient != 'Hernandez';
'M%'
NomChien LIKE 'M%'
'%y'
NomChien LIKE '%y'
'%oul%'
NomChien LIKE '%oul%'
'_____'
NomChien LIKE '_____'
'R_t%'
RaceChien LIKE 'R_t%'
Préférez ILIKE à LIKE en PostgreSQL pour des recherches insensibles à la casse. Très utile pour les moteurs de recherche.
-- Tri alphabétique SELECT NomClient, PrenomClient FROM Clients ORDER BY NomClient ASC; -- Les 5 chiens les plus chers SELECT NomChien, PrixVente FROM Chiens WHERE PrixVente IS NOT NULL ORDER BY PrixVente DESC LIMIT 5; -- Pagination : page 2 (lignes 6 à 10) SELECT IdChien, NomChien FROM Chiens OFFSET 5 LIMIT 5;
LIMIT et OFFSET sont utilisés pour la pagination dans les applications web et les API REST.
LIMIT
OFFSET
-- Chiens nés en 2020 SELECT NomChien FROM Chiens WHERE DATE_PART('year', DateNaissanceChien) = 2020; -- Ventes de 2023 SELECT NomChien, PrixVente FROM Chiens WHERE DATE_PART('year', DateVente) = 2023;
Sans jointure, interroger plusieurs tables produit un produit cartésien. Avec 21 chiens et 12 clients, on obtient 252 lignes inutilisables :
-- Produit cartésien (à NE PAS faire sans jointure) SELECT NomChien, NomClient FROM Chiens, Clients; -- 252 lignes !
Les jointures permettent de combiner les tables en respectant les relations définies dans le MLD.
Retourne uniquement les lignes qui ont une correspondance dans les deux tables.
-- Chiens vendus avec le nom de leur propriétaire SELECT ch.NomChien, cl.PrenomClient, cl.NomClient FROM Chiens ch INNER JOIN Clients cl ON ch.IdClient = cl.IdClient; -- 16 lignes (les 5 chiens non vendus sont exclus) -- Avec la race en plus (double jointure) SELECT ch.NomChien, r.NomRace, cl.PrenomClient, cl.NomClient FROM Chiens ch INNER JOIN Clients cl ON ch.IdClient = cl.IdClient INNER JOIN Races r ON ch.IdRace = r.IdRace;
Toujours consulter le MLD pour savoir quelles colonnes relier dans les JOIN. La jointure se fait toujours sur clé étrangère = clé primaire.
clé étrangère = clé primaire
Retourne toutes les lignes de la table de gauche, même sans correspondance à droite (NULL alors affiché).
-- Tous les chiens, vendus ou non, avec leur propriétaire s'il existe SELECT ch.NomChien, cl.PrenomClient, cl.NomClient FROM Chiens ch LEFT JOIN Clients cl ON ch.IdClient = cl.IdClient; -- 21 lignes (les 5 chiens non vendus ont NULL pour le client)
-- Tous les clients, avec leur(s) chien(s) si achat SELECT cl.PrenomClient, cl.NomClient, ch.NomChien FROM Chiens ch RIGHT JOIN Clients cl ON ch.IdClient = cl.IdClient; -- Le prospect Vincent Drier apparaît avec NULL pour NomChien
💡 Un LEFT JOIN peut toujours être réécrit en RIGHT JOIN en inversant l’ordre des tables. On préfère généralement LEFT JOIN car la lecture va de gauche à droite.
LEFT JOIN
RIGHT JOIN
-- Alias de colonnes (étiquetage pour l'affichage) SELECT PrenomClient AS Prénom, NomClient AS Nom FROM Clients; -- Alias de tables (simplification des requêtes) SELECT cl.PrenomClient, cl.NomClient, ch.NomChien, r.NomRace FROM Clients cl LEFT JOIN Chiens ch ON ch.IdClient = cl.IdClient LEFT JOIN Races r ON ch.IdRace = r.IdRace; -- Concaténation SELECT CONCAT(cl.PrenomClient, ' ', cl.NomClient) AS Client, ch.NomChien AS Chien, r.NomRace AS Race FROM Clients cl LEFT JOIN Chiens ch ON ch.IdClient = cl.IdClient LEFT JOIN Races r ON ch.IdRace = r.IdRace;
COUNT(*)
SELECT COUNT(*) FROM Chiens;
COUNT(col)
SELECT COUNT(NumTatouageChien) FROM Chiens;
SUM(col)
SELECT SUM(PrixVente) FROM Chiens;
AVG(col)
SELECT AVG(PrixVente) FROM Chiens;
MAX(col)
SELECT MAX(PrixVente) FROM Chiens;
MIN(col)
SELECT MIN(DateNaissanceChien) FROM Chiens;
-- Nombre de chiens par sexe SELECT SexeChien AS Sexe, COUNT(*) AS "Nombre de chiens" FROM Chiens GROUP BY SexeChien; -- Chiffre d'affaires par année SELECT DATE_PART('year', DateVente) AS Annee, ROUND(SUM(PrixVente)) AS CA FROM Chiens WHERE DateVente IS NOT NULL GROUP BY DATE_PART('year', DateVente) ORDER BY CA DESC; -- Clients ayant acheté plus d'un chien (HAVING filtre APRÈS agrégation) SELECT IdClient, COUNT(*) AS NbChiens FROM Chiens WHERE IdClient IS NOT NULL GROUP BY IdClient HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC;
WHERE filtre les lignes avant agrégation. HAVING filtre les résultats après agrégation. On ne peut pas mettre une fonction d’agrégation dans un WHERE.
WHERE
HAVING
-- Afficher chaque chien avec le prix moyen de tous les chiens SELECT NomChien, (SELECT AVG(PrixVente) FROM Chiens) AS PrixMoyen FROM Chiens;
-- Clients ayant acheté au moins un chien à plus de 500 euros SELECT * FROM Clients c WHERE EXISTS ( SELECT 1 FROM Chiens ch WHERE ch.IdClient = c.IdClient AND ch.PrixVente > 500 );
EXISTS est souvent plus performant qu’un JOIN quand on teste seulement l’existence d’un enregistrement.
EXISTS
JOIN
Une vue encapsule une requête réutilisable. Elle se comporte comme une table virtuelle.
-- Créer une vue : chiens vendus en 2023 CREATE VIEW VChiensVendus2023 AS SELECT NomChien, DateVente, PrixVente FROM Chiens WHERE DateVente BETWEEN '2023-01-01' AND '2023-12-31'; -- Utiliser la vue SELECT * FROM VChiensVendus2023; -- Supprimer la vue DROP VIEW IF EXISTS VChiensVendus2023;
-- Note de fidélité par client selon le total dépensé SELECT cl.NomClient, SUM(ch.PrixVente) AS TotalDepense, CASE WHEN SUM(ch.PrixVente) >= 1500 THEN 'Excellent' WHEN SUM(ch.PrixVente) >= 1000 THEN 'Bon' ELSE 'Moyen' END AS NoteFidelite FROM Clients cl LEFT JOIN Chiens ch ON cl.IdClient = ch.IdClient GROUP BY cl.IdClient, cl.NomClient;
Un trigger (déclencheur) exécute automatiquement une fonction lors d’un événement sur une table (INSERT, UPDATE, DELETE).
Exemple : conserver un historique des modifications de prix dans une table d’audit.
-- 1. Table d'audit CREATE TABLE AuditPrix ( IdAudit SERIAL PRIMARY KEY, IdChien INTEGER, AncienPrix DECIMAL(10,2), NouveauPrix DECIMAL(10,2), DateModif TIMESTAMP DEFAULT NOW() ); -- 2. Fonction déclenchée CREATE OR REPLACE FUNCTION audit_prix() RETURNS TRIGGER AS $$ BEGIN IF OLD.PrixVente IS DISTINCT FROM NEW.PrixVente THEN INSERT INTO AuditPrix (IdChien, AncienPrix, NouveauPrix) VALUES (OLD.IdChien, OLD.PrixVente, NEW.PrixVente); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- 3. Associer la fonction à la table CREATE TRIGGER trg_audit_prix BEFORE UPDATE ON Chiens FOR EACH ROW EXECUTE FUNCTION audit_prix();
-- Créer un superutilisateur CREATE USER nathalie SUPERUSER PASSWORD 'motdepasse'; -- Créer un utilisateur classique CREATE ROLE antoine WITH LOGIN PASSWORD 'mdp_antoine'; -- Modifier le mot de passe ALTER ROLE antoine WITH PASSWORD 'nouveau_mdp'; -- Lister les utilisateurs \du
-- Donner uniquement le droit de lecture à Antoine GRANT SELECT ON ALL TABLES IN SCHEMA public TO antoine; -- Révoquer tous les droits REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM antoine; -- Créer un rôle groupe et l'attribuer CREATE ROLE data_analyst; GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst; GRANT data_analyst TO antoine;
Appliquer le principe du moindre privilège : chaque utilisateur ne reçoit que les droits strictement nécessaires à son rôle.
Depuis PowerShell (Windows), dans le dossier de votre projet :
# Sauvegarder une base docker exec pg_wouaf pg_dump -U devuser -d chenil_v4 > backup_chenil_v4.sql # Sauvegarder toutes les bases docker exec pg_wouaf pg_dumpall -U devuser > backup_all.sql # Restaurer docker exec -i pg_wouaf psql -U devuser -d chenil_v4 < backup_chenil_v4.sql
Planifiez des sauvegardes régulières et testez la restauration périodiquement. Une sauvegarde non testée n’est pas une sauvegarde fiable.
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.7.3</version> </dependency>
import java.sql.*; public class PostgresDemo { private static final String URL = "jdbc:postgresql://localhost:5432/chenil_v4"; private static final String USER = "devuser"; private static final String PWD = "devpassword"; public static void main(String[] args) throws SQLException { try (Connection conn = DriverManager.getConnection(URL, USER, PWD); PreparedStatement ps = conn.prepareStatement( "SELECT NomChien, PrixVente FROM Chiens WHERE PrixVente > ?")) { ps.setDouble(1, 500.0); ResultSet rs = ps.executeQuery(); while (rs.next()) { System.out.println(rs.getString("NomChien") + " - " + rs.getDouble("PrixVente") + " euros"); } } } }
Utilisez toujours des PreparedStatement et jamais la concaténation de chaînes pour construire des requêtes SQL. La concaténation directe expose à l’injection SQL.
PreparedStatement
spring.datasource.url=jdbc:postgresql://localhost:5432/chenil_v4 spring.datasource.username=devuser spring.datasource.password=devpassword spring.datasource.driver-class-name=org.postgresql.Driver spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect spring.jpa.hibernate.ddl-auto=validate
@Entity @Table(name = "Chiens") public class Chien { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "IdChien") private Long id; @Column(name = "NomChien", nullable = false) private String nom; @ManyToOne @JoinColumn(name = "IdRace") private Race race; @ManyToOne @JoinColumn(name = "IdClient") private Client client; // getters / setters ... }
<?php $dsn = 'pgsql:host=localhost;port=5432;dbname=chenil_v4'; $user = 'devuser'; $password = 'devpassword'; try { $pdo = new PDO($dsn, $user, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ]); $stmt = $pdo->prepare( 'SELECT NomChien, PrixVente FROM Chiens WHERE PrixVente > :prix' ); $stmt->execute([':prix' => 500]); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) { echo $row['NomChien'] . ' — ' . $row['PrixVente'] . " euros\n"; } } catch (PDOException $e) { echo 'Erreur : ' . $e->getMessage(); }
En PHP, Doctrine ORM (utilisé dans Symfony) génère automatiquement les entités et les migrations à partir de votre schéma. La commande php bin/console doctrine:mapping:import est votre alliée pour partir d’une base existante.
php bin/console doctrine:mapping:import
Les exercices suivants s’effectuent sur la base chenil_v4. Connectez-vous via pgAdmin ou psql.
NOT EXISTS
Nathalie souhaite ajouter un suivi vétérinaire à sa base de données. Chaque chien peut avoir plusieurs visites vétérinaires. Chaque visite est caractérisée par une date, un motif et un vétérinaire.
CREATE TABLE
\l
\c nom_base
\dt
\d nom_table
\d+ nom_table
\du
\timing
\e
\i fichier.sql
\q