Spaces:
Sleeping
Sleeping
Download code/local_store.py from acra-thesis/acra-backend: direct link, hf CLI and curl.
- Browser
- Download file 11.1 kB
-
https://huggingface.co/spaces/acra-thesis/acra-backend/resolve/main/code/local_store.py
- Command line
-
hf download hf://spaces/acra-thesis/acra-backend/code/local_store.py
-
curl -L -o local_store.py https://huggingface.co/spaces/acra-thesis/acra-backend/resolve/main/code/local_store.py
11.1 kB
| """Local SQLite + filesystem persistence for ACRA when Supabase is not configured.""" | |
| from __future__ import annotations | |
| import json | |
| import shutil | |
| import sqlite3 | |
| from contextlib import contextmanager | |
| from datetime import datetime, timedelta, timezone | |
| from pathlib import Path | |
| from typing import Any, Dict, List, Optional | |
| def init_paths(data_dir: Path) -> tuple[Path, Path]: | |
| static_dir = data_dir / "static" | |
| static_dir.mkdir(parents=True, exist_ok=True) | |
| (static_dir / "jobs").mkdir(exist_ok=True) | |
| (static_dir / "test-runs").mkdir(exist_ok=True) | |
| db_path = data_dir / "jobs.db" | |
| init_db(db_path) | |
| return static_dir, db_path | |
| def init_db(db_path: Path) -> None: | |
| with _connect(db_path) as con: | |
| con.executescript( | |
| """ | |
| CREATE TABLE IF NOT EXISTS jobs ( | |
| job_id TEXT PRIMARY KEY, | |
| user_id TEXT NOT NULL, | |
| cvd_type TEXT NOT NULL, | |
| severity REAL NOT NULL, | |
| conf_threshold REAL NOT NULL, | |
| use_segmentation INTEGER NOT NULL DEFAULT 1, | |
| seg_soft REAL NOT NULL DEFAULT 3.5, | |
| n_clusters INTEGER, | |
| seg_clusters_per_roi INTEGER DEFAULT 3, | |
| created_at TEXT NOT NULL, | |
| expires_at TEXT NOT NULL, | |
| original_url TEXT NOT NULL, | |
| corrected_url TEXT NOT NULL, | |
| metrics TEXT NOT NULL, | |
| boxes TEXT NOT NULL | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_jobs_user ON jobs(user_id); | |
| CREATE INDEX IF NOT EXISTS idx_jobs_expires ON jobs(expires_at); | |
| CREATE TABLE IF NOT EXISTS test_runs ( | |
| run_id TEXT PRIMARY KEY, | |
| user_id TEXT NOT NULL, | |
| filename TEXT, | |
| cvd_type TEXT NOT NULL, | |
| severity REAL NOT NULL, | |
| conf_threshold REAL NOT NULL, | |
| use_segmentation INTEGER NOT NULL DEFAULT 1, | |
| seg_soft REAL NOT NULL DEFAULT 3.5, | |
| n_clusters INTEGER, | |
| seg_clusters_per_roi INTEGER DEFAULT 3, | |
| created_at TEXT NOT NULL, | |
| original_url TEXT NOT NULL, | |
| corrected_url TEXT NOT NULL, | |
| metrics TEXT NOT NULL, | |
| boxes TEXT NOT NULL, | |
| de_improvement REAL, | |
| conflict_resolution REAL, | |
| naturalness REAL, | |
| conflicts_found INTEGER, | |
| boxes_detected INTEGER, | |
| inference_ms REAL, | |
| pass_de INTEGER, | |
| pass_resolution INTEGER, | |
| pass_naturalness INTEGER | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_runs_user ON test_runs(user_id); | |
| """ | |
| ) | |
| def _connect(db_path: Path): | |
| con = sqlite3.connect(str(db_path), check_same_thread=False) | |
| con.row_factory = sqlite3.Row | |
| try: | |
| yield con | |
| con.commit() | |
| except Exception: | |
| con.rollback() | |
| raise | |
| finally: | |
| con.close() | |
| def public_url(base_url: str, storage_path: str) -> str: | |
| return f"{base_url.rstrip('/')}/static/{storage_path.lstrip('/')}" | |
| def save_image(static_dir: Path, storage_path: str, data: bytes) -> None: | |
| dest = static_dir / storage_path | |
| dest.parent.mkdir(parents=True, exist_ok=True) | |
| dest.write_bytes(data) | |
| def delete_images(static_dir: Path, prefix: str) -> None: | |
| folder = static_dir / prefix | |
| try: | |
| shutil.rmtree(folder) | |
| except FileNotFoundError: | |
| pass # A prior cleanup or concurrent explicit deletion already removed it. | |
| def _encode_job(row: dict) -> dict: | |
| out = dict(row) | |
| out["use_segmentation"] = 1 if row.get("use_segmentation", True) else 0 | |
| out["metrics"] = json.dumps(row.get("metrics") or {}) | |
| out["boxes"] = json.dumps(row.get("boxes") or []) | |
| return out | |
| def _decode_job(row: sqlite3.Row) -> dict: | |
| d = dict(row) | |
| d["use_segmentation"] = bool(d.get("use_segmentation", 1)) | |
| d["metrics"] = json.loads(d.get("metrics") or "{}") | |
| d["boxes"] = json.loads(d.get("boxes") or "[]") | |
| return d | |
| def _encode_run(row: dict) -> dict: | |
| out = _encode_job(row) | |
| for key in ("pass_de", "pass_resolution", "pass_naturalness"): | |
| if key in out: | |
| out[key] = 1 if out[key] else 0 | |
| return out | |
| def _decode_run(row: sqlite3.Row) -> dict: | |
| d = _decode_job(row) | |
| for key in ("pass_de", "pass_resolution", "pass_naturalness"): | |
| if key in d: | |
| d[key] = bool(d[key]) | |
| return d | |
| def insert_job(db_path: Path, row: dict) -> None: | |
| encoded = _encode_job(row) | |
| with _connect(db_path) as con: | |
| con.execute( | |
| """ | |
| INSERT INTO jobs ( | |
| job_id, user_id, cvd_type, severity, conf_threshold, | |
| use_segmentation, seg_soft, n_clusters, seg_clusters_per_roi, | |
| created_at, expires_at, original_url, corrected_url, metrics, boxes | |
| ) VALUES ( | |
| :job_id, :user_id, :cvd_type, :severity, :conf_threshold, | |
| :use_segmentation, :seg_soft, :n_clusters, :seg_clusters_per_roi, | |
| :created_at, :expires_at, :original_url, :corrected_url, :metrics, :boxes | |
| ) | |
| """, | |
| encoded, | |
| ) | |
| def insert_test_run(db_path: Path, row: dict) -> None: | |
| encoded = _encode_run(row) | |
| with _connect(db_path) as con: | |
| con.execute( | |
| """ | |
| INSERT INTO test_runs ( | |
| run_id, user_id, filename, cvd_type, severity, conf_threshold, | |
| use_segmentation, seg_soft, n_clusters, seg_clusters_per_roi, | |
| created_at, original_url, corrected_url, metrics, boxes, | |
| de_improvement, conflict_resolution, naturalness, | |
| conflicts_found, boxes_detected, inference_ms, | |
| pass_de, pass_resolution, pass_naturalness | |
| ) VALUES ( | |
| :run_id, :user_id, :filename, :cvd_type, :severity, :conf_threshold, | |
| :use_segmentation, :seg_soft, :n_clusters, :seg_clusters_per_roi, | |
| :created_at, :original_url, :corrected_url, :metrics, :boxes, | |
| :de_improvement, :conflict_resolution, :naturalness, | |
| :conflicts_found, :boxes_detected, :inference_ms, | |
| :pass_de, :pass_resolution, :pass_naturalness | |
| ) | |
| """, | |
| encoded, | |
| ) | |
| def list_jobs(db_path: Path, user_id: str) -> List[dict]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| "SELECT * FROM jobs WHERE user_id = ? ORDER BY created_at DESC", | |
| (user_id,), | |
| ).fetchall() | |
| return [_decode_job(r) for r in rows] | |
| def get_job(db_path: Path, job_id: str, user_id: str) -> Optional[dict]: | |
| with _connect(db_path) as con: | |
| row = con.execute( | |
| "SELECT * FROM jobs WHERE job_id = ? AND user_id = ?", | |
| (job_id, user_id), | |
| ).fetchone() | |
| return _decode_job(row) if row else None | |
| def delete_job(db_path: Path, job_id: str) -> None: | |
| with _connect(db_path) as con: | |
| con.execute("DELETE FROM jobs WHERE job_id = ?", (job_id,)) | |
| def list_all_test_runs(db_path: Path, user_id: str) -> List[dict]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| "SELECT * FROM test_runs WHERE user_id = ? ORDER BY created_at DESC", | |
| (user_id,), | |
| ).fetchall() | |
| return [_decode_run(r) for r in rows] | |
| def list_test_runs(db_path: Path, user_id: str, limit: int, offset: int) -> List[dict]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| """ | |
| SELECT * FROM test_runs WHERE user_id = ? | |
| ORDER BY created_at DESC LIMIT ? OFFSET ? | |
| """, | |
| (user_id, limit, offset), | |
| ).fetchall() | |
| return [_decode_run(r) for r in rows] | |
| def get_test_run(db_path: Path, run_id: str, user_id: str) -> Optional[dict]: | |
| with _connect(db_path) as con: | |
| row = con.execute( | |
| "SELECT * FROM test_runs WHERE run_id = ? AND user_id = ?", | |
| (run_id, user_id), | |
| ).fetchone() | |
| return _decode_run(row) if row else None | |
| def delete_test_run(db_path: Path, run_id: str) -> None: | |
| with _connect(db_path) as con: | |
| con.execute("DELETE FROM test_runs WHERE run_id = ?", (run_id,)) | |
| def clear_test_runs(db_path: Path, user_id: str) -> List[str]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| "SELECT run_id FROM test_runs WHERE user_id = ?", | |
| (user_id,), | |
| ).fetchall() | |
| ids = [r["run_id"] for r in rows] | |
| con.execute("DELETE FROM test_runs WHERE user_id = ?", (user_id,)) | |
| return ids | |
| def analytics_rows(db_path: Path, user_id: str) -> List[dict]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| """ | |
| SELECT de_improvement, conflict_resolution, naturalness, | |
| pass_de, pass_resolution, pass_naturalness | |
| FROM test_runs WHERE user_id = ? | |
| """, | |
| (user_id,), | |
| ).fetchall() | |
| out = [] | |
| for r in rows: | |
| d = dict(r) | |
| for key in ("pass_de", "pass_resolution", "pass_naturalness"): | |
| d[key] = bool(d.get(key)) | |
| out.append(d) | |
| return out | |
| def analytics_page(db_path: Path, user_id: str, limit: int, offset: int) -> List[dict]: | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| """ | |
| SELECT run_id, filename, cvd_type, severity, conf_threshold, created_at, metrics, | |
| de_improvement, conflict_resolution, naturalness, | |
| pass_de, pass_resolution, pass_naturalness, | |
| conflicts_found, boxes_detected, inference_ms | |
| FROM test_runs WHERE user_id = ? | |
| ORDER BY created_at DESC LIMIT ? OFFSET ? | |
| """, | |
| (user_id, limit, offset), | |
| ).fetchall() | |
| out = [] | |
| for r in rows: | |
| d = dict(r) | |
| d["metrics"] = json.loads(d.get("metrics") or "{}") | |
| for key in ("pass_de", "pass_resolution", "pass_naturalness"): | |
| d[key] = bool(d.get(key)) | |
| out.append(d) | |
| return out | |
| def purge_expired(db_path: Path, static_dir: Path) -> None: | |
| now = datetime.now(timezone.utc).isoformat() | |
| with _connect(db_path) as con: | |
| rows = con.execute( | |
| "SELECT job_id FROM jobs WHERE expires_at <= ?", | |
| (now,), | |
| ).fetchall() | |
| for r in rows: | |
| delete_images(static_dir, f"jobs/{r['job_id']}") | |
| con.execute("DELETE FROM jobs WHERE expires_at <= ?", (now,)) | |
| cutoff = (datetime.now(timezone.utc) - timedelta(hours=24)).isoformat() | |
| runs = con.execute( | |
| "SELECT run_id FROM test_runs WHERE created_at <= ? AND original_url != ''", (cutoff,), | |
| ).fetchall() | |
| for row in runs: | |
| delete_images(static_dir, f"test-runs/{row['run_id']}") | |
| con.execute("UPDATE test_runs SET original_url = '', corrected_url = '' WHERE run_id = ?", | |
| (row['run_id'],)) | |