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 ); } }
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 ); } }
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(); } }
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 ); }
// ------ 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; }
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::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 ); } }
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; }
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; }