공급업체 납품 데이터 검사하기

소개

MEND & MOSS는 생활용품을 수리하는 가게입니다. 두 공급업체가 모두 SKU BOLT-1을 사용하지만 서로 다른 볼트를 판매합니다. 매장은 각 공급업체의 상품명, 가격, 리비전과 함께 자체 선반 메모를 보관합니다. 리비전은 양의 판 번호이며, 숫자가 클수록 최신입니다.

이 가이드 실습에서는 재고를 변경하지 않고 여섯 데이터 행으로 된 공급업체 CSV를 임시 적재한 다음, 사용 가능한 레코드와 잘못된 레코드를 구분하는 재사용 가능한 SQL 뷰를 만듭니다. 뷰는 저장된 쿼리이며, 행을 한 벌 더 복사한 것이 아닙니다. 기본적인 터미널 탐색만 알면 됩니다. SQL과 SQLite 명령은 사용하기 전에 설명합니다. 모든 매장 레코드는 합성 데이터입니다.

전체 과정에서 Terminal 탭을 사용하세요. SQLite와 초기 데이터베이스는 준비되어 있습니다. 웹사이트를 만들 필요는 없습니다. 원래 재고와 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 문은 ;로 끝납니다. 두 점 명령은 표시 방식을 제어합니다. .headers on은 열 이름을 표시하고, .mode column은 값을 열에 맞춰 정렬합니다. 점 명령에는 세미콜론을 붙이지 않습니다:

.headers on
.mode column

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

재고에는 세 행이 있습니다. 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만으로는 두 볼트를 구분할 수 없습니다. 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는 쉼표로 구분된 값을 뜻합니다. 첫 줄에는 다섯 열의 이름이 있습니다. 파일은 가격과 리비전을 텍스트로 담고 있으며, 12kits, 알 수 없는 공급업체 Z, SKU가 빈 행도 포함합니다. 이는 의도적으로 넣은 입력 문제이며, 수정값을 추측하라는 지시가 아닙니다. 원본 파일은 변경하지 않습니다. 제공된 CSV의 각 데이터 행에는 정확히 다섯 필드가 있으며, 표준 CSV 따옴표 처리를 지원합니다.

데이터베이스를 다시 여세요:

sqlite3 stock.db

준비된 raw_delivery 테이블에는 이 순서대로 다섯 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번 행은 두 볼트와 새 수리용 테이프입니다. 4번 행에는 12kits, 5번 행에는 공급업체 Z, 6번 행에는 빈 SKU가 있습니다. AS는 표시되는 rowid에 더 명확한 이름인 delivery_line을 붙입니다.

가져오기는 행을 추가합니다. 실수로 두 번 가져왔다면 DELETE FROM raw_delivery;로 이 임시 적재 테이블만 비운 다음 한 번만 다시 가져오세요. 이것이 이 단원에서 일반적인 복구 방법입니다. stock을 삭제하거나 VM 전체를 초기화하지 마세요:

DELETE FROM raw_delivery;

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

Linux 셸로 돌아와 마무리하세요:

.quit

임시 적재된 납품 데이터가 첫 번째 결과물입니다. 재고에는 원래 세 행과 선반 메모가 그대로 남아 있습니다.

사용 가능한 행과 잘못된 값 구분하기

이 단계에서는 검사 스크립트를 저장하고 실제 뷰를 사용해 납품 데이터를 검토합니다. Terminal의 /home/labex/project/supplier-desk에서 시작하세요.

매장은 등록된 공급업체, 비어 있지 않은 SKU, 공백 이외의 문자가 들어 있는 상품명을 허용합니다. 가격은 ASCII 숫자 1–6자리로 구성되고 양수를 나타내야 합니다. 리비전은 ASCII 숫자 1–3자리로 구성되고 양수를 나타내야 합니다. 앞에 붙는 0은 허용됩니다. 이 작은 범위는 입력 계약이며, 임의의 모든 숫자 형식을 검증한다는 뜻은 아닙니다.

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]는 숫자가 아닌 한 문자와 일치합니다. 별도의 길이 검사가 빈 문자열을 거부합니다. CAST(... AS INTEGER)는 텍스트를 정수로 변환하지만 숫자와 길이를 검사한 후에만 사용합니다. 형 변환만으로는 안전하지 않습니다. 12kits 같은 텍스트를 12로 승인해서는 안 됩니다.

뷰 네 개를 만듭니다. delivery_check는 원래 값과 사유를 보존합니다. typed_delivery는 유효한 행만 변환합니다. delivery_errors는 거부된 행 번호와 사유를 나열합니다. delivery_ready는 이후 갱신에 적합한 값을 제공합니다. 스크립트는 다시 실행할 수 있도록 이 이름의 뷰만 삭제하며, 재고나 원본 행은 삭제하지 않습니다.

다음 Linux 명령은 이 SQL을 inspect.sql에 저장합니다. >는 대상 파일을 덮어씁니다. <<'SQL'은 내용을 그대로 받는 here-document를 시작합니다. 마지막 줄에 단독으로 쓰인 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 문 하나도 받을 수 있습니다. -header -column은 이 명령에 한해 읽기 쉬운 표시를 선택합니다. 따옴표는 공백과 세미콜론을 하나의 셸 인수 안에 유지합니다:

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;'

승인 가능한 세 행이 나와야 합니다. North의 볼트는 135센트/리비전 3, 수리용 테이프는 450/리비전 1, West의 볼트는 190/리비전 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을 저장했습니다.