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++;
        }
// this should take a const command, need to check/make json stuff mutable for some objs tho maybe.
void
DatabaseWorker::logOp( DatabaseCommandLoggable* command )
{
    TomahawkSqlQuery oplogquery = Database::instance()->impl()->newquery();
    qDebug() << "INSERTING INTO OPLOG:" << command->source()->id() << command->guid() << command->commandname();
    oplogquery.prepare( "INSERT INTO oplog(source, guid, command, singleton, compressed, json) "
                        "VALUES(?, ?, ?, ?, ?, ?)" );

    QVariantMap variant = QJson::QObjectHelper::qobject2qvariant( command );
    QByteArray ba = m_serializer.serialize( variant );

//     qDebug() << "OP JSON:" << ba.isNull() << ba << "from:" << variant; // debug

    bool compressed = false;
    if ( ba.length() >= 512 )
    {
        // We need to compress this in this thread, since inserting into the log
        // has to happen as part of the same transaction as the dbcmd.
        // (we are in a worker thread for RW dbcmds anyway, so it's ok)
        //qDebug() << "Compressing DB OP JSON, uncompressed size:" << ba.length();
        ba = qCompress( ba, 9 );
        compressed = true;
        //qDebug() << "Compressed DB OP JSON size:" << ba.length();
    }

    if ( command->singletonCmd() )
    {
        tDebug() << "Singleton command, deleting previous oplog commands";

        TomahawkSqlQuery oplogdelquery = Database::instance()->impl()->newquery();
        oplogdelquery.prepare( QString( "DELETE FROM oplog WHERE source %1 AND singleton = 'true' AND command = ?" )
                                  .arg( command->source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( command->source()->id() ) ) );

        oplogdelquery.bindValue( 0, command->commandname() );
        oplogdelquery.exec();
    }

    tDebug() << "Saving to oplog:" << command->commandname()
             << "bytes:" << ba.length()
             << "guid:" << command->guid();

    oplogquery.bindValue( 0, command->source()->isLocal() ?
                          QVariant(QVariant::Int) : command->source()->id() );
    oplogquery.bindValue( 1, command->guid() );
    oplogquery.bindValue( 2, command->commandname() );
    oplogquery.bindValue( 3, command->singletonCmd() );
    oplogquery.bindValue( 4, compressed );
    oplogquery.bindValue( 5, ba );
    if ( !oplogquery.exec() )
    {
        tLog() << "Error saving to oplog";
        throw "Failed to save to oplog";
    }
}
void
DatabaseCommand_SetTrackAttributes::exec( DatabaseImpl* dbi )
{
    TomahawkSqlQuery checkquery = dbi->newquery();
    TomahawkSqlQuery delquery = dbi->newquery();
    TomahawkSqlQuery insertquery = dbi->newquery();

    QString k;
    switch ( m_type )
    {
    case EchonestCatalogId:
        k = "echonestcatalogid";
        break;
    }

    if ( m_delete && m_tracks.isEmpty() )
    {
        //delete all
        TomahawkSqlQuery delAll = dbi->newquery();
        delAll.prepare( "DELETE FROM track_attributes WHERE k = ?" );
        delAll.bindValue( 0, k );
        delAll.exec();
        return;
    }

    checkquery.prepare( "SELECT id, sortname FROM track WHERE id = ?" );
    delquery.prepare( "DELETE FROM track_attributes WHERE id = ? AND k = ?" );
    insertquery.prepare( "INSERT INTO track_attributes ( id, k, v ) VALUES( ?, ?, ? )" );

    QPair< QID, QString > track;
    foreach ( track, m_tracks )
    {
        checkquery.bindValue( 0, track.first );
        if ( !checkquery.exec() )
        {
            tLog() << "No track in track table for set track attribute command...aborting:" << track.first;
            continue;
        }

        delquery.bindValue( 0, track.first );
        delquery.bindValue( 1, k );
        delquery.exec();

        if ( m_delete )
            continue; // stop at deleting, don't insert

        insertquery.bindValue( 0, track.first );
        insertquery.bindValue( 1, k );
        insertquery.bindValue( 2, track.second );
        if ( !insertquery.exec() )
            tLog() << "Failed to insert track attribute:" << k << track.first << track.second;

    }
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_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();
}
void
DatabaseCommand_ModifyInboxEntry::exec( DatabaseImpl* dbi )
{
    TomahawkSqlQuery query = dbi->newquery();

    Q_ASSERT( !m_query.isNull() );

    if ( m_query->queryTrack()->track().isEmpty() || m_query->queryTrack()->artist().isEmpty() )
    {
        emit done();
        return;
    }

    query.prepare(
                "UPDATE social_attributes "
                "SET v = ? "
                "WHERE social_attributes.k = ? AND social_attributes.id = ( "
                    "SELECT id FROM track "
                    "WHERE track.name = ? AND track.artist = ( "
                        "SELECT id FROM artist WHERE artist.name = ? "
                    ") "
                ")" );
    query.addBindValue( m_newValue );
    query.addBindValue( "Inbox" );
    query.addBindValue( m_query->queryTrack()->track() );
    query.addBindValue( m_query->queryTrack()->artist() );

    query.exec();

    emit done();
}
void
DatabaseCommand_CreateDynamicPlaylist::exec( DatabaseImpl* lib )
{
    qDebug() << Q_FUNC_INFO;
    Q_ASSERT( !( m_playlist.isNull() && m_v.isNull() ) );
    Q_ASSERT( !source().isNull() );

    DatabaseCommand_CreatePlaylist::createPlaylist( lib, true );
    qDebug() << "Created normal playlist, now creating additional dynamic info!";

    qDebug() << "Create dynamic execing!" << m_playlist << m_v;
    TomahawkSqlQuery cre = lib->newquery();

    cre.prepare( "INSERT INTO dynamic_playlist( guid, pltype, plmode, autoload ) "
                 "VALUES( ?, ?, ?, ? )" );

    if( m_playlist.isNull() ) {
        QVariantMap m = m_v.toMap();
        cre.addBindValue( m.value( "guid" ) );
        cre.addBindValue( m.value( "type" ) );
        cre.addBindValue( m.value( "mode" ) );
    } else {
        cre.addBindValue( m_playlist->guid() );
        cre.addBindValue( m_playlist->type() );
        cre.addBindValue( m_playlist->mode() );
    }
    cre.addBindValue( m_autoLoad );
    cre.exec();
}
void
DatabaseCommand_LogPlayback::exec( DatabaseImpl* dbi )
{
    Q_ASSERT( !source().isNull() );

    if ( m_action != Finished )
        return;
    if ( m_secsPlayed < FINISHED_THRESHOLD )
        return;

    TomahawkSqlQuery query = dbi->newquery();
    query.prepare( "INSERT INTO playback_log(source, track, playtime, secs_played) "
                   "VALUES (?, ?, ?, ?)" );

    QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();
    qDebug() << "Logging playback of" << m_artist << "-" << m_track << "for source" << srcid;
    query.bindValue( 0, srcid );

    // If there's no artist, becuase it's a resolver result with bad metadata for example, don't save it
    bool autoCreate = m_artist.isEmpty();
    int artid = dbi->artistId( m_artist, autoCreate );
    if( artid < 1 )
        return;

    autoCreate = true; // artistId overwrites autoCreate (reference)
    int trkid = dbi->trackId( artid, m_track, autoCreate );
    if( trkid < 1 )
        return;

    query.bindValue( 1, trkid );
    query.bindValue( 2, m_playtime );
    query.bindValue( 3, m_secsPlayed );

    query.exec();
}
void
DatabaseCommand_LogPlayback::exec( DatabaseImpl* dbi )
{
    qDebug() << Q_FUNC_INFO;
    Q_ASSERT( !source().isNull() );

    if ( m_action != Finished )
        return;

    TomahawkSqlQuery query = dbi->newquery();
    query.prepare( "INSERT INTO playback_log(source, track, playtime, secs_played) "
                   "VALUES (?, ?, ?, ?)" );

    QVariant srcid = source()->isLocal() ? QVariant( QVariant::Int ) : source()->id();

    qDebug() << "Logging playback of" << m_artist << "-" << m_track << "for source" << srcid;

    query.bindValue( 0, srcid );

    bool isnew;
    int artid = dbi->artistId( m_artist, isnew );
    if( artid < 1 )
        return;

    int trkid = dbi->trackId( artid, m_track, isnew );
    if( trkid < 1 )
        return;

    query.bindValue( 1, trkid );
    query.bindValue( 2, m_playtime );
    query.bindValue( 3, m_secsPlayed );

    query.exec();
}
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_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_LoadDynamicPlaylistEntries::exec( DatabaseImpl* dbi )
{
//    qDebug() << "Loading dynamic playlist guid" << guid();
    // load the entries first
    generateEntries( dbi );

    // now load the controls etc

    TomahawkSqlQuery controlsQuery = dbi->newquery();
    controlsQuery.prepare("SELECT playlist_revision.playlist, controls, plmode, pltype "
                          "FROM dynamic_playlist_revision, playlist_revision "
                          "WHERE dynamic_playlist_revision.guid = ? AND playlist_revision.guid = dynamic_playlist_revision.guid");
    controlsQuery.addBindValue( revisionGuid() );
    controlsQuery.exec();

    QString type;
    GeneratorMode mode;

    QList< QVariantMap > controls;
    QString playlist_guid;
//    qDebug() << "Loading controls..." << revisionGuid();
//    qDebug() << "SELECT playlist_revision.playlist, controls, plmode, pltype "
//    "FROM dynamic_playlist_revision, playlist_revision "
//    "WHERE dynamic_playlist_revision.guid = "<< revisionGuid() << " AND playlist_revision.guid = dynamic_playlist_revision.guid";

    if( controlsQuery.first() )
    {
        playlist_guid = controlsQuery.value( 0 ).toString();
        QJson::Parser parser;
        bool ok;
        QVariant v = parser.parse( controlsQuery.value(1).toByteArray(), &ok );
        Q_ASSERT( ok && v.type() == QVariant::List ); //TODO


        type = controlsQuery.value( 3 ).toString();
        mode = static_cast<GeneratorMode>( controlsQuery.value( 2 ).toInt() );

        QStringList controlIds = v.toStringList();
//        qDebug() << "Got controls in dynamic playlist, loading:" << controlIds << controlsQuery.value(1);
        foreach( const QString& controlId, controlIds )
        {
            TomahawkSqlQuery controlQuery = dbi->newquery();
            controlQuery.prepare( "SELECT selectedType, match, input "
                                  "FROM dynamic_playlist_controls "
                                  "WHERE id = :id" );
            controlQuery.bindValue( ":id", controlId );
            controlQuery.exec();
            if( controlQuery.next() )
            {
                QVariantMap c;
                c[ "type" ] = type;
                c[ "id" ] = controlId;
                c[ "selectedType" ] = controlQuery.value( 0 ).toString();
                c[ "match" ] = controlQuery.value( 1 ).toString();
                c[ "input" ] = controlQuery.value( 2 ).toString();
                controls << c;
            }
        }
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
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 )
{
    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_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_DeletePlaylist::exec( DatabaseImpl* lib )
{
    qDebug() << Q_FUNC_INFO;

    TomahawkSqlQuery cre = lib->newquery();

    QString sql = QString( "DELETE FROM playlist WHERE guid = :id AND source %1" )
                  .arg( source()->isLocal() ? "IS NULL" : QString("= %1").arg( source()->id() ) );
    cre.prepare( sql );
    cre.bindValue( ":id", m_playlistguid );

    cre.exec();
}
void DatabaseCommand_AddClientAuth::exec(DatabaseImpl* lib)
{
    TomahawkSqlQuery q = lib->newquery();
    q.prepare( "INSERT INTO http_client_auth (token, website, name, ua, mtime, permissions) VALUES (?, ?, ?, ?, ?, ?)" );
    q.addBindValue( m_clientToken );
    q.addBindValue( m_website );
    q.addBindValue( m_name );
    q.addBindValue( m_userAgent );
    q.addBindValue( 0 );
    q.addBindValue( "*" );
    
    if( !q.exec() ) {
        qWarning() << "Failed to insert http client into auth table!";
    }
}
void
DatabaseCommand_RenamePlaylist::exec( DatabaseImpl* lib )
{
    TomahawkSqlQuery cre = lib->newquery();

    QString sql = QString( "UPDATE playlist SET title = :title WHERE guid = :id AND source %1" )
                     .arg( source()->isLocal() ? "IS NULL" : QString( "= %1" ).arg( source()->id() ) );

    cre.prepare( sql );
    cre.bindValue( ":id", m_playlistguid );
    cre.bindValue( ":title", m_playlistTitle );

    qDebug() << Q_FUNC_INFO << m_playlistTitle << m_playlistguid;

    cre.exec();
}
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_DirMtimes::execUpdate( DatabaseImpl* dbi )
{
    qDebug() << "Saving mtimes...";
    TomahawkSqlQuery query = dbi->newquery();
    query.exec( "DELETE FROM dirs_scanned" );
    query.prepare( "INSERT INTO dirs_scanned(name, mtime) VALUES(?, ?)" );

    foreach( const QString& k, m_tosave.keys() )
    {
        query.bindValue( 0, k );
        query.bindValue( 1, m_tosave.value( k ) );
        query.exec();
    }

    qDebug() << "Saved mtimes for" << m_tosave.size() << "dirs.";
}
void
DatabaseCommand_CreatePlaylist::createPlaylist( DatabaseImpl* lib, bool dynamic)
{
    Q_ASSERT( !( m_playlist.isNull() && m_v.isNull() ) );
    Q_ASSERT( !source().isNull() );

    uint now = 0;
    if ( m_playlist.isNull() )
    {
        now = m_v.toMap()[ "createdon" ].toUInt();
    }
    else
    {
        now = QDateTime::currentDateTime().toTime_t();
        m_playlist->setCreatedOn( now );
    }

    TomahawkSqlQuery cre = lib->newquery();
    cre.prepare( "INSERT INTO playlist( guid, source, shared, title, info, creator, lastmodified, dynplaylist, createdOn ) "
                 "VALUES( :guid, :source, :shared, :title, :info, :creator, :lastmodified, :dynplaylist, :createdOn )" );

    cre.bindValue( ":source", source()->isLocal() ? QVariant(QVariant::Int) : source()->id() );
    cre.bindValue( ":dynplaylist", dynamic );
    cre.bindValue( ":createdOn", now );
    if ( !m_playlist.isNull() )
    {
        cre.bindValue( ":guid", m_playlist->guid() );
        cre.bindValue( ":shared", m_playlist->shared() );
        cre.bindValue( ":title", m_playlist->title() );
        cre.bindValue( ":info", m_playlist->info() );
        cre.bindValue( ":creator", m_playlist->creator() );
        cre.bindValue( ":lastmodified", m_playlist->lastmodified() );
    }
    else
    {
        QVariantMap m = m_v.toMap();
        cre.bindValue( ":guid", m.value( "guid" ) );
        cre.bindValue( ":shared", m.value( "shared" ) );
        cre.bindValue( ":title", m.value( "title" ) );
        cre.bindValue( ":info", m.value( "info" ) );
        cre.bindValue( ":creator", m.value( "creator" ) );
        cre.bindValue( ":lastmodified", m.value( "lastmodified", 0 ) );
    }

    cre.exec();
}
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_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_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_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;
    }
}
void DatabaseCommand_TrackAttributes::exec( DatabaseImpl* lib )
{
    TomahawkSqlQuery query = lib->newquery();

    QString k;
    switch ( m_type )
    {
        case DatabaseCommand_SetTrackAttributes::EchonestCatalogId:
            k = "echonestcatalogid";
            break;
    }

    PairList results;
    if ( !m_ids.isEmpty() )
    {
        foreach ( const QID id, m_ids )
        {
            query.prepare( "SELECT v FROM track_attributes WHERE id = ? AND k = ?" );
            query.bindValue( 0, id );
            query.bindValue( 1, k );
            if ( query.exec() )
                results.append( QPair< QID, QString >( id, query.value( 0 ).toString() ) );
        }