1 /* ============================================================
2 *
3 * This file is a part of digiKam project
4 * https://www.digikam.org
5 *
6 * Date : 2004-06-18
7 * Description : Core database interface.
8 *
9 * Copyright (C) 2004-2005 by Renchi Raju <renchi dot raju at gmail dot com>
10 * Copyright (C) 2006-2021 by Gilles Caulier <caulier dot gilles at gmail dot com>
11 * Copyright (C) 2006-2012 by Marcel Wiesweg <marcel dot wiesweg at gmx dot de>
12 * Copyright (C) 2012 by Andi Clemens <andi dot clemens at gmail dot com>
13 *
14 * This program is free software; you can redistribute it
15 * and/or modify it under the terms of the GNU General
16 * Public License as published by the Free Software Foundation;
17 * either version 2, or (at your option)
18 * any later version.
19 *
20 * This program is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * ============================================================ */
26
27 #include "coredb.h"
28
29 // KDE includes
30
31 #include <ksharedconfig.h>
32 #include <kconfiggroup.h>
33
34 // Local includes
35
36 #include "digikam_debug.h"
37 #include "digikam_globals.h"
38 #include "coredbbackend.h"
39 #include "collectionmanager.h"
40 #include "collectionlocation.h"
41 #include "dbengineactiontype.h"
42 #include "tagscache.h"
43 #include "album.h"
44
45 namespace Digikam
46 {
47
48 class Q_DECL_HIDDEN CoreDB::Private
49 {
50
51 public:
52
Private()53 explicit Private()
54 : db (nullptr),
55 uniqueHashVersion(-1)
56 {
57 }
58
59 static const QString configGroupName;
60 static const QString configRecentlyUsedTags;
61
62 CoreDbBackend* db;
63 QList<int> recentlyAssignedTags;
64
65 int uniqueHashVersion;
66
67 public:
68
69 QString constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean);
70 QList<qlonglong> execRelatedImagesQuery(DbEngineSqlQuery& query, qlonglong id, DatabaseRelation::Type type);
71 };
72
73 const QString CoreDB::Private::configGroupName(QLatin1String("CoreDB Settings"));
74 const QString CoreDB::Private::configRecentlyUsedTags(QLatin1String("Recently Used Tags"));
75
constructRelatedImagesSQL(bool fromOrTo,DatabaseRelation::Type type,bool boolean)76 QString CoreDB::Private::constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean)
77 {
78 QString sql;
79
80 if (fromOrTo)
81 {
82 sql = QString::fromUtf8("SELECT object FROM ImageRelations "
83 "INNER JOIN Images ON ImageRelations.object=Images.id "
84 " WHERE subject=? %1 AND status<3 %2;");
85 }
86 else
87 {
88 sql = QString::fromUtf8("SELECT subject FROM ImageRelations "
89 "INNER JOIN Images ON ImageRelations.subject=Images.id "
90 " WHERE object=? %1 AND status<3 %2;");
91 }
92
93 if (type != DatabaseRelation::UndefinedType)
94 {
95 sql = sql.arg(QString::fromUtf8("AND type=?"));
96 }
97 else
98 {
99 sql = sql.arg(QString());
100 }
101
102 if (boolean)
103 {
104 sql = sql.arg(QString::fromUtf8("LIMIT 1"));
105 }
106 else
107 {
108 sql = sql.arg(QString());
109 }
110
111 return sql;
112 }
113
execRelatedImagesQuery(DbEngineSqlQuery & query,qlonglong id,DatabaseRelation::Type type)114 QList<qlonglong> CoreDB::Private::execRelatedImagesQuery(DbEngineSqlQuery& query, qlonglong id, DatabaseRelation::Type type)
115 {
116 QVariantList values;
117
118 if (type == DatabaseRelation::UndefinedType)
119 {
120 db->execSql(query, id, &values);
121 }
122 else
123 {
124 db->execSql(query, id, type, &values);
125 }
126
127 QList<qlonglong> imageIds;
128
129 if (values.isEmpty())
130 {
131 return imageIds;
132 }
133
134 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
135 {
136 imageIds << (*it).toInt();
137 }
138
139 return imageIds;
140 }
141
142 // --------------------------------------------------------
143
CoreDB(CoreDbBackend * const backend)144 CoreDB::CoreDB(CoreDbBackend* const backend)
145 : d(new Private)
146 {
147 d->db = backend;
148 readSettings();
149 }
150
~CoreDB()151 CoreDB::~CoreDB()
152 {
153 writeSettings();
154 delete d;
155 }
156
getAlbumRoots() const157 QList<AlbumRootInfo> CoreDB::getAlbumRoots() const
158 {
159 QList<AlbumRootInfo> list;
160 QList<QVariant> values;
161
162 d->db->execSql(QString::fromUtf8("SELECT id, label, status, type, identifier, specificPath FROM AlbumRoots;"), &values);
163
164 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
165 {
166 AlbumRootInfo info;
167 info.id = (*it).toInt();
168 ++it;
169 info.label = (*it).toString();
170 ++it;
171 info.status = (*it).toInt();
172 ++it;
173 info.type = (AlbumRoot::Type)(*it).toInt();
174 ++it;
175 info.identifier = (*it).toString();
176 ++it;
177 info.specificPath = (*it).toString();
178 ++it;
179
180 list << info;
181 }
182
183 return list;
184 }
185
addAlbumRoot(AlbumRoot::Type type,const QString & identifier,const QString & specificPath,const QString & label) const186 int CoreDB::addAlbumRoot(AlbumRoot::Type type, const QString& identifier, const QString& specificPath, const QString& label) const
187 {
188 QVariant id;
189 d->db->execSql(QString::fromUtf8("REPLACE INTO AlbumRoots (type, label, status, identifier, specificPath) "
190 "VALUES(?, ?, 0, ?, ?);"),
191 (int)type, label, identifier, specificPath, nullptr, &id);
192
193 d->db->recordChangeset(AlbumRootChangeset(id.toInt(), AlbumRootChangeset::Added));
194
195 return id.toInt();
196 }
197
deleteAlbumRoot(int rootId)198 void CoreDB::deleteAlbumRoot(int rootId)
199 {
200 d->db->execSql(QString::fromUtf8("DELETE FROM AlbumRoots WHERE id=?;"),
201 rootId);
202 QMap<QString, QVariant> parameters;
203 parameters.insert(QLatin1String(":albumRoot"), rootId);
204
205 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRoot")), parameters))
206 {
207 return;
208 }
209
210 d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::Deleted));
211 }
212
migrateAlbumRoot(int rootId,const QString & identifier)213 void CoreDB::migrateAlbumRoot(int rootId, const QString& identifier)
214 {
215 d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET identifier=? WHERE id=?;"),
216 identifier, rootId);
217 d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged));
218 }
219
setAlbumRootLabel(int rootId,const QString & newLabel)220 void CoreDB::setAlbumRootLabel(int rootId, const QString& newLabel)
221 {
222 d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET label=? WHERE id=?;"),
223 newLabel, rootId);
224 d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged));
225 }
226
changeAlbumRootType(int rootId,AlbumRoot::Type newType)227 void CoreDB::changeAlbumRootType(int rootId, AlbumRoot::Type newType)
228 {
229 d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET type=? WHERE id=?;"),
230 (int)newType, rootId);
231 d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged));
232 }
233
setAlbumRootPath(int rootId,const QString & newPath)234 void CoreDB::setAlbumRootPath(int rootId, const QString& newPath)
235 {
236 d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET specificPath=? WHERE id=?;"),
237 newPath, rootId);
238 d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged));
239 }
240
scanAlbums() const241 AlbumInfo::List CoreDB::scanAlbums() const
242 {
243 AlbumInfo::List aList;
244
245 QList<QVariant> values;
246 d->db->execSql(QString::fromUtf8("SELECT albumRoot, id, relativePath, date, caption, collection, icon "
247 "FROM Albums WHERE albumRoot != 0;"), // exclude stale albums
248 &values);
249
250 QString iconAlbumUrl, iconName;
251
252 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
253 {
254 AlbumInfo info;
255
256 info.albumRootId = (*it).toInt();
257 ++it;
258 info.id = (*it).toInt();
259 ++it;
260 info.relativePath = (*it).toString();
261 ++it;
262 info.date = (*it).toDate();
263 ++it;
264 info.caption = (*it).toString();
265 ++it;
266 info.category = (*it).toString();
267 ++it;
268 info.iconId = (*it).toLongLong();
269 ++it;
270
271 aList.append(info);
272 }
273
274 return aList;
275 }
276
scanTags() const277 TagInfo::List CoreDB::scanTags() const
278 {
279 TagInfo::List tList;
280
281 QList<QVariant> values;
282 d->db->execSql(QString::fromUtf8("SELECT id, pid, name, icon, iconkde FROM Tags;"),
283 &values);
284
285 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
286 {
287 TagInfo info;
288
289 info.id = (*it).toInt();
290 ++it;
291 info.pid = (*it).toInt();
292 ++it;
293 info.name = (*it).toString();
294 ++it;
295 info.iconId = (*it).toLongLong();
296 ++it;
297 info.icon = (*it).toString();
298 ++it;
299
300 tList.append(info);
301 }
302
303 return tList;
304 }
305
getTagInfo(int tagId) const306 TagInfo CoreDB::getTagInfo(int tagId) const
307 {
308 QList<QVariant> values;
309 d->db->execSql(QString::fromUtf8("SELECT id, pid, name, icon, iconkde WHERE id=? FROM Tags;"),
310 tagId, &values);
311
312 TagInfo info;
313
314 if (!values.isEmpty() && values.size() == 5)
315 {
316 QList<QVariant>::const_iterator it = values.constBegin();
317
318 info.id = (*it).toInt();
319 ++it;
320 info.pid = (*it).toInt();
321 ++it;
322 info.name = (*it).toString();
323 ++it;
324 info.iconId = (*it).toLongLong();
325 ++it;
326 info.icon = (*it).toString();
327 ++it;
328 }
329
330 return info;
331 }
332
scanSearches() const333 SearchInfo::List CoreDB::scanSearches() const
334 {
335 SearchInfo::List searchList;
336 QList<QVariant> values;
337
338 d->db->execSql(QString::fromUtf8("SELECT id, type, name, query FROM Searches;"),
339 &values);
340
341 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
342 {
343 SearchInfo info;
344
345 info.id = (*it).toInt();
346 ++it;
347 info.type = (DatabaseSearch::Type)(*it).toInt();
348 ++it;
349 info.name = (*it).toString();
350 ++it;
351 info.query = (*it).toString();
352 ++it;
353
354 searchList.append(info);
355 }
356
357 return searchList;
358 }
359
getAlbumShortInfos() const360 QList<AlbumShortInfo> CoreDB::getAlbumShortInfos() const
361 {
362 QList<QVariant> values;
363
364 d->db->execSql(QString::fromUtf8("SELECT id, relativePath, albumRoot FROM Albums ORDER BY id;"),
365 &values);
366
367 QList<AlbumShortInfo> albumList;
368
369 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
370 {
371 AlbumShortInfo info;
372
373 info.id = (*it).toInt();
374 ++it;
375 info.relativePath = (*it).toString();
376 ++it;
377 info.albumRootId = (*it).toInt();
378 ++it;
379
380 albumList << info;
381 }
382
383 return albumList;
384 }
385
getTagShortInfos() const386 QList<TagShortInfo> CoreDB::getTagShortInfos() const
387 {
388 QList<QVariant> values;
389
390 d->db->execSql(QString::fromUtf8("SELECT id, pid, name FROM Tags ORDER BY id;"),
391 &values);
392
393 QList<TagShortInfo> tagList;
394
395 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
396 {
397 TagShortInfo info;
398
399 info.id = (*it).toInt();
400 ++it;
401 info.pid = (*it).toInt();
402 ++it;
403 info.name = (*it).toString();
404 ++it;
405
406 tagList << info;
407 }
408
409 return tagList;
410 }
411
addAlbum(int albumRootId,const QString & relativePath,const QString & caption,const QDate & date,const QString & collection) const412 int CoreDB::addAlbum(int albumRootId, const QString& relativePath,
413 const QString& caption,
414 const QDate& date, const QString& collection) const
415 {
416 QVariant id;
417 QList<QVariant> boundValues;
418
419 boundValues << albumRootId << relativePath << date << caption << collection;
420
421 d->db->execSql(QString::fromUtf8("REPLACE INTO Albums (albumRoot, relativePath, date, caption, collection) "
422 "VALUES(?, ?, ?, ?, ?);"),
423 boundValues, nullptr, &id);
424
425 d->db->recordChangeset(AlbumChangeset(id.toInt(), AlbumChangeset::Added));
426
427 return id.toInt();
428 }
429
setAlbumCaption(int albumID,const QString & caption)430 void CoreDB::setAlbumCaption(int albumID, const QString& caption)
431 {
432 d->db->execSql(QString::fromUtf8("UPDATE Albums SET caption=? WHERE id=?;"),
433 caption, albumID);
434 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged));
435 }
436
setAlbumCategory(int albumID,const QString & category)437 void CoreDB::setAlbumCategory(int albumID, const QString& category)
438 {
439 // TODO : change "collection" property in DB ALbum table to "category"
440
441 d->db->execSql(QString::fromUtf8("UPDATE Albums SET collection=? WHERE id=?;"),
442 category, albumID);
443 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged));
444 }
445
setAlbumDate(int albumID,const QDate & date)446 void CoreDB::setAlbumDate(int albumID, const QDate& date)
447 {
448 d->db->execSql(QString::fromUtf8("UPDATE Albums SET date=? WHERE id=?;"),
449 date, albumID);
450 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged));
451 }
452
setAlbumModificationDate(int albumID,const QDateTime & modificationDate)453 void CoreDB::setAlbumModificationDate(int albumID, const QDateTime& modificationDate)
454 {
455 d->db->execSql(QString::fromUtf8("UPDATE Albums SET modificationDate=? WHERE id=?;"),
456 modificationDate, albumID);
457 }
458
setAlbumIcon(int albumID,qlonglong iconID)459 void CoreDB::setAlbumIcon(int albumID, qlonglong iconID)
460 {
461 if (iconID == 0)
462 {
463 d->db->execSql(QString::fromUtf8("UPDATE Albums SET icon=NULL WHERE id=?;"),
464 albumID);
465 }
466 else
467 {
468 d->db->execSql(QString::fromUtf8("UPDATE Albums SET icon=? WHERE id=?;"),
469 iconID, albumID);
470 }
471
472 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged));
473 }
474
deleteAlbum(int albumID)475 void CoreDB::deleteAlbum(int albumID)
476 {
477 QMap<QString, QVariant> parameters;
478 parameters.insert(QLatin1String(":albumId"), albumID);
479
480 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumID")),
481 parameters))
482 {
483 return;
484 }
485
486 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Deleted));
487 }
488
makeStaleAlbum(int albumID)489 void CoreDB::makeStaleAlbum(int albumID)
490 {
491 // We need to work around the table constraint, no we want to delete older stale albums with
492 // the same relativePath, and adjust relativePaths depending on albumRoot.
493
494 QList<QVariant> values;
495
496 // retrieve information
497
498 d->db->execSql(QString::fromUtf8("SELECT albumRoot, relativePath FROM Albums WHERE id=?;"),
499 albumID, &values);
500
501 if (values.isEmpty())
502 {
503 return;
504 }
505
506 // prepend albumRootId to relativePath. relativePath is unused and officially undefined after this call.
507
508 QString newRelativePath = values.at(0).toString() + QLatin1Char('-') + values.at(1).toString();
509
510 // delete older stale albums
511
512 QMap<QString, QVariant> parameters;
513 parameters.insert(QLatin1String(":albumRoot"), 0);
514 parameters.insert(QLatin1String(":relativePath"), newRelativePath);
515
516 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRootPath")),
517 parameters))
518 {
519 return;
520 }
521
522 // now do our update
523
524 d->db->setForeignKeyChecks(false);
525 d->db->execSql(QString::fromUtf8("UPDATE Albums SET albumRoot=0, relativePath=? WHERE id=?;"),
526 newRelativePath, albumID);
527
528 // for now, we make no distinction to deleteAlbums wrt to changeset
529
530 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Deleted));
531 d->db->setForeignKeyChecks(true);
532 }
533
deleteStaleAlbums()534 void CoreDB::deleteStaleAlbums()
535 {
536 QMap<QString, QVariant> parameters;
537 parameters.insert(QLatin1String(":albumRoot"), 0);
538
539 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRoot")),
540 parameters))
541 {
542 return;
543 }
544
545 // deliberately no changeset here, is done above
546 }
547
addTag(int parentTagID,const QString & name,const QString & iconKDE,qlonglong iconID) const548 int CoreDB::addTag(int parentTagID, const QString& name, const QString& iconKDE, qlonglong iconID) const
549 {
550 QVariant id;
551 QMap<QString, QVariant> parameters;
552
553 parameters.insert(QLatin1String(":tagPID"), parentTagID);
554 parameters.insert(QLatin1String(":tagname"), name);
555
556 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("InsertTag")),
557 parameters, nullptr , &id))
558 {
559 return -1;
560 }
561
562 if (!iconKDE.isEmpty())
563 {
564 d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=? WHERE id=?;"),
565 iconKDE, id.toInt());
566 }
567 else if (iconID == 0)
568 {
569 d->db->execSql(QString::fromUtf8("UPDATE Tags SET icon=NULL WHERE id=?;"),
570 id.toInt());
571 }
572 else
573 {
574 d->db->execSql(QString::fromUtf8("UPDATE Tags SET icon=? WHERE id=?;"),
575 iconID, id.toInt());
576 }
577
578 d->db->recordChangeset(TagChangeset(id.toInt(), TagChangeset::Added));
579
580 return id.toInt();
581 }
582
deleteTag(int tagID)583 void CoreDB::deleteTag(int tagID)
584 {
585 /*
586 QString("DELETE FROM Tags WHERE id=?;"), tagID
587 */
588
589 QMap<QString, QVariant> bindingMap;
590 bindingMap.insert(QLatin1String(":tagID"), tagID);
591
592 d->db->execDBAction(d->db->getDBAction(QLatin1String("DeleteTag")), bindingMap);
593 d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Deleted));
594 }
595
setTagIcon(int tagID,const QString & iconKDE,qlonglong iconID)596 void CoreDB::setTagIcon(int tagID, const QString& iconKDE, qlonglong iconID)
597 {
598 int dbIconID = iconKDE.isEmpty() ? iconID : 0;
599 QString dbIconKDE = iconKDE;
600
601 if (iconKDE.isEmpty() ||
602 (iconKDE.toLower() == QLatin1String("tag")) ||
603 (iconKDE.toLower() == QLatin1String("smiley")))
604 {
605 dbIconKDE.clear();
606 }
607
608 if (dbIconID == 0)
609 {
610 d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=?, icon=NULL WHERE id=?;"),
611 dbIconKDE, tagID);
612 }
613 else
614 {
615 d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=?, icon=? WHERE id=?;"),
616 dbIconKDE, dbIconID, tagID);
617 }
618
619 d->db->recordChangeset(TagChangeset(tagID, TagChangeset::IconChanged));
620 }
621
setTagParentID(int tagID,int newParentTagID)622 void CoreDB::setTagParentID(int tagID, int newParentTagID)
623 {
624 d->db->execSql(QString::fromUtf8("UPDATE Tags SET pid=? WHERE id=?;"),
625 newParentTagID, tagID);
626
627 d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Reparented));
628 }
629
getTagProperties(int tagId) const630 QList<TagProperty> CoreDB::getTagProperties(int tagId) const
631 {
632 QList<QVariant> values;
633
634 d->db->execSql(QString::fromUtf8("SELECT property, value FROM TagProperties WHERE tagid=?;"),
635 tagId, &values);
636
637 QList<TagProperty> properties;
638
639 if (values.isEmpty())
640 {
641 return properties;
642 }
643
644 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
645 {
646 TagProperty property;
647
648 property.tagId = tagId;
649
650 property.property = (*it).toString();
651 ++it;
652 property.value = (*it).toString();
653 ++it;
654
655 properties << property;
656 }
657
658 return properties;
659 }
660
getTagProperties(const QString & property) const661 QList<TagProperty> CoreDB::getTagProperties(const QString& property) const
662 {
663 QList<QVariant> values;
664
665 d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM TagProperties WHERE property=?;"),
666 property, &values);
667
668 QList<TagProperty> properties;
669
670 if (values.isEmpty())
671 {
672 return properties;
673 }
674
675 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
676 {
677 TagProperty prop;
678
679 prop.tagId = (*it).toInt();
680 ++it;
681 prop.property = (*it).toString();
682 ++it;
683 prop.value = (*it).toString();
684 ++it;
685
686 properties << prop;
687 }
688
689 return properties;
690 }
691
getTagProperties() const692 QList<TagProperty> CoreDB::getTagProperties() const
693 {
694 QList<QVariant> values;
695
696 d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM TagProperties ORDER BY tagid, property;"),
697 &values);
698
699 QList<TagProperty> properties;
700
701 if (values.isEmpty())
702 {
703 return properties;
704 }
705
706 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
707 {
708 TagProperty property;
709
710 property.tagId = (*it).toInt();
711 ++it;
712 property.property = (*it).toString();
713 ++it;
714 property.value = (*it).toString();
715 ++it;
716
717 properties << property;
718 }
719
720 return properties;
721 }
722
getTagsWithProperty(const QString & property) const723 QList<int> CoreDB::getTagsWithProperty(const QString& property) const
724 {
725 QList<QVariant> values;
726
727 d->db->execSql(QString::fromUtf8("SELECT DISTINCT tagid FROM TagProperties WHERE property=?;"),
728 property, &values);
729
730 QList<int> tagIds;
731
732 foreach (const QVariant& var, values)
733 {
734 tagIds << var.toInt();
735 }
736
737 return tagIds;
738 }
739
addTagProperty(int tagId,const QString & property,const QString & value)740 void CoreDB::addTagProperty(int tagId, const QString& property, const QString& value)
741 {
742 d->db->execSql(QString::fromUtf8("INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);"),
743 tagId, property, value);
744
745 d->db->recordChangeset(TagChangeset(tagId, TagChangeset::PropertiesChanged));
746 }
747
addTagProperty(const TagProperty & property)748 void CoreDB::addTagProperty(const TagProperty& property)
749 {
750 addTagProperty(property.tagId, property.property, property.value);
751 }
752
removeTagProperties(int tagId,const QString & property,const QString & value)753 void CoreDB::removeTagProperties(int tagId, const QString& property, const QString& value)
754 {
755 if (property.isNull())
756 {
757 d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=?;"),
758 tagId);
759 }
760 else if (value.isNull())
761 {
762 d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=? AND property=?;"),
763 tagId, property);
764 }
765 else
766 {
767 d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=? AND property=? AND value=?;"),
768 tagId, property, value);
769 }
770
771 d->db->recordChangeset(TagChangeset(tagId, TagChangeset::PropertiesChanged));
772 }
773
addSearch(DatabaseSearch::Type type,const QString & name,const QString & query) const774 int CoreDB::addSearch(DatabaseSearch::Type type, const QString& name, const QString& query) const
775 {
776 QVariant id;
777
778 if (!d->db->execSql(QString::fromUtf8("INSERT INTO Searches (type, name, query) VALUES(?, ?, ?);"),
779 type, name, query, nullptr, &id))
780 {
781 return -1;
782 }
783
784 d->db->recordChangeset(SearchChangeset(id.toInt(), SearchChangeset::Added));
785
786 return id.toInt();
787 }
788
updateSearch(int searchID,DatabaseSearch::Type type,const QString & name,const QString & query)789 void CoreDB::updateSearch(int searchID, DatabaseSearch::Type type,
790 const QString& name, const QString& query)
791 {
792 d->db->execSql(QString::fromUtf8("UPDATE Searches SET type=?, name=?, query=? WHERE id=?;"),
793 type, name, query, searchID);
794 d->db->recordChangeset(SearchChangeset(searchID, SearchChangeset::Changed));
795 }
796
deleteSearch(int searchID)797 void CoreDB::deleteSearch(int searchID)
798 {
799 d->db->execSql(QString::fromUtf8("DELETE FROM Searches WHERE id=?;"),
800 searchID);
801 d->db->recordChangeset(SearchChangeset(searchID, SearchChangeset::Deleted));
802 }
803
deleteSearches(DatabaseSearch::Type type)804 void CoreDB::deleteSearches(DatabaseSearch::Type type)
805 {
806 d->db->execSql(QString::fromUtf8("DELETE FROM Searches WHERE type=?;"),
807 type);
808 d->db->recordChangeset(SearchChangeset(0, SearchChangeset::Deleted));
809 }
810
getSearchQuery(int searchId) const811 QString CoreDB::getSearchQuery(int searchId) const
812 {
813 QList<QVariant> values;
814 d->db->execSql(QString::fromUtf8("SELECT query FROM Searches WHERE id=?;"),
815 searchId, &values);
816
817 if (values.isEmpty())
818 {
819 return QString();
820 }
821
822 return values.first().toString();
823 }
824
getSearchInfo(int searchId) const825 SearchInfo CoreDB::getSearchInfo(int searchId) const
826 {
827 SearchInfo info;
828
829 QList<QVariant> values;
830 d->db->execSql(QString::fromUtf8("SELECT id, type, name, query FROM Searches WHERE id=?;"),
831 searchId, &values);
832
833 if (values.size() == 4)
834 {
835 QList<QVariant>::const_iterator it = values.constBegin();
836 info.id = (*it).toInt();
837 ++it;
838 info.type = (DatabaseSearch::Type)(*it).toInt();
839 ++it;
840 info.name = (*it).toString();
841 ++it;
842 info.query = (*it).toString();
843 ++it;
844 }
845
846 return info;
847 }
848
setSetting(const QString & keyword,const QString & value)849 void CoreDB::setSetting(const QString& keyword, const QString& value)
850 {
851 d->db->execSql(QString::fromUtf8("REPLACE INTO Settings VALUES (?,?);"),
852 keyword, value);
853 }
854
getSetting(const QString & keyword) const855 QString CoreDB::getSetting(const QString& keyword) const
856 {
857 QList<QVariant> values;
858 d->db->execSql(QString::fromUtf8("SELECT value FROM Settings "
859 "WHERE keyword=?;"),
860 keyword, &values);
861
862 if (values.isEmpty())
863 {
864 return QString();
865 }
866
867 return values.first().toString();
868 }
869
870 /// helper method
joinMainAndUserFilterString(const QChar & sep,const QString & filter,const QString & userFilter)871 static QStringList joinMainAndUserFilterString(const QChar& sep, const QString& filter,
872 const QString& userFilter)
873 {
874 QStringList filterList;
875 QStringList userFilterList;
876
877 filterList = filter.split(sep, QString::SkipEmptyParts);
878 userFilterList = userFilter.split(sep, QString::SkipEmptyParts);
879
880 foreach (const QString& userFormat, userFilterList)
881 {
882 if (userFormat.startsWith(QLatin1Char('-')))
883 {
884 filterList.removeAll(userFormat.mid(1));
885 }
886 else
887 {
888 filterList << userFormat;
889 }
890 }
891
892 filterList.removeDuplicates();
893 filterList.sort();
894
895 return filterList;
896 }
897
getFilterSettings(QStringList * imageFilter,QStringList * videoFilter,QStringList * audioFilter)898 void CoreDB::getFilterSettings(QStringList* imageFilter, QStringList* videoFilter, QStringList* audioFilter)
899 {
900 QString imageFormats, videoFormats, audioFormats, userImageFormats, userVideoFormats, userAudioFormats;
901
902 if (imageFilter)
903 {
904 imageFormats = getSetting(QLatin1String("databaseImageFormats"));
905 userImageFormats = getSetting(QLatin1String("databaseUserImageFormats"));
906 *imageFilter = joinMainAndUserFilterString(QLatin1Char(';'), imageFormats, userImageFormats);
907 }
908
909 if (videoFilter)
910 {
911 videoFormats = getSetting(QLatin1String("databaseVideoFormats"));
912 userVideoFormats = getSetting(QLatin1String("databaseUserVideoFormats"));
913 *videoFilter = joinMainAndUserFilterString(QLatin1Char(';'), videoFormats, userVideoFormats);
914 }
915
916 if (audioFilter)
917 {
918 audioFormats = getSetting(QLatin1String("databaseAudioFormats"));
919 userAudioFormats = getSetting(QLatin1String("databaseUserAudioFormats"));
920 *audioFilter = joinMainAndUserFilterString(QLatin1Char(';'), audioFormats, userAudioFormats);
921 }
922 }
923
getUserFilterSettings(QString * imageFilterString,QString * videoFilterString,QString * audioFilterString)924 void CoreDB::getUserFilterSettings(QString* imageFilterString, QString* videoFilterString, QString* audioFilterString)
925 {
926 if (imageFilterString)
927 {
928 *imageFilterString = getSetting(QLatin1String("databaseUserImageFormats"));
929 }
930
931 if (videoFilterString)
932 {
933 *videoFilterString = getSetting(QLatin1String("databaseUserVideoFormats"));
934 }
935
936 if (audioFilterString)
937 {
938 *audioFilterString = getSetting(QLatin1String("databaseUserAudioFormats"));
939 }
940 }
941
getUserIgnoreDirectoryFilterSettings(QString * ignoreDirectoryFilterString)942 void CoreDB::getUserIgnoreDirectoryFilterSettings(QString* ignoreDirectoryFilterString)
943 {
944 *ignoreDirectoryFilterString = getSetting(QLatin1String("databaseUserIgnoreDirectoryFormats"));
945 }
946
getIgnoreDirectoryFilterSettings(QStringList * ignoreDirectoryFilter)947 void CoreDB::getIgnoreDirectoryFilterSettings(QStringList* ignoreDirectoryFilter)
948 {
949 QString ignoreDirectoryFormats, userIgnoreDirectoryFormats;
950
951 ignoreDirectoryFormats = getSetting(QLatin1String("databaseIgnoreDirectoryFormats"));
952 userIgnoreDirectoryFormats = getSetting(QLatin1String("databaseUserIgnoreDirectoryFormats"));
953 *ignoreDirectoryFilter = joinMainAndUserFilterString(QLatin1Char(';'),
954 ignoreDirectoryFormats, userIgnoreDirectoryFormats);
955 }
956
setFilterSettings(const QStringList & imageFilter,const QStringList & videoFilter,const QStringList & audioFilter)957 void CoreDB::setFilterSettings(const QStringList& imageFilter, const QStringList& videoFilter, const QStringList& audioFilter)
958 {
959 setSetting(QLatin1String("databaseImageFormats"), imageFilter.join(QLatin1Char(';')));
960 setSetting(QLatin1String("databaseVideoFormats"), videoFilter.join(QLatin1Char(';')));
961 setSetting(QLatin1String("databaseAudioFormats"), audioFilter.join(QLatin1Char(';')));
962 }
963
setIgnoreDirectoryFilterSettings(const QStringList & ignoreDirectoryFilter)964 void CoreDB::setIgnoreDirectoryFilterSettings(const QStringList& ignoreDirectoryFilter)
965 {
966 setSetting(QLatin1String("databaseIgnoreDirectoryFormats"), ignoreDirectoryFilter.join(QLatin1Char(';')));
967 }
968
setUserFilterSettings(const QStringList & imageFilter,const QStringList & videoFilter,const QStringList & audioFilter)969 void CoreDB::setUserFilterSettings(const QStringList& imageFilter,
970 const QStringList& videoFilter,
971 const QStringList& audioFilter)
972 {
973 setSetting(QLatin1String("databaseUserImageFormats"), imageFilter.join(QLatin1Char(';')));
974 setSetting(QLatin1String("databaseUserVideoFormats"), videoFilter.join(QLatin1Char(';')));
975 setSetting(QLatin1String("databaseUserAudioFormats"), audioFilter.join(QLatin1Char(';')));
976 }
977
setUserIgnoreDirectoryFilterSettings(const QStringList & ignoreDirectoryFilters)978 void CoreDB::setUserIgnoreDirectoryFilterSettings(const QStringList& ignoreDirectoryFilters)
979 {
980 qCDebug(DIGIKAM_DATABASE_LOG) << "CoreDB::setUserIgnoreDirectoryFilterSettings. "
981 "ignoreDirectoryFilterString: "
982 << ignoreDirectoryFilters.join(QLatin1Char(';'));
983
984 setSetting(QLatin1String("databaseUserIgnoreDirectoryFormats"), ignoreDirectoryFilters.join(QLatin1Char(';')));
985 }
986
databaseUuid()987 QUuid CoreDB::databaseUuid()
988 {
989 QString uuidString = getSetting(QLatin1String("databaseUUID"));
990 QUuid uuid = QUuid(uuidString);
991
992 if (uuidString.isNull() || uuid.isNull())
993 {
994 uuid = QUuid::createUuid();
995 setSetting(QLatin1String("databaseUUID"), uuid.toString());
996 }
997
998 return uuid;
999 }
1000
getUniqueHashVersion() const1001 int CoreDB::getUniqueHashVersion() const
1002 {
1003 if (d->uniqueHashVersion == -1)
1004 {
1005 QString v = getSetting(QLatin1String("uniqueHashVersion"));
1006
1007 if (v.isEmpty())
1008 {
1009 d->uniqueHashVersion = 1;
1010 }
1011 else
1012 {
1013 d->uniqueHashVersion = v.toInt();
1014 }
1015 }
1016
1017 return d->uniqueHashVersion;
1018 }
1019
isUniqueHashV2() const1020 bool CoreDB::isUniqueHashV2() const
1021 {
1022 return (getUniqueHashVersion() == 2);
1023 }
1024
setUniqueHashVersion(int version)1025 void CoreDB::setUniqueHashVersion(int version)
1026 {
1027 d->uniqueHashVersion = version;
1028 setSetting(QLatin1String("uniqueHashVersion"), QString::number(d->uniqueHashVersion));
1029 }
1030
getImageId(int albumID,const QString & name) const1031 qlonglong CoreDB::getImageId(int albumID, const QString& name) const
1032 {
1033 QList<QVariant> values;
1034
1035 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1036 "WHERE album=? AND name=?;"),
1037 albumID, name, &values);
1038
1039 if (values.isEmpty())
1040 {
1041 return -1;
1042 }
1043
1044 return values.first().toLongLong();
1045 }
1046
getImageIds(int albumID,const QString & name,DatabaseItem::Status status) const1047 QList<qlonglong> CoreDB::getImageIds(int albumID, const QString& name, DatabaseItem::Status status) const
1048 {
1049 QList<QVariant> values;
1050
1051 if (albumID == -1)
1052 {
1053 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1054 "WHERE album IS NULL AND name=? AND status=?;"),
1055 name, status, &values);
1056 }
1057 else
1058 {
1059 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1060 "WHERE album=? AND name=? AND status=?;"),
1061 albumID, name, status, &values);
1062 }
1063
1064 QList<qlonglong> items;
1065
1066 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
1067 {
1068 items << it->toLongLong();
1069 }
1070
1071 return items;
1072 }
1073
getImageIds(DatabaseItem::Status status) const1074 QList<qlonglong> CoreDB::getImageIds(DatabaseItem::Status status) const
1075 {
1076 QList<QVariant> values;
1077 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1078 "WHERE status=?;"),
1079 status, &values);
1080
1081 QList<qlonglong> imageIds;
1082
1083 foreach (const QVariant& object, values)
1084 {
1085 imageIds << object.toLongLong();
1086 }
1087
1088 return imageIds;
1089 }
1090
getImageIds(DatabaseItem::Status status,DatabaseItem::Category category) const1091 QList<qlonglong> CoreDB::getImageIds(DatabaseItem::Status status, DatabaseItem::Category category) const
1092 {
1093 QList<QVariant> values;
1094 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1095 "WHERE status=? AND category=?;"),
1096 status, category, &values);
1097
1098 QList<qlonglong> imageIds;
1099
1100 foreach (const QVariant& object, values)
1101 {
1102 imageIds << object.toLongLong();
1103 }
1104
1105 return imageIds;
1106 }
1107
findImageId(int albumID,const QString & name,DatabaseItem::Status status,DatabaseItem::Category category,qlonglong fileSize,const QString & uniqueHash) const1108 qlonglong CoreDB::findImageId(int albumID, const QString& name,
1109 DatabaseItem::Status status,
1110 DatabaseItem::Category category,
1111 qlonglong fileSize,
1112 const QString& uniqueHash) const
1113 {
1114 QList<QVariant> values;
1115 QVariantList boundValues;
1116
1117 // Add the standard bindings
1118
1119 boundValues << name << (int)status << (int)category
1120 << fileSize << uniqueHash;
1121
1122 // If the album id is -1, no album is assigned. Get all images with NULL album
1123
1124 if (albumID == -1)
1125 {
1126 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1127 "WHERE name=? AND status=? "
1128 "AND category=? AND fileSize=? "
1129 "AND uniqueHash=? AND album IS NULL;"),
1130 boundValues, &values);
1131 }
1132 else
1133 {
1134 boundValues << albumID;
1135
1136 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
1137 "WHERE name=? AND status=? "
1138 "AND category=? AND fileSize=? "
1139 "AND uniqueHash=? AND album=?;"),
1140 boundValues, &values);
1141 }
1142
1143 if (values.isEmpty())
1144 {
1145 return -1;
1146 }
1147
1148 // If there are several identical image ids,
1149 // probably use the last most recent one.
1150
1151 return values.last().toLongLong();
1152 }
1153
getItemTagNames(qlonglong imageID) const1154 QStringList CoreDB::getItemTagNames(qlonglong imageID) const
1155 {
1156 QList<QVariant> values;
1157
1158 d->db->execSql(QString::fromUtf8("SELECT name FROM Tags "
1159 "WHERE id IN (SELECT tagid FROM ImageTags "
1160 " WHERE imageid=?) "
1161 " ORDER BY name;"),
1162 imageID, &values);
1163
1164 QStringList names;
1165
1166 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
1167 {
1168 names << it->toString();
1169 }
1170
1171 return names;
1172 }
1173
getItemTagIDs(qlonglong imageID) const1174 QList<int> CoreDB::getItemTagIDs(qlonglong imageID) const
1175 {
1176 QList<QVariant> values;
1177
1178 d->db->execSql(QString::fromUtf8("SELECT tagid FROM ImageTags WHERE imageID=?;"),
1179 imageID, &values);
1180
1181 QList<int> ids;
1182
1183 if (values.isEmpty())
1184 {
1185 return ids;
1186 }
1187
1188 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
1189 {
1190 ids << it->toInt();
1191 }
1192
1193 return ids;
1194 }
1195
getItemsTagIDs(const QList<qlonglong> & imageIds) const1196 QVector<QList<int> > CoreDB::getItemsTagIDs(const QList<qlonglong>& imageIds) const
1197 {
1198 if (imageIds.isEmpty())
1199 {
1200 return QVector<QList<int> >();
1201 }
1202
1203 QVector<QList<int> > results(imageIds.size());
1204 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("SELECT tagid FROM ImageTags WHERE imageID=?;"));
1205 QVariantList values;
1206
1207 for (int i = 0 ; i < imageIds.size() ; ++i)
1208 {
1209 d->db->execSql(query, imageIds[i], &values);
1210 QList<int>& tagIds = results[i];
1211
1212 foreach (const QVariant& v, values)
1213 {
1214 tagIds << v.toInt();
1215 }
1216 }
1217
1218 return results;
1219 }
1220
getImageTagProperties(qlonglong imageId,int tagId) const1221 QList<ImageTagProperty> CoreDB::getImageTagProperties(qlonglong imageId, int tagId) const
1222 {
1223 QList<QVariant> values;
1224
1225 if (tagId == -1)
1226 {
1227 d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM ImageTagProperties "
1228 "WHERE imageid=?;"),
1229 imageId, &values);
1230 }
1231 else
1232 {
1233 d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM ImageTagProperties "
1234 "WHERE imageid=? AND tagid=?;"),
1235 imageId, tagId, &values);
1236 }
1237
1238 QList<ImageTagProperty> properties;
1239
1240 if (values.isEmpty())
1241 {
1242 return properties;
1243 }
1244
1245 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
1246 {
1247 ImageTagProperty property;
1248
1249 property.imageId = imageId;
1250
1251 property.tagId = (*it).toInt();
1252 ++it;
1253 property.property = (*it).toString();
1254 ++it;
1255 property.value = (*it).toString();
1256 ++it;
1257
1258 properties << property;
1259 }
1260
1261 return properties;
1262 }
1263
getTagIdsWithProperties(qlonglong imageId) const1264 QList<int> CoreDB::getTagIdsWithProperties(qlonglong imageId) const
1265 {
1266 QList<QVariant> values;
1267
1268 d->db->execSql(QString::fromUtf8("SELECT DISTINCT tagid FROM ImageTagProperties WHERE imageid=?;"),
1269 imageId, &values);
1270
1271 QList<int> tagIds;
1272
1273 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
1274 {
1275 tagIds << (*it).toInt();
1276 }
1277
1278 return tagIds;
1279 }
1280
addImageTagProperty(qlonglong imageId,int tagId,const QString & property,const QString & value)1281 void CoreDB::addImageTagProperty(qlonglong imageId, int tagId, const QString& property, const QString& value)
1282 {
1283 d->db->execSql(QString::fromUtf8("INSERT INTO ImageTagProperties (imageid, tagid, property, value) "
1284 "VALUES(?, ?, ?, ?);"),
1285 imageId, tagId, property, value);
1286
1287 d->db->recordChangeset(ImageTagChangeset(imageId, tagId, ImageTagChangeset::PropertiesChanged));
1288 }
1289
addImageTagProperty(const ImageTagProperty & property)1290 void CoreDB::addImageTagProperty(const ImageTagProperty& property)
1291 {
1292 addImageTagProperty(property.imageId, property.tagId, property.property, property.value);
1293 }
1294
removeImageTagProperties(qlonglong imageId,int tagId,const QString & property,const QString & value)1295 void CoreDB::removeImageTagProperties(qlonglong imageId, int tagId, const QString& property, const QString& value)
1296 {
1297 if (tagId == -1)
1298 {
1299 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties "
1300 "WHERE imageid=?;"),
1301 imageId);
1302 }
1303 else if (property.isNull())
1304 {
1305 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties "
1306 "WHERE imageid=? AND tagid=?;"),
1307 imageId, tagId);
1308 }
1309 else if (value.isNull())
1310 {
1311 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties "
1312 "WHERE imageid=? AND tagid=? AND property=?;"),
1313 imageId, tagId, property);
1314 }
1315 else
1316 {
1317 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties "
1318 "WHERE imageid=? AND tagid=? AND property=? AND value=?;"),
1319 imageId, tagId, property, value);
1320 }
1321
1322 d->db->recordChangeset(ImageTagChangeset(imageId, tagId, ImageTagChangeset::PropertiesChanged));
1323 }
1324
getItemShortInfo(qlonglong imageID) const1325 ItemShortInfo CoreDB::getItemShortInfo(qlonglong imageID) const
1326 {
1327 QList<QVariant> values;
1328
1329 d->db->execSql(QString::fromUtf8("SELECT Images.name, Albums.albumRoot, Albums.relativePath, Albums.id "
1330 "FROM Images "
1331 " INNER JOIN Albums ON Albums.id=Images.album "
1332 " WHERE Images.id=?;"),
1333 imageID, &values);
1334
1335 ItemShortInfo info;
1336
1337 if (!values.isEmpty())
1338 {
1339 info.id = imageID;
1340 info.itemName = values.at(0).toString();
1341 info.albumRootID = values.at(1).toInt();
1342 info.album = values.at(2).toString();
1343 info.albumID = values.at(3).toInt();
1344 }
1345
1346 return info;
1347 }
1348
getItemShortInfo(int albumRootId,const QString & relativePath,const QString & name) const1349 ItemShortInfo CoreDB::getItemShortInfo(int albumRootId, const QString& relativePath, const QString& name) const
1350 {
1351 QList<QVariant> values;
1352
1353 d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.id FROM Images "
1354 "INNER JOIN Albums ON Albums.id=Images.album "
1355 " WHERE name=? AND albumRoot=? AND relativePath=?;"),
1356 name, albumRootId, relativePath, &values);
1357
1358 ItemShortInfo info;
1359
1360 if (!values.isEmpty())
1361 {
1362 info.id = values.at(0).toLongLong();
1363 info.itemName = name;
1364 info.albumRootID = albumRootId;
1365 info.album = relativePath;
1366 info.albumID = values.at(1).toInt();
1367 }
1368
1369 return info;
1370 }
1371
hasTags(const QList<qlonglong> & imageIDList) const1372 bool CoreDB::hasTags(const QList<qlonglong>& imageIDList) const
1373 {
1374 QList<int> ids;
1375
1376 if (imageIDList.isEmpty())
1377 {
1378 return false;
1379 }
1380
1381 QList<QVariant> values;
1382 QList<QVariant> boundValues;
1383
1384 QString sql = QString::fromUtf8("SELECT COUNT(tagid) FROM ImageTags "
1385 "WHERE imageid=? ");
1386 boundValues << imageIDList.first();
1387
1388 QList<qlonglong>::const_iterator it = imageIDList.constBegin();
1389 ++it;
1390
1391 for ( ; it != imageIDList.constEnd() ; ++it)
1392 {
1393 sql += QString::fromUtf8(" OR imageid=? ");
1394 boundValues << (*it);
1395 }
1396
1397 sql += QString::fromUtf8(";");
1398 d->db->execSql(sql, boundValues, &values);
1399
1400 if (values.isEmpty() || (values.first().toInt() == 0))
1401 {
1402 return false;
1403 }
1404
1405 return true;
1406 }
1407
getItemCommonTagIDs(const QList<qlonglong> & imageIDList) const1408 QList<int> CoreDB::getItemCommonTagIDs(const QList<qlonglong>& imageIDList) const
1409 {
1410 QList<int> ids;
1411
1412 if (imageIDList.isEmpty())
1413 {
1414 return ids;
1415 }
1416
1417 QList<QVariant> values;
1418 QList<QVariant> boundValues;
1419
1420 QString sql = QString::fromUtf8("SELECT DISTINCT tagid FROM ImageTags "
1421 "WHERE imageid=? ");
1422 boundValues << imageIDList.first();
1423
1424 QList<qlonglong>::const_iterator it = imageIDList.constBegin();
1425 ++it;
1426
1427 for ( ; it != imageIDList.constEnd() ; ++it)
1428 {
1429 sql += QString::fromUtf8(" OR imageid=? ");
1430 boundValues << (*it);
1431 }
1432
1433 sql += QString::fromUtf8(";");
1434 d->db->execSql(sql, boundValues, &values);
1435
1436 if (values.isEmpty())
1437 {
1438 return ids;
1439 }
1440
1441 for (QList<QVariant>::const_iterator it2 = values.constBegin() ; it2 != values.constEnd() ; ++it2)
1442 {
1443 ids << it2->toInt();
1444 }
1445
1446 return ids;
1447 }
1448
getImagesFields(qlonglong imageID,DatabaseFields::Images fields) const1449 QVariantList CoreDB::getImagesFields(qlonglong imageID, DatabaseFields::Images fields) const
1450 {
1451 QVariantList values;
1452
1453 if (fields != DatabaseFields::ImagesNone)
1454 {
1455 QString query(QString::fromUtf8("SELECT "));
1456 QStringList fieldNames = imagesFieldList(fields);
1457 query += fieldNames.join(QString::fromUtf8(", "));
1458 query += QString::fromUtf8(" FROM Images WHERE id=?;");
1459
1460 d->db->execSql(query, imageID, &values);
1461
1462 if (fieldNames.size() != values.size())
1463 {
1464 return QVariantList();
1465 }
1466
1467 // Convert date times to QDateTime, they come as QString
1468
1469 if ((fields & DatabaseFields::ModificationDate))
1470 {
1471 int index = fieldNames.indexOf(QLatin1String("modificationDate"));
1472 values[index] = values.at(index).toDateTime();
1473 }
1474 }
1475
1476 return values;
1477 }
1478
getItemInformation(qlonglong imageID,DatabaseFields::ItemInformation fields) const1479 QVariantList CoreDB::getItemInformation(qlonglong imageID, DatabaseFields::ItemInformation fields) const
1480 {
1481 QVariantList values;
1482
1483 if (fields != DatabaseFields::ItemInformationNone)
1484 {
1485 QString query(QString::fromUtf8("SELECT "));
1486 QStringList fieldNames = imageInformationFieldList(fields);
1487 query += fieldNames.join(QString::fromUtf8(", "));
1488 query += QString::fromUtf8(" FROM ImageInformation WHERE imageid=?;");
1489
1490 d->db->execSql(query, imageID, &values);
1491
1492 if (fieldNames.size() != values.size())
1493 {
1494 return QVariantList();
1495 }
1496
1497 // Convert date times to QDateTime, they come as QString
1498
1499 if ((fields & DatabaseFields::CreationDate))
1500 {
1501 int index = fieldNames.indexOf(QLatin1String("creationDate"));
1502 values[index] = values.at(index).toDateTime();
1503 }
1504
1505 if ((fields & DatabaseFields::DigitizationDate))
1506 {
1507 int index = fieldNames.indexOf(QLatin1String("digitizationDate"));
1508 values[index] = values.at(index).toDateTime();
1509 }
1510 }
1511
1512 return values;
1513 }
1514
getImageMetadata(qlonglong imageID,DatabaseFields::ImageMetadata fields) const1515 QVariantList CoreDB::getImageMetadata(qlonglong imageID, DatabaseFields::ImageMetadata fields) const
1516 {
1517 QVariantList values;
1518
1519 if (fields != DatabaseFields::ImageMetadataNone)
1520 {
1521 QString query(QString::fromUtf8("SELECT "));
1522 QStringList fieldNames = imageMetadataFieldList(fields);
1523 query += fieldNames.join(QString::fromUtf8(", "));
1524 query += QString::fromUtf8(" FROM ImageMetadata WHERE imageid=?;");
1525
1526 d->db->execSql(query, imageID, &values);
1527 }
1528
1529 return values;
1530 }
1531
getVideoMetadata(qlonglong imageID,DatabaseFields::VideoMetadata fields) const1532 QVariantList CoreDB::getVideoMetadata(qlonglong imageID, DatabaseFields::VideoMetadata fields) const
1533 {
1534 QVariantList values;
1535
1536 if (fields != DatabaseFields::VideoMetadataNone)
1537 {
1538 QString query(QString::fromUtf8("SELECT "));
1539 QStringList fieldNames = videoMetadataFieldList(fields);
1540 query += fieldNames.join(QString::fromUtf8(", "));
1541 query += QString::fromUtf8(" FROM VideoMetadata WHERE imageid=?;");
1542
1543 d->db->execSql(query, imageID, &values);
1544 }
1545
1546 return values;
1547 }
1548
getItemPosition(qlonglong imageID,DatabaseFields::ItemPositions fields) const1549 QVariantList CoreDB::getItemPosition(qlonglong imageID, DatabaseFields::ItemPositions fields) const
1550 {
1551 QVariantList values;
1552
1553 if (fields != DatabaseFields::ItemPositionsNone)
1554 {
1555 QString query(QString::fromUtf8("SELECT "));
1556 QStringList fieldNames = imagePositionsFieldList(fields);
1557 query += fieldNames.join(QString::fromUtf8(", "));
1558 query += QString::fromUtf8(" FROM ImagePositions WHERE imageid=?;");
1559
1560 d->db->execSql(query, imageID, &values);
1561
1562 // For some reason REAL values may come as QString QVariants. Convert here.
1563
1564 if (values.size() == fieldNames.size() &&
1565 ((fields & DatabaseFields::LatitudeNumber) ||
1566 (fields & DatabaseFields::LongitudeNumber) ||
1567 (fields & DatabaseFields::Altitude) ||
1568 (fields & DatabaseFields::PositionOrientation) ||
1569 (fields & DatabaseFields::PositionTilt) ||
1570 (fields & DatabaseFields::PositionRoll) ||
1571 (fields & DatabaseFields::PositionAccuracy))
1572 )
1573 {
1574 for (int i = 0 ; i < values.size() ; ++i)
1575 {
1576 if (values.at(i).type() == QVariant::String &&
1577 (fieldNames.at(i) == QLatin1String("latitudeNumber") ||
1578 fieldNames.at(i) == QLatin1String("longitudeNumber") ||
1579 fieldNames.at(i) == QLatin1String("altitude") ||
1580 fieldNames.at(i) == QLatin1String("orientation") ||
1581 fieldNames.at(i) == QLatin1String("tilt") ||
1582 fieldNames.at(i) == QLatin1String("roll") ||
1583 fieldNames.at(i) == QLatin1String("accuracy"))
1584 )
1585 {
1586 if (!values.at(i).isNull())
1587 {
1588 values[i] = values.at(i).toDouble();
1589 }
1590 }
1591 }
1592 }
1593 }
1594
1595 return values;
1596 }
1597
getItemPositions(QList<qlonglong> imageIDs,DatabaseFields::ItemPositions fields) const1598 QVariantList CoreDB::getItemPositions(QList<qlonglong> imageIDs, DatabaseFields::ItemPositions fields) const
1599 {
1600 QVariantList values;
1601
1602 if (fields != DatabaseFields::ItemPositionsNone)
1603 {
1604 QString sql(QString::fromUtf8("SELECT "));
1605 QStringList fieldNames = imagePositionsFieldList(fields);
1606 sql += fieldNames.join(QString::fromUtf8(", "));
1607 sql += QString::fromUtf8(" FROM ImagePositions WHERE imageid=?;");
1608
1609 DbEngineSqlQuery query = d->db->prepareQuery(sql);
1610
1611 foreach (const qlonglong& imageid, imageIDs)
1612 {
1613 QVariantList singleValueList;
1614 d->db->execSql(query, imageid, &singleValueList);
1615 values << singleValueList;
1616 }
1617
1618 // For some reason REAL values may come as QString QVariants. Convert here.
1619
1620 if (values.size() == fieldNames.size() &&
1621 (fields & DatabaseFields::LatitudeNumber ||
1622 fields & DatabaseFields::LongitudeNumber ||
1623 fields & DatabaseFields::Altitude ||
1624 fields & DatabaseFields::PositionOrientation ||
1625 fields & DatabaseFields::PositionTilt ||
1626 fields & DatabaseFields::PositionRoll ||
1627 fields & DatabaseFields::PositionAccuracy)
1628 )
1629 {
1630 for (int i = 0 ; i < values.size() ; ++i)
1631 {
1632 if (values.at(i).type() == QVariant::String &&
1633 (fieldNames.at(i) == QLatin1String("latitudeNumber") ||
1634 fieldNames.at(i) == QLatin1String("longitudeNumber") ||
1635 fieldNames.at(i) == QLatin1String("altitude") ||
1636 fieldNames.at(i) == QLatin1String("orientation") ||
1637 fieldNames.at(i) == QLatin1String("tilt") ||
1638 fieldNames.at(i) == QLatin1String("roll") ||
1639 fieldNames.at(i) == QLatin1String("accuracy"))
1640 )
1641 {
1642 if (!values.at(i).isNull())
1643 {
1644 values[i] = values.at(i).toDouble();
1645 }
1646 }
1647 }
1648 }
1649 }
1650
1651 return values;
1652 }
1653
addItemInformation(qlonglong imageID,const QVariantList & infos,DatabaseFields::ItemInformation fields)1654 void CoreDB::addItemInformation(qlonglong imageID, const QVariantList& infos,
1655 DatabaseFields::ItemInformation fields)
1656 {
1657 if (fields == DatabaseFields::ItemInformationNone)
1658 {
1659 return;
1660 }
1661
1662 QString query(QString::fromUtf8("REPLACE INTO ImageInformation ( imageid, "));
1663
1664 QStringList fieldNames = imageInformationFieldList(fields);
1665
1666 Q_ASSERT(fieldNames.size() == infos.size());
1667
1668 query += fieldNames.join(QLatin1String(", "));
1669 query += QString::fromUtf8(" ) VALUES (");
1670 addBoundValuePlaceholders(query, infos.size() + 1);
1671 query += QString::fromUtf8(");");
1672
1673 QVariantList boundValues;
1674 boundValues << imageID;
1675 boundValues << infos;
1676
1677 d->db->execSql(query, boundValues);
1678 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields)));
1679 }
1680
changeItemInformation(qlonglong imageId,const QVariantList & infos,DatabaseFields::ItemInformation fields)1681 void CoreDB::changeItemInformation(qlonglong imageId, const QVariantList& infos,
1682 DatabaseFields::ItemInformation fields)
1683 {
1684 if (fields == DatabaseFields::ItemInformationNone)
1685 {
1686 return;
1687 }
1688
1689 QStringList fieldNames = imageInformationFieldList(fields);
1690
1691 d->db->execUpsertDBAction(QLatin1String("changeItemInformation"),
1692 imageId, fieldNames, infos);
1693 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields)));
1694 }
1695
addImageMetadata(qlonglong imageID,const QVariantList & infos,DatabaseFields::ImageMetadata fields)1696 void CoreDB::addImageMetadata(qlonglong imageID, const QVariantList& infos,
1697 DatabaseFields::ImageMetadata fields)
1698 {
1699 if (fields == DatabaseFields::ImageMetadataNone)
1700 {
1701 return;
1702 }
1703
1704 QString query(QString::fromUtf8("REPLACE INTO ImageMetadata ( imageid, "));
1705 QStringList fieldNames = imageMetadataFieldList(fields);
1706
1707 Q_ASSERT(fieldNames.size() == infos.size());
1708
1709 query += fieldNames.join(QLatin1String(", "));
1710 query += QString::fromUtf8(" ) VALUES (");
1711 addBoundValuePlaceholders(query, infos.size() + 1);
1712 query += QString::fromUtf8(");");
1713
1714 QVariantList boundValues;
1715 boundValues << imageID << infos;
1716
1717 d->db->execSql(query, boundValues);
1718 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields)));
1719 }
1720
changeImageMetadata(qlonglong imageId,const QVariantList & infos,DatabaseFields::ImageMetadata fields)1721 void CoreDB::changeImageMetadata(qlonglong imageId, const QVariantList& infos,
1722 DatabaseFields::ImageMetadata fields)
1723 {
1724 if (fields == DatabaseFields::ImageMetadataNone)
1725 {
1726 return;
1727 }
1728
1729 QString query(QString::fromUtf8("UPDATE ImageMetadata SET "));
1730
1731 QStringList fieldNames = imageMetadataFieldList(fields);
1732
1733 Q_ASSERT(fieldNames.size() == infos.size());
1734
1735 query += fieldNames.join(QString::fromUtf8("=?,"));
1736 query += QString::fromUtf8("=? WHERE imageid=?;");
1737
1738 QVariantList boundValues;
1739 boundValues << infos << imageId;
1740
1741 d->db->execSql(query, boundValues);
1742 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields)));
1743 }
1744
addVideoMetadata(qlonglong imageID,const QVariantList & infos,DatabaseFields::VideoMetadata fields)1745 void CoreDB::addVideoMetadata(qlonglong imageID, const QVariantList& infos, DatabaseFields::VideoMetadata fields)
1746 {
1747 if (fields == DatabaseFields::VideoMetadataNone)
1748 {
1749 return;
1750 }
1751
1752 QString query(QString::fromUtf8("REPLACE INTO VideoMetadata ( imageid, ")); // need to create this database
1753 QStringList fieldNames = videoMetadataFieldList(fields);
1754
1755 Q_ASSERT(fieldNames.size() == infos.size());
1756
1757 query += fieldNames.join(QLatin1String(", "));
1758 query += QString::fromUtf8(" ) VALUES (");
1759 addBoundValuePlaceholders(query, infos.size() + 1);
1760 query += QString::fromUtf8(");");
1761
1762 QVariantList boundValues;
1763 boundValues << imageID << infos;
1764
1765 d->db->execSql(query, boundValues);
1766 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields)));
1767 }
1768
changeVideoMetadata(qlonglong imageId,const QVariantList & infos,DatabaseFields::VideoMetadata fields)1769 void CoreDB::changeVideoMetadata(qlonglong imageId, const QVariantList& infos,
1770 DatabaseFields::VideoMetadata fields)
1771 {
1772 if (fields == DatabaseFields::VideoMetadataNone)
1773 {
1774 return;
1775 }
1776
1777 QString query(QString::fromUtf8("UPDATE VideoMetadata SET "));
1778 QStringList fieldNames = videoMetadataFieldList(fields);
1779
1780 Q_ASSERT(fieldNames.size() == infos.size());
1781
1782 query += fieldNames.join(QString::fromUtf8("=?,"));
1783 query += QString::fromUtf8("=? WHERE imageid=?;");
1784
1785 QVariantList boundValues;
1786 boundValues << infos << imageId;
1787
1788 d->db->execSql(query, boundValues);
1789 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields)));
1790 }
1791
addItemPosition(qlonglong imageID,const QVariantList & infos,DatabaseFields::ItemPositions fields)1792 void CoreDB::addItemPosition(qlonglong imageID, const QVariantList& infos, DatabaseFields::ItemPositions fields)
1793 {
1794 if (fields == DatabaseFields::ItemPositionsNone)
1795 {
1796 return;
1797 }
1798
1799 QString query(QString::fromUtf8("REPLACE INTO ImagePositions ( imageid, "));
1800 QStringList fieldNames = imagePositionsFieldList(fields);
1801
1802 Q_ASSERT(fieldNames.size() == infos.size());
1803
1804 query += fieldNames.join(QLatin1String(", "));
1805 query += QString::fromUtf8(" ) VALUES (");
1806 addBoundValuePlaceholders(query, infos.size() + 1);
1807 query += QString::fromUtf8(");");
1808
1809 QVariantList boundValues;
1810 boundValues << imageID << infos;
1811
1812 d->db->execSql(query, boundValues);
1813 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields)));
1814 }
1815
changeItemPosition(qlonglong imageId,const QVariantList & infos,DatabaseFields::ItemPositions fields)1816 void CoreDB::changeItemPosition(qlonglong imageId, const QVariantList& infos,
1817 DatabaseFields::ItemPositions fields)
1818 {
1819 if (fields == DatabaseFields::ItemPositionsNone)
1820 {
1821 return;
1822 }
1823
1824 QString query(QString::fromUtf8("UPDATE ImagePositions SET "));
1825 QStringList fieldNames = imagePositionsFieldList(fields);
1826
1827 Q_ASSERT(fieldNames.size() == infos.size());
1828
1829 query += fieldNames.join(QString::fromUtf8("=?,"));
1830 query += QString::fromUtf8("=? WHERE imageid=?;");
1831
1832 QVariantList boundValues;
1833 boundValues << infos << imageId;
1834
1835 d->db->execSql(query, boundValues);
1836 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields)));
1837 }
1838
removeItemPosition(qlonglong imageid)1839 void CoreDB::removeItemPosition(qlonglong imageid)
1840 {
1841 d->db->execSql(QString(QString::fromUtf8("DELETE FROM ImagePositions WHERE imageid=?;")),
1842 imageid);
1843
1844 d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::ItemPositionsAll)));
1845 }
1846
removeItemPositionAltitude(qlonglong imageid)1847 void CoreDB::removeItemPositionAltitude(qlonglong imageid)
1848 {
1849 d->db->execSql(QString(QString::fromUtf8("UPDATE ImagePositions SET altitude=NULL WHERE imageid=?;")),
1850 imageid);
1851
1852 d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::Altitude)));
1853 }
1854
getItemComments(qlonglong imageID) const1855 QList<CommentInfo> CoreDB::getItemComments(qlonglong imageID) const
1856 {
1857 QList<CommentInfo> list;
1858
1859 QList<QVariant> values;
1860 d->db->execSql(QString::fromUtf8("SELECT id, type, language, author, date, comment "
1861 "FROM ImageComments WHERE imageid=?;"),
1862 imageID, &values);
1863
1864 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
1865 {
1866 CommentInfo info;
1867 info.imageId = imageID;
1868
1869 info.id = (*it).toInt();
1870 ++it;
1871 info.type = (DatabaseComment::Type)(*it).toInt();
1872 ++it;
1873 info.language = (*it).toString();
1874 ++it;
1875 info.author = (*it).toString();
1876 ++it;
1877 info.date = (*it).toDateTime();
1878 ++it;
1879 info.comment = (*it).toString();
1880 ++it;
1881
1882 list << info;
1883 }
1884
1885 return list;
1886 }
1887
setImageComment(qlonglong imageID,const QString & comment,DatabaseComment::Type type,const QString & language,const QString & author,const QDateTime & date) const1888 int CoreDB::setImageComment(qlonglong imageID, const QString& comment, DatabaseComment::Type type,
1889 const QString& language, const QString& author, const QDateTime& date) const
1890 {
1891 QVariantList boundValues;
1892 boundValues << imageID << (int)type << language << author << date << comment;
1893
1894 QVariant id;
1895 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageComments "
1896 "( imageid, type, language, author, date, comment ) "
1897 " VALUES (?,?,?,?,?,?);"),
1898 boundValues, nullptr, &id);
1899
1900 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ItemCommentsAll)));
1901
1902 return id.toInt();
1903 }
1904
changeImageComment(int commentId,qlonglong imageID,const QVariantList & infos,DatabaseFields::ItemComments fields)1905 void CoreDB::changeImageComment(int commentId, qlonglong imageID, const QVariantList& infos, DatabaseFields::ItemComments fields)
1906 {
1907 if (fields == DatabaseFields::ItemCommentsNone)
1908 {
1909 return;
1910 }
1911
1912 QString query(QString::fromUtf8("UPDATE ImageComments SET "));
1913 QStringList fieldNames = imageCommentsFieldList(fields);
1914
1915 Q_ASSERT(fieldNames.size() == infos.size());
1916
1917 query += fieldNames.join(QString::fromUtf8("=?,"));
1918 query += QString::fromUtf8("=? WHERE id=?;");
1919
1920 QVariantList boundValues;
1921 boundValues << infos << commentId;
1922
1923 d->db->execSql(query, boundValues);
1924 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields)));
1925 }
1926
removeImageComment(int commentid,qlonglong imageid)1927 void CoreDB::removeImageComment(int commentid, qlonglong imageid)
1928 {
1929 d->db->execSql(QString::fromUtf8("DELETE FROM ImageComments WHERE id=?;"),
1930 commentid);
1931
1932 d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::ItemCommentsAll)));
1933 }
1934
getImageProperty(qlonglong imageID,const QString & property) const1935 QString CoreDB::getImageProperty(qlonglong imageID, const QString& property) const
1936 {
1937 QList<QVariant> values;
1938
1939 d->db->execSql(QString::fromUtf8("SELECT value FROM ImageProperties "
1940 "WHERE imageid=? AND property=?;"),
1941 imageID, property, &values);
1942
1943 if (values.isEmpty())
1944 {
1945 return QString();
1946 }
1947
1948 return values.first().toString();
1949 }
1950
setImageProperty(qlonglong imageID,const QString & property,const QString & value)1951 void CoreDB::setImageProperty(qlonglong imageID, const QString& property, const QString& value)
1952 {
1953 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageProperties "
1954 "(imageid, property, value) "
1955 " VALUES(?, ?, ?);"),
1956 imageID, property, value);
1957 }
1958
removeImageProperty(qlonglong imageID,const QString & property)1959 void CoreDB::removeImageProperty(qlonglong imageID, const QString& property)
1960 {
1961 d->db->execSql(QString::fromUtf8("DELETE FROM ImageProperties WHERE imageid=? AND property=?;"),
1962 imageID, property);
1963 }
1964
removeImagePropertyByName(const QString & property)1965 void CoreDB::removeImagePropertyByName(const QString& property)
1966 {
1967 d->db->execSql(QString::fromUtf8("DELETE FROM ImageProperties WHERE property=?;"),
1968 property);
1969 }
1970
getItemCopyright(qlonglong imageID,const QString & property) const1971 QList<CopyrightInfo> CoreDB::getItemCopyright(qlonglong imageID, const QString& property) const
1972 {
1973 QList<CopyrightInfo> list;
1974 QList<QVariant> values;
1975
1976 if (property.isNull())
1977 {
1978 d->db->execSql(QString::fromUtf8("SELECT property, value, extraValue FROM ImageCopyright "
1979 "WHERE imageid=?;"),
1980 imageID, &values);
1981 }
1982 else
1983 {
1984 d->db->execSql(QString::fromUtf8("SELECT property, value, extraValue FROM ImageCopyright "
1985 "WHERE imageid=? AND property=?;"),
1986 imageID, property, &values);
1987 }
1988
1989 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
1990 {
1991 CopyrightInfo info;
1992 info.id = imageID;
1993
1994 info.property = (*it).toString();
1995 ++it;
1996 info.value = (*it).toString();
1997 ++it;
1998 info.extraValue = (*it).toString();
1999 ++it;
2000
2001 list << info;
2002 }
2003
2004 return list;
2005 }
2006
setItemCopyrightProperty(qlonglong imageID,const QString & property,const QString & value,const QString & extraValue,CopyrightPropertyUnique uniqueness)2007 void CoreDB::setItemCopyrightProperty(qlonglong imageID, const QString& property,
2008 const QString& value, const QString& extraValue,
2009 CopyrightPropertyUnique uniqueness)
2010 {
2011 if (uniqueness == PropertyUnique)
2012 {
2013 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2014 "WHERE imageid=? AND property=?;"),
2015 imageID, property);
2016 }
2017 else if (uniqueness == PropertyExtraValueUnique)
2018 {
2019 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2020 "WHERE imageid=? AND property=? AND extraValue=?;"),
2021 imageID, property, extraValue);
2022 }
2023
2024 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageCopyright "
2025 "(imageid, property, value, extraValue) "
2026 " VALUES(?, ?, ?, ?);"),
2027 imageID, property, value, extraValue);
2028 }
2029
removeItemCopyrightProperties(qlonglong imageID,const QString & property,const QString & extraValue,const QString & value)2030 void CoreDB::removeItemCopyrightProperties(qlonglong imageID, const QString& property,
2031 const QString& extraValue, const QString& value)
2032 {
2033 int removeBy = 0;
2034
2035 if (!property.isNull())
2036 {
2037 ++removeBy;
2038 }
2039
2040 if (!extraValue.isNull())
2041 {
2042 ++removeBy;
2043 }
2044
2045 if (!value.isNull())
2046 {
2047 ++removeBy;
2048 }
2049
2050 switch (removeBy)
2051 {
2052 case 0:
2053 {
2054 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2055 "WHERE imageid=?;"),
2056 imageID);
2057 break;
2058 }
2059
2060 case 1:
2061 {
2062 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2063 "WHERE imageid=? AND property=?;"),
2064 imageID, property);
2065 break;
2066 }
2067
2068 case 2:
2069 {
2070 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2071 "WHERE imageid=? AND property=? AND extraValue=?;"),
2072 imageID, property, extraValue);
2073 break;
2074 }
2075
2076 case 3:
2077 {
2078 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright "
2079 "WHERE imageid=? AND property=? AND extraValue=? AND value=?;"),
2080 imageID, property, extraValue, value);
2081 break;
2082 }
2083 }
2084 }
2085
findByNameAndCreationDate(const QString & fileName,const QDateTime & creationDate) const2086 QList<qlonglong> CoreDB::findByNameAndCreationDate(const QString& fileName, const QDateTime& creationDate) const
2087 {
2088 QList<QVariant> values;
2089
2090 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
2091 "LEFT JOIN ImageInformation ON id=imageid "
2092 " WHERE name=? AND creationDate=? AND status<3;"),
2093 fileName, creationDate, &values);
2094
2095 QList<qlonglong> ids;
2096
2097 foreach (const QVariant& var, values)
2098 {
2099 ids << var.toLongLong();
2100 }
2101
2102 return ids;
2103 }
2104
hasImageHistory(qlonglong imageId) const2105 bool CoreDB::hasImageHistory(qlonglong imageId) const
2106 {
2107 QList<QVariant> values;
2108
2109 d->db->execSql(QString::fromUtf8("SELECT history FROM ImageHistory WHERE imageid=?;"),
2110 imageId, &values);
2111
2112 return !values.isEmpty();
2113 }
2114
getItemHistory(qlonglong imageId) const2115 ImageHistoryEntry CoreDB::getItemHistory(qlonglong imageId) const
2116 {
2117 QList<QVariant> values;
2118
2119 d->db->execSql(QString::fromUtf8("SELECT uuid, history FROM ImageHistory WHERE imageid=?;"),
2120 imageId, &values);
2121
2122 ImageHistoryEntry entry;
2123 entry.imageId = imageId;
2124
2125 if (values.count() != 2)
2126 {
2127 return entry;
2128 }
2129
2130 QList<QVariant>::const_iterator it = values.constBegin();
2131
2132 entry.uuid = (*it).toString();
2133 ++it;
2134 entry.history = (*it).toString();
2135 ++it;
2136
2137 return entry;
2138 }
2139
getItemsForUuid(const QString & uuid) const2140 QList<qlonglong> CoreDB::getItemsForUuid(const QString& uuid) const
2141 {
2142 QList<QVariant> values;
2143
2144 d->db->execSql(QString::fromUtf8("SELECT imageid FROM ImageHistory "
2145 "INNER JOIN Images ON imageid=id "
2146 " WHERE uuid=? AND status<3;"),
2147 uuid, &values);
2148
2149 QList<qlonglong> imageIds;
2150
2151 if (values.isEmpty())
2152 {
2153 return imageIds;
2154 }
2155
2156 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
2157 {
2158 imageIds << (*it).toInt();
2159 }
2160
2161 return imageIds;
2162 }
2163
getImageUuid(qlonglong imageId) const2164 QString CoreDB::getImageUuid(qlonglong imageId) const
2165 {
2166 QList<QVariant> values;
2167
2168 d->db->execSql(QString::fromUtf8("SELECT uuid FROM ImageHistory WHERE imageid=?;"),
2169 imageId, &values);
2170
2171 if (values.isEmpty())
2172 {
2173 return QString();
2174 }
2175
2176 QString uuid = values.first().toString();
2177
2178 if (uuid.isEmpty())
2179 {
2180 return QString();
2181 }
2182
2183 return uuid;
2184 }
2185
setItemHistory(qlonglong imageId,const QString & history)2186 void CoreDB::setItemHistory(qlonglong imageId, const QString& history)
2187 {
2188 d->db->execUpsertDBAction(QLatin1String("changeImageHistory"),
2189 imageId, QStringList() << QLatin1String("history"), QVariantList() << history);
2190 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::ImageHistory)));
2191 }
2192
setImageUuid(qlonglong imageId,const QString & uuid)2193 void CoreDB::setImageUuid(qlonglong imageId, const QString& uuid)
2194 {
2195 d->db->execUpsertDBAction(QLatin1String("changeImageHistory"),
2196 imageId, QStringList() << QLatin1String("uuid"), QVariantList() << uuid);
2197 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::ImageUUID)));
2198 }
2199
addImageRelation(qlonglong subjectId,qlonglong objectId,DatabaseRelation::Type type)2200 void CoreDB::addImageRelation(qlonglong subjectId, qlonglong objectId, DatabaseRelation::Type type)
2201 {
2202 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations (subject, object, type) "
2203 "VALUES (?, ?, ?);"),
2204 subjectId, objectId, type);
2205 d->db->recordChangeset(ImageChangeset(QList<qlonglong>() << subjectId << objectId,
2206 DatabaseFields::Set(DatabaseFields::ImageRelations)));
2207 }
2208
addImageRelations(const QList<qlonglong> & subjectIds,const QList<qlonglong> & objectIds,DatabaseRelation::Type type)2209 void CoreDB::addImageRelations(const QList<qlonglong>& subjectIds,
2210 const QList<qlonglong>& objectIds, DatabaseRelation::Type type)
2211 {
2212 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("REPLACE INTO ImageRelations (subject, object, type) "
2213 "VALUES (?, ?, ?);"));
2214
2215 QVariantList subjects, objects, types;
2216
2217 for (int i = 0 ; i < subjectIds.size() ; ++i)
2218 {
2219 subjects << subjectIds.at(i);
2220 objects << objectIds.at(i);
2221 types << type;
2222 }
2223
2224 query.addBindValue(subjects);
2225 query.addBindValue(objects);
2226 query.addBindValue(types);
2227 d->db->execBatch(query);
2228 d->db->recordChangeset(ImageChangeset(subjectIds + objectIds,
2229 DatabaseFields::Set(DatabaseFields::ImageRelations)));
2230 }
2231
2232
addImageRelation(const ImageRelation & relation)2233 void CoreDB::addImageRelation(const ImageRelation& relation)
2234 {
2235 addImageRelation(relation.subjectId, relation.objectId, relation.type);
2236 }
2237
removeImageRelation(qlonglong subjectId,qlonglong objectId,DatabaseRelation::Type type)2238 void CoreDB::removeImageRelation(qlonglong subjectId, qlonglong objectId, DatabaseRelation::Type type)
2239 {
2240 d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE subject=? AND object=? AND type=?;"),
2241 subjectId, objectId, type);
2242 d->db->recordChangeset(ImageChangeset(QList<qlonglong>() << subjectId << objectId,
2243 DatabaseFields::Set(DatabaseFields::ImageRelations)));
2244 }
2245
removeImageRelation(const ImageRelation & relation)2246 void CoreDB::removeImageRelation(const ImageRelation& relation)
2247 {
2248 removeImageRelation(relation.subjectId, relation.objectId, relation.type);
2249 }
2250
removeAllImageRelationsTo(qlonglong objectId,DatabaseRelation::Type type) const2251 QList<qlonglong> CoreDB::removeAllImageRelationsTo(qlonglong objectId, DatabaseRelation::Type type) const
2252 {
2253 QList<qlonglong> affected = getImagesRelatingTo(objectId, type);
2254
2255 if (affected.isEmpty())
2256 {
2257 return affected;
2258 }
2259
2260 d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE object=? AND type=?;"),
2261 objectId, type);
2262 d->db->recordChangeset(ImageChangeset(QList<qlonglong>() << affected << objectId,
2263 DatabaseFields::Set(DatabaseFields::ImageRelations)));
2264
2265 return affected;
2266 }
2267
removeAllImageRelationsFrom(qlonglong subjectId,DatabaseRelation::Type type) const2268 QList<qlonglong> CoreDB::removeAllImageRelationsFrom(qlonglong subjectId, DatabaseRelation::Type type) const
2269 {
2270 QList<qlonglong> affected = getImagesRelatedFrom(subjectId, type);
2271
2272 if (affected.isEmpty())
2273 {
2274 return affected;
2275 }
2276
2277 d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE subject=? AND type=?;"),
2278 subjectId, type);
2279 d->db->recordChangeset(ImageChangeset(QList<qlonglong>() << affected << subjectId,
2280 DatabaseFields::Set(DatabaseFields::ImageRelations)));
2281
2282 return affected;
2283 }
2284
getImagesRelatedFrom(qlonglong subjectId,DatabaseRelation::Type type) const2285 QList<qlonglong> CoreDB::getImagesRelatedFrom(qlonglong subjectId, DatabaseRelation::Type type) const
2286 {
2287 return getRelatedImages(subjectId, true, type, false);
2288 }
2289
getImagesRelatedFrom(const QList<qlonglong> & subjectIds,DatabaseRelation::Type type) const2290 QVector<QList<qlonglong> > CoreDB::getImagesRelatedFrom(const QList<qlonglong>& subjectIds, DatabaseRelation::Type type) const
2291 {
2292 return getRelatedImages(subjectIds, true, type, false);
2293 }
2294
hasImagesRelatedFrom(qlonglong subjectId,DatabaseRelation::Type type) const2295 bool CoreDB::hasImagesRelatedFrom(qlonglong subjectId, DatabaseRelation::Type type) const
2296 {
2297 // returns 0 or 1 item in list
2298
2299 return !getRelatedImages(subjectId, true, type, true).isEmpty();
2300 }
2301
getImagesRelatingTo(qlonglong objectId,DatabaseRelation::Type type) const2302 QList<qlonglong> CoreDB::getImagesRelatingTo(qlonglong objectId, DatabaseRelation::Type type) const
2303 {
2304 return getRelatedImages(objectId, false, type, false);
2305 }
2306
getImagesRelatingTo(const QList<qlonglong> & objectIds,DatabaseRelation::Type type) const2307 QVector<QList<qlonglong> > CoreDB::getImagesRelatingTo(const QList<qlonglong>& objectIds, DatabaseRelation::Type type) const
2308 {
2309 return getRelatedImages(objectIds, false, type, false);
2310 }
2311
hasImagesRelatingTo(qlonglong objectId,DatabaseRelation::Type type) const2312 bool CoreDB::hasImagesRelatingTo(qlonglong objectId, DatabaseRelation::Type type) const
2313 {
2314 // returns 0 or 1 item in list
2315
2316 return !getRelatedImages(objectId, false, type, true).isEmpty();
2317 }
2318
getRelatedImages(qlonglong id,bool fromOrTo,DatabaseRelation::Type type,bool boolean) const2319 QList<qlonglong> CoreDB::getRelatedImages(qlonglong id, bool fromOrTo, DatabaseRelation::Type type, bool boolean) const
2320 {
2321 QString sql = d->constructRelatedImagesSQL(fromOrTo, type, boolean);
2322 DbEngineSqlQuery query = d->db->prepareQuery(sql);
2323
2324 return d->execRelatedImagesQuery(query, id, type);
2325 }
2326
getRelatedImages(QList<qlonglong> ids,bool fromOrTo,DatabaseRelation::Type type,bool boolean) const2327 QVector<QList<qlonglong> > CoreDB::getRelatedImages(QList<qlonglong> ids,
2328 bool fromOrTo, DatabaseRelation::Type type, bool boolean) const
2329 {
2330 if (ids.isEmpty())
2331 {
2332 return QVector<QList<qlonglong> >();
2333 }
2334
2335 QVector<QList<qlonglong> > result(ids.size());
2336
2337 QString sql = d->constructRelatedImagesSQL(fromOrTo, type, boolean);
2338 DbEngineSqlQuery query = d->db->prepareQuery(sql);
2339
2340 for (int i = 0 ; i < ids.size() ; ++i)
2341 {
2342 result[i] = d->execRelatedImagesQuery(query, ids[i], type);
2343 }
2344
2345 return result;
2346 }
2347
getRelationCloud(qlonglong imageId,DatabaseRelation::Type type) const2348 QList<QPair<qlonglong, qlonglong> > CoreDB::getRelationCloud(qlonglong imageId, DatabaseRelation::Type type) const
2349 {
2350 QSet<qlonglong> todo, done;
2351 QSet<QPair<qlonglong, qlonglong> > pairs;
2352 todo << imageId;
2353
2354 QString sql = QString::fromUtf8("SELECT subject, object FROM ImageRelations "
2355 "INNER JOIN Images AS SubjectImages "
2356 "ON ImageRelations.subject=SubjectImages.id "
2357 " INNER JOIN Images AS ObjectImages "
2358 " ON ImageRelations.object=ObjectImages.id "
2359 " WHERE (subject=? OR object=?) %1 "
2360 " AND SubjectImages.status<3 "
2361 " AND ObjectImages.status<3;");
2362
2363 if (type == DatabaseRelation::UndefinedType)
2364 {
2365 sql = sql.arg(QString());
2366 }
2367 else
2368 {
2369 sql = sql.arg(QString::fromUtf8("AND type=?"));
2370 }
2371
2372 DbEngineSqlQuery query = d->db->prepareQuery(sql);
2373
2374 QList<QVariant> values;
2375 qlonglong subject, object;
2376
2377 while (!todo.isEmpty())
2378 {
2379 qlonglong id = *todo.begin();
2380 todo.erase(todo.begin());
2381 done << id;
2382
2383 if (type == DatabaseRelation::UndefinedType)
2384 {
2385 d->db->execSql(query, id, id, &values);
2386 }
2387 else
2388 {
2389 d->db->execSql(query, id, id, type, &values);
2390 }
2391
2392 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
2393 {
2394 subject = (*it).toLongLong();
2395 ++it;
2396 object = (*it).toLongLong();
2397 ++it;
2398
2399 pairs << qMakePair(subject, object);
2400
2401 if (!done.contains(subject))
2402 {
2403 todo << subject;
2404 }
2405
2406 if (!done.contains(object))
2407 {
2408 todo << object;
2409 }
2410 }
2411 }
2412
2413 return pairs.values();
2414 }
2415
getOneRelatedImageEach(const QList<qlonglong> & ids,DatabaseRelation::Type type) const2416 QList<qlonglong> CoreDB::getOneRelatedImageEach(const QList<qlonglong>& ids, DatabaseRelation::Type type) const
2417 {
2418 QString sql = QString::fromUtf8("SELECT subject, object FROM ImageRelations "
2419 "INNER JOIN Images AS SubjectImages "
2420 "ON ImageRelations.subject=SubjectImages.id "
2421 " INNER JOIN Images AS ObjectImages "
2422 " ON ImageRelations.object=ObjectImages.id "
2423 " WHERE ( (subject=? AND ObjectImages.status<3) "
2424 " OR (object=? AND SubjectImages.status<3) ) "
2425 " %1 LIMIT 1;");
2426
2427 if (type == DatabaseRelation::UndefinedType)
2428 {
2429 sql = sql.arg(QString());
2430 }
2431 else
2432 {
2433 sql = sql.arg(QString::fromUtf8("AND type=?"));
2434 }
2435
2436 DbEngineSqlQuery query = d->db->prepareQuery(sql);
2437 QSet<qlonglong> result;
2438 QList<QVariant> values;
2439
2440 foreach (const qlonglong& id, ids)
2441 {
2442 if (type == DatabaseRelation::UndefinedType)
2443 {
2444 d->db->execSql(query, id, id, &values);
2445 }
2446 else
2447 {
2448 d->db->execSql(query, id, id, type, &values);
2449 }
2450
2451 if (values.size() != 2)
2452 {
2453 continue;
2454 }
2455
2456 // one of subject and object is the given id, the other our result
2457
2458 if (values.first() != id)
2459 {
2460 result << values.first().toLongLong();
2461 }
2462 else
2463 {
2464 result << values.last().toLongLong();
2465 }
2466 }
2467
2468 return result.values();
2469 }
2470
getRelatedImagesToByType(DatabaseRelation::Type type) const2471 QList<qlonglong> CoreDB::getRelatedImagesToByType(DatabaseRelation::Type type) const
2472 {
2473 QList<QVariant> values;
2474
2475 d->db->execSql(QString::fromUtf8("SELECT object FROM ImageRelations "
2476 "INNER JOIN Images AS SubjectImages "
2477 "ON ImageRelations.subject=SubjectImages.id "
2478 " INNER JOIN Images AS ObjectImages "
2479 " ON ImageRelations.object=ObjectImages.id "
2480 " WHERE type=? "
2481 " AND SubjectImages.status<3 "
2482 " AND ObjectImages.status<3;"),
2483 (int)type, &values);
2484
2485 QList<qlonglong> imageIds;
2486
2487 if (values.isEmpty())
2488 {
2489 return imageIds;
2490 }
2491
2492 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
2493 {
2494 imageIds << (*it).toLongLong();
2495 }
2496
2497 return imageIds;
2498 }
2499
getItemsURLsWithTag(int tagId) const2500 QStringList CoreDB::getItemsURLsWithTag(int tagId) const
2501 {
2502 QList<QVariant> values;
2503 QList<QVariant> boundValues;
2504
2505 QString query(QString::fromUtf8("SELECT DISTINCT Albums.albumRoot, Albums.relativePath, Images.name FROM Images "
2506 "LEFT JOIN ImageTags ON Images.id=ImageTags.imageid "
2507 "INNER JOIN Albums ON Albums.id=Images.album "
2508 " WHERE Images.status=1 AND Images.category=1 AND "));
2509
2510 if ((tagId == TagsCache::instance()->tagForPickLabel(NoPickLabel)) ||
2511 (tagId == TagsCache::instance()->tagForColorLabel(NoColorLabel)))
2512 {
2513 query += QString::fromUtf8("( ImageTags.tagid=? OR ImageTags.tagid "
2514 "NOT BETWEEN ? AND ? OR ImageTags.tagid IS NULL );");
2515 boundValues << tagId;
2516
2517 if (tagId == TagsCache::instance()->tagForPickLabel(NoPickLabel))
2518 {
2519 boundValues << TagsCache::instance()->tagForPickLabel(FirstPickLabel);
2520 boundValues << TagsCache::instance()->tagForPickLabel(LastPickLabel);
2521 }
2522 else
2523 {
2524 boundValues << TagsCache::instance()->tagForColorLabel(FirstColorLabel);
2525 boundValues << TagsCache::instance()->tagForColorLabel(LastColorLabel);
2526 }
2527 }
2528 else
2529 {
2530 query += QString::fromUtf8("ImageTags.tagid=?;");
2531 boundValues << tagId;
2532 }
2533
2534 d->db->execSql(query, boundValues, &values);
2535
2536 QStringList urls;
2537 QString albumRootPath, relativePath, name;
2538
2539 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
2540 {
2541 albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt());
2542 ++it;
2543 relativePath = (*it).toString();
2544 ++it;
2545 name = (*it).toString();
2546 ++it;
2547
2548 if (relativePath == QLatin1String("/"))
2549 {
2550 urls << albumRootPath + relativePath + name;
2551 }
2552 else
2553 {
2554 urls << albumRootPath + relativePath + QLatin1Char('/') + name;
2555 }
2556 }
2557
2558 return urls;
2559 }
2560
getDirtyOrMissingFaceImageUrls() const2561 QStringList CoreDB::getDirtyOrMissingFaceImageUrls() const
2562 {
2563 QList<QVariant> values;
2564
2565 d->db->execSql(QString::fromUtf8("SELECT Albums.albumRoot, Albums.relativePath, Images.name FROM Images "
2566 "LEFT JOIN ImageScannedMatrix ON Images.id=ImageScannedMatrix.imageid "
2567 "INNER JOIN Albums ON Albums.id=Images.album "
2568 " WHERE Images.status=1 AND Images.category=1 AND "
2569 " ( ImageScannedMatrix.imageid IS NULL "
2570 " OR Images.modificationDate != ImageScannedMatrix.modificationDate "
2571 " OR Images.uniqueHash != ImageScannedMatrix.uniqueHash );"),
2572 &values);
2573
2574 QStringList urls;
2575 QString albumRootPath, relativePath, name;
2576
2577 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
2578 {
2579 albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt());
2580 ++it;
2581 relativePath = (*it).toString();
2582 ++it;
2583 name = (*it).toString();
2584 ++it;
2585
2586 if (relativePath == QLatin1String("/"))
2587 {
2588 urls << albumRootPath + relativePath + name;
2589 }
2590 else
2591 {
2592 urls << albumRootPath + relativePath + QLatin1Char('/') + name;
2593 }
2594 }
2595
2596 return urls;
2597 }
2598
getIdenticalFiles(qlonglong id) const2599 QList<ItemScanInfo> CoreDB::getIdenticalFiles(qlonglong id) const
2600 {
2601 if (!id)
2602 {
2603 return QList<ItemScanInfo>();
2604 }
2605
2606 QList<QVariant> values;
2607
2608 // retrieve unique hash and file size
2609
2610 d->db->execSql(QString::fromUtf8("SELECT uniqueHash, fileSize FROM Images WHERE id=?;"),
2611 id, &values);
2612
2613 if (values.isEmpty())
2614 {
2615 return QList<ItemScanInfo>();
2616 }
2617
2618 QString uniqueHash = values.at(0).toString();
2619 qlonglong fileSize = values.at(1).toLongLong();
2620
2621 return getIdenticalFiles(uniqueHash, fileSize, id);
2622 }
2623
getIdenticalFiles(const QString & uniqueHash,qlonglong fileSize,qlonglong sourceId) const2624 QList<ItemScanInfo> CoreDB::getIdenticalFiles(const QString& uniqueHash, qlonglong fileSize, qlonglong sourceId) const
2625 {
2626 // enforce validity
2627
2628 if (uniqueHash.isEmpty() || (fileSize <= 0))
2629 {
2630 return QList<ItemScanInfo>();
2631 }
2632
2633 QList<QVariant> values;
2634
2635 // find items with same fingerprint
2636
2637 d->db->execSql(QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize "
2638 "FROM Images WHERE fileSize=? AND uniqueHash=? AND album IS NOT NULL;"),
2639 fileSize, uniqueHash, &values);
2640
2641 QList<ItemScanInfo> list;
2642
2643 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
2644 {
2645 ItemScanInfo info;
2646
2647 info.id = (*it).toLongLong();
2648 ++it;
2649 info.albumID = (*it).toInt();
2650 ++it;
2651 info.itemName = (*it).toString();
2652 ++it;
2653 info.status = (DatabaseItem::Status)(*it).toInt();
2654 ++it;
2655 info.category = (DatabaseItem::Category)(*it).toInt();
2656 ++it;
2657 info.modificationDate = (*it).toDateTime();
2658 ++it;
2659 info.fileSize = (*it).toLongLong();
2660 ++it;
2661
2662 // exclude one source id from list
2663
2664 if (sourceId == info.id)
2665 {
2666 continue;
2667 }
2668
2669 // same for all here, per definition
2670
2671 info.uniqueHash = uniqueHash;
2672
2673 list << info;
2674 }
2675
2676 return list;
2677 }
2678
imagesFieldList(DatabaseFields::Images fields)2679 QStringList CoreDB::imagesFieldList(DatabaseFields::Images fields)
2680 {
2681 // adds no spaces at beginning or end
2682
2683 QStringList list;
2684
2685 if (fields & DatabaseFields::Album)
2686 {
2687 list << QLatin1String("album");
2688 }
2689
2690 if (fields & DatabaseFields::Name)
2691 {
2692 list << QLatin1String("name");
2693 }
2694
2695 if (fields & DatabaseFields::Status)
2696 {
2697 list << QLatin1String("status");
2698 }
2699
2700 if (fields & DatabaseFields::Category)
2701 {
2702 list << QLatin1String("category");
2703 }
2704
2705 if (fields & DatabaseFields::ModificationDate)
2706 {
2707 list << QLatin1String("modificationDate");
2708 }
2709
2710 if (fields & DatabaseFields::FileSize)
2711 {
2712 list << QLatin1String("fileSize");
2713 }
2714
2715 if (fields & DatabaseFields::UniqueHash)
2716 {
2717 list << QLatin1String("uniqueHash");
2718 }
2719
2720 if (fields & DatabaseFields::ManualOrder)
2721 {
2722 list << QLatin1String("manualOrder");
2723 }
2724
2725 return list;
2726 }
2727
imageInformationFieldList(DatabaseFields::ItemInformation fields)2728 QStringList CoreDB::imageInformationFieldList(DatabaseFields::ItemInformation fields)
2729 {
2730 // adds no spaces at beginning or end
2731
2732 QStringList list;
2733
2734 if (fields & DatabaseFields::Rating)
2735 {
2736 list << QLatin1String("rating");
2737 }
2738
2739 if (fields & DatabaseFields::CreationDate)
2740 {
2741 list << QLatin1String("creationDate");
2742 }
2743
2744 if (fields & DatabaseFields::DigitizationDate)
2745 {
2746 list << QLatin1String("digitizationDate");
2747 }
2748
2749 if (fields & DatabaseFields::Orientation)
2750 {
2751 list << QLatin1String("orientation");
2752 }
2753
2754 if (fields & DatabaseFields::Width)
2755 {
2756 list << QLatin1String("width");
2757 }
2758
2759 if (fields & DatabaseFields::Height)
2760 {
2761 list << QLatin1String("height");
2762 }
2763
2764 if (fields & DatabaseFields::Format)
2765 {
2766 list << QLatin1String("format");
2767 }
2768
2769 if (fields & DatabaseFields::ColorDepth)
2770 {
2771 list << QLatin1String("colorDepth");
2772 }
2773
2774 if (fields & DatabaseFields::ColorModel)
2775 {
2776 list << QLatin1String("colorModel");
2777 }
2778
2779 return list;
2780 }
2781
videoMetadataFieldList(DatabaseFields::VideoMetadata fields)2782 QStringList CoreDB::videoMetadataFieldList(DatabaseFields::VideoMetadata fields)
2783 {
2784 // adds no spaces at beginning or end
2785
2786 QStringList list;
2787
2788 if (fields & DatabaseFields::AspectRatio)
2789 {
2790 list << QLatin1String("aspectRatio");
2791 }
2792
2793 if (fields & DatabaseFields::AudioBitRate)
2794 {
2795 list << QLatin1String("audioBitRate");
2796 }
2797
2798 if (fields & DatabaseFields::AudioChannelType)
2799 {
2800 list << QLatin1String("audioChannelType");
2801 }
2802
2803 if (fields & DatabaseFields::AudioCodec)
2804 {
2805 list << QLatin1String("audioCompressor");
2806 }
2807
2808 if (fields & DatabaseFields::Duration)
2809 {
2810 list << QLatin1String("duration");
2811 }
2812
2813 if (fields & DatabaseFields::FrameRate)
2814 {
2815 list << QLatin1String("frameRate");
2816 }
2817
2818 if (fields & DatabaseFields::VideoCodec)
2819 {
2820 list << QLatin1String("videoCodec");
2821 }
2822
2823 return list;
2824 }
2825
imageMetadataFieldList(DatabaseFields::ImageMetadata fields)2826 QStringList CoreDB::imageMetadataFieldList(DatabaseFields::ImageMetadata fields)
2827 {
2828 // adds no spaces at beginning or end
2829
2830 QStringList list;
2831
2832 if (fields & DatabaseFields::Make)
2833 {
2834 list << QLatin1String("make");
2835 }
2836
2837 if (fields & DatabaseFields::Model)
2838 {
2839 list << QLatin1String("model");
2840 }
2841
2842 if (fields & DatabaseFields::Lens)
2843 {
2844 list << QLatin1String("lens");
2845 }
2846
2847 if (fields & DatabaseFields::Aperture)
2848 {
2849 list << QLatin1String("aperture");
2850 }
2851
2852 if (fields & DatabaseFields::FocalLength)
2853 {
2854 list << QLatin1String("focalLength");
2855 }
2856
2857 if (fields & DatabaseFields::FocalLength35)
2858 {
2859 list << QLatin1String("focalLength35");
2860 }
2861
2862 if (fields & DatabaseFields::ExposureTime)
2863 {
2864 list << QLatin1String("exposureTime");
2865 }
2866
2867 if (fields & DatabaseFields::ExposureProgram)
2868 {
2869 list << QLatin1String("exposureProgram");
2870 }
2871
2872 if (fields & DatabaseFields::ExposureMode)
2873 {
2874 list << QLatin1String("exposureMode");
2875 }
2876
2877 if (fields & DatabaseFields::Sensitivity)
2878 {
2879 list << QLatin1String("sensitivity");
2880 }
2881
2882 if (fields & DatabaseFields::FlashMode)
2883 {
2884 list << QLatin1String("flash");
2885 }
2886
2887 if (fields & DatabaseFields::WhiteBalance)
2888 {
2889 list << QLatin1String("whiteBalance");
2890 }
2891
2892 if (fields & DatabaseFields::WhiteBalanceColorTemperature)
2893 {
2894 list << QLatin1String("whiteBalanceColorTemperature");
2895 }
2896
2897 if (fields & DatabaseFields::MeteringMode)
2898 {
2899 list << QLatin1String("meteringMode");
2900 }
2901
2902 if (fields & DatabaseFields::SubjectDistance)
2903 {
2904 list << QLatin1String("subjectDistance");
2905 }
2906
2907 if (fields & DatabaseFields::SubjectDistanceCategory)
2908 {
2909 list << QLatin1String("subjectDistanceCategory");
2910 }
2911
2912 return list;
2913 }
2914
imagePositionsFieldList(DatabaseFields::ItemPositions fields)2915 QStringList CoreDB::imagePositionsFieldList(DatabaseFields::ItemPositions fields)
2916 {
2917 // adds no spaces at beginning or end
2918
2919 QStringList list;
2920
2921 if (fields & DatabaseFields::Latitude)
2922 {
2923 list << QLatin1String("latitude");
2924 }
2925
2926 if (fields & DatabaseFields::LatitudeNumber)
2927 {
2928 list << QLatin1String("latitudeNumber");
2929 }
2930
2931 if (fields & DatabaseFields::Longitude)
2932 {
2933 list << QLatin1String("longitude");
2934 }
2935
2936 if (fields & DatabaseFields::LongitudeNumber)
2937 {
2938 list << QLatin1String("longitudeNumber");
2939 }
2940
2941 if (fields & DatabaseFields::Altitude)
2942 {
2943 list << QLatin1String("altitude");
2944 }
2945
2946 if (fields & DatabaseFields::PositionOrientation)
2947 {
2948 list << QLatin1String("orientation");
2949 }
2950
2951 if (fields & DatabaseFields::PositionTilt)
2952 {
2953 list << QLatin1String("tilt");
2954 }
2955
2956 if (fields & DatabaseFields::PositionRoll)
2957 {
2958 list << QLatin1String("roll");
2959 }
2960
2961 if (fields & DatabaseFields::PositionAccuracy)
2962 {
2963 list << QLatin1String("accuracy");
2964 }
2965
2966 if (fields & DatabaseFields::PositionDescription)
2967 {
2968 list << QLatin1String("description");
2969 }
2970
2971 return list;
2972 }
2973
imageCommentsFieldList(DatabaseFields::ItemComments fields)2974 QStringList CoreDB::imageCommentsFieldList(DatabaseFields::ItemComments fields)
2975 {
2976 // adds no spaces at beginning or end
2977
2978 QStringList list;
2979
2980 if (fields & DatabaseFields::CommentType)
2981 {
2982 list << QLatin1String("type");
2983 }
2984
2985 if (fields & DatabaseFields::CommentLanguage)
2986 {
2987 list << QLatin1String("language");
2988 }
2989
2990 if (fields & DatabaseFields::CommentAuthor)
2991 {
2992 list << QLatin1String("author");
2993 }
2994
2995 if (fields & DatabaseFields::CommentDate)
2996 {
2997 list << QLatin1String("date");
2998 }
2999
3000 if (fields & DatabaseFields::Comment)
3001 {
3002 list << QLatin1String("comment");
3003 }
3004
3005 return list;
3006 }
3007
addBoundValuePlaceholders(QString & query,int count)3008 void CoreDB::addBoundValuePlaceholders(QString& query, int count)
3009 {
3010 // adds no spaces at beginning or end
3011
3012 QString questionMarks;
3013 questionMarks.reserve(count * 2);
3014 QString questionMark(QString::fromUtf8("?,"));
3015
3016 for (int i = 0 ; i < count ; ++i)
3017 {
3018 questionMarks += questionMark;
3019 }
3020
3021 // remove last ','
3022
3023 questionMarks.chop(1);
3024
3025 query += questionMarks;
3026 }
3027
findInDownloadHistory(const QString & identifier,const QString & name,qlonglong fileSize,const QDateTime & date) const3028 int CoreDB::findInDownloadHistory(const QString& identifier, const QString& name, qlonglong fileSize, const QDateTime& date) const
3029 {
3030 QList<QVariant> values;
3031 QVariantList boundValues;
3032 boundValues << identifier << name << fileSize << date.addSecs(-2) << date.addSecs(2);
3033
3034 d->db->execSql(QString::fromUtf8("SELECT id FROM DownloadHistory "
3035 " WHERE identifier=? AND filename=? "
3036 " AND filesize=? AND (filedate>? "
3037 " AND filedate<?);"),
3038 boundValues, &values);
3039
3040 if (values.isEmpty())
3041 {
3042 return -1;
3043 }
3044
3045 return values.first().toInt();
3046 }
3047
addToDownloadHistory(const QString & identifier,const QString & name,qlonglong fileSize,const QDateTime & date) const3048 int CoreDB::addToDownloadHistory(const QString& identifier, const QString& name, qlonglong fileSize, const QDateTime& date) const
3049 {
3050 QVariant id;
3051 d->db->execSql(QString::fromUtf8("REPLACE INTO DownloadHistory "
3052 "(identifier, filename, filesize, filedate) "
3053 " VALUES (?,?,?,?);"),
3054 identifier, name, fileSize, date, nullptr, &id);
3055
3056 return id.toInt();
3057 }
3058
addItemTag(qlonglong imageID,int tagID,bool newTag)3059 void CoreDB::addItemTag(qlonglong imageID, int tagID, bool newTag)
3060 {
3061 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTags (imageid, tagid) "
3062 "VALUES(?, ?);"),
3063 imageID, tagID);
3064
3065 d->db->recordChangeset(ImageTagChangeset(imageID, tagID, ImageTagChangeset::Added));
3066
3067 // don't save pick or color tags
3068
3069 if (!newTag || TagsCache::instance()->isInternalTag(tagID))
3070 {
3071 return;
3072 }
3073
3074 // move current tag to front
3075
3076 d->recentlyAssignedTags.removeAll(tagID);
3077 d->recentlyAssignedTags.prepend(tagID);
3078
3079 if (d->recentlyAssignedTags.size() > 10)
3080 {
3081 d->recentlyAssignedTags.removeLast();
3082 }
3083 }
3084
addItemTag(int albumID,const QString & name,int tagID)3085 void CoreDB::addItemTag(int albumID, const QString& name, int tagID)
3086 {
3087 // easier because of attributes watch
3088
3089 addItemTag(getImageId(albumID, name), tagID);
3090 }
3091
addTagsToItems(QList<qlonglong> imageIDs,QList<int> tagIDs)3092 void CoreDB::addTagsToItems(QList<qlonglong> imageIDs, QList<int> tagIDs)
3093 {
3094 if (imageIDs.isEmpty() || tagIDs.isEmpty())
3095 {
3096 return;
3097 }
3098
3099 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("REPLACE INTO ImageTags (imageid, tagid) "
3100 "VALUES(?, ?);"));
3101 QVariantList images;
3102 QVariantList tags;
3103
3104 foreach (const qlonglong& imageid, imageIDs)
3105 {
3106 foreach (int tagid, tagIDs)
3107 {
3108 images << imageid;
3109 tags << tagid;
3110 }
3111 }
3112
3113 query.addBindValue(images);
3114 query.addBindValue(tags);
3115 d->db->execBatch(query);
3116 d->db->recordChangeset(ImageTagChangeset(imageIDs, tagIDs, ImageTagChangeset::Added));
3117 }
3118
getRecentlyAssignedTags() const3119 QList<int> CoreDB::getRecentlyAssignedTags() const
3120 {
3121 return d->recentlyAssignedTags;
3122 }
3123
removeItemTag(qlonglong imageID,int tagID)3124 void CoreDB::removeItemTag(qlonglong imageID, int tagID)
3125 {
3126 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags "
3127 "WHERE imageID=? AND tagid=?;"),
3128 imageID, tagID);
3129
3130 d->db->recordChangeset(ImageTagChangeset(imageID, tagID, ImageTagChangeset::Removed));
3131 }
3132
removeItemAllTags(qlonglong imageID,const QList<int> & currentTagIds)3133 void CoreDB::removeItemAllTags(qlonglong imageID, const QList<int>& currentTagIds)
3134 {
3135 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags "
3136 "WHERE imageID=?;"),
3137 imageID);
3138
3139 d->db->recordChangeset(ImageTagChangeset(imageID, currentTagIds, ImageTagChangeset::RemovedAll));
3140 }
3141
removeTagsFromItems(QList<qlonglong> imageIDs,const QList<int> & tagIDs)3142 void CoreDB::removeTagsFromItems(QList<qlonglong> imageIDs, const QList<int>& tagIDs)
3143 {
3144 if (imageIDs.isEmpty() || tagIDs.isEmpty())
3145 {
3146 return;
3147 }
3148
3149 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("DELETE FROM ImageTags WHERE imageID=? AND tagid=?;"));
3150 QVariantList images;
3151 QVariantList tags;
3152
3153 foreach (const qlonglong& imageid, imageIDs)
3154 {
3155 foreach (int tagid, tagIDs)
3156 {
3157 images << imageid;
3158 tags << tagid;
3159 }
3160 }
3161
3162 query.addBindValue(images);
3163 query.addBindValue(tags);
3164 d->db->execBatch(query);
3165 d->db->recordChangeset(ImageTagChangeset(imageIDs, tagIDs, ImageTagChangeset::Removed));
3166 }
3167
getItemNamesInAlbum(int albumID,bool recursive) const3168 QStringList CoreDB::getItemNamesInAlbum(int albumID, bool recursive) const
3169 {
3170 QList<QVariant> values;
3171
3172 if (recursive)
3173 {
3174 int rootId = getAlbumRootId(albumID);
3175 QString path = getAlbumRelativePath(albumID);
3176 d->db->execSql(QString::fromUtf8("SELECT Images.name FROM Images WHERE Images.album IN "
3177 " (SELECT DISTINCT id FROM Albums "
3178 " WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?));"),
3179 rootId, path, path == QLatin1String("/") ? QLatin1String("/%")
3180 : QString(path + QLatin1String("/%")), &values);
3181 }
3182 else
3183 {
3184 d->db->execSql(QString::fromUtf8("SELECT name FROM Images "
3185 "WHERE album=?;"),
3186 albumID, &values);
3187 }
3188
3189 QStringList names;
3190
3191 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3192 {
3193 names << it->toString();
3194 }
3195
3196 return names;
3197 }
3198
getItemFromAlbum(int albumID,const QString & fileName) const3199 qlonglong CoreDB::getItemFromAlbum(int albumID, const QString& fileName) const
3200 {
3201 QList<QVariant> values;
3202
3203 d->db->execSql(QString::fromUtf8("SELECT id FROM Images "
3204 "WHERE album=? AND name=?;"),
3205 albumID, fileName, &values);
3206
3207 if (values.isEmpty())
3208 {
3209 return -1;
3210 }
3211
3212 return values.first().toLongLong();
3213 }
3214
getAllCreationDates() const3215 QVariantList CoreDB::getAllCreationDates() const
3216 {
3217 QVariantList values;
3218 d->db->execSql(QString::fromUtf8("SELECT creationDate FROM ImageInformation "
3219 "INNER JOIN Images ON Images.id=ImageInformation.imageid "
3220 " WHERE Images.status=1;"),
3221 &values);
3222
3223 return values;
3224 }
3225
getAlbumModificationDate(int albumID) const3226 QDateTime CoreDB::getAlbumModificationDate(int albumID) const
3227 {
3228 QVariantList values;
3229
3230 d->db->execSql(QString::fromUtf8("SELECT modificationDate FROM Albums "
3231 " WHERE id=?;"),
3232 albumID, &values);
3233
3234 if (values.isEmpty())
3235 {
3236 return QDateTime();
3237 }
3238
3239 return values.first().toDateTime();
3240 }
3241
getAlbumModificationMap(int albumRootId) const3242 QMap<QString, QDateTime> CoreDB::getAlbumModificationMap(int albumRootId) const
3243 {
3244 QList<QVariant> values;
3245 QMap<QString, QDateTime> pathDateMap;
3246
3247 d->db->execSql(QString::fromUtf8("SELECT relativePath, modificationDate FROM Albums "
3248 " WHERE albumRoot=?;"),
3249 albumRootId, &values);
3250
3251 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3252 {
3253 QString relativePath = (*it).toString();
3254 ++it;
3255 QDateTime dateTime = (*it).toDateTime();
3256 ++it;
3257
3258 pathDateMap.insert(relativePath, dateTime);
3259 }
3260
3261 return pathDateMap;
3262
3263 }
3264
getNumberOfAllItemsAndAlbums(int albumID) const3265 QPair<int, int> CoreDB::getNumberOfAllItemsAndAlbums(int albumID) const
3266 {
3267 int items = 0;
3268 int albums = 0;
3269 QVariantList values;
3270
3271 int rootId = getAlbumRootId(albumID);
3272 QString path = getAlbumRelativePath(albumID);
3273 d->db->execSql(QString::fromUtf8("SELECT COUNT(*) FROM Images WHERE Images.album IN "
3274 " (SELECT DISTINCT id FROM Albums "
3275 " WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?));"),
3276 rootId, path, path == QLatin1String("/") ? QLatin1String("/%")
3277 : QString(path + QLatin1String("/%")), &values);
3278
3279 if (!values.isEmpty())
3280 {
3281 items = values.first().toInt();
3282 }
3283
3284 values.clear();
3285
3286 d->db->execSql(QString::fromUtf8("SELECT DISTINCT COUNT(*) FROM Albums "
3287 " WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?);"),
3288 rootId, path, path == QLatin1String("/") ? QLatin1String("/%")
3289 : QString(path + QLatin1String("/%")), &values);
3290
3291 if (!values.isEmpty())
3292 {
3293 albums = values.first().toInt();
3294 }
3295
3296 return qMakePair(items, albums);
3297 }
3298
getNumberOfItemsInAlbum(int albumID) const3299 int CoreDB::getNumberOfItemsInAlbum(int albumID) const
3300 {
3301 QVariantList values;
3302
3303 d->db->execSql(QString::fromUtf8("SELECT COUNT(*) FROM Images "
3304 "WHERE album=?;"),
3305 albumID, &values);
3306
3307 if (values.isEmpty())
3308 {
3309 return 0;
3310 }
3311
3312 return values.first().toInt();
3313 }
3314
getNumberOfImagesInAlbums() const3315 QMap<int, int> CoreDB::getNumberOfImagesInAlbums() const
3316 {
3317 QList<QVariant> values, allAbumIDs;
3318 QMap<int, int> albumsStatMap;
3319 int albumID, count;
3320
3321 // initialize allAbumIDs with all existing albums from db to prevent
3322 // wrong album image counters
3323
3324 d->db->execSql(QString::fromUtf8("SELECT id FROM Albums;"),
3325 &allAbumIDs);
3326
3327 for (QList<QVariant>::const_iterator it = allAbumIDs.constBegin() ; it != allAbumIDs.constEnd() ; ++it)
3328 {
3329 albumID = (*it).toInt();
3330 albumsStatMap.insert(albumID, 0);
3331 }
3332
3333 d->db->execSql(QString::fromUtf8("SELECT album, COUNT(*) FROM Images "
3334 "WHERE Images.status=1 GROUP BY album;"),
3335 &values);
3336
3337 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3338 {
3339 albumID = (*it).toInt();
3340 ++it;
3341 count = (*it).toInt();
3342 ++it;
3343
3344 albumsStatMap[albumID] = count;
3345 }
3346
3347 return albumsStatMap;
3348 }
3349
getNumberOfImagesInTags() const3350 QMap<int, int> CoreDB::getNumberOfImagesInTags() const
3351 {
3352 QList<QVariant> values, allTagIDs;
3353 QMap<int, int> tagsStatMap;
3354 int tagID, count;
3355
3356 // initialize allTagIDs with all existing tags from db to prevent
3357 // wrong tag counters
3358
3359 d->db->execSql(QString::fromUtf8("SELECT id FROM Tags;"),
3360 &allTagIDs);
3361
3362 for (QList<QVariant>::const_iterator it = allTagIDs.constBegin() ; it != allTagIDs.constEnd() ; ++it)
3363 {
3364 tagID = (*it).toInt();
3365 tagsStatMap.insert(tagID, 0);
3366 }
3367
3368 d->db->execSql(QString::fromUtf8("SELECT tagid, COUNT(*) FROM ImageTags "
3369 "LEFT JOIN Images ON Images.id=ImageTags.imageid "
3370 " WHERE Images.status=1 GROUP BY tagid;"),
3371 &values);
3372
3373 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3374 {
3375 tagID = (*it).toInt();
3376 ++it;
3377 count = (*it).toInt();
3378 ++it;
3379
3380 tagsStatMap[tagID] = count;
3381 }
3382
3383 return tagsStatMap;
3384 }
3385
getNumberOfImagesInTagProperties(const QString & property) const3386 QMap<int, int> CoreDB::getNumberOfImagesInTagProperties(const QString& property) const
3387 {
3388 QList<QVariant> values;
3389 QMap<int, int> tagsStatMap;
3390 int tagID, count;
3391
3392 d->db->execSql(QString::fromUtf8("SELECT tagid, COUNT(*) FROM ImageTagProperties "
3393 "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid "
3394 " WHERE ImageTagProperties.property=? AND Images.status=1 "
3395 " GROUP BY tagid;"),
3396 property, &values);
3397
3398 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3399 {
3400 tagID = (*it).toInt();
3401 ++it;
3402 count = (*it).toInt();
3403 ++it;
3404
3405 tagsStatMap[tagID] = count;
3406 }
3407
3408 return tagsStatMap;
3409 }
3410
getNumberOfImagesInTagProperties(int tagId,const QString & property) const3411 int CoreDB::getNumberOfImagesInTagProperties(int tagId, const QString& property) const
3412 {
3413 QList<QVariant> values;
3414
3415 d->db->execSql(QString::fromUtf8("SELECT COUNT(*) FROM ImageTagProperties "
3416 "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid "
3417 " WHERE ImageTagProperties.property=? AND Images.status=1 "
3418 " AND ImageTagProperties.tagid=?;"),
3419 property, tagId, &values);
3420
3421 if (values.isEmpty())
3422 {
3423 return 0;
3424 }
3425
3426 return values.first().toInt();
3427 }
3428
getImagesWithImageTagProperty(int tagId,const QString & property) const3429 QList<qlonglong> CoreDB::getImagesWithImageTagProperty(int tagId, const QString& property) const
3430 {
3431 QList<QVariant> values;
3432 QList<qlonglong> imageIds;
3433
3434 d->db->execSql(QString::fromUtf8("SELECT DISTINCT Images.id FROM ImageTagProperties "
3435 "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid "
3436 " WHERE ImageTagProperties.property=? AND Images.status=1 "
3437 " AND ImageTagProperties.tagid=?;"),
3438 property, tagId, &values);
3439
3440 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3441 {
3442 imageIds.append((*it).toInt());
3443 }
3444
3445 return imageIds;
3446 }
3447
getImagesWithProperty(const QString & property) const3448 QList<qlonglong> CoreDB::getImagesWithProperty(const QString& property) const
3449 {
3450 QList<QVariant> values;
3451 QList<qlonglong> imageIds;
3452
3453 d->db->execSql(QString::fromUtf8("SELECT DISTINCT Images.id FROM ImageTagProperties "
3454 "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid "
3455 " WHERE ImageTagProperties.property=? AND Images.status=1;"),
3456 property, &values);
3457
3458 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3459 {
3460 imageIds.append((*it).toInt());
3461 }
3462
3463 return imageIds;
3464 }
3465
getFormatStatistics() const3466 QMap<QString, int> CoreDB::getFormatStatistics() const
3467 {
3468 return getFormatStatistics(DatabaseItem::UndefinedCategory);
3469 }
3470
getFormatStatistics(DatabaseItem::Category category) const3471 QMap<QString, int> CoreDB::getFormatStatistics(DatabaseItem::Category category) const
3472 {
3473 QMap<QString, int> map;
3474
3475 QString queryString = QString::fromUtf8("SELECT COUNT(*), II.format "
3476 "FROM ImageInformation AS II "
3477 "INNER JOIN Images ON II.imageid=Images.id "
3478 " WHERE Images.status=1 ");
3479
3480 if (category != DatabaseItem::UndefinedCategory)
3481 {
3482 queryString.append(QString::fromUtf8("AND Images.category=%1 ").arg(category));
3483 }
3484
3485 queryString.append(QString::fromUtf8("GROUP BY II.format;"));
3486 qCDebug(DIGIKAM_DATABASE_LOG) << queryString;
3487
3488 DbEngineSqlQuery query = d->db->prepareQuery(queryString);
3489
3490 if (d->db->exec(query))
3491 {
3492 while (query.next())
3493 {
3494 QString quantity = query.value(0).toString();
3495 QString format = query.value(1).toString();
3496
3497 if (format.isEmpty())
3498 {
3499 continue;
3500 }
3501
3502 map[format] = quantity.isEmpty() ? 0 : quantity.toInt();
3503 }
3504 }
3505
3506 return map;
3507 }
3508
getListFromImageMetadata(DatabaseFields::ImageMetadata field) const3509 QStringList CoreDB::getListFromImageMetadata(DatabaseFields::ImageMetadata field) const
3510 {
3511 QStringList list;
3512 QList<QVariant> values;
3513 QStringList fieldName = imageMetadataFieldList(field);
3514
3515 if (fieldName.count() != 1)
3516 {
3517 return list;
3518 }
3519
3520 QString sql = QString::fromUtf8("SELECT DISTINCT %1 FROM ImageMetadata "
3521 "INNER JOIN Images ON imageid=Images.id "
3522 " WHERE Images.status=1;");
3523
3524 sql = sql.arg(fieldName.first());
3525 d->db->execSql(sql, &values);
3526
3527 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3528 {
3529 if (!it->isNull())
3530 {
3531 list << it->toString();
3532 }
3533 }
3534
3535 return list;
3536 }
3537
getAlbumForPath(int albumRootId,const QString & folder,bool create) const3538 int CoreDB::getAlbumForPath(int albumRootId, const QString& folder, bool create) const
3539 {
3540 QList<QVariant> values;
3541 d->db->execSql(QString::fromUtf8("SELECT id FROM Albums WHERE albumRoot=? AND relativePath=?;"),
3542 albumRootId, folder, &values);
3543
3544 int albumID = -1;
3545
3546 if (values.isEmpty())
3547 {
3548 if (create)
3549 {
3550 albumID = addAlbum(albumRootId, folder, QString(), QDate::currentDate(), QString());
3551 }
3552 }
3553 else
3554 {
3555 albumID = values.first().toInt();
3556 }
3557
3558 return albumID;
3559 }
3560
getAlbumAndSubalbumsForPath(int albumRootId,const QString & relativePath) const3561 QList<int> CoreDB::getAlbumAndSubalbumsForPath(int albumRootId, const QString& relativePath) const
3562 {
3563 QList<QVariant> values;
3564 d->db->execSql(QString::fromUtf8("SELECT id, relativePath FROM Albums "
3565 "WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?);"),
3566 albumRootId, relativePath,
3567 (relativePath == QLatin1String("/") ? QLatin1String("/%")
3568 : QString(relativePath + QLatin1String("/%"))), &values);
3569
3570 QList<int> albumIds;
3571 int id;
3572 QString albumRelativePath;
3573
3574 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3575 {
3576 id = (*it).toInt();
3577 ++it;
3578 albumRelativePath = (*it).toString();
3579 ++it;
3580
3581 // bug #223050: The LIKE operator is case insensitive
3582
3583 if (albumRelativePath.startsWith(relativePath))
3584 {
3585 albumIds << id;
3586 }
3587 }
3588
3589 return albumIds;
3590 }
3591
getAlbumsOnAlbumRoot(int albumRootId) const3592 QList<int> CoreDB::getAlbumsOnAlbumRoot(int albumRootId) const
3593 {
3594 QList<QVariant> values;
3595 d->db->execSql(QString::fromUtf8("SELECT id FROM Albums WHERE albumRoot=?;"),
3596 albumRootId, &values);
3597
3598 QList<int> albumIds;
3599
3600 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3601 {
3602 albumIds << (*it).toInt();
3603 }
3604
3605 return albumIds;
3606 }
3607
addItem(int albumID,const QString & name,DatabaseItem::Status status,DatabaseItem::Category category,const QDateTime & modificationDate,qlonglong fileSize,const QString & uniqueHash) const3608 qlonglong CoreDB::addItem(int albumID, const QString& name,
3609 DatabaseItem::Status status,
3610 DatabaseItem::Category category,
3611 const QDateTime& modificationDate,
3612 qlonglong fileSize,
3613 const QString& uniqueHash) const
3614 {
3615 QVariantList boundValues;
3616 boundValues << albumID << name << (int)status << (int)category
3617 << modificationDate << fileSize << uniqueHash;
3618
3619 QVariant id;
3620 d->db->execSql(QString::fromUtf8("REPLACE INTO Images "
3621 "( album, name, status, category, modificationDate, fileSize, uniqueHash ) "
3622 " VALUES (?,?,?,?,?,?,?);"),
3623 boundValues, nullptr, &id);
3624
3625 if (id.isNull())
3626 {
3627 return -1;
3628 }
3629
3630 d->db->recordChangeset(ImageChangeset(id.toLongLong(), DatabaseFields::Set(DatabaseFields::ImagesAll)));
3631 d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), albumID,
3632 CollectionImageChangeset::Added));
3633 return id.toLongLong();
3634 }
3635
updateItem(qlonglong imageID,DatabaseItem::Category category,const QDateTime & modificationDate,qlonglong fileSize,const QString & uniqueHash)3636 void CoreDB::updateItem(qlonglong imageID, DatabaseItem::Category category,
3637 const QDateTime& modificationDate,
3638 qlonglong fileSize, const QString& uniqueHash)
3639 {
3640 QVariantList boundValues;
3641 boundValues << (int)category << modificationDate << fileSize << uniqueHash << imageID;
3642
3643 d->db->execSql(QString::fromUtf8("UPDATE Images SET category=?, modificationDate=?, fileSize=?, uniqueHash=? "
3644 "WHERE id=?;"),
3645 boundValues);
3646
3647 d->db->recordChangeset(ImageChangeset(imageID,
3648 DatabaseFields::Set(DatabaseFields::Category |
3649 DatabaseFields::ModificationDate |
3650 DatabaseFields::FileSize |
3651 DatabaseFields::UniqueHash)));
3652 }
3653
setItemStatus(qlonglong imageID,DatabaseItem::Status status)3654 void CoreDB::setItemStatus(qlonglong imageID, DatabaseItem::Status status)
3655 {
3656 QVariantList boundValues;
3657 boundValues << (int)status << imageID;
3658 d->db->execSql(QString::fromUtf8("UPDATE Images SET status=? WHERE id=?;"),
3659 boundValues);
3660 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::Status)));
3661 }
3662
setItemAlbum(qlonglong imageID,qlonglong album)3663 void CoreDB::setItemAlbum(qlonglong imageID, qlonglong album)
3664 {
3665 QVariantList boundValues;
3666 boundValues << album << imageID;
3667 d->db->execSql(QString::fromUtf8("UPDATE Images SET album=? WHERE id=?;"),
3668 boundValues);
3669
3670 // record that the image was assigned a new album
3671
3672 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::Album)));
3673
3674 // also record that the collection was changed by adding an image to an album.
3675
3676 d->db->recordChangeset(CollectionImageChangeset(imageID, album, CollectionImageChangeset::Added));
3677 }
3678
setItemManualOrder(qlonglong imageID,qlonglong value)3679 void CoreDB::setItemManualOrder(qlonglong imageID, qlonglong value)
3680 {
3681 QVariantList boundValues;
3682 boundValues << value << imageID;
3683 d->db->execSql(QString::fromUtf8("UPDATE Images SET manualOrder=? WHERE id=?;"),
3684 boundValues);
3685
3686 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ManualOrder)));
3687 }
3688
setItemModificationDate(qlonglong imageID,const QDateTime & modificationDate)3689 void CoreDB::setItemModificationDate(qlonglong imageID, const QDateTime& modificationDate)
3690 {
3691 QVariantList boundValues;
3692 boundValues << modificationDate << imageID;
3693 d->db->execSql(QString::fromUtf8("UPDATE Images SET modificationDate=? WHERE id=?;"),
3694 boundValues);
3695
3696 d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ModificationDate)));
3697 }
3698
renameItem(qlonglong imageID,const QString & newName)3699 void CoreDB::renameItem(qlonglong imageID, const QString& newName)
3700 {
3701 d->db->execSql(QString::fromUtf8("UPDATE Images SET name=? WHERE id=?;"),
3702 newName, imageID);
3703 }
3704
getItemAlbum(qlonglong imageID) const3705 int CoreDB::getItemAlbum(qlonglong imageID) const
3706 {
3707 QList<QVariant> values;
3708
3709 d->db->execSql(QString::fromUtf8("SELECT album FROM Images WHERE id=?;"),
3710 imageID, &values);
3711
3712 if (values.isEmpty())
3713 {
3714 return 1;
3715 }
3716
3717 return values.first().toInt();
3718 }
3719
getItemName(qlonglong imageID) const3720 QString CoreDB::getItemName(qlonglong imageID) const
3721 {
3722 QList<QVariant> values;
3723
3724 d->db->execSql(QString::fromUtf8("SELECT name FROM Images WHERE id=?;"),
3725 imageID, &values);
3726
3727 if (values.isEmpty())
3728 {
3729 return QString();
3730 }
3731
3732 return values.first().toString();
3733 }
3734
getItemURLsInAlbum(int albumID,ItemSortOrder sortOrder) const3735 QStringList CoreDB::getItemURLsInAlbum(int albumID, ItemSortOrder sortOrder) const
3736 {
3737 QList<QVariant> values;
3738
3739 int albumRootId = getAlbumRootId(albumID);
3740
3741 if (albumRootId == -1)
3742 {
3743 return QStringList();
3744 }
3745
3746 QString albumRootPath = CollectionManager::instance()->albumRootPath(albumRootId);
3747
3748 if (albumRootPath.isNull())
3749 {
3750 return QStringList();
3751 }
3752
3753 QMap<QString, QVariant> bindingMap;
3754 bindingMap.insert(QString::fromUtf8(":albumID"), albumID);
3755
3756 switch (sortOrder)
3757 {
3758 case ByItemName:
3759 {
3760 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemName")),
3761 bindingMap, &values);
3762 break;
3763 }
3764
3765 case ByItemPath:
3766 {
3767 // Don't collate on the path - this is to maintain the same behavior
3768 // that happens when sort order is "By Path"
3769
3770 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemPath")),
3771 bindingMap, &values);
3772 break;
3773 }
3774
3775 case ByItemDate:
3776 {
3777 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemDate")),
3778 bindingMap, &values);
3779 break;
3780 }
3781
3782 case ByItemRating:
3783 {
3784 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemRating")),
3785 bindingMap, &values);
3786 break;
3787 }
3788
3789 case NoItemSorting:
3790 default:
3791 {
3792 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumNoItemSorting")),
3793 bindingMap, &values);
3794 break;
3795 }
3796 }
3797
3798 QStringList urls;
3799 QString relativePath, name;
3800
3801 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3802 {
3803 relativePath = (*it).toString();
3804 ++it;
3805 name = (*it).toString();
3806 ++it;
3807
3808 if (relativePath == QLatin1String("/"))
3809 {
3810 urls << albumRootPath + relativePath + name;
3811 }
3812 else
3813 {
3814 urls << albumRootPath + relativePath + QLatin1Char('/') + name;
3815 }
3816 }
3817
3818 return urls;
3819 }
3820
getItemIDsInAlbum(int albumID) const3821 QList<qlonglong> CoreDB::getItemIDsInAlbum(int albumID) const
3822 {
3823 QList<qlonglong> itemIDs;
3824 QList<QVariant> values;
3825
3826 d->db->execSql(QString::fromUtf8("SELECT id FROM Images WHERE album=?;"),
3827 albumID, &values);
3828
3829 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
3830 {
3831 itemIDs << (*it).toLongLong();
3832 }
3833
3834 return itemIDs;
3835 }
3836
getItemIDsAndURLsInAlbum(int albumID) const3837 QMap<qlonglong, QString> CoreDB::getItemIDsAndURLsInAlbum(int albumID) const
3838 {
3839 int albumRootId = getAlbumRootId(albumID);
3840
3841 if (albumRootId == -1)
3842 {
3843 return QMap<qlonglong, QString>();
3844 }
3845
3846 QString albumRootPath = CollectionManager::instance()->albumRootPath(albumRootId);
3847
3848 if (albumRootPath.isNull())
3849 {
3850 return QMap<qlonglong, QString>();
3851 }
3852
3853 QMap<qlonglong, QString> itemsMap;
3854 QList<QVariant> values;
3855
3856 d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.relativePath, Images.name "
3857 "FROM Images "
3858 " INNER JOIN Albums ON Albums.id=Images.album "
3859 " WHERE Albums.id=?;"),
3860 albumID, &values);
3861
3862 QString path;
3863 qlonglong id;
3864 QString relativePath, name;
3865
3866 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3867 {
3868 id = (*it).toLongLong();
3869 ++it;
3870 relativePath = (*it).toString();
3871 ++it;
3872 name = (*it).toString();
3873 ++it;
3874
3875 if (relativePath == QLatin1String("/"))
3876 {
3877 path = albumRootPath + relativePath + name;
3878 }
3879 else
3880 {
3881 path = albumRootPath + relativePath + QLatin1Char('/') + name;
3882 }
3883
3884 itemsMap.insert(id, path);
3885 };
3886
3887 return itemsMap;
3888 }
3889
getAllItems() const3890 QList<qlonglong> CoreDB::getAllItems() const
3891 {
3892 QList<QVariant> values;
3893
3894 d->db->execSql(QString::fromUtf8("SELECT id FROM Images;"),
3895 &values);
3896
3897 QList<qlonglong> items;
3898
3899 foreach (const QVariant& item, values)
3900 {
3901 items << item.toLongLong();
3902 }
3903
3904 return items;
3905 }
3906
getAllItemsWithAlbum() const3907 QHash<qlonglong, QPair<int, int> > CoreDB::getAllItemsWithAlbum() const
3908 {
3909 QList<QVariant> values;
3910
3911 d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.albumRoot, Albums.id FROM Images "
3912 "INNER JOIN Albums ON Albums.id=Images.album "
3913 " WHERE Images.status<3;"),
3914 &values);
3915
3916 QHash<qlonglong, QPair<int, int> > itemAlbumHash;
3917
3918 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
3919 {
3920 qlonglong id = (*it).toLongLong();
3921 ++it;
3922 int albumRoot = (*it).toInt();
3923 ++it;
3924 int album = (*it).toInt();
3925 ++it;
3926
3927 itemAlbumHash[id] = qMakePair(albumRoot, album);
3928 }
3929
3930 return itemAlbumHash;
3931 }
3932
getItemScanInfos(int albumID) const3933 QList<ItemScanInfo> CoreDB::getItemScanInfos(int albumID) const
3934 {
3935 QList<ItemScanInfo> list;
3936
3937 QString sql = QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize, uniqueHash "
3938 "FROM Images WHERE album=?;");
3939
3940 DbEngineSqlQuery query = d->db->prepareQuery(sql);
3941 query.addBindValue(albumID);
3942
3943 if (d->db->exec(query))
3944 {
3945 while (query.next())
3946 {
3947 ItemScanInfo info;
3948
3949 info.id = query.value(0).toLongLong();
3950 info.albumID = query.value(1).toInt();
3951 info.itemName = query.value(2).toString();
3952 info.status = (DatabaseItem::Status)query.value(3).toInt();
3953 info.category = (DatabaseItem::Category)query.value(4).toInt();
3954 info.modificationDate = query.value(5).toDateTime();
3955 info.fileSize = query.value(6).toLongLong();
3956 info.uniqueHash = query.value(7).toString();
3957
3958 list << info;
3959 }
3960 }
3961
3962 return list;
3963 }
3964
getItemScanInfo(qlonglong imageID) const3965 ItemScanInfo CoreDB::getItemScanInfo(qlonglong imageID) const
3966 {
3967 QList<QVariant> values;
3968
3969 d->db->execSql(QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize, uniqueHash "
3970 "FROM Images WHERE id=?;"),
3971 imageID, &values);
3972
3973 ItemScanInfo info;
3974
3975 if (!values.isEmpty())
3976 {
3977 QList<QVariant>::const_iterator it = values.constBegin();
3978
3979 info.id = (*it).toLongLong();
3980 ++it;
3981 info.albumID = (*it).toInt();
3982 ++it;
3983 info.itemName = (*it).toString();
3984 ++it;
3985 info.status = (DatabaseItem::Status)(*it).toInt();
3986 ++it;
3987 info.category = (DatabaseItem::Category)(*it).toInt();
3988 ++it;
3989 info.modificationDate = (*it).toDateTime();
3990 ++it;
3991 info.fileSize = (*it).toLongLong();
3992 ++it;
3993 info.uniqueHash = (*it).toString();
3994 ++it;
3995 }
3996
3997 return info;
3998 }
3999
getItemURLsInTag(int tagID,bool recursive) const4000 QStringList CoreDB::getItemURLsInTag(int tagID, bool recursive) const
4001 {
4002 QList<QVariant> values;
4003 QMap<QString, QVariant> bindingMap;
4004
4005 bindingMap.insert(QString::fromUtf8(":tagID"), tagID);
4006 bindingMap.insert(QString::fromUtf8(":tagID2"), tagID);
4007
4008 if (recursive)
4009 {
4010 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("GetItemURLsInTagRecursive")), bindingMap, &values);
4011 }
4012 else
4013 {
4014 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("GetItemURLsInTag")), bindingMap, &values);
4015 }
4016
4017 QStringList urls;
4018 QString albumRootPath, relativePath, name;
4019
4020 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; )
4021 {
4022 albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt());
4023 ++it;
4024 relativePath = (*it).toString();
4025 ++it;
4026 name = (*it).toString();
4027 ++it;
4028
4029 if (relativePath == QLatin1String("/"))
4030 {
4031 urls << albumRootPath + relativePath + name;
4032 }
4033 else
4034 {
4035 urls << albumRootPath + relativePath + QLatin1Char('/') + name;
4036 }
4037 }
4038
4039 return urls;
4040 }
4041
getItemIDsInTag(int tagID,bool recursive) const4042 QList<qlonglong> CoreDB::getItemIDsInTag(int tagID, bool recursive) const
4043 {
4044 QList<qlonglong> itemIDs;
4045 QList<QVariant> values;
4046 QMap<QString, QVariant> parameters;
4047
4048 parameters.insert(QString::fromUtf8(":tagPID"), tagID);
4049 parameters.insert(QString::fromUtf8(":tagID"), tagID);
4050
4051 if (recursive)
4052 {
4053 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemIDsInTagRecursive")), parameters, &values);
4054 }
4055 else
4056 {
4057 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemIDsInTag")), parameters, &values);
4058 }
4059
4060 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
4061 {
4062 itemIDs << (*it).toLongLong();
4063 }
4064
4065 return itemIDs;
4066 }
4067
getAlbumRelativePath(int albumID) const4068 QString CoreDB::getAlbumRelativePath(int albumID) const
4069 {
4070 QList<QVariant> values;
4071 d->db->execSql(QString::fromUtf8("SELECT relativePath FROM Albums WHERE id=?;"),
4072 albumID, &values);
4073
4074 if (values.isEmpty())
4075 {
4076 return QString();
4077 }
4078
4079 return values.first().toString();
4080 }
4081
getAlbumRootId(int albumID) const4082 int CoreDB::getAlbumRootId(int albumID) const
4083 {
4084 QList<QVariant> values;
4085 d->db->execSql(QString::fromUtf8("SELECT albumRoot FROM Albums WHERE id=?;"),
4086 albumID, &values);
4087
4088 if (values.isEmpty())
4089 {
4090 return -1;
4091 }
4092
4093 return values.first().toInt();
4094 }
4095
getAlbumLowestDate(int albumID) const4096 QDate CoreDB::getAlbumLowestDate(int albumID) const
4097 {
4098 QList<QVariant> values;
4099 d->db->execSql(QString::fromUtf8("SELECT MIN(creationDate) FROM ImageInformation "
4100 "INNER JOIN Images ON Images.id=ImageInformation.imageid "
4101 " WHERE Images.album=? GROUP BY Images.album;"),
4102 albumID, &values);
4103
4104 if (values.isEmpty())
4105 {
4106 return QDate();
4107 }
4108
4109 return values.first().toDate();
4110 }
4111
getAlbumHighestDate(int albumID) const4112 QDate CoreDB::getAlbumHighestDate(int albumID) const
4113 {
4114 QList<QVariant> values;
4115 d->db->execSql(QString::fromUtf8("SELECT MAX(creationDate) FROM ImageInformation "
4116 "INNER JOIN Images ON Images.id=ImageInformation.imageid "
4117 " WHERE Images.album=? GROUP BY Images.album;"),
4118 albumID , &values);
4119
4120 if (values.isEmpty())
4121 {
4122 return QDate();
4123 }
4124
4125 return values.first().toDate();
4126 }
4127
getAlbumAverageDate(int albumID) const4128 QDate CoreDB::getAlbumAverageDate(int albumID) const
4129 {
4130 QList<QVariant> values;
4131 d->db->execSql(QString::fromUtf8("SELECT creationDate FROM ImageInformation "
4132 "INNER JOIN Images ON Images.id=ImageInformation.imageid "
4133 " WHERE Images.album=?;"),
4134 albumID , &values);
4135
4136 QList<QDate> dates;
4137
4138 for (QList<QVariant>::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it)
4139 {
4140 QDateTime itemDateTime = (*it).toDateTime();
4141
4142 if (itemDateTime.isValid())
4143 {
4144 dates << itemDateTime.date();
4145 }
4146 }
4147
4148 if (dates.isEmpty())
4149 {
4150 return QDate();
4151 }
4152
4153 qint64 julianDays = 0;
4154
4155 foreach (const QDate& date, dates)
4156 {
4157 julianDays += date.toJulianDay();
4158 }
4159
4160 return QDate::fromJulianDay(julianDays / dates.size());
4161 }
4162
deleteItem(int albumID,const QString & file)4163 void CoreDB::deleteItem(int albumID, const QString& file)
4164 {
4165 qlonglong imageId = getImageId(albumID, file);
4166
4167 if (imageId == -1)
4168 {
4169 return;
4170 }
4171
4172 d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE id=?;"),
4173 imageId);
4174
4175 d->db->recordChangeset(CollectionImageChangeset(imageId, albumID, CollectionImageChangeset::Deleted));
4176 }
4177
deleteItem(qlonglong imageId)4178 void CoreDB::deleteItem(qlonglong imageId)
4179 {
4180 d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE id=? AND album IS NULL;"),
4181 imageId);
4182
4183 }
4184
removeItemsFromAlbum(int albumID,const QList<qlonglong> & ids_forInformation)4185 void CoreDB::removeItemsFromAlbum(int albumID, const QList<qlonglong>& ids_forInformation)
4186 {
4187 d->db->execSql(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE album=?;"),
4188 (int)DatabaseItem::Trashed, albumID);
4189
4190 d->db->recordChangeset(CollectionImageChangeset(ids_forInformation, albumID, CollectionImageChangeset::RemovedAll));
4191 }
4192
removeItems(QList<qlonglong> itemIDs,const QList<int> & albumIDs)4193 void CoreDB::removeItems(QList<qlonglong> itemIDs, const QList<int>& albumIDs)
4194 {
4195 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE id=?;"));
4196
4197 QVariantList imageIds;
4198 QVariantList status;
4199
4200 foreach (const qlonglong& id, itemIDs)
4201 {
4202 status << (int)DatabaseItem::Trashed;
4203 imageIds << id;
4204 }
4205
4206 query.addBindValue(status);
4207 query.addBindValue(imageIds);
4208 d->db->execBatch(query);
4209
4210 d->db->recordChangeset(CollectionImageChangeset(itemIDs, albumIDs, CollectionImageChangeset::Removed));
4211 }
4212
removeItemsPermanently(QList<qlonglong> itemIDs,const QList<int> & albumIDs)4213 void CoreDB::removeItemsPermanently(QList<qlonglong> itemIDs, const QList<int>& albumIDs)
4214 {
4215 DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE id=?;"));
4216
4217 QVariantList imageIds;
4218 QVariantList status;
4219
4220 foreach (const qlonglong& id, itemIDs)
4221 {
4222 status << (int)DatabaseItem::Obsolete;
4223 imageIds << id;
4224 }
4225
4226 query.addBindValue(status);
4227 query.addBindValue(imageIds);
4228 d->db->execBatch(query);
4229
4230 d->db->recordChangeset(CollectionImageChangeset(itemIDs, albumIDs, CollectionImageChangeset::Removed));
4231 }
4232
deleteRemovedItems()4233 void CoreDB::deleteRemovedItems()
4234 {
4235 d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE status=?;"),
4236 (int)DatabaseItem::Obsolete);
4237
4238 d->db->recordChangeset(CollectionImageChangeset(QList<qlonglong>(), QList<int>(), CollectionImageChangeset::RemovedDeleted));
4239 }
4240
renameAlbum(int albumID,int newAlbumRoot,const QString & newRelativePath)4241 void CoreDB::renameAlbum(int albumID, int newAlbumRoot, const QString& newRelativePath)
4242 {
4243 int albumRoot = getAlbumRootId(albumID);
4244 QString relativePath = getAlbumRelativePath(albumID);
4245
4246 if (relativePath == newRelativePath && albumRoot == newAlbumRoot)
4247 {
4248 return;
4249 }
4250
4251 // first delete any stale albums left behind at the destination of renaming
4252
4253 QMap<QString, QVariant> parameters;
4254 parameters.insert(QString::fromUtf8(":albumRoot"), newAlbumRoot);
4255 parameters.insert(QString::fromUtf8(":relativePath"), newRelativePath);
4256
4257 if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("deleteAlbumRootPath")), parameters))
4258 {
4259 return;
4260 }
4261
4262 // now update the album
4263
4264 d->db->execSql(QString::fromUtf8("UPDATE Albums SET albumRoot=?, relativePath=? WHERE id=? AND albumRoot=?;"),
4265 newAlbumRoot, newRelativePath, albumID, albumRoot);
4266 d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Renamed));
4267 }
4268
setTagName(int tagID,const QString & name)4269 void CoreDB::setTagName(int tagID, const QString& name)
4270 {
4271 d->db->execSql(QString::fromUtf8("UPDATE Tags SET name=? WHERE id=?;"),
4272 name, tagID);
4273 d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Renamed));
4274 }
4275
moveItem(int srcAlbumID,const QString & srcName,int dstAlbumID,const QString & dstName)4276 void CoreDB::moveItem(int srcAlbumID, const QString& srcName,
4277 int dstAlbumID, const QString& dstName)
4278 {
4279 // find id of src image
4280
4281 qlonglong imageId = getImageId(srcAlbumID, srcName);
4282
4283 if (imageId == -1)
4284 {
4285 return;
4286 }
4287
4288 // first delete any stale database entries (for destination) if any
4289
4290 deleteItem(dstAlbumID, dstName);
4291
4292 d->db->execSql(QString::fromUtf8("UPDATE Images SET album=?, name=? "
4293 "WHERE id=?;"),
4294 dstAlbumID, dstName, imageId);
4295 d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::Album)));
4296 d->db->recordChangeset(CollectionImageChangeset(imageId, srcAlbumID, CollectionImageChangeset::Moved));
4297 d->db->recordChangeset(CollectionImageChangeset(imageId, srcAlbumID, CollectionImageChangeset::Removed));
4298 d->db->recordChangeset(CollectionImageChangeset(imageId, dstAlbumID, CollectionImageChangeset::Added));
4299 }
4300
copyItem(int srcAlbumID,const QString & srcName,int dstAlbumID,const QString & dstName)4301 int CoreDB::copyItem(int srcAlbumID, const QString& srcName,
4302 int dstAlbumID, const QString& dstName)
4303 {
4304 // find id of src image
4305
4306 qlonglong srcId = getImageId(srcAlbumID, srcName);
4307
4308 if ((srcId == -1) || (dstAlbumID == -1) || dstName.isEmpty())
4309 {
4310 return -1;
4311 }
4312
4313 // check for src == dest
4314
4315 if ((srcAlbumID == dstAlbumID) && (srcName == dstName))
4316 {
4317 return srcId;
4318 }
4319
4320 // first delete any stale database entries if any
4321
4322 deleteItem(dstAlbumID, dstName);
4323
4324 // copy entry in Images table
4325
4326 QVariant id;
4327 d->db->execSql(QString::fromUtf8("INSERT INTO Images "
4328 "( album, name, status, category, modificationDate, fileSize, uniqueHash ) "
4329 " SELECT ?, ?, status, category, modificationDate, fileSize, uniqueHash "
4330 " FROM Images WHERE id=?;"),
4331 dstAlbumID, dstName, srcId, nullptr, &id);
4332
4333 if (id.isNull())
4334 {
4335 return -1;
4336 }
4337
4338 d->db->recordChangeset(ImageChangeset(id.toLongLong(), DatabaseFields::Set(DatabaseFields::ImagesAll)));
4339 d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), srcAlbumID, CollectionImageChangeset::Copied));
4340 d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), dstAlbumID, CollectionImageChangeset::Added));
4341
4342 // copy all other tables
4343
4344 copyImageAttributes(srcId, id.toLongLong());
4345
4346 return id.toLongLong();
4347 }
4348
copyImageAttributes(qlonglong srcId,qlonglong dstId)4349 void CoreDB::copyImageAttributes(qlonglong srcId, qlonglong dstId)
4350 {
4351 // Go through all image-specific tables and copy the entries
4352
4353 DatabaseFields::Set fields;
4354
4355 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageInformation "
4356 "(imageid, rating, creationDate, digitizationDate, orientation, "
4357 " width, height, format, colorDepth, colorModel) "
4358 "SELECT ?, rating, creationDate, digitizationDate, orientation, "
4359 " width, height, format, colorDepth, colorModel "
4360 "FROM ImageInformation WHERE imageid=?;"),
4361 dstId, srcId);
4362 fields |= DatabaseFields::ItemInformationAll;
4363
4364 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageMetadata "
4365 "(imageid, make, model, lens, aperture, focalLength, focalLength35, "
4366 " exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
4367 " whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) "
4368 "SELECT ?, make, model, lens, aperture, focalLength, focalLength35, "
4369 " exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
4370 " whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory "
4371 "FROM ImageMetadata WHERE imageid=?;"),
4372 dstId, srcId);
4373 fields |= DatabaseFields::ImageMetadataAll;
4374
4375 d->db->execSql(QString::fromUtf8("REPLACE INTO VideoMetadata "
4376 "(imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, "
4377 " frameRate, videoCodec) "
4378 "SELECT ?, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, "
4379 " frameRate, videoCodec "
4380 "FROM VideoMetadata WHERE imageid=?;"),
4381 dstId, srcId);
4382 fields |= DatabaseFields::VideoMetadataAll;
4383
4384 d->db->execSql(QString::fromUtf8("REPLACE INTO ImagePositions "
4385 "(imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
4386 " altitude, orientation, tilt, roll, accuracy, description) "
4387 "SELECT ?, latitude, latitudeNumber, longitude, longitudeNumber, "
4388 " altitude, orientation, tilt, roll, accuracy, description "
4389 "FROM ImagePositions WHERE imageid=?;"),
4390 dstId, srcId);
4391 fields |= DatabaseFields::ItemPositionsAll;
4392
4393 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageComments "
4394 "(imageid, type, language, author, date, comment) "
4395 "SELECT ?, type, language, author, date, comment "
4396 "FROM ImageComments WHERE imageid=?;"),
4397 dstId, srcId);
4398 fields |= DatabaseFields::ItemCommentsAll;
4399
4400 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageCopyright "
4401 "(imageid, property, value, extraValue) "
4402 "SELECT ?, property, value, extraValue "
4403 "FROM ImageCopyright WHERE imageid=?;"),
4404 dstId, srcId);
4405
4406 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageHistory "
4407 "(imageid, uuid, history) "
4408 "SELECT ?, uuid, history "
4409 "FROM ImageHistory WHERE imageid=?;"),
4410 dstId, srcId);
4411 fields |= DatabaseFields::ImageHistoryInfoAll;
4412
4413 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations "
4414 "(subject, object, type) "
4415 "SELECT ?, object, type "
4416 "FROM ImageRelations WHERE subject=?;"),
4417 dstId, srcId);
4418 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations "
4419 "(subject, object, type) "
4420 "SELECT subject, ?, type "
4421 "FROM ImageRelations WHERE object=?;"),
4422 dstId, srcId);
4423 fields |= DatabaseFields::ImageRelations;
4424
4425 d->db->recordChangeset(ImageChangeset(dstId, fields));
4426
4427 copyImageTags(srcId, dstId);
4428 copyImageProperties(srcId, dstId);
4429 }
4430
copyImageProperties(qlonglong srcId,qlonglong dstId)4431 void CoreDB::copyImageProperties(qlonglong srcId, qlonglong dstId)
4432 {
4433 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageProperties "
4434 "(imageid, property, value) "
4435 "SELECT ?, property, value "
4436 "FROM ImageProperties WHERE imageid=?;"),
4437 dstId, srcId);
4438 }
4439
copyImageTags(qlonglong srcId,qlonglong dstId)4440 void CoreDB::copyImageTags(qlonglong srcId, qlonglong dstId)
4441 {
4442 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTags "
4443 "(imageid, tagid) "
4444 "SELECT ?, tagid "
4445 "FROM ImageTags WHERE imageid=?;"),
4446 dstId, srcId);
4447
4448 d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTagProperties "
4449 "(imageid, tagid, property, value) "
4450 "SELECT ?, tagid, property, value "
4451 "FROM ImageTagProperties WHERE imageid=?;"),
4452 dstId, srcId);
4453
4454 // leave empty tag list for now
4455
4456 d->db->recordChangeset(ImageTagChangeset(dstId, QList<int>(),
4457 ImageTagChangeset::Added));
4458
4459 d->db->recordChangeset(ImageTagChangeset(dstId, QList<int>(),
4460 ImageTagChangeset::PropertiesChanged));
4461 }
4462
copyAlbumProperties(int srcAlbumID,int dstAlbumID) const4463 bool CoreDB::copyAlbumProperties(int srcAlbumID, int dstAlbumID) const
4464 {
4465 if (srcAlbumID == dstAlbumID)
4466 {
4467 return true;
4468 }
4469
4470 QList<QVariant> values;
4471 d->db->execSql(QString::fromUtf8("SELECT date, caption, collection, icon "
4472 "FROM Albums WHERE id=?;"),
4473 srcAlbumID, &values);
4474
4475 if (values.isEmpty())
4476 {
4477 qCWarning(DIGIKAM_DATABASE_LOG) << " src album ID " << srcAlbumID << " does not exist";
4478
4479 return false;
4480 }
4481
4482 QList<QVariant> boundValues;
4483 boundValues << values.at(0) << values.at(1) << values.at(2) << values.at(3);
4484 boundValues << dstAlbumID;
4485
4486 d->db->execSql(QString::fromUtf8("UPDATE Albums SET date=?, caption=?, "
4487 "collection=?, icon=? WHERE id=?;"),
4488 boundValues);
4489 return true;
4490 }
4491
getImageIdsFromArea(qreal lat1,qreal lat2,qreal lng1,qreal lng2,int,const QString &) const4492 QList<QVariant> CoreDB::getImageIdsFromArea(qreal lat1, qreal lat2, qreal lng1, qreal lng2, int /*sortMode*/,
4493 const QString& /*sortBy*/) const
4494 {
4495 QList<QVariant> values;
4496 QList<QVariant> boundValues;
4497 boundValues << lat1 << lat2 << lng1 << lng2;
4498
4499 d->db->execSql(QString::fromUtf8("Select ImageInformation.imageid, ImageInformation.rating, "
4500 "ImagePositions.latitudeNumber, ImagePositions.longitudeNumber "
4501 "FROM ImageInformation INNER JOIN ImagePositions "
4502 " ON ImageInformation.imageid = ImagePositions.imageid "
4503 " WHERE (ImagePositions.latitudeNumber>? AND ImagePositions.latitudeNumber<?) "
4504 " AND (ImagePositions.longitudeNumber>? AND ImagePositions.longitudeNumber<?);"),
4505 boundValues, &values);
4506
4507 return values;
4508 }
4509
clearMetadataFromImage(qlonglong imageID)4510 void CoreDB::clearMetadataFromImage(qlonglong imageID)
4511 {
4512 DatabaseFields::Set fields;
4513
4514 qCDebug(DIGIKAM_DATABASE_LOG) << "Clean up the image information, the "
4515 "file will be scanned again";
4516
4517 changeItemInformation(imageID, QVariantList() << 0, DatabaseFields::Rating);
4518
4519 d->db->execSql(QString::fromUtf8("DELETE FROM ImageProperties WHERE imageid=?;"),
4520 imageID);
4521
4522 d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright WHERE imageid=?;"),
4523 imageID);
4524
4525 d->db->execSql(QString::fromUtf8("DELETE FROM ImagePositions WHERE imageid=?;"),
4526 imageID);
4527 fields |= DatabaseFields::ItemPositionsAll;
4528
4529 d->db->execSql(QString::fromUtf8("DELETE FROM ImageComments WHERE imageid=?;"),
4530 imageID);
4531 fields |= DatabaseFields::ItemCommentsAll;
4532
4533 d->db->execSql(QString::fromUtf8("DELETE FROM ImageMetadata WHERE imageid=?;"),
4534 imageID);
4535 fields |= DatabaseFields::ImageMetadataAll;
4536
4537 d->db->execSql(QString::fromUtf8("DELETE FROM VideoMetadata WHERE imageid=?;"),
4538 imageID);
4539 fields |= DatabaseFields::VideoMetadataAll;
4540
4541 d->db->recordChangeset(ImageChangeset(imageID, fields));
4542
4543 QList<int> tagIds = getItemTagIDs(imageID);
4544
4545 if (!tagIds.isEmpty())
4546 {
4547 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags WHERE imageid=?;"),
4548 imageID);
4549 d->db->recordChangeset(ImageTagChangeset(imageID, tagIds, ImageTagChangeset::RemovedAll));
4550 }
4551
4552 QList<ImageTagProperty> properties = getImageTagProperties(imageID);
4553
4554 if (!properties.isEmpty())
4555 {
4556 QList<int> tids;
4557
4558 foreach (const ImageTagProperty& property, properties)
4559 {
4560 tids << property.tagId;
4561 }
4562
4563 d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties WHERE imageid=?;"),
4564 imageID);
4565 d->db->recordChangeset(ImageTagChangeset(imageID, tids, ImageTagChangeset::PropertiesChanged));
4566 }
4567 }
4568
integrityCheck() const4569 bool CoreDB::integrityCheck() const
4570 {
4571 QList<QVariant> values;
4572 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("checkCoreDbIntegrity")), &values);
4573
4574 switch (d->db->databaseType())
4575 {
4576 case BdEngineBackend::DbType::SQLite:
4577 {
4578 // For SQLite the integrity check returns a single row with one string column "ok" on success and multiple rows on error.
4579
4580 return ((values.size() == 1) && (values.first().toString().toLower().compare(QLatin1String("ok")) == 0));
4581 }
4582
4583 case BdEngineBackend::DbType::MySQL:
4584 {
4585 // For MySQL, for every checked table, the table name, operation (check), message type (status) and the message text (ok on success)
4586 // are returned. So we check if there are four elements and if yes, whether the fourth element is "ok".
4587 /*
4588 qCDebug(DIGIKAM_DATABASE_LOG) << "MySQL check returned " << values.size() << " rows";
4589 */
4590 if ((values.size() % 4) != 0)
4591 {
4592 return false;
4593 }
4594
4595 for (QList<QVariant>::iterator it = values.begin() ; it != values.end() ; )
4596 {
4597 QString tableName = (*it).toString();
4598 ++it;
4599 QString operation = (*it).toString();
4600 ++it;
4601 QString messageType = (*it).toString();
4602 ++it;
4603 QString messageText = (*it).toString();
4604 ++it;
4605
4606 if (messageText.toLower().compare(QLatin1String("ok")) != 0)
4607 {
4608 qCDebug(DIGIKAM_DATABASE_LOG) << "Failed integrity check for table " << tableName << ". Reason:" << messageText;
4609 return false;
4610 }
4611 else
4612 {
4613 /*
4614 qCDebug(DIGIKAM_DATABASE_LOG) << "Passed integrity check for table " << tableName;
4615 */
4616 }
4617 }
4618
4619 // No error conditions. Db passed the integrity check.
4620
4621 return true;
4622 }
4623
4624 default:
4625 {
4626 return false;
4627 }
4628 }
4629 }
4630
vacuum()4631 void CoreDB::vacuum()
4632 {
4633 d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("vacuumCoreDB")));
4634 }
4635
readSettings()4636 void CoreDB::readSettings()
4637 {
4638 KSharedConfig::Ptr config = KSharedConfig::openConfig();
4639 KConfigGroup group = config->group(d->configGroupName);
4640
4641 d->recentlyAssignedTags = group.readEntry(d->configRecentlyUsedTags, QList<int>());
4642 }
4643
writeSettings()4644 void CoreDB::writeSettings()
4645 {
4646 KSharedConfig::Ptr config = KSharedConfig::openConfig();
4647 KConfigGroup group = config->group(d->configGroupName);
4648
4649 group.writeEntry(d->configRecentlyUsedTags, d->recentlyAssignedTags);
4650 }
4651
4652 } // namespace Digikam
4653