KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > mmbase > storage > search > implementation > database > BasicSqlHandler


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.storage.search.implementation.database;
11
12 import org.mmbase.bridge.Field;
13 import org.mmbase.module.core.MMBase;
14 import org.mmbase.storage.search.*;
15 import org.mmbase.util.logging.*;
16 import java.util.*;
17 import java.text.DateFormat JavaDoc;
18 import java.text.SimpleDateFormat JavaDoc;
19 import java.text.FieldPosition JavaDoc;
20
21 /**
22  * Basic implementation.
23  *
24  * @author Rob van Maris
25  * @version $Id: BasicSqlHandler.java,v 1.62 2006/08/02 17:26:55 michiel Exp $
26  * @since MMBase-1.7
27  */

28
29 public class BasicSqlHandler implements SqlHandler {
30
31     private static final Logger log = Logging.getLoggerInstance(BasicSqlHandler.class);
32
33     private static final SimpleDateFormat JavaDoc dateFormat = new SimpleDateFormat JavaDoc("yyyy-MM-dd HH:mm:ss.SSS");
34     private static final FieldPosition JavaDoc dontcareFieldPosition = new FieldPosition JavaDoc(DateFormat.YEAR_FIELD);
35
36     /**
37      * Constructor.
38      */

39     public BasicSqlHandler() {
40     }
41
42     /**
43      * Utility method, modifies strings for use in SQL statements.
44      * This amounts to replacing all single quotes by two single quotes.
45      *
46      * @param str The input string.
47      * @return The modified string.
48      */

49     // XXX must wildcard characters be escaped?
50
// XXX perhaps place this somewhere else?
51
public static String JavaDoc toSqlString(String JavaDoc str) {
52         String JavaDoc result = str;
53         if (str != null) {
54             int offset = str.indexOf('\'');
55             if (offset != -1) {
56                 // At least one single quote found.
57
StringBuffer JavaDoc sb = new StringBuffer JavaDoc(str.length() + 4);
58                 int start = 0;
59                 do {
60                     sb.append(str.substring(start, offset)).append("''");
61                     start = offset + 1;
62                     offset = str.indexOf('\'', start);
63                 } while (offset != -1);
64                 sb.append(str.substring(start, str.length()));
65                 result = sb.toString();
66                 if (log.isDebugEnabled()) {
67                     log.debug("converted string \"" + str + "\" to \"" + result + "\"");
68                 }
69             }
70         }
71         return result;
72     }
73     /**
74      * Tests if a case sensitivity for a field constraint is false
75      * and relevant, i.e. the constraint is set to case insensitive and
76      * the field has string type.
77      *
78      * @param constraint The constraint.
79      * @return true if the constraint is set to case insensitive
80      * and the field has string type, false otherwise.
81      */

82     private static boolean isRelevantCaseInsensitive(FieldConstraint constraint) {
83         return !constraint.isCaseSensitive()
84         && (constraint.getField().getType() == Field.TYPE_STRING
85         || constraint.getField().getType() == Field.TYPE_XML);
86     }
87
88     /**
89      * Wether the 'LOWER' function needs to be used to implement case insensitivity. This is
90      * not always the case, because some database only match case insensitively, in which case it
91      * does not make sense to lowercase.
92      */

93     protected boolean useLower(FieldCompareConstraint constraint) {
94         return true;
95     }
96
97     protected void appendDateValue(StringBuffer JavaDoc sb, Date value) {
98         int timeZoneOffset = MMBase.getMMBase().getStorageManagerFactory().getTimeZoneOffset(value.getTime());
99         Date date = new Date(value.getTime() - timeZoneOffset);
100         //Date date = new Date(value.getTime());
101
//log.debug("Using offset " + timeZoneOffset + " " + value + " -> " + date);
102
dateFormat.format(date, sb, dontcareFieldPosition);
103     }
104
105     /**
106      * Represents field value as a string, appending the result to a
107      * stringbuffer.
108      * <p>
109      * Depending on the fieldType:
110      * <ul>
111      * <li> String values are converted to SQL-formatted string,
112      * surrounded by single quotes,
113      * <li>Numerical values are represented as integer (integral values)
114      * or floating point.
115      * </ul>
116      *
117      * @param sb The stringbuffer to append to.
118      * @param value The field value.
119      * @param toLowerCase True when <code>String</code> must be converted to
120      * lower case.
121      * @param fieldType The field type.
122      */

123     // TODO: elaborate javadoc, add to SqlHandler interface?
124
public void appendFieldValue(StringBuffer JavaDoc sb, Object JavaDoc value, boolean toLowerCase, int fieldType) {
125         if (fieldType == Field.TYPE_STRING || fieldType == Field.TYPE_XML) {
126             // escape single quotes in string
127
String JavaDoc stringValue = toSqlString((String JavaDoc) value);
128             // to lowercase when case insensitive
129
if (toLowerCase) {
130                 stringValue = stringValue.toLowerCase();
131             }
132             sb.append("'").
133             append(stringValue).
134             append("'");
135         } else if (fieldType == Field.TYPE_DATETIME) {
136             // should this not be translated to a date first??
137
if (value instanceof Number JavaDoc) {
138                 sb.append(((Number JavaDoc) value).longValue());
139             } else {
140                 sb.append("'");
141                 appendDateValue(sb, (Date) value);
142                 sb.append("'");
143             }
144         } else if (fieldType == Field.TYPE_BOOLEAN) {
145             boolean isTrue = ((Boolean JavaDoc) value).booleanValue();
146             if (isTrue) {
147                 sb.append("TRUE");
148             } else {
149                 sb.append("FALSE");
150             }
151         } else {
152             // Numerical field:
153
// represent integeral Number values as integer, other
154
// Number values as floating point, and String values as-is.
155
if (value instanceof Number JavaDoc) {
156                 Number JavaDoc numberValue = (Number JavaDoc) value;
157                 if (numberValue.doubleValue() == numberValue.intValue()) {
158                     // Integral Number value.
159
sb.append(numberValue.intValue());
160                 } else {
161                     // Non-integral Number value.
162
sb.append(numberValue.doubleValue());
163                 }
164             } else {
165                 // String value.
166
sb.append((String JavaDoc) value);
167             }
168         }
169     }
170
171     // javadoc is inherited
172
// XXX what exception to throw when an unsupported feature is
173
// encountered (currently throws UnsupportedOperationException)?
174
public String JavaDoc toSql(SearchQuery query, SqlHandler firstInChain)
175     throws SearchQueryException {
176         // XXX should table and field aliases be tested for uniqueness?
177

178         // Test for at least 1 step and 1 field.
179
if (query.getSteps().isEmpty()) {
180             throw new IllegalStateException JavaDoc("Searchquery has no step (at least 1 step is required).");
181         }
182         if (query.getFields().isEmpty()) {
183             throw new IllegalStateException JavaDoc("Searchquery has no field (at least 1 field is required).");
184         }
185
186         // SELECT
187
StringBuffer JavaDoc sbQuery = new StringBuffer JavaDoc("SELECT ");
188
189         // DISTINCT
190
// Note that DISTINCT can be omitted for an aggregating query.
191
// It is ommitted to make the resulting SQL more portable,
192
// some databases will otherwise report a syntax error (e.g. Informix).
193
if (query.isDistinct() && !query.isAggregating()) {
194             sbQuery.append("DISTINCT ");
195         }
196
197         firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
198
199         String JavaDoc strSQL = sbQuery.toString();
200         if (log.isDebugEnabled()) {
201             log.debug("generated SQL: " + strSQL);
202         }
203         return strSQL;
204     }
205
206
207     /**
208      * @since MMBase-1.8
209      */

210     protected void appendRelationConstraints(StringBuffer JavaDoc sbRelations, RelationStep relationStep, boolean multipleSteps) {
211
212         Step previousStep = relationStep.getPrevious();
213         Step nextStep = relationStep.getNext();
214         if (sbRelations.length() > 0) {
215             sbRelations.append(" AND ");
216         }
217         switch (relationStep.getDirectionality()) {
218         case RelationStep.DIRECTIONS_SOURCE:
219             sbRelations.append('(');
220             appendField(sbRelations, previousStep, "number", multipleSteps);
221             sbRelations.append('=');
222             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
223             sbRelations.append(" AND ");
224             appendField(sbRelations, nextStep, "number", multipleSteps);
225             sbRelations.append('=');
226             appendField(sbRelations, relationStep, "snumber", multipleSteps);
227             if (relationStep.getCheckedDirectionality()) {
228                 sbRelations.append(" AND ");
229                 appendField(sbRelations, relationStep, "dir", multipleSteps);
230                 sbRelations.append("<>1");
231             }
232             break;
233
234         case RelationStep.DIRECTIONS_DESTINATION:
235             sbRelations.append('(');
236             appendField(sbRelations, previousStep, "number", multipleSteps);
237             sbRelations.append('=');
238             appendField(sbRelations, relationStep, "snumber", multipleSteps);
239             sbRelations.append(" AND ");
240             appendField(sbRelations, nextStep, "number", multipleSteps);
241             sbRelations.append('=');
242             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
243             break;
244
245         case RelationStep.DIRECTIONS_BOTH:
246             if (relationStep.getRole() != null) {
247                 sbRelations.append("(((");
248             } else {
249                 sbRelations.append("((");
250             }
251             appendField(sbRelations, previousStep, "number", multipleSteps);
252             sbRelations.append('=');
253             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
254             sbRelations.append(" AND ");
255             appendField(sbRelations, nextStep, "number", multipleSteps);
256             sbRelations.append('=');
257             appendField(sbRelations, relationStep, "snumber", multipleSteps);
258             if (relationStep.getCheckedDirectionality()) {
259                 sbRelations.append(" AND ");
260                 appendField(sbRelations, relationStep, "dir", multipleSteps);
261                 sbRelations.append("<>1");
262             }
263             sbRelations.append(") OR (");
264             appendField(sbRelations, previousStep, "number", multipleSteps);
265             sbRelations.append('=');
266             appendField(sbRelations, relationStep, "snumber", multipleSteps);
267             sbRelations.append(" AND ");
268             appendField(sbRelations, nextStep, "number", multipleSteps);
269             sbRelations.append('=');
270             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
271             if (relationStep.getRole() != null) {
272                 sbRelations.append("))");
273             } else {
274                 sbRelations.append(')');
275             }
276             break;
277
278         case RelationStep.DIRECTIONS_ALL:
279             if (relationStep.getRole() != null) {
280                 sbRelations.append("(((");
281             } else {
282                 sbRelations.append("((");
283             }
284             appendField(sbRelations, previousStep, "number", multipleSteps);
285             sbRelations.append('=');
286             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
287             sbRelations.append(" AND ");
288             appendField(sbRelations, nextStep, "number", multipleSteps);
289             sbRelations.append('=');
290             appendField(sbRelations, relationStep, "snumber", multipleSteps);
291             sbRelations.append(") OR (");
292             appendField(sbRelations, previousStep, "number", multipleSteps);
293             sbRelations.append('=');
294             appendField(sbRelations, relationStep, "snumber", multipleSteps);
295             sbRelations.append(" AND ");
296             appendField(sbRelations, nextStep, "number", multipleSteps);
297             sbRelations.append('=');
298             appendField(sbRelations, relationStep, "dnumber", multipleSteps);
299             if (relationStep.getRole() != null) {
300                 sbRelations.append("))");
301             } else {
302                 sbRelations.append(')');
303             }
304             break;
305
306         case RelationStep.DIRECTIONS_EITHER:
307             throw new UnsupportedOperationException JavaDoc("Directionality 'EITHER' is not (yet) supported");
308
309         default: // Invalid directionality value.
310
throw new IllegalStateException JavaDoc(
311                                             "Invalid directionality value: " + relationStep.getDirectionality());
312         }
313         if (relationStep.getRole() != null) {
314             sbRelations.append(" AND ");
315             appendField(sbRelations, relationStep, "rnumber", multipleSteps);
316             sbRelations.append('=').append(relationStep.getRole());
317         }
318         sbRelations.append(')');
319     }
320
321     // javadoc is inherited
322
public void appendQueryBodyToSql(StringBuffer JavaDoc sb, SearchQuery query, SqlHandler firstInChain) throws SearchQueryException {
323
324         // Buffer expressions for included nodes, like
325
// "x.number in (...)".
326
StringBuffer JavaDoc sbNodes = new StringBuffer JavaDoc();
327
328         // Buffer expressions for relations, like
329
// "x.number = r.snumber AND y.number = r.dnumber".
330
StringBuffer JavaDoc sbRelations = new StringBuffer JavaDoc();
331
332         // Buffer fields to group by, like
333
// "alias1, alias2, ..."
334
StringBuffer JavaDoc sbGroups = new StringBuffer JavaDoc();
335
336         boolean multipleSteps = query.getSteps().size() > 1;
337
338         // Fields expression
339
List lFields = query.getFields();
340
341
342         boolean storesAsFile = MMBase.getMMBase().getStorageManagerFactory().hasOption(org.mmbase.storage.implementation.database.Attributes.STORES_BINARY_AS_FILE);
343         Iterator iFields = lFields.iterator();
344         boolean appended = false;
345         while (iFields.hasNext()) {
346             StepField field = (StepField) iFields.next();
347             if (field.getType() == Field.TYPE_BINARY) continue;
348             if (appended) {
349                 sb.append(',');
350             }
351             appended = true;
352             // fieldname prefixed by table alias.
353
Step step = field.getStep();
354             String JavaDoc fieldName = field.getFieldName();
355             String JavaDoc fieldAlias = field.getAlias();
356
357             if (field instanceof AggregatedField) {
358                 int aggregationType = ((AggregatedField) field).getAggregationType();
359                 if (aggregationType == AggregatedField.AGGREGATION_TYPE_GROUP_BY) {
360
361                     // Group by.
362
appendField(sb, step, fieldName, multipleSteps);
363
364                     // Append to "GROUP BY"-buffer.
365
if (sbGroups.length() > 0) {
366                         sbGroups.append(',');
367                     }
368                     if (fieldAlias != null) {
369                         sbGroups.append(getAllowedValue(fieldAlias));
370                     } else {
371                         appendField(sbGroups, step, fieldName, multipleSteps);
372                     }
373                 } else {
374
375                     // Aggregate function.
376
switch (aggregationType) {
377                     case AggregatedField.AGGREGATION_TYPE_COUNT:
378                         sb.append("COUNT(");
379                         break;
380
381                     case AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT:
382                         sb.append("COUNT(DISTINCT ");
383                         break;
384
385                     case AggregatedField.AGGREGATION_TYPE_MIN:
386                         sb.append("MIN(");
387                         break;
388
389                     case AggregatedField.AGGREGATION_TYPE_MAX:
390                         sb.append("MAX(");
391                         break;
392
393                     default:
394                         throw new IllegalStateException JavaDoc("Invalid aggregationType value: " + aggregationType);
395                     }
396                     appendField(sb, step, fieldName, multipleSteps);
397                     sb.append(')');
398                 }
399
400             } else {
401
402                 // Non-aggregate field.
403
appendField(sb, step, fieldName, multipleSteps);
404             }
405
406             // Field alias.
407
if (fieldAlias != null) {
408                 sb.append(" AS ").append(getAllowedValue(fieldAlias));
409             }
410
411         }
412
413
414         // When 'distinct', make sure all fields used for sorting are
415
// included in the query.
416
// Some databases require this (including PostgreSQL).
417
// By fixing this here, the result of the query remains consistent
418
// across databases, while requiring no modification in the calling
419
// code.
420
if (query.isDistinct()) {
421             if (log.isDebugEnabled()) {
422                 log.debug("Query is distinct, adding " + query.getSortOrders());
423             }
424             boolean needComma = appended;
425             Iterator iSortOrder = query.getSortOrders().iterator();
426             while (iSortOrder.hasNext()) {
427                 SortOrder sortOrder = (SortOrder) iSortOrder.next();
428                 StepField field = sortOrder.getField();
429                 if (lFields.indexOf(field) == -1) {
430                     if (needComma) sb.append(',');
431                     appendSortOrderField(sb, sortOrder, multipleSteps);
432                     needComma = true;
433                 }
434             }
435         }
436
437         // Tables
438
sb.append(" FROM ");
439         Iterator iSteps = query.getSteps().iterator();
440         while (iSteps.hasNext()) {
441             Step step = (Step) iSteps.next();
442             appendTableName(sb, step);
443
444             if (iSteps.hasNext()) {
445                 sb.append(",");
446             }
447
448             // Included nodes.
449
SortedSet nodes = step.getNodes();
450             if (nodes.size() > 0) {
451                 if (sbNodes.length() > 0) {
452                     sbNodes.append(" AND ");
453                 }
454                 appendField(sbNodes, step, "number", multipleSteps);
455                 if (nodes.size() > 1) {
456                     // only use IN(...) if there are really more numbers
457
sbNodes.append(" IN (");
458                     Iterator iNodes = nodes.iterator();
459                     while (iNodes.hasNext()) {
460                         Integer JavaDoc node = (Integer JavaDoc) iNodes.next();
461                         sbNodes.append(node);
462                         if (iNodes.hasNext()) {
463                             sbNodes.append(',');
464                         }
465                     }
466                     sbNodes.append(')');
467                 } else {
468                     // otherwise use equals, which is a LOT faster in some cases
469
sbNodes.append('=');
470                     sbNodes.append(nodes.first());
471                 }
472             }
473
474             // Relation steps.
475
if (step instanceof RelationStep){
476                 appendRelationConstraints(sbRelations, (RelationStep) step, multipleSteps);
477             }
478         }
479
480         // Constraints
481
StringBuffer JavaDoc sbConstraints = new StringBuffer JavaDoc();
482         sbConstraints.append(sbNodes); // Constraints by included nodes.
483
if (sbConstraints.length() > 0 && sbRelations.length() > 0) {
484             sbConstraints.append(" AND ");
485         }
486         sbConstraints.append(sbRelations); // Constraints by relations.
487
if (query.getConstraint() != null) {
488             Constraint constraint = query.getConstraint();
489             if (sbConstraints.length() > 0) {
490                 // Combine constraints.
491
sbConstraints.append(" AND ");
492                 if (constraint instanceof CompositeConstraint) {
493                     appendCompositeConstraintToSql(
494                     sbConstraints, (CompositeConstraint) constraint,
495                     query, false, true, firstInChain);
496                 } else {
497                     firstInChain.appendConstraintToSql(
498                     sbConstraints, constraint, query,
499                     false, true);
500                 }
501             } else {
502                 // Only regular constraints.
503
if (constraint instanceof CompositeConstraint) {
504                     appendCompositeConstraintToSql(
505                     sbConstraints, (CompositeConstraint) constraint,
506                     query, false, false, firstInChain);
507                 } else {
508                     firstInChain.appendConstraintToSql(
509                     sbConstraints, constraint, query,
510                     false, false);
511                 }
512             }
513         }
514         if (sbConstraints.length() > 0) {
515             sb.append(" WHERE ").
516             append(sbConstraints.toString());
517         }
518
519         // GROUP BY
520
if (sbGroups.length() > 0) {
521             sb.append(" GROUP BY ").
522             append(sbGroups.toString());
523         }
524
525         appendSortOrders(sb, query);
526     }
527
528
529     /**
530      * @param sb
531      * @param step
532      * @since MMBase-1.8
533      */

534     protected void appendTableName(StringBuffer JavaDoc sb, Step step) {
535         // Tablename, prefixed with basename and underscore
536
sb.append(MMBase.getMMBase().getBaseName()).
537         append('_').
538         //Currently no replacement strategy is implemented for
539
//invalid tablenames.
540
//This would be useful, but requires modification to
541
//the insert/update/delete code as well.
542
//append(getAllowedValue(tableName));
543
append(step.getTableName());
544
545         appendTableAlias(sb, step);
546     }
547
548     /**
549      * @since MMBase-1.8
550      */

551     protected void appendTableAlias(StringBuffer JavaDoc sb, Step step) {
552         String JavaDoc tableAlias = step.getAlias();
553         // Table alias (tablename when table alias not set).
554
if (tableAlias != null) {
555             sb.append(" ").
556                 append(getAllowedValue(tableAlias));
557         } else {
558             sb.append(" ").
559                 append(getAllowedValue(step.getTableName()));
560         }
561     }
562
563
564     /**
565      * @since MMBase-1.8
566      */

567     protected StringBuffer JavaDoc appendSortOrderDirection(StringBuffer JavaDoc sb, SortOrder sortOrder) throws IllegalStateException JavaDoc {
568         // Sort direction.
569
switch (sortOrder.getDirection()) {
570         case SortOrder.ORDER_ASCENDING:
571             sb.append(" ASC");
572             break;
573         case SortOrder.ORDER_DESCENDING:
574             sb.append(" DESC");
575             break;
576         default: // Invalid direction value.
577
throw new IllegalStateException JavaDoc("Invalid direction value: " + sortOrder.getDirection());
578         }
579         return sb;
580     }
581
582     /**
583      * @since MMBase-1.8
584      */

585     protected StringBuffer JavaDoc appendSortOrderField(StringBuffer JavaDoc sb, SortOrder sortOrder, boolean multipleSteps) {
586          boolean uppered = false;
587          if (! sortOrder.isCaseSensitive() && sortOrder.getField().getType() == Field.TYPE_STRING) {
588              sb.append("UPPER(");
589              uppered = true;
590          }
591          // Fieldname.
592
Step step = sortOrder.getField().getStep();
593          appendField(sb, step, sortOrder.getField().getFieldName(), multipleSteps);
594          if (uppered) {
595              sb.append(")");
596              appendSortOrderDirection(sb, sortOrder);
597              sb.append(",");
598              // also order by field itself, so ensure uniqueness.
599
appendField(sb, step, sortOrder.getField().getFieldName(), multipleSteps);
600          }
601          return sb;
602     }
603     /**
604      * @since MMBase-1.8.2
605      */

606     protected StringBuffer JavaDoc appendSortOrderField(StringBuffer JavaDoc sb, SortOrder sortOrder, boolean multipleSteps, SearchQuery query) {
607         return appendSortOrderField(sb, sortOrder, multipleSteps);
608     }
609
610     /**
611      * @since MMBase-1.8
612      */

613     protected StringBuffer JavaDoc appendSortOrders(StringBuffer JavaDoc sb, SearchQuery query) {
614         boolean multipleSteps = query.getSteps().size() > 1;
615         List sortOrders = query.getSortOrders();
616         if (sortOrders.size() > 0) {
617             sb.append(" ORDER BY ");
618             Iterator iSortOrders = sortOrders.iterator();
619             while (iSortOrders.hasNext()) {
620                 SortOrder sortOrder = (SortOrder) iSortOrders.next();
621                 appendSortOrderField(sb, sortOrder, multipleSteps, query);
622                 appendSortOrderDirection(sb, sortOrder);
623                 if (iSortOrders.hasNext()) {
624                     sb.append(",");
625                 }
626             }
627         }
628         return sb;
629     }
630
631     /**
632      * Appends the 'LIKE' operator for the given case sensitiviy. Some databases support a case
633      * insensitive LIKE ('ILIKE'). Implementations for those database can override this method.
634      *
635      * @return The string buffer.
636      */

637     protected StringBuffer JavaDoc appendLikeOperator(StringBuffer JavaDoc sb, boolean caseSensitive) {
638         sb.append(" LIKE ");
639         return sb;
640     }
641
642
643     /*
644     protected StringBuffer appendRegularExpressionOperator(StringBuffer sb, boolean caseSensitive) {
645         sb.append(" ~ ");
646         return sb;
647     }
648     */

649
650     /**
651      * @javadoc
652      */

653     protected void appendDateField(StringBuffer JavaDoc sb, Step step, String JavaDoc fieldName, boolean multipleSteps, int datePart) {
654         String JavaDoc datePartFunction = null;
655         switch (datePart) {
656         case -1:
657             break;
658         case FieldValueDateConstraint.YEAR:
659             datePartFunction = "YEAR";
660             break;
661         case FieldValueDateConstraint.MONTH:
662             datePartFunction = "MONTH";
663             break;
664         case FieldValueDateConstraint.DAY_OF_MONTH:
665             datePartFunction = "DAY";
666             break;
667         case FieldValueDateConstraint.HOUR:
668             datePartFunction = "HOUR";
669             break;
670         case FieldValueDateConstraint.MINUTE:
671             datePartFunction = "MINUTE";
672                 break;
673         case FieldValueDateConstraint.SECOND:
674             datePartFunction = "SECOND";
675             break;
676         default:
677             throw new UnsupportedOperationException JavaDoc("This date partition function (" + datePart + ") is not supported.");
678         }
679         if (datePartFunction != null) {
680             sb.append("EXTRACT(");
681             sb.append(datePartFunction);
682             sb.append(" FROM ");
683         }
684         appendField(sb, step, fieldName, multipleSteps);
685         if (datePartFunction != null) {
686             sb.append(")");
687         }
688     }
689
690     // javadoc is inherited
691
// XXX what exception to throw when an unsupported constraint is
692
// encountered (currently throws UnsupportedOperationException)?
693
public void appendConstraintToSql(StringBuffer JavaDoc sb, Constraint constraint,
694     SearchQuery query, boolean inverse, boolean inComposite) {
695
696         // Net effect of inverse setting with constraint inverse property.
697
boolean overallInverse = inverse ^ constraint.isInverse();
698
699         boolean multipleSteps = query.getSteps().size() > 1;
700
701         boolean toLowerCase = false;
702         if (constraint instanceof FieldConstraint) {
703
704             // Field constraint
705
FieldConstraint fieldConstraint = (FieldConstraint) constraint;
706             StepField field = fieldConstraint.getField();
707             int fieldType = field.getType();
708             String JavaDoc fieldName = field.getFieldName();
709             Step step = field.getStep();
710
711
712             // hardly nice and OO, the following code.
713
//
714
if (fieldConstraint instanceof FieldValueInConstraint) {
715
716                 // Field value-in constraint
717
FieldValueInConstraint valueInConstraint = (FieldValueInConstraint) fieldConstraint;
718                 SortedSet values = valueInConstraint.getValues();
719                 if (values.size() == 0) {
720                     throw new IllegalStateException JavaDoc(
721                     "Field value-in constraint specifies no values "
722                     + "(at least 1 value is required).");
723                 }
724                 if (isRelevantCaseInsensitive(fieldConstraint)) {
725                     // case insensitive
726
sb.append("LOWER(");
727                     appendField(sb, step, fieldName, multipleSteps);
728                     sb.append(")");
729                 } else {
730                     // case sensitive or case irrelevant
731
appendField(sb, step, fieldName, multipleSteps);
732                 }
733
734                 if (values.size() > 1) {
735                     // only use IN(...) if there are really more numbers
736
sb.append(overallInverse? " NOT IN (": " IN (");
737                     Iterator iValues = values.iterator();
738                     while (iValues.hasNext()) {
739                         Object JavaDoc value = iValues.next();
740                         appendFieldValue(sb, value,
741                             !fieldConstraint.isCaseSensitive(), fieldType);
742                         if (iValues.hasNext()) {
743                             sb.append(",");
744                         }
745                     }
746                     sb.append(")");
747                 } else {
748                     // otherwise use equals, which is a LOT faster in some cases
749
sb.append(overallInverse? "<>": "=");
750                     appendFieldValue(sb, values.first(),
751                         !fieldConstraint.isCaseSensitive(), fieldType);
752                 }
753
754             } else if (fieldConstraint instanceof FieldValueBetweenConstraint) {
755
756                 // Field value-between constraint
757
FieldValueBetweenConstraint valueBetweenConstraint = (FieldValueBetweenConstraint) fieldConstraint;
758                 if (isRelevantCaseInsensitive(fieldConstraint)) {
759                     // case insensitive
760
sb.append("LOWER(");
761                     appendField(sb, step, fieldName, multipleSteps);
762                     sb.append(")");
763                 } else {
764                     // case sensitive or case irrelevant
765
appendField(sb, step, fieldName, multipleSteps);
766                 }
767                 sb.append(overallInverse? " NOT BETWEEN ": " BETWEEN ");
768                 appendFieldValue(sb, valueBetweenConstraint.getLowerLimit(),
769                     !fieldConstraint.isCaseSensitive(), fieldType);
770                 sb.append(" AND ");
771                 appendFieldValue(sb, valueBetweenConstraint.getUpperLimit(),
772                     !fieldConstraint.isCaseSensitive(), fieldType);
773
774             } else if (fieldConstraint instanceof FieldNullConstraint) {
775
776                 // Field null constraint
777
appendField(sb, step, fieldName, multipleSteps);
778                 sb.append(overallInverse? " IS NOT NULL": " IS NULL");
779
780             } else if (fieldConstraint instanceof FieldCompareConstraint) {
781
782                 // Field compare constraint
783
FieldCompareConstraint fieldCompareConstraint = (FieldCompareConstraint) fieldConstraint;
784
785                 // Negate by leading NOT, unless it's a LIKE constraint,
786
// in which case NOT LIKE is used.
787
if (fieldCompareConstraint.getOperator() != FieldCompareConstraint.LIKE) {
788                     sb.append(overallInverse? "NOT (": "");
789                 }
790
791                 if (fieldConstraint instanceof FieldValueDateConstraint) {
792                     int part = ((FieldValueDateConstraint)fieldConstraint).getPart();
793                     appendDateField(sb, step, fieldName, multipleSteps, part);
794                 } else if (useLower(fieldCompareConstraint) && isRelevantCaseInsensitive(fieldConstraint)) {
795                     // case insensitive and database needs it
796
sb.append("LOWER(");
797                     appendField(sb, step, fieldName, multipleSteps);
798                     sb.append(")");
799                 } else {
800                     // case sensitive or case irrelevant
801
appendField(sb, step, fieldName, multipleSteps);
802                 }
803                 switch (fieldCompareConstraint.getOperator()) {
804                 case FieldCompareConstraint.LESS:
805                     sb.append("<");
806                     break;
807
808                 case FieldCompareConstraint.LESS_EQUAL:
809                     sb.append("<=");
810                     break;
811
812                 case FieldCompareConstraint.EQUAL:
813                     sb.append("=");
814                     break;
815
816                 case FieldCompareConstraint.NOT_EQUAL:
817                     sb.append("<>");
818                     break;
819
820                 case FieldCompareConstraint.GREATER:
821                     sb.append(">");
822                     break;
823
824                 case FieldCompareConstraint.GREATER_EQUAL:
825                     sb.append(">=");
826                     break;
827
828                 case FieldCompareConstraint.LIKE:
829                     if (overallInverse) {
830                         sb.append(" NOT");
831                     }
832                     appendLikeOperator(sb, fieldConstraint.isCaseSensitive());
833                     break;
834                     /*
835                 case FieldValueConstraint.REGEXP:
836                     sb.append(getRegularExpressionOperator());
837                     break;
838                     */

839                 default:
840                     throw new IllegalStateException JavaDoc("Unknown operator value in constraint: " + fieldCompareConstraint.getOperator());
841                 }
842                 if (fieldCompareConstraint instanceof FieldValueConstraint) {
843                     // FieldValueConstraint.
844
FieldValueConstraint fieldValueConstraint = (FieldValueConstraint) fieldCompareConstraint;
845                     Object JavaDoc value = fieldValueConstraint.getValue();
846                     appendFieldValue(sb, value, useLower(fieldValueConstraint) && isRelevantCaseInsensitive(fieldValueConstraint), fieldType);
847                 } else if (fieldCompareConstraint instanceof CompareFieldsConstraint) {
848                     // CompareFieldsConstraint
849
CompareFieldsConstraint compareFieldsConstraint = (CompareFieldsConstraint) fieldCompareConstraint;
850                     StepField field2 = compareFieldsConstraint.getField2();
851                     String JavaDoc fieldName2 = field2.getFieldName();
852                     Step step2 = field2.getStep();
853                     if (useLower(fieldCompareConstraint) && isRelevantCaseInsensitive(fieldConstraint)) {
854                         // case insensitive
855
sb.append("LOWER(");
856                         appendField(sb, step2, fieldName2, multipleSteps);
857                         sb.append(")");
858                     } else {
859                         // case sensitive or case irrelevant
860
appendField(sb, step2, fieldName2, multipleSteps);
861                     }
862                 } else {
863                     throw new UnsupportedOperationException JavaDoc("Unknown constraint type: " + constraint.getClass().getName());
864                 }
865                 // Negate by leading NOT, unless it's a LIKE constraint,
866
// in which case NOT LIKE is used.
867
if (fieldCompareConstraint.getOperator() != FieldCompareConstraint.LIKE) {
868                     sb.append(overallInverse? ")": "");
869                 }
870             } else {
871                 throw new UnsupportedOperationException JavaDoc("Unknown constraint type: " + constraint.getClass().getName());
872             }
873
874         } else if (constraint instanceof CompositeConstraint) {
875             throw new IllegalArgumentException JavaDoc("Illegal constraint type for this method: " + constraint.getClass().getName());
876         } else if (constraint instanceof LegacyConstraint) {
877             LegacyConstraint legacyConstraint = (LegacyConstraint) constraint;
878             if (legacyConstraint.getConstraint().trim().length() != 0) {
879                 if (overallInverse) {
880                     sb.append("NOT ");
881                 }
882                 if (overallInverse || inComposite) {
883                     sb.append("(");
884                 }
885                 sb.append(legacyConstraint.getConstraint());
886                 if (overallInverse || inComposite) {
887                     sb.append(")");
888                 }
889             }
890         } else {
891             throw new UnsupportedOperationException JavaDoc(
892             "Unknown constraint type: " + constraint.getClass().getName());
893         }
894     }
895
896     // javadoc is inherited
897
public int getSupportLevel(int feature, SearchQuery query)
898     throws SearchQueryException {
899         int result;
900         switch (feature) {
901             case SearchQueryHandler.FEATURE_MAX_NUMBER:
902                 if (query.getMaxNumber() == SearchQuery.DEFAULT_MAX_NUMBER){
903                     result = SearchQueryHandler.SUPPORT_OPTIMAL;
904                 } else {
905                     result = SearchQueryHandler.SUPPORT_NONE;
906                 }
907                 break;
908
909             case SearchQueryHandler.FEATURE_OFFSET:
910                 if (query.getOffset() == SearchQuery.DEFAULT_OFFSET) {
911                     result = SearchQueryHandler.SUPPORT_OPTIMAL;
912                 } else {
913                     result = SearchQueryHandler.SUPPORT_NONE;
914                 }
915                 break;
916
917             default:
918                 result = SearchQueryHandler.SUPPORT_NONE;
919         }
920         return result;
921     }
922
923     // javadoc is inherited
924
public int getSupportLevel(Constraint constraint, SearchQuery query)
925             throws SearchQueryException {
926         return constraint.getBasicSupportLevel();
927     }
928
929     // javadoc is inherited
930
public String JavaDoc getAllowedValue(String JavaDoc value) {
931         if (value == null) {
932             throw new IllegalArgumentException JavaDoc("Invalid value: " + value);
933         }
934         return (String JavaDoc) MMBase.getMMBase().getStorageManagerFactory().getStorageIdentifier(value);
935     }
936
937     /**
938      * Represents a CompositeConstraint object as a constraint in SQL format,
939      * appending the result to a stringbuffer.
940      * When it is part of a composite expression, it will be surrounded by
941      * parenthesis when needed.
942      *
943      * @param sb The stringbuffer to append to.
944      * @param compositeConstraint The composite constraint.
945      * @param query The searchquery containing the constraint.
946      * @param inverse True when the inverse constraint must be represented,
947      * false otherwise.
948      * @param inComposite True when the constraint is part of
949      * a composite expression.
950      * @param firstInChain The first element in the chain of handlers.
951      * At some point <code>appendConstraintToSql()</code> will have
952      * to be called on this handler, to generate the constraints in
953      * the composite.
954      */

955     protected void appendCompositeConstraintToSql(
956     StringBuffer JavaDoc sb, CompositeConstraint compositeConstraint, SearchQuery query,
957     boolean inverse, boolean inComposite, SqlHandler firstInChain)
958     throws SearchQueryException {
959
960         // Net effect of inverse setting with constraint inverse property.
961
boolean overallInverse = inverse ^ compositeConstraint.isInverse();
962
963         String JavaDoc strOperator = null;
964         if (compositeConstraint.getLogicalOperator() == CompositeConstraint.LOGICAL_AND) {
965             if (overallInverse) {
966                 // Inverse: NOT (A and B and C) = NOT A or NOT B or NOT C
967
strOperator = " OR ";
968             } else {
969                 strOperator = " AND ";
970             }
971         } else if (compositeConstraint.getLogicalOperator() == CompositeConstraint.LOGICAL_OR) {
972             if (overallInverse) {
973                 // Inverse: NOT (A or B or C) = NOT A and NOT B and NOT C
974
strOperator = " AND ";
975             } else {
976                 strOperator = " OR ";
977             }
978         } else {
979             throw new IllegalStateException JavaDoc(
980             "Invalid logical operator: " + compositeConstraint.getLogicalOperator()
981             + ", must be either "
982             + CompositeConstraint.LOGICAL_AND + " or " + CompositeConstraint.LOGICAL_OR);
983         }
984         List childs = compositeConstraint.getChilds();
985
986         // Test for at least 1 child.
987
if (childs.isEmpty()) {
988             throw new IllegalStateException JavaDoc(
989             "Composite constraint has no child (at least 1 child is required).");
990         }
991
992         boolean hasMultipleChilds = childs.size() > 1;
993
994         // Opening parenthesis, when part of composite expression
995
// and with multiple childs.
996
if (inComposite && hasMultipleChilds) {
997             sb.append("(");
998         }
999
1000        // Recursively append all childs.
1001
Iterator iChilds = childs.iterator();
1002        while (iChilds.hasNext()) {
1003            Constraint child = (Constraint) iChilds.next();
1004            if (child instanceof CompositeConstraint) {
1005                // Child is composite constraint.
1006
appendCompositeConstraintToSql(
1007                    sb, (CompositeConstraint) child, query,
1008                    overallInverse, hasMultipleChilds, firstInChain);
1009            } else {
1010                // Child is non-composite constraint.
1011
firstInChain.appendConstraintToSql(
1012                    sb, child, query, overallInverse, hasMultipleChilds);
1013            }
1014            if (iChilds.hasNext()) {
1015                sb.append(strOperator);
1016            }
1017        }
1018
1019        // Closing parenthesis, when part of composite expression
1020
// and with multiple childs.
1021
if (inComposite && hasMultipleChilds) {
1022            sb.append(")");
1023        }
1024    }
1025
1026    /**
1027     * Creates an identifier for a field, and appends it to a stringbuffer.
1028     * The identifier is constructed from the fieldname, optionally prefixed
1029     * by the tablename or the tablealias - when available.
1030     *
1031     * @param sb The stringbuffer to append to.
1032     * @param step The Step the field belongs to.
1033     * @param fieldName The fields fieldname.
1034     * @param includeTablePrefix <code>true</code> when the fieldname must be
1035     * prefixed with the tablename or tablealias (e.g. like in "images.number"),
1036     * <code>false</code> otherwise.
1037     */

1038    // TODO RvM: add to interface, add javadoc
1039
protected void appendField(StringBuffer JavaDoc sb, Step step,
1040            String JavaDoc fieldName, boolean includeTablePrefix) {
1041
1042        String JavaDoc tableAlias = step.getAlias();
1043        if (includeTablePrefix) {
1044            if (tableAlias != null) {
1045                sb.append(getAllowedValue(tableAlias));
1046            } else {
1047                sb.append(getAllowedValue(step.getTableName()));
1048            }
1049            sb.append(".");
1050        }
1051        sb.append(getAllowedValue(fieldName));
1052    }
1053
1054}
1055
Popular Tags