Author: rareddy Date: 2011-06-13 12:01:18 -0400 (Mon, 13 Jun 2011) New Revision: 3246 Added: branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/ branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java Modified: branches/7.1.1.CP2/connectors/sandbox/translator-teradata/ branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java Log: SOA-3103 Property changes on: branches/7.1.1.CP2/connectors/sandbox/translator-teradata ___________________________________________________________________ Added: svn:ignore + .settings .project .classpath target Modified: branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java =================================================================== --- branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-06-13 14:52:40 UTC (rev 3245) +++ branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-06-13 16:01:18 UTC (rev 3246) @@ -22,60 +22,449 @@ 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.language.ColumnReference; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.language.Literal; +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.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.SQLConversionVisitor; /** - * 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.TIMESTAMP, FunctionModifier.TIME, new CastModifier("TIME")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new CastModifier("DATE")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.TIME, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); //$NON-NLS-1$ + + convert.addConvert(FunctionModifier.STRING, FunctionModifier.INTEGER, new CastModifier("integer")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.BIGDECIMAL, new CastModifier("decimal(37,5)"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.BIGINTEGER, new CastModifier("numeric(18,0)"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.FLOAT, new CastModifier("float"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.BOOLEAN, new CastModifier("byteint"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.LONG, new CastModifier("numeric(18,0)"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.SHORT, new CastModifier("smallint"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.DOUBLE, new CastModifier("double precision"));//$NON-NLS-1$ + convert.addConvert(FunctionModifier.STRING, FunctionModifier.BYTE, new CastModifier("byteint")); //$NON-NLS-1$ + + convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return Arrays.asList("cast(cast(", function.getParameters().get(0), " AS FORMAT 'Y4-MM-DDBHH:MI:SSDS(6)') AS VARCHAR(26))"); //$NON-NLS-1$ //$NON-NLS-2$ + } + }); + convert.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return Arrays.asList("cast(cast(", function.getParameters().get(0), " AS FORMAT 'HH:MI:SS') AS VARCHAR(9))"); //$NON-NLS-1$ //$NON-NLS-2$ + } + }); + convert.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return Arrays.asList("cast(cast(", function.getParameters().get(0), " AS FORMAT 'YYYY-MM-DD') AS VARCHAR(11))"); //$NON-NLS-1$ //$NON-NLS-2$ + } + }); + + convert.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$ + convert.addNumericBooleanConversions(); + + registerFunctionModifier(SourceSystemFunctions.CONVERT, convert); + registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new SubstrModifier(this.convert)); + registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LCASE, new StringOnlyModifier("LOWER", this.convert)); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.UCASE, new StringOnlyModifier("UPPER", this.convert)); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LENGTH, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + ArrayList target = new ArrayList(); + target.add("character_length("); //$NON-NLS-1$ + target.addAll(expressionToString(function.getParameters().get(0), convert)); + target.add(")"); //$NON-NLS-1$ + return target; + } + }); + registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); //$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 LocateModifier(this.convert)); + registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert)); + registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert)); + registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + function.setName(SourceSystemFunctions.TAN); + return Arrays.asList(getLanguageFactory().createFunction(SourceSystemFunctions.DIVIDE_OP, new Expression[] {new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), function}, TypeFacility.RUNTIME_TYPES.DOUBLE)); + } + }); + registerFunctionModifier(SourceSystemFunctions.LTRIM, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + ArrayList target = new ArrayList(); + target.add("TRIM(LEADING FROM ");//$NON-NLS-1$ + target.addAll(expressionToString(function.getParameters().get(0), convert)); + target.add(")"); //$NON-NLS-1$ + return target; + } + }); + registerFunctionModifier(SourceSystemFunctions.RTRIM, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + ArrayList target = new ArrayList(); + target.add("TRIM(TRAILING FROM ");//$NON-NLS-1$ + target.addAll(expressionToString(function.getParameters().get(0), convert)); + target.add(")"); //$NON-NLS-1$ + return target; + } + }); + registerFunctionModifier(SourceSystemFunctions.MOD, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + return Arrays.asList(function.getParameters().get(0), " MOD ", function.getParameters().get(1)); //$NON-NLS-1$ + } + }); + + /* Push down support only available from 7.4 + addPushDownFunction(TERADATA, "COSH", FLOAT, FLOAT); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "TANH", FLOAT, FLOAT); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "ACOSH", FLOAT, FLOAT); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "ASINH", FLOAT, FLOAT); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "ATANH", FLOAT, FLOAT); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "CHAR2HEXINT", STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "INDEX", INTEGER, STRING, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "BYTES", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "OCTET_LENGTH", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "HASHAMP", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "HASHBAKAMP", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "HASHBUCKET", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "HASHROW", INTEGER, STRING); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "NULLIFZERO", BIG_DECIMAL, BIG_DECIMAL); //$NON-NLS-1$ + addPushDownFunction(TERADATA, "ZEROIFNULL", BIG_DECIMAL, BIG_DECIMAL); //$NON-NLS-1$ + */ + } + + @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("||"); //$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" + supportedFunctions.add(SourceSystemFunctions.ABS); + supportedFunctions.add(SourceSystemFunctions.ACOS); + supportedFunctions.add(SourceSystemFunctions.ASIN); + supportedFunctions.add(SourceSystemFunctions.ATAN); + supportedFunctions.add(SourceSystemFunctions.ATAN2); + supportedFunctions.add(SourceSystemFunctions.COALESCE); + supportedFunctions.add(SourceSystemFunctions.COS); + supportedFunctions.add(SourceSystemFunctions.COT); + supportedFunctions.add(SourceSystemFunctions.CONVERT); + supportedFunctions.add(SourceSystemFunctions.CURDATE); + supportedFunctions.add(SourceSystemFunctions.CURTIME); + supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH); + supportedFunctions.add(SourceSystemFunctions.EXP); + supportedFunctions.add(SourceSystemFunctions.HOUR); + supportedFunctions.add(SourceSystemFunctions.LEFT); + supportedFunctions.add(SourceSystemFunctions.LOCATE); + supportedFunctions.add(SourceSystemFunctions.LOG); + supportedFunctions.add(SourceSystemFunctions.LCASE); + supportedFunctions.add(SourceSystemFunctions.LTRIM); + supportedFunctions.add(SourceSystemFunctions.LENGTH); + supportedFunctions.add(SourceSystemFunctions.MINUTE); + supportedFunctions.add(SourceSystemFunctions.MOD); + supportedFunctions.add(SourceSystemFunctions.MONTH); + supportedFunctions.add(SourceSystemFunctions.NULLIF); + supportedFunctions.add(SourceSystemFunctions.RAND); + supportedFunctions.add(SourceSystemFunctions.RIGHT); + supportedFunctions.add(SourceSystemFunctions.RTRIM); + supportedFunctions.add(SourceSystemFunctions.SECOND); + supportedFunctions.add(SourceSystemFunctions.SIN); + supportedFunctions.add(SourceSystemFunctions.SQRT); + supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.TAN); + supportedFunctions.add(SourceSystemFunctions.UCASE); + supportedFunctions.add(SourceSystemFunctions.YEAR); return supportedFunctions; } + @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; + } + + /* only in 7.4 + @Override + public boolean supportsCommonTableExpressions() { + return false; + } + */ + + @Override + public NullOrder getDefaultNullOrder() { + return NullOrder.FIRST; + } + + @Override + public boolean supportsSetQueryOrderBy() { + return false; + } + + public static class LocateModifier extends FunctionModifier { + ConvertModifier convertModifier; + + public LocateModifier(ConvertModifier convertModifier) { + this.convertModifier = convertModifier; + } + + @Override + public List<?> translate(Function function) { + ArrayList target = new ArrayList(); + Expression expr1 = function.getParameters().get(0); + Expression expr2 = function.getParameters().get(1); + if (function.getParameters().size() > 2) { + Expression expr3 = function.getParameters().get(2); + target.add("position("); //$NON-NLS-1$ + target.addAll(expressionToString(expr1, this.convertModifier)); + target.add( " in "); //$NON-NLS-1$ + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(expr2, this.convertModifier)); + target.add(","); //$NON-NLS-1$ + target.add(expr3); + target.add("))"); //$NON-NLS-1$ + } + else { + target.add("position("); //$NON-NLS-1$ + target.addAll(expressionToString(expr1, this.convertModifier)); + target.add( " in "); //$NON-NLS-1$ + target.addAll(expressionToString(expr2, this.convertModifier)); + target.add(")"); //$NON-NLS-1$ + } + return target; + } + } + + private static List<?> expressionToString(Expression expr, ConvertModifier modifier) { + Class tgtType = expr.getType(); + if (tgtType.equals(String.class) && ((expr instanceof Literal) || expr instanceof ColumnReference)) { + return Arrays.asList(expr); + } + else if (tgtType.equals(String.class) && (expr instanceof Function)) { + + Function func = (Function)expr; + while(true) { + Expression arg1 = func.getParameters().get(0); + if ((arg1 instanceof Function) && ((Function)arg1).getName().equals("convert")) { //$NON-NLS-1$ + func = (Function)arg1; + } + else { + break; + } + } + Expression arg1 = func.getParameters().get(0); + if (arg1 instanceof ColumnReference) { + ColumnReference ref = (ColumnReference)func.getParameters().get(0); + if(Number.class.isAssignableFrom(ref.getType())) { + ArrayList target = new ArrayList(); + target.add("cast("); //$NON-NLS-1$ + target.add(func.getParameters().get(0)); + target.add(" AS varchar(100))"); //$NON-NLS-1$ + return target; + } + else if (String.class.isAssignableFrom(ref.getType())) { + return Arrays.asList(ref); + } + } + return modifier.translate(func); + } + return Arrays.asList("cast(" , expr, " AS varchar(100))"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + 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 CastModifier extends FunctionModifier { + private String target; + public CastModifier(String target) { + this.target = target; + } + @Override + public List<?> translate(Function function) { + return Arrays.asList("cast(", function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + } + + public static class StringOnlyModifier extends FunctionModifier { + String funcName; + ConvertModifier convertModifier; + public StringOnlyModifier(String name, ConvertModifier converModifier) { + this.funcName = name; + this.convertModifier = converModifier; + } + @Override + public List<?> translate(Function function) { + Expression expr = function.getParameters().get(0); + ArrayList target = new ArrayList(); + target.add(this.funcName); + target.add("("); //$NON-NLS-1$ + target.addAll(expressionToString(expr, this.convertModifier)); + target.add(")"); //$NON-NLS-1$ + return target; + } + } + + public static class SubstrModifier extends FunctionModifier { + ConvertModifier convertModifier; + public SubstrModifier(ConvertModifier converModifier) { + this.convertModifier = converModifier; + } + @Override + public List<?> translate(Function function) { + Expression expr = function.getParameters().get(0); + ArrayList target = new ArrayList(); + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(expr, this.convertModifier)); + target.add(","); //$NON-NLS-1$ + target.add(function.getParameters().get(1)); + if (function.getParameters().size() > 2 ) { + target.add(","); //$NON-NLS-1$ + target.add(function.getParameters().get(2)); + } + target.add(")"); //$NON-NLS-1$ + return target; + } + } + + public static class LeftOrRightFunctionModifier extends FunctionModifier { + private LanguageFactory langFactory; + ConvertModifier convertModifier; + + public LeftOrRightFunctionModifier(LanguageFactory langFactory, ConvertModifier converModifier) { + this.langFactory = langFactory; + this.convertModifier = converModifier; + } + + @Override + public List<?> translate(Function function) { + List<Expression> args = function.getParameters(); + ArrayList target = new ArrayList(); + if (function.getName().equalsIgnoreCase("left")) { //$NON-NLS-1$ + //substr(string, 1, length) + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + target.add(","); //$NON-NLS-1$ + target.add(langFactory.createLiteral(Integer.valueOf(1), TypeFacility.RUNTIME_TYPES.INTEGER)); + target.add(","); //$NON-NLS-1$ + target.add(args.get(1)); + target.add(")"); //$NON-NLS-1$ + } else if (function.getName().equalsIgnoreCase("right")) { //$NON-NLS-1$ + //substr(case_size, character_length(case_size) -4) + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + + target.add(",(character_length("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + target.add(")-"); //$NON-NLS-1$ + target.add(args.get(1)); + target.add("+1))"); //$NON-NLS-1$ // offset for 1 based index + } + return target; + } + } } Added: branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java =================================================================== --- branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java (rev 0) +++ branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java 2011-06-13 16:01:18 UTC (rev 3246) @@ -0,0 +1,72 @@ +/* + * 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) { + Comparison.Operator opCode = obj.isNegated()?Comparison.Operator.NE:Comparison.Operator.EQ; + if (exprs.size() > 1) { + Condition left = LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(0)); + for (int i = 1; i < exprs.size(); i++) { + AndOr replace = LanguageFactory.INSTANCE.createAndOr(obj.isNegated()?Operator.AND:Operator.OR, left, LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(i))); + left = replace; + } + super.visit((AndOr)left); + } + else { + super.visit(LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(0))); + } + } + else { + super.visit(obj); + } + } +} Property changes on: branches/7.1.1.CP2/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java =================================================================== --- branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java (rev 0) +++ branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-06-13 16:01:18 UTC (rev 3246) @@ -0,0 +1,207 @@ +/* + * 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.sql.Timestamp; +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.query.unittest.TimestampUtil; +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 testTimestampToTime() throws Exception { + helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(111, 4, 5, 9, 16, 34, 220000000), Timestamp.class), "time", "cast(cast('2011-05-05 09:16:34.22' AS TIMESTAMP(6)) AS TIME)"); + } + + @Test public void testIntegerToString() throws Exception { + String input = "SELECT lcase(bigdecimalvalue) FROM BQT1.SMALLA"; + String output = "SELECT LOWER(cast(SmallA.BigDecimalValue AS varchar(100))) FROM SmallA"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testSubString() throws Exception { + String input = "SELECT intkey FROM BQT1.SmallA WHERE SUBSTRING(BQT1.SmallA.IntKey, 1) = '1' ORDER BY intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1) = '1' ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testSubString2() throws Exception { + String input = "SELECT intkey FROM BQT1.SmallA WHERE SUBSTRING(BQT1.SmallA.IntKey, 1, 2) = '1' ORDER BY intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1,2) = '1' ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testDateToString() throws Exception { + String input = "SELECT intkey, UPPER(timevalue) AS UPPER FROM BQT1.SmallA ORDER BY intkey"; + String output = "SELECT SmallA.IntKey, UPPER(cast(cast(SmallA.TimeValue AS FORMAT 'HH:MI:SS') AS VARCHAR(9))) AS UPPER FROM SmallA ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate() throws Exception { + String input = "SELECT INTKEY, BIGDECIMALVALUE FROM BQT1.SmallA WHERE LOCATE('-', BIGDECIMALVALUE) = 1 ORDER BY intkey"; + String output = "SELECT SmallA.IntKey, SmallA.BigDecimalValue FROM SmallA WHERE position('-' in cast(SmallA.BigDecimalValue AS varchar(100))) = 1 ORDER BY SmallA.IntKey"; + 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", "cast(1 AS varchar(4000))"); + } + + @Test public void testByte2ToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)-1), Byte.class), "string", "cast(-1 AS varchar(4000))"); + } + + @Test public void testDoubleToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.0), Double.class), "string", "cast(1.0 AS varchar(4000))"); + } + + @Test public void testStringToDouble() throws Exception { + helpTest(LANG_FACTORY.createLiteral("1.0", String.class), "double", "cast('1.0' AS double precision)"); + } + + @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 testNegatedInDecomposeNonLiterals() 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, true); + + assertEquals("'1' <> func() AND '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)); + } + + /* only 7.4 + @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, TRANSLATOR); + } + @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, TRANSLATOR); + } + */ + + @Test public void testRightFunction() throws Exception { + String input = "SELECT INTKEY, FLOATNUM FROM BQT1.SmallA WHERE right(FLOATNUM, 2) <> 0 ORDER BY INTKEY"; + String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE substr(cast(SmallA.FloatNum AS varchar(100)),(character_length(cast(SmallA.FloatNum AS varchar(100)))-2+1)) <> '0' ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); + } + + @Test public void testLocateFunction() throws Exception { + String input = "SELECT INTKEY, STRINGKEY, SHORTVALUE FROM BQT1.SmallA WHERE (LOCATE(0, STRINGKEY) = 2) OR (LOCATE(2, SHORTVALUE, 4) = 6) ORDER BY intkey"; + String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.ShortValue FROM SmallA WHERE position('0' in SmallA.StringKey) = 2 OR position('2' in substr(cast(SmallA.ShortValue AS varchar(100)),4)) = 6 ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); + } +} Property changes on: branches/7.1.1.CP2/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java ___________________________________________________________________ Added: svn:mime-type + text/plain