collectiondb.cpp

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

    dbConn = getMyConnection();

    QStringList values = dbConn->query( statement, suppressDebug );
    if ( DEBUG )
    {
        clock_t finish = clock();
        const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
        debug() << "SQL-query (" << duration << "s): " << statement << endl;
    }
    m_mutex.unlock();
    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 )
{
    m_mutex.lock();
    clock_t start;
    if ( DEBUG )
    {
        debug() << "insert-start: " << statement << endl;
        start = clock();
    }

    DbConnection *dbConn;
    dbConn = getMyConnection();

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

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

QString
CollectionDB::deviceidSelection( const bool showAll )
{
    if ( !showAll )
    {
        IdList list = MountPointManager::instance()->getMountedDeviceIds();
        QString deviceIds = "";
        foreachType( IdList, list )
        {
            if ( it != list.begin() ) deviceIds += ',';
            deviceIds += QString::number(*it);
        }
        return " AND tags.deviceid IN (" + deviceIds + ')';
    }
    else return "";
}

QStringList
CollectionDB::URLsFromQuery( const QStringList &result ) const
{
    QStringList values;
    foreach( result )
    {
        const int id = (*it).toInt();
        values << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
    }
    return values;
}

KURL::List
CollectionDB::URLsFromSqlDrag( const QStringList &values ) const
{
    KURL::List urls;
    for( QStringList::const_iterator it = values.begin();
            it != values.end();
            it++ )
    {
        const QString &rel = *it;
        it++;
        int id = (*it).toInt();
        urls += KURL::fromPathOrURL( MountPointManager::instance()->getAbsolutePath( id, rel ) );
        for( int i = 0;
                i < QueryBuilder::dragFieldCount-1 && it != values.end();
                i++ )
            it++;
    }

    return urls;
}

bool
CollectionDB::isEmpty( )
{
    QStringList values;

    values = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );

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


bool
CollectionDB::isValid( )
{
    QStringList values1;
    QStringList values2;
    QStringList values3;
    QStringList values4;
    QStringList values5;

    values1 = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
    values2 = query( "SELECT COUNT( url ) FROM statistics LIMIT 1 OFFSET 0;" );
    values3 = query( "SELECT COUNT( url ) FROM podcastchannels LIMIT 1 OFFSET 0;" );
    values4 = query( "SELECT COUNT( url ) FROM podcastepisodes LIMIT 1 OFFSET 0;" );
    values5 = query( "SELECT COUNT( id ) FROM devices LIMIT 1 OFFSET 0;" );

    //It's valid as long as we've got _some_ tables that have something in.
    return !( values1.isEmpty() && values2.isEmpty() && values3.isEmpty() && values4.isEmpty() && values5.isEmpty() );
}


QString
CollectionDB::adminValue( QString noption ) {
    QStringList values;
    values = query (
        QString( "SELECT value FROM admin WHERE noption = '%1';").arg(noption)
    );
    return values.isEmpty() ? "" : values.first();
}


void
CollectionDB::setAdminValue( QString noption, QString value ) {

    QStringList values = query( QString( "SELECT value FROM admin WHERE noption = '%1';").arg( noption ));
    if(values.count() > 0)
    {
        query( QString( "UPDATE admin SET value = '%1' WHERE noption = '%2';" ).arg( value, noption ) );
    }
    else
    {
        insert( QString( "INSERT INTO admin (value, noption) values ( '%1', '%2' );" ).arg( value, noption ),
         NULL );
    }
}



void
CollectionDB::createTables( const bool temporary )
{
    DEBUG_BLOCK

    //create tag table
    query( QString( "CREATE %1 TABLE tags%2 ("
                    "url " + exactTextColumnType() + ","
                    "dir " + exactTextColumnType() + ","
                    "createdate INTEGER,"
                    "modifydate INTEGER,"
                    "album INTEGER,"
                    "artist INTEGER,"
                    "composer INTEGER,"
                    "genre INTEGER,"
                    "title " + textColumnType() + ","
                    "year INTEGER,"
                    "comment " + longTextColumnType() + ","
                    "track NUMERIC(4),"
                    "discnumber INTEGER,"
                    "bitrate INTEGER,"
                    "length INTEGER,"
                    "samplerate INTEGER,"
                    "filesize INTEGER,"
                    "filetype INTEGER,"
                    "sampler BOOL,"
                    "bpm FLOAT,"
                    "deviceid INTEGER);" )
                    .arg( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" ) );

    QString albumAutoIncrement = "";
    QString artistAutoIncrement = "";
    QString composerAutoIncrement = "";
    QString genreAutoIncrement = "";
    QString yearAutoIncrement = "";
    if ( getDbConnectionType() == DbConnection::postgresql )
    {
        if(!temporary)
        {
            query( QString( "CREATE SEQUENCE album_seq;" ) );
            query( QString( "CREATE SEQUENCE artist_seq;" ) );
            query( QString( "CREATE SEQUENCE composer_seq;" ) );
            query( QString( "CREATE SEQUENCE genre_seq;" ) );
            query( QString( "CREATE SEQUENCE year_seq;" ) );
        }

        albumAutoIncrement = QString("DEFAULT nextval('album_seq')");
        artistAutoIncrement = QString("DEFAULT nextval('artist_seq')");
        composerAutoIncrement = QString("DEFAULT nextval('composer_seq')");
        genreAutoIncrement = QString("DEFAULT nextval('genre_seq')");
        yearAutoIncrement = QString("DEFAULT nextval('year_seq')");
    }
    else if ( getDbConnectionType() == DbConnection::mysql )
    {
        albumAutoIncrement = "AUTO_INCREMENT";
        artistAutoIncrement = "AUTO_INCREMENT";
        composerAutoIncrement = "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( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" )
                    .arg( albumAutoIncrement ) );

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

    //create composer table
    query( QString( "CREATE %1 TABLE composer%2 ("
                    "id INTEGER PRIMARY KEY %3,"
                    "name " + textColumnType() + ");" )
                    .arg( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" )
                    .arg( composerAutoIncrement ) );

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

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

    //create images table
    query( QString( "CREATE %1 TABLE images%2 ("
                    "path " + exactTextColumnType() + ","
                    "deviceid INTEGER,"
                    "artist " + textColumnType() + ","
                    "album " + textColumnType() + ");" )
                    .arg( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" ) );

    //create embed table
    query( QString( "CREATE %1 TABLE embed%2 ("
                    "url " + exactTextColumnType() + ","
                    "deviceid INTEGER,"
                    "hash " + exactTextColumnType() + ","
                    "description " + textColumnType() + ");" )
                    .arg( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" ) );

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

    //create uniqueid table
    query( QString( "CREATE %1 TABLE uniqueid%2 ("
                    "url " + exactTextColumnType() + ","
                    "deviceid INTEGER,"
                    "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
                    "dir " + exactTextColumnType() + ");" )
                    .arg( temporary ? "TEMPORARY" : "" )
                    .arg( temporary ? "_temp" : "" ) );

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

    if ( !temporary )
    {
        //create admin table -- holds the db version, put here other stuff if necessary
        query( QString( "CREATE TABLE admin ("
                    "noption " + textColumnType() + ", "
                    "value " + textColumnType() + ");" ) );

        // create related artists cache
        query( QString( "CREATE TABLE related_artists ("
                        "artist " + textColumnType() + ","
                        "suggestion " + textColumnType() + ","
                        "changedate INTEGER );" ) );

        createIndices();
    }
    else
    {
        query( "CREATE UNIQUE INDEX url_tagtemp ON tags_temp( url, deviceid );" );
        query( "CREATE UNIQUE INDEX embed_urltemp ON embed_temp( url, deviceid );" );
        query( "CREATE UNIQUE INDEX dir_temp_dir ON directories_temp( dir, deviceid );" );
    }
}

void
CollectionDB::createIndices()
{
    //This creates the indices for tables created in createTables. It should not refer to
    //tables which are not created in that function.
    debug() << "Creating indices, ignore errors about already existing indices" << endl;

    query( "CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );" );
    query( "CREATE INDEX album_tag ON tags( album );" );
    query( "CREATE INDEX artist_tag ON tags( artist );" );
    query( "CREATE INDEX composer_tag ON tags( composer );" );
    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 images_url ON images( path, deviceid );" );

    query( "CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );" );
    query( "CREATE INDEX embed_hash ON embed( hash );" );

    query( "CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );" );
    query( "CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );");
    query( "CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );");

    query( "CREATE INDEX album_idx ON album( name );" );
    query( "CREATE INDEX artist_idx ON artist( name );" );
    query( "CREATE INDEX composer_idx ON composer( name );" );
    query( "CREATE INDEX genre_idx ON genre( name );" );
    query( "CREATE INDEX year_idx ON year( name );" );

    query( "CREATE INDEX tags_artist_index ON tags( artist );" );
    query( "CREATE INDEX tags_album_index ON tags( album );" );
    query( "CREATE INDEX tags_deviceid_index ON tags( deviceid ); ");
    query( "CREATE INDEX tags_url_index ON tags( url ); ");

    query( "CREATE INDEX embed_deviceid_index ON embed( deviceid ); ");
    query( "CREATE INDEX embed_url_index ON embed( url ); "); 

    query( "CREATE INDEX related_artists_artist ON related_artists( artist );" );

    debug() << "Finished creating indices, stop ignoring errors" << endl;
}

void
CollectionDB::createPermanentIndices()
{
    //this method creates all indices which are not referred to in createTables
    //this method is called on each startup of amarok
    //until we figure out a way to handle this better it produces SQL errors if the indices
    //already exist, but these can be ignored
    debug() << "Creating permanent indices, ignore errors about already existing indices" << endl;

    query( "CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );" );