Примените все полученные знания по PostgreSQL в реальном проекте. Спроектируйте, реализуйте и оптимизируйте базу данных для интернет-магазина.
- Проектирование схемы базы данных
- Реализация бизнес-логики
- 1. Функция для добавления товара в корзину:
- 2. Триггер для обновления общей суммы заказа:
- Оптимизация и индексирование
- Обеспечение безопасности
- 1. Создадим роль для веб-приложения:
- 2. Предоставим необходимые права:
- 3. Создадим представление для безопасного отображения данных пользователей:
- Тестирование
- Домашнее задание:
Проектирование схемы базы данных
Наша задача — создать базу данных для интернет-магазина. Давайте определим основные сущности:
- Пользователи (users)
- Товары (products)
- Категории товаров (categories)
- Заказы (orders)
- Детали заказа (order_items)
Теперь создадим таблицы:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INTEGER NOT NULL,
category_id INTEGER REFERENCES categories(category_id)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
total_amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id),
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
Реализация бизнес-логики
Теперь давайте реализуем некоторые функции для работы с нашей базой данных:
1. Функция для добавления товара в корзину:
CREATE OR REPLACE FUNCTION add_to_cart(
p_user_id INTEGER,
p_product_id INTEGER,
p_quantity INTEGER
)
RETURNS VOID AS $
DECLARE
v_price DECIMAL(10, 2);
v_stock INTEGER;
BEGIN
-- Проверяем наличие товара
SELECT price, stock_quantity INTO v_price, v_stock
FROM products
WHERE product_id = p_product_id;
IF v_stock < p_quantity THEN
RAISE EXCEPTION 'Недостаточно товара на складе';
END IF;
-- Добавляем товар в заказ
INSERT INTO orders (user_id, total_amount, status)
VALUES (p_user_id, v_price * p_quantity, 'в корзине')
ON CONFLICT (user_id, status) WHERE status = 'в корзине'
DO UPDATE SET total_amount = orders.total_amount + v_price * p_quantity;
-- Добавляем детали заказа
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (
(SELECT order_id FROM orders WHERE user_id = p_user_id AND status = 'в корзине'),
p_product_id,
p_quantity,
v_price
);
-- Обновляем количество товара на складе
UPDATE products
SET stock_quantity = stock_quantity - p_quantity
WHERE product_id = p_product_id;
END;
$ LANGUAGE plpgsql;
2. Триггер для обновления общей суммы заказа:
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $
BEGIN
UPDATE orders
SET total_amount = (
SELECT SUM(quantity * price)
FROM order_items
WHERE order_id = NEW.order_id
)
WHERE order_id = NEW.order_id;
RETURN NEW;
END;
$ LANGUAGE plpgsql;
CREATE TRIGGER update_order_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();
Оптимизация и индексирование
Для улучшения производительности создадим индексы:
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
Обеспечение безопасности
1. Создадим роль для веб-приложения:
CREATE ROLE web_app LOGIN PASSWORD 'secure_password';
2. Предоставим необходимые права:
GRANT SELECT, INSERT, UPDATE ON users, categories, products, orders, order_items TO web_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO web_app;
3. Создадим представление для безопасного отображения данных пользователей:
CREATE VIEW safe_users AS
SELECT user_id, username, email, created_at
FROM users;
GRANT SELECT ON safe_users TO web_app;
Тестирование
Давайте протестируем нашу базу данных:
-- Добавим категорию
INSERT INTO categories (name, description) VALUES ('Электроника', 'Электронные устройства и гаджеты');
-- Добавим продукт
INSERT INTO products (name, description, price, stock_quantity, category_id)
VALUES ('Смартфон XYZ', 'Мощный смартфон с отличной камерой', 599.99, 100, 1);
-- Создадим пользователя
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', '[email protected]', 'hashed_password');
-- Добавим товар в корзину
SELECT add_to_cart(1, 1, 2);
-- Проверим содержимое корзины
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM order_items WHERE order_id = 1;
Поздравляем! Вы успешно создали, реализовали и протестировали базу данных для интернет-магазина. Этот проект объединил все ключевые концепции, которые мы изучили в ходе курса: от проектирования схемы и создания таблиц до написания сложных функций, триггеров и обеспечения безопасности. Вы теперь обладаете навыками для работы с реальными проектами на PostgreSQL. Продолжайте практиковаться и расширять свои знания. Удачи в вашем дальнейшем пути в мире баз данных!
Домашнее задание:
- Добавьте функциональность для обработки возвратов товаров.
- Реализуйте систему скидок и промокодов.
- Создайте отчет о самых популярных товарах за последний месяц.
- Напишите функцию для автоматического обновления статуса заказа.
Если вам нужно освежить знания по основам, вернитесь к уроку 1: Введение в мир баз данных и PostgreSQL