File indexing completed on 2024-05-12 16:23:38
0001 /** 0002 * SPDX-FileCopyrightText: 2020 Tobias Fella <fella@posteo.de> 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 if (q.prepare(query)) { 0094 return execute(q); 0095 } else { 0096 return false; 0097 } 0098 } 0099 0100 bool Database::execute(QSqlQuery &query) 0101 { 0102 if (!query.exec()) { 0103 qWarning() << "Failed to execute SQL Query"; 0104 qWarning() << query.lastQuery(); 0105 qWarning() << query.lastError(); 0106 return false; 0107 } 0108 return true; 0109 } 0110 0111 int Database::version() 0112 { 0113 QSqlQuery query; 0114 if (query.prepare(QStringLiteral("PRAGMA user_version;"))) { 0115 execute(query); 0116 if (query.next()) { 0117 bool ok; 0118 int value = query.value(0).toInt(&ok); 0119 qDebug() << "Database version " << value; 0120 if (ok) { 0121 return value; 0122 } 0123 } 0124 } 0125 qCritical() << "Failed to check database version"; 0126 return -1; 0127 } 0128 0129 void Database::cleanup() 0130 { 0131 AlligatorSettings settings; 0132 int count = settings.deleteAfterCount(); 0133 int type = settings.deleteAfterType(); 0134 0135 if (type == 0) { // Never delete Entries 0136 return; 0137 } 0138 0139 if (type == 1) { // Delete after <count> posts per feed 0140 // TODO 0141 } else { 0142 QDateTime dateTime = QDateTime::currentDateTime(); 0143 if (type == 2) { 0144 dateTime = dateTime.addDays(-count); 0145 } else if (type == 3) { 0146 dateTime = dateTime.addDays(-7 * count); 0147 } else if (type == 4) { 0148 dateTime = dateTime.addMonths(-count); 0149 } 0150 qint64 sinceEpoch = dateTime.toSecsSinceEpoch(); 0151 0152 QSqlQuery query; 0153 if (query.prepare(QStringLiteral("DELETE FROM Entries WHERE updated < :sinceEpoch;"))) { 0154 query.bindValue(QStringLiteral(":sinceEpoch"), sinceEpoch); 0155 execute(query); 0156 } 0157 } 0158 } 0159 0160 bool Database::feedExists(const QString &url) 0161 { 0162 QSqlQuery query; 0163 if (query.prepare(QStringLiteral("SELECT COUNT (url) FROM Feeds WHERE url=:url;"))) { 0164 query.bindValue(QStringLiteral(":url"), url); 0165 Database::instance().execute(query); 0166 query.next(); 0167 return query.value(0).toInt() != 0; 0168 } else { 0169 return false; 0170 } 0171 } 0172 0173 void Database::addFeed(const QString &url, const QString &groupName, const bool markEntriesRead) 0174 { 0175 qDebug() << "Adding feed"; 0176 if (feedExists(url)) { 0177 qDebug() << "Feed already exists"; 0178 return; 0179 } 0180 qDebug() << "Feed does not yet exist"; 0181 0182 QUrl urlFromInput = QUrl::fromUserInput(url); 0183 QSqlQuery query; 0184 if (query.prepare(QStringLiteral("INSERT INTO Feeds VALUES (:name, :url, :image, :link, :description, :deleteAfterCount, :deleteAfterType, :subscribed, " 0185 ":lastUpdated, :notify, :groupName, :displayName);"))) { 0186 query.bindValue(QStringLiteral(":name"), urlFromInput.toString()); 0187 query.bindValue(QStringLiteral(":url"), urlFromInput.toString()); 0188 query.bindValue(QStringLiteral(":image"), QLatin1String("")); 0189 query.bindValue(QStringLiteral(":link"), QLatin1String("")); 0190 query.bindValue(QStringLiteral(":description"), QLatin1String("")); 0191 query.bindValue(QStringLiteral(":deleteAfterCount"), 0); 0192 query.bindValue(QStringLiteral(":deleteAfterType"), 0); 0193 query.bindValue(QStringLiteral(":subscribed"), QDateTime::currentDateTime().toSecsSinceEpoch()); 0194 query.bindValue(QStringLiteral(":lastUpdated"), 0); 0195 query.bindValue(QStringLiteral(":notify"), false); 0196 query.bindValue(QStringLiteral(":groupName"), groupName.isEmpty() ? defaultGroup() : groupName); 0197 query.bindValue(QStringLiteral(":displayName"), QLatin1String("")); 0198 execute(query); 0199 Q_EMIT feedAdded(urlFromInput.toString()); 0200 Fetcher::instance().fetch(urlFromInput.toString(), markEntriesRead); 0201 } 0202 } 0203 0204 void Database::importFeeds(const QString &path) 0205 { 0206 QUrl url(path); 0207 QFile file(url.isLocalFile() ? url.toLocalFile() : url.toString()); 0208 file.open(QIODevice::ReadOnly); 0209 0210 QXmlStreamReader xmlReader(&file); 0211 while (!xmlReader.atEnd()) { 0212 xmlReader.readNext(); 0213 if (xmlReader.tokenType() == 4 && xmlReader.attributes().hasAttribute(QStringLiteral("xmlUrl"))) { 0214 addFeed(xmlReader.attributes().value(QStringLiteral("xmlUrl")).toString()); 0215 } 0216 } 0217 Fetcher::instance().fetchAll(); 0218 } 0219 0220 void Database::exportFeeds(const QString &path) 0221 { 0222 QUrl url(path); 0223 QFile file(url.isLocalFile() ? url.toLocalFile() : url.toString()); 0224 file.open(QIODevice::WriteOnly); 0225 0226 QXmlStreamWriter xmlWriter(&file); 0227 xmlWriter.setAutoFormatting(true); 0228 xmlWriter.writeStartDocument(QStringLiteral("1.0")); 0229 xmlWriter.writeStartElement(QStringLiteral("opml")); 0230 xmlWriter.writeEmptyElement(QStringLiteral("head")); 0231 xmlWriter.writeStartElement(QStringLiteral("body")); 0232 xmlWriter.writeAttribute(QStringLiteral("version"), QStringLiteral("1.0")); 0233 QSqlQuery query; 0234 if (query.prepare(QStringLiteral("SELECT url, name FROM Feeds;"))) { 0235 execute(query); 0236 while (query.next()) { 0237 xmlWriter.writeEmptyElement(QStringLiteral("outline")); 0238 xmlWriter.writeAttribute(QStringLiteral("xmlUrl"), query.value(0).toString()); 0239 xmlWriter.writeAttribute(QStringLiteral("title"), query.value(1).toString()); 0240 } 0241 xmlWriter.writeEndElement(); 0242 xmlWriter.writeEndElement(); 0243 xmlWriter.writeEndDocument(); 0244 } 0245 } 0246 0247 void Database::addFeedGroup(const QString &name, const QString &description, const int isDefault) 0248 { 0249 if (feedGroupExists(name)) { 0250 qDebug() << "Feed group already exists, nothing to add"; 0251 return; 0252 } 0253 0254 QSqlQuery query; 0255 if (query.prepare(QStringLiteral("INSERT INTO FeedGroups VALUES (:name, :desc, :isDefault);"))) { 0256 query.bindValue(QStringLiteral(":name"), name); 0257 query.bindValue(QStringLiteral(":desc"), description); 0258 query.bindValue(QStringLiteral(":isDefault"), isDefault); 0259 execute(query); 0260 0261 Q_EMIT feedGroupsUpdated(); 0262 } 0263 } 0264 0265 void Database::editFeed(const QString &url, const QString &displayName, const QString &groupName) 0266 { 0267 QSqlQuery query; 0268 if (query.prepare(QStringLiteral("UPDATE Feeds SET displayName = :displayName, groupName = :groupName WHERE url = :url;"))) { 0269 query.bindValue(QStringLiteral(":displayName"), displayName); 0270 query.bindValue(QStringLiteral(":groupName"), groupName); 0271 query.bindValue(QStringLiteral(":url"), url); 0272 execute(query); 0273 0274 Q_EMIT feedDetailsUpdated(url, displayName, groupName); 0275 } 0276 } 0277 0278 void Database::removeFeedGroup(const QString &name) 0279 { 0280 clearFeedGroup(name); 0281 0282 QSqlQuery query; 0283 if (query.prepare(QStringLiteral("DELETE FROM FeedGroups WHERE name = :name;"))) { 0284 query.bindValue(QStringLiteral(":name"), name); 0285 execute(query); 0286 0287 Q_EMIT feedGroupRemoved(name); 0288 } 0289 } 0290 0291 void Database::setRead(const QString &entryId, bool read) 0292 { 0293 QSqlQuery query; 0294 if (query.prepare(QStringLiteral("UPDATE Entries SET read=:read WHERE id=:id"))) { 0295 query.bindValue(QStringLiteral(":id"), entryId); 0296 query.bindValue(QStringLiteral(":read"), read); 0297 execute(query); 0298 0299 Q_EMIT entryReadChanged(entryId, read); 0300 } 0301 } 0302 0303 bool Database::feedGroupExists(const QString &name) 0304 { 0305 QSqlQuery query; 0306 if (query.prepare(QStringLiteral("SELECT COUNT (1) FROM FeedGroups WHERE name = :name;"))) { 0307 query.bindValue(QStringLiteral(":name"), name); 0308 Database::instance().execute(query); 0309 query.next(); 0310 return (query.value(0).toInt() != 0); 0311 } else { 0312 return false; 0313 } 0314 } 0315 0316 void Database::clearFeedGroup(const QString &name) 0317 { 0318 QSqlQuery query; 0319 if (query.prepare(QStringLiteral("UPDATE Feeds SET groupName = NULL WHERE groupName = :name;"))) { 0320 query.bindValue(QStringLiteral(":name"), name); 0321 execute(query); 0322 } 0323 } 0324 0325 QString Database::defaultGroup() 0326 { 0327 QSqlQuery query; 0328 if (query.prepare(QStringLiteral("SELECT Name FROM FeedGroups WHERE defaultGroup = 1"))) { 0329 execute(query); 0330 0331 if (query.next()) { 0332 return query.value(0).toString(); 0333 } 0334 } 0335 auto dg = i18n("Default"); 0336 addFeedGroup(dg, i18n("Default Feed Group"), 1); 0337 return dg; 0338 } 0339 0340 void Database::setDefaultGroup(const QString &name) 0341 { 0342 if (execute(QStringLiteral("UPDATE FeedGroups SET defaultGroup = 0;"))) { 0343 QSqlQuery query; 0344 if (query.prepare(QStringLiteral("UPDATE FeedGroups SET defaultGroup = 1 WHERE name = :name ;"))) { 0345 query.bindValue(QStringLiteral(":name"), name); 0346 execute(query); 0347 0348 Q_EMIT feedGroupsUpdated(); 0349 } 0350 } 0351 }