Введение
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. Один цент — минимальная денежная единица в этом вымышленном магазине; 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;
Выйдите из SQLite командой .quit; вы вернётесь в командную оболочку 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 и названием, содержащим хотя бы один символ помимо пробелов. Цена должна состоять из 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] — одному символу, не являющемуся цифрой. Отдельная проверка длины отклоняет пустые строки. 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, вернитесь в каталог абсолютной командой cd из шага 1, исправьте 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 для повторного использования при следующей поставке.
