/*
$Id: pqxxTransfer.h 2080 2006-08-18 01:53:19Z phil $
*/
#pragma once
#include <pqxx/connection>
#include <pqxx/transaction>
#if !defined(__ATLCURR_H__)
#error This file requires <atlcurr.h> to be included
#endif
#define NULLINT 0x80000000
#define NULLDBL ((double)0xFFFFFFFFFFFFFFFF)
class dmwork;
namespace pqxx
{
// Render a value as a safe SQL value
inline std::string safe_value_i(const std::string& val, const char* nullStr)
{
std::string ret = nullStr;
if (val.length() > 0)
{
ret.assign("'");
ret.append(pqxx::sqlesc(val));
ret.append("'");
}
return ret;
}
inline std::string safe_value_i(const int& val, const char* nullStr)
{
std::string ret = nullStr;
if (val != NULLINT)
{
char buf[20];
ret = itoa(val, buf, 10);
}
return ret;
}
inline std::string safe_value_i(const double& val, const char* nullStr)
{
std::string ret = nullStr;
if (val != NULLDBL)
{
char buf[20];
sprintf(buf, "%f", val);
ret = buf;
}
return ret;
}
inline std::string safe_value_i(const COleDateTime& val, const char* nullStr)
{
std::string ret = nullStr;
if (val.GetStatus() != COleDateTime::invalid)
{
ret = (const char*)val.Format("'%Y-%m-%d %H:%M:%S'");
}
return ret;
}
inline std::string safe_value_i(const bool& val, const char* nullStr)
{
return val ? "'T'" : "'F'";
}
inline std::string safe_value_i(const COleCurrency& val, const char* nullStr)
{
std::string ret = nullStr;
if (val.GetStatus() != COleCurrency::invalid)
{
ret = (const char*)val.Format();
}
return ret;
}
template<typename T> inline std::string safe_value(const T& pVal, const char* nullStr="NULL")
{
return safe_value_i(pVal, nullStr);
}
// Reset methods
template<typename T> inline void reset(T& obj) { return reset(obj);}
template<> inline void reset(std::string& s) { s = ""; }
template<> inline void reset(int& i) { i = NULLINT; }
template<> inline void reset(double& f) { f = NULLDBL; }
template<> inline void reset(COleDateTime& dt) { dt.m_dt = 0; dt.m_status = COleDateTime::invalid; }
template<> inline void reset(bool& b) { b = false; }
template<> inline void reset(COleCurrency& cy) { cy.m_cur.int64 = 0; cy.m_status = COleCurrency::invalid; }
// Extra from_string methods for dates and currencies
template<> inline void from_string(const char str[], COleDateTime &obj)
{
std::string s = str;
size_t dotPos = s.find('.');
if (dotPos != std::string::npos)
{
s = s.substr(0, dotPos);
}
COleDateTime result;
result.ParseDateTime(s.c_str());
obj = result;
}
template<> inline void from_string(const char str[], COleCurrency &obj)
{
COleCurrency result;
result.ParseCurrency(str);
obj = result;
}
}
namespace Database
{
enum EPQXXCOLTYPE
{
EPCT_NULL,
EPCT_INT,
EPCT_STRING,
EPCT_BOOL,
EPCT_DOUBLE,
EPCT_DATE,
EPCT_CURR
};
struct PQXXCOLDESC
{
size_t fieldNo;
std::string fieldName;
void *dataAddress;
EPQXXCOLTYPE colType;
bool isIndex;
};
template <class T>
class CPqxxTransfer
{
public:
CPqxxTransfer<T>(const char* szTable = 0)
{
if (szTable)
{
_table = szTable;
}
}
public:
void where(const std::string& where) { _where = where; }
const std::string& where() const { return _where; }
void whereIDEq(int idVal)
{
std::string s("id=");
where(s.append(pqxx::safe_value(idVal)));
}
void order(const std::string& order) { _order = order; }
const std::string& order() const { return _order; }
const std::string& table() const { return _table; }
std::string query() const
{
std::string fldList;
const PQXXCOLDESC* pcd = colDescs();
int numCols = columnCount();
ATLASSERT(numCols > 0);
for (int colCtr = 0; colCtr < numCols; colCtr++, pcd++)
{
fldList.append(",").append(pcd->fieldName);
}
std::string ret("select ");
ret.append(fldList.substr(1));
ret.append(" from ").append(table());
if (_where != "")
{
ret += " where " + _where;
}
if (_order != "")
{
ret += " order by " + _order;
}
return ret;
}
protected:
int getInsertedID(dmwork& trx)
{
std::string query("select currval('");
query.append(table()).append("_id_seq')");
int ret = NULLINT;
pqxx::result res = trx.exec(query);
pqxx::result::const_iterator rit = res.begin();
if (rit != res.end())
{
rit->at(0).to(ret);
}
return ret;
}
void table(const std::string& tbl) { _table = tbl; }
virtual size_t columnCount() const = 0;
virtual const PQXXCOLDESC* colDescs() const = 0;
virtual void initColDescs() = 0;
virtual void xferValues(const pqxx::result::const_iterator& src)
{
const PQXXCOLDESC* pcd = colDescs();
int numCols = columnCount();
ATLASSERT(numCols > 0);
for (int colCtr = 0; colCtr < numCols; colCtr++)
{
ATLASSERT(pcd->fieldNo >= 0 && pcd->fieldNo < src->size());
switch (pcd->colType)
{
case EPCT_INT:
pqxx::reset(*((int*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((int*)pcd->dataAddress));
break;
case EPCT_STRING:
pqxx::reset(*((std::string*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((std::string*)pcd->dataAddress));
break;
case EPCT_BOOL:
pqxx::reset(*((bool*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((bool*)pcd->dataAddress));
break;
case EPCT_DOUBLE:
pqxx::reset(*((double*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((double*)pcd->dataAddress));
break;
case EPCT_DATE:
pqxx::reset(*((COleDateTime*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((COleDateTime*)pcd->dataAddress));
break;
case EPCT_CURR:
pqxx::reset(*((COleCurrency*)pcd->dataAddress));
src.at(pcd->fieldNo).to(*((COleCurrency*)pcd->dataAddress));
break;
}
++pcd;
}
}
void update(dmwork& trx)
{
std::string query("UPDATE ");
query.append(table()).append(" set ");
size_t numCols = columnCount();
ATLASSERT(numCols > 0);
for (size_t passCtr = 0; passCtr < 2; passCtr++)
{
const PQXXCOLDESC* pcd = colDescs();
int processedFields = 0;
for (size_t colCtr = 0; colCtr < numCols; colCtr++)
{
ATLASSERT(pcd->fieldNo >= 0 && pcd->fieldNo < numCols);
if ((passCtr == 0 && !pcd->isIndex) || (passCtr == 1 && pcd->isIndex))
{
if (processedFields > 0)
{
query.append(passCtr == 0 ? "," : " AND ");
}
query.append(pcd->fieldName).append("=");
switch (pcd->colType)
{
case EPCT_INT:
query.append(pqxx::safe_value(*((int*)pcd->dataAddress)));
break;
case EPCT_STRING:
query.append(pqxx::safe_value(*((std::string*)pcd->dataAddress)));
break;
case EPCT_BOOL:
query.append(pqxx::safe_value(*((bool*)pcd->dataAddress)));
break;
case EPCT_DOUBLE:
query.append(pqxx::safe_value(*((double*)pcd->dataAddress)));
break;
case EPCT_DATE:
query.append(pqxx::safe_value(*((COleDateTime*)pcd->dataAddress)));
break;
case EPCT_CURR:
query.append(pqxx::safe_value(*((COleCurrency*)pcd->dataAddress)));
break;
}
++processedFields;
}
++pcd;
}
if (passCtr == 0)
{
query.append(" WHERE ");
}
}
//::OutputDebugString((query + "\n").c_str());
trx.exec(query);
}
void insert(dmwork& trx)
{
std::string query("INSERT INTO ");
std::string values;
query.append(table()).append(" (");
size_t numCols = columnCount();
ATLASSERT(numCols > 0);
const PQXXCOLDESC* pcd = colDescs();
int processedFields = 0;
size_t colCtr = 0;
for (; colCtr < numCols; colCtr++)
{
ATLASSERT(pcd->fieldNo >= 0 && pcd->fieldNo < numCols);
if (!pcd->isIndex)
{
if (processedFields > 0)
{
query.append(",");
values.append(",");
}
query.append(pcd->fieldName);
switch (pcd->colType)
{
case EPCT_INT:
values.append(pqxx::safe_value(*((int*)pcd->dataAddress)));
break;
case EPCT_STRING:
values.append(pqxx::safe_value(*((std::string*)pcd->dataAddress)));
break;
case EPCT_BOOL:
values.append(pqxx::safe_value(*((bool*)pcd->dataAddress)));
break;
case EPCT_DOUBLE:
values.append(pqxx::safe_value(*((double*)pcd->dataAddress)));
break;
case EPCT_DATE:
values.append(pqxx::safe_value(*((COleDateTime*)pcd->dataAddress)));
break;
case EPCT_CURR:
values.append(pqxx::safe_value(*((COleCurrency*)pcd->dataAddress)));
break;
}
++processedFields;
}
++pcd;
}
query.append(") VALUES (").append(values).append(")");
//::OutputDebugString((query + "\n").c_str());
trx.exec(query);
// Put the new id into the index field.
pcd = colDescs();
for (colCtr = 0; colCtr < numCols; colCtr++)
{
if (pcd->isIndex)
{
*((int*)pcd->dataAddress) = getInsertedID(trx);
break;
}
++pcd;
}
}
public:
void save(dmwork& trx)
{
bool shouldInsert = false;
int indexCount = 0;
size_t numCols = columnCount();
ATLASSERT(numCols > 0);
const PQXXCOLDESC* pcd = colDescs();
for (size_t colCtr = 0; colCtr < numCols && !shouldInsert; colCtr++)
{
if (pcd->isIndex)
{
switch (pcd->colType)
{
case EPCT_INT:
shouldInsert = (*((int*)pcd->dataAddress) == NULLINT);
break;
case EPCT_STRING:
shouldInsert = (*((std::string*)pcd->dataAddress) == "");
break;
case EPCT_DOUBLE:
shouldInsert = (*((double*)pcd->dataAddress) == NULLDBL);
break;
case EPCT_DATE:
shouldInsert = (((COleDateTime*)pcd->dataAddress)->GetStatus() == COleDateTime::invalid);
break;
case EPCT_CURR:
shouldInsert = (((COleCurrency*)pcd->dataAddress)->GetStatus() == COleCurrency::invalid);
break;
}
++indexCount;
}
++pcd;
}
T* pT = (T*)this;
if (shouldInsert || indexCount == 0)
{
pT->insert(trx);
}
else
{
pT->update(trx);
}
}
void del(dmwork& trx)
{
std::string query("DELETE FROM ");
query.append(table()).append(" WHERE ");
size_t numCols = columnCount();
ATLASSERT(numCols > 0);
const PQXXCOLDESC* pcd = colDescs();
int processedFields = 0;
for (size_t colCtr = 0; colCtr < numCols; colCtr++)
{
ATLASSERT(pcd->fieldNo >= 0 && pcd->fieldNo < numCols);
if (pcd->isIndex)
{
if (processedFields > 0)
{
query.append(" AND ");
}
query.append(pcd->fieldName).append("=");
switch (pcd->colType)
{
case EPCT_INT:
query.append(pqxx::safe_value(*((int*)pcd->dataAddress)));
break;
case EPCT_STRING:
query.append(pqxx::safe_value(*((std::string*)pcd->dataAddress)));
break;
case EPCT_BOOL:
query.append(pqxx::safe_value(*((bool*)pcd->dataAddress)));
break;
case EPCT_DOUBLE:
query.append(pqxx::safe_value(*((double*)pcd->dataAddress)));
break;
case EPCT_DATE:
query.append(pqxx::safe_value(*((COleDateTime*)pcd->dataAddress)));
break;
case EPCT_CURR:
query.append(pqxx::safe_value(*((COleCurrency*)pcd->dataAddress)));
break;
}
++processedFields;
}
++pcd;
}
trx.exec(query);
}
void load(dmwork& trx, int idVal)
{
whereIDEq(idVal);
pqxx::result dbr = trx.exec(query());
if (dbr.size() == 1)
{
*((T*)this) = dbr.begin();
}
}
private:
std::string _table;
std::string _order;
std::string _where;
};
}
#define BEGIN_DB_MAP(T, colCount) \
public: \
T& operator=(const pqxx::result::const_iterator& src) \
{ \
xferValues(src); \
return *this; \
} \
protected: \
size_t columnCount() const { return colCount; } \
const PQXXCOLDESC* colDescs() const { return _columnDescriptions; } \
private: \
PQXXCOLDESC _columnDescriptions[colCount+1]; \
protected: \
void initColDescs() \
{ \
PQXXCOLDESC* pDesc;
#define DB_COLUMN_ENTRY(fieldPos, member, epct, idx) \
pDesc = _columnDescriptions + fieldPos; \
pDesc->fieldNo = fieldPos; \
pDesc->fieldName = #member; \
pDesc->dataAddress = &member; \
pDesc->colType = epct; \
pDesc->isIndex = idx;
#define DB_INT_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_INT, false)
#define DB_STRING_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_STRING, false)
#define DB_BOOL_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_BOOL, false)
#define DB_DOUBLE_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_DOUBLE, false)
#define DB_DATE_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_DATE, false)
#define DB_CURR_ENTRY(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_CURR, false)
#define DB_INT_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_INT, true)
#define DB_STRING_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_STRING, true)
#define DB_BOOL_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_BOOL, true)
#define DB_DOUBLE_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_DOUBLE, true)
#define DB_DATE_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_DATE, true)
#define DB_CURR_INDEX(fieldPos, member) DB_COLUMN_ENTRY(fieldPos, member, EPCT_CURR, true)
#define END_DB_MAP() \
}