Logo Search packages:      
Sourcecode: mailody version File versions  Download package

db.cpp

/* This file is part of the KDE project

   Copyright (C) 2006-2007 KovoKs <info@kovoks.nl>

   This file is based on digikams albumdb which is
   Copyright 2004 by Renchi Raju <no_working@address.known>

   This program is free software; you can redistribute it and/or
   modify it under the terms of the GNU General Public
   License as published by the Free Software Foundation; either
   version 2 of the License, or (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/

// C Ansi includes.

extern "C"
{
#include <sys/time.h>
#include <time.h>
}

// C++ includes.

#include <cstdio>
#include <cstdlib>

// Qt includes.

#include <qdir.h>

// KDE includes.

#include <kdebug.h>
#include <klocale.h>
#include <kstandarddirs.h>

#include <qmap.h>

#include "db.h"

namespace Mailody {

extern "C"
{
#include <sqlite3.h>
}

DB *DB::m_dbinstance = 0;

typedef struct sqlite3_stmt sqlite3_stmt;

class DBPriv
{
    public:
        QMap<QString, QStringList>          cache;
};

00067 DB::DB()
{
    d=new DBPriv;
    // kdDebug() << "DB CONSTRUCTOR called" << endl;
    m_valid = false;
    m_db    = 0;
    setDBPath(locateLocal("appdata","")+"/mailody.db");
}

00076 DB::~DB()
{
    if (m_db)
        sqlite3_close(m_db);

    delete d;
}

DB *DB::dbinstance()
{
    if ( !m_dbinstance )
        m_dbinstance = new DB;

    return m_dbinstance;
}

00092 void DB::setDBPath(const QString& path)
{
    if (m_db)
    {
        sqlite3_close(m_db);
        m_db = 0;
    }

    m_valid = false;

    sqlite3_open(QFile::encodeName(path), &m_db);
    if (m_db == 0)
    {
        kdWarning() << "Cannot open database: "
                    << sqlite3_errmsg(m_db)
                    << endl;
    }
    else
        initDB();
}

00113 void DB::initDB()
{
    m_valid = false;

    // Check if we have the required tables

    QStringList values;

    if (!execSql( QString("SELECT name FROM sqlite_master"
                          " WHERE type='table'"
                          " ORDER BY name;"),
                  &values ))
        return;

    if (!values.contains("mailboxes"))
    {
        if (!execSql( QString("CREATE TABLE mailboxes"
                              " (id integer PRIMARY KEY AUTOINCREMENT,"
                              " name TEXT UNIQUE,"
                              " totalmessages int,"
                              " checkmail int);") ))
            return;
    }

    if (!values.contains("messages"))
    {
        if (!execSql( QString("CREATE TABLE messages"
                              " (uid integer,"
                              " mailbox TEXT, "
                              " comments TEXT, "
                              " header TEXT, "
                              " flags TEXT, "
                              " body BLOB, "
                              " UNIQUE( uid, mailbox) );") ))
            return;
    }

    if (!values.contains("recent"))
    {
        if (!execSql( QString("CREATE TABLE recent"
                              " (email TEXT, "
                              " name TEXT, "
                              " last TEXT, "
                              " amount integer, "
                              " UNIQUE( email ) );") ))
            return;
    }

    if (!values.contains("certificates"))
    {
        if (!execSql( QString("CREATE TABLE certificates"
             " (cert BLOB, error int, UNIQUE( cert, error ) );") ))
            return;
    }


    if (!values.contains("settings"))
    {
        if (!execSql( QString("CREATE TABLE settings "
                              "(keyword TEXT NOT NULL UNIQUE, "
                              " value TEXT);") ))
            return;
        else
            setSetting("DBVersion","1");
    }

    m_valid = true;
}

void DB::getResult(const QString& sql, QStringList& values)
{
    // first the cache...
    values = d->cache[sql];

    if ( values.count() > 0)
    {
        if (values[0] == "NULL")
            values.clear();
    }
    else
    {
        execSql( sql, &values );

        QStringList storeValues = values;
        if (storeValues.count() == 0)
            storeValues.append("NULL");
        d->cache[sql] = storeValues;
    }
}

00203 bool DB::execSql(const QString& sql, QStringList* const values,
                    const bool debug)
{
    if (debug)
        kdDebug() << "SQL-query: " << sql << endl;

    if ( !m_db )
    {
        kdWarning() << k_funcinfo << "SQLite pointer == NULL"
                << endl;
        return false;
    }

    const char*   tail;
    sqlite3_stmt* stmt;
    int           error;

    //compile SQL program to virtual machine
    error = sqlite3_prepare(m_db, sql.utf8(), -1, &stmt, &tail);
    if ( error != SQLITE_OK )
    {
        kdWarning() << k_funcinfo
                << "sqlite_compile error: "
                << sqlite3_errmsg(m_db)
                << " on query: "
                << sql << endl;
        return false;
    }

    int cols = sqlite3_column_count(stmt);

    while ( true )
    {
        error = sqlite3_step( stmt );

        if ( error == SQLITE_DONE || error == SQLITE_ERROR )
            break;

        //iterate over columns
        for ( int i = 0; values && i < cols; i++ )
        {
            *values <<
                QString::fromUtf8( (const char*)sqlite3_column_text( stmt, i ) );
        }
    }

    sqlite3_finalize( stmt );

    if ( error != SQLITE_DONE )
    {
        kdWarning() << "sqlite_step error: "
                << sqlite3_errmsg( m_db )
                << " on query: "
                << sql << endl;
        return false;
    }

    return true;
}

void DB::beginTransaction()
{
    execSql( "BEGIN TRANSACTION;" );
}

void DB::commitTransaction()
{
    execSql( "COMMIT TRANSACTION;" );
}

00273 QString DB::escapeString(const QString& str) const
{
    QString sting = str;
    sting.replace( "'", "''" );
    return sting;
}

00280 Q_LLONG DB::lastInsertedRow() const
{
    return sqlite3_last_insert_rowid(m_db);
}

void DB::insertMailBox(const QString& mb)
{
    execSql( QString("REPLACE INTO mailboxes (name) VALUES('%1')").
            arg(escapeString(mb)));
    d->cache.clear();
}

void DB::renameMailBox(const QString& oldBox, const QString& newBox)
{
   execSql( QString("UPDATE mailboxes set name='%1' where name='%2'").
            arg(escapeString(newBox),escapeString(oldBox) ));
    d->cache.clear();
}

void DB::setTotalMessagesMailbox(const QString& box, int amount)
{
    // don't update when there is nothing to do, remember this call is cached
    if (getTotalMessagesMailbox(box) != amount)
    {
        execSql( QString("UPDATE mailboxes set totalMessages=%1 "
                         "WHERE name='%2'")
            .arg(amount).arg(escapeString(box)));
        d->cache.clear();
    }
}

int DB::getTotalSeenMessagesMessages(const QString& box)
{
    QStringList values;
    getResult( QString("SELECT count(uid) from messages WHERE mailbox='%1' "
                      "and flags like '%%\\Seen%%'")
            .arg(escapeString(box)), values );
    return values[0].toInt();
}

int DB::getTotalMessagesMessages(const QString& box)
{
    QStringList values;
    getResult( QString("SELECT count(uid) from messages WHERE mailbox='%1' ")
            .arg(escapeString(box)), values );
    return values[0].toInt();
}

int DB::getTotalMessagesMailbox(const QString& box)
{
    QStringList values;
    getResult( QString("SELECT totalMessages from mailboxes WHERE name='%1'")
            .arg(escapeString(box)), values );
    return values[0].toInt();
}

void DB::getMinMax(const QString& box, int& min, int& max)
{
    QStringList values;
    getResult( QString("SELECT min(uid), max(uid) from messages "
                       "WHERE mailbox='%1'").arg(escapeString(box)), values );
    min = values[0].toInt();
    max = values[1].toInt();
}

QStringList DB::getMailBoxList()
{
    QStringList values;
    getResult( QString("SELECT name, totalMessages FROM mailboxes "
            "ORDER BY name;"), values );
    return values;
}

void DB::setCheckMailMailbox(const QString& box, int check)
{
    execSql( QString("UPDATE mailboxes set checkMail=%1 WHERE name='%2'")
            .arg(check).arg(escapeString(box)));
    d->cache.clear();
}

QStringList DB::getCheckMailBoxList()
{
    QStringList values;
    getResult( QString("SELECT name FROM mailboxes where checkMail=1"), values );
    return values;
}

void DB::storeHeaders(int uid, const QString& mb, const QString& header)
{
    // kdDebug() << "StoreHeaders" << uid << mb << header << endl;
    createID(uid, mb);
    execSql( QString("update messages set header='%3' where uid=%1 "
                     "and mailbox='%2' ")
                    .arg(uid).arg(escapeString(mb), escapeString(header)));
    d->cache.clear();
}

void DB::deleteMessagesAndMailBoxes()
{
    execSql( QString("delete from messages"));
    execSql( QString("delete from mailboxes"));
    d->cache.clear();
}

void DB::deleteMessagesAndMailBoxes(const QString& mb)
{
    // kdDebug() << "Del from db: " << mb << endl;
    execSql( QString("delete from messages where mailbox='%1'").arg(mb));
    execSql( QString("delete from mailboxes where name='%1'").arg(mb));
    d->cache.clear();
}

void DB::deleteMessage(int uid, const QString& mb)
{
    // kdDebug() << "Deleting " << uid << " from " << mb << endl;
    execSql( QString("delete from messages "
            "where uid = %1 and mailbox='%2'")
            .arg(uid).arg( escapeString(mb)) );
    d->cache.clear();
}

void DB::deleteMessages(const QString& mb)
{
    //kdDebug() << "Deleting messages from local cache for " << mb << endl;
    execSql( QString("delete from messages where mailbox='%2'")
            .arg( escapeString(mb)) );
    d->cache.clear();
}

void DB::expunge(const QString& mb)
{
    // kdDebug() << "Deleting " << uid << " from " << mb << endl;
    execSql( QString("delete from messages "
            "where mailbox='%2' and flags like '%\\Deleted%'")
            .arg( escapeString(mb)) );
    d->cache.clear();
}

void DB::storeBody(int uid, const QString& mb, const QString &body)
{
    // kdDebug() << "StoreBody: " << body.length()  << endl;
    execSql( QString("UPDATE messages set body='%2' "
                     "where uid = %1 and mailbox='%3'")
            .arg(uid).arg( escapeString(body), escapeString(mb)));
    d->cache.clear();
}

QString DB::getHeader( int uid, const QString& mb)
{
    QStringList values;
    getResult( QString("SELECT header FROM messages "
                       "where uid = %1 and mailbox = '%2'")
            .arg(uid).arg( escapeString(mb)), values );
    return values[0];
}

void DB::getCurrentMessages( const QString& mb, QStringList& values )
{
    if (mb == "*")
        getResult( "SELECT uid, mailbox, header, flags FROM messages", values );
    else
        getResult( QString("SELECT uid, mailbox, header, flags FROM messages "
            "where mailbox = '%2'")
            .arg( escapeString(mb)), values );

    // we now fill the cache, this will save time in the end.
    // with 8000 messages it saves around 1200 ms to determine the
    // deleted state.
    QStringList::ConstIterator it = values.begin();
    while (it != values.end())
    {
        int uid = (*it).toInt();
        ++it;

        QString mb = (*it);
        ++it;

        // QString headers = (*it);
        ++it;

        QString flags = (*it);
        ++it;

        d->cache[QString("SELECT flags FROM messages where "
                       "uid=%1 and mailbox='%2'")
                      .arg(uid).arg( escapeString(mb))] = flags;
    }

}

void DB::search( const QStringList& keywords, QStringList& values)
{
    QString query;
    QStringList::ConstIterator ita = keywords.begin();
    while (ita != keywords.end())
    {
        query += QString(" (lower(body) like '% %1 %' or "
                         "  lower(header) like '% %2 %' ) and")
                    .arg(escapeString((*ita).lower()),
                         escapeString((*ita).lower()));
        ++ita;
    }

    query = query.mid(0,query.length()-4);
    // kdDebug() << query  << endl;

    getResult( QString("SELECT uid, mailbox, header, flags FROM messages "
            "where %1").arg( query ), values);

    // we now fill the cache, this will save time in the end.
    // with 8000 messages it saves around 1200 ms to determine the
    // deleted state.
    QStringList::ConstIterator it = values.begin();
    while (it != values.end())
    {
        int uid = (*it).toInt();
        ++it;

        QString mb = (*it);
        ++it;

        // QString headers = (*it);
        ++it;

        QString flags = (*it);
        ++it;

        d->cache[QString("SELECT flags FROM messages where "
                       "uid=%1 and mailbox='%2'")
                      .arg(uid).arg( escapeString(mb))] = flags;
    }

}

void DB::getCurrentMessageIDs( const QString& mb, QStringList& values)
{
    if (mb == "*")
        getResult( "SELECT uid FROM messages ", values );
    else
        getResult( QString("SELECT uid FROM messages where mailbox = '%2'")
                .arg( escapeString(mb)), values );
}

QString DB::getBody( int uid, const QString& mb  )
{
    QStringList values;
    getResult( QString("SELECT body FROM messages "
                       "where uid = %1 and mailbox = '%2'")
            .arg(uid).arg( escapeString(mb)), values );
    return values[0];
}

bool DB::hasHeader( int uid, const QString& mb  )
{
    return !getHeader( uid, mb).stripWhiteSpace().isEmpty();
}

void DB::createID( int uid, const QString& mb  )
{
    QStringList values;
    getResult( QString("SELECT uid FROM messages "
            "where uid = %1 and mailbox = '%2'")
            .arg(uid).arg( escapeString(mb)), values );

    if (values[0].isEmpty())
    {
        execSql( QString("insert into messages (uid, mailbox) "
                         "values (%1, '%2')").arg(uid).arg( escapeString(mb)));
        d->cache.clear();
    }
}

bool DB::hasBody( int uid, const QString& mb  )
{
    return !getBody( uid, mb ).stripWhiteSpace().isEmpty();
}

/* ------------------- Operation on flags ---------------------- */

bool DB::hasFlag( int uid, const QString& mb, const QString& flag )
{
    QStringList values;
    // do a global request. The result will be cached, so if this
    // function is called 6 times, it is only executed on the database
    // once, which is the fastest solution here...
    getResult( QString("SELECT flags FROM messages where "
                       "uid=%1 and mailbox='%2'")
                      .arg(uid).arg( escapeString(mb)), values );

    // kdDebug() << uid << " Has Flag : " << flag
    //         << " | " << !(values[0].find(flag) == -1)
    //         << " (" << values[0] << ")"
    //         << endl;

    return  !(values[0].find(flag) == -1);
}

QString DB::getFlags( int uid, const QString& mb )
{
    // Keep this query the same as hasFlag(), so it can come from the cache
    QStringList values;
    getResult( QString("SELECT flags FROM messages where "
                       "uid=%1 and mailbox='%2'")
                      .arg(uid).arg( escapeString(mb)), values);
    return values[0];
}

void DB::setFlags( int uid, const QString& mb, const QString& flag )
{
    //kdDebug() << uid << " SetFlag: " << flag << endl;

    createID(uid, mb);
    execSql( QString("update messages set flags = '%1' where "
                         "uid=%2 and mailbox='%3'")
            .arg(escapeString(flag)).arg(uid).arg( escapeString(mb)));
    d->cache.clear();
}

void DB::addFlag( int uid, const QString& mb, const QString flag)
{
    // kdDebug() << uid << " AddFlag: " << flag << " "
    //      << mb << " " << flag << endl;

    // if there are no flags, concenate will fail, so catch that. *sigh*
    if (getFlags(uid, mb).isEmpty())
        setFlags(uid,mb, flag);
    else if (!hasFlag(uid, mb, flag))
    {
        //  kdDebug() << " now adding" << endl;
        // || is concanate in sqlite *sigh*
        execSql( QString("update messages set flags=flags || ' %1' where "
                         "uid=%2 and mailbox='%3'")
                .arg(escapeString(flag)).arg(uid).arg( escapeString(mb)));
    }
    d->cache.clear();
}

void DB::addFlag( const QString& mb, const QString flag)
{
    //kdDebug() << "AddFlag: " << flag << " to: "  << mb << " " << endl;

    execSql( QString("update messages set flags=flags || ' %1' where "
                " mailbox='%3'")
                .arg(escapeString(flag), escapeString(mb)));
    d->cache.clear();
}

void DB::removeFlag( int uid, const QString& mb, const QString flag)
{
    // kdDebug() << uid << " RemoveFlag: " << flag << " "
    //        << mb << " " << flag << endl;

    if (!hasFlag(uid, mb, flag))
        return;

    QString i = getFlags(uid, mb);
    QStringList j = QStringList::split(" ",i);
    QStringList::iterator t = j.find(flag);
    if (t != j.end())
    {
        j.remove(t);
        setFlags(uid,mb, j.join(" "));
    }
}

bool DB::hasCert( const QString& cert, int error)
{
    QStringList values;
    getResult( QString("SELECT cert FROM certificates where cert='%1'"
            " and error=%2")
            .arg(escapeString(cert)).arg(error), values);

    return values.count()>0;
}

void DB::addCert( const QString& cert, int error)
{
    execSql( QString("REPLACE into certificates VALUES('%1', %2)")
            .arg( escapeString(cert)).arg(error));
    d->cache.clear();
}

// -------------------- recent queries ----------------- //

void DB::getRecentList(QStringList& values)
{
    getResult( QString("SELECT * FROM recent"), values);
}

void DB::getTopTenRecentList(QStringList& values)
{
    getResult( QString("SELECT name,email FROM recent "
            "order by amount desc limit 10"), values);
}

void DB::addToRecentList(const QString& email, const QString& name)
{
    QStringList values;
    // first see if it is already in the database
    getResult( QString("SELECT last from recent where email='%1'")
            .arg( escapeString(email)), values);

    if (values.count() > 0)
    {
        // It exists so update the data
        execSql( QString("update recent set amount=amount+1, "
                         "last=strftime(\'%s\',\'now\') where "
                         "email='%1'").arg(escapeString(email)));
    }
    else
    {
        // does not exist, add it.
        execSql( QString("insert into recent values('%1', '%2', "
                          "strftime(\'%s\',\'now\'), 1)")
                         .arg(escapeString(email), escapeString(name)));
    }
    d->cache.clear();
}

void DB::deleteFromRecentList(const QString& email)
{
    execSql( QString("delete from recent where email = '%1'")
                     .arg(escapeString(email)));
    d->cache.clear();
}

// --------------------- Settings ----------------------//

void DB::setSetting(const QString& keyword, const QString& value )
{
    // kdDebug() << k_funcinfo << ": " << keyword << " - " << value << endl;
    execSql( QString("REPLACE into settings VALUES ('%1','%2');")
             .arg(escapeString(keyword),
                  escapeString(value) ));
    d->cache.clear();
}

QString DB::getSetting(const QString& keyword)
{
    QStringList values;
    getResult( QString("SELECT value FROM settings WHERE keyword='%1';")
             .arg(escapeString(keyword)), values );

    if (values.isEmpty())
        return QString::null;
    else
        return values[0];
}

}

Generated by  Doxygen 1.6.0   Back to index