void
DatabaseCommand_UpdateSearchIndex::exec( DatabaseImpl* db )
{
db->m_fuzzyIndex->beginIndexing();
TomahawkSqlQuery q = db->newquery();
q.exec( "SELECT track.id, track.name, artist.name, artist.id FROM track, artist WHERE artist.id = track.artist" );
while ( q.next() )
{
IndexData ida;
ida.id = q.value( 0 ).toUInt();
ida.artistId = q.value( 3 ).toUInt();
ida.track = q.value( 1 ).toString();
ida.artist = q.value( 2 ).toString();
db->m_fuzzyIndex->appendFields( ida );
}
q.exec( "SELECT album.id, album.name FROM album" );
while ( q.next() )
{
IndexData ida;
ida.id = q.value( 0 ).toUInt();
ida.album = q.value( 1 ).toString();
db->m_fuzzyIndex->appendFields( ida );
}
tDebug( LOGVERBOSE ) << "Building index finished.";
db->m_fuzzyIndex->endIndexing();
}
void
DatabaseCommand_loadOps::exec( DatabaseImpl* dbi )
{
QList< dbop_ptr > ops;
if ( !m_since.isEmpty() )
{
TomahawkSqlQuery query = dbi->newquery();
query.prepare( QString( "SELECT id FROM oplog WHERE guid = ?" ) );
query.addBindValue( m_since );
query.exec();
if ( !query.next() )
{
tLog() << "Unknown oplog guid, requested, not replying:" << m_since;
Q_ASSERT( false );
emit done( m_since, m_since, ops );
return;
}
}
TomahawkSqlQuery query = dbi->newquery();
query.prepare( QString(
"SELECT guid, command, json, compressed, singleton "
"FROM oplog "
"WHERE source %1 "
"AND id > coalesce((SELECT id FROM oplog WHERE guid = ?),0) "
"ORDER BY id ASC"
).arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) )
);
query.addBindValue( m_since );
query.exec();
QString lastguid = m_since;
while( query.next() )
{
dbop_ptr op( new DBOp );
op->guid = query.value( 0 ).toString();
op->command = query.value( 1 ).toString();
op->payload = query.value( 2 ).toByteArray();
op->compressed = query.value( 3 ).toBool();
op->singleton = query.value( 4 ).toBool();
lastguid = op->guid;
ops << op;
}
// qDebug() << "Loaded" << ops.length() << "ops from db";
emit done( m_since, lastguid, ops );
}
void
DatabaseCommand_Resolve::fullTextResolve( DatabaseImpl* lib )
{
QList<Tomahawk::result_ptr> res;
typedef QPair<int, float> scorepair_t;
// STEP 1
QList< QPair<int, float> > trackPairs = lib->search( m_query );
QList< QPair<int, float> > albumPairs = lib->searchAlbum( m_query, 20 );
TomahawkSqlQuery query = lib->newquery();
query.prepare( "SELECT album.name, artist.id, artist.name FROM album, artist WHERE artist.id = album.artist AND album.id = ?" );
foreach ( const scorepair_t& albumPair, albumPairs )
{
query.bindValue( 0, albumPair.first );
query.exec();
QList<Tomahawk::album_ptr> albumList;
while ( query.next() )
{
Tomahawk::artist_ptr artist = Tomahawk::Artist::get( query.value( 1 ).toUInt(), query.value( 2 ).toString() );
Tomahawk::album_ptr album = Tomahawk::Album::get( albumPair.first, query.value( 0 ).toString(), artist );
albumList << album;
}
emit albums( m_query->id(), albumList );
}
void
DatabaseCommand_CollectionAttributes::exec( DatabaseImpl *lib )
{
TomahawkSqlQuery query = lib->newquery();
// QString sourceStr;
// if ( source().isNull() )
// sourceStr = "id IS NULL";
// else
// sourceStr = QString( "id == %1" ).arg( source()->id() );
QString typeStr;
if ( m_type == DatabaseCommand_SetCollectionAttributes::EchonestSongCatalog )
typeStr = "echonest_song";
else if ( m_type == DatabaseCommand_SetCollectionAttributes::EchonestArtistCatalog )
typeStr = "echonest_artist";
QString queryStr = QString( "SELECT id, v FROM collection_attributes WHERE k = \"%1\"" ).arg( typeStr );
qDebug() << "Doing queryL" << queryStr;
query.exec( queryStr );
PairList data;
while ( query.next() )
{
QPair< QString, QString > part;
part.first = query.value( 0 ).toString();
part.second = query.value( 1 ).toString();
data << part;
}
emit collectionAttributes( data );
}
void DatabaseCommand_LoadAllDynamicPlaylists::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
query.exec( QString( "SELECT playlist.guid as guid, title, info, creator, lastmodified, shared, currentrevision, dynamic_playlist.pltype, dynamic_playlist.plmode "
"FROM playlist, dynamic_playlist WHERE source %1 AND dynplaylist = 'true' AND playlist.guid = dynamic_playlist.guid" )
.arg( source()->isLocal() ? "IS NULL" :
QString( "=%1" ).arg( source()->id() )
) );
QList<dynplaylist_ptr> plists;
while ( query.next() )
{
QVariantList data = QVariantList() << query.value(6).toString() //current rev
<< query.value(1).toString() //title
<< query.value(2).toString() //info
<< query.value(3).toString() //creator
<< query.value(7).toString() // dynamic type
<< static_cast<GeneratorMode>(query.value(8).toInt()) // dynamic mode
<< query.value(5).toBool() //shared
<< query.value(4).toInt() //lastmod
<< query.value(0).toString(); //GUID
emit playlistLoaded( source(), data );
}
emit done();
}
void
DatabaseImpl::dumpDatabase()
{
QFile dump( "dbdump.txt" );
if ( !dump.open( QIODevice::WriteOnly | QIODevice::Text ) )
{
tDebug() << "Couldn't open dbdump.txt for writing!";
Q_ASSERT( false );
}
else
{
QTextStream dumpout( &dump );
TomahawkSqlQuery query = newquery();
query.exec( "SELECT * FROM oplog" );
while ( query.next() )
{
dumpout << "ID: " << query.value( 0 ).toInt() << endl
<< "GUID: " << query.value( 2 ).toString() << endl
<< "Command: " << query.value( 3 ).toString() << endl
<< "Singleton: " << query.value( 4 ).toBool() << endl
<< "JSON: " << ( query.value( 5 ).toBool() ? qUncompress( query.value( 6 ).toByteArray() ) : query.value( 6 ).toByteArray() )
<< endl << endl << endl;
}
}
}
void DatabaseCommand_LoadAllPlaylists::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
query.exec( QString( "SELECT guid, title, info, creator, lastmodified, shared, currentrevision "
"FROM playlist WHERE source %1 AND dynplaylist = 'false'" )
.arg( source()->isLocal() ? "IS NULL" :
QString( "= %1" ).arg( source()->id() )
) );
QList<playlist_ptr> plists;
while ( query.next() )
{
playlist_ptr p( new Playlist( source(), //src
query.value(6).toString(), //current rev
query.value(1).toString(), //title
query.value(2).toString(), //info
query.value(3).toString(), //creator
query.value(5).toBool(), //shared
query.value(4).toInt(), //lastmod
query.value(0).toString() //GUID
) );
plists.append( p );
}
emit done( plists );
}
void
DatabaseCommand_PlaybackHistory::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QList<Tomahawk::query_ptr> ql;
QString whereToken;
if ( !source().isNull() )
{
whereToken = QString( "WHERE source %1" ).arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) );
}
QString sql = QString(
"SELECT track, playtime, secs_played, source "
"FROM playback_log "
"%1 "
"ORDER BY playtime DESC "
"%2" ).arg( whereToken )
.arg( m_amount > 0 ? QString( "LIMIT 0, %1" ).arg( m_amount ) : QString() );
query.prepare( sql );
query.exec();
while( query.next() )
{
TomahawkSqlQuery query_track = dbi->newquery();
QString sql = QString(
"SELECT track.name, artist.name "
"FROM track, artist "
"WHERE artist.id = track.artist "
"AND track.id = %1"
).arg( query.value( 0 ).toUInt() );
query_track.prepare( sql );
query_track.exec();
if ( query_track.next() )
{
Tomahawk::query_ptr q = Tomahawk::Query::get( query_track.value( 1 ).toString(), query_track.value( 0 ).toString(), QString() );
if ( query.value( 3 ).toUInt() == 0 )
{
q->setPlayedBy( SourceList::instance()->getLocal(), query.value( 1 ).toUInt() );
}
else
{
q->setPlayedBy( SourceList::instance()->get( query.value( 3 ).toUInt() ), query.value( 1 ).toUInt() );
}
ql << q;
}
}
if ( ql.count() )
emit tracks( ql );
}
void
DatabaseCommand_AllAlbums::execForArtist( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QList<Tomahawk::album_ptr> al;
QString orderToken, sourceToken;
switch ( m_sortOrder )
{
case 0:
break;
case ModificationTime:
orderToken = "file.mtime";
}
if ( !m_collection.isNull() )
sourceToken = QString( "AND file.source %1 " ).arg( m_collection->source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( m_collection->source()->id() ) );
QString sql = QString(
"SELECT DISTINCT album.id, album.name "
"FROM file, file_join "
"LEFT OUTER JOIN album "
"ON file_join.album = album.id "
"WHERE file.id = file_join.file "
"AND file_join.artist = %1 "
"%2 "
"%3 %4 %5"
).arg( m_artist->id() )
.arg( sourceToken )
.arg( m_sortOrder > 0 ? QString( "ORDER BY %1" ).arg( orderToken ) : QString() )
.arg( m_sortDescending ? "DESC" : QString() )
.arg( m_amount > 0 ? QString( "LIMIT 0, %1" ).arg( m_amount ) : QString() );
query.prepare( sql );
query.exec();
while( query.next() )
{
unsigned int albumId = query.value( 0 ).toUInt();
QString albumName = query.value( 1 ).toString();
if ( query.value( 0 ).isNull() )
{
albumName = tr( "Unknown" );
}
Tomahawk::album_ptr album = Tomahawk::Album::get( albumId, albumName, m_artist );
al << album;
}
if ( al.count() )
emit albums( al, data() );
emit done();
}
void
DatabaseCommand_DeleteFiles::exec( DatabaseImpl* dbi )
{
qDebug() << Q_FUNC_INFO;
Q_ASSERT( !source().isNull() );
int deleted = 0;
QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();
TomahawkSqlQuery delquery = dbi->newquery();
QString lastPath;
if ( !m_dir.path().isEmpty() && source()->isLocal() )
{
qDebug() << "Deleting" << m_dir.path() << "from db for localsource" << srcid;
TomahawkSqlQuery dirquery = dbi->newquery();
dirquery.prepare( QString( "SELECT id, url FROM file WHERE source %1 AND url LIKE ?" )
.arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) ) );
delquery.prepare( QString( "DELETE FROM file WHERE source %1 AND id = ?" )
.arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) ) );
dirquery.bindValue( 0, "file://" + m_dir.canonicalPath() + "/%" );
dirquery.exec();
while ( dirquery.next() )
{
QFileInfo fi( dirquery.value( 1 ).toString().mid( 7 ) ); // remove file://
if ( fi.canonicalPath() != m_dir.canonicalPath() )
{
if ( lastPath != fi.canonicalPath() )
qDebug() << "Skipping subdir:" << fi.canonicalPath();
lastPath = fi.canonicalPath();
continue;
}
m_ids << dirquery.value( 0 ).toUInt();
m_files << dirquery.value( 1 ).toString();
}
foreach ( const QVariant& id, m_ids )
{
delquery.bindValue( 0, id.toUInt() );
if( !delquery.exec() )
{
qDebug() << "Failed to delete file:"
<< delquery.lastError().databaseText()
<< delquery.lastError().driverText()
<< delquery.boundValues();
continue;
}
deleted++;
}
void
DatabaseCommand_DeleteFiles::exec( DatabaseImpl* dbi )
{
Q_ASSERT( !source().isNull() );
int srcid = source()->isLocal() ? 0 : source()->id();
TomahawkSqlQuery delquery = dbi->newquery();
if ( m_deleteAll )
{
TomahawkSqlQuery dirquery = dbi->newquery();
dirquery.prepare( QString( "SELECT id FROM file WHERE source %1" )
.arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) ) );
dirquery.exec();
while ( dirquery.next() )
m_idList << dirquery.value( 0 ).toUInt();
}
else if ( source()->isLocal() )
{
if ( m_dir.path() != QString( "." ) )
{
tDebug() << "Deleting" << m_dir.path() << "from db for localsource" << srcid;
TomahawkSqlQuery dirquery = dbi->newquery();
QString path( "file://" + m_dir.canonicalPath() + "/%" );
dirquery.prepare( QString( "SELECT id FROM file WHERE source IS NULL AND url LIKE '%1'" ).arg( TomahawkSqlQuery::escape( path ) ) );
dirquery.exec();
while ( dirquery.next() )
{
m_ids << dirquery.value( 0 );
m_idList << dirquery.value( 0 ).toUInt();
}
}
else if ( !m_ids.isEmpty() )
{
tDebug() << Q_FUNC_INFO << "deleting given ids";
foreach ( const QVariant& id, m_ids )
m_idList << id.toUInt();
}
}
void
DatabaseCommand_LoadAllStations::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QString orderToken, sourceToken;
switch ( m_sortOrder )
{
case 0:
break;
case DatabaseCommand_LoadAllPlaylists::ModificationTime:
orderToken = "playlist.createdOn";
}
if ( !source().isNull() )
sourceToken = QString( "AND source %1 " ).arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) );
query.exec( QString( "SELECT playlist.guid as guid, title, info, creator, createdOn, lastmodified, shared, currentrevision, dynamic_playlist.pltype, dynamic_playlist.plmode "
"FROM playlist, dynamic_playlist WHERE "
"(dynplaylist = 'true' OR dynplaylist = 1) "
"AND playlist.guid = dynamic_playlist.guid "
"AND dynamic_playlist.plmode = %1 "
"AND (dynamic_playlist.autoload = 'true' OR dynamic_playlist.autoload = 1) "
"%2"
"%3 %4 %5"
)
.arg( OnDemand )
.arg( sourceToken )
.arg( m_sortOrder > 0 ? QString( "ORDER BY %1" ).arg( orderToken ) : QString() )
.arg( m_sortDescending ? "DESC" : QString() )
.arg( m_limitAmount > 0 ? QString( "LIMIT 0, %1" ).arg( m_limitAmount ) : QString() ) );
QList<dynplaylist_ptr> plists;
while ( query.next() )
{
QVariantList data = QVariantList() << query.value(7).toString() //current rev
<< query.value(1).toString() //title
<< query.value(2).toString() //info
<< query.value(3).toString() //creator
<< query.value(4).toString() //createdOn
<< query.value(8).toString() // dynamic type
<< static_cast<GeneratorMode>(query.value(9).toInt()) // dynamic mode
<< query.value(6).toBool() //shared
<< query.value(5).toInt() //lastmod
<< query.value(0).toString(); //GUID
emit stationLoaded( source(), data );
}
emit done();
}
void
DatabaseCommand_DirMtimes::execSelectPath( DatabaseImpl *dbi, const QDir& path, QMap<QString, unsigned int> &mtimes )
{
TomahawkSqlQuery query = dbi->newquery();
query.prepare( QString( "SELECT name, mtime "
"FROM dirs_scanned "
"WHERE name LIKE :prefix" ) );
query.bindValue( ":prefix", path.canonicalPath() + "%" );
query.exec();
while( query.next() )
mtimes.insert( query.value( 0 ).toString(), query.value( 1 ).toUInt() );
}
void
DatabaseCommand_UpdateSearchIndex::exec( DatabaseImpl* db )
{
db->m_fuzzyIndex->beginIndexing();
QMap< unsigned int, QMap< QString, QString > > data;
TomahawkSqlQuery q = db->newquery();
q.exec( "SELECT track.id, track.name, artist.name, artist.id FROM track, artist WHERE artist.id = track.artist" );
while ( q.next() )
{
QMap< QString, QString > track;
track.insert( "track", q.value( 1 ).toString() );
track.insert( "artist", q.value( 2 ).toString() );
track.insert( "artistid", q.value( 3 ).toString() );
data.insert( q.value( 0 ).toUInt(), track );
}
db->m_fuzzyIndex->appendFields( data );
data.clear();
q.exec( "SELECT album.id, album.name FROM album" );
while ( q.next() )
{
QMap< QString, QString > album;
album.insert( "album", q.value( 1 ).toString() );
data.insert( q.value( 0 ).toUInt(), album );
}
db->m_fuzzyIndex->appendFields( data );
qDebug() << "Building index finished.";
db->m_fuzzyIndex->endIndexing();
}
void
DatabaseCommand_SocialAction::exec( DatabaseImpl* dbi )
{
qDebug() << Q_FUNC_INFO;
Q_ASSERT( !source().isNull() );
TomahawkSqlQuery query = dbi->newquery();
QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();
if ( m_artist.isNull() || m_track.isEmpty() )
return;
int artid = dbi->artistId( m_artist, true );
if ( artid < 1 )
return;
int trkid = dbi->trackId( artid, m_track, true );
if ( trkid < 1 )
return;
// update if it already exists
TomahawkSqlQuery find = dbi->newquery();
find.prepare( QString( "SELECT id, k, v FROM social_attributes WHERE social_attributes.id = ? AND social_attributes.source %1 AND social_attributes.k = ?" ).arg( source()->isLocal() ? "IS NULL" : QString( "=%1" ).arg( source()->id() ) ) );
find.addBindValue( trkid );
find.addBindValue( m_action );
if ( find.exec() && find.next() )
{
// update
query.prepare( QString( "UPDATE social_attributes SET v = '%1', timestamp = %2 WHERE social_attributes.id = %3 AND social_attributes.source %4 AND social_attributes.k = '%5'" )
.arg( m_comment )
.arg( m_timestamp )
.arg( trkid )
.arg( source()->isLocal() ? "IS NULL" : QString( "=%1" ).arg( source()->id() ) )
.arg( m_action ) );
}
else
{
query.prepare( "INSERT INTO social_attributes(id, source, k, v, timestamp) "
"VALUES (?, ?, ?, ?, ?)" );
query.bindValue( 0, trkid );
query.bindValue( 1, srcid );
query.bindValue( 2, m_action );
query.bindValue( 3, m_comment );
query.bindValue( 4, m_timestamp );
}
query.exec();
}
DatabaseImpl::DatabaseImpl( const QString& dbname, Database* parent )
: QObject( (QObject*) parent )
, m_lastartid( 0 )
, m_lastalbid( 0 )
, m_lasttrkid( 0 )
{
QTime t;
t.start();
bool schemaUpdated = openDatabase( dbname );
tDebug( LOGVERBOSE ) << "Opened database:" << t.elapsed();
TomahawkSqlQuery query = newquery();
query.exec( "SELECT v FROM settings WHERE k='dbid'" );
if ( query.next() )
{
m_dbid = query.value( 0 ).toString();
}
else
{
m_dbid = uuid();
query.exec( QString( "INSERT INTO settings(k,v) VALUES('dbid','%1')" ).arg( m_dbid ) );
}
tLog() << "Database ID:" << m_dbid;
// make sqlite behave how we want:
query.exec( "PRAGMA auto_vacuum = FULL" );
query.exec( "PRAGMA synchronous = ON" );
query.exec( "PRAGMA foreign_keys = ON" );
//query.exec( "PRAGMA temp_store = MEMORY" );
tDebug( LOGVERBOSE ) << "Tweaked db pragmas:" << t.elapsed();
// in case of unclean shutdown last time:
query.exec( "UPDATE source SET isonline = 'false'" );
// schemaUpdated = true; // REMOVE ME
m_fuzzyIndex = new FuzzyIndex( *this, schemaUpdated );
if ( schemaUpdated )
QTimer::singleShot( 0, this, SLOT( updateIndex() ) );
tDebug( LOGVERBOSE ) << "Loaded index:" << t.elapsed();
if ( qApp->arguments().contains( "--dumpdb" ) )
{
dumpDatabase();
::exit( 0 );
}
}
void
DatabaseCommand_LoadAllPlaylists::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QString orderToken, sourceToken;
switch ( m_sortOrder )
{
case 0:
break;
case ModificationTime:
orderToken = "playlist.createdOn";
}
if ( !source().isNull() )
sourceToken = QString( "AND source %1 " ).arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) );
query.exec( QString( "SELECT guid, title, info, creator, lastmodified, shared, currentrevision, createdOn "
"FROM playlist "
"WHERE dynplaylist = 'false' "
"%1 "
"%2 %3 %4"
)
.arg( sourceToken )
.arg( m_sortOrder > 0 ? QString( "ORDER BY %1" ).arg( orderToken ) : QString() )
.arg( m_sortDescending ? "DESC" : QString() )
.arg( m_limitAmount > 0 ? QString( "LIMIT 0, %1" ).arg( m_limitAmount ) : QString() ) );
QList<playlist_ptr> plists;
while ( query.next() )
{
playlist_ptr p( new Playlist( source(), //src
query.value(6).toString(), //current rev
query.value(1).toString(), //title
query.value(2).toString(), //info
query.value(3).toString(), //creator
query.value(7).toInt(), //lastmod
query.value(5).toBool(), //shared
query.value(4).toInt(), //lastmod
query.value(0).toString() //GUID
), &QObject::deleteLater );
plists.append( p );
}
emit done( plists );
}
Tomahawk::DatabaseImpl::DatabaseImpl( const QString& dbname )
{
QTime t;
t.start();
// Signals for splash screen must be connected here
connect( this, SIGNAL( schemaUpdateStarted() ),
qApp, SLOT( onSchemaUpdateStarted() ) );
connect( this, SIGNAL( schemaUpdateStatus( QString ) ),
qApp, SLOT( onSchemaUpdateStatus( QString ) ) );
connect( this, SIGNAL( schemaUpdateDone() ),
qApp, SLOT( onSchemaUpdateDone() ) );
bool schemaUpdated = openDatabase( dbname );
tDebug( LOGVERBOSE ) << "Opened database:" << t.elapsed();
TomahawkSqlQuery query = newquery();
query.exec( "SELECT v FROM settings WHERE k='dbid'" );
if ( query.next() )
{
m_dbid = query.value( 0 ).toString();
}
else
{
m_dbid = uuid();
query.exec( QString( "INSERT INTO settings(k,v) VALUES('dbid','%1')" ).arg( m_dbid ) );
}
tLog() << "Database ID:" << m_dbid;
init();
query.exec( "PRAGMA auto_vacuum = FULL" );
query.exec( "PRAGMA synchronous = NORMAL" );
tDebug( LOGVERBOSE ) << "Tweaked db pragmas:" << t.elapsed();
// in case of unclean shutdown last time:
query.exec( "UPDATE source SET isonline = 'false'" );
query.exec( "DELETE FROM oplog WHERE source IS NULL AND singleton = 'true'" );
m_fuzzyIndex = new Tomahawk::DatabaseFuzzyIndex( this, schemaUpdated );
tDebug( LOGVERBOSE ) << "Loaded index:" << t.elapsed();
if ( qApp->arguments().contains( "--dumpdb" ) )
{
dumpDatabase();
::exit( 0 );
}
}
void
DatabaseCommand_AllAlbums::exec( DatabaseImpl* dbi )
{
Q_ASSERT( !m_collection->source().isNull() );
TomahawkSqlQuery query = dbi->newquery();
QList<Tomahawk::album_ptr> al;
QString m_orderToken;
switch ( m_sortOrder )
{
case 0:
break;
case ModificationTime:
m_orderToken = "file.mtime";
}
QString sql = QString(
"SELECT DISTINCT album.id, album.name, album.artist, artist.name "
"FROM album, file, file_join "
"LEFT OUTER JOIN artist "
"ON album.artist = artist.id "
"WHERE file.id = file_join.file "
"AND file_join.album = album.id "
"AND file.source %1 "
"%2 %3 %4"
).arg( m_collection->source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( m_collection->source()->id() ) )
.arg( m_sortOrder > 0 ? QString( "ORDER BY %1" ).arg( m_orderToken ) : QString() )
.arg( m_sortDescending ? "DESC" : QString() )
.arg( m_amount > 0 ? QString( "LIMIT 0, %1" ).arg( m_amount ) : QString() );
query.prepare( sql );
query.exec();
while( query.next() )
{
Tomahawk::artist_ptr artist = Tomahawk::Artist::get( query.value( 2 ).toUInt(), query.value( 3 ).toString() );
Tomahawk::album_ptr album = Tomahawk::Album::get( query.value( 0 ).toUInt(), query.value( 1 ).toString(), artist );
al << album;
}
if ( al.count() )
emit albums( al, m_collection );
emit done( m_collection );
}
void
DatabaseCommand_SetPlaylistRevision::exec( DatabaseImpl* lib )
{
QString currentRevision;
// get the current revision for this playlist
// this also serves to check the playlist exists.
TomahawkSqlQuery chkq = lib->newquery();
chkq.prepare( "SELECT currentrevision FROM playlist WHERE guid = ?" );
chkq.addBindValue( m_playlistguid );
if ( chkq.exec() && chkq.next() )
{
currentRevision = chkq.value( 0 ).toString();
tDebug() << Q_FUNC_INFO << "pl guid" << m_playlistguid << "- curr rev" << currentRevision << source()->friendlyName() << source()->id();
}
else
{
tDebug() << "ERROR: No such playlist:" << m_playlistguid << currentRevision << source()->friendlyName() << source()->id();
// Q_ASSERT_X( false, "DatabaseCommand_SetPlaylistRevision::exec", "No such playlist, WTF?" );
m_failed = true;
return;
}
QVariantList vlist = m_orderedguids;
const QByteArray entries = TomahawkUtils::toJson( vlist );
// add any new items:
TomahawkSqlQuery adde = lib->newquery();
if ( m_localOnly )
{
QString sql = "UPDATE playlist_item SET result_hint = ? WHERE guid = ?";
adde.prepare( sql );
foreach( const plentry_ptr& e, m_entries )
{
if ( !e->isValid() )
continue;
if ( !e->query()->numResults() )
continue;
adde.bindValue( 0, e->resultHint() );
adde.bindValue( 1, e->guid() );
adde.exec();
}
return;
}
else if ( m_metadataUpdate )
void
DatabaseCommand_LogPlayback::exec( DatabaseImpl* dbi )
{
Q_ASSERT( !source().isNull() );
if ( m_action != Finished )
return;
if ( m_secsPlayed < FINISHED_THRESHOLD && m_trackDuration > 0 )
return;
if ( m_artist.isEmpty() || m_track.isEmpty() )
return;
QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();
TomahawkSqlQuery query = dbi->newquery();
if ( !m_query.isNull() )
{
query.prepare( QString( "SELECT * FROM playback_log WHERE source %1 AND playtime = %2" ).arg( srcid.isNull() ? "IS NULL" : srcid.toString() ).arg( m_playtime ) );
query.exec();
if ( query.next() )
{
tDebug() << "Ignoring dupe playback log for source" << srcid << "with timestamp" << m_playtime;
return;
}
}
// tDebug() << "Logging playback of" << m_artist << "-" << m_track << "for source" << srcid << "- timestamp:" << m_playtime;
query.prepare( "INSERT INTO playback_log(source, track, playtime, secs_played) VALUES (?, ?, ?, ?)" );
query.bindValue( 0, srcid );
// If there's no artist, because it's a resolver result with bad metadata for example, don't save it
int artid = dbi->artistId( m_artist, true );
if( artid < 1 )
return;
int trkid = dbi->trackId( artid, m_track, true );
if( trkid < 1 )
return;
query.bindValue( 1, trkid );
query.bindValue( 2, m_playtime );
query.bindValue( 3, m_secsPlayed );
query.exec();
}
void
DatabaseCommand_SetPlaylistRevision::exec( DatabaseImpl* lib )
{
using namespace Tomahawk;
// get the current revision for this playlist
// this also serves to check the playlist exists.
TomahawkSqlQuery chkq = lib->newquery();
chkq.prepare( "SELECT currentrevision FROM playlist WHERE guid = ?" );
chkq.addBindValue( m_playlistguid );
if( chkq.exec() && chkq.next() )
{
m_currentRevision = chkq.value( 0 ).toString();
qDebug() << Q_FUNC_INFO << "pl guid" << m_playlistguid << " curr rev" << m_currentRevision;
}
else
{
throw "No such playlist, WTF?";
return;
}
QVariantList vlist = m_orderedguids;
QJson::Serializer ser;
const QByteArray entries = ser.serialize( vlist );
// add any new items:
TomahawkSqlQuery adde = lib->newquery();
if ( m_localOnly )
{
QString sql = "UPDATE playlist_item SET result_hint = ? WHERE guid = ?";
adde.prepare( sql );
foreach( const plentry_ptr& e, m_entries )
{
if ( e->query()->results().isEmpty() )
continue;
adde.bindValue( 0, e->query()->results().first()->url() );
adde.bindValue( 1, e->guid() );
adde.exec();
}
return;
}
else
{
void
Tomahawk::DatabaseCommand_LoadDynamicPlaylist::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
query.exec( QString( "SELECT playlist.guid as guid, title, info, creator, createdOn, lastmodified, shared, currentrevision, dynamic_playlist.pltype, dynamic_playlist.plmode "
"FROM playlist, dynamic_playlist WHERE "
"source %1 "
"AND (dynplaylist = 'true' OR dynplaylist = 1)"
"AND playlist.guid = dynamic_playlist.guid "
"AND playlist.guid = '%2'" )
.arg( source()->isLocal() ? "IS NULL" : QString( "=%1" ).arg( source()->id() ) ).arg( m_plid ) );
QList<dynplaylist_ptr> plists;
if( query.next() )
{
dynplaylist_ptr p( new DynamicPlaylist( source(),
query.value(7).toString(), //current rev
query.value(1).toString(), //title
query.value(2).toString(), //info
query.value(3).toString(), //creator
query.value(4).toUInt(), //createdOn
query.value(8).toString(), // dynamic type
static_cast<GeneratorMode>(query.value(9).toInt()), // dynamic mode
query.value(6).toBool(), //shared
query.value(5).toInt(), //lastmod
query.value(0).toString() ) ); //GUID
p->setWeakSelf( p.toWeakRef() );
/*
tLog() << "Loaded individual dynamic playlist:" << query.value(7).toString() //current rev
<< query.value(1).toString() //title
<< query.value(2).toString() //info
<< query.value(3).toString() //creator
<< query.value(4).toString() //createdOn
<< query.value(8).toString() // dynamic type
<< static_cast<GeneratorMode>(query.value(9).toInt()) // dynamic mode
<< query.value(6).toBool() //shared
<< query.value(5).toInt() //lastmod
<< query.value(0).toString(); //GUID */
emit dynamicPlaylistLoaded( p );
}
emit done();
}
void
DatabaseCommand_LoadAllSources::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
query.exec( QString( "SELECT id, name, friendlyname "
"FROM source" ) );
QList<source_ptr> sources;
while ( query.next() )
{
source_ptr src( new Source( query.value( 0 ).toUInt(), query.value( 1 ).toString() ) );
src->setDbFriendlyName( query.value( 2 ).toString() );
sources << src;
}
emit done( sources );
}
void
DatabaseCommand_UpdateSearchIndex::indexTable( DatabaseImpl* db, const QString& table )
{
qDebug() << Q_FUNC_INFO;
TomahawkSqlQuery query = db->newquery();
qDebug() << "Building index for" << table;
query.exec( QString( "SELECT id, name FROM %1" ).arg( table ) );
QMap< unsigned int, QString > fields;
while ( query.next() )
{
fields.insert( query.value( 0 ).toUInt(), query.value( 1 ).toString() );
}
db->m_fuzzyIndex->appendFields( table, fields );
qDebug() << "Building index for" << table << "finished.";
}
void
DatabaseCommand_AllArtists::exec( DatabaseImpl* dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QList<Tomahawk::artist_ptr> al;
QString orderToken, sourceToken;
switch ( m_sortOrder )
{
case 0:
break;
case ModificationTime:
orderToken = "file.mtime";
}
if ( !m_collection.isNull() )
sourceToken = QString( "AND file.source %1 " ).arg( m_collection->source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( m_collection->source()->id() ) );
QString sql = QString(
"SELECT DISTINCT artist.id, artist.name "
"FROM artist, file, file_join "
"WHERE file.id = file_join.file "
"AND file_join.artist = artist.id "
"%1 %2 %3 %4"
).arg( sourceToken )
.arg( m_sortOrder > 0 ? QString( "ORDER BY %1" ).arg( orderToken ) : QString() )
.arg( m_sortDescending ? "DESC" : QString() )
.arg( m_amount > 0 ? QString( "LIMIT 0, %1" ).arg( m_amount ) : QString() );
query.prepare( sql );
query.exec();
while( query.next() )
{
Tomahawk::artist_ptr artist = Tomahawk::Artist::get( query.value( 0 ).toUInt(), query.value( 1 ).toString() );
al << artist;
}
if ( al.count() )
emit artists( al );
emit done();
}
void
DatabaseCommand_LoadSocialActions::exec( DatabaseImpl* dbi )
{
qDebug() << Q_FUNC_INFO;
Q_ASSERT( !source().isNull() );
TomahawkSqlQuery query = dbi->newquery();
QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();
int artid = dbi->artistId( m_artist, false );
if( artid < 1 )
return;
int trkid = dbi->trackId( artid, m_track, false );
if( trkid < 1 )
return;
QString whereToken;
whereToken = QString( "WHERE id IS %1" ).arg( trkid );
QString sql = QString(
"SELECT k, v, timestamp, source "
"FROM social_attributes %1 "
"ORDER BY timestamp ASC" ).arg( whereToken );
query.prepare( sql );
query.exec();
QList< Tomahawk::SocialAction > allSocialActions;
while ( query.next() )
{
Tomahawk::SocialAction action;
action.action = query.value( 0 ); // action
action.value = query.value( 1 ); // comment
action.timestamp = query.value( 2 ); // timestamp
action.source = query.value( 3 ); // source
allSocialActions.append( action );
}
m_query->setAllSocialActions( allSocialActions );
}
void
DatabaseCommand_NetworkCharts::exec( DatabaseImpl * dbi )
{
TomahawkSqlQuery query = dbi->newquery();
QString limit;
if ( m_amount > 0 )
{
limit = QString( "LIMIT 0, %1" ).arg( m_amount );
}
QString timespan;
if ( m_from.isValid() && m_to.isValid() )
{
timespan = QString(
" AND playback_log.playtime >= %1 AND playback_log.playtime <= %2 "
).arg( m_from.toTime_t() ).arg( m_to.toTime_t() );
}
QString sql = QString(
"SELECT COUNT(*) as counter, track.name, artist.name "
" FROM playback_log, track, artist "
" WHERE track.id = playback_log.track AND artist.id = track.artist "
" AND playback_log.source IS NOT NULL %1 " // exclude self
" GROUP BY playback_log.track "
" ORDER BY counter DESC "
" %2"
).arg( timespan ).arg( limit );
query.prepare( sql );
query.exec();
QList<Tomahawk::track_ptr> tracks;
while ( query.next() )
{
Tomahawk::track_ptr track = Tomahawk::Track::get( query.value( 2 ).toString(), query.value( 1 ).toString() );
if ( !track )
continue;
tracks << track;
}
emit done( tracks );
}
void
DatabaseCommand_DirMtimes::execSelect( DatabaseImpl* dbi )
{
QMap<QString,unsigned int> mtimes;
TomahawkSqlQuery query = dbi->newquery();
if( m_prefix.isEmpty() )
query.exec( "SELECT name, mtime FROM dirs_scanned" );
else
{
query.prepare( QString( "SELECT name, mtime "
"FROM dirs_scanned "
"WHERE name LIKE '%1%'" ).arg( m_prefix.replace( '\'',"''" ) ) );
query.exec();
}
while( query.next() )
{
mtimes.insert( query.value( 0 ).toString(), query.value( 1 ).toUInt() );
}
emit done( mtimes );
}
void DatabaseCommand_ClientAuthValid::exec( DatabaseImpl* lib )
{
TomahawkSqlQuery q = lib->newquery();
q.prepare( "SELECT name FROM http_client_auth WHERE token = ?" );
q.addBindValue( m_clientToken );
if ( q.exec() )
{
if ( q.next() )
{
QString name = q.value( 0 ).toString();
emit authValid( m_clientToken, name, true );
}
else
{
emit authValid( m_clientToken, QString(), false );
}
}
else
{
qWarning() << "Failed to query http auth table for client:" << m_clientToken;
}
}