xbmc/pvr/PVRDatabase.cpp

   1 /*
   2  *  Copyright (C) 2012-2018 Team Kodi
   3  *  This file is part of Kodi - https://kodi.tv
   4  *
   5  *  SPDX-License-Identifier: GPL-2.0-or-later
   6  *  See LICENSES/README.md for more information.
   7  */
   8
   9 #include "PVRDatabase.h"
  10
  11 #include "ServiceBroker.h"
  12 #include "dbwrappers/dataset.h"
  13 #include "pvr/addons/PVRClient.h"
  14 #include "pvr/channels/PVRChannel.h"
  15 #include "pvr/channels/PVRChannelGroupMember.h"
  16 #include "pvr/channels/PVRChannelGroups.h"
  17 #include "pvr/providers/PVRProvider.h"
  18 #include "pvr/providers/PVRProviders.h"
  19 #include "pvr/timers/PVRTimerInfoTag.h"
  20 #include "pvr/timers/PVRTimerType.h"
  21 #include "settings/AdvancedSettings.h"
  22 #include "settings/SettingsComponent.h"
  23 #include "utils/StringUtils.h"
  24 #include "utils/log.h"
  25
  26 #include <cstdlib>
  27 #include <map>
  28 #include <memory>
  29 #include <mutex>
  30 #include <string>
  31 #include <utility>
  32 #include <vector>
  33
  34 using namespace dbiplus;
  35 using namespace PVR;
  36
  37 namespace
  38 {
  39 // clang-format off
  40
  41   static const std::string sqlCreateTimersTable =
  42     "CREATE TABLE timers ("
  43       "iClientIndex       integer primary key, "
  44       "iParentClientIndex integer, "
  45       "iClientId          integer, "
  46       "iTimerType         integer, "
  47       "iState             integer, "
  48       "sTitle             varchar(255), "
  49       "iClientChannelUid  integer, "
  50       "sSeriesLink        varchar(255), "
  51       "sStartTime         varchar(20), "
  52       "bStartAnyTime      bool, "
  53       "sEndTime           varchar(20), "
  54       "bEndAnyTime        bool, "
  55       "sFirstDay          varchar(20), "
  56       "iWeekdays          integer, "
  57       "iEpgUid            integer, "
  58       "iMarginStart       integer, "
  59       "iMarginEnd         integer, "
  60       "sEpgSearchString   varchar(255), "
  61       "bFullTextEpgSearch bool, "
  62       "iPreventDuplicates integer,"
  63       "iPrority           integer,"
  64       "iLifetime          integer,"
  65       "iMaxRecordings     integer,"
  66       "iRecordingGroup    integer"
  67   ")";
  68
  69   static const std::string sqlCreateProvidersTable =
  70     "CREATE TABLE providers ("
  71       "idProvider           integer primary key, "
  72       "iUniqueId            integer, "
  73       "iClientId            integer, "
  74       "sName                varchar(64), "
  75       "iType                integer, "
  76       "sIconPath            varchar(255), "
  77       "sCountries           varchar(64), "
  78       "sLanguages           varchar(64) "
  79     ")";
  80
  81   // clang-format on
  82
  83   std::string GetClientIdsSQL(const std::vector<std::shared_ptr<CPVRClient>>& clients,
  84                               bool migrate = false)
  85   {
  86     if (clients.empty() && !migrate)
  87       return {};
  88
  89     std::string clientIds = "(";
  90     for (auto it = clients.cbegin(); it != clients.cend(); ++it)
  91     {
  92       if (it != clients.cbegin())
  93         clientIds += " OR ";
  94
  95       clientIds += "iClientId = ";
  96       clientIds += std::to_string((*it)->GetID());
  97     }
  98     if (migrate)
  99     {
 100       if (!clients.empty())
 101         clientIds += " OR ";
 102
 103       clientIds += "iClientId = -2"; // PVR_GROUP_CLIENT_ID_UNKNOWN
 104     }
 105     clientIds += ")";
 106     return clientIds;
 107   }
 108
 109 } // unnamed namespace
 110
 111 bool CPVRDatabase::Open()
 112 {
 113   std::unique_lock<CCriticalSection> lock(m_critSection);
 114   return CDatabase::Open(CServiceBroker::GetSettingsComponent()->GetAdvancedSettings()->m_databaseTV);
 115 }
 116
 117 void CPVRDatabase::Close()
 118 {
 119   std::unique_lock<CCriticalSection> lock(m_critSection);
 120   CDatabase::Close();
 121 }
 122
 123 void CPVRDatabase::Lock()
 124 {
 125   m_critSection.lock();
 126 }
 127
 128 void CPVRDatabase::Unlock()
 129 {
 130   m_critSection.unlock();
 131 }
 132
 133 void CPVRDatabase::CreateTables()
 134 {
 135   std::unique_lock<CCriticalSection> lock(m_critSection);
 136
 137   CLog::LogF(LOGINFO, "Creating PVR database tables");
 138
 139   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'channels'");
 140   m_pDS->exec("CREATE TABLE channels ("
 141               "idChannel            integer primary key, "
 142               "iUniqueId            integer, "
 143               "bIsRadio             bool, "
 144               "bIsHidden            bool, "
 145               "bIsUserSetIcon       bool, "
 146               "bIsUserSetName       bool, "
 147               "bIsLocked            bool, "
 148               "sIconPath            varchar(255), "
 149               "sChannelName         varchar(64), "
 150               "bIsVirtual           bool, "
 151               "bEPGEnabled          bool, "
 152               "sEPGScraper          varchar(32), "
 153               "iLastWatched         integer, "
 154               "iClientId            integer, " //! @todo use mapping table
 155               "idEpg                integer, "
 156               "bHasArchive          bool, "
 157               "iClientProviderUid   integer, "
 158               "bIsUserSetHidden     bool, "
 159               "iLastWatchedGroupId  integer"
 160               ")");
 161
 162   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'channelgroups'");
 163   m_pDS->exec("CREATE TABLE channelgroups ("
 164               "idGroup         integer primary key, "
 165               "bIsRadio        bool, "
 166               "iGroupType      integer, "
 167               "sName           varchar(64), "
 168               "iLastWatched    integer, "
 169               "bIsHidden       bool, "
 170               "iPosition       integer, "
 171               "iLastOpened     bigint unsigned, "
 172               "iClientId       integer, "
 173               "bIsUserSetName  bool, "
 174               "sClientName     varchar(64), "
 175               "iClientPosition integer"
 176               ")");
 177
 178   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'map_channelgroups_channels'");
 179   m_pDS->exec(
 180       "CREATE TABLE map_channelgroups_channels ("
 181         "idChannel         integer, "
 182         "idGroup           integer, "
 183         "iChannelNumber    integer, "
 184         "iSubChannelNumber integer, "
 185         "iOrder            integer, "
 186         "iClientChannelNumber    integer, "
 187         "iClientSubChannelNumber integer"
 188       ")"
 189   );
 190
 191   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'clients'");
 192   m_pDS->exec(
 193       "CREATE TABLE clients ("
 194         "idClient  integer primary key, "
 195         "iPriority integer"
 196       ")"
 197   );
 198
 199   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'timers'");
 200   m_pDS->exec(sqlCreateTimersTable);
 201
 202   CLog::LogFC(LOGDEBUG, LOGPVR, "Creating table 'providers'");
 203   m_pDS->exec(sqlCreateProvidersTable);
 204 }
 205
 206 void CPVRDatabase::CreateAnalytics()
 207 {
 208   std::unique_lock<CCriticalSection> lock(m_critSection);
 209
 210   CLog::LogF(LOGINFO, "Creating PVR database indices");
 211   m_pDS->exec("CREATE INDEX idx_clients_idClient on clients(idClient);");
 212   m_pDS->exec("CREATE UNIQUE INDEX idx_channels_iClientId_iUniqueId on channels(iClientId, iUniqueId);");
 213   m_pDS->exec("CREATE INDEX idx_channelgroups_bIsRadio on channelgroups(bIsRadio);");
 214   m_pDS->exec("CREATE UNIQUE INDEX idx_idGroup_idChannel on map_channelgroups_channels(idGroup, idChannel);");
 215   m_pDS->exec("CREATE INDEX idx_timers_iClientIndex on timers(iClientIndex);");
 216 }
 217
 218 void CPVRDatabase::UpdateTables(int iVersion)
 219 {
 220   std::unique_lock<CCriticalSection> lock(m_critSection);
 221
 222   if (iVersion < 13)
 223     m_pDS->exec("ALTER TABLE channels ADD idEpg integer;");
 224
 225   if (iVersion < 20)
 226     m_pDS->exec("ALTER TABLE channels ADD bIsUserSetIcon bool");
 227
 228   if (iVersion < 21)
 229     m_pDS->exec("ALTER TABLE channelgroups ADD iGroupType integer");
 230
 231   if (iVersion < 22)
 232     m_pDS->exec("ALTER TABLE channels ADD bIsLocked bool");
 233
 234   if (iVersion < 23)
 235     m_pDS->exec("ALTER TABLE channelgroups ADD iLastWatched integer");
 236
 237   if (iVersion < 24)
 238     m_pDS->exec("ALTER TABLE channels ADD bIsUserSetName bool");
 239
 240   if (iVersion < 25)
 241     m_pDS->exec("DROP TABLE IF EXISTS channelsettings");
 242
 243   if (iVersion < 26)
 244   {
 245     m_pDS->exec("ALTER TABLE channels ADD iClientSubChannelNumber integer");
 246     m_pDS->exec("UPDATE channels SET iClientSubChannelNumber = 0");
 247     m_pDS->exec("ALTER TABLE map_channelgroups_channels ADD iSubChannelNumber integer");
 248     m_pDS->exec("UPDATE map_channelgroups_channels SET iSubChannelNumber = 0");
 249   }
 250
 251   if (iVersion < 27)
 252     m_pDS->exec("ALTER TABLE channelgroups ADD bIsHidden bool");
 253
 254   if (iVersion < 28)
 255     m_pDS->exec("DROP TABLE clients");
 256
 257   if (iVersion < 29)
 258     m_pDS->exec("ALTER TABLE channelgroups ADD iPosition integer");
 259
 260   if (iVersion < 32)
 261     m_pDS->exec("CREATE TABLE clients (idClient integer primary key, iPriority integer)");
 262
 263   if (iVersion < 33)
 264     m_pDS->exec(sqlCreateTimersTable);
 265
 266   if (iVersion < 34)
 267     m_pDS->exec("ALTER TABLE channels ADD bHasArchive bool");
 268
 269   if (iVersion < 35)
 270   {
 271     m_pDS->exec("ALTER TABLE map_channelgroups_channels ADD iOrder integer");
 272     m_pDS->exec("UPDATE map_channelgroups_channels SET iOrder = 0");
 273   }
 274
 275   if (iVersion < 36)
 276   {
 277     m_pDS->exec("ALTER TABLE map_channelgroups_channels ADD iClientChannelNumber integer");
 278     m_pDS->exec("UPDATE map_channelgroups_channels SET iClientChannelNumber = 0");
 279     m_pDS->exec("ALTER TABLE map_channelgroups_channels ADD iClientSubChannelNumber integer");
 280     m_pDS->exec("UPDATE map_channelgroups_channels SET iClientSubChannelNumber = 0");
 281   }
 282
 283   if (iVersion < 37)
 284     m_pDS->exec("ALTER TABLE channelgroups ADD iLastOpened integer");
 285
 286   if (iVersion < 38)
 287   {
 288     m_pDS->exec("ALTER TABLE channelgroups "
 289                 "RENAME TO channelgroups_old");
 290
 291     m_pDS->exec("CREATE TABLE channelgroups ("
 292                 "idGroup      integer primary key, "
 293                 "bIsRadio     bool, "
 294                 "iGroupType   integer, "
 295                 "sName        varchar(64), "
 296                 "iLastWatched integer, "
 297                 "bIsHidden    bool, "
 298                 "iPosition    integer, "
 299                 "iLastOpened  bigint unsigned"
 300                 ")");
 301
 302     m_pDS->exec(
 303         "INSERT INTO channelgroups (bIsRadio, iGroupType, sName, iLastWatched, bIsHidden, "
 304         "iPosition, iLastOpened) "
 305         "SELECT bIsRadio, iGroupType, sName, iLastWatched, bIsHidden, iPosition, iLastOpened "
 306         "FROM channelgroups_old");
 307
 308     m_pDS->exec("DROP TABLE channelgroups_old");
 309   }
 310
 311   if (iVersion < 39)
 312   {
 313     m_pDS->exec(sqlCreateProvidersTable);
 314     m_pDS->exec("CREATE UNIQUE INDEX idx_iUniqueId_iClientId on providers(iUniqueId, iClientId);");
 315     m_pDS->exec("ALTER TABLE channels ADD iClientProviderUid integer");
 316     m_pDS->exec("UPDATE channels SET iClientProviderUid = -1");
 317   }
 318
 319   if (iVersion < 40)
 320   {
 321     m_pDS->exec("ALTER TABLE channels ADD bIsUserSetHidden bool");
 322     m_pDS->exec("UPDATE channels SET bIsUserSetHidden = bIsHidden");
 323   }
 324
 325   if (iVersion < 41)
 326   {
 327     try
 328     {
 329       // cleanup orphaned entries that might have piled up over time...
 330       m_pDS->exec("DELETE FROM map_channelgroups_channels WHERE idChannel NOT IN (SELECT "
 331                   "idChannel FROM channels)");
 332       m_pDS->exec("DELETE FROM channelgroups WHERE idGroup NOT IN (SELECT idGroup FROM "
 333                   "map_channelgroups_channels)");
 334     }
 335     catch (...)
 336     {
 337       CLog::LogFC(LOGERROR, LOGPVR, "Exception in database cleanup");
 338     }
 339
 340     m_pDS->exec("ALTER TABLE channelgroups ADD iClientId integer");
 341     // set "PVR_GROUP_CLIENT_ID_UNKNOWN for migration of backend provided groups
 342     m_pDS->exec("UPDATE channelgroups SET iClientId = -2 WHERE iGroupType = 0");
 343     // set PVR_GROUP_CLIENT_ID_LOCAL for local groups
 344     m_pDS->exec("UPDATE channelgroups SET iClientId = -1 WHERE iGroupType != 0");
 345   }
 346
 347   if (iVersion < 42)
 348   {
 349     m_pDS->exec("ALTER TABLE channelgroups ADD bIsUserSetName bool");
 350     m_pDS->exec("UPDATE channelgroups SET bIsUserSetName = 0");
 351
 352     m_pDS->exec("ALTER TABLE channelgroups ADD sClientName varchar(64)");
 353     m_pDS->exec("UPDATE channelgroups SET sClientName = sName WHERE iGroupType = 0");
 354     m_pDS->exec("UPDATE channelgroups SET sClientName = '' WHERE iGroupType != 0");
 355   }
 356
 357   if (iVersion < 43)
 358   {
 359     m_pDS->exec("ALTER TABLE channelgroups ADD iClientPosition integer");
 360     m_pDS->exec("UPDATE channelgroups SET iClientPosition = iPosition");
 361     // Setup initial local order, not perfect but at least unique across all groups.
 362     // Should mostly be the order the groups appeared from backend or were created by user locally.
 363     m_pDS->exec("UPDATE channelgroups SET iPosition = idGroup");
 364   }
 365
 366   if (iVersion < 44)
 367   {
 368     m_pDS->exec("ALTER TABLE channels ADD iLastWatchedGroupId integer");
 369     m_pDS->exec("UPDATE channels SET iLastWatchedGroupId = -1");
 370   }
 371 }
 372
 373 /********** Client methods **********/
 374
 375 bool CPVRDatabase::DeleteClients()
 376 {
 377   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting all clients from the database");
 378
 379   std::unique_lock<CCriticalSection> lock(m_critSection);
 380   return DeleteValues("clients");
 381 }
 382
 383 bool CPVRDatabase::Persist(const CPVRClient& client)
 384 {
 385   if (client.GetID() == PVR_INVALID_CLIENT_ID)
 386     return false;
 387
 388   CLog::LogFC(LOGDEBUG, LOGPVR, "Persisting client {} to database", client.GetID());
 389
 390   std::unique_lock<CCriticalSection> lock(m_critSection);
 391
 392   const std::string strQuery = PrepareSQL("REPLACE INTO clients (idClient, iPriority) VALUES (%i, %i);",
 393                                           client.GetID(), client.GetPriority());
 394
 395   return ExecuteQuery(strQuery);
 396 }
 397
 398 bool CPVRDatabase::Delete(const CPVRClient& client)
 399 {
 400   if (client.GetID() == PVR_INVALID_CLIENT_ID)
 401     return false;
 402
 403   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting client {} from the database", client.GetID());
 404
 405   std::unique_lock<CCriticalSection> lock(m_critSection);
 406
 407   Filter filter;
 408   filter.AppendWhere(PrepareSQL("idClient = '%i'", client.GetID()));
 409
 410   return DeleteValues("clients", filter);
 411 }
 412
 413 int CPVRDatabase::GetPriority(const CPVRClient& client) const
 414 {
 415   if (client.GetID() == PVR_INVALID_CLIENT_ID)
 416     return 0;
 417
 418   CLog::LogFC(LOGDEBUG, LOGPVR, "Getting priority for client {} from the database", client.GetID());
 419
 420   std::unique_lock<CCriticalSection> lock(m_critSection);
 421
 422   const std::string strWhereClause = PrepareSQL("idClient = '%i'", client.GetID());
 423   const std::string strValue = GetSingleValue("clients", "iPriority", strWhereClause);
 424
 425   if (strValue.empty())
 426     return 0;
 427
 428   return atoi(strValue.c_str());
 429 }
 430
 431 /********** Channel provider methods **********/
 432
 433 bool CPVRDatabase::DeleteProviders()
 434 {
 435   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting all providers from the database");
 436
 437   std::unique_lock<CCriticalSection> lock(m_critSection);
 438   return DeleteValues("providers");
 439 }
 440
 441 bool CPVRDatabase::Persist(CPVRProvider& provider, bool updateRecord /* = false */)
 442 {
 443   bool bReturn = false;
 444   if (provider.GetName().empty())
 445   {
 446     CLog::LogF(LOGERROR, "Empty provider name");
 447     return bReturn;
 448   }
 449
 450   std::string strQuery;
 451
 452   std::unique_lock<CCriticalSection> lock(m_critSection);
 453   {
 454     /* insert a new entry when this is a new group, or replace the existing one otherwise */
 455     if (!updateRecord)
 456       strQuery =
 457           PrepareSQL("INSERT INTO providers (idProvider, iUniqueId, iClientId, sName, "
 458                      "iType, sIconPath, sCountries, sLanguages) "
 459                      "VALUES (%i, %i, %i, '%s', %i, '%s', '%s', '%s');",
 460                      provider.GetDatabaseId(), provider.GetUniqueId(), provider.GetClientId(),
 461                      provider.GetName().c_str(), static_cast<int>(provider.GetType()),
 462                      provider.GetClientIconPath().c_str(), provider.GetCountriesDBString().c_str(),
 463                      provider.GetLanguagesDBString().c_str());
 464     else
 465       strQuery =
 466           PrepareSQL("REPLACE INTO providers (idProvider, iUniqueId, iClientId, sName, "
 467                      "iType, sIconPath, sCountries, sLanguages) "
 468                      "VALUES (%i, %i, %i, '%s', %i, '%s', '%s', '%s');",
 469                      provider.GetDatabaseId(), provider.GetUniqueId(), provider.GetClientId(),
 470                      provider.GetName().c_str(), static_cast<int>(provider.GetType()),
 471                      provider.GetClientIconPath().c_str(), provider.GetCountriesDBString().c_str(),
 472                      provider.GetLanguagesDBString().c_str());
 473
 474     bReturn = ExecuteQuery(strQuery);
 475
 476     /* set the provider id if it was <= 0 */
 477     if (bReturn && provider.GetDatabaseId() <= 0)
 478     {
 479       provider.SetDatabaseId(static_cast<int>(m_pDS->lastinsertid()));
 480     }
 481   }
 482
 483   return bReturn;
 484 }
 485
 486 bool CPVRDatabase::Delete(const CPVRProvider& provider)
 487 {
 488   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting provider '{}' from the database",
 489               provider.GetName());
 490
 491   std::unique_lock<CCriticalSection> lock(m_critSection);
 492
 493   Filter filter;
 494   filter.AppendWhere(PrepareSQL("idProvider = '%i'", provider.GetDatabaseId()));
 495
 496   return DeleteValues("providers", filter);
 497 }
 498
 499 bool CPVRDatabase::Get(CPVRProviders& results,
 500                        const std::vector<std::shared_ptr<CPVRClient>>& clients) const
 501 {
 502   bool bReturn = false;
 503
 504   std::string strQuery = "SELECT * from providers ";
 505   const std::string clientIds = GetClientIdsSQL(clients);
 506   if (!clientIds.empty())
 507     strQuery += "WHERE " + clientIds + " OR iType = 1"; // always load addon providers
 508
 509   std::unique_lock<CCriticalSection> lock(m_critSection);
 510   strQuery = PrepareSQL(strQuery);
 511   if (ResultQuery(strQuery))
 512   {
 513     try
 514     {
 515       while (!m_pDS->eof())
 516       {
 517         std::shared_ptr<CPVRProvider> provider = std::make_shared<CPVRProvider>(
 518             m_pDS->fv("iUniqueId").get_asInt(), m_pDS->fv("iClientId").get_asInt());
 519
 520         provider->SetDatabaseId(m_pDS->fv("idProvider").get_asInt());
 521         provider->SetName(m_pDS->fv("sName").get_asString());
 522         provider->SetType(
 523             static_cast<PVR_PROVIDER_TYPE>(m_pDS->fv("iType").get_asInt()));
 524         provider->SetIconPath(m_pDS->fv("sIconPath").get_asString());
 525         provider->SetCountriesFromDBString(m_pDS->fv("sCountries").get_asString());
 526         provider->SetLanguagesFromDBString(m_pDS->fv("sLanguages").get_asString());
 527
 528         results.CheckAndAddEntry(provider, ProviderUpdateMode::BY_DATABASE);
 529
 530         CLog::LogFC(LOGDEBUG, LOGPVR, "Channel Provider '{}' loaded from PVR database",
 531                     provider->GetName());
 532         m_pDS->next();
 533       }
 534
 535       m_pDS->close();
 536       bReturn = true;
 537     }
 538     catch (...)
 539     {
 540       CLog::LogF(LOGERROR, "Couldn't load providers from PVR database");
 541     }
 542   }
 543
 544   return bReturn;
 545 }
 546
 547 int CPVRDatabase::GetMaxProviderId() const
 548 {
 549   std::string strQuery = PrepareSQL("SELECT max(idProvider) as maxProviderId from providers");
 550   std::unique_lock<CCriticalSection> lock(m_critSection);
 551
 552   return GetSingleValueInt(strQuery);
 553 }
 554
 555 /********** Channel methods **********/
 556
 557 int CPVRDatabase::Get(bool bRadio,
 558                       const std::vector<std::shared_ptr<CPVRClient>>& clients,
 559                       std::map<std::pair<int, int>, std::shared_ptr<CPVRChannel>>& results) const
 560 {
 561   int iReturn = 0;
 562
 563   std::string strQuery = "SELECT * from channels WHERE bIsRadio = %u ";
 564   const std::string clientIds = GetClientIdsSQL(clients);
 565   if (!clientIds.empty())
 566     strQuery += "AND " + clientIds;
 567
 568   std::unique_lock<CCriticalSection> lock(m_critSection);
 569   strQuery = PrepareSQL(strQuery, bRadio);
 570   if (ResultQuery(strQuery))
 571   {
 572     try
 573     {
 574       while (!m_pDS->eof())
 575       {
 576         const std::shared_ptr<CPVRChannel> channel(new CPVRChannel(
 577             m_pDS->fv("bIsRadio").get_asBool(), m_pDS->fv("sIconPath").get_asString()));
 578
 579         channel->m_iChannelId = m_pDS->fv("idChannel").get_asInt();
 580         channel->m_iUniqueId = m_pDS->fv("iUniqueId").get_asInt();
 581         channel->m_bIsHidden = m_pDS->fv("bIsHidden").get_asBool();
 582         channel->m_bIsUserSetIcon = m_pDS->fv("bIsUserSetIcon").get_asBool();
 583         channel->m_bIsUserSetName = m_pDS->fv("bIsUserSetName").get_asBool();
 584         channel->m_bIsLocked = m_pDS->fv("bIsLocked").get_asBool();
 585         channel->m_strChannelName = m_pDS->fv("sChannelName").get_asString();
 586         channel->m_bEPGEnabled = m_pDS->fv("bEPGEnabled").get_asBool();
 587         channel->m_strEPGScraper = m_pDS->fv("sEPGScraper").get_asString();
 588         channel->m_iLastWatched = static_cast<time_t>(m_pDS->fv("iLastWatched").get_asInt());
 589         channel->m_iClientId = m_pDS->fv("iClientId").get_asInt();
 590         channel->m_iEpgId = m_pDS->fv("idEpg").get_asInt();
 591         channel->m_bHasArchive = m_pDS->fv("bHasArchive").get_asBool();
 592         channel->m_iClientProviderUid = m_pDS->fv("iClientProviderUid").get_asInt();
 593         channel->m_bIsUserSetHidden = m_pDS->fv("bIsUserSetHidden").get_asBool();
 594         channel->m_lastWatchedGroupId = m_pDS->fv("iLastWatchedGroupId").get_asInt();
 595
 596         channel->UpdateEncryptionName();
 597
 598         results.insert({channel->StorageId(), channel});
 599
 600         m_pDS->next();
 601         ++iReturn;
 602       }
 603       m_pDS->close();
 604     }
 605     catch (...)
 606     {
 607       CLog::LogF(LOGERROR, "Couldn't load channels from PVR database");
 608     }
 609   }
 610   else
 611   {
 612     CLog::LogF(LOGERROR, "PVR database query failed");
 613   }
 614
 615   m_pDS->close();
 616   return iReturn;
 617 }
 618
 619 bool CPVRDatabase::DeleteChannels()
 620 {
 621   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting all channels from the database");
 622
 623   std::unique_lock<CCriticalSection> lock(m_critSection);
 624   return DeleteValues("channels");
 625 }
 626
 627 bool CPVRDatabase::QueueDeleteQuery(const CPVRChannel& channel)
 628 {
 629   /* invalid channel */
 630   if (channel.ChannelID() <= 0)
 631   {
 632     CLog::LogF(LOGERROR, "Invalid channel id: {}", channel.ChannelID());
 633     return false;
 634   }
 635
 636   CLog::LogFC(LOGDEBUG, LOGPVR, "Queueing delete for channel '{}' from the database",
 637               channel.ChannelName());
 638
 639   Filter filter;
 640   filter.AppendWhere(PrepareSQL("idChannel = %i", channel.ChannelID()));
 641
 642   std::string strQuery;
 643   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "channels"), filter, strQuery))
 644     return CDatabase::QueueDeleteQuery(strQuery);
 645
 646   return false;
 647 }
 648
 649 /********** Channel group member methods **********/
 650
 651 bool CPVRDatabase::QueueDeleteQuery(const CPVRChannelGroupMember& groupMember)
 652 {
 653   CLog::LogFC(LOGDEBUG, LOGPVR, "Queueing delete for channel group member '{}' from the database",
 654               groupMember.Channel() ? groupMember.Channel()->ChannelName()
 655                                     : std::to_string(groupMember.ChannelDatabaseID()));
 656
 657   Filter filter;
 658   filter.AppendWhere(PrepareSQL("idGroup = %i", groupMember.GroupID()));
 659   filter.AppendWhere(PrepareSQL("idChannel = %i", groupMember.ChannelDatabaseID()));
 660
 661   std::string strQuery;
 662   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "map_channelgroups_channels"), filter, strQuery))
 663     return CDatabase::QueueDeleteQuery(strQuery);
 664
 665   return false;
 666 }
 667
 668 /********** Channel group methods **********/
 669
 670 bool CPVRDatabase::RemoveChannelsFromGroup(const CPVRChannelGroup& group)
 671 {
 672   Filter filter;
 673   filter.AppendWhere(PrepareSQL("idGroup = %i", group.GroupID()));
 674
 675   std::unique_lock<CCriticalSection> lock(m_critSection);
 676   return DeleteValues("map_channelgroups_channels", filter);
 677 }
 678
 679 bool CPVRDatabase::DeleteChannelGroups()
 680 {
 681   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting all channel groups from the database");
 682
 683   std::unique_lock<CCriticalSection> lock(m_critSection);
 684   return DeleteValues("channelgroups") && DeleteValues("map_channelgroups_channels");
 685 }
 686
 687 bool CPVRDatabase::Delete(const CPVRChannelGroup& group)
 688 {
 689   /* invalid group id */
 690   if (group.GroupID() <= 0)
 691   {
 692     CLog::LogF(LOGERROR, "Invalid channel group id: {}", group.GroupID());
 693     return false;
 694   }
 695
 696   std::unique_lock<CCriticalSection> lock(m_critSection);
 697
 698   Filter filter;
 699   filter.AppendWhere(PrepareSQL("idGroup = %i", group.GroupID()));
 700   filter.AppendWhere(PrepareSQL("bIsRadio = %u", group.IsRadio()));
 701
 702   return RemoveChannelsFromGroup(group) && DeleteValues("channelgroups", filter);
 703 }
 704
 705 int CPVRDatabase::GetGroups(CPVRChannelGroups& results, const std::string& query) const
 706 {
 707   int iLoaded = 0;
 708   if (ResultQuery(query))
 709   {
 710     try
 711     {
 712       while (!m_pDS->eof())
 713       {
 714         const std::shared_ptr<CPVRChannelGroup> group = results.CreateChannelGroup(
 715             m_pDS->fv("iGroupType").get_asInt(),
 716             CPVRChannelsPath(m_pDS->fv("bIsRadio").get_asBool(), m_pDS->fv("sName").get_asString(),
 717                              m_pDS->fv("iClientId").get_asInt()));
 718
 719         group->m_iGroupId = m_pDS->fv("idGroup").get_asInt();
 720         group->m_iLastWatched = static_cast<time_t>(m_pDS->fv("iLastWatched").get_asInt());
 721         group->m_bHidden = m_pDS->fv("bIsHidden").get_asBool();
 722         group->m_iPosition = m_pDS->fv("iPosition").get_asInt();
 723         group->m_iLastOpened = static_cast<uint64_t>(m_pDS->fv("iLastOpened").get_asInt64());
 724         group->m_isUserSetName = m_pDS->fv("bIsUserSetName").get_asBool();
 725         group->m_clientGroupName = m_pDS->fv("sClientName").get_asString();
 726         group->m_iClientPosition = m_pDS->fv("iClientPosition").get_asInt();
 727         results.Update(group);
 728
 729         CLog::LogFC(LOGDEBUG, LOGPVR, "Group '{}' loaded from PVR database", group->GroupName());
 730         m_pDS->next();
 731         iLoaded++;
 732       }
 733       m_pDS->close();
 734     }
 735     catch (...)
 736     {
 737       CLog::LogF(LOGERROR, "Couldn't load channel groups from PVR database. Exception.");
 738     }
 739   }
 740   else
 741   {
 742     CLog::LogF(LOGERROR, "Couldn't load channel groups from PVR database. Query failed.");
 743   }
 744
 745   return iLoaded;
 746 }
 747
 748 int CPVRDatabase::GetLocalGroups(CPVRChannelGroups& results) const
 749 {
 750   std::unique_lock<CCriticalSection> lock(m_critSection);
 751   const std::string query = PrepareSQL(
 752       "SELECT * from channelgroups WHERE bIsRadio = %u AND iClientId = -1", results.IsRadio());
 753   return GetGroups(results, query);
 754 }
 755
 756 int CPVRDatabase::Get(CPVRChannelGroups& results,
 757                       const std::vector<std::shared_ptr<CPVRClient>>& clients) const
 758 {
 759   std::string query = "SELECT * from channelgroups WHERE bIsRadio = %u ";
 760
 761   const std::string clientIds =
 762       GetClientIdsSQL(clients, true /* add PVR_GROUP_CLIENT_ID_UNKNOWN for db data migration */);
 763   if (!clientIds.empty())
 764     query += "AND " + clientIds;
 765
 766   std::unique_lock<CCriticalSection> lock(m_critSection);
 767   query = PrepareSQL(query, results.IsRadio());
 768   return GetGroups(results, query);
 769 }
 770
 771 std::vector<std::shared_ptr<CPVRChannelGroupMember>> CPVRDatabase::Get(
 772     const CPVRChannelGroup& group, const std::vector<std::shared_ptr<CPVRClient>>& clients) const
 773 {
 774   std::vector<std::shared_ptr<CPVRChannelGroupMember>> results;
 775
 776   /* invalid group id */
 777   if (group.GroupID() < 0)
 778   {
 779     CLog::LogF(LOGERROR, "Invalid channel group id: {}", group.GroupID());
 780     return results;
 781   }
 782
 783   std::string strQuery =
 784       "SELECT map_channelgroups_channels.idChannel, "
 785       "map_channelgroups_channels.iChannelNumber, "
 786       "map_channelgroups_channels.iSubChannelNumber, "
 787       "map_channelgroups_channels.iOrder, "
 788       "map_channelgroups_channels.iClientChannelNumber, "
 789       "map_channelgroups_channels.iClientSubChannelNumber, "
 790       "channels.iClientId, channels.iUniqueId, channels.bIsRadio "
 791       "FROM map_channelgroups_channels "
 792       "LEFT JOIN channels ON channels.idChannel = map_channelgroups_channels.idChannel "
 793       "WHERE map_channelgroups_channels.idGroup = %i ";
 794   const std::string clientIds = GetClientIdsSQL(clients);
 795   if (!clientIds.empty())
 796     strQuery += "AND " + clientIds;
 797   strQuery += " ORDER BY map_channelgroups_channels.iChannelNumber";
 798
 799   std::unique_lock<CCriticalSection> lock(m_critSection);
 800   strQuery = PrepareSQL(strQuery, group.GroupID());
 801   if (ResultQuery(strQuery))
 802   {
 803     try
 804     {
 805       while (!m_pDS->eof())
 806       {
 807         const auto newMember = std::make_shared<CPVRChannelGroupMember>();
 808         newMember->m_iChannelDatabaseID = m_pDS->fv("idChannel").get_asInt();
 809         newMember->m_iChannelClientID = m_pDS->fv("iClientId").get_asInt();
 810         newMember->m_iChannelUID = m_pDS->fv("iUniqueId").get_asInt();
 811         newMember->m_iGroupID = group.GroupID();
 812         newMember->m_iGroupClientID = group.GetClientID();
 813         newMember->m_bIsRadio = m_pDS->fv("bIsRadio").get_asBool();
 814         newMember->m_channelNumber = {
 815             static_cast<unsigned int>(m_pDS->fv("iChannelNumber").get_asInt()),
 816             static_cast<unsigned int>(m_pDS->fv("iSubChannelNumber").get_asInt())};
 817         newMember->m_clientChannelNumber = {
 818             static_cast<unsigned int>(m_pDS->fv("iClientChannelNumber").get_asInt()),
 819             static_cast<unsigned int>(m_pDS->fv("iClientSubChannelNumber").get_asInt())};
 820         newMember->m_iOrder = static_cast<int>(m_pDS->fv("iOrder").get_asInt());
 821         newMember->SetGroupName(group.GroupName());
 822
 823         results.emplace_back(newMember);
 824         m_pDS->next();
 825       }
 826       m_pDS->close();
 827     }
 828     catch(...)
 829     {
 830       CLog::LogF(LOGERROR, "Failed to get channel group members");
 831     }
 832   }
 833
 834   return results;
 835 }
 836
 837 bool CPVRDatabase::PersistChannels(const CPVRChannelGroup& group)
 838 {
 839   /* invalid group id */
 840   if (group.GroupID() < 0)
 841   {
 842     CLog::LogF(LOGERROR, "Invalid channel group id: {}", group.GroupID());
 843     return false;
 844   }
 845
 846   bool bReturn(true);
 847
 848   std::shared_ptr<CPVRChannel> channel;
 849   for (const auto& groupMember : group.m_members)
 850   {
 851     channel = groupMember.second->Channel();
 852     if (channel->IsChanged() || channel->IsNew())
 853     {
 854       if (Persist(*channel, false))
 855       {
 856         channel->Persisted();
 857         bReturn = true;
 858       }
 859     }
 860   }
 861
 862   bReturn &= CommitInsertQueries();
 863
 864   if (bReturn)
 865   {
 866     std::string strQuery;
 867     std::string strValue;
 868     for (const auto& groupMember : group.m_members)
 869     {
 870       channel = groupMember.second->Channel();
 871       strQuery =
 872           PrepareSQL("iUniqueId = %i AND iClientId = %i", channel->UniqueID(), channel->ClientID());
 873       strValue = GetSingleValue("channels", "idChannel", strQuery);
 874       if (!strValue.empty() && StringUtils::IsInteger(strValue))
 875       {
 876         const int iChannelID = std::atoi(strValue.c_str());
 877         channel->SetChannelID(iChannelID);
 878         groupMember.second->m_iChannelDatabaseID = iChannelID;
 879       }
 880     }
 881   }
 882
 883   return bReturn;
 884 }
 885
 886 bool CPVRDatabase::PersistGroupMembers(const CPVRChannelGroup& group)
 887 {
 888   /* invalid group id */
 889   if (group.GroupID() < 0)
 890   {
 891     CLog::LogF(LOGERROR, "Invalid channel group id: {}", group.GroupID());
 892     return false;
 893   }
 894
 895   bool bReturn = true;
 896
 897   if (group.HasChannels())
 898   {
 899     for (const auto& groupMember : group.m_sortedMembers)
 900     {
 901       if (groupMember->NeedsSave())
 902       {
 903         if (groupMember->ChannelDatabaseID() <= 0)
 904         {
 905           CLog::LogF(LOGERROR, "Invalid channel id: {}", groupMember->ChannelDatabaseID());
 906           continue;
 907         }
 908
 909         const std::string strWhereClause =
 910             PrepareSQL("idChannel = %i AND idGroup = %i AND iChannelNumber = %u AND "
 911                        "iSubChannelNumber = %u AND "
 912                        "iOrder = %i AND iClientChannelNumber = %u AND iClientSubChannelNumber = %u",
 913                        groupMember->ChannelDatabaseID(), group.GroupID(),
 914                        groupMember->ChannelNumber().GetChannelNumber(),
 915                        groupMember->ChannelNumber().GetSubChannelNumber(), groupMember->Order(),
 916                        groupMember->ClientChannelNumber().GetChannelNumber(),
 917                        groupMember->ClientChannelNumber().GetSubChannelNumber());
 918
 919         const std::string strValue =
 920             GetSingleValue("map_channelgroups_channels", "idChannel", strWhereClause);
 921         if (strValue.empty())
 922         {
 923           const std::string strQuery =
 924               PrepareSQL("REPLACE INTO map_channelgroups_channels ("
 925                          "idGroup, idChannel, iChannelNumber, iSubChannelNumber, iOrder, "
 926                          "iClientChannelNumber, iClientSubChannelNumber) "
 927                          "VALUES (%i, %i, %i, %i, %i, %i, %i);",
 928                          group.GroupID(), groupMember->ChannelDatabaseID(),
 929                          groupMember->ChannelNumber().GetChannelNumber(),
 930                          groupMember->ChannelNumber().GetSubChannelNumber(), groupMember->Order(),
 931                          groupMember->ClientChannelNumber().GetChannelNumber(),
 932                          groupMember->ClientChannelNumber().GetSubChannelNumber());
 933           QueueInsertQuery(strQuery);
 934         }
 935       }
 936     }
 937
 938     bReturn = CommitInsertQueries();
 939
 940     if (bReturn)
 941     {
 942       for (const auto& groupMember : group.m_sortedMembers)
 943       {
 944         groupMember->SetSaved();
 945       }
 946     }
 947   }
 948
 949   return bReturn;
 950 }
 951
 952 bool CPVRDatabase::ResetEPG()
 953 {
 954   std::unique_lock<CCriticalSection> lock(m_critSection);
 955   const std::string strQuery = PrepareSQL("UPDATE channels SET idEpg = 0");
 956   return ExecuteQuery(strQuery);
 957 }
 958
 959 bool CPVRDatabase::Persist(CPVRChannelGroup& group)
 960 {
 961   bool bReturn(false);
 962   if (group.GroupName().empty())
 963   {
 964     CLog::LogF(LOGERROR, "Empty group name");
 965     return bReturn;
 966   }
 967
 968   std::string strQuery;
 969
 970   std::unique_lock<CCriticalSection> lock(m_critSection);
 971
 972   if (group.HasChanges() || group.IsNew())
 973   {
 974     /* insert a new entry when this is a new group, or replace the existing one otherwise */
 975     if (group.IsNew())
 976       strQuery = PrepareSQL("INSERT INTO channelgroups (bIsRadio, iGroupType, sName, iLastWatched, "
 977                             "bIsHidden, iPosition, iLastOpened, iClientId, bIsUserSetName, "
 978                             "sClientName, iClientPosition) VALUES (%i, %i, '%s', "
 979                             "%u, %i, %i, %llu, %i, %i, '%s', %i)",
 980                             (group.IsRadio() ? 1 : 0), group.GroupType(), group.GroupName().c_str(),
 981                             static_cast<unsigned int>(group.LastWatched()), group.IsHidden(),
 982                             group.GetPosition(), group.LastOpened(), group.GetClientID(),
 983                             (group.IsUserSetName() ? 1 : 0), group.ClientGroupName().c_str(),
 984                             group.GetClientPosition());
 985     else
 986       strQuery = PrepareSQL(
 987           "REPLACE INTO channelgroups (idGroup, bIsRadio, iGroupType, sName, iLastWatched, "
 988           "bIsHidden, iPosition, iLastOpened, iClientId, bIsUserSetName, sClientName, "
 989           "iClientPosition) VALUES (%i, %i, %i, '%s', %u, %i, %i, %llu, %i, %i, '%s', %i)",
 990           group.GroupID(), (group.IsRadio() ? 1 : 0), group.GroupType(), group.GroupName().c_str(),
 991           static_cast<unsigned int>(group.LastWatched()), group.IsHidden(), group.GetPosition(),
 992           group.LastOpened(), group.GetClientID(), (group.IsUserSetName() ? 1 : 0),
 993           group.ClientGroupName().c_str(), group.GetClientPosition());
 994
 995     bReturn = ExecuteQuery(strQuery);
 996
 997     // set the group ID for new groups
 998     if (bReturn && group.IsNew())
 999       group.SetGroupID(static_cast<int>(m_pDS->lastinsertid()));
1000   }
1001   else
1002     bReturn = true;
1003
1004   // only persist the channel data for the all channels groups as all groups
1005   // share the same channel instances and all groups has them all.
1006   if (group.IsChannelsOwner())
1007     bReturn &= PersistChannels(group);
1008
1009   /* persist the group member entries */
1010   if (bReturn)
1011     bReturn = PersistGroupMembers(group);
1012
1013   return bReturn;
1014 }
1015
1016 bool CPVRDatabase::Persist(CPVRChannel& channel, bool bCommit)
1017 {
1018   bool bReturn(false);
1019
1020   /* invalid channel */
1021   if (channel.UniqueID() <= 0)
1022   {
1023     CLog::LogF(LOGERROR, "Invalid channel uid: {}", channel.UniqueID());
1024     return bReturn;
1025   }
1026
1027   std::unique_lock<CCriticalSection> lock(m_critSection);
1028
1029   // Note: Do not use channel.ChannelID value to check presence of channel in channels table. It might not yet be set correctly.
1030   std::string strQuery =
1031       PrepareSQL("iUniqueId = %i AND iClientId = %i", channel.UniqueID(), channel.ClientID());
1032   const std::string strValue = GetSingleValue("channels", "idChannel", strQuery);
1033   if (strValue.empty())
1034   {
1035     /* new channel */
1036     strQuery = PrepareSQL(
1037         "INSERT INTO channels ("
1038         "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsUserSetName, bIsLocked, "
1039         "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
1040         "idEpg, bHasArchive, iClientProviderUid, bIsUserSetHidden, iLastWatchedGroupId) "
1041         "VALUES (%i, %i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i)",
1042         channel.UniqueID(), (channel.IsRadio() ? 1 : 0), (channel.IsHidden() ? 1 : 0),
1043         (channel.IsUserSetIcon() ? 1 : 0), (channel.IsUserSetName() ? 1 : 0),
1044         (channel.IsLocked() ? 1 : 0), channel.IconPath().c_str(), channel.ChannelName().c_str(), 0,
1045         (channel.EPGEnabled() ? 1 : 0), channel.EPGScraper().c_str(),
1046         static_cast<unsigned int>(channel.LastWatched()), channel.ClientID(), channel.EpgID(),
1047         channel.HasArchive(), channel.ClientProviderUid(), channel.IsUserSetHidden() ? 1 : 0,
1048         channel.LastWatchedGroupId());
1049   }
1050   else
1051   {
1052     /* update channel */
1053     strQuery = PrepareSQL(
1054         "REPLACE INTO channels ("
1055         "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsUserSetName, bIsLocked, "
1056         "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
1057         "idChannel, idEpg, bHasArchive, iClientProviderUid, bIsUserSetHidden, iLastWatchedGroupId) "
1058         "VALUES (%i, %i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %s, %i, %i, %i, %i, %i)",
1059         channel.UniqueID(), (channel.IsRadio() ? 1 : 0), (channel.IsHidden() ? 1 : 0),
1060         (channel.IsUserSetIcon() ? 1 : 0), (channel.IsUserSetName() ? 1 : 0),
1061         (channel.IsLocked() ? 1 : 0), channel.ClientIconPath().c_str(),
1062         channel.ChannelName().c_str(), 0, (channel.EPGEnabled() ? 1 : 0),
1063         channel.EPGScraper().c_str(), static_cast<unsigned int>(channel.LastWatched()),
1064         channel.ClientID(), strValue.c_str(), channel.EpgID(), channel.HasArchive(),
1065         channel.ClientProviderUid(), channel.IsUserSetHidden() ? 1 : 0,
1066         channel.LastWatchedGroupId());
1067   }
1068
1069   if (QueueInsertQuery(strQuery))
1070   {
1071     bReturn = true;
1072
1073     if (bCommit)
1074       bReturn = CommitInsertQueries();
1075   }
1076
1077   return bReturn;
1078 }
1079
1080 bool CPVRDatabase::UpdateLastWatched(const CPVRChannel& channel, int groupId)
1081 {
1082   std::unique_lock<CCriticalSection> lock(m_critSection);
1083   const std::string strQuery = PrepareSQL(
1084       "UPDATE channels SET iLastWatched = %u, iLastWatchedGroupId = %i WHERE idChannel = %i",
1085       static_cast<unsigned int>(channel.LastWatched()), groupId, channel.ChannelID());
1086   return ExecuteQuery(strQuery);
1087 }
1088
1089 bool CPVRDatabase::UpdateLastWatched(const CPVRChannelGroup& group)
1090 {
1091   std::unique_lock<CCriticalSection> lock(m_critSection);
1092   const std::string strQuery =
1093       PrepareSQL("UPDATE channelgroups SET iLastWatched = %u WHERE idGroup = %i",
1094                  static_cast<unsigned int>(group.LastWatched()), group.GroupID());
1095   return ExecuteQuery(strQuery);
1096 }
1097
1098 bool CPVRDatabase::UpdateLastOpened(const CPVRChannelGroup& group)
1099 {
1100   std::unique_lock<CCriticalSection> lock(m_critSection);
1101   const std::string strQuery =
1102       PrepareSQL("UPDATE channelgroups SET iLastOpened = %llu WHERE idGroup = %i",
1103                  group.LastOpened(), group.GroupID());
1104   return ExecuteQuery(strQuery);
1105 }
1106
1107 /********** Timer methods **********/
1108
1109 std::vector<std::shared_ptr<CPVRTimerInfoTag>> CPVRDatabase::GetTimers(
1110     CPVRTimers& timers, const std::vector<std::shared_ptr<CPVRClient>>& clients) const
1111 {
1112   std::vector<std::shared_ptr<CPVRTimerInfoTag>> result;
1113
1114   std::string strQuery = "SELECT * FROM timers ";
1115   const std::string clientIds = GetClientIdsSQL(clients);
1116   if (!clientIds.empty())
1117     strQuery += "WHERE " + clientIds;
1118
1119   std::unique_lock<CCriticalSection> lock(m_critSection);
1120   strQuery = PrepareSQL(strQuery);
1121   if (ResultQuery(strQuery))
1122   {
1123     try
1124     {
1125       while (!m_pDS->eof())
1126       {
1127         std::shared_ptr<CPVRTimerInfoTag> newTag(new CPVRTimerInfoTag());
1128
1129         newTag->m_iClientIndex = -m_pDS->fv("iClientIndex").get_asInt();
1130         newTag->m_iParentClientIndex = m_pDS->fv("iParentClientIndex").get_asInt();
1131         newTag->m_iClientId = m_pDS->fv("iClientId").get_asInt();
1132         newTag->SetTimerType(CPVRTimerType::CreateFromIds(m_pDS->fv("iTimerType").get_asInt(), -1));
1133         newTag->m_state = static_cast<PVR_TIMER_STATE>(m_pDS->fv("iState").get_asInt());
1134         newTag->m_strTitle = m_pDS->fv("sTitle").get_asString().c_str();
1135         newTag->m_iClientChannelUid = m_pDS->fv("iClientChannelUid").get_asInt();
1136         newTag->m_strSeriesLink = m_pDS->fv("sSeriesLink").get_asString().c_str();
1137         newTag->SetStartFromUTC(CDateTime::FromDBDateTime(m_pDS->fv("sStartTime").get_asString().c_str()));
1138         newTag->m_bStartAnyTime = m_pDS->fv("bStartAnyTime").get_asBool();
1139         newTag->SetEndFromUTC(CDateTime::FromDBDateTime(m_pDS->fv("sEndTime").get_asString().c_str()));
1140         newTag->m_bEndAnyTime = m_pDS->fv("bEndAnyTime").get_asBool();
1141         newTag->SetFirstDayFromUTC(CDateTime::FromDBDateTime(m_pDS->fv("sFirstDay").get_asString().c_str()));
1142         newTag->m_iWeekdays = m_pDS->fv("iWeekdays").get_asInt();
1143         newTag->m_iEpgUid = m_pDS->fv("iEpgUid").get_asInt();
1144         newTag->m_iMarginStart = m_pDS->fv("iMarginStart").get_asInt();
1145         newTag->m_iMarginEnd = m_pDS->fv("iMarginEnd").get_asInt();
1146         newTag->m_strEpgSearchString = m_pDS->fv("sEpgSearchString").get_asString().c_str();
1147         newTag->m_bFullTextEpgSearch = m_pDS->fv("bFullTextEpgSearch").get_asBool();
1148         newTag->m_iPreventDupEpisodes = m_pDS->fv("iPreventDuplicates").get_asInt();
1149         newTag->m_iPriority = m_pDS->fv("iPrority").get_asInt();
1150         newTag->m_iLifetime = m_pDS->fv("iLifetime").get_asInt();
1151         newTag->m_iMaxRecordings = m_pDS->fv("iMaxRecordings").get_asInt();
1152         newTag->m_iRecordingGroup = m_pDS->fv("iRecordingGroup").get_asInt();
1153         newTag->UpdateSummary();
1154
1155         result.emplace_back(newTag);
1156
1157         m_pDS->next();
1158       }
1159       m_pDS->close();
1160     }
1161     catch (...)
1162     {
1163       CLog::LogF(LOGERROR, "Could not load timer data from the database");
1164     }
1165   }
1166   return result;
1167 }
1168
1169 bool CPVRDatabase::Persist(CPVRTimerInfoTag& timer)
1170 {
1171   std::unique_lock<CCriticalSection> lock(m_critSection);
1172
1173   // insert a new entry if this is a new timer, or replace the existing one otherwise
1174   std::string strQuery;
1175   if (timer.m_iClientIndex == PVR_TIMER_NO_CLIENT_INDEX)
1176     strQuery = PrepareSQL("INSERT INTO timers "
1177                           "(iParentClientIndex, iClientId, iTimerType, iState, sTitle, iClientChannelUid, sSeriesLink, sStartTime,"
1178                           " bStartAnyTime, sEndTime, bEndAnyTime, sFirstDay, iWeekdays, iEpgUid, iMarginStart, iMarginEnd,"
1179                           " sEpgSearchString, bFullTextEpgSearch, iPreventDuplicates, iPrority, iLifetime, iMaxRecordings, iRecordingGroup) "
1180                           "VALUES (%i, %i, %u, %i, '%s', %i, '%s', '%s', %i, '%s', %i, '%s', %i, %u, %i, %i, '%s', %i, %i, %i, %i, %i, %i);",
1181                           timer.m_iParentClientIndex, timer.m_iClientId, timer.GetTimerType()->GetTypeId(), timer.m_state,
1182                           timer.Title().c_str(), timer.m_iClientChannelUid, timer.SeriesLink().c_str(),
1183                           timer.StartAsUTC().GetAsDBDateTime().c_str(), timer.m_bStartAnyTime ? 1 : 0,
1184                           timer.EndAsUTC().GetAsDBDateTime().c_str(), timer.m_bEndAnyTime ? 1 : 0,
1185                           timer.FirstDayAsUTC().GetAsDBDateTime().c_str(), timer.m_iWeekdays, timer.UniqueBroadcastID(),
1186                           timer.m_iMarginStart, timer.m_iMarginEnd, timer.m_strEpgSearchString.c_str(), timer.m_bFullTextEpgSearch ? 1 : 0,
1187                           timer.m_iPreventDupEpisodes, timer.m_iPriority, timer.m_iLifetime, timer.m_iMaxRecordings, timer.m_iRecordingGroup);
1188   else
1189     strQuery = PrepareSQL("REPLACE INTO timers "
1190                           "(iClientIndex,"
1191                           " iParentClientIndex, iClientId, iTimerType, iState, sTitle, iClientChannelUid, sSeriesLink, sStartTime,"
1192                           " bStartAnyTime, sEndTime, bEndAnyTime, sFirstDay, iWeekdays, iEpgUid, iMarginStart, iMarginEnd,"
1193                           " sEpgSearchString, bFullTextEpgSearch, iPreventDuplicates, iPrority, iLifetime, iMaxRecordings, iRecordingGroup) "
1194                           "VALUES (%i, %i, %i, %u, %i, '%s', %i, '%s', '%s', %i, '%s', %i, '%s', %i, %u, %i, %i, '%s', %i, %i, %i, %i, %i, %i);",
1195                           -timer.m_iClientIndex,
1196                           timer.m_iParentClientIndex, timer.m_iClientId, timer.GetTimerType()->GetTypeId(), timer.m_state,
1197                           timer.Title().c_str(), timer.m_iClientChannelUid, timer.SeriesLink().c_str(),
1198                           timer.StartAsUTC().GetAsDBDateTime().c_str(), timer.m_bStartAnyTime ? 1 : 0,
1199                           timer.EndAsUTC().GetAsDBDateTime().c_str(), timer.m_bEndAnyTime ? 1 : 0,
1200                           timer.FirstDayAsUTC().GetAsDBDateTime().c_str(), timer.m_iWeekdays, timer.UniqueBroadcastID(),
1201                           timer.m_iMarginStart, timer.m_iMarginEnd, timer.m_strEpgSearchString.c_str(), timer.m_bFullTextEpgSearch ? 1 : 0,
1202                           timer.m_iPreventDupEpisodes, timer.m_iPriority, timer.m_iLifetime, timer.m_iMaxRecordings, timer.m_iRecordingGroup);
1203
1204   bool bReturn = ExecuteQuery(strQuery);
1205
1206   // set the client index for just inserted timers
1207   if (bReturn && timer.m_iClientIndex == PVR_TIMER_NO_CLIENT_INDEX)
1208   {
1209     // index must be negative for local timers!
1210     timer.m_iClientIndex = -static_cast<int>(m_pDS->lastinsertid());
1211   }
1212
1213   return bReturn;
1214 }
1215
1216 bool CPVRDatabase::Delete(const CPVRTimerInfoTag& timer)
1217 {
1218   if (timer.m_iClientIndex == PVR_TIMER_NO_CLIENT_INDEX)
1219     return false;
1220
1221   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting timer '{}' from the database", timer.m_iClientIndex);
1222
1223   std::unique_lock<CCriticalSection> lock(m_critSection);
1224
1225   Filter filter;
1226   filter.AppendWhere(PrepareSQL("iClientIndex = '%i'", -timer.m_iClientIndex));
1227
1228   return DeleteValues("timers", filter);
1229 }
1230
1231 bool CPVRDatabase::DeleteTimers()
1232 {
1233   CLog::LogFC(LOGDEBUG, LOGPVR, "Deleting all timers from the database");
1234
1235   std::unique_lock<CCriticalSection> lock(m_critSection);
1236   return DeleteValues("timers");
1237 }