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(); }
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_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 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_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_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(); }
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 DatabaseImpl::init() { m_lastartid = m_lastalbid = m_lasttrkid = 0; TomahawkSqlQuery query = newquery(); // make sqlite behave how we want: query.exec( "PRAGMA foreign_keys = ON" ); }
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::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_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(); }
DatabaseImpl::DatabaseImpl( const QString& dbname ) { 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; init(); query.exec( "PRAGMA auto_vacuum = FULL" ); query.exec( "PRAGMA synchronous = NORMAL" ); tDebug( LOGVERBOSE ) << "Tweaked db pragmas:" << t.elapsed(); // in case of unclean shutdown last time: query.exec( "UPDATE source SET isonline = 'false'" ); m_fuzzyIndex = new FuzzyIndex( this, schemaUpdated ); tDebug( LOGVERBOSE ) << "Loaded index:" << t.elapsed(); if ( qApp->arguments().contains( "--dumpdb" ) ) { dumpDatabase(); ::exit( 0 ); } }