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

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