Project

General

Profile

RE: FirebirdSQL backend integrated into WebToolkit C++ Li... » Firebird.C

Łukasz Matuszewski, 10/22/2011 11:12 AM

 
#include <Wt/WApplication>
#include <Wt/WLogger>
#include <Wt/Dbo/backend/Firebird>

#include "Wt/Dbo/Exception"

#include <Wt/Dbo/SqlConnection>
#include <Wt/Dbo/SqlStatement>

#include <boost/smart_ptr/scoped_array.hpp>
#include <iostream>
#include <vector>
#include <sstream>

#include <boost/date_time/posix_time/posix_time.hpp>
#include <boost/date_time/gregorian/gregorian.hpp>

#ifdef WIN32
#define snprintf _snprintf
#define strcasecmp _stricmp
#endif

//#define DEBUG(x) x
#define DEBUG(x)

#include <ibpp.h>

namespace Wt
{
namespace Dbo
{
namespace backend {
using namespace Wt::Dbo;
using namespace Wt;
using namespace boost::gregorian;
using namespace boost::posix_time;

class FirebirdException : public Wt::Dbo::Exception
{
public:
FirebirdException(const std::string& msg)
: Wt::Dbo::Exception(msg)
{ }
};

class FirebirdStatement : public SqlStatement
{
public:
FirebirdStatement(Firebird& conn, const std::string& sql)
: conn_(conn),
sql_(convertToNumberedPlaceholders(sql))
{
lastId_ = -1;
row_ = affectedRows_ = 0;

snprintf(name_, 64, "SQL%p%08X", this, rand());

DEBUG(std::cerr << this << " for: " << sql_ << std::endl);

IBPP::Transaction tr = conn_.transaction();

m_stmt = IBPP::StatementFactory(conn_.connection(), tr);

try
{
this->m_stmt->Prepare(sql_);
}
catch(IBPP::LogicException &e)
{
throw FirebirdException(e.what());
}

}

virtual ~FirebirdStatement()
{
}

virtual void reset()
{
// maybe in this place should be a reevaluation of statement
// this->m_stmt->Execute(this->sql());
}

virtual void bind(int column, const std::string& value)
{
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);

this->m_stmt->Set(column + 1, value);
}

virtual void bind(int column, short value)
{
this->m_stmt->Set(column + 1, value);
}

virtual void bind(int column, int value)
{
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);

this->m_stmt->Set(column + 1, value);
}

virtual void bind(int column, long long value)
{
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);

this->m_stmt->Set(column + 1, (int64_t) value);
}

virtual void bind(int column, float value)
{
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);

this->m_stmt->Set(column + 1, value);
}

virtual void bind(int column, double value)
{
DEBUG(std::cerr << this << " bind " << column << " " << value << std::endl);

this->m_stmt->Set(column + 1, value);
}

virtual void bind(int column, const boost::posix_time::time_duration & value)
{
DEBUG(std::cerr << this << " bind " << column << " " << boost::posix_time::to_simple_string(value) << std::endl);

IBPP::Time t(value.hours(), value.seconds(), value.minutes(), (int) (((value.fractional_seconds() * 1.0) / time_duration::ticks_per_second()) * 10000));
this->m_stmt->Set(column + 1, t);
}

virtual void bind(int column, const boost::posix_time::ptime& value,
SqlDateTimeType type)
{
DEBUG(std::cerr << this << " bind " << column << " "
<< boost::posix_time::to_simple_string(value) << std::endl);

if (type == SqlDate)
{
IBPP::Date idate(value.date().year(), value.date().month(), value.date().day());
this->m_stmt->Set(column + 1, idate);
}
else
{
IBPP::Timestamp ts(value.date().year(), value.date().month(), value.date().day()
, value.time_of_day().hours(), value.time_of_day().minutes()
, value.time_of_day().seconds(), (int) (((value.time_of_day().fractional_seconds() * 1.0) / time_duration::ticks_per_second()) * 10000));
this->m_stmt->Set(column + 1, ts);
}

}

virtual void bind(int column, const std::vector<unsigned char>& value)
{
DEBUG(std::cerr << this << " bind " << column << " (blob, size=" <<
value.size() << ")" << std::endl);

string blob;
blob.resize(value.size());
if (value.size() > 0)
{
memcpy(const_cast<char *>(blob.data()), &(*value.begin()), value.size());

IBPP::Blob b = IBPP::BlobFactory(this->conn_.connection(), this->conn_.transaction());
b->Write(blob.data(), (int) blob.size());
b->Close();
this->m_stmt->Set(column + 1, b);
}

}

virtual void bindNull(int column)
{
DEBUG(std::cerr << this << " bind " << column << " null" << std::endl);

this->m_stmt->SetNull(column + 1);
}

virtual void execute()
{
if (conn_.showQueries())
std::cerr << sql_ << std::endl;

try
{
this->m_stmt->Execute();
affectedRows_ = this->m_stmt->AffectedRows();

row_ = 0;
}
catch(IBPP::LogicException &e)
{
throw FirebirdException(e.what());
}
}

virtual long long insertedId()
{
return lastId_;
}

virtual int affectedRowCount()
{
return affectedRows_;
}

virtual bool nextRow()
{
try
{
if (this->m_stmt.intf())
return this->m_stmt->Fetch();
else
throw FirebirdException("Sql statement not assigned (bool nextRow())");
}
catch(IBPP::Exception &e)
{
throw FirebirdException(e.what());
}
return false;
}

virtual bool getResult(int column, std::string *value, int size)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;
if (size > 0)
this->m_stmt->Get(column, const_cast<char *>(value->data()), size);
else
this->m_stmt->Get(column, *value);

if ((value->size() > size) && (size > -1))
value->resize(size);

}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, std::string *value, int size))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, short *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, *value);
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, short *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, int *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, *value);
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, int *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, long long *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, *((int64_t *)value));
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, long long *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, float *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, *value);
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, float *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, double *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, *value);
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, double *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, boost::posix_time::ptime *value,
SqlDateTimeType type)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(++column))
return false;

switch(type)
{
case SqlDate:
{
Date d;
this->m_stmt->Get(column, d);
*value = boost::posix_time::ptime(boost::gregorian::date(d.Year(), d.Month(), d.Day()));
break;
}
case SqlDateTime:
{
Timestamp tm;
this->m_stmt->Get(column, tm);
*value = boost::posix_time::ptime(boost::gregorian::date(tm.Year(), tm.Month(), tm.Day()), boost::posix_time::hours(tm.Hours()) + boost::posix_time::minutes(tm.Minutes()) + boost::posix_time::seconds(tm.Seconds()) + boost::posix_time::microseconds(tm.SubSeconds() * 100));
break;
}
case SqlTime:
{
Time t;
this->m_stmt->Get(column, t);
*value = boost::posix_time::ptime(boost::gregorian::date(0, 0, 0), boost::posix_time::hours(t.Hours()) + boost::posix_time::minutes(t.Minutes()) + boost::posix_time::seconds(t.Seconds()) + boost::posix_time::microseconds(t.SubSeconds() * 100));
break;
}
}
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, boost::posix_time::ptime *value, SqlDateTimeType type))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}


/**
* There's no such thing in FirebirdSQL like PostgreSQL time interval.
*/
virtual bool getResult(int column, boost::posix_time::time_duration *value)
{
if (this->m_stmt.intf())
{
if (this->m_stmt->IsNull(column))
return false;
Time t;
this->m_stmt->Get(column, t);
*value = boost::posix_time::time_duration(boost::posix_time::hours(t.Hours()) + boost::posix_time::minutes(t.Minutes()) + boost::posix_time::seconds(t.Seconds()) + boost::posix_time::microseconds(t.SubSeconds() * 100));
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, boost::posix_time::time_duration *value))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual bool getResult(int column, std::vector<unsigned char> *value,
int size)
{
if (this->m_stmt.intf())
{
boost::scoped_array<char> result(new char[size]);
if (this->m_stmt->IsNull(++column))
return false;

this->m_stmt->Get(column, result.get(), size);

for(int i = 0; i < size; i++)
value->push_back(result[i]);
}
else
throw FirebirdException("Sql statement not assigned (bool getResult(int column, std::vector<unsigned char> *value, int size))");

DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

return true;
}

virtual std::string sql() const {
return sql_;
}

private:

Firebird& conn_;
std::string sql_;
IBPP::Statement m_stmt;
char name_[64];

int lastId_, row_, affectedRows_;


void setValue(int column, const std::string& value)
{
this->m_stmt->Set(column + 1, value);
}

std::string convertToNumberedPlaceholders(const std::string& sql)
{
return sql;
}
};

Firebird::Firebird()
: m_writableTransaction(false)
{
}

Firebird::Firebird(IBPP::Database db)
: m_writableTransaction(false)
{
this->m_db = db;
}

Firebird::Firebird(const std::string& ServerName,
const std::string& DatabaseName, const std::string& UserName,
const std::string& UserPassword, const std::string& RoleName,
const std::string& CharSet, const std::string& CreateParams)
{
this->m_db = IBPP::DatabaseFactory(ServerName, DatabaseName, UserName, UserPassword, RoleName, CharSet, CreateParams);
this->m_db->Connect();
}

Firebird::Firebird(const Firebird& other)
: SqlConnection(other)
, m_writableTransaction(other.m_writableTransaction)
{
this->m_db = IBPP::DatabaseFactory(other.m_db->ServerName()
,other.m_db->DatabaseName()
,other.m_db->Username()
,other.m_db->UserPassword()
,other.m_db->RoleName()
,other.m_db->CharSet()
,other.m_db->CreateParams());
this->m_db->Connect();
}

Firebird::~Firebird()
{
clearStatementCache();
}

Firebird *Firebird::clone() const
{
return new Firebird(*this);
}

bool Firebird::connect(const std::string& ServerName,
const std::string& DatabaseName, const std::string& UserName,
const std::string& UserPassword, const std::string& RoleName,
const std::string& CharSet, const std::string& CreateParams)
{

this->m_db = IBPP::DatabaseFactory(ServerName, DatabaseName, UserName, UserPassword, RoleName, CharSet, CreateParams);
this->m_db->Connect();
return true;
}

SqlStatement *Firebird::prepareStatement(const std::string& sql)
{
return new FirebirdStatement(*this, sql);
}

void Firebird::executeSql(const std::string &sql)
{

if (showQueries())
std::cerr << sql << std::endl;
SqlStatement *stmt = 0;

try
{
this->startTransaction();
stmt = this->prepareStatement(sql);

stmt->execute();

this->commitTransaction();
}
catch(...)
{
if (this->m_tra.intf())
if (this->m_tra->Started())
this->m_tra->Rollback();
}
if (stmt)
delete stmt;
}

/**
* Auto increment in Firebird is done thru Before Insert Trigger...
*/
std::string Firebird::autoincrementType() const
{
return std::string();
}

std::string Firebird::autoincrementSql() const
{
return std::string();
}

std::string Firebird::autoincrementInsertSuffix() const
{
return std::string();
}

const char *Firebird::dateTimeType(SqlDateTimeType type) const
{
switch (type) {
case SqlDate:
return "date";
case SqlDateTime:
return "timestamp";
case SqlTime:
return "time";
}

std::stringstream ss;
ss << __FILE__ << ":" << __LINE__ << ": implementation error";
throw FirebirdException(ss.str());
}

const char *Firebird::blobType() const
{
return "blob";
}

void Firebird::startTransaction()
{
if (!this->m_tra.intf())
{
if (this->m_writableTransaction)
{
m_tra = IBPP::TransactionFactory(this->m_db, amWrite, ilReadCommitted, lrWait);
}
else
{
m_tra = IBPP::TransactionFactory(this->m_db, amRead, ilReadCommitted, lrWait);
}
}
this->m_tra->Start();
}

void Firebird::commitTransaction()
{
if (this->m_tra.intf())
this->m_tra->Commit();
}

void Firebird::rollbackTransaction()
{
if (this->m_tra.intf())
this->m_tra->Rollback();
}

}
}
}
(2-2/2)