UrlStatisticsStore::UrlStatisticsStore( Meta::Track *track, const QString &permanentUrl )
: PersistentStatisticsStore( track )
, m_permanentUrl( permanentUrl )
{
if( m_permanentUrl.isEmpty() )
m_permanentUrl = track->uidUrl();
SqlStorage *sql = CollectionManager::instance()->sqlStorage();
if( !sql )
{
warning() << __PRETTY_FUNCTION__ << "could not get SqlStorage, aborting";
return;
}
const QString query = "SELECT firstplayed, lastplayed, score, rating, playcount FROM "
"statistics_permanent WHERE url = '%1'";
QStringList result = sql->query( query.arg( sql->escape( m_permanentUrl ) ) );
if( !result.isEmpty() )
{
m_firstPlayed = QDateTime::fromString( result.value( 0 ), s_sqlDateFormat );
m_lastPlayed = QDateTime::fromString( result.value( 1 ), s_sqlDateFormat );
m_score = result.value( 2 ).toDouble();
m_rating = result.value( 3 ).toInt();
m_playCount = result.value( 4 ).toInt();
}
}
void
UrlStatisticsStore::save()
{
SqlStorage *sql = CollectionManager::instance()->sqlStorage();
if( !sql )
{
warning() << __PRETTY_FUNCTION__ << "could not get SqlStorage, aborting";
return;
}
const QString check = "SELECT COUNT(*) FROM statistics_permanent WHERE url = '%1'";
QStringList rsCheck = sql->query( check.arg( sql->escape( m_permanentUrl ) ) );
if( !rsCheck.isEmpty() )
{
QString sqlString;
if( rsCheck.first().toInt() )
{
sqlString = "UPDATE statistics_permanent SET firstplayed = '%1',lastplayed = '%2',"
"score = %3,rating = %4,playcount=%5 WHERE url = '%6'";
}
else
{
sqlString = "INSERT INTO statistics_permanent(firstplayed,lastplayed,score,"
"rating,playcount,url) VALUE ('%1','%2',%3,%4,%5,'%6')";
}
sqlString = sqlString.arg( m_firstPlayed.toString( s_sqlDateFormat ),
m_lastPlayed.toString( s_sqlDateFormat ),
QString::number( m_score ),
QString::number( m_rating ),
QString::number( m_playCount ),
sql->escape( m_permanentUrl ) );
sql->query( sqlString );
}
}
Meta::TrackPtr
ServiceSqlCollection::trackForUrl(const KUrl & url)
{
if ( !possiblyContainsTrack( url ) ) //do we even bother trying?
return Meta::TrackPtr();
//split out the parts we can be sure about ( strip username and such info )
QString trackRows = m_metaFactory->getTrackSqlRows() + ',' + m_metaFactory->getAlbumSqlRows() + ',' + m_metaFactory->getArtistSqlRows() + ',' + m_metaFactory->getGenreSqlRows();
QString prefix = m_metaFactory->tablePrefix();
QString pristineUrl = url.url();
SqlStorage *sqlDb = CollectionManager::instance()->sqlStorage();
QString from = prefix + "_tracks";
from += " LEFT JOIN " + prefix + "_albums ON " + prefix + "_tracks.album_id = " + prefix + "_albums.id";
from += " LEFT JOIN " + prefix + "_artists ON " + prefix + "_albums.artist_id = " + prefix + "_artists.id";
from += " LEFT JOIN " + prefix + "_genre ON " + prefix + "_genre.album_id = " + prefix + "_albums.id";
QString queryString = QString( "select DISTINCT %1 FROM %2 WHERE %3_tracks.preview_url = '%4' GROUP BY %5_tracks.id;" )
.arg( trackRows)
.arg( from )
.arg( prefix )
.arg( sqlDb->escape( pristineUrl ) )
.arg( prefix );
//debug() << "Querying for track: " << queryString;
QStringList result = sqlDb->query( queryString );
//debug() << "result: " << result;
return m_registry->getTrack( result );
}
void
SqlScanResultProcessor::deleteDeletedDirectories()
{
SqlStorage *storage = m_collection->sqlStorage();
QList<DirectoryEntry> toCheck;
switch( m_type )
{
case GenericScanManager::FullScan:
case GenericScanManager::UpdateScan:
toCheck = mountedDirectories();
break;
case GenericScanManager::PartialUpdateScan:
toCheck = deletedDirectories();
}
// -- check if the have been found during the scan
foreach( const DirectoryEntry &e, toCheck )
{
/* we need to match directories by their (absolute) path, otherwise following
* scenario triggers statistics loss (bug 298275):
*
* 1. user relocates collection to different filesystem, but clones path structure
* or toggles MassStorageDeviceHandler enabled in Config -> plugins.
* 2. collectionscanner knows nothings about directory ids, so it doesn't detect
* any track changes and emits a bunch of skipped (unchanged) dirs with no
* tracks.
* 3. SqlRegistry::getDirectory() called there from returns different directory id
* then in past.
* 4. deleteDeletedDirectories() is called, and if it operates on directory ids,
* it happily removes _all_ directories, taking tracks with it.
* 5. Tracks disappear from the UI until full rescan, stats, lyrics, labels are
* lost forever.
*/
QString path = m_collection->mountPointManager()->getAbsolutePath( e.deviceId, e.dir );
bool deleteThisDir = false;
if( !m_foundDirectories.contains( path ) )
deleteThisDir = true;
else if( m_foundDirectories.value( path ) != e.dirId )
{
int newDirId = m_foundDirectories.value( path );
// as a safety measure, we don't delete the old dir if relocation fails
deleteThisDir = relocateTracksToNewDirectory( e.dirId, newDirId );
}
if( deleteThisDir )
{
deleteDeletedTracks( e.dirId );
QString query = QString( "DELETE FROM directories WHERE id = %1;" ).arg( e.dirId );
storage->query( query );
}
}
void MagnatuneDatabaseWorker::doFetchTrackswithMood()
{
SqlStorage *sqlDb = CollectionManager::instance()->sqlStorage();
//ok, a huge joing turned out to be _really_ slow, so lets chop up the query a bit...
QString queryString = "SELECT DISTINCT track_id FROM magnatune_moods WHERE mood =\"" + m_mood + "\" ORDER BY RANDOM() LIMIT " + QString::number( m_noOfTracks, 10 ) + ';';
QStringList result = sqlDb->query( queryString );
int rowCount = ( m_registry->factory()->getTrackSqlRowCount() +
m_registry->factory()->getAlbumSqlRowCount() +
m_registry->factory()->getArtistSqlRowCount() +
m_registry->factory()->getGenreSqlRowCount() );
foreach( const QString &idString, result ) {
QString queryString = "SELECT DISTINCT ";
queryString += m_registry->factory()->getTrackSqlRows() + ',' +
m_registry->factory()->getAlbumSqlRows() + ',' +
m_registry->factory()->getArtistSqlRows() + ',' +
m_registry->factory()->getGenreSqlRows();
queryString += " FROM magnatune_tracks LEFT JOIN magnatune_albums ON magnatune_tracks.album_id = magnatune_albums.id LEFT JOIN magnatune_artists ON magnatune_albums.artist_id = magnatune_artists.id LEFT JOIN magnatune_genre ON magnatune_genre.album_id = magnatune_albums.id";
queryString += " WHERE magnatune_tracks.id = " + idString;
queryString += " GROUP BY magnatune_tracks.id";
//debug() << "Querying for moody tracks: " << queryString;
QStringList result = sqlDb->query( queryString );
//debug() << "result: " << result;
int resultRows = result.count() / rowCount;
for( int i = 0; i < resultRows; i++ )
{
QStringList row = result.mid( i*rowCount, rowCount );
Meta::TrackPtr trackptr = m_registry->getTrack( row );
m_moodyTracks.append( trackptr );
}
}
void MagnatuneDatabaseWorker::doFetchMoodMap()
{
SqlStorage *sqlDb = CollectionManager::instance()->sqlStorage();
QString queryString = "select count( mood ), mood from magnatune_moods GROUP BY mood;";
debug() << "Querying for moods: " << queryString;
QStringList result = sqlDb->query( queryString );
debug() << "result: " << result;
while ( !result.isEmpty() ) {
int count = result.takeFirst().toInt();
QString string = result.takeFirst();
m_moodMap.insert( string, count );
}
}
void
SqlPlaylist::saveTracks()
{
int trackNum = 1;
SqlStorage *sql = CollectionManager::instance()->sqlStorage();
foreach( Meta::TrackPtr trackPtr, m_tracks )
{
if( trackPtr )
{
debug() << "saving track with url " << trackPtr->uidUrl();
QString query = "INSERT INTO playlist_tracks ( playlist_id, track_num, url, title, "
"album, artist, length, uniqueid ) VALUES ( %1, %2, '%3', '%4', '%5', "
"'%6', %7, '%8' );";
query = query.arg( QString::number( m_dbId ), QString::number( trackNum ),
sql->escape( trackPtr->uidUrl() ),
sql->escape( trackPtr->prettyName() ),
trackPtr->album() ? sql->escape( trackPtr->album()->prettyName() ) : "",
trackPtr->artist()? sql->escape( trackPtr->artist()->prettyName() ) : "",
QString::number( trackPtr->length() ),
sql->escape( trackPtr->uidUrl() ) );
sql->insert( query, "playlist_tracks" );
trackNum++;
}
}
}
int
SqlPlaylist::trackCount() const
{
if( m_tracksLoaded )
return m_tracks.count();
QString query = "SELECT COUNT(id) FROM playlist_tracks WHERE playlist_id=%1;";
SqlStorage *sql = CollectionManager::instance()->sqlStorage();
Q_ASSERT( sql );
QStringList results = sql->query( query.arg( m_dbId ) );
debug() << results;
if( results.isEmpty() )
{
error() << "no results for COUNT query on playlist_tracks table!";
return -1;
}
int trackCount = results.first().toInt();
return trackCount;
}
// ------ directory
int
SqlRegistry::getDirectory( const QString &path, uint mtime )
{
int dirId;
int deviceId = m_collection->mountPointManager()->getIdForUrl( path );
QString rdir = m_collection->mountPointManager()->getRelativePath( deviceId, path );
SqlStorage *storage = m_collection->sqlStorage();
// - find existing entry
QString query = QString( "SELECT id, changedate FROM directories "
"WHERE deviceid = %1 AND dir = '%2';" )
.arg( QString::number( deviceId ), storage->escape( rdir ) );
QStringList res = storage->query( query );
// - create new entry
if( res.isEmpty() )
{
debug() << "SqlRegistry::getDirectory(): new directory" << path;
QString insert = QString( "INSERT INTO directories(deviceid,changedate,dir) "
"VALUES (%1,%2,'%3');" )
.arg( QString::number( deviceId ), QString::number( mtime ),
storage->escape( rdir ) );
dirId = storage->insert( insert, "directories" );
m_collectionChanged = true;
}
else
{
// update old one
dirId = res[0].toUInt();
uint oldMtime = res[1].toUInt();
if( oldMtime != mtime )
{
QString update = QString( "UPDATE directories SET changedate = %1 "
"WHERE id = %2;" )
.arg( QString::number( mtime ), res[0] );
debug() << "SqlRegistry::getDirectory(): update directory" << path << "(id" <<
res[0] << ") from" << oldMtime << "to" << mtime << "UNIX time";
storage->query( update );
}
}
return dirId;
}
bool
SqlPlaylist::saveToDb( bool tracks )
{
int parentId = -1;
if( m_parent )
parentId = m_parent->id();
SqlStorage *sql = CollectionManager::instance()->sqlStorage();
//figure out if we have a urlId and if this id is already in the db, if so, update it instead of creating a new one.
if( !m_urlId.isEmpty() )
{
debug() << "Checking " << m_urlId << " against db";
//check if urlId exists
QString query = "SELECT id from playlists WHERE urlid='%1'";
query = query.arg( sql->escape( m_urlId ) );
QStringList result = sql->query( query );
if( !result.isEmpty() )
{
//set this id to the already existing one
m_dbId = result.at( 0 ).toInt();
debug() << "Got existing playlist with id " << m_dbId;
}
}
if( m_dbId != -1 )
{
//update existing
QString query = "UPDATE playlists SET parent_id=%1, name='%2', description='%3' WHERE id=%4;";
query = query.arg( QString::number( parentId ) )
.arg( sql->escape( m_name ) )
.arg( sql->escape( m_description ) )
.arg( QString::number( m_dbId ) );
CollectionManager::instance()->sqlStorage()->query( query );
if( tracks )
{
//delete existing tracks and insert all
query = "DELETE FROM playlist_tracks where playlist_id=%1;";
query = query.arg( QString::number( m_dbId ) );
CollectionManager::instance()->sqlStorage()->query( query );
saveTracks();
}
}
else
{
//insert new
QString query = "INSERT INTO playlists ( parent_id, name, description, urlid ) "
"VALUES ( %1, '%2', '%3', '%4' );";
query = query.arg( QString::number( parentId ) )
.arg( sql->escape( m_name ) )
.arg( sql->escape( m_description ) )
.arg( sql->escape( m_urlId ) );
m_dbId = CollectionManager::instance()->sqlStorage()->insert( query, "playlists" );
if( tracks )
saveTracks();
}
//HACK! if this has just been added from the collection scanner, the list is full of "dirty"
//tracks that might not all have been properly trackForUrl'ed, so clear the track list so we
//reload if we ever need them!
if( !m_urlId.isEmpty() )
{
m_tracks.clear();
m_tracksLoaded = false;
}
//clean the cache
if( m_parent )
m_parent->clear();
return true;
}
