Initial commit

Signed-off-by: Mavridis Philippe <mavridisf@gmail.com>

1 files changed, 791 insertions, 0 deletions

diff --git a/src/collectiondb.cpp b/src/collectiondb.cpp
new file mode 100644
index 0000000..ebb9742
--- /dev/null
+++ b/src/collectiondb.cpp
@@ -0,0 +1,791 @@
+// (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.
+
+
+#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"