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