/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 LinkC++ | 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}