================================================================================

/* database.h */
/* hide database */


/*--------*/
/* A.P.I. */
/*--------*/


int database_init( char * database_name ) ;
/* returns 0 on success */

int query_to_file( char * q_buff, FILE * file ) ;
/* returns 0 on success */

int query_to_stdout( char * q_buff ) ;
/* returns 0 on success */

int get_query_fields( char * q_buff, void * * buffer ) ;
/* returns 0 on success */
/* if * buffer is null, one will be allocated unless no rows are returned, and must be freed */
/* if rows are returned * buffer will be allocated or reallocated  must be in heap, not stack or static */
/* fills the * buffer with pointers to each field, followed by NULL , followed by data */
/* USAGE EXAMPLE rc = get_query_fields( scratch, (void*)&data ) ;  
	data is char * * data = NULL; 
	scratch point to the query
	first field is *data or data[0], the second is data[1], the third is data[2] etc.
*/

/* returns 0 with empty buffer ( *buffer == 0 or NULL buffer ) means zero rows */

================================================================================

/* database.c */
/* structures and getters and putters */

#include 
#include 
#include 
#include 

/*---------*/
/* STATICS */
/*---------*/

#define CONN_HERE
PGconn     *conn = NULL;
int nicely	= 0;

char * db_name;
int inited = 0;

/* local functions */
static int set_PGconn() ;

static void exit_nicely() {
	PQfinish(conn);
}

static int set_PGconn() {
	/* attempt connection */
	char scratch[ 100 ];
	sprintf( scratch ,  "dbname = %s", db_name );
	conn = PQconnectdb( scratch );

	/* Check to see that the backend connection was successfully made */
	if (PQstatus(conn) != CONNECTION_OK) {
		return -1;
	}
	if( ! nicely ) { atexit( exit_nicely ); nicely=1; }
	return 0;
}

int database_init( char * database_name ) {
	if( inited ) return -2;
	db_name = malloc( strlen( database_name ) + 1 );
	strcpy( db_name, database_name );
	return set_PGconn();
}

PGconn     * get_database() {
	return conn;
}

int query_to_file( char * q_buff, FILE * file ) {

	PGresult * my_result;   /* re use this pointer -- be sure to clear */
	int rc;
	int nf;
	int J;
	int K;
	int C;

	my_result = PQexec( conn, q_buff );
	rc = PQresultStatus( my_result );
	if( rc != PGRES_COMMAND_OK && rc != PGRES_TUPLES_OK ) {
		/* database error */
		fprintf( stderr, "Database Error %s (%s)", PQresultErrorMessage( my_result ), q_buff );
		PQclear( my_result );
		return -3;
	}

	if( rc != PGRES_TUPLES_OK ) {
		PQclear( my_result );
		return 0;
	}

	C = PQntuples( my_result );
	nf = PQnfields( my_result );
	/* print headers */
	for( K = 0; K < nf-1 ; K++ ) {
		fprintf( file, "%s|", PQfname( my_result, K ) );
	}
	fprintf( file, "%s\n", PQfname( my_result, nf-1 ) );
	/* print each row */
	for( J = 0; J < C ;  J++ ) {
		for( K = 0; K < nf-1 ; K++ ) {
			fprintf( file, "%s|", PQgetvalue( my_result, J, K ));
		}
		fprintf( file, "%s\n", PQgetvalue( my_result, J, nf-1 ));
	}

	fprintf( file,  "(%d rows)\n" , C );

	PQclear( my_result );
	return 0;
}

int query_to_stdout( char * q_buff ) {
	return query_to_file( q_buff, stdout );
}

int get_query_fields( char * q_buff, void * * inbuff ) {

	PGresult * my_result;   /* re use this pointer -- be sure to clear */
	int rc;
	int nf;
	int J;
	int K;
	int C;
	int sz;
	int pointer_size;
	int data_size;
	char * * field_ptr;
	char * data_ptr ;
	char * buff = *inbuff;

	my_result = PQexec( conn, q_buff );
	rc = PQresultStatus( my_result );
	if( rc != PGRES_COMMAND_OK && rc != PGRES_TUPLES_OK ) {
		/* database error */
		fprintf( stdout, "\nDatabase Error %s (%s)\n", PQresultErrorMessage( my_result ), q_buff );
		PQclear( my_result );
		if( buff ) *(char **)buff = NULL;
		return -3;
	}

	if( rc != PGRES_TUPLES_OK ) {
		if( strlen( PQresultErrorMessage( my_result ) ) > 3 ) {
			fprintf( stdout, "\nDatabase Error(line%d) %s (%s)\n", __LINE__, PQresultErrorMessage( my_result ), q_buff );
		}
		PQclear( my_result );
		if( buff ) *(char **)buff = NULL;
		return 0;
	}

	/* get the required size */
	C = PQntuples( my_result );
	nf = PQnfields( my_result );
	pointer_size = ( C * nf + 1 ) * sizeof( char * );

	for( K = 0; K < nf ; K++ ) {
		for( J = 0; J < C ;  J++ ) {
			data_size += strlen( PQgetvalue( my_result, J, K ) ) + 1;
		}
	}

	/* validate the buffer */
	sz = pointer_size + data_size ;
	if( ! buff ) {
		buff = malloc( sz );
	} else {
		buff = realloc( buff, sz );
	}
	* inbuff = buff;

	memset( buff, 0, sz );

	/* get the data */
	data_ptr = ( char * ) buff + pointer_size;
	field_ptr = (char * * ) buff;

	for( J = 0; J < C ;  J++ ) {
		for( K = 0; K < nf ; K++ ) {
			* field_ptr ++ = data_ptr;
			strcpy( data_ptr, PQgetvalue( my_result, J, K ) );
			data_ptr += strlen( PQgetvalue( my_result, J, K ) ) + 1;
		}
	}

	PQclear( my_result );
	return 0;
}

================================================================================