yuming
b3608f534e
- 新增 analysis/ 模块：MOG2活动检测、YOLO人物跟踪、MediaPipe摔倒检测、Ollama活动打标、缩略图抽取 - 新增 webapp/：Flask事件浏览界面，按需HEVC→H264转码缓存 - 新增 analyze.py：7阶段状态机流水线（scan→backfill→motion→person→fall→activity→thumbnail） - 新增 cleanup_orphans.py：清理文件已删除的数据库孤儿记录 - 修复：motion/person/activity三阶段加单视频异常兜底，避免批次中断 - 修复：ollama_client加健康检查和3次重试，num_ctx从8192降到2048 - 修复：transcode_cache加fcntl文件锁，防止并发转码同一视频 - 修复：SQLite开WAL模式+busy_timeout，支持读写并发 - 修复：sync.py ffmpeg路径改为shutil.which查找，删除automate.py死代码PYTHON - 修复：thumbnail失败加3次重试上限，避免无限重试损坏文件 - 新增：requirements.lock锁定版本、README文档 - 更新：.gitignore排除.playwright-mcp、截图、备份文件
91 lines
3.0 KiB
Python
91 lines
3.0 KiB
Python
"""清理数据库中文件已不存在的孤儿记录。
|
|
|
|
video_scanner 入库后，如果录像文件被手动删除或被 dedup_cleanup 清理，
|
|
数据库里的 videos/events 记录不会自动消失，导致：
|
|
- analyze.py 对不存在的文件跑分析，全部失败或产生空结果
|
|
- webapp 点击播放 404
|
|
- 统计数据虚高
|
|
|
|
本脚本扫描 videos 表，删除 file_path 不存在的记录及其关联 events，
|
|
并同步清理孤儿缩略图文件。执行前请确保已备份 analysis.db。
|
|
"""
|
|
import os
|
|
import sqlite3
|
|
|
|
from analysis.config import PROJECT_ROOT, THUMBNAILS_DIR, DB_PATH
|
|
|
|
|
|
def cleanup_orphans(dry_run=True):
|
|
conn = sqlite3.connect(str(DB_PATH))
|
|
conn.row_factory = sqlite3.Row
|
|
|
|
videos = conn.execute("SELECT id, file_path, status FROM videos").fetchall()
|
|
to_delete = []
|
|
for v in videos:
|
|
full_path = PROJECT_ROOT / v["file_path"]
|
|
if not full_path.exists():
|
|
to_delete.append(v)
|
|
|
|
print(f"videos 总数: {len(videos)}")
|
|
print(f"文件不存在的孤儿记录: {len(to_delete)}")
|
|
|
|
# 按状态统计
|
|
by_status = {}
|
|
for v in to_delete:
|
|
by_status[v["status"]] = by_status.get(v["status"], 0) + 1
|
|
for status, cnt in sorted(by_status.items(), key=lambda x: -x[1]):
|
|
print(f" {status}: {cnt}")
|
|
|
|
if dry_run:
|
|
print("\n[dry-run] 未执行任何删除。加 --execute 实际执行。")
|
|
conn.close()
|
|
return
|
|
|
|
# 删除关联 events 和缩略图
|
|
deleted_events = 0
|
|
deleted_thumbs = 0
|
|
for v in to_delete:
|
|
events = conn.execute(
|
|
"SELECT id, thumbnail_path FROM events WHERE video_id = ?", (v["id"],)
|
|
).fetchall()
|
|
for e in events:
|
|
if e["thumbnail_path"]:
|
|
thumb_path = PROJECT_ROOT / e["thumbnail_path"]
|
|
if thumb_path.exists():
|
|
thumb_path.unlink()
|
|
deleted_thumbs += 1
|
|
conn.execute("DELETE FROM events WHERE video_id = ?", (v["id"],))
|
|
deleted_events += len(events)
|
|
|
|
# 删除 video 记录
|
|
video_ids = [v["id"] for v in to_delete]
|
|
if video_ids:
|
|
placeholders = ",".join("?" * len(video_ids))
|
|
conn.execute(f"DELETE FROM videos WHERE id IN ({placeholders})", video_ids)
|
|
|
|
conn.commit()
|
|
|
|
# 清理孤儿缩略图（events 已删但文件还在的）
|
|
event_thumbs = set(
|
|
r[0] for r in conn.execute("SELECT thumbnail_path FROM events WHERE thumbnail_path IS NOT NULL")
|
|
)
|
|
orphan_thumbs = 0
|
|
if THUMBNAILS_DIR.exists():
|
|
for f in THUMBNAILS_DIR.glob("*.jpg"):
|
|
rel = str(f.relative_to(PROJECT_ROOT))
|
|
if rel not in event_thumbs:
|
|
f.unlink()
|
|
orphan_thumbs += 1
|
|
|
|
conn.close()
|
|
|
|
print(f"\n已删除 videos 记录: {len(to_delete)}")
|
|
print(f"已删除 events 记录: {deleted_events}")
|
|
print(f"已清理缩略图文件: {deleted_thumbs + orphan_thumbs}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
import sys
|
|
dry_run = "--execute" not in sys.argv
|
|
cleanup_orphans(dry_run=dry_run)
|