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