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

void
DatabaseCommand_SetCollectionAttributes::exec( DatabaseImpl *lib )
{
    TomahawkSqlQuery query = lib->newquery();

    QString sourceStr;
    if ( source().isNull() )
        setSource( SourceList::instance()->getLocal() );

    if ( source().isNull() || source()->isLocal() )
        sourceStr = "NULL";
    else
        sourceStr = QString( "%1" ).arg( source()->id() );

    QString typeStr;
    if ( m_type == EchonestSongCatalog )
        typeStr = "echonest_song";
    else if ( m_type == EchonestArtistCatalog )
        typeStr = "echonest_artist";

    TomahawkSqlQuery delQuery = lib->newquery();
    delQuery.exec( QString( "DELETE FROM collection_attributes WHERE id %1" ).arg( source()->isLocal() ? QString("IS NULL") : QString( "= %1" ).arg( source()->id() )));

    if ( m_delete )
        return;

    QString queryStr = QString( "INSERT INTO collection_attributes ( id, k, v ) VALUES( %1, \"%2\", \"%3\" )" ).arg( sourceStr ).arg( typeStr ).arg( QString::fromUtf8( m_id ) );
    qDebug() << "Doing query:" << queryStr;
    query.exec( queryStr );
}

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

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

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

    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_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_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_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
DatabaseCommand_SourceOffline::exec( DatabaseImpl* lib )
{
    TomahawkSqlQuery q = lib->newquery();
    q.exec( QString( "UPDATE source SET isonline = 'false' WHERE id = %1" )
            .arg( m_id ) );
}

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