src/collectiondb.cpp

   1 // (c) 2004 Mark Kretschmann <markey@web.de>
   2 // (c) 2004 Christian Muehlhaeuser <chris@chris.de>
   3 // (c) 2004 Sami Nieminen <sami.nieminen@iki.fi>
   4 // (c) 2005 Ian Monroe <ian@monroe.nu>
   5 // (c) 2005 Jeff Mitchell <kde-dev@emailgoeshere.com>
   6 // (c) 2005 Isaiah Damron <xepo@trifault.net>
   7 // (c) 2005-2006 Alexandre Pereira de Oliveira <aleprj@gmail.com>
   8 // (c) 2006 Jonas Hurrelmann <j@outpo.st>
   9 // (c) 2006 Shane King <kde@dontletsstart.com>
  10 // (c) 2006 Peter C. Ndikuwera <pndiku@gmail.com>
  11 // (c) 2006 Stanislav Nikolov <valsinats@gmail.com>
  12 // See COPYING file for licensing information.
  13
  14 //krazy:excludeall=doublequote_chars
  15
  16 #define DEBUG_PREFIX "CollectionDB"
  17
  18 #include "app.h"
  19 #include "amarok.h"
  20 #include "amarokconfig.h"
  21 #include "config.h"
  22 #include "debug.h"
  23 #include "collectionbrowser.h"    //updateTags()
  24 #include "collectiondb.h"
  25 #include "coverfetcher.h"
  26 #include "enginecontroller.h"
  27 #include "expression.h"
  28 #include "mediabrowser.h"
  29 #include "metabundle.h"           //updateTags()
  30 #include "mountpointmanager.h"    //buildQuery()
  31 #include "organizecollectiondialog.h"
  32 #include "playlist.h"
  33 #include "playlistloader.h"
  34 #include "playlistbrowser.h"
  35 #include "podcastbundle.h"        //addPodcast
  36 #include "qstringx.h"
  37 #include "querybuilder.h"
  38 //Added by qt3to4:
  39 #include <QTimerEvent>
  40 #include <QCustomEvent>
  41 #include <QByteArray>
  42 #include <QLabel>
  43 #include <Q3ValueList>
  44 #include <QPixmap>
  45 #include "scancontroller.h"
  46 #include "scriptmanager.h"
  47 #include "scrobbler.h"
  48 #include "statusbar.h"
  49 #include "threadmanager.h"
  50
  51 #include <QBuffer>
  52 #include <QCheckBox>
  53 #include <QEventLoop>
  54 #include <QFile>
  55 #include <qmap.h>
  56 #include <QMutex>
  57 #include <QRegExp>              //setHTMLLyrics()
  58 #include <QTimer>
  59 #include <QPainter>             //createDragPixmap()
  60 #include <QPalette>
  61 #include <pthread.h>              //debugging, can be removed later
  62 #include <QIODevice>
  63
  64 #include <kcharsets.h>            //setHTMLLyrics()
  65 #include <kcombobox.h>
  66 #include <kconfig.h>
  67 #include <kdialog.h>          //checkDatabase()
  68 #include <kglobal.h>
  69 #include <kinputdialog.h>         //setupCoverFetcher()
  70 #include <klineedit.h>            //setupCoverFetcher()
  71 #include <klocale.h>
  72 #include <kcodecs.h>
  73 #include <kmessagebox.h>
  74 #include <kstandarddirs.h>
  75 #include <kio/job.h>
  76 #include <kio/netaccess.h>
  77 #include <krandom.h>
  78
  79 #include <cmath>                 //DbConnection::sqlite_power()
  80 #include <ctime>                 //query()
  81 #include <unistd.h>              //usleep()
  82
  83 #include <audioproperties.h>
  84
  85 #include "sqlite/sqlite3.h"
  86
  87 #ifdef USE_MYSQL
  88     #include <mysql/mysql.h>
  89     #include <mysql/mysql_version.h>
  90 #endif
  91
  92 #ifdef USE_POSTGRESQL
  93     #include <libpq-fe.h>
  94 #endif
  95
  96 #undef HAVE_INOTIFY  // NOTE Disabled for now, due to stability issues
  97
  98 #ifdef HAVE_INOTIFY
  99     #include <linux/inotify.h>
 100     #include "inotify/inotify-syscalls.h"
 101 #endif
 102
 103
 104         //bump DATABASE_VERSION whenever changes to the table structure are made.
 105         // This erases tags, album, artist, composer, genre, year, images, embed, directory and related_artists tables.
 106 const int CollectionDB::DATABASE_VERSION = 35;
 107         // Persistent Tables hold data that is somehow valuable to the user, and can't be erased when rescaning.
 108         // When bumping this, write code to convert the data!
 109 const int CollectionDB::DATABASE_PERSISTENT_TABLES_VERSION = 19;
 110         // Bumping this erases stats table. If you ever need to, write code to convert the data!
 111 const int CollectionDB::DATABASE_STATS_VERSION = 12;
 112         // When bumping this, you should provide code to convert the data.
 113 const int CollectionDB::DATABASE_PODCAST_TABLES_VERSION = 2;
 114 const int CollectionDB::DATABASE_AFT_VERSION = 2;
 115         // persistent table. you should provide code to convert the data when bumping this
 116 const int CollectionDB::DATABASE_DEVICES_VERSION = 1;
 117 const int CollectionDB::MONITOR_INTERVAL = 60;
 118
 119 using Amarok::QStringx;
 120
 121 #define DEBUG 0
 122
 123 //////////////////////////////////////////////////////////////////////////////////////////
 124 // CLASS INotify
 125 //////////////////////////////////////////////////////////////////////////////////////////
 126
 127 INotify* INotify::s_instance = 0;
 128
 129 INotify::INotify( CollectionDB *parent, int fd )
 130     : DependentJob( parent, "INotify" )
 131     , m_parent( parent )
 132     , m_fd( fd )
 133 {
 134     s_instance = this;
 135 }
 136
 137
 138 INotify::~INotify()
 139 {}
 140
 141
 142 bool
 143 INotify::watchDir( const QString directory )
 144 {
 145 #ifdef HAVE_INOTIFY
 146     int wd = inotify_add_watch( m_fd, directory.local8Bit(), IN_CLOSE_WRITE | IN_DELETE | IN_MOVE |
 147                                                              IN_MODIFY | IN_ATTRIB );
 148     if ( wd < 0 )
 149         debug() << "Could not add INotify watch for: " << directory << endl;
 150
 151     return ( wd >= 0 );
 152 #else
 153     Q_UNUSED(directory);
 154 #endif
 155
 156     return false;
 157 }
 158
 159
 160 bool
 161 INotify::doJob()
 162 {
 163 #ifdef HAVE_INOTIFY
 164     DEBUG_BLOCK
 165
 166     IdList list = MountPointManager::instance()->getMountedDeviceIds();
 167     QString deviceIds;
 168     oldForeachType( IdList, list )
 169     {
 170         if ( !deviceIds.isEmpty() ) deviceIds += ',';
 171         deviceIds += QString::number(*it);
 172     }
 173     const QStringList values = m_parent->query( QString( "SELECT dir, deviceid FROM directories WHERE deviceid IN (%1);" )
 174                                                     .arg( deviceIds ) );
 175     oldForeach( values )
 176     {
 177         QString rpath = *it;
 178         int deviceid = (*(++it)).toInt();
 179         QString abspath = MountPointManager::instance()->getAbsolutePath( deviceid, rpath );
 180         watchDir( abspath );
 181     }
 182
 183     /* size of the event structure, not counting name */
 184     const int EVENT_SIZE = ( sizeof( struct inotify_event ) );
 185     /* reasonable guess as to size of 1024 events */
 186     const int BUF_LEN = 1024 * ( EVENT_SIZE + 16 );
 187
 188     while ( 1 )
 189     {
 190         char buf[BUF_LEN];
 191         int len, i = 0;
 192         len = read( m_fd, buf, BUF_LEN );
 193         if ( len < 0 )
 194         {
 195             debug() << "Read from INotify failed" << endl;
 196             return false;
 197         }
 198         else
 199         {
 200             if ( !len )
 201             {
 202                 /* BUF_LEN too small? */
 203             }
 204             else
 205             {
 206                 while ( i < len )
 207                 {
 208                     struct inotify_event *event;
 209                     event = (struct inotify_event *) &buf[i];
 210
 211                     i += EVENT_SIZE + event->len;
 212                 }
 213
 214                 QTimer::singleShot( 0, m_parent, SLOT( scanMonitor() ) );
 215             }
 216         }
 217     }
 218 #endif
 219
 220     // this shouldn't happen
 221     return false;
 222 }
 223
 224
 225 //////////////////////////////////////////////////////////////////////////////////////////
 226 // CLASS CollectionDB
 227 //////////////////////////////////////////////////////////////////////////////////////////
 228
 229 QMutex* CollectionDB::connectionMutex = new QMutex();
 230 QMutex* CollectionDB::itemCoverMapMutex = new QMutex();
 231 //we don't have to worry about this map leaking memory since ThreadManager limits the total
 232 //number of QThreads ever created
 233 QMap<QThread *, DbConnection *> *CollectionDB::threadConnections = new QMap<QThread *, DbConnection *>();
 234 QMap<Q3ListViewItem*, CoverFetcher*> *CollectionDB::itemCoverMap = new QMap<Q3ListViewItem*, CoverFetcher*>();
 235
 236 CollectionDB* CollectionDB::instance()
 237 {
 238     static CollectionDB db;
 239     return &db;
 240 }
 241
 242
 243 CollectionDB::CollectionDB()
 244         : EngineObserver( EngineController::instance() )
 245         , m_autoScoring( true )
 246         , m_noCover( KStandardDirs::locate( "data", "amarok/images/nocover.png" ) )
 247         , m_scanInProgress( false )
 248         , m_rescanRequired( false )
 249         , m_aftEnabledPersistentTables()
 250         , m_moveFileJobCancelled( false )
 251 {
 252     DEBUG_BLOCK
 253
 254 #ifdef USE_MYSQL
 255     if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
 256         m_dbConnType = DbConnection::mysql;
 257     else
 258 #endif
 259 #ifdef USE_POSTGRESQL
 260     if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
 261         m_dbConnType = DbConnection::postgresql;
 262     else
 263 #endif
 264         m_dbConnType = DbConnection::sqlite;
 265
 266     //perform all necessary operations to allow MountPointManager to access the devices table here
 267     //there is a recursive dependency between CollectionDB and MountPointManager and this is the workaround
 268     //checkDatabase has to be able to access MountPointManager
 269
 270     //<OPEN DATABASE>
 271     initialize();
 272     //</OPEN DATABASE>
 273
 274     // Remove cached "nocover" images, so that a new version actually gets shown
 275     // The asterisk is for also deleting the shadow-caches.
 276     const QStringList entryList = cacheCoverDir().entryList( "*nocover.png*", QDir::Files );
 277     oldForeach( entryList )
 278         cacheCoverDir().remove( *it );
 279
 280
 281     connect( this, SIGNAL(fileMoved(const QString&, const QString&, const QString&)),
 282              this, SLOT(aftMigratePermanentTablesUrl(const QString&, const QString&, const QString&)) );
 283     connect( this, SIGNAL(uniqueIdChanged(const QString&, const QString&, const QString&)),
 284              this, SLOT(aftMigratePermanentTablesUniqueId(const QString&, const QString&, const QString&)) );
 285
 286     connect( kapp, SIGNAL( aboutToQuit() ), this, SLOT( disableAutoScoring() ) );
 287
 288     connect( this, SIGNAL( coverRemoved( const QString&, const QString& ) ),
 289                    SIGNAL( coverChanged( const QString&, const QString& ) ) );
 290     connect( Scrobbler::instance(), SIGNAL( similarArtistsFetched( const QString&, const QStringList& ) ),
 291              this,                    SLOT( similarArtistsFetched( const QString&, const QStringList& ) ) );
 292
 293     // If we're supposed to monitor dirs for changes, make sure we run it once
 294     // on startup, since inotify can't inform us about old events
 295 //     QTimer::singleShot( 0, this, SLOT( scanMonitor() ) )
 296     initDirOperations();
 297     m_aftEnabledPersistentTables << "lyrics" << "statistics" << "tags_labels";
 298 }
 299
 300
 301 CollectionDB::~CollectionDB()
 302 {
 303     DEBUG_BLOCK
 304
 305 #ifdef HAVE_INOTIFY
 306     if ( INotify::instance()->fd() >= 0 )
 307         close( INotify::instance()->fd() );
 308 #endif
 309
 310     destroy();
 311 }
 312
 313
 314 QString
 315 CollectionDB::exactCondition( const QString &right )
 316 {
 317     if ( DbConnection::mysql == instance()->getDbConnectionType() )
 318         return QString( "= BINARY '" + instance()->escapeString( right ) + '\'' );
 319     else
 320         return QString( "= '" + instance()->escapeString( right ) + '\'' );
 321 }
 322
 323
 324 QString
 325 CollectionDB::likeCondition( const QString &right, bool anyBegin, bool anyEnd )
 326 {
 327     QString escaped = right;
 328     escaped.replace( '/', "//" ).replace( '%', "/%" ).replace( '_', "/_" );
 329     escaped = instance()->escapeString( escaped );
 330
 331     QString ret;
 332     if ( DbConnection::postgresql == instance()->getDbConnectionType() )
 333         ret = " ILIKE "; //case-insensitive according to locale
 334     else
 335         ret = " LIKE ";
 336
 337     ret += '\'';
 338     if ( anyBegin )
 339             ret += '%';
 340     ret += escaped;
 341     if ( anyEnd )
 342             ret += '%';
 343     ret += '\'';
 344
 345     //Use / as the escape character
 346     ret += " ESCAPE '/' ";
 347
 348     return ret;
 349 }
 350
 351 //////////////////////////////////////////////////////////////////////////////////////////
 352 // PUBLIC
 353 //////////////////////////////////////////////////////////////////////////////////////////
 354
 355 void
 356 CollectionDB::initDirOperations()
 357 {
 358     //this code was originally part of the ctor. It has to call MountPointManager to
 359     //generate absolute paths from deviceids and relative paths. MountPointManager's ctor
 360     //absolutely has to access the database, which resulted in a recursive ctor call. To
 361     //solve this problem, the directory access code was moved into its own method, which can
 362     //only be called when the CollectionDB object already exists.
 363
 364     //FIXME max: make sure we check additional directories if we connect a new device
 365 #ifdef HAVE_INOTIFY
 366     // Try to initialize inotify, if not available use the old timer approach.
 367     int inotify_fd = inotify_init();
 368     if ( inotify_fd < 0 )
 369 #endif
 370     {
 371 //         debug() << "INotify not available, using QTimer!" << endl;
 372         startTimer( MONITOR_INTERVAL * 1000 );
 373     }
 374 #ifdef HAVE_INOTIFY
 375     else
 376     {
 377         debug() << "INotify enabled!" << endl;
 378         ThreadManager::instance()->onlyOneJob( new INotify( this, inotify_fd ) );
 379     }
 380 #endif
 381
 382 }
 383
 384
 385 /**
 386  * Executes a SQL query on the already opened database
 387  * @param statement SQL program to execute. Only one SQL statement is allowed.
 388  * @return          The queried data, or QStringList() on error.
 389  */
 390 QStringList
 391 CollectionDB::query( const QString& statement, bool suppressDebug )
 392 {
 393     m_mutex.lock();
 394     clock_t start;
 395     if ( DEBUG )
 396     {
 397         debug() << "Query-start: " << statement << endl;
 398         start = clock();
 399     }
 400     if ( statement.trimmed().isEmpty() )
 401     {
 402         m_mutex.unlock();
 403         return QStringList();
 404     }
 405
 406     DbConnection *dbConn;
 407     dbConn = getMyConnection();
 408
 409     QStringList values = dbConn->query( statement, suppressDebug );
 410     if ( DEBUG )
 411     {
 412         clock_t finish = clock();
 413         const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
 414         debug() << "SQL-query (" << duration << "s): " << statement << endl;
 415     }
 416     m_mutex.unlock();
 417     return values;
 418 }
 419
 420
 421 /**
 422  * Executes a SQL insert on the already opened database
 423  * @param statement SQL statement to execute. Only one SQL statement is allowed.
 424  * @return          The rowid of the inserted item.
 425  */
 426 int
 427 CollectionDB::insert( const QString& statement, const QString& table )
 428 {
 429     m_mutex.lock();
 430     clock_t start;
 431     if ( DEBUG )
 432     {
 433         debug() << "insert-start: " << statement << endl;
 434         start = clock();
 435     }
 436
 437     DbConnection *dbConn;
 438     dbConn = getMyConnection();
 439
 440     int id = dbConn->insert( statement, table );
 441
 442     if ( DEBUG )
 443     {
 444         clock_t finish = clock();
 445         const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
 446         debug() << "SQL-insert (" << duration << "s): " << statement << endl;
 447     }
 448     m_mutex.unlock();
 449     return id;
 450 }
 451
 452 QString
 453 CollectionDB::deviceidSelection( const bool showAll )
 454 {
 455     if ( !showAll )
 456     {
 457         IdList list = MountPointManager::instance()->getMountedDeviceIds();
 458         QString deviceIds = "";
 459         oldForeachType( IdList, list )
 460         {
 461             if ( it != list.begin() ) deviceIds += ',';
 462             deviceIds += QString::number(*it);
 463         }
 464         return " AND tags.deviceid IN (" + deviceIds + ')';
 465     }
 466     else return "";
 467 }
 468
 469 QStringList
 470 CollectionDB::URLsFromQuery( const QStringList &result ) const
 471 {
 472     QStringList values;
 473     oldForeach( result )
 474     {
 475         const int id = (*it).toInt();
 476         values << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
 477     }
 478     return values;
 479 }
 480
 481 KUrl::List
 482 CollectionDB::URLsFromSqlDrag( const QStringList &values ) const
 483 {
 484     KUrl::List urls;
 485     for( QStringList::const_iterator it = values.begin();
 486             it != values.end();
 487             it++ )
 488     {
 489         const QString &rel = *it;
 490         it++;
 491         int id = (*it).toInt();
 492         urls += KUrl( MountPointManager::instance()->getAbsolutePath( id, rel ) );
 493         for( int i = 0;
 494                 i < QueryBuilder::dragFieldCount-1 && it != values.end();
 495                 i++ )
 496             it++;
 497     }
 498
 499     return urls;
 500 }
 501
 502 bool
 503 CollectionDB::isEmpty( )
 504 {
 505     QStringList values;
 506
 507     values = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
 508
 509     return values.isEmpty() ? true : values.first() == "0";
 510 }
 511
 512
 513 bool
 514 CollectionDB::isValid( )
 515 {
 516     QStringList values1;
 517     QStringList values2;
 518     QStringList values3;
 519     QStringList values4;
 520     QStringList values5;
 521
 522     values1 = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
 523     values2 = query( "SELECT COUNT( url ) FROM statistics LIMIT 1 OFFSET 0;" );
 524     values3 = query( "SELECT COUNT( url ) FROM podcastchannels LIMIT 1 OFFSET 0;" );
 525     values4 = query( "SELECT COUNT( url ) FROM podcastepisodes LIMIT 1 OFFSET 0;" );
 526     values5 = query( "SELECT COUNT( id ) FROM devices LIMIT 1 OFFSET 0;" );
 527
 528     //It's valid as long as we've got _some_ tables that have something in.
 529     return !( values1.isEmpty() && values2.isEmpty() && values3.isEmpty() && values4.isEmpty() && values5.isEmpty() );
 530 }
 531
 532
 533 QString
 534 CollectionDB::adminValue( QString noption ) {
 535     QStringList values;
 536     values = query (
 537         QString( "SELECT value FROM admin WHERE noption = '%1';").arg(noption)
 538     );
 539     return values.isEmpty() ? "" : values.first();
 540 }
 541
 542
 543 void
 544 CollectionDB::setAdminValue( QString noption, QString value ) {
 545
 546     QStringList values = query( QString( "SELECT value FROM admin WHERE noption = '%1';").arg( noption ));
 547     if(values.count() > 0)
 548     {
 549         query( QString( "UPDATE admin SET value = '%1' WHERE noption = '%2';" ).arg( value, noption ) );
 550     }
 551     else
 552     {
 553         insert( QString( "INSERT INTO admin (value, noption) values ( '%1', '%2' );" ).arg( value, noption ),
 554          NULL );
 555     }
 556 }
 557
 558
 559
 560 void
 561 CollectionDB::createTables( const bool temporary )
 562 {
 563     DEBUG_BLOCK
 564
 565     //create tag table
 566     query( QString( "CREATE %1 TABLE tags%2 ("
 567                     "url " + exactTextColumnType() + ","
 568                     "dir " + exactTextColumnType() + ","
 569                     "createdate INTEGER,"
 570                     "modifydate INTEGER,"
 571                     "album INTEGER,"
 572                     "artist INTEGER,"
 573                     "composer INTEGER,"
 574                     "genre INTEGER,"
 575                     "title " + textColumnType() + ","
 576                     "year INTEGER,"
 577                     "comment " + longTextColumnType() + ","
 578                     "track NUMERIC(4),"
 579                     "discnumber INTEGER,"
 580                     "bitrate INTEGER,"
 581                     "length INTEGER,"
 582                     "samplerate INTEGER,"
 583                     "filesize INTEGER,"
 584                     "filetype INTEGER,"
 585                     "sampler BOOL,"
 586                     "bpm FLOAT,"
 587                     "deviceid INTEGER);" )
 588                     .arg( temporary ? "TEMPORARY" : "" )
 589                     .arg( temporary ? "_temp" : "" ) );
 590
 591     QString albumAutoIncrement = "";
 592     QString artistAutoIncrement = "";
 593     QString composerAutoIncrement = "";
 594     QString genreAutoIncrement = "";
 595     QString yearAutoIncrement = "";
 596     if ( getDbConnectionType() == DbConnection::postgresql )
 597     {
 598         if(!temporary)
 599         {
 600             query( QString( "CREATE SEQUENCE album_seq;" ) );
 601             query( QString( "CREATE SEQUENCE artist_seq;" ) );
 602             query( QString( "CREATE SEQUENCE composer_seq;" ) );
 603             query( QString( "CREATE SEQUENCE genre_seq;" ) );
 604             query( QString( "CREATE SEQUENCE year_seq;" ) );
 605         }
 606
 607         albumAutoIncrement = QString("DEFAULT nextval('album_seq')");
 608         artistAutoIncrement = QString("DEFAULT nextval('artist_seq')");
 609         composerAutoIncrement = QString("DEFAULT nextval('composer_seq')");
 610         genreAutoIncrement = QString("DEFAULT nextval('genre_seq')");
 611         yearAutoIncrement = QString("DEFAULT nextval('year_seq')");
 612     }
 613     else if ( getDbConnectionType() == DbConnection::mysql )
 614     {
 615         albumAutoIncrement = "AUTO_INCREMENT";
 616         artistAutoIncrement = "AUTO_INCREMENT";
 617         composerAutoIncrement = "AUTO_INCREMENT";
 618         genreAutoIncrement = "AUTO_INCREMENT";
 619         yearAutoIncrement = "AUTO_INCREMENT";
 620     }
 621
 622     //create album table
 623     query( QString( "CREATE %1 TABLE album%2 ("
 624                     "id INTEGER PRIMARY KEY %3,"
 625                     "name " + textColumnType() + ");" )
 626                     .arg( temporary ? "TEMPORARY" : "" )
 627                     .arg( temporary ? "_temp" : "" )
 628                     .arg( albumAutoIncrement ) );
 629
 630     //create artist table
 631     query( QString( "CREATE %1 TABLE artist%2 ("
 632                     "id INTEGER PRIMARY KEY %3,"
 633                     "name " + textColumnType() + ");" )
 634                     .arg( temporary ? "TEMPORARY" : "" )
 635                     .arg( temporary ? "_temp" : "" )
 636                     .arg( artistAutoIncrement ) );
 637
 638     //create composer table
 639     query( QString( "CREATE %1 TABLE composer%2 ("
 640                     "id INTEGER PRIMARY KEY %3,"
 641                     "name " + textColumnType() + ");" )
 642                     .arg( temporary ? "TEMPORARY" : "" )
 643                     .arg( temporary ? "_temp" : "" )
 644                     .arg( composerAutoIncrement ) );
 645
 646     //create genre table
 647     query( QString( "CREATE %1 TABLE genre%2 ("
 648                     "id INTEGER PRIMARY KEY %3,"
 649                     "name " + textColumnType() +");" )
 650                     .arg( temporary ? "TEMPORARY" : "" )
 651                     .arg( temporary ? "_temp" : "" )
 652                     .arg( genreAutoIncrement ) );
 653
 654     //create year table
 655     query( QString( "CREATE %1 TABLE year%2 ("
 656                     "id INTEGER PRIMARY KEY %3,"
 657                     "name " + textColumnType() + ");" )
 658                     .arg( temporary ? "TEMPORARY" : "" )
 659                     .arg( temporary ? "_temp" : "" )
 660                     .arg( yearAutoIncrement ) );
 661
 662     //create images table
 663     query( QString( "CREATE %1 TABLE images%2 ("
 664                     "path " + exactTextColumnType() + ","
 665                     "deviceid INTEGER,"
 666                     "artist " + textColumnType() + ","
 667                     "album " + textColumnType() + ");" )
 668                     .arg( temporary ? "TEMPORARY" : "" )
 669                     .arg( temporary ? "_temp" : "" ) );
 670
 671     //create embed table
 672     query( QString( "CREATE %1 TABLE embed%2 ("
 673                     "url " + exactTextColumnType() + ","
 674                     "deviceid INTEGER,"
 675                     "hash " + exactTextColumnType() + ","
 676                     "description " + textColumnType() + ");" )
 677                     .arg( temporary ? "TEMPORARY" : "" )
 678                     .arg( temporary ? "_temp" : "" ) );
 679
 680     // create directory statistics table
 681     query( QString( "CREATE %1 TABLE directories%2 ("
 682                     "dir " + exactTextColumnType() + ","
 683                     "deviceid INTEGER,"
 684                     "changedate INTEGER);" )
 685                     .arg( temporary ? "TEMPORARY" : "" )
 686                     .arg( temporary ? "_temp" : "" ) );
 687
 688     //create uniqueid table
 689     query( QString( "CREATE %1 TABLE uniqueid%2 ("
 690                     "url " + exactTextColumnType() + ","
 691                     "deviceid INTEGER,"
 692                     "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
 693                     "dir " + exactTextColumnType() + ");" )
 694                     .arg( temporary ? "TEMPORARY" : "" )
 695                     .arg( temporary ? "_temp" : "" ) );
 696
 697     //create indexes
 698     query( QString( "CREATE INDEX album_idx%1 ON album%2( name );" )
 699                     .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
 700     query( QString( "CREATE INDEX artist_idx%1 ON artist%2( name );" )
 701                     .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
 702     query( QString( "CREATE INDEX composer_idx%1 ON composer%2( name );" )
 703                     .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
 704     query( QString( "CREATE INDEX genre_idx%1 ON genre%2( name );" )
 705                     .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
 706     query( QString( "CREATE INDEX year_idx%1 ON year%2( name );" )
 707                     .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
 708
 709     if ( !temporary )
 710     {
 711         //create admin table -- holds the db version, put here other stuff if necessary
 712         query( QString( "CREATE TABLE admin ("
 713                     "noption " + textColumnType() + ", "
 714                     "value " + textColumnType() + ");" ) );
 715
 716         // create related artists cache
 717         query( QString( "CREATE TABLE related_artists ("
 718                         "artist " + textColumnType() + ","
 719                         "suggestion " + textColumnType() + ","
 720                         "changedate INTEGER );" ) );
 721         query( "CREATE INDEX related_artists_artist ON related_artists( artist );" );
 722
 723         createIndices();
 724     }
 725     else
 726     {
 727         query( "CREATE UNIQUE INDEX url_tagtemp ON tags_temp( url, deviceid );" );
 728         query( "CREATE UNIQUE INDEX embed_urltemp ON embed_temp( url, deviceid );" );
 729         query( "CREATE UNIQUE INDEX dir_temp_dir ON directories_temp( dir, deviceid );" );
 730     }
 731 }
 732
 733 void
 734 CollectionDB::createIndices()
 735 {
 736     //This creates the indices for tables created in createTables. It should not refer to
 737     //tables which are not created in that function.
 738     debug() << "Creating indices, ignore errors about already existing indices" << endl;
 739
 740     query( "CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );" );
 741     query( "CREATE INDEX album_tag ON tags( album );" );
 742     query( "CREATE INDEX artist_tag ON tags( artist );" );
 743     query( "CREATE INDEX composer_tag ON tags( composer );" );
 744     query( "CREATE INDEX genre_tag ON tags( genre );" );
 745     query( "CREATE INDEX year_tag ON tags( year );" );
 746     query( "CREATE INDEX sampler_tag ON tags( sampler );" );
 747
 748     query( "CREATE INDEX images_album ON images( album );" );
 749     query( "CREATE INDEX images_artist ON images( artist );" );
 750
 751     query( "CREATE INDEX images_url ON images( path, deviceid );" );
 752
 753     query( "CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );" );
 754     query( "CREATE INDEX embed_hash ON embed( hash );" );
 755
 756     query( "CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );" );
 757     query( "CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );");
 758     query( "CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );");
 759
 760     query( "CREATE INDEX album_idx ON album( name );" );
 761     query( "CREATE INDEX artist_idx ON artist( name );" );
 762     query( "CREATE INDEX composer_idx ON composer( name );" );
 763     query( "CREATE INDEX genre_idx ON genre( name );" );
 764     query( "CREATE INDEX year_idx ON year( name );" );
 765
 766     query( "CREATE INDEX related_artists_artist ON related_artists( artist );" );
 767
 768     debug() << "Finished creating indices, stop ignoring errors" << endl;
 769 }
 770
 771 void
 772 CollectionDB::createPermanentIndices()
 773 {
 774     //this method creates all indices which are not referred to in createTables
 775     //this method is called on each startup of amarok
 776     //until we figure out a way to handle this better it produces SQL errors if the indices
 777     //already exist, but these can be ignored
 778     debug() << "Creating permanent indices, ignore errors about already existing indices" << endl;
 779
 780     query( "CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );" );
 781     query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
 782     query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
 783     query( "CREATE INDEX url_playlists ON playlists( url );" );
 784     query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
 785     query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
 786     query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
 787     query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" );   //m:n relationship, DO NOT MAKE UNIQUE!
 788
 789     query( "CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );" );
 790     query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
 791     query( "CREATE INDEX rating_stats ON statistics( rating );" );
 792     query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
 793     query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
 794
 795     query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
 796     query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
 797     query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
 798     query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
 799
 800     debug() << "Finished creating permanent indices, stop ignoring errors" << endl;
 801 }
 802
 803
 804 void
 805 CollectionDB::dropTables( const bool temporary )
 806 {
 807     query( QString( "DROP TABLE tags%1;" ).arg( temporary ? "_temp" : "" ) );
 808     query( QString( "DROP TABLE album%1;" ).arg( temporary ? "_temp" : "" ) );
 809     query( QString( "DROP TABLE artist%1;" ).arg( temporary ? "_temp" : "" ) );
 810     query( QString( "DROP TABLE composer%1;" ).arg( temporary ? "_temp" : "" ) );
 811     query( QString( "DROP TABLE genre%1;" ).arg( temporary ? "_temp" : "" ) );
 812     query( QString( "DROP TABLE year%1;" ).arg( temporary ? "_temp" : "" ) );
 813     query( QString( "DROP TABLE images%1;" ).arg( temporary ? "_temp" : "" ) );
 814     query( QString( "DROP TABLE embed%1;" ).arg( temporary ? "_temp" : "" ) );
 815     query( QString( "DROP TABLE directories%1;" ).arg( temporary ? "_temp" : "" ) );
 816     query( QString( "DROP TABLE uniqueid%1;" ).arg( temporary ? "_temp" : "" ) );
 817     if ( !temporary )
 818     {
 819         query( QString( "DROP TABLE related_artists;" ) );
 820         debug() << "Dropping media table" << endl;
 821     }
 822
 823     if ( getDbConnectionType() == DbConnection::postgresql )
 824     {
 825         if (temporary == false) {
 826             query( QString( "DROP SEQUENCE album_seq;" ) );
 827             query( QString( "DROP SEQUENCE artist_seq;" ) );
 828             query( QString( "DROP SEQUENCE composer_seq;" ) );
 829             query( QString( "DROP SEQUENCE genre_seq;" ) );
 830             query( QString( "DROP SEQUENCE year_seq;" ) );
 831         }
 832     }
 833 }
 834
 835
 836 void
 837 CollectionDB::clearTables( const bool temporary )
 838 {
 839     QString clearCommand = "DELETE FROM";
 840     if ( getDbConnectionType() == DbConnection::mysql || getDbConnectionType() == DbConnection::postgresql)
 841     {
 842         // TRUNCATE TABLE is faster than DELETE FROM TABLE, so use it when supported.
 843         clearCommand = "TRUNCATE TABLE";
 844     }
 845
 846     query( QString( "%1 tags%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 847     query( QString( "%1 album%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 848     query( QString( "%1 artist%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 849     query( QString( "%1 composer%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 850     query( QString( "%1 genre%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 851     query( QString( "%1 year%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 852     query( QString( "%1 images%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 853     query( QString( "%1 embed%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 854     query( QString( "%1 directories%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 855     query( QString( "%1 uniqueid%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
 856     if ( !temporary )
 857     {
 858         query( QString( "%1 related_artists;" ).arg( clearCommand ) );
 859         //debug() << "Clearing media table" << endl;
 860         //query( QString( "%1 media;" ).arg( clearCommand ) );
 861     }
 862 }
 863
 864
 865 void
 866 CollectionDB::copyTempTables( )
 867 {
 868     DEBUG_BLOCK
 869
 870     insert( "INSERT INTO tags SELECT * FROM tags_temp;", NULL );
 871
 872     //mysql 5 supports subqueries with IN, mysql 4 doesn't. this way will work for all SQL servers
 873     QStringList albumIdList = query( "SELECT album.id FROM album;" );
 874     //in an empty database, albumIdList is empty. This would result in a SQL query like NOT IN ( ) without
 875     //the -1 below which is invalid SQL. The auto generated values start at 1 so this is fine
 876     QString albumIds = "-1";
 877     oldForeach( albumIdList )
 878     {
 879         albumIds += ',';
 880         albumIds += *it;
 881     }
 882     insert( QString ( "INSERT INTO album SELECT * FROM album_temp WHERE album_temp.id NOT IN ( %1 );" ).arg( albumIds ), NULL );
 883
 884     QStringList artistIdList = query( "SELECT artist.id FROM artist;" );
 885     QString artistIds = "-1";
 886     oldForeach( artistIdList )
 887     {
 888         artistIds += ',';
 889         artistIds += *it;
 890     }
 891     insert( QString ( "INSERT INTO artist SELECT * FROM artist_temp WHERE artist_temp.id NOT IN ( %1 );" ).arg( artistIds ), NULL );
 892
 893     QStringList composerIdList = query( "SELECT composer.id FROM composer;" );
 894     QString composerIds = "-1";
 895     oldForeach( composerIdList )
 896     {
 897         composerIds += ',';
 898         composerIds += *it;
 899     }
 900     insert( QString ( "INSERT INTO composer SELECT * FROM composer_temp WHERE composer_temp.id NOT IN ( %1 );" ).arg( composerIds ), NULL );
 901
 902     QStringList genreIdList = query( "SELECT genre.id FROM genre;" );
 903     QString genreIds = "-1";
 904     oldForeach( genreIdList )
 905     {
 906         genreIds += ',';
 907         genreIds += *it;
 908     }
 909     insert( QString ( "INSERT INTO genre SELECT * FROM genre_temp WHERE genre_temp.id NOT IN ( %1 );" ).arg( genreIds ), NULL );
 910
 911     QStringList yearIdList = query( "SELECT year.id FROM year;" );
 912     QString yearIds = "-1";
 913     oldForeach( yearIdList )
 914     {
 915         yearIds += ',';
 916         yearIds += *it;
 917     }
 918     insert( QString ( "INSERT INTO year SELECT * FROM year_temp WHERE year_temp.id NOT IN ( %1 );" ).arg( yearIds ), NULL );
 919
 920     insert( "INSERT INTO images SELECT * FROM images_temp;", NULL );
 921     insert( "INSERT INTO embed SELECT * FROM embed_temp;", NULL );
 922     insert( "INSERT INTO directories SELECT * FROM directories_temp;", NULL );
 923     insert( "INSERT INTO uniqueid SELECT * FROM uniqueid_temp;", NULL );
 924 }
 925
 926 void
 927 CollectionDB::prepareTempTables()
 928 {
 929     DEBUG_BLOCK
 930     insert( "INSERT INTO album_temp SELECT * from album;", 0 );
 931     insert( "INSERT INTO artist_temp SELECT * from artist;", 0 );
 932     insert( "INSERT INTO composer_temp SELECT * from composer;", 0 );
 933     insert( "INSERT INTO genre_temp SELECT * from genre;", 0 );
 934     insert( "INSERT INTO year_temp SELECT * from year;", 0 );
 935 }
 936
 937 void
 938 CollectionDB::createDevicesTable()
 939 {
 940     debug() << "Creating DEVICES table" << endl;
 941     QString deviceAutoIncrement = "";
 942     if ( getDbConnectionType() == DbConnection::postgresql )
 943     {
 944         query( QString( "CREATE SEQUENCE devices_seq;" ) );
 945         deviceAutoIncrement = QString("DEFAULT nextval('devices_seq')");
 946     }
 947     else if ( getDbConnectionType() == DbConnection::mysql )
 948     {
 949         deviceAutoIncrement = "AUTO_INCREMENT";
 950     }
 951     query( QString( "CREATE TABLE devices ("
 952                     "id INTEGER PRIMARY KEY %1,"
 953                     "type " + textColumnType() + ","
 954                     "label " + textColumnType() + ","
 955                     "lastmountpoint " + textColumnType() + ","
 956                     "uuid " + textColumnType() + ","
 957                     "servername " + textColumnType() + ","
 958                     "sharename " + textColumnType() + ");" )
 959                  .arg( deviceAutoIncrement ) );
 960     query( "CREATE INDEX devices_type ON devices( type );" );
 961     query( "CREATE INDEX devices_uuid ON devices( uuid );" );
 962     query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" );
 963 }
 964
 965 void
 966 CollectionDB::createStatsTable()
 967 {
 968     // create music statistics database
 969     query( QString( "CREATE TABLE statistics ("
 970                     "url " + exactTextColumnType() + ","
 971                     "deviceid INTEGER,"
 972                     "createdate INTEGER,"
 973                     "accessdate INTEGER,"
 974                     "percentage FLOAT,"
 975                     "rating INTEGER DEFAULT 0,"
 976                     "playcounter INTEGER,"
 977                     "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
 978                     "deleted BOOL DEFAULT " + boolF() + ","
 979                     "PRIMARY KEY(url, deviceid) );" ) );
 980
 981 }
 982
 983 //Old version, used in upgrade code. This should never be changed.
 984 void
 985 CollectionDB::createStatsTableV8()
 986 {
 987     // create music statistics database - old form, for upgrade code.
 988     query( QString( "CREATE TABLE statistics ("
 989                     "url " + textColumnType() + " UNIQUE,"
 990                     "createdate INTEGER,"
 991                     "accessdate INTEGER,"
 992                     "percentage FLOAT,"
 993                     "rating INTEGER DEFAULT 0,"
 994                     "playcounter INTEGER,"
 995                     "uniqueid " + textColumnType(8) + " UNIQUE,"
 996                     "deleted BOOL DEFAULT " + boolF() + ");" ) );
 997
 998     query( "CREATE INDEX url_stats ON statistics( url );" );
 999     query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
1000     query( "CREATE INDEX rating_stats ON statistics( rating );" );
1001     query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
1002     query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
1003 }
1004
1005 //Old version, used in upgrade code
1006 void
1007 CollectionDB::createStatsTableV10( bool temp )
1008 {
1009     // create music statistics database
1010     query( QString( "CREATE %1 TABLE statistics%2 ("
1011                     "url " + exactTextColumnType() + ","
1012                     "deviceid INTEGER,"
1013                     "createdate INTEGER,"
1014                     "accessdate INTEGER,"
1015                     "percentage FLOAT,"
1016                     "rating INTEGER DEFAULT 0,"
1017                     "playcounter INTEGER,"
1018                     "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
1019                     "deleted BOOL DEFAULT " + boolF() + ","
1020                     "PRIMARY KEY(url, deviceid) );"
1021                     ).arg( temp ? "TEMPORARY" : "" )
1022                      .arg( temp ? "_fix_ten" : "" ) );
1023
1024     if ( !temp )
1025     {
1026         query( "CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );" );
1027         query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
1028         query( "CREATE INDEX rating_stats ON statistics( rating );" );
1029         query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
1030         query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
1031     }
1032 }
1033
1034
1035 void
1036 CollectionDB::dropStatsTable()
1037 {
1038     query( "DROP TABLE statistics;" );
1039 }
1040
1041 void
1042 CollectionDB::dropStatsTableV1()
1043 {
1044     query( "DROP TABLE statistics;" );
1045 }
1046
1047 void
1048 CollectionDB::createPersistentTables()
1049 {
1050     // create amazon table
1051     query(          "CREATE TABLE amazon ( "
1052             "asin " + textColumnType(20) + ", "
1053             "locale " + textColumnType(2) + ", "
1054             "filename " + exactTextColumnType(33) + ", "
1055             "refetchdate INTEGER );" );
1056
1057     // create lyrics table
1058     query( QString( "CREATE TABLE lyrics ("
1059             "url " + exactTextColumnType() + ", "
1060             "deviceid INTEGER,"
1061             "lyrics " + longTextColumnType() + ", "
1062             "uniqueid " + exactTextColumnType(32) + ");" ) );
1063
1064     query( QString( "CREATE TABLE playlists ("
1065             "playlist " + textColumnType() + ", "
1066             "url " + exactTextColumnType() + ", "
1067             "tracknum INTEGER );" ) );
1068
1069     QString labelsAutoIncrement = "";
1070     if ( getDbConnectionType() == DbConnection::postgresql )
1071     {
1072         query( QString( "CREATE SEQUENCE labels_seq;" ) );
1073
1074         labelsAutoIncrement = QString("DEFAULT nextval('labels_seq')");
1075     }
1076     else if ( getDbConnectionType() == DbConnection::mysql )
1077     {
1078         labelsAutoIncrement = "AUTO_INCREMENT";
1079     }
1080
1081     //create labels tables
1082     query( QString( "CREATE TABLE labels ("
1083                     "id INTEGER PRIMARY KEY " + labelsAutoIncrement + ", "
1084                     "name " + textColumnType() + ", "
1085                     "type INTEGER);" ) );
1086
1087     query( QString( "CREATE TABLE tags_labels ("
1088                     "deviceid INTEGER,"
1089                     "url " + exactTextColumnType() + ", "
1090                     "uniqueid " + exactTextColumnType(32) + ", "      //m:n relationship, DO NOT MAKE UNIQUE!
1091                     "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
1092 }
1093
1094 void
1095 CollectionDB::createPersistentTablesV12()
1096 {
1097     // create amazon table
1098     query(          "CREATE TABLE amazon ( "
1099             "asin " + textColumnType(20) + ", "
1100             "locale " + textColumnType(2) + ", "
1101             "filename " + textColumnType(33) + ", "
1102             "refetchdate INTEGER );" );
1103
1104     // create lyrics table
1105     query( QString( "CREATE TABLE lyrics ("
1106             "url " + textColumnType() + ", "
1107             "lyrics " + longTextColumnType() + ");" ) );
1108
1109     // create labels table
1110     query( QString( "CREATE TABLE label ("
1111         "url " + textColumnType() + ","
1112         "label " + textColumnType() + ");" ) );
1113
1114     query( QString( "CREATE TABLE playlists ("
1115             "playlist " + textColumnType() + ", "
1116             "url " + textColumnType() + ", "
1117             "tracknum INTEGER );" ) );
1118
1119     query( "CREATE INDEX url_label ON label( url );" );
1120     query( "CREATE INDEX label_label ON label( label );" );
1121     query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
1122     query( "CREATE INDEX url_playlists ON playlists( url );" );
1123 }
1124
1125 void
1126 CollectionDB::createPersistentTablesV14( bool temp )
1127 {
1128     const QString a( temp ? "TEMPORARY" : "" );
1129     const QString b( temp ? "_fix" : "" );
1130
1131     // create amazon table
1132     query(  QString( "CREATE %1 TABLE amazon%2 ( "
1133             "asin " + textColumnType(20) + ", "
1134             "locale " + textColumnType(2) + ", "
1135             "filename " + exactTextColumnType(33) + ", "
1136             "refetchdate INTEGER );" ).arg( a,b ) );
1137
1138     // create lyrics table
1139     query( QString( "CREATE %1 TABLE lyrics%2 ("
1140             "url " + exactTextColumnType() + ", "
1141             "deviceid INTEGER,"
1142             "lyrics " + longTextColumnType() + ");" ).arg( a,b ) );
1143
1144     query( QString( "CREATE %1 TABLE playlists%2 ("
1145             "playlist " + textColumnType() + ", "
1146             "url " + exactTextColumnType() + ", "
1147             "tracknum INTEGER );" ).arg( a,b ) );
1148
1149     if ( !temp )
1150     {
1151         query(  "CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );" );
1152         query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
1153         query( "CREATE INDEX url_playlists ON playlists( url );" );
1154     }
1155 }
1156
1157 void
1158 CollectionDB::createPodcastTables()
1159 {
1160     QString podcastAutoIncrement = "";
1161     QString podcastFolderAutoInc = "";
1162     if ( getDbConnectionType() == DbConnection::postgresql )
1163     {
1164         query( QString( "CREATE SEQUENCE podcastepisode_seq;" ) );
1165
1166         query( QString( "CREATE SEQUENCE podcastfolder_seq;" ) );
1167
1168         podcastAutoIncrement = QString("DEFAULT nextval('podcastepisode_seq')");
1169         podcastFolderAutoInc = QString("DEFAULT nextval('podcastfolder_seq')");
1170     }
1171     else if ( getDbConnectionType() == DbConnection::mysql )
1172     {
1173         podcastAutoIncrement = "AUTO_INCREMENT";
1174         podcastFolderAutoInc = "AUTO_INCREMENT";
1175     }
1176
1177     // create podcast channels table
1178     query( QString( "CREATE TABLE podcastchannels ("
1179                     "url " + exactTextColumnType() + " UNIQUE,"
1180                     "title " + textColumnType() + ","
1181                     "weblink " + exactTextColumnType() + ","
1182                     "image " + exactTextColumnType() + ","
1183                     "comment " + longTextColumnType() + ","
1184                     "copyright "  + textColumnType() + ","
1185                     "parent INTEGER,"
1186                     "directory "  + textColumnType() + ","
1187                     "autoscan BOOL, fetchtype INTEGER, "
1188                     "autotransfer BOOL, haspurge BOOL, purgecount INTEGER );" ) );
1189
1190     // create podcast episodes table
1191     query( QString( "CREATE TABLE podcastepisodes ("
1192                     "id INTEGER PRIMARY KEY %1, "
1193                     "url " + exactTextColumnType() + " UNIQUE,"
1194                     "localurl " + exactTextColumnType() + ","
1195                     "parent " + exactTextColumnType() + ","
1196                     "guid " + exactTextColumnType() + ","
1197                     "title " + textColumnType() + ","
1198                     "subtitle " + textColumnType() + ","
1199                     "composer " + textColumnType() + ","
1200                     "comment " + longTextColumnType() + ","
1201                     "filetype "  + textColumnType() + ","
1202                     "createdate "  + textColumnType() + ","
1203                     "length INTEGER,"
1204                     "size INTEGER,"
1205                     "isNew BOOL );" )
1206                     .arg( podcastAutoIncrement ) );
1207
1208     // create podcast folders table
1209     query( QString( "CREATE TABLE podcastfolders ("
1210                     "id INTEGER PRIMARY KEY %1, "
1211                     "name " + textColumnType() + ","
1212                     "parent INTEGER, isOpen BOOL );" )
1213                     .arg( podcastFolderAutoInc ) );
1214
1215     query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
1216     query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
1217     query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
1218     query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
1219 }
1220
1221 void
1222 CollectionDB::createPodcastTablesV2( bool temp )
1223 {
1224     const QString a( temp ? "TEMPORARY" : "" );
1225     const QString b( temp ? "_fix" : "" );
1226
1227     QString podcastAutoIncrement = "";
1228     QString podcastFolderAutoInc = "";
1229     if ( getDbConnectionType() == DbConnection::postgresql )
1230     {
1231         query( QString( "CREATE SEQUENCE podcastepisode_seq;" ) );
1232
1233         query( QString( "CREATE SEQUENCE podcastfolder_seq;" ) );
1234
1235         podcastAutoIncrement = QString("DEFAULT nextval('podcastepisode_seq')");
1236         podcastFolderAutoInc = QString("DEFAULT nextval('podcastfolder_seq')");
1237     }
1238     else if ( getDbConnectionType() == DbConnection::mysql )
1239     {
1240         podcastAutoIncrement = "AUTO_INCREMENT";
1241         podcastFolderAutoInc = "AUTO_INCREMENT";
1242     }
1243
1244     // create podcast channels table
1245     query( QString( "CREATE %1 TABLE podcastchannels%2 ("
1246                     "url " + exactTextColumnType() + " UNIQUE,"
1247                     "title " + textColumnType() + ","
1248                     "weblink " + exactTextColumnType() + ","
1249                     "image " + exactTextColumnType() + ","
1250                     "comment " + longTextColumnType() + ","
1251                     "copyright "  + textColumnType() + ","
1252                     "parent INTEGER,"
1253                     "directory "  + textColumnType() + ","
1254                     "autoscan BOOL, fetchtype INTEGER, "
1255                     "autotransfer BOOL, haspurge BOOL, purgecount INTEGER );" ).arg( a,b ) );
1256
1257     // create podcast episodes table
1258     query( QString( "CREATE %2 TABLE podcastepisodes%3 ("
1259                     "id INTEGER PRIMARY KEY %1, "
1260                     "url " + exactTextColumnType() + " UNIQUE,"
1261                     "localurl " + exactTextColumnType() + ","
1262                     "parent " + exactTextColumnType() + ","
1263                     "guid " + exactTextColumnType() + ","
1264                     "title " + textColumnType() + ","
1265                     "subtitle " + textColumnType() + ","
1266                     "composer " + textColumnType() + ","
1267                     "comment " + longTextColumnType() + ","
1268                     "filetype "  + textColumnType() + ","
1269                     "createdate "  + textColumnType() + ","
1270                     "length INTEGER,"
1271                     "size INTEGER,"
1272                     "isNew BOOL );" )
1273                     .arg( podcastAutoIncrement, a, b ) );
1274
1275     // create podcast folders table
1276     query( QString( "CREATE %2 TABLE podcastfolders%3 ("
1277                     "id INTEGER PRIMARY KEY %1, "
1278                     "name " + textColumnType() + ","
1279                     "parent INTEGER, isOpen BOOL );" )
1280                     .arg( podcastFolderAutoInc, a, b ) );
1281
1282     if ( !temp )
1283     {
1284         query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
1285         query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
1286         query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
1287         query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
1288     }
1289 }
1290
1291
1292 void
1293 CollectionDB::dropPersistentTables()
1294 {
1295     query( "DROP TABLE amazon;" );
1296     query( "DROP TABLE lyrics;" );
1297     query( "DROP TABLE playlists;" );
1298     query( "DROP TABLE tags_labels;" );
1299     query( "DROP TABLE labels;" );
1300 }
1301
1302 void
1303 CollectionDB::dropPersistentTablesV14()
1304 {
1305     query( "DROP TABLE amazon;" );
1306     query( "DROP TABLE lyrics;" );
1307     query( "DROP TABLE label;" );
1308     query( "DROP TABLE playlists;" );
1309 }
1310
1311 void
1312 CollectionDB::dropPodcastTables()
1313 {
1314     query( "DROP TABLE podcastchannels;" );
1315     query( "DROP TABLE podcastepisodes;" );
1316     query( "DROP TABLE podcastfolders;" );
1317 }
1318
1319 void
1320 CollectionDB::dropPodcastTablesV2()
1321 {
1322     query( "DROP TABLE podcastchannels;" );
1323     query( "DROP TABLE podcastepisodes;" );
1324     query( "DROP TABLE podcastfolders;" );
1325 }
1326
1327 void
1328 CollectionDB::dropDevicesTable()
1329 {
1330     query( "DROP TABLE devices;" );
1331 }
1332
1333 uint
1334 CollectionDB::artistID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1335 {
1336     // lookup cache
1337     if ( m_validArtistCache && m_cacheArtist[(int)temporary] == value )
1338         return m_cacheArtistID[(int)temporary];
1339
1340     uint id;
1341     if ( exact )
1342         id = IDFromExactValue( "artist", value, autocreate, temporary ).toUInt();
1343     else
1344         id = IDFromValue( "artist", value, autocreate, temporary );
1345
1346     // cache values
1347     m_cacheArtist[(int)temporary] = value;
1348     m_cacheArtistID[(int)temporary] = id;
1349     m_validArtistCache = 1;
1350
1351     return id;
1352 }
1353
1354
1355 QString
1356 CollectionDB::artistValue( uint id )
1357 {
1358     // lookup cache
1359     if ( m_cacheArtistID[0] == id )
1360         return m_cacheArtist[0];
1361
1362     QString value = valueFromID( "artist", id );
1363
1364     // cache values
1365     m_cacheArtist[0] = value;
1366     m_cacheArtistID[0] = id;
1367
1368     return value;
1369 }
1370
1371
1372 uint
1373 CollectionDB::composerID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1374 {
1375     // lookup cache
1376     if ( m_validComposerCache && m_cacheComposer[(int)temporary] == value )
1377         return m_cacheComposerID[(int)temporary];
1378
1379     uint id;
1380     if ( exact )
1381         id = IDFromExactValue( "composer", value, autocreate, temporary ).toUInt();
1382     else
1383         id = IDFromValue( "composer", value, autocreate, temporary );
1384
1385     // cache values
1386     m_cacheComposer[(int)temporary] = value;
1387     m_cacheComposerID[(int)temporary] = id;
1388     m_validComposerCache = 1;
1389
1390     return id;
1391 }
1392
1393
1394 QString
1395 CollectionDB::composerValue( uint id )
1396 {
1397     // lookup cache
1398     if ( m_cacheComposerID[0] == id )
1399         return m_cacheComposer[0];
1400
1401     QString value = valueFromID( "composer", id );
1402
1403     // cache values
1404     m_cacheComposer[0] = value;
1405     m_cacheComposerID[0] = id;
1406
1407     return value;
1408 }
1409
1410
1411 uint
1412 CollectionDB::albumID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1413 {
1414     // lookup cache
1415     if ( m_validAlbumCache && m_cacheAlbum[(int)temporary] == value )
1416         return m_cacheAlbumID[(int)temporary];
1417
1418     uint id;
1419     if ( exact )
1420         id = IDFromExactValue( "album", value, autocreate, temporary ).toUInt();
1421     else
1422         id = IDFromValue( "album", value, autocreate, temporary );
1423
1424     // cache values
1425     m_cacheAlbum[(int)temporary] = value;
1426     m_cacheAlbumID[(int)temporary] = id;
1427     m_validAlbumCache = 1;
1428
1429     return id;
1430 }
1431
1432 QString
1433 CollectionDB::albumValue( uint id )
1434 {
1435     // lookup cache
1436     if ( m_cacheAlbumID[0] == id )
1437         return m_cacheAlbum[0];
1438
1439     QString value = valueFromID( "album", id );
1440
1441     // cache values
1442     m_cacheAlbum[0] = value;
1443     m_cacheAlbumID[0] = id;
1444
1445     return value;
1446 }
1447
1448 uint
1449 CollectionDB::genreID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1450 {
1451     return exact ?
1452         IDFromExactValue( "genre", value, autocreate, temporary ).toUInt() :
1453         IDFromValue( "genre", value, autocreate, temporary );
1454 }
1455
1456 QString
1457 CollectionDB::genreValue( uint id )
1458 {
1459     return valueFromID( "genre", id );
1460 }
1461
1462
1463 uint
1464 CollectionDB::yearID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1465 {
1466     return exact ?
1467         IDFromExactValue( "year", value, autocreate, temporary ).toUInt() :
1468         IDFromValue( "year", value, autocreate, temporary );
1469 }
1470
1471
1472 QString
1473 CollectionDB::yearValue( uint id )
1474 {
1475     return valueFromID( "year", id );
1476 }
1477
1478
1479 uint
1480 CollectionDB::IDFromValue( QString name, QString value, bool autocreate, const bool temporary )
1481 {
1482     if ( temporary )
1483         name.append( "_temp" );
1484 //    what the hell is the reason for this?
1485 //    else
1486 //        conn = NULL;
1487
1488     QStringList values =
1489         query( QString(
1490             "SELECT id, name FROM %1 WHERE name %2;" )
1491             .arg( name )
1492             .arg( CollectionDB::likeCondition( value ) ) );
1493
1494     //check if item exists. if not, should we autocreate it?
1495     uint id;
1496     if ( values.isEmpty() && autocreate )
1497     {
1498         id = insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
1499                         .arg( name )
1500                         .arg( CollectionDB::instance()->escapeString( value ) ), name );
1501
1502         return id;
1503     }
1504
1505     return values.isEmpty() ? 0 : values.first().toUInt();
1506 }
1507
1508
1509 QString
1510 CollectionDB::valueFromID( QString table, uint id )
1511 {
1512     QStringList values =
1513         query( QString(
1514             "SELECT name FROM %1 WHERE id=%2;" )
1515             .arg( table )
1516             .arg( id ) );
1517
1518
1519     return values.isEmpty() ? 0 : values.first();
1520 }
1521
1522
1523 QString
1524 CollectionDB::albumSongCount( const QString &artist_id, const QString &album_id )
1525 {
1526     QStringList values =
1527         query( QString(
1528             "SELECT COUNT( url ) FROM tags WHERE album = %1 AND artist = %2;" )
1529             .arg( album_id )
1530             .arg( artist_id ) );
1531     return values.first();
1532 }
1533
1534 bool
1535 CollectionDB::albumIsCompilation( const QString &album_id )
1536 {
1537     QStringList values =
1538         query( QString(
1539             "SELECT sampler FROM tags WHERE sampler=%1 AND album=%2" )
1540             .arg( CollectionDB::instance()->boolT() )
1541             .arg( album_id ) );
1542
1543     return (values.count() != 0);
1544 }
1545
1546 QStringList
1547 CollectionDB::albumTracks( const QString &artist_id, const QString &album_id )
1548 {
1549     QueryBuilder qb;
1550     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
1551     qb.addMatch( QueryBuilder::tabAlbum, QueryBuilder::valID, album_id );
1552     const bool isCompilation = albumIsCompilation( album_id );
1553     if( !isCompilation )
1554         qb.addMatch( QueryBuilder::tabArtist, QueryBuilder::valID, artist_id );
1555     qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
1556     qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valTrack );
1557     QStringList ret = qb.run();
1558
1559     uint returnValues = qb.countReturnValues();
1560     if ( returnValues > 1 )
1561     {
1562         QStringList ret2;
1563         for ( QStringList::size_type i = 0; i < ret.size(); i += returnValues )
1564             ret2 << ret[ i ];
1565         return ret2;
1566     }
1567     else
1568         return ret;
1569 }
1570
1571 QStringList
1572 CollectionDB::albumDiscTracks( const QString &artist_id, const QString &album_id, const QString &discNumber)
1573 {
1574     QStringList rs;
1575     rs = query( QString( "SELECT tags.deviceid, tags.url FROM tags, year WHERE tags.album = %1 AND "
1576                          "tags.artist = %2 AND year.id = tags.year AND tags.discnumber = %3 "
1577                          + deviceidSelection() + " ORDER BY tags.track;" )
1578                 .arg( album_id )
1579                 .arg( artist_id )
1580                 .arg( discNumber ) );
1581     QStringList result;
1582     oldForeach( rs )
1583     {
1584         const int id = (*it).toInt();
1585         result << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
1586     }
1587     return result;
1588 }
1589
1590 QStringList
1591 CollectionDB::artistTracks( const QString &artist_id )
1592 {
1593     QStringList rs = query( QString( "SELECT tags.deviceid, tags.url FROM tags, album "
1594                 "WHERE tags.artist = '%1' AND album.id = tags.album " + deviceidSelection() +
1595                 "ORDER BY album.name, tags.discnumber, tags.track;" )
1596             .arg( artist_id ) );
1597     QStringList result = QStringList();
1598     oldForeach( rs )
1599     {
1600         const int id = (*it).toInt();
1601         result << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
1602     }
1603     return result;
1604 }
1605
1606
1607 void
1608 CollectionDB::addImageToAlbum( const QString& image, Q3ValueList< QPair<QString, QString> > info, const bool temporary )
1609 {
1610     int deviceid = MountPointManager::instance()->getIdForUrl( image );
1611     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, image );
1612     for ( Q3ValueList< QPair<QString, QString> >::ConstIterator it = info.begin(); it != info.end(); ++it )
1613     {
1614         if ( (*it).first.isEmpty() || (*it).second.isEmpty() )
1615             continue;
1616
1617         QString sql = QString( "INSERT INTO images%1 ( path, deviceid, artist, album ) VALUES ( '%3', %2" )
1618                             .arg( temporary ? "_temp" : "" )
1619                             .arg( deviceid )
1620                             .arg( escapeString( rpath ) );
1621         sql += QString( ", '%1'" ).arg( escapeString( (*it).first ) );
1622         sql += QString( ", '%1' );" ).arg( escapeString( (*it).second ) );
1623
1624 //         debug() << "Added image for album: " << (*it).first << " - " << (*it).second << ": " << image << endl;
1625         insert( sql, NULL );
1626     }
1627 }
1628
1629 void
1630 CollectionDB::addEmbeddedImage( const QString& path, const QString& hash, const QString& description )
1631 {
1632 //     debug() << "Added embedded image hash " << hash << " for file " << path << endl;
1633     //TODO: figure out what this embedded table does and then add the necessary code
1634     //what are embedded images anyway?
1635     int deviceid = MountPointManager::instance()->getIdForUrl( path );
1636     QString rpath = MountPointManager::instance()->getRelativePath(deviceid, path );
1637     insert( QString( "INSERT INTO embed_temp ( url, deviceid, hash, description ) VALUES ( '%2', %1, '%3', '%4' );" )
1638                 .arg( deviceid )
1639                 .arg( escapeString( rpath ), escapeString( hash ), escapeString( description ) ), NULL );
1640 }
1641
1642 void
1643 CollectionDB::removeOrphanedEmbeddedImages()
1644 {
1645     //TODO refactor
1646     // do it the hard way, since a delete subquery wont work on MySQL
1647     QStringList orphaned = query( "SELECT embed.deviceid, embed.url FROM embed LEFT JOIN tags ON embed.url = tags.url AND embed.deviceid = tags.deviceid WHERE tags.url IS NULL;" );
1648     oldForeach( orphaned ) {
1649         QString deviceid = *it;
1650         QString rpath = *(++it);
1651         query( QString( "DELETE FROM embed WHERE embed.deviceid = %1 AND embed.url = '%2';" )
1652                 .arg( deviceid, escapeString( rpath ) ) );
1653     }
1654 }
1655
1656 QPixmap
1657 CollectionDB::createDragPixmapFromSQL( const QString &sql, QString textOverRide )
1658 {
1659     // it is too slow to check if the url is actually in the colleciton.
1660     //TODO mountpointmanager: figure out what has to be done here
1661     QStringList values = instance()->query( sql );
1662     KUrl::List list;
1663     oldForeach( values )
1664     {
1665         KUrl u = KUrl( *it );
1666         if( u.isValid() )
1667             list += u;
1668     }
1669     return createDragPixmap( list, textOverRide );
1670 }
1671
1672 QPixmap
1673 CollectionDB::createDragPixmap( const KUrl::List &urls, QString textOverRide )
1674 {
1675     // settings
1676     const int maxCovers = 4; // maximum number of cover images to show
1677     const int coverSpacing = 20; // spacing between stacked covers
1678     const int fontSpacing = 5; // spacing between covers and info text
1679     const int coverW = AmarokConfig::coverPreviewSize() > 100 ? 100 : AmarokConfig::coverPreviewSize();
1680     const int coverH = coverW;
1681     const int margin = 2; //px margin
1682
1683     int covers      = 0;
1684     int songs       = 0;
1685     int pixmapW     = 0;
1686     int pixmapH     = 0;
1687     int remoteUrls  = 0;
1688     int playlists   = 0;
1689
1690     QMap<QString, int> albumMap;
1691     QPixmap coverPm[maxCovers];
1692
1693     QString song, album;
1694
1695
1696     // iterate urls, get covers and count artist/albums
1697     bool correctAlbumCount = true;
1698     KUrl::List::ConstIterator it = urls.begin();
1699     for ( ; it != urls.end(); ++it )
1700     {
1701         if( PlaylistFile::isPlaylistFile( *it )
1702             || (*it).protocol() == "playlist" || (*it).protocol() == "smartplaylist"
1703             || (*it).protocol() == "dynamic" )
1704         {
1705             playlists++;
1706         }
1707         else if( (*it).isLocalFile() )
1708         {
1709             songs++;
1710
1711             if( covers >= maxCovers )
1712             {
1713                 correctAlbumCount = false;
1714                 continue;
1715             }
1716
1717             MetaBundle mb( *it );
1718
1719             song = mb.title();
1720             album = mb.album();
1721
1722             QString artist = mb.artist();
1723             if( mb.compilation() == MetaBundle::CompilationYes )
1724                 artist = QString( "Various_AMAROK_Artists" ); // magic key for the albumMap!
1725
1726             if( !albumMap.contains( artist + album ) )
1727             {
1728                 albumMap[ artist + album ] = 1;
1729                 QString coverName = CollectionDB::instance()->albumImage( mb.artist(), album, false, coverW );
1730
1731                 if ( !coverName.endsWith( "@nocover.png" ) )
1732                     coverPm[covers++].load( coverName );
1733             }
1734         }
1735         else
1736         {
1737             MetaBundle mb( *it );
1738             if( !albumMap.contains( mb.artist() + mb.album() ) )
1739             {
1740                 albumMap[ mb.artist() + mb.album() ] = 1;
1741                 QString coverName = CollectionDB::instance()->podcastImage( mb, false, coverW );
1742
1743                 if ( covers < maxCovers && !coverName.endsWith( "@nocover.png" ) )
1744                     coverPm[covers++].load( coverName );
1745             }
1746             remoteUrls++;
1747         }
1748     }
1749
1750     // make better text...
1751     int albums = albumMap.count();
1752     QString text;
1753
1754     if( !textOverRide.isEmpty() )
1755     {
1756         text = textOverRide;
1757     }
1758     else if( ( songs && remoteUrls ) ||
1759              ( songs && playlists  ) ||
1760              ( playlists && remoteUrls ) )
1761     {
1762         text = i18np( "One item", "%1 items", songs + remoteUrls + playlists );
1763     }
1764     else if( songs > 0 )
1765     {
1766         if( correctAlbumCount ) {
1767             text = i18nc( "X songs from X albums", "%2 from %1" );
1768             text = text.arg( albums == 1 && !album.isEmpty() ? album : i18np( "one album", "%1 albums",albums ) );
1769         }
1770         else
1771             text = "%1";
1772         text = text.arg( songs == 1 && !song.isEmpty() ? song : i18np( "One song", "%1 songs", songs ) );
1773     }
1774     else if( playlists > 0 )
1775         text = i18np( "One playlist", "%1 playlists", playlists );
1776     else if ( remoteUrls > 0 )
1777         text = i18np( "One remote file", "%1 remote files", remoteUrls );
1778     else
1779         text = i18n( "Unknown item" );
1780
1781     QFont font;
1782     QFontMetrics fm( font );
1783     int fontH = fm.height() + margin;
1784     int minWidth = fm.width( text ) + margin*2; //margin either side
1785
1786     if ( covers > 0 )
1787     {
1788         // insert "..." cover as first image if appropriate
1789         if ( covers < albums )
1790         {
1791             if ( covers < maxCovers ) covers++;
1792             for ( int i = maxCovers-1; i > 0; i-- )
1793                 coverPm[i] = coverPm[i-1];
1794
1795             QImage im( KStandardDirs::locate( "data","amarok/images/more_albums.png" ) );
1796             coverPm[0].convertFromImage( im.scaled( coverW, coverH, Qt::KeepAspectRatio, Qt::SmoothTransformation ) );
1797         }
1798
1799         pixmapH = coverPm[0].height();
1800         pixmapW = coverPm[0].width();
1801
1802         // caluclate pixmap height
1803         int dW, dH;
1804         for ( int i = 1; i < covers; i++ )
1805         {
1806             dW = coverPm[i].width() - coverPm[i-1].width() + coverSpacing;
1807             dH = coverPm[i].height() - coverPm[i-1].height() + coverSpacing;
1808             if ( dW > 0 ) pixmapW += dW;
1809             if ( dH > 0 ) pixmapH += dH;
1810         }
1811         pixmapH += fontSpacing + fontH;
1812
1813         if ( pixmapW < minWidth )
1814             pixmapW = minWidth;
1815     }
1816     else
1817     {
1818         pixmapW = minWidth;
1819         pixmapH = fontH;
1820     }
1821
1822     QPixmap pmdrag( pixmapW, pixmapH );
1823     QPixmap pmtext( pixmapW, fontH );
1824
1825     QPalette palette = QToolTip::palette();
1826
1827     QPainter p;
1828     p.begin( &pmtext );
1829     p.fillRect( 0, 0, pixmapW, fontH, QBrush( Qt::black ) ); // border
1830     p.fillRect( 1, 1, pixmapW-margin, fontH-margin, palette.brush( QPalette::Normal, QColorGroup::Background ) );
1831     p.setBrush( palette.color( QPalette::Normal, QColorGroup::Text ) );
1832     p.setFont( font );
1833     p.drawText( margin, fm.ascent() + 1, text );
1834     p.end();
1835
1836     QBitmap pmtextMask(pixmapW, fontH);
1837     pmtextMask.fill( Qt::color1 );
1838
1839     // when we have found no covers, just display the text message
1840     if( !covers )
1841     {
1842         pmtext.setMask(pmtextMask);
1843         return pmtext;
1844     }
1845
1846     // compose image
1847     p.begin( &pmdrag );
1848     for ( int i = 0; i < covers; i++ )
1849         bitBlt( &pmdrag, i * coverSpacing, i * coverSpacing, &coverPm[i], 0 );
1850
1851     bitBlt( &pmdrag, 0, pixmapH - fontH, &pmtext, 0 );
1852     p.end();
1853
1854     QBitmap pmdragMask( pmdrag.size(), true );
1855     for ( int i = 0; i < covers; i++ )
1856     {
1857         QBitmap coverMask( coverPm[i].width(), coverPm[i].height() );
1858         coverMask.fill( Qt::color1 );
1859         bitBlt( &pmdragMask, i * coverSpacing, i * coverSpacing, &coverMask, 0 );
1860     }
1861     bitBlt( &pmdragMask, 0, pixmapH - fontH, &pmtextMask, 0 );
1862     pmdrag.setMask( pmdragMask );
1863
1864     return pmdrag;
1865 }
1866
1867 QImage
1868 CollectionDB::fetchImage( const KUrl& url, QString &/*tmpFile*/ )
1869 {
1870     if ( url.protocol() != "file" )
1871     {
1872         QString tmpFile;
1873         KIO::NetAccess::download( url, tmpFile, 0 ); //TODO set 0 to the window, though it probably doesn't really matter
1874         return QImage( tmpFile );
1875     }
1876     else
1877     {
1878         return QImage( url.path() );
1879     }
1880
1881 }
1882 bool
1883 CollectionDB::setAlbumImage( const QString& artist, const QString& album, const KUrl& url )
1884 {
1885     QString tmpFile;
1886     bool success = setAlbumImage( artist, album, fetchImage(url, tmpFile) );
1887     KIO::NetAccess::removeTempFile( tmpFile ); //only removes file if it was created with NetAccess
1888     return success;
1889 }
1890
1891
1892 bool
1893 CollectionDB::setAlbumImage( const QString& artist, const QString& album, QImage img, const QString& amazonUrl, const QString& asin )
1894 {
1895     //show a wait cursor for the duration
1896     Amarok::OverrideCursor keep;
1897
1898     const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
1899     const QString artist_ = isCompilation ? "" : artist;
1900
1901     // remove existing album covers
1902     removeAlbumImage( artist_, album );
1903
1904     QByteArray key = md5sum( artist_, album );
1905     newAmazonReloadDate(asin, AmarokConfig::amazonLocale(), key);
1906     // Save Amazon product page URL as embedded string, for later retreival
1907     if ( !amazonUrl.isEmpty() )
1908         img.setText( "amazon-url", 0, amazonUrl );
1909
1910     const bool b = img.save( largeCoverDir().filePath( key ), "PNG");
1911     emit coverChanged( artist_, album );
1912     return b;
1913 }
1914
1915
1916 QString
1917 CollectionDB::podcastImage( const MetaBundle &bundle, const bool withShadow, uint width )
1918 {
1919     PodcastEpisodeBundle peb;
1920     PodcastChannelBundle pcb;
1921
1922     KUrl url = bundle.url().url();
1923
1924     if( getPodcastEpisodeBundle( url, &peb ) )
1925     {
1926         url = peb.parent().url();
1927     }
1928
1929     if( getPodcastChannelBundle( url, &pcb ) )
1930     {
1931         if( pcb.imageURL().isValid() )
1932             return podcastImage( pcb.imageURL().url(), withShadow, width );
1933     }
1934
1935     return notAvailCover( withShadow, width );
1936 }
1937
1938
1939 QString
1940 CollectionDB::podcastImage( const QString &remoteURL, const bool withShadow, uint width )
1941 {
1942     // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1943     // width of 0 == full size
1944     if( width == 1 )
1945         width = AmarokConfig::coverPreviewSize();
1946
1947     QString s = findAmazonImage( "Podcast", remoteURL, width );
1948
1949     if( s.isEmpty() )
1950     {
1951         s = notAvailCover( withShadow, width );
1952
1953         const KUrl url = KUrl( remoteURL );
1954         if( url.isValid() ) //KIO crashes with invalid URLs
1955         {
1956             KIO::Job *job = KIO::storedGet( url, false, false );
1957             m_podcastImageJobs[job] = remoteURL;
1958             connect( job, SIGNAL( result( KIO::Job* ) ), SLOT( podcastImageResult( KIO::Job* ) ) );
1959         }
1960     }
1961
1962     if ( withShadow )
1963         s = makeShadowedImage( s );
1964
1965     return s;
1966 }
1967
1968 void
1969 CollectionDB::podcastImageResult( KIO::Job *gjob )
1970 {
1971     QString url = m_podcastImageJobs[gjob];
1972     m_podcastImageJobs.remove( gjob );
1973
1974     KIO::StoredTransferJob *job = dynamic_cast<KIO::StoredTransferJob *>( gjob );
1975     if( !job )
1976     {
1977         debug() << "connected to wrong job type" << endl;
1978         return;
1979     }
1980
1981     if( job->error() )
1982     {
1983         debug() << "job finished with error" << endl;
1984         return;
1985     }
1986
1987     if( job->isErrorPage() )
1988     {
1989         debug() << "error page" << endl;
1990         return;
1991     }
1992
1993     QImage image( job->data() );
1994     if( !image.isNull() )
1995     {
1996        if( url.isEmpty() )
1997           url = job->url().url();
1998
1999         QByteArray key = md5sum( "Podcast", url );
2000         if( image.save( largeCoverDir().filePath( key ), "PNG") )
2001            emit imageFetched( url );
2002     }
2003 }
2004
2005
2006 QString
2007 CollectionDB::albumImage( const QString &artist, const QString &album, bool withShadow, uint width, bool* embedded )
2008 {
2009     QString s;
2010     // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
2011     // width of 0 == full size
2012     if( width == 1 )
2013         width = AmarokConfig::coverPreviewSize();
2014     if( embedded )
2015         *embedded = false;
2016
2017     s = findAmazonImage( artist, album, width );
2018
2019     if( s.isEmpty() )
2020         s = findAmazonImage( "", album, width ); // handle compilations
2021
2022     if( s.isEmpty() )
2023         s = findDirectoryImage( artist, album, width );
2024
2025     if( s.isEmpty() )
2026     {
2027         s = findEmbeddedImage( artist, album, width );
2028         if( embedded && !s.isEmpty() )
2029             *embedded = true;
2030     }
2031
2032     if( s.isEmpty() )
2033         s = notAvailCover( withShadow, width );
2034
2035     if ( withShadow )
2036         s = makeShadowedImage( s );
2037
2038     return s;
2039 }
2040
2041
2042 QString
2043 CollectionDB::albumImage( const uint artist_id, const uint album_id, bool withShadow, uint width, bool* embedded )
2044 {
2045     return albumImage( artistValue( artist_id ), albumValue( album_id ), withShadow, width, embedded );
2046 }
2047
2048
2049 QString
2050 CollectionDB::albumImage( const MetaBundle &trackInformation, bool withShadow, uint width, bool* embedded )
2051 {
2052     QString s;
2053     if( width == 1 )
2054         width = AmarokConfig::coverPreviewSize();
2055
2056     QString album = trackInformation.album();
2057     QString artist = trackInformation.artist();
2058
2059     // this art is per track, so should check for it first
2060     s = findMetaBundleImage( trackInformation, width );
2061     if( embedded )
2062         *embedded = !s.isEmpty();
2063
2064     if( s.isEmpty() )
2065         s = findAmazonImage( artist, album, width );
2066     if( s.isEmpty() )
2067         s = findAmazonImage( "", album, width ); // handle compilations
2068     if( s.isEmpty() )
2069         s = findDirectoryImage( artist, album, width );
2070     if( s.isEmpty() )
2071         s = notAvailCover( withShadow, width );
2072     if ( withShadow )
2073         s = makeShadowedImage( s );
2074     return s;
2075 }
2076
2077
2078 QString
2079 CollectionDB::makeShadowedImage( const QString& albumImage, bool cache )
2080 {
2081     const QImage original( albumImage );
2082
2083     if( original.hasAlphaBuffer() )
2084         return albumImage;
2085
2086     const QFileInfo fileInfo( albumImage );
2087     const uint shadowSize = static_cast<uint>( original.width() / 100.0 * 6.0 );
2088     const QString cacheFile = fileInfo.fileName() + "@shadow";
2089     QImage shadow;
2090
2091     if ( !cache && cacheCoverDir().exists( cacheFile ) )
2092         return cacheCoverDir().filePath( cacheFile );
2093
2094     const QString folder = Amarok::saveLocation( "covershadow-cache/" );
2095     const QString file = QString( "shadow_albumcover%1x%2.png" ).arg( original.width() + shadowSize ).arg( original.height() + shadowSize  );
2096     if ( QFile::exists( folder + file ) )
2097         shadow.load( folder + file );
2098     else {
2099         shadow.load( KStandardDirs::locate( "data", "amarok/images/shadow_albumcover.png" ) );
2100         shadow = shadow.scaled( original.width() + shadowSize, original.height() + shadowSize, Qt::IgnoreAspectRatio, Qt::SmoothTransformation );
2101         shadow.save( folder + file, "PNG" );
2102     }
2103
2104     QImage target( shadow );
2105     bitBlt( &target, 0, 0, &original );
2106
2107     if ( cache ) {
2108         target.save( cacheCoverDir().filePath( cacheFile ), "PNG" );
2109         return cacheCoverDir().filePath( cacheFile );
2110     }
2111
2112     target.save( albumImage, "PNG" );
2113     return albumImage;
2114 }
2115
2116
2117 // Amazon Image
2118 QString
2119 CollectionDB::findAmazonImage( const QString &artist, const QString &album, uint width )
2120 {
2121     QByteArray widthKey = makeWidthKey( width );
2122
2123     if ( artist.isEmpty() && album.isEmpty() )
2124         return QString();
2125
2126     QByteArray key = md5sum( artist, album );
2127
2128     // check cache for existing cover
2129     if ( cacheCoverDir().exists( widthKey + key ) )
2130         return cacheCoverDir().filePath( widthKey + key );
2131
2132     // we need to create a scaled version of this cover
2133     QDir imageDir = largeCoverDir();
2134     if ( imageDir.exists( key ) )
2135     {
2136         if ( width > 1 )
2137         {
2138             QImage img( imageDir.filePath( key ) );
2139             img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + key ), "PNG" );
2140
2141             return cacheCoverDir().filePath( widthKey + key );
2142         }
2143         else
2144             return imageDir.filePath( key );
2145     }
2146
2147     return QString();
2148 }
2149
2150
2151 QString
2152 CollectionDB::findDirectoryImage( const QString& artist, const QString& album, uint width )
2153 {
2154     if ( width == 1 )
2155         width = AmarokConfig::coverPreviewSize();
2156     QByteArray widthKey = makeWidthKey( width );
2157     if ( album.isEmpty() )
2158         return QString();
2159
2160     IdList list = MountPointManager::instance()->getMountedDeviceIds();
2161     QString deviceIds;
2162     oldForeachType( IdList, list )
2163     {
2164         if ( !deviceIds.isEmpty() ) deviceIds = deviceIds + ",";
2165         deviceIds += QString::number(*it);
2166     }
2167
2168     QStringList rs;
2169     if ( artist == i18n( "Various Artists" ) || artist.isEmpty() )
2170     {
2171          rs = query( QString(
2172             "SELECT images.deviceid,images.path FROM images, artist, tags "
2173             "WHERE images.artist = artist.name "
2174             "AND artist.id = tags.artist "
2175             "AND tags.sampler = %1 "
2176             "AND images.album %2 "
2177             "AND images.deviceid IN (%3) " )
2178                  .arg( boolT() )
2179                  .arg( CollectionDB::likeCondition( album ) )
2180                  .arg( deviceIds ) );
2181     }
2182     else
2183     {
2184         rs = query( QString(
2185             "SELECT images.deviceid,images.path FROM images WHERE artist %1 AND album %2 AND deviceid IN (%3) ORDER BY path;" )
2186                     .arg( CollectionDB::likeCondition( artist ) )
2187                     .arg( CollectionDB::likeCondition( album ) )
2188                     .arg( deviceIds ) );
2189     }
2190     QStringList values = URLsFromQuery( rs );
2191     if ( !values.isEmpty() )
2192     {
2193         QString image( values.first() );
2194         uint matches = 0;
2195         uint maxmatches = 0;
2196         QRegExp iTunesArt( "^AlbumArt_.*Large" );
2197         for ( uint i = 0; i < values.count(); i++ )
2198         {
2199              matches = values[i].count( "front", Qt::CaseInsensitive ) + values[i].count( "cover", Qt::CaseInsensitive ) + values[i].count( "folder", Qt::CaseInsensitive ) + values[i].count( iTunesArt );
2200             if ( matches > maxmatches )
2201             {
2202                 image = values[i];
2203                 maxmatches = matches;
2204             }
2205         }
2206
2207         QByteArray key = md5sum( artist, album, image );
2208
2209         if ( width > 1 )
2210         {
2211             QString path = cacheCoverDir().filePath( widthKey + key );
2212             if ( !QFile::exists( path ) )
2213             {
2214                 QImage img( image );
2215                 img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" );
2216             }
2217             return path;
2218         }
2219         else //large image
2220             return image;
2221     }
2222     return QString();
2223 }
2224
2225
2226 QString
2227 CollectionDB::findEmbeddedImage( const QString& artist, const QString& album, uint width )
2228 {
2229     // In the case of multiple embedded images, we arbitrarily choose one from the newest file
2230     // could potentially select multiple images within a file based on description, although a
2231     // lot of tagging software doesn't fill in that field, so we just get whatever the DB
2232     // happens to return for us
2233     QStringList rs;
2234     if ( artist == i18n("Various Artists") || artist.isEmpty() ) {
2235         // VAs need special handling to not match on artist name but instead check for sampler flag
2236         rs = query( QString(
2237                 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2238                 "tags INNER JOIN embed ON tags.url = embed.url "
2239                      "INNER JOIN album ON tags.album = album.id "
2240                 "WHERE "
2241                 "album.name = '%1' "
2242                 "AND tags.sampler = %2 "
2243                 "ORDER BY modifydate DESC LIMIT 1;" )
2244                 .arg( escapeString( album ) )
2245                 .arg( boolT() ) );
2246     } else {
2247         rs = query( QString(
2248                 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2249                 "tags INNER JOIN embed ON tags.url = embed.url "
2250                      "INNER JOIN artist ON tags.artist = artist.id "
2251                      "INNER JOIN album ON tags.album = album.id "
2252                 "WHERE  "
2253                 "artist.name = '%1' "
2254                 "AND album.name = '%2' "
2255                 "ORDER BY modifydate DESC LIMIT 1;" )
2256                 .arg( escapeString( artist ) )
2257                 .arg( escapeString( album ) ) );
2258     }
2259
2260     QStringList values = QStringList();
2261     if ( rs.count() == 3 ) {
2262         values += rs.first();
2263         values += MountPointManager::instance()->getAbsolutePath( rs[1].toInt(), rs[2] );
2264     }
2265
2266     if ( values.count() == 2 ) {
2267         QByteArray hash = values.first().toUtf8();
2268         QString result = loadHashFile( hash, width );
2269         if ( result.isEmpty() ) {
2270             // need to get original from file first
2271             MetaBundle mb(  KUrl( values.last() ) );
2272             if ( extractEmbeddedImage( mb, hash ) ) {
2273                 // try again, as should be possible now
2274                 result = loadHashFile( hash, width );
2275             }
2276         }
2277         return result;
2278     }
2279     return QString();
2280 }
2281
2282
2283 QString
2284 CollectionDB::findMetaBundleImage( const MetaBundle& trackInformation, uint width )
2285 {
2286     int deviceid = MountPointManager::instance()->getIdForUrl( trackInformation.url() );
2287     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, trackInformation.url().path() );
2288     QStringList values =
2289             query( QString(
2290             "SELECT embed.hash FROM tags LEFT JOIN embed ON tags.url = embed.url "
2291             " AND tags.deviceid = embed.deviceid WHERE tags.url = '%2' AND tags.deviceid = %1 ORDER BY hash DESC LIMIT 1;" )
2292             .arg( deviceid ).arg( escapeString( rpath ) ) );
2293
2294     if ( values.empty() || !values.first().isEmpty() ) {
2295         QByteArray hash;
2296         QString result;
2297         if( !values.empty() ) { // file in collection, so we know the hash
2298             hash = values.first().toUtf8();
2299             result = loadHashFile( hash, width );
2300         }
2301         if ( result.isEmpty() ) {
2302             // need to get original from file first
2303             if ( extractEmbeddedImage( trackInformation, hash ) ) {
2304                 // try again, as should be possible now
2305                 result = loadHashFile( hash, width );
2306             }
2307         }
2308         return result;
2309     }
2310     return QString();
2311 }
2312
2313
2314 QByteArray
2315 CollectionDB::makeWidthKey( uint width )
2316 {
2317     return QString::number( width ).local8Bit() + '@';
2318 }
2319
2320
2321 bool
2322 CollectionDB::removeAlbumImage( const QString &artist, const QString &album )
2323 {
2324     DEBUG_BLOCK
2325
2326     QByteArray widthKey = "*@";
2327     QByteArray key = md5sum( artist, album );
2328     query( "DELETE FROM amazon WHERE filename='" + key + '\'' );
2329
2330     // remove scaled versions of images (and add the asterisk for the shadow-caches)
2331     QStringList scaledList = cacheCoverDir().entryList( widthKey + key + '*' );
2332     if ( scaledList.count() > 0 )
2333         for ( uint i = 0; i < scaledList.count(); i++ )
2334             QFile::remove( cacheCoverDir().filePath( scaledList[ i ] ) );
2335
2336     bool deleted = false;
2337     // remove large, original image
2338     if ( largeCoverDir().exists( key ) && QFile::remove( largeCoverDir().filePath( key ) ) )
2339         deleted = true;
2340
2341     QString hardImage = findDirectoryImage( artist, album );
2342     debug() << "hardImage: " << hardImage << endl;
2343
2344     if( !hardImage.isEmpty() )
2345     {
2346         int id = MountPointManager::instance()->getIdForUrl( hardImage );
2347         QString rpath = MountPointManager::instance()->getRelativePath( id, hardImage );
2348         query( "DELETE FROM images WHERE path='" + escapeString( hardImage ) + "' AND deviceid = " + QString::number( id ) + ';' );
2349         deleted = true;
2350     }
2351
2352     if ( deleted )
2353     {
2354         emit coverRemoved( artist, album );
2355         return true;
2356     }
2357
2358     return false;
2359 }
2360
2361
2362 bool
2363 CollectionDB::removeAlbumImage( const uint artist_id, const uint album_id )
2364 {
2365     return removeAlbumImage( artistValue( artist_id ), albumValue( album_id ) );
2366 }
2367
2368
2369 QString
2370 CollectionDB::notAvailCover( const bool withShadow, int width )
2371 {
2372     if ( width <= 1 )
2373         width = AmarokConfig::coverPreviewSize();
2374     QString widthKey = QString::number( width ) + '@';
2375     QString s;
2376
2377     if( cacheCoverDir().exists( widthKey + "nocover.png" ) )
2378         s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2379     else
2380     {
2381         m_noCover.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + "nocover.png" ), "PNG" );
2382         s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2383     }
2384
2385     if ( withShadow )
2386         s = makeShadowedImage( s );
2387
2388     return s;
2389 }
2390
2391
2392 QStringList
2393 CollectionDB::artistList( bool withUnknowns, bool withCompilations )
2394 {
2395     QueryBuilder qb;
2396     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
2397
2398     if ( !withUnknowns )
2399         qb.excludeMatch( QueryBuilder::tabArtist, i18n( "Unknown" ) );
2400     if ( !withCompilations )
2401         qb.setOptions( QueryBuilder::optNoCompilations );
2402
2403     qb.groupBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2404     qb.setOptions( QueryBuilder::optShowAll );
2405     qb.sortBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2406     return qb.run();
2407 }
2408
2409
2410 QStringList
2411 CollectionDB::composerList( bool withUnknowns, bool withCompilations )
2412 {
2413     DEBUG_BLOCK
2414     QueryBuilder qb;
2415     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
2416
2417     if ( !withUnknowns )
2418         qb.excludeMatch( QueryBuilder::tabComposer, i18n( "Unknown" ) );
2419     if ( !withCompilations )
2420         qb.setOptions( QueryBuilder::optNoCompilations );
2421
2422     qb.groupBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2423     qb.setOptions( QueryBuilder::optShowAll );
2424     qb.sortBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2425     return qb.run();
2426 }
2427
2428
2429 QStringList
2430 CollectionDB::albumList( bool withUnknowns, bool withCompilations )
2431 {
2432     QueryBuilder qb;
2433     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
2434
2435     if ( !withUnknowns )
2436         qb.excludeMatch( QueryBuilder::tabAlbum, i18n( "Unknown" ) );
2437     if ( !withCompilations )
2438         qb.setOptions( QueryBuilder::optNoCompilations );
2439
2440     qb.groupBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2441     qb.setOptions( QueryBuilder::optShowAll );
2442     qb.sortBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2443     return qb.run();
2444 }
2445
2446
2447 QStringList
2448 CollectionDB::genreList( bool withUnknowns, bool withCompilations )
2449 {
2450     QueryBuilder qb;
2451     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
2452
2453     //Only report genres that currently have at least one song
2454     qb.addFilter( QueryBuilder::tabSong, "" );
2455
2456     if ( !withUnknowns )
2457         qb.excludeMatch( QueryBuilder::tabGenre, i18n( "Unknown" ) );
2458     if ( !withCompilations )
2459         qb.setOptions( QueryBuilder::optNoCompilations );
2460
2461     qb.groupBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2462     qb.setOptions( QueryBuilder::optShowAll );
2463     qb.sortBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2464     return qb.run();
2465 }
2466
2467
2468 QStringList
2469 CollectionDB::yearList( bool withUnknowns, bool withCompilations )
2470 {
2471     QueryBuilder qb;
2472     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
2473
2474     if ( !withUnknowns )
2475         qb.excludeMatch( QueryBuilder::tabYear, i18n( "Unknown" ) );
2476     if ( !withCompilations )
2477         qb.setOptions( QueryBuilder::optNoCompilations );
2478
2479     qb.groupBy( QueryBuilder::tabYear, QueryBuilder::valName );
2480     qb.setOptions( QueryBuilder::optShowAll );
2481     qb.sortBy( QueryBuilder::tabYear, QueryBuilder::valName );
2482     return qb.run();
2483 }
2484
2485 QStringList
2486 CollectionDB::labelList()
2487 {
2488     QueryBuilder qb;
2489     qb.addReturnValue( QueryBuilder::tabLabels, QueryBuilder::valName );
2490     qb.groupBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2491     qb.setOptions( QueryBuilder::optShowAll );
2492     qb.sortBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2493     return qb.run();
2494 }
2495
2496 QStringList
2497 CollectionDB::albumListOfArtist( const QString &artist, bool withUnknown, bool withCompilations )
2498 {
2499     if (getDbConnectionType() == DbConnection::postgresql)
2500     {
2501         return query( "SELECT DISTINCT album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2502                       "tags.album = album.id AND tags.artist = artist.id "
2503                       "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2504                       ( withUnknown ? QString() : "AND album.name <> '' " ) +
2505                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2506                       " ORDER BY lower( album.name );" );
2507     }
2508     else
2509     {
2510         return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE "
2511                       "tags.album = album.id AND tags.artist = artist.id "
2512                       "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2513                       ( withUnknown ? QString() : "AND album.name <> '' " ) +
2514                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2515                       " ORDER BY lower( album.name );" );
2516     }
2517 }
2518
2519
2520 QStringList
2521 CollectionDB::artistAlbumList( bool withUnknown, bool withCompilations )
2522 {
2523     if (getDbConnectionType() == DbConnection::postgresql)
2524     {
2525         return query( "SELECT DISTINCT artist.name, album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2526                       "tags.album = album.id AND tags.artist = artist.id " +
2527                       ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2528                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2529                       " ORDER BY lower( album.name );" );
2530     }
2531     else
2532     {
2533         return query( "SELECT DISTINCT artist.name, album.name FROM tags, album, artist WHERE "
2534                       "tags.album = album.id AND tags.artist = artist.id " +
2535                       ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2536                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2537                       " ORDER BY lower( album.name );" );
2538     }
2539 }
2540
2541 bool
2542 CollectionDB::addPodcastChannel( const PodcastChannelBundle &pcb, const bool &replace )
2543 {
2544     QString command;
2545     if( replace ) {
2546         command = "REPLACE INTO podcastchannels "
2547                   "( url, title, weblink, image, comment, copyright, parent, directory"
2548                   ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2549                   "VALUES (";
2550     } else {
2551         command = "INSERT INTO podcastchannels "
2552                   "( url, title, weblink, image, comment, copyright, parent, directory"
2553                   ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2554                   "VALUES (";
2555     }
2556
2557     QString title       = pcb.title();
2558     KUrl    link        = pcb.link();
2559     KUrl    image       = pcb.imageURL();
2560     QString description = pcb.description();
2561     QString copyright   = pcb.copyright();
2562
2563     if( title.isEmpty() )
2564         title = pcb.url().prettyUrl();
2565
2566     command += '\'' + escapeString( pcb.url().url() )  + "',";
2567     command += ( title.isEmpty() ?       "NULL" : '\'' + escapeString( title ) + '\'' ) + ',';
2568     command += ( link.isEmpty() ?        "NULL" : '\'' + escapeString( link.url() ) + '\'' ) + ',';
2569     command += ( image.isEmpty() ?       "NULL" : '\'' + escapeString( image.url() ) + '\'' ) + ',';
2570     command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2571     command += ( copyright.isEmpty() ?   "NULL" : '\'' + escapeString( copyright ) + '\'' ) + ',';
2572     command += QString::number( pcb.parentId() ) + ",'";
2573     command += escapeString( pcb.saveLocation() ) + "',";
2574     command += pcb.autoscan() ? boolT() + ',' : boolF() + ',';
2575     command += QString::number( pcb.fetchType() ) + ',';
2576     command += pcb.autotransfer() ? boolT() + ',' : boolF() + ',';
2577     command += pcb.hasPurge() ? boolT() + ',' : boolF() + ',';
2578     command += QString::number( pcb.purgeCount() ) + ");";
2579
2580     //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2581     // Now it might be possible as insert returns the rowid.
2582     insert( command, NULL );
2583     return true;
2584 }
2585
2586 int
2587 CollectionDB::addPodcastEpisode( const PodcastEpisodeBundle &episode, const int idToUpdate )
2588 {
2589     QString command;
2590
2591     if( idToUpdate ) {
2592         command = "REPLACE INTO podcastepisodes "
2593                   "( id, url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2594                   "VALUES (";
2595     } else {
2596         command = "INSERT INTO podcastepisodes "
2597                   "( url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2598                   "VALUES (";
2599     }
2600
2601     QString localurl    = episode.localUrl().url();
2602     QString title       = episode.title();
2603     QString subtitle    = episode.subtitle();
2604     QString author      = episode.author();
2605     QString description = episode.description();
2606     QString type        = episode.type();
2607     QString date        = episode.date();
2608     QString guid        = episode.guid();
2609     int     duration    = episode.duration();
2610     uint    size        = episode.size();
2611
2612     if( title.isEmpty() )
2613         title = episode.url().prettyUrl();
2614
2615     if( idToUpdate )
2616         command += QString::number( idToUpdate ) + ',';
2617
2618     command += '\'' + escapeString( episode.url().url() )   + "',";
2619     command += ( localurl.isEmpty()       ? "NULL" : '\'' + escapeString( localurl )       + '\'' ) + ',';
2620     command += '\'' + escapeString( episode.parent().url()) + "',";
2621     command += ( title.isEmpty()       ? "NULL" : '\'' + escapeString( title )       + '\'' ) + ',';
2622     command += ( subtitle.isEmpty()    ? "NULL" : '\'' + escapeString( subtitle )    + '\'' ) + ',';
2623     command += ( author.isEmpty()      ? "NULL" : '\'' + escapeString( author )      + '\'' ) + ',';
2624     command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2625     command += ( type.isEmpty()        ? "NULL" : '\'' + escapeString( type )        + '\'' ) + ',';
2626     command += ( date.isEmpty()        ? "NULL" : '\'' + escapeString( date )        + '\'' ) + ',';
2627     command += ( guid.isEmpty()        ? "NULL" : '\'' + escapeString( guid )        + '\'' ) + ',';
2628     command += QString::number( duration ) + ',';
2629     command += QString::number( size ) + ',';
2630     command += episode.isNew() ? boolT() + " );" : boolF() + " );";
2631
2632     insert( command, NULL );
2633
2634     if( idToUpdate ) return idToUpdate;
2635     //This is a bit of a hack. We have just inserted an item, so it is going to be the one with the
2636     //highest id.  Change this if threaded insertions are used in the future.
2637     QStringList values = query( QString("SELECT id FROM podcastepisodes WHERE url='%1' ORDER BY id DESC;")
2638                                         .arg( escapeString( episode.url().url() ) ) );
2639     if( values.isEmpty() ) return -1;
2640
2641     return values[0].toInt();
2642 }
2643
2644 Q3ValueList<PodcastChannelBundle>
2645 CollectionDB::getPodcastChannels()
2646 {
2647     QString command = "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2648         ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels;";
2649
2650     QStringList values = query( command );
2651     Q3ValueList<PodcastChannelBundle> bundles;
2652
2653     oldForeach( values )
2654     {
2655         PodcastChannelBundle pcb;
2656         pcb.setUrl         ( KUrl(*it) );
2657         pcb.setTitle       ( *++it );
2658         pcb.setLink        ( KUrl(*++it) );
2659         pcb.setImageURL    ( KUrl(*++it) );
2660         pcb.setDescription ( *++it );
2661         pcb.setCopyright   ( *++it );
2662         pcb.setParentId    ( (*++it).toInt() );
2663         pcb.setSaveLocation( *++it );
2664         pcb.setAutoScan    ( *++it == boolT() ? true : false );
2665         pcb.setFetchType   ( (*++it).toInt() );
2666         pcb.setAutoTransfer( *++it == boolT() ? true : false  );
2667         pcb.setPurge       ( *++it == boolT() ? true : false  );
2668         pcb.setPurgeCount  ( (*++it).toInt() );
2669
2670         bundles.append( pcb );
2671     }
2672
2673     return bundles;
2674 }
2675
2676 Q3ValueList<PodcastEpisodeBundle>
2677 CollectionDB::getPodcastEpisodes( const KUrl &parent, bool onlyNew, int limit )
2678 {
2679     QString command = QString( "SELECT id, url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE ( parent='%1'" ).arg( parent.url() );
2680     if( onlyNew )
2681         command += QString( " AND isNew='%1'" ).arg( boolT() );
2682     command += " ) ORDER BY id";
2683     if( limit != -1 )
2684         command += QString( " DESC LIMIT %1 OFFSET 0" ).arg( limit );
2685     command += ';';
2686
2687     QStringList values = query( command );
2688     Q3ValueList<PodcastEpisodeBundle> bundles;
2689
2690     oldForeach( values )
2691     {
2692         PodcastEpisodeBundle peb;
2693         peb.setDBId        ( (*it).toInt() );
2694         peb.setUrl         ( KUrl(*++it) );
2695         if( *++it != "NULL" )
2696             peb.setLocalURL    ( KUrl(*it) );
2697         peb.setParent      ( KUrl(*++it) );
2698         peb.setGuid        ( *++it );
2699         peb.setTitle       ( *++it );
2700         if( *++it != NULL )
2701             peb.setSubtitle( *it );
2702         peb.setAuthor      ( *++it );
2703         peb.setDescription ( *++it );
2704         peb.setType        ( *++it );
2705         peb.setDate        ( *++it );
2706         peb.setDuration    ( (*++it).toInt() );
2707         if( *++it == NULL )
2708             peb.setSize        ( 0 );
2709         else
2710             peb.setSize        ( (*it).toInt() );
2711         peb.setNew         ( (*++it) == boolT() ? true : false  );
2712
2713         bundles.append( peb );
2714     }
2715
2716     return bundles;
2717 }
2718
2719 PodcastEpisodeBundle
2720 CollectionDB::getPodcastEpisodeById( int id )
2721 {
2722     QString command = QString( "SELECT url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE id=%1;").arg( id );
2723
2724     QStringList values = query( command );
2725     PodcastEpisodeBundle peb;
2726     oldForeach( values )
2727     {
2728         peb.setDBId        ( id );
2729         peb.setUrl         ( KUrl(*it) );
2730         if( *++it != "NULL" )
2731             peb.setLocalURL( KUrl(*it) );
2732         peb.setParent      ( KUrl(*++it) );
2733         peb.setGuid        ( *++it );
2734         peb.setTitle       ( *++it );
2735         peb.setSubtitle    ( *++it );
2736         peb.setAuthor      ( *++it );
2737         peb.setDescription ( *++it );
2738         peb.setType        ( *++it );
2739         peb.setDate        ( *++it );
2740         peb.setDuration    ( (*++it).toInt() );
2741         if( *++it == NULL )
2742             peb.setSize    ( 0 );
2743         else
2744             peb.setSize    ( (*it).toInt() );
2745         peb.setNew         ( (*++it) == boolT() ? true : false  );
2746     }
2747
2748     return peb;
2749 }
2750
2751 bool
2752 CollectionDB::getPodcastEpisodeBundle( const KUrl &url, PodcastEpisodeBundle *peb )
2753 {
2754     int id = 0;
2755     if( url.isLocalFile() )
2756     {
2757         QStringList values =
2758             query( QString( "SELECT id FROM podcastepisodes WHERE localurl = '%1';" )
2759                     .arg( escapeString( url.url() ) ) );
2760         if( !values.isEmpty() )
2761             id = values[0].toInt();
2762     }
2763     else
2764     {
2765         QStringList values =
2766             query( QString( "SELECT id FROM podcastepisodes WHERE url = '%1';" )
2767                     .arg( escapeString( url.url() ) ) );
2768         if( !values.isEmpty() )
2769             id = values[0].toInt();
2770     }
2771
2772     if( id )
2773     {
2774         *peb = getPodcastEpisodeById( id );
2775         return true;
2776     }
2777
2778     return false;
2779 }
2780
2781 bool
2782 CollectionDB::getPodcastChannelBundle( const KUrl &url, PodcastChannelBundle *pcb )
2783 {
2784     QStringList values = query( QString(
2785                 "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2786                 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels WHERE url = '%1';"
2787                 ).arg( escapeString( url.url() ) ) );
2788
2789     oldForeach( values )
2790     {
2791         pcb->setUrl         ( KUrl(*it) );
2792         pcb->setTitle       ( *++it );
2793         pcb->setLink        ( KUrl(*++it) );
2794         if( *++it != "NULL" )
2795             pcb->setImageURL( KUrl(*it) );
2796         pcb->setDescription ( *++it );
2797         pcb->setCopyright   ( *++it );
2798         pcb->setParentId    ( (*++it).toInt() );
2799         pcb->setSaveLocation( *++it );
2800         pcb->setAutoScan    ( *++it == boolT() ? true : false );
2801         pcb->setFetchType   ( (*++it).toInt() );
2802         pcb->setAutoTransfer( *++it == boolT() ? true : false  );
2803         pcb->setPurge       ( *++it == boolT() ? true : false  );
2804         pcb->setPurgeCount  ( (*++it).toInt() );
2805     }
2806
2807     return !values.isEmpty();
2808 }
2809
2810 // return newly created folder id
2811 int
2812 CollectionDB::addPodcastFolder( const QString &name, const int parent_id, const bool isOpen )
2813 {
2814     QString command = QString( "INSERT INTO podcastfolders ( name, parent, isOpen ) VALUES ('" );
2815     command += escapeString( name )   + "',";
2816     command += QString::number( parent_id ) + ',';
2817     command += isOpen ? boolT() + ");" : boolF() + ");";
2818
2819     insert( command, NULL );
2820
2821     command = QString( "SELECT id FROM podcastfolders WHERE name = '%1' AND parent = '%2';" )
2822                        .arg( name, QString::number(parent_id) );
2823     QStringList values = query( command );
2824
2825     return values[0].toInt();
2826 }
2827
2828 void
2829 CollectionDB::updatePodcastChannel( const PodcastChannelBundle &b )
2830 {
2831     if( getDbConnectionType() == DbConnection::postgresql )
2832     {
2833         query( QStringx( "UPDATE podcastchannels SET title='%1', weblink='%2', comment='%3', "
2834                          "copyright='%4', parent=%5, directory='%6', autoscan=%7, fetchtype=%8, "
2835              "autotransfer=%9, haspurge=%10, purgecount=%11 WHERE url='%12';" )
2836            .args ( QStringList()
2837                << escapeString( b.title() )
2838                << escapeString( b.link().url() )
2839                << escapeString( b.description() )
2840                << escapeString( b.copyright() )
2841                << QString::number( b.parentId() )
2842                << escapeString( b.saveLocation() )
2843                << ( b.autoscan() ? boolT() : boolF() )
2844                << QString::number( b.fetchType() )
2845                << (b.hasPurge() ? boolT() : boolF() )
2846                << (b.autotransfer() ? boolT() : boolF() )
2847                << QString::number( b.purgeCount() )
2848                << escapeString( b.url().url() )
2849            )
2850         );
2851     }
2852     else {
2853         addPodcastChannel( b, true ); //replace the already existing row
2854     }
2855 }
2856
2857 void
2858 CollectionDB::updatePodcastEpisode( const int id, const PodcastEpisodeBundle &b )
2859 {
2860     if( getDbConnectionType() == DbConnection::postgresql )
2861     {
2862         query( QStringx( "UPDATE podcastepisodes SET url='%1', localurl='%2', parent='%3', title='%4', subtitle='%5', composer='%6', comment='%7', "
2863                  "filetype='%8', createdate='%9', guid='%10', length=%11, size=%12, isNew=%13 WHERE id=%14;" )
2864               .args( QStringList()
2865                   << escapeString( b.url().url() )
2866                   << ( b.localUrl().isValid() ? escapeString( b.localUrl().url() ) : "NULL" )
2867                   << escapeString( b.parent().url() )
2868                   << escapeString( b.title() )
2869                   << escapeString( b.subtitle() )
2870                   << escapeString( b.author() )
2871                   << escapeString( b.description() )
2872                   << escapeString( b.type() )
2873                   << escapeString( b.date() )
2874                   << escapeString( b.guid() )
2875                   << QString::number( b.duration() )
2876                   << escapeString( QString::number( b.size() ) )
2877                   << ( b.isNew() ? boolT() : boolF() )
2878                   << QString::number( id )
2879                   )
2880              );
2881     }
2882     else {
2883         addPodcastEpisode( b, id );
2884     }
2885 }
2886
2887 void
2888 CollectionDB::updatePodcastFolder( const int folder_id, const QString &name, const int parent_id, const bool isOpen )
2889 {
2890     if( getDbConnectionType() == DbConnection::postgresql ) {
2891         query( QStringx( "UPDATE podcastfolders SET name='%1', parent=%2, isOpen=%3 WHERE id=%4;" )
2892                 .args( QStringList()
2893                     << escapeString(name)
2894                     << QString::number(parent_id)
2895                     << ( isOpen ? boolT() : boolF() )
2896                     << QString::number(folder_id)
2897                     )
2898              );
2899     }
2900     else {
2901         query( QStringx( "REPLACE INTO podcastfolders ( id, name, parent, isOpen ) "
2902                         "VALUES ( %1, '%2', %3, %4 );" )
2903                 .args( QStringList()
2904                     << QString::number(folder_id)
2905                     << escapeString(name)
2906                     << QString::number(parent_id)
2907                     << ( isOpen ? boolT() : boolF() )
2908                     )
2909              );
2910     }
2911 }
2912
2913 void
2914 CollectionDB::removePodcastChannel( const KUrl &url )
2915 {
2916     //remove channel
2917     query( QString( "DELETE FROM podcastchannels WHERE url = '%1';" )
2918               .arg( escapeString( url.url() ) ) );
2919     //remove all children
2920     query( QString( "DELETE FROM podcastepisodes WHERE parent = '%1';" )
2921               .arg( escapeString( url.url() ) ) );
2922 }
2923
2924
2925 /// Try not to delete by url, since some podcast feeds have all the same url
2926 void
2927 CollectionDB::removePodcastEpisode( const int id )
2928 {
2929     if( id < 0 ) return;
2930     query( QString( "DELETE FROM podcastepisodes WHERE id = '%1';" )
2931               .arg( QString::number(id) ) );
2932 }
2933
2934 void
2935 CollectionDB::removePodcastFolder( const int id )
2936 {
2937     if( id < 0 ) return;
2938     query( QString("DELETE FROM podcastfolders WHERE id=%1;")
2939               .arg( QString::number(id) ) );
2940 }
2941
2942 bool
2943 CollectionDB::addSong( MetaBundle* bundle, const bool incremental )
2944 {
2945     if ( !QFileInfo( bundle->url().path() ).isReadable() ) return false;
2946
2947     QString command = "INSERT INTO tags_temp "
2948                       "( url, dir, deviceid, createdate, modifydate, album, artist, composer, genre, year, title, "
2949                       "comment, track, discnumber, bpm, sampler, length, bitrate, "
2950                       "samplerate, filesize, filetype ) "
2951                       "VALUES ('";
2952
2953     QString artist = bundle->artist();
2954     QString title = bundle->title();
2955     if ( title.isEmpty() )
2956     {
2957         title = bundle->url().fileName();
2958         if ( bundle->url().fileName().find( '-' ) > 0 )
2959         {
2960             if ( artist.isEmpty() )
2961             {
2962                 artist = bundle->url().fileName().section( '-', 0, 0 ).trimmed();
2963                 bundle->setArtist( artist );
2964             }
2965             title = bundle->url().fileName().section( '-', 1 ).trimmed();
2966             title = title.left( title.lastIndexOf( '.' ) ).trimmed();
2967             if ( title.isEmpty() ) title = bundle->url().fileName();
2968         }
2969         bundle->setTitle( title );
2970     }
2971
2972     int deviceId = MountPointManager::instance()->getIdForUrl( bundle->url() );
2973     KUrl relativePath;
2974     MountPointManager::instance()->getRelativePath( deviceId, bundle->url(), relativePath );
2975     //debug() << "File has deviceId " << deviceId << ", relative path " << relativePath.path() << ", absolute path " << bundle->url().path() << endl;
2976
2977     command += escapeString( relativePath.path() ) + "','";
2978     command += escapeString( relativePath.directory() ) + "',";
2979     command += QString::number( deviceId ) + ',';
2980     command += QString::number( QFileInfo( bundle->url().path() ).created().toTime_t() ) + ',';
2981     command += QString::number( QFileInfo( bundle->url().path() ).lastModified().toTime_t() ) + ',';
2982
2983     command += escapeString( QString::number( albumID( bundle->album(),   true, !incremental, true ) ) ) + ',';
2984     command += escapeString( QString::number( artistID( bundle->artist(), true, !incremental, true ) ) ) + ',';
2985     command += escapeString( QString::number( composerID( bundle->composer(), true, !incremental, true ) ) ) + ',';
2986     command += escapeString( QString::number( genreID( bundle->genre(),   true, !incremental, true ) ) ) + ",'";
2987     command += escapeString( QString::number( yearID( QString::number( bundle->year() ), true, !incremental, true ) ) ) + "','";
2988
2989     command += escapeString( bundle->title() ) + "','";
2990     command += escapeString( bundle->comment() ) + "', ";
2991     command += escapeString( QString::number( bundle->track() ) ) + " , ";
2992     command += escapeString( QString::number( bundle->discNumber() ) ) + " , ";
2993     command += escapeString( QString::number( bundle->bpm() ) ) + " , ";
2994     switch( bundle->compilation() ) {
2995         case MetaBundle::CompilationNo:
2996             command += boolF();
2997             break;
2998
2999         case MetaBundle::CompilationYes:
3000             command += boolT();
3001             break;
3002
3003         case MetaBundle::CompilationUnknown:
3004         default:
3005             command += "NULL";
3006     }
3007     command += ',';
3008
3009     // NOTE any of these may be -1 or -2, this is what we want
3010     //      see MetaBundle::Undetermined
3011     command += QString::number( bundle->length() ) + ',';
3012     command += QString::number( bundle->bitrate() ) + ',';
3013     command += QString::number( bundle->sampleRate() ) + ',';
3014     command += QString::number( bundle->filesize() ) + ',';
3015     command += QString::number( bundle->fileType() ) + ')';
3016
3017     //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
3018     // Now it might be possible as insert returns the rowid.
3019     insert( command, NULL );
3020
3021     doAFTStuff( bundle, true );
3022
3023     return true;
3024 }
3025
3026 void
3027 CollectionDB::doAFTStuff( MetaBundle* bundle, const bool tempTables )
3028 {
3029     if( bundle->uniqueId().isEmpty() || bundle->url().path().isEmpty() )
3030         return;
3031
3032     MountPointManager *mpm = MountPointManager::instance();
3033     //const to make sure one isn't later modified without the other being changed
3034     const int deviceIdInt = mpm->getIdForUrl( bundle->url().path() );
3035     const QString currdeviceid = QString::number( deviceIdInt );
3036     QString currid = escapeString( bundle->uniqueId() );
3037     QString currurl = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().path() ) );
3038     QString currdir = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().directory() ) );
3039     //debug() << "Checking currid = " << currid << ", currdir = " << currdir << ", currurl = " << currurl << endl;
3040     //debug() << "tempTables = " << (tempTables?"true":"false") << endl;
3041
3042
3043     QStringList urls = query( QString(
3044             "SELECT url, uniqueid "
3045             "FROM uniqueid%1 "
3046             "WHERE deviceid = %2 AND url = '%3';" )
3047                 .arg( tempTables ? "_temp" : ""
3048                  , currdeviceid
3049                  , currurl ) );
3050
3051     QStringList uniqueids = query( QString(
3052             "SELECT url, uniqueid, deviceid "
3053             "FROM uniqueid%1 "
3054             "WHERE uniqueid = '%2';" )
3055                 .arg( tempTables ? "_temp" : ""
3056                 , currid ) );
3057
3058     QStringList nonTempIDs = query( QString(
3059             "SELECT url, uniqueid, deviceid "
3060             "FROM uniqueid "
3061             "WHERE uniqueid = '%1';" )
3062                 .arg( currid ) );
3063
3064     QStringList nonTempURLs = query( QString(
3065             "SELECT url, uniqueid "
3066             "FROM uniqueid "
3067             "WHERE deviceid = %1 AND url = '%2';" )
3068                 .arg( currdeviceid
3069                 , currurl ) );
3070
3071     bool tempTablesAndInPermanent = false;
3072     bool permanentFullMatch = false;
3073
3074     //if we're not using temp tables here, i.e. tempTables is false,
3075     //then the results from both sets of queries above should be equal,
3076     //so behavior should be the same
3077     if( tempTables && ( nonTempURLs.count() > 0 || nonTempIDs.count() > 0 ) )
3078             tempTablesAndInPermanent = true;
3079     if( tempTablesAndInPermanent && nonTempURLs.count() > 0 && nonTempIDs.count() > 0 )
3080             permanentFullMatch = true;
3081
3082     //debug() << "tempTablesAndInPermanent = " << (tempTablesAndInPermanent?"true":"false") << endl;
3083     //debug() << "permanentFullMatch = " << (permanentFullMatch?"true":"false") << endl;
3084
3085     //debug() << "Entering checks" << endl;
3086     //first case: not in permanent table or temporary table
3087     if( !tempTablesAndInPermanent && urls.empty() && uniqueids.empty() )
3088     {
3089         //debug() << "first case" << endl;
3090         QString insertline = QStringx( "INSERT INTO uniqueid%1 (deviceid, url, uniqueid, dir) "
3091                                       "VALUES ( %2,'%3', '%4', '%5');" )
3092                 .args( QStringList()
3093                  << ( tempTables ? "_temp" : "" )
3094                  << currdeviceid
3095                  << currurl
3096                  << currid
3097                  << currdir );
3098         insert( insertline, NULL );
3099         //debug() << "aftCheckPermanentTables #1" << endl;
3100         aftCheckPermanentTables( currdeviceid, currid, currurl );
3101         return;
3102     }
3103
3104     //next case: not in permanent table, but a match on one or the other in the temporary table
3105     //OR, we are using permanent tables (and not considering temp ones)
3106     if( !tempTablesAndInPermanent )
3107     {
3108         if( urls.empty() ) //uniqueid already found in temporary table but not url; check the old URL
3109         {
3110             //stat the original URL
3111             QString absPath = mpm->getAbsolutePath( uniqueids[2].toInt(), uniqueids[0] );
3112             //debug() << "At doAFTStuff, stat-ing file " << absPath << endl;
3113             bool statSuccessful = false;
3114             bool pathsSame = absPath == bundle->url().path();
3115             if( !pathsSame )
3116                 statSuccessful = QFile::exists( absPath );
3117             if( statSuccessful ) //if true, new one is a copy
3118                 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << bundle->url().path() << endl;
3119             else  //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3120             {
3121                 //debug() << "stat was NOT successful, updating tables with: " << endl;
3122                 //debug() << QString( "UPDATE uniqueid%1 SET url='%2', dir='%3' WHERE uniqueid='%4';" ).arg( ( tempTables ? "_temp" : "" ), currurl, currdir, currid ) << endl;
3123                 query( QStringx( "UPDATE uniqueid%1 SET deviceid = %2, url='%3', dir='%4' WHERE uniqueid='%5';" )
3124                       .args( QStringList()
3125                       << ( tempTables ? "_temp" : "" )
3126                       << currdeviceid
3127                       << currurl
3128                       << currdir
3129                       << currid ) );
3130                 if( !pathsSame )
3131                     emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3132             }
3133         }
3134         //okay then, url already found in temporary table but different uniqueid
3135         //a file exists in the same place as before, but new uniqueid...assume
3136         //that this is desired user behavior
3137         //NOTE: this should never happen during an incremental scan with temporary tables...!
3138         else if( uniqueids.empty() )
3139         {
3140             //debug() << "file exists in same place as before, new uniqueid" << endl;
3141             query( QString( "UPDATE uniqueid%1 SET uniqueid='%2' WHERE deviceid = %3 AND url='%4';" )
3142                     .arg( tempTables ? "_temp" : ""
3143                    , currid
3144                    , currdeviceid
3145                    , currurl ) );
3146             emit uniqueIdChanged( bundle->url().path(), urls[1], bundle->uniqueId() );
3147         }
3148         //else uniqueid and url match; nothing happened, so safely exit
3149         return;
3150     }
3151     //okay...being here means, we are using temporary tables, AND it exists in the permanent table
3152     else
3153     {
3154         //first case...full match exists in permanent table, should then be no match in temp table
3155         //(since code below deleted from permanent table after changes)
3156         //in this case, just insert into temp table
3157         if( permanentFullMatch )
3158         {
3159             QString insertline = QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3160                                           "VALUES ( %1, '%2'" )
3161                                             .arg( currdeviceid
3162                                             , currurl );
3163             insertline += QString( ", '%1', '%2');" ).arg( currid ).arg( currdir );
3164             //debug() << "running command: " << insertline << endl;
3165             insert( insertline, NULL );
3166             //debug() << "aftCheckPermanentTables #2" << endl;
3167             aftCheckPermanentTables( currdeviceid, currid, currurl );
3168             return;
3169         }
3170
3171         //second case...full match exists in permanent table, but path is different
3172         if( nonTempURLs.empty() )
3173         {
3174             //stat the original URL
3175             QString absPath = mpm->getAbsolutePath( nonTempIDs[2].toInt(), nonTempIDs[0] );
3176             //debug() << "At doAFTStuff part 2, stat-ing file " << absPath << endl;
3177             bool statSuccessful = false;
3178             bool pathsSame = absPath == bundle->url().path();
3179             if( !pathsSame )
3180                 statSuccessful = QFile::exists( absPath );
3181             if( statSuccessful ) //if true, new one is a copy
3182                 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << currurl << endl;
3183             else  //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3184             {
3185                 //debug() << "stat part 2 was NOT successful, updating tables with: " << endl;
3186                 query( QString( "DELETE FROM uniqueid WHERE uniqueid='%1';" )
3187                       .arg( currid ) );
3188                 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3189                                 "VALUES ( %1, '%2', '%3', '%4')" )
3190                                 .arg( currdeviceid
3191                                 , currurl
3192                                 , currid
3193                                 , currdir ) );
3194                 if( !pathsSame )
3195                     emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3196             }
3197         }
3198         else if( nonTempIDs.empty() )
3199         {
3200             //debug() << "file exists in same place as before, part 2, new uniqueid" << endl;
3201             query( QString( "DELETE FROM uniqueid WHERE deviceid = %1 AND url='%2';" )
3202                       .arg( currdeviceid )
3203                       .arg( currurl ) );
3204             query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) VALUES ( %1, '%2', '%3', '%4')" )
3205                 .arg( currdeviceid
3206                 , currurl
3207                 , currid
3208                 , currdir ) );
3209             emit uniqueIdChanged( bundle->url().path(), nonTempURLs[1], bundle->uniqueId() );
3210         }
3211         //else do nothing...really this case should never happen
3212         return;
3213     }
3214 }
3215
3216 void
3217 CollectionDB::emitFileDeleted( const QString &absPath, const QString &uniqueid )
3218 {
3219   if( uniqueid.isEmpty() )
3220       emit fileDeleted( absPath );
3221   else
3222       emit fileDeleted( absPath, uniqueid );
3223 }
3224
3225 void
3226 CollectionDB::emitFileAdded( const QString &absPath, const QString &uniqueid )
3227 {
3228     if( uniqueid.isEmpty() )
3229         emit fileAdded( absPath );
3230     else
3231         emit fileAdded( absPath, uniqueid );
3232 }
3233
3234 QString
3235 CollectionDB::urlFromUniqueId( const QString &id )
3236 {
3237     bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3238     QStringList urls = query( QString(
3239             "SELECT deviceid, url "
3240             "FROM uniqueid%1 "
3241             "WHERE uniqueid = '%2';" )
3242                 .arg( scanning ? "_temp" : QString() )
3243                 .arg( id ), true );
3244
3245     if( urls.empty() && scanning )
3246         urls = query( QString(
3247                     "SELECT deviceid, url "
3248                     "FROM uniqueid "
3249                     "WHERE uniqueid = '%1';" )
3250                         .arg( id ) );
3251
3252     if( urls.empty() )
3253         return QString();
3254
3255     return MountPointManager::instance()->getAbsolutePath( urls[0].toInt(), urls[1] );
3256 }
3257
3258 QString
3259 CollectionDB::uniqueIdFromUrl( const KUrl &url )
3260 {
3261     MountPointManager *mpm = MountPointManager::instance();
3262     int currdeviceid = mpm->getIdForUrl( url.path() );
3263     QString currurl = escapeString( mpm->getRelativePath( currdeviceid, url.path() ) );
3264
3265     bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3266     QStringList uid = query( QString(
3267             "SELECT uniqueid "
3268             "FROM uniqueid%1 "
3269             "WHERE deviceid = %2 AND url = '%3';" )
3270                 .arg( scanning ? "_temp" : QString() )
3271                 .arg( currdeviceid )
3272                 .arg( currurl ), true );
3273
3274     if( uid.empty() && scanning )
3275         uid = query( QString(
3276                 "SELECT uniqueid "
3277                 "FROM uniqueid "
3278                 "WHERE deviceid = %1 AND url = '%2';" )
3279                     .arg( currdeviceid )
3280                     .arg( currurl ) );
3281
3282     if( uid.empty() )
3283         return QString();
3284
3285     return uid[0];
3286 }
3287
3288 QString
3289 CollectionDB::getURL( const MetaBundle &bundle )
3290 {
3291     uint artID = artistID( bundle.artist(), false );
3292     if( !artID )
3293         return QString();
3294
3295     uint albID = albumID( bundle.album(), false );
3296     if( !albID )
3297         return QString();
3298
3299     QString q = QString( "SELECT tags.deviceid, tags.url "
3300             "FROM tags "
3301             "WHERE tags.album = '%1' AND tags.artist = '%2' AND tags.track = '%3' AND tags.title = '%4'" +
3302             deviceidSelection() + ';' )
3303         .arg( albID )
3304         .arg( artID )
3305         .arg( bundle.track() )
3306         .arg( escapeString( bundle.title() ) );
3307
3308     QStringList urls = URLsFromQuery( query( q ) );
3309
3310     if( urls.empty() )
3311         return QString();
3312
3313     if( urls.size() == 1 )
3314     {
3315         return urls.first();
3316     }
3317
3318     QString url = urls.first();
3319     int maxPlayed = -1;
3320     for( QStringList::iterator it = urls.begin();
3321             it != urls.end();
3322             it++ )
3323     {
3324         int pc = getPlayCount( *it );
3325         if( pc > maxPlayed )
3326         {
3327             maxPlayed = pc;
3328             url = *it;
3329         }
3330     }
3331
3332     return url;
3333 }
3334
3335 // Helper function to convert the "tags.sampler" column to a MetaBundle::Collection value
3336 //
3337 // We use the first char of boolT / boolF as not all DBs store true/false as
3338 // numerics (and it's only a single-char column)
3339 static int
3340 samplerToCompilation( const QString &it )
3341 {
3342     if( it == CollectionDB::instance()->boolT().mid( 0, 1 ) )
3343     {
3344          return MetaBundle::CompilationYes;
3345     }
3346     else if( it == CollectionDB::instance()->boolF().mid( 0, 1 ) )
3347     {
3348         return MetaBundle::CompilationNo;
3349     }
3350     return MetaBundle::CompilationUnknown;
3351 }
3352
3353 MetaBundle
3354 CollectionDB::bundleFromQuery( QStringList::const_iterator *iter )
3355 {
3356     QStringList::const_iterator &it = *iter;
3357     MetaBundle b;
3358     //QueryBuilder automatically inserts the deviceid as return value if asked for the path
3359     QString rpath = *it;
3360     int deviceid = (*++it).toInt();
3361     b.setPath      ( MountPointManager::instance()->getAbsolutePath( deviceid, rpath ) );
3362     b.setAlbum     (  *++it );
3363     b.setArtist    (  *++it );
3364     b.setComposer  (  *++it );
3365     b.setGenre     (  *++it );
3366     b.setTitle     (  *++it );
3367     b.setYear      ( (*++it).toInt() );
3368     b.setComment   (  *++it );
3369     b.setTrack     ( (*++it).toInt() );
3370     b.setBitrate   ( (*++it).toInt() );
3371     b.setDiscNumber( (*++it).toInt() );
3372     b.setLength    ( (*++it).toInt() );
3373     b.setSampleRate( (*++it).toInt() );
3374     b.setFilesize  ( (*++it).toInt() );
3375
3376     b.setCompilation( samplerToCompilation( *it ) );
3377     ++it;
3378     b.setFileType( (*++it).toInt() );
3379     b.setBpm       ( (*++it).toFloat() );
3380
3381     b.setScore     ( (*++it).toFloat() );
3382     b.setRating    ( (*++it).toInt() );
3383     b.setPlayCount ( (*++it).toInt() );
3384     b.setLastPlay  ( (*++it).toInt() );
3385
3386     if( false && b.length() <= 0 ) {
3387         // we try to read the tags, despite the slow-down
3388         debug() << "Audioproperties not known for: " << b.url().fileName() << endl;
3389         b.readTags( TagLib::AudioProperties::Fast);
3390     }
3391
3392     return b;
3393 }
3394
3395 static void
3396 fillInBundle( QStringList values, MetaBundle &bundle )
3397 {
3398     //TODO use this whenever possible
3399
3400     // crash prevention
3401     while( values.count() < 16 )
3402         values += "IF YOU CAN SEE THIS THERE IS A BUG!";
3403
3404     QStringList::ConstIterator it = values.begin();
3405
3406     bundle.setAlbum     ( *it ); ++it;
3407     bundle.setArtist    ( *it ); ++it;
3408     bundle.setComposer  ( *it ); ++it;
3409     bundle.setGenre     ( *it ); ++it;
3410     bundle.setTitle     ( *it ); ++it;
3411     bundle.setYear      ( (*it).toInt() ); ++it;
3412     bundle.setComment   ( *it ); ++it;
3413     bundle.setDiscNumber( (*it).toInt() ); ++it;
3414     bundle.setTrack     ( (*it).toInt() ); ++it;
3415     bundle.setBitrate   ( (*it).toInt() ); ++it;
3416     bundle.setLength    ( (*it).toInt() ); ++it;
3417     bundle.setSampleRate( (*it).toInt() ); ++it;
3418     bundle.setFilesize  ( (*it).toInt() ); ++it;
3419     bundle.setFileType  ( (*it).toInt() ); ++it;
3420     bundle.setBpm       ( (*it).toFloat() ); ++it;
3421
3422     bundle.setCompilation( samplerToCompilation( *it ) );
3423     ++it;
3424
3425     bundle.setUniqueId(*it);
3426 }
3427
3428 bool
3429 CollectionDB::bundleForUrl( MetaBundle* bundle )
3430 {
3431     int deviceid = MountPointManager::instance()->getIdForUrl( bundle->url() );
3432     KUrl rpath;
3433     MountPointManager::instance()->getRelativePath( deviceid, bundle->url(), rpath );
3434     QStringList values = query( QString(
3435             "SELECT album.name, artist.name, composer.name, genre.name, tags.title, "
3436             "year.name, tags.comment, tags.discnumber, "
3437             "tags.track, tags.bitrate, tags.length, tags.samplerate, "
3438             "tags.filesize, tags.filetype, tags.bpm, tags.sampler, uniqueid.uniqueid "
3439             "FROM tags LEFT OUTER JOIN uniqueid ON tags.url = uniqueid.url AND tags.deviceid = uniqueid.deviceid,"
3440             "album, artist, composer, genre, year "
3441             "WHERE album.id = tags.album AND artist.id = tags.artist AND composer.id = tags.composer AND "
3442             "genre.id = tags.genre AND year.id = tags.year AND tags.url = '%2' AND tags.deviceid = %1;" )
3443                 .arg( deviceid )
3444                 .arg( escapeString( rpath.path( ) ) ) );
3445
3446     bool valid = false;
3447
3448     if ( !values.empty() )
3449     {
3450         fillInBundle( values, *bundle );
3451         valid = true;
3452     }
3453     else if( MediaBrowser::instance() && MediaBrowser::instance()->getBundle( bundle->url(), bundle ) )
3454     {
3455         valid = true;
3456     }
3457     else
3458     {
3459         // check if it's a podcast
3460         PodcastEpisodeBundle peb;
3461         if( getPodcastEpisodeBundle( bundle->url(), &peb ) )
3462         {
3463             if( bundle->url().protocol() == "file" && QFile::exists( bundle->url().path() ) )
3464             {
3465                 MetaBundle mb( bundle->url(), true /* avoid infinite recursion */ );
3466                 *bundle = mb;
3467             }
3468             bundle->copyFrom( peb );
3469             valid = true;
3470         }
3471     }
3472
3473     return valid;
3474 }
3475
3476
3477 Q3ValueList<MetaBundle>
3478 CollectionDB::bundlesByUrls( const KUrl::List& urls )
3479 {
3480     BundleList bundles;
3481     QStringList paths;
3482     QueryBuilder qb;
3483     int count = 0;
3484
3485     for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it, ++count )
3486     {
3487         // non file stuff won't exist in the db, but we still need to
3488         // re-insert it into the list we return, just with no tags assigned
3489         paths += (*it).protocol() == "file" ? (*it).path() : (*it).url();
3490
3491         if( paths.count() == 50 || count == urls.size() - 1 )
3492         {
3493             qb.clear();
3494
3495             qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
3496             qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
3497             qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
3498             qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
3499             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
3500             qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
3501             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
3502             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
3503             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBitrate );
3504             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
3505             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valLength );
3506             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valSamplerate );
3507             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
3508             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
3509             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
3510             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
3511             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valIsCompilation );
3512
3513             qb.addUrlFilters( paths );
3514             qb.setOptions( QueryBuilder::optRemoveDuplicates );
3515
3516             const QStringList values = qb.run();
3517
3518             BundleList buns50;
3519             MetaBundle b;
3520             oldForeach( values )
3521             {
3522                 b.setAlbum     (    *it );
3523                 b.setArtist    (  *++it );
3524                 b.setComposer  (  *++it );
3525                 b.setGenre     (  *++it );
3526                 b.setTitle     (  *++it );
3527                 b.setYear      ( (*++it).toInt() );
3528                 b.setComment   (  *++it );
3529                 b.setTrack     ( (*++it).toInt() );
3530                 b.setBitrate   ( (*++it).toInt() );
3531                 b.setDiscNumber( (*++it).toInt() );
3532                 b.setLength    ( (*++it).toInt() );
3533                 b.setSampleRate( (*++it).toInt() );
3534                 b.setFilesize  ( (*++it).toInt() );
3535                 b.setFileType  ( (*++it).toInt() );
3536                 b.setBpm       ( (*++it).toFloat() );
3537                 b.setPath      (  *++it );
3538
3539                 b.setCompilation( samplerToCompilation( *it ) );
3540                 ++it;
3541
3542                 b.checkExists();
3543
3544                 buns50.append( b );
3545             }
3546
3547             // we get no guarantee about the order that the database
3548             // will return our values, and sqlite indeed doesn't return
3549             // them in the desired order :( (MySQL does though)
3550             oldForeach( paths )
3551             {
3552                 for( BundleList::Iterator jt = buns50.begin(), end = buns50.end(); jt != end; ++jt )
3553                 {
3554                     if ( ( *jt ).url().path() == ( *it ))
3555                     {
3556                         bundles += *jt;
3557                         buns50.remove( jt );
3558                         goto success;
3559                     }
3560                 }
3561
3562                 // if we get here, we didn't find an entry
3563                 {
3564                     KUrl url = KUrl( *it );
3565
3566                     if( true /* !MediaBrowser::instance()->getBundle( url, &b ) */ )
3567                     {
3568                         if( url.isLocalFile() )
3569                         {
3570                             b = MetaBundle( url );
3571                         }
3572                         else
3573                         {
3574                             b = MetaBundle();
3575                             b.setUrl( url );
3576                             // FIXME: more context for i18n after string freeze
3577                             b.setTitle( QString( "%1 %2 %3%4" )
3578                                   .arg( url.fileName(),
3579                                      i18n( "from" ),
3580                                      url.hasHost() ? url.host() : QString(),
3581                                      url.directory() ) );
3582                         }
3583
3584                         // check if it's a podcast
3585                         PodcastEpisodeBundle peb;
3586                         if( getPodcastEpisodeBundle( url, &peb ) )
3587                         {
3588                             b.copyFrom( peb );
3589                         }
3590                         else if( b.url().protocol() == "audiocd" || b.url().protocol() == "cdda" )
3591                         {
3592                             // try to see if the engine has some info about the
3593                             // item (the intended behaviour should be that if the
3594                             // item is an AudioCD track, the engine can return
3595                             // CDDB data for it)
3596                             Engine::SimpleMetaBundle smb;
3597                             if ( EngineController::engine()->metaDataForUrl( b.url(), smb ) )
3598                             {
3599                                 b.setTitle( smb.title );
3600                                 b.setArtist( smb.artist );
3601                                 b.setAlbum( smb.album );
3602                                 b.setComment( smb.comment );
3603                                 b.setGenre( smb.genre );
3604                                 b.setBitrate( smb.bitrate.toInt() );
3605                                 b.setSampleRate( smb.samplerate.toInt() );
3606                                 b.setLength( smb.length.toInt() );
3607                                 b.setYear( smb.year.toInt() );
3608                                 b.setTrack( smb.tracknr.toInt() );
3609                             }
3610                         }
3611                     }
3612                 }
3613                 bundles += b;
3614
3615 success: ;
3616             }
3617
3618             paths.clear();
3619         }
3620     }
3621
3622     return bundles;
3623 }
3624
3625
3626 void
3627 CollectionDB::addAudioproperties( const MetaBundle& bundle )
3628 {
3629     int deviceid = MountPointManager::instance()->getIdForUrl( bundle.url() );
3630     KUrl rpath;
3631     MountPointManager::instance()->getRelativePath( deviceid, bundle.url(), rpath );
3632     query( QString( "UPDATE tags SET bitrate='%1', length='%2', samplerate='%3' WHERE url='%5' AND deviceid = %4;" )
3633                     .arg( bundle.bitrate() )
3634                     .arg( bundle.length() )
3635                     .arg( bundle.sampleRate() )
3636                     .arg( deviceid )
3637                     .arg( escapeString( rpath.path() ) ) );
3638 }
3639
3640
3641 void
3642 CollectionDB::addSongPercentage( const QString &url, float percentage,
3643         const QString &reason, const QDateTime *playtime )
3644 {
3645     //the URL must always be inserted last! an escaped URL can contain Strings like %1->bug
3646     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3647     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3648     //statistics table might not have those values, but we need them later, so keep them
3649     int statDevId = deviceid;
3650     QString statRPath = rpath;
3651     QStringList values =
3652         query( QString(
3653             "SELECT playcounter, createdate, percentage, rating FROM statistics "
3654             "WHERE url = '%2' AND deviceid = %1;" )
3655             .arg( statDevId ).arg( escapeString( statRPath ) ) );
3656
3657     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3658     if ( values.isEmpty() )
3659     {
3660         QString rpath2 = '.' + url;
3661         values = query( QString(
3662             "SELECT playcounter, createdate, percentage, rating FROM statistics "
3663             "WHERE url = '%1' AND deviceid = -1;" )
3664             .arg( escapeString( rpath2 ) ) );
3665         if ( !values.isEmpty() )
3666         {
3667             statRPath = rpath2;
3668             statDevId = -1;
3669         }
3670     }
3671
3672     uint atime = playtime ? playtime->toTime_t() : QDateTime::currentDateTime().toTime_t();
3673
3674     // check boundaries
3675     if ( percentage > 100.f ) percentage = 100.f;
3676     if ( percentage < 1.f )   percentage = 1.f;
3677
3678     if ( !values.isEmpty() )
3679     {
3680
3681         // increment playcounter and update accesstime
3682         query( QString( "UPDATE statistics SET playcounter=%1, accessdate=%2 WHERE url='%4' AND deviceid= %3;" )
3683                         .arg( values[0] + " + 1" )
3684                         .arg( atime )
3685                         .arg( statDevId )
3686                         .arg( escapeString( statRPath ) ) );
3687     }
3688     else
3689     {
3690         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3691                         "VALUES ( '%6', %5, %1, %2, 0, 1, 0, %3, %4 );" )
3692                         .arg( atime )
3693                         .arg( atime )
3694                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3695                         .arg( boolF() )
3696                         .arg( statDevId )
3697                         .arg( escapeString( statRPath ) ), 0 );
3698     }
3699
3700     double prevscore = 50;
3701     int playcount = 0;
3702     if( !values.isEmpty() )
3703     {
3704         playcount = values[ 0 ].toInt();
3705         // This stops setting the Rating (which creates a row) from affecting the
3706         // prevscore of an unplayed track. See bug 127475
3707         if ( playcount )
3708             prevscore = values[ 2 ].toDouble();
3709     }
3710     const QStringList v = query( QString( "SELECT length FROM tags WHERE url = '%2' AND deviceid = %1;" )
3711                             .arg( deviceid ).arg( escapeString( rpath ) ) );
3712     const int length = v.isEmpty() ? 0 : v.first().toInt();
3713
3714     ScriptManager::instance()->requestNewScore( url, prevscore, playcount, length, percentage, reason );
3715 }
3716
3717
3718 float
3719 CollectionDB::getSongPercentage( const QString &url )
3720 {
3721     QueryBuilder qb;
3722     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valScore );
3723     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3724
3725     QStringList values = qb.run();
3726
3727     if( !values.isEmpty() )
3728         return values.first().toFloat();
3729
3730     return 0;
3731 }
3732
3733 int
3734 CollectionDB::getSongRating( const QString &url )
3735 {
3736     QueryBuilder qb;
3737     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valRating );
3738     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3739
3740     QStringList values = qb.run();
3741
3742     if( values.count() )
3743         return qBound( 0, values.first().toInt(), 10 );
3744
3745     return 0;
3746 }
3747
3748 void
3749 CollectionDB::setSongPercentage( const QString &url , float percentage)
3750 {
3751     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3752     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3753     QStringList values =
3754         query( QString(
3755             "SELECT playcounter, createdate, accessdate, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3756             .arg( deviceid ).arg( escapeString( rpath ) ) );
3757
3758     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3759     if ( values.isEmpty() )
3760     {
3761         QString rpath2 = '.' + url;
3762         values = query( QString(
3763             "SELECT playcounter, createdate, accessdate, rating FROM statistics "
3764             "WHERE url = '%1' AND deviceid = -1;" )
3765             .arg( escapeString( rpath2 ) ) );
3766         if ( !values.isEmpty() )
3767         {
3768             rpath = rpath2;
3769             deviceid = -1;
3770         }
3771     }
3772
3773     // check boundaries
3774     if ( percentage > 100.f ) percentage = 100.f;
3775     if ( percentage < 0.f )   percentage = 0.f;
3776
3777     if ( !values.isEmpty() )
3778     {
3779         query( QString( "UPDATE statistics SET percentage=%1 WHERE url='%3' AND deviceid = %2;" )
3780                         .arg( percentage )
3781                         .arg( deviceid ).arg( escapeString( rpath ) ) );
3782     }
3783     else
3784     {
3785         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3786                          "VALUES ( '%7', %6, %2, %3, %1, 0, 0, %3, %4 );" )
3787                         .arg( percentage )
3788                         .arg( QDateTime::currentDateTime().toTime_t() )
3789                         .arg( 0 )
3790                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3791                         .arg( boolF() )
3792                         .arg( deviceid )
3793                         .arg( escapeString( rpath ) ),0 );
3794     }
3795
3796     emit scoreChanged( url, percentage );
3797 }
3798
3799 void
3800 CollectionDB::setSongRating( const QString &url, int rating, bool toggleHalf )
3801 {
3802     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3803     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3804     QStringList values =
3805         query( QString(
3806             "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3807             .arg( deviceid )
3808             .arg( escapeString( rpath ) ) );
3809
3810     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3811     if ( values.isEmpty() )
3812     {
3813         QString rpath2 = '.' + url;
3814         values = query( QString(
3815             "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics "
3816             "WHERE url = '%1' AND deviceid = -1;" )
3817             .arg( escapeString( rpath2 ) ) );
3818         if ( !values.isEmpty() )
3819         {
3820             rpath = rpath2;
3821             deviceid = -1;
3822         }
3823     }
3824
3825     bool ok = true;
3826     if ( !values.isEmpty() )
3827     {
3828         int prev = values[4].toInt( &ok );
3829         if( ok && toggleHalf && ( prev == rating || ( prev == 1 && rating == 2 ) ) )
3830         {
3831             if( prev == 1 && rating == 2 )
3832                 rating = 0;
3833             else if( rating % 2 ) //.5
3834                 rating++;
3835             else
3836                 rating--;
3837         }
3838     }
3839
3840     // check boundaries
3841     if ( rating > 10 ) rating = 10;
3842     if ( rating < 0 ) rating = 0;
3843
3844     if ( !values.isEmpty() )
3845     {
3846         query( QString( "UPDATE statistics SET rating=%1 WHERE url='%3' AND deviceid = %2;" )
3847                         .arg( rating )
3848                         .arg( deviceid )
3849                         .arg( escapeString( rpath ) ) );
3850     }
3851     else
3852     {
3853         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, rating, playcounter, uniqueid, deleted ) "
3854                          "VALUES ( '%7', %6, %2, %3, 0, %1, 0, %4, %5 );" )
3855                         .arg( rating )
3856                         .arg( QDateTime::currentDateTime().toTime_t() )
3857                         .arg( 0 )
3858                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3859                         .arg( boolF() )
3860                         .arg( deviceid )
3861                         .arg( escapeString( rpath ) ), NULL );
3862     }
3863
3864     emit ratingChanged( url, rating );
3865 }
3866
3867 int
3868 CollectionDB::getPlayCount( const QString &url )
3869 {
3870     //queryBuilder is good
3871     QueryBuilder qb;
3872     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valPlayCounter );
3873     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3874     QStringList values = qb.run();
3875     if( values.count() )
3876         return values.first().toInt();
3877     return 0;
3878 }
3879
3880 QDateTime
3881 CollectionDB::getFirstPlay( const QString &url )
3882 {
3883     QueryBuilder qb;
3884     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valCreateDate );
3885     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3886     QStringList values = qb.run();
3887     QDateTime dt;
3888     if( values.count() )
3889         dt.setTime_t( values.first().toUInt() );
3890     return dt;
3891 }
3892
3893 QDateTime
3894 CollectionDB::getLastPlay( const QString &url )
3895 {
3896     QueryBuilder qb;
3897     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valAccessDate );
3898     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3899     QStringList values = qb.run();
3900     QDateTime dt;
3901     if( values.count() )
3902         dt.setTime_t( values.first().toUInt() );
3903     else
3904         dt.setTime_t( 0 );
3905     return dt;
3906 }
3907 /*!
3908  *  @short: exchange url references in the database for a particular file
3909  *  @note: deletes all items for newURL, changes oldURL->newURL, deletes oldURL.
3910  *  FIXME: should we check if lyrics etc exist in the newURL and keep them if necessary?
3911  */
3912 void
3913 CollectionDB::migrateFile( const QString &oldURL, const QString &newURL )
3914 {
3915     int oldMediaid = MountPointManager::instance()->getIdForUrl( oldURL );
3916     QString oldRpath = MountPointManager::instance()->getRelativePath( oldMediaid, oldURL );
3917
3918     int newMediaid = MountPointManager::instance()->getIdForUrl( newURL );
3919     QString newRpath = MountPointManager::instance()->getRelativePath( newMediaid, newURL );
3920
3921     //  Ensure destination is clear.
3922     query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
3923         .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3924
3925     query( QString( "DELETE FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3926         .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3927
3928     query( QString( "DELETE FROM tags_labels WHERE url = '%2' and deviceid = %1;" )
3929         .arg( newMediaid).arg( escapeString( newRpath ) ) );
3930
3931     if ( !getLyrics( oldURL ).isEmpty() )
3932         query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
3933             .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3934     //  Migrate
3935     //code looks ugly but prevents problems when the URL contains HTTP escaped characters
3936     query( QString( "UPDATE tags SET url = '%3', deviceid = %1" )
3937         .arg( newMediaid ).arg( escapeString( newRpath ) )
3938         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3939         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3940
3941     query( QString( "UPDATE statistics SET url = '%2', deviceid = %1" )
3942         .arg( newMediaid ).arg( escapeString( newRpath ) )
3943         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3944         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3945
3946     query( QString( "UPDATE lyrics SET url = '%2', deviceid = %1" )
3947         .arg( newMediaid ).arg( escapeString( newRpath ) )
3948         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3949         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3950
3951     query( QString( "UPDATE tags_labels SET url = '%2', deviceid = %1 WHERE deviceid = %3 AND url = '%4';" )
3952             .arg( QString::number( newMediaid ), escapeString( newRpath ), QString::number( oldMediaid ), escapeString( oldRpath ) ) );
3953
3954     query( QString( "UPDATE uniqueid SET url = '%1', deviceid = %2 WHERE url = '%3' AND deviceid = %4;" )
3955         .arg( escapeString( newRpath ), QString::number( newMediaid ),
3956               escapeString( oldRpath ), QString::number( oldMediaid ) ) );
3957
3958     query( QString( "UPDATE playlists SET url = '%1' WHERE url = '%2';" )
3959         .arg( escapeString( newURL ),
3960               escapeString( oldURL ) ) );
3961 }
3962
3963 void
3964 CollectionDB::fileOperationResult( KIO::Job *job ) // slot
3965 {
3966     if(job->error())
3967     {
3968         m_fileOperationFailed = true;
3969         debug() << "file operation failed: " << job->errorText() << endl;
3970     }
3971     else
3972     {
3973         m_fileOperationFailed = false;
3974     }
3975
3976     m_waitForFileOperation = false;
3977 }
3978
3979 void CollectionDB::cancelMovingFileJob()
3980 {
3981     m_moveFileJobCancelled = true;
3982 }
3983
3984 bool
3985 CollectionDB::organizeFile( const KUrl &src, const OrganizeCollectionDialog &dialog, bool copy )
3986 {
3987    if( !MetaBundle::isKioUrl( src ) )
3988        return false;
3989
3990    bool overwrite = dialog.overwriteCheck->isChecked();
3991    bool localFile = src.isLocalFile();
3992    KUrl tmpSrc = src;
3993    if( !localFile )
3994    {
3995       QString tmp;
3996       QString extension = src.url().section( '.', -1 );
3997       extension = extension.section("?", 0, 0);  // remove trailling stuff lead by ?, if any
3998
3999       int count = 0;
4000       do
4001       {
4002          tmp = QString( dialog.folderCombo->currentText() + "/amarok-tmp-%1." + extension ).arg( count );
4003          count++;
4004       } while( QFile::exists( tmp ) );
4005       tmpSrc = KUrl( tmp );
4006
4007       KIO::FileCopyJob *job = 0;
4008       if( copy )
4009       {
4010          job = KIO::file_copy( src, tmpSrc, -1, false, false, false );
4011       }
4012       else
4013       {
4014          job = KIO::file_move( src, tmpSrc, -1, false, false, false );
4015       }
4016       connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
4017       m_waitForFileOperation = true;
4018       while( m_waitForFileOperation )
4019       {
4020         if( m_moveFileJobCancelled )
4021         {
4022             disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
4023
4024             QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4025             job->kill();
4026             QFile file( partFile );
4027             if( file.exists() ) file.remove();
4028
4029             m_waitForFileOperation = false;
4030             m_fileOperationFailed = true;
4031             continue;
4032         }
4033
4034          usleep( 10000 );
4035          kapp->processEvents( QEventLoop::AllEvents );
4036       }
4037
4038       if( m_fileOperationFailed )
4039       {
4040          debug() << "failed to transfer " << src.url() << " to " << tmpSrc << endl;
4041
4042          m_moveFileJobCancelled = false;
4043          return false;
4044       }
4045    }
4046
4047    //Building destination here.
4048    MetaBundle mb( tmpSrc );
4049    QString dest = dialog.buildDestination( dialog.buildFormatString(), mb );
4050
4051    debug() << "Destination: " << dest << endl;
4052
4053    if( !m_moveFileJobCancelled && tmpSrc.path() != dest ) //suppress error warning that file couldn't be moved
4054    {
4055       if( !CollectionDB::instance()->moveFile( tmpSrc.url(), dest, overwrite, copy && localFile ) )
4056       {
4057          if( !localFile )
4058             QFile::remove( tmpSrc.path() );
4059
4060          m_moveFileJobCancelled = false;
4061          return false;
4062       }
4063    }
4064
4065    //Use cover image for folder icon
4066    if( !m_moveFileJobCancelled && dialog.coverCheck->isChecked() && !mb.artist().isEmpty() && !mb.album().isEmpty() )
4067    {
4068       KUrl dstURL = KUrl( dest );
4069       dstURL.cleanPath();
4070
4071       QString path  = dstURL.directory();
4072       QString cover = CollectionDB::instance()->albumImage( mb.artist(), mb.album(), false, 1 );
4073
4074       if( !QFile::exists(path + "/.directory") && !cover.endsWith( "nocover.png" ) )
4075       {
4076          //QPixmap thumb;        //Not amazon nice.
4077          //if ( thumb.load( cover ) ){
4078          //thumb.save(path + "/.front.png", "PNG", -1 ); //hide files
4079
4080          KConfig config(path + "/.directory");
4081          config.setGroup("Desktop Entry");
4082
4083          if( !config.hasKey("Icon") )
4084          {
4085             //config.writeEntry("Icon", QString("%1/.front.png").arg( path ));
4086             config.writeEntry( "Icon", cover );
4087             config.sync();
4088             debug() << "Using this cover as icon for: " << path << endl;
4089             debug() << cover << endl;
4090          }
4091          //}         //Not amazon nice.
4092       }
4093    }
4094
4095    if( localFile && QDir().rmdir( src.directory() ) )
4096    {
4097       debug() << "removed: " << src.directory() << endl;
4098    }
4099
4100    m_moveFileJobCancelled = false;
4101
4102    return true;
4103 }
4104
4105 bool
4106 CollectionDB::moveFile( const QString &src, const QString &dest, bool overwrite, bool copy )
4107 {
4108     DEBUG_BLOCK
4109     if(src == dest){
4110         debug() << "Source and destination URLs are the same, aborting."  << endl;
4111         return false;
4112     }
4113
4114     // Escape URL.
4115     KUrl srcURL = KUrl( src );
4116     KUrl dstURL = KUrl( dest );
4117
4118     // Clean it.
4119     srcURL.cleanPath();
4120     dstURL.cleanPath();
4121
4122     // Make sure it is valid.
4123     if(!srcURL.isValid() || !dstURL.isValid())
4124         debug() << "Invalid URL "  << endl;
4125
4126     // Get just the directory.
4127     KUrl dir = dstURL;
4128     dir.setFileName(QString());
4129
4130     // Create the directory.
4131     if(!KStandardDirs::exists(dir.path()))
4132         if(!KStandardDirs::makeDir(dir.path())) {
4133             debug() << "Unable to create directory " << dir.path() << endl;
4134         }
4135
4136     m_fileOperationFailed = false;
4137     KIO::FileCopyJob *job = 0;
4138     if( copy )
4139     {
4140         job = KIO::file_copy( srcURL, dstURL, -1, overwrite, false, false );
4141     }
4142     else
4143     {
4144         job = KIO::file_move( srcURL, dstURL, -1, overwrite, false, false );
4145     }
4146     connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
4147     m_waitForFileOperation = true;
4148     while( m_waitForFileOperation )
4149     {
4150         if( m_moveFileJobCancelled )
4151         {
4152             disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
4153
4154             QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4155             job->kill();
4156             QFile file( partFile );
4157             if( file.exists() ) file.remove();
4158
4159             m_waitForFileOperation = false;
4160             m_fileOperationFailed = true;
4161             continue;
4162         }
4163
4164         usleep( 10000 );
4165         kapp->processEvents( QEventLoop::AllEvents );
4166     }
4167
4168     if( !m_fileOperationFailed )
4169     {
4170         if( copy )
4171         {
4172             MetaBundle bundle( dstURL );
4173             if( bundle.isValidMedia() )
4174             {
4175                 addSong( &bundle, true );
4176                 return true;
4177             }
4178         }
4179         else
4180         {
4181             emit fileMoved( src, dest );
4182             migrateFile( srcURL.path(), dstURL.path() );
4183
4184             if( isFileInCollection( srcURL.path() ) )
4185             {
4186                 return true;
4187             }
4188             else
4189             {
4190                 MetaBundle bundle( dstURL );
4191                 if( bundle.isValidMedia() )
4192                 {
4193                     addSong( &bundle, true );
4194                     return true;
4195                 }
4196             }
4197         }
4198     }
4199
4200     return false;
4201 }
4202
4203
4204 void
4205 CollectionDB::updateDirStats( QString path, const long datetime, const bool temporary )
4206 {
4207     if ( path.endsWith( "/" ) )
4208         path = path.left( path.length() - 1 );
4209
4210     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4211     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4212
4213     if (getDbConnectionType() == DbConnection::postgresql)
4214     {
4215         // REPLACE INTO is not valid SQL for postgres, so we need to check whether we
4216         // should UPDATE() or INSERT()
4217         QStringList values = query( QString("SELECT * FROM directories%1 WHERE dir='%3' AND deviceid=%2;")
4218             .arg( temporary ? "_temp" : "")
4219             .arg( deviceid )
4220             .arg( escapeString( rpath ) ) );
4221
4222         if(values.count() > 0 )
4223         {
4224             query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%4'AND deviceid=%3;")
4225             .arg( temporary ? "_temp" : "" )
4226             .arg( datetime )
4227             .arg( deviceid )
4228             .arg( escapeString( rpath ) ) );
4229         }
4230         else
4231         {
4232
4233             query( QString( "INSERT INTO directories%1 (dir, deviceid,changedate) VALUES ('%4', %3, '%2');")
4234             .arg( temporary ? "_temp" : "")
4235             .arg( datetime )
4236             .arg( deviceid )
4237             .arg( escapeString( rpath ) ) );
4238         }
4239     }
4240     else
4241     {
4242         query( QString( "REPLACE INTO directories%1 ( dir, deviceid, changedate ) VALUES ( '%4', %3, %2 );" )
4243                   .arg( temporary ? "_temp" : "" )
4244                   .arg( datetime )
4245                   .arg( deviceid )
4246                   .arg( escapeString( rpath ) ) );
4247     }
4248
4249     INotify::instance()->watchDir( path );
4250 }
4251
4252
4253 void
4254 CollectionDB::removeSongsInDir( QString path, QMap<QString,QString> *tagsRemoved )
4255 {
4256     if ( path.endsWith( "/" ) )
4257         path = path.left( path.length() - 1 );
4258     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4259     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4260
4261     // Pass back the list of tags we actually delete if requested.
4262     if( tagsRemoved )
4263     {
4264         QStringList result
4265           = query( QString( "SELECT tags.deviceid, tags.url, uniqueid.uniqueid FROM tags "
4266                             "LEFT JOIN uniqueid ON uniqueid.url = tags.url "
4267                             "AND uniqueid.deviceid = tags.deviceid "
4268                             "WHERE tags.dir = '%2' AND tags.deviceid = %1" )
4269                    .arg( deviceid )
4270                    .arg( escapeString( rpath ) ) );
4271         QStringList::ConstIterator it = result.begin(), end = result.end();
4272         while( it != end )
4273         {
4274             int deviceid2    = (*(it++)).toInt();
4275             QString rpath2   =  *(it++);
4276             QString uniqueid =  *(it++);
4277             (*tagsRemoved)[uniqueid] = MountPointManager::instance()->getAbsolutePath(
4278                                 deviceid2, rpath2 );
4279         }
4280     }
4281
4282     query( QString( "DELETE FROM tags WHERE dir = '%2' AND deviceid = %1;" )
4283               .arg( deviceid )
4284               .arg( escapeString( rpath ) ) );
4285
4286     query( QString( "DELETE FROM uniqueid WHERE dir = '%2' AND deviceid = %1;" )
4287               .arg( deviceid )
4288               .arg( escapeString( rpath ) ) );
4289 }
4290
4291
4292 bool
4293 CollectionDB::isDirInCollection( QString path )
4294 {
4295     if ( path.endsWith( "/" ) )
4296         path = path.left( path.length() - 1 );
4297     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4298     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4299
4300     QStringList values =
4301         query( QString( "SELECT changedate FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4302                   .arg( deviceid )
4303                   .arg( escapeString( rpath ) ) );
4304
4305     return !values.isEmpty();
4306 }
4307
4308
4309 bool
4310 CollectionDB::isFileInCollection( const QString &url  )
4311 {
4312     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4313     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4314
4315     QString sql = QString( "SELECT url FROM tags WHERE url = '%2' AND deviceid = %1" )
4316                              .arg( deviceid )
4317                              .arg( escapeString( rpath ) );
4318     if ( deviceid == -1 )
4319     {
4320         sql += ';';
4321     }
4322     else
4323     {
4324         QString rpath2 = '.' + url;
4325         sql += QString( " OR url = '%1' AND deviceid = -1;" )
4326                       .arg( escapeString( rpath2 ) );
4327     }
4328     QStringList values = query( sql );
4329
4330     return !values.isEmpty();
4331 }
4332
4333
4334 void
4335 CollectionDB::removeSongs( const KUrl::List& urls )
4336 {
4337     for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it )
4338     {
4339         int deviceid = MountPointManager::instance()->getIdForUrl( *it );
4340         QString rpath = MountPointManager::instance()->getRelativePath( deviceid, (*it).path() );
4341
4342         query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
4343             .arg( deviceid )
4344             .arg( escapeString( rpath ) ) );
4345         query( QString( "DELETE FROM uniqueid WHERE url = '%2' AND deviceid = %1;" )
4346                 .arg( deviceid )
4347                 .arg( escapeString( rpath ) ) );
4348         query( QString( "UPDATE statistics SET deleted = %1 WHERE url = '%3' AND deviceid = %2;" )
4349                 .arg( boolT() )
4350                 .arg( deviceid )
4351                 .arg( escapeString( rpath ) ) );
4352     }
4353 }
4354
4355
4356 QStringList
4357 CollectionDB::similarArtists( const QString &artist, uint count )
4358 {
4359     QStringList values;
4360
4361     values = query( QString( "SELECT suggestion FROM related_artists WHERE artist = '%1' ORDER BY %2 LIMIT %3 OFFSET 0;" )
4362                                  .arg( escapeString( artist ), randomFunc(), QString::number( count ) ) );
4363
4364     if ( values.isEmpty() )
4365         Scrobbler::instance()->similarArtists( artist );
4366
4367     return values;
4368 }
4369
4370
4371 void
4372 CollectionDB::sanitizeCompilations()
4373 {
4374     query( QString( "UPDATE tags_temp SET sampler = %1 WHERE sampler IS NULL;").arg( boolF() ) );
4375 }
4376
4377 void
4378 CollectionDB::checkCompilations( const QString &path, const bool temporary )
4379 {
4380     QStringList albums;
4381     QStringList artists;
4382     QStringList dirs;
4383
4384     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4385     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4386
4387     albums = query( QString( "SELECT DISTINCT album.name FROM tags_temp, album%1 AS album WHERE tags_temp.dir = '%3' AND tags_temp.deviceid = %2 AND album.id = tags_temp.album AND tags_temp.sampler IS NULL;" )
4388               .arg( temporary ? "_temp" : "" )
4389               .arg( deviceid )
4390               .arg( escapeString( rpath ) ) );
4391
4392     for ( uint i = 0; i < albums.count(); i++ )
4393     {
4394         if ( albums[ i ].isEmpty() ) continue;
4395
4396         const uint album_id = albumID( albums[ i ], false, temporary, true );
4397         artists = query( QString( "SELECT DISTINCT artist.name FROM tags_temp, artist%1 AS artist WHERE tags_temp.album = '%2' AND tags_temp.artist = artist.id;" )
4398                             .arg( temporary ? "_temp" : "" )
4399                             .arg( album_id ) );
4400         dirs    = query( QString( "SELECT DISTINCT dir FROM tags_temp WHERE album = '%1';" )
4401                             .arg( album_id ) );
4402
4403         if ( artists.count() > dirs.count() )
4404         {
4405             debug() << "Detected compilation: " << albums[ i ] << " - " << artists.count() << ':' << dirs.count() << endl;
4406         }
4407         query( QString( "UPDATE tags_temp SET sampler = %1 WHERE album = '%2' AND sampler IS NULL;" )
4408                          .arg(artists.count() > dirs.count() ? boolT() : boolF()).arg( album_id ) );
4409     }
4410 }
4411
4412 void
4413 CollectionDB::setCompilation( const KUrl::List &urls, bool enabled, bool updateView )
4414 {
4415     for ( KUrl::List::const_iterator it = urls.begin(); it != urls.end(); ++it )
4416     {
4417         QString url( ( *it ).path() );
4418
4419         int deviceid = MountPointManager::instance()->getIdForUrl( url );
4420         QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4421
4422         query( QString( "UPDATE tags SET sampler = %1 WHERE tags.url = '%2' AND tags.deviceid = %3;" )
4423                 .arg( ( enabled ? boolT() : boolF() ), escapeString( rpath ), QString::number( deviceid ) ) );
4424     }
4425
4426     // Update the Collection-Browser view,
4427     // using QTimer to make sure we don't manipulate the GUI from a thread
4428     if ( updateView )
4429         QTimer::singleShot( 0, CollectionView::instance(), SLOT( renderView() ) );
4430 }
4431
4432
4433 void
4434 CollectionDB::removeDirFromCollection( QString path )
4435 {
4436     //if ( path.endsWith( "/" ) )
4437     //    path = path.left( path.length() - 1 );
4438     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4439     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4440
4441     query( QString( "DELETE FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4442                     .arg( deviceid )
4443                     .arg( escapeString( rpath ) ) );
4444 }
4445
4446
4447 QString
4448 CollectionDB::IDFromExactValue( QString table, QString value, bool autocreate, bool temporary /* = false */ )
4449 {
4450     if ( temporary )
4451     {
4452             table.append( "_temp" );
4453     }
4454
4455     QString querystr( QString( "SELECT id FROM %1 WHERE name " ).arg( table ) );
4456     querystr += exactCondition( value ) + ';';
4457     QStringList result = query( querystr );
4458     if ( result.isEmpty() )
4459     {
4460         if ( autocreate )
4461             return QString::number( insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
4462                  .arg( table, escapeString( value ) ),
4463                  table ) );
4464         else
4465             return 0;
4466     }
4467     else
4468     {
4469         if ( result.count() > 1 )
4470             debug() << "More than one entry in the " << table << " database for '" << value << '\'' << endl;
4471         return result.first();
4472     }
4473 }
4474
4475 void
4476 CollectionDB::deleteRedundantName( const QString &table, const QString &id )
4477 {
4478     QString querystr( QString( "SELECT %1 FROM tags WHERE tags.%1 = %2 LIMIT 1;" ).arg( table, id ) );
4479     QStringList result = query( querystr );
4480     if ( result.isEmpty() )
4481         query( QString( "DELETE FROM %1 WHERE id = %2;" ).arg( table,id ) );
4482 }
4483
4484 void
4485 CollectionDB::deleteAllRedundant( const QString &table )
4486 {
4487     //This works with MySQL4. I thought it might not do, due to the comment in copyTempTables
4488     query( QString( "DELETE FROM %1 WHERE id NOT IN ( SELECT %2 FROM tags )" ).arg( table, table ) );
4489 }
4490
4491
4492 void
4493 CollectionDB::updateTags( const QString &url, const MetaBundle &bundle, const bool updateView )
4494 {
4495     DEBUG_BLOCK
4496     QueryBuilder qb;
4497     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
4498     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
4499     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
4500     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
4501     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
4502     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
4503     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
4504     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
4505     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
4506     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
4507     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
4508     // [10] is above. [11] is below.
4509     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
4510     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valID );
4511     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valID );
4512     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valID );
4513     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valID );
4514     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valID );
4515
4516     qb.addUrlFilters ( QStringList( url ) );
4517     qb.setOptions( QueryBuilder::optRemoveDuplicates );
4518     QStringList values = qb.run();
4519
4520     if ( values.count() > 17 )
4521     {
4522         error() << "Query returned more than 1 song. Aborting updating metadata" << endl;
4523         return;
4524     }
4525
4526     if ( !values.isEmpty() )
4527     {
4528         bool art=false, comp=false, alb=false, gen=false, year=false;
4529
4530         QString command = "UPDATE tags SET ";
4531         if ( values[ 0 ] != bundle.title() )
4532             command += "title = '" + escapeString( bundle.title() ) + "', ";
4533         if ( values[ 1 ] != bundle.artist() )
4534         {
4535             art = true;
4536             command += "artist = " + IDFromExactValue( "artist", bundle.artist() ) + ", ";
4537         }
4538         if ( values[ 2 ] != bundle.composer() )
4539         {
4540             comp = true;
4541             command += "composer = " + IDFromExactValue( "composer", bundle.composer() ) + ", ";
4542         }
4543         if ( values[ 3 ] != bundle.album() )
4544         {
4545             alb = true;
4546             command += "album = "  + IDFromExactValue( "album", bundle.album() ) + ", ";
4547         }
4548         if ( values[ 4 ] != bundle.genre() )
4549         {
4550             gen = true;
4551             command += "genre = "  + IDFromExactValue( "genre", bundle.genre() ) + ", ";
4552         }
4553         if ( values[ 5 ] != QString::number( bundle.year() ) )
4554         {
4555             year = false;
4556             command += "year = "   + IDFromExactValue( "year", QString::number( bundle.year() ) ) + ", ";
4557         }
4558         if ( values[ 6 ] != QString::number( bundle.track() ) )
4559             command += "track = " + QString::number( bundle.track() ) + ", ";
4560         if ( values[ 7 ] != bundle.comment() )
4561             command += "comment = '" + escapeString( bundle.comment() ) + "', ";
4562         if ( values[ 8 ] != QString::number( bundle.discNumber() ) )
4563             command += "discnumber = '" + QString::number( bundle.discNumber() ) + "', ";
4564         if ( values[ 9 ] != QString::number( bundle.filesize() ) )
4565             command += "filesize = '" + QString::number( bundle.filesize() ) + "', ";
4566         if ( values[ 10 ] != QString::number( bundle.fileType() ) )
4567             command += "filetype = '" + QString::number( bundle.fileType() ) + "', ";
4568         if ( values[ 11 ] != QString::number( bundle.bpm() ) )
4569             command += "bpm = '" + QString::number( bundle.bpm() ) + "', ";
4570
4571         if ( "UPDATE tags SET " == command )
4572         {
4573             debug() << "No tags selected to be changed" << endl;
4574         }
4575         else
4576         {
4577             int deviceid = MountPointManager::instance()->getIdForUrl( url );
4578             QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4579             //We have to remove the trailing comma from command
4580             query( command.left( command.length() - 2 ) + " WHERE url = '" + escapeString( rpath ) +
4581                     "' AND deviceid = " + QString::number( deviceid ) + ';' );
4582         }
4583
4584         //Check to see if we use the entry anymore. If not, delete it
4585         if ( art )
4586             deleteRedundantName( "artist", values[ 12 ] );
4587         if ( comp )
4588             deleteRedundantName( "composer", values[ 13 ] );
4589         if ( alb )
4590             deleteRedundantName( "album", values[ 14 ] );
4591         if ( gen )
4592             deleteRedundantName( "genre", values[ 15 ] );
4593         if ( year )
4594             deleteRedundantName( "year", values[ 16 ] );
4595
4596         // Update the Collection-Browser view,
4597         // using QTimer to make sure we don't manipulate the GUI from a thread
4598         if ( updateView )
4599             QTimer::singleShot( 0, CollectionView::instance(), SLOT( databaseChanged() ) );
4600
4601         if( art || alb )
4602             emit tagsChanged( values[12], values[14] );
4603     }
4604
4605     if ( EngineController::instance()->bundle().url() == bundle.url() )
4606     {
4607         debug() << "Current song edited, updating widgets: " << bundle.title() << endl;
4608         EngineController::instance()->currentTrackMetaDataChanged( bundle );
4609     }
4610
4611     emit tagsChanged( bundle );
4612 }
4613
4614
4615 void
4616 CollectionDB::updateURL( const QString &url, const bool updateView )
4617 {
4618     // don't use the KUrl ctor as it checks the db first
4619     MetaBundle bundle;
4620     bundle.setPath( url );
4621     bundle.readTags( TagLib::AudioProperties::Fast );
4622
4623     updateTags( url, bundle, updateView);
4624     doAFTStuff( &bundle, false );
4625 }
4626
4627 QString
4628 CollectionDB::getUniqueId( const QString &url )
4629 {
4630     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4631     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4632     QStringList values = query( QString( "SELECT uniqueid FROM uniqueid WHERE deviceid = %1 AND url = '%2';" )
4633                                 .arg( deviceid )
4634                                 .arg( escapeString( rpath ) ));
4635     if( !values.empty() )
4636         return values[0];
4637     else
4638         return QString();
4639 }
4640
4641 void
4642 CollectionDB::setLyrics( const QString &url, const QString &lyrics, const QString &uniqueid )
4643 {
4644     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4645     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4646
4647     QStringList values = query(QString("SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;")
4648                     .arg( deviceid ).arg( escapeString( rpath ) ) );
4649     if(values.count() > 0)
4650     {
4651         if ( !lyrics.isEmpty() )
4652             query( QString( "UPDATE lyrics SET lyrics = '%1' WHERE url = '%3' AND deviceid = %2;" )
4653                     .arg( escapeString( lyrics), QString::number(deviceid), escapeString( rpath ) ) );
4654         else
4655             query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4656                     .arg( deviceid).arg( escapeString( rpath ) ) );
4657     }
4658     else
4659     {
4660         insert( QString( "INSERT INTO lyrics (deviceid, url, lyrics, uniqueid) values ( %1, '%2', '%3', '%4' );" )
4661                 .arg( QString::number(deviceid), escapeString( rpath ), escapeString( lyrics ), escapeString( uniqueid ) ), NULL);
4662     }
4663 }
4664
4665
4666 QString
4667 CollectionDB::getLyrics( const QString &url )
4668 {
4669     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4670     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4671     QStringList values = query( QString( "SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4672                 .arg( deviceid ).arg( escapeString( rpath ) ) );
4673     if ( !values.isEmpty() )
4674         return values[0];
4675     else
4676         return QString();
4677 }
4678
4679 void CollectionDB::removeInvalidAmazonInfo( const QString& md5sum )
4680 {
4681     query( QString( "DELETE FROM amazon WHERE filename='%1'" ).arg( md5sum ) );
4682 }
4683
4684 void CollectionDB::newAmazonReloadDate( const QString& asin, const QString& locale, const QString& md5sum)
4685 {
4686     QStringList values = query(QString("SELECT filename FROM amazon WHERE filename = '%1'")
4687         .arg(md5sum));
4688     if(values.count() > 0)
4689     {
4690         query( QString("UPDATE amazon SET asin = '%1', locale = '%2', refetchdate = '%3' WHERE filename = '%4'")
4691             .arg(asin)
4692             .arg(locale)
4693             .arg(QDateTime::currentDateTime().addDays(80).toTime_t())
4694             .arg(md5sum));
4695     }
4696     else
4697     {
4698         insert( QString( "INSERT INTO amazon ( asin, locale, filename, refetchdate ) VALUES ( '%1', '%2', '%3', '%4');" )
4699          .arg(asin)
4700          .arg(locale)
4701          .arg(md5sum)
4702          .arg(QDateTime::currentDateTime().addDays(80).toTime_t()), NULL );
4703     }
4704 }
4705
4706 QStringList CollectionDB::staleImages()
4707 {
4708     return query(QString("SELECT asin, locale, filename FROM amazon WHERE refetchdate < %1 ;")
4709             .arg(QDateTime::currentDateTime().toTime_t() ));
4710 }
4711
4712 void
4713 CollectionDB::applySettings()
4714 {
4715     bool recreateConnections = false;
4716     if ( AmarokConfig::databaseEngine().toInt() != getDbConnectionType() )
4717     {
4718         if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4719             m_dbConnType = DbConnection::mysql;
4720         else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4721             m_dbConnType = DbConnection::postgresql;
4722         else m_dbConnType = DbConnection::sqlite;
4723         recreateConnections = true;
4724     }
4725     else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4726     {
4727         // Using MySQL, so check if MySQL settings were changed
4728         const MySqlConfig *config =
4729             static_cast<const MySqlConfig*> ( m_dbConfig );
4730         if ( AmarokConfig::mySqlHost() != config->host() )
4731         {
4732             recreateConnections = true;
4733         }
4734         else if ( AmarokConfig::mySqlPort() != config->port() )
4735         {
4736             recreateConnections = true;
4737         }
4738         else if ( AmarokConfig::mySqlDbName() != config->database() )
4739         {
4740             recreateConnections = true;
4741         }
4742         else if ( AmarokConfig::mySqlUser() != config->username() )
4743         {
4744             recreateConnections = true;
4745         }
4746         else if ( AmarokConfig::mySqlPassword() != config->password() )
4747         {
4748             recreateConnections = true;
4749         }
4750     }
4751     else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4752     {
4753       const PostgresqlConfig *config =
4754           static_cast<const PostgresqlConfig*> ( m_dbConfig );
4755         if ( AmarokConfig::postgresqlHost() != config->host() )
4756         {
4757             recreateConnections = true;
4758         }
4759         else if ( AmarokConfig::postgresqlPort() != config->port() )
4760         {
4761             recreateConnections = true;
4762         }
4763         else if ( AmarokConfig::postgresqlDbName() != config->database() )
4764         {
4765             recreateConnections = true;
4766         }
4767         else if ( AmarokConfig::postgresqlUser() != config->username() )
4768         {
4769             recreateConnections = true;
4770         }
4771         else if ( AmarokConfig::postgresqlPassword() != config->password() )
4772       {
4773           recreateConnections = true;
4774       }
4775     }
4776
4777     if ( recreateConnections )
4778     {
4779         debug()
4780             << "Database engine settings changed: "
4781             << "recreating DbConnections" << endl;
4782         // If Database engine was changed, recreate DbConnections.
4783         destroy();
4784         initialize();
4785         CollectionView::instance()->renderView();
4786         PlaylistBrowser::instance()->loadPodcastsFromDatabase();
4787
4788         emit databaseEngineChanged();
4789     }
4790 }
4791
4792 DbConnection * CollectionDB::getMyConnection()
4793 {
4794     //after some thought, to be thread-safe, must lock at the beginning of this function,
4795     //not only if a new connection is made
4796     connectionMutex->lock();
4797
4798     DbConnection *dbConn;
4799     QThread *currThread = QThread::currentThread();
4800
4801     if (threadConnections->contains(currThread))
4802     {
4803         QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4804         dbConn = it.data();
4805         connectionMutex->unlock();
4806         return dbConn;
4807     }
4808
4809 #ifdef USE_MYSQL
4810     if ( m_dbConnType == DbConnection::mysql )
4811     {
4812         dbConn = new MySqlConnection( static_cast<MySqlConfig*>( m_dbConfig ) );
4813     }
4814     else
4815 #endif
4816 #ifdef USE_POSTGRESQL
4817     if ( m_dbConnType == DbConnection::postgresql )
4818     {
4819         dbConn = new PostgresqlConnection( static_cast<PostgresqlConfig*>( m_dbConfig ) );
4820     }
4821     else
4822 #endif
4823     {
4824         dbConn = new SqliteConnection( static_cast<SqliteConfig*>( m_dbConfig ) );
4825     }
4826
4827     threadConnections->insert(currThread, dbConn);
4828
4829     connectionMutex->unlock();
4830     return dbConn;
4831 }
4832
4833
4834 void
4835 CollectionDB::releasePreviousConnection(QThread *currThread)
4836 {
4837     //if something already exists, delete the object, and erase knowledge of it from the QMap.
4838     connectionMutex->lock();
4839     DbConnection *dbConn;
4840     if (threadConnections->contains(currThread))
4841     {
4842         QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4843         dbConn = it.data();
4844         delete dbConn;
4845         threadConnections->erase(currThread);
4846     }
4847     connectionMutex->unlock();
4848 }
4849
4850 bool
4851 CollectionDB::isConnected()
4852 {
4853     return getMyConnection()->isConnected();
4854 }
4855
4856 //////////////////////////////////////////////////////////////////////////////////////////
4857 // PROTECTED
4858 //////////////////////////////////////////////////////////////////////////////////////////
4859
4860 QByteArray
4861 CollectionDB::md5sum( const QString& artist, const QString& album, const QString& file )
4862 {
4863     KMD5 context( artist.toLower().local8Bit() + album.toLower().local8Bit() + file.local8Bit() );
4864 //     debug() << "MD5 SUM for " << artist << ", " << album << ": " << context.hexDigest() << endl;
4865     return context.hexDigest();
4866 }
4867
4868
4869 void CollectionDB::engineTrackEnded( int finalPosition, int trackLength, const QString &reason )
4870 {
4871     //This is where percentages are calculated
4872     //TODO statistics are not calculated when currentTrack doesn't exist
4873
4874     // Don't update statistics if song has been played for less than 15 seconds
4875     // if ( finalPosition < 15000 ) return;
4876
4877     const KUrl url = EngineController::instance()->bundle().url();
4878     debug() << "track ended: " << url.url() << endl;
4879     PodcastEpisodeBundle peb;
4880     if( getPodcastEpisodeBundle( url.url(), &peb ) )
4881     {
4882         PodcastEpisode *p = PlaylistBrowser::instance()->findPodcastEpisode( peb.url(), peb.parent() );
4883         if ( p )
4884             p->setListened();
4885
4886         if( !url.isLocalFile() )
4887             return;
4888     }
4889
4890     if ( url.path().isEmpty() || !m_autoScoring ) return;
4891
4892     // sanity check
4893     if ( finalPosition > trackLength || finalPosition <= 0 )
4894         finalPosition = trackLength;
4895
4896     int pct = (int) ( ( (double) finalPosition / (double) trackLength ) * 100 );
4897
4898     // increase song counter & calculate new statistics
4899     addSongPercentage( url.path(), pct, reason );
4900 }
4901
4902
4903 void
4904 CollectionDB::timerEvent( QTimerEvent* )
4905 {
4906     scanMonitor();
4907 }
4908
4909
4910 //////////////////////////////////////////////////////////////////////////////////////////
4911 // PUBLIC SLOTS
4912 //////////////////////////////////////////////////////////////////////////////////////////
4913
4914 void
4915 CollectionDB::fetchCover( QWidget* parent, const QString& artist, const QString& album, bool noedit, Q3ListViewItem* item ) //SLOT
4916 {
4917     debug() << "Fetching cover for " << artist << " - " << album << endl;
4918
4919     const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
4920     CoverFetcher* fetcher;
4921     if( isCompilation )
4922         // avoid putting various artists in front of album title. this causes problems for locales other than US.
4923         fetcher = new CoverFetcher( parent, "", album );
4924     else
4925         fetcher = new CoverFetcher( parent, artist, album );
4926     if( item )
4927     {
4928         itemCoverMapMutex->lock();
4929         itemCoverMap->insert( item, fetcher );
4930         itemCoverMapMutex->unlock();
4931     }
4932     connect( fetcher, SIGNAL(result( CoverFetcher* )), SLOT(coverFetcherResult( CoverFetcher* )) );
4933     fetcher->setUserCanEditQuery( !noedit );
4934     fetcher->startFetch();
4935 }
4936
4937 void
4938 CollectionDB::scanMonitor()  //SLOT
4939 {
4940     if ( AmarokConfig::monitorChanges() )
4941         scanModifiedDirs();
4942 }
4943
4944
4945 void
4946 CollectionDB::startScan()  //SLOT
4947 {
4948     QStringList folders = MountPointManager::instance()->collectionFolders();
4949
4950     if ( folders.isEmpty() )
4951     {
4952         //dropTables( false );
4953         //createTables( false );
4954         clearTables( false );
4955         emit scanDone( true );
4956     }
4957     else if( PlaylistBrowser::instance() )
4958     {
4959         emit scanStarted();
4960         ThreadManager::instance()->queueJob( new ScanController( this, false, folders ) );
4961     }
4962 }
4963
4964
4965 void
4966 CollectionDB::stopScan() //SLOT
4967 {
4968     ThreadManager::instance()->abortAllJobsNamed( "CollectionScanner" );
4969 }
4970
4971
4972 //////////////////////////////////////////////////////////////////////////////////////////
4973 // PRIVATE SLOTS
4974 //////////////////////////////////////////////////////////////////////////////////////////
4975
4976 void
4977 CollectionDB::dirDirty( const QString& path )
4978 {
4979     debug() << k_funcinfo << "Dirty: " << path << endl;
4980     QStringList dir;
4981     dir.append( path );
4982
4983     ThreadManager::instance()->queueJob( new ScanController( this, false, dir ) );
4984 }
4985
4986
4987 void
4988 CollectionDB::coverFetcherResult( CoverFetcher *fetcher )
4989 {
4990     if( fetcher->wasError() ) {
4991         error() << fetcher->errors() << endl;
4992         emit coverFetcherError( fetcher->errors().front() );
4993     }
4994
4995     else {
4996         setAlbumImage( fetcher->artist(), fetcher->album(), fetcher->image(), fetcher->amazonURL(), fetcher->asin() );
4997         emit coverFetched( fetcher->artist(), fetcher->album() );
4998     }
4999
5000     //check the validity of the CollectionItem as it may have been deleted e.g. by a
5001     //collection scan while fetching the cover
5002     itemCoverMapMutex->lock();
5003     QMap<Q3ListViewItem*, CoverFetcher*>::Iterator it;
5004     for( it = itemCoverMap->begin(); it != itemCoverMap->end(); ++it )
5005     {
5006         if( it.data() == fetcher )
5007         {
5008             if( it.key()->isOpen() )
5009                 static_cast<CollectionItem*>(it.key())->setPixmap( 0, QPixmap() );
5010             itemCoverMap->erase( it );
5011         }
5012     }
5013     itemCoverMapMutex->unlock();
5014 }
5015
5016 /**
5017  * This query is fairly slow with sqlite, and often happens just
5018  * after the OSD is shown. Threading it restores responsivity.
5019  */
5020 class SimilarArtistsInsertionJob : public ThreadManager::DependentJob
5021 {
5022     virtual bool doJob()
5023     {
5024         CollectionDB::instance()->query( QString( "DELETE FROM related_artists WHERE artist = '%1';" ).arg( escapedArtist ) );
5025
5026         const QString sql = "INSERT INTO related_artists ( artist, suggestion, changedate ) VALUES ( '%1', '%2', 0 );";
5027         oldForeach( suggestions )
5028             CollectionDB::instance()->insert( sql
5029                     .arg( escapedArtist,
5030                           CollectionDB::instance()->escapeString( *it ) ), NULL);
5031
5032         return true;
5033     }
5034
5035     virtual void completeJob() { emit CollectionDB::instance()->similarArtistsFetched( artist ); }
5036
5037     const QString artist;
5038     const QString escapedArtist;
5039     const QStringList suggestions;
5040
5041 public:
5042     SimilarArtistsInsertionJob( CollectionDB *parent, const QString &s, const QStringList &list )
5043             : ThreadManager::DependentJob( parent, "SimilarArtistsInsertionJob" )
5044             , artist( s )
5045             , escapedArtist( parent->escapeString( s ) )
5046             , suggestions( list )
5047     {}
5048 };
5049
5050 void
5051 CollectionDB::similarArtistsFetched( const QString& artist, const QStringList& suggestions )
5052 {
5053     debug() << "Received similar artists\n";
5054
5055     ThreadManager::instance()->queueJob( new SimilarArtistsInsertionJob( this, artist, suggestions ) );
5056 }
5057
5058 void
5059 CollectionDB::aftCheckPermanentTables( const QString &currdeviceid, const QString &currid, const QString &currurl )
5060 {
5061     //DEBUG_BLOCK
5062     //debug() << "deviceid = " << currdeviceid << endl << "url = " << currurl << endl << "uid = " << currid << endl;
5063
5064     QStringList check1, check2;
5065
5066     oldForeach( m_aftEnabledPersistentTables )
5067     {
5068         //debug() << "Checking " << (*it) << endl;;
5069         check1 = query( QString(
5070                 "SELECT url, deviceid "
5071                 "FROM %1 "
5072                 "WHERE uniqueid = '%2';" )
5073                     .arg( escapeString( *it ) )
5074                     .arg( currid ) );
5075
5076         check2 = query( QString(
5077                 "SELECT url, uniqueid "
5078                 "FROM %1 "
5079                 "WHERE deviceid = %2 AND url = '%3';" )
5080                     .arg( escapeString( *it ) )
5081                     .arg( currdeviceid
5082                     , currurl ) );
5083
5084         if( !check1.empty() )
5085         {
5086             //debug() << "uniqueid found, updating url" << endl;
5087             query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5088                                 .arg( escapeString( *it ) )
5089                                 .arg( currdeviceid
5090                                 , currid
5091                                 , currurl ) );
5092         }
5093         else if( !check2.empty() )
5094         {
5095             //debug() << "url found, updating uniqueid" << endl;
5096             query( QString( "UPDATE %1 SET uniqueid = '%2' WHERE deviceid = %3 AND url = '%4';" )
5097                                 .arg( escapeString( *it ) )
5098                                 .arg( currid
5099                                 , currdeviceid
5100                                 , currurl ) );
5101         }
5102     }
5103 }
5104
5105 void
5106 CollectionDB::aftMigratePermanentTablesUrl( const QString& /*oldUrl*/, const QString& newUrl, const QString& uniqueid )
5107 {
5108     //DEBUG_BLOCK
5109     int deviceid = MountPointManager::instance()->getIdForUrl( newUrl );
5110     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, newUrl );
5111     //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5112     //below; will need special case.
5113     //debug() << "deviceid = " << deviceid << endl << "newurl = " << newUrl << endl << "uid = " << uniqueid << endl;
5114     oldForeach( m_aftEnabledPersistentTables )
5115     {
5116         query( QString( "DELETE FROM %1 WHERE deviceid = %2 AND url = '%3';" )
5117                                 .arg( escapeString( *it ) )
5118                                 .arg( deviceid )
5119                                 .arg( escapeString( rpath ) ) );
5120         query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5121                                 .arg( escapeString( *it ) )
5122                                 .arg( deviceid )
5123                                 .arg( escapeString( uniqueid ) )
5124                                 .arg( escapeString( rpath ) ) );
5125     }
5126 }
5127
5128 void
5129 CollectionDB::aftMigratePermanentTablesUniqueId( const QString& /*url*/, const QString& oldid, const QString& newid )
5130 {
5131     //DEBUG_BLOCK
5132     //debug() << "oldid = " << oldid << endl << "newid = " << newid << endl;
5133     //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5134     //below; will need special case.
5135     oldForeach( m_aftEnabledPersistentTables )
5136     {
5137         query( QString( "DELETE FROM %1 WHERE uniqueid = '%2';" )
5138                                 .arg( escapeString( *it ) )
5139                                 .arg( escapeString( newid ) ) );
5140         query( QString( "UPDATE %1 SET uniqueid = '%1' WHERE uniqueid = '%2';" )
5141                                 .arg( escapeString( *it ) )
5142                                 .arg( escapeString( newid ) )
5143                                 .arg( escapeString( oldid ) ) );
5144     }
5145 }
5146
5147
5148 //////////////////////////////////////////////////////////////////////////////////////////
5149 // PRIVATE
5150 //////////////////////////////////////////////////////////////////////////////////////////
5151
5152 void
5153 CollectionDB::initialize()
5154 {
5155     DEBUG_BLOCK
5156
5157     /// Create DBConfig instance:
5158
5159 #ifdef USE_MYSQL
5160     if ( m_dbConnType == DbConnection::mysql )
5161     {
5162         QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5163         QString passwd = AmarokConfig::mySqlPassword2(); // stored as string type
5164
5165         if( passwd.isEmpty() )
5166         {
5167             if( appVersion.startsWith( "1.3" ) )
5168             {
5169                 /// This is because of the encrypted -> plaintext conversion
5170                 passwd = AmarokConfig::mySqlPassword(); // stored as password type
5171                 AmarokConfig::setMySqlPassword2( passwd );
5172             }
5173             else if( appVersion.startsWith( "1.4" ) )
5174             {
5175                 passwd = Amarok::config( "MySql" ).readEntry( "MySqlPassword" ); //read the field as plaintext
5176                 AmarokConfig::setMySqlPassword2( passwd ); // store it in plaintext field
5177             }
5178         }
5179
5180         m_dbConfig = new MySqlConfig(
5181                     AmarokConfig::mySqlHost(),
5182                     AmarokConfig::mySqlPort(),
5183                     AmarokConfig::mySqlDbName(),
5184                     AmarokConfig::mySqlUser(),
5185                     passwd );
5186     }
5187     else
5188 #endif
5189 #ifdef USE_POSTGRESQL
5190     if ( m_dbConnType == DbConnection::postgresql )
5191     {
5192         QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5193         QString passwd = AmarokConfig::postgresqlPassword2();
5194
5195         if( passwd.isEmpty() )
5196         {
5197             if( appVersion.startsWith( "1.3" ) )
5198             {
5199                 /// This is because of the encrypted -> plaintext conversion
5200                 passwd = AmarokConfig::postgresqlPassword(); // stored as password type
5201                 AmarokConfig::setPostgresqlPassword2( passwd );
5202             }
5203             else if( appVersion.startsWith( "1.4" ) &&
5204                    ( appVersion.contains( "beta", false ) ||
5205                      appVersion.contains( "svn",  false ) ) )
5206             {
5207                 passwd = Amarok::config( "Postgresql" ).readEntry( "PostgresqlPassword" );
5208                 AmarokConfig::setPostgresqlPassword2( passwd );
5209             }
5210         }
5211
5212         m_dbConfig = new PostgresqlConfig(
5213                     AmarokConfig::postgresqlHost(),
5214                     AmarokConfig::postgresqlPort(),
5215                     AmarokConfig::postgresqlDbName(),
5216                     AmarokConfig::postgresqlUser(),
5217                     passwd );
5218     }
5219     else
5220 #endif
5221     {
5222         m_dbConfig = new SqliteConfig(
5223                         Amarok::config( "Sqlite" ).readEntry( "location",
5224                                         Amarok::saveLocation() + "collection.db" ) );
5225     }
5226
5227     DbConnection *dbConn = getMyConnection();
5228
5229     if ( !dbConn->isConnected() || !dbConn->isInitialized() )
5230     {
5231         error() << "Failed to connect to or initialise database!" << endl;
5232         Amarok::MessageQueue::instance()->addMessage( dbConn->lastError() );
5233     }
5234     else
5235     {
5236         if ( !isValid() )
5237         {
5238             //No tables seem to exist (as doing a count(url) didn't even return any number, even 0).
5239             warning() << "Tables seem to not exist." << endl;
5240             warning() << "Attempting to create tables (this should be safe; ignore any errors)..." << endl;
5241             createTables(false);
5242             createPersistentTables();
5243             createPodcastTables();
5244             createStatsTable();
5245             warning() << "Tables should now definitely exist. (Stop ignoring errors)" << endl;
5246
5247             //Since we have created the tables, we need to make sure the version numbers are
5248             //set to the correct values. If this is not done now, the database update code may
5249             //run, which could corrupt things.
5250             Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5251             Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5252             Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5253             Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION );    Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5254
5255             setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5256             setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5257             setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5258             setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5259             setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5260         }
5261
5262
5263         // Due to a bug in our sqllite handling code, we have to recreate the indices.
5264         // We should rmeove this before 1.4.5
5265         if ( m_dbConnType == DbConnection::sqlite ) {
5266             QStringList indices = query( "SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;" );
5267             if (!indices.contains("url_tag")) {
5268                 createIndices();
5269             }
5270         }
5271
5272
5273         //updates for the Devices table go here
5274         //put all other update code into checkDatabase()
5275         //make sure that there is no call to MountPointManager in CollectionDB's ctor
5276         //or in methods called from the ctor.
5277         if ( adminValue( "Database Devices Version" ).isEmpty()
5278              && Amarok::config( "CollectionBrowser" ).readEntry( "Database Devices Version", 0 ) == 0 )
5279         {
5280             createDevicesTable();
5281         }
5282         else if ( adminValue( "Database Devices Version" ).toInt() != DATABASE_DEVICES_VERSION
5283               || Amarok::config( "Collection Browser" ).readEntry( "Database Devices Version", 0 ) != DATABASE_DEVICES_VERSION )
5284         {
5285             int prev = adminValue( "Database Devices Version" ).toInt();
5286
5287             if ( prev > DATABASE_DEVICES_VERSION || prev < 0 )
5288             {
5289                 error() << "Database devices version too new for this version of Amarok" << endl;
5290                 exit( 1 );
5291                 //dropDevicesTable();
5292             }
5293             else
5294             {
5295                 debug() << "Updating DEVICES table" << endl;
5296                 //add future Devices update code here
5297             }
5298         }
5299         Amarok::config( "Collection Browser" ).writeEntry( "Database Devices Version", DATABASE_DEVICES_VERSION );
5300         setAdminValue( "Database Devices Version", QString::number( DATABASE_DEVICES_VERSION ) );
5301
5302         //make sure that all indices exist
5303         createIndices();
5304         createPermanentIndices();
5305     }
5306
5307 }
5308
5309 void
5310 CollectionDB::checkDatabase()
5311 {
5312     DEBUG_BLOCK
5313     if ( isValid() )
5314     {
5315         //Inform the user that he should attach as many devices with music as possible
5316         //Hopefully this won't be necessary soon.
5317         //
5318         //Currently broken, so disabled - seems to cause crashes as events are sent to
5319         //the Playlist - maybe it's not fully initialised?
5320         /*
5321         QString text = i18n( "Amarok has to update your database to be able to use the new Dynamic Collection(insert link) feature. Amarok now has to determine on which physical devices your collection is stored. Please attach all removable devices which contain part of your collection and continue. Cancelling will exit Amarok." );
5322         int result = KMessageBox::warningContinueCancel( 0, text, "Database migration" );
5323         if ( result != KMessageBox::Continue )
5324         {
5325             error() << "Dynamic Collection migration was aborted by user...exiting" << endl;
5326             exit( 1 );
5327         }
5328         */
5329
5330         bool needsUpdate = ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5331                            || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION
5332                            || Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5333                            || adminValue( "Database Version" ).toInt() != DATABASE_VERSION
5334                            || Amarok::config( "Collection Browser" ).readEntry( "Database Persistent Tables Version", 0 ) != DATABASE_PERSISTENT_TABLES_VERSION
5335                            || adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION
5336                            || Amarok::config( "Collection Browser" ).readEntry( "Database Podcast Tables Version", 0 ) != DATABASE_PODCAST_TABLES_VERSION
5337                            || adminValue( "Database Podcast Tables Version" ).toInt() != DATABASE_PODCAST_TABLES_VERSION
5338                            || Amarok::config( "Collection Browser" ).readEntry( "Database AFT Version", 0 ) != DATABASE_AFT_VERSION
5339                            || adminValue( "Database AFT Version" ).toInt() != DATABASE_AFT_VERSION );
5340
5341         if ( needsUpdate )
5342         {
5343
5344             KDialog *dialog = new KDialog( 0 );
5345             dialog->setCaption( i18n( "Updating database" ) );
5346             dialog->setModal( false );
5347             dialog->showButtonSeparator( false );
5348
5349
5350             /* TODO: remove the standard window controls from the dialog window, the user should not be able
5351                      to close, minimize, maximize the dialog
5352                      add additional text, e.g. Amarok is currently updating your database. This may take a while.
5353                      Please wait.
5354
5355                      Consider using a ProgressBarDialog
5356             */
5357             QLabel *label = new QLabel( i18n( "Updating database" ), dialog );
5358             dialog->setMainWidget( label );
5359             label->show();
5360             QTimer::singleShot( 0, dialog, SLOT( show() ) );
5361             //process events in the main event loop so that the dialog actually gets shown
5362             kapp->processEvents();
5363             debug() << "Beginning database update" << endl;
5364
5365             updateStatsTables();
5366
5367             updatePersistentTables();
5368
5369             updatePodcastTables();
5370
5371             //This is really a one-off call that fixes a Collection Browser glitch
5372             updateGroupBy();
5373
5374             //remove database file if version is incompatible
5375             if ( Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5376                  || adminValue( "Database Version" ).toInt() != DATABASE_VERSION )
5377             {
5378                 debug() << "Rebuilding database!" << endl;
5379                 dropTables(false);
5380                 createTables(false);
5381             }
5382             delete dialog;
5383         }
5384         emit databaseUpdateDone();
5385     }
5386
5387     // TODO Should write to config in dtor, but it crashes...
5388     Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5389     Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5390     Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5391     Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION );
5392     Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5393
5394     setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5395     setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5396     setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5397     setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5398     setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5399
5400     initDirOperations();
5401 }
5402
5403 void
5404 CollectionDB::updateGroupBy()
5405 {
5406     //This ugly bit of code makes sure the Group BY setting is preserved, after the
5407     //meanings of the values were changed due to the addition of the Composer table.
5408     int version = adminValue( "Database Version" ).toInt();
5409     if (!version) // an even older update
5410        version = Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 );
5411
5412     if ( version && version < 32 )
5413     {
5414         KConfigGroup config = Amarok::config( "Collection Browser" );
5415         int m_cat1 = config.readEntry( "Category1", int(0) );
5416         int m_cat2 = config.readEntry( "Category2", int(0) );
5417         int m_cat3 = config.readEntry( "Category3", int(0) );
5418         m_cat1 = m_cat1 ? ( m_cat1 > 2 ? m_cat1 << 1 : m_cat1 ) : CollectionBrowserIds::IdArtist;
5419         m_cat2 = m_cat2 ? ( m_cat2 > 2 ? m_cat2 << 1 : m_cat2 ) : CollectionBrowserIds::IdAlbum;
5420         m_cat3 = m_cat3 ? ( m_cat3 > 2 ? m_cat3 << 1 : m_cat3 ) : CollectionBrowserIds::IdNone;
5421         config.writeEntry( "Category1", m_cat1 );
5422         config.writeEntry( "Category2", m_cat2 );
5423         config.writeEntry( "Category3", m_cat3 );
5424     }
5425 }
5426
5427 void
5428 CollectionDB::updateStatsTables()
5429 {
5430     if ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5431           || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION )
5432     {
5433         debug() << "Different database stats version detected! Stats table will be updated or rebuilt." << endl;
5434
5435             #if 0 // causes mysterious crashes
5436             if( getType() == DbConnection::sqlite && QFile::exists( Amarok::saveLocation()+"collection.db" ) )
5437             {
5438                 debug() << "Creating a backup of the database in "
5439                         << Amarok::saveLocation()+"collection-backup.db" << '.' << endl;
5440
5441                 bool copied = KIO::NetAccess::file_copy( Amarok::saveLocation()+"collection.db",
5442                                                          Amarok::saveLocation()+"collection-backup.db",
5443                                                          -1 /*perms*/, true /*overwrite*/, false /*resume*/ );
5444
5445                 if( !copied )
5446                 {
5447                     debug() << "Backup failed! Perhaps the volume is not writable." << endl;
5448                     debug() << "Error was: " << KIO::NetAccess::lastErrorString() << endl;
5449                 }
5450             }
5451             #endif
5452
5453         int prev = adminValue( "Database Stats Version" ).toInt();
5454
5455         /* If config returns 3 or lower, it came from an Amarok version that was not aware of
5456            admin table, so we can't trust this table at all */
5457         if( !prev || ( Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 )
5458                   && Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) <= 3  ) )
5459             prev = Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 );
5460
5461         //pre somewhere in the 1.3-1.4 timeframe, the version wasn't stored in the DB, so try to guess it
5462         const QString q = "SELECT COUNT( %1 ) FROM statistics;";
5463         if( !prev && query( q.arg( "url" ) ).first().toInt()
5464                   && query( q.arg( "createdate" ) ).first().toInt()
5465                   && query( q.arg( "accessdate" ) ).first().toInt()
5466                   && query( q.arg( "percentage" ) ).first().toInt()
5467                   && query( q.arg( "playcounter" ) ).first().toInt() )
5468         {
5469             prev = 3;
5470         }
5471
5472         if ( prev < 3 ) //it is from before 1.2, or our poor user is otherwise fucked
5473         {
5474             debug() << "Rebuilding stats-database!" << endl;
5475             dropStatsTableV1();
5476             createStatsTable();
5477         }
5478         else        //Incrementally update the stats table to reach the present version
5479         {
5480             if( prev < 4 ) //every version from 1.2 forward had a stats version of 3
5481             {
5482                 debug() << "Updating stats-database!" << endl;
5483                 query( "ALTER TABLE statistics ADD rating INTEGER DEFAULT 0;" );
5484                 query( "CREATE INDEX rating_stats ON statistics( rating );" );
5485                 query( "UPDATE statistics SET rating=0 WHERE " + boolT() + ';' );
5486             }
5487             if( prev < 5 )
5488             {
5489                 debug() << "Updating stats-database!" << endl;
5490                 query( "UPDATE statistics SET rating = rating * 2;" );
5491             }
5492             if( prev < 8 )  //Versions 6, 7 and 8 all were all attempts to add columns for ATF. his code should do it all.
5493             {
5494                 query( QString( "CREATE TABLE statistics_fix ("
5495                     "url " + textColumnType() + " UNIQUE,"
5496                     "createdate INTEGER,"
5497                     "accessdate INTEGER,"
5498                     "percentage FLOAT,"
5499                     "rating INTEGER DEFAULT 0,"
5500                     "playcounter INTEGER);" ) );
5501
5502                 insert( "INSERT INTO statistics_fix (url, createdate, accessdate, percentage, playcounter, rating)"
5503                         "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics;"
5504                         , NULL );
5505
5506                 dropStatsTableV1();
5507                 createStatsTableV8();
5508
5509                 insert( "INSERT INTO statistics (url, createdate, accessdate, percentage, playcounter, rating)"
5510                         "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics_fix;"
5511                         , NULL );
5512                 query( "DROP TABLE statistics_fix" );
5513             }
5514             if( prev < 9 )
5515             {
5516                 //Update for Dynamic Collection:
5517
5518                 //This is not technically for the stats table, but it is part of the same
5519                 //update, so put it here anyway.
5520                 MountPointManager::instance()->setCollectionFolders( Amarok::config( "Collection" ).readEntry( "Collection Folders", QStringList() ) );
5521
5522                 query( "ALTER TABLE statistics ADD deviceid INTEGER;" );
5523
5524                 //FIXME: (max) i know this is bad but its fast
5525                 QStringList oldURLs = query( "SELECT url FROM statistics;" );
5526                 //it might be necessary to use batch updates to improve speed
5527                 debug() << "Updating " << oldURLs.count() << " rows in statistics" << endl;
5528                 oldForeach( oldURLs )
5529                 {
5530                     bool exists = QFile::exists( *it );
5531                     int deviceid = exists ? MountPointManager::instance()->getIdForUrl( *it ) : -2;
5532                     QString rpath = exists ? MountPointManager::instance()->getRelativePath( deviceid, *it ) : *it;
5533                     QString update = QString( "UPDATE statistics SET deviceid = %1, url = '%2' WHERE " )
5534                                      .arg( deviceid )
5535                                      .arg( escapeString( rpath ) );
5536                     update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5537                     query ( update );
5538                 }
5539             }
5540             if ( prev < 12 )
5541             {
5542                 //re-using old method cause just a slight change to one column...
5543                 //if people are upgrading from earlier than 11, just get the new column
5544                 //earlier  :-)
5545                 createStatsTableV10( true );
5546                 query( "INSERT INTO statistics_fix_ten SELECT url,deviceid,createdate,"
5547                        "accessdate,percentage,rating,playcounter,uniqueid,deleted FROM "
5548                        "statistics;" );
5549                 dropStatsTableV1();
5550                 createStatsTableV10( false );
5551                 query( "INSERT INTO statistics SELECT * FROM statistics_fix_ten;" );
5552                 query( "UPDATE statistics SET uniqueid=NULL;" );
5553             }
5554             else if( prev > DATABASE_STATS_VERSION )
5555             {
5556                 error() << "Database statistics version too new for this version of Amarok. Quitting..." << endl;
5557                 exit( 1 );
5558             }
5559         }
5560     }
5561 }
5562
5563 void
5564 CollectionDB::updatePersistentTables()
5565 {
5566     QString PersistentVersion = adminValue( "Database Persistent Tables Version" );
5567     if ( PersistentVersion.isEmpty() )
5568     {
5569         /* persistent tables didn't have a version on 1.3X and older, but let's be nice and try to
5570            copy/keep the good information instead of just deleting the tables */
5571         debug() << "Detected old schema for tables with important data. Amarok will convert the tables, ignore any \"table already exists\" errors." << endl;
5572         createPersistentTables();
5573         /* Copy lyrics */
5574         debug() << "Trying to get lyrics from old db schema." << endl;
5575         QStringList Lyrics = query( "SELECT url, lyrics FROM tags where tags.lyrics IS NOT NULL;" );
5576         for (uint i=0; i<Lyrics.count(); i+=2  )
5577         setLyrics( Lyrics[i], Lyrics[i+1]  );
5578         debug() << "Building podcast tables" << endl;
5579         createPodcastTables();
5580     }
5581     else if ( PersistentVersion == "1" || PersistentVersion == "2" )
5582     {
5583         createPersistentTables(); /* From 1 to 2 nothing changed. There was just a bug on the code, and
5584                                      on some cases the table wouldn't be created.
5585                                      From 2 to 3, lyrics were made plain text, instead of HTML */
5586         debug() << "Converting Lyrics to Plain Text." << endl;
5587         QStringList Lyrics = query( "SELECT url, lyrics FROM lyrics;" );
5588         for (uint i=0; i<Lyrics.count(); i+=2  )
5589         setLyrics( Lyrics[i], Lyrics[i+1]  );
5590         debug() << "Building podcast tables" << endl;
5591         createPodcastTables();
5592     }
5593     else if ( PersistentVersion.toInt() < 4 )
5594     {
5595         debug() << "Building podcast tables" << endl;
5596         createPodcastTables();
5597     }
5598     else
5599     {
5600         if ( PersistentVersion.toInt() < 5 )
5601         {
5602             debug() << "Updating podcast tables" << endl;
5603             query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5604             query( "ALTER TABLE podcastepisodes ADD localurl " + textColumnType() + ';' );
5605             query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5606             query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5607             query( "ALTER TABLE podcastepisodes DROP comment;" );
5608             query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5609             query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
5610         }
5611         if ( PersistentVersion.toInt() < 6 )
5612         {
5613             debug() << "Updating podcast tables" << endl;
5614             query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5615             query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5616             query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5617             query( "ALTER TABLE podcastepisodes DROP comment;" );
5618             query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5619         }
5620         if ( PersistentVersion.toInt() < 11 )
5621         {
5622             debug() << "This is used to handle problems from uniqueid changeover and should not do anything" << endl;
5623         }
5624         if ( PersistentVersion.toInt() < 12 )
5625         {
5626             debug() << "Adding playlists table..." << endl;
5627             createPersistentTablesV12();
5628         }
5629         if ( PersistentVersion.toInt() < 13 )
5630         {
5631             //Update for Dynamic Collection:
5632             query( "ALTER TABLE lyrics ADD deviceid INTEGER;" );
5633
5634             //FIXME: (max) i know this is bad but its fast
5635             QStringList oldURLs = query( "SELECT url FROM lyrics;" );
5636             //it might be necessary to use batch updates to improve speed
5637             debug() << "Updating " << oldURLs.count() << " rows in lyrics" << endl;
5638             oldForeach( oldURLs )
5639             {
5640                 int deviceid = MountPointManager::instance()->getIdForUrl( *it );
5641                 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, *it );
5642                 QString update = QString( "UPDATE lyrics SET deviceid = %1, url = '%2' WHERE " )
5643                                  .arg( deviceid )
5644                                  .arg( escapeString( rpath ) );
5645                 update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5646                 query ( update );
5647             }
5648         }
5649         if ( PersistentVersion.toInt() < 15 )
5650         {
5651             createPersistentTablesV14( true );
5652             query( "INSERT INTO amazon_fix SELECT asin,locale,filename,refetchdate FROM amazon;" );
5653             query( "INSERT INTO lyrics_fix SELECT url,deviceid,lyrics FROM lyrics;" );
5654             query( "INSERT INTO playlists_fix SELECT playlist,url,tracknum FROM playlists;" );
5655             dropPersistentTablesV14();
5656             createPersistentTablesV14( false );
5657             query( "INSERT INTO amazon SELECT * FROM amazon_fix;" );
5658             query( "INSERT INTO lyrics SELECT * FROM lyrics_fix;" );
5659             query( "INSERT INTO playlists SELECT * FROM playlists_fix;" );
5660         }
5661         if ( PersistentVersion.toInt() < 17 )
5662         {
5663             //drop old labels and label tables, they were never used anyway and just confuse things
5664             query( "DROP TABLE label;" );
5665             query( "DROP TABLE labels;" );
5666             query( "DROP TABLE tags_labels;" );
5667             //update for label support
5668             QString labelsAutoIncrement = "";
5669             if ( getDbConnectionType() == DbConnection::postgresql )
5670             {
5671                 query( QString( "CREATE SEQUENCE labels_seq;" ) );
5672
5673                 labelsAutoIncrement = QString("DEFAULT nextval('labels_seq')");
5674             }
5675             else if ( getDbConnectionType() == DbConnection::mysql )
5676             {
5677                 labelsAutoIncrement = "AUTO_INCREMENT";
5678             }
5679
5680             query( QString( "CREATE TABLE labels ("
5681                             "id INTEGER PRIMARY KEY " + labelsAutoIncrement + ", "
5682                             "name " + textColumnType() + ", "
5683                             "type INTEGER);" ) );
5684
5685             query( QString( "CREATE TABLE tags_labels ("
5686                             "deviceid INTEGER,"
5687                             "url " + exactTextColumnType() + ", "
5688                             "uniqueid " + exactTextColumnType(32) + ", "      //m:n relationship, DO NOT MAKE UNIQUE!
5689                             "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
5690
5691             query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
5692             query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5693             query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5694         }
5695         if ( PersistentVersion.toInt() < 18 )
5696         {
5697             query( "ALTER TABLE lyrics ADD uniqueid " + exactTextColumnType(32) + ';' );
5698             query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
5699         }
5700         if ( PersistentVersion.toInt() < 19 )
5701         {
5702             query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" );   //m:n relationship, DO NOT MAKE UNIQUE!
5703         }
5704         //Up to date. Keep this number   \/   in sync!
5705         if ( PersistentVersion.toInt() > 19 || PersistentVersion.toInt() < 0 )
5706         {
5707             //Something is horribly wrong
5708             if ( adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION )
5709             {
5710                 error() << "There is a bug in Amarok: instead of destroying your valuable"
5711                         << " database tables, I'm quitting" << endl;
5712                 exit( 1 );
5713
5714                 debug() << "Rebuilding persistent tables database!" << endl;
5715                 dropPersistentTables();
5716                 createPersistentTables();
5717             }
5718         }
5719     }
5720 }
5721
5722 void
5723 CollectionDB::updatePodcastTables()
5724 {
5725     QString PodcastVersion = adminValue( "Database Podcast Tables Version" );
5726     if ( PodcastVersion.toInt() < 2 )
5727     {
5728         createPodcastTablesV2( true );
5729         query( "INSERT INTO podcastchannels_fix SELECT url,title,weblink,image,comment,"
5730                "copyright,parent,directory,autoscan,fetchtype,autotransfer,haspurge,"
5731                "purgecount FROM podcastchannels;" );
5732         query( "INSERT INTO podcastepisodes_fix SELECT id,url,localurl,parent,guid,title,"
5733                "subtitle,composer,comment,filetype,createdate,length,size,isNew FROM "
5734                "podcastepisodes;" );
5735         query( "INSERT INTO podcastfolders_fix SELECT id,name,parent,isOpen FROM podcastfolders;" );
5736         dropPodcastTablesV2();
5737         createPodcastTablesV2( false );
5738         query( "INSERT INTO podcastchannels SELECT * FROM podcastchannels_fix;" );
5739         query( "INSERT INTO podcastepisodes SELECT * FROM podcastepisodes_fix;" );
5740         query( "INSERT INTO podcastfolders SELECT * FROM podcastfolders_fix;" );
5741     }
5742
5743     //Keep this number in sync    \/
5744     if ( PodcastVersion.toInt() > 2 )
5745     {
5746         error() << "Something is very wrong with the Podcast Tables. Aborting" << endl;
5747         exit( 1 );
5748         dropPodcastTables();
5749         createPodcastTables();
5750     }
5751 }
5752
5753 void
5754 CollectionDB::vacuum()
5755 {
5756     if ( DbConnection::sqlite == getDbConnectionType() ||
5757          DbConnection::postgresql == getDbConnectionType() )
5758     {
5759         //Clean up DB and free unused space.
5760         debug() << "Running VACUUM" << endl;
5761         query( "VACUUM;" );
5762     }
5763 }
5764
5765 void
5766 CollectionDB::destroy()
5767 {
5768     //do we need or want to delete the actual connection objects as well as clearing them from the QMap?
5769     //or does QMap's clear function delete them?
5770     //this situation is not at all likely to happen often, so leaving them might be okay to prevent a
5771     //thread from having its connection torn out from under it...not likely, but possible
5772     //and leaving it should not end up eating much memory at all
5773
5774     connectionMutex->lock();
5775
5776     threadConnections->clear();
5777     delete m_dbConfig;
5778
5779     connectionMutex->unlock();
5780 }
5781
5782 void
5783 CollectionDB::scanModifiedDirs()
5784 {
5785     if ( !m_scanInProgress
5786             && ( !CollectionView::instance() || !CollectionView::instance()->isOrganizingFiles() )
5787             && ( !MediaBrowser::instance() || !MediaBrowser::instance()->isTranscoding() ) )
5788     {
5789         //we check if a job is pending because we don't want to abort incremental collection readings
5790         if ( !ThreadManager::instance()->isJobPending( "CollectionScanner" ) && PlaylistBrowser::instance() )
5791         {
5792             m_scanInProgress = true;
5793             m_rescanRequired = false;
5794             emit scanStarted();
5795
5796             ThreadManager::instance()->onlyOneJob( new ScanController( this, true ) );
5797         }
5798     }
5799     else
5800         m_rescanRequired = true;
5801 }
5802
5803
5804 void
5805 CollectionDB::customEvent( QEvent *e )
5806 {
5807     if ( e->type() == (int)ScanController::JobFinishedEvent )
5808     {
5809         ScanController* s = static_cast<ScanController*>( e );
5810         m_scanInProgress = false;
5811
5812         if ( s->isIncremental() )
5813         {
5814             debug() << "JobFinishedEvent from Incremental ScanController received.\n";
5815             emit scanDone( s->hasChanged() );
5816
5817             // check if something changed while we were scanning. in this case we should
5818             // rescan again, now.
5819             if ( m_rescanRequired )
5820                 QTimer::singleShot( 0, CollectionDB::instance(), SLOT( scanMonitor() ) );
5821         }
5822         else
5823         {
5824             debug() << "JobFinishedEvent from ScanController received.\n";
5825             emit scanDone( s->wasSuccessful() );
5826         }
5827     }
5828 }
5829
5830
5831 QString
5832 CollectionDB::loadHashFile( const QByteArray& hash, uint width )
5833 {
5834     //debug() << "loadHashFile: " << hash << " - " << width << endl;
5835
5836     QString full = tagCoverDir().filePath( hash );
5837
5838     if ( width == 0 ) {
5839         if ( QFileInfo( full ).isReadable() ) {
5840             //debug() << "loadHashFile: fullsize: " << full << endl;
5841             return full;
5842         }
5843     } else {
5844         if ( width == 1 ) width = AmarokConfig::coverPreviewSize();
5845         QByteArray widthKey = makeWidthKey( width );
5846
5847         QString path = cacheCoverDir().filePath( widthKey + hash );
5848         if ( QFileInfo( path ).isReadable() ) {
5849             //debug() << "loadHashFile: scaled: " << path << endl;
5850             return path;
5851         } else if ( QFileInfo( full ).isReadable() ) {
5852             //debug() << "loadHashFile: scaling: " << full << endl;
5853             QImage image( full );
5854             if ( image.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" ) ) {
5855                 //debug() << "loadHashFile: scaled: " << path << endl;
5856                 return path;
5857             }
5858         }
5859     }
5860     return QString();
5861 }
5862
5863
5864 bool
5865 CollectionDB::extractEmbeddedImage( const MetaBundle &trackInformation, QByteArray& hash )
5866 {
5867     //debug() << "extractEmbeddedImage: " << hash << " - " << trackInformation.url().path() << endl;
5868
5869     MetaBundle::EmbeddedImageList images;
5870     trackInformation.embeddedImages( images );
5871     oldForeachType ( MetaBundle::EmbeddedImageList, images ) {
5872         if ( hash.isEmpty() || (*it).hash() == hash ) {
5873             if ( (*it).save( tagCoverDir() ) ) {
5874                 //debug() << "extractEmbeddedImage: saved to " << tagCoverDir().path() << endl;
5875                 hash = (*it).hash();
5876                 return true;
5877             }
5878         }
5879     }
5880     return false;
5881 }
5882
5883 QStringList
5884 CollectionDB::getLabels( const QString &url, const uint type )
5885 {
5886     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5887     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
5888     return query( QString( "SELECT labels.name FROM labels "
5889                            "LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5890                            "WHERE labels.type = %1 AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5891                          .arg( type ).arg( deviceid ).arg( escapeString( rpath ) ) );
5892 }
5893
5894 void
5895 CollectionDB::cleanLabels()
5896 {
5897     DEBUG_BLOCK
5898     QStringList labelIds = query( "select labels.id "
5899                                   "from labels left join tags_labels on labels.id = tags_labels.labelid "
5900                                   "where tags_labels.labelid is NULL;" );
5901     if ( !labelIds.isEmpty() )
5902     {
5903         QString ids;
5904         oldForeach( labelIds )
5905         {
5906             if ( !ids.isEmpty() )
5907                 ids += ',';
5908             ids += *it;
5909         }
5910         query( QString( "DELETE FROM labels "
5911                         "WHERE labels.id IN ( %1 );" )
5912                         .arg( ids ) );
5913     }
5914 }
5915
5916 void
5917 CollectionDB::setLabels( const QString &url, const QStringList &labels, const QString &uid, const uint type )
5918 {
5919     DEBUG_BLOCK
5920     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5921     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5922     QStringList labelIds = query( QString( "SELECT id FROM labels WHERE type = %1;" ).arg( type ) );
5923     QString ids;
5924     if ( !labelIds.isEmpty() )
5925     {
5926         oldForeach( labelIds )
5927         {
5928             if ( !ids.isEmpty() )
5929                 ids += ',';
5930             ids += *it;
5931         }
5932         //TODO: max: add uniqueid handling
5933         query( QString( "DELETE FROM tags_labels "
5934                         "WHERE tags_labels.labelid IN (%1) AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5935                         .arg( ids,  QString::number(deviceid),  rpath ) );
5936     }
5937
5938     oldForeach( labels )
5939     {
5940         int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5941                                          .arg( type ).arg( escapeString( *it ) ) ).first().toInt();
5942         if ( !id )
5943         {
5944             id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5945                                   .arg( type ).arg( escapeString( *it ) ), "labels" );
5946         }
5947         insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5948                          .arg( QString::number(id), QString::number(deviceid),  rpath, escapeString( uid ) ), 0 );
5949     }
5950
5951     emit labelsChanged( url );
5952 }
5953
5954 void
5955 CollectionDB::removeLabels( const QString &url, const QStringList &labels, const uint type )
5956 {
5957     DEBUG_BLOCK
5958     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5959     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5960     QString sql = QString( "DELETE FROM tags_labels "
5961                              "FROM tags_labels AS t LEFT JOIN labels AS l ON t.labelid = l.id "
5962                              "WHERE l.type = %1 AND t.deviceid = %2 AND t.url = '%3' AND ( 0" )
5963                              .arg( type ).arg( deviceid ).arg( rpath );
5964     oldForeach( labels )
5965     {
5966         sql += QString( " OR l.name = '%1'" ).arg( escapeString( *it ) );
5967     }
5968     sql += ");";
5969     query( sql );
5970
5971     emit labelsChanged( url );
5972 }
5973
5974 bool
5975 CollectionDB::addLabel( const QString &url, const QString &label, const QString &uid, const uint type )
5976 {
5977     DEBUG_BLOCK
5978     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5979     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5980
5981     int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5982                              .arg( type ).arg( escapeString( label ) ) ).first().toInt();
5983     bool labelAlreadyExists = id > 0;
5984     if ( !id )
5985     {
5986         id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5987                               .arg( type ).arg( escapeString( label ) ), "labels" );
5988     }
5989     if ( labelAlreadyExists )
5990     {
5991         //we can return if the link between the tags row and the labels row already exists
5992         int count = query( QString( "SELECT COUNT(*) FROM tags_labels WHERE labelid = %1 AND deviceid = %2 AND url = '%3';" )
5993                                     .arg( id ).arg( deviceid ).arg( rpath ) ).first().toInt();
5994         if ( count )
5995             return false;
5996     }
5997     insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5998                      .arg( QString::number(id), QString::number(deviceid), rpath, escapeString( uid ) ), "tags_labels" );
5999
6000     emit labelsChanged( url );
6001     return true;
6002 }
6003
6004 QStringList
6005 CollectionDB::favoriteLabels( int type, int count )
6006 {
6007     return query( QString( "SELECT labels.name, count( tags_labels.labelid ) "
6008                            "FROM labels LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
6009                            "WHERE labels.type = %1 GROUP BY labels.name "
6010                            "ORDER BY count(tags_labels.labelid) DESC LIMIT %2;" )
6011                            .arg( QString::number( type ), QString::number( count ) ) );
6012 }
6013
6014 QDir
6015 CollectionDB::largeCoverDir() //static
6016 {
6017     return QDir( Amarok::saveLocation( "albumcovers/large/" ) );
6018 }
6019
6020
6021 QDir
6022 CollectionDB::tagCoverDir()  //static
6023 {
6024     return QDir( Amarok::saveLocation( "albumcovers/tagcover/" ) );
6025 }
6026
6027
6028 QDir
6029 CollectionDB::cacheCoverDir()  //static
6030 {
6031     return QDir( Amarok::saveLocation( "albumcovers/cache/" ) );
6032 }
6033
6034
6035 //////////////////////////////////////////////////////////////////////////////////////////
6036 // CLASS DbConnection
6037 //////////////////////////////////////////////////////////////////////////////////////////
6038
6039 DbConnection::DbConnection()
6040     : m_initialized( false )
6041 {}
6042
6043
6044 //////////////////////////////////////////////////////////////////////////////////////////
6045 // CLASS SqliteConnection
6046 //////////////////////////////////////////////////////////////////////////////////////////
6047
6048 SqliteConnection::SqliteConnection( const SqliteConfig* config )
6049     : DbConnection()
6050 {
6051
6052     DEBUG_BLOCK
6053
6054     const QByteArray path = QFile::encodeName( config->dbFile() );
6055
6056     // Open database file and check for correctness
6057     QFile file( path );
6058     if ( file.open( QIODevice::ReadOnly ) )
6059     {
6060         QByteArray format;
6061         format = file.readLine( 50 );
6062         if ( !format.startsWith( "SQLite format 3" ) )
6063         {
6064             warning() << "Database versions incompatible. Removing and rebuilding database.\n";
6065         }
6066         else if ( sqlite3_open( path, &m_db ) != SQLITE_OK )
6067         {
6068             warning() << "Database file corrupt. Removing and rebuilding database.\n";
6069             sqlite3_close( m_db );
6070         }
6071         else
6072             m_initialized = true;
6073     }
6074
6075     if ( !m_initialized )
6076     {
6077         // Remove old db file; create new
6078         QFile::remove( path );
6079         if ( sqlite3_open( path, &m_db ) == SQLITE_OK )
6080         {
6081             m_initialized = true;
6082         }
6083     }
6084     if ( m_initialized )
6085     {
6086         if( sqlite3_create_function(m_db, "rand", 0, SQLITE_UTF8, NULL, sqlite_rand, NULL, NULL) != SQLITE_OK )
6087             m_initialized = false;
6088         if( sqlite3_create_function(m_db, "power", 2, SQLITE_UTF8, NULL, sqlite_power, NULL, NULL) != SQLITE_OK )
6089             m_initialized = false;
6090         if ( sqlite3_create_function(m_db, "like", 2, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6091             m_initialized = false;
6092         if ( sqlite3_create_function(m_db, "like", 3, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6093             m_initialized = false;
6094     }
6095
6096     //optimization for speeding up SQLite
6097     query( "PRAGMA default_synchronous = OFF;" );
6098 }
6099
6100
6101 SqliteConnection::~SqliteConnection()
6102 {
6103     if ( m_db ) sqlite3_close( m_db );
6104 }
6105
6106
6107 QStringList SqliteConnection::query( const QString& statement, bool /*suppressDebug*/ )
6108 {
6109
6110     QStringList values;
6111     int error;
6112     int rc = 0;
6113     const char* tail;
6114     sqlite3_stmt* stmt;
6115     int busyCnt = 0;
6116     int retryCnt = 0;
6117
6118     do {
6119         //compile SQL program to virtual machine, reattempting if busy
6120         do {
6121             if ( busyCnt )
6122             {
6123                 ::usleep( 100000 );      // Sleep 100 msec
6124                 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt << endl;
6125             }
6126             error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6127         }
6128         while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6129
6130         if ( error != SQLITE_OK )
6131         {
6132             if ( SQLITE_BUSY==error )
6133                 Debug::error() << "Gave up waiting for lock to clear" << endl;
6134             Debug::error() << k_funcinfo << " sqlite3_compile error:" << endl;
6135             Debug::error() << sqlite3_errmsg( m_db ) << endl;
6136             Debug::error() << "on query: " << statement << endl;
6137             values = QStringList();
6138             break;
6139         }
6140         else
6141         {
6142             busyCnt = 0;
6143             int number = sqlite3_column_count( stmt );
6144             //execute virtual machine by iterating over rows
6145             while ( true )
6146             {
6147                 error = sqlite3_step( stmt );
6148
6149                 if ( error == SQLITE_BUSY )
6150                 {
6151                     if ( busyCnt++ > 120 ) {
6152                         Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6153                         break;
6154                     }
6155                     ::usleep( 100000 ); // Sleep 100 msec
6156                     debug() << "sqlite3_step: BUSY counter: " << busyCnt << endl;
6157                     continue;
6158                 }
6159                 if ( error == SQLITE_MISUSE )
6160                     debug() << "sqlite3_step: MISUSE" << endl;
6161                 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6162                     break;
6163
6164                 //iterate over columns
6165                 for ( int i = 0; i < number; i++ )
6166                 {
6167                     values << QString::fromUtf8( reinterpret_cast<const char*>( sqlite3_column_text( stmt, i ) ) );
6168                 }
6169             }
6170             //deallocate vm resources
6171             rc = sqlite3_finalize( stmt );
6172
6173             if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA )
6174             {
6175                 Debug::error() << k_funcinfo << "sqlite_step error.\n";
6176                 Debug::error() << sqlite3_errmsg( m_db ) << endl;
6177                 Debug::error() << "on query: " << statement << endl;
6178                 values = QStringList();
6179             }
6180             if ( rc == SQLITE_SCHEMA )
6181             {
6182                 retryCnt++;
6183                 debug() << "SQLITE_SCHEMA error occurred on query: " << statement << endl;
6184                 if ( retryCnt < 10 )
6185                     debug() << "Retrying now." << endl;
6186                 else
6187                 {
6188                     Debug::error() << "Retry-Count has reached maximum. Aborting this SQL statement!" << endl;
6189                     Debug::error() << "SQL statement: " << statement << endl;
6190                     values = QStringList();
6191                 }
6192             }
6193         }
6194     }
6195     while ( rc == SQLITE_SCHEMA && retryCnt < 10 );
6196
6197     return values;
6198 }
6199
6200
6201 int SqliteConnection::insert( const QString& statement, const QString& /* table */ )
6202 {
6203     int error;
6204     int rc = 0;
6205     const char* tail;
6206     sqlite3_stmt* stmt;
6207     int busyCnt = 0;
6208     int retryCnt = 0;
6209
6210     do {
6211         //compile SQL program to virtual machine, reattempting if busy
6212         do {
6213             if ( busyCnt )
6214             {
6215                 ::usleep( 100000 );      // Sleep 100 msec
6216                 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt << endl;
6217             }
6218             error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6219         }
6220         while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6221
6222         if ( error != SQLITE_OK )
6223         {
6224             if ( SQLITE_BUSY==error )
6225                 Debug::error() << "Gave up waiting for lock to clear" << endl;
6226             Debug::error() << k_funcinfo << " sqlite3_compile error:" << endl;
6227             Debug::error() << sqlite3_errmsg( m_db ) << endl;
6228             Debug::error() << "on insert: " << statement << endl;
6229             break;
6230         }
6231         else
6232         {
6233             busyCnt = 0;
6234             //execute virtual machine by iterating over rows
6235             while ( true )
6236             {
6237                 error = sqlite3_step( stmt );
6238
6239                 if ( error == SQLITE_BUSY )
6240                 {
6241                     if ( busyCnt++ > 120 ) {
6242                         Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6243                         break;
6244                     }
6245                     ::usleep( 100000 ); // Sleep 100 msec
6246                     debug() << "sqlite3_step: BUSY counter: " << busyCnt << endl;
6247                 }
6248                 if ( error == SQLITE_MISUSE )
6249                     debug() << "sqlite3_step: MISUSE" << endl;
6250                 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6251                     break;
6252             }
6253             //deallocate vm resources
6254             rc = sqlite3_finalize( stmt );
6255
6256             if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA)
6257             {
6258                 Debug::error() << k_funcinfo << "sqlite_step error.\n";
6259                 Debug::error() << sqlite3_errmsg( m_db ) << endl;
6260                 Debug::error() << "on insert: " << statement << endl;
6261             }
6262             if ( rc == SQLITE_SCHEMA )
6263             {
6264                 retryCnt++;
6265                 debug() << "SQLITE_SCHEMA error occurred on insert: " << statement << endl;
6266                 if ( retryCnt < 10 )
6267                     debug() << "Retrying now." << endl;
6268                 else
6269                 {
6270                     Debug::error() << "Retry-Count has reached maximum. Aborting this SQL insert!" << endl;
6271                     Debug::error() << "SQL statement: " << statement << endl;
6272                 }
6273             }
6274         }
6275     }
6276     while ( SQLITE_SCHEMA == rc && retryCnt < 10 );
6277     return sqlite3_last_insert_rowid( m_db );
6278 }
6279
6280
6281 // this implements a RAND() function compatible with the MySQL RAND() (0-param-form without seed)
6282 void SqliteConnection::sqlite_rand(sqlite3_context *context, int /*argc*/, sqlite3_value ** /*argv*/)
6283 {
6284     sqlite3_result_double( context, static_cast<double>(KRandom::random()) / (RAND_MAX+1.0) );
6285 }
6286
6287 // this implements a POWER() function compatible with the MySQL POWER()
6288 void SqliteConnection::sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv)
6289 {
6290     Q_ASSERT( argc==2 );
6291     if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ) {
6292         sqlite3_result_null(context);
6293         return;
6294     }
6295     double a = sqlite3_value_double(argv[0]);
6296     double b = sqlite3_value_double(argv[1]);
6297     sqlite3_result_double( context, pow(a,b) );
6298 }
6299
6300 // this implements a LIKE() function that overrides the default string comparison function
6301 // Reason: default function is case-sensitive for utf8 strings (BUG: 116458, ...)
6302 void SqliteConnection::sqlite_like_new( sqlite3_context *context, int argc, sqlite3_value **argv )
6303 {
6304
6305     const unsigned char *zA = sqlite3_value_text( argv[0] );
6306     const unsigned char *zB = sqlite3_value_text( argv[1] );
6307
6308     QString pattern = QString::fromUtf8( (const char*)zA );
6309     QString text = QString::fromUtf8( (const char*)zB );
6310
6311     int begin = pattern.startsWith( "%" ), end = pattern.endsWith( "%" );
6312     if (begin)
6313         pattern = pattern.right( pattern.length() - 1 );
6314     if (end)
6315         pattern = pattern.left( pattern.length() - 1 );
6316
6317     if( argc == 3 ) // The function is given an escape character. In likeCondition() it defaults to '/'
6318         pattern.replace( "/%", "%" ).replace( "/_", "_" ).replace( "//", "/" );
6319
6320     int result = 0;
6321     if ( begin && end ) result = ( text.find( pattern, 0, 0 ) != -1);
6322     else if ( begin ) result = text.endsWith( pattern, Qt::CaseInsensitive );
6323     else if ( end ) result = text.startsWith( pattern, Qt::CaseInsensitive );
6324     else result = ( text.toLower() == pattern.toLower() );
6325
6326     sqlite3_result_int( context, result );
6327 }
6328
6329 //////////////////////////////////////////////////////////////////////////////////////////
6330 // CLASS MySqlConnection
6331 //////////////////////////////////////////////////////////////////////////////////////////
6332
6333 #ifdef USE_MYSQL
6334 MySqlConnection::MySqlConnection( const MySqlConfig* config )
6335     : DbConnection()
6336     , m_connected( false )
6337 {
6338     DEBUG_BLOCK
6339
6340     debug() << k_funcinfo << endl;
6341     m_db = mysql_init(NULL);
6342     if (m_db)
6343     {
6344 //         if ( config->username().isEmpty() )
6345 //             pApp->slotConfigAmarok("MySql");
6346
6347         if ( mysql_real_connect( m_db, config->host().toLatin1(),
6348                                        config->username().toLatin1(),
6349                                        config->password().toLatin1(),
6350                                        config->database().toLatin1(),
6351                                        config->port(),
6352                                        NULL, CLIENT_COMPRESS ) )
6353         {
6354             m_initialized = true;
6355
6356 #if MYSQL_VERSION_ID >= 40113
6357             // now set the right charset for the connection
6358             QStringList my_qslist = query( "SHOW VARIABLES LIKE 'character_set_database'" );
6359             if( !my_qslist.isEmpty() && !mysql_set_character_set( m_db, const_cast<char *>( my_qslist[1].toLatin1() ) ) )
6360                 //charset was updated
6361                 debug() << "Connection Charset is now: " << my_qslist[1].toLatin1() << endl;
6362             else
6363                 error() << "Failed to set database charset\n";
6364 #endif
6365
6366             m_db->reconnect = 1; //setting reconnect flag for newer mysqld
6367             m_connected = true;
6368         }
6369         else
6370         {
6371
6372             if ( mysql_real_connect(
6373                     m_db,
6374                     config->host().toLatin1(),
6375                     config->username().toLatin1(),
6376                     config->password().toLatin1(),
6377                     NULL,
6378                     config->port(),
6379                     NULL, CLIENT_COMPRESS))
6380             {
6381                 if ( mysql_query(m_db,
6382                     QString( "CREATE DATABASE " + config->database() ).toLatin1() ) )
6383                     { m_connected = true; m_initialized = true; }
6384                 else
6385                     { setMysqlError(); }
6386             }
6387             else
6388                 setMysqlError();
6389
6390         }
6391     }
6392     else
6393         error() << "Failed to allocate/initialize MySql struct\n";
6394 }
6395
6396
6397 MySqlConnection::~MySqlConnection()
6398 {
6399     if ( m_db ) mysql_close( m_db );
6400 }
6401
6402
6403 QStringList MySqlConnection::query( const QString& statement, bool suppressDebug )
6404 {
6405     QStringList values;
6406
6407     if ( !mysql_query( m_db, statement.toUtf8() ) )
6408     {
6409         MYSQL_RES* result;
6410         if ( ( result = mysql_use_result( m_db ) ) )
6411         {
6412             int number = mysql_field_count( m_db );
6413             MYSQL_ROW row;
6414             while ( ( row = mysql_fetch_row( result ) ) )
6415             {
6416                 for ( int i = 0; i < number; i++ )
6417                 {
6418                   values << QString::fromUtf8( (const char*)row[i] );
6419                 }
6420             }
6421         }
6422         else
6423         {
6424             if ( mysql_field_count( m_db ) != 0 )
6425             {
6426                 if ( !suppressDebug )
6427                     debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6428                 values = QStringList();
6429             }
6430         }
6431         mysql_free_result( result );
6432     }
6433     else
6434     {
6435         if ( !suppressDebug )
6436             debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6437         values = QStringList();
6438     }
6439
6440     return values;
6441 }
6442
6443
6444 int MySqlConnection::insert( const QString& statement, const QString& /* table */ )
6445 {
6446     mysql_query( m_db, statement.toUtf8() );
6447     if ( mysql_errno( m_db ) )
6448     {
6449         debug() << "MYSQL INSERT FAILED: " << mysql_error( m_db ) << "\n" << "FAILED INSERT: " << statement << endl;
6450     }
6451     return mysql_insert_id( m_db );
6452 }
6453
6454
6455 void
6456 MySqlConnection::setMysqlError()
6457 {
6458     m_error = i18n("MySQL reported the following error:<br>") + mysql_error(m_db)
6459             + i18n("<p>You can configure MySQL in the Collection section under Settings->Configure Amarok</p>");
6460 }
6461 #endif
6462
6463
6464 //////////////////////////////////////////////////////////////////////////////////////////
6465 // CLASS PostgresqlConnection
6466 //////////////////////////////////////////////////////////////////////////////////////////
6467
6468 #ifdef USE_POSTGRESQL
6469 PostgresqlConnection::PostgresqlConnection( const PostgresqlConfig* config )
6470       : DbConnection()
6471       , m_connected( false )
6472 {
6473   QString conninfo;
6474     debug() << k_funcinfo << endl;
6475
6476 //     if ( config->username().isEmpty() )
6477 //         pApp->slotConfigAmarok("Postgresql");
6478
6479     conninfo = "host='" + config->host() +
6480       "' port=" + QString::number( config->port() ) +
6481       " dbname='" + config->database() +
6482       "' user='" + config->username() +
6483       "' password='" + config->password() + '\'';
6484
6485     m_db = PQconnectdb( conninfo.toUtf8() );
6486
6487     if (!m_db)
6488     {
6489         debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6490         error() << "Failed to allocate/initialize Postgresql struct\n";
6491         setPostgresqlError();
6492         return;
6493     }
6494
6495     if (PQstatus(m_db) != CONNECTION_OK)
6496     {
6497         debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6498         error() << "Failed to allocate/initialize Postgresql struct\n";
6499         setPostgresqlError();
6500         PQfinish(m_db);
6501         m_db = NULL;
6502         return;
6503     }
6504
6505     m_initialized = true;
6506     m_connected = true;
6507 }
6508
6509
6510 PostgresqlConnection::~PostgresqlConnection()
6511 {
6512     if ( m_db ) PQfinish( m_db );
6513 }
6514
6515
6516 QStringList PostgresqlConnection::query( const QString& statement, bool suppressDebug )
6517 {
6518     QStringList values;
6519     PGresult* result;
6520     ExecStatusType status;
6521
6522     result = PQexec(m_db, statement.toUtf8());
6523     if (result == NULL)
6524     {
6525         if ( !suppressDebug )
6526             debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6527         return values;
6528     }
6529
6530     status = PQresultStatus(result);
6531     if ((status != PGRES_COMMAND_OK) && (status != PGRES_TUPLES_OK))
6532     {
6533         if ( !suppressDebug )
6534             debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6535         PQclear(result);
6536         return values;
6537     }
6538
6539     int cols = PQnfields( result );
6540     int rows = PQntuples( result );
6541     QMap<int, bool> discardCols;
6542     for(int col=0; col< cols; col++) {
6543         if (QString(PQfname(result, col)) == QString("__discard") || QString(PQfname(result, col)) == QString("__random"))
6544         {
6545             discardCols[col] = true;
6546         }
6547     }
6548
6549     for(int row=0; row< rows; row++)
6550     {
6551         for(int col=0; col< cols; col++)
6552         {
6553             if (discardCols[col]) continue;
6554
6555             values << QString::fromUtf8(PQgetvalue(result, row, col));
6556         }
6557     }
6558
6559     PQclear(result);
6560
6561     return values;
6562 }
6563
6564
6565 int PostgresqlConnection::insert( const QString& statement, const QString& table )
6566 {
6567     PGresult* result;
6568     ExecStatusType status;
6569     QString curvalSql;
6570     int id;
6571
6572     result = PQexec(m_db, statement.toUtf8());
6573     if (result == NULL)
6574     {
6575         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6576         return 0;
6577     }
6578
6579     status = PQresultStatus(result);
6580     if (status != PGRES_COMMAND_OK)
6581     {
6582         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6583         PQclear(result);
6584         return 0;
6585     }
6586     PQclear(result);
6587
6588     if (table == NULL) return 0;
6589
6590     QString _table = table;
6591     if (table.find("_temp") > 0) _table = table.left(table.find("_temp"));
6592
6593     curvalSql = QString("SELECT currval('%1_seq');").arg(_table);
6594     result = PQexec(m_db, curvalSql.toUtf8());
6595     if (result == NULL)
6596     {
6597         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6598         return 0;
6599     }
6600
6601     status = PQresultStatus(result);
6602     if (status != PGRES_TUPLES_OK)
6603     {
6604         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6605         PQclear(result);
6606         return 0;
6607     }
6608
6609     if ((PQnfields( result ) != 1) || (PQntuples( result ) != 1))
6610     {
6611         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6612         PQclear(result);
6613         return 0;
6614     }
6615
6616     id = QString::fromUtf8(PQgetvalue(result, 0, 0)).toInt();
6617     PQclear(result);
6618
6619     return id;
6620 }
6621
6622
6623 void PostgresqlConnection::setPostgresqlError()
6624 {
6625     m_error = i18n("Postgresql reported the following error:<br>") + PQerrorMessage(m_db)
6626             + i18n("<p>You can configure Postgresql in the Collection section under Settings->Configure Amarok</p>");
6627 }
6628 #endif
6629
6630
6631
6632 //////////////////////////////////////////////////////////////////////////////////////////
6633 // CLASS SqliteConfig
6634 //////////////////////////////////////////////////////////////////////////////////////////
6635
6636 SqliteConfig::SqliteConfig( const QString& dbfile )
6637     : m_dbfile( dbfile )
6638 {}
6639
6640
6641 //////////////////////////////////////////////////////////////////////////////////////////
6642 // CLASS MySqlConfig
6643 //////////////////////////////////////////////////////////////////////////////////////////
6644
6645 MySqlConfig::MySqlConfig(
6646     const QString& host,
6647     const int port,
6648     const QString& database,
6649     const QString& username,
6650     const QString& password )
6651     : m_host( host ),
6652       m_port( port ),
6653       m_database( database ),
6654       m_username( username ),
6655       m_password( password )
6656 {}
6657
6658
6659 //////////////////////////////////////////////////////////////////////////////////////////
6660 // CLASS PostgresqlConfig
6661 //////////////////////////////////////////////////////////////////////////////////////////
6662
6663 PostgresqlConfig::PostgresqlConfig(
6664     const QString& host,
6665     const int port,
6666     const QString& database,
6667     const QString& username,
6668     const QString& password )
6669     : m_host( host ),
6670       m_port( port ),
6671       m_database( database ),
6672       m_username( username ),
6673       m_password( password )
6674 {}
6675
6676 #include "collectiondb.moc"