Пекарната, етап 5: базата работи сама

Накратко: Петият етап добавя индекс, изглед със сумите на поръчките, тригер, който сваля наличността, и нова колона с ALTER TABLE.

Проблем

Нещата, за които се сещаш

В етап 4 наличността се сваляше от заявка, написана на ръка, а сумата на поръчка се смяташе с три таблици всеки път. Пекарната расте: поръчките стават хиляди, а хората, които пишат заявки, стават повече. Нещата, които зависят от това някой да се сети, рано или късно се забравят.

Този етап прехвърля работата към базата: индекс за бързо търсене, изглед за сумите, тригер за склада и нова колона за телефона.

Идеята

Как базата търси

Страницата „Моите поръчки“ пита за поръчките на един клиент. EXPLAIN QUERY PLAN показва как базата ще го направи:

SQLпразна база

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

▶ ИзпълниВърни заявкатаНова база

SCAN orders: базата чете всяка поръчка и гледа клиента. При шест поръчки няма значение, при милион е бавно.

Пробвай

Индексът

Направи индекс orders_customer върху колоната customer_id на orders.

SQLпразна база

SELECT * FROM orders WHERE customer_id = 2;

SELECT * FROM orders WHERE customer_id = 2;

Провери▶ Изпълни

Същата заявка с индекса:

SQLпразна база

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

▶ ИзпълниВърни заявкатаНова база

SEARCH … USING INDEX: базата отива направо при поръчките на клиент 2.

По-точно, за любопитните

SQLite не прави сам индекс за външен ключ, PostgreSQL също. Колона, която сочи към друга таблица, почти винаги иска индекс: по нея се свързва и се търси. Индексът обаче не е безплатен: всеки INSERT и UPDATE го обновява.

Пробвай

Изгледът

Сумата на поръчка е три таблици и GROUP BY всеки път. Изглед (view) е заявка с име: от него се чете като от таблица.

Направи изглед order_totals с номера на поръчката (id), клиента (customer_id), статуса и сумата (total, закръглена до 2 знака) на всяка поръчка.

SQLпразна база

CREATE VIEW order_totals AS
SELECT id, customer_id, status
FROM orders;

CREATE VIEW order_totals AS SELECT id, customer_id, status FROM orders;

Провери▶ Изпълни

Виж го

Изгледът като таблица

Колко е похарчил всеки клиент, без отказаните поръчки. Сметката с трите таблици е скрита в изгледа:

SQLпразна база

SELECT c.name, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY spent DESC;

SELECT c.name, SUM(t.total) AS spent FROM order_totals t JOIN customers c ON c.id = t.customer_id WHERE t.status <> 'cancelled' GROUP BY c.id ORDER BY spent DESC;

▶ ИзпълниВърни заявкатаНова база

Изгледът не пази данни, а заявката си. Нова поръчка се вижда в него веднага:

SQLпразна база

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 3, 2, 1.60);
SELECT * FROM order_totals WHERE id = 7;

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 3, 2, 1.60); SELECT * FROM order_totals WHERE id = 7;

▶ ИзпълниВърни заявкатаНова база

Пробвай

Тригерът

Тригер е код, който базата пуска сама при INSERT, UPDATE или DELETE в таблица. В тялото му new е новият ред.

Направи тригер take_stock: след всеки нов ред в order_items наличността на продукта от реда намалява с бройките в него.

SQLпразна база

CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
  SELECT 1;
END;

CREATE TRIGGER take_stock AFTER INSERT ON order_items BEGIN SELECT 1; END;

Провери▶ Изпълни

Виж го

Тригерът и правилата

С тригера Калин поръчва 20 козунака, а те са 10:

SQLпразна база

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 5, 20, 6.50);

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 5, 20, 6.50);

▶ ИзпълниВърни заявкатаНова база

UPDATE в тригера нарушава CHECK (stock >= 0). Тригерът е част от INSERT, затова и редът на поръчката не влиза: в базата няма ред, който да казва „продадени 20 козунака“, когато те ги няма.

По-точно, за любопитните

Тригерите пазят правило на едно място, но са скрити: който чете кода на програмата, не ги вижда. Затова се ползват пестеливо, за неща, които трябва да станат винаги, каквото и да пише в базата. Наличността тук е такова нещо, а пращането на имейл не е.

Пробвай

Нова колона

Пекарната иска да звъни на клиентите, когато поръчката е готова. Таблицата има данни, затова не се прави наново: ALTER TABLE добавя колона към нея.

Добави на customers колона phone от тип TEXT, която може да липсва.

SQLпразна база

SELECT * FROM customers;

SELECT * FROM customers;

Провери▶ Изпълни

С NOT NULL не става:

SQLпразна база

ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

▶ ИзпълниВърни заявкатаНова база

По-точно, за любопитните

ALTER TABLE в SQLite може да добавя, преименува и (от 3.35) маха колони. Ограничение като CHECK или външен ключ към съществуваща колона не може да добави: тогава таблицата се прави наново (нова таблица, INSERT … SELECT, DROP на старата и преименуване). В PostgreSQL ALTER TABLE … ADD CONSTRAINT го прави директно.

Идеята

Какво следва

Базата е пълна, бърза и пази правилата си сама. Последният етап е краят: отчет, копие на базата и DROP на всичко в правилния ред.

Тест

Провери се

1. Изгледът order_totals пази ли сумите на поръчките?

Пази ги до рестартНе, пази заявката. Сумите се смятат всеки път, когато някой чете от изгледаДа, като таблица

2. Тригерът сваля наличността, а продуктът свършва. Какво става с реда в order_items?

Записва се с NULLЗаписва се, а наличността остава 0Не се записва: тригерът е част от INSERT и грешката в него отменя и реда

3. Защо ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL дава грешка?

Клиентите, които вече ги има, ще имат празен телефон, а NOT NULL не го позволяваALTER TABLE не съществува в SQLiteTEXT не може да е NOT NULL

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: Изгледи · Тригери · Индекси · EXPLAIN: планът на заявката