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 "PVRDatabase.h"
22 #include "dbwrappers/dataset.h"
23 #include "settings/AdvancedSettings.h"
24 #include "settings/VideoSettings.h"
25 #include "settings/Settings.h"
26 #include "utils/log.h"
27 #include "utils/StringUtils.h"
29 #include "PVRManager.h"
30 #include "channels/PVRChannelGroupsContainer.h"
31 #include "channels/PVRChannelGroupInternal.h"
32 #include "addons/PVRClient.h"
35 using namespace dbiplus;
37 using namespace ADDON;
39 #define PVRDB_DEBUGGING 0
41 bool CPVRDatabase::Open()
43 return CDatabase::Open(g_advancedSettings.m_databaseTV);
46 void CPVRDatabase::CreateTables()
48 CLog::Log(LOGINFO, "PVR - %s - creating tables", __FUNCTION__);
50 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'clients'", __FUNCTION__);
52 "CREATE TABLE clients ("
53 "idClient integer primary key, "
59 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channels'", __FUNCTION__);
61 "CREATE TABLE channels ("
62 "idChannel integer primary key, "
66 "bIsUserSetIcon bool, "
68 "sIconPath varchar(255), "
69 "sChannelName varchar(64), "
72 "sEPGScraper varchar(32), "
73 "iLastWatched integer,"
75 // TODO use mapping table
77 "iClientChannelNumber integer, "
78 "sInputFormat varchar(32), "
79 "sStreamURL varchar(255), "
80 "iEncryptionSystem integer, "
86 // TODO use a mapping table so multiple backends per channel can be implemented
87 // CLog::Log(LOGDEBUG, "PVR - %s - creating table 'map_channels_clients'", __FUNCTION__);
89 // "CREATE TABLE map_channels_clients ("
90 // "idChannel integer primary key, "
91 // "idClient integer, "
92 // "iClientChannelNumber integer,"
93 // "sInputFormat string,"
94 // "sStreamURL string,"
95 // "iEncryptionSystem integer"
98 // m_pDS->exec("CREATE UNIQUE INDEX idx_idChannel_idClient on map_channels_clients(idChannel, idClient);");
100 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channelgroups'", __FUNCTION__);
102 "CREATE TABLE channelgroups ("
103 "idGroup integer primary key,"
105 "iGroupType integer, "
110 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'map_channelgroups_channels'", __FUNCTION__);
112 "CREATE TABLE map_channelgroups_channels ("
113 "idChannel integer, "
115 "iChannelNumber integer"
119 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channelsettings'", __FUNCTION__);
121 "CREATE TABLE channelsettings ("
122 "idChannel integer primary key, "
123 "iInterlaceMethod integer, "
124 "iViewMode integer, "
125 "fCustomZoomAmount float, "
126 "fPixelRatio float, "
127 "iAudioStream integer, "
128 "iSubtitleStream integer,"
129 "fSubtitleDelay float, "
131 "fBrightness float, "
134 "fVolumeAmplification float, "
135 "fAudioDelay float, "
136 "bOutputToAllSpeakers bool, "
138 "iCropLeft integer, "
139 "iCropRight integer, "
141 "iCropBottom integer, "
143 "fNoiseReduction float, "
144 "fCustomVerticalShift float, "
145 "bCustomNonLinStretch bool, "
146 "bPostProcess bool, "
147 "iScalingMethod integer, "
148 "iDeinterlaceMode integer "
152 // disable all PVR add-on when started the first time
153 ADDON::VECADDONS addons;
154 if (!CAddonMgr::Get().GetAddons(ADDON_PVRDLL, addons, true))
155 CLog::Log(LOGERROR, "PVR - %s - failed to get add-ons from the add-on manager", __FUNCTION__);
158 for (IVECADDONS it = addons.begin(); it != addons.end(); it++)
159 CAddonMgr::Get().DisableAddon(it->get()->ID());
163 void CPVRDatabase::CreateAnalytics()
165 CLog::Log(LOGINFO, "%s - creating indices", __FUNCTION__);
166 m_pDS->exec("CREATE UNIQUE INDEX idx_channels_iClientId_iUniqueId on channels(iClientId, iUniqueId);");
167 m_pDS->exec("CREATE INDEX idx_channelgroups_bIsRadio on channelgroups(bIsRadio);");
168 m_pDS->exec("CREATE UNIQUE INDEX idx_idGroup_idChannel on map_channelgroups_channels(idGroup, idChannel);");
171 void CPVRDatabase::UpdateTables(int iVersion)
174 m_pDS->exec("ALTER TABLE channels ADD idEpg integer;");
177 m_pDS->exec("ALTER TABLE channelsettings ADD fCustomVerticalShift float;");
181 m_pDS->exec("ALTER TABLE channelsettings ADD bCustomNonLinStretch bool;");
182 m_pDS->exec("ALTER TABLE channelsettings ADD bPostProcess bool;");
183 m_pDS->exec("ALTER TABLE channelsettings ADD iScalingMethod integer;");
187 /* sqlite apparently can't delete columns from an existing table, so just leave the extra column alone */
191 m_pDS->exec("ALTER TABLE channelsettings ADD iDeinterlaceMode integer");
192 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 2 WHERE iInterlaceMethod NOT IN (0,1)"); // anything other than none: method auto => mode force
193 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 1 WHERE iInterlaceMethod = 1"); // method auto => mode auto
194 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 0, iInterlaceMethod = 1 WHERE iInterlaceMethod = 0"); // method none => mode off, method auto
198 // bit of a hack, but we need to keep the version/contents of the non-pvr databases the same to allow clean upgrades
199 ADDON::VECADDONS addons;
200 if (!CAddonMgr::Get().GetAddons(ADDON_PVRDLL, addons, true))
201 CLog::Log(LOGERROR, "PVR - %s - failed to get add-ons from the add-on manager", __FUNCTION__);
204 CAddonDatabase database;
206 for (IVECADDONS it = addons.begin(); it != addons.end(); it++)
208 if (!database.IsSystemPVRAddonEnabled(it->get()->ID()))
209 CAddonMgr::Get().DisableAddon(it->get()->ID());
215 m_pDS->exec("ALTER TABLE channels ADD bIsUserSetIcon bool");
218 m_pDS->exec("ALTER TABLE channelgroups ADD iGroupType integer");
221 m_pDS->exec("ALTER TABLE channels ADD bIsLocked bool");
224 int CPVRDatabase::GetLastChannelId(void)
228 CStdString strQuery = PrepareSQL("SELECT MAX(idChannel) as iMaxChannel FROM channels");
229 if (ResultQuery(strQuery))
234 iReturn = m_pDS->fv("iMaxChannel").get_asInt();
243 /********** Channel methods **********/
245 bool CPVRDatabase::DeleteChannels(void)
247 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channels from the database", __FUNCTION__);
248 return DeleteValues("channels");
251 bool CPVRDatabase::DeleteClientChannels(const CPVRClient &client)
253 /* invalid client Id */
254 if (client.GetID() <= 0)
256 CLog::Log(LOGERROR, "PVR - %s - invalid client id: %i", __FUNCTION__, client.GetID());
260 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channels from client '%i' from the database", __FUNCTION__, client.GetID());
263 filter.AppendWhere(PrepareSQL("iClientId = %u", client.GetID()));
265 return DeleteValues("channels", filter);
268 bool CPVRDatabase::Delete(const CPVRChannel &channel)
270 /* invalid channel */
271 if (channel.ChannelID() <= 0)
274 CLog::Log(LOGDEBUG, "PVR - %s - deleting channel '%s' from the database", __FUNCTION__, channel.ChannelName().c_str());
277 filter.AppendWhere(PrepareSQL("idChannel = %u", channel.ChannelID()));
279 return DeleteValues("channels", filter);
282 int CPVRDatabase::Get(CPVRChannelGroupInternal &results)
286 CStdString strQuery = PrepareSQL("SELECT channels.idChannel, channels.iUniqueId, channels.bIsRadio, channels.bIsHidden, channels.bIsUserSetIcon, "
287 "channels.sIconPath, channels.sChannelName, channels.bIsVirtual, channels.bEPGEnabled, channels.sEPGScraper, channels.iLastWatched, channels.iClientId, channels.bIsLocked, "
288 "channels.iClientChannelNumber, channels.sInputFormat, channels.sInputFormat, channels.sStreamURL, channels.iEncryptionSystem, map_channelgroups_channels.iChannelNumber, channels.idEpg "
289 "FROM map_channelgroups_channels "
290 "LEFT JOIN channels ON channels.idChannel = map_channelgroups_channels.idChannel "
291 "WHERE map_channelgroups_channels.idGroup = %u", results.IsRadio() ? XBMC_INTERNAL_GROUP_RADIO : XBMC_INTERNAL_GROUP_TV);
292 if (ResultQuery(strQuery))
296 bool bIgnoreEpgDB = CSettings::Get().GetBool("epg.ignoredbforclient");
297 while (!m_pDS->eof())
299 CPVRChannelPtr channel = CPVRChannelPtr(new CPVRChannel());
301 channel->m_iChannelId = m_pDS->fv("idChannel").get_asInt();
302 channel->m_iUniqueId = m_pDS->fv("iUniqueId").get_asInt();
303 channel->m_bIsRadio = m_pDS->fv("bIsRadio").get_asBool();
304 channel->m_bIsHidden = m_pDS->fv("bIsHidden").get_asBool();
305 channel->m_bIsUserSetIcon = m_pDS->fv("bIsUserSetIcon").get_asBool();
306 channel->m_bIsLocked = m_pDS->fv("bIsLocked").get_asBool();
307 channel->m_strIconPath = m_pDS->fv("sIconPath").get_asString();
308 channel->m_strChannelName = m_pDS->fv("sChannelName").get_asString();
309 channel->m_bIsVirtual = m_pDS->fv("bIsVirtual").get_asBool();
310 channel->m_bEPGEnabled = m_pDS->fv("bEPGEnabled").get_asBool();
311 channel->m_strEPGScraper = m_pDS->fv("sEPGScraper").get_asString();
312 channel->m_iLastWatched = (time_t) m_pDS->fv("iLastWatched").get_asInt();
313 channel->m_iClientId = m_pDS->fv("iClientId").get_asInt();
314 channel->m_iClientChannelNumber = m_pDS->fv("iClientChannelNumber").get_asInt();
315 channel->m_strInputFormat = m_pDS->fv("sInputFormat").get_asString();
316 channel->m_strStreamURL = m_pDS->fv("sStreamURL").get_asString();
317 channel->m_iClientEncryptionSystem = m_pDS->fv("iEncryptionSystem").get_asInt();
319 channel->m_iEpgId = -1;
321 channel->m_iEpgId = m_pDS->fv("idEpg").get_asInt();
322 channel->UpdateEncryptionName();
325 CLog::Log(LOGDEBUG, "PVR - %s - channel '%s' loaded from the database", __FUNCTION__, channel->m_strChannelName.c_str());
327 PVRChannelGroupMember newMember = { channel, (unsigned int)m_pDS->fv("iChannelNumber").get_asInt() };
328 results.m_members.push_back(newMember);
337 CLog::Log(LOGERROR, "PVR - %s - couldn't load channels from the database", __FUNCTION__);
342 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
349 bool CPVRDatabase::DeleteChannelSettings()
351 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channel settings from the database", __FUNCTION__);
352 return DeleteValues("channelsettings");
355 bool CPVRDatabase::DeleteChannelSettings(const CPVRChannel &channel)
359 /* invalid channel */
360 if (channel.ChannelID() <= 0)
362 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
367 filter.AppendWhere(PrepareSQL("idChannel = %u", channel.ChannelID()));
369 return DeleteValues("channelsettings", filter);
372 bool CPVRDatabase::GetChannelSettings(const CPVRChannel &channel, CVideoSettings &settings)
376 /* invalid channel */
377 if (channel.ChannelID() <= 0)
379 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
383 CStdString strQuery = PrepareSQL("SELECT * FROM channelsettings WHERE idChannel = %u;", channel.ChannelID());
385 if (ResultQuery(strQuery))
389 if (m_pDS->num_rows() > 0)
391 settings.m_AudioDelay = m_pDS->fv("fAudioDelay").get_asFloat();
392 settings.m_AudioStream = m_pDS->fv("iAudioStream").get_asInt();
393 settings.m_Brightness = m_pDS->fv("fBrightness").get_asFloat();
394 settings.m_Contrast = m_pDS->fv("fContrast").get_asFloat();
395 settings.m_CustomPixelRatio = m_pDS->fv("fPixelRatio").get_asFloat();
396 settings.m_CustomNonLinStretch = m_pDS->fv("bCustomNonLinStretch").get_asBool();
397 settings.m_NoiseReduction = m_pDS->fv("fNoiseReduction").get_asFloat();
398 settings.m_PostProcess = m_pDS->fv("bPostProcess").get_asBool();
399 settings.m_Sharpness = m_pDS->fv("fSharpness").get_asFloat();
400 settings.m_CustomZoomAmount = m_pDS->fv("fCustomZoomAmount").get_asFloat();
401 settings.m_CustomVerticalShift = m_pDS->fv("fCustomVerticalShift").get_asFloat();
402 settings.m_Gamma = m_pDS->fv("fGamma").get_asFloat();
403 settings.m_SubtitleDelay = m_pDS->fv("fSubtitleDelay").get_asFloat();
404 settings.m_SubtitleOn = m_pDS->fv("bSubtitles").get_asBool();
405 settings.m_SubtitleStream = m_pDS->fv("iSubtitleStream").get_asInt();
406 settings.m_ViewMode = m_pDS->fv("iViewMode").get_asInt();
407 settings.m_Crop = m_pDS->fv("bCrop").get_asBool();
408 settings.m_CropLeft = m_pDS->fv("iCropLeft").get_asInt();
409 settings.m_CropRight = m_pDS->fv("iCropRight").get_asInt();
410 settings.m_CropTop = m_pDS->fv("iCropTop").get_asInt();
411 settings.m_CropBottom = m_pDS->fv("iCropBottom").get_asInt();
412 settings.m_InterlaceMethod = (EINTERLACEMETHOD)m_pDS->fv("iInterlaceMethod").get_asInt();
413 settings.m_DeinterlaceMode = (EDEINTERLACEMODE)m_pDS->fv("iDeinterlaceMode").get_asInt();
414 settings.m_VolumeAmplification = m_pDS->fv("fVolumeAmplification").get_asFloat();
415 settings.m_OutputToAllSpeakers = m_pDS->fv("bOutputToAllSpeakers").get_asBool();
416 settings.m_ScalingMethod = (ESCALINGMETHOD)m_pDS->fv("iScalingMethod").get_asInt();
425 CLog::Log(LOGERROR, "PVR - %s - failed to get channel settings for channel '%s'", __FUNCTION__, channel.ChannelName().c_str());
430 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
436 bool CPVRDatabase::PersistChannelSettings(const CPVRChannel &channel, const CVideoSettings &settings)
438 /* invalid channel */
439 if (channel.ChannelID() <= 0)
441 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
445 CStdString strQuery = PrepareSQL(
446 "REPLACE INTO channelsettings "
447 "(idChannel, iInterlaceMethod, iViewMode, fCustomZoomAmount, fPixelRatio, iAudioStream, iSubtitleStream, fSubtitleDelay, "
448 "bSubtitles, fBrightness, fContrast, fGamma, fVolumeAmplification, fAudioDelay, bOutputToAllSpeakers, bCrop, iCropLeft, "
449 "iCropRight, iCropTop, iCropBottom, fSharpness, fNoiseReduction, fCustomVerticalShift, bCustomNonLinStretch, bPostProcess, iScalingMethod, iDeinterlaceMode) VALUES "
450 "(%i, %i, %i, %f, %f, %i, %i, %f, %i, %f, %f, %f, %f, %f, %i, %i, %i, %i, %i, %i, %f, %f, %f, %i, %i, %i, %i);",
451 channel.ChannelID(), settings.m_InterlaceMethod, settings.m_ViewMode, settings.m_CustomZoomAmount, settings.m_CustomPixelRatio,
452 settings.m_AudioStream, settings.m_SubtitleStream, settings.m_SubtitleDelay, settings.m_SubtitleOn ? 1 :0,
453 settings.m_Brightness, settings.m_Contrast, settings.m_Gamma, settings.m_VolumeAmplification, settings.m_AudioDelay,
454 settings.m_OutputToAllSpeakers ? 1 : 0, settings.m_Crop ? 1 : 0, settings.m_CropLeft, settings.m_CropRight, settings.m_CropTop,
455 settings.m_CropBottom, settings.m_Sharpness, settings.m_NoiseReduction, settings.m_CustomVerticalShift,
456 settings.m_CustomNonLinStretch ? 1 : 0, settings.m_PostProcess ? 1 : 0, settings.m_ScalingMethod, settings.m_DeinterlaceMode);
458 return ExecuteQuery(strQuery);
461 /********** Channel group methods **********/
463 bool CPVRDatabase::RemoveChannelsFromGroup(const CPVRChannelGroup &group)
466 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
468 return DeleteValues("map_channelgroups_channels", filter);
471 bool CPVRDatabase::GetCurrentGroupMembers(const CPVRChannelGroup &group, vector<int> &members)
474 /* invalid group id */
475 if (group.GroupID() <= 0)
477 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
481 CStdString strCurrentMembersQuery = PrepareSQL("SELECT idChannel FROM map_channelgroups_channels WHERE idGroup = %u", group.GroupID());
482 if (ResultQuery(strCurrentMembersQuery))
486 while (!m_pDS->eof())
488 members.push_back(m_pDS->fv("idChannel").get_asInt());
496 CLog::Log(LOGERROR, "PVR - %s - couldn't load channels from the database", __FUNCTION__);
501 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
507 bool CPVRDatabase::DeleteChannelsFromGroup(const CPVRChannelGroup &group)
509 /* invalid group id */
510 if (group.GroupID() <= 0)
512 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
517 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
519 return DeleteValues("map_channelgroups_channels", filter);
522 bool CPVRDatabase::DeleteChannelsFromGroup(const CPVRChannelGroup &group, const vector<int> &channelsToDelete)
525 unsigned int iDeletedChannels(0);
526 /* invalid group id */
527 if (group.GroupID() <= 0)
529 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
533 while (iDeletedChannels < channelsToDelete.size())
535 CStdString strChannelsToDelete;
537 for (unsigned int iChannelPtr = 0; iChannelPtr + iDeletedChannels < channelsToDelete.size() && iChannelPtr < 50; iChannelPtr++)
538 strChannelsToDelete += StringUtils::Format(", %d", channelsToDelete.at(iDeletedChannels + iChannelPtr));
540 if (!strChannelsToDelete.empty())
542 strChannelsToDelete.erase(0, 2);
545 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
546 filter.AppendWhere(PrepareSQL("idChannel IN (%s)", strChannelsToDelete.c_str()));
548 bDelete = DeleteValues("map_channelgroups_channels", filter) && bDelete;
551 iDeletedChannels += 50;
557 bool CPVRDatabase::RemoveStaleChannelsFromGroup(const CPVRChannelGroup &group)
560 /* invalid group id */
561 if (group.GroupID() <= 0)
563 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
567 if (!group.IsInternalGroup())
569 /* First remove channels that don't exist in the main channels table */
571 // XXX work around for frodo: fix this up so it uses one query for all db types
572 // mysql doesn't support subqueries when deleting and sqlite doesn't support joins when deleting
573 if (g_advancedSettings.m_databaseTV.type.Equals("mysql"))
575 CStdString strQuery = PrepareSQL("DELETE m FROM map_channelgroups_channels m LEFT JOIN channels c ON (c.idChannel = m.idChannel) WHERE c.idChannel IS NULL");
576 bDelete = ExecuteQuery(strQuery);
581 filter.AppendWhere("idChannel IN (SELECT m.idChannel FROM map_channelgroups_channels m LEFT JOIN channels on m.idChannel = channels.idChannel WHERE channels.idChannel IS NULL)");
583 bDelete = DeleteValues("map_channelgroups_channels", filter);
587 if (group.m_members.size() > 0)
589 vector<int> currentMembers;
590 if (GetCurrentGroupMembers(group, currentMembers))
592 vector<int> channelsToDelete;
593 for (unsigned int iChannelPtr = 0; iChannelPtr < currentMembers.size(); iChannelPtr++)
595 if (!group.IsGroupMember(currentMembers.at(iChannelPtr)))
596 channelsToDelete.push_back(currentMembers.at(iChannelPtr));
599 bDelete = DeleteChannelsFromGroup(group, channelsToDelete) && bDelete;
605 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
607 bDelete = DeleteValues("map_channelgroups_channels", filter) && bDelete;
613 bool CPVRDatabase::DeleteChannelGroups(void)
615 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channel groups from the database", __FUNCTION__);
617 return DeleteValues("channelgroups") &&
618 DeleteValues("map_channelgroups_channels");
621 bool CPVRDatabase::Delete(const CPVRChannelGroup &group)
623 /* invalid group id */
624 if (group.GroupID() <= 0)
626 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
631 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
632 filter.AppendWhere(PrepareSQL("bIsRadio = %u", group.IsRadio()));
634 return RemoveChannelsFromGroup(group) &&
635 DeleteValues("channelgroups", filter);
638 bool CPVRDatabase::Get(CPVRChannelGroups &results)
640 bool bReturn = false;
641 CStdString strQuery = PrepareSQL("SELECT * from channelgroups WHERE bIsRadio = %u", results.IsRadio());
643 if (ResultQuery(strQuery))
647 while (!m_pDS->eof())
649 CPVRChannelGroup data(m_pDS->fv("bIsRadio").get_asBool(), m_pDS->fv("idGroup").get_asInt(), m_pDS->fv("sName").get_asString());
650 data.SetGroupType(m_pDS->fv("iGroupType").get_asInt());
651 results.Update(data);
653 CLog::Log(LOGDEBUG, "PVR - %s - group '%s' loaded from the database", __FUNCTION__, data.GroupName().c_str());
661 CLog::Log(LOGERROR, "%s - couldn't load channels from the database", __FUNCTION__);
668 int CPVRDatabase::Get(CPVRChannelGroup &group)
672 /* invalid group id */
673 if (group.GroupID() < 0)
675 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
679 CStdString strQuery = PrepareSQL("SELECT idChannel, iChannelNumber FROM map_channelgroups_channels WHERE idGroup = %u ORDER BY iChannelNumber", group.GroupID());
680 if (ResultQuery(strQuery))
686 while (!m_pDS->eof())
688 int iChannelId = m_pDS->fv("idChannel").get_asInt();
689 int iChannelNumber = m_pDS->fv("iChannelNumber").get_asInt();
690 CPVRChannelPtr channel = g_PVRChannelGroups->GetGroupAll(group.IsRadio())->GetByChannelID(iChannelId);
695 CLog::Log(LOGDEBUG, "PVR - %s - channel '%s' loaded from the database", __FUNCTION__, channel->m_strChannelName.c_str());
697 PVRChannelGroupMember newMember = { channel, (unsigned int)iChannelNumber };
698 group.m_members.push_back(newMember);
703 // remove a channel that doesn't exist (anymore) from the table
705 filter.AppendWhere(PrepareSQL("idGroup = %u", group.GroupID()));
706 filter.AppendWhere(PrepareSQL("idChannel = %u", iChannelId));
708 DeleteValues("map_channelgroups_channels", filter);
717 CLog::Log(LOGERROR, "PVR - %s - failed to get channels", __FUNCTION__);
722 group.SortByChannelNumber();
727 bool CPVRDatabase::PersistChannels(CPVRChannelGroup &group)
732 /* we can only safely get this from a local db */
734 iLastChannel = GetLastChannelId();
736 for (unsigned int iChannelPtr = 0; iChannelPtr < group.m_members.size(); iChannelPtr++)
738 PVRChannelGroupMember member = group.m_members.at(iChannelPtr);
739 if (member.channel->IsChanged() || member.channel->IsNew())
741 if (m_sqlite && member.channel->IsNew())
742 member.channel->SetChannelID(++iLastChannel);
743 bReturn &= Persist(*member.channel, m_sqlite || !member.channel->IsNew());
747 bReturn &= CommitInsertQueries();
752 bool CPVRDatabase::PersistGroupMembers(CPVRChannelGroup &group)
755 bool bRemoveChannels = true;
757 CSingleLock lock(group.m_critSection);
759 if (group.m_members.size() > 0)
761 for (unsigned int iChannelPtr = 0; iChannelPtr < group.m_members.size(); iChannelPtr++)
763 PVRChannelGroupMember member = group.m_members.at(iChannelPtr);
765 CStdString strWhereClause = PrepareSQL("idChannel = %u AND idGroup = %u AND iChannelNumber = %u",
766 member.channel->ChannelID(), group.GroupID(), member.iChannelNumber);
768 CStdString strValue = GetSingleValue("map_channelgroups_channels", "idChannel", strWhereClause);
769 if (strValue.empty())
771 strQuery = PrepareSQL("REPLACE INTO map_channelgroups_channels ("
772 "idGroup, idChannel, iChannelNumber) "
773 "VALUES (%i, %i, %i);",
774 group.GroupID(), member.channel->ChannelID(), member.iChannelNumber);
775 QueueInsertQuery(strQuery);
780 bReturn = CommitInsertQueries();
781 bRemoveChannels = RemoveStaleChannelsFromGroup(group);
784 return bReturn && bRemoveChannels;
787 /********** Client methods **********/
789 bool CPVRDatabase::DeleteClients()
791 CLog::Log(LOGDEBUG, "PVR - %s - deleting all clients from the database", __FUNCTION__);
793 return DeleteValues("clients");
794 //TODO && DeleteValues("map_channels_clients");
797 bool CPVRDatabase::Delete(const CPVRClient &client)
799 /* invalid client uid */
800 if (client.ID().empty())
802 CLog::Log(LOGERROR, "PVR - %s - invalid client uid", __FUNCTION__);
807 filter.AppendWhere(PrepareSQL("sUid = '%s'", client.ID().c_str()));
809 return DeleteValues("clients", filter);
812 int CPVRDatabase::GetClientId(const CStdString &strClientUid)
814 CStdString strWhereClause = PrepareSQL("sUid = '%s'", strClientUid.c_str());
815 CStdString strValue = GetSingleValue("clients", "idClient", strWhereClause);
817 if (strValue.empty())
820 return atol(strValue.c_str());
823 bool CPVRDatabase::ResetEPG(void)
825 CStdString strQuery = PrepareSQL("UPDATE channels SET idEpg = 0");
826 return ExecuteQuery(strQuery);
829 bool CPVRDatabase::Persist(CPVRChannelGroup &group)
832 if (group.GroupName().empty())
834 CLog::Log(LOGERROR, "%s - empty group name", __FUNCTION__);
841 CSingleLock lock(group.m_critSection);
843 /* insert a new entry when this is a new group, or replace the existing one otherwise */
844 if (group.GroupID() <= 0)
845 strQuery = PrepareSQL("INSERT INTO channelgroups (bIsRadio, iGroupType, sName) VALUES (%i, %i, '%s')",
846 (group.IsRadio() ? 1 :0), group.GroupType(), group.GroupName().c_str());
848 strQuery = PrepareSQL("REPLACE INTO channelgroups (idGroup, bIsRadio, iGroupType, sName) VALUES (%i, %i, %i, '%s')",
849 group.GroupID(), (group.IsRadio() ? 1 :0), group.GroupType(), group.GroupName().c_str());
851 bReturn = ExecuteQuery(strQuery);
853 /* set the group id if it was <= 0 */
854 if (bReturn && group.GroupID() <= 0)
855 group.m_iGroupId = (int) m_pDS->lastinsertid();
858 /* only persist the channel data for the internal groups */
859 if (group.IsInternalGroup())
860 bReturn &= PersistChannels(group);
862 /* persist the group member entries */
864 bReturn = PersistGroupMembers(group);
869 int CPVRDatabase::Persist(const AddonPtr client)
873 /* invalid client uid or name */
874 if (client->Name().empty() || client->ID().empty())
876 CLog::Log(LOGERROR, "PVR - %s - invalid client uid or name", __FUNCTION__);
880 CStdString strQuery = PrepareSQL("REPLACE INTO clients (sName, sUid) VALUES ('%s', '%s');",
881 client->Name().c_str(), client->ID().c_str());
883 if (ExecuteQuery(strQuery))
884 iReturn = (int) m_pDS->lastinsertid();
889 bool CPVRDatabase::Persist(CPVRChannel &channel, bool bQueueWrite /* = false */)
893 /* invalid channel */
894 if (channel.UniqueID() <= 0)
896 CLog::Log(LOGERROR, "PVR - %s - invalid channel uid: %d", __FUNCTION__, channel.UniqueID());
901 if (channel.ChannelID() <= 0)
904 strQuery = PrepareSQL("INSERT INTO channels ("
905 "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsLocked, "
906 "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
907 "iClientChannelNumber, sInputFormat, sStreamURL, iEncryptionSystem, idEpg) "
908 "VALUES (%i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %i, '%s', '%s', %i, %i)",
909 channel.UniqueID(), (channel.IsRadio() ? 1 :0), (channel.IsHidden() ? 1 : 0), (channel.IsUserSetIcon() ? 1 : 0), (channel.IsLocked() ? 1 : 0),
910 channel.IconPath().c_str(), channel.ChannelName().c_str(), (channel.IsVirtual() ? 1 : 0), (channel.EPGEnabled() ? 1 : 0), channel.EPGScraper().c_str(), channel.LastWatched(), channel.ClientID(),
911 channel.ClientChannelNumber(), channel.InputFormat().c_str(), channel.StreamURL().c_str(), channel.EncryptionSystem(),
917 strQuery = PrepareSQL("REPLACE INTO channels ("
918 "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsLocked, "
919 "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
920 "iClientChannelNumber, sInputFormat, sStreamURL, iEncryptionSystem, idChannel, idEpg) "
921 "VALUES (%i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %i, '%s', '%s', %i, %i, %i)",
922 channel.UniqueID(), (channel.IsRadio() ? 1 :0), (channel.IsHidden() ? 1 : 0), (channel.IsUserSetIcon() ? 1 : 0), (channel.IsLocked() ? 1 : 0),
923 channel.IconPath().c_str(), channel.ChannelName().c_str(), (channel.IsVirtual() ? 1 : 0), (channel.EPGEnabled() ? 1 : 0), channel.EPGScraper().c_str(), channel.LastWatched(), channel.ClientID(),
924 channel.ClientChannelNumber(), channel.InputFormat().c_str(), channel.StreamURL().c_str(), channel.EncryptionSystem(), channel.ChannelID(),
930 QueueInsertQuery(strQuery);
933 else if (ExecuteQuery(strQuery))
935 CSingleLock lock(channel.m_critSection);
936 if (channel.m_iChannelId <= 0)
937 channel.m_iChannelId = (int)m_pDS->lastinsertid();