仕入先の納品データを点検する

はじめに

MEND & MOSS は家庭用品を修理する店です。2 社の仕入先がどちらも SKU BOLT-1 を使っていますが、扱うボルトは異なります。店では、仕入先の商品名・価格・版番号とともに、独自の棚メモを保管しています。版番号は正の整数で、大きいほど新しい版を表します。

このガイド付き実習では、在庫を変更せずに仕入先の 6 行の CSV をステージングテーブル（在庫に反映する前のデータを保管する通常のテーブル）に取り込み、利用できるレコードと不正なレコードを分ける再利用可能な SQL ビューを作ります。ビューは保存されたクエリであり、行の別コピーではありません。必要な前提知識は基本的なターミナル操作だけです。SQL と SQLite のコマンドは、使用前に説明します。店のレコードはすべて架空のものです。

全編を通して Terminal タブを使います。SQLite と初期データベースは準備済みです。Web サイトを作る作業はありません。元の在庫と deliveries/ 内の両ファイルを変更しないでください。

在庫を読み取り、納品データをステージングテーブルに取り込む

このステップでは、通常の在庫レコードを仕入先に対応付けてから、今回の納品データ全体を別のステージングテーブルに取り込みます。Terminal から始めます。cd はディレクトリを移動するコマンドです。この絶対パスは、誤ったディレクトリに移動した場合の復帰にも使えます。

cd /home/labex/project/supplier-desk

ls はファイルの一覧を表示します。stock.db と deliveries が見えるはずです。

ls

データベースは実際のファイルです。sqlite3 stock.db で SQLite の対話型シェルを開きます。以下の SQL とドットコマンドは、sqlite> プロンプトで実行してください。Linux シェルのプロンプトには入力しません。

sqlite3 stock.db

テーブルは、名前の付いた列に行を保存します。SELECT は読み取る列、FROM はテーブルを指定し、ORDER BY は表示順を一定にします。SQL 文は ; で終わります。2 つのドットコマンドは表示を設定します。.headers on は列名を表示し、.mode column は値を整列させます。ドットコマンドにセミコロンは付けません。

.headers on
.mode column

SELECT supplier,sku,title,price_cents,revision,shelf_note
FROM stock ORDER BY supplier,sku;

在庫は 3 行です。North の N / BOLT-1 は Steel bolt で、120 セント、版番号 2、保管場所は Bin A です。West の W / BOLT-1 は Brass bolt で、180 セント、版番号 1、保管場所は Drawer 4 です。この架空の店では 1 セントが最小通貨単位で、120 は 1.20 を意味します。この実習では整数のセントを使うことで小数の丸めを避けます。N / WASH-2 も引き続き利用できる状態に保つ必要があります。

在庫レコードは supplier,sku の組で識別します。SKU だけでは 2 種類のボルトを区別できません。SELECT * はすべての列を指定します。

SELECT * FROM suppliers ORDER BY code;

N / North Parts と W / West Workshop が表示されるはずです。納品データの仕入先コードは、このテーブルに存在する必要があります。

SQLite は接続ごとに外部キーの関係を検査します。このシェルで検査を有効にします。この設定は在庫データを変更しません。

PRAGMA foreign_keys=ON;

.quit で SQLite を終了し、Linux シェルに戻ります。

.quit

cat はテキストファイルを表示します。実際の CSV を読んでください。

cat deliveries/current.csv

CSV はカンマ区切りの値を意味します。先頭行には 5 列の名前があります。ファイル内の価格と版番号はテキストで、12kits、未登録の仕入先 Z、SKU が空の行も含まれています。これらは意図的に用意した入力の問題であり、推測して修正する指示ではありません。元ファイルは変更しません。提供された CSV の各データ行にはちょうど 5 フィールドがあり、標準の CSV 引用符にも対応しています。

データベースを再度開きます。

sqlite3 stock.db

準備済みの raw_delivery テーブルには、この順番で 5 つの TEXT 列があります。TEXT は不正な数値も含め、届いた内容をそのまま保持します。stock への直接インポートは、点検と業務更新を混同してしまいます。.import --csv は CSV として解析し、--skip 1 は見出し行を飛ばします。

.import --csv --skip 1 deliveries/current.csv raw_delivery

rowid は、このステージングテーブルで SQLite が使う行識別子です。納品データの行番号として便利ですが、商品の識別情報ではありません。取り込んだすべての行を表示します。

.headers on
.mode column

SELECT rowid AS delivery_line,* FROM raw_delivery ORDER BY rowid;

ファイル順に 1～6 行が表示されるはずです。1～3 行目は 2 種類のボルトと新しい補修テープです。4 行目には 12kits、5 行目には仕入先 Z、6 行目には空の SKU があります。AS は、表示する rowid に分かりやすい delivery_line という名前を付けます。

インポートは行を追加します。誤って 2 回インポートした場合は、DELETE FROM raw_delivery; でこのステージングテーブルだけを空にしてから、1 回だけインポートし直します。これがこの単元での通常の復旧方法です。stock を削除したり、VM 全体をリセットしたりしないでください。

DELETE FROM raw_delivery;

.import --csv --skip 1 deliveries/current.csv raw_delivery

最後に Linux シェルに戻ります。

.quit

ステージングテーブルに取り込んだ納品データが最初の成果です。在庫には元の 3 行と棚メモがそのまま残っています。

利用できる行と不正な値を分ける

このステップでは、点検スクリプトを保存し、そのビューを実際に使って納品データを確認します。Terminal の /home/labex/project/supplier-desk から始めます。

店で受け入れるのは、登録済みの仕入先、空でない SKU、空白以外の文字を含む商品名です。価格は 1～6 桁の ASCII 数字で、正の数を表す必要があります。版番号は 1～3 桁の ASCII 数字で、正の数を表す必要があります。先頭のゼロは許可されます。この小さな桁数制限は入力契約であり、任意の数値形式を検証できるという意味ではありません。

CREATE VIEW name AS SELECT ... はクエリに名前を付けて保存します。DROP VIEW IF EXISTS は、その保存済みクエリが存在すれば削除するだけで、元のテーブルは削除しません。WHERE は条件が真の行を残します。BETWEEN 1 AND 6 は両端を含み、NOT BETWEEN は範囲外の値を検出します。

CASE 式は、最初に一致した理由を選びます。WHEN が条件、THEN が結果を指定します。一致がなければ NULL となり、ここではエラーがないことを意味します。IS NULL はその不在を判定します。OR は値を拒否する複数の条件を結びます。NOT IN (SELECT code FROM suppliers) は仕入先の登録有無を調べます。trim は前後の空白を取り除き、length は文字数を数えます。

数値の検査では、GLOB '*[^0-9]*' が ASCII 数字以外の文字を検出します。* は任意の文字列、[^0-9] は数字以外の 1 文字に一致します。空文字列は別の長さ検査で拒否します。CAST(... AS INTEGER) はテキストを整数に変換しますが、数字と長さを検査してから使います。変換だけでは安全ではありません。12kits のようなテキストを 12 として受け入れてはいけません。

4 つのビューを作ります。delivery_check は元の値と理由を保持します。typed_delivery は有効な行だけを変換します。delivery_errors は拒否された行番号と理由を列挙します。delivery_ready は後の更新に適した値を公開します。このスクリプトは再実行できるよう、これらの名前のビューだけを削除します。在庫や元の行は削除しません。

次の Linux コマンドで、この SQL を inspect.sql に保存します。> は保存先ファイルを置き換えます。<<'SQL' は内容をそのまま扱うヒアドキュメントの開始です。最後の、単独行の SQL まで、ブロック全体を貼り付けてください。この cat コマンドを SQLite 内で実行しないでください。

cat > inspect.sql <<'SQL'
DROP VIEW IF EXISTS delivery_ready;
DROP VIEW IF EXISTS delivery_errors;
DROP VIEW IF EXISTS typed_delivery;
DROP VIEW IF EXISTS delivery_check;
CREATE VIEW delivery_check AS
SELECT rowid AS delivery_line, *,
  CASE
    WHEN supplier NOT IN (SELECT code FROM suppliers) OR supplier IS NULL
      THEN 'unknown supplier'
    WHEN sku IS NULL OR sku = '' THEN 'missing SKU'
    WHEN title IS NULL OR trim(title) = '' THEN 'missing title'
    WHEN price_cents IS NULL OR length(price_cents) NOT BETWEEN 1 AND 6
      OR price_cents GLOB '*[^0-9]*' OR CAST(price_cents AS INTEGER) < 1
      THEN 'invalid price'
    WHEN revision IS NULL OR length(revision) NOT BETWEEN 1 AND 3
      OR revision GLOB '*[^0-9]*' OR CAST(revision AS INTEGER) < 1
      THEN 'invalid revision'
  END AS reason
FROM raw_delivery;
CREATE VIEW typed_delivery AS
SELECT delivery_line, supplier, sku, title,
       CAST(price_cents AS INTEGER) AS price_cents,
       CAST(revision AS INTEGER) AS revision
FROM delivery_check WHERE reason IS NULL;
CREATE VIEW delivery_errors AS
SELECT delivery_line, reason FROM delivery_check WHERE reason IS NOT NULL;
CREATE VIEW delivery_ready AS
SELECT supplier,sku,title,price_cents,revision FROM typed_delivery;
SQL

ターミナルが入力待ちのままなら、終了マーカーが不足しています。SQL だけの行を入力してください。行を打ち間違えた場合は、ブロック全体を繰り返してスクリプトを置き換えます。エディターやリセットは不要です。

Linux の入力リダイレクト < を使ってスクリプトを実行します。-bail は、後続の文を続けず、最初のエラーで SQLite を停止させます。成功時には何も出力されません。

sqlite3 -bail stock.db < inspect.sql

通常のファイル名や構文の誤りでは、ls で確認し、ステップ 1 の絶対パスの cd で戻り、ブロック全体を使って inspect.sql を修正して再実行します。在庫は変更されていません。

SQLite は、データベースのパスの後に引用符で囲んだ SQL 文を 1 つ指定する方法にも対応しています。-header -column はこのコマンドだけの表示を読みやすくします。引用符により、空白とセミコロンを 1 つのシェル引数にまとめます。

sqlite3 -header -column stock.db 'SELECT * FROM delivery_errors ORDER BY delivery_line;'

次の結果を確認します。

delivery_line  reason
-------------  ----------------
4              invalid price
5              unknown supplier
6              missing SKU

理由は納品データの行に対応しているため、拒否された項目を元データまでたどれます。元の値は修正も削除もされていません。

sqlite3 -header -column stock.db 'SELECT * FROM delivery_ready ORDER BY supplier,sku;'

受け入れ可能な 3 行を確認します。North のボルトは 135 セント・版番号 3、補修テープは 450・版番号 1、West のボルトは 190・版番号 2 です。2 種類のボルトは区別されたままです。typeof は SQLite の実際の値の型を返します。

sqlite3 -header -column stock.db 'SELECT sku,typeof(price_cents),typeof(revision) FROM delivery_ready ORDER BY supplier,sku;'

両方の型の列が integer になるはずです。最後に実際の在庫を読みます。

sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'

元の値と棚メモがそのまま残っています。再利用できる受入点検を準備しましたが、仕入先の変更はまだ適用していません。次の独立したガイド付き実習は、新しいデータベースと、より多くの行を取り込んだ納品データから始まります。

まとめ

実際の SQLite の行を使って、仕入先の識別情報、納品データの値、店の在庫を対応付けました。受け入れられたレコードと拒否されたレコードの内容を読み取り、元のデータを保全し、次の納品にも使える SQL を保存しました。