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 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++; } } }
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; }
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; }