db_tests.sql - task12 - MarkZhukov/web

db_tests.sql
138 строк6 KB
markkentos
add pdf view
04 июн 2026, 01:25
04 июн 2026, 01:25bb001aa
\echo '--------------------------------------------------'
\echo 'НАЧАЛО ТЕСТИРОВАНИЯ РЕШЕНИЙ POSTGRESQL'
\echo '--------------------------------------------------'

-- БЛОК 1: ФУНКЦИИ И ПРОЦЕДУРЫ

\echo '--- Блок 1: Тест 1. get_customer_total_spent (Сумма покупок клиента) ---'
-- Переведем заказ 1 в 'completed', чтобы проверить расчет
UPDATE shop_order SET status = 'completed' WHERE id = 1;
SELECT id, first_name, last_name, get_customer_total_spent(id) AS total_spent
FROM shop_customer;
-- Вернем обратно в 'pending'
UPDATE shop_order SET status = 'pending' WHERE id = 1;

\echo '--- Блок 1: Тест 2. get_products_in_promotion (Товары по акции) ---'
SELECT * FROM get_products_in_promotion(1);

\echo '--- Блок 1: Тест 3. process_order_payment (Процедура оплаты с транзакциями) ---'
-- Оплата неполной суммой (ожидается failed и статус pending)
DO $$
DECLARE res VARCHAR(100);
BEGIN
    CALL process_order_payment(1, 50000.00, 'card', res);
    RAISE NOTICE 'Результат 1-й оплаты: %', res;
END;
$$;
SELECT id, status, final_amount FROM shop_order WHERE id = 1;
SELECT * FROM shop_payment WHERE order_id = 1;

-- Оплата полной суммой (ожидается paid и изменение статуса заказа)
DO $$
DECLARE res VARCHAR(100);
BEGIN
    CALL process_order_payment(1, 120000.00, 'card', res);
    RAISE NOTICE 'Результат 2-й оплаты: %', res;
END;
$$;
SELECT id, status FROM shop_order WHERE id = 1;
SELECT * FROM shop_payment WHERE order_id = 1;

\echo '--- Блок 1: Тест 4. apply_mass_category_discount (Перегрузка и скидка на категорию) ---'
SELECT id, name, price FROM shop_product WHERE category_id = 1;
SELECT apply_mass_category_discount(1, 10, 'percent') AS updated_products;
SELECT id, name, price FROM shop_product WHERE category_id = 1;


-- БЛОК 2: ЦИКЛЫ

\echo '--- Блок 2: Тест 1. generate_test_categories (Цикл FOR range) ---'
CALL generate_test_categories(3);
SELECT * FROM shop_category;

\echo '--- Блок 2: Тест 2. notify_low_stock_products (Цикл FOR query) ---'
-- Ожидается NOTICE о Lenovo (у него на складе 2 шт. при пороге 5)
CALL notify_low_stock_products(5);

\echo '--- Блок 2: Тест 3. bulk_restock_in_batches (Цикл WHILE) ---'
SELECT product_id, quantity_in_stock FROM shop_stock WHERE product_id IN (3, 4);
-- Пополняем категорию 2 (Ноутбуки) до 20 штук на складе
CALL bulk_restock_in_batches(2, 20);
SELECT product_id, quantity_in_stock FROM shop_stock WHERE product_id IN (3, 4);

\echo '--- Блок 2: Тест 4. calculate_selected_products_cost (Цикл FOREACH по массиву) ---'
SELECT calculate_selected_products_cost(ARRAY[1, 2, 3]) AS array_products_sum;


-- БЛОК 3: ИНДЕКСЫ

\echo '--- Блок 3: Тест индексов ---'
SELECT indexname, indexdef FROM pg_indexes WHERE tablename IN ('shop_product', 'shop_customer') AND indexname NOT LIKE '%_pkey%';


-- БЛОК 4: ПРЕДСТАВЛЕНИЯ И ДОСТУП

\echo '--- Блок 4: Тест временного представления temp_recent_large_orders ---'
-- Сделаем тестовый заказ большим и недавним
INSERT INTO shop_order (id, customer_id, order_date, status, total_amount, discount_amount, final_amount)
VALUES (3, 2, NOW(), 'pending', 60000.00, 0.00, 60000.00)
ON CONFLICT DO NOTHING;

-- Воссоздаем временное представление в рамках текущей сессии тестирования
CREATE OR REPLACE TEMP VIEW temp_recent_large_orders AS
SELECT 
    o.id AS order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    o.final_amount
FROM shop_order o
JOIN shop_customer c ON o.customer_id = c.id
WHERE o.final_amount > 50000.00
  AND o.order_date >= NOW() - INTERVAL '7 days'
ORDER BY o.final_amount DESC;

SELECT * FROM temp_recent_large_orders;


-- БЛОК 5: ТРИГГЕРЫ

\echo '--- Блок 5: Тест триггера 1 (Валидация остатка и авторасчет line_total) ---'
-- 1. Попытка заказать 100 шт. MacBook (в наличии всего 20) -> ожидается ошибка
\echo 'Ожидается ошибка нехватки товара на складе:'
DO $$
BEGIN
    INSERT INTO shop_orderitem (order_id, product_id, quantity, price, line_total)
    VALUES (1, 3, 100, 140000.00, 0.00);
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Успешно перехвачена ошибка: %', SQLERRM;
END;
$$;

-- 2. Успешный заказ 2 шт. (цена возьмется из товара, line_total рассчитается, склад уменьшится)
SELECT quantity_in_stock FROM shop_stock WHERE product_id = 3;
INSERT INTO shop_orderitem (order_id, product_id, quantity, price, line_total)
VALUES (1, 3, 2, 0.00, 0.00);
SELECT * FROM shop_orderitem WHERE order_id = 1 AND product_id = 3;
SELECT quantity_in_stock FROM shop_stock WHERE product_id = 3;

\echo '--- Блок 5: Тест триггера 2 (Синхронизация статуса заказа по оплате) ---'
-- Создадим новый заказ на 45000
INSERT INTO shop_order (id, customer_id, order_date, status, total_amount, discount_amount, final_amount)
VALUES (4, 2, NOW(), 'pending', 45000.00, 0.00, 45000.00)
ON CONFLICT DO NOTHING;

SELECT id, status FROM shop_order WHERE id = 4;
-- Внесем платеж (Trigger 2 переведет статус заказа в 'paid')
INSERT INTO shop_payment (order_id, payment_date, amount, payment_method, payment_status)
VALUES (4, NOW(), 45000.00, 'card', 'paid');
SELECT id, status FROM shop_order WHERE id = 4;

\echo '--- Блок 5: Тест триггера 3 (Аудит изменения цен) ---'
SELECT * FROM shop_product_price_history;
UPDATE shop_product SET price = 130000.00 WHERE id = 1;
SELECT * FROM shop_product_price_history;

\echo '--------------------------------------------------'
\echo 'ТЕСТИРОВАНИЕ ЗАВЕРШЕНО УСПЕШНО!'
\echo '--------------------------------------------------'