|
#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();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|