DBSqLite.cpp

Go to the documentation of this file.

/*
 * DBSqLite.cpp
 *
 *  This file is part of the HausmiSEP project
 *
 *  Copyright (C) 2012, 2013 Marco Alvarado (malvcr@gmail.com)
 *
 *  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 3 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, see <http://www.gnu.org/licenses/>.
 */

#include <HSEPDataDrvSqLite/DBSqLite.h>
#include <HSEPData/DBBase.h>
#include <HSEP/Array.h>
#include <HSEP/Dictionary.h>
#include <iostream>
#include <sqlite3.h>
#include <stdio.h>

using namespace HSEPData;
using namespace HSEP;
using namespace std;

namespace HSEPDataSqLite {

// ----------------------------------------------------------------------------------------

    string SqLiteWhatMethod(string& pMessageRef, exception* pExceptionPtr) {

        char vTemp[10240];

        //sql::SQLException& vException = (sql::SQLException&)*pException;

        sprintf(vTemp,
                "# ERR: SQLException in %s (%s) on line %d\n# ERR: %s (SqLite error code: #d, SQLState: #s)\n",
                __FILE__,
                __FUNCTION__,
                __LINE__,
                pExceptionPtr->what()
                //pException->getErrorCode(),
                //vException.getSQLState().c_str()
                );

        pMessageRef = string (vTemp);

        return pMessageRef;

    } // SqLiteWhatMethod

    class RAII_mutex {
        sqlite3* aConnectionPtr;
    public:
        RAII_mutex(sqlite3* pConnectionPtr) : aConnectionPtr(pConnectionPtr) {
            sqlite3_mutex_enter(sqlite3_db_mutex(aConnectionPtr));
        }
        ~RAII_mutex() {
            sqlite3_mutex_leave(sqlite3_db_mutex(aConnectionPtr));
        }
    };

    // ----------------------------------------------------------------------------------------

    struct SQLResultValue {
        virtual string toString() = 0;
        virtual ~SQLResultValue() {}
    };

    struct SQLResultValue_Int : public SQLResultValue {
        int aValue;

        string toString() {
            return to_string(aValue);
        }
    };

    struct SQLResultValue_String : public SQLResultValue {
        string aValue;

        string toString() {
            return aValue;
        }
    };

    typedef Array<SQLResultValue> SQLResultRow;
    typedef Array<SQLResultRow>   SQLResultTable;

    struct SQLResultData {

        DBMetadata          aMetadata;
        IntDictionary       aPositions;
        SQLResultTable      aData;
        size_t              aCurrent = 0;

        bool isFirst() {
            return (aCurrent == 0);
        }

        bool isLast() {
            return (aCurrent == (count()-1));
        }

        size_t count() {
            return (aData.size());
        }

        void next() {
            aCurrent++;
        }

        SQLResultRow* current() {
            return aData.ptr(aCurrent);
        }

        void getMetadata(DBMetadata& pMetadata) {
            pMetadata(aMetadata);
        }

        SQLResultTable& table() {
            return aData;
        }

        void getString(string& pNameRef,string& pResult) {

            if (aPositions.has(pNameRef)) {
                int vPosition = aPositions.ref(pNameRef);
                SQLResultRow* vRow = aData.ptr(aCurrent);
                pResult = vRow->ptr(vPosition)->toString();
            }

        } // getString

        void getString(size_t pPosition,string& pResult) {

            if (aPositions.size() > pPosition) {
                SQLResultRow*   vRow = aData.ptr(aCurrent);
                pResult = vRow->ptr(pPosition)->toString();
            }

        } // getString


    }; // SQLResultData


    SqLiteResultSet::SqLiteResultSet() : DBResultSet() {
        aResultSet = (void*)new SQLResultData();
    } // SqLiteResultSet constructor

    SqLiteResultSet::~SqLiteResultSet() {
        delete (SQLResultData*)aResultSet;
    } // SqLiteResultSet destructor


    bool SqLiteResultSet::isLast() {

        bool vResult = false;
           vResult = ((SQLResultData*)aResultSet)->isLast();
        return vResult;
    } // SqLiteResultSet::isLast

    size_t SqLiteResultSet::size() {
        bool vResult = -1;
           vResult =((SQLResultData*)aResultSet)->count();
        return vResult;
    } // SqLiteResultSet::size

    void SqLiteResultSet::getMetadata(DBMetadata& pMetadataRef) {

        SQLResultData* vResultData = (SQLResultData*)aResultSet;
        vResultData->getMetadata(pMetadataRef);

    } // SqLiteResultSet::getMetadata


    int SqLiteResultSet::forEach(CycleBase& pCycleRef) {

        SQLResultData* vResultData = ((SQLResultData*)aResultSet);

        int vWorked  = 0;

        vResultData->table().forEach([&](SQLResultRow* vRowPtr){
            if (pCycleRef.execute(this,vResultData->isFirst(),vResultData->isLast())) {
                vWorked++;
            }
            vResultData->next();
        });

        return vWorked;

    } // SqLiteResultSet::forEach

    int SqLiteResultSet::forEach(ExecuteFunctionType pFunction) {

        SQLResultData* vResultData = ((SQLResultData*)aResultSet);

        int vWorked  = 0;

        vResultData->table().forEach([&](SQLResultRow* vRowPtr) {
            if (pFunction(this,vResultData->isFirst(),vResultData->isLast())) {
                vWorked++;
            }
            vResultData->next();
        });

        return vWorked;

    } // SqLiteResultSet::forEach

    string SqLiteResultSet::getString(string& pNameRef) {

        SQLResultData* vResultData = ((SQLResultData*)aResultSet);
        string vResult;
        vResultData->getString(pNameRef,vResult);

        return vResult;

    } // SqLiteResultSet::getString

    string SqLiteResultSet::getString(int pPosition) {


        SQLResultData* vResultData = ((SQLResultData*)aResultSet);
        string vResult;
        vResultData->getString(pPosition,vResult);

        return vResult;

    } // SqLiteResultSet::getString

    // ----------------------------------------------------------------------------------------

    SqLiteStatement::SqLiteStatement() : DBStatement() {
        aStatement = nullptr;
    } // SqLiteStatement constructor

    SqLiteStatement::~SqLiteStatement() {
        if (aStatement) {
            sqlite3_finalize((sqlite3_stmt*)aStatement);
        }
    } // SqLiteStatement destructor

    bool SqLiteStatement::createIt(void* pConnectionPtr,string& pQueryRef) {

        bool vResult = false;
        int  vSize   = (int)pQueryRef.size()+1;

        try {
            RAII_mutex((sqlite3*)pConnectionPtr);
            int vResCode = sqlite3_prepare_v2(
                (sqlite3*)pConnectionPtr,
                pQueryRef.c_str(),
                vSize,
                (sqlite3_stmt**)&aStatement,
                nullptr
            );
            if (SQLITE_OK != vResCode) {
                setLastError(sqlite3_errmsg((sqlite3*)pConnectionPtr));
                aStatement = nullptr;
            }
            else {
                vResult = true;
            }
            // implicit RAII_mutex release (going out of scope)
        }
        catch (exception &vExc) {
            string vMessage;
            setLastError(SqLiteWhatMethod(vMessage,&vExc));
            aStatement = nullptr;
        }
        return vResult;

    } // SqLiteStatement::createIt

    // The index 0 is not used ... we put the first DBT_Null to fill the gap ...
    //
    const DBType DBT_SQL2BW[] = {DBT_Null,DBT_Number,DBT_Float,DBT_String,DBT_Binary,DBT_Null};


    void feedMetadata(sqlite3_stmt* pStatement, SQLResultData* pResultData) {

        int vColumns = sqlite3_column_count(pStatement);

        for (int vIdx=0;vIdx<vColumns;vIdx++) {

            DBMetadataField* vMetadataField = new DBMetadataField();
            vMetadataField->aColumnName = string(sqlite3_column_name(pStatement,vIdx));
            int vSqliteType = sqlite3_column_type(pStatement,vIdx);
            vMetadataField->aColumnType = DBT_SQL2BW[vSqliteType];

            pResultData->aMetadata.push(vMetadataField);
            pResultData->aPositions.put(vMetadataField->aColumnName,vIdx);
        }

    } // feedMetadata

    void feedData(sqlite3_stmt* pStatement, SQLResultData* pResultData) {

        SQLResultRow* vRow = new SQLResultRow();

        int vIdx = 0;

        pResultData->aMetadata.forEach([&](DBMetadataField* vFieldPtr){

            SQLResultValue* vValue;

            switch (vFieldPtr->aColumnType) {
                case DBT_String:
                    const unsigned char* vValueStr;
                    vValue = new SQLResultValue_String();
                    vValueStr = sqlite3_column_text(pStatement,vIdx);
                    ((SQLResultValue_String*)vValue)->aValue = string((char*)vValueStr);
                    break;
                case DBT_Number:
                    vValue = new SQLResultValue_Int();
                    ((SQLResultValue_Int*)vValue)->aValue = sqlite3_column_int(pStatement,vIdx);
                    break;
            }

            vRow->push(vValue);

            vIdx++;

        });

        pResultData->aData.push(vRow);

    } // feedData


    DBResultSet* SqLiteStatement::executeQueryPtr() {

        SqLiteResultSet* vResultSet = new SqLiteResultSet();
        SQLResultData*   vResultData = ((SQLResultData*)vResultSet->aResultSet);

        sqlite3_stmt*    vStatement = (sqlite3_stmt*)aStatement;
        bool vFirstLine = true;

        while (sqlite3_step(vStatement) == SQLITE_ROW) {

            if (vFirstLine) {
                vFirstLine = false;
                feedMetadata(vStatement,vResultData);
            }
            feedData(vStatement,vResultData);
        }

        return vResultSet;

    } // SqLiteStatement::executeQuery

    void feedParameters(sqlite3_stmt* pStatement, DBParameters& pParametersRef) {

        int vIndex = 1;

        for (DBParameter vParameter : pParametersRef) {

            switch (vParameter.first) {
                case DBT_String:
                    sqlite3_bind_text(pStatement,vIndex,vParameter.second.c_str(),(int)vParameter.second.size(),0);
                    break;
                case DBT_Number:
                    int vValue;
                    vValue = stoi(vParameter.second);
                    sqlite3_bind_int(pStatement,vIndex,vValue);
                    break;
            }
            vIndex++;
        }

    } // feedParameters

    DBResultSet* SqLiteStatement::executeQueryPtr(DBParameters& pParametersRef) {

        SqLiteResultSet* vResultSet = new SqLiteResultSet();
        SQLResultData*   vResultData = ((SQLResultData*)vResultSet->aResultSet);

        sqlite3_stmt*    vStatement = (sqlite3_stmt*)aStatement;
        bool vFirstLine = true;

        feedParameters(vStatement,pParametersRef);

        while (sqlite3_step(vStatement) == SQLITE_ROW) {
            if (vFirstLine) {
                vFirstLine = false;
                feedMetadata(vStatement,vResultData);
            }
            feedData(vStatement,vResultData);
        }
        sqlite3_reset(vStatement);

        return vResultSet;

    } // SqLiteStatement::executeQueryPtr

    // ----------------------------------------------------------------------------------------

    SqLiteEnvironment::SqLiteEnvironment() : DBEnvironment() {
        aOpened     = false;
        aConnection = nullptr;
    } // SqLiteEnvironment constructor

    SqLiteEnvironment::~SqLiteEnvironment() {

        try {
            sqlite3_close((sqlite3*)aConnection);
        } catch (exception &vExc) {
            string vMessage;
            setLastError(SqLiteWhatMethod(vMessage,&vExc));
        }

    } // SqLiteEnvironment destructor

    ErrorCode SqLiteEnvironment::connect(string& pConnectionStringRef,string& pUserRef,string& pPasswordRef) {

        ErrorCode vResult = EC::NotCatalogedError;

        int vSQLResult = -1;
        int vFlags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX; // serialized "full threading" mode

        try {
            if (aOpened) {
                sqlite3_close((sqlite3*)aConnection);
                aOpened = false;
            }
            vSQLResult = sqlite3_open_v2(pConnectionStringRef.c_str(), (sqlite3**)&aConnection,vFlags,nullptr);
            RAII_mutex((sqlite3*)aConnection);
            if (SQLITE_OK != vSQLResult) {
                setLastError(sqlite3_errmsg((sqlite3*)aConnection));
            }
            else {
                aOpened = true;
                vResult = EC::OK;
            }
            // RAII mutex going out of context and releasing internal mutex
        } catch (exception& vExc) {
            string vMessage;
            setLastError(SqLiteWhatMethod(vMessage,&vExc));
        }

        return vResult;

    } // SqLiteEnvironment::connect

    ErrorCode SqLiteEnvironment::setSchema(string& pNewSchemaRef) {
        return EC::OK;
    } // SqLiteEnvironment::setSchema

    DBStatement* SqLiteEnvironment::createStatementPtr(string& pQueryRef) {

        SqLiteStatement* vResult = nullptr;

        try {
            vResult = new SqLiteStatement();

            if (!vResult->createIt(aConnection,pQueryRef)) {
                setLastError(vResult->lastErrorRef());
                delete vResult;
                vResult = nullptr;
            }

        } catch (exception &vExc) {
            string vMessage;
            setLastError(SqLiteWhatMethod(vMessage,&vExc));
        }

        return vResult;

    } // SqLiteEnvironment::getStatement

    // ----------------------------------------------------------------------------------------

#define SQLITE_HEAP_SIZE 102400

    class SQLiteHeap {
        void* aHeap;
    public:

        static SQLiteHeap aSQLHeap;

        SQLiteHeap() {
            aHeap = malloc(SQLITE_HEAP_SIZE);

            // No guarantee that the heap es 100% clean.  No surprises.
            //
            memset(aHeap,1,SQLITE_HEAP_SIZE);
            // 8 byte - 64 bit architecture
            //
            sqlite3_config(SQLITE_CONFIG_HEAP, aHeap, SQLITE_HEAP_SIZE, 8);
        }
        ~SQLiteHeap() {
            // As this is "security oriented", it is important to clear the
            // used heap before returning it to the Operating System.
            //
            memset(aHeap,1,SQLITE_HEAP_SIZE);
            free(aHeap);
        }
    };

    SQLiteHeap SQLiteHeap::aSQLHeap; // can't be a pointer -- to be managed by the destructor accordingly

} // HSEPDataSqLite namespace

extern "C" HSEPData::DBEnvironment* createEnvironment() {
    return new HSEPDataSqLite::SqLiteEnvironment();
} // createEnvironment

extern "C" void driverInfo(string& pInfo) {
    pInfo = string("SqLite BW driver 1.0");
} // driverInfo