Warning, file /utilities/telly-skout/src/database.cpp was not indexed or was modified since last indexation (in which case cross-reference links may be missing, inaccurate or erroneous).
0001 // SPDX-FileCopyrightText: 2022 Plata Hill <plata.hill@kdemail.net> 0002 // SPDX-License-Identifier: LGPL-2.1-or-later 0003 0004 #include "database.h" 0005 0006 #include "TellySkoutSettings.h" 0007 0008 #include <QDateTime> 0009 #include <QDebug> 0010 #include <QDir> 0011 #include <QSqlDatabase> 0012 #include <QSqlError> 0013 #include <QStandardPaths> 0014 #include <QUrl> 0015 0016 #define TRUE_OR_RETURN(x) \ 0017 if (!x) \ 0018 return false; 0019 0020 Database::Database() 0021 { 0022 QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE")); 0023 const QString databasePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation); 0024 QDir(databasePath).mkpath(databasePath); 0025 db.setDatabaseName(databasePath + QStringLiteral("/database.db3")); 0026 if (!db.open()) { 0027 qCritical() << "Failed to open database"; 0028 } 0029 0030 // drop DB if it doesn't use the correct fetcher 0031 if (TellySkoutSettings::fetcher() != fetcher()) { 0032 if (!dropTables()) { 0033 qCritical() << "Failed to drop database"; 0034 } 0035 } 0036 0037 if (!createTables()) { 0038 qCritical() << "Failed to create database"; 0039 } 0040 0041 cleanup(); 0042 0043 // speed up database (especially for slow persistent memory like on the PinePhone) 0044 execute(QStringLiteral("PRAGMA synchronous = OFF;")); 0045 execute(QStringLiteral("PRAGMA journal_mode = WAL;")); // no measurable effect in Database benchmark, but recommended everywhere 0046 0047 // prepare queries once (faster) 0048 m_addGroupQuery.reset(new QSqlQuery(db)); 0049 bool success = m_addGroupQuery->prepare(QStringLiteral("INSERT OR IGNORE INTO \"Groups\" VALUES (:id, :name, :url);")); 0050 m_groupCountQuery.reset(new QSqlQuery(db)); 0051 success &= m_groupCountQuery->prepare(QStringLiteral("SELECT COUNT() FROM \"Groups\";")); 0052 m_groupExistsQuery.reset(new QSqlQuery(db)); 0053 success &= m_groupExistsQuery->prepare(QStringLiteral("SELECT COUNT () FROM \"Groups\" WHERE id=:id;")); 0054 m_groupsQuery.reset(new QSqlQuery(db)); 0055 success &= m_groupsQuery->prepare(QStringLiteral("SELECT * FROM \"Groups\" ORDER BY name COLLATE NOCASE;")); 0056 m_groupsPerChannelQuery.reset(new QSqlQuery(db)); 0057 success &= m_groupsPerChannelQuery->prepare( 0058 QStringLiteral("SELECT * FROM \"Groups\" WHERE id=(SELECT \"group\" from GroupChannels WHERE channel=:channel) ORDER BY name COLLATE NOCASE;")); 0059 0060 m_addGroupChannelQuery.reset(new QSqlQuery(db)); 0061 success &= m_addGroupChannelQuery->prepare(QStringLiteral("INSERT OR IGNORE INTO GroupChannels VALUES (:id, :group, :channel);")); 0062 0063 m_addFavoriteQuery.reset(new QSqlQuery(db)); 0064 success &= m_addFavoriteQuery->prepare(QStringLiteral("INSERT INTO Favorites VALUES ((SELECT COUNT() FROM Favorites) + 1, :channel);")); 0065 m_addChannelQuery.reset(new QSqlQuery(db)); 0066 success &= m_addChannelQuery->prepare(QStringLiteral("INSERT OR IGNORE INTO Channels VALUES (:id, :name, :url, :image);")); 0067 m_channelCountQuery.reset(new QSqlQuery(db)); 0068 success &= m_channelCountQuery->prepare(QStringLiteral("SELECT COUNT() FROM Channels;")); 0069 m_channelExistsQuery.reset(new QSqlQuery(db)); 0070 success &= m_channelExistsQuery->prepare(QStringLiteral("SELECT COUNT () FROM Channels WHERE id=:id;")); 0071 m_channelsQuery.reset(new QSqlQuery(db)); 0072 success &= m_channelsQuery->prepare(QStringLiteral("SELECT * FROM Channels ORDER BY name COLLATE NOCASE;")); 0073 m_channelQuery.reset(new QSqlQuery(db)); 0074 success &= m_channelQuery->prepare(QStringLiteral("SELECT * FROM Channels WHERE id=:channelId;")); 0075 0076 m_clearFavoritesQuery.reset(new QSqlQuery(db)); 0077 success &= m_clearFavoritesQuery->prepare(QStringLiteral("DELETE FROM Favorites;")); 0078 m_favoriteCountQuery.reset(new QSqlQuery(db)); 0079 success &= m_favoriteCountQuery->prepare(QStringLiteral("SELECT COUNT() FROM Favorites;")); 0080 m_favoritesQuery.reset(new QSqlQuery(db)); 0081 success &= m_favoritesQuery->prepare(QStringLiteral("SELECT channel FROM Favorites ORDER BY id;")); 0082 m_isFavoriteQuery.reset(new QSqlQuery(db)); 0083 success &= m_isFavoriteQuery->prepare(QStringLiteral("SELECT COUNT() FROM Favorites WHERE channel=:channel")); 0084 0085 m_addProgramQuery.reset(new QSqlQuery(db)); 0086 success &= m_addProgramQuery->prepare( 0087 QStringLiteral("INSERT OR IGNORE INTO Programs VALUES (:id, :url, :channel, :start, :stop, :title, :subtitle, :description, :descriptionFetched);")); 0088 m_updateProgramDescriptionQuery.reset(new QSqlQuery(db)); 0089 success &= m_updateProgramDescriptionQuery->prepare(QStringLiteral("UPDATE Programs SET description=:description, descriptionFetched=TRUE WHERE id=:id;")); 0090 m_programExistsQuery.reset(new QSqlQuery(db)); 0091 success &= m_programExistsQuery->prepare(QStringLiteral("SELECT COUNT () FROM Programs WHERE channel=:channel AND stop>=:lastTime;")); 0092 m_programCountQuery.reset(new QSqlQuery(db)); 0093 success &= m_programCountQuery->prepare(QStringLiteral("SELECT COUNT() FROM Programs WHERE channel=:channel;")); 0094 m_programsQuery.reset(new QSqlQuery(db)); 0095 success &= m_programsQuery->prepare(QStringLiteral("SELECT * FROM Programs ORDER BY channel, start;")); 0096 m_programsPerChannelQuery.reset(new QSqlQuery(db)); 0097 success &= m_programsPerChannelQuery->prepare(QStringLiteral("SELECT * FROM Programs WHERE channel=:channel ORDER BY start;")); 0098 0099 m_addProgramCategoryQuery.reset(new QSqlQuery(db)); 0100 success &= m_addProgramCategoryQuery->prepare(QStringLiteral("INSERT OR IGNORE INTO ProgramCategories VALUES (:program, :category);")); 0101 m_programCategoriesQuery.reset(new QSqlQuery(db)); 0102 success &= m_programCategoriesQuery->prepare(QStringLiteral("SELECT * FROM ProgramCategories;")); 0103 0104 if (!success) { 0105 qCritical() << "Failed to prepare database queries"; 0106 } 0107 0108 connect(TellySkoutSettings::self(), &TellySkoutSettings::fetcherChanged, this, [this]() { 0109 dropTables(); 0110 createTables(); 0111 }); 0112 } 0113 0114 bool Database::createTables() 0115 { 0116 qDebug() << "Create DB tables"; 0117 0118 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Fetcher (id INTEGER UNIQUE);"))); 0119 TRUE_OR_RETURN(execute(QStringLiteral("INSERT OR IGNORE INTO Fetcher VALUES (") + QString::number(TellySkoutSettings::fetcher()) + ");")); 0120 0121 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS \"Groups\" (id TEXT UNIQUE, name TEXT, url TEXT);"))); 0122 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Channels (id TEXT UNIQUE, name TEXT, url TEXT, image TEXT);"))); 0123 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS GroupChannels (id TEXT UNIQUE, \"Group\" TEXT, channel TEXT);"))); 0124 TRUE_OR_RETURN(execute( 0125 QStringLiteral("CREATE TABLE IF NOT EXISTS Programs (id TEXT UNIQUE, url TEXT, channel TEXT, start INTEGER, stop INTEGER, title TEXT, subtitle TEXT, " 0126 "description TEXT, descriptionFetched INTEGER);"))); 0127 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS ProgramCategories (program TEXT, category TEXT);"))); 0128 TRUE_OR_RETURN(execute(QStringLiteral("CREATE TABLE IF NOT EXISTS Favorites (id INTEGER UNIQUE, channel TEXT UNIQUE);"))); 0129 0130 TRUE_OR_RETURN(execute(QStringLiteral("PRAGMA user_version = 1;"))); 0131 return true; 0132 } 0133 0134 bool Database::dropTables() 0135 { 0136 qDebug() << "Drop DB tables"; 0137 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS Fetcher;"))); 0138 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS \"Groups\";"))); 0139 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS Channels;"))); 0140 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS GroupChannels;"))); 0141 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS Programs;"))); 0142 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS ProgramCategories;"))); 0143 TRUE_OR_RETURN(execute(QStringLiteral("DROP TABLE IF EXISTS Favorites;"))); 0144 0145 return true; 0146 } 0147 0148 bool Database::execute(const QString &query) const 0149 { 0150 QSqlQuery q; 0151 if (q.prepare(query)) { 0152 return execute(q); 0153 } else { 0154 qCritical() << "Failed to prepare query '" << query << "'"; 0155 return false; 0156 } 0157 } 0158 0159 bool Database::execute(QSqlQuery &query) const 0160 { 0161 if (!query.exec()) { 0162 qWarning() << "Failed to execute SQL Query"; 0163 qWarning() << query.lastQuery(); 0164 qWarning() << query.lastError(); 0165 return false; 0166 } 0167 return true; 0168 } 0169 0170 int Database::version() const 0171 { 0172 const int error = -1; 0173 0174 QSqlQuery query; 0175 if (!query.prepare(QStringLiteral("PRAGMA user_version;"))) { 0176 qCritical() << "Failed to prepare query for user_version"; 0177 return error; 0178 } 0179 if (!execute(query)) { 0180 qCritical() << "Failed to execute query for user_version"; 0181 return error; 0182 } 0183 if (query.next()) { 0184 bool ok; 0185 int value = query.value(0).toInt(&ok); 0186 qDebug() << "Database version" << value; 0187 if (ok) { 0188 return value; 0189 } 0190 } else { 0191 qCritical() << "Failed to check database version"; 0192 } 0193 return error; 0194 } 0195 0196 int Database::fetcher() const 0197 { 0198 const int error = -1; 0199 0200 QSqlQuery query; 0201 if (!query.prepare(QStringLiteral("SELECT * FROM Fetcher;"))) { 0202 qCritical() << "Failed to prepare query for fetcher"; 0203 return error; 0204 } 0205 if (!execute(query)) { 0206 qCritical() << "Failed to execute query for fetcher"; 0207 return error; 0208 } 0209 if (query.next()) { 0210 bool ok; 0211 int value = query.value(0).toInt(&ok); 0212 qDebug() << "Database for fetcher" << value; 0213 if (ok) { 0214 return value; 0215 } 0216 } else { 0217 qCritical() << "Failed to check fetcher"; 0218 } 0219 return error; 0220 } 0221 0222 void Database::cleanup() 0223 { 0224 // delete programs in the past to avoid that the database grows over time 0225 const unsigned int daysPast = TellySkoutSettings::deleteProgramAfter(); 0226 QDateTime dateTimePast = QDateTime::currentDateTime(); 0227 dateTimePast = dateTimePast.addDays(-static_cast<qint64>(daysPast)); 0228 0229 // delete programs in the far future (probably they have been added by mistake) 0230 QDateTime dateTimeFuture = QDateTime::currentDateTime(); 0231 dateTimeFuture = dateTimeFuture.addDays(30); 0232 0233 QSqlDatabase::database().transaction(); 0234 QSqlQuery query; 0235 if (!query.prepare(QStringLiteral("DELETE FROM Programs WHERE stop < :sinceEpochPast OR stop > :sinceEpochFuture;"))) { 0236 qCritical() << "Failed to prepare cleanup query for Programs"; 0237 return; 0238 } 0239 query.bindValue(QStringLiteral(":sinceEpochPast"), dateTimePast.toSecsSinceEpoch()); 0240 query.bindValue(QStringLiteral(":sinceEpochFuture"), dateTimeFuture.toSecsSinceEpoch()); 0241 execute(query); 0242 0243 // delete categories which are no longer referenced 0244 if (!query.prepare(QStringLiteral("DELETE FROM ProgramCategories WHERE program NOT IN (SELECT id FROM Programs WHERE id IS NOT NULL);"))) { 0245 qCritical() << "Failed to prepare cleanup query for ProgramCategories"; 0246 return; 0247 } 0248 execute(query); 0249 0250 QSqlDatabase::database().commit(); 0251 } 0252 0253 void Database::addGroup(const GroupData &data) 0254 { 0255 if (!groupExists(data.m_id)) { 0256 qDebug() << "Add group" << data.m_name; 0257 m_addGroupQuery->bindValue(QStringLiteral(":id"), data.m_id.value()); 0258 m_addGroupQuery->bindValue(QStringLiteral(":name"), data.m_name); 0259 m_addGroupQuery->bindValue(QStringLiteral(":url"), data.m_url); 0260 execute(*m_addGroupQuery); 0261 0262 Q_EMIT groupAdded(data.m_id); 0263 } 0264 } 0265 0266 void Database::addGroups(const QVector<GroupData> &groups) 0267 { 0268 QSqlDatabase::database().transaction(); 0269 0270 for (const GroupData &data : groups) { 0271 addGroup(data); 0272 } 0273 0274 QSqlDatabase::database().commit(); 0275 } 0276 0277 size_t Database::groupCount() const 0278 { 0279 execute(*m_groupCountQuery); 0280 if (!m_groupCountQuery->next()) { 0281 qWarning() << "Failed to query group count"; 0282 return 0; 0283 } 0284 return m_groupCountQuery->value(0).toUInt(); 0285 } 0286 0287 bool Database::groupExists(const GroupId &id) const 0288 { 0289 m_groupExistsQuery->bindValue(QStringLiteral(":id"), id.value()); 0290 execute(*m_groupExistsQuery); 0291 m_groupExistsQuery->next(); 0292 0293 return m_groupExistsQuery->value(0).toInt() > 0; 0294 } 0295 0296 QVector<GroupData> Database::groups() const 0297 { 0298 QVector<GroupData> groups; 0299 0300 execute(*m_groupsQuery); 0301 while (m_groupsQuery->next()) { 0302 GroupData data; 0303 data.m_id = GroupId(m_groupsQuery->value(QStringLiteral("id")).toString()); 0304 data.m_name = m_groupsQuery->value(QStringLiteral("name")).toString(); 0305 data.m_url = m_groupsQuery->value(QStringLiteral("url")).toString(); 0306 groups.append(data); 0307 } 0308 return groups; 0309 } 0310 0311 QVector<GroupData> Database::groups(const ChannelId &channelId) const 0312 { 0313 QVector<GroupData> groups; 0314 0315 m_groupsPerChannelQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0316 execute(*m_groupsPerChannelQuery); 0317 while (m_groupsPerChannelQuery->next()) { 0318 GroupData data; 0319 data.m_id = GroupId(m_groupsPerChannelQuery->value(QStringLiteral("id")).toString()); 0320 data.m_name = m_groupsPerChannelQuery->value(QStringLiteral("name")).toString(); 0321 data.m_url = m_groupsPerChannelQuery->value(QStringLiteral("url")).toString(); 0322 groups.append(data); 0323 } 0324 return groups; 0325 } 0326 0327 void Database::addChannel(const ChannelData &data, const GroupId &group) 0328 { 0329 if (!channelExists(data.m_id)) { 0330 qDebug() << "Add channel" << data.m_name; 0331 0332 // store channel per group 0333 { 0334 m_addGroupChannelQuery->bindValue(QStringLiteral(":id"), group.value() + "_" + data.m_id.value()); 0335 m_addGroupChannelQuery->bindValue(QStringLiteral(":group"), group.value()); 0336 m_addGroupChannelQuery->bindValue(QStringLiteral(":channel"), data.m_id.value()); 0337 execute(*m_addGroupChannelQuery); 0338 } 0339 0340 // store channel 0341 { 0342 m_addChannelQuery->bindValue(QStringLiteral(":id"), data.m_id.value()); 0343 m_addChannelQuery->bindValue(QStringLiteral(":name"), data.m_name); 0344 m_addChannelQuery->bindValue(QStringLiteral(":url"), data.m_url); 0345 m_addChannelQuery->bindValue(QStringLiteral(":group"), group.value()); 0346 m_addChannelQuery->bindValue(QStringLiteral(":image"), data.m_image); 0347 execute(*m_addChannelQuery); 0348 Q_EMIT channelAdded(data.m_id); 0349 } 0350 } 0351 } 0352 0353 void Database::addChannels(const QList<ChannelData> &channels, const GroupId &group) 0354 { 0355 QSqlDatabase::database().transaction(); 0356 0357 for (const ChannelData &data : channels) { 0358 addChannel(data, group); 0359 } 0360 0361 QSqlDatabase::database().commit(); 0362 } 0363 0364 size_t Database::channelCount() const 0365 { 0366 execute(*m_channelCountQuery); 0367 if (!m_channelCountQuery->next()) { 0368 qWarning() << "Failed to query channel count"; 0369 return 0; 0370 } 0371 return m_channelCountQuery->value(0).toUInt(); 0372 } 0373 0374 bool Database::channelExists(const ChannelId &id) const 0375 { 0376 m_channelExistsQuery->bindValue(QStringLiteral(":id"), id.value()); 0377 execute(*m_channelExistsQuery); 0378 m_channelExistsQuery->next(); 0379 0380 return m_channelExistsQuery->value(0).toInt() > 0; 0381 } 0382 0383 QVector<ChannelData> Database::channels(bool onlyFavorites) const 0384 { 0385 QVector<ChannelData> channels; 0386 0387 if (onlyFavorites) { 0388 const QVector<ChannelId> &favoriteIds = favorites(); 0389 0390 QSqlDatabase::database().transaction(); 0391 for (int i = 0; i < favoriteIds.size(); ++i) { 0392 channels.append(channel(favoriteIds.at(i))); 0393 } 0394 QSqlDatabase::database().commit(); 0395 } else { 0396 execute(*m_channelsQuery); 0397 while (m_channelsQuery->next()) { 0398 ChannelData data; 0399 data.m_id = ChannelId(m_channelsQuery->value(QStringLiteral("id")).toString()); 0400 data.m_name = m_channelsQuery->value(QStringLiteral("name")).toString(); 0401 data.m_url = m_channelsQuery->value(QStringLiteral("url")).toString(); 0402 data.m_image = m_channelsQuery->value(QStringLiteral("image")).toString(); 0403 channels.append(data); 0404 } 0405 } 0406 return channels; 0407 } 0408 0409 ChannelData Database::channel(const ChannelId &channelId) const 0410 { 0411 ChannelData data; 0412 data.m_id = channelId; 0413 0414 m_channelQuery->bindValue(QStringLiteral(":channelId"), data.m_id.value()); 0415 execute(*m_channelQuery); 0416 if (!m_channelQuery->next()) { 0417 qWarning() << "Failed to query channel" << channelId.value(); 0418 } else { 0419 data.m_id = ChannelId(m_channelQuery->value(QStringLiteral("id")).toString()); 0420 data.m_name = m_channelQuery->value(QStringLiteral("name")).toString(); 0421 data.m_url = m_channelQuery->value(QStringLiteral("url")).toString(); 0422 data.m_image = m_channelQuery->value(QStringLiteral("image")).toString(); 0423 } 0424 return data; 0425 } 0426 0427 void Database::addFavorite(const ChannelId &channelId) 0428 { 0429 m_addFavoriteQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0430 execute(*m_addFavoriteQuery); 0431 0432 Q_EMIT channelDetailsUpdated(channelId, true); 0433 } 0434 0435 void Database::removeFavorite(const ChannelId &channelId) 0436 { 0437 // just removing channelId from the Favorites table does not work 0438 // it would leave gaps in the IDs (making it impossible to add new favorites) 0439 QVector<ChannelId> favoriteChannelIds = favorites(); 0440 favoriteChannelIds.removeAll(channelId); 0441 0442 QSqlDatabase::database().transaction(); 0443 execute(*m_clearFavoritesQuery); 0444 for (const auto &id : std::as_const(favoriteChannelIds)) { 0445 m_addFavoriteQuery->bindValue(QStringLiteral(":channel"), id.value()); 0446 execute(*m_addFavoriteQuery); 0447 } 0448 QSqlDatabase::database().commit(); 0449 0450 Q_EMIT channelDetailsUpdated(channelId, false); 0451 } 0452 0453 void Database::sortFavorites(const QVector<ChannelId> &newOrder) 0454 { 0455 QSqlDatabase::database().transaction(); 0456 // do not use clearFavorites() and addFavorite() to avoid unneccesary signals (and therefore updates) 0457 execute(*m_clearFavoritesQuery); 0458 for (const auto &channelId : newOrder) { 0459 m_addFavoriteQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0460 execute(*m_addFavoriteQuery); 0461 } 0462 QSqlDatabase::database().commit(); 0463 0464 Q_EMIT favoritesUpdated(); 0465 } 0466 0467 void Database::clearFavorites() 0468 { 0469 const QVector<ChannelId> favoriteChannelIds = favorites(); 0470 0471 execute(*m_clearFavoritesQuery); 0472 0473 for (const auto &channelId : favoriteChannelIds) { 0474 Q_EMIT channelDetailsUpdated(channelId, false); 0475 } 0476 } 0477 0478 size_t Database::favoriteCount() const 0479 { 0480 execute(*m_favoriteCountQuery); 0481 if (!m_favoriteCountQuery->next()) { 0482 qWarning() << "Failed to query favorite count"; 0483 return 0; 0484 } 0485 return m_favoriteCountQuery->value(0).toUInt(); 0486 } 0487 0488 QVector<ChannelId> Database::favorites() const 0489 { 0490 QVector<ChannelId> favorites; 0491 0492 execute(*m_favoritesQuery); 0493 while (m_favoritesQuery->next()) { 0494 const ChannelId channelId = ChannelId(m_favoritesQuery->value(QStringLiteral("channel")).toString()); 0495 favorites.append(channelId); 0496 } 0497 return favorites; 0498 } 0499 0500 bool Database::isFavorite(const ChannelId &channelId) const 0501 { 0502 m_isFavoriteQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0503 execute(*m_isFavoriteQuery); 0504 m_isFavoriteQuery->next(); 0505 return m_isFavoriteQuery->value(0).toInt() > 0; 0506 } 0507 0508 void Database::addProgram(const ProgramData &data) 0509 { 0510 m_addProgramQuery->bindValue(QStringLiteral(":id"), data.m_id.value()); 0511 m_addProgramQuery->bindValue(QStringLiteral(":url"), data.m_url); 0512 m_addProgramQuery->bindValue(QStringLiteral(":channel"), data.m_channelId.value()); 0513 m_addProgramQuery->bindValue(QStringLiteral(":start"), data.m_startTime.toSecsSinceEpoch()); 0514 m_addProgramQuery->bindValue(QStringLiteral(":stop"), data.m_stopTime.toSecsSinceEpoch()); 0515 m_addProgramQuery->bindValue(QStringLiteral(":title"), data.m_title); 0516 m_addProgramQuery->bindValue(QStringLiteral(":subtitle"), data.m_subtitle); 0517 m_addProgramQuery->bindValue(QStringLiteral(":description"), data.m_description); 0518 m_addProgramQuery->bindValue(QStringLiteral(":descriptionFetched"), data.m_descriptionFetched); 0519 0520 execute(*m_addProgramQuery); 0521 0522 m_addProgramCategoryQuery->bindValue(QStringLiteral(":program"), data.m_id.value()); 0523 0524 const QVector<QString> &categories = data.m_categories; 0525 for (int i = 0; i < categories.size(); ++i) { 0526 m_addProgramCategoryQuery->bindValue(QStringLiteral(":category"), categories.at(i)); 0527 execute(*m_addProgramCategoryQuery); 0528 } 0529 } 0530 0531 void Database::updateProgramDescription(const ProgramId &id, const QString &description) 0532 { 0533 m_updateProgramDescriptionQuery->bindValue(QStringLiteral(":id"), id.value()); 0534 m_updateProgramDescriptionQuery->bindValue(QStringLiteral(":description"), description); 0535 0536 execute(*m_updateProgramDescriptionQuery); 0537 } 0538 0539 void Database::addPrograms(const QVector<ProgramData> &programs) 0540 { 0541 QSqlDatabase::database().transaction(); 0542 0543 for (const ProgramData &data : programs) { 0544 addProgram(data); 0545 } 0546 0547 QSqlDatabase::database().commit(); 0548 } 0549 0550 bool Database::programExists(const ChannelId &channelId, const QDateTime &lastTime) const 0551 { 0552 m_programExistsQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0553 m_programExistsQuery->bindValue(QStringLiteral(":lastTime"), lastTime.toSecsSinceEpoch()); 0554 execute(*m_programExistsQuery); 0555 m_programExistsQuery->next(); 0556 0557 return m_programExistsQuery->value(0).toInt() > 0; 0558 } 0559 0560 size_t Database::programCount(const ChannelId &channelId) const 0561 { 0562 m_programCountQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0563 execute(*m_programCountQuery); 0564 if (!m_programCountQuery->next()) { 0565 qWarning() << "Failed to query program count"; 0566 return 0; 0567 } 0568 return m_programCountQuery->value(0).toUInt(); 0569 } 0570 0571 QMap<ChannelId, QVector<ProgramData>> Database::programs() const 0572 { 0573 QMap<ChannelId, QVector<ProgramData>> programs; 0574 0575 QSqlDatabase::database().transaction(); 0576 const QMultiMap<ProgramId, QString> categories = programCategories(); 0577 0578 execute(*m_programsQuery); 0579 0580 while (m_programsQuery->next()) { 0581 const ChannelId channelId = ChannelId(m_programsQuery->value(QStringLiteral("channel")).toString()); 0582 if (!programs.contains(channelId)) { 0583 programs.insert(channelId, QVector<ProgramData>()); 0584 } 0585 0586 ProgramData data; 0587 data.m_id = ProgramId(m_programsQuery->value(QStringLiteral("id")).toString()); 0588 data.m_url = m_programsQuery->value(QStringLiteral("url")).toString(); 0589 data.m_channelId = channelId; 0590 data.m_startTime.setSecsSinceEpoch(m_programsQuery->value(QStringLiteral("start")).toInt()); 0591 data.m_stopTime.setSecsSinceEpoch(m_programsQuery->value(QStringLiteral("stop")).toInt()); 0592 data.m_title = m_programsQuery->value(QStringLiteral("title")).toString(); 0593 data.m_subtitle = m_programsQuery->value(QStringLiteral("subtitle")).toString(); 0594 data.m_description = m_programsQuery->value(QStringLiteral("description")).toString(); 0595 data.m_descriptionFetched = m_programsQuery->value(QStringLiteral("descriptionFetched")).toBool(); 0596 0597 for (auto &&category : categories.values(data.m_id)) { 0598 data.m_categories.push_back(category); 0599 } 0600 0601 programs[channelId].push_back(data); 0602 } 0603 QSqlDatabase::database().commit(); 0604 0605 return programs; 0606 } 0607 0608 QVector<ProgramData> Database::programs(const ChannelId &channelId) const 0609 { 0610 QVector<ProgramData> programs; 0611 0612 QSqlDatabase::database().transaction(); 0613 const QMultiMap<ProgramId, QString> categories = programCategories(); 0614 0615 m_programsPerChannelQuery->bindValue(QStringLiteral(":channel"), channelId.value()); 0616 execute(*m_programsPerChannelQuery); 0617 0618 while (m_programsPerChannelQuery->next()) { 0619 ProgramData data; 0620 data.m_id = ProgramId(m_programsPerChannelQuery->value(QStringLiteral("id")).toString()); 0621 data.m_url = m_programsPerChannelQuery->value(QStringLiteral("url")).toString(); 0622 data.m_channelId = ChannelId(m_programsPerChannelQuery->value(QStringLiteral("channel")).toString()); 0623 data.m_startTime.setSecsSinceEpoch(m_programsPerChannelQuery->value(QStringLiteral("start")).toInt()); 0624 data.m_stopTime.setSecsSinceEpoch(m_programsPerChannelQuery->value(QStringLiteral("stop")).toInt()); 0625 data.m_title = m_programsPerChannelQuery->value(QStringLiteral("title")).toString(); 0626 data.m_subtitle = m_programsPerChannelQuery->value(QStringLiteral("subtitle")).toString(); 0627 data.m_description = m_programsPerChannelQuery->value(QStringLiteral("description")).toString(); 0628 data.m_descriptionFetched = m_programsPerChannelQuery->value(QStringLiteral("descriptionFetched")).toBool(); 0629 0630 for (auto &&category : categories.values(data.m_id)) { 0631 data.m_categories.push_back(category); 0632 } 0633 0634 programs.push_back(data); 0635 } 0636 QSqlDatabase::database().commit(); 0637 0638 return programs; 0639 } 0640 0641 QMultiMap<ProgramId, QString> Database::programCategories() const 0642 { 0643 QMultiMap<ProgramId, QString> categories; 0644 execute(*m_programCategoriesQuery); 0645 while (m_programCategoriesQuery->next()) { 0646 categories.insert(ProgramId(m_programCategoriesQuery->value(QStringLiteral("program")).toString()), 0647 m_programCategoriesQuery->value(QStringLiteral("category")).toString()); 0648 } 0649 return categories; 0650 }