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