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());
173 CStdString strWhereClause;
174 strWhereClause = FormatSQL("idEpg = %u", table.EpgID());
176 return DeleteValues("epg", strWhereClause);
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);
186 CStdString strWhereClause = FormatSQL("iEndTime < %u", iCleanupTime);
188 return DeleteValues("epgtags", strWhereClause);
191 bool CEpgDatabase::Delete(const CEpgInfoTag &tag)
193 /* tag without a database ID was not persisted */
194 if (tag.BroadcastId() <= 0)
197 CStdString strWhereClause = FormatSQL("idBroadcast = %u", tag.BroadcastId());
199 return DeleteValues("epgtags", strWhereClause);
202 int CEpgDatabase::Get(CEpgContainer &container)
206 CStdString strQuery = FormatSQL("SELECT idEpg, sName, sScraperName FROM epg;");
207 if (ResultQuery(strQuery))
213 while (!m_pDS->eof())
215 int iEpgID = m_pDS->fv("idEpg").get_asInt();
216 CStdString strName = m_pDS->fv("sName").get_asString().c_str();
217 CStdString strScraperName = m_pDS->fv("sScraperName").get_asString().c_str();
219 container.InsertFromDatabase(iEpgID, strName, strScraperName);
227 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
234 int CEpgDatabase::Get(CEpg &epg)
238 CStdString strQuery = FormatSQL("SELECT * FROM epgtags WHERE idEpg = %u;", epg.EpgID());
239 if (ResultQuery(strQuery))
244 while (!m_pDS->eof())
248 time_t iStartTime, iEndTime, iFirstAired;
249 iStartTime = (time_t) m_pDS->fv("iStartTime").get_asInt();
250 CDateTime startTime(iStartTime);
251 newTag.m_startTime = startTime;
253 iEndTime = (time_t) m_pDS->fv("iEndTime").get_asInt();
254 CDateTime endTime(iEndTime);
255 newTag.m_endTime = endTime;
257 iFirstAired = (time_t) m_pDS->fv("iFirstAired").get_asInt();
258 CDateTime firstAired(iFirstAired);
259 newTag.m_firstAired = firstAired;
261 newTag.m_iUniqueBroadcastID = m_pDS->fv("iBroadcastUid").get_asInt();
262 newTag.m_iBroadcastId = m_pDS->fv("idBroadcast").get_asInt();
263 newTag.m_strTitle = m_pDS->fv("sTitle").get_asString().c_str();
264 newTag.m_strPlotOutline = m_pDS->fv("sPlotOutline").get_asString().c_str();
265 newTag.m_strPlot = m_pDS->fv("sPlot").get_asString().c_str();
266 newTag.m_iGenreType = m_pDS->fv("iGenreType").get_asInt();
267 newTag.m_iGenreSubType = m_pDS->fv("iGenreSubType").get_asInt();
268 newTag.m_genre = StringUtils::Split(m_pDS->fv("sGenre").get_asString().c_str(), g_advancedSettings.m_videoItemSeparator);
269 newTag.m_iParentalRating = m_pDS->fv("iParentalRating").get_asInt();
270 newTag.m_iStarRating = m_pDS->fv("iStarRating").get_asInt();
271 newTag.m_bNotify = m_pDS->fv("bNotify").get_asBool();
272 newTag.m_iEpisodeNumber = m_pDS->fv("iEpisodeId").get_asInt();
273 newTag.m_iEpisodePart = m_pDS->fv("iEpisodePart").get_asInt();
274 newTag.m_strEpisodeName = m_pDS->fv("sEpisodeName").get_asString().c_str();
275 newTag.m_iSeriesNumber = m_pDS->fv("iSeriesId").get_asInt();
277 epg.AddEntry(newTag);
286 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
292 bool CEpgDatabase::GetLastEpgScanTime(int iEpgId, CDateTime *lastScan)
294 bool bReturn = false;
295 CStdString strWhereClause = FormatSQL("idEpg = %u", iEpgId);
296 CStdString strValue = GetSingleValue("lastepgscan", "sLastScan", strWhereClause);
298 if (!strValue.empty())
300 lastScan->SetFromDBDateTime(strValue.c_str());
305 lastScan->SetValid(false);
311 bool CEpgDatabase::PersistLastEpgScanTime(int iEpgId /* = 0 */, bool bQueueWrite /* = false */)
313 CStdString strQuery = FormatSQL("REPLACE INTO lastepgscan(idEpg, sLastScan) VALUES (%u, '%s');",
314 iEpgId, CDateTime::GetCurrentDateTime().GetAsUTCDateTime().GetAsDBDateTime().c_str());
316 return bQueueWrite ? QueueInsertQuery(strQuery) : ExecuteQuery(strQuery);
319 bool CEpgDatabase::Persist(const CEpgContainer &epg)
321 for (map<unsigned int, CEpg *>::const_iterator it = epg.m_epgs.begin(); it != epg.m_epgs.end(); it++)
323 CEpg *epg = it->second;
328 return CommitInsertQueries();
331 int CEpgDatabase::Persist(const CEpg &epg, bool bQueueWrite /* = false */)
337 strQuery = FormatSQL("REPLACE INTO epg (idEpg, sName, sScraperName) "
338 "VALUES (%u, '%s', '%s');", epg.EpgID(), epg.Name().c_str(), epg.ScraperName().c_str());
340 strQuery = FormatSQL("INSERT INTO epg (sName, sScraperName) "
341 "VALUES ('%s', '%s');", epg.Name().c_str(), epg.ScraperName().c_str());
345 if (QueueInsertQuery(strQuery))
346 iReturn = epg.EpgID() <= 0 ? 0 : epg.EpgID();
350 if (ExecuteQuery(strQuery))
351 iReturn = epg.EpgID() <= 0 ? (int) m_pDS->lastinsertid() : epg.EpgID();
357 int CEpgDatabase::Persist(const CEpgInfoTag &tag, bool bSingleUpdate /* = true */)
361 if (tag.EpgID() <= 0)
363 CLog::Log(LOGERROR, "%s - tag '%s' does not have a valid table", __FUNCTION__, tag.Title(true).c_str());
367 time_t iStartTime, iEndTime, iFirstAired;
368 tag.StartAsUTC().GetAsTime(iStartTime);
369 tag.EndAsUTC().GetAsTime(iEndTime);
370 tag.FirstAiredAsUTC().GetAsTime(iFirstAired);
372 int iBroadcastId = tag.BroadcastId();
375 /* Only store the genre string when needed */
376 CStdString strGenre = (tag.GenreType() == EPG_GENRE_USE_STRING) ? StringUtils::Join(tag.Genre(), g_advancedSettings.m_videoItemSeparator) : "";
378 if (iBroadcastId < 0)
380 strQuery = FormatSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
381 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
382 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
383 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid) "
384 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i);",
385 tag.EpgID(), iStartTime, iEndTime,
386 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
387 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
388 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
389 tag.UniqueBroadcastID());
393 strQuery = FormatSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
394 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
395 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
396 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid, idBroadcast) "
397 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i, %i);",
398 tag.EpgID(), iStartTime, iEndTime,
399 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
400 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
401 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
402 tag.UniqueBroadcastID(), iBroadcastId);
407 if (ExecuteQuery(strQuery))
408 iReturn = (int) m_pDS->lastinsertid();
412 QueueInsertQuery(strQuery);
419 int CEpgDatabase::GetLastEPGId(void)
421 CStdString strQuery = FormatSQL("SELECT MAX(idEpg) FROM epg");
422 CStdString strValue = GetSingleValue(strQuery);
423 if (!strValue.empty())
424 return atoi(strValue.c_str());