sql/lab7_all.sql - main - Kiyonory/4sem

sql/lab7_all.sql
555 строк17 KB
Kiyonory
front
02 июн 2026, 01:35
02 июн 2026, 01:35b6d2cfb
-- Лабораторные 7–8 (PostgreSQL, магазин). Один файл, без дублей.
-- Запуск: PGPASSWORD=shop psql -h 127.0.0.1 -p 5434 -U shop -d shop_lab7 -f sql/lab7_all.sql

-- =============================================================================
-- БЛОК 1. Функции и процедуры
-- =============================================================================

-- 1.1 Отчёт по зависшим pending-заказам
CREATE OR REPLACE PROCEDURE shop_report_stale_orders(p_hours INT)
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT id, customer_name, order_date,
           EXTRACT(EPOCH FROM (NOW() - order_date)) / 3600 AS hours_waiting
    FROM shop_order
    WHERE status = 'pending'
      AND order_date < NOW() - (p_hours || ' hours')::INTERVAL
    ORDER BY order_date
  LOOP
    RAISE NOTICE 'Заказ %: %, ждёт % ч', r.id, r.customer_name, ROUND(r.hours_waiting::numeric, 1);
  END LOOP;
END;
$$;

-- 1.2 Возраст заказа в часах (FUNCTION, PL/pgSQL)
CREATE OR REPLACE FUNCTION shop_get_order_age_hours(p_order_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_hours NUMERIC;
BEGIN
  SELECT EXTRACT(EPOCH FROM (NOW() - order_date)) / 3600
  INTO v_hours
  FROM shop_order
  WHERE id = p_order_id;

  IF NOT FOUND THEN
    RETURN NULL;
  END IF;
  RETURN ROUND(v_hours, 2);
END;
$$;

-- 1.3 Безопасная отмена заказа (PROCEDURE + OUT)
CREATE OR REPLACE PROCEDURE shop_cancel_order(
  p_order_id INT,
  OUT result_message TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_status VARCHAR(20);
BEGIN
  SELECT status INTO v_status FROM shop_order WHERE id = p_order_id;

  IF NOT FOUND THEN
    result_message := 'Заказ не найден';
    RETURN;
  END IF;

  IF v_status NOT IN ('pending', 'paid') THEN
    result_message := format('Нельзя отменить: текущий статус %s', v_status);
    RETURN;
  END IF;

  UPDATE shop_order SET status = 'cancelled' WHERE id = p_order_id;
  result_message := 'Заказ отменён';
END;
$$;

-- 1.4 Продажи по бренду (FUNCTION, SQL)
CREATE OR REPLACE FUNCTION shop_get_brand_sales_count(p_brand_id INT)
RETURNS INT
LANGUAGE sql
AS $$
  SELECT COUNT(*)::INT
  FROM shop_orderitem oi
  JOIN shop_productvariant pv ON pv.id = oi.product_variant_id
  JOIN shop_product p ON p.id = pv.product_id
  WHERE p.brand_id = p_brand_id;
$$;

-- Демо блока 1
CALL shop_report_stale_orders(24);
SELECT shop_get_order_age_hours((SELECT id FROM shop_order ORDER BY id LIMIT 1));
-- CALL shop_cancel_order(1, NULL);
SELECT shop_get_brand_sales_count(1);

-- Дополнительно (lab7): апгрейд позиции, склад, скидка по категории, вывод бренда
CREATE OR REPLACE PROCEDURE shop_upgrade_cheapest_item(
  p_order_id INT,
  p_max_surcharge NUMERIC,
  OUT msg TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_item_id INT;
  v_old_price NUMERIC(10,2);
  v_new_price NUMERIC(10,2);
  v_category_id INT;
  v_surcharge NUMERIC(12,2);
  v_order_total NUMERIC(12,2);
BEGIN
  SELECT oi.id, oi.price_at_time, p.category_id
  INTO v_item_id, v_old_price, v_category_id
  FROM shop_orderitem oi
  JOIN shop_productvariant pv ON pv.id = oi.product_variant_id
  JOIN shop_product p ON p.id = pv.product_id
  WHERE oi.order_id = p_order_id
  ORDER BY oi.price_at_time ASC, oi.id
  LIMIT 1;

  IF NOT FOUND THEN
    msg := 'В заказе нет позиций';
    RETURN;
  END IF;

  SELECT MAX(p.base_price) INTO v_new_price
  FROM shop_product p
  WHERE p.category_id = v_category_id;

  v_surcharge := v_new_price - v_old_price;

  IF v_surcharge > p_max_surcharge THEN
    RAISE EXCEPTION 'Слишком дорогой апгрейд: доплата %', v_surcharge;
  END IF;

  UPDATE shop_orderitem SET price_at_time = v_new_price WHERE id = v_item_id;

  SELECT COALESCE(SUM(price_at_time * quantity), 0)
  INTO v_order_total
  FROM shop_orderitem
  WHERE order_id = p_order_id;

  UPDATE shop_order SET total_amount = v_order_total WHERE id = p_order_id;

  msg := format('Позиция %s: %s → %s', v_item_id, v_old_price, v_new_price);
END;
$$;

CREATE OR REPLACE FUNCTION shop_avg_discount_by_category(p_category_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_avg NUMERIC;
BEGIN
  SELECT AVG(o.discount_amount)
  INTO v_avg
  FROM shop_order o
  WHERE EXISTS (
    SELECT 1
    FROM shop_orderitem oi
    JOIN shop_productvariant pv ON pv.id = oi.product_variant_id
    JOIN shop_product p ON p.id = pv.product_id
    WHERE oi.order_id = o.id
      AND p.category_id = p_category_id
  );

  RETURN COALESCE(ROUND(v_avg, 2), 0);
END;
$$;

CREATE OR REPLACE PROCEDURE shop_decommission_brand(p_brand_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT DISTINCT o.id AS order_id
    FROM shop_order o
    JOIN shop_orderitem oi ON oi.order_id = o.id
    JOIN shop_productvariant pv ON pv.id = oi.product_variant_id
    JOIN shop_product p ON p.id = pv.product_id
    WHERE p.brand_id = p_brand_id
      AND o.status = 'pending'
  LOOP
    UPDATE shop_order SET status = 'cancelled' WHERE id = r.order_id;

    UPDATE shop_order o
    SET total_amount = GREATEST(0, o.total_amount - COALESCE((
      SELECT SUM(oi.price_at_time * oi.quantity)
      FROM shop_orderitem oi
      JOIN shop_productvariant pv ON pv.id = oi.product_variant_id
      JOIN shop_product p ON p.id = pv.product_id
      WHERE oi.order_id = r.order_id AND p.brand_id = p_brand_id
    ), 0))
    WHERE o.id = r.order_id;
  END LOOP;

  DELETE FROM shop_orderitem oi
  USING shop_productvariant pv, shop_product p
  WHERE oi.product_variant_id = pv.id
    AND pv.product_id = p.id
    AND p.brand_id = p_brand_id;

  DELETE FROM shop_producttag
  WHERE product_id IN (SELECT id FROM shop_product WHERE brand_id = p_brand_id);

  DELETE FROM shop_productsimilar
  WHERE from_product_id IN (SELECT id FROM shop_product WHERE brand_id = p_brand_id)
     OR to_product_id IN (SELECT id FROM shop_product WHERE brand_id = p_brand_id);

  DELETE FROM shop_product_available_sizes
  WHERE product_id IN (SELECT id FROM shop_product WHERE brand_id = p_brand_id);

  DELETE FROM shop_product_available_colors
  WHERE product_id IN (SELECT id FROM shop_product WHERE brand_id = p_brand_id);

  DELETE FROM shop_productvariant pv
  USING shop_product p
  WHERE pv.product_id = p.id AND p.brand_id = p_brand_id;

  DELETE FROM shop_product WHERE brand_id = p_brand_id;

  DELETE FROM shop_brand WHERE id = p_brand_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Бренд % не найден', p_brand_id;
  END IF;

  RAISE NOTICE 'Бренд % выведен из ассортимента', p_brand_id;
END;
$$;

-- =============================================================================
-- БЛОК 2. Циклы (каждый тип — один раз)
-- =============================================================================

-- FOR … BY 2 (чётные позиции в массиве id вариантов)
DO $$
DECLARE
  variant_ids INT[] := ARRAY(SELECT id FROM shop_productvariant ORDER BY id LIMIT 6);
  even_only INT[] := '{}';
  i INT;
BEGIN
  FOR i IN 2..cardinality(variant_ids) BY 2 LOOP
    even_only := even_only || variant_ids[i];
  END LOOP;
  RAISE NOTICE 'FOR BY 2: %', even_only;
END $$;

-- FOREACH (весь массив → метки SKU)
DO $$
DECLARE
  variant_ids INT[] := ARRAY(SELECT id FROM shop_productvariant ORDER BY id LIMIT 5);
  labels TEXT := '';
  vid INT;
BEGIN
  FOREACH vid IN ARRAY variant_ids LOOP
    labels := labels || 'SKU-' || vid || ';';
  END LOOP;
  RAISE NOTICE 'FOREACH: %', labels;
END $$;

-- WHILE (уникальный код заказа)
CREATE OR REPLACE FUNCTION shop_generate_order_code()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  code TEXT;
  exists_flag BOOLEAN;
BEGIN
  WHILE TRUE LOOP
    code := 'ORD-' || upper(substr(md5(random()::text), 1, 8));
    SELECT EXISTS(SELECT 1 FROM shop_order WHERE customer_email = code || '@gen.tmp')
    INTO exists_flag;
    EXIT WHEN NOT exists_flag;
  END LOOP;
  RETURN code;
END;
$$;

SELECT shop_generate_order_code();

-- LOOP … EXIT WHEN (пакетное удаление старых cancelled; CALL только вручную)
CREATE OR REPLACE PROCEDURE shop_purge_old_cancelled(p_years INT DEFAULT 2)
LANGUAGE plpgsql
AS $$
DECLARE
  v_left INT;
  v_deleted INT;
BEGIN
  LOOP
    SELECT COUNT(*) INTO v_left
    FROM shop_order
    WHERE status = 'cancelled'
      AND order_date < NOW() - (p_years || ' years')::INTERVAL;

    EXIT WHEN v_left = 0;

    DELETE FROM shop_order
    WHERE id IN (
      SELECT id FROM shop_order
      WHERE status = 'cancelled'
        AND order_date < NOW() - (p_years || ' years')::INTERVAL
      LIMIT 1000
    );

    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    RAISE NOTICE 'Удалено %', v_deleted;
    COMMIT;
  END LOOP;
END;
$$;

-- =============================================================================
-- БЛОК 3. Индексы
-- =============================================================================

CREATE INDEX IF NOT EXISTS idx_shop_order_status_date
  ON shop_order (status, order_date DESC);

CREATE INDEX IF NOT EXISTS idx_shop_product_brand_price
  ON shop_product (brand_id, base_price);

EXPLAIN (COSTS OFF)
SELECT id, customer_email, order_date
FROM shop_order
WHERE status = 'pending'
ORDER BY order_date DESC
LIMIT 20;

-- =============================================================================
-- БЛОК 4. Временные представления и права
-- =============================================================================

DROP VIEW IF EXISTS shop_v_brand_avg_price;
CREATE TEMP VIEW shop_v_brand_avg_price AS
SELECT
  b.id AS brand_id,
  b.name AS brand_name,
  ROUND(AVG(p.base_price), 2) AS avg_price,
  COUNT(p.id) AS products_count
FROM shop_brand b
LEFT JOIN shop_product p ON p.brand_id = b.id
GROUP BY b.id, b.name;

DROP VIEW IF EXISTS shop_v_pending_recent;
CREATE TEMP VIEW shop_v_pending_recent AS
SELECT id, customer_name, customer_email, order_date, total_amount
FROM shop_order
WHERE status = 'pending'
  AND order_date >= NOW() - INTERVAL '7 days';

SELECT * FROM shop_v_brand_avg_price ORDER BY avg_price DESC NULLS LAST LIMIT 5;
SELECT * FROM shop_v_pending_recent LIMIT 5;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'shop_analyst') THEN
    CREATE ROLE shop_analyst NOLOGIN;
  END IF;
END $$;

GRANT CONNECT ON DATABASE shop_lab7 TO shop_analyst;
GRANT USAGE ON SCHEMA public TO shop_analyst;
GRANT SELECT ON shop_brand, shop_category, shop_product, shop_productvariant, shop_order TO shop_analyst;

-- =============================================================================
-- БЛОК 5. Триггеры (3 задания — каждое: функция + триггер + демо)
-- В pgAdmin можно выполнять по одному заданию (5.1 → 5.2 → 5.3).
-- =============================================================================

-- Снять старые имена триггеров
DROP TRIGGER IF EXISTS trg_orderitem_check_stock ON shop_orderitem;
DROP TRIGGER IF EXISTS trg_orderitem_recalc_total ON shop_orderitem;
DROP TRIGGER IF EXISTS trg_order_lock_delivered ON shop_order;
DROP TRIGGER IF EXISTS trg_product_audit ON shop_product;
DROP TRIGGER IF EXISTS trg_check_stock ON shop_orderitem;
DROP TRIGGER IF EXISTS trg_recalc_total ON shop_orderitem;
DROP TRIGGER IF EXISTS trg_lock_delivered ON shop_order;

-- ---------- Задание 5.1. Проверка остатка (BEFORE INSERT, ROW) ----------
CREATE OR REPLACE FUNCTION shop_trg_check_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_stock INT;
BEGIN
  SELECT stock_quantity INTO v_stock
  FROM shop_productvariant
  WHERE id = NEW.product_variant_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Вариант % не найден', NEW.product_variant_id;
  END IF;

  IF NEW.quantity > v_stock THEN
    RAISE EXCEPTION 'На складе % шт., в заказ — %', v_stock, NEW.quantity;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_check_stock ON shop_orderitem;
CREATE TRIGGER trg_check_stock
  BEFORE INSERT ON shop_orderitem
  FOR EACH ROW
  EXECUTE FUNCTION shop_trg_check_stock();

-- Демо 5.1: выполнить весь блок DO целиком (не по одной строке). Скрин — Messages.
DO $$
DECLARE
  v_order_id INT;
  v_variant_id INT;
  v_stock INT;
BEGIN
  DELETE FROM shop_orderitem oi
  USING shop_order o
  WHERE oi.order_id = o.id AND o.customer_email LIKE 'lab_trg51@%';
  DELETE FROM shop_order WHERE customer_email LIKE 'lab_trg51@%';

  SELECT id, stock_quantity INTO v_variant_id, v_stock
  FROM shop_productvariant
  WHERE stock_quantity >= 1
  ORDER BY id
  LIMIT 1;

  INSERT INTO shop_order (customer_name, customer_email, order_date, status, total_amount, discount_amount)
  VALUES ('Демо 5.1', 'lab_trg51@test.ru', NOW(), 'pending', 0, 0)
  RETURNING id INTO v_order_id;

  BEGIN
    INSERT INTO shop_orderitem (order_id, product_variant_id, quantity, price_at_time)
    SELECT v_order_id, v_variant_id, v_stock + 100, p.base_price
    FROM shop_productvariant pv
    JOIN shop_product p ON p.id = pv.product_id
    WHERE pv.id = v_variant_id;
    RAISE NOTICE '5.1: ожидалась ошибка, но INSERT прошёл';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '5.1 OK — триггер остановил INSERT: %', SQLERRM;
  END;

  DELETE FROM shop_orderitem WHERE order_id = v_order_id;
  DELETE FROM shop_order WHERE id = v_order_id;
END $$;

-- ---------- Задание 5.2. Пересчёт суммы заказа (AFTER INSERT, ROW) ----------
CREATE OR REPLACE FUNCTION shop_trg_recalc_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE shop_order o
  SET total_amount = GREATEST(COALESCE((
    SELECT SUM(oi.price_at_time * oi.quantity)
    FROM shop_orderitem oi
    WHERE oi.order_id = NEW.order_id
  ), 0) - COALESCE(o.discount_amount, 0), 0)
  WHERE o.id = NEW.order_id;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_recalc_total ON shop_orderitem;
CREATE TRIGGER trg_recalc_total
  AFTER INSERT ON shop_orderitem
  FOR EACH ROW
  EXECUTE FUNCTION shop_trg_recalc_total();

-- Демо 5.2: выполнить весь блок до конца (одним Execute). Скрин — таблица «до/после».
DELETE FROM shop_orderitem oi
USING shop_order o
WHERE oi.order_id = o.id AND o.customer_email = 'lab_trg52@test.ru';
DELETE FROM shop_order WHERE customer_email = 'lab_trg52@test.ru';

WITH picked AS (
  SELECT id AS variant_id
  FROM shop_productvariant
  WHERE stock_quantity >= 1
  ORDER BY id
  LIMIT 1
),
new_order AS (
  INSERT INTO shop_order (customer_name, customer_email, order_date, status, total_amount, discount_amount)
  VALUES ('Демо 5.2', 'lab_trg52@test.ru', NOW(), 'pending', 0, 0)
  RETURNING id, total_amount AS total_before
),
add_item AS (
  INSERT INTO shop_orderitem (order_id, product_variant_id, quantity, price_at_time)
  SELECT new_order.id, picked.variant_id, 1, p.base_price
  FROM new_order, picked
  JOIN shop_productvariant pv ON pv.id = picked.variant_id
  JOIN shop_product p ON p.id = pv.product_id
  RETURNING order_id
)
SELECT
  new_order.id AS order_id,
  new_order.total_before,
  o.total_amount AS total_after
FROM new_order
JOIN shop_order o ON o.id = new_order.id;

DELETE FROM shop_orderitem oi
USING shop_order o
WHERE oi.order_id = o.id AND o.customer_email = 'lab_trg52@test.ru';
DELETE FROM shop_order WHERE customer_email = 'lab_trg52@test.ru';

-- ---------- Задание 5.3. Запрет менять сумму у доставленного (BEFORE UPDATE, ROW) ----------
CREATE OR REPLACE FUNCTION shop_trg_lock_delivered()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.status = 'delivered'
     AND OLD.total_amount IS DISTINCT FROM NEW.total_amount THEN
    RAISE EXCEPTION 'Заказ % доставлен — сумму менять нельзя', OLD.id;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_lock_delivered ON shop_order;
CREATE TRIGGER trg_lock_delivered
  BEFORE UPDATE ON shop_order
  FOR EACH ROW
  EXECUTE FUNCTION shop_trg_lock_delivered();

-- Демо 5.3: выполнить весь блок DO целиком. Скрин — Messages.
DO $$
DECLARE
  v_order_id INT;
BEGIN
  DELETE FROM shop_orderitem oi
  USING shop_order o
  WHERE oi.order_id = o.id AND o.customer_email = 'lab_trg53@test.ru';
  DELETE FROM shop_order WHERE customer_email = 'lab_trg53@test.ru';

  INSERT INTO shop_order (customer_name, customer_email, order_date, status, total_amount, discount_amount)
  VALUES ('Демо 5.3', 'lab_trg53@test.ru', NOW(), 'delivered', 1000.00, 0)
  RETURNING id INTO v_order_id;

  BEGIN
    UPDATE shop_order SET total_amount = 1 WHERE id = v_order_id;
    RAISE NOTICE '5.3: ожидалась ошибка, но UPDATE прошёл';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '5.3 OK — триггер остановил UPDATE: %', SQLERRM;
  END;

  DELETE FROM shop_orderitem WHERE order_id = v_order_id;
  DELETE FROM shop_order WHERE id = v_order_id;
END $$;