9:37 p.m.
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$