Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 4 (modified by 231175, 29 hours ago) ( diff )
--

Напреден развој на базата

Правила за запишување на курс (Enrollment)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

Кога корисникот се запишува на курс по даден course_id, автоматски се избира и запишува моментално активната верзија на курсот (course_version.active = true) за тој курс.
Не е дозволено запишување на курс за кој воопшто нема активна верзија.
Секоја верзија се третира независно: корисникот може да има повеќе запишувања на различни верзии на истиот курс, без разлика дали претходните се завршени или не.

Имплементација

Тригери

BEFORE INSERT тригер на enrollment за автоматско доделување на активната верзија на curse (course version).

CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
RETURNS TRIGGER 
AS $$
DECLARE
    v_course_version_id INTEGER;
BEGIN
    SELECT cv.id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = NEW.course_id
      AND cv.active = TRUE
    ORDER BY cv.version_number DESC
    LIMIT 1;
    
    IF v_course_version_id IS NULL THEN
        RAISE EXCEPTION 'No active course version found for course_id=%', NEW.course_id;
    END IF;
    
    NEW.course_version_id := v_course_version_id;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_set_active_course_version_on_enrollment
BEFORE INSERT ON enrollment
FOR EACH ROW
EXECUTE FUNCTION set_active_course_version_on_enrollment();

Функции / Stored Procedures

Функција за креирање на нов enrollment. При креирање се зема најновата активна верзија на курсот.

CREATE OR REPLACE FUNCTION create_enrollment_for_active_version(p_user_id INTEGER, p_course_id INTEGER)
RETURNS INTEGER 
AS $$
DECLARE
    v_course_version_id INTEGER;
    v_enrollment_id INTEGER;
BEGIN
    SELECT cv.id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = p_course_id
      AND cv.active = TRUE
    ORDER BY cv.version_number DESC
    LIMIT 1;
    
    IF v_course_version_id IS NULL THEN
        RAISE EXCEPTION 'No active course version found for course_id=%', p_course_id;
    END IF;
    
    INSERT INTO enrollment (user_id, course_id, course_version_id, enrollment_purchase_date, status)
    VALUES (p_user_id, p_course_id, v_course_version_id, NOW(), 'PENDING')
    RETURNING id INTO v_enrollment_id;
    
    RETURN v_enrollment_id;
END;
$$ 
LANGUAGE plpgsql;

Погледи (Views)

Поглед за генерирање на основни информации за кои enrollments имаат застарена верзија од курсот, а кои најнова.

CREATE OR REPLACE VIEW enrollments_with_active_version AS
SELECT
    e.id AS enrollment_id,
    u.id AS user_id,
    u.name AS user_name,
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.id AS course_version_id,
    cv.version_number AS course_version_number,
    cv.active AS is_version_active,
    e.enrollment_purchase_date AS purchase_date,
    e.status AS enrollment_status
FROM enrollment e
JOIN user u ON e.user_id = u.id
JOIN course c ON e.course_id = c.id
JOIN course_version cv ON e.course_version_id = cv.id
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk';

Менаџирање на верзии на курс (Course Version Management)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

Само една верзија може да биде активна (active = true) по курс во исто време
Кога се активира нова верзија, претходните активни верзии на истиот курс автоматски се деактивираат
Не смее да се избрише верзија која има активни (незавршени) запишувања (enrollments)

Имплементација

Тригери

BEFORE INSERT тригер во course_version, каде само таа верзија што се креира е active, останатите не се. Дополнително се ажурираат и уште некои атрибути.

CREATE OR REPLACE FUNCTION ensure_single_active_version_when_created()
RETURNS TRIGGER 
AS $$
DECLARE
    v_num_new_version INTEGER;
BEGIN
    UPDATE course_version
    SET active = FALSE
    WHERE course_id = NEW.course_id;

    SELECT COALESCE(MAX(version_number), 0) + 1 INTO v_num_new_version
    FROM course_version
    WHERE course_id = NEW.course_id;

    NEW.active := TRUE;
    NEW.creation_date := CURRENT_DATE;
    NEW.version_number := v_num_new_version;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_ensure_single_active_version_when_created
BEFORE INSERT ON course_version
FOR EACH ROW
EXECUTE FUNCTION ensure_single_active_version_when_created();

BEFORE UPDATE тригер за атрибутот active во course_version, со цел осигурување дека постои само една активна верзија од курсот.

CREATE OR REPLACE FUNCTION ensure_single_active_version()
RETURNS TRIGGER 
AS $$
BEGIN
    IF NEW.active = TRUE THEN
        UPDATE course_version
        SET active = FALSE
        WHERE course_id = NEW.course_id
          AND id != NEW.id
          AND active = TRUE;
    END IF;
    
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_ensure_single_active_version
BEFORE UPDATE OF active ON course_version
FOR EACH ROW
WHEN (NEW.active = TRUE)
EXECUTE FUNCTION ensure_single_active_version();

BEFORE DELETE тригер за course version, со цел осигурување дека course version не може да биде избришано доколку веќе постојат enrollments за него.

CREATE OR REPLACE FUNCTION prevent_version_deletion_with_active_enrollments()
RETURNS TRIGGER 
AS $$
DECLARE
    v_active_enrollments INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_active_enrollments
    FROM enrollment
    WHERE course_version_id = OLD.id;
    
    IF v_active_enrollments > 0 THEN
        RAISE EXCEPTION 'Cannot delete course version with % active enrollments', v_active_enrollments;
    END IF;
    
    RETURN OLD;
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_version_deletion
BEFORE DELETE ON course_version
FOR EACH ROW
EXECUTE FUNCTION prevent_version_deletion_with_active_enrollments();

Погледи (Views)

Поглед за преглед на курсевите со нивните најнови верзии и enrollments.

CREATE OR REPLACE VIEW course_latest_versions AS
SELECT
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.id AS version_id,
    cv.version_number,
    cv.active,
    cv.version_creation_date,
    COUNT(DISTINCT e.id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
    COUNT(DISTINCT e.id) AS total_enrollments
FROM course c
JOIN course_version cv ON c.id = cv.course_id
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
LEFT JOIN enrollment e ON cv.id = e.course_version_id
WHERE cv.version_number = (
    SELECT MAX(version_number)
    FROM course_version
    WHERE course_id = c.id
)
GROUP BY c.id, ct.title_short, cv.id, cv.version_number, cv.active, cv.version_creation_date;

Валидација на рецензии и рејтинг (Review Validation & Rating)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

Корисникот може да остави рецензија само ако enrollment е завршен (completion_date IS NOT NULL)
Еден корисник може да остави само една рецензија по enrollment
Рејтингот мора да биде валиден број од 1 до 5
Сите промени на рецензии се евидентираат во audit табела

Имплементација

Прилагодени домени (Custom Domains)

Ограничување дека рејтинг мора да биде во доменот [1, 5]

CREATE DOMAIN rating_scale AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;

Тригери

BEFORE INSERT тригер за review, каде се прекинува внесувањето на review доколку курсот (enrollment) не е завршен.

CREATE OR REPLACE FUNCTION validate_review_before_insert()
RETURNS TRIGGER AS $$
DECLARE
    v_completion_date TIMESTAMP;
    v_existing_review_count INTEGER;
BEGIN
    SELECT completion_date INTO v_completion_date
    FROM enrollment
    WHERE id = NEW.enrollment_id;
    
    IF v_completion_date IS NULL THEN
        RAISE EXCEPTION 'Cannot create review for incomplete enrollment';
    END IF;
    
    SELECT COUNT(*) INTO v_existing_review_count
    FROM review
    WHERE enrollment_id = NEW.enrollment_id;
    
    IF v_existing_review_count > 0 THEN
        RAISE EXCEPTION 'Review already exists for this enrollment';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_review
BEFORE INSERT ON review
FOR EACH ROW
EXECUTE FUNCTION validate_review_before_insert();

Погледи (Views)

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

CREATE OR REPLACE VIEW course_average_ratings AS
SELECT
    c.id AS course_id,
    ct.title_short AS course_title,
    COUNT(r.id) AS total_reviews,
    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
    COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 3) AS three_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 2) AS two_star_count,
    COUNT(r.id) FILTER (WHERE r.rating = 1) AS one_star_count
FROM course c
JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
LEFT JOIN course_version cv ON c.id = cv.course_id
LEFT JOIN enrollment e ON cv.id = e.course_version_id
LEFT JOIN review r ON e.id = r.enrollment_id
GROUP BY c.id, ct.title_short;

Следење и ограничување на бесплатни консултации

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

Корисникот може да користи само една бесплатна консултација
Може да се креира meeting_reminder за бесплатна консултација само ако корисникот сè уште не ја искористил
После креирање на meeting_reminder за бесплатна консултација, флагот used_free_consultation автоматски се поставува на TRUE

Имплементација

Тригери

BEFORE INSERT тригер на meeting reminder, каде не смее да се креира нов митинг и потсетување по емаил доколку корисникот веќе имал бесплатна консултација.

CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT used_free_consultation INTO v_used_free_consultation
    FROM _user
    WHERE id = NEW.user_id;
    
    IF v_used_free_consultation = TRUE THEN
        RAISE EXCEPTION 'User has already used their free consultation';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_free_consultation_before_meeting
BEFORE INSERT ON meeting_reminder
FOR EACH ROW
EXECUTE FUNCTION check_free_consultation_eligibility();

AFTER INSERT тригер на meeting reminder, каде одкако ќе се закаже состанокот да се маркира дека тој корисник го има искористено своето право за бесплатна консултативна сесија со експерт.

CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE _user
    SET used_free_consultation = TRUE
    WHERE id = NEW.user_id
      AND used_free_consultation = FALSE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_mark_free_consultation_used
AFTER INSERT ON meeting_reminder
FOR EACH ROW
EXECUTE FUNCTION mark_free_consultation_as_used();

Функции / Stored Procedures

Функција која кажува дали корисникот може да закаже бесплатна консултативна сесија со експерт.

CREATE OR REPLACE FUNCTION can_user_schedule_free_consultation(p_user_id INTEGER)
RETURNS TABLE(
    can_schedule BOOLEAN,
    reason TEXT
) 
AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT used_free_consultation INTO v_used_free_consultation
    FROM _user
    WHERE id = p_user_id;
    
    IF v_used_free_consultation = TRUE THEN
        RETURN QUERY SELECT FALSE, 'Free consultation already used';
        RETURN;
    END IF;
    
    RETURN QUERY SELECT TRUE, 'Eligible for free consultation';
END;
$$ 
LANGUAGE plpgsql;

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text