170 lines
6.8 KiB
Python
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)
|