Changes between Version 2 and Version 3 of DatabaseCreation


Ignore:
Timestamp:
09/13/26 21:23:10 (2 weeks ago)
Author:
231075
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v2 v3  
    11= DatabaseCreation =
    22
    3 == Имплементација на базата ==
    4 
    5 Релационата база на податоци е имплементирана во PostgreSQL според претходно дефинираниот релационен модел.
    6 
    7 Структурата на базата е дефинирана во:
     3== Опис ==
     4
     5Релационата база на податоци е имплементирана во PostgreSQL според претходно дефинираниот ER и релационен модел.
     6
     7DDL скриптата ги дефинира сите потребни табели, примарни и странски клучеви, ограничувања, проверки и default вредности.
     8
     9Целосната скрипта е достапна во:
    810
    911* [attachment:ddl.sql ddl.sql]
    1012
    11 За генерирање на податоците кои се користат при пополнување и тестирање на базата се користи:
    12 
    13 * [attachment:seed_generator.py seed_generator.py]
    14 
    15 == Генерирање и внесување на податоци ==
    16 
    17 Податоците се генерираат со Python скрипта и се запишуваат во CSV датотеки кои одговараат на табелите од базата.
    18 
    19 За генерирањето се користи `Faker`, при што е поставен фиксен seed за резултатите да можат повторно да се репродуцираат:
    20 
    21 {{{#!python
    22 SEED = 42
    23 
    24 fake = Faker()
    25 Faker.seed(SEED)
    26 random.seed(SEED)
    27 }}}
    28 
    29 За запишување на генерираните записи се користи заедничка функција:
    30 
    31 {{{#!python
    32 @contextmanager
    33 def csv_writer(filename: str, fieldnames: list):
    34 path = OUT_DIR / filename
    35 with path.open("w", newline="", encoding="utf-8") as f:
    36 w = csv.DictWriter(f, fieldnames=fieldnames)
    37 w.writeheader()
    38 yield w
    39 }}}
    40 
    41 == Основни податоци ==
    42 
    43 Најпрво се генерираат податоците за табелите од кои зависат останатите записи. На пример, статусите на проектите се дефинирани на следниот начин:
    44 
    45 {{{#!python
    46 STATUSES = [
    47 "Draft",
    48 "In Progress",
    49 "On Hold",
    50 "Completed",
    51 "Cancelled",
    52 "Under Review"
    53 ]
    54 
    55 write_csv(
    56 "project_status.csv",
    57 ["status_id", "status_name"],
    58 [
    59 {"status_id": i + 1, "status_name": s}
    60 for i, s in enumerate(STATUSES)
    61 ],
    62 )
    63 }}}
    64 
    65 На сличен начин се генерираат и `Role`, `Permission`, `Industry`, `Technology`, `Rating_Dimension` и `Subscription_Tier`.
    66 
    67 == Клиенти и продавачи ==
    68 
    69 Записите за клиентите и продавачите се генерираат со реалистични имиња, веб страници и контакт информации:
    70 
    71 {{{#!python
    72 write_csv(
    73 "vendor.csv",
    74 ["vendor_id", "agency_name", "website"],
    75 [
    76 {
    77 "vendor_id": i + 1,
    78 "agency_name": fake.company(),
    79 "website": fake.url()
    80 }
    81 for i in range(NUM_VENDORS)
    82 ],
    83 )
    84 
    85 write_csv(
    86 "client.csv",
    87 ["client_id", "industry_id", "company_name", "contact_email"],
    88 [
    89 {
    90 "client_id": i + 1,
    91 "industry_id": random.choice(industry_ids),
    92 "company_name": fake.company(),
    93 "contact_email": fake.company_email(),
    94 }
    95 for i in range(NUM_CLIENTS)
    96 ],
    97 )
    98 }}}
    99 
    100 `industry_id` се избира од претходно генерираните индустрии, со што се почитува врската помеѓу `Client` и `Industry`.
     13== Основни табели ==
     14
     15Во почетниот дел од скриптата се креираат помошните табели кои понатаму се користат од останатите ентитети.
     16
     17Пример за `Role`:
     18
     19{{{#!sql
     20CREATE TABLE Role (
     21role_id     SERIAL NOT NULL PRIMARY KEY,
     22role_name   text   NOT NULL UNIQUE,
     23description text
     24);
     25}}}
     26
     27На сличен начин се дефинирани `Permission`, `Industry`, `Project_Status`, `Technology`, `Rating_Dimension` и `Subscription_Tier`.
    10128
    10229== Корисници ==
    10330
    104 За различните типови на корисници се генерираат заедничките податоци од табелата `User`:
    105 
    106 {{{#!python
    107 user_rows.append(
    108 {
    109 "user_id": uid,
    110 "type": user_type,
    111 "first_name": fake.first_name(),
    112 "last_name": fake.last_name(),
    113 "email": fake.unique.email(),
    114 "password_hash": hashlib.sha256(
    115 fake.password().encode()
    116 ).hexdigest(),
    117 "is_active": random.random() > 0.25,
    118 "last_login_at": nullable(rand_ts(1), 0.30),
    119 "created_at": rand_ts(3),
    120 "updated_at": rand_ts(1),
    121 }
    122 )
    123 }}}
    124 
    125 Потоа корисниците се поврзуваат со соодветниот клиент, продавач или management улога:
    126 
    127 {{{#!python
    128 write_csv(
    129 "client_user.csv",
    130 ["user_id", "client_id"],
    131 [
    132 {
    133 "user_id": u,
    134 "client_id": random.choice(client_ids)
    135 }
    136 for u in client_user_ids
    137 ],
    138 )
    139 }}}
     31Сите типови на корисници ги содржат заедничките податоци во табелата `"User"`:
     32
     33{{{#!sql
     34CREATE TABLE "User" (
     35user_id       SERIAL    NOT NULL PRIMARY KEY,
     36type          text      NOT NULL
     37CHECK (type IN ('client', 'vendor', 'management')),
     38first_name    text      NOT NULL,
     39last_name     text      NOT NULL,
     40email         text      NOT NULL UNIQUE,
     41password_hash text      NOT NULL,
     42is_active     bool      NOT NULL DEFAULT false,
     43last_login_at timestamp,
     44created_at    timestamp NOT NULL DEFAULT NOW(),
     45updated_at    timestamp NOT NULL DEFAULT NOW()
     46);
     47}}}
     48
     49Полето `type` е ограничено со `CHECK`, така што може да има само една од трите дозволени вредности.
     50
     51За конкретните типови на корисници се користат дополнителни табели. На пример:
     52
     53{{{#!sql
     54CREATE TABLE Client_User (
     55user_id   int4 NOT NULL PRIMARY KEY,
     56client_id int4 NOT NULL,
     57
     58CONSTRAINT fk_clientuser_user
     59FOREIGN KEY (user_id) REFERENCES "User" (user_id)
     60ON DELETE CASCADE
     61ON UPDATE CASCADE,
     62
     63CONSTRAINT fk_clientuser_client
     64FOREIGN KEY (client_id) REFERENCES Client (client_id)
     65ON DELETE RESTRICT
     66ON UPDATE CASCADE
     67);
     68}}}
     69
     70На истиот принцип се дефинирани и `Vendor_User` и `Management_User`.
     71
     72== Клиенти, продавачи и договори ==
     73
     74Клиентот е поврзан со индустријата преку странски клуч:
     75
     76{{{#!sql
     77CREATE TABLE Client (
     78client_id     SERIAL NOT NULL PRIMARY KEY,
     79industry_id   int4   NOT NULL,
     80company_name  text   NOT NULL,
     81contact_email text   NOT NULL,
     82
     83CONSTRAINT fk_client_industry
     84FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
     85ON DELETE RESTRICT
     86ON UPDATE CASCADE
     87);
     88}}}
     89
     90Врската помеѓу клиент и vendor е претставена преку договор:
     91
     92{{{#!sql
     93CREATE TABLE Client_Vendor_Contract (
     94contract_id     SERIAL NOT NULL PRIMARY KEY,
     95client_id       int4   NOT NULL,
     96vendor_id       int4   NOT NULL,
     97contract_number text UNIQUE,
     98contract_title  text   NOT NULL,
     99start_date      date   NOT NULL DEFAULT CURRENT_DATE,
     100end_date        date,
     101total_value     numeric(10,2),
     102currency_code   text,
     103terms_summary   text,
     104is_active       bool      NOT NULL DEFAULT true,
     105created_at      timestamp NOT NULL DEFAULT NOW(),
     106updated_at      timestamp NOT NULL DEFAULT NOW(),
     107
     108CONSTRAINT chk_cvc_dates
     109CHECK (end_date IS NULL OR end_date > start_date),
     110
     111CONSTRAINT fk_cvc_client
     112FOREIGN KEY (client_id) REFERENCES Client (client_id),
     113
     114CONSTRAINT fk_cvc_vendor
     115FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
     116);
     117}}}
     118
     119Со `CHECK` ограничувањето се спречува крајниот датум на договорот да биде пред почетниот.
    140120
    141121== Проекти ==
    142122
    143 При генерирање на проект се користат веќе постоечки договори и проектни статуси:
    144 
    145 {{{#!python
    146 w.writerow(
    147 {
    148 "project_id": i + 1,
    149 "contract_id": random.choice(contract_ids),
    150 "status_id": random.choice(status_ids),
    151 "project_name": fake.catch_phrase(),
    152 "start_date": sd,
    153 "end_date": ed,
    154 "budget": round(random.uniform(1_000, 250_000), 2),
    155 }
    156 )
    157 }}}
    158 
    159 Со ова секој проект е поврзан со валиден `Client_Vendor_Contract` и `Project_Status`.
    160 
    161 За many-to-many врската помеѓу проектите и технологиите се генерираат записи во `Project_Technology`:
    162 
    163 {{{#!python
    164 with csv_writer(
    165 "project_technology.csv",
    166 ["project_id", "technology_id"]
    167 ) as w:
    168 for pid in project_ids:
    169 for tid in random.sample(
    170 tech_ids,
    171 random.randint(1, 5)
    172 ):
    173 w.writerow(
    174 {
    175 "project_id": pid,
    176 "technology_id": tid
    177 }
    178 )
    179 }}}
    180 
    181 == Рецензии и оценки ==
    182 
    183 Рецензиите се поврзуваат со постоечки проект и клиентски корисник:
    184 
    185 {{{#!python
    186 w.writerow(
    187 {
    188 "review_id": rid,
    189 "project_id": pid,
    190 "client_user_id": random.choice(client_user_ids),
    191 "review_date": rand_date(0, 3),
    192 "summary_text": fake.paragraph(nb_sentences=3),
    193 "is_published": random.random() > 0.30,
    194 }
    195 )
    196 }}}
    197 
    198 Оценките за рецензиите се внесуваат посебно во `Review_Score`:
    199 
    200 {{{#!python
    201 with csv_writer(
    202 "review_score.csv",
    203 ["review_id", "dimension_id", "score_value"]
    204 ) as w:
    205 for rid in review_ids:
    206 for did in dim_ids:
    207 w.writerow(
    208 {
    209 "review_id": rid,
    210 "dimension_id": did,
    211 "score_value": random.randint(1, 5),
    212 }
    213 )
    214 }}}
    215 
    216 На овој начин секоја оценка се поврзува со соодветна рецензија и димензија на оценување.
    217 
    218 == Историја и промени на податоците ==
    219 
    220 За да се зачува историјата на буџетот на проектите се генерираат записи со стара и нова вредност:
    221 
    222 {{{#!python
    223 w.writerow(
    224 {
    225 "audit_id": audit_id,
    226 "project_id": pid,
    227 "old_budget": old_b,
    228 "new_budget": round(
    229 old_b * random.uniform(0.6, 1.6),
    230 2
    231 ),
    232 }
    233 )
    234 }}}
    235 
    236 Исто така се генерира историја на промена на статусите преку `Project_Status_History`, како и податоци за `Dispute_Ticket`.
    237 
    238 == Полнење на базата ==
    239 
    240 Скриптата генерира поголем број реалистични записи за различните делови од системот, вклучувајќи клиенти, продавачи, договори, проекти, технологии, рецензии и нивните зависни записи.
    241 
    242 Податоците се генерираат во редослед кој ги почитува foreign key зависностите помеѓу табелите. За поголемите табели се генерира доволно големо количество на податоци за тестирање на базата со реалистично оптоварување.
     123Секој проект е поврзан со договор и со тековен статус:
     124
     125{{{#!sql
     126CREATE TABLE Project (
     127project_id   SERIAL        NOT NULL PRIMARY KEY,
     128contract_id  int4          NOT NULL,
     129status_id    int4          NOT NULL,
     130project_name text          NOT NULL,
     131start_date   date          NOT NULL DEFAULT CURRENT_DATE,
     132end_date     date,
     133budget       numeric(10,2) NOT NULL,
     134created_at   timestamp     NOT NULL DEFAULT NOW(),
     135updated_at   timestamp     NOT NULL DEFAULT NOW(),
     136
     137CONSTRAINT chk_project_dates
     138CHECK (end_date IS NULL OR end_date > start_date),
     139
     140CONSTRAINT fk_project_contract
     141FOREIGN KEY (contract_id)
     142REFERENCES Client_Vendor_Contract (contract_id),
     143
     144CONSTRAINT fk_project_status
     145FOREIGN KEY (status_id)
     146REFERENCES Project_Status (status_id)
     147);
     148}}}
     149
     150Технологиите користени на проектите се моделирани со many-to-many релација:
     151
     152{{{#!sql
     153CREATE TABLE Project_Technology (
     154project_id    int4 NOT NULL,
     155technology_id int4 NOT NULL,
     156
     157PRIMARY KEY (project_id, technology_id),
     158
     159CONSTRAINT fk_projtec_project
     160FOREIGN KEY (project_id) REFERENCES Project (project_id)
     161ON DELETE CASCADE,
     162
     163CONSTRAINT fk_projtec_technology
     164FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
     165ON DELETE RESTRICT
     166);
     167}}}
     168
     169== Историја на проектите ==
     170
     171За промените на буџетот се користи посебна audit табела:
     172
     173{{{#!sql
     174CREATE TABLE Project_Budget_Audit (
     175audit_id   SERIAL        NOT NULL PRIMARY KEY,
     176project_id int4          NOT NULL,
     177old_budget numeric(10,2) NOT NULL,
     178new_budget numeric(10,2) NOT NULL,
     179created_at timestamp     NOT NULL DEFAULT NOW(),
     180updated_at timestamp     NOT NULL DEFAULT NOW(),
     181
     182CONSTRAINT fk_budgetaudit_project
     183FOREIGN KEY (project_id) REFERENCES Project (project_id)
     184);
     185}}}
     186
     187Промените на статусот се зачувуваат во `Project_Status_History`, каде покрај проектот и статусот се чува и корисникот кој ја направил промената.
     188
     189== Reviews и оценки ==
     190
     191За секој проект може да постои една рецензија:
     192
     193{{{#!sql
     194CREATE TABLE Review (
     195review_id       SERIAL NOT NULL PRIMARY KEY,
     196project_id      int4   NOT NULL UNIQUE,
     197client_user_id  int4   NOT NULL,
     198review_date     date   NOT NULL DEFAULT CURRENT_DATE,
     199summary_text    text   NOT NULL,
     200is_published    bool   NOT NULL DEFAULT false,
     201
     202CONSTRAINT fk_review_project
     203FOREIGN KEY (project_id) REFERENCES Project (project_id),
     204
     205CONSTRAINT fk_review_clientuser
     206FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
     207);
     208}}}
     209
     210Оценките се чуваат одделно за секоја димензија:
     211
     212{{{#!sql
     213CREATE TABLE Review_Score (
     214review_id    int4 NOT NULL,
     215dimension_id int4 NOT NULL,
     216score_value  int4 NOT NULL,
     217
     218PRIMARY KEY (review_id, dimension_id),
     219
     220CONSTRAINT fk_reviewscore_review
     221FOREIGN KEY (review_id) REFERENCES Review (review_id)
     222ON DELETE CASCADE,
     223
     224CONSTRAINT fk_reviewscore_dimension
     225FOREIGN KEY (dimension_id)
     226REFERENCES Rating_Dimension (dimension_id)
     227);
     228}}}
     229
     230== Dispute систем ==
     231
     232При оспорување на review се креира запис во `Dispute_Ticket`:
     233
     234{{{#!sql
     235CREATE TABLE Dispute_Ticket (
     236ticket_id                     SERIAL NOT NULL PRIMARY KEY,
     237assigned_management_user_id   int4 DEFAULT NULL,
     238review_id                     int4 NOT NULL,
     239vendor_user_id                int4 NOT NULL,
     240reason                        text NOT NULL,
     241is_resolved                   bool NOT NULL DEFAULT false,
     242filed_at                      date NOT NULL DEFAULT CURRENT_DATE,
     243resolved_at                   timestamp,
     244resolution_note               text
     245);
     246}}}
     247
     248Табелата е дополнително поврзана со `Management_User`, `Review` и `Vendor_User` преку странски клучеви.
     249
     250== Полнење со податоци ==
     251
     252За тестирање на базата се користи `seed_generator.py`, кој генерира реалистични податоци и CSV датотеки за табелите.
    243253
    244254Генерирањето се извршува со:
    … …  
    247257python3 seed_generator.py seed_data
    248258}}}
     259
     260Податоците се генерираат според зависностите помеѓу табелите, така што се почитуваат дефинираните foreign key ограничувања.