Sped up some query-table queries by adding optimize_sql_stmt() call

to build_sql_stmt().

diff --git a/server/qrtn.dc b/server/qrtn.dc
index 4944c8569eb729c7220baf99990dbc24a46bae18..9234e76b7e066369de6acbf260698db5d0a6e7d6 100644 (file)
--- a/server/qrtn.dc
+++ b/server/qrtn.dc
@@ -14,6 +14,7 @@ static char *rcsid_qrtn_dc = "$Header$";
 #endif lint
 
 #include <mit-copyright.h>
+#include <string.h>
 #include "query.h"
 #include "mr_server.h"
 EXEC SQL INCLUDE sqlca;  /* SQL Communications Area */
@@ -717,6 +718,91 @@ EXEC SQL END DECLARE SECTION;
     return ((rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS);
 }
 
+char *sqlstrstr(str,pat)
+    char *str;
+    char *pat;
+{
+    register char *p=pat;
+    
+    do {
+       if(*str=='\'') {    /* Skip over single-quote delimited substrings */ 
+           while(*++str && (*str!='\'')) 
+               ; 
+           continue;
+       }
+       if(*str==*p) {
+           register char *s;
+           s=str; 
+           while(*++p && (*++s==*p))
+               ; 
+           if(*p) p=pat;  /* failed */
+       }
+    } while(*p && *++str);
+
+    if(!*str) str=NULL;
+    return(str);
+}
+
+void optimize_sql_stmt(buf)
+char *buf;
+{
+    char *point=buf, *pat, *eopat, *esc1, *esc2, *csr;
+
+    for(point=buf; point=sqlstrstr(point,"LIKE"); point++)  {
+       /* Now pointing to string "LIKE" */
+
+       /* Look at next word */
+       for(pat=point+4; *pat==' '; pat++) ;
+
+       /* Is it a single-quote delimited string? */
+       if(*pat!='\'') continue;
+
+       /* look for "escape" clause - save escape character */
+       /* 1. Find end of pattern */
+       for(eopat=pat+1; 1; eopat++) {
+           if(*eopat=='\'') {
+               if(eopat[1]=='\'')  /* single-quote is self-escaping */
+                   eopat++;
+               else
+                   break;
+           }
+       }
+
+       /* 2. Look at next word */
+       for(esc1=eopat; *++esc1==' ';) ;
+
+       /* 3. esc1=0 if not "ESCAPE '?'", where the ? may be any character. */
+       if(strncmp(esc1,"ESCAPE",6)) esc1=NULL;
+
+       if(esc1) {
+           for(esc2=esc1+6; *esc2==' '; esc2++) ;
+           
+           if(*esc2++!='\'') continue; /* Bad SQL syntax. Skip. */
+           /* esc2 now points at the escape character itself */
+           if(esc2[1]!='\'') continue; /* Weird escape string. Skip. */
+       } else {
+           esc2="\\";
+       }
+
+       /* Is pattern free from special characters? */
+       for(csr=pat; csr<eopat; csr++) 
+           if((*csr=='%') || (*csr=='_') || (*csr==*esc2)) break;
+       if(csr!=eopat) continue; /* Uses pattern matching. Skip. */
+
+       /* Optimize the query statement */
+       /* 1. Change "LIKE" to " =  " */
+       memcpy(point," =  ",4);
+       
+       /* 2. Change "ESCAPE" to "      " */
+       if(esc1) {
+           memset(esc1,' ',6);
+           /* 3. Change  "'*'" to "   " */
+           /*    (Changes '''' to "    ") */
+           if(esc2) memset(esc2-1,' ',(*esc2=='\'')?4:3); 
+       }
+    }
+}
+
 build_sql_stmt(result_buf,cmd,targetlist,argv,qual)
     char *result_buf;
     char *cmd;
@@ -758,6 +844,8 @@ build_sql_stmt(result_buf,cmd,targetlist,argv,qual)
        } else *res++ = *fmt;                            /* text -> result buffer */
     }
     *res='\0';
+
+    optimize_sql_stmt(result_buf);
 }
 
 do_update(q, argv, qual, action, actarg)