/////////////////////////////////////////////////////////////////////////////
// Name:        pgdatabase.cpp
// Author:      Stefan Liersch
// Contact:		stefan.liersch@ufz.de stefan.liersch@googlemail.com
// 
// Date:			2007-02-05
// last modified:	2007-06-23
/////////////////////////////////////////////////////////////////////////////

#include <iostream>	// for test output only
#include <fstream>	// for test output only
using std::ofstream;// for test output only
using std::endl;	// for test output only


#include "pgdatabase.h"

#include "helper.h"		// using function MSG_General_Add()
#define MSG_ADD(s)	MSG_General_Add(s, true, true);

///////////////////////////////////////////////////////////////////////
//
//								CONSTRUCTOR
//
///////////////////////////////////////////////////////////////////////

Cpgdatabase::Cpgdatabase()
{
}

Cpgdatabase::Cpgdatabase(char *pghost, char *pgport,
						 char *pgoptions, char *pgtty,
						 char *dbname, char *user, char *password)
{
	this->pghost	= pghost;
	this->pgport	= pgport;
	this->pgoptions	= pgoptions;
	this->pgtty		= pgtty;
	this->dbname	= dbname;
	this->user		= user;
	this->password	= password;

	// connect database
	conn = PQsetdbLogin(pghost,pgport,pgoptions,pgtty,dbname,user,password);
	if (PQstatus(conn) != CONNECTION_OK)
	{
		PQfinish(conn);
		b_conn = false;
	} else {
		b_conn = true;
	}
}

// DESTRUCTOR
Cpgdatabase::~Cpgdatabase()
{
	PQfinish(conn);
}

///////////////////////////////////////////////////////////////////////
//
//						PUBLIC MEMBER FUNCTIONS
//
///////////////////////////////////////////////////////////////////////

//---------------------------------------------------------------------
bool Cpgdatabase::ConnectToDB(string pghost, string pgport,
					   string pgoptions, string pgtty,
					   string dbname, string user, string password)
{
	// !!!!!!!!!!!!
	// this function is not used for connection anymore
	// PQconnectdb is used and should be used instead
	try
	{
		// make a connection to the database
		conn = PQsetdbLogin(pghost.c_str(),pgport.c_str(),
							pgoptions.c_str(),pgtty.c_str(),
							dbname.c_str(),user.c_str(),password.c_str());
	}
	catch(...)
	{
		return(false);
	}

	if (PQstatus(conn) != CONNECTION_OK)
	{
		PQfinish(conn);
		b_conn = false;
		return(false);
	} else {
		b_conn = true;
		return(true);
	}
}
//---------------------------------------------------------------------
bool Cpgdatabase::ConnectToDB(const char* connInfo)
{
	try
	{
		// make a connection to the database using PQconnectdb
		conn = PQconnectdb(connInfo);
	}
	catch(...)
	{
		return(false);
	}

	if (PQstatus(conn) != CONNECTION_OK)
	{
		PQfinish(conn);
		b_conn = false;
		return(false);
	} else {
		b_conn = true;
		return(true);
	}
}

//---------------------------------------------------------------------
void Cpgdatabase::ExitDB()
{
	if (b_conn) PQfinish(conn);
	b_conn = false;
}
//---------------------------------------------------------------------

int Cpgdatabase::GetNumberCols(const char *tablename)
{
	int ncols = 0;
	PGresult *res;
	string sql = "SELECT * FROM ";
	sql +=  tablename;
	sql += " LIMIT 1";

	res = PQexec(conn, (const char*)sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		MSG_ADD("unable to open table");
		MSG_ADD(wxString::Format("%s", PQresultErrorMessage(res)));
		PQclear(res);
		return(0);
	} else {
		ncols = PQnfields(res);
		PQclear(res);
		return(ncols);
	}
}
//---------------------------------------------------------------------

int Cpgdatabase::GetNumberRows(const char *tablename)
{
	int nrows = 0;
	PGresult *res;
	string sql = "SELECT * FROM ";
	sql +=  tablename;

	res = PQexec(conn, (const char*)sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		MSG_ADD("unable to open table");
		MSG_ADD(wxString::Format("%s", PQresultErrorMessage(res)));
		PQclear(res);
		return(0);
	} else {
		nrows = PQntuples(res);
		PQclear(res);
		return(nrows);
	}
}
//---------------------------------------------------------------------
bool Cpgdatabase::CheckAccessibility(std::string tablename)
{
	// build SQL query
	std::string sql = "SELECT * FROM ";
	sql += tablename;
	
	try
	{			
		// Check that we can read from the table (i.e., we have
		// select permission).
		sql += " limit 1";
		PGresult* testAccess = PQexec(conn, sql.c_str());
		if (PQresultStatus(testAccess) != PGRES_TUPLES_OK)
		{
			MSG_ADD(wxString::Format("%s",sql.c_str()));
			MSG_ADD("Unable to access relation");
			MSG_ADD(wxString::Format("%s", PQresultErrorMessage(testAccess)));
			PQclear(testAccess);
			return(false);
		} else {
			PQclear(testAccess);
			return(true);
		}
	}
	catch(...)
	{
		return(false);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::ReadTable(Csqltable *SQLTable)
{
	PGresult* res;
	int ncols = 0;
	int nrows = 0;
	int intval = 0;

	// build SQL query
	string SQL_Query = "SELECT * FROM ";
	SQL_Query += SQLTable->GetTableName();
	
	if ( CheckAccessibility(SQLTable->GetTableName()) ) {
		res = PQexec (conn, SQL_Query.c_str());
		if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
		{
			PQclear (res);
			//PQfinish (conn);
			return(false);
		} else {
			ncols = PQnfields(res);
			nrows = PQntuples(res);
			SQLTable->Set_ncols(ncols);
			SQLTable->Set_nrows(nrows);
			SQLTable->resize_fields(ncols, nrows);

			// assign values to incoming Csqltable 'table'
			// assign field names
			for (int i = 0; i < ncols; i++)
				SQLTable->SetFieldName(PQfname(res,i), i);

			// assign field types
			for (int i = 0; i < ncols; i++)
			{
				SQLTable->SetFieldType(GetFieldTypeString(res,i),i);
				SQLTable->SetFieldTypeInt(PQftype(res,i),i);
			}

			// assign field values to table matrix
			for (int col = 0; col < SQLTable->Get_ncols(); col++)
			{
				for (int row = 0; row < SQLTable->Get_nrows(); row++)
					SQLTable->SetValue(PQgetvalue(res,row,col),col,row);
			}
			PQclear(res);
			return(true);
		}
	} else {
		return(false);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::Get_QueryResult(std::string query, Csqltable* sqltable)
{
	PGresult* res;
	int ncols = 0;
	int nrows = 0;
	int intval = 0;

	if ( CheckAccessibility(sqltable->Get_TableName()) ) {
		res = PQexec (conn, query.c_str());
		if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
		{
			PQclear (res);
			return(false);
		} else {
			ncols = PQnfields(res);
			nrows = PQntuples(res);
			sqltable->Set_ncols(ncols);
			sqltable->Set_nrows(nrows);
			sqltable->resize_fields(ncols, nrows);

			// assign values to incoming Csqltable 'table'
			// assign field names
			for (int i = 0; i < ncols; i++)
				sqltable->SetFieldName(PQfname(res,i), i);

			// assign field types
			for (int i = 0; i < ncols; i++)
			{
				sqltable->SetFieldType(GetFieldTypeString(res,i),i);
				sqltable->SetFieldTypeInt(PQftype(res,i),i);
			}

			// assign field values to table matrix
			for (int col = 0; col < sqltable->Get_ncols(); col++)
			{
				for (int row = 0; row < sqltable->Get_nrows(); row++)
					sqltable->SetValue(PQgetvalue(res,row,col),col,row);
			}
			PQclear(res);
			return(true);
		}
	} else {
		return(false);
	}
}

//---------------------------------------------------------------------

bool Cpgdatabase::GetSelection(Csqltable *SQLTable, wxString sql)
{
	PGresult* res;
	int ncols = 0;
	int nrows = 0;
	int intval = 0;
	
	try
	{			
		// Check that we can read from the table (i.e., we have
		// select permission).
		wxString sql1 = sql;
		sql1.Append(" limit 1");
		PGresult* testAccess = PQexec(conn, (const char*)sql1.c_str());
		if (PQresultStatus(testAccess) != PGRES_TUPLES_OK)
		{
			MSG_ADD(wxString::Format("%s",sql1.c_str()));
			MSG_ADD("Unable to access relation");
			MSG_ADD(wxString::Format("%s", PQresultErrorMessage(testAccess)));
			PQclear(testAccess);
			return(false);
		} else {
			PQclear(testAccess);
			// send SELECT * FROM tablename query
			res = PQexec (conn, sql.c_str());
		}
	}
	catch(...)
	{
		PQclear(res);
		return(false);
	}
	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		//PQfinish (conn);
		return(false);
	} else {
		SQLTable->Set_ncols(PQnfields(res));
		SQLTable->Set_nrows(PQntuples(res));
		SQLTable->resize_fields(SQLTable->Get_ncols(), SQLTable->Get_nrows());

		// assign values to incoming Csqltable 'table'
		// assign field names
		for (int i = 0; i < ncols; i++)
			SQLTable->SetFieldName(PQfname(res,i), i);

		// assign field types
		for (int i = 0; i < ncols; i++)
		{
			SQLTable->SetFieldType(GetFieldTypeString(res,i),i);
			SQLTable->SetFieldTypeInt(PQftype(res,i),i);
		}

		// assign field values to table matrix
		for (int col = 0; col < SQLTable->Get_ncols(); col++)
		{
			for (int row = 0; row < SQLTable->Get_nrows(); row++)
				SQLTable->SetValue(PQgetvalue(res,row,col),col,row);
		}
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

/*
bool Cpgdatabase::ReadSpatialTable(CSimpleFeature *SimpleFeature)
{
	const int FIELDTYPE_GEOMETRY = 16416; //16632;
	int i, x, y;

	char SELECT[255] = { "SELECT * FROM "};
	strcat(SELECT, SimpleFeature->GetTableName().c_str());
	res = PQexec(conn, SELECT);
	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		// error
		PQclear(res);
	} else
	{
		// assign field types to fieldtypeInt[]
		for (x = 0; x < PQnfields(res); x++)
			SimpleFeature->SetFieldTypeInt(PQftype(res,x),i);
	}

	// build SQL query
	char SQL_Query[4096] = { "SELECT " };
	for (i = 0; i < SimpleFeature->Get_ncols(); i++)
	{
		// searching for field type "geometry" OID = 16632)
		if (SimpleFeature->GetFieldTypeInt(i) != FIELDTYPE_GEOMETRY)
		{
			SimpleFeature->SetFieldName(PQfname(res,i),i);
			strcat (SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat (SQL_Query, ", ");
		} else
		{
			//SimpleFeature->fieldname[i] = PQfname(res,i);
			SimpleFeature->SetFieldName(PQfname(res,i),i);
			strcat(SQL_Query, "AsText(");
			strcat(SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat(SQL_Query, ") AS ");
			strcat(SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat(SQL_Query, " FROM ");
			strcat(SQL_Query, SimpleFeature->GetTableName().c_str());
		}
	}

	// send SELECT * FROM tablename query
	res = PQexec (conn, SQL_Query);

	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		return(false);
	} else
	{
		// Assign table values to SQLTable
		for (x = 0; x < PQnfields(res); x++)
		{
			SimpleFeature->SetValue(PQgetvalue(res,0,x),x,0);
			MSG_ADD(SimpleFeature->GetValue(x,0).c_str());
		}
		PQclear (res);
	}

	return(true);
}
*/

//---------------------------------------------------------------------

string Cpgdatabase::GetDBTables()
{
	// this function returns a string containing all user table names
	// in the current database (default system tables not included)
	PGresult* res;
	string str; // storing table names for Add_Choice
	
	// sending SQL query to database
	res = PQexec(conn, sql_getTables.c_str());
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return("");
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			str.append(PQgetvalue(res,y,0));
			str.append("|");
		}
		PQclear(res);
		return(str);
	}
}

//---------------------------------------------------------------------

wxArrayString Cpgdatabase::GetDBTablesArray()
{
	// this function returns a string containing all user table names
	// in the current database (default system tables not included)
	PGresult* res;
	wxArrayString str; // storing table names for Add.Choice
	
	// sending SQL query to database
	res = PQexec(conn, sql_getTables.c_str());
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return(str.Add(""));
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			str.Add(PQgetvalue(res,y,0));
			//str.append("|");
		}
		PQclear(res);
		return(str);
	}
}
//---------------------------------------------------------------------

string Cpgdatabase::GetDBGeometryTables()
{
	string str; // storing table names for Add.Choice
	PGresult* res;
	
	// sending SQL query to database
	res = PQexec(conn, sql_getGeometryTables.c_str());
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return("");
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			str.append(PQgetvalue(res,y,2));
			str.append(" (");
			str.append(PQgetvalue(res,y,3));
			str.append(")");
			str.append("|");
		}
		PQclear(res);
		return(str);
	}
}

//---------------------------------------------------------------------

wxArrayString Cpgdatabase::GetDBGeometryTablesArray()
{
	wxArrayString str; // storing table names for Add.Choice
	std::string stemp;
	PGresult* res;
	
	// sending SQL query to database
	res = PQexec(conn, sql_getGeometryTables.c_str());
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return(str.Add(""));
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			stemp = PQgetvalue(res,y,2);
			stemp += " (";
			stemp += PQgetvalue(res,y,3);
			stemp += ")";
			str.Add(stemp.c_str());
			stemp = "";
			/*str.add(PQgetvalue(res,y,2));
			str.add(" (");
			str.add(PQgetvalue(res,y,3));
			str.add(")");*/
		}
		PQclear(res);
		return(str);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::CreateTable(Csqltable *SQLTable)
{
	// creating the SQL command
	string SQL_Query = "CREATE TABLE ";
	SQL_Query += SQLTable->GetTableName();
	SQL_Query += " (";

	for (int i = 0; i < SQLTable->Get_ncols(); i++)
	{
		SQL_Query += SQLTable->GetFieldName(i); //fname[i].c_str());
		SQL_Query += " ";
		SQL_Query += SQLTable->GetFieldType(i); //ftype[i].c_str());
		if (i <  SQLTable->Get_ncols() -1) SQL_Query += ", ";
		if (i == SQLTable->Get_ncols() -1) SQL_Query += ")";
	}
	
	//MSG_ADD(SQL_Query);

	if (ExecSQL(SQL_Query.c_str()))
	{
		//cout << "\n Create Table successfull..." << endl;
		return(true);
	} else{
		//cout << "\n Create Table not successfull..." << endl;
		return(false);
	}
}

//---------------------------------------------------------------------

bool Cpgdatabase::DropTable(const char *tablename)
{
	try
	{
		// creating the SQL command
		string SQL_Query = "DROP TABLE ";
		SQL_Query += tablename;
		if (!ExecSQL(SQL_Query.c_str())) return(false);
	}
	catch(...)
	{
		return(false);
	}
	return(true);
}
//---------------------------------------------------------------------

bool Cpgdatabase::InsertIntoTable(const char *tablename, const char *record)
{
	try
	{
		// creating the SQL command
		string SQL_Query = "INSERT INTO ";
		SQL_Query += tablename;
		SQL_Query += " VALUES(";
		SQL_Query += record;
		SQL_Query += ")";
		if (!ExecSQL(SQL_Query.c_str())) return(false);
	}
	catch(...)
	{
		return(false);
	}
	return(true);
}
//---------------------------------------------------------------------

string Cpgdatabase::Get_SQL_InsertInto(const char *tablename, const char *record)
{
	string SQL_Query;

	// creating the SQL command
	SQL_Query = "INSERT INTO ";
	SQL_Query += tablename;
	SQL_Query += " VALUES(";
	SQL_Query += record;
	SQL_Query += ")";
	
	return(SQL_Query);
}
//---------------------------------------------------------------------

std::string Cpgdatabase::Get_PrimaryKeyColumnName(const char* tablename)
{
	int pk_col_number = _GetPrimaryKeyColumnNumber(tablename);
	return( _GetPrimaryKeyColumnName(tablename,pk_col_number) );
}
//---------------------------------------------------------------------

int Cpgdatabase::_GetPrimaryKeyColumnNumber(string FeatureName)
{
	int pk_col_number = 0;
	string	sql;
	sql =  "select conkey from pg_class r, pg_constraint c ";
	sql += "where r.oid = c.conrelid and contype = 'p'";
	sql += "and relname = '";
	sql += FeatureName;
	sql += "'";

	PGresult *res;
	res = PQexec(conn, (const char*)sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		SG_UI_Msg_Add("Error: _GetPrimaryKeyColumnNumber()",true);
		SG_UI_Msg_Add(PQresultErrorMessage(res),true);
		PQclear(res);
		return(-2);
	} else {
		pk_col_number = atoi(PQgetvalue(res,0,0));
		PQclear(res);
		return(pk_col_number);
	}
}
//---------------------------------------------------------------------

std::string Cpgdatabase::_GetPrimaryKeyColumnName(string FeatureName, int pk_col_number)
{
	string pk_col_name;
	string sql = "SELECT * FROM ";
	sql +=  FeatureName;
	sql += " LIMIT 1";
	PGresult *res;

	res = PQexec(conn, (const char*)sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		SG_UI_Msg_Add("Error: _GetPrimaryKeyColumnName()",true);
		SG_UI_Msg_Add(PQresultErrorMessage(res),true);
		PQclear(res);
		return("");
	} else {
		pk_col_name = PQfname(res,pk_col_number);
		PQclear(res);
		return(pk_col_name);
	}
}
//---------------------------------------------------------------------

std::string	Cpgdatabase::_Get_GeometryColumnName(std::string FeatureName)
{
	std::string GeometryColumnName;
	std::ostringstream sql;

	sql << "SELECT f_geometry_column FROM geometry_columns";
	sql << " WHERE f_table_name = '"<< FeatureName << "'";

	PGresult* res = PQexec(conn, sql.str().c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		SG_UI_Msg_Add("Error in function: _Get_GeometryColumnName()",true);
		SG_UI_Msg_Add(PQresultErrorMessage(res),true);
		PQclear(res);
		return("");
	} else {
		GeometryColumnName = PQgetvalue(res,0,0);
		PQclear(res);
		return(GeometryColumnName);
	}
}
//---------------------------------------------------------------------

std::string Cpgdatabase::Cut_FileNameExtension(const char* filename)
{
	std::string str = filename;
	return( str.substr(0,str.find(".")) );
}

//---------------------------------------------------------------------
int Cpgdatabase::get_lastval(CSG_String sequencename)
{
	int lastval;

	wxString sql = "SELECT last_value FROM ";
	sql.Append(sequencename);

	PGresult *res;

	res = PQexec(conn, sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		PQclear(res);
		return(-999);
	} else {
		lastval = convert_sl::StringToInt( PQgetvalue(res,0,0) );
		PQclear(res);
		return(lastval);
	}
}
//---------------------------------------------------------------------
int Cpgdatabase::_Get_last_serial(CSG_String tablename, CSG_String pk_colname)
{
	//SELECT currval(pg_get_serial_sequence('users', 'id'));")
	int lastval;

	wxString sql = "SELECT currval(pg_get_serial_sequence('";
	sql.Append(tablename);
	sql.Append("','");
	sql.Append(pk_colname);
	sql.Append("'))");

	PGresult *res;

	res = PQexec(conn, sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		PQclear(res);
		return(-999);
	} else {
		lastval = convert_sl::StringToInt( PQgetvalue(res,0,0) );
		PQclear(res);
		return(lastval);
	}
}
//---------------------------------------------------------------------


//---------------------------------------------------------------------
//				GET FUNCTIONS
//---------------------------------------------------------------------

char *Cpgdatabase::GetDBName()
{
	return(PQdb(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetUser()
{
	return(PQuser(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetPW()
{
	return(PQpass(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetHost()
{
	if (PQhost(conn) !=NULL)
	{
		return(PQhost(conn));
	} else {
		return("Hostname returned: NULL");
	}
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetPort()
{
	return(PQport(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetTTY()
{
	return(PQtty(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetOptions()
{
	return(PQoptions(conn));
}
//---------------------------------------------------------------------

PGconn *Cpgdatabase::GetConn()
{
	if (b_conn)
		return this->conn;
	else
		return 0;
}
//---------------------------------------------------------------------

//---------------------------------------------------------------------

///////////////////////////////////////////////////////////////////////
//
//						PRIVATE MEMBER FUNCTIONS
//
///////////////////////////////////////////////////////////////////////

//---------------------------------------------------------------------

bool Cpgdatabase::ExecSQL(const char *SQL_Query)
{
	// executes any SQL query in the currently connected database
	// using the global connection variable 'conn'
	PGresult *res;
	try
	{
		res = PQexec(conn, SQL_Query);
	}
	catch(...)
	{
		return(false);
	}
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::ExecSQL_str(string SQL_Query)
{
	// executes any SQL query in the currently connected database
	// using the global connection variable 'conn'
	PGresult *res;
	res = PQexec(conn, SQL_Query.c_str());
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::StartTransaction()
{
	// Start transaction block
	PGresult* res;
	res = PQexec(conn, "BEGIN");
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		PQfinish(conn);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

void Cpgdatabase::CommitTransaction()
{
	// COMMIT Transaction
	PGresult* res;
	res = PQexec(conn, "COMMIT");
	PQclear(res);
}

//---------------------------------------------------------------------

void Cpgdatabase::CloseCursor()
{
	PGresult* res;
	// close cursor (FetchRows(...))
	res = PQexec(conn, "CLOSE mycursor");
	PQclear(res);
}

string Cpgdatabase::GetFieldTypeString(PGresult *res, int i)
{
	// You find the Oid number in the PostgreSQL installation directory:
	// src/include/catalog/pg_type
	int Oid;
	Oid = PQftype(res,i);
	switch(Oid)
	{
	case 16 : return("BOOL");
		break;
	case 25 : return("TEXT");
		break;
	case 20 : return("INT8");
		break;
	case 23 : return("INT4");
		break;
	case 700 : return("FLOAT4");
		break;
	case 701 : return("FLOAT8");
		break;
	case 702 : return("ABSTIME");
		break;
	case 703 : return("RELTIME");
		break;
	case 704 : return("TINTERVAL");
		break;
	case 705 : return("UNKNOWN");
		break;
	case 1043 : return("VARCHAR");
		break;
	case 1082 : return("DATE");
		break;
	case 1083 : return("TIME");
		break;
	case 2275 : return("CSTRING");
		break;
	default: return("undefined");
	}
}

TSG_Table_Field_Type Cpgdatabase::SQLFieldTypeToSAGAFieldtype(int id)
{
	// You find the Oid number in the PostgreSQL installation directory:
	// src/include/catalog/pg_type
	//int Oid;
	//Oid = PQftype(res,id);
	switch(id)
	{
	case 16 : // BOOL
		return(TABLE_FIELDTYPE_Char);	break;
	case 25 : // TEXT
		return(TABLE_FIELDTYPE_String); break;
	case 20 : // INT8
		return(TABLE_FIELDTYPE_Int); break;
	case 23 : // INT4
		return(TABLE_FIELDTYPE_Int); break;
	case 700 : // FLOAT4
		return(TABLE_FIELDTYPE_Float); break;
	case 701 : // FLOAT8
		return(TABLE_FIELDTYPE_Double); break;
	case 702 : // ABSTIME
		return(TABLE_FIELDTYPE_None);	break;
	case 703 : // RELTIME
		return(TABLE_FIELDTYPE_None); break;
	case 704 : // TINTERVAL
		return(TABLE_FIELDTYPE_None); break;
	case 705 : // UNKNOWN
		return(TABLE_FIELDTYPE_None); break;
	case 1043 : // VARCHAR
		return(TABLE_FIELDTYPE_String); break;
	case 1082 : // DATE
		return(TABLE_FIELDTYPE_String); break;
	case 1083 : // TIME
		return(TABLE_FIELDTYPE_String); break;
	case 2275 : // CSTRING
		return(TABLE_FIELDTYPE_String); break;
	default: return(TABLE_FIELDTYPE_None);
	}
}

///////////////////////////////////////////////////////////
//
//	Class CSQL_Record
//
///////////////////////////////////////////////////////////
/*
CSQL_Record::CSQL_Record()
{};
//---------------------------------------------------------------------

CSQL_Record::CSQL_Record(wxArrayString FieldType, wxArrayString Value)
{
	m_FieldType = FieldType;
	m_Value = Value;
	m_pIsNumeric = new bool[WXSIZEOF(FieldType)];
	_AssignIsNumeric();
}
//---------------------------------------------------------------------

CSQL_Record::~CSQL_Record()
{
	delete[] m_pIsNumeric;
}
//---------------------------------------------------------------------

void CSQL_Record::_AssignIsNumeric()
{
	for (int i = 0; i < WXSIZEOF(m_FieldType); i++)
	{
		if (m_FieldType[i] == "INT4"	||
			m_FieldType[i] == "INT8"	||
			m_FieldType[i] == "FLOAT4"	||
			m_FieldType[i] == "FLOAT8"	  )
		{
			m_pIsNumeric[i] = true;
		} else {
			m_pIsNumeric[i] = false;
		}
	}
}
//---------------------------------------------------------------------

wxString CSQL_Record::GetFieldType(int i)
{
	if (i < WXSIZEOF(m_FieldType))
		return(m_FieldType[i]);
	return("INVALID FIELD TYPE");
}
//---------------------------------------------------------------------

wxString CSQL_Record::GetValue(int i)
{
	if (i < WXSIZEOF(m_Value))
		return(m_Value[i]);
	return("-9999");
}
//---------------------------------------------------------------------

bool CSQL_Record::IsNumeric(int i)
{
	if (i < WXSIZEOF(m_FieldType))
		return(m_pIsNumeric[i]);
	return(false);
}
*/