/* psql.c
 * MATLAB mex file which reads from the PostgreSQL database
 * 
 * Usage:  [a, b, c, ...] = psql(database_name_string, query_string);
 *
 * Example:  [monkey, arm, cell] = psql('db01', 'select distinct monkey, arm, cell from cell');
 *
 * Tony Reina
 * Motor Control Lab
 * The Neurosciences Institute
 * San Diego, CA
 *
 * Created: 1 Oct 1999 
 * Last Update: 1 Oct 1999 GAR
 * To compile use the CMEX compiler:
 *	cmex mat_psql.c $SQL -output psql.mexsg
 */
#include <strings.h>
#include "mex.h"
#include "libpq-fe.h"

void mexFunction(   int nlhs, 
		    mxArray *plhs[], 
		    int nrhs, 
		    const mxArray *prhs[] )
		    
{
  char *db_name, *query_string;
  char *buffer;

  char *output_string[8192];

  unsigned int buffer_length;

  int i, j, status, number_of_tuples, number_of_fields;

  /* SQL database variables */
  /* ---------------------- */
  char *pghost, *pgport, *pgoptions, *pgtty;
  PGconn *conn;
  PGresult *res;

  
  
  /* Verify that there are 11 variables passed into the function */
  /* rhs = right-hand side = input variables */
  if (nrhs != 2) {
    mexErrMsgTxt("ERROR! I need two input arguments: database name and query string.");
  }
  
  buffer_length = mxGetM(prhs[0]) * mxGetN(prhs[0]) + 1;
  db_name = mxCalloc(buffer_length, sizeof(char));

  status = mxGetString(prhs[0], db_name, buffer_length);
  if (status != 0)
    mexErrMsgTxt("Could not perform the database query because of input error!");

  buffer_length = mxGetM(prhs[1]) * mxGetN(prhs[1]) + 1;
  query_string = mxCalloc(buffer_length, sizeof(char));

  status = mxGetString(prhs[1], query_string, buffer_length);
  if (status != 0)
    mexErrMsgTxt("Could not perform the database query because of input error!");

  /* Initialize database variables */
  /* ============================= */
  pghost = NULL;		/* host name of the backend server */
  pgport = NULL;		/* port of the backend server */
  pgoptions = NULL;		/* special options to start up the backend server */
  pgtty = NULL;			/* debugging tty for the backend server */

  /* Open a connection to the database */
  /* ================================= */
  conn = PQsetdb (pghost, pgport, pgoptions, pgtty, db_name);

  if (PQstatus (conn) == CONNECTION_BAD)
    {
      printf ("Connection to database '%s' failed.\n", db_name);
      printf ("%s\n", PQerrorMessage (conn));
      PQfinish(conn);
      mexErrMsgTxt("Could not perform the database query because of connection error!");
    }				/* if (PQstatus(conn) == CONNECTION_BAD) */

  res = PQexec(conn, query_string);

  number_of_tuples = PQntuples(res);

  if (number_of_tuples == 0) {
    PQclear(res);
    PQfinish(conn);
    mexErrMsgTxt("Could not perform the database query because no data returned for query!");
  }

  number_of_fields = PQnfields(res);

  if (nlhs > number_of_fields) {
    printf("Too many output variables for the desired query.\n"
	   "I will only fill the first %d variables.\n", number_of_fields);
    nlhs = number_of_fields;
  }
  else if (nlhs < number_of_fields) {
     printf("Too few output variables for the desired query.\n"
	   "The last %d columns (fields) in the query will be dropped.\n", 
	    number_of_fields - nlhs);
  }
    

  /* This declares the memory space in MATLAB for the left-handed arguments:
   * coefficients, work, and error. These are the output variables.
   */
  for (i = 0; i < nlhs; i++) {
    
    for (j = 0; j < number_of_tuples; j++)
      output_string[j] = PQgetvalue(res, j, i);
    

    plhs[i] = mxCreateCharMatrixFromStrings(number_of_tuples, (const char **)output_string);

  }

  PQclear(res);
  PQfinish(conn);
      

}