--- /dev/null
+
+/* mysql_lib.c copyright (c) 2000 William R. Pearson */
+
+/* $Name: fa_34_26_5 $ - $Id: mysql_lib.c,v 1.21 2006/04/12 18:00:02 wrp Exp $ */
+
+/* functions for opening, reading, seeking a mySQL 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 mySQL 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 ';' */
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <ctype.h>
+
+#include <mysql.h>
+#define MYSQL_LIB 16
+
+#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 mysql_getlib(unsigned char *, int, char *, int, fseek_t *, int *, struct lmf_str *, long *);
+void mysql_ranlib(char *, int, fseek_t, char *, struct lmf_str *m_fd);
+
+#define MYSQL_BUF 4096
+
+struct lmf_str *
+mysql_openlib(char *sname, int ldnaseq, int *sascii) {
+ FILE *sql_file;
+ 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_do;
+ int sql_do_cnt;
+ int sql_port;
+ struct lmf_str *m_fptr;
+
+ /* if (sql_reopen) return NULL; - should not be called for re-open */
+
+ tmp_str_len = MYSQL_BUF;
+ if ((tmp_str=(char *)calloc(tmp_str_len,sizeof(char)))==NULL) {
+ fprintf(stderr,"cannot allocate %d for mySQL 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 mySQL 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 mySQL 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 += MYSQL_BUF;
+ if ((tmp_str=(char *)realloc(tmp_str,tmp_str_len))==NULL) {
+ fprintf(stderr,
+ " cannot reallocate %d for mySQL buffer\n",tmp_str_len);
+ return NULL;
+ }
+ ttmp_str = &tmp_str[qqs_len];
+ if ((qs_len=fread(ttmp_str,sizeof(char),MYSQL_BUF,sql_file))<0) {
+ fprintf(stderr," cannot read mySQL 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");
+ sql_dbname = strtok(NULL," \t\n");
+ sql_user = strtok(NULL," \t\n");
+ sql_pass = strtok(NULL," \t\n");
+ if ((tp=strchr(sql_host,':'))!=NULL) {
+ *tp='\0';
+ sql_port=atoi(tp+1);
+ }
+ else sql_port = 0;
+ }
+ }
+ 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 = mysql_getlib;
+ m_fptr->ranlib = mysql_ranlib;
+ m_fptr->mm_flg = 0;
+ m_fptr->sql_reopen = 0;
+ m_fptr->lb_type = MYSQL_LIB;
+
+ /* now open the database, if necessary */
+ if ((m_fptr->mysql_conn=mysql_init(NULL))==NULL) {
+ fprintf(stderr,"*** Error - mysql_init\n");
+ goto error_r;
+ }
+
+ if (mysql_real_connect(m_fptr->mysql_conn,
+ sql_host,sql_user,sql_pass,
+ sql_dbname,
+ sql_port,
+ NULL,
+ 0)==NULL)
+ {
+ fprintf(stderr,"*** Error %u - could not open database:\n%s\n%s",
+ mysql_errno(m_fptr->mysql_conn),tmp_str,
+ mysql_error(m_fptr->mysql_conn));
+ goto error_r;
+ }
+ else {
+ 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 mysql statement on bdp+3 */
+ /* check for error */
+ *bp='\0';
+ if (mysql_query(m_fptr->mysql_conn,bdp+3)) {
+ fprintf(stderr,"*** Error %u - query failed:\n%s\n%s\n",
+ mysql_errno(m_fptr->mysql_conn), bdp+3, mysql_error(m_fptr->mysql_conn));
+ goto error_r;
+ }
+ *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)+1,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 {
+ strcpy(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 0;
+ }
+ if (bp!=NULL) *bp=';';
+
+ /* now do the query */
+
+ if (mysql_query(m_fptr->mysql_conn,m_fptr->sql_query)) {
+ fprintf(stderr,"*** Error %u - query failed:\n%s\n%s\n",
+ mysql_errno(m_fptr->mysql_conn), m_fptr->sql_query, mysql_error(m_fptr->mysql_conn));
+ goto error_r;
+ }
+
+ if ((m_fptr->mysql_res = mysql_use_result(m_fptr->mysql_conn)) == NULL) {
+ fprintf(stderr,"*** Error = use result failed\n%s\n",
+ mysql_error(m_fptr->mysql_conn));
+ goto error_r;
+ }
+ 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 *
+mysql_reopen(struct lmf_str *m_fptr) {
+ m_fptr->sql_reopen = 1;
+ return m_fptr;
+}
+
+void
+mysql_closelib(struct lmf_str *m_fptr) {
+
+ if (m_fptr == NULL) return;
+ if (m_fptr->mysql_res != NULL)
+ mysql_free_result(m_fptr->mysql_res);
+ mysql_close(m_fptr->mysql_conn);
+ m_fptr->sql_reopen=0;
+}
+
+/*
+static char *sql_seq = NULL, *sql_seqp;
+static int sql_seq_len;
+static MYSQL_ROW sql_row;
+*/
+
+int
+mysql_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;
+ 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;
+ if ((lm_fd->mysql_row =mysql_fetch_row(lm_fd->mysql_res))!=NULL) {
+ *libpos=(fseek_t)atol(lm_fd->mysql_row[0]);
+
+ /* for @P:1-n removed */
+ /*
+ if ((bp=strchr(lm_fd->mysql_row[2],'@'))!=NULL &&
+ !strncmp(bp+1,"P:",2)) {
+ sscanf(bp+3,"%d-%d",&l_start,&l_stop)
+ l_start--;
+ if (l_start < 0) l_start=0;
+ if (l_stop > (len=strlen(lm_fd->mysql_row[1]))) l_stop= len-1;
+ lm_fd->sql_seqp = lm_fd->mysql_row[1];
+ lm_fd->sql_seqp[l_stop]='\0';
+ lm_fd->sql_seqp += l_start;
+ */
+
+ if (lm_fd->mysql_row[2] == NULL) {
+ fprintf(stderr," NULL comment at: [%s] %ld\n",
+ lm_fd->mysql_row[0],*libpos);
+ }
+ else if ((bp=strchr(lm_fd->mysql_row[2],'@'))!=NULL &&
+ !strncmp(bp+1,"C:",2)) sscanf(bp+3,"%ld",l_off);
+ else *l_off = 1;
+
+ lm_fd->sql_seqp = lm_fd->mysql_row[1];
+
+ /* because of changes in mysql_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 mysql libraries.
+ */
+
+ if (n_libstr <= MAX_UID) {
+ /* the normal case returns only GID/sequence */
+ strncpy(libstr,lm_fd->mysql_row[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 (lm_fd->mysql_row[2]!=NULL) {
+ strncpy(libstr,lm_fd->mysql_row[2],n_libstr-1);
+ }
+ else {
+ strncpy(libstr,lm_fd->mysql_row[0],n_libstr-1);
+ }
+ libstr[n_libstr-1]='\0';
+ }
+ }
+ else {
+ mysql_free_result(lm_fd->mysql_res);
+ lm_fd->mysql_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;
+ if (lm_fd->sql_reopen) {
+ mysql_free_result(lm_fd->mysql_res);
+ lm_fd->mysql_res = NULL;
+ }
+ }
+
+ *seqp = EOSEQ;
+ /* if ((int)(seqp-seq)==0) return 1; */
+ return (int)(seqp-seq);
+}
+
+void
+mysql_ranlib(char *str,
+ int cnt,
+ fseek_t libpos,
+ char *libstr,
+ struct lmf_str *lm_fd
+ )
+{
+ char tmp_query[1024], tmp_val[20];
+ 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 GID 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->mysql_res !=NULL) {
+ mysql_free_result(lm_fd->mysql_res);
+ lm_fd->mysql_res = NULL;
+ }
+
+ if (mysql_query(lm_fd->mysql_conn,tmp_query)) {
+ fprintf(stderr,"*** Error - query failed:\n%s\n%s\n",tmp_query,
+ mysql_error(lm_fd->mysql_conn));
+ sprintf(str,"gi|%ld ***Error - query failed***",(long)libpos);
+ goto next1;
+ }
+
+ if ((lm_fd->mysql_res = mysql_use_result(lm_fd->mysql_conn)) == NULL) {
+/* fprintf(stderr,"*** Error = use result failed\n%s\n",
+ mysql_error(lm_fd->mysql_conn)); */
+ sprintf(str,"gi|%ld ***use result failed***",(long)libpos);
+ goto next0;
+ }
+
+ /* have the description */
+ if ((lm_fd->mysql_row = mysql_fetch_row(lm_fd->mysql_res))==NULL) {
+ /* fprintf(stderr," cannot fetch description: %s\n",tmp_query); */
+ sprintf(str,"gi|%ld ***cannot fetch description***",(long)libpos);
+ goto next0;
+ }
+
+ if (lm_fd->mysql_row[1] != NULL) strncpy(str,lm_fd->mysql_row[1],cnt-1);
+ else strncpy(str,lm_fd->mysql_row[0],cnt-1);
+ str[cnt-1]='\0';
+ while (strlen(str) < cnt-1 &&
+ (lm_fd->mysql_row = mysql_fetch_row(lm_fd->mysql_res))!=NULL) {
+ strncat(str," ",cnt-2-strlen(str));
+ if (lm_fd->mysql_row[1]!=NULL)
+ strncat(str,lm_fd->mysql_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:
+ mysql_free_result(lm_fd->mysql_res);
+ next1:
+ lm_fd->mysql_res = NULL;
+
+ /* get the sequence, set up for mysql_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='#';
+ }
+
+ if (mysql_query(lm_fd->mysql_conn,tmp_query)) {
+ fprintf(stderr,"*** Error - query failed:\n%s\n%s\n",tmp_query,
+ mysql_error(lm_fd->mysql_conn));
+ }
+
+ if ((lm_fd->mysql_res = mysql_use_result(lm_fd->mysql_conn)) == NULL) {
+ fprintf(stderr,"*** Error = use result failed\n%s\n",
+ mysql_error(lm_fd->mysql_conn));
+ }
+}
git://source.jalview.org / jabaws.git / blobdiff