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 "utils/log.h"
26 #include "utils/StringUtils.h"
28 #include "PVRManager.h"
29 #include "channels/PVRChannelGroupsContainer.h"
30 #include "channels/PVRChannelGroupInternal.h"
31 #include "addons/PVRClient.h"
34 using namespace dbiplus;
36 using namespace ADDON;
38 #define PVRDB_DEBUGGING 0
40 bool CPVRDatabase::Open()
42 return CDatabase::Open(g_advancedSettings.m_databaseTV);
45 bool CPVRDatabase::CreateTables()
51 if (!CDatabase::CreateTables())
55 CLog::Log(LOGINFO, "PVR - %s - creating tables", __FUNCTION__);
57 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'clients'", __FUNCTION__);
59 "CREATE TABLE clients ("
60 "idClient integer primary key, "
66 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channels'", __FUNCTION__);
68 "CREATE TABLE channels ("
69 "idChannel integer primary key, "
73 "bIsUserSetIcon bool, "
75 "sIconPath varchar(255), "
76 "sChannelName varchar(64), "
79 "sEPGScraper varchar(32), "
80 "iLastWatched integer,"
82 // TODO use mapping table
84 "iClientChannelNumber integer, "
85 "sInputFormat varchar(32), "
86 "sStreamURL varchar(255), "
87 "iEncryptionSystem integer, "
92 m_pDS->exec("CREATE UNIQUE INDEX idx_channels_iClientId_iUniqueId on channels(iClientId, iUniqueId);");
94 // TODO use a mapping table so multiple backends per channel can be implemented
95 // CLog::Log(LOGDEBUG, "PVR - %s - creating table 'map_channels_clients'", __FUNCTION__);
97 // "CREATE TABLE map_channels_clients ("
98 // "idChannel integer primary key, "
99 // "idClient integer, "
100 // "iClientChannelNumber integer,"
101 // "sInputFormat string,"
102 // "sStreamURL string,"
103 // "iEncryptionSystem integer"
106 // m_pDS->exec("CREATE UNIQUE INDEX idx_idChannel_idClient on map_channels_clients(idChannel, idClient);");
108 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channelgroups'", __FUNCTION__);
110 "CREATE TABLE channelgroups ("
111 "idGroup integer primary key,"
113 "iGroupType integer, "
117 m_pDS->exec("CREATE INDEX idx_channelgroups_bIsRadio on channelgroups(bIsRadio);");
119 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'map_channelgroups_channels'", __FUNCTION__);
121 "CREATE TABLE map_channelgroups_channels ("
122 "idChannel integer, "
124 "iChannelNumber integer"
127 m_pDS->exec("CREATE UNIQUE INDEX idx_idGroup_idChannel on map_channelgroups_channels(idGroup, idChannel);");
129 CLog::Log(LOGDEBUG, "PVR - %s - creating table 'channelsettings'", __FUNCTION__);
131 "CREATE TABLE channelsettings ("
132 "idChannel integer primary key, "
133 "iInterlaceMethod integer, "
134 "iViewMode integer, "
135 "fCustomZoomAmount float, "
136 "fPixelRatio float, "
137 "iAudioStream integer, "
138 "iSubtitleStream integer,"
139 "fSubtitleDelay float, "
141 "fBrightness float, "
144 "fVolumeAmplification float, "
145 "fAudioDelay float, "
146 "bOutputToAllSpeakers bool, "
148 "iCropLeft integer, "
149 "iCropRight integer, "
151 "iCropBottom integer, "
153 "fNoiseReduction float, "
154 "fCustomVerticalShift float, "
155 "bCustomNonLinStretch bool, "
156 "bPostProcess bool, "
157 "iScalingMethod integer, "
158 "iDeinterlaceMode integer "
167 CLog::Log(LOGERROR, "PVR - %s - unable to create PVR database tables (error %i)", __FUNCTION__, (int)GetLastError());
168 RollbackTransaction();
174 // disable all PVR add-on when started the first time
175 ADDON::VECADDONS addons;
176 if ((bReturn = CAddonMgr::Get().GetAddons(ADDON_PVRDLL, addons, true)) == false)
177 CLog::Log(LOGERROR, "PVR - %s - failed to get add-ons from the add-on manager", __FUNCTION__);
180 for (IVECADDONS it = addons.begin(); it != addons.end(); it++)
181 CAddonMgr::Get().DisableAddon(it->get()->ID());
188 bool CPVRDatabase::UpdateOldVersion(int iVersion)
198 CLog::Log(LOGERROR, "PVR - %s - updating from table versions < 11 not supported. please delete '%s'",
199 __FUNCTION__, GetBaseDBName());
205 m_pDS->exec("DROP VIEW vw_last_watched;");
208 m_pDS->exec("ALTER TABLE channels ADD idEpg integer;");
211 m_pDS->exec("ALTER TABLE channelsettings ADD fCustomVerticalShift float;");
215 m_pDS->exec("ALTER TABLE channelsettings ADD bCustomNonLinStretch bool;");
216 m_pDS->exec("ALTER TABLE channelsettings ADD bPostProcess bool;");
217 m_pDS->exec("ALTER TABLE channelsettings ADD iScalingMethod integer;");
221 /* sqlite apparently can't delete columns from an existing table, so just leave the extra column alone */
225 m_pDS->exec("ALTER TABLE channelsettings ADD iDeinterlaceMode integer");
226 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 2 WHERE iInterlaceMethod NOT IN (0,1)"); // anything other than none: method auto => mode force
227 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 1 WHERE iInterlaceMethod = 1"); // method auto => mode auto
228 m_pDS->exec("UPDATE channelsettings SET iDeinterlaceMode = 0, iInterlaceMethod = 1 WHERE iInterlaceMethod = 0"); // method none => mode off, method auto
232 m_pDS->exec("DROP INDEX idx_channels_iClientId;");
233 m_pDS->exec("DROP INDEX idx_channels_iLastWatched;");
234 m_pDS->exec("DROP INDEX idx_channels_bIsRadio;");
235 m_pDS->exec("DROP INDEX idx_channels_bIsHidden;");
236 m_pDS->exec("DROP INDEX idx_idChannel_idGroup;");
237 m_pDS->exec("DROP INDEX idx_idGroup_iChannelNumber;");
238 m_pDS->exec("CREATE UNIQUE INDEX idx_channels_iClientId_iUniqueId on channels(iClientId, iUniqueId);");
239 m_pDS->exec("CREATE UNIQUE INDEX idx_idGroup_idChannel on map_channelgroups_channels(idGroup, idChannel);");
243 // bit of a hack, but we need to keep the version/contents of the non-pvr databases the same to allow clean upgrades
244 ADDON::VECADDONS addons;
245 if ((bReturn = CAddonMgr::Get().GetAddons(ADDON_PVRDLL, addons, true)) == false)
246 CLog::Log(LOGERROR, "PVR - %s - failed to get add-ons from the add-on manager", __FUNCTION__);
249 CAddonDatabase database;
251 for (IVECADDONS it = addons.begin(); it != addons.end(); it++)
253 if (!database.IsSystemPVRAddonEnabled(it->get()->ID()))
254 CAddonMgr::Get().DisableAddon(it->get()->ID());
260 m_pDS->exec("ALTER TABLE channels ADD bIsUserSetIcon bool");
263 m_pDS->exec("ALTER TABLE channelgroups ADD iGroupType integer");
266 m_pDS->exec("ALTER TABLE channels ADD bIsLocked bool");
271 CLog::Log(LOGERROR, "PVR - %s - error attempting to update the database version!", __FUNCTION__);
278 RollbackTransaction();
283 int CPVRDatabase::GetLastChannelId(void)
287 CStdString strQuery = FormatSQL("SELECT MAX(idChannel) as iMaxChannel FROM channels");
288 if (ResultQuery(strQuery))
293 iReturn = m_pDS->fv("iMaxChannel").get_asInt();
301 /********** Channel methods **********/
303 bool CPVRDatabase::DeleteChannels(void)
305 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channels from the database", __FUNCTION__);
306 return DeleteValues("channels");
309 bool CPVRDatabase::DeleteClientChannels(const CPVRClient &client)
311 /* invalid client Id */
312 if (client.GetID() <= 0)
314 CLog::Log(LOGERROR, "PVR - %s - invalid client id: %i", __FUNCTION__, client.GetID());
318 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channels from client '%i' from the database", __FUNCTION__, client.GetID());
319 CStdString strWhereClause = FormatSQL("iClientId = %u", client.GetID());
320 return DeleteValues("channels", strWhereClause);
323 bool CPVRDatabase::Delete(const CPVRChannel &channel)
325 /* invalid channel */
326 if (channel.ChannelID() <= 0)
329 CLog::Log(LOGDEBUG, "PVR - %s - deleting channel '%s' from the database", __FUNCTION__, channel.ChannelName().c_str());
330 CStdString strWhereClause = FormatSQL("idChannel = %u", channel.ChannelID());
331 return DeleteValues("channels", strWhereClause);
334 int CPVRDatabase::Get(CPVRChannelGroupInternal &results)
338 CStdString strQuery = FormatSQL("SELECT channels.idChannel, channels.iUniqueId, channels.bIsRadio, channels.bIsHidden, channels.bIsUserSetIcon, "
339 "channels.sIconPath, channels.sChannelName, channels.bIsVirtual, channels.bEPGEnabled, channels.sEPGScraper, channels.iLastWatched, channels.iClientId, channels.bIsLocked, "
340 "channels.iClientChannelNumber, channels.sInputFormat, channels.sInputFormat, channels.sStreamURL, channels.iEncryptionSystem, map_channelgroups_channels.iChannelNumber, channels.idEpg "
341 "FROM map_channelgroups_channels "
342 "LEFT JOIN channels ON channels.idChannel = map_channelgroups_channels.idChannel "
343 "WHERE map_channelgroups_channels.idGroup = %u", results.IsRadio() ? XBMC_INTERNAL_GROUP_RADIO : XBMC_INTERNAL_GROUP_TV);
344 if (ResultQuery(strQuery))
348 while (!m_pDS->eof())
350 CPVRChannelPtr channel = CPVRChannelPtr(new CPVRChannel());
352 channel->m_iChannelId = m_pDS->fv("idChannel").get_asInt();
353 channel->m_iUniqueId = m_pDS->fv("iUniqueId").get_asInt();
354 channel->m_bIsRadio = m_pDS->fv("bIsRadio").get_asBool();
355 channel->m_bIsHidden = m_pDS->fv("bIsHidden").get_asBool();
356 channel->m_bIsUserSetIcon = m_pDS->fv("bIsUserSetIcon").get_asBool();
357 channel->m_bIsLocked = m_pDS->fv("bIsLocked").get_asBool();
358 channel->m_strIconPath = m_pDS->fv("sIconPath").get_asString();
359 channel->m_strChannelName = m_pDS->fv("sChannelName").get_asString();
360 channel->m_bIsVirtual = m_pDS->fv("bIsVirtual").get_asBool();
361 channel->m_bEPGEnabled = m_pDS->fv("bEPGEnabled").get_asBool();
362 channel->m_strEPGScraper = m_pDS->fv("sEPGScraper").get_asString();
363 channel->m_iLastWatched = (time_t) m_pDS->fv("iLastWatched").get_asInt();
364 channel->m_iClientId = m_pDS->fv("iClientId").get_asInt();
365 channel->m_iClientChannelNumber = m_pDS->fv("iClientChannelNumber").get_asInt();
366 channel->m_strInputFormat = m_pDS->fv("sInputFormat").get_asString();
367 channel->m_strStreamURL = m_pDS->fv("sStreamURL").get_asString();
368 channel->m_iClientEncryptionSystem = m_pDS->fv("iEncryptionSystem").get_asInt();
369 channel->m_iEpgId = m_pDS->fv("idEpg").get_asInt();
370 channel->UpdateEncryptionName();
373 CLog::Log(LOGDEBUG, "PVR - %s - channel '%s' loaded from the database", __FUNCTION__, channel->m_strChannelName.c_str());
375 PVRChannelGroupMember newMember = { channel, (unsigned int)m_pDS->fv("iChannelNumber").get_asInt() };
376 results.m_members.push_back(newMember);
385 CLog::Log(LOGERROR, "PVR - %s - couldn't load channels from the database", __FUNCTION__);
390 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
397 bool CPVRDatabase::DeleteChannelSettings()
399 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channel settings from the database", __FUNCTION__);
400 return DeleteValues("channelsettings");
403 bool CPVRDatabase::DeleteChannelSettings(const CPVRChannel &channel)
407 /* invalid channel */
408 if (channel.ChannelID() <= 0)
410 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
414 CStdString strWhereClause = FormatSQL("idChannel = %u", channel.ChannelID());
415 return DeleteValues("channelsettings", strWhereClause);
418 bool CPVRDatabase::GetChannelSettings(const CPVRChannel &channel, CVideoSettings &settings)
422 /* invalid channel */
423 if (channel.ChannelID() <= 0)
425 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
429 CStdString strQuery = FormatSQL("SELECT * FROM channelsettings WHERE idChannel = %u;", channel.ChannelID());
431 if (ResultQuery(strQuery))
435 if (m_pDS->num_rows() > 0)
437 settings.m_AudioDelay = m_pDS->fv("fAudioDelay").get_asFloat();
438 settings.m_AudioStream = m_pDS->fv("iAudioStream").get_asInt();
439 settings.m_Brightness = m_pDS->fv("fBrightness").get_asFloat();
440 settings.m_Contrast = m_pDS->fv("fContrast").get_asFloat();
441 settings.m_CustomPixelRatio = m_pDS->fv("fPixelRatio").get_asFloat();
442 settings.m_CustomNonLinStretch = m_pDS->fv("bCustomNonLinStretch").get_asBool();
443 settings.m_NoiseReduction = m_pDS->fv("fNoiseReduction").get_asFloat();
444 settings.m_PostProcess = m_pDS->fv("bPostProcess").get_asBool();
445 settings.m_Sharpness = m_pDS->fv("fSharpness").get_asFloat();
446 settings.m_CustomZoomAmount = m_pDS->fv("fCustomZoomAmount").get_asFloat();
447 settings.m_CustomVerticalShift = m_pDS->fv("fCustomVerticalShift").get_asFloat();
448 settings.m_Gamma = m_pDS->fv("fGamma").get_asFloat();
449 settings.m_SubtitleDelay = m_pDS->fv("fSubtitleDelay").get_asFloat();
450 settings.m_SubtitleOn = m_pDS->fv("bSubtitles").get_asBool();
451 settings.m_SubtitleStream = m_pDS->fv("iSubtitleStream").get_asInt();
452 settings.m_ViewMode = m_pDS->fv("iViewMode").get_asInt();
453 settings.m_Crop = m_pDS->fv("bCrop").get_asBool();
454 settings.m_CropLeft = m_pDS->fv("iCropLeft").get_asInt();
455 settings.m_CropRight = m_pDS->fv("iCropRight").get_asInt();
456 settings.m_CropTop = m_pDS->fv("iCropTop").get_asInt();
457 settings.m_CropBottom = m_pDS->fv("iCropBottom").get_asInt();
458 settings.m_InterlaceMethod = (EINTERLACEMETHOD)m_pDS->fv("iInterlaceMethod").get_asInt();
459 settings.m_DeinterlaceMode = (EDEINTERLACEMODE)m_pDS->fv("iDeinterlaceMode").get_asInt();
460 settings.m_VolumeAmplification = m_pDS->fv("fVolumeAmplification").get_asFloat();
461 settings.m_OutputToAllSpeakers = m_pDS->fv("bOutputToAllSpeakers").get_asBool();
462 settings.m_ScalingMethod = (ESCALINGMETHOD)m_pDS->fv("iScalingMethod").get_asInt();
471 CLog::Log(LOGERROR, "PVR - %s - failed to get channel settings for channel '%s'", __FUNCTION__, channel.ChannelName().c_str());
476 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
482 bool CPVRDatabase::PersistChannelSettings(const CPVRChannel &channel, const CVideoSettings &settings)
484 /* invalid channel */
485 if (channel.ChannelID() <= 0)
487 CLog::Log(LOGERROR, "PVR - %s - invalid channel id: %i", __FUNCTION__, channel.ChannelID());
491 CStdString strQuery = FormatSQL(
492 "REPLACE INTO channelsettings "
493 "(idChannel, iInterlaceMethod, iViewMode, fCustomZoomAmount, fPixelRatio, iAudioStream, iSubtitleStream, fSubtitleDelay, "
494 "bSubtitles, fBrightness, fContrast, fGamma, fVolumeAmplification, fAudioDelay, bOutputToAllSpeakers, bCrop, iCropLeft, "
495 "iCropRight, iCropTop, iCropBottom, fSharpness, fNoiseReduction, fCustomVerticalShift, bCustomNonLinStretch, bPostProcess, iScalingMethod, iDeinterlaceMode) VALUES "
496 "(%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);",
497 channel.ChannelID(), settings.m_InterlaceMethod, settings.m_ViewMode, settings.m_CustomZoomAmount, settings.m_CustomPixelRatio,
498 settings.m_AudioStream, settings.m_SubtitleStream, settings.m_SubtitleDelay, settings.m_SubtitleOn ? 1 :0,
499 settings.m_Brightness, settings.m_Contrast, settings.m_Gamma, settings.m_VolumeAmplification, settings.m_AudioDelay,
500 settings.m_OutputToAllSpeakers ? 1 : 0, settings.m_Crop ? 1 : 0, settings.m_CropLeft, settings.m_CropRight, settings.m_CropTop,
501 settings.m_CropBottom, settings.m_Sharpness, settings.m_NoiseReduction, settings.m_CustomVerticalShift,
502 settings.m_CustomNonLinStretch ? 1 : 0, settings.m_PostProcess ? 1 : 0, settings.m_ScalingMethod, settings.m_DeinterlaceMode);
504 return ExecuteQuery(strQuery);
507 /********** Channel group methods **********/
509 bool CPVRDatabase::RemoveChannelsFromGroup(const CPVRChannelGroup &group)
511 CStdString strWhereClause = FormatSQL("idGroup = %u", group.GroupID());
512 return DeleteValues("map_channelgroups_channels", strWhereClause);
515 bool CPVRDatabase::GetCurrentGroupMembers(const CPVRChannelGroup &group, vector<int> &members)
518 /* invalid group id */
519 if (group.GroupID() <= 0)
521 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
525 CStdString strCurrentMembersQuery = FormatSQL("SELECT idChannel FROM map_channelgroups_channels WHERE idGroup = %u", group.GroupID());
526 if (ResultQuery(strCurrentMembersQuery))
530 while (!m_pDS->eof())
532 members.push_back(m_pDS->fv("idChannel").get_asInt());
540 CLog::Log(LOGERROR, "PVR - %s - couldn't load channels from the database", __FUNCTION__);
545 CLog::Log(LOGERROR, "PVR - %s - query failed", __FUNCTION__);
551 bool CPVRDatabase::DeleteChannelsFromGroup(const CPVRChannelGroup &group)
553 /* invalid group id */
554 if (group.GroupID() <= 0)
556 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
560 CStdString strWhereClause;
561 strWhereClause = FormatSQL("idGroup = %u", group.GroupID());
562 return DeleteValues("map_channelgroups_channels", strWhereClause);
565 bool CPVRDatabase::DeleteChannelsFromGroup(const CPVRChannelGroup &group, const vector<int> &channelsToDelete)
568 unsigned int iDeletedChannels(0);
569 /* invalid group id */
570 if (group.GroupID() <= 0)
572 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
576 while (iDeletedChannels < channelsToDelete.size())
578 CStdString strChannelsToDelete;
579 CStdString strWhereClause;
581 for (unsigned int iChannelPtr = 0; iChannelPtr + iDeletedChannels < channelsToDelete.size() && iChannelPtr < 50; iChannelPtr++)
582 strChannelsToDelete += StringUtils::Format(", %d", channelsToDelete.at(iDeletedChannels + iChannelPtr));
584 if (!strChannelsToDelete.empty())
586 strChannelsToDelete.erase(0, 2);
587 strWhereClause = FormatSQL("idGroup = %u AND idChannel IN (%s)", group.GroupID(), strChannelsToDelete.c_str());
588 bDelete = DeleteValues("map_channelgroups_channels", strWhereClause) && bDelete;
591 iDeletedChannels += 50;
597 bool CPVRDatabase::RemoveStaleChannelsFromGroup(const CPVRChannelGroup &group)
600 /* invalid group id */
601 if (group.GroupID() <= 0)
603 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
607 if (!group.IsInternalGroup())
609 /* First remove channels that don't exist in the main channels table */
611 // XXX work around for frodo: fix this up so it uses one query for all db types
612 // mysql doesn't support subqueries when deleting and sqlite doesn't support joins when deleting
613 if (g_advancedSettings.m_databaseTV.type.Equals("mysql"))
615 CStdString strQuery = FormatSQL("DELETE m FROM map_channelgroups_channels m LEFT JOIN channels c ON (c.idChannel = m.idChannel) WHERE c.idChannel IS NULL");
616 bDelete = ExecuteQuery(strQuery);
620 CStdString strWhereClause = FormatSQL("idChannel IN (SELECT m.idChannel FROM map_channelgroups_channels m LEFT JOIN channels on m.idChannel = channels.idChannel WHERE channels.idChannel IS NULL)");
621 bDelete = DeleteValues("map_channelgroups_channels", strWhereClause);
625 if (group.m_members.size() > 0)
627 vector<int> currentMembers;
628 if (GetCurrentGroupMembers(group, currentMembers))
630 vector<int> channelsToDelete;
631 for (unsigned int iChannelPtr = 0; iChannelPtr < currentMembers.size(); iChannelPtr++)
633 if (!group.IsGroupMember(currentMembers.at(iChannelPtr)))
634 channelsToDelete.push_back(currentMembers.at(iChannelPtr));
637 bDelete = DeleteChannelsFromGroup(group, channelsToDelete) && bDelete;
642 CStdString strWhereClause = FormatSQL("idGroup = %u", group.GroupID());
643 bDelete = DeleteValues("map_channelgroups_channels", strWhereClause) && bDelete;
649 bool CPVRDatabase::DeleteChannelGroups(void)
651 CLog::Log(LOGDEBUG, "PVR - %s - deleting all channel groups from the database", __FUNCTION__);
653 return DeleteValues("channelgroups") &&
654 DeleteValues("map_channelgroups_channels");
657 bool CPVRDatabase::Delete(const CPVRChannelGroup &group)
659 /* invalid group id */
660 if (group.GroupID() <= 0)
662 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
666 CStdString strWhereClause = FormatSQL("idGroup = %u AND bIsRadio = %u", group.GroupID(), group.IsRadio());
667 return RemoveChannelsFromGroup(group) &&
668 DeleteValues("channelgroups", strWhereClause);
671 bool CPVRDatabase::Get(CPVRChannelGroups &results)
673 bool bReturn = false;
674 CStdString strQuery = FormatSQL("SELECT * from channelgroups WHERE bIsRadio = %u", results.IsRadio());
676 if (ResultQuery(strQuery))
680 while (!m_pDS->eof())
682 CPVRChannelGroup data(m_pDS->fv("bIsRadio").get_asBool(), m_pDS->fv("idGroup").get_asInt(), m_pDS->fv("sName").get_asString());
683 data.SetGroupType(m_pDS->fv("iGroupType").get_asInt());
684 results.Update(data);
686 CLog::Log(LOGDEBUG, "PVR - %s - group '%s' loaded from the database", __FUNCTION__, data.GroupName().c_str());
694 CLog::Log(LOGERROR, "%s - couldn't load channels from the database", __FUNCTION__);
701 int CPVRDatabase::Get(CPVRChannelGroup &group)
705 /* invalid group id */
706 if (group.GroupID() < 0)
708 CLog::Log(LOGERROR, "PVR - %s - invalid group id: %d", __FUNCTION__, group.GroupID());
712 CStdString strQuery = FormatSQL("SELECT idChannel, iChannelNumber FROM map_channelgroups_channels WHERE idGroup = %u ORDER BY iChannelNumber", group.GroupID());
713 if (ResultQuery(strQuery))
719 while (!m_pDS->eof())
721 int iChannelId = m_pDS->fv("idChannel").get_asInt();
722 int iChannelNumber = m_pDS->fv("iChannelNumber").get_asInt();
723 CPVRChannelPtr channel = g_PVRChannelGroups->GetGroupAll(group.IsRadio())->GetByChannelID(iChannelId);
728 CLog::Log(LOGDEBUG, "PVR - %s - channel '%s' loaded from the database", __FUNCTION__, channel->m_strChannelName.c_str());
730 PVRChannelGroupMember newMember = { channel, (unsigned int)iChannelNumber };
731 group.m_members.push_back(newMember);
736 // remove a channel that doesn't exist (anymore) from the table
737 DeleteValues("map_channelgroups_channels", FormatSQL("idGroup = %u AND idChannel = %u", group.GroupID(), iChannelId));
746 CLog::Log(LOGERROR, "PVR - %s - failed to get channels", __FUNCTION__);
751 group.SortByChannelNumber();
756 bool CPVRDatabase::PersistChannels(CPVRChannelGroup &group)
761 /* we can only safely get this from a local db */
763 iLastChannel = GetLastChannelId();
765 for (unsigned int iChannelPtr = 0; iChannelPtr < group.m_members.size(); iChannelPtr++)
767 PVRChannelGroupMember member = group.m_members.at(iChannelPtr);
768 if (member.channel->IsChanged() || member.channel->IsNew())
770 if (m_sqlite && member.channel->IsNew())
771 member.channel->SetChannelID(++iLastChannel);
772 bReturn &= Persist(*member.channel, m_sqlite || !member.channel->IsNew());
776 bReturn &= CommitInsertQueries();
781 bool CPVRDatabase::PersistGroupMembers(CPVRChannelGroup &group)
784 bool bRemoveChannels = true;
786 CSingleLock lock(group.m_critSection);
788 if (group.m_members.size() > 0)
790 for (unsigned int iChannelPtr = 0; iChannelPtr < group.m_members.size(); iChannelPtr++)
792 PVRChannelGroupMember member = group.m_members.at(iChannelPtr);
794 CStdString strWhereClause = FormatSQL("idChannel = %u AND idGroup = %u AND iChannelNumber = %u",
795 member.channel->ChannelID(), group.GroupID(), member.iChannelNumber);
797 CStdString strValue = GetSingleValue("map_channelgroups_channels", "idChannel", strWhereClause);
798 if (strValue.empty())
800 strQuery = FormatSQL("REPLACE INTO map_channelgroups_channels ("
801 "idGroup, idChannel, iChannelNumber) "
802 "VALUES (%i, %i, %i);",
803 group.GroupID(), member.channel->ChannelID(), member.iChannelNumber);
804 QueueInsertQuery(strQuery);
809 bReturn = CommitInsertQueries();
810 bRemoveChannels = RemoveStaleChannelsFromGroup(group);
813 return bReturn && bRemoveChannels;
816 /********** Client methods **********/
818 bool CPVRDatabase::DeleteClients()
820 CLog::Log(LOGDEBUG, "PVR - %s - deleting all clients from the database", __FUNCTION__);
822 return DeleteValues("clients");
823 //TODO && DeleteValues("map_channels_clients");
826 bool CPVRDatabase::Delete(const CPVRClient &client)
828 /* invalid client uid */
829 if (client.ID().empty())
831 CLog::Log(LOGERROR, "PVR - %s - invalid client uid", __FUNCTION__);
835 CStdString strWhereClause = FormatSQL("sUid = '%s'", client.ID().c_str());
836 return DeleteValues("clients", strWhereClause);
839 int CPVRDatabase::GetClientId(const CStdString &strClientUid)
841 CStdString strWhereClause = FormatSQL("sUid = '%s'", strClientUid.c_str());
842 CStdString strValue = GetSingleValue("clients", "idClient", strWhereClause);
844 if (strValue.empty())
847 return atol(strValue.c_str());
850 bool CPVRDatabase::ResetEPG(void)
852 CStdString strQuery = FormatSQL("UPDATE channels SET idEpg = 0");
853 return ExecuteQuery(strQuery);
856 bool CPVRDatabase::Persist(CPVRChannelGroup &group)
859 if (group.GroupName().empty())
861 CLog::Log(LOGERROR, "%s - empty group name", __FUNCTION__);
868 CSingleLock lock(group.m_critSection);
870 /* insert a new entry when this is a new group, or replace the existing one otherwise */
871 if (group.GroupID() <= 0)
872 strQuery = FormatSQL("INSERT INTO channelgroups (bIsRadio, iGroupType, sName) VALUES (%i, %i, '%s')",
873 (group.IsRadio() ? 1 :0), group.GroupType(), group.GroupName().c_str());
875 strQuery = FormatSQL("REPLACE INTO channelgroups (idGroup, bIsRadio, iGroupType, sName) VALUES (%i, %i, %i, '%s')",
876 group.GroupID(), (group.IsRadio() ? 1 :0), group.GroupType(), group.GroupName().c_str());
878 bReturn = ExecuteQuery(strQuery);
880 /* set the group id if it was <= 0 */
881 if (bReturn && group.GroupID() <= 0)
882 group.m_iGroupId = (int) m_pDS->lastinsertid();
885 /* only persist the channel data for the internal groups */
886 if (group.IsInternalGroup())
887 bReturn &= PersistChannels(group);
889 /* persist the group member entries */
891 bReturn = PersistGroupMembers(group);
896 int CPVRDatabase::Persist(const AddonPtr client)
900 /* invalid client uid or name */
901 if (client->Name().empty() || client->ID().empty())
903 CLog::Log(LOGERROR, "PVR - %s - invalid client uid or name", __FUNCTION__);
907 CStdString strQuery = FormatSQL("REPLACE INTO clients (sName, sUid) VALUES ('%s', '%s');",
908 client->Name().c_str(), client->ID().c_str());
910 if (ExecuteQuery(strQuery))
911 iReturn = (int) m_pDS->lastinsertid();
916 bool CPVRDatabase::Persist(CPVRChannel &channel, bool bQueueWrite /* = false */)
920 /* invalid channel */
921 if (channel.UniqueID() <= 0)
923 CLog::Log(LOGERROR, "PVR - %s - invalid channel uid: %d", __FUNCTION__, channel.UniqueID());
928 if (channel.ChannelID() <= 0)
931 strQuery = FormatSQL("INSERT INTO channels ("
932 "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsLocked, "
933 "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
934 "iClientChannelNumber, sInputFormat, sStreamURL, iEncryptionSystem, idEpg) "
935 "VALUES (%i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %i, '%s', '%s', %i, %i)",
936 channel.UniqueID(), (channel.IsRadio() ? 1 :0), (channel.IsHidden() ? 1 : 0), (channel.IsUserSetIcon() ? 1 : 0), (channel.IsLocked() ? 1 : 0),
937 channel.IconPath().c_str(), channel.ChannelName().c_str(), (channel.IsVirtual() ? 1 : 0), (channel.EPGEnabled() ? 1 : 0), channel.EPGScraper().c_str(), channel.LastWatched(), channel.ClientID(),
938 channel.ClientChannelNumber(), channel.InputFormat().c_str(), channel.StreamURL().c_str(), channel.EncryptionSystem(),
944 strQuery = FormatSQL("REPLACE INTO channels ("
945 "iUniqueId, bIsRadio, bIsHidden, bIsUserSetIcon, bIsLocked, "
946 "sIconPath, sChannelName, bIsVirtual, bEPGEnabled, sEPGScraper, iLastWatched, iClientId, "
947 "iClientChannelNumber, sInputFormat, sStreamURL, iEncryptionSystem, idChannel, idEpg) "
948 "VALUES (%i, %i, %i, %i, %i, '%s', '%s', %i, %i, '%s', %u, %i, %i, '%s', '%s', %i, %i, %i)",
949 channel.UniqueID(), (channel.IsRadio() ? 1 :0), (channel.IsHidden() ? 1 : 0), (channel.IsUserSetIcon() ? 1 : 0), (channel.IsLocked() ? 1 : 0),
950 channel.IconPath().c_str(), channel.ChannelName().c_str(), (channel.IsVirtual() ? 1 : 0), (channel.EPGEnabled() ? 1 : 0), channel.EPGScraper().c_str(), channel.LastWatched(), channel.ClientID(),
951 channel.ClientChannelNumber(), channel.InputFormat().c_str(), channel.StreamURL().c_str(), channel.EncryptionSystem(), channel.ChannelID(),
957 QueueInsertQuery(strQuery);
960 else if (ExecuteQuery(strQuery))
962 CSingleLock lock(channel.m_critSection);
963 if (channel.m_iChannelId <= 0)
964 channel.m_iChannelId = (int)m_pDS->lastinsertid();