Modélisez et implémentez une base de données relationnelle avec UML

Dans le chapitre précédent, vous avez créé votre base de données, maintenant vous allez l'utiliser !

Utiliser une base de données peut se résumer à quatre opérations principales, le fameux CRUD :

• Create : créer des données

• Read : lire des données

• Update : modifier des données

• Delete : supprimer des données

Pour effectuer ces opérations, nous allons encore une fois utiliser le langage SQL.

Ne vous en faites pas, vous allez voir, c'est assez simple et lisible le SQL...

Un petit aperçu des requêtes de CRUD

Create avec la commande INSERT

La création de données consiste à insérer de nouvelles lignes dans les tables. Pour cela, on va utiliser la commande SQL INSERT :

INSERT INTO public.utilisateur (
    nom,
    prenom
) VALUES (
    'Dalton',
    'Joe'
);

Avec cette instruction, je viens d'ajouter l'utilisateur Joe Dalton dans la table utilisateur.

Read avec la commande SELECT

Pour récupérer toutes les informations du projet System42 :

SELECT * FROM projet
WHERE nom = 'System42';

Il est aussi possible de croiser des informations en s'appuyant sur les clés étrangères. Pour récupérer le nom et le prénom du responsable du projet System42 :

SELECT nom, prenom
FROM utilisateur
WHERE
    id IN (
        SELECT responsable_id FROM projet
        WHERE nom = 'System42'
    )
;

Et même fusionner les tables (on appelle cela une jointure) :

/* Je récupère toutes les colonnes de la table projet
   + le nom et le prénom de l'utilisateur */
SELECT
    projet.*,
    utilisateur.nom, utilisateur.prenom
FROM
    projet,
    JOIN utilisateur ON utilisateur.id = projet.responsable_id
WHERE
    projet.nom = 'System42'
;

Update avec la commande UPDATE

Voici comment modifier le titre et le statut actuel du ticket numéro 256 :

UPDATE ticket SET
    titre = 'Le nouveau titre',
    statut_actuel_id = 3
WHERE numero = 256;

Delete avec la commande DELETE

Par suppression, on entend suppression de ligne dans une table, et non suppression de valeur dans une colonne.

Voici comment supprimer le projet ayant l'id 18 :

DELETE FROM projet WHERE id = 18;

Pour supprimer tous les utilisateurs qui s'appellent Dalton :

DELETE FROM utilisateur WHERE nom = 'Dalton';

Les transactions

Il est possible de regrouper les instructions dans une transaction comme s'il s'agissait d'un seul bloc.

Une transaction est ouverte avec l'instruction BEGIN TRANSACTION; et elle est clôturée de deux manières :

• soit en la validant (instruction COMMIT;)

• soit en l'annulant (instruction ROLLBACK;)

Ainsi, les modifications apportées par les commandes dans la transaction se seront pas visibles en dehors de la transaction tant que celle-ci n'aura pas été validée. De même, si la transaction est annulée, alors les modifications ne seront pas appliquées.

Cela est particulièrement utile pour former un ensemble d'instructions cohérent.

Imaginez par exemple que vous vouliez supprimer un projet de la base. À cause des clés étrangères, vous allez aussi devoir supprimer les lignes des autres tables qui référencent ce projet (ticket, version) et ceci en cascade (tables bug, evolution, bug_version_affectee...).

Vous allez donc :

1. démarrer une transaction

2. faire toutes les requêtes de DELETE de la grappe concernée par le projet (en commençant par les feuilles)

3. valider la transaction

Ce qui va se faire comme ceci :

BEGIN TRANSACTION;   -- Ouverture de la transaction

-- Projet à supprimer : projet.id = 18

DELETE FROM evolution
WHERE EXISTS (
    SELECT 1 FROM ticket
    WHERE ticket.numero = evolution.ticket_numero
    AND ticket.projet_id = 18
);

/*
DELETE FROM bug_version_affectee ...
DELETE FROM bug ...
...
DELETE FROM ticket ...
DELETE FROM version ...
*/

DELETE FROM projet WHERE id = 18;

COMMIT;   -- Validation de la transaction