Author: shawkins Date: 2009-08-18 15:37:24 -0400 (Tue, 18 Aug 2009) New Revision: 1262 Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.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 Log: TEIID-787 after consulting with John Doyle, removing special spatial logic from the oracle connector 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-18 16:09:14 UTC (rev 1261) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-08-18 19:37:24 UTC (rev 1262) @@ -39,13 +39,11 @@ 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; @@ -54,16 +52,12 @@ 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 { /* @@ -115,37 +109,6 @@ @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; } @@ -294,16 +257,13 @@ // 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' + // Then we check if any of those functions are sdo_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) + if (func.getName().equalsIgnoreCase(RELATE)) { return comment + "/*+ ORDERED */ "; //$NON-NLS-1$ + } } } return comment; 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-18 16:09:14 UTC (rev 1261) +++ trunk/connectors/connector-jdbc/src/main/resources/org/teiid/connector/jdbc/i18n.properties 2009-08-18 19:37:24 UTC (rev 1262) @@ -73,5 +73,3 @@ 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-18 16:09:14 UTC (rev 1261) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-08-18 19:37:24 UTC (rev 1262) @@ -116,7 +116,7 @@ output, TRANSLATOR); } - @Test public void testRewriteConversion1() throws Exception { + @Test public void testConversion1() throws Exception { String input = "SELECT char(convert(STRINGNUM, integer) + 100) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT chr((to_number(SmallA.StringNum) + 100)) FROM SmallA"; //$NON-NLS-1$ @@ -125,7 +125,7 @@ TRANSLATOR); } - @Test public void testRewriteConversion2() throws Exception { + @Test public void testConversion2() throws Exception { String input = "SELECT convert(STRINGNUM, long) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_number(SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ @@ -134,7 +134,7 @@ TRANSLATOR); } - @Test public void testRewriteConversion3() throws Exception { + @Test public void testConversion3() throws Exception { String input = "SELECT convert(convert(STRINGNUM, long), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(to_number(SmallA.StringNum)) FROM SmallA"; //$NON-NLS-1$ @@ -143,7 +143,7 @@ TRANSLATOR); } - @Test public void testRewriteConversion4() throws Exception { + @Test public void testConversion4() throws Exception { String input = "SELECT convert(convert(TIMESTAMPVALUE, date), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(trunc(SmallA.TimestampValue), 'YYYY-MM-DD') FROM SmallA"; //$NON-NLS-1$ @@ -151,7 +151,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion5() throws Exception { + @Test public void testConversion5() throws Exception { String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(to_date(('1970-01-01 ' || to_char(SmallA.TimestampValue, 'HH24:MI:SS')), 'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') FROM SmallA"; //$NON-NLS-1$ @@ -159,7 +159,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion6() throws Exception { + @Test public void testConversion6() throws Exception { String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; //$NON-NLS-1$ @@ -167,7 +167,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion8() throws Exception { + @Test public void testConversion8() throws Exception { String input = "SELECT nvl(INTNUM, 'otherString') FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT nvl(to_char(SmallA.IntNum), 'otherString') FROM SmallA"; //$NON-NLS-1$ @@ -175,7 +175,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteConversion7() throws Exception { + @Test public void testConversion7() throws Exception { String input = "SELECT convert(convert(STRINGNUM, integer), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(to_number(SmallA.StringNum)) FROM SmallA"; //$NON-NLS-1$ @@ -184,7 +184,7 @@ TRANSLATOR); } @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") - @Test public void testRewriteLocate() throws Exception { + @Test public void testLocate() throws Exception { // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$ @@ -193,7 +193,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteLocate2() throws Exception { + @Test public void testLocate2() throws Exception { String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ @@ -202,7 +202,7 @@ TRANSLATOR); } @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is") - @Test public void testRewriteLocate3() throws Exception { + @Test public void testLocate3() throws Exception { // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is used as is String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ String output = "SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ @@ -211,7 +211,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteLocate4() throws Exception { + @Test public void testLocate4() throws Exception { String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ @@ -220,7 +220,7 @@ TRANSLATOR); } @Ignore("TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given") - @Test public void testRewriteLocate5() throws Exception { + @Test public void testLocate5() throws Exception { // TODO TEIID-754: Fix Oracle translator so fromPosition of LOCATE function is 1 if a value of < 1 is given String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ @@ -229,7 +229,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteSubstring1() throws Exception { + @Test public void testSubstring1() throws Exception { String input = "SELECT substring(StringNum, 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT substr(SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ @@ -237,7 +237,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteSubstring2() throws Exception { + @Test public void testSubstring2() throws Exception { String input = "SELECT substring(StringNum, 1, 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT substr(SmallA.StringNum, 1, 1) FROM SmallA"; //$NON-NLS-1$ @@ -245,7 +245,7 @@ input, output, TRANSLATOR); } - @Test public void testRewriteUnionWithOrderBy() throws Exception { + @Test public void testUnionWithOrderBy() throws Exception { String input = "SELECT IntKey FROM BQT1.SMALLA UNION SELECT IntKey FROM BQT1.SMALLB ORDER BY IntKey"; //$NON-NLS-1$ String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallB.IntKey FROM SmallB ORDER BY IntKey NULLS FIRST"; //$NON-NLS-1$ @@ -317,7 +317,7 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_relate() throws Exception { + @Test public void test_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$ @@ -333,7 +333,7 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_within_distance() throws Exception { + @Test public void test_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$ @@ -349,7 +349,7 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_within_distance2() throws Exception { + @Test public void test_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$ @@ -365,9 +365,9 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_within_distance3() throws Exception { + @Test public void test_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 + // using ? for bind value as r 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 @@ -386,9 +386,9 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_within_distance4() throws Exception { + @Test public void test_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 + // using ? for bind value as r 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 @@ -407,7 +407,7 @@ * * @throws Exception */ - @Test public void testRewrite_sdo_within_distance5() throws Exception { + @Test public void test_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$