[teiid-commits] teiid SVN: r1255 - in trunk/connectors: connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator and 4 other directories.

Author: shawkins Date: 2009-08-17 23:01:39 -0400 (Mon, 17 Aug 2009) New Revision: 1255 Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSpatialFunctionModifier.java trunk/connectors/connector-jdbc/src/main/resources/OracleSpatialFunctions.xmi Removed: trunk/connectors/sandbox/connector-oracle-spatial/ Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java trunk/connectors/connector-jdbc/src/main/resources/org/teiid/connector/jdbc/i18n.properties trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java trunk/connectors/sandbox/pom.xml Log: TEIID-787 merging oracle and oracle spatial connectors. Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java 2009-08-18 03:01:39 UTC (rev 1255) @@ -39,8 +39,8 @@ /** * @see com.metamatrix.data.ConnectorCapabilities#getSupportedFunctions() */ - public List getSupportedFunctions() { - List supportedFunctions = new ArrayList(); + public List<String> getSupportedFunctions() { + List<String> supportedFunctions = new ArrayList<String>(); supportedFunctions.addAll(super.getSupportedFunctions()); supportedFunctions.add("ABS"); //$NON-NLS-1$ supportedFunctions.add("ACOS"); //$NON-NLS-1$ @@ -104,7 +104,13 @@ supportedFunctions.add("CONVERT"); //$NON-NLS-1$ supportedFunctions.add("IFNULL"); //$NON-NLS-1$ supportedFunctions.add("NVL"); //$NON-NLS-1$ - supportedFunctions.add("COALESCE"); //$NON-NLS-1$ + supportedFunctions.add("COALESCE"); //$NON-NLS-1$ + + supportedFunctions.add(OracleSQLTranslator.RELATE); + supportedFunctions.add(OracleSQLTranslator.NEAREST_NEIGHBOR); + supportedFunctions.add(OracleSQLTranslator.FILTER); + supportedFunctions.add(OracleSQLTranslator.NEAREST_NEIGHBOR_DISTANCE); + supportedFunctions.add(OracleSQLTranslator.WITHIN_DISTANCE); return supportedFunctions; } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-18 03:01:39 UTC (rev 1255) @@ -31,6 +31,7 @@ import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; +import java.util.Collection; import java.util.List; import org.teiid.connector.api.ConnectorCapabilities; @@ -38,27 +39,42 @@ import org.teiid.connector.api.ConnectorException; import org.teiid.connector.api.ExecutionContext; import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.api.TypeFacility; import org.teiid.connector.jdbc.JDBCPlugin; import org.teiid.connector.jdbc.translator.AliasModifier; import org.teiid.connector.jdbc.translator.ExtractFunctionModifier; import org.teiid.connector.jdbc.translator.Translator; import org.teiid.connector.language.ICommand; +import org.teiid.connector.language.ICriteria; import org.teiid.connector.language.IElement; +import org.teiid.connector.language.IFunction; import org.teiid.connector.language.IGroup; import org.teiid.connector.language.IInsert; import org.teiid.connector.language.IInsertExpressionValueSource; import org.teiid.connector.language.ILimit; +import org.teiid.connector.language.IQuery; import org.teiid.connector.language.IQueryCommand; +import org.teiid.connector.language.ISelect; import org.teiid.connector.language.ISelectSymbol; import org.teiid.connector.language.ISetQuery.Operation; import org.teiid.connector.metadata.runtime.Element; +import org.teiid.connector.visitor.util.CollectorVisitor; import org.teiid.connector.visitor.util.SQLReservedWords; /** */ -public class OracleSQLTranslator extends Translator { +public class OracleSQLTranslator extends Translator { + /* + * Spatial Functions + */ + public static final String RELATE = "sdo_relate"; //$NON-NLS-1$ + public static final String NEAREST_NEIGHBOR = "sdo_nn"; //$NON-NLS-1$ + public static final String FILTER = "sdo_filter"; //$NON-NLS-1$ + public static final String WITHIN_DISTANCE = "sdo_within_distance"; //$NON-NLS-1$ + public static final String NEAREST_NEIGHBOR_DISTANCE = "sdo_nn_distance"; //$NON-NLS-1$ + public final static String HINT_PREFIX = "/*+"; //$NON-NLS-1$ public final static String DUAL = "DUAL"; //$NON-NLS-1$ public final static String ROWNUM = "ROWNUM"; //$NON-NLS-1$ @@ -91,10 +107,45 @@ registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory())); + registerFunctionModifier(RELATE, new OracleSpatialFunctionModifier()); + registerFunctionModifier(NEAREST_NEIGHBOR, new OracleSpatialFunctionModifier()); + registerFunctionModifier(FILTER, new OracleSpatialFunctionModifier()); + registerFunctionModifier(WITHIN_DISTANCE, new OracleSpatialFunctionModifier()); } @Override public ICommand modifyCommand(ICommand command, ExecutionContext context) throws ConnectorException { + if (command instanceof IQuery) { + IQuery query = (IQuery)command; + + ISelect select = ((IQuery)command).getSelect(); + List<ISelectSymbol> symbols = select.getSelectSymbols(); + + Collection<IFunction> functions = CollectorVisitor.collectObjects(IFunction.class, select); + for (IFunction function : functions) { + if (function.getName().equalsIgnoreCase("SDO_NN_DISTANCE")) {//$NON-NLS-1$ + ICriteria criteria = query.getWhere(); + if(criteria == null || criteria.toString().indexOf("SDO_NN") == -1){ //$NON-NLS-1$ + throw(new ConnectorException( + JDBCPlugin.Util.getString("OracleSpatialSQLTranslator.SDO_NN_DEPENDENCY_ERROR"))); //$NON-NLS-1$ + } + break; + } + } + + for (int i = 0; i < symbols.size(); i++) { + ISelectSymbol symbol = symbols.get(i); + if (symbol.getExpression().getType().equals(Object.class)) { + String outName = symbol.getOutputName(); + int lIndx = outName.lastIndexOf("."); //$NON-NLS-1$ + symbol.setOutputName(outName.substring(lIndx + 1)); + symbol.setExpression(getLanguageFactory().createLiteral(null, TypeFacility.RUNTIME_TYPES.OBJECT)); + symbol.setAlias(true); + } + } + return query; + } + if (!(command instanceof IInsert)) { return command; } @@ -236,6 +287,25 @@ } } } + + if (command instanceof IQuery) { + // + // This simple algorithm determines the hint which will be added to the + // query. + // Right now, we look through all functions passed in the query + // (returned as a collection) + // Then we check if any of those functions contain the strings 'sdo' and + // 'relate' + // If so, the ORDERED hint is added, if not, it isn't + Collection<IFunction> col = CollectorVisitor.collectObjects(IFunction.class, command); + for (IFunction func : col) { + String funcName = func.getName().toUpperCase(); + int indx1 = funcName.indexOf("SDO"); //$NON-NLS-1$ + int indx2 = funcName.indexOf("RELATE"); //$NON-NLS-1$ + if (indx1 >= 0 && indx2 > indx1) + return comment + "/*+ ORDERED */ "; //$NON-NLS-1$ + } + } return comment; } Copied: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSpatialFunctionModifier.java (from rev 1253, trunk/connectors/sandbox/connector-oracle-spatial/src/main/java/com/metamatrix/connector/jdbc/oracle/spatial/OracleSpatialFunctionModifier.java) =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSpatialFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSpatialFunctionModifier.java 2009-08-18 03:01:39 UTC (rev 1255) @@ -0,0 +1,67 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * This library is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 2.1 of the License, or (at your option) any later version. + * + * This library is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with this library; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + * 02110-1301 USA. + */ + +package org.teiid.connector.jdbc.oracle; + +import java.util.ArrayList; +import java.util.List; + +import org.teiid.connector.jdbc.translator.BasicFunctionModifier; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.language.ILiteral; + + +public class OracleSpatialFunctionModifier extends BasicFunctionModifier { + + /** + * If either of the first two parameters are a Literal String, then we need to put the literal itself in the SQL + * to be passed to Oracle, without the tick marks + */ + public List<?> translate(IFunction function) { + List<IExpression> params = function.getParameters(); + List<Object> objs = new ArrayList<Object>(); + objs.add(function.getName()); + objs.add("("); //$NON-NLS-1$ + addParamWithConversion(objs, params.get(0)); + objs.add(", "); //$NON-NLS-1$ + + addParamWithConversion(objs, params.get(1)); + for (int i = 2; i < params.size(); i++) { + objs.add(", "); //$NON-NLS-1$ + objs.add(params.get(i)); + } + objs.add(")"); //$NON-NLS-1$ + return objs; + } + + protected void addParamWithConversion(List<Object> objs, + IExpression expression) { + if ((expression instanceof ILiteral) + && (((ILiteral) expression).getValue() instanceof String)) { + objs.add(((ILiteral) expression).getValue()); + } else { + objs.add(expression); + } + } + +} \ No newline at end of file Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-08-18 03:01:39 UTC (rev 1255) @@ -56,7 +56,6 @@ import org.teiid.connector.language.IParameter; import org.teiid.connector.language.ISetQuery; import org.teiid.connector.language.IParameter.Direction; -import org.teiid.connector.visitor.util.SQLReservedWords; import com.metamatrix.common.util.PropertiesUtils; import com.metamatrix.core.util.ReflectionHelper; Copied: trunk/connectors/connector-jdbc/src/main/resources/OracleSpatialFunctions.xmi (from rev 1253, trunk/connectors/sandbox/connector-oracle-spatial/src/main/resources/OracleSpatialFunctions.xmi) =================================================================== --- trunk/connectors/connector-jdbc/src/main/resources/OracleSpatialFunctions.xmi (rev 0) +++ trunk/connectors/connector-jdbc/src/main/resources/OracleSpatialFunctions.xmi 2009-08-18 03:01:39 UTC (rev 1255) @@ -0,0 +1,129 @@ +<?xml version="1.0" encoding="ASCII"?> +<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:Diagram="http://www.metamatrix.com/metamodels/Diagram" xmlns:mmcore="http://www.metamatrix.com/metamodels/Core" xmlns:mmfunction="http://www.metamatrix.com/metamodels/MetaMatrixFun... + <mmcore:ModelAnnotation xmi:uuid="mmuuid:1339aac0-1cd3-1f76-a60a-ef2ca142dae4" primaryMetamodelUri="http://www.metamatrix.com/metamodels/MetaMatrix... + <Diagram:DiagramContainer xmi:uuid="mmuuid:141e8c80-1cd3-1f76-a60a-ef2ca142dae4"> + <diagram xmi:uuid="mmuuid:141e8c81-1cd3-1f76-a60a-ef2ca142dae4" type="packageDiagramType" target="mmuuid/1339aac0-1cd3-1f76-a60a-ef2ca142dae4"> + <diagramEntity xmi:uuid="mmuuid:141e8c86-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" modelObject="mmuuid/141e8ca5-1cd3-1f76-a60a-ef2ca142dae4" xPosition="394" yPosition="301"/> + <diagramEntity xmi:uuid="mmuuid:141e8c82-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" modelObject="mmuuid/141e8cb4-1cd3-1f76-a60a-ef2ca142dae4" xPosition="202" yPosition="148"/> + <diagramEntity xmi:uuid="mmuuid:141e8c8a-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" modelObject="mmuuid/141e8c9b-1cd3-1f76-a60a-ef2ca142dae4" xPosition="586" yPosition="394"/> + <diagramEntity xmi:uuid="mmuuid:141e8c83-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_nn" modelObject="mmuuid/141e8cbe-1cd3-1f76-a60a-ef2ca142dae4" xPosition="586" yPosition="10"/> + <diagramEntity xmi:uuid="mmuuid:141e8c8e-1cd3-1f76-a60a-ef2ca142dae4" modelObject="mmuuid/1512b080-1cd3-1f76-a60a-ef2ca142dae4" xPosition="202" yPosition="286"/> + <diagramEntity xmi:uuid="mmuuid:141e8c85-1cd3-1f76-a60a-ef2ca142dae4" modelObject="mmuuid/1512b08b-1cd3-1f76-a60a-ef2ca142dae4" xPosition="394" yPosition="439"/> + <diagramEntity xmi:uuid="mmuuid:141e8c8c-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" modelObject="mmuuid/141e8cb9-1cd3-1f76-a60a-ef2ca142dae4" xPosition="586" yPosition="256"/> + <diagramEntity xmi:uuid="mmuuid:141e8c89-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" modelObject="mmuuid/141e8caa-1cd3-1f76-a60a-ef2ca142dae4" xPosition="10" yPosition="286"/> + <diagramEntity xmi:uuid="mmuuid:141e8c90-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" modelObject="mmuuid/141e8c96-1cd3-1f76-a60a-ef2ca142dae4" xPosition="10" yPosition="10"/> + <diagramEntity xmi:uuid="mmuuid:dfa98740-1cee-1f76-a60a-ef2ca142dae4" modelObject="mmuuid/cb37c380-1cee-1f76-a60a-ef2ca142dae4" xPosition="10" yPosition="148"/> + <diagramEntity xmi:uuid="mmuuid:a0c900c0-1cf1-1f76-a60a-ef2ca142dae4" modelObject="mmuuid/9955a780-1cf1-1f76-a60a-ef2ca142dae4" xPosition="202" yPosition="10"/> + <diagramEntity xmi:uuid="mmuuid:f98ad640-1cf4-1f76-a60a-ef2ca142dae4" modelObject="mmuuid/f2177d00-1cf4-1f76-a60a-ef2ca142dae4" xPosition="394" yPosition="10"/> + <diagramEntity xmi:uuid="mmuuid:bd8b5440-abcd-1f7b-8928-e3ec4f4f400f" modelObject="mmuuid/b7f100c0-abcd-1f7b-8928-e3ec4f4f400f" xPosition="586" yPosition="148"/> + <diagramEntity xmi:uuid="mmuuid:1747de40-ef9e-1f8f-972f-81dc9991b565" modelObject="mmuuid/0678e3c0-ef9e-1f8f-972f-81dc9991b565" xPosition="202" yPosition="424"/> + <diagramEntity xmi:uuid="mmuuid:5ea222c0-efa1-1f8f-972f-81dc9991b565" modelObject="mmuuid/59fbf340-efa1-1f8f-972f-81dc9991b565" xPosition="10" yPosition="424"/> + <diagramEntity xmi:uuid="mmuuid:5ea222c1-efa1-1f8f-972f-81dc9991b565" modelObject="mmuuid/59fbf346-efa1-1f8f-972f-81dc9991b565" xPosition="394" yPosition="148"/> + <diagramEntity xmi:uuid="mmuuid:a97a7ac0-19d4-1fad-a630-f3ba21f9cca0" modelObject="mmuuid/96d27a80-19d4-1fad-a630-f3ba21f9cca0" xPosition="20" yPosition="20"/> + </diagram> + </Diagram:DiagramContainer> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8c96-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8c9a-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:141e8c98-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8c99-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8c97-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8c9b-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8c9e-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:141e8c9c-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8c9f-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8c9d-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8ca5-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8ca9-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8ca8-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:141e8ca6-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8ca7-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8caa-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8cae-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cac-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:141e8cab-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8cad-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8cb4-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_filter" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8cb5-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cb7-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cb8-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8cb6-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8cb9-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_within_distance" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8cbd-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cbc-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cbb-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8cba-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:141e8cbe-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:141e8cc2-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cc1-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:141e8cc0-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:141e8cbf-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:1512b080-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:1512b084-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:1512b083-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:1512b082-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:1512b081-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:1512b08b-1cd3-1f76-a60a-ef2ca142dae4" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:1512b08c-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:1512b08e-1cd3-1f76-a60a-ef2ca142dae4" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:1512b08f-1cd3-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:1512b08d-1cd3-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:cb37c380-1cee-1f76-a60a-ef2ca142dae4" name="sdo_relate" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:cb37c384-1cee-1f76-a60a-ef2ca142dae4" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:cb37c381-1cee-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:cb37c382-1cee-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:cb37c383-1cee-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:9955a780-1cf1-1f76-a60a-ef2ca142dae4" name="sdo_relate" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:9955a782-1cf1-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:9955a783-1cf1-1f76-a60a-ef2ca142dae4" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:9955a781-1cf1-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:9955a784-1cf1-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:96d27a80-19d4-1fad-a630-f3ba21f9cca0" name="sdo_relate" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:96d27a84-19d4-1fad-a630-f3ba21f9cca0" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:96d27a83-19d4-1fad-a630-f3ba21f9cca0" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:96d27a82-19d4-1fad-a630-f3ba21f9cca0" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:96d27a81-19d4-1fad-a630-f3ba21f9cca0" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:f2177d00-1cf4-1f76-a60a-ef2ca142dae4" name="sdo_relate" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:f2177d01-1cf4-1f76-a60a-ef2ca142dae4" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:f2177d02-1cf4-1f76-a60a-ef2ca142dae4" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:f2177d03-1cf4-1f76-a60a-ef2ca142dae4" name="PARAMS" type="string"/> + <returnParameter xmi:uuid="mmuuid:f2177d04-1cf4-1f76-a60a-ef2ca142dae4" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:b7f100c0-abcd-1f7b-8928-e3ec4f4f400f" name="sdo_nn_distance" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:b7f100c1-abcd-1f7b-8928-e3ec4f4f400f" name="number" type="integer"/> + <returnParameter xmi:uuid="mmuuid:b7f100c2-abcd-1f7b-8928-e3ec4f4f400f" type="integer"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:0678e3c0-ef9e-1f8f-972f-81dc9991b565" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:0678e3c1-ef9e-1f8f-972f-81dc9991b565" name="GEOM1" type="string"/> + <inputParameters xmi:uuid="mmuuid:0678e3c3-ef9e-1f8f-972f-81dc9991b565" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:0678e3c6-ef9e-1f8f-972f-81dc9991b565" name="PARAMS" type="string"/> + <inputParameters xmi:uuid="mmuuid:0678e3c4-ef9e-1f8f-972f-81dc9991b565" name="NUMBER" type="integer"/> + <inputParameters xmi:uuid="mmuuid:0678e3c2-ef9e-1f8f-972f-81dc9991b565" name="NUMBER" type="integer"/> + <returnParameter xmi:uuid="mmuuid:0678e3c5-ef9e-1f8f-972f-81dc9991b565" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:59fbf340-efa1-1f8f-972f-81dc9991b565" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:59fbf344-efa1-1f8f-972f-81dc9991b565" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:59fbf345-efa1-1f8f-972f-81dc9991b565" name="GEOM2" type="object"/> + <inputParameters xmi:uuid="mmuuid:59fbf342-efa1-1f8f-972f-81dc9991b565" name="PARAMS" type="string"/> + <inputParameters xmi:uuid="mmuuid:59fbf343-efa1-1f8f-972f-81dc9991b565" name="NUMBER" type="integer"/> + <returnParameter xmi:uuid="mmuuid:59fbf341-efa1-1f8f-972f-81dc9991b565" type="string"/> + </mmfunction:ScalarFunction> + <mmfunction:ScalarFunction xmi:uuid="mmuuid:59fbf346-efa1-1f8f-972f-81dc9991b565" name="sdo_nn" category="Miscellaneous" pushDown="REQUIRED" invocationClass="none" invocationMethod="none"> + <inputParameters xmi:uuid="mmuuid:59fbf34b-efa1-1f8f-972f-81dc9991b565" name="GEOM1" type="object"/> + <inputParameters xmi:uuid="mmuuid:59fbf347-efa1-1f8f-972f-81dc9991b565" name="GEOM2" type="string"/> + <inputParameters xmi:uuid="mmuuid:59fbf349-efa1-1f8f-972f-81dc9991b565" name="PARAMS" type="string"/> + <inputParameters xmi:uuid="mmuuid:59fbf348-efa1-1f8f-972f-81dc9991b565" name="NUMBER" type="integer"/> + <returnParameter xmi:uuid="mmuuid:59fbf34a-efa1-1f8f-972f-81dc9991b565" type="string"/> + </mmfunction:ScalarFunction> +</xmi:XMI> Modified: trunk/connectors/connector-jdbc/src/main/resources/org/teiid/connector/jdbc/i18n.properties =================================================================== --- trunk/connectors/connector-jdbc/src/main/resources/org/teiid/connector/jdbc/i18n.properties 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/connector-jdbc/src/main/resources/org/teiid/connector/jdbc/i18n.properties 2009-08-18 03:01:39 UTC (rev 1255) @@ -73,3 +73,5 @@ BasicResultsTranslator.Couldn__t_parse_property=Could not parse property: {0} JDBCMetadataProcessor.cannot_find_primary=Cannot find primary key table {0} + +OracleSpatialSQLTranslator.SDO_NN_DEPENDENCY_ERROR=The SDO_NN_DISTANCE operator can only be used in conjunction with the SDO_NN operator. Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-18 03:01:39 UTC (rev 1255) @@ -22,8 +22,11 @@ package org.teiid.connector.jdbc.oracle; +import java.io.File; +import java.net.URL; import java.util.Properties; +import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Ignore; import org.junit.Test; @@ -35,6 +38,8 @@ import com.metamatrix.cdk.api.EnvironmentUtility; import com.metamatrix.cdk.unittest.FakeTranslationFactory; +import com.metamatrix.query.function.FunctionLibraryManager; +import com.metamatrix.query.function.UDFSource; public class TestOracleTranslator { @@ -43,20 +48,35 @@ */ private static Translator TRANSLATOR; + @BeforeClass public static void oneTimeSetup() throws Exception { + TRANSLATOR = new OracleSQLTranslator(); + TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + // Define a UDFSource to hold the reference to our function definitions + File udfFile = new File("src/main/resources/OracleSpatialFunctions.xmi"); //$NON-NLS-1$; + URL[] urls = new URL[0]; + UDFSource udfSource = new UDFSource(udfFile.toURI().toURL(), urls); + FunctionLibraryManager.deregisterSource(udfSource); + FunctionLibraryManager.registerSource(udfSource); + } + /** - * Performs setup tasks that should be executed prior to an instance of this - * class being created. This method should only be executed once and does + * Performs cleanup tasks that should be executed after all test methods + * have been executed. This method should only be executed once and does * not protect from multiple executions. It is intended to be executed by * the JUnit4 test framework. * <p> - * This method sets {@link TestOracleTranslator#TRANSLATOR} to an instance - * of {@link OracleSQLTranslator} and then calls its {@link OracleSQLTranslator#initialize(ConnectorEnvironment)} - * method. + * This method unloads the function definitions supported by the Oracle Spatial + * Connector from the global instance of <code>FunctionLibraryManager</code> + * so that they are no longer resolvable during query parsing. + * * @throws Exception */ - @BeforeClass public static void oneTimeSetup() throws Exception { - TRANSLATOR = new OracleSQLTranslator(); - TRANSLATOR.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + @AfterClass public static void oneTimeFinished() throws Exception { + // Define a UDFSource to hold the reference to our function definitions + File udfFile = new File("src/main/resources/OracleSpatialFunctions.xmi"); //$NON-NLS-1$; + URL[] urls = new URL[0]; + UDFSource udfSource = new UDFSource(udfFile.toURI().toURL(), urls); + FunctionLibraryManager.deregisterSource(udfSource); } private void helpTestVisitor(String input, String expectedOutput) throws ConnectorException { @@ -289,5 +309,111 @@ input, output, TRANSLATOR); } + + /** + * Test a query which uses + * <code>sdo_relate(Object element, Object element, String literal) in its + * criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_relate() throws Exception { + String input = "SELECT a.INTKEY FROM BQT1.SMALLA A, BQT1.SMALLB B WHERE sdo_relate(A.OBJECTVALUE, b.OBJECTVALUE, 'mask=ANYINTERACT') = true"; //$NON-NLS-1$ + String output = "SELECT /*+ ORDERED */ A.IntKey FROM SmallA A, SmallB B WHERE sdo_relate(A.ObjectValue, B.ObjectValue, 'mask=ANYINTERACT') = 'true'"; //$NON-NLS-1$ + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test a query which uses + * <code>sdo_within_distance(Object element, String literal, String literal) + * in its criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_within_distance() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA WHERE sdo_within_distance(OBJECTVALUE, 'SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL)', 'DISTANCE=25.0 UNIT=NAUT_MILE') = true"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA WHERE sdo_within_distance(SmallA.ObjectValue, SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL), 'DISTANCE=25.0 UNIT=NAUT_MILE') = 'true'"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test a query which uses + * <code>sdo_within_distance(String literal, Object element, String literal) + * in its criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_within_distance2() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA WHERE sdo_within_distance('SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL)', OBJECTVALUE, 'DISTANCE=25.0 UNIT=NAUT_MILE') = true"; //$NON-NLS-1$ + String output = "SELECT SmallA.IntKey FROM SmallA WHERE sdo_within_distance(SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL), SmallA.ObjectValue, 'DISTANCE=25.0 UNIT=NAUT_MILE') = 'true'"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test a query which uses + * <code>sdo_within_distance(String element, String literal, String literal) + * in its criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_within_distance3() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA WHERE sdo_within_distance(STRINGKEY, 'SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL)', 'DISTANCE=25.0 UNIT=NAUT_MILE') = true"; //$NON-NLS-1$ + // using ? for bind value as rewriter marks the criteria as bindEligible + // due to literal of type Object appearing in left side of criteria. + // The literal Object is a result of the sdo_within_distance function + // signature being sdo_within_distance(string, object, string) : string + // as the signature was the best match for this query. + String output = "SELECT SmallA.IntKey FROM SmallA WHERE sdo_within_distance(SmallA.StringKey, SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL), 'DISTANCE=25.0 UNIT=NAUT_MILE') = ?"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test a query which uses + * <code>sdo_within_distance(String literal, String literal, String literal) + * in its criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_within_distance4() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA WHERE sdo_within_distance('SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL)', 'SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL)', 'DISTANCE=25.0 UNIT=NAUT_MILE') = true"; //$NON-NLS-1$ + // using ? for bind value as rewriter marks the criteria as bindEligible + // due to literal of type Object appearing in left side of criteria. + // The literal Object is a result of the sdo_within_distance function + // signature being sdo_within_distance(string, object, string) : string + // as the signature was the best match for this query. + String output = "SELECT SmallA.IntKey FROM SmallA WHERE sdo_within_distance(SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL), SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(90.0, -45.0, NULL), NULL, NULL), 'DISTANCE=25.0 UNIT=NAUT_MILE') = ?"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + + /** + * Test a query which uses + * <code>sdo_within_distance(Object element, Object element, String literal) + * in its criteria into a source specific command. + * + * @throws Exception + */ + @Test public void testRewrite_sdo_within_distance5() throws Exception { + String input = "SELECT a.INTKEY FROM BQT1.SMALLA A, BQT1.SMALLB B WHERE sdo_within_distance(a.OBJECTVALUE, b.OBJECTVALUE, 'DISTANCE=25.0 UNIT=NAUT_MILE') = true"; //$NON-NLS-1$ + String output = "SELECT A.IntKey FROM SmallA A, SmallB B WHERE sdo_within_distance(A.ObjectValue, B.ObjectValue, 'DISTANCE=25.0 UNIT=NAUT_MILE') = 'true'"; //$NON-NLS-1$ + + MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, + input, output, + TRANSLATOR); + } + } Modified: trunk/connectors/sandbox/pom.xml =================================================================== --- trunk/connectors/sandbox/pom.xml 2009-08-17 21:38:58 UTC (rev 1254) +++ trunk/connectors/sandbox/pom.xml 2009-08-18 03:01:39 UTC (rev 1255) @@ -15,6 +15,5 @@ <module>connector-yahoo</module> <module>connector-exec</module> <module>connector-object</module> - <module>connector-oracle-spatial</module> </modules> </project> \ No newline at end of file