Лабораторная работа 2

Лабораторная работа 2

01_lab_full.sql (самодостаточный сценарий)

Можно выполнить тут
https://aiven.io/tools/pg-playground

-- =========================================================
-- ЛАБА: Администрирование БД (создание, данные, базовые проверки)
-- Работает в песочницах без права CREATE DATABASE (через схему).
-- Если есть CREATE DATABASE — см. блок A ниже (опционально).
-- =========================================================

-- ---------------------------------------------------------
-- [A] ОПЦИОНАЛЬНО: создать отдельную БД (выполнять ТОЛЬКО если доступно)
-- ---------------------------------------------------------
--  CREATE DATABASE lab_admindb WITH ENCODING 'UTF8' TEMPLATE template1;
--  -- затем ПОДКЛЮЧИТЕСЬ к неё (в некоторых UI это делают вручную)
--  -- \c lab_admindb

-- ---------------------------------------------------------
-- [БЕЗОПАСНО ПО УМОЛЧАНИЮ] Создаём схему как «контейнер БД»
-- ---------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS lab_admindb AUTHORIZATION CURRENT_USER;
SET search_path TO lab_admindb, public;

-- На всякий случай чистим прошлые артефакты (если перезапуск)
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;

-- ---------------------------------------------------------
-- Таблицы и ограничения
-- ---------------------------------------------------------
CREATE TABLE customers (
  customer_id  BIGSERIAL PRIMARY KEY,
  email        TEXT NOT NULL UNIQUE,
  full_name    TEXT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  product_id   BIGSERIAL PRIMARY KEY,
  sku          TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  price_cents  INTEGER NOT NULL CHECK(price_cents >= 0),
  active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
  order_id     BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  total_cents  INTEGER NOT NULL DEFAULT 0 CHECK(total_cents >= 0)
);

CREATE TABLE order_items (
  order_id     BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id   BIGINT NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
  qty          INTEGER NOT NULL CHECK (qty > 0),
  price_cents  INTEGER NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (order_id, product_id)
);

-- Индексы для типовых выборок
CREATE INDEX idx_orders_customer     ON orders(customer_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_active     ON products(active);

-- ---------------------------------------------------------
-- Тестовые данные
-- ---------------------------------------------------------
INSERT INTO customers (email, full_name) VALUES
('alice@example.com','Alice A.'),
('bob@example.com','Bob B.'),
('carol@example.com','Carol C.');

INSERT INTO products (sku, name, price_cents, active) VALUES
('SKU-KEYB','Keyboard', 3999, TRUE),
('SKU-MOUS','Mouse',    1999, TRUE),
('SKU-MONI','Monitor', 12999, TRUE),
('SKU-USBH','USB Hub',  2499, FALSE);  -- неактивный, для примеров

-- Заказ #1 для Alice: клавиатура ×1 + мышь ×2
WITH o AS (
  INSERT INTO orders (customer_id) 
  VALUES ((SELECT customer_id FROM customers WHERE email='alice@example.com'))
  RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, qty, price_cents)
SELECT o.order_id, p.product_id, x.qty, p.price_cents
FROM o
JOIN LATERAL (
  VALUES ('SKU-KEYB', 1),
         ('SKU-MOUS', 2)
) AS x(sku, qty) ON TRUE
JOIN products p ON p.sku = x.sku;

-- Пересчёт суммы заказа #1
UPDATE orders o SET total_cents = s.sum_cents
FROM (
  SELECT order_id, SUM(qty * price_cents) AS sum_cents
  FROM order_items GROUP BY order_id
) s
WHERE s.order_id = o.order_id;

-- Заказ #2 для Bob: монитор ×1
WITH o AS (
  INSERT INTO orders (customer_id) 
  VALUES ((SELECT customer_id FROM customers WHERE email='bob@example.com'))
  RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, qty, price_cents)
SELECT o.order_id, p.product_id, 1, p.price_cents
FROM o
JOIN products p ON p.sku = 'SKU-MONI';

UPDATE orders o SET total_cents = s.sum_cents
FROM (
  SELECT order_id, SUM(qty * price_cents) AS sum_cents
  FROM order_items GROUP BY order_id
) s
WHERE s.order_id = o.order_id;

-- ---------------------------------------------------------
-- Быстрые проверки (должно выполниться без ошибок)
-- ---------------------------------------------------------
-- Клиенты
SELECT * FROM customers ORDER BY customer_id;

-- Заказы и суммы
SELECT o.order_id, c.email, o.total_cents, o.created_at
FROM orders o 
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

-- Позиции заказа
SELECT oi.order_id, p.sku, p.name, oi.qty, oi.price_cents, (oi.qty*oi.price_cents) AS line_sum
FROM order_items oi JOIN products p ON p.product_id = oi.product_id
ORDER BY oi.order_id, p.sku;

-- Топ-таблиц по размерам (в песочнице может вернуть пусто/скромно — это нормально)
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- ---------------------------------------------------------
-- ИМИТАЦИЯ ОШИБОК ДЛЯ ОТЛАДКИ (раскомментировать по одному)
-- ---------------------------------------------------------
-- 1) Нарушение внешнего ключа (несуществующий заказ)
-- INSERT INTO order_items (order_id, product_id, qty, price_cents)
-- VALUES (99999, (SELECT product_id FROM products LIMIT 1), 1, 1000);

-- 2) Нарушение уникальности (дубликат email)
-- INSERT INTO customers (email, full_name) VALUES ('alice@example.com','Dup');

-- 3) Нарушение CHECK (отрицательная цена)
-- INSERT INTO products (sku, name, price_cents) VALUES ('SKU-BAD','Bad', -1);

-- ---------------------------------------------------------
-- [B] ОПЦИОНАЛЬНО: РОЛИ/ПРАВА (выполнять, если разрешено CREATE ROLE)
-- ---------------------------------------------------------
-- Идея: переключаться в «урезанные» роли и ловить permission denied.

-- CREATE ROLE app_owner  NOLOGIN;
-- CREATE ROLE app_reader NOINHERIT;
-- CREATE ROLE app_writer NOINHERIT;

-- -- Передаём владение объектами «владельцу приложения», чтобы текущий пользователь
-- -- при SET ROLE не был владельцем (иначе у него всегда есть права).
-- ALTER TABLE customers    OWNER TO app_owner;
-- ALTER TABLE products     OWNER TO app_owner;
-- ALTER TABLE orders       OWNER TO app_owner;
-- ALTER TABLE order_items  OWNER TO app_owner;
-- ALTER SCHEMA lab_admindb OWNER TO app_owner;

-- -- Базовые права
-- GRANT USAGE ON SCHEMA lab_admindb TO app_reader, app_writer;
-- GRANT SELECT ON ALL TABLES IN SCHEMA lab_admindb TO app_reader, app_writer;
-- GRANT INSERT, UPDATE, DELETE ON orders, order_items TO app_writer;

-- -- Чтобы вы могли имитировать пользователей через SET ROLE,
-- -- добавим текущего пользователя в эти роли:
-- GRANT app_reader TO CURRENT_USER;
-- GRANT app_writer TO CURRENT_USER;

-- -- Тест 1: читатель не может INSERT (должна быть ОШИБКА)
-- SET ROLE app_reader;
-- INSERT INTO orders (customer_id) VALUES ((SELECT customer_id FROM customers LIMIT 1));
-- RESET ROLE;

-- -- Тест 2: писатель может менять заказы
-- SET ROLE app_writer;
-- INSERT INTO orders (customer_id) VALUES ((SELECT customer_id FROM customers LIMIT 1));
-- DELETE FROM order_items WHERE qty < 0;  -- нет строк, просто пример
-- RESET ROLE;

-- -- Убрать права и проверить отказ:
-- REVOKE SELECT ON products FROM app_reader;
-- SET ROLE app_reader;
-- SELECT * FROM products;   -- ОЖИДАЕМО: permission denied
-- RESET ROLE;

-- ---------------------------------------------------------
-- Аккуратная зачистка (если надо перезапустить с нуля)
-- ---------------------------------------------------------
-- DROP SCHEMA lab_admindb CASCADE;   -- удалит всё внутри (только для песочницы!)

Примечание 1: дополнительно создать и выполнить несколько своих оригинальных запросов - скрины приложить
Примечание 2: создать и выполнить запросы для проверки результата, скрины приложить