Introduction
MEND & MOSS is ready to apply supplier updates. Its receiving desk already has a staged delivery and the basic inspection from the preceding lab. This is an independent environment: no files from a previous VM are needed.
Some usable-looking lines disagree about the same edition. Others arrive late. You will extend the inspection to reject those contradictions, then save an update script that advances only newer revisions while preserving the shop's local shelf notes. Basic SQL from the first Guided Lab is assumed; conflict checks, UPSERT and transactions are explained here.
Use Terminal throughout. You may change inspect.sql, create apply.sql, and reload raw_delivery. Keep original stock identities and shelf notes, suppliers and both CSV files. Supplier-owned title/price/revision values may advance according to the rules below.
Reject Contradictory Editions
In this step, you will distinguish a repeated identical line from an edition conflict. Begin in Terminal:
cd /home/labex/project/supplier-desk
Read this unit's actual delivery and supplied inspection script:
cat deliveries/current.csv
cat inspect.sql
The ten delivery lines are already staged. Lines 3 and 7 repeat the same repair-tape identity, title, price and revision; repeating a fact does not make it a new product. Lines 8 and 9 both describe N / PIN-2, revision 2, but disagree about its price. Neither is an approved answer. Line 10 gives N / WASH-2, revision 1, a different price from stock's existing revision 1. An edition number cannot mean two different contents.
The first lab's rules still apply. There are two additional rejection reasons: delivery conflict when otherwise valid rows have the same supplier/SKU/revision but different title or integer price, and stock conflict when that edition disagrees with the current stock. A delivery conflict takes precedence if both apply. Identical repetitions are accepted. Lower editions may be accepted for inspection but must never overwrite newer stock.
Look at the original stock before changing it:
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
Run the basic inspection so its views exist:
sqlite3 -bail stock.db < inspect.sql
Before editing, make a recovery copy of the supplied script. cp copies the first path to the second without changing the original:
cp inspect.sql inspect-start.sql
The next SQL uses aliases t, other, s and e to name a delivery row, another delivery row, a stock row and an error row. EXISTS (SELECT 1 ...) asks whether any matching row exists; the 1 is only a placeholder because the row's contents are not returned. AND requires all key fields to match. <> means different. NOT EXISTS keeps rows without an error. UNION ALL combines base errors and conflicts without losing separate line identities.
Append the extension to inspect.sql. >> appends instead of replacing, so retain the supplied basic definitions above it. Paste this block once. Repeating it is harmless here because each extension recreates only the two final views, but avoid unnecessary duplicate text:
cat >> inspect.sql <<'SQL'
DROP VIEW IF EXISTS delivery_ready;
DROP VIEW IF EXISTS delivery_errors;
CREATE VIEW delivery_errors AS
SELECT delivery_line,reason FROM delivery_check WHERE reason IS NOT NULL
UNION ALL
SELECT t.delivery_line,
CASE WHEN EXISTS (
SELECT 1 FROM typed_delivery AS other
WHERE other.supplier=t.supplier AND other.sku=t.sku
AND other.revision=t.revision
AND (other.title<>t.title OR other.price_cents<>t.price_cents)
) THEN 'delivery conflict' ELSE 'stock conflict' END
FROM typed_delivery AS t
WHERE EXISTS (
SELECT 1 FROM typed_delivery AS other
WHERE other.supplier=t.supplier AND other.sku=t.sku
AND other.revision=t.revision
AND (other.title<>t.title OR other.price_cents<>t.price_cents)
) OR EXISTS (
SELECT 1 FROM stock AS s
WHERE s.supplier=t.supplier AND s.sku=t.sku AND s.revision=t.revision
AND (s.title<>t.title OR s.price_cents<>t.price_cents)
);
CREATE VIEW delivery_ready AS
SELECT supplier,sku,title,price_cents,revision FROM typed_delivery AS t
WHERE NOT EXISTS (
SELECT 1 FROM delivery_errors AS e WHERE e.delivery_line=t.delivery_line
);
SQL
Run the whole saved script again:
sqlite3 -bail stock.db < inspect.sql
sqlite3 -header -column stock.db 'SELECT * FROM delivery_errors ORDER BY delivery_line;'
Expect six errors: line 4 invalid price, 5 unknown supplier, 6 missing SKU, 8 and 9 delivery conflict, and 10 stock conflict. Repair tape appears twice in the accepted review, because both source lines are acceptable; a pair of identical lines must not double a price or create two stock items.
sqlite3 -header -column stock.db 'SELECT * FROM delivery_ready ORDER BY supplier,sku;'
There are four accepted delivery rows representing three product identities. Stock is still unchanged. If you see only the three basic errors, check that the append block is present and rerun the full script. For a syntax error in the appended block, restore the supplied base with cp inspect-start.sql inspect.sql, then repeat the complete append block and run the script again. This recovery leaves stock and both CSV files unchanged.
Apply Newer Values and Safely Repeat the Delivery
In this step, you will apply the acceptable rows without changing local shelf notes, then prove that repeating the same delivery or receiving an older one does not undo your work. Begin in the same Terminal directory.
The stock table has a primary key on (supplier,sku): those two fields identify one row. An UPSERT inserts a missing identity or handles a conflict with that existing key. excluded refers to the proposed incoming row. Its revision must be greater than the current stock revision before an update occurs. The supplied shelf_note column belongs to the shop: leave it out of both the incoming values and the update assignments. A new stock identity receives the prepared empty-note default.
BEGIN starts a transaction and COMMIT saves it as one unit. An error stops this -bail command; closing its connection rolls an unfinished transaction back. This example updates a small, prepared database; it is not a concurrent synchronization or backup system.
The SELECT reads the actual accepted view. Its WHERE true retains all those rows while preventing SQLite from confusing the following ON CONFLICT with a join condition. Save the complete script:
cat > apply.sql <<'SQL'
BEGIN;
INSERT INTO stock(supplier,sku,title,price_cents,revision)
SELECT supplier,sku,title,price_cents,revision
FROM delivery_ready WHERE true
ON CONFLICT(supplier,sku) DO UPDATE SET
title=excluded.title,
price_cents=excluded.price_cents,
revision=excluded.revision
WHERE excluded.revision>stock.revision;
COMMIT;
SQL
Enable foreign keys on the connection used to apply it. -cmd runs the quoted connection setting first; it is not saved globally. Then < apply.sql supplies the SQL:
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
Expect four stock rows:
supplier sku title price_cents revision shelf_note
-------- ------ ----------- ----------- -------- ----------
N BOLT-1 Steel bolt 135 3 Bin A
N TAPE-4 Repair tape 450 1
N WASH-2 Washer 40 1 Bin C
W BOLT-1 Brass bolt 190 2 Drawer 4
The washer stays at 40 because its contradictory edition was rejected. No pin, bad cap, unknown supplier or empty-SKU stock item was added. Both original bolt notes survive. Repair tape has one row even though it appeared twice in the delivery.
Repeat the same script:
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
The result stays the same. The revision condition makes an identical repeated import safe; it does not add the price or replace the local note.
Now read the older delivery before using it:
cat deliveries/older.csv
These are older bolt editions. Reload only the staging table using the interactive shell:
sqlite3 stock.db
DELETE FROM raw_delivery;
.import --csv --skip 1 deliveries/older.csv raw_delivery
.quit
The views read the table's current rows automatically. Run the unchanged update, then inspect stock:
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
All four rows still have the same values. Inspection accepts older valid numbers, but applying them cannot roll stock backward. This is different from an invalid or contradictory delivery.
Leave the desk ready to review today's delivery again. Clearing staging does not clear stock:
sqlite3 stock.db
DELETE FROM raw_delivery;
.import --csv --skip 1 deliveries/current.csv raw_delivery
.quit
sqlite3 -header -column stock.db 'SELECT * FROM delivery_errors ORDER BY delivery_line;'
The six rejected lines remain visible with their reasons. Preserve both scripts: inspect.sql describes how another compatible delivery is reviewed; apply.sql describes how its safe rows advance stock. A wrong path can be recovered with the absolute cd; a mistyped script can be replaced from the complete block. Do not recreate stock.db or delete records to repair a spelling mistake.
Summary
You used real SQLite rows to connect supplier identities, delivery values and local stock. You interpreted the actual accepted and rejected records, preserved the original sources, and saved reusable SQL for the next delivery.
