Changes between Version 2 and Version 3 of DatabaseProgramming

Timestamp:

06/15/26 20:32:17 (4 days ago)

Author:

231067

Comment:

--

DatabaseProgramming

@@ -292 +292 @@
 
 == Тригери
+
+Тригерот `trg_anti_spam_chat`, кој ја извршува тригер функцијата `fn_enforce_chat_security`, дејствува пред секое внесување на нова порака во табелата со цел да обезбеди безбедност и заштита од несакани содржини. Првиот чекор на овој механизам е да провери дали корисникот кој се обидува да ја испрати пораката е всушност заведен како студент или ментор во конкретниот разговор, со што се спречува неовластен пристап и упад од надворешни лица. Вториот чекор претставува заштита против спам која пресметува колку пораки испратил истиот корисник во тој разговор во последните 5 минути. Доколку овој број го надмине лимитот од 10 пораки, тригерот автоматски го блокира внесувањето и исфрла безбедносен исклучок.
+
+{{{
+CREATE OR REPLACE FUNCTION fn_enforce_chat_security()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_chat_owner_student BIGINT;
+    v_chat_owner_mentor BIGINT;
+    v_recent_messages INT;
+BEGIN
+    -- 1. Валидација на пристап (Дали е овластен за овој чет?)
+    SELECT StudentID, MentorID INTO v_chat_owner_student, v_chat_owner_mentor 
+    FROM Chat WHERE ID = NEW.ChatID;
+
+    IF NEW.UserID != v_chat_owner_student AND NEW.UserID != v_chat_owner_mentor THEN
+        RAISE EXCEPTION 'ГРЕШКА: Безбедносен прекршок! Корисникот % не е учесник во чет %.', NEW.UserID, NEW.ChatID;
+    END IF;
+
+    -- 2. Anti-Spam Заштита (Макс 10 пораки во 5 минути)
+    SELECT COUNT(*) INTO v_recent_messages FROM Message 
+    WHERE UserID = NEW.UserID AND ChatID = NEW.ChatID AND Timestamp > (NOW() - INTERVAL '5 minutes');
+
+    IF v_recent_messages >= 10 THEN
+        RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Го надминавте лимитот за праќање пораки. Обидете се подоцна!';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_anti_spam_chat ON Message;
+CREATE TRIGGER trg_anti_spam_chat
+BEFORE INSERT ON Message
+FOR EACH ROW EXECUTE FUNCTION fn_enforce_chat_security();
+
+
+-- ТЕСТИРАЊЕ
+INSERT INTO Message (Content, isRead, Timestamp, ChatID, UserID) VALUES ('Тест спам порака', false, NOW(), 5, 1);
+
+--INSERT INTO Chat (Title, Topic, Status, "Date", StudentID, MentorID) VALUES ('Тест разговор за спам', 'Комуникација', 0, NOW(), 500, 300);
+
+}}}
+
+----
+
+Тригерот `trg_strict_task_timeline`, придружен со функцијата `fn_guard_task_timeline`, е задолжен за одржување на временскиот интегритет на задачите и се активира пред секое внесување или ажурирање на податоците во табелата за задачи. Тој забранува поставување на краен рок кој е еднаков или пред почетниот датум на задачата. Дополнително, при креирање на сосема нова задача, тригерот не дозволува нејзиниот краен рок да биде поставен во минатото во однос на тековниот ден. Исто така, доколку се прави обид за ажурирање на задача која веќе е завршена, тригерот ја одбива промената на крајниот рок со цел да ги заклучи минатите временски рамки.
+
+{{{
+CREATE OR REPLACE FUNCTION fn_guard_task_timeline()
+RETURNS TRIGGER AS $$
+BEGIN
+    -- Дали EndDate е пред StartDate?
+    IF NEW.EndDate <= NEW.StartDate THEN
+        RAISE EXCEPTION 'ГРЕШКА: Крајниот рок мора да биде по почетниот датум!';
+    END IF;
+
+    -- Ако е ВНЕСУВАЊЕ, не смее крајниот рок да е во минатото
+    IF TG_OP = 'INSERT' AND NEW.EndDate < CURRENT_DATE THEN
+        RAISE EXCEPTION 'ГРЕШКА: Не можете да креирате нова задача со краен рок во минатото!';
+    END IF;
+
+    -- Ако е АЖУРИРАЊЕ и задачата е веќе завршена
+    IF TG_OP = 'UPDATE' AND OLD.Status = 1 AND NEW.EndDate != OLD.EndDate THEN
+        RAISE EXCEPTION 'ОДБИЕНО: Задачата е веќе завршена. Временските рамки се заклучени!';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_strict_task_timeline ON Task;
+CREATE TRIGGER trg_strict_task_timeline
+BEFORE INSERT OR UPDATE ON Task
+FOR EACH ROW EXECUTE FUNCTION fn_guard_task_timeline();
+
+
+-- ТЕСТИРАЊЕ
+INSERT INTO Task (MentorshipID, description , StartDate, EndDate, Status) VALUES (1, 'Невалидна Задача', NOW(), NOW() - INTERVAL '7 days', 0);
+
+}}}
+
+----
+
+Тригерот `trg_topic_state_machine` ја извршува функцијата `fn_topic_state_guard` пред секое ажурирање на податоците во табелата за предложени теми за менторство со цел да го контролира и заштити животниот циклус на темите. При обид за менување на матичниот предмет на кој му припаѓа веќе креирана тема, овој механизам ја прекинува операцијата и исфрла грешка за да спречи конфузија во наставната програма. Дополнително, тригерот спречува рачно менување на статусот на темата од зафатена во слободна доколку таа тема сè уште е поврзана со некое активно менторство кое содржи незавршени задачи, со што се гарантира дека темите нема предвремено и нелогично да се ослободат во системот.
+
+{{{
+
+CREATE OR REPLACE FUNCTION fn_topic_state_guard()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_active_mentorships INT;
+BEGIN
+    -- Забрането менување на предмет откако темата е креирана
+    IF TG_OP = 'UPDATE' AND OLD.SubjectID != NEW.SubjectID THEN
+        RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Откако темата е предложена, забрането е менување на нејзиниот предмет!';
+    END IF;
+
+    -- Ако некој рачно пробува да ја ослободи темата (isAvailable -> true)
+    IF TG_OP = 'UPDATE' AND OLD.isAvailable = false AND NEW.isAvailable = true THEN
+        SELECT COUNT(*) INTO v_active_mentorships FROM Mentorship m 
+        JOIN Task t ON m.ID = t.MentorshipID
+        WHERE m.TopicSuggestionID = NEW.ID AND t.Status != 1;
+        
+        IF v_active_mentorships > 0 THEN
+            RAISE EXCEPTION 'ПРЕДУПРЕДУВАЊЕ: Темата не може да се ослободи бидејќи сè уште е поврзана со активно менторство!';
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_topic_state_machine ON TopicSuggestion;
+CREATE TRIGGER trg_topic_state_machine
+BEFORE UPDATE ON TopicSuggestion
+FOR EACH ROW EXECUTE FUNCTION fn_topic_state_guard();
+
+-- ТЕСТИРАЊЕ 
+UPDATE TopicSuggestion SET SubjectID = 99999 WHERE ID = (SELECT ID FROM TopicSuggestion LIMIT 1);
+UPDATE TopicSuggestion SET isAvailable = true WHERE ID = (SELECT TopicSuggestionID FROM Mentorship LIMIT 1);
+--UPDATE TopicSuggestion SET isAvailable = true WHERE ID = (SELECT m.TopicSuggestionID FROM Mentorship m JOIN Task t ON m.ID = t.MentorshipID WHERE t.Status != 1 LIMIT 1);
+
+}}}