Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by 231166, 2 days ago) ( diff )
--

DatabaseProgramming: Имплементација на Функции, Тригери и Процедури

Оваа страница го деталниот преглед на програмските објекти креирани на ниво на базата на податоци. Преку нивна имплементација, деловната логика на стриминг платформата е централизирана директно на серверот. Ова гарантира максимална безбедност, заштита на податочниот интегритет и драстично намалување на мрежниот сообраќај помеѓу апликацијата и базата.

---

1. Кориснички дефинирани функции (User-Defined Functions)

Функциите во системот овозможуваат брзо пресметување, агрегирање и деловна обработка на податоците, враќајќи готови резултати до корисничкиот интерфејс без потреба од повторливи JOIN операции во апликативниот код.

Имплементирани од Кристијан Атанасов

1. get_avg_rating(p_content_id)

Опис и примена: Ја пресметува средната вредност на сите рејтинзи за одредена содржина и ја заокружува на две децимали. Се користи при пребарување и прикажување на картичките за филмови/серии на почетната страна на корисничкиот интерфејс.
```
CREATE FUNCTION get_avg_rating(p_content_id INT)
RETURNS NUMERIC AS $$
DECLARE
  v_avg NUMERIC;
BEGIN
  SELECT ROUND(AVG(RatingValue), 2)
  INTO v_avg
  FROM Rating
  WHERE ContentContentID = p_content_id;

  RETURN v_avg;
END;
$$ LANGUAGE plpgsql;
```

2. get_user_watch_count(p_user_id)

Опис и примена: Враќа вкупен број на содржини кои некој корисник ги проследил. Ова е критично за генерирање на кориснички профилни статистики и за алгоритмите за препорака (Recommendation Engines).
```
CREATE FUNCTION get_user_watch_count(p_user_id INT)
RETURNS INT AS $$
DECLARE
  v_count INT;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM WatchHistory
  WHERE UserUserID = p_user_id;

  RETURN v_count;
END;
$$ LANGUAGE plpgsql;
```

3. get_content_genres(p_content_id)

Опис и примена: Наместо апликацијата постојано да прави комплексни спојувања со табелата за врски Content_Genre и матичната Genre, оваа функција директно ги враќа сите жанрови за даден медиум споени во една текстуална низа (String).
```
CREATE FUNCTION get_content_genres(p_content_id INT)
RETURNS VARCHAR AS $$
DECLARE
  v_genres VARCHAR;
BEGIN
  SELECT STRING_AGG(g.Name, ', ' ORDER BY g.Name)
  INTO v_genres
  FROM Genre g
  JOIN Content_Genre cg ON cg.GenreGenreID = g.GenreID
  WHERE cg.ContentContentID = p_content_id;

  RETURN COALESCE(v_genres, 'No genres found');
END;
$$ LANGUAGE plpgsql;
```

Имплементирани од Дамјан Димовски

4. get_subscription_info(p_user_id)

Опис и примена: Враќа табеларан приказ со детали за активниот претплатнички план на корисникот. Оваа функција ја повикува безбедносниот систем (Middleware) при секое пуштање на видео за да потврди дали корисникот платил и какви се неговите привилегии.
```
CREATE FUNCTION get_subscription_info(p_user_id INT)
RETURNS TABLE(plan_name VARCHAR, price NUMERIC, max_devices INT, status VARCHAR) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.Name,
    s.Price,
    s.MaxDevices,
    us.Status
  FROM User_Subscription us
  JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
  WHERE us.UserUserID = p_user_id
  LIMIT 1;
END;
$$ LANGUAGE plpgsql;
```

5. get_series_episode_count(p_series_id)

Опис и примена: Го пресметува вкупниот број на епизоди во сите сезони за една конкретна серија. Се користи во корисничкиот интерфејс за да се прикаже информација од типот: *"Серијата содржи вкупно 48 епизоди"*.
```
CREATE FUNCTION get_series_episode_count(p_series_id INT)
RETURNS INT AS $$
DECLARE
  v_count INT;
BEGIN
  SELECT COUNT(e.EpisodeID)
  INTO v_count
  FROM Episode e
  JOIN Season sea ON sea.SeasonID = e.SeasonSeasonID
  WHERE sea.SeriesSeriesID = p_series_id;

  RETURN v_count;
END;
$$ LANGUAGE plpgsql;
```

6. get_top_content_by_genre(p_genre_name)

Опис и примена: Враќа динамичка табела од топ 10 најдобро оценети содржини кои припаѓаат на специфичен жанр. Идеално за прикажување на персонализирани ленти со содржини (на пр. *"Топ 10 Sci-Fi Филмови"*).

CREATE FUNCTION get_top_content_by_genre(p_genre_name VARCHAR)
RETURNS TABLE(title VARCHAR, avg_rating NUMERIC, total_ratings BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    m.title,
    ROUND(AVG(r.RatingValue), 2) AS avg_rating,
    COUNT(r.RatingID) AS total_ratings
  FROM Media m
  JOIN Content_Genre cg ON cg.ContentContentID = m.ContentID
  JOIN Genre g ON g.GenreID = cg.GenreGenreID
  JOIN Rating r ON r.ContentContentID = m.ContentID
  WHERE g.Name = p_genre_name
  GROUP BY m.title
  ORDER BY avg_rating DESC
  LIMIT 10;
END;
$$ LANGUAGE plpgsql;

---

2. Базни тригери (Database Triggers)

Тригерите во системот гарантираат автоматско извршување на логиката во заднина и го штитат интегритетот на податоците без разлика на начинот на кој апликацијата комуницира со базата.

Имплементирани од Кристијан Атанасов

1. Логирање на избришани корисници (`trigger_log_user_delete`)

Зошто постои во реален свет: Поради безбедносни ревизии (Auditing), системот мора да зачува трага кога и кој профил бил отстранет, без притоа да ги чува лозинките и сензитивните податоци на корисникот.

CREATE TABLE IF NOT EXISTS User_Delete_Log (
  LogID      SERIAL PRIMARY KEY,
  UserID     INT,
  Username   VARCHAR(255),
  Email      VARCHAR(255),
  DeletedAt  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION trg_log_user_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO User_Delete_Log (UserID, Username, Email)
  VALUES (OLD.UserID, OLD.Username, OLD.Email);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_user_delete
BEFORE DELETE ON "User"
FOR EACH ROW
EXECUTE FUNCTION trg_log_user_delete();

2. Валидација на опсег на рејтинг (`trigger_validate_rating`)

Зошто постои во реален свет: Оневозможува малициозни корисници или системски багови на фронтендот да внесат невалиден рејтинг (на пр. оцена 15 или -5) кој би ја нарушил комплетната статистика на платформата. Вредноста строго мора да биде помеѓу 1 и 10.
```
CREATE FUNCTION trg_validate_rating()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.RatingValue < 1 OR NEW.RatingValue > 10 THEN
    RAISE EXCEPTION 'RatingValue mora da bide pomegu 1 i 10, vnesena vrednost: %', NEW.RatingValue;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_validate_rating
BEFORE INSERT OR UPDATE ON Rating
FOR EACH ROW
EXECUTE FUNCTION trg_validate_rating();
```

3. Автоматско комплетирање на прогрес (`trigger_auto_complete_progress`)

Зошто постои во реален свет: Кога корисникот ќе стигне до одреден висок процент од видеото (90% или повеќе), системот претпоставува дека ја изгледал целата содржина (вклучувајќи ги кредитите) и автоматски го поставува прогресот на 100% за да се отстрани од корисничката лента "Продолжи со гледање".
```
CREATE FUNCTION trg_auto_complete_progress()
RETURNS TRIGGER AS $$
DECLARE
  v_duration INT;
BEGIN
  SELECT duration INTO v_duration
  FROM Watchable
  WHERE WatchableID = NEW.WatchableWatchableID;

  IF NEW.Progress_percentage >= 90 THEN
    NEW.Progress_percentage := 100;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_complete_progress
BEFORE INSERT ON WatchHistory
FOR EACH ROW
EXECUTE FUNCTION trg_auto_complete_progress();
```

Имплементирани од Дамјан Димовски

4. Автоматско истекување на претплата (`trigger_auto_expire_subscription`)

Зошто постои во реален свет: Спречува корисници со поминат рок на картичката или откажана претплата да продолжат бесплатно да го користат сервисот. При секој обид за промена на претплатата, ако End_date е помал од денешниот датум, статусот инстант станува Expired.
```
CREATE FUNCTION trg_auto_expire_subscription()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.End_date IS NOT NULL AND NEW.End_date < CURRENT_DATE AND NEW.Status != 'Expired' THEN
    NEW.Status := 'Expired';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_expire_subscription
BEFORE UPDATE ON User_Subscription
FOR EACH ROW
EXECUTE FUNCTION trg_auto_expire_subscription();
```

5. Контрола на максимален број уреди (`trigger_check_max_devices`)

Зошто постои во реален свет: Ја штити бизнис логиката на компанијата од нелегално споделување на лозинки. Ако корисникот има „Basic“ пакет кој дозволува само 1 уред, а се обиде да се најави од втор уред, тригерот фрла исклучок и го блокира внесувањето во табелата Devices.

CREATE FUNCTION trg_check_max_devices()
RETURNS TRIGGER AS $$
DECLARE
  v_max_devices INT;
  v_current_devices INT;
BEGIN
  SELECT s.MaxDevices INTO v_max_devices
  FROM User_Subscription us
  JOIN Subscription s ON s.SubscriptionID = us.SubscriptionSubscriptionID
  WHERE us.UserSubscriptionID = NEW.UserSubscriptionID;

  SELECT COUNT(*) INTO v_current_devices
  FROM Devices
  WHERE UserSubscriptionID = NEW.UserSubscriptionID;

  IF v_current_devices >= v_max_devices THEN
    RAISE EXCEPTION 'Maksimalen broj na uredi e dostигнат: % od %', v_current_devices, v_max_devices;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_max_devices
BEFORE INSERT ON Devices
FOR EACH ROW
EXECUTE FUNCTION trg_check_max_devices();

6. Логови за статус на претплати (`trigger_log_subscription_status_change`)

Зошто постои во реален свет: Ова овозможува финансиска ревизија. Кога претплатата ќе премине од Active во Cancelled или Suspended, секоја промена мора хронолошки да се зачува за корисничката поддршка да може да види историја на трансакции.

CREATE TABLE Subscription_Status_Log (
  LogID       SERIAL PRIMARY KEY,
  UserSubID   INT,
  UserID      INT,
  OldStatus   VARCHAR(255),
  NewStatus   VARCHAR(255),
  ChangedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION trg_log_subscription_status_change()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.Status <> NEW.Status THEN
    INSERT INTO Subscription_Status_Log (UserSubID, UserID, OldStatus, NewStatus)
    VALUES (OLD.UserSubscriptionID, OLD.UserUserID, OLD.Status, NEW.Status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_subscription_status_change
AFTER UPDATE ON User_Subscription
FOR EACH ROW
EXECUTE FUNCTION trg_log_subscription_status_change();

---

3. Зачувани Процедури (Stored Procedures)

За разлика од функциите, процедурите се користат за извршување на комплексни деловни трансакции кои модифицираат повеќе табели одеднаш по принципот "сè или ништо" (Трансакциска сигурност).

Имплементирани од Кристијан Атанасов

1. Регистрација на нов корисник со почетна претплата (`sp_register_user`)

Зошто постои во реален свет: Кога се регистрирате на стриминг сервис, тоа не е обичен единечен INSERT. Мора истовремено да се креира корисничкиот профил, да му се креира инстанца за претплата во User_Subscription и да му се постави почетен рок од 30 дена. Оваа процедура го извршува тоа како една атомска операција.

CREATE OR REPLACE PROCEDURE sp_register_user(
  p_first_name   VARCHAR,
  p_last_name    VARCHAR,
  p_username     VARCHAR,
  p_email        VARCHAR,
  p_password     VARCHAR,
  p_subscription_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_user_id INT;
BEGIN
  INSERT INTO "User" (FirstName, LastName, Username, Email, password, Date_registered)
  VALUES (p_first_name, p_last_name, p_username, p_email, p_password, CURRENT_DATE)
  RETURNING UserID INTO v_user_id;

  INSERT INTO User_Subscription (UserUserID, SubscriptionSubscriptionID, Start_date, End_date, Status, Auto_renew)
  VALUES (v_user_id, p_subscription_id, CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', 'Active', 1);

  RAISE NOTICE 'Korisnikot % % uspeshno registriran so UserID = %', p_first_name, p_last_name, v_user_id;

EXCEPTION
  WHEN unique_violation THEN
    RAISE EXCEPTION 'Email % veke postoi vo sistemot.', p_email;
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Subscription so ID % ne postoi.', p_subscription_id;
END;
$$;

2. Додавање во watchlist со заштита од дупликати (`sp_add_to_watchlist`)

Зошто постои во реален свет: Наместо апликацијата прво да прави SELECT за да провери дали корисникот веќе го додал филмот во "Моја Листа", процедурата на ниво на база ја врши таа проверка и спречува појава на дупликат записи, оптимизирајќи ги перформансите на серверот.

CREATE OR REPLACE PROCEDURE sp_add_to_watchlist(
  p_user_id    INT,
  p_content_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_exists INT;
BEGIN
  SELECT COUNT(*) INTO v_exists
  FROM Watchlist
  WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

  IF v_exists > 0 THEN
    RAISE NOTICE 'Sodrzhinata veke e vo watchlist-ot na korisnikot.';
    RETURN;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM Media WHERE ContentID = p_content_id) THEN
    RAISE EXCEPTION 'Sodrzhinata so ID % ne postoi.', p_content_id;
  END IF;

  INSERT INTO Watchlist (UserUserID, ContentContentID, dateAdded)
  VALUES (p_user_id, p_content_id, CURRENT_DATE);

  RAISE NOTICE 'Sodrzhinata % dodadena vo watchlist za korisnik %.', p_content_id, p_user_id;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Korisnik % ili sodrzina % ne postoi.', p_user_id, p_content_id;
END;
$$;

Имплементирани од Дамјан Димовски

3. Снимање на кориснички прогрес и рејтинг нотификација (`sp_record_watch`)

Зошто постои во реален свет: При секое стиснување на копчето пауза или при исклучување на видеото, апликацијата во позадина го праќа моменталниот тренд на гледање. Оваа процедура го запишува тоа во WatchHistory, соработува со тригерот за 90% комплетирање, и доколку корисникот го изгледал филмот, испраќа известување за добивање рејтинг (точно како познатите поп-ап прозорци на стриминг сервисите).

CREATE OR REPLACE PROCEDURE sp_record_watch(
  p_user_id      INT,
  p_content_id   INT,
  p_watchable_id INT,
  p_device_id    INT,
  p_progress     INT
)
LANGUAGE plpgsql AS $$
BEGIN
  IF p_progress < 0 OR p_progress > 100 THEN
    RAISE EXCEPTION 'Progress mora da bide pomegu 0 i 100.';
  END IF;

  INSERT INTO WatchHistory (WatchedAt, Progress_percentage, UserUserID, ContentContentID, WatchableWatchableID, DevicesDeviceID)
  VALUES (CURRENT_DATE, p_progress, p_user_id, p_content_id, p_watchable_id, p_device_id);

  IF p_progress >= 90 THEN
    IF NOT EXISTS (
      SELECT 1 FROM Rating
      WHERE UserUserID = p_user_id AND ContentContentID = p_content_id
    ) THEN
      RAISE NOTICE 'Korisnik % ja zavrsil sodrzhinata %. Pokanete go da ostavi rejting.', p_user_id, p_content_id;
    END IF;
  END IF;

EXCEPTION
  WHEN foreign_key_violation THEN
    RAISE EXCEPTION 'Nevaliden user, content, watchable ili device ID.';
END;
$$;

4. Паметно поднесување и ажурирање на рејтинг (`sp_submit_rating`)

Зошто постои во реален свет: Оваа процедура воведува две клучни деловни правила: Прво, спречува корисникот да остави оцена за филм кој воопшто никогаш не го пуштил (нема запис во WatchHistory). Второ, таа е паметна (Upsert логика) - ако корисникот веќе оценил, ќе се изврши UPDATE, а ако оценува за првпат, се прави нов INSERT.

CREATE OR REPLACE PROCEDURE sp_submit_rating(
  p_user_id    INT,
  p_content_id INT,
  p_rating     INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_watched INT;
BEGIN
  SELECT COUNT(*) INTO v_watched
  FROM WatchHistory
  WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

  IF v_watched = 0 THEN
    RAISE EXCEPTION 'Korisnikot ne ja gledal sodrzhinata i ne moze da ostavi rejting.';
  END IF;

  IF EXISTS (SELECT 1 FROM Rating WHERE UserUserID = p_user_id AND ContentContentID = p_content_id) THEN
    UPDATE Rating
    SET RatingValue = p_rating, Rating_Date = CURRENT_DATE
    WHERE UserUserID = p_user_id AND ContentContentID = p_content_id;

    RAISE NOTICE 'Rejtingot azhurirani na % za sodrzina %.', p_rating, p_content_id;
  ELSE
    INSERT INTO Rating (Rating_Date, RatingValue, UserUserID, ContentContentID)
    VALUES (CURRENT_DATE, p_rating, p_user_id, p_content_id);

    RAISE NOTICE 'Nov rejting % vnesen za sodrzina %.', p_rating, p_content_id;
  END IF;

EXCEPTION
  WHEN check_violation THEN
    RAISE EXCEPTION 'Rejtingot mora da bide pomegu 1 i 10.';
END;
$$;

5. Комплетна деактивирање на кориснички профил (`sp_deactivate_user`)

Зошто постои во реален свет: Кога корисникот ќе избере опција "Откажи ја сметката", процесот во заднина не смее да остави сирачиња во базата (orphan records). Оваа процедура ги исклучува сите негови активни претплати во User_Subscription, ги исфрла и брише сите негови активни сесии на уреди од табелата Devices и прави безбедно затворање на сметката.

CREATE OR REPLACE PROCEDURE sp_deactivate_user(
  p_user_id INT
)
LANGUAGE plpgsql AS $$
DECLARE
  v_count INT;
BEGIN
  IF NOT EXISTS (SELECT 1 FROM "User" WHERE UserID = p_user_id) THEN
    RAISE EXCEPTION 'Korisnik so ID % ne postoi.', p_user_id;
  END IF;

  SELECT COUNT(*) INTO v_count
  FROM User_Subscription
  WHERE UserUserID = p_user_id AND Status = 'Active';

  UPDATE User_Subscription
  SET Status = 'Cancelled', End_date = CURRENT_DATE
  WHERE UserUserID = p_user_id AND Status = 'Active';

  DELETE FROM Devices
  WHERE UserSubscriptionID IN (
    SELECT UserSubscriptionID FROM User_Subscription
    WHERE UserUserID = p_user_id
  );

  RAISE NOTICE 'Korisnik % deaktiviran. % pretplati otkazani, uredite izbrishani.', p_user_id, v_count;

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Greshka pri deaktiviranje na korisnik %: %', p_user_id, SQLERRM;
END;
$$;

Целосниот код за наште тригери,процедури и функции може да го погледнете тука: console_12.sql console_13.sql

Attachments (2)

console_12.sql (9.4 KB ) - added by 231166 2 days ago.
console_13.sql (10.4 KB ) - added by 231166 2 days ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text

Context Navigation

DatabaseProgramming: Имплементација на Функции, Тригери и Процедури

1. Кориснички дефинирани функции (User-Defined Functions)

Имплементирани од Кристијан Атанасов

1. get_avg_rating(p_content_id)

2. get_user_watch_count(p_user_id)

3. get_content_genres(p_content_id)

Имплементирани од Дамјан Димовски

4. get_subscription_info(p_user_id)

5. get_series_episode_count(p_series_id)

6. get_top_content_by_genre(p_genre_name)

2. Базни тригери (Database Triggers)

Имплементирани од Кристијан Атанасов

1. Логирање на избришани корисници (trigger_log_user_delete)

2. Валидација на опсег на рејтинг (trigger_validate_rating)

3. Автоматско комплетирање на прогрес (trigger_auto_complete_progress)

Имплементирани од Дамјан Димовски

4. Автоматско истекување на претплата (trigger_auto_expire_subscription)

5. Контрола на максимален број уреди (trigger_check_max_devices)

6. Логови за статус на претплати (trigger_log_subscription_status_change)

3. Зачувани Процедури (Stored Procedures)

Имплементирани од Кристијан Атанасов

1. Регистрација на нов корисник со почетна претплата (sp_register_user)

2. Додавање во watchlist со заштита од дупликати (sp_add_to_watchlist)

Имплементирани од Дамјан Димовски

3. Снимање на кориснички прогрес и рејтинг нотификација (sp_record_watch)

4. Паметно поднесување и ажурирање на рејтинг (sp_submit_rating)

5. Комплетна деактивирање на кориснички профил (sp_deactivate_user)

Attachments (2)

Download in other formats:

1. Логирање на избришани корисници (`trigger_log_user_delete`)

2. Валидација на опсег на рејтинг (`trigger_validate_rating`)

3. Автоматско комплетирање на прогрес (`trigger_auto_complete_progress`)

4. Автоматско истекување на претплата (`trigger_auto_expire_subscription`)

5. Контрола на максимален број уреди (`trigger_check_max_devices`)

6. Логови за статус на претплати (`trigger_log_subscription_status_change`)

1. Регистрација на нов корисник со почетна претплата (`sp_register_user`)

2. Додавање во watchlist со заштита од дупликати (`sp_add_to_watchlist`)

3. Снимање на кориснички прогрес и рејтинг нотификација (`sp_record_watch`)

4. Паметно поднесување и ажурирање на рејтинг (`sp_submit_rating`)

5. Комплетна деактивирање на кориснички профил (`sp_deactivate_user`)