File indexing completed on 2025-04-20 04:02:15

 // SPDX-FileCopyrightText: 2014 Vishesh Handa <vhanda@kde.org>
 // SPDX-FileCopyrightText: 2017 Atul Sharma <atulsharma406@gmail.com>
 // SPDX-License-Identifier: LGPL-2.1-or-later
 
 #include "imagestorage.h"
 
 #include <QDataStream>
 #include <QDebug>
 #include <QGeoAddress>
 #include <QGeoCoordinate>
 
 #include <QDir>
 #include <QStandardPaths>
 #include <QUrl>
 
 #include <QSqlDatabase>
 #include <QSqlError>
 #include <QSqlQuery>
 
 ImageStorage::ImageStorage(QObject *parent)
     : QObject(parent)
 {
     QString dir = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation) + "/koko";
     QDir().mkpath(dir);
 
     QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
     db.setDatabaseName(dir + "/imageData.sqlite3");
 
     if (!db.open()) {
         qDebug() << "Failed to open db" << db.lastError().text();
         return;
     }
 
     if (db.tables().contains("files")) {
         QSqlQuery query(db);
         query.prepare("PRAGMA table_info(files)");
         bool favorites_present = false;
         if (!query.exec()) {
             qDebug() << "Failed to read db" << query.lastError();
             return;
         }
         while (query.next()) {
             if (query.value(1).toString() == "favorite") {
                 favorites_present = true;
             }
         }
         if (!favorites_present) {
             // migrate to new table
             query.exec("ALTER TABLE files ADD COLUMN favorite INTEGER");
         }
 
         db.transaction();
 
         return;
     }
 
     QSqlQuery query(db);
     query.exec(
         "CREATE TABLE locations (id INTEGER PRIMARY KEY, country TEXT, state TEXT, city TEXT"
         "                        , UNIQUE(country, state, city) ON CONFLICT REPLACE"
         ")");
     query.exec("CREATE TABLE tags (url TEXT NOT NULL, tag TEXT)");
     query.exec(
         "CREATE TABLE files (url TEXT NOT NULL UNIQUE PRIMARY KEY,"
         "                    favorite INTEGER,"
         "                    location INTEGER,"
         "                    dateTime STRING NOT NULL,"
         "                    FOREIGN KEY(location) REFERENCES locations(id)"
         "                    FOREIGN KEY(url) REFERENCES tags(url)"
         "                    )");
 
     db.transaction();
 }
 
 ImageStorage::~ImageStorage()
 {
     QString name;
     {
         QSqlDatabase db = QSqlDatabase::database();
         db.commit();
         name = db.connectionName();
     }
     QSqlDatabase::removeDatabase(name);
 }
 
 ImageStorage *ImageStorage::instance()
 {
     static ImageStorage storage;
     return &storage;
 }
 
 void ImageStorage::addImage(const ImageInfo &ii)
 {
     if (imageExists(ii.path)) {
         removeImage(ii.path);
     }
     QMutexLocker lock(&m_mutex);
     QGeoAddress addr = ii.location.address();
 
     if (!addr.country().isEmpty()) {
         int locId = -1;
 
         if (!addr.city().isEmpty()) {
             QSqlQuery query;
             query.prepare("SELECT id FROM LOCATIONS WHERE country = ? AND state = ? AND city = ?");
             query.addBindValue(addr.country());
             query.addBindValue(addr.state());
             query.addBindValue(addr.city());
             if (!query.exec()) {
                 qDebug() << "LOC SELECT" << query.lastError();
             }
 
             if (query.next()) {
                 locId = query.value(0).toInt();
             }
         } else {
             QSqlQuery query;
             query.prepare("SELECT id FROM LOCATIONS WHERE country = ? AND state = ?");
             query.addBindValue(addr.country());
             query.addBindValue(addr.state());
             if (!query.exec()) {
                 qDebug() << "LOC SELECT" << query.lastError();
             }
 
             if (query.next()) {
                 locId = query.value(0).toInt();
             }
         }
 
         if (locId == -1) {
             QSqlQuery query;
             query.prepare("INSERT INTO LOCATIONS(country, state, city) VALUES (?, ?, ?)");
             query.addBindValue(addr.country());
             query.addBindValue(addr.state());
             query.addBindValue(addr.city());
             if (!query.exec()) {
                 qDebug() << "LOC INSERT" << query.lastError();
             }
 
             locId = query.lastInsertId().toInt();
         }
 
         QSqlQuery query;
         query.prepare("INSERT INTO FILES(url, favorite, location, dateTime) VALUES(?, ?, ?, ?)");
         query.addBindValue(ii.path);
         query.addBindValue(int(ii.favorite));
         query.addBindValue(locId);
         query.addBindValue(ii.dateTime.toString(Qt::ISODate));
         if (!query.exec()) {
             qDebug() << "FILE LOC INSERT" << query.lastError();
         }
     } else {
         QSqlQuery query;
         query.prepare("INSERT INTO FILES(url, favorite, dateTime) VALUES(?, ?, ?)");
         query.addBindValue(ii.path);
         query.addBindValue(int(ii.favorite));
         query.addBindValue(ii.dateTime.toString(Qt::ISODate));
         if (!query.exec()) {
             qDebug() << "FILE INSERT" << query.lastError();
         }
     }
 
     for (const auto &tag : std::as_const(ii.tags)) {
         QSqlQuery query;
         query.prepare("SELECT url FROM TAGS WHERE url = ? AND tag = ?");
         query.addBindValue(ii.path);
         query.addBindValue(tag);
 
         if (!query.exec()) {
             qDebug() << "tag select" << query.lastError();
         }
 
         if (!query.next()) {
             QSqlQuery query;
             query.prepare("INSERT INTO TAGS(url, tag) VALUES (?, ?)");
             query.addBindValue(ii.path);
             query.addBindValue(tag);
             if (!query.exec()) {
                 qDebug() << "tag insert" << query.lastError();
             }
         }
     }
 }
 
 bool ImageStorage::imageExists(const QString &filePath)
 {
     QMutexLocker lock(&m_mutex);
 
     QSqlQuery query;
     query.prepare("SELECT EXISTS(SELECT 1 FROM files WHERE url = ?)");
     query.addBindValue(filePath);
 
     if (!query.exec()) {
         qDebug() << query.lastError();
         return false;
     }
 
     return query.next();
 }
 
 void ImageStorage::removeImage(const QString &filePath)
 {
     QMutexLocker lock(&m_mutex);
 
     QSqlQuery query;
     query.prepare("DELETE FROM FILES WHERE URL = ?");
     query.addBindValue(filePath);
     if (!query.exec()) {
         qDebug() << "FILE del" << query.lastError();
     }
 
     QSqlQuery query2;
     query2.prepare("DELETE FROM LOCATIONS WHERE id NOT IN (SELECT DISTINCT location FROM files WHERE location IS NOT NULL)");
     if (!query2.exec()) {
         qDebug() << "Loc del" << query2.lastError();
     }
 
     QSqlQuery query3;
     query3.prepare("DELETE FROM TAGS WHERE url NOT IN (SELECT DISTINCT url FROM files)");
     if (!query3.exec()) {
         qDebug() << "tag delete" << query3.lastError();
     }
 }
 
 void ImageStorage::commit()
 {
     {
         QMutexLocker lock(&m_mutex);
         QSqlDatabase db = QSqlDatabase::database();
         db.commit();
         db.transaction();
     }
 
     emit storageModified();
 }
 
 QList<QPair<QByteArray, QString>> ImageStorage::locations(Types::LocationGroup loca)
 {
     QMutexLocker lock(&m_mutex);
     QList<QPair<QByteArray, QString>> list;
 
     if (loca == Types::LocationGroup::Country) {
         QSqlQuery query;
         query.prepare("SELECT DISTINCT country from locations");
 
         if (!query.exec()) {
             qDebug() << loca << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString val = query.value(0).toString();
             list << qMakePair(val.toUtf8(), val);
         }
         return list;
     } else if (loca == Types::LocationGroup::State) {
         QSqlQuery query;
         query.prepare("SELECT DISTINCT country, state from locations");
 
         if (!query.exec()) {
             qDebug() << loca << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString country = query.value(0).toString();
             QString state = query.value(1).toString();
             QString display = state + ", " + country;
 
             QByteArray key;
             QDataStream stream(&key, QIODevice::WriteOnly);
             stream << country << state;
 
             list << qMakePair(key, display);
         }
         return list;
     } else if (loca == Types::LocationGroup::City) {
         QSqlQuery query;
         query.prepare("SELECT DISTINCT country, state, city from locations");
 
         if (!query.exec()) {
             qDebug() << loca << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString country = query.value(0).toString();
             QString state = query.value(1).toString();
             QString city = query.value(2).toString();
 
             QString display;
             if (!city.isEmpty()) {
                 display = city + ", " + state + ", " + country;
             } else {
                 display = state + ", " + country;
             }
 
             QByteArray key;
             QDataStream stream(&key, QIODevice::WriteOnly);
             stream << country << state << city;
 
             list << qMakePair(key, display);
         }
         return list;
     }
 
     return list;
 }
 
 QStringList ImageStorage::imagesForFavorites()
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
 
     query.prepare("SELECT DISTINCT url from files where favorite = 1");
 
     if (!query.exec()) {
         qDebug() << "imagesForFavorites: " << query.lastError();
         return QStringList();
     }
 
     QStringList files;
     while (query.next()) {
         files << QString("file://" + query.value(0).toString());
     }
 
     return files;
 }
 
 QStringList ImageStorage::tags()
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
 
     query.prepare("SELECT DISTINCT tag from tags");
 
     if (!query.exec()) {
         qDebug() << "tags: " << query.lastError();
         return QStringList();
     }
 
     QStringList tags;
     while (query.next()) {
         tags << query.value(0).toString();
     }
 
     return tags;
 }
 
 QStringList ImageStorage::imagesForTag(const QString &tag)
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
 
     query.prepare("SELECT DISTINCT url from tags where tag = ?");
     query.addBindValue(tag);
 
     if (!query.exec()) {
         qDebug() << "imagesForTag: " << query.lastError();
         return QStringList();
     }
 
     QStringList files;
     while (query.next()) {
         files << QString("file://" + query.value(0).toString());
     }
 
     return files;
 }
 
 QStringList ImageStorage::imagesForLocation(const QByteArray &name, Types::LocationGroup loc)
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
     if (loc == Types::LocationGroup::Country) {
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND files.location = locations.id");
         query.addBindValue(QString::fromUtf8(name));
     } else if (loc == Types::LocationGroup::State) {
         QDataStream st(name);
 
         QString country;
         QString state;
         st >> country >> state;
 
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND state = ? AND files.location = locations.id");
         query.addBindValue(country);
         query.addBindValue(state);
     } else if (loc == Types::LocationGroup::City) {
         QDataStream st(name);
 
         QString country;
         QString state;
         QString city;
         st >> country >> state >> city;
 
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND state = ? AND files.location = locations.id");
         query.addBindValue(country);
         query.addBindValue(state);
     }
 
     if (!query.exec()) {
         qDebug() << "imagesForLocation: " << loc << query.lastError();
         return QStringList();
     }
 
     QStringList files;
     while (query.next()) {
         files << QString("file://" + query.value(0).toString());
     }
     return files;
 }
 
 QString ImageStorage::imageForLocation(const QByteArray &name, Types::LocationGroup loc)
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
     if (loc == Types::LocationGroup::Country) {
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND files.location = locations.id");
         query.addBindValue(QString::fromUtf8(name));
     } else if (loc == Types::LocationGroup::State) {
         QDataStream st(name);
 
         QString country;
         QString state;
         st >> country >> state;
 
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND state = ? AND files.location = locations.id");
         query.addBindValue(country);
         query.addBindValue(state);
     } else if (loc == Types::LocationGroup::City) {
         QDataStream st(name);
 
         QString country;
         QString state;
         QString city;
         st >> country >> state >> city;
 
         query.prepare("SELECT DISTINCT url from files, locations where country = ? AND state = ? AND files.location = locations.id");
         query.addBindValue(country);
         query.addBindValue(state);
     }
 
     if (!query.exec()) {
         qDebug() << "imageForLocation: " << loc << query.lastError();
         return QString();
     }
 
     if (query.next()) {
         return QString("file://" + query.value(0).toString());
     }
     return QString();
 }
 
 QList<QPair<QByteArray, QString>> ImageStorage::timeTypes(Types::TimeGroup group)
 {
     QMutexLocker lock(&m_mutex);
     QList<QPair<QByteArray, QString>> list;
 
     QSqlQuery query;
     if (group == Types::TimeGroup::Year) {
         query.prepare("SELECT DISTINCT strftime('%Y', dateTime) from files");
         if (!query.exec()) {
             qDebug() << group << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString val = query.value(0).toString();
             list << qMakePair(val.toUtf8(), val);
         }
         return list;
     } else if (group == Types::TimeGroup::Month) {
         query.prepare("SELECT DISTINCT strftime('%Y', dateTime), strftime('%m', dateTime) from files");
         if (!query.exec()) {
             qDebug() << group << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString year = query.value(0).toString();
             QString month = query.value(1).toString();
 
             QString display = QLocale().monthName(month.toInt(), QLocale::LongFormat) + ", " + year;
 
             QByteArray key;
             QDataStream stream(&key, QIODevice::WriteOnly);
             stream << year << month;
 
             list << qMakePair(key, display);
         }
         return list;
     } else if (group == Types::TimeGroup::Week) {
         query.prepare("SELECT DISTINCT strftime('%Y', dateTime), strftime('%m', dateTime), strftime('%W', dateTime) from files");
         if (!query.exec()) {
             qDebug() << group << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QString year = query.value(0).toString();
             QString month = query.value(1).toString();
             QString week = query.value(2).toString();
 
             QString display = "Week " + week + ", " + QLocale().monthName(month.toInt(), QLocale::LongFormat) + ", " + year;
 
             QByteArray key;
             QDataStream stream(&key, QIODevice::WriteOnly);
             stream << year << week;
 
             list << qMakePair(key, display);
         }
         return list;
     } else if (group == Types::TimeGroup::Day) {
         query.prepare("SELECT DISTINCT date(dateTime) from files");
         if (!query.exec()) {
             qDebug() << group << query.lastError();
             return list;
         }
 
         while (query.next()) {
             QDate date = query.value(0).toDate();
 
             QString display = QLocale::system().toString(date, QLocale::LongFormat);
             QByteArray key = date.toString(Qt::ISODate).toUtf8();
 
             list << qMakePair(key, display);
         }
         return list;
     }
 
     Q_ASSERT(0);
     return list;
 }
 
 QStringList ImageStorage::imagesForTime(const QByteArray &name, Types::TimeGroup group)
 {
     QMutexLocker lock(&m_mutex);
     QSqlQuery query;
     if (group == Types::TimeGroup::Year) {
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ?");
         query.addBindValue(QString::fromUtf8(name));
     } else if (group == Types::TimeGroup::Month) {
         QDataStream stream(name);
         QString year;
         QString month;
         stream >> year >> month;
 
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ? AND strftime('%m', dateTime) = ?");
         query.addBindValue(year);
         query.addBindValue(month);
     } else if (group == Types::TimeGroup::Week) {
         QDataStream stream(name);
         QString year;
         QString week;
         stream >> year >> week;
 
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ? AND strftime('%W', dateTime) = ?");
         query.addBindValue(year);
         query.addBindValue(week);
     } else if (group == Types::TimeGroup::Day) {
         QDate date = QDate::fromString(QString::fromUtf8(name), Qt::ISODate);
 
         query.prepare("SELECT DISTINCT url from files where date(dateTime) = ?");
         query.addBindValue(date);
     }
 
     if (!query.exec()) {
         qDebug() << group << query.lastError();
         return QStringList();
     }
 
     QStringList files;
     while (query.next()) {
         files << QString("file://" + query.value(0).toString());
     }
 
     return files;
 }
 
 QString ImageStorage::imageForTime(const QByteArray &name, Types::TimeGroup group)
 {
     QMutexLocker lock(&m_mutex);
     Q_ASSERT(!name.isEmpty());
 
     QSqlQuery query;
     if (group == Types::TimeGroup::Year) {
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ? LIMIT 1");
         query.addBindValue(QString::fromUtf8(name));
     } else if (group == Types::TimeGroup::Month) {
         QDataStream stream(name);
         QString year;
         QString month;
         stream >> year >> month;
 
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ? AND strftime('%m', dateTime) = ? LIMIT 1");
         query.addBindValue(year);
         query.addBindValue(month);
     } else if (group == Types::TimeGroup::Week) {
         QDataStream stream(name);
         QString year;
         QString week;
         stream >> year >> week;
 
         query.prepare("SELECT DISTINCT url from files where strftime('%Y', dateTime) = ? AND strftime('%W', dateTime) = ? LIMIT 1");
         query.addBindValue(year);
         query.addBindValue(week);
     } else if (group == Types::TimeGroup::Day) {
         QDate date = QDate::fromString(QString::fromUtf8(name), Qt::ISODate);
 
         query.prepare("SELECT DISTINCT url from files where date(dateTime) = ? LIMIT 1");
         query.addBindValue(date);
     }
 
     if (!query.exec()) {
         qDebug() << group << query.lastError();
         return QString();
     }
 
     if (query.next()) {
         return QString("file://" + query.value(0).toString());
     }
 
     Q_ASSERT(0);
     return QString();
 }
 
 QDate ImageStorage::dateForKey(const QByteArray &key, Types::TimeGroup group)
 {
     if (group == Types::TimeGroup::Year) {
         return QDate(key.toInt(), 1, 1);
     } else if (group == Types::TimeGroup::Month) {
         QDataStream stream(key);
         QString year;
         QString month;
         stream >> year >> month;
 
         return QDate(year.toInt(), month.toInt(), 1);
     } else if (group == Types::TimeGroup::Week) {
         QDataStream stream(key);
         QString year;
         QString week;
         stream >> year >> week;
 
         int month = week.toInt() / 4;
         int day = week.toInt() % 4;
         return QDate(year.toInt(), month, day);
     } else if (group == Types::TimeGroup::Day) {
         return QDate::fromString(QString::fromUtf8(key), Qt::ISODate);
     }
 
     Q_ASSERT(0);
     return QDate();
 }
 
 void ImageStorage::reset()
 {
     qDebug() << "Resetting database";
     QString dir = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation) + "/koko";
     QDir(dir).removeRecursively();
 }
 
 bool ImageStorage::shouldReset()
 {
     bool shouldReset = false;
     {
         QString dir = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation) + "/koko";
         QDir().mkpath(dir);
 
         QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), QStringLiteral("resetChecker"));
         db.setDatabaseName(dir + "/imageData.sqlite3");
 
         if (!db.open()) {
             qDebug() << "Failed to open db" << db.lastError().text();
             shouldReset = true;
         } else if (db.tables().contains("files") == true && db.tables().contains("tags") == false) {
             shouldReset = true;
         }
         db.close();
     }
     QSqlDatabase::removeDatabase(QStringLiteral("resetChecker"));
     return shouldReset;
 }
 
 QStringList ImageStorage::allImages(int size, int offset)
 {
     QMutexLocker lock(&m_mutex);
 
     QSqlQuery query;
     if (size == -1) {
         query.prepare("SELECT DISTINCT url from files ORDER BY dateTime DESC");
     } else {
         query.prepare("SELECT DISTINCT url from files ORDER BY dateTime DESC LIMIT ? OFFSET ?");
         query.addBindValue(size);
         query.addBindValue(offset);
     }
 
     if (!query.exec()) {
         qDebug() << query.lastError();
         return QStringList();
     }
 
     QStringList imageList;
     while (query.next())
         imageList << query.value(0).toString();
 
     return imageList;
 }