Author: shawkins Date: 2009-07-25 16:58:40 -0400 (Sat, 25 Jul 2009) New Revision: 1188 Added: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ExtractFunctionModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestExtractFunctionModifier.java Removed: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/ExtractFunctionModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/DatePartFunctionModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/ModifiedDatePartFunctionModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestExtractFunctionModifier.java Modified: trunk/connector-api/src/main/java/org/teiid/connector/visitor/util/SQLStringVisitor.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/postgresql/PostgreSQLCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseConvertModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseConvertModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java Log: TEIID-739 TEIID-360 updates to jdbc connector translators and capabilities. also changing the sybase/sql server time literal back to using the year 1900 Modified: trunk/connector-api/src/main/java/org/teiid/connector/visitor/util/SQLStringVisitor.java =================================================================== --- trunk/connector-api/src/main/java/org/teiid/connector/visitor/util/SQLStringVisitor.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connector-api/src/main/java/org/teiid/connector/visitor/util/SQLStringVisitor.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -85,7 +85,7 @@ public class SQLStringVisitor extends AbstractLanguageVisitor implements SQLReservedWords { private Set<String> infixFunctions = new HashSet<String>(Arrays.asList("%", "+", "-", "*", "+", "/", "||", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ - "&", "|", "^")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + "&", "|", "^", "#")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ private static final String ESCAPED_QUOTE = "''"; //$NON-NLS-1$ Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/ExtractFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/ExtractFunctionModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/ExtractFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -1,59 +0,0 @@ -/* - * 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; - - -/** - * Convert the YEAR/MONTH/DAY etc. function into an equivalent Oracle function. - * Format: EXTRACT(YEAR from Element) or EXTRACT(YEAR from DATE '2004-03-03') - */ -public class ExtractFunctionModifier extends BasicFunctionModifier { - public static final String SPACE = " "; //$NON-NLS-1$ - - private String target; - - public ExtractFunctionModifier(String target) { - this.target = target; - } - - public List<?> translate(IFunction function) { - List<IExpression> args = function.getParameters(); - - List<Object> objs = new ArrayList<Object>(); - objs.add("EXTRACT("); //$NON-NLS-1$ - objs.add(target); - objs.add(SPACE); - objs.add("FROM"); //$NON-NLS-1$ - objs.add(SPACE); - objs.add(args.get(0)); - objs.add(")"); //$NON-NLS-1$ - return objs; - } -} 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-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -40,6 +40,7 @@ import org.teiid.connector.api.SourceSystemFunctions; 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.IElement; @@ -72,12 +73,12 @@ registerFunctionModifier(SourceSystemFunctions.CEILING, new AliasModifier("ceil")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LOG10, new Log10FunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.CONVERT, new OracleConvertModifier(getLanguageFactory(), getEnvironment().getLogger())); - registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier("HOUR"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier("YEAR"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier("MINUTE"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier("SECOND"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier("MONTH"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier("DAY"));//$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier()); registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Month"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Day"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.WEEK, new DayWeekQuarterFunctionModifier(getLanguageFactory(), "WW"));//$NON-NLS-1$ Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/DatePartFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/DatePartFunctionModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/DatePartFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -1,52 +0,0 @@ -/* - * 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.postgresql; - -import java.util.Arrays; - -import org.teiid.connector.jdbc.translator.BasicFunctionModifier; -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.ILanguageFactory; - - - -/** - * @since 4.3 - */ -class DatePartFunctionModifier extends BasicFunctionModifier { - - protected ILanguageFactory factory; - private String part; - - DatePartFunctionModifier(ILanguageFactory langFactory, String partName) { - this.factory = langFactory; - this.part = partName; - } - - public IExpression modify(IFunction function) { - return factory.createFunction("date_part", //$NON-NLS-1$ - Arrays.asList(factory.createLiteral(part, String.class), function.getParameters().get(0)), - Integer.class); - } -} Deleted: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/ModifiedDatePartFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/ModifiedDatePartFunctionModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/ModifiedDatePartFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -1,55 +0,0 @@ -/* - * 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.postgresql; - -import java.util.Arrays; - -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.ILanguageFactory; - - - -/** - * @since 4.3 - */ -class ModifiedDatePartFunctionModifier extends DatePartFunctionModifier { - - private String modifier; - private Object arg; - - ModifiedDatePartFunctionModifier(ILanguageFactory factory, String partName, String modifierFunctionName, Object modifierArgument) { - super(factory, partName); - this.modifier = modifierFunctionName; - this.arg = modifierArgument; - } - - public IExpression modify(IFunction function) { - IExpression expr = super.modify(function); - return factory.createFunction(modifier, - Arrays.asList(expr, factory.createLiteral(arg, arg.getClass())), - arg.getClass()); - } - - -} Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLCapabilities.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -22,8 +22,8 @@ package org.teiid.connector.jdbc.postgresql; -import java.util.ArrayList; -import java.util.List; +import java.util.ArrayList; +import java.util.List; import org.teiid.connector.jdbc.JDBCCapabilities; @@ -44,11 +44,10 @@ supportedFunctions.add("ASIN"); //$NON-NLS-1$ supportedFunctions.add("ATAN"); //$NON-NLS-1$ supportedFunctions.add("ATAN2"); //$NON-NLS-1$ - // These are executed within the server and never pushed down -// supportedFunctions.add("BITAND"); //$NON-NLS-1$ -// supportedFunctions.add("BITNOT"); //$NON-NLS-1$ -// supportedFunctions.add("BITOR"); //$NON-NLS-1$ -// supportedFunctions.add("BITXOR"); //$NON-NLS-1$ + supportedFunctions.add("BITAND"); //$NON-NLS-1$ + supportedFunctions.add("BITNOT"); //$NON-NLS-1$ + supportedFunctions.add("BITOR"); //$NON-NLS-1$ + supportedFunctions.add("BITXOR"); //$NON-NLS-1$ supportedFunctions.add("CEILING"); //$NON-NLS-1$ supportedFunctions.add("COS"); //$NON-NLS-1$ supportedFunctions.add("COT"); //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/PostgreSQLTranslator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -37,6 +37,7 @@ import org.teiid.connector.jdbc.oracle.LeftOrRightFunctionModifier; import org.teiid.connector.jdbc.oracle.MonthOrDayNameFunctionModifier; 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.IAggregate; import org.teiid.connector.language.ICommand; @@ -46,16 +47,22 @@ -/** +/** + * Translator class for PostgreSQL. Updated to expect a 8.0+ jdbc client * @since 4.3 */ public class PostgreSQLTranslator extends Translator { public void initialize(ConnectorEnvironment env) throws ConnectorException { - + //TODO: all of the functions (except for convert) can be handled through just the escape syntax super.initialize(env); registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("ln")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.LOG10, new AliasModifier("log")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOG10, new AliasModifier("log")); //$NON-NLS-1$ + + registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("&")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("~")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("|")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("#")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("chr")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("||")); //$NON-NLS-1$ @@ -66,20 +73,24 @@ registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Day"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ModifiedDatePartFunctionModifier(getLanguageFactory(), "dow", "+", new Integer(1)));//$NON-NLS-1$ //$NON-NLS-2$ - registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new DatePartFunctionModifier(getLanguageFactory(), "day"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new DatePartFunctionModifier(getLanguageFactory(), "doy"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.HOUR, new DatePartFunctionModifier(getLanguageFactory(), "hour"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.MINUTE, new DatePartFunctionModifier(getLanguageFactory(), "minute"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.MONTH, new DatePartFunctionModifier(getLanguageFactory(), "month"));//$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier()); registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Month"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.QUARTER, new DatePartFunctionModifier(getLanguageFactory(), "quarter"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.SECOND, new DatePartFunctionModifier(getLanguageFactory(), "second"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.WEEK, new DatePartFunctionModifier(getLanguageFactory(), "week"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.YEAR, new DatePartFunctionModifier(getLanguageFactory(), "year"));//$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.QUARTER, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.WEEK, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier()); + + //specific to 8.2 client or later + //registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new EscapeSyntaxModifier()); + //registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.CONVERT, new PostgreSQLConvertModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.CONVERT, new PostgreSQLConvertModifier(getLanguageFactory())); } @Override Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerCapabilities.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerCapabilities.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -88,20 +88,20 @@ supportedFunctions.add("UPPER"); //$NON-NLS-1$ //supportedFunctons.add("CURDATE"); //$NON-NLS-1$ //supportedFunctons.add("CURTIME"); //$NON-NLS-1$ - //supportedFunctons.add("DAYNAME"); //$NON-NLS-1$ + supportedFunctions.add("DAYNAME"); //$NON-NLS-1$ supportedFunctions.add("DAYOFMONTH"); //$NON-NLS-1$ - //supportedFunctons.add("DAYOFWEEK"); //$NON-NLS-1$ - //supportedFunctons.add("DAYOFYEAR"); //$NON-NLS-1$ - //supportedFunctons.add("HOUR"); //$NON-NLS-1$ - //supportedFunctons.add("MINUTE"); //$NON-NLS-1$ + supportedFunctions.add("DAYOFWEEK"); //$NON-NLS-1$ + supportedFunctions.add("DAYOFYEAR"); //$NON-NLS-1$ + supportedFunctions.add("HOUR"); //$NON-NLS-1$ + supportedFunctions.add("MINUTE"); //$NON-NLS-1$ supportedFunctions.add("MONTH"); //$NON-NLS-1$ - //supportedFunctons.add("MONTHNAME"); //$NON-NLS-1$ - //supportedFunctons.add("NOW"); //$NON-NLS-1$ - //supportedFunctons.add("QUARTER"); //$NON-NLS-1$ - //supportedFunctons.add("SECOND"); //$NON-NLS-1$ - //supportedFunctons.add("TIMESTAMPADD"); //$NON-NLS-1$ - //supportedFunctons.add("TIMESTAMPDIFF"); //$NON-NLS-1$ - //supportedFunctons.add("WEEK"); //$NON-NLS-1$ + supportedFunctions.add("MONTHNAME"); //$NON-NLS-1$ + //supportedFunctions.add("NOW"); //$NON-NLS-1$ + supportedFunctions.add("QUARTER"); //$NON-NLS-1$ + supportedFunctions.add("SECOND"); //$NON-NLS-1$ + supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$ + supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$ + supportedFunctions.add("WEEK"); //$NON-NLS-1$ supportedFunctions.add("YEAR"); //$NON-NLS-1$ supportedFunctions.add("CAST"); //$NON-NLS-1$ supportedFunctions.add("CONVERT"); //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerSQLTranslator.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sqlserver/SqlServerSQLTranslator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -30,9 +30,10 @@ import org.teiid.connector.api.SourceSystemFunctions; import org.teiid.connector.jdbc.sybase.SybaseSQLTranslator; import org.teiid.connector.jdbc.translator.AliasModifier; +import org.teiid.connector.jdbc.translator.EscapeSyntaxModifier; - -/** +/** + * Updated to assume the use of the DataDirect, 2005 driver, or later. */ public class SqlServerSQLTranslator extends SybaseSQLTranslator { @@ -40,7 +41,18 @@ super.initialize(env); //TEIID-31 remove mod modifier for SQL Server 2008 registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new AliasModifier("day")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.REPEAT, new AliasModifier("replicate")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.REPEAT, new AliasModifier("replicate")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.DAYNAME, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.HOUR, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.MINUTE, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.QUARTER, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.SECOND, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.WEEK, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier()); } @Override Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseConvertModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseConvertModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseConvertModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -28,7 +28,6 @@ import org.teiid.connector.jdbc.translator.BasicFunctionModifier; import org.teiid.connector.jdbc.translator.DropFunctionModifier; -import org.teiid.connector.jdbc.translator.FunctionModifier; import org.teiid.connector.language.IExpression; import org.teiid.connector.language.IFunction; import org.teiid.connector.language.ILanguageFactory; Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/sybase/SybaseSQLTranslator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -75,6 +75,11 @@ } @Override + public String getDefaultTimeYMD() { + return "1900-01-01"; //$NON-NLS-1$ + } + + @Override public List<?> translateCommand(ICommand command, ExecutionContext context) { if (!(command instanceof IQueryCommand)) { return null; Copied: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ExtractFunctionModifier.java (from rev 1179, trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/ExtractFunctionModifier.java) =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ExtractFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/ExtractFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -0,0 +1,87 @@ +/* + * 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.translator; + +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.visitor.util.SQLReservedWords; + + +/** + * Convert the YEAR/MONTH/DAY etc. function into an equivalent Extract function. + * Format: EXTRACT(YEAR from Element) or EXTRACT(YEAR from DATE '2004-03-03') + */ +public class ExtractFunctionModifier extends BasicFunctionModifier { + public static final String YEAR = "YEAR"; //$NON-NLS-1$ + public static final String QUARTER = "QUARTER"; //$NON-NLS-1$ + public static final String MONTH = "MONTH"; //$NON-NLS-1$ + public static final String DAYOFYEAR = "DOY"; //$NON-NLS-1$ + public static final String DAY = "DAY"; //$NON-NLS-1$ + public static final String WEEK = "WEEK"; //$NON-NLS-1$ + public static final String DAYOFWEEK = "DOW"; //$NON-NLS-1$ + public static final String HOUR = "HOUR"; //$NON-NLS-1$ + public static final String MINUTE = "MINUTE"; //$NON-NLS-1$ + public static final String SECOND = "SECOND"; //$NON-NLS-1$ + public static final String MILLISECONDS = "MILLISECONDS"; //$NON-NLS-1$ + + private static Map<String, String> FUNCTION_PART_MAP = new HashMap<String, String>(); + + static { + FUNCTION_PART_MAP.put(SourceSystemFunctions.WEEK, WEEK); + FUNCTION_PART_MAP.put(SourceSystemFunctions.DAYOFWEEK, DAYOFWEEK); + FUNCTION_PART_MAP.put(SourceSystemFunctions.DAYOFYEAR, DAYOFYEAR); + FUNCTION_PART_MAP.put(SourceSystemFunctions.YEAR, YEAR); + FUNCTION_PART_MAP.put(SourceSystemFunctions.QUARTER, QUARTER); + FUNCTION_PART_MAP.put(SourceSystemFunctions.MONTH, MONTH); + FUNCTION_PART_MAP.put(SourceSystemFunctions.DAYOFMONTH, DAY); + FUNCTION_PART_MAP.put(SourceSystemFunctions.HOUR, HOUR); + FUNCTION_PART_MAP.put(SourceSystemFunctions.MINUTE, MINUTE); + FUNCTION_PART_MAP.put(SourceSystemFunctions.SECOND, SECOND); + } + + public ExtractFunctionModifier() { + } + + public List<?> translate(IFunction function) { + List<IExpression> args = function.getParameters(); + List<Object> objs = new ArrayList<Object>(); + objs.add("EXTRACT("); //$NON-NLS-1$ + objs.add(FUNCTION_PART_MAP.get(function.getName().toLowerCase())); + objs.add(SQLReservedWords.SPACE); + objs.add(SQLReservedWords.FROM); + objs.add(SQLReservedWords.SPACE); + objs.add(args.get(0)); + objs.add(SQLReservedWords.RPAREN); + if (function.getName().toLowerCase().equals(SourceSystemFunctions.DAYOFWEEK)) { + objs.add(0, SQLReservedWords.LPAREN); + objs.add(" + 1)"); //$NON-NLS-1$ + } + return objs; + } +} 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-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/Translator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -263,7 +263,7 @@ */ public String translateLiteralTime(Time timeValue) { if (!hasTimeType()) { - return "{ts'1970-01-01 " + formatDateValue(timeValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$ + return "{ts'"+ getDefaultTimeYMD()+ " " + formatDateValue(timeValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } return "{t'" + formatDateValue(timeValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$ } @@ -350,6 +350,10 @@ return true; } + public String getDefaultTimeYMD() { + return "1970-01-01"; //$NON-NLS-1$ + } + /** * Returns the name for a given {@link ISetQuery.Operation} * @param operation Deleted: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestExtractFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestExtractFunctionModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestExtractFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -1,118 +0,0 @@ -/* - * 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.sql.Timestamp; -import java.util.Arrays; -import java.util.List; -import java.util.Properties; - -import org.teiid.connector.api.TypeFacility; -import org.teiid.connector.jdbc.oracle.ExtractFunctionModifier; -import org.teiid.connector.jdbc.oracle.OracleSQLTranslator; -import org.teiid.connector.jdbc.translator.SQLConversionVisitor; -import org.teiid.connector.language.IElement; -import org.teiid.connector.language.IExpression; -import org.teiid.connector.language.IFunction; -import org.teiid.connector.language.IGroup; -import org.teiid.connector.language.ILanguageFactory; -import org.teiid.connector.language.ILiteral; - -import junit.framework.TestCase; - -import com.metamatrix.cdk.CommandBuilder; -import com.metamatrix.cdk.api.EnvironmentUtility; -import com.metamatrix.query.unittest.TimestampUtil; - -/** - */ -public class TestExtractFunctionModifier extends TestCase { - - private static final ILanguageFactory LANG_FACTORY = CommandBuilder.getLanuageFactory(); - - /** - * Constructor for TestMonthFunctionModifier. - * @param name - */ - public TestExtractFunctionModifier(String name) { - super(name); - } - - public IExpression helpTestMod(IExpression c, String expectedStr, String target) throws Exception { - IFunction func = LANG_FACTORY.createFunction(target, - Arrays.asList(c), - Integer.class); - - ExtractFunctionModifier mod = new ExtractFunctionModifier (target); - IExpression expr = mod.modify(func); - List<?> parts = mod.translate(func); - assertEquals(7, parts.size()); - assertFalse(parts.get(5) instanceof String); - OracleSQLTranslator trans = new OracleSQLTranslator(); - trans.registerFunctionModifier("extract", mod); - trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); - - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - - sqlVisitor.append(expr); - assertEquals(expectedStr, sqlVisitor.toString()); - return expr; - } - public void test1() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); - helpTestMod(arg1, "EXTRACT(MONTH FROM {d'2004-01-21'})" , "month"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test2() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); - helpTestMod(arg1, "EXTRACT(MONTH FROM {ts'2004-01-21 17:05:00.0'})", "month"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test3() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); - helpTestMod(arg1, "EXTRACT(YEAR FROM {d'2004-01-21'})", "year"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test4() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); - helpTestMod(arg1, "EXTRACT(YEAR FROM {ts'2004-01-21 17:05:00.0'})", "year"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test5() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); - helpTestMod(arg1, "EXTRACT(DAY FROM {d'2004-01-21'})", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test6() throws Exception { - ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); - helpTestMod(arg1, "EXTRACT(DAY FROM {ts'2004-01-21 17:05:00.0'})", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - public void test11() throws Exception { - IGroup group = LANG_FACTORY.createGroup(null, "group", null); //$NON-NLS-1$ - IElement elem = LANG_FACTORY.createElement("col", group, null, TypeFacility.RUNTIME_TYPES.DATE); //$NON-NLS-1$ - helpTestMod(elem, "EXTRACT(DAY FROM col)", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ - } - -} - Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -289,7 +289,7 @@ public void testDayOfWeek() throws Exception { String input = "SELECT dayofweek(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT (date_part('dow', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) + 1) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT (EXTRACT(DOW FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) + 1) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -298,7 +298,7 @@ } public void testDayOfMonth() throws Exception { String input = "SELECT dayofmonth(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('day', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(DAY FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -307,7 +307,7 @@ } public void testDayOfYear() throws Exception { String input = "SELECT dayofyear(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('doy', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(DOY FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -316,7 +316,7 @@ } public void testHour() throws Exception { String input = "SELECT hour(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('hour', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(HOUR FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -325,7 +325,7 @@ } public void testMinute() throws Exception { String input = "SELECT minute(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('minute', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(MINUTE FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -334,7 +334,7 @@ } public void testMonth() throws Exception { String input = "SELECT month(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('month', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(MONTH FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -343,7 +343,7 @@ } public void testQuarter() throws Exception { String input = "SELECT quarter(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('quarter', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(QUARTER FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -352,7 +352,7 @@ } public void testSecond() throws Exception { String input = "SELECT second(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('second', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(SECOND FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -361,7 +361,7 @@ } public void testWeek() throws Exception { String input = "SELECT week(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('week', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(WEEK FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -370,7 +370,7 @@ } public void testYear() throws Exception { String input = "SELECT year(convert(PART_WEIGHT, timestamp)) FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT date_part('year', to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT EXTRACT(YEAR FROM to_timestamp(PARTS.PART_WEIGHT, 'YYYY-MM-DD HH24:MI:SS.UF')) FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -448,6 +448,17 @@ input, MODIFIERS, output); - } + } + + public void testBitFunctions() throws Exception { + String input = "select bitand(intkey, intnum), bitnot(intkey), bitor(intnum, intkey), bitxor(intnum, intkey) from bqt1.smalla"; //$NON-NLS-1$ + String output = "SELECT (SmallA.IntKey & SmallA.IntNum), ~(SmallA.IntKey), (SmallA.IntNum | SmallA.IntKey), (SmallA.IntNum # SmallA.IntKey) FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(getTestBQTVDB(), + input, + MODIFIERS, + output); + } + } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sqlserver/TestSqlServerConversionVisitor.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -113,5 +113,15 @@ input, output); } + + @Test + public void testDateFunctions() throws Exception { + String input = "select dayName(timestampValue), dayOfWeek(timestampValue), quarter(timestampValue) from bqt1.smalla"; //$NON-NLS-1$ + String output = "SELECT {fn dayName(SmallA.TimestampValue)}, {fn dayOfWeek(SmallA.TimestampValue)}, {fn quarter(SmallA.TimestampValue)} FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(getBQTVDB(), + input, + output); + } } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseConvertModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseConvertModifier.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseConvertModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -171,7 +171,7 @@ LANG_FACTORY.createLiteral("timestamp", String.class)}, //$NON-NLS-1$ java.sql.Timestamp.class); - helpGetString1(func, "convert(datetime, {ts'1970-01-01 12:02:03'})"); //$NON-NLS-1$ + helpGetString1(func, "convert(datetime, {ts'1900-01-01 12:02:03'})"); //$NON-NLS-1$ } public void testDateToTimestamp() throws Exception { @@ -236,7 +236,7 @@ LANG_FACTORY.createLiteral("string", String.class)}, //$NON-NLS-1$ String.class); - helpGetString1(func, "convert(varchar, {ts'1970-01-01 03:10:01'}, 108)"); //$NON-NLS-1$ + helpGetString1(func, "convert(varchar, {ts'1900-01-01 03:10:01'}, 108)"); //$NON-NLS-1$ } public void testBigDecimalToString() throws Exception { Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -190,7 +190,7 @@ public void testTimeLiteral() { helpTestVisitor(getTestVDB(), "select {t'13:59:59'} FROM parts", //$NON-NLS-1$ - "SELECT {ts'1970-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ + "SELECT {ts'1900-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ } @Test Copied: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestExtractFunctionModifier.java (from rev 1179, trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestExtractFunctionModifier.java) =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestExtractFunctionModifier.java (rev 0) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/translator/TestExtractFunctionModifier.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -0,0 +1,118 @@ +/* + * 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.translator; + +import java.sql.Timestamp; +import java.util.Arrays; +import java.util.Properties; + +import junit.framework.TestCase; + +import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.api.TypeFacility; +import org.teiid.connector.language.IElement; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFunction; +import org.teiid.connector.language.IGroup; +import org.teiid.connector.language.ILanguageFactory; +import org.teiid.connector.language.ILiteral; + +import com.metamatrix.cdk.CommandBuilder; +import com.metamatrix.cdk.api.EnvironmentUtility; +import com.metamatrix.query.unittest.TimestampUtil; + +/** + */ +public class TestExtractFunctionModifier extends TestCase { + + private static final ILanguageFactory LANG_FACTORY = CommandBuilder.getLanuageFactory(); + + /** + * Constructor for TestMonthFunctionModifier. + * @param name + */ + public TestExtractFunctionModifier(String name) { + super(name); + } + + public IExpression helpTestMod(IExpression c, String expectedStr, String target) throws Exception { + IFunction func = LANG_FACTORY.createFunction(target, + Arrays.asList(c), + Integer.class); + + ExtractFunctionModifier mod = new ExtractFunctionModifier (); + IExpression expr = mod.modify(func); + Translator trans = new Translator(); + trans.registerFunctionModifier(target, mod); + trans.initialize(EnvironmentUtility.createEnvironment(new Properties(), false)); + + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + + sqlVisitor.append(expr); + assertEquals(expectedStr, sqlVisitor.toString()); + return expr; + } + public void test1() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); + helpTestMod(arg1, "EXTRACT(MONTH FROM {d'2004-01-21'})" , "month"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test2() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); + helpTestMod(arg1, "EXTRACT(MONTH FROM {ts'2004-01-21 17:05:00.0'})", "month"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test3() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); + helpTestMod(arg1, "EXTRACT(YEAR FROM {d'2004-01-21'})", "year"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test4() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); + helpTestMod(arg1, "EXTRACT(YEAR FROM {ts'2004-01-21 17:05:00.0'})", "year"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test5() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createDate(104, 0, 21), java.sql.Date.class); + helpTestMod(arg1, "EXTRACT(DAY FROM {d'2004-01-21'})", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test6() throws Exception { + ILiteral arg1 = LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(104, 0, 21, 17, 5, 0, 0), Timestamp.class); + helpTestMod(arg1, "EXTRACT(DAY FROM {ts'2004-01-21 17:05:00.0'})", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test11() throws Exception { + IGroup group = LANG_FACTORY.createGroup(null, "group", null); //$NON-NLS-1$ + IElement elem = LANG_FACTORY.createElement("col", group, null, TypeFacility.RUNTIME_TYPES.DATE); //$NON-NLS-1$ + helpTestMod(elem, "EXTRACT(DAY FROM col)", "dayofmonth"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public void test12() throws Exception { + IGroup group = LANG_FACTORY.createGroup(null, "group", null); //$NON-NLS-1$ + IElement elem = LANG_FACTORY.createElement("col", group, null, TypeFacility.RUNTIME_TYPES.DATE); //$NON-NLS-1$ + helpTestMod(elem, "(EXTRACT(DOW FROM col) + 1)", SourceSystemFunctions.DAYOFWEEK); //$NON-NLS-1$ + } + +} + Modified: trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java =================================================================== --- trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-07-25 02:40:46 UTC (rev 1187) +++ trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-07-25 20:58:40 UTC (rev 1188) @@ -22,20 +22,19 @@ package com.metamatrix.connector.jdbc.oracle; +import static org.junit.Assert.*; + import java.util.Properties; -import junit.framework.TestCase; - +import org.junit.Test; import org.teiid.connector.api.ConnectorException; import org.teiid.connector.api.ExecutionContext; import org.teiid.connector.jdbc.JDBCPropertyNames; import org.teiid.connector.jdbc.oracle.OracleSQLTranslator; import org.teiid.connector.jdbc.translator.TranslatedCommand; import org.teiid.connector.language.ICommand; -import org.teiid.connector.metadata.runtime.RuntimeMetadata; import org.teiid.dqp.internal.datamgr.impl.ExecutionContextImpl; import org.teiid.dqp.internal.datamgr.impl.FakeExecutionContextImpl; -import org.teiid.dqp.internal.datamgr.metadata.RuntimeMetadataImpl; import com.metamatrix.cdk.CommandBuilder; import com.metamatrix.cdk.api.EnvironmentUtility; @@ -47,20 +46,10 @@ import com.metamatrix.query.unittest.FakeMetadataFactory; import com.metamatrix.query.unittest.FakeMetadataObject; import com.metamatrix.query.unittest.FakeMetadataStore; - -/** - */ -public class TestOracleSQLConversionVisitor extends TestCase { + +public class TestOracleSQLConversionVisitor { private static ExecutionContext EMPTY_CONTEXT = new FakeExecutionContextImpl(); - /** - * Constructor for TestOracleSQLConversionVisitor. - * @param name - */ - public TestOracleSQLConversionVisitor(String name) { - super(name); - } - private String getTestVDB() { return UnitTestUtil.getTestDataPath() + "/PartsSupplierOracle.vdb"; //$NON-NLS-1$ } @@ -77,7 +66,7 @@ // Convert from sql to objects TranslationUtility util = new TranslationUtility(vdb); ICommand obj = util.parseCommand(input, correctNaming, true); - this.helpTestVisitor(obj, util.createRuntimeMetadata(), context, dbmsTimeZone, expectedOutput); + this.helpTestVisitor(obj, context, dbmsTimeZone, expectedOutput); } /** Helper method takes a QueryMetadataInterface impl instead of a VDB filename @@ -87,11 +76,10 @@ // Convert from sql to objects CommandBuilder commandBuilder = new CommandBuilder(metadata); ICommand obj = commandBuilder.getCommand(input); - RuntimeMetadata runtimeMetadata = new RuntimeMetadataImpl(metadata); - this.helpTestVisitor(obj, runtimeMetadata, context, dbmsTimeZone, expectedOutput); + this.helpTestVisitor(obj, context, dbmsTimeZone, expectedOutput); } - private void helpTestVisitor(ICommand obj, RuntimeMetadata metadata, ExecutionContext context, String dbmsTimeZone, String expectedOutput) throws ConnectorException { + private void helpTestVisitor(ICommand obj, ExecutionContext context, String dbmsTimeZone, String expectedOutput) throws ConnectorException { // Apply function replacement @@ -124,7 +112,7 @@ } /** defect 21775 */ - public void testDateStuff() throws Exception { + @Test public void testDateStuff() throws Exception { String input = "SELECT ((CASE WHEN month(datevalue) < 10 THEN ('0' || convert(month(datevalue), string)) ELSE convert(month(datevalue), string) END || CASE WHEN dayofmonth(datevalue) < 10 THEN ('0' || convert(dayofmonth(datevalue), string)) ELSE convert(dayofmonth(datevalue), string) END) || convert(year(datevalue), string)), SUM(intkey) FROM bqt1.SMALLA GROUP BY datevalue"; //$NON-NLS-1$ String output = "SELECT CASE WHEN (CASE WHEN (CASE WHEN EXTRACT(MONTH FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(MONTH FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(MONTH FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(MONTH FROM SmallA.DateValue)) END IS NULL) OR (CASE WHEN EXTRACT(DAY FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(DAY FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(DAY FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(DAY FROM SmallA.DateValue)) END IS NULL) THEN NULL ELSE concat(CASE WHEN EXTRACT(MONTH FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(MONTH FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(MONTH FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(MONTH FROM SmallA.DateValue)) END, CASE WHEN EXTRACT(DAY FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(DAY FROM SmallA.DateValue)) IS NULL THEN NULL ELSE con! cat('0', to_char(EXTRACT(DAY FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(DAY FROM SmallA.DateValue)) END) END IS NULL) OR (to_char(EXTRACT(YEAR FROM SmallA.DateValue)) IS NULL) THEN NULL ELSE concat(CASE WHEN (CASE WHEN EXTRACT(MONTH FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(MONTH FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(MONTH FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(MONTH FROM SmallA.DateValue)) END IS NULL) OR (CASE WHEN EXTRACT(DAY FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(DAY FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(DAY FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(DAY FROM SmallA.DateValue)) END IS NULL) THEN NULL ELSE concat(CASE WHEN EXTRACT(MONTH FROM SmallA.DateValue) < 10 THEN CASE WHEN to_char(EXTRACT(MONTH FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(MONTH FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(MONTH FR! OM SmallA.DateValue)) END, CASE WHEN EXTRACT(DAY FROM SmallA.D! ateValue ) < 10 THEN CASE WHEN to_char(EXTRACT(DAY FROM SmallA.DateValue)) IS NULL THEN NULL ELSE concat('0', to_char(EXTRACT(DAY FROM SmallA.DateValue))) END ELSE to_char(EXTRACT(DAY FROM SmallA.DateValue)) END) END, to_char(EXTRACT(YEAR FROM SmallA.DateValue))) END, SUM(SmallA.IntKey) FROM SmallA GROUP BY SmallA.DateValue"; //$NON-NLS-1$ @@ -148,7 +136,7 @@ EMPTY_CONTEXT, null, output); } - public void testCharFunction() throws Exception { + @Test public void testCharFunction() throws Exception { String input = "SELECT char(CONVERT(PART_ID, INTEGER)) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT chr(to_number(PARTS.PART_ID)) FROM PARTS"; //$NON-NLS-1$ @@ -158,7 +146,7 @@ output); } - public void testLcaseFunction() throws Exception { + @Test public void testLcaseFunction() throws Exception { String input = "SELECT lcase(PART_NAME) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT lower(PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -168,7 +156,7 @@ output); } - public void testUcaseFunction() throws Exception { + @Test public void testUcaseFunction() throws Exception { String input = "SELECT ucase(PART_NAME) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT upper(PARTS.PART_NAME) FROM PARTS"; //$NON-NLS-1$ @@ -178,7 +166,7 @@ output); } - public void testIfnullFunction() throws Exception { + @Test public void testIfnullFunction() throws Exception { String input = "SELECT ifnull(PART_NAME, 'x') FROM PARTS"; //$NON-NLS-1$ String output = "SELECT nvl(PARTS.PART_NAME, 'x') FROM PARTS"; //$NON-NLS-1$ @@ -188,7 +176,7 @@ output); } - public void testLogFunction() throws Exception { + @Test public void testLogFunction() throws Exception { String input = "SELECT log(CONVERT(PART_ID, INTEGER)) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT ln(to_number(PARTS.PART_ID)) FROM PARTS"; //$NON-NLS-1$ @@ -198,7 +186,7 @@ output); } - public void testLog10Function() throws Exception { + @Test public void testLog10Function() throws Exception { String input = "SELECT log10(CONVERT(PART_ID, INTEGER)) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT log(10, to_number(PARTS.PART_ID)) FROM PARTS"; //$NON-NLS-1$ @@ -208,7 +196,7 @@ output); } - public void testConvertFunctionInteger() throws Exception { + @Test public void testConvertFunctionInteger() throws Exception { String input = "SELECT convert(PARTS.PART_ID, integer) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_number(PARTS.PART_ID) FROM PARTS"; //$NON-NLS-1$ @@ -219,7 +207,7 @@ output); } - public void testConvertFunctionChar() throws Exception { + @Test public void testConvertFunctionChar() throws Exception { String input = "SELECT convert(PARTS.PART_ID, char) FROM PARTS"; //$NON-NLS-1$ String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ @@ -229,7 +217,7 @@ output); } - public void testConvertFunctionBoolean() throws Exception { + @Test public void testConvertFunctionBoolean() throws Exception { String input = "SELECT convert(PARTS.PART_ID, boolean) FROM PARTS"; //$NON-NLS-1$ //String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ String output = "SELECT decode(PARTS.PART_ID, 'true', 1, 'false', 0) FROM PARTS"; //$NON-NLS-1$ @@ -240,7 +228,7 @@ output); } - public void testConvertFunctionDate() throws Exception { + @Test public void testConvertFunctionDate() throws Exception { String input = "SELECT convert(PARTS.PART_ID, date) FROM PARTS"; //$NON-NLS-1$ //String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_date(PARTS.PART_ID, 'YYYY-MM-DD') FROM PARTS"; //$NON-NLS-1$ @@ -250,7 +238,7 @@ output); } - public void testConvertFunctionTime() throws Exception { + @Test public void testConvertFunctionTime() throws Exception { String input = "SELECT convert(PARTS.PART_ID, time) FROM PARTS"; //$NON-NLS-1$ //String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_date(('1970-01-01 ' || to_char(PARTS.PART_ID, 'HH24:MI:SS')), 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"; //$NON-NLS-1$ @@ -261,7 +249,7 @@ output); } - public void testConvertFunctionTimestamp() throws Exception { + @Test public void testConvertFunctionTimestamp() throws Exception { String input = "SELECT convert(PARTS.PART_ID, timestamp) FROM PARTS"; //$NON-NLS-1$ //String output = "SELECT PARTS.PART_ID FROM PARTS"; //$NON-NLS-1$ String output = "SELECT to_timestamp(PARTS.PART_ID, 'YYYY-MM-DD HH24:MI:SS.FF') FROM PARTS"; //$NON-NLS-1$ @@ -272,7 +260,7 @@ output); } - public void testExtractFunctionTimestamp() throws Exception { + @Test public void testExtractFunctionTimestamp() throws Exception { String input = "SELECT month(TIMESTAMPVALUE) FROM BQT1.Smalla"; //$NON-NLS-1$ String output = "SELECT EXTRACT(MONTH FROM SmallA.TimestampValue) FROM SmallA"; //$NON-NLS-1$ @@ -281,58 +269,58 @@ EMPTY_CONTEXT, null, output); } - public void testAliasedGroup() throws Exception { + @Test public void testAliasedGroup() throws Exception { helpTestVisitor(getTestVDB(), "select y.part_name from parts as y", //$NON-NLS-1$ null, "SELECT y.PART_NAME FROM PARTS y"); //$NON-NLS-1$ } - public void testDateLiteral() throws Exception { + @Test public void testDateLiteral() throws Exception { helpTestVisitor(getTestVDB(), "select {d'2002-12-31'} FROM parts", //$NON-NLS-1$ null, "SELECT {d'2002-12-31'} FROM PARTS"); //$NON-NLS-1$ } - public void testTimeLiteral() throws Exception { + @Test public void testTimeLiteral() throws Exception { helpTestVisitor(getTestVDB(), "select {t'13:59:59'} FROM parts", //$NON-NLS-1$ null, "SELECT {ts'1970-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ } - public void testTimestampLiteral() throws Exception { + @Test public void testTimestampLiteral() 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$ } - public void testUnionOrderByWithThreeBranches() throws Exception { + @Test public void testUnionOrderByWithThreeBranches() throws Exception { helpTestVisitor(getTestVDB(), "select part_id id FROM parts UNION ALL select part_name FROM parts UNION ALL select part_id FROM parts ORDER BY id", //$NON-NLS-1$ null, - "(SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT g_1.PART_NAME AS c_0 FROM PARTS g_1) UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0 ORDER BY c_0", - true); //$NON-NLS-1$ + "(SELECT g_2.PART_ID AS c_0 FROM PARTS g_2 UNION ALL SELECT g_1.PART_NAME AS c_0 FROM PARTS g_1) UNION ALL SELECT g_0.PART_ID AS c_0 FROM PARTS g_0 ORDER BY c_0", //$NON-NLS-1$ + true); } - public void testUnionOrderBy() throws Exception { + @Test public void testUnionOrderBy() throws Exception { helpTestVisitor(getTestVDB(), "select part_id FROM parts UNION ALL select part_name FROM parts ORDER BY part_id", //$NON-NLS-1$ null, - "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0", - true); //$NON-NLS-1$ + "SELECT g_1.PART_ID AS c_0 FROM PARTS g_1 UNION ALL SELECT g_0.PART_NAME AS c_0 FROM PARTS g_0 ORDER BY c_0", //$NON-NLS-1$ + true); } - public void testUnionOrderBy2() throws Exception { + @Test public void testUnionOrderBy2() throws Exception { helpTestVisitor(getTestVDB(), "select part_id as p FROM parts UNION ALL select part_name FROM parts ORDER BY p", //$NON-NLS-1$ null, "SELECT PARTS.PART_ID AS p FROM PARTS UNION ALL SELECT PARTS.PART_NAME FROM PARTS ORDER BY p"); //$NON-NLS-1$ } - public void testUpdateWithFunction() throws Exception { + @Test public void testUpdateWithFunction() throws Exception { String input = "UPDATE bqt1.smalla SET intkey = intkey + 1"; //$NON-NLS-1$ String output = "UPDATE SmallA SET IntKey = (SmallA.IntKey + 1)"; //$NON-NLS-1$ @@ -351,7 +339,7 @@ * * @since 4.3 */ - public void testDUAL() throws Exception { + @Test public void testDUAL() throws Exception { String input = "SELECT something FROM DUAL"; //$NON-NLS-1$ String output = "SELECT something FROM DUAL"; //$NON-NLS-1$ @@ -369,7 +357,7 @@ * * @since 4.3 */ - public void testROWNUM() throws Exception { + @Test public void testROWNUM() throws Exception { String input = "SELECT part_name, rownum FROM parts"; //$NON-NLS-1$ String output = "SELECT PARTS.PART_NAME, ROWNUM FROM PARTS"; //$NON-NLS-1$ @@ -386,7 +374,7 @@ * * @since 4.3 */ - public void testROWNUM2() throws Exception { + @Test public void testROWNUM2() throws Exception { String input = "SELECT part_name FROM parts where rownum < 100"; //$NON-NLS-1$ String output = "SELECT PARTS.PART_NAME FROM PARTS WHERE ROWNUM < 100"; //$NON-NLS-1$ @@ -402,7 +390,7 @@ * * @since 4.3 */ - public void testOracleCommentPayload() throws Exception { + @Test public void testOracleCommentPayload() throws Exception { String input = "SELECT part_name, rownum FROM parts"; //$NON-NLS-1$ String output = "SELECT /*+ ALL_ROWS */ PARTS.PART_NAME, ROWNUM FROM PARTS"; //$NON-NLS-1$ @@ -421,7 +409,7 @@ * reproducing this case relies on the name in source for the table being different from * the name */ - public void testCase3845() throws Exception { + @Test public void testCase3845() throws Exception { String input = "SELECT (DoubleNum * 1.0) FROM BQT1.Smalla"; //$NON-NLS-1$ String output = "SELECT (SmallishA.DoubleNum * 1.0) FROM SmallishA"; //$NON-NLS-1$ @@ -450,7 +438,7 @@ helpTestVisitor(vdb, input, null, expectedOutput); } - public void testRowLimit2() throws Exception { + @Test public void testRowLimit2() throws Exception { String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$ String output = "SELECT * FROM (SELECT SmallA.IntKey FROM SmallA) WHERE ROWNUM <= 100"; //$NON-NLS-1$ @@ -459,7 +447,7 @@ EMPTY_CONTEXT, null, output); } - public void testRowLimit3() throws Exception { + @Test public void testRowLimit3() throws Exception { String input = "select intkey from bqt1.smalla limit 50, 100"; //$NON-NLS-1$ String output = "SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT SmallA.IntKey FROM SmallA) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50"; //$NON-NLS-1$ @@ -468,7 +456,7 @@ EMPTY_CONTEXT, null, output); } - public void testLimitWithNestedInlineView() throws Exception { + @Test public void testLimitWithNestedInlineView() throws Exception { String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by intkey"; //$NON-NLS-1$ String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey) WHERE ROWNUM <= 100) x GROUP BY x.intkey"; //$NON-NLS-1$ @@ -477,7 +465,7 @@ EMPTY_CONTEXT, null, output); } - public void testExceptAsMinus() throws Exception { + @Test public void testExceptAsMinus() throws Exception { String input = "select intkey, intnum from bqt1.smalla except select intnum, intkey from bqt1.smallb"; //$NON-NLS-1$ String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA MINUS SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; //$NON-NLS-1$ @@ -486,27 +474,27 @@ EMPTY_CONTEXT, null, output); } - public void testConcat2_useLiteral() throws Exception { + @Test public void testConcat2_useLiteral() throws Exception { String sql = "select concat2(stringnum,'_xx') from BQT1.Smalla"; //$NON-NLS-1$ String expected = "SELECT concat(nvl(SmallA.StringNum, ''), '_xx') FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); } - public void testConcat2() throws Exception { + @Test public void testConcat2() throws Exception { String sql = "select concat2(stringnum, stringnum) from BQT1.Smalla"; //$NON-NLS-1$ - String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(nvl(SmallA.StringNum, ''), nvl(SmallA.StringNum, '')) END FROM SmallA"; + String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(nvl(SmallA.StringNum, ''), nvl(SmallA.StringNum, '')) END FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); } - public void testConcat() throws Exception { + @Test public void testConcat() throws Exception { String sql = "select concat(stringnum, stringkey) from BQT1.Smalla"; //$NON-NLS-1$ - String expected = "SELECT CASE WHEN (SmallA.StringNum IS NULL) OR (SmallA.StringKey IS NULL) THEN NULL ELSE concat(SmallA.StringNum, SmallA.StringKey) END FROM SmallA"; + String expected = "SELECT CASE WHEN (SmallA.StringNum IS NULL) OR (SmallA.StringKey IS NULL) THEN NULL ELSE concat(SmallA.StringNum, SmallA.StringKey) END FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); } - public void testConcat_withLiteral() throws Exception { + @Test public void testConcat_withLiteral() throws Exception { String sql = "select stringnum || '1' from BQT1.Smalla"; //$NON-NLS-1$ - String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; + String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); }