Protéger les règles d’inscription aux ateliers

Introduction

REPAIR CIRCLE organise des ateliers pratiques de réparation. Son poste d’inscription sait déjà enregistrer une réservation et afficher la véritable liste des inscrits. Cependant, la base de données ne rejette pas encore les inscriptions en double ni les références invalides. L’objectif est de conserver ce poste opérationnel tout en faisant appliquer les relations d’inscription par la base de données.

Vous devez savoir naviguer dans un terminal et posséder des connaissances élémentaires de SELECT, INSERT et UPDATE dans MySQL. Les contraintes, les transactions utilisées pour des essais réversibles et ALTER TABLE sont expliqués ici. MySQL et les données fictives des ateliers sont prêts. Utilisez Terminal tout au long du TP ; les clients fournis se connectent à la base MySQL de ce cours.

Vous pouvez ajouter l’inscription demandée et modifier la structure nécessaire de registrations, en enregistrant votre SQL dans rules.sql. Conservez les identifiants d’origine, les personnes, les titres des séances, les tailles de groupe, les notes et les clients fournis. Une personne peut assister à plusieurs séances ; des personnes différentes peuvent porter le même nom. Ce projet ne limite pas la capacité et ne gère pas l’attribution concurrente des places.

Utiliser le poste et mettre en évidence un doublon

Dans cette étape, vous enregistrerez une réservation ordinaire, lirez ses véritables relations et montrerez la règle manquante sans laisser d’enregistrement incorrect. Commencez dans Terminal. cd change de répertoire ; réutilisez ce chemin absolu si vous entrez dans le mauvais répertoire :

cd /home/labex/project/sign-up-desk

./desk est le client d’inscription fourni dans ce répertoire. ./ désigne le répertoire courant. roster lit les véritables tables MySQL ; il n’affiche pas une réponse précalculée :

./desk roster

Vous devez voir les deux inscriptions d’origine. L’inscription 501 relie la personne 101, Mara Lin, à la séance 11, Lamp Repair, avec une place et la note Window seat. L’inscription 502 relie la personne 102, Alex Kim, à la séance 12, Bike Patch, avec deux places et Bring partner. Conservez ces relations et valeurs d’origine.

Une nouvelle demande concerne la personne 104, Sam Reed, pour la séance 11 avec une place. book prend l’identifiant de la personne, l’identifiant de l’événement et la taille du groupe, dans cet ordre. Enregistrez cette réservation légitime :

./desk book 104 11 1

./desk roster

La troisième inscription appartient à Sam, pas à Mara, même si les deux participent à Lamp Repair. Le client renvoie l’identifiant enregistré par la base. Vous n’avez pas besoin de choisir ni de mémoriser ce nouvel identifiant.

./db ouvre la connexion client MySQL de ce cours. Utilisez-le à la place de la commande mysql de l’image, qui est sans rapport avec ce client. --table demande une sortie tabulaire lisible ; -e exécute le SQL entre guillemets et ferme la connexion. Les guillemets regroupent les espaces et les points-virgules dans un seul argument. Lisez les deux tables parentes :

./db --table -e "SELECT id,name FROM participants ORDER BY id; SELECT id,title FROM sessions ORDER BY id;"

Les personnes 102 et 103 s’appellent toutes deux Alex Kim. Ce sont des personnes différentes, car leurs identifiants diffèrent. Un nom ne doit pas devenir une identité unique d’inscription. Les trois tables ont les relations suivantes :

Les règles métier n’autorisent qu’une inscription par paire (participant_id,session_id), avec un nombre positif de places et des identifiants parents existants. Une réservation existante doit empêcher la suppression ou la modification de l’identifiant de la personne ou de la séance qu’elle référence. Les séances inutilisées peuvent toujours être supprimées si nécessaire.

Avant de protéger le schéma, testez la règle de rejet des doublons actuellement manquante. Une transaction regroupe les modifications de données sur une connexion. START TRANSACTION démarre l’essai ; ROLLBACK annule son INSERT au lieu de l’enregistrer. La commande unique suivante essaie volontairement une deuxième réservation pour Mara dans Lamp Repair, compte cette paire, puis annule l’essai. Les guillemets sur plusieurs lignes forment toujours un seul argument du shell :

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,11,1);
SELECT COUNT(*) AS matching_registrations FROM registrations WHERE participant_id=101 AND session_id=11;
ROLLBACK;"

Le nombre est 2 : la base a accepté un doublon. C’est une véritable faiblesse, pas un échec de connexion. L’essai utilise INSERT, pas ALTER. Son annulation laisse intactes la réservation d’origine de Mara et la nouvelle demande de Sam :

./desk roster

Il doit toujours y avoir trois inscriptions. Si une commande ordinaire utilise un mauvais chemin, revenez avec le cd absolu. Utilisez ls pour lister les fichiers du répertoire. Ne supprimez pas de personne, ne videz pas la table des inscriptions et ne réinitialisez pas la base pour corriger une faute de frappe.

Faire respecter les relations et conserver les réservations légitimes

Dans cette étape, vous enregistrerez une modification du schéma, l’appliquerez à la table existante et prouverez que les réservations utiles fonctionnent toujours tandis que les demandes invalides sont rejetées. Commencez dans le même répertoire de Terminal.

Une contrainte est une règle de base de données appliquée aux écritures, y compris celles provenant d’un autre client que ./desk. Une contrainte UNIQUE composite rejette une paire répétée sans interdire à la même personne de participer à une autre séance. Une FOREIGN KEY relie une colonne de référence à la clé primaire d’une table parente. RESTRICT refuse la suppression ou la modification d’un identifiant tant qu’une réservation référence ce parent ; il ne supprime pas automatiquement la réservation.

CHECK (seats > 0) rejette zéro et les valeurs négatives. En SQL, NULL signifie une valeur manquante ; un CHECK dont le résultat est inconnu ne rejette pas NULL à lui seul. NOT NULL est donc également nécessaire pour le nombre de places et les deux références. MODIFY redéfinit le type de la colonne et ajoute cette exigence. Les trois tables fournies utilisent déjà InnoDB, le moteur de stockage qui fait respecter ces relations.

ALTER TABLE modifie une table existante. MySQL valide implicitement les modifications du schéma : ne placez pas cet ALTER dans une transaction d’essai en espérant que ROLLBACK l’annulera. Nous utilisons une seule instruction ALTER sur des enregistrements déjà valides, en conservant leurs identifiants et leurs notes.

Enregistrez dans rules.sql uniquement des instructions ALTER TABLE ordinaires pour registrations, sans qualifier le nom de la table par un nom de base ; USE, les commandes shell et les modifications d’autres objets sont hors du périmètre de ce fichier. N’ajoutez pas de déclencheurs et ne modifiez pas les définitions des tables parentes. Dans le shell Linux, > remplace le fichier de destination et <<'SQL' commence un here-document littéral. Collez le bloc complet, y compris son SQL final seul sur une ligne. Ne saisissez pas cette commande cat à une invite MySQL :

cat > rules.sql <<'SQL'
ALTER TABLE registrations
  MODIFY participant_id INT NOT NULL,
  MODIFY session_id INT NOT NULL,
  MODIFY seats INT NOT NULL,
  ADD CONSTRAINT one_person_event UNIQUE (participant_id, session_id),
  ADD CONSTRAINT known_person FOREIGN KEY (participant_id)
    REFERENCES participants(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT known_event FOREIGN KEY (session_id)
    REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT positive_group CHECK (seats > 0);
SQL

Si le shell continue d’attendre, saisissez la ligne finale SQL manquante. Si vous avez mal saisi le fichier, répétez le bloc entier pour le remplacer. Les noms de contraintes identifient ces règles dans les messages d’erreur de MySQL ; leurs noms exacts ne constituent pas le résultat métier.

< fournit le fichier enregistré comme entrée SQL du client. Appliquez-le une fois :

./db < rules.sql

Aucune sortie n’est attendue en cas de réussite. Si le nom du fichier était incorrect, utilisez ls, corrigez le chemin et réessayez. Si cet unique ALTER comporte une erreur de syntaxe, MySQL le rejette ; corrigez le bloc complet enregistré avant de réessayer. Une fois l’ALTER réussi, ne l’appliquez pas à nouveau : ses contraintes existent déjà. Une erreur de contrainte en double lors d’une deuxième exécution ne signifie pas que vos données ont été perdues.

Lisez la véritable définition. SHOW CREATE TABLE décrit la table actuelle ; \G affiche son résultat verticalement afin de faciliter la lecture de la définition complète :

./db -e "SHOW CREATE TABLE registrations\G"

Repérez les trois colonnes de référence/quantité NOT NULL, la clé UNIQUE à deux colonnes, les deux relations FOREIGN KEY et le CHECK imposant un nombre positif de places. Les noms ou la mise en forme peuvent varier ; les relations doivent correspondre.

Répétez maintenant la demande en double avec le client ordinaire. Cette commande doit échouer avec un refus de la base contenant ERROR 1062. Elle ne doit pas créer de ligne supplémentaire :

./desk book 101 11 1

L’échec est un refus de clé en double, pas une déconnexion du serveur. La liste des inscrits reste utilisable :

./desk roster

Prouvez les deux cas d’identité autorisés dans une transaction de données temporaire sur le véritable poste. Mara peut participer à Bike Patch, et l’autre Alex Kim (103) peut assister à la même séance qu’Alex 102. Une taille de groupe positive supérieure à un est également légitime :

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,12,3),(103,12,5);
SELECT participant_id,session_id,seats FROM registrations ORDER BY participant_id,session_id;
ROLLBACK;"

L’essai montre cinq lignes avec des identités distinctes. L’annulation retire uniquement ses deux nouvelles lignes d’essai. Elle n’annule ni la modification antérieure du schéma ni la demande de Sam déjà validée.

Vérifiez directement trois écritures rejetées représentatives. Chaque commande ci-dessous doit échouer ; ces commandes ne contiennent ni transaction à réparer ni modification réussie à annuler. MySQL rejette l’instruction invalide individuellement. 9999 est absent de la table parente fournie :

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(9999,11,1);"

Vous devez obtenir ERROR 1452, une référence parente inconnue.

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(103,11,NULL);"

Vous devez obtenir ERROR 1048, un nombre de places manquant. Remplacer NULL par 0 enfreindrait le CHECK au lieu de produire une réservation valide.

./db -e "DELETE FROM sessions WHERE id=11;"

Vous devez obtenir ERROR 1451 : Lamp Repair possède toujours des réservations, la séance est donc conservée. Cela montre pourquoi supprimer le parent ou faire disparaître les inscriptions en cascade serait un mauvais raccourci.

Enfin, prouvez qu’une séance sans réservation peut toujours être supprimée dans un essai réversible :

./db --table -e "
START TRANSACTION;
DELETE FROM sessions WHERE id=13;
SELECT id,title FROM sessions ORDER BY id;
ROLLBACK;"

Seules les séances 11 et 12 apparaissent dans cette transaction. L’annulation restaure la séance inutilisée. Examinez l’état final à livrer :

./db --table -e "SELECT id,title FROM sessions ORDER BY id;"

./desk roster

Les trois séances d’origine sont conservées, ainsi qu’exactement trois inscriptions réelles : les deux d’origine et la nouvelle demande de Sam. Gardez rules.sql pour effectuer la même modification structurelle sur un autre poste compatible. Il s’agit d’une migration de schéma à appliquer une seule fois, pas d’un import idempotent. Les connexions par défaut font respecter les clés étrangères ; désactiver leurs vérifications est hors du périmètre de cette tâche. Les contraintes protègent les données lors des écritures ordinaires de l’application, mais ne les protègent pas contre un administrateur qui désactive délibérément leur application.

Résumé

Vous avez conservé un poste d’inscription utilisable tout en transférant ses règles de relation vers de véritables contraintes MySQL. Vous avez préservé les réservations et les notes d’origine, accepté les combinaisons légitimes de personnes homonymes et de participation à plusieurs séances, rejeté les doublons et les références invalides, et empêché la suppression des enregistrements référencés.