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
  32 #include "podcastbundle.h"        //addPodcast
  33 #include "qstringx.h"
  34 #include "querybuilder.h"
  35 //Added by qt3to4:
  36 #include <QTimerEvent>
  37 #include <QByteArray>
  38 #include <QLabel>
  39 #include <Q3ValueList>
  40 #include <QPixmap>
  41
  42 #include "scriptmanager.h"
  43 #include "scrobbler.h"
  44 #include "ContextStatusBar.h"
  45 #include "threadmanager.h"
  46
  47 //queries moved to sqlcollection, CollectionDB is still being used by legacy stuff
  48 #include "collection/CollectionManager.h"
  49 #include "SqlStorage.h"
  50
  51 #include <QBitmap>
  52 #include <QBuffer>
  53 #include <QCheckBox>
  54 #include <QEvent>
  55 #include <QEventLoop>
  56 #include <QFile>
  57 #include <QMap>
  58 #include <QMutex>
  59 #include <QRegExp>              //setHTMLLyrics()
  60 #include <QTimer>
  61 #include <QPainter>             //createDragPixmap()
  62 #include <QPalette>
  63 #include <QIODevice>
  64 #include <QToolTip>
  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         //PORT 2.0
1669         if( /*PlaylistFile::isPlaylistFile( *it )
1670             ||*/ (*it).protocol() == "playlist" || (*it).protocol() == "smartplaylist"
1671             || (*it).protocol() == "dynamic" )
1672         {
1673             playlists++;
1674         }
1675         else if( (*it).isLocalFile() )
1676         {
1677             songs++;
1678
1679             if( covers >= maxCovers )
1680             {
1681                 correctAlbumCount = false;
1682                 continue;
1683             }
1684
1685             MetaBundle mb( *it );
1686
1687             song = mb.title();
1688             album = mb.album();
1689
1690             QString artist = mb.artist();
1691             if( mb.compilation() == MetaBundle::CompilationYes )
1692                 artist = QString( "Various_AMAROK_Artists" ); // magic key for the albumMap!
1693
1694             if( !albumMap.contains( artist + album ) )
1695             {
1696                 albumMap[ artist + album ] = 1;
1697                 QString coverName = CollectionDB::instance()->albumImage( mb.artist(), album, false, coverW );
1698
1699                 if ( !coverName.endsWith( "@nocover.png" ) )
1700                     coverPm[covers++].load( coverName );
1701             }
1702         }
1703         else
1704         {
1705             MetaBundle mb( *it );
1706             if( !albumMap.contains( mb.artist() + mb.album() ) )
1707             {
1708                 albumMap[ mb.artist() + mb.album() ] = 1;
1709                 QString coverName = CollectionDB::instance()->podcastImage( mb, false, coverW );
1710
1711                 if ( covers < maxCovers && !coverName.endsWith( "@nocover.png" ) )
1712                     coverPm[covers++].load( coverName );
1713             }
1714             remoteUrls++;
1715         }
1716     }
1717
1718     // make better text...
1719     int albums = albumMap.count();
1720     QString text;
1721
1722     if( !textOverRide.isEmpty() )
1723     {
1724         text = textOverRide;
1725     }
1726     else if( ( songs && remoteUrls ) ||
1727              ( songs && playlists  ) ||
1728              ( playlists && remoteUrls ) )
1729     {
1730         text = i18np( "One item", "%1 items", songs + remoteUrls + playlists );
1731     }
1732     else if( songs > 0 )
1733     {
1734         if( correctAlbumCount ) {
1735             text = i18nc( "X songs from X albums", "%2 from %1" );
1736             text = text.arg( albums == 1 && !album.isEmpty() ? album : i18np( "one album", "%1 albums",albums ) );
1737         }
1738         else
1739             text = "%1";
1740         text = text.arg( songs == 1 && !song.isEmpty() ? song : i18np( "One song", "%1 songs", songs ) );
1741     }
1742     else if( playlists > 0 )
1743         text = i18np( "One playlist", "%1 playlists", playlists );
1744     else if ( remoteUrls > 0 )
1745         text = i18np( "One remote file", "%1 remote files", remoteUrls );
1746     else
1747         text = i18n( "Unknown item" );
1748
1749     QFont font;
1750     QFontMetrics fm( font );
1751     int fontH = fm.height() + margin;
1752     int minWidth = fm.width( text ) + margin*2; //margin either side
1753
1754     if ( covers > 0 )
1755     {
1756         // insert "..." cover as first image if appropriate
1757         if ( covers < albums )
1758         {
1759             if ( covers < maxCovers ) covers++;
1760             for ( int i = maxCovers-1; i > 0; i-- )
1761                 coverPm[i] = coverPm[i-1];
1762
1763             QImage im( KStandardDirs::locate( "data","amarok/images/more_albums.png" ) );
1764             coverPm[0] = QPixmap::fromImage( im.scaled( coverW, coverH, Qt::KeepAspectRatio, Qt::SmoothTransformation ) );
1765         }
1766
1767         pixmapH = coverPm[0].height();
1768         pixmapW = coverPm[0].width();
1769
1770         // caluclate pixmap height
1771         int dW, dH;
1772         for ( int i = 1; i < covers; i++ )
1773         {
1774             dW = coverPm[i].width() - coverPm[i-1].width() + coverSpacing;
1775             dH = coverPm[i].height() - coverPm[i-1].height() + coverSpacing;
1776             if ( dW > 0 ) pixmapW += dW;
1777             if ( dH > 0 ) pixmapH += dH;
1778         }
1779         pixmapH += fontSpacing + fontH;
1780
1781         if ( pixmapW < minWidth )
1782             pixmapW = minWidth;
1783     }
1784     else
1785     {
1786         pixmapW = minWidth;
1787         pixmapH = fontH;
1788     }
1789
1790     QPixmap pmdrag( pixmapW, pixmapH );
1791     QPixmap pmtext( pixmapW, fontH );
1792
1793     QPalette palette = QToolTip::palette();
1794
1795     QPainter p;
1796     p.begin( &pmtext );
1797     p.fillRect( 0, 0, pixmapW, fontH, QBrush( Qt::black ) ); // border
1798     p.fillRect( 1, 1, pixmapW-margin, fontH-margin, palette.brush( QPalette::Normal, QColorGroup::Background ) );
1799     p.setBrush( palette.color( QPalette::Normal, QColorGroup::Text ) );
1800     p.setFont( font );
1801     p.drawText( margin, fm.ascent() + 1, text );
1802     p.end();
1803
1804     QBitmap pmtextMask(pixmapW, fontH);
1805     pmtextMask.fill( Qt::color1 );
1806
1807     // when we have found no covers, just display the text message
1808     if( !covers )
1809     {
1810         pmtext.setMask(pmtextMask);
1811         return pmtext;
1812     }
1813
1814     // compose image
1815     p.begin( &pmdrag );
1816     for ( int i = 0; i < covers; i++ )
1817         bitBlt( &pmdrag, i * coverSpacing, i * coverSpacing, &coverPm[i], 0 );
1818
1819     bitBlt( &pmdrag, 0, pixmapH - fontH, &pmtext, 0 );
1820     p.end();
1821
1822     QBitmap pmdragMask( pmdrag.size() );
1823     pmdragMask.clear();
1824     for ( int i = 0; i < covers; i++ )
1825     {
1826         QBitmap coverMask( coverPm[i].width(), coverPm[i].height() );
1827         coverMask.fill( Qt::color1 );
1828         bitBlt( &pmdragMask, i * coverSpacing, i * coverSpacing, &coverMask, 0 );
1829     }
1830     bitBlt( &pmdragMask, 0, pixmapH - fontH, &pmtextMask, 0 );
1831     pmdrag.setMask( pmdragMask );
1832
1833     return pmdrag;
1834 }
1835
1836 QImage
1837 CollectionDB::fetchImage( const KUrl& url, QString &/*tmpFile*/ )
1838 {
1839     if ( url.protocol() != "file" )
1840     {
1841         QString tmpFile;
1842         KIO::NetAccess::download( url, tmpFile, 0 ); //TODO set 0 to the window, though it probably doesn't really matter
1843         return QImage( tmpFile );
1844     }
1845     else
1846     {
1847         return QImage( url.path() );
1848     }
1849
1850 }
1851 bool
1852 CollectionDB::setAlbumImage( const QString& artist, const QString& album, const KUrl& url )
1853 {
1854     QString tmpFile;
1855     bool success = setAlbumImage( artist, album, fetchImage(url, tmpFile) );
1856     KIO::NetAccess::removeTempFile( tmpFile ); //only removes file if it was created with NetAccess
1857     return success;
1858 }
1859
1860
1861 bool
1862 CollectionDB::setAlbumImage( const QString& artist, const QString& album, QImage img, const QString& amazonUrl, const QString& asin )
1863 {
1864     //show a wait cursor for the duration
1865     Amarok::OverrideCursor keep;
1866
1867     const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
1868     const QString artist_ = isCompilation ? "" : artist;
1869
1870     // remove existing album covers
1871     removeAlbumImage( artist_, album );
1872
1873     QByteArray key = md5sum( artist_, album );
1874     newAmazonReloadDate(asin, AmarokConfig::amazonLocale(), key);
1875     // Save Amazon product page URL as embedded string, for later retreival
1876     if ( !amazonUrl.isEmpty() )
1877         img.setText( "amazon-url", 0, amazonUrl );
1878
1879     const bool b = img.save( largeCoverDir().filePath( key ), "PNG");
1880     emit coverChanged( artist_, album );
1881     return b;
1882 }
1883
1884
1885 QString
1886 CollectionDB::podcastImage( const MetaBundle &bundle, const bool withShadow, uint width )
1887 {
1888     PodcastEpisodeBundle peb;
1889     PodcastChannelBundle pcb;
1890
1891     KUrl url = bundle.url().url();
1892
1893     if( getPodcastEpisodeBundle( url, &peb ) )
1894     {
1895         url = peb.parent().url();
1896     }
1897
1898     if( getPodcastChannelBundle( url, &pcb ) )
1899     {
1900         if( pcb.imageURL().isValid() )
1901             return podcastImage( pcb.imageURL().url(), withShadow, width );
1902     }
1903
1904     return notAvailCover( withShadow, width );
1905 }
1906
1907
1908 QString
1909 CollectionDB::podcastImage( const QString &remoteURL, const bool withShadow, uint width )
1910 {
1911     // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1912     // width of 0 == full size
1913     if( width == 1 )
1914         width = AmarokConfig::coverPreviewSize();
1915
1916     QString s = findAmazonImage( "Podcast", remoteURL, width );
1917
1918     if( s.isEmpty() )
1919     {
1920         s = notAvailCover( withShadow, width );
1921
1922         const KUrl url = KUrl( remoteURL );
1923         if( url.isValid() ) //KIO crashes with invalid URLs
1924         {
1925             KIO::Job *job = KIO::storedGet( url, KIO::NoReload, KIO::HideProgressInfo );
1926             m_podcastImageJobs[job] = remoteURL;
1927             connect( job, SIGNAL( result( KIO::Job* ) ), SLOT( podcastImageResult( KIO::Job* ) ) );
1928         }
1929     }
1930
1931     if ( withShadow )
1932         s = makeShadowedImage( s );
1933
1934     return s;
1935 }
1936
1937 void
1938 CollectionDB::podcastImageResult( KIO::Job *gjob )
1939 {
1940     QString url = m_podcastImageJobs[gjob];
1941     m_podcastImageJobs.remove( gjob );
1942
1943     KIO::StoredTransferJob *job = dynamic_cast<KIO::StoredTransferJob *>( gjob );
1944     if( !job )
1945     {
1946         debug() << "connected to wrong job type";
1947         return;
1948     }
1949
1950     if( job->error() )
1951     {
1952         debug() << "job finished with error";
1953         return;
1954     }
1955
1956     if( job->isErrorPage() )
1957     {
1958         debug() << "error page";
1959         return;
1960     }
1961
1962     QImage image = QImage::fromData( job->data() );
1963     if( !image.isNull() )
1964     {
1965        if( url.isEmpty() )
1966           url = job->url().url();
1967
1968         QByteArray key = md5sum( "Podcast", url );
1969         if( image.save( largeCoverDir().filePath( key ), "PNG") )
1970            emit imageFetched( url );
1971     }
1972 }
1973
1974
1975 QString
1976 CollectionDB::albumImage( const QString &artist, const QString &album, bool withShadow, uint width, bool* embedded )
1977 {
1978     QString s;
1979     // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1980     // width of 0 == full size
1981     if( width == 1 )
1982         width = AmarokConfig::coverPreviewSize();
1983     if( embedded )
1984         *embedded = false;
1985
1986     s = findAmazonImage( artist, album, width );
1987
1988     if( s.isEmpty() )
1989         s = findAmazonImage( "", album, width ); // handle compilations
1990
1991     if( s.isEmpty() )
1992         s = findDirectoryImage( artist, album, width );
1993
1994     if( s.isEmpty() )
1995     {
1996         s = findEmbeddedImage( artist, album, width );
1997         if( embedded && !s.isEmpty() )
1998             *embedded = true;
1999     }
2000
2001     if( s.isEmpty() )
2002         s = notAvailCover( withShadow, width );
2003
2004     if ( withShadow )
2005         s = makeShadowedImage( s );
2006
2007     return s;
2008 }
2009
2010
2011 QString
2012 CollectionDB::albumImage( const uint artist_id, const uint album_id, bool withShadow, uint width, bool* embedded )
2013 {
2014     return albumImage( artistValue( artist_id ), albumValue( album_id ), withShadow, width, embedded );
2015 }
2016
2017
2018 QString
2019 CollectionDB::albumImage( const MetaBundle &trackInformation, bool withShadow, uint width, bool* embedded )
2020 {
2021     QString s;
2022     if( width == 1 )
2023         width = AmarokConfig::coverPreviewSize();
2024
2025     QString album = trackInformation.album();
2026     QString artist = trackInformation.artist();
2027
2028     // this art is per track, so should check for it first
2029     s = findMetaBundleImage( trackInformation, width );
2030     if( embedded )
2031         *embedded = !s.isEmpty();
2032
2033     if( s.isEmpty() )
2034         s = findAmazonImage( artist, album, width );
2035     if( s.isEmpty() )
2036         s = findAmazonImage( "", album, width ); // handle compilations
2037     if( s.isEmpty() )
2038         s = findDirectoryImage( artist, album, width );
2039     if( s.isEmpty() )
2040         s = notAvailCover( withShadow, width );
2041     if ( withShadow )
2042         s = makeShadowedImage( s );
2043     return s;
2044 }
2045
2046
2047 QString
2048 CollectionDB::makeShadowedImage( const QString& albumImage, bool cache )
2049 {
2050     const QImage original( albumImage );
2051
2052     if( original.hasAlphaChannel() )
2053         return albumImage;
2054
2055     const QFileInfo fileInfo( albumImage );
2056     const uint shadowSize = static_cast<uint>( original.width() / 100.0 * 6.0 );
2057     const QString cacheFile = fileInfo.fileName() + "@shadow";
2058     QImage shadow;
2059
2060     if ( !cache && cacheCoverDir().exists( cacheFile ) )
2061         return cacheCoverDir().filePath( cacheFile );
2062
2063     const QString folder = Amarok::saveLocation( "covershadow-cache/" );
2064     const QString file = QString( "shadow_albumcover%1x%2.png" ).arg( original.width() + shadowSize ).arg( original.height() + shadowSize  );
2065     if ( QFile::exists( folder + file ) )
2066         shadow.load( folder + file );
2067     else {
2068         shadow.load( KStandardDirs::locate( "data", "amarok/images/shadow_albumcover.png" ) );
2069         shadow = shadow.scaled( original.width() + shadowSize, original.height() + shadowSize, Qt::IgnoreAspectRatio, Qt::SmoothTransformation );
2070         shadow.save( folder + file, "PNG" );
2071     }
2072
2073     QImage target( shadow );
2074     bitBlt( &target, 0, 0, &original );
2075
2076     if ( cache ) {
2077         target.save( cacheCoverDir().filePath( cacheFile ), "PNG" );
2078         return cacheCoverDir().filePath( cacheFile );
2079     }
2080
2081     target.save( albumImage, "PNG" );
2082     return albumImage;
2083 }
2084
2085
2086 // Amazon Image
2087 QString
2088 CollectionDB::findAmazonImage( const QString &artist, const QString &album, uint width )
2089 {
2090     QByteArray widthKey = makeWidthKey( width );
2091
2092     if ( artist.isEmpty() && album.isEmpty() )
2093         return QString();
2094
2095     QByteArray key = md5sum( artist, album );
2096
2097     // check cache for existing cover
2098     if ( cacheCoverDir().exists( widthKey + key ) )
2099         return cacheCoverDir().filePath( widthKey + key );
2100
2101     // we need to create a scaled version of this cover
2102     QDir imageDir = largeCoverDir();
2103     if ( imageDir.exists( key ) )
2104     {
2105         if ( width > 1 )
2106         {
2107             QImage img( imageDir.filePath( key ) );
2108             img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + key ), "PNG" );
2109
2110             return cacheCoverDir().filePath( widthKey + key );
2111         }
2112         else
2113             return imageDir.filePath( key );
2114     }
2115
2116     return QString();
2117 }
2118
2119
2120 QString
2121 CollectionDB::findDirectoryImage( const QString& artist, const QString& album, uint width )
2122 {
2123     if ( width == 1 )
2124         width = AmarokConfig::coverPreviewSize();
2125     QByteArray widthKey = makeWidthKey( width );
2126     if ( album.isEmpty() )
2127         return QString();
2128
2129     IdList list = MountPointManager::instance()->getMountedDeviceIds();
2130     QString deviceIds;
2131     oldForeachType( IdList, list )
2132     {
2133         if ( !deviceIds.isEmpty() ) deviceIds = deviceIds + ",";
2134         deviceIds += QString::number(*it);
2135     }
2136
2137     QStringList rs;
2138     if ( artist == i18n( "Various Artists" ) || artist.isEmpty() )
2139     {
2140          rs = query( QString(
2141             "SELECT images.deviceid,images.path FROM images, artist, tags "
2142             "WHERE images.artist = artist.name "
2143             "AND artist.id = tags.artist "
2144             "AND tags.sampler = %1 "
2145             "AND images.album %2 "
2146             "AND images.deviceid IN (%3) " )
2147                  .arg( boolT() )
2148                  .arg( CollectionDB::likeCondition( album ) )
2149                  .arg( deviceIds ) );
2150     }
2151     else
2152     {
2153         rs = query( QString(
2154             "SELECT images.deviceid,images.path FROM images WHERE artist %1 AND album %2 AND deviceid IN (%3) ORDER BY path;" )
2155                     .arg( CollectionDB::likeCondition( artist ) )
2156                     .arg( CollectionDB::likeCondition( album ) )
2157                     .arg( deviceIds ) );
2158     }
2159     QStringList values = URLsFromQuery( rs );
2160     if ( !values.isEmpty() )
2161     {
2162         QString image( values.first() );
2163         uint matches = 0;
2164         uint maxmatches = 0;
2165         QRegExp iTunesArt( "^AlbumArt_.*Large" );
2166         for ( int i = 0; i < values.count(); i++ )
2167         {
2168             matches = values[i].count( "front", Qt::CaseInsensitive ) + values[i].count( "cover", Qt::CaseInsensitive ) + values[i].count( "folder", Qt::CaseInsensitive ) + values[i].count( iTunesArt );
2169             if ( matches > maxmatches )
2170             {
2171                 image = values[i];
2172                 maxmatches = matches;
2173             }
2174         }
2175
2176         QByteArray key = md5sum( artist, album, image );
2177
2178         if ( width > 1 )
2179         {
2180             QString path = cacheCoverDir().filePath( widthKey + key );
2181             if ( !QFile::exists( path ) )
2182             {
2183                 QImage img( image );
2184                 img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" );
2185             }
2186             return path;
2187         }
2188         else //large image
2189             return image;
2190     }
2191     return QString();
2192 }
2193
2194
2195 QString
2196 CollectionDB::findEmbeddedImage( const QString& artist, const QString& album, uint width )
2197 {
2198     // In the case of multiple embedded images, we arbitrarily choose one from the newest file
2199     // could potentially select multiple images within a file based on description, although a
2200     // lot of tagging software doesn't fill in that field, so we just get whatever the DB
2201     // happens to return for us
2202     QStringList rs;
2203     if ( artist == i18n("Various Artists") || artist.isEmpty() ) {
2204         // VAs need special handling to not match on artist name but instead check for sampler flag
2205         rs = query( QString(
2206                 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2207                 "tags INNER JOIN embed ON tags.url = embed.url "
2208                      "INNER JOIN album ON tags.album = album.id "
2209                 "WHERE "
2210                 "album.name = '%1' "
2211                 "AND tags.sampler = %2 "
2212                 "ORDER BY modifydate DESC LIMIT 1;" )
2213                 .arg( escapeString( album ) )
2214                 .arg( boolT() ) );
2215     } else {
2216         rs = query( QString(
2217                 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2218                 "tags INNER JOIN embed ON tags.url = embed.url "
2219                      "INNER JOIN artist ON tags.artist = artist.id "
2220                      "INNER JOIN album ON tags.album = album.id "
2221                 "WHERE  "
2222                 "artist.name = '%1' "
2223                 "AND album.name = '%2' "
2224                 "ORDER BY modifydate DESC LIMIT 1;" )
2225                 .arg( escapeString( artist ) )
2226                 .arg( escapeString( album ) ) );
2227     }
2228
2229     QStringList values = QStringList();
2230     if ( rs.count() == 3 ) {
2231         values += rs.first();
2232         values += MountPointManager::instance()->getAbsolutePath( rs[1].toInt(), rs[2] );
2233     }
2234
2235     if ( values.count() == 2 ) {
2236         QByteArray hash = values.first().toUtf8();
2237         QString result = loadHashFile( hash, width );
2238         if ( result.isEmpty() ) {
2239             // need to get original from file first
2240             MetaBundle mb(  KUrl( values.last() ) );
2241             if ( extractEmbeddedImage( mb, hash ) ) {
2242                 // try again, as should be possible now
2243                 result = loadHashFile( hash, width );
2244             }
2245         }
2246         return result;
2247     }
2248     return QString();
2249 }
2250
2251
2252 QString
2253 CollectionDB::findMetaBundleImage( const MetaBundle& trackInformation, uint width )
2254 {
2255     int deviceid = MountPointManager::instance()->getIdForUrl( trackInformation.url() );
2256     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, trackInformation.url().path() );
2257     QStringList values =
2258             query( QString(
2259             "SELECT embed.hash FROM tags LEFT JOIN embed ON tags.url = embed.url "
2260             " AND tags.deviceid = embed.deviceid WHERE tags.url = '%2' AND tags.deviceid = %1 ORDER BY hash DESC LIMIT 1;" )
2261             .arg( deviceid ).arg( escapeString( rpath ) ) );
2262
2263     if ( values.empty() || !values.first().isEmpty() ) {
2264         QByteArray hash;
2265         QString result;
2266         if( !values.empty() ) { // file in collection, so we know the hash
2267             hash = values.first().toUtf8();
2268             result = loadHashFile( hash, width );
2269         }
2270         if ( result.isEmpty() ) {
2271             // need to get original from file first
2272             if ( extractEmbeddedImage( trackInformation, hash ) ) {
2273                 // try again, as should be possible now
2274                 result = loadHashFile( hash, width );
2275             }
2276         }
2277         return result;
2278     }
2279     return QString();
2280 }
2281
2282
2283 QByteArray
2284 CollectionDB::makeWidthKey( uint width )
2285 {
2286     return QString::number( width ).toLocal8Bit() + '@';
2287 }
2288
2289
2290 bool
2291 CollectionDB::removeAlbumImage( const QString &artist, const QString &album )
2292 {
2293     DEBUG_BLOCK
2294
2295     QByteArray widthKey = "*@";
2296     QByteArray key = md5sum( artist, album );
2297     query( "DELETE FROM amazon WHERE filename='" + key + '\'' );
2298
2299     // remove scaled versions of images (and add the asterisk for the shadow-caches)
2300     QStringList filter;
2301     filter << widthKey + key + '*';
2302     QStringList scaledList = cacheCoverDir().entryList( filter );
2303     if ( scaledList.count() > 0 )
2304         for ( int i = 0; i < scaledList.count(); i++ )
2305             QFile::remove( cacheCoverDir().filePath( scaledList[ i ] ) );
2306
2307     bool deleted = false;
2308     // remove large, original image
2309     if ( largeCoverDir().exists( key ) && QFile::remove( largeCoverDir().filePath( key ) ) )
2310         deleted = true;
2311
2312     QString hardImage = findDirectoryImage( artist, album );
2313     debug() << "hardImage: " << hardImage;
2314
2315     if( !hardImage.isEmpty() )
2316     {
2317         int id = MountPointManager::instance()->getIdForUrl( hardImage );
2318         QString rpath = MountPointManager::instance()->getRelativePath( id, hardImage );
2319         query( "DELETE FROM images WHERE path='" + escapeString( hardImage ) + "' AND deviceid = " + QString::number( id ) + ';' );
2320         deleted = true;
2321     }
2322
2323     if ( deleted )
2324     {
2325         emit coverRemoved( artist, album );
2326         return true;
2327     }
2328
2329     return false;
2330 }
2331
2332
2333 bool
2334 CollectionDB::removeAlbumImage( const uint artist_id, const uint album_id )
2335 {
2336     return removeAlbumImage( artistValue( artist_id ), albumValue( album_id ) );
2337 }
2338
2339
2340 QString
2341 CollectionDB::notAvailCover( const bool withShadow, int width )
2342 {
2343     if ( width <= 1 )
2344         width = AmarokConfig::coverPreviewSize();
2345     QString widthKey = QString::number( width ) + '@';
2346     QString s;
2347
2348     if( cacheCoverDir().exists( widthKey + "nocover.png" ) )
2349         s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2350     else
2351     {
2352         m_noCover.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + "nocover.png" ), "PNG" );
2353         s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2354     }
2355
2356     if ( withShadow )
2357         s = makeShadowedImage( s );
2358
2359     return s;
2360 }
2361
2362
2363 QStringList
2364 CollectionDB::artistList( bool withUnknowns, bool withCompilations )
2365 {
2366     QueryBuilder qb;
2367     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
2368
2369     if ( !withUnknowns )
2370         qb.excludeMatch( QueryBuilder::tabArtist, i18n( "Unknown" ) );
2371     if ( !withCompilations )
2372         qb.setOptions( QueryBuilder::optNoCompilations );
2373
2374     qb.groupBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2375     qb.setOptions( QueryBuilder::optShowAll );
2376     qb.sortBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2377     return qb.run();
2378 }
2379
2380
2381 QStringList
2382 CollectionDB::composerList( bool withUnknowns, bool withCompilations )
2383 {
2384     DEBUG_BLOCK
2385     QueryBuilder qb;
2386     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
2387
2388     if ( !withUnknowns )
2389         qb.excludeMatch( QueryBuilder::tabComposer, i18n( "Unknown" ) );
2390     if ( !withCompilations )
2391         qb.setOptions( QueryBuilder::optNoCompilations );
2392
2393     qb.groupBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2394     qb.setOptions( QueryBuilder::optShowAll );
2395     qb.sortBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2396     return qb.run();
2397 }
2398
2399
2400 QStringList
2401 CollectionDB::albumList( bool withUnknowns, bool withCompilations )
2402 {
2403     QueryBuilder qb;
2404     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
2405
2406     if ( !withUnknowns )
2407         qb.excludeMatch( QueryBuilder::tabAlbum, i18n( "Unknown" ) );
2408     if ( !withCompilations )
2409         qb.setOptions( QueryBuilder::optNoCompilations );
2410
2411     qb.groupBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2412     qb.setOptions( QueryBuilder::optShowAll );
2413     qb.sortBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2414     return qb.run();
2415 }
2416
2417
2418 QStringList
2419 CollectionDB::genreList( bool withUnknowns, bool withCompilations )
2420 {
2421     QueryBuilder qb;
2422     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
2423
2424     //Only report genres that currently have at least one song
2425     qb.addFilter( QueryBuilder::tabSong, "" );
2426
2427     if ( !withUnknowns )
2428         qb.excludeMatch( QueryBuilder::tabGenre, i18n( "Unknown" ) );
2429     if ( !withCompilations )
2430         qb.setOptions( QueryBuilder::optNoCompilations );
2431
2432     qb.groupBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2433     qb.setOptions( QueryBuilder::optShowAll );
2434     qb.sortBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2435     return qb.run();
2436 }
2437
2438
2439 QStringList
2440 CollectionDB::yearList( bool withUnknowns, bool withCompilations )
2441 {
2442     QueryBuilder qb;
2443     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
2444
2445     if ( !withUnknowns )
2446         qb.excludeMatch( QueryBuilder::tabYear, i18n( "Unknown" ) );
2447     if ( !withCompilations )
2448         qb.setOptions( QueryBuilder::optNoCompilations );
2449
2450     qb.groupBy( QueryBuilder::tabYear, QueryBuilder::valName );
2451     qb.setOptions( QueryBuilder::optShowAll );
2452     qb.sortBy( QueryBuilder::tabYear, QueryBuilder::valName );
2453     return qb.run();
2454 }
2455
2456 QStringList
2457 CollectionDB::labelList()
2458 {
2459     QueryBuilder qb;
2460     qb.addReturnValue( QueryBuilder::tabLabels, QueryBuilder::valName );
2461     qb.groupBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2462     qb.setOptions( QueryBuilder::optShowAll );
2463     qb.sortBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2464     return qb.run();
2465 }
2466
2467 QStringList
2468 CollectionDB::albumListOfArtist( const QString &artist, bool withUnknown, bool withCompilations )
2469 {
2470     if (getDbConnectionType() == DbConnection::postgresql)
2471     {
2472         return query( "SELECT DISTINCT album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2473                       "tags.album = album.id AND tags.artist = artist.id "
2474                       "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2475                       ( withUnknown ? QString() : "AND album.name <> '' " ) +
2476                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2477                       " ORDER BY lower( album.name );" );
2478     }
2479     else
2480     {
2481         return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE "
2482                       "tags.album = album.id AND tags.artist = artist.id "
2483                       "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2484                       ( withUnknown ? QString() : "AND album.name <> '' " ) +
2485                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2486                       " ORDER BY lower( album.name );" );
2487     }
2488 }
2489
2490
2491 QStringList
2492 CollectionDB::artistAlbumList( bool withUnknown, bool withCompilations )
2493 {
2494     if (getDbConnectionType() == DbConnection::postgresql)
2495     {
2496         return query( "SELECT DISTINCT artist.name, album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2497                       "tags.album = album.id AND tags.artist = artist.id " +
2498                       ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2499                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2500                       " ORDER BY lower( album.name );" );
2501     }
2502     else
2503     {
2504         return query( "SELECT DISTINCT artist.name, album.name FROM tags, album, artist WHERE "
2505                       "tags.album = album.id AND tags.artist = artist.id " +
2506                       ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2507                       ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2508                       " ORDER BY lower( album.name );" );
2509     }
2510 }
2511
2512 bool
2513 CollectionDB::addPodcastChannel( const PodcastChannelBundle &pcb, const bool &replace )
2514 {
2515     QString command;
2516     if( replace ) {
2517         command = "REPLACE INTO podcastchannels "
2518                   "( url, title, weblink, image, comment, copyright, parent, directory"
2519                   ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2520                   "VALUES (";
2521     } else {
2522         command = "INSERT INTO podcastchannels "
2523                   "( url, title, weblink, image, comment, copyright, parent, directory"
2524                   ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2525                   "VALUES (";
2526     }
2527
2528     QString title       = pcb.title();
2529     KUrl    link        = pcb.link();
2530     KUrl    image       = pcb.imageURL();
2531     QString description = pcb.description();
2532     QString copyright   = pcb.copyright();
2533
2534     if( title.isEmpty() )
2535         title = pcb.url().prettyUrl();
2536
2537     command += '\'' + escapeString( pcb.url().url() )  + "',";
2538     command += ( title.isEmpty() ?       "NULL" : '\'' + escapeString( title ) + '\'' ) + ',';
2539     command += ( link.isEmpty() ?        "NULL" : '\'' + escapeString( link.url() ) + '\'' ) + ',';
2540     command += ( image.isEmpty() ?       "NULL" : '\'' + escapeString( image.url() ) + '\'' ) + ',';
2541     command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2542     command += ( copyright.isEmpty() ?   "NULL" : '\'' + escapeString( copyright ) + '\'' ) + ',';
2543     command += QString::number( pcb.parentId() ) + ",'";
2544     command += escapeString( pcb.saveLocation() ) + "',";
2545     command += pcb.autoscan() ? boolT() + ',' : boolF() + ',';
2546     command += QString::number( pcb.fetchType() ) + ',';
2547     command += pcb.autotransfer() ? boolT() + ',' : boolF() + ',';
2548     command += pcb.hasPurge() ? boolT() + ',' : boolF() + ',';
2549     command += QString::number( pcb.purgeCount() ) + ");";
2550
2551     //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2552     // Now it might be possible as insert returns the rowid.
2553     insert( command, NULL );
2554     return true;
2555 }
2556
2557 int
2558 CollectionDB::addPodcastEpisode( const PodcastEpisodeBundle &episode, const int idToUpdate )
2559 {
2560     QString command;
2561
2562     if( idToUpdate ) {
2563         command = "REPLACE INTO podcastepisodes "
2564                   "( id, url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2565                   "VALUES (";
2566     } else {
2567         command = "INSERT INTO podcastepisodes "
2568                   "( url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2569                   "VALUES (";
2570     }
2571
2572     QString localurl    = episode.localUrl().url();
2573     QString title       = episode.title();
2574     QString subtitle    = episode.subtitle();
2575     QString author      = episode.author();
2576     QString description = episode.description();
2577     QString type        = episode.type();
2578     QString date        = episode.date();
2579     QString guid        = episode.guid();
2580     int     duration    = episode.duration();
2581     uint    size        = episode.size();
2582
2583     if( title.isEmpty() )
2584         title = episode.url().prettyUrl();
2585
2586     if( idToUpdate )
2587         command += QString::number( idToUpdate ) + ',';
2588
2589     command += '\'' + escapeString( episode.url().url() )   + "',";
2590     command += ( localurl.isEmpty()       ? "NULL" : '\'' + escapeString( localurl )       + '\'' ) + ',';
2591     command += '\'' + escapeString( episode.parent().url()) + "',";
2592     command += ( title.isEmpty()       ? "NULL" : '\'' + escapeString( title )       + '\'' ) + ',';
2593     command += ( subtitle.isEmpty()    ? "NULL" : '\'' + escapeString( subtitle )    + '\'' ) + ',';
2594     command += ( author.isEmpty()      ? "NULL" : '\'' + escapeString( author )      + '\'' ) + ',';
2595     command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2596     command += ( type.isEmpty()        ? "NULL" : '\'' + escapeString( type )        + '\'' ) + ',';
2597     command += ( date.isEmpty()        ? "NULL" : '\'' + escapeString( date )        + '\'' ) + ',';
2598     command += ( guid.isEmpty()        ? "NULL" : '\'' + escapeString( guid )        + '\'' ) + ',';
2599     command += QString::number( duration ) + ',';
2600     command += QString::number( size ) + ',';
2601     command += episode.isNew() ? boolT() + " );" : boolF() + " );";
2602
2603     insert( command, NULL );
2604
2605     if( idToUpdate ) return idToUpdate;
2606     //This is a bit of a hack. We have just inserted an item, so it is going to be the one with the
2607     //highest id.  Change this if threaded insertions are used in the future.
2608     QStringList values = query( QString("SELECT id FROM podcastepisodes WHERE url='%1' ORDER BY id DESC;")
2609                                         .arg( escapeString( episode.url().url() ) ) );
2610     if( values.isEmpty() ) return -1;
2611
2612     return values[0].toInt();
2613 }
2614
2615 Q3ValueList<PodcastChannelBundle>
2616 CollectionDB::getPodcastChannels()
2617 {
2618     QString command = "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2619         ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels;";
2620
2621     QStringList values = query( command );
2622     Q3ValueList<PodcastChannelBundle> bundles;
2623
2624     oldForeach( values )
2625     {
2626         PodcastChannelBundle pcb;
2627         pcb.setUrl         ( KUrl(*it) );
2628         pcb.setTitle       ( *++it );
2629         pcb.setLink        ( KUrl(*++it) );
2630         pcb.setImageURL    ( KUrl(*++it) );
2631         pcb.setDescription ( *++it );
2632         pcb.setCopyright   ( *++it );
2633         pcb.setParentId    ( (*++it).toInt() );
2634         pcb.setSaveLocation( *++it );
2635         pcb.setAutoScan    ( *++it == boolT() ? true : false );
2636         pcb.setFetchType   ( (*++it).toInt() );
2637         pcb.setAutoTransfer( *++it == boolT() ? true : false  );
2638         pcb.setPurge       ( *++it == boolT() ? true : false  );
2639         pcb.setPurgeCount  ( (*++it).toInt() );
2640
2641         bundles.append( pcb );
2642     }
2643
2644     return bundles;
2645 }
2646
2647 Q3ValueList<PodcastEpisodeBundle>
2648 CollectionDB::getPodcastEpisodes( const KUrl &parent, bool onlyNew, int limit )
2649 {
2650     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() );
2651     if( onlyNew )
2652         command += QString( " AND isNew='%1'" ).arg( boolT() );
2653     command += " ) ORDER BY id";
2654     if( limit != -1 )
2655         command += QString( " DESC LIMIT %1 OFFSET 0" ).arg( limit );
2656     command += ';';
2657
2658     QStringList values = query( command );
2659     Q3ValueList<PodcastEpisodeBundle> bundles;
2660
2661     oldForeach( values )
2662     {
2663         PodcastEpisodeBundle peb;
2664         peb.setDBId        ( (*it).toInt() );
2665         peb.setUrl         ( KUrl(*++it) );
2666         if( *++it != "NULL" )
2667             peb.setLocalURL    ( KUrl(*it) );
2668         peb.setParent      ( KUrl(*++it) );
2669         peb.setGuid        ( *++it );
2670         peb.setTitle       ( *++it );
2671         if( *++it != NULL )
2672             peb.setSubtitle( *it );
2673         peb.setAuthor      ( *++it );
2674         peb.setDescription ( *++it );
2675         peb.setType        ( *++it );
2676         peb.setDate        ( *++it );
2677         peb.setDuration    ( (*++it).toInt() );
2678         if( *++it == NULL )
2679             peb.setSize        ( 0 );
2680         else
2681             peb.setSize        ( (*it).toInt() );
2682         peb.setNew         ( (*++it) == boolT() ? true : false  );
2683
2684         bundles.append( peb );
2685     }
2686
2687     return bundles;
2688 }
2689
2690 PodcastEpisodeBundle
2691 CollectionDB::getPodcastEpisodeById( int id )
2692 {
2693     QString command = QString( "SELECT url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE id=%1;").arg( id );
2694
2695     QStringList values = query( command );
2696     PodcastEpisodeBundle peb;
2697     oldForeach( values )
2698     {
2699         peb.setDBId        ( id );
2700         peb.setUrl         ( KUrl(*it) );
2701         if( *++it != "NULL" )
2702             peb.setLocalURL( KUrl(*it) );
2703         peb.setParent      ( KUrl(*++it) );
2704         peb.setGuid        ( *++it );
2705         peb.setTitle       ( *++it );
2706         peb.setSubtitle    ( *++it );
2707         peb.setAuthor      ( *++it );
2708         peb.setDescription ( *++it );
2709         peb.setType        ( *++it );
2710         peb.setDate        ( *++it );
2711         peb.setDuration    ( (*++it).toInt() );
2712         if( *++it == NULL )
2713             peb.setSize    ( 0 );
2714         else
2715             peb.setSize    ( (*it).toInt() );
2716         peb.setNew         ( (*++it) == boolT() ? true : false  );
2717     }
2718
2719     return peb;
2720 }
2721
2722 bool
2723 CollectionDB::getPodcastEpisodeBundle( const KUrl &url, PodcastEpisodeBundle *peb )
2724 {
2725     int id = 0;
2726     if( url.isLocalFile() )
2727     {
2728         QStringList values =
2729             query( QString( "SELECT id FROM podcastepisodes WHERE localurl = '%1';" )
2730                     .arg( escapeString( url.url() ) ) );
2731         if( !values.isEmpty() )
2732             id = values[0].toInt();
2733     }
2734     else
2735     {
2736         QStringList values =
2737             query( QString( "SELECT id FROM podcastepisodes WHERE url = '%1';" )
2738                     .arg( escapeString( url.url() ) ) );
2739         if( !values.isEmpty() )
2740             id = values[0].toInt();
2741     }
2742
2743     if( id )
2744     {
2745         *peb = getPodcastEpisodeById( id );
2746         return true;
2747     }
2748
2749     return false;
2750 }
2751
2752 bool
2753 CollectionDB::getPodcastChannelBundle( const KUrl &url, PodcastChannelBundle *pcb )
2754 {
2755     QStringList values = query( QString(
2756                 "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2757                 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels WHERE url = '%1';"
2758                 ).arg( escapeString( url.url() ) ) );
2759
2760     oldForeach( values )
2761     {
2762         pcb->setUrl         ( KUrl(*it) );
2763         pcb->setTitle       ( *++it );
2764         pcb->setLink        ( KUrl(*++it) );
2765         if( *++it != "NULL" )
2766             pcb->setImageURL( KUrl(*it) );
2767         pcb->setDescription ( *++it );
2768         pcb->setCopyright   ( *++it );
2769         pcb->setParentId    ( (*++it).toInt() );
2770         pcb->setSaveLocation( *++it );
2771         pcb->setAutoScan    ( *++it == boolT() ? true : false );
2772         pcb->setFetchType   ( (*++it).toInt() );
2773         pcb->setAutoTransfer( *++it == boolT() ? true : false  );
2774         pcb->setPurge       ( *++it == boolT() ? true : false  );
2775         pcb->setPurgeCount  ( (*++it).toInt() );
2776     }
2777
2778     return !values.isEmpty();
2779 }
2780
2781 // return newly created folder id
2782 int
2783 CollectionDB::addPodcastFolder( const QString &name, const int parent_id, const bool isOpen )
2784 {
2785     QString command = QString( "INSERT INTO podcastfolders ( name, parent, isOpen ) VALUES ('" );
2786     command += escapeString( name )   + "',";
2787     command += QString::number( parent_id ) + ',';
2788     command += isOpen ? boolT() + ");" : boolF() + ");";
2789
2790     insert( command, NULL );
2791
2792     command = QString( "SELECT id FROM podcastfolders WHERE name = '%1' AND parent = '%2';" )
2793                        .arg( name, QString::number(parent_id) );
2794     QStringList values = query( command );
2795
2796     return values[0].toInt();
2797 }
2798
2799 void
2800 CollectionDB::updatePodcastChannel( const PodcastChannelBundle &b )
2801 {
2802     if( getDbConnectionType() == DbConnection::postgresql )
2803     {
2804         query( QStringx( "UPDATE podcastchannels SET title='%1', weblink='%2', comment='%3', "
2805                          "copyright='%4', parent=%5, directory='%6', autoscan=%7, fetchtype=%8, "
2806              "autotransfer=%9, haspurge=%10, purgecount=%11 WHERE url='%12';" )
2807            .args ( QStringList()
2808                << escapeString( b.title() )
2809                << escapeString( b.link().url() )
2810                << escapeString( b.description() )
2811                << escapeString( b.copyright() )
2812                << QString::number( b.parentId() )
2813                << escapeString( b.saveLocation() )
2814                << ( b.autoscan() ? boolT() : boolF() )
2815                << QString::number( b.fetchType() )
2816                << (b.hasPurge() ? boolT() : boolF() )
2817                << (b.autotransfer() ? boolT() : boolF() )
2818                << QString::number( b.purgeCount() )
2819                << escapeString( b.url().url() )
2820            )
2821         );
2822     }
2823     else {
2824         addPodcastChannel( b, true ); //replace the already existing row
2825     }
2826 }
2827
2828 void
2829 CollectionDB::updatePodcastEpisode( const int id, const PodcastEpisodeBundle &b )
2830 {
2831     if( getDbConnectionType() == DbConnection::postgresql )
2832     {
2833         query( QStringx( "UPDATE podcastepisodes SET url='%1', localurl='%2', parent='%3', title='%4', subtitle='%5', composer='%6', comment='%7', "
2834                  "filetype='%8', createdate='%9', guid='%10', length=%11, size=%12, isNew=%13 WHERE id=%14;" )
2835               .args( QStringList()
2836                   << escapeString( b.url().url() )
2837                   << ( b.localUrl().isValid() ? escapeString( b.localUrl().url() ) : "NULL" )
2838                   << escapeString( b.parent().url() )
2839                   << escapeString( b.title() )
2840                   << escapeString( b.subtitle() )
2841                   << escapeString( b.author() )
2842                   << escapeString( b.description() )
2843                   << escapeString( b.type() )
2844                   << escapeString( b.date() )
2845                   << escapeString( b.guid() )
2846                   << QString::number( b.duration() )
2847                   << escapeString( QString::number( b.size() ) )
2848                   << ( b.isNew() ? boolT() : boolF() )
2849                   << QString::number( id )
2850                   )
2851              );
2852     }
2853     else {
2854         addPodcastEpisode( b, id );
2855     }
2856 }
2857
2858 void
2859 CollectionDB::updatePodcastFolder( const int folder_id, const QString &name, const int parent_id, const bool isOpen )
2860 {
2861     if( getDbConnectionType() == DbConnection::postgresql ) {
2862         query( QStringx( "UPDATE podcastfolders SET name='%1', parent=%2, isOpen=%3 WHERE id=%4;" )
2863                 .args( QStringList()
2864                     << escapeString(name)
2865                     << QString::number(parent_id)
2866                     << ( isOpen ? boolT() : boolF() )
2867                     << QString::number(folder_id)
2868                     )
2869              );
2870     }
2871     else {
2872         query( QStringx( "REPLACE INTO podcastfolders ( id, name, parent, isOpen ) "
2873                         "VALUES ( %1, '%2', %3, %4 );" )
2874                 .args( QStringList()
2875                     << QString::number(folder_id)
2876                     << escapeString(name)
2877                     << QString::number(parent_id)
2878                     << ( isOpen ? boolT() : boolF() )
2879                     )
2880              );
2881     }
2882 }
2883
2884 void
2885 CollectionDB::removePodcastChannel( const KUrl &url )
2886 {
2887     //remove channel
2888     query( QString( "DELETE FROM podcastchannels WHERE url = '%1';" )
2889               .arg( escapeString( url.url() ) ) );
2890     //remove all children
2891     query( QString( "DELETE FROM podcastepisodes WHERE parent = '%1';" )
2892               .arg( escapeString( url.url() ) ) );
2893 }
2894
2895
2896 /// Try not to delete by url, since some podcast feeds have all the same url
2897 void
2898 CollectionDB::removePodcastEpisode( const int id )
2899 {
2900     if( id < 0 ) return;
2901     query( QString( "DELETE FROM podcastepisodes WHERE id = '%1';" )
2902               .arg( QString::number(id) ) );
2903 }
2904
2905 void
2906 CollectionDB::removePodcastFolder( const int id )
2907 {
2908     if( id < 0 ) return;
2909     query( QString("DELETE FROM podcastfolders WHERE id=%1;")
2910               .arg( QString::number(id) ) );
2911 }
2912
2913 bool
2914 CollectionDB::addSong( MetaBundle* bundle, const bool incremental )
2915 {
2916     if ( !QFileInfo( bundle->url().path() ).isReadable() ) return false;
2917
2918     QString command = "INSERT INTO tags_temp "
2919                       "( url, dir, deviceid, createdate, modifydate, album, artist, composer, genre, year, title, "
2920                       "comment, track, discnumber, bpm, sampler, length, bitrate, "
2921                       "samplerate, filesize, filetype ) "
2922                       "VALUES ('";
2923
2924     QString artist = bundle->artist();
2925     QString title = bundle->title();
2926     if ( title.isEmpty() )
2927     {
2928         title = bundle->url().fileName();
2929         if ( bundle->url().fileName().indexOf( '-' ) > 0 )
2930         {
2931             if ( artist.isEmpty() )
2932             {
2933                 artist = bundle->url().fileName().section( '-', 0, 0 ).trimmed();
2934                 bundle->setArtist( artist );
2935             }
2936             title = bundle->url().fileName().section( '-', 1 ).trimmed();
2937             title = title.left( title.lastIndexOf( '.' ) ).trimmed();
2938             if ( title.isEmpty() ) title = bundle->url().fileName();
2939         }
2940         bundle->setTitle( title );
2941     }
2942
2943     int deviceId = MountPointManager::instance()->getIdForUrl( bundle->url() );
2944     KUrl relativePath;
2945     MountPointManager::instance()->getRelativePath( deviceId, bundle->url(), relativePath );
2946     //debug() << "File has deviceId " << deviceId << ", relative path " << relativePath.path() << ", absolute path " << bundle->url().path();
2947
2948     command += escapeString( relativePath.path() ) + "','";
2949     command += escapeString( relativePath.directory() ) + "',";
2950     command += QString::number( deviceId ) + ',';
2951     command += QString::number( QFileInfo( bundle->url().path() ).created().toTime_t() ) + ',';
2952     command += QString::number( QFileInfo( bundle->url().path() ).lastModified().toTime_t() ) + ',';
2953
2954     command += escapeString( QString::number( albumID( bundle->album(),   true, !incremental, true ) ) ) + ',';
2955     command += escapeString( QString::number( artistID( bundle->artist(), true, !incremental, true ) ) ) + ',';
2956     command += escapeString( QString::number( composerID( bundle->composer(), true, !incremental, true ) ) ) + ',';
2957     command += escapeString( QString::number( genreID( bundle->genre(),   true, !incremental, true ) ) ) + ",'";
2958     command += escapeString( QString::number( yearID( QString::number( bundle->year() ), true, !incremental, true ) ) ) + "','";
2959
2960     command += escapeString( bundle->title() ) + "','";
2961     command += escapeString( bundle->comment() ) + "', ";
2962     command += escapeString( QString::number( bundle->track() ) ) + " , ";
2963     command += escapeString( QString::number( bundle->discNumber() ) ) + " , ";
2964     command += escapeString( QString::number( bundle->bpm() ) ) + " , ";
2965     switch( bundle->compilation() ) {
2966         case MetaBundle::CompilationNo:
2967             command += boolF();
2968             break;
2969
2970         case MetaBundle::CompilationYes:
2971             command += boolT();
2972             break;
2973
2974         case MetaBundle::CompilationUnknown:
2975         default:
2976             command += "NULL";
2977     }
2978     command += ',';
2979
2980     // NOTE any of these may be -1 or -2, this is what we want
2981     //      see MetaBundle::Undetermined
2982     command += QString::number( bundle->length() ) + ',';
2983     command += QString::number( bundle->bitrate() ) + ',';
2984     command += QString::number( bundle->sampleRate() ) + ',';
2985     command += QString::number( bundle->filesize() ) + ',';
2986     command += QString::number( bundle->fileType() ) + ')';
2987
2988     //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2989     // Now it might be possible as insert returns the rowid.
2990     insert( command, NULL );
2991
2992     doAFTStuff( bundle, true );
2993
2994     return true;
2995 }
2996
2997 void
2998 CollectionDB::doAFTStuff( MetaBundle* bundle, const bool tempTables )
2999 {
3000     if( bundle->uniqueId().isEmpty() || bundle->url().path().isEmpty() )
3001         return;
3002
3003     MountPointManager *mpm = MountPointManager::instance();
3004     //const to make sure one isn't later modified without the other being changed
3005     const int deviceIdInt = mpm->getIdForUrl( bundle->url().path() );
3006     const QString currdeviceid = QString::number( deviceIdInt );
3007     QString currid = escapeString( bundle->uniqueId() );
3008     QString currurl = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().path() ) );
3009     QString currdir = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().directory() ) );
3010     //debug() << "Checking currid = " << currid << ", currdir = " << currdir << ", currurl = " << currurl;
3011     //debug() << "tempTables = " << (tempTables?"true":"false");
3012
3013
3014     QStringList urls = query( QString(
3015             "SELECT url, uniqueid "
3016             "FROM uniqueid%1 "
3017             "WHERE deviceid = %2 AND url = '%3';" )
3018                 .arg( tempTables ? "_temp" : ""
3019                  , currdeviceid
3020                  , currurl ) );
3021
3022     QStringList uniqueids = query( QString(
3023             "SELECT url, uniqueid, deviceid "
3024             "FROM uniqueid%1 "
3025             "WHERE uniqueid = '%2';" )
3026                 .arg( tempTables ? "_temp" : ""
3027                 , currid ) );
3028
3029     QStringList nonTempIDs = query( QString(
3030             "SELECT url, uniqueid, deviceid "
3031             "FROM uniqueid "
3032             "WHERE uniqueid = '%1';" )
3033                 .arg( currid ) );
3034
3035     QStringList nonTempURLs = query( QString(
3036             "SELECT url, uniqueid "
3037             "FROM uniqueid "
3038             "WHERE deviceid = %1 AND url = '%2';" )
3039                 .arg( currdeviceid
3040                 , currurl ) );
3041
3042     bool tempTablesAndInPermanent = false;
3043     bool permanentFullMatch = false;
3044
3045     //if we're not using temp tables here, i.e. tempTables is false,
3046     //then the results from both sets of queries above should be equal,
3047     //so behavior should be the same
3048     if( tempTables && ( nonTempURLs.count() > 0 || nonTempIDs.count() > 0 ) )
3049             tempTablesAndInPermanent = true;
3050     if( tempTablesAndInPermanent && nonTempURLs.count() > 0 && nonTempIDs.count() > 0 )
3051             permanentFullMatch = true;
3052
3053     //debug() << "tempTablesAndInPermanent = " << (tempTablesAndInPermanent?"true":"false");
3054     //debug() << "permanentFullMatch = " << (permanentFullMatch?"true":"false");
3055
3056     //debug() << "Entering checks";
3057     //first case: not in permanent table or temporary table
3058     if( !tempTablesAndInPermanent && urls.empty() && uniqueids.empty() )
3059     {
3060         //debug() << "first case";
3061         QString insertline = QStringx( "INSERT INTO uniqueid%1 (deviceid, url, uniqueid, dir) "
3062                                       "VALUES ( %2,'%3', '%4', '%5');" )
3063                 .args( QStringList()
3064                  << ( tempTables ? "_temp" : "" )
3065                  << currdeviceid
3066                  << currurl
3067                  << currid
3068                  << currdir );
3069         insert( insertline, NULL );
3070         //debug() << "aftCheckPermanentTables #1";
3071         aftCheckPermanentTables( currdeviceid, currid, currurl );
3072         return;
3073     }
3074
3075     //next case: not in permanent table, but a match on one or the other in the temporary table
3076     //OR, we are using permanent tables (and not considering temp ones)
3077     if( !tempTablesAndInPermanent )
3078     {
3079         if( urls.empty() ) //uniqueid already found in temporary table but not url; check the old URL
3080         {
3081             //stat the original URL
3082             QString absPath = mpm->getAbsolutePath( uniqueids[2].toInt(), uniqueids[0] );
3083             //debug() << "At doAFTStuff, stat-ing file " << absPath;
3084             bool statSuccessful = false;
3085             bool pathsSame = absPath == bundle->url().path();
3086             if( !pathsSame )
3087                 statSuccessful = QFile::exists( absPath );
3088             if( statSuccessful ) //if true, new one is a copy
3089                 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << bundle->url().path();
3090             else  //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3091             {
3092                 //debug() << "stat was NOT successful, updating tables with: ";
3093                 //debug() << QString( "UPDATE uniqueid%1 SET url='%2', dir='%3' WHERE uniqueid='%4';" ).arg( ( tempTables ? "_temp" : "" ), currurl, currdir, currid );
3094                 query( QStringx( "UPDATE uniqueid%1 SET deviceid = %2, url='%3', dir='%4' WHERE uniqueid='%5';" )
3095                       .args( QStringList()
3096                       << ( tempTables ? "_temp" : "" )
3097                       << currdeviceid
3098                       << currurl
3099                       << currdir
3100                       << currid ) );
3101                 if( !pathsSame )
3102                     emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3103             }
3104         }
3105         //okay then, url already found in temporary table but different uniqueid
3106         //a file exists in the same place as before, but new uniqueid...assume
3107         //that this is desired user behavior
3108         //NOTE: this should never happen during an incremental scan with temporary tables...!
3109         else if( uniqueids.empty() )
3110         {
3111             //debug() << "file exists in same place as before, new uniqueid";
3112             query( QString( "UPDATE uniqueid%1 SET uniqueid='%2' WHERE deviceid = %3 AND url='%4';" )
3113                     .arg( tempTables ? "_temp" : ""
3114                    , currid
3115                    , currdeviceid
3116                    , currurl ) );
3117             emit uniqueIdChanged( bundle->url().path(), urls[1], bundle->uniqueId() );
3118         }
3119         //else uniqueid and url match; nothing happened, so safely exit
3120         return;
3121     }
3122     //okay...being here means, we are using temporary tables, AND it exists in the permanent table
3123     else
3124     {
3125         //first case...full match exists in permanent table, should then be no match in temp table
3126         //(since code below deleted from permanent table after changes)
3127         //in this case, just insert into temp table
3128         if( permanentFullMatch )
3129         {
3130             QString insertline = QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3131                                           "VALUES ( %1, '%2'" )
3132                                             .arg( currdeviceid
3133                                             , currurl );
3134             insertline += QString( ", '%1', '%2');" ).arg( currid ).arg( currdir );
3135             //debug() << "running command: " << insertline;
3136             insert( insertline, NULL );
3137             //debug() << "aftCheckPermanentTables #2";
3138             aftCheckPermanentTables( currdeviceid, currid, currurl );
3139             return;
3140         }
3141
3142         //second case...full match exists in permanent table, but path is different
3143         if( nonTempURLs.empty() )
3144         {
3145             //stat the original URL
3146             QString absPath = mpm->getAbsolutePath( nonTempIDs[2].toInt(), nonTempIDs[0] );
3147             //debug() << "At doAFTStuff part 2, stat-ing file " << absPath;
3148             bool statSuccessful = false;
3149             bool pathsSame = absPath == bundle->url().path();
3150             if( !pathsSame )
3151                 statSuccessful = QFile::exists( absPath );
3152             if( statSuccessful ) //if true, new one is a copy
3153                 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << currurl;
3154             else  //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3155             {
3156                 //debug() << "stat part 2 was NOT successful, updating tables with: ";
3157                 query( QString( "DELETE FROM uniqueid WHERE uniqueid='%1';" )
3158                       .arg( currid ) );
3159                 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3160                                 "VALUES ( %1, '%2', '%3', '%4')" )
3161                                 .arg( currdeviceid
3162                                 , currurl
3163                                 , currid
3164                                 , currdir ) );
3165                 if( !pathsSame )
3166                     emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3167             }
3168         }
3169         else if( nonTempIDs.empty() )
3170         {
3171             //debug() << "file exists in same place as before, part 2, new uniqueid";
3172             query( QString( "DELETE FROM uniqueid WHERE deviceid = %1 AND url='%2';" )
3173                       .arg( currdeviceid )
3174                       .arg( currurl ) );
3175             query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) VALUES ( %1, '%2', '%3', '%4')" )
3176                 .arg( currdeviceid
3177                 , currurl
3178                 , currid
3179                 , currdir ) );
3180             emit uniqueIdChanged( bundle->url().path(), nonTempURLs[1], bundle->uniqueId() );
3181         }
3182         //else do nothing...really this case should never happen
3183         return;
3184     }
3185 }
3186
3187 void
3188 CollectionDB::emitFileDeleted( const QString &absPath, const QString &uniqueid )
3189 {
3190   if( uniqueid.isEmpty() )
3191       emit fileDeleted( absPath );
3192   else
3193       emit fileDeleted( absPath, uniqueid );
3194 }
3195
3196 void
3197 CollectionDB::emitFileAdded( const QString &absPath, const QString &uniqueid )
3198 {
3199     if( uniqueid.isEmpty() )
3200         emit fileAdded( absPath );
3201     else
3202         emit fileAdded( absPath, uniqueid );
3203 }
3204
3205 QString
3206 CollectionDB::urlFromUniqueId( const QString &id )
3207 {
3208     //bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3209     bool scanning = false;
3210     QStringList urls = query( QString(
3211             "SELECT deviceid, url "
3212             "FROM uniqueid%1 "
3213             "WHERE uniqueid = '%2';" )
3214                 .arg( scanning ? "_temp" : QString() )
3215                 .arg( id ), true );
3216
3217     if( urls.empty() && scanning )
3218         urls = query( QString(
3219                     "SELECT deviceid, url "
3220                     "FROM uniqueid "
3221                     "WHERE uniqueid = '%1';" )
3222                         .arg( id ) );
3223
3224     if( urls.empty() )
3225         return QString();
3226
3227     return MountPointManager::instance()->getAbsolutePath( urls[0].toInt(), urls[1] );
3228 }
3229
3230 QString
3231 CollectionDB::uniqueIdFromUrl( const KUrl &url )
3232 {
3233     MountPointManager *mpm = MountPointManager::instance();
3234     int currdeviceid = mpm->getIdForUrl( url.path() );
3235     QString currurl = escapeString( mpm->getRelativePath( currdeviceid, url.path() ) );
3236
3237     //bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3238     bool scanning = false;
3239     QStringList uid = query( QString(
3240             "SELECT uniqueid "
3241             "FROM uniqueid%1 "
3242             "WHERE deviceid = %2 AND url = '%3';" )
3243                 .arg( scanning ? "_temp" : QString() )
3244                 .arg( currdeviceid )
3245                 .arg( currurl ), true );
3246
3247     if( uid.empty() && scanning )
3248         uid = query( QString(
3249                 "SELECT uniqueid "
3250                 "FROM uniqueid "
3251                 "WHERE deviceid = %1 AND url = '%2';" )
3252                     .arg( currdeviceid )
3253                     .arg( currurl ) );
3254
3255     if( uid.empty() )
3256         return QString();
3257
3258     return uid[0];
3259 }
3260
3261 QString
3262 CollectionDB::getURL( const MetaBundle &bundle )
3263 {
3264     uint artID = artistID( bundle.artist(), false );
3265     if( !artID )
3266         return QString();
3267
3268     uint albID = albumID( bundle.album(), false );
3269     if( !albID )
3270         return QString();
3271
3272     QString q = QString( "SELECT tags.deviceid, tags.url "
3273             "FROM tags "
3274             "WHERE tags.album = '%1' AND tags.artist = '%2' AND tags.track = '%3' AND tags.title = '%4'" +
3275             deviceidSelection() + ';' )
3276         .arg( albID )
3277         .arg( artID )
3278         .arg( bundle.track() )
3279         .arg( escapeString( bundle.title() ) );
3280
3281     QStringList urls = URLsFromQuery( query( q ) );
3282
3283     if( urls.empty() )
3284         return QString();
3285
3286     if( urls.size() == 1 )
3287     {
3288         return urls.first();
3289     }
3290
3291     QString url = urls.first();
3292     int maxPlayed = -1;
3293     for( QStringList::iterator it = urls.begin();
3294             it != urls.end();
3295             it++ )
3296     {
3297         int pc = getPlayCount( *it );
3298         if( pc > maxPlayed )
3299         {
3300             maxPlayed = pc;
3301             url = *it;
3302         }
3303     }
3304
3305     return url;
3306 }
3307
3308 // Helper function to convert the "tags.sampler" column to a MetaBundle::Collection value
3309 //
3310 // We use the first char of boolT / boolF as not all DBs store true/false as
3311 // numerics (and it's only a single-char column)
3312 static int
3313 samplerToCompilation( const QString &it )
3314 {
3315     if( it == CollectionDB::instance()->boolT().mid( 0, 1 ) )
3316     {
3317          return MetaBundle::CompilationYes;
3318     }
3319     else if( it == CollectionDB::instance()->boolF().mid( 0, 1 ) )
3320     {
3321         return MetaBundle::CompilationNo;
3322     }
3323     return MetaBundle::CompilationUnknown;
3324 }
3325
3326 MetaBundle
3327 CollectionDB::bundleFromQuery( QStringList::const_iterator *iter )
3328 {
3329     QStringList::const_iterator &it = *iter;
3330     MetaBundle b;
3331     //QueryBuilder automatically inserts the deviceid as return value if asked for the path
3332     QString rpath = *it;
3333     int deviceid = (*++it).toInt();
3334     b.setPath      ( MountPointManager::instance()->getAbsolutePath( deviceid, rpath ) );
3335     b.setAlbum     (  *++it );
3336     b.setArtist    (  *++it );
3337     b.setComposer  (  *++it );
3338     b.setGenre     (  *++it );
3339     b.setTitle     (  *++it );
3340     b.setYear      ( (*++it).toInt() );
3341     b.setComment   (  *++it );
3342     b.setTrack     ( (*++it).toInt() );
3343     b.setBitrate   ( (*++it).toInt() );
3344     b.setDiscNumber( (*++it).toInt() );
3345     b.setLength    ( (*++it).toInt() );
3346     b.setSampleRate( (*++it).toInt() );
3347     b.setFilesize  ( (*++it).toInt() );
3348
3349     b.setCompilation( samplerToCompilation( *it ) );
3350     ++it;
3351     b.setFileType( (*++it).toInt() );
3352     b.setBpm       ( (*++it).toFloat() );
3353
3354     b.setScore     ( (*++it).toFloat() );
3355     b.setRating    ( (*++it).toInt() );
3356     b.setPlayCount ( (*++it).toInt() );
3357     b.setLastPlay  ( (*++it).toInt() );
3358
3359     if( false && b.length() <= 0 ) {
3360         // we try to read the tags, despite the slow-down
3361         debug() << "Audioproperties not known for: " << b.url().fileName();
3362         b.readTags( TagLib::AudioProperties::Fast);
3363     }
3364
3365     return b;
3366 }
3367
3368 static void
3369 fillInBundle( QStringList values, MetaBundle &bundle )
3370 {
3371     //TODO use this whenever possible
3372
3373     // crash prevention
3374     while( values.count() < 16 )
3375         values += "IF YOU CAN SEE THIS THERE IS A BUG!";
3376
3377     QStringList::ConstIterator it = values.begin();
3378
3379     bundle.setAlbum     ( *it ); ++it;
3380     bundle.setArtist    ( *it ); ++it;
3381     bundle.setComposer  ( *it ); ++it;
3382     bundle.setGenre     ( *it ); ++it;
3383     bundle.setTitle     ( *it ); ++it;
3384     bundle.setYear      ( (*it).toInt() ); ++it;
3385     bundle.setComment   ( *it ); ++it;
3386     bundle.setDiscNumber( (*it).toInt() ); ++it;
3387     bundle.setTrack     ( (*it).toInt() ); ++it;
3388     bundle.setBitrate   ( (*it).toInt() ); ++it;
3389     bundle.setLength    ( (*it).toInt() ); ++it;
3390     bundle.setSampleRate( (*it).toInt() ); ++it;
3391     bundle.setFilesize  ( (*it).toInt() ); ++it;
3392     bundle.setFileType  ( (*it).toInt() ); ++it;
3393     bundle.setBpm       ( (*it).toFloat() ); ++it;
3394
3395     bundle.setCompilation( samplerToCompilation( *it ) );
3396     ++it;
3397
3398     bundle.setUniqueId(*it);
3399 }
3400
3401 bool
3402 CollectionDB::bundleForUrl( MetaBundle* bundle )
3403 {
3404     int deviceid = MountPointManager::instance()->getIdForUrl( bundle->url() );
3405     KUrl rpath;
3406     MountPointManager::instance()->getRelativePath( deviceid, bundle->url(), rpath );
3407     QStringList values = query( QString(
3408             "SELECT album.name, artist.name, composer.name, genre.name, tags.title, "
3409             "year.name, tags.comment, tags.discnumber, "
3410             "tags.track, tags.bitrate, tags.length, tags.samplerate, "
3411             "tags.filesize, tags.filetype, tags.bpm, tags.sampler, uniqueid.uniqueid "
3412             "FROM tags LEFT OUTER JOIN uniqueid ON tags.url = uniqueid.url AND tags.deviceid = uniqueid.deviceid,"
3413             "album, artist, composer, genre, year "
3414             "WHERE album.id = tags.album AND artist.id = tags.artist AND composer.id = tags.composer AND "
3415             "genre.id = tags.genre AND year.id = tags.year AND tags.url = '%2' AND tags.deviceid = %1;" )
3416                 .arg( deviceid )
3417                 .arg( escapeString( rpath.path( ) ) ) );
3418
3419     bool valid = false;
3420
3421     if ( !values.empty() )
3422     {
3423         fillInBundle( values, *bundle );
3424         valid = true;
3425     }
3426 //    else if( MediaBrowser::instance() && MediaBrowser::instance()->getBundle( bundle->url(), bundle ) )
3427 //    {
3428 //        valid = true;
3429 //    }
3430     else
3431     {
3432         // check if it's a podcast
3433         PodcastEpisodeBundle peb;
3434         if( getPodcastEpisodeBundle( bundle->url(), &peb ) )
3435         {
3436             if( bundle->url().protocol() == "file" && QFile::exists( bundle->url().path() ) )
3437             {
3438                 MetaBundle mb( bundle->url(), true /* avoid infinite recursion */ );
3439                 *bundle = mb;
3440             }
3441             bundle->copyFrom( peb );
3442             valid = true;
3443         }
3444     }
3445
3446     return valid;
3447 }
3448
3449
3450 Q3ValueList<MetaBundle>
3451 CollectionDB::bundlesByUrls( const KUrl::List& urls )
3452 {
3453     BundleList bundles;
3454     QStringList paths;
3455     QueryBuilder qb;
3456     int count = 0;
3457
3458     for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it, ++count )
3459     {
3460         // non file stuff won't exist in the db, but we still need to
3461         // re-insert it into the list we return, just with no tags assigned
3462         paths += (*it).protocol() == "file" ? (*it).path() : (*it).url();
3463
3464         if( paths.count() == 50 || count == urls.size() - 1 )
3465         {
3466             qb.clear();
3467
3468             qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
3469             qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
3470             qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
3471             qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
3472             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
3473             qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
3474             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
3475             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
3476             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBitrate );
3477             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
3478             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valLength );
3479             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valSamplerate );
3480             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
3481             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
3482             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
3483             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
3484             qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valIsCompilation );
3485
3486             qb.addUrlFilters( paths );
3487             qb.setOptions( QueryBuilder::optRemoveDuplicates );
3488
3489             const QStringList values = qb.run();
3490
3491             BundleList buns50;
3492             MetaBundle b;
3493             oldForeach( values )
3494             {
3495                 b.setAlbum     (    *it );
3496                 b.setArtist    (  *++it );
3497                 b.setComposer  (  *++it );
3498                 b.setGenre     (  *++it );
3499                 b.setTitle     (  *++it );
3500                 b.setYear      ( (*++it).toInt() );
3501                 b.setComment   (  *++it );
3502                 b.setTrack     ( (*++it).toInt() );
3503                 b.setBitrate   ( (*++it).toInt() );
3504                 b.setDiscNumber( (*++it).toInt() );
3505                 b.setLength    ( (*++it).toInt() );
3506                 b.setSampleRate( (*++it).toInt() );
3507                 b.setFilesize  ( (*++it).toInt() );
3508                 b.setFileType  ( (*++it).toInt() );
3509                 b.setBpm       ( (*++it).toFloat() );
3510                 b.setPath      (  *++it );
3511
3512                 b.setCompilation( samplerToCompilation( *it ) );
3513                 ++it;
3514
3515                 b.checkExists();
3516
3517                 buns50.append( b );
3518             }
3519
3520             // we get no guarantee about the order that the database
3521             // will return our values, and sqlite indeed doesn't return
3522             // them in the desired order :( (MySQL does though)
3523             oldForeach( paths )
3524             {
3525                 for( BundleList::Iterator jt = buns50.begin(), end = buns50.end(); jt != end; ++jt )
3526                 {
3527                     if ( ( *jt ).url().path() == ( *it ))
3528                     {
3529                         bundles += *jt;
3530                         buns50.remove( jt );
3531                         goto success;
3532                     }
3533                 }
3534
3535                 // if we get here, we didn't find an entry
3536                 {
3537                     KUrl url = KUrl( *it );
3538
3539                     if( true /* !MediaBrowser::instance()->getBundle( url, &b ) */ )
3540                     {
3541                         if( url.isLocalFile() )
3542                         {
3543                             b = MetaBundle( url );
3544                         }
3545                         else
3546                         {
3547                             b = MetaBundle();
3548                             b.setUrl( url );
3549                             // FIXME: more context for i18n after string freeze
3550                             b.setTitle( QString( "%1 %2 %3%4" )
3551                                   .arg( url.fileName(),
3552                                      i18n( "from" ),
3553                                      url.hasHost() ? url.host() : QString(),
3554                                      url.directory() ) );
3555                         }
3556
3557                         // check if it's a podcast
3558                         PodcastEpisodeBundle peb;
3559                         if( getPodcastEpisodeBundle( url, &peb ) )
3560                         {
3561                             b.copyFrom( peb );
3562                         }
3563                         else if( b.url().protocol() == "audiocd" || b.url().protocol() == "cdda" )
3564                         {
3565                             // try to see if the engine has some info about the
3566                             // item (the intended behaviour should be that if the
3567                             // item is an AudioCD track, the engine can return
3568                             // CDDB data for it)
3569                             Engine::SimpleMetaBundle smb;
3570                             if ( EngineController::engine()->metaDataForUrl( b.url(), smb ) )
3571                             {
3572                                 b.setTitle( smb.title );
3573                                 b.setArtist( smb.artist );
3574                                 b.setAlbum( smb.album );
3575                                 b.setComment( smb.comment );
3576                                 b.setGenre( smb.genre );
3577                                 b.setBitrate( smb.bitrate.toInt() );
3578                                 b.setSampleRate( smb.samplerate.toInt() );
3579                                 b.setLength( smb.length.toInt() );
3580                                 b.setYear( smb.year.toInt() );
3581                                 b.setTrack( smb.tracknr.toInt() );
3582                             }
3583                         }
3584                     }
3585                 }
3586                 bundles += b;
3587
3588 success: ;
3589             }
3590
3591             paths.clear();
3592         }
3593     }
3594
3595     return bundles;
3596 }
3597
3598
3599 void
3600 CollectionDB::addAudioproperties( const MetaBundle& bundle )
3601 {
3602     int deviceid = MountPointManager::instance()->getIdForUrl( bundle.url() );
3603     KUrl rpath;
3604     MountPointManager::instance()->getRelativePath( deviceid, bundle.url(), rpath );
3605     query( QString( "UPDATE tags SET bitrate='%1', length='%2', samplerate='%3' WHERE url='%5' AND deviceid = %4;" )
3606                     .arg( bundle.bitrate() )
3607                     .arg( bundle.length() )
3608                     .arg( bundle.sampleRate() )
3609                     .arg( deviceid )
3610                     .arg( escapeString( rpath.path() ) ) );
3611 }
3612
3613
3614 void
3615 CollectionDB::addSongPercentage( const QString &url, float percentage,
3616         const QString &reason, const QDateTime *playtime )
3617 {
3618     //the URL must always be inserted last! an escaped URL can contain Strings like %1->bug
3619     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3620     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3621     //statistics table might not have those values, but we need them later, so keep them
3622     int statDevId = deviceid;
3623     QString statRPath = rpath;
3624     QStringList values =
3625         query( QString(
3626             "SELECT playcounter, createdate, percentage, rating FROM statistics "
3627             "WHERE url = '%2' AND deviceid = %1;" )
3628             .arg( statDevId ).arg( escapeString( statRPath ) ) );
3629
3630     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3631     if ( values.isEmpty() )
3632     {
3633         QString rpath2 = '.' + url;
3634         values = query( QString(
3635             "SELECT playcounter, createdate, percentage, rating FROM statistics "
3636             "WHERE url = '%1' AND deviceid = -1;" )
3637             .arg( escapeString( rpath2 ) ) );
3638         if ( !values.isEmpty() )
3639         {
3640             statRPath = rpath2;
3641             statDevId = -1;
3642         }
3643     }
3644
3645     uint atime = playtime ? playtime->toTime_t() : QDateTime::currentDateTime().toTime_t();
3646
3647     // check boundaries
3648     if ( percentage > 100.f ) percentage = 100.f;
3649     if ( percentage < 1.f )   percentage = 1.f;
3650
3651     if ( !values.isEmpty() )
3652     {
3653
3654         // increment playcounter and update accesstime
3655         query( QString( "UPDATE statistics SET playcounter=%1, accessdate=%2 WHERE url='%4' AND deviceid= %3;" )
3656                         .arg( values[0] + " + 1" )
3657                         .arg( atime )
3658                         .arg( statDevId )
3659                         .arg( escapeString( statRPath ) ) );
3660     }
3661     else
3662     {
3663         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3664                         "VALUES ( '%6', %5, %1, %2, 0, 1, 0, %3, %4 );" )
3665                         .arg( atime )
3666                         .arg( atime )
3667                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3668                         .arg( boolF() )
3669                         .arg( statDevId )
3670                         .arg( escapeString( statRPath ) ), 0 );
3671     }
3672
3673     double prevscore = 50;
3674     int playcount = 0;
3675     if( !values.isEmpty() )
3676     {
3677         playcount = values[ 0 ].toInt();
3678         // This stops setting the Rating (which creates a row) from affecting the
3679         // prevscore of an unplayed track. See bug 127475
3680         if ( playcount )
3681             prevscore = values[ 2 ].toDouble();
3682     }
3683     const QStringList v = query( QString( "SELECT length FROM tags WHERE url = '%2' AND deviceid = %1;" )
3684                             .arg( deviceid ).arg( escapeString( rpath ) ) );
3685     const int length = v.isEmpty() ? 0 : v.first().toInt();
3686
3687     ScriptManager::instance()->requestNewScore( url, prevscore, playcount, length, percentage, reason );
3688 }
3689
3690
3691 float
3692 CollectionDB::getSongPercentage( const QString &url )
3693 {
3694     QueryBuilder qb;
3695     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valScore );
3696     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3697
3698     QStringList values = qb.run();
3699
3700     if( !values.isEmpty() )
3701         return values.first().toFloat();
3702
3703     return 0;
3704 }
3705
3706 int
3707 CollectionDB::getSongRating( const QString &url )
3708 {
3709     QueryBuilder qb;
3710     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valRating );
3711     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3712
3713     QStringList values = qb.run();
3714
3715     if( values.count() )
3716         return qBound( 0, values.first().toInt(), 10 );
3717
3718     return 0;
3719 }
3720
3721 void
3722 CollectionDB::setSongPercentage( const QString &url , float percentage)
3723 {
3724     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3725     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3726     QStringList values =
3727         query( QString(
3728             "SELECT playcounter, createdate, accessdate, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3729             .arg( deviceid ).arg( escapeString( rpath ) ) );
3730
3731     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3732     if ( values.isEmpty() )
3733     {
3734         QString rpath2 = '.' + url;
3735         values = query( QString(
3736             "SELECT playcounter, createdate, accessdate, rating FROM statistics "
3737             "WHERE url = '%1' AND deviceid = -1;" )
3738             .arg( escapeString( rpath2 ) ) );
3739         if ( !values.isEmpty() )
3740         {
3741             rpath = rpath2;
3742             deviceid = -1;
3743         }
3744     }
3745
3746     // check boundaries
3747     if ( percentage > 100.f ) percentage = 100.f;
3748     if ( percentage < 0.f )   percentage = 0.f;
3749
3750     if ( !values.isEmpty() )
3751     {
3752         query( QString( "UPDATE statistics SET percentage=%1 WHERE url='%3' AND deviceid = %2;" )
3753                         .arg( percentage )
3754                         .arg( deviceid ).arg( escapeString( rpath ) ) );
3755     }
3756     else
3757     {
3758         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3759                          "VALUES ( '%7', %6, %2, %3, %1, 0, 0, %3, %4 );" )
3760                         .arg( percentage )
3761                         .arg( QDateTime::currentDateTime().toTime_t() )
3762                         .arg( 0 )
3763                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3764                         .arg( boolF() )
3765                         .arg( deviceid )
3766                         .arg( escapeString( rpath ) ),0 );
3767     }
3768
3769     emit scoreChanged( url, percentage );
3770 }
3771
3772 void
3773 CollectionDB::setSongRating( const QString &url, int rating, bool toggleHalf )
3774 {
3775     int deviceid = MountPointManager::instance()->getIdForUrl( url );
3776     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3777     QStringList values =
3778         query( QString(
3779             "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3780             .arg( deviceid )
3781             .arg( escapeString( rpath ) ) );
3782
3783     //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3784     if ( values.isEmpty() )
3785     {
3786         QString rpath2 = '.' + url;
3787         values = query( QString(
3788             "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics "
3789             "WHERE url = '%1' AND deviceid = -1;" )
3790             .arg( escapeString( rpath2 ) ) );
3791         if ( !values.isEmpty() )
3792         {
3793             rpath = rpath2;
3794             deviceid = -1;
3795         }
3796     }
3797
3798     bool ok = true;
3799     if ( !values.isEmpty() )
3800     {
3801         int prev = values[4].toInt( &ok );
3802         if( ok && toggleHalf && ( prev == rating || ( prev == 1 && rating == 2 ) ) )
3803         {
3804             if( prev == 1 && rating == 2 )
3805                 rating = 0;
3806             else if( rating % 2 ) //.5
3807                 rating++;
3808             else
3809                 rating--;
3810         }
3811     }
3812
3813     // check boundaries
3814     if ( rating > 10 ) rating = 10;
3815     if ( rating < 0 ) rating = 0;
3816
3817     if ( !values.isEmpty() )
3818     {
3819         query( QString( "UPDATE statistics SET rating=%1 WHERE url='%3' AND deviceid = %2;" )
3820                         .arg( rating )
3821                         .arg( deviceid )
3822                         .arg( escapeString( rpath ) ) );
3823     }
3824     else
3825     {
3826         insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, rating, playcounter, uniqueid, deleted ) "
3827                          "VALUES ( '%7', %6, %2, %3, 0, %1, 0, %4, %5 );" )
3828                         .arg( rating )
3829                         .arg( QDateTime::currentDateTime().toTime_t() )
3830                         .arg( 0 )
3831                         .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3832                         .arg( boolF() )
3833                         .arg( deviceid )
3834                         .arg( escapeString( rpath ) ), NULL );
3835     }
3836
3837     emit ratingChanged( url, rating );
3838 }
3839
3840 int
3841 CollectionDB::getPlayCount( const QString &url )
3842 {
3843     //queryBuilder is good
3844     QueryBuilder qb;
3845     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valPlayCounter );
3846     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3847     QStringList values = qb.run();
3848     if( values.count() )
3849         return values.first().toInt();
3850     return 0;
3851 }
3852
3853 QDateTime
3854 CollectionDB::getFirstPlay( const QString &url )
3855 {
3856     QueryBuilder qb;
3857     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valCreateDate );
3858     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3859     QStringList values = qb.run();
3860     QDateTime dt;
3861     if( values.count() )
3862         dt.setTime_t( values.first().toUInt() );
3863     return dt;
3864 }
3865
3866 QDateTime
3867 CollectionDB::getLastPlay( const QString &url )
3868 {
3869     QueryBuilder qb;
3870     qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valAccessDate );
3871     qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3872     QStringList values = qb.run();
3873     QDateTime dt;
3874     if( values.count() )
3875         dt.setTime_t( values.first().toUInt() );
3876     else
3877         dt.setTime_t( 0 );
3878     return dt;
3879 }
3880 /*!
3881  *  @short: exchange url references in the database for a particular file
3882  *  @note: deletes all items for newURL, changes oldURL->newURL, deletes oldURL.
3883  *  FIXME: should we check if lyrics etc exist in the newURL and keep them if necessary?
3884  */
3885 void
3886 CollectionDB::migrateFile( const QString &oldURL, const QString &newURL )
3887 {
3888     int oldMediaid = MountPointManager::instance()->getIdForUrl( oldURL );
3889     QString oldRpath = MountPointManager::instance()->getRelativePath( oldMediaid, oldURL );
3890
3891     int newMediaid = MountPointManager::instance()->getIdForUrl( newURL );
3892     QString newRpath = MountPointManager::instance()->getRelativePath( newMediaid, newURL );
3893
3894     //  Ensure destination is clear.
3895     query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
3896         .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3897
3898     query( QString( "DELETE FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3899         .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3900
3901     query( QString( "DELETE FROM tags_labels WHERE url = '%2' and deviceid = %1;" )
3902         .arg( newMediaid).arg( escapeString( newRpath ) ) );
3903
3904     if ( !getLyrics( oldURL ).isEmpty() )
3905         query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
3906             .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3907     //  Migrate
3908     //code looks ugly but prevents problems when the URL contains HTTP escaped characters
3909     query( QString( "UPDATE tags SET url = '%3', deviceid = %1" )
3910         .arg( newMediaid ).arg( escapeString( newRpath ) )
3911         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3912         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3913
3914     query( QString( "UPDATE statistics SET url = '%2', deviceid = %1" )
3915         .arg( newMediaid ).arg( escapeString( newRpath ) )
3916         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3917         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3918
3919     query( QString( "UPDATE lyrics SET url = '%2', deviceid = %1" )
3920         .arg( newMediaid ).arg( escapeString( newRpath ) )
3921         + QString( " WHERE deviceid=%1 AND url = '%2';" )
3922         .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3923
3924     query( QString( "UPDATE tags_labels SET url = '%2', deviceid = %1 WHERE deviceid = %3 AND url = '%4';" )
3925             .arg( QString::number( newMediaid ), escapeString( newRpath ), QString::number( oldMediaid ), escapeString( oldRpath ) ) );
3926
3927     query( QString( "UPDATE uniqueid SET url = '%1', deviceid = %2 WHERE url = '%3' AND deviceid = %4;" )
3928         .arg( escapeString( newRpath ), QString::number( newMediaid ),
3929               escapeString( oldRpath ), QString::number( oldMediaid ) ) );
3930
3931     query( QString( "UPDATE playlists SET url = '%1' WHERE url = '%2';" )
3932         .arg( escapeString( newURL ),
3933               escapeString( oldURL ) ) );
3934 }
3935
3936 void
3937 CollectionDB::fileOperationResult( KIO::Job *job ) // slot
3938 {
3939     if(job->error())
3940     {
3941         m_fileOperationFailed = true;
3942         debug() << "file operation failed: " << job->errorText();
3943     }
3944     else
3945     {
3946         m_fileOperationFailed = false;
3947     }
3948
3949     m_waitForFileOperation = false;
3950 }
3951
3952 void CollectionDB::cancelMovingFileJob()
3953 {
3954     m_moveFileJobCancelled = true;
3955 }
3956
3957 bool
3958 CollectionDB::organizeFile( const KUrl &src, /*const OrganizeCollectionDialog &dialog,*/ bool copy )
3959 {
3960     Q_UNUSED( src ); Q_UNUSED( copy );
3961     return false;
3962 /*
3963    if( !MetaBundle::isKioUrl( src ) )
3964        return false;
3965
3966    bool overwrite = dialog.overwriteCheck->isChecked();
3967    bool localFile = src.isLocalFile();
3968    KUrl tmpSrc = src;
3969    if( !localFile )
3970    {
3971       QString tmp;
3972       QString extension = src.url().section( '.', -1 );
3973       extension = extension.section("?", 0, 0);  // remove trailling stuff lead by ?, if any
3974
3975       int count = 0;
3976       do
3977       {
3978          tmp = QString( dialog.folderCombo->currentText() + "/amarok-tmp-%1." + extension ).arg( count );
3979          count++;
3980       } while( QFile::exists( tmp ) );
3981       tmpSrc = KUrl( tmp );
3982
3983       KIO::FileCopyJob *job = 0;
3984       if( copy )
3985       {
3986          job = KIO::file_copy( src, tmpSrc, -1, KIO::HideProgressInfo );
3987       }
3988       else
3989       {
3990          job = KIO::file_move( src, tmpSrc, -1, KIO::HideProgressInfo );
3991       }
3992       connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
3993       m_waitForFileOperation = true;
3994       while( m_waitForFileOperation )
3995       {
3996         if( m_moveFileJobCancelled )
3997         {
3998             disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
3999
4000             QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4001             job->kill();
4002             QFile file( partFile );
4003             if( file.exists() ) file.remove();
4004
4005             m_waitForFileOperation = false;
4006             m_fileOperationFailed = true;
4007             continue;
4008         }
4009
4010          usleep( 10000 );
4011          kapp->processEvents( QEventLoop::AllEvents );
4012       }
4013
4014       if( m_fileOperationFailed )
4015       {
4016          debug() << "failed to transfer " << src.url() << " to " << tmpSrc;
4017
4018          m_moveFileJobCancelled = false;
4019          return false;
4020       }
4021    }
4022
4023    //Building destination here.
4024    MetaBundle mb( tmpSrc );
4025    QString dest = dialog.buildDestination( dialog.buildFormatString(), mb );
4026
4027    debug() << "Destination: " << dest;
4028
4029    if( !m_moveFileJobCancelled && tmpSrc.path() != dest ) //suppress error warning that file couldn't be moved
4030    {
4031       if( !CollectionDB::instance()->moveFile( tmpSrc.url(), dest, overwrite, copy && localFile ) )
4032       {
4033          if( !localFile )
4034             QFile::remove( tmpSrc.path() );
4035
4036          m_moveFileJobCancelled = false;
4037          return false;
4038       }
4039    }
4040
4041    //Use cover image for folder icon
4042    if( !m_moveFileJobCancelled && dialog.coverCheck->isChecked() && !mb.artist().isEmpty() && !mb.album().isEmpty() )
4043    {
4044       KUrl dstURL = KUrl( dest );
4045       dstURL.cleanPath();
4046
4047       QString path  = dstURL.directory();
4048       QString cover = CollectionDB::instance()->albumImage( mb.artist(), mb.album(), false, 1 );
4049
4050       if( !QFile::exists(path + "/.directory") && !cover.endsWith( "nocover.png" ) )
4051       {
4052          //QPixmap thumb;        //Not amazon nice.
4053          //if ( thumb.load( cover ) ){
4054          //thumb.save(path + "/.front.png", "PNG", -1 ); //hide files
4055
4056          KConfig config(path + "/.directory");
4057          config.setGroup("Desktop Entry");
4058
4059          if( !config.hasKey("Icon") )
4060          {
4061             config.writeEntry( "Icon", cover );
4062             config.sync();
4063          }
4064          //}         //Not amazon nice.
4065       }
4066    }
4067
4068    if( localFile && isDirInCollection( src.directory() ) && QDir().rmdir( src.directory() ) )
4069    {
4070       debug() << "removed: " << src.directory();
4071    }
4072
4073    m_moveFileJobCancelled = false;
4074
4075    return true;
4076 */
4077 }
4078
4079 bool
4080 CollectionDB::moveFile( const QString &src, const QString &dest, bool overwrite, bool copy )
4081 {
4082     DEBUG_BLOCK
4083     if(src == dest){
4084         debug() << "Source and destination URLs are the same, aborting.";
4085         return false;
4086     }
4087
4088     // Escape URL.
4089     KUrl srcURL = KUrl( src );
4090     KUrl dstURL = KUrl( dest );
4091
4092     // Clean it.
4093     srcURL.cleanPath();
4094     dstURL.cleanPath();
4095
4096     // Make sure it is valid.
4097     if(!srcURL.isValid() || !dstURL.isValid())
4098         debug() << "Invalid URL ";
4099
4100     // Get just the directory.
4101     KUrl dir = dstURL;
4102     dir.setFileName(QString());
4103
4104     // Create the directory.
4105     if(!KStandardDirs::exists(dir.path()))
4106         if(!KStandardDirs::makeDir(dir.path())) {
4107             debug() << "Unable to create directory " << dir.path();
4108         }
4109
4110     m_fileOperationFailed = false;
4111     KIO::JobFlags jobFlags = KIO::HideProgressInfo;
4112     if (overwrite)
4113         jobFlags |= KIO::Overwrite;
4114     KIO::FileCopyJob *job = 0;
4115     if( copy )
4116     {
4117         job = KIO::file_copy( srcURL, dstURL, -1, jobFlags );
4118     }
4119     else
4120     {
4121         job = KIO::file_move( srcURL, dstURL, -1, jobFlags );
4122     }
4123     connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
4124     m_waitForFileOperation = true;
4125     while( m_waitForFileOperation )
4126     {
4127         if( m_moveFileJobCancelled )
4128         {
4129             disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
4130
4131             QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4132             job->kill();
4133             QFile file( partFile );
4134             if( file.exists() ) file.remove();
4135
4136             m_waitForFileOperation = false;
4137             m_fileOperationFailed = true;
4138             continue;
4139         }
4140
4141         usleep( 10000 );
4142         kapp->processEvents( QEventLoop::AllEvents );
4143     }
4144
4145     if( !m_fileOperationFailed )
4146     {
4147         if( copy )
4148         {
4149             MetaBundle bundle( dstURL );
4150             if( bundle.isValidMedia() )
4151             {
4152                 addSong( &bundle, true );
4153                 return true;
4154             }
4155         }
4156         else
4157         {
4158             emit fileMoved( src, dest );
4159             migrateFile( srcURL.path(), dstURL.path() );
4160
4161             if( isFileInCollection( srcURL.path() ) )
4162             {
4163                 return true;
4164             }
4165             else
4166             {
4167                 MetaBundle bundle( dstURL );
4168                 if( bundle.isValidMedia() )
4169                 {
4170                     addSong( &bundle, true );
4171                     return true;
4172                 }
4173             }
4174         }
4175     }
4176
4177     return false;
4178 }
4179
4180
4181 void
4182 CollectionDB::updateDirStats( QString path, const long datetime, const bool temporary )
4183 {
4184     if ( path.endsWith( "/" ) )
4185         path = path.left( path.length() - 1 );
4186
4187     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4188     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4189
4190     if (getDbConnectionType() == DbConnection::postgresql)
4191     {
4192         // REPLACE INTO is not valid SQL for postgres, so we need to check whether we
4193         // should UPDATE() or INSERT()
4194         QStringList values = query( QString("SELECT * FROM directories%1 WHERE dir='%3' AND deviceid=%2;")
4195             .arg( temporary ? "_temp" : "")
4196             .arg( deviceid )
4197             .arg( escapeString( rpath ) ) );
4198
4199         if(values.count() > 0 )
4200         {
4201             query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%4'AND deviceid=%3;")
4202             .arg( temporary ? "_temp" : "" )
4203             .arg( datetime )
4204             .arg( deviceid )
4205             .arg( escapeString( rpath ) ) );
4206         }
4207         else
4208         {
4209
4210             query( QString( "INSERT INTO directories%1 (dir, deviceid,changedate) VALUES ('%4', %3, '%2');")
4211             .arg( temporary ? "_temp" : "")
4212             .arg( datetime )
4213             .arg( deviceid )
4214             .arg( escapeString( rpath ) ) );
4215         }
4216     }
4217     else
4218     {
4219         query( QString( "REPLACE INTO directories%1 ( dir, deviceid, changedate ) VALUES ( '%4', %3, %2 );" )
4220                   .arg( temporary ? "_temp" : "" )
4221                   .arg( datetime )
4222                   .arg( deviceid )
4223                   .arg( escapeString( rpath ) ) );
4224     }
4225
4226     INotify::instance()->watchDir( path );
4227 }
4228
4229
4230 void
4231 CollectionDB::removeSongsInDir( QString path, QMap<QString,QString> *tagsRemoved )
4232 {
4233     if ( path.endsWith( "/" ) )
4234         path = path.left( path.length() - 1 );
4235     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4236     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4237
4238     // Pass back the list of tags we actually delete if requested.
4239     if( tagsRemoved )
4240     {
4241         QStringList result
4242           = query( QString( "SELECT tags.deviceid, tags.url, uniqueid.uniqueid FROM tags "
4243                             "LEFT JOIN uniqueid ON uniqueid.url = tags.url "
4244                             "AND uniqueid.deviceid = tags.deviceid "
4245                             "WHERE tags.dir = '%2' AND tags.deviceid = %1" )
4246                    .arg( deviceid )
4247                    .arg( escapeString( rpath ) ) );
4248         QStringList::ConstIterator it = result.begin(), end = result.end();
4249         while( it != end )
4250         {
4251             int deviceid2    = (*(it++)).toInt();
4252             QString rpath2   =  *(it++);
4253             QString uniqueid =  *(it++);
4254             (*tagsRemoved)[uniqueid] = MountPointManager::instance()->getAbsolutePath(
4255                                 deviceid2, rpath2 );
4256         }
4257     }
4258
4259     query( QString( "DELETE FROM tags WHERE dir = '%2' AND deviceid = %1;" )
4260               .arg( deviceid )
4261               .arg( escapeString( rpath ) ) );
4262
4263     query( QString( "DELETE FROM uniqueid WHERE dir = '%2' AND deviceid = %1;" )
4264               .arg( deviceid )
4265               .arg( escapeString( rpath ) ) );
4266 }
4267
4268
4269 bool
4270 CollectionDB::isDirInCollection( QString path )
4271 {
4272     if ( path.endsWith( "/" ) )
4273         path = path.left( path.length() - 1 );
4274     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4275     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4276
4277     QStringList values =
4278         query( QString( "SELECT changedate FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4279                   .arg( deviceid )
4280                   .arg( escapeString( rpath ) ) );
4281
4282     return !values.isEmpty();
4283 }
4284
4285
4286 bool
4287 CollectionDB::isFileInCollection( const QString &url  )
4288 {
4289     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4290     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4291
4292     QString sql = QString( "SELECT url FROM tags WHERE url = '%2' AND deviceid = %1" )
4293                              .arg( deviceid )
4294                              .arg( escapeString( rpath ) );
4295     if ( deviceid == -1 )
4296     {
4297         sql += ';';
4298     }
4299     else
4300     {
4301         QString rpath2 = '.' + url;
4302         sql += QString( " OR url = '%1' AND deviceid = -1;" )
4303                       .arg( escapeString( rpath2 ) );
4304     }
4305     QStringList values = query( sql );
4306
4307     return !values.isEmpty();
4308 }
4309
4310
4311 void
4312 CollectionDB::removeSongs( const KUrl::List& urls )
4313 {
4314     for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it )
4315     {
4316         int deviceid = MountPointManager::instance()->getIdForUrl( *it );
4317         QString rpath = MountPointManager::instance()->getRelativePath( deviceid, (*it).path() );
4318
4319         query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
4320             .arg( deviceid )
4321             .arg( escapeString( rpath ) ) );
4322         query( QString( "DELETE FROM uniqueid WHERE url = '%2' AND deviceid = %1;" )
4323                 .arg( deviceid )
4324                 .arg( escapeString( rpath ) ) );
4325         query( QString( "UPDATE statistics SET deleted = %1 WHERE url = '%3' AND deviceid = %2;" )
4326                 .arg( boolT() )
4327                 .arg( deviceid )
4328                 .arg( escapeString( rpath ) ) );
4329     }
4330 }
4331
4332
4333 QStringList
4334 CollectionDB::similarArtists( const QString &artist, uint count )
4335 {
4336     QStringList values;
4337
4338     values = query( QString( "SELECT suggestion FROM related_artists WHERE artist = '%1' ORDER BY %2 LIMIT %3 OFFSET 0;" )
4339                                  .arg( escapeString( artist ), randomFunc(), QString::number( count ) ) );
4340
4341     if ( values.isEmpty() )
4342         Scrobbler::instance()->similarArtists( artist );
4343
4344     return values;
4345 }
4346
4347
4348 void
4349 CollectionDB::sanitizeCompilations()
4350 {
4351     query( QString( "UPDATE tags_temp SET sampler = %1 WHERE sampler IS NULL;").arg( boolF() ) );
4352 }
4353
4354 void
4355 CollectionDB::checkCompilations( const QString &path, const bool temporary )
4356 {
4357     QStringList albums;
4358     QStringList artists;
4359     QStringList dirs;
4360
4361     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4362     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4363
4364     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;" )
4365               .arg( temporary ? "_temp" : "" )
4366               .arg( deviceid )
4367               .arg( escapeString( rpath ) ) );
4368
4369     for ( int i = 0; i < albums.count(); i++ )
4370     {
4371         if ( albums[ i ].isEmpty() ) continue;
4372
4373         const uint album_id = albumID( albums[ i ], false, temporary, true );
4374         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;" )
4375                             .arg( temporary ? "_temp" : "" )
4376                             .arg( album_id ) );
4377         dirs    = query( QString( "SELECT DISTINCT dir FROM tags_temp WHERE album = '%1';" )
4378                             .arg( album_id ) );
4379
4380         if ( artists.count() > dirs.count() )
4381         {
4382             debug() << "Detected compilation: " << albums[ i ] << " - " << artists.count() << ':' << dirs.count();
4383         }
4384         query( QString( "UPDATE tags_temp SET sampler = %1 WHERE album = '%2' AND sampler IS NULL;" )
4385                          .arg(artists.count() > dirs.count() ? boolT() : boolF()).arg( album_id ) );
4386     }
4387 }
4388
4389 void
4390 CollectionDB::setCompilation( const KUrl::List &urls, bool enabled, bool updateView )
4391 {
4392     Q_UNUSED( updateView );
4393     for ( KUrl::List::const_iterator it = urls.begin(); it != urls.end(); ++it )
4394     {
4395         QString url( ( *it ).path() );
4396
4397         int deviceid = MountPointManager::instance()->getIdForUrl( url );
4398         QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4399
4400         query( QString( "UPDATE tags SET sampler = %1 WHERE tags.url = '%2' AND tags.deviceid = %3;" )
4401                 .arg( ( enabled ? boolT() : boolF() ), escapeString( rpath ), QString::number( deviceid ) ) );
4402     }
4403
4404     // Update the Collection-Browser view,
4405     // using QTimer to make sure we don't manipulate the GUI from a thread
4406
4407 //<PORT> 2.0
4408 //    if ( updateView )
4409 //        QTimer::singleShot( 0, CollectionView::instance(), SLOT( renderView() ) );
4410 //</PORT>
4411 }
4412
4413
4414 void
4415 CollectionDB::removeDirFromCollection( QString path )
4416 {
4417     //if ( path.endsWith( "/" ) )
4418     //    path = path.left( path.length() - 1 );
4419     int deviceid = MountPointManager::instance()->getIdForUrl( path );
4420     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4421
4422     query( QString( "DELETE FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4423                     .arg( deviceid )
4424                     .arg( escapeString( rpath ) ) );
4425 }
4426
4427
4428 QString
4429 CollectionDB::IDFromExactValue( QString table, QString value, bool autocreate, bool temporary /* = false */ )
4430 {
4431     if ( temporary )
4432     {
4433             table.append( "_temp" );
4434     }
4435
4436     QString querystr( QString( "SELECT id FROM %1 WHERE name " ).arg( table ) );
4437     querystr += exactCondition( value ) + ';';
4438     QStringList result = query( querystr );
4439     if ( result.isEmpty() )
4440     {
4441         if ( autocreate )
4442             return QString::number( insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
4443                  .arg( table, escapeString( value ) ),
4444                  table ) );
4445         else
4446             return 0;
4447     }
4448     else
4449     {
4450         if ( result.count() > 1 )
4451             debug() << "More than one entry in the " << table << " database for '" << value << '\'';
4452         return result.first();
4453     }
4454 }
4455
4456 void
4457 CollectionDB::deleteRedundantName( const QString &table, const QString &id )
4458 {
4459     QString querystr( QString( "SELECT %1 FROM tags WHERE tags.%1 = %2 LIMIT 1;" ).arg( table, id ) );
4460     QStringList result = query( querystr );
4461     if ( result.isEmpty() )
4462         query( QString( "DELETE FROM %1 WHERE id = %2;" ).arg( table,id ) );
4463 }
4464
4465 void
4466 CollectionDB::deleteAllRedundant( const QString &table )
4467 {
4468     //This works with MySQL4. I thought it might not do, due to the comment in copyTempTables
4469     query( QString( "DELETE FROM %1 WHERE id NOT IN ( SELECT %2 FROM tags )" ).arg( table, table ) );
4470 }
4471
4472
4473 void
4474 CollectionDB::updateTags( const QString &url, const MetaBundle &bundle, const bool updateView )
4475 {
4476     Q_UNUSED( updateView );
4477     DEBUG_BLOCK
4478     QueryBuilder qb;
4479     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
4480     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
4481     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
4482     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
4483     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
4484     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
4485     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
4486     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
4487     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
4488     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
4489     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
4490     // [10] is above. [11] is below.
4491     qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
4492     qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valID );
4493     qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valID );
4494     qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valID );
4495     qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valID );
4496     qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valID );
4497
4498     qb.addUrlFilters ( QStringList( url ) );
4499     qb.setOptions( QueryBuilder::optRemoveDuplicates );
4500     QStringList values = qb.run();
4501
4502     if ( values.count() > 17 )
4503     {
4504         error() << "Query returned more than 1 song. Aborting updating metadata";
4505         return;
4506     }
4507
4508     if ( !values.isEmpty() )
4509     {
4510         bool art=false, comp=false, alb=false, gen=false, year=false;
4511
4512         QString command = "UPDATE tags SET ";
4513         if ( values[ 0 ] != bundle.title() )
4514             command += "title = '" + escapeString( bundle.title() ) + "', ";
4515         if ( values[ 1 ] != bundle.artist() )
4516         {
4517             art = true;
4518             command += "artist = " + IDFromExactValue( "artist", bundle.artist() ) + ", ";
4519         }
4520         if ( values[ 2 ] != bundle.composer() )
4521         {
4522             comp = true;
4523             command += "composer = " + IDFromExactValue( "composer", bundle.composer() ) + ", ";
4524         }
4525         if ( values[ 3 ] != bundle.album() )
4526         {
4527             alb = true;
4528             command += "album = "  + IDFromExactValue( "album", bundle.album() ) + ", ";
4529         }
4530         if ( values[ 4 ] != bundle.genre() )
4531         {
4532             gen = true;
4533             command += "genre = "  + IDFromExactValue( "genre", bundle.genre() ) + ", ";
4534         }
4535         if ( values[ 5 ] != QString::number( bundle.year() ) )
4536         {
4537             year = false;
4538             command += "year = "   + IDFromExactValue( "year", QString::number( bundle.year() ) ) + ", ";
4539         }
4540         if ( values[ 6 ] != QString::number( bundle.track() ) )
4541             command += "track = " + QString::number( bundle.track() ) + ", ";
4542         if ( values[ 7 ] != bundle.comment() )
4543             command += "comment = '" + escapeString( bundle.comment() ) + "', ";
4544         if ( values[ 8 ] != QString::number( bundle.discNumber() ) )
4545             command += "discnumber = '" + QString::number( bundle.discNumber() ) + "', ";
4546         if ( values[ 9 ] != QString::number( bundle.filesize() ) )
4547             command += "filesize = '" + QString::number( bundle.filesize() ) + "', ";
4548         if ( values[ 10 ] != QString::number( bundle.fileType() ) )
4549             command += "filetype = '" + QString::number( bundle.fileType() ) + "', ";
4550         if ( values[ 11 ] != QString::number( bundle.bpm() ) )
4551             command += "bpm = '" + QString::number( bundle.bpm() ) + "', ";
4552
4553         if ( "UPDATE tags SET " == command )
4554         {
4555             debug() << "No tags selected to be changed";
4556         }
4557         else
4558         {
4559             int deviceid = MountPointManager::instance()->getIdForUrl( url );
4560             QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4561             //We have to remove the trailing comma from command
4562             query( command.left( command.length() - 2 ) + " WHERE url = '" + escapeString( rpath ) +
4563                     "' AND deviceid = " + QString::number( deviceid ) + ';' );
4564         }
4565
4566         //Check to see if we use the entry anymore. If not, delete it
4567         if ( art )
4568             deleteRedundantName( "artist", values[ 12 ] );
4569         if ( comp )
4570             deleteRedundantName( "composer", values[ 13 ] );
4571         if ( alb )
4572             deleteRedundantName( "album", values[ 14 ] );
4573         if ( gen )
4574             deleteRedundantName( "genre", values[ 15 ] );
4575         if ( year )
4576             deleteRedundantName( "year", values[ 16 ] );
4577
4578         // Update the Collection-Browser view,
4579         // using QTimer to make sure we don't manipulate the GUI from a thread
4580         //<PORT> 2.0
4581         //if ( updateView )
4582         //    QTimer::singleShot( 0, CollectionView::instance(), SLOT( databaseChanged() ) );
4583         //</PORT>
4584
4585         if( art || alb )
4586             emit tagsChanged( values[12], values[14] );
4587     }
4588
4589     //this method is totally useless now, so i'm not going to fix this
4590     /*if ( EngineController::instance()->bundle().url() == bundle.url() )
4591     {
4592         debug() << "Current song edited, updating widgets: " << bundle.title();
4593         EngineController::instance()->currentTrackMetaDataChanged( bundle );
4594     }*/
4595
4596     emit tagsChanged( bundle );
4597 }
4598
4599
4600 void
4601 CollectionDB::updateURL( const QString &url, const bool updateView )
4602 {
4603     // don't use the KUrl ctor as it checks the db first
4604     MetaBundle bundle;
4605     bundle.setPath( url );
4606     bundle.readTags( TagLib::AudioProperties::Fast );
4607
4608     updateTags( url, bundle, updateView);
4609     doAFTStuff( &bundle, false );
4610 }
4611
4612 QString
4613 CollectionDB::getUniqueId( const QString &url )
4614 {
4615     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4616     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4617     QStringList values = query( QString( "SELECT uniqueid FROM uniqueid WHERE deviceid = %1 AND url = '%2';" )
4618                                 .arg( deviceid )
4619                                 .arg( escapeString( rpath ) ));
4620     if( !values.empty() )
4621         return values[0];
4622     else
4623         return QString();
4624 }
4625
4626 void
4627 CollectionDB::setLyrics( const QString &url, const QString &lyrics, const QString &uniqueid )
4628 {
4629     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4630     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4631
4632     QStringList values = query(QString("SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;")
4633                     .arg( deviceid ).arg( escapeString( rpath ) ) );
4634     if(values.count() > 0)
4635     {
4636         if ( !lyrics.isEmpty() )
4637             query( QString( "UPDATE lyrics SET lyrics = '%1' WHERE url = '%3' AND deviceid = %2;" )
4638                     .arg( escapeString( lyrics), QString::number(deviceid), escapeString( rpath ) ) );
4639         else
4640             query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4641                     .arg( deviceid).arg( escapeString( rpath ) ) );
4642     }
4643     else
4644     {
4645         insert( QString( "INSERT INTO lyrics (deviceid, url, lyrics, uniqueid) values ( %1, '%2', '%3', '%4' );" )
4646                 .arg( QString::number(deviceid), escapeString( rpath ), escapeString( lyrics ), escapeString( uniqueid ) ), NULL);
4647     }
4648 }
4649
4650
4651 QString
4652 CollectionDB::getLyrics( const QString &url )
4653 {
4654     int deviceid = MountPointManager::instance()->getIdForUrl( url );
4655     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4656     QStringList values = query( QString( "SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4657                 .arg( deviceid ).arg( escapeString( rpath ) ) );
4658     if ( !values.isEmpty() )
4659         return values[0];
4660     else
4661         return QString();
4662 }
4663
4664 void CollectionDB::removeInvalidAmazonInfo( const QString& md5sum )
4665 {
4666     query( QString( "DELETE FROM amazon WHERE filename='%1'" ).arg( md5sum ) );
4667 }
4668
4669 void CollectionDB::newAmazonReloadDate( const QString& asin, const QString& locale, const QString& md5sum)
4670 {
4671     QStringList values = query(QString("SELECT filename FROM amazon WHERE filename = '%1'")
4672         .arg(md5sum));
4673     if(values.count() > 0)
4674     {
4675         query( QString("UPDATE amazon SET asin = '%1', locale = '%2', refetchdate = '%3' WHERE filename = '%4'")
4676             .arg(asin)
4677             .arg(locale)
4678             .arg(QDateTime::currentDateTime().addDays(80).toTime_t())
4679             .arg(md5sum));
4680     }
4681     else
4682     {
4683         insert( QString( "INSERT INTO amazon ( asin, locale, filename, refetchdate ) VALUES ( '%1', '%2', '%3', '%4');" )
4684          .arg(asin)
4685          .arg(locale)
4686          .arg(md5sum)
4687          .arg(QDateTime::currentDateTime().addDays(80).toTime_t()), NULL );
4688     }
4689 }
4690
4691 QStringList CollectionDB::staleImages()
4692 {
4693     return query(QString("SELECT asin, locale, filename FROM amazon WHERE refetchdate < %1 ;")
4694             .arg(QDateTime::currentDateTime().toTime_t() ));
4695 }
4696
4697 void
4698 CollectionDB::applySettings()
4699 {
4700     bool recreateConnections = false;
4701     if ( AmarokConfig::databaseEngine().toInt() != getDbConnectionType() )
4702     {
4703         if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4704             m_dbConnType = DbConnection::mysql;
4705         else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4706             m_dbConnType = DbConnection::postgresql;
4707         else m_dbConnType = DbConnection::sqlite;
4708         recreateConnections = true;
4709     }
4710     else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4711     {
4712         // Using MySQL, so check if MySQL settings were changed
4713         const MySqlConfig *config =
4714             static_cast<const MySqlConfig*> ( m_dbConfig );
4715         if ( AmarokConfig::mySqlHost() != config->host() )
4716         {
4717             recreateConnections = true;
4718         }
4719         else if ( AmarokConfig::mySqlPort() != config->port() )
4720         {
4721             recreateConnections = true;
4722         }
4723         else if ( AmarokConfig::mySqlDbName() != config->database() )
4724         {
4725             recreateConnections = true;
4726         }
4727         else if ( AmarokConfig::mySqlUser() != config->username() )
4728         {
4729             recreateConnections = true;
4730         }
4731         else if ( AmarokConfig::mySqlPassword() != config->password() )
4732         {
4733             recreateConnections = true;
4734         }
4735     }
4736     else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4737     {
4738       const PostgresqlConfig *config =
4739           static_cast<const PostgresqlConfig*> ( m_dbConfig );
4740         if ( AmarokConfig::postgresqlHost() != config->host() )
4741         {
4742             recreateConnections = true;
4743         }
4744         else if ( AmarokConfig::postgresqlPort() != config->port() )
4745         {
4746             recreateConnections = true;
4747         }
4748         else if ( AmarokConfig::postgresqlDbName() != config->database() )
4749         {
4750             recreateConnections = true;
4751         }
4752         else if ( AmarokConfig::postgresqlUser() != config->username() )
4753         {
4754             recreateConnections = true;
4755         }
4756         else if ( AmarokConfig::postgresqlPassword() != config->password() )
4757       {
4758           recreateConnections = true;
4759       }
4760     }
4761
4762     if ( recreateConnections )
4763     {
4764         debug()
4765             << "Database engine settings changed: "
4766             << "recreating DbConnections";
4767         // If Database engine was changed, recreate DbConnections.
4768         destroy();
4769         initialize();
4770 //PORT 2.0   CollectionView::instance()->renderView();
4771 //         PlaylistBrowser::instance()->loadPodcastsFromDatabase();
4772
4773         emit databaseEngineChanged();
4774     }
4775 }
4776
4777 DbConnection * CollectionDB::getMyConnection()
4778 {
4779     //after some thought, to be thread-safe, must lock at the beginning of this function,
4780     //not only if a new connection is made
4781     connectionMutex->lock();
4782
4783     DbConnection *dbConn;
4784     QThread *currThread = QThread::currentThread();
4785
4786     if( threadConnections->contains(currThread) )
4787     {
4788         QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4789         dbConn = it.value();
4790         connectionMutex->unlock();
4791         return dbConn;
4792     }
4793
4794 #ifdef USE_MYSQL
4795     if( m_dbConnType == DbConnection::mysql )
4796     {
4797         dbConn = new MySqlConnection( static_cast<MySqlConfig*>( m_dbConfig ) );
4798     }
4799     else
4800 #endif
4801 #ifdef USE_POSTGRESQL
4802     if( m_dbConnType == DbConnection::postgresql )
4803     {
4804         dbConn = new PostgresqlConnection( static_cast<PostgresqlConfig*>( m_dbConfig ) );
4805     }
4806     else
4807 #endif
4808     {
4809         dbConn = new SqliteConnection( static_cast<SqliteConfig*>( m_dbConfig ) );
4810     }
4811
4812     threadConnections->insert(currThread, dbConn);
4813
4814     connectionMutex->unlock();
4815     return dbConn;
4816 }
4817
4818
4819 void
4820 CollectionDB::releasePreviousConnection(QThread *currThread)
4821 {
4822     //if something already exists, delete the object, and erase knowledge of it from the QMap.
4823     connectionMutex->lock();
4824     DbConnection *dbConn;
4825     if (threadConnections->contains(currThread))
4826     {
4827         QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4828         dbConn = it.value();
4829         delete dbConn;
4830         threadConnections->remove(currThread);
4831     }
4832     connectionMutex->unlock();
4833 }
4834
4835 bool
4836 CollectionDB::isConnected()
4837 {
4838     return getMyConnection()->isConnected();
4839 }
4840
4841 //////////////////////////////////////////////////////////////////////////////////////////
4842 // PROTECTED
4843 //////////////////////////////////////////////////////////////////////////////////////////
4844
4845 QByteArray
4846 CollectionDB::md5sum( const QString& artist, const QString& album, const QString& file )
4847 {
4848     KMD5 context( artist.toLower().toLocal8Bit() + album.toLower().toLocal8Bit() + file.toLocal8Bit() );
4849 //     debug() << "MD5 SUM for " << artist << ", " << album << ": " << context.hexDigest();
4850     return context.hexDigest();
4851 }
4852
4853 void
4854 CollectionDB::timerEvent( QTimerEvent* )
4855 {
4856     scanMonitor();
4857 }
4858
4859
4860 //////////////////////////////////////////////////////////////////////////////////////////
4861 // PUBLIC SLOTS
4862 //////////////////////////////////////////////////////////////////////////////////////////
4863
4864 // void
4865 // CollectionDB::fetchCover( QWidget* parent, const QString& artist, const QString& album, bool noedit, Q3ListViewItem* item ) //SLOT
4866 // {
4867 //     debug() << "Fetching cover for " << artist << " - " << album;
4868 //
4869 //     const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
4870 //     CoverFetcher* fetcher;
4871 //     if( isCompilation )
4872 //         // avoid putting various artists in front of album title. this causes problems for locales other than US.
4873 //         fetcher = new CoverFetcher( parent, "", album );
4874 //     else
4875 //         fetcher = new CoverFetcher( parent, artist, album );
4876 //     if( item )
4877 //     {
4878 //         itemCoverMapMutex->lock();
4879 //         itemCoverMap->insert( item, fetcher );
4880 //         itemCoverMapMutex->unlock();
4881 //     }
4882 //     connect( fetcher, SIGNAL(result( CoverFetcher* )), SLOT(coverFetcherResult( CoverFetcher* )) );
4883 //     fetcher->setUserCanEditQuery( !noedit );
4884 //     fetcher->startFetch();
4885 // }
4886
4887 void
4888 CollectionDB::scanMonitor()  //SLOT
4889 {
4890     if ( AmarokConfig::monitorChanges() )
4891         scanModifiedDirs();
4892 }
4893
4894
4895 void
4896 CollectionDB::startScan()  //SLOT
4897 {
4898     QStringList folders = MountPointManager::instance()->collectionFolders();
4899
4900     if ( folders.isEmpty() )
4901     {
4902         //dropTables( false );
4903         //createTables( false );
4904         clearTables( false );
4905         emit scanDone( true );
4906     }
4907     //Port 2.0
4908     else /*if( PlaylistBrowser::instance() )*/
4909     {
4910         emit scanStarted();
4911         CollectionManager::instance()->startFullScan();
4912     }
4913 }
4914
4915
4916 void
4917 CollectionDB::stopScan() //SLOT
4918 {
4919     ThreadManager::instance()->abortAllJobsNamed( "CollectionScanner" );
4920 }
4921
4922
4923 //////////////////////////////////////////////////////////////////////////////////////////
4924 // PRIVATE SLOTS
4925 //////////////////////////////////////////////////////////////////////////////////////////
4926
4927 void
4928 CollectionDB::dirDirty( const QString& path )
4929 {
4930     debug() << "Dirty: " << path;
4931     QStringList dir;
4932     dir.append( path );
4933
4934     CollectionManager::instance()->startFullScan();
4935 }
4936
4937
4938 // void
4939 // CollectionDB::coverFetcherResult( CoverFetcher *fetcher )
4940 // {
4941 //     if( fetcher->wasError() ) {
4942 //         error() << fetcher->errors();
4943 //         emit coverFetcherError( fetcher->errors().front() );
4944 //     }
4945 //
4946 //     else {
4947 //         setAlbumImage( fetcher->artist(), fetcher->album(), fetcher->image(), fetcher->amazonURL(), fetcher->asin() );
4948 //         emit coverFetched( fetcher->artist(), fetcher->album() );
4949 //     }
4950 //
4951 //     //check the validity of the CollectionItem as it may have been deleted e.g. by a
4952 //     //collection scan while fetching the cover
4953 // /* PORT 2.0    itemCoverMapMutex->lock();
4954 //     QMap<Q3ListViewItem*, CoverFetcher*>::Iterator it;
4955 //     for( it = itemCoverMap->begin(); it != itemCoverMap->end(); ++it )
4956 //     {
4957 //         if( it.data() == fetcher )
4958 //         {
4959 //             if( it.key()->isOpen() )
4960 //                 static_cast<CollectionItem*>(it.key())->setPixmap( 0, QPixmap() );
4961 //             itemCoverMap->erase( it );
4962 //         }
4963 //     } */
4964 //     itemCoverMapMutex->unlock();
4965 // }
4966
4967 /**
4968  * This query is fairly slow with sqlite, and often happens just
4969  * after the OSD is shown. Threading it restores responsivity.
4970  */
4971 class SimilarArtistsInsertionJob : public ThreadManager::DependentJob
4972 {
4973     virtual bool doJob()
4974     {
4975         CollectionDB::instance()->query( QString( "DELETE FROM related_artists WHERE artist = '%1';" ).arg( escapedArtist ) );
4976
4977         const QString sql = "INSERT INTO related_artists ( artist, suggestion, changedate ) VALUES ( '%1', '%2', 0 );";
4978         oldForeach( suggestions )
4979             CollectionDB::instance()->insert( sql
4980                     .arg( escapedArtist,
4981                           CollectionDB::instance()->escapeString( *it ) ), NULL);
4982
4983         return true;
4984     }
4985
4986     virtual void completeJob() { emit CollectionDB::instance()->similarArtistsFetched( artist ); }
4987
4988     const QString artist;
4989     const QString escapedArtist;
4990     const QStringList suggestions;
4991
4992 public:
4993     SimilarArtistsInsertionJob( CollectionDB *parent, const QString &s, const QStringList &list )
4994             : ThreadManager::DependentJob( parent, "SimilarArtistsInsertionJob" )
4995             , artist( s )
4996             , escapedArtist( parent->escapeString( s ) )
4997             , suggestions( list )
4998     {}
4999 };
5000
5001 void
5002 CollectionDB::similarArtistsFetched( const QString& artist, const QStringList& suggestions )
5003 {
5004     debug() << "Received similar artists\n";
5005
5006     ThreadManager::instance()->queueJob( new SimilarArtistsInsertionJob( this, artist, suggestions ) );
5007 }
5008
5009 void
5010 CollectionDB::aftCheckPermanentTables( const QString &currdeviceid, const QString &currid, const QString &currurl )
5011 {
5012     //DEBUG_BLOCK
5013     //debug() << "deviceid = " << currdeviceid << endl << "url = " << currurl << endl << "uid = " << currid;
5014
5015     QStringList check1, check2;
5016
5017     oldForeach( m_aftEnabledPersistentTables )
5018     {
5019         //debug() << "Checking " << (*it);;
5020         check1 = query( QString(
5021                 "SELECT url, deviceid "
5022                 "FROM %1 "
5023                 "WHERE uniqueid = '%2';" )
5024                     .arg( escapeString( *it ) )
5025                     .arg( currid ) );
5026
5027         check2 = query( QString(
5028                 "SELECT url, uniqueid "
5029                 "FROM %1 "
5030                 "WHERE deviceid = %2 AND url = '%3';" )
5031                     .arg( escapeString( *it ) )
5032                     .arg( currdeviceid
5033                     , currurl ) );
5034
5035         if( !check1.empty() )
5036         {
5037             //debug() << "uniqueid found, updating url";
5038             query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5039                                 .arg( escapeString( *it ) )
5040                                 .arg( currdeviceid
5041                                 , currid
5042                                 , currurl ) );
5043         }
5044         else if( !check2.empty() )
5045         {
5046             //debug() << "url found, updating uniqueid";
5047             query( QString( "UPDATE %1 SET uniqueid = '%2' WHERE deviceid = %3 AND url = '%4';" )
5048                                 .arg( escapeString( *it ) )
5049                                 .arg( currid
5050                                 , currdeviceid
5051                                 , currurl ) );
5052         }
5053     }
5054 }
5055
5056 void
5057 CollectionDB::aftMigratePermanentTablesUrl( const QString& /*oldUrl*/, const QString& newUrl, const QString& uniqueid )
5058 {
5059     //DEBUG_BLOCK
5060     int deviceid = MountPointManager::instance()->getIdForUrl( newUrl );
5061     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, newUrl );
5062     //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5063     //below; will need special case.
5064     //debug() << "deviceid = " << deviceid << endl << "newurl = " << newUrl << endl << "uid = " << uniqueid;
5065     oldForeach( m_aftEnabledPersistentTables )
5066     {
5067         query( QString( "DELETE FROM %1 WHERE deviceid = %2 AND url = '%3';" )
5068                                 .arg( escapeString( *it ) )
5069                                 .arg( deviceid )
5070                                 .arg( escapeString( rpath ) ) );
5071         query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5072                                 .arg( escapeString( *it ) )
5073                                 .arg( deviceid )
5074                                 .arg( escapeString( uniqueid ) )
5075                                 .arg( escapeString( rpath ) ) );
5076     }
5077 }
5078
5079 void
5080 CollectionDB::aftMigratePermanentTablesUniqueId( const QString& /*url*/, const QString& oldid, const QString& newid )
5081 {
5082     //DEBUG_BLOCK
5083     //debug() << "oldid = " << oldid << endl << "newid = " << newid;
5084     //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5085     //below; will need special case.
5086     oldForeach( m_aftEnabledPersistentTables )
5087     {
5088         query( QString( "DELETE FROM %1 WHERE uniqueid = '%2';" )
5089                                 .arg( escapeString( *it ) )
5090                                 .arg( escapeString( newid ) ) );
5091         query( QString( "UPDATE %1 SET uniqueid = '%1' WHERE uniqueid = '%2';" )
5092                                 .arg( escapeString( *it ) )
5093                                 .arg( escapeString( newid ) )
5094                                 .arg( escapeString( oldid ) ) );
5095     }
5096 }
5097
5098
5099 //////////////////////////////////////////////////////////////////////////////////////////
5100 // PRIVATE
5101 //////////////////////////////////////////////////////////////////////////////////////////
5102
5103 void
5104 CollectionDB::initialize()
5105 {
5106     DEBUG_BLOCK
5107
5108     /// Create DBConfig instance:
5109
5110 #ifdef USE_MYSQL
5111     if ( m_dbConnType == DbConnection::mysql )
5112     {
5113         QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5114         QString passwd = AmarokConfig::mySqlPassword2(); // stored as string type
5115
5116         if( passwd.isEmpty() )
5117         {
5118             if( appVersion.startsWith( "1.3" ) )
5119             {
5120                 /// This is because of the encrypted -> plaintext conversion
5121                 passwd = AmarokConfig::mySqlPassword(); // stored as password type
5122                 AmarokConfig::setMySqlPassword2( passwd );
5123             }
5124             else if( appVersion.startsWith( "1.4" ) )
5125             {
5126                 passwd = Amarok::config( "MySql" ).readEntry( "MySqlPassword" ); //read the field as plaintext
5127                 AmarokConfig::setMySqlPassword2( passwd ); // store it in plaintext field
5128             }
5129         }
5130
5131         m_dbConfig = new MySqlConfig(
5132                     AmarokConfig::mySqlHost(),
5133                     AmarokConfig::mySqlPort(),
5134                     AmarokConfig::mySqlDbName(),
5135                     AmarokConfig::mySqlUser(),
5136                     passwd );
5137     }
5138     else
5139 #endif
5140 #ifdef USE_POSTGRESQL
5141     if ( m_dbConnType == DbConnection::postgresql )
5142     {
5143         QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5144         QString passwd = AmarokConfig::postgresqlPassword2();
5145
5146         if( passwd.isEmpty() )
5147         {
5148             if( appVersion.startsWith( "1.3" ) )
5149             {
5150                 /// This is because of the encrypted -> plaintext conversion
5151                 passwd = AmarokConfig::postgresqlPassword(); // stored as password type
5152                 AmarokConfig::setPostgresqlPassword2( passwd );
5153             }
5154             else if( appVersion.startsWith( "1.4" ) &&
5155                    ( appVersion.contains( "beta", false ) ||
5156                      appVersion.contains( "svn",  false ) ) )
5157             {
5158                 passwd = Amarok::config( "Postgresql" ).readEntry( "PostgresqlPassword" );
5159                 AmarokConfig::setPostgresqlPassword2( passwd );
5160             }
5161         }
5162
5163         m_dbConfig = new PostgresqlConfig(
5164                     AmarokConfig::postgresqlHost(),
5165                     AmarokConfig::postgresqlPort(),
5166                     AmarokConfig::postgresqlDbName(),
5167                     AmarokConfig::postgresqlUser(),
5168                     passwd );
5169     }
5170     else
5171 #endif
5172     {
5173         m_dbConfig = new SqliteConfig(
5174                         Amarok::config( "Sqlite" ).readEntry( "location",
5175                                         Amarok::saveLocation() + "collection.db" ) );
5176     }
5177
5178     DbConnection *dbConn = getMyConnection();
5179
5180     if ( !dbConn->isConnected() || !dbConn->isInitialized() )
5181     {
5182         error() << "Failed to connect to or initialise database!";
5183         Amarok::MessageQueue::instance()->addMessage( dbConn->lastError() );
5184     }
5185     else
5186     {
5187         if ( !isValid() )
5188         {
5189             //No tables seem to exist (as doing a count(url) didn't even return any number, even 0).
5190             warning() << "Tables seem to not exist.";
5191             warning() << "Attempting to create tables (this should be safe; ignore any errors)...";
5192             createTables(false);
5193             createPersistentTables();
5194             createPodcastTables();
5195             createStatsTable();
5196             warning() << "Tables should now definitely exist. (Stop ignoring errors)";
5197
5198             //Since we have created the tables, we need to make sure the version numbers are
5199             //set to the correct values. If this is not done now, the database update code may
5200             //run, which could corrupt things.
5201             Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5202             Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5203             Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5204             Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION );    Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5205
5206             setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5207             setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5208             setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5209             setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5210             setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5211         }
5212
5213
5214         // Due to a bug in our sqllite handling code, we have to recreate the indices.
5215         // We should rmeove this before 1.4.5
5216         if ( m_dbConnType == DbConnection::sqlite ) {
5217             QStringList indices = query( "SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;" );
5218             if (!indices.contains("url_tag")) {
5219                 createIndices();
5220             }
5221         }
5222
5223
5224         //updates for the Devices table go here
5225         //put all other update code into checkDatabase()
5226         //make sure that there is no call to MountPointManager in CollectionDB's ctor
5227         //or in methods called from the ctor.
5228         if ( adminValue( "Database Devices Version" ).isEmpty()
5229              && Amarok::config( "CollectionBrowser" ).readEntry( "Database Devices Version", 0 ) == 0 )
5230         {
5231             createDevicesTable();
5232         }
5233         else if ( adminValue( "Database Devices Version" ).toInt() != DATABASE_DEVICES_VERSION
5234               || Amarok::config( "Collection Browser" ).readEntry( "Database Devices Version", 0 ) != DATABASE_DEVICES_VERSION )
5235         {
5236             int prev = adminValue( "Database Devices Version" ).toInt();
5237
5238             if ( prev > DATABASE_DEVICES_VERSION || prev < 0 )
5239             {
5240                 error() << "Database devices version too new for this version of Amarok";
5241                 exit( 1 );
5242                 //dropDevicesTable();
5243             }
5244             else
5245             {
5246                 debug() << "Updating DEVICES table";
5247                 //add future Devices update code here
5248             }
5249         }
5250         Amarok::config( "Collection Browser" ).writeEntry( "Database Devices Version", DATABASE_DEVICES_VERSION );
5251         setAdminValue( "Database Devices Version", QString::number( DATABASE_DEVICES_VERSION ) );
5252
5253         //make sure that all indices exist
5254         createIndices();
5255         createPermanentIndices();
5256     }
5257
5258 }
5259
5260 void
5261 CollectionDB::checkDatabase()
5262 {
5263     DEBUG_BLOCK
5264     if ( isValid() )
5265     {
5266         //Inform the user that he should attach as many devices with music as possible
5267         //Hopefully this won't be necessary soon.
5268         //
5269         //Currently broken, so disabled - seems to cause crashes as events are sent to
5270         //the Playlist - maybe it's not fully initialised?
5271         /*
5272         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." );
5273         int result = KMessageBox::warningContinueCancel( 0, text, "Database migration" );
5274         if ( result != KMessageBox::Continue )
5275         {
5276             error() << "Dynamic Collection migration was aborted by user...exiting";
5277             exit( 1 );
5278         }
5279         */
5280
5281         bool needsUpdate = ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5282                            || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION
5283                            || Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5284                            || adminValue( "Database Version" ).toInt() != DATABASE_VERSION
5285                            || Amarok::config( "Collection Browser" ).readEntry( "Database Persistent Tables Version", 0 ) != DATABASE_PERSISTENT_TABLES_VERSION
5286                            || adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION
5287                            || Amarok::config( "Collection Browser" ).readEntry( "Database Podcast Tables Version", 0 ) != DATABASE_PODCAST_TABLES_VERSION
5288                            || adminValue( "Database Podcast Tables Version" ).toInt() != DATABASE_PODCAST_TABLES_VERSION
5289                            || Amarok::config( "Collection Browser" ).readEntry( "Database AFT Version", 0 ) != DATABASE_AFT_VERSION
5290                            || adminValue( "Database AFT Version" ).toInt() != DATABASE_AFT_VERSION );
5291
5292         if ( needsUpdate )
5293         {
5294
5295             KDialog *dialog = new KDialog( 0 );
5296             dialog->setCaption( i18n( "Updating database" ) );
5297             dialog->setModal( false );
5298             dialog->showButtonSeparator( false );
5299
5300
5301             /* TODO: remove the standard window controls from the dialog window, the user should not be able
5302                      to close, minimize, maximize the dialog
5303                      add additional text, e.g. Amarok is currently updating your database. This may take a while.
5304                      Please wait.
5305
5306                      Consider using a ProgressBarDialog
5307             */
5308             QLabel *label = new QLabel( i18n( "Updating database" ), dialog );
5309             dialog->setMainWidget( label );
5310             label->show();
5311             QTimer::singleShot( 0, dialog, SLOT( show() ) );
5312             //process events in the main event loop so that the dialog actually gets shown
5313             kapp->processEvents();
5314             debug() << "Beginning database update";
5315
5316             updateStatsTables();
5317
5318             updatePersistentTables();
5319
5320             updatePodcastTables();
5321
5322             //remove database file if version is incompatible
5323             if ( Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5324                  || adminValue( "Database Version" ).toInt() != DATABASE_VERSION )
5325             {
5326                 debug() << "Rebuilding database!";
5327                 dropTables(false);
5328                 createTables(false);
5329             }
5330             delete dialog;
5331         }
5332         emit databaseUpdateDone();
5333     }
5334
5335     // TODO Should write to config in dtor, but it crashes...
5336     Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5337     Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5338     Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5339     Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION );
5340     Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5341
5342     setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5343     setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5344     setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5345     setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5346     setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5347
5348     initDirOperations();
5349 }
5350
5351 void
5352 CollectionDB::updateStatsTables()
5353 {
5354     if ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5355           || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION )
5356     {
5357         debug() << "Different database stats version detected! Stats table will be updated or rebuilt.";
5358
5359             #if 0 // causes mysterious crashes
5360             if( getType() == DbConnection::sqlite && QFile::exists( Amarok::saveLocation()+"collection.db" ) )
5361             {
5362                 debug() << "Creating a backup of the database in "
5363                         << Amarok::saveLocation()+"collection-backup.db" << '.';
5364
5365                 bool copied = KIO::NetAccess::file_copy( Amarok::saveLocation()+"collection.db",
5366                                                          Amarok::saveLocation()+"collection-backup.db",
5367                                                          -1 /*perms*/, true /*overwrite*/, false /*resume*/ );
5368
5369                 if( !copied )
5370                 {
5371                     debug() << "Backup failed! Perhaps the volume is not writable.";
5372                     debug() << "Error was: " << KIO::NetAccess::lastErrorString();
5373                 }
5374             }
5375             #endif
5376
5377         int prev = adminValue( "Database Stats Version" ).toInt();
5378
5379         /* If config returns 3 or lower, it came from an Amarok version that was not aware of
5380            admin table, so we can't trust this table at all */
5381         if( !prev || ( Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 )
5382                   && Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) <= 3  ) )
5383             prev = Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 );
5384
5385         //pre somewhere in the 1.3-1.4 timeframe, the version wasn't stored in the DB, so try to guess it
5386         const QString q = "SELECT COUNT( %1 ) FROM statistics;";
5387         if( !prev && query( q.arg( "url" ) ).first().toInt()
5388                   && query( q.arg( "createdate" ) ).first().toInt()
5389                   && query( q.arg( "accessdate" ) ).first().toInt()
5390                   && query( q.arg( "percentage" ) ).first().toInt()
5391                   && query( q.arg( "playcounter" ) ).first().toInt() )
5392         {
5393             prev = 3;
5394         }
5395
5396         if ( prev < 3 ) //it is from before 1.2, or our poor user is otherwise fucked
5397         {
5398             debug() << "Rebuilding stats-database!";
5399             dropStatsTableV1();
5400             createStatsTable();
5401         }
5402         else        //Incrementally update the stats table to reach the present version
5403         {
5404             if( prev < 4 ) //every version from 1.2 forward had a stats version of 3
5405             {
5406                 debug() << "Updating stats-database!";
5407                 query( "ALTER TABLE statistics ADD rating INTEGER DEFAULT 0;" );
5408                 query( "CREATE INDEX rating_stats ON statistics( rating );" );
5409                 query( "UPDATE statistics SET rating=0 WHERE " + boolT() + ';' );
5410             }
5411             if( prev < 5 )
5412             {
5413                 debug() << "Updating stats-database!";
5414                 query( "UPDATE statistics SET rating = rating * 2;" );
5415             }
5416             if( prev < 8 )  //Versions 6, 7 and 8 all were all attempts to add columns for ATF. his code should do it all.
5417             {
5418                 query( QString( "CREATE TABLE statistics_fix ("
5419                     "url " + textColumnType() + " UNIQUE,"
5420                     "createdate INTEGER,"
5421                     "accessdate INTEGER,"
5422                     "percentage FLOAT,"
5423                     "rating INTEGER DEFAULT 0,"
5424                     "playcounter INTEGER);" ) );
5425
5426                 insert( "INSERT INTO statistics_fix (url, createdate, accessdate, percentage, playcounter, rating)"
5427                         "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics;"
5428                         , NULL );
5429
5430                 dropStatsTableV1();
5431                 createStatsTableV8();
5432
5433                 insert( "INSERT INTO statistics (url, createdate, accessdate, percentage, playcounter, rating)"
5434                         "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics_fix;"
5435                         , NULL );
5436                 query( "DROP TABLE statistics_fix" );
5437             }
5438             if( prev < 9 )
5439             {
5440                 //Update for Dynamic Collection:
5441
5442                 //This is not technically for the stats table, but it is part of the same
5443                 //update, so put it here anyway.
5444                 MountPointManager::instance()->setCollectionFolders( Amarok::config( "Collection" ).readEntry( "Collection Folders", QStringList() ) );
5445
5446                 query( "ALTER TABLE statistics ADD deviceid INTEGER;" );
5447
5448                 //FIXME: (max) i know this is bad but its fast
5449                 QStringList oldURLs = query( "SELECT url FROM statistics;" );
5450                 //it might be necessary to use batch updates to improve speed
5451                 debug() << "Updating " << oldURLs.count() << " rows in statistics";
5452                 oldForeach( oldURLs )
5453                 {
5454                     bool exists = QFile::exists( *it );
5455                     int deviceid = exists ? MountPointManager::instance()->getIdForUrl( *it ) : -2;
5456                     QString rpath = exists ? MountPointManager::instance()->getRelativePath( deviceid, *it ) : *it;
5457                     QString update = QString( "UPDATE statistics SET deviceid = %1, url = '%2' WHERE " )
5458                                      .arg( deviceid )
5459                                      .arg( escapeString( rpath ) );
5460                     update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5461                     query ( update );
5462                 }
5463             }
5464             if ( prev < 12 )
5465             {
5466                 //re-using old method cause just a slight change to one column...
5467                 //if people are upgrading from earlier than 11, just get the new column
5468                 //earlier  :-)
5469                 createStatsTableV10( true );
5470                 query( "INSERT INTO statistics_fix_ten SELECT url,deviceid,createdate,"
5471                        "accessdate,percentage,rating,playcounter,uniqueid,deleted FROM "
5472                        "statistics;" );
5473                 dropStatsTableV1();
5474                 createStatsTableV10( false );
5475                 query( "INSERT INTO statistics SELECT * FROM statistics_fix_ten;" );
5476                 query( "UPDATE statistics SET uniqueid=NULL;" );
5477             }
5478             else if( prev > DATABASE_STATS_VERSION )
5479             {
5480                 error() << "Database statistics version too new for this version of Amarok. Quitting...";
5481                 exit( 1 );
5482             }
5483         }
5484     }
5485 }
5486
5487 void
5488 CollectionDB::updatePersistentTables()
5489 {
5490     QString PersistentVersion = adminValue( "Database Persistent Tables Version" );
5491     if ( PersistentVersion.isEmpty() )
5492     {
5493         /* persistent tables didn't have a version on 1.3X and older, but let's be nice and try to
5494            copy/keep the good information instead of just deleting the tables */
5495         debug() << "Detected old schema for tables with important data. Amarok will convert the tables, ignore any \"table already exists\" errors.";
5496         createPersistentTables();
5497         /* Copy lyrics */
5498         debug() << "Trying to get lyrics from old db schema.";
5499
5500         QStringList lyrics = query( "SELECT url, lyrics FROM tags where tags.lyrics IS NOT NULL;" );
5501
5502         for( int i = 0; i < lyrics.count(); i += 2  )
5503             setLyrics( lyrics[i], lyrics[i+1] );
5504
5505         debug() << "Building podcast tables";
5506         createPodcastTables();
5507     }
5508     else if ( PersistentVersion == "1" || PersistentVersion == "2" )
5509     {
5510         createPersistentTables(); /* From 1 to 2 nothing changed. There was just a bug on the code, and
5511                                      on some cases the table wouldn't be created.
5512                                      From 2 to 3, lyrics were made plain text, instead of HTML */
5513         debug() << "Converting Lyrics to Plain Text.";
5514
5515         QStringList lyrics = query( "SELECT url, lyrics FROM lyrics;" );
5516
5517         for( int i=0; i < lyrics.count(); i += 2 )
5518             setLyrics( lyrics[i], lyrics[i+1]  );
5519
5520         debug() << "Building podcast tables";
5521         createPodcastTables();
5522     }
5523     else if ( PersistentVersion.toInt() < 4 )
5524     {
5525         debug() << "Building podcast tables";
5526         createPodcastTables();
5527     }
5528     else
5529     {
5530         if ( PersistentVersion.toInt() < 5 )
5531         {
5532             debug() << "Updating podcast tables";
5533             query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5534             query( "ALTER TABLE podcastepisodes ADD localurl " + textColumnType() + ';' );
5535             query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5536             query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5537             query( "ALTER TABLE podcastepisodes DROP comment;" );
5538             query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5539             query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
5540         }
5541         if ( PersistentVersion.toInt() < 6 )
5542         {
5543             debug() << "Updating podcast tables";
5544             query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5545             query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5546             query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5547             query( "ALTER TABLE podcastepisodes DROP comment;" );
5548             query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5549         }
5550         if ( PersistentVersion.toInt() < 11 )
5551         {
5552             debug() << "This is used to handle problems from uniqueid changeover and should not do anything";
5553         }
5554         if ( PersistentVersion.toInt() < 12 )
5555         {
5556             debug() << "Adding playlists table...";
5557             createPersistentTablesV12();
5558         }
5559         if ( PersistentVersion.toInt() < 13 )
5560         {
5561             //Update for Dynamic Collection:
5562             query( "ALTER TABLE lyrics ADD deviceid INTEGER;" );
5563
5564             //FIXME: (max) i know this is bad but its fast
5565             QStringList oldURLs = query( "SELECT url FROM lyrics;" );
5566             //it might be necessary to use batch updates to improve speed
5567             debug() << "Updating " << oldURLs.count() << " rows in lyrics";
5568             oldForeach( oldURLs )
5569             {
5570                 int deviceid = MountPointManager::instance()->getIdForUrl( *it );
5571                 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, *it );
5572                 QString update = QString( "UPDATE lyrics SET deviceid = %1, url = '%2' WHERE " )
5573                                  .arg( deviceid )
5574                                  .arg( escapeString( rpath ) );
5575                 update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5576                 query ( update );
5577             }
5578         }
5579         if ( PersistentVersion.toInt() < 15 )
5580         {
5581             createPersistentTablesV14( true );
5582             query( "INSERT INTO amazon_fix SELECT asin,locale,filename,refetchdate FROM amazon;" );
5583             query( "INSERT INTO lyrics_fix SELECT url,deviceid,lyrics FROM lyrics;" );
5584             query( "INSERT INTO playlists_fix SELECT playlist,url,tracknum FROM playlists;" );
5585             dropPersistentTablesV14();
5586             createPersistentTablesV14( false );
5587             query( "INSERT INTO amazon SELECT * FROM amazon_fix;" );
5588             query( "INSERT INTO lyrics SELECT * FROM lyrics_fix;" );
5589             query( "INSERT INTO playlists SELECT * FROM playlists_fix;" );
5590         }
5591         if ( PersistentVersion.toInt() < 17 )
5592         {
5593             //drop old labels and label tables, they were never used anyway and just confuse things
5594             query( "DROP TABLE label;" );
5595             query( "DROP TABLE labels;" );
5596             query( "DROP TABLE tags_labels;" );
5597             //update for label support
5598             QString labelsAutoIncrement = "";
5599             if ( getDbConnectionType() == DbConnection::postgresql )
5600             {
5601                 query( QString( "CREATE SEQUENCE labels_seq;" ) );
5602
5603                 labelsAutoIncrement = QString("DEFAULT nextval('labels_seq')");
5604             }
5605             else if ( getDbConnectionType() == DbConnection::mysql )
5606             {
5607                 labelsAutoIncrement = "AUTO_INCREMENT";
5608             }
5609
5610             query( QString( "CREATE TABLE labels ("
5611                             "id INTEGER PRIMARY KEY " + labelsAutoIncrement + ", "
5612                             "name " + textColumnType() + ", "
5613                             "type INTEGER);" ) );
5614
5615             query( QString( "CREATE TABLE tags_labels ("
5616                             "deviceid INTEGER,"
5617                             "url " + exactTextColumnType() + ", "
5618                             "uniqueid " + exactTextColumnType(32) + ", "      //m:n relationship, DO NOT MAKE UNIQUE!
5619                             "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
5620
5621             query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
5622             query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5623             query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5624         }
5625         if ( PersistentVersion.toInt() < 18 )
5626         {
5627             query( "ALTER TABLE lyrics ADD uniqueid " + exactTextColumnType(32) + ';' );
5628             query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
5629         }
5630         if ( PersistentVersion.toInt() < 19 )
5631         {
5632             query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" );   //m:n relationship, DO NOT MAKE UNIQUE!
5633         }
5634         //Up to date. Keep this number   \/   in sync!
5635         if ( PersistentVersion.toInt() > 19 || PersistentVersion.toInt() < 0 )
5636         {
5637             //Something is horribly wrong
5638             if ( adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION )
5639             {
5640                 error() << "There is a bug in Amarok: instead of destroying your valuable"
5641                         << " database tables, I'm quitting";
5642                 exit( 1 );
5643
5644                 debug() << "Rebuilding persistent tables database!";
5645                 dropPersistentTables();
5646                 createPersistentTables();
5647             }
5648         }
5649     }
5650 }
5651
5652 void
5653 CollectionDB::updatePodcastTables()
5654 {
5655     QString PodcastVersion = adminValue( "Database Podcast Tables Version" );
5656     if ( PodcastVersion.toInt() < 2 )
5657     {
5658         createPodcastTablesV2( true );
5659         query( "INSERT INTO podcastchannels_fix SELECT url,title,weblink,image,comment,"
5660                "copyright,parent,directory,autoscan,fetchtype,autotransfer,haspurge,"
5661                "purgecount FROM podcastchannels;" );
5662         query( "INSERT INTO podcastepisodes_fix SELECT id,url,localurl,parent,guid,title,"
5663                "subtitle,composer,comment,filetype,createdate,length,size,isNew FROM "
5664                "podcastepisodes;" );
5665         query( "INSERT INTO podcastfolders_fix SELECT id,name,parent,isOpen FROM podcastfolders;" );
5666         dropPodcastTablesV2();
5667         createPodcastTablesV2( false );
5668         query( "INSERT INTO podcastchannels SELECT * FROM podcastchannels_fix;" );
5669         query( "INSERT INTO podcastepisodes SELECT * FROM podcastepisodes_fix;" );
5670         query( "INSERT INTO podcastfolders SELECT * FROM podcastfolders_fix;" );
5671     }
5672
5673     //Keep this number in sync    \/
5674     if ( PodcastVersion.toInt() > 2 )
5675     {
5676         error() << "Something is very wrong with the Podcast Tables. Aborting";
5677         exit( 1 );
5678         dropPodcastTables();
5679         createPodcastTables();
5680     }
5681 }
5682
5683 void
5684 CollectionDB::vacuum()
5685 {
5686     if ( DbConnection::sqlite == getDbConnectionType() ||
5687          DbConnection::postgresql == getDbConnectionType() )
5688     {
5689         //Clean up DB and free unused space.
5690         debug() << "Running VACUUM";
5691         query( "VACUUM;" );
5692     }
5693 }
5694
5695 void
5696 CollectionDB::destroy()
5697 {
5698     //do we need or want to delete the actual connection objects as well as clearing them from the QMap?
5699     //or does QMap's clear function delete them?
5700     //this situation is not at all likely to happen often, so leaving them might be okay to prevent a
5701     //thread from having its connection torn out from under it...not likely, but possible
5702     //and leaving it should not end up eating much memory at all
5703
5704     connectionMutex->lock();
5705
5706     threadConnections->clear();
5707     delete m_dbConfig;
5708
5709     connectionMutex->unlock();
5710 }
5711
5712 void
5713 CollectionDB::scanModifiedDirs()
5714 {
5715    /* if ( !m_scanInProgress
5716 //PORT 2.0  && ( !CollectionView::instance() || !CollectionView::instance()->isOrganizingFiles() )
5717 //            && ( !MediaBrowser::instance() || !MediaBrowser::instance()->isTranscoding() )
5718        )
5719     {
5720         //we check if a job is pending because we don't want to abort incremental collection readings
5721         //Port 2.0
5722         if ( !ThreadManager::instance()->isJobPending( "CollectionScanner" ) && PlaylistBrowser::instance() )
5723         {
5724             m_scanInProgress = true;
5725             m_rescanRequired = false;
5726             emit scanStarted();
5727
5728             ThreadManager::instance()->onlyOneJob( new ScanController( this, true ) );
5729         }
5730     }
5731     else
5732         m_rescanRequired = true;*/
5733 }
5734
5735
5736 void
5737 CollectionDB::customEvent( QEvent *e )
5738 {
5739     /*if ( e->type() == (int)ScanController::JobFinishedEventType )
5740     {
5741         ScanController* s = static_cast<ScanController*>( e );
5742         m_scanInProgress = false;
5743
5744         if ( s->isIncremental() )
5745         {
5746             debug() << "JobFinishedEventType from Incremental ScanController received.\n";
5747             emit scanDone( s->hasChanged() );
5748
5749             // check if something changed while we were scanning. in this case we should
5750             // rescan again, now.
5751             if ( m_rescanRequired )
5752                 QTimer::singleShot( 0, CollectionDB::instance(), SLOT( scanMonitor() ) );
5753         }
5754         else
5755         {
5756             debug() << "JobFinishedEventType from ScanController received.\n";
5757             emit scanDone( s->wasSuccessful() );
5758         }
5759     }*/
5760 }
5761
5762
5763 QString
5764 CollectionDB::loadHashFile( const QByteArray& hash, uint width )
5765 {
5766     //debug() << "loadHashFile: " << hash << " - " << width;
5767
5768     QString full = tagCoverDir().filePath( hash );
5769
5770     if ( width == 0 ) {
5771         if ( QFileInfo( full ).isReadable() ) {
5772             //debug() << "loadHashFile: fullsize: " << full;
5773             return full;
5774         }
5775     } else {
5776         if ( width == 1 ) width = AmarokConfig::coverPreviewSize();
5777         QByteArray widthKey = makeWidthKey( width );
5778
5779         QString path = cacheCoverDir().filePath( widthKey + hash );
5780         if ( QFileInfo( path ).isReadable() ) {
5781             //debug() << "loadHashFile: scaled: " << path;
5782             return path;
5783         } else if ( QFileInfo( full ).isReadable() ) {
5784             //debug() << "loadHashFile: scaling: " << full;
5785             QImage image( full );
5786             if ( image.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" ) ) {
5787                 //debug() << "loadHashFile: scaled: " << path;
5788                 return path;
5789             }
5790         }
5791     }
5792     return QString();
5793 }
5794
5795
5796 bool
5797 CollectionDB::extractEmbeddedImage( const MetaBundle &trackInformation, QByteArray& hash )
5798 {
5799     //debug() << "extractEmbeddedImage: " << hash << " - " << trackInformation.url().path();
5800
5801     MetaBundle::EmbeddedImageList images;
5802     trackInformation.embeddedImages( images );
5803     oldForeachType ( MetaBundle::EmbeddedImageList, images ) {
5804         if ( hash.isEmpty() || (*it).hash() == hash ) {
5805             if ( (*it).save( tagCoverDir() ) ) {
5806                 //debug() << "extractEmbeddedImage: saved to " << tagCoverDir().path();
5807                 hash = (*it).hash();
5808                 return true;
5809             }
5810         }
5811     }
5812     return false;
5813 }
5814
5815 QStringList
5816 CollectionDB::getLabels( const QString &url, const uint type )
5817 {
5818     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5819     QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
5820     return query( QString( "SELECT labels.name FROM labels "
5821                            "LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5822                            "WHERE labels.type = %1 AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5823                          .arg( type ).arg( deviceid ).arg( escapeString( rpath ) ) );
5824 }
5825
5826 void
5827 CollectionDB::cleanLabels()
5828 {
5829     DEBUG_BLOCK
5830     QStringList labelIds = query( "select labels.id "
5831                                   "from labels left join tags_labels on labels.id = tags_labels.labelid "
5832                                   "where tags_labels.labelid is NULL;" );
5833     if ( !labelIds.isEmpty() )
5834     {
5835         QString ids;
5836         oldForeach( labelIds )
5837         {
5838             if ( !ids.isEmpty() )
5839                 ids += ',';
5840             ids += *it;
5841         }
5842         query( QString( "DELETE FROM labels "
5843                         "WHERE labels.id IN ( %1 );" )
5844                         .arg( ids ) );
5845     }
5846 }
5847
5848 void
5849 CollectionDB::setLabels( const QString &url, const QStringList &labels, const QString &uid, const uint type )
5850 {
5851     DEBUG_BLOCK
5852     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5853     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5854     QStringList labelIds = query( QString( "SELECT id FROM labels WHERE type = %1;" ).arg( type ) );
5855     QString ids;
5856     if ( !labelIds.isEmpty() )
5857     {
5858         oldForeach( labelIds )
5859         {
5860             if ( !ids.isEmpty() )
5861                 ids += ',';
5862             ids += *it;
5863         }
5864         //TODO: max: add uniqueid handling
5865         query( QString( "DELETE FROM tags_labels "
5866                         "WHERE tags_labels.labelid IN (%1) AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5867                         .arg( ids,  QString::number(deviceid),  rpath ) );
5868     }
5869
5870     oldForeach( labels )
5871     {
5872         int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5873                                          .arg( type ).arg( escapeString( *it ) ) ).first().toInt();
5874         if ( !id )
5875         {
5876             id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5877                                   .arg( type ).arg( escapeString( *it ) ), "labels" );
5878         }
5879         insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5880                          .arg( QString::number(id), QString::number(deviceid),  rpath, escapeString( uid ) ), 0 );
5881     }
5882
5883     emit labelsChanged( url );
5884 }
5885
5886 void
5887 CollectionDB::removeLabels( const QString &url, const QStringList &labels, const uint type )
5888 {
5889     DEBUG_BLOCK
5890     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5891     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5892     QString sql = QString( "DELETE FROM tags_labels "
5893                              "FROM tags_labels AS t LEFT JOIN labels AS l ON t.labelid = l.id "
5894                              "WHERE l.type = %1 AND t.deviceid = %2 AND t.url = '%3' AND ( 0" )
5895                              .arg( type ).arg( deviceid ).arg( rpath );
5896     oldForeach( labels )
5897     {
5898         sql += QString( " OR l.name = '%1'" ).arg( escapeString( *it ) );
5899     }
5900     sql += ");";
5901     query( sql );
5902
5903     emit labelsChanged( url );
5904 }
5905
5906 bool
5907 CollectionDB::addLabel( const QString &url, const QString &label, const QString &uid, const uint type )
5908 {
5909     DEBUG_BLOCK
5910     int deviceid = MountPointManager::instance()->getIdForUrl( url );
5911     QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5912
5913     int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5914                              .arg( type ).arg( escapeString( label ) ) ).first().toInt();
5915     bool labelAlreadyExists = id > 0;
5916     if ( !id )
5917     {
5918         id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5919                               .arg( type ).arg( escapeString( label ) ), "labels" );
5920     }
5921     if ( labelAlreadyExists )
5922     {
5923         //we can return if the link between the tags row and the labels row already exists
5924         int count = query( QString( "SELECT COUNT(*) FROM tags_labels WHERE labelid = %1 AND deviceid = %2 AND url = '%3';" )
5925                                     .arg( id ).arg( deviceid ).arg( rpath ) ).first().toInt();
5926         if ( count )
5927             return false;
5928     }
5929     insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5930                      .arg( QString::number(id), QString::number(deviceid), rpath, escapeString( uid ) ), "tags_labels" );
5931
5932     emit labelsChanged( url );
5933     return true;
5934 }
5935
5936 QStringList
5937 CollectionDB::favoriteLabels( int type, int count )
5938 {
5939     return query( QString( "SELECT labels.name, count( tags_labels.labelid ) "
5940                            "FROM labels LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5941                            "WHERE labels.type = %1 GROUP BY labels.name "
5942                            "ORDER BY count(tags_labels.labelid) DESC LIMIT %2;" )
5943                            .arg( QString::number( type ), QString::number( count ) ) );
5944 }
5945
5946 QDir
5947 CollectionDB::largeCoverDir() //static
5948 {
5949     return QDir( Amarok::saveLocation( "albumcovers/large/" ) );
5950 }
5951
5952
5953 QDir
5954 CollectionDB::tagCoverDir()  //static
5955 {
5956     return QDir( Amarok::saveLocation( "albumcovers/tagcover/" ) );
5957 }
5958
5959
5960 QDir
5961 CollectionDB::cacheCoverDir()  //static
5962 {
5963     return QDir( Amarok::saveLocation( "albumcovers/cache/" ) );
5964 }
5965
5966
5967 //////////////////////////////////////////////////////////////////////////////////////////
5968 // CLASS DbConnection
5969 //////////////////////////////////////////////////////////////////////////////////////////
5970
5971 DbConnection::DbConnection()
5972     : m_initialized( false )
5973 {}
5974
5975
5976 //////////////////////////////////////////////////////////////////////////////////////////
5977 // CLASS SqliteConnection
5978 //////////////////////////////////////////////////////////////////////////////////////////
5979
5980 SqliteConnection::SqliteConnection( const SqliteConfig* config )
5981     : DbConnection()
5982 {
5983
5984     DEBUG_BLOCK
5985
5986     const QByteArray path = QFile::encodeName( config->dbFile() );
5987
5988     // Open database file and check for correctness
5989     QFile file( path );
5990     if ( file.open( QIODevice::ReadOnly ) )
5991     {
5992         QByteArray format;
5993         format = file.readLine( 50 );
5994         if ( !format.startsWith( "SQLite format 3" ) )
5995         {
5996             warning() << "Database versions incompatible. Removing and rebuilding database.\n";
5997         }
5998         else if ( sqlite3_open( path, &m_db ) != SQLITE_OK )
5999         {
6000             warning() << "Database file corrupt. Removing and rebuilding database.\n";
6001             sqlite3_close( m_db );
6002         }
6003         else
6004             m_initialized = true;
6005     }
6006
6007     if ( !m_initialized )
6008     {
6009         // Remove old db file; create new
6010         QFile::remove( path );
6011         if ( sqlite3_open( path, &m_db ) == SQLITE_OK )
6012         {
6013             m_initialized = true;
6014         }
6015     }
6016     if ( m_initialized )
6017     {
6018         if( sqlite3_create_function(m_db, "rand", 0, SQLITE_UTF8, NULL, sqlite_rand, NULL, NULL) != SQLITE_OK )
6019             m_initialized = false;
6020         if( sqlite3_create_function(m_db, "power", 2, SQLITE_UTF8, NULL, sqlite_power, NULL, NULL) != SQLITE_OK )
6021             m_initialized = false;
6022         if ( sqlite3_create_function(m_db, "like", 2, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6023             m_initialized = false;
6024         if ( sqlite3_create_function(m_db, "like", 3, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6025             m_initialized = false;
6026     }
6027
6028     //optimization for speeding up SQLite
6029     query( "PRAGMA default_synchronous = OFF;" );
6030 }
6031
6032
6033 SqliteConnection::~SqliteConnection()
6034 {
6035     if ( m_db ) sqlite3_close( m_db );
6036 }
6037
6038
6039 QStringList SqliteConnection::query( const QString& statement, bool /*suppressDebug*/ )
6040 {
6041
6042     QStringList values;
6043     int error;
6044     int rc = 0;
6045     const char* tail;
6046     sqlite3_stmt* stmt;
6047     int busyCnt = 0;
6048     int retryCnt = 0;
6049
6050     do {
6051         //compile SQL program to virtual machine, reattempting if busy
6052         do {
6053             if ( busyCnt )
6054             {
6055                 ::usleep( 100000 );      // Sleep 100 msec
6056                 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt;
6057             }
6058             error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6059         }
6060         while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6061
6062         if ( error != SQLITE_OK )
6063         {
6064             if ( SQLITE_BUSY==error )
6065                 Debug::error() << "Gave up waiting for lock to clear";
6066             Debug::error() << " sqlite3_compile error:";
6067             Debug::error() << sqlite3_errmsg( m_db );
6068             Debug::error() << "on query: " << statement;
6069             values = QStringList();
6070             break;
6071         }
6072         else
6073         {
6074             busyCnt = 0;
6075             int number = sqlite3_column_count( stmt );
6076             //execute virtual machine by iterating over rows
6077             while ( true )
6078             {
6079                 error = sqlite3_step( stmt );
6080
6081                 if ( error == SQLITE_BUSY )
6082                 {
6083                     if ( busyCnt++ > 120 ) {
6084                         Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6085                         break;
6086                     }
6087                     ::usleep( 100000 ); // Sleep 100 msec
6088                     debug() << "sqlite3_step: BUSY counter: " << busyCnt;
6089                     continue;
6090                 }
6091                 if ( error == SQLITE_MISUSE )
6092                     debug() << "sqlite3_step: MISUSE";
6093                 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6094                     break;
6095
6096                 //iterate over columns
6097                 for ( int i = 0; i < number; i++ )
6098                 {
6099                     values << QString::fromUtf8( reinterpret_cast<const char*>( sqlite3_column_text( stmt, i ) ) );
6100                 }
6101             }
6102             //deallocate vm resources
6103             rc = sqlite3_finalize( stmt );
6104
6105             if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA )
6106             {
6107                 Debug::error() << "sqlite_step error.\n";
6108                 Debug::error() << sqlite3_errmsg( m_db );
6109                 Debug::error() << "on query: " << statement;
6110                 values = QStringList();
6111             }
6112             if ( rc == SQLITE_SCHEMA )
6113             {
6114                 retryCnt++;
6115                 debug() << "SQLITE_SCHEMA error occurred on query: " << statement;
6116                 if ( retryCnt < 10 )
6117                     debug() << "Retrying now.";
6118                 else
6119                 {
6120                     Debug::error() << "Retry-Count has reached maximum. Aborting this SQL statement!";
6121                     Debug::error() << "SQL statement: " << statement;
6122                     values = QStringList();
6123                 }
6124             }
6125         }
6126     }
6127     while ( rc == SQLITE_SCHEMA && retryCnt < 10 );
6128
6129     return values;
6130 }
6131
6132
6133 int SqliteConnection::insert( const QString& statement, const QString& /* table */ )
6134 {
6135     int error;
6136     int rc = 0;
6137     const char* tail;
6138     sqlite3_stmt* stmt;
6139     int busyCnt = 0;
6140     int retryCnt = 0;
6141
6142     do {
6143         //compile SQL program to virtual machine, reattempting if busy
6144         do {
6145             if ( busyCnt )
6146             {
6147                 ::usleep( 100000 );      // Sleep 100 msec
6148                 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt;
6149             }
6150             error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6151         }
6152         while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6153
6154         if ( error != SQLITE_OK )
6155         {
6156             if ( SQLITE_BUSY==error )
6157                 Debug::error() << "Gave up waiting for lock to clear";
6158             Debug::error() << " sqlite3_compile error:";
6159             Debug::error() << sqlite3_errmsg( m_db );
6160             Debug::error() << "on insert: " << statement;
6161             break;
6162         }
6163         else
6164         {
6165             busyCnt = 0;
6166             //execute virtual machine by iterating over rows
6167             while ( true )
6168             {
6169                 error = sqlite3_step( stmt );
6170
6171                 if ( error == SQLITE_BUSY )
6172                 {
6173                     if ( busyCnt++ > 120 ) {
6174                         Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6175                         break;
6176                     }
6177                     ::usleep( 100000 ); // Sleep 100 msec
6178                     debug() << "sqlite3_step: BUSY counter: " << busyCnt;
6179                 }
6180                 if ( error == SQLITE_MISUSE )
6181                     debug() << "sqlite3_step: MISUSE";
6182                 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6183                     break;
6184             }
6185             //deallocate vm resources
6186             rc = sqlite3_finalize( stmt );
6187
6188             if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA)
6189             {
6190                 Debug::error() << "sqlite_step error.\n";
6191                 Debug::error() << sqlite3_errmsg( m_db );
6192                 Debug::error() << "on insert: " << statement;
6193             }
6194             if ( rc == SQLITE_SCHEMA )
6195             {
6196                 retryCnt++;
6197                 debug() << "SQLITE_SCHEMA error occurred on insert: " << statement;
6198                 if ( retryCnt < 10 )
6199                     debug() << "Retrying now.";
6200                 else
6201                 {
6202                     Debug::error() << "Retry-Count has reached maximum. Aborting this SQL insert!";
6203                     Debug::error() << "SQL statement: " << statement;
6204                 }
6205             }
6206         }
6207     }
6208     while ( SQLITE_SCHEMA == rc && retryCnt < 10 );
6209     return sqlite3_last_insert_rowid( m_db );
6210 }
6211
6212
6213 // this implements a RAND() function compatible with the MySQL RAND() (0-param-form without seed)
6214 void SqliteConnection::sqlite_rand(sqlite3_context *context, int /*argc*/, sqlite3_value ** /*argv*/)
6215 {
6216     sqlite3_result_double( context, static_cast<double>(KRandom::random()) / (RAND_MAX+1.0) );
6217 }
6218
6219 // this implements a POWER() function compatible with the MySQL POWER()
6220 void SqliteConnection::sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv)
6221 {
6222     Q_ASSERT( argc==2 );
6223     if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ) {
6224         sqlite3_result_null(context);
6225         return;
6226     }
6227     double a = sqlite3_value_double(argv[0]);
6228     double b = sqlite3_value_double(argv[1]);
6229     sqlite3_result_double( context, pow(a,b) );
6230 }
6231
6232 // this implements a LIKE() function that overrides the default string comparison function
6233 // Reason: default function is case-sensitive for utf8 strings (BUG: 116458, ...)
6234 void SqliteConnection::sqlite_like_new( sqlite3_context *context, int argc, sqlite3_value **argv )
6235 {
6236
6237     const unsigned char *zA = sqlite3_value_text( argv[0] );
6238     const unsigned char *zB = sqlite3_value_text( argv[1] );
6239
6240     QString pattern = QString::fromUtf8( (const char*)zA );
6241     QString text = QString::fromUtf8( (const char*)zB );
6242
6243     int begin = pattern.startsWith( "%" ), end = pattern.endsWith( "%" );
6244     if (begin)
6245         pattern = pattern.right( pattern.length() - 1 );
6246     if (end)
6247         pattern = pattern.left( pattern.length() - 1 );
6248
6249     if( argc == 3 ) // The function is given an escape character. In likeCondition() it defaults to '/'
6250         pattern.replace( "/%", "%" ).replace( "/_", "_" ).replace( "//", "/" );
6251
6252     int result = 0;
6253     if ( begin && end ) result = ( text.indexOf( pattern, 0, Qt::CaseInsensitive ) != -1);
6254     else if ( begin ) result = text.endsWith( pattern, Qt::CaseInsensitive );
6255     else if ( end ) result = text.startsWith( pattern, Qt::CaseInsensitive );
6256     else result = ( text.toLower() == pattern.toLower() );
6257
6258     sqlite3_result_int( context, result );
6259 }
6260
6261 //////////////////////////////////////////////////////////////////////////////////////////
6262 // CLASS MySqlConnection
6263 //////////////////////////////////////////////////////////////////////////////////////////
6264
6265 #ifdef USE_MYSQL
6266 MySqlConnection::MySqlConnection( const MySqlConfig* config )
6267     : DbConnection()
6268     , m_connected( false )
6269 {
6270     DEBUG_BLOCK
6271
6272     debug() ;
6273     m_db = mysql_init(NULL);
6274     if (m_db)
6275     {
6276 //         if ( config->username().isEmpty() )
6277 //             pApp->slotConfigAmarok("MySql");
6278
6279         if ( mysql_real_connect( m_db, config->host().toLatin1(),
6280                                        config->username().toLatin1(),
6281                                        config->password().toLatin1(),
6282                                        config->database().toLatin1(),
6283                                        config->port(),
6284                                        NULL, CLIENT_COMPRESS ) )
6285         {
6286             m_initialized = true;
6287
6288 #if MYSQL_VERSION_ID >= 40113
6289             // now set the right charset for the connection
6290             QStringList my_qslist = query( "SHOW VARIABLES LIKE 'character_set_database'" );
6291             if( !my_qslist.isEmpty() && !mysql_set_character_set( m_db, const_cast<char *>( my_qslist[1].toLatin1() ) ) )
6292                 //charset was updated
6293                 debug() << "Connection Charset is now: " << my_qslist[1].toLatin1();
6294             else
6295                 error() << "Failed to set database charset\n";
6296 #endif
6297
6298             m_db->reconnect = 1; //setting reconnect flag for newer mysqld
6299             m_connected = true;
6300         }
6301         else
6302         {
6303
6304             if ( mysql_real_connect(
6305                     m_db,
6306                     config->host().toLatin1(),
6307                     config->username().toLatin1(),
6308                     config->password().toLatin1(),
6309                     NULL,
6310                     config->port(),
6311                     NULL, CLIENT_COMPRESS))
6312             {
6313                 if ( mysql_query(m_db,
6314                     QString( "CREATE DATABASE " + config->database() ).toLatin1() ) )
6315                     { m_connected = true; m_initialized = true; }
6316                 else
6317                     { setMysqlError(); }
6318             }
6319             else
6320                 setMysqlError();
6321
6322         }
6323     }
6324     else
6325         error() << "Failed to allocate/initialize MySql struct\n";
6326 }
6327
6328
6329 MySqlConnection::~MySqlConnection()
6330 {
6331     if ( m_db ) mysql_close( m_db );
6332 }
6333
6334
6335 QStringList MySqlConnection::query( const QString& statement, bool suppressDebug )
6336 {
6337     QStringList values;
6338
6339     if ( !mysql_query( m_db, statement.toUtf8() ) )
6340     {
6341         MYSQL_RES* result;
6342         if ( ( result = mysql_use_result( m_db ) ) )
6343         {
6344             int number = mysql_field_count( m_db );
6345             MYSQL_ROW row;
6346             while ( ( row = mysql_fetch_row( result ) ) )
6347             {
6348                 for ( int i = 0; i < number; i++ )
6349                 {
6350                   values << QString::fromUtf8( (const char*)row[i] );
6351                 }
6352             }
6353         }
6354         else
6355         {
6356             if ( mysql_field_count( m_db ) != 0 )
6357             {
6358                 if ( !suppressDebug )
6359                     debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6360                 values = QStringList();
6361             }
6362         }
6363         mysql_free_result( result );
6364     }
6365     else
6366     {
6367         if ( !suppressDebug )
6368             debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6369         values = QStringList();
6370     }
6371
6372     return values;
6373 }
6374
6375
6376 int MySqlConnection::insert( const QString& statement, const QString& /* table */ )
6377 {
6378     mysql_query( m_db, statement.toUtf8() );
6379     if ( mysql_errno( m_db ) )
6380     {
6381         debug() << "MYSQL INSERT FAILED: " << mysql_error( m_db ) << "\n" << "FAILED INSERT: " << statement;
6382     }
6383     return mysql_insert_id( m_db );
6384 }
6385
6386
6387 void
6388 MySqlConnection::setMysqlError()
6389 {
6390     m_error = i18n("MySQL reported the following error:<br>") + mysql_error(m_db)
6391             + i18n("<p>You can configure MySQL in the Collection section under Settings->Configure Amarok</p>");
6392 }
6393 #endif
6394
6395
6396 //////////////////////////////////////////////////////////////////////////////////////////
6397 // CLASS PostgresqlConnection
6398 //////////////////////////////////////////////////////////////////////////////////////////
6399
6400 #ifdef USE_POSTGRESQL
6401 PostgresqlConnection::PostgresqlConnection( const PostgresqlConfig* config )
6402       : DbConnection()
6403       , m_connected( false )
6404 {
6405   QString conninfo;
6406     debug() ;
6407
6408 //     if ( config->username().isEmpty() )
6409 //         pApp->slotConfigAmarok("Postgresql");
6410
6411     conninfo = "host='" + config->host() +
6412       "' port=" + QString::number( config->port() ) +
6413       " dbname='" + config->database() +
6414       "' user='" + config->username() +
6415       "' password='" + config->password() + '\'';
6416
6417     m_db = PQconnectdb( conninfo.toUtf8() );
6418
6419     if (!m_db)
6420     {
6421         debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6422         error() << "Failed to allocate/initialize Postgresql struct\n";
6423         setPostgresqlError();
6424         return;
6425     }
6426
6427     if (PQstatus(m_db) != CONNECTION_OK)
6428     {
6429         debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6430         error() << "Failed to allocate/initialize Postgresql struct\n";
6431         setPostgresqlError();
6432         PQfinish(m_db);
6433         m_db = NULL;
6434         return;
6435     }
6436
6437     m_initialized = true;
6438     m_connected = true;
6439 }
6440
6441
6442 PostgresqlConnection::~PostgresqlConnection()
6443 {
6444     if ( m_db ) PQfinish( m_db );
6445 }
6446
6447
6448 QStringList PostgresqlConnection::query( const QString& statement, bool suppressDebug )
6449 {
6450     QStringList values;
6451     PGresult* result;
6452     ExecStatusType status;
6453
6454     result = PQexec(m_db, statement.toUtf8());
6455     if (result == NULL)
6456     {
6457         if ( !suppressDebug )
6458             debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6459         return values;
6460     }
6461
6462     status = PQresultStatus(result);
6463     if ((status != PGRES_COMMAND_OK) && (status != PGRES_TUPLES_OK))
6464     {
6465         if ( !suppressDebug )
6466             debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6467         PQclear(result);
6468         return values;
6469     }
6470
6471     int cols = PQnfields( result );
6472     int rows = PQntuples( result );
6473     QMap<int, bool> discardCols;
6474     for(int col=0; col< cols; col++) {
6475         if (QString(PQfname(result, col)) == QString("__discard") || QString(PQfname(result, col)) == QString("__random"))
6476         {
6477             discardCols[col] = true;
6478         }
6479     }
6480
6481     for(int row=0; row< rows; row++)
6482     {
6483         for(int col=0; col< cols; col++)
6484         {
6485             if (discardCols[col]) continue;
6486
6487             values << QString::fromUtf8(PQgetvalue(result, row, col));
6488         }
6489     }
6490
6491     PQclear(result);
6492
6493     return values;
6494 }
6495
6496
6497 int PostgresqlConnection::insert( const QString& statement, const QString& table )
6498 {
6499     PGresult* result;
6500     ExecStatusType status;
6501     QString curvalSql;
6502     int id;
6503
6504     result = PQexec(m_db, statement.toUtf8());
6505     if (result == NULL)
6506     {
6507         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6508         return 0;
6509     }
6510
6511     status = PQresultStatus(result);
6512     if (status != PGRES_COMMAND_OK)
6513     {
6514         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6515         PQclear(result);
6516         return 0;
6517     }
6518     PQclear(result);
6519
6520     if (table == NULL) return 0;
6521
6522     QString _table = table;
6523     if (table.find("_temp") > 0) _table = table.left(table.find("_temp"));
6524
6525     curvalSql = QString("SELECT currval('%1_seq');").arg(_table);
6526     result = PQexec(m_db, curvalSql.toUtf8());
6527     if (result == NULL)
6528     {
6529         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6530         return 0;
6531     }
6532
6533     status = PQresultStatus(result);
6534     if (status != PGRES_TUPLES_OK)
6535     {
6536         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6537         PQclear(result);
6538         return 0;
6539     }
6540
6541     if ((PQnfields( result ) != 1) || (PQntuples( result ) != 1))
6542     {
6543         debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6544         PQclear(result);
6545         return 0;
6546     }
6547
6548     id = QString::fromUtf8(PQgetvalue(result, 0, 0)).toInt();
6549     PQclear(result);
6550
6551     return id;
6552 }
6553
6554
6555 void PostgresqlConnection::setPostgresqlError()
6556 {
6557     m_error = i18n("Postgresql reported the following error:<br>") + PQerrorMessage(m_db)
6558             + i18n("<p>You can configure Postgresql in the Collection section under Settings->Configure Amarok</p>");
6559 }
6560 #endif
6561
6562
6563
6564 //////////////////////////////////////////////////////////////////////////////////////////
6565 // CLASS SqliteConfig
6566 //////////////////////////////////////////////////////////////////////////////////////////
6567
6568 SqliteConfig::SqliteConfig( const QString& dbfile )
6569     : m_dbfile( dbfile )
6570 {}
6571
6572
6573 //////////////////////////////////////////////////////////////////////////////////////////
6574 // CLASS MySqlConfig
6575 //////////////////////////////////////////////////////////////////////////////////////////
6576
6577 MySqlConfig::MySqlConfig(
6578     const QString& host,
6579     const int port,
6580     const QString& database,
6581     const QString& username,
6582     const QString& password )
6583     : m_host( host ),
6584       m_port( port ),
6585       m_database( database ),
6586       m_username( username ),
6587       m_password( password )
6588 {}
6589
6590
6591 //////////////////////////////////////////////////////////////////////////////////////////
6592 // CLASS PostgresqlConfig
6593 //////////////////////////////////////////////////////////////////////////////////////////
6594
6595 PostgresqlConfig::PostgresqlConfig(
6596     const QString& host,
6597     const int port,
6598     const QString& database,
6599     const QString& username,
6600     const QString& password )
6601     : m_host( host ),
6602       m_port( port ),
6603       m_database( database ),
6604       m_username( username ),
6605       m_password( password )
6606 {}
6607
6608 #include "collectiondb.moc"