"""对象库:工作区、文件对象、原始数据索引、行级血缘。 一个“对象”(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 .catalog import describe_table from .query_indexes import create_query_indexes, raw_numeric_columns, quote from .query_precision import number 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 w.*, (SELECT COUNT(*) FROM objects o WHERE o.workspace_id=w.id) AS object_count " "FROM workspaces w WHERE w.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", ".tsv", ".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) if any(c in {'_row_index','_row_hash'} or c.startswith('_num_') for c in columns): raise ValueError('输入表头使用了内部保留字段名') calendar = raw_numeric_columns(columns) calendar_positions = [columns.index(c) for c in calendar] quoted = ",".join(_quote(column) + " TEXT" for column in columns) shadows = ''.join(','+_quote('_num_'+c)+' REAL' for c in calendar) connection.execute(f'CREATE TABLE "raw" ("_row_index" INTEGER PRIMARY KEY,"_row_hash" TEXT,{quoted}{shadows})') placeholders = ",".join("?" for _ in range(len(columns) + 2 + len(calendar))) 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")) parsed_calendar = [number(values[i]) for i in calendar_positions] batch.append((count, row_hash, *values, *(float(v) if v is not None else None for v in parsed_calendar))) 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")') create_query_indexes(connection, 'raw', columns + ['_num_'+c for c in calendar], raw=True) connection.execute('ANALYZE') 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 "[]"), "catalog": describe_table(table["name"], json.loads(table["columns_json"] or "[]"), kind=item["kind"], lineage=item["meta"].get("lineage", {}))} for table in tables ] for table in item["tables"]: table["catalog"]["coverage_detail"] = {key: item["meta"].get("stats", {}).get(key) for key in ("min_month", "max_month", "cutoff")} 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", "recognition", "exclusion_reasons"} }, } # 磁盘占用:把整次运行目录(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 try: for sheet in result.get("sheets", []): table = sheet.get("table") if not table: continue cursor = connection.execute(f'SELECT * FROM {quote(table)}') while True: rows = cursor.fetchmany(1000) if not rows: break entries = [] 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)) with database.transaction() as target: target.executemany( "INSERT OR REPLACE INTO row_lineage(object_id,table_name,row_index,row_hash,source_object_id,source_rows) VALUES(?,?,?,?,?,?)", entries) finally: connection.close() 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): from .source import iter_source iterator = iter_source(path) _, _, headers, _ = next(iterator) return headers, (values for _, _, _, values in iterator) def _sniff_encoding(path: Path) -> str: from .source import detect_encoding return detect_encoding(path) store = ObjectStore()