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 "settings/AdvancedSettings.h"
17 #include "settings/SettingsComponent.h"
18 #include "threads/SingleLock.h"
19 #include "utils/log.h"
20 
21 #include <cstdlib>
22 #include <memory>
23 #include <string>
24 #include <vector>
25 
26 using namespace dbiplus;
27 using namespace PVR;
28 
Open()29 bool CPVREpgDatabase::Open()
30 {
31   CSingleLock lock(m_critSection);
32   return CDatabase::Open(CServiceBroker::GetSettingsComponent()->GetAdvancedSettings()->m_databaseEpg);
33 }
34 
Close()35 void CPVREpgDatabase::Close()
36 {
37   CSingleLock lock(m_critSection);
38   CDatabase::Close();
39 }
40 
Lock()41 void CPVREpgDatabase::Lock()
42 {
43   m_critSection.lock();
44 }
45 
Unlock()46 void CPVREpgDatabase::Unlock()
47 {
48   m_critSection.unlock();
49 }
50 
CreateTables()51 void CPVREpgDatabase::CreateTables()
52 {
53   CLog::Log(LOGINFO, "Creating EPG database tables");
54 
55   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'epg'");
56 
57   CSingleLock lock(m_critSection);
58 
59   m_pDS->exec(
60       "CREATE TABLE epg ("
61         "idEpg           integer primary key, "
62         "sName           varchar(64),"
63         "sScraperName    varchar(32)"
64       ")"
65   );
66 
67   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'epgtags'");
68   m_pDS->exec(
69       "CREATE TABLE epgtags ("
70         "idBroadcast     integer primary key, "
71         "iBroadcastUid   integer, "
72         "idEpg           integer, "
73         "sTitle          varchar(128), "
74         "sPlotOutline    text, "
75         "sPlot           text, "
76         "sOriginalTitle  varchar(128), "
77         "sCast           varchar(255), "
78         "sDirector       varchar(255), "
79         "sWriter         varchar(255), "
80         "iYear           integer, "
81         "sIMDBNumber     varchar(50), "
82         "sIconPath       varchar(255), "
83         "iStartTime      integer, "
84         "iEndTime        integer, "
85         "iGenreType      integer, "
86         "iGenreSubType   integer, "
87         "sGenre          varchar(128), "
88         "sFirstAired     varchar(32), "
89         "iParentalRating integer, "
90         "iStarRating     integer, "
91         "iSeriesId       integer, "
92         "iEpisodeId      integer, "
93         "iEpisodePart    integer, "
94         "sEpisodeName    varchar(128), "
95         "iFlags          integer, "
96         "sSeriesLink     varchar(255)"
97       ")"
98   );
99 
100   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating table 'lastepgscan'");
101   m_pDS->exec("CREATE TABLE lastepgscan ("
102         "idEpg integer primary key, "
103         "sLastScan varchar(20)"
104       ")"
105   );
106 }
107 
CreateAnalytics()108 void CPVREpgDatabase::CreateAnalytics()
109 {
110   CLog::LogFC(LOGDEBUG, LOGEPG, "Creating EPG database indices");
111 
112   CSingleLock lock(m_critSection);
113   m_pDS->exec("CREATE UNIQUE INDEX idx_epg_idEpg_iStartTime on epgtags(idEpg, iStartTime desc);");
114   m_pDS->exec("CREATE INDEX idx_epg_iEndTime on epgtags(iEndTime);");
115 }
116 
UpdateTables(int iVersion)117 void CPVREpgDatabase::UpdateTables(int iVersion)
118 {
119   CSingleLock lock(m_critSection);
120   if (iVersion < 5)
121     m_pDS->exec("ALTER TABLE epgtags ADD sGenre varchar(128);");
122 
123   if (iVersion < 9)
124     m_pDS->exec("ALTER TABLE epgtags ADD sIconPath varchar(255);");
125 
126   if (iVersion < 10)
127   {
128     m_pDS->exec("ALTER TABLE epgtags ADD sOriginalTitle varchar(128);");
129     m_pDS->exec("ALTER TABLE epgtags ADD sCast varchar(255);");
130     m_pDS->exec("ALTER TABLE epgtags ADD sDirector varchar(255);");
131     m_pDS->exec("ALTER TABLE epgtags ADD sWriter varchar(255);");
132     m_pDS->exec("ALTER TABLE epgtags ADD iYear integer;");
133     m_pDS->exec("ALTER TABLE epgtags ADD sIMDBNumber varchar(50);");
134   }
135 
136   if (iVersion < 11)
137   {
138     m_pDS->exec("ALTER TABLE epgtags ADD iFlags integer;");
139   }
140 
141   if (iVersion < 12)
142   {
143     m_pDS->exec("ALTER TABLE epgtags ADD sSeriesLink varchar(255);");
144   }
145 
146   if (iVersion < 13)
147   {
148     const bool isMySQL = StringUtils::EqualsNoCase(
149         CServiceBroker::GetSettingsComponent()->GetAdvancedSettings()->m_databaseEpg.type, "mysql");
150 
151     m_pDS->exec(
152         "CREATE TABLE epgtags_new ("
153         "idBroadcast     integer primary key, "
154         "iBroadcastUid   integer, "
155         "idEpg           integer, "
156         "sTitle          varchar(128), "
157         "sPlotOutline    text, "
158         "sPlot           text, "
159         "sOriginalTitle  varchar(128), "
160         "sCast           varchar(255), "
161         "sDirector       varchar(255), "
162         "sWriter         varchar(255), "
163         "iYear           integer, "
164         "sIMDBNumber     varchar(50), "
165         "sIconPath       varchar(255), "
166         "iStartTime      integer, "
167         "iEndTime        integer, "
168         "iGenreType      integer, "
169         "iGenreSubType   integer, "
170         "sGenre          varchar(128), "
171         "sFirstAired     varchar(32), "
172         "iParentalRating integer, "
173         "iStarRating     integer, "
174         "iSeriesId       integer, "
175         "iEpisodeId      integer, "
176         "iEpisodePart    integer, "
177         "sEpisodeName    varchar(128), "
178         "iFlags          integer, "
179         "sSeriesLink     varchar(255)"
180         ")"
181     );
182 
183     m_pDS->exec(
184         "INSERT INTO epgtags_new ("
185         "idBroadcast, "
186         "iBroadcastUid, "
187         "idEpg, "
188         "sTitle, "
189         "sPlotOutline, "
190         "sPlot, "
191         "sOriginalTitle, "
192         "sCast, "
193         "sDirector, "
194         "sWriter, "
195         "iYear, "
196         "sIMDBNumber, "
197         "sIconPath, "
198         "iStartTime, "
199         "iEndTime, "
200         "iGenreType, "
201         "iGenreSubType, "
202         "sGenre, "
203         "sFirstAired, "
204         "iParentalRating, "
205         "iStarRating, "
206         "iSeriesId, "
207         "iEpisodeId, "
208         "iEpisodePart, "
209         "sEpisodeName, "
210         "iFlags, "
211         "sSeriesLink"
212         ") "
213         "SELECT "
214         "idBroadcast, "
215         "iBroadcastUid, "
216         "idEpg, "
217         "sTitle, "
218         "sPlotOutline, "
219         "sPlot, "
220         "sOriginalTitle, "
221         "sCast, "
222         "sDirector, "
223         "sWriter, "
224         "iYear, "
225         "sIMDBNumber, "
226         "sIconPath, "
227         "iStartTime, "
228         "iEndTime, "
229         "iGenreType, "
230         "iGenreSubType, "
231         "sGenre, "
232         "'' AS sFirstAired, "
233         "iParentalRating, "
234         "iStarRating, "
235         "iSeriesId, "
236         "iEpisodeId, "
237         "iEpisodePart, "
238         "sEpisodeName, "
239         "iFlags, "
240         "sSeriesLink "
241         "FROM epgtags"
242     );
243 
244     if (isMySQL)
245       m_pDS->exec(
246         "UPDATE epgtags_new INNER JOIN epgtags ON epgtags_new.idBroadcast = epgtags.idBroadcast "
247         "SET epgtags_new.sFirstAired = DATE(FROM_UNIXTIME(epgtags.iFirstAired)) "
248         "WHERE epgtags.iFirstAired > 0"
249       );
250     else
251       m_pDS->exec(
252         "UPDATE epgtags_new SET sFirstAired = "
253         "COALESCE((SELECT STRFTIME('%Y-%m-%d', iFirstAired, 'UNIXEPOCH') "
254         "FROM epgtags WHERE epgtags.idBroadcast = epgtags_new.idBroadcast "
255         "AND epgtags.iFirstAired > 0), '')"
256       );
257 
258     m_pDS->exec("DROP TABLE epgtags");
259     m_pDS->exec("ALTER TABLE epgtags_new RENAME TO epgtags");
260   }
261 }
262 
DeleteEpg()263 bool CPVREpgDatabase::DeleteEpg()
264 {
265   bool bReturn(false);
266   CLog::LogFC(LOGDEBUG, LOGEPG, "Deleting all EPG data from the database");
267 
268   CSingleLock lock(m_critSection);
269 
270   bReturn = DeleteValues("epg") || bReturn;
271   bReturn = DeleteValues("epgtags") || bReturn;
272   bReturn = DeleteValues("lastepgscan") || bReturn;
273 
274   return bReturn;
275 }
276 
QueueDeleteEpgQuery(const CPVREpg & table)277 bool CPVREpgDatabase::QueueDeleteEpgQuery(const CPVREpg& table)
278 {
279   /* invalid channel */
280   if (table.EpgID() <= 0)
281   {
282     CLog::LogF(LOGERROR, "Invalid channel id: {}", table.EpgID());
283     return false;
284   }
285 
286   Filter filter;
287 
288   CSingleLock lock(m_critSection);
289   filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
290 
291   std::string strQuery;
292   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "epg"), filter, strQuery))
293     return QueueDeleteQuery(strQuery);
294 
295   return false;
296 }
297 
QueueDeleteTagQuery(const CPVREpgInfoTag & tag)298 bool CPVREpgDatabase::QueueDeleteTagQuery(const CPVREpgInfoTag& tag)
299 {
300   /* tag without a database ID was not persisted */
301   if (tag.DatabaseID() <= 0)
302     return false;
303 
304   Filter filter;
305 
306   CSingleLock lock(m_critSection);
307   filter.AppendWhere(PrepareSQL("idBroadcast = %u", tag.DatabaseID()));
308 
309   std::string strQuery;
310   BuildSQL(PrepareSQL("DELETE FROM %s ", "epgtags"), filter, strQuery);
311   return QueueDeleteQuery(strQuery);
312 }
313 
GetAll()314 std::vector<std::shared_ptr<CPVREpg>> CPVREpgDatabase::GetAll()
315 {
316   std::vector<std::shared_ptr<CPVREpg>> result;
317 
318   CSingleLock lock(m_critSection);
319   std::string strQuery = PrepareSQL("SELECT idEpg, sName, sScraperName FROM epg;");
320   if (ResultQuery(strQuery))
321   {
322     try
323     {
324       while (!m_pDS->eof())
325       {
326         int iEpgID = m_pDS->fv("idEpg").get_asInt();
327         std::string strName = m_pDS->fv("sName").get_asString().c_str();
328         std::string strScraperName = m_pDS->fv("sScraperName").get_asString().c_str();
329 
330         result.emplace_back(new CPVREpg(iEpgID, strName, strScraperName, shared_from_this()));
331         m_pDS->next();
332       }
333       m_pDS->close();
334     }
335     catch (...)
336     {
337       CLog::LogF(LOGERROR, "Could not load EPG data from the database");
338     }
339   }
340 
341   return result;
342 }
343 
CreateEpgTag(const std::unique_ptr<dbiplus::Dataset> & pDS)344 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::CreateEpgTag(
345     const std::unique_ptr<dbiplus::Dataset>& pDS)
346 {
347   if (!pDS->eof())
348   {
349     std::shared_ptr<CPVREpgInfoTag> newTag(new CPVREpgInfoTag());
350 
351     time_t iStartTime;
352     iStartTime = static_cast<time_t>(m_pDS->fv("iStartTime").get_asInt());
353     const CDateTime startTime(iStartTime);
354     newTag->m_startTime = startTime;
355 
356     time_t iEndTime = static_cast<time_t>(m_pDS->fv("iEndTime").get_asInt());
357     const CDateTime endTime(iEndTime);
358     newTag->m_endTime = endTime;
359 
360     const std::string sFirstAired = m_pDS->fv("sFirstAired").get_asString();
361     if (sFirstAired.length() > 0)
362       newTag->m_firstAired.SetFromW3CDate(sFirstAired);
363 
364     int iBroadcastUID = m_pDS->fv("iBroadcastUid").get_asInt();
365     // Compat: null value for broadcast uid changed from numerical -1 to 0 with PVR Addon API v4.0.0
366     newTag->m_iUniqueBroadcastID = iBroadcastUID == -1 ? EPG_TAG_INVALID_UID : iBroadcastUID;
367 
368     newTag->m_iEpgID = m_pDS->fv("idEpg").get_asInt();
369     newTag->m_iDatabaseID = m_pDS->fv("idBroadcast").get_asInt();
370     newTag->m_strTitle = m_pDS->fv("sTitle").get_asString().c_str();
371     newTag->m_strPlotOutline = m_pDS->fv("sPlotOutline").get_asString().c_str();
372     newTag->m_strPlot = m_pDS->fv("sPlot").get_asString().c_str();
373     newTag->m_strOriginalTitle = m_pDS->fv("sOriginalTitle").get_asString().c_str();
374     newTag->m_cast = newTag->Tokenize(m_pDS->fv("sCast").get_asString());
375     newTag->m_directors = newTag->Tokenize(m_pDS->fv("sDirector").get_asString());
376     newTag->m_writers = newTag->Tokenize(m_pDS->fv("sWriter").get_asString());
377     newTag->m_iYear = m_pDS->fv("iYear").get_asInt();
378     newTag->m_strIMDBNumber = m_pDS->fv("sIMDBNumber").get_asString().c_str();
379     newTag->m_iParentalRating = m_pDS->fv("iParentalRating").get_asInt();
380     newTag->m_iStarRating = m_pDS->fv("iStarRating").get_asInt();
381     newTag->m_iEpisodeNumber = m_pDS->fv("iEpisodeId").get_asInt();
382     newTag->m_iEpisodePart = m_pDS->fv("iEpisodePart").get_asInt();
383     newTag->m_strEpisodeName = m_pDS->fv("sEpisodeName").get_asString().c_str();
384     newTag->m_iSeriesNumber = m_pDS->fv("iSeriesId").get_asInt();
385     newTag->m_strIconPath = m_pDS->fv("sIconPath").get_asString().c_str();
386     newTag->m_iFlags = m_pDS->fv("iFlags").get_asInt();
387     newTag->m_strSeriesLink = m_pDS->fv("sSeriesLink").get_asString().c_str();
388 
389     newTag->SetGenre(m_pDS->fv("iGenreType").get_asInt(), m_pDS->fv("iGenreSubType").get_asInt(),
390                      m_pDS->fv("sGenre").get_asString().c_str());
391     newTag->UpdatePath();
392 
393     return newTag;
394   }
395   return {};
396 }
397 
GetFirstStartTime(int iEpgID)398 CDateTime CPVREpgDatabase::GetFirstStartTime(int iEpgID)
399 {
400   CSingleLock lock(m_critSection);
401   const std::string strQuery =
402       PrepareSQL("SELECT MIN(iStartTime) FROM epgtags WHERE idEpg = %u;", iEpgID);
403   std::string strValue = GetSingleValue(strQuery);
404   if (!strValue.empty())
405     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
406 
407   return {};
408 }
409 
GetLastEndTime(int iEpgID)410 CDateTime CPVREpgDatabase::GetLastEndTime(int iEpgID)
411 {
412   CSingleLock lock(m_critSection);
413   const std::string strQuery =
414       PrepareSQL("SELECT MAX(iEndTime) FROM epgtags WHERE idEpg = %u;", iEpgID);
415   std::string strValue = GetSingleValue(strQuery);
416   if (!strValue.empty())
417     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
418 
419   return {};
420 }
421 
GetMinStartTime(int iEpgID,const CDateTime & minStart)422 CDateTime CPVREpgDatabase::GetMinStartTime(int iEpgID, const CDateTime& minStart)
423 {
424   time_t t;
425   minStart.GetAsTime(t);
426 
427   CSingleLock lock(m_critSection);
428   const std::string strQuery = PrepareSQL("SELECT MIN(iStartTime) "
429                                           "FROM epgtags "
430                                           "WHERE idEpg = %u AND iStartTime > %u;",
431                                           iEpgID, static_cast<unsigned int>(t));
432   std::string strValue = GetSingleValue(strQuery);
433   if (!strValue.empty())
434     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
435 
436   return {};
437 }
438 
GetMaxEndTime(int iEpgID,const CDateTime & maxEnd)439 CDateTime CPVREpgDatabase::GetMaxEndTime(int iEpgID, const CDateTime& maxEnd)
440 {
441   time_t t;
442   maxEnd.GetAsTime(t);
443 
444   CSingleLock lock(m_critSection);
445   const std::string strQuery = PrepareSQL("SELECT MAX(iEndTime) "
446                                           "FROM epgtags "
447                                           "WHERE idEpg = %u AND iEndTime <= %u;",
448                                           iEpgID, static_cast<unsigned int>(t));
449   std::string strValue = GetSingleValue(strQuery);
450   if (!strValue.empty())
451     return CDateTime(static_cast<time_t>(std::atoi(strValue.c_str())));
452 
453   return {};
454 }
455 
456 namespace
457 {
458 
ConvertLocalTimeToUTC(const CDateTime & local)459 CDateTime ConvertLocalTimeToUTC(const CDateTime& local)
460 {
461   time_t time = 0;
462   local.GetAsTime(time);
463 
464   struct tm* tms;
465 
466   // obtain dst flag for given datetime
467 #ifdef HAVE_LOCALTIME_R
468   struct tm loc_buf;
469   tms = localtime_r(&time, &loc_buf);
470 #else
471   tms = localtime(&time);
472 #endif
473 
474   if (!tms)
475   {
476     CLog::LogF(LOGWARNING, "localtime() returned NULL!");
477     return {};
478   }
479 
480   int isdst = tms->tm_isdst;
481 
482 #ifdef HAVE_GMTIME_R
483   struct tm gm_buf;
484   tms = gmtime_r(&time, &gm_buf);
485 #else
486   tms = gmtime(&time);
487 #endif
488 
489   if (!tms)
490   {
491     CLog::LogF(LOGWARNING, "gmtime() returned NULL!");
492     return {};
493   }
494 
495   tms->tm_isdst = isdst;
496   return CDateTime(mktime(tms));
497 }
498 
499 class CSearchTermConverter
500 {
501 public:
CSearchTermConverter(const std::string & strSearchTerm)502   CSearchTermConverter(const std::string& strSearchTerm) { Parse(strSearchTerm); }
503 
ToSQL(const std::string & strFieldName) const504   std::string ToSQL(const std::string& strFieldName) const
505   {
506     std::string result = "(";
507 
508     for (auto it = m_fragments.cbegin(); it != m_fragments.cend();)
509     {
510       result += (*it);
511 
512       ++it;
513       if (it != m_fragments.cend())
514         result += strFieldName;
515     }
516 
517     StringUtils::TrimRight(result);
518     result += ")";
519     return result;
520   }
521 
522 private:
Parse(const std::string & strSearchTerm)523   void Parse(const std::string& strSearchTerm)
524   {
525     std::string strParsedSearchTerm(strSearchTerm);
526     StringUtils::Trim(strParsedSearchTerm);
527 
528     std::string strFragment;
529 
530     bool bNextOR = false;
531     while (!strParsedSearchTerm.empty())
532     {
533       StringUtils::TrimLeft(strParsedSearchTerm);
534 
535       if (StringUtils::StartsWith(strParsedSearchTerm, "!") ||
536           StringUtils::StartsWithNoCase(strParsedSearchTerm, "not"))
537       {
538         std::string strDummy;
539         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
540         strFragment += " NOT ";
541         bNextOR = false;
542       }
543       else if (StringUtils::StartsWith(strParsedSearchTerm, "+") ||
544                StringUtils::StartsWithNoCase(strParsedSearchTerm, "and"))
545       {
546         std::string strDummy;
547         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
548         strFragment += " AND ";
549         bNextOR = false;
550       }
551       else if (StringUtils::StartsWith(strParsedSearchTerm, "|") ||
552                StringUtils::StartsWithNoCase(strParsedSearchTerm, "or"))
553       {
554         std::string strDummy;
555         GetAndCutNextTerm(strParsedSearchTerm, strDummy);
556         strFragment += " OR ";
557         bNextOR = false;
558       }
559       else
560       {
561         std::string strTerm;
562         GetAndCutNextTerm(strParsedSearchTerm, strTerm);
563         if (!strTerm.empty())
564         {
565           if (bNextOR && !m_fragments.empty())
566             strFragment += " OR "; // default operator
567 
568           strFragment += "(UPPER(";
569 
570           m_fragments.emplace_back(strFragment);
571           strFragment.clear();
572 
573           strFragment += ") LIKE UPPER('%";
574           StringUtils::Replace(strTerm, "'", "''"); // escape '
575           strFragment += strTerm;
576           strFragment += "%')) ";
577 
578           bNextOR = true;
579         }
580         else
581         {
582           break;
583         }
584       }
585 
586       StringUtils::TrimLeft(strParsedSearchTerm);
587     }
588 
589     if (!strFragment.empty())
590       m_fragments.emplace_back(strFragment);
591   }
592 
GetAndCutNextTerm(std::string & strSearchTerm,std::string & strNextTerm)593   static void GetAndCutNextTerm(std::string& strSearchTerm, std::string& strNextTerm)
594   {
595     std::string strFindNext(" ");
596 
597     if (StringUtils::EndsWith(strSearchTerm, "\""))
598     {
599       strSearchTerm.erase(0, 1);
600       strFindNext = "\"";
601     }
602 
603     const size_t iNextPos = strSearchTerm.find(strFindNext);
604     if (iNextPos != std::string::npos)
605     {
606       strNextTerm = strSearchTerm.substr(0, iNextPos);
607       strSearchTerm.erase(0, iNextPos + 1);
608     }
609     else
610     {
611       strNextTerm = strSearchTerm;
612       strSearchTerm.clear();
613     }
614   }
615 
616   std::vector<std::string> m_fragments;
617 };
618 
619 } // unnamed namespace
620 
GetEpgTags(const PVREpgSearchData & searchData)621 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTags(
622     const PVREpgSearchData& searchData)
623 {
624   CSingleLock lock(m_critSection);
625 
626   std::string strQuery = PrepareSQL("SELECT * FROM epgtags");
627 
628   Filter filter;
629 
630   /////////////////////////////////////////////////////////////////////////////////////////////
631   // broadcast UID
632   /////////////////////////////////////////////////////////////////////////////////////////////
633 
634   if (searchData.m_iUniqueBroadcastId != EPG_TAG_INVALID_UID)
635   {
636     filter.AppendWhere(PrepareSQL("iBroadcastUid = %u", searchData.m_iUniqueBroadcastId));
637   }
638 
639   /////////////////////////////////////////////////////////////////////////////////////////////
640   // min start datetime
641   /////////////////////////////////////////////////////////////////////////////////////////////
642 
643   const CDateTime minStartTime = ConvertLocalTimeToUTC(searchData.m_startDateTime);
644   time_t minStart;
645   minStartTime.GetAsTime(minStart);
646   filter.AppendWhere(PrepareSQL("iStartTime >= %u", static_cast<unsigned int>(minStart)));
647 
648   /////////////////////////////////////////////////////////////////////////////////////////////
649   // max end datetime
650   /////////////////////////////////////////////////////////////////////////////////////////////
651 
652   const CDateTime maxEndTime = ConvertLocalTimeToUTC(searchData.m_endDateTime);
653   time_t maxEnd;
654   maxEndTime.GetAsTime(maxEnd);
655   filter.AppendWhere(PrepareSQL("iEndTime <= %u", static_cast<unsigned int>(maxEnd)));
656 
657   /////////////////////////////////////////////////////////////////////////////////////////////
658   // genre type
659   /////////////////////////////////////////////////////////////////////////////////////////////
660 
661   if (searchData.m_iGenreType != EPG_SEARCH_UNSET)
662   {
663     filter.AppendWhere(PrepareSQL("(iGenreType < %u) OR (iGenreType > %u) OR (iGenreType = %u)",
664                                   EPG_EVENT_CONTENTMASK_MOVIEDRAMA,
665                                   EPG_EVENT_CONTENTMASK_USERDEFINED, searchData.m_iGenreType));
666   }
667 
668   /////////////////////////////////////////////////////////////////////////////////////////////
669   // search term
670   /////////////////////////////////////////////////////////////////////////////////////////////
671 
672   if (!searchData.m_strSearchTerm.empty())
673   {
674     const CSearchTermConverter conv(searchData.m_strSearchTerm);
675 
676     // title
677     std::string strWhere = conv.ToSQL("sTitle");
678 
679     // plot outline
680     strWhere += " OR ";
681     strWhere += conv.ToSQL("sPlotOutline");
682 
683     if (searchData.m_bSearchInDescription)
684     {
685       // plot
686       strWhere += " OR ";
687       strWhere += conv.ToSQL("sPlot");
688     }
689 
690     filter.AppendWhere(strWhere);
691   }
692 
693   if (BuildSQL(strQuery, filter, strQuery))
694   {
695     try
696     {
697       if (m_pDS->query(strQuery))
698       {
699         std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
700         while (!m_pDS->eof())
701         {
702           tags.emplace_back(CreateEpgTag(m_pDS));
703           m_pDS->next();
704         }
705         m_pDS->close();
706         return tags;
707       }
708     }
709     catch (...)
710     {
711       CLog::LogF(LOGERROR, "Could not load tags for given search criteria");
712     }
713   }
714 
715   return {};
716 }
717 
GetEpgTagByUniqueBroadcastID(int iEpgID,unsigned int iUniqueBroadcastId)718 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByUniqueBroadcastID(
719     int iEpgID, unsigned int iUniqueBroadcastId)
720 {
721   CSingleLock lock(m_critSection);
722   const std::string strQuery = PrepareSQL("SELECT * "
723                                           "FROM epgtags "
724                                           "WHERE idEpg = %u AND iBroadcastUid = %u;",
725                                           iEpgID, iUniqueBroadcastId);
726 
727   if (ResultQuery(strQuery))
728   {
729     try
730     {
731       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
732       m_pDS->close();
733       return tag;
734     }
735     catch (...)
736     {
737       CLog::LogF(LOGERROR, "Could not load EPG tag with unique broadcast ID ({}) from the database",
738                  iUniqueBroadcastId);
739     }
740   }
741 
742   return {};
743 }
744 
GetEpgTagByDatabaseID(int iEpgID,int iDatabaseId)745 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByDatabaseID(int iEpgID, int iDatabaseId)
746 {
747   CSingleLock lock(m_critSection);
748   const std::string strQuery = PrepareSQL("SELECT * "
749                                           "FROM epgtags "
750                                           "WHERE idEpg = %u AND idBroadcast = %u;",
751                                           iEpgID, iDatabaseId);
752 
753   if (ResultQuery(strQuery))
754   {
755     try
756     {
757       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
758       m_pDS->close();
759       return tag;
760     }
761     catch (...)
762     {
763       CLog::LogF(LOGERROR, "Could not load EPG tag with database ID (%u) from the database",
764                  iDatabaseId);
765     }
766   }
767 
768   return {};
769 }
770 
GetEpgTagByStartTime(int iEpgID,const CDateTime & startTime)771 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByStartTime(int iEpgID,
772                                                                       const CDateTime& startTime)
773 {
774   time_t start;
775   startTime.GetAsTime(start);
776 
777   CSingleLock lock(m_critSection);
778   const std::string strQuery = PrepareSQL("SELECT * "
779                                           "FROM epgtags "
780                                           "WHERE idEpg = %u AND iStartTime = %u;",
781                                           iEpgID, static_cast<unsigned int>(start));
782 
783   if (ResultQuery(strQuery))
784   {
785     try
786     {
787       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
788       m_pDS->close();
789       return tag;
790     }
791     catch (...)
792     {
793       CLog::LogF(LOGERROR, "Could not load EPG tag with start time ({}) from the database",
794                  startTime.GetAsDBDateTime());
795     }
796   }
797 
798   return {};
799 }
800 
GetEpgTagByMinStartTime(int iEpgID,const CDateTime & minStartTime)801 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByMinStartTime(
802     int iEpgID, const CDateTime& minStartTime)
803 {
804   time_t minStart;
805   minStartTime.GetAsTime(minStart);
806 
807   CSingleLock lock(m_critSection);
808   const std::string strQuery =
809       PrepareSQL("SELECT * "
810                  "FROM epgtags "
811                  "WHERE idEpg = %u AND iStartTime >= %u ORDER BY iStartTime ASC LIMIT 1;",
812                  iEpgID, static_cast<unsigned int>(minStart));
813 
814   if (ResultQuery(strQuery))
815   {
816     try
817     {
818       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
819       m_pDS->close();
820       return tag;
821     }
822     catch (...)
823     {
824       CLog::LogF(LOGERROR, "Could not load tags with min start time ({}) for EPG ({})",
825                  minStartTime.GetAsDBDateTime(), iEpgID);
826     }
827   }
828 
829   return {};
830 }
831 
GetEpgTagByMaxEndTime(int iEpgID,const CDateTime & maxEndTime)832 std::shared_ptr<CPVREpgInfoTag> CPVREpgDatabase::GetEpgTagByMaxEndTime(int iEpgID,
833                                                                        const CDateTime& maxEndTime)
834 {
835   time_t maxEnd;
836   maxEndTime.GetAsTime(maxEnd);
837 
838   CSingleLock lock(m_critSection);
839   const std::string strQuery =
840       PrepareSQL("SELECT * "
841                  "FROM epgtags "
842                  "WHERE idEpg = %u AND iEndTime <= %u ORDER BY iStartTime DESC LIMIT 1;",
843                  iEpgID, static_cast<unsigned int>(maxEnd));
844 
845   if (ResultQuery(strQuery))
846   {
847     try
848     {
849       std::shared_ptr<CPVREpgInfoTag> tag = CreateEpgTag(m_pDS);
850       m_pDS->close();
851       return tag;
852     }
853     catch (...)
854     {
855       CLog::LogF(LOGERROR, "Could not load tags with max end time ({}) for EPG ({})",
856                  maxEndTime.GetAsDBDateTime(), iEpgID);
857     }
858   }
859 
860   return {};
861 }
862 
GetEpgTagsByMinStartMaxEndTime(int iEpgID,const CDateTime & minStartTime,const CDateTime & maxEndTime)863 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTagsByMinStartMaxEndTime(
864     int iEpgID, const CDateTime& minStartTime, const CDateTime& maxEndTime)
865 {
866   time_t minStart;
867   minStartTime.GetAsTime(minStart);
868 
869   time_t maxEnd;
870   maxEndTime.GetAsTime(maxEnd);
871 
872   CSingleLock lock(m_critSection);
873   const std::string strQuery =
874       PrepareSQL("SELECT * "
875                  "FROM epgtags "
876                  "WHERE idEpg = %u AND iStartTime >= %u AND iEndTime <= %u ORDER BY iStartTime;",
877                  iEpgID, static_cast<unsigned int>(minStart), static_cast<unsigned int>(maxEnd));
878 
879   if (ResultQuery(strQuery))
880   {
881     try
882     {
883       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
884       while (!m_pDS->eof())
885       {
886         tags.emplace_back(CreateEpgTag(m_pDS));
887         m_pDS->next();
888       }
889       m_pDS->close();
890       return tags;
891     }
892     catch (...)
893     {
894       CLog::LogF(LOGERROR,
895                  "Could not load tags with min start time ({}) and max end time ({}) for EPG ({})",
896                  minStartTime.GetAsDBDateTime(), maxEndTime.GetAsDBDateTime(), iEpgID);
897     }
898   }
899 
900   return {};
901 }
902 
GetEpgTagsByMinEndMaxStartTime(int iEpgID,const CDateTime & minEndTime,const CDateTime & maxStartTime)903 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetEpgTagsByMinEndMaxStartTime(
904     int iEpgID, const CDateTime& minEndTime, const CDateTime& maxStartTime)
905 {
906   time_t minEnd;
907   minEndTime.GetAsTime(minEnd);
908 
909   time_t maxStart;
910   maxStartTime.GetAsTime(maxStart);
911 
912   CSingleLock lock(m_critSection);
913   const std::string strQuery =
914       PrepareSQL("SELECT * "
915                  "FROM epgtags "
916                  "WHERE idEpg = %u AND iEndTime >= %u AND iStartTime <= %u ORDER BY iStartTime;",
917                  iEpgID, static_cast<unsigned int>(minEnd), static_cast<unsigned int>(maxStart));
918 
919   if (ResultQuery(strQuery))
920   {
921     try
922     {
923       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
924       while (!m_pDS->eof())
925       {
926         tags.emplace_back(CreateEpgTag(m_pDS));
927         m_pDS->next();
928       }
929       m_pDS->close();
930       return tags;
931     }
932     catch (...)
933     {
934       CLog::LogF(LOGERROR,
935                  "Could not load tags with min end time ({}) and max start time ({}) for EPG ({})",
936                  minEndTime.GetAsDBDateTime(), maxStartTime.GetAsDBDateTime(), iEpgID);
937     }
938   }
939 
940   return {};
941 }
942 
QueueDeleteEpgTagsByMinEndMaxStartTimeQuery(int iEpgID,const CDateTime & minEndTime,const CDateTime & maxStartTime)943 bool CPVREpgDatabase::QueueDeleteEpgTagsByMinEndMaxStartTimeQuery(int iEpgID,
944                                                                   const CDateTime& minEndTime,
945                                                                   const CDateTime& maxStartTime)
946 {
947   time_t minEnd;
948   minEndTime.GetAsTime(minEnd);
949 
950   time_t maxStart;
951   maxStartTime.GetAsTime(maxStart);
952 
953   Filter filter;
954 
955   CSingleLock lock(m_critSection);
956   filter.AppendWhere(PrepareSQL("idEpg = %u AND iEndTime >= %u AND iStartTime <= %u", iEpgID,
957                                 static_cast<unsigned int>(minEnd),
958                                 static_cast<unsigned int>(maxStart)));
959 
960   std::string strQuery;
961   if (BuildSQL("DELETE FROM epgtags", filter, strQuery))
962     return QueueDeleteQuery(strQuery);
963 
964   return false;
965 }
966 
GetAllEpgTags(int iEpgID)967 std::vector<std::shared_ptr<CPVREpgInfoTag>> CPVREpgDatabase::GetAllEpgTags(int iEpgID)
968 {
969   CSingleLock lock(m_critSection);
970   const std::string strQuery =
971       PrepareSQL("SELECT * FROM epgtags WHERE idEpg = %u ORDER BY iStartTime;", iEpgID);
972   if (ResultQuery(strQuery))
973   {
974     try
975     {
976       std::vector<std::shared_ptr<CPVREpgInfoTag>> tags;
977       while (!m_pDS->eof())
978       {
979         tags.emplace_back(CreateEpgTag(m_pDS));
980         m_pDS->next();
981       }
982       m_pDS->close();
983       return tags;
984     }
985     catch (...)
986     {
987       CLog::LogF(LOGERROR, "Could not load tags for EPG ({})", iEpgID);
988     }
989   }
990   return {};
991 }
992 
GetLastEpgScanTime(int iEpgId,CDateTime * lastScan)993 bool CPVREpgDatabase::GetLastEpgScanTime(int iEpgId, CDateTime* lastScan)
994 {
995   bool bReturn = false;
996 
997   CSingleLock lock(m_critSection);
998   std::string strWhereClause = PrepareSQL("idEpg = %u", iEpgId);
999   std::string strValue = GetSingleValue("lastepgscan", "sLastScan", strWhereClause);
1000 
1001   if (!strValue.empty())
1002   {
1003     lastScan->SetFromDBDateTime(strValue.c_str());
1004     bReturn = true;
1005   }
1006   else
1007   {
1008     lastScan->SetValid(false);
1009   }
1010 
1011   return bReturn;
1012 }
1013 
QueuePersistLastEpgScanTimeQuery(int iEpgId,const CDateTime & lastScanTime)1014 bool CPVREpgDatabase::QueuePersistLastEpgScanTimeQuery(int iEpgId, const CDateTime& lastScanTime)
1015 {
1016   CSingleLock lock(m_critSection);
1017   std::string strQuery = PrepareSQL("REPLACE INTO lastepgscan(idEpg, sLastScan) VALUES (%u, '%s');",
1018       iEpgId, lastScanTime.GetAsDBDateTime().c_str());
1019 
1020   return QueueInsertQuery(strQuery);
1021 }
1022 
QueueDeleteLastEpgScanTimeQuery(const CPVREpg & table)1023 bool CPVREpgDatabase::QueueDeleteLastEpgScanTimeQuery(const CPVREpg& table)
1024 {
1025   if (table.EpgID() <= 0)
1026   {
1027     CLog::LogF(LOGERROR, "Invalid EPG id: {}", table.EpgID());
1028     return false;
1029   }
1030 
1031   Filter filter;
1032 
1033   CSingleLock lock(m_critSection);
1034   filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
1035 
1036   std::string strQuery;
1037   if (BuildSQL(PrepareSQL("DELETE FROM %s ", "lastepgscan"), filter, strQuery))
1038     return QueueDeleteQuery(strQuery);
1039 
1040   return false;
1041 }
1042 
Persist(const CPVREpg & epg,bool bQueueWrite)1043 int CPVREpgDatabase::Persist(const CPVREpg& epg, bool bQueueWrite)
1044 {
1045   int iReturn = -1;
1046   std::string strQuery;
1047 
1048   CSingleLock lock(m_critSection);
1049   if (epg.EpgID() > 0)
1050     strQuery = PrepareSQL("REPLACE INTO epg (idEpg, sName, sScraperName) "
1051                           "VALUES (%u, '%s', '%s');",
1052                           epg.EpgID(), epg.Name().c_str(), epg.ScraperName().c_str());
1053   else
1054     strQuery = PrepareSQL("INSERT INTO epg (sName, sScraperName) "
1055                           "VALUES ('%s', '%s');",
1056                           epg.Name().c_str(), epg.ScraperName().c_str());
1057 
1058   if (bQueueWrite)
1059   {
1060     if (QueueInsertQuery(strQuery))
1061       iReturn = epg.EpgID() <= 0 ? 0 : epg.EpgID();
1062   }
1063   else
1064   {
1065     if (ExecuteQuery(strQuery))
1066       iReturn = epg.EpgID() <= 0 ? static_cast<int>(m_pDS->lastinsertid()) : epg.EpgID();
1067   }
1068 
1069   return iReturn;
1070 }
1071 
DeleteEpgTags(int iEpgId,const CDateTime & maxEndTime)1072 bool CPVREpgDatabase::DeleteEpgTags(int iEpgId, const CDateTime& maxEndTime)
1073 {
1074   time_t iMaxEndTime;
1075   maxEndTime.GetAsTime(iMaxEndTime);
1076 
1077   Filter filter;
1078 
1079   CSingleLock lock(m_critSection);
1080   filter.AppendWhere(
1081       PrepareSQL("idEpg = %u AND iEndTime < %u", iEpgId, static_cast<unsigned int>(iMaxEndTime)));
1082   return DeleteValues("epgtags", filter);
1083 }
1084 
DeleteEpgTags(int iEpgId)1085 bool CPVREpgDatabase::DeleteEpgTags(int iEpgId)
1086 {
1087   Filter filter;
1088 
1089   CSingleLock lock(m_critSection);
1090   filter.AppendWhere(PrepareSQL("idEpg = %u", iEpgId));
1091   return DeleteValues("epgtags", filter);
1092 }
1093 
QueueDeleteEpgTags(int iEpgId)1094 bool CPVREpgDatabase::QueueDeleteEpgTags(int iEpgId)
1095 {
1096   Filter filter;
1097 
1098   CSingleLock lock(m_critSection);
1099   filter.AppendWhere(PrepareSQL("idEpg = %u", iEpgId));
1100 
1101   std::string strQuery;
1102   BuildSQL(PrepareSQL("DELETE FROM %s ", "epgtags"), filter, strQuery);
1103   return QueueDeleteQuery(strQuery);
1104 }
1105 
QueuePersistQuery(const CPVREpgInfoTag & tag)1106 bool CPVREpgDatabase::QueuePersistQuery(const CPVREpgInfoTag& tag)
1107 {
1108   if (tag.EpgID() <= 0)
1109   {
1110     CLog::LogF(LOGERROR, "Tag '{}' does not have a valid table", tag.Title());
1111     return false;
1112   }
1113 
1114   time_t iStartTime, iEndTime;
1115   tag.StartAsUTC().GetAsTime(iStartTime);
1116   tag.EndAsUTC().GetAsTime(iEndTime);
1117 
1118   std::string sFirstAired;
1119   if (tag.FirstAired().IsValid())
1120     sFirstAired = tag.FirstAired().GetAsW3CDate();
1121 
1122   int iBroadcastId = tag.DatabaseID();
1123   std::string strQuery;
1124 
1125   /* Only store the genre string when needed */
1126   std::string strGenre = (tag.GenreType() == EPG_GENRE_USE_STRING || tag.GenreSubType() == EPG_GENRE_USE_STRING) ? tag.DeTokenize(tag.Genre()) : "";
1127 
1128   CSingleLock lock(m_critSection);
1129 
1130   if (iBroadcastId < 0)
1131   {
1132     strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
1133         "iEndTime, sTitle, sPlotOutline, sPlot, sOriginalTitle, sCast, sDirector, sWriter, iYear, sIMDBNumber, "
1134         "sIconPath, iGenreType, iGenreSubType, sGenre, sFirstAired, iParentalRating, iStarRating, iSeriesId, "
1135         "iEpisodeId, iEpisodePart, sEpisodeName, iFlags, sSeriesLink, iBroadcastUid) "
1136         "VALUES (%u, %u, %u, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i, '%s', '%s', %i, %i, '%s', '%s', %i, %i, %i, %i, %i, '%s', %i, '%s', %i);",
1137         tag.EpgID(), static_cast<unsigned int>(iStartTime), static_cast<unsigned int>(iEndTime),
1138         tag.Title().c_str(), tag.PlotOutline().c_str(), tag.Plot().c_str(),
1139         tag.OriginalTitle().c_str(), tag.DeTokenize(tag.Cast()).c_str(), tag.DeTokenize(tag.Directors()).c_str(),
1140         tag.DeTokenize(tag.Writers()).c_str(), tag.Year(), tag.IMDBNumber().c_str(),
1141         tag.Icon().c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
1142         sFirstAired.c_str(), tag.ParentalRating(), tag.StarRating(),
1143         tag.SeriesNumber(), tag.EpisodeNumber(), tag.EpisodePart(), tag.EpisodeName().c_str(), tag.Flags(), tag.SeriesLink().c_str(),
1144         tag.UniqueBroadcastID());
1145   }
1146   else
1147   {
1148     strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
1149         "iEndTime, sTitle, sPlotOutline, sPlot, sOriginalTitle, sCast, sDirector, sWriter, iYear, sIMDBNumber, "
1150         "sIconPath, iGenreType, iGenreSubType, sGenre, sFirstAired, iParentalRating, iStarRating, iSeriesId, "
1151         "iEpisodeId, iEpisodePart, sEpisodeName, iFlags, sSeriesLink, iBroadcastUid, idBroadcast) "
1152         "VALUES (%u, %u, %u, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %i, '%s', '%s', %i, %i, '%s', '%s', %i, %i, %i, %i, %i, '%s', %i, '%s', %i, %i);",
1153         tag.EpgID(), static_cast<unsigned int>(iStartTime), static_cast<unsigned int>(iEndTime),
1154         tag.Title().c_str(), tag.PlotOutline().c_str(), tag.Plot().c_str(),
1155         tag.OriginalTitle().c_str(), tag.DeTokenize(tag.Cast()).c_str(), tag.DeTokenize(tag.Directors()).c_str(),
1156         tag.DeTokenize(tag.Writers()).c_str(), tag.Year(), tag.IMDBNumber().c_str(),
1157         tag.Icon().c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
1158         sFirstAired.c_str(), tag.ParentalRating(), tag.StarRating(),
1159         tag.SeriesNumber(), tag.EpisodeNumber(), tag.EpisodePart(), tag.EpisodeName().c_str(), tag.Flags(), tag.SeriesLink().c_str(),
1160         tag.UniqueBroadcastID(), iBroadcastId);
1161   }
1162 
1163   QueueInsertQuery(strQuery);
1164   return true;
1165 }
1166 
GetLastEPGId()1167 int CPVREpgDatabase::GetLastEPGId()
1168 {
1169   CSingleLock lock(m_critSection);
1170   std::string strQuery = PrepareSQL("SELECT MAX(idEpg) FROM epg");
1171   std::string strValue = GetSingleValue(strQuery);
1172   if (!strValue.empty())
1173     return std::atoi(strValue.c_str());
1174   return 0;
1175 }
1176