telly-skout/src/database.cpp

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 }