File indexing completed on 2025-07-13 04:30:27
0001 /** 0002 * SPDX-FileCopyrightText: 2020 Tobias Fella <tobias.fella@kde.org> 0003 * 0004 * SPDX-License-Identifier: GPL-2.0-only OR GPL-3.0-only OR LicenseRef-KDE-Accepted-GPL 0005 */ 0006 0007 #include <KLocalizedString> 0008 #include <QDateTime> 0009 #include <QDir> 0010 #include <QSqlDatabase> 0011 #include <QSqlError> 0012 #include <QStandardPaths> 0013 #include <QUrl> 0014 #include <QXmlStreamReader> 0015 #include <QXmlStreamWriter> 0016 0017 #include "alligatorsettings.h" 0018 #include "database.h" 0019 #include "fetcher.h" 0020 0021 #define TRUE_OR_RETURN(x) \ 0022 if (!x) \ 0023 return false; 0024 0025 Database::Database() 0026 { 0027 QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE")); 0028 QString databasePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation); 0029 QDir(databasePath).mkpath(databasePath); 0030 db.setDatabaseName(databasePath + QStringLiteral("/database.db3")); 0031 if (!db.open()) { 0032 qCritical() << "Failed to open the database"; 0033 } 0034 0035 if (!migrateTo(2)) { 0036 qCritical() << "Failed to migrate the database"; 0037 } 0038 0039 cleanup(); 0040 } 0041 0042 bool Database::migrateTo(const int targetVersion) 0043 { 0044 if (version() >= targetVersion) { 0045 qDebug() << "Database already in version" << targetVersion; 0046 return true; 0047 } 0048 0049 switch (targetVersion) { 0050 case 1: 0051 return migrateTo1(); 0052 case 2: 0053 return migrateTo2(); 0054 default: 0055 return true; 0056 } 0057 } 0058 0059 bool Database::migrateTo2() 0060 { 0061 migrateTo(1); 0062 0063 qDebug() << "Migrating database to version 2"; 0064 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS FeedGroups (name TEXT NOT NULL, description TEXT, defaultGroup INTEGER);"))); 0065 TRUE_OR_RETURN(execute(QStringLiteral("ALTER TABLE Feeds ADD COLUMN groupName TEXT;"))); 0066 TRUE_OR_RETURN(execute(QStringLiteral("ALTER TABLE Feeds ADD COLUMN displayName TEXT;"))); 0067 auto dg = i18n("Default"); 0068 TRUE_OR_RETURN(execute(QStringLiteral("INSERT INTO FeedGroups VALUES ('%1', '%2', 1);").arg(dg, i18n("Default Feed Group")))); 0069 TRUE_OR_RETURN(execute(QStringLiteral("UPDATE Feeds SET groupName = '%1';").arg(dg))); 0070 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 2;"))); 0071 0072 return true; 0073 } 0074 0075 bool Database::migrateTo1() 0076 { 0077 qDebug() << "Migrating database to version 1"; 0078 TRUE_OR_RETURN( 0079 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Feeds (name TEXT, url TEXT, image TEXT, link TEXT, description TEXT, deleteAfterCount INTEGER, " 0080 "deleteAfterType INTEGER, subscribed INTEGER, lastUpdated INTEGER, notify BOOL);"))); 0081 TRUE_OR_RETURN(execute(QStringLiteral( 0082 "CREATE TABLE IF NOT EXISTS Entries (feed TEXT, id TEXT UNIQUE, title TEXT, content TEXT, created INTEGER, updated INTEGER, link TEXT, read bool);"))); 0083 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Authors (feed TEXT, id TEXT, name TEXT, uri TEXT, email TEXT);"))); 0084 TRUE_OR_RETURN(execute( 0085 QStringLiteral("CREATE TABLE IF NOT EXISTS Enclosures (feed TEXT, id TEXT, duration INTEGER, size INTEGER, title TEXT, type STRING, url STRING);"))); 0086 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 1;"))); 0087 return true; 0088 } 0089 0090 bool Database::execute(const QString &query) 0091 { 0092 QSqlQuery q; 0093 q.prepare(query); 0094 return execute(q); 0095 } 0096 0097 bool Database::execute(QSqlQuery &query) 0098 { 0099 if (!query.exec()) { 0100 qWarning() << "Failed to execute SQL Query"; 0101 qWarning() << query.lastQuery(); 0102 qWarning() << query.lastError(); 0103 return false; 0104 } 0105 return true; 0106 } 0107 0108 int Database::version() 0109 { 0110 QSqlQuery query; 0111 query.prepare(QStringLiteral("PRAGMA user_version;")); 0112 execute(query); 0113 if (!query.next()) { 0114 return -1; 0115 } 0116 int value = query.value(0).toInt(); 0117 qDebug() << "Database version " << value; 0118 return value; 0119 } 0120 0121 void Database::cleanup() 0122 { 0123 AlligatorSettings settings; 0124 int count = settings.deleteAfterCount(); 0125 int type = settings.deleteAfterType(); 0126 0127 if (type == 0) { // Never delete Entries 0128 return; 0129 } 0130 0131 if (type == 1) { // Delete after <count> posts per feed 0132 // TODO 0133 } else { 0134 QDateTime dateTime = QDateTime::currentDateTime(); 0135 if (type == 2) { 0136 dateTime = dateTime.addDays(-count); 0137 } else if (type == 3) { 0138 dateTime = dateTime.addDays(-7 * count); 0139 } else if (type == 4) { 0140 dateTime = dateTime.addMonths(-count); 0141 } 0142 qint64 sinceEpoch = dateTime.toSecsSinceEpoch(); 0143 0144 QSqlQuery query; 0145 query.prepare(QStringLiteral("DELETE FROM Entries WHERE updated < :sinceEpoch;")); 0146 query.bindValue(QStringLiteral(":sinceEpoch"), sinceEpoch); 0147 execute(query); 0148 } 0149 } 0150 0151 bool Database::feedExists(const QString &url) 0152 { 0153 QSqlQuery query; 0154 query.prepare(QStringLiteral("SELECT COUNT (url) FROM Feeds WHERE url=:url;")); 0155 query.bindValue(QStringLiteral(":url"), url); 0156 Database::instance().execute(query); 0157 query.next(); 0158 return query.value(0).toInt() != 0; 0159 } 0160 0161 void Database::addFeed(const QString &url, const QString &groupName, const bool markEntriesRead) 0162 { 0163 qDebug() << "Adding feed"; 0164 if (feedExists(url)) { 0165 qDebug() << "Feed already exists"; 0166 return; 0167 } 0168 qDebug() << "Feed does not yet exist"; 0169 0170 QUrl urlFromInput = QUrl::fromUserInput(url); 0171 QSqlQuery query; 0172 query.prepare( 0173 QStringLiteral("INSERT INTO Feeds VALUES (:name, :url, :image, :link, :description, :deleteAfterCount, :deleteAfterType, :subscribed, " 0174 ":lastUpdated, :notify, :groupName, :displayName);")); 0175 query.bindValue(QStringLiteral(":name"), urlFromInput.toString()); 0176 query.bindValue(QStringLiteral(":url"), urlFromInput.toString()); 0177 query.bindValue(QStringLiteral(":image"), QLatin1String("")); 0178 query.bindValue(QStringLiteral(":link"), QLatin1String("")); 0179 query.bindValue(QStringLiteral(":description"), QLatin1String("")); 0180 query.bindValue(QStringLiteral(":deleteAfterCount"), 0); 0181 query.bindValue(QStringLiteral(":deleteAfterType"), 0); 0182 query.bindValue(QStringLiteral(":subscribed"), QDateTime::currentDateTime().toSecsSinceEpoch()); 0183 query.bindValue(QStringLiteral(":lastUpdated"), 0); 0184 query.bindValue(QStringLiteral(":notify"), false); 0185 query.bindValue(QStringLiteral(":groupName"), groupName.isEmpty() ? defaultGroup() : groupName); 0186 query.bindValue(QStringLiteral(":displayName"), QLatin1String("")); 0187 execute(query); 0188 Q_EMIT feedAdded(urlFromInput.toString()); 0189 Fetcher::instance().fetch(urlFromInput.toString(), markEntriesRead); 0190 } 0191 0192 void Database::importFeeds(const QString &path) 0193 { 0194 QUrl url(path); 0195 QFile file(url.isLocalFile() ? url.toLocalFile() : url.toString()); 0196 file.open(QIODevice::ReadOnly); 0197 0198 QXmlStreamReader xmlReader(&file); 0199 while (!xmlReader.atEnd()) { 0200 xmlReader.readNext(); 0201 if (xmlReader.tokenType() == 4 && xmlReader.attributes().hasAttribute(QStringLiteral("xmlUrl"))) { 0202 addFeed(xmlReader.attributes().value(QStringLiteral("xmlUrl")).toString()); 0203 } 0204 } 0205 Fetcher::instance().fetchAll(); 0206 } 0207 0208 void Database::exportFeeds(const QString &path) 0209 { 0210 QUrl url(path); 0211 QFile file(url.isLocalFile() ? url.toLocalFile() : url.toString()); 0212 file.open(QIODevice::WriteOnly); 0213 0214 QXmlStreamWriter xmlWriter(&file); 0215 xmlWriter.setAutoFormatting(true); 0216 xmlWriter.writeStartDocument(QStringLiteral("1.0")); 0217 xmlWriter.writeStartElement(QStringLiteral("opml")); 0218 xmlWriter.writeEmptyElement(QStringLiteral("head")); 0219 xmlWriter.writeStartElement(QStringLiteral("body")); 0220 xmlWriter.writeAttribute(QStringLiteral("version"), QStringLiteral("1.0")); 0221 QSqlQuery query; 0222 query.prepare(QStringLiteral("SELECT url, name FROM Feeds;")); 0223 execute(query); 0224 while (query.next()) { 0225 xmlWriter.writeEmptyElement(QStringLiteral("outline")); 0226 xmlWriter.writeAttribute(QStringLiteral("xmlUrl"), query.value(0).toString()); 0227 xmlWriter.writeAttribute(QStringLiteral("title"), query.value(1).toString()); 0228 } 0229 xmlWriter.writeEndElement(); 0230 xmlWriter.writeEndElement(); 0231 xmlWriter.writeEndDocument(); 0232 } 0233 0234 void Database::addFeedGroup(const QString &name, const QString &description, const int isDefault) 0235 { 0236 if (feedGroupExists(name)) { 0237 qDebug() << "Feed group already exists, nothing to add"; 0238 return; 0239 } 0240 0241 QSqlQuery query; 0242 query.prepare(QStringLiteral("INSERT INTO FeedGroups VALUES (:name, :desc, :isDefault);")); 0243 query.bindValue(QStringLiteral(":name"), name); 0244 query.bindValue(QStringLiteral(":desc"), description); 0245 query.bindValue(QStringLiteral(":isDefault"), isDefault); 0246 execute(query); 0247 0248 Q_EMIT feedGroupsUpdated(); 0249 } 0250 0251 void Database::editFeed(const QString &url, const QString &displayName, const QString &groupName) 0252 { 0253 QSqlQuery query; 0254 query.prepare(QStringLiteral("UPDATE Feeds SET displayName = :displayName, groupName = :groupName WHERE url = :url;")); 0255 query.bindValue(QStringLiteral(":displayName"), displayName); 0256 query.bindValue(QStringLiteral(":groupName"), groupName); 0257 query.bindValue(QStringLiteral(":url"), url); 0258 execute(query); 0259 0260 Q_EMIT feedDetailsUpdated(url, displayName, groupName); 0261 } 0262 0263 void Database::removeFeedGroup(const QString &name) 0264 { 0265 clearFeedGroup(name); 0266 0267 QSqlQuery query; 0268 query.prepare(QStringLiteral("DELETE FROM FeedGroups WHERE name = :name;")); 0269 query.bindValue(QStringLiteral(":name"), name); 0270 execute(query); 0271 0272 Q_EMIT feedGroupRemoved(name); 0273 } 0274 0275 void Database::setRead(const QString &entryId, bool read) 0276 { 0277 QSqlQuery query; 0278 query.prepare(QStringLiteral("UPDATE Entries SET read=:read WHERE id=:id")); 0279 query.bindValue(QStringLiteral(":id"), entryId); 0280 query.bindValue(QStringLiteral(":read"), read); 0281 execute(query); 0282 0283 Q_EMIT entryReadChanged(entryId, read); 0284 } 0285 0286 bool Database::feedGroupExists(const QString &name) 0287 { 0288 QSqlQuery query; 0289 query.prepare(QStringLiteral("SELECT COUNT (1) FROM FeedGroups WHERE name = :name;")); 0290 query.bindValue(QStringLiteral(":name"), name); 0291 Database::instance().execute(query); 0292 query.next(); 0293 return query.value(0).toInt() != 0; 0294 } 0295 0296 void Database::clearFeedGroup(const QString &name) 0297 { 0298 QSqlQuery query; 0299 query.prepare(QStringLiteral("UPDATE Feeds SET groupName = NULL WHERE groupName = :name;")); 0300 query.bindValue(QStringLiteral(":name"), name); 0301 execute(query); 0302 } 0303 0304 QString Database::defaultGroup() 0305 { 0306 QSqlQuery query; 0307 query.prepare(QStringLiteral("SELECT Name FROM FeedGroups WHERE defaultGroup = 1")); 0308 execute(query); 0309 0310 if (query.next()) { 0311 return query.value(0).toString(); 0312 } 0313 auto dg = i18n("Default"); 0314 addFeedGroup(dg, i18n("Default Feed Group"), 1); 0315 return dg; 0316 } 0317 0318 void Database::setDefaultGroup(const QString &name) 0319 { 0320 execute(QStringLiteral("UPDATE FeedGroups SET defaultGroup = 0;")); 0321 QSqlQuery query; 0322 query.prepare(QStringLiteral("UPDATE FeedGroups SET defaultGroup = 1 WHERE name=:name;")); 0323 query.bindValue(QStringLiteral(":name"), name); 0324 execute(query); 0325 Q_EMIT feedGroupsUpdated(); 0326 }