Project

General

Profile

Download (23.2 KB)

Feature #998 » mir_firebird.cpp

Łukasz Matuszewski, 09/23/2011 10:59 AM

 
#include <Wt/WApplication>
#include <Wt/WLogger>
#include "mir_firebird.h"

//#include "mir_exception.h"
#include "Wt/Dbo/Exception"

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

#include <boost/lexical_cast.hpp>
#include <iostream>
#include <vector>
#include <sstream>

#include <unicode/fmtable.h>
#include <unicode/msgfmt.h>

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

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

#define BYTEAOID 17

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

#include <ibpp.h>

namespace SyNaT
{
namespace MIR
{

using namespace Wt::Dbo;
using namespace Wt;

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;
//result_ = 0;

paramValues_ = 0;
paramTypes_ = paramLengths_ = paramFormats_ = 0;

this->m_stmt = 0;

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

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

Transaction tr = conn_.transaction();
// if (!this->m_stmt)
// {
m_stmtnp = conn_.connection()->createStatement(&tr);
this->m_stmt = &m_stmtnp;
// }
//(*this->m_stmt)->TransactionPtr().
try
{
(*this->m_stmt)->Prepare(sql_);
// wApp->log("info") << sql_;
}
catch(IBPP::LogicException &e)
{
throw FirebirdException(e.what());
}

state_ = Done;
}

virtual ~FirebirdStatement()
{
//PQclear(result_);
if (paramValues_)
delete[] paramValues_;
if (paramTypes_)
delete[] paramTypes_;
}

virtual void reset()
{
state_ = Done;
}

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

setValue(column, value);
}

virtual void bind(int column, short value)
{
bind(column, static_cast<int>(value));
}

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

setValue(column, boost::lexical_cast<std::string>(value));
}

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

setValue(column, boost::lexical_cast<std::string>(value));
}

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

setValue(column, boost::lexical_cast<std::string>(value));
}

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

setValue(column, boost::lexical_cast<std::string>(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);

std::string v = boost::posix_time::to_simple_string(value);

setValue(column, v);
}

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);

std::string v;
if (type == SqlDate)
v = boost::gregorian::to_iso_extended_string(value.date());
else
{
v = boost::posix_time::to_iso_extended_string(value);
v[v.find('T')] = ' ';
}

setValue(column, v);
}

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

for (int i = (int)params_.size(); i <= column; ++i)
params_.push_back(Param());

Param& p = params_[column];
p.value.resize(value.size());
if (value.size() > 0)
memcpy(const_cast<char *>(p.value.data()), &(*value.begin()),
value.size());
p.isbinary = true;
p.isnull = false;

// FIXME if first null was bound, check here and invalidate the prepared
// statement if necessary because the type changes
}

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

for (int i = (int)params_.size(); i <= column; ++i)
params_.push_back(Param());

params_[column].isnull = true;
}

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

// if (!result_)
// {
paramValues_ = new char *[params_.size()];

for (unsigned i = 0; i < params_.size(); ++i)
{
if (params_[i].isbinary)
{
paramTypes_ = new int[params_.size() * 3];
paramLengths_ = paramTypes_ + params_.size();
paramFormats_ = paramLengths_ + params_.size();
for (unsigned j = 0; j < params_.size(); ++j)
{
paramTypes_[j] = params_[j].isbinary ? BYTEAOID : 0;
paramFormats_[j] = params_[j].isbinary ? 1 : 0;
paramLengths_[j] = 0;
}
break;
}
// }

// result_ = PQprepare(conn_.connection(), name_, sql_.c_str(),
// paramTypes_ ? params_.size() : 0, (Oid *)paramTypes_);
// handleErr(PQresultStatus(result_));
}

// Transaction tr = conn_.transaction();
//// if (!this->m_stmt)
//// {
// m_stmtnp = conn_.connection()->createStatement(&tr);
// this->m_stmt = &m_stmtnp;
//// }
// //(*this->m_stmt)->TransactionPtr().
// try
// {
// (*this->m_stmt)->Prepare(sql_);
//// wApp->log("info") << sql_;
// }
// catch(IBPP::LogicException &e)
// {
// throw FirebirdException(e.what());
// }



for (unsigned i = 0; i < params_.size(); ++i)
{
if (params_[i].isnull)
{
paramValues_[i] = 0;
(*this->m_stmt)->SetNull(i + 1);
}
else
if (params_[i].isbinary)
{
paramValues_[i] = const_cast<char *>(params_[i].value.data());
paramLengths_[i] = (int) params_[i].value.length();
(*this->m_stmt)->Set(i + 1, paramValues_[i], paramLengths_[i]);
}
else
{
paramValues_[i] = const_cast<char *>(params_[i].value.c_str());
switch((*this->m_stmt)->ParameterType(i + 1))
{
case sdString:
(*this->m_stmt)->Set(i + 1, paramValues_[i]);
break;
case sdBlob:
break;

case sdDate:
break;

case sdTime:
break;

case sdTimestamp:
break;

case sdFloat:
case sdDouble:
case sdInteger:
case sdSmallint:
case sdLargeint:
Formattable f;
UErrorCode status = U_ZERO_ERROR;
f.setDecimalNumber(paramValues_[i], status);
switch(f.getType())
{
case Formattable::kDouble:
(*this->m_stmt)->Set(i + 1, f.getDouble());
break;
case Formattable::kInt64:
(*this->m_stmt)->Set(i + 1, f.getInt64());
break;
case Formattable::kLong:
long l = f.getLong();
(*this->m_stmt)->Set(i + 1, l);
break;
}

break;

}
}


}
try
{

(*this->m_stmt)->Execute();
// if (sql_.find("select count(1)") == 0)
// {
// if ((*this->m_stmt)->Fetch())
// (*this->m_stmt)->Get(1, affectedRows_);
// }
// else
// {
affectedRows_ = (*this->m_stmt)->AffectedRows();
//(*this->m_stmt)->Fetch();
// }


row_ = 0;
// if((*this->m_stmt)->Fetch())
// (*this->m_stmt)->Get(1, lastId_);
}
catch(IBPP::LogicException &e)
{
throw FirebirdException(e.what());
}
// if (PQresultStatus(result_) == PGRES_COMMAND_OK) {
// std::string s = PQcmdTuples(result_);
// if (!s.empty())
// affectedRows_ = boost::lexical_cast<int>(s);
// else
// affectedRows_ = 0;
// } else if (PQresultStatus(result_) == PGRES_TUPLES_OK)
// affectedRows_ = PQntuples(result_);
//
// bool isInsertReturningId = false;
// if (affectedRows_ == 1) {
// const std::string returning = " returning ";
// std::size_t j = sql_.rfind(returning);
// if (j != std::string::npos
// && sql_.find(' ', j + returning.length()) == std::string::npos)
// isInsertReturningId = true;
// }
//
// if (isInsertReturningId) {
// state_ = NoFirstRow;
// if (PQntuples(result_) == 1 && PQnfields(result_) == 1) {
// lastId_ = boost::lexical_cast<long long>(PQgetvalue(result_, 0, 0));
// }
// } else {
// if (PQntuples(result_) == 0) {
// state_ = NoFirstRow;
// } else {
// state_ = FirstRow;
// }
// }
//
// handleErr(PQresultStatus(result_));
}

virtual long long insertedId()
{
return lastId_;
}

virtual int affectedRowCount()
{
return affectedRows_;
}

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

virtual bool getResult(int column, std::string *value, int size)
{
if (this->m_stmt && this->m_stmt->intf())
{
if ((*this->m_stmt)->IsNull(++column))
return false;

(*this->m_stmt)->Get(column, *value);
//value->resize(size);
}
else
return false;

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

return true;
}

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

(*this->m_stmt)->Get(column, *value);
//value->resize(size);
}
else
return false;

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

return true;
}

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

(*this->m_stmt)->Get(column, *value);
//value->resize(size);
}
else
return false;

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

return true;
}

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

(*this->m_stmt)->Get(column, *((int64_t *)value));
//value->resize(size);
}
else
return false;

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

return true;
}

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

(*this->m_stmt)->Get(column, *value);
//value->resize(size);
}
else
return false;

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

return true;
}

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

(*this->m_stmt)->Get(column, *value);
//value->resize(size);
}
else
return false;

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 && 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));
}
}
//value->resize(size);
}
else
return false;

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

return true;
}

virtual bool getResult(int column, boost::posix_time::time_duration *value)
{
// if (!this->m_stmt)
// {
// 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));
// }
// //value->resize(size);
// }
// else
return false;

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

// return true;
}

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

(*this->m_stmt)->Get(column, result, size);
//value->resize(size);

for(int i = 0; i < size; i++)
{
value->push_back(result[i]);
}
}
else
{
delete[] result;
return false;
}
DEBUG(std::cerr << this
<< " result string " << column << " " << *value << std::endl);

delete[] result;
return true;
}

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

private:
struct Param {
std::string value;
bool isnull, isbinary;

Param() : isnull(true), isbinary(false) { }
};

Firebird& conn_;
std::string sql_;
IBPP::Statement *m_stmt;
IBPP::Statement m_stmtnp;
char name_[64];
//PGresult *result_;
enum { NoFirstRow, FirstRow, NextRow, Done } state_;
std::vector<Param> params_;

char **paramValues_;
int *paramTypes_, *paramLengths_, *paramFormats_;

int lastId_, row_, affectedRows_;

// void handleErr(int err)
// {
// if (err != PGRES_COMMAND_OK && err != PGRES_TUPLES_OK)
// throw PostgresException(PQerrorMessage(conn_.connection()));
// }

void setValue(int column, const std::string& value)
{
for (int i = (int)params_.size(); i <= column; ++i)
params_.push_back(Param());

params_[column].value = value;
params_[column].isnull = false;
}

std::string convertToNumberedPlaceholders(const std::string& sql)
{
// std::stringstream result;
//
// enum { Statement, SQuote, DQuote } state = Statement;
// int placeholder = 1;
//
// for (unsigned i = 0; i < sql.length(); ++i) {
// switch (state) {
// case Statement:
// if (sql[i] == '\'')
// state = SQuote;
// else if (sql[i] == '"')
// state = DQuote;
// else if (sql[i] == '?') {
// result << '$' << placeholder++;
// continue;
// }
// break;
// case SQuote:
// if (sql[i] == '\'') {
// if (i + 1 == sql.length())
// state = Statement;
// else if (sql[i + 1] == '\'') {
// result << sql[i];
// ++i; // skip to next
// } else
// state = Statement;
// }
// break;
// case DQuote:
// if (sql[i] == '"')
// state = Statement;
// break;
// }
// result << sql[i];
// }
//
// return result.str();
return sql;
}
};

Firebird::Firebird()
: m_tr(NULL), m_writableTransaction(false), m_dmOwned(true)
{
this->m_dm = new DatabaseModule();
}

Firebird::Firebird(DatabaseModule *dm)
: m_dm(dm), m_tr(NULL), m_writableTransaction(false), m_dmOwned(false)
{
// if (!db.empty())
// connect(db);
}

Firebird::Firebird(const Firebird& other)
: SqlConnection(other), m_dm(other.m_dm), m_tr(other.m_tr)
, m_writableTransaction(other.m_writableTransaction), m_dmOwned(false)
{
// if (!other.connInfo_.empty())
// this->connect(other.connInfo_);
}

Firebird::~Firebird()
{
clearStatementCache();
// if (conn_)
// PQfinish(conn_);
if (this->m_dmOwned)
if (this->m_dm)
delete this->m_dm;
}

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

bool Firebird::connect(const std::string& db)
{
if (!this->m_dm)
return false;

// connInfo_ = db;
// conn_ = PQconnectdb(db.c_str());
//
// if (PQstatus(conn_) != CONNECTION_OK) {
// std::string error = PQerrorMessage(conn_);
// PQfinish(conn_);
// conn_ = 0;
// throw PostgresException("Could not connect to: " + error);
// }
//
// PQsetClientEncoding(conn_, "UTF8");

this->m_dm->connect(this->m_dm->getServerName(), db,
this->m_dm->getUserName(), this->m_dm->getUserPassword(),
this->m_dm->getRoleName(), this->m_dm->getCharset(),
this->m_dm->getCreateParams());
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_tr)
if ((*this->m_tr)->Started())
(*this->m_tr)->Rollback();
}
if (stmt)
delete stmt;

// result = PQexec(conn_, sql.c_str());
// err = PQresultStatus(result);
// if (err != PGRES_COMMAND_OK && err != PGRES_TUPLES_OK) {
// PQclear(result);
// throw PostgresException(PQerrorMessage(conn_));
// }
// PQclear(result);
}

std::string Firebird::autoincrementType() const
{
//return "serial";
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 not null";
}

void Firebird::startTransaction()
{
// if (this->m_tr)
// delete this->m_tr;
if (!this->m_tr)
{
if (this->m_writableTransaction)
{
m_tra = this->m_dm->createTransaction(amWrite, ilReadCommitted, lrWait);
this->m_tr = &m_tra;
}
else
{
m_tra = this->m_dm->createTransaction(amRead, ilReadCommitted, lrWait);
this->m_tr = &m_tra;
}
}
(*this->m_tr)->Start();
}

void Firebird::commitTransaction()
{
if (this->m_tr && this->m_tr->intf())
(*this->m_tr)->Commit();
}

void Firebird::rollbackTransaction()
{
if (this->m_tr && this->m_tr->intf())
(*this->m_tr)->Rollback();
}

}
}

(3-3/3)