141 lines
4.9 KiB
Python
141 lines
4.9 KiB
Python
"""Context-builder query functions for the SEO tab (Chapter 09).
|
|
|
|
Per Ch09's own Output instruction, bot-related widgets query bot_hits
|
|
directly (small, indefinitely-retained, indexed — Ch06) rather than a new
|
|
rollup; only the human-side of the crawled-vs-visited comparison needs
|
|
the new human_path_stats_daily table.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
from collections import defaultdict
|
|
from datetime import date
|
|
|
|
from sqlalchemy import case, func
|
|
|
|
from app.extensions import db
|
|
from app.models.bot_hit import BotHit
|
|
from app.models.human_path_stats import HumanPathStatsDaily
|
|
from app.utils.dates import day_bounds
|
|
|
|
# ASSUMPTION (flagged): Ch09 doesn't define "major crawler" for the
|
|
# crawl-frequency chart's series cap.
|
|
TOP_N_BOTS_FOR_CHART = 6
|
|
|
|
|
|
def get_bot_summary(from_date: date, to_date: date) -> list[dict]:
|
|
start, end = day_bounds(from_date, to_date)
|
|
rows = (
|
|
db.session.query(
|
|
BotHit.bot_name,
|
|
func.count().label("hits"),
|
|
func.sum(case((BotHit.verified.is_(True), 1), else_=0)).label("verified_hits"),
|
|
func.max(BotHit.timestamp).label("last_seen"),
|
|
)
|
|
.filter(BotHit.timestamp >= start, BotHit.timestamp < end)
|
|
.group_by(BotHit.bot_name)
|
|
.order_by(func.count().desc())
|
|
.all()
|
|
)
|
|
return [
|
|
{
|
|
"bot_name": r.bot_name,
|
|
"hits": r.hits,
|
|
"verified_pct": round(r.verified_hits / r.hits * 100, 1) if r.hits else 0.0,
|
|
"last_seen": r.last_seen.isoformat() if r.last_seen else None,
|
|
}
|
|
for r in rows
|
|
]
|
|
|
|
|
|
def get_crawl_chart_data(from_date: date, to_date: date, bot_name: str | None) -> dict:
|
|
start, end = day_bounds(from_date, to_date)
|
|
base_filters = [BotHit.timestamp >= start, BotHit.timestamp < end]
|
|
|
|
if bot_name:
|
|
allowed_bots = [bot_name]
|
|
else:
|
|
top_rows = (
|
|
db.session.query(BotHit.bot_name, func.count().label("hits"))
|
|
.filter(*base_filters)
|
|
.group_by(BotHit.bot_name)
|
|
.order_by(func.count().desc())
|
|
.limit(TOP_N_BOTS_FOR_CHART)
|
|
.all()
|
|
)
|
|
allowed_bots = [r.bot_name for r in top_rows]
|
|
|
|
if not allowed_bots:
|
|
return {"series": []}
|
|
|
|
rows = (
|
|
db.session.query(func.date(BotHit.timestamp).label("day"), BotHit.bot_name, func.count().label("count"))
|
|
.filter(*base_filters, BotHit.bot_name.in_(allowed_bots))
|
|
.group_by("day", BotHit.bot_name)
|
|
.order_by("day")
|
|
.all()
|
|
)
|
|
points_by_bot: dict[str, list[dict]] = defaultdict(list)
|
|
for r in rows:
|
|
points_by_bot[r.bot_name].append({"t": r.day, "count": r.count})
|
|
|
|
return {"series": [{"bot_name": b, "points": points_by_bot.get(b, [])} for b in allowed_bots]}
|
|
|
|
|
|
def get_bot_status_codes(from_date: date, to_date: date) -> dict:
|
|
start, end = day_bounds(from_date, to_date)
|
|
bucket = case(
|
|
(BotHit.status_code < 300, "2xx"),
|
|
(BotHit.status_code < 400, "3xx"),
|
|
(BotHit.status_code < 500, "4xx"),
|
|
else_="5xx",
|
|
)
|
|
rows = (
|
|
db.session.query(bucket.label("bucket"), func.count().label("count"))
|
|
.filter(BotHit.timestamp >= start, BotHit.timestamp < end)
|
|
.group_by("bucket")
|
|
.all()
|
|
)
|
|
breakdown = {"2xx": 0, "3xx": 0, "4xx": 0, "5xx": 0}
|
|
for r in rows:
|
|
breakdown[r.bucket] = r.count
|
|
|
|
# Ch09 calls out 404 by name specifically, not just the 4xx bucket.
|
|
not_found_404 = (
|
|
db.session.query(func.count())
|
|
.filter(BotHit.timestamp >= start, BotHit.timestamp < end, BotHit.status_code == 404)
|
|
.scalar()
|
|
)
|
|
return {"breakdown": breakdown, "not_found_404": not_found_404 or 0}
|
|
|
|
|
|
def get_crawled_vs_visited(from_date: date, to_date: date, page: int, per_page: int) -> tuple[list[list], int]:
|
|
"""Diffed table: one row per path, bot hits vs. human hits."""
|
|
start, end = day_bounds(from_date, to_date)
|
|
bot_rows = (
|
|
db.session.query(BotHit.path, func.count().label("hits"))
|
|
.filter(BotHit.timestamp >= start, BotHit.timestamp < end)
|
|
.group_by(BotHit.path)
|
|
.all()
|
|
)
|
|
human_rows = (
|
|
db.session.query(HumanPathStatsDaily.path, func.sum(HumanPathStatsDaily.count).label("hits"))
|
|
.filter(HumanPathStatsDaily.date >= from_date, HumanPathStatsDaily.date <= to_date)
|
|
.group_by(HumanPathStatsDaily.path)
|
|
.all()
|
|
)
|
|
bot_counts = {r.path: r.hits for r in bot_rows}
|
|
human_counts = {r.path: r.hits for r in human_rows}
|
|
|
|
combined = []
|
|
for path in set(bot_counts) | set(human_counts):
|
|
b, h = bot_counts.get(path, 0), human_counts.get(path, 0)
|
|
total = b + h
|
|
combined.append([path, b, h, round(b / total * 100, 1) if total else 0.0])
|
|
|
|
# ASSUMPTION (flagged): sorted by bot hits desc — Ch09 doesn't specify.
|
|
combined.sort(key=lambda row: row[1], reverse=True)
|
|
|
|
total_count = len(combined)
|
|
offset = (page - 1) * per_page
|
|
return combined[offset : offset + per_page], total_count
|