wiki:ReportsProcedures

Напредни извештаи од базата (PostgreSQL)

Оваа страна прикажува 3 покомплексни извештаи (со релациска алгебра), докази за индекси (EXPLAIN/ANALYZE) и доказ за транскации + тригери.

Процедури и тригери (Stored Procedures & Triggers)

Што додадовме

  • Погледи: vw_user_spend, vw_class_utilization, vw_training_pop_monthly
  • Тригери: BEFORE INSERT guard, AFTER INSERT/DELETE на "User_Booked_Class" (одржуваат seats_available)
  • Складирана функција: book_class(user_id, class_id)
  • Индекси (од страната Оптимизација), пожелно да се погледне прва

Скрипта

Целиот SQL е во backend/sql/blisscore_enhancements.sql и содржи:

  • 3x CREATE VIEW (vw_user_spend, vw_class_utilization, vw_training_pop_monthly)
  • 3x CREATE FUNCTION за тригерите (guard/decrement/increment)
  • 3x CREATE TRIGGER за "User_Booked_Class"
  • CREATE FUNCTION book_class(p_user_id BIGINT, p_class_id BIGINT) RETURNS text
  • Индекси

Извршени се SQL надградбите (views, triggers, function, indexes) преку Node

cd backend
npm run db:apply

Излез: 

"Enhancements applied (views, triggers, function, indexes)."

Потоа внесовме мал демо-сет: 

npm run db:seed

Излез: 

Seed done. User IDs: { ana: '12', bojan: '13', ciro: '14', classId: '7' }

1) Топ потрошувачи (пакети + мерч) со рангирање

Цел: По корисник, колку потрошил (сума на пакети + мерч) и кој му е рангот според вкупниот трошок. Користен поглед: vw_user_spend

SQL: 

SELECT
  user_id, username, email,
  spend_packages, spend_merch, total_spend,
  RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
FROM vw_user_spend
ORDER BY total_spend DESC, user_id;

Релациска алгебра: 

Π_{user_id,username,email,spend_packages,spend_merch,total_spend}
(
  User
  ⟕ (γ_{user_id; SUM(price)→spend_packages}(User_Purchased_Package ⋈ Package))
  ⟕ (γ_{user_id; SUM(price)→spend_merch}(User_Purchased_Merch ⋈ Merch_Items))
)

Резултати (генерирани со npm run db:reports, датотека: backend/proofs/top_spenders.json): 

[
  { "user_id": "8",  "username": "mikiYoga", "email": "miki@example.com ",  "spend_packages": "45.00", "spend_merch": "12.00", "total_spend": "57.00", "spend_rank": "1" },
  { "user_id": "13", "username": "bojan",     "email": "bojan@example.com ", "spend_packages": "0",     "spend_merch": "30.00", "total_spend": "30.00", "spend_rank": "2" },
  { "user_id": "12", "username": "ana",       "email": "ana@example.com ",   "spend_packages": "25.00", "spend_merch": "0",     "total_spend": "25.00", "spend_rank": "3" },
  { "user_id": "9",  "username": "davidG",    "email": "david@example.com ", "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" },
  { "user_id": "10", "username": "proba1",    "email": "proba1@gmail.com ",  "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" },
  { "user_id": "14", "username": "ciro",      "email": "ciro@example.com ",  "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" }
]

2) Исполнетост на часови со дневно рангирање

Цел: За секој час: booked/capacity (во %) и ранг за истиот ден. Користен поглед: vw_class_utilization

SQL: 

SELECT
  *,
  DENSE_RANK() OVER (PARTITION BY date ORDER BY utilization_pct DESC NULLS LAST) AS daily_rank
FROM vw_class_utilization
ORDER BY date, start_time, class_id;

Релациска алгебра: 

γ_{class_id; COUNT(user_id)→booked}(Class ⟕ User_Booked_Class)
калкулација utilization_pct = booked / capacity

Резултати (датотека: backend/proofs/class_utilization.json): 

[
  { "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" },
  { "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" },
  { "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" }
]

3) Популарност на тренинзи по месец

Цел: По месец, броиме уникатни корисници кои резервирале часови што содржат даден тренинг; одредуваме месечен ранг. Користен поглед: vw_training_pop_monthly

SQL: 

SELECT
  training_id, training_name, month, num_bookings,
  RANK() OVER (PARTITION BY month ORDER BY num_bookings DESC NULLS LAST) AS rank_in_month
FROM vw_training_pop_monthly
ORDER BY month DESC, rank_in_month, training_name;

Релациска алгебра: 

γ_{training_id,month; COUNT(DISTINCT user_id)→num_bookings}
( Training ⋈ Class_Includes_Training ⋈ Class ⟕ User_Booked_Class )

Резултати (датотека: backend/proofs/training_pop_monthly.json): 

[
  { "training_id": "7", "training_name": "Vinyasa",       "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
  { "training_id": "8", "training_name": "Yin",           "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
  { "training_id": "6", "training_name": "Hatha Basics",  "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
  { "training_id": "5", "training_name": "Vinyasa Flow",  "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" }
]

4) EXPLAIN/ANALYZE (доказ за индекси)

Ги генериравме плановите со „forced“ планови (enable_seqscan=off) заради мал сет: 

npm run db:explain

Датотеки:

  • backend/proofs/events_explain_forced.json
  • backend/proofs/class_bookings_explain_forced.json

A) Настани по датум/време – составен индекс (date,time)

Клучен извадок што докажува Index Scan преку idx_event_date_time: 

{
  "Node Type": "Index Scan",
  "Index Name": "idx_event_date_time",
  "Relation Name": "Event",
  "Alias": "Event",
  "Index Cond": "(date >= CURRENT_DATE)"
}

B) Резервации по час – два индекса

Индекс врз Class(date,start_time): idx_class_date_time: 

{
  "Node Type": "Index Scan",
  "Index Name": "idx_class_date_time",
  "Relation Name": "Class",
  "Alias": "c",
  "Index Cond": "(date >= CURRENT_DATE)"
}

Индекс врз User_Booked_Class(class_id): idx_ubc_class: 

{
  "Node Type": "Index Scan",
  "Index Name": "idx_ubc_class",
  "Relation Name": "User_Booked_Class",
  "Alias": "ubc",
  "Index Cond": "(class_id = c.class_id)"
}

5) Доказ: Транскации + тригери (book_class, seats_available)

Извршување: 

npm run db:proofs

Конзолен излез: 

=== PROOF: transactions + triggers ===
Class before: { class_id: '7', capacity: 2, seats_available: 1 }
Users: [ { user_id: '12', username: 'ana' }, { user_id: '13', username: 'bojan' }, { user_id: '14', username: 'ciro' } ]
book_class(ana): OK
book_class(bojan): CLASS_FULL
book_class(ciro): CLASS_FULL (expected CLASS_FULL)
Class after bookings: { class_id: '7', capacity: 2, seats_available: 0 }
Class after delete (seats should increase by 1): { class_id: '7', capacity: 2, seats_available: 1 }
Proof finished.
Saved views snapshot in proofs/views_snapshot.json
Saved transaction proof in proofs/transactions_proof.json

Објаснување: Повикот book_class() се извршува во транскација. BEFORE INSERT тригерот блокира преполнување (ако нема слободни места, CLASS_FULL), а AFTER INSERT/DELETE тригерите го одржуваат seats_available. Излезот погоре докажува: успешна резервација, потоа две одбиени резервации (полн час), и на крај бришење што ја зголемува променливата seats_available за +1.

Last modified 2 weeks ago Last modified on 09/30/25 23:57:52
Note: See TracWiki for help on using the wiki.