Changes between Version 2 and Version 3 of UseCase0001PrototypeImplementationDB


Ignore:
Timestamp:
08/05/26 22:23:15 (20 hours ago)
Author:
231175
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • UseCase0001PrototypeImplementationDB

    v2 v3  
    88
    99Експертот сака да креира нов курс во системот.
     10
     11== Предуслов: табела `expert_course` ==
     12
     13Шемата нема врска помеѓу `expert` и `course`, па нема каде да се запише кој експерт го креирал курсот. Оваа табела мора да се додаде во DDL пред овој случај на употреба да работи.
     14
     15{{{
     16CREATE TABLE expert_course (
     17    expert_id  bigint NOT NULL,
     18    course_id  bigint NOT NULL,
     19    CONSTRAINT pk_expert_course PRIMARY KEY (expert_id, course_id),
     20    CONSTRAINT fk_ec_expert FOREIGN KEY (expert_id)
     21        REFERENCES expert (expert_id) ON DELETE CASCADE,
     22    CONSTRAINT fk_ec_course FOREIGN KEY (course_id)
     23        REFERENCES course (course_id) ON DELETE CASCADE
     24);
     25}}}
    1026
    1127== Главен тек ==
    … …  
    1329* На експертот му се прикажува долга форма со полиња кои треба да ги пополни.
    1430[[Image(create_course_page_1.png, 800px)]]
     31
    1532* Експертот ги пополнува сите полиња и кликнува на копчето Create Course
    1633[[Image(create_course_page_2.png, 800px)]]
     34
    1735* Експертот е навигиран кон почетната страна за експерти. Во база се креира новиот курс.
     36
    1837{{{
     38BEGIN;
     39
    1940DROP TABLE IF EXISTS temp_course_id;
    2041DROP TABLE IF EXISTS temp_course_version_id;
    21 DROP TABLE IF EXISTS temp_course_translate_id;
    2242DROP TABLE IF EXISTS temp_course_content_id;
    2343DROP TABLE IF EXISTS temp_course_lecture_id;
    2444DROP TABLE IF EXISTS temp_tag_id;
    2545
    26 CREATE TEMP TABLE temp_course_id (id BIGINT);
    27 CREATE TEMP TABLE temp_course_version_id (id BIGINT);
    28 CREATE TEMP TABLE temp_course_translate_id (id BIGINT, language VARCHAR(2));
    29 CREATE TEMP TABLE temp_course_content_id (id BIGINT);
    30 CREATE TEMP TABLE temp_course_lecture_id (id BIGINT);
    31 CREATE TEMP TABLE temp_tag_id (id BIGINT, type VARCHAR(50));
    32 
     46CREATE TEMP TABLE temp_course_id         (course_id BIGINT);
     47CREATE TEMP TABLE temp_course_version_id (course_version_id BIGINT);
     48CREATE TEMP TABLE temp_course_content_id (course_content_id BIGINT);
     49CREATE TEMP TABLE temp_course_lecture_id (course_lecture_id BIGINT);
     50CREATE TEMP TABLE temp_tag_id            (tag_id BIGINT, tag_type tag_type);
     51
     52-- 1. Курс
    3353WITH new_course AS (
    3454    INSERT INTO course (image_url, difficulty, duration_minutes, price, color)
    35     VALUES ('https://example.com/image.png', 'INTERMEDIATE', 120, 50, '#008CC2')
    36     RETURNING id
    37 )
    38 INSERT INTO temp_course_id (id)
    39 SELECT id FROM new_course;
    40 
     55    VALUES ('https://example.com/image.png', 'intermediate', 120, 50.00, '#008CC2')
     56    RETURNING course_id
     57)
     58INSERT INTO temp_course_id (course_id)
     59SELECT course_id FROM new_course;
     60
     61-- 2. Верзија на курс
    4162WITH new_course_version AS (
    42     INSERT INTO course_version (version_number, creation_date, active, course_id)
    43     SELECT 1, CURRENT_DATE, true, id FROM temp_course_id
    44     RETURNING id
    45 )
    46 INSERT INTO temp_course_version_id (id)
    47 SELECT id FROM new_course_version;
    48 
    49 WITH new_course_translate_en AS (
    50     INSERT INTO course_translate (language, title_short, title, description_short, description, description_long, course_id)
    51     SELECT 'en', 'Advanced Sales', 'Advanced Sales For Senior Salesman', 'Short desc', 'Normal desc', 'Long desc', id FROM temp_course_id
    52     RETURNING id, language
    53 )
    54 INSERT INTO temp_course_translate_id (id, language)
    55 SELECT id, language FROM new_course_translate_en;
    56 
    57 WITH new_course_translate_mk AS (
    58     INSERT INTO course_translate (language, title_short, title, description_short, description, description_long, course_id)
    59     SELECT 'mk', 'Напредна Продажба', 'Напредна Продажба за Сениор Продавачи', 'Кратка дескрипција', 'Нормална дескрипција', 'Долга дескрипција', id FROM temp_course_id
    60     RETURNING id, language
    61 )
    62 INSERT INTO temp_course_translate_id (id, language)
    63 SELECT id, language FROM new_course_translate_mk;
    64 
     63    INSERT INTO course_version (version_number, created_at, is_active, course_id)
     64    SELECT 1, CURRENT_DATE, true, course_id FROM temp_course_id
     65    RETURNING course_version_id
     66)
     67INSERT INTO temp_course_version_id (course_version_id)
     68SELECT course_version_id FROM new_course_version;
     69
     70-- 3. Преводи на курсот
     71--    what_will_be_learned е text[] колона, па се внесува заедно со преводот.
     72INSERT INTO course_translate (language_id, title_short, title,
     73                              description_short, description, description_long,
     74                              what_will_be_learned, course_id)
     75SELECT (SELECT id FROM language WHERE value = 'en'),
     76       'Advanced Sales',
     77       'Advanced Sales For Senior Salesman',
     78       'Short desc',
     79       'Normal desc',
     80       'Long desc',
     81       ARRAY['Advanced sales techniques',
     82             'How to successfully close deals'],
     83       course_id
     84FROM temp_course_id;
     85
     86INSERT INTO course_translate (language_id, title_short, title,
     87                              description_short, description, description_long,
     88                              what_will_be_learned, course_id)
     89SELECT (SELECT id FROM language WHERE value = 'mk'),
     90       'Напредна Продажба',
     91       'Напредна Продажба за Сениор Продавачи',
     92       'Кратка дескрипција',
     93       'Нормална дескрипција',
     94       'Долга дескрипција',
     95       ARRAY['Напредни техники на продажба',
     96             'Како до успешно затворање на продажби'],
     97       course_id
     98FROM temp_course_id;
     99
     100-- 4. Модул
    65101WITH new_course_content AS (
    66102    INSERT INTO course_content (position, course_version_id)
    67     SELECT 1, id FROM temp_course_version_id
    68     RETURNING id
    69 )
    70 INSERT INTO temp_course_content_id (id)
    71 SELECT id FROM new_course_content;
    72 
    73 INSERT INTO course_content_translate (title, language, course_content_id)
    74 SELECT 'Module 1: Sales Strategies', 'en', id FROM temp_course_content_id;
    75 
    76 INSERT INTO course_content_translate (title, language, course_content_id)
    77 SELECT 'Модул 1: Продажни Стратегии', 'mk', id FROM temp_course_content_id;
    78 
     103    SELECT 1, course_version_id FROM temp_course_version_id
     104    RETURNING course_content_id
     105)
     106INSERT INTO temp_course_content_id (course_content_id)
     107SELECT course_content_id FROM new_course_content;
     108
     109INSERT INTO course_content_translate (title, language_id, course_content_id)
     110SELECT 'Module 1: Sales Strategies',
     111       (SELECT id FROM language WHERE value = 'en'),
     112       course_content_id
     113FROM temp_course_content_id;
     114
     115INSERT INTO course_content_translate (title, language_id, course_content_id)
     116SELECT 'Модул 1: Продажни Стратегии',
     117       (SELECT id FROM language WHERE value = 'mk'),
     118       course_content_id
     119FROM temp_course_content_id;
     120
     121-- 5. Лекција
    79122WITH new_course_lecture AS (
    80123    INSERT INTO course_lecture (duration_minutes, position, content_type, course_content_id)
    81     SELECT 30, 1, 'video', id FROM temp_course_content_id
    82     RETURNING id
    83 )
    84 INSERT INTO temp_course_lecture_id (id)
    85 SELECT id FROM new_course_lecture;
    86 
    87 INSERT INTO course_lecture_translate (title, language, content_file_name, description, content_text, course_lecture_id)
    88 SELECT 'Lecture 1: Intro', 'en', 'lecture1.mp4', 'Introduction to advanced sales', 'Video text content', id
     124    SELECT 30, 1, 'video', course_content_id FROM temp_course_content_id
     125    RETURNING course_lecture_id
     126)
     127INSERT INTO temp_course_lecture_id (course_lecture_id)
     128SELECT course_lecture_id FROM new_course_lecture;
     129
     130INSERT INTO course_lecture_translate (title, language_id, content_file_name,
     131                                      description, content_text, course_lecture_id)
     132SELECT 'Lecture 1: Intro',
     133       (SELECT id FROM language WHERE value = 'en'),
     134       'lecture1.mp4',
     135       'Introduction to advanced sales',
     136       NULL,                       -- content_type = 'video' -> нема текстуална содржина
     137       course_lecture_id
    89138FROM temp_course_lecture_id;
    90139
    91 INSERT INTO course_lecture_translate (title, language, content_file_name, description, content_text, course_lecture_id)
    92 SELECT 'Предавање 1: Вовед', 'mk', 'lecture1.mp4', 'Вовед во напредна продажба', 'Тмп', id
     140INSERT INTO course_lecture_translate (title, language_id, content_file_name,
     141                                      description, content_text, course_lecture_id)
     142SELECT 'Предавање 1: Вовед',
     143       (SELECT id FROM language WHERE value = 'mk'),
     144       'lecture1.mp4',
     145       'Вовед во напредна продажба',
     146       NULL,
     147       course_lecture_id
    93148FROM temp_course_lecture_id;
    94149
     150-- 6. Тагови
     151--    ВНИМАНИЕ: ова креира НОВИ тагови при секое креирање на курс.
     152--    Во продукција формата треба да нуди постоечки тагови, па овој блок
     153--    се заменува со SELECT врз tag/tag_translate по избраните вредности.
    95154WITH new_tags AS (
    96     INSERT INTO tag (type)
    97     VALUES ('skill'), ('interest')
    98     RETURNING id, type
    99 )
    100 INSERT INTO temp_tag_id (id, type)
    101 SELECT id, type FROM new_tags;
    102 
    103 INSERT INTO tag_translate (language, value, tag_id)
    104 SELECT 'en', CASE type WHEN 'skill' THEN 'Skill' WHEN 'interest' THEN 'Interest' END, id FROM temp_tag_id;
    105 
    106 INSERT INTO tag_translate (language, value, tag_id)
    107 SELECT 'mk', CASE type WHEN 'skill' THEN 'Вештина' WHEN 'interest' THEN 'Интерес' END, id FROM temp_tag_id;
     155    INSERT INTO tag (tag_type)
     156    VALUES ('skill'), ('topic')
     157    RETURNING tag_id, tag_type
     158)
     159INSERT INTO temp_tag_id (tag_id, tag_type)
     160SELECT tag_id, tag_type FROM new_tags;
     161
     162INSERT INTO tag_translate (language_id, value, tag_id)
     163SELECT (SELECT id FROM language WHERE value = 'en'),
     164       CASE tag_type WHEN 'skill' THEN 'Skill' WHEN 'topic' THEN 'Topic' END,
     165       tag_id
     166FROM temp_tag_id;
     167
     168INSERT INTO tag_translate (language_id, value, tag_id)
     169SELECT (SELECT id FROM language WHERE value = 'mk'),
     170       CASE tag_type WHEN 'skill' THEN 'Вештина' WHEN 'topic' THEN 'Тема' END,
     171       tag_id
     172FROM temp_tag_id;
    108173
    109174INSERT INTO course_tag (tag_id, course_id)
    110 SELECT t.id, c.id
     175SELECT t.tag_id, c.course_id
    111176FROM temp_tag_id t
    112177CROSS JOIN temp_course_id c;
    113178
     179-- 7. Врска експерт <-> курс
    114180INSERT INTO expert_course (course_id, expert_id)
    115 SELECT id, 1
     181SELECT course_id, :expert_id
    116182FROM temp_course_id;
    117183
    118 INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
    119 SELECT id, 'Advanced sales techniques'
    120 FROM temp_course_translate_id
    121 WHERE language = 'en';
    122 
    123 INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
    124 SELECT id, 'How to successfully close deals'
    125 FROM temp_course_translate_id
    126 WHERE language = 'en';
    127 
    128 INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
    129 SELECT id, 'Напредни техники на продажба'
    130 FROM temp_course_translate_id
    131 WHERE language = 'mk';
    132 
    133 INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
    134 SELECT id, 'Како до успешно затворање на продажби'
    135 FROM temp_course_translate_id
    136 WHERE language = 'mk';
     184COMMIT;
    137185}}}
    138186
    139 
    140187== Алтернативен тек ==
    141188
    142189* Доколку експертот не пополнил некои од полињата, се покажува порака за грешка.
     190
     191Валидацијата се прави на ниво на апликација пред да се почне трансакцијата. Базата ги штити следниве полиња преку `NOT NULL`, но грешката тогаш доаѓа како `23502` откако курсот е делумно креиран:
     192
     193|| '''Табела''' || '''Задолжителни полиња''' ||
     194|| course || image_url, color, difficulty, duration_minutes, price ||
     195|| course_translate || title_short, title, description_short, description, description_long, what_will_be_learned, language_id ||
     196|| course_content || position ||
     197|| course_content_translate || title, language_id ||
     198|| course_lecture || position, duration_minutes, content_type ||
     199|| course_lecture_translate || title, description, language_id ||
     200
     201Дополнителни ограничувања кои можат да фрлат грешка:
     202
     203- `ck_course_duration_positive` и `ck_course_price_non_negative` — времетраењето мора да е поголемо од нула, цената не смее да е негативна.
     204- `uq_course_content_position` и `uq_course_lecture_position` — две модули или две лекции не смеат да имаат иста позиција.
     205- `uq_course_translate` — не смее да има два превода на ист јазик за ист курс.
     206
     207== Забелешки ==
     208
     209- Сите вредности за `difficulty`, `content_type` и `tag_type` мора да се со мали букви — тоа се лабели на PostgreSQL enum типови и се осетливи на голема/мала буква.
     210- `tag_type` има само две вредности: `skill` и `topic`. Вредноста `interest` не постои и предизвикува грешка, а не празен резултат.
     211- Целиот блок мора да биде во една трансакција. Ако некој од чекорите падне, курсот не смее да остане делумно креиран.
     212- `uq_course_version_one_active` дозволува само една активна верзија по курс. При креирање на нова верзија, старата мора прво да се постави на `is_active = false`.