Real2Motrix/backend/app/database.py
2026-06-04 23:07:55 +08:00

170 lines
6.8 KiB
Python

import json
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Dict, Iterable, Iterator, List, Optional
def utc_now() -> str:
return datetime.now(timezone.utc).isoformat()
class Database:
def __init__(self, path: Path):
self.path = Path(path)
self.path.parent.mkdir(parents=True, exist_ok=True)
self.init_schema()
@contextmanager
def connect(self) -> Iterator[sqlite3.Connection]:
conn = sqlite3.connect(self.path)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
def init_schema(self) -> None:
with sqlite3.connect(self.path) as conn:
conn.execute("PRAGMA foreign_keys = ON")
conn.executescript(
"""
CREATE TABLE IF NOT EXISTS projects (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
source_type TEXT NOT NULL DEFAULT 'ios_capture',
status TEXT NOT NULL DEFAULT 'created',
root_dir TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS capture_sessions (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
capture_type TEXT NOT NULL,
capture_mode TEXT NOT NULL,
device_model TEXT NOT NULL,
has_lidar INTEGER NOT NULL DEFAULT 0,
has_depth INTEGER NOT NULL DEFAULT 0,
has_arkit_pose INTEGER NOT NULL DEFAULT 0,
zip_path TEXT NOT NULL,
metadata_json TEXT NOT NULL DEFAULT '{}',
quality_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS live_scan_sessions (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
status TEXT NOT NULL DEFAULT 'running',
capture_type TEXT NOT NULL,
capture_mode TEXT NOT NULL,
target_type TEXT NOT NULL,
device_model TEXT NOT NULL,
has_lidar INTEGER NOT NULL DEFAULT 0,
root_dir TEXT NOT NULL,
frame_count INTEGER NOT NULL DEFAULT 0,
depth_frame_count INTEGER NOT NULL DEFAULT 0,
mesh_chunk_count INTEGER NOT NULL DEFAULT 0,
coverage_json TEXT NOT NULL DEFAULT '{}',
quality_json TEXT NOT NULL DEFAULT '{}',
last_pose_json TEXT NOT NULL DEFAULT '{}',
preview_path TEXT,
archive_path TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
finished_at TEXT
);
CREATE TABLE IF NOT EXISTS live_scan_frames (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL REFERENCES live_scan_sessions(id) ON DELETE CASCADE,
frame_index INTEGER NOT NULL,
timestamp REAL NOT NULL,
frame_path TEXT NOT NULL,
depth_path TEXT,
pose_json TEXT NOT NULL DEFAULT '{}',
quality_json TEXT NOT NULL DEFAULT '{}',
intrinsics_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL,
UNIQUE(session_id, frame_index)
);
CREATE TABLE IF NOT EXISTS uploaded_files (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
filename TEXT NOT NULL,
path TEXT NOT NULL,
file_type TEXT NOT NULL,
size INTEGER NOT NULL,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS jobs (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
capture_session_id TEXT REFERENCES capture_sessions(id) ON DELETE SET NULL,
status TEXT NOT NULL,
progress REAL NOT NULL DEFAULT 0,
current_step TEXT NOT NULL DEFAULT '',
error_message TEXT,
source_type TEXT NOT NULL DEFAULT 'ios_capture',
input_path TEXT,
params_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL,
started_at TEXT,
finished_at TEXT
);
CREATE TABLE IF NOT EXISTS job_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
level TEXT NOT NULL,
message TEXT NOT NULL,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS assets (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
asset_type TEXT NOT NULL,
path TEXT NOT NULL,
metadata_json TEXT NOT NULL DEFAULT '{}',
created_at TEXT NOT NULL
);
"""
)
conn.commit()
def execute(self, sql: str, params: Iterable[Any] = ()) -> None:
with self.connect() as conn:
conn.execute(sql, tuple(params))
def fetch_one(self, sql: str, params: Iterable[Any] = ()) -> Optional[Dict[str, Any]]:
with self.connect() as conn:
row = conn.execute(sql, tuple(params)).fetchone()
return dict(row) if row else None
def fetch_all(self, sql: str, params: Iterable[Any] = ()) -> List[Dict[str, Any]]:
with self.connect() as conn:
rows = conn.execute(sql, tuple(params)).fetchall()
return [dict(row) for row in rows]
def json_loads(value: Optional[str]) -> Any:
if not value:
return {}
return json.loads(value)
def json_dumps(value: Any) -> str:
return json.dumps(value, ensure_ascii=False, indent=2, sort_keys=True)