path: root/amarok/src/database_refactor/collectiondb.cpp

// (c) 2004 Mark Kretschmann <markey@web.de>
// (c) 2004 Christian Muehlhaeuser <chris@chris.de>
// (c) 2004 Sami Nieminen <sami.nieminen@iki.fi>
// (c) 2005 Ian Monroe <ian@monroe.nu>
// See COPYING file for licensing information.

#define DEBUG_PREFIX "CollectionDB"

#include "app.h"
#include "amarok.h"
#include "amarokconfig.h"
#include "config.h"
#include "debug.h"
#include "collectionbrowser.h"    //updateTags()
#include "collectiondb.h"
#include "collectionreader.h"
#include "coverfetcher.h"
#include "enginecontroller.h"
#include "metabundle.h"           //updateTags()
#include "playlist.h"
#include "playlistbrowser.h"
#include "pluginmanager.h"
#include "scrobbler.h"
#include "statusbar.h"
#include "threadweaver.h"

#include <tqfile.h>
#include <tqimage.h>
#include <tqtimer.h>

#include <kapplication.h>
#include <kconfig.h>
#include <kglobal.h>
#include <kinputdialog.h>         //setupCoverFetcher()
#include <kio/job.h>
#include <klineedit.h>            //setupCoverFetcher()
#include <klocale.h>
#include <kmdcodec.h>
#include <kstandarddirs.h>
#include <kurl.h>
#include <kio/netaccess.h>

#include <cmath>                 //DbConnection::sqlite_power()
#include <ctime>                 //query()
#include <unistd.h>              //usleep()

#include <taglib/mpegfile.h>
#include <taglib/mpegfile.h>
#include <taglib/id3v2tag.h>
#include <taglib/attachedpictureframe.h>
#include <taglib/tbytevector.h>


//////////////////////////////////////////////////////////////////////////////////////////
// CLASS CollectionDB
//////////////////////////////////////////////////////////////////////////////////////////

CollectionDB* CollectionDB::instance()
{
    static CollectionDB db;
    return &db;
}


CollectionDB::CollectionDB()
        : EngineObserver( EngineController::instance() )
        , m_cacheDir( amaroK::saveLocation() )
        , m_coverDir( amaroK::saveLocation() )
{
    DEBUG_BLOCK

    // create cover dir, if it doesn't exist.
    if( !m_coverDir.exists( "albumcovers", false ) )
        m_coverDir.mkdir( "albumcovers", false );
    m_coverDir.cd( "albumcovers" );

     // create image cache dir, if it doesn't exist.
    if( !m_cacheDir.exists( "albumcovers/cache", false ) )
        m_cacheDir.mkdir( "albumcovers/cache", false );
    m_cacheDir.cd( "albumcovers/cache" );

    // Load DBEngine plugin
    TQString query = "[X-KDE-Amarok-plugintype] == 'dbengine' and [X-KDE-Amarok-name] != '%1'";
    KTrader::OfferList offers = PluginManager::query( query.arg( "sqlite-dbengine" ) );
    m_dbEngine = (DBEngine*) PluginManager::createFromService( offers.first() );

    //<OPEN DATABASE>
    initialize();
    //</OPEN DATABASE>

    // TODO: Should write to config in dtor, but it crashes...
    KConfig* config = amaroK::config( "Collection Browser" );
    config->writeEntry( "Database Version", DATABASE_VERSION );
    config->writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );

    startTimer( MONITOR_INTERVAL * 1000 );

    connect( Scrobbler::instance(), TQT_SIGNAL( similarArtistsFetched( const TQString&, const TQStringList& ) ),
             this,                  TQT_SLOT( similarArtistsFetched( const TQString&, const TQStringList& ) ) );
}


CollectionDB::~CollectionDB()
{
    DEBUG_FUNC_INFO

    destroy();

//     This crashes so it's done at the end of ctor.
//     KConfig* const config = amaroK::config( "Collection Browser" );
//     config->writeEntry( "Database Version", DATABASE_VERSION );
//     config->writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
}


//////////////////////////////////////////////////////////////////////////////////////////
// PUBLIC
//////////////////////////////////////////////////////////////////////////////////////////


DbConnection
*CollectionDB::getStaticDbConnection()
{
    return m_dbConnPool->getDbConnection();
}


void
CollectionDB::returnStaticDbConnection( DbConnection *conn )
{
    m_dbConnPool->putDbConnection( conn );
}


/**
 * Executes a SQL query on the already opened database
 * @param statement SQL program to execute. Only one SQL statement is allowed.
 * @return          The queried data, or TQStringList() on error.
 */
TQStringList
CollectionDB::query( const TQString& statement, DbConnection *conn )
{
    if ( DEBUG )
        debug() << "Query-start: " << statement << endl;

    clock_t start = clock();

    DbConnection *dbConn;
    if ( conn != NULL )
    {
        dbConn = conn;
    }
    else
    {
        dbConn = m_dbConnPool->getDbConnection();
    }

    TQStringList values = dbConn->query( statement );

    if ( conn == NULL )
    {
        m_dbConnPool->putDbConnection( dbConn );
    }

    if ( DEBUG )
    {
        clock_t finish = clock();
        const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
        debug() << "SQL-query (" << duration << "s): " << statement << endl;
    }
    return values;
}


/**
 * Executes a SQL insert on the already opened database
 * @param statement SQL statement to execute. Only one SQL statement is allowed.
 * @return          The rowid of the inserted item.
 */
int
CollectionDB::insert( const TQString& statement, const TQString& table, DbConnection *conn )
{
    if ( DEBUG )
        debug() << "insert-start: " << statement << endl;

    clock_t start = clock();

    DbConnection *dbConn;
    if ( conn != NULL )
    {
        dbConn = conn;
    }
    else
    {
        dbConn = m_dbConnPool->getDbConnection();
    }

    int id = dbConn->insert( statement, table );

    if ( conn == NULL )
    {
        m_dbConnPool->putDbConnection( dbConn );
    }

    if ( DEBUG )
    {
        clock_t finish = clock();
        const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
        debug() << "SQL-insert (" << duration << "s): " << statement << endl;
    }
    return id;
}


bool
CollectionDB::isEmpty()
{
    TQStringList values;

    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql)
    {
        values = query( "SELECT COUNT( url ) FROM tags OFFSET 0 LIMIT 1;" );
    }
    else
    {
        values = query( "SELECT COUNT( url ) FROM tags LIMIT 0, 1;" );
    }

    return values.isEmpty() ? true : values.first() == "0";
}


bool
CollectionDB::isValid()
{
    TQStringList values1;
    TQStringList values2;

    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        values1 = query( "SELECT COUNT( url ) FROM tags OFFSET 0 LIMIT 1;" );
        values2 = query( "SELECT COUNT( url ) FROM statistics OFFSET 0 LIMIT 1;" );
    }
    else
    {
        values1 = query( "SELECT COUNT( url ) FROM tags LIMIT 0, 1;" );
        values2 = query( "SELECT COUNT( url ) FROM statistics LIMIT 0, 1;" );
    }

    //TODO? this returns true if value1 or value2 is not empty. Shouldn't this be and (&&)???
    return !values1.isEmpty() || !values2.isEmpty();
}


void
CollectionDB::createTables( DbConnection *conn )
{
    DEBUG_FUNC_INFO

    //create tag table
    query( TQString( "CREATE %1 TABLE tags%2 ("
                    "url " + textColumnType() + ","
                    "dir " + textColumnType() + ","
                    "createdate INTEGER,"
                    "album INTEGER,"
                    "artist INTEGER,"
                    "genre INTEGER,"
                    "title " + textColumnType() + ","
                    "year INTEGER,"
                    "comment " + textColumnType() + ","
                    "track NUMERIC(4),"
                    "bitrate INTEGER,"
                    "length INTEGER,"
                    "samplerate INTEGER,"
                    "sampler BOOL );" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" ), conn );

    TQString albumAutoIncrement = "";
    TQString artistAutoIncrement = "";
    TQString genreAutoIncrement = "";
    TQString yearAutoIncrement = "";
    if ( m_dbConnPool->getDbConnectionType() == DbConnection::postgresql )
    {
        query( TQString( "CREATE SEQUENCE album_seq;" ), conn );
        query( TQString( "CREATE SEQUENCE artist_seq;" ), conn );
        query( TQString( "CREATE SEQUENCE genre_seq;" ), conn );
        query( TQString( "CREATE SEQUENCE year_seq;" ), conn );

        albumAutoIncrement = TQString("DEFAULT nextval('album_seq')");
        artistAutoIncrement = TQString("DEFAULT nextval('artist_seq')");
        genreAutoIncrement = TQString("DEFAULT nextval('genre_seq')");
        yearAutoIncrement = TQString("DEFAULT nextval('year_seq')");
    }
    else if ( m_dbConnPool->getDbConnectionType() == DbConnection::mysql )
    {
        albumAutoIncrement = "AUTO_INCREMENT";
        artistAutoIncrement = "AUTO_INCREMENT";
        genreAutoIncrement = "AUTO_INCREMENT";
        yearAutoIncrement = "AUTO_INCREMENT";
    }
    //create album table
    query( TQString( "CREATE %1 TABLE album%2 ("
                    "id INTEGER PRIMARY KEY %3,"
                    "name " + textColumnType() + ");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" )
                    .arg( albumAutoIncrement ), conn );

    //create artist table
    query( TQString( "CREATE %1 TABLE artist%2 ("
                    "id INTEGER PRIMARY KEY %3,"
                    "name " + textColumnType() + ");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" )
                    .arg( artistAutoIncrement ), conn );

    //create genre table
    query( TQString( "CREATE %1 TABLE genre%2 ("
                    "id INTEGER PRIMARY KEY %3,"
                    "name " + textColumnType() +");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" )
                    .arg( genreAutoIncrement ), conn );

    //create year table
    query( TQString( "CREATE %1 TABLE year%2 ("
                    "id INTEGER PRIMARY KEY %3,"
                    "name " + textColumnType() + ");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" )
                    .arg( yearAutoIncrement ), conn );

    //create images table
    query( TQString( "CREATE %1 TABLE images%2 ("
                    "path " + textColumnType() + ","
                    "artist " + textColumnType() + ","
                    "album " + textColumnType() + ");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" ), conn );

    // create directory statistics table
    query( TQString( "CREATE %1 TABLE directories%2 ("
                    "dir " + textColumnType() + " UNITQUE,"
                    "changedate INTEGER );" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" ), conn );


    //create indexes
    query( TQString( "CREATE INDEX album_idx%1 ON album%2( name );" )
                    .arg( conn ? "_temp" : "" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "CREATE INDEX artist_idx%1 ON artist%2( name );" )
                    .arg( conn ? "_temp" : "" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "CREATE INDEX genre_idx%1 ON genre%2( name );" )
                    .arg( conn ? "_temp" : "" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "CREATE INDEX year_idx%1 ON year%2( name );" )
                    .arg( conn ? "_temp" : "" ).arg( conn ? "_temp" : "" ), conn );

    if ( !conn )
    {
        // create related artists cache
        query( TQString( "CREATE TABLE related_artists ("
                        "artist " + textColumnType() + ","
                        "suggestion " + textColumnType() + ","
                        "changedate INTEGER );" ) );

        query( "CREATE INDEX url_tag ON tags( url );" );
        query( "CREATE INDEX album_tag ON tags( album );" );
        query( "CREATE INDEX artist_tag ON tags( artist );" );
        query( "CREATE INDEX genre_tag ON tags( genre );" );
        query( "CREATE INDEX year_tag ON tags( year );" );
        query( "CREATE INDEX sampler_tag ON tags( sampler );" );

        query( "CREATE INDEX images_album ON images( album );" );
        query( "CREATE INDEX images_artist ON images( artist );" );

        query( "CREATE INDEX directories_dir ON directories( dir );" );
        query( "CREATE INDEX related_artists_artist ON related_artists( artist );" );
    }
}


void
CollectionDB::dropTables( DbConnection *conn )
{
    DEBUG_FUNC_INFO

    query( TQString( "DROP TABLE tags%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE album%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE artist%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE genre%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE year%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE images%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "DROP TABLE directories%1;" ).arg( conn ? "_temp" : "" ), conn );
    if ( !conn )
    {
        query( TQString( "DROP TABLE related_artists;" ) );
    }

    if ( m_dbConnPool->getDbConnectionType() == DbConnection::postgresql )
    {
        if (conn == NULL) {
            query( TQString( "DROP SEQUENCE album_seq;" ), conn );
            query( TQString( "DROP SEQUENCE artist_seq;" ), conn );
            query( TQString( "DROP SEQUENCE genre_seq;" ), conn );
            query( TQString( "DROP SEQUENCE year_seq;" ), conn );
	}
    }
}


void
CollectionDB::clearTables( DbConnection *conn )
{
    DEBUG_FUNC_INFO

    TQString clearCommand = "DELETE FROM";
    if ( m_dbConnPool->getDbConnectionType() == DbConnection::mysql )
    {
        // TRUNCATE TABLE is faster than DELETE FROM TABLE, so use it when supported.
        clearCommand = "TRUNCATE TABLE";
    }

    query( TQString( "%1 tags%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 album%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 artist%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 genre%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 year%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 images%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( TQString( "%1 directories%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    if ( !conn )
    {
        query( TQString( "%1 related_artists;" ).arg( clearCommand ) );
    }
}


void
CollectionDB::moveTempTables( DbConnection *conn )
{
    insert( "INSERT INTO tags SELECT * FROM tags_temp;", NULL, conn );
    insert( "INSERT INTO album SELECT * FROM album_temp;", NULL, conn );
    insert( "INSERT INTO artist SELECT * FROM artist_temp;", NULL, conn );
    insert( "INSERT INTO genre SELECT * FROM genre_temp;", NULL, conn );
    insert( "INSERT INTO year SELECT * FROM year_temp;", NULL, conn );
    insert( "INSERT INTO images SELECT * FROM images_temp;", NULL, conn );
    insert( "INSERT INTO directories SELECT * FROM directories_temp;", NULL, conn );
}


void
CollectionDB::createStatsTable()
{
    DEBUG_FUNC_INFO

    // create music statistics database
    query( TQString( "CREATE TABLE statistics ("
                    "url " + textColumnType() + " UNITQUE,"
                    "createdate INTEGER,"
                    "accessdate INTEGER,"
                    "percentage FLOAT,"
                    "playcounter INTEGER );" ) );

    query( "CREATE INDEX url_stats ON statistics( url );" );
    query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
    query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
}


void
CollectionDB::dropStatsTable()
{
    DEBUG_FUNC_INFO

    query( "DROP TABLE statistics;" );
}


uint
CollectionDB::artistID( TQString value, bool autocreate, const bool temporary, const bool updateSpelling, DbConnection *conn )
{
    // lookup cache
    if ( m_cacheArtist == value )
        return m_cacheArtistID;

    uint id = IDFromValue( "artist", value, autocreate, temporary, updateSpelling, conn );

    // cache values
    m_cacheArtist = value;
    m_cacheArtistID = id;

    return id;
}


TQString
CollectionDB::artistValue( uint id )
{
    // lookup cache
    if ( m_cacheArtistID == id )
        return m_cacheArtist;

    TQString value = valueFromID( "artist", id );

    // cache values
    m_cacheArtist = value;
    m_cacheArtistID = id;

    return value;
}



uint
CollectionDB::albumID( TQString value, bool autocreate, const bool temporary, const bool updateSpelling, DbConnection *conn )
{
    // lookup cache
    if ( m_cacheAlbum == value )
        return m_cacheAlbumID;

    uint id = IDFromValue( "album", value, autocreate, temporary, updateSpelling, conn );

    // cache values
    m_cacheAlbum = value;
    m_cacheAlbumID = id;

    return id;
}


TQString
CollectionDB::albumValue( uint id )
{
    // lookup cache
    if ( m_cacheAlbumID == id )
        return m_cacheAlbum;

    TQString value = valueFromID( "album", id );

    // cache values
    m_cacheAlbum = value;
    m_cacheAlbumID = id;

    return value;
}


uint
CollectionDB::genreID( TQString value, bool autocreate, const bool temporary, const bool updateSpelling, DbConnection *conn )
{
    return IDFromValue( "genre", value, autocreate, temporary, updateSpelling, conn );
}


TQString
CollectionDB::genreValue( uint id )
{
    return valueFromID( "genre", id );
}


uint
CollectionDB::yearID( TQString value, bool autocreate, const bool temporary, const bool updateSpelling, DbConnection *conn )
{
    return IDFromValue( "year", value, autocreate, temporary, updateSpelling, conn );
}


TQString
CollectionDB::yearValue( uint id )
{
    return valueFromID( "year", id );
}


uint
CollectionDB::IDFromValue( TQString name, TQString value, bool autocreate, const bool temporary, const bool updateSpelling, DbConnection *conn )
{
    if ( temporary )
        name.append( "_temp" );
    else
        conn = NULL;

    TQStringList values =
        query( TQString(
            "SELECT id, name FROM %1 WHERE name LIKE '%2';" )
            .arg( name )
            .arg( CollectionDB::instance()->escapeString( value ) ), conn );

    if ( updateSpelling && !values.isEmpty() && ( values[1] != value ) )
    {
        query( TQString( "UPDATE %1 SET id = %2, name = '%3' WHERE id = %4;" )
                  .arg( name )
                  .arg( values.first() )
                  .arg( CollectionDB::instance()->escapeString( value ) )
                  .arg( values.first() ), conn );
    }

    //check if item exists. if not, should we autocreate it?
    uint id;
    if ( values.isEmpty() && autocreate )
    {
        id = insert( TQString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
                        .arg( name )
                        .arg( CollectionDB::instance()->escapeString( value ) ), name, conn );

        return id;
    }

    return values.isEmpty() ? 0 : values.first().toUInt();
}


TQString
CollectionDB::valueFromID( TQString table, uint id )
{
    TQStringList values =
        query( TQString(
            "SELECT name FROM %1 WHERE id=%2;" )
            .arg( table )
            .arg( id ) );


    return values.isEmpty() ? 0 : values.first();
}


TQString
CollectionDB::albumSongCount( const TQString &artist_id, const TQString &album_id )
{
    TQStringList values =
        query( TQString(
            "SELECT COUNT( url ) FROM tags WHERE album = %1 AND artist = %2;" )
            .arg( album_id )
            .arg( artist_id ) );
    return values.first();
}

bool
CollectionDB::albumIsCompilation( const TQString &album_id )
{
    TQStringList values =
        query( TQString(
            "SELECT sampler FROM tags WHERE sampler=%1 AND album=%2" )
            .arg( CollectionDB::instance()->boolT() )
            .arg( album_id ) );

    return (values.count() != 0);
}

TQStringList
CollectionDB::albumTracks( const TQString &artist_id, const TQString &album_id )
{
    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        return query( TQString( "SELECT tags.url, tags.track AS __discard FROM tags, year WHERE tags.album = %1 AND "
                               "( tags.sampler = %2 OR tags.artist = %3 ) AND year.id = tags.year "
                               "ORDER BY tags.track;" )
                      .arg( album_id )
                      .arg( boolT() )
                      .arg( artist_id ) );
    }
    else
    {
        return query( TQString( "SELECT tags.url FROM tags, year WHERE tags.album = %1 AND "
                              "( tags.sampler = 1 OR tags.artist = %2 ) AND year.id = tags.year "
                              "ORDER BY tags.track;" )
                        .arg( album_id )
                        .arg( artist_id ) );
    }
}


void
CollectionDB::addImageToAlbum( const TQString& image, TQValueList< TQPair<TQString, TQString> > info, DbConnection *conn )
{
    for ( TQValueList< TQPair<TQString, TQString> >::ConstIterator it = info.begin(); it != info.end(); ++it )
    {
        if ( (*it).first.isEmpty() || (*it).second.isEmpty() )
            continue;

        debug() << "Added image for album: " << (*it).first << " - " << (*it).second << ": " << image << endl;
        insert( TQString( "INSERT INTO images%1 ( path, artist, album ) VALUES ( '%1', '%2', '%3' );" )
         .arg( conn ? "_temp" : "" )
         .arg( escapeString( image ) )
         .arg( escapeString( (*it).first ) )
         .arg( escapeString( (*it).second ) ), NULL, conn );
    }
}

TQImage
CollectionDB::fetchImage(const KURL& url, TQString &/*tmpFile*/)
{
    if(url.protocol() != "file")
    {
        TQString tmpFile;
        KIO::NetAccess::download( url, tmpFile, 0); //TODO set 0 to the window, though it probably doesn't really matter
        return TQImage(tmpFile);
    }
    else
    {
        return TQImage( url.path() );
    }

}
bool
CollectionDB::setAlbumImage( const TQString& artist, const TQString& album, const KURL& url )
{
    TQString tmpFile;
    bool success = setAlbumImage( artist, album, fetchImage(url, tmpFile) );
    KIO::NetAccess::removeTempFile( tmpFile ); //only removes file if it was created with NetAccess
    return success;
}


bool
CollectionDB::setAlbumImage( const TQString& artist, const TQString& album, TQImage img, const TQString& amazonUrl )
{
    debug() << "Saving cover for: " << artist << " - " << album << endl;

    //show a wait cursor for the duration
    amaroK::OverrideCursor keep;

    // remove existing album covers
    removeAlbumImage( artist, album );

    TQDir largeCoverDir( amaroK::saveLocation( "albumcovers/large/" ) );
    TQCString key = md5sum( artist, album );

    // Save Amazon product page URL as embedded string, for later retreival
    if ( !amazonUrl.isEmpty() )
        img.setText( "amazon-url", 0, amazonUrl );

    return img.save( largeCoverDir.filePath( key ), "PNG");
}


TQString
CollectionDB::findImageByMetabundle( MetaBundle trackInformation, uint width )
{
    if( width == 1 ) width = AmarokConfig::coverPreviewSize();

    TQCString widthKey = makeWidthKey( width );
    TQCString tagKey = md5sum( trackInformation.artist(), trackInformation.album() );
    TQDir tagCoverDir( amaroK::saveLocation( "albumcovers/tagcover/" ) );

    //FIXME: the cached versions will never be refreshed
    if ( tagCoverDir.exists( widthKey + tagKey ) )
    {
        // cached version
        return tagCoverDir.filePath( widthKey + tagKey );
    } else
    {
        // look into the tag
        TagLib::MPEG::File f( TQFile::encodeName( trackInformation.url().path() ) );
        TagLib::ID3v2::Tag *tag = f.ID3v2Tag();

        if ( tag )
        {
            TagLib::ID3v2::FrameList l = f.ID3v2Tag()->frameListMap()[ "APIC" ];
            if ( !l.isEmpty() )
            {
                debug() << "Found APIC frame(s)" << endl;
                TagLib::ID3v2::Frame *f = l.front();
                TagLib::ID3v2::AttachedPictureFrame *ap = (TagLib::ID3v2::AttachedPictureFrame*)f;

                const TagLib::ByteVector &imgVector = ap->picture();
                debug() << "Size of image: " <<  imgVector.size() << " byte" << endl;

                // ignore APIC frames without picture and those with obviously bogus size
                if( imgVector.size() == 0 || imgVector.size() > 10000000 /*10MB*/ )
                    return TQString();

                TQImage image;
                if( image.loadFromData((const uchar*)imgVector.data(), imgVector.size()) )
                {
                    if ( width > 1 )
                    {
                        image.smoothScale( width, width, TQImage::ScaleMin ).save( m_cacheDir.filePath( widthKey + tagKey ), "PNG" );
                        return m_cacheDir.filePath( widthKey + tagKey );
                    } else
                    {
                        image.save( tagCoverDir.filePath( tagKey ), "PNG" );
                        return tagCoverDir.filePath( tagKey );
                    }
                } // image.isNull
            } // apic list is empty
        } // tag is empty
    } // caching

    return TQString();
}


TQString
CollectionDB::findImageByArtistAlbum( const TQString &artist, const TQString &album, uint width )
{
    TQCString widthKey = makeWidthKey( width );

    if ( artist.isEmpty() && album.isEmpty() )
        return notAvailCover( width );
    else
    {
        TQCString key = md5sum( artist, album );

        // check cache for existing cover
        if ( m_cacheDir.exists( widthKey + key ) )
            return m_cacheDir.filePath( widthKey + key );
        else
        {
            // we need to create a scaled version of this cover
            TQDir largeCoverDir( amaroK::saveLocation( "albumcovers/large/" ) );
            if ( largeCoverDir.exists( key ) )
                if ( width > 1 )
                {
                    TQImage img( largeCoverDir.filePath( key ) );
                    img.smoothScale( width, width, TQImage::ScaleMin ).save( m_cacheDir.filePath( widthKey + key ), "PNG" );

                    return m_cacheDir.filePath( widthKey + key );
                }
                else
                    return largeCoverDir.filePath( key );
        }

        // no amazon cover found, let's try to find a cover in the song's directory
        return getImageForAlbum( artist, album, width );
    }
}


TQString
CollectionDB::albumImage( const TQString &artist, const TQString &album, uint width )
{
    TQString s;
    // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
    if ( width == 1 ) width = AmarokConfig::coverPreviewSize();

    s = findImageByArtistAlbum( artist, album, width );
    if ( s == notAvailCover( width ) )
        return findImageByArtistAlbum( "", album, width );

    return s;
}


TQString
CollectionDB::albumImage( const uint artist_id, const uint album_id, const uint width )
{
    return albumImage( artistValue( artist_id ), albumValue( album_id ), width );
}


TQString
CollectionDB::albumImage( MetaBundle trackInformation, uint width )
{
    TQString path = findImageByMetabundle( trackInformation, width );
    if( path.isEmpty() )
        path =albumImage( trackInformation.artist(), trackInformation.album(), width );

    return path;
}


TQCString
CollectionDB::makeWidthKey( uint width )
{
    return TQString::number( width ).local8Bit() + "@";
}

// get image from path
TQString
CollectionDB::getImageForAlbum( const TQString& artist, const TQString& album, uint width )
{
    if ( width == 1 ) width = AmarokConfig::coverPreviewSize();
    TQCString widthKey = TQString::number( width ).local8Bit() + "@";

    if ( album.isEmpty() )
        return notAvailCover( width );

    TQStringList values =
        query( TQString(
            "SELECT path FROM images WHERE artist LIKE '%1' AND album LIKE '%2' ORDER BY path;" )
            .arg( escapeString( artist ) )
            .arg( escapeString( album ) ) );

    if ( !values.isEmpty() )
    {
        TQString image( values.first() );
        uint matches = 0;
        uint maxmatches = 0;
        for ( uint i = 0; i < values.count(); i++ )
        {
            matches = values[i].contains( "front", false ) + values[i].contains( "cover", false ) + values[i].contains( "folder", false );
            if ( matches > maxmatches )
            {
                image = values[i];
                maxmatches = matches;
            }
        }

        TQCString key = md5sum( artist, album, image );

        if ( width > 1 )
        {
            if ( !m_cacheDir.exists( widthKey + key ) )
            {
                TQImage img = TQImage( image );
                img.smoothScale( width, width, TQImage::ScaleMin ).save( m_cacheDir.filePath( widthKey + key ), "PNG" );
            }

            return m_cacheDir.filePath( widthKey + key );
        }
        else //large image
        {
            return image;
        }
    }

    return notAvailCover( width );
}


bool
CollectionDB::removeAlbumImage( const TQString &artist, const TQString &album )
{
    TQCString widthKey = "*@";
    TQCString key = md5sum( artist, album );

    // remove scaled versions of images
    TQStringList scaledList = m_cacheDir.entryList( widthKey + key );
    if ( scaledList.count() > 0 )
        for ( uint i = 0; i < scaledList.count(); i++ )
            TQFile::remove( m_cacheDir.filePath( scaledList[ i ] ) );

    // remove large, original image
    TQDir largeCoverDir( amaroK::saveLocation( "albumcovers/large/" ) );

    if ( largeCoverDir.exists( key ) && TQFile::remove( largeCoverDir.filePath( key ) ) ) {
        emit coverRemoved( artist, album );
        return true;
    }

    return false;
}

bool
CollectionDB::removeAlbumImage( const uint artist_id, const uint album_id )
{
    return removeAlbumImage( artistValue( artist_id ), albumValue( album_id ) );
}


TQString
CollectionDB::notAvailCover( int width )
{
    if ( !width ) width = AmarokConfig::coverPreviewSize();
    TQString widthKey = TQString::number( width ) + "@";

    if( m_cacheDir.exists( widthKey + "nocover.png" ) )
        return m_cacheDir.filePath( widthKey + "nocover.png" );
    else
    {
        TQImage nocover( locate( "data", "amarok/images/nocover.png" ) );
        nocover.smoothScale( width, width, TQImage::ScaleMin ).save( m_cacheDir.filePath( widthKey + "nocover.png" ), "PNG" );
        return m_cacheDir.filePath( widthKey + "nocover.png" );
    }
}


TQStringList
CollectionDB::artistList( bool withUnknowns, bool withCompilations )
{
    QueryBuilder qb;
    qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );

    if ( !withUnknowns )
        qb.excludeMatch( QueryBuilder::tabArtist, i18n( "Unknown" ) );
    if ( !withCompilations )
        qb.setOptions( QueryBuilder::optNoCompilations );

    qb.setOptions( QueryBuilder::optRemoveDuplicates );
    qb.sortBy( QueryBuilder::tabArtist, QueryBuilder::valName );
    return qb.run();
}


TQStringList
CollectionDB::albumList( bool withUnknowns, bool withCompilations )
{
    QueryBuilder qb;
    qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );

    if ( !withUnknowns )
        qb.excludeMatch( QueryBuilder::tabAlbum, i18n( "Unknown" ) );
    if ( !withCompilations )
        qb.setOptions( QueryBuilder::optNoCompilations );

    qb.setOptions( QueryBuilder::optRemoveDuplicates );
    qb.sortBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
    return qb.run();
}


TQStringList
CollectionDB::genreList( bool withUnknowns, bool withCompilations )
{
    QueryBuilder qb;
    qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );

    if ( !withUnknowns )
        qb.excludeMatch( QueryBuilder::tabGenre, i18n( "Unknown" ) );
    if ( !withCompilations )
        qb.setOptions( QueryBuilder::optNoCompilations );

    qb.setOptions( QueryBuilder::optRemoveDuplicates );
    qb.sortBy( QueryBuilder::tabGenre, QueryBuilder::valName );
    return qb.run();
}


TQStringList
CollectionDB::yearList( bool withUnknowns, bool withCompilations )
{
    QueryBuilder qb;
    qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );

    if ( !withUnknowns )
        qb.excludeMatch( QueryBuilder::tabYear, i18n( "Unknown" ) );
    if ( !withCompilations )
        qb.setOptions( QueryBuilder::optNoCompilations );

    qb.setOptions( QueryBuilder::optRemoveDuplicates );
    qb.sortBy( QueryBuilder::tabYear, QueryBuilder::valName );
    return qb.run();
}


TQStringList
CollectionDB::albumListOfArtist( const TQString &artist, bool withUnknown, bool withCompilations )
{
    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql)
    {
        return query( "SELECT DISTINCT album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
                      "tags.album = album.id AND tags.artist = artist.id "
                      "AND artist.name = '" + escapeString( artist ) + "' " +
                      ( withUnknown ? TQString() : "AND album.name <> '' " ) +
                      ( withCompilations ? TQString() : "AND tags.sampler = " + boolF() ) +
                      " ORDER BY lower( album.name );" );
    }
    else
    {
        return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE "
                      "tags.album = album.id AND tags.artist = artist.id "
                      "AND artist.name = '" + escapeString( artist ) + "' " +
                      ( withUnknown ? TQString() : "AND album.name <> '' " ) +
                      ( withCompilations ? TQString() : "AND tags.sampler = " + boolF() ) +
                      " ORDER BY lower( album.name );" );
    }
}


TQStringList
CollectionDB::artistAlbumList( bool withUnknown, bool withCompilations )
{
    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql)
    {
        return query( "SELECT DISTINCT artist.name, album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
                      "tags.album = album.id AND tags.artist = artist.id " +
                      ( withUnknown ? TQString() : "AND album.name <> '' AND artist.name <> '' " ) +
                      ( withCompilations ? TQString() : "AND tags.sampler = " + boolF() ) +
                      " ORDER BY lower( album.name );" );
    }
    else
    {
        return query( "SELECT DISTINCT artist.name, album.name FROM tags, album, artist WHERE "
                      "tags.album = album.id AND tags.artist = artist.id " +
                      ( withUnknown ? TQString() : "AND album.name <> '' AND artist.name <> '' " ) +
                      ( withCompilations ? TQString() : "AND tags.sampler = " + boolF() ) +
                      " ORDER BY lower( album.name );" );
    }
}


bool
CollectionDB::addSong( MetaBundle* bundle, const bool incremental, DbConnection *conn )
{
    if ( !TQFileInfo( bundle->url().path() ).isReadable() ) return false;

    TQString command = "INSERT INTO tags_temp "
                      "( url, dir, createdate, album, artist, genre, year, title, comment, track, sampler, length, bitrate, samplerate ) "
                      "VALUES ('";

    TQString artist = bundle->artist();
    TQString title = bundle->title();
    if ( title.isEmpty() )
    {
        title = bundle->url().fileName();
        if ( bundle->url().fileName().find( '-' ) > 0 )
        {
            if ( artist.isEmpty() ) artist = bundle->url().fileName().section( '-', 0, 0 ).stripWhiteSpace();
            title = bundle->url().fileName().section( '-', 1 ).stripWhiteSpace();
            title = title.left( title.findRev( '.' ) ).stripWhiteSpace();
            if ( title.isEmpty() ) title = bundle->url().fileName();
        }
    }
    bundle->setArtist( artist );
    bundle->setTitle( title );

    command += escapeString( bundle->url().path() ) + "','";
    command += escapeString( bundle->url().directory() ) + "',";
    command += TQString::number( TQFileInfo( bundle->url().path() ).lastModified().toTime_t() ) + ",";

    command += escapeString( TQString::number( albumID( bundle->album(),   true, !incremental, false, conn ) ) ) + ",";
    command += escapeString( TQString::number( artistID( bundle->artist(), true, !incremental, false, conn ) ) ) + ",";
    command += escapeString( TQString::number( genreID( bundle->genre(),   true, !incremental, false, conn ) ) ) + ",'";
    command += escapeString( TQString::number( yearID( bundle->year(),     true, !incremental, false, conn ) ) ) + "','";

    command += escapeString( bundle->title() ) + "','";
    command += escapeString( bundle->comment() ) + "', ";
    command += ( bundle->track().isEmpty() ? "NULL" : escapeString( bundle->track() ) ) + " , ";
    command += artist == i18n( "Various Artists" ) ? boolT() + "," : boolF() + ",";

    // NOTE any of these may be -1 or -2, this is what we want
    //      see MetaBundle::Undetermined
    command += TQString::number( bundle->length() ) + ",";
    command += TQString::number( bundle->bitrate() ) + ",";
    command += TQString::number( bundle->sampleRate() ) + ")";

    //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
    // Now it might be possible as insert returns the rowid.
    insert( command, NULL, conn );
    return true;
}


static void
fillInBundle( TQStringList values, MetaBundle &bundle )
{
    //TODO use this whenever possible

    // crash prevention
    while( values.count() != 10 )
        values += "IF YOU CAN SEE THIS THERE IS A BUG!";

    TQStringList::ConstIterator it = values.begin();

    bundle.setAlbum     ( *it ); ++it;
    bundle.setArtist    ( *it ); ++it;
    bundle.setGenre     ( *it ); ++it;
    bundle.setTitle     ( *it ); ++it;
    bundle.setYear      ( *it ); ++it;
    bundle.setComment   ( *it ); ++it;
    bundle.setTrack     ( *it ); ++it;
    bundle.setBitrate   ( (*it).toInt() ); ++it;
    bundle.setLength    ( (*it).toInt() ); ++it;
    bundle.setSampleRate( (*it).toInt() );
}

bool
CollectionDB::bundleForUrl( MetaBundle* bundle )
{
    TQStringList values = query( TQString(
            "SELECT album.name, artist.name, genre.name, tags.title, "
            "year.name, tags.comment, tags.track, tags.bitrate, tags.length, "
            "tags.samplerate "
            "FROM tags, album, artist, genre, year "
            "WHERE album.id = tags.album AND artist.id = tags.artist AND "
            "genre.id = tags.genre AND year.id = tags.year AND tags.url = '%1';" )
                .arg( escapeString( bundle->url().path() ) ) );

    if ( !values.empty() )
        fillInBundle( values, *bundle );

    return !values.isEmpty();
}


TQValueList<MetaBundle>
CollectionDB::bundlesByUrls( const KURL::List& urls )
{
    typedef TQValueList<MetaBundle> BundleList;
    BundleList bundles;
    TQStringList paths;
    QueryBuilder qb;

    for( KURL::List::ConstIterator it = urls.begin(), end = urls.end(), last = urls.fromLast(); it != end; ++it )
    {
        // non file stuff won't exist in the db, but we still need to
        // re-insert it into the list we return, just with no tags assigned
        paths += (*it).protocol() == "file" ? (*it).path() : (*it).url();

        if( paths.count() == 50 || it == last )
        {
            qb.clear();

            qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
            qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
            qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
            qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBitrate );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valLength );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valSamplerate );
            qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );

            qb.addURLFilters( paths );
            qb.setOptions( QueryBuilder::optRemoveDuplicates );

            const TQStringList values = qb.run();

            BundleList buns50;
            MetaBundle b;
            foreach( values )
            {
                b.setAlbum     (    *it );
                b.setArtist    (  *++it );
                b.setGenre     (  *++it );
                b.setTitle     (  *++it );
                b.setYear      (  *++it );
                b.setComment   (  *++it );
                b.setTrack     (  *++it );
                b.setBitrate   ( (*++it).toInt() );
                b.setLength    ( (*++it).toInt() );
                b.setSampleRate( (*++it).toInt() );
                b.setPath      (  *++it );

                buns50.append( b );
            }

            // we get no guarantee about the order that the database
            // will return our values, and sqlite indeed doesn't return
            // them in the desired order :( (MySQL does though)
            foreach( paths ) {
                for( BundleList::Iterator jt = buns50.begin(), end = buns50.end(); jt != end; ++jt )
                    if ( (*jt).url().path() == (*it) ) {
                        bundles += *jt;
                        buns50.remove( jt );
                        goto success;
                    }

                // if we get here, we didn't find an entry
                debug() << "No bundle recovered for: " << *it << endl;
                b = MetaBundle();
                b.setUrl( KURL::fromPathOrURL(*it) );
                bundles += b;

            success: ;
            }

            paths.clear();
        }
    }

    return bundles;
}


void
CollectionDB::addAudioproperties( const MetaBundle& bundle )
{
    query( TQString( "UPDATE tags SET bitrate='%1', length='%2', samplerate='%3' WHERE url='%4';" )
                    .arg( bundle.bitrate() )
                    .arg( bundle.length() )
                    .arg( bundle.sampleRate() )
                    .arg( escapeString( bundle.url().path() ) ) );
}


int
CollectionDB::addSongPercentage( const TQString &url, int percentage )
{
    float score;
    TQStringList values =
        query( TQString(
            "SELECT playcounter, createdate, percentage FROM statistics "
            "WHERE url = '%1';" )
            .arg( escapeString( url ) ) );

    // check boundaries
    if ( percentage > 100 ) percentage = 100;
    if ( percentage < 1 )   percentage = 1;

    if ( !values.isEmpty() )
    {
        // entry exists, increment playcounter and update accesstime
        score = ( ( values[2].toDouble() * values.first().toInt() ) + percentage ) / ( values.first().toInt() + 1 );

        if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
            query( TQString( "UPDATE statistics SET percentage=%1, playcounter=%2+1 WHERE url='%3';" )
                            .arg( score )
                            .arg( values[0] + " + 1" )
                            .arg( escapeString( url ) ) );
        }
        else
        {
            query( TQString( "REPLACE INTO statistics ( url, createdate, accessdate, percentage, playcounter ) "
                            "VALUES ( '%1', %2, %3, %4, %5 );" )
                            .arg( escapeString( url ) )
                            .arg( values[1] )
                            .arg( TQDateTime::currentDateTime().toTime_t() )
                            .arg( score )
                            .arg( values[0] + " + 1" ) );
        }
    }
    else
    {
        // entry didn't exist yet, create a new one
        score = ( ( 50 + percentage ) / 2 );

        insert( TQString( "INSERT INTO statistics ( url, createdate, accessdate, percentage, playcounter ) "
                        "VALUES ( '%1', %2, %3, %4, 1 );" )
                        .arg( escapeString( url ) )
                        .arg( TQDateTime::currentDateTime().toTime_t() )
                        .arg( TQDateTime::currentDateTime().toTime_t() )
                        .arg( score ), NULL );
    }

    int iscore = getSongPercentage( url );
    emit scoreChanged( url, iscore );
    return iscore;
}


int
CollectionDB::getSongPercentage( const TQString &url  )
{
    TQStringList values = query( TQString( "SELECT round( percentage + 0.4 ) FROM statistics WHERE url = '%1';" )
                                         .arg( escapeString( url ) ) );

    if( values.count() )
        return values.first().toInt();

    return 0;
}


void
CollectionDB::setSongPercentage( const TQString &url , int percentage )
{
    TQStringList values =
        query( TQString(
            "SELECT playcounter, createdate, accessdate FROM statistics WHERE url = '%1';" )
            .arg( escapeString( url ) ) );

    // check boundaries
    if ( percentage > 100 ) percentage = 100;
    if ( percentage < 1 )   percentage = 1;

    if ( !values.isEmpty() )
    {
        if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
            query( TQString( "UPDATE statistics SET percentage=%1 WHERE url='%2';" )
                            .arg( percentage )
                            .arg( escapeString( url ) ) );
        }
        else
        {
            // entry exists
            query( TQString( "REPLACE INTO statistics ( url, createdate, accessdate, percentage, playcounter ) "
                            "VALUES ( '%1', '%2', '%3', %4, %5 );" )
                            .arg( escapeString( url ) )
                            .arg( values[1] )
                            .arg( values[2] )
                            .arg( percentage )
                            .arg( values[0] ) );
        }
    }
    else
    {
        insert( TQString( "INSERT INTO statistics ( url, createdate, accessdate, percentage, playcounter ) "
                         "VALUES ( '%1', %2, %3, %4, 0 );" )
                        .arg( escapeString( url ) )
                        .arg( TQDateTime::currentDateTime().toTime_t() )
                        .arg( TQDateTime::currentDateTime().toTime_t() )
                        .arg( percentage ), NULL );
    }

    emit scoreChanged( url, percentage );
}


void
CollectionDB::updateDirStats( TQString path, const long datetime, DbConnection *conn )
{
    if ( path.endsWith( "/" ) )
        path = path.left( path.length() - 1 );

    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        query( TQString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%3';")
                        .arg( conn ? "_temp" : "" )
                        .arg( datetime )
                        .arg( escapeString( path ) ), conn );
    }
    else
    {
        query( TQString( "REPLACE INTO directories%1 ( dir, changedate ) VALUES ( '%3', %2 );" )
                  .arg( conn ? "_temp" : "" )
                  .arg( datetime )
                  .arg( escapeString( path ) ),
                  conn );
    }
}


void
CollectionDB::removeSongsInDir( TQString path )
{
    if ( path.endsWith( "/" ) )
        path = path.left( path.length() - 1 );

    query( TQString( "DELETE FROM tags WHERE dir = '%1';" )
              .arg( escapeString( path ) ) );
}


bool
CollectionDB::isDirInCollection( TQString path )
{
    if ( path.endsWith( "/" ) )
        path = path.left( path.length() - 1 );

    TQStringList values =
        query( TQString( "SELECT changedate FROM directories WHERE dir = '%1';" )
                  .arg( escapeString( path ) ) );

    return !values.isEmpty();
}


bool
CollectionDB::isFileInCollection( const TQString &url  )
{
    TQStringList values =
        query( TQString( "SELECT url FROM tags WHERE url = '%1';" )
                  .arg( escapeString( url ) ) );

    return !values.isEmpty();
}


void
CollectionDB::removeSongs( const KURL::List& urls )
{
    for( KURL::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it )
    {
        query( TQString( "DELETE FROM tags WHERE url = '%1';" )
            .arg( escapeString( (*it).path() ) ) );
    }
}


TQStringList
CollectionDB::similarArtists( const TQString &artist, uint count )
{
    TQStringList values;

    if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        values = query( TQString( "SELECT suggestion FROM related_artists WHERE artist = '%1' OFFSET 0 LIMIT %2;" )
                                 .arg( escapeString( artist ) ).arg( count ) );
    }
    else
    {
        values = query( TQString( "SELECT suggestion FROM related_artists WHERE artist = '%1' LIMIT 0, %2;" )
                                 .arg( escapeString( artist ) ).arg( count ) );
    }

    if ( values.isEmpty() )
        Scrobbler::instance()->similarArtists( artist );

    return values;
}


void
CollectionDB::checkCompilations( const TQString &path, const bool temporary, DbConnection *conn )
{
    TQStringList albums;
    TQStringList artists;
    TQStringList dirs;

    albums = query( TQString( "SELECT DISTINCT album.name FROM tags_temp, album%1 AS album WHERE tags_temp.dir = '%2' AND album.id = tags_temp.album;" )
              .arg( temporary ? "_temp" : "" )
              .arg( escapeString( path ) ), conn );

    for ( uint i = 0; i < albums.count(); i++ )
    {
        if ( albums[ i ].isEmpty() ) continue;

        const uint album_id = albumID( albums[ i ], false, temporary, false, conn );
        artists = query( TQString( "SELECT DISTINCT artist.name FROM tags_temp, artist%1 AS artist WHERE tags_temp.album = '%2' AND tags_temp.artist = artist.id;" )
                            .arg( temporary ? "_temp" : "" )
                            .arg( album_id ), conn );
        dirs    = query( TQString( "SELECT DISTINCT dir FROM tags_temp WHERE album = '%1';" )
                            .arg( album_id ), conn );

        if ( artists.count() > dirs.count() )
        {
            debug() << "Detected compilation: " << albums[ i ] << " - " << artists.count() << ":" << dirs.count() << endl;
            query( TQString( "UPDATE tags_temp SET sampler = %1 WHERE album = '%2';" )
                            .arg(boolT()).arg( album_id ), conn );
        }
    }
}


void
CollectionDB::setCompilation( const TQString &album, const bool enabled, const bool updateView )
{
    query( TQString( "UPDATE tags, album SET tags.sampler = %1 WHERE tags.album = album.id AND album.name = '%2';" )
              .arg( enabled ? "1" : "0" )
              .arg( escapeString( album ) ) );

    // Update the Collection-Browser view,
    // using TQTimer to make sure we don't manipulate the GUI from a thread
    if ( updateView )
        TQTimer::singleShot( 0, CollectionView::instance(), TQT_SLOT( renderView() ) );
}


void
CollectionDB::removeDirFromCollection( TQString path )
{
    if ( path.endsWith( "/" ) )
        path = path.left( path.length() - 1 );

    query( TQString( "DELETE FROM directories WHERE dir = '%1';" )
                    .arg( escapeString( path ) ) );
}


void
CollectionDB::updateTags( const TQString &url, const MetaBundle &bundle, const bool updateView )
{
    TQString command = "UPDATE tags SET ";
    command += "title = '" + escapeString( bundle.title() ) + "', ";
    command += "artist = " + TQString::number( artistID( bundle.artist(), true, false, true ) ) + ", ";
    command += "album = "  + TQString::number( albumID( bundle.album(), true, false, true ) ) + ", ";
    command += "genre = "  + TQString::number( genreID( bundle.genre(), true, false, true ) ) + ", ";
    command += "year = "   + TQString::number( yearID( bundle.year(), true, false, true ) ) + ", ";
    if ( !bundle.track().isEmpty() )
        command += "track = " + bundle.track() + ", ";
    command += "comment = '" + escapeString( bundle.comment() ) + "' ";
    command += "WHERE url = '" + escapeString( url ) + "';";

    query( command );

    if ( EngineController::instance()->bundle().url() == bundle.url() )
    {
        debug() << "Current song edited, updating widgets: " << bundle.title() << endl;
        EngineController::instance()->currentTrackMetaDataChanged( bundle );
    }

    // Update the Collection-Browser view,
    // using TQTimer to make sure we don't manipulate the GUI from a thread
    if ( updateView )
        TQTimer::singleShot( 0, CollectionView::instance(), TQT_SLOT( renderView() ) );
}


void
CollectionDB::updateURL( const TQString &url, const bool updateView )
{
    // don't use the KURL ctor as it checks the db first
    MetaBundle bundle;
    bundle.setPath( url );
    bundle.readTags( TagLib::AudioProperties::Fast );

    updateTags( url, bundle, updateView );
}


void
CollectionDB::applySettings()
{
    bool recreateConnections = false;
    if ( AmarokConfig::databaseEngine().toInt() != m_dbConnPool->getDbConnectionType() )
    {
        recreateConnections = true;
    }
    else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
    {
        // Using MySQL, so check if MySQL settings were changed
        const MySqlConfig *config =
            static_cast<const MySqlConfig*> ( m_dbConnPool->getDbConfig() );
        if ( AmarokConfig::mySqlHost() != config->host() )
        {
            recreateConnections = true;
        }
        else if ( AmarokConfig::mySqlPort() != config->port() )
        {
            recreateConnections = true;
        }
        else if ( AmarokConfig::mySqlDbName() != config->database() )
        {
            recreateConnections = true;
        }
        else if ( AmarokConfig::mySqlUser() != config->username() )
        {
            recreateConnections = true;
        }
        else if ( AmarokConfig::mySqlPassword() != config->password() )
        {
            recreateConnections = true;
        }
    }
    else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
    {
      const PostgresqlConfig *config =
          static_cast<const PostgresqlConfig*> ( m_dbConnPool->getDbConfig() );
      if ( AmarokConfig::postgresqlConninfo() != config->conninfo() )
      {
          recreateConnections = true;
      }
    }
    if ( recreateConnections )
    {
        debug()
            << "Database engine settings changed: "
            << "recreating DbConnections" << endl;
        // If Database engine was changed, recreate DbConnections.
        destroy();
        initialize();
        CollectionView::instance()->renderView();
        emit databaseEngineChanged();
    }
}


//////////////////////////////////////////////////////////////////////////////////////////
// PROTECTED
//////////////////////////////////////////////////////////////////////////////////////////

TQCString
CollectionDB::md5sum( const TQString& artist, const TQString& album, const TQString& file )
{
    KMD5 context( artist.lower().local8Bit() + album.lower().local8Bit() + file.local8Bit() );
//     debug() << "MD5 SUM for " << artist << ", " << album << ": " << context.hexDigest() << endl;
    return context.hexDigest();
}


void CollectionDB::engineTrackEnded( int finalPosition, int trackLength )
{
    //This is where percentages are calculated
    //TODO statistics are not calculated when currentTrack doesn't exist

    // Don't update statistics if song has been played for less than 15 seconds
    // if ( finalPosition < 15000 ) return;

    const KURL url = EngineController::instance()->bundle().url();
    if ( url.path().isEmpty() ) return;

    // sanity check
    if ( finalPosition > trackLength || finalPosition <= 0 )
        finalPosition = trackLength;

    int pct = (int) ( ( (double) finalPosition / (double) trackLength ) * 100 );

    // increase song counter & calculate new statistics
    addSongPercentage( url.path(), pct );
}


void
CollectionDB::timerEvent( TQTimerEvent* )
{
    if ( AmarokConfig::monitorChanges() )
        scanMonitor();
}


//////////////////////////////////////////////////////////////////////////////////////////
// PUBLIC SLOTS
//////////////////////////////////////////////////////////////////////////////////////////

void
CollectionDB::fetchCover( TQWidget* parent, const TQString& artist, const TQString& album, bool noedit ) //SLOT
{
    #ifdef AMAZON_SUPPORT
    debug() << "Fetching cover for " << artist << " - " << album << endl;

    CoverFetcher* fetcher = new CoverFetcher( parent, artist, album );
    connect( fetcher, TQT_SIGNAL(result( CoverFetcher* )), TQT_SLOT(coverFetcherResult( CoverFetcher* )) );
    fetcher->setUserCanEditQuery( !noedit );
    fetcher->startFetch();
    #endif
}


void
CollectionDB::scanMonitor()  //SLOT
{
    scanModifiedDirs();
}


void
CollectionDB::startScan()  //SLOT
{
    TQStringList folders = MountPointManager::instance()->collectionFolders();

    if ( folders.isEmpty() ) {
        dropTables();
        createTables();
    }
    else if( PlaylistBrowser::instance() )
    {
        emit scanStarted();

        ThreadWeaver::instance()->queueJob( new CollectionReader( this, folders ) );
    }
}


void
CollectionDB::stopScan() //SLOT
{
    ThreadWeaver::instance()->abortAllJobsNamed( "CollectionReader" );
}


//////////////////////////////////////////////////////////////////////////////////////////
// PRIVATE SLOTS
//////////////////////////////////////////////////////////////////////////////////////////

void
CollectionDB::dirDirty( const TQString& path )
{
    debug() << k_funcinfo << "Dirty: " << path << endl;

    ThreadWeaver::instance()->queueJob( new CollectionReader( this, path ) );
}


void
CollectionDB::coverFetcherResult( CoverFetcher *fetcher )
{
    if( fetcher->wasError() ) {
        error() << fetcher->errors() << endl;
        emit coverFetcherError( fetcher->errors().front() );
    }

    else {
        setAlbumImage( fetcher->artist(), fetcher->album(), fetcher->image(), fetcher->amazonURL() );
        emit coverFetched( fetcher->artist(), fetcher->album() );
    }
}

/**
 * This query is fairly slow with sqlite, and often happens just
 * after the OSD is shown. Threading it restores responsivity.
 */
class SimilarArtistsInsertionJob : public ThreadWeaver::DependentJob
{
    virtual bool doJob()
    {
        CollectionDB::instance()->query( TQString( "DELETE FROM related_artists WHERE artist = '%1';" ).arg( escapedArtist ) );

        const TQString sql = "INSERT INTO related_artists ( artist, suggestion, changedate ) VALUES ( '%1', '%2', 0 );";
        foreach( suggestions )
            CollectionDB::instance()->insert( sql
                    .arg( escapedArtist )
                    .arg( CollectionDB::instance()->escapeString( *it ) ), NULL );

        return true;
    }

    virtual void completeJob() { emit CollectionDB::instance()->similarArtistsFetched( artist ); }

    const TQString artist;
    const TQString escapedArtist;
    const TQStringList suggestions;

public:
    SimilarArtistsInsertionJob( CollectionDB *parent, const TQString &s, const TQStringList &list )
            : ThreadWeaver::DependentJob( parent, "SimilarArtistsInsertionJob" )
            , artist( s )
            , escapedArtist( parent->escapeString( s ) )
            , suggestions( list )
    {}
};

void
CollectionDB::similarArtistsFetched( const TQString& artist, const TQStringList& suggestions )
{
    debug() << "Received similar artists\n";

    ThreadWeaver::instance()->queueJob( new SimilarArtistsInsertionJob( this, artist, suggestions ) );
}


//////////////////////////////////////////////////////////////////////////////////////////
// PRIVATE
//////////////////////////////////////////////////////////////////////////////////////////


void
CollectionDB::initialize()
{
    m_dbConnPool = new DbConnectionPool();
    DbConnection *dbConn = m_dbConnPool->getDbConnection();
    m_dbConnPool->putDbConnection( dbConn );

    KConfig* config = amaroK::config( "Collection Browser" );
    if(!dbConn->isConnected())
        amaroK::MessageQueue::instance()->addMessage(dbConn->lastError());
    if ( !dbConn->isInitialized() || !isValid() )
    {
        createTables();
        createStatsTable();
    }
    else
    {
        //remove database file if version is incompatible
        if ( config->readNumEntry( "Database Version", 0 ) != DATABASE_VERSION )
        {
            debug() << "Rebuilding database!" << endl;
            dropTables();
            createTables();
        }
        if ( config->readNumEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION )
        {
            debug() << "Rebuilding stats-database!" << endl;
            dropStatsTable();
            createStatsTable();
        }
    }

    m_dbConnPool->createDbConnections();
}


void
CollectionDB::destroy()
{
    delete m_dbConnPool;
}


void
CollectionDB::scanModifiedDirs()
{
    //we check if a job is pending because we don't want to abort incremental collection readings
    if ( !ThreadWeaver::instance()->isJobPending( "CollectionReader" ) && PlaylistBrowser::instance() ) {
        emit scanStarted();
        ThreadWeaver::instance()->onlyOneJob( new IncrementalCollectionReader( this ) );
    }
}


void
CollectionDB::customEvent( TQCustomEvent *e )
{
    if ( e->type() == (int)CollectionReader::JobFinishedEvent )
        emit scanDone( static_cast<ThreadWeaver::Job*>(e)->wasSuccessful() );
}


//////////////////////////////////////////////////////////////////////////////////////////
// CLASS DbConnectionPool
//////////////////////////////////////////////////////////////////////////////////////////

DbConnectionPool::DbConnectionPool() : m_semaphore( POOL_SIZE )
{
#ifdef USE_MYSQL
    if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
        m_dbConnType = DbConnection::mysql;
    else
#endif
#ifdef USE_POSTGRESQL
    if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
        m_dbConnType = DbConnection::postgresql;
    else
#endif
        m_dbConnType = DbConnection::sqlite;

    m_semaphore += POOL_SIZE;
    DbConnection *dbConn;

#ifdef USE_MYSQL
    if ( m_dbConnType == DbConnection::mysql )
    {
        m_dbConfig =
            new MySqlConfig(
                AmarokConfig::mySqlHost(),
                AmarokConfig::mySqlPort(),
                AmarokConfig::mySqlDbName(),
                AmarokConfig::mySqlUser(),
                AmarokConfig::mySqlPassword() );
        dbConn = new MySqlConnection( static_cast<MySqlConfig*> ( m_dbConfig ) );
    }
    else
#endif
#ifdef USE_POSTGRESQL
    if ( m_dbConnType == DbConnection::postgresql )
    {
        m_dbConfig =
            new PostgresqlConfig(
                AmarokConfig::postgresqlConninfo() );
        dbConn = new PostgresqlConnection( static_cast<PostgresqlConfig*> ( m_dbConfig ) );
    }
    else
#endif
    {
        m_dbConfig = new SqliteConfig( "collection.db" );
        dbConn = new SqliteConnection( static_cast<SqliteConfig*> ( m_dbConfig ) );
    }
    enqueue( dbConn );
    m_semaphore--;
    debug() << "Available db connections: " << m_semaphore.available() << endl;
}


DbConnectionPool::~DbConnectionPool()
{
    m_semaphore += POOL_SIZE;
    DbConnection *conn;
    bool vacuum = true;

    while ( ( conn = dequeue() ) != 0 )
    {
        if ( m_dbConnType == DbConnection::sqlite && vacuum )
        {
            vacuum = false;
            debug() << "Running VACUUM" << endl;
            conn->query( "VACUUM; ");
        }

        delete conn;
    }

    delete m_dbConfig;
}


void DbConnectionPool::createDbConnections()
{
    for ( int i = 0; i < POOL_SIZE - 1; i++ )
    {
        DbConnection *dbConn;

#ifdef USE_MYSQL
        if ( m_dbConnType == DbConnection::mysql )
            dbConn = new MySqlConnection( static_cast<MySqlConfig*> ( m_dbConfig ) );
        else
#endif
#ifdef USE_POSTGRESQL
        if ( m_dbConnType == DbConnection::postgresql )
            dbConn = new PostgresqlConnection( static_cast<PostgresqlConfig*> ( m_dbConfig ) );
        else
#endif
            dbConn = new SqliteConnection( static_cast<SqliteConfig*> ( m_dbConfig ) );
        enqueue( dbConn );
        m_semaphore--;
    }
    debug() << "Available db connections: " << m_semaphore.available() << endl;
}


DbConnection *DbConnectionPool::getDbConnection()
{
    m_semaphore++;
    return dequeue();
}


void DbConnectionPool::putDbConnection( const DbConnection *conn )
{
    enqueue( conn );
    m_semaphore--;
}


#include "collectiondb.moc"