0001: //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/trunk/src/org/deegree/io/datastore/sql/wherebuilder/WhereBuilder.java $
0002: /*---------------- FILE HEADER ------------------------------------------
0003:
0004: This file is part of deegree.
0005: Copyright (C) 2001-2008 by:
0006: EXSE, Department of Geography, University of Bonn
0007: http://www.giub.uni-bonn.de/deegree/
0008: lat/lon GmbH
0009: http://www.lat-lon.de
0010:
0011: This library is free software; you can redistribute it and/or
0012: modify it under the terms of the GNU Lesser General Public
0013: License as published by the Free Software Foundation; either
0014: version 2.1 of the License, or (at your option) any later version.
0015:
0016: This library is distributed in the hope that it will be useful,
0017: but WITHOUT ANY WARRANTY; without even the implied warranty of
0018: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0019: Lesser General Public License for more details.
0020:
0021: You should have received a copy of the GNU Lesser General Public
0022: License along with this library; if not, write to the Free Software
0023: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0024:
0025: Contact:
0026:
0027: Andreas Poth
0028: lat/lon GmbH
0029: Aennchenstraße 19
0030: 53177 Bonn
0031: Germany
0032: E-Mail: poth@lat-lon.de
0033:
0034: Prof. Dr. Klaus Greve
0035: Department of Geography
0036: University of Bonn
0037: Meckenheimer Allee 166
0038: 53115 Bonn
0039: Germany
0040: E-Mail: greve@giub.uni-bonn.de
0041:
0042: ---------------------------------------------------------------------------*/
0043: package org.deegree.io.datastore.sql.wherebuilder;
0044:
0045: import java.sql.Types;
0046: import java.util.ArrayList;
0047: import java.util.Hashtable;
0048: import java.util.Iterator;
0049: import java.util.List;
0050: import java.util.Stack;
0051:
0052: import org.deegree.framework.log.ILogger;
0053: import org.deegree.framework.log.LoggerFactory;
0054: import org.deegree.i18n.Messages;
0055: import org.deegree.io.datastore.DatastoreException;
0056: import org.deegree.io.datastore.PropertyPathResolvingException;
0057: import org.deegree.io.datastore.schema.MappedFeaturePropertyType;
0058: import org.deegree.io.datastore.schema.MappedFeatureType;
0059: import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
0060: import org.deegree.io.datastore.schema.MappedPropertyType;
0061: import org.deegree.io.datastore.schema.MappedSimplePropertyType;
0062: import org.deegree.io.datastore.schema.TableRelation;
0063: import org.deegree.io.datastore.schema.content.ConstantContent;
0064: import org.deegree.io.datastore.schema.content.MappingField;
0065: import org.deegree.io.datastore.schema.content.SQLFunctionCall;
0066: import org.deegree.io.datastore.schema.content.SimpleContent;
0067: import org.deegree.io.datastore.sql.StatementBuffer;
0068: import org.deegree.io.datastore.sql.TableAliasGenerator;
0069: import org.deegree.io.datastore.sql.VirtualContentProvider;
0070: import org.deegree.model.filterencoding.ArithmeticExpression;
0071: import org.deegree.model.filterencoding.ComparisonOperation;
0072: import org.deegree.model.filterencoding.ComplexFilter;
0073: import org.deegree.model.filterencoding.DBFunction;
0074: import org.deegree.model.filterencoding.Expression;
0075: import org.deegree.model.filterencoding.ExpressionDefines;
0076: import org.deegree.model.filterencoding.FeatureFilter;
0077: import org.deegree.model.filterencoding.Filter;
0078: import org.deegree.model.filterencoding.FilterEvaluationException;
0079: import org.deegree.model.filterencoding.Function;
0080: import org.deegree.model.filterencoding.Literal;
0081: import org.deegree.model.filterencoding.LogicalOperation;
0082: import org.deegree.model.filterencoding.Operation;
0083: import org.deegree.model.filterencoding.OperationDefines;
0084: import org.deegree.model.filterencoding.PropertyIsBetweenOperation;
0085: import org.deegree.model.filterencoding.PropertyIsCOMPOperation;
0086: import org.deegree.model.filterencoding.PropertyIsLikeOperation;
0087: import org.deegree.model.filterencoding.PropertyIsNullOperation;
0088: import org.deegree.model.filterencoding.PropertyName;
0089: import org.deegree.model.filterencoding.SpatialOperation;
0090: import org.deegree.ogcbase.PropertyPath;
0091: import org.deegree.ogcbase.SortProperty;
0092:
0093: /**
0094: * Creates SQL-WHERE clauses from OGC filter expressions (to restrict SQL statements to all stored
0095: * features that match a given filter).
0096: * <p>
0097: * Also handles the creation of ORDER-BY clauses.
0098: *
0099: * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
0100: * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
0101: * @author last edited by: $Author: aschmitz $
0102: *
0103: * @version $Revision: 10506 $, $Date: 2008-03-06 08:50:33 -0800 (Thu, 06 Mar 2008) $
0104: */
0105: public class WhereBuilder {
0106:
0107: protected static final ILogger LOG = LoggerFactory
0108: .getLogger(WhereBuilder.class);
0109:
0110: // database specific SRS code for unspecified SRS
0111: protected static final int SRS_UNDEFINED = -1;
0112:
0113: /** Targeted feature types. */
0114: protected MappedFeatureType[] rootFts;
0115:
0116: /** {@link Filter} for which the corresponding WHERE-clause will be generated. */
0117: protected Filter filter;
0118:
0119: protected SortProperty[] sortProperties;
0120:
0121: protected VirtualContentProvider vcProvider;
0122:
0123: protected QueryTableTree queryTableTree;
0124:
0125: protected List<PropertyPath> filterPropertyPaths = new ArrayList<PropertyPath>();
0126:
0127: protected List<PropertyPath> sortPropertyPaths = new ArrayList<PropertyPath>();
0128:
0129: private Hashtable<String, String> functionMap = new Hashtable<String, String>();
0130:
0131: /**
0132: * Creates a new <code>WhereBuilder</code> instance.
0133: *
0134: * @param rootFts
0135: * selected feature types, more than one type means that the types are joined
0136: * @param aliases
0137: * aliases for the feature types, may be null (must have same length as rootFts
0138: * otherwise)
0139: * @param filter
0140: * @param sortProperties
0141: * @param aliasGenerator
0142: * aliasGenerator to be used to generate table aliases, may be null
0143: * @param vcProvider
0144: * @throws DatastoreException
0145: */
0146: public WhereBuilder(MappedFeatureType[] rootFts, String[] aliases,
0147: Filter filter, SortProperty[] sortProperties,
0148: TableAliasGenerator aliasGenerator,
0149: VirtualContentProvider vcProvider)
0150: throws DatastoreException {
0151:
0152: this .rootFts = rootFts;
0153: this .queryTableTree = new QueryTableTree(rootFts, aliases,
0154: aliasGenerator);
0155:
0156: // add filter properties to the QueryTableTree
0157: this .filter = filter;
0158: if (filter != null) {
0159: assert filter instanceof ComplexFilter
0160: || filter instanceof FeatureFilter;
0161: buildFilterPropertyNameMap();
0162: for (PropertyPath property : this .filterPropertyPaths) {
0163: this .queryTableTree.addFilterProperty(property);
0164: }
0165: fillFunctionNameMap();
0166: }
0167:
0168: // add sort properties to the QueryTableTree
0169: this .sortProperties = sortProperties;
0170: if (sortProperties != null) {
0171: for (SortProperty property : sortProperties) {
0172: this .sortPropertyPaths.add(property.getSortProperty());
0173: this .queryTableTree.addSortProperty(property
0174: .getSortProperty());
0175: }
0176: }
0177:
0178: this .vcProvider = vcProvider;
0179:
0180: if (LOG.getLevel() == ILogger.LOG_DEBUG) {
0181: LOG.logDebug("QueryTableTree:\n" + this .queryTableTree);
0182: }
0183: }
0184:
0185: /**
0186: * Returns the table alias used for the specified root feature type.
0187: *
0188: * @param i
0189: * index of the requested root feature type
0190: * @return the alias used for the root table
0191: */
0192: public String getRootTableAlias(int i) {
0193: return this .queryTableTree.getRootNodes()[i].getTableAlias();
0194: }
0195:
0196: /**
0197: * Returns the associated <code>Filter</code> instance.
0198: *
0199: * @return the associated <code>Filter</code> instance
0200: */
0201: public Filter getFilter() {
0202: return this .filter;
0203: }
0204:
0205: protected MappedGeometryPropertyType getGeometryProperty(
0206: PropertyName propName) {
0207: PropertyPath propertyPath = propName.getValue();
0208: PropertyNode propertyNode = this .queryTableTree
0209: .getPropertyNode(propertyPath);
0210: assert propertyNode != null;
0211: assert propertyNode instanceof GeometryPropertyNode;
0212: return (MappedGeometryPropertyType) propertyNode.getProperty();
0213: }
0214:
0215: // /**
0216: // * Returns the SRS of the {@link MappedGeometryPropertyType} that is identified by the given
0217: // * {@link PropertyPath}.
0218: // *
0219: // * @param propertyPath
0220: // * @return the default SRS of the geometry property type
0221: // */
0222: // protected String getSrs( PropertyPath propertyPath ) {
0223: // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
0224: // assert propertyNode != null;
0225: // assert propertyNode instanceof GeometryPropertyNode;
0226: // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty();
0227: // return geoProp.getSRS().toString();
0228: // }
0229: //
0230: // /**
0231: // * Returns the internal Srs of the {@link MappedGeometryPropertyType} that is identified by
0232: // the
0233: // * given {@link PropertyPath}.
0234: // *
0235: // * @param propertyPath
0236: // * @return the default SRS of the geometry property type
0237: // */
0238: // protected int getInternalSrsCode( PropertyPath propertyPath ) {
0239: // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
0240: // assert propertyNode != null;
0241: // assert propertyNode instanceof GeometryPropertyNode;
0242: // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty();
0243: // return geoProp.getMappingField().getSRS();
0244: // }
0245:
0246: protected int getPropertyNameSQLType(PropertyName propertyName) {
0247:
0248: PropertyPath propertyPath = propertyName.getValue();
0249: PropertyNode propertyNode = this .queryTableTree
0250: .getPropertyNode(propertyPath);
0251: assert propertyNode != null;
0252:
0253: if (propertyNode == null) {
0254: LOG.logDebug("Null propertyNode for propertyName: "
0255: + propertyName + " with queryTable: "
0256: + this .queryTableTree);
0257: }
0258:
0259: MappedPropertyType propertyType = propertyNode.getProperty();
0260: if (!(propertyType instanceof MappedSimplePropertyType)) {
0261: String msg = "Error in WhereBuilder: cannot compare against properties of type '"
0262: + propertyType.getClass() + "'.";
0263: LOG.logError(msg);
0264: throw new RuntimeException(msg);
0265: }
0266:
0267: SimpleContent content = ((MappedSimplePropertyType) propertyType)
0268: .getContent();
0269: if (!(content instanceof MappingField)) {
0270: String msg = "Virtual properties are currently ignored in WhereBuilder#getPropertyNameSQLType(PropertyName).";
0271: LOG.logError(msg);
0272: return Types.VARCHAR;
0273: }
0274:
0275: int targetSqlType = ((MappingField) content).getType();
0276: return targetSqlType;
0277: }
0278:
0279: protected void buildFilterPropertyNameMap()
0280: throws PropertyPathResolvingException {
0281: if (this .filter instanceof ComplexFilter) {
0282: buildPropertyNameMapFromOperation(((ComplexFilter) this .filter)
0283: .getOperation());
0284: } else if (this .filter instanceof FeatureFilter) {
0285: // TODO
0286: // throw new PropertyPathResolvingException( "FeatureFilter not implemented yet." );
0287: }
0288: }
0289:
0290: private void buildPropertyNameMapFromOperation(Operation operation)
0291: throws PropertyPathResolvingException {
0292: switch (OperationDefines.getTypeById(operation.getOperatorId())) {
0293: case OperationDefines.TYPE_SPATIAL: {
0294: registerPropertyName(((SpatialOperation) operation)
0295: .getPropertyName());
0296: break;
0297: }
0298: case OperationDefines.TYPE_COMPARISON: {
0299: buildPropertyNameMap((ComparisonOperation) operation);
0300: break;
0301: }
0302: case OperationDefines.TYPE_LOGICAL: {
0303: buildPropertyNameMap((LogicalOperation) operation);
0304: break;
0305: }
0306: default: {
0307: break;
0308: }
0309: }
0310: }
0311:
0312: private void buildPropertyNameMap(ComparisonOperation operation)
0313: throws PropertyPathResolvingException {
0314: switch (operation.getOperatorId()) {
0315: case OperationDefines.PROPERTYISEQUALTO:
0316: case OperationDefines.PROPERTYISLESSTHAN:
0317: case OperationDefines.PROPERTYISGREATERTHAN:
0318: case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
0319: case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
0320: buildPropertyNameMap(((PropertyIsCOMPOperation) operation)
0321: .getFirstExpression());
0322: buildPropertyNameMap(((PropertyIsCOMPOperation) operation)
0323: .getSecondExpression());
0324: break;
0325: }
0326: case OperationDefines.PROPERTYISLIKE: {
0327: registerPropertyName(((PropertyIsLikeOperation) operation)
0328: .getPropertyName());
0329: break;
0330: }
0331: case OperationDefines.PROPERTYISNULL: {
0332: buildPropertyNameMap(((PropertyIsNullOperation) operation)
0333: .getPropertyName());
0334: break;
0335: }
0336: case OperationDefines.PROPERTYISBETWEEN: {
0337: buildPropertyNameMap(((PropertyIsBetweenOperation) operation)
0338: .getLowerBoundary());
0339: buildPropertyNameMap(((PropertyIsBetweenOperation) operation)
0340: .getUpperBoundary());
0341: registerPropertyName(((PropertyIsBetweenOperation) operation)
0342: .getPropertyName());
0343: break;
0344: }
0345: default: {
0346: break;
0347: }
0348: }
0349: }
0350:
0351: private void buildPropertyNameMap(LogicalOperation operation)
0352: throws PropertyPathResolvingException {
0353: List<?> operationList = operation.getArguments();
0354: Iterator<?> it = operationList.iterator();
0355: while (it.hasNext()) {
0356: buildPropertyNameMapFromOperation((Operation) it.next());
0357: }
0358: }
0359:
0360: private void buildPropertyNameMap(Expression expression)
0361: throws PropertyPathResolvingException {
0362: switch (expression.getExpressionId()) {
0363: case ExpressionDefines.PROPERTYNAME: {
0364: registerPropertyName((PropertyName) expression);
0365: break;
0366: }
0367: case ExpressionDefines.ADD:
0368: case ExpressionDefines.SUB:
0369: case ExpressionDefines.MUL:
0370: case ExpressionDefines.DIV: {
0371: buildPropertyNameMap(((ArithmeticExpression) expression)
0372: .getFirstExpression());
0373: buildPropertyNameMap(((ArithmeticExpression) expression)
0374: .getSecondExpression());
0375: break;
0376: }
0377: case ExpressionDefines.FUNCTION: {
0378: // TODO: What about PropertyNames used here?
0379: break;
0380: }
0381: case ExpressionDefines.EXPRESSION:
0382: case ExpressionDefines.LITERAL: {
0383: break;
0384: }
0385: }
0386: }
0387:
0388: private void registerPropertyName(PropertyName propertyName) {
0389: this .filterPropertyPaths.add(propertyName.getValue());
0390: }
0391:
0392: /**
0393: * Appends the alias-qualified, comma separated list of all tables to be joined in order to
0394: * represent the associated filter expression (and possibly feature type joins).
0395: * <p>
0396: * The list consist of left outer joins ("x LEFT OUTER JOIN y") and cross-product joins ("x,y"):
0397: * <ul>
0398: * <li>left outer joins are generated for each join that is necessary, because of filter
0399: * expressions that target properties stored in related tables (condition joins)
0400: * <li>cross-product joins are generated for all feature type root tables (feature type joins)
0401: * that have not joined by filter expression joins before</li>
0402: * </ul>
0403: *
0404: * @param query
0405: * the list is appended to this <code>SQLStatement</code>
0406: */
0407: public void appendJoinTableList(StatementBuffer query) {
0408:
0409: FeatureTypeNode[] rootNodes = this .queryTableTree
0410: .getRootNodes();
0411: appendOuterJoinTableList(query, rootNodes[0]);
0412: for (int i = 1; i < rootNodes.length; i++) {
0413: query.append(',');
0414: appendOuterJoinTableList(query, rootNodes[i]);
0415: }
0416: }
0417:
0418: /**
0419: * Appends the alias-qualified, comma separated list of tables to be joined (for one root
0420: * feature type node). This includes the join conditions (necessary for the filter conditions),
0421: * which are generated in ANSI-SQL left outer join style.
0422: *
0423: * @param query
0424: * the list is appended to this <code>SQLStatement</code>
0425: * @param rootNode
0426: * one root feature type node in the <code>QueryTableTree</code>
0427: */
0428: private void appendOuterJoinTableList(StatementBuffer query,
0429: FeatureTypeNode rootNode) {
0430:
0431: query.append(rootNode.getTable());
0432: query.append(' ');
0433: query.append(rootNode.getTableAlias());
0434: Stack<PropertyNode> propertyNodeStack = new Stack<PropertyNode>();
0435: PropertyNode[] propertyNodes = rootNode.getPropertyNodes();
0436: for (int i = 0; i < propertyNodes.length; i++) {
0437: propertyNodeStack.push(propertyNodes[i]);
0438: }
0439:
0440: while (!propertyNodeStack.isEmpty()) {
0441: PropertyNode currentNode = propertyNodeStack.pop();
0442: String fromAlias = currentNode.getParent().getTableAlias();
0443: TableRelation[] tableRelations = currentNode
0444: .getPathFromParent();
0445: if (tableRelations != null && tableRelations.length != 0) {
0446: String[] toAliases = currentNode.getTableAliases();
0447: appendOuterJoins(tableRelations, fromAlias, toAliases,
0448: query);
0449:
0450: }
0451: if (currentNode instanceof FeaturePropertyNode) {
0452: FeaturePropertyNode featurePropertyNode = (FeaturePropertyNode) currentNode;
0453: FeatureTypeNode[] childNodes = ((FeaturePropertyNode) currentNode)
0454: .getFeatureTypeNodes();
0455: for (int i = 0; i < childNodes.length; i++) {
0456: // TODO is this way of skipping root tables o.k.?
0457: if (childNodes[i].getFtAlias() != null) {
0458: continue;
0459: }
0460: String toTable = childNodes[i].getTable();
0461: String toAlias = childNodes[i].getTableAlias();
0462: String[] pathAliases = featurePropertyNode
0463: .getTableAliases();
0464: if (pathAliases.length == 0) {
0465: fromAlias = featurePropertyNode.getParent()
0466: .getTableAlias();
0467: } else {
0468: fromAlias = pathAliases[pathAliases.length - 1];
0469: }
0470: MappedFeaturePropertyType content = (MappedFeaturePropertyType) featurePropertyNode
0471: .getProperty();
0472: TableRelation[] relations = content
0473: .getTableRelations();
0474: TableRelation relation = relations[relations.length - 1];
0475: appendOuterJoin(relation, fromAlias, toAlias,
0476: toTable, query);
0477: propertyNodes = childNodes[i].getPropertyNodes();
0478: for (int j = 0; j < propertyNodes.length; j++) {
0479: propertyNodeStack.push(propertyNodes[j]);
0480: }
0481: }
0482: }
0483: }
0484: }
0485:
0486: private void appendOuterJoins(TableRelation[] tableRelation,
0487: String fromAlias, String[] toAliases, StatementBuffer query) {
0488: for (int i = 0; i < toAliases.length; i++) {
0489: String toAlias = toAliases[i];
0490: appendOuterJoin(tableRelation[i], fromAlias, toAlias, query);
0491: fromAlias = toAlias;
0492: }
0493: }
0494:
0495: private void appendOuterJoin(TableRelation tableRelation,
0496: String fromAlias, String toAlias, StatementBuffer query) {
0497:
0498: query.append(" LEFT OUTER JOIN ");
0499: query.append(tableRelation.getToTable());
0500: query.append(" ");
0501: query.append(toAlias);
0502: query.append(" ON ");
0503:
0504: MappingField[] fromFields = tableRelation.getFromFields();
0505: MappingField[] toFields = tableRelation.getToFields();
0506: for (int i = 0; i < fromFields.length; i++) {
0507: if (toAlias.equals("")) {
0508: toAlias = tableRelation.getToTable();
0509: }
0510: query.append(toAlias);
0511: query.append(".");
0512: query.append(toFields[i].getField());
0513: query.append("=");
0514: if (fromAlias.equals("")) {
0515: fromAlias = tableRelation.getFromTable();
0516: }
0517: query.append(fromAlias);
0518: query.append(".");
0519: query.append(fromFields[i].getField());
0520: if (i != fromFields.length - 1) {
0521: query.append(" AND ");
0522: }
0523: }
0524: }
0525:
0526: private void appendOuterJoin(TableRelation tableRelation,
0527: String fromAlias, String toAlias, String toTable,
0528: StatementBuffer query) {
0529:
0530: query.append(" LEFT OUTER JOIN ");
0531: query.append(toTable);
0532: query.append(" ");
0533: query.append(toAlias);
0534: query.append(" ON ");
0535:
0536: MappingField[] fromFields = tableRelation.getFromFields();
0537: MappingField[] toFields = tableRelation.getToFields();
0538: for (int i = 0; i < fromFields.length; i++) {
0539: if (toAlias.equals("")) {
0540: toAlias = toTable;
0541: }
0542: query.append(toAlias);
0543: query.append(".");
0544: query.append(toFields[i].getField());
0545: query.append("=");
0546: if (fromAlias.equals("")) {
0547: fromAlias = tableRelation.getFromTable();
0548: }
0549: query.append(fromAlias);
0550: query.append(".");
0551: query.append(fromFields[i].getField());
0552: if (i != fromFields.length - 1) {
0553: query.append(" AND ");
0554: }
0555: }
0556: }
0557:
0558: /**
0559: * Appends an SQL WHERE-condition corresponding to the <code>Filter</code> to the given SQL
0560: * statement.
0561: *
0562: * @param query
0563: * @throws DatastoreException
0564: */
0565: public final void appendWhereCondition(StatementBuffer query)
0566: throws DatastoreException {
0567: if (filter instanceof ComplexFilter) {
0568: query.append(" WHERE ");
0569: appendComplexFilterAsSQL(query, (ComplexFilter) filter);
0570: } else if (filter instanceof FeatureFilter) {
0571: FeatureFilter featureFilter = (FeatureFilter) filter;
0572: if (featureFilter.getFeatureIds().size() > 0) {
0573: query.append(" WHERE ");
0574: appendFeatureFilterAsSQL(query, featureFilter);
0575: }
0576: } else {
0577: assert false : "Unexpected filter type: "
0578: + filter.getClass();
0579: }
0580: }
0581:
0582: /**
0583: * Appends an SQL "ORDER BY"-condition that corresponds to the sort properties of the query to
0584: * the given SQL statement.
0585: *
0586: * @param query
0587: * @throws DatastoreException
0588: */
0589: public void appendOrderByCondition(StatementBuffer query)
0590: throws DatastoreException {
0591:
0592: // ignore properties that are unsuitable as sort criteria (like constant properties)
0593: List<SortProperty> sortProps = new ArrayList<SortProperty>();
0594:
0595: if (this .sortProperties != null
0596: && this .sortProperties.length != 0) {
0597: for (int i = 0; i < this .sortProperties.length; i++) {
0598: SortProperty sortProperty = this .sortProperties[i];
0599: PropertyPath path = sortProperty.getSortProperty();
0600: PropertyNode propertyNode = this .queryTableTree
0601: .getPropertyNode(path);
0602: MappedPropertyType pt = propertyNode.getProperty();
0603: if (!(pt instanceof MappedSimplePropertyType)) {
0604: String msg = Messages.getMessage(
0605: "DATASTORE_INVALID_SORT_PROPERTY", pt
0606: .getName());
0607: throw new DatastoreException(msg);
0608: }
0609: SimpleContent content = ((MappedSimplePropertyType) pt)
0610: .getContent();
0611: if (content.isSortable()) {
0612: sortProps.add(sortProperty);
0613: } else {
0614: String msg = "Ignoring sort criterion - property '"
0615: + path.getAsString()
0616: + "' is not suitable for sorting.";
0617: LOG.logDebug(msg);
0618: }
0619: }
0620: }
0621:
0622: if (sortProps.size() > 0) {
0623: query.append(" ORDER BY ");
0624: }
0625:
0626: for (int i = 0; i < sortProps.size(); i++) {
0627: SortProperty sortProperty = sortProps.get(i);
0628: PropertyPath path = sortProperty.getSortProperty();
0629: appendPropertyPathAsSQL(query, path);
0630: if (!sortProperty.getSortOrder()) {
0631: query.append(" DESC");
0632: }
0633: if (i != sortProps.size() - 1) {
0634: query.append(',');
0635: }
0636: }
0637: }
0638:
0639: /**
0640: * Appends an SQL fragment for the given object.
0641: *
0642: * @param query
0643: * @param filter
0644: * @throws DatastoreException
0645: */
0646: protected void appendComplexFilterAsSQL(StatementBuffer query,
0647: ComplexFilter filter) throws DatastoreException {
0648: appendOperationAsSQL(query, filter.getOperation());
0649: }
0650:
0651: /**
0652: * Appends an SQL fragment for the given object to the given sql statement.
0653: *
0654: * @param query
0655: * @param operation
0656: * @throws DatastoreException
0657: */
0658: protected void appendOperationAsSQL(StatementBuffer query,
0659: Operation operation) throws DatastoreException {
0660:
0661: switch (OperationDefines.getTypeById(operation.getOperatorId())) {
0662: case OperationDefines.TYPE_SPATIAL: {
0663: appendSpatialOperationAsSQL(query,
0664: (SpatialOperation) operation);
0665: break;
0666: }
0667: case OperationDefines.TYPE_COMPARISON: {
0668: try {
0669: appendComparisonOperationAsSQL(query,
0670: (ComparisonOperation) operation);
0671: } catch (FilterEvaluationException e) {
0672: new DatastoreException(e.getMessage(), e);
0673: }
0674: break;
0675: }
0676: case OperationDefines.TYPE_LOGICAL: {
0677: appendLogicalOperationAsSQL(query,
0678: (LogicalOperation) operation);
0679: break;
0680: }
0681: default: {
0682: break;
0683: }
0684: }
0685: }
0686:
0687: /**
0688: * Appends an SQL fragment for the given object to the given sql statement.
0689: *
0690: * @param query
0691: * @param operation
0692: * @throws FilterEvaluationException
0693: */
0694: protected void appendComparisonOperationAsSQL(
0695: StatementBuffer query, ComparisonOperation operation)
0696: throws FilterEvaluationException {
0697: switch (operation.getOperatorId()) {
0698: case OperationDefines.PROPERTYISEQUALTO:
0699: case OperationDefines.PROPERTYISLESSTHAN:
0700: case OperationDefines.PROPERTYISGREATERTHAN:
0701: case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
0702: case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
0703: appendPropertyIsCOMPOperationAsSQL(query,
0704: (PropertyIsCOMPOperation) operation);
0705: break;
0706: }
0707: case OperationDefines.PROPERTYISLIKE: {
0708: appendPropertyIsLikeOperationAsSQL(query,
0709: (PropertyIsLikeOperation) operation);
0710: break;
0711: }
0712: case OperationDefines.PROPERTYISNULL: {
0713: appendPropertyIsNullOperationAsSQL(query,
0714: (PropertyIsNullOperation) operation);
0715: break;
0716: }
0717: case OperationDefines.PROPERTYISBETWEEN: {
0718: appendPropertyIsBetweenOperationAsSQL(query,
0719: (PropertyIsBetweenOperation) operation);
0720: break;
0721: }
0722: }
0723: }
0724:
0725: /**
0726: * Appends an SQL fragment for the given object to the given sql statement.
0727: *
0728: * @param query
0729: * @param operation
0730: * @throws FilterEvaluationException
0731: */
0732: protected void appendPropertyIsCOMPOperationAsSQL(
0733: StatementBuffer query, PropertyIsCOMPOperation operation)
0734: throws FilterEvaluationException {
0735: Expression firstExpr = operation.getFirstExpression();
0736: if (!(firstExpr instanceof PropertyName)) {
0737: throw new IllegalArgumentException(
0738: "First expression in a comparison must "
0739: + "always be a 'PropertyName' element.");
0740: }
0741: int targetSqlType = getPropertyNameSQLType((PropertyName) firstExpr);
0742: if (operation.isMatchCase()) {
0743: appendExpressionAsSQL(query, firstExpr, targetSqlType);
0744: } else {
0745: List<Expression> list = new ArrayList<Expression>();
0746: list.add(firstExpr);
0747: Function func = new DBFunction(getFunctionName("LOWER"),
0748: list);
0749: appendFunctionAsSQL(query, func, targetSqlType);
0750: }
0751: switch (operation.getOperatorId()) {
0752: case OperationDefines.PROPERTYISEQUALTO: {
0753: query.append(" = ");
0754: break;
0755: }
0756: case OperationDefines.PROPERTYISLESSTHAN: {
0757: query.append(" < ");
0758: break;
0759: }
0760: case OperationDefines.PROPERTYISGREATERTHAN: {
0761: query.append(" > ");
0762: break;
0763: }
0764: case OperationDefines.PROPERTYISLESSTHANOREQUALTO: {
0765: query.append(" <= ");
0766: break;
0767: }
0768: case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
0769: query.append(" >= ");
0770: break;
0771: }
0772: }
0773: if (operation.isMatchCase()) {
0774: appendExpressionAsSQL(query, operation
0775: .getSecondExpression(), targetSqlType);
0776: } else {
0777: List<Expression> list = new ArrayList<Expression>();
0778: list.add(operation.getSecondExpression());
0779: Function func = new DBFunction(getFunctionName("LOWER"),
0780: list);
0781: appendFunctionAsSQL(query, func, targetSqlType);
0782: }
0783: }
0784:
0785: /**
0786: * Appends an SQL fragment for the given object to the given sql statement.
0787: *
0788: * @param query
0789: * @param operation
0790: * @throws FilterEvaluationException
0791: */
0792: protected void appendPropertyIsLikeOperationAsSQL(
0793: StatementBuffer query, PropertyIsLikeOperation operation)
0794: throws FilterEvaluationException {
0795:
0796: String literal = operation.getLiteral().getValue();
0797: String escape = "" + operation.getEscapeChar();
0798: String wildCard = "" + operation.getWildCard();
0799: String singleChar = "" + operation.getSingleChar();
0800:
0801: SpecialCharString specialString = new SpecialCharString(
0802: literal, wildCard, singleChar, escape);
0803: String sqlEncoded = specialString.toSQLStyle(!operation
0804: .isMatchCase());
0805:
0806: int targetSqlType = getPropertyNameSQLType(operation
0807: .getPropertyName());
0808:
0809: // if isMatchCase == false surround first argument with LOWER (...) and convert characters
0810: // in second argument to lower case
0811: if (operation.isMatchCase()) {
0812: appendPropertyNameAsSQL(query, operation.getPropertyName());
0813: } else {
0814: List<Expression> list = new ArrayList<Expression>();
0815: list.add(operation.getPropertyName());
0816: Function func = new DBFunction(getFunctionName("LOWER"),
0817: list);
0818: appendFunctionAsSQL(query, func, targetSqlType);
0819: }
0820:
0821: query.append(" LIKE ? ESCAPE ?");
0822: query.addArgument(sqlEncoded, Types.VARCHAR);
0823: query.addArgument("\\", Types.VARCHAR);
0824: }
0825:
0826: /**
0827: * Appends an SQL fragment for the given object to the given sql statement.
0828: *
0829: * @param query
0830: * @param operation
0831: */
0832: protected void appendPropertyIsNullOperationAsSQL(
0833: StatementBuffer query, PropertyIsNullOperation operation) {
0834: appendPropertyNameAsSQL(query, operation.getPropertyName());
0835: query.append(" IS NULL");
0836: }
0837:
0838: /**
0839: * Appends an SQL fragment for the given object to the given sql statement.
0840: *
0841: * @param query
0842: * @param operation
0843: * @throws FilterEvaluationException
0844: */
0845: protected void appendPropertyIsBetweenOperationAsSQL(
0846: StatementBuffer query, PropertyIsBetweenOperation operation)
0847: throws FilterEvaluationException {
0848:
0849: PropertyName propertyName = operation.getPropertyName();
0850: int targetSqlType = getPropertyNameSQLType(propertyName);
0851: appendExpressionAsSQL(query, operation.getLowerBoundary(),
0852: targetSqlType);
0853: query.append(" <= ");
0854: appendPropertyNameAsSQL(query, propertyName);
0855: query.append(" AND ");
0856: appendPropertyNameAsSQL(query, propertyName);
0857: query.append(" <= ");
0858: appendExpressionAsSQL(query, operation.getUpperBoundary(),
0859: targetSqlType);
0860: }
0861:
0862: /**
0863: * Appends an SQL fragment for the given object to the given sql statement.
0864: *
0865: * @param query
0866: * @param expression
0867: * @param targetSqlType
0868: * sql type code to be used for literals at the bottom of the expression tree
0869: * @throws FilterEvaluationException
0870: */
0871: protected void appendExpressionAsSQL(StatementBuffer query,
0872: Expression expression, int targetSqlType)
0873: throws FilterEvaluationException {
0874: switch (expression.getExpressionId()) {
0875: case ExpressionDefines.PROPERTYNAME: {
0876: appendPropertyNameAsSQL(query, (PropertyName) expression);
0877: break;
0878: }
0879: case ExpressionDefines.LITERAL: {
0880: appendLiteralAsSQL(query, (Literal) expression,
0881: targetSqlType);
0882: break;
0883: }
0884: case ExpressionDefines.FUNCTION: {
0885: Function function = (Function) expression;
0886: appendFunctionAsSQL(query, function, targetSqlType);
0887: break;
0888: }
0889: case ExpressionDefines.ADD:
0890: case ExpressionDefines.SUB:
0891: case ExpressionDefines.MUL:
0892: case ExpressionDefines.DIV: {
0893: appendArithmeticExpressionAsSQL(query,
0894: (ArithmeticExpression) expression, targetSqlType);
0895: break;
0896: }
0897: case ExpressionDefines.EXPRESSION:
0898: default: {
0899: throw new IllegalArgumentException(
0900: "Unexpected expression type: "
0901: + expression.getExpressionName());
0902: }
0903: }
0904: }
0905:
0906: /**
0907: * Appends an SQL fragment for the given object to the given sql statement.
0908: *
0909: * @param query
0910: * @param literal
0911: * @param targetSqlType
0912: */
0913: protected void appendLiteralAsSQL(StatementBuffer query,
0914: Literal literal, int targetSqlType) {
0915: query.append('?');
0916: query.addArgument(literal.getValue(), targetSqlType);
0917: }
0918:
0919: /**
0920: * Appends an SQL fragment for the given object to the given sql statement.
0921: *
0922: * @param query
0923: * @param propertyName
0924: */
0925: protected void appendPropertyNameAsSQL(StatementBuffer query,
0926: PropertyName propertyName) {
0927:
0928: PropertyPath propertyPath = propertyName.getValue();
0929: appendPropertyPathAsSQL(query, propertyPath);
0930: }
0931:
0932: /**
0933: * Appends an SQL fragment for the given object to the given sql statement.
0934: *
0935: * @param query
0936: * @param propertyPath
0937: */
0938: protected void appendPropertyPathAsSQL(StatementBuffer query,
0939: PropertyPath propertyPath) {
0940:
0941: LOG.logDebug("Looking up '" + propertyPath
0942: + "' in the query table tree.");
0943: MappingField mappingField = null;
0944: PropertyNode propertyNode = this .queryTableTree
0945: .getPropertyNode(propertyPath);
0946: assert (propertyNode != null);
0947: if (propertyNode instanceof SimplePropertyNode) {
0948: SimpleContent content = ((MappedSimplePropertyType) (propertyNode
0949: .getProperty())).getContent();
0950: if (!(content instanceof MappingField)) {
0951: if (content instanceof ConstantContent) {
0952: query.append("'"
0953: + ((ConstantContent) content).getValue()
0954: + "'");
0955: return;
0956: } else if (content instanceof SQLFunctionCall) {
0957: SQLFunctionCall call = (SQLFunctionCall) content;
0958: String tableAlias = null;
0959: String[] tableAliases = propertyNode
0960: .getTableAliases();
0961: if (tableAliases == null
0962: || tableAliases.length == 0) {
0963: tableAlias = propertyNode.getParent()
0964: .getTableAlias();
0965: } else {
0966: tableAlias = tableAliases[tableAliases.length - 1];
0967: }
0968: this .vcProvider.appendSQLFunctionCall(query,
0969: tableAlias, call);
0970: return;
0971: }
0972: String msg = "Virtual properties are currently ignored in WhereBuilder#appendPropertyPathAsSQL(StatementBuffer,PropertyPath).";
0973: LOG.logError(msg);
0974: assert false;
0975: }
0976: mappingField = (MappingField) content;
0977: } else if (propertyNode instanceof GeometryPropertyNode) {
0978: mappingField = ((MappedGeometryPropertyType) propertyNode
0979: .getProperty()).getMappingField();
0980: } else {
0981: String msg = "Internal error in WhereBuilder: unhandled PropertyNode type: '"
0982: + propertyNode.getClass().getName() + "'.";
0983: LOG.logError(msg);
0984: throw new RuntimeException(msg);
0985: }
0986: String tableAlias = null;
0987: String[] tableAliases = propertyNode.getTableAliases();
0988: if (tableAliases == null || tableAliases.length == 0) {
0989: tableAlias = propertyNode.getParent().getTableAlias();
0990: } else {
0991: tableAlias = tableAliases[tableAliases.length - 1];
0992: }
0993: if (tableAlias != "") {
0994: query.append(tableAlias);
0995: query.append('.');
0996: } else {
0997: query.append(mappingField.getTable());
0998: query.append('.');
0999: }
1000: query.append(mappingField.getField());
1001: }
1002:
1003: /**
1004: * Appends an SQL fragment for the given object to the given sql statement.
1005: *
1006: * @param query
1007: * @param expression
1008: * @param targetSqlType
1009: * @throws FilterEvaluationException
1010: */
1011: protected void appendArithmeticExpressionAsSQL(
1012: StatementBuffer query, ArithmeticExpression expression,
1013: int targetSqlType) throws FilterEvaluationException {
1014: query.append('(');
1015: appendExpressionAsSQL(query, expression.getFirstExpression(),
1016: targetSqlType);
1017: switch (expression.getExpressionId()) {
1018: case ExpressionDefines.ADD: {
1019: query.append('+');
1020: break;
1021: }
1022: case ExpressionDefines.SUB: {
1023: query.append('-');
1024: break;
1025: }
1026: case ExpressionDefines.MUL: {
1027: query.append('*');
1028: break;
1029: }
1030: case ExpressionDefines.DIV: {
1031: query.append('/');
1032: break;
1033: }
1034: }
1035: appendExpressionAsSQL(query, expression.getSecondExpression(),
1036: targetSqlType);
1037: query.append(')');
1038: }
1039:
1040: /**
1041: * Appends an SQL fragment for the given object to the given sql statement.
1042: *
1043: * @param query
1044: * @param function
1045: * @param targetSqlType
1046: * @throws FilterEvaluationException
1047: */
1048: protected void appendFunctionAsSQL(StatementBuffer query,
1049: Function function, int targetSqlType)
1050: throws FilterEvaluationException {
1051: if (function instanceof DBFunction) {
1052: query.append(function.getName());
1053: query.append(" (");
1054: List<?> list = function.getArguments();
1055: for (int i = 0; i < list.size(); i++) {
1056: Expression expression = (Expression) list.get(i);
1057: appendExpressionAsSQL(query, expression, targetSqlType);
1058: if (i != list.size() - 1)
1059: query.append(", ");
1060: }
1061: query.append(")");
1062: } else {
1063: Object o = function.evaluate(null);
1064: if (o != null) {
1065: Literal literal = new Literal(o.toString());
1066: appendExpressionAsSQL(query, literal, targetSqlType);
1067: }
1068: }
1069: }
1070:
1071: /**
1072: * Appends an SQL fragment for the given object to the given sql statement.
1073: *
1074: * @param query
1075: * @param operation
1076: * @throws DatastoreException
1077: */
1078: protected void appendLogicalOperationAsSQL(StatementBuffer query,
1079: LogicalOperation operation) throws DatastoreException {
1080: List<?> argumentList = operation.getArguments();
1081: switch (operation.getOperatorId()) {
1082: case OperationDefines.AND: {
1083: for (int i = 0; i < argumentList.size(); i++) {
1084: Operation argument = (Operation) argumentList.get(i);
1085: query.append('(');
1086: appendOperationAsSQL(query, argument);
1087: query.append(')');
1088: if (i != argumentList.size() - 1)
1089: query.append(" AND ");
1090: }
1091: break;
1092: }
1093: case OperationDefines.OR: {
1094: for (int i = 0; i < argumentList.size(); i++) {
1095: Operation argument = (Operation) argumentList.get(i);
1096: query.append('(');
1097: appendOperationAsSQL(query, argument);
1098: query.append(')');
1099: if (i != argumentList.size() - 1)
1100: query.append(" OR ");
1101: }
1102: break;
1103: }
1104: case OperationDefines.NOT: {
1105: Operation argument = (Operation) argumentList.get(0);
1106: query.append("NOT (");
1107: appendOperationAsSQL(query, argument);
1108: query.append(')');
1109: break;
1110: }
1111: }
1112: }
1113:
1114: /**
1115: * Appends an SQL fragment for the given object to the given sql statement.
1116: *
1117: * TODO Handle compound primary keys correctly.
1118: *
1119: * @param query
1120: * @param filter
1121: * @throws DatastoreException
1122: */
1123: protected void appendFeatureFilterAsSQL(StatementBuffer query,
1124: FeatureFilter filter) throws DatastoreException {
1125:
1126: // List list = filter.getFeatureIds();
1127: // Iterator it = list.iterator();
1128: // while (it.hasNext()) {
1129: // FeatureId fid = (FeatureId) it.next();
1130: // MappingField mapping = null;
1131: // DatastoreMapping mapping = featureType.getFidDefinition().getFidFields()[0];
1132: // query.append( ' ' );
1133: // query.append( this.joinTableTree.getAlias() );
1134: // query.append( "." );
1135: // query.append( mapping.getField() );
1136: // query.append( "=?" );
1137: // query.addArgument( fid.getValue() );
1138: // if ( it.hasNext() ) {
1139: // query.append( " OR" );
1140: // }
1141: // }
1142:
1143: if (this .rootFts.length > 1) {
1144: String msg = Messages
1145: .getMessage("DATASTORE_FEATURE_QUERY_MORE_THAN_FEATURE_TYPE");
1146: throw new DatastoreException(msg);
1147: }
1148:
1149: MappedFeatureType rootFt = this .rootFts[0];
1150:
1151: ArrayList<?> list = filter.getFeatureIds();
1152: MappingField mapping = rootFt.getGMLId().getIdFields()[0];
1153: query.append(' ');
1154: String tbl = getRootTableAlias(0);
1155: if (null != tbl && 0 < tbl.length()) {
1156: query.append(tbl);
1157: query.append(".");
1158: }
1159: query.append(mapping.getField());
1160: try {
1161: for (int i = 0; i < list.size(); i++) {
1162: if (0 == i)
1163: query.append(" IN (?");
1164: else
1165: query.append(",?");
1166: String fid = ((org.deegree.model.filterencoding.FeatureId) list
1167: .get(i)).getValue();
1168: Object fidValue = org.deegree.io.datastore.FeatureId
1169: .removeFIDPrefix(fid, rootFt.getGMLId());
1170: query.addArgument(fidValue, mapping.getType());
1171: }
1172: } catch (Exception e) {
1173: LOG.logError("Error converting feature id", e);
1174: }
1175: query.append(")");
1176: }
1177:
1178: /**
1179: * Appends an SQL fragment for the given object to the given sql statement. As this depends on
1180: * the handling of geometry data by the concrete database in use, this method must be
1181: * overwritten by any datastore implementation that has spatial capabilities.
1182: *
1183: * @param query
1184: * @param operation
1185: * @throws DatastoreException
1186: */
1187: protected void appendSpatialOperationAsSQL(
1188: @SuppressWarnings("unused")
1189: StatementBuffer query, @SuppressWarnings("unused")
1190: SpatialOperation operation) throws DatastoreException {
1191: String msg = "Spatial operations are not supported by the WhereBuilder implementation in use: '"
1192: + getClass() + "'";
1193: throw new DatastoreException(msg);
1194: }
1195:
1196: /**
1197: * Prepares the function map for functions with implementation specific names, e.g. upper case
1198: * conversion in ORACLE = UPPER(string); POSTGRES = UPPER(string), and MS Access =
1199: * UCase(string). Default SQL-function name map function 'UPPER' is 'UPPER'. If this function
1200: * shall be used with user databases e.g. SQLServer a specialized WhereBuilder must override
1201: * this method.
1202: */
1203: protected void fillFunctionNameMap() {
1204: functionMap.clear();
1205: functionMap.put("LOWER", "LOWER");
1206: }
1207:
1208: /**
1209: * Get the function with the specified name.
1210: *
1211: * @param name
1212: * the function name
1213: * @return the mapped function name
1214: */
1215: protected String getFunctionName(String name) {
1216: String f = functionMap.get(name);
1217: if (null == f)
1218: f = name;
1219: return f;
1220: }
1221: }
|