app_sq_save.cpp 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418
  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::AppDataLocation) + "/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. WARN("无法打开数据库: {}", m_db.lastError().text().toStdString());
  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. WARN("创建 json_messages 表失败: {}", query.lastError().text().toStdString());
  47. return false;
  48. }
  49. // 图片路径表(parent_path 记录入库时图片的父目录,供补录/重传时对齐远端归档目录)
  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. parent_path TEXT,
  55. stamp INT
  56. )
  57. )")) {
  58. WARN("创建 image_paths 表失败: {}", query.lastError().text().toStdString());
  59. return false;
  60. }
  61. // 兼容旧库:老表没有 parent_path 列时补充(CREATE 语句对已存在表不做修改)
  62. {
  63. QSqlQuery check(m_db);
  64. if (check.exec("SELECT parent_path FROM image_paths LIMIT 1") == false) {
  65. QSqlQuery alter(m_db);
  66. if (!alter.exec("ALTER TABLE image_paths ADD COLUMN parent_path TEXT")) {
  67. WARN("image_paths 添加 parent_path 列失败: {}", alter.lastError().text().toStdString());
  68. }
  69. }
  70. }
  71. return true;
  72. }
  73. bool OfflineStorage::cacheJsonMessage(const QString &topic, const QByteArray &json)
  74. {
  75. // 获取当前本地时间(基于系统时区)
  76. QDateTime localNow = QDateTime::currentDateTime();
  77. qint64 secTimestamp = localNow.toSecsSinceEpoch();
  78. LOG("本地秒级时间戳:{}",secTimestamp);
  79. if (!m_jsonCacheEnabled) {
  80. WARN("JSON缓存已禁用,拒绝缓存数据");
  81. return false;
  82. }
  83. QSqlQuery query(m_db);
  84. // ==================== 步骤1:解析原始 JSON ====================
  85. QJsonParseError parseError;
  86. QJsonDocument doc = QJsonDocument::fromJson(json, &parseError);
  87. // 检查解析是否成功
  88. if (parseError.error != QJsonParseError::NoError) {
  89. ERROR("JSON 解析失败,错误信息:{}",parseError.errorString().toStdString());
  90. return false;
  91. }
  92. // 🔴 先检查是否超限,是则删除最老一条
  93. if (!enforceLimit("json_messages", m_maxJsonEntries)) {
  94. WARN("清理 json_messages 失败");
  95. // 即使失败也尝试插入(避免阻塞)
  96. }
  97. // ==================== 步骤2:修改 cmd 键值 ====================
  98. QJsonObject rootObj = doc.object();
  99. // 查找 "cmd" 键并修改其值
  100. if(rootObj.contains("cmd")){
  101. rootObj["cmd"] = "data-history";
  102. }else{
  103. WARN("未匹配到cmd键值...");
  104. }
  105. // ==================== 步骤3:序列化为新 JSON 字符串 ====================
  106. QJsonDocument modifiedDoc(rootObj);
  107. QByteArray modifiedJson = modifiedDoc.toJson(QJsonDocument::Indented); // 保留缩进格式
  108. query.prepare("INSERT INTO json_messages (topic, payload,stamp) VALUES (?, ?, ?)");
  109. query.addBindValue(topic);
  110. query.addBindValue(modifiedJson);
  111. query.addBindValue(secTimestamp);
  112. return query.exec();
  113. }
  114. bool OfflineStorage::cacheImagePath_Real(const QString &imagePath)
  115. {
  116. // 获取当前本地时间(基于系统时区)
  117. QDateTime localNow = QDateTime::currentDateTime();
  118. qint64 secTimestamp = localNow.toSecsSinceEpoch();
  119. LOG("本地秒级时间戳:{}",secTimestamp);
  120. if (!m_imageCacheEnabled) {
  121. WARN("图片缓存已禁用,拒绝缓存路径");
  122. return false;
  123. }
  124. QSqlQuery query(m_db);
  125. // 🔴 先检查是否超限
  126. if (!enforceLimit("image_paths", m_maxImageEntries)) {
  127. WARN("清理 image_paths 失败");
  128. }
  129. // 1. 检查原文件是否存在
  130. QFileInfo originalFile(imagePath);
  131. if (!originalFile.exists()) {
  132. WARN("原文件不存在:{}",imagePath.toStdString());
  133. //删除此纪录
  134. markImageUploaded(imagePath);
  135. return false;
  136. }
  137. // 2. 检查是否为文件(非目录)
  138. if (!originalFile.isFile()) {
  139. WARN("路径不是文件:{}", imagePath.toStdString());
  140. return false;
  141. }
  142. // 记录图片父目录,供补录/重传时对齐远端归档目录
  143. QString parent_path = originalFile.absolutePath();
  144. query.prepare("INSERT INTO image_paths (image_path,parent_path,stamp) VALUES (?,?,?)");
  145. query.addBindValue(imagePath);
  146. query.addBindValue(parent_path);
  147. query.addBindValue(secTimestamp);
  148. if(!query.exec()){
  149. ERROR("插入失败:{}",query.lastError().text().toStdString());
  150. return false;
  151. }
  152. return true;
  153. }
  154. bool OfflineStorage::cacheImagePath(const QString &imagePath)
  155. {
  156. // 获取当前本地时间(基于系统时区)
  157. QDateTime localNow = QDateTime::currentDateTime();
  158. qint64 secTimestamp = localNow.toSecsSinceEpoch();
  159. LOG("本地秒级时间戳:{}",secTimestamp);
  160. if (!m_imageCacheEnabled) {
  161. WARN("图片缓存已禁用,拒绝缓存路径");
  162. return false;
  163. }
  164. QSqlQuery query(m_db);
  165. // 🔴 先检查是否超限
  166. if (!enforceLimit("image_paths", m_maxImageEntries)) {
  167. WARN("清理 image_paths 失败");
  168. }
  169. // 1. 检查原文件是否存在
  170. QFileInfo originalFile(imagePath);
  171. if (!originalFile.exists()) {
  172. WARN("原文件不存在:{}",imagePath.toStdString());
  173. //删除此纪录
  174. markImageUploaded(imagePath);
  175. return false;
  176. }
  177. // 2. 检查是否为文件(非目录)
  178. if (!originalFile.isFile()) {
  179. WARN("路径不是文件:{}", imagePath.toStdString());
  180. return false;
  181. }
  182. // 3. 提取目录、文件名(含扩展名)和基名(不含扩展名)
  183. QString dirPath = originalFile.absolutePath(); // 原目录(如 "/pic")
  184. QString fileName = originalFile.fileName(); // 原文件名(如 "123123.jpg")
  185. QString baseName = originalFile.baseName(); // 基名(如 "123123")
  186. QString suffix = originalFile.suffix(); // 扩展名(如 "jpg")
  187. // 4. 构造新文件名(添加 "history-" 前缀)
  188. QString newFileName = QString("history-%1.%2").arg(baseName, suffix); // "history-123123.jpg"
  189. // 5. 构造新文件完整路径
  190. QString newPath = QDir(dirPath).filePath(newFileName); // 拼接目录和新文件名(自动处理斜杠)
  191. // 6. 执行重命名(覆盖模式:若目标文件已存在,会直接覆盖)
  192. // 注意:若需避免覆盖,可先检查 newPath 是否存在
  193. if (QFile::exists(newPath)) {
  194. WARN("目标文件已存在,重命名失败:{}", newPath.toStdString());
  195. return false;
  196. }
  197. // 执行重命名操作
  198. if (!QFile::rename(imagePath, newPath)) {
  199. WARN("重命名失败!");
  200. return false;
  201. }
  202. // 记录图片父目录,供补录/重传时对齐远端归档目录(重命名不改变所在目录)
  203. query.prepare("INSERT INTO image_paths (image_path,parent_path,stamp) VALUES (?,?,?)");
  204. query.addBindValue(newPath);
  205. query.addBindValue(dirPath);
  206. query.addBindValue(secTimestamp);
  207. if(!query.exec()){
  208. ERROR("插入失败:{}",query.lastError().text().toStdString());
  209. return false;
  210. }
  211. return true;
  212. }
  213. //获取最新时间戳的JSON
  214. QList<QPair<int, QPair<QString, QByteArray>>>
  215. OfflineStorage::getPendingJsonMessages(int maxCount)
  216. {
  217. QList<QPair<int, QPair<QString, QByteArray>>> list;
  218. QSqlQuery query(m_db);
  219. // 修改点 1:按时间戳降序排序(stamp DESC),取最新的 maxCount 条
  220. query.prepare(R"(
  221. SELECT id, topic, payload
  222. FROM json_messages
  223. ORDER BY stamp DESC -- 按时间戳降序(最新的在前)
  224. LIMIT ? -- 限制返回数量
  225. )");
  226. query.addBindValue(maxCount);
  227. if (query.exec()) {
  228. while (query.next()) {
  229. int id = query.value(0).toInt();
  230. QString topic = query.value(1).toString();
  231. QByteArray json = query.value(2).toByteArray();
  232. list.append({id,{topic, json}});
  233. // 注意:这里不立即标记 uploaded,等待上传成功后再标记
  234. }
  235. }
  236. return list;
  237. }
  238. //获取最新时间戳的图片路径(返回 (id, (image_path, parent_path)),parent_path 供补录/重传对齐归档目录)
  239. QList<QPair<int, QPair<QString, QString>>>
  240. OfflineStorage::getPendingImagePaths(int maxCount)
  241. {
  242. LOG("getPendingImagePaths...");
  243. QList<QPair<int, QPair<QString, QString>>> list;
  244. QSqlQuery query(m_db);
  245. // 按时间戳降序取最新 maxCount 条,同时取出入库时记录的父目录
  246. query.prepare(R"(
  247. SELECT id,image_path,parent_path
  248. FROM image_paths
  249. ORDER BY stamp DESC -- 按时间戳降序(最新的在前)
  250. LIMIT ? -- 限制返回数量
  251. )");
  252. query.addBindValue(maxCount);
  253. if (query.exec()) {
  254. while (query.next()) {
  255. int id = query.value(0).toInt();
  256. QString path = query.value(1).toString();
  257. QString parent = query.value(2).toString();
  258. // 旧记录无 parent_path 时,退化为当前文件所在目录
  259. if (parent.isEmpty()) {
  260. parent = QFileInfo(path).absolutePath();
  261. }
  262. list.append({id,{path,parent}});
  263. }
  264. }
  265. return list;
  266. }
  267. //删除指定ID的数据记录
  268. bool OfflineStorage::markJsonUploaded(int id)
  269. {
  270. QSqlQuery query(m_db);
  271. query.prepare("DELETE FROM json_messages WHERE id = ?");
  272. query.addBindValue(id);
  273. return query.exec();
  274. }
  275. //删除指定路径的数据记录
  276. bool OfflineStorage::markImageUploaded(const QString &imagePath)
  277. {
  278. QSqlQuery query(m_db);
  279. // 修改点 1:SQL 条件改为根据 image_path 匹配
  280. query.prepare(R"(
  281. DELETE FROM image_paths
  282. WHERE image_path = ? -- 匹配 image_path 字段
  283. )");
  284. // 修改点 2:绑定图片路径参数
  285. query.addBindValue(imagePath);
  286. // 执行删除操作
  287. if (!query.exec()) {
  288. WARN("删除图片路径记录失败,SQL 错误:{}", query.lastError().text().toStdString());
  289. WARN("失败 SQL:{}", query.lastQuery().toStdString()); // 打印实际执行的 SQL(调试用)
  290. return false;
  291. } else {
  292. LOG("成功删除图片路径记录,路径:{},受影响行数:{}",
  293. imagePath.toStdString(), (int)query.numRowsAffected());
  294. return true;
  295. }
  296. }
  297. bool OfflineStorage::cleanupUploaded()
  298. {
  299. QSqlQuery query(m_db);
  300. bool success = true;
  301. success &= query.exec("DELETE FROM json_messages WHERE uploaded = 1");
  302. success &= query.exec("DELETE FROM image_paths WHERE uploaded = 1");
  303. return success;
  304. }
  305. // offline_storage.cpp
  306. bool OfflineStorage::enforceLimit(const QString &table, int maxCount)
  307. {
  308. int count = 0;
  309. if(table=="json_messages"){
  310. count= JsonEntries_saved();
  311. }else if(table=="image_paths"){
  312. count= ImageEntries_saved();
  313. }
  314. if (count < maxCount || count==0) {
  315. return true; // 未超限,无需处理
  316. }
  317. QSqlQuery query(m_db);
  318. // 超限了 → 删除最早的一条
  319. query.prepare(QString(R"(
  320. DELETE FROM %1
  321. WHERE id = (
  322. SELECT id FROM %1
  323. ORDER BY stamp ASC LIMIT 1
  324. )
  325. )").arg(table));
  326. if (!query.exec()) {
  327. WARN("删除最老记录失败: {} {}", table.toStdString(), query.lastError().text().toStdString());
  328. return false;
  329. }
  330. LOG("已删除最老记录 from {}, 当前数量: {}", table.toStdString(), count);
  331. return true;
  332. }
  333. void OfflineStorage::setMaxJsonEntries(int max)
  334. {
  335. if (m_maxJsonEntries != max) {
  336. m_maxJsonEntries = max;
  337. emit maxJsonEntriesChanged();
  338. // 可选:立即清理,确保不超过新上限
  339. enforceLimit("json_messages", m_maxJsonEntries);
  340. }
  341. }
  342. void OfflineStorage::setJsonCacheEnabled(bool enabled)
  343. {
  344. if (m_jsonCacheEnabled != enabled) {
  345. m_jsonCacheEnabled = enabled;
  346. emit jsonCacheEnabledChanged(enabled); // 发射信号通知状态变更
  347. }
  348. }
  349. void OfflineStorage::setImageCacheEnabled(bool enabled)
  350. {
  351. if (m_imageCacheEnabled != enabled) {
  352. m_imageCacheEnabled = enabled;
  353. emit imageCacheEnabledChanged(enabled); // 发射信号通知状态变更
  354. }
  355. }
  356. void OfflineStorage::setMaxImageEntries(int max)
  357. {
  358. if (m_maxImageEntries != max) {
  359. m_maxImageEntries = max;
  360. emit maxImageEntriesChanged();
  361. enforceLimit("image_paths", m_maxImageEntries);
  362. }
  363. }
  364. int OfflineStorage::JsonEntries_saved()
  365. {
  366. QSqlQuery query(m_db);
  367. query.prepare("SELECT COUNT(*) FROM json_messages");
  368. if (!query.exec() || !query.next()) {
  369. WARN("查询JSON数量失败: {}", query.lastError().text().toStdString());
  370. return 0;
  371. }
  372. m_JsonEntries_saved = query.value(0).toInt();
  373. return m_JsonEntries_saved;
  374. }
  375. int OfflineStorage::ImageEntries_saved()
  376. {
  377. QSqlQuery query(m_db);
  378. query.prepare("SELECT COUNT(*) FROM image_paths");
  379. if (!query.exec() || !query.next()) {
  380. WARN("查询图片数量失败: {}", query.lastError().text().toStdString());
  381. return 0;
  382. }
  383. m_ImageEntries_saved = query.value(0).toInt();
  384. return m_ImageEntries_saved;
  385. }