xbmc/pvr/epg/EpgDatabase.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 "EpgDatabase.h"
  10
  11 #include "ServiceBroker.h"
  12 #include "dbwrappers/dataset.h"
  13 #include "pvr/epg/Epg.h"
  14 #include "pvr/epg/EpgInfoTag.h"
  15 #include "pvr/epg/EpgSearchData.h"
  16 #include "pvr/epg/EpgSearchFilter.h"
  17 #include "settings/AdvancedSettings.h"
  18 #include "settings/SettingsComponent.h"
  19 #include "utils/StringUtils.h"
  20 #include "utils/log.h"
  21
  22 #include <memory>
  23 #include <mutex>
  24 #include <string>
  25 #include <vector>
  26
  27 using namespace dbiplus;
  28 using namespace PVR;
  29
  30 bool CPVREpgDatabase::Open()
  31 {
  32   std::unique_lock<CCriticalSection> lock(m_critSection);
  33   return CDatabase::Open(CServiceBroker::GetSettingsComponent()->GetAdvancedSettings()->m_databaseEpg);
  34 }
  35
  36 void CPVREpgDatabase::Close()
  37 {
  38   std::unique_lock<CCriticalSection> lock(m_critSection);
  39   CDatabase::Close();
  40 }
  41
  42 void CPVREpgDatabase::Lock()
  43 {
  44   m_critSection.lock();
  45 }
  46
  47 void CPVREpgDatabase::Unlock()
  48 {
  49   m_critSection.unlock();
  50 }
  51
  52 void CPVREpgDatabase::CreateTables()
  53 {
  54   CLog::Log(LOGINFO, "Creating EPG database tables");
  55
  56   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'epg'");
  57
  58   std::unique_lock<CCriticalSection> lock(m_critSection);
  59
  60   m_pDS->exec(
  61       "CREATE TABLE epg ("
  62         "idEpg           integer primary key, "
  63         "sName           varchar(64),"
  64         "sScraperName    varchar(32)"
  65       ")"
  66   );
  67
  68   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'epgtags'");
  69   m_pDS->exec(
  70       "CREATE TABLE epgtags ("
  71         "idBroadcast     integer primary key, "
  72         "iBroadcastUid   integer, "
  73         "idEpg           integer, "
  74         "sTitle          varchar(128), "
  75         "sPlotOutline    text, "
  76         "sPlot           text, "
  77         "sOriginalTitle  varchar(128), "
  78         "sCast           varchar(255), "
  79         "sDirector       varchar(255), "
  80         "sWriter         varchar(255), "
  81         "iYear           integer, "
  82         "sIMDBNumber     varchar(50), "
  83         "sIconPath       varchar(255), "
  84         "iStartTime      integer, "
  85         "iEndTime        integer, "
  86         "iGenreType      integer, "
  87         "iGenreSubType   integer, "
  88         "sGenre          varchar(128), "
  89         "sFirstAired     varchar(32), "
  90         "iParentalRating integer, "
  91         "iStarRating     integer, "
  92         "iSeriesId       integer, "
  93         "iEpisodeId      integer, "
  94         "iEpisodePart    integer, "
  95         "sEpisodeName    varchar(128), "
  96         "iFlags          integer, "
  97         "sSeriesLink     varchar(255), "
  98         "sParentalRatingCode varchar(64)"
  99       ")"
 100   );
 101
 102   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'lastepgscan'");
 103   m_pDS->exec("CREATE TABLE lastepgscan ("
 104         "idEpg integer primary key, "
 105         "sLastScan varchar(20)"
 106       ")"
 107   );
 108
 109   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'savedsearches'");
 110   m_pDS->exec("CREATE TABLE savedsearches ("
 111               "idSearch                  integer primary key,"
 112               "sTitle                    varchar(255), "
 113               "sLastExecutedDateTime     varchar(20), "
 114               "sSearchTerm               varchar(255), "
 115               "bSearchInDescription      bool, "
 116               "iGenreType                integer, "
 117               "sStartDateTime            varchar(20), "
 118               "sEndDateTime              varchar(20), "
 119               "bIsCaseSensitive          bool, "
 120               "iMinimumDuration          integer, "
 121               "iMaximumDuration          integer, "
 122               "bIsRadio                  bool, "
 123               "iClientId                 integer, "
 124               "iChannelUid               integer, "
 125               "bIncludeUnknownGenres     bool, "
 126               "bRemoveDuplicates         bool, "
 127               "bIgnoreFinishedBroadcasts bool, "
 128               "bIgnoreFutureBroadcasts   bool, "
 129               "bFreeToAirOnly            bool, "
 130               "bIgnorePresentTimers      bool, "
 131               "bIgnorePresentRecordings  bool,"
 132               "iChannelGroup             integer"
 133               ")");
 134 }
 135
 136 void CPVREpgDatabase::CreateAnalytics()
 137 {
 138   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating EPG database indices");
 139
 140   std::unique_lock<CCriticalSection> lock(m_critSection);
 141   m_pDS->exec("CREATE UNIQUE INDEX idx_epg_idEpg_iStartTime on epgtags(idEpg, iStartTime desc);");
 142   m_pDS->exec("CREATE INDEX idx_epg_iEndTime on epgtags(iEndTime);");
 143 }
 144
 145 void CPVREpgDatabase::UpdateTables(int iVersion)
 146 {
 147   std::unique_lock<CCriticalSection> lock(m_critSection);
 148   if (iVersion < 5)
 149     m_pDS->exec("ALTER TABLE epgtags ADD sGenre varchar(128);");
 150
 151   if (iVersion < 9)
 152     m_pDS->exec("ALTER TABLE epgtags ADD sIconPath varchar(255);");
 153
 154   if (iVersion < 10)
 155   {
 156     m_pDS->exec("ALTER TABLE epgtags ADD sOriginalTitle varchar(128);");
 157     m_pDS->exec("ALTER TABLE epgtags ADD sCast varchar(255);");
 158     m_pDS->exec("ALTER TABLE epgtags ADD sDirector varchar(255);");
 159     m_pDS->exec("ALTER TABLE epgtags ADD sWriter varchar(255);");
 160     m_pDS->exec("ALTER TABLE epgtags ADD iYear integer;");
 161     m_pDS->exec("ALTER TABLE epgtags ADD sIMDBNumber varchar(50);");
 162   }
 163
 164   if (iVersion < 11)
 165   {
 166     m_pDS->exec("ALTER TABLE epgtags ADD iFlags integer;");
 167   }
 168
 169   if (iVersion < 12)
 170   {
 171     m_pDS->exec("ALTER TABLE epgtags ADD sSeriesLink varchar(255);");
 172   }
 173
 174   if (iVersion < 13)
 175   {
 176     const bool isMySQL = StringUtils::EqualsNoCase(
 177         CServiceBroker::GetSettingsComponent()->GetAdvancedSettings()->m_databaseEpg.type, "mysql");
 178
 179     m_pDS->exec(
 180         "CREATE TABLE epgtags_new ("
 181         "idBroadcast     integer primary key, "
 182         "iBroadcastUid   integer, "
 183         "idEpg           integer, "
 184         "sTitle          varchar(128), "
 185         "sPlotOutline    text, "
 186         "sPlot           text, "
 187         "sOriginalTitle  varchar(128), "
 188         "sCast           varchar(255), "
 189         "sDirector       varchar(255), "
 190         "sWriter         varchar(255), "
 191         "iYear           integer, "
 192         "sIMDBNumber     varchar(50), "
 193         "sIconPath       varchar(255), "
 194         "iStartTime      integer, "
 195         "iEndTime        integer, "
 196         "iGenreType      integer, "
 197         "iGenreSubType   integer, "
 198         "sGenre          varchar(128), "
 199         "sFirstAired     varchar(32), "
 200         "iParentalRating integer, "
 201         "iStarRating     integer, "
 202         "iSeriesId       integer, "
 203         "iEpisodeId      integer, "
 204         "iEpisodePart    integer, "
 205         "sEpisodeName    varchar(128), "
 206         "iFlags          integer, "
 207         "sSeriesLink     varchar(255)"
 208         ")"
 209     );
 210
 211     m_pDS->exec(
 212         "INSERT INTO epgtags_new ("
 213         "idBroadcast, "
 214         "iBroadcastUid, "
 215         "idEpg, "
 216         "sTitle, "
 217         "sPlotOutline, "
 218         "sPlot, "
 219         "sOriginalTitle, "
 220         "sCast, "
 221         "sDirector, "
 222         "sWriter, "
 223         "iYear, "
 224         "sIMDBNumber, "
 225         "sIconPath, "
 226         "iStartTime, "
 227         "iEndTime, "
 228         "iGenreType, "
 229         "iGenreSubType, "
 230         "sGenre, "
 231         "sFirstAired, "
 232         "iParentalRating, "
 233         "iStarRating, "
 234         "iSeriesId, "
 235         "iEpisodeId, "
 236         "iEpisodePart, "
 237         "sEpisodeName, "
 238         "iFlags, "
 239         "sSeriesLink"
 240         ") "
 241         "SELECT "
 242         "idBroadcast, "
 243         "iBroadcastUid, "
 244         "idEpg, "
 245         "sTitle, "
 246         "sPlotOutline, "
 247         "sPlot, "
 248         "sOriginalTitle, "
 249         "sCast, "
 250         "sDirector, "
 251         "sWriter, "
 252         "iYear, "
 253         "sIMDBNumber, "
 254         "sIconPath, "
 255         "iStartTime, "
 256         "iEndTime, "
 257         "iGenreType, "
 258         "iGenreSubType, "
 259         "sGenre, "
 260         "'' AS sFirstAired, "
 261         "iParentalRating, "
 262         "iStarRating, "
 263         "iSeriesId, "
 264         "iEpisodeId, "
 265         "iEpisodePart, "
 266         "sEpisodeName, "
 267         "iFlags, "
 268         "sSeriesLink "
 269         "FROM epgtags"
 270     );
 271
 272     if (isMySQL)
 273       m_pDS->exec(
 274         "UPDATE epgtags_new INNER JOIN epgtags ON epgtags_new.idBroadcast = epgtags.idBroadcast "
 275         "SET epgtags_new.sFirstAired = DATE(FROM_UNIXTIME(epgtags.iFirstAired)) "
 276         "WHERE epgtags.iFirstAired > 0"
 277       );
 278     else
 279       m_pDS->exec(
 280         "UPDATE epgtags_new SET sFirstAired = "
 281         "COALESCE((SELECT STRFTIME('%Y-%m-%d', iFirstAired, 'UNIXEPOCH') "
 282         "FROM epgtags WHERE epgtags.idBroadcast = epgtags_new.idBroadcast "
 283         "AND epgtags.iFirstAired > 0), '')"
 284       );
 285
 286     m_pDS->exec("DROP TABLE epgtags");
 287     m_pDS->exec("ALTER TABLE epgtags_new RENAME TO epgtags");
 288   }
 289
 290   if (iVersion < 14)
 291   {
 292     m_pDS->exec("ALTER TABLE epgtags ADD sParentalRatingCode varchar(64);");
 293   }
 294
 295   if (iVersion < 15)
 296   {
 297     m_pDS->exec("CREATE TABLE savedsearches ("
 298                 "idSearch                  integer primary key,"
 299                 "sTitle                    varchar(255), "
 300                 "sLastExecutedDateTime     varchar(20), "
 301                 "sSearchTerm               varchar(255), "
 302                 "bSearchInDescription      bool, "
 303                 "iGenreType                integer, "
 304                 "sStartDateTime            varchar(20), "
 305                 "sEndDateTime              varchar(20), "
 306                 "bIsCaseSensitive          bool, "
 307                 "iMinimumDuration          integer, "
 308                 "iMaximumDuration          integer, "
 309                 "bIsRadio                  bool, "
 310                 "iClientId                 integer, "
 311                 "iChannelUid               integer, "
 312                 "bIncludeUnknownGenres     bool, "
 313                 "bRemoveDuplicates         bool, "
 314                 "bIgnoreFinishedBroadcasts bool, "
 315                 "bIgnoreFutureBroadcasts   bool, "
 316                 "bFreeToAirOnly            bool, "
 317                 "bIgnorePresentTimers      bool, "
 318                 "bIgnorePresentRecordings  bool"
 319                 ")");
 320   }
 321
 322   if (iVersion < 16)
 323   {
 324     m_pDS->exec("ALTER TABLE savedsearches ADD iChannelGroup integer;");
 325     m_pDS->exec("UPDATE savedsearches SET iChannelGroup = -1");
 326   }
 327 }
 328
 329 bool CPVREpgDatabase::DeleteEpg()
 330 {
 331   bool bReturn(false);
 332   CLog::LogFC(LOGDEBUG, LOGEPG, "Deleting all EPG data from the database");
 333
 334   std::unique_lock<CCriticalSection> lock(m_critSection);
 335
 336   bReturn = DeleteValues("epg") || bReturn;
 337   bReturn = DeleteValues("epgtags") || bReturn;
 338   bReturn = DeleteValues("lastepgscan") || bReturn;
 339
 340   return bReturn;
 341 }
 342
 343 bool CPVREpgDatabase::QueueDeleteEpgQuery(const CPVREpg& table)
 344 {
 345   /* invalid channel */
 346   if (table.EpgID() <= 0)
 347   {
 348     CLog::LogF(LOGERROR, "Invalid channel id: {}", table.EpgID());
 349     return false;
 350   }
 351
 352   Filter filter;
 353
 354   std::unique_lock<CCriticalSection> lock(m_critSection);
 355   filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
 356
 357   std::string strQuery;
 358   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "epg"), filter, strQuery))
 359     return QueueDeleteQuery(strQuery);
 360
 361   return false;
 362 }
 363
 364 bool CPVREpgDatabase::QueueDeleteTagQuery(const CPVREpgInfoTag& tag)
 365 {
 366   /* tag without a database ID was not persisted */
 367   if (tag.DatabaseID() <= 0)
 368     return false;
 369
 370   Filter filter;
 371
 372   std::unique_lock<CCriticalSection> lock(m_critSection);
 373   filter.AppendWhere(PrepareSQL("idBroadcast = %u", tag.DatabaseID()));
 374
 375   std::string strQuery;
 376   BuildSQL(PrepareSQL("DELETE FROM %s ", "epgtags"), filter, strQuery);
 377   return QueueDeleteQuery(strQuery);
 378 }
 379
 380 std::vector<std::shared_ptr<CPVREpg>> CPVREpgDatabase::GetAll()
 381 {
 382   std::vector<std::shared_ptr<CPVREpg>> result;
 383
 384   std::unique_lock<CCriticalSection> lock(m_critSection);
 385   std::string strQuery = PrepareSQL("SELECT idEpg, sName, sScraperName FROM epg;");
 386   if (ResultQuery(strQuery))
 387   {
 388     try
 389     {
 390       while (!m_pDS->eof())
 391       {
 392         int iEpgID = m_pDS->fv("idEpg").get_asInt();
 393         std::string strName = m_pDS->fv("sName").get_asString().c_str();
 394         std::string strScraperName = m_pDS->fv("sScraperName").get_asString().c_str();
 395
 396         result.emplace_back(new CPVREpg(iEpgID, strName, strScraperName, shared_from_this()));
 397         m_pDS->next();
 398       }
 399       m_pDS->close();
 400     }
 401     catch (...)
 402     {
 403       CLog::LogF(LOGERROR, "Could not load EPG data from the database");
 404     }
 405   }
 406
 407   return result;
 408 }
 409
 410 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::CreateEpgTag(
 411     const std::unique_ptr<dbiplus::Dataset>& pDS) const
 412 {
 413   if (!pDS->eof())
 414   {
 415     std::shared_ptr<CPVREpgInfoTag> newTag(
 416         new CPVREpgInfoTag(m_pDS->fv("idEpg").get_asInt(), m_pDS->fv("sIconPath").get_asString()));
 417
 418     time_t iStartTime;
 419     iStartTime = static_cast<time_t>(m_pDS->fv("iStartTime").get_asInt());
 420     const CDateTime startTime(iStartTime);
 421     newTag->m_startTime = startTime;
 422
 423     time_t iEndTime = static_cast<time_t>(m_pDS->fv("iEndTime").get_asInt());
 424     const CDateTime endTime(iEndTime);
 425     newTag->m_endTime = endTime;
 426
 427     const std::string sFirstAired = m_pDS->fv("sFirstAired").get_asString();
 428     if (sFirstAired.length() > 0)
 429       newTag->m_firstAired.SetFromW3CDate(sFirstAired);
 430
 431     int iBroadcastUID = m_pDS->fv("iBroadcastUid").get_asInt();
 432     // Compat: null value for broadcast uid changed from numerical -1 to 0 with PVR Addon API v4.0.0
 433     newTag->m_iUniqueBroadcastID = iBroadcastUID == -1 ? EPG_TAG_INVALID_UID : iBroadcastUID;
 434
 435     newTag->m_iDatabaseID = m_pDS->fv("idBroadcast").get_asInt();
 436     newTag->m_strTitle = m_pDS->fv("sTitle").get_asString();
 437     newTag->m_strPlotOutline = m_pDS->fv("sPlotOutline").get_asString();
 438     newTag->m_strPlot = m_pDS->fv("sPlot").get_asString();
 439     newTag->m_strOriginalTitle = m_pDS->fv("sOriginalTitle").get_asString();
 440     newTag->m_cast = newTag->Tokenize(m_pDS->fv("sCast").get_asString());
 441     newTag->m_directors = newTag->Tokenize(m_pDS->fv("sDirector").get_asString());
 442     newTag->m_writers = newTag->Tokenize(m_pDS->fv("sWriter").get_asString());
 443     newTag->m_iYear = m_pDS->fv("iYear").get_asInt();
 444     newTag->m_strIMDBNumber = m_pDS->fv("sIMDBNumber").get_asString();
 445     newTag->m_iParentalRating = m_pDS->fv("iParentalRating").get_asInt();
 446     newTag->m_iStarRating = m_pDS->fv("iStarRating").get_asInt();
 447     newTag->m_iEpisodeNumber = m_pDS->fv("iEpisodeId").get_asInt();
 448     newTag->m_iEpisodePart = m_pDS->fv("iEpisodePart").get_asInt();
 449     newTag->m_strEpisodeName = m_pDS->fv("sEpisodeName").get_asString();
 450     newTag->m_iSeriesNumber = m_pDS->fv("iSeriesId").get_asInt();
 451     newTag->m_iFlags = m_pDS->fv("iFlags").get_asInt();
 452     newTag->m_strSeriesLink = m_pDS->fv("sSeriesLink").get_asString();
 453     newTag->m_strParentalRatingCode = m_pDS->fv("sParentalRatingCode").get_asString();
 454     newTag->m_iGenreType = m_pDS->fv("iGenreType").get_asInt();
 455     newTag->m_iGenreSubType = m_pDS->fv("iGenreSubType").get_asInt();
 456     newTag->m_strGenreDescription = m_pDS->fv("sGenre").get_asString();
 457
 458     return newTag;
 459   }
 460   return {};
 461 }
 462
 463 bool CPVREpgDatabase::HasTags(int iEpgID) const
 464 {
 465   std::unique_lock<CCriticalSection> lock(m_critSection);
 466   const std::string strQuery =
 467       PrepareSQL("SELECT iStartTime FROM epgtags WHERE idEpg = %u LIMIT 1;", iEpgID);
 468   std::string strValue = GetSingleValue(strQuery);
 469   return !strValue.empty();
 470 }
 471
 472 CDateTime CPVREpgDatabase::GetLastEndTime(int iEpgID) const
 473 {
 474   std::unique_lock<CCriticalSection> lock(m_critSection);
 475   const std::string strQuery =
 476       PrepareSQL("SELECT MAX(iEndTime) FROM epgtags WHERE idEpg = %u;", iEpgID);
 477   std::string strValue = GetSingleValue(strQuery);
 478   if (!strValue.empty())
 479     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
 480
 481   return {};
 482 }
 483
 484 std::pair<CDateTime, CDateTime> CPVREpgDatabase::GetFirstAndLastEPGDate() const
 485 {
 486   CDateTime first;
 487   CDateTime last;
 488
 489   std::unique_lock<CCriticalSection> lock(m_critSection);
 490
 491   // 1st query: get min start time
 492   std::string strQuery = PrepareSQL("SELECT MIN(iStartTime) FROM epgtags;");
 493
 494   std::string strValue = GetSingleValue(strQuery);
 495   if (!strValue.empty())
 496     first = CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
 497
 498   // 2nd query: get max end time
 499   strQuery = PrepareSQL("SELECT MAX(iEndTime) FROM epgtags;");
 500
 501   strValue = GetSingleValue(strQuery);
 502   if (!strValue.empty())
 503     last = CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
 504
 505   return {first, last};
 506 }
 507
 508 CDateTime CPVREpgDatabase::GetMinStartTime(int iEpgID, const CDateTime& minStart) const
 509 {
 510   time_t t;
 511   minStart.GetAsTime(t);
 512
 513   std::unique_lock<CCriticalSection> lock(m_critSection);
 514   const std::string strQuery = PrepareSQL("SELECT MIN(iStartTime) "
 515                                           "FROM epgtags "
 516                                           "WHERE idEpg = %u AND iStartTime > %u;",
 517                                           iEpgID, static_cast<unsigned int>(t));
 518   std::string strValue = GetSingleValue(strQuery);
 519   if (!strValue.empty())
 520     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
 521
 522   return {};
 523 }
 524
 525 CDateTime CPVREpgDatabase::GetMaxEndTime(int iEpgID, const CDateTime& maxEnd) const
 526 {
 527   time_t t;
 528   maxEnd.GetAsTime(t);
 529
 530   std::unique_lock<CCriticalSection> lock(m_critSection);
 531   const std::string strQuery = PrepareSQL("SELECT MAX(iEndTime) "
 532                                           "FROM epgtags "
 533                                           "WHERE idEpg = %u AND iEndTime <= %u;",
 534                                           iEpgID, static_cast<unsigned int>(t));
 535   std::string strValue = GetSingleValue(strQuery);
 536   if (!strValue.empty())
 537     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
 538
 539   return {};
 540 }
 541
 542 namespace
 543 {
 544
 545 class CSearchTermConverter
 546 {
 547 public:
 548   explicit CSearchTermConverter(const std::string& strSearchTerm) { Parse(strSearchTerm); }
 549
 550   std::string ToSQL(const std::string& strFieldName) const
 551   {
 552     std::string result = "(";
 553
 554     for (auto it = m_fragments.cbegin(); it != m_fragments.cend();)
 555     {
 556       result += (*it);
 557
 558       ++it;
 559       if (it != m_fragments.cend())
 560         result += strFieldName;
 561     }
 562
 563     StringUtils::TrimRight(result);
 564     result += ")";
 565     return result;
 566   }
 567
 568 private:
 569   void Parse(const std::string& strSearchTerm)
 570   {
 571     std::string strParsedSearchTerm(strSearchTerm);
 572     StringUtils::Trim(strParsedSearchTerm);
 573
 574     std::string strFragment;
 575
 576     bool bNextOR = false;
 577     while (!strParsedSearchTerm.empty())
 578     {
 579       StringUtils::TrimLeft(strParsedSearchTerm);
 580
 581       if (StringUtils::StartsWith(strParsedSearchTerm, "!") ||
 582           StringUtils::StartsWithNoCase(strParsedSearchTerm, "not"))
 583       {
 584         std::string strDummy;
 585         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
 586         strFragment += " NOT ";
 587         bNextOR = false;
 588       }
 589       else if (StringUtils::StartsWith(strParsedSearchTerm, "+") ||
 590                StringUtils::StartsWithNoCase(strParsedSearchTerm, "and"))
 591       {
 592         std::string strDummy;
 593         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
 594         strFragment += " AND ";
 595         bNextOR = false;
 596       }
 597       else if (StringUtils::StartsWith(strParsedSearchTerm, "|") ||
 598                StringUtils::StartsWithNoCase(strParsedSearchTerm, "or"))
 599       {
 600         std::string strDummy;
 601         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
 602         strFragment += " OR ";
 603         bNextOR = false;
 604       }
 605       else
 606       {
 607         std::string strTerm;
 608         GetAndCutNextTerm(strParsedSearchTerm, strTerm);
 609         if (!strTerm.empty())
 610         {
 611           if (bNextOR && !m_fragments.empty())
 612             strFragment += " OR "; // default operator
 613
 614           strFragment += "(UPPER(";
 615
 616           m_fragments.emplace_back(strFragment);
 617           strFragment.clear();
 618
 619           strFragment += ") LIKE UPPER('%";
 620           StringUtils::Replace(strTerm, "'", "''"); // escape '
 621           strFragment += strTerm;
 622           strFragment += "%')) ";
 623
 624           bNextOR = true;
 625         }
 626         else
 627         {
 628           break;
 629         }
 630       }
 631
 632       StringUtils::TrimLeft(strParsedSearchTerm);
 633     }
 634
 635     if (!strFragment.empty())
 636       m_fragments.emplace_back(strFragment);
 637   }
 638
 639   static void GetAndCutNextTerm(std::string& strSearchTerm, std::string& strNextTerm)
 640   {
 641     std::string strFindNext(" ");
 642
 643     if (StringUtils::EndsWith(strSearchTerm, "\""))
 644     {
 645       strSearchTerm.erase(0, 1);
 646       strFindNext = "\"";
 647     }
 648
 649     const size_t iNextPos = strSearchTerm.find(strFindNext);
 650     if (iNextPos != std::string::npos)
 651     {
 652       strNextTerm = strSearchTerm.substr(0, iNextPos);
 653       strSearchTerm.erase(0, iNextPos + 1);
 654     }
 655     else
 656     {
 657       strNextTerm = strSearchTerm;
 658       strSearchTerm.clear();
 659     }
 660   }
 661
 662   std::vector<std::string> m_fragments;
 663 };
 664
 665 } // unnamed namespace
 666
 667 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTags(
 668     const PVREpgSearchData& searchData) const
 669 {
 670   std::unique_lock<CCriticalSection> lock(m_critSection);
 671
 672   std::string strQuery = PrepareSQL("SELECT * FROM epgtags");
 673
 674   Filter filter;
 675
 676   /////////////////////////////////////////////////////////////////////////////////////////////
 677   // min start datetime
 678   /////////////////////////////////////////////////////////////////////////////////////////////
 679
 680   if (searchData.m_startDateTime.IsValid())
 681   {
 682     time_t minStart;
 683     searchData.m_startDateTime.GetAsTime(minStart);
 684     filter.AppendWhere(PrepareSQL("iStartTime >= %u", static_cast<unsigned int>(minStart)));
 685   }
 686
 687   /////////////////////////////////////////////////////////////////////////////////////////////
 688   // max end datetime
 689   /////////////////////////////////////////////////////////////////////////////////////////////
 690
 691   if (searchData.m_endDateTime.IsValid())
 692   {
 693     time_t maxEnd;
 694     searchData.m_endDateTime.GetAsTime(maxEnd);
 695     filter.AppendWhere(PrepareSQL("iEndTime <= %u", static_cast<unsigned int>(maxEnd)));
 696   }
 697
 698   /////////////////////////////////////////////////////////////////////////////////////////////
 699   // ignore finished broadcasts
 700   /////////////////////////////////////////////////////////////////////////////////////////////
 701
 702   if (searchData.m_bIgnoreFinishedBroadcasts)
 703   {
 704     const time_t minEnd = std::time(nullptr); // now
 705     filter.AppendWhere(PrepareSQL("iEndTime > %u", static_cast<unsigned int>(minEnd)));
 706   }
 707
 708   /////////////////////////////////////////////////////////////////////////////////////////////
 709   // ignore future broadcasts
 710   /////////////////////////////////////////////////////////////////////////////////////////////
 711
 712   if (searchData.m_bIgnoreFutureBroadcasts)
 713   {
 714     const time_t maxStart = std::time(nullptr); // now
 715     filter.AppendWhere(PrepareSQL("iStartTime < %u", static_cast<unsigned int>(maxStart)));
 716   }
 717
 718   /////////////////////////////////////////////////////////////////////////////////////////////
 719   // genre type
 720   /////////////////////////////////////////////////////////////////////////////////////////////
 721
 722   if (searchData.m_iGenreType != EPG_SEARCH_UNSET)
 723   {
 724     if (searchData.m_bIncludeUnknownGenres)
 725     {
 726       // match the exact genre and everything with unknown genre
 727       filter.AppendWhere(PrepareSQL("(iGenreType == %u) OR (iGenreType < %u) OR (iGenreType > %u)",
 728                                     searchData.m_iGenreType, EPG_EVENT_CONTENTMASK_MOVIEDRAMA,
 729                                     EPG_EVENT_CONTENTMASK_USERDEFINED));
 730     }
 731     else
 732     {
 733       // match only the exact genre
 734       filter.AppendWhere(PrepareSQL("iGenreType == %u", searchData.m_iGenreType));
 735     }
 736   }
 737
 738   /////////////////////////////////////////////////////////////////////////////////////////////
 739   // search term
 740   /////////////////////////////////////////////////////////////////////////////////////////////
 741
 742   if (!searchData.m_strSearchTerm.empty())
 743   {
 744     const CSearchTermConverter conv(searchData.m_strSearchTerm);
 745
 746     // title
 747     std::string strWhere = conv.ToSQL("sTitle");
 748
 749     // plot outline
 750     strWhere += " OR ";
 751     strWhere += conv.ToSQL("sPlotOutline");
 752
 753     if (searchData.m_bSearchInDescription)
 754     {
 755       // plot
 756       strWhere += " OR ";
 757       strWhere += conv.ToSQL("sPlot");
 758     }
 759
 760     filter.AppendWhere(strWhere);
 761   }
 762
 763   if (BuildSQL(strQuery, filter, strQuery))
 764   {
 765     try
 766     {
 767       if (m_pDS->query(strQuery))
 768       {
 769         std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
 770         while (!m_pDS->eof())
 771         {
 772           tags.emplace_back(CreateEpgTag(m_pDS));
 773           m_pDS->next();
 774         }
 775         m_pDS->close();
 776         return tags;
 777       }
 778     }
 779     catch (...)
 780     {
 781       CLog::LogF(LOGERROR, "Could not load tags for given search criteria");
 782     }
 783   }
 784
 785   return {};
 786 }
 787
 788 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByUniqueBroadcastID(
 789     int iEpgID, unsigned int iUniqueBroadcastId) const
 790 {
 791   std::unique_lock<CCriticalSection> lock(m_critSection);
 792   const std::string strQuery = PrepareSQL("SELECT * "
 793                                           "FROM epgtags "
 794                                           "WHERE idEpg = %u AND iBroadcastUid = %u;",
 795                                           iEpgID, iUniqueBroadcastId);
 796
 797   if (ResultQuery(strQuery))
 798   {
 799     try
 800     {
 801       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
 802       m_pDS->close();
 803       return tag;
 804     }
 805     catch (...)
 806     {
 807       CLog::LogF(LOGERROR, "Could not load EPG tag with unique broadcast ID ({}) from the database",
 808                  iUniqueBroadcastId);
 809     }
 810   }
 811
 812   return {};
 813 }
 814
 815 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByDatabaseID(int iEpgID,
 816                                                                        int iDatabaseId) const
 817 {
 818   std::unique_lock<CCriticalSection> lock(m_critSection);
 819   const std::string strQuery = PrepareSQL("SELECT * "
 820                                           "FROM epgtags "
 821                                           "WHERE idEpg = %u AND idBroadcast = %u;",
 822                                           iEpgID, iDatabaseId);
 823
 824   if (ResultQuery(strQuery))
 825   {
 826     try
 827     {
 828       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
 829       m_pDS->close();
 830       return tag;
 831     }
 832     catch (...)
 833     {
 834       CLog::LogF(LOGERROR, "Could not load EPG tag with database ID ({}) from the database",
 835                  iDatabaseId);
 836     }
 837   }
 838
 839   return {};
 840 }
 841
 842 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByStartTime(
 843     int iEpgID, const CDateTime& startTime) const
 844 {
 845   time_t start;
 846   startTime.GetAsTime(start);
 847
 848   std::unique_lock<CCriticalSection> lock(m_critSection);
 849   const std::string strQuery = PrepareSQL("SELECT * "
 850                                           "FROM epgtags "
 851                                           "WHERE idEpg = %u AND iStartTime = %u;",
 852                                           iEpgID, static_cast<unsigned int>(start));
 853
 854   if (ResultQuery(strQuery))
 855   {
 856     try
 857     {
 858       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
 859       m_pDS->close();
 860       return tag;
 861     }
 862     catch (...)
 863     {
 864       CLog::LogF(LOGERROR, "Could not load EPG tag with start time ({}) from the database",
 865                  startTime.GetAsDBDateTime());
 866     }
 867   }
 868
 869   return {};
 870 }
 871
 872 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByMinStartTime(
 873     int iEpgID, const CDateTime& minStartTime) const
 874 {
 875   time_t minStart;
 876   minStartTime.GetAsTime(minStart);
 877
 878   std::unique_lock<CCriticalSection> lock(m_critSection);
 879   const std::string strQuery =
 880       PrepareSQL("SELECT * "
 881                  "FROM epgtags "
 882                  "WHERE idEpg = %u AND iStartTime >= %u ORDER BY iStartTime ASC LIMIT 1;",
 883                  iEpgID, static_cast<unsigned int>(minStart));
 884
 885   if (ResultQuery(strQuery))
 886   {
 887     try
 888     {
 889       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
 890       m_pDS->close();
 891       return tag;
 892     }
 893     catch (...)
 894     {
 895       CLog::LogF(LOGERROR, "Could not load tags with min start time ({}) for EPG ({})",
 896                  minStartTime.GetAsDBDateTime(), iEpgID);
 897     }
 898   }
 899
 900   return {};
 901 }
 902
 903 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByMaxEndTime(
 904     int iEpgID, const CDateTime& maxEndTime) const
 905 {
 906   time_t maxEnd;
 907   maxEndTime.GetAsTime(maxEnd);
 908
 909   std::unique_lock<CCriticalSection> lock(m_critSection);
 910   const std::string strQuery =
 911       PrepareSQL("SELECT * "
 912                  "FROM epgtags "
 913                  "WHERE idEpg = %u AND iEndTime <= %u ORDER BY iStartTime DESC LIMIT 1;",
 914                  iEpgID, static_cast<unsigned int>(maxEnd));
 915
 916   if (ResultQuery(strQuery))
 917   {
 918     try
 919     {
 920       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
 921       m_pDS->close();
 922       return tag;
 923     }
 924     catch (...)
 925     {
 926       CLog::LogF(LOGERROR, "Could not load tags with max end time ({}) for EPG ({})",
 927                  maxEndTime.GetAsDBDateTime(), iEpgID);
 928     }
 929   }
 930
 931   return {};
 932 }
 933
 934 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTagsByMinStartMaxEndTime(
 935     int iEpgID, const CDateTime& minStartTime, const CDateTime& maxEndTime) const
 936 {
 937   time_t minStart;
 938   minStartTime.GetAsTime(minStart);
 939
 940   time_t maxEnd;
 941   maxEndTime.GetAsTime(maxEnd);
 942
 943   std::unique_lock<CCriticalSection> lock(m_critSection);
 944   const std::string strQuery =
 945       PrepareSQL("SELECT * "
 946                  "FROM epgtags "
 947                  "WHERE idEpg = %u AND iStartTime >= %u AND iEndTime <= %u ORDER BY iStartTime;",
 948                  iEpgID, static_cast<unsigned int>(minStart), static_cast<unsigned int>(maxEnd));
 949
 950   if (ResultQuery(strQuery))
 951   {
 952     try
 953     {
 954       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
 955       while (!m_pDS->eof())
 956       {
 957         tags.emplace_back(CreateEpgTag(m_pDS));
 958         m_pDS->next();
 959       }
 960       m_pDS->close();
 961       return tags;
 962     }
 963     catch (...)
 964     {
 965       CLog::LogF(LOGERROR,
 966                  "Could not load tags with min start time ({}) and max end time ({}) for EPG ({})",
 967                  minStartTime.GetAsDBDateTime(), maxEndTime.GetAsDBDateTime(), iEpgID);
 968     }
 969   }
 970
 971   return {};
 972 }
 973
 974 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTagsByMinEndMaxStartTime(
 975     int iEpgID, const CDateTime& minEndTime, const CDateTime& maxStartTime) const
 976 {
 977   time_t minEnd;
 978   minEndTime.GetAsTime(minEnd);
 979
 980   time_t maxStart;
 981   maxStartTime.GetAsTime(maxStart);
 982
 983   std::unique_lock<CCriticalSection> lock(m_critSection);
 984   const std::string strQuery =
 985       PrepareSQL("SELECT * "
 986                  "FROM epgtags "
 987                  "WHERE idEpg = %u AND iEndTime >= %u AND iStartTime <= %u ORDER BY iStartTime;",
 988                  iEpgID, static_cast<unsigned int>(minEnd), static_cast<unsigned int>(maxStart));
 989
 990   if (ResultQuery(strQuery))
 991   {
 992     try
 993     {
 994       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
 995       while (!m_pDS->eof())
 996       {
 997         tags.emplace_back(CreateEpgTag(m_pDS));
 998         m_pDS->next();
 999       }
1000       m_pDS->close();
1001       return tags;
1002     }
1003     catch (...)
1004     {
1005       CLog::LogF(LOGERROR,
1006                  "Could not load tags with min end time ({}) and max start time ({}) for EPG ({})",
1007                  minEndTime.GetAsDBDateTime(), maxStartTime.GetAsDBDateTime(), iEpgID);
1008     }
1009   }
1010
1011   return {};
1012 }
1013
1014 bool CPVREpgDatabase::QueueDeleteEpgTagsByMinEndMaxStartTimeQuery(int iEpgID,
1015                                                                   const CDateTime& minEndTime,
1016                                                                   const CDateTime& maxStartTime)
1017 {
1018   time_t minEnd;
1019   minEndTime.GetAsTime(minEnd);
1020
1021   time_t maxStart;
1022   maxStartTime.GetAsTime(maxStart);
1023
1024   Filter filter;
1025
1026   std::unique_lock<CCriticalSection> lock(m_critSection);
1027   filter.AppendWhere(PrepareSQL("idEpg = %u AND iEndTime >= %u AND iStartTime <= %u", iEpgID,
1028                                 static_cast<unsigned int>(minEnd),
1029                                 static_cast<unsigned int>(maxStart)));
1030
1031   std::string strQuery;
1032   if (BuildSQL("DELETE FROM epgtags", filter, strQuery))
1033     return QueueDeleteQuery(strQuery);
1034
1035   return false;
1036 }
1037
1038 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetAllEpgTags(int iEpgID) const
1039 {
1040   std::unique_lock<CCriticalSection> lock(m_critSection);
1041   const std::string strQuery =
1042       PrepareSQL("SELECT * FROM epgtags WHERE idEpg = %u ORDER BY iStartTime;", iEpgID);
1043   if (ResultQuery(strQuery))
1044   {
1045     try
1046     {
1047       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
1048       while (!m_pDS->eof())
1049       {
1050         tags.emplace_back(CreateEpgTag(m_pDS));
1051         m_pDS->next();
1052       }
1053       m_pDS->close();
1054       return tags;
1055     }
1056     catch (...)
1057     {
1058       CLog::LogF(LOGERROR, "Could not load tags for EPG ({})", iEpgID);
1059     }
1060   }
1061   return {};
1062 }
1063
1064 std::vector<std::string> CPVREpgDatabase::GetAllIconPaths(int iEpgID) const
1065 {
1066   std::unique_lock<CCriticalSection> lock(m_critSection);
1067   const std::string strQuery =
1068       PrepareSQL("SELECT sIconPath FROM epgtags WHERE idEpg = %u;", iEpgID);
1069   if (ResultQuery(strQuery))
1070   {
1071     try
1072     {
1073       std::vector<std::string> paths;
1074       while (!m_pDS->eof())
1075       {
1076         paths.emplace_back(m_pDS->fv("sIconPath").get_asString());
1077         m_pDS->next();
1078       }
1079       m_pDS->close();
1080       return paths;
1081     }
1082     catch (...)
1083     {
1084       CLog::LogF(LOGERROR, "Could not load tags for EPG ({})", iEpgID);
1085     }
1086   }
1087   return {};
1088 }
1089
1090 bool CPVREpgDatabase::GetLastEpgScanTime(int iEpgId, CDateTime* lastScan) const
1091 {
1092   bool bReturn = false;
1093
1094   std::unique_lock<CCriticalSection> lock(m_critSection);
1095   std::string strWhereClause = PrepareSQL("idEpg = %u", iEpgId);
1096   std::string strValue = GetSingleValue("lastepgscan", "sLastScan", strWhereClause);
1097
1098   if (!strValue.empty())
1099   {
1100     lastScan->SetFromDBDateTime(strValue);
1101     bReturn = true;
1102   }
1103   else
1104   {
1105     lastScan->SetValid(false);
1106   }
1107
1108   return bReturn;
1109 }
1110
1111 bool CPVREpgDatabase::QueuePersistLastEpgScanTimeQuery(int iEpgId, const CDateTime& lastScanTime)
1112 {
1113   std::unique_lock<CCriticalSection> lock(m_critSection);
1114   std::string strQuery = PrepareSQL("REPLACE INTO lastepgscan(idEpg, sLastScan) VALUES (%u, '%s');",
1115       iEpgId, lastScanTime.GetAsDBDateTime().c_str());
1116
1117   return QueueInsertQuery(strQuery);
1118 }
1119
1120 bool CPVREpgDatabase::QueueDeleteLastEpgScanTimeQuery(const CPVREpg& table)
1121 {
1122   if (table.EpgID() <= 0)
1123   {
1124     CLog::LogF(LOGERROR, "Invalid EPG id: {}", table.EpgID());
1125     return false;
1126   }
1127
1128   Filter filter;
1129
1130   std::unique_lock<CCriticalSection> lock(m_critSection);
1131   filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
1132
1133   std::string strQuery;
1134   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "lastepgscan"), filter, strQuery))
1135     return QueueDeleteQuery(strQuery);
1136
1137   return false;
1138 }
1139
1140 int CPVREpgDatabase::Persist(const CPVREpg& epg, bool bQueueWrite)
1141 {
1142   int iReturn = -1;
1143   std::string strQuery;
1144
1145   std::unique_lock<CCriticalSection> lock(m_critSection);
1146   if (epg.EpgID() > 0)
1147     strQuery = PrepareSQL("REPLACE INTO epg (idEpg, sName, sScraperName) "
1148                           "VALUES (%u, '%s', '%s');",
1149                           epg.EpgID(), epg.Name().c_str(), epg.ScraperName().c_str());
1150   else
1151     strQuery = PrepareSQL("INSERT INTO epg (sName, sScraperName) "
1152                           "VALUES ('%s', '%s');",
1153                           epg.Name().c_str(), epg.ScraperName().c_str());
1154
1155   if (bQueueWrite)
1156   {
1157     if (QueueInsertQuery(strQuery))
1158       iReturn = epg.EpgID() <= 0 ? 0 : epg.EpgID();
1159   }
1160   else
1161   {
1162     if (ExecuteQuery(strQuery))
1163       iReturn = epg.EpgID() <= 0 ? static_cast<int>(m_pDS->lastinsertid()) : epg.EpgID();
1164   }
1165
1166   return iReturn;
1167 }
1168
1169 bool CPVREpgDatabase::DeleteEpgTags(int iEpgId, const CDateTime& maxEndTime)
1170 {
1171   time_t iMaxEndTime;
1172   maxEndTime.GetAsTime(iMaxEndTime);
1173
1174   Filter filter;
1175
1176   std::unique_lock<CCriticalSection> lock(m_critSection);
1177   filter.AppendWhere(
1178       PrepareSQL("idEpg = %u AND iEndTime < %u", iEpgId, static_cast<unsigned int>(iMaxEndTime)));
1179   return DeleteValues("epgtags", filter);
1180 }
1181
1182 bool CPVREpgDatabase::DeleteEpgTags(int iEpgId)
1183 {
1184   Filter filter;
1185
1186   std::unique_lock<CCriticalSection> lock(m_critSection);
1187   filter.AppendWhere(PrepareSQL("idEpg = %u", iEpgId));
1188   return DeleteValues("epgtags", filter);
1189 }
1190
1191 bool CPVREpgDatabase::QueueDeleteEpgTags(int iEpgId)
1192 {
1193   Filter filter;
1194
1195   std::unique_lock<CCriticalSection> lock(m_critSection);
1196   filter.AppendWhere(PrepareSQL("idEpg = %u", iEpgId));
1197
1198   std::string strQuery;
1199   BuildSQL(PrepareSQL("DELETE FROM %s ", "epgtags"), filter, strQuery);
1200   return QueueDeleteQuery(strQuery);
1201 }
1202
1203 bool CPVREpgDatabase::QueuePersistQuery(const CPVREpgInfoTag& tag)
1204 {
1205   if (tag.EpgID() <= 0)
1206   {
1207     CLog::LogF(LOGERROR, "Tag '{}' does not have a valid table", tag.Title());
1208     return false;
1209   }
1210
1211   time_t iStartTime, iEndTime;
1212   tag.StartAsUTC().GetAsTime(iStartTime);
1213   tag.EndAsUTC().GetAsTime(iEndTime);
1214
1215   std::string sFirstAired;
1216   if (tag.FirstAired().IsValid())
1217     sFirstAired = tag.FirstAired().GetAsW3CDate();
1218
1219   int iBroadcastId = tag.DatabaseID();
1220   std::string strQuery;
1221
1222   std::unique_lock<CCriticalSection> lock(m_critSection);
1223
1224   if (iBroadcastId < 0)
1225   {
1226     strQuery = PrepareSQL(
1227         "REPLACE INTO epgtags (idEpg, iStartTime, "
1228         "iEndTime, sTitle, sPlotOutline, sPlot, sOriginalTitle, sCast, sDirector, sWriter, iYear, "
1229         "sIMDBNumber, "
1230         "sIconPath, iGenreType, iGenreSubType, sGenre, sFirstAired, iParentalRating, iStarRating, "
1231         "iSeriesId, "
1232         "iEpisodeId, iEpisodePart, sEpisodeName, iFlags, sSeriesLink, sParentalRatingCode, "
1233         "iBroadcastUid) "
1234         "VALUES (%u, %u, %u, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i, '%s', '%s', %i, %i, "
1235         "'%s', '%s', %i, %i, %i, %i, %i, '%s', %i, '%s', '%s', %i);",
1236         tag.EpgID(), static_cast<unsigned int>(iStartTime), static_cast<unsigned int>(iEndTime),
1237         tag.Title().c_str(), tag.PlotOutline().c_str(), tag.Plot().c_str(),
1238         tag.OriginalTitle().c_str(), tag.DeTokenize(tag.Cast()).c_str(),
1239         tag.DeTokenize(tag.Directors()).c_str(), tag.DeTokenize(tag.Writers()).c_str(), tag.Year(),
1240         tag.IMDBNumber().c_str(), tag.ClientIconPath().c_str(), tag.GenreType(), tag.GenreSubType(),
1241         tag.GenreDescription().c_str(), sFirstAired.c_str(), tag.ParentalRating(), tag.StarRating(),
1242         tag.SeriesNumber(), tag.EpisodeNumber(), tag.EpisodePart(), tag.EpisodeName().c_str(),
1243         tag.Flags(), tag.SeriesLink().c_str(), tag.ParentalRatingCode().c_str(),
1244         tag.UniqueBroadcastID());
1245   }
1246   else
1247   {
1248     strQuery = PrepareSQL(
1249         "REPLACE INTO epgtags (idEpg, iStartTime, "
1250         "iEndTime, sTitle, sPlotOutline, sPlot, sOriginalTitle, sCast, sDirector, sWriter, iYear, "
1251         "sIMDBNumber, "
1252         "sIconPath, iGenreType, iGenreSubType, sGenre, sFirstAired, iParentalRating, iStarRating, "
1253         "iSeriesId, "
1254         "iEpisodeId, iEpisodePart, sEpisodeName, iFlags, sSeriesLink, sParentalRatingCode, "
1255         "iBroadcastUid, idBroadcast) "
1256         "VALUES (%u, %u, %u, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i, '%s', '%s', %i, %i, "
1257         "'%s', '%s', %i, %i, %i, %i, %i, '%s', %i, '%s', '%s', %i, %i);",
1258         tag.EpgID(), static_cast<unsigned int>(iStartTime), static_cast<unsigned int>(iEndTime),
1259         tag.Title().c_str(), tag.PlotOutline().c_str(), tag.Plot().c_str(),
1260         tag.OriginalTitle().c_str(), tag.DeTokenize(tag.Cast()).c_str(),
1261         tag.DeTokenize(tag.Directors()).c_str(), tag.DeTokenize(tag.Writers()).c_str(), tag.Year(),
1262         tag.IMDBNumber().c_str(), tag.ClientIconPath().c_str(), tag.GenreType(), tag.GenreSubType(),
1263         tag.GenreDescription().c_str(), sFirstAired.c_str(), tag.ParentalRating(), tag.StarRating(),
1264         tag.SeriesNumber(), tag.EpisodeNumber(), tag.EpisodePart(), tag.EpisodeName().c_str(),
1265         tag.Flags(), tag.SeriesLink().c_str(), tag.ParentalRatingCode().c_str(),
1266         tag.UniqueBroadcastID(), iBroadcastId);
1267   }
1268
1269   QueueInsertQuery(strQuery);
1270   return true;
1271 }
1272
1273 int CPVREpgDatabase::GetLastEPGId() const
1274 {
1275   std::unique_lock<CCriticalSection> lock(m_critSection);
1276   std::string strQuery = PrepareSQL("SELECT MAX(idEpg) FROM epg");
1277   std::string strValue = GetSingleValue(strQuery);
1278   if (!strValue.empty())
1279     return std::atoi(strValue.c_str());
1280   return 0;
1281 }
1282
1283 /********** Saved searches methods **********/
1284
1285 std::shared_ptr<CPVREpgSearchFilter> CPVREpgDatabase::CreateEpgSearchFilter(
1286     bool bRadio, const std::unique_ptr<dbiplus::Dataset>& pDS) const
1287 {
1288   if (!pDS->eof())
1289   {
1290     auto newSearch = std::make_shared<CPVREpgSearchFilter>(bRadio);
1291
1292     newSearch->SetDatabaseId(m_pDS->fv("idSearch").get_asInt());
1293     newSearch->SetTitle(m_pDS->fv("sTitle").get_asString());
1294
1295     const std::string lastExec = m_pDS->fv("sLastExecutedDateTime").get_asString();
1296     if (!lastExec.empty())
1297       newSearch->SetLastExecutedDateTime(CDateTime::FromDBDateTime(lastExec));
1298
1299     newSearch->SetSearchTerm(m_pDS->fv("sSearchTerm").get_asString());
1300     newSearch->SetSearchInDescription(m_pDS->fv("bSearchInDescription").get_asBool());
1301     newSearch->SetGenreType(m_pDS->fv("iGenreType").get_asInt());
1302
1303     const std::string start = m_pDS->fv("sStartDateTime").get_asString();
1304     if (!start.empty())
1305       newSearch->SetStartDateTime(CDateTime::FromDBDateTime(start));
1306
1307     const std::string end = m_pDS->fv("sEndDateTime").get_asString();
1308     if (!end.empty())
1309       newSearch->SetEndDateTime(CDateTime::FromDBDateTime(end));
1310
1311     newSearch->SetCaseSensitive(m_pDS->fv("bIsCaseSensitive").get_asBool());
1312     newSearch->SetMinimumDuration(m_pDS->fv("iMinimumDuration").get_asInt());
1313     newSearch->SetMaximumDuration(m_pDS->fv("iMaximumDuration").get_asInt());
1314     newSearch->SetClientID(m_pDS->fv("iClientId").get_asInt());
1315     newSearch->SetChannelUID(m_pDS->fv("iChannelUid").get_asInt());
1316     newSearch->SetIncludeUnknownGenres(m_pDS->fv("bIncludeUnknownGenres").get_asBool());
1317     newSearch->SetRemoveDuplicates(m_pDS->fv("bRemoveDuplicates").get_asBool());
1318     newSearch->SetIgnoreFinishedBroadcasts(m_pDS->fv("bIgnoreFinishedBroadcasts").get_asBool());
1319     newSearch->SetIgnoreFutureBroadcasts(m_pDS->fv("bIgnoreFutureBroadcasts").get_asBool());
1320     newSearch->SetFreeToAirOnly(m_pDS->fv("bFreeToAirOnly").get_asBool());
1321     newSearch->SetIgnorePresentTimers(m_pDS->fv("bIgnorePresentTimers").get_asBool());
1322     newSearch->SetIgnorePresentRecordings(m_pDS->fv("bIgnorePresentRecordings").get_asBool());
1323     newSearch->SetChannelGroupID(m_pDS->fv("iChannelGroup").get_asInt());
1324
1325     newSearch->SetChanged(false);
1326
1327     return newSearch;
1328   }
1329   return {};
1330 }
1331
1332 std::vector<std::shared_ptr<CPVREpgSearchFilter>> CPVREpgDatabase::GetSavedSearches(
1333     bool bRadio) const
1334 {
1335   std::vector<std::shared_ptr<CPVREpgSearchFilter>> result;
1336
1337   std::unique_lock<CCriticalSection> lock(m_critSection);
1338   const std::string strQuery =
1339       PrepareSQL("SELECT * FROM savedsearches WHERE bIsRadio = %u", bRadio);
1340   if (ResultQuery(strQuery))
1341   {
1342     try
1343     {
1344       while (!m_pDS->eof())
1345       {
1346         result.emplace_back(CreateEpgSearchFilter(bRadio, m_pDS));
1347         m_pDS->next();
1348       }
1349       m_pDS->close();
1350     }
1351     catch (...)
1352     {
1353       CLog::LogF(LOGERROR, "Could not load EPG search data from the database");
1354     }
1355   }
1356   return result;
1357 }
1358
1359 std::shared_ptr<CPVREpgSearchFilter> CPVREpgDatabase::GetSavedSearchById(bool bRadio, int iId) const
1360 {
1361   std::unique_lock<CCriticalSection> lock(m_critSection);
1362   const std::string strQuery =
1363       PrepareSQL("SELECT * FROM savedsearches WHERE bIsRadio = %u AND idSearch = %u;", bRadio, iId);
1364
1365   if (ResultQuery(strQuery))
1366   {
1367     try
1368     {
1369       std::shared_ptr<CPVREpgSearchFilter> filter = CreateEpgSearchFilter(bRadio, m_pDS);
1370       m_pDS->close();
1371       return filter;
1372     }
1373     catch (...)
1374     {
1375       CLog::LogF(LOGERROR, "Could not load EPG search filter with id ({}) from the database", iId);
1376     }
1377   }
1378
1379   return {};
1380 }
1381
1382 bool CPVREpgDatabase::Persist(CPVREpgSearchFilter& epgSearch)
1383 {
1384   std::unique_lock<CCriticalSection> lock(m_critSection);
1385
1386   // Insert a new entry if this is a new search, replace the existing otherwise
1387   std::string strQuery;
1388   if (epgSearch.GetDatabaseId() == -1)
1389     strQuery = PrepareSQL(
1390         "INSERT INTO savedsearches "
1391         "(sTitle, sLastExecutedDateTime, sSearchTerm, bSearchInDescription, bIsCaseSensitive, "
1392         "iGenreType, bIncludeUnknownGenres, sStartDateTime, sEndDateTime, iMinimumDuration, "
1393         "iMaximumDuration, bIsRadio, iClientId, iChannelUid, bRemoveDuplicates, "
1394         "bIgnoreFinishedBroadcasts, bIgnoreFutureBroadcasts, bFreeToAirOnly, bIgnorePresentTimers, "
1395         "bIgnorePresentRecordings, iChannelGroup) "
1396         "VALUES ('%s', '%s', '%s', %i, %i, %i, %i, '%s', '%s', %i, %i, %i, %i, %i, %i, %i, %i, "
1397         "%i, %i, %i, %i);",
1398         epgSearch.GetTitle().c_str(),
1399         epgSearch.GetLastExecutedDateTime().IsValid()
1400             ? epgSearch.GetLastExecutedDateTime().GetAsDBDateTime().c_str()
1401             : "",
1402         epgSearch.GetSearchTerm().c_str(), epgSearch.ShouldSearchInDescription() ? 1 : 0,
1403         epgSearch.IsCaseSensitive() ? 1 : 0, epgSearch.GetGenreType(),
1404         epgSearch.ShouldIncludeUnknownGenres() ? 1 : 0,
1405         epgSearch.GetStartDateTime().IsValid()
1406             ? epgSearch.GetStartDateTime().GetAsDBDateTime().c_str()
1407             : "",
1408         epgSearch.GetEndDateTime().IsValid() ? epgSearch.GetEndDateTime().GetAsDBDateTime().c_str()
1409                                              : "",
1410         epgSearch.GetMinimumDuration(), epgSearch.GetMaximumDuration(), epgSearch.IsRadio() ? 1 : 0,
1411         epgSearch.GetClientID(), epgSearch.GetChannelUID(),
1412         epgSearch.ShouldRemoveDuplicates() ? 1 : 0,
1413         epgSearch.ShouldIgnoreFinishedBroadcasts() ? 1 : 0,
1414         epgSearch.ShouldIgnoreFutureBroadcasts() ? 1 : 0, epgSearch.IsFreeToAirOnly() ? 1 : 0,
1415         epgSearch.ShouldIgnorePresentTimers() ? 1 : 0,
1416         epgSearch.ShouldIgnorePresentRecordings() ? 1 : 0, epgSearch.GetChannelGroupID());
1417   else
1418     strQuery = PrepareSQL(
1419         "REPLACE INTO savedsearches "
1420         "(idSearch, sTitle, sLastExecutedDateTime, sSearchTerm, bSearchInDescription, "
1421         "bIsCaseSensitive, iGenreType, bIncludeUnknownGenres, sStartDateTime, sEndDateTime, "
1422         "iMinimumDuration, iMaximumDuration, bIsRadio, iClientId, iChannelUid, bRemoveDuplicates, "
1423         "bIgnoreFinishedBroadcasts, bIgnoreFutureBroadcasts, bFreeToAirOnly, bIgnorePresentTimers, "
1424         "bIgnorePresentRecordings, iChannelGroup) "
1425         "VALUES (%i, '%s', '%s', '%s', %i, %i, %i, %i, '%s', '%s', %i, %i, %i, %i, %i, %i, %i, %i, "
1426         "%i, %i, %i, %i);",
1427         epgSearch.GetDatabaseId(), epgSearch.GetTitle().c_str(),
1428         epgSearch.GetLastExecutedDateTime().IsValid()
1429             ? epgSearch.GetLastExecutedDateTime().GetAsDBDateTime().c_str()
1430             : "",
1431         epgSearch.GetSearchTerm().c_str(), epgSearch.ShouldSearchInDescription() ? 1 : 0,
1432         epgSearch.IsCaseSensitive() ? 1 : 0, epgSearch.GetGenreType(),
1433         epgSearch.ShouldIncludeUnknownGenres() ? 1 : 0,
1434         epgSearch.GetStartDateTime().IsValid()
1435             ? epgSearch.GetStartDateTime().GetAsDBDateTime().c_str()
1436             : "",
1437         epgSearch.GetEndDateTime().IsValid() ? epgSearch.GetEndDateTime().GetAsDBDateTime().c_str()
1438                                              : "",
1439         epgSearch.GetMinimumDuration(), epgSearch.GetMaximumDuration(), epgSearch.IsRadio() ? 1 : 0,
1440         epgSearch.GetClientID(), epgSearch.GetChannelUID(),
1441         epgSearch.ShouldRemoveDuplicates() ? 1 : 0,
1442         epgSearch.ShouldIgnoreFinishedBroadcasts() ? 1 : 0,
1443         epgSearch.ShouldIgnoreFutureBroadcasts() ? 1 : 0, epgSearch.IsFreeToAirOnly() ? 1 : 0,
1444         epgSearch.ShouldIgnorePresentTimers() ? 1 : 0,
1445         epgSearch.ShouldIgnorePresentRecordings() ? 1 : 0, epgSearch.GetChannelGroupID());
1446
1447   bool bReturn = ExecuteQuery(strQuery);
1448
1449   if (bReturn)
1450   {
1451     // Set the database id for searches persisted for the first time
1452     if (epgSearch.GetDatabaseId() == -1)
1453       epgSearch.SetDatabaseId(static_cast<int>(m_pDS->lastinsertid()));
1454
1455     epgSearch.SetChanged(false);
1456   }
1457
1458   return bReturn;
1459 }
1460
1461 bool CPVREpgDatabase::UpdateSavedSearchLastExecuted(const CPVREpgSearchFilter& epgSearch)
1462 {
1463   if (epgSearch.GetDatabaseId() == -1)
1464     return false;
1465
1466   std::unique_lock<CCriticalSection> lock(m_critSection);
1467
1468   const std::string strQuery = PrepareSQL(
1469       "UPDATE savedsearches SET sLastExecutedDateTime = '%s' WHERE idSearch = %i",
1470       epgSearch.GetLastExecutedDateTime().GetAsDBDateTime().c_str(), epgSearch.GetDatabaseId());
1471   return ExecuteQuery(strQuery);
1472 }
1473
1474 bool CPVREpgDatabase::Delete(const CPVREpgSearchFilter& epgSearch)
1475 {
1476   if (epgSearch.GetDatabaseId() == -1)
1477     return false;
1478
1479   CLog::LogFC(LOGDEBUG, LOGEPG, "Deleting saved search '{}' from the database",
1480               epgSearch.GetTitle());
1481
1482   std::unique_lock<CCriticalSection> lock(m_critSection);
1483
1484   Filter filter;
1485   filter.AppendWhere(PrepareSQL("idSearch = '%i'", epgSearch.GetDatabaseId()));
1486
1487   return DeleteValues("savedsearches", filter);
1488 }
1489
1490 bool CPVREpgDatabase::DeleteSavedSearches()
1491 {
1492   CLog::LogFC(LOGDEBUG, LOGEPG, "Deleting all saved searches from the database");
1493
1494   std::unique_lock<CCriticalSection> lock(m_critSection);
1495   return DeleteValues("savedsearches");
1496 }