diff --git a/core/libs/database/coredb/coredb.cpp b/core/libs/database/coredb/coredb.cpp index c26b7e19e0..7b33873e3b 100644 --- a/core/libs/database/coredb/coredb.cpp +++ b/core/libs/database/coredb/coredb.cpp @@ -1,4539 +1,4539 @@ /* ============================================================ * * This file is a part of digiKam project * https://www.digikam.org * * Date : 2004-06-18 * Description : Core database interface. * * Copyright (C) 2004-2005 by Renchi Raju * Copyright (C) 2006-2019 by Gilles Caulier * Copyright (C) 2006-2012 by Marcel Wiesweg * Copyright (C) 2012 by Andi Clemens * * This program is free software; you can redistribute it * and/or modify it under the terms of the GNU General * Public License as published by the Free Software Foundation; * either version 2, or (at your option) * any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * ============================================================ */ #include "coredb.h" // Qt includes #include #include #include #include // KDE includes #include #include // Local includes #include "digikam_debug.h" #include "digikam_globals.h" #include "coredbbackend.h" #include "collectionmanager.h" #include "collectionlocation.h" #include "dbengineactiontype.h" #include "tagscache.h" #include "album.h" namespace Digikam { class Q_DECL_HIDDEN CoreDB::Private { public: explicit Private() : db(nullptr), uniqueHashVersion(-1) { } static const QString configGroupName; static const QString configRecentlyUsedTags; CoreDbBackend* db; QList recentlyAssignedTags; int uniqueHashVersion; public: QString constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean); QList execRelatedImagesQuery(DbEngineSqlQuery& query, qlonglong id, DatabaseRelation::Type type); }; const QString CoreDB::Private::configGroupName(QLatin1String("CoreDB Settings")); const QString CoreDB::Private::configRecentlyUsedTags(QLatin1String("Recently Used Tags")); QString CoreDB::Private::constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean) { QString sql; if (fromOrTo) { sql = QString::fromUtf8("SELECT object FROM ImageRelations " "INNER JOIN Images ON ImageRelations.object=Images.id " " WHERE subject=? %1 AND status<3 %2;"); } else { sql = QString::fromUtf8("SELECT subject FROM ImageRelations " "INNER JOIN Images ON ImageRelations.subject=Images.id " " WHERE object=? %1 AND status<3 %2;"); } if (type != DatabaseRelation::UndefinedType) { sql = sql.arg(QString::fromUtf8("AND type=?")); } else { sql = sql.arg(QString()); } if (boolean) { sql = sql.arg(QString::fromUtf8("LIMIT 1")); } else { sql = sql.arg(QString()); } return sql; } QList CoreDB::Private::execRelatedImagesQuery(DbEngineSqlQuery& query, qlonglong id, DatabaseRelation::Type type) { QVariantList values; if (type == DatabaseRelation::UndefinedType) { db->execSql(query, id, &values); } else { db->execSql(query, id, type, &values); } QList imageIds; if (values.isEmpty()) { return imageIds; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { imageIds << (*it).toInt(); } return imageIds; } // -------------------------------------------------------- CoreDB::CoreDB(CoreDbBackend* const backend) : d(new Private) { d->db = backend; readSettings(); } CoreDB::~CoreDB() { writeSettings(); delete d; } QList CoreDB::getAlbumRoots() const { QList list; QList values; d->db->execSql(QString::fromUtf8("SELECT id, label, status, type, identifier, specificPath FROM AlbumRoots;"), &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { AlbumRootInfo info; info.id = (*it).toInt(); ++it; info.label = (*it).toString(); ++it; info.status = (*it).toInt(); ++it; info.type = (AlbumRoot::Type)(*it).toInt(); ++it; info.identifier = (*it).toString(); ++it; info.specificPath = (*it).toString(); ++it; list << info; } return list; } int CoreDB::addAlbumRoot(AlbumRoot::Type type, const QString& identifier, const QString& specificPath, const QString& label) const { QVariant id; d->db->execSql(QString::fromUtf8("REPLACE INTO AlbumRoots (type, label, status, identifier, specificPath) " "VALUES(?, ?, 0, ?, ?);"), (int)type, label, identifier, specificPath, nullptr, &id); d->db->recordChangeset(AlbumRootChangeset(id.toInt(), AlbumRootChangeset::Added)); return id.toInt(); } void CoreDB::deleteAlbumRoot(int rootId) { d->db->execSql(QString::fromUtf8("DELETE FROM AlbumRoots WHERE id=?;"), rootId); QMap parameters; parameters.insert(QLatin1String(":albumRoot"), rootId); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRoot")), parameters)) { return; } d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::Deleted)); } void CoreDB::migrateAlbumRoot(int rootId, const QString& identifier) { d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET identifier=? WHERE id=?;"), identifier, rootId); d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged)); } void CoreDB::setAlbumRootLabel(int rootId, const QString& newLabel) { d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET label=? WHERE id=?;"), newLabel, rootId); d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged)); } void CoreDB::changeAlbumRootType(int rootId, AlbumRoot::Type newType) { d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET type=? WHERE id=?;"), (int)newType, rootId); d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged)); } void CoreDB::setAlbumRootPath(int rootId, const QString& newPath) { d->db->execSql(QString::fromUtf8("UPDATE AlbumRoots SET specificPath=? WHERE id=?;"), newPath, rootId); d->db->recordChangeset(AlbumRootChangeset(rootId, AlbumRootChangeset::PropertiesChanged)); } AlbumInfo::List CoreDB::scanAlbums() const { AlbumInfo::List aList; QList values; d->db->execSql(QString::fromUtf8("SELECT albumRoot, id, relativePath, date, caption, collection, icon " "FROM Albums WHERE albumRoot != 0;"), // exclude stale albums &values); QString iconAlbumUrl, iconName; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { AlbumInfo info; info.albumRootId = (*it).toInt(); ++it; info.id = (*it).toInt(); ++it; info.relativePath = (*it).toString(); ++it; info.date = (*it).toDate(); ++it; info.caption = (*it).toString(); ++it; info.category = (*it).toString(); ++it; info.iconId = (*it).toLongLong(); ++it; aList.append(info); } return aList; } TagInfo::List CoreDB::scanTags() const { TagInfo::List tList; QList values; d->db->execSql(QString::fromUtf8("SELECT id, pid, name, icon, iconkde FROM Tags;"), &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { TagInfo info; info.id = (*it).toInt(); ++it; info.pid = (*it).toInt(); ++it; info.name = (*it).toString(); ++it; info.iconId = (*it).toLongLong(); ++it; info.icon = (*it).toString(); ++it; tList.append(info); } return tList; } TagInfo CoreDB::getTagInfo(int tagId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id, pid, name, icon, iconkde WHERE id=? FROM Tags;"), tagId, &values); TagInfo info; if (!values.isEmpty() && values.size() == 5) { QList::const_iterator it = values.constBegin(); info.id = (*it).toInt(); ++it; info.pid = (*it).toInt(); ++it; info.name = (*it).toString(); ++it; info.iconId = (*it).toLongLong(); ++it; info.icon = (*it).toString(); ++it; } return info; } SearchInfo::List CoreDB::scanSearches() const { SearchInfo::List searchList; QList values; d->db->execSql(QString::fromUtf8("SELECT id, type, name, query FROM Searches;"), &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { SearchInfo info; info.id = (*it).toInt(); ++it; info.type = (DatabaseSearch::Type)(*it).toInt(); ++it; info.name = (*it).toString(); ++it; info.query = (*it).toString(); ++it; searchList.append(info); } return searchList; } QList CoreDB::getAlbumShortInfos() const { QList values; d->db->execSql(QString::fromUtf8("SELECT id, relativePath, albumRoot FROM Albums ORDER BY id;"), &values); QList albumList; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { AlbumShortInfo info; info.id = (*it).toInt(); ++it; info.relativePath = (*it).toString(); ++it; info.albumRootId = (*it).toInt(); ++it; albumList << info; } return albumList; } QList CoreDB::getTagShortInfos() const { QList values; d->db->execSql(QString::fromUtf8("SELECT id, pid, name FROM Tags ORDER BY id;"), &values); QList tagList; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { TagShortInfo info; info.id = (*it).toInt(); ++it; info.pid = (*it).toInt(); ++it; info.name = (*it).toString(); ++it; tagList << info; } return tagList; } int CoreDB::addAlbum(int albumRootId, const QString& relativePath, const QString& caption, const QDate& date, const QString& collection) const { QVariant id; QList boundValues; boundValues << albumRootId << relativePath << date << caption << collection; d->db->execSql(QString::fromUtf8("REPLACE INTO Albums (albumRoot, relativePath, date, caption, collection) " "VALUES(?, ?, ?, ?, ?);"), boundValues, nullptr, &id); d->db->recordChangeset(AlbumChangeset(id.toInt(), AlbumChangeset::Added)); return id.toInt(); } void CoreDB::setAlbumCaption(int albumID, const QString& caption) { d->db->execSql(QString::fromUtf8("UPDATE Albums SET caption=? WHERE id=?;"), caption, albumID); d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged)); } void CoreDB::setAlbumCategory(int albumID, const QString& category) { // TODO : change "collection" property in DB ALbum table to "category" d->db->execSql(QString::fromUtf8("UPDATE Albums SET collection=? WHERE id=?;"), category, albumID); d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged)); } void CoreDB::setAlbumDate(int albumID, const QDate& date) { d->db->execSql(QString::fromUtf8("UPDATE Albums SET date=? WHERE id=?;"), date, albumID); d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged)); } void CoreDB::setAlbumIcon(int albumID, qlonglong iconID) { if (iconID == 0) { d->db->execSql(QString::fromUtf8("UPDATE Albums SET icon=NULL WHERE id=?;"), albumID); } else { d->db->execSql(QString::fromUtf8("UPDATE Albums SET icon=? WHERE id=?;"), iconID, albumID); } d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::PropertiesChanged)); } void CoreDB::deleteAlbum(int albumID) { QMap parameters; parameters.insert(QLatin1String(":albumId"), albumID); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumID")), parameters)) { return; } d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Deleted)); } void CoreDB::makeStaleAlbum(int albumID) { // We need to work around the table constraint, no we want to delete older stale albums with // the same relativePath, and adjust relativePaths depending on albumRoot. QList values; // retrieve information d->db->execSql(QString::fromUtf8("SELECT albumRoot, relativePath FROM Albums WHERE id=?;"), albumID, &values); if (values.isEmpty()) { return; } // prepend albumRootId to relativePath. relativePath is unused and officially undefined after this call. QString newRelativePath = values.at(0).toString() + QLatin1Char('-') + values.at(1).toString(); // delete older stale albums QMap parameters; parameters.insert(QLatin1String(":albumRoot"), 0); parameters.insert(QLatin1String(":relativePath"), newRelativePath); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRootPath")), parameters)) { return; } // now do our update d->db->setForeignKeyChecks(false); d->db->execSql(QString::fromUtf8("UPDATE Albums SET albumRoot=0, relativePath=? WHERE id=?;"), newRelativePath, albumID); // for now, we make no distinction to deleteAlbums wrt to changeset d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Deleted)); d->db->setForeignKeyChecks(true); } void CoreDB::deleteStaleAlbums() { QMap parameters; parameters.insert(QLatin1String(":albumRoot"), 0); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("deleteAlbumRoot")), parameters)) { return; } // deliberately no changeset here, is done above } int CoreDB::addTag(int parentTagID, const QString& name, const QString& iconKDE, qlonglong iconID) const { QVariant id; QMap parameters; parameters.insert(QLatin1String(":tagPID"), parentTagID); parameters.insert(QLatin1String(":tagname"), name); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QLatin1String("InsertTag")), parameters, nullptr , &id)) { return -1; } if (!iconKDE.isEmpty()) { d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=? WHERE id=?;"), iconKDE, id.toInt()); } else if (iconID == 0) { d->db->execSql(QString::fromUtf8("UPDATE Tags SET icon=NULL WHERE id=?;"), id.toInt()); } else { d->db->execSql(QString::fromUtf8("UPDATE Tags SET icon=? WHERE id=?;"), iconID, id.toInt()); } d->db->recordChangeset(TagChangeset(id.toInt(), TagChangeset::Added)); return id.toInt(); } void CoreDB::deleteTag(int tagID) { /* QString("DELETE FROM Tags WHERE id=?;"), tagID */ QMap bindingMap; bindingMap.insert(QLatin1String(":tagID"), tagID); d->db->execDBAction(d->db->getDBAction(QLatin1String("DeleteTag")), bindingMap); d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Deleted)); } void CoreDB::setTagIcon(int tagID, const QString& iconKDE, qlonglong iconID) { int _iconID = iconKDE.isEmpty() ? iconID : 0; QString _iconKDE = iconKDE; if (iconKDE.isEmpty() || iconKDE.toLower() == QLatin1String("tag")) { _iconKDE.clear(); } if (_iconID == 0) { d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=?, icon=NULL WHERE id=?;"), _iconKDE, tagID); } else { d->db->execSql(QString::fromUtf8("UPDATE Tags SET iconkde=?, icon=? WHERE id=?;"), _iconKDE, _iconID, tagID); } d->db->recordChangeset(TagChangeset(tagID, TagChangeset::IconChanged)); } void CoreDB::setTagParentID(int tagID, int newParentTagID) { if (d->db->databaseType() == BdEngineBackend::DbType::SQLite) { d->db->execSql(QString::fromUtf8("UPDATE OR REPLACE Tags SET pid=? WHERE id=?;"), newParentTagID, tagID); } else { d->db->execSql(QString::fromUtf8("UPDATE Tags SET pid=? WHERE id=?;"), newParentTagID, tagID); // NOTE: Update the Mysql TagsTree table which is used only in some search SQL queries (See lft/rgt tag ID properties). // In SQlite, it is nicely maintained by Triggers. // With MySQL, this did not work for some reason, and we patch a tree structure mimics in a different way. QMap bindingMap; bindingMap.insert(QLatin1String(":tagID"), tagID); bindingMap.insert(QLatin1String(":newTagPID"), newParentTagID); d->db->execDBAction(d->db->getDBAction(QLatin1String("MoveTagTree")), bindingMap); } d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Reparented)); } QList CoreDB::getTagProperties(int tagId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT property, value FROM TagProperties WHERE tagid=?;"), tagId, &values); QList properties; if (values.isEmpty()) { return properties; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { TagProperty property; property.tagId = tagId; property.property = (*it).toString(); ++it; property.value = (*it).toString(); ++it; properties << property; } return properties; } QList CoreDB::getTagProperties(const QString& property) const { QList values; d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM TagProperties WHERE property=?;"), property, &values); QList properties; if (values.isEmpty()) { return properties; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { TagProperty property; property.tagId = (*it).toInt(); ++it; property.property = (*it).toString(); ++it; property.value = (*it).toString(); ++it; properties << property; } return properties; } QList CoreDB::getTagProperties() const { QList values; d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM TagProperties ORDER BY tagid, property;"), &values); QList properties; if (values.isEmpty()) { return properties; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { TagProperty property; property.tagId = (*it).toInt(); ++it; property.property = (*it).toString(); ++it; property.value = (*it).toString(); ++it; properties << property; } return properties; } QList< int > CoreDB::getTagsWithProperty(const QString& property) const { QList values; d->db->execSql(QString::fromUtf8("SELECT DISTINCT tagid FROM TagProperties WHERE property=?;"), property, &values); QList tagIds; foreach (const QVariant& var, values) { tagIds << var.toInt(); } return tagIds; } void CoreDB::addTagProperty(int tagId, const QString& property, const QString& value) { d->db->execSql(QString::fromUtf8("INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);"), tagId, property, value); d->db->recordChangeset(TagChangeset(tagId, TagChangeset::PropertiesChanged)); } void CoreDB::addTagProperty(const TagProperty& property) { addTagProperty(property.tagId, property.property, property.value); } void CoreDB::removeTagProperties(int tagId, const QString& property, const QString& value) { if (property.isNull()) { d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=?;"), tagId); } else if (value.isNull()) { d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=? AND property=?;"), tagId, property); } else { d->db->execSql(QString::fromUtf8("DELETE FROM TagProperties WHERE tagid=? AND property=? AND value=?;"), tagId, property, value); } d->db->recordChangeset(TagChangeset(tagId, TagChangeset::PropertiesChanged)); } int CoreDB::addSearch(DatabaseSearch::Type type, const QString& name, const QString& query) const { QVariant id; if (!d->db->execSql(QString::fromUtf8("INSERT INTO Searches (type, name, query) VALUES(?, ?, ?);"), type, name, query, nullptr, &id)) { return -1; } d->db->recordChangeset(SearchChangeset(id.toInt(), SearchChangeset::Added)); return id.toInt(); } void CoreDB::updateSearch(int searchID, DatabaseSearch::Type type, const QString& name, const QString& query) { d->db->execSql(QString::fromUtf8("UPDATE Searches SET type=?, name=?, query=? WHERE id=?;"), type, name, query, searchID); d->db->recordChangeset(SearchChangeset(searchID, SearchChangeset::Changed)); } void CoreDB::deleteSearch(int searchID) { d->db->execSql(QString::fromUtf8("DELETE FROM Searches WHERE id=?;"), searchID); d->db->recordChangeset(SearchChangeset(searchID, SearchChangeset::Deleted)); } void CoreDB::deleteSearches(DatabaseSearch::Type type) { d->db->execSql(QString::fromUtf8("DELETE FROM Searches WHERE type=?;"), type); d->db->recordChangeset(SearchChangeset(0, SearchChangeset::Deleted)); } QString CoreDB::getSearchQuery(int searchId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT query FROM Searches WHERE id=?;"), searchId, &values); if (values.isEmpty()) { return QString(); } return values.first().toString(); } SearchInfo CoreDB::getSearchInfo(int searchId) const { SearchInfo info; QList values; d->db->execSql(QString::fromUtf8("SELECT id, type, name, query FROM Searches WHERE id=?;"), searchId, &values); if (values.size() == 4) { QList::const_iterator it = values.constBegin(); info.id = (*it).toInt(); ++it; info.type = (DatabaseSearch::Type)(*it).toInt(); ++it; info.name = (*it).toString(); ++it; info.query = (*it).toString(); ++it; } return info; } void CoreDB::setSetting(const QString& keyword, const QString& value) { d->db->execSql(QString::fromUtf8("REPLACE INTO Settings VALUES (?,?);"), keyword, value); } QString CoreDB::getSetting(const QString& keyword) const { QList values; d->db->execSql(QString::fromUtf8("SELECT value FROM Settings " "WHERE keyword=?;"), keyword, &values); if (values.isEmpty()) { return QString(); } return values.first().toString(); } // helper method static QStringList joinMainAndUserFilterString(const QChar& sep, const QString& filter, const QString& userFilter) { QSet filterSet; QStringList userFilterList; QStringList sortedList; filterSet = filter.split(sep, QString::SkipEmptyParts).toSet(); userFilterList = userFilter.split(sep, QString::SkipEmptyParts); foreach (const QString& userFormat, userFilterList) { if (userFormat.startsWith(QLatin1Char('-'))) { filterSet.remove(userFormat.mid(1)); } else { filterSet << userFormat; } } sortedList = filterSet.toList(); sortedList.sort(); return sortedList; } void CoreDB::getFilterSettings(QStringList* imageFilter, QStringList* videoFilter, QStringList* audioFilter) { QString imageFormats, videoFormats, audioFormats, userImageFormats, userVideoFormats, userAudioFormats; if (imageFilter) { imageFormats = getSetting(QLatin1String("databaseImageFormats")); userImageFormats = getSetting(QLatin1String("databaseUserImageFormats")); *imageFilter = joinMainAndUserFilterString(QLatin1Char(';'), imageFormats, userImageFormats); } if (videoFilter) { videoFormats = getSetting(QLatin1String("databaseVideoFormats")); userVideoFormats = getSetting(QLatin1String("databaseUserVideoFormats")); *videoFilter = joinMainAndUserFilterString(QLatin1Char(';'), videoFormats, userVideoFormats); } if (audioFilter) { audioFormats = getSetting(QLatin1String("databaseAudioFormats")); userAudioFormats = getSetting(QLatin1String("databaseUserAudioFormats")); *audioFilter = joinMainAndUserFilterString(QLatin1Char(';'), audioFormats, userAudioFormats); } } void CoreDB::getUserFilterSettings(QString* imageFilterString, QString* videoFilterString, QString* audioFilterString) { if (imageFilterString) { *imageFilterString = getSetting(QLatin1String("databaseUserImageFormats")); } if (videoFilterString) { *videoFilterString = getSetting(QLatin1String("databaseUserVideoFormats")); } if (audioFilterString) { *audioFilterString = getSetting(QLatin1String("databaseUserAudioFormats")); } } void CoreDB::getUserIgnoreDirectoryFilterSettings(QString* ignoreDirectoryFilterString) { *ignoreDirectoryFilterString = getSetting(QLatin1String("databaseUserIgnoreDirectoryFormats")); } void CoreDB::getIgnoreDirectoryFilterSettings(QStringList* ignoreDirectoryFilter) { QString ignoreDirectoryFormats, userIgnoreDirectoryFormats; ignoreDirectoryFormats = getSetting(QLatin1String("databaseIgnoreDirectoryFormats")); userIgnoreDirectoryFormats = getSetting(QLatin1String("databaseUserIgnoreDirectoryFormats")); *ignoreDirectoryFilter = joinMainAndUserFilterString(QLatin1Char(';'), ignoreDirectoryFormats, userIgnoreDirectoryFormats); } void CoreDB::setFilterSettings(const QStringList& imageFilter, const QStringList& videoFilter, const QStringList& audioFilter) { setSetting(QLatin1String("databaseImageFormats"), imageFilter.join(QLatin1Char(';'))); setSetting(QLatin1String("databaseVideoFormats"), videoFilter.join(QLatin1Char(';'))); setSetting(QLatin1String("databaseAudioFormats"), audioFilter.join(QLatin1Char(';'))); } void CoreDB::setIgnoreDirectoryFilterSettings(const QStringList& ignoreDirectoryFilter) { setSetting(QLatin1String("databaseIgnoreDirectoryFormats"), ignoreDirectoryFilter.join(QLatin1Char(';'))); } void CoreDB::setUserFilterSettings(const QStringList& imageFilter, const QStringList& videoFilter, const QStringList& audioFilter) { setSetting(QLatin1String("databaseUserImageFormats"), imageFilter.join(QLatin1Char(';'))); setSetting(QLatin1String("databaseUserVideoFormats"), videoFilter.join(QLatin1Char(';'))); setSetting(QLatin1String("databaseUserAudioFormats"), audioFilter.join(QLatin1Char(';'))); } void CoreDB::setUserIgnoreDirectoryFilterSettings(const QStringList& ignoreDirectoryFilters) { qCDebug(DIGIKAM_DATABASE_LOG) << "CoreDB::setUserIgnoreDirectoryFilterSettings. " "ignoreDirectoryFilterString: " << ignoreDirectoryFilters.join(QLatin1Char(';')); setSetting(QLatin1String("databaseUserIgnoreDirectoryFormats"), ignoreDirectoryFilters.join(QLatin1Char(';'))); } QUuid CoreDB::databaseUuid() { QString uuidString = getSetting(QLatin1String("databaseUUID")); QUuid uuid = QUuid(uuidString); if (uuidString.isNull() || uuid.isNull()) { uuid = QUuid::createUuid(); setSetting(QLatin1String("databaseUUID"), uuid.toString()); } return uuid; } int CoreDB::getUniqueHashVersion() const { if (d->uniqueHashVersion == -1) { QString v = getSetting(QLatin1String("uniqueHashVersion")); if (v.isEmpty()) { d->uniqueHashVersion = 1; } else { d->uniqueHashVersion = v.toInt(); } } return d->uniqueHashVersion; } bool CoreDB::isUniqueHashV2() const { return (getUniqueHashVersion() == 2); } void CoreDB::setUniqueHashVersion(int version) { d->uniqueHashVersion = version; setSetting(QLatin1String("uniqueHashVersion"), QString::number(d->uniqueHashVersion)); } qlonglong CoreDB::getImageId(int albumID, const QString& name) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE album=? AND name=?;"), albumID, name, &values); if (values.isEmpty()) { return -1; } return values.first().toLongLong(); } QList CoreDB::getImageIds(int albumID, const QString& name, DatabaseItem::Status status) const { QList values; if (albumID == -1) { d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE album IS NULL AND name=? AND status=?;"), name, status, &values); } else { d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE album=? AND name=? AND status=?;"), albumID, name, status, &values); } QList items; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { items << it->toLongLong(); } return items; } QList CoreDB::getImageIds(DatabaseItem::Status status) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE status=?;"), status, &values); QList imageIds; foreach (const QVariant& object, values) { imageIds << object.toLongLong(); } return imageIds; } QList CoreDB::getImageIds(DatabaseItem::Status status, DatabaseItem::Category category) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE status=? AND category=?;"), status, category, &values); QList imageIds; foreach (const QVariant& object, values) { imageIds << object.toLongLong(); } return imageIds; } qlonglong CoreDB::findImageId(int albumID, const QString& name, DatabaseItem::Status status, DatabaseItem::Category category, qlonglong fileSize, const QString& uniqueHash) const { QList values; QVariantList boundValues; // Add the standard bindings boundValues << name << (int)status << (int)category << fileSize << uniqueHash; // If the album id is -1, no album is assigned. Get all images with NULL album if (albumID == -1) { d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE name=? AND status=? " "AND category=? AND fileSize=? " "AND uniqueHash=? AND album IS NULL;"), boundValues, &values); } else { boundValues << albumID; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE name=? AND status=? " "AND category=? AND fileSize=? " "AND uniqueHash=? AND album=?;"), boundValues, &values); } if (values.isEmpty()) { return -1; } // If there are several identical image ids, // probably use the last most recent one. return values.last().toLongLong(); } QStringList CoreDB::getItemTagNames(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT name FROM Tags " "WHERE id IN (SELECT tagid FROM ImageTags " " WHERE imageid=?) " " ORDER BY name;"), imageID, &values); QStringList names; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { names << it->toString(); } return names; } QList CoreDB::getItemTagIDs(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT tagid FROM ImageTags WHERE imageID=?;"), imageID, &values); QList ids; if (values.isEmpty()) { return ids; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { ids << it->toInt(); } return ids; } QVector > CoreDB::getItemsTagIDs(const QList imageIds) const { if (imageIds.isEmpty()) { return QVector >(); } QVector > results(imageIds.size()); DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("SELECT tagid FROM ImageTags WHERE imageID=?;")); QVariantList values; for (int i = 0 ; i < imageIds.size() ; ++i) { d->db->execSql(query, imageIds[i], &values); QList& tagIds = results[i]; foreach (const QVariant& v, values) { tagIds << v.toInt(); } } return results; } QList CoreDB::getImageTagProperties(qlonglong imageId, int tagId) const { QList values; if (tagId == -1) { d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM ImageTagProperties " "WHERE imageid=?;"), imageId, &values); } else { d->db->execSql(QString::fromUtf8("SELECT tagid, property, value FROM ImageTagProperties " "WHERE imageid=? AND tagid=?;"), imageId, tagId, &values); } QList properties; if (values.isEmpty()) { return properties; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { ImageTagProperty property; property.imageId = imageId; property.tagId = (*it).toInt(); ++it; property.property = (*it).toString(); ++it; property.value = (*it).toString(); ++it; properties << property; } return properties; } QList CoreDB::getTagIdsWithProperties(qlonglong imageId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT DISTINCT tagid FROM ImageTagProperties WHERE imageid=?;"), imageId, &values); QList tagIds; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { tagIds << (*it).toInt(); } return tagIds; } void CoreDB::addImageTagProperty(qlonglong imageId, int tagId, const QString& property, const QString& value) { d->db->execSql(QString::fromUtf8("INSERT INTO ImageTagProperties (imageid, tagid, property, value) " "VALUES(?, ?, ?, ?);"), imageId, tagId, property, value); d->db->recordChangeset(ImageTagChangeset(imageId, tagId, ImageTagChangeset::PropertiesChanged)); } void CoreDB::addImageTagProperty(const ImageTagProperty& property) { addImageTagProperty(property.imageId, property.tagId, property.property, property.value); } void CoreDB::removeImageTagProperties(qlonglong imageId, int tagId, const QString& property, const QString& value) { if (tagId == -1) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties " "WHERE imageid=?;"), imageId); } else if (property.isNull()) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties " "WHERE imageid=? AND tagid=?;"), imageId, tagId); } else if (value.isNull()) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties " "WHERE imageid=? AND tagid=? AND property=?;"), imageId, tagId, property); } else { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties " "WHERE imageid=? AND tagid=? AND property=? AND value=?;"), imageId, tagId, property, value); } d->db->recordChangeset(ImageTagChangeset(imageId, tagId, ImageTagChangeset::PropertiesChanged)); } ItemShortInfo CoreDB::getItemShortInfo(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT Images.name, Albums.albumRoot, Albums.relativePath, Albums.id " "FROM Images " " INNER JOIN Albums ON Albums.id=Images.album " " WHERE Images.id=?;"), imageID, &values); ItemShortInfo info; if (!values.isEmpty()) { info.id = imageID; info.itemName = values.at(0).toString(); info.albumRootID = values.at(1).toInt(); info.album = values.at(2).toString(); info.albumID = values.at(3).toInt(); } return info; } ItemShortInfo CoreDB::getItemShortInfo(int albumRootId, const QString& relativePath, const QString& name) const { QList values; d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.id FROM Images " "INNER JOIN Albums ON Albums.id=Images.album " " WHERE name=? AND albumRoot=? AND relativePath=?;"), name, albumRootId, relativePath, &values); ItemShortInfo info; if (!values.isEmpty()) { info.id = values.at(0).toLongLong(); info.itemName = name; info.albumRootID = albumRootId; info.album = relativePath; info.albumID = values.at(1).toInt(); } return info; } bool CoreDB::hasTags(const QList& imageIDList) const { QList ids; if (imageIDList.isEmpty()) { return false; } QList values; QList boundValues; QString sql = QString::fromUtf8("SELECT COUNT(tagid) FROM ImageTags " "WHERE imageid=? "); boundValues << imageIDList.first(); QList::const_iterator it = imageIDList.constBegin(); ++it; for (; it != imageIDList.constEnd() ; ++it) { sql += QString::fromUtf8(" OR imageid=? "); boundValues << (*it); } sql += QString::fromUtf8(";"); d->db->execSql(sql, boundValues, &values); if (values.isEmpty() || values.first().toInt() == 0) { return false; } return true; } QList CoreDB::getItemCommonTagIDs(const QList& imageIDList) const { QList ids; if (imageIDList.isEmpty()) { return ids; } QList values; QList boundValues; QString sql = QString::fromUtf8("SELECT DISTINCT tagid FROM ImageTags " "WHERE imageid=? "); boundValues << imageIDList.first(); QList::const_iterator it = imageIDList.constBegin(); ++it; for (; it != imageIDList.constEnd() ; ++it) { sql += QString::fromUtf8(" OR imageid=? "); boundValues << (*it); } sql += QString::fromUtf8(";"); d->db->execSql(sql, boundValues, &values); if (values.isEmpty()) { return ids; } - for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) + for (QList::const_iterator it2 = values.constBegin() ; it2 != values.constEnd() ; ++it2) { - ids << it->toInt(); + ids << it2->toInt(); } return ids; } QVariantList CoreDB::getImagesFields(qlonglong imageID, DatabaseFields::Images fields) const { QVariantList values; if (fields != DatabaseFields::ImagesNone) { QString query(QString::fromUtf8("SELECT ")); QStringList fieldNames = imagesFieldList(fields); query += fieldNames.join(QString::fromUtf8(", ")); query += QString::fromUtf8(" FROM Images WHERE id=?;"); d->db->execSql(query, imageID, &values); if (fieldNames.size() != values.size()) { return QVariantList(); } // Convert date times to QDateTime, they come as QString if ((fields & DatabaseFields::ModificationDate)) { int index = fieldNames.indexOf(QLatin1String("modificationDate")); values[index] = values.at(index).toDateTime(); } } return values; } QVariantList CoreDB::getItemInformation(qlonglong imageID, DatabaseFields::ItemInformation fields) const { QVariantList values; if (fields != DatabaseFields::ItemInformationNone) { QString query(QString::fromUtf8("SELECT ")); QStringList fieldNames = imageInformationFieldList(fields); query += fieldNames.join(QString::fromUtf8(", ")); query += QString::fromUtf8(" FROM ImageInformation WHERE imageid=?;"); d->db->execSql(query, imageID, &values); if (fieldNames.size() != values.size()) { return QVariantList(); } // Convert date times to QDateTime, they come as QString if ((fields & DatabaseFields::CreationDate)) { int index = fieldNames.indexOf(QLatin1String("creationDate")); values[index] = values.at(index).toDateTime(); } if ((fields & DatabaseFields::DigitizationDate)) { int index = fieldNames.indexOf(QLatin1String("digitizationDate")); values[index] = values.at(index).toDateTime(); } } return values; } QVariantList CoreDB::getImageMetadata(qlonglong imageID, DatabaseFields::ImageMetadata fields) const { QVariantList values; if (fields != DatabaseFields::ImageMetadataNone) { QString query(QString::fromUtf8("SELECT ")); QStringList fieldNames = imageMetadataFieldList(fields); query += fieldNames.join(QString::fromUtf8(", ")); query += QString::fromUtf8(" FROM ImageMetadata WHERE imageid=?;"); d->db->execSql(query, imageID, &values); // For some reason, if REAL values may be required from variables stored as QString QVariants. Convert code will come here. } return values; } QVariantList CoreDB::getVideoMetadata(qlonglong imageID, DatabaseFields::VideoMetadata fields) const { QVariantList values; if (fields != DatabaseFields::VideoMetadataNone) { QString query(QString::fromUtf8("SELECT ")); QStringList fieldNames = videoMetadataFieldList(fields); query += fieldNames.join(QString::fromUtf8(", ")); query += QString::fromUtf8(" FROM VideoMetadata WHERE imageid=?;"); d->db->execSql(query, imageID, &values); // For some reason REAL values may come as QString QVariants. Convert here. if (values.size() == fieldNames.size() && ((fields & DatabaseFields::Aperture) || (fields & DatabaseFields::FocalLength) || (fields & DatabaseFields::FocalLength35) || (fields & DatabaseFields::ExposureTime) || (fields & DatabaseFields::SubjectDistance)) ) { for (int i = 0 ; i < values.size() ; ++i) { if (values.at(i).type() == QVariant::String && (fieldNames.at(i) == QLatin1String("aperture") || fieldNames.at(i) == QLatin1String("focalLength") || fieldNames.at(i) == QLatin1String("focalLength35") || fieldNames.at(i) == QLatin1String("exposureTime") || fieldNames.at(i) == QLatin1String("subjectDistance")) ) { values[i] = values.at(i).toDouble(); } } } } return values; } QVariantList CoreDB::getItemPosition(qlonglong imageID, DatabaseFields::ItemPositions fields) const { QVariantList values; if (fields != DatabaseFields::ItemPositionsNone) { QString query(QString::fromUtf8("SELECT ")); QStringList fieldNames = imagePositionsFieldList(fields); query += fieldNames.join(QString::fromUtf8(", ")); query += QString::fromUtf8(" FROM ImagePositions WHERE imageid=?;"); d->db->execSql(query, imageID, &values); // For some reason REAL values may come as QString QVariants. Convert here. if (values.size() == fieldNames.size() && ((fields & DatabaseFields::LatitudeNumber) || (fields & DatabaseFields::LongitudeNumber) || (fields & DatabaseFields::Altitude) || (fields & DatabaseFields::PositionOrientation) || (fields & DatabaseFields::PositionTilt) || (fields & DatabaseFields::PositionRoll) || (fields & DatabaseFields::PositionAccuracy)) ) { for (int i = 0 ; i < values.size() ; ++i) { if (values.at(i).type() == QVariant::String && (fieldNames.at(i) == QLatin1String("latitudeNumber") || fieldNames.at(i) == QLatin1String("longitudeNumber") || fieldNames.at(i) == QLatin1String("altitude") || fieldNames.at(i) == QLatin1String("orientation") || fieldNames.at(i) == QLatin1String("tilt") || fieldNames.at(i) == QLatin1String("roll") || fieldNames.at(i) == QLatin1String("accuracy")) ) { if (!values.at(i).isNull()) values[i] = values.at(i).toDouble(); } } } } return values; } QVariantList CoreDB::getItemPositions(QList imageIDs, DatabaseFields::ItemPositions fields) const { QVariantList values; if (fields != DatabaseFields::ItemPositionsNone) { QString sql(QString::fromUtf8("SELECT ")); QStringList fieldNames = imagePositionsFieldList(fields); sql += fieldNames.join(QString::fromUtf8(", ")); sql += QString::fromUtf8(" FROM ImagePositions WHERE imageid=?;"); DbEngineSqlQuery query = d->db->prepareQuery(sql); foreach (const qlonglong& imageid, imageIDs) { QVariantList singleValueList; d->db->execSql(query, imageid, &singleValueList); values << singleValueList; } // For some reason REAL values may come as QString QVariants. Convert here. if (values.size() == fieldNames.size() && (fields & DatabaseFields::LatitudeNumber || fields & DatabaseFields::LongitudeNumber || fields & DatabaseFields::Altitude || fields & DatabaseFields::PositionOrientation || fields & DatabaseFields::PositionTilt || fields & DatabaseFields::PositionRoll || fields & DatabaseFields::PositionAccuracy) ) { for (int i = 0 ; i < values.size() ; ++i) { if (values.at(i).type() == QVariant::String && (fieldNames.at(i) == QLatin1String("latitudeNumber") || fieldNames.at(i) == QLatin1String("longitudeNumber") || fieldNames.at(i) == QLatin1String("altitude") || fieldNames.at(i) == QLatin1String("orientation") || fieldNames.at(i) == QLatin1String("tilt") || fieldNames.at(i) == QLatin1String("roll") || fieldNames.at(i) == QLatin1String("accuracy")) ) { if (!values.at(i).isNull()) values[i] = values.at(i).toDouble(); } } } } return values; } void CoreDB::addItemInformation(qlonglong imageID, const QVariantList& infos, DatabaseFields::ItemInformation fields) { if (fields == DatabaseFields::ItemInformationNone) { return; } QString query(QString::fromUtf8("REPLACE INTO ImageInformation ( imageid, ")); QStringList fieldNames = imageInformationFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QLatin1String(", ")); query += QString::fromUtf8(" ) VALUES ("); addBoundValuePlaceholders(query, infos.size() + 1); query += QString::fromUtf8(");"); QVariantList boundValues; boundValues << imageID; boundValues << infos; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields))); } void CoreDB::changeItemInformation(qlonglong imageId, const QVariantList& infos, DatabaseFields::ItemInformation fields) { if (fields == DatabaseFields::ItemInformationNone) { return; } QStringList fieldNames = imageInformationFieldList(fields); d->db->execUpsertDBAction(QLatin1String("changeItemInformation"), imageId, fieldNames, infos); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields))); } void CoreDB::addImageMetadata(qlonglong imageID, const QVariantList& infos, DatabaseFields::ImageMetadata fields) { if (fields == DatabaseFields::ImageMetadataNone) { return; } QString query(QString::fromUtf8("REPLACE INTO ImageMetadata ( imageid, ")); QStringList fieldNames = imageMetadataFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QLatin1String(", ")); query += QString::fromUtf8(" ) VALUES ("); addBoundValuePlaceholders(query, infos.size() + 1); query += QString::fromUtf8(");"); QVariantList boundValues; boundValues << imageID << infos; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields))); } void CoreDB::changeImageMetadata(qlonglong imageId, const QVariantList& infos, DatabaseFields::ImageMetadata fields) { if (fields == DatabaseFields::ImageMetadataNone) { return; } QString query(QString::fromUtf8("UPDATE ImageMetadata SET ")); QStringList fieldNames = imageMetadataFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QString::fromUtf8("=?,")); query += QString::fromUtf8("=? WHERE imageid=?;"); QVariantList boundValues; boundValues << infos << imageId; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields))); } void CoreDB::addVideoMetadata(qlonglong imageID, const QVariantList& infos, DatabaseFields::VideoMetadata fields) { if (fields == DatabaseFields::VideoMetadataNone) { return; } QString query(QString::fromUtf8("REPLACE INTO VideoMetadata ( imageid, ")); // need to create this database QStringList fieldNames = videoMetadataFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QLatin1String(", ")); query += QString::fromUtf8(" ) VALUES ("); addBoundValuePlaceholders(query, infos.size() + 1); query += QString::fromUtf8(");"); QVariantList boundValues; boundValues << imageID << infos; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields))); } void CoreDB::changeVideoMetadata(qlonglong imageId, const QVariantList& infos, DatabaseFields::VideoMetadata fields) { if (fields == DatabaseFields::VideoMetadataNone) { return; } QString query(QString::fromUtf8("UPDATE VideoMetadata SET ")); QStringList fieldNames = videoMetadataFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QString::fromUtf8("=?,")); query += QString::fromUtf8("=? WHERE imageid=?;"); QVariantList boundValues; boundValues << infos << imageId; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields))); } void CoreDB::addItemPosition(qlonglong imageID, const QVariantList& infos, DatabaseFields::ItemPositions fields) { if (fields == DatabaseFields::ItemPositionsNone) { return; } QString query(QString::fromUtf8("REPLACE INTO ImagePositions ( imageid, ")); QStringList fieldNames = imagePositionsFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QLatin1String(", ")); query += QString::fromUtf8(" ) VALUES ("); addBoundValuePlaceholders(query, infos.size() + 1); query += QString::fromUtf8(");"); QVariantList boundValues; boundValues << imageID << infos; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields))); } void CoreDB::changeItemPosition(qlonglong imageId, const QVariantList& infos, DatabaseFields::ItemPositions fields) { if (fields == DatabaseFields::ItemPositionsNone) { return; } QString query(QString::fromUtf8("UPDATE ImagePositions SET ")); QStringList fieldNames = imagePositionsFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QString::fromUtf8("=?,")); query += QString::fromUtf8("=? WHERE imageid=?;"); QVariantList boundValues; boundValues << infos << imageId; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(fields))); } void CoreDB::removeItemPosition(qlonglong imageid) { d->db->execSql(QString(QString::fromUtf8("DELETE FROM ImagePositions WHERE imageid=?;")), imageid); d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::ItemPositionsAll))); } void CoreDB::removeItemPositionAltitude(qlonglong imageid) { d->db->execSql(QString(QString::fromUtf8("UPDATE ImagePositions SET altitude=NULL WHERE imageid=?;")), imageid); d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::Altitude))); } QList CoreDB::getItemComments(qlonglong imageID) const { QList list; QList values; d->db->execSql(QString::fromUtf8("SELECT id, type, language, author, date, comment " "FROM ImageComments WHERE imageid=?;"), imageID, &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { CommentInfo info; info.imageId = imageID; info.id = (*it).toInt(); ++it; info.type = (DatabaseComment::Type)(*it).toInt(); ++it; info.language = (*it).toString(); ++it; info.author = (*it).toString(); ++it; info.date = (*it).toDateTime(); ++it; info.comment = (*it).toString(); ++it; list << info; } return list; } int CoreDB::setImageComment(qlonglong imageID, const QString& comment, DatabaseComment::Type type, const QString& language, const QString& author, const QDateTime& date) const { QVariantList boundValues; boundValues << imageID << (int)type << language << author << date << comment; QVariant id; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageComments " "( imageid, type, language, author, date, comment ) " " VALUES (?,?,?,?,?,?);"), boundValues, nullptr, &id); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ItemCommentsAll))); return id.toInt(); } void CoreDB::changeImageComment(int commentId, qlonglong imageID, const QVariantList& infos, DatabaseFields::ItemComments fields) { if (fields == DatabaseFields::ItemCommentsNone) { return; } QString query(QString::fromUtf8("UPDATE ImageComments SET ")); QStringList fieldNames = imageCommentsFieldList(fields); Q_ASSERT(fieldNames.size() == infos.size()); query += fieldNames.join(QString::fromUtf8("=?,")); query += QString::fromUtf8("=? WHERE id=?;"); QVariantList boundValues; boundValues << infos << commentId; d->db->execSql(query, boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(fields))); } void CoreDB::removeImageComment(int commentid, qlonglong imageid) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageComments WHERE id=?;"), commentid); d->db->recordChangeset(ImageChangeset(imageid, DatabaseFields::Set(DatabaseFields::ItemCommentsAll))); } QString CoreDB::getImageProperty(qlonglong imageID, const QString& property) const { QList values; d->db->execSql(QString::fromUtf8("SELECT value FROM ImageProperties " "WHERE imageid=? and property=?;"), imageID, property, &values); if (values.isEmpty()) { return QString(); } return values.first().toString(); } void CoreDB::setImageProperty(qlonglong imageID, const QString& property, const QString& value) { d->db->execSql(QString::fromUtf8("REPLACE INTO ImageProperties " "(imageid, property, value) " " VALUES(?, ?, ?);"), imageID, property, value); } void CoreDB::removeImageProperty(qlonglong imageID, const QString& property) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageProperties WHERE imageid=? AND property=?;"), imageID, property); } void CoreDB::removeImagePropertyByName(const QString& property) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageProperties WHERE property=?;"), property); } QList CoreDB::getItemCopyright(qlonglong imageID, const QString& property) const { QList list; QList values; if (property.isNull()) { d->db->execSql(QString::fromUtf8("SELECT property, value, extraValue FROM ImageCopyright " "WHERE imageid=?;"), imageID, &values); } else { d->db->execSql(QString::fromUtf8("SELECT property, value, extraValue FROM ImageCopyright " "WHERE imageid=? and property=?;"), imageID, property, &values); } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { CopyrightInfo info; info.id = imageID; info.property = (*it).toString(); ++it; info.value = (*it).toString(); ++it; info.extraValue = (*it).toString(); ++it; list << info; } return list; } void CoreDB::setItemCopyrightProperty(qlonglong imageID, const QString& property, const QString& value, const QString& extraValue, CopyrightPropertyUnique uniqueness) { if (uniqueness == PropertyUnique) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=? AND property=?;"), imageID, property); } else if (uniqueness == PropertyExtraValueUnique) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=? AND property=? AND extraValue=?;"), imageID, property, extraValue); } d->db->execSql(QString::fromUtf8("REPLACE INTO ImageCopyright " "(imageid, property, value, extraValue) " " VALUES(?, ?, ?, ?);"), imageID, property, value, extraValue); } void CoreDB::removeItemCopyrightProperties(qlonglong imageID, const QString& property, const QString& extraValue, const QString& value) { int removeBy = 0; if (!property.isNull()) { ++removeBy; } if (!extraValue.isNull()) { ++removeBy; } if (!value.isNull()) { ++removeBy; } switch (removeBy) { case 0: d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=?;"), imageID); break; case 1: d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=? AND property=?;"), imageID, property); break; case 2: d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=? AND property=? AND extraValue=?;"), imageID, property, extraValue); break; case 3: d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright " "WHERE imageid=? AND property=? AND extraValue=? AND value=?;"), imageID, property, extraValue, value); break; } } QList CoreDB::findByNameAndCreationDate(const QString& fileName, const QDateTime& creationDate) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "LEFT JOIN ImageInformation ON id=imageid " " WHERE name=? AND creationDate=? AND status<3;"), fileName, creationDate, &values); QList ids; foreach (const QVariant& var, values) { ids << var.toLongLong(); } return ids; } bool CoreDB::hasImageHistory(qlonglong imageId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT history FROM ImageHistory WHERE imageid=?;"), imageId, &values); return !values.isEmpty(); } ImageHistoryEntry CoreDB::getItemHistory(qlonglong imageId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT uuid, history FROM ImageHistory WHERE imageid=?;"), imageId, &values); ImageHistoryEntry entry; entry.imageId = imageId; if (values.count() != 2) { return entry; } QList::const_iterator it = values.constBegin(); entry.uuid = (*it).toString(); ++it; entry.history = (*it).toString(); ++it; return entry; } QList CoreDB::getItemsForUuid(const QString& uuid) const { QList values; d->db->execSql(QString::fromUtf8("SELECT imageid FROM ImageHistory " "INNER JOIN Images ON imageid=id " " WHERE uuid=? AND status<3;"), uuid, &values); QList imageIds; if (values.isEmpty()) { return imageIds; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { imageIds << (*it).toInt(); } return imageIds; } QString CoreDB::getImageUuid(qlonglong imageId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT uuid FROM ImageHistory WHERE imageid=?;"), imageId, &values); if (values.isEmpty()) { return QString(); } QString uuid = values.first().toString(); if (uuid.isEmpty()) { return QString(); } return uuid; } void CoreDB::setItemHistory(qlonglong imageId, const QString& history) { d->db->execUpsertDBAction(QLatin1String("changeImageHistory"), imageId, QStringList() << QLatin1String("history"), QVariantList() << history); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::ImageHistory))); } void CoreDB::setImageUuid(qlonglong imageId, const QString& uuid) { d->db->execUpsertDBAction(QLatin1String("changeImageHistory"), imageId, QStringList() << QLatin1String("uuid"), QVariantList() << uuid); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::ImageUUID))); } void CoreDB::addImageRelation(qlonglong subjectId, qlonglong objectId, DatabaseRelation::Type type) { d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations (subject, object, type) " "VALUES (?, ?, ?);"), subjectId, objectId, type); d->db->recordChangeset(ImageChangeset(QList() << subjectId << objectId, DatabaseFields::Set(DatabaseFields::ImageRelations))); } void CoreDB::addImageRelations(const QList& subjectIds, const QList& objectIds, DatabaseRelation::Type type) { DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("REPLACE INTO ImageRelations (subject, object, type) " "VALUES (?, ?, ?);")); QVariantList subjects, objects, types; for (int i = 0 ; i < subjectIds.size() ; ++i) { subjects << subjectIds.at(i); objects << objectIds.at(i); types << type; } query.addBindValue(subjects); query.addBindValue(objects); query.addBindValue(types); d->db->execBatch(query); d->db->recordChangeset(ImageChangeset(subjectIds + objectIds, DatabaseFields::Set(DatabaseFields::ImageRelations))); } void CoreDB::addImageRelation(const ImageRelation& relation) { addImageRelation(relation.subjectId, relation.objectId, relation.type); } void CoreDB::removeImageRelation(qlonglong subjectId, qlonglong objectId, DatabaseRelation::Type type) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE subject=? AND object=? AND type=?;"), subjectId, objectId, type); d->db->recordChangeset(ImageChangeset(QList() << subjectId << objectId, DatabaseFields::Set(DatabaseFields::ImageRelations))); } void CoreDB::removeImageRelation(const ImageRelation& relation) { removeImageRelation(relation.subjectId, relation.objectId, relation.type); } QList CoreDB::removeAllImageRelationsTo(qlonglong objectId, DatabaseRelation::Type type) const { QList affected = getImagesRelatingTo(objectId, type); if (affected.isEmpty()) { return affected; } d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE object=? AND type=?;"), objectId, type); d->db->recordChangeset(ImageChangeset(QList() << affected << objectId, DatabaseFields::Set(DatabaseFields::ImageRelations))); return affected; } QList CoreDB::removeAllImageRelationsFrom(qlonglong subjectId, DatabaseRelation::Type type) const { QList affected = getImagesRelatedFrom(subjectId, type); if (affected.isEmpty()) { return affected; } d->db->execSql(QString::fromUtf8("DELETE FROM ImageRelations WHERE subject=? AND type=?;"), subjectId, type); d->db->recordChangeset(ImageChangeset(QList() << affected << subjectId, DatabaseFields::Set(DatabaseFields::ImageRelations))); return affected; } QList CoreDB::getImagesRelatedFrom(qlonglong subjectId, DatabaseRelation::Type type) const { return getRelatedImages(subjectId, true, type, false); } QVector > CoreDB::getImagesRelatedFrom(QList subjectIds, DatabaseRelation::Type type) const { return getRelatedImages(subjectIds, true, type, false); } bool CoreDB::hasImagesRelatedFrom(qlonglong subjectId, DatabaseRelation::Type type) const { // returns 0 or 1 item in list return !getRelatedImages(subjectId, true, type, true).isEmpty(); } QList CoreDB::getImagesRelatingTo(qlonglong objectId, DatabaseRelation::Type type) const { return getRelatedImages(objectId, false, type, false); } QVector > CoreDB::getImagesRelatingTo(QList objectIds, DatabaseRelation::Type type) const { return getRelatedImages(objectIds, false, type, false); } bool CoreDB::hasImagesRelatingTo(qlonglong objectId, DatabaseRelation::Type type) const { // returns 0 or 1 item in list return !getRelatedImages(objectId, false, type, true).isEmpty(); } QList CoreDB::getRelatedImages(qlonglong id, bool fromOrTo, DatabaseRelation::Type type, bool boolean) const { QString sql = d->constructRelatedImagesSQL(fromOrTo, type, boolean); DbEngineSqlQuery query = d->db->prepareQuery(sql); return d->execRelatedImagesQuery(query, id, type); } QVector > CoreDB::getRelatedImages(QList ids, bool fromOrTo, DatabaseRelation::Type type, bool boolean) const { if (ids.isEmpty()) { return QVector >(); } QVector > result(ids.size()); QString sql = d->constructRelatedImagesSQL(fromOrTo, type, boolean); DbEngineSqlQuery query = d->db->prepareQuery(sql); for (int i = 0 ; i < ids.size() ; ++i) { result[i] = d->execRelatedImagesQuery(query, ids[i], type); } return result; } QList > CoreDB::getRelationCloud(qlonglong imageId, DatabaseRelation::Type type) const { QSet todo, done; QSet > pairs; todo << imageId; QString sql = QString::fromUtf8("SELECT subject, object FROM ImageRelations " "INNER JOIN Images AS SubjectImages " "ON ImageRelations.subject=SubjectImages.id " " INNER JOIN Images AS ObjectImages " " ON ImageRelations.object=ObjectImages.id " " WHERE (subject=? OR object=?) %1 " " AND SubjectImages.status<3 " " AND ObjectImages.status<3;"); if (type == DatabaseRelation::UndefinedType) { sql = sql.arg(QString()); } else { sql = sql.arg(QString::fromUtf8("AND type=?")); } DbEngineSqlQuery query = d->db->prepareQuery(sql); QList values; qlonglong subject, object; while (!todo.isEmpty()) { qlonglong id = *todo.begin(); todo.erase(todo.begin()); done << id; if (type == DatabaseRelation::UndefinedType) { d->db->execSql(query, id, id, &values); } else { d->db->execSql(query, id, id, type, &values); } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { subject = (*it).toLongLong(); ++it; object = (*it).toLongLong(); ++it; pairs << qMakePair(subject, object); if (!done.contains(subject)) { todo << subject; } if (!done.contains(object)) { todo << object; } } } return pairs.toList(); } QList CoreDB::getOneRelatedImageEach(const QList& ids, DatabaseRelation::Type type) const { QString sql = QString::fromUtf8("SELECT subject, object FROM ImageRelations " "INNER JOIN Images AS SubjectImages " "ON ImageRelations.subject=SubjectImages.id " " INNER JOIN Images AS ObjectImages " " ON ImageRelations.object=ObjectImages.id " " WHERE ( (subject=? AND ObjectImages.status<3) " " OR (object=? AND SubjectImages.status<3) ) " " %1 LIMIT 1;"); if (type == DatabaseRelation::UndefinedType) { sql = sql.arg(QString()); } else { sql = sql.arg(QString::fromUtf8("AND type=?")); } DbEngineSqlQuery query = d->db->prepareQuery(sql); QSet result; QList values; foreach (const qlonglong& id, ids) { if (type == DatabaseRelation::UndefinedType) { d->db->execSql(query, id, id, &values); } else { d->db->execSql(query, id, id, type, &values); } if (values.size() != 2) { continue; } // one of subject and object is the given id, the other our result if (values.first() != id) { result << values.first().toLongLong(); } else { result << values.last().toLongLong(); } } return result.toList(); } QList CoreDB::getRelatedImagesToByType(DatabaseRelation::Type type) const { QList values; d->db->execSql(QString::fromUtf8("SELECT object FROM ImageRelations " "INNER JOIN Images AS SubjectImages " "ON ImageRelations.subject=SubjectImages.id " " INNER JOIN Images AS ObjectImages " " ON ImageRelations.object=ObjectImages.id " " WHERE type=? " " AND SubjectImages.status<3 " " AND ObjectImages.status<3;"), (int)type, &values); QList imageIds; if (values.isEmpty()) { return imageIds; } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { imageIds << (*it).toLongLong(); } return imageIds; } QStringList CoreDB::getItemsURLsWithTag(int tagId) const { QList values; QList boundValues; QString query(QString::fromUtf8("SELECT DISTINCT Albums.albumRoot, Albums.relativePath, Images.name FROM Images " "LEFT JOIN ImageTags ON Images.id=ImageTags.imageid " "INNER JOIN Albums ON Albums.id=Images.album " " WHERE Images.status=1 AND Images.category=1 AND ")); if (tagId == TagsCache::instance()->tagForPickLabel(NoPickLabel) || tagId == TagsCache::instance()->tagForColorLabel(NoColorLabel)) { query += QString::fromUtf8("( ImageTags.tagid=? OR ImageTags.tagid " "NOT BETWEEN ? AND ? OR ImageTags.tagid IS NULL );"); boundValues << tagId; if (tagId == TagsCache::instance()->tagForPickLabel(NoPickLabel)) { boundValues << TagsCache::instance()->tagForPickLabel(FirstPickLabel); boundValues << TagsCache::instance()->tagForPickLabel(LastPickLabel); } else { boundValues << TagsCache::instance()->tagForColorLabel(FirstColorLabel); boundValues << TagsCache::instance()->tagForColorLabel(LastColorLabel); } } else { query += QString::fromUtf8("ImageTags.tagid=?;"); boundValues << tagId; } d->db->execSql(query, boundValues, &values); QStringList urls; QString albumRootPath, relativePath, name; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt()); ++it; relativePath = (*it).toString(); ++it; name = (*it).toString(); ++it; if (relativePath == QLatin1String("/")) { urls << albumRootPath + relativePath + name; } else { urls << albumRootPath + relativePath + QLatin1Char('/') + name; } } return urls; } QStringList CoreDB::getDirtyOrMissingFaceImageUrls() const { QList values; d->db->execSql(QString::fromUtf8("SELECT Albums.albumRoot, Albums.relativePath, Images.name FROM Images " "LEFT JOIN ImageScannedMatrix ON Images.id=ImageScannedMatrix.imageid " "INNER JOIN Albums ON Albums.id=Images.album " " WHERE Images.status=1 AND Images.category=1 AND " " ( ImageScannedMatrix.imageid IS NULL " " OR Images.modificationDate != ImageScannedMatrix.modificationDate " " OR Images.uniqueHash != ImageScannedMatrix.uniqueHash );"), &values); QStringList urls; QString albumRootPath, relativePath, name; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt()); ++it; relativePath = (*it).toString(); ++it; name = (*it).toString(); ++it; if (relativePath == QLatin1String("/")) { urls << albumRootPath + relativePath + name; } else { urls << albumRootPath + relativePath + QLatin1Char('/') + name; } } return urls; } QList CoreDB::getIdenticalFiles(qlonglong id) const { if (!id) { return QList(); } QList values; // retrieve unique hash and file size d->db->execSql(QString::fromUtf8("SELECT uniqueHash, fileSize FROM Images WHERE id=?;"), id, &values); if (values.isEmpty()) { return QList(); } QString uniqueHash = values.at(0).toString(); qlonglong fileSize = values.at(1).toLongLong(); return getIdenticalFiles(uniqueHash, fileSize, id); } QList CoreDB::getIdenticalFiles(const QString& uniqueHash, qlonglong fileSize, qlonglong sourceId) const { // enforce validity if (uniqueHash.isEmpty() || fileSize <= 0) { return QList(); } QList values; // find items with same fingerprint d->db->execSql(QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize " "FROM Images WHERE fileSize=? AND uniqueHash=? AND album IS NOT NULL;"), fileSize, uniqueHash, &values); QList list; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { ItemScanInfo info; info.id = (*it).toLongLong(); ++it; info.albumID = (*it).toInt(); ++it; info.itemName = (*it).toString(); ++it; info.status = (DatabaseItem::Status)(*it).toInt(); ++it; info.category = (DatabaseItem::Category)(*it).toInt(); ++it; info.modificationDate = (*it).toDateTime(); ++it; info.fileSize = (*it).toLongLong(); ++it; // exclude one source id from list if (sourceId == info.id) { continue; } // same for all here, per definition info.uniqueHash = uniqueHash; list << info; } return list; } QStringList CoreDB::imagesFieldList(DatabaseFields::Images fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::Album) { list << QLatin1String("album"); } if (fields & DatabaseFields::Name) { list << QLatin1String("name"); } if (fields & DatabaseFields::Status) { list << QLatin1String("status"); } if (fields & DatabaseFields::Category) { list << QLatin1String("category"); } if (fields & DatabaseFields::ModificationDate) { list << QLatin1String("modificationDate"); } if (fields & DatabaseFields::FileSize) { list << QLatin1String("fileSize"); } if (fields & DatabaseFields::UniqueHash) { list << QLatin1String("uniqueHash"); } if (fields & DatabaseFields::ManualOrder) { list << QLatin1String("manualOrder"); } return list; } QStringList CoreDB::imageInformationFieldList(DatabaseFields::ItemInformation fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::Rating) { list << QLatin1String("rating"); } if (fields & DatabaseFields::CreationDate) { list << QLatin1String("creationDate"); } if (fields & DatabaseFields::DigitizationDate) { list << QLatin1String("digitizationDate"); } if (fields & DatabaseFields::Orientation) { list << QLatin1String("orientation"); } if (fields & DatabaseFields::Width) { list << QLatin1String("width"); } if (fields & DatabaseFields::Height) { list << QLatin1String("height"); } if (fields & DatabaseFields::Format) { list << QLatin1String("format"); } if (fields & DatabaseFields::ColorDepth) { list << QLatin1String("colorDepth"); } if (fields & DatabaseFields::ColorModel) { list << QLatin1String("colorModel"); } return list; } QStringList CoreDB::videoMetadataFieldList(DatabaseFields::VideoMetadata fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::AspectRatio) { list << QLatin1String("aspectRatio"); } if (fields & DatabaseFields::AudioBitRate) { list << QLatin1String("audioBitRate"); } if (fields & DatabaseFields::AudioChannelType) { list << QLatin1String("audioChannelType"); } if (fields & DatabaseFields::AudioCodec) { list << QLatin1String("audioCompressor"); } if (fields & DatabaseFields::Duration) { list << QLatin1String("duration"); } if (fields & DatabaseFields::FrameRate) { list << QLatin1String("frameRate"); } if (fields & DatabaseFields::VideoCodec) { list << QLatin1String("videoCodec"); } return list; } QStringList CoreDB::imageMetadataFieldList(DatabaseFields::ImageMetadata fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::Make) { list << QLatin1String("make"); } if (fields & DatabaseFields::Model) { list << QLatin1String("model"); } if (fields & DatabaseFields::Lens) { list << QLatin1String("lens"); } if (fields & DatabaseFields::Aperture) { list << QLatin1String("aperture"); } if (fields & DatabaseFields::FocalLength) { list << QLatin1String("focalLength"); } if (fields & DatabaseFields::FocalLength35) { list << QLatin1String("focalLength35"); } if (fields & DatabaseFields::ExposureTime) { list << QLatin1String("exposureTime"); } if (fields & DatabaseFields::ExposureProgram) { list << QLatin1String("exposureProgram"); } if (fields & DatabaseFields::ExposureMode) { list << QLatin1String("exposureMode"); } if (fields & DatabaseFields::Sensitivity) { list << QLatin1String("sensitivity"); } if (fields & DatabaseFields::FlashMode) { list << QLatin1String("flash"); } if (fields & DatabaseFields::WhiteBalance) { list << QLatin1String("whiteBalance"); } if (fields & DatabaseFields::WhiteBalanceColorTemperature) { list << QLatin1String("whiteBalanceColorTemperature"); } if (fields & DatabaseFields::MeteringMode) { list << QLatin1String("meteringMode"); } if (fields & DatabaseFields::SubjectDistance) { list << QLatin1String("subjectDistance"); } if (fields & DatabaseFields::SubjectDistanceCategory) { list << QLatin1String("subjectDistanceCategory"); } return list; } QStringList CoreDB::imagePositionsFieldList(DatabaseFields::ItemPositions fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::Latitude) { list << QLatin1String("latitude"); } if (fields & DatabaseFields::LatitudeNumber) { list << QLatin1String("latitudeNumber"); } if (fields & DatabaseFields::Longitude) { list << QLatin1String("longitude"); } if (fields & DatabaseFields::LongitudeNumber) { list << QLatin1String("longitudeNumber"); } if (fields & DatabaseFields::Altitude) { list << QLatin1String("altitude"); } if (fields & DatabaseFields::PositionOrientation) { list << QLatin1String("orientation"); } if (fields & DatabaseFields::PositionTilt) { list << QLatin1String("tilt"); } if (fields & DatabaseFields::PositionRoll) { list << QLatin1String("roll"); } if (fields & DatabaseFields::PositionAccuracy) { list << QLatin1String("accuracy"); } if (fields & DatabaseFields::PositionDescription) { list << QLatin1String("description"); } return list; } QStringList CoreDB::imageCommentsFieldList(DatabaseFields::ItemComments fields) { // adds no spaces at beginning or end QStringList list; if (fields & DatabaseFields::CommentType) { list << QLatin1String("type"); } if (fields & DatabaseFields::CommentLanguage) { list << QLatin1String("language"); } if (fields & DatabaseFields::CommentAuthor) { list << QLatin1String("author"); } if (fields & DatabaseFields::CommentDate) { list << QLatin1String("date"); } if (fields & DatabaseFields::Comment) { list << QLatin1String("comment"); } return list; } void CoreDB::addBoundValuePlaceholders(QString& query, int count) { // adds no spaces at beginning or end QString questionMarks; questionMarks.reserve(count * 2); QString questionMark(QString::fromUtf8("?,")); for (int i = 0 ; i < count ; ++i) { questionMarks += questionMark; } // remove last ',' questionMarks.chop(1); query += questionMarks; } int CoreDB::findInDownloadHistory(const QString& identifier, const QString& name, qlonglong fileSize, const QDateTime& date) const { QList values; QVariantList boundValues; boundValues << identifier << name << fileSize << date.addSecs(-2) << date.addSecs(2); d->db->execSql(QString::fromUtf8("SELECT id FROM DownloadHistory " " WHERE identifier=? AND filename=? " " AND filesize=? AND (filedate>? " " AND filedatedb->execSql(QString::fromUtf8("REPLACE INTO DownloadHistory " "(identifier, filename, filesize, filedate) " " VALUES (?,?,?,?);"), identifier, name, fileSize, date, nullptr, &id); return id.toInt(); } void CoreDB::addItemTag(qlonglong imageID, int tagID) { d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTags (imageid, tagid) " "VALUES(?, ?);"), imageID, tagID); d->db->recordChangeset(ImageTagChangeset(imageID, tagID, ImageTagChangeset::Added)); //don't save pick or color tags if (TagsCache::instance()->isInternalTag(tagID)) return; //move current tag to front d->recentlyAssignedTags.removeAll(tagID); d->recentlyAssignedTags.prepend(tagID); if (d->recentlyAssignedTags.size() > 10) { d->recentlyAssignedTags.removeLast(); } } void CoreDB::addItemTag(int albumID, const QString& name, int tagID) { // easier because of attributes watch return addItemTag(getImageId(albumID, name), tagID); } void CoreDB::addTagsToItems(QList imageIDs, QList tagIDs) { if (imageIDs.isEmpty() || tagIDs.isEmpty()) { return; } DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("REPLACE INTO ImageTags (imageid, tagid) " "VALUES(?, ?);")); QVariantList images; QVariantList tags; foreach (const qlonglong& imageid, imageIDs) { foreach (int tagid, tagIDs) { images << imageid; tags << tagid; } } query.addBindValue(images); query.addBindValue(tags); d->db->execBatch(query); d->db->recordChangeset(ImageTagChangeset(imageIDs, tagIDs, ImageTagChangeset::Added)); } QList CoreDB::getRecentlyAssignedTags() const { return d->recentlyAssignedTags; } void CoreDB::removeItemTag(qlonglong imageID, int tagID) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags " "WHERE imageID=? AND tagid=?;"), imageID, tagID); d->db->recordChangeset(ImageTagChangeset(imageID, tagID, ImageTagChangeset::Removed)); } void CoreDB::removeItemAllTags(qlonglong imageID, const QList& currentTagIds) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags " "WHERE imageID=?;"), imageID); d->db->recordChangeset(ImageTagChangeset(imageID, currentTagIds, ImageTagChangeset::RemovedAll)); } void CoreDB::removeTagsFromItems(QList imageIDs, const QList& tagIDs) { if (imageIDs.isEmpty() || tagIDs.isEmpty()) { return; } DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("DELETE FROM ImageTags WHERE imageID=? AND tagid=?;")); QVariantList images; QVariantList tags; foreach (const qlonglong& imageid, imageIDs) { foreach (int tagid, tagIDs) { images << imageid; tags << tagid; } } query.addBindValue(images); query.addBindValue(tags); d->db->execBatch(query); d->db->recordChangeset(ImageTagChangeset(imageIDs, tagIDs, ImageTagChangeset::Removed)); } QStringList CoreDB::getItemNamesInAlbum(int albumID, bool recursive) const { QList values; if (recursive) { int rootId = getAlbumRootId(albumID); QString path = getAlbumRelativePath(albumID); d->db->execSql(QString::fromUtf8("SELECT Images.name FROM Images WHERE Images.album IN " " (SELECT DISTINCT id FROM Albums " " WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?));"), rootId, path, path == QLatin1String("/") ? QLatin1String("/%") : QString(path + QLatin1String("/%")), &values); } else { d->db->execSql(QString::fromUtf8("SELECT name FROM Images " "WHERE album=?;"), albumID, &values); } QStringList names; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { names << it->toString(); } return names; } qlonglong CoreDB::getItemFromAlbum(int albumID, const QString& fileName) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images " "WHERE album=? AND name=?;"), albumID, fileName, &values); if (values.isEmpty()) { return -1; } return values.first().toLongLong(); } QList CoreDB::getAllCreationDates() const { QList values; d->db->execSql(QString::fromUtf8("SELECT creationDate FROM ImageInformation " "INNER JOIN Images ON Images.id=ImageInformation.imageid " " WHERE Images.status=1;"), &values); QList list; foreach (const QVariant& value, values) { if (!value.isNull()) { list << value.toDateTime(); } } return list; } QMap CoreDB::getAllCreationDatesAndNumberOfImages() const { QList values; d->db->execSql(QString::fromUtf8("SELECT creationDate FROM ImageInformation " "INNER JOIN Images ON Images.id=ImageInformation.imageid " " WHERE Images.status=1;"), &values); QMap datesStatMap; foreach (const QVariant& value, values) { if (!value.isNull()) { QDateTime dateTime = value.toDateTime(); if (!dateTime.isValid()) { continue; } QMap::iterator it2 = datesStatMap.find(dateTime); if (it2 == datesStatMap.end()) { datesStatMap.insert(dateTime, 1); } else { it2.value()++; } } } return datesStatMap; } int CoreDB::getNumberOfItemsInAlbum(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT COUNT(id) FROM Images WHERE album=?;"), albumID, &values); if (values.isEmpty()) { return 0; } return values.first().toInt(); } QMap CoreDB::getNumberOfImagesInAlbums() const { QList values, allAbumIDs; QMap albumsStatMap; int albumID, count; // initialize allAbumIDs with all existing albums from db to prevent // wrong album image counters d->db->execSql(QString::fromUtf8("SELECT id from Albums"), &allAbumIDs); for (QList::const_iterator it = allAbumIDs.constBegin() ; it != allAbumIDs.constEnd() ; ++it) { albumID = (*it).toInt(); albumsStatMap.insert(albumID, 0); } d->db->execSql(QString::fromUtf8("SELECT album, COUNT(*) FROM Images " "WHERE Images.status=1 GROUP BY album;"), &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { albumID = (*it).toInt(); ++it; count = (*it).toInt(); ++it; albumsStatMap[albumID] = count; } return albumsStatMap; } QMap CoreDB::getNumberOfImagesInTags() const { QList values, allTagIDs; QMap tagsStatMap; int tagID, count; // initialize allTagIDs with all existing tags from db to prevent // wrong tag counters d->db->execSql(QString::fromUtf8("SELECT id from Tags"), &allTagIDs); for (QList::const_iterator it = allTagIDs.constBegin() ; it != allTagIDs.constEnd() ; ++it) { tagID = (*it).toInt(); tagsStatMap.insert(tagID, 0); } d->db->execSql(QString::fromUtf8("SELECT tagid, COUNT(*) FROM ImageTags " "LEFT JOIN Images ON Images.id=ImageTags.imageid " " WHERE Images.status=1 GROUP BY tagid;"), &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { tagID = (*it).toInt(); ++it; count = (*it).toInt(); ++it; tagsStatMap[tagID] = count; } return tagsStatMap; } QMap CoreDB::getNumberOfImagesInTagProperties(const QString& property) const { QList values; QMap tagsStatMap; int tagID, count; d->db->execSql(QString::fromUtf8("SELECT tagid, COUNT(*) FROM ImageTagProperties " "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid " " WHERE ImageTagProperties.property=? AND Images.status=1 " " GROUP BY tagid;"), property, &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { tagID = (*it).toInt(); ++it; count = (*it).toInt(); ++it; tagsStatMap[tagID] = count; } return tagsStatMap; } int CoreDB::getNumberOfImagesInTagProperties(int tagId, const QString& property) const { QList values; d->db->execSql(QString::fromUtf8("SELECT COUNT(*) FROM ImageTagProperties " "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid " " WHERE ImageTagProperties.property=? AND Images.status=1 " " AND ImageTagProperties.tagid=? ;"), property, tagId, &values); if (values.isEmpty()) { return 0; } return values.first().toInt(); } QList CoreDB::getImagesWithImageTagProperty(int tagId, const QString& property) const { QList values; QList imageIds; d->db->execSql(QString::fromUtf8("SELECT DISTINCT Images.id FROM ImageTagProperties " "LEFT JOIN Images ON Images.id=ImageTagProperties.imageid " " WHERE ImageTagProperties.property=? AND Images.status=1 " " AND ImageTagProperties.tagid=? ;"), property, tagId, &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { imageIds.append((*it).toInt()); } return imageIds; } QMap CoreDB::getFormatStatistics() const { return getFormatStatistics(DatabaseItem::UndefinedCategory); } QMap CoreDB::getFormatStatistics(DatabaseItem::Category category) const { QMap map; QString queryString = QString::fromUtf8("SELECT COUNT(*), II.format " "FROM ImageInformation AS II " "INNER JOIN Images ON II.imageid=Images.id " " WHERE Images.status=1 "); if (category != DatabaseItem::UndefinedCategory) { queryString.append(QString::fromUtf8("AND Images.category=%1").arg(category)); } queryString.append(QString::fromUtf8(" GROUP BY II.format;")); qCDebug(DIGIKAM_DATABASE_LOG) << queryString; DbEngineSqlQuery query = d->db->prepareQuery(queryString); if (d->db->exec(query)) { while (query.next()) { QString quantity = query.value(0).toString(); QString format = query.value(1).toString(); if (format.isEmpty()) { continue; } map[format] = quantity.isEmpty() ? 0 : quantity.toInt(); } } return map; } QStringList CoreDB::getListFromImageMetadata(DatabaseFields::ImageMetadata field) const { QStringList list; QList values; QStringList fieldName = imageMetadataFieldList(field); if (fieldName.count() != 1) { return list; } QString sql = QString::fromUtf8("SELECT DISTINCT %1 FROM ImageMetadata " "INNER JOIN Images ON imageid=Images.id;"); sql = sql.arg(fieldName.first()); d->db->execSql(sql, &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { if (!it->isNull()) { list << it->toString(); } } return list; } int CoreDB::getAlbumForPath(int albumRootId, const QString& folder, bool create) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Albums WHERE albumRoot=? AND relativePath=?;"), albumRootId, folder, &values); int albumID = -1; if (values.isEmpty()) { if (create) { albumID = addAlbum(albumRootId, folder, QString(), QDate::currentDate(), QString()); } } else { albumID = values.first().toInt(); } return albumID; } QList CoreDB::getAlbumAndSubalbumsForPath(int albumRootId, const QString& relativePath) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id, relativePath FROM Albums " "WHERE albumRoot=? AND (relativePath=? OR relativePath LIKE ?);"), albumRootId, relativePath, (relativePath == QLatin1String("/") ? QLatin1String("/%") : QString(relativePath + QLatin1String("/%"))), &values); QList albumIds; int id; QString albumRelativePath; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { id = (*it).toInt(); ++it; - QString albumRelativePath = (*it).toString(); + albumRelativePath = (*it).toString(); ++it; // bug #223050: The LIKE operator is case insensitive if (albumRelativePath.startsWith(relativePath)) { albumIds << id; } } return albumIds; } QList CoreDB::getAlbumsOnAlbumRoot(int albumRootId) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Albums WHERE albumRoot=?;"), albumRootId, &values); QList albumIds; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { albumIds << (*it).toInt(); } return albumIds; } qlonglong CoreDB::addItem(int albumID, const QString& name, DatabaseItem::Status status, DatabaseItem::Category category, const QDateTime& modificationDate, qlonglong fileSize, const QString& uniqueHash) const { QVariantList boundValues; boundValues << albumID << name << (int)status << (int)category << modificationDate << fileSize << uniqueHash; QVariant id; d->db->execSql(QString::fromUtf8("REPLACE INTO Images " "( album, name, status, category, modificationDate, fileSize, uniqueHash ) " " VALUES (?,?,?,?,?,?,?);"), boundValues, nullptr, &id); if (id.isNull()) { return -1; } d->db->recordChangeset(ImageChangeset(id.toLongLong(), DatabaseFields::Set(DatabaseFields::ImagesAll))); d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), albumID, CollectionImageChangeset::Added)); return id.toLongLong(); } void CoreDB::updateItem(qlonglong imageID, DatabaseItem::Category category, const QDateTime& modificationDate, qlonglong fileSize, const QString& uniqueHash) { QVariantList boundValues; boundValues << (int)category << modificationDate << fileSize << uniqueHash << imageID; d->db->execSql(QString::fromUtf8("UPDATE Images SET category=?, modificationDate=?, fileSize=?, uniqueHash=? " "WHERE id=?;"), boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::Category | DatabaseFields::ModificationDate | DatabaseFields::FileSize | DatabaseFields::UniqueHash))); } void CoreDB::setItemStatus(qlonglong imageID, DatabaseItem::Status status) { QVariantList boundValues; boundValues << (int)status << imageID; d->db->execSql(QString::fromUtf8("UPDATE Images SET status=? WHERE id=?;"), boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::Status))); } void CoreDB::setItemAlbum(qlonglong imageID, qlonglong album) { QVariantList boundValues; boundValues << album << imageID; d->db->execSql(QString::fromUtf8("UPDATE Images SET album=? WHERE id=?;"), boundValues); // record that the image was assigned a new album d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::Album))); // also record that the collection was changed by adding an image to an album. d->db->recordChangeset(CollectionImageChangeset(imageID, album, CollectionImageChangeset::Added)); } void CoreDB::setItemManualOrder(qlonglong imageID, qlonglong value) { QVariantList boundValues; boundValues << value << imageID; d->db->execSql(QString::fromUtf8("UPDATE Images SET manualOrder=? WHERE id=?;"), boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ManualOrder))); } void CoreDB::setItemModificationDate(qlonglong imageID, const QDateTime& modificationDate) { QVariantList boundValues; boundValues << modificationDate << imageID; d->db->execSql(QString::fromUtf8("UPDATE Images SET modificationDate=? WHERE id=?;"), boundValues); d->db->recordChangeset(ImageChangeset(imageID, DatabaseFields::Set(DatabaseFields::ModificationDate))); } void CoreDB::renameItem(qlonglong imageID, const QString& newName) { d->db->execSql(QString::fromUtf8("UPDATE Images SET name=? WHERE id=?;"), newName, imageID); } int CoreDB::getItemAlbum(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT album FROM Images WHERE id=?;"), imageID, &values); if (values.isEmpty()) { return 1; } return values.first().toInt(); } QString CoreDB::getItemName(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT name FROM Images WHERE id=?;"), imageID, &values); if (values.isEmpty()) { return QString(); } return values.first().toString(); } QStringList CoreDB::getItemURLsInAlbum(int albumID, ItemSortOrder sortOrder) const { QList values; int albumRootId = getAlbumRootId(albumID); if (albumRootId == -1) { return QStringList(); } QString albumRootPath = CollectionManager::instance()->albumRootPath(albumRootId); if (albumRootPath.isNull()) { return QStringList(); } QMap bindingMap; bindingMap.insert(QString::fromUtf8(":albumID"), albumID); switch (sortOrder) { case ByItemName: d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemName")), bindingMap, &values); break; case ByItemPath: // Don't collate on the path - this is to maintain the same behavior // that happens when sort order is "By Path" d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemPath")), bindingMap, &values); break; case ByItemDate: d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemDate")), bindingMap, &values); break; case ByItemRating: d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumByItemRating")), bindingMap, &values); break; case NoItemSorting: default: d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemURLsInAlbumNoItemSorting")), bindingMap, &values); break; } QStringList urls; QString relativePath, name; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { relativePath = (*it).toString(); ++it; name = (*it).toString(); ++it; if (relativePath == QLatin1String("/")) { urls << albumRootPath + relativePath + name; } else { urls << albumRootPath + relativePath + QLatin1Char('/') + name; } } return urls; } QList CoreDB::getItemIDsInAlbum(int albumID) const { QList itemIDs; QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images WHERE album=?;"), albumID, &values); for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { itemIDs << (*it).toLongLong(); } return itemIDs; } QMap CoreDB::getItemIDsAndURLsInAlbum(int albumID) const { int albumRootId = getAlbumRootId(albumID); if (albumRootId == -1) { return QMap(); } QString albumRootPath = CollectionManager::instance()->albumRootPath(albumRootId); if (albumRootPath.isNull()) { return QMap(); } QMap itemsMap; QList values; d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.relativePath, Images.name " "FROM Images " " INNER JOIN Albums ON Albums.id=Images.album " " WHERE Albums.id=?;"), albumID, &values); QString path; qlonglong id; QString relativePath, name; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { id = (*it).toLongLong(); ++it; relativePath = (*it).toString(); ++it; name = (*it).toString(); ++it; if (relativePath == QLatin1String("/")) { path = albumRootPath + relativePath + name; } else { path = albumRootPath + relativePath + QLatin1Char('/') + name; } itemsMap.insert(id, path); }; return itemsMap; } QList CoreDB::getAllItems() const { QList values; d->db->execSql(QString::fromUtf8("SELECT id FROM Images;"), &values); QList items; foreach (const QVariant& item, values) { items << item.toLongLong(); } return items; } QHash > CoreDB::getAllItemsWithAlbum() const { QList values; d->db->execSql(QString::fromUtf8("SELECT Images.id, Albums.albumRoot, Albums.id FROM Images " "INNER JOIN Albums ON Albums.id=Images.album " " WHERE Images.status<3;"), &values); QHash > itemAlbumHash; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { qlonglong id = (*it).toLongLong(); ++it; int albumRoot = (*it).toInt(); ++it; int album = (*it).toInt(); ++it; itemAlbumHash[id] = qMakePair(albumRoot, album); } return itemAlbumHash; } QList CoreDB::getItemScanInfos(int albumID) const { QList list; QString sql = QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize, uniqueHash " "FROM Images WHERE album=?;"); DbEngineSqlQuery query = d->db->prepareQuery(sql); query.addBindValue(albumID); if (d->db->exec(query)) { while (query.next()) { ItemScanInfo info; info.id = query.value(0).toLongLong(); info.albumID = query.value(1).toInt(); info.itemName = query.value(2).toString(); info.status = (DatabaseItem::Status)query.value(3).toInt(); info.category = (DatabaseItem::Category)query.value(4).toInt(); info.modificationDate = query.value(5).toDateTime(); info.fileSize = query.value(6).toLongLong(); info.uniqueHash = query.value(7).toString(); list << info; } } return list; } ItemScanInfo CoreDB::getItemScanInfo(qlonglong imageID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT id, album, name, status, category, modificationDate, fileSize, uniqueHash " "FROM Images WHERE id=?;"), imageID, &values); ItemScanInfo info; if (!values.isEmpty()) { QList::const_iterator it = values.constBegin(); info.id = (*it).toLongLong(); ++it; info.albumID = (*it).toInt(); ++it; info.itemName = (*it).toString(); ++it; info.status = (DatabaseItem::Status)(*it).toInt(); ++it; info.category = (DatabaseItem::Category)(*it).toInt(); ++it; info.modificationDate = (*it).toDateTime(); ++it; info.fileSize = (*it).toLongLong(); ++it; info.uniqueHash = (*it).toString(); ++it; } return info; } QStringList CoreDB::getItemURLsInTag(int tagID, bool recursive) const { QList values; QMap bindingMap; bindingMap.insert(QString::fromUtf8(":tagID"), tagID); bindingMap.insert(QString::fromUtf8(":tagID2"), tagID); if (recursive) { d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("GetItemURLsInTagRecursive")), bindingMap, &values); } else { d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("GetItemURLsInTag")), bindingMap, &values); } QStringList urls; QString albumRootPath, relativePath, name; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ) { albumRootPath = CollectionManager::instance()->albumRootPath((*it).toInt()); ++it; relativePath = (*it).toString(); ++it; name = (*it).toString(); ++it; if (relativePath == QLatin1String("/")) { urls << albumRootPath + relativePath + name; } else { urls << albumRootPath + relativePath + QLatin1Char('/') + name; } } return urls; } QList CoreDB::getItemIDsInTag(int tagID, bool recursive) const { QList itemIDs; QList values; QMap parameters; parameters.insert(QString::fromUtf8(":tagPID"), tagID); parameters.insert(QString::fromUtf8(":tagID"), tagID); if (recursive) { d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemIDsInTagRecursive")), parameters, &values); } else { d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("getItemIDsInTag")), parameters, &values); } for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { itemIDs << (*it).toLongLong(); } return itemIDs; } QString CoreDB::getAlbumRelativePath(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT relativePath from Albums WHERE id=?;"), albumID, &values); if (values.isEmpty()) { return QString(); } return values.first().toString(); } int CoreDB::getAlbumRootId(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT albumRoot FROM Albums WHERE id=?;"), albumID, &values); if (values.isEmpty()) { return -1; } return values.first().toInt(); } QDate CoreDB::getAlbumLowestDate(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT MIN(creationDate) FROM ImageInformation " "INNER JOIN Images ON Images.id=ImageInformation.imageid " " WHERE Images.album=? GROUP BY Images.album;"), albumID, &values); if (values.isEmpty()) { return QDate(); } return values.first().toDate(); } QDate CoreDB::getAlbumHighestDate(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT MAX(creationDate) FROM ImageInformation " "INNER JOIN Images ON Images.id=ImageInformation.imageid " " WHERE Images.album=? GROUP BY Images.album;"), albumID , &values); if (values.isEmpty()) { return QDate(); } return values.first().toDate(); } QDate CoreDB::getAlbumAverageDate(int albumID) const { QList values; d->db->execSql(QString::fromUtf8("SELECT creationDate FROM ImageInformation " "INNER JOIN Images ON Images.id=ImageInformation.imageid " " WHERE Images.album=?;"), albumID , &values); QList dates; for (QList::const_iterator it = values.constBegin() ; it != values.constEnd() ; ++it) { QDateTime itemDateTime = (*it).toDateTime(); if (itemDateTime.isValid()) { dates << itemDateTime.date(); } } if (dates.isEmpty()) { return QDate(); } qint64 julianDays = 0; foreach (const QDate& date, dates) { julianDays += date.toJulianDay(); } return QDate::fromJulianDay(julianDays / dates.size()); } void CoreDB::deleteItem(int albumID, const QString& file) { qlonglong imageId = getImageId(albumID, file); if (imageId == -1) { return; } d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE id=?;"), imageId); d->db->recordChangeset(CollectionImageChangeset(imageId, albumID, CollectionImageChangeset::Deleted)); } void CoreDB::deleteItem(qlonglong imageId) { d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE id=? AND album IS NULL;"), imageId); } void CoreDB::removeItemsFromAlbum(int albumID, const QList& ids_forInformation) { d->db->execSql(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE album=?;"), (int)DatabaseItem::Trashed, albumID); d->db->recordChangeset(CollectionImageChangeset(ids_forInformation, albumID, CollectionImageChangeset::RemovedAll)); } void CoreDB::removeItems(QList itemIDs, const QList& albumIDs) { DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE id=?;")); QVariantList imageIds; QVariantList status; foreach (const qlonglong& id, itemIDs) { status << (int)DatabaseItem::Trashed; imageIds << id; } query.addBindValue(status); query.addBindValue(imageIds); d->db->execBatch(query); d->db->recordChangeset(CollectionImageChangeset(itemIDs, albumIDs, CollectionImageChangeset::Removed)); } void CoreDB::removeItemsPermanently(QList itemIDs, const QList& albumIDs) { DbEngineSqlQuery query = d->db->prepareQuery(QString::fromUtf8("UPDATE Images SET status=?, album=NULL WHERE id=?;")); QVariantList imageIds; QVariantList status; foreach (const qlonglong& id, itemIDs) { status << (int)DatabaseItem::Obsolete; imageIds << id; } query.addBindValue(status); query.addBindValue(imageIds); d->db->execBatch(query); d->db->recordChangeset(CollectionImageChangeset(itemIDs, albumIDs, CollectionImageChangeset::Removed)); } void CoreDB::deleteRemovedItems() { d->db->execSql(QString::fromUtf8("DELETE FROM Images WHERE status=?;"), (int)DatabaseItem::Obsolete); d->db->recordChangeset(CollectionImageChangeset(QList(), QList(), CollectionImageChangeset::RemovedDeleted)); } void CoreDB::renameAlbum(int albumID, int newAlbumRoot, const QString& newRelativePath) { int albumRoot = getAlbumRootId(albumID); QString relativePath = getAlbumRelativePath(albumID); if (relativePath == newRelativePath && albumRoot == newAlbumRoot) { return; } // first delete any stale albums left behind at the destination of renaming QMap parameters; parameters.insert(QString::fromUtf8(":albumRoot"), newAlbumRoot); parameters.insert(QString::fromUtf8(":relativePath"), newRelativePath); if (BdEngineBackend::NoErrors != d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("deleteAlbumRootPath")), parameters)) { return; } // now update the album d->db->execSql(QString::fromUtf8("UPDATE Albums SET albumRoot=?, relativePath=? WHERE id=? AND albumRoot=?;"), newAlbumRoot, newRelativePath, albumID, albumRoot); d->db->recordChangeset(AlbumChangeset(albumID, AlbumChangeset::Renamed)); } void CoreDB::setTagName(int tagID, const QString& name) { d->db->execSql(QString::fromUtf8("UPDATE Tags SET name=? WHERE id=?;"), name, tagID); d->db->recordChangeset(TagChangeset(tagID, TagChangeset::Renamed)); } void CoreDB::moveItem(int srcAlbumID, const QString& srcName, int dstAlbumID, const QString& dstName) { // find id of src image qlonglong imageId = getImageId(srcAlbumID, srcName); if (imageId == -1) { return; } // first delete any stale database entries (for destination) if any deleteItem(dstAlbumID, dstName); d->db->execSql(QString::fromUtf8("UPDATE Images SET album=?, name=? " "WHERE id=?;"), dstAlbumID, dstName, imageId); d->db->recordChangeset(ImageChangeset(imageId, DatabaseFields::Set(DatabaseFields::Album))); d->db->recordChangeset(CollectionImageChangeset(imageId, srcAlbumID, CollectionImageChangeset::Moved)); d->db->recordChangeset(CollectionImageChangeset(imageId, srcAlbumID, CollectionImageChangeset::Removed)); d->db->recordChangeset(CollectionImageChangeset(imageId, dstAlbumID, CollectionImageChangeset::Added)); } int CoreDB::copyItem(int srcAlbumID, const QString& srcName, int dstAlbumID, const QString& dstName) { // find id of src image qlonglong srcId = getImageId(srcAlbumID, srcName); if (srcId == -1 || dstAlbumID == -1 || dstName.isEmpty()) { return -1; } // check for src == dest if (srcAlbumID == dstAlbumID && srcName == dstName) { return srcId; } // first delete any stale database entries if any deleteItem(dstAlbumID, dstName); // copy entry in Images table QVariant id; d->db->execSql(QString::fromUtf8("INSERT INTO Images " "( album, name, status, category, modificationDate, fileSize, uniqueHash ) " " SELECT ?, ?, status, category, modificationDate, fileSize, uniqueHash " " FROM Images WHERE id=?;"), dstAlbumID, dstName, srcId, nullptr, &id); if (id.isNull()) { return -1; } d->db->recordChangeset(ImageChangeset(id.toLongLong(), DatabaseFields::Set(DatabaseFields::ImagesAll))); d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), srcAlbumID, CollectionImageChangeset::Copied)); d->db->recordChangeset(CollectionImageChangeset(id.toLongLong(), dstAlbumID, CollectionImageChangeset::Added)); // copy all other tables copyImageAttributes(srcId, id.toLongLong()); return id.toLongLong(); } void CoreDB::copyImageAttributes(qlonglong srcId, qlonglong dstId) { // Go through all image-specific tables and copy the entries DatabaseFields::Set fields; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageInformation " "(imageid, rating, creationDate, digitizationDate, orientation, " " width, height, format, colorDepth, colorModel) " "SELECT ?, rating, creationDate, digitizationDate, orientation, " " width, height, format, colorDepth, colorModel " "FROM ImageInformation WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::ItemInformationAll; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageMetadata " "(imageid, make, model, lens, aperture, focalLength, focalLength35, " " exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, " " whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) " "SELECT ?, make, model, lens, aperture, focalLength, focalLength35, " " exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, " " whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory " "FROM ImageMetadata WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::ImageMetadataAll; d->db->execSql(QString::fromUtf8("REPLACE INTO VideoMetadata " "(imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, " " frameRate, videoCodec) " "SELECT ?, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, " " frameRate, videoCodec " "FROM VideoMetadata WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::VideoMetadataAll; d->db->execSql(QString::fromUtf8("REPLACE INTO ImagePositions " "(imageid, latitude, latitudeNumber, longitude, longitudeNumber, " " altitude, orientation, tilt, roll, accuracy, description) " "SELECT ?, latitude, latitudeNumber, longitude, longitudeNumber, " " altitude, orientation, tilt, roll, accuracy, description " "FROM ImagePositions WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::ItemPositionsAll; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageComments " "(imageid, type, language, author, date, comment) " "SELECT ?, type, language, author, date, comment " "FROM ImageComments WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::ItemCommentsAll; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageCopyright " "(imageid, property, value, extraValue) " "SELECT ?, property, value, extraValue " "FROM ImageCopyright WHERE imageid=?;"), dstId, srcId); d->db->execSql(QString::fromUtf8("REPLACE INTO ImageHistory " "(imageid, uuid, history) " "SELECT ?, uuid, history " "FROM ImageHistory WHERE imageid=?;"), dstId, srcId); fields |= DatabaseFields::ImageHistoryInfoAll; d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations " "(subject, object, type) " "SELECT ?, object, type " "FROM ImageRelations WHERE subject=?;"), dstId, srcId); d->db->execSql(QString::fromUtf8("REPLACE INTO ImageRelations " "(subject, object, type) " "SELECT subject, ?, type " "FROM ImageRelations WHERE object=?;"), dstId, srcId); fields |= DatabaseFields::ImageRelations; d->db->recordChangeset(ImageChangeset(dstId, fields)); copyImageTags(srcId, dstId); copyImageProperties(srcId, dstId); } void CoreDB::copyImageProperties(qlonglong srcId, qlonglong dstId) { d->db->execSql(QString::fromUtf8("REPLACE INTO ImageProperties " "(imageid, property, value) " "SELECT ?, property, value " "FROM ImageProperties WHERE imageid=?;"), dstId, srcId); } void CoreDB::copyImageTags(qlonglong srcId, qlonglong dstId) { d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTags " "(imageid, tagid) " "SELECT ?, tagid " "FROM ImageTags WHERE imageid=?;"), dstId, srcId); d->db->execSql(QString::fromUtf8("REPLACE INTO ImageTagProperties " "(imageid, tagid, property, value) " "SELECT ?, tagid, property, value " "FROM ImageTagProperties WHERE imageid=?;"), dstId, srcId); // leave empty tag list for now d->db->recordChangeset(ImageTagChangeset(dstId, QList(), ImageTagChangeset::Added)); d->db->recordChangeset(ImageTagChangeset(dstId, QList(), ImageTagChangeset::PropertiesChanged)); } bool CoreDB::copyAlbumProperties(int srcAlbumID, int dstAlbumID) const { if (srcAlbumID == dstAlbumID) { return true; } QList values; d->db->execSql(QString::fromUtf8("SELECT date, caption, collection, icon " "FROM Albums WHERE id=?;"), srcAlbumID, &values); if (values.isEmpty()) { qCWarning(DIGIKAM_DATABASE_LOG) << " src album ID " << srcAlbumID << " does not exist"; return false; } QList boundValues; boundValues << values.at(0) << values.at(1) << values.at(2) << values.at(3); boundValues << dstAlbumID; d->db->execSql(QString::fromUtf8("UPDATE Albums SET date=?, caption=?, " "collection=?, icon=? WHERE id=?;"), boundValues); return true; } QList CoreDB::getImageIdsFromArea(qreal lat1, qreal lat2, qreal lng1, qreal lng2, int /*sortMode*/, const QString& /*sortBy*/) const { QList values; QList boundValues; boundValues << lat1 << lat2 << lng1 << lng2; d->db->execSql(QString::fromUtf8("Select ImageInformation.imageid, ImageInformation.rating, " "ImagePositions.latitudeNumber, ImagePositions.longitudeNumber " "FROM ImageInformation INNER JOIN ImagePositions " " ON ImageInformation.imageid = ImagePositions.imageid " " WHERE (ImagePositions.latitudeNumber>? AND ImagePositions.latitudeNumber? AND ImagePositions.longitudeNumberdb->execSql(QString::fromUtf8("DELETE FROM ImagePositions WHERE imageid=?;"), imageID); fields |= DatabaseFields::ItemPositionsAll; d->db->execSql(QString::fromUtf8("DELETE FROM ImageCopyright WHERE imageid=?;"), imageID); d->db->execSql(QString::fromUtf8("DELETE FROM ImageComments WHERE imageid=?;"), imageID); fields |= DatabaseFields::ItemCommentsAll; d->db->execSql(QString::fromUtf8("DELETE FROM ImageMetadata WHERE imageid=?;"), imageID); fields |= DatabaseFields::ImageMetadataAll; d->db->execSql(QString::fromUtf8("DELETE FROM VideoMetadata WHERE imageid=?;"), imageID); fields |= DatabaseFields::VideoMetadataAll; d->db->recordChangeset(ImageChangeset(imageID, fields)); QList tagIds = getItemTagIDs(imageID); if (!tagIds.isEmpty()) { d->db->execSql(QString::fromUtf8("DELETE FROM ImageTags WHERE imageid=?;"), imageID); d->db->recordChangeset(ImageTagChangeset(imageID, tagIds, ImageTagChangeset::RemovedAll)); } QList properties = getImageTagProperties(imageID); if (!properties.isEmpty()) { - QList tagIds; + QList tids; foreach (const ImageTagProperty& property, properties) { - tagIds << property.imageId; + tids << property.imageId; } d->db->execSql(QString::fromUtf8("DELETE FROM ImageTagProperties WHERE imageid=?;"), imageID); - d->db->recordChangeset(ImageTagChangeset(imageID, tagIds, ImageTagChangeset::PropertiesChanged)); + d->db->recordChangeset(ImageTagChangeset(imageID, tids, ImageTagChangeset::PropertiesChanged)); } } bool CoreDB::integrityCheck() const { QList values; d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("checkCoreDbIntegrity")), &values); switch (d->db->databaseType()) { case BdEngineBackend::DbType::SQLite: // For SQLite the integrity check returns a single row with one string column "ok" on success and multiple rows on error. return values.size() == 1 && values.first().toString().toLower().compare(QLatin1String("ok")) == 0; case BdEngineBackend::DbType::MySQL: // For MySQL, for every checked table, the table name, operation (check), message type (status) and the message text (ok on success) // are returned. So we check if there are four elements and if yes, whether the fourth element is "ok". //qCDebug(DIGIKAM_DATABASE_LOG) << "MySQL check returned " << values.size() << " rows"; if ((values.size() % 4) != 0) { return false; } for (QList::iterator it = values.begin() ; it != values.end() ; ) { QString tableName = (*it).toString(); ++it; QString operation = (*it).toString(); ++it; QString messageType = (*it).toString(); ++it; QString messageText = (*it).toString(); ++it; if (messageText.toLower().compare(QLatin1String("ok")) != 0) { qCDebug(DIGIKAM_DATABASE_LOG) << "Failed integrity check for table " << tableName << ". Reason:" << messageText; return false; } else { //qCDebug(DIGIKAM_DATABASE_LOG) << "Passed integrity check for table " << tableName; } } // No error conditions. Db passed the integrity check. return true; default: return false; } } void CoreDB::vacuum() { d->db->execDBAction(d->db->getDBAction(QString::fromUtf8("vacuumCoreDB"))); } void CoreDB::readSettings() { KSharedConfig::Ptr config = KSharedConfig::openConfig(); KConfigGroup group = config->group(d->configGroupName); d->recentlyAssignedTags = group.readEntry(d->configRecentlyUsedTags, QList()); } void CoreDB::writeSettings() { KSharedConfig::Ptr config = KSharedConfig::openConfig(); KConfigGroup group = config->group(d->configGroupName); group.writeEntry(d->configRecentlyUsedTags, d->recentlyAssignedTags); } } // namespace Digikam