= Креирање на базата (!DatabaseCreation) = Во оваа фаза се прикажани DDL скриптата за креирање на базата (2А), скриптите за генерирање и полнење на податоците (2Б) и погледите што апликацијата ги користи. [[PageOutline(2-3,Содржина,inline)]] == 2А: DDL скрипта == [attachment:ddl.sql] Скриптата ги креира 23-те табели од релациониот модел (RelationalModel) по редослед на зависност. Надворешните клучеви, проверките и составеното уникатно ограничување се именувани (`fk_*`, `chk_*`, `uq_*`), така што пораката за грешка при прекршување го именува правилото. Табелата за корисници се вика `"User"`, со наводници, бидејќи `USER` е резервиран збор. Табелите се поделени во шест модули: * '''Идентитет и пристап''': `"User"` е централната табела за сите корисници со `type` (`client`, `vendor` или `management`); трите подтипови `Client_User`, `Vendor_User` и `Management_User` го делат нејзиниот примарен клуч и го поврзуваат корисникот со клиентот, софтверската агенција или улогата на која припаѓа. Дозволите се доделуваат на улогите преку `Role_Permission`. * '''Страни''': `Client` (клиентска компанија, припаѓа на една `Industry`) и `Vendor` (софтверска агенција). * '''Комерцијален дел''': `Client_Vendor_Contract` е договорот меѓу клиент и софтверска агенција; `Vendor_Subscription` е претплатата на софтверската агенција на платформата по периоди, на ниво од `Subscription_Tier` (фиксна цена од ценовник или договорна цена). * '''Испорака''': `Project` припаѓа на договор, а не директно на клиент и софтверска агенција, и има тековен статус од `Project_Status`; `Project_Technology` ги поврзува проектите со `Technology`. * '''Квалитет''': `Review` е рецензијата на клиентот за завршен проект (најмногу една по проект), со оценки од 1 до 5 по секоја димензија од `Rating_Dimension` во `Review_Score`; `Dispute_Ticket` е спорот што корисник на софтверската агенција го поднесува врз рецензија, а го решава менаџмент корисник. * '''Историја''': `Project_Status_History` чува по една редица за секоја промена на статус, со точно еден актер (корисник на софтверската агенција или менаџмент корисник); `Project_Budget_Audit` чува стар и нов буџет за секоја промена на буџетот. '''Проверки (CHECK):''' * Датуми * крајниот датум е по почетниот: договор, претплата, проект * спорот не се решава пред да е поднесен * Износи: сите се позитивни * буџет на проект, вредност на договор, цена од ценовник, договорна цена, стар и нов буџет во ревизијата * Оценка: меѓу 1 и 5 * Формат * типот на корисник е една од `client`, `vendor`, `management` * е-поштата е во облик `име@домен` * валутата е три големи букви * Непразен текст * име, презиме и лозинка на корисник * наслов на договор, име на проект * резиме на рецензија, причина и белешка на спор * Конзистентна состојба * промена на статус има точно еден актер * решен спор има датум на решавање, доделен менаџмент корисник и белешка; нерешен нема датум * ниво на претплата има или цена од ценовник или дозвола за договорна цена, никогаш двете '''UNIQUE:''' * имињата во табелите со фиксни листи: улоги, дозволи, индустрии, статуси, технологии, димензии, нивоа * е-поштата на корисникот * бројот на договорот * една рецензија по проект * името на проектот во рамки на договорот (`uq_project_contract_name`) '''Стандардни вредности:''' * `created_at` и `updated_at`: `NOW()` * `CURRENT_DATE`: почеток на договор, претплата и проект; датум на рецензија; датум на поднесување на спор * Почетна состојба * нов корисник е неактивен (се активира по потврда) * нов договор и нова претплата се активни * нова рецензија е необјавена * нов спор е нерешен и недоделен '''Бришење:''' * сите надворешни клучеви се `ON DELETE RESTRICT` * апликацијата не брише: корисниците се деактивираат, споровите се решаваат, а договорите и претплатите истекуваат '''Делумни уникатни индекси.''' Две правила важат само за дел од редиците и не можат да се запишат како ограничување на ниво на табела: {{{#!sql -- WHERE: уникатноста важи само за редиците што го исполнуваат условот CREATE UNIQUE INDEX uq_vendorsub_one_active ON Vendor_Subscription (vendor_id) WHERE is_active; CREATE UNIQUE INDEX uq_dispute_open_per_vendor_review ON Dispute_Ticket (review_id, vendor_user_id) WHERE is_resolved = false; }}} Софтверска агенција има најмногу една активна претплата, а корисник на агенцијата најмногу еден отворен спор за иста рецензија. == 2Б: Податоци == [attachment:seed_generator.py] [attachment:load_seed.sql] Податоците ги генерира скриптата `seed_generator.py` (Python, библиотека Faker за имиња и текст) како по една CSV датотека за секоја табела; скриптата `load_seed.sql` ги полни табелите по редослед на зависност. Податоците ги задоволуваат сите ограничувања од шемата и правилата од апликациската логика: * Авторот на рецензија е корисник на клиентот од договорот на проектот, поднесувачот на спор е корисник на софтверската агенција од истиот договор, а актерот во историјата на статуси е корисник на софтверската агенција на проектот или менаџмент корисник. * Секој проект минува низ животниот циклус Draft → Under Review → In Progress (→ On Hold) и завршува како Completed или Cancelled, или останува отворен; промените на статус се временски подредени внатре во траењето на проектот, а статусот на проектот е секогаш последната редица од историјата. * Рецензии постојат само за завршени проекти (Completed или Cancelled), најмногу една по проект и со по една оценка за секоја од десетте димензии. * Решените спорови имаат датум на решавање, доделен менаџмент корисник и белешка. Рецензија со отворен спор не е објавена. * Промените на буџетот формираат синџир: стариот буџет на секоја промена е новиот од претходната, а последниот нов буџет е тековниот буџет на проектот. * Периодите на претплата на една софтверска агенција не се преклопуваат и активен е најмногу последниот; договорна цена има само на нивоата што ја дозволуваат. * Проектите лежат внатре во периодот на договорот, секој договор има барем еден проект, а секој клиент и секоја софтверска агенција има барем еден корисник. Е-поштата е во облик `име.презиме@домен-на-компанијата`. Распределбите се избрани при генерирањето, за реалистични податоци: * 78,5 % од проектите се Completed и 5,6 % Cancelled; останатите се отворени. * Рецензијата е напишана 1 до 45 дена по крајот на проектот. Спор е поднесен за 15 % од рецензиите, а од рецензиите без отворен спор се објавени 70 %. * Секоја софтверска агенција има скриен параметар на квалитет околу кој се влечат оценките на нејзините рецензии, па просечните оценки по агенција се распоредени од 1,66 до 4,61, а не сите околу 3. '''Големина на табелите.''' Број на редици по полнењето: ||= Табела =||= Редици =|| || `Review_Score` || 10.000.000 || || `Project_Status_History` || 5.946.160 || || `Project_Technology` || 4.197.954 || || `Project` || 1.400.000 || || `Project_Budget_Audit` || 1.048.649 || || `Review` || 1.000.000 || || `Client_Vendor_Contract` || 250.000 || || `Dispute_Ticket` || 143.894 || || `"User"` || 77.000 || || `Client_User` || 50.000 || || `Vendor_User` || 25.000 || || `Client` || 20.000 || || `Vendor_Subscription` || 5.916 || || `Vendor` || 5.000 || || `Management_User` || 2.000 || || `Role_Permission` || 40 || || `Technology` || 30 || || `Industry` || 20 || || `Permission` || 15 || || `Rating_Dimension` || 10 || || `Project_Status` || 6 || || `Role` || 5 || || `Subscription_Tier` || 4 || Шест табели имаат милион или повеќе редици; вкупно има 24,2 милиони редици и базата зафаќа 2,1 GB. == Погледи == [attachment:views.sql] Скриптата креира 16 погледи. Првите два, `vw_contract_details` и `vw_project_details`, се помошни: апликацијата не ги чита директно, туку врз нив се градат останатите. === Поглед 1: Детали за договор (vw_contract_details) === Помошен поглед: по една редица за секој договор со имињата на двете страни. Поврзувањето договор–клиент–софтверска агенција е напишано еднаш, а погледите за договори и за проекти го користат. {{{#!sql CREATE OR REPLACE VIEW vw_contract_details AS SELECT cvc.contract_id, cvc.contract_number, cvc.contract_title, cvc.start_date, cvc.end_date, cvc.total_value, cvc.currency_code, cvc.is_active, c.client_id, c.company_name, v.vendor_id, v.agency_name FROM Client_Vendor_Contract cvc JOIN Client c ON c.client_id = cvc.client_id JOIN Vendor v ON v.vendor_id = cvc.vendor_id; }}} === Поглед 2: Детали за проект (vw_project_details) === Помошен поглед: проектот со името на статусот, договорот и двете страни. Од него читаат погледите по софтверска агенција и по клиент, погледите за оценки и погледот за промени на буџет. Оптимизаторот ја вметнува дефиницијата на помошниот поглед во прашалникот што го користи, па помошниот поглед не додава чекор во планот. {{{#!sql CREATE OR REPLACE VIEW vw_project_details AS SELECT p.project_id, p.project_name, p.status_id, ps.status_name, p.start_date, p.end_date, p.budget, cd.contract_id, cd.contract_number, cd.currency_code, cd.client_id, cd.company_name, cd.vendor_id, cd.agency_name FROM Project p -- договорот и двете страни доаѓаат од помошниот поглед JOIN vw_contract_details cd ON cd.contract_id = p.contract_id JOIN Project_Status ps ON ps.status_id = p.status_id; }}} === Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor) === Сите проекти на секоја софтверска агенција со статус, датуми, буџет и валута. Апликацијата го чита филтриран по агенција на нејзината контролна табла и на нејзиниот профил, каде што клиентот го разгледува портфолиото на реални проекти пред избор на агенција. {{{#!sql CREATE OR REPLACE VIEW vw_projects_per_vendor AS SELECT pd.vendor_id, pd.agency_name, pd.project_id, pd.project_name, pd.status_name, pd.start_date, pd.end_date, pd.budget, pd.currency_code FROM vw_project_details pd ORDER BY pd.agency_name, pd.status_name, pd.project_name; }}} === Поглед 4: Проекти по клиент (vw_projects_per_client) === Сите проекти на една клиентска компанија низ сите нејзини договори и софтверски агенции. Апликацијата го чита филтриран по клиент на контролната табла на клиентот; оттука клиентот избира завршен проект за кој ќе остави рецензија. {{{#!sql CREATE OR REPLACE VIEW vw_projects_per_client AS SELECT pd.client_id, pd.company_name, pd.project_id, pd.project_name, pd.status_name, pd.start_date, pd.end_date, pd.budget, pd.currency_code FROM vw_project_details pd ORDER BY pd.company_name, pd.status_name, pd.project_name; }}} === Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor) === Бројот на проекти и збирот на нивните буџети за секоја софтверска агенција, по валута, за да не се собираат износи во различни валути. Финансиски преглед на обемот на работа на агенцијата: увид во сопствените перформанси и, за клиентот, показател за големината на агенцијата. {{{#!sql CREATE OR REPLACE VIEW vw_budget_per_vendor AS SELECT pd.vendor_id, pd.agency_name, pd.currency_code, COUNT(pd.project_id) AS project_count, SUM(pd.budget) AS total_budget FROM vw_project_details pd GROUP BY pd.vendor_id, pd.agency_name, pd.currency_code ORDER BY pd.agency_name, pd.currency_code; }}} === Поглед 6: Вкупен буџет по клиент (vw_budget_per_client) === Истата агрегација по клиент: вкупниот буџет што клиентот го ангажирал низ сите проекти и договори, по валута. Финансиски преглед на клиентот за следење на потрошениот буџет. {{{#!sql CREATE OR REPLACE VIEW vw_budget_per_client AS SELECT pd.client_id, pd.company_name, pd.currency_code, COUNT(pd.project_id) AS project_count, SUM(pd.budget) AS total_budget FROM vw_project_details pd GROUP BY pd.client_id, pd.company_name, pd.currency_code ORDER BY pd.company_name, pd.currency_code; }}} === Поглед 7: Клиенти по индустрија (vw_clients_per_industry) === Клиентите со нивната индустрија. Апликацијата го чита филтриран по индустрија, на пример кога софтверска агенција бара референци во одредена гранка; ја имплементира организацијата на клиентите по индустриска категорија од описот на проектот. {{{#!sql CREATE OR REPLACE VIEW vw_clients_per_industry AS SELECT i.industry_id, i.industry_name, c.client_id, c.company_name, c.contact_email FROM Client c JOIN Industry i ON i.industry_id = c.industry_id ORDER BY i.industry_name, c.company_name; }}} === Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor) === Просечната оценка на секоја софтверска агенција преку сите димензии и бројот на рецензии, само од објавени рецензии, така што оспорена или необјавена рецензија не влијае на јавниот просек. Агенциите без објавена рецензија остануваат во резултатот со нула рецензии. Ова е јавната ранг-листа на софтверски агенции, главната функционалност на платформата; филтриран по агенција, погледот ја дава оценката прикажана на нејзиниот профил. {{{#!sql CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS SELECT v.vendor_id, v.agency_name, COUNT(vr.review_id) AS review_count, -- просек само од објавените рецензии: збир на збировите / збир на броевите, -- што е истиот просек како AVG, но без COUNT(DISTINCT); NULLIF штити од делење со нула ROUND(SUM(vr.score_sum) / NULLIF(SUM(vr.score_cnt), 0), 2) AS avg_rating FROM Vendor v -- LEFT JOIN: агенција без објавена рецензија останува со review_count = 0 -- потпрашалник: збир и број на оценки по рецензија, само објавени рецензии LEFT JOIN (SELECT pd.vendor_id, r.review_id, SUM(rs.score_value)::numeric AS score_sum, -- ::numeric за децимален просек COUNT(*) AS score_cnt FROM Review r JOIN Review_Score rs ON rs.review_id = r.review_id JOIN vw_project_details pd ON pd.project_id = r.project_id WHERE r.is_published = true GROUP BY pd.vendor_id, r.review_id) vr ON vr.vendor_id = v.vendor_id GROUP BY v.vendor_id, v.agency_name ORDER BY avg_rating DESC NULLS LAST; }}} === Поглед 9: Нерешени спорови (vw_unresolved_dispute_tickets) === Сите нерешени спорови со рецензијата, проектот, името на поднесувачот, името на доделениот менаџмент корисник (празно додека спорот не е доделен) и софтверската агенција во чие име е поднесен. Редот на задачи на менаџментот: се чита филтриран по проект, по агенција или по доделен корисник. {{{#!sql CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS SELECT dt.ticket_id, dt.filed_at, dt.reason, r.review_id, p.project_id, p.project_name, dt.vendor_user_id AS filed_by_vendor_user_id, -- имињата на поднесувачот (vusr) и на доделениот корисник (musr) од две -- поврзувања со "User"; musr е NULL додека спорот не е доделен (LEFT JOIN) vusr.first_name || ' ' || vusr.last_name AS filed_by_vendor_user, dt.assigned_management_user_id, musr.first_name || ' ' || musr.last_name AS assigned_to_management_user, dt.created_at, dt.updated_at, ven.vendor_id, ven.agency_name FROM Dispute_Ticket dt JOIN Review r ON r.review_id = dt.review_id JOIN Project p ON p.project_id = r.project_id JOIN Vendor_User vu ON vu.user_id = dt.vendor_user_id JOIN Vendor ven ON ven.vendor_id = vu.vendor_id JOIN "User" vusr ON vusr.user_id = vu.user_id LEFT JOIN "User" musr ON musr.user_id = dt.assigned_management_user_id WHERE dt.is_resolved = false ORDER BY dt.filed_at; }}} === Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor) === Бројот на проекти на секоја софтверска агенција по статус. Преглед на контролната табла на агенцијата (колку проекти се во тек, завршени, откажани) и, за клиентот, показател колку од проектите на агенцијата навистина се завршуваат. {{{#!sql CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS SELECT pd.vendor_id, pd.agency_name, pd.status_id, pd.status_name, COUNT(pd.project_id) AS project_count FROM vw_project_details pd GROUP BY pd.vendor_id, pd.agency_name, pd.status_id, pd.status_name ORDER BY pd.agency_name, pd.status_name; }}} === Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client) === Истото по клиент: состојбата на портфолиото на една клиентска компанија низ сите нејзини софтверски агенции, на контролната табла на клиентот. {{{#!sql CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS SELECT pd.client_id, pd.company_name, pd.status_id, pd.status_name, COUNT(pd.project_id) AS project_count FROM vw_project_details pd GROUP BY pd.client_id, pd.company_name, pd.status_id, pd.status_name ORDER BY pd.company_name, pd.status_name; }}} === Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor) === Сите договори на секоја софтверска агенција со името на клиентот, периодот, вредноста и валутата, прво активните, па најновите. Преглед на активните и историските договори на агенцијата; договорот е врската преку која проектите се врзуваат за клиент и агенција. {{{#!sql CREATE OR REPLACE VIEW vw_contracts_per_vendor AS SELECT cd.vendor_id, cd.agency_name, cd.contract_id, cd.contract_number, cd.contract_title, cd.client_id, cd.company_name AS client_name, cd.start_date, cd.end_date, cd.total_value, cd.currency_code, cd.is_active FROM vw_contract_details cd ORDER BY cd.agency_name, cd.is_active DESC, cd.start_date DESC; }}} === Поглед 13: Договори по клиент (vw_contracts_per_client) === Истото од страна на клиентот: сите негови договори со името на софтверската агенција, за прегледот на активните и историските договори на клиентската компанија. {{{#!sql CREATE OR REPLACE VIEW vw_contracts_per_client AS SELECT cd.client_id, cd.company_name, cd.contract_id, cd.contract_number, cd.contract_title, cd.vendor_id, cd.agency_name AS vendor_name, cd.start_date, cd.end_date, cd.total_value, cd.currency_code, cd.is_active FROM vw_contract_details cd ORDER BY cd.company_name, cd.is_active DESC, cd.start_date DESC; }}} === Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions) === Сите периоди на претплата на секоја софтверска агенција со нивото и ефективната цена: договорната цена ако постои, инаку цената од ценовникот. Преглед на активната и историските претплати на агенцијата и основа за наплатата. {{{#!sql CREATE OR REPLACE VIEW vw_vendor_subscriptions AS SELECT v.vendor_id, v.agency_name, st.tier_id, st.tier_name, -- договорената цена ако постои, инаку цената од ценовникот, инаку 0 COALESCE(vs.negotiated_price, st.list_price, 0) AS effective_price, vs.start_date, vs.end_date, vs.is_active FROM Vendor_Subscription vs JOIN Vendor v ON v.vendor_id = vs.vendor_id JOIN Subscription_Tier st ON st.tier_id = vs.tier_id ORDER BY v.agency_name, vs.is_active DESC, vs.start_date DESC; }}} === Поглед 15: Оценки по димензија по софтверска агенција (vw_vendor_rating_by_dimension) === За секоја софтверска агенција и димензија на оценување: бројот на оценки, просекот, минимумот и максимумот, само од објавени рецензии. Деталниот приказ зад просечната оценка на профилот на агенцијата (квалитет на код, комуникација, почитување на рокови и останатите димензии), односно повеќедимензионалното оценување по кое платформата се разликува од решенијата со една оценка. {{{#!sql CREATE OR REPLACE VIEW vw_vendor_rating_by_dimension AS SELECT pd.vendor_id, pd.agency_name, rd.dimension_id, rd.dimension_name, COUNT(*) AS score_count, ROUND(AVG(rs.score_value), 2) AS avg_score, MIN(rs.score_value) AS min_score, MAX(rs.score_value) AS max_score FROM Review_Score rs JOIN Rating_Dimension rd ON rd.dimension_id = rs.dimension_id JOIN Review r ON r.review_id = rs.review_id JOIN vw_project_details pd ON pd.project_id = r.project_id WHERE r.is_published = true GROUP BY pd.vendor_id, pd.agency_name, rd.dimension_id, rd.dimension_name; }}} === Поглед 16: Промени на буџетот по проект (vw_project_budget_changes) === Историјата на буџетот: по една редица за секоја промена на буџетот на проект, со стариот и новиот износ, разликата и процентуалната промена. Се чита филтриран по проект на страницата на проектот или по клиент и софтверска агенција за преглед на сите промени на една страна. {{{#!sql CREATE OR REPLACE VIEW vw_project_budget_changes AS SELECT pba.audit_id, pd.project_id, pd.project_name, pd.vendor_id, pd.client_id, pd.currency_code, pba.old_budget, pba.new_budget, pba.new_budget - pba.old_budget AS delta, -- процентуална промена; NULLIF штити од делење со нула ROUND(100.0 * (pba.new_budget - pba.old_budget) / NULLIF(pba.old_budget, 0), 1) AS pct_change, pba.created_at AS changed_at FROM Project_Budget_Audit pba JOIN vw_project_details pd ON pd.project_id = pba.project_id; }}}