Introduction
The shop's pickup column is already expanded, with counter as the legacy default. Historical orders still need their approved choices. Later orders have already arrived: one omitted the new column, and one explicitly chose garden. A broad backfill could overwrite that legitimate newer choice.
You will deliver backfill.sql and tighten.sql, apply the exact approved historical mapping, and preserve both client contracts. Prerequisites are the previous lab's concepts plus basic SELECT/WHERE and UPDATE; the JOIN, aggregate, temporary guard and transaction patterns are taught here. This independent Terminal unit has its own expanded table, data and private MySQL. No file, connection or progress carries over from the previous VM.
Only update orders.pickup_code for the approved historical IDs and tighten that column's type/default/nullability/reference rule. Preserve original headers, item relationships, later pickup choices, approval/point tables, demo table and prepared tools. Only practice orders 301 and 302 below may be added. Requests are sequential in a controlled maintenance window with no competing approval writer. This does not implement online migration, distributed coordination or automatic rollback of DDL. The author's time estimate includes reading and common mistakes, without beginner timing data.
Apply only complete approved historical choices
In this step, you will validate the business mapping, save a transactional backfill, and prove that invalid mappings leave all order values unchanged.
Begin in Terminal and enter this independent workspace:
cd /home/labex/project/pickup-desk
./shop status
./shop map
./shop points
The historical set is legacy_orders: IDs 101, 102 and 103. The approved map says 101 goes to north, 102 to counter and 103 to garden. Their original totals remain 2100, 3400 and 0. The two Sam orders must not be joined by customer name. Later IDs 201 and 202 are outside that historical set: keep 201 at counter and 202 at garden. Their item details must also remain.
Count the approved rows per historical identity. COUNT(*) counts rows. GROUP BY order_id makes one group per ID; HAVING filters groups after counting. <> means not equal. This query returns duplicate or otherwise non-singleton groups:
./db -e "SELECT order_id,COUNT(*) FROM approved_pickups GROUP BY order_id HAVING COUNT(*)<>1;"
An empty result is expected. It cannot reveal a completely missing ID, because a missing row forms no group. A LEFT JOIN preserves every row on its left, with NULL right-hand fields where no match exists. l and m are short table aliases; ON relates exact order IDs. IS NULL selects missing right-side matches:
./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;"
Also require each map code to exist in pickup_points and every map ID to belong to legacy_orders. A NULL code counts as unmatched in this relationship check:
./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;"
All four queries should be empty. The prepared live mapping is valid and read-only. Empty audit output is useful evidence, but a reusable SQL file must also refuse a bad mapping rather than blindly updating its valid subset.
A temporary table belongs to one database connection and disappears when that connection closes. Our small guard stores only integer zero: CHECK(errors=0) rejects a nonzero count, and NOT NULL rejects a missing count. INSERT ... SELECT COUNT(*) inserts the number of violations. If any guard insert fails, the native batch stops before the business UPDATE and closes its connection. Pending InnoDB data changes roll back. ENGINE=InnoDB selects MySQL's transaction-capable storage engine for this temporary table. The temporary table itself is not a rollbackable permanent schema change. Each apply or trial starts a new connection, so no previous temporary table must be reused.
Save this complete file with the quoted here-document introduced in the previous lab. The derived query in parentheses, named duplicates, turns its non-singleton groups into rows that an outer COUNT can count. The UPDATE JOIN changes only orders with an approved exact ID; it never assigns a default to every order. COMMIT makes the data change permanent only after the guards succeeded:
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
First exercise the saved file in disposable native databases. trial executes your file unchanged under an isolated migration identity, with different historical IDs and values. It reports the actual SQL exit status and actual preservation comparison. It never edits the live map or repairs your SQL. In a bad case, native_sql_status is nonzero while accepted is true only when that failure was expected and every business value remained unchanged. This means the trial succeeded at testing a refusal, not that the SQL write succeeded.
./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
The good trial applies every approved choice while preserving the later explicit pickup. Each bad trial reports native CHECK error 3819 and unchanged business facts. These cases respectively remove an approval, duplicate an identity, use an unknown point, use NULL, or try to overwrite a later order. An always-failing file cannot pass the good trial. If a trial is not accepted, read its native stderr and reason, inspect cat backfill.sql, and correct the complete file. Do not alter the prepared tools or mapping to make the test green.
Apply the same original file to the valid live map, then read each affected and protected order:
./shop apply backfill.sql
./shop show 101
./shop show 102
./shop show 103
./shop show 201
./shop show 202
The historical codes are north, counter, garden. Later 201 remains counter and 202 remains garden, with their notes and details intact. A successful default-filled schema alone would not prove those historical choices. Rerunning this pure data backfill from a new connection with the same unchanged approved map produces the same choices; it is not permission to rerun arbitrary one-time ALTER files.
For ./shopp, use ls and correct the tool name. For a syntax error, inspect and replace the saved SQL before applying it again. The prepared batch stops at its first native error and disconnects; do not use --force or an interactive SOURCE workflow that continues past a failed guard. This controlled maintenance window excludes concurrent approval edits; the separate audit queries are not a general concurrent-migration lock protocol.
Tighten the rule without losing either checkout
In this step, you will enforce a valid non-null pickup reference while keeping omitted-column legacy writes and explicit new choices usable.
Continue in Terminal, in /home/labex/project/pickup-desk. The approved backfill is complete; inspect the live definition:
./db -e "SHOW CREATE TABLE orders;"
The column is still nullable, with an explicit counter default. NOT NULL rejects explicit NULL; it does not supply a default for an old client by itself. A foreign key relates pickup_code to an actual unique pickup_points.code, rejecting unknown codes. RESTRICT prevents deletion or identity changes of a used point. These are real database rules, not application dropdown checks.
MODIFY COLUMN restates the complete intended column definition. Retain both VARCHAR(16) and DEFAULT 'counter' while adding NOT NULL; attributes omitted from MODIFY need not survive. The comma places both changes in one ALTER statement. Constraint names identify a rule, but equivalent names are accepted. Keep this DDL outside the backfill transaction: its implicit commit boundary was taught in the previous unit.
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;"
Read pickup_code as NOT NULL with default counter, and the reference to pickup_points(code) with restricted parent changes. All existing values remain. Now create the allowed two practice orders:
./shop old 301 Mina 1600 MUG 1
./shop new 302 Pia 1900 BAG 2 north
./shop show 301
./shop show 302
The actual legacy row stores counter; the actual new row stores north. Their item quantities are 1 and 2, and both transactions committed. The old client still omits pickup_code; it was not secretly rewritten to supply a value.
The next two calls are intentional failures and must exit nonzero. new-null supplies a real SQL NULL rather than omitting the field. Native error 1048 should reject it. The ordinary new client supplying unknown should receive native foreign-key error 1452. They use the same unused ID because neither failure may leave a header or item:
./shop new-null 399 Invalid 100 MUG 1
./shop new 399 Invalid 100 MUG 1 unknown
./shop show 399
./shop status
Show 399 has empty header/items. The three historical choices, later 201/202 and newly created 301/302 remain correct. A native database error is not a successful empty checkout or a connection problem. The prepared client closes the failed transaction; no VM reset is needed. Continue with a read of the existing allowed orders.
Your final result is the reusable backfill/tightening SQL plus the actual correct order system. Assessment reads this live result and tests the original saved SQL and current definition on disposable native resources, including legitimate writes and forbidden parent changes. It does not create an order, apply a migration or repair a missing value in the live shop.
If you accidentally rerun a successful tighten file, the named foreign key may already exist. Inspect SHOW CREATE TABLE and preserve the valid definition; do not drop/recreate orders to make the script rerunnable. A stale file still needs to contain the correct one-time migration from the stated prior phase. The maintenance-window result does not establish lock-free DDL, arbitrary deployment rollback, continuous production availability or administrator-forgery resistance.
Summary
You validated exact historical identities, refused incomplete or ambiguous mappings before changing business values, and applied a transactional backfill without overwriting later client choices. Separate tightening DDL preserved the legacy default while enforcing non-null valid pickup references. Both real checkout versions still work, and invalid writes fail natively.
