app_sq_save.cpp 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356
  1. // offline_storage.cpp
  2. #include "app_sq_save.h"
  3. #include <QStandardPaths>
  4. #include <QDir>
  5. #include <QSqlQuery>
  6. #include <QSqlError>
  7. #include <QDebug>
  8. #include <m_loger.h>
  9. #include <QJsonDocument>
  10. #include <QJsonObject>
  11. #include <QDateTime>
  12. #include <QFileInfo>
  13. #include <QFile>
  14. #include <QDir>
  15. OfflineStorage::OfflineStorage(QObject *parent)
  16. : QObject(parent)
  17. {
  18. QString dbPath = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation) + "/offline.db";
  19. QDir().mkpath(QFileInfo(dbPath).absolutePath());
  20. m_db = QSqlDatabase::addDatabase("QSQLITE", "OFFLINE_DB");
  21. m_db.setDatabaseName(dbPath);
  22. if (!m_db.open()) {
  23. qWarning() << "无法打开数据库:" << m_db.lastError();
  24. return;
  25. }
  26. createTables();
  27. }
  28. OfflineStorage::~OfflineStorage()
  29. {
  30. if (m_db.isOpen()) {
  31. m_db.close();
  32. }
  33. }
  34. bool OfflineStorage::createTables()
  35. {
  36. QSqlQuery query(m_db);
  37. // JSON 表
  38. if (!query.exec(R"(
  39. CREATE TABLE IF NOT EXISTS json_messages (
  40. id INTEGER PRIMARY KEY AUTOINCREMENT,
  41. topic TEXT NOT NULL,
  42. payload TEXT NOT NULL,
  43. stamp INT
  44. )
  45. )")) {
  46. qWarning() << "创建 json_messages 表失败:" << query.lastError();
  47. return false;
  48. }
  49. // 图片路径表
  50. if (!query.exec(R"(
  51. CREATE TABLE IF NOT EXISTS image_paths (
  52. id INTEGER PRIMARY KEY AUTOINCREMENT,
  53. image_path TEXT NOT NULL,
  54. stamp INT
  55. )
  56. )")) {
  57. qWarning() << "创建 image_paths 表失败:" << query.lastError();
  58. return false;
  59. }
  60. return true;
  61. }
  62. bool OfflineStorage::cacheJsonMessage(const QString &topic, const QByteArray &json)
  63. {
  64. // 获取当前本地时间(基于系统时区)
  65. QDateTime localNow = QDateTime::currentDateTime();
  66. qint64 secTimestamp = localNow.toSecsSinceEpoch();
  67. qDebug() << "本地秒级时间戳:" << secTimestamp;
  68. if (!m_jsonCacheEnabled) {
  69. qWarning() << "JSON缓存已禁用,拒绝缓存数据";
  70. return false;
  71. }
  72. QSqlQuery query(m_db);
  73. // ==================== 步骤1:解析原始 JSON ====================
  74. QJsonParseError parseError;
  75. QJsonDocument doc = QJsonDocument::fromJson(json, &parseError);
  76. // 检查解析是否成功
  77. if (parseError.error != QJsonParseError::NoError) {
  78. ERROR("JSON 解析失败,错误信息:{}",parseError.errorString().toStdString());
  79. return false;
  80. }
  81. // 🔴 先检查是否超限,是则删除最老一条
  82. if (!enforceLimit("json_messages", m_maxJsonEntries)) {
  83. qWarning() << "清理 json_messages 失败";
  84. // 即使失败也尝试插入(避免阻塞)
  85. }
  86. // ==================== 步骤2:修改 cmd 键值 ====================
  87. QJsonObject rootObj = doc.object();
  88. // 查找 "cmd" 键并修改其值
  89. if(rootObj.contains("cmd")){
  90. rootObj["cmd"] = "data-history";
  91. }else{
  92. WARN("未匹配到cmd键值...");
  93. }
  94. // ==================== 步骤3:序列化为新 JSON 字符串 ====================
  95. QJsonDocument modifiedDoc(rootObj);
  96. QByteArray modifiedJson = modifiedDoc.toJson(QJsonDocument::Indented); // 保留缩进格式
  97. query.prepare("INSERT INTO json_messages (topic, payload,stamp) VALUES (?, ?, ?)");
  98. query.addBindValue(topic);
  99. query.addBindValue(modifiedJson);
  100. query.addBindValue(secTimestamp);
  101. return query.exec();
  102. }
  103. bool OfflineStorage::cacheImagePath(const QString &imagePath)
  104. {
  105. // 获取当前本地时间(基于系统时区)
  106. QDateTime localNow = QDateTime::currentDateTime();
  107. qint64 secTimestamp = localNow.toSecsSinceEpoch();
  108. qDebug() << "本地秒级时间戳:" << secTimestamp;
  109. if (!m_imageCacheEnabled) {
  110. qWarning() << "图片缓存已禁用,拒绝缓存路径";
  111. return false;
  112. }
  113. QSqlQuery query(m_db);
  114. // 🔴 先检查是否超限
  115. if (!enforceLimit("image_paths", m_maxImageEntries)) {
  116. qWarning() << "清理 image_paths 失败";
  117. }
  118. // 1. 检查原文件是否存在
  119. QFileInfo originalFile(imagePath);
  120. if (!originalFile.exists()) {
  121. qWarning() << "原文件不存在:" << imagePath;
  122. return false;
  123. }
  124. // 2. 检查是否为文件(非目录)
  125. if (!originalFile.isFile()) {
  126. qWarning() << "路径不是文件:" << imagePath;
  127. return false;
  128. }
  129. // 3. 提取目录、文件名(含扩展名)和基名(不含扩展名)
  130. QString dirPath = originalFile.absolutePath(); // 原目录(如 "/pic")
  131. QString fileName = originalFile.fileName(); // 原文件名(如 "123123.jpg")
  132. QString baseName = originalFile.baseName(); // 基名(如 "123123")
  133. QString suffix = originalFile.suffix(); // 扩展名(如 "jpg")
  134. // 4. 构造新文件名(添加 "history-" 前缀)
  135. QString newFileName = QString("history-%1.%2").arg(baseName, suffix); // "history-123123.jpg"
  136. // 5. 构造新文件完整路径
  137. QString newPath = QDir(dirPath).filePath(newFileName); // 拼接目录和新文件名(自动处理斜杠)
  138. // 6. 执行重命名(覆盖模式:若目标文件已存在,会直接覆盖)
  139. // 注意:若需避免覆盖,可先检查 newPath 是否存在
  140. if (QFile::exists(newPath)) {
  141. qWarning() << "目标文件已存在,重命名失败:" << newPath;
  142. return false;
  143. }
  144. // 执行重命名操作
  145. if (!QFile::rename(imagePath, newPath)) {
  146. qWarning() << "重命名失败!";
  147. return false;
  148. }
  149. query.prepare("INSERT INTO image_paths (image_path,stamp) VALUES (?,?)");
  150. query.addBindValue(newPath);
  151. query.addBindValue(secTimestamp);
  152. if(!query.exec()){
  153. ERROR("插入失败:{}",query.lastError().text().toStdString());
  154. return false;
  155. }
  156. return true;
  157. }
  158. //获取最新时间戳的JSON
  159. QList<QPair<int, QPair<QString, QByteArray>>>
  160. OfflineStorage::getPendingJsonMessages(int maxCount)
  161. {
  162. QList<QPair<int, QPair<QString, QByteArray>>> list;
  163. QSqlQuery query(m_db);
  164. // 修改点 1:按时间戳降序排序(stamp DESC),取最新的 maxCount 条
  165. query.prepare(R"(
  166. SELECT id, topic, payload
  167. FROM json_messages
  168. ORDER BY stamp DESC -- 按时间戳降序(最新的在前)
  169. LIMIT ? -- 限制返回数量
  170. )");
  171. query.addBindValue(maxCount);
  172. if (query.exec()) {
  173. while (query.next()) {
  174. int id = query.value(0).toInt();
  175. QString topic = query.value(1).toString();
  176. QByteArray json = query.value(2).toByteArray();
  177. list.append({id,{topic, json}});
  178. // 注意:这里不立即标记 uploaded,等待上传成功后再标记
  179. }
  180. }
  181. return list;
  182. }
  183. //获取最新时间戳的图片路径
  184. QList<QPair<int,QString>>
  185. OfflineStorage::getPendingImagePaths(int maxCount)
  186. {
  187. LOG("getPendingImagePaths...");
  188. QList<QPair<int,QString>> list;
  189. QSqlQuery query(m_db);
  190. // 修改点 1:按时间戳降序排序(stamp DESC),取最新的 maxCount 条
  191. query.prepare(R"(
  192. SELECT id,image_path
  193. FROM image_paths
  194. ORDER BY stamp DESC -- 按时间戳降序(最新的在前)
  195. LIMIT ? -- 限制返回数量
  196. )");
  197. query.addBindValue(maxCount);
  198. if (query.exec()) {
  199. while (query.next()) {
  200. int id = query.value(0).toInt();
  201. QString path = query.value(1).toString();
  202. list.append({id,path});
  203. }
  204. }
  205. return list;
  206. }
  207. //删除指定ID的数据记录
  208. bool OfflineStorage::markJsonUploaded(int id)
  209. {
  210. QSqlQuery query(m_db);
  211. query.prepare("DELETE FROM json_messages WHERE id = ?");
  212. query.addBindValue(id);
  213. return query.exec();
  214. }
  215. //删除指定路径的数据记录
  216. bool OfflineStorage::markImageUploaded(const QString &imagePath)
  217. {
  218. QSqlQuery query(m_db);
  219. // 修改点 1:SQL 条件改为根据 image_path 匹配
  220. query.prepare(R"(
  221. DELETE FROM image_paths
  222. WHERE image_path = ? -- 匹配 image_path 字段
  223. )");
  224. // 修改点 2:绑定图片路径参数
  225. query.addBindValue(imagePath);
  226. // 执行删除操作
  227. if (!query.exec()) {
  228. qWarning() << "删除图片路径记录失败,SQL 错误:" << query.lastError().text();
  229. qWarning() << "失败 SQL:" << query.lastQuery(); // 打印实际执行的 SQL(调试用)
  230. return false;
  231. } else {
  232. qDebug() << "成功删除图片路径记录,路径:" << imagePath
  233. << ",受影响行数:" << query.numRowsAffected();
  234. return true;
  235. }
  236. }
  237. bool OfflineStorage::cleanupUploaded()
  238. {
  239. QSqlQuery query(m_db);
  240. bool success = true;
  241. success &= query.exec("DELETE FROM json_messages WHERE uploaded = 1");
  242. success &= query.exec("DELETE FROM image_paths WHERE uploaded = 1");
  243. return success;
  244. }
  245. // offline_storage.cpp
  246. bool OfflineStorage::enforceLimit(const QString &table, int maxCount)
  247. {
  248. int count = 0;
  249. if(table=="json_messages"){
  250. count= JsonEntries_saved();
  251. }else if(table=="image_paths"){
  252. count= ImageEntries_saved();
  253. }
  254. if (count < maxCount || count==0) {
  255. return true; // 未超限,无需处理
  256. }
  257. QSqlQuery query(m_db);
  258. // 超限了 → 删除最早的一条
  259. query.prepare(QString(R"(
  260. DELETE FROM %1
  261. WHERE id = (
  262. SELECT id FROM %1
  263. ORDER BY stamp ASC LIMIT 1
  264. )
  265. )").arg(table));
  266. if (!query.exec()) {
  267. qWarning() << "删除最老记录失败:" << table << query.lastError();
  268. return false;
  269. }
  270. qDebug() << "已删除最老记录 from" << table << ", 当前数量:" << count;
  271. return true;
  272. }
  273. void OfflineStorage::setMaxJsonEntries(int max)
  274. {
  275. if (m_maxJsonEntries != max) {
  276. m_maxJsonEntries = max;
  277. emit maxJsonEntriesChanged();
  278. // 可选:立即清理,确保不超过新上限
  279. enforceLimit("json_messages", m_maxJsonEntries);
  280. }
  281. }
  282. void OfflineStorage::setJsonCacheEnabled(bool enabled)
  283. {
  284. if (m_jsonCacheEnabled != enabled) {
  285. m_jsonCacheEnabled = enabled;
  286. emit jsonCacheEnabledChanged(enabled); // 发射信号通知状态变更
  287. }
  288. }
  289. void OfflineStorage::setImageCacheEnabled(bool enabled)
  290. {
  291. if (m_imageCacheEnabled != enabled) {
  292. m_imageCacheEnabled = enabled;
  293. emit imageCacheEnabledChanged(enabled); // 发射信号通知状态变更
  294. }
  295. }
  296. void OfflineStorage::setMaxImageEntries(int max)
  297. {
  298. if (m_maxImageEntries != max) {
  299. m_maxImageEntries = max;
  300. emit maxImageEntriesChanged();
  301. enforceLimit("image_paths", m_maxImageEntries);
  302. }
  303. }
  304. int OfflineStorage::JsonEntries_saved()
  305. {
  306. QSqlQuery query(m_db);
  307. query.prepare("SELECT COUNT(*) FROM json_messages");
  308. if (!query.exec() || !query.next()) {
  309. qWarning() << "查询JSON数量失败:" << query.lastError();
  310. return 0;
  311. }
  312. m_JsonEntries_saved = query.value(0).toInt();
  313. return m_JsonEntries_saved;
  314. }
  315. int OfflineStorage::ImageEntries_saved()
  316. {
  317. QSqlQuery query(m_db);
  318. query.prepare("SELECT COUNT(*) FROM image_paths");
  319. if (!query.exec() || !query.next()) {
  320. qWarning() << "查询图片数量失败:" << query.lastError();
  321. return 0;
  322. }
  323. m_ImageEntries_saved = query.value(0).toInt();
  324. return m_ImageEntries_saved;
  325. }