001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/datastore/sql/wherebuilder/WhereBuilder.java $ 002 /*---------------------------------------------------------------------------- 003 This file is part of deegree, http://deegree.org/ 004 Copyright (C) 2001-2009 by: 005 Department of Geography, University of Bonn 006 and 007 lat/lon GmbH 008 009 This library is free software; you can redistribute it and/or modify it under 010 the terms of the GNU Lesser General Public License as published by the Free 011 Software Foundation; either version 2.1 of the License, or (at your option) 012 any later version. 013 This library is distributed in the hope that it will be useful, but WITHOUT 014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more 016 details. 017 You should have received a copy of the GNU Lesser General Public License 018 along with this library; if not, write to the Free Software Foundation, Inc., 019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 020 021 Contact information: 022 023 lat/lon GmbH 024 Aennchenstr. 19, 53177 Bonn 025 Germany 026 http://lat-lon.de/ 027 028 Department of Geography, University of Bonn 029 Prof. Dr. Klaus Greve 030 Postfach 1147, 53001 Bonn 031 Germany 032 http://www.geographie.uni-bonn.de/deegree/ 033 034 e-mail: info@deegree.org 035 ----------------------------------------------------------------------------*/ 036 package org.deegree.io.datastore.sql.wherebuilder; 037 038 import static java.sql.Types.DOUBLE; 039 import static java.sql.Types.INTEGER; 040 import static java.sql.Types.SMALLINT; 041 import static org.deegree.model.filterencoding.ExpressionDefines.ADD; 042 import static org.deegree.model.filterencoding.ExpressionDefines.DIV; 043 import static org.deegree.model.filterencoding.ExpressionDefines.MUL; 044 import static org.deegree.model.filterencoding.ExpressionDefines.SUB; 045 import static org.deegree.model.filterencoding.OperationDefines.BBOX; 046 047 import java.sql.Types; 048 import java.util.ArrayList; 049 import java.util.Hashtable; 050 import java.util.Iterator; 051 import java.util.List; 052 import java.util.Stack; 053 054 import org.deegree.framework.log.ILogger; 055 import org.deegree.framework.log.LoggerFactory; 056 import org.deegree.i18n.Messages; 057 import org.deegree.io.datastore.DatastoreException; 058 import org.deegree.io.datastore.PropertyPathResolvingException; 059 import org.deegree.io.datastore.schema.MappedFeaturePropertyType; 060 import org.deegree.io.datastore.schema.MappedFeatureType; 061 import org.deegree.io.datastore.schema.MappedGeometryPropertyType; 062 import org.deegree.io.datastore.schema.MappedPropertyType; 063 import org.deegree.io.datastore.schema.MappedSimplePropertyType; 064 import org.deegree.io.datastore.schema.TableRelation; 065 import org.deegree.io.datastore.schema.content.ConstantContent; 066 import org.deegree.io.datastore.schema.content.MappingField; 067 import org.deegree.io.datastore.schema.content.SQLFunctionCall; 068 import org.deegree.io.datastore.schema.content.SimpleContent; 069 import org.deegree.io.datastore.sql.StatementBuffer; 070 import org.deegree.io.datastore.sql.TableAliasGenerator; 071 import org.deegree.io.datastore.sql.VirtualContentProvider; 072 import org.deegree.model.feature.schema.FeatureType; 073 import org.deegree.model.filterencoding.ArithmeticExpression; 074 import org.deegree.model.filterencoding.ComparisonOperation; 075 import org.deegree.model.filterencoding.ComplexFilter; 076 import org.deegree.model.filterencoding.DBFunction; 077 import org.deegree.model.filterencoding.Expression; 078 import org.deegree.model.filterencoding.ExpressionDefines; 079 import org.deegree.model.filterencoding.FeatureFilter; 080 import org.deegree.model.filterencoding.Filter; 081 import org.deegree.model.filterencoding.FilterEvaluationException; 082 import org.deegree.model.filterencoding.Function; 083 import org.deegree.model.filterencoding.Literal; 084 import org.deegree.model.filterencoding.LogicalOperation; 085 import org.deegree.model.filterencoding.Operation; 086 import org.deegree.model.filterencoding.OperationDefines; 087 import org.deegree.model.filterencoding.PropertyIsBetweenOperation; 088 import org.deegree.model.filterencoding.PropertyIsCOMPOperation; 089 import org.deegree.model.filterencoding.PropertyIsLikeOperation; 090 import org.deegree.model.filterencoding.PropertyIsNullOperation; 091 import org.deegree.model.filterencoding.PropertyName; 092 import org.deegree.model.filterencoding.SpatialOperation; 093 import org.deegree.ogcbase.PropertyPath; 094 import org.deegree.ogcbase.SortProperty; 095 096 /** 097 * Creates SQL-WHERE clauses from OGC filter expressions (to restrict SQL statements to all stored features that match a 098 * given filter). 099 * <p> 100 * Also handles the creation of ORDER-BY clauses. 101 * 102 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a> 103 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a> 104 * @author last edited by: $Author: mschneider $ 105 * 106 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $ 107 */ 108 public class WhereBuilder { 109 110 private static final ILogger LOG = LoggerFactory.getLogger( WhereBuilder.class ); 111 112 // database specific SRS code for unspecified SRS 113 protected static final int SRS_UNDEFINED = -1; 114 115 /** Targeted feature types. */ 116 protected MappedFeatureType[] rootFts; 117 118 /** {@link Filter} for which the corresponding WHERE-clause will be generated. */ 119 protected Filter filter; 120 121 protected SortProperty[] sortProperties; 122 123 protected VirtualContentProvider vcProvider; 124 125 protected QueryTableTree queryTableTree; 126 127 protected List<PropertyPath> filterPropertyPaths = new ArrayList<PropertyPath>(); 128 129 protected List<PropertyPath> sortPropertyPaths = new ArrayList<PropertyPath>(); 130 131 private Hashtable<String, String> functionMap = new Hashtable<String, String>(); 132 133 /** 134 * Creates a new <code>WhereBuilder</code> instance. 135 * 136 * @param rootFts 137 * selected feature types, more than one type means that the types are joined 138 * @param aliases 139 * aliases for the feature types, may be null (must have same length as rootFts otherwise) 140 * @param filter 141 * @param sortProperties 142 * @param aliasGenerator 143 * aliasGenerator to be used to generate table aliases, may be null 144 * @param vcProvider 145 * @throws DatastoreException 146 */ 147 public WhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter, SortProperty[] sortProperties, 148 TableAliasGenerator aliasGenerator, VirtualContentProvider vcProvider ) 149 throws DatastoreException { 150 151 this.rootFts = rootFts; 152 this.queryTableTree = new QueryTableTree( rootFts, aliases, aliasGenerator ); 153 154 // add filter properties to the QueryTableTree 155 this.filter = filter; 156 if ( filter != null ) { 157 assert filter instanceof ComplexFilter || filter instanceof FeatureFilter; 158 buildFilterPropertyNameMap(); 159 for ( PropertyPath property : this.filterPropertyPaths ) { 160 this.queryTableTree.addFilterProperty( property ); 161 } 162 fillFunctionNameMap(); 163 } 164 165 // add sort properties to the QueryTableTree 166 this.sortProperties = sortProperties; 167 if ( sortProperties != null ) { 168 for ( SortProperty property : sortProperties ) { 169 this.sortPropertyPaths.add( property.getSortProperty() ); 170 this.queryTableTree.addSortProperty( property.getSortProperty() ); 171 } 172 } 173 174 this.vcProvider = vcProvider; 175 176 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) { 177 LOG.logDebug( "QueryTableTree:\n" + this.queryTableTree ); 178 } 179 } 180 181 /** 182 * Returns the table alias used for the specified root feature type. 183 * 184 * @param i 185 * index of the requested root feature type 186 * @return the alias used for the root table 187 */ 188 public String getRootTableAlias( int i ) { 189 return this.queryTableTree.getRootNodes()[i].getTableAlias(); 190 } 191 192 /** 193 * Returns the associated <code>Filter</code> instance. 194 * 195 * @return the associated <code>Filter</code> instance 196 */ 197 public Filter getFilter() { 198 return this.filter; 199 } 200 201 protected MappedGeometryPropertyType getGeometryProperty( PropertyName propName ) { 202 PropertyPath propertyPath = propName.getValue(); 203 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath ); 204 assert propertyNode != null; 205 assert propertyNode instanceof GeometryPropertyNode; 206 LOG.logDebug( "Found geometry property with path: " + propertyPath + " and propName: " 207 + propertyNode.getProperty().getName() ); 208 return (MappedGeometryPropertyType) propertyNode.getProperty(); 209 } 210 211 // /** 212 // * Returns the SRS of the {@link MappedGeometryPropertyType} that is identified by the given 213 // * {@link PropertyPath}. 214 // * 215 // * @param propertyPath 216 // * @return the default SRS of the geometry property type 217 // */ 218 // protected String getSrs( PropertyPath propertyPath ) { 219 // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath ); 220 // assert propertyNode != null; 221 // assert propertyNode instanceof GeometryPropertyNode; 222 // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty(); 223 // return geoProp.getSRS().toString(); 224 // } 225 // 226 // /** 227 // * Returns the internal Srs of the {@link MappedGeometryPropertyType} that is identified by 228 // the 229 // * given {@link PropertyPath}. 230 // * 231 // * @param propertyPath 232 // * @return the default SRS of the geometry property type 233 // */ 234 // protected int getInternalSrsCode( PropertyPath propertyPath ) { 235 // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath ); 236 // assert propertyNode != null; 237 // assert propertyNode instanceof GeometryPropertyNode; 238 // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty(); 239 // return geoProp.getMappingField().getSRS(); 240 // } 241 242 protected int getPropertyNameSQLType( PropertyName propertyName ) { 243 244 PropertyPath propertyPath = propertyName.getValue(); 245 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath ); 246 assert propertyNode != null; 247 248 // can be useful if the assertions are not enabled 249 if ( propertyNode == null ) { 250 LOG.logDebug( "Null propertyNode for propertyName: " + propertyName + " with queryTable: " 251 + this.queryTableTree ); 252 } 253 254 MappedPropertyType propertyType = propertyNode.getProperty(); 255 if ( !( propertyType instanceof MappedSimplePropertyType ) ) { 256 String msg = "Error in WhereBuilder: cannot compare against properties of type '" + propertyType.getClass() 257 + "'."; 258 LOG.logError( msg ); 259 throw new RuntimeException( msg ); 260 } 261 262 SimpleContent content = ( (MappedSimplePropertyType) propertyType ).getContent(); 263 if ( !( content instanceof MappingField ) ) { 264 String msg = "Virtual properties are currently ignored in WhereBuilder#getPropertyNameSQLType(PropertyName)."; 265 LOG.logError( msg ); 266 return Types.VARCHAR; 267 } 268 269 int targetSqlType = ( (MappingField) content ).getType(); 270 return targetSqlType; 271 } 272 273 protected void buildFilterPropertyNameMap() 274 throws PropertyPathResolvingException { 275 if ( this.filter instanceof ComplexFilter ) { 276 buildPropertyNameMapFromOperation( ( (ComplexFilter) this.filter ).getOperation() ); 277 } else if ( this.filter instanceof FeatureFilter ) { 278 // TODO 279 // throw new PropertyPathResolvingException( "FeatureFilter not implemented yet." ); 280 } 281 } 282 283 private void buildPropertyNameMapFromOperation( Operation operation ) 284 throws PropertyPathResolvingException { 285 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) { 286 case OperationDefines.TYPE_SPATIAL: { 287 PropertyName name = ( (SpatialOperation) operation ).getPropertyName(); 288 289 // possible in case of BBOX 290 if ( name == null && operation.getOperatorId() == BBOX ) { 291 // use first ft and first geom property 292 FeatureType ft = queryTableTree.getRootNodes()[0].getFeatureType(); 293 name = new PropertyName( ft.getGeometryProperties()[0].getName() ); 294 // modify the operation, since the property name is used later on from the datastores' where builders 295 ( (SpatialOperation) operation ).setPropertyName( name ); 296 } 297 registerPropertyName( name ); 298 break; 299 } 300 case OperationDefines.TYPE_COMPARISON: { 301 buildPropertyNameMap( (ComparisonOperation) operation ); 302 break; 303 } 304 case OperationDefines.TYPE_LOGICAL: { 305 buildPropertyNameMap( (LogicalOperation) operation ); 306 break; 307 } 308 default: { 309 break; 310 } 311 } 312 } 313 314 private void buildPropertyNameMap( ComparisonOperation operation ) 315 throws PropertyPathResolvingException { 316 switch ( operation.getOperatorId() ) { 317 case OperationDefines.PROPERTYISEQUALTO: 318 case OperationDefines.PROPERTYISNOTEQUALTO: 319 case OperationDefines.PROPERTYISLESSTHAN: 320 case OperationDefines.PROPERTYISGREATERTHAN: 321 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: 322 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: { 323 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getFirstExpression() ); 324 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getSecondExpression() ); 325 break; 326 } 327 case OperationDefines.PROPERTYISLIKE: { 328 registerPropertyName( ( (PropertyIsLikeOperation) operation ).getPropertyName() ); 329 break; 330 } 331 case OperationDefines.PROPERTYISNULL: { 332 buildPropertyNameMap( ( (PropertyIsNullOperation) operation ).getPropertyName() ); 333 break; 334 } 335 case OperationDefines.PROPERTYISBETWEEN: { 336 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getLowerBoundary() ); 337 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getUpperBoundary() ); 338 registerPropertyName( ( (PropertyIsBetweenOperation) operation ).getPropertyName() ); 339 break; 340 } 341 default: { 342 break; 343 } 344 } 345 } 346 347 private void buildPropertyNameMap( LogicalOperation operation ) 348 throws PropertyPathResolvingException { 349 List<?> operationList = operation.getArguments(); 350 Iterator<?> it = operationList.iterator(); 351 while ( it.hasNext() ) { 352 buildPropertyNameMapFromOperation( (Operation) it.next() ); 353 } 354 } 355 356 private void buildPropertyNameMap( Expression expression ) 357 throws PropertyPathResolvingException { 358 switch ( expression.getExpressionId() ) { 359 case ExpressionDefines.PROPERTYNAME: { 360 registerPropertyName( (PropertyName) expression ); 361 break; 362 } 363 case ExpressionDefines.ADD: 364 case ExpressionDefines.SUB: 365 case ExpressionDefines.MUL: 366 case ExpressionDefines.DIV: { 367 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getFirstExpression() ); 368 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getSecondExpression() ); 369 break; 370 } 371 case ExpressionDefines.FUNCTION: { 372 // TODO: What about PropertyNames used here? 373 break; 374 } 375 case ExpressionDefines.EXPRESSION: 376 case ExpressionDefines.LITERAL: { 377 break; 378 } 379 } 380 } 381 382 private void registerPropertyName( PropertyName propertyName ) { 383 this.filterPropertyPaths.add( propertyName.getValue() ); 384 } 385 386 /** 387 * Appends the alias-qualified, comma separated list of all tables to be joined in order to represent the associated 388 * filter expression (and possibly feature type joins). 389 * <p> 390 * The list consist of left outer joins ("x LEFT OUTER JOIN y") and cross-product joins ("x,y"): 391 * <ul> 392 * <li>left outer joins are generated for each join that is necessary, because of filter expressions that target 393 * properties stored in related tables (condition joins) 394 * <li>cross-product joins are generated for all feature type root tables (feature type joins) that have not joined 395 * by filter expression joins before</li> 396 * </ul> 397 * 398 * @param query 399 * the list is appended to this <code>SQLStatement</code> 400 */ 401 public void appendJoinTableList( StatementBuffer query ) { 402 403 FeatureTypeNode[] rootNodes = this.queryTableTree.getRootNodes(); 404 appendOuterJoinTableList( query, rootNodes[0] ); 405 for ( int i = 1; i < rootNodes.length; i++ ) { 406 query.append( ',' ); 407 appendOuterJoinTableList( query, rootNodes[i] ); 408 } 409 } 410 411 /** 412 * Appends the alias-qualified, comma separated list of tables to be joined (for one root feature type node). This 413 * includes the join conditions (necessary for the filter conditions), which are generated in ANSI-SQL left outer 414 * join style. 415 * 416 * @param query 417 * the list is appended to this <code>SQLStatement</code> 418 * @param rootNode 419 * one root feature type node in the <code>QueryTableTree</code> 420 */ 421 private void appendOuterJoinTableList( StatementBuffer query, FeatureTypeNode rootNode ) { 422 423 query.append( rootNode.getTable() ); 424 query.append( ' ' ); 425 query.append( rootNode.getTableAlias() ); 426 Stack<PropertyNode> propertyNodeStack = new Stack<PropertyNode>(); 427 PropertyNode[] propertyNodes = rootNode.getPropertyNodes(); 428 for ( int i = 0; i < propertyNodes.length; i++ ) { 429 propertyNodeStack.push( propertyNodes[i] ); 430 } 431 432 while ( !propertyNodeStack.isEmpty() ) { 433 PropertyNode currentNode = propertyNodeStack.pop(); 434 String fromAlias = currentNode.getParent().getTableAlias(); 435 TableRelation[] tableRelations = currentNode.getPathFromParent(); 436 if ( tableRelations != null && tableRelations.length != 0 ) { 437 String[] toAliases = currentNode.getTableAliases(); 438 appendOuterJoins( tableRelations, fromAlias, toAliases, query ); 439 440 } 441 if ( currentNode instanceof FeaturePropertyNode ) { 442 FeaturePropertyNode featurePropertyNode = (FeaturePropertyNode) currentNode; 443 FeatureTypeNode[] childNodes = ( (FeaturePropertyNode) currentNode ).getFeatureTypeNodes(); 444 for ( int i = 0; i < childNodes.length; i++ ) { 445 // TODO is this way of skipping root tables o.k.? 446 if ( childNodes[i].getFtAlias() != null ) { 447 continue; 448 } 449 String toTable = childNodes[i].getTable(); 450 String toAlias = childNodes[i].getTableAlias(); 451 String[] pathAliases = featurePropertyNode.getTableAliases(); 452 if ( pathAliases.length == 0 ) { 453 fromAlias = featurePropertyNode.getParent().getTableAlias(); 454 } else { 455 fromAlias = pathAliases[pathAliases.length - 1]; 456 } 457 MappedFeaturePropertyType content = (MappedFeaturePropertyType) featurePropertyNode.getProperty(); 458 TableRelation[] relations = content.getTableRelations(); 459 TableRelation relation = relations[relations.length - 1]; 460 appendOuterJoin( relation, fromAlias, toAlias, toTable, query ); 461 propertyNodes = childNodes[i].getPropertyNodes(); 462 for ( int j = 0; j < propertyNodes.length; j++ ) { 463 propertyNodeStack.push( propertyNodes[j] ); 464 } 465 } 466 } 467 } 468 } 469 470 private void appendOuterJoins( TableRelation[] tableRelation, String fromAlias, String[] toAliases, 471 StatementBuffer query ) { 472 for ( int i = 0; i < toAliases.length; i++ ) { 473 String toAlias = toAliases[i]; 474 appendOuterJoin( tableRelation[i], fromAlias, toAlias, query ); 475 fromAlias = toAlias; 476 } 477 } 478 479 private void appendOuterJoin( TableRelation tableRelation, String fromAlias, String toAlias, StatementBuffer query ) { 480 481 query.append( " LEFT OUTER JOIN " ); 482 query.append( tableRelation.getToTable() ); 483 query.append( " " ); 484 query.append( toAlias ); 485 query.append( " ON " ); 486 487 MappingField[] fromFields = tableRelation.getFromFields(); 488 MappingField[] toFields = tableRelation.getToFields(); 489 for ( int i = 0; i < fromFields.length; i++ ) { 490 if ( toAlias.equals( "" ) ) { 491 toAlias = tableRelation.getToTable(); 492 } 493 query.append( toAlias ); 494 query.append( "." ); 495 query.append( toFields[i].getField() ); 496 query.append( "=" ); 497 if ( fromAlias.equals( "" ) ) { 498 fromAlias = tableRelation.getFromTable(); 499 } 500 query.append( fromAlias ); 501 query.append( "." ); 502 query.append( fromFields[i].getField() ); 503 if ( i != fromFields.length - 1 ) { 504 query.append( " AND " ); 505 } 506 } 507 } 508 509 private void appendOuterJoin( TableRelation tableRelation, String fromAlias, String toAlias, String toTable, 510 StatementBuffer query ) { 511 512 query.append( " LEFT OUTER JOIN " ); 513 query.append( toTable ); 514 query.append( " " ); 515 query.append( toAlias ); 516 query.append( " ON " ); 517 518 MappingField[] fromFields = tableRelation.getFromFields(); 519 MappingField[] toFields = tableRelation.getToFields(); 520 for ( int i = 0; i < fromFields.length; i++ ) { 521 if ( toAlias.equals( "" ) ) { 522 toAlias = toTable; 523 } 524 query.append( toAlias ); 525 query.append( "." ); 526 query.append( toFields[i].getField() ); 527 query.append( "=" ); 528 if ( fromAlias.equals( "" ) ) { 529 fromAlias = tableRelation.getFromTable(); 530 } 531 query.append( fromAlias ); 532 query.append( "." ); 533 query.append( fromFields[i].getField() ); 534 if ( i != fromFields.length - 1 ) { 535 query.append( " AND " ); 536 } 537 } 538 } 539 540 /** 541 * Appends an SQL WHERE-condition corresponding to the <code>Filter</code> to the given SQL statement. 542 * 543 * @param query 544 * @throws DatastoreException 545 */ 546 public final void appendWhereCondition( StatementBuffer query ) 547 throws DatastoreException { 548 if ( filter instanceof ComplexFilter ) { 549 query.append( " WHERE " ); 550 appendComplexFilterAsSQL( query, (ComplexFilter) filter ); 551 } else if ( filter instanceof FeatureFilter ) { 552 FeatureFilter featureFilter = (FeatureFilter) filter; 553 if ( featureFilter.getFeatureIds().size() > 0 ) { 554 query.append( " WHERE " ); 555 appendFeatureFilterAsSQL( query, featureFilter ); 556 } 557 } else if ( filter != null ) { 558 assert false : "Unexpected filter type: " + filter.getClass(); 559 } 560 } 561 562 /** 563 * Appends an SQL "ORDER BY"-condition that corresponds to the sort properties of the query to the given SQL 564 * statement. 565 * 566 * @param query 567 * @throws DatastoreException 568 */ 569 public void appendOrderByCondition( StatementBuffer query ) 570 throws DatastoreException { 571 572 // ignore properties that are unsuitable as sort criteria (like constant properties) 573 List<SortProperty> sortProps = new ArrayList<SortProperty>(); 574 575 if ( this.sortProperties != null && this.sortProperties.length != 0 ) { 576 for ( int i = 0; i < this.sortProperties.length; i++ ) { 577 SortProperty sortProperty = this.sortProperties[i]; 578 PropertyPath path = sortProperty.getSortProperty(); 579 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( path ); 580 MappedPropertyType pt = propertyNode.getProperty(); 581 if ( !( pt instanceof MappedSimplePropertyType ) ) { 582 String msg = Messages.getMessage( "DATASTORE_INVALID_SORT_PROPERTY", pt.getName() ); 583 throw new DatastoreException( msg ); 584 } 585 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent(); 586 if ( content.isSortable() ) { 587 sortProps.add( sortProperty ); 588 } else { 589 String msg = "Ignoring sort criterion - property '" + path.getAsString() 590 + "' is not suitable for sorting."; 591 LOG.logDebug( msg ); 592 } 593 } 594 } 595 596 if ( sortProps.size() > 0 ) { 597 query.append( " ORDER BY " ); 598 } 599 600 for ( int i = 0; i < sortProps.size(); i++ ) { 601 SortProperty sortProperty = sortProps.get( i ); 602 PropertyPath path = sortProperty.getSortProperty(); 603 appendPropertyPathAsSQL( query, path ); 604 if ( !sortProperty.getSortOrder() ) { 605 query.append( " DESC" ); 606 } 607 if ( i != sortProps.size() - 1 ) { 608 query.append( ',' ); 609 } 610 } 611 } 612 613 /** 614 * Appends an SQL fragment for the given object. 615 * 616 * @param query 617 * @param filter 618 * @throws DatastoreException 619 */ 620 protected void appendComplexFilterAsSQL( StatementBuffer query, ComplexFilter filter ) 621 throws DatastoreException { 622 appendOperationAsSQL( query, filter.getOperation() ); 623 } 624 625 /** 626 * Appends an SQL fragment for the given object to the given sql statement. 627 * 628 * @param query 629 * @param operation 630 * @throws DatastoreException 631 */ 632 protected void appendOperationAsSQL( StatementBuffer query, Operation operation ) 633 throws DatastoreException { 634 635 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) { 636 case OperationDefines.TYPE_SPATIAL: { 637 appendSpatialOperationAsSQL( query, (SpatialOperation) operation ); 638 break; 639 } 640 case OperationDefines.TYPE_COMPARISON: { 641 try { 642 appendComparisonOperationAsSQL( query, (ComparisonOperation) operation ); 643 } catch ( FilterEvaluationException e ) { 644 new DatastoreException( e.getMessage(), e ); 645 } 646 break; 647 } 648 case OperationDefines.TYPE_LOGICAL: { 649 appendLogicalOperationAsSQL( query, (LogicalOperation) operation ); 650 break; 651 } 652 default: { 653 break; 654 } 655 } 656 } 657 658 /** 659 * Appends an SQL fragment for the given object to the given sql statement. 660 * 661 * @param query 662 * @param operation 663 * @throws FilterEvaluationException 664 */ 665 protected void appendComparisonOperationAsSQL( StatementBuffer query, ComparisonOperation operation ) 666 throws FilterEvaluationException { 667 switch ( operation.getOperatorId() ) { 668 case OperationDefines.PROPERTYISEQUALTO: 669 case OperationDefines.PROPERTYISNOTEQUALTO: 670 case OperationDefines.PROPERTYISLESSTHAN: 671 case OperationDefines.PROPERTYISGREATERTHAN: 672 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: 673 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: { 674 appendPropertyIsCOMPOperationAsSQL( query, (PropertyIsCOMPOperation) operation ); 675 break; 676 } 677 case OperationDefines.PROPERTYISLIKE: { 678 appendPropertyIsLikeOperationAsSQL( query, (PropertyIsLikeOperation) operation ); 679 break; 680 } 681 case OperationDefines.PROPERTYISNULL: { 682 appendPropertyIsNullOperationAsSQL( query, (PropertyIsNullOperation) operation ); 683 break; 684 } 685 case OperationDefines.PROPERTYISBETWEEN: { 686 appendPropertyIsBetweenOperationAsSQL( query, (PropertyIsBetweenOperation) operation ); 687 break; 688 } 689 } 690 } 691 692 /** 693 * Appends an SQL fragment for the given object to the given sql statement. 694 * 695 * @param query 696 * @param operation 697 * @throws FilterEvaluationException 698 */ 699 protected void appendPropertyIsCOMPOperationAsSQL( StatementBuffer query, PropertyIsCOMPOperation operation ) 700 throws FilterEvaluationException { 701 Expression firstExpr = operation.getFirstExpression(); 702 if ( !( firstExpr instanceof PropertyName ) ) { 703 throw new IllegalArgumentException( "First expression in a comparison must " 704 + "always be a 'PropertyName' element." ); 705 } 706 int targetSqlType = getPropertyNameSQLType( (PropertyName) firstExpr ); 707 if ( operation.isMatchCase() ) { 708 appendExpressionAsSQL( query, firstExpr, targetSqlType ); 709 } else { 710 List<Expression> list = new ArrayList<Expression>(); 711 list.add( firstExpr ); 712 Function func = new DBFunction( getFunctionName( "LOWER" ), list ); 713 appendFunctionAsSQL( query, func, targetSqlType ); 714 } 715 switch ( operation.getOperatorId() ) { 716 case OperationDefines.PROPERTYISEQUALTO: { 717 query.append( " = " ); 718 break; 719 } 720 case OperationDefines.PROPERTYISNOTEQUALTO: { 721 query.append( " <> " ); 722 break; 723 } 724 case OperationDefines.PROPERTYISLESSTHAN: { 725 query.append( " < " ); 726 break; 727 } 728 case OperationDefines.PROPERTYISGREATERTHAN: { 729 query.append( " > " ); 730 break; 731 } 732 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: { 733 query.append( " <= " ); 734 break; 735 } 736 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: { 737 query.append( " >= " ); 738 break; 739 } 740 } 741 if ( operation.isMatchCase() ) { 742 appendExpressionAsSQL( query, operation.getSecondExpression(), targetSqlType ); 743 } else { 744 List<Expression> list = new ArrayList<Expression>(); 745 list.add( operation.getSecondExpression() ); 746 Function func = new DBFunction( getFunctionName( "LOWER" ), list ); 747 appendFunctionAsSQL( query, func, targetSqlType ); 748 } 749 } 750 751 /** 752 * Appends an SQL fragment for the given object to the given sql statement. 753 * 754 * @param query 755 * @param operation 756 * @throws FilterEvaluationException 757 */ 758 protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation ) 759 throws FilterEvaluationException { 760 761 String literal = operation.getLiteral().getValue(); 762 String escape = "" + operation.getEscapeChar(); 763 String wildCard = "" + operation.getWildCard(); 764 String singleChar = "" + operation.getSingleChar(); 765 766 SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape ); 767 String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() ); 768 769 int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() ); 770 771 // if isMatchCase == false surround first argument with LOWER (...) and convert characters 772 // in second argument to lower case 773 if ( operation.isMatchCase() ) { 774 appendPropertyNameAsSQL( query, operation.getPropertyName() ); 775 } else { 776 List<Expression> list = new ArrayList<Expression>(); 777 list.add( operation.getPropertyName() ); 778 Function func = new DBFunction( getFunctionName( "LOWER" ), list ); 779 appendFunctionAsSQL( query, func, targetSqlType ); 780 } 781 782 query.append( " LIKE ? ESCAPE ?" ); 783 query.addArgument( sqlEncoded, Types.VARCHAR ); 784 query.addArgument( "\\", Types.VARCHAR ); 785 } 786 787 /** 788 * Appends an SQL fragment for the given object to the given sql statement. 789 * 790 * @param query 791 * @param operation 792 */ 793 protected void appendPropertyIsNullOperationAsSQL( StatementBuffer query, PropertyIsNullOperation operation ) { 794 appendPropertyNameAsSQL( query, operation.getPropertyName() ); 795 query.append( " IS NULL" ); 796 } 797 798 /** 799 * Appends an SQL fragment for the given object to the given sql statement. 800 * 801 * @param query 802 * @param operation 803 * @throws FilterEvaluationException 804 */ 805 protected void appendPropertyIsBetweenOperationAsSQL( StatementBuffer query, PropertyIsBetweenOperation operation ) 806 throws FilterEvaluationException { 807 808 PropertyName propertyName = operation.getPropertyName(); 809 int targetSqlType = getPropertyNameSQLType( propertyName ); 810 appendExpressionAsSQL( query, operation.getLowerBoundary(), targetSqlType ); 811 query.append( " <= " ); 812 appendPropertyNameAsSQL( query, propertyName ); 813 query.append( " AND " ); 814 appendPropertyNameAsSQL( query, propertyName ); 815 query.append( " <= " ); 816 appendExpressionAsSQL( query, operation.getUpperBoundary(), targetSqlType ); 817 } 818 819 /** 820 * Appends an SQL fragment for the given object to the given sql statement. 821 * 822 * @param query 823 * @param expression 824 * @param targetSqlType 825 * sql type code to be used for literals at the bottom of the expression tree 826 * @throws FilterEvaluationException 827 */ 828 protected void appendExpressionAsSQL( StatementBuffer query, Expression expression, int targetSqlType ) 829 throws FilterEvaluationException { 830 switch ( expression.getExpressionId() ) { 831 case ExpressionDefines.PROPERTYNAME: { 832 appendPropertyNameAsSQL( query, (PropertyName) expression ); 833 break; 834 } 835 case ExpressionDefines.LITERAL: { 836 appendLiteralAsSQL( query, (Literal) expression, targetSqlType ); 837 break; 838 } 839 case ExpressionDefines.FUNCTION: { 840 Function function = (Function) expression; 841 appendFunctionAsSQL( query, function, targetSqlType ); 842 break; 843 } 844 case ExpressionDefines.ADD: 845 case ExpressionDefines.SUB: 846 case ExpressionDefines.MUL: 847 case ExpressionDefines.DIV: { 848 appendArithmeticExpressionAsSQL( query, (ArithmeticExpression) expression, targetSqlType ); 849 break; 850 } 851 case ExpressionDefines.EXPRESSION: 852 default: { 853 throw new IllegalArgumentException( "Unexpected expression type: " + expression.getExpressionName() ); 854 } 855 } 856 } 857 858 /** 859 * Appends an SQL fragment for the given object to the given sql statement. 860 * 861 * @param query 862 * @param literal 863 * @param targetSqlType 864 */ 865 protected void appendLiteralAsSQL( StatementBuffer query, Literal literal, int targetSqlType ) { 866 query.append( '?' ); 867 query.addArgument( literal.getValue(), targetSqlType ); 868 } 869 870 /** 871 * Appends an SQL fragment for the given object to the given sql statement. 872 * 873 * @param query 874 * @param propertyName 875 */ 876 protected void appendPropertyNameAsSQL( StatementBuffer query, PropertyName propertyName ) { 877 878 PropertyPath propertyPath = propertyName.getValue(); 879 appendPropertyPathAsSQL( query, propertyPath ); 880 } 881 882 /** 883 * Appends an SQL fragment for the given object to the given sql statement. 884 * 885 * @param query 886 * @param propertyPath 887 */ 888 protected void appendPropertyPathAsSQL( StatementBuffer query, PropertyPath propertyPath ) { 889 890 LOG.logDebug( "Looking up '" + propertyPath + "' in the query table tree." ); 891 MappingField mappingField = null; 892 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath ); 893 assert ( propertyNode != null ); 894 if ( propertyNode instanceof SimplePropertyNode ) { 895 SimpleContent content = ( (MappedSimplePropertyType) ( propertyNode.getProperty() ) ).getContent(); 896 if ( !( content instanceof MappingField ) ) { 897 if ( content instanceof ConstantContent ) { 898 query.append( "'" + ( (ConstantContent) content ).getValue() + "'" ); 899 return; 900 } else if ( content instanceof SQLFunctionCall ) { 901 SQLFunctionCall call = (SQLFunctionCall) content; 902 String tableAlias = null; 903 String[] tableAliases = propertyNode.getTableAliases(); 904 if ( tableAliases == null || tableAliases.length == 0 ) { 905 tableAlias = propertyNode.getParent().getTableAlias(); 906 } else { 907 tableAlias = tableAliases[tableAliases.length - 1]; 908 } 909 this.vcProvider.appendSQLFunctionCall( query, tableAlias, call ); 910 return; 911 } 912 String msg = "Virtual properties are currently ignored in WhereBuilder#appendPropertyPathAsSQL(StatementBuffer,PropertyPath)."; 913 LOG.logError( msg ); 914 assert false; 915 } 916 mappingField = (MappingField) content; 917 } else if ( propertyNode instanceof GeometryPropertyNode ) { 918 mappingField = ( (MappedGeometryPropertyType) propertyNode.getProperty() ).getMappingField(); 919 } else { 920 String msg = "Internal error in WhereBuilder: unhandled PropertyNode type: '" 921 + propertyNode.getClass().getName() + "'."; 922 LOG.logError( msg ); 923 throw new RuntimeException( msg ); 924 } 925 String tableAlias = null; 926 String[] tableAliases = propertyNode.getTableAliases(); 927 if ( tableAliases == null || tableAliases.length == 0 ) { 928 tableAlias = propertyNode.getParent().getTableAlias(); 929 } else { 930 tableAlias = tableAliases[tableAliases.length - 1]; 931 } 932 if ( tableAlias != "" ) { 933 query.append( tableAlias ); 934 query.append( '.' ); 935 } else { 936 query.append( mappingField.getTable() ); 937 query.append( '.' ); 938 } 939 query.append( mappingField.getField() ); 940 } 941 942 /** 943 * Appends an SQL fragment for the given object to the given sql statement. 944 * 945 * @param query 946 * @param expression 947 * @param targetSqlType 948 * @throws FilterEvaluationException 949 */ 950 protected void appendArithmeticExpressionAsSQL( StatementBuffer query, ArithmeticExpression expression, 951 int targetSqlType ) 952 throws FilterEvaluationException { 953 954 // quirk to enable SQL-level type conversions of numbers. We really should use numbers as literals in the SQL 955 // string. 956 switch ( targetSqlType ) { 957 case INTEGER: 958 case SMALLINT: { 959 targetSqlType = DOUBLE; 960 } 961 } 962 963 query.append( '(' ); 964 appendExpressionAsSQL( query, expression.getFirstExpression(), targetSqlType ); 965 switch ( expression.getExpressionId() ) { 966 case ADD: { 967 query.append( '+' ); 968 break; 969 } 970 case SUB: { 971 query.append( '-' ); 972 break; 973 } 974 case MUL: { 975 query.append( '*' ); 976 break; 977 } 978 case DIV: { 979 query.append( '/' ); 980 break; 981 } 982 } 983 appendExpressionAsSQL( query, expression.getSecondExpression(), targetSqlType ); 984 query.append( ')' ); 985 } 986 987 /** 988 * Appends an SQL fragment for the given object to the given sql statement. 989 * 990 * @param query 991 * @param function 992 * @param targetSqlType 993 * @throws FilterEvaluationException 994 */ 995 protected void appendFunctionAsSQL( StatementBuffer query, Function function, int targetSqlType ) 996 throws FilterEvaluationException { 997 if ( function instanceof DBFunction ) { 998 query.append( function.getName() ); 999 query.append( " (" ); 1000 List<?> list = function.getArguments(); 1001 for ( int i = 0; i < list.size(); i++ ) { 1002 Expression expression = (Expression) list.get( i ); 1003 appendExpressionAsSQL( query, expression, targetSqlType ); 1004 if ( i != list.size() - 1 ) 1005 query.append( ", " ); 1006 } 1007 query.append( ")" ); 1008 } else { 1009 Object o = function.evaluate( null ); 1010 if ( o != null ) { 1011 Literal literal = new Literal( o.toString() ); 1012 appendExpressionAsSQL( query, literal, targetSqlType ); 1013 } 1014 } 1015 } 1016 1017 /** 1018 * Appends an SQL fragment for the given object to the given sql statement. 1019 * 1020 * @param query 1021 * @param operation 1022 * @throws DatastoreException 1023 */ 1024 protected void appendLogicalOperationAsSQL( StatementBuffer query, LogicalOperation operation ) 1025 throws DatastoreException { 1026 List<?> argumentList = operation.getArguments(); 1027 switch ( operation.getOperatorId() ) { 1028 case OperationDefines.AND: { 1029 for ( int i = 0; i < argumentList.size(); i++ ) { 1030 Operation argument = (Operation) argumentList.get( i ); 1031 query.append( '(' ); 1032 appendOperationAsSQL( query, argument ); 1033 query.append( ')' ); 1034 if ( i != argumentList.size() - 1 ) 1035 query.append( " AND " ); 1036 } 1037 break; 1038 } 1039 case OperationDefines.OR: { 1040 for ( int i = 0; i < argumentList.size(); i++ ) { 1041 Operation argument = (Operation) argumentList.get( i ); 1042 query.append( '(' ); 1043 appendOperationAsSQL( query, argument ); 1044 query.append( ')' ); 1045 if ( i != argumentList.size() - 1 ) 1046 query.append( " OR " ); 1047 } 1048 break; 1049 } 1050 case OperationDefines.NOT: { 1051 Operation argument = (Operation) argumentList.get( 0 ); 1052 query.append( "NOT (" ); 1053 appendOperationAsSQL( query, argument ); 1054 query.append( ')' ); 1055 break; 1056 } 1057 } 1058 } 1059 1060 /** 1061 * Appends an SQL fragment for the given object to the given sql statement. 1062 * 1063 * TODO Handle compound primary keys correctly. 1064 * 1065 * @param query 1066 * @param filter 1067 * @throws DatastoreException 1068 */ 1069 protected void appendFeatureFilterAsSQL( StatementBuffer query, FeatureFilter filter ) 1070 throws DatastoreException { 1071 1072 // List list = filter.getFeatureIds(); 1073 // Iterator it = list.iterator(); 1074 // while (it.hasNext()) { 1075 // FeatureId fid = (FeatureId) it.next(); 1076 // MappingField mapping = null; 1077 // DatastoreMapping mapping = featureType.getFidDefinition().getFidFields()[0]; 1078 // query.append( ' ' ); 1079 // query.append( this.joinTableTree.getAlias() ); 1080 // query.append( "." ); 1081 // query.append( mapping.getField() ); 1082 // query.append( "=?" ); 1083 // query.addArgument( fid.getValue() ); 1084 // if ( it.hasNext() ) { 1085 // query.append( " OR" ); 1086 // } 1087 // } 1088 1089 if ( this.rootFts.length > 1 ) { 1090 String msg = Messages.getMessage( "DATASTORE_FEATURE_QUERY_MORE_THAN_FEATURE_TYPE" ); 1091 throw new DatastoreException( msg ); 1092 } 1093 1094 MappedFeatureType rootFt = this.rootFts[0]; 1095 MappingField[] idFields = rootFt.getGMLId().getIdFields(); 1096 MappingField idField = null; 1097 String tbl = getRootTableAlias( 0 ); 1098 ArrayList<?> list = filter.getFeatureIds(); 1099 try { 1100 for ( int i = 0; i < idFields.length; i++ ) { 1101 idField = idFields[i]; 1102 if ( i == 0 ) { 1103 query.append( ' ' ); 1104 } else { 1105 query.append( " AND " ); 1106 } 1107 if ( null != tbl && 0 < tbl.length() ) { 1108 query.append( tbl ); 1109 query.append( "." ); 1110 } 1111 query.append( idField.getField() ); 1112 for ( int j = 0; j < list.size(); j++ ) { 1113 if ( j == 0 ) { 1114 query.append( " IN (?" ); 1115 } else { 1116 query.append( ",?" ); 1117 } 1118 if ( j == list.size() - 1 ) { 1119 query.append( ")" ); 1120 } 1121 String fid = ( (org.deegree.model.filterencoding.FeatureId) list.get( j ) ).getValue(); 1122 Object fidValue = org.deegree.io.datastore.FeatureId.removeFIDPrefix( fid, rootFt.getGMLId() ); 1123 if ( idFields.length > 1 ) { // Equal to: if ( fidValue instanceof Object[] ) { 1124 fidValue = ( (Object[]) fidValue )[i]; 1125 } 1126 query.addArgument( fidValue, idField.getType() ); 1127 } 1128 } 1129 } catch ( Exception e ) { 1130 LOG.logError( "Error converting feature id", e ); 1131 } 1132 } 1133 1134 /** 1135 * Appends an SQL fragment for the given object to the given sql statement. As this depends on the handling of 1136 * geometry data by the concrete database in use, this method must be overwritten by any datastore implementation 1137 * that has spatial capabilities. 1138 * 1139 * @param query 1140 * @param operation 1141 * @throws DatastoreException 1142 */ 1143 protected void appendSpatialOperationAsSQL( @SuppressWarnings("unused") StatementBuffer query, 1144 @SuppressWarnings("unused") SpatialOperation operation ) 1145 throws DatastoreException { 1146 String msg = "Spatial operations are not supported by the WhereBuilder implementation in use: '" + getClass() 1147 + "'"; 1148 throw new DatastoreException( msg ); 1149 } 1150 1151 /** 1152 * Prepares the function map for functions with implementation specific names, e.g. upper case conversion in ORACLE 1153 * = UPPER(string); POSTGRES = UPPER(string), and MS Access = UCase(string). Default SQL-function name map function 1154 * 'UPPER' is 'UPPER'. If this function shall be used with user databases e.g. SQLServer a specialized WhereBuilder 1155 * must override this method. 1156 */ 1157 protected void fillFunctionNameMap() { 1158 functionMap.clear(); 1159 functionMap.put( "LOWER", "LOWER" ); 1160 } 1161 1162 /** 1163 * Get the function with the specified name. 1164 * 1165 * @param name 1166 * the function name 1167 * @return the mapped function name 1168 */ 1169 protected String getFunctionName( String name ) { 1170 String f = functionMap.get( name ); 1171 if ( null == f ) 1172 f = name; 1173 return f; 1174 } 1175 }