Author: shawkins Date: 2010-06-22 22:52:46 -0400 (Tue, 22 Jun 2010) New Revision: 2282 Removed: trunk/connectors/translator-jdbc/src/test/java/org/teiid/connector/ Modified: trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestDayWeekQuarterFunctionModifier.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestMonthOrDayNameFunctionModifier.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleConvertModifier.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java Log: TEIID-968 updating the oracle timestamp literal translation Modified: trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java =================================================================== --- trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -77,9 +77,6 @@ private ConnectionImpl driverConnection; private Properties execProps; - // the string which is the XSLT style sheet - private String styleSheet; - // fetch size value. This is the default fetch size used by the server private int fetchSize = BaseDataSource.DEFAULT_FETCH_SIZE; @@ -821,7 +818,6 @@ reqMsg.setExecutionPayload(this.payload); reqMsg.setCursorType(this.resultSetType); reqMsg.setFetchSize(this.fetchSize); - reqMsg.setStyleSheet(this.styleSheet); reqMsg.setRowLimit(this.maxRows); reqMsg.setTransactionIsolation(this.driverConnection.getTransactionIsolation()); @@ -870,23 +866,6 @@ } /** - * <p> This method sets a style sheet to this object. The style sheet is - * to perform transformations. - * @param reader The reader object from which the styleSheet is to be read - * @throws IOException if unable to read the style sheet from the Reader object. - */ - public void attachStylesheet(Reader reader) throws IOException { - this.styleSheet = ObjectConverterUtil.convertToString(reader); - } - - /** - * <p> This method removes any existing style sheet on this object. - */ - public void clearStylesheet() { - this.styleSheet = null; - } - - /** * Get Query plan description. * If the Statement has a resultSet, we get the plan description from the result set * If that plan description is null, though, we return the very first plan description Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -29,6 +29,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.sql.Timestamp; import java.sql.Types; import java.util.ArrayList; import java.util.Arrays; @@ -53,10 +54,10 @@ import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.metadata.Column; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.ExecutionContext; -import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.TypeFacility; import org.teiid.translator.jdbc.AliasModifier; import org.teiid.translator.jdbc.ConvertModifier; @@ -70,6 +71,11 @@ @Translator(name="oracle") public class OracleExecutionFactory extends JDBCExecutionFactory { + private static final String TIME_FORMAT = "HH24:MI:SS"; //$NON-NLS-1$ + private static final String DATE_FORMAT = "YYYY-MM-DD"; //$NON-NLS-1$ + private static final String DATETIME_FORMAT = DATE_FORMAT + " " + TIME_FORMAT; //$NON-NLS-1$ + private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".FF"; //$NON-NLS-1$ + /* * Spatial Functions */ @@ -142,17 +148,17 @@ return Arrays.asList("trunc(cast(",function.getParameters().get(0)," AS date))"); //$NON-NLS-1$ //$NON-NLS-2$ } }); - convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "YYYY-MM-DD")); //$NON-NLS-1$ //$NON-NLS-2$ - convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "HH24:MI:SS")); //$NON-NLS-1$ //$NON-NLS-2$ + convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", DATE_FORMAT)); //$NON-NLS-1$ + convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", TIME_FORMAT)); //$NON-NLS-1$ convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { @Override public List<?> translate(Function function) { //if column and type is date, just use date format Expression ex = function.getParameters().get(0); - String format = "YYYY-MM-DD HH24:MI:SS.FF"; //$NON-NLS-1$ + String format = TIMESTAMP_FORMAT; if (ex instanceof ColumnReference) { if ("date".equals(((ColumnReference)ex).getMetadataObject().getNativeType())) { //$NON-NLS-1$ - format = "YYYY-MM-DD HH24:MI:SS"; //$NON-NLS-1$ + format = DATETIME_FORMAT; } } else if (!(ex instanceof Function) && !(ex instanceof Literal)) { //this isn't needed in every case, but it's simpler than inspecting the expression more @@ -161,9 +167,9 @@ return Arrays.asList("to_char(", ex, ", '", format, "')"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } }); - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DATE, new ConvertModifier.FormatModifier("to_date", "YYYY-MM-DD")); //$NON-NLS-1$ //$NON-NLS-2$ - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_date", "HH24:MI:SS")); //$NON-NLS-1$ //$NON-NLS-2$ - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", "YYYY-MM-DD HH24:MI:SS.FF")); //$NON-NLS-1$ //$NON-NLS-2$ + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DATE, new ConvertModifier.FormatModifier("to_date", DATE_FORMAT)); //$NON-NLS-1$ + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_date", TIME_FORMAT)); //$NON-NLS-1$ + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); //$NON-NLS-1$ convertModifier.addTypeConversion(new ConvertModifier.FormatModifier("to_char"), FunctionModifier.STRING); //$NON-NLS-1$ //NOTE: numeric handling in Oracle is split only between integral vs. floating/decimal types convertModifier.addTypeConversion(new ConvertModifier.FormatModifier("to_number"), //$NON-NLS-1$ @@ -511,6 +517,16 @@ } @Override + public String translateLiteralTimestamp(Timestamp timestampValue) { + if (timestampValue.getNanos() == 0) { + String val = formatDateValue(timestampValue); + val = val.substring(0, val.length() - 2); + return "to_date('" + val + "', '" + DATETIME_FORMAT + "')"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + return super.translateLiteralTimestamp(timestampValue); + } + + @Override public boolean supportsInlineViews() { return true; } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestDayWeekQuarterFunctionModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestDayWeekQuarterFunctionModifier.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestDayWeekQuarterFunctionModifier.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -63,9 +63,9 @@ } public void test1() throws Exception { - Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 0), Timestamp.class); + Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 10000000), Timestamp.class); helpTestMod(arg1, SourceSystemFunctions.DAYOFYEAR, - "to_number(TO_CHAR({ts '2004-01-21 10:05:00.0'}, 'DDD'))"); //$NON-NLS-1$ + "to_number(TO_CHAR({ts '2004-01-21 10:05:00.01'}, 'DDD'))"); //$NON-NLS-1$ } public void test2() throws Exception { @@ -75,9 +75,9 @@ } public void test9() throws Exception { - Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 0), Timestamp.class); + Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 10000000), Timestamp.class); helpTestMod(arg1, SourceSystemFunctions.QUARTER, - "to_number(TO_CHAR({ts '2004-01-21 10:05:00.0'}, 'Q'))"); //$NON-NLS-1$ + "to_number(TO_CHAR({ts '2004-01-21 10:05:00.01'}, 'Q'))"); //$NON-NLS-1$ } public void test10() throws Exception { Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestMonthOrDayNameFunctionModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestMonthOrDayNameFunctionModifier.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestMonthOrDayNameFunctionModifier.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -62,9 +62,9 @@ } public void test1() throws Exception { - Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 0), Timestamp.class); + Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 10000000), Timestamp.class); helpTestMod(arg1, "Month", //$NON-NLS-1$ - "rtrim(TO_CHAR({ts '2004-01-21 10:05:00.0'}, 'Month'))"); //$NON-NLS-1$ + "rtrim(TO_CHAR({ts '2004-01-21 10:05:00.01'}, 'Month'))"); //$NON-NLS-1$ } public void test2() throws Exception { @@ -74,9 +74,9 @@ } public void test3() throws Exception { - Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 0), Timestamp.class); + Literal arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 10, 5, 0, 10000000), Timestamp.class); helpTestMod(arg1, "Day", //$NON-NLS-1$ - "rtrim(TO_CHAR({ts '2004-01-21 10:05:00.0'}, 'Day'))"); //$NON-NLS-1$ + "rtrim(TO_CHAR({ts '2004-01-21 10:05:00.01'}, 'Day'))"); //$NON-NLS-1$ } public void test4() throws Exception { Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleConvertModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleConvertModifier.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleConvertModifier.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -497,18 +497,18 @@ // Source = TIMESTAMP @Test public void testTimestampToString() throws Exception { - Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); - helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "string", "to_char({ts '2003-11-01 12:05:02.0'}, 'YYYY-MM-DD HH24:MI:SS.FF')"); //$NON-NLS-1$ //$NON-NLS-2$ + Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 10000000); + helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "string", "to_char({ts '2003-11-01 12:05:02.01'}, 'YYYY-MM-DD HH24:MI:SS.FF')"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testTimestampToDate() throws Exception { - Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); - helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "date", "trunc(cast({ts '2003-11-01 12:05:02.0'} AS date))"); //$NON-NLS-1$ //$NON-NLS-2$ + Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 10000000); + helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "date", "trunc(cast({ts '2003-11-01 12:05:02.01'} AS date))"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testTimestampToTime() throws Exception { - Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); - helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "case when {ts '2003-11-01 12:05:02.0'} is null then null else to_date('1970-01-01 ' || to_char({ts '2003-11-01 12:05:02.0'}, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ + Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 10000000); + helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "case when {ts '2003-11-01 12:05:02.01'} is null then null else to_date('1970-01-01 ' || to_char({ts '2003-11-01 12:05:02.01'}, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ } } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-06-22 20:53:33 UTC (rev 2281) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-06-23 02:52:46 UTC (rev 2282) @@ -564,9 +564,16 @@ @Test public void testTimestampLiteral() throws Exception { helpTestVisitor(getTestVDB(), + "select {ts '2002-12-31 13:59:59.1'} FROM parts", //$NON-NLS-1$ + null, + "SELECT {ts '2002-12-31 13:59:59.1'} FROM PARTS"); //$NON-NLS-1$ + } + + @Test public void testTimestampLiteral1() throws Exception { + helpTestVisitor(getTestVDB(), "select {ts '2002-12-31 13:59:59'} FROM parts", //$NON-NLS-1$ null, - "SELECT {ts '2002-12-31 13:59:59.0'} FROM PARTS"); //$NON-NLS-1$ + "SELECT to_date('2002-12-31 13:59:59', 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"); //$NON-NLS-1$ } @Test public void testUnionOrderByWithThreeBranches() throws Exception {