| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525 |
- """对象库:工作区、文件对象、原始数据索引、行级血缘。
- 一个“对象”(object)就是一次上传或一次处理产物,带不可变哈希:
- * ``raw`` —— 用户上传的原始 CSV/XLSX,入库时建立 ``raw.sqlite`` 便于按列查询;
- * ``stat`` —— ``customs_process`` 产出的统计结果,含 16 张模板表与 ``stat.sqlite``。
- 对象 id 形如 ``obj_ab12cd34``。前端、Agent 工具、核验层都只引用 id,不引用路径。
- """
- from __future__ import annotations
- import csv
- import hashlib
- import json
- import re
- import shutil
- import sqlite3
- import uuid
- from datetime import datetime, timezone
- from pathlib import Path
- from .config import settings
- from .db import database
- #: 中文表头 → 内部维度名(用于生成可下钻的血缘谓词)
- HEADER_TO_DIM = {
- "一级类目": "c1",
- "二级类目": "c2",
- "三级类目": "c3",
- "描述": "c4",
- "基础类目": "c2",
- "品牌": "brand",
- "前十大型号": "model",
- "型号": "model",
- "应用领域标签": "app",
- "品牌国别": "brand_country",
- "原产国": "origin",
- "贸易国": "trade_country",
- "启运国": "dispatch",
- "经停港/指运港": "port",
- "进/出口": "direction",
- }
- def now() -> str:
- return datetime.now(timezone.utc).astimezone().isoformat(timespec="seconds")
- def new_id(prefix: str) -> str:
- return f"{prefix}_{uuid.uuid4().hex[:8]}"
- def sha256_bytes(payload: bytes) -> str:
- return "sha256:" + hashlib.sha256(payload).hexdigest()
- def normalize_header(name: str) -> str:
- return re.sub(r"\s+", "", str(name or "")).strip()
- class ObjectStore:
- """对象库门面。所有方法都是线程安全的(依赖 db 模块的线程本地连接)。"""
- def __init__(self, data_dir: Path | None = None) -> None:
- self.data_dir = Path(data_dir or settings.data_dir)
- self.objects_dir = self.data_dir / "objects"
- self.objects_dir.mkdir(parents=True, exist_ok=True)
- # ------------------------------------------------------------------ workspace
- def create_workspace(self, name: str, description: str = "") -> dict:
- workspace_id = new_id("ws")
- with database.transaction() as connection:
- connection.execute(
- "INSERT INTO workspaces(id,name,description,created_at) VALUES(?,?,?,?)",
- (workspace_id, name.strip() or "未命名工作区", description, now()),
- )
- return self.get_workspace(workspace_id)
- def get_workspace(self, workspace_id: str) -> dict:
- with database.connect() as connection:
- row = connection.execute("SELECT * FROM workspaces WHERE id=?", (workspace_id,)).fetchone()
- if not row:
- raise KeyError(f"工作区不存在: {workspace_id}")
- return dict(row)
- def list_workspaces(self) -> list[dict]:
- with database.connect() as connection:
- rows = connection.execute(
- "SELECT w.*, (SELECT COUNT(*) FROM objects o WHERE o.workspace_id=w.id) AS object_count "
- "FROM workspaces w ORDER BY w.created_at DESC"
- ).fetchall()
- return [dict(row) for row in rows]
- def ensure_default_workspace(self) -> dict:
- workspaces = self.list_workspaces()
- if workspaces:
- return workspaces[-1]
- return self.create_workspace("默认工作区", "系统初始化时自动创建")
- def delete_workspace(self, workspace_id: str) -> None:
- self.get_workspace(workspace_id)
- for item in self.list_objects(workspace_id):
- self.delete_object(item["id"])
- with database.transaction() as connection:
- connection.execute("DELETE FROM workspaces WHERE id=?", (workspace_id,))
- # ------------------------------------------------------------------ objects
- def register_upload(
- self,
- workspace_id: str,
- filename: str,
- payload: bytes,
- kind: str = "raw",
- producer: str = "upload",
- index: bool = True,
- ) -> dict:
- self.get_workspace(workspace_id)
- object_id = new_id("obj")
- directory = self.objects_dir / object_id
- directory.mkdir(parents=True, exist_ok=True)
- safe_name = re.sub(r"[\\/:*?\"<>|]+", "_", filename).strip() or "upload.bin"
- path = directory / safe_name
- path.write_bytes(payload)
- columns: list[str] = []
- rows = 0
- meta: dict = {}
- if index and path.suffix.lower() in {".csv", ".txt", ".xlsx", ".xlsm"}:
- columns, rows = self._index_raw(object_id, path)
- meta["sqlite_path"] = str(directory / "raw.sqlite") if index and columns else ""
- with database.transaction() as connection:
- connection.execute(
- "INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,"
- "producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
- (
- object_id,
- workspace_id,
- safe_name,
- kind,
- _media_type(path.suffix),
- str(path),
- sha256_bytes(payload),
- len(payload),
- rows,
- json.dumps(columns, ensure_ascii=False),
- producer,
- "[]",
- json.dumps(meta, ensure_ascii=False),
- now(),
- ),
- )
- if columns:
- connection.execute(
- "INSERT INTO tables(object_id,name,sqlite_name,rows,columns_json) VALUES(?,?,?,?,?)",
- (object_id, "raw", "raw", rows, json.dumps(columns, ensure_ascii=False)),
- )
- connection.execute(
- "INSERT INTO row_lineage(object_id,table_name,row_index,row_hash,source_object_id,source_rows) "
- "VALUES(?,?,?,?,?,?)",
- (object_id, "raw", -1, sha256_bytes(payload), object_id, None),
- )
- return self.get_object(object_id)
- def _index_raw(self, object_id: str, path: Path) -> tuple[list[str], int]:
- """把原始文件落成可查询的 SQLite 表 ``raw``,并记录行哈希。"""
- target = path.parent / "raw.sqlite"
- if target.exists():
- target.unlink()
- connection = sqlite3.connect(str(target))
- try:
- connection.execute('PRAGMA journal_mode=OFF')
- headers, rows = _iter_rows(path)
- columns = [normalize_header(header) or f"列{index + 1}" for index, header in enumerate(headers)]
- columns = _dedupe(columns)
- quoted = ",".join(_quote(column) + " TEXT" for column in columns)
- connection.execute(f'CREATE TABLE "raw" ("_row_index" INTEGER PRIMARY KEY,"_row_hash" TEXT,{quoted})')
- placeholders = ",".join("?" for _ in range(len(columns) + 2))
- batch: list[tuple] = []
- count = 0
- for row in rows:
- values = ["" if value is None else str(value) for value in row][: len(columns)]
- values += [""] * (len(columns) - len(values))
- row_hash = sha256_bytes(json.dumps(values, ensure_ascii=False).encode("utf-8"))
- batch.append((count, row_hash, *values))
- count += 1
- if len(batch) >= 5000:
- connection.executemany(f'INSERT INTO "raw" VALUES({placeholders})', batch)
- batch.clear()
- if batch:
- connection.executemany(f'INSERT INTO "raw" VALUES({placeholders})', batch)
- connection.execute('CREATE INDEX "idx_raw_row_hash" ON "raw"("_row_hash")')
- connection.commit()
- finally:
- connection.close()
- return columns, count
- def get_object(self, object_id: str) -> dict:
- with database.connect() as connection:
- row = connection.execute("SELECT * FROM objects WHERE id=?", (object_id,)).fetchone()
- tables = connection.execute(
- "SELECT name,sqlite_name,rows,columns_json FROM tables WHERE object_id=? ORDER BY rowid",
- (object_id,),
- ).fetchall()
- if not row:
- raise KeyError(f"对象不存在: {object_id}")
- item = dict(row)
- item["columns"] = json.loads(item.pop("columns_json") or "[]")
- item["parents"] = json.loads(item.pop("parents_json") or "[]")
- item["meta"] = json.loads(item.pop("meta_json") or "{}")
- item["tables"] = [
- {"name": table["name"], "sqlite_name": table["sqlite_name"], "rows": table["rows"], "columns": json.loads(table["columns_json"] or "[]")}
- for table in tables
- ]
- return item
- def list_objects(self, workspace_id: str | None = None, kind: str | None = None) -> list[dict]:
- clause = []
- params: list = []
- if workspace_id:
- clause.append("workspace_id=?")
- params.append(workspace_id)
- if kind:
- clause.append("kind=?")
- params.append(kind)
- where = f"WHERE {' AND '.join(clause)}" if clause else ""
- with database.connect() as connection:
- rows = connection.execute(f"SELECT id FROM objects {where} ORDER BY created_at DESC", params).fetchall()
- return [self.get_object(row["id"]) for row in rows]
- def delete_object(self, object_id: str) -> None:
- item = self.get_object(object_id)
- with database.transaction() as connection:
- connection.execute("DELETE FROM row_lineage WHERE object_id=?", (object_id,))
- connection.execute("DELETE FROM tables WHERE object_id=?", (object_id,))
- connection.execute("DELETE FROM objects WHERE id=?", (object_id,))
- path = Path(item["path"])
- directory = path.parent if path.parent != self.objects_dir else path
- if directory.exists() and directory.is_dir():
- shutil.rmtree(directory, ignore_errors=True)
- # ------------------------------------------------------------------ stat objects
- def register_processed(
- self,
- workspace_id: str,
- input_object_id: str,
- name: str,
- run_dir: Path,
- result: dict,
- producer: str = "customs_process",
- ) -> dict:
- """把一次处理产物登记为 stat 对象,并建立行级血缘(谓词形式)。"""
- self.get_workspace(workspace_id)
- object_id = new_id("obj")
- run_dir = Path(run_dir)
- stat_db = run_dir / "stat.sqlite"
- lineage = result.get("lineage", {})
- meta = {
- "sqlite_path": str(stat_db) if stat_db.exists() else "",
- "lineage": lineage,
- "xlsx_path": result.get("xlsx_path"),
- "warnings": result.get("warnings", []),
- "stats": {
- key: value
- for key, value in (result.get("stats") or {}).items()
- if key in {"total", "included", "excluded", "alerts", "min_month", "max_month", "seconds", "engine", "cutoff"}
- },
- }
- # 磁盘占用:把整次运行目录(xlsx + 逐表 CSV + 两个 sqlite + 明细)都算进去
- total_bytes = 0
- for path in run_dir.rglob("*"):
- if path.is_file():
- try:
- total_bytes += path.stat().st_size
- except OSError:
- continue
- with database.transaction() as connection:
- connection.execute(
- "INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,"
- "producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
- (
- object_id,
- workspace_id,
- name,
- "stat",
- "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
- str(run_dir),
- lineage.get("output_hash", ""),
- total_bytes,
- sum(int(sheet.get("rows") or 0) for sheet in result.get("sheets", [])),
- json.dumps([sheet["sheet"] for sheet in result.get("sheets", [])], ensure_ascii=False),
- producer,
- json.dumps([input_object_id], ensure_ascii=False),
- json.dumps(meta, ensure_ascii=False),
- now(),
- ),
- )
- for sheet in result.get("sheets", []):
- connection.execute(
- "INSERT OR REPLACE INTO tables(object_id,name,sqlite_name,rows,columns_json) VALUES(?,?,?,?,?)",
- (
- object_id,
- sheet["sheet"],
- sheet.get("table") or sheet["sheet"],
- int(sheet.get("rows") or 0),
- json.dumps(sheet.get("columns", []), ensure_ascii=False),
- ),
- )
- self._index_stat_lineage(object_id, input_object_id, stat_db, result)
- return self.get_object(object_id)
- def _index_stat_lineage(self, object_id: str, input_object_id: str, stat_db: Path, result: dict) -> None:
- """为统计结果每一行写入 ``source_rows`` 谓词,支持从结果行下钻到原始记录。"""
- if not stat_db.exists():
- return
- connection = sqlite3.connect(str(stat_db))
- connection.row_factory = sqlite3.Row
- entries: list[tuple] = []
- try:
- for sheet in result.get("sheets", []):
- table = sheet.get("table")
- if not table:
- continue
- try:
- rows = connection.execute(f'SELECT * FROM "{table}"').fetchall()
- except sqlite3.Error:
- continue
- for row in rows:
- predicate = build_predicate(dict(row))
- entries.append(
- (
- object_id,
- sheet["sheet"],
- int(row["_row_index"]),
- str(row["_row_hash"]),
- input_object_id,
- json.dumps(predicate, ensure_ascii=False) if predicate else None,
- )
- )
- finally:
- connection.close()
- if not entries:
- return
- with database.transaction() as connection:
- connection.executemany(
- "INSERT OR REPLACE INTO row_lineage(object_id,table_name,row_index,row_hash,source_object_id,source_rows) "
- "VALUES(?,?,?,?,?,?)",
- entries,
- )
- def row_lineage(self, object_id: str, table: str, row_index: int) -> dict | None:
- with database.connect() as connection:
- row = connection.execute(
- "SELECT * FROM row_lineage WHERE object_id=? AND table_name=? AND row_index=?",
- (object_id, table, row_index),
- ).fetchone()
- if not row:
- return None
- item = dict(row)
- item["source_rows"] = json.loads(item["source_rows"]) if item["source_rows"] else None
- return item
- def table_sqlite(self, object_id: str, table: str) -> tuple[Path, str]:
- """返回 (sqlite 文件, 该逻辑表在库里的真实表名)。"""
- item = self.get_object(object_id)
- sqlite_path = item["meta"].get("sqlite_path") or str(Path(item["path"]).parent / "raw.sqlite")
- with database.connect() as connection:
- row = connection.execute(
- "SELECT sqlite_name FROM tables WHERE object_id=? AND name=?", (object_id, table)
- ).fetchone()
- if not row:
- raise KeyError(f"对象 {object_id} 中没有表 {table}")
- return Path(sqlite_path), row["sqlite_name"]
- def preview(self, object_id: str, table: str, limit: int = 20, offset: int = 0) -> dict:
- from .readers import read_rows
- result = read_rows(object_id, table=table, limit=limit, offset=offset)
- return {
- "object_id": object_id,
- "table": table,
- "columns": result["columns"],
- "rows": [row["values"] for row in result["rows"]],
- # 行号用于"点 chip 跳到那一行并高亮",前端据此定位
- "row_indexes": [int(row["_trace"]["row_index"]) for row in result["rows"]],
- "row_hashes": [row["_trace"]["row_hash"] for row in result["rows"]],
- "offset": result["offset"],
- "limit": result["limit"],
- "total": result["total"],
- }
- def read_row(self, object_id: str, table: str, row_index: int) -> dict:
- """按行号取一行原文 + 行哈希 + 行级血缘(溯源 chip 的详情面板用)。"""
- sqlite_path, sqlite_name = self.table_sqlite(object_id, table)
- connection = sqlite3.connect(str(sqlite_path))
- connection.row_factory = sqlite3.Row
- try:
- row = connection.execute(
- f'SELECT * FROM "{sqlite_name}" WHERE "_row_index"=?', (int(row_index),)
- ).fetchone()
- finally:
- connection.close()
- if row is None:
- raise KeyError(f"表 {table} 中没有第 {row_index} 行")
- values = {key: row[key] for key in row.keys() if not key.startswith("_")}
- lineage = self.row_lineage(object_id, table, int(row_index))
- return {
- "object_id": object_id,
- "table": table,
- "row_index": int(row_index),
- "row_hash": row["_row_hash"],
- "values": values,
- "lineage": lineage,
- }
- DIMENSION_KEYS = {
- "c1",
- "c2",
- "c3",
- "c4",
- "brand",
- "model",
- "app",
- "brand_country",
- "origin",
- "trade_country",
- "dispatch",
- "port",
- "direction",
- }
- def build_predicate(row: dict) -> dict:
- """从统计结果行反推“这批记录”的过滤条件(用于下钻到原始数据)。"""
- predicate: dict[str, str] = {}
- for header, value in row.items():
- if header in ("_row_index", "_row_hash") or value in (None, ""):
- continue
- dimension = HEADER_TO_DIM.get(str(header))
- if dimension and dimension in DIMENSION_KEYS:
- predicate[dimension] = str(value)
- year = row.get("年度")
- month = row.get("月度")
- if year not in (None, ""):
- predicate["year"] = str(year)
- if month not in (None, ""):
- try:
- predicate["month"] = f"{int(year):04d}-{int(month):02d}"
- except (TypeError, ValueError):
- pass
- return predicate
- def _quote(identifier: str) -> str:
- return '"' + identifier.replace('"', '""') + '"'
- def _dedupe(columns: list[str]) -> list[str]:
- seen: dict[str, int] = {}
- result = []
- for column in columns:
- if column in seen:
- seen[column] += 1
- result.append(f"{column}_{seen[column]}")
- else:
- seen[column] = 0
- result.append(column)
- return result
- def _media_type(suffix: str) -> str:
- return {
- ".csv": "text/csv",
- ".txt": "text/plain",
- ".xlsx": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
- ".xlsm": "application/vnd.ms-excel.sheet.macroEnabled.12",
- ".json": "application/json",
- }.get(suffix.lower(), "application/octet-stream")
- def _iter_rows(path: Path):
- """产出 (表头, 行迭代器)。CSV 与 XLSX 都支持。"""
- suffix = path.suffix.lower()
- if suffix in {".xlsx", ".xlsm"}:
- from openpyxl import load_workbook
- workbook = load_workbook(path, read_only=True, data_only=True)
- sheet = workbook.worksheets[0]
- iterator = sheet.iter_rows(values_only=True)
- headers = ["" if value is None else str(value) for value in next(iterator)]
- return headers, (row for row in iterator)
- if suffix == ".json":
- payload = json.loads(path.read_text(encoding="utf-8-sig"))
- if isinstance(payload, dict):
- payload = payload.get("rows") or payload.get("data") or []
- headers = list(payload[0].keys()) if payload else []
- return headers, (list(item.get(header) for header in headers) for item in payload)
- encoding = _sniff_encoding(path)
- handle = path.open(encoding=encoding, newline="")
- reader = csv.reader(handle)
- headers = next(reader)
- return headers, reader
- def _sniff_encoding(path: Path) -> str:
- raw = path.open("rb").read(4096)
- for encoding in ("utf-8-sig", "utf-8", "gb18030", "utf-16"):
- try:
- raw.decode(encoding)
- return encoding
- except UnicodeDecodeError:
- continue
- return "utf-8"
- store = ObjectStore()
|