7
7
* Description :database album interface.
9
* Copyright (C) 2004-2005 by Renchi Raju <renchi@pooh.tam.uiuc.edu>
10
* Copyright (C) 2006-2010 by Gilles Caulier <caulier dot gilles at gmail dot com>
11
* Copyright (C) 2006-2010 by Marcel Wiesweg <marcel dot wiesweg at gmx dot de>
12
* Copyright (C) 2012 by Andi Clemens <andi dot clemens at googlemail dot com>
9
* Copyright (C) 2004-2005 by Renchi Raju <renchi dot raju at gmail dot com>
10
* Copyright (C) 2006-2012 by Gilles Caulier <caulier dot gilles at gmail dot com>
11
* Copyright (C) 2006-2012 by Marcel Wiesweg <marcel dot wiesweg at gmx dot de>
12
* Copyright (C) 2012 by Andi Clemens <andi dot clemens at gmail dot com>
14
14
* This program is free software; you can redistribute it
15
15
* and/or modify it under the terms of the GNU General
88
88
QList<qlonglong> execRelatedImagesQuery(SqlQuery& query, qlonglong id, DatabaseRelation::Type type);
91
const QString AlbumDB::AlbumDBPriv::configGroupName("AlbumDB Settings");
92
const QString AlbumDB::AlbumDBPriv::configRecentlyUsedTags("Recently Used Tags");
91
const QString AlbumDB::Private::configGroupName("AlbumDB Settings");
92
const QString AlbumDB::Private::configRecentlyUsedTags("Recently Used Tags");
94
QString AlbumDB::Private::constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean)
100
sql = "SELECT object FROM ImageRelations "
101
"INNER JOIN Images ON ImageRelations.object=Images.id "
102
"WHERE subject=? %1 AND status!=3 %2;";
106
sql = "SELECT subject FROM ImageRelations "
107
"INNER JOIN Images ON ImageRelations.subject=Images.id "
108
"WHERE object=? %1 AND status!=3 %2;";
111
if (type != DatabaseRelation::UndefinedType)
113
sql = sql.arg(QString("AND type=?"));
117
sql = sql.arg(QString());
122
sql = sql.arg(QString("LIMIT 1"));
126
sql = sql.arg(QString());
132
QList<qlonglong> AlbumDB::Private::execRelatedImagesQuery(SqlQuery& query, qlonglong id, DatabaseRelation::Type type)
136
if (type == DatabaseRelation::UndefinedType)
138
db->execSql(query, id, &values);
142
db->execSql(query, id, type, &values);
145
QList<qlonglong> imageIds;
147
if (values.isEmpty())
152
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd(); ++it)
154
imageIds << (*it).toInt();
94
160
// --------------------------------------------------------
96
AlbumDB::AlbumDB(DatabaseBackend* backend)
162
AlbumDB::AlbumDB(DatabaseBackend* const backend)
109
175
QList<AlbumRootInfo> AlbumDB::getAlbumRoots()
111
177
QList<AlbumRootInfo> list;
178
QList<QVariant> values;
113
QList<QVariant> values;
114
180
d->db->execSql("SELECT id, label, status, type, identifier, specificPath FROM AlbumRoots;", &values);
116
182
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd();)
329
395
SearchInfo::List AlbumDB::scanSearches()
331
397
SearchInfo::List searchList;
398
QList<QVariant> values;
333
QList<QVariant> values;
334
400
d->db->execSql("SELECT id, type, name, query FROM Searches;", &values);
336
402
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd();)
380
447
QList<TagShortInfo> AlbumDB::getTagShortInfos()
382
449
QList<QVariant> values;
383
d->db->execSql(QString("SELECT id, pid, name FROM Tags ORDER BY id;"),
451
d->db->execSql(QString("SELECT id, pid, name FROM Tags ORDER BY id;"), &values);
386
453
QList<TagShortInfo> tagList;
459
526
const QString& caption,
460
527
const QDate& date, const QString& collection)
463
530
QList<QVariant> boundValues;
464
532
boundValues << albumRootId << relativePath << date.toString(Qt::ISODate) << caption << collection;
466
534
d->db->execSql(QString("REPLACE INTO Albums (albumRoot, relativePath, date, caption, collection) "
505
573
bool AlbumDB::getAlbumIcon(int albumID, int* albumRootId, QString* iconRelativePath)
507
575
QList<QVariant> values;
508
577
d->db->execSql(QString("SELECT B.relativePath, I.name, B.albumRoot \n "
509
578
"FROM Albums AS A \n "
510
579
" LEFT JOIN Images AS I ON I.id=A.icon \n "
520
589
QList<QVariant>::const_iterator it = values.constBegin();
521
QString album = (*it).toString();
523
QString iconName = (*it).toString();
525
*albumRootId = (*it).toInt();
590
QString album = (*it).toString();
592
QString iconName = (*it).toString();
594
*albumRootId = (*it).toInt();
527
*iconRelativePath = album + '/' + iconName;
596
*iconRelativePath = album + '/' + iconName;
529
598
return !iconName.isEmpty();
594
663
int AlbumDB::addTag(int parentTagID, const QString& name, const QString& iconKDE,
595
664
qlonglong iconID)
598
667
QMap<QString, QVariant> parameters;
599
669
parameters.insert(":tagPID", parentTagID);
600
670
parameters.insert(":tagname", name);
624
694
void AlbumDB::deleteTag(int tagID)
627
QString("DELETE FROM Tags WHERE id=?;"),
697
QString("DELETE FROM Tags WHERE id=?;"), tagID
630
700
QMap<QString, QVariant> bindingMap;
631
701
bindingMap.insert(QString(":tagID"), tagID);
780
849
void AlbumDB::addTagProperty(int tagId, const QString& property, const QString& value)
782
851
d->db->execSql("INSERT INTO TagProperties (tagid, property, value) VALUES(?, ?, ?);",
915
984
static QStringList joinMainAndUserFilterString(const QString& filter, const QString& userFilter)
917
986
QSet<QString> filterSet;
918
QStringList userFilterList;
919
QStringList sortedList;
987
QStringList userFilterList;
988
QStringList sortedList;
921
filterSet = filter.split(';', QString::SkipEmptyParts).toSet();
990
filterSet = filter.split(';', QString::SkipEmptyParts).toSet();
922
991
userFilterList = userFilter.split(';', QString::SkipEmptyParts);
923
993
foreach(const QString& userFormat, userFilterList)
925
995
if (userFormat.startsWith('-'))
1033
1104
return filterList;
1036
void AlbumDB::setUserFilterSettings(const QString& imageFilterString, const QString& videoFilterString, const QString& audioFilterString)
1107
void AlbumDB::setUserFilterSettings(const QString& imageFilterString, const QString& videoFilterString,
1108
const QString& audioFilterString)
1038
1110
setUserFilterSettings(cleanUserFilterString(imageFilterString),
1039
1111
cleanUserFilterString(videoFilterString),
1050
1122
void AlbumDB::addToUserImageFilterSettings(const QString& filterString)
1052
QStringList addList = cleanUserFilterString(filterString);
1124
QStringList addList = cleanUserFilterString(filterString);
1054
1125
QStringList currentList = getSetting("databaseUserImageFormats").split(';', QString::SkipEmptyParts);
1068
1139
QUuid AlbumDB::databaseUuid()
1070
1141
QString uuidString = getSetting("databaseUUID");
1071
QUuid uuid = QUuid(uuidString);
1142
QUuid uuid = QUuid(uuidString);
1073
1144
if (uuidString.isNull() || uuid.isNull())
1247
1318
QVector<QList<int> > results(imageIds.size());
1249
SqlQuery query = d->db->prepareQuery("SELECT tagid FROM ImageTags WHERE imageID=?;");
1251
QVariantList values;
1319
SqlQuery query = d->db->prepareQuery("SELECT tagid FROM ImageTags WHERE imageID=?;");
1320
QVariantList values;
1253
1322
for (int i = 0; i < imageIds.size(); i++)
1255
1324
d->db->execSql(query, imageIds[i], &values);
1256
1325
QList<int>& tagIds = results[i];
1257
1327
foreach(const QVariant& v, values)
1259
1329
tagIds << v.toInt();
1541
1609
int index = fieldNames.indexOf("creationDate");
1542
1610
values[index] = (values.at(index).isNull() ? QDateTime()
1543
: QDateTime::fromString(values.at(index).toString(), Qt::ISODate));
1611
: QDateTime::fromString(values.at(index).toString(), Qt::ISODate));
1546
1614
if ((fields & DatabaseFields::DigitizationDate) && !values.isEmpty())
1548
1616
int index = fieldNames.indexOf("digitizationDate");
1549
1617
values[index] = (values.at(index).isNull() ? QDateTime()
1550
: QDateTime::fromString(values.at(index).toString(), Qt::ISODate));
1618
: QDateTime::fromString(values.at(index).toString(), Qt::ISODate));
1568
1636
d->db->execSql(query, imageID, &values);
1638
// For some reason, if REAL values may be required from variables stored as QString QVariants. Convert code will come here.
1644
QVariantList AlbumDB::getVideoMetadata(qlonglong imageID, DatabaseFields::VideoMetadata fields)
1646
QVariantList values;
1648
if (fields != DatabaseFields::VideoMetadataNone)
1650
QString query("SELECT ");
1651
QStringList fieldNames = videoMetadataFieldList(fields);
1652
query += fieldNames.join(", ");
1653
query += (" FROM VideoMetadata WHERE imageid=?;");
1655
d->db->execSql(query, imageID, &values);
1570
1657
// For some reason REAL values may come as QString QVariants. Convert here.
1571
1658
if (values.size() == fieldNames.size() &&
1572
1659
((fields & DatabaseFields::Aperture) ||
1602
1689
if (fields != DatabaseFields::ImagePositionsNone)
1604
1691
QString query("SELECT ");
1605
QStringList fieldNames = imagePositionsFieldList(fields);
1606
query += fieldNames.join(", ");
1607
query += (" FROM ImagePositions WHERE imageid=?;");
1692
QStringList fieldNames = imagePositionsFieldList(fields);
1693
query += fieldNames.join(", ");
1694
query += (" FROM ImagePositions WHERE imageid=?;");
1609
1696
d->db->execSql(query, imageID, &values);
1611
1698
// For some reason REAL values may come as QString QVariants. Convert here.
1612
1699
if (values.size() == fieldNames.size() &&
1613
((fields & DatabaseFields::LatitudeNumber) ||
1614
(fields & DatabaseFields::LongitudeNumber) ||
1615
(fields & DatabaseFields::Altitude) ||
1700
((fields & DatabaseFields::LatitudeNumber) ||
1701
(fields & DatabaseFields::LongitudeNumber) ||
1702
(fields & DatabaseFields::Altitude) ||
1616
1703
(fields & DatabaseFields::PositionOrientation) ||
1617
(fields & DatabaseFields::PositionTilt) ||
1618
(fields & DatabaseFields::PositionRoll) ||
1704
(fields & DatabaseFields::PositionTilt) ||
1705
(fields & DatabaseFields::PositionRoll) ||
1619
1706
(fields & DatabaseFields::PositionAccuracy))
1622
1709
for (int i = 0; i < values.size(); ++i)
1624
1711
if (values.at(i).type() == QVariant::String &&
1625
(fieldNames.at(i) == "latitudeNumber" ||
1712
(fieldNames.at(i) == "latitudeNumber" ||
1626
1713
fieldNames.at(i) == "longitudeNumber" ||
1627
fieldNames.at(i) == "altitude" ||
1628
fieldNames.at(i) == "orientation" ||
1629
fieldNames.at(i) == "tilt" ||
1630
fieldNames.at(i) == "roll" ||
1714
fieldNames.at(i) == "altitude" ||
1715
fieldNames.at(i) == "orientation" ||
1716
fieldNames.at(i) == "tilt" ||
1717
fieldNames.at(i) == "roll" ||
1631
1718
fieldNames.at(i) == "accuracy")
1647
1734
if (fields != DatabaseFields::ImagePositionsNone)
1649
1736
QString sql("SELECT ");
1650
QStringList fieldNames = imagePositionsFieldList(fields);
1651
sql += fieldNames.join(", ");
1652
sql += (" FROM ImagePositions WHERE imageid=?;");
1737
QStringList fieldNames = imagePositionsFieldList(fields);
1738
sql += fieldNames.join(", ");
1739
sql += (" FROM ImagePositions WHERE imageid=?;");
1654
1741
SqlQuery query = d->db->prepareQuery(sql);
1663
1750
// For some reason REAL values may come as QString QVariants. Convert here.
1664
1751
if (values.size() == fieldNames.size() &&
1665
(fields & DatabaseFields::LatitudeNumber ||
1666
fields & DatabaseFields::LongitudeNumber ||
1667
fields & DatabaseFields::Altitude ||
1752
(fields & DatabaseFields::LatitudeNumber ||
1753
fields & DatabaseFields::LongitudeNumber ||
1754
fields & DatabaseFields::Altitude ||
1668
1755
fields & DatabaseFields::PositionOrientation ||
1669
fields & DatabaseFields::PositionTilt ||
1670
fields & DatabaseFields::PositionRoll ||
1756
fields & DatabaseFields::PositionTilt ||
1757
fields & DatabaseFields::PositionRoll ||
1671
1758
fields & DatabaseFields::PositionAccuracy)
1674
1761
for (int i = 0; i < values.size(); ++i)
1676
1763
if (values.at(i).type() == QVariant::String &&
1677
(fieldNames.at(i) == "latitudeNumber" ||
1764
(fieldNames.at(i) == "latitudeNumber" ||
1678
1765
fieldNames.at(i) == "longitudeNumber" ||
1679
fieldNames.at(i) == "altitude" ||
1680
fieldNames.at(i) == "orientation" ||
1681
fieldNames.at(i) == "tilt" ||
1682
fieldNames.at(i) == "roll" ||
1766
fieldNames.at(i) == "altitude" ||
1767
fieldNames.at(i) == "orientation" ||
1768
fieldNames.at(i) == "tilt" ||
1769
fieldNames.at(i) == "roll" ||
1683
1770
fieldNames.at(i) == "accuracy")
1702
1789
QString query("REPLACE INTO ImageInformation ( imageid, ");
1704
1791
QStringList fieldNames = imageInformationFieldList(fields);
1705
1793
Q_ASSERT(fieldNames.size() == infos.size());
1706
1795
query += fieldNames.join(", ");
1708
1796
query += " ) VALUES (";
1709
1797
addBoundValuePlaceholders(query, infos.size() + 1);
1775
1863
QString query("REPLACE INTO ImageMetadata ( imageid, ");
1777
1864
QStringList fieldNames = imageMetadataFieldList(fields);
1778
1866
Q_ASSERT(fieldNames.size() == infos.size());
1779
1868
query += fieldNames.join(", ");
1781
1869
query += " ) VALUES (";
1782
1870
addBoundValuePlaceholders(query, infos.size() + 1);
1800
1888
QString query("UPDATE ImageMetadata SET ");
1802
1890
QStringList fieldNames = imageMetadataFieldList(fields);
1803
Q_ASSERT(fieldNames.size() == infos.size());
1804
query += fieldNames.join("=?,");
1892
Q_ASSERT(fieldNames.size() == infos.size());
1894
query += fieldNames.join("=?,");
1895
query += "=? WHERE imageid=?;";
1897
QVariantList boundValues;
1898
boundValues << infos << imageId;
1900
d->db->execSql(query, boundValues);
1901
d->db->recordChangeset(ImageChangeset(imageId, fields));
1904
void AlbumDB::addVideoMetadata(qlonglong imageID, const QVariantList& infos, DatabaseFields::VideoMetadata fields)
1906
if (fields == DatabaseFields::VideoMetadataNone)
1911
QString query("REPLACE INTO VideoMetadata ( imageid, "); //need to create this database
1912
QStringList fieldNames = videoMetadataFieldList(fields);
1914
Q_ASSERT(fieldNames.size() == infos.size());
1916
query += fieldNames.join(", ");
1917
query += " ) VALUES (";
1918
addBoundValuePlaceholders(query, infos.size() + 1);
1921
QVariantList boundValues;
1922
boundValues << imageID << infos;
1924
d->db->execSql(query, boundValues);
1925
d->db->recordChangeset(ImageChangeset(imageID, fields));
1928
void AlbumDB::changeVideoMetadata(qlonglong imageId, const QVariantList& infos,
1929
DatabaseFields::VideoMetadata fields)
1931
if (fields == DatabaseFields::VideoMetadataNone)
1936
QString query("UPDATE VideoMetadata SET ");
1937
QStringList fieldNames = videoMetadataFieldList(fields);
1939
Q_ASSERT(fieldNames.size() == infos.size());
1941
query += fieldNames.join("=?,");
1806
1942
query += "=? WHERE imageid=?;";
1808
1944
QVariantList boundValues;
1822
1958
QString query("REPLACE INTO ImagePositions ( imageid, ");
1824
1959
QStringList fieldNames = imagePositionsFieldList(fields);
1825
1961
Q_ASSERT(fieldNames.size() == infos.size());
1826
1963
query += fieldNames.join(", ");
1828
1964
query += " ) VALUES (";
1829
1965
addBoundValuePlaceholders(query, infos.size() + 1);
1847
1983
QString query("UPDATE ImagePositions SET ");
1849
1984
QStringList fieldNames = imagePositionsFieldList(fields);
1850
1986
Q_ASSERT(fieldNames.size() == infos.size());
1851
1988
query += fieldNames.join("=?,");
1853
1989
query += "=? WHERE imageid=?;";
1855
1991
QVariantList boundValues;
1926
2062
QString query("UPDATE ImageComments SET ");
1928
2063
QStringList fieldNames = imageCommentsFieldList(fields);
1929
2065
Q_ASSERT(fieldNames.size() == infos.size());
1930
2067
query += fieldNames.join("=?,");
1931
2068
query += "=? WHERE id=?;";
2303
2439
return !getRelatedImages(objectId, false, type, true).isEmpty();
2306
QString AlbumDB::AlbumDBPriv::constructRelatedImagesSQL(bool fromOrTo, DatabaseRelation::Type type, bool boolean)
2312
sql = "SELECT object FROM ImageRelations "
2313
"INNER JOIN Images ON ImageRelations.object=Images.id "
2314
"WHERE subject=? %1 AND status!=3 %2;";
2318
sql = "SELECT subject FROM ImageRelations "
2319
"INNER JOIN Images ON ImageRelations.subject=Images.id "
2320
"WHERE object=? %1 AND status!=3 %2;";
2323
if (type != DatabaseRelation::UndefinedType)
2325
sql = sql.arg(QString("AND type=?"));
2329
sql = sql.arg(QString());
2334
sql = sql.arg(QString("LIMIT 1"));
2338
sql = sql.arg(QString());
2344
QList<qlonglong> AlbumDB::AlbumDBPriv::execRelatedImagesQuery(SqlQuery& query, qlonglong id, DatabaseRelation::Type type)
2346
QVariantList values;
2348
if (type == DatabaseRelation::UndefinedType)
2350
db->execSql(query, id, &values);
2354
db->execSql(query, id, type, &values);
2357
QList<qlonglong> imageIds;
2359
if (values.isEmpty())
2364
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd(); ++it)
2366
imageIds << (*it).toInt();
2372
2442
QList<qlonglong> AlbumDB::getRelatedImages(qlonglong id, bool fromOrTo, DatabaseRelation::Type type, bool boolean)
2374
2444
QString sql = d->constructRelatedImagesSQL(fromOrTo, type, boolean);
2479
2549
sql = sql.arg("AND type=?");
2482
SqlQuery query = d->db->prepareQuery(sql);
2552
SqlQuery query = d->db->prepareQuery(sql);
2484
2553
QSet<qlonglong> result;
2485
2554
QList<QVariant> values;
2486
2556
foreach(qlonglong id, ids)
2488
2558
if (type == DatabaseRelation::UndefinedType)
2562
2632
QStringList urls;
2563
QString albumRootPath, relativePath, name;
2633
QString albumRootPath, relativePath, name;
2565
2635
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd();)
2600
2670
QStringList urls;
2601
QString albumRootPath, relativePath, name;
2671
QString albumRootPath, relativePath, name;
2603
2673
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd();)
2869
QStringList AlbumDB::videoMetadataFieldList(DatabaseFields::VideoMetadata fields)
2871
// adds no spaces at beginning or end
2874
if (fields & DatabaseFields::AspectRatio)
2876
list << "aspectRatio";
2879
if (fields & DatabaseFields::AudioBitRate)
2881
list << "audioBitRate";
2884
if (fields & DatabaseFields::AudioChannelType)
2886
list << "audioChannelType";
2889
if (fields & DatabaseFields::AudioCompressor)
2891
list << "audioCompressor";
2894
if (fields & DatabaseFields::Duration)
2899
if (fields & DatabaseFields::FrameRate)
2901
list << "frameRate";
2904
if (fields & DatabaseFields::VideoCodec)
2906
list << "videoCodec";
2799
2912
QStringList AlbumDB::imageMetadataFieldList(DatabaseFields::ImageMetadata fields)
2801
2914
// adds no spaces at beginning or end
4059
4169
QStringList AlbumDB::getItemURLsInTag(int tagID, bool recursive)
4061
QList<QVariant> values;
4063
QString imagesIdClause;
4171
QList<QVariant> values;
4172
QString imagesIdClause;
4065
4173
QMap<QString, QVariant> bindingMap;
4066
4175
bindingMap.insert(QString(":tagID"), tagID);
4067
4176
bindingMap.insert(QString(":tagID2"), tagID);
4078
4187
QStringList urls;
4079
QString albumRootPath, relativePath, name;
4188
QString albumRootPath, relativePath, name;
4081
4190
for (QList<QVariant>::const_iterator it = values.constBegin(); it != values.constEnd();)
4103
4212
QList<qlonglong> AlbumDB::getItemIDsInTag(int tagID, bool recursive)
4105
QList<qlonglong> itemIDs;
4106
QList<QVariant> values;
4214
QList<qlonglong> itemIDs;
4215
QList<QVariant> values;
4216
QMap<QString, QVariant> parameters;
4108
QMap<QString, QVariant> parameters;
4109
4218
parameters.insert(":tagPID", tagID);
4110
4219
parameters.insert(":tagID", tagID);
4466
4577
fields |= DatabaseFields::ImageMetadataAll;
4579
d->db->execSql(QString("INSERT INTO VideoMetadata "
4580
" (imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, "
4582
"SELECT ?, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, "
4584
"FROM VideoMetadata WHERE imageid=?;"),
4586
fields |= DatabaseFields::VideoMetadataAll;
4468
4588
d->db->execSql(QString("INSERT INTO ImagePositions "
4469
4589
" (imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
4470
4590
" altitude, orientation, tilt, roll, accuracy, description) "
4576
QList<QVariant> AlbumDB::getImageIdsFromArea(qreal lat1, qreal lat2, qreal lng1, qreal lng2, int /*sortMode*/, const QString& /*sortBy*/)
4696
QList<QVariant> AlbumDB::getImageIdsFromArea(qreal lat1, qreal lat2, qreal lng1, qreal lng2, int /*sortMode*/,
4697
const QString& /*sortBy*/)
4578
4699
QList<QVariant> values;
4579
4700
QList<QVariant> boundValues;