objects.py 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525
  1. """对象库:工作区、文件对象、原始数据索引、行级血缘。
  2. 一个“对象”(object)就是一次上传或一次处理产物,带不可变哈希:
  3. * ``raw`` —— 用户上传的原始 CSV/XLSX,入库时建立 ``raw.sqlite`` 便于按列查询;
  4. * ``stat`` —— ``customs_process`` 产出的统计结果,含 16 张模板表与 ``stat.sqlite``。
  5. 对象 id 形如 ``obj_ab12cd34``。前端、Agent 工具、核验层都只引用 id,不引用路径。
  6. """
  7. from __future__ import annotations
  8. import csv
  9. import hashlib
  10. import json
  11. import re
  12. import shutil
  13. import sqlite3
  14. import uuid
  15. from datetime import datetime, timezone
  16. from pathlib import Path
  17. from .config import settings
  18. from .db import database
  19. #: 中文表头 → 内部维度名(用于生成可下钻的血缘谓词)
  20. HEADER_TO_DIM = {
  21. "一级类目": "c1",
  22. "二级类目": "c2",
  23. "三级类目": "c3",
  24. "描述": "c4",
  25. "基础类目": "c2",
  26. "品牌": "brand",
  27. "前十大型号": "model",
  28. "型号": "model",
  29. "应用领域标签": "app",
  30. "品牌国别": "brand_country",
  31. "原产国": "origin",
  32. "贸易国": "trade_country",
  33. "启运国": "dispatch",
  34. "经停港/指运港": "port",
  35. "进/出口": "direction",
  36. }
  37. def now() -> str:
  38. return datetime.now(timezone.utc).astimezone().isoformat(timespec="seconds")
  39. def new_id(prefix: str) -> str:
  40. return f"{prefix}_{uuid.uuid4().hex[:8]}"
  41. def sha256_bytes(payload: bytes) -> str:
  42. return "sha256:" + hashlib.sha256(payload).hexdigest()
  43. def normalize_header(name: str) -> str:
  44. return re.sub(r"\s+", "", str(name or "")).strip()
  45. class ObjectStore:
  46. """对象库门面。所有方法都是线程安全的(依赖 db 模块的线程本地连接)。"""
  47. def __init__(self, data_dir: Path | None = None) -> None:
  48. self.data_dir = Path(data_dir or settings.data_dir)
  49. self.objects_dir = self.data_dir / "objects"
  50. self.objects_dir.mkdir(parents=True, exist_ok=True)
  51. # ------------------------------------------------------------------ workspace
  52. def create_workspace(self, name: str, description: str = "") -> dict:
  53. workspace_id = new_id("ws")
  54. with database.transaction() as connection:
  55. connection.execute(
  56. "INSERT INTO workspaces(id,name,description,created_at) VALUES(?,?,?,?)",
  57. (workspace_id, name.strip() or "未命名工作区", description, now()),
  58. )
  59. return self.get_workspace(workspace_id)
  60. def get_workspace(self, workspace_id: str) -> dict:
  61. with database.connect() as connection:
  62. row = connection.execute("SELECT * FROM workspaces WHERE id=?", (workspace_id,)).fetchone()
  63. if not row:
  64. raise KeyError(f"工作区不存在: {workspace_id}")
  65. return dict(row)
  66. def list_workspaces(self) -> list[dict]:
  67. with database.connect() as connection:
  68. rows = connection.execute(
  69. "SELECT w.*, (SELECT COUNT(*) FROM objects o WHERE o.workspace_id=w.id) AS object_count "
  70. "FROM workspaces w ORDER BY w.created_at DESC"
  71. ).fetchall()
  72. return [dict(row) for row in rows]
  73. def ensure_default_workspace(self) -> dict:
  74. workspaces = self.list_workspaces()
  75. if workspaces:
  76. return workspaces[-1]
  77. return self.create_workspace("默认工作区", "系统初始化时自动创建")
  78. def delete_workspace(self, workspace_id: str) -> None:
  79. self.get_workspace(workspace_id)
  80. for item in self.list_objects(workspace_id):
  81. self.delete_object(item["id"])
  82. with database.transaction() as connection:
  83. connection.execute("DELETE FROM workspaces WHERE id=?", (workspace_id,))
  84. # ------------------------------------------------------------------ objects
  85. def register_upload(
  86. self,
  87. workspace_id: str,
  88. filename: str,
  89. payload: bytes,
  90. kind: str = "raw",
  91. producer: str = "upload",
  92. index: bool = True,
  93. ) -> dict:
  94. self.get_workspace(workspace_id)
  95. object_id = new_id("obj")
  96. directory = self.objects_dir / object_id
  97. directory.mkdir(parents=True, exist_ok=True)
  98. safe_name = re.sub(r"[\\/:*?\"<>|]+", "_", filename).strip() or "upload.bin"
  99. path = directory / safe_name
  100. path.write_bytes(payload)
  101. columns: list[str] = []
  102. rows = 0
  103. meta: dict = {}
  104. if index and path.suffix.lower() in {".csv", ".txt", ".xlsx", ".xlsm"}:
  105. columns, rows = self._index_raw(object_id, path)
  106. meta["sqlite_path"] = str(directory / "raw.sqlite") if index and columns else ""
  107. with database.transaction() as connection:
  108. connection.execute(
  109. "INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,"
  110. "producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
  111. (
  112. object_id,
  113. workspace_id,
  114. safe_name,
  115. kind,
  116. _media_type(path.suffix),
  117. str(path),
  118. sha256_bytes(payload),
  119. len(payload),
  120. rows,
  121. json.dumps(columns, ensure_ascii=False),
  122. producer,
  123. "[]",
  124. json.dumps(meta, ensure_ascii=False),
  125. now(),
  126. ),
  127. )
  128. if columns:
  129. connection.execute(
  130. "INSERT INTO tables(object_id,name,sqlite_name,rows,columns_json) VALUES(?,?,?,?,?)",
  131. (object_id, "raw", "raw", rows, json.dumps(columns, ensure_ascii=False)),
  132. )
  133. connection.execute(
  134. "INSERT INTO row_lineage(object_id,table_name,row_index,row_hash,source_object_id,source_rows) "
  135. "VALUES(?,?,?,?,?,?)",
  136. (object_id, "raw", -1, sha256_bytes(payload), object_id, None),
  137. )
  138. return self.get_object(object_id)
  139. def _index_raw(self, object_id: str, path: Path) -> tuple[list[str], int]:
  140. """把原始文件落成可查询的 SQLite 表 ``raw``,并记录行哈希。"""
  141. target = path.parent / "raw.sqlite"
  142. if target.exists():
  143. target.unlink()
  144. connection = sqlite3.connect(str(target))
  145. try:
  146. connection.execute('PRAGMA journal_mode=OFF')
  147. headers, rows = _iter_rows(path)
  148. columns = [normalize_header(header) or f"列{index + 1}" for index, header in enumerate(headers)]
  149. columns = _dedupe(columns)
  150. quoted = ",".join(_quote(column) + " TEXT" for column in columns)
  151. connection.execute(f'CREATE TABLE "raw" ("_row_index" INTEGER PRIMARY KEY,"_row_hash" TEXT,{quoted})')
  152. placeholders = ",".join("?" for _ in range(len(columns) + 2))
  153. batch: list[tuple] = []
  154. count = 0
  155. for row in rows:
  156. values = ["" if value is None else str(value) for value in row][: len(columns)]
  157. values += [""] * (len(columns) - len(values))
  158. row_hash = sha256_bytes(json.dumps(values, ensure_ascii=False).encode("utf-8"))
  159. batch.append((count, row_hash, *values))
  160. count += 1
  161. if len(batch) >= 5000:
  162. connection.executemany(f'INSERT INTO "raw" VALUES({placeholders})', batch)
  163. batch.clear()
  164. if batch:
  165. connection.executemany(f'INSERT INTO "raw" VALUES({placeholders})', batch)
  166. connection.execute('CREATE INDEX "idx_raw_row_hash" ON "raw"("_row_hash")')
  167. connection.commit()
  168. finally:
  169. connection.close()
  170. return columns, count
  171. def get_object(self, object_id: str) -> dict:
  172. with database.connect() as connection:
  173. row = connection.execute("SELECT * FROM objects WHERE id=?", (object_id,)).fetchone()
  174. tables = connection.execute(
  175. "SELECT name,sqlite_name,rows,columns_json FROM tables WHERE object_id=? ORDER BY rowid",
  176. (object_id,),
  177. ).fetchall()
  178. if not row:
  179. raise KeyError(f"对象不存在: {object_id}")
  180. item = dict(row)
  181. item["columns"] = json.loads(item.pop("columns_json") or "[]")
  182. item["parents"] = json.loads(item.pop("parents_json") or "[]")
  183. item["meta"] = json.loads(item.pop("meta_json") or "{}")
  184. item["tables"] = [
  185. {"name": table["name"], "sqlite_name": table["sqlite_name"], "rows": table["rows"], "columns": json.loads(table["columns_json"] or "[]")}
  186. for table in tables
  187. ]
  188. return item
  189. def list_objects(self, workspace_id: str | None = None, kind: str | None = None) -> list[dict]:
  190. clause = []
  191. params: list = []
  192. if workspace_id:
  193. clause.append("workspace_id=?")
  194. params.append(workspace_id)
  195. if kind:
  196. clause.append("kind=?")
  197. params.append(kind)
  198. where = f"WHERE {' AND '.join(clause)}" if clause else ""
  199. with database.connect() as connection:
  200. rows = connection.execute(f"SELECT id FROM objects {where} ORDER BY created_at DESC", params).fetchall()
  201. return [self.get_object(row["id"]) for row in rows]
  202. def delete_object(self, object_id: str) -> None:
  203. item = self.get_object(object_id)
  204. with database.transaction() as connection:
  205. connection.execute("DELETE FROM row_lineage WHERE object_id=?", (object_id,))
  206. connection.execute("DELETE FROM tables WHERE object_id=?", (object_id,))
  207. connection.execute("DELETE FROM objects WHERE id=?", (object_id,))
  208. path = Path(item["path"])
  209. directory = path.parent if path.parent != self.objects_dir else path
  210. if directory.exists() and directory.is_dir():
  211. shutil.rmtree(directory, ignore_errors=True)
  212. # ------------------------------------------------------------------ stat objects
  213. def register_processed(
  214. self,
  215. workspace_id: str,
  216. input_object_id: str,
  217. name: str,
  218. run_dir: Path,
  219. result: dict,
  220. producer: str = "customs_process",
  221. ) -> dict:
  222. """把一次处理产物登记为 stat 对象,并建立行级血缘(谓词形式)。"""
  223. self.get_workspace(workspace_id)
  224. object_id = new_id("obj")
  225. run_dir = Path(run_dir)
  226. stat_db = run_dir / "stat.sqlite"
  227. lineage = result.get("lineage", {})
  228. meta = {
  229. "sqlite_path": str(stat_db) if stat_db.exists() else "",
  230. "lineage": lineage,
  231. "xlsx_path": result.get("xlsx_path"),
  232. "warnings": result.get("warnings", []),
  233. "stats": {
  234. key: value
  235. for key, value in (result.get("stats") or {}).items()
  236. if key in {"total", "included", "excluded", "alerts", "min_month", "max_month", "seconds", "engine", "cutoff"}
  237. },
  238. }
  239. # 磁盘占用:把整次运行目录(xlsx + 逐表 CSV + 两个 sqlite + 明细)都算进去
  240. total_bytes = 0
  241. for path in run_dir.rglob("*"):
  242. if path.is_file():
  243. try:
  244. total_bytes += path.stat().st_size
  245. except OSError:
  246. continue
  247. with database.transaction() as connection:
  248. connection.execute(
  249. "INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,"
  250. "producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
  251. (
  252. object_id,
  253. workspace_id,
  254. name,
  255. "stat",
  256. "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  257. str(run_dir),
  258. lineage.get("output_hash", ""),
  259. total_bytes,
  260. sum(int(sheet.get("rows") or 0) for sheet in result.get("sheets", [])),
  261. json.dumps([sheet["sheet"] for sheet in result.get("sheets", [])], ensure_ascii=False),
  262. producer,
  263. json.dumps([input_object_id], ensure_ascii=False),
  264. json.dumps(meta, ensure_ascii=False),
  265. now(),
  266. ),
  267. )
  268. for sheet in result.get("sheets", []):
  269. connection.execute(
  270. "INSERT OR REPLACE INTO tables(object_id,name,sqlite_name,rows,columns_json) VALUES(?,?,?,?,?)",
  271. (
  272. object_id,
  273. sheet["sheet"],
  274. sheet.get("table") or sheet["sheet"],
  275. int(sheet.get("rows") or 0),
  276. json.dumps(sheet.get("columns", []), ensure_ascii=False),
  277. ),
  278. )
  279. self._index_stat_lineage(object_id, input_object_id, stat_db, result)
  280. return self.get_object(object_id)
  281. def _index_stat_lineage(self, object_id: str, input_object_id: str, stat_db: Path, result: dict) -> None:
  282. """为统计结果每一行写入 ``source_rows`` 谓词,支持从结果行下钻到原始记录。"""
  283. if not stat_db.exists():
  284. return
  285. connection = sqlite3.connect(str(stat_db))
  286. connection.row_factory = sqlite3.Row
  287. entries: list[tuple] = []
  288. try:
  289. for sheet in result.get("sheets", []):
  290. table = sheet.get("table")
  291. if not table:
  292. continue
  293. try:
  294. rows = connection.execute(f'SELECT * FROM "{table}"').fetchall()
  295. except sqlite3.Error:
  296. continue
  297. for row in rows:
  298. predicate = build_predicate(dict(row))
  299. entries.append(
  300. (
  301. object_id,
  302. sheet["sheet"],
  303. int(row["_row_index"]),
  304. str(row["_row_hash"]),
  305. input_object_id,
  306. json.dumps(predicate, ensure_ascii=False) if predicate else None,
  307. )
  308. )
  309. finally:
  310. connection.close()
  311. if not entries:
  312. return
  313. with database.transaction() as connection:
  314. connection.executemany(
  315. "INSERT OR REPLACE INTO row_lineage(object_id,table_name,row_index,row_hash,source_object_id,source_rows) "
  316. "VALUES(?,?,?,?,?,?)",
  317. entries,
  318. )
  319. def row_lineage(self, object_id: str, table: str, row_index: int) -> dict | None:
  320. with database.connect() as connection:
  321. row = connection.execute(
  322. "SELECT * FROM row_lineage WHERE object_id=? AND table_name=? AND row_index=?",
  323. (object_id, table, row_index),
  324. ).fetchone()
  325. if not row:
  326. return None
  327. item = dict(row)
  328. item["source_rows"] = json.loads(item["source_rows"]) if item["source_rows"] else None
  329. return item
  330. def table_sqlite(self, object_id: str, table: str) -> tuple[Path, str]:
  331. """返回 (sqlite 文件, 该逻辑表在库里的真实表名)。"""
  332. item = self.get_object(object_id)
  333. sqlite_path = item["meta"].get("sqlite_path") or str(Path(item["path"]).parent / "raw.sqlite")
  334. with database.connect() as connection:
  335. row = connection.execute(
  336. "SELECT sqlite_name FROM tables WHERE object_id=? AND name=?", (object_id, table)
  337. ).fetchone()
  338. if not row:
  339. raise KeyError(f"对象 {object_id} 中没有表 {table}")
  340. return Path(sqlite_path), row["sqlite_name"]
  341. def preview(self, object_id: str, table: str, limit: int = 20, offset: int = 0) -> dict:
  342. from .readers import read_rows
  343. result = read_rows(object_id, table=table, limit=limit, offset=offset)
  344. return {
  345. "object_id": object_id,
  346. "table": table,
  347. "columns": result["columns"],
  348. "rows": [row["values"] for row in result["rows"]],
  349. # 行号用于"点 chip 跳到那一行并高亮",前端据此定位
  350. "row_indexes": [int(row["_trace"]["row_index"]) for row in result["rows"]],
  351. "row_hashes": [row["_trace"]["row_hash"] for row in result["rows"]],
  352. "offset": result["offset"],
  353. "limit": result["limit"],
  354. "total": result["total"],
  355. }
  356. def read_row(self, object_id: str, table: str, row_index: int) -> dict:
  357. """按行号取一行原文 + 行哈希 + 行级血缘(溯源 chip 的详情面板用)。"""
  358. sqlite_path, sqlite_name = self.table_sqlite(object_id, table)
  359. connection = sqlite3.connect(str(sqlite_path))
  360. connection.row_factory = sqlite3.Row
  361. try:
  362. row = connection.execute(
  363. f'SELECT * FROM "{sqlite_name}" WHERE "_row_index"=?', (int(row_index),)
  364. ).fetchone()
  365. finally:
  366. connection.close()
  367. if row is None:
  368. raise KeyError(f"表 {table} 中没有第 {row_index} 行")
  369. values = {key: row[key] for key in row.keys() if not key.startswith("_")}
  370. lineage = self.row_lineage(object_id, table, int(row_index))
  371. return {
  372. "object_id": object_id,
  373. "table": table,
  374. "row_index": int(row_index),
  375. "row_hash": row["_row_hash"],
  376. "values": values,
  377. "lineage": lineage,
  378. }
  379. DIMENSION_KEYS = {
  380. "c1",
  381. "c2",
  382. "c3",
  383. "c4",
  384. "brand",
  385. "model",
  386. "app",
  387. "brand_country",
  388. "origin",
  389. "trade_country",
  390. "dispatch",
  391. "port",
  392. "direction",
  393. }
  394. def build_predicate(row: dict) -> dict:
  395. """从统计结果行反推“这批记录”的过滤条件(用于下钻到原始数据)。"""
  396. predicate: dict[str, str] = {}
  397. for header, value in row.items():
  398. if header in ("_row_index", "_row_hash") or value in (None, ""):
  399. continue
  400. dimension = HEADER_TO_DIM.get(str(header))
  401. if dimension and dimension in DIMENSION_KEYS:
  402. predicate[dimension] = str(value)
  403. year = row.get("年度")
  404. month = row.get("月度")
  405. if year not in (None, ""):
  406. predicate["year"] = str(year)
  407. if month not in (None, ""):
  408. try:
  409. predicate["month"] = f"{int(year):04d}-{int(month):02d}"
  410. except (TypeError, ValueError):
  411. pass
  412. return predicate
  413. def _quote(identifier: str) -> str:
  414. return '"' + identifier.replace('"', '""') + '"'
  415. def _dedupe(columns: list[str]) -> list[str]:
  416. seen: dict[str, int] = {}
  417. result = []
  418. for column in columns:
  419. if column in seen:
  420. seen[column] += 1
  421. result.append(f"{column}_{seen[column]}")
  422. else:
  423. seen[column] = 0
  424. result.append(column)
  425. return result
  426. def _media_type(suffix: str) -> str:
  427. return {
  428. ".csv": "text/csv",
  429. ".txt": "text/plain",
  430. ".xlsx": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  431. ".xlsm": "application/vnd.ms-excel.sheet.macroEnabled.12",
  432. ".json": "application/json",
  433. }.get(suffix.lower(), "application/octet-stream")
  434. def _iter_rows(path: Path):
  435. """产出 (表头, 行迭代器)。CSV 与 XLSX 都支持。"""
  436. suffix = path.suffix.lower()
  437. if suffix in {".xlsx", ".xlsm"}:
  438. from openpyxl import load_workbook
  439. workbook = load_workbook(path, read_only=True, data_only=True)
  440. sheet = workbook.worksheets[0]
  441. iterator = sheet.iter_rows(values_only=True)
  442. headers = ["" if value is None else str(value) for value in next(iterator)]
  443. return headers, (row for row in iterator)
  444. if suffix == ".json":
  445. payload = json.loads(path.read_text(encoding="utf-8-sig"))
  446. if isinstance(payload, dict):
  447. payload = payload.get("rows") or payload.get("data") or []
  448. headers = list(payload[0].keys()) if payload else []
  449. return headers, (list(item.get(header) for header in headers) for item in payload)
  450. encoding = _sniff_encoding(path)
  451. handle = path.open(encoding=encoding, newline="")
  452. reader = csv.reader(handle)
  453. headers = next(reader)
  454. return headers, reader
  455. def _sniff_encoding(path: Path) -> str:
  456. raw = path.open("rb").read(4096)
  457. for encoding in ("utf-8-sig", "utf-8", "gb18030", "utf-16"):
  458. try:
  459. raw.decode(encoding)
  460. return encoding
  461. except UnicodeDecodeError:
  462. continue
  463. return "utf-8"
  464. store = ObjectStore()