/* pgsql_lib.c copyright (c) 2004 William R. Pearson */

/* $Name: fa_34_26_5 $ - $Id: pgsql_lib.c,v 1.3 2006/04/12 18:00:02 wrp Exp $ */

/* functions for opening, reading, seeking a pgsql database */

/*
  For the moment, this interface assumes that the file to be searched will
  be specified in a single, long, string with 4 parts:

  (1) a database open string. This string has four fields, separated by
      whitespace (' \t'):
        hostname:port dbname user password

   '--' dashes at the beginning of lines are ignored -
   thus the first line could be:
   -- hostname:port dbname user password

  (2) a database query string that will return an unique ID (not
      necessarily numberic, but it must be < 12 characters as libstr[12]
      is used) and a sequence string

  (2a) a series of pgsql commands that do not generate results
       starting with 'DO', followed by a select() statement.

  (3) a database select string that will return a description
      given a unique ID

  (4) a database select string that well return a sequence given a
      unique ID

   Lines (3) and (4) are not required for pv34comp* libraries, but
   line (2) must generate a complete description as well as a sequence.


   18-July-2001
   Additional syntax has been added to support multiline SQL queries.

   If the host line begins with '+', then the SQL is openned on the same
   connection as the previous SQL file.

   If the host line contains '-' just before the terminal ';', then
   the file will not produce any output.

   This string can contain "\n". ";" are used to separate the four
   functions, which must be specified in the order shown above.
   The last (fourth) query must terminate with a ';'

   19-July-2004

   This file is designed for PostgreSQL, which uses a different syntax
   for getting rows of data.  Specifically, a select statement must be
   associated with a "cursor", so that one can fetch a single row.

   This can be simply done with the statment:

   DECLARE next_seq CURSOR FOR "select statement ..."

   The need for a CURSOR complicates the getlib()/ranlib() design, which
   assumes that ranlib() can set something up that getlib() can read.
   This can be avoided by setting up an otherwise unnecessary cursor for
   the ranlib statement that gets a sequence.

*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#include <libpq-fe.h>
#define PGSQL_LIB 17

#include "defs.h"
#include "mm_file.h"

#define XTERNAL
#include "uascii.h"
#define EOSEQ 0
/* #include "upam.h" */

#ifdef SUPERFAMNUM
int sfnum[10], nsfnum;
#endif

int pgsql_getlib(unsigned char *, int, char *, int, fseek_t *, int *, struct lmf_str *, long *);
void pgsql_ranlib(char *, int, fseek_t, char *, struct lmf_str *m_fd);

#define PGSQL_BUF 4096

struct lmf_str *
pgsql_openlib(char *sname, int ldnaseq, int *sascii) {
  FILE *sql_file;
  PGconn *conn;
  PGresult *res;
  char *tmp_str, *ttmp_str;
  int tmp_str_len;
  char *bp, *bps, *bdp, *tp, tchar;
  int i, qs_len, qqs_len;
  char *sql_db, *sql_host, *sql_dbname, *sql_user, *sql_pass;
  char *sql_port;
  char *sql_do;
  int sql_do_cnt;
  struct lmf_str *m_fptr;

  /*  if (sql_reopen) return NULL; - should not be called for re-open */

  tmp_str_len = PGSQL_BUF;
  if ((tmp_str=(char *)calloc(tmp_str_len,sizeof(char)))==NULL) {
    fprintf(stderr,"cannot allocate %d for pgSQL buffer\n",tmp_str_len);
    return NULL;
  }

  if (sname[0] == '%') {
    strncpy(tmp_str,sname+1,tmp_str_len);
    tmp_str[sizeof(tmp_str)-1]='\0';
  }
  else {
    if ((sql_file=fopen(sname,"r"))==NULL) {
      fprintf(stderr," cannot open pgSQL file: %s\n",sname);
      return NULL;
    }

    if ((qs_len=fread(tmp_str,sizeof(char),tmp_str_len-1,sql_file))<=0) {
      fprintf(stderr," cannot read pgSQL file: %s\n",sname);
      return NULL;
    }
    else  {
      tmp_str[qs_len]='\0';
      qqs_len = qs_len;
      while (qqs_len >= tmp_str_len-1) {
	tmp_str_len += PGSQL_BUF;
	if ((tmp_str=(char *)realloc(tmp_str,tmp_str_len))==NULL) {
	  fprintf(stderr,
		  " cannot reallocate %d for pgSQL buffer\n",tmp_str_len);
	  return NULL;
	}
	ttmp_str = &tmp_str[qqs_len];
	if ((qs_len=fread(ttmp_str,sizeof(char),PGSQL_BUF,sql_file))<0) {
	  fprintf(stderr," cannot read pgSQL file: %s\n",sname);
	  return NULL;
	}
	ttmp_str[qs_len]='\0';
	qqs_len += qs_len;
      }
    }
    fclose(sql_file);
  }

  bps = tmp_str;
  if ((bp=strchr(bps,';'))!=NULL) {
    *bp='\0';
    if ((sql_db=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
      fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
	      strlen(bps),bps);
      return NULL;
    }
    /* have database name, parse the fields */
    else {
      strcpy(sql_db,bps);	/* strcpy OK because allocated strlen(bps) */
      bps = bp+1;	/* points to next char after ';' */
      while (isspace(*bps)) bps++;
      *bp=';'; /* replace ; */
      bp = sql_db;
      while (*bp=='-') {*bp++ = ' ';}
      sql_host = strtok(bp," \t\n");
      if (sql_host[0]=='@') sql_host="";
      sql_dbname = strtok(NULL," \t\n");
      sql_user = strtok(NULL," \t\n");
      if (sql_user[0]=='@') sql_user="";
      sql_pass = strtok(NULL," \t\n");
      if (sql_pass[0]=='@') sql_pass="";
      if ((tp=strchr(sql_host,':'))!=NULL) {
	sql_port = tp+1;
	*tp='\0';
      }
      else sql_port = "";
    }
  }
  else {
    fprintf(stderr," cannot find database fields:\n%s\n",tmp_str);
    return NULL;
  }

  /* we have all the info we need to open a database, allocate lmf_str */
  if ((m_fptr = (struct lmf_str *)calloc(1,sizeof(struct lmf_str)))==NULL) {
    fprintf(stderr," cannot allocate lmf_str (%ld) for %s\n",
	    sizeof(struct lmf_str),sname);
    return NULL;
  }

  /* have our struct, initialize it */

  strncpy(m_fptr->lb_name,sname,MAX_FN);
  m_fptr->lb_name[MAX_FN-1]='\0';

  m_fptr->sascii = sascii;

  m_fptr->sql_db = sql_db;
  m_fptr->getlib = pgsql_getlib;
  m_fptr->ranlib = pgsql_ranlib;
  m_fptr->mm_flg = 0;
  m_fptr->sql_reopen = 0;
  m_fptr->lb_type = PGSQL_LIB;

  /* now open the database, if necessary */
  conn = PQsetdbLogin(sql_host,
		      sql_port,
		      NULL,
		      NULL,
		      sql_dbname,
		      sql_user,
		      sql_pass);

  if (PQstatus(conn) != CONNECTION_OK)     {
    fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
    fprintf(stderr, "%s", PQerrorMessage(conn));
    PQfinish(conn);
    goto error_r;
  }
  else {
    m_fptr->pgsql_conn = conn;
    fprintf(stderr," Database %s opened on %s\n",sql_dbname,sql_host);
  }

  /* check for 'DO' command - copy to 'DO' string */
  while (*bps == '-') { *bps++=' ';}
  if (isspace(bps[-1]) && toupper(bps[0])=='D' &&
      toupper(bps[1])=='O' && isspace(bps[2])) {
    /* have some 'DO' commands */
    /* check where the end of the last DO statement is */

    sql_do_cnt = 1;	/* count up the number of 'DO' statements for later */
    bdp=bps+3;
    while ((bp=strchr(bdp,';'))!=NULL) {
      tp = bp+2; /* skip ;\n */
      while (isspace(*tp) || *tp == '-') {*tp++ = ' ';}
      if (toupper(*tp)=='D' && toupper(tp[1])=='O' && isspace(tp[2])) {
	sql_do_cnt++;		/* count the DO statements */
	bdp = tp+3;		/* move to the next DO statement */
      }
      else break;
    }
    if (bp != NULL) {	/* end of the last DO, begin of select */
      tchar = *(bp+1);
      *(bp+1)='\0';		/* terminate DO strings */
      if ((sql_do = calloc(strlen(bps)+1, sizeof(char)))==NULL) {
	fprintf(stderr," cannot allocate %d for sql_do\n",strlen(bps));
	goto error_r;
      }
      else {
	strcpy(sql_do,bps);
	*(bp+1)=tchar;	/* replace missing ';' */
      }
      bps = bp+1;
      while (isspace(*bps)) bps++;
    }
    else {
      fprintf(stderr," terminal ';' not found: %s\n",bps);
      goto error_r;
    }
    /* all the DO commands are in m_fptr->sql_do in the form: 
     DO command1; DO command2; DO command3; */
    bdp = sql_do;
    while (sql_do_cnt-- && (bp=strchr(bdp,';'))!=NULL) {
      /* do the pgsql statement on bdp+3 */
      /* check for error */
      *bp='\0';
      res = PQexec(m_fptr->pgsql_conn,bdp+3);
      if (PQresultStatus(res) != PGRES_COMMAND_OK) {
	fprintf(stderr,"*** Error %s - query failed:\n%s\n",
		PQerrorMessage(m_fptr->pgsql_conn), bdp+3);
	PQclear(res);
	goto error_r;
      }
      PQclear(res);

      *bp=';';
      bdp = bp+1;
      while (isspace(*bdp)) bdp++;
    }
  }

  /* copy 1st query field */
  if ((bp=strchr(bps,';'))!=NULL) {
    *bp='\0';
    if ((m_fptr->sql_query=calloc(strlen(bps)+41,sizeof(char)))==NULL) {
      fprintf(stderr, " cannot allocate space for query string [%d], %s\n",
	      strlen(bps),bps);
      goto error_r;
    }
    /* have query, copy it */
    else {
      strncpy(m_fptr->sql_query,"DECLARE next_seq CURSOR FOR ",40);
      strcat(m_fptr->sql_query,bps);
      *bp=';'; /* replace ; */
      bps = bp+1;
      while(isspace(*bps)) bps++;
    }
  }
  else {
    fprintf(stderr," cannot find database query field:\n%s\n",tmp_str);
    goto error_r;
  }

  /* copy get_desc field */
  if ((bp=strchr(bps,';'))!=NULL) {
    *bp='\0';
    if ((m_fptr->sql_getdesc=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
      fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
	      strlen(bps),bps);
      goto error_r;
    }
    /* have get_desc, copy it */
    else {
      strcpy(m_fptr->sql_getdesc,bps);
      *bp=';'; /* replace ; */
      bps = bp+1;
      while(isspace(*bps)) bps++;
    }
  }
  else {
    fprintf(stderr," cannot find getdesc field:\n%s\n",tmp_str);
    goto error_r;
  }

  if ((bp=strchr(bps,';'))!=NULL) { *bp='\0';}

  if ((m_fptr->sql_getseq=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
    fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
	    strlen(bps),bps);
    goto error_r;
  }

  if (strlen(bps) > 0) {
    strcpy(m_fptr->sql_getseq,bps);
  }
  else {
    fprintf(stderr," cannot find getseq field:\n%s\n",tmp_str);
    return NULL;
  }
  if (bp!=NULL) *bp=';';

  /* now do the fetch */

  res = PQexec(m_fptr->pgsql_conn,"BEGIN;");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr,"*** Error %s - BEGIN failed:\n",
	    PQerrorMessage(conn));
    PQclear(res);
    goto error_r;
  }
  PQclear(res);

  res = PQexec(m_fptr->pgsql_conn, m_fptr->sql_query);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr,"*** Error %d:%s - query failed:\n%s\n",
	    PQresultStatus(res),PQerrorMessage(conn), m_fptr->sql_query);
    PQclear(res);
    goto error_r;
  }
  PQclear(res);
  m_fptr->pgsql_res=NULL;

  return m_fptr;

 error_r:
  free(m_fptr->sql_getseq);
  free(m_fptr->sql_getdesc);
  free(m_fptr->sql_query);
  free(m_fptr);
  free(sql_db);
  return NULL;
}

struct lmf_str *
pgsql_reopen(struct lmf_str *m_fptr) {
  m_fptr->sql_reopen = 1;
  return m_fptr;
}

void
pgsql_closelib(struct lmf_str *m_fptr) {

  if (m_fptr == NULL) return;
  if (m_fptr->pgsql_res != NULL) PQclear(m_fptr->pgsql_res);
  PQfinish(m_fptr->pgsql_conn);
  m_fptr->sql_reopen=0;
}

/*
static char *sql_seq = NULL, *sql_seqp;
static int sql_seq_len;
*/

int
pgsql_getlib( unsigned char *seq,
	      int maxs,
	      char *libstr,
	      int n_libstr,
	      fseek_t *libpos,
	      int *lcont,
	      struct lmf_str *lm_fd,
	      long *l_off)
{
  register unsigned char *cp, *seqp;
  register int *ap;
  unsigned char *seqm, *seqm1;
  PGresult *res;

  char *bp;
  /*   int l_start, l_stop, len; */

  seqp = seq;
  seqm = &seq[maxs-9];
  seqm1 = seqm-1;

  ap = lm_fd->sascii;

#ifdef SUPERFAMNUM
  sfnum[0]=nsfnum = 0;
#endif

  if (*lcont==0) {
    /* get a row, with UID, sequence */
    *l_off = 1;

    /* check to see if we already have a valid result */
    if (lm_fd->pgsql_res==NULL) {
      res = PQexec(lm_fd->pgsql_conn,"FETCH next_seq");
      if (PQresultStatus(res) != PGRES_TUPLES_OK) {
	fprintf(stderr,"*** Error %s - getlib FETCH failed:\n%s\n",
		PQerrorMessage(lm_fd->pgsql_conn), lm_fd->sql_query);
	PQclear(res);
	lm_fd->pgsql_res = NULL;
	*lcont = 0;
	*seqp = EOSEQ;
	return -1;
      }
    }
    else {res = lm_fd->pgsql_res;}

    if (PQntuples(res)>0) {
      lm_fd->pgsql_res = res;
      *libpos=(fseek_t)atol(PQgetvalue(res,0,0));
	
      *l_off = 1;
      if (PQnfields(res) > 2 && (bp=strchr(PQgetvalue(res,0,2),'@'))!=NULL &&
	  !strncmp(bp+1,"C:",2)) sscanf(bp+3,"%ld",l_off);

      lm_fd->sql_seqp = PQgetvalue(res,0,1);
    
      /* because of changes in pgsql_ranlib(), it is essential that
         libstr return the unique identifier; thus we must use
         sql_row[0], not sql_row[2]. Using libstr as the UID allows
         one to use any UID, not just numeric ones.  *libpos is not
         used for pgsql libraries.
      */

      if (n_libstr <= MAX_UID) {
	/* the normal case returns only GID/sequence */
	strncpy(libstr,PQgetvalue(res,0,0),MAX_UID-1);
	libstr[MAX_UID-1]='\0';
      }
      else {
	/* here we do not use the UID in libstr, because we are not
           going back into the db */
	/* the PVM case also returns a long description */
	if (PQnfields(res)>2) {
	  strncpy(libstr,PQgetvalue(res,0,2),n_libstr-1);
	}
	else {
	  strncpy(libstr,PQgetvalue(res,0,0),n_libstr-1);
	}
	libstr[n_libstr-1]='\0';
      }
    }
    else {
      PQclear(lm_fd->pgsql_res);
      lm_fd->pgsql_res=NULL;
      *lcont = 0;
      *seqp = EOSEQ;
      return -1;
    }
  }

  for (cp=(unsigned char *)lm_fd->sql_seqp; seqp<seqm1 && *cp; ) {
    if ((*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA &&
	(*seqp++=ap[*cp++])<NA) continue;
    --seqp;
    if (*(cp-1)==0) break;
  }
  lm_fd->sql_seqp = (char *)cp;

  if (seqp>=seqm1) (*lcont)++;
  else {
    *lcont=0;
    PQclear(lm_fd->pgsql_res);
    lm_fd->pgsql_res = NULL;
  }

  *seqp = EOSEQ;
  /*   if ((int)(seqp-seq)==0) return 1; */
  return (int)(seqp-seq);
}

void
pgsql_ranlib(char *str,
	     int cnt,
	     fseek_t libpos,
	     char *libstr,
	     struct lmf_str *lm_fd
	     )
{
  char tmp_query[1024], tmp_val[20];
  PGresult *res;
  char *bp;

  str[0]='\0';

  /* put the UID into the query string - cannot use sprintf because of
     "%' etc */

  /*   sprintf(tmp_query,lm_fd->sql_getdesc,libpos); */

  if ((bp=strchr(lm_fd->sql_getdesc,'#'))==NULL) {
    fprintf(stderr, "no KEY position in %s\n",lm_fd->sql_getdesc);
    goto next1;
  }
  else {
    *bp = '\0';
    strncpy(tmp_query,lm_fd->sql_getdesc,sizeof(tmp_query));
    tmp_query[sizeof(tmp_query)-1]='\0';
    /*    sprintf(tmp_val,"%ld",(long)libpos); */
    strncat(tmp_query,libstr,sizeof(tmp_query)-1);
    strncat(tmp_query,bp+1,sizeof(tmp_query)-1);
    *bp='#';
    lm_fd->lpos = libpos;
  }

  /*  fprintf(stderr," requesting: %s\n",tmp_query); */

  if (lm_fd->pgsql_res !=NULL) {
    PQclear(lm_fd->pgsql_res);
    lm_fd->pgsql_res = NULL;
  }

  res = PQexec(lm_fd->pgsql_conn,tmp_query);
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    lm_fd->pgsql_res = NULL;

    sprintf(str,"gi|%ld ***Error - query failed***",(long)libpos);
    fprintf(stderr,"*** Error %s - ranlib DESC failed:\n%s\n",
	    PQerrorMessage(lm_fd->pgsql_conn), tmp_query);
    PQclear(res);
    goto next1;
  }

  if (PQntuples(res)<=0) {
/*     fprintf(stderr,"*** Error = use result failed\n%s\n", 
	   pgsql_error(lm_fd->pgsql_conn)); */
    sprintf(str,"gi|%ld ***use result failed***",(long)libpos);
    goto next0;
  }

  if (PQgetvalue(res,0,1)!= NULL) strncpy(str,PQgetvalue(res,0,1),cnt-1);
  else strncpy(str,PQgetvalue(res,0,0),cnt-1);
  str[cnt-1]='\0';
  /* change this later to support multiple row returns */
  /*
  while (strlen(str) < cnt-1 &&
	 (lm_fd->sql_row = pgsql_fetch_row(lm_fd->pgsql_res))!=NULL) {
    strncat(str," ",cnt-2-strlen(str));
    if (lm_fd->sql_row[1]!=NULL) 
      strncat(str,lm_fd->sql_row[1],cnt-2-strlen(str));
    else break;
  }
  */

  str[cnt-1]='\0';
  if ((bp = strchr(str,'\r'))!=NULL) *bp='\0';
  if ((bp = strchr(str,'\n'))!=NULL) *bp='\0';

 next0:
  PQclear(res);
 next1: 
  lm_fd->pgsql_res = NULL;

  /* get the sequence, set up for pgsql_getseq() */
  /* put the UID into the query string */

  if ((bp=strchr(lm_fd->sql_getseq,'#'))==NULL) {
    fprintf(stderr, "no GID position in %s\n",lm_fd->sql_getseq);
    return;
  }
  else {
    *bp = '\0';
    strncpy(tmp_query,lm_fd->sql_getseq,sizeof(tmp_query));
    tmp_query[sizeof(tmp_query)-1]='\0';
    /*    sprintf(tmp_val,"%ld",(long)libpos); */
    strncat(tmp_query,libstr,sizeof(tmp_query));
    strncat(tmp_query,bp+1,sizeof(tmp_query));
    *bp='#';
  }

  res = PQexec(lm_fd->pgsql_conn,tmp_query);
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    PQclear(res);
    lm_fd->pgsql_res = NULL;
    fprintf(stderr,"*** Error - ranlib SEQ failed:\n%s\n%s\n",tmp_query,
	    PQerrorMessage(lm_fd->pgsql_conn));
    exit(1);
  }
  else {
    lm_fd->pgsql_res = res;
  }
}