Пекарната, етап 4: промените
Накратко: Четвъртият етап променя данните на пекарната с UPDATE, DELETE и upsert, а доставената поръчка сваля наличността в транзакция.
Проблем
Нищо не стои на място
Брашното поскъпна. Ана плати поръчката си. Поръчката на Десислава тръгна към нея. Борис отказа своята. Дойде доставка. Всичко това е промяна на редове, които вече ги има: UPDATE, DELETE и вмъкване, което може да е и промяна.
Всяко поле тръгва от пълната база от етап 2.
Идеята
Първо SELECT
Сладкишите поскъпват с 10%. Преди UPDATE се пуска SELECT със същото WHERE и новата стойност като израз. Така се вижда кои редове ще се сменят и как:
Три реда, само сладкишите. Сега същото WHERE отива в UPDATE.
Пробвай
Поскъпването
Вдигни цените на сладкишите (категория 2) с 10%, закръглени до 2 знака.
Решение
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category_id = 2;UPDATE products SET price = ROUND(price * 1.1, 2) WHERE category_id = 2;. Без WHERE поскъпва и хлябът, и кафето. С category_id = 1 поскъпва хлябът вместо сладкишите.
Виж го
Старата сметка
Цената на кроасана вече е 1.43 €. Поръчка 1 обаче пази цената, на която Иван е купил:
Затова в етап 1 order_items има unit_price: сметката от миналата седмица не се променя.
Пробвай
Платено
Ана плати поръчка 6. Смени статуса ѝ на 'paid'.
Решение
UPDATE orders SET status = 'paid' WHERE id = 6;UPDATE orders SET status = 'paid' WHERE id = 6;. Без WHERE всички поръчки стават платени, и доставените, и отказаната. С WHERE customer_id = 2 се сменя и доставената поръчка 2 на Ана: условието е по поръчка, не по клиент.
Грешка в думата не минава, защото CHECK от етап 1 пази статусите:
Упражнение
Доставката
Когато поръчката е доставена, продуктите излизат от склада. За всеки продукт от поръчката наличността намалява с бройките в реда. Това е UPDATE с подзаявка, която за всеки продукт взима неговите бройки.
Поръчка 5 (на Десислава) е доставена. В една транзакция намали наличността на всеки продукт от нея с бройките в реда и смени статуса на поръчката на 'delivered'.
Решение
BEGIN;
UPDATE products
SET stock = stock - (SELECT quantity FROM order_items
WHERE order_id = 5 AND product_id = products.id)
WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5);
UPDATE orders SET status = 'delivered' WHERE id = 5;
COMMIT;SET stock = stock - (SELECT quantity FROM order_items WHERE order_id = 5 AND product_id = products.id) и WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5). Подзаявката е свързана: products.id е продуктът, който се сменя в момента. Без WHERE подзаявката дава NULL за продуктите извън поръчката, stock - NULL е NULL и NOT NULL спира целия UPDATE. С stock - 1 шестте кроасана свалят само един.
По-точно, за любопитните
SQLite от 3.33 и PostgreSQL имат и UPDATE … FROM: UPDATE products SET stock = stock - i.quantity FROM order_items i WHERE i.order_id = 5 AND i.product_id = products.id;. Резултатът е същият, а за много редове е по-бърз.
Виж го
Повече, отколкото има
Козунаците са 10. Някой се опитва да продаде 20:
CHECK (stock >= 0) от етап 1 не позволява продажба на нещо, което го няма.
Идеята
Изтриване
Продукт, който е в поръчки, не може да се изтрие: външният ключ в order_items го пази.
Продуктът, който вече не се продава, остава в базата. Махането му от менюто е друга колона (active) или друга категория, не DELETE.
Пробвай
Отказаната поръчка
Пекарната не пази отказаните поръчки. Изтрий поръчка 4 заедно с редовете ѝ.
Решение
DELETE FROM orders WHERE id = 4;DELETE FROM orders WHERE id = 4; е достатъчно: ON DELETE CASCADE в order_items трие редовете ѝ сам. Ако се изтрият само редовете, остава празна поръчка без продукти. WHERE customer_id = 1 трие двете поръчки на Иван.
По-точно, за любопитните
Много системи не трият поръчки изобщо: статусът 'cancelled' е достатъчен, а историята остава за отчетите и за счетоводството. Това се казва меко изтриване (soft delete). Тук DELETE е, за да се види CASCADE в действие.
Пробвай
Нова доставка
Дойде доставка: 20 кроасана (продукт 4, вече го има) и нов продукт, 25 бутилки боза (ще е продукт 9, напитка за 1.50 €). Един и същ INSERT трябва да добави новото и да увеличи наличното. Това е upsert: INSERT … ON CONFLICT (id) DO UPDATE.
С един INSERT запиши доставката: (4, 'Кроасан', 2, 1.30, 20) и (9, 'Боза', 3, 1.50, 25). Ако продуктът го има, наличността му се увеличава с доставеното.
Решение
INSERT INTO products (id, name, category_id, price, stock) VALUES
(4, 'Кроасан', 2, 1.30, 20),
(9, 'Боза', 3, 1.50, 25)
ON CONFLICT (id) DO UPDATE SET stock = stock + excluded.stock;ON CONFLICT (id) DO UPDATE SET stock = stock + excluded.stock;. excluded е редът, който не е влязъл заради конфликта: тук 20-те кроасана. С SET stock = excluded.stock наличността става 20, а 40-те стари изчезват. INSERT OR REPLACE прави същото по-тихо: трие стария ред и пише новия.
Идеята
Какво следва
Всяка промяна тук е заявка, написана на ръка: наличността се сваля, когато някой се сети. Следващият етап кара базата да го прави сама и да е по-бърза: индекс, изглед, тригер и нова колона.
Тест