Épreuve blanche E6 n° 2 : contrôler les licences

Reformulation. « Une nouvelle installation doit être refusée par la base elle-même quand le logiciel n'a plus de licence libre ; GestParc affiche un message explicite ; un écran montre les dépassements existants. »

Les quatre questions.

La grille des six axes.

Ambiguïté. « Le besoin ne dit pas ce que doit faire le script d'import quand une ligne est refusée. La base renverra une erreur sur cette ligne ; je pars du principe que le script s'arrête et que le technicien traite le cas à la main, puisqu'il faut décider d'acheter une licence ou de désinstaller. Si vous voulez que le script continue et liste les refus, il faut le modifier, ce qui sort de GestParc. »

Spécifications techniques.

• Trigger BEFORE INSERT ON installation : lit nb_licences du logiciel ; s'il n'est pas vide, compte les installations ; si le compte atteint le nombre de licences, lève une erreur SIGNAL SQLSTATE '45000' avec le message demandé.
• Verrou FOR UPDATE sur la ligne du logiciel, pour que deux ajouts simultanés passent l'un après l'autre.
• Vue v_licences_depassees : jointure logiciel et installation, GROUP BY, HAVING COUNT(*) > nb_licences.
• GestParc : dans FrmInstallation, intercepter la MySqlException dont le code d'erreur (ErrorCode) vaut 1644 (l'erreur levée par SIGNAL) et afficher son message ; un formulaire FrmDepassements avec un DataGridView alimenté par la vue.
• Tests : script SQL de test sur une copie de la base, puis essais dans GestParc ; rapport de test.

Outils. Visual Studio et MySqlConnector, déjà utilisés ; le client MySQL pour écrire et tester le trigger avant de toucher au C#.

Alternative écartée. Un SELECT COUNT(*) dans GestParc avant l'INSERT. Il ne protège pas du script d'import, ce qui contredit la spécification 1, et deux techniciens simultanés passeraient tous les deux entre la lecture et l'écriture.

Vital et confort.

1. « Une procédure ne protège que ceux qui l'appellent : le script d'import fait des INSERT directs. Le trigger s'exécute à chaque INSERT, d'où qu'il vienne. C'est la seule façon de respecter "quelle que soit la façon dont l'installation est ajoutée". »
2. « Le trigger ne regarde que les nouveaux ajouts : les trois installations existantes restent. Toute nouvelle installation d'Acrobat est refusée, puisque 3 est déjà au-delà de 2. Le dépassement apparaît dans le nouvel écran ; le responsable décidera d'acheter une licence ou de désinstaller, ce n'est pas au code de choisir. »
3. « SIGNAL SQLSTATE '45000' lève une erreur MySQL de numéro 1644, avec mon texte comme message. MySqlConnector la transforme en MySqlException ; je l'intercepte par son code d'erreur dans FrmInstallation et j'affiche son message dans une boîte de dialogue. »
4. « Rien ne l'empêche aujourd'hui : la modification est acceptée et le logiciel apparaît dans la liste des dépassements. Je pourrais ajouter un trigger sur logiciel qui refuse la baisse, mais c'est parfois une décision volontaire, par exemple une licence résiliée. Je préfère le rendre visible plutôt que de l'interdire, et je vous propose de le noter comme évolution. »

La règle dans la base.

-- migrations/2027-04-licences.sql
DELIMITER //

CREATE PROCEDURE verifier_licence(IN p_id_logiciel INT)
BEGIN
  DECLARE v_nom VARCHAR(60);
  DECLARE v_licences INT;
  DECLARE v_installees INT;
  DECLARE v_message VARCHAR(128);

  -- Verrou sur la ligne du logiciel : deux ajouts simultanés passent l'un après l'autre.
  SELECT nom, nb_licences INTO v_nom, v_licences
    FROM logiciel WHERE id_logiciel = p_id_logiciel FOR UPDATE;

  IF v_licences IS NOT NULL THEN
    SELECT COUNT(*) INTO v_installees FROM installation WHERE id_logiciel = p_id_logiciel;
    IF v_installees >= v_licences THEN
      SET v_message = CONCAT(v_nom, ' : ', v_installees, ' installation(s) pour ', v_licences, ' licence(s)');
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;
    END IF;
  END IF;
END //

CREATE TRIGGER trg_installation_avant_ajout
BEFORE INSERT ON installation
FOR EACH ROW
BEGIN
  CALL verifier_licence(NEW.id_logiciel);
END //

CREATE TRIGGER trg_installation_avant_modif
BEFORE UPDATE ON installation
FOR EACH ROW
BEGIN
  IF NEW.id_logiciel <> OLD.id_logiciel THEN
    CALL verifier_licence(NEW.id_logiciel);
  END IF;
END //

DELIMITER ;

CREATE OR REPLACE VIEW v_licences_depassees AS
SELECT l.id_logiciel, l.nom, l.editeur, l.nb_licences,
       COUNT(*) AS nb_installations,
       COUNT(*) - l.nb_licences AS ecart
  FROM logiciel l
  JOIN installation i ON i.id_logiciel = l.id_logiciel
 WHERE l.nb_licences IS NOT NULL
 GROUP BY l.id_logiciel, l.nom, l.editeur, l.nb_licences
HAVING COUNT(*) > l.nb_licences;

-- Annulation :
-- DROP TRIGGER trg_installation_avant_ajout; DROP TRIGGER trg_installation_avant_modif;
-- DROP PROCEDURE verifier_licence; DROP VIEW v_licences_depassees;

Le trigger BEFORE UPDATE ne vérifie que les changements de logiciel : modifier la date d'installation ne doit pas être refusé parce que le logiciel est plein. Le cours Procédures stockées et triggers détaille cette syntaxe.

Le formulaire.

// FrmInstallation.cs
private void btnInstaller_Click(object sender, EventArgs e)
{
    if (cboPoste.SelectedValue is not int idPoste || cboLogiciel.SelectedValue is not int idLogiciel)
    {
        MessageBox.Show("Choisis un poste et un logiciel.", "Installation",
            MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    try
    {
        _installationDao.Ajouter(idPoste, idLogiciel, DateTime.Today);
        ChargerInstallations();
    }
    catch (MySqlException ex) when (ex.Number == 1644)   // erreur levée par SIGNAL dans la base
    {
        MessageBox.Show($"Installation refusée. {ex.Message}", "Plus de licence disponible",
            MessageBoxButtons.OK, MessageBoxIcon.Warning);
    }
    catch (MySqlException ex) when (ex.Number == 1062)   // clé primaire : déjà installé sur ce poste
    {
        MessageBox.Show("Ce logiciel est déjà installé sur ce poste.", "Installation",
            MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

InstallationDao.Ajouter ne change pas : c'est toujours un INSERT paramétré. Toute la règle vit dans la base.

Le script de test, lancé sur une copie de la base :

INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (14, 5, CURDATE());                         -- Odoo, 8e installation : acceptée
INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (9, 5, CURDATE());                          -- Odoo, 9e : refusée
INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (14, 3, CURDATE());                         -- Firefox, libre : acceptée
INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (14, 8, CURDATE());                         -- Acrobat Pro, déjà en dépassement : refusée
UPDATE installation SET date_installation = CURDATE()
 WHERE id_poste = 14 AND id_logiciel = 5;            -- même logiciel : acceptée
UPDATE installation SET id_logiciel = 5
 WHERE id_poste = 9 AND id_logiciel = 3;             -- Firefox remplacé par Odoo, plein : refusée
SELECT nom, nb_licences, nb_installations, ecart FROM v_licences_depassees;

ERROR 1644 (45000): Odoo : 8 installation(s) pour 8 licence(s)
ERROR 1644 (45000): Acrobat Pro : 3 installation(s) pour 2 licence(s)
ERROR 1644 (45000): Odoo : 8 installation(s) pour 8 licence(s)

+-------------+-------------+------------------+-------+
| nom         | nb_licences | nb_installations | ecart |
+-------------+-------------+------------------+-------+
| Acrobat Pro |           2 |                3 |     1 |
+-------------+-------------+------------------+-------+

La documentation technique, une demi-page : la règle (« une installation est refusée quand le nombre d'installations du logiciel atteint nb_licences ; les logiciels sans nb_licences ne sont pas limités »), les objets créés (procédure, deux triggers, vue) et leur script d'annulation, le code d'erreur 1644 et son message, l'effet sur le script d'import (il s'arrête sur la ligne refusée), la requête de consultation des dépassements.

Le rapport de test.

Le formulaire FrmDepassements, passé en confort par l'ajustement, n'est pas fait.

1. « Pour écrire la règle une seule fois. Si l'éditeur change sa façon de compter, par exemple une licence pour deux postes, je modifie la procédure et les deux triggers suivent. Deux copies finiraient par diverger. »
2. « Deux techniciens qui ajoutent la dernière licence au même instant liraient tous les deux sept installations et passeraient tous les deux. Le verrou sur la ligne du logiciel fait attendre le second jusqu'à la fin de la transaction du premier : il compte alors huit et il est refusé. »
3. « WHERE filtre les lignes avant le regroupement : il ne connaît pas encore le nombre d'installations. HAVING filtre les groupes, après le COUNT. Le WHERE nb_licences IS NOT NULL reste en WHERE, parce qu'il porte sur une colonne de ligne. »
4. « Il comprend que tout est pris, mais le message pourrait être plus clair. J'écrirais "Plus de licence disponible pour Odoo : 8 installations pour 8 licences", et j'adapterais le singulier et le pluriel. C'est une amélioration de dix minutes, que j'ajoute à mes notes. »