Rabbit Remote Control 0.1.0-bate7
Loading...
Searching...
No Matches
HistoryDatabase.cpp
1// Author: Kang Lin <kl222@126.com>
2
3#include <QSqlQuery>
4#include <QSqlError>
5#include <QStandardPaths>
6#include <QDir>
7#include <QDebug>
8#include <QLoggingCategory>
9#include <QCoreApplication>
10#include <QJsonDocument>
11#include <QJsonObject>
12#include <QJsonArray>
13#include "HistoryDatabase.h"
14
15static Q_LOGGING_CATEGORY(log, "WebBrowser.History.DB")
16
17#if HAVE_SQLITE
18#include <sqlite3.h> // 需要链接 sqlite3 库
19// SQLite 回调函数
20static void sqlTrace(void* /*data*/, const char* sql) {
21 qDebug(log) << "SQL Trace:" << sql;
22}
23
24// 启用 SQL 跟踪
25void enableSqlTrace(const QString& connectionName = QSqlDatabase::defaultConnection) {
26 QSqlDatabase db = QSqlDatabase::database(connectionName);
27
28 // 获取底层 SQLite 数据库句柄
29 QVariant v = db.driver()->handle();
30 if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
31 sqlite3* handle = *static_cast<sqlite3**>(v.data());
32 if (handle) {
33 sqlite3_trace(handle, sqlTrace, nullptr);
34 qDebug(log) << "SQLite trace enabled";
35 }
36 }
37}
38#else // HAVE_SQLITE
39void enableSqlTrace(const QString& connectionName)
40{
41}
42#endif // HAVE_SQLITE
43
44CHistoryDatabase* CHistoryDatabase::Instance(const QString &szPath)
45{
46 static CHistoryDatabase* p = nullptr;
47 if(!p) {
48 p = new CHistoryDatabase();
49 if(p) {
50 bool bRet = p->OpenSQLiteDatabase("history_connection", szPath);
51 if(!bRet) {
52 delete p;
53 p = nullptr;
54 }
55 }
56 }
57 return p;
58}
59
60CHistoryDatabase::CHistoryDatabase(QObject *parent)
61 : CDatabase(parent)
62{
63 qDebug(log) << Q_FUNC_INFO;
64}
65
66CHistoryDatabase::~CHistoryDatabase()
67{
68 qDebug(log) << Q_FUNC_INFO;
69}
70
71bool CHistoryDatabase::OnInitializeDatabase()
72{
73 bool success = false;
74 success = CDatabase::OnInitializeDatabase();
75 if(!success) return false;
76 m_UrlDB.SetDatabase(GetDatabase());
77 m_UrlDB.OnInitializeDatabase();
78 return success;
79}
80
81bool CHistoryDatabase::addHistoryEntry(const QString &url)
82{
83 if (url.isEmpty()) return false;
84
85 int nUrlId = 0;
86 // 检查 URL 是否已存在。如果不存在，则增加
87 nUrlId = m_UrlDB.GetId(url);
88 if(0 == nUrlId) {
89 nUrlId = m_UrlDB.AddUrl(url);
90 if(0 == nUrlId)
91 return false;
92 }
93
94 // 插入新记录
95 QSqlQuery query(GetDatabase());
96 query.prepare(
97 "INSERT INTO history (url, visit_time) "
98 "VALUES (:url, :visit_time)"
99 );
100 query.bindValue(":url", nUrlId);
101 QDateTime tm = QDateTime::currentDateTime();
102 query.bindValue(":visit_time", tm);
103
104 bool success = query.exec();
105 if (!success) {
106 qCritical(log) << "Failed to add history:" << query.lastError().text();
107 }
108
109 return success;
110}
111
112bool CHistoryDatabase::addHistoryEntry(const QString &url, const QString& title, const QDateTime& time)
113{
114 if (url.isEmpty()) return false;
115
116 int nUrlId = 0;
117 // 检查 URL 是否已存在。如果不存在，则增加
118 nUrlId = m_UrlDB.GetId(url);
119 if(0 == nUrlId) {
120 nUrlId = m_UrlDB.AddUrl(url, title);
121 if(0 == nUrlId)
122 return false;
123 }
124
125 // 插入新记录
126 QSqlQuery query(GetDatabase());
127 query.prepare(
128 "INSERT INTO history (url, visit_time) "
129 "VALUES (:url, :visit_time)"
130 );
131 query.bindValue(":url", nUrlId);
132 query.bindValue(":visit_time", time);
133
134 bool success = query.exec();
135 if (!success) {
136 qCritical(log) << "Failed to add history:" << query.lastError().text();
137 }
138
139 return success;
140}
141
142bool CHistoryDatabase::updateHistoryEntry(const QString& url, const QString &title, const QIcon &icon)
143{
144 if (url.isEmpty()) return false;
145
146 int nUrlId = 0;
147 nUrlId = m_UrlDB.GetId(url);
148 if(0 == nUrlId) {
149 nUrlId = m_UrlDB.AddUrl(url, title, icon);
150 return (0 < nUrlId);
151 }
152 return m_UrlDB.UpdateUrl(url, title, icon);
153}
154
155bool CHistoryDatabase::updateHistoryEntry(int id, const QString &title, const QIcon &icon)
156{
157 QSqlQuery query(GetDatabase());
158 query.prepare(
159 "SELECT url"
160 "FROM history "
161 "WHERE id=:id"
162 );
163 query.bindValue(":id", id);
164 if(query.exec() && query.next()) {
165 int urlId = query.value(0).toInt();
166 return m_UrlDB.UpdateUrl(urlId, title, icon);
167 }
168
169 return false;
170}
171
172bool CHistoryDatabase::deleteHistoryEntry(int id)
173{
174 QSqlQuery query(GetDatabase());
175 query.prepare("DELETE FROM history WHERE id = :id");
176 query.bindValue(":id", id);
177
178 return query.exec();
179}
180
181bool CHistoryDatabase::deleteHistoryEntry(const QString &url)
182{
183 int urlId = m_UrlDB.GetId(url);
184 if(0 == urlId)
185 return false;
186 QSqlQuery query(GetDatabase());
187 query.prepare("DELETE FROM history WHERE url = :url");
188 query.bindValue(":url", urlId);
189 return query.exec();
190}
191
192bool CHistoryDatabase::deleteDomainEntries(const QString &szDomain)
193{
194 if(szDomain.isEmpty()) return false;
195 auto domains = m_UrlDB.GetDomain(szDomain);
196 foreach(auto urlId, domains) {
197 QSqlQuery query(GetDatabase());
198 query.prepare("DELETE FROM history WHERE url = :url");
199 query.bindValue(":url", urlId);
200 if(!query.exec())
201 qCritical(log) << "Failed to delete domain:" << szDomain << "url id:" << urlId;
202 m_UrlDB.DeleteUrl(urlId);
203 }
204 return true;
205}
206
207bool CHistoryDatabase::clearHistory(int days)
208{
209 QSqlQuery query(GetDatabase());
210
211 if (days > 0) {
212 QDateTime cutoff = QDateTime::currentDateTime().addDays(-days);
213 query.prepare("DELETE FROM history WHERE visit_time < :cutoff");
214 query.bindValue(":cutoff", cutoff);
215 } else {
216 query.prepare("DELETE FROM history");
217 }
218
219 bool success = query.exec();
220
221 if (success) {
222 // 清理后重置自增ID
223 query.exec("VACUUM");
224 }
225
226 return success;
227}
228
229void CHistoryDatabase::scheduleCleanup(int maxDays, int maxCount)
230{
231 // 按时间清理
232 if (maxDays > 0) {
233 QSqlQuery query(GetDatabase());
234 QDateTime cutoff = QDateTime::currentDateTime().addDays(-maxDays);
235 query.prepare("DELETE FROM history WHERE visit_time < :cutoff");
236 query.bindValue(":cutoff", cutoff);
237 query.exec();
238 }
239
240 // 按数量清理
241 if (maxCount > 0) {
242 QSqlQuery query(GetDatabase());
243 query.prepare(
244 "DELETE FROM history WHERE id IN ("
245 " SELECT id FROM history "
246 " ORDER BY visit_time DESC "
247 " LIMIT -1 OFFSET :maxCount"
248 ")"
249 );
250 query.bindValue(":maxCount", maxCount);
251 query.exec();
252 }
253}
254
255QList<HistoryItem> CHistoryDatabase::getAllHistory(int limit, int offset)
256{
257 QList<HistoryItem> historyList;
258
259 QSqlQuery query(GetDatabase());
260 if(0 > limit) {
261 query.prepare(
262 "SELECT id, url, visit_time "
263 "FROM history "
264 "ORDER BY visit_time DESC "
265 );
266 } else {
267 query.prepare(
268 "SELECT id, url, visit_time "
269 "FROM history "
270 "ORDER BY visit_time DESC "
271 "LIMIT :limit OFFSET :offset"
272 );
273 query.bindValue(":limit", limit);
274 query.bindValue(":offset", offset);
275 }
276 if (query.exec()) {
277 while (query.next()) {
278 HistoryItem item;
279 item.id = query.value(0).toInt();
280 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
281 item.url = urlItem.szUrl;
282 item.title = urlItem.szTitle;
283 item.visitTime = query.value(2).toDateTime();
284 item.icon = urlItem.icon;
285 historyList.append(item);
286 }
287 } else {
288 qCritical(log) << "Failed to get all history:" << query.lastError().text();
289 }
290
291 return historyList;
292}
293
294QList<HistoryItem> CHistoryDatabase::getHistoryByDate(const QDate &date)
295{
296 QList<HistoryItem> historyList;
297
298 QSqlQuery query(GetDatabase());
299 query.prepare(
300 "SELECT id, url, visit_time "
301 "FROM history "
302 "WHERE date(visit_time) = date(:date) "
303 "ORDER BY visit_time DESC"
304 );
305 query.bindValue(":date", date.toString("yyyy-MM-dd"));
306
307 if (query.exec()) {
308 while (query.next()) {
309 HistoryItem item;
310 item.id = query.value(0).toInt();
311 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
312 item.url = urlItem.szUrl;
313 item.title = urlItem.szTitle;
314 item.visitTime = query.value(2).toDateTime();
315 item.icon = urlItem.icon;
316 historyList.append(item);
317 }
318 }
319
320 return historyList;
321}
322
323QList<HistoryItem> CHistoryDatabase::getHistoryByDate(
324 const QDate &start, const QDate &end, int limit)
325{
326 QList<HistoryItem> historyList;
327
328 QSqlQuery query(GetDatabase());
329 query.prepare(
330 "SELECT id, url, visit_time "
331 "FROM history "
332 "WHERE date(visit_time) >= date(:start) AND date(visit_time) <= date(:end) "
333 "ORDER BY visit_time DESC "
334 "LIMIT :limit"
335 );
336 query.bindValue(":start", start.toString("yyyy-MM-dd"));
337 query.bindValue(":end", end.toString("yyyy-MM-dd"));
338 query.bindValue(":limit", limit);
339
340 if (query.exec()) {
341 while (query.next()) {
342 HistoryItem item;
343 item.id = query.value(0).toInt();
344 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
345 item.url = urlItem.szUrl;
346 item.title = urlItem.szTitle;
347 item.visitTime = query.value(2).toDateTime();
348 item.icon = urlItem.icon;
349 historyList.append(item);
350 }
351 }
352
353 return historyList;
354}
355
356QList<HistoryItem> CHistoryDatabase::searchHistory(const QString &keyword)
357{
358 auto items = m_UrlDB.Search(keyword);
359 QList<HistoryItem> retItems;
360 foreach (auto i, items) {
361 HistoryItem item;
362 item.icon = i.icon;
363 item.title = i.szTitle;
364 item.url = i.szUrl;
365 retItems << item;
366 }
367 return retItems;
368}
369
370HistoryItem CHistoryDatabase::getHistoryByUrl(const QString &url)
371{
372 HistoryItem item;
373
374 if(url.isEmpty()) return item;
375 auto urlItem = m_UrlDB.GetItem(url);
376 if(0 >= urlItem.id)
377 return item;
378
379 QSqlQuery query(GetDatabase());
380 query.prepare(
381 "SELECT id, visit_time "
382 "FROM history WHERE url = :url"
383 );
384 query.bindValue(":url", urlItem.id);
385
386 if (query.exec() && query.next()) {
387 item.id = query.value(0).toInt();
388 item.url = urlItem.szUrl;
389 item.title = urlItem.szTitle;
390 item.icon = urlItem.icon;
391 item.visitTime = query.value(1).toDateTime();
392 }
393
394 return item;
395}
396
397HistoryItem CHistoryDatabase::getHistoryById(int id)
398{
399 HistoryItem item;
400
401 QSqlQuery query(GetDatabase());
402 query.prepare(
403 "SELECT id, url, visit_time "
404 "FROM history WHERE id = :id"
405 );
406 query.bindValue(":id", id);
407
408 if (query.exec() && query.next()) {
409 item.id = query.value(0).toInt();
410 int urlId = query.value(1).toInt();
411 auto urlItem = m_UrlDB.GetItem(urlId);
412 item.url = urlItem.szUrl;
413 item.title = urlItem.szTitle;
414 item.icon = urlItem.icon;
415 item.visitTime = query.value(2).toDateTime();
416 }
417
418 return item;
419}
420
421int CHistoryDatabase::getHistoryCount()
422{
423 QSqlQuery query(GetDatabase());
424 query.exec("SELECT COUNT(*) FROM history");
425
426 if (query.next()) {
427 return query.value(0).toInt();
428 }
429
430 return 0;
431}
432
433QDateTime CHistoryDatabase::getLastVisitTime()
434{
435 QSqlQuery query(GetDatabase());
436 query.exec("SELECT MAX(visit_time) FROM history");
437
438 if (query.next()) {
439 return query.value(0).toDateTime();
440 }
441
442 return QDateTime();
443}
444
445bool CHistoryDatabase::importFromJson(const QString &filename)
446{
447 QFile file(filename);
448 if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
449 return false;
450
451 do {
452 QJsonDocument doc;
453 doc = QJsonDocument::fromJson(file.readAll());
454 if(!doc.isArray())
455 break;
456 auto array = doc.array();
457 for(auto it = array.begin(); it != array.end(); it++) {
458 auto o = it->toObject();
459 QString url = o["url"].toString();
460 QString title = o["title"].toString();
461 QDateTime time = QDateTime::fromString(o["visit_time"].toString());
462 qDebug(log) << "title:" << title << "url:" << url << "visit_time:" << time;
463 bool ok = addHistoryEntry(url, title, time);
464 if(!ok)
465 qWarning(log) << "Failed to add history:" << o["title"].toString();
466 }
467 } while(0);
468
469 file.close();
470 return true;
471}
472
473bool CHistoryDatabase::exportToJson(const QString &filename)
474{
475 QFile file(filename);
476 if (!file.open(QIODevice::WriteOnly | QIODevice::Text))
477 return false;
478
479 QTextStream out(&file);
480#if QT_VERSION >= QT_VERSION_CHECK(6, 0, 0)
481 out.setEncoding(QStringConverter::Utf8);
482#else
483 out.setCodec("UTF-8");
484#endif
485 out.setGenerateByteOrderMark(true); // 添加 UTF-8 BOM
486
487 QJsonDocument doc;
488 QJsonArray list;
489 auto items = getAllHistory();
490 foreach(auto it, items) {
491 QJsonObject title;
492 title.insert("title", it.title);
493 title.insert("url", it.url);
494 title.insert("visit_time", it.visitTime.toString());
495 list.append(title);
496 }
497 doc.setArray(list);
498 out << doc.toJson();
499
500 file.close();
501 return true;
502}
503
504bool CHistoryDatabase::importFromCSV(const QString &filename)
505{
506 QFile file(filename);
507 if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
508 qCritical(log) << "Failed to open file" << filename;
509 return false;
510 }
511
512 QTextStream in(&file);
513#if QT_VERSION >= QT_VERSION_CHECK(6, 0, 0)
514 in.setEncoding(QStringConverter::Utf8);
515#else
516 in.setCodec("UTF-8");
517#endif
518 in.setGenerateByteOrderMark(true); // 添加 UTF-8 BOM
519 int importedCount = 0;
520 int lineNumber = 0;
521
522 // 开始事务
523 GetDatabase().transaction();
524
525 try {
526 while (!in.atEnd()) {
527 QString line = in.readLine().trimmed();
528 lineNumber++;
529
530 // 跳过空行和注释
531 if (line.isEmpty() || line.startsWith("#")) {
532 continue;
533 }
534
535 // 跳过表头（第一行）
536 if (lineNumber == 1) {
537 // 验证表头格式
538 if (!validateCsvHeader(line)) {
539 throw QString("Invalid CSV header format");
540 }
541 continue;
542 }
543
544 // 解析 CSV 行
545 QStringList fields = parseCsvLine(line);
546
547 if (fields.size() >= 3) {
548 if (importCsvRecord(fields)) {
549 importedCount++;
550 }
551 } else {
552 qWarning() << "Invalid CSV line" << lineNumber << ":" << line;
553 }
554 }
555
556 file.close();
557
558 if (importedCount == 0) {
559 throw QString("No valid records found in CSV file");
560 }
561
562 if (!GetDatabase().commit()) {
563 throw QString("Failed to commit transaction: %1").arg(GetDatabase().lastError().text());
564 }
565
566 qDebug(log) << "Successfully imported" << importedCount << "records from CSV file";
567 return true;
568 } catch (const QString &error) {
569 GetDatabase().rollback();
570 file.close();
571 qCritical(log) << "CSV import failed at line" << lineNumber << ":" << error;
572 return false;
573 }
574 return false;
575}
576
577bool CHistoryDatabase::exportToCSV(const QString &filename)
578{
579 QFile file(filename);
580 if (!file.open(QIODevice::WriteOnly | QIODevice::Text))
581 return false;
582
583 QTextStream out(&file);
584#if QT_VERSION >= QT_VERSION_CHECK(6, 0, 0)
585 out.setEncoding(QStringConverter::Utf8);
586#else
587 out.setCodec("UTF-8");
588#endif
589 out.setGenerateByteOrderMark(true); // 添加 UTF-8 BOM
590
591 // 写入表头
592 const QStringList headers = {
593 "Title", "URL", "Visit Time"
594 };
595 out << headers.join(",") << "\n";
596
597 auto items = getAllHistory();
598 foreach(auto it, items) {
599 QStringList row;
600 row << escapeForCsv(it.title);
601 row << escapeForCsv(it.url);
602 row << it.visitTime.toString();
603 out << row.join(",") << "\n";
604 }
605
606 file.close();
607 return true;
608}
609
610QString CHistoryDatabase::escapeForCsv(const QString &text)
611{
612 if (text.isEmpty())
613 return "\"\"";
614
615 // 判断是否需要引号
616 bool needQuotes = text.contains(',') ||
617 text.contains('"') ||
618 text.contains('\n') ||
619 text.contains('\r') ||
620 text.contains('\t') ||
621 text.startsWith(' ') ||
622 text.endsWith(' ') ||
623 text.startsWith('\t') ||
624 text.endsWith('\t');
625
626 if (!needQuotes)
627 return text;
628
629 // 转义双引号
630 QString escaped = text;
631 escaped.replace("\"", "\"\"");
632
633 return "\"" + escaped + "\"";
634}
635
636QString CHistoryDatabase::unescapeCsvField(const QString &field)
637{
638 if (field.isEmpty()) {
639 return QString();
640 }
641
642 QString unescaped = field;
643
644 // 去除包围的引号
645 if (unescaped.startsWith('"') && unescaped.endsWith('"')) {
646 unescaped = unescaped.mid(1, unescaped.length() - 2);
647 }
648
649 // 反转义双引号
650 unescaped.replace("\"\"", "\"");
651
652 return unescaped.trimmed();
653}
654
655QStringList CHistoryDatabase::parseCsvLine(const QString &line)
656{
657 QStringList fields;
658 QString field;
659 bool inQuotes = false;
660
661 for (int i = 0; i < line.length(); ++i) {
662 QChar ch = line[i];
663
664 if (ch == '"') {
665 // 处理转义的双引号
666 if (i + 1 < line.length() && line[i + 1] == '"') {
667 field += '"';
668 i++; // 跳过下一个引号
669 } else {
670 inQuotes = !inQuotes;
671 }
672 } else if (ch == ',' && !inQuotes) {
673 fields.append(field.trimmed());
674 field.clear();
675 } else {
676 field += ch;
677 }
678 }
679
680 // 添加最后一个字段
681 if (!field.isEmpty()) {
682 fields.append(field.trimmed());
683 }
684
685 return fields;
686}
687
688bool CHistoryDatabase::validateCsvHeader(const QString &headerLine)
689{
690 QStringList headers = parseCsvLine(headerLine);
691
692 // 检查必需的表头
693 if (headers.size() < 3) {
694 return false;
695 }
696
697 // 检查关键字段
698 QStringList requiredHeaders = {"Title", "URL", "Visit Time"};
699 for (const QString &required : requiredHeaders) {
700 if (!headers.contains(required, Qt::CaseInsensitive)) {
701 qWarning(log) << "Missing required header:" << required;
702 return false;
703 }
704 }
705
706 return true;
707}
708
709bool CHistoryDatabase::importCsvRecord(const QStringList &fields)
710{
711 if (fields.size() < 3) {
712 return false;
713 }
714
715 HistoryItem item;
716
717 // 解析字段
718 // 字段顺序：Title, URL, Visit Time
719
720 // Title（可选）
721 item.title = fields[0];
722 // URL（必需）
723 item.url = fields[1];
724
725 // Visit Time（必需）
726 item.visitTime = QDateTime::fromString(fields[2]);
727 if (!item.visitTime.isValid()) {
728 // 尝试其他格式
729 item.visitTime = QDateTime::fromString(fields[2]);
730 if (!item.visitTime.isValid()) {
731 item.visitTime = QDateTime::currentDateTime();
732 }
733 }
734
735 return addHistoryEntry(item.url, item.title, item.visitTime);
736}
737
738bool CHistoryDatabase::ExportToJson(QJsonObject &obj)
739{
740 return true;
741}
742
743bool CHistoryDatabase::ImportFromJson(const QJsonObject &obj)
744{
745 return true;
746}
747
748bool CHistoryDatabase::OnInitializeSqliteDatabase()
749{
750 QSqlQuery query(GetDatabase());
751
752 // 创建历史记录表
753 bool success = query.exec(
754 "CREATE TABLE IF NOT EXISTS history ("
755 " id INTEGER PRIMARY KEY AUTOINCREMENT,"
756 " url INTEGER NOT NULL,"
757 " visit_time DATETIME DEFAULT CURRENT_TIMESTAMP"
758 ")"
759 );
760
761 if (!success) {
762 qCritical(log) << "Failed to create table:" << query.lastError().text();
763 return false;
764 }
765
766 // 创建索引
767 success = query.exec("CREATE INDEX IF NOT EXISTS idx_history_url ON history(url)");
768 if (!success) {
769 qWarning(log) << "Failed to create index idx_history_url:"
770 << query.lastError().text()
771 << query.executedQuery();
772 }
773 success = query.exec("CREATE INDEX IF NOT EXISTS idx_history_time ON history(visit_time)");
774 if (!success) {
775 qWarning(log) << "Failed to create index idx_history_time:"
776 << query.lastError().text()
777 << query.executedQuery();
778 }
779
780 return true;
781}
782
783bool CHistoryDatabase::OnInitializeMySqlDatabase()
784{
785 QSqlQuery query(GetDatabase());
786
787 // 创建历史记录表
788 bool success = query.exec(
789 "CREATE TABLE IF NOT EXISTS history ("
790 " id INTEGER PRIMARY KEY AUTO_INCREMENT,"
791 " url INTEGER NOT NULL,"
792 " visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
793 " INDEX idx_history_url (url),"
794 " INDEX idx_history_time (visit_time)"
795 ")"
796 );
797
798 if (!success) {
799 qCritical(log) << "Failed to create table:" << query.lastError().text();
800 return false;
801 }
802
803 return true;
804}
Author: Kang Lin (kl222@126.com)

Copyright (c) Kang Lin Studios All Rights Reserved