PostgreSQL/libpqxx Class Generator

/***************************************************************************
 DataManager.h - Singleton class that manages connection to the database.
                 This file also include the declaration of the dmwork class,
				 which is a custom transaction object, designed to work with
				 the data manager's connection pooling.

 begin     : August 2006
 copyright : (C) 2006 by Phil Cairns
 email     : oti169@hotmail.com
 $Id: DataManager.cpp 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!
 ***************************************************************************/

#include "StdAfx.h"
#include "resource.h"
#include ".\datamanager.h"

Pagaros::SyncObject DataManager::_trxSync;

DataManager::DataManager(void)
{
	_connStr.assign("dbname=test user=test password=testpwd host=localhost");

	bool bDBOpened = false;
	dmwork TRX(checkOutDbConn(), "TRX001", false);
	try
	{
		// Find out which tables we have in the database.
		__int64 versionNumber = 0x0000000000010000;
		pqxx::result dbr;
		pqxx::result::const_iterator rsit;
		bool bMustCreate = false;

		dbr = TRX.exec("select count(*) from pg_class where relname='sysparams'");
		rsit = dbr.begin();
		if (rsit != dbr.end())
		{
			int relCount = 0;
			rsit[0].to(relCount, 0);
			bMustCreate = (relCount == 0);
		}
		if (bMustCreate)
		{
			TRX.exec("create table sysparams ("
					"spname varchar(50) not null, "
					"spvalue text, "
					"constraint sysparams_pkey primary key (spname)"
				") without oids");
			TRX.exec("insert into sysparams (spname, spvalue) values ('dbversion', '0.0.1.0')");
		}
		else
		{
			dbr = TRX.exec("select spvalue from sysparams where spname='dbversion'");
			std::string sVersion;
			dbr.begin()->at(0).to(sVersion);
			strToVersion(sVersion, versionNumber);
		}

		if (versionNumber < 0x0000000000010001)
		{
			TRX.exec("create table articles ("
					"id serial primary key, "
					"author varchar(100) not null, "
					"title varchar(100) not null, "
					"content text, "
					"numwords int, "
					"archived char(1) default 'F', "
					"submitted timestamp not null, "
					"price numeric(11,4), "
					"weight float"
				") without oids");
			TRX.exec("update sysparams set spvalue='0.0.1.1' where spname='dbversion'");
			versionNumber = 0x0000000000010001;
		}

		//if (versionNumber < 0x0000000000010002)
		//{
		//	TRX.exec("update sysparams set spvalue='0.0.1.2' where spname='dbversion'");
		//	versionNumber = 0x0000000000010002;
		//}

		// keep all the changes
		TRX.commit();
	}
	catch (pqxx::sql_error& sqlEx)
	{
		MessageBox(0, sqlEx.what(), "DB Error", MB_ICONSTOP);
	}
	catch (const exception & ex)
	{
		MessageBox(0, ex.what(), "DB Error", MB_ICONSTOP);
	}
	checkInDbConn(TRX._myConn);
}

DataManager::~DataManager(void)
{
}

void DataManager::cleanUp()
{
	Pagaros::SyncLock sl(_dbConnPool);
	Pagaros::SyncList<pqxx::connection*>::iterator cpit;
	for (cpit = _dbConnPool.begin(); cpit != _dbConnPool.end(); cpit++)
	{
		pqxx::connection* pc = *cpit;
		if (pc->is_open())
		{
			pc->disconnect();
			delete pc;
		}
	}
}

pqxx::connection* DataManager::checkOutDbConn()
{
	Pagaros::SyncLock sl(_dbConnPool);
	pqxx::connection* ret = 0;
	if (_dbConnPool.empty())
	{
		ret = new pqxx::connection(connStr());
	}
	else
	{
		ret = _dbConnPool.front();
		_dbConnPool.pop_front();
	}
	return ret;
}

void DataManager::checkInDbConn(pqxx::connection* pConn)
{
	Pagaros::SyncLock sl(_dbConnPool);
	_dbConnPool.push_back(pConn);
}

bool DataManager::processSQLFile(dmwork& trx, const std::string& fName)
{
	FILE* fIn = fopen(fName.c_str(), "rt");
	if (fIn == 0)
	{
		return false;
	}

	char ch;
	int state = 0;
	std::string stmt;
	stmt.reserve(256);
	while (!feof(fIn))
	{
		ch = fgetc(fIn);
		switch (state)
		{
		case 0:	// normal text
			switch (ch)
			{
			case ';':
				ATLTRACE("%s\n", stmt.c_str());
				trx.exec(stmt);
				stmt.clear();
				break;

			case '\'':
				stmt.append(1, ch);
				state = 1;
				break;

			default:
				stmt.append(1, ch);
				break;
			}
			break;

		case 1: // in string
			if (ch == '\'')
			{
				state = 0;
			}
			stmt.append(1, ch);
			break;
		}
	}
	fclose(fIn);
	return true;
}

void DataManager::strToVersion(const std::string& s, __int64& i64v)
{
	i64v = 0;

	char buf[MAX_PATH];
	const char *szTok = 0;

	memset(buf, 0, MAX_PATH);
	strncpy(buf, s.c_str(), MAX_PATH - 1);
	szTok = strtok(buf, ".");
	while (szTok != 0)
	{
		i64v = i64v << 16;
		i64v += (short)atoi(szTok);
		szTok = strtok(0, ".");
	}
}

void DataManager::versionToStr(__int64 i64v, std::string& s)
{
	char buf[MAX_PATH];

	sprintf(buf,
		"%I64d.%I64d.%I64d.%I64d",
		(i64v & 0xFFFF000000000000) >> 48,
		(i64v & 0x0000FFFF00000000) >> 32,
		(i64v & 0x00000000FFFF0000) >> 16,
		(i64v & 0x000000000000FFFF) >> 0);

	s = buf;
}