Click here to Skip to main content
15,886,807 members
Search within:


Articles / Database Development / SQL Server
 

PostgreSQL/libpqxx Class Generator

Rate me:
Please Sign up or sign in to vote.
4.87/5 (15 votes)
18 Aug 200627 min read 82.7K   1.9K   37  
Automated generation of PostgreSQL data transfer classes.
 
/***************************************************************************
 pqxxTransfer.h - Declaration of the CPqxxTransfer class template, and
                  various inline functions that facilitate conversion
				  between data members and their SQL-safe representations.

 begin     : August 2006
 copyright : (C) 2006 by Phil Cairns
 email     : oti169@hotmail.com
 $Id: pqxxTransfer.h 2080 2006-08-18 01:53:19Z phil $

 This code may be used in compiled form in any way you desire (including
 commercial use). The code may be redistributed unmodified by any means
 providing it is not sold for profit without the authors written consent,
 and providing that this notice and the authors name and all copyright
 notices remains intact.

 This software is provided "as is" without express or implied warranty. Use
 it at your own risk!
 ***************************************************************************/

#pragma once

#include <pqxx/connection>
#include <pqxx/transaction>

#if !defined(__ATLCURR_H__)
#error This file requires <atlcurr.h> to be included
#endif
#if !defined(__ATLCOMTIME_H__)
#error This file requires <atlcomtime.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)
		{
			int charCount = (int)log10(fabs(val)) + 10;
			char* buf = new char[charCount];
			sprintf(buf, "%.6f", val);
			ret = buf;
			delete[] 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


Written By
_oti
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 6 Jan 2005

Article Copyright 2005 by _oti
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-04-02:1