Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

06/16/26 00:45:35 (3 days ago)

Author:

231166

Comment:

--

DatabaseProgramming

@@ +1 @@
+= 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]