[teiid-commits] teiid SVN: r4353 - in branches/7.4.x: connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase and 1 other directory.

Author: jolee Date: 2012-08-21 12:45:15 -0400 (Tue, 21 Aug 2012) New Revision: 4353 Modified: branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java Log: TEIID-2157 Need ability to push down LIMIT clause to Sybase Modified: branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2012-08-21 16:33:11 UTC (rev 4352) +++ branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2012-08-21 16:45:15 UTC (rev 4353) @@ -265,6 +265,7 @@ <li>[<a href='https://issues.jboss.org/browse/TEIID-2120'>TEIID-2120&l...>] - fix for batchiterator early termination</li> <li>[<a href='https://issues.jboss.org/browse/TEIID-2146'>TEIID-2146&l...>] - Dependent join independent dup removal issue</li> <li>[<a href='https://issues.jboss.org/browse/TEIID-1956'>TEIID-1956&l...>] - (updated) an additional fix for having the same element repeated in equi-join criteria</li> +<li>[<a href='https://issues.jboss.org/browse/TEIID-2157'>TEIID-2157&l...>] - Need ability to push down LIMIT clause to Sybase</li> </ul> Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-08-21 16:33:11 UTC (rev 4352) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-08-21 16:45:15 UTC (rev 4353) @@ -1,394 +1,400 @@ -/* - * 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. - */ - /* + * 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.translator.jdbc.sybase; -import java.sql.CallableStatement; -import java.sql.Date; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Timestamp; -import java.util.ArrayList; -import java.util.Arrays; -import java.util.LinkedList; -import java.util.List; - -import org.teiid.language.Command; -import org.teiid.language.Function; -import org.teiid.language.LanguageObject; -import org.teiid.language.Limit; -import org.teiid.language.OrderBy; -import org.teiid.language.SetQuery; -import org.teiid.translator.ExecutionContext; -import org.teiid.translator.SourceSystemFunctions; -import org.teiid.translator.Translator; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.AliasModifier; -import org.teiid.translator.jdbc.ConvertModifier; -import org.teiid.translator.jdbc.EscapeSyntaxModifier; -import org.teiid.translator.jdbc.FunctionModifier; -import org.teiid.translator.jdbc.JDBCExecutionFactory; -import org.teiid.translator.jdbc.ModFunctionModifier; -import org.teiid.translator.jdbc.db2.DB2ExecutionFactory; -import org.teiid.translator.jdbc.oracle.ConcatFunctionModifier; - +import java.sql.CallableStatement; +import java.sql.Date; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Timestamp; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.LinkedList; +import java.util.List; +import org.teiid.language.Command; +import org.teiid.language.Function; +import org.teiid.language.LanguageObject; +import org.teiid.language.Limit; +import org.teiid.language.OrderBy; +import org.teiid.language.SetQuery; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; +import org.teiid.translator.Translator; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.AliasModifier; +import org.teiid.translator.jdbc.ConvertModifier; +import org.teiid.translator.jdbc.EscapeSyntaxModifier; +import org.teiid.translator.jdbc.FunctionModifier; +import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.ModFunctionModifier; +import org.teiid.translator.jdbc.db2.DB2ExecutionFactory; +import org.teiid.translator.jdbc.oracle.ConcatFunctionModifier; + + @Translator(name="sybase", description="A translator for Sybase Database") -public class SybaseExecutionFactory extends JDBCExecutionFactory { - - public static final String TWELVE_5 = "12.5"; //$NON-NLS-1$ - public static final String FIFTEEN_0_2 = "15.0.2"; //$NON-NLS-1$ - public static final String FIFTEEN_5 = "15.5"; //$NON-NLS-1$ - - public SybaseExecutionFactory() { - setDatabaseVersion(TWELVE_5); - setSupportsFullOuterJoins(false); - setMaxInCriteriaSize(250); - setMaxDependentInPredicates(10); +public class SybaseExecutionFactory extends JDBCExecutionFactory { + + public static final String TWELVE_5 = "12.5"; //$NON-NLS-1$ + public static final String TWELVE_5_3 = "12.5.3"; //$NON-NLS-1$ + public static final String FIFTEEN_0_2 = "15.0.2"; //$NON-NLS-1$ + public static final String FIFTEEN_5 = "15.5"; //$NON-NLS-1$ + + public SybaseExecutionFactory() { + setDatabaseVersion(TWELVE_5); + setSupportsFullOuterJoins(false); + setMaxInCriteriaSize(250); + setMaxDependentInPredicates(10); } public void start() throws TranslatorException { - super.start(); + super.start(); - registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory())); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory())); //$NON-NLS-1$ if (nullPlusNonNullIsNull()) { - registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("+")); //$NON-NLS-1$ - } else { - registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory()) { - @Override - public List<?> translate(Function function) { - function.setName("+"); //$NON-NLS-1$ - return super.translate(function); - } - }); + registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("+")); //$NON-NLS-1$ + } else { + registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory()) { + @Override + public List<?> translate(Function function) { + function.setName("+"); //$NON-NLS-1$ + return super.translate(function); + } + }); } registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("lower")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("isnull")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.REPEAT, new AliasModifier("replicate")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new SubstringFunctionModifier(getLanguageFactory())); - registerFunctionModifier(SourceSystemFunctions.DAYNAME, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, 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()); - registerFunctionModifier(SourceSystemFunctions.LENGTH, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.ATAN2, new EscapeSyntaxModifier()); - - //add in type conversion - ConvertModifier convertModifier = new ConvertModifier(); - convertModifier.setBooleanNullable(booleanNullable()); - //boolean isn't treated as bit, since it doesn't support null - //byte is treated as smallint, since tinyint is unsigned - convertModifier.addTypeMapping("smallint", FunctionModifier.BYTE, FunctionModifier.SHORT); //$NON-NLS-1$ - convertModifier.addTypeMapping("int", FunctionModifier.INTEGER); //$NON-NLS-1$ - convertModifier.addTypeMapping("numeric(19,0)", FunctionModifier.LONG); //$NON-NLS-1$ - convertModifier.addTypeMapping("real", FunctionModifier.FLOAT); //$NON-NLS-1$ - convertModifier.addTypeMapping("double precision", FunctionModifier.DOUBLE); //$NON-NLS-1$ - convertModifier.addTypeMapping("numeric(38, 0)", FunctionModifier.BIGINTEGER); //$NON-NLS-1$ - convertModifier.addTypeMapping("numeric(38, 19)", FunctionModifier.BIGDECIMAL); //$NON-NLS-1$ - convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$ - convertModifier.addTypeMapping("varchar(40)", FunctionModifier.STRING); //$NON-NLS-1$ - convertModifier.addTypeMapping("datetime", FunctionModifier.DATE, FunctionModifier.TIME, FunctionModifier.TIMESTAMP); //$NON-NLS-1$ - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - List<Object> result = new ArrayList<Object>(); - result.add("cast("); //$NON-NLS-1$ - boolean needsEnd = false; - if (!nullPlusNonNullIsNull() && !ConcatFunctionModifier.isNotNull(function.getParameters().get(0))) { - result.add("CASE WHEN "); //$NON-NLS-1$ - result.add(function.getParameters().get(0)); - result.add(" IS NOT NULL THEN "); //$NON-NLS-1$ - needsEnd = true; - } - result.add("'1970-01-01 ' + "); //$NON-NLS-1$ - result.addAll(convertTimeToString(function)); - if (needsEnd) { - result.add(" END"); //$NON-NLS-1$ - } - result.add(" AS datetime)"); //$NON-NLS-1$ - return result; - } - }); - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - List<Object> result = new ArrayList<Object>(); - result.add("cast("); //$NON-NLS-1$ - result.addAll(convertDateToString(function)); - result.add(" AS datetime)"); //$NON-NLS-1$ - return result; - } - }); - convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - return convertTimeToString(function); - } - }); - convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - return convertDateToString(function); - } - }); - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - return convertTimestampToString(function); - } - }); - convertModifier.addNumericBooleanConversions(); + registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.REPEAT, new AliasModifier("replicate")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new SubstringFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.DAYNAME, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, 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()); + registerFunctionModifier(SourceSystemFunctions.LENGTH, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.ATAN2, new EscapeSyntaxModifier()); + + //add in type conversion + ConvertModifier convertModifier = new ConvertModifier(); + convertModifier.setBooleanNullable(booleanNullable()); + //boolean isn't treated as bit, since it doesn't support null + //byte is treated as smallint, since tinyint is unsigned + convertModifier.addTypeMapping("smallint", FunctionModifier.BYTE, FunctionModifier.SHORT); //$NON-NLS-1$ + convertModifier.addTypeMapping("int", FunctionModifier.INTEGER); //$NON-NLS-1$ + convertModifier.addTypeMapping("numeric(19,0)", FunctionModifier.LONG); //$NON-NLS-1$ + convertModifier.addTypeMapping("real", FunctionModifier.FLOAT); //$NON-NLS-1$ + convertModifier.addTypeMapping("double precision", FunctionModifier.DOUBLE); //$NON-NLS-1$ + convertModifier.addTypeMapping("numeric(38, 0)", FunctionModifier.BIGINTEGER); //$NON-NLS-1$ + convertModifier.addTypeMapping("numeric(38, 19)", FunctionModifier.BIGDECIMAL); //$NON-NLS-1$ + convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$ + convertModifier.addTypeMapping("varchar(40)", FunctionModifier.STRING); //$NON-NLS-1$ + convertModifier.addTypeMapping("datetime", FunctionModifier.DATE, FunctionModifier.TIME, FunctionModifier.TIMESTAMP); //$NON-NLS-1$ + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + List<Object> result = new ArrayList<Object>(); + result.add("cast("); //$NON-NLS-1$ + boolean needsEnd = false; + if (!nullPlusNonNullIsNull() && !ConcatFunctionModifier.isNotNull(function.getParameters().get(0))) { + result.add("CASE WHEN "); //$NON-NLS-1$ + result.add(function.getParameters().get(0)); + result.add(" IS NOT NULL THEN "); //$NON-NLS-1$ + needsEnd = true; + } + result.add("'1970-01-01 ' + "); //$NON-NLS-1$ + result.addAll(convertTimeToString(function)); + if (needsEnd) { + result.add(" END"); //$NON-NLS-1$ + } + result.add(" AS datetime)"); //$NON-NLS-1$ + return result; + } + }); + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + List<Object> result = new ArrayList<Object>(); + result.add("cast("); //$NON-NLS-1$ + result.addAll(convertDateToString(function)); + result.add(" AS datetime)"); //$NON-NLS-1$ + return result; + } + }); + convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return convertTimeToString(function); + } + }); + convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return convertDateToString(function); + } + }); + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return convertTimestampToString(function); + } + }); + convertModifier.addNumericBooleanConversions(); registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); - } - - private List<Object> convertTimeToString(Function function) { - return Arrays.asList("convert(varchar, ", function.getParameters().get(0), ", 8)"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - protected List<Object> convertDateToString(Function function) { - return Arrays.asList("stuff(stuff(convert(varchar, ", function.getParameters().get(0), ", 102), 5, 1, '-'), 8, 1, '-')"); //$NON-NLS-1$ //$NON-NLS-2$ - } - - //TODO: this looses the milliseconds - protected List<?> convertTimestampToString(Function function) { - LinkedList<Object> result = new LinkedList<Object>(); - result.addAll(convertDateToString(function)); - result.add('+'); - result.addAll(convertTimeToString(function)); - return result; - } - - @Override - public boolean useAsInGroupAlias() { - return false; - } - - @Override - public boolean hasTimeType() { - return false; - } - - @Override - public int getTimestampNanoPrecision() { - return 3; - } - - /** - * SetQueries don't have a concept of TOP, an inline view is needed. - */ - @Override - public List<?> translateCommand(Command command, ExecutionContext context) { - if (!(command instanceof SetQuery)) { - return null; - } - SetQuery queryCommand = (SetQuery)command; - if (queryCommand.getLimit() == null) { - return null; - } - Limit limit = queryCommand.getLimit(); - OrderBy orderBy = queryCommand.getOrderBy(); - queryCommand.setLimit(null); - queryCommand.setOrderBy(null); - List<Object> parts = new ArrayList<Object>(6); - parts.add("SELECT "); //$NON-NLS-1$ - parts.addAll(translateLimit(limit, context)); - parts.add(" * FROM ("); //$NON-NLS-1$ - parts.add(queryCommand); - parts.add(") AS X"); //$NON-NLS-1$ - if (orderBy != null) { - parts.add(" "); //$NON-NLS-1$ - parts.add(orderBy); - } - return parts; - } - - @Override - public List<?> translate(LanguageObject obj, ExecutionContext context) { - if (!supportsCrossJoin()) { - DB2ExecutionFactory.convertCrossJoinToInner(obj, getLanguageFactory()); - } - return super.translate(obj, context); - } - - @SuppressWarnings("unchecked") - @Override - public List<?> translateLimit(Limit limit, ExecutionContext context) { - return Arrays.asList("TOP ", limit.getRowLimit()); //$NON-NLS-1$ - } - - @Override - public boolean useSelectLimit() { - return true; - } - - @Override - public List<String> getSupportedFunctions() { - List<String> supportedFunctions = new ArrayList<String>(); - supportedFunctions.addAll(super.getSupportedFunctions()); - supportedFunctions.add("ABS"); //$NON-NLS-1$ - supportedFunctions.add("ACOS"); //$NON-NLS-1$ - supportedFunctions.add("ASIN"); //$NON-NLS-1$ - supportedFunctions.add("ATAN"); //$NON-NLS-1$ - supportedFunctions.add("ATAN2"); //$NON-NLS-1$ - supportedFunctions.add("COS"); //$NON-NLS-1$ - supportedFunctions.add("COT"); //$NON-NLS-1$ - supportedFunctions.add("DEGREES"); //$NON-NLS-1$ - supportedFunctions.add("EXP"); //$NON-NLS-1$ - supportedFunctions.add("FLOOR"); //$NON-NLS-1$ - supportedFunctions.add("LOG"); //$NON-NLS-1$ - supportedFunctions.add("LOG10"); //$NON-NLS-1$ - supportedFunctions.add("MOD"); //$NON-NLS-1$ - supportedFunctions.add("PI"); //$NON-NLS-1$ - supportedFunctions.add("POWER"); //$NON-NLS-1$ - supportedFunctions.add("RADIANS"); //$NON-NLS-1$ - supportedFunctions.add("SIGN"); //$NON-NLS-1$ - supportedFunctions.add("SIN"); //$NON-NLS-1$ - supportedFunctions.add("SQRT"); //$NON-NLS-1$ - supportedFunctions.add("TAN"); //$NON-NLS-1$ - supportedFunctions.add("ASCII"); //$NON-NLS-1$ - supportedFunctions.add("CHAR"); //$NON-NLS-1$ - supportedFunctions.add("CHR"); //$NON-NLS-1$ - supportedFunctions.add("CONCAT"); //$NON-NLS-1$ - supportedFunctions.add("||"); //$NON-NLS-1$ - supportedFunctions.add("LCASE"); //$NON-NLS-1$ - supportedFunctions.add("LEFT"); //$NON-NLS-1$ - supportedFunctions.add("LENGTH"); //$NON-NLS-1$ - supportedFunctions.add("LOWER"); //$NON-NLS-1$ - supportedFunctions.add("LTRIM"); //$NON-NLS-1$ - supportedFunctions.add("REPEAT"); //$NON-NLS-1$ - //supportedFunctions.add("RAND"); //$NON-NLS-1$ - supportedFunctions.add("RIGHT"); //$NON-NLS-1$ - supportedFunctions.add("RTRIM"); //$NON-NLS-1$ - supportedFunctions.add("SPACE"); //$NON-NLS-1$ - supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ - supportedFunctions.add("UCASE"); //$NON-NLS-1$ - supportedFunctions.add("UPPER"); //$NON-NLS-1$ - //supportedFunctons.add("CURDATE"); //$NON-NLS-1$ - //supportedFunctons.add("CURTIME"); //$NON-NLS-1$ - supportedFunctions.add("DAYNAME"); //$NON-NLS-1$ - supportedFunctions.add("DAYOFMONTH"); //$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$ - 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$ - supportedFunctions.add("IFNULL"); //$NON-NLS-1$ - supportedFunctions.add("NVL"); //$NON-NLS-1$ - //supportedFunctions.add("FORMATTIMESTAMP"); //$NON-NLS-1$ - - return supportedFunctions; - } - - @Override - public boolean supportsInlineViews() { - return true; - } - - @Override - public boolean supportsFunctionsInGroupBy() { - return true; - } - - @Override - public int getMaxFromGroups() { - return 50; - } - - @Override - public boolean supportsAggregatesEnhancedNumeric() { - return getDatabaseVersion().compareTo(FIFTEEN_0_2) >= 0; - } - - public boolean nullPlusNonNullIsNull() { - return false; - } - - public boolean booleanNullable() { - return false; - } - - @Override - public Object retrieveValue(ResultSet results, int columnIndex, - Class<?> expectedType) throws SQLException { - if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { - expectedType = TypeFacility.RUNTIME_TYPES.SHORT; - } - return super.retrieveValue(results, columnIndex, expectedType); - } - - @Override - public Object retrieveValue(CallableStatement results, int parameterIndex, - Class<?> expectedType) throws SQLException { - if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { - expectedType = TypeFacility.RUNTIME_TYPES.SHORT; - } - return super.retrieveValue(results, parameterIndex, expectedType); - } - - @Override - public void bindValue(PreparedStatement stmt, Object param, - Class<?> paramType, int i) throws SQLException { - if (paramType == TypeFacility.RUNTIME_TYPES.BYTE) { - paramType = TypeFacility.RUNTIME_TYPES.SHORT; - param = ((Byte)param).shortValue(); - } - super.bindValue(stmt, param, paramType, i); - } - - @Override - public String translateLiteralTimestamp(Timestamp timestampValue) { - return "CAST('" + formatDateValue(timestampValue) +"' AS DATETIME)"; //$NON-NLS-1$ //$NON-NLS-2$ - } - - @Override - public String translateLiteralDate(Date dateValue) { - return "CAST('" + formatDateValue(dateValue) +"' AS DATE)"; //$NON-NLS-1$ //$NON-NLS-2$ - } - - protected boolean supportsCrossJoin() { - return false; - } - + } + + private List<Object> convertTimeToString(Function function) { + return Arrays.asList("convert(varchar, ", function.getParameters().get(0), ", 8)"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + protected List<Object> convertDateToString(Function function) { + return Arrays.asList("stuff(stuff(convert(varchar, ", function.getParameters().get(0), ", 102), 5, 1, '-'), 8, 1, '-')"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + //TODO: this looses the milliseconds + protected List<?> convertTimestampToString(Function function) { + LinkedList<Object> result = new LinkedList<Object>(); + result.addAll(convertDateToString(function)); + result.add('+'); + result.addAll(convertTimeToString(function)); + return result; + } + + @Override + public boolean useAsInGroupAlias() { + return false; + } + + @Override + public boolean hasTimeType() { + return false; + } + + @Override + public int getTimestampNanoPrecision() { + return 3; + } + + /** + * SetQueries don't have a concept of TOP, an inline view is needed. + */ + @Override + public List<?> translateCommand(Command command, ExecutionContext context) { + if (!(command instanceof SetQuery)) { + return null; + } + SetQuery queryCommand = (SetQuery)command; + if (queryCommand.getLimit() == null) { + return null; + } + Limit limit = queryCommand.getLimit(); + OrderBy orderBy = queryCommand.getOrderBy(); + queryCommand.setLimit(null); + queryCommand.setOrderBy(null); + List<Object> parts = new ArrayList<Object>(6); + parts.add("SELECT "); //$NON-NLS-1$ + parts.addAll(translateLimit(limit, context)); + parts.add(" * FROM ("); //$NON-NLS-1$ + parts.add(queryCommand); + parts.add(") AS X"); //$NON-NLS-1$ + if (orderBy != null) { + parts.add(" "); //$NON-NLS-1$ + parts.add(orderBy); + } + return parts; + } + + @Override + public List<?> translate(LanguageObject obj, ExecutionContext context) { + if (!supportsCrossJoin()) { + DB2ExecutionFactory.convertCrossJoinToInner(obj, getLanguageFactory()); + } + return super.translate(obj, context); + } + + @SuppressWarnings("unchecked") + @Override + public List<?> translateLimit(Limit limit, ExecutionContext context) { + return Arrays.asList("TOP ", limit.getRowLimit()); //$NON-NLS-1$ + } + + @Override + public boolean useSelectLimit() { + return true; + } + + @Override + public List<String> getSupportedFunctions() { + List<String> supportedFunctions = new ArrayList<String>(); + supportedFunctions.addAll(super.getSupportedFunctions()); + supportedFunctions.add("ABS"); //$NON-NLS-1$ + supportedFunctions.add("ACOS"); //$NON-NLS-1$ + supportedFunctions.add("ASIN"); //$NON-NLS-1$ + supportedFunctions.add("ATAN"); //$NON-NLS-1$ + supportedFunctions.add("ATAN2"); //$NON-NLS-1$ + supportedFunctions.add("COS"); //$NON-NLS-1$ + supportedFunctions.add("COT"); //$NON-NLS-1$ + supportedFunctions.add("DEGREES"); //$NON-NLS-1$ + supportedFunctions.add("EXP"); //$NON-NLS-1$ + supportedFunctions.add("FLOOR"); //$NON-NLS-1$ + supportedFunctions.add("LOG"); //$NON-NLS-1$ + supportedFunctions.add("LOG10"); //$NON-NLS-1$ + supportedFunctions.add("MOD"); //$NON-NLS-1$ + supportedFunctions.add("PI"); //$NON-NLS-1$ + supportedFunctions.add("POWER"); //$NON-NLS-1$ + supportedFunctions.add("RADIANS"); //$NON-NLS-1$ + supportedFunctions.add("SIGN"); //$NON-NLS-1$ + supportedFunctions.add("SIN"); //$NON-NLS-1$ + supportedFunctions.add("SQRT"); //$NON-NLS-1$ + supportedFunctions.add("TAN"); //$NON-NLS-1$ + supportedFunctions.add("ASCII"); //$NON-NLS-1$ + supportedFunctions.add("CHAR"); //$NON-NLS-1$ + supportedFunctions.add("CHR"); //$NON-NLS-1$ + supportedFunctions.add("CONCAT"); //$NON-NLS-1$ + supportedFunctions.add("||"); //$NON-NLS-1$ + supportedFunctions.add("LCASE"); //$NON-NLS-1$ + supportedFunctions.add("LEFT"); //$NON-NLS-1$ + supportedFunctions.add("LENGTH"); //$NON-NLS-1$ + supportedFunctions.add("LOWER"); //$NON-NLS-1$ + supportedFunctions.add("LTRIM"); //$NON-NLS-1$ + supportedFunctions.add("REPEAT"); //$NON-NLS-1$ + //supportedFunctions.add("RAND"); //$NON-NLS-1$ + supportedFunctions.add("RIGHT"); //$NON-NLS-1$ + supportedFunctions.add("RTRIM"); //$NON-NLS-1$ + supportedFunctions.add("SPACE"); //$NON-NLS-1$ + supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ + supportedFunctions.add("UCASE"); //$NON-NLS-1$ + supportedFunctions.add("UPPER"); //$NON-NLS-1$ + //supportedFunctons.add("CURDATE"); //$NON-NLS-1$ + //supportedFunctons.add("CURTIME"); //$NON-NLS-1$ + supportedFunctions.add("DAYNAME"); //$NON-NLS-1$ + supportedFunctions.add("DAYOFMONTH"); //$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$ + 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$ + supportedFunctions.add("IFNULL"); //$NON-NLS-1$ + supportedFunctions.add("NVL"); //$NON-NLS-1$ + //supportedFunctions.add("FORMATTIMESTAMP"); //$NON-NLS-1$ + + return supportedFunctions; + } + + @Override + public boolean supportsInlineViews() { + return true; + } + + @Override + public boolean supportsFunctionsInGroupBy() { + return true; + } + + @Override + public int getMaxFromGroups() { + return 50; + } + + @Override + public boolean supportsAggregatesEnhancedNumeric() { + return getDatabaseVersion().compareTo(FIFTEEN_0_2) >= 0; + } + + public boolean nullPlusNonNullIsNull() { + return false; + } + + public boolean booleanNullable() { + return false; + } + + @Override + public Object retrieveValue(ResultSet results, int columnIndex, + Class<?> expectedType) throws SQLException { + if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { + expectedType = TypeFacility.RUNTIME_TYPES.SHORT; + } + return super.retrieveValue(results, columnIndex, expectedType); + } + + @Override + public Object retrieveValue(CallableStatement results, int parameterIndex, + Class<?> expectedType) throws SQLException { + if (expectedType == TypeFacility.RUNTIME_TYPES.BYTE) { + expectedType = TypeFacility.RUNTIME_TYPES.SHORT; + } + return super.retrieveValue(results, parameterIndex, expectedType); + } + + @Override + public void bindValue(PreparedStatement stmt, Object param, + Class<?> paramType, int i) throws SQLException { + if (paramType == TypeFacility.RUNTIME_TYPES.BYTE) { + paramType = TypeFacility.RUNTIME_TYPES.SHORT; + param = ((Byte)param).shortValue(); + } + super.bindValue(stmt, param, paramType, i); + } + + @Override + public String translateLiteralTimestamp(Timestamp timestampValue) { + return "CAST('" + formatDateValue(timestampValue) +"' AS DATETIME)"; //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Override + public String translateLiteralDate(Date dateValue) { + return "CAST('" + formatDateValue(dateValue) +"' AS DATE)"; //$NON-NLS-1$ //$NON-NLS-2$ + } + + protected boolean supportsCrossJoin() { + return false; + } + + @Override + public boolean supportsRowLimit() { + return getDatabaseVersion().compareTo(TWELVE_5_3) >= 0; + } + }