Introduction
La colonne de retrait du magasin est déjà étendue, avec counter comme valeur par défaut pour l’ancien client. Les commandes historiques doivent encore recevoir leurs choix approuvés. Des commandes ultérieures sont déjà arrivées : l’une a omis la nouvelle colonne et l’autre a explicitement choisi garden. Une mise à jour historique trop large pourrait écraser ce choix plus récent légitime.
Vous livrerez backfill.sql et tighten.sql, appliquerez la correspondance historique approuvée exacte et préserverez les contrats des deux clients. Les prérequis sont les concepts du TP précédent ainsi que les bases de SELECT/WHERE et UPDATE ; les modèles de JOIN, d’agrégat, de contrôle temporaire et de transaction sont enseignés ici. Cette unité indépendante dans Terminal possède sa propre table étendue, ses données et son MySQL privé. Aucun fichier, aucune connexion ni aucune progression ne provient de la VM précédente.
Mettez à jour uniquement orders.pickup_code pour les identifiants historiques approuvés et renforcez la règle de type/valeur par défaut/NULL/référence de cette colonne. Préservez les en-têtes d’origine, les relations avec les articles, les choix de retrait ultérieurs, les tables d’approbation/points, la table de démonstration et les outils préparés. Seules les commandes d’entraînement 301 et 302 ci-dessous peuvent être ajoutées. Les demandes sont séquentielles dans une fenêtre de maintenance contrôlée sans processus concurrent modifiant les approbations. Cela ne met pas en œuvre une migration en ligne, une coordination distribuée ni une annulation automatique du DDL. La durée estimée par l’auteur comprend la lecture et les erreurs courantes, sans données chronométrées auprès de débutants.
Appliquer uniquement les choix historiques approuvés complets
Dans cette étape, vous validerez la correspondance métier, enregistrerez une mise à jour historique transactionnelle et prouverez que les correspondances invalides laissent toutes les valeurs de commande inchangées.
Commencez dans Terminal et entrez dans cet espace de travail indépendant :
cd /home/labex/project/pickup-desk
./shop status
./shop map
./shop points
L’ensemble historique est legacy_orders : les identifiants 101, 102 et 103. La correspondance approuvée indique 101 vers north, 102 vers counter et 103 vers garden. Leurs totaux d’origine restent 2100, 3400 et 0. Les deux commandes Sam ne doivent pas être jointes par nom de client. Les identifiants ultérieurs 201 et 202 sont hors de cet ensemble historique : gardez 201 à counter et 202 à garden. Leurs détails d’articles doivent également être conservés.
Comptez les lignes approuvées par identité historique. COUNT(*) compte les lignes. GROUP BY order_id crée un groupe par identifiant ; HAVING filtre les groupes après le comptage. <> signifie différent. Cette requête renvoie les groupes en double ou contenant autrement un nombre de lignes différent de un :
./db -e "SELECT order_id,COUNT(*) FROM approved_pickups GROUP BY order_id HAVING COUNT(*)<>1;"
Un résultat vide est attendu. Il ne peut pas révéler un identifiant complètement absent, car une ligne manquante ne forme aucun groupe. Un LEFT JOIN préserve chaque ligne à sa gauche, avec des champs de droite NULL lorsqu’aucune correspondance n’existe. l et m sont de courts alias de table ; ON relie les identifiants de commande exacts. IS NULL sélectionne les correspondances manquantes à droite :
./db -e "SELECT l.order_id FROM legacy_orders l LEFT JOIN approved_pickups m ON m.order_id=l.order_id WHERE m.order_id IS NULL;"
Exigez aussi que chaque code de la correspondance existe dans pickup_points et que chaque identifiant de la correspondance appartienne à legacy_orders. Un code NULL compte comme non correspondant dans cette vérification de relation :
./db -e "SELECT m.order_id,m.pickup_code FROM approved_pickups m LEFT JOIN pickup_points p ON p.code=m.pickup_code WHERE p.code IS NULL;"
./db -e "SELECT m.order_id FROM approved_pickups m LEFT JOIN legacy_orders l ON l.order_id=m.order_id WHERE l.order_id IS NULL;"
Les quatre requêtes doivent être vides. La correspondance active préparée est valide et en lecture seule. Une sortie d’audit vide est une observation utile, mais un fichier SQL réutilisable doit aussi refuser une mauvaise correspondance au lieu de mettre aveuglément à jour son sous-ensemble valide.
Une table temporaire appartient à une seule connexion de base de données et disparaît à sa fermeture. Notre petit contrôle ne stocke que l’entier zéro : CHECK(errors=0) rejette un compte non nul et NOT NULL rejette un compte manquant. INSERT ... SELECT COUNT(*) insère le nombre de violations. Si une insertion de contrôle échoue, le lot natif s’arrête avant l’UPDATE métier et ferme sa connexion. Les modifications de données InnoDB en attente sont annulées. ENGINE=InnoDB sélectionne le moteur de stockage MySQL transactionnel pour cette table temporaire. La table temporaire elle-même n’est pas un changement de schéma permanent annulable. Chaque apply ou trial démarre une nouvelle connexion ; aucune table temporaire précédente ne doit donc être réutilisée.
Enregistrez ce fichier complet avec le here-document entre guillemets présenté au TP précédent. La requête dérivée entre parenthèses, nommée duplicates, transforme ses groupes ne contenant pas exactement une ligne en lignes qu’un COUNT externe peut compter. L’UPDATE JOIN modifie uniquement les commandes possédant un identifiant approuvé exact ; il n’affecte jamais une valeur par défaut à toutes les commandes. COMMIT rend la modification des données permanente uniquement après la réussite des contrôles :
cat > backfill.sql <<'SQL'
CREATE TEMPORARY TABLE map_guard (
errors INT NOT NULL CHECK(errors=0)
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO map_guard
SELECT COUNT(*) FROM legacy_orders l
LEFT JOIN approved_pickups m ON m.order_id=l.order_id
WHERE m.order_id IS NULL;
INSERT INTO map_guard
SELECT COUNT(*) FROM (
SELECT order_id FROM approved_pickups
GROUP BY order_id HAVING COUNT(*)<>1
) duplicates;
INSERT INTO map_guard
SELECT COUNT(*) FROM approved_pickups m
LEFT JOIN pickup_points p ON p.code=m.pickup_code
WHERE p.code IS NULL;
INSERT INTO map_guard
SELECT COUNT(*) FROM approved_pickups m
LEFT JOIN legacy_orders l ON l.order_id=m.order_id
WHERE l.order_id IS NULL;
UPDATE orders o
JOIN approved_pickups m ON m.order_id=o.order_id
SET o.pickup_code=m.pickup_code;
COMMIT;
SQL
Testez d’abord le fichier enregistré dans des bases natives jetables. trial exécute votre fichier inchangé sous une identité de migration isolée, avec des identifiants et valeurs historiques différents. Il indique le véritable statut de sortie SQL et la véritable comparaison de préservation. Il ne modifie jamais la correspondance active et ne répare pas votre SQL. Dans un mauvais cas, native_sql_status est non nul tandis que accepted est true uniquement lorsque cet échec était attendu et que toutes les valeurs métier sont restées inchangées. Cela signifie que l’essai a réussi à tester un refus, pas que l’écriture SQL a réussi.
./shop trial backfill.sql good
./shop trial backfill.sql missing
./shop trial backfill.sql duplicate
./shop trial backfill.sql unknown
./shop trial backfill.sql null
./shop trial backfill.sql extra
Le bon essai applique chaque choix approuvé tout en préservant le retrait explicite ultérieur. Chaque mauvais essai indique l’erreur CHECK native 3819 et des faits métier inchangés. Ces cas retirent respectivement une approbation, dupliquent une identité, utilisent un point inconnu, utilisent NULL ou essaient d’écraser une commande ultérieure. Un fichier qui échoue toujours ne peut pas réussir le bon essai. Si un essai n’est pas accepté, lisez son stderr natif et son motif, examinez cat backfill.sql et corrigez le fichier complet. Ne modifiez pas les outils préparés ni la correspondance pour faire passer le test au vert.
Appliquez le même fichier d’origine à la correspondance active valide, puis lisez chaque commande affectée et protégée :
./shop apply backfill.sql
./shop show 101
./shop show 102
./shop show 103
./shop show 201
./shop show 202
Les codes historiques sont north, counter, garden. La commande ultérieure 201 reste counter et 202 reste garden, avec leurs notes et détails intacts. Un schéma rempli avec succès par des valeurs par défaut ne prouverait pas à lui seul ces choix historiques. Réexécuter cette mise à jour de données pures depuis une nouvelle connexion avec la même correspondance approuvée inchangée produit les mêmes choix ; ce n’est pas une autorisation de relancer des fichiers ALTER quelconques prévus pour une seule exécution.
Pour ./shopp, utilisez ls et corrigez le nom de l’outil. Pour une erreur de syntaxe, examinez et remplacez le SQL enregistré avant de l’appliquer à nouveau. Le lot préparé s’arrête à sa première erreur native et se déconnecte ; n’utilisez pas --force ni un processus SOURCE interactif qui continue après l’échec d’un contrôle. Cette fenêtre de maintenance contrôlée exclut les modifications concurrentes d’approbations ; les requêtes d’audit séparées ne constituent pas un protocole général de verrouillage de migration concurrente.
Renforcer la règle sans perdre aucun des deux clients
Dans cette étape, vous imposerez une référence de retrait valide et non NULL tout en gardant utilisables les écritures de l’ancien client omettant la colonne et les nouveaux choix explicites.
Continuez dans Terminal, dans /home/labex/project/pickup-desk. La mise à jour historique approuvée est terminée ; examinez la définition active :
./db -e "SHOW CREATE TABLE orders;"
La colonne autorise toujours NULL, avec une valeur par défaut explicite counter. NOT NULL rejette un NULL explicite ; il ne fournit pas à lui seul une valeur par défaut pour un ancien client. Une clé étrangère relie pickup_code à une véritable valeur unique de pickup_points.code, rejetant les codes inconnus. RESTRICT empêche la suppression ou la modification d’identité d’un point utilisé. Ce sont de véritables règles de base de données, pas des vérifications d’une liste déroulante d’application.
MODIFY COLUMN réénonce la définition complète voulue de la colonne. Conservez à la fois VARCHAR(16) et DEFAULT 'counter' en ajoutant NOT NULL ; les attributs omis de MODIFY ne sont pas nécessairement conservés. La virgule place les deux changements dans une seule instruction ALTER. Les noms de contrainte identifient une règle, mais les noms équivalents sont acceptés. Gardez ce DDL hors de la transaction de mise à jour historique : sa frontière de commit implicite a été enseignée dans l’unité précédente.
cat > tighten.sql <<'SQL'
ALTER TABLE orders
MODIFY COLUMN pickup_code VARCHAR(16) NOT NULL DEFAULT 'counter',
ADD CONSTRAINT order_pickup_fk FOREIGN KEY(pickup_code)
REFERENCES pickup_points(code)
ON DELETE RESTRICT ON UPDATE RESTRICT;
SQL
./shop apply tighten.sql
./db -e "SHOW COLUMNS FROM orders;"
./db -e "SHOW CREATE TABLE orders;"
Lisez pickup_code comme NOT NULL avec la valeur par défaut counter, et la référence à pickup_points(code) avec restrictions sur les modifications du parent. Toutes les valeurs existantes sont conservées. Créez maintenant les deux commandes d’entraînement autorisées :
./shop old 301 Mina 1600 MUG 1
./shop new 302 Pia 1900 BAG 2 north
./shop show 301
./shop show 302
La véritable ligne de l’ancien client stocke counter ; la véritable nouvelle ligne stocke north. Leurs quantités d’articles sont 1 et 2 et les deux transactions ont été validées. L’ancien client omet toujours pickup_code ; il n’a pas été secrètement réécrit pour fournir une valeur.
Les deux appels suivants sont des échecs intentionnels et doivent se terminer avec un code non nul. new-null fournit un véritable NULL SQL au lieu d’omettre le champ. L’erreur native 1048 doit le rejeter. Le nouveau client ordinaire fournissant unknown doit recevoir l’erreur native de clé étrangère 1452. Ils utilisent le même identifiant inutilisé, car aucun échec ne doit laisser un en-tête ou un article :
./shop new-null 399 Invalid 100 MUG 1
./shop new 399 Invalid 100 MUG 1 unknown
./shop show 399
./shop status
Le résultat de show pour 399 contient des listes d’en-têtes et d’articles vides. Les trois choix historiques, les commandes ultérieures 201/202 et les nouvelles 301/302 restent corrects. Une erreur native de base de données n’est pas une commande vide réussie ni un problème de connexion. Le client préparé ferme la transaction en échec ; aucune réinitialisation de VM n’est nécessaire. Continuez par une lecture des commandes autorisées existantes.
Votre résultat final est le SQL réutilisable de mise à jour historique/renforcement ainsi que le véritable système de commandes correct. L’évaluation lit ce résultat actif et teste le SQL d’origine enregistré et la définition actuelle sur des ressources natives jetables, y compris des écritures légitimes et des modifications interdites du parent. Elle ne crée pas de commande, n’applique pas de migration et ne répare pas de valeur manquante dans le magasin actif.
Si vous relancez accidentellement un fichier de renforcement réussi, la clé étrangère nommée peut déjà exister. Examinez SHOW CREATE TABLE et préservez la définition valide ; ne supprimez/recréez pas orders pour rendre le script réexécutable. Un fichier obsolète doit toujours contenir la bonne migration à exécuter une fois depuis la phase précédente indiquée. Le résultat de la fenêtre de maintenance n’établit ni DDL sans verrou, ni annulation de déploiement quelconque, ni disponibilité continue en production, ni résistance à la falsification par un administrateur.
Résumé
Vous avez validé les identités historiques exactes, refusé les correspondances incomplètes ou ambiguës avant de changer les valeurs métier et appliqué un renseignement transactionnel de l’historique sans écraser les choix ultérieurs des clients. Un DDL de renforcement séparé a préservé la valeur par défaut de l’ancien client tout en imposant des références de retrait valides et non NULL. Les deux véritables versions du client de commande fonctionnent toujours et les écritures invalides échouent nativement.
