001 /***************************************************************************************************
002 * MODULE DESCRIPTION
003 ****************************************************************************************************
004 *
005 * NAME: SqlSearchParser.java
006 * LANGUAGE: Java2
007 * DATE: 8.4.2003
008 * AUTHOR: Miika Nurminen, Jyväskylän yliopisto
009 *
010 ****************************************************************************************************
011 * COPYRIGHT (C) KIURU -PROJECT GROUP
012 * Limited rights granted. Please refer to license
013 ****************************************************************************************************
014 *
015 ****************************************************************************************************
016 * UPDATES
017 ****************************************************************************************************
018 *
019 * 8.4.2003 Initial release
020 * 16.4.2003 Some bugs with phrase search.... /mn
021 *
022 ****************************************************************************************************/
023 package kiurubeans;
024
025 import java.util.*;
026 import kotkabeans.Encoder;
027
028 /**
029 * Generates part of sql-query when you want to make such a query, that retrieves
030 * fields by pattern matching. This is a generalized version of
031 * kiurubeans.Tools.parseSearch method.
032 *
033 * @author Miika Nurminen
034 */
035 public class SqlSearchParser {
036 // Attributes
037 /** Delimiters indicating OR clause*/
038 public static final String orDelims = ",;:/";
039 /** Delimiters indicating AND clause*/
040 public static final String andDelims = " ";
041 /** Phrase delimiters */
042 public static final String phraseDelims = "\"'";
043 /** User wildcards */
044 public static final String wildCards = "*%";
045
046
047 /**
048 * Processes input for a single token to SQL string
049 * @param input user's input string
050 * @param fieldName SQL field to apply for search
051 */
052 private static String format(String input,String fieldName) {
053 return " upper("+fieldName+") LIKE '" + kotkabeans.Encoder.SQLEncode(input).toUpperCase() +"' ";
054 }
055
056 /**
057 * Processes and-delimited chunk of users search string
058 * @param input user's input string
059 * @param fieldName SQL field to apply for search
060 */
061 private static String handleAndChunk(String input,String fieldName) {
062 if (KiuruString.anyCharIndexOf(input, andDelims+wildCards+phraseDelims)==-1) {
063 return format(input+"%",fieldName);
064 }
065 input = input.replaceAll("["+Encoder.regExpEncode(wildCards)+"]", "%");
066 PhraseStringTokenizer t = new PhraseStringTokenizer(input,andDelims,phraseDelims/*""*/);
067 StringBuffer result=new StringBuffer(20);
068 String s = null;
069 String fin = null;
070 while(t.hasMoreElements()) {
071 s = t.nextToken();
072 if (KiuruString.anyCharIndexOf(s,phraseDelims)!=-1) {
073 fin = s.replaceAll("["+Encoder.regExpEncode(phraseDelims)+"]","");//KiuruString.stripChars(s, phraseDelims);
074 }
075 else {
076 if (s.indexOf('%')==-1) {
077 fin = "%"+s+"%";
078 }
079 else fin = s;
080 }
081 if (result.length()>0) result.append(" AND ");
082 result.append(format(fin,fieldName));
083 }
084 return result.toString();
085 }
086
087 /**
088 * Generates SQL clause for searching.
089 *
090 * @param input user's input string
091 * @param fieldName SQL field to apply for search
092 * @return SQL clause for searching
093 */
094 public static String search(String input,String fieldName) {
095 StringBuffer result = new StringBuffer(100);
096 PhraseStringTokenizer p = new PhraseStringTokenizer(input, orDelims, phraseDelims);
097 String s = null;
098 while (p.hasMoreElements()) {
099 String t = p.nextToken().trim(); // trim might be more generalized..
100 if ( (t.length()==0) && ( (result.length()>0 || p.hasMoreElements()) ))
101 continue;
102 s=handleAndChunk(t,fieldName);
103 if ((result.length()>0) && (s.length()>0)) result.append(" OR ");
104 result.append(" ( "+s+" )");
105 }
106 return result.toString();
107 }
108
109 // Access methods
110
111 // Operations
112 /** Unit test.
113 * @param args command line arguments (not used).
114 */
115 public static void main(String[] args) {
116 String[] testCases = {
117 "",
118 " ",
119 "\"agora\"",
120 "ag aud",
121 "\"ag aud\", alfa",
122 "ag aud*, '*alfa'",
123 "'*aud*' ag,Ma*,Sem"
124 };
125
126 for (int i=0; i<testCases.length; i++) {
127 System.out.println("Test case: "+testCases[i]);
128 System.out.println(SqlSearchParser.search(testCases[i], "f"));
129 }
130 }
131 }
132 /***************************************************************************************************
133 * COPYRIGHT (C) KIURU -PROJECT GROUP
134 * Limited rights granted. Please refer to license.
135 **************************************************************************************************/