Debian Codegraph @

src/album/dbmanager/dbmanager.cpp

raw
1 /*
2 * Copyright (C) 2020 ~ 2021 Uniontech Software Technology Co., Ltd.
3 *
4 * Author: ZhangYong <[email protected]>
5 *
6 * Maintainer: ZhangYong <[email protected]>
7 *
8 * This program is free software: you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation, either version 3 of the License, or
11 * any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <http://www.gnu.org/licenses/>.
20 */
21 #include "dbmanager.h"
22 #include "application.h"
23 #include "controller/signalmanager.h"
24 #include "utils/baseutils.h"
25 #include "albumgloabl.h"
26
27 #include <QDebug>
28 #include <QDir>
29 #include <QMutex>
30 #include <QSqlDatabase>
31 #include <QSqlError>
32 #include <QSqlQuery>
33 #include <QStandardPaths>
34
35 #include "imageengineapi.h"
36
37 namespace {
38 const QString DATABASE_PATH = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)
39 + QDir::separator() + "deepin" + QDir::separator() + "deepin-album" + QDir::separator();
40 const QString DATABASE_NAME = "deepinalbum.db";
41 const QString EMPTY_HASH_STR = utils::base::hashByString(QString(" "));
42
43 } // namespace
44
45 DBManager *DBManager::m_dbManager = nullptr;
46
47 DBManager *DBManager::instance()
48 {
49 if (!m_dbManager) {
50 m_dbManager = new DBManager();
51 }
52 return m_dbManager;
53 }
54
55 DBManager::DBManager(QObject *parent)
56 : QObject(parent)
57 , m_db(QSqlDatabase::addDatabase("QSQLITE", "album_sql_connect"))
58 {
59 m_db.setDatabaseName(DATABASE_PATH + DATABASE_NAME);
60 checkDatabase();
61 }
62
63 const QStringList DBManager::getAllPaths() const
64 {
65 QMutexLocker mutex(&m_mutex);
66 QStringList paths;
67 QSqlDatabase db = getDatabase();
68 if (! db.isValid())
69 return paths;
70
71 QSqlQuery query(db);
72 query.setForwardOnly(true);
73 bool b = query.prepare("SELECT "
74 "FilePath "
75 "FROM ImageTable3"/* ORDER BY Time DESC"*/);
76 if (!b || ! query.exec()) {
77 db.close();
78 return paths;
79 } else {
80 while (query.next()) {
81 paths << query.value(0).toString();
82 }
83 }
84 // 连接使用完后需要释放回数据库连接池
85 db.close();
86 return paths;
87 }
88
89 const DBImgInfoList DBManager::getAllInfos(int loadCount)const
90 {
91 QMutexLocker mutex(&m_mutex);
92 DBImgInfoList infos;
93 QSqlDatabase db = getDatabase();
94 if (! db.isValid()) {
95 return infos;
96 }
97 QSqlQuery query(db);
98 query.setForwardOnly(true);
99 bool b = false;
100 if (loadCount == 0) {
101 b = query.prepare("SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType FROM ImageTable3 order by Time desc");
102 } else {
103 b = query.prepare("SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType FROM ImageTable3 order by Time desc limit 80");
104 }
105 if (!b || ! query.exec()) {
106 // qDebug() << query.lastError();
107 return infos;
108 } else {
109 using namespace utils::base;
110 while (query.next()) {
111 DBImgInfo info;
112 info.filePath = query.value(0).toString();
113 info.fileName = query.value(1).toString();
114 info.dirHash = query.value(2).toString();
115 info.time = stringToDateTime(query.value(3).toString());
116 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
117 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
118 info.itemType = static_cast<ItemType>(query.value(6).toInt());
119 infos << info;
120 }
121 }
122 return infos;
123 }
124
125 const QStringList DBManager::getAllTimelines() const
126 {
127 QMutexLocker mutex(&m_mutex);
128 QStringList times;
129 QSqlDatabase db = getDatabase();
130 if (! db.isValid())
131 return times;
132
133 QSqlQuery query(db);
134 query.setForwardOnly(true);
135 bool b = query.prepare("SELECT DISTINCT Time FROM ImageTable3 ORDER BY Time DESC");
136 if (!b || ! query.exec()) {
137 db.close();
138 return times;
139 } else {
140 while (query.next()) {
141 times << query.value(0).toString();
142 }
143 }
144 db.close();
145 return times;
146 }
147
148 const DBImgInfoList DBManager::getInfosByTimeline(const QString &timeline) const
149 {
150 const DBImgInfoList list = getImgInfos("Time", timeline);
151 if (list.count() < 1) {
152 return DBImgInfoList();
153 } else {
154 return list;
155 }
156 }
157
158 const QStringList DBManager::getImportTimelines() const
159 {
160 QMutexLocker mutex(&m_mutex);
161 QStringList importtimes;
162 QSqlDatabase db = getDatabase();
163 if (! db.isValid())
164 return importtimes;
165
166 QSqlQuery query(db);
167 query.setForwardOnly(true);
168 bool b = query.prepare("SELECT DISTINCT ImportTime FROM ImageTable3 ORDER BY ImportTime DESC");
169 if (!b || !query.exec()) {
170 db.close();
171 return importtimes;
172 } else {
173 while (query.next()) {
174 importtimes << query.value(0).toString();
175 }
176 }
177 db.close();
178 return importtimes;
179 }
180
181 const DBImgInfoList DBManager::getInfosByImportTimeline(const QString &timeline) const
182 {
183 const DBImgInfoList list = getImgInfos("ImportTime", timeline);
184 if (list.count() < 1) {
185 return DBImgInfoList();
186 } else {
187 return list;
188 }
189 }
190
191 const DBImgInfo DBManager::getInfoByPath(const QString &path) const
192 {
193 DBImgInfoList list = getImgInfos("FilePath", path);
194 if (list.count() != 1) {
195 return DBImgInfo();
196 } else {
197 return list.first();
198 }
199 }
200
201 int DBManager::getImgsCount() const
202 {
203 QMutexLocker mutex(&m_mutex);
204 QSqlDatabase db = getDatabase();
205 if (! db.isValid()) {
206 return 0;
207 }
208 QSqlQuery query(db);
209 query.setForwardOnly(true);
210 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
211 db.close();
212 return 0;
213 }
214 if (!query.prepare("SELECT COUNT(*) FROM ImageTable3")) {
215 db.close();
216 return 0;
217 }
218 if (query.exec()) {
219 query.first();
220 int count = query.value(0).toInt();
221 if (!query.exec("COMMIT")) {
222 }
223 db.close();
224 return count;
225 }
226 db.close();
227 return 0;
228 }
229
230 void DBManager::insertImgInfos(const DBImgInfoList &infos)
231 {
232 QMutexLocker mutex(&m_mutex);
233 QSqlDatabase db = getDatabase();
234 if (infos.isEmpty() || ! db.isValid()) {
235 return;
236 }
237 QVariantList pathhashs, filenames, filepaths, dirs, times, changetimes, importtimes, fileTypes;
238 for (DBImgInfo info : infos) {
239 filenames << info.fileName;
240 filepaths << info.filePath;
241 pathhashs << utils::base::hashByString(info.filePath);
242 dirs << info.dirHash;
243 times << info.time.toString("yyyy.MM.dd");
244 changetimes << info.changeTime.toString(DATETIME_FORMAT_DATABASE);
245 importtimes << info.importTime.toString(DATETIME_FORMAT_DATABASE);
246 fileTypes << info.itemType;
247 }
248 QSqlQuery query(db);
249 query.setForwardOnly(true);
250 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
251 // qDebug() << query.lastError();
252 }
253 bool b = query.prepare("REPLACE INTO ImageTable3 (PathHash, FilePath, FileName, Dir, Time, "
254 "ChangeTime, ImportTime, FileType) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
255 if (!b) {
256 db.close();
257 return;
258 }
259 query.addBindValue(pathhashs);
260 query.addBindValue(filepaths);
261 query.addBindValue(filenames);
262 query.addBindValue(dirs);
263 query.addBindValue(times);
264 query.addBindValue(changetimes);
265 query.addBindValue(importtimes);
266 query.addBindValue(fileTypes);
267 if (! query.execBatch()) {
268 if (!query.exec("COMMIT")) {
269 // qDebug() << query.lastError();
270 }
271 db.close();
272 } else {
273 if (!query.exec("COMMIT")) {
274 // qDebug() << query.lastError();
275 }
276 db.close();
277 mutex.unlock();
278 for (DBImgInfo info : infos) {
279 ImageEngineApi::instance()->addImageData(info.filePath, info);
280 }
281 emit dApp->signalM->imagesInserted(/*infos*/);
282 }
283 }
284
285 void DBManager::removeImgInfos(const QStringList &paths)
286 {
287 qDebug() << "------" << __FUNCTION__ << "---size = " << paths.size();
288 if (paths.isEmpty()) {
289 return;
290 }
291 // Collect info before removing data
292 DBImgInfoList infos;
293 QStringList pathHashs;
294 for (QString path : paths) {
295 pathHashs << utils::base::hashByString(path);
296 infos.append(getImgInfos("FilePath", path, false));
297 }
298 QMutexLocker mutex(&m_mutex);
299 QSqlDatabase db = getDatabase();
300 if (! db.isValid()) {
301 return;
302 }
303 QSqlQuery query(db);
304 // Remove from albums table
305 query.setForwardOnly(true);
306 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
307 // qDebug() << query.lastError();
308 }
309 QString qs = "DELETE FROM AlbumTable3 WHERE PathHash=?";
310 bool b = query.prepare(qs);
311 if (!b) {
312 db.close();
313 return;
314 }
315 query.addBindValue(pathHashs);
316 query.execBatch();
317 if (!query.exec("COMMIT")) {
318 // qDebug() << query.lastError();
319 }
320 // Remove from image table
321 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
322 // qDebug() << query.lastError();
323 }
324 qs = "DELETE FROM ImageTable3 WHERE PathHash=?";
325 bool bs = query.prepare(qs);
326 if (!bs) {
327 db.close();
328 return;
329 }
330 query.addBindValue(pathHashs);
331 if (! query.execBatch()) {
332 if (!query.exec("COMMIT")) {
333 // qDebug() << query.lastError();
334 }
335 db.close();
336 } else {
337 if (!query.exec("COMMIT")) {
338 // qDebug() << query.lastError();
339 }
340 db.close();
341 mutex.unlock();
342 emit dApp->signalM->imagesRemoved();
343 qDebug() << "------" << __FUNCTION__ << "size = " << paths.size();
344 emit dApp->signalM->imagesRemovedPar(infos);
345 }
346 }
347
348 void DBManager::removeImgInfosNoSignal(const QStringList &paths)
349 {
350 QMutexLocker mutex(&m_mutex);
351 QSqlDatabase db = getDatabase();
352 if (paths.isEmpty() || ! db.isValid()) {
353 return;
354 }
355
356 // Collect info before removing data
357 QStringList pathHashs;
358 for (QString path : paths) {
359 pathHashs << utils::base::hashByString(path);
360 }
361
362 QSqlQuery query(db);
363 // Remove from albums table
364 query.setForwardOnly(true);
365 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
366 // qDebug() << "begin transaction failed.";
367 }
368 QString qs = "DELETE FROM AlbumTable3 WHERE PathHash=?";
369 bool b = query.prepare(qs);
370 if (!b) {
371 db.close();
372 return;
373 }
374 query.addBindValue(pathHashs);
375 query.execBatch();
376 if (!query.exec("COMMIT")) {
377 // qDebug() << "COMMIT failed.";
378 }
379
380 // Remove from image table
381 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
382 }
383 qs = "DELETE FROM ImageTable3 WHERE PathHash=?";
384 bool bs = query.prepare(qs);
385 if (!bs) {
386 db.close();
387 return;
388 }
389 query.addBindValue(pathHashs);
390 query.execBatch();
391 if (!query.exec("COMMIT")) {
392 }
393 db.close();
394 }
395
396 const QStringList DBManager::getAllAlbumNames(AlbumDBType atype) const
397 {
398 QMutexLocker mutex(&m_mutex);
399 QStringList list;
400 QSqlDatabase db = getDatabase();
401 if (! db.isValid()) {
402 return list;
403 }
404 QSqlQuery query(db);
405 query.setForwardOnly(true);
406 bool b = query.prepare("SELECT DISTINCT AlbumName FROM AlbumTable3 WHERE AlbumDBType =:atype");
407 query.bindValue(":atype", atype);
408 if (!b || !query.exec()) {
409 } else {
410 while (query.next()) {
411 list << query.value(0).toString();
412 }
413 }
414 db.close();
415
416 return list;
417 }
418
419 const QStringList DBManager::getPathsByAlbum(const QString &album, AlbumDBType atype) const
420 {
421 QMutexLocker mutex(&m_mutex);
422 QStringList list;
423 QSqlDatabase db = getDatabase();
424 if (! db.isValid()) {
425 return list;
426 }
427 QSqlQuery query(db);
428 query.setForwardOnly(true);
429 bool b = query.prepare("SELECT DISTINCT i.FilePath "
430 "FROM ImageTable3 AS i, AlbumTable3 AS a "
431 "WHERE i.PathHash=a.PathHash "
432 "AND a.AlbumName=:album "
433 "AND a.AlbumDBType=:atype "
434 /*"AND FilePath != \" \" "*/);
435 query.bindValue(":album", album);
436 query.bindValue(":atype", atype);
437 if (!b || ! query.exec()) {
438 } else {
439 while (query.next()) {
440 list << query.value(0).toString();
441 }
442 }
443 db.close();
444
445 return list;
446 }
447
448 const DBImgInfoList DBManager::getInfosByAlbum(const QString &album, AlbumDBType atype) const
449 {
450 QMutexLocker mutex(&m_mutex);
451 DBImgInfoList infos;
452 QSqlDatabase db = getDatabase();
453 if (! db.isValid()) {
454 return infos;
455 }
456 QSqlQuery query(db);
457 query.setForwardOnly(true);
458 bool b = query.prepare("SELECT DISTINCT i.FilePath, i.FileName, i.FileType, i.Time, i.ChangeTime, i.ImportTime "
459 "FROM ImageTable3 AS i, AlbumTable3 AS a "
460 "WHERE i.PathHash=a.PathHash "
461 "AND a.AlbumName=:album "
462 "AND a.AlbumDBType=:atype ");
463 query.bindValue(":album", album);
464 query.bindValue(":atype", atype);
465 if (!b || ! query.exec()) {
466 // qWarning() << "Get ImgInfo by album failed: " << query.lastError();
467 } else {
468 using namespace utils::base;
469 while (query.next()) {
470 DBImgInfo info;
471 info.filePath = query.value(0).toString();
472 info.fileName = query.value(1).toString();
473 info.itemType = static_cast<ItemType>(query.value(2).toInt());
474 info.time = stringToDateTime(query.value(3).toString());
475 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
476 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
477 infos << info;
478 }
479 }
480 db.close();
481 return infos;
482 }
483
484 int DBManager::getItemsCountByAlbum(const QString &album, const ItemType &type, AlbumDBType atype) const
485 {
486 int count = 0;
487 QMutexLocker mutex(&m_mutex);
488 QSqlDatabase db = getDatabase();
489 if (! db.isValid()) {
490 return 0;
491 }
492 QSqlQuery query(db);
493 query.setForwardOnly(true);
494 bool b = query.prepare("SELECT i.FileType "
495 "FROM ImageTable3 AS i, AlbumTable3 AS a "
496 "WHERE i.PathHash=a.PathHash "
497 "AND a.AlbumName=:album "
498 "AND a.AlbumDBType=:atype ");
499 query.bindValue(":album", album);
500 query.bindValue(":atype", atype);
501 if (!b || ! query.exec()) {
502 // qWarning() << "Get ImgInfo by album failed: " << query.lastError();
503 } else {
504 using namespace utils::base;
505 while (query.next()) {
506 ItemType itemType = static_cast<ItemType>(query.value(0).toInt());
507 if (type == ItemTypeNull) {
508 count++;
509 } else if (itemType == type) {
510 count++;
511 }
512 }
513 }
514 db.close();
515 qDebug() << __FUNCTION__ << "---count = " << count;
516 return count;
517 }
518
519 bool DBManager::isImgExistInAlbum(const QString &album, const QString &path, AlbumDBType atype) const
520 {
521 QMutexLocker mutex(&m_mutex);
522 QSqlDatabase db = getDatabase();
523 if (! db.isValid()) {
524 return false;
525 }
526 QSqlQuery query(db);
527 query.setForwardOnly(true);
528 bool b = query.prepare("SELECT COUNT(*) FROM AlbumTable3 WHERE PathHash = :hash "
529 "AND AlbumName = :album "
530 "AND AlbumDBType =:atype ");
531 if (!b) {
532 db.close();
533 return false;
534 }
535 query.bindValue(":hash", utils::base::hashByString(path));
536 query.bindValue(":album", album);
537 query.bindValue(":atype", atype);
538 if (query.exec()) {
539 query.first();
540 db.close();
541 return (query.value(0).toInt() == 1);
542 } else {
543 db.close();
544 return false;
545 }
546 }
547
548 bool DBManager::isAlbumExistInDB(const QString &album, AlbumDBType atype) const
549 {
550 QMutexLocker mutex(&m_mutex);
551 QSqlDatabase db = getDatabase();
552 if (! db.isValid()) {
553 return false;
554 }
555 QSqlQuery query(db);
556 query.setForwardOnly(true);
557 bool b = query.prepare("SELECT COUNT(*) FROM AlbumTable3 WHERE AlbumName = :album AND AlbumDBType =:atype");
558 if (!b) {
559 db.close();
560 return false;
561 }
562 query.bindValue(":album", album);
563 query.bindValue(":atype", atype);
564 if (query.exec()) {
565 query.first();
566 // 连接使用完后需要释放回数据库连接池
567 db.close();
568 return (query.value(0).toInt() >= 1);
569 } else {
570 // 连接使用完后需要释放回数据库连接池
571 db.close();
572 return false;
573 }
574 }
575
576 void DBManager::insertIntoAlbum(const QString &album, const QStringList &paths, AlbumDBType atype)
577 {
578 QMutexLocker mutex(&m_mutex);
579 QSqlDatabase db = getDatabase();
580 if (!db.isValid() || album.isEmpty()) {
581 return;
582 }
583 QStringList nameRows, pathHashRows;
584 QVariantList atypes;
585 for (QString path : paths) {
586 nameRows << album;
587 pathHashRows << utils::base::hashByString(path);
588 atypes << atype;
589 }
590 QSqlQuery query(db);
591 query.setForwardOnly(true);
592 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
593 }
594 bool b = query.prepare("REPLACE INTO AlbumTable3 (AlbumId, AlbumName, PathHash, AlbumDBType) VALUES (null, ?, ?, ?)");
595 if (!b) {
596 db.close();
597 return;
598 }
599 query.addBindValue(nameRows);
600 query.addBindValue(pathHashRows);
601 query.addBindValue(atypes);
602
603 if (! query.execBatch()) {
604 }
605 if (!query.exec("COMMIT")) {
606 }
607
608 //FIXME: Don't insert the repeated filepath into the same album
609 //Delete the same data
610 QString ps = "DELETE FROM AlbumTable3 where AlbumId NOT IN"
611 "(SELECT min(AlbumId) FROM AlbumTable3 GROUP BY"
612 " AlbumName, PathHash, AlbumDBType) AND PathHash != \"%1\""
613 " AND AlbumDBType =:atype ";
614 bool bs = query.prepare(ps.arg(EMPTY_HASH_STR));
615 if (!bs) {
616 db.close();
617 mutex.unlock();
618 return;
619 }
620 query.bindValue(":atype", atype);
621 if (!query.exec()) {
622 // qDebug() << "delete same date failed!";
623 }
624 if (!query.exec("COMMIT")) {
625 }
626 db.close();
627 mutex.unlock();
628 }
629
630 void DBManager::insertIntoAlbumNoSignal(const QString &album, const QStringList &paths, AlbumDBType atype)
631 {
632 QMutexLocker mutex(&m_mutex);
633 QSqlDatabase db = getDatabase();
634 if (! db.isValid() || album.isEmpty() || paths.isEmpty()) {
635 return;
636 }
637 QStringList nameRows, pathHashRows;
638 QVariantList atypes;
639 for (QString path : paths) {
640 nameRows << album;
641 pathHashRows << utils::base::hashByString(path);
642 atypes << atype;
643 }
644
645 QSqlQuery query(db);
646 query.setForwardOnly(true);
647 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
648 // qDebug() << "begin transaction failed.";
649 }
650 bool b = query.prepare("REPLACE INTO AlbumTable3 (AlbumId, AlbumName, PathHash, AlbumDBType) "
651 "VALUES (null, ?, ?, ?)");
652 if (!b) {
653 db.close();
654 return;
655 }
656 query.addBindValue(nameRows);
657 query.addBindValue(pathHashRows);
658 query.addBindValue(atypes);
659 if (! query.execBatch()) {
660 }
661 if (!query.exec("COMMIT")) {
662 // qDebug() << "COMMIT failed.";
663 }
664
665 //FIXME: Don't insert the repeated filepath into the same album
666 //Delete the same data
667 QString ps = "DELETE FROM AlbumTable3 where AlbumId NOT IN "
668 "(SELECT min(AlbumId) FROM AlbumTable3 GROUP BY "
669 " AlbumName, PathHash) AND PathHash != \"%1\" "
670 " AND AlbumDBType =:atype ";
671 bool bs = query.prepare(ps.arg(EMPTY_HASH_STR));
672 if (!bs) {
673 db.close();
674 return;
675 }
676 query.bindValue(":atype", atype);
677 if (!query.exec()) {
678 }
679 if (!query.exec("COMMIT")) {
680 // qDebug() << "COMMIT failed.";
681 }
682 db.close();
683 }
684
685
686 void DBManager::removeAlbum(const QString &album, AlbumDBType atype)
687 {
688 QMutexLocker mutex(&m_mutex);
689 QSqlDatabase db = getDatabase();
690 if (! db.isValid()) {
691 return;
692 }
693 QSqlQuery query(db);
694 query.setForwardOnly(true);
695 bool b = query.prepare("DELETE FROM AlbumTable3 WHERE AlbumName=:album AND AlbumDBType =:atype");
696 if (!b) {
697 db.close();
698 return;
699 }
700 query.bindValue(":album", album);
701 query.bindValue(":atype", atype);
702 if (!query.exec()) {
703 }
704 db.close();
705 }
706
707 void DBManager::removeFromAlbum(const QString &album, const QStringList &paths, AlbumDBType atype)
708 {
709 QMutexLocker mutex(&m_mutex);
710 QSqlDatabase db = getDatabase();
711 if (! db.isValid()) {
712 return;
713 }
714
715 QStringList pathHashs;
716 QVariantList atypes;
717 for (QString path : paths) {
718 pathHashs << utils::base::hashByString(path);
719 atypes << atype;
720 }
721 QSqlQuery query(db);
722 query.setForwardOnly(true);
723 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
724 // qDebug() << "begin transaction failed.";
725 }
726 // Remove from albums table
727 QString qs("DELETE FROM AlbumTable3 WHERE AlbumName=\"%1\" AND PathHash=? AND AlbumDBType=? ");
728 bool b = query.prepare(qs.arg(album));
729 if (!b) {
730 db.close();
731 return;
732 }
733 query.addBindValue(pathHashs);
734 query.addBindValue(atypes);
735 bool suc = false;
736 if (! query.execBatch()) {
737 } else {
738 suc = true;
739 }
740 if (!query.exec("COMMIT")) {
741 // qDebug() << "COMMIT failed.";
742 }
743 db.close();
744 mutex.unlock();
745 if (suc)
746 emit dApp->signalM->removedFromAlbum(album, paths);
747 }
748
749 void DBManager::renameAlbum(const QString &oldAlbum, const QString &newAlbum, AlbumDBType atype)
750 {
751 QMutexLocker mutex(&m_mutex);
752 QSqlDatabase db = getDatabase();
753 if (! db.isValid()) {
754 return;
755 }
756 QSqlQuery query(db);
757
758 query.setForwardOnly(true);
759 bool b = query.prepare("UPDATE AlbumTable3 SET "
760 "AlbumName = :newName "
761 "WHERE AlbumName = :oldName "
762 "AND AlbumDBType = :atype");
763 if (!b) {
764 db.close();
765 return;
766 }
767 query.bindValue(":newName", newAlbum);
768 query.bindValue(":oldName", oldAlbum);
769 query.bindValue(":atype", atype);
770 if (! query.exec()) {
771 }
772 db.close();
773 }
774
775 const DBImgInfoList DBManager::getInfosByNameTimeline(const QString &value) const
776 {
777 QMutexLocker mutex(&m_mutex);
778 DBImgInfoList infos;
779 QSqlDatabase db = getDatabase();
780 if (! db.isValid()) {
781 return infos;
782 }
783 QSqlQuery query(db);
784 query.setForwardOnly(true);
785
786 QString queryStr = "SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType FROM ImageTable3 "
787 "WHERE FileName like '%" + value + "%' OR Time like '%" + value + "%' ORDER BY Time DESC";
788
789 bool b = query.prepare(queryStr);
790
791 if (!b || !query.exec()) {
792 } else {
793 using namespace utils::base;
794 while (query.next()) {
795 DBImgInfo info;
796 info.filePath = query.value(0).toString();
797 info.fileName = query.value(1).toString();
798 info.dirHash = query.value(2).toString();
799 info.time = stringToDateTime(query.value(3).toString());
800 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
801 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
802 info.itemType = static_cast<ItemType>(query.value(6).toInt());
803 infos << info;
804 }
805 }
806 db.close();
807 return infos;
808 }
809
810 const DBImgInfoList DBManager::getInfosForKeyword(const QString &keywords) const
811 {
812 const DBImgInfoList list = getInfosByNameTimeline(keywords);
813 if (list.count() < 1) {
814 return DBImgInfoList();
815 } else {
816 return list;
817 }
818 }
819
820 const DBImgInfoList DBManager::getTrashInfosForKeyword(const QString &keywords) const
821 {
822 QMutexLocker mutex(&m_mutex);
823 DBImgInfoList infos;
824 QSqlDatabase db = getDatabase();
825 if (! db.isValid()) {
826 return infos;
827 }
828 QSqlQuery query(db);
829 query.setForwardOnly(true);
830
831 QString queryStr = "SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType AlbumName FROM TrashTable3 "
832 "WHERE FileName like '%" + keywords + "%' OR Time like '%" + keywords + "%' ORDER BY Time DESC";
833
834 bool b = query.prepare(queryStr);
835
836 if (!b || !query.exec()) {
837 } else {
838 using namespace utils::base;
839 while (query.next()) {
840 DBImgInfo info;
841 info.filePath = query.value(0).toString();
842 info.fileName = query.value(1).toString();
843 info.dirHash = query.value(2).toString();
844 info.time = stringToDateTime(query.value(3).toString());
845 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
846 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
847 info.itemType = ItemType(query.value(6).toInt());
848 infos << info;
849 }
850 }
851 db.close();
852 return infos;
853 }
854
855 const DBImgInfoList DBManager::getInfosForKeyword(const QString &album, const QString &keywords) const
856 {
857 QMutexLocker mutex(&m_mutex);
858
859 DBImgInfoList infos;
860 QSqlDatabase db = getDatabase();
861 if (! db.isValid()) {
862 return infos;
863 }
864
865 QString queryStr = "SELECT DISTINCT i.FilePath, i.FileName, i.Dir, i.Time, i.ChangeTime, i.ImportTime "
866 "FROM ImageTable3 AS i "
867 "inner join AlbumTable3 AS a on i.PathHash=a.PathHash AND a.AlbumName=:album "
868 "WHERE i.FileName like '%" + keywords + "%' ORDER BY Time DESC"; //OR Time like '%" + keywords + "%' 移除按时间搜索
869
870 QSqlQuery query(db);
871 query.setForwardOnly(true);
872 bool b = query.prepare(queryStr);
873 query.bindValue(":album", album);
874
875
876 if (!b || ! query.exec()) {
877 } else {
878 using namespace utils::base;
879 while (query.next()) {
880 DBImgInfo info;
881 info.filePath = query.value(0).toString();
882 info.fileName = query.value(1).toString();
883 info.dirHash = query.value(2).toString();
884 info.time = stringToDateTime(query.value(3).toString());
885 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
886 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
887 infos << info;
888 }
889 }
890 db.close();
891 return infos;
892 }
893
894 const QMultiMap<QString, QString> DBManager::getAllPathAlbumNames() const
895 {
896 QMutexLocker mutex(&m_mutex);
897
898 QMultiMap<QString, QString> infos;
899 infos.clear();
900 QSqlDatabase db = getDatabase();
901 if (! db.isValid()) {
902 return infos;
903 }
904
905 QString queryStr = "SELECT DISTINCT i.FilePath, a.AlbumName "
906 "FROM ImageTable3 AS i, AlbumTable3 AS a "
907 "inner join AlbumTable3 on i.PathHash=a.PathHash "
908 "where a.AlbumDBType = 1";
909
910 QSqlQuery query(db);
911 query.setForwardOnly(true);
912 bool b = query.prepare(queryStr);
913 if (!b || ! query.exec()) {
914 // qWarning() << "getAllPathAlbumNames failed: " << query.lastError();
915 } else {
916 using namespace utils::base;
917 while (query.next()) {
918 infos.insert(query.value(0).toString(), query.value(1).toString());
919 }
920 }
921 db.close();
922 return infos;
923 }
924
925 const DBImgInfoList DBManager::getImgInfos(const QString &key, const QString &value, const bool &needlock) const
926 {
927 Q_UNUSED(needlock)
928 QMutexLocker mutex(&m_mutex);
929 DBImgInfoList infos;
930 QSqlDatabase db = getDatabase();
931 if (! db.isValid()) {
932 return infos;
933 }
934 QSqlQuery query(db);
935 query.setForwardOnly(true);
936 bool b = query.prepare(QString("SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType FROM ImageTable3 "
937 "WHERE %1= :value ORDER BY Time DESC").arg(key));
938
939 query.bindValue(":value", value);
940
941 if (!b || !query.exec()) {
942 } else {
943 using namespace utils::base;
944 while (query.next()) {
945 DBImgInfo info;
946 info.filePath = query.value(0).toString();
947 info.fileName = query.value(1).toString();
948 info.dirHash = query.value(2).toString();
949 info.time = stringToDateTime(query.value(3).toString());
950 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
951 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
952 info.itemType = static_cast<ItemType>(query.value(6).toInt());
953 infos << info;
954 }
955 }
956 db.close();
957 return infos;
958 }
959
960 const QSqlDatabase DBManager::getDatabase() const
961 {
962 if (!m_db.open()) {
963 m_db = QSqlDatabase::addDatabase("QSQLITE", "album_sql_connect"); //not dbConnection
964 m_db.setDatabaseName(DATABASE_PATH + DATABASE_NAME);
965 if (!m_db.open()) {
966 return QSqlDatabase();
967 }
968 }
969 return m_db;
970 }
971
972 void DBManager::checkDatabase()
973 {
974 QDir dd(DATABASE_PATH);
975 if (! dd.exists()) {
976 dd.mkpath(DATABASE_PATH);
977 } else {
978 }
979 QMutexLocker mutex(&m_mutex);
980 QSqlDatabase db = getDatabase();
981 if (! db.isValid()) {
982 return;
983 }
984 bool tableExist = false;
985 QSqlQuery query(db);
986 query.setForwardOnly(true);
987 bool b = query.prepare("SELECT name FROM sqlite_master "
988 "WHERE type=\"table\" AND name = \"ImageTable3\"");
989 if (!b) {
990 db.close();
991 return;
992 }
993 if (query.exec() && query.first()) {
994 tableExist = ! query.value(0).toString().isEmpty();
995 }
996 //if tables not exist, create it.
997 if (!tableExist) {
998 QSqlQuery queryCreate(db);
999 // ImageTable3
1000 //////////////////////////////////////////////////////////////
1001 //PathHash | FilePath | FileName | Dir | Time | ChangeTime | ImportTime//
1002 //TEXT primari key | TEXT | TEXT | TEXT | TEXT | TEXT | TEXT //
1003 //////////////////////////////////////////////////////////////
1004 bool b = queryCreate.exec(QString("CREATE TABLE IF NOT EXISTS ImageTable3 ( "
1005 "PathHash TEXT primary key, "
1006 "FilePath TEXT, "
1007 "FileName TEXT, "
1008 "Dir TEXT, "
1009 "Time TEXT, "
1010 "ChangeTime TEXT, "
1011 "ImportTime TEXT, "
1012 "FileType INTEGER, "
1013 "DataHash TEXT)"));
1014 if (!b) {
1015 qDebug() << "b CREATE TABLE exec failed.";
1016 }
1017
1018 // AlbumTable3
1019 ///////////////////////////////////////////////////////////////////////////
1020 //AlbumId | AlbumName | PathHash |AlbumDBType //
1021 //INTEGER primari key | TEXT | TEXT |TEXT //
1022 ///////////////////////////////////////////////////////////////////////////
1023 bool c = queryCreate.exec(QString("CREATE TABLE IF NOT EXISTS AlbumTable3 ( "
1024 "AlbumId INTEGER primary key, "
1025 "AlbumName TEXT, "
1026 "PathHash TEXT,"
1027 "AlbumDBType INTEGER)"));
1028 if (!c) {
1029 qDebug() << "c CREATE TABLE exec failed.";
1030 }
1031 // TrashTable
1032 //////////////////////////////////////////////////////////////
1033 //PathHash | FilePath | FileName | Dir | Time | ChangeTime | ImportTime//
1034 //TEXT primari key | TEXT | TEXT | TEXT | TEXT | TEXT | TEXT //
1035 //////////////////////////////////////////////////////////////
1036 bool d = queryCreate.exec(QString("CREATE TABLE IF NOT EXISTS TrashTable3 ( "
1037 "PathHash TEXT primary key, "
1038 "FilePath TEXT, "
1039 "FileName TEXT, "
1040 "Dir TEXT, "
1041 "Time TEXT, "
1042 "ChangeTime TEXT, "
1043 "ImportTime TEXT,"
1044 "FileType INTEGER)"));
1045 if (!d) {
1046 qDebug() << "d CREATE TABLE exec failed.";
1047 }
1048 // Check if there is an old version table exist or not
1049 //TODO: AlbumTable's primary key is changed, need to importVersion again
1050 } else {
1051 // 判断ImageTable3中是否有ChangeTime字段
1052 QString strSqlImage = QString::fromLocal8Bit("select sql from sqlite_master where name = \"ImageTable3\" and sql like \"%ChangeTime%\"");
1053 QSqlQuery queryImage1(db);
1054 bool q = queryImage1.exec(strSqlImage);
1055 if (!q && queryImage1.next()) {
1056 // 无ChangeTime字段,则增加ChangeTime字段,赋值当前时间
1057 QString strDate = QDateTime::currentDateTime().toString(DATETIME_FORMAT_DATABASE);
1058 if (queryImage1.exec(QString("ALTER TABLE \"ImageTable3\" ADD COLUMN \"ChangeTime\" TEXT default \"%1\"")
1059 .arg(strDate))) {
1060 qDebug() << "add ChangeTime success";
1061 }
1062 }
1063
1064 // 判断ImageTable3中是否有ImportTime字段
1065 QString strSqlImportTime = "select sql from sqlite_master where name = 'ImageTable3' and sql like '%ImportTime%'";
1066 QSqlQuery queryImage2(db);
1067 if (!queryImage2.exec(strSqlImportTime)) {
1068 qDebug() << queryImage2.lastError();
1069 }
1070 if (!queryImage2.next()) {
1071 // 无ImportTime字段,则增加ImportTime字段
1072 QString strDate = QDateTime::currentDateTime().toString(DATETIME_FORMAT_DATABASE);
1073 if (queryImage2.exec(QString("ALTER TABLE \"ImageTable3\" ADD COLUMN \"ImportTime\" TEXT default \"%1\"")
1074 .arg(strDate))) {
1075 qDebug() << "add ImportTime success";
1076 }
1077 }
1078
1079 // 判断ImageTable3中是否有FileType字段,区分是图片还是视频
1080 QString strSqlFileType = QString::fromLocal8Bit(
1081 "select * from sqlite_master where name = 'ImageTable3' and sql like '%FileType%'");
1082 QSqlQuery queryFileType(db);
1083 int fileType = static_cast<int>(ItemTypePic);
1084 queryFileType.exec(strSqlFileType);
1085 if (!queryFileType.next()) {
1086 // 无FileType字段,则增加FileType字段,赋值1,默认是图片
1087 if (queryFileType.exec(QString("ALTER TABLE \"ImageTable3\" ADD COLUMN \"FileType\" INTEGER default \"%1\"")
1088 .arg(QString::number(fileType)))) {
1089 qDebug() << "add FileType success";
1090 }
1091 }
1092
1093 // 判断ImageTable3中是否有DataHash字段,根据文件内容产生的hash
1094 QString strDataHash = QString::fromLocal8Bit(
1095 "select * from sqlite_master where name = 'ImageTable3' and sql like '%DataHash%'");
1096 QSqlQuery queryDataHash(db);
1097 queryDataHash.exec(strDataHash);
1098 if (!queryDataHash.next()) {
1099 // DataHash,则增加DataHash字段
1100 if (queryDataHash.exec(QString("ALTER TABLE \"ImageTable3\" ADD COLUMN \"DataHash\" TEXT default \"%1\"")
1101 .arg(""))) {
1102 qDebug() << "add FileType success";
1103 }
1104 }
1105
1106 // 判断AlbumTable3中是否有AlbumDBType字段
1107 QString strSqlDBType = QString::fromLocal8Bit("select * from sqlite_master where name = \"AlbumTable3\" and sql like \"%AlbumDBType%\"");
1108 QSqlQuery queryType(db);
1109 bool q2 = queryType.exec(strSqlDBType);
1110 if (!q2 && queryType.next()) {
1111 // 无AlbumDBType字段,则增加AlbumDBType字段, 全部赋值为个人相册
1112 if (queryType.exec(QString("ALTER TABLE \"AlbumTable3\" ADD COLUMN \"AlbumDBType\" INTEGER default %1")
1113 .arg("1"))) {
1114 qDebug() << "add AlbumDBType success";
1115 }
1116 if (queryType.exec(QString("update AlbumTable3 SET AlbumDBType = 0 Where AlbumName = \"%1\" ")
1117 .arg(COMMON_STR_FAVORITES))) {
1118 }
1119 }
1120 }
1121 // 判断TrashTable的版本
1122 QString strSqlTrashTable = QString::fromLocal8Bit("select * from sqlite_master where name = \"TrashTable3\"");
1123 QSqlQuery queryTrashReBuild(db);
1124 bool build = queryTrashReBuild.exec(strSqlTrashTable);
1125 if (!build) {
1126 qDebug() << queryTrashReBuild.lastError();
1127 }
1128 if (!queryTrashReBuild.next()) {
1129 //无新版TrashTable,则创建新表,导入旧表数据
1130 QString defaultImportTime = QDateTime::currentDateTime().toString(DATETIME_FORMAT_DATABASE);
1131 if (!queryTrashReBuild.exec(QString("CREATE TABLE IF NOT EXISTS TrashTable3 ( "
1132 "PathHash TEXT primary key, "
1133 "FilePath TEXT, "
1134 "FileName TEXT, "
1135 "Dir TEXT, "
1136 "Time TEXT, "
1137 "ChangeTime TEXT, "
1138 "ImportTime TEXT default \"%1\", "
1139 "FileType INTEGER)").arg(defaultImportTime))) {
1140 qDebug() << queryTrashReBuild.lastError();
1141 }
1142 } else {
1143 //判断TrashTable3是否包含FileType
1144 QString strSqlFileType = QString::fromLocal8Bit("select * from sqlite_master where name = \"TrashTable3\" and sql like \"%FileType%\"");
1145 QSqlQuery queryFileType(db);
1146 bool q2 = queryFileType.exec(strSqlFileType);
1147 if (!q2) {
1148 qDebug() << queryFileType.lastError();
1149 }
1150 if (!queryFileType.next()) {
1151 // 无FileType字段,则增加FileType字段, 全部赋值为图片
1152 int type = ItemType::ItemTypePic;
1153 if (queryFileType.exec(QString("ALTER TABLE \"TrashTable3\" ADD COLUMN \"FileType\" INTEGER default %1")
1154 .arg(QString::number(type)))) {
1155 qDebug() << "add AlbumDBType success";
1156 }
1157 }
1158 }
1159
1160 QString strSqlTrashTableUpdate = QString::fromLocal8Bit("select * from sqlite_master where name = \"TrashTable\"");
1161 QSqlQuery queryTrashUpdate(db);
1162 bool update = queryTrashUpdate.exec(strSqlTrashTableUpdate);
1163 if (!update) {
1164 qDebug() << queryTrashUpdate.lastError();
1165 }
1166 if (queryTrashUpdate.next()) {
1167 if (!queryTrashUpdate.exec("REPLACE INTO TrashTable3 (PathHash, FilePath, FileName, Dir, Time, ChangeTime)"
1168 " SELECT PathHash, FilePath, FileName, Dir, Time, ChangeTime From TrashTable ")) {
1169 qDebug() << queryTrashUpdate.lastError();
1170 }
1171 if (!queryTrashUpdate.exec(QString("DROP TABLE TrashTable"))) {
1172 qDebug() << queryTrashUpdate.lastError();
1173 }
1174 }
1175 db.close();
1176 }
1177
1178 const QStringList DBManager::getAllTrashPaths() const
1179 {
1180 QMutexLocker mutex(&m_mutex);
1181 QStringList paths;
1182 QSqlDatabase db = getDatabase();
1183 if (! db.isValid())
1184 return paths;
1185
1186 QSqlQuery query(db);
1187 query.setForwardOnly(true);
1188 bool b = query.prepare("SELECT "
1189 "FilePath "
1190 "FROM TrashTable3 ORDER BY Time DESC");
1191 if (!b || ! query.exec()) {
1192 // qWarning() << "Get Data from TrashTable failed: " << query.lastError();
1193 // // 连接使用完后需要释放回数据库连接池
1194 //ConnectionPool::closeConnection(db);
1195 db.close();
1196 return paths;
1197 } else {
1198 while (query.next()) {
1199 paths << query.value(0).toString();
1200 }
1201 }
1202 // // 连接使用完后需要释放回数据库连接池
1203 //ConnectionPool::closeConnection(db);
1204 db.close();
1205 return paths;
1206 }
1207
1208 const DBImgInfoList DBManager::getAllTrashInfos() const
1209 {
1210 QMutexLocker mutex(&m_mutex);
1211 DBImgInfoList infos;
1212 QSqlDatabase db = getDatabase();
1213 if (! db.isValid()) {
1214 return infos;
1215 }
1216 QSqlQuery query(db);
1217 query.setForwardOnly(true);
1218 bool b = query.prepare("SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType "
1219 "FROM TrashTable3 ORDER BY ImportTime DESC");
1220 if (!b || ! query.exec()) {
1221 // qWarning() << "Get data from TrashTable failed: " << query.lastError();
1222 // // 连接使用完后需要释放回数据库连接池
1223 //ConnectionPool::closeConnection(db);
1224 db.close();
1225 return infos;
1226 } else {
1227 using namespace utils::base;
1228 while (query.next()) {
1229 DBImgInfo info;
1230 info.filePath = query.value(0).toString();
1231 if (info.filePath.isEmpty()) //如果路径为空
1232 continue;
1233 info.fileName = query.value(1).toString();
1234 info.dirHash = query.value(2).toString();
1235 info.time = stringToDateTime(query.value(3).toString());
1236 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
1237 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
1238 info.itemType = ItemType(query.value(6).toInt());
1239 infos << info;
1240 }
1241 }
1242 // // 连接使用完后需要释放回数据库连接池
1243 //ConnectionPool::closeConnection(db);
1244 db.close();
1245 return infos;
1246 }
1247
1248 void DBManager::insertTrashImgInfos(const DBImgInfoList &infos)
1249 {
1250 QMutexLocker mutex(&m_mutex);
1251 QSqlDatabase db = getDatabase();
1252 if (infos.isEmpty() || ! db.isValid()) {
1253 return;
1254 }
1255
1256 QVariantList pathhashs, filenames, filepaths, dirs, times, changetimes, importtimes, filetypes;
1257
1258 for (DBImgInfo info : infos) {
1259 filenames << info.fileName;
1260 filepaths << info.filePath;
1261 pathhashs << utils::base::hashByString(info.filePath);
1262 dirs << info.dirHash;
1263 times << info.time.toString("yyyy.MM.dd");
1264 changetimes << info.changeTime.toString(DATETIME_FORMAT_DATABASE);
1265 importtimes << info.importTime.toString(DATETIME_FORMAT_DATABASE);
1266 filetypes << static_cast<int>(info.itemType);
1267 }
1268
1269 // Insert into TrashTable
1270 QSqlQuery query(db);
1271 query.setForwardOnly(true);
1272 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
1273 qDebug() << "begin transaction failed.";
1274 }
1275 bool b = query.prepare("REPLACE INTO TrashTable3 "
1276 "(PathHash, FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
1277 if (!b) {
1278 db.close();
1279 return;
1280 }
1281 query.addBindValue(pathhashs);
1282 query.addBindValue(filepaths);
1283 query.addBindValue(filenames);
1284 query.addBindValue(dirs);
1285 query.addBindValue(times);
1286 query.addBindValue(changetimes);
1287 query.addBindValue(importtimes);
1288 query.addBindValue(filetypes);
1289 if (! query.execBatch()) {
1290 // qWarning() << "Insert data into TrashTable failed: "
1291 // << query.lastError();
1292 if (!query.exec("COMMIT")) {
1293 qDebug() << "COMMIT failed.";
1294 }
1295 db.close();
1296 } else {
1297 if (!query.exec("COMMIT")) {
1298 qDebug() << "COMMIT failed.";
1299 }
1300 db.close();
1301 mutex.unlock();
1302 emit dApp->signalM->imagesTrashInserted(/*infos*/);
1303 }
1304 // 连接使用完后需要释放回数据库连接池
1305 //ConnectionPool::closeConnection(db);
1306 }
1307
1308 void DBManager::removeTrashImgInfos(const QStringList &paths)
1309 {
1310 QMutexLocker mutex(&m_mutex);
1311 QSqlDatabase db = getDatabase();
1312 if (paths.isEmpty() || ! db.isValid()) {
1313 return;
1314 }
1315
1316 // Collect info before removing data
1317 // DBImgInfoList infos;
1318 QStringList pathHashs;
1319 for (QString path : paths) {
1320 pathHashs << utils::base::hashByString(path);
1321 // infos << getInfoByPath(path);
1322 }
1323
1324 QSqlQuery query(db);
1325 // Remove from albums table
1326 query.setForwardOnly(true);
1327
1328 // Remove from image table
1329 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
1330 // qDebug() << "begin transaction failed.";
1331 }
1332 QString qs = "DELETE FROM TrashTable3 WHERE PathHash=?";
1333 bool b = query.prepare(qs);
1334 if (!b) {
1335 db.close();
1336 return;
1337 }
1338 query.addBindValue(pathHashs);
1339 if (! query.execBatch()) {
1340 // qWarning() << "Remove data from TrashTable failed: "
1341 // << query.lastError();
1342 if (!query.exec("COMMIT")) {
1343 // qDebug() << "COMMIT failed.";
1344 }
1345 db.close();
1346 } else {
1347 if (!query.exec("COMMIT")) {
1348 // qDebug() << "COMMIT failed.";
1349 }
1350 db.close();
1351 mutex.unlock();
1352 emit dApp->signalM->imagesTrashRemoved(/*infos*/);
1353 }
1354 // // 连接使用完后需要释放回数据库连接池
1355 //ConnectionPool::closeConnection(db);
1356 }
1357
1358 void DBManager::removeTrashImgInfosNoSignal(const QStringList &paths)
1359 {
1360 QMutexLocker mutex(&m_mutex);
1361 QSqlDatabase db = getDatabase();
1362 if (paths.isEmpty() || ! db.isValid()) {
1363 return;
1364 }
1365
1366 // Collect info before removing data
1367 // DBImgInfoList infos;
1368 QStringList pathHashs;
1369 for (QString path : paths) {
1370 pathHashs << utils::base::hashByString(path);
1371 // infos << getInfoByPath(path);
1372 }
1373
1374 QSqlQuery query(db);
1375 // Remove from albums table
1376 query.setForwardOnly(true);
1377 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
1378 // qDebug() << "begin transaction failed.";
1379 }
1380 QString qs = "DELETE FROM AlbumTable3 WHERE PathHash=?";
1381 bool b = query.prepare(qs);
1382 if (!b) {
1383 db.close();
1384 return;
1385 }
1386 query.addBindValue(pathHashs);
1387 query.execBatch();
1388 if (!query.exec("COMMIT")) {
1389 // qDebug() << "COMMIT failed.";
1390 }
1391
1392 // Remove from image table
1393 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
1394 // qDebug() << "begin transaction failed.";
1395 }
1396 qs = "DELETE FROM TrashTable3 WHERE PathHash=?";
1397 bool bs = query.prepare(qs);
1398 if (!bs) {
1399 db.close();
1400 return;
1401 }
1402 query.addBindValue(pathHashs);
1403 query.execBatch();
1404 if (!query.exec("COMMIT")) {
1405 // qDebug() << "COMMIT failed.";
1406 }
1407 // 连接使用完后需要释放回数据库连接池
1408 ////ConnectionPool::closeConnection(db);
1409 db.close();
1410 }
1411
1412 const DBImgInfo DBManager::getTrashInfoByPath(const QString &path) const
1413 {
1414 DBImgInfoList list = getTrashImgInfos("FilePath", path);
1415 if (list.count() != 1) {
1416 return DBImgInfo();
1417 } else {
1418 return list.first();
1419 }
1420 }
1421
1422 const DBImgInfoList DBManager::getTrashImgInfos(const QString &key, const QString &value) const
1423 {
1424 QMutexLocker mutex(&m_mutex);
1425 DBImgInfoList infos;
1426 QSqlDatabase db = getDatabase();
1427 if (! db.isValid()) {
1428 return infos;
1429 }
1430 QSqlQuery query(db);
1431 query.setForwardOnly(true);
1432 bool b = query.prepare(QString("SELECT FilePath, FileName, Dir, Time, ChangeTime, ImportTime, FileType FROM TrashTable3 "
1433 "WHERE %1= :value ORDER BY Time DESC").arg(key));
1434
1435 query.bindValue(":value", value);
1436
1437 if (!b || !query.exec()) {
1438 // qWarning() << "Get Image from database failed: " << query.lastError();
1439 } else {
1440 using namespace utils::base;
1441 while (query.next()) {
1442 DBImgInfo info;
1443 info.filePath = query.value(0).toString();
1444 if (info.filePath.isEmpty()) //如果路径为空
1445 continue;
1446 info.fileName = query.value(1).toString();
1447 info.dirHash = query.value(2).toString();
1448 info.time = stringToDateTime(query.value(3).toString());
1449 info.changeTime = QDateTime::fromString(query.value(4).toString(), DATETIME_FORMAT_DATABASE);
1450 info.importTime = QDateTime::fromString(query.value(5).toString(), DATETIME_FORMAT_DATABASE);
1451 info.itemType = ItemType(query.value(6).toInt());
1452
1453 infos << info;
1454 }
1455 }
1456 // 连接使用完后需要释放回数据库连接池
1457 ////ConnectionPool::closeConnection(db);
1458 db.close();
1459 return infos;
1460 }
1461
1462 int DBManager::getTrashImgsCount() const
1463 {
1464 QMutexLocker mutex(&m_mutex);
1465 QSqlDatabase db = getDatabase();
1466 if (!db.isValid()) {
1467 return 0;
1468 }
1469 QSqlQuery query(db);
1470 query.setForwardOnly(true);
1471 if (!query.exec("BEGIN IMMEDIATE TRANSACTION")) {
1472 // qDebug() << "begin transaction failed.";
1473 }
1474 bool b = query.prepare("SELECT COUNT(*) FROM TrashTable3");
1475 if (!b) {
1476 db.close();
1477 return 0;
1478 }
1479 if (query.exec()) {
1480 query.first();
1481 int count = query.value(0).toInt();
1482 if (!query.exec("COMMIT")) {
1483 // qDebug() << "COMMIT failed.";
1484 }
1485 // 连接使用完后需要释放回数据库连接池
1486 ////ConnectionPool::closeConnection(db);
1487 db.close();
1488 return count;
1489 }
1490 // 连接使用完后需要释放回数据库连接池
1491 ////ConnectionPool::closeConnection(db);
1492 db.close();
1493 return 0;
1494 }