2 /* pgsql_lib.c copyright (c) 2004 William R. Pearson */
4 /* $Name: fa_34_26_5 $ - $Id: pgsql_lib.c,v 1.3 2006/04/12 18:00:02 wrp Exp $ */
6 /* functions for opening, reading, seeking a pgsql database */
9 For the moment, this interface assumes that the file to be searched will
10 be specified in a single, long, string with 4 parts:
12 (1) a database open string. This string has four fields, separated by
14 hostname:port dbname user password
16 '--' dashes at the beginning of lines are ignored -
17 thus the first line could be:
18 -- hostname:port dbname user password
20 (2) a database query string that will return an unique ID (not
21 necessarily numberic, but it must be < 12 characters as libstr[12]
22 is used) and a sequence string
24 (2a) a series of pgsql commands that do not generate results
25 starting with 'DO', followed by a select() statement.
27 (3) a database select string that will return a description
30 (4) a database select string that well return a sequence given a
33 Lines (3) and (4) are not required for pv34comp* libraries, but
34 line (2) must generate a complete description as well as a sequence.
38 Additional syntax has been added to support multiline SQL queries.
40 If the host line begins with '+', then the SQL is openned on the same
41 connection as the previous SQL file.
43 If the host line contains '-' just before the terminal ';', then
44 the file will not produce any output.
46 This string can contain "\n". ";" are used to separate the four
47 functions, which must be specified in the order shown above.
48 The last (fourth) query must terminate with a ';'
52 This file is designed for PostgreSQL, which uses a different syntax
53 for getting rows of data. Specifically, a select statement must be
54 associated with a "cursor", so that one can fetch a single row.
56 This can be simply done with the statment:
58 DECLARE next_seq CURSOR FOR "select statement ..."
60 The need for a CURSOR complicates the getlib()/ranlib() design, which
61 assumes that ranlib() can set something up that getlib() can read.
62 This can be avoided by setting up an otherwise unnecessary cursor for
63 the ranlib statement that gets a sequence.
81 /* #include "upam.h" */
84 int sfnum[10], nsfnum;
87 int pgsql_getlib(unsigned char *, int, char *, int, fseek_t *, int *, struct lmf_str *, long *);
88 void pgsql_ranlib(char *, int, fseek_t, char *, struct lmf_str *m_fd);
90 #define PGSQL_BUF 4096
93 pgsql_openlib(char *sname, int ldnaseq, int *sascii) {
97 char *tmp_str, *ttmp_str;
99 char *bp, *bps, *bdp, *tp, tchar;
100 int i, qs_len, qqs_len;
101 char *sql_db, *sql_host, *sql_dbname, *sql_user, *sql_pass;
105 struct lmf_str *m_fptr;
107 /* if (sql_reopen) return NULL; - should not be called for re-open */
109 tmp_str_len = PGSQL_BUF;
110 if ((tmp_str=(char *)calloc(tmp_str_len,sizeof(char)))==NULL) {
111 fprintf(stderr,"cannot allocate %d for pgSQL buffer\n",tmp_str_len);
115 if (sname[0] == '%') {
116 strncpy(tmp_str,sname+1,tmp_str_len);
117 tmp_str[sizeof(tmp_str)-1]='\0';
120 if ((sql_file=fopen(sname,"r"))==NULL) {
121 fprintf(stderr," cannot open pgSQL file: %s\n",sname);
125 if ((qs_len=fread(tmp_str,sizeof(char),tmp_str_len-1,sql_file))<=0) {
126 fprintf(stderr," cannot read pgSQL file: %s\n",sname);
130 tmp_str[qs_len]='\0';
132 while (qqs_len >= tmp_str_len-1) {
133 tmp_str_len += PGSQL_BUF;
134 if ((tmp_str=(char *)realloc(tmp_str,tmp_str_len))==NULL) {
136 " cannot reallocate %d for pgSQL buffer\n",tmp_str_len);
139 ttmp_str = &tmp_str[qqs_len];
140 if ((qs_len=fread(ttmp_str,sizeof(char),PGSQL_BUF,sql_file))<0) {
141 fprintf(stderr," cannot read pgSQL file: %s\n",sname);
144 ttmp_str[qs_len]='\0';
152 if ((bp=strchr(bps,';'))!=NULL) {
154 if ((sql_db=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
155 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
159 /* have database name, parse the fields */
161 strcpy(sql_db,bps); /* strcpy OK because allocated strlen(bps) */
162 bps = bp+1; /* points to next char after ';' */
163 while (isspace(*bps)) bps++;
164 *bp=';'; /* replace ; */
166 while (*bp=='-') {*bp++ = ' ';}
167 sql_host = strtok(bp," \t\n");
168 if (sql_host[0]=='@') sql_host="";
169 sql_dbname = strtok(NULL," \t\n");
170 sql_user = strtok(NULL," \t\n");
171 if (sql_user[0]=='@') sql_user="";
172 sql_pass = strtok(NULL," \t\n");
173 if (sql_pass[0]=='@') sql_pass="";
174 if ((tp=strchr(sql_host,':'))!=NULL) {
182 fprintf(stderr," cannot find database fields:\n%s\n",tmp_str);
186 /* we have all the info we need to open a database, allocate lmf_str */
187 if ((m_fptr = (struct lmf_str *)calloc(1,sizeof(struct lmf_str)))==NULL) {
188 fprintf(stderr," cannot allocate lmf_str (%ld) for %s\n",
189 sizeof(struct lmf_str),sname);
193 /* have our struct, initialize it */
195 strncpy(m_fptr->lb_name,sname,MAX_FN);
196 m_fptr->lb_name[MAX_FN-1]='\0';
198 m_fptr->sascii = sascii;
200 m_fptr->sql_db = sql_db;
201 m_fptr->getlib = pgsql_getlib;
202 m_fptr->ranlib = pgsql_ranlib;
204 m_fptr->sql_reopen = 0;
205 m_fptr->lb_type = PGSQL_LIB;
207 /* now open the database, if necessary */
208 conn = PQsetdbLogin(sql_host,
216 if (PQstatus(conn) != CONNECTION_OK) {
217 fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
218 fprintf(stderr, "%s", PQerrorMessage(conn));
223 m_fptr->pgsql_conn = conn;
224 fprintf(stderr," Database %s opened on %s\n",sql_dbname,sql_host);
227 /* check for 'DO' command - copy to 'DO' string */
228 while (*bps == '-') { *bps++=' ';}
229 if (isspace(bps[-1]) && toupper(bps[0])=='D' &&
230 toupper(bps[1])=='O' && isspace(bps[2])) {
231 /* have some 'DO' commands */
232 /* check where the end of the last DO statement is */
234 sql_do_cnt = 1; /* count up the number of 'DO' statements for later */
236 while ((bp=strchr(bdp,';'))!=NULL) {
237 tp = bp+2; /* skip ;\n */
238 while (isspace(*tp) || *tp == '-') {*tp++ = ' ';}
239 if (toupper(*tp)=='D' && toupper(tp[1])=='O' && isspace(tp[2])) {
240 sql_do_cnt++; /* count the DO statements */
241 bdp = tp+3; /* move to the next DO statement */
245 if (bp != NULL) { /* end of the last DO, begin of select */
247 *(bp+1)='\0'; /* terminate DO strings */
248 if ((sql_do = calloc(strlen(bps)+1, sizeof(char)))==NULL) {
249 fprintf(stderr," cannot allocate %d for sql_do\n",strlen(bps));
254 *(bp+1)=tchar; /* replace missing ';' */
257 while (isspace(*bps)) bps++;
260 fprintf(stderr," terminal ';' not found: %s\n",bps);
263 /* all the DO commands are in m_fptr->sql_do in the form:
264 DO command1; DO command2; DO command3; */
266 while (sql_do_cnt-- && (bp=strchr(bdp,';'))!=NULL) {
267 /* do the pgsql statement on bdp+3 */
268 /* check for error */
270 res = PQexec(m_fptr->pgsql_conn,bdp+3);
271 if (PQresultStatus(res) != PGRES_COMMAND_OK) {
272 fprintf(stderr,"*** Error %s - query failed:\n%s\n",
273 PQerrorMessage(m_fptr->pgsql_conn), bdp+3);
281 while (isspace(*bdp)) bdp++;
285 /* copy 1st query field */
286 if ((bp=strchr(bps,';'))!=NULL) {
288 if ((m_fptr->sql_query=calloc(strlen(bps)+41,sizeof(char)))==NULL) {
289 fprintf(stderr, " cannot allocate space for query string [%d], %s\n",
293 /* have query, copy it */
295 strncpy(m_fptr->sql_query,"DECLARE next_seq CURSOR FOR ",40);
296 strcat(m_fptr->sql_query,bps);
297 *bp=';'; /* replace ; */
299 while(isspace(*bps)) bps++;
303 fprintf(stderr," cannot find database query field:\n%s\n",tmp_str);
307 /* copy get_desc field */
308 if ((bp=strchr(bps,';'))!=NULL) {
310 if ((m_fptr->sql_getdesc=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
311 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
315 /* have get_desc, copy it */
317 strcpy(m_fptr->sql_getdesc,bps);
318 *bp=';'; /* replace ; */
320 while(isspace(*bps)) bps++;
324 fprintf(stderr," cannot find getdesc field:\n%s\n",tmp_str);
328 if ((bp=strchr(bps,';'))!=NULL) { *bp='\0';}
330 if ((m_fptr->sql_getseq=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
331 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
336 if (strlen(bps) > 0) {
337 strcpy(m_fptr->sql_getseq,bps);
340 fprintf(stderr," cannot find getseq field:\n%s\n",tmp_str);
343 if (bp!=NULL) *bp=';';
345 /* now do the fetch */
347 res = PQexec(m_fptr->pgsql_conn,"BEGIN;");
348 if (PQresultStatus(res) != PGRES_COMMAND_OK) {
349 fprintf(stderr,"*** Error %s - BEGIN failed:\n",
350 PQerrorMessage(conn));
356 res = PQexec(m_fptr->pgsql_conn, m_fptr->sql_query);
357 if (PQresultStatus(res) != PGRES_COMMAND_OK) {
358 fprintf(stderr,"*** Error %d:%s - query failed:\n%s\n",
359 PQresultStatus(res),PQerrorMessage(conn), m_fptr->sql_query);
364 m_fptr->pgsql_res=NULL;
369 free(m_fptr->sql_getseq);
370 free(m_fptr->sql_getdesc);
371 free(m_fptr->sql_query);
378 pgsql_reopen(struct lmf_str *m_fptr) {
379 m_fptr->sql_reopen = 1;
384 pgsql_closelib(struct lmf_str *m_fptr) {
386 if (m_fptr == NULL) return;
387 if (m_fptr->pgsql_res != NULL) PQclear(m_fptr->pgsql_res);
388 PQfinish(m_fptr->pgsql_conn);
389 m_fptr->sql_reopen=0;
393 static char *sql_seq = NULL, *sql_seqp;
394 static int sql_seq_len;
398 pgsql_getlib( unsigned char *seq,
404 struct lmf_str *lm_fd,
407 register unsigned char *cp, *seqp;
409 unsigned char *seqm, *seqm1;
413 /* int l_start, l_stop, len; */
426 /* get a row, with UID, sequence */
429 /* check to see if we already have a valid result */
430 if (lm_fd->pgsql_res==NULL) {
431 res = PQexec(lm_fd->pgsql_conn,"FETCH next_seq");
432 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
433 fprintf(stderr,"*** Error %s - getlib FETCH failed:\n%s\n",
434 PQerrorMessage(lm_fd->pgsql_conn), lm_fd->sql_query);
436 lm_fd->pgsql_res = NULL;
442 else {res = lm_fd->pgsql_res;}
444 if (PQntuples(res)>0) {
445 lm_fd->pgsql_res = res;
446 *libpos=(fseek_t)atol(PQgetvalue(res,0,0));
449 if (PQnfields(res) > 2 && (bp=strchr(PQgetvalue(res,0,2),'@'))!=NULL &&
450 !strncmp(bp+1,"C:",2)) sscanf(bp+3,"%ld",l_off);
452 lm_fd->sql_seqp = PQgetvalue(res,0,1);
454 /* because of changes in pgsql_ranlib(), it is essential that
455 libstr return the unique identifier; thus we must use
456 sql_row[0], not sql_row[2]. Using libstr as the UID allows
457 one to use any UID, not just numeric ones. *libpos is not
458 used for pgsql libraries.
461 if (n_libstr <= MAX_UID) {
462 /* the normal case returns only GID/sequence */
463 strncpy(libstr,PQgetvalue(res,0,0),MAX_UID-1);
464 libstr[MAX_UID-1]='\0';
467 /* here we do not use the UID in libstr, because we are not
468 going back into the db */
469 /* the PVM case also returns a long description */
470 if (PQnfields(res)>2) {
471 strncpy(libstr,PQgetvalue(res,0,2),n_libstr-1);
474 strncpy(libstr,PQgetvalue(res,0,0),n_libstr-1);
476 libstr[n_libstr-1]='\0';
480 PQclear(lm_fd->pgsql_res);
481 lm_fd->pgsql_res=NULL;
488 for (cp=(unsigned char *)lm_fd->sql_seqp; seqp<seqm1 && *cp; ) {
489 if ((*seqp++=ap[*cp++])<NA &&
490 (*seqp++=ap[*cp++])<NA &&
491 (*seqp++=ap[*cp++])<NA &&
492 (*seqp++=ap[*cp++])<NA &&
493 (*seqp++=ap[*cp++])<NA &&
494 (*seqp++=ap[*cp++])<NA &&
495 (*seqp++=ap[*cp++])<NA &&
496 (*seqp++=ap[*cp++])<NA &&
497 (*seqp++=ap[*cp++])<NA &&
498 (*seqp++=ap[*cp++])<NA) continue;
500 if (*(cp-1)==0) break;
502 lm_fd->sql_seqp = (char *)cp;
504 if (seqp>=seqm1) (*lcont)++;
507 PQclear(lm_fd->pgsql_res);
508 lm_fd->pgsql_res = NULL;
512 /* if ((int)(seqp-seq)==0) return 1; */
513 return (int)(seqp-seq);
517 pgsql_ranlib(char *str,
521 struct lmf_str *lm_fd
524 char tmp_query[1024], tmp_val[20];
530 /* put the UID into the query string - cannot use sprintf because of
533 /* sprintf(tmp_query,lm_fd->sql_getdesc,libpos); */
535 if ((bp=strchr(lm_fd->sql_getdesc,'#'))==NULL) {
536 fprintf(stderr, "no KEY position in %s\n",lm_fd->sql_getdesc);
541 strncpy(tmp_query,lm_fd->sql_getdesc,sizeof(tmp_query));
542 tmp_query[sizeof(tmp_query)-1]='\0';
543 /* sprintf(tmp_val,"%ld",(long)libpos); */
544 strncat(tmp_query,libstr,sizeof(tmp_query)-1);
545 strncat(tmp_query,bp+1,sizeof(tmp_query)-1);
547 lm_fd->lpos = libpos;
550 /* fprintf(stderr," requesting: %s\n",tmp_query); */
552 if (lm_fd->pgsql_res !=NULL) {
553 PQclear(lm_fd->pgsql_res);
554 lm_fd->pgsql_res = NULL;
557 res = PQexec(lm_fd->pgsql_conn,tmp_query);
558 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
559 lm_fd->pgsql_res = NULL;
561 sprintf(str,"gi|%ld ***Error - query failed***",(long)libpos);
562 fprintf(stderr,"*** Error %s - ranlib DESC failed:\n%s\n",
563 PQerrorMessage(lm_fd->pgsql_conn), tmp_query);
568 if (PQntuples(res)<=0) {
569 /* fprintf(stderr,"*** Error = use result failed\n%s\n",
570 pgsql_error(lm_fd->pgsql_conn)); */
571 sprintf(str,"gi|%ld ***use result failed***",(long)libpos);
575 if (PQgetvalue(res,0,1)!= NULL) strncpy(str,PQgetvalue(res,0,1),cnt-1);
576 else strncpy(str,PQgetvalue(res,0,0),cnt-1);
578 /* change this later to support multiple row returns */
580 while (strlen(str) < cnt-1 &&
581 (lm_fd->sql_row = pgsql_fetch_row(lm_fd->pgsql_res))!=NULL) {
582 strncat(str," ",cnt-2-strlen(str));
583 if (lm_fd->sql_row[1]!=NULL)
584 strncat(str,lm_fd->sql_row[1],cnt-2-strlen(str));
590 if ((bp = strchr(str,'\r'))!=NULL) *bp='\0';
591 if ((bp = strchr(str,'\n'))!=NULL) *bp='\0';
596 lm_fd->pgsql_res = NULL;
598 /* get the sequence, set up for pgsql_getseq() */
599 /* put the UID into the query string */
601 if ((bp=strchr(lm_fd->sql_getseq,'#'))==NULL) {
602 fprintf(stderr, "no GID position in %s\n",lm_fd->sql_getseq);
607 strncpy(tmp_query,lm_fd->sql_getseq,sizeof(tmp_query));
608 tmp_query[sizeof(tmp_query)-1]='\0';
609 /* sprintf(tmp_val,"%ld",(long)libpos); */
610 strncat(tmp_query,libstr,sizeof(tmp_query));
611 strncat(tmp_query,bp+1,sizeof(tmp_query));
615 res = PQexec(lm_fd->pgsql_conn,tmp_query);
616 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
618 lm_fd->pgsql_res = NULL;
619 fprintf(stderr,"*** Error - ranlib SEQ failed:\n%s\n%s\n",tmp_query,
620 PQerrorMessage(lm_fd->pgsql_conn));
624 lm_fd->pgsql_res = res;