SCENE 5 : Définition des Données - L'Architecte de l'Ombre | Faites entrer SQL

Le plan de la ville n'est pas figé. On bâtit, on rase, on agrandit. Les tables sont les immeubles. Les colonnes, les étages. Les contraintes, les lois. Le DDL, c'est le permis de construire.

Introduction au DDL – Le Plan du Maître

Le Data Definition Language.
Pas pour interroger. Pour construire.
Créer les tables, les colonnes, les liens.
La charpente. Avant les meubles, avant les habitants.
Si la DML parle aux données, la DDL parle à la structure. Au squelette.

📐 Création et Modification des Tables

`CREATE TABLE` – Poser la Première Pierre

CREATE TABLE employe (
    employe_id SERIAL PRIMARY KEY, -- Le numéro de plaque
    matricule VARCHAR(10) UNIQUE NOT NULL, -- Le code secret
    nom VARCHAR(50) NOT NULL, -- Le nom de famille. Toujours.
    prenom VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL, -- Une adresse, un homme
    date_naissance DATE NOT NULL,
    date_embauche DATE NOT NULL DEFAULT CURRENT_DATE, -- Le jour où il est entré
    salaire DECIMAL(10,2) CHECK (salaire > 0), -- L'argent. Toujours positif.
    manager_id INTEGER, -- Le chef. Peut être NULL si c'est le grand patron.
    departement_id INTEGER NOT NULL, -- La section. Obligatoire.
    
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employe(employe_id)
        ON DELETE SET NULL, -- Si le chef disparaît, on devient orphelin.
        
    CONSTRAINT fk_departement 
        FOREIGN KEY (departement_id) 
        REFERENCES departement(departement_id)
        ON DELETE CASCADE, -- Si la section saute, on saute avec.
        
    CONSTRAINT check_age_adulte 
        CHECK (date_naissance <= CURRENT_DATE - INTERVAL '18 years') -- Pas de mineurs.
);

Les Types – Le Matériau de Construction

Numériques :

SMALLINT, INT, BIGINT. Choisis le plus petit qui passe. L'espace, c'est de l'argent.
DECIMAL(10,2) pour l'argent. Jamais FLOAT. Les flottants mentent sur les cents.
SERIAL (PostgreSQL) / AUTO_INCREMENT (MySQL) – Le numéro qui monte tout seul. Comme un compteur.

Textes :

CHAR(n) – Longueur fixe. Pour les codes. 'FR', 'US'. Rempli d'espaces.
VARCHAR(n) – Longueur variable. Jusqu'à la limite. L'espace utilisé est ce qu'il faut.
TEXT (PostgreSQL) – Illimité. Pour les confessions longues.

Temps :

DATE – La date seule. '2024-03-15'.
TIMESTAMP – La date et l'heure. '2024-03-15 14:30:00'.
TIMESTAMPTZ (PostgreSQL) – Avec fuseau horaire. Toujours. Le temps est relatif, mais la base doit savoir.

Booléens :

BOOLEAN. TRUE, FALSE, NULL. Un bit. Une décision.

Types Avancés (PostgreSQL, le raffinement) :

JSONB – Le JSON binaire. Indexable. Pour les données qui changent de forme.
UUID – Un identifiant universel. 128 bits de hasard. Pas de séquence devinable.
ENUM – Une liste de valeurs autorisées. Comme un menu fermé.

Les Contraintes – Les Lois de la Ville

1. PRIMARY KEY – L'identifiant unique.
Le numéro de dossier. Une table, une clé primaire.
Implique NOT NULL et UNIQUE.

2. FOREIGN KEY – Le lien de sang.
Garantit que la référence existe ailleurs.
ON DELETE dit ce qui se passe quand le parent disparaît :

RESTRICT – Empêche. La sécurité.
CASCADE – Supprime les enfants. Un nettoyage.
SET NULL – Laisse les enfants orphelins.
SET DEFAULT – Leur donne une valeur par défaut.

3. UNIQUE – L'exclusivité.
Une valeur, une occurrence. Plusieurs NULL sont acceptés (ils ne sont pas égaux à eux-mêmes).

4. CHECK – La règle métier gravée dans le marbre.
CHECK (salaire > 0).
CHECK (date_fin > date_debut).
La base refuse ce qui ne respecte pas la règle.

5. DEFAULT – La valeur par défaut quand on ne dit rien.
DEFAULT CURRENT_TIMESTAMP.
DEFAULT TRUE.
Le silence a une signification.

6. NOT NULL – L'obligation.
La colonne doit être remplie. Toujours.
La question à se poser : "Est-ce que cette info peut manquer ?"
Pour une date de naissance ? Non.
Pour un deuxième prénom ? Peut-être.

Dates de création/modification (pattern universel) :

date_creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_modification TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-- La modification sera mise à jour par un trigger. Plus tard.

🔧 Altération : `ALTER TABLE` – La Rénovation Urbaine

La ville change. Les besoins évoluent.
On ne rase pas tout. On modifie.
ALTER TABLE est le permis de démolition/reconstruction partielle.

Ajouter une Colonne – Agrandir l'Immeuble

ALTER TABLE client
ADD COLUMN telephone VARCHAR(15);
-- Une nouvelle ligne dans la fiche.

ALTER TABLE produit
ADD COLUMN seuil_alerte INTEGER NOT NULL DEFAULT 10;
-- NOT NULL avec DEFAULT : on donne une valeur à l'existant.

Supprimer une Colonne – Abattre un Mur

ALTER TABLE employe
DROP COLUMN ancienne_colonne CASCADE;
-- CASCADE : tout ce qui tenait à cette colonne tombe aussi.

Modifier une Colonne – Changer la Destination

PostgreSQL :

-- Changer le type (si convertible)
ALTER TABLE client
ALTER COLUMN age TYPE SMALLINT;

-- Rendre obligatoire (il ne doit pas y avoir de NULL)
ALTER TABLE commande
ALTER COLUMN statut SET NOT NULL;

-- Ajouter une valeur par défaut
ALTER TABLE produit
ALTER COLUMN prix SET DEFAULT 0.00;

MySQL :

ALTER TABLE client
MODIFY COLUMN email VARCHAR(150) NOT NULL;

Renommer – Changer l'Enseigne

ALTER TABLE ancien_nom
RENAME TO nouveau_nom;

ALTER TABLE employe
RENAME COLUMN ancien_nom_col TO nouveau_nom_col;

Gérer les Contraintes – Modifier le Règlement

-- Ajouter une contrainte
ALTER TABLE commande
ADD CONSTRAINT fk_client
FOREIGN KEY (client_id) REFERENCES client(client_id);

-- Supprimer une contrainte (il faut connaître son nom)
ALTER TABLE employe
DROP CONSTRAINT check_salaire;

Stratégies en Production – La Prudence du Bâtisseur

Planifier les migrations. Des scripts versionnés. Jamais à la main.
Tester en pré-production. Avec des données réelles.
Pour les grosses modifications :
- Ajouter une colonne NULLABLE d'abord.
- Remplir progressivement (par lots de 10 000).
- Basculer l'application.
- Supprimer l'ancienne colonne.

Exemple de migration progressive :

-- Étape 1 : Ajouter la nouvelle colonne (vide)
ALTER TABLE commande ADD COLUMN nouveau_statut VARCHAR(20);

-- Étape 2 : Remplir par lots (la nuit)
UPDATE commande SET nouveau_statut = 
    CASE 
        WHEN ancien_statut = 'P' THEN 'EN_ATTENTE'
        WHEN ancien_statut = 'V' THEN 'VALIDEE'
        ELSE 'INCONNU'
    END
WHERE id BETWEEN 1 AND 10000;

-- Étape 3 : Basculer l'application
-- Étape 4 : Supprimer l'ancien
ALTER TABLE commande DROP COLUMN ancien_statut;

🗑️ Suppression : `DROP TABLE` – La Démolition

DROP TABLE table_a_supprimer;

Options :

CASCADE – Démolit aussi tout ce qui est accroché. Les dépendances.
RESTRICT (défaut) – Refuse si quelque chose dépend de la table.

LA RÈGLE : Avant un DROP TABLE :

Faites un SELECT pour voir ce que vous tuez.
Mieux : renommez.
Puis supprimez.

-- 1. Vérifier
SELECT * FROM table_a_supprimer LIMIT 10;

-- 2. Renommer (réversible)
ALTER TABLE table_a_supprimer RENAME TO table_a_supprimer_archive_20240315;

-- 3. Supprimer après vérification
DROP TABLE table_a_supprimer_archive_20240315;

`TRUNCATE TABLE` vs `DROP TABLE`

TRUNCATE : Vide la table. Garde la structure. Rapide. Réinitialise les séquences.
DROP : Supprime la table. Structure et données. Il faut recréer.

📜 Script de Migration – Le Plan en Action

-- migration_v2.0.sql
-- Ajout du système de fidélité. Comme un nouveau quartier.

BEGIN; -- Une transaction. Tout ou rien.

-- 1. Créer la table des points
CREATE TABLE programme_fidelite (
    client_id INTEGER PRIMARY KEY,
    points INTEGER NOT NULL DEFAULT 0 CHECK (points >= 0),
    niveau VARCHAR(20) DEFAULT 'Bronze',
    date_dernier_achat DATE,
    
    CONSTRAINT fk_client
        FOREIGN KEY (client_id)
        REFERENCES client(client_id)
        ON DELETE CASCADE -- Si le client disparaît, ses points aussi.
);

-- 2. Ajouter une colonne à commande
ALTER TABLE commande
ADD COLUMN points_gagnes INTEGER DEFAULT 0;

-- 3. Un index pour chercher vite
CREATE INDEX idx_fidelite_points ON programme_fidelite(points DESC);

-- 4. Peupler avec l'existant
INSERT INTO programme_fidelite (client_id, points)
SELECT client_id, 
       CASE 
           WHEN COUNT(*) > 10 THEN 500
           WHEN COUNT(*) > 5 THEN 200
           ELSE 50
       END
FROM commande
GROUP BY client_id;

-- 5. Une vue pour simplifier le regard
CREATE VIEW vue_client_fidelite AS
SELECT c.*, pf.points, pf.niveau
FROM client c
LEFT JOIN programme_fidelite pf ON c.client_id = pf.client_id;

COMMIT; -- Valider. C'est fait.
-- En cas de problème : ROLLBACK. Comme si de rien n'était.

Fin du DDL

Le DDL, c'est le pouvoir de créer et de détruire.
Un CREATE TABLE bien pensé évite des ALTER TABLE douloureux plus tard.
Les types sont les matériaux. Les contraintes sont les lois.
ALTER TABLE est l'évolution. DROP TABLE est l'effacement.

Souvenez-vous :
Une base de données, c'est comme une ville.
On ne construit pas n'importe comment.
On ne détruit pas sans réfléchir.
Chaque modification laisse une trace.
Dans les logs. Dans l'histoire.

Prochain épisode : les index.
La mémoire de la ville. Les raccourcis. Les traces qu'on laisse pour retrouver son chemin plus vite.

SCENE 5 : Définition des Données - L'Architecte de l'Ombre