168 lines
7.1 KiB
Python
168 lines
7.1 KiB
Python
"""Context-builder query functions for the Overview tab (Chapter 08).
|
|
|
|
Every function here reads request_stats_hourly / request_stats_daily /
|
|
referrer_stats_daily / browser_stats_daily — never log_entries — per
|
|
Ch03 rule 6 / Ch08's own data-source rule.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
from datetime import date
|
|
|
|
from sqlalchemy import case, func
|
|
|
|
from app.extensions import db
|
|
from app.models.browser_stats import BrowserStatsDaily
|
|
from app.models.referrer_stats import ReferrerStatsDaily
|
|
from app.models.request_stats import RequestStatsDaily, RequestStatsHourly
|
|
from app.utils.dates import day_bounds
|
|
|
|
# ASSUMPTION (flagged in Ch08): Ch08 doesn't give a numeric threshold for
|
|
# "hourly vs daily depending on range width" — picked 3 days.
|
|
HOURLY_GRANULARITY_THRESHOLD_DAYS = 3
|
|
|
|
|
|
def get_kpis(from_date: date, to_date: date) -> dict:
|
|
"""All six Ch08 KPI-card fields, plus peak-day (folded in — Ch11 has
|
|
no dedicated route for it and Ch08 calls for only a 'simple max-lookup').
|
|
"""
|
|
row = (
|
|
db.session.query(
|
|
func.coalesce(func.sum(RequestStatsDaily.count), 0).label("total_requests"),
|
|
func.coalesce(func.sum(RequestStatsDaily.unique_ips), 0).label("unique_ips_sum"),
|
|
func.coalesce(func.sum(RequestStatsDaily.bytes_sum), 0).label("total_bandwidth"),
|
|
func.coalesce(func.sum(RequestStatsDaily.error_count), 0).label("total_errors"),
|
|
)
|
|
.filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
|
|
.one()
|
|
)
|
|
num_days = (to_date - from_date).days + 1
|
|
avg_response_size = (row.total_bandwidth / row.total_requests) if row.total_requests else 0.0
|
|
error_rate_pct = (row.total_errors / row.total_requests * 100) if row.total_requests else 0.0
|
|
avg_requests_per_day = row.total_requests / num_days if num_days else 0.0
|
|
|
|
peak_row = (
|
|
db.session.query(RequestStatsDaily.date, RequestStatsDaily.count)
|
|
.filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
|
|
.order_by(RequestStatsDaily.count.desc())
|
|
.first()
|
|
)
|
|
|
|
return {
|
|
"total_requests": row.total_requests,
|
|
# APPROXIMATION (flagged in Ch08): sum of daily unique_ips over-counts
|
|
# repeat visitors across days.
|
|
"unique_ips": row.unique_ips_sum,
|
|
"total_bandwidth_bytes": row.total_bandwidth,
|
|
"avg_response_size_bytes": round(avg_response_size, 1),
|
|
"error_rate_pct": round(error_rate_pct, 2),
|
|
"avg_requests_per_day": round(avg_requests_per_day, 1),
|
|
"peak_day": {"date": peak_row.date.isoformat(), "count": peak_row.count} if peak_row else None,
|
|
}
|
|
|
|
|
|
def get_traffic_chart_series(from_date: date, to_date: date) -> dict:
|
|
span_days = (to_date - from_date).days + 1
|
|
if span_days <= HOURLY_GRANULARITY_THRESHOLD_DAYS:
|
|
start, end = day_bounds(from_date, to_date)
|
|
rows = (
|
|
db.session.query(RequestStatsHourly.date_hour, func.sum(RequestStatsHourly.count).label("count"))
|
|
.filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
|
|
.group_by(RequestStatsHourly.date_hour)
|
|
.order_by(RequestStatsHourly.date_hour)
|
|
.all()
|
|
)
|
|
return {"granularity": "hourly", "series": [{"t": r.date_hour.isoformat(), "count": r.count} for r in rows]}
|
|
|
|
rows = (
|
|
db.session.query(RequestStatsDaily.date, RequestStatsDaily.count)
|
|
.filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
|
|
.order_by(RequestStatsDaily.date)
|
|
.all()
|
|
)
|
|
return {"granularity": "daily", "series": [{"t": r.date.isoformat(), "count": r.count} for r in rows]}
|
|
|
|
|
|
def get_status_code_breakdown(from_date: date, to_date: date) -> dict:
|
|
start, end = day_bounds(from_date, to_date)
|
|
bucket = case(
|
|
(RequestStatsHourly.status_code < 300, "2xx"),
|
|
(RequestStatsHourly.status_code < 400, "3xx"),
|
|
(RequestStatsHourly.status_code < 500, "4xx"),
|
|
else_="5xx",
|
|
)
|
|
rows = (
|
|
db.session.query(bucket.label("bucket"), func.sum(RequestStatsHourly.count).label("count"))
|
|
.filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
|
|
.group_by("bucket")
|
|
.all()
|
|
)
|
|
breakdown = {"2xx": 0, "3xx": 0, "4xx": 0, "5xx": 0}
|
|
for r in rows:
|
|
breakdown[r.bucket] = r.count
|
|
return breakdown
|
|
|
|
|
|
def get_top_urls(from_date: date, to_date: date, page: int, per_page: int) -> tuple[list[list], int]:
|
|
"""Top URLs by hits. NOTE (flagged in Ch08): only available within the
|
|
~90-day hourly retention window (Ch06) — request_stats_daily has no
|
|
path column, so a wider range returns nothing here.
|
|
"""
|
|
start, end = day_bounds(from_date, to_date)
|
|
hits = func.sum(RequestStatsHourly.count)
|
|
errors = func.sum(case((RequestStatsHourly.status_code >= 400, RequestStatsHourly.count), else_=0))
|
|
bytes_sum = func.sum(RequestStatsHourly.bytes_sent_sum)
|
|
|
|
base_query = (
|
|
db.session.query(RequestStatsHourly.path, hits.label("hits"), bytes_sum.label("bytes_sum"), errors.label("errors"))
|
|
.filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
|
|
.group_by(RequestStatsHourly.path)
|
|
)
|
|
total = base_query.count()
|
|
rows = base_query.order_by(hits.desc()).offset((page - 1) * per_page).limit(per_page).all()
|
|
|
|
results = [
|
|
[
|
|
r.path,
|
|
r.hits,
|
|
round(r.bytes_sum / r.hits, 1) if r.hits else 0.0,
|
|
round(r.errors / r.hits * 100, 2) if r.hits else 0.0,
|
|
]
|
|
for r in rows
|
|
]
|
|
return results, total
|
|
|
|
|
|
def get_top_referrers(from_date: date, to_date: date, page: int, per_page: int) -> tuple[list[list], int]:
|
|
"""Domain-bucketed referrers (Method A, Ch08 follow-up)."""
|
|
hits = func.sum(ReferrerStatsDaily.count)
|
|
base_query = (
|
|
db.session.query(ReferrerStatsDaily.referrer_domain, hits.label("hits"))
|
|
.filter(ReferrerStatsDaily.date >= from_date, ReferrerStatsDaily.date <= to_date)
|
|
.group_by(ReferrerStatsDaily.referrer_domain)
|
|
)
|
|
total = base_query.count()
|
|
rows = base_query.order_by(hits.desc()).offset((page - 1) * per_page).limit(per_page).all()
|
|
return [[r.referrer_domain, r.hits] for r in rows], total
|
|
|
|
|
|
def get_browser_breakdown(from_date: date, to_date: date) -> dict:
|
|
"""Human-only (bots excluded at rollup-write time, Ch08)."""
|
|
browser_rows = (
|
|
db.session.query(BrowserStatsDaily.browser, func.sum(BrowserStatsDaily.count).label("count"))
|
|
.filter(BrowserStatsDaily.date >= from_date, BrowserStatsDaily.date <= to_date)
|
|
.group_by(BrowserStatsDaily.browser)
|
|
.order_by(func.sum(BrowserStatsDaily.count).desc())
|
|
.all()
|
|
)
|
|
os_rows = (
|
|
db.session.query(BrowserStatsDaily.os, func.sum(BrowserStatsDaily.count).label("count"))
|
|
.filter(BrowserStatsDaily.date >= from_date, BrowserStatsDaily.date <= to_date)
|
|
.group_by(BrowserStatsDaily.os)
|
|
.order_by(func.sum(BrowserStatsDaily.count).desc())
|
|
.all()
|
|
)
|
|
return {
|
|
"by_browser": [{"name": r.browser, "count": r.count} for r in browser_rows],
|
|
"by_os": [{"name": r.os, "count": r.count} for r in os_rows],
|
|
}
|