2 * Copyright (C) 2012-2013 Team XBMC
5 * This Program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2, or (at your option)
10 * This Program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with XBMC; see the file COPYING. If not, see
17 * <http://www.gnu.org/licenses/>.
21 #include "dbwrappers/dataset.h"
22 #include "settings/AdvancedSettings.h"
23 #include "settings/VideoSettings.h"
24 #include "utils/log.h"
25 #include "addons/include/xbmc_pvr_types.h"
27 #include "EpgDatabase.h"
28 #include "EpgContainer.h"
32 using namespace dbiplus;
35 bool CEpgDatabase::Open(void)
37 return CDatabase::Open(g_advancedSettings.m_databaseEpg);
40 bool CEpgDatabase::CreateTables(void)
46 CDatabase::CreateTables();
50 CLog::Log(LOGINFO, "EpgDB - %s - creating tables", __FUNCTION__);
52 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'epg'", __FUNCTION__);
55 "idEpg integer primary key, "
57 "sScraperName varchar(32)"
61 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'epgtags'", __FUNCTION__);
63 "CREATE TABLE epgtags ("
64 "idBroadcast integer primary key, "
65 "iBroadcastUid integer, "
67 "sTitle varchar(128), "
70 "iStartTime integer, "
72 "iGenreType integer, "
73 "iGenreSubType integer, "
74 "sGenre varchar(128), "
75 "iFirstAired integer, "
76 "iParentalRating integer, "
77 "iStarRating integer, "
80 "iEpisodeId integer, "
81 "iEpisodePart integer, "
82 "sEpisodeName varchar(128)"
85 m_pDS->exec("CREATE UNIQUE INDEX idx_epg_idEpg_iStartTime on epgtags(idEpg, iStartTime desc);");
86 m_pDS->exec("CREATE INDEX idx_epg_iEndTime on epgtags(iEndTime);");
88 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'lastepgscan'", __FUNCTION__);
89 m_pDS->exec("CREATE TABLE lastepgscan ("
90 "idEpg integer primary key, "
91 "sLastScan varchar(20)"
101 CLog::Log(LOGERROR, "EpgDB - %s - unable to create EPG tables:%i",
102 __FUNCTION__, (int)GetLastError());
103 RollbackTransaction();
110 bool CEpgDatabase::UpdateOldVersion(int iVersion)
116 CLog::Log(LOGERROR, "EpgDB - %s - updating from table versions < 4 not supported. please delete '%s'", __FUNCTION__, GetBaseDBName());
125 m_pDS->exec("ALTER TABLE epgtags ADD sGenre varchar(128);");
128 m_pDS->exec("DROP INDEX idx_epg_iBroadcastUid;");
129 m_pDS->exec("DROP INDEX idx_epg_idEpg;");
130 m_pDS->exec("DROP INDEX idx_epg_iStartTime;");
131 m_pDS->exec("DROP INDEX idx_epg_iEndTime;");
135 m_pDS->exec("CREATE INDEX idx_epg_iEndTime on epgtags(iEndTime);");
140 CLog::Log(LOGERROR, "Error attempting to update the database version!");
147 RollbackTransaction();
152 bool CEpgDatabase::DeleteEpg(void)
155 CLog::Log(LOGDEBUG, "EpgDB - %s - deleting all EPG data from the database", __FUNCTION__);
157 bReturn = DeleteValues("epg") || bReturn;
158 bReturn = DeleteValues("epgtags") || bReturn;
159 bReturn = DeleteValues("lastepgscan") || bReturn;
164 bool CEpgDatabase::Delete(const CEpg &table)
166 /* invalid channel */
167 if (table.EpgID() <= 0)
169 CLog::Log(LOGERROR, "EpgDB - %s - invalid channel id: %d", __FUNCTION__, table.EpgID());
174 filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
176 return DeleteValues("epg", filter);
179 bool CEpgDatabase::DeleteOldEpgEntries(void)
182 CDateTime cleanupTime = CDateTime::GetCurrentDateTime().GetAsUTCDateTime() -
183 CDateTimeSpan(0, g_advancedSettings.m_iEpgLingerTime / 60, g_advancedSettings.m_iEpgLingerTime % 60, 0);
184 cleanupTime.GetAsTime(iCleanupTime);
187 filter.AppendWhere(PrepareSQL("iEndTime < %u", iCleanupTime));
189 return DeleteValues("epgtags", filter);
192 bool CEpgDatabase::Delete(const CEpgInfoTag &tag)
194 /* tag without a database ID was not persisted */
195 if (tag.BroadcastId() <= 0)
199 filter.AppendWhere(PrepareSQL("idBroadcast = %u", tag.BroadcastId()));
201 return DeleteValues("epgtags", filter);
204 int CEpgDatabase::Get(CEpgContainer &container)
208 CStdString strQuery = PrepareSQL("SELECT idEpg, sName, sScraperName FROM epg;");
209 if (ResultQuery(strQuery))
215 while (!m_pDS->eof())
217 int iEpgID = m_pDS->fv("idEpg").get_asInt();
218 CStdString strName = m_pDS->fv("sName").get_asString().c_str();
219 CStdString strScraperName = m_pDS->fv("sScraperName").get_asString().c_str();
221 container.InsertFromDatabase(iEpgID, strName, strScraperName);
229 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
236 int CEpgDatabase::Get(CEpg &epg)
240 CStdString strQuery = PrepareSQL("SELECT * FROM epgtags WHERE idEpg = %u;", epg.EpgID());
241 if (ResultQuery(strQuery))
246 while (!m_pDS->eof())
250 time_t iStartTime, iEndTime, iFirstAired;
251 iStartTime = (time_t) m_pDS->fv("iStartTime").get_asInt();
252 CDateTime startTime(iStartTime);
253 newTag.m_startTime = startTime;
255 iEndTime = (time_t) m_pDS->fv("iEndTime").get_asInt();
256 CDateTime endTime(iEndTime);
257 newTag.m_endTime = endTime;
259 iFirstAired = (time_t) m_pDS->fv("iFirstAired").get_asInt();
260 CDateTime firstAired(iFirstAired);
261 newTag.m_firstAired = firstAired;
263 newTag.m_iUniqueBroadcastID = m_pDS->fv("iBroadcastUid").get_asInt();
264 newTag.m_iBroadcastId = m_pDS->fv("idBroadcast").get_asInt();
265 newTag.m_strTitle = m_pDS->fv("sTitle").get_asString().c_str();
266 newTag.m_strPlotOutline = m_pDS->fv("sPlotOutline").get_asString().c_str();
267 newTag.m_strPlot = m_pDS->fv("sPlot").get_asString().c_str();
268 newTag.m_iGenreType = m_pDS->fv("iGenreType").get_asInt();
269 newTag.m_iGenreSubType = m_pDS->fv("iGenreSubType").get_asInt();
270 newTag.m_genre = StringUtils::Split(m_pDS->fv("sGenre").get_asString().c_str(), g_advancedSettings.m_videoItemSeparator);
271 newTag.m_iParentalRating = m_pDS->fv("iParentalRating").get_asInt();
272 newTag.m_iStarRating = m_pDS->fv("iStarRating").get_asInt();
273 newTag.m_bNotify = m_pDS->fv("bNotify").get_asBool();
274 newTag.m_iEpisodeNumber = m_pDS->fv("iEpisodeId").get_asInt();
275 newTag.m_iEpisodePart = m_pDS->fv("iEpisodePart").get_asInt();
276 newTag.m_strEpisodeName = m_pDS->fv("sEpisodeName").get_asString().c_str();
277 newTag.m_iSeriesNumber = m_pDS->fv("iSeriesId").get_asInt();
279 epg.AddEntry(newTag);
288 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
294 bool CEpgDatabase::GetLastEpgScanTime(int iEpgId, CDateTime *lastScan)
296 bool bReturn = false;
297 CStdString strWhereClause = PrepareSQL("idEpg = %u", iEpgId);
298 CStdString strValue = GetSingleValue("lastepgscan", "sLastScan", strWhereClause);
300 if (!strValue.empty())
302 lastScan->SetFromDBDateTime(strValue.c_str());
307 lastScan->SetValid(false);
313 bool CEpgDatabase::PersistLastEpgScanTime(int iEpgId /* = 0 */, bool bQueueWrite /* = false */)
315 CStdString strQuery = PrepareSQL("REPLACE INTO lastepgscan(idEpg, sLastScan) VALUES (%u, '%s');",
316 iEpgId, CDateTime::GetCurrentDateTime().GetAsUTCDateTime().GetAsDBDateTime().c_str());
318 return bQueueWrite ? QueueInsertQuery(strQuery) : ExecuteQuery(strQuery);
321 bool CEpgDatabase::Persist(const CEpgContainer &epg)
323 for (map<unsigned int, CEpg *>::const_iterator it = epg.m_epgs.begin(); it != epg.m_epgs.end(); it++)
325 CEpg *epg = it->second;
330 return CommitInsertQueries();
333 int CEpgDatabase::Persist(const CEpg &epg, bool bQueueWrite /* = false */)
339 strQuery = PrepareSQL("REPLACE INTO epg (idEpg, sName, sScraperName) "
340 "VALUES (%u, '%s', '%s');", epg.EpgID(), epg.Name().c_str(), epg.ScraperName().c_str());
342 strQuery = PrepareSQL("INSERT INTO epg (sName, sScraperName) "
343 "VALUES ('%s', '%s');", epg.Name().c_str(), epg.ScraperName().c_str());
347 if (QueueInsertQuery(strQuery))
348 iReturn = epg.EpgID() <= 0 ? 0 : epg.EpgID();
352 if (ExecuteQuery(strQuery))
353 iReturn = epg.EpgID() <= 0 ? (int) m_pDS->lastinsertid() : epg.EpgID();
359 int CEpgDatabase::Persist(const CEpgInfoTag &tag, bool bSingleUpdate /* = true */)
363 if (tag.EpgID() <= 0)
365 CLog::Log(LOGERROR, "%s - tag '%s' does not have a valid table", __FUNCTION__, tag.Title(true).c_str());
369 time_t iStartTime, iEndTime, iFirstAired;
370 tag.StartAsUTC().GetAsTime(iStartTime);
371 tag.EndAsUTC().GetAsTime(iEndTime);
372 tag.FirstAiredAsUTC().GetAsTime(iFirstAired);
374 int iBroadcastId = tag.BroadcastId();
377 /* Only store the genre string when needed */
378 CStdString strGenre = (tag.GenreType() == EPG_GENRE_USE_STRING) ? StringUtils::Join(tag.Genre(), g_advancedSettings.m_videoItemSeparator) : "";
380 if (iBroadcastId < 0)
382 strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
383 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
384 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
385 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid) "
386 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i);",
387 tag.EpgID(), iStartTime, iEndTime,
388 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
389 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
390 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
391 tag.UniqueBroadcastID());
395 strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
396 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
397 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
398 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid, idBroadcast) "
399 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i, %i);",
400 tag.EpgID(), iStartTime, iEndTime,
401 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
402 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
403 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
404 tag.UniqueBroadcastID(), iBroadcastId);
409 if (ExecuteQuery(strQuery))
410 iReturn = (int) m_pDS->lastinsertid();
414 QueueInsertQuery(strQuery);
421 int CEpgDatabase::GetLastEPGId(void)
423 CStdString strQuery = PrepareSQL("SELECT MAX(idEpg) FROM epg");
424 CStdString strValue = GetSingleValue(strQuery);
425 if (!strValue.empty())
426 return atoi(strValue.c_str());