2 * Copyright (C) 2005-2008 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, write to
17 * the Free Software Foundation, 675 Mass Ave, Cambridge, MA 02139, USA.
18 * http://www.gnu.org/copyleft/gpl.html
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 "music/dialogs/GUIDialogMusicScan.h"
32 #include "GUIInfoManager.h"
33 #include "music/tags/MusicInfoTag.h"
34 #include "addons/AddonManager.h"
35 #include "addons/Scraper.h"
36 #include "addons/Addon.h"
37 #include "utils/URIUtils.h"
41 #include "guilib/GUIWindowManager.h"
42 #include "dialogs/GUIDialogOK.h"
43 #include "dialogs/GUIDialogProgress.h"
44 #include "dialogs/GUIDialogYesNo.h"
45 #include "dialogs/GUIDialogSelect.h"
46 #include "filesystem/File.h"
47 #include "settings/GUISettings.h"
48 #include "settings/AdvancedSettings.h"
50 #include "Application.h"
52 #include "karaoke/karaokelyricsfactory.h"
54 #include "storage/MediaManager.h"
55 #include "settings/Settings.h"
56 #include "utils/StringUtils.h"
57 #include "guilib/LocalizeStrings.h"
58 #include "utils/log.h"
59 #include "utils/TimeUtils.h"
60 #include "TextureCache.h"
61 #include "addons/AddonInstaller.h"
62 #include "utils/AutoPtrHandle.h"
63 #include "interfaces/AnnouncementManager.h"
64 #include "dbwrappers/dataset.h"
67 using namespace AUTOPTR;
68 using namespace XFILE;
69 using namespace MUSICDATABASEDIRECTORY;
70 using ADDON::AddonPtr;
72 #define RECENTLY_PLAYED_LIMIT 25
73 #define MIN_FULL_SEARCH_LENGTH 3
79 CMusicDatabase::CMusicDatabase(void)
83 CMusicDatabase::~CMusicDatabase(void)
88 bool CMusicDatabase::Open()
90 return CDatabase::Open(g_advancedSettings.m_databaseMusic);
93 bool CMusicDatabase::CreateTables()
98 CDatabase::CreateTables();
100 CLog::Log(LOGINFO, "create artist table");
101 m_pDS->exec("CREATE TABLE artist ( idArtist integer primary key, strArtist varchar(256))\n");
102 CLog::Log(LOGINFO, "create album table");
103 m_pDS->exec("CREATE TABLE album ( idAlbum integer primary key, strAlbum varchar(256), idArtist integer, strExtraArtists text, idGenre integer, strExtraGenres text, iYear integer, idThumb integer)\n");
104 CLog::Log(LOGINFO, "create genre table");
105 m_pDS->exec("CREATE TABLE genre ( idGenre integer primary key, strGenre varchar(256))\n");
106 CLog::Log(LOGINFO, "create path table");
107 m_pDS->exec("CREATE TABLE path ( idPath integer primary key, strPath varchar(512), strHash text)\n");
108 CLog::Log(LOGINFO, "create song table");
109 m_pDS->exec("CREATE TABLE song ( idSong integer primary key, idAlbum integer, idPath integer, idArtist integer, strExtraArtists 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)\n");
110 CLog::Log(LOGINFO, "create albuminfo table");
111 m_pDS->exec("CREATE TABLE albuminfo ( idAlbumInfo integer primary key, idAlbum integer, iYear integer, idGenre integer, strExtraGenres text, strMoods text, strStyles text, strThemes text, strReview text, strImage text, strLabel text, strType text, iRating integer)\n");
112 CLog::Log(LOGINFO, "create albuminfosong table");
113 m_pDS->exec("CREATE TABLE albuminfosong ( idAlbumInfoSong integer primary key, idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer)\n");
114 CLog::Log(LOGINFO, "create thumb table");
115 m_pDS->exec("CREATE TABLE thumb (idThumb integer primary key, strThumb varchar(256))\n");
116 CLog::Log(LOGINFO, "create artistnfo table");
117 m_pDS->exec("CREATE TABLE artistinfo ( idArtistInfo integer primary key, idArtist integer, strBorn text, strFormed text, strGenres text, strMoods text, strStyles text, strInstruments text, strBiography text, strDied text, strDisbanded text, strYearsActive text, strImage text, strFanart text)\n");
118 CLog::Log(LOGINFO, "create content table");
119 m_pDS->exec("CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text)\n");
120 CLog::Log(LOGINFO, "create discography table");
121 m_pDS->exec("CREATE TABLE discography (idArtist integer, strAlbum text, strYear text)\n");
123 CLog::Log(LOGINFO, "create exartistsong table");
124 m_pDS->exec("CREATE TABLE exartistsong ( idSong integer, iPosition integer, idArtist integer)\n");
125 CLog::Log(LOGINFO, "create extragenresong table");
126 m_pDS->exec("CREATE TABLE exgenresong ( idSong integer, iPosition integer, idGenre integer)\n");
127 CLog::Log(LOGINFO, "create exartistalbum table");
128 m_pDS->exec("CREATE TABLE exartistalbum ( idAlbum integer, iPosition integer, idArtist integer)\n");
129 CLog::Log(LOGINFO, "create exgenrealbum table");
130 m_pDS->exec("CREATE TABLE exgenrealbum ( idAlbum integer, iPosition integer, idGenre integer)\n");
132 CLog::Log(LOGINFO, "create karaokedata table");
133 m_pDS->exec("CREATE TABLE karaokedata ( iKaraNumber integer, idSong integer, iKaraDelay integer, strKaraEncoding text, "
134 "strKaralyrics text, strKaraLyrFileCRC text )\n");
137 CLog::Log(LOGINFO, "create exartistsong index");
138 m_pDS->exec("CREATE INDEX idxExtraArtistSong ON exartistsong(idSong)");
139 m_pDS->exec("CREATE INDEX idxExtraArtistSong2 ON exartistsong(idArtist)");
140 CLog::Log(LOGINFO, "create exgenresong index");
141 m_pDS->exec("CREATE INDEX idxExtraGenreSong ON exgenresong(idSong)");
142 m_pDS->exec("CREATE INDEX idxExtraGenreSong2 ON exgenresong(idGenre)");
143 CLog::Log(LOGINFO, "create exartistalbum index");
144 m_pDS->exec("CREATE INDEX idxExtraArtistAlbum ON exartistalbum(idAlbum)");
145 m_pDS->exec("CREATE INDEX idxExtraArtistAlbum2 ON exartistalbum(idArtist)");
146 CLog::Log(LOGINFO, "create exgenrealbum index");
147 m_pDS->exec("CREATE INDEX idxExtraGenreAlbum ON exgenrealbum(idAlbum)");
148 m_pDS->exec("CREATE INDEX idxExtraGenreAlbum2 ON exgenrealbum(idGenre)");
150 CLog::Log(LOGINFO, "create album index");
151 m_pDS->exec("CREATE INDEX idxAlbum ON album(strAlbum)");
152 CLog::Log(LOGINFO, "create album index2");
153 m_pDS->exec("CREATE INDEX idxAlbum2 ON album(idArtist)");
155 CLog::Log(LOGINFO, "create genre index");
156 m_pDS->exec("CREATE INDEX idxGenre ON genre(strGenre)");
157 CLog::Log(LOGINFO, "create artist index");
158 m_pDS->exec("CREATE INDEX idxArtist ON artist(strArtist)");
159 CLog::Log(LOGINFO, "create path index");
160 m_pDS->exec("CREATE INDEX idxPath ON path(strPath)");
162 CLog::Log(LOGINFO, "create song index");
163 m_pDS->exec("CREATE INDEX idxSong ON song(strTitle)");
164 CLog::Log(LOGINFO, "create song index1");
165 m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
166 CLog::Log(LOGINFO, "create song index2");
167 m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
168 CLog::Log(LOGINFO, "create song index3");
169 m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
170 CLog::Log(LOGINFO, "create song index4");
171 m_pDS->exec("CREATE INDEX idxSong4 ON song(idArtist)");
172 CLog::Log(LOGINFO, "create song index5");
173 m_pDS->exec("CREATE INDEX idxSong5 ON song(idGenre)");
174 CLog::Log(LOGINFO, "create song index6");
175 m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
177 CLog::Log(LOGINFO, "create thumb index");
178 m_pDS->exec("CREATE INDEX idxThumb ON thumb(strThumb)");
179 //m_pDS->exec("CREATE INDEX idxSong ON song(dwFileNameCRC)");
180 CLog::Log(LOGINFO, "create artistinfo index");
181 m_pDS->exec("CREATE INDEX idxArtistInfo on artistinfo(idArtist)");
182 CLog::Log(LOGINFO, "create albuminfo index");
183 m_pDS->exec("CREATE INDEX idxAlbumInfo on albuminfo(idAlbum)");
185 CLog::Log(LOGINFO, "create karaokedata index");
186 m_pDS->exec("CREATE INDEX idxKaraNumber on karaokedata(iKaraNumber)");
187 m_pDS->exec("CREATE INDEX idxKarSong on karaokedata(idSong)");
190 CLog::Log(LOGINFO, "create albuminfo trigger");
191 m_pDS->exec("CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW BEGIN delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo; END");
193 // we create views last to ensure all indexes are rolled in
196 // Add 'Karaoke' genre
197 AddGenre( "Karaoke" );
201 CLog::Log(LOGERROR, "%s unable to create tables:%i", __FUNCTION__, (int)GetLastError());
202 RollbackTransaction();
209 void CMusicDatabase::CreateViews()
211 CLog::Log(LOGINFO, "create song view");
212 m_pDS->exec("DROP VIEW IF EXISTS songview");
213 m_pDS->exec("create view songview as select song.idSong as idSong, song.strExtraArtists as strExtraArtists, song.strExtraGenres as strExtraGenres, strTitle, iTrack, iDuration, song.iYear as iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, lastplayed, rating, comment, song.idAlbum as idAlbum, strAlbum, strPath, song.idArtist as idArtist, strArtist, song.idGenre as idGenre, strGenre, strThumb, iKaraNumber, iKaraDelay, strKaraEncoding from song join album on song.idAlbum=album.idAlbum join path on song.idPath=path.idPath join artist on song.idArtist=artist.idArtist join genre on song.idGenre=genre.idGenre join thumb on song.idThumb=thumb.idThumb left outer join karaokedata on song.idSong=karaokedata.idSong");
215 CLog::Log(LOGINFO, "create album view");
216 m_pDS->exec("DROP VIEW IF EXISTS albumview");
217 m_pDS->exec("create view albumview as select album.idAlbum as idAlbum, strAlbum, strExtraArtists, "
218 "album.idArtist as idArtist, album.strExtraGenres as strExtraGenres, album.idGenre as idGenre, "
219 "strArtist, strGenre, album.iYear as iYear, strThumb, idAlbumInfo, strMoods, strStyles, strThemes, "
220 "strReview, strLabel, strType, strImage, iRating from album "
221 "left outer join artist on album.idArtist=artist.idArtist "
222 "left outer join genre on album.idGenre=genre.idGenre "
223 "left outer join thumb on album.idThumb=thumb.idThumb "
224 "left outer join albuminfo on album.idAlbum=albuminfo.idAlbum");
227 void CMusicDatabase::AddSong(CSong& song, bool bCheck)
232 // We need at least the title
233 if (song.strTitle.IsEmpty())
236 CStdString strPath, strFileName;
237 URIUtils::Split(song.strFileName, strPath, strFileName);
239 if (NULL == m_pDB.get()) return ;
240 if (NULL == m_pDS.get()) return ;
242 // split our (possibly) multiple artist string into individual artists
243 std::vector<std::string> extraArtists;
244 for (unsigned int index = 1; index < song.artist.size(); index++)
245 extraArtists.push_back(song.artist.at(index));
246 CStdString strExtraArtists = StringUtils::Join(extraArtists, g_advancedSettings.m_musicItemSeparator);
248 // do the same with our albumartist
249 std::vector<std::string> extraAlbumArtists;
250 for (unsigned int index = 1; index < song.albumArtist.size(); index++)
251 extraAlbumArtists.push_back(song.albumArtist.at(index));
252 CStdString strExtraAlbumArtists = StringUtils::Join(extraAlbumArtists, g_advancedSettings.m_musicItemSeparator);
254 // and the same for our genres
255 std::vector<std::string> extraGenres;
256 for (unsigned int index = 1; index < song.genre.size(); index++)
257 extraGenres.push_back(song.genre.at(index));
258 CStdString strExtraGenres = StringUtils::Join(extraGenres, g_advancedSettings.m_musicItemSeparator);
260 // add the primary artist/genre
261 // SplitString returns >= 1 so no worries referencing the first item here
264 if (!song.artist.empty())
265 idArtist = AddArtist(song.artist[0]);
266 if (!song.genre.empty())
267 idGenre = AddGenre(song.genre[0]);
268 // and also the primary album artist (if applicable)
269 int idAlbumArtist = -1;
270 if (!song.albumArtist.empty() && !song.albumArtist[0].empty())
271 idAlbumArtist = AddArtist(song.albumArtist[0]);
273 int idPath = AddPath(strPath);
274 int idThumb = AddThumb(song.strThumb);
276 if (idAlbumArtist > -1) // have an album artist
277 idAlbum = AddAlbum(song.strAlbum, idAlbumArtist, strExtraAlbumArtists, StringUtils::Join(song.albumArtist, g_advancedSettings.m_musicItemSeparator), idThumb, idGenre, strExtraGenres, song.iYear);
279 idAlbum = AddAlbum(song.strAlbum, idArtist, strExtraArtists, StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator), idThumb, idGenre, strExtraGenres, song.iYear);
281 DWORD crc = ComputeCRC(song.strFileName);
285 bool bHasKaraoke = false;
287 bHasKaraoke = CKaraokeLyricsFactory::HasLyrics( song.strFileName );
290 // If this is karaoke song, change the genre to 'Karaoke' (and add it if it's not there)
291 if ( bHasKaraoke && g_advancedSettings.m_karaokeChangeGenreForKaraokeSongs )
292 idGenre = AddGenre( "Karaoke" );
296 strSQL=PrepareSQL("select * from song where idAlbum=%i and dwFileNameCRC='%ul' and strTitle='%s'",
297 idAlbum, crc, song.strTitle.c_str());
299 if (!m_pDS->query(strSQL.c_str()))
302 if (m_pDS->num_rows() != 0)
304 idSong = m_pDS->fv("idSong").get_asInt();
313 strSQL=PrepareSQL("insert into song (idSong,idAlbum,idPath,idArtist,strExtraArtists,idGenre,strExtraGenres,strTitle,iTrack,iDuration,iYear,dwFileNameCRC,strFileName,strMusicBrainzTrackID,strMusicBrainzArtistID,strMusicBrainzAlbumID,strMusicBrainzAlbumArtistID,strMusicBrainzTRMID,iTimesPlayed,iStartOffset,iEndOffset,idThumb,lastplayed,rating,comment) values (NULL,%i,%i,%i,'%s',%i,'%s','%s',%i,%i,%i,'%ul','%s','%s','%s','%s','%s','%s'",
314 idAlbum, idPath, idArtist, strExtraArtists.c_str(), idGenre, strExtraGenres.c_str(),
315 song.strTitle.c_str(),
316 song.iTrack, song.iDuration, song.iYear,
317 crc, strFileName.c_str(),
318 song.strMusicBrainzTrackID.c_str(),
319 song.strMusicBrainzArtistID.c_str(),
320 song.strMusicBrainzAlbumID.c_str(),
321 song.strMusicBrainzAlbumArtistID.c_str(),
322 song.strMusicBrainzTRMID.c_str());
324 if (song.lastPlayed.IsValid())
325 strSQL1=PrepareSQL(",%i,%i,%i,%i,'%s','%c','%s')",
326 song.iTimesPlayed, song.iStartOffset, song.iEndOffset, idThumb, song.lastPlayed.GetAsDBDateTime().c_str(), song.rating, song.strComment.c_str());
328 strSQL1=PrepareSQL(",%i,%i,%i,%i,NULL,'%c','%s')",
329 song.iTimesPlayed, song.iStartOffset, song.iEndOffset, idThumb, song.rating, song.strComment.c_str());
332 m_pDS->exec(strSQL.c_str());
333 idSong = (int)m_pDS->lastinsertid();
336 // add extra artists and genres
337 AddExtraSongArtists(song.artist, idSong, bCheck);
338 if (idAlbumArtist > -1)
339 AddExtraAlbumArtists(song.albumArtist, idAlbum);
341 AddExtraAlbumArtists(song.artist, idAlbum);
342 AddExtraGenres(song.genre, idSong, idAlbum, bCheck);
344 song.idSong = idSong;
346 // Add karaoke information (if any)
348 AddKaraokeData( song );
350 AnnounceUpdate("song", idSong);
354 CLog::Log(LOGERROR, "musicdatabase:unable to addsong (%s)", strSQL.c_str());
358 int CMusicDatabase::AddAlbum(const CStdString& strAlbum1, int idArtist, const CStdString &extraArtists, const CStdString &strArtist, int idThumb, int idGenre, const CStdString &extraGenres, int year)
363 CStdString strAlbum=strAlbum1;
364 strAlbum.TrimLeft(" ");
365 strAlbum.TrimRight(" ");
367 if (strAlbum.IsEmpty())
369 // album tag is empty, so we treat this as a single, or a collection of singles,
370 // so we don't specify a thumb
371 idThumb = AddThumb("");
374 if (NULL == m_pDB.get()) return -1;
375 if (NULL == m_pDS.get()) return -1;
377 map <CStdString, CAlbumCache>::const_iterator it;
379 it = m_albumCache.find(strAlbum + strArtist);
380 if (it != m_albumCache.end())
381 return it->second.idAlbum;
383 strSQL=PrepareSQL("select * from album where idArtist=%i and strAlbum like '%s'", idArtist, strAlbum.c_str());
384 m_pDS->query(strSQL.c_str());
386 if (m_pDS->num_rows() == 0)
389 // doesnt exists, add it
390 strSQL=PrepareSQL("insert into album (idAlbum, strAlbum, idArtist, strExtraArtists, idGenre, strExtraGenres, iYear, idThumb) values( NULL, '%s', %i, '%s', %i, '%s', %i, %i)", strAlbum.c_str(), idArtist, extraArtists.c_str(), idGenre, extraGenres.c_str(), year, idThumb);
391 m_pDS->exec(strSQL.c_str());
394 album.idAlbum = (int)m_pDS->lastinsertid();
395 album.strAlbum = strAlbum;
396 album.idArtist = idArtist;
397 album.artist = StringUtils::Split(strArtist, g_advancedSettings.m_musicItemSeparator);
398 m_albumCache.insert(pair<CStdString, CAlbumCache>(album.strAlbum + strArtist, album));
399 return album.idAlbum;
403 // exists in our database and not scanned during this scan, so we should update it as the details
404 // may have changed (there's a reason we're rescanning, afterall!)
406 album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
407 album.strAlbum = strAlbum;
408 album.idArtist = idArtist;
409 album.artist = StringUtils::Split(strArtist, g_advancedSettings.m_musicItemSeparator);
410 m_albumCache.insert(pair<CStdString, CAlbumCache>(album.strAlbum + strArtist, album));
412 strSQL=PrepareSQL("update album set strExtraArtists='%s', idGenre=%i, strExtraGenres='%s', iYear=%i, idThumb=%i where idAlbum=%i", extraArtists.c_str(), idGenre, extraGenres.c_str(), year, idThumb, album.idAlbum);
413 m_pDS->exec(strSQL.c_str());
414 // and clear the exartistalbum and exgenrealbum tables - these are updated in AddSong()
415 strSQL=PrepareSQL("delete from exartistalbum where idAlbum=%i", album.idAlbum);
416 m_pDS->exec(strSQL.c_str());
417 strSQL=PrepareSQL("delete from exgenrealbum where idAlbum=%i", album.idAlbum);
418 m_pDS->exec(strSQL.c_str());
419 return album.idAlbum;
424 CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
430 int CMusicDatabase::AddGenre(const CStdString& strGenre1)
435 CStdString strGenre = strGenre1;
436 strGenre.TrimLeft(" ");
437 strGenre.TrimRight(" ");
439 if (strGenre.IsEmpty())
440 strGenre=g_localizeStrings.Get(13205); // Unknown
442 if (NULL == m_pDB.get()) return -1;
443 if (NULL == m_pDS.get()) return -1;
444 map <CStdString, int>::const_iterator it;
446 it = m_genreCache.find(strGenre);
447 if (it != m_genreCache.end())
451 strSQL=PrepareSQL("select * from genre where strGenre like '%s'", strGenre.c_str());
452 m_pDS->query(strSQL.c_str());
453 if (m_pDS->num_rows() == 0)
456 // doesnt exists, add it
457 strSQL=PrepareSQL("insert into genre (idGenre, strGenre) values( NULL, '%s' )", strGenre.c_str());
458 m_pDS->exec(strSQL.c_str());
460 int idGenre = (int)m_pDS->lastinsertid();
461 m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
466 int idGenre = m_pDS->fv("idGenre").get_asInt();
467 m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
474 CLog::Log(LOGERROR, "musicdatabase:unable to addgenre (%s)", strSQL.c_str());
480 int CMusicDatabase::AddArtist(const CStdString& strArtist1)
485 CStdString strArtist = strArtist1;
486 strArtist.TrimLeft(" ");
487 strArtist.TrimRight(" ");
489 if (strArtist.IsEmpty())
490 strArtist=g_localizeStrings.Get(13205); // Unknown
492 if (NULL == m_pDB.get()) return -1;
493 if (NULL == m_pDS.get()) return -1;
495 map <CStdString, int>::const_iterator it;
497 it = m_artistCache.find(strArtist);
498 if (it != m_artistCache.end())
499 return it->second;//.idArtist;
501 strSQL=PrepareSQL("select * from artist where strArtist like '%s'", strArtist.c_str());
502 m_pDS->query(strSQL.c_str());
504 if (m_pDS->num_rows() == 0)
507 // doesnt exists, add it
508 strSQL=PrepareSQL("insert into artist (idArtist, strArtist) values( NULL, '%s' )", strArtist.c_str());
509 m_pDS->exec(strSQL.c_str());
510 int idArtist = (int)m_pDS->lastinsertid();
511 m_artistCache.insert(pair<CStdString, int>(strArtist1, idArtist));
516 int idArtist = (int)m_pDS->fv("idArtist").get_asInt();
517 m_artistCache.insert(pair<CStdString, int>(strArtist1, idArtist));
524 CLog::Log(LOGERROR, "musicdatabase:unable to addartist (%s)", strSQL.c_str());
530 void CMusicDatabase::AddExtraSongArtists(const std::vector<std::string> &vecArtists, int idSong, bool bCheck)
534 // add each of the artists in the vector of artists
535 for (int i = 1; i < (int)vecArtists.size(); i++)
537 int idArtist = AddArtist(vecArtists[i]);
539 { // added successfully, we must now add entries to the exartistsong table
541 // first link the artist with the song
545 strSQL=PrepareSQL("select * from exartistsong where idSong=%i and idArtist=%i",
547 if (!m_pDS->query(strSQL.c_str())) return ;
548 if (m_pDS->num_rows() != 0)
549 bInsert = false; // already exists
554 strSQL=PrepareSQL("insert into exartistsong (idSong,iPosition,idArtist) values(%i,%i,%i)",
555 idSong, i, idArtist);
557 m_pDS->exec(strSQL.c_str());
564 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
568 void CMusicDatabase::AddExtraAlbumArtists(const std::vector<std::string> &vecArtists, int idAlbum)
572 // add each of the artists in the vector of artists
573 for (int i = 1; i < (int)vecArtists.size(); i++)
575 int idArtist = AddArtist(vecArtists[i]);
577 { // added successfully, we must now add entries to the exartistalbum table
580 // always check artists (as this routine is called whenever a song is added)
581 strSQL=PrepareSQL("select * from exartistalbum where idAlbum=%i and idArtist=%i",
583 if (!m_pDS->query(strSQL.c_str())) return ;
584 if (m_pDS->num_rows() != 0)
585 bInsert = false; // already exists
589 strSQL=PrepareSQL("insert into exartistalbum (idAlbum,iPosition,idArtist) values(%i,%i,%i)",
590 idAlbum, i, idArtist);
592 m_pDS->exec(strSQL.c_str());
599 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
603 void CMusicDatabase::AddExtraGenres(const std::vector<std::string> &vecGenres, int idSong, int idAlbum, bool bCheck)
607 // add each of the genres in the vector
608 for (int i = 1; i < (int)vecGenres.size(); i++)
610 int idGenre = AddGenre(vecGenres[i]);
612 { // added successfully!
614 // first link the genre with the song
620 strSQL=PrepareSQL("select * from exgenresong where idSong=%i and idGenre=%i",
622 if (!m_pDS->query(strSQL.c_str())) return ;
623 if (m_pDS->num_rows() != 0)
624 bInsert = false; // already exists
629 strSQL=PrepareSQL("insert into exgenresong (idSong,iPosition,idGenre) values(%i,%i,%i)",
632 m_pDS->exec(strSQL.c_str());
635 // now link the genre with the album - we always check these as there's usually
636 // more than one song per album with the same extra genres
639 strSQL=PrepareSQL("select * from exgenrealbum where idAlbum=%i and idGenre=%i",
641 if (!m_pDS->query(strSQL.c_str())) return ;
642 if (m_pDS->num_rows() == 0)
645 strSQL=PrepareSQL("insert into exgenrealbum (idAlbum,iPosition,idGenre) values(%i,%i,%i)",
646 idAlbum, i, idGenre);
648 m_pDS->exec(strSQL.c_str());
656 CLog::Log(LOGERROR, "%s(%i,%i) failed", __FUNCTION__, idSong, idAlbum);
660 int CMusicDatabase::AddPath(const CStdString& strPath1)
665 CStdString strPath(strPath1);
666 if (!URIUtils::HasSlashAtEnd(strPath))
667 URIUtils::AddSlashAtEnd(strPath);
669 if (NULL == m_pDB.get()) return -1;
670 if (NULL == m_pDS.get()) return -1;
672 map <CStdString, int>::const_iterator it;
674 it = m_pathCache.find(strPath);
675 if (it != m_pathCache.end())
678 strSQL=PrepareSQL( "select * from path where strPath='%s'", strPath.c_str());
679 m_pDS->query(strSQL.c_str());
680 if (m_pDS->num_rows() == 0)
683 // doesnt exists, add it
684 strSQL=PrepareSQL("insert into path (idPath, strPath) values( NULL, '%s' )", strPath.c_str());
685 m_pDS->exec(strSQL.c_str());
687 int idPath = (int)m_pDS->lastinsertid();
688 m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
693 int idPath = m_pDS->fv("idPath").get_asInt();
694 m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
701 CLog::Log(LOGERROR, "musicdatabase:unable to addpath (%s)", strSQL.c_str());
707 CSong CMusicDatabase::GetSongFromDataset(bool bWithMusicDbPath/*=false*/)
710 song.idSong = m_pDS->fv(song_idSong).get_asInt();
711 // get the full artist string
712 song.artist = StringUtils::Split(m_pDS->fv(song_strArtist).get_asString(), g_advancedSettings.m_musicItemSeparator);
713 std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv(song_strExtraArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
714 for (unsigned int index = 0; index < extraArtists.size(); index++)
715 song.artist.push_back(extraArtists.at(index));
716 song.iArtistId = m_pDS->fv(song_idArtist).get_asInt();
717 // and the full genre string
718 song.genre.push_back(m_pDS->fv(song_strGenre).get_asString());
719 std::vector<std::string> extraGenres = StringUtils::Split(m_pDS->fv(song_strExtraGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
720 for (unsigned int index = 0; index < extraGenres.size(); index++)
721 song.genre.push_back(extraGenres.at(index));
723 song.strAlbum = m_pDS->fv(song_strAlbum).get_asString();
724 song.iAlbumId = m_pDS->fv(song_idAlbum).get_asInt();
725 song.iTrack = m_pDS->fv(song_iTrack).get_asInt() ;
726 song.iDuration = m_pDS->fv(song_iDuration).get_asInt() ;
727 song.iYear = m_pDS->fv(song_iYear).get_asInt() ;
728 song.strTitle = m_pDS->fv(song_strTitle).get_asString();
729 song.iTimesPlayed = m_pDS->fv(song_iTimesPlayed).get_asInt();
730 song.lastPlayed.SetFromDBDateTime(m_pDS->fv(song_lastplayed).get_asString());
731 song.iStartOffset = m_pDS->fv(song_iStartOffset).get_asInt();
732 song.iEndOffset = m_pDS->fv(song_iEndOffset).get_asInt();
733 song.strMusicBrainzTrackID = m_pDS->fv(song_strMusicBrainzTrackID).get_asString();
734 song.strMusicBrainzArtistID = m_pDS->fv(song_strMusicBrainzArtistID).get_asString();
735 song.strMusicBrainzAlbumID = m_pDS->fv(song_strMusicBrainzAlbumID).get_asString();
736 song.strMusicBrainzAlbumArtistID = m_pDS->fv(song_strMusicBrainzAlbumArtistID).get_asString();
737 song.strMusicBrainzTRMID = m_pDS->fv(song_strMusicBrainzTRMID).get_asString();
738 song.rating = m_pDS->fv(song_rating).get_asChar();
739 song.strComment = m_pDS->fv(song_comment).get_asString();
740 song.strThumb = m_pDS->fv(song_strThumb).get_asString();
741 song.iKaraokeNumber = m_pDS->fv(song_iKarNumber).get_asInt();
742 song.strKaraokeLyrEncoding = m_pDS->fv(song_strKarEncoding).get_asString();
743 song.iKaraokeDelay = m_pDS->fv(song_iKarDelay).get_asInt();
745 if (song.strThumb == "NONE")
746 song.strThumb.Empty();
747 // Get filename with full path
748 if (!bWithMusicDbPath)
749 URIUtils::AddFileToFolder(m_pDS->fv(song_strPath).get_asString(), m_pDS->fv(song_strFileName).get_asString(), song.strFileName);
752 CStdString strFileName=m_pDS->fv(song_strFileName).get_asString();
753 CStdString strExt=URIUtils::GetExtension(strFileName);
754 song.strFileName.Format("musicdb://3/%ld/%ld%s", m_pDS->fv(song_idAlbum).get_asInt(), m_pDS->fv(song_idSong).get_asInt(), strExt.c_str());
760 void CMusicDatabase::GetFileItemFromDataset(CFileItem* item, const CStdString& strMusicDBbasePath)
762 // get the full artist string
763 CStdString strArtist=m_pDS->fv(song_strArtist).get_asString();
764 strArtist += m_pDS->fv(song_strExtraArtists).get_asString();
765 item->GetMusicInfoTag()->SetArtist(strArtist);
766 item->GetMusicInfoTag()->SetArtistId(m_pDS->fv(song_idArtist).get_asInt());
767 // and the full genre string
768 CStdString strGenre = m_pDS->fv(song_strGenre).get_asString();
769 strGenre += m_pDS->fv(song_strExtraGenres).get_asString();
770 item->GetMusicInfoTag()->SetGenre(strGenre);
772 item->GetMusicInfoTag()->SetAlbum(m_pDS->fv(song_strAlbum).get_asString());
773 item->GetMusicInfoTag()->SetAlbumId(m_pDS->fv(song_idAlbum).get_asInt());
774 item->GetMusicInfoTag()->SetTrackAndDiskNumber(m_pDS->fv(song_iTrack).get_asInt());
775 item->GetMusicInfoTag()->SetDuration(m_pDS->fv(song_iDuration).get_asInt());
776 item->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv(song_idSong).get_asInt());
778 stTime.wYear = (WORD)m_pDS->fv(song_iYear).get_asInt();
779 item->GetMusicInfoTag()->SetReleaseDate(stTime);
780 item->GetMusicInfoTag()->SetTitle(m_pDS->fv(song_strTitle).get_asString());
781 item->SetLabel(m_pDS->fv(song_strTitle).get_asString());
782 item->m_lStartOffset = m_pDS->fv(song_iStartOffset).get_asInt();
783 item->m_lEndOffset = m_pDS->fv(song_iEndOffset).get_asInt();
784 item->GetMusicInfoTag()->SetMusicBrainzTrackID(m_pDS->fv(song_strMusicBrainzTrackID).get_asString());
785 item->GetMusicInfoTag()->SetMusicBrainzArtistID(m_pDS->fv(song_strMusicBrainzArtistID).get_asString());
786 item->GetMusicInfoTag()->SetMusicBrainzAlbumID(m_pDS->fv(song_strMusicBrainzAlbumID).get_asString());
787 item->GetMusicInfoTag()->SetMusicBrainzAlbumArtistID(m_pDS->fv(song_strMusicBrainzAlbumArtistID).get_asString());
788 item->GetMusicInfoTag()->SetMusicBrainzTRMID(m_pDS->fv(song_strMusicBrainzTRMID).get_asString());
789 item->GetMusicInfoTag()->SetRating(m_pDS->fv(song_rating).get_asChar());
790 item->GetMusicInfoTag()->SetComment(m_pDS->fv(song_comment).get_asString());
791 item->GetMusicInfoTag()->SetPlayCount(m_pDS->fv(song_iTimesPlayed).get_asInt());
792 item->GetMusicInfoTag()->SetLastPlayed(m_pDS->fv(song_lastplayed).get_asString());
793 CStdString strRealPath;
794 URIUtils::AddFileToFolder(m_pDS->fv(song_strPath).get_asString(), m_pDS->fv(song_strFileName).get_asString(), strRealPath);
795 item->GetMusicInfoTag()->SetURL(strRealPath);
796 item->GetMusicInfoTag()->SetLoaded(true);
797 CStdString strThumb=m_pDS->fv(song_strThumb).get_asString();
798 if (strThumb != "NONE")
799 item->SetThumbnailImage(strThumb);
800 // Get filename with full path
801 if (strMusicDBbasePath.IsEmpty())
803 item->SetPath(strRealPath);
807 CStdString strFileName=m_pDS->fv(song_strFileName).get_asString();
808 CStdString strExt=URIUtils::GetExtension(strFileName);
809 CStdString path; path.Format("%s%ld%s", strMusicDBbasePath.c_str(), m_pDS->fv(song_idSong).get_asInt(), strExt.c_str());
814 CAlbum CMusicDatabase::GetAlbumFromDataset(dbiplus::Dataset* pDS, bool imageURL /* = false*/)
817 album.idAlbum = pDS->fv(album_idAlbum).get_asInt();
818 album.strAlbum = pDS->fv(album_strAlbum).get_asString();
819 if (album.strAlbum.IsEmpty())
820 album.strAlbum = g_localizeStrings.Get(1050);
821 album.artist = StringUtils::Split(pDS->fv(album_strArtist).get_asString(), g_advancedSettings.m_musicItemSeparator);
822 std::vector<std::string> extraArtists = StringUtils::Split(pDS->fv(album_strExtraArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
823 for (unsigned int index = 0; index < extraArtists.size(); index++)
824 album.artist.push_back(extraArtists.at(index));
825 album.idArtist = pDS->fv(album_idArtist).get_asInt();
826 album.genre = StringUtils::Split(pDS->fv(album_strGenre).get_asString(), g_advancedSettings.m_musicItemSeparator);
827 std::vector<std::string> extraGenres = StringUtils::Split(pDS->fv(album_strExtraGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
828 for (unsigned int index = 0; index < extraGenres.size(); index++)
829 album.genre.push_back(extraGenres.at(index));
830 album.iYear = pDS->fv(album_iYear).get_asInt();
832 album.thumbURL.ParseString(pDS->fv(album_strThumbURL).get_asString());
835 CStdString strThumb = pDS->fv(album_strThumb).get_asString();
836 if (strThumb != "NONE")
837 album.thumbURL.ParseString(strThumb);
839 album.iRating = pDS->fv(album_iRating).get_asInt();
840 album.iYear = pDS->fv(album_iYear).get_asInt();
841 album.strReview = pDS->fv(album_strReview).get_asString();
842 album.styles = StringUtils::Split(pDS->fv(album_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
843 album.moods = StringUtils::Split(pDS->fv(album_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
844 album.themes = StringUtils::Split(pDS->fv(album_strThemes).get_asString(), g_advancedSettings.m_musicItemSeparator);
845 album.strLabel = pDS->fv(album_strLabel).get_asString();
846 album.strType = pDS->fv(album_strType).get_asString();
850 CArtist CMusicDatabase::GetArtistFromDataset(dbiplus::Dataset* pDS, bool needThumb)
853 artist.idArtist = pDS->fv(artist_idArtist).get_asInt();
854 artist.strArtist = pDS->fv("artist.strArtist").get_asString();
855 artist.genre = StringUtils::Split(pDS->fv(artist_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
856 artist.strBiography = pDS->fv(artist_strBiography).get_asString();
857 artist.styles = StringUtils::Split(pDS->fv(artist_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
858 artist.moods = StringUtils::Split(pDS->fv(artist_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
859 artist.strBorn = pDS->fv(artist_strBorn).get_asString();
860 artist.strFormed = pDS->fv(artist_strFormed).get_asString();
861 artist.strDied = pDS->fv(artist_strDied).get_asString();
862 artist.strDisbanded = pDS->fv(artist_strDisbanded).get_asString();
863 artist.yearsActive = StringUtils::Split(pDS->fv(artist_strYearsActive).get_asString(), g_advancedSettings.m_musicItemSeparator);
864 artist.instruments = StringUtils::Split(pDS->fv(artist_strInstruments).get_asString(), g_advancedSettings.m_musicItemSeparator);
868 artist.fanart.m_xml = pDS->fv(artist_strFanart).get_asString();
869 artist.fanart.Unpack();
870 artist.thumbURL.ParseString(pDS->fv(artist_strImage).get_asString());
876 bool CMusicDatabase::GetSongByFileName(const CStdString& strFileName, CSong& song)
881 CURL url(strFileName);
883 if (url.GetProtocol()=="musicdb")
885 CStdString strFile = URIUtils::GetFileName(strFileName);
886 URIUtils::RemoveExtension(strFile);
887 return GetSongById(atol(strFile.c_str()), song);
891 URIUtils::GetDirectory(strFileName, strPath);
892 URIUtils::AddSlashAtEnd(strPath);
894 if (NULL == m_pDB.get()) return false;
895 if (NULL == m_pDS.get()) return false;
897 DWORD crc = ComputeCRC(strFileName);
899 CStdString strSQL=PrepareSQL("select * from songview "
900 "where dwFileNameCRC='%ul' and strPath='%s'"
904 if (!m_pDS->query(strSQL.c_str())) return false;
905 int iRowsFound = m_pDS->num_rows();
911 song = GetSongFromDataset();
912 m_pDS->close(); // cleanup recordset data
917 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strFileName.c_str());
923 int CMusicDatabase::GetAlbumIdByPath(const CStdString& strPath)
927 CStdString strSQL=PrepareSQL("select distinct idAlbum from song join path on song.idPath = path.idPath where path.strPath='%s'", strPath.c_str());
928 m_pDS->query(strSQL.c_str());
932 int idAlbum = m_pDS->fv(0).get_asInt();
939 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
945 int CMusicDatabase::GetSongByArtistAndAlbumAndTitle(const CStdString& strArtist, const CStdString& strAlbum, const CStdString& strTitle)
949 CStdString strSQL=PrepareSQL("select idSong from songview "
950 "where strArtist like '%s' and strAlbum like '%s' and "
951 "strTitle like '%s'",strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
953 if (!m_pDS->query(strSQL.c_str())) return false;
954 int iRowsFound = m_pDS->num_rows();
960 int lResult = m_pDS->fv(0).get_asInt();
961 m_pDS->close(); // cleanup recordset data
966 CLog::Log(LOGERROR, "%s (%s,%s,%s) failed", __FUNCTION__, strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
972 bool CMusicDatabase::GetSongById(int idSong, CSong& song)
978 if (NULL == m_pDB.get()) return false;
979 if (NULL == m_pDS.get()) return false;
981 CStdString strSQL=PrepareSQL("select * from songview "
985 if (!m_pDS->query(strSQL.c_str())) return false;
986 int iRowsFound = m_pDS->num_rows();
992 song = GetSongFromDataset();
993 m_pDS->close(); // cleanup recordset data
998 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
1004 bool CMusicDatabase::SearchArtists(const CStdString& search, CFileItemList &artists)
1008 if (NULL == m_pDB.get()) return false;
1009 if (NULL == m_pDS.get()) return false;
1011 // Exclude "Various Artists"
1012 int idVariousArtist = AddArtist(g_localizeStrings.Get(340));
1015 if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
1016 strSQL=PrepareSQL("select * from artist "
1017 "where (strArtist like '%s%%' or strArtist like '%% %s%%') and idArtist <> %i "
1018 , search.c_str(), search.c_str(), idVariousArtist );
1020 strSQL=PrepareSQL("select * from artist "
1021 "where strArtist like '%s%%' and idArtist <> %i "
1022 , search.c_str(), idVariousArtist );
1024 if (!m_pDS->query(strSQL.c_str())) return false;
1025 if (m_pDS->num_rows() == 0)
1031 CStdString artistLabel(g_localizeStrings.Get(557)); // Artist
1032 while (!m_pDS->eof())
1035 path.Format("musicdb://2/%ld/", m_pDS->fv(0).get_asInt());
1036 CFileItemPtr pItem(new CFileItem(path, true));
1038 label.Format("[%s] %s", artistLabel.c_str(), m_pDS->fv(1).get_asString());
1039 pItem->SetLabel(label);
1040 label.Format("A %s", m_pDS->fv(1).get_asString()); // sort label is stored in the title tag
1041 pItem->GetMusicInfoTag()->SetTitle(label);
1042 pItem->SetCachedArtistThumb();
1047 m_pDS->close(); // cleanup recordset data
1052 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1058 bool CMusicDatabase::GetArbitraryQuery(const CStdString& strQuery, const CStdString& strOpenRecordSet, const CStdString& strCloseRecordSet,
1059 const CStdString& strOpenRecord, const CStdString& strCloseRecord, const CStdString& strOpenField,
1060 const CStdString& strCloseField, CStdString& strResult)
1065 if (NULL == m_pDB.get()) return false;
1066 if (NULL == m_pDS.get()) return false;
1067 CStdString strSQL=strQuery;
1068 if (!m_pDS->query(strSQL.c_str()))
1070 strResult = m_pDB->getErrorMsg();
1073 strResult=strOpenRecordSet;
1074 while (!m_pDS->eof())
1076 strResult += strOpenRecord;
1077 for (int i=0; i<m_pDS->fieldCount(); i++)
1079 strResult += strOpenField;
1080 strResult += m_pDS->fv(i).get_asString();
1081 strResult += strCloseField;
1083 strResult += strCloseRecord;
1086 strResult += strCloseRecordSet;
1092 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strQuery.c_str());
1096 if (NULL == m_pDB.get()) return false;
1097 strResult = m_pDB->getErrorMsg();
1107 bool CMusicDatabase::ArbitraryExec(const CStdString& strExec)
1111 if (NULL == m_pDB.get()) return false;
1112 if (NULL == m_pDS.get()) return false;
1113 CStdString strSQL = strExec;
1114 m_pDS->exec(strSQL.c_str());
1120 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1125 bool CMusicDatabase::GetAlbumInfo(int idAlbum, CAlbum &info, VECSONGS* songs)
1130 return false; // not in the database
1132 CStdString strSQL=PrepareSQL("select * from albumview where idAlbum = %ld", idAlbum);
1134 if (!m_pDS2->query(strSQL.c_str())) return false;
1135 int iRowsFound = m_pDS2->num_rows();
1136 if (iRowsFound != 0)
1138 info = GetAlbumFromDataset(m_pDS2.get(), true); // true to grab the thumburl rather than the thumb
1139 int idAlbumInfo = m_pDS2->fv(album_idAlbumInfo).get_asInt();
1140 m_pDS2->close(); // cleanup recordset data
1143 GetAlbumInfoSongs(idAlbumInfo, *songs);
1152 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
1158 bool CMusicDatabase::HasAlbumInfo(int idAlbum)
1163 return false; // not in the database
1165 CStdString strSQL=PrepareSQL("select * from albuminfo where idAlbum = %ld", idAlbum);
1167 if (!m_pDS2->query(strSQL.c_str())) return false;
1168 int iRowsFound = m_pDS2->num_rows();
1170 return iRowsFound > 0;
1174 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
1180 bool CMusicDatabase::DeleteAlbumInfo(int idAlbum)
1185 return false; // not in the database
1187 CStdString strSQL = PrepareSQL("delete from albuminfo where idAlbum=%i",idAlbum);
1189 if (!m_pDS2->exec(strSQL.c_str()))
1196 CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idAlbum);
1202 bool CMusicDatabase::GetArtistInfo(int idArtist, CArtist &info, bool needAll)
1207 return false; // not in the database
1209 CStdString strSQL=PrepareSQL("select * from artistinfo "
1210 "join artist on artist.idArtist=artistinfo.idArtist "
1211 "where artistinfo.idArtist = %i"
1214 if (!m_pDS2->query(strSQL.c_str())) return false;
1215 int iRowsFound = m_pDS2->num_rows();
1216 if (iRowsFound != 0)
1218 info = GetArtistFromDataset(m_pDS2.get(),needAll);
1221 strSQL=PrepareSQL("select * from discography where idArtist=%i",idArtist);
1222 m_pDS2->query(strSQL.c_str());
1223 while (!m_pDS2->eof())
1225 info.discography.push_back(make_pair(m_pDS2->fv("strAlbum").get_asString(),m_pDS2->fv("strYear").get_asString()));
1229 m_pDS2->close(); // cleanup recordset data
1237 CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idArtist);
1243 bool CMusicDatabase::DeleteArtistInfo(int idArtist)
1248 return false; // not in the database
1250 CStdString strSQL = PrepareSQL("delete from artistinfo where idArtist=%i",idArtist);
1252 if (!m_pDS2->exec(strSQL.c_str()))
1259 CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idArtist);
1265 bool CMusicDatabase::GetAlbumInfoSongs(int idAlbumInfo, VECSONGS& songs)
1269 CStdString strSQL=PrepareSQL("select * from albuminfosong "
1270 "where idAlbumInfo=%i "
1271 "order by iTrack", idAlbumInfo);
1273 if (!m_pDS2->query(strSQL.c_str())) return false;
1274 int iRowsFound = m_pDS2->num_rows();
1275 if (iRowsFound == 0) return false;
1276 while (!m_pDS2->eof())
1279 song.iTrack = m_pDS2->fv("iTrack").get_asInt();
1280 song.strTitle = m_pDS2->fv("strTitle").get_asString();
1281 song.iDuration = m_pDS2->fv("iDuration").get_asInt();
1283 songs.push_back(song);
1287 m_pDS2->close(); // cleanup recordset data
1293 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbumInfo);
1299 bool CMusicDatabase::GetTop100(const CStdString& strBaseDir, CFileItemList& items)
1303 if (NULL == m_pDB.get()) return false;
1304 if (NULL == m_pDS.get()) return false;
1306 CStdString strSQL="select * from songview "
1307 "where iTimesPlayed>0 "
1308 "order by iTimesPlayed desc "
1311 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1312 if (!m_pDS->query(strSQL.c_str())) return false;
1313 int iRowsFound = m_pDS->num_rows();
1314 if (iRowsFound == 0)
1319 items.Reserve(iRowsFound);
1320 while (!m_pDS->eof())
1322 CFileItemPtr item(new CFileItem);
1323 GetFileItemFromDataset(item.get(), strBaseDir);
1328 m_pDS->close(); // cleanup recordset data
1333 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1339 bool CMusicDatabase::GetTop100Albums(VECALBUMS& albums)
1343 albums.erase(albums.begin(), albums.end());
1344 if (NULL == m_pDB.get()) return false;
1345 if (NULL == m_pDS.get()) return false;
1347 // NOTE: The song.idAlbum is needed for the group by, as for some reason group by albumview.idAlbum doesn't work
1348 // consistently - possibly an SQLite bug, as it works fine in SQLiteSpy (v3.3.17)
1349 CStdString strSQL = "select albumview.*, sum(song.iTimesPlayed) as total, song.idAlbum from song "
1350 "join albumview on albumview.idAlbum=song.idAlbum "
1351 "where song.iTimesPlayed>0 and albumview.strAlbum != '' "
1352 "group by song.idAlbum "
1353 "order by total desc "
1356 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1357 if (!m_pDS->query(strSQL.c_str())) return false;
1358 int iRowsFound = m_pDS->num_rows();
1359 if (iRowsFound == 0)
1364 while (!m_pDS->eof())
1366 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
1370 m_pDS->close(); // cleanup recordset data
1375 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1381 bool CMusicDatabase::GetTop100AlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
1385 if (NULL == m_pDB.get()) return false;
1386 if (NULL == m_pDS.get()) return false;
1389 strSQL.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)");
1390 CLog::Log(LOGDEBUG,"GetTop100AlbumSongs() query: %s", strSQL.c_str());
1391 if (!m_pDS->query(strSQL.c_str())) return false;
1393 int iRowsFound = m_pDS->num_rows();
1394 if (iRowsFound == 0)
1400 // get data from returned rows
1401 items.Reserve(iRowsFound);
1402 while (!m_pDS->eof())
1404 CFileItemPtr item(new CFileItem);
1405 GetFileItemFromDataset(item.get(), strBaseDir);
1416 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1421 bool CMusicDatabase::GetRecentlyPlayedAlbums(VECALBUMS& albums)
1425 albums.erase(albums.begin(), albums.end());
1426 if (NULL == m_pDB.get()) return false;
1427 if (NULL == m_pDS.get()) return false;
1430 strSQL.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);
1431 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1432 if (!m_pDS->query(strSQL.c_str())) return false;
1433 int iRowsFound = m_pDS->num_rows();
1434 if (iRowsFound == 0)
1439 while (!m_pDS->eof())
1441 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
1445 m_pDS->close(); // cleanup recordset data
1450 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1456 bool CMusicDatabase::GetRecentlyPlayedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
1460 if (NULL == m_pDB.get()) return false;
1461 if (NULL == m_pDS.get()) return false;
1464 strSQL.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);
1465 CLog::Log(LOGDEBUG,"GetRecentlyPlayedAlbumSongs() query: %s", strSQL.c_str());
1466 if (!m_pDS->query(strSQL.c_str())) return false;
1468 int iRowsFound = m_pDS->num_rows();
1469 if (iRowsFound == 0)
1475 // get data from returned rows
1476 items.Reserve(iRowsFound);
1477 while (!m_pDS->eof())
1479 CFileItemPtr item(new CFileItem);
1480 GetFileItemFromDataset(item.get(), strBaseDir);
1491 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1496 bool CMusicDatabase::GetRecentlyAddedAlbums(VECALBUMS& albums, unsigned int limit)
1500 albums.erase(albums.begin(), albums.end());
1501 if (NULL == m_pDB.get()) return false;
1502 if (NULL == m_pDS.get()) return false;
1505 strSQL.Format("select * from albumview order by idAlbum desc limit %u", limit ? limit : g_advancedSettings.m_iMusicLibraryRecentlyAddedItems);
1507 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
1508 if (!m_pDS->query(strSQL.c_str())) return false;
1509 int iRowsFound = m_pDS->num_rows();
1510 if (iRowsFound == 0)
1516 while (!m_pDS->eof())
1518 albums.push_back(GetAlbumFromDataset(m_pDS.get()));
1522 m_pDS->close(); // cleanup recordset data
1527 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1533 bool CMusicDatabase::GetRecentlyAddedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items, unsigned int limit)
1537 if (NULL == m_pDB.get()) return false;
1538 if (NULL == m_pDS.get()) return false;
1541 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);
1542 CLog::Log(LOGDEBUG,"GetRecentlyAddedAlbumSongs() query: %s", strSQL.c_str());
1543 if (!m_pDS->query(strSQL.c_str())) return false;
1545 int iRowsFound = m_pDS->num_rows();
1546 if (iRowsFound == 0)
1552 // get data from returned rows
1553 items.Reserve(iRowsFound);
1554 while (!m_pDS->eof())
1556 CFileItemPtr item(new CFileItem);
1557 GetFileItemFromDataset(item.get(), strBaseDir);
1568 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1573 bool CMusicDatabase::IncrTop100CounterByFileName(const CStdString& strFileName)
1577 if (NULL == m_pDB.get()) return false;
1578 if (NULL == m_pDS.get()) return false;
1580 int idSong = GetSongIDFromPath(strFileName);
1582 CStdString sql=PrepareSQL("UPDATE song SET iTimesPlayed=iTimesPlayed+1, lastplayed=CURRENT_TIMESTAMP where idSong=%i", idSong);
1583 m_pDS->exec(sql.c_str());
1588 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strFileName.c_str());
1594 bool CMusicDatabase::GetSongsByPath(const CStdString& strPath1, CSongMap& songs, bool bAppendToMap)
1596 CStdString strPath(strPath1);
1599 if (!URIUtils::HasSlashAtEnd(strPath))
1600 URIUtils::AddSlashAtEnd(strPath);
1605 if (NULL == m_pDB.get()) return false;
1606 if (NULL == m_pDS.get()) return false;
1608 CStdString strSQL=PrepareSQL("select * from songview where strPath='%s'", strPath.c_str() );
1609 if (!m_pDS->query(strSQL.c_str())) return false;
1610 int iRowsFound = m_pDS->num_rows();
1611 if (iRowsFound == 0)
1616 while (!m_pDS->eof())
1618 CSong song = GetSongFromDataset();
1619 songs.Add(song.strFileName, song);
1623 m_pDS->close(); // cleanup recordset data
1628 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
1634 void CMusicDatabase::EmptyCache()
1636 m_artistCache.erase(m_artistCache.begin(), m_artistCache.end());
1637 m_genreCache.erase(m_genreCache.begin(), m_genreCache.end());
1638 m_pathCache.erase(m_pathCache.begin(), m_pathCache.end());
1639 m_albumCache.erase(m_albumCache.begin(), m_albumCache.end());
1640 m_thumbCache.erase(m_thumbCache.begin(), m_thumbCache.end());
1643 bool CMusicDatabase::Search(const CStdString& search, CFileItemList &items)
1645 unsigned int time = XbmcThreads::SystemClockMillis();
1646 // first grab all the artists that match
1647 SearchArtists(search, items);
1648 CLog::Log(LOGDEBUG, "%s Artist search in %i ms",
1649 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
1651 // then albums that match
1652 SearchAlbums(search, items);
1653 CLog::Log(LOGDEBUG, "%s Album search in %i ms",
1654 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
1656 // and finally songs
1657 SearchSongs(search, items);
1658 CLog::Log(LOGDEBUG, "%s Songs search in %i ms",
1659 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
1663 bool CMusicDatabase::SearchSongs(const CStdString& search, CFileItemList &items)
1667 if (NULL == m_pDB.get()) return false;
1668 if (NULL == m_pDS.get()) return false;
1671 if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
1672 strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' or strTitle like '%% %s%%' limit 1000", search.c_str(), search.c_str());
1674 strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' limit 1000", search.c_str());
1676 if (!m_pDS->query(strSQL.c_str())) return false;
1677 if (m_pDS->num_rows() == 0) return false;
1679 CStdString songLabel = g_localizeStrings.Get(179); // Song
1680 while (!m_pDS->eof())
1682 CFileItemPtr item(new CFileItem);
1683 GetFileItemFromDataset(item.get(), "musicdb://4/");
1693 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1699 bool CMusicDatabase::SearchAlbums(const CStdString& search, CFileItemList &albums)
1703 if (NULL == m_pDB.get()) return false;
1704 if (NULL == m_pDS.get()) return false;
1707 if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
1708 strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%' or strAlbum like '%% %s%%'", search.c_str(), search.c_str());
1710 strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%'", search.c_str());
1712 if (!m_pDS->query(strSQL.c_str())) return false;
1714 CStdString albumLabel(g_localizeStrings.Get(558)); // Album
1715 while (!m_pDS->eof())
1717 CAlbum album = GetAlbumFromDataset(m_pDS.get());
1719 path.Format("musicdb://3/%ld/", album.idAlbum);
1720 CFileItemPtr pItem(new CFileItem(path, album));
1722 label.Format("[%s] %s", albumLabel.c_str(), album.strAlbum);
1723 pItem->SetLabel(label);
1724 label.Format("B %s", album.strAlbum); // sort label is stored in the title tag
1725 pItem->GetMusicInfoTag()->SetTitle(label);
1729 m_pDS->close(); // cleanup recordset data
1734 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
1739 int CMusicDatabase::SetAlbumInfo(int idAlbum, const CAlbum& album, const VECSONGS& songs, bool bTransaction)
1744 if (NULL == m_pDB.get()) return -1;
1745 if (NULL == m_pDS.get()) return -1;
1750 // and also the multiple genre string into single genres.
1751 std::vector<std::string> extraGenres;
1752 for (unsigned int index = 1; index < album.genre.size(); index++)
1753 extraGenres.push_back(album.genre.at(index));
1754 CStdString strExtraGenres = StringUtils::Join(extraGenres, g_advancedSettings.m_musicItemSeparator);
1757 if (!album.genre.empty())
1758 idGenre = AddGenre(album.genre[0]);
1760 // delete any album info we may have
1761 strSQL=PrepareSQL("delete from albuminfo where idAlbum=%i", idAlbum);
1762 m_pDS->exec(strSQL.c_str());
1764 // insert the albuminfo
1765 strSQL=PrepareSQL("insert into albuminfo (idAlbumInfo,idAlbum,idGenre,strExtraGenres,strMoods,strStyles,strThemes,strReview,strImage,strLabel,strType,iRating,iYear) values(NULL,%i,%i,'%s','%s','%s','%s','%s','%s','%s','%s',%i,%i)",
1766 idAlbum, idGenre, strExtraGenres.c_str(),
1767 StringUtils::Join(album.moods, g_advancedSettings.m_musicItemSeparator).c_str(),
1768 StringUtils::Join(album.styles, g_advancedSettings.m_musicItemSeparator).c_str(),
1769 StringUtils::Join(album.themes, g_advancedSettings.m_musicItemSeparator).c_str(),
1770 album.strReview.c_str(),
1771 album.thumbURL.m_xml.c_str(),
1772 album.strLabel.c_str(),
1773 album.strType.c_str(),
1776 m_pDS->exec(strSQL.c_str());
1777 int idAlbumInfo = (int)m_pDS->lastinsertid();
1779 if (SetAlbumInfoSongs(idAlbumInfo, songs))
1782 CommitTransaction();
1786 if (bTransaction) // icky
1787 RollbackTransaction();
1795 CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
1799 RollbackTransaction();
1804 int CMusicDatabase::SetArtistInfo(int idArtist, const CArtist& artist)
1809 if (NULL == m_pDB.get()) return -1;
1810 if (NULL == m_pDS.get()) return -1;
1812 // delete any artist info we may have
1813 strSQL=PrepareSQL("delete from artistinfo where idArtist=%i", idArtist);
1814 m_pDS->exec(strSQL.c_str());
1815 strSQL=PrepareSQL("delete from discography where idArtist=%i", idArtist);
1816 m_pDS->exec(strSQL.c_str());
1818 // insert the artistinfo
1819 strSQL=PrepareSQL("insert into artistinfo (idArtistInfo,idArtist,strBorn,strFormed,strGenres,strMoods,strStyles,strInstruments,strBiography,strDied,strDisbanded,strYearsActive,strImage,strFanart) values(NULL,%i,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
1820 idArtist, artist.strBorn.c_str(),
1821 artist.strFormed.c_str(),
1822 StringUtils::Join(artist.genre, g_advancedSettings.m_musicItemSeparator).c_str(),
1823 StringUtils::Join(artist.moods, g_advancedSettings.m_musicItemSeparator).c_str(),
1824 StringUtils::Join(artist.styles, g_advancedSettings.m_musicItemSeparator).c_str(),
1825 StringUtils::Join(artist.instruments, g_advancedSettings.m_musicItemSeparator).c_str(),
1826 artist.strBiography.c_str(),
1827 artist.strDied.c_str(),
1828 artist.strDisbanded.c_str(),
1829 StringUtils::Join(artist.yearsActive, g_advancedSettings.m_musicItemSeparator).c_str(),
1830 artist.thumbURL.m_xml.c_str(),
1831 artist.fanart.m_xml.c_str());
1832 m_pDS->exec(strSQL.c_str());
1833 int idArtistInfo = (int)m_pDS->lastinsertid();
1834 for (unsigned int i=0;i<artist.discography.size();++i)
1836 strSQL=PrepareSQL("insert into discography (idArtist,strAlbum,strYear) values (%i,'%s','%s')",idArtist,artist.discography[i].first.c_str(),artist.discography[i].second.c_str());
1837 m_pDS->exec(strSQL.c_str());
1840 return idArtistInfo;
1844 CLog::Log(LOGERROR, "%s - failed with query (%s)", __FUNCTION__, strSQL.c_str());
1851 bool CMusicDatabase::SetAlbumInfoSongs(int idAlbumInfo, const VECSONGS& songs)
1856 if (NULL == m_pDB.get()) return false;
1857 if (NULL == m_pDS.get()) return false;
1859 strSQL=PrepareSQL("delete from albuminfosong where idAlbumInfo=%i", idAlbumInfo);
1860 m_pDS->exec(strSQL.c_str());
1862 for (int i = 0; i < (int)songs.size(); i++)
1864 CSong song = songs[i];
1865 strSQL=PrepareSQL("insert into albuminfosong (idAlbumInfoSong,idAlbumInfo,iTrack,strTitle,iDuration) values(NULL,%i,%i,'%s',%i)",
1868 song.strTitle.c_str(),
1870 m_pDS->exec(strSQL.c_str());
1876 CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
1882 bool CMusicDatabase::CleanupSongsByIds(const CStdString &strSongIds)
1886 if (NULL == m_pDB.get()) return false;
1887 if (NULL == m_pDS.get()) return false;
1888 // ok, now find all idSong's
1889 CStdString strSQL=PrepareSQL("select * from song join path on song.idPath = path.idPath where song.idSong in %s", strSongIds.c_str());
1890 if (!m_pDS->query(strSQL.c_str())) return false;
1891 int iRowsFound = m_pDS->num_rows();
1892 if (iRowsFound == 0)
1897 CStdString strSongsToDelete = "";
1898 while (!m_pDS->eof())
1899 { // get the full song path
1900 CStdString strFileName;
1901 URIUtils::AddFileToFolder(m_pDS->fv("path.strPath").get_asString(), m_pDS->fv("song.strFileName").get_asString(), strFileName);
1903 // Special case for streams inside an ogg file. (oggstream)
1904 // The last dir in the path is the ogg file that
1905 // contains the stream, so test if its there
1906 CStdString strExtension=URIUtils::GetExtension(strFileName);
1907 if (strExtension==".oggstream" || strExtension==".nsfstream")
1909 CStdString strFileAndPath=strFileName;
1910 URIUtils::GetDirectory(strFileAndPath, strFileName);
1911 // we are dropping back to a file, so remove the slash at end
1912 URIUtils::RemoveSlashAtEnd(strFileName);
1915 if (!CFile::Exists(strFileName))
1916 { // file no longer exists, so add to deletion list
1917 strSongsToDelete += m_pDS->fv("song.idSong").get_asString() + ",";
1923 if ( ! strSongsToDelete.IsEmpty() )
1925 strSongsToDelete = "(" + strSongsToDelete.TrimRight(",") + ")";
1926 // ok, now delete these songs + all references to them from the exartistsong and exgenresong tables
1927 strSQL = "delete from song where idSong in " + strSongsToDelete;
1928 m_pDS->exec(strSQL.c_str());
1929 strSQL = "delete from exartistsong where idSong in " + strSongsToDelete;
1930 m_pDS->exec(strSQL.c_str());
1931 strSQL = "delete from exgenresong where idSong in " + strSongsToDelete;
1932 m_pDS->exec(strSQL.c_str());
1933 strSQL = "delete from karaokedata where idSong in " + strSongsToDelete;
1934 m_pDS->exec(strSQL.c_str());
1941 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongsFromPaths()");
1947 bool CMusicDatabase::CleanupSongs()
1951 // run through all songs and get all unique path ids
1953 for (int i=0;;i+=iLIMIT)
1955 CStdString strSQL=PrepareSQL("select song.idSong from song order by song.idSong limit %i offset %i",iLIMIT,i);
1956 if (!m_pDS->query(strSQL.c_str())) return false;
1957 int iRowsFound = m_pDS->num_rows();
1958 // keep going until no rows are left!
1959 if (iRowsFound == 0)
1964 CStdString strSongIds = "(";
1965 while (!m_pDS->eof())
1967 strSongIds += m_pDS->fv("song.idSong").get_asString() + ",";
1971 strSongIds.TrimRight(",");
1973 CLog::Log(LOGDEBUG,"Checking songs from song ID list: %s",strSongIds.c_str());
1974 if (!CleanupSongsByIds(strSongIds)) return false;
1980 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongs()");
1985 bool CMusicDatabase::CleanupAlbums()
1989 // This must be run AFTER songs have been cleaned up
1990 // delete albums with no reference to songs
1991 CStdString strSQL = "select * from album where album.idAlbum not in (select idAlbum from song)";
1992 if (!m_pDS->query(strSQL.c_str())) return false;
1993 int iRowsFound = m_pDS->num_rows();
1994 if (iRowsFound == 0)
1999 CStdString strAlbumIds = "(";
2000 while (!m_pDS->eof())
2002 strAlbumIds += m_pDS->fv("album.idAlbum").get_asString() + ",";
2007 strAlbumIds.TrimRight(",");
2009 // ok, now we can delete them and the references in the exartistalbum, exgenrealbum and albuminfo tables
2010 strSQL = "delete from album where idAlbum in " + strAlbumIds;
2011 m_pDS->exec(strSQL.c_str());
2012 strSQL = "delete from albuminfo where idAlbum in " + strAlbumIds;
2013 m_pDS->exec(strSQL.c_str());
2014 strSQL = "delete from exartistalbum where idAlbum in " + strAlbumIds;
2015 m_pDS->exec(strSQL.c_str());
2016 strSQL = "delete from exgenrealbum where idAlbum in " + strAlbumIds;
2017 m_pDS->exec(strSQL.c_str());
2022 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupAlbums()");
2027 bool CMusicDatabase::CleanupPaths()
2031 // needs to be done AFTER the songs and albums have been cleaned up.
2032 // we can happily delete any path that has no reference to a song
2033 // but we must keep all paths that have been scanned that may contain songs in subpaths
2035 // first create a temporary table of song paths
2036 m_pDS->exec("CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512))\n");
2037 m_pDS->exec("INSERT INTO songpaths select idPath,strPath from path where idPath in (select idPath from song)\n");
2039 // grab all paths that aren't immediately connected with a song
2040 CStdString sql = "select * from path where idPath not in (select idPath from song)";
2041 if (!m_pDS->query(sql.c_str())) return false;
2042 int iRowsFound = m_pDS->num_rows();
2043 if (iRowsFound == 0)
2048 // and construct a list to delete
2049 CStdString deleteSQL;
2050 while (!m_pDS->eof())
2052 // anything that isn't a parent path of a song path is to be deleted
2053 CStdString path = m_pDS->fv("strPath").get_asString();
2054 CStdString sql = PrepareSQL("select count(idPath) from songpaths where SUBSTR(strPath,1,%i)='%s'", StringUtils::utf8_strlen(path.c_str()), path.c_str());
2055 if (m_pDS2->query(sql.c_str()) && m_pDS2->num_rows() == 1 && m_pDS2->fv(0).get_asInt() == 0)
2056 deleteSQL += PrepareSQL("%i,", m_pDS->fv("idPath").get_asInt()); // nothing found, so delete
2062 if ( ! deleteSQL.IsEmpty() )
2064 deleteSQL = "DELETE FROM path WHERE idPath IN (" + deleteSQL.TrimRight(',') + ")";
2065 // do the deletion, and drop our temp table
2066 m_pDS->exec(deleteSQL.c_str());
2068 m_pDS->exec("drop table songpaths");
2073 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupPaths() or was aborted");
2078 bool CMusicDatabase::CleanupThumbs()
2082 // needs to be done AFTER the songs have been cleaned up.
2083 // we can happily delete any thumb that has no reference to a song
2084 CStdString strSQL = "select * from thumb where idThumb not in (select idThumb from song) and idThumb not in (select idThumb from album)";
2085 if (!m_pDS->query(strSQL.c_str())) return false;
2086 int iRowsFound = m_pDS->num_rows();
2087 if (iRowsFound == 0)
2093 CStdString strThumbsDir = g_settings.GetMusicThumbFolder();
2094 while (!m_pDS->eof())
2096 CStdString strThumb = m_pDS->fv("strThumb").get_asString();
2097 if (strThumb.Left(strThumbsDir.size()) == strThumbsDir)
2098 { // only delete cached thumbs
2099 CTextureCache::Get().ClearCachedImage(strThumb, true);
2103 // clear the thumb cache
2104 //URIUtils::ThumbCacheClear();
2105 //g_directoryCache.ClearMusicThumbCache();
2106 // now we can delete
2108 strSQL = "delete from thumb where idThumb not in (select idThumb from song) and idThumb not in (select idThumb from album)";
2109 m_pDS->exec(strSQL.c_str());
2114 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupThumbs() or was aborted");
2119 bool CMusicDatabase::CleanupArtists()
2123 // (nested queries by Bobbin007)
2124 // must be executed AFTER the song, exartistsong, album and exartistalbum tables are cleaned.
2125 // don't delete the "Various Artists" string
2126 CStdString strVariousArtists = g_localizeStrings.Get(340);
2127 int idVariousArtists = AddArtist(strVariousArtists);
2128 CStdString strSQL = "delete from artist where idArtist not in (select idArtist from song)";
2129 strSQL += " and idArtist not in (select idArtist from exartistsong)";
2130 strSQL += " and idArtist not in (select idArtist from album)";
2131 strSQL += " and idArtist not in (select idArtist from exartistalbum)";
2133 strSQL2.Format(" and idArtist<>%i", idVariousArtists);
2135 m_pDS->exec(strSQL.c_str());
2136 m_pDS->exec("delete from artistinfo where idArtist not in (select idArtist from artist)");
2137 m_pDS->exec("delete from discography where idArtist not in (select idArtist from artist)");
2142 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupArtists() or was aborted");
2147 bool CMusicDatabase::CleanupGenres()
2151 // Cleanup orphaned genres (ie those that don't belong to a song or an albuminfo entry)
2152 // (nested queries by Bobbin007)
2153 // Must be executed AFTER the song, exgenresong, albuminfo and exgenrealbum tables have been cleaned.
2154 CStdString strSQL = "delete from genre where idGenre not in (select idGenre from song) and";
2155 strSQL += " idGenre not in (select idGenre from exgenresong) and";
2156 strSQL += " idGenre not in (select idGenre from albuminfo) and";
2157 strSQL += " idGenre not in (select idGenre from exgenrealbum)";
2158 m_pDS->exec(strSQL.c_str());
2163 CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupGenres() or was aborted");
2168 bool CMusicDatabase::CleanupOrphanedItems()
2170 // paths aren't cleaned up here - they're cleaned up in RemoveSongsFromPath()
2171 if (NULL == m_pDB.get()) return false;
2172 if (NULL == m_pDS.get()) return false;
2173 if (!CleanupAlbums()) return false;
2174 if (!CleanupArtists()) return false;
2175 if (!CleanupGenres()) return false;
2176 if (!CleanupThumbs()) return false;
2180 int CMusicDatabase::Cleanup(CGUIDialogProgress *pDlgProgress)
2182 if (NULL == m_pDB.get()) return ERROR_DATABASE;
2183 if (NULL == m_pDS.get()) return ERROR_DATABASE;
2185 unsigned int time = XbmcThreads::SystemClockMillis();
2186 CLog::Log(LOGNOTICE, "%s: Starting musicdatabase cleanup ..", __FUNCTION__);
2188 // first cleanup any songs with invalid paths
2191 pDlgProgress->SetHeading(700);
2192 pDlgProgress->SetLine(0, "");
2193 pDlgProgress->SetLine(1, 318);
2194 pDlgProgress->SetLine(2, 330);
2195 pDlgProgress->SetPercentage(0);
2196 pDlgProgress->StartModal();
2197 pDlgProgress->ShowProgressBar(true);
2199 if (!CleanupSongs())
2201 RollbackTransaction();
2202 return ERROR_REORG_SONGS;
2204 // then the albums that are not linked to a song or to albuminfo, or whose path is removed
2207 pDlgProgress->SetLine(1, 326);
2208 pDlgProgress->SetPercentage(20);
2209 pDlgProgress->Progress();
2211 if (!CleanupAlbums())
2213 RollbackTransaction();
2214 return ERROR_REORG_ALBUM;
2219 pDlgProgress->SetLine(1, 324);
2220 pDlgProgress->SetPercentage(40);
2221 pDlgProgress->Progress();
2223 if (!CleanupPaths() || !CleanupThumbs())
2225 RollbackTransaction();
2226 return ERROR_REORG_PATH;
2228 // and finally artists + genres
2231 pDlgProgress->SetLine(1, 320);
2232 pDlgProgress->SetPercentage(60);
2233 pDlgProgress->Progress();
2235 if (!CleanupArtists())
2237 RollbackTransaction();
2238 return ERROR_REORG_ARTIST;
2242 pDlgProgress->SetLine(1, 322);
2243 pDlgProgress->SetPercentage(80);
2244 pDlgProgress->Progress();
2246 if (!CleanupGenres())
2248 RollbackTransaction();
2249 return ERROR_REORG_GENRE;
2251 // commit transaction
2254 pDlgProgress->SetLine(1, 328);
2255 pDlgProgress->SetPercentage(90);
2256 pDlgProgress->Progress();
2258 if (!CommitTransaction())
2260 RollbackTransaction();
2261 return ERROR_WRITING_CHANGES;
2263 // and compress the database
2266 pDlgProgress->SetLine(1, 331);
2267 pDlgProgress->SetPercentage(100);
2268 pDlgProgress->Progress();
2270 time = XbmcThreads::SystemClockMillis() - time;
2271 CLog::Log(LOGNOTICE, "%s: Cleaning musicdatabase done. Operation took %s", __FUNCTION__, StringUtils::SecondsToTimeString(time / 1000).c_str());
2273 if (!Compress(false))
2275 return ERROR_COMPRESSING;
2280 void CMusicDatabase::DeleteAlbumInfo()
2282 // open our database
2284 if (NULL == m_pDB.get()) return ;
2285 if (NULL == m_pDS.get()) return ;
2287 // If we are scanning for music info in the background,
2288 // other writing access to the database is prohibited.
2289 CGUIDialogMusicScan* dlgMusicScan = (CGUIDialogMusicScan*)g_windowManager.GetWindow(WINDOW_DIALOG_MUSIC_SCAN);
2290 if (dlgMusicScan->IsDialogRunning())
2292 CGUIDialogOK::ShowAndGetInput(189, 14057, 0, 0);
2296 CStdString strSQL="select * from albuminfo,album,artist where and albuminfo.idAlbum=album.idAlbum and album.idArtist=artist.idArtist order by album.strAlbum";
2297 if (!m_pDS->query(strSQL.c_str())) return ;
2298 int iRowsFound = m_pDS->num_rows();
2299 if (iRowsFound == 0)
2302 CGUIDialogOK::ShowAndGetInput(313, 425, 0, 0);
2304 vector<CAlbumCache> vecAlbums;
2305 while (!m_pDS->eof())
2308 album.idAlbum = m_pDS->fv("album.idAlbum").get_asInt() ;
2309 album.strAlbum = m_pDS->fv("album.strAlbum").get_asString();
2310 album.artist = StringUtils::Split(m_pDS->fv("artist.strArtist").get_asString(), g_advancedSettings.m_musicItemSeparator);
2311 std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv("album.strExtraArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
2312 for (unsigned int index = 0; index < extraArtists.size(); index++)
2313 album.artist.push_back(extraArtists.at(index));
2314 vecAlbums.push_back(album);
2319 // Show a selectdialog that the user can select the albuminfo to delete
2320 CGUIDialogSelect *pDlg = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
2323 pDlg->SetHeading(g_localizeStrings.Get(181).c_str());
2325 for (int i = 0; i < (int)vecAlbums.size(); ++i)
2327 CMusicDatabase::CAlbumCache& album = vecAlbums[i];
2328 pDlg->Add(album.strAlbum + " - " + StringUtils::Join(album.artist, g_advancedSettings.m_musicItemSeparator));
2332 // and wait till user selects one
2333 int iSelectedAlbum = pDlg->GetSelectedLabel();
2334 if (iSelectedAlbum < 0)
2336 vecAlbums.erase(vecAlbums.begin(), vecAlbums.end());
2340 CAlbumCache& album = vecAlbums[iSelectedAlbum];
2341 strSQL=PrepareSQL("delete from albuminfo where albuminfo.idAlbum=%i", album.idAlbum);
2342 if (!m_pDS->exec(strSQL.c_str())) return ;
2344 vecAlbums.erase(vecAlbums.begin(), vecAlbums.end());
2348 bool CMusicDatabase::LookupCDDBInfo(bool bRequery/*=false*/)
2350 #ifdef HAS_DVD_DRIVE
2351 if (!g_guiSettings.GetBool("audiocds.usecddb"))
2354 // check network connectivity
2355 if (!g_application.getNetwork().IsAvailable())
2358 // Get information for the inserted disc
2359 CCdInfo* pCdInfo = g_mediaManager.GetCdInfo();
2360 if (pCdInfo == NULL)
2363 // If the disc has no tracks, we are finished here.
2364 int nTracks = pCdInfo->GetTrackCount();
2368 // Delete old info if any
2372 strFile.Format("%x.cddb", pCdInfo->GetCddbDiscId());
2373 CFile::Delete(URIUtils::AddFileToFolder(g_settings.GetCDDBFolder(), strFile));
2378 cddb.setCacheDir(g_settings.GetCDDBFolder());
2380 // Do we have to look for cddb information
2381 if (pCdInfo->HasCDDBInfo() && !cddb.isCDCached(pCdInfo))
2383 CGUIDialogProgress* pDialogProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
2384 CGUIDialogSelect *pDlgSelect = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
2386 if (!pDialogProgress) return false;
2387 if (!pDlgSelect) return false;
2389 // Show progress dialog if we have to connect to freedb.org
2390 pDialogProgress->SetHeading(255); //CDDB
2391 pDialogProgress->SetLine(0, ""); // Querying freedb for CDDB info
2392 pDialogProgress->SetLine(1, 256);
2393 pDialogProgress->SetLine(2, "");
2394 pDialogProgress->ShowProgressBar(false);
2395 pDialogProgress->StartModal();
2397 // get cddb information
2398 if (!cddb.queryCDinfo(pCdInfo))
2400 pDialogProgress->Close();
2401 int lasterror = cddb.getLastError();
2403 // Have we found more then on match in cddb for this disc,...
2404 if (lasterror == E_WAIT_FOR_INPUT)
2406 // ...yes, show the matches found in a select dialog
2407 // and let the user choose an entry.
2408 pDlgSelect->Reset();
2409 pDlgSelect->SetHeading(255);
2413 CStdString strTitle = cddb.getInexactTitle(i);
2414 if (strTitle == "") break;
2416 CStdString strArtist = cddb.getInexactArtist(i);
2417 if (!strArtist.IsEmpty())
2418 strTitle += " - " + strArtist;
2420 pDlgSelect->Add(strTitle);
2423 pDlgSelect->DoModal();
2425 // Has the user selected a match...
2426 int iSelectedCD = pDlgSelect->GetSelectedLabel();
2427 if (iSelectedCD >= 0)
2429 // ...query cddb for the inexact match
2430 if (!cddb.queryCDinfo(pCdInfo, 1 + iSelectedCD))
2431 pCdInfo->SetNoCDDBInfo();
2434 pCdInfo->SetNoCDDBInfo();
2436 else if (lasterror == E_NO_MATCH_FOUND)
2438 pCdInfo->SetNoCDDBInfo();
2442 pCdInfo->SetNoCDDBInfo();
2443 // ..no, an error occured, display it to the user
2444 CStdString strErrorText;
2445 strErrorText.Format("[%d] %s", cddb.getLastError(), cddb.getLastErrorText());
2446 CGUIDialogOK::ShowAndGetInput(255, 257, strErrorText, 0);
2448 } // if ( !cddb.queryCDinfo( pCdInfo ) )
2450 pDialogProgress->Close();
2451 } // if (pCdInfo->HasCDDBInfo() && g_settings.m_bUseCDDB)
2453 // Filling the file items with cddb info happens in CMusicInfoTagLoaderCDDA
2455 return pCdInfo->HasCDDBInfo();
2461 void CMusicDatabase::DeleteCDDBInfo()
2463 #ifdef HAS_DVD_DRIVE
2464 CFileItemList items;
2465 if (!CDirectory::GetDirectory(g_settings.GetCDDBFolder(), items, ".cddb", false))
2467 CGUIDialogOK::ShowAndGetInput(313, 426, 0, 0);
2470 // Show a selectdialog that the user can select the albuminfo to delete
2471 CGUIDialogSelect *pDlg = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
2474 pDlg->SetHeading(g_localizeStrings.Get(181).c_str());
2477 map<ULONG, CStdString> mapCDDBIds;
2478 for (int i = 0; i < items.Size(); ++i)
2480 if (items[i]->m_bIsFolder)
2483 CStdString strFile = URIUtils::GetFileName(items[i]->GetPath());
2484 strFile.Delete(strFile.size() - 5, 5);
2485 ULONG lDiscId = strtoul(strFile.c_str(), NULL, 16);
2487 cddb.setCacheDir(g_settings.GetCDDBFolder());
2489 if (!cddb.queryCache(lDiscId))
2492 CStdString strDiskTitle, strDiskArtist;
2493 cddb.getDiskTitle(strDiskTitle);
2494 cddb.getDiskArtist(strDiskArtist);
2497 if (strDiskArtist.IsEmpty())
2500 str = strDiskTitle + " - " + strDiskArtist;
2503 mapCDDBIds.insert(pair<ULONG, CStdString>(lDiscId, str));
2509 // and wait till user selects one
2510 int iSelectedAlbum = pDlg->GetSelectedLabel();
2511 if (iSelectedAlbum < 0)
2513 mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
2517 CStdString strSelectedAlbum = pDlg->GetSelectedLabelText();
2518 map<ULONG, CStdString>::iterator it;
2519 for (it = mapCDDBIds.begin();it != mapCDDBIds.end();it++)
2521 if (it->second == strSelectedAlbum)
2524 strFile.Format("%x.cddb", it->first);
2525 CFile::Delete(URIUtils::AddFileToFolder(g_settings.GetCDDBFolder(), strFile));
2529 mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
2534 void CMusicDatabase::Clean()
2536 // If we are scanning for music info in the background,
2537 // other writing access to the database is prohibited.
2538 CGUIDialogMusicScan* dlgMusicScan = (CGUIDialogMusicScan*)g_windowManager.GetWindow(WINDOW_DIALOG_MUSIC_SCAN);
2539 if (dlgMusicScan->IsDialogRunning())
2541 CGUIDialogOK::ShowAndGetInput(189, 14057, 0, 0);
2545 if (CGUIDialogYesNo::ShowAndGetInput(313, 333, 0, 0))
2547 CGUIDialogProgress* dlgProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
2550 CMusicDatabase musicdatabase;
2551 if (musicdatabase.Open())
2553 int iReturnString = musicdatabase.Cleanup(dlgProgress);
2554 musicdatabase.Close();
2556 if (iReturnString != ERROR_OK)
2558 CGUIDialogOK::ShowAndGetInput(313, iReturnString, 0, 0);
2561 dlgProgress->Close();
2566 bool CMusicDatabase::GetGenresNav(const CStdString& strBaseDir, CFileItemList& items)
2570 if (NULL == m_pDB.get()) return false;
2571 if (NULL == m_pDS.get()) return false;
2573 // get primary genres for songs
2574 CStdString strSQL="select * from genre "
2575 "where (idGenre IN ("
2576 "select song.idGenre from song) "
2578 "select exgenresong.idGenre from exgenresong)) ";
2580 // block null strings
2581 strSQL += " and genre.strGenre != \"\"";
2584 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2585 if (!m_pDS->query(strSQL.c_str())) return false;
2586 int iRowsFound = m_pDS->num_rows();
2587 if (iRowsFound == 0)
2593 // get data from returned rows
2594 while (!m_pDS->eof())
2596 CFileItemPtr pItem(new CFileItem(m_pDS->fv("strGenre").get_asString()));
2597 pItem->GetMusicInfoTag()->SetGenre(m_pDS->fv("strGenre").get_asString());
2598 pItem->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv("idGenre").get_asInt());
2600 strDir.Format("%ld/", m_pDS->fv("idGenre").get_asInt());
2601 pItem->SetPath(strBaseDir + strDir);
2602 pItem->m_bIsFolder=true;
2615 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2620 bool CMusicDatabase::GetYearsNav(const CStdString& strBaseDir, CFileItemList& items)
2624 if (NULL == m_pDB.get()) return false;
2625 if (NULL == m_pDS.get()) return false;
2627 // get years from album list
2628 CStdString strSQL="select distinct iYear from album where iYear <> 0";
2631 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2632 if (!m_pDS->query(strSQL.c_str())) return false;
2633 int iRowsFound = m_pDS->num_rows();
2634 if (iRowsFound == 0)
2640 // get data from returned rows
2641 while (!m_pDS->eof())
2643 CFileItemPtr pItem(new CFileItem(m_pDS->fv("iYear").get_asString()));
2645 stTime.wYear = (WORD)m_pDS->fv("iYear").get_asInt();
2646 pItem->GetMusicInfoTag()->SetReleaseDate(stTime);
2648 strDir.Format("%ld/", m_pDS->fv("iYear").get_asInt());
2649 pItem->SetPath(strBaseDir + strDir);
2650 pItem->m_bIsFolder=true;
2663 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2668 bool CMusicDatabase::GetAlbumsByYear(const CStdString& strBaseDir, CFileItemList& items, int year)
2670 CStdString where = PrepareSQL("where iYear=%ld", year);
2672 return GetAlbumsByWhere(strBaseDir, where, "", items);
2675 bool CMusicDatabase::GetArtistsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, bool albumArtistsOnly)
2677 if (NULL == m_pDB.get()) return false;
2678 if (NULL == m_pDS.get()) return false;
2681 if (NULL == m_pDB.get()) return false;
2682 if (NULL == m_pDS.get()) return false;
2684 unsigned int time = XbmcThreads::SystemClockMillis();
2686 CStdString strSQL = "select * from artist where (idArtist IN ";
2690 if (!albumArtistsOnly) // show all artists in this case (ie those linked to a song)
2692 "select song.idArtist from song" // All primary artists linked to a song
2696 "select exartistsong.idArtist from exartistsong" // All extra artists linked to a song
2700 // and always show any artists linked to an album (may be different from above due to album artist tag)
2702 "select album.idArtist from album" // All primary artists linked to an album
2706 "select exartistalbum.idArtist from exartistalbum "; // All extra artists linked to an album
2707 if (albumArtistsOnly)
2708 strSQL += "join album on album.idAlbum = exartistalbum.idAlbum " // if we're hiding compilation artists,
2709 "where album.strExtraArtists != ''"; // then exclude those that have no extra artists
2714 { // same statements as above, but limit to the specified genre
2715 // in this case we show the whole lot always - there is no limitation to just album artists
2716 if (!albumArtistsOnly) // show all artists in this case (ie those linked to a song)
2717 strSQL+=PrepareSQL("("
2718 "select song.idArtist from song " // All primary artists linked to primary genres
2719 "where song.idGenre=%i"
2723 "select song.idArtist from song " // All primary artists linked to extra genres
2724 "join exgenresong on song.idSong=exgenresong.idSong "
2725 "where exgenresong.idGenre=%i"
2729 "select exartistsong.idArtist from exartistsong " // All extra artists linked to extra genres
2730 "join song on exartistsong.idSong=song.idSong "
2731 "join exgenresong on song.idSong=exgenresong.idSong "
2732 "where exgenresong.idGenre=%i"
2736 "select exartistsong.idArtist from exartistsong " // All extra artists linked to primary genres
2737 "join song on exartistsong.idSong=song.idSong "
2738 "where song.idGenre=%i"
2741 , idGenre, idGenre, idGenre, idGenre);
2742 // and add any artists linked to an album (may be different from above due to album artist tag)
2743 strSQL += PrepareSQL("("
2744 "select album.idArtist from album " // All primary album artists linked to primary genres
2745 "where album.idGenre=%i"
2749 "select album.idArtist from album " // All primary album artists linked to extra genres
2750 "join exgenrealbum on album.idAlbum=exgenrealbum.idAlbum "
2751 "where exgenrealbum.idGenre=%i"
2755 "select exartistalbum.idArtist from exartistalbum " // All extra album artists linked to extra genres
2756 "join album on exartistalbum.idAlbum=album.idAlbum "
2757 "join exgenrealbum on album.idAlbum=exgenrealbum.idAlbum "
2758 "where exgenrealbum.idGenre=%i"
2762 "select exartistalbum.idArtist from exartistalbum " // All extra album artists linked to primary genres
2763 "join album on exartistalbum.idAlbum=album.idAlbum "
2764 "where album.idGenre=%i"
2766 ")", idGenre, idGenre, idGenre, idGenre);
2769 // remove the null string
2770 strSQL += " and artist.strArtist != \"\"";
2771 // and the various artist entry if applicable
2772 if (!albumArtistsOnly)
2774 CStdString strVariousArtists = g_localizeStrings.Get(340);
2775 int idVariousArtists = AddArtist(strVariousArtists);
2776 strSQL+=PrepareSQL(" and artist.idArtist<>%i", idVariousArtists);
2780 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
2781 if (!m_pDS->query(strSQL.c_str())) return false;
2782 int iRowsFound = m_pDS->num_rows();
2783 if (iRowsFound == 0)
2789 items.Reserve(iRowsFound);
2791 // get data from returned rows
2792 while (!m_pDS->eof())
2794 CStdString strArtist = m_pDS->fv("strArtist").get_asString();
2795 CFileItemPtr pItem(new CFileItem(strArtist));
2796 pItem->GetMusicInfoTag()->SetArtist(strArtist);
2798 int idArtist = m_pDS->fv("idArtist").get_asInt();
2799 strDir.Format("%ld/", idArtist);
2800 pItem->SetPath(strBaseDir + strDir);
2801 pItem->m_bIsFolder=true;
2802 pItem->GetMusicInfoTag()->SetDatabaseId(idArtist);
2803 if (CFile::Exists(pItem->GetCachedArtistThumb()))
2804 pItem->SetThumbnailImage(pItem->GetCachedArtistThumb());
2805 pItem->SetIconImage("DefaultArtist.png");
2807 GetArtistInfo(idArtist,artist,false);
2809 SetPropertiesFromArtist(*pItem,artist);
2814 CLog::Log(LOGDEBUG,"Time to retrieve artists from dataset = %i", XbmcThreads::SystemClockMillis() - time);
2824 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2829 bool CMusicDatabase::GetAlbumFromSong(int idSong, CAlbum &album)
2833 if (NULL == m_pDB.get()) return false;
2834 if (NULL == m_pDS.get()) return false;
2836 CStdString strSQL = PrepareSQL("select albumview.* from song join albumview on song.idAlbum = albumview.idAlbum where song.idSong='%i'", idSong);
2837 if (!m_pDS->query(strSQL.c_str())) return false;
2838 int iRowsFound = m_pDS->num_rows();
2839 if (iRowsFound != 1)
2845 album = GetAlbumFromDataset(m_pDS.get());
2853 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2858 // This function won't be required if/when the fileitem tag has idSong information
2859 bool CMusicDatabase::GetAlbumFromSong(const CSong &song, CAlbum &album)
2863 if (NULL == m_pDB.get()) return false;
2864 if (NULL == m_pDS.get()) return false;
2866 if (song.idSong != -1) return GetAlbumFromSong(song.idSong, album);
2868 CStdString path, file;
2869 URIUtils::Split(song.strFileName, path, file);
2871 CStdString strSQL = PrepareSQL("select albumview.* from song join albumview on song.idAlbum = albumview.idAlbum join path on song.idPath = path.idPath where song.strFileName='%s' and path.strPath='%s'", file.c_str(), path.c_str());
2872 if (!m_pDS->query(strSQL.c_str())) return false;
2873 int iRowsFound = m_pDS->num_rows();
2874 if (iRowsFound != 1)
2880 album = GetAlbumFromDataset(m_pDS.get());
2887 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
2892 bool CMusicDatabase::GetAlbumsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, int idArtist, int start, int end)
2896 if (start >= 0 && end >= 0)
2898 limit.Format(" limit %i,%i", start, end);
2902 CStdString strWhere;
2905 strWhere+=PrepareSQL("where (idAlbum IN "
2907 "select song.idAlbum from song " // All albums where the primary genre fits
2908 "where song.idGenre=%i"
2912 "select song.idAlbum from song " // All albums where extra genres fits
2913 "join exgenresong on song.idSong=exgenresong.idSong "
2914 "where exgenresong.idGenre=%i"
2917 , idGenre, idGenre);
2922 if (strWhere.IsEmpty())
2923 strWhere += "where ";
2927 strWhere +=PrepareSQL("(idAlbum IN "
2929 "select song.idAlbum from song " // All albums where the primary artist fits
2930 "where song.idArtist=%i"
2934 "select song.idAlbum from song " // All albums where extra artists fit
2935 "join exartistsong on song.idSong=exartistsong.idSong "
2936 "where exartistsong.idArtist=%i"
2940 "select album.idAlbum from album " // All albums where primary album artist fits
2941 "where album.idArtist=%i"
2945 "select exartistalbum.idAlbum from exartistalbum " // All albums where extra album artists fit
2946 "where exartistalbum.idArtist=%i"
2949 , idArtist, idArtist, idArtist, idArtist);
2952 { // no artist given, so exclude any single albums (aka empty tagged albums)
2953 if (strWhere.IsEmpty())
2954 strWhere += "where albumview.strAlbum <> ''" + limit;
2956 strWhere += "and albumview.strAlbum <> ''" + limit;
2959 bool bResult = GetAlbumsByWhere(strBaseDir, strWhere, "", items);
2960 if (bResult && idArtist != -1)
2962 CStdString strArtist = GetArtistById(idArtist);
2963 CStdString strFanart = items.GetCachedThumb(strArtist,g_settings.GetMusicFanartFolder());
2964 if (CFile::Exists(strFanart))
2965 items.SetProperty("fanart_image",strFanart);
2971 bool CMusicDatabase::GetAlbumsByWhere(const CStdString &baseDir, const CStdString &where, const CStdString &order, CFileItemList &items)
2973 if (m_pDB.get() == NULL || m_pDS.get() == NULL)
2978 CStdString sql = "select * from albumview " + where + order;
2980 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, sql.c_str());
2982 unsigned int time = XbmcThreads::SystemClockMillis();
2983 if (!m_pDS->query(sql.c_str())) return false;
2984 CLog::Log(LOGDEBUG, "%s - query took %i ms",
2985 __FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
2987 int iRowsFound = m_pDS->num_rows();
2988 if (iRowsFound == 0)
2994 items.Reserve(iRowsFound);
2996 // get data from returned rows
2997 while (!m_pDS->eof())
3002 int idAlbum = m_pDS->fv("idAlbum").get_asInt();
3003 strDir.Format("%s%ld/", baseDir.c_str(), idAlbum);
3004 CFileItemPtr pItem(new CFileItem(strDir, GetAlbumFromDataset(m_pDS.get())));
3005 pItem->SetIconImage("DefaultAlbumCover.png");
3012 CLog::Log(LOGERROR, "%s - out of memory getting listing (got %i)", __FUNCTION__, items.Size());
3023 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, where.c_str());
3028 bool CMusicDatabase::GetSongsByWhere(const CStdString &baseDir, const CStdString &whereClause, CFileItemList &items)
3030 if (NULL == m_pDB.get()) return false;
3031 if (NULL == m_pDS.get()) return false;
3035 unsigned int time = XbmcThreads::SystemClockMillis();
3036 // We don't use PrepareSQL here, as the WHERE clause is already formatted.
3037 CStdString strSQL = "select * from songview " + whereClause;
3038 CLog::Log(LOGDEBUG, "%s query = %s", __FUNCTION__, strSQL.c_str());
3040 if (!m_pDS->query(strSQL.c_str()))
3042 int iRowsFound = m_pDS->num_rows();
3043 if (iRowsFound == 0)
3049 // get data from returned rows
3050 items.Reserve(items.Size() + iRowsFound);
3051 // get songs from returned subtable
3053 while (!m_pDS->eof())
3057 CFileItemPtr item(new CFileItem);
3058 GetFileItemFromDataset(item.get(), baseDir);
3059 // HACK for sorting by database returned order
3060 item->m_iprogramCount = ++count;
3067 CLog::Log(LOGERROR, "%s: out of memory loading query: %s", __FUNCTION__, whereClause.c_str());
3068 return (items.Size() > 0);
3073 CLog::Log(LOGDEBUG, "%s(%s) - took %d ms", __FUNCTION__, whereClause.c_str(), XbmcThreads::SystemClockMillis() - time);
3080 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, whereClause.c_str());
3085 bool CMusicDatabase::GetSongsByYear(const CStdString& baseDir, CFileItemList& items, int year)
3087 CStdString where=PrepareSQL("where (iYear=%ld)", year);
3088 return GetSongsByWhere(baseDir, where, items);
3091 bool CMusicDatabase::GetSongsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, int idArtist,int idAlbum)
3093 CStdString strWhere;
3096 strWhere=PrepareSQL("where (idAlbum=%ld) ", idAlbum);
3100 if (strWhere.IsEmpty())
3101 strWhere += "where ";
3105 strWhere += PrepareSQL("(idGenre=%i " // All songs where primary genre fits
3108 "select exgenresong.idSong from exgenresong " // All songs by where extra genres fit
3109 "where exgenresong.idGenre=%i"
3112 , idGenre, idGenre);
3117 if (strWhere.IsEmpty())
3118 strWhere += "where ";
3122 strWhere += PrepareSQL("(idArtist=%i " // All songs where primary artist fits
3125 "select exartistsong.idSong from exartistsong " // All songs where extra artists fit
3126 "where exartistsong.idArtist=%i"
3130 "select song.idSong from song " // All songs where the primary album artist fits
3131 "join album on song.idAlbum=album.idAlbum "
3132 "where album.idArtist=%i"
3136 "select song.idSong from song " // All songs where the extra album artist fit, excluding
3137 "join exartistalbum on song.idAlbum=exartistalbum.idAlbum " // various artist albums
3138 "join album on song.idAlbum=album.idAlbum "
3139 "where exartistalbum.idArtist=%i and album.strExtraArtists != ''"
3142 , idArtist, idArtist, idArtist, idArtist);
3146 bool bResult = GetSongsByWhere(strBaseDir, strWhere, items);
3147 if (bResult && idArtist != -1)
3149 CStdString strArtist = GetArtistById(idArtist);
3150 CStdString strFanart = items.GetCachedThumb(strArtist,g_settings.GetMusicFanartFolder());
3151 if (CFile::Exists(strFanart))
3152 items.SetProperty("fanart_image",strFanart);
3158 bool CMusicDatabase::UpdateOldVersion(int version)
3160 if (NULL == m_pDB.get()) return false;
3161 if (NULL == m_pDS.get()) return false;
3162 if (NULL == m_pDS2.get()) return false;
3170 // only if MySQL is used and default character set is not utf8
3171 // string data needs to be converted to proper utf8
3172 CStdString charset = m_pDS->getDatabase()->getDefaultCharset();
3173 if (!m_sqlite && !charset.empty() && charset != "utf8")
3175 map<CStdString, CStdStringArray> tables;
3176 map<CStdString, CStdStringArray>::iterator itt;
3177 CStdStringArray::iterator itc;
3179 //columns that need to be converted
3181 c1.push_back("strAlbum");
3182 c1.push_back("strExtraArtists");
3183 c1.push_back("strExtraGenres");
3184 tables.insert(pair<CStdString, CStdStringArray> ("album", c1));
3187 c2.push_back("strExtraGenres");
3188 c2.push_back("strMoods");
3189 c2.push_back("strStyles");
3190 c2.push_back("strThemes");
3191 c2.push_back("strReview");
3192 c2.push_back("strLabel");
3193 tables.insert(pair<CStdString, CStdStringArray> ("albuminfo", c2));
3196 c3.push_back("strTitle");
3197 tables.insert(pair<CStdString, CStdStringArray> ("albuminfosong", c3));
3200 c4.push_back("strArtist");
3201 tables.insert(pair<CStdString, CStdStringArray> ("artist", c4));
3204 c5.push_back("strBorn");
3205 c5.push_back("strFormed");
3206 c5.push_back("strGenres");
3207 c5.push_back("strMoods");
3208 c5.push_back("strStyles");
3209 c5.push_back("strInstruments");
3210 c5.push_back("strBiography");
3211 c5.push_back("strDied");
3212 c5.push_back("strDisbanded");
3213 c5.push_back("strYearsActive");
3214 tables.insert(pair<CStdString, CStdStringArray> ("artistinfo", c5));
3217 c6.push_back("strAlbum");
3218 tables.insert(pair<CStdString, CStdStringArray> ("discography", c6));
3221 c7.push_back("strGenre");
3222 tables.insert(pair<CStdString, CStdStringArray> ("genre", c7));
3225 c8.push_back("strKaraLyrics");
3226 tables.insert(pair<CStdString, CStdStringArray> ("karaokedata", c8));
3229 c9.push_back("strTitle");
3230 c9.push_back("strFilename");
3231 c9.push_back("comment");
3232 tables.insert(pair<CStdString, CStdStringArray> ("song", c9));
3234 for (itt = tables.begin(); itt != tables.end(); ++itt)
3237 q = PrepareSQL("UPDATE `%s` SET", itt->first.c_str());
3238 for (itc = itt->second.begin(); itc != itt->second.end(); ++itc)
3240 q += PrepareSQL(" `%s` = CONVERT(CAST(CONVERT(`%s` USING %s) AS BINARY) USING utf8)",
3241 itc->c_str(), itc->c_str(), charset.c_str());
3242 if (*itc != itt->second.back())
3253 m_pDS->exec("CREATE INDEX idxAlbum2 ON album(idArtist)");
3254 m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
3255 m_pDS->exec("CREATE INDEX idxSong4 ON song(idArtist)");
3256 m_pDS->exec("CREATE INDEX idxSong5 ON song(idGenre)");
3257 m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
3260 // always recreate the views after any table change
3263 CommitTransaction();
3267 CLog::Log(LOGERROR, "Error attempting to update the database version!");
3273 int CMusicDatabase::AddThumb(const CStdString& strThumb1)
3278 CStdString strThumb = strThumb1;
3279 if (strThumb.IsEmpty())
3282 if (NULL == m_pDB.get()) return -1;
3283 if (NULL == m_pDS.get()) return -1;
3285 map <CStdString, int>::const_iterator it;
3287 it = m_thumbCache.find(strThumb1);
3288 if (it != m_thumbCache.end())
3291 strSQL=PrepareSQL( "select * from thumb where strThumb='%s'", strThumb.c_str());
3292 m_pDS->query(strSQL.c_str());
3293 if (m_pDS->num_rows() == 0)
3296 // doesnt exists, add it
3297 strSQL=PrepareSQL("insert into thumb (idThumb, strThumb) values( NULL, '%s' )", strThumb.c_str());
3298 m_pDS->exec(strSQL.c_str());
3300 int idPath = (int)m_pDS->lastinsertid();
3301 m_thumbCache.insert(pair<CStdString, int>(strThumb1, idPath));
3306 int idPath = m_pDS->fv("idThumb").get_asInt();
3307 m_thumbCache.insert(pair<CStdString, int>(strThumb1, idPath));
3314 CLog::Log(LOGERROR, "musicdatabase:unable to addthumb (%s)", strSQL.c_str());
3320 unsigned int CMusicDatabase::GetSongIDs(const CStdString& strWhere, vector<pair<int,int> > &songIDs)
3324 if (NULL == m_pDB.get()) return 0;
3325 if (NULL == m_pDS.get()) return 0;
3327 CStdString strSQL = "select idSong from songview " + strWhere;
3328 if (!m_pDS->query(strSQL.c_str())) return 0;
3330 if (m_pDS->num_rows() == 0)
3335 songIDs.reserve(m_pDS->num_rows());
3336 while (!m_pDS->eof())
3338 songIDs.push_back(make_pair<int,int>(1,m_pDS->fv(song_idSong).get_asInt()));
3342 return songIDs.size();
3346 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strWhere.c_str());
3351 int CMusicDatabase::GetSongsCount(const CStdString& strWhere)
3355 if (NULL == m_pDB.get()) return 0;
3356 if (NULL == m_pDS.get()) return 0;
3358 CStdString strSQL = "select count(idSong) as NumSongs from songview " + strWhere;
3359 if (!m_pDS->query(strSQL.c_str())) return false;
3360 if (m_pDS->num_rows() == 0)
3366 int iNumSongs = m_pDS->fv("NumSongs").get_asInt();
3373 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strWhere.c_str());
3378 bool CMusicDatabase::GetAlbumPath(int idAlbum, CStdString& path)
3382 if (NULL == m_pDB.get()) return false;
3383 if (NULL == m_pDS2.get()) return false;
3387 CStdString strSQL=PrepareSQL("select strPath from song join path on song.idPath = path.idPath where song.idAlbum=%ld", idAlbum);
3388 if (!m_pDS2->query(strSQL.c_str())) return false;
3389 int iRowsFound = m_pDS2->num_rows();
3390 if (iRowsFound == 0)
3396 // if this returns more than one path, we just grab the first one. It's just for determining where to obtain + place
3397 // a local thumbnail
3398 path = m_pDS2->fv("strPath").get_asString();
3400 m_pDS2->close(); // cleanup recordset data
3405 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
3412 bool CMusicDatabase::SaveAlbumThumb(int idAlbum, const CStdString& strThumb)
3416 if (NULL == m_pDB.get()) return false;
3417 if (NULL == m_pDS.get()) return false;
3419 int idThumb=AddThumb(strThumb);
3423 CStdString strSQL=PrepareSQL("UPDATE album SET idThumb=%ld where idAlbum=%ld", idThumb, idAlbum);
3424 CLog::Log(LOGDEBUG, "%s exec: %s", __FUNCTION__, strSQL.c_str());
3425 m_pDS->exec(strSQL.c_str());
3426 strSQL=PrepareSQL("UPDATE song SET idThumb=%ld where idAlbum=%ld", idThumb, idAlbum);
3427 CLog::Log(LOGDEBUG, "%s exec: %s", __FUNCTION__, strSQL.c_str());
3428 m_pDS->exec(strSQL.c_str());
3435 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
3441 bool CMusicDatabase::GetAlbumThumb(int idAlbum, CStdString& strThumb)
3445 if (NULL == m_pDB.get()) return false;
3446 if (NULL == m_pDS.get()) return false;
3448 CStdString strSQL=PrepareSQL("select strThumb from thumb join album on album.idThumb = thumb.idThumb where album.idAlbum=%i", idAlbum);
3449 m_pDS2->query(strSQL.c_str());
3453 strThumb = m_pDS2->fv("strThumb").get_asString();
3459 CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idAlbum);
3465 bool CMusicDatabase::GetArtistPath(int idArtist, CStdString &basePath)
3469 if (NULL == m_pDB.get()) return false;
3470 if (NULL == m_pDS2.get()) return false;
3472 // find all albums from this artist, and all the paths to the songs from those albums
3473 CStdString strSQL=PrepareSQL("select strPath from album join song on album.idAlbum = song.idAlbum join path on song.idPath = path.idPath "
3474 "where album.idAlbum in (select idAlbum from album where album.idArtist=%i) "
3475 "or album.idAlbum in (select idAlbum from exartistalbum where exartistalbum.idArtist = %i) "
3476 "group by song.idPath", idArtist, idArtist);
3479 if (!m_pDS2->query(strSQL.c_str())) return false;
3480 int iRowsFound = m_pDS2->num_rows();
3481 if (iRowsFound == 0)
3487 // special case for single path - assume that we're in an artist/album/songs filesystem
3488 if (iRowsFound == 1)
3490 URIUtils::GetParentPath(m_pDS2->fv("strPath").get_asString(), basePath);
3495 // find the common path (if any) to these albums
3497 while (!m_pDS2->eof())
3499 CStdString path = m_pDS2->fv("strPath").get_asString();
3500 if (basePath.IsEmpty())
3503 URIUtils::GetCommonPath(basePath,path);
3515 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3520 int CMusicDatabase::GetArtistByName(const CStdString& strArtist)
3524 if (NULL == m_pDB.get()) return false;
3525 if (NULL == m_pDS.get()) return false;
3527 CStdString strSQL=PrepareSQL("select idArtist from artist where artist.strArtist like '%s'", strArtist.c_str());
3530 if (!m_pDS->query(strSQL.c_str())) return false;
3531 int iRowsFound = m_pDS->num_rows();
3532 if (iRowsFound != 1)
3537 int lResult = m_pDS->fv("artist.idArtist").get_asInt();
3543 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3548 int CMusicDatabase::GetAlbumByName(const CStdString& strAlbum, const CStdString& strArtist)
3552 if (NULL == m_pDB.get()) return false;
3553 if (NULL == m_pDS.get()) return false;
3556 if (strArtist.IsEmpty())
3557 strSQL=PrepareSQL("select idAlbum from album where album.strAlbum like '%s'", strAlbum.c_str());
3559 strSQL=PrepareSQL("select album.idAlbum from album join artist on artist.idArtist = album.idArtist where album.strAlbum like '%s' and artist.strArtist like '%s'", strAlbum.c_str(),strArtist.c_str());
3561 if (!m_pDS->query(strSQL.c_str())) return false;
3562 int iRowsFound = m_pDS->num_rows();
3563 if (iRowsFound != 1)
3568 return m_pDS->fv("album.idAlbum").get_asInt();
3572 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3577 int CMusicDatabase::GetAlbumByName(const CStdString& strAlbum, const std::vector<std::string>& artist)
3579 return GetAlbumByName(strAlbum, StringUtils::Join(artist, g_advancedSettings.m_musicItemSeparator));
3582 CStdString CMusicDatabase::GetGenreById(int id)
3584 return GetSingleValue("genre", "strGenre", PrepareSQL("idGenre=%i", id));
3587 CStdString CMusicDatabase::GetArtistById(int id)
3589 return GetSingleValue("artist", "strArtist", PrepareSQL("idArtist=%i", id));
3592 CStdString CMusicDatabase::GetAlbumById(int id)
3594 return GetSingleValue("album", "strAlbum", PrepareSQL("idAlbum=%i", id));
3597 int CMusicDatabase::GetGenreByName(const CStdString& strGenre)
3601 if (NULL == m_pDB.get()) return false;
3602 if (NULL == m_pDS.get()) return false;
3605 strSQL=PrepareSQL("select idGenre from genre where genre.strGenre like '%s'", strGenre.c_str());
3607 if (!m_pDS->query(strSQL.c_str())) return false;
3608 int iRowsFound = m_pDS->num_rows();
3609 if (iRowsFound != 1)
3614 return m_pDS->fv("genre.idGenre").get_asInt();
3618 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3623 bool CMusicDatabase::GetRandomSong(CFileItem* item, int& idSong, const CStdString& strWhere)
3629 int iCount = GetSongsCount(strWhere);
3632 int iRandom = rand() % iCount;
3634 if (NULL == m_pDB.get()) return false;
3635 if (NULL == m_pDS.get()) return false;
3637 // We don't use PrepareSQL here, as the WHERE clause is already formatted
3639 strSQL.Format("select * from songview %s order by idSong limit 1 offset %i", strWhere.c_str(), iRandom);
3641 CLog::Log(LOGDEBUG, "%s query = %s", __FUNCTION__, strSQL.c_str());
3643 if (!m_pDS->query(strSQL.c_str()))
3645 int iRowsFound = m_pDS->num_rows();
3646 if (iRowsFound != 1)
3651 GetFileItemFromDataset(item, "");
3652 idSong = m_pDS->fv("songview.idSong").get_asInt();
3658 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strWhere.c_str());
3663 bool CMusicDatabase::GetVariousArtistsAlbums(const CStdString& strBaseDir, CFileItemList& items)
3667 if (NULL == m_pDB.get()) return false;
3668 if (NULL == m_pDS.get()) return false;
3670 CStdString strVariousArtists = g_localizeStrings.Get(340);
3671 int idVariousArtists=AddArtist(strVariousArtists);
3672 if (idVariousArtists<0)
3675 CStdString strSQL = PrepareSQL("select * from albumview where idArtist=%i", idVariousArtists);
3678 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
3679 if (!m_pDS->query(strSQL.c_str())) return false;
3680 int iRowsFound = m_pDS->num_rows();
3681 if (iRowsFound == 0)
3687 items.Reserve(iRowsFound);
3689 // get data from returned rows
3690 while (!m_pDS->eof())
3693 strDir.Format("%s%ld/", strBaseDir.c_str(), m_pDS->fv("idAlbum").get_asInt());
3694 CFileItemPtr pItem(new CFileItem(strDir, GetAlbumFromDataset(m_pDS.get())));
3707 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3712 bool CMusicDatabase::GetVariousArtistsAlbumsSongs(const CStdString& strBaseDir, CFileItemList& items)
3716 if (NULL == m_pDB.get()) return false;
3717 if (NULL == m_pDS.get()) return false;
3719 CStdString strVariousArtists = g_localizeStrings.Get(340);
3720 int idVariousArtists=AddArtist(strVariousArtists);
3721 if (idVariousArtists<0)
3724 CStdString strSQL = PrepareSQL("select * from songview where idAlbum IN (select idAlbum from album where idArtist=%i)", idVariousArtists);
3727 CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
3728 if (!m_pDS->query(strSQL.c_str())) return false;
3729 int iRowsFound = m_pDS->num_rows();
3730 if (iRowsFound == 0)
3736 items.Reserve(iRowsFound);
3738 // get data from returned rows
3739 while (!m_pDS->eof())
3741 CFileItemPtr item(new CFileItem);
3742 GetFileItemFromDataset(item.get(), strBaseDir);
3755 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3760 void CMusicDatabase::SplitString(const CStdString &multiString, vector<string> &vecStrings, CStdString &extraStrings)
3762 vecStrings = StringUtils::Split(multiString, g_advancedSettings.m_musicItemSeparator);
3763 for (unsigned int i = 1; i < vecStrings.size(); i++)
3764 extraStrings += g_advancedSettings.m_musicItemSeparator + CStdString(vecStrings[i]);
3767 bool CMusicDatabase::SetPathHash(const CStdString &path, const CStdString &hash)
3771 if (NULL == m_pDB.get()) return false;
3772 if (NULL == m_pDS.get()) return false;
3775 { // this is an empty folder - we need only add it to the path table
3776 // if the path actually exists
3777 if (!CDirectory::Exists(path))
3780 int idPath = AddPath(path);
3781 if (idPath < 0) return false;
3783 CStdString strSQL=PrepareSQL("update path set strHash='%s' where idPath=%ld", hash.c_str(), idPath);
3784 m_pDS->exec(strSQL.c_str());
3790 CLog::Log(LOGERROR, "%s (%s, %s) failed", __FUNCTION__, path.c_str(), hash.c_str());
3796 bool CMusicDatabase::GetPathHash(const CStdString &path, CStdString &hash)
3800 if (NULL == m_pDB.get()) return false;
3801 if (NULL == m_pDS.get()) return false;
3803 CStdString strSQL=PrepareSQL("select strHash from path where strPath='%s'", path.c_str());
3804 m_pDS->query(strSQL.c_str());
3805 if (m_pDS->num_rows() == 0)
3807 hash = m_pDS->fv("strHash").get_asString();
3812 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, path.c_str());
3818 bool CMusicDatabase::RemoveSongsFromPath(const CStdString &path1, CSongMap &songs, bool exact)
3820 // We need to remove all songs from this path, as their tags are going
3821 // to be re-read. We need to remove all songs from the song table + all links to them
3822 // from the exartistsong and exgenresong tables (as otherwise if a song is added back
3823 // to the table with the same idSong, these tables can't be cleaned up properly later)
3825 // TODO: SQLite probably doesn't allow this, but can we rely on that??
3827 // We don't need to remove orphaned albums at this point as in AddAlbum() we check
3828 // first whether the album has already been read during this scan, and if it hasn't
3829 // we check whether it's in the table and update accordingly at that point, removing the entries from
3830 // the exartistalbum and exgenrealbum tables. The only failure point for this is albums
3831 // that span multiple folders, where just the files in one folder have been changed. In this case
3832 // any exalbumartist(s) that are only in the files that haven't changed will be removed. Clearly
3833 // the primary albumartist still matches (as that's what we looked up based on) so is this really
3834 // an issue? I don't think it is, as those artists will still have links to the album via the songs
3835 // which is generally what we rely on, so the only failure point is albumartist lookup. In this
3836 // case, it will return only things in the exartistalbum table from the newly updated songs (and
3837 // only if they have additional artists). I think the effect of this is minimal at best, as ALL
3838 // songs in the album should have the same albumartist!
3840 // we also remove the path at this point as it will be added later on if the
3841 // path still exists.
3842 // After scanning we then remove the orphaned artists, genres and thumbs.
3844 // Note: when used to remove all songs from a path and its subpath (exact=false), this
3845 // does miss archived songs.
3846 CStdString path(path1);
3849 if (!URIUtils::HasSlashAtEnd(path))
3850 URIUtils::AddSlashAtEnd(path);
3852 if (NULL == m_pDB.get()) return false;
3853 if (NULL == m_pDS.get()) return false;
3857 where = PrepareSQL(" where strPath='%s'", path.c_str());
3859 where = PrepareSQL(" where SUBSTR(strPath,1,%i)='%s'", StringUtils::utf8_strlen(path.c_str()), path.c_str());
3860 CStdString sql = "select * from songview" + where;
3861 if (!m_pDS->query(sql.c_str())) return false;
3862 int iRowsFound = m_pDS->num_rows();
3865 std::vector<int> ids;
3866 CStdString songIds = "(";
3867 while (!m_pDS->eof())
3869 CSong song = GetSongFromDataset();
3870 songs.Add(song.strFileName, song);
3871 songIds += PrepareSQL("%i,", song.idSong);
3872 ids.push_back(song.idSong);
3875 songIds.TrimRight(",");
3880 // and delete all songs, exartistsongs and exgenresongs and karaoke
3881 sql = "delete from song where idSong in " + songIds;
3882 m_pDS->exec(sql.c_str());
3883 sql = "delete from exartistsong where idSong in " + songIds;
3884 m_pDS->exec(sql.c_str());
3885 sql = "delete from exgenresong where idSong in " + songIds;
3886 m_pDS->exec(sql.c_str());
3887 sql = "delete from karaokedata where idSong in " + songIds;
3888 m_pDS->exec(sql.c_str());
3890 for (unsigned int i = 0; i < ids.size(); i++)
3891 AnnounceRemove("song", ids[i]);
3893 // and remove the path as well (it'll be re-added later on with the new hash if it's non-empty)
3894 sql = "delete from path" + where;
3895 m_pDS->exec(sql.c_str());
3896 return iRowsFound > 0;
3900 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, path.c_str());
3905 bool CMusicDatabase::GetPaths(set<CStdString> &paths)
3909 if (NULL == m_pDB.get()) return false;
3910 if (NULL == m_pDS.get()) return false;
3915 if (!m_pDS->query("select strPath from path")) return false;
3916 int iRowsFound = m_pDS->num_rows();
3917 if (iRowsFound == 0)
3922 while (!m_pDS->eof())
3924 paths.insert(m_pDS->fv("strPath").get_asString());
3932 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
3937 bool CMusicDatabase::SetSongRating(const CStdString &filePath, char rating)
3941 if (filePath.IsEmpty()) return false;
3942 if (NULL == m_pDB.get()) return false;
3943 if (NULL == m_pDS.get()) return false;
3945 int songID = GetSongIDFromPath(filePath);
3946 if (-1 == songID) return false;
3948 CStdString sql = PrepareSQL("update song set rating='%c' where idSong = %i", rating, songID);
3949 m_pDS->exec(sql.c_str());
3954 CLog::Log(LOGERROR, "%s (%s,%c) failed", __FUNCTION__, filePath.c_str(), rating);
3959 int CMusicDatabase::GetSongIDFromPath(const CStdString &filePath)
3961 // grab the where string to identify the song id
3963 if (url.GetProtocol()=="musicdb")
3965 CStdString strFile=URIUtils::GetFileName(filePath);
3966 URIUtils::RemoveExtension(strFile);
3967 return atol(strFile.c_str());
3972 if (NULL == m_pDB.get()) return -1;
3973 if (NULL == m_pDS.get()) return -1;
3975 URIUtils::GetDirectory(filePath, strPath);
3976 URIUtils::AddSlashAtEnd(strPath);
3978 DWORD crc = ComputeCRC(filePath);
3980 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());
3981 if (!m_pDS->query(sql.c_str())) return -1;
3983 if (m_pDS->num_rows() == 0)
3989 int songID = m_pDS->fv("idSong").get_asInt();
3995 CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, filePath.c_str());
4000 bool CMusicDatabase::CommitTransaction()
4002 if (CDatabase::CommitTransaction())
4003 { // number of items in the db has likely changed, so reset the infomanager cache
4004 g_infoManager.SetLibraryBool(LIBRARY_HAS_MUSIC, GetSongsCount("") > 0);
4010 bool CMusicDatabase::SetScraperForPath(const CStdString& strPath, const ADDON::ScraperPtr& scraper)
4014 if (NULL == m_pDB.get()) return false;
4015 if (NULL == m_pDS.get()) return false;
4017 // wipe old settings
4018 CStdString strSQL = PrepareSQL("delete from content where strPath='%s'",strPath.c_str());
4019 m_pDS->exec(strSQL.c_str());
4021 // insert new settings
4022 strSQL = PrepareSQL("insert into content (strPath, strScraperPath, strContent, strSettings) values ('%s','%s','%s','%s')",
4023 strPath.c_str(), scraper->ID().c_str(), ADDON::TranslateContent(scraper->Content()).c_str(), scraper->GetPathSettings().c_str());
4024 m_pDS->exec(strSQL.c_str());
4030 CLog::Log(LOGERROR, "%s - (%s) failed", __FUNCTION__, strPath.c_str());
4035 bool CMusicDatabase::GetScraperForPath(const CStdString& strPath, ADDON::ScraperPtr& info, const ADDON::TYPE &type)
4039 if (NULL == m_pDB.get()) return false;
4040 if (NULL == m_pDS.get()) return false;
4042 CStdString strSQL = PrepareSQL("select * from content where strPath='%s'",strPath.c_str());
4043 m_pDS->query(strSQL.c_str());
4044 if (m_pDS->eof()) // no info set for path - fallback logic commencing
4046 CQueryParams params;
4047 CDirectoryNode::GetDatabaseInfo(strPath, params);
4048 if (params.GetGenreId() != -1) // check genre
4050 strSQL = PrepareSQL("select * from content where strPath='musicdb://1/%i/'",params.GetGenreId());
4051 m_pDS->query(strSQL.c_str());
4053 if (m_pDS->eof() && params.GetAlbumId() != -1) // check album
4055 strSQL = PrepareSQL("select * from content where strPath='musicdb://3/%i/'",params.GetGenreId());
4056 m_pDS->query(strSQL.c_str());
4058 if (m_pDS->eof() && params.GetArtistId() != -1) // check artist
4060 strSQL = PrepareSQL("select * from content where strPath='musicdb://2/%i/'",params.GetArtistId());
4061 m_pDS->query(strSQL.c_str());
4063 if (m_pDS->eof()) // general albums setting
4065 strSQL = PrepareSQL("select * from content where strPath='musicdb://3/'");
4066 m_pDS->query(strSQL.c_str());
4068 if (m_pDS->eof()) // general artist setting
4070 strSQL = PrepareSQL("select * from content where strPath='musicdb://2/'");
4071 m_pDS->query(strSQL.c_str());
4076 { // try and ascertain scraper for this path
4077 CONTENT_TYPE content = ADDON::TranslateContent(m_pDS->fv("content.strContent").get_asString());
4078 CStdString scraperUUID = m_pDS->fv("content.strScraperPath").get_asString();
4080 if (content != CONTENT_NONE)
4081 { // content set, use pre configured or default scraper
4082 ADDON::AddonPtr addon;
4083 if (!scraperUUID.empty() && ADDON::CAddonMgr::Get().GetAddon(scraperUUID, addon) && addon)
4085 info = boost::dynamic_pointer_cast<ADDON::CScraper>(addon->Clone(addon));
4088 // store this path's settings
4089 info->SetPathSettings(content, m_pDS->fv("content.strSettings").get_asString());
4093 { // use default scraper of the requested type
4094 ADDON::AddonPtr defaultScraper;
4095 if (ADDON::CAddonMgr::Get().GetDefault(type, defaultScraper))
4097 info = boost::dynamic_pointer_cast<ADDON::CScraper>(defaultScraper->Clone(defaultScraper));
4104 { // use default music scraper instead
4105 ADDON::AddonPtr addon;
4106 if(ADDON::CAddonMgr::Get().GetDefault(type, addon))
4108 info = boost::dynamic_pointer_cast<ADDON::CScraper>(addon);
4119 CLog::Log(LOGERROR, "%s -(%s) failed", __FUNCTION__, strPath.c_str());
4124 bool CMusicDatabase::ScraperInUse(const CStdString &scraperID) const
4128 if (NULL == m_pDB.get()) return false;
4129 if (NULL == m_pDS.get()) return false;
4131 CStdString sql = PrepareSQL("select count(1) from content where strScraperPath='%s'",scraperID.c_str());
4132 if (!m_pDS->query(sql.c_str()) || m_pDS->num_rows() == 0)
4134 bool found = m_pDS->fv(0).get_asInt() > 0;
4140 CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, scraperID.c_str());
4145 void CMusicDatabase::ExportToXML(const CStdString &xmlFile, bool singleFiles, bool images, bool overwrite)
4149 if (NULL == m_pDB.get()) return;
4150 if (NULL == m_pDS.get()) return;
4151 if (NULL == m_pDS2.get()) return;
4154 CStdString sql = "select albumview.*,albuminfo.strImage,albuminfo.idAlbumInfo from albuminfo "
4155 "join albumview on albuminfo.idAlbum=albumview.idAlbum "
4156 "join genre on albuminfo.idGenre=genre.idGenre";
4158 m_pDS->query(sql.c_str());
4160 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4163 progress->SetHeading(20196);
4164 progress->SetLine(0, 650);
4165 progress->SetLine(1, "");
4166 progress->SetLine(2, "");
4167 progress->SetPercentage(0);
4168 progress->StartModal();
4169 progress->ShowProgressBar(true);
4172 int total = m_pDS->num_rows();
4175 // create our xml document
4176 TiXmlDocument xmlDoc;
4177 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4178 xmlDoc.InsertEndChild(decl);
4179 TiXmlNode *pMain = NULL;
4184 TiXmlElement xmlMainElement("musicdb");
4185 pMain = xmlDoc.InsertEndChild(xmlMainElement);
4187 while (!m_pDS->eof())
4189 CAlbum album = GetAlbumFromDataset(m_pDS.get());
4190 album.thumbURL.Clear();
4191 album.thumbURL.ParseString(m_pDS->fv("albuminfo.strImage").get_asString());
4192 int idAlbumInfo = m_pDS->fv("albuminfo.idAlbumInfo").get_asInt();
4193 GetAlbumInfoSongs(idAlbumInfo,album.songs);
4195 GetAlbumPath(album.idAlbum,strPath);
4196 album.Save(pMain, "album", strPath);
4199 if (!CDirectory::Exists(strPath))
4200 CLog::Log(LOGDEBUG, "%s - Not exporting item %s as it does not exist", __FUNCTION__, strPath.c_str());
4204 URIUtils::AddFileToFolder(strPath, "album.nfo", nfoFile);
4205 if (overwrite || !CFile::Exists(nfoFile))
4207 if (!xmlDoc.SaveFile(nfoFile))
4208 CLog::Log(LOGERROR, "%s: Album nfo export failed! ('%s')", __FUNCTION__, nfoFile.c_str());
4213 CStdString strThumb;
4214 if (GetAlbumThumb(album.idAlbum,strThumb) && (overwrite || !CFile::Exists(URIUtils::AddFileToFolder(strPath,"folder.jpg"))))
4215 CFile::Cache(strThumb,URIUtils::AddFileToFolder(strPath,"folder.jpg"));
4218 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4219 xmlDoc.InsertEndChild(decl);
4223 if ((current % 50) == 0 && progress)
4225 progress->SetLine(1, album.strAlbum);
4226 progress->SetPercentage(current * 100 / total);
4227 progress->Progress();
4228 if (progress->IsCanceled())
4241 sql = "select * from artistinfo "
4242 "join artist on artist.idArtist=artistinfo.idArtist";
4244 // needed due to getartistpath
4245 auto_ptr<dbiplus::Dataset> pDS;
4246 pDS.reset(m_pDB->CreateDataset());
4247 pDS->query(sql.c_str());
4249 total = pDS->num_rows();
4254 CArtist artist = GetArtistFromDataset(pDS.get());
4255 CStdString strSQL=PrepareSQL("select * from discography where idArtist=%i",artist.idArtist);
4256 m_pDS->query(strSQL.c_str());
4257 while (!m_pDS->eof())
4259 artist.discography.push_back(make_pair(m_pDS->fv("strAlbum").get_asString(),m_pDS->fv("strYear").get_asString()));
4264 GetArtistPath(artist.idArtist,strPath);
4265 artist.Save(pMain, "artist", strPath);
4268 if (!CDirectory::Exists(strPath))
4269 CLog::Log(LOGDEBUG, "%s - Not exporting item %s as it does not exist", __FUNCTION__, strPath.c_str());
4273 URIUtils::AddFileToFolder(strPath, "artist.nfo", nfoFile);
4274 if (overwrite || !CFile::Exists(nfoFile))
4276 if (!xmlDoc.SaveFile(nfoFile))
4277 CLog::Log(LOGERROR, "%s: Artist nfo export failed! ('%s')", __FUNCTION__, nfoFile.c_str());
4282 CFileItem item(artist);
4283 if (CFile::Exists(item.GetCachedArtistThumb()) && (overwrite || !CFile::Exists(URIUtils::AddFileToFolder(strPath,"folder.jpg"))))
4284 CFile::Cache(item.GetCachedArtistThumb(),URIUtils::AddFileToFolder(strPath,"folder.jpg"));
4285 if (CFile::Exists(item.GetCachedFanart()) && (overwrite || !CFile::Exists(URIUtils::AddFileToFolder(strPath,"fanart.jpg"))))
4286 CFile::Cache(item.GetCachedFanart(),URIUtils::AddFileToFolder(strPath,"fanart.jpg"));
4289 TiXmlDeclaration decl("1.0", "UTF-8", "yes");
4290 xmlDoc.InsertEndChild(decl);
4294 if ((current % 50) == 0 && progress)
4296 progress->SetLine(1, artist.strArtist);
4297 progress->SetPercentage(current * 100 / total);
4298 progress->Progress();
4299 if (progress->IsCanceled())
4314 xmlDoc.SaveFile(xmlFile);
4318 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4322 void CMusicDatabase::ImportFromXML(const CStdString &xmlFile)
4324 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4327 if (NULL == m_pDB.get()) return;
4328 if (NULL == m_pDS.get()) return;
4330 TiXmlDocument xmlDoc;
4331 if (!xmlDoc.LoadFile(xmlFile))
4334 TiXmlElement *root = xmlDoc.RootElement();
4339 progress->SetHeading(20197);
4340 progress->SetLine(0, 649);
4341 progress->SetLine(1, 330);
4342 progress->SetLine(2, "");
4343 progress->SetPercentage(0);
4344 progress->StartModal();
4345 progress->ShowProgressBar(true);
4348 TiXmlElement *entry = root->FirstChildElement();
4351 // first count the number of items...
4354 if (strnicmp(entry->Value(), "artist", 6)==0 ||
4355 strnicmp(entry->Value(), "album", 5)==0)
4357 entry = entry->NextSiblingElement();
4361 entry = root->FirstChildElement();
4364 CStdString strTitle;
4365 if (strnicmp(entry->Value(), "artist", 6) == 0)
4369 strTitle = artist.strArtist;
4370 int idArtist = GetArtistByName(artist.strArtist);
4372 SetArtistInfo(idArtist,artist);
4376 else if (strnicmp(entry->Value(), "album", 5) == 0)
4380 strTitle = album.strAlbum;
4381 int idAlbum = GetAlbumByName(album.strAlbum,album.artist);
4383 SetAlbumInfo(idAlbum,album,album.songs,false);
4387 entry = entry ->NextSiblingElement();
4388 if (progress && total)
4390 progress->SetPercentage(current * 100 / total);
4391 progress->SetLine(2, strTitle);
4392 progress->Progress();
4393 if (progress->IsCanceled())
4396 RollbackTransaction();
4401 CommitTransaction();
4403 g_infoManager.ResetLibraryBools();
4407 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4413 void CMusicDatabase::AddKaraokeData(const CSong& song)
4419 // If song.iKaraokeNumber is non-zero, we already have it in the database. Just replace the song ID.
4420 if ( song.iKaraokeNumber > 0 )
4422 CStdString strSQL = PrepareSQL("UPDATE karaokedata SET idSong=%i WHERE iKaraNumber=%i", song.idSong, song.iKaraokeNumber);
4423 m_pDS->exec(strSQL.c_str());
4427 // Add new karaoke data
4428 DWORD crc = ComputeCRC( song.strFileName );
4430 // Get the maximum number allocated
4431 strSQL=PrepareSQL( "SELECT MAX(iKaraNumber) FROM karaokedata" );
4432 if (!m_pDS->query(strSQL.c_str())) return;
4434 int iKaraokeNumber = g_advancedSettings.m_karaokeStartIndex;
4436 if ( m_pDS->num_rows() == 1 )
4437 iKaraokeNumber = m_pDS->fv("MAX(iKaraNumber)").get_asInt() + 1;
4440 strSQL=PrepareSQL( "INSERT INTO karaokedata (iKaraNumber, idSong, iKaraDelay, strKaraEncoding, strKaralyrics, strKaraLyrFileCRC) "
4441 "VALUES( %i, %i, 0, NULL, NULL, '%ul' )", iKaraokeNumber, song.idSong, crc );
4443 m_pDS->exec(strSQL.c_str());
4447 CLog::Log(LOGERROR, "%s -(%s) failed", __FUNCTION__, song.strFileName.c_str());
4452 bool CMusicDatabase::GetSongByKaraokeNumber(int number, CSong & song)
4456 // Get info from karaoke db
4457 if (NULL == m_pDB.get()) return false;
4458 if (NULL == m_pDS.get()) return false;
4460 CStdString strSQL=PrepareSQL("SELECT * FROM karaokedata where iKaraNumber=%ld", number);
4462 if (!m_pDS->query(strSQL.c_str())) return false;
4463 if (m_pDS->num_rows() == 0)
4469 int idSong = m_pDS->fv("karaokedata.idSong").get_asInt();
4472 return GetSongById( idSong, song );
4476 CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, number);
4483 void CMusicDatabase::ExportKaraokeInfo(const CStdString & outFile, bool asHTML)
4487 if (NULL == m_pDB.get()) return;
4488 if (NULL == m_pDS.get()) return;
4490 // find all karaoke songs
4491 CStdString sql = "SELECT * FROM songview WHERE iKaraNumber > 0 ORDER BY strFileName";
4493 m_pDS->query(sql.c_str());
4495 int total = m_pDS->num_rows();
4504 // Write the document
4507 if ( !file.OpenForWrite( outFile, true ) )
4510 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4513 progress->SetHeading(asHTML ? 22034 : 22035);
4514 progress->SetLine(0, 650);
4515 progress->SetLine(1, "");
4516 progress->SetLine(2, "");
4517 progress->SetPercentage(0);
4518 progress->StartModal();
4519 progress->ShowProgressBar(true);
4525 outdoc = "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></meta></head>\n"
4526 "<body>\n<table>\n";
4528 file.Write( outdoc, outdoc.size() );
4531 while (!m_pDS->eof())
4533 CSong song = GetSongFromDataset( false );
4535 songnum.Format( "%06d", song.iKaraokeNumber );
4538 outdoc = "<tr><td>" + songnum + "</td><td>" + StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator) + "</td><td>" + song.strTitle + "</td></tr>\r\n";
4540 outdoc = songnum + "\t" + StringUtils::Join(song.artist, g_advancedSettings.m_musicItemSeparator) + "\t" + song.strTitle + "\t" + song.strFileName + "\r\n";
4542 file.Write( outdoc, outdoc.size() );
4544 if ((current % 50) == 0 && progress)
4546 progress->SetPercentage(current * 100 / total);
4547 progress->Progress();
4548 if (progress->IsCanceled())
4563 outdoc = "</table>\n</body>\n</html>\n";
4564 file.Write( outdoc, outdoc.size() );
4574 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4579 void CMusicDatabase::ImportKaraokeInfo(const CStdString & inputFile)
4581 CGUIDialogProgress *progress = (CGUIDialogProgress *)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
4585 if (NULL == m_pDB.get()) return;
4589 if ( !file.Open( inputFile ) )
4591 CLog::Log( LOGERROR, "Cannot open karaoke import file %s", inputFile.c_str() );
4595 unsigned int size = (unsigned int) file.GetLength();
4600 // Read the file into memory array
4601 std::vector<char> data( size + 1 );
4603 file.Seek( 0, SEEK_SET );
4605 // Read the whole file
4606 if ( file.Read( &data[0], size) != size )
4608 CLog::Log( LOGERROR, "Cannot read karaoke import file %s", inputFile.c_str() );
4613 data[ size ] = '\0';
4617 progress->SetHeading( 22036 );
4618 progress->SetLine(0, 649);
4619 progress->SetLine(1, "");
4620 progress->SetLine(2, "");
4621 progress->SetPercentage(0);
4622 progress->StartModal();
4623 progress->ShowProgressBar(true);
4626 if (NULL == m_pDS.get()) return;
4630 // A simple state machine to parse the file
4632 char * linestart = &data[0];
4633 unsigned int offset = 0, lastpercentage = 0;
4635 for ( char * p = &data[0]; *p; p++, offset++ )
4649 unsigned int tabs = 0;
4651 for ( songpath = linestart; *songpath; songpath++ )
4653 if ( *songpath == '\t' )
4658 *songpath = '\0'; // terminate number
4663 break; // songpath points to file name
4668 int num = atoi( linestart );
4669 if ( num <= 0 || *songpath == '\0' )
4671 CLog::Log( LOGERROR, "Karaoke import: error in line %s", linestart );
4676 // Update the database
4678 if ( GetSongByFileName( songpath, song) )
4680 CStdString strSQL = PrepareSQL("UPDATE karaokedata SET iKaraNumber=%i WHERE idSong=%i", num, song.idSong);
4681 m_pDS->exec(strSQL.c_str());
4685 CLog::Log( LOGDEBUG, "Karaoke import: file '%s' was not found in database, skipped", songpath );
4690 if ( progress && (offset * 100 / size) != lastpercentage )
4692 lastpercentage = offset * 100 / size;
4693 progress->SetPercentage( lastpercentage);
4694 progress->Progress();
4695 if ( progress->IsCanceled() )
4697 RollbackTransaction();
4705 CommitTransaction();
4707 CLog::Log( LOGNOTICE, "Karaoke import: file '%s' was imported successfully", inputFile.c_str() );
4711 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4719 bool CMusicDatabase::SetKaraokeSongDelay(int idSong, int delay)
4723 if (NULL == m_pDB.get()) return false;
4724 if (NULL == m_pDS.get()) return false;
4726 CStdString strSQL = PrepareSQL("UPDATE karaokedata SET iKaraDelay=%i WHERE idSong=%i", delay, idSong);
4727 m_pDS->exec(strSQL.c_str());
4733 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4739 int CMusicDatabase::GetKaraokeSongsCount()
4743 if (NULL == m_pDB.get()) return 0;
4744 if (NULL == m_pDS.get()) return 0;
4746 if (!m_pDS->query( "select count(idSong) as NumSongs from karaokedata")) return 0;
4747 if (m_pDS->num_rows() == 0)
4753 int iNumSongs = m_pDS->fv("NumSongs").get_asInt();
4760 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4765 void CMusicDatabase::SetPropertiesFromArtist(CFileItem& item, const CArtist& artist)
4767 item.SetProperty("artist_instrument", StringUtils::Join(artist.instruments, g_advancedSettings.m_musicItemSeparator));
4768 item.SetProperty("artist_instrument_array", artist.instruments);
4769 item.SetProperty("artist_style", StringUtils::Join(artist.styles, g_advancedSettings.m_musicItemSeparator));
4770 item.SetProperty("artist_style_array", artist.styles);
4771 item.SetProperty("artist_mood", StringUtils::Join(artist.moods, g_advancedSettings.m_musicItemSeparator));
4772 item.SetProperty("artist_mood_array", artist.moods);
4773 item.SetProperty("artist_born", artist.strBorn);
4774 item.SetProperty("artist_formed", artist.strFormed);
4775 item.SetProperty("artist_description", artist.strBiography);
4776 item.SetProperty("artist_genre", StringUtils::Join(artist.genre, g_advancedSettings.m_musicItemSeparator));
4777 item.SetProperty("artist_genre_array", artist.genre);
4778 item.SetProperty("artist_died", artist.strDied);
4779 item.SetProperty("artist_disbanded", artist.strDisbanded);
4780 item.SetProperty("artist_yearsactive", StringUtils::Join(artist.yearsActive, g_advancedSettings.m_musicItemSeparator));
4781 item.SetProperty("artist_yearsactive_array", artist.yearsActive);
4784 void CMusicDatabase::SetPropertiesFromAlbum(CFileItem& item, const CAlbum& album)
4786 item.SetProperty("album_description", album.strReview);
4787 item.SetProperty("album_theme", StringUtils::Join(album.themes, g_advancedSettings.m_musicItemSeparator));
4788 item.SetProperty("album_theme_array", album.themes);
4789 item.SetProperty("album_mood", StringUtils::Join(album.moods, g_advancedSettings.m_musicItemSeparator));
4790 item.SetProperty("album_mood_array", album.moods);
4791 item.SetProperty("album_style", StringUtils::Join(album.styles, g_advancedSettings.m_musicItemSeparator));
4792 item.SetProperty("album_style_array", album.styles);
4793 item.SetProperty("album_type", album.strType);
4794 item.SetProperty("album_label", album.strLabel);
4795 item.SetProperty("album_artist", StringUtils::Join(album.artist, g_advancedSettings.m_musicItemSeparator));
4796 item.SetProperty("album_artist_array", album.artist);
4797 item.SetProperty("album_genre", StringUtils::Join(album.genre, g_advancedSettings.m_musicItemSeparator));
4798 item.SetProperty("album_genre_array", album.genre);
4799 item.SetProperty("album_title", album.strAlbum);
4800 if (album.iRating > 0)
4801 item.SetProperty("album_rating", album.iRating);
4804 void CMusicDatabase::SetPropertiesForFileItem(CFileItem& item)
4806 if (!item.HasMusicInfoTag())
4808 int idArtist = GetArtistByName(StringUtils::Join(item.GetMusicInfoTag()->GetArtist(), g_advancedSettings.m_musicItemSeparator));
4812 if (GetArtistInfo(idArtist,artist))
4813 SetPropertiesFromArtist(item,artist);
4815 int idAlbum = GetAlbumByName(item.GetMusicInfoTag()->GetAlbum(),
4816 item.GetMusicInfoTag()->GetArtist());
4820 if (GetAlbumInfo(idAlbum,album,NULL))
4821 SetPropertiesFromAlbum(item,album);
4824 CStdString strFanart = item.GetCachedFanart();
4825 if (XFILE::CFile::Exists(strFanart))
4826 item.SetProperty("fanart_image",strFanart);
4829 int CMusicDatabase::GetVariousArtistsAlbumsCount()
4831 CStdString strVariousArtists = g_localizeStrings.Get(340);
4832 int idVariousArtists=AddArtist(strVariousArtists);
4833 CStdString strSQL = PrepareSQL("select count(idAlbum) from album where idArtist=%i", idVariousArtists);
4837 if (NULL == m_pDB.get()) return 0;
4838 if (NULL == m_pDS.get()) return 0;
4839 m_pDS->query(strSQL.c_str());
4841 result = m_pDS->fv(0).get_asInt();
4846 CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
4852 void CMusicDatabase::AnnounceRemove(std::string content, int id)
4855 data["type"] = content;
4857 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnRemove", data);
4860 void CMusicDatabase::AnnounceUpdate(std::string content, int id)
4863 data["type"] = content;
4865 ANNOUNCEMENT::CAnnouncementManager::Announce(ANNOUNCEMENT::AudioLibrary, "xbmc", "OnUpdate", data);