File indexing completed on 2024-05-12 16:21:27
0001 /** 0002 * SPDX-FileCopyrightText: 2020 Tobias Fella <tobias.fella@kde.org> 0003 * SPDX-FileCopyrightText: 2021-2022 Bart De Vries <bart@mogwai.be> 0004 * 0005 * SPDX-License-Identifier: GPL-2.0-only OR GPL-3.0-only OR LicenseRef-KDE-Accepted-GPL 0006 */ 0007 0008 #include "database.h" 0009 0010 #include <QCryptographicHash> 0011 #include <QDateTime> 0012 #include <QDebug> 0013 #include <QDir> 0014 #include <QFile> 0015 #include <QFileInfo> 0016 #include <QRegularExpression> 0017 #include <QSqlDatabase> 0018 #include <QSqlError> 0019 #include <QStandardPaths> 0020 #include <QUrl> 0021 0022 #include "settingsmanager.h" 0023 0024 #define TRUE_OR_RETURN(x) \ 0025 if (!x) \ 0026 return false; 0027 0028 Database::Database() 0029 { 0030 Database::openDatabase(); 0031 0032 if (!migrate()) { 0033 qCritical() << "Failed to migrate the database"; 0034 } 0035 0036 cleanup(); 0037 } 0038 0039 void Database::openDatabase(const QString &connectionName) 0040 { 0041 QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), connectionName); 0042 QString databasePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation); 0043 QDir(databasePath).mkpath(databasePath); 0044 db.setDatabaseName(databasePath + QStringLiteral("/") + m_dbName); 0045 db.open(); 0046 } 0047 0048 void Database::closeDatabase(const QString &connectionName) 0049 { 0050 QSqlDatabase::database(connectionName).close(); 0051 QSqlDatabase::removeDatabase(connectionName); 0052 } 0053 0054 bool Database::migrate() 0055 { 0056 setWalMode(); 0057 0058 int dbversion = version(); 0059 if (dbversion < 1) 0060 TRUE_OR_RETURN(migrateTo1()); 0061 if (dbversion < 2) 0062 TRUE_OR_RETURN(migrateTo2()); 0063 if (dbversion < 3) 0064 TRUE_OR_RETURN(migrateTo3()); 0065 if (dbversion < 4) 0066 TRUE_OR_RETURN(migrateTo4()); 0067 if (dbversion < 5) 0068 TRUE_OR_RETURN(migrateTo5()); 0069 if (dbversion < 6) 0070 TRUE_OR_RETURN(migrateTo6()); 0071 if (dbversion < 7) 0072 TRUE_OR_RETURN(migrateTo7()); 0073 if (dbversion < 8) 0074 TRUE_OR_RETURN(migrateTo8()); 0075 return true; 0076 } 0077 0078 bool Database::migrateTo1() 0079 { 0080 qDebug() << "Migrating database to version 1"; 0081 TRUE_OR_RETURN(transaction()); 0082 TRUE_OR_RETURN( 0083 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Feeds (name TEXT, url TEXT, image TEXT, link TEXT, description TEXT, deleteAfterCount INTEGER, " 0084 "deleteAfterType INTEGER, subscribed INTEGER, lastUpdated INTEGER, new BOOL, notify BOOL);"))); 0085 TRUE_OR_RETURN( 0086 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Entries (feed TEXT, id TEXT UNIQUE, title TEXT, content TEXT, created INTEGER, updated INTEGER, " 0087 "link TEXT, read bool, new bool, hasEnclosure BOOL, image TEXT);"))); 0088 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Authors (feed TEXT, id TEXT, name TEXT, uri TEXT, email TEXT);"))); 0089 TRUE_OR_RETURN( 0090 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Enclosures (feed TEXT, id TEXT, duration INTEGER, size INTEGER, title TEXT, type TEXT, url TEXT, " 0091 "playposition INTEGER, downloaded BOOL);"))); 0092 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Queue (listnr INTEGER, feed TEXT, id TEXT, playing BOOL);"))); 0093 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Errors (url TEXT, id TEXT, code INTEGER, message TEXT, date INTEGER);"))); 0094 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 1;"))); 0095 TRUE_OR_RETURN(commit()); 0096 return true; 0097 } 0098 0099 bool Database::migrateTo2() 0100 { 0101 qDebug() << "Migrating database to version 2"; 0102 TRUE_OR_RETURN(transaction()); 0103 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE Errors;"))); 0104 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Errors (type INTEGER, url TEXT, id TEXT, code INTEGER, message TEXT, date INTEGER);"))); 0105 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 2;"))); 0106 TRUE_OR_RETURN(commit()); 0107 return true; 0108 } 0109 0110 bool Database::migrateTo3() 0111 { 0112 qDebug() << "Migrating database to version 3"; 0113 TRUE_OR_RETURN(transaction()); 0114 TRUE_OR_RETURN( 0115 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Enclosurestemp (feed TEXT, id TEXT, duration INTEGER, size INTEGER, title TEXT, type TEXT, url " 0116 "TEXT, playposition INTEGER, downloaded INTEGER);"))); 0117 TRUE_OR_RETURN( 0118 execute(QStringLiteral("INSERT INTO Enclosurestemp (feed, id, duration, size, title, type, url, playposition, downloaded) SELECT feed, id, duration, " 0119 "size, title, type, url, playposition, downloaded FROM Enclosures;"))); 0120 TRUE_OR_RETURN(execute(QStringLiteral("UPDATE Enclosurestemp SET downloaded=3 WHERE downloaded=1;"))); 0121 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE Enclosures;"))); 0122 TRUE_OR_RETURN(execute(QStringLiteral("ALTER TABLE Enclosurestemp RENAME TO Enclosures;"))); 0123 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 3;"))); 0124 TRUE_OR_RETURN(commit()); 0125 return true; 0126 } 0127 0128 bool Database::migrateTo4() 0129 { 0130 qDebug() << "Migrating database to version 4"; 0131 TRUE_OR_RETURN(transaction()); 0132 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE Errors;"))); 0133 TRUE_OR_RETURN( 0134 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Errors (type INTEGER, url TEXT, id TEXT, code INTEGER, message TEXT, date INTEGER, title TEXT);"))); 0135 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 4;"))); 0136 TRUE_OR_RETURN(commit()); 0137 return true; 0138 } 0139 0140 bool Database::migrateTo5() 0141 { 0142 qDebug() << "Migrating database to version 5"; 0143 TRUE_OR_RETURN(transaction()); 0144 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Chapters (feed TEXT, id TEXT, start INTEGER, title TEXT, link TEXT, image TEXT);"))); 0145 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 5;"))); 0146 TRUE_OR_RETURN(commit()); 0147 return true; 0148 } 0149 0150 bool Database::migrateTo6() 0151 { 0152 qDebug() << "Migrating database to version 6"; 0153 TRUE_OR_RETURN(transaction()); 0154 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS SyncTimestamps (syncservice TEXT, timestamp INTEGER);"))); 0155 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS FeedActions (url TEXT, action TEXT, timestamp INTEGER);"))); 0156 TRUE_OR_RETURN( 0157 execute(QStringLiteral("CREATE TABLE IF NOT EXISTS EpisodeActions (podcast TEXT, url TEXT, id TEXT, action TEXT, started INTEGER, position INTEGER, " 0158 "total INTEGER, timestamp INTEGER);"))); 0159 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 6;"))); 0160 TRUE_OR_RETURN(commit()); 0161 return true; 0162 } 0163 0164 bool Database::migrateTo7() 0165 { 0166 qDebug() << "Migrating database to version 7"; 0167 TRUE_OR_RETURN(transaction()); 0168 TRUE_OR_RETURN(execute(QStringLiteral("ALTER TABLE Entries ADD COLUMN favorite BOOL DEFAULT 0;"))); 0169 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 7;"))); 0170 TRUE_OR_RETURN(commit()); 0171 return true; 0172 } 0173 0174 bool Database::migrateTo8() 0175 { 0176 qDebug() << "Migrating database to version 8; this can take a while"; 0177 0178 const int maxFilenameLength = 200; 0179 QString enclosurePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation); 0180 if (!SettingsManager::self()->storagePath().isEmpty()) { 0181 enclosurePath = SettingsManager::self()->storagePath().toLocalFile(); 0182 } 0183 enclosurePath += QStringLiteral("/enclosures/"); 0184 0185 TRUE_OR_RETURN(transaction()); 0186 TRUE_OR_RETURN(execute(QStringLiteral("ALTER TABLE Feeds ADD COLUMN dirname TEXT;"))); 0187 0188 QStringList dirNameList; 0189 QSqlQuery query(QStringLiteral("SELECT url, name FROM Feeds;")); 0190 while (query.next()) { 0191 QString url = query.value(QStringLiteral("url")).toString(); 0192 QString name = query.value(QStringLiteral("name")).toString(); 0193 0194 // Generate directory name for enclosures based on feed name 0195 QString dirBaseName = name.remove(QRegularExpression(QStringLiteral("[^a-zA-Z0-9 ._()-]"))).simplified().left(maxFilenameLength); 0196 dirBaseName = dirBaseName.isEmpty() ? QStringLiteral("Noname") : dirBaseName; 0197 QString dirName = dirBaseName; 0198 0199 // Check for duplicate names 0200 int numDups = 1; // Minimum to append is " (1)" if file already exists 0201 while (dirNameList.contains(dirName)) { 0202 dirName = QStringLiteral("%1 (%2)").arg(dirBaseName, QString::number(numDups)); 0203 numDups++; 0204 } 0205 0206 dirNameList << dirName; 0207 0208 QSqlQuery writeQuery; 0209 writeQuery.prepare(QStringLiteral("UPDATE Feeds SET dirname=:dirname WHERE url=:url;")); 0210 writeQuery.bindValue(QStringLiteral(":dirname"), dirName); 0211 writeQuery.bindValue(QStringLiteral(":url"), url); 0212 TRUE_OR_RETURN(execute(writeQuery)); 0213 } 0214 0215 // Rename enclosures to new filename convention 0216 query.prepare( 0217 QStringLiteral("SELECT entry.title, enclosure.id, enclosure.url, feed.dirname FROM Enclosures enclosure JOIN Entries entry ON enclosure.id = entry.id " 0218 "JOIN Feeds feed ON enclosure.feed = feed.url;")); 0219 TRUE_OR_RETURN(execute(query)); 0220 while (query.next()) { 0221 QString queryTitle = query.value(QStringLiteral("title")).toString(); 0222 QString queryId = query.value(QStringLiteral("id")).toString(); 0223 QString queryUrl = query.value(QStringLiteral("url")).toString(); 0224 QString feedDirName = query.value(QStringLiteral("dirname")).toString(); 0225 0226 // Rename any existing files with the new filename generated above 0227 QString legacyPath = enclosurePath + QString::fromStdString(QCryptographicHash::hash(queryUrl.toUtf8(), QCryptographicHash::Md5).toHex().toStdString()); 0228 0229 if (QFileInfo::exists(legacyPath) && QFileInfo(legacyPath).isFile()) { 0230 // Generate filename based on episode name and url hash with feedname as subdirectory 0231 QString enclosureFilenameBase = queryTitle.remove(QRegularExpression(QStringLiteral("[^a-zA-Z0-9 ._()-]"))).simplified().left(maxFilenameLength); 0232 enclosureFilenameBase = enclosureFilenameBase.isEmpty() ? QStringLiteral("Noname") : enclosureFilenameBase; 0233 enclosureFilenameBase += QStringLiteral(".") 0234 + QString::fromStdString(QCryptographicHash::hash(queryUrl.toUtf8(), QCryptographicHash::Md5).toHex().toStdString()).left(6); 0235 0236 QString enclosureFilenameExt = QFileInfo(QUrl::fromUserInput(queryUrl).fileName()).suffix(); 0237 0238 QString enclosureFilename = 0239 !enclosureFilenameExt.isEmpty() ? enclosureFilenameBase + QStringLiteral(".") + enclosureFilenameExt : enclosureFilenameBase; 0240 0241 QString newDirPath = enclosurePath + feedDirName + QStringLiteral("/"); 0242 QString newFilePath = newDirPath + enclosureFilename; 0243 0244 QFileInfo().absoluteDir().mkpath(newDirPath); 0245 QFile::rename(legacyPath, newFilePath); 0246 } 0247 } 0248 0249 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 8;"))); 0250 TRUE_OR_RETURN(commit()); 0251 return true; 0252 } 0253 0254 bool Database::execute(const QString &query, const QString &connectionName) 0255 { 0256 QSqlQuery q(connectionName); 0257 q.prepare(query); 0258 return execute(q); 0259 } 0260 0261 bool Database::execute(QSqlQuery &query) 0262 { 0263 // NOTE that this will execute the query on the database that was specified 0264 // when the QSqlQuery was created. There is no way to change that later on. 0265 if (!query.exec()) { 0266 qWarning() << "Failed to execute SQL Query"; 0267 qWarning() << query.lastQuery(); 0268 qWarning() << query.lastError(); 0269 return false; 0270 } 0271 return true; 0272 } 0273 0274 bool Database::transaction(const QString &connectionName) 0275 { 0276 // use IMMEDIATE transaction here to avoid deadlocks with writes happening 0277 // in different threads 0278 QSqlQuery query(QSqlDatabase::database(connectionName)); 0279 query.prepare(QStringLiteral("BEGIN IMMEDIATE TRANSACTION;")); 0280 return execute(query); 0281 } 0282 0283 bool Database::commit(const QString &connectionName) 0284 { 0285 return QSqlDatabase::database(connectionName).commit(); 0286 } 0287 0288 int Database::version() 0289 { 0290 QSqlQuery query; 0291 query.prepare(QStringLiteral("PRAGMA user_version;")); 0292 execute(query); 0293 if (query.next()) { 0294 bool ok; 0295 int value = query.value(0).toInt(&ok); 0296 qDebug() << "Database version" << value; 0297 if (ok) 0298 return value; 0299 } else { 0300 qCritical() << "Failed to check database version"; 0301 } 0302 return -1; 0303 } 0304 0305 void Database::setWalMode() 0306 { 0307 bool ok = false; 0308 QSqlQuery query; 0309 query.prepare(QStringLiteral("PRAGMA journal_mode;")); 0310 execute(query); 0311 if (query.next()) { 0312 ok = (query.value(0).toString() == QStringLiteral("wal")); 0313 } 0314 0315 if (!ok) { 0316 query.prepare(QStringLiteral("PRAGMA journal_mode=WAL;")); 0317 execute(query); 0318 if (query.next()) { 0319 ok = (query.value(0).toString() == QStringLiteral("wal")); 0320 } 0321 qDebug() << "Activating WAL mode on database:" << (ok ? "ok" : "not ok!"); 0322 } 0323 } 0324 0325 void Database::cleanup() 0326 { 0327 // TODO: create database sanity checks, or, alternatively, create database scrub routine 0328 }