C++ TomahawkSqlQuery::bindValue方法代码示例

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

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

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