Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by 231175, 2 hours ago) ( diff )
--

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

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

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

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

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

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

Тригери

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

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)

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,
    cv.active,
    e.enrollment_purchase_date,
    e.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)

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

Тригери

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();

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
      AND completion_date IS NULL;
    
    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)

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)

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

ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;

Тригери

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();

CREATE OR REPLACE FUNCTION audit_review_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO review_audit (review_id, new_rating, new_comment, action)
        VALUES (NEW.id, NEW.rating, NEW.comment, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO review_audit (review_id, old_rating, new_rating, old_comment, new_comment, action)
        VALUES (NEW.id, OLD.rating, NEW.rating, OLD.comment, NEW.comment, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO review_audit (review_id, old_rating, old_comment, action)
        VALUES (OLD.id, OLD.rating, OLD.comment, 'DELETE');
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_review
AFTER INSERT OR UPDATE OR DELETE ON review
FOR EACH ROW
EXECUTE FUNCTION audit_review_changes();

Погледи (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;

Помошни табели (Audit)

CREATE TABLE review_audit (
    id SERIAL PRIMARY KEY,
    review_id INTEGER NOT NULL REFERENCES review(id),
    old_rating INTEGER,
    new_rating INTEGER,
    old_comment TEXT,
    new_comment TEXT,
    changed_at TIMESTAMP DEFAULT NOW(),
    action VARCHAR(20)
);

CREATE INDEX idx_review_audit_review ON review_audit(review_id);
CREATE INDEX idx_review_audit_changed_at ON review_audit(changed_at);

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

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

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

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

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

Тригери

CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
RETURNS TRIGGER AS $$
DECLARE
    v_used_free_consultation BOOLEAN;
BEGIN
    SELECT user_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();

CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE "user"
    SET user_used_free_consultation = TRUE
    WHERE id = NEW.user_id
      AND user_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;
    v_pending_meetings INTEGER;
BEGIN
    SELECT user_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;
    
    SELECT COUNT(*) INTO v_pending_meetings
    FROM meeting_reminder
    WHERE user_id = p_user_id
      AND meeting_at > NOW();
    
    IF v_pending_meetings > 0 THEN
        RETURN QUERY SELECT FALSE, 'Already has pending meeting scheduled';
        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