|| || || || || || || ||

---

TableDataSourceSqliteDb.h

Go to the documentation of this file.

#ifndef VideoExcel_TableDataSourceSqliteDb_h
#define VideoExcel_TableDataSourceSqliteDb_h

#include "TableDataSource.h"

//#include "OglGui/DocDimensions.h"
//#include "OglGui/DocFlowDrag.h"
//#include "Util/DirImageFileNames.h"
//#include "Core/Array/ReadFile.h"
//#include "Visualization/RgbOglImage.h"


//#include <iostream>
//#include "Core/Array/Arrays.h"
//#include "Core/Array/ReadFile.h"
//#include "Util/Database.h"

#include "CppSQLite3.h"

//using namespace Impala::Core::Stream;
//using namespace Impala::Util;

namespace Impala {
namespace Application {
namespace VideoExcel {

/* This file allows reading of Khronos databases in a limited fashion only */

class TableDataSourceSqliteDb : public TableDataSource
{
public:

    TableDataSourceSqliteDb(std::string database, bool readonly=true):
        TableDataSource()
    {
        Init(database, readonly);
    }

    ~TableDataSourceSqliteDb()
    {
        ILOG_DEBUG("destructing DB");
        if (mSqlDb) {
            sqlite3_enable_shared_cache(false);
            delete mSqlDb;
        }
        if (mQueryBuffer)
            delete mQueryBuffer;
        if (mBuffer)
            delete mBuffer;
    }


    /* get individual row characteristics ************************/
    virtual std::string
    GetTextDataByID(std::string column, int row)
    {
        if (OutOfBounds(row))
            return "";

        if (column == "filename")
            return RetrieveTextFromDb("origfilename", row);

        if (column == "timestamp" || column == "width" || column == "height")
            return RetrieveTextFromDb(column, row);

        return "N/A";
    }

    virtual double
    GetNormalizedDataByID(std::string column, int row)
    {
        return 0;
    }

    virtual Array2dVec3UInt8*
    GetImageDataByID(std::string column, int row)
    {
        if (OutOfBounds(row))
            return 0;
        Array2dVec3UInt8* ar = 0;
        ar = RetrieveImageFromDb(row);
        return ar;
    }

    /* Khronos database functions *********************************/

    std::string
    RetrieveTextFromDb(std::string field, int index)
    {

        std::ostringstream os;
        os << "select " << field << " from cache where idx=(" << index << ");";
        try
        {
            CppSQLite3Query q = mSqlDb->execQuery(os.str().c_str());
            std::string val = q.getStringField(field.c_str());
            return val;
        } catch (CppSQLite3Exception &e)
        {
            return "";
        }
    }

    Array2dVec3UInt8*
    RetrieveImageFromDb(int index)
    {
        CppSQLite3Buffer b;
        b.format("select data from cache where idx = '%d';", index);
        CppSQLite3Query q = mSqlDb->execQuery(b);

        //ILOG_DEBUG("ASPECT = " << mAspectRatio << " --> w=" << mNormW << " h=" << mNormH);
        if (q.fieldIsNull("data"))
        {
            ILOG_WARN("no thumbnail found for " << index);
            return 0;
        }

        int length;
        const unsigned char* image= q.getBlobField("data", length);

        Core::Array::Array2dVec3UInt8* datastream = 0;
        ReadJpgFromMemory(datastream, (char*)image, length);
        return datastream;
    }


    int
    GetNrOfImages()
    {
        return mSqlDb->execScalar("select count(*) from cache;");
    }


    bool
    DbReady()
    {
        return mDbOpened;
    }

    std::string
    DbGetStringProperty(std::string key)
    {
        mQueryBuffer->format("select value from properties where key=(%Q);", key.c_str());
        try
        {
            CppSQLite3Query q = mSqlDb->execQuery(mQueryBuffer->buffer());
            std::string val = q.getStringField("value");
            return val;
        } catch (CppSQLite3Exception &e)
        {
            return "";
        }
    }

    int
    DbGetIntProperty(std::string key)
    {
        mQueryBuffer->format("select value from properties where key=(%Q);", key.c_str());
        //try
       // {
            CppSQLite3Query q = mSqlDb->execQuery(mQueryBuffer->buffer());
            int val = q.getIntField("value");
            return val;
       /* } catch (CppSQLite3Exception &e)
        {
            return -1;
        }*/
    }


    CppSQLite3DB*
    GetDBHandle()
    {
        return mSqlDb;
    }

    int
    GetThumbWidth()
    {
        return mTWidth;
    }

    int
    GetThumbHeight()
    {
        return mTHeight;
    }

private:

    bool
    OpenDb(std::string database, bool readonly=true)
    {
        mSqlDb->open(database.c_str());
        mReadonly = readonly;
        ILOG_INFO("Opening database " << database << "...");
        int version = DbGetIntProperty("dbversion");
        ILOG_DEBUG("Database version: " << version);
        if (version == -1)
        {
            ILOG_ERROR("Cannot open non-existing database.");
            return false;
        }

        sqlite3_enable_shared_cache(true);

        if (version != 5)
        {
            ILOG_WARN("trying to open an incompatible database version.");
            return false;
        }

        try
        {
            int rows = mSqlDb->execScalar("select count(*) from cache;");
            ILOG_INFO("Database opened, " << rows << " images found.");
        }
        catch (CppSQLite3Exception& e)
        {
            ILOG_ERROR("Could not read from cache table.");
            return false;
        }
        return true;
    }


    bool
    DbHasFile(std::string file)
    {
        mQueryBuffer->format("select count(*) from cache where origfilename=%Q;", file.c_str());
        int rows = mSqlDb->execScalar(mQueryBuffer->buffer());
        if (rows == 1)
            return true;
        return false;
    }

    void
    Init(std::string database, bool readonly)
    {
        ILOG_DEBUG("initializing TableDataSourceSqliteDb...");

        mSqlDb = new CppSQLite3DB();
        mMaxBufferSize = 256000;
        mBuffer = new char[mMaxBufferSize];
        mQueryBuffer = new CppSQLite3Buffer();
        mDatabaseFile = database;
        mDbOpened = OpenDb(database, readonly);
        
        if (!mDbOpened)
        {
            return;
        }

        mTotalRows = GetNrOfImages();
        mFilteredRows = mTotalRows;

        mTWidth = 512;
        mTHeight = 512;

        AddColumn("id", TYPE_TEXT, 50);
        AddColumn("image", TYPE_IMAGE, 120);
        AddColumn("filename", TYPE_TEXT, 300);
        AddColumn("timestamp", TYPE_TEXT, 120);
        AddColumn("width", TYPE_TEXT, 120);
        AddColumn("height", TYPE_TEXT, 120);

    }

    std::string mDatabaseFile;

    CppSQLite3DB *mSqlDb;
    CppSQLite3Buffer *mQueryBuffer;

    int mTWidth, mTHeight;

    int mMaxBufferSize;
    char *mBuffer;
    bool mUseFileTimes;
    bool mDbOpened;
    bool mReadonly;


    ILOG_VAR_DEC;

};

ILOG_VAR_INIT(TableDataSourceSqliteDb, Application.VideoExcel);


} // namespace VideoExcel
} // namespace Application
} // namespace Impala


#endif // TableDataSourceSqliteDb_h

---