0001: /*
0002:
0003: This software is OSI Certified Open Source Software.
0004: OSI Certified is a certification mark of the Open Source Initiative.
0005:
0006: The license (Mozilla version 1.0) can be read at the MMBase site.
0007: See http://www.MMBase.org/license
0008:
0009: */
0010: package org.mmbase.storage.search.implementation.database;
0011:
0012: import org.mmbase.storage.implementation.database.*;
0013: import org.mmbase.bridge.Field;
0014: import org.mmbase.module.core.MMBase;
0015: import org.mmbase.storage.search.*;
0016: import org.mmbase.util.logging.*;
0017: import java.util.*;
0018: import java.text.DateFormat;
0019: import java.text.SimpleDateFormat;
0020: import java.text.FieldPosition;
0021:
0022: /**
0023: * Basic implementation.
0024: *
0025: * @author Rob van Maris
0026: * @version $Id: BasicSqlHandler.java,v 1.74 2007/09/05 08:12:13 michiel Exp $
0027: * @since MMBase-1.7
0028: */
0029:
0030: public class BasicSqlHandler implements SqlHandler {
0031:
0032: private static final Logger log = Logging
0033: .getLoggerInstance(BasicSqlHandler.class);
0034:
0035: private static final SimpleDateFormat dateFormat = new SimpleDateFormat(
0036: "yyyy-MM-dd HH:mm:ss.SSS");
0037: private static final FieldPosition dontcareFieldPosition = new FieldPosition(
0038: DateFormat.YEAR_FIELD);
0039:
0040: /**
0041: * Constructor.
0042: */
0043: public BasicSqlHandler() {
0044: }
0045:
0046: /**
0047: * Utility method, modifies strings for use in SQL statements.
0048: * This amounts to replacing all single quotes by two single quotes.
0049: *
0050: * @param str The input string.
0051: * @return The modified string.
0052: */
0053: // XXX must wildcard characters be escaped?
0054: // XXX perhaps place this somewhere else?
0055: protected String toSqlString(String str) {
0056: String result = str;
0057: if (str != null) {
0058: int offset = str.indexOf('\'');
0059: if (offset != -1) {
0060: // At least one single quote found.
0061: StringBuilder sb = new StringBuilder(str.length() + 4);
0062: int start = 0;
0063: do {
0064: sb.append(str.substring(start, offset))
0065: .append("''");
0066: start = offset + 1;
0067: offset = str.indexOf('\'', start);
0068: } while (offset != -1);
0069: sb.append(str.substring(start, str.length()));
0070: result = sb.toString();
0071: if (log.isDebugEnabled()) {
0072: log.debug("converted string \"" + str + "\" to \""
0073: + result + "\"");
0074: }
0075: }
0076: }
0077: return forceEncode(result);
0078: }
0079:
0080: public static String forceEncode(String st) {
0081: DatabaseStorageManagerFactory factory = (DatabaseStorageManagerFactory) MMBase
0082: .getMMBase().getStorageManagerFactory();
0083: if (factory.hasOption(Attributes.FORCE_ENCODE_TEXT)) {
0084: String encoding = factory.getMMBase().getEncoding();
0085: byte[] rawchars = null;
0086: try {
0087: if (encoding.equalsIgnoreCase("ISO-8859-1")
0088: && factory.hasOption(Attributes.LIE_CP1252)) {
0089: encoding = "CP1252";
0090: } else {
0091: }
0092: rawchars = st.getBytes(encoding);
0093: return new String(rawchars, "ISO-8859-1");
0094: } catch (Exception e) {
0095: return st;
0096: }
0097:
0098: } else {
0099: return st;
0100: }
0101: }
0102:
0103: /**
0104: * Tests if a case sensitivity for a field constraint is false
0105: * and relevant, i.e. the constraint is set to case insensitive and
0106: * the field has string type.
0107: *
0108: * @param constraint The constraint.
0109: * @return true if the constraint is set to case insensitive
0110: * and the field has string type, false otherwise.
0111: */
0112: private static boolean isRelevantCaseInsensitive(
0113: FieldConstraint constraint) {
0114: return !constraint.isCaseSensitive()
0115: && (constraint.getField().getType() == Field.TYPE_STRING || constraint
0116: .getField().getType() == Field.TYPE_XML);
0117: }
0118:
0119: /**
0120: * Wether the 'LOWER' function needs to be used to implement case insensitivity. This is
0121: * not always the case, because some database only match case insensitively, in which case it
0122: * does not make sense to lowercase.
0123: */
0124: protected boolean useLower(FieldCompareConstraint constraint) {
0125: return true;
0126: }
0127:
0128: protected void appendDateValue(StringBuilder sb, Date value) {
0129: int timeZoneOffset = MMBase.getMMBase()
0130: .getStorageManagerFactory().getTimeZoneOffset(
0131: value.getTime());
0132: Date date = new Date(value.getTime() - timeZoneOffset);
0133: //Date date = new Date(value.getTime());
0134: //log.debug("Using offset " + timeZoneOffset + " " + value + " -> " + date);
0135: sb.append(dateFormat.format(date, new StringBuffer(),
0136: dontcareFieldPosition));
0137: }
0138:
0139: /**
0140: * Represents field value as a string, appending the result to a
0141: * stringbuffer.
0142: * <p>
0143: * Depending on the fieldType:
0144: * <ul>
0145: * <li> String values are converted to SQL-formatted string,
0146: * surrounded by single quotes,
0147: * <li>Numerical values are represented as integer (integral values)
0148: * or floating point.
0149: * </ul>
0150: *
0151: * @param sb The stringbuffer to append to.
0152: * @param value The field value.
0153: * @param toLowerCase True when <code>String</code> must be converted to
0154: * lower case.
0155: * @param fieldType The field type.
0156: */
0157: // TODO: elaborate javadoc, add to SqlHandler interface?
0158: public void appendFieldValue(StringBuilder sb, Object value,
0159: boolean toLowerCase, int fieldType) {
0160: if (fieldType == Field.TYPE_STRING
0161: || fieldType == Field.TYPE_XML) {
0162: // escape single quotes in string
0163: String stringValue = toSqlString((String) value);
0164: // to lowercase when case insensitive
0165: if (toLowerCase) {
0166: stringValue = stringValue.toLowerCase();
0167: }
0168: sb.append("'").append(stringValue).append("'");
0169: } else if (fieldType == Field.TYPE_DATETIME) {
0170: // should this not be translated to a date first??
0171: if (value instanceof Number) {
0172: sb.append(((Number) value).longValue());
0173: } else {
0174: sb.append("'");
0175: appendDateValue(sb, (Date) value);
0176: sb.append("'");
0177: }
0178: } else if (fieldType == Field.TYPE_BOOLEAN) {
0179: boolean isTrue = ((Boolean) value).booleanValue();
0180: if (isTrue) {
0181: sb.append("TRUE");
0182: } else {
0183: sb.append("FALSE");
0184: }
0185: } else {
0186: // Numerical field:
0187: // represent integeral Number values as integer, other
0188: // Number values as floating point, and String values as-is.
0189: if (value instanceof Number) {
0190: Number numberValue = (Number) value;
0191: if (numberValue.doubleValue() == numberValue.intValue()) {
0192: // Integral Number value.
0193: sb.append(numberValue.intValue());
0194: } else {
0195: // Non-integral Number value.
0196: sb.append(numberValue.doubleValue());
0197: }
0198: } else if (value instanceof Date) {
0199: // mainly legacy (integers in db, datetime in build xmls)
0200: sb.append(((Date) value).getTime() / 1000);
0201: } else {
0202: // String value.
0203: sb.append((String) value);
0204: }
0205: }
0206: }
0207:
0208: // javadoc is inherited
0209: // XXX what exception to throw when an unsupported feature is
0210: // encountered (currently throws UnsupportedOperationException)?
0211: public String toSql(SearchQuery query, SqlHandler firstInChain)
0212: throws SearchQueryException {
0213: // XXX should table and field aliases be tested for uniqueness?
0214:
0215: // Test for at least 1 step and 1 field.
0216: if (query.getSteps().isEmpty()) {
0217: throw new IllegalStateException(
0218: "Searchquery has no step (at least 1 step is required).");
0219: }
0220: if (query.getFields().isEmpty()) {
0221: throw new IllegalStateException(
0222: "Searchquery has no field (at least 1 field is required).");
0223: }
0224:
0225: // SELECT
0226: StringBuilder sbQuery = new StringBuilder("SELECT ");
0227:
0228: // DISTINCT
0229: // Note that DISTINCT can be omitted for an aggregating query.
0230: // It is ommitted to make the resulting SQL more portable,
0231: // some databases will otherwise report a syntax error (e.g. Informix).
0232: if (query.isDistinct() && !query.isAggregating()) {
0233: sbQuery.append("DISTINCT ");
0234: }
0235:
0236: firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
0237:
0238: String strSQL = sbQuery.toString();
0239: if (log.isDebugEnabled()) {
0240: log.debug("generated SQL: " + strSQL);
0241: }
0242: return strSQL;
0243: }
0244:
0245: /**
0246: * @since MMBase-1.8
0247: */
0248: protected void appendRelationConstraints(StringBuilder sbRelations,
0249: RelationStep relationStep, boolean multipleSteps) {
0250:
0251: Step previousStep = relationStep.getPrevious();
0252: Step nextStep = relationStep.getNext();
0253: if (sbRelations.length() > 0) {
0254: sbRelations.append(" AND ");
0255: }
0256: switch (relationStep.getDirectionality()) {
0257: case RelationStep.DIRECTIONS_SOURCE:
0258: sbRelations.append('(');
0259: appendField(sbRelations, previousStep, "number",
0260: multipleSteps);
0261: sbRelations.append('=');
0262: appendField(sbRelations, relationStep, "dnumber",
0263: multipleSteps);
0264: sbRelations.append(" AND ");
0265: appendField(sbRelations, nextStep, "number", multipleSteps);
0266: sbRelations.append('=');
0267: appendField(sbRelations, relationStep, "snumber",
0268: multipleSteps);
0269: if (relationStep.getCheckedDirectionality()) {
0270: sbRelations.append(" AND ");
0271: appendField(sbRelations, relationStep, "dir",
0272: multipleSteps);
0273: sbRelations.append("<>1");
0274: }
0275: break;
0276:
0277: case RelationStep.DIRECTIONS_DESTINATION:
0278: sbRelations.append('(');
0279: appendField(sbRelations, previousStep, "number",
0280: multipleSteps);
0281: sbRelations.append('=');
0282: appendField(sbRelations, relationStep, "snumber",
0283: multipleSteps);
0284: sbRelations.append(" AND ");
0285: appendField(sbRelations, nextStep, "number", multipleSteps);
0286: sbRelations.append('=');
0287: appendField(sbRelations, relationStep, "dnumber",
0288: multipleSteps);
0289: break;
0290:
0291: case RelationStep.DIRECTIONS_BOTH:
0292: if (relationStep.getRole() != null) {
0293: sbRelations.append("(((");
0294: } else {
0295: sbRelations.append("((");
0296: }
0297: appendField(sbRelations, previousStep, "number",
0298: multipleSteps);
0299: sbRelations.append('=');
0300: appendField(sbRelations, relationStep, "dnumber",
0301: multipleSteps);
0302: sbRelations.append(" AND ");
0303: appendField(sbRelations, nextStep, "number", multipleSteps);
0304: sbRelations.append('=');
0305: appendField(sbRelations, relationStep, "snumber",
0306: multipleSteps);
0307: if (relationStep.getCheckedDirectionality()) {
0308: sbRelations.append(" AND ");
0309: appendField(sbRelations, relationStep, "dir",
0310: multipleSteps);
0311: sbRelations.append("<>1");
0312: }
0313: sbRelations.append(") OR (");
0314: appendField(sbRelations, previousStep, "number",
0315: multipleSteps);
0316: sbRelations.append('=');
0317: appendField(sbRelations, relationStep, "snumber",
0318: multipleSteps);
0319: sbRelations.append(" AND ");
0320: appendField(sbRelations, nextStep, "number", multipleSteps);
0321: sbRelations.append('=');
0322: appendField(sbRelations, relationStep, "dnumber",
0323: multipleSteps);
0324: if (relationStep.getRole() != null) {
0325: sbRelations.append("))");
0326: } else {
0327: sbRelations.append(')');
0328: }
0329: break;
0330:
0331: case RelationStep.DIRECTIONS_ALL:
0332: if (relationStep.getRole() != null) {
0333: sbRelations.append("(((");
0334: } else {
0335: sbRelations.append("((");
0336: }
0337: appendField(sbRelations, previousStep, "number",
0338: multipleSteps);
0339: sbRelations.append('=');
0340: appendField(sbRelations, relationStep, "dnumber",
0341: multipleSteps);
0342: sbRelations.append(" AND ");
0343: appendField(sbRelations, nextStep, "number", multipleSteps);
0344: sbRelations.append('=');
0345: appendField(sbRelations, relationStep, "snumber",
0346: multipleSteps);
0347: sbRelations.append(") OR (");
0348: appendField(sbRelations, previousStep, "number",
0349: multipleSteps);
0350: sbRelations.append('=');
0351: appendField(sbRelations, relationStep, "snumber",
0352: multipleSteps);
0353: sbRelations.append(" AND ");
0354: appendField(sbRelations, nextStep, "number", multipleSteps);
0355: sbRelations.append('=');
0356: appendField(sbRelations, relationStep, "dnumber",
0357: multipleSteps);
0358: if (relationStep.getRole() != null) {
0359: sbRelations.append("))");
0360: } else {
0361: sbRelations.append(')');
0362: }
0363: break;
0364:
0365: case RelationStep.DIRECTIONS_EITHER:
0366: throw new UnsupportedOperationException(
0367: "Directionality 'EITHER' is not (yet) supported");
0368:
0369: default: // Invalid directionality value.
0370: throw new IllegalStateException(
0371: "Invalid directionality value: "
0372: + relationStep.getDirectionality());
0373: }
0374: if (relationStep.getRole() != null) {
0375: sbRelations.append(" AND ");
0376: appendField(sbRelations, relationStep, "rnumber",
0377: multipleSteps);
0378: sbRelations.append('=').append(relationStep.getRole());
0379: }
0380: sbRelations.append(')');
0381: }
0382:
0383: // javadoc is inherited
0384: public void appendQueryBodyToSql(StringBuilder sb,
0385: SearchQuery query, SqlHandler firstInChain)
0386: throws SearchQueryException {
0387:
0388: // Buffer expressions for included nodes, like
0389: // "x.number in (...)".
0390: StringBuilder sbNodes = new StringBuilder();
0391:
0392: // Buffer expressions for relations, like
0393: // "x.number = r.snumber AND y.number = r.dnumber".
0394: StringBuilder sbRelations = new StringBuilder();
0395:
0396: // Buffer fields to group by, like
0397: // "alias1, alias2, ..."
0398: StringBuilder sbGroups = new StringBuilder();
0399:
0400: boolean multipleSteps = query.getSteps().size() > 1;
0401:
0402: // Fields expression
0403: List<StepField> lFields = query.getFields();
0404:
0405: boolean appended = false;
0406: for (StepField field : lFields) {
0407: if (field.getType() == Field.TYPE_BINARY)
0408: continue;
0409: if (appended) {
0410: sb.append(',');
0411: }
0412: appended = true;
0413: // fieldname prefixed by table alias.
0414: Step step = field.getStep();
0415: String fieldName = field.getFieldName();
0416: String fieldAlias = field.getAlias();
0417:
0418: if (field instanceof AggregatedField) {
0419: int aggregationType = ((AggregatedField) field)
0420: .getAggregationType();
0421: if (aggregationType == AggregatedField.AGGREGATION_TYPE_GROUP_BY) {
0422:
0423: // Group by.
0424: appendField(sb, step, fieldName, multipleSteps);
0425:
0426: // Append to "GROUP BY"-buffer.
0427: if (sbGroups.length() > 0) {
0428: sbGroups.append(',');
0429: }
0430: if (fieldAlias != null) {
0431: sbGroups.append(getAllowedValue(fieldAlias));
0432: } else {
0433: appendField(sbGroups, step, fieldName,
0434: multipleSteps);
0435: }
0436: } else {
0437:
0438: // Aggregate function.
0439: switch (aggregationType) {
0440: case AggregatedField.AGGREGATION_TYPE_COUNT:
0441: sb.append("COUNT(");
0442: break;
0443:
0444: case AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT:
0445: sb.append("COUNT(DISTINCT ");
0446: break;
0447:
0448: case AggregatedField.AGGREGATION_TYPE_MIN:
0449: sb.append("MIN(");
0450: break;
0451:
0452: case AggregatedField.AGGREGATION_TYPE_MAX:
0453: sb.append("MAX(");
0454: break;
0455:
0456: default:
0457: throw new IllegalStateException(
0458: "Invalid aggregationType value: "
0459: + aggregationType);
0460: }
0461: appendField(sb, step, fieldName, multipleSteps);
0462: sb.append(')');
0463: }
0464:
0465: } else {
0466:
0467: // Non-aggregate field.
0468: appendField(sb, step, fieldName, multipleSteps);
0469: }
0470:
0471: // Field alias.
0472: if (fieldAlias != null) {
0473: sb.append(" AS ").append(getAllowedValue(fieldAlias));
0474: }
0475:
0476: }
0477:
0478: // When 'distinct', make sure all fields used for sorting are
0479: // included in the query.
0480: // Some databases require this (including PostgreSQL).
0481: // By fixing this here, the result of the query remains consistent
0482: // across databases, while requiring no modification in the calling
0483: // code.
0484: if (query.isDistinct()) {
0485: if (log.isDebugEnabled()) {
0486: log.debug("Query is distinct, adding "
0487: + query.getSortOrders());
0488: }
0489: boolean needComma = appended;
0490: Iterator<SortOrder> iSortOrder = query.getSortOrders()
0491: .iterator();
0492: while (iSortOrder.hasNext()) {
0493: SortOrder sortOrder = iSortOrder.next();
0494: StepField field = sortOrder.getField();
0495: if (lFields.indexOf(field) == -1) {
0496: if (needComma)
0497: sb.append(',');
0498: appendSortOrderField(sb, sortOrder, multipleSteps);
0499: needComma = true;
0500: }
0501: }
0502: }
0503:
0504: // Tables
0505: sb.append(" FROM ");
0506: Iterator<Step> iSteps = query.getSteps().iterator();
0507: while (iSteps.hasNext()) {
0508: Step step = iSteps.next();
0509: appendTableName(sb, step);
0510:
0511: if (iSteps.hasNext()) {
0512: sb.append(",");
0513: }
0514:
0515: // Included nodes.
0516: SortedSet<Integer> nodes = step.getNodes();
0517: if (nodes.size() > 0) {
0518: if (sbNodes.length() > 0) {
0519: sbNodes.append(" AND ");
0520: }
0521: appendField(sbNodes, step, "number", multipleSteps);
0522: if (nodes.size() > 1) {
0523: // only use IN(...) if there are really more numbers
0524: sbNodes.append(" IN (");
0525: Iterator<Integer> iNodes = nodes.iterator();
0526: while (iNodes.hasNext()) {
0527: Integer node = iNodes.next();
0528: sbNodes.append(node);
0529: if (iNodes.hasNext()) {
0530: sbNodes.append(',');
0531: }
0532: }
0533: sbNodes.append(')');
0534: } else {
0535: // otherwise use equals, which is a LOT faster in some cases
0536: sbNodes.append('=');
0537: sbNodes.append(nodes.first());
0538: }
0539: }
0540:
0541: // Relation steps.
0542: if (step instanceof RelationStep) {
0543: appendRelationConstraints(sbRelations,
0544: (RelationStep) step, multipleSteps);
0545: }
0546: }
0547:
0548: // Constraints
0549: StringBuilder sbConstraints = new StringBuilder();
0550: sbConstraints.append(sbNodes); // Constraints by included nodes.
0551: if (sbConstraints.length() > 0 && sbRelations.length() > 0) {
0552: sbConstraints.append(" AND ");
0553: }
0554: sbConstraints.append(sbRelations); // Constraints by relations.
0555: if (query.getConstraint() != null) {
0556: Constraint constraint = query.getConstraint();
0557: if (sbConstraints.length() > 0) {
0558: // Combine constraints.
0559: sbConstraints.append(" AND ");
0560: if (constraint instanceof CompositeConstraint) {
0561: appendCompositeConstraintToSql(sbConstraints,
0562: (CompositeConstraint) constraint, query,
0563: false, true, firstInChain);
0564: } else {
0565: firstInChain.appendConstraintToSql(sbConstraints,
0566: constraint, query, false, true);
0567: }
0568: } else {
0569: // Only regular constraints.
0570: if (constraint instanceof CompositeConstraint) {
0571: appendCompositeConstraintToSql(sbConstraints,
0572: (CompositeConstraint) constraint, query,
0573: false, false, firstInChain);
0574: } else {
0575: firstInChain.appendConstraintToSql(sbConstraints,
0576: constraint, query, false, false);
0577: }
0578: }
0579: }
0580: if (sbConstraints.length() > 0) {
0581: sb.append(" WHERE ").append(sbConstraints.toString());
0582: }
0583:
0584: // GROUP BY
0585: if (sbGroups.length() > 0) {
0586: sb.append(" GROUP BY ").append(sbGroups.toString());
0587: }
0588:
0589: appendSortOrders(sb, query);
0590: }
0591:
0592: /**
0593: * @param sb
0594: * @param step
0595: * @since MMBase-1.8
0596: */
0597: protected void appendTableName(StringBuilder sb, Step step) {
0598: // Tablename, prefixed with basename and underscore
0599: sb.append(MMBase.getMMBase().getBaseName()).append('_').
0600: //Currently no replacement strategy is implemented for
0601: //invalid tablenames.
0602: //This would be useful, but requires modification to
0603: //the insert/update/delete code as well.
0604: //append(getAllowedValue(tableName));
0605: append(step.getTableName());
0606:
0607: appendTableAlias(sb, step);
0608: }
0609:
0610: /**
0611: * @since MMBase-1.8
0612: */
0613: protected void appendTableAlias(StringBuilder sb, Step step) {
0614: String tableAlias = step.getAlias();
0615: // Table alias (tablename when table alias not set).
0616: if (tableAlias != null) {
0617: sb.append(" ").append(getAllowedValue(tableAlias));
0618: } else {
0619: sb.append(" ").append(getAllowedValue(step.getTableName()));
0620: }
0621: }
0622:
0623: /**
0624: * @since MMBase-1.8
0625: */
0626: protected StringBuilder appendSortOrderDirection(StringBuilder sb,
0627: SortOrder sortOrder) throws IllegalStateException {
0628: // Sort direction.
0629: switch (sortOrder.getDirection()) {
0630: case SortOrder.ORDER_ASCENDING:
0631: sb.append(" ASC");
0632: break;
0633: case SortOrder.ORDER_DESCENDING:
0634: sb.append(" DESC");
0635: break;
0636: default: // Invalid direction value.
0637: throw new IllegalStateException("Invalid direction value: "
0638: + sortOrder.getDirection());
0639: }
0640: return sb;
0641: }
0642:
0643: /**
0644: * @since MMBase-1.8
0645: */
0646: protected StringBuilder appendSortOrderField(StringBuilder sb,
0647: SortOrder sortOrder, boolean multipleSteps) {
0648: boolean uppered = false;
0649: if (!sortOrder.isCaseSensitive()
0650: && sortOrder.getField().getType() == Field.TYPE_STRING) {
0651: sb.append("UPPER(");
0652: uppered = true;
0653: }
0654: // Fieldname.
0655: appendField(sb, sortOrder, multipleSteps);
0656: if (uppered) {
0657: sb.append(")");
0658: appendSortOrderDirection(sb, sortOrder);
0659: sb.append(",");
0660: // also order by field itself, so ensure uniqueness.
0661: appendField(sb, sortOrder, multipleSteps);
0662: }
0663: return sb;
0664: }
0665:
0666: /**
0667: * @since MMBase-1.8.2
0668: */
0669: protected StringBuilder appendSortOrderField(StringBuilder sb,
0670: SortOrder sortOrder, boolean multipleSteps,
0671: SearchQuery query) {
0672: return appendSortOrderField(sb, sortOrder, multipleSteps);
0673: }
0674:
0675: /**
0676: * @since MMBase-1.8
0677: */
0678: protected StringBuilder appendSortOrders(StringBuilder sb,
0679: SearchQuery query) {
0680: boolean multipleSteps = query.getSteps().size() > 1;
0681: List<SortOrder> sortOrders = query.getSortOrders();
0682: if (sortOrders.size() > 0) {
0683: sb.append(" ORDER BY ");
0684: Iterator<SortOrder> iSortOrders = sortOrders.iterator();
0685: while (iSortOrders.hasNext()) {
0686: SortOrder sortOrder = iSortOrders.next();
0687: appendSortOrderField(sb, sortOrder, multipleSteps,
0688: query);
0689: appendSortOrderDirection(sb, sortOrder);
0690: if (iSortOrders.hasNext()) {
0691: sb.append(",");
0692: }
0693: }
0694: }
0695: return sb;
0696: }
0697:
0698: /**
0699: * Appends the 'LIKE' operator for the given case sensitiviy. Some databases support a case
0700: * insensitive LIKE ('ILIKE'). Implementations for those database can override this method.
0701: *
0702: * @return The string buffer.
0703: */
0704: protected StringBuilder appendLikeOperator(StringBuilder sb,
0705: boolean caseSensitive) {
0706: sb.append(" LIKE ");
0707: return sb;
0708: }
0709:
0710: /**
0711: * @javadoc
0712: */
0713: protected void appendDateField(StringBuilder sb, Step step,
0714: String fieldName, boolean multipleSteps, int datePart) {
0715: String datePartFunction = null;
0716: switch (datePart) {
0717: case -1:
0718: break;
0719: case FieldValueDateConstraint.YEAR:
0720: datePartFunction = "YEAR";
0721: break;
0722: case FieldValueDateConstraint.MONTH:
0723: datePartFunction = "MONTH";
0724: break;
0725: case FieldValueDateConstraint.DAY_OF_MONTH:
0726: datePartFunction = "DAY";
0727: break;
0728: case FieldValueDateConstraint.HOUR:
0729: datePartFunction = "HOUR";
0730: break;
0731: case FieldValueDateConstraint.MINUTE:
0732: datePartFunction = "MINUTE";
0733: break;
0734: case FieldValueDateConstraint.SECOND:
0735: datePartFunction = "SECOND";
0736: break;
0737: default:
0738: throw new UnsupportedOperationException(
0739: "This date partition function (" + datePart
0740: + ") is not supported.");
0741: }
0742: if (datePartFunction != null) {
0743: sb.append("EXTRACT(");
0744: sb.append(datePartFunction);
0745: sb.append(" FROM ");
0746: }
0747: appendField(sb, step, fieldName, multipleSteps);
0748: if (datePartFunction != null) {
0749: sb.append(")");
0750: }
0751: }
0752:
0753: // javadoc is inherited
0754: // XXX what exception to throw when an unsupported constraint is
0755: // encountered (currently throws UnsupportedOperationException)?
0756: public void appendConstraintToSql(StringBuilder sb,
0757: Constraint constraint, SearchQuery query, boolean inverse,
0758: boolean inComposite) {
0759:
0760: // Net effect of inverse setting with constraint inverse property.
0761: boolean overallInverse = inverse ^ constraint.isInverse();
0762:
0763: boolean multipleSteps = query.getSteps().size() > 1;
0764:
0765: if (constraint instanceof FieldConstraint) {
0766:
0767: // Field constraint
0768: FieldConstraint fieldConstraint = (FieldConstraint) constraint;
0769: StepField field = fieldConstraint.getField();
0770: int fieldType = field.getType();
0771: String fieldName = field.getFieldName();
0772: Step step = field.getStep();
0773:
0774: // hardly nice and OO, the following code.
0775: //
0776: if (fieldConstraint instanceof FieldValueInConstraint) {
0777:
0778: // Field value-in constraint
0779: FieldValueInConstraint valueInConstraint = (FieldValueInConstraint) fieldConstraint;
0780: SortedSet<Object> values = valueInConstraint
0781: .getValues();
0782: if (values.size() == 0) {
0783: throw new IllegalStateException(
0784: "Field value-in constraint specifies no values "
0785: + "(at least 1 value is required).");
0786: }
0787: if (isRelevantCaseInsensitive(fieldConstraint)) {
0788: // case insensitive
0789: sb.append("LOWER(");
0790: appendField(sb, step, fieldName, multipleSteps);
0791: sb.append(")");
0792: } else {
0793: // case sensitive or case irrelevant
0794: appendField(sb, step, fieldName, multipleSteps);
0795: }
0796:
0797: if (values.size() > 1) {
0798: // only use IN(...) if there are really more numbers
0799: sb.append(overallInverse ? " NOT IN (" : " IN (");
0800: Iterator<Object> iValues = values.iterator();
0801: while (iValues.hasNext()) {
0802: Object value = iValues.next();
0803: appendFieldValue(sb, value, !fieldConstraint
0804: .isCaseSensitive(), fieldType);
0805: if (iValues.hasNext()) {
0806: sb.append(",");
0807: }
0808: }
0809: sb.append(")");
0810: } else {
0811: // otherwise use equals, which is a LOT faster in some cases
0812: sb.append(overallInverse ? "<>" : "=");
0813: appendFieldValue(sb, values.first(),
0814: !fieldConstraint.isCaseSensitive(),
0815: fieldType);
0816: }
0817:
0818: } else if (fieldConstraint instanceof FieldValueBetweenConstraint) {
0819:
0820: // Field value-between constraint
0821: FieldValueBetweenConstraint valueBetweenConstraint = (FieldValueBetweenConstraint) fieldConstraint;
0822: if (isRelevantCaseInsensitive(fieldConstraint)) {
0823: // case insensitive
0824: appendLowerField(sb, step, fieldName, multipleSteps);
0825: } else {
0826: // case sensitive or case irrelevant
0827: appendField(sb, step, fieldName, multipleSteps);
0828: }
0829: sb.append(overallInverse ? " NOT BETWEEN "
0830: : " BETWEEN ");
0831: appendFieldValue(sb, valueBetweenConstraint
0832: .getLowerLimit(), !fieldConstraint
0833: .isCaseSensitive(), fieldType);
0834: sb.append(" AND ");
0835: appendFieldValue(sb, valueBetweenConstraint
0836: .getUpperLimit(), !fieldConstraint
0837: .isCaseSensitive(), fieldType);
0838:
0839: } else if (fieldConstraint instanceof FieldNullConstraint) {
0840:
0841: // Field null constraint
0842: appendField(sb, step, fieldName, multipleSteps);
0843: sb.append(overallInverse ? " IS NOT NULL" : " IS NULL");
0844:
0845: } else if (fieldConstraint instanceof FieldCompareConstraint) {
0846:
0847: // Field compare constraint
0848: FieldCompareConstraint fieldCompareConstraint = (FieldCompareConstraint) fieldConstraint;
0849:
0850: // Negate by leading NOT, unless it's a LIKE constraint,
0851: // in which case NOT LIKE is used.
0852: if (fieldCompareConstraint.getOperator() != FieldCompareConstraint.LIKE) {
0853: sb.append(overallInverse ? "NOT (" : "");
0854: }
0855:
0856: if (fieldConstraint instanceof FieldValueDateConstraint) {
0857: int part = ((FieldValueDateConstraint) fieldConstraint)
0858: .getPart();
0859: appendDateField(sb, step, fieldName, multipleSteps,
0860: part);
0861: } else if (useLower(fieldCompareConstraint)
0862: && isRelevantCaseInsensitive(fieldConstraint)) {
0863: // case insensitive and database needs it
0864: appendLowerField(sb, step, fieldName, multipleSteps);
0865: } else {
0866: // case sensitive or case irrelevant
0867: appendField(sb, step, fieldName, multipleSteps);
0868: }
0869: switch (fieldCompareConstraint.getOperator()) {
0870: case FieldCompareConstraint.LESS:
0871: sb.append("<");
0872: break;
0873:
0874: case FieldCompareConstraint.LESS_EQUAL:
0875: sb.append("<=");
0876: break;
0877:
0878: case FieldCompareConstraint.EQUAL:
0879: sb.append("=");
0880: break;
0881:
0882: case FieldCompareConstraint.NOT_EQUAL:
0883: sb.append("<>");
0884: break;
0885:
0886: case FieldCompareConstraint.GREATER:
0887: sb.append(">");
0888: break;
0889:
0890: case FieldCompareConstraint.GREATER_EQUAL:
0891: sb.append(">=");
0892: break;
0893:
0894: case FieldCompareConstraint.LIKE:
0895: if (overallInverse) {
0896: sb.append(" NOT");
0897: }
0898: appendLikeOperator(sb, fieldConstraint
0899: .isCaseSensitive());
0900: break;
0901: /*
0902: case FieldValueConstraint.REGEXP:
0903: sb.append(getRegularExpressionOperator());
0904: break;
0905: */
0906: default:
0907: throw new IllegalStateException(
0908: "Unknown operator value in constraint: "
0909: + fieldCompareConstraint
0910: .getOperator());
0911: }
0912: if (fieldCompareConstraint instanceof FieldValueConstraint) {
0913: // FieldValueConstraint.
0914: FieldValueConstraint fieldValueConstraint = (FieldValueConstraint) fieldCompareConstraint;
0915: Object value = fieldValueConstraint.getValue();
0916: appendFieldValue(
0917: sb,
0918: value,
0919: useLower(fieldValueConstraint)
0920: && isRelevantCaseInsensitive(fieldValueConstraint),
0921: fieldType);
0922: } else if (fieldCompareConstraint instanceof CompareFieldsConstraint) {
0923: // CompareFieldsConstraint
0924: CompareFieldsConstraint compareFieldsConstraint = (CompareFieldsConstraint) fieldCompareConstraint;
0925: StepField field2 = compareFieldsConstraint
0926: .getField2();
0927: String fieldName2 = field2.getFieldName();
0928: Step step2 = field2.getStep();
0929: if (useLower(fieldCompareConstraint)
0930: && isRelevantCaseInsensitive(fieldConstraint)) {
0931: // case insensitive
0932: appendLowerField(sb, step2, fieldName2,
0933: multipleSteps);
0934: } else {
0935: // case sensitive or case irrelevant
0936: appendField(sb, step2, fieldName2,
0937: multipleSteps);
0938: }
0939: } else {
0940: throw new UnsupportedOperationException(
0941: "Unknown constraint type: "
0942: + constraint.getClass().getName());
0943: }
0944: // Negate by leading NOT, unless it's a LIKE constraint,
0945: // in which case NOT LIKE is used.
0946: if (fieldCompareConstraint.getOperator() != FieldCompareConstraint.LIKE) {
0947: sb.append(overallInverse ? ")" : "");
0948: }
0949: } else {
0950: throw new UnsupportedOperationException(
0951: "Unknown constraint type: "
0952: + constraint.getClass().getName());
0953: }
0954:
0955: } else if (constraint instanceof CompositeConstraint) {
0956: throw new IllegalArgumentException(
0957: "Illegal constraint type for this method: "
0958: + constraint.getClass().getName());
0959: } else if (constraint instanceof LegacyConstraint) {
0960: LegacyConstraint legacyConstraint = (LegacyConstraint) constraint;
0961: if (legacyConstraint.getConstraint().trim().length() != 0) {
0962: if (overallInverse) {
0963: sb.append("NOT ");
0964: }
0965: if (overallInverse || inComposite) {
0966: sb.append("(");
0967: }
0968: sb.append(legacyConstraint.getConstraint());
0969: if (overallInverse || inComposite) {
0970: sb.append(")");
0971: }
0972: }
0973: } else {
0974: throw new UnsupportedOperationException(
0975: "Unknown constraint type: "
0976: + constraint.getClass().getName());
0977: }
0978: }
0979:
0980: // javadoc is inherited
0981: @SuppressWarnings("unused")
0982: // subclasses throw exception
0983: public int getSupportLevel(int feature, SearchQuery query)
0984: throws SearchQueryException {
0985: int result;
0986: switch (feature) {
0987: case SearchQueryHandler.FEATURE_MAX_NUMBER:
0988: if (query.getMaxNumber() == SearchQuery.DEFAULT_MAX_NUMBER) {
0989: result = SearchQueryHandler.SUPPORT_OPTIMAL;
0990: } else {
0991: result = SearchQueryHandler.SUPPORT_NONE;
0992: }
0993: break;
0994:
0995: case SearchQueryHandler.FEATURE_OFFSET:
0996: if (query.getOffset() == SearchQuery.DEFAULT_OFFSET) {
0997: result = SearchQueryHandler.SUPPORT_OPTIMAL;
0998: } else {
0999: result = SearchQueryHandler.SUPPORT_NONE;
1000: }
1001: break;
1002:
1003: default:
1004: result = SearchQueryHandler.SUPPORT_NONE;
1005: }
1006: return result;
1007: }
1008:
1009: // javadoc is inherited
1010: @SuppressWarnings("unused")
1011: // subclasses throw exception
1012: public int getSupportLevel(Constraint constraint, SearchQuery query)
1013: throws SearchQueryException {
1014: return constraint.getBasicSupportLevel();
1015: }
1016:
1017: // javadoc is inherited
1018: public String getAllowedValue(String value) {
1019: if (value == null) {
1020: throw new IllegalArgumentException("Invalid value: "
1021: + value);
1022: }
1023: return (String) MMBase.getMMBase().getStorageManagerFactory()
1024: .getStorageIdentifier(value);
1025: }
1026:
1027: /**
1028: * Represents a CompositeConstraint object as a constraint in SQL format,
1029: * appending the result to a stringbuffer.
1030: * When it is part of a composite expression, it will be surrounded by
1031: * parenthesis when needed.
1032: *
1033: * @param sb The stringbuffer to append to.
1034: * @param compositeConstraint The composite constraint.
1035: * @param query The searchquery containing the constraint.
1036: * @param inverse True when the inverse constraint must be represented,
1037: * false otherwise.
1038: * @param inComposite True when the constraint is part of
1039: * a composite expression.
1040: * @param firstInChain The first element in the chain of handlers.
1041: * At some point <code>appendConstraintToSql()</code> will have
1042: * to be called on this handler, to generate the constraints in
1043: * the composite.
1044: */
1045: protected void appendCompositeConstraintToSql(StringBuilder sb,
1046: CompositeConstraint compositeConstraint, SearchQuery query,
1047: boolean inverse, boolean inComposite,
1048: SqlHandler firstInChain) throws SearchQueryException {
1049:
1050: // Net effect of inverse setting with constraint inverse property.
1051: boolean overallInverse = inverse
1052: ^ compositeConstraint.isInverse();
1053:
1054: String strOperator = null;
1055: if (compositeConstraint.getLogicalOperator() == CompositeConstraint.LOGICAL_AND) {
1056: if (overallInverse) {
1057: // Inverse: NOT (A and B and C) = NOT A or NOT B or NOT C
1058: strOperator = " OR ";
1059: } else {
1060: strOperator = " AND ";
1061: }
1062: } else if (compositeConstraint.getLogicalOperator() == CompositeConstraint.LOGICAL_OR) {
1063: if (overallInverse) {
1064: // Inverse: NOT (A or B or C) = NOT A and NOT B and NOT C
1065: strOperator = " AND ";
1066: } else {
1067: strOperator = " OR ";
1068: }
1069: } else {
1070: throw new IllegalStateException(
1071: "Invalid logical operator: "
1072: + compositeConstraint.getLogicalOperator()
1073: + ", must be either "
1074: + CompositeConstraint.LOGICAL_AND + " or "
1075: + CompositeConstraint.LOGICAL_OR);
1076: }
1077: List<Constraint> childs = compositeConstraint.getChilds();
1078:
1079: // Test for at least 1 child.
1080: if (childs.isEmpty()) {
1081: throw new IllegalStateException(
1082: "Composite constraint has no child (at least 1 child is required).");
1083: }
1084:
1085: boolean hasMultipleChilds = childs.size() > 1;
1086:
1087: // Opening parenthesis, when part of composite expression
1088: // and with multiple childs.
1089: if (inComposite && hasMultipleChilds) {
1090: sb.append("(");
1091: }
1092:
1093: // Recursively append all childs.
1094: Iterator<Constraint> iChilds = childs.iterator();
1095: while (iChilds.hasNext()) {
1096: Constraint child = iChilds.next();
1097: if (child instanceof CompositeConstraint) {
1098: // Child is composite constraint.
1099: appendCompositeConstraintToSql(sb,
1100: (CompositeConstraint) child, query,
1101: overallInverse, hasMultipleChilds, firstInChain);
1102: } else {
1103: // Child is non-composite constraint.
1104: firstInChain.appendConstraintToSql(sb, child, query,
1105: overallInverse, hasMultipleChilds);
1106: }
1107: if (iChilds.hasNext()) {
1108: sb.append(strOperator);
1109: }
1110: }
1111:
1112: // Closing parenthesis, when part of composite expression
1113: // and with multiple childs.
1114: if (inComposite && hasMultipleChilds) {
1115: sb.append(")");
1116: }
1117: }
1118:
1119: /**
1120: * Creates an identifier for a field absed on adate from a sortorder, and appends it to a stringbuffer.
1121: * The identifier is constructed from the fieldname, optionally prefixed
1122: * by the tablename or the tablealias - when available.
1123: *
1124: * @param sb The stringbuffer to append to.
1125: * @param sortOrder The sortOrder object containing the field data.
1126: * @param includeTablePrefix <code>true</code> when the fieldname must be
1127: * prefixed with the tablename or tablealias (e.g. like in "images.number"),
1128: * <code>false</code> otherwise.
1129: */
1130: protected void appendField(StringBuilder sb, SortOrder sortOrder,
1131: boolean includeTablePrefix) {
1132: StepField field = sortOrder.getField();
1133: if (sortOrder instanceof DateSortOrder) {
1134: appendDateField(sb, field.getStep(), field.getFieldName(),
1135: includeTablePrefix, ((DateSortOrder) sortOrder)
1136: .getPart());
1137: } else {
1138: appendField(sb, field.getStep(), field.getFieldName(),
1139: includeTablePrefix);
1140: }
1141: }
1142:
1143: /**
1144: * Creates an identifier for a field, and appends it to a stringbuffer.
1145: * The identifier is constructed from the fieldname, optionally prefixed
1146: * by the tablename or the tablealias - when available.
1147: *
1148: * @param sb The stringbuffer to append to.
1149: * @param step The Step the field belongs to.
1150: * @param fieldName The fields fieldname.
1151: * @param includeTablePrefix <code>true</code> when the fieldname must be
1152: * prefixed with the tablename or tablealias (e.g. like in "images.number"),
1153: * <code>false</code> otherwise.
1154: */
1155: // TODO RvM: add to interface, add javadoc
1156: protected void appendField(StringBuilder sb, Step step,
1157: String fieldName, boolean includeTablePrefix) {
1158:
1159: String tableAlias = step.getAlias();
1160: if (includeTablePrefix) {
1161: if (tableAlias != null) {
1162: sb.append(getAllowedValue(tableAlias));
1163: } else {
1164: sb.append(getAllowedValue(step.getTableName()));
1165: }
1166: sb.append(".");
1167: }
1168: sb.append(getAllowedValue(fieldName));
1169: }
1170:
1171: /**
1172: * @since MMBase-1.8.5
1173: */
1174: protected void appendLowerField(StringBuilder sb, Step step,
1175: String fieldName, boolean includeTablePrefix) {
1176: // case insensitive
1177: sb.append("LOWER(");
1178: appendField(sb, step, fieldName, includeTablePrefix);
1179: sb.append(')');
1180: }
1181:
1182: }
|