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