QueryConvertor


1   /*
2   
3   This software is OSI Certified Open Source Software.
4   OSI Certified is a certification mark of the Open Source Initiative.
5   
6   The license (Mozilla version 1.0) can be read at the MMBase site.
7   See http://www.MMBase.org/license
8   
9   */
10  package org.mmbase.util;
11  
12  import java.util.Enumeration  ;
13  import java.util.Iterator  ;
14  import java.util.StringTokenizer  ;
15  import java.util.Vector  ;
16  
17  import org.mmbase.bridge.Field;
18  import org.mmbase.core.CoreField;
19  import org.mmbase.storage.StorageManagerFactory;
20  import org.mmbase.storage.search.CompositeConstraint;
21  import org.mmbase.storage.search.Constraint;
22  import org.mmbase.storage.search.FieldCompareConstraint;
23  import org.mmbase.storage.search.FieldValueConstraint;
24  import org.mmbase.storage.search.implementation.BasicCompositeConstraint;
25  import org.mmbase.storage.search.implementation.BasicFieldValueConstraint;
26  import org.mmbase.storage.search.implementation.BasicSearchQuery;
27  import org.mmbase.storage.search.implementation.BasicStep;
28  import org.mmbase.storage.search.implementation.BasicStepField;
29  import org.mmbase.storage.search.legacy.ConstraintParser;
30  
31  /**
32   * Class for the converion of a expression string to a SQL where clause.
33   * The expressions string is expected to be in 'altavista' format.
34   * This means that logical operators are identified by '+' (AND), '-' (NOT),
35   * and '|' (OR).
36   * Comparative operators are the same as those used in SCAN (i.e. '=E', '=N', etc)
37   * A wildcarded strings (with '*' or '?' characters) are automatically converted
38   * to a LIKE expression.
39   * <br />
40   * The resulting converted expression is preceded with the SQL 'WHERE ' keyword.
41   * <br />
42   * Note that if the expression to convert starts with "WHERE", it is not converted at all,
43   * but returned as is.
44   *
45   * @move org.mmbase.storage.search.util
46   * @author Daniel Ockeloen
47   * @author Pierre van Rooden (javadocs)
48   * @version $Id: QueryConvertor.java,v 1.30 2005/10/05 10:44:00 michiel Exp $
49   */
50  public class QueryConvertor {
51  
52      static StorageManagerFactory factory = null;
53  
54      /**
55       * Converts query to a SQL "where"-clause.
56       * @param query the query to convert
57       * @param smf the storagemanagerfactory to use when converting fieldnames
58       * @deprecated Use {@link #setConstraint setConstraint()} to parse
59       *        these expressions.
60       */
61      public static String   altaVista2SQL(String   query, StorageManagerFactory smf) {
62          factory = smf;
63          return altaVista2SQL(query);
64      }
65  
66      /**
67       * Converts query to a SQL "where"-clause.
68       * @param query the query to convert
69       * @deprecated Use {@link #setConstraint setConstraint()} to parse
70       *        these expressions.
71       */
72      public static String   altaVista2SQL(String   query) {
73          if (query.indexOf("where")!=-1 || query.indexOf("WHERE")!=-1) {
74              return query;
75          }
76  
77          StringBuffer   buffer = new StringBuffer  (64);
78          // query = query.toLowerCase();
79          DBQuery parsedQuery = new DBQuery(query);
80          // log.debug("Converting: " + query);
81          if(!query.equals(""))
82              parsedQuery.sqlConversion(buffer);
83          // log.debug("Converted to: "+buffer.toString());
84  
85          return buffer.toString();
86      }
87  
88      /**
89       * Sets constraint for a
90       * {@link org.mmbase.storage.search.implementation.BasicSearchQuery
91       * BasicSearchQuery} object.
92       * <p>
93       * The constraint may be specified as either one of these formats:
94       * <ol>
95       * <li><code>null</code> or empty.
96       * <li>A SQL search condition, starting with "WHERE " (ignoring case).
97       * <li>A SQL search condition, of the form "WHERE(......)" (ignoring case).
98       * <li>Altavista format.
99       * </ol>
100      * If the query contains more than one step, the fields must be of the form
101      * <em>stepalias.field</em>.
102      * <p>See {@link org.mmbase.storage.search.legacy.ConstraintParser} for
103      * more on how SQL search conditions are supported.
104      * <p><b>Note:</b>
105      * This method is provided to support different constraint formats for
106      * backward compatibility (1, 3 and 4 above).
107      * Do not call this method directly from new code, but rather use
108      * {@link org.mmbase.storage.search.legacy.ConstraintParser} to parse
109      * search constraints.
110      *
111      * @param query The query.
112      * @param where The constraint.
113      * @since MMBase-1.7
114      */
115     public static void setConstraint(BasicSearchQuery query, String   where) {
116 
117         Constraint constraint = null;
118 
119         if (where == null || where.trim().length() == 0) {
120             // Empty constraint.
121 
122         } else if (where.substring(0, 6).equalsIgnoreCase("WHERE ")) {
123             // "where"-clause.
124             // Strip leading "where ".
125             constraint =
126                 new ConstraintParser(query).toConstraint(where.substring(6));
127 
128         } else if (where.substring(0, 6).equalsIgnoreCase("WHERE(")) {
129             // "where"-clause, without space following "where".
130             // Supported for backward compatibility.
131             // Strip leading "where".
132             constraint =
133                 new ConstraintParser(query).toConstraint(where.substring(5));
134 
135         } else {
136             // AltaVista format.
137             DBQuery parsedQuery = new DBQuery(where);
138             constraint = parsedQuery.toConstraint(query);
139         }
140         query.setConstraint(constraint);
141     }
142 }
143 
144 /**
145  * Basic Class for parsing values and expressions.
146  */
147 class ParseItem {
148 
149     /**
150      * Appends the converted item to the stringbuffer.
151      * @param result the stringbuffer to which to add the item
152      */
153     public void sqlConversion(StringBuffer   result) {
154     }
155 
156     /**
157      * Returns the converted item as a <code>String</code>
158      */
159     public String   toString() {
160         StringBuffer   result = new StringBuffer  ();
161         this.sqlConversion(result);
162         return result.toString();
163     }
164 }
165 
166 /**
167  * Basic Class for parsing a set of conditional expressions.
168  */
169 class DBQuery  extends ParseItem {
170     // logger
171     //private static Logger log = Logging.getLoggerInstance(DBQuery.class.getName());
172 
173     public Vector   items = new Vector  ();
174 
175     /**
176      * Creates the query
177      * @item the query to convert
178      */
179     public DBQuery(String   query) {
180         StringTokenizer   parser = new StringTokenizer  (query, "+-|",true);
181         ParseItem item;
182 
183         while (parser.hasMoreTokens()) {
184             item = new DBConditionItem(parser.nextToken());
185             items.addElement(item);
186 
187             if (parser.hasMoreTokens()) {
188                 item = new DBLogicalOperator(parser.nextToken());
189                 items.addElement(item);
190             }
191         }
192     }
193 
194     /**
195      * Appends the converted query to the stringbuffer.
196      * @param result the stringbuffer to which to add the query
197      */
198     public void sqlConversion(StringBuffer   result) {
199         Enumeration   enumeration = items.elements();
200 
201         result.append("WHERE ");
202 
203         while (enumeration.hasMoreElements()) {
204             ((ParseItem)enumeration.nextElement()).sqlConversion(result);
205         }
206     }
207 
208     /**
209      * Converts this query to a constraint for a search query.
210      *
211      * @param SearchQuery The search query.
212      * @return The constraint.
213      */
214     // package access!
215     Constraint toConstraint(BasicSearchQuery query) {
216         BasicCompositeConstraint compositeConstraint = null;
217         BasicFieldValueConstraint fieldValueConstraint = null;
218 
219         Iterator   iItems = items.iterator();
220         DBLogicalOperator logicalOperator = null;
221         while (iItems.hasNext()) {
222 
223             // Logical operator requires compositeConstraint.
224             if (logicalOperator != null) {
225 
226                 // Create new composite constraint when not present already.
227                 if (compositeConstraint == null) {
228                     if (logicalOperator.logOperator == DBLogicalOperator.OR) {
229                         compositeConstraint
230                             = new BasicCompositeConstraint(
231                                 CompositeConstraint.LOGICAL_OR);
232                     } else {
233                         compositeConstraint
234                             = new BasicCompositeConstraint(
235                                 CompositeConstraint.LOGICAL_AND);
236                     }
237                     compositeConstraint.addChild(fieldValueConstraint);
238 
239                 // If a composite constraint is already present, add a new
240                 // child composite constraint if the logical operator is
241                 // not compatible.
242                 } else if (compositeConstraint.getLogicalOperator()
243                         == CompositeConstraint.LOGICAL_AND) {
244                     if (logicalOperator.logOperator
245                             == DBLogicalOperator.OR) {
246                         BasicCompositeConstraint compositeConstraint2
247                             = new BasicCompositeConstraint(
248                                 CompositeConstraint.LOGICAL_OR);
249                         compositeConstraint2.addChild(compositeConstraint);
250                         compositeConstraint = compositeConstraint2;
251                     }
252                 } else if (compositeConstraint.getLogicalOperator()
253                         == CompositeConstraint.LOGICAL_OR) {
254                     if (logicalOperator.logOperator
255                             != DBLogicalOperator.OR) {
256                         BasicCompositeConstraint compositeConstraint2
257                             = new BasicCompositeConstraint(
258                                 CompositeConstraint.LOGICAL_AND);
259                         compositeConstraint2.addChild(compositeConstraint);
260                         compositeConstraint = compositeConstraint2;
261                     }
262                 }
263             }
264 
265             DBConditionItem condition = (DBConditionItem) iItems.next();
266 
267             // Find corresponding field in query.
268             BasicStepField field = null;
269             Iterator   iFields = query.getFields().iterator();
270             while (iFields.hasNext()) {
271                 BasicStepField field2 = (BasicStepField) iFields.next();
272                 String   alias2 = field2.getStep().getAlias();
273                 if (alias2 == null) {
274                     alias2 = field2.getStep().getTableName();
275                 }
276                 if ((condition.prefix == null
277                         || alias2.equals(condition.prefix))
278                     && field2.getFieldName().equals(condition.fieldName)) {
279                     field = field2;
280                     break;
281                 }
282             }
283 
284             if (field == null) {
285                 // Field not found, find step and add field.
286                 BasicStep step = null;
287                 if (condition.prefix == null) {
288                     step = (BasicStep) query.getSteps().get(0);
289                 } else {
290                     Iterator   iSteps = query.getSteps().iterator();
291                     while (iSteps.hasNext()) {
292                         BasicStep step2 = (BasicStep) iSteps.next();
293                         if (step2.getAlias().equals(condition.prefix)) {
294                             step = step2;
295                             break;
296                         }
297                     }
298                     if (step == null) {
299                         // Step not found.
300                         throw new IllegalStateException  ("Step with alias '"
301                             + condition.prefix + "' not found in this query: "
302                             + query);
303                     }
304                 }
305 
306                 CoreField coreField = step.getBuilder().getField(condition.fieldName);
307                 if (coreField == null) {
308                     // Field not found.
309                     throw new IllegalStateException  ("Field with name '"
310                         + condition.fieldName + "' not found in builder "
311                         + step.getTableName());
312                 } else {
313                     field = query.addField(step, coreField);
314                 }
315             }
316 
317             int fieldType = field.getType();
318             if (fieldType == Field.TYPE_STRING
319                 || fieldType == Field.TYPE_XML) {
320                 // String field.
321                 fieldValueConstraint = new BasicFieldValueConstraint(field, condition.value.getValue());
322                 fieldValueConstraint.setCaseSensitive(false);
323             } else {
324                 // Numerical field.
325                 Object   numericalValue = new Double  (condition.value.getValue());
326                 fieldValueConstraint = new BasicFieldValueConstraint(field, numericalValue);
327             }
328 
329             switch (condition.operator) {
330                 case DBConditionItem.NOTEQUAL:
331                     fieldValueConstraint.setOperator(FieldValueConstraint.NOT_EQUAL);
332                     break;
333 
334                 case DBConditionItem.EQUAL:
335                     if (fieldType == Field.TYPE_STRING
336                         || fieldType == Field.TYPE_XML) {
337                         fieldValueConstraint.setOperator(FieldCompareConstraint.LIKE);
338                     } else {
339                         fieldValueConstraint.setOperator(FieldCompareConstraint.EQUAL);
340                     }
341                     break;
342 
343                 case DBConditionItem.GREATER:
344                     fieldValueConstraint.setOperator(FieldCompareConstraint.GREATER);
345                     break;
346 
347                 case DBConditionItem.SMALLER:
348                     fieldValueConstraint.setOperator(FieldCompareConstraint.LESS);
349                     break;
350 
351                 case DBConditionItem.GREATEREQUAL:
352                     fieldValueConstraint.setOperator(FieldCompareConstraint.GREATER_EQUAL);
353                     break;
354 
355                 case DBConditionItem.SMALLEREQUAL:
356                     fieldValueConstraint.setOperator(FieldCompareConstraint.LESS_EQUAL);
357                     break;
358 
359                 default:
360                     // Unknown operator.
361                     throw new IllegalStateException  (
362                         "Invalid operator value: " + condition.operator);
363 
364             }
365 
366             // Add to compositeConstraint when present.
367             if (compositeConstraint != null) {
368                 fieldValueConstraint.setInverse(logicalOperator.logOperator
369                     == DBLogicalOperator.NOT);
370                 compositeConstraint.addChild(fieldValueConstraint);
371             }
372 
373             if (iItems.hasNext()) {
374                 logicalOperator = (DBLogicalOperator) iItems.next();
375             }
376         }
377 
378         if (compositeConstraint != null) {
379             return compositeConstraint;
380         } else {
381             return fieldValueConstraint;
382         }
383     }
384 }
385 
386 /**
387  * Class for conversion of boolean xpressions to their SQL equivalent.
388  * This class converts the following conditional operators encountered in the
389  * parameter passed to the constructor :<br />
390  * '=='' or '=E' to '='<br />
391  * '=N' to '<>'<br />
392  * '=G' to '&gt;'<br />
393  * '=g' to '&gt;='<br />
394  * '=S' to '&lt;'<br />
395  * '=s' to '&lt;='<br />
396  * It also wraps string values with the SQL lower() function, and uses LIKE
397  * when wildcards are used in a stringvalue.
398  *
399  */
400 class DBConditionItem extends ParseItem {
401     public static final int NOTEQUAL=0, EQUAL = 1, GREATER = 2, SMALLER = 3, GREATEREQUAL=4,SMALLEREQUAL=5;
402     // logger
403     //private static Logger log = Logging.getLoggerInstance(DBConditionItem.class.getName());
404 
405     /** The fieldname. */
406     String   fieldName = null;
407 
408     /** The table alias prefix (if present). */
409     String   prefix = null;
410 
411     /**
412      * The field identifier as it appears in SQL expressions, with
413      * table alias prefix (if present), and the fieldname converted to
414      * an allowed fieldname.
415      */
416     String   identifier = null;
417 
418     /**
419      * The comparison operator, must be one of the constants defined
420      * in this class.
421      */
422     int operator = 0;
423 
424     /** The value to compare to. */
425     DBValue value = null;
426 
427     /**
428      * Creates the boolean expression
429      * @item the expression to convert
430      */
431     public DBConditionItem(String   item) {
432         int conditionPos;
433         char operatorChar;
434 
435         conditionPos = item.indexOf('=');
436         if (conditionPos == -1) {
437             throw new IllegalArgumentException  (
438             "No '=' found in query item '" + item + "'");
439         }
440 
441         fieldName = item.substring(0, conditionPos);
442         int prefixPos = fieldName.indexOf(".");
443         if (prefixPos != -1) {
444             prefix = fieldName.substring(0, prefixPos);
445             fieldName = fieldName.substring(prefixPos + 1);
446         }
447         if (QueryConvertor.factory != null) {
448             identifier = (String  )QueryConvertor.factory.getStorageIdentifier(fieldName);
449         } else {
450             identifier = fieldName;
451         }
452         if (prefix != null) {
453             identifier = prefix +"."+ identifier;
454         }
455 
456         value = DBValue.abstractCreation(item.substring(conditionPos+2));
457 
458         operatorChar = item.charAt(conditionPos + 1);
459         // log.debug("char="+operatorChar);
460         switch (operatorChar) {
461         case '=':
462         case 'E':
463             operator = EQUAL;
464             break;
465         case 'N':
466             operator = NOTEQUAL;
467             break;
468         case 'G':
469             operator = GREATER;
470             break;
471         case 'g':
472             operator = GREATEREQUAL;
473             break;
474         case 'S':
475             operator = SMALLER;
476             break;
477         case 's':
478             operator = SMALLEREQUAL;
479             break;
480         default:
481             break;
482         }
483     }
484 
485     /**
486      * Appends the converted expression to the stringbuffer.
487      * @param result the stringbuffer to which to add the expression
488      */
489     public void sqlConversion(StringBuffer   result) {
490         if (value instanceof DBWildcardStringValue || value instanceof DBStringValue)
491             result.append("lower(").append(identifier).append(")");
492             //result.append("").append(identifier).append("");
493         else
494             result.append(identifier);
495 
496         if (value instanceof DBWildcardStringValue) {
497             result.append(" LIKE ");
498         }
499         else {
500             switch (operator) {
501             case EQUAL:
502                 result.append(" = ");
503                 break;
504             case NOTEQUAL:
505                 result.append(" <> ");
506                 break;
507             case GREATER:
508                 result.append(" > ");
509                 break;
510             case GREATEREQUAL:
511                 result.append(" >= ");
512                 break;
513             case SMALLER:
514                 result.append(" < ");
515                 break;
516             case SMALLEREQUAL:
517                 result.append(" <= ");
518                 break;
519             default:
520                 result.append(" = ");
521             }
522         }
523         value.sqlConversion(result);
524     }
525 }
526 
527 /**
528  * Basic Class for storing values.
529  */
530 class DBValue extends ParseItem {
531 
532     private String   value = null;
533 
534     /**
535      * Constructor, only subclasses can be instantiated.
536      */
537     protected DBValue() {}
538 
539     /**
540      * Determines whether a value is a string, a string with wildcards, or
541      * a number, and returns the appropriate class.
542      * @param value the value to parse
543      * @return the appropriate subclass of <code>DBValue</code>
544      */
545     public static DBValue abstractCreation(String   value) {
546         value = value.toLowerCase();
547         if (value.startsWith("'")) {
548             if (value.indexOf('?') >= 0 || value.indexOf('*') >= 0)
549                 return new DBWildcardStringValue(Strip.Chars(value,"' ",Strip.BOTH));
550             else
551                 return new DBStringValue(Strip.Chars(value,"' ",Strip.BOTH));
552         }
553         else
554             return new DBNumberValue(value);
555     }
556 
557     /**
558      * Sets value property.
559      *
560      * @param value The string representation of the value.
561      */
562     protected void setValue(String   value) {
563         this.value = value;
564     }
565 
566     /**
567      * Gets value property.
568      *
569      * @return The string representation of the value.
570      */
571     public String   getValue() {
572         return value;
573     }
574 }
575 
576 /**
577  * Class for storing numeric values.
578  */
579 class DBNumberValue extends DBValue {
580     /**
581      * Creates the numeric value
582      * @value the value to convert
583      */
584     public DBNumberValue(String   value) {
585         // Protection against empty numbers
586         if (value == null || value.length() == 0) {
587             setValue(Integer.toString(Integer.MIN_VALUE));
588         } else {
589             setValue(value);
590         }
591     }
592 
593     /**
594      * Appends the converted value to the stringbuffer.
595      * @param result the stringbuffer to which to add the expression
596      */
597     public void sqlConversion(StringBuffer   result) {
598         result.append(getValue());
599     }
600 }
601 
602 /**
603  * Class for storing and converting string values.
604  * Wraps the result with quotes.
605  */
606 class DBStringValue extends DBValue {
607     /**
608      * Creates the string value
609      * @value the value to convert
610      */
611     public DBStringValue(String   value) {
612         setValue(value);
613     }
614 
615     /**
616      * Appends the converted value to the stringbuffer.
617      * @param result the stringbuffer to which to add the expression
618      */
619     public void sqlConversion(StringBuffer   result) {
620         result.append("'").append(getValue()).append("'");
621     }
622 
623 }
624 
625 /**
626  * Class for storing and converting string values with wildcards.
627  * Wraps the result with quotes and replaces any wildcards with
628  * SQL-wildcards.
629  */
630 class DBWildcardStringValue extends DBValue {
631     /**
632      * Creates the wildcarded string value
633      * @value the value to convert
634      */
635     public DBWildcardStringValue(String   value) {
636         if (value == null) {
637             value = "";
638         }
639         setValue(value.replace('*', '%').replace('?', '_'));
640     }
641 
642     /**
643      * Appends the converted value to the stringbuffer.
644      * @param result the stringbuffer to which to add the expression
645      */
646     public void sqlConversion(StringBuffer   result) {
647         result.append("'").append(getValue()).append("'");
648     }
649 }
650 
651 /**
652  * Class for conversion of operators to their SQL equivalent.
653  * This class converts:<br />
654  * '+' to 'AND'<br />
655  * '-' to 'AND NOT'<br />
656  * '|' to 'OR'<br />
657  */
658 class DBLogicalOperator extends ParseItem {
659     public static final char AND = '+';
660     public static final char NOT = '-';
661     public static final char OR ='|';
662 
663     char logOperator;
664 
665     /**
666      * Creates the operator
667      * @operator the original operator to convert
668      */
669     public DBLogicalOperator(String   operator) {
670         if      (operator.equals("+")) logOperator = AND;
671         else if (operator.equals("-")) logOperator = NOT;
672         else if (operator.equals("|")) logOperator = OR;
673     }
674 
675     public DBLogicalOperator(char operator) {
676         logOperator = operator;
677     }
678 
679     /**
680      * Appends the converted operator to the stringbuffer.
681      * @param result the stringbuffer to which to add the operator
682      */
683     public void sqlConversion(StringBuffer   result) {
684         switch (logOperator) {
685             case AND:
686                 result.append(" AND ");
687                 break;
688             case NOT:
689                 result.append(" AND NOT ");
690                 break;
691             case OR:
692                 result.append(" OR ");
693                 break;
694             default:
695                 break;
696         }
697     }
698 }
699 
700
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags