Rabbit Remote Control 0.1.0-bate8
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(QObject *parent)
45 : CDatabase(parent)
46{
47 qDebug(log) << Q_FUNC_INFO;
48}
49
50CHistoryDatabase::~CHistoryDatabase()
51{
52 qDebug(log) << Q_FUNC_INFO;
53}
54
55bool CHistoryDatabase::OnInitializeDatabase()
56{
57 bool success = false;
58 success = CDatabase::OnInitializeDatabase();
59 if(!success) return false;
60 success = m_UrlDB.SetDatabase(GetDatabase(), m_pPara);
61 return success;
62}
63
64bool CHistoryDatabase::addHistoryEntry(const QString &url)
65{
66 if (url.isEmpty()) return false;
67
68 int nUrlId = 0;
69 // 检查 URL 是否已存在。如果不存在，则增加
70 nUrlId = m_UrlDB.GetId(url);
71 if(0 == nUrlId) {
72 nUrlId = m_UrlDB.AddUrl(url);
73 if(0 == nUrlId)
74 return false;
75 }
76
77 // 插入新记录
78 QSqlQuery query(GetDatabase());
79 query.prepare(
80 "INSERT INTO history (url, visit_time) "
81 "VALUES (:url, :visit_time) "
82 );
83 query.bindValue(":url", nUrlId);
84 QDateTime tm = QDateTime::currentDateTime();
85 query.bindValue(":visit_time", tm);
86
87 bool success = query.exec();
88 if (!success) {
89 QString szErr = "Failed to add history: " + query.lastError().text()
90 + "; Sql: " + query.executedQuery()
91 + "; url: " + url;
92 SetError(szErr);
93 qCritical(log) << GetError();
94 }
95
96 return success;
97}
98
99bool CHistoryDatabase::addHistoryEntry(const QString &url, const QString& title, const QDateTime& time)
100{
101 if (url.isEmpty()) return false;
102
103 int nUrlId = 0;
104 // 检查 URL 是否已存在。如果不存在，则增加
105 nUrlId = m_UrlDB.GetId(url);
106 if(0 == nUrlId) {
107 nUrlId = m_UrlDB.AddUrl(url, title);
108 if(0 == nUrlId)
109 return false;
110 }
111
112 // 插入新记录
113 QSqlQuery query(GetDatabase());
114 query.prepare(
115 "INSERT INTO history (url, visit_time) "
116 "VALUES (:url, :visit_time) "
117 );
118 query.bindValue(":url", nUrlId);
119 query.bindValue(":visit_time", time);
120
121 bool success = query.exec();
122 if (!success) {
123 QString szErr = "Failed to add history: " + query.lastError().text()
124 + "; Sql: " + query.executedQuery()
125 + "; url: " + url;
126 SetError(szErr);
127 qCritical(log) << GetError();
128 }
129
130 return success;
131}
132
133bool CHistoryDatabase::updateHistoryEntry(const QString& url, const QString &title, const QIcon &icon)
134{
135 if (url.isEmpty()) return false;
136
137 int nUrlId = 0;
138 nUrlId = m_UrlDB.GetId(url);
139 if(0 == nUrlId) {
140 nUrlId = m_UrlDB.AddUrl(url, title, icon);
141 return (0 < nUrlId);
142 }
143 return m_UrlDB.UpdateUrl(url, title, icon);
144}
145
146bool CHistoryDatabase::updateHistoryEntry(int id, const QString &title, const QIcon &icon)
147{
148 QSqlQuery query(GetDatabase());
149 query.prepare(
150 "SELECT url"
151 "FROM history "
152 "WHERE id=:id"
153 );
154 query.bindValue(":id", id);
155 if(!query.exec()) {
156 QString szErr = "Failed to update history: " + query.lastError().text()
157 + "; Sql: " + query.executedQuery()
158 + "; id: " + QString::number(id);
159 SetError(szErr);
160 qCritical(log) << GetError();
161 return false;
162 }
163
164 if(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 bool bRet = query.exec();
179 if(!bRet) {
180 QString szErr = "Failed to delete history: " + query.lastError().text()
181 + "; Sql: " + query.executedQuery()
182 + "; id: " + QString::number(id);
183 SetError(szErr);
184 qCritical(log) << GetError();
185 }
186 return bRet;
187}
188
189bool CHistoryDatabase::deleteHistoryEntry(const QString &url)
190{
191 int urlId = m_UrlDB.GetId(url);
192 if(0 == urlId)
193 return false;
194 QSqlQuery query(GetDatabase());
195 query.prepare("DELETE FROM history WHERE url = :url");
196 query.bindValue(":url", urlId);
197 bool bRet = query.exec();
198 if(!bRet) {
199 QString szErr = "Failed to delete history: " + query.lastError().text()
200 + "; Sql: " + query.executedQuery()
201 + "; url: " + url;
202 SetError(szErr);
203 qCritical(log) << GetError();
204 }
205 return bRet;
206}
207
208bool CHistoryDatabase::deleteDomainEntries(const QString &szDomain)
209{
210 if(szDomain.isEmpty()) return false;
211 auto domains = m_UrlDB.GetDomain(szDomain);
212 foreach(auto urlId, domains) {
213 QSqlQuery query(GetDatabase());
214 query.prepare("DELETE FROM history WHERE url = :url");
215 query.bindValue(":url", urlId);
216 if(!query.exec()) {
217 QString szErr = "Failed to delete domain: " + szDomain
218 + "; Error: " + query.lastError().text()
219 + "; Sql: " + query.executedQuery()
220 + "; url id: " + QString::number(urlId);
221 SetError(szErr);
222 qCritical(log) << GetError();
223 }
224 m_UrlDB.DeleteUrl(urlId);
225 }
226 return true;
227}
228
229bool CHistoryDatabase::clearHistory(int days)
230{
231 QSqlQuery query(GetDatabase());
232
233 if (days > 0) {
234 QDateTime cutoff = QDateTime::currentDateTime().addDays(-days);
235 query.prepare("DELETE FROM history WHERE visit_time < :cutoff");
236 query.bindValue(":cutoff", cutoff);
237 } else {
238 query.prepare("DELETE FROM history");
239 }
240
241 bool success = query.exec();
242 if (success) {
243 // 清理后重置自增ID
244 query.exec("VACUUM");
245 } else {
246 QString szErr = "Failed to clear history: " + query.lastError().text()
247 + "; Sql: " + query.executedQuery();
248 SetError(szErr);
249 qCritical(log) << GetError();
250 }
251
252 return success;
253}
254
255void CHistoryDatabase::scheduleCleanup(int maxDays, int maxCount)
256{
257 // 按时间清理
258 if (maxDays > 0) {
259 QSqlQuery query(GetDatabase());
260 QDateTime cutoff = QDateTime::currentDateTime().addDays(-maxDays);
261 query.prepare("DELETE FROM history WHERE visit_time < :cutoff");
262 query.bindValue(":cutoff", cutoff);
263 query.exec();
264 }
265
266 // 按数量清理
267 if (maxCount > 0) {
268 QSqlQuery query(GetDatabase());
269 query.prepare(
270 "DELETE FROM history WHERE id IN ("
271 " SELECT id FROM history "
272 " ORDER BY visit_time DESC "
273 " LIMIT -1 OFFSET :maxCount"
274 ")"
275 );
276 query.bindValue(":maxCount", maxCount);
277 query.exec();
278 }
279}
280
281QList<HistoryItem> CHistoryDatabase::getAllHistory(int limit, int offset)
282{
283 QList<HistoryItem> historyList;
284
285 QSqlQuery query(GetDatabase());
286 if(0 > limit) {
287 query.prepare(
288 "SELECT id, url, visit_time "
289 "FROM history "
290 "ORDER BY visit_time DESC "
291 );
292 } else {
293 query.prepare(
294 "SELECT id, url, visit_time "
295 "FROM history "
296 "ORDER BY visit_time DESC "
297 "LIMIT :limit OFFSET :offset"
298 );
299 query.bindValue(":limit", limit);
300 query.bindValue(":offset", offset);
301 }
302 if (query.exec()) {
303 while (query.next()) {
304 HistoryItem item;
305 item.id = query.value(0).toInt();
306 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
307 item.url = urlItem.szUrl;
308 item.title = urlItem.szTitle;
309 item.visitTime = query.value(2).toDateTime();
310 item.icon = urlItem.icon;
311 historyList.append(item);
312 }
313 } else {
314 QString szErr = "Failed to get all history:" + query.lastError().text()
315 + "; Sql: " + query.executedQuery();
316 SetError(szErr);
317 qCritical(log) << GetError();
318 }
319
320 return historyList;
321}
322
323QList<HistoryItem> CHistoryDatabase::getHistoryByDate(const QDate &date)
324{
325 QList<HistoryItem> historyList;
326
327 QSqlQuery query(GetDatabase());
328 query.prepare(
329 "SELECT id, url, visit_time "
330 "FROM history "
331 "WHERE date(visit_time) = date(:date) "
332 "ORDER BY visit_time DESC"
333 );
334 query.bindValue(":date", date.toString("yyyy-MM-dd"));
335
336 if (query.exec()) {
337 while (query.next()) {
338 HistoryItem item;
339 item.id = query.value(0).toInt();
340 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
341 item.url = urlItem.szUrl;
342 item.title = urlItem.szTitle;
343 item.visitTime = query.value(2).toDateTime();
344 item.icon = urlItem.icon;
345 historyList.append(item);
346 }
347 } else {
348 QString szErr = "Failed to get history by date:" + query.lastError().text()
349 + "; Sql: " + query.executedQuery();
350 SetError(szErr);
351 qCritical(log) << GetError();
352 }
353
354 return historyList;
355}
356
357QList<HistoryItem> CHistoryDatabase::getHistoryByDate(
358 const QDate &start, const QDate &end, int limit)
359{
360 QList<HistoryItem> historyList;
361
362 QSqlQuery query(GetDatabase());
363 query.prepare(
364 "SELECT id, url, visit_time "
365 "FROM history "
366 "WHERE date(visit_time) >= date(:start) AND date(visit_time) <= date(:end) "
367 "ORDER BY visit_time DESC "
368 "LIMIT :limit"
369 );
370 query.bindValue(":start", start.toString("yyyy-MM-dd"));
371 query.bindValue(":end", end.toString("yyyy-MM-dd"));
372 query.bindValue(":limit", limit);
373
374 if (query.exec()) {
375 while (query.next()) {
376 HistoryItem item;
377 item.id = query.value(0).toInt();
378 CDatabaseUrl::UrlItem urlItem = m_UrlDB.GetItem(query.value(1).toInt());
379 item.url = urlItem.szUrl;
380 item.title = urlItem.szTitle;
381 item.visitTime = query.value(2).toDateTime();
382 item.icon = urlItem.icon;
383 historyList.append(item);
384 }
385 } else {
386 QString szErr = "Failed to get history by date:" + query.lastError().text()
387 + "; Sql: " + query.executedQuery();
388 SetError(szErr);
389 qCritical(log) << GetError();
390 }
391
392 return historyList;
393}
394
395QList<HistoryItem> CHistoryDatabase::searchHistory(const QString &keyword)
396{
397 auto items = m_UrlDB.Search(keyword);
398 QList<HistoryItem> retItems;
399 foreach (auto i, items) {
400 HistoryItem item;
401 item.icon = i.icon;
402 item.title = i.szTitle;
403 item.url = i.szUrl;
404 retItems << item;
405 }
406 return retItems;
407}
408
409HistoryItem CHistoryDatabase::getHistoryByUrl(const QString &url)
410{
411 HistoryItem item;
412
413 if(url.isEmpty()) return item;
414 auto urlItem = m_UrlDB.GetItem(url);
415 if(0 >= urlItem.id)
416 return item;
417
418 QSqlQuery query(GetDatabase());
419 query.prepare(
420 "SELECT id, visit_time "
421 "FROM history WHERE url = :url"
422 );
423 query.bindValue(":url", urlItem.id);
424
425 if (query.exec()) {
426 if(query.next()) {
427 item.id = query.value(0).toInt();
428 item.url = urlItem.szUrl;
429 item.title = urlItem.szTitle;
430 item.icon = urlItem.icon;
431 item.visitTime = query.value(1).toDateTime();
432 }
433 } else {
434 QString szErr = "Failed to get history by url:" + query.lastError().text()
435 + "; Sql: " + query.executedQuery()
436 + "; Url: " + url;
437 SetError(szErr);
438 qCritical(log) << GetError();
439 }
440
441 return item;
442}
443
444HistoryItem CHistoryDatabase::getHistoryById(int id)
445{
446 HistoryItem item;
447
448 QSqlQuery query(GetDatabase());
449 query.prepare(
450 "SELECT id, url, visit_time "
451 "FROM history WHERE id = :id"
452 );
453 query.bindValue(":id", id);
454
455 if (query.exec()) {
456 if(query.next()) {
457 item.id = query.value(0).toInt();
458 int urlId = query.value(1).toInt();
459 auto urlItem = m_UrlDB.GetItem(urlId);
460 item.url = urlItem.szUrl;
461 item.title = urlItem.szTitle;
462 item.icon = urlItem.icon;
463 item.visitTime = query.value(2).toDateTime();
464 }
465 } else {
466 QString szErr = "Failed to get history by id:" + query.lastError().text()
467 + "; Sql: " + query.executedQuery()
468 + "; id: " + QString::number(id);
469 SetError(szErr);
470 qCritical(log) << GetError();
471 }
472
473 return item;
474}
475
476int CHistoryDatabase::getHistoryCount()
477{
478 QSqlQuery query(GetDatabase());
479 query.exec("SELECT COUNT(*) FROM history");
480
481 if (query.next()) {
482 return query.value(0).toInt();
483 } else {
484 QString szErr = "Failed to get history count:" + query.lastError().text()
485 + "; Sql: " + query.executedQuery();
486 SetError(szErr);
487 qCritical(log) << GetError();
488 }
489
490 return 0;
491}
492
493QDateTime CHistoryDatabase::getLastVisitTime()
494{
495 QSqlQuery query(GetDatabase());
496 query.exec("SELECT MAX(visit_time) FROM history");
497
498 if (query.next()) {
499 return query.value(0).toDateTime();
500 } else {
501 QString szErr = "Failed to get last visit time:" + query.lastError().text()
502 + "; Sql: " + query.executedQuery();
503 SetError(szErr);
504 qCritical(log) << GetError();
505 }
506
507 return QDateTime();
508}
509
510bool CHistoryDatabase::ExportToJson(QJsonObject &obj)
511{
512 QJsonArray list;
513 auto items = getAllHistory();
514 foreach(auto it, items) {
515 QJsonObject title;
516 title.insert("title", it.title);
517 title.insert("url", it.url);
518 title.insert("visit_time", it.visitTime.toString());
519 list.append(title);
520 }
521 obj.insert("browser_history", list);
522 return true;
523}
524
525bool CHistoryDatabase::ImportFromJson(const QJsonObject &obj)
526{
527 auto array = obj["browser_history"].toArray();
528 if(array.isEmpty()) {
529 SetError(tr("The file format is error. Json without \"browser_history\""));
530 qCritical(log) << GetError();
531 return false;
532 }
533 for(auto it = array.begin(); it != array.end(); it++) {
534 auto o = it->toObject();
535 QString url = o["url"].toString();
536 QString title = o["title"].toString();
537 QDateTime time = QDateTime::fromString(o["visit_time"].toString());
538 qDebug(log) << "title:" << title << "url:" << url << "visit_time:" << time;
539 bool ok = addHistoryEntry(url, title, time);
540 if(!ok)
541 qWarning(log) << "Failed to add history:" << o["title"].toString();
542 }
543 return true;
544}
545
546bool CHistoryDatabase::OnInitializeSqliteDatabase()
547{
548 QSqlQuery query(GetDatabase());
549
550 // 创建历史记录表
551 query.prepare(
552 "CREATE TABLE IF NOT EXISTS history ("
553 " id INTEGER PRIMARY KEY AUTOINCREMENT,"
554 " url INTEGER NOT NULL,"
555 " visit_time DATETIME DEFAULT CURRENT_TIMESTAMP"
556 ")"
557 );
558 bool success = query.exec();
559
560 if (!success) {
561 QString szErr = "Failed to create history sqlite table:"
562 + query.lastError().text()
563 + "; Sql: " + query.executedQuery();
564 SetError(szErr);
565 qCritical(log) << GetError();
566 return false;
567 }
568
569 // 创建索引
570 query.prepare("CREATE INDEX IF NOT EXISTS idx_history_url ON history(url)");
571 success = query.exec();
572 if (!success) {
573 qWarning(log) << "Failed to create index idx_history_url:"
574 << query.lastError().text()
575 << query.executedQuery();
576 }
577 query.prepare("CREATE INDEX IF NOT EXISTS idx_history_time ON history(visit_time)");
578 success = query.exec();
579 if (!success) {
580 qWarning(log) << "Failed to create index idx_history_time:"
581 << query.lastError().text()
582 << query.executedQuery();
583 }
584
585 return true;
586}
587
588bool CHistoryDatabase::OnInitializeMySqlDatabase()
589{
590 QSqlQuery query(GetDatabase());
591 query.prepare(
592 "CREATE TABLE IF NOT EXISTS history ("
593 " id INTEGER PRIMARY KEY AUTO_INCREMENT,"
594 " url INTEGER NOT NULL,"
595 " visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
596 " INDEX idx_history_url (url),"
597 " INDEX idx_history_time (visit_time)"
598 ")"
599 );
600 bool success = query.exec();
601 if (!success) {
602 QString szErr = "Failed to create history mysql table:"
603 + query.lastError().text()
604 + "; Sql: " + query.executedQuery();
605 SetError(szErr);
606 qCritical(log) << GetError();
607 }
608 return success;
609}
CDatabase
Provide interfaces such as opening the database and initializing the database.
Definition Database.h:21
CDatabase::OnInitializeDatabase
virtual bool OnInitializeDatabase()
Initialize database.
Definition Database.cpp:288
CDatabase::SetDatabase
bool SetDatabase(const CDatabase *db)
Share an existing database.
Definition Database.cpp:34
CHistoryDatabase::OnInitializeDatabase
bool OnInitializeDatabase() override
Initialize database.
Definition HistoryDatabase.cpp:55
Author: Kang Lin (kl222@126.com)

Copyright (c) Kang Lin Studios All Rights Reserved