// offline_storage.cpp #include "app_sq_save.h" #include #include #include #include #include #include #include #include #include #include #include #include OfflineStorage::OfflineStorage(QObject *parent) : QObject(parent) { QString dbPath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation) + "/offline.db"; QDir().mkpath(QFileInfo(dbPath).absolutePath()); m_db = QSqlDatabase::addDatabase("QSQLITE", "OFFLINE_DB"); m_db.setDatabaseName(dbPath); if (!m_db.open()) { WARN("无法打开数据库: {}", m_db.lastError().text().toStdString()); return; } createTables(); } OfflineStorage::~OfflineStorage() { if (m_db.isOpen()) { m_db.close(); } } bool OfflineStorage::createTables() { QSqlQuery query(m_db); // JSON 表 if (!query.exec(R"( CREATE TABLE IF NOT EXISTS json_messages ( id INTEGER PRIMARY KEY AUTOINCREMENT, topic TEXT NOT NULL, payload TEXT NOT NULL, stamp INT ) )")) { WARN("创建 json_messages 表失败: {}", query.lastError().text().toStdString()); return false; } // 图片路径表(parent_path 记录入库时图片的父目录,供补录/重传时对齐远端归档目录) if (!query.exec(R"( CREATE TABLE IF NOT EXISTS image_paths ( id INTEGER PRIMARY KEY AUTOINCREMENT, image_path TEXT NOT NULL, parent_path TEXT, stamp INT ) )")) { WARN("创建 image_paths 表失败: {}", query.lastError().text().toStdString()); return false; } // 兼容旧库:老表没有 parent_path 列时补充(CREATE 语句对已存在表不做修改) { QSqlQuery check(m_db); if (check.exec("SELECT parent_path FROM image_paths LIMIT 1") == false) { QSqlQuery alter(m_db); if (!alter.exec("ALTER TABLE image_paths ADD COLUMN parent_path TEXT")) { WARN("image_paths 添加 parent_path 列失败: {}", alter.lastError().text().toStdString()); } } } return true; } bool OfflineStorage::cacheJsonMessage(const QString &topic, const QByteArray &json) { // 获取当前本地时间(基于系统时区) QDateTime localNow = QDateTime::currentDateTime(); qint64 secTimestamp = localNow.toSecsSinceEpoch(); LOG("本地秒级时间戳:{}",secTimestamp); if (!m_jsonCacheEnabled) { WARN("JSON缓存已禁用,拒绝缓存数据"); return false; } QSqlQuery query(m_db); // ==================== 步骤1:解析原始 JSON ==================== QJsonParseError parseError; QJsonDocument doc = QJsonDocument::fromJson(json, &parseError); // 检查解析是否成功 if (parseError.error != QJsonParseError::NoError) { ERROR("JSON 解析失败,错误信息:{}",parseError.errorString().toStdString()); return false; } // 🔴 先检查是否超限,是则删除最老一条 if (!enforceLimit("json_messages", m_maxJsonEntries)) { WARN("清理 json_messages 失败"); // 即使失败也尝试插入(避免阻塞) } // ==================== 步骤2:修改 cmd 键值 ==================== QJsonObject rootObj = doc.object(); // 查找 "cmd" 键并修改其值 if(rootObj.contains("cmd")){ rootObj["cmd"] = "data-history"; }else{ WARN("未匹配到cmd键值..."); } // ==================== 步骤3:序列化为新 JSON 字符串 ==================== QJsonDocument modifiedDoc(rootObj); QByteArray modifiedJson = modifiedDoc.toJson(QJsonDocument::Indented); // 保留缩进格式 query.prepare("INSERT INTO json_messages (topic, payload,stamp) VALUES (?, ?, ?)"); query.addBindValue(topic); query.addBindValue(modifiedJson); query.addBindValue(secTimestamp); return query.exec(); } bool OfflineStorage::cacheImagePath_Real(const QString &imagePath) { // 获取当前本地时间(基于系统时区) QDateTime localNow = QDateTime::currentDateTime(); qint64 secTimestamp = localNow.toSecsSinceEpoch(); LOG("本地秒级时间戳:{}",secTimestamp); if (!m_imageCacheEnabled) { WARN("图片缓存已禁用,拒绝缓存路径"); return false; } QSqlQuery query(m_db); // 🔴 先检查是否超限 if (!enforceLimit("image_paths", m_maxImageEntries)) { WARN("清理 image_paths 失败"); } // 1. 检查原文件是否存在 QFileInfo originalFile(imagePath); if (!originalFile.exists()) { WARN("原文件不存在:{}",imagePath.toStdString()); //删除此纪录 markImageUploaded(imagePath); return false; } // 2. 检查是否为文件(非目录) if (!originalFile.isFile()) { WARN("路径不是文件:{}", imagePath.toStdString()); return false; } // 记录图片父目录,供补录/重传时对齐远端归档目录 QString parent_path = originalFile.absolutePath(); query.prepare("INSERT INTO image_paths (image_path,parent_path,stamp) VALUES (?,?,?)"); query.addBindValue(imagePath); query.addBindValue(parent_path); query.addBindValue(secTimestamp); if(!query.exec()){ ERROR("插入失败:{}",query.lastError().text().toStdString()); return false; } return true; } bool OfflineStorage::cacheImagePath(const QString &imagePath) { // 获取当前本地时间(基于系统时区) QDateTime localNow = QDateTime::currentDateTime(); qint64 secTimestamp = localNow.toSecsSinceEpoch(); LOG("本地秒级时间戳:{}",secTimestamp); if (!m_imageCacheEnabled) { WARN("图片缓存已禁用,拒绝缓存路径"); return false; } QSqlQuery query(m_db); // 🔴 先检查是否超限 if (!enforceLimit("image_paths", m_maxImageEntries)) { WARN("清理 image_paths 失败"); } // 1. 检查原文件是否存在 QFileInfo originalFile(imagePath); if (!originalFile.exists()) { WARN("原文件不存在:{}",imagePath.toStdString()); //删除此纪录 markImageUploaded(imagePath); return false; } // 2. 检查是否为文件(非目录) if (!originalFile.isFile()) { WARN("路径不是文件:{}", imagePath.toStdString()); return false; } // 3. 提取目录、文件名(含扩展名)和基名(不含扩展名) QString dirPath = originalFile.absolutePath(); // 原目录(如 "/pic") QString fileName = originalFile.fileName(); // 原文件名(如 "123123.jpg") QString baseName = originalFile.baseName(); // 基名(如 "123123") QString suffix = originalFile.suffix(); // 扩展名(如 "jpg") // 4. 构造新文件名(添加 "history-" 前缀) QString newFileName = QString("history-%1.%2").arg(baseName, suffix); // "history-123123.jpg" // 5. 构造新文件完整路径 QString newPath = QDir(dirPath).filePath(newFileName); // 拼接目录和新文件名(自动处理斜杠) // 6. 执行重命名(覆盖模式:若目标文件已存在,会直接覆盖) // 注意:若需避免覆盖,可先检查 newPath 是否存在 if (QFile::exists(newPath)) { WARN("目标文件已存在,重命名失败:{}", newPath.toStdString()); return false; } // 执行重命名操作 if (!QFile::rename(imagePath, newPath)) { WARN("重命名失败!"); return false; } // 记录图片父目录,供补录/重传时对齐远端归档目录(重命名不改变所在目录) query.prepare("INSERT INTO image_paths (image_path,parent_path,stamp) VALUES (?,?,?)"); query.addBindValue(newPath); query.addBindValue(dirPath); query.addBindValue(secTimestamp); if(!query.exec()){ ERROR("插入失败:{}",query.lastError().text().toStdString()); return false; } return true; } //获取最新时间戳的JSON QList>> OfflineStorage::getPendingJsonMessages(int maxCount) { QList>> list; QSqlQuery query(m_db); // 修改点 1:按时间戳降序排序(stamp DESC),取最新的 maxCount 条 query.prepare(R"( SELECT id, topic, payload FROM json_messages ORDER BY stamp DESC -- 按时间戳降序(最新的在前) LIMIT ? -- 限制返回数量 )"); query.addBindValue(maxCount); if (query.exec()) { while (query.next()) { int id = query.value(0).toInt(); QString topic = query.value(1).toString(); QByteArray json = query.value(2).toByteArray(); list.append({id,{topic, json}}); // 注意:这里不立即标记 uploaded,等待上传成功后再标记 } } return list; } //获取最新时间戳的图片路径(返回 (id, (image_path, parent_path)),parent_path 供补录/重传对齐归档目录) QList>> OfflineStorage::getPendingImagePaths(int maxCount) { LOG("getPendingImagePaths..."); QList>> list; QSqlQuery query(m_db); // 按时间戳降序取最新 maxCount 条,同时取出入库时记录的父目录 query.prepare(R"( SELECT id,image_path,parent_path FROM image_paths ORDER BY stamp DESC -- 按时间戳降序(最新的在前) LIMIT ? -- 限制返回数量 )"); query.addBindValue(maxCount); if (query.exec()) { while (query.next()) { int id = query.value(0).toInt(); QString path = query.value(1).toString(); QString parent = query.value(2).toString(); // 旧记录无 parent_path 时,退化为当前文件所在目录 if (parent.isEmpty()) { parent = QFileInfo(path).absolutePath(); } list.append({id,{path,parent}}); } } return list; } //删除指定ID的数据记录 bool OfflineStorage::markJsonUploaded(int id) { QSqlQuery query(m_db); query.prepare("DELETE FROM json_messages WHERE id = ?"); query.addBindValue(id); return query.exec(); } //删除指定路径的数据记录 bool OfflineStorage::markImageUploaded(const QString &imagePath) { QSqlQuery query(m_db); // 修改点 1:SQL 条件改为根据 image_path 匹配 query.prepare(R"( DELETE FROM image_paths WHERE image_path = ? -- 匹配 image_path 字段 )"); // 修改点 2:绑定图片路径参数 query.addBindValue(imagePath); // 执行删除操作 if (!query.exec()) { WARN("删除图片路径记录失败,SQL 错误:{}", query.lastError().text().toStdString()); WARN("失败 SQL:{}", query.lastQuery().toStdString()); // 打印实际执行的 SQL(调试用) return false; } else { LOG("成功删除图片路径记录,路径:{},受影响行数:{}", imagePath.toStdString(), (int)query.numRowsAffected()); return true; } } bool OfflineStorage::cleanupUploaded() { QSqlQuery query(m_db); bool success = true; success &= query.exec("DELETE FROM json_messages WHERE uploaded = 1"); success &= query.exec("DELETE FROM image_paths WHERE uploaded = 1"); return success; } // offline_storage.cpp bool OfflineStorage::enforceLimit(const QString &table, int maxCount) { int count = 0; if(table=="json_messages"){ count= JsonEntries_saved(); }else if(table=="image_paths"){ count= ImageEntries_saved(); } if (count < maxCount || count==0) { return true; // 未超限,无需处理 } QSqlQuery query(m_db); // 超限了 → 删除最早的一条 query.prepare(QString(R"( DELETE FROM %1 WHERE id = ( SELECT id FROM %1 ORDER BY stamp ASC LIMIT 1 ) )").arg(table)); if (!query.exec()) { WARN("删除最老记录失败: {} {}", table.toStdString(), query.lastError().text().toStdString()); return false; } LOG("已删除最老记录 from {}, 当前数量: {}", table.toStdString(), count); return true; } void OfflineStorage::setMaxJsonEntries(int max) { if (m_maxJsonEntries != max) { m_maxJsonEntries = max; emit maxJsonEntriesChanged(); // 可选:立即清理,确保不超过新上限 enforceLimit("json_messages", m_maxJsonEntries); } } void OfflineStorage::setJsonCacheEnabled(bool enabled) { if (m_jsonCacheEnabled != enabled) { m_jsonCacheEnabled = enabled; emit jsonCacheEnabledChanged(enabled); // 发射信号通知状态变更 } } void OfflineStorage::setImageCacheEnabled(bool enabled) { if (m_imageCacheEnabled != enabled) { m_imageCacheEnabled = enabled; emit imageCacheEnabledChanged(enabled); // 发射信号通知状态变更 } } void OfflineStorage::setMaxImageEntries(int max) { if (m_maxImageEntries != max) { m_maxImageEntries = max; emit maxImageEntriesChanged(); enforceLimit("image_paths", m_maxImageEntries); } } int OfflineStorage::JsonEntries_saved() { QSqlQuery query(m_db); query.prepare("SELECT COUNT(*) FROM json_messages"); if (!query.exec() || !query.next()) { WARN("查询JSON数量失败: {}", query.lastError().text().toStdString()); return 0; } m_JsonEntries_saved = query.value(0).toInt(); return m_JsonEntries_saved; } int OfflineStorage::ImageEntries_saved() { QSqlQuery query(m_db); query.prepare("SELECT COUNT(*) FROM image_paths"); if (!query.exec() || !query.next()) { WARN("查询图片数量失败: {}", query.lastError().text().toStdString()); return 0; } m_ImageEntries_saved = query.value(0).toInt(); return m_ImageEntries_saved; }