2 * Copyright (C) 2005-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 "network/Network.h"
22 #include "threads/SystemClock.h"
24 #include "MusicDatabase.h"
25 #include "network/cddb.h"
26 #include "filesystem/DirectoryCache.h"
27 #include "filesystem/MusicDatabaseDirectory/DirectoryNode.h"
28 #include "filesystem/MusicDatabaseDirectory/QueryParams.h"
29 #include "filesystem/MusicDatabaseDirectory.h"
30 #include "filesystem/SpecialProtocol.h"
31 #include "GUIInfoManager.h"
32 #include "music/tags/MusicInfoTag.h"
33 #include "addons/AddonManager.h"
34 #include "addons/Scraper.h"
35 #include "addons/Addon.h"
36 #include "utils/URIUtils.h"
40 #include "guilib/GUIWindowManager.h"
41 #include "dialogs/GUIDialogOK.h"
42 #include "dialogs/GUIDialogProgress.h"
43 #include "dialogs/GUIDialogYesNo.h"
44 #include "dialogs/GUIDialogSelect.h"
45 #include "filesystem/File.h"
46 #include "profiles/ProfilesManager.h"
47 #include "settings/AdvancedSettings.h"
49 #include "Application.h"
51 #include "karaoke/karaokelyricsfactory.h"
53 #include "storage/MediaManager.h"
54 #include "settings/MediaSettings.h"
55 #include "settings/Settings.h"
56 #include "utils/StringUtils.h"
57 #include "guilib/LocalizeStrings.h"
58 #include "utils/LegacyPathTranslation.h"
59 #include "utils/log.h"
60 #include "utils/TimeUtils.h"
61 #include "TextureCache.h"
62 #include "addons/AddonInstaller.h"
63 #include "utils/AutoPtrHandle.h"
64 #include "interfaces/AnnouncementManager.h"
65 #include "dbwrappers/dataset.h"
66 #include "utils/XMLUtils.h"
68 #include "playlists/SmartPlayList.h"
71 using namespace AUTOPTR;
72 using namespace XFILE;
73 using namespace MUSICDATABASEDIRECTORY;
74 using ADDON::AddonPtr;
76 #define RECENTLY_PLAYED_LIMIT 25
77 #define MIN_FULL_SEARCH_LENGTH 3
83 static void AnnounceRemove(const std::string& content, int id)
86 data["type"] = content;
88 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnRemove", data);
91 static void AnnounceUpdate(const std::string& content, int id)
94 data["type"] = content;
96 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnUpdate", data);
99 CMusicDatabase::CMusicDatabase(void)
103 CMusicDatabase::~CMusicDatabase(void)
108 bool CMusicDatabase::Open()
110 return CDatabase::Open(g_advancedSettings.m_databaseMusic);
113 void CMusicDatabase::CreateTables()
115 CLog::Log(LOGINFO, "create artist table");
116 m_pDS->exec("CREATE TABLE artist ( idArtist integer primary key, "
117 " strArtist varchar(256), strMusicBrainzArtistID text, "
118 " strBorn text, strFormed text, strGenres text, strMoods text, "
119 " strStyles text, strInstruments text, strBiography text, "
120 " strDied text, strDisbanded text, strYearsActive text, "
121 " strImage text, strFanart text, "
122 " lastScraped varchar(20) default NULL, "
123 " dateAdded varchar (20) default NULL)");
124 CLog::Log(LOGINFO, "create album table");
125 m_pDS->exec("CREATE TABLE album (idAlbum integer primary key, "
126 " strAlbum varchar(256), strMusicBrainzAlbumID text, "
127 " strArtists text, strGenres text, "
128 " iYear integer, idThumb integer, "
129 " bCompilation integer not null default '0', "
130 " strMoods text, strStyles text, strThemes text, "
131 " strReview text, strImage text, strLabel text, "
134 " lastScraped varchar(20) default NULL, "
135 " dateAdded varchar (20) default NULL)");
136 CLog::Log(LOGINFO, "create album_artist table");
137 m_pDS->exec("CREATE TABLE album_artist (idArtist integer, idAlbum integer, strJoinPhrase text, boolFeatured integer, iOrder integer, strArtist text)");
138 CLog::Log(LOGINFO, "create album_genre table");
139 m_pDS->exec("CREATE TABLE album_genre (idGenre integer, idAlbum integer, iOrder integer)");
141 CLog::Log(LOGINFO, "create genre table");
142 m_pDS->exec("CREATE TABLE genre (idGenre integer primary key, strGenre varchar(256))");
143 CLog::Log(LOGINFO, "create path table");
144 m_pDS->exec("CREATE TABLE path (idPath integer primary key, strPath varchar(512), strHash text)");
145 CLog::Log(LOGINFO, "create song table");
146 m_pDS->exec("CREATE TABLE song (idSong integer primary key, "
147 " idAlbum integer, idPath integer, "
148 " strArtists text, strGenres text, strTitle varchar(512), "
149 " iTrack integer, iDuration integer, iYear integer, "
150 " dwFileNameCRC text, "
151 " strFileName text, strMusicBrainzTrackID text, "
152 " iTimesPlayed integer, iStartOffset integer, iEndOffset integer, "
154 " lastplayed varchar(20) default NULL, "
155 " rating char default '0', comment text)");
156 CLog::Log(LOGINFO, "create song_artist table");
157 m_pDS->exec("CREATE TABLE song_artist (idArtist integer, idSong integer, strJoinPhrase text, boolFeatured integer, iOrder integer, strArtist text)");
158 CLog::Log(LOGINFO, "create song_genre table");
159 m_pDS->exec("CREATE TABLE song_genre (idGenre integer, idSong integer, iOrder integer)");
161 CLog::Log(LOGINFO, "create albuminfosong table");
162 m_pDS->exec("CREATE TABLE albuminfosong (idAlbumInfoSong integer primary key, idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer)");
164 CLog::Log(LOGINFO, "create content table");
165 m_pDS->exec("CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text)");
166 CLog::Log(LOGINFO, "create discography table");
167 m_pDS->exec("CREATE TABLE discography (idArtist integer, strAlbum text, strYear text)");
169 CLog::Log(LOGINFO, "create karaokedata table");
170 m_pDS->exec("CREATE TABLE karaokedata (iKaraNumber integer, idSong integer, iKaraDelay integer, strKaraEncoding text, "
171 "strKaralyrics text, strKaraLyrFileCRC text)");
173 CLog::Log(LOGINFO, "create art table");
174 m_pDS->exec("CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT)");
176 // Add 'Karaoke' genre
177 AddGenre( "Karaoke" );
180 void CMusicDatabase::CreateAnalytics()
182 CLog::Log(LOGINFO, "%s - creating indices", __FUNCTION__);
183 m_pDS->exec("CREATE INDEX idxAlbum ON album(strAlbum(255))");
184 m_pDS->exec("CREATE INDEX idxAlbum_1 ON album(bCompilation)");
185 m_pDS->exec("CREATE UNIQUE INDEX idxAlbum_2 ON album(strMusicBrainzAlbumID(36))");
187 m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_1 ON album_artist ( idAlbum, idArtist )");
188 m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_2 ON album_artist ( idArtist, idAlbum )");
189 m_pDS->exec("CREATE INDEX idxAlbumArtist_3 ON album_artist ( boolFeatured )");
191 m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_1 ON album_genre ( idAlbum, idGenre )");
192 m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_2 ON album_genre ( idGenre, idAlbum )");
194 m_pDS->exec("CREATE INDEX idxGenre ON genre(strGenre(255))");
196 m_pDS->exec("CREATE INDEX idxArtist ON artist(strArtist(255))");
197 m_pDS->exec("CREATE UNIQUE INDEX idxArtist1 ON artist(strMusicBrainzArtistID(36))");
199 m_pDS->exec("CREATE INDEX idxPath ON path(strPath(255))");
201 m_pDS->exec("CREATE INDEX idxSong ON song(strTitle(255))");
202 m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
203 m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
204 m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
205 m_pDS->exec("CREATE INDEX idxSong6 ON song( idPath, strFileName(255) )");
206 m_pDS->exec("CREATE UNIQUE INDEX idxSong7 ON song( idAlbum, strMusicBrainzTrackID(36) )");
208 m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_1 ON song_artist ( idSong, idArtist )");
209 m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_2 ON song_artist ( idArtist, idSong )");
210 m_pDS->exec("CREATE INDEX idxSongArtist_3 ON song_artist ( boolFeatured )");
212 m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_1 ON song_genre ( idSong, idGenre )");
213 m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_2 ON song_genre ( idGenre, idSong )");
215 m_pDS->exec("CREATE INDEX idxAlbumInfoSong_1 ON albuminfosong ( idAlbumInfo )");
217 m_pDS->exec("CREATE INDEX idxKaraNumber on karaokedata(iKaraNumber)");
218 m_pDS->exec("CREATE INDEX idxKarSong on karaokedata(idSong)");
220 m_pDS->exec("CREATE INDEX idxDiscography_1 ON discography ( idArtist )");
222 m_pDS->exec("CREATE INDEX ix_art ON art(media_id, media_type(20), type(20))");
224 CLog::Log(LOGINFO, "create triggers");
225 m_pDS->exec("CREATE TRIGGER tgrDeleteAlbum AFTER delete ON album FOR EACH ROW BEGIN"
226 " DELETE FROM song WHERE song.idAlbum = old.idAlbum;"
227 " DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum;"
228 " DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum;"
229 " DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum;"
230 " DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';"
232 m_pDS->exec("CREATE TRIGGER tgrDeleteArtist AFTER delete ON artist FOR EACH ROW BEGIN"
233 " DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist;"
234 " DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist;"
235 " DELETE FROM discography WHERE discography.idArtist = old.idArtist;"
236 " DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist';"
238 m_pDS->exec("CREATE TRIGGER tgrDeleteSong AFTER delete ON song FOR EACH ROW BEGIN"
239 " DELETE FROM song_artist WHERE song_artist.idSong = old.idSong;"
240 " DELETE FROM song_genre WHERE song_genre.idSong = old.idSong;"
241 " DELETE FROM karaokedata WHERE karaokedata.idSong = old.idSong;"
242 " DELETE FROM art WHERE media_id=old.idSong AND media_type='song';"
245 // we create views last to ensure all indexes are rolled in
249 void CMusicDatabase::CreateViews()
251 CLog::Log(LOGINFO, "create song view");
252 m_pDS->exec("CREATE VIEW songview AS SELECT "
253 " song.idSong AS idSong, "
254 " song.strArtists AS strArtists,"
255 " song.strGenres AS strGenres,"
257 " iTrack, iDuration, "
258 " song.iYear AS iYear, "
261 " strMusicBrainzTrackID, "
262 " iTimesPlayed, iStartOffset, iEndOffset, "
263 " lastplayed, rating, comment, "
264 " song.idAlbum AS idAlbum, "
267 " iKaraNumber, iKaraDelay, strKaraEncoding,"
268 " album.bCompilation AS bCompilation,"
269 " album.strArtists AS strAlbumArtists "
272 " song.idAlbum=album.idAlbum"
274 " song.idPath=path.idPath"
275 " LEFT OUTER JOIN karaokedata ON"
276 " song.idSong=karaokedata.idSong");
278 CLog::Log(LOGINFO, "create album view");
279 m_pDS->exec("CREATE VIEW albumview AS SELECT "
280 " album.idAlbum AS idAlbum, "
282 " strMusicBrainzAlbumID, "
283 " album.strArtists AS strArtists, "
284 " album.strGenres AS strGenres, "
285 " album.iYear AS iYear, "
286 " album.strMoods AS strMoods, "
287 " album.strStyles AS strStyles, "
292 " album.strImage as strImage, "
295 " MIN(song.iTimesPlayed) AS iTimesPlayed "
297 " LEFT OUTER JOIN song ON"
298 " album.idAlbum=song.idAlbum "
299 "GROUP BY album.idAlbum");
301 CLog::Log(LOGINFO, "create artist view");
302 m_pDS->exec("CREATE VIEW artistview AS SELECT"
303 " idArtist, strArtist, "
304 " strMusicBrainzArtistID, "
305 " strBorn, strFormed, strGenres,"
306 " strMoods, strStyles, strInstruments, "
307 " strBiography, strDied, strDisbanded, "
308 " strYearsActive, strImage, strFanart "
311 CLog::Log(LOGINFO, "create albumartist view");
312 m_pDS->exec("CREATE VIEW albumartistview AS SELECT"
313 " album_artist.idAlbum AS idAlbum, "
314 " album_artist.idArtist AS idArtist, "
315 " artist.strArtist AS strArtist, "
316 " artist.strMusicBrainzArtistID AS strMusicBrainzArtistID, "
317 " album_artist.boolFeatured AS boolFeatured, "
318 " album_artist.strJoinPhrase AS strJoinPhrase, "
319 " album_artist.iOrder AS iOrder "
322 " album_artist.idArtist = artist.idArtist");
324 CLog::Log(LOGINFO, "create songartist view");
325 m_pDS->exec("CREATE VIEW songartistview AS SELECT"
326 " song_artist.idSong AS idSong, "
327 " song_artist.idArtist AS idArtist, "
328 " artist.strArtist AS strArtist, "
329 " artist.strMusicBrainzArtistID AS strMusicBrainzArtistID, "
330 " song_artist.boolFeatured AS boolFeatured, "
331 " song_artist.strJoinPhrase AS strJoinPhrase, "
332 " song_artist.iOrder AS iOrder "
335 " song_artist.idArtist = artist.idArtist");
338 int CMusicDatabase::AddAlbumInfoSong(int idAlbum, const CSong& song)
340 CStdString strSQL = PrepareSQL("SELECT idAlbumInfoSong FROM albuminfosong WHERE idAlbumInfo = %i and iTrack = %i", idAlbum, song.iTrack);
341 int idAlbumInfoSong = (int)strtol(GetSingleValue(strSQL).c_str(), NULL, 10);
342 if (idAlbumInfoSong > 0)
344 strSQL = PrepareSQL("UPDATE albuminfosong SET strTitle = '%s', iDuration = %i WHERE idAlbumInfoSong = %i", song.strTitle.c_str(), song.iDuration, idAlbumInfoSong);
345 return ExecuteQuery(strSQL);
349 strSQL = PrepareSQL("INSERT INTO albuminfosong (idAlbumInfoSong,idAlbumInfo,iTrack,strTitle,iDuration) VALUES (NULL,%i,%i,'%s',%i)",
352 song.strTitle.c_str(),
354 return ExecuteQuery(strSQL);
358 std::string GetArtistString(const VECARTISTCREDITS &credits)
360 std::string artistString;
361 for (VECARTISTCREDITS::const_iterator i = credits.begin(); i != credits.end(); ++i)
362 artistString += i->GetArtist() + i->GetJoinPhrase();
366 bool CMusicDatabase::AddAlbum(CAlbum& album)
370 album.idAlbum = AddAlbum(album.strAlbum,
371 album.strMusicBrainzAlbumID,
372 GetArtistString(album.artistCredits),
373 album.GetGenreString(),
377 // Add the album artists
378 for (VECARTISTCREDITS::iterator artistCredit = album.artistCredits.begin(); artistCredit != album.artistCredits.end(); ++artistCredit)
380 artistCredit->idArtist = AddArtist(artistCredit->GetArtist(), artistCredit->GetMusicBrainzArtistID());
381 AddAlbumArtist(artistCredit->idArtist,
383 artistCredit->GetArtist(),
384 artistCredit->GetJoinPhrase(),
385 artistCredit == album.artistCredits.begin() ? false : true,
386 std::distance(album.artistCredits.begin(), artistCredit));
389 for (VECSONGS::iterator song = album.songs.begin(); song != album.songs.end(); ++song)
391 song->idAlbum = album.idAlbum;
392 song->idSong = AddSong(song->idAlbum,
393 song->strTitle, song->strMusicBrainzTrackID,
394 song->strFileName, song->strComment,
396 GetArtistString(song->artistCredits), song->genre,
397 song->iTrack, song->iDuration, song->iYear,
398 song->iTimesPlayed, song->iStartOffset,
402 song->iKaraokeNumber);
403 for (VECARTISTCREDITS::iterator artistCredit = song->artistCredits.begin(); artistCredit != song->artistCredits.end(); ++artistCredit)
405 artistCredit->idArtist = AddArtist(artistCredit->GetArtist(),
406 artistCredit->GetMusicBrainzArtistID());
407 AddSongArtist(artistCredit->idArtist,
409 artistCredit->GetArtist(),
410 artistCredit->GetJoinPhrase(), // we don't have song artist breakdowns from scrapers, yet
411 artistCredit == song->artistCredits.begin() ? false : true,
412 std::distance(song->artistCredits.begin(), artistCredit));
415 for (VECSONGS::const_iterator infoSong = album.infoSongs.begin(); infoSong != album.infoSongs.end(); ++infoSong)
416 AddAlbumInfoSong(album.idAlbum, *infoSong);
418 for (std::map<std::string, std::string>::const_iterator albumArt = album.art.begin();
419 albumArt != album.art.end();
421 SetArtForItem(album.idAlbum, "album", albumArt->first, albumArt->second);
427 bool CMusicDatabase::UpdateAlbum(CAlbum& album)
431 UpdateAlbum(album.idAlbum,
432 album.strAlbum, album.strMusicBrainzAlbumID,
433 GetArtistString(album.artistCredits), album.GetGenreString(),
434 StringUtils::Join(album.moods, g_advancedSettings.m_musicItemSeparator).c_str(),
435 StringUtils::Join(album.styles, g_advancedSettings.m_musicItemSeparator).c_str(),
436 StringUtils::Join(album.themes, g_advancedSettings.m_musicItemSeparator).c_str(),
438 album.thumbURL.m_xml.c_str(),
439 album.strLabel, album.strType,
440 album.iRating, album.iYear, album.bCompilation);
442 // Add the album artists
443 DeleteAlbumArtistsByAlbum(album.idAlbum);
444 for (VECARTISTCREDITS::iterator artistCredit = album.artistCredits.begin(); artistCredit != album.artistCredits.end(); ++artistCredit)
446 artistCredit->idArtist = AddArtist(artistCredit->GetArtist(),
447 artistCredit->GetMusicBrainzArtistID());
448 AddAlbumArtist(artistCredit->idArtist,
450 artistCredit->GetArtist(),
451 artistCredit->GetJoinPhrase(),
452 artistCredit == album.artistCredits.begin() ? false : true,
453 std::distance(album.artistCredits.begin(), artistCredit));
456 for (VECSONGS::iterator song = album.songs.begin(); song != album.songs.end(); ++song)
458 UpdateSong(song->idSong,
460 song->strMusicBrainzTrackID,
464 GetArtistString(song->artistCredits),
474 song->iKaraokeNumber);
475 DeleteSongArtistsBySong(song->idSong);
476 for (VECARTISTCREDITS::iterator artistCredit = song->artistCredits.begin(); artistCredit != song->artistCredits.end(); ++artistCredit)
478 artistCredit->idArtist = AddArtist(artistCredit->GetArtist(),
479 artistCredit->GetMusicBrainzArtistID());
480 AddSongArtist(artistCredit->idArtist,
482 artistCredit->GetArtist(),
483 artistCredit->GetJoinPhrase(),
484 artistCredit == song->artistCredits.begin() ? false : true,
485 std::distance(song->artistCredits.begin(), artistCredit));
488 for (VECSONGS::const_iterator infoSong = album.infoSongs.begin(); infoSong != album.infoSongs.end(); ++infoSong)
489 AddAlbumInfoSong(album.idAlbum, *infoSong);
491 if (!album.art.empty())
492 SetArtForItem(album.idAlbum, "album", album.art);
498 int CMusicDatabase::AddSong(const int idAlbum,
499 const CStdString& strTitle, const CStdString& strMusicBrainzTrackID,
500 const CStdString& strPathAndFileName, const CStdString& strComment, const CStdString& strThumb,
501 const std::string &artistString, const std::vector<std::string>& genres,
502 int iTrack, int iDuration, int iYear,
503 const int iTimesPlayed, int iStartOffset, int iEndOffset,
504 const CDateTime& dtLastPlayed, char rating, int iKaraokeNumber)
510 // We need at least the title
511 if (strTitle.empty())
514 if (NULL == m_pDB.get()) return -1;
515 if (NULL == m_pDS.get()) return -1;
517 CStdString strPath, strFileName;
518 URIUtils::Split(strPathAndFileName, strPath, strFileName);
519 int idPath = AddPath(strPath);
520 DWORD crc = ComputeCRC(strFileName);
522 bool bHasKaraoke = false;
524 bHasKaraoke = CKaraokeLyricsFactory::HasLyrics(strPathAndFileName);
527 if (!strMusicBrainzTrackID.empty())
528 strSQL = PrepareSQL("SELECT * FROM song WHERE idAlbum = %i AND strMusicBrainzTrackID = '%s'",
530 strMusicBrainzTrackID.c_str());
532 strSQL = PrepareSQL("SELECT * FROM song WHERE idAlbum=%i AND dwFileNameCRC='%ul' AND strTitle='%s' AND strMusicBrainzTrackID IS NULL",
537 if (!m_pDS->query(strSQL.c_str()))
540 if (m_pDS->num_rows() == 0)
543 strSQL=PrepareSQL("INSERT INTO song (idSong,idAlbum,idPath,strArtists,strGenres,strTitle,iTrack,iDuration,iYear,dwFileNameCRC,strFileName,strMusicBrainzTrackID,iTimesPlayed,iStartOffset,iEndOffset,lastplayed,rating,comment) values (NULL, %i, %i, '%s', '%s', '%s', %i, %i, %i, '%ul', '%s'",
546 artistString.c_str(),
547 StringUtils::Join(genres, g_advancedSettings.m_musicItemSeparator).c_str(),
549 iTrack, iDuration, iYear,
550 crc, strFileName.c_str());
552 if (strMusicBrainzTrackID.empty())
553 strSQL += PrepareSQL(",NULL");
555 strSQL += PrepareSQL(",'%s'", strMusicBrainzTrackID.c_str());
557 if (dtLastPlayed.IsValid())
558 strSQL += PrepareSQL(",%i,%i,%i,'%s','%c','%s')",
559 iTimesPlayed, iStartOffset, iEndOffset, dtLastPlayed.GetAsDBDateTime().c_str(), rating, strComment.c_str());
561 strSQL += PrepareSQL(",%i,%i,%i,NULL,'%c','%s')",
562 iTimesPlayed, iStartOffset, iEndOffset, rating, strComment.c_str());
563 m_pDS->exec(strSQL.c_str());
564 idSong = (int)m_pDS->lastinsertid();
568 idSong = m_pDS->fv("idSong").get_asInt();
570 UpdateSong(idSong, strTitle, strMusicBrainzTrackID, strPathAndFileName, strComment, strThumb, artistString, genres, iTrack, iDuration, iYear, iTimesPlayed, iStartOffset, iEndOffset, dtLastPlayed, rating, iKaraokeNumber);
573 if (!strThumb.empty())
574 SetArtForItem(idSong, "song", "thumb", strThumb);
576 unsigned int index = 0;
577 // If this is karaoke song, change the genre to 'Karaoke' (and add it if it's not there)
578 if ( bHasKaraoke && g_advancedSettings.m_karaokeChangeGenreForKaraokeSongs )
580 int idGenre = AddGenre("Karaoke");
581 AddSongGenre(idGenre, idSong, index);
582 AddAlbumGenre(idGenre, idAlbum, index++);
584 for (vector<string>::const_iterator i = genres.begin(); i != genres.end(); ++i)
586 // index will be wrong for albums, but ordering is not all that relevant
588 int idGenre = AddGenre(*i);
589 AddSongGenre(idGenre, idSong, index);
590 AddAlbumGenre(idGenre, idAlbum, index++);
593 // Add karaoke information (if any)
595 AddKaraokeData(idSong, iKaraokeNumber, crc);
597 AnnounceUpdate("song", idSong);
601 CLog::Log(LOGERROR, "musicdatabase:unable to addsong (%s)", strSQL.c_str());
606 bool CMusicDatabase::GetSong(int idSong, CSong& song)
612 if (NULL == m_pDB.get()) return false;
613 if (NULL == m_pDS.get()) return false;
615 CStdString strSQL=PrepareSQL("SELECT songview.*,songartistview.* FROM songview "
616 " LEFT JOIN songartistview ON songview.idSong = songartistview.idSong "
617 " WHERE songview.idSong = %i", idSong);
619 if (!m_pDS->query(strSQL.c_str())) return false;
620 int iRowsFound = m_pDS->num_rows();
627 int songArtistOffset = song_enumCount;
629 set<int> artistcredits;
630 song = GetSongFromDataset(m_pDS.get()->get_sql_record());
631 while (!m_pDS->eof())
633 const dbiplus::sql_record* const record = m_pDS.get()->get_sql_record();
635 int idSongArtist = record->at(songArtistOffset + artistCredit_idArtist).get_asInt();
636 if (artistcredits.find(idSongArtist) == artistcredits.end())
638 song.artistCredits.push_back(GetArtistCreditFromDataset(record, songArtistOffset));
639 artistcredits.insert(idSongArtist);
644 m_pDS->close(); // cleanup recordset data
649 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
655 int CMusicDatabase::UpdateSong(int idSong, const CSong &song)
657 return UpdateSong(idSong,
659 song.strMusicBrainzTrackID,
663 StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator), // NOTE: Don't call this function internally!!!
673 song.iKaraokeNumber);
676 int CMusicDatabase::UpdateSong(int idSong,
677 const CStdString& strTitle, const CStdString& strMusicBrainzTrackID,
678 const CStdString& strPathAndFileName, const CStdString& strComment, const CStdString& strThumb,
679 const std::string& artistString, const std::vector<std::string>& genres,
680 int iTrack, int iDuration, int iYear,
681 int iTimesPlayed, int iStartOffset, int iEndOffset,
682 const CDateTime& dtLastPlayed, char rating, int iKaraokeNumber)
688 CStdString strPath, strFileName;
689 URIUtils::Split(strPathAndFileName, strPath, strFileName);
690 int idPath = AddPath(strPath);
691 DWORD crc = ComputeCRC(strFileName);
693 strSQL = PrepareSQL("UPDATE song SET idPath = %i, strArtists = '%s', strGenres = '%s', strTitle = '%s', iTrack = %i, iDuration = %i, iYear = %i, dwFileNameCRC = '%ul', strFileName = '%s'",
695 artistString.c_str(),
696 StringUtils::Join(genres, g_advancedSettings.m_musicItemSeparator).c_str(),
698 iTrack, iDuration, iYear,
699 crc, strFileName.c_str());
700 if (strMusicBrainzTrackID.empty())
701 strSQL += PrepareSQL(", strMusicBrainzTrackID = NULL");
703 strSQL += PrepareSQL(", strMusicBrainzTrackID = '%s'", strMusicBrainzTrackID.c_str());
705 if (dtLastPlayed.IsValid())
706 strSQL += PrepareSQL(", iTimesPlayed = %i, iStartOffset = %i, iEndOffset = %i, lastplayed = '%s', rating = '%c', comment = '%s'",
707 iTimesPlayed, iStartOffset, iEndOffset, dtLastPlayed.GetAsDBDateTime().c_str(), rating, strComment.c_str());
709 strSQL += PrepareSQL(", iTimesPlayed = %i, iStartOffset = %i, iEndOffset = %i, lastplayed = NULL, rating = '%c', comment = '%s'",
710 iTimesPlayed, iStartOffset, iEndOffset, rating, strComment.c_str());
711 strSQL += PrepareSQL(" WHERE idSong = %i", idSong);
713 bool status = ExecuteQuery(strSQL);
715 AnnounceUpdate("song", idSong);
719 int CMusicDatabase::AddAlbum(const CStdString& strAlbum, const CStdString& strMusicBrainzAlbumID,
720 const CStdString& strArtist, const CStdString& strGenre, int year, bool bCompilation)
725 if (NULL == m_pDB.get()) return -1;
726 if (NULL == m_pDS.get()) return -1;
728 if (!strMusicBrainzAlbumID.empty())
729 strSQL = PrepareSQL("SELECT * FROM album WHERE strMusicBrainzAlbumID = '%s'",
730 strMusicBrainzAlbumID.c_str());
732 strSQL = PrepareSQL("SELECT * FROM album WHERE strArtists LIKE '%s' AND strAlbum LIKE '%s' AND strMusicBrainzAlbumID IS NULL",
735 m_pDS->query(strSQL.c_str());
737 if (m_pDS->num_rows() == 0)
740 // doesnt exists, add it
741 if (strMusicBrainzAlbumID.empty())
742 strSQL=PrepareSQL("insert into album (idAlbum, strAlbum, strMusicBrainzAlbumID, strArtists, strGenres, iYear, bCompilation) values( NULL, '%s', NULL, '%s', '%s', %i, %i)",
749 strSQL=PrepareSQL("insert into album (idAlbum, strAlbum, strMusicBrainzAlbumID, strArtists, strGenres, iYear, bCompilation) values( NULL, '%s', '%s', '%s', '%s', %i, %i)",
751 strMusicBrainzAlbumID.c_str(),
756 m_pDS->exec(strSQL.c_str());
758 return (int)m_pDS->lastinsertid();
762 /* Exists in our database and being re-scanned from tags, so we should update it as the details
765 Note that for multi-folder albums this will mean the last folder scanned will have the information
766 stored for it. Most values here should be the same across all songs anyway, but it does mean
767 that if there's any inconsistencies then only the last folders information will be taken.
769 We make sure we clear out the link tables (album artists, album genres) and we reset
770 the last scraped time to make sure that online metadata is re-fetched. */
771 int idAlbum = m_pDS->fv("idAlbum").get_asInt();
773 if (strMusicBrainzAlbumID.empty())
774 strSQL=PrepareSQL("UPDATE album SET strGenres = '%s', iYear=%i, bCompilation=%i, lastScraped = NULL WHERE idAlbum=%i",
780 strSQL=PrepareSQL("UPDATE album SET strAlbum = '%s', strArtists = '%s', strGenres = '%s', iYear=%i, bCompilation=%i, lastScraped = NULL WHERE idAlbum=%i",
787 m_pDS->exec(strSQL.c_str());
788 DeleteAlbumArtistsByAlbum(idAlbum);
789 DeleteAlbumGenresByAlbum(idAlbum);
795 CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
801 int CMusicDatabase::UpdateAlbum(int idAlbum,
802 const CStdString& strAlbum, const CStdString& strMusicBrainzAlbumID,
803 const CStdString& strArtist, const CStdString& strGenre,
804 const CStdString& strMoods, const CStdString& strStyles,
805 const CStdString& strThemes, const CStdString& strReview,
806 const CStdString& strImage, const CStdString& strLabel,
807 const CStdString& strType,
808 int iRating, int iYear, bool bCompilation)
814 strSQL = PrepareSQL("UPDATE album SET "
815 " strAlbum = '%s', strArtists = '%s', strGenres = '%s', "
816 " strMoods = '%s', strStyles = '%s', strThemes = '%s', "
817 " strReview = '%s', strImage = '%s', strLabel = '%s', "
818 " strType = '%s', iRating = %i,"
819 " iYear = %i, bCompilation = %i, lastScraped = '%s'",
820 strAlbum.c_str(), strArtist.c_str(), strGenre.c_str(),
821 strMoods.c_str(), strStyles.c_str(), strThemes.c_str(),
822 strReview.c_str(), strImage.c_str(), strLabel.c_str(),
823 strType.c_str(), iRating,
825 CDateTime::GetCurrentDateTime().GetAsDBDateTime().c_str());
826 if (strMusicBrainzAlbumID.empty())
827 strSQL += PrepareSQL(", strMusicBrainzAlbumID = NULL");
829 strSQL += PrepareSQL(", strMusicBrainzAlbumID = '%s'", strMusicBrainzAlbumID.c_str());
831 strSQL += PrepareSQL(" WHERE idAlbum = %i", idAlbum);
833 bool status = ExecuteQuery(strSQL);
835 AnnounceUpdate("album", idAlbum);
839 bool CMusicDatabase::GetAlbum(int idAlbum, CAlbum& album, bool getSongs /* = true */)
843 if (NULL == m_pDB.get()) return false;
844 if (NULL == m_pDS.get()) return false;
847 return false; // not in the database
852 sql = PrepareSQL("SELECT albumview.*,albumartistview.*,songview.*,songartistview.*,albuminfosong.* "
854 " LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum "
855 " JOIN songview ON albumview.idAlbum = songview.idAlbum "
856 " LEFT JOIN songartistview ON songview.idSong = songartistview.idSong "
857 " LEFT JOIN albuminfosong ON albumview.idAlbum = albuminfosong.idAlbumInfo "
858 " WHERE albumview.idAlbum = %ld "
859 " ORDER BY albumartistview.iOrder, songview.iTrack, songartistview.iOrder", idAlbum);
863 sql = PrepareSQL("SELECT albumview.*,albumartistview.* "
865 " LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum "
866 " WHERE albumview.idAlbum = %ld "
867 " ORDER BY albumartistview.iOrder", idAlbum);
870 CLog::Log(LOGDEBUG, "%s", sql.c_str());
871 if (!m_pDS->query(sql.c_str())) return false;
872 if (m_pDS->num_rows() == 0)
878 int albumArtistOffset = album_enumCount;
879 int songOffset = albumArtistOffset + artistCredit_enumCount;
880 int songArtistOffset = songOffset + song_enumCount;
881 int infoSongOffset = songArtistOffset + artistCredit_enumCount;
883 set<int> artistcredits;
885 set<pair<int, int> > songartistcredits;
887 album = GetAlbumFromDataset(m_pDS.get()->get_sql_record(), 0, true); // true to grab and parse the imageURL
888 while (!m_pDS->eof())
890 const dbiplus::sql_record* const record = m_pDS->get_sql_record();
892 // Because rows repeat in the joined query (cartesian join) we may see each
893 // entity (album artist, song, song artist) multiple times in the result set.
894 // Since there should never be a song with the same artist twice, or an album
895 // with the same song (by id) listed twice, we key on the entity ID and only
896 // create an entity for the first occurence of each entity in the data set.
897 int idAlbumArtist = record->at(albumArtistOffset + artistCredit_idArtist).get_asInt();
898 if (artistcredits.find(idAlbumArtist) == artistcredits.end())
900 album.artistCredits.push_back(GetArtistCreditFromDataset(record, albumArtistOffset));
901 artistcredits.insert(idAlbumArtist);
906 int idSong = record->at(songOffset + song_idSong).get_asInt();
907 if (songs.find(idSong) == songs.end())
909 album.songs.push_back(GetSongFromDataset(record, songOffset));
910 songs.insert(idSong);
913 int idSongArtistSong = record->at(songArtistOffset + artistCredit_idEntity).get_asInt();
914 int idSongArtistArtist = record->at(songArtistOffset + artistCredit_idArtist).get_asInt();
915 if (songartistcredits.find(make_pair(idSongArtistSong, idSongArtistArtist)) == songartistcredits.end())
917 for (VECSONGS::iterator si = album.songs.begin(); si != album.songs.end(); ++si)
918 if (si->idSong == idSongArtistSong)
919 si->artistCredits.push_back(GetArtistCreditFromDataset(record, songArtistOffset));
920 songartistcredits.insert(make_pair(idSongArtistSong, idSongArtistArtist));
923 int idAlbumInfoSong = m_pDS.get()->get_sql_record()->at(infoSongOffset + albumInfoSong_idAlbumInfoSong).get_asInt();
924 if (infosongs.find(idAlbumInfoSong) == infosongs.end())
926 album.infoSongs.push_back(GetAlbumInfoSongFromDataset(record, infoSongOffset));
927 infosongs.insert(idAlbumInfoSong);
932 m_pDS->close(); // cleanup recordset data
937 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
943 bool CMusicDatabase::ClearAlbumLastScrapedTime(int idAlbum)
945 CStdString strSQL = PrepareSQL("UPDATE album SET lastScraped = NULL WHERE idAlbum = %i", idAlbum);
946 return ExecuteQuery(strSQL);
949 bool CMusicDatabase::HasAlbumBeenScraped(int idAlbum)
951 CStdString strSQL = PrepareSQL("SELECT idAlbum FROM album WHERE idAlbum = %i AND lastScraped IS NULL", idAlbum);
952 return GetSingleValue(strSQL).empty();
955 int CMusicDatabase::AddGenre(const CStdString& strGenre1)
960 CStdString strGenre = strGenre1;
961 StringUtils::Trim(strGenre);
963 if (strGenre.empty())
964 strGenre=g_localizeStrings.Get(13205); // Unknown
966 if (NULL == m_pDB.get()) return -1;
967 if (NULL == m_pDS.get()) return -1;
968 map <CStdString, int>::const_iterator it;
970 it = m_genreCache.find(strGenre);
971 if (it != m_genreCache.end())
975 strSQL=PrepareSQL("select * from genre where strGenre like '%s'", strGenre.c_str());
976 m_pDS->query(strSQL.c_str());
977 if (m_pDS->num_rows() == 0)
980 // doesnt exists, add it
981 strSQL=PrepareSQL("insert into genre (idGenre, strGenre) values( NULL, '%s' )", strGenre.c_str());
982 m_pDS->exec(strSQL.c_str());
984 int idGenre = (int)m_pDS->lastinsertid();
985 m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
990 int idGenre = m_pDS->fv("idGenre").get_asInt();
991 m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
998 CLog::Log(LOGERROR, "musicdatabase:unable to addgenre (%s)", strSQL.c_str());
1004 bool CMusicDatabase::UpdateArtist(const CArtist& artist)
1006 UpdateArtist(artist.idArtist,
1007 artist.strArtist, artist.strMusicBrainzArtistID,
1008 artist.strBorn, artist.strFormed,
1009 StringUtils::Join(artist.genre, g_advancedSettings.m_musicItemSeparator),
1010 StringUtils::Join(artist.moods, g_advancedSettings.m_musicItemSeparator),
1011 StringUtils::Join(artist.styles, g_advancedSettings.m_musicItemSeparator),
1012 StringUtils::Join(artist.instruments, g_advancedSettings.m_musicItemSeparator),
1013 artist.strBiography, artist.strDied,
1014 artist.strDisbanded,
1015 StringUtils::Join(artist.yearsActive, g_advancedSettings.m_musicItemSeparator).c_str(),
1016 artist.thumbURL.m_xml.c_str(),
1017 artist.fanart.m_xml.c_str());
1019 DeleteArtistDiscography(artist.idArtist);
1020 for (std::vector<std::pair<CStdString,CStdString> >::const_iterator disc = artist.discography.begin();
1021 disc != artist.discography.end();
1024 AddArtistDiscography(artist.idArtist, disc->first, disc->second);
1030 int CMusicDatabase::AddArtist(const CStdString& strArtist, const CStdString& strMusicBrainzArtistID)
1035 if (NULL == m_pDB.get()) return -1;
1036 if (NULL == m_pDS.get()) return -1;
1039 if (!strMusicBrainzArtistID.empty())
1041 // 1.a) Match on a MusicBrainz ID
1042 strSQL = PrepareSQL("SELECT * FROM artist WHERE strMusicBrainzArtistID = '%s'",
1043 strMusicBrainzArtistID.c_str());
1044 m_pDS->query(strSQL.c_str());
1045 if (m_pDS->num_rows() > 0)
1047 int idArtist = (int)m_pDS->fv("idArtist").get_asInt();
1054 // 1.b) No match on MusicBrainz ID. Look for a previously added artist with no MusicBrainz ID
1055 // and update that if it exists.
1056 strSQL = PrepareSQL("SELECT * FROM artist WHERE strArtist LIKE '%s' AND strMusicBrainzArtistID IS NULL", strArtist.c_str());
1057 m_pDS->query(strSQL.c_str());
1058 if (m_pDS->num_rows() > 0)
1060 int idArtist = (int)m_pDS->fv("idArtist").get_asInt();
1062 // 1.b.a) We found an artist by name but with no MusicBrainz ID set, update it and assume it is our artist
1063 strSQL = PrepareSQL("UPDATE artist SET strArtist = '%s', strMusicBrainzArtistID = '%s' WHERE idArtist = %i",
1065 strMusicBrainzArtistID.c_str(),
1067 m_pDS->exec(strSQL.c_str());
1071 // 2) No MusicBrainz - search for any artist (MB ID or non) with the same name.
1072 // With MusicBrainz IDs this could return multiple artists and is non-determinstic
1073 // Always pick the first artist ID returned by the DB to return.
1077 strSQL = PrepareSQL("SELECT * FROM artist WHERE strArtist LIKE '%s'",
1080 m_pDS->query(strSQL.c_str());
1081 if (m_pDS->num_rows() > 0)
1083 int idArtist = (int)m_pDS->fv("idArtist").get_asInt();
1090 // 3) No artist exists at all - add it
1091 if (strMusicBrainzArtistID.empty())
1092 strSQL = PrepareSQL("INSERT INTO artist (idArtist, strArtist, strMusicBrainzArtistID) VALUES( NULL, '%s', NULL )",
1095 strSQL = PrepareSQL("INSERT INTO artist (idArtist, strArtist, strMusicBrainzArtistID) VALUES( NULL, '%s', '%s' )",
1097 strMusicBrainzArtistID.c_str());
1099 m_pDS->exec(strSQL.c_str());
1100 int idArtist = (int)m_pDS->lastinsertid();
1105 CLog::Log(LOGERROR, "musicdatabase:unable to addartist (%s)", strSQL.c_str());
1111 int CMusicDatabase::UpdateArtist(int idArtist,
1112 const CStdString& strArtist, const CStdString& strMusicBrainzArtistID,
1113 const CStdString& strBorn, const CStdString& strFormed,
1114 const CStdString& strGenres, const CStdString& strMoods,
1115 const CStdString& strStyles, const CStdString& strInstruments,
1116 const CStdString& strBiography, const CStdString& strDied,
1117 const CStdString& strDisbanded, const CStdString& strYearsActive,
1118 const CStdString& strImage, const CStdString& strFanart)
1120 CScraperUrl thumbURL;
1122 std::vector<std::pair<CStdString,CStdString> > discography;
1127 strSQL = PrepareSQL("UPDATE artist SET "
1128 " strArtist = '%s', "
1129 " strBorn = '%s', strFormed = '%s', strGenres = '%s', "
1130 " strMoods = '%s', strStyles = '%s', strInstruments = '%s', "
1131 " strBiography = '%s', strDied = '%s', strDisbanded = '%s', "
1132 " strYearsActive = '%s', strImage = '%s', strFanart = '%s', "
1133 " lastScraped = '%s'",
1134 strArtist.c_str(), /* strMusicBrainzArtistID.c_str(), */
1135 strBorn.c_str(), strFormed.c_str(), strGenres.c_str(),
1136 strMoods.c_str(), strStyles.c_str(), strInstruments.c_str(),
1137 strBiography.c_str(), strDied.c_str(), strDisbanded.c_str(),
1138 strYearsActive.c_str(), strImage.c_str(), strFanart.c_str(),
1139 CDateTime::GetCurrentDateTime().GetAsDBDateTime().c_str());
1140 if (strMusicBrainzArtistID.empty())
1141 strSQL += PrepareSQL(", strMusicBrainzArtistID = NULL");
1143 strSQL += PrepareSQL(", strMusicBrainzArtistID = '%s'", strMusicBrainzArtistID.c_str());
1145 strSQL += PrepareSQL(" WHERE idArtist = %i", idArtist);
1147 bool status = ExecuteQuery(strSQL);
1149 AnnounceUpdate("artist", idArtist);
1153 bool CMusicDatabase::GetArtist(int idArtist, CArtist &artist, bool fetchAll /* = false */)
1157 if (NULL == m_pDB.get()) return false;
1158 if (NULL == m_pDS.get()) return false;
1161 return false; // not in the database
1165 strSQL = PrepareSQL("SELECT * FROM artistview LEFT JOIN discography ON artistview.idArtist = discography.idArtist WHERE artistview.idArtist = %i", idArtist);
1167 strSQL = PrepareSQL("SELECT * FROM artistview WHERE artistview.idArtist = %i", idArtist);
1169 if (!m_pDS->query(strSQL.c_str())) return false;
1170 if (m_pDS->num_rows() == 0)
1176 int discographyOffset = artist_enumCount;
1178 artist.discography.clear();
1179 artist = GetArtistFromDataset(m_pDS.get()->get_sql_record(), 0, fetchAll);
1182 while (!m_pDS->eof())
1184 const dbiplus::sql_record* const record = m_pDS.get()->get_sql_record();
1186 artist.discography.push_back(make_pair(record->at(discographyOffset + 1).get_asString(), record->at(discographyOffset + 2).get_asString()));
1190 m_pDS->close(); // cleanup recordset data
1195 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idArtist);
1201 bool CMusicDatabase::HasArtistBeenScraped(int idArtist)
1203 CStdString strSQL = PrepareSQL("SELECT idArtist FROM artist WHERE idArtist = %i AND lastScraped IS NULL", idArtist);
1204 return GetSingleValue(strSQL).empty();
1207 bool CMusicDatabase::ClearArtistLastScrapedTime(int idArtist)
1209 CStdString strSQL = PrepareSQL("UPDATE artist SET lastScraped = NULL WHERE idArtist = %i", idArtist);
1210 return ExecuteQuery(strSQL);
1213 int CMusicDatabase::AddArtistDiscography(int idArtist, const CStdString& strAlbum, const CStdString& strYear)
1215 CStdString strSQL=PrepareSQL("INSERT INTO discography (idArtist, strAlbum, strYear) values(%i, '%s', '%s')",
1219 return ExecuteQuery(strSQL);
1222 bool CMusicDatabase::DeleteArtistDiscography(int idArtist)
1224 CStdString strSQL = PrepareSQL("DELETE FROM discography WHERE idArtist = %i", idArtist);
1225 return ExecuteQuery(strSQL);
1228 bool CMusicDatabase::AddSongArtist(int idArtist, int idSong, std::string strArtist, std::string joinPhrase, bool featured, int iOrder)
1231 strSQL=PrepareSQL("replace into song_artist (idArtist, idSong, strArtist, strJoinPhrase, boolFeatured, iOrder) values(%i,%i,'%s','%s',%i,%i)",
1232 idArtist, idSong, strArtist.c_str(), joinPhrase.c_str(), featured == true ? 1 : 0, iOrder);
1233 return ExecuteQuery(strSQL);
1236 bool CMusicDatabase::DeleteSongArtistsBySong(int idSong)
1238 return ExecuteQuery(PrepareSQL("DELETE FROM song_artist WHERE idSong = %i", idSong));
1241 bool CMusicDatabase::AddAlbumArtist(int idArtist, int idAlbum, std::string strArtist, std::string joinPhrase, bool featured, int iOrder)
1244 strSQL=PrepareSQL("replace into album_artist (idArtist, idAlbum, strArtist, strJoinPhrase, boolFeatured, iOrder) values(%i,%i,'%s','%s',%i,%i)",
1245 idArtist, idAlbum, strArtist.c_str(), joinPhrase.c_str(), featured == true ? 1 : 0, iOrder);
1246 return ExecuteQuery(strSQL);
1249 bool CMusicDatabase::DeleteAlbumArtistsByAlbum(int idAlbum)
1251 return ExecuteQuery(PrepareSQL("DELETE FROM album_artist WHERE idAlbum = %i", idAlbum));
1254 bool CMusicDatabase::AddSongGenre(int idGenre, int idSong, int iOrder)
1256 if (idGenre == -1 || idSong == -1)
1260 strSQL=PrepareSQL("replace into song_genre (idGenre, idSong, iOrder) values(%i,%i,%i)",
1261 idGenre, idSong, iOrder);
1262 return ExecuteQuery(strSQL);
1265 bool CMusicDatabase::DeleteSongGenresBySong(int idSong)
1267 return ExecuteQuery(PrepareSQL("DELETE FROM song_genre WHERE idSong = %i", idSong));
1270 bool CMusicDatabase::AddAlbumGenre(int idGenre, int idAlbum, int iOrder)
1272 if (idGenre == -1 || idAlbum == -1)
1276 strSQL=PrepareSQL("replace into album_genre (idGenre, idAlbum, iOrder) values(%i,%i,%i)",
1277 idGenre, idAlbum, iOrder);
1278 return ExecuteQuery(strSQL);
1281 bool CMusicDatabase::DeleteAlbumGenresByAlbum(int idAlbum)
1283 return ExecuteQuery(PrepareSQL("DELETE FROM album_genre WHERE idAlbum = %i", idAlbum));
1286 bool CMusicDatabase::GetAlbumsByArtist(int idArtist, bool includeFeatured, std::vector<int> &albums)
1290 CStdString strSQL, strPrepSQL;
1292 strPrepSQL = "select idAlbum from album_artist where idArtist=%i";
1293 if (includeFeatured == false)
1294 strPrepSQL += " AND boolFeatured = 0";
1296 strSQL=PrepareSQL(strPrepSQL, idArtist);
1297 if (!m_pDS->query(strSQL.c_str()))
1299 if (m_pDS->num_rows() == 0)
1305 while (!m_pDS->eof())
1307 albums.push_back(m_pDS->fv("idAlbum").get_asInt());
1315 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idArtist);
1320 bool CMusicDatabase::GetArtistsByAlbum(int idAlbum, bool includeFeatured, std::vector<int> &artists)
1324 CStdString strSQL, strPrepSQL;
1326 strPrepSQL = "select idArtist from album_artist where idAlbum=%i";
1327 if (includeFeatured == false)
1328 strPrepSQL += " AND boolFeatured = 0";
1330 strSQL=PrepareSQL(strPrepSQL, idAlbum);
1331 if (!m_pDS->query(strSQL.c_str()))
1333 if (m_pDS->num_rows() == 0)
1339 while (!m_pDS->eof())
1341 artists.push_back(m_pDS->fv("idArtist").get_asInt());
1349 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
1354 bool CMusicDatabase::GetSongsByArtist(int idArtist, bool includeFeatured, std::vector<int> &songs)
1358 CStdString strSQL, strPrepSQL;
1360 strPrepSQL = "select idSong from song_artist where idArtist=%i";
1361 if (includeFeatured == false)
1362 strPrepSQL += " AND boolFeatured = 0";
1364 strSQL=PrepareSQL(strPrepSQL, idArtist);
1365 if (!m_pDS->query(strSQL.c_str()))
1367 if (m_pDS->num_rows() == 0)
1373 while (!m_pDS->eof())
1375 songs.push_back(m_pDS->fv("idSong").get_asInt());
1383 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idArtist);
1388 bool CMusicDatabase::GetArtistsBySong(int idSong, bool includeFeatured, std::vector<int> &artists)
1392 CStdString strSQL, strPrepSQL;
1394 strPrepSQL = "select idArtist from song_artist where idSong=%i";
1395 if (includeFeatured == false)
1396 strPrepSQL += " AND boolFeatured = 0";
1398 strSQL=PrepareSQL(strPrepSQL, idSong);
1399 if (!m_pDS->query(strSQL.c_str()))
1401 if (m_pDS->num_rows() == 0)
1407 while (!m_pDS->eof())
1409 artists.push_back(m_pDS->fv("idArtist").get_asInt());
1417 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
1422 bool CMusicDatabase::GetGenresByAlbum(int idAlbum, std::vector<int>& genres)
1426 CStdString strSQL = PrepareSQL("select idGenre from album_genre where idAlbum = %i ORDER BY iOrder ASC", idAlbum);
1427 if (!m_pDS->query(strSQL.c_str()))
1429 if (m_pDS->num_rows() == 0)
1435 while (!m_pDS->eof())
1437 genres.push_back(m_pDS->fv("idGenre").get_asInt());
1446 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
1451 bool CMusicDatabase::GetGenresBySong(int idSong, std::vector<int>& genres)
1455 CStdString strSQL = PrepareSQL("select idGenre from song_genre where idSong = %i ORDER BY iOrder ASC", idSong);
1456 if (!m_pDS->query(strSQL.c_str()))
1458 if (m_pDS->num_rows() == 0)
1464 while (!m_pDS->eof())
1466 genres.push_back(m_pDS->fv("idGenre").get_asInt());
1475 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
1480 int CMusicDatabase::AddPath(const CStdString& strPath1)
1485 CStdString strPath(strPath1);
1486 if (!URIUtils::HasSlashAtEnd(strPath))
1487 URIUtils::AddSlashAtEnd(strPath);
1489 if (NULL == m_pDB.get()) return -1;
1490 if (NULL == m_pDS.get()) return -1;
1492 map <CStdString, int>::const_iterator it;
1494 it = m_pathCache.find(strPath);
1495 if (it != m_pathCache.end())
1498 strSQL=PrepareSQL( "select * from path where strPath='%s'", strPath.c_str());
1499 m_pDS->query(strSQL.c_str());
1500 if (m_pDS->num_rows() == 0)
1503 // doesnt exists, add it
1504 strSQL=PrepareSQL("insert into path (idPath, strPath) values( NULL, '%s' )", strPath.c_str());
1505 m_pDS->exec(strSQL.c_str());
1507 int idPath = (int)m_pDS->lastinsertid();
1508 m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
1513 int idPath = m_pDS->fv("idPath").get_asInt();
1514 m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
1521 CLog::Log(LOGERROR, "musicdatabase:unable to addpath (%s)", strSQL.c_str());
1527 CSong CMusicDatabase::GetSongFromDataset()
1529 return GetSongFromDataset(m_pDS->get_sql_record());
1532 CSong CMusicDatabase::GetSongFromDataset(const dbiplus::sql_record* const record, int offset /* = 0 */)
1535 song.idSong = record->at(offset + song_idSong).get_asInt();
1536 // get the full artist string
1537 song.artist = StringUtils::Split(record->at(offset + song_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
1538 // and the full genre string
1539 song.genre = StringUtils::Split(record->at(offset + song_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
1541 song.strAlbum = record->at(offset + song_strAlbum).get_asString();
1542 song.idAlbum = record->at(offset + song_idAlbum).get_asInt();
1543 song.iTrack = record->at(offset + song_iTrack).get_asInt() ;
1544 song.iDuration = record->at(offset + song_iDuration).get_asInt() ;
1545 song.iYear = record->at(offset + song_iYear).get_asInt() ;
1546 song.strTitle = record->at(offset + song_strTitle).get_asString();
1547 song.iTimesPlayed = record->at(offset + song_iTimesPlayed).get_asInt();
1548 song.lastPlayed.SetFromDBDateTime(record->at(offset + song_lastplayed).get_asString());
1549 song.iStartOffset = record->at(offset + song_iStartOffset).get_asInt();
1550 song.iEndOffset = record->at(offset + song_iEndOffset).get_asInt();
1551 song.strMusicBrainzTrackID = record->at(offset + song_strMusicBrainzTrackID).get_asString();
1552 song.rating = record->at(offset + song_rating).get_asChar();
1553 song.strComment = record->at(offset + song_comment).get_asString();
1554 song.iKaraokeNumber = record->at(offset + song_iKarNumber).get_asInt();
1555 song.strKaraokeLyrEncoding = record->at(offset + song_strKarEncoding).get_asString();
1556 song.iKaraokeDelay = record->at(offset + song_iKarDelay).get_asInt();
1557 song.bCompilation = record->at(offset + song_bCompilation).get_asInt() == 1;
1558 song.albumArtist = StringUtils::Split(record->at(offset + song_strAlbumArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
1560 // Get filename with full path
1561 song.strFileName = URIUtils::AddFileToFolder(record->at(offset + song_strPath).get_asString(), record->at(offset + song_strFileName).get_asString());
1565 void CMusicDatabase::GetFileItemFromDataset(CFileItem* item, const CMusicDbUrl &baseUrl)
1567 GetFileItemFromDataset(m_pDS->get_sql_record(), item, baseUrl);
1570 void CMusicDatabase::GetFileItemFromDataset(const dbiplus::sql_record* const record, CFileItem* item, const CMusicDbUrl &baseUrl)
1572 // get the full artist string
1573 item->GetMusicInfoTag()->SetArtist(StringUtils::Split(record->at(song_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator));
1574 // and the full genre string
1575 item->GetMusicInfoTag()->SetGenre(record->at(song_strGenres).get_asString());
1577 item->GetMusicInfoTag()->SetAlbum(record->at(song_strAlbum).get_asString());
1578 item->GetMusicInfoTag()->SetAlbumId(record->at(song_idAlbum).get_asInt());
1579 item->GetMusicInfoTag()->SetTrackAndDiskNumber(record->at(song_iTrack).get_asInt());
1580 item->GetMusicInfoTag()->SetDuration(record->at(song_iDuration).get_asInt());
1581 item->GetMusicInfoTag()->SetDatabaseId(record->at(song_idSong).get_asInt(), "song");
1583 stTime.wYear = (WORD)record->at(song_iYear).get_asInt();
1584 item->GetMusicInfoTag()->SetReleaseDate(stTime);
1585 item->GetMusicInfoTag()->SetTitle(record->at(song_strTitle).get_asString());
1586 item->SetLabel(record->at(song_strTitle).get_asString());
1587 item->m_lStartOffset = record->at(song_iStartOffset).get_asInt();
1588 item->SetProperty("item_start", item->m_lStartOffset);
1589 item->m_lEndOffset = record->at(song_iEndOffset).get_asInt();
1590 item->GetMusicInfoTag()->SetMusicBrainzTrackID(record->at(song_strMusicBrainzTrackID).get_asString());
1591 item->GetMusicInfoTag()->SetRating(record->at(song_rating).get_asChar());
1592 item->GetMusicInfoTag()->SetComment(record->at(song_comment).get_asString());
1593 item->GetMusicInfoTag()->SetPlayCount(record->at(song_iTimesPlayed).get_asInt());
1594 item->GetMusicInfoTag()->SetLastPlayed(record->at(song_lastplayed).get_asString());
1595 CStdString strRealPath = URIUtils::AddFileToFolder(record->at(song_strPath).get_asString(), record->at(song_strFileName).get_asString());
1596 item->GetMusicInfoTag()->SetURL(strRealPath);
1597 item->GetMusicInfoTag()->SetCompilation(record->at(song_bCompilation).get_asInt() == 1);
1598 item->GetMusicInfoTag()->SetAlbumArtist(record->at(song_strAlbumArtists).get_asString());
1599 item->GetMusicInfoTag()->SetLoaded(true);
1600 // Get filename with full path
1601 if (!baseUrl.IsValid())
1602 item->SetPath(strRealPath);
1605 CMusicDbUrl itemUrl = baseUrl;
1606 CStdString strFileName = record->at(song_strFileName).get_asString();
1607 CStdString strExt = URIUtils::GetExtension(strFileName);
1608 CStdString path = StringUtils::Format("%ld%s", record->at(song_idSong).get_asInt(), strExt.c_str());
1609 itemUrl.AppendPath(path);
1610 item->SetPath(itemUrl.ToString());
1614 CAlbum CMusicDatabase::GetAlbumFromDataset(dbiplus::Dataset* pDS, int offset /* = 0 */, bool imageURL /* = false*/)
1616 return GetAlbumFromDataset(pDS->get_sql_record(), offset, imageURL);
1619 CAlbum CMusicDatabase::GetAlbumFromDataset(const dbiplus::sql_record* const record, int offset /* = 0 */, bool imageURL /* = false*/)
1622 album.idAlbum = record->at(offset + album_idAlbum).get_asInt();
1623 album.strAlbum = record->at(offset + album_strAlbum).get_asString();
1624 if (album.strAlbum.empty())
1625 album.strAlbum = g_localizeStrings.Get(1050);
1626 album.strMusicBrainzAlbumID = record->at(offset + album_strMusicBrainzAlbumID).get_asString();
1627 album.artist = StringUtils::Split(record->at(offset + album_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
1628 album.genre = StringUtils::Split(record->at(offset + album_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
1629 album.iYear = record->at(offset + album_iYear).get_asInt();
1631 album.thumbURL.ParseString(record->at(offset + album_strThumbURL).get_asString());
1632 album.iRating = record->at(offset + album_iRating).get_asInt();
1633 album.iYear = record->at(offset + album_iYear).get_asInt();
1634 album.strReview = record->at(offset + album_strReview).get_asString();
1635 album.styles = StringUtils::Split(record->at(offset + album_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
1636 album.moods = StringUtils::Split(record->at(offset + album_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
1637 album.themes = StringUtils::Split(record->at(offset + album_strThemes).get_asString(), g_advancedSettings.m_musicItemSeparator);
1638 album.strLabel = record->at(offset + album_strLabel).get_asString();
1639 album.strType = record->at(offset + album_strType).get_asString();
1640 album.bCompilation = record->at(offset + album_bCompilation).get_asInt() == 1;
1641 album.iTimesPlayed = record->at(offset + album_iTimesPlayed).get_asInt();
1645 CArtistCredit CMusicDatabase::GetArtistCreditFromDataset(const dbiplus::sql_record* const record, int offset /* = 0 */)
1647 CArtistCredit artistCredit;
1648 artistCredit.idArtist = record->at(offset + artistCredit_idArtist).get_asInt();
1649 artistCredit.m_strArtist = record->at(offset + artistCredit_strArtist).get_asString();
1650 artistCredit.m_strMusicBrainzArtistID = record->at(offset + artistCredit_strMusicBrainzArtistID).get_asString();
1651 artistCredit.m_boolFeatured = record->at(offset + artistCredit_bFeatured).get_asBool();
1652 artistCredit.m_strJoinPhrase = record->at(offset + artistCredit_strJoinPhrase).get_asString();
1653 return artistCredit;
1656 CArtist CMusicDatabase::GetArtistFromDataset(dbiplus::Dataset* pDS, int offset /* = 0 */, bool needThumb /* = true */)
1658 return GetArtistFromDataset(pDS->get_sql_record(), offset, needThumb);
1661 CArtist CMusicDatabase::GetArtistFromDataset(const dbiplus::sql_record* const record, int offset /* = 0 */, bool needThumb /* = true */)
1664 artist.idArtist = record->at(offset + artist_idArtist).get_asInt();
1665 artist.strArtist = record->at(offset + artist_strArtist).get_asString();
1666 artist.strMusicBrainzArtistID = record->at(offset + artist_strMusicBrainzArtistID).get_asString();
1667 artist.genre = StringUtils::Split(record->at(offset + artist_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
1668 artist.strBiography = record->at(offset + artist_strBiography).get_asString();
1669 artist.styles = StringUtils::Split(record->at(offset + artist_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
1670 artist.moods = StringUtils::Split(record->at(offset + artist_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
1671 artist.strBorn = record->at(offset + artist_strBorn).get_asString();
1672 artist.strFormed = record->at(offset + artist_strFormed).get_asString();
1673 artist.strDied = record->at(offset + artist_strDied).get_asString();
1674 artist.strDisbanded = record->at(offset + artist_strDisbanded).get_asString();
1675 artist.yearsActive = StringUtils::Split(record->at(offset + artist_strYearsActive).get_asString(), g_advancedSettings.m_musicItemSeparator);
1676 artist.instruments = StringUtils::Split(record->at(offset + artist_strInstruments).get_asString(), g_advancedSettings.m_musicItemSeparator);
1680 artist.fanart.m_xml = record->at(artist_strFanart).get_asString();
1681 artist.fanart.Unpack();
1682 artist.thumbURL.ParseString(record->at(artist_strImage).get_asString());
1688 CSong CMusicDatabase::GetAlbumInfoSongFromDataset(const dbiplus::sql_record* const record, int offset /* = 0 */)
1691 song.iTrack = record->at(offset + albumInfoSong_iTrack).get_asInt();
1692 song.iDuration = record->at(offset + albumInfoSong_iDuration).get_asInt();
1693 song.strTitle = record->at(offset + albumInfoSong_strTitle).get_asString();
1697 bool CMusicDatabase::GetSongByFileName(const CStdString& strFileNameAndPath, CSong& song, int startOffset)
1700 CURL url(strFileNameAndPath);
1702 if (url.GetProtocol()=="musicdb")
1704 CStdString strFile = URIUtils::GetFileName(strFileNameAndPath);
1705 URIUtils::RemoveExtension(strFile);
1706 return GetSong(atol(strFile.c_str()), song);
1709 if (NULL == m_pDB.get()) return false;
1710 if (NULL == m_pDS.get()) return false;
1712 CStdString strPath, strFileName;
1713 URIUtils::Split(strFileNameAndPath, strPath, strFileName);
1714 URIUtils::AddSlashAtEnd(strPath);
1715 DWORD crc = ComputeCRC(strFileName);
1717 CStdString strSQL = PrepareSQL("select idSong from songview "
1718 "where dwFileNameCRC='%ul' and strPath='%s'",
1719 crc, strPath.c_str());
1721 strSQL += PrepareSQL(" AND iStartOffset=%i", startOffset);
1723 int idSong = (int)strtol(GetSingleValue(strSQL).c_str(), NULL, 10);
1725 return GetSong(idSong, song);
1730 int CMusicDatabase::GetAlbumIdByPath(const CStdString& strPath)
1734 CStdString strSQL=PrepareSQL("select distinct idAlbum from song join path on song.idPath = path.idPath where path.strPath='%s'", strPath.c_str());
1735 m_pDS->query(strSQL.c_str());
1739 int idAlbum = m_pDS->fv(0).get_asInt();
1746 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
1752 int CMusicDatabase::GetSongByArtistAndAlbumAndTitle(const CStdString& strArtist, const CStdString& strAlbum, const CStdString& strTitle)
1756 CStdString strSQL=PrepareSQL("select idSong from songview "
1757 "where strArtists like '%s' and strAlbum like '%s' and "
1758 "strTitle like '%s'",strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
1760 if (!m_pDS->query(strSQL.c_str())) return false;
1761 int iRowsFound = m_pDS->num_rows();
1762 if (iRowsFound == 0)
1767 int lResult = m_pDS->fv(0).get_asInt();
1768 m_pDS->close(); // cleanup recordset data
1773 CLog::Log(LOGERROR, "%s (%s,%s,%s) failed", __FUNCTION__, strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
1779 bool CMusicDatabase::SearchArtists(const CStdString& search, CFileItemList &artists)
1783 if (NULL == m_pDB.get()) return false;
1784 if (NULL == m_pDS.get()) return false;
1786 CStdString strVariousArtists = g_localizeStrings.Get(340).c_str();
1788 if (search.size() >= MIN_FULL_SEARCH_LENGTH)
1789 strSQL=PrepareSQL("select * from artist "
1790 "where (strArtist like '%s%%' or strArtist like '%% %s%%') and strArtist <> '%s' "
1791 , search.c_str(), search.c_str(), strVariousArtists.c_str() );
1793 strSQL=PrepareSQL("select * from artist "
1794 "where strArtist like '%s%%' and strArtist <> '%s' "
1795 , search.c_str(), strVariousArtists.c_str() );
1797 if (!m_pDS->query(strSQL.c_str())) return false;
1798 if (m_pDS->num_rows() == 0)
1804 CStdString artistLabel(g_localizeStrings.Get(557)); // Artist
1805 while (!m_pDS->eof())
1807 CStdString path = StringUtils::Format("musicdb://artists/%ld/", m_pDS->fv(0).get_asInt());
1808 CFileItemPtr pItem(new CFileItem(path, true));
1809 CStdString label = StringUtils::Format("[%s] %s", artistLabel.c_str(), m_pDS->fv(1).get_asString().c_str());
1810 pItem->SetLabel(label);
1811 label = StringUtils::Format("A %s", m_pDS->fv(1).get_asString().c_str()); // sort label is stored in the title tag
1812 pItem->GetMusicInfoTag()->SetTitle(label);
1813 pItem->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv(0).get_asInt(), "artist");
1818 m_pDS->close(); // cleanup recordset data
1823 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1829 bool CMusicDatabase::GetTop100(const CStdString& strBaseDir, CFileItemList& items)
1833 if (NULL == m_pDB.get()) return false;
1834 if (NULL == m_pDS.get()) return false;
1836 CMusicDbUrl baseUrl;
1837 if (!strBaseDir.empty() && !baseUrl.FromString(strBaseDir))
1840 CStdString strSQL="select * from songview "
1841 "where iTimesPlayed>0 "
1842 "order by iTimesPlayed desc "
1845 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1846 if (!m_pDS->query(strSQL.c_str())) return false;
1847 int iRowsFound = m_pDS->num_rows();
1848 if (iRowsFound == 0)
1853 items.Reserve(iRowsFound);
1854 while (!m_pDS->eof())
1856 CFileItemPtr item(new CFileItem);
1857 GetFileItemFromDataset(item.get(), baseUrl);
1862 m_pDS->close(); // cleanup recordset data
1867 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1873 bool CMusicDatabase::GetTop100Albums(VECALBUMS& albums)
1877 albums.erase(albums.begin(), albums.end());
1878 if (NULL == m_pDB.get()) return false;
1879 if (NULL == m_pDS.get()) return false;
1881 // NOTE: The song.idAlbum is needed for the group by, as for some reason group by albumview.idAlbum doesn't work
1882 // consistently - possibly an SQLite bug, as it works fine in SQLiteSpy (v3.3.17)
1883 CStdString strSQL = "select albumview.* from albumview "
1884 "where albumview.iTimesPlayed>0 and albumview.strAlbum != '' "
1885 "order by albumview.iTimesPlayed desc "
1888 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1889 if (!m_pDS->query(strSQL.c_str())) return false;
1890 int iRowsFound = m_pDS->num_rows();
1891 if (iRowsFound == 0)
1896 while (!m_pDS->eof())
1898 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
1902 m_pDS->close(); // cleanup recordset data
1907 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1913 bool CMusicDatabase::GetTop100AlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
1917 if (NULL == m_pDB.get()) return false;
1918 if (NULL == m_pDS.get()) return false;
1920 CMusicDbUrl baseUrl;
1921 if (!strBaseDir.empty() && baseUrl.FromString(strBaseDir))
1924 CStdString strSQL = StringUtils::Format("select * from songview join albumview on (songview.idAlbum = albumview.idAlbum) where albumview.idAlbum in (select song.idAlbum from song where song.iTimesPlayed>0 group by idAlbum order by sum(song.iTimesPlayed) desc limit 100) order by albumview.idAlbum in (select song.idAlbum from song where song.iTimesPlayed>0 group by idAlbum order by sum(song.iTimesPlayed) desc limit 100)");
1925 CLog::Log(LOGDEBUG,"GetTop100AlbumSongs() query: %s", strSQL.c_str());
1926 if (!m_pDS->query(strSQL.c_str())) return false;
1928 int iRowsFound = m_pDS->num_rows();
1929 if (iRowsFound == 0)
1935 // get data from returned rows
1936 items.Reserve(iRowsFound);
1937 while (!m_pDS->eof())
1939 CFileItemPtr item(new CFileItem);
1940 GetFileItemFromDataset(item.get(), baseUrl);
1951 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1956 bool CMusicDatabase::GetRecentlyPlayedAlbums(VECALBUMS& albums)
1960 albums.erase(albums.begin(), albums.end());
1961 if (NULL == m_pDB.get()) return false;
1962 if (NULL == m_pDS.get()) return false;
1964 CStdString strSQL = StringUtils::Format("select distinct albumview.* from song join albumview on albumview.idAlbum=song.idAlbum where song.lastplayed IS NOT NULL order by song.lastplayed desc limit %i", RECENTLY_PLAYED_LIMIT);
1965 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1966 if (!m_pDS->query(strSQL.c_str())) return false;
1967 int iRowsFound = m_pDS->num_rows();
1968 if (iRowsFound == 0)
1973 while (!m_pDS->eof())
1975 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
1979 m_pDS->close(); // cleanup recordset data
1984 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1990 bool CMusicDatabase::GetRecentlyPlayedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
1994 if (NULL == m_pDB.get()) return false;
1995 if (NULL == m_pDS.get()) return false;
1997 CMusicDbUrl baseUrl;
1998 if (!strBaseDir.empty() && !baseUrl.FromString(strBaseDir))
2001 CStdString strSQL = StringUtils::Format("select * from songview join albumview on (songview.idAlbum = albumview.idAlbum) where albumview.idAlbum in (select distinct albumview.idAlbum from albumview join song on albumview.idAlbum=song.idAlbum where song.lastplayed IS NOT NULL order by song.lastplayed desc limit %i)", g_advancedSettings.m_iMusicLibraryRecentlyAddedItems);
2002 CLog::Log(LOGDEBUG,"GetRecentlyPlayedAlbumSongs() query: %s", strSQL.c_str());
2003 if (!m_pDS->query(strSQL.c_str())) return false;
2005 int iRowsFound = m_pDS->num_rows();
2006 if (iRowsFound == 0)
2012 // get data from returned rows
2013 items.Reserve(iRowsFound);
2014 while (!m_pDS->eof())
2016 CFileItemPtr item(new CFileItem);
2017 GetFileItemFromDataset(item.get(), baseUrl);
2028 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2033 bool CMusicDatabase::GetRecentlyAddedAlbums(VECALBUMS& albums, unsigned int limit)
2037 albums.erase(albums.begin(), albums.end());
2038 if (NULL == m_pDB.get()) return false;
2039 if (NULL == m_pDS.get()) return false;
2041 CStdString strSQL = StringUtils::Format("select * from albumview where strAlbum != '' order by idAlbum desc limit %u", limit ? limit : g_advancedSettings.m_iMusicLibraryRecentlyAddedItems);
2043 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2044 if (!m_pDS->query(strSQL.c_str())) return false;
2045 int iRowsFound = m_pDS->num_rows();
2046 if (iRowsFound == 0)
2052 while (!m_pDS->eof())
2054 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
2058 m_pDS->close(); // cleanup recordset data
2063 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2069 bool CMusicDatabase::GetRecentlyAddedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items, unsigned int limit)
2073 if (NULL == m_pDB.get()) return false;
2074 if (NULL == m_pDS.get()) return false;
2076 CMusicDbUrl baseUrl;
2077 if (!strBaseDir.empty() && !baseUrl.FromString(strBaseDir))
2081 strSQL = PrepareSQL("SELECT songview.* FROM (SELECT idAlbum FROM albumview ORDER BY idAlbum DESC LIMIT %u) AS recentalbums JOIN songview ON songview.idAlbum=recentalbums.idAlbum", limit ? limit : g_advancedSettings.m_iMusicLibraryRecentlyAddedItems);
2082 CLog::Log(LOGDEBUG,"GetRecentlyAddedAlbumSongs() query: %s", strSQL.c_str());
2083 if (!m_pDS->query(strSQL.c_str())) return false;
2085 int iRowsFound = m_pDS->num_rows();
2086 if (iRowsFound == 0)
2092 // get data from returned rows
2093 items.Reserve(iRowsFound);
2094 while (!m_pDS->eof())
2096 CFileItemPtr item(new CFileItem);
2097 GetFileItemFromDataset(item.get(), baseUrl);
2108 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2113 void CMusicDatabase::IncrementPlayCount(const CFileItem& item)
2117 if (NULL == m_pDB.get()) return;
2118 if (NULL == m_pDS.get()) return;
2120 int idSong = GetSongIDFromPath(item.GetPath());
2122 CStdString sql=PrepareSQL("UPDATE song SET iTimesPlayed=iTimesPlayed+1, lastplayed=CURRENT_TIMESTAMP where idSong=%i", idSong);
2123 m_pDS->exec(sql.c_str());
2127 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, item.GetPath().c_str());
2131 bool CMusicDatabase::GetSongsByPath(const CStdString& strPath1, MAPSONGS& songs, bool bAppendToMap)
2133 CStdString strPath(strPath1);
2136 if (!URIUtils::HasSlashAtEnd(strPath))
2137 URIUtils::AddSlashAtEnd(strPath);
2142 if (NULL == m_pDB.get()) return false;
2143 if (NULL == m_pDS.get()) return false;
2145 CStdString strSQL=PrepareSQL("select * from songview where strPath='%s'", strPath.c_str() );
2146 if (!m_pDS->query(strSQL.c_str())) return false;
2147 int iRowsFound = m_pDS->num_rows();
2148 if (iRowsFound == 0)
2153 while (!m_pDS->eof())
2155 CSong song = GetSongFromDataset();
2156 songs.insert(make_pair(song.strFileName, song));
2160 m_pDS->close(); // cleanup recordset data
2165 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
2171 void CMusicDatabase::EmptyCache()
2173 m_artistCache.erase(m_artistCache.begin(), m_artistCache.end());
2174 m_genreCache.erase(m_genreCache.begin(), m_genreCache.end());
2175 m_pathCache.erase(m_pathCache.begin(), m_pathCache.end());
2176 m_albumCache.erase(m_albumCache.begin(), m_albumCache.end());
2177 m_thumbCache.erase(m_thumbCache.begin(), m_thumbCache.end());
2180 bool CMusicDatabase::Search(const CStdString& search, CFileItemList &items)
2182 unsigned int time = XbmcThreads::SystemClockMillis();
2183 // first grab all the artists that match
2184 SearchArtists(search, items);
2185 CLog::Log(LOGDEBUG, "%s Artist search in %i ms",
2186 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
2188 // then albums that match
2189 SearchAlbums(search, items);
2190 CLog::Log(LOGDEBUG, "%s Album search in %i ms",
2191 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
2193 // and finally songs
2194 SearchSongs(search, items);
2195 CLog::Log(LOGDEBUG, "%s Songs search in %i ms",
2196 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
2200 bool CMusicDatabase::SearchSongs(const CStdString& search, CFileItemList &items)
2204 if (NULL == m_pDB.get()) return false;
2205 if (NULL == m_pDS.get()) return false;
2207 CMusicDbUrl baseUrl;
2208 if (!baseUrl.FromString("musicdb://songs/"))
2212 if (search.size() >= MIN_FULL_SEARCH_LENGTH)
2213 strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' or strTitle like '%% %s%%' limit 1000", search.c_str(), search.c_str());
2215 strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' limit 1000", search.c_str());
2217 if (!m_pDS->query(strSQL.c_str())) return false;
2218 if (m_pDS->num_rows() == 0) return false;
2220 CStdString songLabel = g_localizeStrings.Get(179); // Song
2221 while (!m_pDS->eof())
2223 CFileItemPtr item(new CFileItem);
2224 GetFileItemFromDataset(item.get(), baseUrl);
2234 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2240 bool CMusicDatabase::SearchAlbums(const CStdString& search, CFileItemList &albums)
2244 if (NULL == m_pDB.get()) return false;
2245 if (NULL == m_pDS.get()) return false;
2248 if (search.size() >= MIN_FULL_SEARCH_LENGTH)
2249 strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%' or strAlbum like '%% %s%%'", search.c_str(), search.c_str());
2251 strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%'", search.c_str());
2253 if (!m_pDS->query(strSQL.c_str())) return false;
2255 CStdString albumLabel(g_localizeStrings.Get(558)); // Album
2256 while (!m_pDS->eof())
2258 CAlbum album = GetAlbumFromDataset(m_pDS.get());
2259 CStdString path = StringUtils::Format("musicdb://albums/%ld/", album.idAlbum);
2260 CFileItemPtr pItem(new CFileItem(path, album));
2261 CStdString label = StringUtils::Format("[%s] %s", albumLabel.c_str(), album.strAlbum.c_str());
2262 pItem->SetLabel(label);
2263 label = StringUtils::Format("B %s", album.strAlbum.c_str()); // sort label is stored in the title tag
2264 pItem->GetMusicInfoTag()->SetTitle(label);
2268 m_pDS->close(); // cleanup recordset data
2273 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2278 bool CMusicDatabase::CleanupSongsByIds(const CStdString &strSongIds)
2282 if (NULL == m_pDB.get()) return false;
2283 if (NULL == m_pDS.get()) return false;
2284 // ok, now find all idSong's
2285 CStdString strSQL=PrepareSQL("select * from song join path on song.idPath = path.idPath where song.idSong in %s", strSongIds.c_str());
2286 if (!m_pDS->query(strSQL.c_str())) return false;
2287 int iRowsFound = m_pDS->num_rows();
2288 if (iRowsFound == 0)
2293 vector<std::string> songsToDelete;
2294 while (!m_pDS->eof())
2295 { // get the full song path
2296 CStdString strFileName = URIUtils::AddFileToFolder(m_pDS->fv("path.strPath").get_asString(), m_pDS->fv("song.strFileName").get_asString());
2298 // Special case for streams inside an ogg file. (oggstream)
2299 // The last dir in the path is the ogg file that
2300 // contains the stream, so test if its there
2301 if (URIUtils::HasExtension(strFileName, ".oggstream|.nsfstream"))
2303 strFileName = URIUtils::GetDirectory(strFileName);
2304 // we are dropping back to a file, so remove the slash at end
2305 URIUtils::RemoveSlashAtEnd(strFileName);
2308 if (!CFile::Exists(strFileName))
2309 { // file no longer exists, so add to deletion list
2310 songsToDelete.push_back(m_pDS->fv("song.idSong").get_asString());
2316 if (!songsToDelete.empty())
2318 std::string strSongsToDelete = "(" + StringUtils::Join(songsToDelete, ",") + ")";
2319 // ok, now delete these songs + all references to them from the linked tables
2320 strSQL = "delete from song where idSong in " + strSongsToDelete;
2321 m_pDS->exec(strSQL.c_str());
2328 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongsFromPaths()");
2333 bool CMusicDatabase::CleanupSongs()
2337 // run through all songs and get all unique path ids
2339 for (int i=0;;i+=iLIMIT)
2341 CStdString strSQL=PrepareSQL("select song.idSong from song order by song.idSong limit %i offset %i",iLIMIT,i);
2342 if (!m_pDS->query(strSQL.c_str())) return false;
2343 int iRowsFound = m_pDS->num_rows();
2344 // keep going until no rows are left!
2345 if (iRowsFound == 0)
2351 std::vector<std::string> songIds;
2352 while (!m_pDS->eof())
2354 songIds.push_back(m_pDS->fv("song.idSong").get_asString());
2358 std::string strSongIds = "(" + StringUtils::Join(songIds, ",") + ")";
2359 CLog::Log(LOGDEBUG,"Checking songs from song ID list: %s",strSongIds.c_str());
2360 if (!CleanupSongsByIds(strSongIds)) return false;
2366 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongs()");
2371 bool CMusicDatabase::CleanupAlbums()
2375 // This must be run AFTER songs have been cleaned up
2376 // delete albums with no reference to songs
2377 CStdString strSQL = "select * from album where album.idAlbum not in (select idAlbum from song)";
2378 if (!m_pDS->query(strSQL.c_str())) return false;
2379 int iRowsFound = m_pDS->num_rows();
2380 if (iRowsFound == 0)
2386 std::vector<std::string> albumIds;
2387 while (!m_pDS->eof())
2389 albumIds.push_back(m_pDS->fv("album.idAlbum").get_asString());
2394 std::string strAlbumIds = "(" + StringUtils::Join(albumIds, ",") + ")";
2395 // ok, now we can delete them and the references in the linked tables
2396 strSQL = "delete from album where idAlbum in " + strAlbumIds;
2397 m_pDS->exec(strSQL.c_str());
2402 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupAlbums()");
2407 bool CMusicDatabase::CleanupPaths()
2411 // needs to be done AFTER the songs and albums have been cleaned up.
2412 // we can happily delete any path that has no reference to a song
2413 // but we must keep all paths that have been scanned that may contain songs in subpaths
2415 // first create a temporary table of song paths
2416 m_pDS->exec("CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512))\n");
2417 m_pDS->exec("INSERT INTO songpaths select idPath,strPath from path where idPath in (select idPath from song)\n");
2419 // grab all paths that aren't immediately connected with a song
2420 CStdString sql = "select * from path where idPath not in (select idPath from song)";
2421 if (!m_pDS->query(sql.c_str())) return false;
2422 int iRowsFound = m_pDS->num_rows();
2423 if (iRowsFound == 0)
2428 // and construct a list to delete
2429 std::vector<std::string> pathIds;
2430 while (!m_pDS->eof())
2432 // anything that isn't a parent path of a song path is to be deleted
2433 CStdString path = m_pDS->fv("strPath").get_asString();
2434 CStdString sql = PrepareSQL("select count(idPath) from songpaths where SUBSTR(strPath,1,%i)='%s'", StringUtils::utf8_strlen(path.c_str()), path.c_str());
2435 if (m_pDS2->query(sql.c_str()) && m_pDS2->num_rows() == 1 && m_pDS2->fv(0).get_asInt() == 0)
2436 pathIds.push_back(m_pDS->fv("idPath").get_asString()); // nothing found, so delete
2442 if (!pathIds.empty())
2444 // do the deletion, and drop our temp table
2445 std::string deleteSQL = "DELETE FROM path WHERE idPath IN (" + StringUtils::Join(pathIds, ",") + ")";
2446 m_pDS->exec(deleteSQL.c_str());
2448 m_pDS->exec("drop table songpaths");
2453 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupPaths() or was aborted");
2458 bool CMusicDatabase::InsideScannedPath(const CStdString& path)
2460 CStdString sql = PrepareSQL("select idPath from path where SUBSTR(strPath,1,%i)='%s' LIMIT 1", path.size(), path.c_str());
2461 return !GetSingleValue(sql).empty();
2464 bool CMusicDatabase::CleanupArtists()
2468 // (nested queries by Bobbin007)
2469 // must be executed AFTER the song, album and their artist link tables are cleaned.
2470 // don't delete the "Various Artists" string
2472 // Create temp table to avoid 1442 trigger hell on mysql
2473 m_pDS->exec("CREATE TEMPORARY TABLE tmp_delartists (idArtist integer)");
2474 m_pDS->exec("INSERT INTO tmp_delartists select idArtist from song_artist");
2475 m_pDS->exec("INSERT INTO tmp_delartists select idArtist from album_artist");
2476 m_pDS->exec("delete from artist where idArtist not in (select idArtist from tmp_delartists)");
2477 m_pDS->exec("DROP TABLE tmp_delartists");
2482 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupArtists() or was aborted");
2487 bool CMusicDatabase::CleanupGenres()
2491 // Cleanup orphaned genres (ie those that don't belong to a song or an album entry)
2492 // (nested queries by Bobbin007)
2493 // Must be executed AFTER the song, song_genre, album and album_genre tables have been cleaned.
2494 CStdString strSQL = "delete from genre where idGenre not in (select idGenre from song_genre) and";
2495 strSQL += " idGenre not in (select idGenre from album_genre)";
2496 m_pDS->exec(strSQL.c_str());
2501 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupGenres() or was aborted");
2506 bool CMusicDatabase::CleanupOrphanedItems()
2508 // paths aren't cleaned up here - they're cleaned up in RemoveSongsFromPath()
2509 if (NULL == m_pDB.get()) return false;
2510 if (NULL == m_pDS.get()) return false;
2511 if (!CleanupAlbums()) return false;
2512 if (!CleanupArtists()) return false;
2513 if (!CleanupGenres()) return false;
2517 int CMusicDatabase::Cleanup(CGUIDialogProgress *pDlgProgress)
2519 if (NULL == m_pDB.get()) return ERROR_DATABASE;
2520 if (NULL == m_pDS.get()) return ERROR_DATABASE;
2523 unsigned int time = XbmcThreads::SystemClockMillis();
2524 CLog::Log(LOGNOTICE, "%s: Starting musicdatabase cleanup ..", __FUNCTION__);
2525 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnCleanStarted");
2527 // first cleanup any songs with invalid paths
2530 pDlgProgress->SetHeading(700);
2531 pDlgProgress->SetLine(0, "");
2532 pDlgProgress->SetLine(1, 318);
2533 pDlgProgress->SetLine(2, 330);
2534 pDlgProgress->SetPercentage(0);
2535 pDlgProgress->StartModal();
2536 pDlgProgress->ShowProgressBar(true);
2538 if (!CleanupSongs())
2540 ret = ERROR_REORG_SONGS;
2543 // then the albums that are not linked to a song or to album, or whose path is removed
2546 pDlgProgress->SetLine(1, 326);
2547 pDlgProgress->SetPercentage(20);
2548 pDlgProgress->Progress();
2550 if (!CleanupAlbums())
2552 ret = ERROR_REORG_ALBUM;
2558 pDlgProgress->SetLine(1, 324);
2559 pDlgProgress->SetPercentage(40);
2560 pDlgProgress->Progress();
2562 if (!CleanupPaths())
2564 ret = ERROR_REORG_PATH;
2567 // and finally artists + genres
2570 pDlgProgress->SetLine(1, 320);
2571 pDlgProgress->SetPercentage(60);
2572 pDlgProgress->Progress();
2574 if (!CleanupArtists())
2576 ret = ERROR_REORG_ARTIST;
2581 pDlgProgress->SetLine(1, 322);
2582 pDlgProgress->SetPercentage(80);
2583 pDlgProgress->Progress();
2585 if (!CleanupGenres())
2587 ret = ERROR_REORG_GENRE;
2590 // commit transaction
2593 pDlgProgress->SetLine(1, 328);
2594 pDlgProgress->SetPercentage(90);
2595 pDlgProgress->Progress();
2597 if (!CommitTransaction())
2599 ret = ERROR_WRITING_CHANGES;
2602 // and compress the database
2605 pDlgProgress->SetLine(1, 331);
2606 pDlgProgress->SetPercentage(100);
2607 pDlgProgress->Progress();
2609 time = XbmcThreads::SystemClockMillis() - time;
2610 CLog::Log(LOGNOTICE, "%s: Cleaning musicdatabase done. Operation took %s", __FUNCTION__, StringUtils::SecondsToTimeString(time / 1000).c_str());
2611 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnCleanFinished");
2613 if (!Compress(false))
2615 return ERROR_COMPRESSING;
2620 RollbackTransaction();
2621 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnCleanFinished");
2625 bool CMusicDatabase::LookupCDDBInfo(bool bRequery/*=false*/)
2627 #ifdef HAS_DVD_DRIVE
2628 if (!CSettings::Get().GetBool("audiocds.usecddb"))
2631 // check network connectivity
2632 if (!g_application.getNetwork().IsAvailable())
2635 // Get information for the inserted disc
2636 CCdInfo* pCdInfo = g_mediaManager.GetCdInfo();
2637 if (pCdInfo == NULL)
2640 // If the disc has no tracks, we are finished here.
2641 int nTracks = pCdInfo->GetTrackCount();
2645 // Delete old info if any
2648 CStdString strFile = StringUtils::Format("%x.cddb", pCdInfo->GetCddbDiscId());
2649 CFile::Delete(URIUtils::AddFileToFolder(CProfilesManager::Get().GetCDDBFolder(), strFile));
2654 cddb.setCacheDir(CProfilesManager::Get().GetCDDBFolder());
2656 // Do we have to look for cddb information
2657 if (pCdInfo->HasCDDBInfo() && !cddb.isCDCached(pCdInfo))
2659 CGUIDialogProgress* pDialogProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
2660 CGUIDialogSelect *pDlgSelect = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
2662 if (!pDialogProgress) return false;
2663 if (!pDlgSelect) return false;
2665 // Show progress dialog if we have to connect to freedb.org
2666 pDialogProgress->SetHeading(255); //CDDB
2667 pDialogProgress->SetLine(0, ""); // Querying freedb for CDDB info
2668 pDialogProgress->SetLine(1, 256);
2669 pDialogProgress->SetLine(2, "");
2670 pDialogProgress->ShowProgressBar(false);
2671 pDialogProgress->StartModal();
2673 // get cddb information
2674 if (!cddb.queryCDinfo(pCdInfo))
2676 pDialogProgress->Close();
2677 int lasterror = cddb.getLastError();
2679 // Have we found more then on match in cddb for this disc,...
2680 if (lasterror == E_WAIT_FOR_INPUT)
2682 // ...yes, show the matches found in a select dialog
2683 // and let the user choose an entry.
2684 pDlgSelect->Reset();
2685 pDlgSelect->SetHeading(255);
2689 CStdString strTitle = cddb.getInexactTitle(i);
2690 if (strTitle == "") break;
2692 CStdString strArtist = cddb.getInexactArtist(i);
2693 if (!strArtist.empty())
2694 strTitle += " - " + strArtist;
2696 pDlgSelect->Add(strTitle);
2699 pDlgSelect->DoModal();
2701 // Has the user selected a match...
2702 int iSelectedCD = pDlgSelect->GetSelectedLabel();
2703 if (iSelectedCD >= 0)
2705 // ...query cddb for the inexact match
2706 if (!cddb.queryCDinfo(pCdInfo, 1 + iSelectedCD))
2707 pCdInfo->SetNoCDDBInfo();
2710 pCdInfo->SetNoCDDBInfo();
2712 else if (lasterror == E_NO_MATCH_FOUND)
2714 pCdInfo->SetNoCDDBInfo();
2718 pCdInfo->SetNoCDDBInfo();
2719 // ..no, an error occured, display it to the user
2720 CStdString strErrorText = StringUtils::Format("[%d] %s", cddb.getLastError(), cddb.getLastErrorText());
2721 CGUIDialogOK::ShowAndGetInput(255, 257, strErrorText, 0);
2723 } // if ( !cddb.queryCDinfo( pCdInfo ) )
2725 pDialogProgress->Close();
2728 // Filling the file items with cddb info happens in CMusicInfoTagLoaderCDDA
2730 return pCdInfo->HasCDDBInfo();
2736 void CMusicDatabase::DeleteCDDBInfo()
2738 #ifdef HAS_DVD_DRIVE
2739 CFileItemList items;
2740 if (!CDirectory::GetDirectory(CProfilesManager::Get().GetCDDBFolder(), items, ".cddb", DIR_FLAG_NO_FILE_DIRS))
2742 CGUIDialogOK::ShowAndGetInput(313, 426, 0, 0);
2745 // Show a selectdialog that the user can select the album to delete
2746 CGUIDialogSelect *pDlg = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
2749 pDlg->SetHeading(g_localizeStrings.Get(181).c_str());
2752 map<ULONG, CStdString> mapCDDBIds;
2753 for (int i = 0; i < items.Size(); ++i)
2755 if (items[i]->m_bIsFolder)
2758 CStdString strFile = URIUtils::GetFileName(items[i]->GetPath());
2759 strFile.erase(strFile.size() - 5, 5);
2760 ULONG lDiscId = strtoul(strFile.c_str(), NULL, 16);
2762 cddb.setCacheDir(CProfilesManager::Get().GetCDDBFolder());
2764 if (!cddb.queryCache(lDiscId))
2767 CStdString strDiskTitle, strDiskArtist;
2768 cddb.getDiskTitle(strDiskTitle);
2769 cddb.getDiskArtist(strDiskArtist);
2772 if (strDiskArtist.empty())
2775 str = strDiskTitle + " - " + strDiskArtist;
2778 mapCDDBIds.insert(pair<ULONG, CStdString>(lDiscId, str));
2784 // and wait till user selects one
2785 int iSelectedAlbum = pDlg->GetSelectedLabel();
2786 if (iSelectedAlbum < 0)
2788 mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
2792 CStdString strSelectedAlbum = pDlg->GetSelectedLabelText();
2793 map<ULONG, CStdString>::iterator it;
2794 for (it = mapCDDBIds.begin();it != mapCDDBIds.end();it++)
2796 if (it->second == strSelectedAlbum)
2798 CStdString strFile = StringUtils::Format("%x.cddb", it->first);
2799 CFile::Delete(URIUtils::AddFileToFolder(CProfilesManager::Get().GetCDDBFolder(), strFile));
2803 mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
2808 void CMusicDatabase::Clean()
2810 // If we are scanning for music info in the background,
2811 // other writing access to the database is prohibited.
2812 if (g_application.IsMusicScanning())
2814 CGUIDialogOK::ShowAndGetInput(189, 14057, 0, 0);
2818 if (CGUIDialogYesNo::ShowAndGetInput(313, 333, 0, 0))
2820 CGUIDialogProgress* dlgProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
2823 CMusicDatabase musicdatabase;
2824 if (musicdatabase.Open())
2826 int iReturnString = musicdatabase.Cleanup(dlgProgress);
2827 musicdatabase.Close();
2829 if (iReturnString != ERROR_OK)
2831 CGUIDialogOK::ShowAndGetInput(313, iReturnString, 0, 0);
2834 dlgProgress->Close();
2839 bool CMusicDatabase::GetGenresNav(const CStdString& strBaseDir, CFileItemList& items, const Filter &filter /* = Filter() */, bool countOnly /* = false */)
2843 if (NULL == m_pDB.get()) return false;
2844 if (NULL == m_pDS.get()) return false;
2846 // get primary genres for songs - could be simplified to just SELECT * FROM genre?
2847 CStdString strSQL = "SELECT %s FROM genre ";
2849 Filter extFilter = filter;
2850 CMusicDbUrl musicUrl;
2851 SortDescription sorting;
2852 if (!musicUrl.FromString(strBaseDir) || !GetFilter(musicUrl, extFilter, sorting))
2855 // if there are extra WHERE conditions we might need access
2856 // to songview or albumview for these conditions
2857 if (extFilter.where.size() > 0)
2859 if (extFilter.where.find("artistview") != string::npos)
2860 extFilter.AppendJoin("JOIN song_genre ON song_genre.idGenre = genre.idGenre JOIN songview ON songview.idSong = song_genre.idSong "
2861 "JOIN song_artist ON song_artist.idSong = songview.idSong JOIN artistview ON artistview.idArtist = song_artist.idArtist");
2862 else if (extFilter.where.find("songview") != string::npos)
2863 extFilter.AppendJoin("JOIN song_genre ON song_genre.idGenre = genre.idGenre JOIN songview ON songview.idSong = song_genre.idSong");
2864 else if (extFilter.where.find("albumview") != string::npos)
2865 extFilter.AppendJoin("JOIN album_genre ON album_genre.idGenre = genre.idGenre JOIN albumview ON albumview.idAlbum = album_genre.idAlbum");
2867 extFilter.AppendGroup("genre.idGenre");
2869 extFilter.AppendWhere("genre.strGenre != ''");
2873 extFilter.fields = "COUNT(DISTINCT genre.idGenre)";
2874 extFilter.group.clear();
2875 extFilter.order.clear();
2878 CStdString strSQLExtra;
2879 if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
2882 strSQL = PrepareSQL(strSQL.c_str(), !extFilter.fields.empty() && extFilter.fields.compare("*") != 0 ? extFilter.fields.c_str() : "genre.*") + strSQLExtra;
2885 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2887 if (!m_pDS->query(strSQL.c_str()))
2889 int iRowsFound = m_pDS->num_rows();
2890 if (iRowsFound == 0)
2898 CFileItemPtr pItem(new CFileItem());
2899 pItem->SetProperty("total", iRowsFound == 1 ? m_pDS->fv(0).get_asInt() : iRowsFound);
2906 // get data from returned rows
2907 while (!m_pDS->eof())
2909 CFileItemPtr pItem(new CFileItem(m_pDS->fv("genre.strGenre").get_asString()));
2910 pItem->GetMusicInfoTag()->SetGenre(m_pDS->fv("genre.strGenre").get_asString());
2911 pItem->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv("genre.idGenre").get_asInt(), "genre");
2913 CMusicDbUrl itemUrl = musicUrl;
2914 CStdString strDir = StringUtils::Format("%ld/", m_pDS->fv("genre.idGenre").get_asInt());
2915 itemUrl.AppendPath(strDir);
2916 pItem->SetPath(itemUrl.ToString());
2918 pItem->m_bIsFolder = true;
2931 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2936 bool CMusicDatabase::GetYearsNav(const CStdString& strBaseDir, CFileItemList& items, const Filter &filter /* = Filter() */)
2940 if (NULL == m_pDB.get()) return false;
2941 if (NULL == m_pDS.get()) return false;
2943 Filter extFilter = filter;
2944 CMusicDbUrl musicUrl;
2945 SortDescription sorting;
2946 if (!musicUrl.FromString(strBaseDir) || !GetFilter(musicUrl, extFilter, sorting))
2949 // get years from album list
2950 CStdString strSQL = "SELECT DISTINCT albumview.iYear FROM albumview ";
2951 extFilter.AppendWhere("albumview.iYear <> 0");
2953 if (!BuildSQL(strSQL, extFilter, strSQL))
2957 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2958 if (!m_pDS->query(strSQL.c_str()))
2960 int iRowsFound = m_pDS->num_rows();
2961 if (iRowsFound == 0)
2967 // get data from returned rows
2968 while (!m_pDS->eof())
2970 CFileItemPtr pItem(new CFileItem(m_pDS->fv(0).get_asString()));
2972 stTime.wYear = (WORD)m_pDS->fv(0).get_asInt();
2973 pItem->GetMusicInfoTag()->SetReleaseDate(stTime);
2975 CMusicDbUrl itemUrl = musicUrl;
2976 CStdString strDir = StringUtils::Format("%ld/", m_pDS->fv(0).get_asInt());
2977 itemUrl.AppendPath(strDir);
2978 pItem->SetPath(itemUrl.ToString());
2980 pItem->m_bIsFolder = true;
2993 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2998 bool CMusicDatabase::GetAlbumsByYear(const CStdString& strBaseDir, CFileItemList& items, int year)
3000 CMusicDbUrl musicUrl;
3001 if (!musicUrl.FromString(strBaseDir))
3004 musicUrl.AddOption("year", year);
3007 return GetAlbumsByWhere(musicUrl.ToString(), filter, items);
3010 bool CMusicDatabase::GetCommonNav(const CStdString &strBaseDir, const CStdString &table, const CStdString &labelField, CFileItemList &items, const Filter &filter /* = Filter() */, bool countOnly /* = false */)
3012 if (NULL == m_pDB.get()) return false;
3013 if (NULL == m_pDS.get()) return false;
3015 if (table.empty() || labelField.empty())
3020 Filter extFilter = filter;
3021 CStdString strSQL = "SELECT %s FROM " + table + " ";
3022 extFilter.AppendGroup(labelField);
3023 extFilter.AppendWhere(labelField + " != ''");
3027 extFilter.fields = "COUNT(DISTINCT " + labelField + ")";
3028 extFilter.group.clear();
3029 extFilter.order.clear();
3032 CMusicDbUrl musicUrl;
3033 if (!BuildSQL(strBaseDir, strSQL, extFilter, strSQL, musicUrl))
3036 strSQL = PrepareSQL(strSQL, !extFilter.fields.empty() ? extFilter.fields.c_str() : labelField.c_str());
3039 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
3040 if (!m_pDS->query(strSQL.c_str()))
3043 int iRowsFound = m_pDS->num_rows();
3044 if (iRowsFound <= 0)
3052 CFileItemPtr pItem(new CFileItem());
3053 pItem->SetProperty("total", iRowsFound == 1 ? m_pDS->fv(0).get_asInt() : iRowsFound);
3060 // get data from returned rows
3061 while (!m_pDS->eof())
3063 string labelValue = m_pDS->fv(labelField).get_asString();
3064 CFileItemPtr pItem(new CFileItem(labelValue));
3066 CMusicDbUrl itemUrl = musicUrl;
3067 CStdString strDir = StringUtils::Format("%s/", labelValue.c_str());
3068 itemUrl.AppendPath(strDir);
3069 pItem->SetPath(itemUrl.ToString());
3071 pItem->m_bIsFolder = true;
3085 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3091 bool CMusicDatabase::GetAlbumTypesNav(const CStdString &strBaseDir, CFileItemList &items, const Filter &filter /* = Filter() */, bool countOnly /* = false */)
3093 return GetCommonNav(strBaseDir, "albumview", "albumview.strType", items, filter, countOnly);
3096 bool CMusicDatabase::GetMusicLabelsNav(const CStdString &strBaseDir, CFileItemList &items, const Filter &filter /* = Filter() */, bool countOnly /* = false */)
3098 return GetCommonNav(strBaseDir, "albumview", "albumview.strLabel", items, filter, countOnly);
3101 bool CMusicDatabase::GetArtistsNav(const CStdString& strBaseDir, CFileItemList& items, bool albumArtistsOnly /* = false */, int idGenre /* = -1 */, int idAlbum /* = -1 */, int idSong /* = -1 */, const Filter &filter /* = Filter() */, const SortDescription &sortDescription /* = SortDescription() */, bool countOnly /* = false */)
3103 if (NULL == m_pDB.get()) return false;
3104 if (NULL == m_pDS.get()) return false;
3107 unsigned int time = XbmcThreads::SystemClockMillis();
3109 CMusicDbUrl musicUrl;
3110 if (!musicUrl.FromString(strBaseDir))
3114 musicUrl.AddOption("genreid", idGenre);
3115 else if (idAlbum > 0)
3116 musicUrl.AddOption("albumid", idAlbum);
3117 else if (idSong > 0)
3118 musicUrl.AddOption("songid", idSong);
3120 musicUrl.AddOption("albumartistsonly", albumArtistsOnly);
3122 bool result = GetArtistsByWhere(musicUrl.ToString(), filter, items, sortDescription, countOnly);
3123 CLog::Log(LOGDEBUG,"Time to retrieve artists from dataset = %i", XbmcThreads::SystemClockMillis() - time);
3130 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3135 bool CMusicDatabase::GetArtistsByWhere(const CStdString& strBaseDir, const Filter &filter, CFileItemList& items, const SortDescription &sortDescription /* = SortDescription() */, bool countOnly /* = false */)
3137 if (NULL == m_pDB.get()) return false;
3138 if (NULL == m_pDS.get()) return false;
3144 CStdString strSQL = "SELECT %s FROM artistview ";
3146 Filter extFilter = filter;
3147 CMusicDbUrl musicUrl;
3148 SortDescription sorting = sortDescription;
3149 if (!musicUrl.FromString(strBaseDir) || !GetFilter(musicUrl, extFilter, sorting))
3152 // if there are extra WHERE conditions we might need access
3153 // to songview or albumview for these conditions
3154 if (extFilter.where.size() > 0)
3156 bool extended = false;
3157 if (extFilter.where.find("songview") != string::npos)
3160 extFilter.AppendJoin("JOIN song_artist ON song_artist.idArtist = artistview.idArtist JOIN songview ON songview.idSong = song_artist.idSong");
3162 else if (extFilter.where.find("albumview") != string::npos)
3165 extFilter.AppendJoin("JOIN album_artist ON album_artist.idArtist = artistview.idArtist JOIN albumview ON albumview.idAlbum = album_artist.idAlbum");
3169 extFilter.AppendGroup("artistview.idArtist");
3174 extFilter.fields = "COUNT(DISTINCT artistview.idArtist)";
3175 extFilter.group.clear();
3176 extFilter.order.clear();
3179 CStdString strSQLExtra;
3180 if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
3183 // Apply the limiting directly here if there's no special sorting but limiting
3184 if (extFilter.limit.empty() &&
3185 sortDescription.sortBy == SortByNone &&
3186 (sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
3188 total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
3189 strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
3192 strSQL = PrepareSQL(strSQL.c_str(), !extFilter.fields.empty() && extFilter.fields.compare("*") != 0 ? extFilter.fields.c_str() : "artistview.*") + strSQLExtra;
3195 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
3196 if (!m_pDS->query(strSQL.c_str())) return false;
3197 int iRowsFound = m_pDS->num_rows();
3198 if (iRowsFound == 0)
3206 CFileItemPtr pItem(new CFileItem());
3207 pItem->SetProperty("total", iRowsFound == 1 ? m_pDS->fv(0).get_asInt() : iRowsFound);
3214 // store the total value of items as a property
3215 if (total < iRowsFound)
3217 items.SetProperty("total", total);
3219 DatabaseResults results;
3220 results.reserve(iRowsFound);
3221 if (!SortUtils::SortFromDataset(sortDescription, MediaTypeArtist, m_pDS, results))
3224 // get data from returned rows
3225 items.Reserve(results.size());
3226 const dbiplus::query_data &data = m_pDS->get_result_set().records;
3227 for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
3229 unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
3230 const dbiplus::sql_record* const record = data.at(targetRow);
3234 CArtist artist = GetArtistFromDataset(record, false);
3235 CFileItemPtr pItem(new CFileItem(artist));
3237 CMusicDbUrl itemUrl = musicUrl;
3238 CStdString path = StringUtils::Format("%ld/", artist.idArtist);
3239 itemUrl.AppendPath(path);
3240 pItem->SetPath(itemUrl.ToString());
3242 pItem->GetMusicInfoTag()->SetDatabaseId(artist.idArtist, "artist");
3243 pItem->SetIconImage("DefaultArtist.png");
3245 SetPropertiesFromArtist(*pItem, artist);
3251 CLog::Log(LOGERROR, "%s - out of memory getting listing (got %i)", __FUNCTION__, items.Size());
3263 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3268 bool CMusicDatabase::GetAlbumFromSong(int idSong, CAlbum &album)
3272 if (NULL == m_pDB.get()) return false;
3273 if (NULL == m_pDS.get()) return false;
3275 CStdString strSQL = PrepareSQL("select albumview.* from song join albumview on song.idAlbum = albumview.idAlbum where song.idSong='%i'", idSong);
3276 if (!m_pDS->query(strSQL.c_str())) return false;
3277 int iRowsFound = m_pDS->num_rows();
3278 if (iRowsFound != 1)
3284 album = GetAlbumFromDataset(m_pDS.get());
3292 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3297 bool CMusicDatabase::GetAlbumsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre /* = -1 */, int idArtist /* = -1 */, const Filter &filter /* = Filter() */, const SortDescription &sortDescription /* = SortDescription() */, bool countOnly /* = false */)
3299 CMusicDbUrl musicUrl;
3300 if (!musicUrl.FromString(strBaseDir))
3305 musicUrl.AddOption("genreid", idGenre);
3308 musicUrl.AddOption("artistid", idArtist);
3310 return GetAlbumsByWhere(musicUrl.ToString(), filter, items, sortDescription, countOnly);
3313 bool CMusicDatabase::GetAlbumsByWhere(const CStdString &baseDir, const Filter &filter, CFileItemList &items, const SortDescription &sortDescription /* = SortDescription() */, bool countOnly /* = false */)
3315 if (m_pDB.get() == NULL || m_pDS.get() == NULL)
3322 CStdString strSQL = "SELECT %s FROM albumview ";
3324 Filter extFilter = filter;
3325 CMusicDbUrl musicUrl;
3326 SortDescription sorting = sortDescription;
3327 if (!musicUrl.FromString(baseDir) || !GetFilter(musicUrl, extFilter, sorting))
3330 // if there are extra WHERE conditions we might need access
3331 // to songview for these conditions
3332 if (extFilter.where.find("songview") != string::npos)
3334 extFilter.AppendJoin("JOIN songview ON songview.idAlbum = albumview.idAlbum");
3335 extFilter.AppendGroup("albumview.idAlbum");
3338 CStdString strSQLExtra;
3339 if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
3342 // Apply the limiting directly here if there's no special sorting but limiting
3343 if (extFilter.limit.empty() &&
3344 sortDescription.sortBy == SortByNone &&
3345 (sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
3347 total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
3348 strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
3351 strSQL = PrepareSQL(strSQL, !filter.fields.empty() && filter.fields.compare("*") != 0 ? filter.fields.c_str() : "albumview.*") + strSQLExtra;
3353 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
3355 unsigned int time = XbmcThreads::SystemClockMillis();
3356 if (!m_pDS->query(strSQL.c_str()))
3358 CLog::Log(LOGDEBUG, "%s - query took %i ms",
3359 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
3361 int iRowsFound = m_pDS->num_rows();
3362 if (iRowsFound <= 0)
3368 // store the total value of items as a property
3369 if (total < iRowsFound)
3371 items.SetProperty("total", total);
3375 CFileItemPtr pItem(new CFileItem());
3376 pItem->SetProperty("total", total);
3383 DatabaseResults results;
3384 results.reserve(iRowsFound);
3385 if (!SortUtils::SortFromDataset(sortDescription, MediaTypeAlbum, m_pDS, results))
3388 // get data from returned rows
3389 items.Reserve(results.size());
3390 const dbiplus::query_data &data = m_pDS->get_result_set().records;
3391 for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
3393 unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
3394 const dbiplus::sql_record* const record = data.at(targetRow);
3398 CMusicDbUrl itemUrl = musicUrl;
3399 CStdString path = StringUtils::Format("%ld/", record->at(album_idAlbum).get_asInt());
3400 itemUrl.AppendPath(path);
3402 CFileItemPtr pItem(new CFileItem(itemUrl.ToString(), GetAlbumFromDataset(record)));
3403 pItem->SetIconImage("DefaultAlbumCover.png");
3409 CLog::Log(LOGERROR, "%s - out of memory getting listing (got %i)", __FUNCTION__, items.Size());
3420 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, filter.where.c_str());
3425 bool CMusicDatabase::GetSongsByWhere(const CStdString &baseDir, const Filter &filter, CFileItemList &items, const SortDescription &sortDescription /* = SortDescription() */)
3427 if (m_pDB.get() == NULL || m_pDS.get() == NULL)
3432 unsigned int time = XbmcThreads::SystemClockMillis();
3435 CStdString strSQL = "SELECT %s FROM songview ";
3437 Filter extFilter = filter;
3438 CMusicDbUrl musicUrl;
3439 SortDescription sorting = sortDescription;
3440 if (!musicUrl.FromString(baseDir) || !GetFilter(musicUrl, extFilter, sorting))
3443 // if there are extra WHERE conditions we might need access
3444 // to songview for these conditions
3445 if (extFilter.where.find("albumview") != string::npos)
3447 extFilter.AppendJoin("JOIN albumview ON albumview.idAlbum = songview.idAlbum");
3448 extFilter.AppendGroup("songview.idSong");
3451 CStdString strSQLExtra;
3452 if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
3455 // Apply the limiting directly here if there's no special sorting but limiting
3456 if (extFilter.limit.empty() &&
3457 sortDescription.sortBy == SortByNone &&
3458 (sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
3460 total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
3461 strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
3464 strSQL = PrepareSQL(strSQL, !filter.fields.empty() && filter.fields.compare("*") != 0 ? filter.fields.c_str() : "songview.*") + strSQLExtra;
3466 CLog::Log(LOGDEBUG, "%s query = %s", __FUNCTION__, strSQL.c_str());
3468 if (!m_pDS->query(strSQL.c_str()))
3471 int iRowsFound = m_pDS->num_rows();
3472 if (iRowsFound == 0)
3478 // store the total value of items as a property
3479 if (total < iRowsFound)
3481 items.SetProperty("total", total);
3483 DatabaseResults results;
3484 results.reserve(iRowsFound);
3485 if (!SortUtils::SortFromDataset(sortDescription, MediaTypeSong, m_pDS, results))
3488 // get data from returned rows
3489 items.Reserve(results.size());
3490 const dbiplus::query_data &data = m_pDS->get_result_set().records;
3492 for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
3494 unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
3495 const dbiplus::sql_record* const record = data.at(targetRow);
3499 CFileItemPtr item(new CFileItem);
3500 GetFileItemFromDataset(record, item.get(), musicUrl);
3501 // HACK for sorting by database returned order
3502 item->m_iprogramCount = ++count;
3508 CLog::Log(LOGERROR, "%s: out of memory loading query: %s", __FUNCTION__, filter.where.c_str());
3509 return (items.Size() > 0);
3515 CLog::Log(LOGDEBUG, "%s(%s) - took %d ms", __FUNCTION__, filter.where.c_str(), XbmcThreads::SystemClockMillis() - time);
3522 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
3527 bool CMusicDatabase::GetSongsByYear(const CStdString& baseDir, CFileItemList& items, int year)
3529 CMusicDbUrl musicUrl;
3530 if (!musicUrl.FromString(baseDir))
3533 musicUrl.AddOption("year", year);
3536 return GetSongsByWhere(baseDir, filter, items);
3539 bool CMusicDatabase::GetSongsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, int idArtist, int idAlbum, const SortDescription &sortDescription /* = SortDescription() */)
3541 CMusicDbUrl musicUrl;
3542 if (!musicUrl.FromString(strBaseDir))
3546 musicUrl.AddOption("albumid", idAlbum);
3549 musicUrl.AddOption("genreid", idGenre);
3552 musicUrl.AddOption("artistid", idArtist);
3555 return GetSongsByWhere(musicUrl.ToString(), filter, items, sortDescription);
3558 void CMusicDatabase::UpdateTables(int version)
3562 int len = g_advancedSettings.m_musicItemSeparator.size() + 1;
3563 CStdString sql = PrepareSQL("UPDATE song SET strExtraArtists=SUBSTR(strExtraArtists,%i), strExtraGenres=SUBSTR(strExtraGenres,%i)", len, len);
3564 m_pDS->exec(sql.c_str());
3565 sql = PrepareSQL("UPDATE album SET strExtraArtists=SUBSTR(strExtraArtists,%i), strExtraGenres=SUBSTR(strExtraGenres,%i)", len, len);
3566 m_pDS->exec(sql.c_str());
3571 m_pDS->exec("CREATE TABLE album_artist ( idArtist integer, idAlbum integer, boolFeatured integer, iOrder integer )\n");
3572 m_pDS->exec("INSERT INTO album_artist (idArtist, idAlbum, boolFeatured, iOrder) SELECT idArtist, idAlbum, 1, iPosition FROM exartistalbum");
3573 m_pDS->exec("REPLACE INTO album_artist (idArtist, idAlbum, boolFeatured, iOrder) SELECT idArtist, idAlbum, 0, 0 FROM album");
3576 strSQL=PrepareSQL("SELECT album.idAlbum AS idAlbum, strExtraArtists,"
3577 " album.idArtist AS idArtist, strArtist FROM album "
3578 " LEFT OUTER JOIN artist ON album.idArtist=artist.idArtist");
3579 m_pDS->query(strSQL.c_str());
3582 while (!m_pDS->eof())
3585 album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
3586 album.artist.push_back(m_pDS->fv("strArtist").get_asString());
3587 if (!m_pDS->fv("strExtraArtists").get_asString().empty())
3589 std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv("strExtraArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
3590 album.artist.insert(album.artist.end(), extraArtists.begin(), extraArtists.end());
3592 albums.push_back(album);
3596 m_pDS->exec("CREATE TABLE album_new ( idAlbum integer primary key, strAlbum varchar(256), strArtists text, idGenre integer, strExtraGenres text, iYear integer, idThumb integer)");
3597 m_pDS->exec("INSERT INTO album_new ( idAlbum, strAlbum, idGenre, strExtraGenres, iYear, idThumb ) SELECT idAlbum, strAlbum, idGenre, strExtraGenres, iYear, idThumb FROM album");
3599 for (VECALBUMS::iterator it = albums.begin(); it != albums.end(); ++it)
3602 strSQL = PrepareSQL("UPDATE album_new SET strArtists='%s' WHERE idAlbum=%i", StringUtils::Join(it->artist, g_advancedSettings.m_musicItemSeparator).c_str(), it->idAlbum);
3603 m_pDS->exec(strSQL);
3606 m_pDS->exec("DROP TABLE album");
3607 m_pDS->exec("ALTER TABLE album_new RENAME TO album");
3608 m_pDS->exec("DROP TABLE IF EXISTS exartistalbum");
3613 m_pDS->exec("CREATE TABLE song_artist ( idArtist integer, idSong integer, boolFeatured integer, iOrder integer )\n");
3614 m_pDS->exec("INSERT INTO song_artist (idArtist, idSong, boolFeatured, iOrder) SELECT idArtist, idSong, 1, iPosition FROM exartistsong");
3615 m_pDS->exec("REPLACE INTO song_artist (idArtist, idSong, boolFeatured, iOrder) SELECT idArtist, idSong, 0, 0 FROM song");
3618 strSQL=PrepareSQL("SELECT song.idSong AS idSong, strExtraArtists,"
3619 " song.idArtist AS idArtist, strArtist FROM song "
3620 " LEFT OUTER JOIN artist ON song.idArtist=artist.idArtist");
3621 m_pDS->query(strSQL.c_str());
3624 while (!m_pDS->eof())
3627 song.idSong = m_pDS->fv("idSong").get_asInt();
3628 song.artist.push_back(m_pDS->fv("strArtist").get_asString());
3629 if (!m_pDS->fv("strExtraArtists").get_asString().empty())
3631 std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv("strExtraArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
3632 song.artist.insert(song.artist.end(), extraArtists.begin(), extraArtists.end());
3634 songs.push_back(song);
3638 m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, idGenre integer, strExtraGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, strMusicBrainzArtistID text, strMusicBrainzAlbumID text, strMusicBrainzAlbumArtistID text, strMusicBrainzTRMID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)");
3639 m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, idGenre, strExtraGenres, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment ) SELECT idSong, idAlbum, idPath, idGenre, strExtraGenres, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
3641 for (VECSONGS::iterator it = songs.begin(); it != songs.end(); ++it)
3644 strSQL = PrepareSQL("UPDATE song_new SET strArtists='%s' WHERE idSong=%i", StringUtils::Join(it->artist, g_advancedSettings.m_musicItemSeparator).c_str(), it->idSong);
3645 m_pDS->exec(strSQL);
3648 m_pDS->exec("DROP TABLE song");
3649 m_pDS->exec("ALTER TABLE song_new RENAME TO song");
3650 m_pDS->exec("DROP TABLE IF EXISTS exartistsong");
3655 m_pDS->exec("CREATE TABLE album_genre ( idGenre integer, idAlbum integer, iOrder integer )\n");
3656 m_pDS->exec("INSERT INTO album_genre ( idGenre, idAlbum, iOrder) SELECT idGenre, idAlbum, iPosition FROM exgenrealbum");
3657 m_pDS->exec("REPLACE INTO album_genre ( idGenre, idAlbum, iOrder) SELECT idGenre, idAlbum, 0 FROM album");
3660 strSQL=PrepareSQL("SELECT album.idAlbum AS idAlbum, strExtraGenres,"
3661 " album.idGenre AS idGenre, strGenre FROM album "
3662 " JOIN genre ON album.idGenre=genre.idGenre");
3663 m_pDS->query(strSQL.c_str());
3666 while (!m_pDS->eof())
3669 album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
3670 album.genre.push_back(m_pDS->fv("strGenre").get_asString());
3671 if (!m_pDS->fv("strExtraGenres").get_asString().empty())
3673 std::vector<std::string> extraGenres = StringUtils::Split(m_pDS->fv("strExtraGenres").get_asString(), g_advancedSettings.m_musicItemSeparator);
3674 album.genre.insert(album.genre.end(), extraGenres.begin(), extraGenres.end());
3676 albums.push_back(album);
3680 m_pDS->exec("CREATE TABLE album_new ( idAlbum integer primary key, strAlbum varchar(256), strArtists text, strGenres text, iYear integer, idThumb integer)");
3681 m_pDS->exec("INSERT INTO album_new ( idAlbum, strAlbum, strArtists, iYear, idThumb) SELECT idAlbum, strAlbum, strArtists, iYear, idThumb FROM album");
3683 for (VECALBUMS::iterator it = albums.begin(); it != albums.end(); ++it)
3686 strSQL = PrepareSQL("UPDATE album_new SET strGenres='%s' WHERE idAlbum=%i", StringUtils::Join(it->genre, g_advancedSettings.m_musicItemSeparator).c_str(), it->idAlbum);
3687 m_pDS->exec(strSQL);
3690 m_pDS->exec("DROP TABLE album");
3691 m_pDS->exec("ALTER TABLE album_new RENAME TO album");
3692 m_pDS->exec("DROP TABLE IF EXISTS exgenrealbum");
3697 m_pDS->exec("CREATE TABLE song_genre ( idGenre integer, idSong integer, iOrder integer )\n");
3698 m_pDS->exec("INSERT INTO song_genre ( idGenre, idSong, iOrder) SELECT idGenre, idSong, iPosition FROM exgenresong");
3699 m_pDS->exec("REPLACE INTO song_genre ( idGenre, idSong, iOrder) SELECT idGenre, idSong, 0 FROM song");
3702 strSQL=PrepareSQL("SELECT song.idSong AS idSong, strExtraGenres,"
3703 " song.idGenre AS idGenre, strGenre FROM song "
3704 " JOIN genre ON song.idGenre=genre.idGenre");
3705 m_pDS->query(strSQL.c_str());
3708 while (!m_pDS->eof())
3711 song.idSong = m_pDS->fv("idSong").get_asInt();
3712 song.genre.push_back(m_pDS->fv("strGenre").get_asString());
3713 if (!m_pDS->fv("strExtraGenres").get_asString().empty())
3715 std::vector<std::string> extraGenres = StringUtils::Split(m_pDS->fv("strExtraGenres").get_asString(), g_advancedSettings.m_musicItemSeparator);
3716 song.genre.insert(song.genre.end(), extraGenres.begin(), extraGenres.end());
3718 songs.push_back(song);
3722 m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, strGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, strMusicBrainzArtistID text, strMusicBrainzAlbumID text, strMusicBrainzAlbumArtistID text, strMusicBrainzTRMID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)\n");
3723 m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment) SELECT idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
3725 for (VECSONGS::iterator it = songs.begin(); it != songs.end(); ++it)
3728 strSQL = PrepareSQL("UPDATE song_new SET strGenres='%s' WHERE idSong=%i", StringUtils::Join(it->genre, g_advancedSettings.m_musicItemSeparator).c_str(), it->idSong);
3729 m_pDS->exec(strSQL);
3732 m_pDS->exec("DROP TABLE song");
3733 m_pDS->exec("ALTER TABLE song_new RENAME TO song");
3734 m_pDS->exec("DROP TABLE IF EXISTS exgenresong");
3739 m_pDS->exec("ALTER TABLE album ADD bCompilation integer not null default '0'");
3744 m_pDS->exec("CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT)");
3749 m_pDS->exec("DROP TABLE IF EXISTS thumb");
3751 CMediaSettings::Get().SetMusicNeedsUpdate(27);
3752 CSettings::Get().Save();
3756 { // update old art URLs
3757 m_pDS->query("select art_id,url from art where url like 'image://%%'");
3758 vector< pair<int, string> > art;
3759 while (!m_pDS->eof())
3761 art.push_back(make_pair(m_pDS->fv(0).get_asInt(), CURL(m_pDS->fv(1).get_asString()).Get()));
3765 for (vector< pair<int, string> >::iterator i = art.begin(); i != art.end(); ++i)
3766 m_pDS->exec(PrepareSQL("update art set url='%s' where art_id=%d", i->second.c_str(), i->first));
3769 { // update URL encoded paths
3770 m_pDS->query("select idSong, strFileName from song");
3771 vector< pair<int, string> > files;
3772 while (!m_pDS->eof())
3774 files.push_back(make_pair(m_pDS->fv(0).get_asInt(), m_pDS->fv(1).get_asString()));
3779 for (vector< pair<int, string> >::iterator i = files.begin(); i != files.end(); ++i)
3781 std::string filename = i->second;
3782 if (URIUtils::UpdateUrlEncoding(filename))
3783 m_pDS->exec(PrepareSQL("UPDATE song SET strFileName='%s' WHERE idSong=%d", filename.c_str(), i->first));
3789 m_pDS->exec("ALTER TABLE artist ADD strMusicBrainzArtistID text\n");
3790 m_pDS->exec("ALTER TABLE album ADD strMusicBrainzAlbumID text\n");
3791 m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, strGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)\n");
3792 m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment) SELECT idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
3794 m_pDS->exec("DROP TABLE song");
3795 m_pDS->exec("ALTER TABLE song_new RENAME TO song");
3797 m_pDS->exec("UPDATE song SET strMusicBrainzTrackID = NULL");
3802 m_pDS->exec("ALTER TABLE album_artist ADD strJoinPhrase text\n");
3803 m_pDS->exec("ALTER TABLE song_artist ADD strJoinPhrase text\n");
3804 CMediaSettings::Get().SetMusicNeedsUpdate(35);
3805 CSettings::Get().Save();
3810 // translate legacy musicdb:// paths
3811 if (m_pDS->query("SELECT strPath FROM content"))
3813 vector<string> contentPaths;
3814 while (!m_pDS->eof())
3816 contentPaths.push_back(m_pDS->fv(0).get_asString());
3821 for (vector<string>::const_iterator it = contentPaths.begin(); it != contentPaths.end(); it++)
3823 std::string originalPath = *it;
3824 std::string path = CLegacyPathTranslation::TranslateMusicDbPath(originalPath);
3825 m_pDS->exec(PrepareSQL("UPDATE content SET strPath='%s' WHERE strPath='%s'", path.c_str(), originalPath.c_str()).c_str());
3832 m_pDS->exec("CREATE TABLE album_new "
3833 "(idAlbum integer primary key, "
3834 " strAlbum varchar(256), strMusicBrainzAlbumID text, "
3835 " strArtists text, strGenres text, "
3836 " iYear integer, idThumb integer, "
3837 " bCompilation integer not null default '0', "
3838 " strMoods text, strStyles text, strThemes text, "
3839 " strReview text, strImage text, strLabel text, "
3841 " iRating integer, "
3842 " lastScraped varchar(20) default NULL, "
3843 " dateAdded varchar (20) default NULL)");
3844 m_pDS->exec("INSERT INTO album_new "
3846 " strAlbum, strMusicBrainzAlbumID, "
3847 " strArtists, strGenres, "
3850 " strMoods, strStyles, strThemes, "
3851 " strReview, strImage, strLabel, "
3856 " strAlbum, strMusicBrainzAlbumID, "
3857 " strArtists, strGenres, "
3858 " album.iYear, idThumb, "
3860 " strMoods, strStyles, strThemes, "
3861 " strReview, strImage, strLabel, "
3862 " strType, iRating "
3863 " FROM album LEFT JOIN albuminfo ON album.idAlbum = albuminfo.idAlbum");
3864 m_pDS->exec("UPDATE albuminfosong SET idAlbumInfo = (SELECT idAlbum FROM albuminfo WHERE albuminfo.idAlbumInfo = albuminfosong.idAlbumInfo)");
3865 m_pDS->exec(PrepareSQL("UPDATE album_new SET lastScraped='%s' WHERE idAlbum IN (SELECT idAlbum FROM albuminfo)", CDateTime::GetCurrentDateTime().GetAsDBDateTime().c_str()));
3866 m_pDS->exec("DROP TABLE album");
3867 m_pDS->exec("DROP TABLE albuminfo");
3868 m_pDS->exec("ALTER TABLE album_new RENAME TO album");
3872 m_pDS->exec("CREATE TABLE artist_new ( idArtist integer primary key, "
3873 " strArtist varchar(256), strMusicBrainzArtistID text, "
3874 " strBorn text, strFormed text, strGenres text, strMoods text, "
3875 " strStyles text, strInstruments text, strBiography text, "
3876 " strDied text, strDisbanded text, strYearsActive text, "
3877 " strImage text, strFanart text, "
3878 " lastScraped varchar(20) default NULL, "
3879 " dateAdded varchar (20) default NULL)");
3880 m_pDS->exec("INSERT INTO artist_new "
3881 "(idArtist, strArtist, strMusicBrainzArtistID, "
3882 " strBorn, strFormed, strGenres, strMoods, "
3883 " strStyles , strInstruments , strBiography , "
3884 " strDied, strDisbanded, strYearsActive, "
3885 " strImage, strFanart) "
3887 " artist.idArtist, "
3888 " strArtist, strMusicBrainzArtistID, "
3889 " strBorn, strFormed, strGenres, strMoods, "
3890 " strStyles, strInstruments, strBiography, "
3891 " strDied, strDisbanded, strYearsActive, "
3892 " strImage, strFanart "
3894 " LEFT JOIN artistinfo ON artist.idArtist = artistinfo.idArtist");
3895 m_pDS->exec(PrepareSQL("UPDATE artist_new SET lastScraped='%s' WHERE idArtist IN (SELECT idArtist FROM artistinfo)", CDateTime::GetCurrentDateTime().GetAsDBDateTime().c_str()));
3896 m_pDS->exec("DROP TABLE artist");
3897 m_pDS->exec("DROP TABLE artistinfo");
3898 m_pDS->exec("ALTER TABLE artist_new RENAME TO artist");
3902 m_pDS->exec("ALTER TABLE album_artist ADD strArtist text\n");
3903 m_pDS->exec("ALTER TABLE song_artist ADD strArtist text\n");
3905 map<int, string> artists;
3906 CStdString sql = "select idArtist,strArtist from artist";
3907 m_pDS->query(sql.c_str());
3908 while (!m_pDS->eof())
3910 m_pDS2->exec(PrepareSQL("UPDATE song_artist SET strArtist='%s' where idArtist=%i", m_pDS->fv(1).get_asString().c_str(), m_pDS->fv(0).get_asInt()));
3911 m_pDS2->exec(PrepareSQL("UPDATE album_artist SET strArtist='%s' where idArtist=%i", m_pDS->fv(1).get_asString().c_str(), m_pDS->fv(0).get_asInt()));
3914 // drop the last separator if more than one
3915 m_pDS->exec("UPDATE song_artist SET strJoinPhrase = '' WHERE 100*idSong+iOrder IN (SELECT id FROM (SELECT 100*idSong+max(iOrder) AS id FROM song_artist GROUP BY idSong) AS sub)");
3916 m_pDS->exec("UPDATE album_artist SET strJoinPhrase = '' WHERE 100*idAlbum+iOrder IN (SELECT id FROM (SELECT 100*idAlbum+max(iOrder) AS id FROM album_artist GROUP BY idAlbum) AS sub)");
3920 int CMusicDatabase::GetSchemaVersion() const
3925 unsigned int CMusicDatabase::GetSongIDs(const Filter &filter, vector<pair<int,int> > &songIDs)
3929 if (NULL == m_pDB.get()) return 0;
3930 if (NULL == m_pDS.get()) return 0;
3932 CStdString strSQL = "select idSong from songview ";
3933 if (!CDatabase::BuildSQL(strSQL, filter, strSQL))
3936 if (!m_pDS->query(strSQL.c_str())) return 0;
3938 if (m_pDS->num_rows() == 0)
3943 songIDs.reserve(m_pDS->num_rows());
3944 while (!m_pDS->eof())
3946 songIDs.push_back(make_pair<int,int>(1,m_pDS->fv(song_idSong).get_asInt()));
3950 return songIDs.size();
3954 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
3959 int CMusicDatabase::GetSongsCount(const Filter &filter)
3963 if (NULL == m_pDB.get()) return 0;
3964 if (NULL == m_pDS.get()) return 0;
3966 CStdString strSQL = "select count(idSong) as NumSongs from songview ";
3967 if (!CDatabase::BuildSQL(strSQL, filter, strSQL))
3970 if (!m_pDS->query(strSQL.c_str())) return false;
3971 if (m_pDS->num_rows() == 0)
3977 int iNumSongs = m_pDS->fv("NumSongs").get_asInt();
3984 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
3989 bool CMusicDatabase::GetAlbumPath(int idAlbum, CStdString& path)
3993 if (NULL == m_pDB.get()) return false;
3994 if (NULL == m_pDS2.get()) return false;
3998 CStdString strSQL=PrepareSQL("select strPath from song join path on song.idPath = path.idPath where song.idAlbum=%ld", idAlbum);
3999 if (!m_pDS2->query(strSQL.c_str())) return false;
4000 int iRowsFound = m_pDS2->num_rows();
4001 if (iRowsFound == 0)
4007 // if this returns more than one path, we just grab the first one. It's just for determining where to obtain + place
4008 // a local thumbnail
4009 path = m_pDS2->fv("strPath").get_asString();
4011 m_pDS2->close(); // cleanup recordset data
4016 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
4022 bool CMusicDatabase::SaveAlbumThumb(int idAlbum, const CStdString& strThumb)
4024 SetArtForItem(idAlbum, "album", "thumb", strThumb);
4025 // TODO: We should prompt the user to update the art for songs
4026 CStdString sql = PrepareSQL("UPDATE art"
4028 " WHERE media_type='song'"
4031 " (SELECT idSong FROM song WHERE idAlbum=%ld)", idAlbum);
4036 bool CMusicDatabase::GetArtistPath(int idArtist, CStdString &basePath)
4040 if (NULL == m_pDB.get()) return false;
4041 if (NULL == m_pDS2.get()) return false;
4043 // find all albums from this artist, and all the paths to the songs from those albums
4044 CStdString strSQL=PrepareSQL("SELECT strPath"
4045 " FROM album_artist"
4047 " ON album_artist.idAlbum = song.idAlbum"
4049 " ON song.idPath = path.idPath"
4050 " WHERE album_artist.idArtist = %i"
4051 " GROUP BY song.idPath", idArtist);
4054 if (!m_pDS2->query(strSQL.c_str())) return false;
4055 int iRowsFound = m_pDS2->num_rows();
4056 if (iRowsFound == 0)
4062 // special case for single path - assume that we're in an artist/album/songs filesystem
4063 if (iRowsFound == 1)
4065 URIUtils::GetParentPath(m_pDS2->fv("strPath").get_asString(), basePath);
4070 // find the common path (if any) to these albums
4072 while (!m_pDS2->eof())
4074 CStdString path = m_pDS2->fv("strPath").get_asString();
4075 if (basePath.empty())
4078 URIUtils::GetCommonPath(basePath,path);
4090 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4095 int CMusicDatabase::GetArtistByName(const CStdString& strArtist)
4099 if (NULL == m_pDB.get()) return false;
4100 if (NULL == m_pDS.get()) return false;
4102 CStdString strSQL=PrepareSQL("select idArtist from artist where artist.strArtist like '%s'", strArtist.c_str());
4105 if (!m_pDS->query(strSQL.c_str())) return false;
4106 int iRowsFound = m_pDS->num_rows();
4107 if (iRowsFound != 1)
4112 int lResult = m_pDS->fv("artist.idArtist").get_asInt();
4118 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4123 int CMusicDatabase::GetAlbumByName(const CStdString& strAlbum, const CStdString& strArtist)
4127 if (NULL == m_pDB.get()) return false;
4128 if (NULL == m_pDS.get()) return false;
4131 if (strArtist.empty())
4132 strSQL=PrepareSQL("SELECT idAlbum FROM album WHERE album.strAlbum LIKE '%s'", strAlbum.c_str());
4134 strSQL=PrepareSQL("SELECT album.idAlbum FROM album WHERE album.strAlbum LIKE '%s' AND album.strArtists LIKE '%s'", strAlbum.c_str(),strArtist.c_str());
4136 if (!m_pDS->query(strSQL.c_str())) return false;
4137 int iRowsFound = m_pDS->num_rows();
4138 if (iRowsFound != 1)
4143 return m_pDS->fv("album.idAlbum").get_asInt();
4147 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4152 int CMusicDatabase::GetAlbumByName(const CStdString& strAlbum, const std::vector<std::string>& artist)
4154 return GetAlbumByName(strAlbum, StringUtils::Join(artist, g_advancedSettings.m_musicItemSeparator));
4157 CStdString CMusicDatabase::GetGenreById(int id)
4159 return GetSingleValue("genre", "strGenre", PrepareSQL("idGenre=%i", id));
4162 CStdString CMusicDatabase::GetArtistById(int id)
4164 return GetSingleValue("artist", "strArtist", PrepareSQL("idArtist=%i", id));
4167 CStdString CMusicDatabase::GetAlbumById(int id)
4169 return GetSingleValue("album", "strAlbum", PrepareSQL("idAlbum=%i", id));
4172 int CMusicDatabase::GetGenreByName(const CStdString& strGenre)
4176 if (NULL == m_pDB.get()) return false;
4177 if (NULL == m_pDS.get()) return false;
4180 strSQL=PrepareSQL("select idGenre from genre where genre.strGenre like '%s'", strGenre.c_str());
4182 if (!m_pDS->query(strSQL.c_str())) return false;
4183 int iRowsFound = m_pDS->num_rows();
4184 if (iRowsFound != 1)
4189 return m_pDS->fv("genre.idGenre").get_asInt();
4193 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4198 bool CMusicDatabase::GetRandomSong(CFileItem* item, int& idSong, const Filter &filter)
4204 if (NULL == m_pDB.get()) return false;
4205 if (NULL == m_pDS.get()) return false;
4207 // We don't use PrepareSQL here, as the WHERE clause is already formatted
4208 CStdString strSQL = PrepareSQL("select %s from songview ", !filter.fields.empty() ? filter.fields.c_str() : "*");
4209 Filter extFilter = filter;
4210 extFilter.AppendOrder(PrepareSQL("RANDOM()"));
4211 extFilter.limit = "1";
4213 if (!CDatabase::BuildSQL(strSQL, extFilter, strSQL))
4216 CLog::Log(LOGDEBUG, "%s query = %s", __FUNCTION__, strSQL.c_str());
4218 if (!m_pDS->query(strSQL.c_str()))
4220 int iRowsFound = m_pDS->num_rows();
4221 if (iRowsFound != 1)
4226 GetFileItemFromDataset(item, CMusicDbUrl());
4227 idSong = m_pDS->fv("songview.idSong").get_asInt();
4233 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
4238 bool CMusicDatabase::GetCompilationAlbums(const CStdString& strBaseDir, CFileItemList& items)
4240 CMusicDbUrl musicUrl;
4241 if (!musicUrl.FromString(strBaseDir))
4244 musicUrl.AddOption("compilation", true);
4247 return GetAlbumsByWhere(musicUrl.ToString(), filter, items);
4250 bool CMusicDatabase::GetCompilationSongs(const CStdString& strBaseDir, CFileItemList& items)
4252 CMusicDbUrl musicUrl;
4253 if (!musicUrl.FromString(strBaseDir))
4256 musicUrl.AddOption("compilation", true);
4259 return GetSongsByWhere(musicUrl.ToString(), filter, items);
4262 int CMusicDatabase::GetCompilationAlbumsCount()
4264 return strtol(GetSingleValue("album", "count(idAlbum)", "bCompilation = 1"), NULL, 10);
4267 void CMusicDatabase::SplitString(const CStdString &multiString, vector<string> &vecStrings, CStdString &extraStrings)
4269 vecStrings = StringUtils::Split(multiString, g_advancedSettings.m_musicItemSeparator);
4270 for (unsigned int i = 1; i < vecStrings.size(); i++)
4271 extraStrings += g_advancedSettings.m_musicItemSeparator + CStdString(vecStrings[i]);
4274 bool CMusicDatabase::SetPathHash(const CStdString &path, const CStdString &hash)
4278 if (NULL == m_pDB.get()) return false;
4279 if (NULL == m_pDS.get()) return false;
4282 { // this is an empty folder - we need only add it to the path table
4283 // if the path actually exists
4284 if (!CDirectory::Exists(path))
4287 int idPath = AddPath(path);
4288 if (idPath < 0) return false;
4290 CStdString strSQL=PrepareSQL("update path set strHash='%s' where idPath=%ld", hash.c_str(), idPath);
4291 m_pDS->exec(strSQL.c_str());
4297 CLog::Log(LOGERROR, "%s (%s, %s) failed", __FUNCTION__, path.c_str(), hash.c_str());
4303 bool CMusicDatabase::GetPathHash(const CStdString &path, CStdString &hash)
4307 if (NULL == m_pDB.get()) return false;
4308 if (NULL == m_pDS.get()) return false;
4310 CStdString strSQL=PrepareSQL("select strHash from path where strPath='%s'", path.c_str());
4311 m_pDS->query(strSQL.c_str());
4312 if (m_pDS->num_rows() == 0)
4314 hash = m_pDS->fv("strHash").get_asString();
4319 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, path.c_str());
4325 bool CMusicDatabase::RemoveSongsFromPath(const CStdString &path1, MAPSONGS& songs, bool exact)
4327 // We need to remove all songs from this path, as their tags are going
4328 // to be re-read. We need to remove all songs from the song table + all links to them
4329 // from the song link tables (as otherwise if a song is added back
4330 // to the table with the same idSong, these tables can't be cleaned up properly later)
4332 // TODO: SQLite probably doesn't allow this, but can we rely on that??
4334 // We don't need to remove orphaned albums at this point as in AddAlbum() we check
4335 // first whether the album has already been read during this scan, and if it hasn't
4336 // we check whether it's in the table and update accordingly at that point, removing the entries from
4337 // the album link tables. The only failure point for this is albums
4338 // that span multiple folders, where just the files in one folder have been changed. In this case
4339 // any linked fields that are only in the files that haven't changed will be removed. Clearly
4340 // the primary albumartist still matches (as that's what we looked up based on) so is this really
4341 // an issue? I don't think it is, as those artists will still have links to the album via the songs
4342 // which is generally what we rely on, so the only failure point is albumartist lookup. In this
4343 // case, it will return only things in the album_artist table from the newly updated songs (and
4344 // only if they have additional artists). I think the effect of this is minimal at best, as ALL
4345 // songs in the album should have the same albumartist!
4347 // we also remove the path at this point as it will be added later on if the
4348 // path still exists.
4349 // After scanning we then remove the orphaned artists, genres and thumbs.
4351 // Note: when used to remove all songs from a path and its subpath (exact=false), this
4352 // does miss archived songs.
4353 CStdString path(path1);
4356 if (!URIUtils::HasSlashAtEnd(path))
4357 URIUtils::AddSlashAtEnd(path);
4359 if (NULL == m_pDB.get()) return false;
4360 if (NULL == m_pDS.get()) return false;
4364 where = PrepareSQL(" where strPath='%s'", path.c_str());
4366 where = PrepareSQL(" where SUBSTR(strPath,1,%i)='%s'", StringUtils::utf8_strlen(path.c_str()), path.c_str());
4367 CStdString sql = "select * from songview" + where;
4368 if (!m_pDS->query(sql.c_str())) return false;
4369 int iRowsFound = m_pDS->num_rows();
4372 std::vector<std::string> songIds;
4373 while (!m_pDS->eof())
4375 CSong song = GetSongFromDataset();
4376 song.strThumb = GetArtForItem(song.idSong, "song", "thumb");
4377 songs.insert(make_pair(song.strFileName, song));
4378 songIds.push_back(PrepareSQL("%i", song.idSong));
4383 //TODO: move this below the m_pDS->exec block, once UPnP doesn't rely on this anymore
4384 for (MAPSONGS::iterator songit = songs.begin(); songit != songs.end(); ++songit)
4385 AnnounceRemove("song", songit->second.idSong);
4387 // and delete all songs, and anything linked to them
4388 sql = "delete from song where idSong in (" + StringUtils::Join(songIds, ",") + ")";
4389 m_pDS->exec(sql.c_str());
4391 // and remove the path as well (it'll be re-added later on with the new hash if it's non-empty)
4392 sql = "delete from path" + where;
4393 m_pDS->exec(sql.c_str());
4394 return iRowsFound > 0;
4398 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, path.c_str());
4403 bool CMusicDatabase::GetPaths(set<string> &paths)
4407 if (NULL == m_pDB.get()) return false;
4408 if (NULL == m_pDS.get()) return false;
4413 if (!m_pDS->query("select strPath from path")) return false;
4414 int iRowsFound = m_pDS->num_rows();
4415 if (iRowsFound == 0)
4420 while (!m_pDS->eof())
4422 paths.insert(m_pDS->fv("strPath").get_asString());
4430 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4435 bool CMusicDatabase::SetSongRating(const CStdString &filePath, char rating)
4439 if (filePath.empty()) return false;
4440 if (NULL == m_pDB.get()) return false;
4441 if (NULL == m_pDS.get()) return false;
4443 int songID = GetSongIDFromPath(filePath);
4444 if (-1 == songID) return false;
4446 CStdString sql = PrepareSQL("update song set rating='%c' where idSong = %i", rating, songID);
4447 m_pDS->exec(sql.c_str());
4452 CLog::Log(LOGERROR, "%s (%s,%c) failed", __FUNCTION__, filePath.c_str(), rating);
4457 int CMusicDatabase::GetSongIDFromPath(const CStdString &filePath)
4459 // grab the where string to identify the song id
4461 if (url.GetProtocol()=="musicdb")
4463 CStdString strFile=URIUtils::GetFileName(filePath);
4464 URIUtils::RemoveExtension(strFile);
4465 return atol(strFile.c_str());
4470 if (NULL == m_pDB.get()) return -1;
4471 if (NULL == m_pDS.get()) return -1;
4473 CStdString strPath, strFileName;
4474 URIUtils::Split(filePath, strPath, strFileName);
4475 URIUtils::AddSlashAtEnd(strPath);
4476 DWORD crc = ComputeCRC(strFileName);
4478 CStdString sql = PrepareSQL("select idSong from song join path on song.idPath = path.idPath where song.dwFileNameCRC='%ul'and path.strPath='%s'", crc, strPath.c_str());
4479 if (!m_pDS->query(sql.c_str())) return -1;
4481 if (m_pDS->num_rows() == 0)
4487 int songID = m_pDS->fv("idSong").get_asInt();
4493 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, filePath.c_str());
4498 bool CMusicDatabase::CommitTransaction()
4500 if (CDatabase::CommitTransaction())
4501 { // number of items in the db has likely changed, so reset the infomanager cache
4502 g_infoManager.SetLibraryBool(LIBRARY_HAS_MUSIC, GetSongsCount() > 0);
4508 bool CMusicDatabase::SetScraperForPath(const CStdString& strPath, const ADDON::ScraperPtr& scraper)
4512 if (NULL == m_pDB.get()) return false;
4513 if (NULL == m_pDS.get()) return false;
4515 // wipe old settings
4516 CStdString strSQL = PrepareSQL("delete from content where strPath='%s'",strPath.c_str());
4517 m_pDS->exec(strSQL.c_str());
4519 // insert new settings
4520 strSQL = PrepareSQL("insert into content (strPath, strScraperPath, strContent, strSettings) values ('%s','%s','%s','%s')",
4521 strPath.c_str(), scraper->ID().c_str(), ADDON::TranslateContent(scraper->Content()).c_str(), scraper->GetPathSettings().c_str());
4522 m_pDS->exec(strSQL.c_str());
4528 CLog::Log(LOGERROR, "%s - (%s) failed", __FUNCTION__, strPath.c_str());
4533 bool CMusicDatabase::GetScraperForPath(const CStdString& strPath, ADDON::ScraperPtr& info, const ADDON::TYPE &type)
4537 if (NULL == m_pDB.get()) return false;
4538 if (NULL == m_pDS.get()) return false;
4540 CStdString strSQL = PrepareSQL("select * from content where strPath='%s'",strPath.c_str());
4541 m_pDS->query(strSQL.c_str());
4542 if (m_pDS->eof()) // no info set for path - fallback logic commencing
4544 CQueryParams params;
4545 CDirectoryNode::GetDatabaseInfo(strPath, params);
4546 if (params.GetGenreId() != -1) // check genre
4548 strSQL = PrepareSQL("select * from content where strPath='musicdb://genres/%i/'",params.GetGenreId());
4549 m_pDS->query(strSQL.c_str());
4551 if (m_pDS->eof() && params.GetAlbumId() != -1) // check album
4553 strSQL = PrepareSQL("select * from content where strPath='musicdb://albums/%i/'",params.GetAlbumId());
4554 m_pDS->query(strSQL.c_str());
4555 if (m_pDS->eof()) // general albums setting
4557 strSQL = PrepareSQL("select * from content where strPath='musicdb://albums/'");
4558 m_pDS->query(strSQL.c_str());
4561 if (m_pDS->eof() && params.GetArtistId() != -1) // check artist
4563 strSQL = PrepareSQL("select * from content where strPath='musicdb://artists/%i/'",params.GetArtistId());
4564 m_pDS->query(strSQL.c_str());
4566 if (m_pDS->eof()) // general artist setting
4568 strSQL = PrepareSQL("select * from content where strPath='musicdb://artists/'");
4569 m_pDS->query(strSQL.c_str());
4575 { // try and ascertain scraper for this path
4576 CONTENT_TYPE content = ADDON::TranslateContent(m_pDS->fv("content.strContent").get_asString());
4577 CStdString scraperUUID = m_pDS->fv("content.strScraperPath").get_asString();
4579 if (content != CONTENT_NONE)
4580 { // content set, use pre configured or default scraper
4581 ADDON::AddonPtr addon;
4582 if (!scraperUUID.empty() && ADDON::CAddonMgr::Get().GetAddon(scraperUUID, addon) && addon)
4584 info = boost::dynamic_pointer_cast<ADDON::CScraper>(addon->Clone());
4587 // store this path's settings
4588 info->SetPathSettings(content, m_pDS->fv("content.strSettings").get_asString());
4592 { // use default scraper of the requested type
4593 ADDON::AddonPtr defaultScraper;
4594 if (ADDON::CAddonMgr::Get().GetDefault(type, defaultScraper))
4596 info = boost::dynamic_pointer_cast<ADDON::CScraper>(defaultScraper->Clone());
4603 { // use default music scraper instead
4604 ADDON::AddonPtr addon;
4605 if(ADDON::CAddonMgr::Get().GetDefault(type, addon))
4607 info = boost::dynamic_pointer_cast<ADDON::CScraper>(addon);
4618 CLog::Log(LOGERROR, "%s -(%s) failed", __FUNCTION__, strPath.c_str());
4623 bool CMusicDatabase::ScraperInUse(const CStdString &scraperID) const
4627 if (NULL == m_pDB.get()) return false;
4628 if (NULL == m_pDS.get()) return false;
4630 CStdString sql = PrepareSQL("select count(1) from content where strScraperPath='%s'",scraperID.c_str());
4631 if (!m_pDS->query(sql.c_str()) || m_pDS->num_rows() == 0)
4633 bool found = m_pDS->fv(0).get_asInt() > 0;
4639 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, scraperID.c_str());
4644 bool CMusicDatabase::GetItems(const CStdString &strBaseDir, CFileItemList &items, const Filter &filter /* = Filter() */, const SortDescription &sortDescription /* = SortDescription() */)
4646 CMusicDbUrl musicUrl;
4647 if (!musicUrl.FromString(strBaseDir))
4650 return GetItems(strBaseDir, musicUrl.GetType(), items, filter, sortDescription);
4653 bool CMusicDatabase::GetItems(const CStdString &strBaseDir, const CStdString &itemType, CFileItemList &items, const Filter &filter /* = Filter() */, const SortDescription &sortDescription /* = SortDescription() */)
4655 if (itemType.Equals("genres"))
4656 return GetGenresNav(strBaseDir, items, filter);
4657 else if (itemType.Equals("years"))
4658 return GetYearsNav(strBaseDir, items, filter);
4659 else if (itemType.Equals("artists"))
4660 return GetArtistsNav(strBaseDir, items, !CSettings::Get().GetBool("musiclibrary.showcompilationartists"), -1, -1, -1, filter, sortDescription);
4661 else if (itemType.Equals("albums"))
4662 return GetAlbumsByWhere(strBaseDir, filter, items, sortDescription);
4663 else if (itemType.Equals("songs"))
4664 return GetSongsByWhere(strBaseDir, filter, items, sortDescription);
4669 CStdString CMusicDatabase::GetItemById(const CStdString &itemType, int id)
4671 if (itemType.Equals("genres"))
4672 return GetGenreById(id);
4673 else if (itemType.Equals("years"))
4674 return StringUtils::Format("%d", id);
4675 else if (itemType.Equals("artists"))
4676 return GetArtistById(id);
4677 else if (itemType.Equals("albums"))
4678 return GetAlbumById(id);
4683 void CMusicDatabase::ExportToXML(const CStdString &xmlFile, bool singleFiles, bool images, bool overwrite)
4687 if (NULL == m_pDB.get()) return;
4688 if (NULL == m_pDS.get()) return;
4689 if (NULL == m_pDS2.get()) return;
4692 vector<int> albumIds;
4693 CStdString sql = "select idAlbum FROM album WHERE lastScraped IS NOT NULL";
4694 m_pDS->query(sql.c_str());
4696 int total = m_pDS->num_rows();
4699 albumIds.reserve(total);
4700 while (!m_pDS->eof())
4702 albumIds.push_back(m_pDS->fv("idAlbum").get_asInt());
4707 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4710 progress->SetHeading(20196);
4711 progress->SetLine(0, 650);
4712 progress->SetLine(1, "");
4713 progress->SetLine(2, "");
4714 progress->SetPercentage(0);
4715 progress->StartModal();
4716 progress->ShowProgressBar(true);
4719 // create our xml document
4720 CXBMCTinyXML xmlDoc;
4721 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4722 xmlDoc.InsertEndChild(decl);
4723 TiXmlNode *pMain = NULL;
4728 TiXmlElement xmlMainElement("musicdb");
4729 pMain = xmlDoc.InsertEndChild(xmlMainElement);
4731 for (vector<int>::iterator albumId = albumIds.begin(); albumId != albumIds.end(); ++albumId)
4734 GetAlbum(*albumId, album);
4736 GetAlbumPath(*albumId, strPath);
4737 album.Save(pMain, "album", strPath);
4740 if (!CDirectory::Exists(strPath))
4741 CLog::Log(LOGDEBUG, "%s - Not exporting item %s as it does not exist", __FUNCTION__, strPath.c_str());
4744 CStdString nfoFile = URIUtils::AddFileToFolder(strPath, "album.nfo");
4745 if (overwrite || !CFile::Exists(nfoFile))
4747 if (!xmlDoc.SaveFile(nfoFile))
4748 CLog::Log(LOGERROR, "%s: Album nfo export failed! ('%s')", __FUNCTION__, nfoFile.c_str());
4753 string thumb = GetArtForItem(album.idAlbum, "album", "thumb");
4754 if (!thumb.empty() && (overwrite || !CFile::Exists(URIUtils::AddFileToFolder(strPath,"folder.jpg"))))
4755 CTextureCache::Get().Export(thumb, URIUtils::AddFileToFolder(strPath,"folder.jpg"));
4758 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4759 xmlDoc.InsertEndChild(decl);
4763 if ((current % 50) == 0 && progress)
4765 progress->SetLine(1, album.strAlbum);
4766 progress->SetPercentage(current * 100 / total);
4767 progress->Progress();
4768 if (progress->IsCanceled())
4779 vector<int> artistIds;
4780 CStdString artistSQL = "SELECT idArtist FROM artist where lastScraped IS NOT NULL";
4781 m_pDS->query(artistSQL.c_str());
4782 total = m_pDS->num_rows();
4784 artistIds.reserve(total);
4785 while (!m_pDS->eof())
4787 artistIds.push_back(m_pDS->fv("idArtist").get_asInt());
4792 for (vector<int>::iterator artistId = artistIds.begin(); artistId != artistIds.end(); ++artistId)
4795 GetArtist(*artistId, artist);
4797 GetArtistPath(artist.idArtist,strPath);
4798 artist.Save(pMain, "artist", strPath);
4800 map<string, string> artwork;
4801 if (GetArtForItem(artist.idArtist, "artist", artwork) && !singleFiles)
4802 { // append to the XML
4803 TiXmlElement additionalNode("art");
4804 for (map<string, string>::const_iterator i = artwork.begin(); i != artwork.end(); ++i)
4805 XMLUtils::SetString(&additionalNode, i->first.c_str(), i->second);
4806 pMain->LastChild()->InsertEndChild(additionalNode);
4810 if (!CDirectory::Exists(strPath))
4811 CLog::Log(LOGDEBUG, "%s - Not exporting item %s as it does not exist", __FUNCTION__, strPath.c_str());
4814 CStdString nfoFile = URIUtils::AddFileToFolder(strPath, "artist.nfo");
4815 if (overwrite || !CFile::Exists(nfoFile))
4817 if (!xmlDoc.SaveFile(nfoFile))
4818 CLog::Log(LOGERROR, "%s: Artist nfo export failed! ('%s')", __FUNCTION__, nfoFile.c_str());
4821 if (images && !artwork.empty())
4823 CStdString savedThumb = URIUtils::AddFileToFolder(strPath,"folder.jpg");
4824 CStdString savedFanart = URIUtils::AddFileToFolder(strPath,"fanart.jpg");
4825 if (artwork.find("thumb") != artwork.end() && (overwrite || !CFile::Exists(savedThumb)))
4826 CTextureCache::Get().Export(artwork["thumb"], savedThumb);
4827 if (artwork.find("fanart") != artwork.end() && (overwrite || !CFile::Exists(savedFanart)))
4828 CTextureCache::Get().Export(artwork["fanart"], savedFanart);
4831 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4832 xmlDoc.InsertEndChild(decl);
4836 if ((current % 50) == 0 && progress)
4838 progress->SetLine(1, artist.strArtist);
4839 progress->SetPercentage(current * 100 / total);
4840 progress->Progress();
4841 if (progress->IsCanceled())
4854 xmlDoc.SaveFile(xmlFile);
4858 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4862 void CMusicDatabase::ImportFromXML(const CStdString &xmlFile)
4864 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4867 if (NULL == m_pDB.get()) return;
4868 if (NULL == m_pDS.get()) return;
4870 CXBMCTinyXML xmlDoc;
4871 if (!xmlDoc.LoadFile(xmlFile))
4874 TiXmlElement *root = xmlDoc.RootElement();
4879 progress->SetHeading(20197);
4880 progress->SetLine(0, 649);
4881 progress->SetLine(1, 330);
4882 progress->SetLine(2, "");
4883 progress->SetPercentage(0);
4884 progress->StartModal();
4885 progress->ShowProgressBar(true);
4888 TiXmlElement *entry = root->FirstChildElement();
4891 // first count the number of items...
4894 if (strnicmp(entry->Value(), "artist", 6)==0 ||
4895 strnicmp(entry->Value(), "album", 5)==0)
4897 entry = entry->NextSiblingElement();
4901 entry = root->FirstChildElement();
4904 CStdString strTitle;
4905 if (strnicmp(entry->Value(), "artist", 6) == 0)
4907 CArtist importedArtist;
4908 importedArtist.Load(entry);
4909 strTitle = importedArtist.strArtist;
4910 int idArtist = GetArtistByName(importedArtist.strArtist);
4914 GetArtist(idArtist, artist);
4915 artist.MergeScrapedArtist(importedArtist, true);
4916 UpdateArtist(artist);
4921 else if (strnicmp(entry->Value(), "album", 5) == 0)
4923 CAlbum importedAlbum;
4924 importedAlbum.Load(entry);
4925 strTitle = importedAlbum.strAlbum;
4926 int idAlbum = GetAlbumByName(importedAlbum.strAlbum, importedAlbum.artist);
4930 GetAlbum(idAlbum, album, true);
4931 album.MergeScrapedAlbum(importedAlbum, true);
4937 entry = entry ->NextSiblingElement();
4938 if (progress && total)
4940 progress->SetPercentage(current * 100 / total);
4941 progress->SetLine(2, strTitle);
4942 progress->Progress();
4943 if (progress->IsCanceled())
4946 RollbackTransaction();
4951 CommitTransaction();
4953 g_infoManager.ResetLibraryBools();
4957 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4958 RollbackTransaction();
4964 void CMusicDatabase::AddKaraokeData(int idSong, int iKaraokeNumber, DWORD crc)
4970 // If song.iKaraokeNumber is non-zero, we already have it in the database. Just replace the song ID.
4971 if (iKaraokeNumber > 0)
4973 CStdString strSQL = PrepareSQL("UPDATE karaokedata SET idSong=%i WHERE iKaraNumber=%i", idSong, iKaraokeNumber);
4974 m_pDS->exec(strSQL.c_str());
4978 // Get the maximum number allocated
4979 strSQL=PrepareSQL( "SELECT MAX(iKaraNumber) FROM karaokedata" );
4980 if (!m_pDS->query(strSQL.c_str())) return;
4982 int iKaraokeNumber = g_advancedSettings.m_karaokeStartIndex;
4984 if ( m_pDS->num_rows() == 1 )
4985 iKaraokeNumber = m_pDS->fv("MAX(iKaraNumber)").get_asInt() + 1;
4988 strSQL=PrepareSQL( "INSERT INTO karaokedata (iKaraNumber, idSong, iKaraDelay, strKaraEncoding, strKaralyrics, strKaraLyrFileCRC) "
4989 "VALUES( %i, %i, 0, NULL, NULL, '%ul' )", iKaraokeNumber, idSong, crc );
4991 m_pDS->exec(strSQL.c_str());
4995 CLog::Log(LOGERROR, "%s -(%i, %i) failed", __FUNCTION__, idSong, iKaraokeNumber);
4999 bool CMusicDatabase::GetSongByKaraokeNumber(int number, CSong & song)
5003 // Get info from karaoke db
5004 if (NULL == m_pDB.get()) return false;
5005 if (NULL == m_pDS.get()) return false;
5007 CStdString strSQL=PrepareSQL("SELECT * FROM karaokedata where iKaraNumber=%ld", number);
5009 if (!m_pDS->query(strSQL.c_str())) return false;
5010 if (m_pDS->num_rows() == 0)
5016 int idSong = m_pDS->fv("karaokedata.idSong").get_asInt();
5019 return GetSong( idSong, song );
5023 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, number);
5029 void CMusicDatabase::ExportKaraokeInfo(const CStdString & outFile, bool asHTML)
5033 if (NULL == m_pDB.get()) return;
5034 if (NULL == m_pDS.get()) return;
5036 // find all karaoke songs
5037 CStdString sql = "SELECT * FROM songview WHERE iKaraNumber > 0 ORDER BY strFileName";
5039 m_pDS->query(sql.c_str());
5041 int total = m_pDS->num_rows();
5050 // Write the document
5053 if ( !file.OpenForWrite( outFile, true ) )
5056 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
5059 progress->SetHeading(asHTML ? 22034 : 22035);
5060 progress->SetLine(0, 650);
5061 progress->SetLine(1, "");
5062 progress->SetLine(2, "");
5063 progress->SetPercentage(0);
5064 progress->StartModal();
5065 progress->ShowProgressBar(true);
5071 outdoc = "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></meta></head>\n"
5072 "<body>\n<table>\n";
5074 file.Write( outdoc, outdoc.size() );
5077 while (!m_pDS->eof())
5079 CSong song = GetSongFromDataset();
5080 CStdString songnum = StringUtils::Format("%06d", song.iKaraokeNumber);
5083 outdoc = "<tr><td>" + songnum + "</td><td>" + StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator) + "</td><td>" + song.strTitle + "</td></tr>\r\n";
5085 outdoc = songnum + "\t" + StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator) + "\t" + song.strTitle + "\t" + song.strFileName + "\r\n";
5087 file.Write( outdoc, outdoc.size() );
5089 if ((current % 50) == 0 && progress)
5091 progress->SetPercentage(current * 100 / total);
5092 progress->Progress();
5093 if (progress->IsCanceled())
5108 outdoc = "</table>\n</body>\n</html>\n";
5109 file.Write( outdoc, outdoc.size() );
5119 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
5123 void CMusicDatabase::ImportKaraokeInfo(const CStdString & inputFile)
5125 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
5129 if (NULL == m_pDB.get()) return;
5133 if ( !file.Open( inputFile ) )
5135 CLog::Log( LOGERROR, "Cannot open karaoke import file %s", inputFile.c_str() );
5139 unsigned int size = (unsigned int) file.GetLength();
5144 // Read the file into memory array
5145 std::vector<char> data( size + 1 );
5147 file.Seek( 0, SEEK_SET );
5149 // Read the whole file
5150 if ( file.Read( &data[0], size) != size )
5152 CLog::Log( LOGERROR, "Cannot read karaoke import file %s", inputFile.c_str() );
5157 data[ size ] = '\0';
5161 progress->SetHeading( 22036 );
5162 progress->SetLine(0, 649);
5163 progress->SetLine(1, "");
5164 progress->SetLine(2, "");
5165 progress->SetPercentage(0);
5166 progress->StartModal();
5167 progress->ShowProgressBar(true);
5170 if (NULL == m_pDS.get()) return;
5174 // A simple state machine to parse the file
5176 char * linestart = &data[0];
5177 unsigned int offset = 0, lastpercentage = 0;
5179 for ( char * p = &data[0]; *p; p++, offset++ )
5193 unsigned int tabs = 0;
5194 char * songpath, *artist = 0, *title = 0;
5195 for ( songpath = linestart; *songpath; songpath++ )
5197 if ( *songpath == '\t' )
5204 case 1: // the number end
5205 artist = songpath + 1;
5208 case 2: // the artist end
5209 title = songpath + 1;
5212 case 3: // the title end
5218 int num = atoi( linestart );
5219 if ( num <= 0 || tabs < 3 || *artist == '\0' || *title == '\0' )
5221 CLog::Log( LOGERROR, "Karaoke import: error in line %s", linestart );
5227 CStdString strSQL=PrepareSQL("select idSong from songview "
5228 "where strArtists like '%s' and strTitle like '%s'", artist, title );
5230 if ( !m_pDS->query(strSQL.c_str()) )
5232 RollbackTransaction();
5239 int iRowsFound = m_pDS->num_rows();
5240 if (iRowsFound == 0)
5242 CLog::Log( LOGERROR, "Karaoke import: song %s by %s #%d is not found in the database, skipped",
5243 title, artist, num );
5247 int lResult = m_pDS->fv(0).get_asInt();
5248 strSQL = PrepareSQL("UPDATE karaokedata SET iKaraNumber=%i WHERE idSong=%i", num, lResult );
5249 m_pDS->exec(strSQL.c_str());
5251 if ( progress && (offset * 100 / size) != lastpercentage )
5253 lastpercentage = offset * 100 / size;
5254 progress->SetPercentage( lastpercentage);
5255 progress->Progress();
5256 if ( progress->IsCanceled() )
5258 RollbackTransaction();
5267 CommitTransaction();
5268 CLog::Log( LOGNOTICE, "Karaoke import: file '%s' was imported successfully", inputFile.c_str() );
5272 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
5273 RollbackTransaction();
5280 bool CMusicDatabase::SetKaraokeSongDelay(int idSong, int delay)
5284 if (NULL == m_pDB.get()) return false;
5285 if (NULL == m_pDS.get()) return false;
5287 CStdString strSQL = PrepareSQL("UPDATE karaokedata SET iKaraDelay=%i WHERE idSong=%i", delay, idSong);
5288 m_pDS->exec(strSQL.c_str());
5294 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
5300 int CMusicDatabase::GetKaraokeSongsCount()
5304 if (NULL == m_pDB.get()) return 0;
5305 if (NULL == m_pDS.get()) return 0;
5307 if (!m_pDS->query( "select count(idSong) as NumSongs from karaokedata")) return 0;
5308 if (m_pDS->num_rows() == 0)
5314 int iNumSongs = m_pDS->fv("NumSongs").get_asInt();
5321 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
5326 void CMusicDatabase::SetPropertiesFromArtist(CFileItem& item, const CArtist& artist)
5328 item.SetProperty("artist_instrument", StringUtils::Join(artist.instruments, g_advancedSettings.m_musicItemSeparator));
5329 item.SetProperty("artist_instrument_array", artist.instruments);
5330 item.SetProperty("artist_style", StringUtils::Join(artist.styles, g_advancedSettings.m_musicItemSeparator));
5331 item.SetProperty("artist_style_array", artist.styles);
5332 item.SetProperty("artist_mood", StringUtils::Join(artist.moods, g_advancedSettings.m_musicItemSeparator));
5333 item.SetProperty("artist_mood_array", artist.moods);
5334 item.SetProperty("artist_born", artist.strBorn);
5335 item.SetProperty("artist_formed", artist.strFormed);
5336 item.SetProperty("artist_description", artist.strBiography);
5337 item.SetProperty("artist_genre", StringUtils::Join(artist.genre, g_advancedSettings.m_musicItemSeparator));
5338 item.SetProperty("artist_genre_array", artist.genre);
5339 item.SetProperty("artist_died", artist.strDied);
5340 item.SetProperty("artist_disbanded", artist.strDisbanded);
5341 item.SetProperty("artist_yearsactive", StringUtils::Join(artist.yearsActive, g_advancedSettings.m_musicItemSeparator));
5342 item.SetProperty("artist_yearsactive_array", artist.yearsActive);
5345 void CMusicDatabase::SetPropertiesFromAlbum(CFileItem& item, const CAlbum& album)
5347 item.SetProperty("album_description", album.strReview);
5348 item.SetProperty("album_theme", StringUtils::Join(album.themes, g_advancedSettings.m_musicItemSeparator));
5349 item.SetProperty("album_theme_array", album.themes);
5350 item.SetProperty("album_mood", StringUtils::Join(album.moods, g_advancedSettings.m_musicItemSeparator));
5351 item.SetProperty("album_mood_array", album.moods);
5352 item.SetProperty("album_style", StringUtils::Join(album.styles, g_advancedSettings.m_musicItemSeparator));
5353 item.SetProperty("album_style_array", album.styles);
5354 item.SetProperty("album_type", album.strType);
5355 item.SetProperty("album_label", album.strLabel);
5356 item.SetProperty("album_artist", StringUtils::Join(album.artist, g_advancedSettings.m_musicItemSeparator));
5357 item.SetProperty("album_artist_array", album.artist);
5358 item.SetProperty("album_genre", StringUtils::Join(album.genre, g_advancedSettings.m_musicItemSeparator));
5359 item.SetProperty("album_genre_array", album.genre);
5360 item.SetProperty("album_title", album.strAlbum);
5361 if (album.iRating > 0)
5362 item.SetProperty("album_rating", album.iRating);
5365 void CMusicDatabase::SetPropertiesForFileItem(CFileItem& item)
5367 if (!item.HasMusicInfoTag())
5369 int idArtist = GetArtistByName(StringUtils::Join(item.GetMusicInfoTag()->GetArtist(), g_advancedSettings.m_musicItemSeparator));
5373 if (GetArtist(idArtist, artist))
5374 SetPropertiesFromArtist(item,artist);
5376 int idAlbum = item.GetMusicInfoTag()->GetAlbumId();
5378 idAlbum = GetAlbumByName(item.GetMusicInfoTag()->GetAlbum(),
5379 item.GetMusicInfoTag()->GetArtist());
5383 if (GetAlbum(idAlbum, album, false))
5384 SetPropertiesFromAlbum(item,album);
5388 void CMusicDatabase::SetArtForItem(int mediaId, const string &mediaType, const map<string, string> &art)
5390 for (map<string, string>::const_iterator i = art.begin(); i != art.end(); ++i)
5391 SetArtForItem(mediaId, mediaType, i->first, i->second);
5394 void CMusicDatabase::SetArtForItem(int mediaId, const string &mediaType, const string &artType, const string &url)
5398 if (NULL == m_pDB.get()) return;
5399 if (NULL == m_pDS.get()) return;
5401 // don't set <foo>.<bar> art types - these are derivative types from parent items
5402 if (artType.find('.') != string::npos)
5405 CStdString sql = PrepareSQL("SELECT art_id FROM art WHERE media_id=%i AND media_type='%s' AND type='%s'", mediaId, mediaType.c_str(), artType.c_str());
5406 m_pDS->query(sql.c_str());
5409 int artId = m_pDS->fv(0).get_asInt();
5411 sql = PrepareSQL("UPDATE art SET url='%s' where art_id=%d", url.c_str(), artId);
5412 m_pDS->exec(sql.c_str());
5417 sql = PrepareSQL("INSERT INTO art(media_id, media_type, type, url) VALUES (%d, '%s', '%s', '%s')", mediaId, mediaType.c_str(), artType.c_str(), url.c_str());
5418 m_pDS->exec(sql.c_str());
5423 CLog::Log(LOGERROR, "%s(%d, '%s', '%s', '%s') failed", __FUNCTION__, mediaId, mediaType.c_str(), artType.c_str(), url.c_str());
5427 bool CMusicDatabase::GetArtForItem(int mediaId, const string &mediaType, map<string, string> &art)
5431 if (NULL == m_pDB.get()) return false;
5432 if (NULL == m_pDS2.get()) return false; // using dataset 2 as we're likely called in loops on dataset 1
5434 CStdString sql = PrepareSQL("SELECT type,url FROM art WHERE media_id=%i AND media_type='%s'", mediaId, mediaType.c_str());
5435 m_pDS2->query(sql.c_str());
5436 while (!m_pDS2->eof())
5438 art.insert(make_pair(m_pDS2->fv(0).get_asString(), m_pDS2->fv(1).get_asString()));
5442 return !art.empty();
5446 CLog::Log(LOGERROR, "%s(%d) failed", __FUNCTION__, mediaId);
5451 string CMusicDatabase::GetArtForItem(int mediaId, const string &mediaType, const string &artType)
5453 std::string query = PrepareSQL("SELECT url FROM art WHERE media_id=%i AND media_type='%s' AND type='%s'", mediaId, mediaType.c_str(), artType.c_str());
5454 return GetSingleValue(query, m_pDS2);
5457 bool CMusicDatabase::GetArtistArtForItem(int mediaId, const std::string &mediaType, std::map<std::string, std::string> &art)
5461 if (NULL == m_pDB.get()) return false;
5462 if (NULL == m_pDS2.get()) return false; // using dataset 2 as we're likely called in loops on dataset 1
5464 CStdString sql = PrepareSQL("SELECT type,url FROM art WHERE media_id=(SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist'", mediaType.c_str(), mediaType.c_str(), mediaId);
5465 m_pDS2->query(sql.c_str());
5466 while (!m_pDS2->eof())
5468 art.insert(make_pair(m_pDS2->fv(0).get_asString(), m_pDS2->fv(1).get_asString()));
5472 return !art.empty();
5476 CLog::Log(LOGERROR, "%s(%d) failed", __FUNCTION__, mediaId);
5481 string CMusicDatabase::GetArtistArtForItem(int mediaId, const string &mediaType, const string &artType)
5483 std::string query = PrepareSQL("SELECT url FROM art WHERE media_id=(SELECT idArtist from %s_artist WHERE id%s=%i AND iOrder=0) AND media_type='artist' AND type='%s'", mediaType.c_str(), mediaType.c_str(), mediaId, artType.c_str());
5484 return GetSingleValue(query, m_pDS2);
5487 bool CMusicDatabase::GetFilter(CDbUrl &musicUrl, Filter &filter, SortDescription &sorting)
5489 if (!musicUrl.IsValid())
5492 std::string type = musicUrl.GetType();
5493 const CUrlOptions::UrlOptions& options = musicUrl.GetOptions();
5494 CUrlOptions::UrlOptions::const_iterator option;
5496 if (type == "artists")
5498 int idArtist = -1, idGenre = -1, idAlbum = -1, idSong = -1;
5499 bool albumArtistsOnly = false;
5501 option = options.find("artistid");
5502 if (option != options.end())
5503 idArtist = (int)option->second.asInteger();
5505 option = options.find("genreid");
5506 if (option != options.end())
5507 idGenre = (int)option->second.asInteger();
5510 option = options.find("genre");
5511 if (option != options.end())
5512 idGenre = GetGenreByName(option->second.asString());
5515 option = options.find("albumid");
5516 if (option != options.end())
5517 idAlbum = (int)option->second.asInteger();
5520 option = options.find("album");
5521 if (option != options.end())
5522 idAlbum = GetAlbumByName(option->second.asString());
5525 option = options.find("songid");
5526 if (option != options.end())
5527 idSong = (int)option->second.asInteger();
5529 option = options.find("albumartistsonly");
5530 if (option != options.end())
5531 albumArtistsOnly = option->second.asBoolean();
5533 CStdString strSQL = "(artistview.idArtist IN ";
5535 strSQL += PrepareSQL("(%d)", idArtist);
5536 else if (idAlbum > 0)
5537 strSQL += PrepareSQL("(SELECT album_artist.idArtist FROM album_artist WHERE album_artist.idAlbum = %i)", idAlbum);
5538 else if (idSong > 0)
5539 strSQL += PrepareSQL("(SELECT song_artist.idArtist FROM song_artist WHERE song_artist.idSong = %i)", idSong);
5540 else if (idGenre > 0)
5541 { // same statements as below, but limit to the specified genre
5542 // in this case we show the whole lot always - there is no limitation to just album artists
5543 if (!albumArtistsOnly) // show all artists in this case (ie those linked to a song)
5544 strSQL+=PrepareSQL("(SELECT song_artist.idArtist FROM song_artist" // All artists linked to extra genres
5545 " JOIN song_genre ON song_artist.idSong = song_genre.idSong"
5546 " WHERE song_genre.idGenre = %i)"
5547 " OR idArtist IN ", idGenre);
5548 // and add any artists linked to an album (may be different from above due to album artist tag)
5549 strSQL += PrepareSQL("(SELECT album_artist.idArtist FROM album_artist" // All album artists linked to extra genres
5550 " JOIN album_genre ON album_artist.idAlbum = album_genre.idAlbum"
5551 " WHERE album_genre.idGenre = %i)", idGenre);
5555 if (!albumArtistsOnly) // show all artists in this case (ie those linked to a song)
5556 strSQL += "(SELECT song_artist.idArtist FROM song_artist)"
5557 " OR artistview.idArtist IN ";
5559 // and always show any artists linked to an album (may be different from above due to album artist tag)
5560 strSQL += "(SELECT album_artist.idArtist FROM album_artist"; // All artists linked to an album
5561 if (albumArtistsOnly)
5562 strSQL += " JOIN album ON album.idAlbum = album_artist.idAlbum WHERE album.bCompilation = 0 "; // then exclude those that have no extra artists
5566 // remove the null string
5567 strSQL += ") and artistview.strArtist != ''";
5569 // and the various artist entry if applicable
5570 if (!albumArtistsOnly)
5572 CStdString strVariousArtists = g_localizeStrings.Get(340);
5573 strSQL += PrepareSQL(" and artistview.strArtist <> '%s'", strVariousArtists.c_str());
5576 filter.AppendWhere(strSQL);
5578 else if (type == "albums")
5580 option = options.find("year");
5581 if (option != options.end())
5582 filter.AppendWhere(PrepareSQL("albumview.iYear = %i", (int)option->second.asInteger()));
5584 option = options.find("compilation");
5585 if (option != options.end())
5586 filter.AppendWhere(PrepareSQL("albumview.bCompilation = %i", option->second.asBoolean() ? 1 : 0));
5588 option = options.find("genreid");
5589 if (option != options.end())
5590 filter.AppendWhere(PrepareSQL("albumview.idAlbum IN (SELECT song.idAlbum FROM song JOIN song_genre ON song.idSong = song_genre.idSong WHERE song_genre.idGenre = %i)", (int)option->second.asInteger()));
5592 option = options.find("genre");
5593 if (option != options.end())
5594 filter.AppendWhere(PrepareSQL("albumview.idAlbum IN (SELECT song.idAlbum FROM song JOIN song_genre ON song.idSong = song_genre.idSong JOIN genre ON genre.idGenre = song_genre.idGenre WHERE genre.strGenre like '%s')", option->second.asString().c_str()));
5596 option = options.find("artistid");
5597 if (option != options.end())
5599 filter.AppendJoin("JOIN song ON song.idAlbum = albumview.idAlbum "
5600 "JOIN song_artist ON song.idSong = song_artist.idSong "
5601 "JOIN album_artist ON albumview.idAlbum = album_artist.idAlbum");
5602 filter.AppendWhere(PrepareSQL(" song_artist.idArtist = %i" // All albums linked to this artist via songs
5603 " OR album_artist.idArtist = %i", // All albums where album artists fit
5604 (int)option->second.asInteger(), (int)option->second.asInteger()));
5605 filter.AppendGroup("albumview.idAlbum");
5609 option = options.find("artist");
5610 if (option != options.end())
5611 filter.AppendWhere(PrepareSQL("albumview.idAlbum IN (SELECT song.idAlbum FROM song JOIN song_artist ON song.idSong = song_artist.idSong JOIN artist ON artist.idArtist = song_artist.idArtist WHERE artist.strArtist like '%s')" // All albums linked to this artist via songs
5612 " OR albumview.idAlbum IN (SELECT album_artist.idAlbum FROM album_artist JOIN artist ON artist.idArtist = album_artist.idArtist WHERE artist.strArtist like '%s')", // All albums where album artists fit
5613 option->second.asString().c_str(), option->second.asString().c_str()));
5614 // no artist given, so exclude any single albums (aka empty tagged albums)
5616 filter.AppendWhere("albumview.strAlbum <> ''");
5619 else if (type == "songs" || type == "singles")
5621 option = options.find("singles");
5622 if (option != options.end())
5623 filter.AppendWhere(PrepareSQL("songview.idAlbum %sIN (SELECT idAlbum FROM album WHERE strAlbum = '')", option->second.asBoolean() ? "" : "NOT "));
5625 option = options.find("year");
5626 if (option != options.end())
5627 filter.AppendWhere(PrepareSQL("songview.iYear = %i", (int)option->second.asInteger()));
5629 option = options.find("compilation");
5630 if (option != options.end())
5631 filter.AppendWhere(PrepareSQL("songview.bCompilation = %i", option->second.asBoolean() ? 1 : 0));
5633 option = options.find("albumid");
5634 if (option != options.end())
5635 filter.AppendWhere(PrepareSQL("songview.idAlbum = %i", (int)option->second.asInteger()));
5637 option = options.find("album");
5638 if (option != options.end())
5639 filter.AppendWhere(PrepareSQL("songview.strAlbum like '%s'", option->second.asString().c_str()));
5641 option = options.find("genreid");
5642 if (option != options.end())
5643 filter.AppendWhere(PrepareSQL("songview.idSong IN (SELECT song_genre.idSong FROM song_genre WHERE song_genre.idGenre = %i)", (int)option->second.asInteger()));
5645 option = options.find("genre");
5646 if (option != options.end())
5647 filter.AppendWhere(PrepareSQL("songview.idSong IN (SELECT song_genre.idSong FROM song_genre JOIN genre ON genre.idGenre = song_genre.idGenre WHERE genre.strGenre like '%s')", option->second.asString().c_str()));
5649 option = options.find("artistid");
5650 if (option != options.end())
5651 filter.AppendWhere(PrepareSQL("songview.idSong IN (SELECT song_artist.idSong FROM song_artist WHERE song_artist.idArtist = %i)" // song artists
5652 " OR songview.idSong IN (SELECT song.idSong FROM song JOIN album_artist ON song.idAlbum=album_artist.idAlbum WHERE album_artist.idArtist = %i)", // album artists
5653 (int)option->second.asInteger(), (int)option->second.asInteger()));
5655 option = options.find("artist");
5656 if (option != options.end())
5657 filter.AppendWhere(PrepareSQL("songview.idSong IN (SELECT song_artist.idSong FROM song_artist JOIN artist ON artist.idArtist = song_artist.idArtist WHERE artist.strArtist like '%s')" // song artists
5658 " OR songview.idSong IN (SELECT song.idSong FROM song JOIN album_artist ON song.idAlbum=album_artist.idAlbum JOIN artist ON artist.idArtist = album_artist.idArtist WHERE artist.strArtist like '%s')", // album artists
5659 option->second.asString().c_str(), option->second.asString().c_str()));
5662 option = options.find("xsp");
5663 if (option != options.end())
5666 if (!xsp.LoadFromJson(option->second.asString()))
5669 // check if the filter playlist matches the item type
5670 if (xsp.GetType() == type ||
5671 (xsp.GetGroup() == type && !xsp.IsGroupMixed()))
5673 std::set<CStdString> playlists;
5674 filter.AppendWhere(xsp.GetWhereClause(*this, playlists));
5676 if (xsp.GetLimit() > 0)
5677 sorting.limitEnd = xsp.GetLimit();
5678 if (xsp.GetOrder() != SortByNone)
5679 sorting.sortBy = xsp.GetOrder();
5680 sorting.sortOrder = xsp.GetOrderAscending() ? SortOrderAscending : SortOrderDescending;
5681 if (CSettings::Get().GetBool("filelists.ignorethewhensorting"))
5682 sorting.sortAttributes = SortAttributeIgnoreArticle;
5686 option = options.find("filter");
5687 if (option != options.end())
5689 CSmartPlaylist xspFilter;
5690 if (!xspFilter.LoadFromJson(option->second.asString()))
5693 // check if the filter playlist matches the item type
5694 if (xspFilter.GetType() == type)
5696 std::set<CStdString> playlists;
5697 filter.AppendWhere(xspFilter.GetWhereClause(*this, playlists));
5699 // remove the filter if it doesn't match the item type
5701 musicUrl.RemoveOption("filter");