Files

409 lines
21 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
#!/usr/bin/env python3
"""Build a local SQLite copy of published quality metrics for a DB-GPT trial.
Reads only from the existing local metrics API via scripts/local_api.py. It does
not inspect credentials, change source data, or call any write endpoint.
"""
from __future__ import annotations
import base64
import hashlib
import json
import sqlite3
import sys
from datetime import datetime, timezone
from pathlib import Path
from urllib.parse import urlencode
ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT / "scripts"))
from local_api import Api # noqa: E402
DATA_DIR = ROOT / ".runtime" / "dbgpt-trial" / "data"
EVIDENCE_DIR = DATA_DIR / "evidence"
DB_PATH = DATA_DIR / "quality_trial.sqlite"
MANIFEST_PATH = ROOT / "output" / "dbgpt-trial" / "data-manifest.json"
CONTEXT_PATH = DATA_DIR / "business-context.md"
RELEASE_FIXTURE = ROOT / "output" / "ai-chat-refinement" / "expected-car-scope.json"
OVERALL_FIXTURE = ROOT / "output" / "ai-chat-refinement" / "metric-before.json"
RELEASE_ID = "194b0287-9044-4d80-9138-ecd3c481015c"
YEAR = 2026
MONTHS = tuple(range(1, 10))
PRODUCT_MONTHS = (8, 9)
def encoded_selection(values: list[str]) -> str:
raw = json.dumps(values, ensure_ascii=False, separators=(",", ":")).encode("utf-8")
return "~qv1~" + base64.urlsafe_b64encode(raw).decode("ascii").rstrip("=")
CAR_BOTH_WIRE = encoded_selection(["汽车交通-国内", "汽车交通-海外"])
SCOPES = (
{"key": "overall", "name": "官方总体", "group": None},
{"key": "auto_domestic", "name": "汽车交通-国内", "group": "汽车交通-国内"},
{"key": "auto_overseas", "name": "汽车交通-海外", "group": "汽车交通-海外"},
{"key": "auto_combined", "name": "汽车交通合计(国内+海外)", "group": CAR_BOTH_WIRE},
)
def write_json(path: Path, value: object) -> None:
path.parent.mkdir(parents=True, exist_ok=True)
path.write_text(json.dumps(value, ensure_ascii=False, indent=2, allow_nan=False) + "\n", encoding="utf-8")
def number(value: object) -> float | None:
return None if value is None else float(value)
def number_text(value: object) -> str | None:
if value is None:
return None
# Api.call parses JSON numbers as Python floats; repr is the shortest
# round-trip representation of the official service value, without math.
return repr(value) if isinstance(value, float) else str(value)
def stage_value(summary: dict, stage: str, field: str) -> object:
block = summary.get(stage)
return None if block is None else block.get(field)
def coverage_value(summary: dict, stage: str) -> object:
coverage = summary.get("coverage") or {}
return coverage.get(f"{stage}Rows")
def check_fixtures() -> tuple[dict, dict]:
car = json.loads(RELEASE_FIXTURE.read_text(encoding="utf-8"))
overall = json.loads(OVERALL_FIXTURE.read_text(encoding="utf-8"))
if car.get("release", {}).get("id") != RELEASE_ID or overall.get("release", {}).get("id") != RELEASE_ID:
raise RuntimeError("现有正式结果参考文件绑定的发布版本不一致")
if car.get("context", {}).get("group") != CAR_BOTH_WIRE:
raise RuntimeError("汽车交通参考筛选值与 SelectionValues 编码不一致")
for fixture in (car, overall):
context = fixture.get("context", {})
if context.get("year") != YEAR or context.get("month") != 9:
raise RuntimeError("参考正式结果不是 2026 年 9 月")
return car, overall
def get_snapshot(api: Api, scope: dict, month: int, view: str) -> tuple[dict, dict, str]:
params: dict[str, object] = {
"year": YEAR,
"month": month,
"view": view,
"includeUnassigned": "false",
"releaseId": RELEASE_ID,
"trendGranularity": "month",
}
if scope["group"] is not None:
params["group"] = scope["group"]
query = urlencode(params)
response = api.call("/api/metrics?" + query)
context = response.get("context") or {}
release = response.get("release") or {}
if release.get("id") != RELEASE_ID or release.get("status") != "PUBLISHED":
raise RuntimeError(f"正式发布版本校验失败:{scope['key']} 2026-{month:02d}")
if context.get("year") != YEAR or context.get("month") != month or context.get("view") != view:
raise RuntimeError(f"查询期间或视图校验失败:{scope['key']} 2026-{month:02d} {view}")
if context.get("group") != scope["group"]:
raise RuntimeError(f"业务范围筛选校验失败:{scope['key']} 2026-{month:02d}")
return response, params, query
def schema(connection: sqlite3.Connection) -> None:
connection.executescript("""
PRAGMA foreign_keys=ON;
CREATE TABLE official_scope_month_metrics (
release_id TEXT NOT NULL,
release_name TEXT,
scope_key TEXT NOT NULL,
scope_name TEXT NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL,
period TEXT NOT NULL,
group_filter_wire TEXT,
official_composite_rate REAL,
official_composite_rate_exact TEXT,
composite_rate_without_hq_scrap REAL,
composite_rate_without_hq_scrap_exact TEXT,
black_rate REAL,
black_rate_exact TEXT,
black_qualified_weight REAL,
black_scrap_weight REAL,
black_total_weight REAL,
bright_rate REAL,
bright_rate_exact TEXT,
bright_qualified_weight REAL,
bright_scrap_weight REAL,
bright_total_weight REAL,
bright_headquarters_scrap_weight REAL,
bright_rate_without_hq_scrap REAL,
bright_rate_without_hq_scrap_exact TEXT,
black_rows INTEGER,
bright_rows INTEGER,
included_rows INTEGER,
raw_rows INTEGER,
rule_version_id TEXT,
evidence_file TEXT NOT NULL,
api_query_json TEXT NOT NULL,
coverage_json TEXT,
summary_json TEXT NOT NULL,
context_json TEXT NOT NULL,
PRIMARY KEY (release_id, scope_key, year, month)
);
CREATE INDEX idx_scope_metrics_period ON official_scope_month_metrics(scope_key, period);
CREATE TABLE official_product_month_rows (
release_id TEXT NOT NULL,
scope_key TEXT NOT NULL,
scope_name TEXT NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL,
period TEXT NOT NULL,
group_filter_wire TEXT,
row_no INTEGER NOT NULL,
product_key TEXT,
product_name TEXT,
official_composite_rate REAL,
official_composite_rate_exact TEXT,
composite_rate_without_hq_scrap REAL,
black_rate REAL,
black_qualified_weight REAL,
black_scrap_weight REAL,
black_total_weight REAL,
bright_rate REAL,
bright_qualified_weight REAL,
bright_scrap_weight REAL,
bright_total_weight REAL,
bright_headquarters_scrap_weight REAL,
bright_rate_without_hq_scrap REAL,
black_rows INTEGER,
bright_rows INTEGER,
evidence_file TEXT NOT NULL,
row_json TEXT NOT NULL,
PRIMARY KEY (release_id, scope_key, year, month, row_no)
);
CREATE INDEX idx_product_scope_period ON official_product_month_rows(scope_key, period);
CREATE INDEX idx_product_name ON official_product_month_rows(product_name);
""")
def insert_metric(connection: sqlite3.Connection, scope: dict, month: int, response: dict,
params: dict, evidence_rel: str) -> None:
release = response["release"]
context = response["context"]
summary = response["summary"]
black = summary.get("black") or {}
bright = summary.get("bright") or {}
coverage = summary.get("coverage") or response.get("coverage") or {}
rate = summary.get("compositeRate")
adj = summary.get("compositeRateWithoutHeadquartersScrap")
black_rate = black.get("rate")
bright_rate = bright.get("rate")
bright_adjusted = bright.get("rateWithoutHeadquartersScrap")
values = (
release.get("id"), release.get("name"), scope["key"], scope["name"], YEAR, month,
f"{YEAR}-{month:02d}", scope["group"], number(rate), number_text(rate), number(adj), number_text(adj),
number(black_rate), number_text(black_rate), number(black.get("qualifiedWeight")),
number(black.get("scrapWeight")), number(black.get("totalWeight")), number(bright_rate),
number_text(bright_rate), number(bright.get("qualifiedWeight")), number(bright.get("scrapWeight")),
number(bright.get("totalWeight")), number(bright.get("headquartersScrapWeight")),
number(bright_adjusted), number_text(bright_adjusted), coverage_value(summary, "black"),
coverage_value(summary, "bright"), coverage.get("includedRows"), coverage.get("rawRows"),
context.get("ruleVersionId"), evidence_rel, json.dumps(params, ensure_ascii=False, sort_keys=True),
json.dumps(coverage, ensure_ascii=False, sort_keys=True),
json.dumps(summary, ensure_ascii=False, sort_keys=True),
json.dumps(context, ensure_ascii=False, sort_keys=True),
)
connection.execute(
"INSERT INTO official_scope_month_metrics VALUES (" + ",".join("?" for _ in values) + ")", values
)
def insert_products(connection: sqlite3.Connection, scope: dict, month: int, response: dict,
evidence_rel: str) -> int:
count = 0
for row_no, row in enumerate(response.get("rows", []), start=1):
black = row.get("black") or {}
bright = row.get("bright") or {}
rate = row.get("compositeRate")
values = (
RELEASE_ID, scope["key"], scope["name"], YEAR, month, f"{YEAR}-{month:02d}",
scope["group"], row_no, row.get("key"), row.get("product") or row.get("label"),
number(rate), number_text(rate), number(row.get("compositeRateWithoutHeadquartersScrap")),
number(black.get("rate")), number(black.get("qualifiedWeight")), number(black.get("scrapWeight")),
number(black.get("totalWeight")), number(bright.get("rate")), number(bright.get("qualifiedWeight")),
number(bright.get("scrapWeight")), number(bright.get("totalWeight")),
number(bright.get("headquartersScrapWeight")), number(bright.get("rateWithoutHeadquartersScrap")),
((row.get("coverage") or {}).get("blackRows")), ((row.get("coverage") or {}).get("brightRows")),
evidence_rel, json.dumps(row, ensure_ascii=False, sort_keys=True),
)
connection.execute(
"INSERT INTO official_product_month_rows VALUES (" + ",".join("?" for _ in values) + ")", values
)
count += 1
return count
def fixture_rate(fixture: dict) -> float:
return float(fixture["summary"]["compositeRate"])
def main() -> None:
expected_car, expected_overall = check_fixtures()
DATA_DIR.mkdir(parents=True, exist_ok=True)
EVIDENCE_DIR.mkdir(parents=True, exist_ok=True)
MANIFEST_PATH.parent.mkdir(parents=True, exist_ok=True)
# Build into a fresh database file beside the destination and replace only
# this trial database once all read-only API fetches have succeeded.
temporary_db = DB_PATH.with_suffix(".sqlite.tmp")
temporary_db.unlink(missing_ok=True)
api = Api().login()
counts = {"official_scope_month_metrics": 0, "official_product_month_rows": 0}
evidence_records: list[dict] = []
sep_scope_metrics: dict[str, dict] = {}
conn = sqlite3.connect(temporary_db)
try:
schema(conn)
with conn:
for scope in SCOPES:
for month in MONTHS:
response, params, query = get_snapshot(api, scope, month, "overall")
evidence_name = f"metrics_{scope['key']}_{YEAR}-{month:02d}.json"
evidence_path = EVIDENCE_DIR / evidence_name
envelope = {"request": {"method": "GET", "path": "/api/metrics", "query": params,
"encoded_query": query}, "response": response}
write_json(evidence_path, envelope)
evidence_rel = evidence_path.relative_to(DATA_DIR).as_posix()
insert_metric(conn, scope, month, response, params, evidence_rel)
counts["official_scope_month_metrics"] += 1
evidence_records.append({"path": evidence_rel, "bytes": evidence_path.stat().st_size,
"sha256": hashlib.sha256(evidence_path.read_bytes()).hexdigest(),
"kind": "official_scope_month_metrics", "scope": scope["key"],
"period": f"{YEAR}-{month:02d}"})
if month == 9:
sep_scope_metrics[scope["key"]] = response
for month in PRODUCT_MONTHS:
response, params, query = get_snapshot(api, scope, month, "product")
evidence_name = f"products_{scope['key']}_{YEAR}-{month:02d}.json"
evidence_path = EVIDENCE_DIR / evidence_name
envelope = {"request": {"method": "GET", "path": "/api/metrics", "query": params,
"encoded_query": query}, "response": response}
write_json(evidence_path, envelope)
evidence_rel = evidence_path.relative_to(DATA_DIR).as_posix()
added = insert_products(conn, scope, month, response, evidence_rel)
counts["official_product_month_rows"] += added
evidence_records.append({"path": evidence_rel, "bytes": evidence_path.stat().st_size,
"sha256": hashlib.sha256(evidence_path.read_bytes()).hexdigest(),
"kind": "official_product_month_rows", "scope": scope["key"],
"period": f"{YEAR}-{month:02d}", "rows": added})
conn.close()
temporary_db.replace(DB_PATH)
except Exception:
conn.close()
temporary_db.unlink(missing_ok=True)
raise
with sqlite3.connect(DB_PATH) as check:
counts["official_scope_month_metrics"] = check.execute(
"SELECT COUNT(*) FROM official_scope_month_metrics").fetchone()[0]
counts["official_product_month_rows"] = check.execute(
"SELECT COUNT(*) FROM official_product_month_rows").fetchone()[0]
missing_periods = check.execute("""
SELECT scope_key, GROUP_CONCAT(period, ',') FROM official_scope_month_metrics
GROUP BY scope_key HAVING COUNT(*) != 9
""").fetchall()
if missing_periods:
raise RuntimeError(f"Jan-Sep 月度指标行数不完整:{missing_periods}")
check_car = check.execute("""
SELECT official_composite_rate FROM official_scope_month_metrics
WHERE scope_key='auto_combined' AND period='2026-09'
""").fetchone()[0]
check_overall = check.execute("""
SELECT official_composite_rate FROM official_scope_month_metrics
WHERE scope_key='overall' AND period='2026-09'
""").fetchone()[0]
trend_ranges = check.execute("""
SELECT scope_key, MIN(period), MAX(period), COUNT(*)
FROM official_scope_month_metrics GROUP BY scope_key ORDER BY scope_key
""").fetchall()
if abs(check_car - fixture_rate(expected_car)) > 1e-12:
raise RuntimeError("汽车交通合计 2026-09 综合合格率与参考正式结果不一致")
if abs(check_overall - fixture_rate(expected_overall)) > 1e-12:
raise RuntimeError("总体 2026-09 综合合格率与参考正式结果不一致")
rule_version = expected_car.get("context", {}).get("ruleVersionId")
context = f"""# DB-GPT 质量数据试验副本说明
本目录是供公司本地 AI 按需读取的 SQLite 数据副本与原始 API 证据。数据来自本机正式指标只读接口,绑定已发布版本 `{RELEASE_ID}`({expected_car['release'].get('name', '')});规则版本为 `{rule_version}`。没有改写源数据库、正式数据或计算规则。
## 指标口径与范围
- `official_composite_rate` 对应服务返回的 `summary.compositeRate`,业务名称为“综合合格率”。它是正式发布版本的服务端固定指标;数据库中的数值直接来自指标服务,没有在导入时重新计算。
- `composite_rate_without_hq_scrap` 是服务另外返回的调整口径,不能和综合合格率混称。
- 汽车交通包括“汽车交通-国内”和“汽车交通-海外”两组。合计筛选按正式 `SelectionValues` 合约编码,wire 值为 `{CAR_BOTH_WIRE}`。
- 当前副本覆盖 `{YEAR}-01` 至 `{YEAR}-09` 的总体和三种汽车交通范围月度指标;产品汇总覆盖 `{YEAR}-08`、`{YEAR}-09`。发布 ID、规则版本、筛选值和原始 API 响应均可在表和 `evidence/` 中追溯。
- 空值在 SQLite 中保留为 `NULL`;`NULL` 表示该服务值缺失或不适用,不能当作 0。
- `official_product_month_rows` 的产品分组用于找规格线索。服务说明这些分组行无法保证重新汇总为阶段分母或总体贡献;只能作为下钻线索,不能据此认定对总体率变化的贡献。
## 表说明
- `official_scope_month_metrics`:总体、汽车交通国内、海外、合计在 2026 年 1–9 月的正式月度汇总率、黑片/亮片阶段率与重量、覆盖行数、范围和证据路径。每范围每月一行。
- `official_product_month_rows`:上述范围在 2026 年 8、9 月由 `view=product` 返回的全量正式产品汇总行。每行保留产品名、正式综合率、阶段率、阶段重量、覆盖行数与完整行 JSON。
## 读取边界
公司本地 AI 可按需使用副本明细执行 SQL 探索,并应区分正式汇总指标和产品分组线索。回答中的正式 KPI 以 `official_scope_month_metrics` 的正式服务值为准;不要把产品行加总成总体贡献,也不要把空值补成 0。`evidence/` 保存原始完整响应,可用于复核字段来源。
"""
CONTEXT_PATH.write_text(context, encoding="utf-8")
manifest = {
"artifact": "DB-GPT local quality-data trial copy",
"generated_at_utc": datetime.now(timezone.utc).isoformat(timespec="seconds"),
"source": {"kind": "read-only local API", "base_url": "http://127.0.0.1:5281",
"endpoint": "/api/metrics", "credentials_written": False},
"release": {"id": RELEASE_ID, "status": "PUBLISHED",
"name": expected_car["release"].get("name"), "rule_version_id": rule_version},
"coverage": {"year": YEAR, "monthly_metrics": [f"{YEAR}-{month:02d}" for month in MONTHS],
"product_months": [f"{YEAR}-{month:02d}" for month in PRODUCT_MONTHS],
"scopes": [{"key": s["key"], "name": s["name"], "group_filter_wire": s["group"]}
for s in SCOPES]},
"files": {"sqlite": str(DB_PATH.relative_to(ROOT)),
"business_context": str(CONTEXT_PATH.relative_to(ROOT)),
"evidence_directory": str(EVIDENCE_DIR.relative_to(ROOT))},
"tables": {
"official_scope_month_metrics": {"rows": counts["official_scope_month_metrics"],
"description_zh": "正式指标服务返回的总体及汽车交通各范围月度汇总;rate不在导入时重算。",
"periods_by_scope": [{"scope_key": r[0], "first_period": r[1], "last_period": r[2], "rows": r[3]}
for r in trend_ranges]},
"official_product_month_rows": {"rows": counts["official_product_month_rows"],
"description_zh": "指标API view=product在2026年8、9月返回的全量产品汇总行;只作规格线索,不代表可回加的总体贡献。"},
},
"reference_checks": {
"overall_2026_09_official_composite_rate": {"expected": fixture_rate(expected_overall),
"sqlite": check_overall, "matched": abs(check_overall - fixture_rate(expected_overall)) <= 1e-12},
"auto_combined_2026_09_official_composite_rate": {"expected": fixture_rate(expected_car),
"sqlite": check_car, "matched": abs(check_car - fixture_rate(expected_car)) <= 1e-12,
"display_percent_2dp": round(check_car * 100, 2)},
"sqlite_query_verified": True,
"null_policy": "preserve SQL NULL; do not interpret as zero",
},
"evidence": evidence_records,
}
write_json(MANIFEST_PATH, manifest)
print(json.dumps({"database": str(DB_PATH), "manifest": str(MANIFEST_PATH),
"business_context": str(CONTEXT_PATH), "counts": counts,
"release_id": RELEASE_ID, "rule_version_id": rule_version,
"overall_2026_09_rate": check_overall,
"auto_combined_2026_09_rate": check_car,
"evidence_files": len(evidence_records)}, ensure_ascii=False, indent=2))
if __name__ == "__main__":
main()