Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version
Go to top

PostgreSQL/libpqxx Class Generator

, 18 Aug 2006
Automated generation of PostgreSQL data transfer classes.
/*
$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() \
	}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

_oti

Australia Australia
No Biography provided

| Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 18 Aug 2006
Article Copyright 2005 by _oti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid