Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 5 (modified by 231175, 10 hours ago) ( diff )
--

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

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

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

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

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

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

Тригери

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

CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
RETURNS TRIGGER 
AS $$
DECLARE
    v_course_id BIGINT;
    v_course_version_id BIGINT;
BEGIN
    SELECT cv.course_id
    INTO v_course_id
    FROM course_version cv
    WHERE cv.course_version_id = NEW.course_version_id;

    IF v_course_id IS NULL THEN
        RAISE EXCEPTION 'Course version % does not exist', NEW.course_version_id;
    END IF;

    SELECT cv.course_version_id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = v_course_id
      AND cv.is_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=%', v_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 BIGINT, p_course_id BIGINT)
RETURNS BIGINT 
AS $$
DECLARE
    v_course_version_id BIGINT;
    v_enrollment_id BIGINT;
BEGIN
    SELECT cv.course_version_id
    INTO v_course_version_id
    FROM course_version cv
    WHERE cv.course_id = p_course_id
      AND cv.is_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_version_id, purchase_date, enrollment_status)
    VALUES (p_user_id, v_course_version_id, CURRENT_DATE, 'pending')
    RETURNING enrollment_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.enrollment_id AS enrollment_id,
    u.user_id AS user_id,
    u.name AS user_name,
    c.course_id AS course_id,
    ct.title_short AS course_title,
    cv.course_version_id AS course_version_id,
    cv.version_number AS course_version_number,
    cv.is_active AS is_version_active,
    e.purchase_date AS purchase_date,
    e.enrollment_status AS enrollment_status
FROM enrollment e
JOIN "user" u ON e.user_id = u.user_id
JOIN course_version cv ON e.course_version_id = cv.course_version_id
JOIN course c ON cv.course_id = c.course_id
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk';

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

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

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

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

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

Тригери

BEFORE INSERT тригер во course_version, каде само таа верзија што се креира е is_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 is_active = FALSE
    WHERE course_id = NEW.course_id
      AND is_active = TRUE;

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

    NEW.is_active := TRUE;
    NEW.created_at := 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 тригер за атрибутот is_active во course_version, со цел осигурување дека постои само една активна верзија од курсот.

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

CREATE TRIGGER trg_ensure_single_active_version
BEFORE UPDATE OF is_active ON course_version
FOR EACH ROW
WHEN (NEW.is_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.course_version_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.course_id AS course_id,
    ct.title_short AS course_title,
    cv.course_version_id AS version_id,
    cv.version_number,
    cv.is_active,
    cv.created_at,
    COUNT(DISTINCT e.enrollment_id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
    COUNT(DISTINCT e.enrollment_id) AS total_enrollments
FROM course c
JOIN course_version cv ON c.course_id = cv.course_id
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
WHERE cv.version_number = (
    SELECT MAX(version_number)
    FROM course_version
    WHERE course_id = c.course_id
)
GROUP BY c.course_id, ct.title_short, cv.course_version_id, cv.version_number, cv.is_active, cv.created_at;

Валидација на рецензии и рејтинг (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 DROP CONSTRAINT IF EXISTS ck_review_rating_range;
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 DATE;
    v_existing_review_count INTEGER;
BEGIN
    SELECT completion_date INTO v_completion_date
    FROM enrollment
    WHERE enrollment_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.course_id AS course_id,
    ct.title_short AS course_title,
    COUNT(r.review_id) AS total_reviews,
    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
    COUNT(r.review_id) FILTER (WHERE r.rating = 5) AS five_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 4) AS four_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 3) AS three_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 2) AS two_star_count,
    COUNT(r.review_id) FILTER (WHERE r.rating = 1) AS one_star_count
FROM course c
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
LEFT JOIN course_version cv ON c.course_id = cv.course_id
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
GROUP BY c.course_id, ct.title_short;

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

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

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

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

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

Тригери

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

CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_has_used_free_consultation BOOLEAN;
BEGIN
    SELECT has_used_free_consultation INTO v_has_used_free_consultation
    FROM "user"
    WHERE user_id = NEW.user_id;
    
    IF v_has_used_free_consultation IS NULL THEN
        RAISE EXCEPTION 'User % does not exist', NEW.user_id;
    END IF;

    IF v_has_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_email_reminder
FOR EACH ROW
EXECUTE FUNCTION check_free_consultation_eligibility();

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

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

CREATE TRIGGER trg_mark_free_consultation_used
AFTER INSERT ON meeting_email_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 BIGINT)
RETURNS TABLE(
    can_schedule BOOLEAN,
    reason TEXT
) 
AS $$
DECLARE
    v_has_used_free_consultation BOOLEAN;
BEGIN
    SELECT has_used_free_consultation INTO v_has_used_free_consultation
    FROM "user"
    WHERE user_id = p_user_id;
    
    IF v_has_used_free_consultation IS NULL THEN
        RETURN QUERY SELECT FALSE, 'User does not exist'::TEXT;
        RETURN;
    END IF;

    IF v_has_used_free_consultation = TRUE THEN
        RETURN QUERY SELECT FALSE, 'Free consultation already used'::TEXT;
        RETURN;
    END IF;
    
    RETURN QUERY SELECT TRUE, 'Eligible for free consultation'::TEXT;
END;
$$ 
LANGUAGE plpgsql;

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text