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 void CEpgDatabase::CreateTables(void)
42 CLog::Log(LOGINFO, "EpgDB - %s - creating tables", __FUNCTION__);
44 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'epg'", __FUNCTION__);
47 "idEpg integer primary key, "
49 "sScraperName varchar(32)"
53 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'epgtags'", __FUNCTION__);
55 "CREATE TABLE epgtags ("
56 "idBroadcast integer primary key, "
57 "iBroadcastUid integer, "
59 "sTitle varchar(128), "
62 "iStartTime integer, "
64 "iGenreType integer, "
65 "iGenreSubType integer, "
66 "sGenre varchar(128), "
67 "iFirstAired integer, "
68 "iParentalRating integer, "
69 "iStarRating integer, "
72 "iEpisodeId integer, "
73 "iEpisodePart integer, "
74 "sEpisodeName varchar(128)"
77 CLog::Log(LOGDEBUG, "EpgDB - %s - creating table 'lastepgscan'", __FUNCTION__);
78 m_pDS->exec("CREATE TABLE lastepgscan ("
79 "idEpg integer primary key, "
80 "sLastScan varchar(20)"
85 void CEpgDatabase::CreateAnalytics()
87 CLog::Log(LOGDEBUG, "%s - creating indices", __FUNCTION__);
88 m_pDS->exec("CREATE UNIQUE INDEX idx_epg_idEpg_iStartTime on epgtags(idEpg, iStartTime desc);");
89 m_pDS->exec("CREATE INDEX idx_epg_iEndTime on epgtags(iEndTime);");
92 void CEpgDatabase::UpdateTables(int iVersion)
95 m_pDS->exec("ALTER TABLE epgtags ADD sGenre varchar(128);");
98 bool CEpgDatabase::DeleteEpg(void)
101 CLog::Log(LOGDEBUG, "EpgDB - %s - deleting all EPG data from the database", __FUNCTION__);
103 bReturn = DeleteValues("epg") || bReturn;
104 bReturn = DeleteValues("epgtags") || bReturn;
105 bReturn = DeleteValues("lastepgscan") || bReturn;
110 bool CEpgDatabase::Delete(const CEpg &table)
112 /* invalid channel */
113 if (table.EpgID() <= 0)
115 CLog::Log(LOGERROR, "EpgDB - %s - invalid channel id: %d", __FUNCTION__, table.EpgID());
120 filter.AppendWhere(PrepareSQL("idEpg = %u", table.EpgID()));
122 return DeleteValues("epg", filter);
125 bool CEpgDatabase::DeleteOldEpgEntries(void)
128 CDateTime cleanupTime = CDateTime::GetCurrentDateTime().GetAsUTCDateTime() -
129 CDateTimeSpan(0, g_advancedSettings.m_iEpgLingerTime / 60, g_advancedSettings.m_iEpgLingerTime % 60, 0);
130 cleanupTime.GetAsTime(iCleanupTime);
133 filter.AppendWhere(PrepareSQL("iEndTime < %u", iCleanupTime));
135 return DeleteValues("epgtags", filter);
138 bool CEpgDatabase::Delete(const CEpgInfoTag &tag)
140 /* tag without a database ID was not persisted */
141 if (tag.BroadcastId() <= 0)
145 filter.AppendWhere(PrepareSQL("idBroadcast = %u", tag.BroadcastId()));
147 return DeleteValues("epgtags", filter);
150 int CEpgDatabase::Get(CEpgContainer &container)
154 CStdString strQuery = PrepareSQL("SELECT idEpg, sName, sScraperName FROM epg;");
155 if (ResultQuery(strQuery))
161 while (!m_pDS->eof())
163 int iEpgID = m_pDS->fv("idEpg").get_asInt();
164 CStdString strName = m_pDS->fv("sName").get_asString().c_str();
165 CStdString strScraperName = m_pDS->fv("sScraperName").get_asString().c_str();
167 container.InsertFromDatabase(iEpgID, strName, strScraperName);
175 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
182 int CEpgDatabase::Get(CEpg &epg)
186 CStdString strQuery = PrepareSQL("SELECT * FROM epgtags WHERE idEpg = %u;", epg.EpgID());
187 if (ResultQuery(strQuery))
192 while (!m_pDS->eof())
196 time_t iStartTime, iEndTime, iFirstAired;
197 iStartTime = (time_t) m_pDS->fv("iStartTime").get_asInt();
198 CDateTime startTime(iStartTime);
199 newTag.m_startTime = startTime;
201 iEndTime = (time_t) m_pDS->fv("iEndTime").get_asInt();
202 CDateTime endTime(iEndTime);
203 newTag.m_endTime = endTime;
205 iFirstAired = (time_t) m_pDS->fv("iFirstAired").get_asInt();
206 CDateTime firstAired(iFirstAired);
207 newTag.m_firstAired = firstAired;
209 newTag.m_iUniqueBroadcastID = m_pDS->fv("iBroadcastUid").get_asInt();
210 newTag.m_iBroadcastId = m_pDS->fv("idBroadcast").get_asInt();
211 newTag.m_strTitle = m_pDS->fv("sTitle").get_asString().c_str();
212 newTag.m_strPlotOutline = m_pDS->fv("sPlotOutline").get_asString().c_str();
213 newTag.m_strPlot = m_pDS->fv("sPlot").get_asString().c_str();
214 newTag.m_iGenreType = m_pDS->fv("iGenreType").get_asInt();
215 newTag.m_iGenreSubType = m_pDS->fv("iGenreSubType").get_asInt();
216 newTag.m_genre = StringUtils::Split(m_pDS->fv("sGenre").get_asString().c_str(), g_advancedSettings.m_videoItemSeparator);
217 newTag.m_iParentalRating = m_pDS->fv("iParentalRating").get_asInt();
218 newTag.m_iStarRating = m_pDS->fv("iStarRating").get_asInt();
219 newTag.m_bNotify = m_pDS->fv("bNotify").get_asBool();
220 newTag.m_iEpisodeNumber = m_pDS->fv("iEpisodeId").get_asInt();
221 newTag.m_iEpisodePart = m_pDS->fv("iEpisodePart").get_asInt();
222 newTag.m_strEpisodeName = m_pDS->fv("sEpisodeName").get_asString().c_str();
223 newTag.m_iSeriesNumber = m_pDS->fv("iSeriesId").get_asInt();
225 epg.AddEntry(newTag);
234 CLog::Log(LOGERROR, "%s - couldn't load EPG data from the database", __FUNCTION__);
240 bool CEpgDatabase::GetLastEpgScanTime(int iEpgId, CDateTime *lastScan)
242 bool bReturn = false;
243 CStdString strWhereClause = PrepareSQL("idEpg = %u", iEpgId);
244 CStdString strValue = GetSingleValue("lastepgscan", "sLastScan", strWhereClause);
246 if (!strValue.empty())
248 lastScan->SetFromDBDateTime(strValue.c_str());
253 lastScan->SetValid(false);
259 bool CEpgDatabase::PersistLastEpgScanTime(int iEpgId /* = 0 */, bool bQueueWrite /* = false */)
261 CStdString strQuery = PrepareSQL("REPLACE INTO lastepgscan(idEpg, sLastScan) VALUES (%u, '%s');",
262 iEpgId, CDateTime::GetCurrentDateTime().GetAsUTCDateTime().GetAsDBDateTime().c_str());
264 return bQueueWrite ? QueueInsertQuery(strQuery) : ExecuteQuery(strQuery);
267 bool CEpgDatabase::Persist(const CEpgContainer &epg)
269 for (map<unsigned int, CEpg *>::const_iterator it = epg.m_epgs.begin(); it != epg.m_epgs.end(); it++)
271 CEpg *epg = it->second;
276 return CommitInsertQueries();
279 int CEpgDatabase::Persist(const CEpg &epg, bool bQueueWrite /* = false */)
285 strQuery = PrepareSQL("REPLACE INTO epg (idEpg, sName, sScraperName) "
286 "VALUES (%u, '%s', '%s');", epg.EpgID(), epg.Name().c_str(), epg.ScraperName().c_str());
288 strQuery = PrepareSQL("INSERT INTO epg (sName, sScraperName) "
289 "VALUES ('%s', '%s');", epg.Name().c_str(), epg.ScraperName().c_str());
293 if (QueueInsertQuery(strQuery))
294 iReturn = epg.EpgID() <= 0 ? 0 : epg.EpgID();
298 if (ExecuteQuery(strQuery))
299 iReturn = epg.EpgID() <= 0 ? (int) m_pDS->lastinsertid() : epg.EpgID();
305 int CEpgDatabase::Persist(const CEpgInfoTag &tag, bool bSingleUpdate /* = true */)
309 if (tag.EpgID() <= 0)
311 CLog::Log(LOGERROR, "%s - tag '%s' does not have a valid table", __FUNCTION__, tag.Title(true).c_str());
315 time_t iStartTime, iEndTime, iFirstAired;
316 tag.StartAsUTC().GetAsTime(iStartTime);
317 tag.EndAsUTC().GetAsTime(iEndTime);
318 tag.FirstAiredAsUTC().GetAsTime(iFirstAired);
320 int iBroadcastId = tag.BroadcastId();
323 /* Only store the genre string when needed */
324 CStdString strGenre = (tag.GenreType() == EPG_GENRE_USE_STRING) ? StringUtils::Join(tag.Genre(), g_advancedSettings.m_videoItemSeparator) : "";
326 if (iBroadcastId < 0)
328 strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
329 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
330 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
331 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid) "
332 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i);",
333 tag.EpgID(), iStartTime, iEndTime,
334 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
335 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
336 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
337 tag.UniqueBroadcastID());
341 strQuery = PrepareSQL("REPLACE INTO epgtags (idEpg, iStartTime, "
342 "iEndTime, sTitle, sPlotOutline, sPlot, iGenreType, iGenreSubType, sGenre, "
343 "iFirstAired, iParentalRating, iStarRating, bNotify, iSeriesId, "
344 "iEpisodeId, iEpisodePart, sEpisodeName, iBroadcastUid, idBroadcast) "
345 "VALUES (%u, %u, %u, '%s', '%s', '%s', %i, %i, '%s', %u, %i, %i, %i, %i, %i, %i, '%s', %i, %i);",
346 tag.EpgID(), iStartTime, iEndTime,
347 tag.Title(true).c_str(), tag.PlotOutline(true).c_str(), tag.Plot(true).c_str(), tag.GenreType(), tag.GenreSubType(), strGenre.c_str(),
348 iFirstAired, tag.ParentalRating(), tag.StarRating(), tag.Notify(),
349 tag.SeriesNum(), tag.EpisodeNum(), tag.EpisodePart(), tag.EpisodeName().c_str(),
350 tag.UniqueBroadcastID(), iBroadcastId);
355 if (ExecuteQuery(strQuery))
356 iReturn = (int) m_pDS->lastinsertid();
360 QueueInsertQuery(strQuery);
367 int CEpgDatabase::GetLastEPGId(void)
369 CStdString strQuery = PrepareSQL("SELECT MAX(idEpg) FROM epg");
370 CStdString strValue = GetSingleValue(strQuery);
371 if (!strValue.empty())
372 return atoi(strValue.c_str());