2 /* mysql_lib.c copyright (c) 2000 William R. Pearson */
4 /* $Name: fa_34_26_5 $ - $Id: mysql_lib.c,v 1.21 2006/04/12 18:00:02 wrp Exp $ */
6 /* functions for opening, reading, seeking a mySQL 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 mySQL 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 ';' */
64 /* #include "upam.h" */
67 int sfnum[10], nsfnum;
70 int mysql_getlib(unsigned char *, int, char *, int, fseek_t *, int *, struct lmf_str *, long *);
71 void mysql_ranlib(char *, int, fseek_t, char *, struct lmf_str *m_fd);
73 #define MYSQL_BUF 4096
76 mysql_openlib(char *sname, int ldnaseq, int *sascii) {
78 char *tmp_str, *ttmp_str;
80 char *bp, *bps, *bdp, *tp, tchar;
81 int i, qs_len, qqs_len;
82 char *sql_db, *sql_host, *sql_dbname, *sql_user, *sql_pass;
86 struct lmf_str *m_fptr;
88 /* if (sql_reopen) return NULL; - should not be called for re-open */
90 tmp_str_len = MYSQL_BUF;
91 if ((tmp_str=(char *)calloc(tmp_str_len,sizeof(char)))==NULL) {
92 fprintf(stderr,"cannot allocate %d for mySQL buffer\n",tmp_str_len);
96 if (sname[0] == '%') {
97 strncpy(tmp_str,sname+1,tmp_str_len);
98 tmp_str[sizeof(tmp_str)-1]='\0';
101 if ((sql_file=fopen(sname,"r"))==NULL) {
102 fprintf(stderr," cannot open mySQL file: %s\n",sname);
106 if ((qs_len=fread(tmp_str,sizeof(char),tmp_str_len-1,sql_file))<=0) {
107 fprintf(stderr," cannot read mySQL file: %s\n",sname);
111 tmp_str[qs_len]='\0';
113 while (qqs_len >= tmp_str_len-1) {
114 tmp_str_len += MYSQL_BUF;
115 if ((tmp_str=(char *)realloc(tmp_str,tmp_str_len))==NULL) {
117 " cannot reallocate %d for mySQL buffer\n",tmp_str_len);
120 ttmp_str = &tmp_str[qqs_len];
121 if ((qs_len=fread(ttmp_str,sizeof(char),MYSQL_BUF,sql_file))<0) {
122 fprintf(stderr," cannot read mySQL file: %s\n",sname);
125 ttmp_str[qs_len]='\0';
133 if ((bp=strchr(bps,';'))!=NULL) {
135 if ((sql_db=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
136 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
140 /* have database name, parse the fields */
142 strcpy(sql_db,bps); /* strcpy OK because allocated strlen(bps) */
143 bps = bp+1; /* points to next char after ';' */
144 while (isspace(*bps)) bps++;
145 *bp=';'; /* replace ; */
147 while (*bp=='-') {*bp++ = ' ';}
148 sql_host = strtok(bp," \t\n");
149 sql_dbname = strtok(NULL," \t\n");
150 sql_user = strtok(NULL," \t\n");
151 sql_pass = strtok(NULL," \t\n");
152 if ((tp=strchr(sql_host,':'))!=NULL) {
160 fprintf(stderr," cannot find database fields:\n%s\n",tmp_str);
164 /* we have all the info we need to open a database, allocate lmf_str */
165 if ((m_fptr = (struct lmf_str *)calloc(1,sizeof(struct lmf_str)))==NULL) {
166 fprintf(stderr," cannot allocate lmf_str (%ld) for %s\n",
167 sizeof(struct lmf_str),sname);
171 /* have our struct, initialize it */
173 strncpy(m_fptr->lb_name,sname,MAX_FN);
174 m_fptr->lb_name[MAX_FN-1]='\0';
176 m_fptr->sascii = sascii;
178 m_fptr->sql_db = sql_db;
179 m_fptr->getlib = mysql_getlib;
180 m_fptr->ranlib = mysql_ranlib;
182 m_fptr->sql_reopen = 0;
183 m_fptr->lb_type = MYSQL_LIB;
185 /* now open the database, if necessary */
186 if ((m_fptr->mysql_conn=mysql_init(NULL))==NULL) {
187 fprintf(stderr,"*** Error - mysql_init\n");
191 if (mysql_real_connect(m_fptr->mysql_conn,
192 sql_host,sql_user,sql_pass,
198 fprintf(stderr,"*** Error %u - could not open database:\n%s\n%s",
199 mysql_errno(m_fptr->mysql_conn),tmp_str,
200 mysql_error(m_fptr->mysql_conn));
204 fprintf(stderr," Database %s opened on %s\n",sql_dbname,sql_host);
207 /* check for 'DO' command - copy to 'DO' string */
208 while (*bps == '-') { *bps++=' ';}
209 if (isspace(bps[-1]) && toupper(bps[0])=='D' &&
210 toupper(bps[1])=='O' && isspace(bps[2])) {
211 /* have some 'DO' commands */
212 /* check where the end of the last DO statement is */
214 sql_do_cnt = 1; /* count up the number of 'DO' statements for later */
216 while ((bp=strchr(bdp,';'))!=NULL) {
217 tp = bp+2; /* skip ;\n */
218 while (isspace(*tp) || *tp == '-') {*tp++ = ' ';}
219 if (toupper(*tp)=='D' && toupper(tp[1])=='O' && isspace(tp[2])) {
220 sql_do_cnt++; /* count the DO statements */
221 bdp = tp+3; /* move to the next DO statement */
225 if (bp != NULL) { /* end of the last DO, begin of select */
227 *(bp+1)='\0'; /* terminate DO strings */
228 if ((sql_do = calloc(strlen(bps)+1, sizeof(char)))==NULL) {
229 fprintf(stderr," cannot allocate %d for sql_do\n",strlen(bps));
234 *(bp+1)=tchar; /* replace missing ';' */
237 while (isspace(*bps)) bps++;
240 fprintf(stderr," terminal ';' not found: %s\n",bps);
243 /* all the DO commands are in m_fptr->sql_do in the form:
244 DO command1; DO command2; DO command3; */
246 while (sql_do_cnt-- && (bp=strchr(bdp,';'))!=NULL) {
247 /* do the mysql statement on bdp+3 */
248 /* check for error */
250 if (mysql_query(m_fptr->mysql_conn,bdp+3)) {
251 fprintf(stderr,"*** Error %u - query failed:\n%s\n%s\n",
252 mysql_errno(m_fptr->mysql_conn), bdp+3, mysql_error(m_fptr->mysql_conn));
257 while (isspace(*bdp)) bdp++;
261 /* copy 1st query field */
262 if ((bp=strchr(bps,';'))!=NULL) {
264 if ((m_fptr->sql_query=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
265 fprintf(stderr, " cannot allocate space for query string [%d], %s\n",
269 /* have query, copy it */
271 strcpy(m_fptr->sql_query,bps);
272 *bp=';'; /* replace ; */
274 while(isspace(*bps)) bps++;
278 fprintf(stderr," cannot find database query field:\n%s\n",tmp_str);
282 /* copy get_desc field */
283 if ((bp=strchr(bps,';'))!=NULL) {
285 if ((m_fptr->sql_getdesc=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
286 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
290 /* have get_desc, copy it */
292 strcpy(m_fptr->sql_getdesc,bps);
293 *bp=';'; /* replace ; */
295 while(isspace(*bps)) bps++;
299 fprintf(stderr," cannot find getdesc field:\n%s\n",tmp_str);
303 if ((bp=strchr(bps,';'))!=NULL) { *bp='\0';}
305 if ((m_fptr->sql_getseq=calloc(strlen(bps)+1,sizeof(char)))==NULL) {
306 fprintf(stderr, " cannot allocate space for database name [%d], %s\n",
311 if (strlen(bps) > 0) {
312 strcpy(m_fptr->sql_getseq,bps);
315 fprintf(stderr," cannot find getseq field:\n%s\n",tmp_str);
318 if (bp!=NULL) *bp=';';
320 /* now do the query */
322 if (mysql_query(m_fptr->mysql_conn,m_fptr->sql_query)) {
323 fprintf(stderr,"*** Error %u - query failed:\n%s\n%s\n",
324 mysql_errno(m_fptr->mysql_conn), m_fptr->sql_query, mysql_error(m_fptr->mysql_conn));
328 if ((m_fptr->mysql_res = mysql_use_result(m_fptr->mysql_conn)) == NULL) {
329 fprintf(stderr,"*** Error = use result failed\n%s\n",
330 mysql_error(m_fptr->mysql_conn));
336 free(m_fptr->sql_getseq);
337 free(m_fptr->sql_getdesc);
338 free(m_fptr->sql_query);
345 mysql_reopen(struct lmf_str *m_fptr) {
346 m_fptr->sql_reopen = 1;
351 mysql_closelib(struct lmf_str *m_fptr) {
353 if (m_fptr == NULL) return;
354 if (m_fptr->mysql_res != NULL)
355 mysql_free_result(m_fptr->mysql_res);
356 mysql_close(m_fptr->mysql_conn);
357 m_fptr->sql_reopen=0;
361 static char *sql_seq = NULL, *sql_seqp;
362 static int sql_seq_len;
363 static MYSQL_ROW sql_row;
367 mysql_getlib( unsigned char *seq,
373 struct lmf_str *lm_fd,
376 register unsigned char *cp, *seqp;
378 unsigned char *seqm, *seqm1;
380 /* int l_start, l_stop, len; */
393 /* get a row, with UID, sequence */
395 if ((lm_fd->mysql_row =mysql_fetch_row(lm_fd->mysql_res))!=NULL) {
396 *libpos=(fseek_t)atol(lm_fd->mysql_row[0]);
398 /* for @P:1-n removed */
400 if ((bp=strchr(lm_fd->mysql_row[2],'@'))!=NULL &&
401 !strncmp(bp+1,"P:",2)) {
402 sscanf(bp+3,"%d-%d",&l_start,&l_stop)
404 if (l_start < 0) l_start=0;
405 if (l_stop > (len=strlen(lm_fd->mysql_row[1]))) l_stop= len-1;
406 lm_fd->sql_seqp = lm_fd->mysql_row[1];
407 lm_fd->sql_seqp[l_stop]='\0';
408 lm_fd->sql_seqp += l_start;
411 if (lm_fd->mysql_row[2] == NULL) {
412 fprintf(stderr," NULL comment at: [%s] %ld\n",
413 lm_fd->mysql_row[0],*libpos);
415 else if ((bp=strchr(lm_fd->mysql_row[2],'@'))!=NULL &&
416 !strncmp(bp+1,"C:",2)) sscanf(bp+3,"%ld",l_off);
419 lm_fd->sql_seqp = lm_fd->mysql_row[1];
421 /* because of changes in mysql_ranlib(), it is essential that
422 libstr return the unique identifier; thus we must use
423 sql_row[0], not sql_row[2]. Using libstr as the UID allows
424 one to use any UID, not just numeric ones. *libpos is not
425 used for mysql libraries.
428 if (n_libstr <= MAX_UID) {
429 /* the normal case returns only GID/sequence */
430 strncpy(libstr,lm_fd->mysql_row[0],MAX_UID-1);
431 libstr[MAX_UID-1]='\0';
434 /* here we do not use the UID in libstr, because we are not
435 going back into the db */
436 /* the PVM case also returns a long description */
437 if (lm_fd->mysql_row[2]!=NULL) {
438 strncpy(libstr,lm_fd->mysql_row[2],n_libstr-1);
441 strncpy(libstr,lm_fd->mysql_row[0],n_libstr-1);
443 libstr[n_libstr-1]='\0';
447 mysql_free_result(lm_fd->mysql_res);
448 lm_fd->mysql_res=NULL;
455 for (cp=(unsigned char *)lm_fd->sql_seqp; seqp<seqm1 && *cp; ) {
456 if ((*seqp++=ap[*cp++])<NA &&
457 (*seqp++=ap[*cp++])<NA &&
458 (*seqp++=ap[*cp++])<NA &&
459 (*seqp++=ap[*cp++])<NA &&
460 (*seqp++=ap[*cp++])<NA &&
461 (*seqp++=ap[*cp++])<NA &&
462 (*seqp++=ap[*cp++])<NA &&
463 (*seqp++=ap[*cp++])<NA &&
464 (*seqp++=ap[*cp++])<NA &&
465 (*seqp++=ap[*cp++])<NA) continue;
467 if (*(cp-1)==0) break;
469 lm_fd->sql_seqp = (char *)cp;
471 if (seqp>=seqm1) (*lcont)++;
474 if (lm_fd->sql_reopen) {
475 mysql_free_result(lm_fd->mysql_res);
476 lm_fd->mysql_res = NULL;
481 /* if ((int)(seqp-seq)==0) return 1; */
482 return (int)(seqp-seq);
486 mysql_ranlib(char *str,
490 struct lmf_str *lm_fd
493 char tmp_query[1024], tmp_val[20];
498 /* put the UID into the query string - cannot use sprintf because of
501 /* sprintf(tmp_query,lm_fd->sql_getdesc,libpos); */
503 if ((bp=strchr(lm_fd->sql_getdesc,'#'))==NULL) {
504 fprintf(stderr, "no GID position in %s\n",lm_fd->sql_getdesc);
509 strncpy(tmp_query,lm_fd->sql_getdesc,sizeof(tmp_query));
510 tmp_query[sizeof(tmp_query)-1]='\0';
511 /* sprintf(tmp_val,"%ld",(long)libpos); */
512 strncat(tmp_query,libstr,sizeof(tmp_query)-1);
513 strncat(tmp_query,bp+1,sizeof(tmp_query)-1);
515 lm_fd->lpos = libpos;
518 /* fprintf(stderr," requesting: %s\n",tmp_query); */
520 if (lm_fd->mysql_res !=NULL) {
521 mysql_free_result(lm_fd->mysql_res);
522 lm_fd->mysql_res = NULL;
525 if (mysql_query(lm_fd->mysql_conn,tmp_query)) {
526 fprintf(stderr,"*** Error - query failed:\n%s\n%s\n",tmp_query,
527 mysql_error(lm_fd->mysql_conn));
528 sprintf(str,"gi|%ld ***Error - query failed***",(long)libpos);
532 if ((lm_fd->mysql_res = mysql_use_result(lm_fd->mysql_conn)) == NULL) {
533 /* fprintf(stderr,"*** Error = use result failed\n%s\n",
534 mysql_error(lm_fd->mysql_conn)); */
535 sprintf(str,"gi|%ld ***use result failed***",(long)libpos);
539 /* have the description */
540 if ((lm_fd->mysql_row = mysql_fetch_row(lm_fd->mysql_res))==NULL) {
541 /* fprintf(stderr," cannot fetch description: %s\n",tmp_query); */
542 sprintf(str,"gi|%ld ***cannot fetch description***",(long)libpos);
546 if (lm_fd->mysql_row[1] != NULL) strncpy(str,lm_fd->mysql_row[1],cnt-1);
547 else strncpy(str,lm_fd->mysql_row[0],cnt-1);
549 while (strlen(str) < cnt-1 &&
550 (lm_fd->mysql_row = mysql_fetch_row(lm_fd->mysql_res))!=NULL) {
551 strncat(str," ",cnt-2-strlen(str));
552 if (lm_fd->mysql_row[1]!=NULL)
553 strncat(str,lm_fd->mysql_row[1],cnt-2-strlen(str));
558 if ((bp = strchr(str,'\r'))!=NULL) *bp='\0';
559 if ((bp = strchr(str,'\n'))!=NULL) *bp='\0';
562 mysql_free_result(lm_fd->mysql_res);
564 lm_fd->mysql_res = NULL;
566 /* get the sequence, set up for mysql_getseq() */
567 /* put the UID into the query string */
569 if ((bp=strchr(lm_fd->sql_getseq,'#'))==NULL) {
570 fprintf(stderr, "no GID position in %s\n",lm_fd->sql_getseq);
575 strncpy(tmp_query,lm_fd->sql_getseq,sizeof(tmp_query));
576 tmp_query[sizeof(tmp_query)-1]='\0';
577 /* sprintf(tmp_val,"%ld",(long)libpos); */
578 strncat(tmp_query,libstr,sizeof(tmp_query));
579 strncat(tmp_query,bp+1,sizeof(tmp_query));
583 if (mysql_query(lm_fd->mysql_conn,tmp_query)) {
584 fprintf(stderr,"*** Error - query failed:\n%s\n%s\n",tmp_query,
585 mysql_error(lm_fd->mysql_conn));
588 if ((lm_fd->mysql_res = mysql_use_result(lm_fd->mysql_conn)) == NULL) {
589 fprintf(stderr,"*** Error = use result failed\n%s\n",
590 mysql_error(lm_fd->mysql_conn));