""" Automotive Intelligence Dashboard — Database Module (raw sqlite3) """ from __future__ import annotations import hashlib import json import logging import sqlite3 import threading from datetime import datetime, timedelta from typing import Any, Dict, List, Optional from config import config logger = logging.getLogger(__name__) class NewsDatabase: """Thread-safe SQLite store for automotive news.""" def __init__(self, db_path: Optional[str] = None) -> None: self._path = str(db_path or config.database_path) self._local = threading.local() self._ensure_schema() def _conn(self) -> sqlite3.Connection: """Get a thread-local connection.""" if not hasattr(self._local, "conn") or self._local.conn is None: conn = sqlite3.connect(self._path, check_same_thread=False) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA busy_timeout=5000") self._local.conn = conn return self._local.conn # ------------------------------------------------------------------ # Schema # ------------------------------------------------------------------ def _ensure_schema(self) -> None: c = self._conn() c.execute("""CREATE TABLE IF NOT EXISTS news ( id INTEGER PRIMARY KEY AUTOINCREMENT, title_en TEXT, title_zh TEXT, summary_en TEXT, summary_zh TEXT, content TEXT, url TEXT, source TEXT, language TEXT, category TEXT, published_at TEXT, fetched_at TEXT, companies TEXT, roles TEXT, content_hash TEXT UNIQUE )""") c.execute("CREATE INDEX IF NOT EXISTS idx_news_source ON news(source)") c.execute("CREATE INDEX IF NOT EXISTS idx_news_pub ON news(published_at)") c.execute("CREATE INDEX IF NOT EXISTS idx_news_hash ON news(content_hash)") c.execute("""CREATE TABLE IF NOT EXISTS crawl_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT, articles_fetched INTEGER, articles_new INTEGER, status TEXT, error TEXT, started_at TEXT, completed_at TEXT )""") c.commit() # ------------------------------------------------------------------ # Helpers # ------------------------------------------------------------------ @staticmethod def _hash(content: str) -> str: return hashlib.sha256(content.encode("utf-8", errors="replace")).hexdigest() # ------------------------------------------------------------------ # Write # ------------------------------------------------------------------ def upsert_article(self, article: Dict[str, Any]) -> bool: title = (article.get("title_en") or "").strip() if not title: return False content = (article.get("content") or article.get("summary_en") or "").strip() h = self._hash(content or title) c = self._conn() cur = c.execute("SELECT id FROM news WHERE content_hash = ?", (h,)) if cur.fetchone(): return False c.execute( """INSERT INTO news (title_en, title_zh, summary_en, summary_zh, content, url, source, language, category, published_at, fetched_at, companies, roles, content_hash) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (title, article.get("title_zh") or "", (article.get("summary_en") or "")[:500], (article.get("summary_zh") or "")[:300], content[:2000], article.get("url", ""), article.get("source", "unknown"), article.get("language", "en"), article.get("category", "general"), article.get("published_at", datetime.now().isoformat()), datetime.now().isoformat(), json.dumps(article.get("companies", []), ensure_ascii=False), json.dumps(article.get("roles", []), ensure_ascii=False), h)) c.commit() return True # ------------------------------------------------------------------ # Read # ------------------------------------------------------------------ def get_news(self, start_date=None, limit=100, company=None, role=None, category=None): c = self._conn() rows = [dict(r) for r in c.execute( "SELECT * FROM news ORDER BY published_at DESC LIMIT ?", (limit,))] if start_date: rows = [r for r in rows if (r.get("published_at") or "") >= start_date] if role and role != "全部": rows = [r for r in rows if role in (r.get("roles") or "[]")] if company and company != "全部": rows = [r for r in rows if company in (r.get("companies") or "[]")] if category and category != "全部": rows = [r for r in rows if (r.get("category") or "").lower() == category.lower()] return rows def get_recent_count(self, hours=24): cutoff = (datetime.now() - timedelta(hours=hours)).isoformat() c = self._conn() results = {} for r in c.execute("SELECT source, COUNT(*) as cnt FROM news WHERE fetched_at >= ? GROUP BY source", (cutoff,)): results[r["source"]] = r["cnt"] return results def total_count(self) -> int: c = self._conn() return c.execute("SELECT COUNT(*) FROM news").fetchone()[0] def log_crawl(self, source, fetched, new, status="ok", error=""): c = self._conn() now = datetime.now().isoformat() c.execute( "INSERT INTO crawl_log (source, articles_fetched, articles_new, status, error, started_at, completed_at) VALUES (?,?,?,?,?,?,?)", (source, fetched, new, status, error, now, now)) c.commit()