Changes between Version 12 and Version 13 of ReportsProcedures


Ignore:
Timestamp:
09/26/25 19:25:56 (3 weeks ago)
Author:
223075
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ReportsProcedures

    v12 v13  
    11= Напредни извештаи од базата (PostgreSQL) =
    2 ----
    3 Оваа страна прикажува 3 покомплексни извештаи.
    42
    5 ==  Извршени се SQL надградбите (views, triggers, function, indexes) преку Node:==
     3Оваа страна прикажува 3 покомплексни извештаи (со релациска алгебра), докази за индекси (EXPLAIN/ANALYZE) и доказ за транскации + тригери.
     4
     5==  Извршени се SQL надградбите (views, triggers, function, indexes) преку Node ==
    66{{{
    77cd backend
    88npm run db:apply
    99}}}
     10Очекуван излез:
    1011{{{
    1112"Enhancements applied (views, triggers, function, indexes)."
    … …  
    1617npm run db:seed
    1718}}}
     19Излез:
    1820{{{
    1921Seed done. User IDs: { ana: '12', bojan: '13', ciro: '14', classId: '7' }
    2022}}}
    2123
    22 ----
     24== 1) Топ потрошувачи (пакети + мерч) со рангирање ==
     25Цел: По корисник, колку потрошил (сума на пакети + мерч) и кој му е рангот според вкупниот трошок.
     26Користен поглед: vw_user_spend
    2327
    24 == 1) Топ потрошувачи (пакети + мерч) со рангирање ==
    25 **Цел:** По корисник, колку потрошил (сума на пакети + мерч) и кој му е рангот според вкупниот трошок.
    26 
    27 **Користен поглед:** `vw_user_spend`
    28 
    29 **SQL:**
     28SQL:
    3029{{{
    31 SELECT user_id, username, email,
    32        spend_packages, spend_merch, total_spend,
    33        RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
     30SELECT
     31user_id, username, email,
     32spend_packages, spend_merch, total_spend,
     33RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
    3434FROM vw_user_spend
    3535ORDER BY total_spend DESC, user_id;
    3636}}}
    3737
    38 **Релациска алгебра:**
     38Релациска алгебра (информативно):
    3939{{{
    4040Π_{user_id,username,email,spend_packages,spend_merch,total_spend}
    41  ( User ⟕ (γ_{user_id; SUM(price)→spend_packages}(User_Purchased_Package ⋈ Package))
    42         ⟕ (γ_{user_id; SUM(price)→spend_merch}(User_Purchased_Merch ⋈ Merch_Items)) )
     41(
     42User
     43⟕ (γ_{user_id; SUM(price)→spend_packages}(User_Purchased_Package ⋈ Package))
     44⟕ (γ_{user_id; SUM(price)→spend_merch}(User_Purchased_Merch ⋈ Merch_Items))
     45)
    4346}}}
    4447
    45 **Извршување:**
    46 {{{
    47 npm run db:reports
    48 
    49 ги создава датотеките:
    50 
    51 backend/proofs/top_spenders.json
    52 
    53 backend/proofs/class_utilization.json
    54 
    55 backend/proofs/training_pop_monthly.json
    56 }}}
    57 {{{
     48Реални резултати (генерирани со npm run db:reports, фајл: backend/proofs/top_spenders.json):
     49{{{json
    5850[
    59   {
    60     "user_id": "8",
    61     "username": "mikiYoga",
    62     "email": "miki@example.com",
    63     "spend_packages": "45.00",
    64     "spend_merch": "12.00",
    65     "total_spend": "57.00",
    66     "spend_rank": "1"
    67   },...
     51{ "user_id": "8", "username": "mikiYoga", "email": "miki@example.com
     52", "spend_packages": "45.00", "spend_merch": "12.00", "total_spend": "57.00", "spend_rank": "1" },
     53{ "user_id": "13", "username": "bojan", "email": "bojan@example.com
     54", "spend_packages": "0", "spend_merch": "30.00", "total_spend": "30.00", "spend_rank": "2" },
     55{ "user_id": "12", "username": "ana", "email": "ana@example.com
     56", "spend_packages": "25.00", "spend_merch": "0", "total_spend": "25.00", "spend_rank": "3" },
     57{ "user_id": "9", "username": "davidG", "email": "david@example.com
     58", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" },
     59{ "user_id": "10", "username": "proba1", "email": "proba1@gmail.com
     60", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" },
     61{ "user_id": "14", "username": "ciro", "email": "ciro@example.com
     62", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" }
    6863]
    6964}}}
    7065
    71 ----
     66== 2) Исполнетост на часови со дневно рангирање ==
     67Цел: За секој час: booked/capacity (во %) и ранг за истиот ден.
     68Користен поглед: vw_class_utilization
    7269
    73 == 2) Исполнетост на часови со дневно рангирање ==
    74 **Цел:** За секој час: booked/capacity (во %) и ранг за истиот ден.
    75 
    76 **Користен поглед:** `vw_class_utilization`
    77 
    78 **SQL:**
     70SQL:
    7971{{{
    80 SELECT *,
    81        DENSE_RANK() OVER (PARTITION BY date ORDER BY utilization_pct DESC NULLS LAST) AS daily_rank
     72SELECT
     73*,
     74DENSE_RANK() OVER (PARTITION BY date ORDER BY utilization_pct DESC NULLS LAST) AS daily_rank
    8275FROM vw_class_utilization
    8376ORDER BY date, start_time, class_id;
    8477}}}
    8578
    86 **Релациска алгебра:**
     79Релациска алгебра (информативно):
    8780{{{
    8881γ_{class_id; COUNT(user_id)→booked}(Class ⟕ User_Booked_Class)
    89 + калкулација utilization_pct = booked / capacity
     82
     83калкулација utilization_pct = booked / capacity
    9084}}}
    9185
    92 {{{
    93  [
    94   {
    95     "class_id": "5",
    96     "date": "2025-06-09T22:00:00.000Z",
    97     "start_time": "08:00:00",
    98     "end_time": "09:00:00",
    99     "location": "Studio A",
    100     "capacity": 20,
    101     "booked": "1",
    102     "utilization_pct": "5.00",
    103     "instructor_id": "5",
    104     "daily_rank": "2"
    105   },...
     86Реални резултати (фајл: backend/proofs/class_utilization.json):
     87{{{json
     88[
     89{ "class_id": "5", "date": "2025-06-09T22:00:00.000Z", "start_time": "08:00:00", "end_time": "09:00:00", "location": "Studio A", "capacity": 20, "booked": "1", "utilization_pct": "5.00", "instructor_id": "5", "daily_rank": "2" },
     90{ "class_id": "6", "date": "2025-06-09T22:00:00.000Z", "start_time": "09:30:00", "end_time": "10:30:00", "location": "Studio B", "capacity": 15, "booked": "1", "utilization_pct": "6.67", "instructor_id": "6", "daily_rank": "1" },
     91{ "class_id": "7", "date": "2025-09-26T22:00:00.000Z", "start_time": "18:00:00", "end_time": "19:00:00", "location": "Studio A", "capacity": 2, "booked": "1", "utilization_pct": "50.00", "instructor_id": "7", "daily_rank": "1" }
    10692]
    10793}}}
    10894
    109 ----
     95== 3) Популарност на тренинзи по месец ==
     96Цел: По месец, броиме уникатни корисници кои резервирале часови што содржат даден тренинг; даваме месечен ранг.
     97Користен поглед: vw_training_pop_monthly
    11098
    111 == 3) Популарност на тренинзи по месец ==
    112 **Цел:** По месец, броиме уникатни корисници кои резервирале часови што содржат даден тренинг; даваме месечен ранг.
    113 
    114 **Користен поглед:** `vw_training_pop_monthly`
    115 
    116 **SQL:**
     99SQL:
    117100{{{
    118 SELECT training_id, training_name, month, num_bookings,
    119        RANK() OVER (PARTITION BY month ORDER BY num_bookings DESC NULLS LAST) AS rank_in_month
     101SELECT
     102training_id, training_name, month, num_bookings,
     103RANK() OVER (PARTITION BY month ORDER BY num_bookings DESC NULLS LAST) AS rank_in_month
    120104FROM vw_training_pop_monthly
    121105ORDER BY month DESC, rank_in_month, training_name;
    122106}}}
    123107
    124 **Релациска алгебра:**
     108Релациска алгебра (информативно):
    125109{{{
    126110γ_{training_id,month; COUNT(DISTINCT user_id)→num_bookings}
    127  ( Training ⋈ Class_Includes_Training ⋈ Class ⟕ User_Booked_Class )
     111( Training ⋈ Class_Includes_Training ⋈ Class ⟕ User_Booked_Class )
    128112}}}
    129113
    130 {{{
     114Реални резултати (фајл: backend/proofs/training_pop_monthly.json):
     115{{{json
    131116[
    132   {
    133     "training_id": "7",
    134     "training_name": "Vinyasa",
    135     "month": "2025-08-31T22:00:00.000Z",
    136     "num_bookings": "1",
    137     "rank_in_month": "1"
    138   },...
    139 ]
     117{ "training_id": "7", "training_name": "Vinyasa", "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     118{ "training_id": "8", "training_name": "Yin", "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     119{ "training_id": "6", "training_name": "Hatha Basics", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     120{ "training_id": "5", "training_name": "Vinyasa Flow", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" }
     121]
    140122}}}
    141123
    142 ----
    143 
    144124== 4) EXPLAIN/ANALYZE (доказ за индекси) ==
    145 Ги генерираме плановите:
     125Ги генериравме плановите со „forced“ планови (enable_seqscan=off) заради мал сет:
    146126{{{
    147127npm run db:explain
    148128}}}
     129Фајлови:
    149130
    150 *Напомена:* Поради мал сет, за демонстрација користевме и „forced“ планови со `enable_seqscan=off`. Фајлови:
    151 - `backend/proofs/events_explain_forced.json`
    152 - `backend/proofs/class_bookings_explain_forced.json`
     131backend/proofs/events_explain_forced.json
     132
     133backend/proofs/class_bookings_explain_forced.json
    153134
    154135=== A) Настани по датум/време – составен индекс (date,time) ===
    155 Фајл: `backend/proofs/events_explain_forced.json`
    156 
    157 Очекуван извадок (Index Scan преку **idx_event_date_time**):
     136Фајл: backend/proofs/events_explain_forced.json
     137Клучен извадок што докажува Index Scan преку idx_event_date_time:
    158138{{{json
    159139{
    160   "Node Type": "Index Scan",
    161   "Index Name": "idx_event_date_time",
    162   "Relation Name": "Event",
    163   "Alias": "Event",
    164   "Index Cond": "(date >= CURRENT_DATE)"
     140"Node Type": "Index Scan",
     141"Index Name": "idx_event_date_time",
     142"Relation Name": "Event",
     143"Alias": "Event",
     144"Index Cond": "(date >= CURRENT_DATE)"
    165145}
    166146}}}
    167147
    168148=== B) Резервации по час – два индекса ===
    169 Фајл: `backend/proofs/class_bookings_explain_forced.json`
     149Фајл: backend/proofs/class_bookings_explain_forced.json
    170150
    171 1) Индекс врз Class за датум:
     151Индекс врз Class(date,start_time) → idx_class_date_time:
    172152{{{json
    173153{
    174   "Node Type": "Index Scan",
    175   "Index Name": "idx_class_date_time",
    176   "Relation Name": "Class",
    177   "Alias": "c",
    178   "Index Cond": "(date >= CURRENT_DATE)"
     154"Node Type": "Index Scan",
     155"Index Name": "idx_class_date_time",
     156"Relation Name": "Class",
     157"Alias": "c",
     158"Index Cond": "(date >= CURRENT_DATE)"
    179159}
    180160}}}
    181161
    182 2) Индекс врз User_Booked_Class за поврзување по class_id:
     162Индекс врз User_Booked_Class(class_id) → idx_ubc_class:
    183163{{{json
    184164{
    185   "Node Type": "Index Scan",
    186   "Index Name": "idx_ubc_class",
    187   "Relation Name": "User_Booked_Class",
    188   "Alias": "ubc",
    189   "Index Cond": "(class_id = c.class_id)"
     165"Node Type": "Index Scan",
     166"Index Name": "idx_ubc_class",
     167"Relation Name": "User_Booked_Class",
     168"Alias": "ubc",
     169"Index Cond": "(class_id = c.class_id)"
    190170}
    191171}}}
     172
     173== 5) Доказ: Транскации + тригери (book_class, seats_available) ==
     174Извршување:
     175{{{
     176npm run db:proofs
     177}}}
     178Конзолен излез (реален):
     179{{{
     180=== PROOF: transactions + triggers ===
     181Class before: { class_id: '7', capacity: 2, seats_available: 1 }
     182Users: [ { user_id: '12', username: 'ana' }, { user_id: '13', username: 'bojan' }, { user_id: '14', username: 'ciro' } ]
     183book_class(ana): OK
     184book_class(bojan): CLASS_FULL
     185book_class(ciro): CLASS_FULL (expected CLASS_FULL)
     186Class after bookings: { class_id: '7', capacity: 2, seats_available: 0 }
     187Class after delete (seats should increase by 1): { class_id: '7', capacity: 2, seats_available: 1 }
     188Proof finished.
     189Saved views snapshot -> proofs/views_snapshot.json
     190Saved transaction proof -> proofs/transactions_proof.json
     191}}}
     192
     193Објаснување:
     194Повикот book_class() се извршува во транскација. BEFORE INSERT тригерот блокира преполнување (ако нема слободни места, CLASS_FULL), а AFTER INSERT/DELETE тригерите го одржуваат seats_available. Излезот погоре докажува: успешна резервација, потоа две одбиени резервации (полн час), и на крај бришење што ја зголемува променливата seats_available за +1.
     195
     196Напомена: Сите views, тригери, функцијии и индекси се применети од backend/sql/ и се повторно-извршливи. Доказите се архивирани во backend/proofs/*.json