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.storage.search.implementation.database;
011:
012: import java.util.*;
013: import java.sql.*;
014: import java.lang.reflect.Method;
015: import org.mmbase.module.core.MMBase;
016:
017: import org.mmbase.storage.search.*;
018: import org.mmbase.util.logging.Logger;
019: import org.mmbase.util.logging.Logging;
020:
021: import org.mmbase.module.database.MultiConnection;
022:
023: /**
024: * The Informix query handler, implements {@link
025: * org.mmbase.storage.search.implementation.database.SqlHandler SqlHandler} for standard
026: * Informix functionality.
027: * <br />
028: * Derived from {@link BasicSqlHandler BasicSqlHandler}, overrides
029: * <ul>
030: * <li>{@link #toSql toSql()}, implements {@link
031: * org.mmbase.storage.search.SearchQueryHandler#FEATURE_MAX_NUMBER
032: * FEATURE_MAX_NUMBER}, by adding a construct like "<code>SELECT FIRST 20</code>"
033: * in front of the body, when appropriate.
034: * <li>{@link #getSupportLevel(int,SearchQuery) getSupportLevel(int,SearchQuery)},
035: * returns {@link
036: * org.mmbase.storage.search.SearchQueryHandler#SUPPORT_OPTIMAL
037: * SUPPORT_OPTIMAL} for this feature, delegates to the superclass for
038: * other features.
039: * </ul>
040: *
041: * @author Rob van Maris
042: * @version $Id: InformixSqlHandler.java,v 1.35 2007/09/18 11:40:42 michiel Exp $
043: * @since MMBase-1.7
044: */
045: public class InformixSqlHandler extends BasicSqlHandler implements
046: SqlHandler {
047:
048: public static final String ADD_ORDERED = "informix-query-optimizer-ordered";
049:
050: private static final Logger log = Logging
051: .getLoggerInstance(InformixSqlHandler.class);
052:
053: /**
054: * Constructor.
055: */
056: public InformixSqlHandler() {
057: super ();
058: }
059:
060: /**
061: * Dertermine if the given query will turn out to be a UNION query
062: *
063: * @param query
064: * @return <code>true</code> if the given query will contain a UNION
065: */
066: private boolean isUnionQuery(SearchQuery query) {
067: Iterator<Step> iSteps = query.getSteps().iterator();
068: while (iSteps.hasNext()) {
069: Step step = iSteps.next();
070: if (step instanceof RelationStep) {
071: RelationStep relationStep = (RelationStep) step;
072: // If the query contains RelationSteps that are bi-directional
073: // then the query will turn out to be a union query.
074: if (relationStep.getDirectionality() == RelationStep.DIRECTIONS_BOTH) {
075: return true;
076: }
077: }
078: }
079: return false;
080: }
081:
082: // javadoc is inherited
083: @Override
084: public int getSupportLevel(int feature, SearchQuery query)
085: throws SearchQueryException {
086: int result;
087: switch (feature) {
088: case SearchQueryHandler.FEATURE_MAX_NUMBER:
089:
090: if (isUnionQuery(query)) {
091: result = SearchQueryHandler.SUPPORT_NONE;
092: } else {
093: result = SearchQueryHandler.SUPPORT_OPTIMAL;
094: }
095: break;
096:
097: default:
098: result = super .getSupportLevel(feature, query);
099: }
100: return result;
101: }
102:
103: // javadoc is inherited
104: @Override
105: public String toSql(SearchQuery query, SqlHandler firstInChain)
106: throws SearchQueryException {
107: // XXX should table and field aliases be tested for uniqueness?
108:
109: // Test for at least 1 step and 1 field.
110: if (query.getSteps().isEmpty()) {
111: throw new IllegalStateException(
112: "Searchquery has no step (at least 1 step is required).");
113: }
114: if (query.getFields().isEmpty()) {
115: throw new IllegalStateException(
116: "Searchquery has no field (at least 1 field is required).");
117: }
118:
119: // Test offset set to default (= 0).
120: if (query.getOffset() != SearchQuery.DEFAULT_OFFSET) {
121: throw new UnsupportedOperationException(
122: "Value of offset other than "
123: + SearchQuery.DEFAULT_OFFSET
124: + " not supported.");
125: }
126:
127: // SELECT
128: StringBuilder sbQuery = new StringBuilder("SELECT ");
129:
130: if (log.isTraceEnabled()) {
131: log.trace("query:" + query.toString());
132: }
133:
134: if (!isUnionQuery(query)) {
135: /*
136: Optimizer directive {+ORDERED} may not be used when using UNIONS
137:
138: It is unclear why it is good to explicit add ORDERED tot the query.
139: I suspect it may not be definitely always good at all.
140: http://www.mmbase.org/jira/browse/MMB-1508
141: made it configurable for now
142:
143: */
144: if (MMBase.getMMBase().getStorageManagerFactory()
145: .hasOption(ADD_ORDERED)) {
146:
147: if (query.getSteps().size() > 3) {
148: sbQuery.append("{+ORDERED} ");
149: }
150: }
151: /*
152: FIRST may not be used when using UNIONS
153: */
154: if (query.getMaxNumber() != -1) {
155: sbQuery.append("FIRST ").append(query.getMaxNumber())
156: .append(" ");
157: }
158: }
159:
160: // DISTINCT
161: if (query.isDistinct() && !query.isAggregating()) {
162: sbQuery.append("DISTINCT ");
163: }
164:
165: firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
166:
167: return sbQuery.toString();
168: }
169:
170: // javadoc is inherited
171: @Override
172: public void appendQueryBodyToSql(StringBuilder sb,
173: SearchQuery query, SqlHandler firstInChain)
174: throws SearchQueryException {
175:
176: // Buffer expressions for included nodes, like
177: // "x.number in (...)".
178: StringBuilder sbNodes = new StringBuilder();
179:
180: // Buffer expressions for relations, like
181: // "x.number = r.snumber AND y.number = r.dnumber".
182: StringBuilder sbRelations = new StringBuilder();
183:
184: // Buffer fields to group by, like
185: // "alias1, alias2, ..."
186: StringBuilder sbGroups = new StringBuilder();
187:
188: boolean multipleSteps = query.getSteps().size() > 1;
189:
190: // Fields expression
191: List<StepField> lFields = new ArrayList<StepField>();
192: lFields.addAll(query.getFields());
193:
194: // When 'distinct', make sure all fields used for sorting are
195: // included in the query.
196: // Some databases require this (including PostgreSQL).
197: // By fixing this here, the result of the query remains consistent
198: // across databases, while requiring no modification in the calling
199: // code.
200: if (query.isDistinct()) {
201: Iterator<SortOrder> iSortOrder = query.getSortOrders()
202: .iterator();
203: while (iSortOrder.hasNext()) {
204: SortOrder sortOrder = iSortOrder.next();
205: StepField field = sortOrder.getField();
206: if (lFields.indexOf(field) == -1) {
207: lFields.add(field);
208: }
209: }
210: }
211:
212: Iterator<StepField> iFields = lFields.iterator();
213: boolean appended = false;
214: while (iFields.hasNext()) {
215: StepField field = iFields.next();
216: if (field.getType() == org.mmbase.bridge.Field.TYPE_BINARY)
217: continue;
218: if (appended) {
219: sb.append(',');
220: }
221: appended = true;
222:
223: // Fieldname prefixed by table alias.
224: Step step = field.getStep();
225: String fieldName = field.getFieldName();
226: String fieldAlias = field.getAlias();
227:
228: if (field instanceof AggregatedField) {
229: int aggregationType = ((AggregatedField) field)
230: .getAggregationType();
231: if (aggregationType == AggregatedField.AGGREGATION_TYPE_GROUP_BY) {
232:
233: // Group by.
234: appendField(sb, step, fieldName, multipleSteps);
235:
236: // Append to "GROUP BY"-buffer.
237: if (sbGroups.length() > 0) {
238: sbGroups.append(",");
239: }
240: if (fieldAlias != null) {
241: sbGroups.append(getAllowedValue(fieldAlias));
242: } else {
243: appendField(sbGroups, step, fieldName,
244: multipleSteps);
245: }
246: } else {
247:
248: // Aggregate function.
249: switch (aggregationType) {
250: case AggregatedField.AGGREGATION_TYPE_COUNT:
251: sb.append("COUNT(");
252: break;
253:
254: case AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT:
255: sb.append("COUNT(DISTINCT ");
256: break;
257:
258: case AggregatedField.AGGREGATION_TYPE_MIN:
259: sb.append("MIN(");
260: break;
261:
262: case AggregatedField.AGGREGATION_TYPE_MAX:
263: sb.append("MAX(");
264: break;
265:
266: default:
267: throw new IllegalStateException(
268: "Invalid aggregationType value: "
269: + aggregationType);
270: }
271: appendField(sb, step, fieldName, multipleSteps);
272: sb.append(")");
273: }
274:
275: } else {
276:
277: // Non-aggregate field.
278: appendField(sb, step, fieldName, multipleSteps);
279: }
280:
281: // Field alias.
282: if (fieldAlias != null) {
283: sb.append(" AS ").append(getAllowedValue(fieldAlias));
284: }
285:
286: }
287:
288: if (log.isDebugEnabled()) {
289: log.trace("Base field part of query : " + sb);
290: }
291:
292: // vector to save OR-Elements (Searchdir=BOTH) for migration to UNION-query
293: List<StringBuilder> orElements = new ArrayList<StringBuilder>();
294:
295: // save AND-Elements from relationString for migration to UNION-query
296: StringBuilder andElements = new StringBuilder();
297:
298: // Tables
299: sb.append(" FROM ");
300: Iterator<Step> iSteps = query.getSteps().iterator();
301: while (iSteps.hasNext()) {
302: Step step = iSteps.next();
303:
304: appendTableName(sb, step);
305:
306: if (iSteps.hasNext()) {
307: sb.append(",");
308: }
309:
310: // Included nodes.
311: SortedSet<Integer> nodes = step.getNodes();
312: if (nodes.size() > 0) {
313: if (sbNodes.length() > 0) {
314: sbNodes.append(" AND ");
315: }
316: appendField(sbNodes, step, "number", multipleSteps);
317: sbNodes.append(" IN (");
318: Iterator<Integer> iNodes = nodes.iterator();
319: while (iNodes.hasNext()) {
320: Integer node = iNodes.next();
321: sbNodes.append(node);
322: if (iNodes.hasNext()) {
323: sbNodes.append(",");
324: }
325: }
326: sbNodes.append(")");
327: }
328: if (log.isDebugEnabled()) {
329: log.trace("Node constraint string : " + sbNodes);
330: }
331:
332: // Relation steps.
333: if (step instanceof RelationStep) {
334: RelationStep relationStep = (RelationStep) step;
335: Step previousStep = relationStep.getPrevious();
336: Step nextStep = relationStep.getNext();
337: if (sbRelations.length() > 0) {
338: sbRelations.append(" AND ");
339: }
340: switch (relationStep.getDirectionality()) {
341: case RelationStep.DIRECTIONS_SOURCE:
342: sbRelations.append("(");
343: appendField(sbRelations, previousStep, "number",
344: multipleSteps);
345: sbRelations.append("=");
346: appendField(sbRelations, relationStep, "dnumber",
347: multipleSteps);
348: sbRelations.append(" AND ");
349: appendField(sbRelations, nextStep, "number",
350: multipleSteps);
351: sbRelations.append("=");
352: appendField(sbRelations, relationStep, "snumber",
353: multipleSteps);
354: if (relationStep.getCheckedDirectionality()) {
355: sbRelations.append(" AND ");
356: appendField(sbRelations, relationStep, "dir",
357: multipleSteps);
358: sbRelations.append("<>1");
359: }
360:
361: // Gather the "And"-elements
362: if (andElements.length() > 0) {
363: andElements.append(" AND ");
364: }
365: appendField(andElements, previousStep, "number",
366: multipleSteps);
367: andElements.append("=");
368: appendField(andElements, relationStep, "dnumber",
369: multipleSteps);
370: andElements.append(" AND ");
371: appendField(andElements, nextStep, "number",
372: multipleSteps);
373: andElements.append("=");
374: appendField(andElements, relationStep, "snumber",
375: multipleSteps);
376: if (relationStep.getCheckedDirectionality()) {
377: andElements.append(" AND ");
378: appendField(andElements, relationStep, "dir",
379: multipleSteps);
380: andElements.append("<>1");
381: }
382:
383: break;
384:
385: case RelationStep.DIRECTIONS_DESTINATION:
386: sbRelations.append("(");
387: appendField(sbRelations, previousStep, "number",
388: multipleSteps);
389: sbRelations.append("=");
390: appendField(sbRelations, relationStep, "snumber",
391: multipleSteps);
392: sbRelations.append(" AND ");
393: appendField(sbRelations, nextStep, "number",
394: multipleSteps);
395: sbRelations.append("=");
396: appendField(sbRelations, relationStep, "dnumber",
397: multipleSteps);
398:
399: // Gather the "And"-elements
400: if (andElements.length() > 0) {
401: andElements.append(" AND ");
402: }
403:
404: appendField(andElements, previousStep, "number",
405: multipleSteps);
406: andElements.append("=");
407: appendField(andElements, relationStep, "snumber",
408: multipleSteps);
409: andElements.append(" AND ");
410: appendField(andElements, nextStep, "number",
411: multipleSteps);
412: andElements.append("=");
413: appendField(andElements, relationStep, "dnumber",
414: multipleSteps);
415:
416: break;
417:
418: case RelationStep.DIRECTIONS_BOTH:
419:
420: if (relationStep.getRole() != null) {
421: sbRelations.append("(((");
422: } else {
423: sbRelations.append("((");
424: }
425:
426: appendField(sbRelations, previousStep, "number",
427: multipleSteps);
428: sbRelations.append("=");
429: appendField(sbRelations, relationStep, "dnumber",
430: multipleSteps);
431: sbRelations.append(" AND ");
432: appendField(sbRelations, nextStep, "number",
433: multipleSteps);
434: sbRelations.append("=");
435: appendField(sbRelations, relationStep, "snumber",
436: multipleSteps);
437: if (relationStep.getCheckedDirectionality()) {
438: sbRelations.append(" AND ");
439: appendField(sbRelations, relationStep, "dir",
440: multipleSteps);
441: sbRelations.append("<>1");
442: }
443: sbRelations.append(") OR (");
444:
445: appendField(sbRelations, previousStep, "number",
446: multipleSteps);
447: sbRelations.append("=");
448: appendField(sbRelations, relationStep, "snumber",
449: multipleSteps);
450: sbRelations.append(" AND ");
451: appendField(sbRelations, nextStep, "number",
452: multipleSteps);
453: sbRelations.append("=");
454: appendField(sbRelations, relationStep, "dnumber",
455: multipleSteps);
456: if (relationStep.getRole() != null) {
457: sbRelations.append("))");
458: } else {
459: sbRelations.append(")");
460: }
461:
462: // Gather al the OR- elements for the union
463: // start of First element
464: StringBuilder orElement = new StringBuilder();
465: orElement.append("(");
466:
467: appendField(orElement, previousStep, "number",
468: multipleSteps);
469: orElement.append("=");
470: appendField(orElement, relationStep, "dnumber",
471: multipleSteps);
472: orElement.append(" AND ");
473: appendField(orElement, nextStep, "number",
474: multipleSteps);
475: orElement.append("=");
476: appendField(orElement, relationStep, "snumber",
477: multipleSteps);
478: if (relationStep.getCheckedDirectionality()) {
479: orElement.append(" AND ");
480: appendField(orElement, relationStep, "dir",
481: multipleSteps);
482: orElement.append("<>1");
483: }
484:
485: if (relationStep.getRole() != null) {
486: // role is given lets add the rnumber
487: orElement.append(" AND ");
488: appendField(orElement, relationStep, "rnumber",
489: multipleSteps);
490: orElement.append("=").append(
491: relationStep.getRole());
492: }
493:
494: orElement.append(")");
495: orElements.add(orElement);
496:
497: // Start of second element
498: orElement = new StringBuilder();
499: orElement.append("(");
500:
501: appendField(orElement, previousStep, "number",
502: multipleSteps);
503: orElement.append("=");
504: appendField(orElement, relationStep, "snumber",
505: multipleSteps);
506: orElement.append(" AND ");
507: appendField(orElement, nextStep, "number",
508: multipleSteps);
509: orElement.append("=");
510: appendField(orElement, relationStep, "dnumber",
511: multipleSteps);
512:
513: if (relationStep.getRole() != null) {
514: // ooops role is given lets add the rnumber
515: orElement.append(" AND ");
516: appendField(orElement, relationStep, "rnumber",
517: multipleSteps);
518: orElement.append("=").append(
519: relationStep.getRole());
520: }
521:
522: orElement.append(")");
523: orElements.add(orElement);
524:
525: // end of hacking
526: break;
527:
528: case RelationStep.DIRECTIONS_ALL:
529: throw new UnsupportedOperationException(
530: "Directionality 'ALL' is not (yet) supported");
531:
532: case RelationStep.DIRECTIONS_EITHER:
533: throw new UnsupportedOperationException(
534: "Directionality 'EITHER' is not (yet) supported");
535:
536: default: // Invalid directionality value.
537: throw new IllegalStateException(
538: "Invalid directionality value: "
539: + relationStep.getDirectionality());
540: }
541: if (relationStep.getRole() != null) {
542: sbRelations.append(" AND ");
543: appendField(sbRelations, relationStep, "rnumber",
544: multipleSteps);
545: sbRelations.append("=").append(
546: relationStep.getRole());
547: }
548: sbRelations.append(")");
549: }
550: }
551:
552: if (log.isDebugEnabled()) {
553: log.trace("Relation string : " + sbRelations);
554: }
555:
556: // Constraints
557: StringBuilder sbConstraints = new StringBuilder();
558: sbConstraints.append(sbNodes); // Constraints by included nodes.
559: if (sbConstraints.length() > 0 && sbRelations.length() > 0) {
560: sbConstraints.append(" AND ");
561: }
562:
563: /*
564: If there are relational Constraints in both directions
565: we need to
566: 1) figure out the contraints that we use in each of the UNION-constraints
567: 2) combine possible different OR-Elements
568: 3) create the query with all the UNIONS
569: 4) add the GROUP BY Clause
570: 5) add the ORDER BY Clause
571: */
572: StringBuilder unionRelationConstraints = new StringBuilder();
573: if (isUnionQuery(query)) {
574: // 1)
575: // we first need to figure out the additional constraints in
576: // order to add them to the relational constraints
577: StringBuilder unionConstraints = new StringBuilder();
578: if (query.getConstraint() != null) {
579: Constraint constraint = query.getConstraint();
580: if (sbConstraints.length() > 0) {
581: // Combine constraints.
582: // if sbConstraints allready ends with " AND " before adding " AND "
583: if (log.isDebugEnabled()) {
584: log.debug("sbConstraints:" + sbConstraints);
585: log.debug("sbConstraints.length:"
586: + sbConstraints.length());
587: }
588:
589: // have to check if the constraint end with "AND ", sometimes it's not :-(
590: if (sbConstraints.length() >= 4) {
591: if (!sbConstraints.substring(
592: sbConstraints.length() - 4,
593: sbConstraints.length()).equals("AND ")) {
594: unionConstraints.append(" AND ");
595: }
596: }
597:
598: if (constraint instanceof CompositeConstraint) {
599: appendCompositeConstraintToSql(
600: unionConstraints,
601: (CompositeConstraint) constraint,
602: query, false, true, firstInChain);
603: } else {
604: firstInChain.appendConstraintToSql(
605: unionConstraints, constraint, query,
606: false, true);
607: }
608: } else {
609: // Only regular constraints.
610: if (constraint instanceof CompositeConstraint) {
611: appendCompositeConstraintToSql(
612: unionConstraints,
613: (CompositeConstraint) constraint,
614: query, false, false, firstInChain);
615: } else {
616: firstInChain.appendConstraintToSql(
617: unionConstraints, constraint, query,
618: false, false);
619: }
620: }
621: }
622:
623: if (log.isDebugEnabled()) {
624: log.trace("Union constraint : " + unionConstraints);
625: }
626:
627: /*
628: 2) Combine the OR-Elements
629: Nested Looping through the OR-Elements to make unique combinations
630: */
631:
632: if (unionConstraints.length() > 0) {
633: unionConstraints.insert(0, " AND ");
634: }
635:
636: List<String> combinedElements = new ArrayList<String>();
637: boolean skipCombination = false;
638: for (int counter = 0; counter < orElements.size(); counter++) {
639: for (int counter2 = counter; counter2 < orElements
640: .size(); counter2++) {
641: // determine if the combination is valid and may be added to the relational constraints
642: if (counter % 2 == 0 && counter2 - counter == 1) {
643: // this combination may not be added
644: skipCombination = true;
645: } else {
646: skipCombination = false;
647: }
648: // Don't combine with same element. That doesn't make sense
649: // If there are just two relation-constraint-elements, we don't need to combine
650: if (counter != counter2 && orElements.size() > 2) {
651: // also add the additinal constraints
652: if (!skipCombination) {
653: combinedElements.add(orElements
654: .get(counter)
655: + " AND "
656: + orElements.get(counter2)
657: + unionConstraints);
658: }
659: } else {
660: // If there's just one OR (two OR-elements), add the elements seperately
661: // also add the additinal constraints
662: if (counter == counter2
663: && orElements.size() <= 2) {
664: combinedElements.add(orElements
665: .get(counter)
666: + "" + unionConstraints);
667: }
668: }
669: }
670: }
671:
672: /*
673: 3) Here we're going to create a new BaseQuery,
674: we need that in order to re-use that for every union
675: */
676: int teller = 1;
677:
678: // New base-query. We need to reuse that for every union
679: StringBuilder baseQuery = new StringBuilder();
680: baseQuery.append(sb); // add "Select ... From ..."
681:
682: // add the constraints
683: if (sbConstraints.length() + combinedElements.size() > 0) {
684: baseQuery.append(" WHERE ").append(
685: sbConstraints.toString());
686: }
687:
688: if (log.isDebugEnabled()) {
689: log.trace("Base query including fields and tables : "
690: + sb);
691: }
692:
693: // now add the combined relation-constraints as UNIONS
694: Iterator<String> e = combinedElements.iterator();
695: while (e.hasNext()) {
696: String combinedElement = e.next();
697: if (teller != 1) {
698: if (sb.indexOf("COUNT") > -1) {
699: unionRelationConstraints.append(" UNION ALL ")
700: .append(baseQuery);
701: } else {
702: unionRelationConstraints.append(" UNION ")
703: .append(baseQuery);
704: }
705: }
706:
707: // Make sure the unionRelationConstraint ends with " AND " or a " WHERE"
708: if (unionRelationConstraints.length() >= 4) {
709: if (!unionRelationConstraints.substring(
710: unionRelationConstraints.length() - 4,
711: unionRelationConstraints.length()).equals(
712: "AND ")
713: && !unionRelationConstraints
714: .substring(
715: unionRelationConstraints
716: .length() - 6,
717: unionRelationConstraints
718: .length()).equals(
719: "WHERE ")) {
720: unionRelationConstraints.append(" AND ");
721: }
722: }
723:
724: if (andElements.length() > 0) {
725: unionRelationConstraints.append(andElements)
726: .append(" AND ");
727: }
728:
729: unionRelationConstraints.append(" " + combinedElement
730: + " ");
731:
732: if (log.isDebugEnabled()) {
733: log.trace("Union relation constraint " + teller
734: + " : " + unionRelationConstraints);
735: }
736: teller++;
737: }
738:
739: // add the stuff to sb
740: if (sbConstraints.length() > 0) {
741: sb.append(" WHERE ").append(sbConstraints.toString())
742: .append(unionRelationConstraints);
743: } else {
744: sb.append(" WHERE ").append(unionRelationConstraints);
745: }
746:
747: /*
748: 4) add GROUP BY Clause
749: */
750: /* ToDo: implement group by
751:
752: if (sbGroups.length() > 0) {
753: sb.append(" GROUP BY ").
754: append(sbGroups.toString());
755: }
756: */
757:
758: /*
759: 5) adding ORDER BY
760: eg. ORDER BY 1,3,4
761: */
762: List<SortOrder> sortOrders = query.getSortOrders();
763: if (sortOrders.size() > 0) {
764: sb.append(" ORDER BY ");
765: Iterator<SortOrder> iSortOrders = sortOrders.iterator();
766: while (iSortOrders.hasNext()) {
767: SortOrder sortOrder = iSortOrders.next();
768:
769: // Field alias.
770: String fieldAlias = sortOrder.getField().getAlias();
771: Step step = sortOrder.getField().getStep();
772: StringBuilder orderByField = new StringBuilder();
773: if (fieldAlias != null) {
774: orderByField
775: .append(getAllowedValue(fieldAlias));
776: } else {
777: appendField(orderByField, step, sortOrder
778: .getField().getFieldName(),
779: multipleSteps);
780: }
781:
782: // Loop through the fields until we find a match
783: boolean found = false;
784: for (int i = 0; i < query.getFields().size(); i++) {
785: StepField sf = query.getFields().get(i);
786: String field = sf.getStep().getAlias() + "."
787: + sf.getFieldName();
788:
789: // search for the matching field to obtain the number of the field
790: if (field.equals(orderByField.toString())) {
791: // match found
792: sb.append((i + 1) + " ");
793: // prevent that the field is listed twice in this order-by
794: found = true;
795: break;
796: }
797: }
798: if (!found) {
799: throw new RuntimeException(
800: "Could not find the field "
801: + orderByField + " in "
802: + query.getFields() + " !");
803: }
804:
805: // Sort direction.
806: switch (sortOrder.getDirection()) {
807: case SortOrder.ORDER_ASCENDING:
808: sb.append(" ASC");
809: break;
810:
811: case SortOrder.ORDER_DESCENDING:
812: sb.append(" DESC");
813: break;
814:
815: default: // Invalid direction value.
816: throw new IllegalStateException(
817: "Invalid direction value: "
818: + sortOrder.getDirection());
819: }
820:
821: if (iSortOrders.hasNext()) {
822: sb.append(",");
823: }
824: }
825: }
826: log.debug("Completed generation of UNION query:"
827: + sb.toString());
828: } else {
829: sbConstraints.append(sbRelations); // Constraints by relations.
830: if (query.getConstraint() != null) {
831: Constraint constraint = query.getConstraint();
832: if (sbConstraints.length() > 0) {
833: // Combine constraints.
834: sbConstraints.append(" AND ");
835: if (constraint instanceof CompositeConstraint) {
836: appendCompositeConstraintToSql(sbConstraints,
837: (CompositeConstraint) constraint,
838: query, false, true, firstInChain);
839: } else {
840: firstInChain.appendConstraintToSql(
841: sbConstraints, constraint, query,
842: false, true);
843: }
844: } else {
845: // Only regular constraints.
846: if (constraint instanceof CompositeConstraint) {
847: appendCompositeConstraintToSql(sbConstraints,
848: (CompositeConstraint) constraint,
849: query, false, false, firstInChain);
850: } else {
851: firstInChain.appendConstraintToSql(
852: sbConstraints, constraint, query,
853: false, false);
854: }
855: }
856: }
857: if (sbConstraints.length() > 0) {
858: sb.append(" WHERE ").append(sbConstraints.toString());
859: }
860:
861: // GROUP BY
862: if (sbGroups.length() > 0) {
863: sb.append(" GROUP BY ").append(sbGroups.toString());
864: }
865:
866: // ORDER BY
867: List<SortOrder> sortOrders = query.getSortOrders();
868: if (sortOrders.size() > 0) {
869: sb.append(" ORDER BY ");
870: Iterator<SortOrder> iSortOrders = sortOrders.iterator();
871: while (iSortOrders.hasNext()) {
872: SortOrder sortOrder = iSortOrders.next();
873:
874: // Field alias.
875: String fieldAlias = sortOrder.getField().getAlias();
876: Step step = sortOrder.getField().getStep();
877: if (fieldAlias != null) {
878: sb.append(getAllowedValue(fieldAlias));
879: } else {
880: appendField(sb, step, sortOrder.getField()
881: .getFieldName(), multipleSteps);
882: }
883:
884: // Sort direction.
885: switch (sortOrder.getDirection()) {
886: case SortOrder.ORDER_ASCENDING:
887: sb.append(" ASC");
888: break;
889:
890: case SortOrder.ORDER_DESCENDING:
891: sb.append(" DESC");
892: break;
893:
894: default: // Invalid direction value.
895: throw new IllegalStateException(
896: "Invalid direction value: "
897: + sortOrder.getDirection());
898: }
899:
900: if (iSortOrders.hasNext()) {
901: sb.append(",");
902: }
903: }
904: }
905: log.debug("Completed generation of query:" + sb.toString());
906: }
907: }
908:
909: /**
910: * Safely close a database connection and/or a database statement.
911: * @param con The connection to close. Can be <code>null</code>.
912: * @param stmt The statement to close, prior to closing the connection. Can be <code>null</code>.
913: */
914: protected void closeConnection(Connection con, Statement stmt) {
915: try {
916: if (stmt != null) {
917: stmt.close();
918: }
919: } catch (Exception g) {
920: }
921: try {
922: if (con != null) {
923: if (con instanceof MultiConnection) {
924: closeInformix((MultiConnection) con);
925: }
926: con.close();
927: }
928: } catch (Exception g) {
929: }
930: }
931:
932: private void closeInformix(MultiConnection activeConnection) {
933: Connection con = activeConnection.unwrap(Connection.class);
934: try {
935: Method scrub = Class.forName(
936: "com.informix.jdbc.IfxConnection").getMethod(
937: "scrubConnection");
938: scrub.invoke(con);
939: } catch (Exception e) {
940: log.error("Exception while calling releaseBlob(): "
941: + e.getMessage());
942: }
943: }
944:
945: }
|