Урок 12. Финальный проект: создание базы данных для веб-приложения интернет-магазина

Примените все полученные знания по 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', 'john@example.com', '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. Продолжайте практиковаться и расширять свои знания. Удачи в вашем дальнейшем пути в мире баз данных!