大疆DJI action5 Pro清理脚本

clean_db.sh

#!/bin/bash
# 大疆 AC004.db 僵尸记录清理脚本
# 用法:
#   bash clean_db.sh          # 仅检测，列出僵尸记录
#   bash clean_db.sh --clean  # 清理僵尸记录（操作前自动备份 DB）

DB="/Volumes/SD_Card/MISC/AC004.db"
MOUNT="/Volumes/SD_Card"
CAM_PREFIX="/mnt/media_rw/sd"
CLEAN=false

[[ "$1" == "--clean" ]] && CLEAN=true

if [[ ! -f "$DB" ]]; then
    echo "错误：找不到数据库 $DB"
    exit 1
fi

echo "=== AC004.db 僵尸记录检测 ==="
echo ""

# 收集所有 DB 中文件不存在的记录（ID 和路径）
stale_ids=()
stale_files=()

while IFS='|' read -r id filepath; do
    macpath="${filepath/${CAM_PREFIX}/${MOUNT}}"
    if [[ ! -f "$macpath" ]]; then
        stale_ids+=("$id")
        stale_files+=("$filepath")
    fi
done < <(sqlite3 "$DB" "SELECT ID, file_name FROM gis_info_table;")

total=${#stale_ids[@]}

if [[ $total -eq 0 ]]; then
    echo "  数据库与磁盘文件完全一致，无僵尸记录。"
    exit 0
fi

# 按类型分类统计
mp4=0; lrf=0; jpg=0; dng=0; other=0
for f in "${stale_files[@]}"; do
    case "$f" in
        *.MP4) (( mp4++ )) ;;
        *.LRF) (( lrf++ )) ;;
        *.JPG) (( jpg++ )) ;;
        *.DNG) (( dng++ )) ;;
        *)     (( other++ )) ;;
    esac
done

echo "僵尸记录（DB 中有记录但文件已不存在）："
echo ""
printf "  %-6s %d 条\n" "MP4"   $mp4
printf "  %-6s %d 条\n" "LRF"   $lrf
printf "  %-6s %d 条\n" "JPG"   $jpg
printf "  %-6s %d 条\n" "DNG"   $dng
[[ $other -gt 0 ]] && printf "  %-6s %d 条\n" "其他" $other
echo ""
echo "  合计：$total 条（共 $(sqlite3 "$DB" "SELECT COUNT(*) FROM gis_info_table;") 条中占比 $(( total * 100 / $(sqlite3 "$DB" "SELECT COUNT(*) FROM gis_info_table;") ))%）"
echo ""
echo "原因说明："
echo "  - 通过相机 UI 删除的文件：相机会同步清理 DB"
echo "  - 在 Mac/PC 上直接删除的文件：DB 记录残留，形成僵尸记录"
echo "  - 相机重新挂载 SD 卡后会自动重建索引，通常无需手动清理"

if $CLEAN; then
    echo ""
    # 备份
    backup="${DB}.bak_$(date +%Y%m%d_%H%M%S)"
    cp "$DB" "$backup"
    echo "已备份数据库至：$backup"
    echo ""
    echo "即将从 gis_info_table 删除 $total 条僵尸记录。"
    echo "（DB 内置触发器会自动级联清理 video_info_table / image_info_table）"
    echo "输入 yes 确认，其他输入取消："
    read -r answer
    if [[ "$answer" != "yes" ]]; then
        echo "已取消。备份文件保留在：$backup"
        exit 0
    fi

    # 构建 DELETE SQL
    id_list=$(IFS=','; echo "${stale_ids[*]}")
    deleted=$(sqlite3 "$DB" "
        DELETE FROM gis_info_table WHERE ID IN ($id_list);
        SELECT changes();
    ")

    # VACUUM 收缩 DB
    sqlite3 "$DB" "VACUUM;"

    echo ""
    echo "完成：删除 $deleted 条记录，数据库已压缩。"
    echo "剩余记录数：$(sqlite3 "$DB" "SELECT COUNT(*) FROM gis_info_table;")"
else
    echo ""
    echo "如需清理，运行："
    echo "  bash $0 --clean"
fi