Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

5361 lines (4680 sloc) 181.875 kb
<
/*
* Copyright (C) 2005-2012 Team XBMC
* http://www.xbmc.org
*
* This Program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2, or (at your option)
* any later version.
*
* This Program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with XBMC; see the file COPYING. If not, see
* <http://www.gnu.org/licenses/>.
*
*/
#include "threads/SystemClock.h"
#include "system.h"
#include "MusicDatabase.h"
#include "network/cddb.h"
#include "filesystem/DirectoryCache.h"
#include "filesystem/MusicDatabaseDirectory/DirectoryNode.h"
#include "filesystem/MusicDatabaseDirectory/QueryParams.h"
#include "filesystem/MusicDatabaseDirectory.h"
#include "filesystem/SpecialProtocol.h"
#include "GUIInfoManager.h"
#include "music/tags/MusicInfoTag.h"
#include "addons/AddonManager.h"
#include "addons/Scraper.h"
#include "addons/Addon.h"
#include "utils/URIUtils.h"
#include "Artist.h"
#include "Album.h"
#include "Song.h"
#include "guilib/GUIWindowManager.h"
#include "dialogs/GUIDialogOK.h"
#include "dialogs/GUIDialogProgress.h"
#include "dialogs/GUIDialogYesNo.h"
#include "dialogs/GUIDialogSelect.h"
#include "filesystem/File.h"
#include "settings/GUISettings.h"
#include "settings/AdvancedSettings.h"
#include "FileItem.h"
#include "Application.h"
#ifdef HAS_KARAOKE
#include "karaoke/karaokelyricsfactory.h"
#endif
#include "storage/MediaManager.h"
#include "settings/Settings.h"
#include "utils/StringUtils.h"
#include "guilib/LocalizeStrings.h"
#include "utils/log.h"
#include "utils/TimeUtils.h"
#include "TextureCache.h"
#include "addons/AddonInstaller.h"
#include "utils/AutoPtrHandle.h"
#include "interfaces/AnnouncementManager.h"
#include "dbwrappers/dataset.h"
#include "utils/XMLUtils.h"
#include "URL.h"
#include "playlists/SmartPlayList.h"
using namespace std;
using namespace AUTOPTR;
using namespace XFILE;
using namespace MUSICDATABASEDIRECTORY;
using ADDON::AddonPtr;
#define RECENTLY_PLAYED_LIMIT 25
#define MIN_FULL_SEARCH_LENGTH 3
#ifdef HAS_DVD_DRIVE
using namespace CDDB;
#endif
CMusicDatabase::CMusicDatabase(void)
{
}
CMusicDatabase::~CMusicDatabase(void)
{
EmptyCache();
}
bool CMusicDatabase::Open()
{
return CDatabase::Open(g_advancedSettings.m_databaseMusic);
}
bool CMusicDatabase::CreateTables()
{
BeginTransaction();
try
{
CDatabase::CreateTables();
CLog::Log(LOGINFO, "create artist table");
m_pDS->exec("CREATE TABLE artist ( idArtist integer primary key, strArtist varchar(256), strMusicBrainzArtistID text)\n");
CLog::Log(LOGINFO, "create album table");
m_pDS->exec("CREATE TABLE album ( idAlbum integer primary key, strAlbum varchar(256), strArtists text, strGenres text, iYear integer, idThumb integer, bCompilation integer not null default '0', strMusicBrainzAlbumID text )\n");
CLog::Log(LOGINFO, "create album_artist table");
m_pDS->exec("CREATE TABLE album_artist ( idArtist integer, idAlbum integer, boolFeatured integer, iOrder integer )\n");
CLog::Log(LOGINFO, "create album_genre table");
m_pDS->exec("CREATE TABLE album_genre ( idGenre integer, idAlbum integer, iOrder integer )\n");
CLog::Log(LOGINFO, "create genre table");
m_pDS->exec("CREATE TABLE genre ( idGenre integer primary key, strGenre varchar(256))\n");
CLog::Log(LOGINFO, "create path table");
m_pDS->exec("CREATE TABLE path ( idPath integer primary key, strPath varchar(512), strHash text)\n");
CLog::Log(LOGINFO, "create song table");
m_pDS->exec("CREATE TABLE song ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, strGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)\n");
CLog::Log(LOGINFO, "create song_artist table");
m_pDS->exec("CREATE TABLE song_artist ( idArtist integer, idSong integer, boolFeatured integer, iOrder integer )\n");
CLog::Log(LOGINFO, "create song_genre table");
m_pDS->exec("CREATE TABLE song_genre ( idGenre integer, idSong integer, iOrder integer )\n");
CLog::Log(LOGINFO, "create albuminfo table");
m_pDS->exec("CREATE TABLE albuminfo ( idAlbumInfo integer primary key, idAlbum integer, iYear integer, strMoods text, strStyles text, strThemes text, strReview text, strImage text, strLabel text, strType text, iRating integer)\n");
CLog::Log(LOGINFO, "create albuminfosong table");
m_pDS->exec("CREATE TABLE albuminfosong ( idAlbumInfoSong integer primary key, idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer)\n");
CLog::Log(LOGINFO, "create artistnfo table");
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");
CLog::Log(LOGINFO, "create content table");
m_pDS->exec("CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text)\n");
CLog::Log(LOGINFO, "create discography table");
m_pDS->exec("CREATE TABLE discography (idArtist integer, strAlbum text, strYear text)\n");
CLog::Log(LOGINFO, "create karaokedata table");
m_pDS->exec("CREATE TABLE karaokedata ( iKaraNumber integer primary key, idSong integer, iKaraDelay integer, strKaraEncoding text, "
"strKaralyrics text, strKaraLyrFileCRC text )\n");
CLog::Log(LOGINFO, "create album index");
m_pDS->exec("CREATE INDEX idxAlbum ON album(strAlbum)");
CLog::Log(LOGINFO, "create album compilation index");
m_pDS->exec("CREATE INDEX idxAlbum_1 ON album(bCompilation)");
CLog::Log(LOGINFO, "create album_artist indexes");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_1 ON album_artist ( idAlbum, idArtist )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_2 ON album_artist ( idArtist, idAlbum )\n");
m_pDS->exec("CREATE INDEX idxAlbumArtist_3 ON album_artist ( boolFeatured )\n");
CLog::Log(LOGINFO, "create album_genre indexes");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_1 ON album_genre ( idAlbum, idGenre )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_2 ON album_genre ( idGenre, idAlbum )\n");
CLog::Log(LOGINFO, "create genre index");
m_pDS->exec("CREATE INDEX idxGenre ON genre(strGenre)");
CLog::Log(LOGINFO, "create artist index");
m_pDS->exec("CREATE INDEX idxArtist ON artist(strArtist)");
m_pDS->exec("CREATE UNIQUE INDEX idxArtist1 ON artist(strMusicBrainzArtistID(36))");
CLog::Log(LOGINFO, "create path index");
m_pDS->exec("CREATE INDEX idxPath ON path(strPath)");
CLog::Log(LOGINFO, "create song index");
m_pDS->exec("CREATE INDEX idxSong ON song(strTitle)");
CLog::Log(LOGINFO, "create song index1");
m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
CLog::Log(LOGINFO, "create song index2");
m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
CLog::Log(LOGINFO, "create song index3");
m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
CLog::Log(LOGINFO, "create song index6");
m_pDS->exec("CREATE UNIQUE INDEX idxSong6 ON song( idPath, strFileName(255) )");
CLog::Log(LOGINFO, "create song_artist indexes");
m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_1 ON song_artist ( idSong, idArtist )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_2 ON song_artist ( idArtist, idSong )\n");
m_pDS->exec("CREATE INDEX idxSongArtist_3 ON song_artist ( boolFeatured )\n");
CLog::Log(LOGINFO, "create song_genre indexes");
m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_1 ON song_genre ( idSong, idGenre )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_2 ON song_genre ( idGenre, idSong )\n");
//m_pDS->exec("CREATE INDEX idxSong ON song(dwFileNameCRC)");
CLog::Log(LOGINFO, "create artistinfo index");
m_pDS->exec("CREATE INDEX idxArtistInfo on artistinfo(idArtist)");
CLog::Log(LOGINFO, "create albuminfo index");
m_pDS->exec("CREATE INDEX idxAlbumInfo on albuminfo(idAlbum)");
CLog::Log(LOGINFO, "create karaokedata index");
m_pDS->exec("CREATE INDEX idxKarSong on karaokedata(idSong)");
CLog::Log(LOGINFO, "seed karaokedata index");
m_pDS->exec(PrepareSQL("INSERT INTO karaokedata (iKaraNumber) VALUES (%d)", g_advancedSettings.m_karaokeStartIndex - 1));
m_pDS->exec(PrepareSQL("DELETE FROM karaokedata WHERE iKaraNumber = %d", g_advancedSettings.m_karaokeStartIndex - 1));
// Trigger
CLog::Log(LOGINFO, "create albuminfo trigger");
m_pDS->exec("CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW BEGIN delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo; END");
CLog::Log(LOGINFO, "create art table, index and triggers");
m_pDS->exec("CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT)");
m_pDS->exec("CREATE INDEX ix_art ON art(media_id, media_type(20), type(20))");
m_pDS->exec("CREATE TRIGGER delete_song AFTER DELETE ON song FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idSong AND media_type='song'; END");
m_pDS->exec("CREATE TRIGGER delete_album AFTER DELETE ON album FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album'; END");
m_pDS->exec("CREATE TRIGGER delete_artist AFTER DELETE ON artist FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'; END");
// we create views last to ensure all indexes are rolled in
CreateViews();
// Add 'Karaoke' genre
AddGenre( "Karaoke" );
}
catch (...)
{
CLog::Log(LOGERROR, "%s unable to create tables:%i", __FUNCTION__, (int)GetLastError());
RollbackTransaction();
return false;
}
CommitTransaction();
return true;
}
void CMusicDatabase::CreateViews()
{
CLog::Log(LOGINFO, "create song view");
m_pDS->exec("DROP VIEW IF EXISTS songview");
m_pDS->exec("CREATE VIEW songview AS SELECT "
" song.idSong AS idSong, "
" song.strArtists AS strArtists,"
" song.strGenres AS strGenres,"
" strTitle, iTrack, iDuration,"
" song.iYear AS iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID,"
" iTimesPlayed, iStartOffset, iEndOffset, lastplayed,"
" rating, comment, song.idAlbum AS idAlbum, strAlbum, strPath,"
" iKaraNumber, iKaraDelay, strKaraEncoding,"
" album.bCompilation AS bCompilation "
"FROM song"
" JOIN album ON"
" song.idAlbum=album.idAlbum"
" JOIN path ON"
" song.idPath=path.idPath"
" LEFT OUTER JOIN karaokedata ON"
" song.idSong=karaokedata.idSong");
CLog::Log(LOGINFO, "create album view");
m_pDS->exec("DROP VIEW IF EXISTS albumview");
m_pDS->exec("CREATE VIEW albumview AS SELECT"
" album.idAlbum AS idAlbum, strAlbum, "
" album.strMusicBrainzAlbumID AS strMusicBrainzAlbumID, "
" album.strArtists AS strArtists,"
" album.strGenres AS strGenres, "
" album.iYear AS iYear,"
" idAlbumInfo, strMoods, strStyles, strThemes,"
" strReview, strLabel, strType, strImage, iRating, "
" bCompilation "
"FROM album "
" LEFT OUTER JOIN albuminfo ON"
" album.idAlbum=albuminfo.idAlbum");
CLog::Log(LOGINFO, "create artist view");
m_pDS->exec("DROP VIEW IF EXISTS artistview");
m_pDS->exec("CREATE VIEW artistview AS SELECT"
" artist.idArtist AS idArtist, strArtist, "
" artist.strMusicBrainzArtistID AS strMusicBrainzArtistID, "
" strBorn, strFormed, strGenres,"
" strMoods, strStyles, strInstruments, "
" strBiography, strDied, strDisbanded, "
" strYearsActive, strImage, strFanart "
"FROM artist "
" LEFT OUTER JOIN artistinfo ON"
" artist.idArtist = artistinfo.idArtist");
}
int CMusicDatabase::AddFileItem(const CFileItem& pItem, const VECALBUMS& albumHints)
{
const MUSIC_INFO::CMusicInfoTag& tag = *pItem.GetMusicInfoTag();
// Check for a valid path name
CStdString strPathAndFileName = tag.GetURL().IsEmpty() ? pItem.GetPath() : tag.GetURL();
if (strPathAndFileName.IsEmpty())
{
CLog::Log(LOGERROR, "skipping song since it doesn't have a filename");
return -1;
}
// Find any album hints for this song
bool albumCompilationHint;
std::vector<std::string> albumArtistHint;
std::map<std::string, std::string> albumArtHint;
for (VECALBUMS::const_iterator it = albumHints.begin(); it != albumHints.end(); ++it)
{
if (it->strAlbum == tag.GetAlbum())
{
albumArtistHint = it->artist;
albumCompilationHint = it->bCompilation;
albumArtHint = it->art;
}
}
int idAlbum;
if (!tag.GetAlbumArtist().empty())
idAlbum = AddAlbum(tag.GetAlbum(), StringUtils::Join(tag.GetAlbumArtist(), g_advancedSettings.m_musicItemSeparator), tag.GetMusicBrainzAlbumID(),StringUtils::Join(tag.GetGenre(), g_advancedSettings.m_musicItemSeparator), tag.GetYear(), tag.GetCompilation());
else
idAlbum = AddAlbum(tag.GetAlbum(), StringUtils::Join(albumArtistHint, g_advancedSettings.m_musicItemSeparator), tag.GetMusicBrainzAlbumID(), StringUtils::Join(tag.GetGenre(), g_advancedSettings.m_musicItemSeparator), tag.GetYear(), albumCompilationHint);
bool bHasKaraoke = false;
#ifdef HAS_KARAOKE
bHasKaraoke = CKaraokeLyricsFactory::HasLyrics(strPathAndFileName);
#endif
int idSong = AddSong(tag.GetTitle(),
StringUtils::Join(tag.GetArtist(), g_advancedSettings.m_musicItemSeparator),
idAlbum,
strPathAndFileName,
StringUtils::Join(tag.GetGenre(), g_advancedSettings.m_musicItemSeparator),
tag.GetTrackNumber(),
tag.GetDuration(),
tag.GetYear(),
tag.GetMusicBrainzTrackID(),
tag.GetComment(),
pItem.m_lStartOffset,
pItem.m_lEndOffset);
DeleteValues("song_artist", PrepareSQL("idSong = %d", idSong));
DeleteValues("song_genre", PrepareSQL("idSong = %d", idSong));
// Add the artists
const vector<string>& albumArtist = tag.GetAlbumArtist().empty() ? albumArtistHint : tag.GetAlbumArtist();
for (unsigned int index = 0; index < albumArtist.size(); index++)
{
int idAlbumArtist = AddArtist(albumArtist[index]);
AddAlbumArtist(idAlbumArtist, idAlbum, index > 0 ? true : false, index);
}
for (unsigned int index = 0; index < tag.GetArtist().size(); index++)
{
int idArtist = AddArtist(tag.GetArtist()[index]);
AddSongArtist(idArtist, idSong, index > 0 ? true : false, index);
}
unsigned int index = 0;
// If this is karaoke song, change the genre to 'Karaoke' (and add it if it's not there)
if ( bHasKaraoke && g_advancedSettings.m_karaokeChangeGenreForKaraokeSongs )
{
int idGenre = AddGenre("Karaoke");
AddSongGenre(idGenre, idSong, index);
AddAlbumGenre(idGenre, idAlbum, index++);
}
vector<string> genre = tag.GetGenre();
for (vector<string>::const_iterator i = genre.begin(); i != genre.end(); ++i)
{
// index will be wrong for albums, but ordering is not all that relevant
// for genres anyway
int idGenre = AddGenre(*i);
AddSongGenre(idGenre, idSong, index);
AddAlbumGenre(idGenre, idAlbum, index++);
}
// Add karaoke information
if (bHasKaraoke)
AddKaraokeData(idSong, strPathAndFileName);
if (!pItem.GetThumbnailImage().empty())
SetArtForItem(idSong, "song", "thumb", pItem.GetThumbnailImage());
if (!albumArtHint.empty())
SetArtForItem(idAlbum, "album", albumArtHint);
AnnounceUpdate("song", idSong);
return idSong;
}
int CMusicDatabase::AddSong(const CStdString& strTitle, const CStdString& strArtists, int idAlbum, const CStdString& strPathAndFileName, const CStdString& strGenres, int iTrack, int iDuration, int iYear, const CStdString& strMusicBrainzTrackID, const CStdString& strComment, int iStartOffset, int iEndOffset)
{
int idSong = -1;
CStdString strSQL;
try
{
// We need at least the title
if (strTitle.IsEmpty())
return -1;
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
CStdString strPath, strFileName;
URIUtils::Split(strPathAndFileName, strPath, strFileName);
int idPath = AddPath(strPath);
DWORD crc = ComputeCRC(strPathAndFileName);
int idSong;
strSQL = PrepareSQL("SELECT idSong FROM song WHERE idPath = %i AND strFileName = '%s'",
idPath, strFileName.c_str());
if (!m_pDS->query(strSQL.c_str()))
return -1;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
strSQL = PrepareSQL("INSERT INTO song "
" (idAlbum, idPath, "
" strArtists, strGenres, strTitle, "
" iTrack, iDuration, iYear, "
" dwFileNameCRC, "
" strFileName, strMusicBrainzTrackID, "
" iStartOffset, iEndOffset, "
" comment) "
"VALUES "
" ( %i, %i, "
" '%s', '%s', '%s', "
" %i, %i, %i,"
" '%ul', "
" '%s', '%s', "
" %i, %i,"
" '%s')",
idAlbum, idPath,
strArtists.c_str(), strGenres.c_str(), strTitle.c_str(),
iTrack, iDuration, iYear,
crc,
strFileName.c_str(), strMusicBrainzTrackID.c_str(),
iStartOffset, iEndOffset,
strComment.c_str());
m_pDS->exec(strSQL.c_str());
idSong = (int)m_pDS->lastinsertid();
}
else
{
idSong = m_pDS->fv(0).get_asInt();
m_pDS->close();
strSQL = PrepareSQL("UPDATE song "
" SET idAlbum = %i, idPath = %i, "
" strArtists = '%s', strGenres = '%s', strTitle = '%s', "
" iTrack = %i, iDuration = %i, iYear = %i, "
" dwFileNameCRC = '%ul', "
" strFileName = '%s', "
" strMusicBrainzTrackID = '%s', "
" iStartOffset = %i, iEndOffset = %i, "
" comment = '%s' "
" WHERE idSong = %i",
idAlbum, idPath,
strArtists.c_str(), strGenres.c_str(), strTitle.c_str(),
iTrack, iDuration, iYear,
crc,
strFileName.c_str(),
strMusicBrainzTrackID.c_str(),
iStartOffset, iEndOffset,
strComment.c_str(),
idSong);
m_pDS->exec(strSQL.c_str());
}
return idSong;
AnnounceUpdate("song", idSong);
}
catch (...)
{
CLog::Log(LOGERROR, "musicdatabase:unable to addsong (%s)", strSQL.c_str());
}
return idSong;
}
int CMusicDatabase::UpdateSong(const CSong& song, int idSong /* = -1 */)
{
CStdString sql;
if (idSong < 0)
idSong = song.idSong;
if (idSong < 0)
return -1;
// delete linked songs
// we don't delete from the song table here because
// AddSong will update the existing record
sql.Format("delete from song_artist where idSong=%d", idSong);
ExecuteQuery(sql);
sql.Format("delete from song_genre where idSong=%d", idSong);
ExecuteQuery(sql);
sql.Format("delete from karaokedata where idSong=%d", idSong);
ExecuteQuery(sql);
CSong newSong = song;
// Make sure newSong.idSong has a valid value (> 0)
newSong.idSong = idSong;
// re-add the song
// newSong.idSong = AddSong(newSong, false);
if (newSong.idSong < 0)
return -1;
return newSong.idSong;
}
bool CMusicDatabase::DeleteSong(int idSong)
{
bool bSuccess = true;
bSuccess &= DeleteValues("song_artist", PrepareSQL("idSong = %d", idSong));
bSuccess &= DeleteValues("song_genre", PrepareSQL("idSong = %d", idSong));
bSuccess &= DeleteValues("song", PrepareSQL("idSong=%d", idSong));
return bSuccess;
};
int CMusicDatabase::AddAlbum(const CStdString& strAlbum1, const CStdString &strArtist, const CStdString& strMusicBrainzAlbumID, const CStdString& strGenre, int year, bool bCompilation)
{
CStdString strSQL;
try
{
CStdString strAlbum=strAlbum1;
strAlbum.TrimLeft(" ");
strAlbum.TrimRight(" ");
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
map <CStdString, CAlbum>::const_iterator it;
it = m_albumCache.find(strAlbum + strArtist);
if (it != m_albumCache.end())
return it->second.idAlbum;
strSQL=PrepareSQL("select * from album where strArtists='%s' and strAlbum like '%s'", strArtist.c_str(), strAlbum.c_str());
m_pDS->query(strSQL.c_str());
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
// doesnt exists, add it
strSQL=PrepareSQL("insert into album (idAlbum, strAlbum, strArtists, strMusicBrainzAlbumID, strGenres, iYear, bCompilation) values( NULL, '%s', '%s', '%s', '%s', %i, %i)", strAlbum.c_str(), strArtist.c_str(), strMusicBrainzAlbumID.c_str(), strGenre.c_str(), year, bCompilation);
m_pDS->exec(strSQL.c_str());
CAlbum album;
album.idAlbum = (int)m_pDS->lastinsertid();
album.strAlbum = strAlbum;
album.artist = StringUtils::Split(strArtist, g_advancedSettings.m_musicItemSeparator);
m_albumCache.insert(pair<CStdString, CAlbum>(album.strAlbum + strArtist, album));
return album.idAlbum;
}
else
{
// exists in our database and not scanned during this scan, so we should update it as the details
// may have changed (there's a reason we're rescanning, afterall!)
CAlbum album;
album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
album.strAlbum = strAlbum;
album.artist = StringUtils::Split(strArtist, g_advancedSettings.m_musicItemSeparator);
m_albumCache.insert(pair<CStdString, CAlbum>(album.strAlbum + strArtist, album));
m_pDS->close();
strSQL=PrepareSQL("update album set strGenres='%s', iYear=%i where idAlbum=%i", strGenre.c_str(), year, album.idAlbum);
m_pDS->exec(strSQL.c_str());
// and clear the link tables - these are updated in AddSong()
strSQL=PrepareSQL("delete from album_artist where idAlbum=%i", album.idAlbum);
m_pDS->exec(strSQL.c_str());
strSQL=PrepareSQL("delete from album_genre where idAlbum=%i", album.idAlbum);
m_pDS->exec(strSQL.c_str());
return album.idAlbum;
}
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
}
return -1;
}
int CMusicDatabase::AddGenre(const CStdString& strGenre1)
{
CStdString strSQL;
try
{
CStdString strGenre = strGenre1;
strGenre.TrimLeft(" ");
strGenre.TrimRight(" ");
if (strGenre.IsEmpty())
strGenre=g_localizeStrings.Get(13205); // Unknown
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
map <CStdString, int>::const_iterator it;
it = m_genreCache.find(strGenre);
if (it != m_genreCache.end())
return it->second;
strSQL=PrepareSQL("select * from genre where strGenre like '%s'", strGenre.c_str());
m_pDS->query(strSQL.c_str());
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
// doesnt exists, add it
strSQL=PrepareSQL("insert into genre (idGenre, strGenre) values( NULL, '%s' )", strGenre.c_str());
m_pDS->exec(strSQL.c_str());
int idGenre = (int)m_pDS->lastinsertid();
m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
return idGenre;
}
else
{
int idGenre = m_pDS->fv("idGenre").get_asInt();
m_genreCache.insert(pair<CStdString, int>(strGenre1, idGenre));
m_pDS->close();
return idGenre;
}
}
catch (...)
{
CLog::Log(LOGERROR, "musicdatabase:unable to addgenre (%s)", strSQL.c_str());
}
return -1;
}
int CMusicDatabase::AddArtist(const CStdString& strArtist1)
{
CStdString strSQL;
try
{
CStdString strArtist = strArtist1;
strArtist.TrimLeft(" ");
strArtist.TrimRight(" ");
if (strArtist.IsEmpty())
strArtist=g_localizeStrings.Get(13205); // Unknown
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
map <CStdString, int>::const_iterator it;
it = m_artistCache.find(strArtist);
if (it != m_artistCache.end())
return it->second;//.idArtist;
strSQL=PrepareSQL("select * from artist where strArtist like '%s'", strArtist.c_str());
m_pDS->query(strSQL.c_str());
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
// doesnt exists, add it
strSQL=PrepareSQL("insert into artist (idArtist, strArtist) values( NULL, '%s' )", strArtist.c_str());
m_pDS->exec(strSQL.c_str());
int idArtist = (int)m_pDS->lastinsertid();
m_artistCache.insert(pair<CStdString, int>(strArtist1, idArtist));
return idArtist;
}
else
{
int idArtist = (int)m_pDS->fv("idArtist").get_asInt();
m_artistCache.insert(pair<CStdString, int>(strArtist1, idArtist));
m_pDS->close();
return idArtist;
}
}
catch (...)
{
CLog::Log(LOGERROR, "musicdatabase:unable to addartist (%s)", strSQL.c_str());
}
return -1;
}
bool CMusicDatabase::UpdateArtist(int idArtist, const CStdString& strArtist)
{
return ExecuteQuery(PrepareSQL("UPDATE artist SET strArtist = '%s' WHERE idArtist = %d", strArtist.c_str(), idArtist));
}
bool CMusicDatabase::AddSongArtist(int idArtist, int idSong, bool featured, int iOrder)
{
CStdString strSQL;
strSQL=PrepareSQL("replace into song_artist (idArtist, idSong, boolFeatured, iOrder) values(%i,%i,%i,%i)",
idArtist, idSong, featured == true ? 1 : 0, iOrder);
return ExecuteQuery(strSQL);
};
bool CMusicDatabase::AddAlbumArtist(int idArtist, int idAlbum, bool featured, int iOrder)
{
CStdString strSQL;
strSQL=PrepareSQL("replace into album_artist (idArtist, idAlbum, boolFeatured, iOrder) values(%i,%i,%i,%i)",
idArtist, idAlbum, featured == true ? 1 : 0, iOrder);
return ExecuteQuery(strSQL);
};
bool CMusicDatabase::AddSongGenre(int idGenre, int idSong, int iOrder)
{
if (idGenre == -1 || idSong == -1)
return true;
CStdString strSQL;
strSQL=PrepareSQL("replace into song_genre (idGenre, idSong, iOrder) values(%i,%i,%i)",
idGenre, idSong, iOrder);
return ExecuteQuery(strSQL);};
bool CMusicDatabase::AddAlbumGenre(int idGenre, int idAlbum, int iOrder)
{
if (idGenre == -1 || idAlbum == -1)
return true;
CStdString strSQL;
strSQL=PrepareSQL("replace into album_genre (idGenre, idAlbum, iOrder) values(%i,%i,%i)",
idGenre, idAlbum, iOrder);
return ExecuteQuery(strSQL);
};
bool CMusicDatabase::GetAlbumsByArtist(int idArtist, bool includeFeatured, std::vector<int> &albums)
{
try
{
CStdString strSQL, strPrepSQL;
strPrepSQL = "select idAlbum from album_artist where idArtist=%i";
if (includeFeatured == false)
strPrepSQL += " AND boolFeatured = 0";
strSQL=PrepareSQL(strPrepSQL, idArtist);
if (!m_pDS->query(strSQL.c_str()))
return false;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
albums.push_back(m_pDS->fv("idAlbum").get_asInt());
m_pDS->next();
}
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idArtist);
}
return false;
}
bool CMusicDatabase::GetArtistsByAlbum(int idAlbum, bool includeFeatured, std::vector<int>& artists)
{
try
{
CStdString strSQL, strPrepSQL;
strPrepSQL = "select idArtist from album_artist where idAlbum=%i";
if (includeFeatured == false)
strPrepSQL += " AND boolFeatured = 0";
strSQL=PrepareSQL(strPrepSQL, idAlbum);
if (!m_pDS->query(strSQL.c_str()))
return false;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
artists.push_back(m_pDS->fv("idArtist").get_asInt());
m_pDS->next();
}
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
}
return false;
}
bool CMusicDatabase::GetSongsByArtist(int idArtist, bool includeFeatured, std::vector<int> &songs)
{
try
{
CStdString strSQL, strPrepSQL;
strPrepSQL = "select idSong from song_artist where idArtist=%i";
if (includeFeatured == false)
strPrepSQL += " AND boolFeatured = 0";
strSQL=PrepareSQL(strPrepSQL, idArtist);
if (!m_pDS->query(strSQL.c_str()))
return false;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
songs.push_back(m_pDS->fv("idSong").get_asInt());
m_pDS->next();
}
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idArtist);
}
return false;
};
bool CMusicDatabase::GetArtistsBySong(int idSong, bool includeFeatured, std::vector<int> &artists)
{
try
{
CStdString strSQL, strPrepSQL;
strPrepSQL = "select idArtist from song_artist where idSong=%i";
if (includeFeatured == false)
strPrepSQL += " AND boolFeatured = 0";
strSQL=PrepareSQL(strPrepSQL, idSong);
if (!m_pDS->query(strSQL.c_str()))
return false;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
artists.push_back(m_pDS->fv("idArtist").get_asInt());
m_pDS->next();
}
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
}
return false;
};
int CMusicDatabase::AddPath(const CStdString& strPath1)
{
CStdString strSQL;
try
{
CStdString strPath(strPath1);
if (!URIUtils::HasSlashAtEnd(strPath))
URIUtils::AddSlashAtEnd(strPath);
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
map <CStdString, int>::const_iterator it;
it = m_pathCache.find(strPath);
if (it != m_pathCache.end())
return it->second;
strSQL=PrepareSQL( "select * from path where strPath='%s'", strPath.c_str());
m_pDS->query(strSQL.c_str());
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
// doesnt exists, add it
strSQL=PrepareSQL("insert into path (idPath, strPath) values( NULL, '%s' )", strPath.c_str());
m_pDS->exec(strSQL.c_str());
int idPath = (int)m_pDS->lastinsertid();
m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
return idPath;
}
else
{
int idPath = m_pDS->fv("idPath").get_asInt();
m_pathCache.insert(pair<CStdString, int>(strPath, idPath));
m_pDS->close();
return idPath;
}
}
catch (...)
{
CLog::Log(LOGERROR, "musicdatabase:unable to addpath (%s)", strSQL.c_str());
}
return -1;
}
CSong CMusicDatabase::GetSongFromDataset(bool bWithMusicDbPath/*=false*/)
{
CSong song;
song.idSong = m_pDS->fv(song_idSong).get_asInt();
// get the full artist string
song.artist = StringUtils::Split(m_pDS->fv(song_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
// and the full genre string
song.genre = StringUtils::Split(m_pDS->fv(song_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
// and the rest...
song.strAlbum = m_pDS->fv(song_strAlbum).get_asString();
song.iAlbumId = m_pDS->fv(song_idAlbum).get_asInt();
song.iTrack = m_pDS->fv(song_iTrack).get_asInt() ;
song.iDuration = m_pDS->fv(song_iDuration).get_asInt() ;
song.iYear = m_pDS->fv(song_iYear).get_asInt() ;
song.strTitle = m_pDS->fv(song_strTitle).get_asString();
song.iTimesPlayed = m_pDS->fv(song_iTimesPlayed).get_asInt();
song.lastPlayed.SetFromDBDateTime(m_pDS->fv(song_lastplayed).get_asString());
song.iStartOffset = m_pDS->fv(song_iStartOffset).get_asInt();
song.iEndOffset = m_pDS->fv(song_iEndOffset).get_asInt();
song.strMusicBrainzTrackID = m_pDS->fv(song_strMusicBrainzTrackID).get_asString();
song.rating = m_pDS->fv(song_rating).get_asChar();
song.strComment = m_pDS->fv(song_comment).get_asString();
song.iKaraokeNumber = m_pDS->fv(song_iKarNumber).get_asInt();
song.strKaraokeLyrEncoding = m_pDS->fv(song_strKarEncoding).get_asString();
song.iKaraokeDelay = m_pDS->fv(song_iKarDelay).get_asInt();
song.bCompilation = m_pDS->fv(song_bCompilation).get_asInt() == 1;
// Get filename with full path
if (!bWithMusicDbPath)
URIUtils::AddFileToFolder(m_pDS->fv(song_strPath).get_asString(), m_pDS->fv(song_strFileName).get_asString(), song.strFileName);
else
{
CStdString strFileName = m_pDS->fv(song_strFileName).get_asString();
CStdString strExt = URIUtils::GetExtension(strFileName);
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());
}
return song;
}
void CMusicDatabase::GetFileItemFromDataset(CFileItem* item, const CStdString& strMusicDBbasePath)
{
return GetFileItemFromDataset(m_pDS->get_sql_record(), item, strMusicDBbasePath);
}
void CMusicDatabase::GetFileItemFromDataset(const dbiplus::sql_record* const record, CFileItem* item, const CStdString& strMusicDBbasePath)
{
// get the full artist string
item->GetMusicInfoTag()->SetArtist(StringUtils::Split(record->at(song_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator));
// and the full genre string
item->GetMusicInfoTag()->SetGenre(record->at(song_strGenres).get_asString());
// and the rest...
item->GetMusicInfoTag()->SetAlbum(record->at(song_strAlbum).get_asString());
item->GetMusicInfoTag()->SetAlbumId(record->at(song_idAlbum).get_asInt());
item->GetMusicInfoTag()->SetTrackAndDiskNumber(record->at(song_iTrack).get_asInt());
item->GetMusicInfoTag()->SetDuration(record->at(song_iDuration).get_asInt());
item->GetMusicInfoTag()->SetDatabaseId(record->at(song_idSong).get_asInt(), "song");
SYSTEMTIME stTime;
stTime.wYear = (WORD)record->at(song_iYear).get_asInt();
item->GetMusicInfoTag()->SetReleaseDate(stTime);
item->GetMusicInfoTag()->SetTitle(record->at(song_strTitle).get_asString());
item->SetLabel(record->at(song_strTitle).get_asString());
item->m_lStartOffset = record->at(song_iStartOffset).get_asInt();
item->SetProperty("item_start", item->m_lStartOffset);
item->m_lEndOffset = record->at(song_iEndOffset).get_asInt();
item->GetMusicInfoTag()->SetMusicBrainzTrackID(record->at(song_strMusicBrainzTrackID).get_asString());
item->GetMusicInfoTag()->SetRating(record->at(song_rating).get_asChar());
item->GetMusicInfoTag()->SetComment(record->at(song_comment).get_asString());
item->GetMusicInfoTag()->SetPlayCount(record->at(song_iTimesPlayed).get_asInt());
item->GetMusicInfoTag()->SetLastPlayed(record->at(song_lastplayed).get_asString());
CStdString strRealPath;
URIUtils::AddFileToFolder(record->at(song_strPath).get_asString(), record->at(song_strFileName).get_asString(), strRealPath);
item->GetMusicInfoTag()->SetURL(strRealPath);
item->GetMusicInfoTag()->SetCompilation(m_pDS->fv(song_bCompilation).get_asInt() == 1);
item->GetMusicInfoTag()->SetLoaded(true);
// Get filename with full path
if (strMusicDBbasePath.IsEmpty())
item->SetPath(strRealPath);
else
{
CMusicDbUrl itemUrl;
if (!itemUrl.FromString(strMusicDBbasePath))
return;
CStdString strFileName = record->at(song_strFileName).get_asString();
CStdString strExt = URIUtils::GetExtension(strFileName);
CStdString path; path.Format("%ld%s", record->at(song_idSong).get_asInt(), strExt.c_str());
itemUrl.AppendPath(path);
item->SetPath(itemUrl.ToString());
}
}
CAlbum CMusicDatabase::GetAlbumFromDataset(dbiplus::Dataset* pDS, bool imageURL /* = false*/)
{
return GetAlbumFromDataset(pDS->get_sql_record(), imageURL);
}
CAlbum CMusicDatabase::GetAlbumFromDataset(const dbiplus::sql_record* const record, bool imageURL /* = false*/)
{
CAlbum album;
album.idAlbum = record->at(album_idAlbum).get_asInt();
album.strAlbum = record->at(album_strAlbum).get_asString();
if (album.strAlbum.IsEmpty())
album.strAlbum = g_localizeStrings.Get(1050);
album.strMusicBrainzAlbumID = record->at(album_strMusicBrainzAlbumID).get_asString();
album.artist = StringUtils::Split(record->at(album_strArtists).get_asString(), g_advancedSettings.m_musicItemSeparator);
album.genre = StringUtils::Split(record->at(album_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
album.iYear = record->at(album_iYear).get_asInt();
if (imageURL)
album.thumbURL.ParseString(record->at(album_strThumbURL).get_asString());
album.iRating = record->at(album_iRating).get_asInt();
album.iYear = record->at(album_iYear).get_asInt();
album.strReview = record->at(album_strReview).get_asString();
album.styles = StringUtils::Split(record->at(album_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
album.moods = StringUtils::Split(record->at(album_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
album.themes = StringUtils::Split(record->at(album_strThemes).get_asString(), g_advancedSettings.m_musicItemSeparator);
album.strLabel = record->at(album_strLabel).get_asString();
album.strType = record->at(album_strType).get_asString();
album.bCompilation = record->at(album_bCompilation).get_asInt() == 1;
return album;
}
CArtist CMusicDatabase::GetArtistFromDataset(dbiplus::Dataset* pDS, bool needThumb)
{
return GetArtistFromDataset(pDS->get_sql_record(), needThumb);
}
CArtist CMusicDatabase::GetArtistFromDataset(const dbiplus::sql_record* const record, bool needThumb /* = true */)
{
CArtist artist;
artist.idArtist = record->at(artist_idArtist).get_asInt();
artist.strArtist = record->at(artist_strArtist).get_asString();
artist.strMusicBrainzArtistID = record->at(artist_strMusicBrainzArtistID).get_asString();
artist.genre = StringUtils::Split(record->at(artist_strGenres).get_asString(), g_advancedSettings.m_musicItemSeparator);
artist.strBiography = record->at(artist_strBiography).get_asString();
artist.styles = StringUtils::Split(record->at(artist_strStyles).get_asString(), g_advancedSettings.m_musicItemSeparator);
artist.moods = StringUtils::Split(record->at(artist_strMoods).get_asString(), g_advancedSettings.m_musicItemSeparator);
artist.strBorn = record->at(artist_strBorn).get_asString();
artist.strFormed = record->at(artist_strFormed).get_asString();
artist.strDied = record->at(artist_strDied).get_asString();
artist.strDisbanded = record->at(artist_strDisbanded).get_asString();
artist.yearsActive = StringUtils::Split(record->at(artist_strYearsActive).get_asString(), g_advancedSettings.m_musicItemSeparator);
artist.instruments = StringUtils::Split(record->at(artist_strInstruments).get_asString(), g_advancedSettings.m_musicItemSeparator);
if (needThumb)
{
artist.fanart.m_xml = record->at(artist_strFanart).get_asString();
artist.fanart.Unpack();
artist.thumbURL.ParseString(record->at(artist_strImage).get_asString());
}
return artist;
}
bool CMusicDatabase::GetSongByFileName(const CStdString& strFileName, CSong& song, int startOffset)
{
try
{
song.Clear();
CURL url(strFileName);
if (url.GetProtocol()=="musicdb")
{
CStdString strFile = URIUtils::GetFileName(strFileName);
URIUtils::RemoveExtension(strFile);
return GetSongById(atol(strFile.c_str()), song);
}
CStdString strPath;
URIUtils::GetDirectory(strFileName, strPath);
URIUtils::AddSlashAtEnd(strPath);
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
DWORD crc = ComputeCRC(strFileName);
CStdString strSQL=PrepareSQL("select * from songview "
"where dwFileNameCRC='%ul' and strPath='%s'"
, crc,
strPath.c_str());
if (startOffset)
strSQL += PrepareSQL(" AND iStartOffset=%i", startOffset);
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
song = GetSongFromDataset();
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strFileName.c_str());
}
return false;
}
int CMusicDatabase::GetAlbumIdByPath(const CStdString& strPath)
{
try
{
CStdString strSQL=PrepareSQL("select distinct idAlbum from song join path on song.idPath = path.idPath where path.strPath='%s'", strPath.c_str());
m_pDS->query(strSQL.c_str());
if (m_pDS->eof())
return -1;
int idAlbum = m_pDS->fv(0).get_asInt();
m_pDS->close();
return idAlbum;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
}
return false;
}
int CMusicDatabase::GetSongByArtistAndAlbumAndTitle(const CStdString& strArtist, const CStdString& strAlbum, const CStdString& strTitle)
{
try
{
CStdString strSQL=PrepareSQL("select idSong from songview "
"where strArtist like '%s' and strAlbum like '%s' and "
"strTitle like '%s'",strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return -1;
}
int lResult = m_pDS->fv(0).get_asInt();
m_pDS->close(); // cleanup recordset data
return lResult;
}
catch (...)
{
CLog::Log(LOGERROR, "%s (%s,%s,%s) failed", __FUNCTION__, strArtist.c_str(),strAlbum.c_str(),strTitle.c_str());
}
return -1;
}
bool CMusicDatabase::GetSongById(int idSong, CSong& song)
{
try
{
song.Clear();
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL=PrepareSQL("select * from songview "
"where idSong=%i"
, idSong);
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
song = GetSongFromDataset();
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idSong);
}
return false;
}
bool CMusicDatabase::SearchArtists(const CStdString& search, CFileItemList &artists)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
// Exclude "Various Artists"
int idVariousArtist = AddArtist(g_localizeStrings.Get(340));
CStdString strSQL;
if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
strSQL=PrepareSQL("select * from artist "
"where (strArtist like '%s%%' or strArtist like '%% %s%%') and idArtist <> %i "
, search.c_str(), search.c_str(), idVariousArtist );
else
strSQL=PrepareSQL("select * from artist "
"where strArtist like '%s%%' and idArtist <> %i "
, search.c_str(), idVariousArtist );
if (!m_pDS->query(strSQL.c_str())) return false;
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return false;
}
CStdString artistLabel(g_localizeStrings.Get(557)); // Artist
while (!m_pDS->eof())
{
CStdString path;
path.Format("musicdb://2/%ld/", m_pDS->fv(0).get_asInt());
CFileItemPtr pItem(new CFileItem(path, true));
CStdString label;
label.Format("[%s] %s", artistLabel.c_str(), m_pDS->fv(1).get_asString());
pItem->SetLabel(label);
label.Format("A %s", m_pDS->fv(1).get_asString()); // sort label is stored in the title tag
pItem->GetMusicInfoTag()->SetTitle(label);
pItem->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv(0).get_asInt(), "artist");
artists.Add(pItem);
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetArbitraryQuery(const CStdString& strQuery, const CStdString& strOpenRecordSet, const CStdString& strCloseRecordSet,
const CStdString& strOpenRecord, const CStdString& strCloseRecord, const CStdString& strOpenField,
const CStdString& strCloseField, CStdString& strResult)
{
try
{
strResult = "";
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL=strQuery;
if (!m_pDS->query(strSQL.c_str()))
{
strResult = m_pDB->getErrorMsg();
return false;
}
strResult=strOpenRecordSet;
while (!m_pDS->eof())
{
strResult += strOpenRecord;
for (int i=0; i<m_pDS->fieldCount(); i++)
{
strResult += strOpenField;
strResult += m_pDS->fv(i).get_asString();
strResult += strCloseField;
}
strResult += strCloseRecord;
m_pDS->next();
}
strResult += strCloseRecordSet;
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strQuery.c_str());
}
try
{
if (NULL == m_pDB.get()) return false;
strResult = m_pDB->getErrorMsg();
}
catch (...)
{
}
return false;
}
bool CMusicDatabase::ArbitraryExec(const CStdString& strExec)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL = strExec;
m_pDS->exec(strSQL.c_str());
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetAlbumInfo(int idAlbum, CAlbum &info, VECSONGS* songs)
{
try
{
if (idAlbum == -1)
return false; // not in the database
CStdString strSQL=PrepareSQL("select * from albumview where idAlbum = %ld", idAlbum);
if (!m_pDS2->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS2->num_rows();
if (iRowsFound != 0)
{
info = GetAlbumFromDataset(m_pDS2.get(), true); // true to grab the thumburl rather than the thumb
int idAlbumInfo = m_pDS2->fv(album_idAlbumInfo).get_asInt();
m_pDS2->close(); // cleanup recordset data
if (songs)
GetAlbumInfoSongs(idAlbumInfo, *songs);
return true;
}
m_pDS2->close();
return false;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
}
return false;
}
bool CMusicDatabase::HasAlbumInfo(int idAlbum)
{
try
{
if (idAlbum == -1)
return false; // not in the database
CStdString strSQL=PrepareSQL("select * from albuminfo where idAlbum = %ld", idAlbum);
if (!m_pDS2->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS2->num_rows();
m_pDS2->close();
return iRowsFound > 0;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbum);
}
return false;
}
bool CMusicDatabase::DeleteAlbumInfo(int idAlbum)
{
try
{
if (idAlbum == -1)
return false; // not in the database
CStdString strSQL = PrepareSQL("delete from albuminfo where idAlbum=%i",idAlbum);
if (!m_pDS2->exec(strSQL.c_str()))
return false;
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idAlbum);
}
return false;
}
bool CMusicDatabase::GetArtistInfo(int idArtist, CArtist &info, bool needAll)
{
try
{
if (idArtist == -1)
return false; // not in the database
CStdString strSQL=PrepareSQL("SELECT * FROM artistview WHERE idArtist = %i", idArtist);
if (!m_pDS2->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS2->num_rows();
if (iRowsFound != 0)
{
info = GetArtistFromDataset(m_pDS2.get(),needAll);
if (needAll)
{
strSQL=PrepareSQL("select * from discography where idArtist=%i",idArtist);
m_pDS2->query(strSQL.c_str());
while (!m_pDS2->eof())
{
info.discography.push_back(make_pair(m_pDS2->fv("strAlbum").get_asString(),m_pDS2->fv("strYear").get_asString()));
m_pDS2->next();
}
}
m_pDS2->close(); // cleanup recordset data
return true;
}
m_pDS2->close();
return false;
}
catch (...)
{
CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idArtist);
}
return false;
}
bool CMusicDatabase::HasArtistInfo(int idArtist)
{
return strtol(GetSingleValue("artistinfo", "count(idArtist)", PrepareSQL("idArtist = %ld", idArtist)), NULL, 10) > 0;
}
bool CMusicDatabase::DeleteArtistInfo(int idArtist)
{
try
{
if (idArtist == -1)
return false; // not in the database
CStdString strSQL = PrepareSQL("delete from artistinfo where idArtist=%i",idArtist);
if (!m_pDS2->exec(strSQL.c_str()))
return false;
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s - (%i) failed", __FUNCTION__, idArtist);
}
return false;
}
bool CMusicDatabase::GetAlbumInfoSongs(int idAlbumInfo, VECSONGS& songs)
{
try
{
CStdString strSQL=PrepareSQL("select * from albuminfosong "
"where idAlbumInfo=%i "
"order by iTrack", idAlbumInfo);
if (!m_pDS2->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS2->num_rows();
if (iRowsFound == 0) return false;
while (!m_pDS2->eof())
{
CSong song;
song.iTrack = m_pDS2->fv("iTrack").get_asInt();
song.strTitle = m_pDS2->fv("strTitle").get_asString();
song.iDuration = m_pDS2->fv("iDuration").get_asInt();
songs.push_back(song);
m_pDS2->next();
}
m_pDS2->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%i) failed", __FUNCTION__, idAlbumInfo);
}
return false;
}
bool CMusicDatabase::GetTop100(const CStdString& strBaseDir, CFileItemList& items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL="select * from songview "
"where iTimesPlayed>0 "
"order by iTimesPlayed desc "
"limit 100";
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
items.Reserve(iRowsFound);
while (!m_pDS->eof())
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(item.get(), strBaseDir);
items.Add(item);
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetTop100Albums(VECALBUMS& albums)
{
try
{
albums.erase(albums.begin(), albums.end());
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
// NOTE: The song.idAlbum is needed for the group by, as for some reason group by albumview.idAlbum doesn't work
// consistently - possibly an SQLite bug, as it works fine in SQLiteSpy (v3.3.17)
CStdString strSQL = "select albumview.*, sum(song.iTimesPlayed) as total, song.idAlbum from song "
"join albumview on albumview.idAlbum=song.idAlbum "
"where song.iTimesPlayed>0 and albumview.strAlbum != '' "
"group by song.idAlbum "
"order by total desc "
"limit 100 ";
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
albums.push_back(GetAlbumFromDataset(m_pDS.get()));
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetTop100AlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
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)");
CLog::Log(LOGDEBUG,"GetTop100AlbumSongs() query: %s", strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// get data from returned rows
items.Reserve(iRowsFound);
while (!m_pDS->eof())
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(item.get(), strBaseDir);
items.Add(item);
m_pDS->next();
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetRecentlyPlayedAlbums(VECALBUMS& albums)
{
try
{
albums.erase(albums.begin(), albums.end());
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
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);
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
albums.push_back(GetAlbumFromDataset(m_pDS.get()));
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetRecentlyPlayedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
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);
CLog::Log(LOGDEBUG,"GetRecentlyPlayedAlbumSongs() query: %s", strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// get data from returned rows
items.Reserve(iRowsFound);
while (!m_pDS->eof())
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(item.get(), strBaseDir);
items.Add(item);
m_pDS->next();
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetRecentlyAddedAlbums(VECALBUMS& albums, unsigned int limit)
{
try
{
albums.erase(albums.begin(), albums.end());
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
strSQL.Format("select * from albumview order by idAlbum desc limit %u", limit ? limit : g_advancedSettings.m_iMusicLibraryRecentlyAddedItems);
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
albums.push_back(GetAlbumFromDataset(m_pDS.get()));
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetRecentlyAddedAlbumSongs(const CStdString& strBaseDir, CFileItemList& items, unsigned int limit)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
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);
CLog::Log(LOGDEBUG,"GetRecentlyAddedAlbumSongs() query: %s", strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// get data from returned rows
items.Reserve(iRowsFound);
while (!m_pDS->eof())
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(item.get(), strBaseDir);
items.Add(item);
m_pDS->next();
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
void CMusicDatabase::IncrementPlayCount(const CFileItem& item)
{
try
{
if (NULL == m_pDB.get()) return;
if (NULL == m_pDS.get()) return;
int idSong = GetSongIDFromPath(item.GetPath());
CStdString sql=PrepareSQL("UPDATE song SET iTimesPlayed=iTimesPlayed+1, lastplayed=CURRENT_TIMESTAMP where idSong=%i", idSong);
m_pDS->exec(sql.c_str());
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, item.GetPath().c_str());
}
}
bool CMusicDatabase::GetSongsByPath(const CStdString& strPath1, MAPSONGS& songs, bool bAppendToMap)
{
CStdString strPath(strPath1);
try
{
if (!URIUtils::HasSlashAtEnd(strPath))
URIUtils::AddSlashAtEnd(strPath);
if (!bAppendToMap)
songs.clear();
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL=PrepareSQL("select * from songview where strPath='%s'", strPath.c_str() );
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
while (!m_pDS->eof())
{
CSong song = GetSongFromDataset();
songs.insert(make_pair(song.strFileName, song));
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, strPath.c_str());
}
return false;
}
void CMusicDatabase::EmptyCache()
{
m_artistCache.erase(m_artistCache.begin(), m_artistCache.end());
m_genreCache.erase(m_genreCache.begin(), m_genreCache.end());
m_pathCache.erase(m_pathCache.begin(), m_pathCache.end());
m_albumCache.erase(m_albumCache.begin(), m_albumCache.end());
m_thumbCache.erase(m_thumbCache.begin(), m_thumbCache.end());
}
bool CMusicDatabase::Search(const CStdString& search, CFileItemList &items)
{
unsigned int time = XbmcThreads::SystemClockMillis();
// first grab all the artists that match
SearchArtists(search, items);
CLog::Log(LOGDEBUG, "%s Artist search in %i ms",
__FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
// then albums that match
SearchAlbums(search, items);
CLog::Log(LOGDEBUG, "%s Album search in %i ms",
__FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
// and finally songs
SearchSongs(search, items);
CLog::Log(LOGDEBUG, "%s Songs search in %i ms",
__FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
return true;
}
bool CMusicDatabase::SearchSongs(const CStdString& search, CFileItemList &items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' or strTitle like '%% %s%%' limit 1000", search.c_str(), search.c_str());
else
strSQL=PrepareSQL("select * from songview where strTitle like '%s%%' limit 1000", search.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
if (m_pDS->num_rows() == 0) return false;
CStdString songLabel = g_localizeStrings.Get(179); // Song
while (!m_pDS->eof())
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(item.get(), "musicdb://4/");
items.Add(item);
m_pDS->next();
}
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::SearchAlbums(const CStdString& search, CFileItemList &albums)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL;
if (search.GetLength() >= MIN_FULL_SEARCH_LENGTH)
strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%' or strAlbum like '%% %s%%'", search.c_str(), search.c_str());
else
strSQL=PrepareSQL("select * from albumview where strAlbum like '%s%%'", search.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
CStdString albumLabel(g_localizeStrings.Get(558)); // Album
while (!m_pDS->eof())
{
CAlbum album = GetAlbumFromDataset(m_pDS.get());
CStdString path;
path.Format("musicdb://3/%ld/", album.idAlbum);
CFileItemPtr pItem(new CFileItem(path, album));
CStdString label;
label.Format("[%s] %s", albumLabel.c_str(), album.strAlbum);
pItem->SetLabel(label);
label.Format("B %s", album.strAlbum); // sort label is stored in the title tag
pItem->GetMusicInfoTag()->SetTitle(label);
albums.Add(pItem);
m_pDS->next();
}
m_pDS->close(); // cleanup recordset data
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
int CMusicDatabase::SetAlbumInfo(int idAlbum, const CAlbum& album, const VECSONGS& songs, bool bTransaction)
{
CStdString strSQL;
try
{
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
if (bTransaction)
BeginTransaction();
// delete any album info we may have
strSQL=PrepareSQL("delete from albuminfo where idAlbum=%i", idAlbum);
m_pDS->exec(strSQL.c_str());
// insert the albuminfo
strSQL=PrepareSQL("insert into albuminfo (idAlbumInfo,idAlbum,strMoods,strStyles,strThemes,strReview,strImage,strLabel,strType,iRating,iYear) values(NULL,%i,'%s','%s','%s','%s','%s','%s','%s',%i,%i)",
idAlbum,
StringUtils::Join(album.moods, g_advancedSettings.m_musicItemSeparator).c_str(),
StringUtils::Join(album.styles, g_advancedSettings.m_musicItemSeparator).c_str(),
StringUtils::Join(album.themes, g_advancedSettings.m_musicItemSeparator).c_str(),
album.strReview.c_str(),
album.thumbURL.m_xml.c_str(),
album.strLabel.c_str(),
album.strType.c_str(),
album.iRating,
album.iYear);
m_pDS->exec(strSQL.c_str());
int idAlbumInfo = (int)m_pDS->lastinsertid();
if (SetAlbumInfoSongs(idAlbumInfo, songs))
{
if (bTransaction)
CommitTransaction();
}
else
{
if (bTransaction) // icky
RollbackTransaction();
idAlbumInfo = -1;
}
return idAlbumInfo;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
}
if (bTransaction)
RollbackTransaction();
return -1;
}
int CMusicDatabase::SetArtistInfo(int idArtist, const CArtist& artist)
{
CStdString strSQL;
try
{
if (NULL == m_pDB.get()) return -1;
if (NULL == m_pDS.get()) return -1;
// delete any artist info we may have
strSQL=PrepareSQL("delete from artistinfo where idArtist=%i", idArtist);
m_pDS->exec(strSQL.c_str());
strSQL=PrepareSQL("delete from discography where idArtist=%i", idArtist);
m_pDS->exec(strSQL.c_str());
// insert the artistinfo
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')",
idArtist, artist.strBorn.c_str(),
artist.strFormed.c_str(),
StringUtils::Join(artist.genre, g_advancedSettings.m_musicItemSeparator).c_str(),
StringUtils::Join(artist.moods, g_advancedSettings.m_musicItemSeparator).c_str(),
StringUtils::Join(artist.styles, g_advancedSettings.m_musicItemSeparator).c_str(),
StringUtils::Join(artist.instruments, g_advancedSettings.m_musicItemSeparator).c_str(),
artist.strBiography.c_str(),
artist.strDied.c_str(),
artist.strDisbanded.c_str(),
StringUtils::Join(artist.yearsActive, g_advancedSettings.m_musicItemSeparator).c_str(),
artist.thumbURL.m_xml.c_str(),
artist.fanart.m_xml.c_str());
m_pDS->exec(strSQL.c_str());
int idArtistInfo = (int)m_pDS->lastinsertid();
for (unsigned int i=0;i<artist.discography.size();++i)
{
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());
m_pDS->exec(strSQL.c_str());
}
return idArtistInfo;
}
catch (...)
{
CLog::Log(LOGERROR, "%s - failed with query (%s)", __FUNCTION__, strSQL.c_str());
}
return -1;
}
bool CMusicDatabase::SetAlbumInfoSongs(int idAlbumInfo, const VECSONGS& songs)
{
CStdString strSQL;
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
strSQL=PrepareSQL("delete from albuminfosong where idAlbumInfo=%i", idAlbumInfo);
m_pDS->exec(strSQL.c_str());
for (int i = 0; i < (int)songs.size(); i++)
{
CSong song = songs[i];
strSQL=PrepareSQL("insert into albuminfosong (idAlbumInfoSong,idAlbumInfo,iTrack,strTitle,iDuration) values(NULL,%i,%i,'%s',%i)",
idAlbumInfo,
song.iTrack,
song.strTitle.c_str(),
song.iDuration);
m_pDS->exec(strSQL.c_str());
}
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed with query (%s)", __FUNCTION__, strSQL.c_str());
}
return false;
}
bool CMusicDatabase::CleanupSongsByIds(const CStdString &strSongIds)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
// ok, now find all idSong's
CStdString strSQL=PrepareSQL("select * from song join path on song.idPath = path.idPath where song.idSong in %s", strSongIds.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return true;
}
CStdString strSongsToDelete = "";
while (!m_pDS->eof())
{ // get the full song path
CStdString strFileName;
URIUtils::AddFileToFolder(m_pDS->fv("path.strPath").get_asString(), m_pDS->fv("song.strFileName").get_asString(), strFileName);
// Special case for streams inside an ogg file. (oggstream)
// The last dir in the path is the ogg file that
// contains the stream, so test if its there
CStdString strExtension=URIUtils::GetExtension(strFileName);
if (strExtension==".oggstream" || strExtension==".nsfstream")
{
CStdString strFileAndPath=strFileName;
URIUtils::GetDirectory(strFileAndPath, strFileName);
// we are dropping back to a file, so remove the slash at end
URIUtils::RemoveSlashAtEnd(strFileName);
}
if (!CFile::Exists(strFileName))
{ // file no longer exists, so add to deletion list
strSongsToDelete += m_pDS->fv("song.idSong").get_asString() + ",";
}
m_pDS->next();
}
m_pDS->close();
if ( ! strSongsToDelete.IsEmpty() )
{
strSongsToDelete = "(" + strSongsToDelete.TrimRight(",") + ")";
// ok, now delete these songs + all references to them from the linked tables
strSQL = "delete from song where idSong in " + strSongsToDelete;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from song_artist where idSong in " + strSongsToDelete;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from song_genre where idSong in " + strSongsToDelete;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from karaokedata where idSong in " + strSongsToDelete;
m_pDS->exec(strSQL.c_str());
m_pDS->close();
}
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongsFromPaths()");
}
return false;
}
bool CMusicDatabase::CleanupSongs()
{
try
{
// run through all songs and get all unique path ids
int iLIMIT = 1000;
for (int i=0;;i+=iLIMIT)
{
CStdString strSQL=PrepareSQL("select song.idSong from song order by song.idSong limit %i offset %i",iLIMIT,i);
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
// keep going until no rows are left!
if (iRowsFound == 0)
{
m_pDS->close();
return true;
}
CStdString strSongIds = "(";
while (!m_pDS->eof())
{
strSongIds += m_pDS->fv("song.idSong").get_asString() + ",";
m_pDS->next();
}
m_pDS->close();
strSongIds.TrimRight(",");
strSongIds += ")";
CLog::Log(LOGDEBUG,"Checking songs from song ID list: %s",strSongIds.c_str());
if (!CleanupSongsByIds(strSongIds)) return false;
}
return true;
}
catch(...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupSongs()");
}
return false;
}
bool CMusicDatabase::CleanupAlbums()
{
try
{
// This must be run AFTER songs have been cleaned up
// delete albums with no reference to songs
CStdString strSQL = "select * from album where album.idAlbum not in (select idAlbum from song)";
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return true;
}
CStdString strAlbumIds = "(";
while (!m_pDS->eof())
{
strAlbumIds += m_pDS->fv("album.idAlbum").get_asString() + ",";
m_pDS->next();
}
m_pDS->close();
strAlbumIds.TrimRight(",");
strAlbumIds += ")";
// ok, now we can delete them and the references in the linked tables
strSQL = "delete from album where idAlbum in " + strAlbumIds;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from album_artist where idAlbum in " + strAlbumIds;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from album_genre where idAlbum in " + strAlbumIds;
m_pDS->exec(strSQL.c_str());
strSQL = "delete from albuminfo where idAlbum in " + strAlbumIds;
m_pDS->exec(strSQL.c_str());
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupAlbums()");
}
return false;
}
bool CMusicDatabase::CleanupPaths()
{
try
{
// needs to be done AFTER the songs and albums have been cleaned up.
// we can happily delete any path that has no reference to a song
// but we must keep all paths that have been scanned that may contain songs in subpaths
// first create a temporary table of song paths
m_pDS->exec("CREATE TEMPORARY TABLE songpaths (idPath integer, strPath varchar(512))\n");
m_pDS->exec("INSERT INTO songpaths select idPath,strPath from path where idPath in (select idPath from song)\n");
// grab all paths that aren't immediately connected with a song
CStdString sql = "select * from path where idPath not in (select idPath from song)";
if (!m_pDS->query(sql.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return true;
}
// and construct a list to delete
CStdString deleteSQL;
while (!m_pDS->eof())
{
// anything that isn't a parent path of a song path is to be deleted
CStdString path = m_pDS->fv("strPath").get_asString();
CStdString sql = PrepareSQL("select count(idPath) from songpaths where SUBSTR(strPath,1,%i)='%s'", StringUtils::utf8_strlen(path.c_str()), path.c_str());
if (m_pDS2->query(sql.c_str()) && m_pDS2->num_rows() == 1 && m_pDS2->fv(0).get_asInt() == 0)
deleteSQL += PrepareSQL("%i,", m_pDS->fv("idPath").get_asInt()); // nothing found, so delete
m_pDS2->close();
m_pDS->next();
}
m_pDS->close();
if ( ! deleteSQL.IsEmpty() )
{
deleteSQL = "DELETE FROM path WHERE idPath IN (" + deleteSQL.TrimRight(',') + ")";
// do the deletion, and drop our temp table
m_pDS->exec(deleteSQL.c_str());
}
m_pDS->exec("drop table songpaths");
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupPaths() or was aborted");
}
return false;
}
bool CMusicDatabase::CleanupArtists()
{
try
{
// (nested queries by Bobbin007)
// must be executed AFTER the song, album and their artist link tables are cleaned.
// don't delete the "Various Artists" string
CStdString strVariousArtists = g_localizeStrings.Get(340);
int idVariousArtists = AddArtist(strVariousArtists);
CStdString strSQL = "delete from artist where idArtist not in (select idArtist from song_artist)";
strSQL += " and idArtist not in (select idArtist from album_artist)";
CStdString strSQL2;
strSQL2.Format(" and idArtist<>%i", idVariousArtists);
strSQL += strSQL2;
m_pDS->exec(strSQL.c_str());
m_pDS->exec("delete from artistinfo where idArtist not in (select idArtist from artist)");
m_pDS->exec("delete from album_artist where idArtist not in (select idArtist from artist)");
m_pDS->exec("delete from song_artist where idArtist not in (select idArtist from artist)");
m_pDS->exec("delete from discography where idArtist not in (select idArtist from artist)");
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupArtists() or was aborted");
}
return false;
}
bool CMusicDatabase::CleanupGenres()
{
try
{
// Cleanup orphaned genres (ie those that don't belong to a song or an albuminfo entry)
// (nested queries by Bobbin007)
// Must be executed AFTER the song, song_genre, albuminfo and album_genre tables have been cleaned.
CStdString strSQL = "delete from genre where idGenre not in (select idGenre from song_genre) and";
strSQL += " idGenre not in (select idGenre from album_genre)";
m_pDS->exec(strSQL.c_str());
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "Exception in CMusicDatabase::CleanupGenres() or was aborted");
}
return false;
}
bool CMusicDatabase::CleanupOrphanedItems()
{
// paths aren't cleaned up here - they're cleaned up in RemoveSongsFromPath()
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
if (!CleanupAlbums()) return false;
if (!CleanupArtists()) return false;
if (!CleanupGenres()) return false;
return true;
}
int CMusicDatabase::Cleanup(CGUIDialogProgress *pDlgProgress)
{
if (NULL == m_pDB.get()) return ERROR_DATABASE;
if (NULL == m_pDS.get()) return ERROR_DATABASE;
unsigned int time = XbmcThreads::SystemClockMillis();
CLog::Log(LOGNOTICE, "%s: Starting musicdatabase cleanup ..", __FUNCTION__);
// first cleanup any songs with invalid paths
if (pDlgProgress)
{
pDlgProgress->SetHeading(700);
pDlgProgress->SetLine(0, "");
pDlgProgress->SetLine(1, 318);
pDlgProgress->SetLine(2, 330);
pDlgProgress->SetPercentage(0);
pDlgProgress->StartModal();
pDlgProgress->ShowProgressBar(true);
}
if (!CleanupSongs())
{
RollbackTransaction();
return ERROR_REORG_SONGS;
}
// then the albums that are not linked to a song or to albuminfo, or whose path is removed
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 326);
pDlgProgress->SetPercentage(20);
pDlgProgress->Progress();
}
if (!CleanupAlbums())
{
RollbackTransaction();
return ERROR_REORG_ALBUM;
}
// now the paths
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 324);
pDlgProgress->SetPercentage(40);
pDlgProgress->Progress();
}
if (!CleanupPaths())
{
RollbackTransaction();
return ERROR_REORG_PATH;
}
// and finally artists + genres
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 320);
pDlgProgress->SetPercentage(60);
pDlgProgress->Progress();
}
if (!CleanupArtists())
{
RollbackTransaction();
return ERROR_REORG_ARTIST;
}
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 322);
pDlgProgress->SetPercentage(80);
pDlgProgress->Progress();
}
if (!CleanupGenres())
{
RollbackTransaction();
return ERROR_REORG_GENRE;
}
// commit transaction
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 328);
pDlgProgress->SetPercentage(90);
pDlgProgress->Progress();
}
if (!CommitTransaction())
{
RollbackTransaction();
return ERROR_WRITING_CHANGES;
}
// and compress the database
if (pDlgProgress)
{
pDlgProgress->SetLine(1, 331);
pDlgProgress->SetPercentage(100);
pDlgProgress->Progress();
}
time = XbmcThreads::SystemClockMillis() - time;
CLog::Log(LOGNOTICE, "%s: Cleaning musicdatabase done. Operation took %s", __FUNCTION__, StringUtils::SecondsToTimeString(time / 1000).c_str());
if (!Compress(false))
{
return ERROR_COMPRESSING;
}
return ERROR_OK;
}
void CMusicDatabase::DeleteAlbumInfo()
{
// open our database
Open();
if (NULL == m_pDB.get()) return ;
if (NULL == m_pDS.get()) return ;
// If we are scanning for music info in the background,
// other writing access to the database is prohibited.
if (g_application.IsMusicScanning())
{
CGUIDialogOK::ShowAndGetInput(189, 14057, 0, 0);
return;
}
CStdString strSQL="select * from albuminfo,album,artist where and albuminfo.idAlbum=album.idAlbum and album.idArtist=artist.idArtist order by album.strAlbum";
if (!m_pDS->query(strSQL.c_str())) return ;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
CGUIDialogOK::ShowAndGetInput(313, 425, 0, 0);
}
vector<CAlbum> vecAlbums;
while (!m_pDS->eof())
{
CAlbum album;
album.idAlbum = m_pDS->fv("album.idAlbum").get_asInt() ;
album.strAlbum = m_pDS->fv("album.strAlbum").get_asString();
album.artist = StringUtils::Split(m_pDS->fv("album.strArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
vecAlbums.push_back(album);
m_pDS->next();
}
m_pDS->close();
// Show a selectdialog that the user can select the albuminfo to delete
CGUIDialogSelect *pDlg = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
if (pDlg)
{
pDlg->SetHeading(g_localizeStrings.Get(181).c_str());
pDlg->Reset();
for (int i = 0; i < (int)vecAlbums.size(); ++i)
{
CAlbum& album = vecAlbums[i];
pDlg->Add(album.strAlbum + " - " + StringUtils::Join(album.artist, g_advancedSettings.m_musicItemSeparator));
}
pDlg->DoModal();
// and wait till user selects one
int iSelectedAlbum = pDlg->GetSelectedLabel();
if (iSelectedAlbum < 0)
{
vecAlbums.erase(vecAlbums.begin(), vecAlbums.end());
return ;
}
CAlbum& album = vecAlbums[iSelectedAlbum];
strSQL=PrepareSQL("delete from albuminfo where albuminfo.idAlbum=%i", album.idAlbum);
if (!m_pDS->exec(strSQL.c_str())) return ;
vecAlbums.erase(vecAlbums.begin(), vecAlbums.end());
}
}
bool CMusicDatabase::LookupCDDBInfo(bool bRequery/*=false*/)
{
#ifdef HAS_DVD_DRIVE
if (!g_guiSettings.GetBool("audiocds.usecddb"))
return false;
// check network connectivity
if (!g_application.getNetwork().IsAvailable())
return false;
// Get information for the inserted disc
CCdInfo* pCdInfo = g_mediaManager.GetCdInfo();
if (pCdInfo == NULL)
return false;
// If the disc has no tracks, we are finished here.
int nTracks = pCdInfo->GetTrackCount();
if (nTracks <= 0)
return false;
// Delete old info if any
if (bRequery)
{
CStdString strFile;
strFile.Format("%x.cddb", pCdInfo->GetCddbDiscId());
CFile::Delete(URIUtils::AddFileToFolder(g_settings.GetCDDBFolder(), strFile));
}
// Prepare cddb
Xcddb cddb;
cddb.setCacheDir(g_settings.GetCDDBFolder());
// Do we have to look for cddb information
if (pCdInfo->HasCDDBInfo() && !cddb.isCDCached(pCdInfo))
{
CGUIDialogProgress* pDialogProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
CGUIDialogSelect *pDlgSelect = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
if (!pDialogProgress) return false;
if (!pDlgSelect) return false;
// Show progress dialog if we have to connect to freedb.org
pDialogProgress->SetHeading(255); //CDDB
pDialogProgress->SetLine(0, ""); // Querying freedb for CDDB info
pDialogProgress->SetLine(1, 256);
pDialogProgress->SetLine(2, "");
pDialogProgress->ShowProgressBar(false);
pDialogProgress->StartModal();
// get cddb information
if (!cddb.queryCDinfo(pCdInfo))
{
pDialogProgress->Close();
int lasterror = cddb.getLastError();
// Have we found more then on match in cddb for this disc,...
if (lasterror == E_WAIT_FOR_INPUT)
{
// ...yes, show the matches found in a select dialog
// and let the user choose an entry.
pDlgSelect->Reset();
pDlgSelect->SetHeading(255);
int i = 1;
while (1)
{
CStdString strTitle = cddb.getInexactTitle(i);
if (strTitle == "") break;
CStdString strArtist = cddb.getInexactArtist(i);
if (!strArtist.IsEmpty())
strTitle += " - " + strArtist;
pDlgSelect->Add(strTitle);
i++;
}
pDlgSelect->DoModal();
// Has the user selected a match...
int iSelectedCD = pDlgSelect->GetSelectedLabel();
if (iSelectedCD >= 0)
{
// ...query cddb for the inexact match
if (!cddb.queryCDinfo(pCdInfo, 1 + iSelectedCD))
pCdInfo->SetNoCDDBInfo();
}
else
pCdInfo->SetNoCDDBInfo();
}
else if (lasterror == E_NO_MATCH_FOUND)
{
pCdInfo->SetNoCDDBInfo();
}
else
{
pCdInfo->SetNoCDDBInfo();
// ..no, an error occured, display it to the user
CStdString strErrorText;
strErrorText.Format("[%d] %s", cddb.getLastError(), cddb.getLastErrorText());
CGUIDialogOK::ShowAndGetInput(255, 257, strErrorText, 0);
}
} // if ( !cddb.queryCDinfo( pCdInfo ) )
else
pDialogProgress->Close();
} // if (pCdInfo->HasCDDBInfo() && g_settings.m_bUseCDDB)
// Filling the file items with cddb info happens in CMusicInfoTagLoaderCDDA
return pCdInfo->HasCDDBInfo();
#else
return false;
#endif
}
void CMusicDatabase::DeleteCDDBInfo()
{
#ifdef HAS_DVD_DRIVE
CFileItemList items;
if (!CDirectory::GetDirectory(g_settings.GetCDDBFolder(), items, ".cddb", DIR_FLAG_NO_FILE_DIRS))
{
CGUIDialogOK::ShowAndGetInput(313, 426, 0, 0);
return ;
}
// Show a selectdialog that the user can select the albuminfo to delete
CGUIDialogSelect *pDlg = (CGUIDialogSelect*)g_windowManager.GetWindow(WINDOW_DIALOG_SELECT);
if (pDlg)
{
pDlg->SetHeading(g_localizeStrings.Get(181).c_str());
pDlg->Reset();
map<ULONG, CStdString> mapCDDBIds;
for (int i = 0; i < items.Size(); ++i)
{
if (items[i]->m_bIsFolder)
continue;
CStdString strFile = URIUtils::GetFileName(items[i]->GetPath());
strFile.Delete(strFile.size() - 5, 5);
ULONG lDiscId = strtoul(strFile.c_str(), NULL, 16);
Xcddb cddb;
cddb.setCacheDir(g_settings.GetCDDBFolder());
if (!cddb.queryCache(lDiscId))
continue;
CStdString strDiskTitle, strDiskArtist;
cddb.getDiskTitle(strDiskTitle);
cddb.getDiskArtist(strDiskArtist);
CStdString str;
if (strDiskArtist.IsEmpty())
str = strDiskTitle;
else
str = strDiskTitle + " - " + strDiskArtist;
pDlg->Add(str);
mapCDDBIds.insert(pair<ULONG, CStdString>(lDiscId, str));
}
pDlg->Sort();
pDlg->DoModal();
// and wait till user selects one
int iSelectedAlbum = pDlg->GetSelectedLabel();
if (iSelectedAlbum < 0)
{
mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
return ;
}
CStdString strSelectedAlbum = pDlg->GetSelectedLabelText();
map<ULONG, CStdString>::iterator it;
for (it = mapCDDBIds.begin();it != mapCDDBIds.end();it++)
{
if (it->second == strSelectedAlbum)
{
CStdString strFile;
strFile.Format("%x.cddb", it->first);
CFile::Delete(URIUtils::AddFileToFolder(g_settings.GetCDDBFolder(), strFile));
break;
}
}
mapCDDBIds.erase(mapCDDBIds.begin(), mapCDDBIds.end());
}
#endif
}
void CMusicDatabase::Clean()
{
// If we are scanning for music info in the background,
// other writing access to the database is prohibited.
if (g_application.IsMusicScanning())
{
CGUIDialogOK::ShowAndGetInput(189, 14057, 0, 0);
return;
}
if (CGUIDialogYesNo::ShowAndGetInput(313, 333, 0, 0))
{
CGUIDialogProgress* dlgProgress = (CGUIDialogProgress*)g_windowManager.GetWindow(WINDOW_DIALOG_PROGRESS);
if (dlgProgress)
{
CMusicDatabase musicdatabase;
if (musicdatabase.Open())
{
int iReturnString = musicdatabase.Cleanup(dlgProgress);
musicdatabase.Close();
if (iReturnString != ERROR_OK)
{
CGUIDialogOK::ShowAndGetInput(313, iReturnString, 0, 0);
}
}
dlgProgress->Close();
}
}
}
bool CMusicDatabase::GetGenresNav(const CStdString& strBaseDir, CFileItemList& items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
// get primary genres for songs - could be simplified to just SELECT * FROM genre?
CStdString strSQL="SELECT * "
" FROM genre "
" WHERE idGenre IN"
"(SELECT song_genre.idGenre "
" FROM song_genre) ";
// block null strings
strSQL += " AND genre.strGenre != \"\"";
// run query
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str()))
return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// get data from returned rows
while (!m_pDS->eof())
{
CFileItemPtr pItem(new CFileItem(m_pDS->fv("strGenre").get_asString()));
pItem->GetMusicInfoTag()->SetGenre(m_pDS->fv("strGenre").get_asString());
pItem->GetMusicInfoTag()->SetDatabaseId(m_pDS->fv("idGenre").get_asInt(), "genre");
CMusicDbUrl itemUrl = musicUrl;
CStdString strDir; strDir.Format("%ld/", m_pDS->fv("idGenre").get_asInt());
itemUrl.AppendPath(strDir);
pItem->SetPath(itemUrl.ToString());
pItem->m_bIsFolder = true;
items.Add(pItem);
m_pDS->next();
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetYearsNav(const CStdString& strBaseDir, CFileItemList& items)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
// get years from album list
CStdString strSQL="select distinct iYear from album where iYear <> 0";
// run query
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str()))
return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// get data from returned rows
while (!m_pDS->eof())
{
CFileItemPtr pItem(new CFileItem(m_pDS->fv("iYear").get_asString()));
SYSTEMTIME stTime;
stTime.wYear = (WORD)m_pDS->fv("iYear").get_asInt();
pItem->GetMusicInfoTag()->SetReleaseDate(stTime);
CMusicDbUrl itemUrl = musicUrl;
CStdString strDir; strDir.Format("%ld/", m_pDS->fv("iYear").get_asInt());
itemUrl.AppendPath(strDir);
pItem->SetPath(itemUrl.ToString());
pItem->m_bIsFolder = true;
items.Add(pItem);
m_pDS->next();
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetAlbumsByYear(const CStdString& strBaseDir, CFileItemList& items, int year)
{
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
musicUrl.AddOption("year", year);
Filter filter;
return GetAlbumsByWhere(musicUrl.ToString(), filter, items);
}
bool CMusicDatabase::GetArtistsNav(const CStdString& strBaseDir, CFileItemList& items, bool albumArtistsOnly /* = false */, int idGenre /* = -1 */, int idAlbum /* = -1 */, int idSong /* = -1 */, const SortDescription &sortDescription /* = SortDescription() */)
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
try
{
unsigned int time = XbmcThreads::SystemClockMillis();
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
if (idGenre > 0)
musicUrl.AddOption("genreid", idGenre);
else if (idAlbum > 0)
musicUrl.AddOption("albumid", idAlbum);
else if (idSong > 0)
musicUrl.AddOption("songid", idSong);
musicUrl.AddOption("albumartistsonly", albumArtistsOnly);
Filter filter;
bool result = GetArtistsByWhere(musicUrl.ToString(), filter, items, sortDescription);
CLog::Log(LOGDEBUG,"Time to retrieve artists from dataset = %i", XbmcThreads::SystemClockMillis() - time);
return result;
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetArtistsByWhere(const CStdString& strBaseDir, const Filter &filter, CFileItemList& items, const SortDescription &sortDescription /* = SortDescription() */)
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
try
{
int total = -1;
CStdString strSQL = "SELECT %s FROM artistview ";
Filter extFilter = filter;
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir) || !GetFilter(musicUrl, extFilter))
return false;
// if there are extra WHERE conditions we might need access
// to songview or albumview for these conditions
if (extFilter.where.size() > 0)
{
bool extended = false;
if (extFilter.where.find("songview") != string::npos)
{
extended = true;
extFilter.AppendJoin("JOIN song_artist ON song_artist.idArtist = artistview.idArtist JOIN songview ON songview.idSong = song_artist.idSong");
}
else if (extFilter.where.find("albumview") != string::npos)
{
extended = true;
extFilter.AppendJoin("JOIN album_artist ON album_artist.idArtist = artistview.idArtist JOIN albumview ON albumview.idAlbum = album_artist.idAlbum");
}
if (extended)
extFilter.AppendGroup("artistview.idArtist");
}
CStdString strSQLExtra;
if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
return false;
// Apply the limiting directly here if there's no special sorting but limiting
if (extFilter.limit.empty() &&
sortDescription.sortBy == SortByNone &&
(sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
{
total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
}
strSQL = PrepareSQL(strSQL.c_str(), !extFilter.fields.empty() && extFilter.fields.compare("*") != 0 ? extFilter.fields.c_str() : "artistview.*") + strSQLExtra;
// run query
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// store the total value of items as a property
if (total < iRowsFound)
total = iRowsFound;
items.SetProperty("total", total);
DatabaseResults results;
results.reserve(iRowsFound);
if (!SortUtils::SortFromDataset(sortDescription, MediaTypeArtist, m_pDS, results))
return false;
// get data from returned rows
items.Reserve(results.size());
const dbiplus::query_data &data = m_pDS->get_result_set().records;
for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
{
unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
const dbiplus::sql_record* const record = data.at(targetRow);
try
{
CArtist artist = GetArtistFromDataset(record, false);
CFileItemPtr pItem(new CFileItem(artist));
CMusicDbUrl itemUrl = musicUrl;
CStdString path; path.Format("%ld/", artist.idArtist);
itemUrl.AppendPath(path);
pItem->SetPath(itemUrl.ToString());
pItem->GetMusicInfoTag()->SetDatabaseId(artist.idArtist, "artist");
pItem->SetIconImage("DefaultArtist.png");
SetPropertiesFromArtist(*pItem, artist);
items.Add(pItem);
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s - out of memory getting listing (got %i)", __FUNCTION__, items.Size());
}
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetAlbumFromSong(int idSong, CAlbum &album)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
CStdString strSQL = PrepareSQL("select albumview.* from song join albumview on song.idAlbum = albumview.idAlbum where song.idSong='%i'", idSong);
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound != 1)
{
m_pDS->close();
return false;
}
album = GetAlbumFromDataset(m_pDS.get());
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
// This function won't be required if/when the fileitem tag has idSong information
bool CMusicDatabase::GetAlbumFromSong(const CSong &song, CAlbum &album)
{
try
{
if (NULL == m_pDB.get()) return false;
if (NULL == m_pDS.get()) return false;
if (song.idSong != -1) return GetAlbumFromSong(song.idSong, album);
CStdString path, file;
URIUtils::Split(song.strFileName, path, file);
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());
if (!m_pDS->query(strSQL.c_str())) return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound != 1)
{
m_pDS->close();
return false;
}
album = GetAlbumFromDataset(m_pDS.get());
m_pDS->close();
return true;
}
catch (...)
{
CLog::Log(LOGERROR, "%s failed", __FUNCTION__);
}
return false;
}
bool CMusicDatabase::GetAlbumsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre /* = -1 */, int idArtist /* = -1 */, const SortDescription &sortDescription /* = SortDescription() */)
{
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
// where clause
if (idGenre > 0)
musicUrl.AddOption("genreid", idGenre);
if (idArtist > 0)
musicUrl.AddOption("artistid", idArtist);
Filter filter;
return GetAlbumsByWhere(musicUrl.ToString(), filter, items, sortDescription);
}
bool CMusicDatabase::GetAlbumsByWhere(const CStdString &baseDir, const Filter &filter, CFileItemList &items, const SortDescription &sortDescription /* = SortDescription() */)
{
if (m_pDB.get() == NULL || m_pDS.get() == NULL)
return false;
try
{
int total = -1;
CStdString strSQL = "SELECT %s FROM albumview ";
Filter extFilter = filter;
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(baseDir) || !GetFilter(musicUrl, extFilter))
return false;
// if there are extra WHERE conditions we might need access
// to songview for these conditions
if (extFilter.where.find("songview") != string::npos)
{
extFilter.AppendJoin("JOIN songview ON songview.idAlbum = albumview.idAlbum");
extFilter.AppendGroup("albumview.idAlbum");
}
CStdString strSQLExtra;
if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
return false;
// Apply the limiting directly here if there's no special sorting but limiting
if (extFilter.limit.empty() &&
sortDescription.sortBy == SortByNone &&
(sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
{
total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
}
strSQL = PrepareSQL(strSQL, !filter.fields.empty() && filter.fields.compare("*") != 0 ? filter.fields.c_str() : "albumview.*") + strSQLExtra;
CLog::Log(LOGDEBUG, "%s query: %s", __FUNCTION__, strSQL.c_str());
// run query
unsigned int time = XbmcThreads::SystemClockMillis();
if (!m_pDS->query(strSQL.c_str()))
return false;
CLog::Log(LOGDEBUG, "%s - query took %i ms",
__FUNCTION__, XbmcThreads::SystemClockMillis() - time); time = XbmcThreads::SystemClockMillis();
int iRowsFound = m_pDS->num_rows();
if (iRowsFound <= 0)
{
m_pDS->close();
return false;
}
// store the total value of items as a property
if (total < iRowsFound)
total = iRowsFound;
items.SetProperty("total", total);
DatabaseResults results;
results.reserve(iRowsFound);
if (!SortUtils::SortFromDataset(sortDescription, MediaTypeAlbum, m_pDS, results))
return false;
// get data from returned rows
items.Reserve(results.size());
const dbiplus::query_data &data = m_pDS->get_result_set().records;
for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
{
unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
const dbiplus::sql_record* const record = data.at(targetRow);
try
{
CMusicDbUrl itemUrl = musicUrl;
CStdString path; path.Format("%ld/", record->at(album_idAlbum).get_asInt());
itemUrl.AppendPath(path);
CFileItemPtr pItem(new CFileItem(itemUrl.ToString(), GetAlbumFromDataset(record)));
pItem->SetIconImage("DefaultAlbumCover.png");
items.Add(pItem);
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s - out of memory getting listing (got %i)", __FUNCTION__, items.Size());
}
}
// cleanup
m_pDS->close();
return true;
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s (%s) failed", __FUNCTION__, filter.where.c_str());
}
return false;
}
bool CMusicDatabase::GetSongsByWhere(const CStdString &baseDir, const Filter &filter, CFileItemList &items, const SortDescription &sortDescription /* = SortDescription() */)
{
if (m_pDB.get() == NULL || m_pDS.get() == NULL)
return false;
try
{
unsigned int time = XbmcThreads::SystemClockMillis();
int total = -1;
CStdString strSQL = "SELECT %s FROM songview ";
Filter extFilter = filter;
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(baseDir) || !GetFilter(musicUrl, extFilter))
return false;
// if there are extra WHERE conditions we might need access
// to songview for these conditions
if (extFilter.where.find("albumview") != string::npos)
{
extFilter.AppendJoin("JOIN albumview ON albumview.idAlbum = songview.idAlbum");
extFilter.AppendGroup("songview.idSong");
}
CStdString strSQLExtra;
if (!BuildSQL(strSQLExtra, extFilter, strSQLExtra))
return false;
// Apply the limiting directly here if there's no special sorting but limiting
if (extFilter.limit.empty() &&
sortDescription.sortBy == SortByNone &&
(sortDescription.limitStart > 0 || sortDescription.limitEnd > 0))
{
total = (int)strtol(GetSingleValue(PrepareSQL(strSQL, "COUNT(1)") + strSQLExtra, m_pDS).c_str(), NULL, 10);
strSQLExtra += DatabaseUtils::BuildLimitClause(sortDescription.limitEnd, sortDescription.limitStart);
}
strSQL = PrepareSQL(strSQL, !filter.fields.empty() && filter.fields.compare("*") != 0 ? filter.fields.c_str() : "songview.*") + strSQLExtra;
CLog::Log(LOGDEBUG, "%s query = %s", __FUNCTION__, strSQL.c_str());
// run query
if (!m_pDS->query(strSQL.c_str()))
return false;
int iRowsFound = m_pDS->num_rows();
if (iRowsFound == 0)
{
m_pDS->close();
return false;
}
// store the total value of items as a property
if (total < iRowsFound)
total = iRowsFound;
items.SetProperty("total", total);
DatabaseResults results;
results.reserve(iRowsFound);
if (!SortUtils::SortFromDataset(sortDescription, MediaTypeSong, m_pDS, results))
return false;
// get data from returned rows
items.Reserve(results.size());
const dbiplus::query_data &data = m_pDS->get_result_set().records;
int count = 0;
for (DatabaseResults::const_iterator it = results.begin(); it != results.end(); it++)
{
unsigned int targetRow = (unsigned int)it->at(FieldRow).asInteger();
const dbiplus::sql_record* const record = data.at(targetRow);
try
{
CFileItemPtr item(new CFileItem);
GetFileItemFromDataset(record, item.get(), musicUrl.ToString());
// HACK for sorting by database returned order
item->m_iprogramCount = ++count;
items.Add(item);
}
catch (...)
{
m_pDS->close();
CLog::Log(LOGERROR, "%s: out of memory loading query: %s", __FUNCTION__, filter.where.c_str());
return (items.Size() > 0);
}
}
// cleanup
m_pDS->close();
CLog::Log(LOGDEBUG, "%s(%s) - took %d ms", __FUNCTION__, filter.where.c_str(), XbmcThreads::SystemClockMillis() - time);
return true;
}
catch (...)
{
// cleanup
m_pDS->close();
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
}
return false;
}
bool CMusicDatabase::GetSongsByYear(const CStdString& baseDir, CFileItemList& items, int year)
{
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(baseDir))
return false;
musicUrl.AddOption("year", year);
Filter filter;
return GetSongsByWhere(baseDir, filter, items);
}
bool CMusicDatabase::GetSongsNav(const CStdString& strBaseDir, CFileItemList& items, int idGenre, int idArtist, int idAlbum, const SortDescription &sortDescription /* = SortDescription() */)
{
CMusicDbUrl musicUrl;
if (!musicUrl.FromString(strBaseDir))
return false;
if (idAlbum > 0)
musicUrl.AddOption("albumid", idAlbum);
if (idGenre > 0)
musicUrl.AddOption("genreid", idGenre);
if (idArtist > 0)
musicUrl.AddOption("artistid", idArtist);
Filter filter;
return GetSongsByWhere(musicUrl.ToString(), filter, items, sortDescription);
}
bool CMusicDatabase::UpdateOldVersion(int version)
{
if (version < 16)
{
// only if MySQL is used and default character set is not utf8
// string data needs to be converted to proper utf8
CStdString charset = m_pDS->getDatabase()->getDefaultCharset();
if (!m_sqlite && !charset.empty() && charset != "utf8")
{
map<CStdString, CStdStringArray> tables;
map<CStdString, CStdStringArray>::iterator itt;
CStdStringArray::iterator itc;
//columns that need to be converted
CStdStringArray c1;
c1.push_back("strAlbum");
c1.push_back("strExtraArtists");
c1.push_back("strExtraGenres");
tables.insert(pair<CStdString, CStdStringArray> ("album", c1));
CStdStringArray c2;
c2.push_back("strExtraGenres");
c2.push_back("strMoods");
c2.push_back("strStyles");
c2.push_back("strThemes");
c2.push_back("strReview");
c2.push_back("strLabel");
tables.insert(pair<CStdString, CStdStringArray> ("albuminfo", c2));
CStdStringArray c3;
c3.push_back("strTitle");
tables.insert(pair<CStdString, CStdStringArray> ("albuminfosong", c3));
CStdStringArray c4;
c4.push_back("strArtist");
tables.insert(pair<CStdString, CStdStringArray> ("artist", c4));
CStdStringArray c5;
c5.push_back("strBorn");
c5.push_back("strFormed");
c5.push_back("strGenres");
c5.push_back("strMoods");
c5.push_back("strStyles");
c5.push_back("strInstruments");
c5.push_back("strBiography");
c5.push_back("strDied");
c5.push_back("strDisbanded");
c5.push_back("strYearsActive");
tables.insert(pair<CStdString, CStdStringArray> ("artistinfo", c5));
CStdStringArray c6;
c6.push_back("strAlbum");
tables.insert(pair<CStdString, CStdStringArray> ("discography", c6));
CStdStringArray c7;
c7.push_back("strGenre");
tables.insert(pair<CStdString, CStdStringArray> ("genre", c7));
CStdStringArray c8;
c8.push_back("strKaraLyrics");
tables.insert(pair<CStdString, CStdStringArray> ("karaokedata", c8));
CStdStringArray c9;
c9.push_back("strTitle");
c9.push_back("strFilename");
c9.push_back("comment");
tables.insert(pair<CStdString, CStdStringArray> ("song", c9));
CStdStringArray c10;
c10.push_back("strPath");
tables.insert(pair<CStdString, CStdStringArray> ("path", c10));
for (itt = tables.begin(); itt != tables.end(); ++itt)
{
CStdString q;
q = PrepareSQL("UPDATE `%s` SET", itt->first.c_str());
for (itc = itt->second.begin(); itc != itt->second.end(); ++itc)
{
q += PrepareSQL(" `%s` = CONVERT(CAST(CONVERT(`%s` USING %s) AS BINARY) USING utf8)",
itc->c_str(), itc->c_str(), charset.c_str());
if (*itc != itt->second.back())
{
q += ", ";
}
}
m_pDS->exec(q);
}
}
}
if (version < 17)
{
m_pDS->exec("CREATE INDEX idxAlbum2 ON album(idArtist)");
m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
m_pDS->exec("CREATE INDEX idxSong4 ON song(idArtist)");
m_pDS->exec("CREATE INDEX idxSong5 ON song(idGenre)");
m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
}
if (version < 19)
{
int len = g_advancedSettings.m_musicItemSeparator.size() + 1;
CStdString sql = PrepareSQL("UPDATE song SET strExtraArtists=SUBSTR(strExtraArtists,%i), strExtraGenres=SUBSTR(strExtraGenres,%i)", len, len);
m_pDS->exec(sql.c_str());
sql = PrepareSQL("UPDATE album SET strExtraArtists=SUBSTR(strExtraArtists,%i), strExtraGenres=SUBSTR(strExtraGenres,%i)", len, len);
m_pDS->exec(sql.c_str());
}
if (version < 21)
{
m_pDS->exec("CREATE TABLE album_artist ( idArtist integer, idAlbum integer, boolFeatured integer, iOrder integer )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_1 ON album_artist ( idAlbum, idArtist )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumArtist_2 ON album_artist ( idArtist, idAlbum )\n");
m_pDS->exec("CREATE INDEX idxAlbumArtist_3 ON album_artist ( boolFeatured )\n");
m_pDS->exec("INSERT INTO album_artist (idArtist, idAlbum, boolFeatured, iOrder) SELECT idArtist, idAlbum, 1, iPosition FROM exartistalbum");
m_pDS->exec("REPLACE INTO album_artist (idArtist, idAlbum, boolFeatured, iOrder) SELECT idArtist, idAlbum, 0, 0 FROM album");
CStdString strSQL;
strSQL=PrepareSQL("SELECT album.idAlbum AS idAlbum, strExtraArtists,"
" album.idArtist AS idArtist, strArtist FROM album "
" LEFT OUTER JOIN artist ON album.idArtist=artist.idArtist");
if (!m_pDS->query(strSQL.c_str()))
{
CLog::Log(LOGDEBUG, "%s could not upgrade albums table", __FUNCTION__);
return false;
}
VECALBUMS albums;
while (!m_pDS->eof())
{
CAlbum album;
album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
album.artist.push_back(m_pDS->fv("strArtist").get_asString());
if (!m_pDS->fv("strExtraArtists").get_asString().empty())
{
std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv("strExtraArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
album.artist.insert(album.artist.end(), extraArtists.begin(), extraArtists.end());
}
albums.push_back(album);
m_pDS->next();
}
m_pDS->close();
m_pDS->exec("CREATE TABLE album_new ( idAlbum integer primary key, strAlbum varchar(256), strArtists text, idGenre integer, strExtraGenres text, iYear integer, idThumb integer)");
m_pDS->exec("INSERT INTO album_new ( idAlbum, strAlbum, idGenre, strExtraGenres, iYear, idThumb ) SELECT idAlbum, strAlbum, idGenre, strExtraGenres, iYear, idThumb FROM album");
for (VECALBUMS::iterator it = albums.begin(); it != albums.end(); ++it)
{
CStdString strSQL;
strSQL = PrepareSQL("UPDATE album_new SET strArtists='%s' WHERE idAlbum=%i", StringUtils::Join(it->artist, g_advancedSettings.m_musicItemSeparator).c_str(), it->idAlbum);
m_pDS->exec(strSQL);
}
m_pDS->exec("DROP TABLE album");
m_pDS->exec("ALTER TABLE album_new RENAME TO album");
m_pDS->exec("CREATE INDEX idxAlbum ON album(strAlbum)");
m_pDS->exec("DROP TABLE IF EXISTS exartistalbum");
}
if (version < 22)
{
m_pDS->exec("CREATE TABLE song_artist ( idArtist integer, idSong integer, boolFeatured integer, iOrder integer )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_1 ON song_artist ( idSong, idArtist )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongArtist_2 ON song_artist ( idArtist, idSong )\n");
m_pDS->exec("CREATE INDEX idxSongArtist_3 ON song_artist ( boolFeatured )\n");
m_pDS->exec("INSERT INTO song_artist (idArtist, idSong, boolFeatured, iOrder) SELECT idArtist, idSong, 1, iPosition FROM exartistsong");
m_pDS->exec("REPLACE INTO song_artist (idArtist, idSong, boolFeatured, iOrder) SELECT idArtist, idSong, 0, 0 FROM song");
CStdString strSQL;
strSQL=PrepareSQL("SELECT song.idSong AS idSong, strExtraArtists,"
" song.idArtist AS idArtist, strArtist FROM song "
" LEFT OUTER JOIN artist ON song.idArtist=artist.idArtist");
if (!m_pDS->query(strSQL.c_str()))
{
CLog::Log(LOGDEBUG, "%s could not upgrade songs table", __FUNCTION__);
return false;
}
VECSONGS songs;
while (!m_pDS->eof())
{
CSong song;
song.idSong = m_pDS->fv("idSong").get_asInt();
song.artist.push_back(m_pDS->fv("strArtist").get_asString());
if (!m_pDS->fv("strExtraArtists").get_asString().empty())
{
std::vector<std::string> extraArtists = StringUtils::Split(m_pDS->fv("strExtraArtists").get_asString(), g_advancedSettings.m_musicItemSeparator);
song.artist.insert(song.artist.end(), extraArtists.begin(), extraArtists.end());
}
songs.push_back(song);
m_pDS->next();
}
m_pDS->close();
m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, idGenre integer, strExtraGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, strMusicBrainzArtistID text, strMusicBrainzAlbumID text, strMusicBrainzAlbumArtistID text, strMusicBrainzTRMID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)");
m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, idGenre, strExtraGenres, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment ) SELECT idSong, idAlbum, idPath, idGenre, strExtraGenres, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
for (VECSONGS::iterator it = songs.begin(); it != songs.end(); ++it)
{
CStdString strSQL;
strSQL = PrepareSQL("UPDATE song_new SET strArtists='%s' WHERE idSong=%i", StringUtils::Join(it->artist, g_advancedSettings.m_musicItemSeparator).c_str(), it->idSong);
m_pDS->exec(strSQL);
}
m_pDS->exec("DROP TABLE song");
m_pDS->exec("ALTER TABLE song_new RENAME TO song");
m_pDS->exec("CREATE INDEX idxSong ON song(strTitle)");
m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
m_pDS->exec("DROP TABLE IF EXISTS exartistsong");
}
if (version < 23)
{
m_pDS->exec("CREATE TABLE album_genre ( idGenre integer, idAlbum integer, iOrder integer )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_1 ON album_genre ( idAlbum, idGenre )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxAlbumGenre_2 ON album_genre ( idGenre, idAlbum )\n");
m_pDS->exec("INSERT INTO album_genre ( idGenre, idAlbum, iOrder) SELECT idGenre, idAlbum, iPosition FROM exgenrealbum");
m_pDS->exec("REPLACE INTO album_genre ( idGenre, idAlbum, iOrder) SELECT idGenre, idAlbum, 0 FROM album");
CStdString strSQL;
strSQL=PrepareSQL("SELECT album.idAlbum AS idAlbum, strExtraGenres,"
" album.idGenre AS idGenre, strGenre FROM album "
" JOIN genre ON album.idGenre=genre.idGenre");
if (!m_pDS->query(strSQL.c_str()))
{
CLog::Log(LOGDEBUG, "%s could not upgrade albums table", __FUNCTION__);
return false;
}
VECALBUMS albums;
while (!m_pDS->eof())
{
CAlbum album;
album.idAlbum = m_pDS->fv("idAlbum").get_asInt();
album.genre.push_back(m_pDS->fv("strGenre").get_asString());
if (!m_pDS->fv("strExtraGenres").get_asString().empty())
{
std::vector<std::string> extraGenres = StringUtils::Split(m_pDS->fv("strExtraGenres").get_asString(), g_advancedSettings.m_musicItemSeparator);
album.genre.insert(album.genre.end(), extraGenres.begin(), extraGenres.end());
}
albums.push_back(album);
m_pDS->next();
}
m_pDS->close();
m_pDS->exec("CREATE TABLE album_new ( idAlbum integer primary key, strAlbum varchar(256), strArtists text, strGenres text, iYear integer, idThumb integer)");
m_pDS->exec("INSERT INTO album_new ( idAlbum, strAlbum, strArtists, iYear, idThumb) SELECT idAlbum, strAlbum, strArtists, iYear, idThumb FROM album");
for (VECALBUMS::iterator it = albums.begin(); it != albums.end(); ++it)
{
CStdString strSQL;
strSQL = PrepareSQL("UPDATE album_new SET strGenres='%s' WHERE idAlbum=%i", StringUtils::Join(it->genre, g_advancedSettings.m_musicItemSeparator).c_str(), it->idAlbum);
m_pDS->exec(strSQL);
}
m_pDS->exec("DROP TABLE album");
m_pDS->exec("ALTER TABLE album_new RENAME TO album");
m_pDS->exec("CREATE INDEX idxAlbum ON album(strAlbum)");
m_pDS->exec("DROP TABLE IF EXISTS exgenrealbum");
}
if (version < 24)
{
m_pDS->exec("CREATE TABLE song_genre ( idGenre integer, idSong integer, iOrder integer )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_1 ON song_genre ( idSong, idGenre )\n");
m_pDS->exec("CREATE UNIQUE INDEX idxSongGenre_2 ON song_genre ( idGenre, idSong )\n");
m_pDS->exec("INSERT INTO song_genre ( idGenre, idSong, iOrder) SELECT idGenre, idSong, iPosition FROM exgenresong");
m_pDS->exec("REPLACE INTO song_genre ( idGenre, idSong, iOrder) SELECT idGenre, idSong, 0 FROM song");
CStdString strSQL;
strSQL=PrepareSQL("SELECT song.idSong AS idSong, strExtraGenres,"
" song.idGenre AS idGenre, strGenre FROM song "
" JOIN genre ON song.idGenre=genre.idGenre");
if (!m_pDS->query(strSQL.c_str()))
{
CLog::Log(LOGDEBUG, "%s could not upgrade songs table", __FUNCTION__);
return false;
}
VECSONGS songs;
while (!m_pDS->eof())
{
CSong song;
song.idSong = m_pDS->fv("idSong").get_asInt();
song.genre.push_back(m_pDS->fv("strGenre").get_asString());
if (!m_pDS->fv("strExtraGenres").get_asString().empty())
{
std::vector<std::string> extraGenres = StringUtils::Split(m_pDS->fv("strExtraGenres").get_asString(), g_advancedSettings.m_musicItemSeparator);
song.genre.insert(song.genre.end(), extraGenres.begin(), extraGenres.end());
}
songs.push_back(song);
m_pDS->next();
}
m_pDS->close();
m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, strGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, strMusicBrainzArtistID text, strMusicBrainzAlbumID text, strMusicBrainzAlbumArtistID text, strMusicBrainzTRMID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)\n");
m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment) SELECT idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID, strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
for (VECSONGS::iterator it = songs.begin(); it != songs.end(); ++it)
{
CStdString strSQL;
strSQL = PrepareSQL("UPDATE song_new SET strGenres='%s' WHERE idSong=%i", StringUtils::Join(it->genre, g_advancedSettings.m_musicItemSeparator).c_str(), it->idSong);
m_pDS->exec(strSQL);
}
m_pDS->exec("DROP TABLE song");
m_pDS->exec("ALTER TABLE song_new RENAME TO song");
m_pDS->exec("CREATE INDEX idxSong ON song(strTitle)");
m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
m_pDS->exec("DROP TABLE IF EXISTS exgenresong");
}
if (version < 25)
{
m_pDS->exec("ALTER TABLE album ADD bCompilation integer not null default '0'");
m_pDS->exec("CREATE INDEX idxAlbum_1 ON album(bCompilation)");
}
if (version < 26)
{ // add art table
m_pDS->exec("CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, type TEXT, url TEXT)");
m_pDS->exec("CREATE INDEX ix_art ON art(media_id, media_type(20), type(20))");
m_pDS->exec("CREATE TRIGGER delete_song AFTER DELETE ON song FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idSong AND media_type='song'; END");
m_pDS->exec("CREATE TRIGGER delete_album AFTER DELETE ON album FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album'; END");
m_pDS->exec("CREATE TRIGGER delete_artist AFTER DELETE ON artist FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'; END");
}
if (version < 27)
{
m_pDS->exec("DROP TABLE thumb");
g_settings.m_musicNeedsUpdate = 27;
g_settings.Save();
}
if (version < 28)
{
m_pDS->exec("CREATE TABLE karaokedata_new ( iKaraNumber integer primary key, idSong integer, iKaraDelay integer, strKaraEncoding text, strKaralyrics text, strKaraLyrFileCRC text )");
m_pDS->exec("INSERT INTO karaokedata_new (iKaraNumber, idSong, iKaraDelay, strKaraEncoding, strKaralyrics, strKaraLyrFileCRC) SELECT iKaraNumber, idSong, iKaraDelay, strKaraEncoding, strKaralyrics, strKaraLyrFileCRC FROM karaokedata");
m_pDS->exec("DROP TABLE karaokedata");
m_pDS->exec("ALTER TABLE karaokedata_new RENAME TO karaokedata");
m_pDS->exec("CREATE INDEX idxKaraNumber on karaokedata(iKaraNumber)");
m_pDS->exec("CREATE INDEX idxKarSong on karaokedata(idSong)");
// Seed the Karaoke index in case the table was empty when we copied data over
m_pDS->exec(PrepareSQL("INSERT INTO karaokedata (iKaraNumber) VALUES (%d)", g_advancedSettings.m_karaokeStartIndex - 1));
m_pDS->exec(PrepareSQL("DELETE FROM karaokedata WHERE iKaraNumber = %d", g_advancedSettings.m_karaokeStartIndex - 1));
}
if (version < 29)
{
m_pDS->exec("DROP INDEX idxSong6 ON song");
m_pDS->exec("CREATE UNIQUE INDEX idxSong6 on song( idPath, strFileName(255) )");
}
if (version < 30)
{
m_pDS->exec("ALTER TABLE artist ADD strMusicBrainzArtistID text\n");
m_pDS->exec("ALTER TABLE album ADD strMusicBrainzAlbumID text\n");
m_pDS->exec("CREATE TABLE song_new ( idSong integer primary key, idAlbum integer, idPath integer, strArtists text, strGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)\n");
m_pDS->exec("INSERT INTO song_new ( idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment) SELECT idSong, idAlbum, idPath, strArtists, strTitle, iTrack, iDuration, iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID, iTimesPlayed, iStartOffset, iEndOffset, idThumb, lastplayed, rating, comment FROM song");
m_pDS->exec("DROP TABLE song");
m_pDS->exec("ALTER TABLE song_new RENAME TO song");
m_pDS->exec("CREATE INDEX idxSong ON song(strTitle)");
m_pDS->exec("CREATE INDEX idxSong1 ON song(iTimesPlayed)");
m_pDS->exec("CREATE INDEX idxSong2 ON song(lastplayed)");
m_pDS->exec("CREATE INDEX idxSong3 ON song(idAlbum)");
m_pDS->exec("CREATE INDEX idxSong6 ON song(idPath)");
m_pDS->exec("CREATE UNIQUE INDEX idxArtist1 ON artist(strMusicBrainzArtistID(36))");
}
// always recreate the views after any table change
CreateViews();
return true;
}
unsigned int CMusicDatabase::GetSongIDs(const Filter &filter, vector<pair<int,int> > &songIDs)
{
try
{
if (NULL == m_pDB.get()) return 0;
if (NULL == m_pDS.get()) return 0;
CStdString strSQL = "select idSong from songview ";
if (!CDatabase::BuildSQL(strSQL, filter, strSQL))
return false;
if (!m_pDS->query(strSQL.c_str())) return 0;
songIDs.clear();
if (m_pDS->num_rows() == 0)
{
m_pDS->close();
return 0;
}
songIDs.reserve(m_pDS->num_rows());
while (!m_pDS->eof())
{
songIDs.push_back(make_pair<int,int>(1,m_pDS->fv(song_idSong).get_asInt()));
m_pDS->next();
} // cleanup
m_pDS->close();
return songIDs.size();
}
catch (...)
{
CLog::Log(LOGERROR, "%s(%s) failed", __FUNCTION__, filter.where.c_str());
}
return 0;
}
int CMusicDatabase::GetSongsCount(const Filter &filter)
{
try
{
if (NULL == m_pDB.get()) return 0;
if (NULL == m_pDS.get()) return 0;