# 同步函数 sync_directories() { local source_dir="$1" local target_dir="$2"
# 检查源目录是否存在 if [ ! -d "$source_dir" ]; then log"源目录 $source_dir 不存在。" exit 1 fi
# 检查目标目录是否存在,如果不存在则创建 if [ ! -d "$target_dir" ]; then log"警告:目标目录 $target_dir 不存在,正在创建..." mkdir -p "$target_dir" if [ $? -ne 0 ]; then log"创建目标目录 $target_dir 失败。" exit 1 fi fi
-- 步骤1:创建临时表 CREATE TEMPORARY TABLE temp_zvideo ( id INTEGER, title VARCHAR(250), auto_series_id VARCHAR(64), user_name VARCHAR(50), release_year VARCHAR(16), release_date INTEGER, release TEXT );
-- 插入数据到临时表,移除LIMIT 1以处理所有符合条件的记录 INSERTINTO temp_zvideo SELECTDISTINCT zc.id, zc.title, zc.auto_series_id, zc.user_name, COALESCE(zc2.release_year, zc.release_year) AS release_year, COALESCE(zc2.release_date, zc.release_date) AS release_date, COALESCE(zc2.release, zc.release) ASrelease FROM zvideo_collection zc LEFTJOIN ( SELECT auto_series_id, user_name, release_year, release_date, release FROM zvideo_collection zc2 WHERE extend_type !=7 AND release_date = ( SELECTMAX(release_date) FROM zvideo_collection zc3 WHERE zc3.auto_series_id = zc2.auto_series_id AND zc3.user_name = zc2.user_name AND zc3.extend_type !=7 AND zc3.release_date ISNOTNULL ) ) zc2 ON zc.auto_series_id = zc2.auto_series_id AND zc.user_name = zc2.user_name WHERE zc.extend_type =7;
-- 步骤2:更新原表 UPDATE zvideo_collection SET release_year =COALESCE(( SELECT release_year FROM temp_zvideo WHERE temp_zvideo.id = zvideo_collection.id ), release_year), release_date =COALESCE(( SELECT release_date FROM temp_zvideo WHERE temp_zvideo.id = zvideo_collection.id ), release_date), release=COALESCE(( SELECTrelease FROM temp_zvideo WHERE temp_zvideo.id = zvideo_collection.id ), release) WHERE id IN (SELECT id FROM temp_zvideo);
# 检查 sqlite3 命令是否可用 if ! command -v sqlite3 &> /dev/null; then log"错误:sqlite3 未安装,请先安装 sqlite3。" exit 1 fi
# 检查文件是否存在,提升脚本鲁棒性 if [ ! -f "$DB_PATH" ]; then log"错误:数据库文件 $DB_PATH 不存在。" exit 1 fi if [ ! -f "$SQL_FILE" ]; then log"错误:SQL 文件 $SQL_FILE 不存在。" exit 1 fi
log"开始更新数据库..."
# 初始化重试计数器 retry_count=0
# 使用循环处理数据库锁定情况 while [ $retry_count -lt $MAX_RETRIES ]; do # 执行 SQL 脚本,捕获输出和状态码 output=$(sqlite3 "$DB_PATH" < "$SQL_FILE" 2>&1) status=$?
# 检查执行结果 if [ $status -eq 0 ]; then log"数据库更新成功完成。更新行数:$output" break# 成功后退出循环 else # 检查是否因数据库锁定失败 ifecho"$output" | grep -q "database is locked"; then retry_count=$((retry_count + 1)) log"数据库被锁定,等待 $RETRY_INTERVAL 秒后重试... (尝试 $retry_count/$MAX_RETRIES)" sleep$RETRY_INTERVAL else log"数据库更新失败,错误信息如下:" log"$output"# 直接打印错误信息 exit 1 # 非锁定错误,直接退出 fi fi done
# 检查是否达到最大重试次数 if [ $retry_count -eq $MAX_RETRIES ]; then log"错误:达到最大重试次数 $MAX_RETRIES,数据库仍被锁定,更新失败。" exit 1 fi