[teiid-commits] teiid SVN: r3114 - in trunk: connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc and 3 other directories.

Author: rareddy Date: 2011-04-25 11:26:44 -0400 (Mon, 25 Apr 2011) New Revision: 3114 Added: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TranslationHelper.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-1495 - Reverified teradata translator with correct capabilities Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-04-25 15:09:21 UTC (rev 3113) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -22,60 +22,281 @@ package org.teiid.translator.jdbc.teradata; +import java.sql.Date; +import java.sql.Time; +import java.sql.Timestamp; import java.util.ArrayList; +import java.util.Arrays; import java.util.List; +import org.teiid.core.types.DataTypeManager; +import org.teiid.language.Function; +import org.teiid.metadata.FunctionMethod; +import org.teiid.metadata.FunctionParameter; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.AliasModifier; +import org.teiid.translator.jdbc.ConvertModifier; +import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.SQLConversionVisitor; +import org.teiid.translator.jdbc.oracle.LeftOrRightFunctionModifier; /** - * for Teradata database Release V2R5.1 + * Teradata database Release 12 */ @Translator(name="teradata", description="A translator for Teradata Database") public class TeradataExecutionFactory extends JDBCExecutionFactory { + public static String TERADATA = "teradata"; //$NON-NLS-1$ + protected ConvertModifier convert = new ConvertModifier(); + public TeradataExecutionFactory() { setSupportsOuterJoins(false); } + @Override + public void start() throws TranslatorException { + super.start(); + convert.addTypeMapping("byteint", FunctionModifier.BYTE, FunctionModifier.SHORT, FunctionModifier.BOOLEAN); //$NON-NLS-1$ + convert.addTypeMapping("double precision", FunctionModifier.DOUBLE); //$NON-NLS-1$ + convert.addTypeMapping("numeric(18,0)", FunctionModifier.BIGINTEGER); //$NON-NLS-1$ + convert.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.INTEGER, FunctionModifier.STRING, new NumericToStringModifier(12)); + convert.addConvert(FunctionModifier.BIGDECIMAL, FunctionModifier.STRING, new NumericToStringModifier(38)); + convert.addConvert(FunctionModifier.BIGINTEGER, FunctionModifier.STRING, new NumericToStringModifier(38)); + convert.addConvert(FunctionModifier.FLOAT, FunctionModifier.STRING, new NumericToStringModifier(25)); + convert.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.STRING, new NumericToStringModifier(1)); + convert.addConvert(FunctionModifier.LONG, FunctionModifier.STRING, new NumericToStringModifier(25)); + convert.addConvert(FunctionModifier.SHORT, FunctionModifier.STRING, new NumericToStringModifier(12)); + convert.addConvert(FunctionModifier.DOUBLE, FunctionModifier.STRING, new NumericToStringModifier(25)); + convert.addConvert(FunctionModifier.BYTE, FunctionModifier.STRING, new NumericToStringModifier(4)); + convert.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$ + convert.addNumericBooleanConversions(); + + registerFunctionModifier(SourceSystemFunctions.CONVERT, convert); + + registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.NULLIF, new AliasModifier("NULLIFZERO")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("ZEROIFNULL")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("LOWER")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("UPPER")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LENGTH, new AliasModifier("CHARACTER_LENGTH")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$ + + registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractModifier("YEAR")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractModifier("MONTH")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractModifier("HOUR")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractModifier("MINUTE")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractModifier("SECOND")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOCATE, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return Arrays.asList("position(",function.getParameters().get(0)," in ",function.getParameters().get(1) ,")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + }); + registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); + } + + @Override + public SQLConversionVisitor getSQLConversionVisitor() { + return new TeradataSQLConversionVisitor(this); + } + + @Override public List getSupportedFunctions() { - List supportedFunctions = new ArrayList(); + 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("EXP"); //$NON-NLS-1$ -// supportedFunctions.add("LOG"); //$NON-NLS-1$ // "LN" -// supportedFunctions.add("LOG10"); //$NON-NLS-1$ // "LOG" - supportedFunctions.add("MOD"); //$NON-NLS-1$ - supportedFunctions.add("SIN"); //$NON-NLS-1$ - supportedFunctions.add("SQRT"); //$NON-NLS-1$ - supportedFunctions.add("TAN"); //$NON-NLS-1$ + + supportedFunctions.add(SourceSystemFunctions.ABS); + supportedFunctions.add(SourceSystemFunctions.ACOS); + supportedFunctions.add(SourceSystemFunctions.ASIN); + supportedFunctions.add(SourceSystemFunctions.ATAN); + supportedFunctions.add(SourceSystemFunctions.ATAN2); + supportedFunctions.add(SourceSystemFunctions.COS); + supportedFunctions.add(SourceSystemFunctions.CONVERT); + supportedFunctions.add(SourceSystemFunctions.EXP); + supportedFunctions.add(SourceSystemFunctions.LOG); + supportedFunctions.add(SourceSystemFunctions.SIN); + supportedFunctions.add(SourceSystemFunctions.SQRT); + supportedFunctions.add(SourceSystemFunctions.TAN); supportedFunctions.add("||"); //$NON-NLS-1$ -// supportedFunctions.add("CONCAT"); //$NON-NLS-1$ // "||" -// supportedFunctions.add("LCASE"); //$NON-NLS-1$ // "LOWER" -// supportedFunctions.add("LOCATE"); //$NON-NLS-1$ //"POSITION", "INDEX" ? - supportedFunctions.add("LOWER"); //$NON-NLS-1$ - supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$ -// supportedFunctions.add("UCASE"); //$NON-NLS-1$ // "UPPER" - supportedFunctions.add("UPPER"); //$NON-NLS-1$ - supportedFunctions.add("HOUR"); //$NON-NLS-1$ - supportedFunctions.add("MONTH"); //$NON-NLS-1$ - supportedFunctions.add("YEAR"); //$NON-NLS-1$ - supportedFunctions.add("DAY"); //$NON-NLS-1$ - supportedFunctions.add("MINUTE"); //$NON-NLS-1$ - supportedFunctions.add("SECOND"); //$NON-NLS-1$ - supportedFunctions.add("CAST"); //$NON-NLS-1$ - //supportedFunctions.add("CONVERT"); //$NON-NLS-1$ "CAST" - return supportedFunctions; } + + @Override + public List<FunctionMethod> getPushDownFunctions(){ + + List<FunctionMethod> pushdownFunctions = new ArrayList<FunctionMethod>(); + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "COSH", "COSH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic Cos")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "SINH", "SINH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic Sin")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "TANH", "TANH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic Tanh")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "ACOSH", "ACOSH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic ArcCos")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "ASINH", "ASINH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic ArcSin")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "ATANH", "ATANH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("float", DataTypeManager.DefaultDataTypes.FLOAT, "Hyperbolic ArcTan")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.FLOAT, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "CHAR2HEXINT", "CHAR2HEXINT", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("string", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "INDEX", "INDEX", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("string1", DataTypeManager.DefaultDataTypes.STRING, ""), //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "BYTES", "BYTES", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "OCTET_LENGTH", "OCTET_LENGTH", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "HASHAMP", "HASHAMP", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "HASHBAKAMP", "HASHBAKAMP", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "HASHBUCKET", "HASHBUCKET", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + pushdownFunctions.add(new FunctionMethod(TERADATA + '.' + "HASHROW", "HASHROW", TERADATA, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("String2", DataTypeManager.DefaultDataTypes.STRING, "")}, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.INTEGER, "") ) ); //$NON-NLS-1$ //$NON-NLS-2$ + + return pushdownFunctions; + } + + + @Override + public String translateLiteralDate(Date dateValue) { + return "cast('" + formatDateValue(dateValue) + "' AS DATE FORMAT 'yyyy-mm-dd')"; //$NON-NLS-1$//$NON-NLS-2$ + } + + @Override + public String translateLiteralTime(Time timeValue) { + return "cast('" + formatDateValue(timeValue) + "' AS TIME(0) FORMAT 'hh:mi:ss')"; //$NON-NLS-1$//$NON-NLS-2$ + } + + @Override + public String translateLiteralTimestamp(Timestamp timestampValue) { + return "cast('" + formatDateValue(timestampValue) + "' AS TIMESTAMP(6))"; //$NON-NLS-1$//$NON-NLS-2$ + } + + // Teradata also supports MINUS & ALL set operators + // more aggregates available + + @Override + public boolean supportsScalarSubqueries() { + return false; + } + + @Override + public boolean supportsUnions() { + return true; + } + + @Override + public boolean supportsIntersect() { + return true; + } + + @Override + public boolean supportsExcept() { + return true; + } + + @Override + public boolean supportsInlineViews() { + return true; + } + + @Override + public boolean supportsAggregatesEnhancedNumeric() { + return true; + } + + @Override + public boolean supportsCommonTableExpressions() { + return false; + } + + @Override + public NullOrder getDefaultNullOrder() { + return NullOrder.FIRST; + } + + @Override + public boolean supportsSetQueryOrderBy() { + return false; + } + + public static class ExtractModifier extends FunctionModifier { + private String type; + public ExtractModifier(String type) { + this.type = type; + } + @Override + public List<?> translate(Function function) { + return Arrays.asList("extract(",this.type," from ",function.getParameters().get(0) ,")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + } + + public static class NumericToStringModifier extends FunctionModifier { + private int charSize; + public NumericToStringModifier(int size) { + this.charSize = size; + } + @Override + public List<?> translate(Function function) { + return Arrays.asList("TRIM(BOTH FROM ",function.getParameters().get(0), " (FORMAT 'Z')(CHAR("+this.charSize+")))"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + } } Added: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java (rev 0) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -0,0 +1,71 @@ +/* + * 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.teradata; + +import java.util.List; + +import org.teiid.language.AndOr; +import org.teiid.language.Comparison; +import org.teiid.language.Condition; +import org.teiid.language.Expression; +import org.teiid.language.In; +import org.teiid.language.LanguageFactory; +import org.teiid.language.AndOr.Operator; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +public class TeradataSQLConversionVisitor extends SQLConversionVisitor { + + public TeradataSQLConversionVisitor(TeradataExecutionFactory ef) { + super(ef); + } + + @Override + public void visit(In obj) { + List<Expression> exprs = obj.getRightExpressions(); + + Class expectedType = obj.getLeftExpression().getType(); + + boolean decompose = false; + for (Expression expr:exprs) { + if (!(expr.getType().equals(expectedType)) || (!(expr.getType().isAssignableFrom(Number.class)) && !expr.getType().isAssignableFrom(String.class))) { + decompose = true; + } + } + + if (decompose) { + if (exprs.size() > 1) { + Condition left = LanguageFactory.INSTANCE.createCompareCriteria(Comparison.Operator.EQ, obj.getLeftExpression(), exprs.get(0)); + for (int i = 1; i < exprs.size(); i++) { + AndOr replace = LanguageFactory.INSTANCE.createAndOr(Operator.OR, left, LanguageFactory.INSTANCE.createCompareCriteria(Comparison.Operator.EQ, obj.getLeftExpression(), exprs.get(i))); + left = replace; + } + super.visit((AndOr)left); + } + else { + super.visit(LanguageFactory.INSTANCE.createCompareCriteria(Comparison.Operator.EQ, obj.getLeftExpression(), exprs.get(0))); + } + } + else { + super.visit(obj); + } + } +} Property changes on: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TranslationHelper.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TranslationHelper.java 2011-04-25 15:09:21 UTC (rev 3113) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TranslationHelper.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -25,7 +25,9 @@ import static org.junit.Assert.assertEquals; import java.io.IOException; +import java.util.ArrayList; import java.util.Collection; +import java.util.List; import javax.xml.bind.JAXBException; @@ -48,7 +50,7 @@ public static final String BQT_VDB = "/bqt.vdb"; //$NON-NLS-1$ public static Command helpTranslate(String vdbFileName, String sql) { - return helpTranslate(vdbFileName, null, sql); + return helpTranslate(vdbFileName, null, null, sql); } public static TranslationUtility getTranslationUtility(String vdbFileName, String udf) { @@ -78,8 +80,24 @@ } } - public static Command helpTranslate(String vdbFileName, String udf, String sql) { - return getTranslationUtility(vdbFileName, udf).parseCommand(sql); + public static Command helpTranslate(String vdbFileName, String udf, List<FunctionMethod> pushdowns, String sql) { + TranslationUtility util = getTranslationUtility(vdbFileName, null); + + Collection <FunctionMethod> methods = new ArrayList<FunctionMethod>(); + if (udf != null) { + try { + methods.addAll(FunctionMetadataReader.loadFunctionMethods(TranslationHelper.class.getResource(udf).openStream())); + } catch (JAXBException e) { + throw new TeiidRuntimeException("failed to load UDF"); //$NON-NLS-1$ + } catch (IOException e) { + throw new TeiidRuntimeException("failed to load UDF"); //$NON-NLS-1$ + } + } + if (pushdowns != null) { + methods.addAll(pushdowns); + } + util.setUDF(methods); + return util.parseCommand(sql); } public static void helpTestVisitor(String vdb, String input, String expectedOutput, JDBCExecutionFactory translator) throws TranslatorException { @@ -88,7 +106,7 @@ public static void helpTestVisitor(String vdb, String udf, String input, String expectedOutput, JDBCExecutionFactory translator) throws TranslatorException { // Convert from sql to objects - Command obj = helpTranslate(vdb, udf, input); + Command obj = helpTranslate(vdb, udf, translator.getPushDownFunctions(), input); helpTestVisitor(expectedOutput, translator, obj); } Added: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -0,0 +1,138 @@ +/* + * 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.teradata; + +import static org.junit.Assert.assertEquals; + +import java.sql.Date; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.In; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; +import org.teiid.translator.jdbc.TranslationHelper; + +@SuppressWarnings("nls") +public class TestTeradataTranslator { + + private static TeradataExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass + public static void setUp() throws TranslatorException { + TRANSLATOR = new TeradataExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + } + + public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", + Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); + + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, + expectedExpression, helpGetString(func)); + } + + public String helpGetString(Expression expr) throws Exception { + SQLConversionVisitor sqlVisitor = TRANSLATOR.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + return sqlVisitor.toString(); + } + + @Test public void testSubstring1() throws Exception { + String input = "SELECT dayofmonth(datevalue) FROM BQT1.SMALLA"; + String output = "SELECT extract(DAY from SmallA.DateValue) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testByteToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "TRIM(BOTH FROM 1 (FORMAT 'Z')(CHAR(4)))"); + } + + @Test public void testDoubleToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.0), Double.class), "string", "TRIM(BOTH FROM 1.0 (FORMAT 'Z')(CHAR(25)))"); + } + + @Test public void testInDecompose() throws Exception { + Expression left = LANG_FACTORY.createLiteral("1", String.class); + List<Expression> right = new ArrayList<Expression>(); + right.add(LANG_FACTORY.createLiteral("2", String.class)); + right.add(LANG_FACTORY.createLiteral("3", String.class)); + + In expr = LANG_FACTORY.createIn(left,right, false); + + assertEquals("'1' IN ('2', '3')", helpGetString(expr)); + } + + @Test public void testSingleInDecompose() throws Exception { + Expression left = LANG_FACTORY.createLiteral("1", String.class); + List<Expression> right = new ArrayList<Expression>(); + right.add(LANG_FACTORY.createLiteral("2", String.class)); + + In expr = LANG_FACTORY.createIn(left,right, false); + + assertEquals("'1' IN ('2')", helpGetString(expr)); + } + + @Test public void testInDecomposeNonLiterals() throws Exception { + Expression left = LANG_FACTORY.createLiteral("1", String.class); + List<Expression> right = new ArrayList<Expression>(); + right.add(LANG_FACTORY.createFunction("func", new Expression[] {}, Date.class)); + right.add(LANG_FACTORY.createLiteral("3", String.class)); + + In expr = LANG_FACTORY.createIn(left,right, false); + + assertEquals("'1' = func() OR '1' = '3'", helpGetString(expr)); + } + + @Test public void testsingleInDecomposeNonLiterals() throws Exception { + Expression left = LANG_FACTORY.createLiteral("1", String.class); + List<Expression> right = new ArrayList<Expression>(); + right.add(LANG_FACTORY.createFunction("func", new Expression[] {}, Date.class)); + + In expr = LANG_FACTORY.createIn(left,right, false); + + assertEquals("'1' = func()", helpGetString(expr)); + } + + @Test public void testNullComapreNull() throws Exception { + String input = "SELECT INTKEY, STRINGKEY, DOUBLENUM FROM bqt1.smalla WHERE NULL <> NULL"; + String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.DoubleNum FROM SmallA WHERE 1 = 0"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, new TeradataExecutionFactory()); + } + + @Test public void testPushDownFunction() throws Exception { + String input = "SELECT teradata.HASHBAKAMP(STRINGKEY) DOUBLENUM FROM bqt1.smalla"; + String out = "SELECT HASHBAKAMP(SmallA.StringKey) AS DOUBLENUM FROM SmallA"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, new TeradataExecutionFactory()); + } +} Property changes on: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-04-25 15:09:21 UTC (rev 3113) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -626,6 +626,8 @@ } if(crit == TRUE_CRITERIA) { query.setCriteria(null); + } else if (crit == UNKNOWN_CRITERIA) { + query.setCriteria(FALSE_CRITERIA); } else { query.setCriteria(crit); } Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-04-25 15:09:21 UTC (rev 3113) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-04-25 15:26:44 UTC (rev 3114) @@ -834,7 +834,7 @@ @Test public void testCompareSubqueryUnknown() { helpTestRewriteCommand("SELECT e1 FROM pm1.g1 WHERE null = SOME (SELECT e1 FROM pm1.g2)", //$NON-NLS-1$ - "SELECT e1 FROM pm1.g1 WHERE null <> null"); //$NON-NLS-1$ + "SELECT e1 FROM pm1.g1 WHERE 1 = 0"); //$NON-NLS-1$ } @Test public void testINClauseSubquery() {