/*
 * gksql_pgsql.cxx
 *
 * PostgreSQL driver module for GnuGk
 *
 * Copyright (c) 2004, Michal Zygmuntowicz
 *
 * This work is published under the GNU Public License (GPL)
 * see file COPYING for details.
 * We also explicitely grant the right to link this code
 * with the OpenH323 library.
 *
 * $Log: gksql_pgsql.cxx,v $
 * Revision 1.11  2006/05/07 12:22:59  willamowius
 * fix comments
 *
 * Revision 1.10  2006/04/14 13:56:19  willamowius
 * call failover code merged
 *
 * Revision 1.1.1.1  2005/11/21 20:19:59  willamowius
 *
 *
 * Revision 1.4  2005/11/15 19:52:56  jan
 * Michal v1 (works, but on in routed, not proxy mode)
 *
 * Revision 1.9  2005/04/24 16:39:44  zvision
 * MSVC6.0 compatibility fixed
 *
 * Revision 1.8  2005/03/15 15:34:03  zvision
 * Compiler warning removed
 *
 * Revision 1.7  2005/01/16 15:22:35  zvision
 * Database Host parameter accepts only one host now
 *
 * Revision 1.6  2004/10/20 09:16:23  zvision
 * VC6 compilation errors fixed
 *
 * Revision 1.5.4.1  2004/10/20 09:10:12  zvision
 * VC6 compilation errors fixed
 *
 * Revision 1.5  2004/08/02 10:52:07  zvision
 * Ability to extract column names from a result set
 *
 * Revision 1.4  2004/07/09 22:11:36  zvision
 * SQLAcct module ported from 2.0 branch
 *
 */
#if HAS_PGSQL

#if defined(_WIN32) && (_MSC_VER <= 1200)
#pragma warning(disable:4786) // warning about too long debug symbol off
#pragma warning(disable:4284)
#endif

#include <ptlib.h>
#include <libpq-fe.h>
#include "gksql.h"

#ifdef _WIN32
#pragma comment( lib, "libpq.lib" )
#endif

/** Class that encapsulates SQL query result for PostgreSQL backend.
	It does not provide any multithread safety, so should be accessed
	from a single thread at time.
*/
class GkPgSQLResult : public GkSQLResult
{
public:
	/// Build the result from SELECT type query
	GkPgSQLResult(
		/// SELECT type query result
		PGresult* selectResult
		);

	/// Build the result from INSERT, DELETE or UPDATE query
	GkPgSQLResult(
		/// number of rows affected by the query
		long numRowsAffected
		);

	/// Build the empty	result and store query execution error information
	GkPgSQLResult(
		/// PostgreSQL specific error code
		unsigned int errorCode,
		/// PostgreSQL specific error message text
		const char* errorMsg
		);
	
	virtual ~GkPgSQLResult();
	
	/** @return
	    Backend specific error message, if the query failed.
	*/	
	virtual PString GetErrorMessage();
	
	/** @return
	    Backend specific error code, if the query failed.
	*/	
	virtual long GetErrorCode();
	
	/** @return
	    True if rows can be fetched in random access order, false if
	    rows have to be fethed sequentially and can be retrieved only once.
	*/
	virtual bool HasRandomAccess();

	/** Fetch a single row from the result set. After each row is fetched,
	    cursor position is moved to a next row.
		
	    @return
	    True if the row has been fetched, false if no more rows are available.
	*/
	virtual bool FetchRow(
		/// array to be filled with string representations of the row fields
		PStringArray& result
		);
	virtual bool FetchRow(
		/// array to be filled with string representations of the row fields
		ResultRow& result
		);

	/** @return
	    True if the column at the index #fieldOffset# is NULL in the row 
	    fetched most recently.
	*/
	virtual bool IsNullField(
		/// index of the column to check
		long fieldOffset
		);
			
	/** Fetch a single row from the result set. This function requires
		that the backend supports random row access.
		
	    @return
	    True if the row has been fetched, false if a row at the given offset
		does not exists or SQL backend does not support random row access.
	*/
	virtual bool FetchRow(
		/// array to be filled with string representations of the row fields
		PStringArray& result,
		/// index (0 based) of the row to fetch
		long rowOffset
		);
	virtual bool FetchRow(
		/// array to be filled with string representations of the row fields
		ResultRow& result,
		/// index (0 based) of the row to fetch
		long rowOffset
		);
		
	/** @return
	    True if the column at the index #fieldOffset# is NULL in the row 
	    at the specified index.
	*/
	virtual bool IsNullField(
		/// index of the column to check
		long fieldOffset,
		/// index (0 based) of the row to check
		long rowOffset
		);

private:
	GkPgSQLResult();
	GkPgSQLResult(const GkPgSQLResult&);
	GkPgSQLResult& operator=(const GkPgSQLResult&);
	
protected:
	/// query result for SELECT type queries, NULL otherwise
	PGresult* m_sqlResult;
	/// the most recent row returned by fetch operation
	int m_sqlRow;
	/// PgSQL specific error code (if the query failed)
	unsigned int m_errorCode;
	/// PgSQL specific error message text (if the query failed)
	PString m_errorMessage;
};

/// PostgreSQL backend connection implementation.
class GkPgSQLConnection : public GkSQLConnection
{
public:
	/// Build a new PgSQL connection object
	GkPgSQLConnection(
		/// name to use in the log
		const char* name = "PostgreSQL"
		);
	
	virtual ~GkPgSQLConnection();

protected:
	class PgSQLConnWrapper : public GkSQLConnection::SQLConnWrapper
	{
	public:
		PgSQLConnWrapper(
			/// unique identifier for this connection
			int id,
			/// host:port this connection is made to
			const PString& host,
			/// PostgreSQL connection object
			PGconn* conn
			) : SQLConnWrapper(id, host), m_conn(conn) {}

		virtual ~PgSQLConnWrapper();

	private:
		PgSQLConnWrapper();
		PgSQLConnWrapper(const PgSQLConnWrapper&);
		PgSQLConnWrapper& operator=(const PgSQLConnWrapper&);

	public:
		PGconn* m_conn;
	};

	/** Create a new SQL connection using parameters stored in this object.
	    When the connection is to be closed, the object is simply deleted
	    using delete operator.
	    
	    @return
	    NULL if database connection could not be established 
	    or an object of PgSQLConnWrapper class.
	*/
	virtual SQLConnPtr CreateNewConnection(
		/// unique identifier for this connection
		int id
		);
	
	/** Execute the query using specified SQL connection.

		@return
		Query execution result.
	*/
	virtual GkSQLResult* ExecuteQuery(
		/// SQL connection to use for query execution
		SQLConnPtr conn,
		/// query string
		const char* queryStr,
		/// maximum time (ms) for the query execution, -1 means infinite
		long timeout = -1
		);
		
	/** Escape any special characters in the string, so it can be used in a SQL query.

		@return
		Escaped string.
	*/
	virtual PString EscapeString(
		/// SQL connection to get escaping parameters from
		SQLConnPtr conn,
		/// string to be escaped
		const char* str
		);

private:
	GkPgSQLConnection(const GkPgSQLConnection&);
	GkPgSQLConnection& operator=(const GkPgSQLConnection&);
};


GkPgSQLResult::GkPgSQLResult(
	/// SELECT type query result
	PGresult* selectResult
	) 
	: GkSQLResult(false), m_sqlResult(selectResult), m_sqlRow(-1),
	m_errorCode(0)
{
	if (m_sqlResult) {
		m_numRows = PQntuples(m_sqlResult);
		m_numFields = PQnfields(m_sqlResult);
	} else
		m_queryError = true;
		
	m_selectType = true;
}

GkPgSQLResult::GkPgSQLResult(
	/// number of rows affected by the query
	long numRowsAffected
	) 
	: GkSQLResult(false), m_sqlResult(NULL), m_sqlRow(-1), 
	m_errorCode(0)
{
	m_numRows = numRowsAffected;
	m_selectType = false;
}
	
GkPgSQLResult::GkPgSQLResult(
	/// PostgreSQL specific error code
	unsigned int errorCode,
	/// PostgreSQL specific error message text
	const char* errorMsg
	) 
	: GkSQLResult(true), m_sqlResult(NULL), m_sqlRow(-1),
	m_errorCode(errorCode), m_errorMessage(errorMsg)
{
}

GkPgSQLResult::~GkPgSQLResult()
{
	if (m_sqlResult)
		PQclear(m_sqlResult);
}

bool GkPgSQLResult::HasRandomAccess()
{
	return true;
}

PString GkPgSQLResult::GetErrorMessage()
{
	return m_errorMessage;
}
	
long GkPgSQLResult::GetErrorCode()
{
	return m_errorCode;
}

bool GkPgSQLResult::FetchRow(
	/// array to be filled with string representations of the row fields
	PStringArray& result
	)
{
	if (m_sqlResult == NULL || m_numRows <= 0)
		return false;
	
	if (m_sqlRow < 0)
		m_sqlRow = 0;
		
	if (m_sqlRow >= m_numRows)
		return false;
		
	result.SetSize(m_numFields);
	
	for (PINDEX i = 0; i < m_numFields; i++)
		result[i] = PString(
			PQgetvalue(m_sqlResult, m_sqlRow, i), 
			PQgetlength(m_sqlResult, m_sqlRow, i)
			);
	
	m_sqlRow++;
	
	return true;
}

bool GkPgSQLResult::FetchRow(
	/// array to be filled with string representations of the row fields
	ResultRow& result
	)
{
	if (m_sqlResult == NULL || m_numRows <= 0)
		return false;
	
	if (m_sqlRow < 0)
		m_sqlRow = 0;
		
	if (m_sqlRow >= m_numRows)
		return false;
		
	result.resize(m_numFields);
	
	for (PINDEX i = 0; i < m_numFields; i++) {
		result[i].first = PString(
			PQgetvalue(m_sqlResult, m_sqlRow, i), 
			PQgetlength(m_sqlResult, m_sqlRow, i)
			);
		result[i].second = PQfname(m_sqlResult, i);
	}
	
	m_sqlRow++;
	
	return true;
}

bool GkPgSQLResult::IsNullField(
	/// index of the column to check
	long fieldOffset
	)
{
	return m_sqlResult == NULL || m_sqlRow < 0 || m_sqlRow >= m_numRows
		|| fieldOffset < 0 || fieldOffset >= m_numFields
		|| PQgetisnull(m_sqlResult, m_sqlRow, fieldOffset);
}

bool GkPgSQLResult::FetchRow(
	/// array to be filled with string representations of the row fields
	PStringArray& result,
	/// index (0 based) of the row to fetch
	long rowOffset
	)
{
	if (m_sqlResult == NULL || rowOffset < 0 || rowOffset >= m_numRows)
		return false;

	result.SetSize(m_numFields);
	
	for (PINDEX i = 0; i < m_numFields; i++)
		result[i] = PString(
			PQgetvalue(m_sqlResult, rowOffset, i), 
			PQgetlength(m_sqlResult, rowOffset, i)
			);
	
	return true;
}

bool GkPgSQLResult::FetchRow(
	/// array to be filled with string representations of the row fields
	ResultRow& result,
	/// index (0 based) of the row to fetch
	long rowOffset
	)
{
	if (m_sqlResult == NULL || rowOffset < 0 || rowOffset >= m_numRows)
		return false;

	result.resize(m_numFields);
	
	for (PINDEX i = 0; i < m_numFields; i++) {
		result[i].first = PString(
			PQgetvalue(m_sqlResult, rowOffset, i), 
			PQgetlength(m_sqlResult, rowOffset, i)
			);
		result[i].second = PQfname(m_sqlResult, i);
	}
	
	return true;
}

bool GkPgSQLResult::IsNullField(
	/// index of the column to check
	long fieldOffset,
	/// index (0 based) of the row to check
	long rowOffset
	)
{
	if (m_sqlResult == NULL || rowOffset < 0 || rowOffset >= m_numRows
		|| fieldOffset < 0 || fieldOffset >= m_numFields)
		return true;

	return PQgetisnull(m_sqlResult, rowOffset, fieldOffset) ? true : false;
}


GkPgSQLConnection::GkPgSQLConnection(
	/// name to use in the log
	const char* name
	) : GkSQLConnection(name)
{
}
	
GkPgSQLConnection::~GkPgSQLConnection()
{
}

GkPgSQLConnection::PgSQLConnWrapper::~PgSQLConnWrapper()
{
	PQfinish(m_conn);
}

GkSQLConnection::SQLConnPtr GkPgSQLConnection::CreateNewConnection(
	/// unique identifier for this connection
	int id
	)
{
	PGconn* conn;
	const PString portStr(m_port);
//	const PString optionsStr("connect_timeout=10000");
	if ((conn = PQsetdbLogin(m_host, 
			m_port ? (const char*)portStr : (const char*)NULL,
			NULL /*(const char*)optionsStr*/, NULL,
			m_database, m_username, 
			m_password.IsEmpty() ? (const char*)NULL : (const char*)m_password
			)) && PQstatus(conn) == CONNECTION_OK) {
		PTRACE(5, GetName() << "\tPgSQL connection to " << m_username << '@' << m_host 
			<< '[' << m_database << "] established successfully"
			);
		return new PgSQLConnWrapper(id, m_host, conn);
	} else {
		PTRACE(2, GetName() << "\tPgSQL connection to " << m_username << '@' << m_host 
			<< '[' << m_database << "] failed (PQsetdbLogin failed): " 
			<< (conn ? PQerrorMessage(conn) : "")
			);
		if (conn)
			PQfinish(conn);
	}
	return NULL;
}
	
GkSQLResult* GkPgSQLConnection::ExecuteQuery(
	/// SQL connection to use for query execution
	GkSQLConnection::SQLConnPtr conn,
	/// query string
	const char* queryStr,
	/// maximum time (ms) for the query execution, -1 means infinite
	long /*timeout*/
	)
{
	PGconn* pgsqlconn = ((PgSQLConnWrapper*)conn)->m_conn;
	PGresult* result = PQexec(pgsqlconn, queryStr);
	if (result == NULL)
		return new GkPgSQLResult(PGRES_FATAL_ERROR, PQerrorMessage(pgsqlconn));
		
	ExecStatusType resultInfo = PQresultStatus(result);
	switch (resultInfo)
	{
	case PGRES_COMMAND_OK:
		return new GkPgSQLResult(
			PQcmdTuples(result) ? atoi(PQcmdTuples(result)) : 0
			);
		
	case PGRES_TUPLES_OK:
		return new GkPgSQLResult(result);
		
	default:
		return new GkPgSQLResult(resultInfo, PQresultErrorMessage(result));
	}
}

PString GkPgSQLConnection::EscapeString(
	/// SQL connection to get escaping parameters from
	SQLConnPtr conn,
	/// string to be escaped
	const char* str
	)
{
	PString escapedStr;
	const unsigned long numChars = str ? strlen(str) : 0;
	
	if (numChars)
		escapedStr.SetSize(
			PQescapeString(escapedStr.GetPointer(numChars*2+1), str, numChars) + 1
			);
	return escapedStr;
}

namespace {
	GkSQLCreator<GkPgSQLConnection> PgSQLCreator("PostgreSQL");
}

#endif /* HAS_PGSQL */