PageRenderTime 73ms CodeModel.GetById 10ms app.highlight 56ms RepoModel.GetById 1ms app.codeStats 1ms

/src/libtomahawk/database/DatabaseImpl.cpp

Relevant Search: With Applications for Solr and Elasticsearch

For more in depth reading about search, ranking and generally everything you could ever want to know about how lucene, elasticsearch or solr work under the hood I highly suggest this book. Easily one of the most interesting technical books I have read in a long time. If you are tasked with solving search relevance problems even if not in Solr or Elasticsearch it should be your first reference. Amazon Affiliate Link
http://github.com/tomahawk-player/tomahawk
C++ | 816 lines | 630 code | 134 blank | 52 comment | 74 complexity | c6eacbc9b27a42ff75fb6b8c4ec36a13 MD5 | raw file
  1/* === This file is part of Tomahawk Player - <http://tomahawk-player.org> ===
  2 *
  3 *   Copyright 2010-2011, Christian Muehlhaeuser <muesli@tomahawk-player.org>
  4 *   Copyright 2010-2011, Leo Franchi <lfranchi@kde.org>
  5 *   Copyright 2010-2011, Jeff Mitchell <jeff@tomahawk-player.org>
  6 *   Copyright 2014,      Teo Mrnjavac <teo@kde.org>
  7 *
  8 *   Tomahawk is free software: you can redistribute it and/or modify
  9 *   it under the terms of the GNU General Public License as published by
 10 *   the Free Software Foundation, either version 3 of the License, or
 11 *   (at your option) any later version.
 12 *
 13 *   Tomahawk is distributed in the hope that it will be useful,
 14 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 15 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 16 *   GNU General Public License for more details.
 17 *
 18 *   You should have received a copy of the GNU General Public License
 19 *   along with Tomahawk. If not, see <http://www.gnu.org/licenses/>.
 20 */
 21
 22#include "DatabaseImpl.h"
 23
 24#include "database/Database.h"
 25#include "utils/Logger.h"
 26#include "utils/ResultUrlChecker.h"
 27#include "utils/TomahawkUtils.h"
 28
 29#include "Album.h"
 30#include "Artist.h"
 31#include "fuzzyindex/DatabaseFuzzyIndex.h"
 32#include "PlaylistEntry.h"
 33#include "Result.h"
 34#include "SourceList.h"
 35#include "Track.h"
 36
 37#include <QtAlgorithms>
 38#include <QCoreApplication>
 39#include <QFile>
 40#include <QRegExp>
 41#include <QStringList>
 42#include <QTime>
 43#include <QTimer>
 44
 45/* !!!! You need to manually generate Schema.sql.h when the schema changes:
 46    cd src/libtomahawk/database
 47   ./gen_schema.h.sh ./Schema.sql tomahawk > Schema.sql.h
 48*/
 49#include "Schema.sql.h"
 50
 51#define CURRENT_SCHEMA_VERSION 31
 52
 53Tomahawk::DatabaseImpl::DatabaseImpl( const QString& dbname )
 54{
 55    QTime t;
 56    t.start();
 57
 58    // Signals for splash screen must be connected here
 59    connect( this, SIGNAL( schemaUpdateStarted() ),
 60             qApp, SLOT( onSchemaUpdateStarted() ) );
 61    connect( this, SIGNAL( schemaUpdateStatus( QString ) ),
 62             qApp, SLOT( onSchemaUpdateStatus( QString ) ) );
 63    connect( this, SIGNAL( schemaUpdateDone() ),
 64             qApp, SLOT( onSchemaUpdateDone() ) );
 65
 66    bool schemaUpdated = openDatabase( dbname );
 67    tDebug( LOGVERBOSE ) << "Opened database:" << t.elapsed();
 68
 69    TomahawkSqlQuery query = newquery();
 70    query.exec( "SELECT v FROM settings WHERE k='dbid'" );
 71    if ( query.next() )
 72    {
 73        m_dbid = query.value( 0 ).toString();
 74    }
 75    else
 76    {
 77        m_dbid = uuid();
 78        query.exec( QString( "INSERT INTO settings(k,v) VALUES('dbid','%1')" ).arg( m_dbid ) );
 79    }
 80
 81    tLog() << "Database ID:" << m_dbid;
 82    init();
 83    query.exec( "PRAGMA auto_vacuum = FULL" );
 84    query.exec( "PRAGMA synchronous = NORMAL" );
 85
 86    tDebug( LOGVERBOSE ) << "Tweaked db pragmas:" << t.elapsed();
 87
 88    // in case of unclean shutdown last time:
 89    query.exec( "UPDATE source SET isonline = 'false'" );
 90    query.exec( "DELETE FROM oplog WHERE source IS NULL AND singleton = 'true'" );
 91
 92    m_fuzzyIndex = new Tomahawk::DatabaseFuzzyIndex( this, schemaUpdated );
 93
 94    tDebug( LOGVERBOSE ) << "Loaded index:" << t.elapsed();
 95    if ( qApp->arguments().contains( "--dumpdb" ) )
 96    {
 97        dumpDatabase();
 98        ::exit( 0 );
 99    }
100}
101
102
103Tomahawk::DatabaseImpl::DatabaseImpl( const QString& dbname, bool internal )
104{
105    Q_UNUSED( internal );
106    openDatabase( dbname, false );
107    init();
108}
109
110
111void
112Tomahawk::DatabaseImpl::init()
113{
114    m_lastartid = m_lastalbid = m_lasttrkid = 0;
115
116    TomahawkSqlQuery query = newquery();
117
118     // make sqlite behave how we want:
119    query.exec( "PRAGMA foreign_keys = ON" );
120}
121
122
123Tomahawk::DatabaseImpl::~DatabaseImpl()
124{
125    tDebug() << "Shutting down database connection.";
126
127/*
128#ifdef TOMAHAWK_QUERY_ANALYZE
129    TomahawkSqlQuery q = newquery();
130
131    q.exec( "ANALYZE" );
132    q.exec( "SELECT * FROM sqlite_stat1" );
133    while ( q.next() )
134    {
135        tLog( LOGSQL ) << q.value( 0 ).toString() << q.value( 1 ).toString() << q.value( 2 ).toString();
136    }
137
138#endif
139*/
140}
141
142
143TomahawkSqlQuery
144Tomahawk::DatabaseImpl::newquery()
145{
146    QMutexLocker lock( &m_mutex );
147    return TomahawkSqlQuery( m_db );
148}
149
150
151QSqlDatabase&
152Tomahawk::DatabaseImpl::database()
153{
154    QMutexLocker lock( &m_mutex );
155    return m_db;
156}
157
158
159Tomahawk::DatabaseImpl*
160Tomahawk::DatabaseImpl::clone() const
161{
162    QMutexLocker lock( &m_mutex );
163
164    DatabaseImpl* impl = new DatabaseImpl( m_db.databaseName(), true );
165    impl->setDatabaseID( m_dbid );
166    impl->setFuzzyIndex( m_fuzzyIndex );
167    return impl;
168}
169
170
171void
172Tomahawk::DatabaseImpl::dumpDatabase()
173{
174    QFile dump( "dbdump.txt" );
175    if ( !dump.open( QIODevice::WriteOnly | QIODevice::Text ) )
176    {
177        tDebug() << "Couldn't open dbdump.txt for writing!";
178        Q_ASSERT( false );
179    }
180    else
181    {
182        QTextStream dumpout( &dump );
183        TomahawkSqlQuery query = newquery();
184
185        query.exec( "SELECT * FROM oplog" );
186        while ( query.next() )
187        {
188            dumpout << "ID: " << query.value( 0 ).toInt() << endl
189                    << "GUID: " << query.value( 2 ).toString() << endl
190                    << "Command: " << query.value( 3 ).toString() << endl
191                    << "Singleton: " << query.value( 4 ).toBool() << endl
192                    << "JSON: " << ( query.value( 5 ).toBool() ? qUncompress( query.value( 6 ).toByteArray() ) : query.value( 6 ).toByteArray() )
193                    << endl << endl << endl;
194        }
195    }
196}
197
198
199void
200Tomahawk::DatabaseImpl::loadIndex()
201{
202    connect( m_fuzzyIndex, SIGNAL( indexStarted() ), SIGNAL( indexStarted() ) );
203    connect( m_fuzzyIndex, SIGNAL( indexReady() ), SIGNAL( indexReady() ) );
204    m_fuzzyIndex->loadLuceneIndex();
205}
206
207
208bool
209Tomahawk::DatabaseImpl::updateSchema( int oldVersion )
210{
211    // we are called here with the old database. we must migrate it to the CURRENT_SCHEMA_VERSION from the oldVersion
212    if ( oldVersion == 0 ) // empty database, so create our tables and stuff
213    {
214        tLog() << "Create tables... old version is" << oldVersion;
215        QString sql( get_tomahawk_sql() );
216        QStringList statements = sql.split( ";", QString::SkipEmptyParts );
217        m_db.transaction();
218
219        foreach ( const QString& sl, statements )
220        {
221            QString s( sl.trimmed() );
222            if ( s.isEmpty() )
223                continue;
224
225            tLog() << "Executing:" << s;
226            TomahawkSqlQuery query = newquery();
227            query.exec( s );
228        }
229
230        m_db.commit();
231        return true;
232    }
233    else // update in place! run the proper upgrade script
234    {
235        emit schemaUpdateStarted();
236        int cur = oldVersion;
237        m_db.transaction();
238        while ( cur < CURRENT_SCHEMA_VERSION )
239        {
240            cur++;
241
242            QString path = QString( RESPATH "sql/dbmigrate-%1_to_%2.sql" ).arg( cur - 1 ).arg( cur );
243            QFile script( path );
244            if ( !script.exists() || !script.open( QIODevice::ReadOnly ) )
245            {
246                tLog() << "Failed to find or open upgrade script from" << (cur-1) << "to" << cur << " (" << path << ")! Aborting upgrade...";
247                return false;
248            }
249
250            QString sql = QString::fromUtf8( script.readAll() ).trimmed();
251            QStringList statements = sql.split( ";", QString::SkipEmptyParts );
252            for ( int i = 0; i < statements.count(); ++i )
253            {
254                QString sql = statements.at( i );
255                QString clean = cleanSql( sql ).trimmed();
256                if ( clean.isEmpty() )
257                    continue;
258
259                tLog() << "Executing upgrade statement:" << clean;
260                TomahawkSqlQuery q = newquery();
261                q.exec( clean );
262
263                //Report to splash screen
264                emit schemaUpdateStatus( QString( "%1/%2" ).arg( QString::number( i + 1 ) )
265                                                           .arg( QString::number( statements.count() ) ) );
266            }
267        }
268        m_db.commit();
269        tLog() << "DB Upgrade successful!";
270        emit schemaUpdateDone();
271        return true;
272    }
273}
274
275
276QString
277Tomahawk::DatabaseImpl::cleanSql( const QString& sql )
278{
279    QString fixed = sql;
280    QRegExp r( "--[^\\n]*" );
281    fixed.replace( r, QString() );
282    return fixed.trimmed();
283}
284
285
286Tomahawk::result_ptr
287Tomahawk::DatabaseImpl::file( int fid )
288{
289    Tomahawk::result_ptr r;
290    TomahawkSqlQuery query = newquery();
291    query.exec( QString( "SELECT url, mtime, size, md5, mimetype, duration, bitrate, "
292                         "file_join.artist, file_join.album, file_join.track, file_join.composer, "
293                         "(SELECT name FROM artist WHERE id = file_join.artist) AS artname, "
294                         "(SELECT name FROM album  WHERE id = file_join.album)  AS albname, "
295                         "(SELECT name FROM track  WHERE id = file_join.track)  AS trkname, "
296                         "(SELECT name FROM artist WHERE id = file_join.composer) AS cmpname, "
297                         "source, "
298                         "(SELECT artist.name FROM artist, album WHERE artist.id = album.artist AND album.id = file_join.album) AS albumartname "
299                         "FROM file, file_join "
300                         "WHERE file.id = file_join.file AND file.id = %1" )
301                .arg( fid ) );
302
303    if ( query.next() )
304    {
305        QString url = query.value( 0 ).toString();
306        Tomahawk::source_ptr s = SourceList::instance()->get( query.value( 15 ).toUInt() );
307        if ( !s )
308            return r;
309        if ( !s->isLocal() )
310            url = QString( "servent://%1\t%2" ).arg( s->nodeId() ).arg( url );
311
312        Tomahawk::track_ptr track = Tomahawk::Track::get( query.value( 9 ).toUInt(), query.value( 11 ).toString(), query.value( 13 ).toString(),
313                                                          query.value( 12 ).toString(), query.value( 16 ).toString(), query.value( 5 ).toUInt(),
314                                                          query.value( 14 ).toString(), 0, 0 );
315        if ( !track )
316            return r;
317        r = Tomahawk::Result::get( url, track );
318        if ( !r )
319            return r;
320
321        r->setModificationTime( query.value( 1 ).toUInt() );
322        r->setSize( query.value( 2 ).toUInt() );
323        r->setMimetype( query.value( 4 ).toString() );
324        r->setBitrate( query.value( 6 ).toUInt() );
325        r->setResolvedByCollection( s->dbCollection() );
326        r->setFileId( fid );
327    }
328
329    return r;
330}
331
332
333int
334Tomahawk::DatabaseImpl::artistId( const QString& name_orig, bool autoCreate )
335{
336    if ( m_lastart == name_orig )
337        return m_lastartid;
338
339    int id = 0;
340    QString sortname = Tomahawk::DatabaseImpl::sortname( name_orig );
341
342    TomahawkSqlQuery query = newquery();
343    query.prepare( "SELECT id FROM artist WHERE sortname = ?" );
344    query.addBindValue( sortname );
345    query.exec();
346    if ( query.next() )
347    {
348        id = query.value( 0 ).toInt();
349    }
350    if ( id )
351    {
352        m_lastart = name_orig;
353        m_lastartid = id;
354        return id;
355    }
356
357    if ( autoCreate )
358    {
359        // not found, insert it.
360        query.prepare( "INSERT INTO artist(id,name,sortname) VALUES(NULL,?,?)" );
361        query.addBindValue( name_orig );
362        query.addBindValue( sortname );
363        if ( !query.exec() )
364        {
365            tDebug() << "Failed to insert artist:" << name_orig;
366            return 0;
367        }
368
369        id = query.lastInsertId().toInt();
370        m_lastart = name_orig;
371        m_lastartid = id;
372    }
373
374    return id;
375}
376
377
378int
379Tomahawk::DatabaseImpl::trackId( int artistid, const QString& name_orig, bool autoCreate )
380{
381    int id = 0;
382    QString sortname = Tomahawk::DatabaseImpl::sortname( name_orig );
383    //if( ( id = m_artistcache[sortname] ) ) return id;
384
385    TomahawkSqlQuery query = newquery();
386    query.prepare( "SELECT id FROM track WHERE artist = ? AND sortname = ?" );
387    query.addBindValue( artistid );
388    query.addBindValue( sortname );
389    query.exec();
390
391    if ( query.next() )
392    {
393        id = query.value( 0 ).toInt();
394    }
395    if ( id )
396    {
397        //m_trackcache[sortname]=id;
398        return id;
399    }
400
401    if ( autoCreate )
402    {
403        // not found, insert it.
404        query.prepare( "INSERT INTO track(id,artist,name,sortname) VALUES(NULL,?,?,?)" );
405        query.addBindValue( artistid );
406        query.addBindValue( name_orig );
407        query.addBindValue( sortname );
408        if ( !query.exec() )
409        {
410            tDebug() << "Failed to insert track:" << name_orig;
411            return 0;
412        }
413
414        id = query.lastInsertId().toInt();
415    }
416
417    return id;
418}
419
420
421int
422Tomahawk::DatabaseImpl::albumId( int artistid, const QString& name_orig, bool autoCreate )
423{
424    if ( name_orig.isEmpty() )
425    {
426        //qDebug() << Q_FUNC_INFO << "empty album name";
427        return 0;
428    }
429
430    if ( m_lastartid == artistid && m_lastalb == name_orig )
431        return m_lastalbid;
432
433    int id = 0;
434    QString sortname = Tomahawk::DatabaseImpl::sortname( name_orig );
435    //if( ( id = m_albumcache[sortname] ) ) return id;
436
437    TomahawkSqlQuery query = newquery();
438    query.prepare( "SELECT id FROM album WHERE artist = ? AND sortname = ?" );
439    query.addBindValue( artistid );
440    query.addBindValue( sortname );
441    query.exec();
442    if ( query.next() )
443    {
444        id = query.value( 0 ).toInt();
445    }
446    if ( id )
447    {
448        m_lastalb = name_orig;
449        m_lastalbid = id;
450        return id;
451    }
452
453    if ( autoCreate )
454    {
455        // not found, insert it.
456        query.prepare( "INSERT INTO album(id,artist,name,sortname) VALUES(NULL,?,?,?)" );
457        query.addBindValue( artistid );
458        query.addBindValue( name_orig );
459        query.addBindValue( sortname );
460        if( !query.exec() )
461        {
462            tDebug() << "Failed to insert album:" << name_orig;
463            return 0;
464        }
465
466        id = query.lastInsertId().toInt();
467        m_lastalb = name_orig;
468        m_lastalbid = id;
469    }
470
471    return id;
472}
473
474
475QList< QPair<int, float> >
476Tomahawk::DatabaseImpl::search( const Tomahawk::query_ptr& query, uint limit )
477{
478    QList< QPair<int, float> > resultslist;
479
480    QMap< int, float > resultsmap = m_fuzzyIndex->search( query );
481    foreach ( int i, resultsmap.keys() )
482    {
483        resultslist << QPair<int, float>( i, (float)resultsmap.value( i ) );
484    }
485    qSort( resultslist.begin(), resultslist.end(), Tomahawk::DatabaseImpl::scorepairSorter );
486
487    if ( !limit )
488        return resultslist;
489
490    QList< QPair<int, float> > resultscapped;
491    for ( int i = 0; i < (int)limit && i < resultsmap.count(); i++ )
492    {
493        resultscapped << resultslist.at( i );
494    }
495
496    return resultscapped;
497}
498
499
500QList< QPair<int, float> >
501Tomahawk::DatabaseImpl::searchAlbum( const Tomahawk::query_ptr& query, uint limit )
502{
503    QList< QPair<int, float> > resultslist;
504
505    QMap< int, float > resultsmap = m_fuzzyIndex->searchAlbum( query );
506    foreach ( int i, resultsmap.keys() )
507    {
508        resultslist << QPair<int, float>( i, (float)resultsmap.value( i ) );
509    }
510    qSort( resultslist.begin(), resultslist.end(), Tomahawk::DatabaseImpl::scorepairSorter );
511
512    if ( !limit )
513        return resultslist;
514
515    QList< QPair<int, float> > resultscapped;
516    for ( int i = 0; i < (int)limit && i < resultsmap.count(); i++ )
517    {
518        resultscapped << resultslist.at( i );
519    }
520
521    return resultscapped;
522}
523
524
525QList< int >
526Tomahawk::DatabaseImpl::getTrackFids( int tid )
527{
528    QList< int > ret;
529
530    TomahawkSqlQuery query = newquery();
531    query.exec( QString( "SELECT file.id FROM file, file_join "
532                         "WHERE file_join.file=file.id "
533                         "AND file_join.track = %1 ").arg( tid ) );
534    query.exec();
535
536    while( query.next() )
537        ret.append( query.value( 0 ).toInt() );
538
539    return ret;
540}
541
542
543QString
544Tomahawk::DatabaseImpl::sortname( const QString& str, bool replaceArticle )
545{
546    QString s = str.simplified().toLower();
547
548    if ( replaceArticle && s.startsWith( "the " ) )
549    {
550        s = s.mid( 4 );
551    }
552
553    return s;
554}
555
556
557QVariantMap
558Tomahawk::DatabaseImpl::artist( int id )
559{
560    TomahawkSqlQuery query = newquery();
561    query.exec( QString( "SELECT id, name, sortname FROM artist WHERE id = %1" ).arg( id ) );
562
563    QVariantMap m;
564    if( !query.next() )
565        return m;
566
567    m["id"] = query.value( 0 );
568    m["name"] = query.value( 1 );
569    m["sortname"] = query.value( 2 );
570    return m;
571}
572
573
574QVariantMap
575Tomahawk::DatabaseImpl::track( int id )
576{
577    TomahawkSqlQuery query = newquery();
578    query.exec( QString( "SELECT id, artist, name, sortname FROM track WHERE id = %1" ).arg( id ) );
579
580    QVariantMap m;
581    if( !query.next() )
582        return m;
583
584    m["id"] = query.value( 0 );
585    m["artist"] = query.value( 1 );
586    m["name"] = query.value( 2 );
587    m["sortname"] = query.value( 3 );
588    return m;
589}
590
591
592QVariantMap
593Tomahawk::DatabaseImpl::album( int id )
594{
595    TomahawkSqlQuery query = newquery();
596    query.exec( QString( "SELECT id, artist, name, sortname FROM album WHERE id = %1" ).arg( id ) );
597
598    QVariantMap m;
599    if( !query.next() )
600        return m;
601
602    m["id"] = query.value( 0 );
603    m["artist"] = query.value( 1 );
604    m["name"] = query.value( 2 );
605    m["sortname"] = query.value( 3 );
606    return m;
607}
608
609
610Tomahawk::result_ptr
611Tomahawk::DatabaseImpl::resultFromHint( const Tomahawk::query_ptr& origquery )
612{
613    QString url = origquery->resultHint();
614    TomahawkSqlQuery query = newquery();
615    Tomahawk::source_ptr s;
616    Tomahawk::result_ptr res;
617    QString fileUrl;
618
619    if ( url.contains( "servent://" ) )
620    {
621        QStringList parts = url.mid( QString( "servent://" ).length() ).split( "\t" );
622        s = SourceList::instance()->get( parts.at( 0 ) );
623        fileUrl = parts.at( 1 );
624
625        if ( s.isNull() )
626            return res;
627    }
628    else if ( url.contains( "file://" ) )
629    {
630        s = SourceList::instance()->getLocal();
631        fileUrl = url;
632    }
633    else if ( TomahawkUtils::whitelistedHttpResultHint( url ) )
634    {
635        Tomahawk::track_ptr track = Tomahawk::Track::get( origquery->queryTrack()->artist(),
636                                                          origquery->queryTrack()->track(),
637                                                          origquery->queryTrack()->album(),
638                                                          QString(),
639                                                          origquery->queryTrack()->duration() );
640
641        // Return http resulthint directly
642        res = Tomahawk::Result::get( url, track );
643        res->setRID( uuid() );
644        const QUrl u = QUrl::fromUserInput( url );
645        res->setFriendlySource( u.host() );
646
647        ResultUrlChecker* checker = new ResultUrlChecker( origquery, nullptr, QList< result_ptr >() << res );
648        QEventLoop loop;
649        connect( checker, SIGNAL( done() ), &loop, SLOT( quit() ) );
650        loop.exec();
651        checker->deleteLater();
652
653        if ( checker->validResults().isEmpty() )
654            res = result_ptr();
655
656        return res;
657    }
658    else
659    {
660        // No resulthint
661        return res;
662    }
663
664    bool searchlocal = s->isLocal();
665
666    QString sql = QString( "SELECT "
667                            "url, mtime, size, md5, mimetype, duration, bitrate, "  //0
668                            "file_join.artist, file_join.album, file_join.track, "  //7
669                            "file_join.composer, "                                  //10
670                            "artist.name as artname, "                              //11
671                            "album.name as albname, "                               //12
672                            "track.name as trkname, "                               //13
673                            "composer.name as cmpname, "                            //14
674                            "file.source, "                                         //15
675                            "file_join.albumpos, "                                  //16
676                            "file_join.discnumber, "                                //17
677                            "artist.id as artid, "                                  //18
678                            "album.id as albid, "                                   //19
679                            "composer.id as cmpid, "                                //20
680                            "albumArtist.id as albumartistid, "                     //21
681                            "albumArtist.name as albumartistname "                  //22
682                            "FROM file, file_join, artist, track "
683                            "LEFT JOIN album ON album.id = file_join.album "
684                            "LEFT JOIN artist AS composer on composer.id = file_join.composer "
685                            "LEFT JOIN artist AS albumArtist on albumArtist.id = album.artist "
686                            "WHERE "
687                            "artist.id = file_join.artist AND "
688                            "track.id = file_join.track AND "
689                            "file.source %1 AND "
690                            "file_join.file = file.id AND "
691                            "file.url = ?"
692        ).arg( searchlocal ? "IS NULL" : QString( "= %1" ).arg( s->id() ) );
693
694    query.prepare( sql );
695    query.bindValue( 0, fileUrl );
696    query.exec();
697
698    if ( query.next() )
699    {
700        QString url = query.value( 0 ).toString();
701        Tomahawk::source_ptr s = SourceList::instance()->get( query.value( 15 ).toUInt() );
702        if ( !s )
703            return res;
704        if ( !s->isLocal() )
705            url = QString( "servent://%1\t%2" ).arg( s->nodeId() ).arg( url );
706
707        Tomahawk::track_ptr track = Tomahawk::Track::get( query.value( 9 ).toUInt(),
708                                                          query.value( 11 ).toString(),
709                                                          query.value( 13 ).toString(),
710                                                          query.value( 12 ).toString(),
711                                                          query.value( 22 ).toString(),
712                                                          query.value( 5 ).toInt(),
713                                                          query.value( 14 ).toString(),
714                                                          query.value( 16 ).toUInt(),
715                                                          query.value( 17 ).toUInt() );
716        track->loadAttributes();
717
718        res = Tomahawk::Result::get( url, track );
719        res->setModificationTime( query.value( 1 ).toUInt() );
720        res->setSize( query.value( 2 ).toUInt() );
721        res->setMimetype( query.value( 4 ).toString() );
722        res->setBitrate( query.value( 6 ).toInt() );
723        res->setRID( uuid() );
724        res->setResolvedByCollection( s->dbCollection() );
725    }
726
727    return res;
728}
729
730
731bool
732Tomahawk::DatabaseImpl::openDatabase( const QString& dbname, bool checkSchema )
733{
734    QString connName( "tomahawk" );
735    if ( !checkSchema )
736    {
737        // secondary connection, use a unique connection name
738        connName += "_" + uuid();
739    }
740
741    static QString sqlDriver;
742    bool schemaUpdated = false;
743    int version = -1;
744    {
745        if ( sqlDriver.isEmpty() )
746        {
747            QStringList drivers = QSqlDatabase::drivers();
748            if (drivers.contains( "QSQLITE3" ))
749            {
750                sqlDriver = "QSQLITE3";
751            }
752            else
753            {
754                sqlDriver = "QSQLITE";
755            }
756        }
757
758        QSqlDatabase db = QSqlDatabase::addDatabase( sqlDriver, connName );
759        db.setDatabaseName( dbname );
760        db.setConnectOptions( "QSQLITE_ENABLE_SHARED_CACHE=1" );
761        if ( !db.open() )
762        {
763            tLog() << "Failed to open database" << dbname << "with driver" << sqlDriver;
764            throw "failed to open db"; // TODO
765        }
766
767        if ( checkSchema )
768        {
769            QSqlQuery qry = QSqlQuery( db );
770            qry.exec( "SELECT v FROM settings WHERE k='schema_version'" );
771            if ( qry.next() )
772            {
773                version = qry.value( 0 ).toInt();
774                tLog() << "Database schema of" << dbname << sqlDriver << "is" << version;
775            }
776        }
777        else
778            version = CURRENT_SCHEMA_VERSION;
779
780        if ( version < 0 || version == CURRENT_SCHEMA_VERSION )
781            m_db = db;
782    }
783
784    if ( version > 0 && version != CURRENT_SCHEMA_VERSION )
785    {
786        QSqlDatabase::removeDatabase( connName );
787
788        QString newname = QString( "%1.v%2" ).arg( dbname ).arg( version );
789        tLog() << endl << "****************************" << endl;
790        tLog() << "Schema version too old: " << version << ". Current version is:" << CURRENT_SCHEMA_VERSION;
791        tLog() << "Moving" << dbname << newname;
792        tLog() << "If the migration fails, you can recover your DB by copying" << newname << "back to" << dbname;
793        tLog() << endl << "****************************" << endl;
794
795        QFile::copy( dbname, newname );
796        {
797            m_db = QSqlDatabase::addDatabase( sqlDriver, connName );
798            m_db.setDatabaseName( dbname );
799            if ( !m_db.open() )
800                throw "db moving failed";
801
802            schemaUpdated = updateSchema( version );
803            if ( !schemaUpdated )
804            {
805                Q_ASSERT( false );
806                QTimer::singleShot( 0, qApp, SLOT( quit() ) );
807            }
808        }
809    }
810    else if ( version < 0 )
811    {
812        schemaUpdated = updateSchema( 0 );
813    }
814
815    return schemaUpdated;
816}