collectiondb.cpp

来自「Amarok是一款在LINUX或其他类UNIX操作系统中运行的音频播放器软件。 」· C++ 代码 · 共 1,976 行 · 第 1/5 页

// (c) 2004 Mark Kretschmann <[e-mail]>
// (c) 2004 Christian Muehlhaeuser <[e-mail]>
// (c) 2004 Sami Nieminen <[e-mail]>
// (c) 2005 Ian Monroe <[e-mail]>
// 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 <qfile.h>
#include <qimage.h>
#include <qtimer.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
    QString 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(), SIGNAL( similarArtistsFetched( const QString&, const QStringList& ) ),
             this,                  SLOT( similarArtistsFetched( const QString&, const QStringList& ) ) );
}


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 QStringList() on error.
 */
QStringList
CollectionDB::query( const QString& 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();
    }

    QStringList 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 QString& statement, const QString& 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()
{
    QStringList 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()
{
    QStringList values1;
    QStringList 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( QString( "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 );

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

        albumAutoIncrement = QString("DEFAULT nextval('album_seq')");
        artistAutoIncrement = QString("DEFAULT nextval('artist_seq')");
        genreAutoIncrement = QString("DEFAULT nextval('genre_seq')");
        yearAutoIncrement = QString("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( QString( "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( QString( "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( QString( "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( QString( "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( QString( "CREATE %1 TABLE images%2 ("
                    "path " + textColumnType() + ","
                    "artist " + textColumnType() + ","
                    "album " + textColumnType() + ");" )
                    .arg( conn ? "TEMPORARY" : "" )
                    .arg( conn ? "_temp" : "" ), conn );

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


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

    if ( !conn )
    {
        // create related artists cache
        query( QString( "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( QString( "DROP TABLE tags%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE album%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE artist%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE genre%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE year%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE images%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE directories%1;" ).arg( conn ? "_temp" : "" ), conn );
    if ( !conn )
    {