path: root/src/collectiondb.cpp

// (c) 2004 Mark Kretschmann <[email protected]>
// (c) 2004 Christian Muehlhaeuser <[email protected]>
// (c) 2004 Sami Nieminen <[email protected]>
// (c) 2005 Ian Monroe <[email protected]>
// See COPYING file for licensing information.


#include "config.h"
#include "klamavconfig.h"
#include "collectiondb.h"
#include "klamav.h"
#include "activityviewer.h"


#include "sqlite/sqlite3.h"


#include <qfile.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 <kdebug.h>
#include <kio/netaccess.h>

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





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

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


CollectionDB::CollectionDB( bool temporary )
        : m_isTemporary( temporary )
{

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

}

CollectionDB::~CollectionDB()
{

    destroy();
}


//////////////////////////////////////////////////////////////////////////////////////////
// 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 )
{
    clock_t start;
    if ( DEBUGSQL )
    {
        kdDebug() << "Query-start: " << statement << endl;
        start = clock();
    }

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

    //kdDebug() << statement << endl;

    QStringList values = dbConn->query( statement );

    //kdDebug() << values << endl;

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

    if ( DEBUGSQL )
    {
        clock_t finish = clock();
        const double duration = (double) (finish - start) / CLOCKS_PER_SEC;
        kdDebug() << "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 )
{
    clock_t start;
    if ( DEBUGSQL )
    {
        kdDebug() << "insert-start: " << statement << endl;
        start = clock();
    }

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

    //kdDebug() << statement << endl;

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

    //kdDebug() << id << endl;

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

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


bool
CollectionDB::isEmpty()
{
    QStringList values;

    values = query( "SELECT COUNT( type ) FROM klamav_activity LIMIT 0, 1;" );

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


bool
CollectionDB::isValid(const QString &column, const QString &table)
{
    QStringList values1;
    QStringList values2;

    values1 = query( QString("SELECT COUNT( %1 ) FROM %2 LIMIT 0, 1;").arg(column).arg(table) );

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


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

    //create tag table
    createActivityTable();
    //createMetaDBTable();

    //create indexes
/*    query( QString( "CREATE INDEX date_idx%1 ON klamav_activity%2( date );" )
                    .arg( conn ? "_temp" : "" ).arg( conn ? "_temp" : "" ), conn );*/
}

void
CollectionDB::createActivityTable( DbConnection *conn )
{

    query( "CREATE TABLE klamav_activity ( date TEXT, type TEXT, event TEXT, file TEXT);", conn );

}

void
CollectionDB::createMetaDBTable( DbConnection *conn )
{

   query( "CREATE TABLE klamav_metadb ( id INTEGER PRIMARY KEY, date TEXT, submission INTEGER, creator TEXT, virus TEXT, alias TEXT, sender TEXT);", conn );
   loadMetaDBTable(conn);

}

void
CollectionDB::loadMetaDBTable(  DbConnection */*conn*/  )
{

	QString location = locate("data", "klamav/about/metadb.txt");

/*    query( QString( ".import %1 klamav_activity;" ).arg(location), conn );

    return;*/
    QFile file( location );
    if ( file.open( IO_ReadOnly ) ) {
        QTextStream stream( &file );
        QString line;
        while ( !stream.atEnd() ) {
            line = stream.readLine(); // line of text excluding '\n'
            QStringList columns = QStringList::split("\t",line.remove("\""));
            if (columns.count() >= 5)
                insertMetaDBEntry(columns[0],columns[1],columns[2],columns[3],columns[4],columns[5]);
        }
        file.close();
    }

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

    query( QString( "DROP TABLE klamav_activity%1;" ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "DROP TABLE klamav_metadb%1;" ).arg( conn ? "_temp" : "" ), conn );
}


void
CollectionDB::clearTables( DbConnection *conn )
{

    QString clearCommand = "DELETE FROM";

    query( QString( "%1 klamav_activity%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );
    query( QString( "%1 klamav_metadb%2;" ).arg( clearCommand ).arg( conn ? "_temp" : "" ), conn );

}


void
CollectionDB::moveTempTables( DbConnection *conn )
{
    insert( "INSERT INTO klamav_activity SELECT * FROM klamav_activity_temp;", NULL, conn );
    insert( "INSERT INTO klamav_metadb SELECT * FROM klamav_activity_temp;", NULL, conn );

}



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

    QStringList values =
        query( QString(
            "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( QString( "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( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
                        .arg( name )
                        .arg( CollectionDB::instance()->escapeString( value ) ), name, conn );

        return id;
    }

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


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


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


QString
CollectionDB::typeCount( const QString &type_id )
{
    QStringList values =
        query( QString(
            "SELECT COUNT( type ) FROM klamav_activity WHERE type = %1;" )
            .arg( type_id ) );
    return values.first();
}


QStringList
CollectionDB::messagesForType( const QString &type_id,  const bool isValue )
{
    if ( isValue)
    {
        return query( QString( "SELECT * FROM klamav_activity "
			"WHERE (type = \"%1\" ) ;" )
                        .arg( type_id ) );
    }
    return "";
}









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





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

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

//    KConfig* config = amaroK::config( "Collection Browser" );
    if(!dbConn->isConnected())
	kdDebug() << "db not connected" << endl;
        //amaroK::MessageQueue::instance()->addMessage(dbConn->lastError());
    if ( !dbConn->isInitialized() )
    {
        createTables();
    }

//     if  (!isValid("id","klamav_metadb"))
//         createMetaDBTable();

    if  (!isValid("type","klamav_activity"))
        createActivityTable();


    m_dbConnPool->createDbConnections();
}


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




//////////////////////////////////////////////////////////////////////////////////////////
// CLASS DbConnection
//////////////////////////////////////////////////////////////////////////////////////////

DbConnection::DbConnection( DbConfig* config )
    : m_config( config )
{}


DbConnection::~DbConnection()
{}


//////////////////////////////////////////////////////////////////////////////////////////
// CLASS SqliteConnection
//////////////////////////////////////////////////////////////////////////////////////////

SqliteConnection::SqliteConnection( SqliteConfig* config )
    : DbConnection( config )
{

    QString homepath = getenv("HOME");
    const QCString path = (homepath+"/.klamav/activity.db").local8Bit();


    // Open database file and check for correctness
    m_initialized = false;
    QFile file( path );
    if ( file.open( IO_ReadOnly ) )
    {
        QString format;
        file.readLine( format, 50 );
        if ( !format.startsWith( "SQLite format 3" ) )
        {
            kdDebug() << "Database versions incompatible. Removing and rebuilding database.\n";
        }
        else if ( sqlite3_open( path, &m_db ) != SQLITE_OK )
        {
            kdDebug() << "Database file corrupt. Removing and rebuilding database.\n";
            sqlite3_close( m_db );
        }
        else
            m_initialized = true;
    }

    if ( !m_initialized )
    {
        // Remove old db file; create new
        QFile::remove( path );
        if ( sqlite3_open( path, &m_db ) == SQLITE_OK )
        {
            m_initialized = true;
        }
    }
    if ( m_initialized )
    {
        if( sqlite3_create_function(m_db, "rand", 0, SQLITE_UTF8, NULL, sqlite_rand, NULL, NULL) != SQLITE_OK )
            m_initialized = false;
        if( sqlite3_create_function(m_db, "power", 2, SQLITE_UTF8, NULL, sqlite_power, NULL, NULL) != SQLITE_OK )
            m_initialized = false;
    }

    //optimization for speeding up SQLite
    query( "PRAGMA default_synchronous = OFF;" );
}


SqliteConnection::~SqliteConnection()
{
    if ( m_db ) sqlite3_close( m_db );
}


QStringList SqliteConnection::query( const QString& statement )
{
    QStringList values;
    int error;
    const char* tail;
    sqlite3_stmt* stmt;

    //compile SQL program to virtual machine
    error = sqlite3_prepare( m_db, statement.utf8(), statement.length(), &stmt, &tail );

    if ( error != SQLITE_OK )
    {
        kdDebug() << k_funcinfo << " sqlite3_compile error:" << endl;
        kdDebug() << sqlite3_errmsg( m_db ) << endl;
        kdDebug() << "on query: " << statement << endl;
        values = QStringList();
    }
    else
    {
        int busyCnt = 0;
        int number = sqlite3_column_count( stmt );
        //execute virtual machine by iterating over rows
        while ( true )
        {
            error = sqlite3_step( stmt );

            if ( error == SQLITE_BUSY )
            {
                if ( busyCnt++ > 20 ) {
                    kdDebug() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
                    break;
                }
                ::usleep( 100000 ); // Sleep 100 msec
                kdDebug() << "sqlite3_step: BUSY counter: " << busyCnt << endl;
            }
            if ( error == SQLITE_MISUSE )
                kdDebug() << "sqlite3_step: MISUSE" << endl;
            if ( error == SQLITE_DONE || error == SQLITE_ERROR )
                break;

            //iterate over columns
            for ( int i = 0; i < number; i++ )
            {
                values << QString::fromUtf8( (const char*) sqlite3_column_text( stmt, i ) );
            }
        }
        //deallocate vm ressources
        sqlite3_finalize( stmt );

        if ( error != SQLITE_DONE )
        {
            kdDebug() << k_funcinfo << "sqlite_step error.\n";
            kdDebug() << sqlite3_errmsg( m_db ) << endl;
            kdDebug() << "on query: " << statement << endl;
            values = QStringList();
        }
    }

    return values;
}


int SqliteConnection::insert( const QString& statement, const QString& /* table */ )
{
    int error;
    const char* tail;
    sqlite3_stmt* stmt;

    //compile SQL program to virtual machine
    error = sqlite3_prepare( m_db, statement.utf8(), statement.length(), &stmt, &tail );

    if ( error != SQLITE_OK )
    {
        kdDebug() << k_funcinfo << " sqlite3_compile error:" << endl;
        kdDebug() << sqlite3_errmsg( m_db ) << endl;
        kdDebug() << "on insert: " << statement << endl;
    }
    else
    {
        int busyCnt = 0;
        //execute virtual machine by iterating over rows
        while ( true )
        {
            error = sqlite3_step( stmt );

            if ( error == SQLITE_BUSY )
            {
                if ( busyCnt++ > 20 ) {
                    kdDebug() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
                    break;
                }
                ::usleep( 100000 ); // Sleep 100 msec
                kdDebug() << "sqlite3_step: BUSY counter: " << busyCnt << endl;
            }
            if ( error == SQLITE_MISUSE )
                kdDebug() << "sqlite3_step: MISUSE" << endl;
            if ( error == SQLITE_DONE || error == SQLITE_ERROR )
                break;
        }
        //deallocate vm ressources
        sqlite3_finalize( stmt );

        if ( error != SQLITE_DONE )
        {
            kdDebug() << k_funcinfo << "sqlite_step error.\n";
            kdDebug() << sqlite3_errmsg( m_db ) << endl;
            kdDebug() << "on insert: " << statement << endl;
        }
    }
    return sqlite3_last_insert_rowid( m_db );
}


// this implements a RAND() function compatible with the MySQL RAND() (0-param-form without seed)
void SqliteConnection::sqlite_rand(sqlite3_context *context, int /*argc*/, sqlite3_value ** /*argv*/)
{
    sqlite3_result_double( context, static_cast<double>(KApplication::random()) / (RAND_MAX+1.0) );
}

// this implements a POWER() function compatible with the MySQL POWER()
void SqliteConnection::sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    Q_ASSERT( argc==2 );
    if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ) {
        sqlite3_result_null(context);
        return;
    }
    double a = sqlite3_value_double(argv[0]);
    double b = sqlite3_value_double(argv[1]);
    sqlite3_result_double( context, pow(a,b) );
}




//////////////////////////////////////////////////////////////////////////////////////////
// CLASS SqliteConfig
//////////////////////////////////////////////////////////////////////////////////////////

SqliteConfig::SqliteConfig( const QString& dbfile )
    : m_dbfile( dbfile )
{
}



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

DbConnectionPool::DbConnectionPool( bool temporary )
    : m_isTemporary( temporary )
    , m_semaphore( POOL_SIZE )
{
   m_dbConnType = DbConnection::sqlite;

    m_semaphore += POOL_SIZE;
    DbConnection *dbConn;

    m_dbConfig = new SqliteConfig( "activity.db" );
    dbConn = new SqliteConnection( static_cast<SqliteConfig*> ( m_dbConfig ) );

    enqueue( dbConn );
    m_semaphore--;
    kdDebug() << "Available db connections: " << m_semaphore.available() << endl;
}


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

    while ( ( conn = dequeue() ) != 0 )
    {
        if ( m_dbConnType == DbConnection::sqlite && vacuum )
        {
            vacuum = false;
            kdDebug() << "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;

        dbConn = new SqliteConnection( static_cast<SqliteConfig*> ( m_dbConfig ) );
        enqueue( dbConn );
        m_semaphore--;
    }
    kdDebug() << "Available db connections: " << m_semaphore.available() << endl;
}


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


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


void
CollectionDB::expireActivity(const  QString &days )
{
	int intdays = days.toInt();
    if (intdays > 0)
	   intdays--;

	query( QString( "DELETE FROM klamav_activity WHERE date < datetime('now','localtime','-%2 days', 'start of day');" ).arg(intdays) );
}


QStringList
CollectionDB::allActivity( )
{
	return query( QString( "SELECT * FROM klamav_activity" ) );
}

QStringList
CollectionDB::allActivityOfType(const  QString &type,const  QString &days )
{
	int intdays = days.toInt();

	intdays--;

	if (type == "All Types")
		return query( QString( "SELECT * FROM klamav_activity WHERE date > datetime('now','localtime','-%2 days', 'start of day');" ).arg(intdays) );

	return query( QString( "SELECT * FROM klamav_activity where type = '%1'"
		"  and date > datetime('now','localtime','-%2 days', 'start of day');" ).arg(type).arg(intdays) );
}

void CollectionDB::insertEvent(const  QString &type, const QString &event, const QString &file, DbConnection *conn)
{

	if (((!(KlamavConfig::launchShutdown())) && (type == "Launch")) ||
	   ((!(KlamavConfig::softwareUpdates())) && (type == "Updates")) ||
	   ((!(KlamavConfig::dBUpdates())) && (type == "Updates")) ||
	   ((!(KlamavConfig::quarantined())) && (type == "Quarantine")) ||
	   ((!(KlamavConfig::virusFound())) && (type == "Virus Found")) ||
	   ((!(KlamavConfig::error())) && (type == "Error Found")) ||
       ((!(KlamavConfig::startedStoppedCancelled())) && (type == "Manual Scan")) ||
       ((!(KlamavConfig::startedStoppedCancelled())) && (type == "Auto-Scan")))
		return;

	QString date = query( QString( "select datetime('now','localtime')" ) ).first();
        insert( QString( "INSERT INTO klamav_activity ( type, date,  event, file )"
	" VALUES ( '%1',  '%2', '%3', '%4' );" )
         .arg( type )
         .arg( date )
         .arg( event )
	 .arg( file )
	 , "klamav_activity", conn);

        kmain->activityviewer->insertItem(date,type,event,file);
}

void CollectionDB::insertMetaDBEntry(const  QString &date, const QString &submission, const QString &creator,const QString &virus,const QString &alias, const QString &sender,DbConnection *conn)
{

    insert( QString( "INSERT INTO klamav_metadb ( id, date, submission,  creator, virus, alias, sender )"
	" VALUES ( NULL, \"%1\",  \"%2\", \"%3\", \"%4\", \"%5\", \"%6\");" )
         .arg( date )
         .arg( submission.toInt() )
         .arg( creator )
         .arg( virus )
         .arg( alias )
         .arg( sender )
	 , "klamav_metadb", conn);

}

QString
CollectionDB::latestMetaDBDate( )
{
	QStringList highest = query( QString( "SELECT MAX(date) FROM klamav_metadb;" ));

    return highest.first();
}


#include "collectiondb.moc"