| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418 |
- // offline_storage.cpp
- #include "app_sq_save.h"
- #include <QStandardPaths>
- #include <QDir>
- #include <QSqlQuery>
- #include <QSqlError>
- #include <QDebug>
- #include <m_loger.h>
- #include <QJsonDocument>
- #include <QJsonObject>
- #include <QDateTime>
- #include <QFileInfo>
- #include <QFile>
- #include <QDir>
- 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<QPair<int, QPair<QString, QByteArray>>>
- OfflineStorage::getPendingJsonMessages(int maxCount)
- {
- QList<QPair<int, QPair<QString, QByteArray>>> 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<QPair<int, QPair<QString, QString>>>
- OfflineStorage::getPendingImagePaths(int maxCount)
- {
- LOG("getPendingImagePaths...");
- QList<QPair<int, QPair<QString, QString>>> 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;
- }
|