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

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

---

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

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

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

==== 1. get_avg_rating(p_content_id) ====
* '''Опис и примена:''' Ја пресметува средната вредност на сите рејтинзи за одредена содржина и ја заокружува на две децимали. Се користи при пребарување и прикажување на картичките за филмови/серии на почетната страна на корисничкиот интерфејс.
{{{
#!sql
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).
{{{
#!sql
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).
{{{
#!sql
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) при секое пуштање на видео за да потврди дали корисникот платил и какви се неговите привилегии.
{{{
#!sql
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 епизоди"*.
{{{
#!sql
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 Филмови"*).
{{{
#!sql
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), системот мора да зачува трага кога и кој профил бил отстранет, без притоа да ги чува лозинките и сензитивните податоци на корисникот.
{{{
#!sql
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.
{{{
#!sql
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% за да се отстрани од корисничката лента "Продолжи со гледање".
{{{
#!sql
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`.
{{{
#!sql
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`.
{{{
#!sql
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`, секоја промена мора хронолошки да се зачува за корисничката поддршка да може да види историја на трансакции.
{{{
#!sql
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 дена. Оваа процедура го извршува тоа како една атомска операција.
{{{
#!sql
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` за да провери дали корисникот веќе го додал филмот во "Моја Листа", процедурата на ниво на база ја врши таа проверка и спречува појава на дупликат записи, оптимизирајќи ги перформансите на серверот.
{{{
#!sql
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% комплетирање, и доколку корисникот го изгледал филмот, испраќа известување за добивање рејтинг (точно како познатите поп-ап прозорци на стриминг сервисите).
{{{
#!sql
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`.
{{{
#!sql
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` и прави безбедно затворање на сметката.
{{{
#!sql
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;
$$;
}}}

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