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