Changes between Version 4 and Version 5 of AdvancedDatabaseDevelopment


Ignore:
Timestamp:
08/06/26 12:58:33 (10 hours ago)
Author:
231175
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedDatabaseDevelopment

    v4 v5  
    66
    77Системот мора да обезбеди дека:
    8 * Кога корисникот се запишува на курс по даден course_id, автоматски се избира и запишува моментално активната верзија на курсот (course_version.active = true) за тој курс.
     8* Кога корисникот се запишува на курс по дадена верзија на курс, автоматски се пренасочува кон моментално активната верзија на тој курс (course_version.is_active = true).
    99* Не е дозволено запишување на курс за кој воопшто нема активна верзија.
    1010* Секоја верзија се третира независно: корисникот може да има повеќе запишувања на различни верзии на истиот курс, без разлика дали претходните се завршени или не.
    … …  
    1414==== Тригери ====
    1515
    16 BEFORE INSERT тригер на enrollment за автоматско доделување на активната верзија на curse (course version).
     16BEFORE INSERT тригер на enrollment за автоматско доделување на активната верзија на курсот (course version).
    1717{{{
    1818CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
    … …  
    2020AS $$
    2121DECLARE
    22     v_course_version_id INTEGER;
    23 BEGIN
    24     SELECT cv.id
     22    v_course_id BIGINT;
     23    v_course_version_id BIGINT;
     24BEGIN
     25    SELECT cv.course_id
     26    INTO v_course_id
     27    FROM course_version cv
     28    WHERE cv.course_version_id = NEW.course_version_id;
     29
     30    IF v_course_id IS NULL THEN
     31        RAISE EXCEPTION 'Course version % does not exist', NEW.course_version_id;
     32    END IF;
     33
     34    SELECT cv.course_version_id
    2535    INTO v_course_version_id
    2636    FROM course_version cv
    27     WHERE cv.course_id = NEW.course_id
    28       AND cv.active = TRUE
     37    WHERE cv.course_id = v_course_id
     38      AND cv.is_active = TRUE
    2939    ORDER BY cv.version_number DESC
    3040    LIMIT 1;
    3141   
    3242    IF v_course_version_id IS NULL THEN
    33         RAISE EXCEPTION 'No active course version found for course_id=%', NEW.course_id;
     43        RAISE EXCEPTION 'No active course version found for course_id=%', v_course_id;
    3444    END IF;
    3545   
    … …  
    5161Функција за креирање на нов enrollment. При креирање се зема најновата активна верзија на курсот.
    5262{{{
    53 CREATE OR REPLACE FUNCTION create_enrollment_for_active_version(p_user_id INTEGER, p_course_id INTEGER)
    54 RETURNS INTEGER
    55 AS $$
    56 DECLARE
    57     v_course_version_id INTEGER;
    58     v_enrollment_id INTEGER;
    59 BEGIN
    60     SELECT cv.id
     63CREATE OR REPLACE FUNCTION create_enrollment_for_active_version(p_user_id BIGINT, p_course_id BIGINT)
     64RETURNS BIGINT
     65AS $$
     66DECLARE
     67    v_course_version_id BIGINT;
     68    v_enrollment_id BIGINT;
     69BEGIN
     70    SELECT cv.course_version_id
    6171    INTO v_course_version_id
    6272    FROM course_version cv
    6373    WHERE cv.course_id = p_course_id
    64       AND cv.active = TRUE
     74      AND cv.is_active = TRUE
    6575    ORDER BY cv.version_number DESC
    6676    LIMIT 1;
    … …  
    7080    END IF;
    7181   
    72     INSERT INTO enrollment (user_id, course_id, course_version_id, enrollment_purchase_date, status)
    73     VALUES (p_user_id, p_course_id, v_course_version_id, NOW(), 'PENDING')
    74     RETURNING id INTO v_enrollment_id;
     82    INSERT INTO enrollment (user_id, course_version_id, purchase_date, enrollment_status)
     83    VALUES (p_user_id, v_course_version_id, CURRENT_DATE, 'pending')
     84    RETURNING enrollment_id INTO v_enrollment_id;
    7585   
    7686    RETURN v_enrollment_id;
    … …  
    8696CREATE OR REPLACE VIEW enrollments_with_active_version AS
    8797SELECT
    88     e.id AS enrollment_id,
    89     u.id AS user_id,
     98    e.enrollment_id AS enrollment_id,
     99    u.user_id AS user_id,
    90100    u.name AS user_name,
    91     c.id AS course_id,
     101    c.course_id AS course_id,
    92102    ct.title_short AS course_title,
    93     cv.id AS course_version_id,
     103    cv.course_version_id AS course_version_id,
    94104    cv.version_number AS course_version_number,
    95     cv.active AS is_version_active,
    96     e.enrollment_purchase_date AS purchase_date,
    97     e.status AS enrollment_status
     105    cv.is_active AS is_version_active,
     106    e.purchase_date AS purchase_date,
     107    e.enrollment_status AS enrollment_status
    98108FROM enrollment e
    99 JOIN user u ON e.user_id = u.id
    100 JOIN course c ON e.course_id = c.id
    101 JOIN course_version cv ON e.course_version_id = cv.id
    102 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk';
     109JOIN "user" u ON e.user_id = u.user_id
     110JOIN course_version cv ON e.course_version_id = cv.course_version_id
     111JOIN course c ON cv.course_id = c.course_id
     112JOIN course_translate ct ON c.course_id = ct.course_id
     113JOIN language l ON l.id = ct.language_id AND l.value = 'mk';
    103114}}}
    104115
    … …  
    110121
    111122Системот мора да обезбеди дека:
    112 * Само една верзија може да биде активна (active = true) по курс во исто време
     123* Само една верзија може да биде активна (is_active = true) по курс во исто време
    113124* Кога се активира нова верзија, претходните активни верзии на истиот курс автоматски се деактивираат
    114125* Не смее да се избрише верзија која има активни (незавршени) запишувања (enrollments)
    … …  
    118129==== Тригери ====
    119130
    120 BEFORE INSERT тригер во course_version, каде само таа верзија што се креира е active, останатите не се. Дополнително се ажурираат и уште некои атрибути.
     131BEFORE INSERT тригер во course_version, каде само таа верзија што се креира е is_active, останатите не се. Дополнително се ажурираат и уште некои атрибути.
    121132{{{
    122133CREATE OR REPLACE FUNCTION ensure_single_active_version_when_created()
    … …  
    127138BEGIN
    128139    UPDATE course_version
    129     SET active = FALSE
    130     WHERE course_id = NEW.course_id;
     140    SET is_active = FALSE
     141    WHERE course_id = NEW.course_id
     142      AND is_active = TRUE;
    131143
    132144    SELECT COALESCE(MAX(version_number), 0) + 1 INTO v_num_new_version
    … …  
    134146    WHERE course_id = NEW.course_id;
    135147
    136     NEW.active := TRUE;
    137     NEW.creation_date := CURRENT_DATE;
     148    NEW.is_active := TRUE;
     149    NEW.created_at := CURRENT_DATE;
    138150    NEW.version_number := v_num_new_version;
    139151   
    … …  
    149161}}}
    150162
    151 BEFORE UPDATE тригер за атрибутот active во course_version, со цел осигурување дека постои само една активна верзија од курсот.
     163BEFORE UPDATE тригер за атрибутот is_active во course_version, со цел осигурување дека постои само една активна верзија од курсот.
    152164{{{
    153165CREATE OR REPLACE FUNCTION ensure_single_active_version()
    … …  
    155167AS $$
    156168BEGIN
    157     IF NEW.active = TRUE THEN
     169    IF NEW.is_active = TRUE THEN
    158170        UPDATE course_version
    159         SET active = FALSE
     171        SET is_active = FALSE
    160172        WHERE course_id = NEW.course_id
    161           AND id != NEW.id
    162           AND active = TRUE;
     173          AND course_version_id != NEW.course_version_id
     174          AND is_active = TRUE;
    163175    END IF;
    164176   
    … …  
    169181
    170182CREATE TRIGGER trg_ensure_single_active_version
    171 BEFORE UPDATE OF active ON course_version
    172 FOR EACH ROW
    173 WHEN (NEW.active = TRUE)
     183BEFORE UPDATE OF is_active ON course_version
     184FOR EACH ROW
     185WHEN (NEW.is_active = TRUE)
    174186EXECUTE FUNCTION ensure_single_active_version();
    175187}}}
    … …  
    185197    SELECT COUNT(*) INTO v_active_enrollments
    186198    FROM enrollment
    187     WHERE course_version_id = OLD.id;
     199    WHERE course_version_id = OLD.course_version_id;
    188200   
    189201    IF v_active_enrollments > 0 THEN
    … …  
    208220CREATE OR REPLACE VIEW course_latest_versions AS
    209221SELECT
    210     c.id AS course_id,
     222    c.course_id AS course_id,
    211223    ct.title_short AS course_title,
    212     cv.id AS version_id,
     224    cv.course_version_id AS version_id,
    213225    cv.version_number,
    214     cv.active,
    215     cv.version_creation_date,
    216     COUNT(DISTINCT e.id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
    217     COUNT(DISTINCT e.id) AS total_enrollments
     226    cv.is_active,
     227    cv.created_at,
     228    COUNT(DISTINCT e.enrollment_id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
     229    COUNT(DISTINCT e.enrollment_id) AS total_enrollments
    218230FROM course c
    219 JOIN course_version cv ON c.id = cv.course_id
    220 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
    221 LEFT JOIN enrollment e ON cv.id = e.course_version_id
     231JOIN course_version cv ON c.course_id = cv.course_id
     232JOIN course_translate ct ON c.course_id = ct.course_id
     233JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
     234LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
    222235WHERE cv.version_number = (
    223236    SELECT MAX(version_number)
    224237    FROM course_version
    225     WHERE course_id = c.id
     238    WHERE course_id = c.course_id
    226239)
    227 GROUP BY c.id, ct.title_short, cv.id, cv.version_number, cv.active, cv.version_creation_date;
     240GROUP BY c.course_id, ct.title_short, cv.course_version_id, cv.version_number, cv.is_active, cv.created_at;
    228241}}}
    229242
    … …  
    249262CHECK (VALUE >= 1 AND VALUE <= 5);
    250263
     264ALTER TABLE review DROP CONSTRAINT IF EXISTS ck_review_rating_range;
    251265ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;
    252266}}}
    … …  
    259273RETURNS TRIGGER AS $$
    260274DECLARE
    261     v_completion_date TIMESTAMP;
     275    v_completion_date DATE;
    262276    v_existing_review_count INTEGER;
    263277BEGIN
    264278    SELECT completion_date INTO v_completion_date
    265279    FROM enrollment
    266     WHERE id = NEW.enrollment_id;
     280    WHERE enrollment_id = NEW.enrollment_id;
    267281   
    268282    IF v_completion_date IS NULL THEN
    … …  
    294308CREATE OR REPLACE VIEW course_average_ratings AS
    295309SELECT
    296     c.id AS course_id,
     310    c.course_id AS course_id,
    297311    ct.title_short AS course_title,
    298     COUNT(r.id) AS total_reviews,
     312    COUNT(r.review_id) AS total_reviews,
    299313    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
    300     COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star_count,
    301     COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star_count,
    302     COUNT(r.id) FILTER (WHERE r.rating = 3) AS three_star_count,
    303     COUNT(r.id) FILTER (WHERE r.rating = 2) AS two_star_count,
    304     COUNT(r.id) FILTER (WHERE r.rating = 1) AS one_star_count
     314    COUNT(r.review_id) FILTER (WHERE r.rating = 5) AS five_star_count,
     315    COUNT(r.review_id) FILTER (WHERE r.rating = 4) AS four_star_count,
     316    COUNT(r.review_id) FILTER (WHERE r.rating = 3) AS three_star_count,
     317    COUNT(r.review_id) FILTER (WHERE r.rating = 2) AS two_star_count,
     318    COUNT(r.review_id) FILTER (WHERE r.rating = 1) AS one_star_count
    305319FROM course c
    306 JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
    307 LEFT JOIN course_version cv ON c.id = cv.course_id
    308 LEFT JOIN enrollment e ON cv.id = e.course_version_id
    309 LEFT JOIN review r ON e.id = r.enrollment_id
    310 GROUP BY c.id, ct.title_short;
     320JOIN course_translate ct ON c.course_id = ct.course_id
     321JOIN language l ON l.id = ct.language_id AND l.value = 'mk'
     322LEFT JOIN course_version cv ON c.course_id = cv.course_id
     323LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id
     324LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
     325GROUP BY c.course_id, ct.title_short;
    311326}}}
    312327
    … …  
    319334Системот мора да обезбеди дека:
    320335* Корисникот може да користи само една бесплатна консултација
    321 * Може да се креира meeting_reminder за бесплатна консултација само ако корисникот сè уште не ја искористил
    322 * После креирање на meeting_reminder за бесплатна консултација, флагот used_free_consultation автоматски се поставува на TRUE
     336* Може да се креира meeting_email_reminder за бесплатна консултација само ако корисникот сè уште не ја искористил
     337* После креирање на meeting_email_reminder за бесплатна консултација, флагот has_used_free_consultation автоматски се поставува на TRUE
    323338
    324339=== Имплементација ===
    … …  
    326341==== Тригери ====
    327342
    328 BEFORE INSERT тригер на meeting reminder, каде не смее да се креира нов митинг и потсетување по емаил доколку корисникот веќе имал бесплатна консултација.
     343BEFORE INSERT тригер на meeting email reminder, каде не смее да се креира нов митинг и потсетување по емаил доколку корисникот веќе имал бесплатна консултација.
    329344{{{
    330345CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
    331346RETURNS TRIGGER AS $$
    332347DECLARE
    333     v_used_free_consultation BOOLEAN;
    334 BEGIN
    335     SELECT used_free_consultation INTO v_used_free_consultation
    336     FROM _user
    337     WHERE id = NEW.user_id;
    338    
    339     IF v_used_free_consultation = TRUE THEN
     348    v_has_used_free_consultation BOOLEAN;
     349BEGIN
     350    SELECT has_used_free_consultation INTO v_has_used_free_consultation
     351    FROM "user"
     352    WHERE user_id = NEW.user_id;
     353   
     354    IF v_has_used_free_consultation IS NULL THEN
     355        RAISE EXCEPTION 'User % does not exist', NEW.user_id;
     356    END IF;
     357
     358    IF v_has_used_free_consultation = TRUE THEN
    340359        RAISE EXCEPTION 'User has already used their free consultation';
    341360    END IF;
    … …  
    346365
    347366CREATE TRIGGER trg_check_free_consultation_before_meeting
    348 BEFORE INSERT ON meeting_reminder
     367BEFORE INSERT ON meeting_email_reminder
    349368FOR EACH ROW
    350369EXECUTE FUNCTION check_free_consultation_eligibility();
    351370}}}
    352371
    353 AFTER INSERT тригер на meeting reminder, каде одкако ќе се закаже состанокот да се маркира дека тој корисник го има искористено своето право за бесплатна консултативна сесија со експерт.
     372AFTER INSERT тригер на meeting email reminder, каде одкако ќе се закаже состанокот да се маркира дека тој корисник го има искористено своето право за бесплатна консултативна сесија со експерт.
    354373{{{
    355374CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
    356375RETURNS TRIGGER AS $$
    357376BEGIN
    358     UPDATE _user
    359     SET used_free_consultation = TRUE
    360     WHERE id = NEW.user_id
    361       AND used_free_consultation = FALSE;
     377    UPDATE "user"
     378    SET has_used_free_consultation = TRUE
     379    WHERE user_id = NEW.user_id
     380      AND has_used_free_consultation = FALSE;
    362381   
    363382    RETURN NEW;
    … …  
    366385
    367386CREATE TRIGGER trg_mark_free_consultation_used
    368 AFTER INSERT ON meeting_reminder
     387AFTER INSERT ON meeting_email_reminder
    369388FOR EACH ROW
    370389EXECUTE FUNCTION mark_free_consultation_as_used();
    … …  
    375394Функција која кажува дали корисникот може да закаже бесплатна консултативна сесија со експерт.
    376395{{{
    377 CREATE OR REPLACE FUNCTION can_user_schedule_free_consultation(p_user_id INTEGER)
     396CREATE OR REPLACE FUNCTION can_user_schedule_free_consultation(p_user_id BIGINT)
    378397RETURNS TABLE(
    379398    can_schedule BOOLEAN,
    … …  
    382401AS $$
    383402DECLARE
    384     v_used_free_consultation BOOLEAN;
    385 BEGIN
    386     SELECT used_free_consultation INTO v_used_free_consultation
    387     FROM _user
    388     WHERE id = p_user_id;
    389    
    390     IF v_used_free_consultation = TRUE THEN
    391         RETURN QUERY SELECT FALSE, 'Free consultation already used';
     403    v_has_used_free_consultation BOOLEAN;
     404BEGIN
     405    SELECT has_used_free_consultation INTO v_has_used_free_consultation
     406    FROM "user"
     407    WHERE user_id = p_user_id;
     408   
     409    IF v_has_used_free_consultation IS NULL THEN
     410        RETURN QUERY SELECT FALSE, 'User does not exist'::TEXT;
    392411        RETURN;
    393412    END IF;
    394    
    395     RETURN QUERY SELECT TRUE, 'Eligible for free consultation';
    396 END;
    397 $$
    398 LANGUAGE plpgsql;
    399 }}}
    400 
     413
     414    IF v_has_used_free_consultation = TRUE THEN
     415        RETURN QUERY SELECT FALSE, 'Free consultation already used'::TEXT;
     416        RETURN;
     417    END IF;
     418   
     419    RETURN QUERY SELECT TRUE, 'Eligible for free consultation'::TEXT;
     420END;
     421$$
     422LANGUAGE plpgsql;
     423}}}