はじめに
MEND & MOSS は仕入先の更新を適用する準備ができました。受入デスクには、取り込み済みの納品データと、前の実習で扱った基本点検がすでにあります。これは独立した環境で、前の VM のファイルは必要ありません。
一見使えそうな行の中にも、同じ版について内容が食い違うものがあります。遅れて届くものもあります。点検を拡張して矛盾を拒否した後、店独自の棚メモを保ちながら、新しい版だけに進める更新スクリプトを保存します。最初のガイド付き実習の基本 SQL を前提とし、競合の検査、UPSERT、トランザクションはここで説明します。
全編を通して Terminal を使います。inspect.sql の変更、apply.sql の作成、raw_delivery の再読み込みを行えます。元の在庫の識別情報と棚メモ、仕入先、両方の CSV ファイルは保全してください。仕入先が管理する商品名・価格・版番号の値は、以下のルールに従って新しくできます。
内容が矛盾する版を拒否する
このステップでは、同一行の繰り返しと、版の内容の矛盾を区別します。Terminal から始めます。
cd /home/labex/project/supplier-desk
この単元の実際の納品データと、提供された点検スクリプトを読みます。
cat deliveries/current.csv
cat inspect.sql
納品データの 10 行は取り込み済みです。3 行目と 7 行目は、同じ補修テープの識別情報、商品名、価格、版番号を繰り返しています。同じ事実が繰り返されても新商品にはなりません。8 行目と 9 行目はどちらも N / PIN-2 の版番号 2 ですが、価格が異なります。どちらも採用してよい答えではありません。10 行目の N / WASH-2 の版番号 1 は、在庫にある版番号 1 と価格が異なります。1 つの版番号が 2 つの異なる内容を意味してはいけません。
最初の実習のルールは引き続き適用されます。拒否理由が 2 つ加わります。それ以外は有効な行で、仕入先・SKU・版番号が同じなのに商品名または整数価格が異なる場合は delivery conflict、その版の内容が現在の在庫と異なる場合は stock conflict です。両方に該当する場合は、納品データ内の矛盾を優先します。同一内容の繰り返しは受け入れます。古い版は点検では受け入れられますが、新しい在庫を上書きしてはいけません。
変更前に元の在庫を確認します。
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
基本点検を実行し、ビューが存在する状態にします。
sqlite3 -bail stock.db < inspect.sql
編集前に、提供されたスクリプトの復旧用コピーを作ります。cp は、元を変更せず、最初のパスから 2 番目のパスへコピーします。
cp inspect.sql inspect-start.sql
次の SQL では、別名 t、other、s、e が、それぞれ納品行、別の納品行、在庫行、エラー行を表します。EXISTS (SELECT 1 ...) は一致する行の有無を調べます。行の内容は返さないため、1 は単なる仮の値です。AND はキーの各フィールドがすべて一致することを要求します。<> は不一致を意味します。NOT EXISTS はエラーのない行を残します。UNION ALL は、個別の行の識別情報を失わずに、基本エラーと矛盾をまとめます。
拡張部分を inspect.sql に追記します。>> は置換ではなく追記なので、上にある提供済みの基本定義を残してください。このブロックは 1 回だけ貼り付けます。各拡張は最後の 2 つのビューだけを再作成するため、ここでは繰り返しても害はありませんが、不要な重複は避けてください。
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
保存したスクリプト全体を再実行します。
sqlite3 -bail stock.db < inspect.sql
sqlite3 -header -column stock.db 'SELECT * FROM delivery_errors ORDER BY delivery_line;'
6 件のエラーを確認します。4 行目は invalid price、5 行目は unknown supplier、6 行目は missing SKU、8・9 行目は delivery conflict、10 行目は stock conflict です。補修テープは両方の元の行が受け入れ可能なので、点検結果に 2 回現れます。同じ行の組によって、価格を倍にしたり、在庫を 2 商品にしたりしてはいけません。
sqlite3 -header -column stock.db 'SELECT * FROM delivery_ready ORDER BY supplier,sku;'
受け入れた納品行は 4 行で、3 つの商品を表します。在庫はまだ変更されていません。基本エラーの 3 件しか表示されない場合は、追記ブロックの存在を確認してスクリプト全体を再実行してください。追記部分に構文エラーがあれば、cp inspect-start.sql inspect.sql で提供された基本部分を復元し、完全な追記ブロックを再度追加して実行します。この復旧では在庫も両 CSV ファイルも変更しません。
新しい値を適用し、同じ納品を安全に再処理する
このステップでは、店独自の棚メモを変えずに受け入れ可能な行を適用します。その後、同じ納品の再処理や古い納品の受入で、作業結果が元に戻らないことを確かめます。同じ Terminal のディレクトリから始めます。
stock テーブルは (supplier,sku) を主キーとしており、この 2 つのフィールドで 1 行を識別します。UPSERT は、未登録の識別情報を挿入するか、既存のキーとの競合を処理します。excluded は、挿入候補の受信行を指します。更新するには、その版番号が現在の在庫の版番号より大きい必要があります。提供済みの shelf_note 列は店が管理する情報なので、入力値にも更新の代入先にも含めません。新しい在庫には、準備された空のメモの既定値が入ります。
BEGIN はトランザクションを開始し、COMMIT は一まとまりとして保存します。エラーが起きると、この -bail コマンドは停止します。接続が閉じられると、未完了のトランザクションはロールバックされます。この例は、準備済みの小さなデータベースを更新するもので、並行同期やバックアップの仕組みではありません。
SELECT は実際の受入済みビューを読みます。WHERE true はその全行を残しつつ、後続の ON CONFLICT を SQLite が結合条件と混同するのを防ぎます。完全なスクリプトを保存します。
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
適用に使う接続で外部キーを有効にします。-cmd は引用符内の接続設定を最初に実行します。この設定は全体には保存されません。その後、< apply.sql が SQL を渡します。
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
在庫は次の 4 行になるはずです。
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
ワッシャーは、矛盾する版が拒否されたため 40 のままです。ピン、不正なキャップ、未登録の仕入先、空の SKU の在庫は追加されていません。元の両ボルトのメモも残ります。補修テープは納品データには 2 回現れましたが、在庫は 1 行です。
同じスクリプトを繰り返します。
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
結果は変わりません。版番号の条件により、同じデータの再取り込みを安全に扱えます。価格を加算したり、店独自のメモを置き換えたりしません。
次に、使う前に古い納品データを読みます。
cat deliveries/older.csv
これはボルトの古い版です。対話型シェルでステージングテーブルだけを再読み込みします。
sqlite3 stock.db
DELETE FROM raw_delivery;
.import --csv --skip 1 deliveries/older.csv raw_delivery
.quit
ビューはテーブルの現在の行を自動的に読みます。変更していない更新スクリプトを実行し、在庫を確認します。
sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON;' stock.db < apply.sql
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
4 行すべての値が変わらず残ります。点検は古くても有効な数値を受け入れますが、適用によって在庫を古い状態には戻せません。これは、不正な納品や内容が矛盾する納品とは異なります。
今日の納品データを再度点検できる状態で作業を終えます。ステージングテーブルを空にしても在庫は消えません。
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;'
拒否された 6 行は、理由とともに引き続き見えます。両スクリプトを保管してください。inspect.sql は互換性のある別の納品を点検する方法、apply.sql はその安全な行を使って在庫を新しくする方法を記述しています。パスの間違いは絶対パスの cd で復帰でき、スクリプトの入力ミスは完全なブロックで置き換えられます。つづりの間違いを直すために stock.db を再作成したり、レコードを削除したりしないでください。
まとめ
実際の SQLite の行を使って、仕入先の識別情報、納品データの値、店の在庫を対応付けました。受け入れられたレコードと拒否されたレコードの内容を読み取り、元のデータを保全し、次の納品にも使える SQL を保存しました。
