platform/databases/SQLiteDatabase.hpp

// ------------------------------------------------------------------------
// Pion is a development platform for building Reactors that process Events
// ------------------------------------------------------------------------
// Copyright (C) 2007-2008 Atomic Labs, Inc.  (http://www.atomiclabs.com)
//
// Pion is free software: you can redistribute it and/or modify it under the
// terms of the GNU Affero General Public License as published by the Free
// Software Foundation, either version 3 of the License, or (at your option)
// any later version.
//
// Pion 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 Affero General Public License for
// more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with Pion.  If not, see <http://www.gnu.org/licenses/>.
//

#ifndef __PION_SQLITEDATABASE_HEADER__
#define __PION_SQLITEDATABASE_HEADER__

#include <pion/PionConfig.hpp>
#include <pion/PionException.hpp>
#include <pion/platform/Database.hpp>
#include "../../sqlite/pion-sqlite.h"

#if SQLITE_VERSION_NUMBER != 3007005
#error Incorrect version of Pion-SQLite
#endif

namespace pion {        // begin namespace pion
namespace plugins {     // begin namespace plugins


class SQLiteDatabase :
    public pion::platform::Database
{
    PionLogger                                  m_logger;

public:

    class EmptyFilenameException : public PionException {
    public:
        EmptyFilenameException(const std::string& database_id)
            : PionException("SQLiteDatabase configuration is missing a required Filename parameter: ", database_id) {}
    };

    class SQLiteAPIException : public PionException {
    public:
        SQLiteAPIException(const std::string& sqlite_error_msg)
            : PionException("SQLiteDatabase API error: ", sqlite_error_msg) {}
    };

    class DBStillOpen : public PionException {
    public:
        DBStillOpen(const std::string& database_id)
            : PionException("SQLiteDatabase DROP on table which is still open: ", database_id) {}
    };


    SQLiteDatabase(void)
        : pion::platform::Database("pion.SQLiteDatabase"), m_logger(PION_GET_LOGGER("pion.SQLiteDatabase")),
            m_sqlite_db(NULL), m_error_ptr(NULL), m_cache_size(0), m_partition(0)
    {
    }

    virtual ~SQLiteDatabase()
    {
        m_query_map.clear();
        close();
    }

    virtual pion::platform::DatabasePtr clone(void) const;

    virtual void open(unsigned partition = 0);

    virtual void close(void);

    std::string dbPartition(std::string name, unsigned partition)
    {
        if (partition) {
            char buff[10];
            sprintf(buff, "_%03u.db", partition);
            std::string::size_type i = 0;
            if ((i = name.find(".db", i)) != std::string::npos)
                name.replace(i, strlen(".db"), buff);
            else
                name += buff;
        }
        return name;
    }

    virtual boost::uint64_t getCache(CACHEPARAM what)
    {
        switch (what) {
            case CACHE_INDEX_ROW_OVERHEAD:
                return 9;   // SQLite has a 9 byte rowid
            case CACHE_PAGE_CACHE_SIZE:
                return m_cache_size;
            case CACHE_PAGE_UTILIZATION:
                return 85;  // average efficiency for index
            case DB_FILE_SIZE:
                {
                    boost::filesystem::path f(dbPartition(m_database_name, m_partition).c_str(), boost::filesystem::native );
                    if (!boost::filesystem::exists(f) || !boost::filesystem::is_regular(f))
                        return 0;
                    return boost::filesystem::file_size(f);
                }
        }
        return 0;   // Just to make compiler happy...
    }

    virtual bool is_open(void) const { return m_sqlite_db != NULL; }

    virtual void runQuery(const std::string& sql_query, const boost::regex& suppress);

    virtual pion::platform::QueryPtr addQuery(pion::platform::QueryID query_id,
                                              const std::string& sql_query);

    virtual void createTable(const pion::platform::Query::FieldMap& field_map,
                            std::string& table_name,
                            const pion::platform::Query::IndexMap& index_map,
                            unsigned partition);

    virtual void dropTable(std::string& table_name, unsigned partition = 0);

    virtual bool tableExists(std::string& table_name, unsigned partition = 0);

    virtual pion::platform::QueryPtr prepareInsertQuery(const pion::platform::Query::FieldMap& field_map,
                                                        const std::string& table_name);

    virtual pion::platform::QueryPtr prepareInsertIgnoreQuery(const pion::platform::Query::FieldMap& field_map,
                                                        const std::string& table_name);

    virtual pion::platform::QueryPtr prepareFullQuery(const std::string& query, const boost::regex& suppress);

    virtual pion::platform::QueryPtr getBeginTransactionQuery(void);

    virtual pion::platform::QueryPtr getCommitTransactionQuery(void);

    virtual void setConfig(const pion::platform::Vocabulary& v, const xmlNodePtr config_ptr);

    static inline void throwAPIException(sqlite3 *db_ptr) {
        PION_ASSERT(db_ptr != NULL);
        std::string error_msg(sqlite3_errmsg(db_ptr));
        throw SQLiteAPIException(error_msg);
    }


protected:

    inline std::string getSQLiteError(void);


    class SQLiteQuery
        : public pion::platform::Query
    {
    public:

        virtual ~SQLiteQuery() { sqlite3_finalize(m_sqlite_stmt); }

        SQLiteQuery(const std::string& sql_query, sqlite3 *db_ptr);

        virtual void bindNull(unsigned int param) {
            if (sqlite3_bind_null(m_sqlite_stmt, param+1) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindString(unsigned int param, const std::string& value, bool copy_value = true) {
            if (sqlite3_bind_text(m_sqlite_stmt, param+1, value.c_str(),
                                  value.size(), (copy_value ? SQLITE_TRANSIENT : SQLITE_STATIC)) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindBlob(unsigned int param, const char *value, size_t size, bool copy_value = true) {
            if (sqlite3_bind_blob(m_sqlite_stmt, param+1, value,
                                  size, (copy_value ? SQLITE_TRANSIENT : SQLITE_STATIC)) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindString(unsigned int param, const char *value, bool copy_value = true) {
            if (sqlite3_bind_text(m_sqlite_stmt, param+1, value,
                                  -1, (copy_value ? SQLITE_TRANSIENT : SQLITE_STATIC)) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindInt(unsigned int param, const boost::int32_t value) {
            if (sqlite3_bind_int(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindUInt(unsigned int param, const boost::uint32_t value) {
            if (sqlite3_bind_int(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindBigInt(unsigned int param, const boost::int64_t value) {
            if (sqlite3_bind_int64(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindUBigInt(unsigned int param, const boost::uint64_t value) {
            if (sqlite3_bind_int64(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindFloat(unsigned int param, const float value) {
            if (sqlite3_bind_double(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindDouble(unsigned int param, const double value) {
            if (sqlite3_bind_double(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindLongDouble(unsigned int param, const long double value) {
            if (sqlite3_bind_double(m_sqlite_stmt, param+1, value) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindDateTime(unsigned int param, const PionDateTime& value) {
            // store it as an iso extended string
            char *as_string = getDateTimeString(boost::get<const PionDateTime&>(value));
            if (sqlite3_bind_text(m_sqlite_stmt, param+1, as_string,
                                  strlen(as_string), SQLITE_TRANSIENT) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindDate(unsigned int param, const PionDateTime& value) {
            // store it as an iso extended string
            char *as_string = getDateString(boost::get<const PionDateTime&>(value));
            if (sqlite3_bind_text(m_sqlite_stmt, param+1, as_string,
                                  strlen(as_string), SQLITE_TRANSIENT) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void bindTime(unsigned int param, const PionDateTime& value) {
            // store it as an iso extended string
            char *as_string = getTimeString(boost::get<const PionDateTime&>(value));
            if (sqlite3_bind_text(m_sqlite_stmt, param+1, as_string,
                                  strlen(as_string), SQLITE_TRANSIENT) != SQLITE_OK)
                SQLiteDatabase::throwAPIException(m_sqlite_db);
        }

        virtual void fetchString(unsigned int param, std::string& value) {
            const char *ptr = (const char *)sqlite3_column_text(m_sqlite_stmt, param);
            if (ptr)
                value = ptr;
            else
                value = "";
        }

        virtual void fetchBlob(unsigned int param, std::string& value) {
            const char *ptr = (const char *)sqlite3_column_blob(m_sqlite_stmt, param);
            int size = sqlite3_column_bytes(m_sqlite_stmt, param);
            if (ptr && size > 0)
                value = std::string(ptr, size);
            else
                value = "";
        }

        virtual boost::int32_t fetchInt(unsigned int param) {
            return sqlite3_column_int(m_sqlite_stmt, param);
        }

        virtual boost::uint32_t fetchUInt(unsigned int param) {
            return sqlite3_column_int(m_sqlite_stmt, param);
        }

        virtual boost::int64_t fetchBigInt(unsigned int param) {
            return sqlite3_column_int64(m_sqlite_stmt, param);
        }

        virtual boost::uint64_t fetchUBigInt(unsigned int param) {
            return sqlite3_column_int64(m_sqlite_stmt, param);
        }

        virtual float fetchFloat(unsigned int param) {
            return sqlite3_column_double(m_sqlite_stmt, param);
        }

        virtual double fetchDouble(unsigned int param) {
            return sqlite3_column_double(m_sqlite_stmt, param);
        }

        virtual long double fetchLongDouble(unsigned int param) {
            return sqlite3_column_double(m_sqlite_stmt, param);
        }

        virtual void fetchDateTime(unsigned int param, PionDateTime& val) {
            // %Y-%m-%dT%H:%M:%S%F
            const char *ptr = (const char *)sqlite3_column_text(m_sqlite_stmt, param);
            if (ptr) {
//              PionTimeFacet time_facet("%Y-%m-%dT%H:%M:%S%F");
                PionTimeFacet time_facet("%Y-%m-%d %H:%M:%S");
                val = time_facet.fromString(ptr);
            }
//          val = boost::posix_time::time_from_string(reinterpret_cast<const char *>(sqlite3_column_text(m_sqlite_stmt, param)));
//          val = f.fromString(reinterpret_cast<const char *>(sqlite3_column_text(m_sqlite_stmt, param)));
        }

        virtual void fetchDate(unsigned int param, PionDateTime& val) {
            // %Y-%m-%dT%H:%M:%S%F
            const char *ptr = (const char *)sqlite3_column_text(m_sqlite_stmt, param);
            if (ptr) {
                PionTimeFacet time_facet("%Y-%m-%d");
                val = time_facet.fromString(ptr);
            }
        }

        virtual void fetchTime(unsigned int param, PionDateTime& val) {
            // %Y-%m-%dT%H:%M:%S%F
            const char *ptr = (const char *)sqlite3_column_text(m_sqlite_stmt, param);
            if (ptr) {
                PionTimeFacet time_facet("%H:%M:%S");
                val = time_facet.fromString(ptr);
            }
        }

        virtual bool run(void);

        virtual bool runFullQuery(const pion::platform::Query::FieldMap& ins, const pion::platform::EventPtr& src,
                                    const pion::platform::Query::FieldMap& outs, pion::platform::EventPtr& dest,
                                    unsigned int limit, const boost::regex& suppress);

        virtual bool runFullGetMore(const pion::platform::Query::FieldMap& outs, pion::platform::EventPtr& dest,
                                    unsigned int limit);

        virtual void reset(void) { sqlite3_reset(m_sqlite_stmt); }

        bool fetchRow(const FieldMap& field_map, pion::platform::EventPtr e);


    private:

        sqlite3 *           m_sqlite_db;

        sqlite3_stmt *      m_sqlite_stmt;
    };


private:

    static const std::string        BACKUP_FILE_EXTENSION;

    static const std::string        FILENAME_ELEMENT_NAME;


    std::string                     m_database_name;

    sqlite3 *                       m_sqlite_db;

    char *                          m_error_ptr;

    boost::uint64_t                 m_cache_size;

    unsigned                        m_partition;
};


// inline member functions for SQLiteDatabase

inline std::string SQLiteDatabase::getSQLiteError(void)
{
    std::string error_str;
    if (m_error_ptr != NULL) {
        error_str = m_error_ptr;
        sqlite3_free(m_error_ptr);
        m_error_ptr = NULL;
    }
    return error_str;
}


}   // end namespace plugins
}   // end namespace pion

#endif

---