[teiid-commits] teiid SVN: r3631 - in branches/7.1.x/connectors: translator-netezza and 15 other directories.

Author: van.halbert Date: 2011-11-10 12:48:21 -0500 (Thu, 10 Nov 2011) New Revision: 3631 Added: branches/7.1.x/connectors/translator-netezza/ branches/7.1.x/connectors/translator-netezza/pom.xml branches/7.1.x/connectors/translator-netezza/src/ branches/7.1.x/connectors/translator-netezza/src/main/ branches/7.1.x/connectors/translator-netezza/src/main/java/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/netezza/ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java branches/7.1.x/connectors/translator-netezza/src/test/ branches/7.1.x/connectors/translator-netezza/src/test/java/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java Modified: branches/7.1.x/connectors/pom.xml Log: SOA-3573 backporting the Netezza translator from 7.4.x Modified: branches/7.1.x/connectors/pom.xml =================================================================== --- branches/7.1.x/connectors/pom.xml 2011-11-10 17:02:42 UTC (rev 3630) +++ branches/7.1.x/connectors/pom.xml 2011-11-10 17:48:21 UTC (rev 3631) @@ -90,5 +90,6 @@ <module>sandbox</module> <module>translator-ws</module> + <module>translator-netezza</module> </modules> </project> Added: branches/7.1.x/connectors/translator-netezza/pom.xml =================================================================== --- branches/7.1.x/connectors/translator-netezza/pom.xml (rev 0) +++ branches/7.1.x/connectors/translator-netezza/pom.xml 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,71 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> + + <parent> + <artifactId>connectors</artifactId> + <groupId>org.jboss.teiid</groupId> + <version>7.1.1.CP3</version> + </parent> + + <modelVersion>4.0.0</modelVersion> + <artifactId>translator-netezza</artifactId> + <groupId>org.jboss.teiid.connectors</groupId> + <name>Netezza Translator</name> + + <description>This project contains translator for a Netezza source. </description> + <dependencies> + <dependency> + <groupId>org.jboss.teiid.connectors</groupId> + <artifactId>translator-jdbc</artifactId> + <version>${project.version}</version> + </dependency> + + <dependency> + <groupId>org.jboss.teiid.connectors</groupId> + <artifactId>translator-jdbc</artifactId> + <type>test-jar</type> + <scope>test</scope> + <version>${project.version}</version> + </dependency> + + <dependency> + <groupId>org.jboss.teiid</groupId> + <artifactId>teiid-api</artifactId> + <version>${project.version}</version> + </dependency> + <dependency> + <groupId>org.jboss.teiid</groupId> + <artifactId>teiid-common-core</artifactId> + <version>${project.version}</version> + </dependency> + <dependency> + <groupId>javax.resource</groupId> + <artifactId>connector-api</artifactId> + <version>1.5</version> + </dependency> + + </dependencies> + + <build> + <outputDirectory>target/classes</outputDirectory> + <resources> + <resource> + <directory>src/main/resources</directory> + <filtering>true</filtering> + <includes> + <include>**/*.xml</include> + <include>**/*.properties</include> + </includes> + </resource> + <resource> + <directory>src/main/resources</directory> + <filtering>false</filtering> + <excludes> + <exclude>**/*.xml</exclude> + <exclude>**/*.properties</exclude> + </excludes> + </resource> + </resources> + </build> + +</project> Added: branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,465 @@ +package org.teiid.translator.jdbc.netezza; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.Limit; +import org.teiid.translator.ExecutionContext; +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.ExtractFunctionModifier; +import org.teiid.translator.jdbc.FunctionModifier; +import org.teiid.translator.jdbc.JDBCExecutionFactory; +import org.teiid.translator.jdbc.LocateFunctionModifier; + +@SuppressWarnings("nls") +@Translator(name = "netezza", description = "A translator for Netezza Database") +public class NetezzaExecutionFactory extends JDBCExecutionFactory { + + private static final String TIME_FORMAT = "HH24:MI:SS"; + private static final String DATE_FORMAT = "YYYY-MM-DD"; + private static final String DATETIME_FORMAT = DATE_FORMAT + " " + + TIME_FORMAT; + private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".MS"; + + public NetezzaExecutionFactory() { + setSupportsFullOuterJoins(true); + setSupportsOrderBy(true); + setSupportsOuterJoins(true); + setSupportsSelectDistinct(true); + setSupportsInnerJoins(true); + } + + public void start() throws TranslatorException { + super.start(); + + // STRING FUNCTION MODIFIERS + // ////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier( + "chr")); + registerFunctionModifier(SourceSystemFunctions.LCASE, + new AliasModifier("lower")); + registerFunctionModifier(SourceSystemFunctions.UCASE, + new AliasModifier("upper")); + registerFunctionModifier(SourceSystemFunctions.LOCATE, + new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); + registerFunctionModifier(SourceSystemFunctions.CONCAT, + new AliasModifier("||")); + // /NUMERIC FUNCTION MODIFIERS + // ////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.CEILING, + new AliasModifier("ceil")); + registerFunctionModifier(SourceSystemFunctions.POWER, + new AliasModifier("pow")); + registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier( + "LN")); + // /BIT FUNCTION MODIFIERS + // ////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.BITAND, + new AliasModifier("intNand")); + registerFunctionModifier(SourceSystemFunctions.BITNOT, + new AliasModifier("intNnot")); + registerFunctionModifier(SourceSystemFunctions.BITOR, + new AliasModifier("intNor")); + registerFunctionModifier(SourceSystemFunctions.BITXOR, + new AliasModifier("intNxor")); + // DATE FUNCTION MODIFIERS + // //////////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.YEAR, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, + new ExtractModifier("DOY")); + registerFunctionModifier(SourceSystemFunctions.QUARTER, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTH, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, + new ExtractModifier("DAY")); + registerFunctionModifier(SourceSystemFunctions.WEEK, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, + new ExtractModifier("DOW")); + registerFunctionModifier(SourceSystemFunctions.HOUR, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MINUTE, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.SECOND, + new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.CURDATE, + new AliasModifier("CURRENT_DATE")); + registerFunctionModifier(SourceSystemFunctions.CURTIME, + new AliasModifier("CURRENT_TIME")); + // SYSTEM FUNCTIONS + // ////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.IFNULL, + new AliasModifier("NVL")); + + // DATA TYPE CONVERSION + // ///////////////////////////////////////// + ConvertModifier convertModifier = new ConvertModifier(); + convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); + convertModifier.addTypeMapping("byteint", FunctionModifier.BYTE); + convertModifier.addTypeMapping("smallint", FunctionModifier.SHORT); + convertModifier.addTypeMapping("bigint", FunctionModifier.LONG); + convertModifier.addTypeMapping("numeric(38)", + FunctionModifier.BIGINTEGER); + convertModifier.addTypeMapping("numeric(38,18)", + FunctionModifier.BIGDECIMAL); + convertModifier + .addTypeMapping("varchar(4000)", FunctionModifier.STRING); + + // /NO BOOLEAN, INTEGER, FLOAT, DATE, TIME, TIMESTAMP, as they are + // directly available in netezza + // /NO NULL, CLOB, BLOB, OBJECT, XML + + // /BOOLEAN--BYTE, SHORT, INTEGER, LONG, FLOAT, DOUBLE, BIGINTEGER, + // BIGDECIMAL--AS IT DOESN'T WORK IMPLICITLY IN NETEZZA + + + convertModifier.addConvert(FunctionModifier.FLOAT, FunctionModifier.BIGDECIMAL, new CastModifier("numeric")); + convertModifier.addConvert(FunctionModifier.DOUBLE, FunctionModifier.BIGDECIMAL, new CastModifier("numeric")); + + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.INTEGER, + new BooleanToNumericConversionModifier()); + convertModifier + .addConvert(FunctionModifier.BOOLEAN, FunctionModifier.BYTE, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.SHORT, + new BooleanToNumericConversionModifier()); + convertModifier + .addConvert(FunctionModifier.BOOLEAN, FunctionModifier.LONG, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.FLOAT, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.DOUBLE, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.BIGINTEGER, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.BIGDECIMAL, + new BooleanToNumericConversionModifier()); + convertModifier.addConvert(FunctionModifier.BOOLEAN, + FunctionModifier.STRING, + new BooleanToStringConversionModifier()); + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.BOOLEAN, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + Expression stringValue = function.getParameters() + .get(0); + return Arrays.asList("CASE WHEN ", stringValue, + " IN ('false', '0') THEN '0' WHEN ", + stringValue, " IS NOT NULL THEN '1' END"); + } + }); + convertModifier.addTypeConversion(new FunctionModifier() { + @Override + public List<?> translate(Function function) { + Expression stringValue = function.getParameters().get(0); + return Arrays.asList("CASE WHEN ", stringValue, + " = 0 THEN '0' WHEN ", stringValue, + " IS NOT NULL THEN '1' END"); + } + }, FunctionModifier.BOOLEAN); + + // //////STRING TO DATATYPE CONVERSION OTHER THAN DATE/TIME + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.INTEGER, new CastModifier("integer")); + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.FLOAT, new CastModifier("float")); + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.DOUBLE, new CastModifier("double")); + // /// STRING --> CHAR, BYTE, SHORT, LONG, BIGI, BIGD, BOOLEAN is taken + // care by Type Mapping + // /// NO conversion support for NULL, CLOB, BLOB, OBJECT, XML + // //STRING TO DATE/TIME CONVERSION//// + // //////////////////////////////////// + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.DATE, new ConvertModifier.FormatModifier( + "to_date", DATE_FORMAT)); + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.TIME, new ConvertModifier.FormatModifier( + "to_timestamp", TIME_FORMAT)); + convertModifier.addConvert(FunctionModifier.STRING, + FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier( + "to_timestamp", TIMESTAMP_FORMAT)); + // ////DATE/TIME INTERNAL CONVERSION///////// + convertModifier.addConvert(FunctionModifier.TIMESTAMP, + FunctionModifier.TIME, new CastModifier("TIME")); + convertModifier.addConvert(FunctionModifier.TIMESTAMP, + FunctionModifier.DATE, new CastModifier("DATE")); + convertModifier.addConvert(FunctionModifier.DATE, + FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); + // convertModifier.addConvert(FunctionModifier.TIME, + // FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); //TIME + // --> TIMESTAMP --DOESN't WORK IN NETEZZA-NO FUNCTION SUPPORT + + // //DATE/TIME to STRING CONVERION//// + // /////////////////////////////////// + convertModifier.addConvert(FunctionModifier.TIMESTAMP, + FunctionModifier.STRING, new ConvertModifier.FormatModifier( + "to_char", TIMESTAMP_FORMAT)); + // /NO NETEZAA FUNCTION for DATE, TIME to STRING + + convertModifier.setWideningNumericImplicit(true); + registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); + } + + @Override + public List<String> getSupportedFunctions() { + List<String> supportedFunctions = new ArrayList<String>(); + supportedFunctions.addAll(super.getSupportedFunctions()); + + // ////////////////////////////////////////////////////////// + // STRING FUNCTIONS + // //////////////////////////////////////////////////////// + supportedFunctions.add(SourceSystemFunctions.ASCII);// taken care with + // alias function + // modifier + supportedFunctions.add(SourceSystemFunctions.CHAR);// ALIAS to use 'chr' + supportedFunctions.add(SourceSystemFunctions.CONCAT); // ALIAS || + supportedFunctions.add(SourceSystemFunctions.INITCAP); + supportedFunctions.add(SourceSystemFunctions.LCASE);// ALIAS 'lower' +// supportedFunctions.add(SourceSystemFunctions.LPAD); + supportedFunctions.add(SourceSystemFunctions.LENGTH); + supportedFunctions.add(SourceSystemFunctions.LOCATE); // LOCATE FUNCTIO + // MODIFIER + supportedFunctions.add(SourceSystemFunctions.LTRIM); + // supportedFunctions.add(SourceSystemFunctions.REPEAT); +// supportedFunctions.add(SourceSystemFunctions.RPAD); + supportedFunctions.add(SourceSystemFunctions.RTRIM); + supportedFunctions.add(SourceSystemFunctions.SUBSTRING); // No Need of + // ALIAS as + // both + // substring + // and + // substr + // work in + // netezza + supportedFunctions.add(SourceSystemFunctions.UCASE); // ALIAS UPPER + // FUNCTION DIFFERENCE = "difference"; ///NO FUNCTION FOUND--DIFFERENCE + // FUNCTION INSERT = "insert"; + // supportedFunctions.add(SourceSystemFunctions.LEFT); //is this + // available or is it simply for LEFT OUTER JOIN? + // FUNCTION REPLACE = "replace"; // NO REPLACE Function + // supportedFunctions.add(SourceSystemFunctions.RIGHT);--is this + // available or is it simply for RIGHT OUTER JOIN? + // FUNCTION SOUNDEX = "soundex"; + // FUNCTION TO_BYTES = "to_bytes"; + // FUNCTION TO_CHARS = "to_chars"; + // //////// + // //////////////////////////////////////////////////////////////////// + // NUMERIC + // FUNCTIONS//////////////////////////////////////////////////////////// + // //////////////////////////////////////////////////////////////////////////// + // supportedFunctions.add(SourceSystemFunctions.ABS); +// supportedFunctions.add(SourceSystemFunctions.ACOS); +// supportedFunctions.add(SourceSystemFunctions.ASIN); +// supportedFunctions.add(SourceSystemFunctions.ATAN); +// supportedFunctions.add(SourceSystemFunctions.ATAN2); + supportedFunctions.add(SourceSystemFunctions.CEILING); // /ALIAS-ceil + supportedFunctions.add(SourceSystemFunctions.COS); + supportedFunctions.add(SourceSystemFunctions.COT); + supportedFunctions.add(SourceSystemFunctions.DEGREES); + // supportedFunctions.add(SourceSystemFunctions.EXP); + supportedFunctions.add(SourceSystemFunctions.FLOOR); + supportedFunctions.add(SourceSystemFunctions.LOG); +// supportedFunctions.add(SourceSystemFunctions.MOD); + supportedFunctions.add(SourceSystemFunctions.PI); + supportedFunctions.add(SourceSystemFunctions.POWER);// ALIAS-POW + supportedFunctions.add(SourceSystemFunctions.RADIANS); + supportedFunctions.add(SourceSystemFunctions.ROUND); + supportedFunctions.add(SourceSystemFunctions.SIGN); + supportedFunctions.add(SourceSystemFunctions.SIN); + supportedFunctions.add(SourceSystemFunctions.SQRT); + supportedFunctions.add(SourceSystemFunctions.TAN); + // FUNCTION TRANSLATE = "translate"; + // FUNCTION TRUNCATE = "truncate"; + // FUNCTION FORMATINTEGER = "formatinteger"; + // FUNCTION FORMATLONG = "formatlong"; + // FUNCTION FORMATDOUBLE = "formatdouble"; + // FUNCTION FORMATFLOAT = "formatfloat"; + // FUNCTION FORMATBIGINTEGER = "formatbiginteger"; + // FUNCTION FORMATBIGDECIMAL = "formatbigdecimal"; + // FUNCTION LOG10 = "log10"; + // FUNCTION PARSEINTEGER = "parseinteger"; + // FUNCTION PARSELONG = "parselong"; + // FUNCTION PARSEDOUBLE = "parsedouble"; + // FUNCTION PARSEFLOAT = "parsefloat"; + // FUNCTION PARSEBIGINTEGER = "parsebiginteger"; + // FUNCTION PARSEBIGDECIMAL = "parsebigdecimal"; + // supportedFunctions.add(SourceSystemFunctions.RAND); --Needs + // Alias--But, is it required to even have an alias??? + // /////////////////////////////////////////////////////////////////// + // BIT FUNCTIONS////////////////////////////////////////////////////// + // ALIAS FUNCTION MODIFIER IS APPLIED////////////////////////////// + supportedFunctions.add(SourceSystemFunctions.BITAND); + supportedFunctions.add(SourceSystemFunctions.BITOR); + supportedFunctions.add(SourceSystemFunctions.BITNOT); + supportedFunctions.add(SourceSystemFunctions.BITXOR); + // DATE FUNCTIONS + supportedFunctions.add(SourceSystemFunctions.CURDATE); + supportedFunctions.add(SourceSystemFunctions.CURTIME); + supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH); + supportedFunctions.add(SourceSystemFunctions.DAYOFYEAR); + supportedFunctions.add(SourceSystemFunctions.DAYOFWEEK); + supportedFunctions.add(SourceSystemFunctions.HOUR); + supportedFunctions.add(SourceSystemFunctions.MINUTE); + supportedFunctions.add(SourceSystemFunctions.MONTH); + supportedFunctions.add(SourceSystemFunctions.QUARTER); + supportedFunctions.add(SourceSystemFunctions.SECOND); + supportedFunctions.add(SourceSystemFunctions.WEEK); + supportedFunctions.add(SourceSystemFunctions.YEAR); + // FUNCTION DAYNAME = "dayname"; + // FUNCTION FORMATTIMESTAMP = "formattimestamp"; + // FUNCTION MODIFYTIMEZONE = "modifytimezone"; + // FUNCTION MONTHNAME = "monthname"; + // FUNCTION NOW = "now"; + // FUNCTION PARSETIMESTAMP = "parsetimestamp"; + // FUNCTION TIMESTAMPADD = "timestampadd"; + // FUNCTION TIMESTAMPCREATE = "timestampcreate"; + // FUNCTION TIMESTAMPDIFF = "timestampdiff"; + + // SYSTEM FUNCTIONS + supportedFunctions.add(SourceSystemFunctions.IFNULL); // ALIAS-NVL + supportedFunctions.add(SourceSystemFunctions.COALESCE); + supportedFunctions.add(SourceSystemFunctions.NULLIF); + + // CONVERSION functions + supportedFunctions.add(SourceSystemFunctions.CONVERT); + + // USER DEFINED FUNCTIONS + // supportedFunctions.add("USR_DEF_FUNC"); + + return supportedFunctions; + } + + 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), ")"); + } + } + + public static class BooleanToNumericConversionModifier extends + FunctionModifier { + @Override + public List<?> translate(Function function) { + Expression booleanValue = function.getParameters().get(0); + if (booleanValue instanceof Function) { + Function nested = (Function) booleanValue; + if (nested.getName().equalsIgnoreCase("convert") + && Number.class.isAssignableFrom(nested.getParameters() + .get(0).getType())) { + booleanValue = nested.getParameters().get(0); + } + } + return Arrays.asList("(CASE WHEN ", booleanValue, + " IN ( '0', 'FALSE') THEN 0 WHEN ", booleanValue, + " IS NOT NULL THEN 1 END)"); + } + + } + + public static class BooleanToStringConversionModifier extends + FunctionModifier { + @Override + public List<?> translate(Function function) { + Expression booleanValue = function.getParameters().get(0); + if (booleanValue instanceof Function) { + Function nested = (Function) booleanValue; + if (nested.getName().equalsIgnoreCase("convert") + && Number.class.isAssignableFrom(nested.getParameters() + .get(0).getType())) { + booleanValue = nested.getParameters().get(0); + } + } + return Arrays.asList("CASE WHEN ", booleanValue, + " = '0' THEN 'false' WHEN ", booleanValue, + " IS NOT NULL THEN 'true' END"); + } + + } + + 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 + ")"); + } + } + + @Override + public List<?> translateLimit(Limit limit, ExecutionContext context) { + if (limit.getRowOffset() > 0) { + return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", + limit.getRowOffset()); + } + return null; + } + + @Override + public boolean supportsCorrelatedSubqueries() { + return false; + } + + @Override + public boolean supportsIntersect() { + return true; + } + + @Override + public boolean supportsExcept() { + return true; + } + + @Override + public boolean supportsInlineViews() { + return true; + } + + @Override + public boolean supportsRowLimit() { + return true; + } + + @Override + public boolean supportsRowOffset() { + return true; + } + + @Override + public boolean supportsAggregatesEnhancedNumeric() { + return true; + } + + @Override + public boolean supportsFunctionsInGroupBy() { + return true; + } + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,509 @@ +/* + * 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.netezza; + +import java.math.BigDecimal; +import java.math.BigInteger; +import java.util.Arrays; + +import junit.framework.TestCase; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; + + +/** + */ +public class TestNetezzaConvertModifier extends TestCase { + + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + public TestNetezzaConvertModifier(String name) { + super(name); + } + + public String helpGetString(Expression expr) throws Exception { + NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); + trans.start(); + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + return sqlVisitor.toString(); + } + + public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", //$NON-NLS-1$ + Arrays.asList( + srcExpression, + LANG_FACTORY.createLiteral(tgtType, String.class)), + TypeFacility.getDataTypeClass(tgtType)); + + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, + expectedExpression, helpGetString(func)); + } + + + + + // Source = STRING + public void testStringToChar() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "char", "cast('5' AS char(1))"); + } + + public void testStringToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "boolean", "CASE WHEN '5' IN ('false', '0') THEN '0' WHEN '5' IS NOT NULL THEN '1' END"); + } + + public void testStringToByte() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "byte", "cast('5' AS byteint)"); + } + + public void testStringToShort() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "short", "cast('5' AS smallint)"); + } + + public void testStringToInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "integer", "cast('5' AS integer)"); + } + + public void testStringToLong() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "long", "cast('5' AS bigint)"); + } + + public void testStringToBigInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "biginteger", "cast('5' AS numeric(38))"); //$NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$ + } + + public void testStringToFloat() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "float", "cast('5' AS float)"); + } + + public void testStringToDouble() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "double", "cast('5' AS double)"); + } + + public void testStringToDate() throws Exception { + helpTest(LANG_FACTORY.createLiteral("2004-06-29", String.class), "date", "to_date('2004-06-29', 'YYYY-MM-DD')"); + } + + public void testStringToTime() throws Exception { + helpTest(LANG_FACTORY.createLiteral("23:59:59", String.class), "time", "to_timestamp('23:59:59', 'HH24:MI:SS')"); + } + + public void testStringToTimestamp() throws Exception { + helpTest(LANG_FACTORY.createLiteral("2004-06-29 23:59:59.987", String.class), "timestamp", "to_timestamp('2004-06-29 23:59:59.987', 'YYYY-MM-DD HH24:MI:SS.MS')"); + } + + public void testStringToBigDecimal() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "bigdecimal", "cast('5' AS numeric(38,18))"); + } + + // Source = CHAR + + public void testCharToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Character('5'), Character.class), "string", "'5'"); + } + + // Source = BOOLEAN + + public void testBooleanToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.TRUE, Boolean.class), "string", "CASE WHEN 1 = '0' THEN 'false' WHEN 1 IS NOT NULL THEN 'true' END"); + } + + public void testBooleanToByte() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "byte", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToShort() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "short", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "integer", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToLong() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "long", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToBigInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "biginteger", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToFloat() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "float", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToDouble() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "double", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToBigDecimal() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "bigdecimal", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + // Source = BYTE + + public void testByteToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testByteToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testByteToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "short", "1"); +// } +// +// public void testByteToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "integer", "integer(1)"); +// } +// +// public void testByteToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "long", "bigint(1)"); +// } +// +// public void testByteToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testByteToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "float", "cast(1 AS real)"); +// } +// +// public void testByteToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "double", "double(1)"); +// } +// +// public void testByteToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = SHORT + + public void testShortToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testShortToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testShortToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "byte", "1"); +// } +// +// public void testShortToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "integer", "integer(1)"); +// } +// +// public void testShortToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "long", "bigint(1)"); +// } +// +// public void testShortToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testShortToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "float", "cast(1 AS real)"); +// } +// +// public void testShortToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "double", "double(1)"); +// } +// +// public void testShortToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = INTEGER + + public void testIntegerToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testIntegerToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testIntegerToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "byte", "smallint(1)"); +// } +// +// public void testIntegerToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "short", "smallint(1)"); +// } +// +// public void testIntegerToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "long", "bigint(1)"); +// } +// +// public void testIntegerToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testIntegerToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "float", "cast(1 AS real)"); +// } +// +// public void testIntegerToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "double", "double(1)"); +// } +// +// public void testIntegerToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = LONG + + public void testLongToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testLongToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testLongToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "byte", "smallint(1)"); +// } +// +// public void testLongToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "short", "smallint(1)"); +// } +// +// public void testLongToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "integer", "integer(1)"); +// } +// +// public void testLongToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testLongToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "float", "cast(1 AS real)"); +// } +// +// public void testLongToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "double", "double(1)"); +// } +// +// public void testLongToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = BIGINTEGER + + public void testBigIntegerToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testBigIntegerToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testBigIntegerToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "byte", "smallint(1)"); +// } +// +// public void testBigIntegerToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "short", "smallint(1)"); +// } +// +// public void testBigIntegerToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "integer", "integer(1)"); +// } +// +// public void testBigIntegerToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "long", "bigint(1)"); +// } +// +// public void testBigIntegerToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "float", "cast(1 AS real)"); +// } +// +// public void testBigIntegerToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "double", "double(1)"); +// } +// +// public void testBigIntegerToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = FLOAT + + public void testFloatToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "string", "cast(1.2 AS varchar(4000))"); + } + + public void testFloatToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); + } + +// public void testFloatToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "byte", "smallint(1.2)"); +// } +// +// public void testFloatToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "short", "smallint(1.2)"); +// } +// +// public void testFloatToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "integer", "integer(1.2)"); +// } +// +// public void testFloatToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "long", "bigint(1.2)"); +// } +// +// public void testFloatToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "biginteger", "cast(1.2 AS numeric(31,0))"); +// } +// +// public void testFloatToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "double", "double(1.2)"); +// } +// +// public void testFloatToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); +// } + + // Source = DOUBLE + + public void testDoubleToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "string", "cast(1.2 AS varchar(4000))"); + } + + public void testDoubleToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); + } + +// public void testDoubleToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "byte", "smallint(1.2)"); +// } +// +// public void testDoubleToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "short", "smallint(1.2)"); +// } +// +// public void testDoubleToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "integer", "integer(1.2)"); +// } +// +// public void testDoubleToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "long", "bigint(1.2)"); +// } +// +// public void testDoubleToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "biginteger", "cast(1.2 AS numeric(31,0))"); +// } +// +// public void testDoubleToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "float", "cast(1.2 AS real)"); +// } +// +// public void testDoubleToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); +// } + + // Source = BIGDECIMAL + + public void testBigDecimalToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "string", "cast(1.0 AS varchar(4000))"); + } + + public void testBigDecimalToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "boolean", "CASE WHEN 1.0 = 0 THEN '0' WHEN 1.0 IS NOT NULL THEN '1' END"); + } + +// public void testBigDecimalToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "byte", "smallint(1.0)"); +// } +// +// public void testBigDecimalToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "short", "smallint(1.0)"); +// } +// +// public void testBigDecimalToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "integer", "integer(1.0)"); +// } +// +// public void testBigDecimalToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "long", "bigint(1.0)"); +// } +// +// public void testBigDecimalToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "biginteger", "cast(1.0 AS numeric(31,0))"); +// } +// +// public void testBigDecimalToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "float", "cast(1.0 AS real)"); +// } +// +// public void testBigDecimalToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "double", "double(1.0)"); +// } + +// // Source = DATE +// +// public void testDateToString() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "string", "char({d '2003-11-01'})"); +// } +// +// public void testDateToTimestamp() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "timestamp", "timestamp({d '2003-11-01'}, '00:00:00')"); +// } +// +// // Source = TIME +// +// public void testTimeToString() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "string", "char({t '23:59:59'})"); +// } +// +// public void testTimeToTimestamp() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "timestamp", "timestamp('1970-01-01', {t '23:59:59'})"); +// } +// +// // Source = TIMESTAMP +// +// public void testTimestampToString() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "string", "char({ts '2003-11-01 12:05:02.0'})"); +// } +// +// public void testTimestampToDate() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "date", "date({ts '2003-11-01 12:05:02.0'})"); +// } +// +// public void testTimestampToTime() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "time({ts '2003-11-01 12:05:02.0'})"); +// } + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,376 @@ +package org.teiid.translator.jdbc.netezza; + +import junit.framework.TestCase; + +import org.junit.Test; +import org.mockito.Mockito; +import org.teiid.cdk.unittest.FakeTranslationFactory; +import org.teiid.dqp.internal.datamgr.FakeExecutionContextImpl; +import org.teiid.language.Command; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.TranslatedCommand; +import org.teiid.translator.jdbc.TranslationHelper; + +public class TestNetezzaTranslatorCapabilities extends TestCase { + + private static NetezzaExecutionFactory TRANSLATOR; + private static ExecutionContext EMPTY_CONTEXT = new FakeExecutionContextImpl(); + + static { + try { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.start(); + } catch(TranslatorException e) { + e.printStackTrace(); + } + } + + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + public void helpTestVisitor(String input, String expectedOutput) throws TranslatorException { + // Convert from sql to objects + Command obj = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(input); + + TranslatedCommand tc = new TranslatedCommand(Mockito.mock(ExecutionContext.class), TRANSLATOR); + tc.translateCommand(obj); + + + // Check stuff + assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); + } + + + /////////BASIC TEST CASES FOR CAPABILITIES///////////// + ///////////////////////////////////////////////// + @Test + public void testRowLimit() throws Exception { + String input = "select intkey from bqt1.smalla limit 100"; + String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100"; + + helpTestVisitor( + input, + output); + + } + @Test + public void testSelectDistinct() throws Exception { + String input = "select distinct intkey from bqt1.smalla limit 100"; + String output = "SELECT DISTINCT SmallA.IntKey FROM SmallA LIMIT 100"; + + helpTestVisitor( + input, + output); + } + @Test + public void testSelectExpression() throws Exception { + String input = "select intkey, intkey + longnum / 2 as test from bqt1.smalla"; + String output = "SELECT SmallA.IntKey, (SmallA.IntKey + (SmallA.LongNum / 2)) AS test FROM SmallA"; + + helpTestVisitor( + input, + output); + } + + public void testBetweenCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum BETWEEN 2 AND 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum >= ? AND SmallA.IntNum <= ?"; + + helpTestVisitor( + input, + output); + } + public void testCompareCriteriaEquals() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum = 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum = ?"; + + helpTestVisitor( + input, + output); + } + public void testCompareCriteriaOrdered() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < ?"; + + helpTestVisitor( + input, + output); + } + public void testLikeCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey like '4%'"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE ?"; + + helpTestVisitor( + input, + output); + } + public void testLikeWithEscapeCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey like '4\\%'"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE ?"; + + helpTestVisitor( + input, + output); + } + + public void testInCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey IN ('10', '11', '12')"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN (?, ?, ?)"; + + helpTestVisitor( + input, + output); + } + public void testInCriteriaSubQuery() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey IN (select stringkey from bqt1.smalla where intkey < 10)"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey < ?)"; + + helpTestVisitor( + input, + output); + } + + public void testIsNullCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum IS NULL"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NULL"; + + helpTestVisitor( + input, + output); + } + @Test public void testOrCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < 2 OR intnum > 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < ? OR SmallA.IntNum > ?"; + + helpTestVisitor( + input, + output); + } + @Test public void testIsNotNullCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum IS NOT NULL"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NOT NULL"; + + helpTestVisitor( + input, + output); + } + + @Test public void testExistsCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where exists (select intkey from bqt1.smallb)"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE EXISTS (SELECT SmallB.IntKey FROM SmallB)"; + + helpTestVisitor( + input, + output); + } + @Test public void testHavingClauseCriteria() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT INTKEY FROM BQT1.SMALLA WHERE STRINGKEY = 20)"; + String output = "SELECT SmallA.IntKey FROM SmallA GROUP BY SmallA.IntKey HAVING SmallA.IntKey = (SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey = ?)"; + + helpTestVisitor( + input, + output); + } + + @Test public void testScalarSubQuery() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < (0.01 * (select sum(intnum) from bqt1.smalla ))"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.010000000000000 * (SELECT SUM(SmallA.IntNum) FROM SmallA))"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSimpleCaseExpression() throws Exception { + String input = "SELECT stringnum, intnum, CASE BOOLEANVALUE WHEN 'true' then 'true' WHEN false THEN 'FALSE' ELSE 'GOOD' END FROM bqt1.smalla;"; + String output = "SELECT SmallA.StringNum, SmallA.IntNum, CASE WHEN SmallA.BooleanValue = ? THEN 'true' WHEN SmallA.BooleanValue = ? THEN 'FALSE' ELSE 'GOOD' END FROM SmallA"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSearchedCaseExpression() throws Exception { + String input = "SELECT AVG(CASE WHEN intnum > 10 THEN intnum ELSE intkey END) \"Average\" FROM bqt1.smalla"; + String output = "SELECT AVG(CASE WHEN SmallA.IntNum > ? THEN SmallA.IntNum ELSE SmallA.IntKey END) AS Average FROM SmallA"; + + helpTestVisitor( + input, + output); + } + + @Test public void testQuantifiedCompareSOMEorANY() throws Exception { + String input = "SELECT INTKEY, BYTENUM FROM BQT1.SmallA WHERE BYTENUM = ANY (SELECT BYTENUM FROM BQT1.SmallA WHERE BYTENUM >= '-108')"; + String output = "SELECT SmallA.IntKey, SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum = SOME (SELECT SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum >= ?)"; + + helpTestVisitor( + input, + output); + } + + @Test public void testQuantifiedCompareALL() throws Exception { + String input = "SELECT INTKEY, STRINGKEY FROM BQT1.SMALLA WHERE STRINGKEY = ALL (SELECT STRINGKEY FROM BQT1.SMALLA WHERE INTKEY = 40)"; + String output = "SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA WHERE SmallA.StringKey = ALL (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey = ?)"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSelfJoin() throws Exception { + String input = "SELECT x.intnum, y.intkey FROM bqt1.smalla x, bqt1.smalla y WHERE x.stringnum = y.intnum;"; + String output = "SELECT x.IntNum, y.IntKey FROM SmallA AS x, SmallA AS y WHERE x.StringNum = cast(y.IntNum AS varchar(4000))"; + + helpTestVisitor( + input, + output); + } + + @Test public void testLimitWithNestedInlineView() throws Exception { + String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; + String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY SmallA.IntKey LIMIT 100) AS x GROUP BY x.stringkey"; + + helpTestVisitor( input, output); + } + + @Test public void testAggregatesAndEnhancedNumeric() throws Exception { + String input = "select count(*), min(intkey), max(intkey), sum(intkey), avg(intkey), count(intkey), STDDEV_SAMP(intkey), STDDEV_POP(intkey), VAR_SAMP(intkey), VAR_POP(intkey) from bqt1.smalla"; + String output = "SELECT COUNT(*), MIN(SmallA.IntKey), MAX(SmallA.IntKey), SUM(SmallA.IntKey), AVG(SmallA.IntKey), COUNT(SmallA.IntKey), STDDEV_SAMP(SmallA.IntKey), STDDEV_POP(SmallA.IntKey), VAR_SAMP(SmallA.IntKey), VAR_POP(SmallA.IntKey) FROM SmallA"; + + helpTestVisitor( input, output); + } + @Test public void testAggregatesDistinct() throws Exception { + String input = "select avg(DISTINCT intnum) from bqt1.smalla"; + String output = "SELECT AVG(DISTINCT SmallA.IntNum) FROM SmallA"; + + helpTestVisitor( input, output); + } + + + @Test public void testExceptAsMinus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla except select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA EXCEPT SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + @Test public void testUnionAsPlus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla union select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + @Test public void testUnionAllAsPlus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla union all select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION ALL SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + @Test public void testUnionAllAsPlusWithAggregates() throws Exception { + String input = "select intkey, Sum(intnum) from bqt1.smalla group by intkey union all select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SUM(SmallA.IntNum) FROM SmallA GROUP BY SmallA.IntKey UNION ALL SELECT SmallB.IntNum, SmallB.IntKey AS IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + + @Test public void testintersect() throws Exception { + String input = "select intkey from bqt1.smalla where intkey < 20 INTERSECT select intkey from bqt1.smalla where intkey > 10"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey < ? INTERSECT SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey > ?"; + + + helpTestVisitor( input, output); + } + + + @Test public void testUnionOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) union select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + @Test public void testIntersectOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) intersect select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA INTERSECT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + @Test public void testExceptOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) except select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA EXCEPT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + + @Test public void testRowLimitOFFSET() throws Exception { + String input = "select intkey from bqt1.smalla limit 20, 30"; + String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 30 OFFSET 20"; + + helpTestVisitor( input, output); + } + + + @Test public void testOrderByNullsFirstLast() throws Exception { + String input = "select intkey, longnum from bqt1.smalla order by longnum NULLS LAST"; + String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.LongNum NULLS LAST"; + + helpTestVisitor( input, output); + } + + @Test public void testOrderByUnRelated() throws Exception { + String input = "select intkey, longnum from bqt1.smalla order by floatnum"; + String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.FloatNum"; + + helpTestVisitor( input, output); + } + + + @Test public void testInnerJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= ? AND SmallB.IntKey >= ? ORDER BY SmallA.IntKey"; + + helpTestVisitor( input, output); + } + + + @Test public void testOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= ? AND SmallB.IntKey >= ? ORDER BY SmallA.IntKey"; + + helpTestVisitor( input, output); + } + + @Test public void testFullOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA FULL OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA FULL OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; + + helpTestVisitor( input, output); + } + + @Test public void testRightOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA RIGHT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT2.SmallB.IntNum"; + String output= "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallB LEFT OUTER JOIN SmallA ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallB.IntNum"; + + helpTestVisitor( input, output); + } + @Test + public void testLeftOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA LEFT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA LEFT OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; + + helpTestVisitor( input, output); + } + + + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,141 @@ +/* + * 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.netezza; + +import static org.junit.Assert.assertEquals; + +import java.util.Arrays; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.Expression; +import org.teiid.language.Function; +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; + +/** + */ +public class TestNetezzaTranslatorDatetimeConversion { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + private String getTestVDB() { + //return TranslationHelper.NETEZZA_VDB; + return TranslationHelper.PARTS_VDB; + } + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + 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(); + } + + + ///////////////DATE/TIME CONVERSION TESTCASES/////// + //////////////////////////////////////////////////// + + @Test public void testdayofmonth() throws Exception { + String input = "SELECT dayofmonth(datevalue) FROM BQT1.SMALLA"; + String output = "SELECT extract(DAY from SmallA.DateValue) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + ///BEGIN--FROM TIMESTAMP->DATE, TIME, STRING//////// + @Test public void testTimestampToDate() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, date), string) FROM BQT1.SMALLA"; + String output = "SELECT cast(cast(SmallA.TimestampValue AS DATE) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + @Test public void testTimestampToTime() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; + String output = "SELECT cast(cast(SmallA.TimestampValue AS TIME) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + @Test public void testTimestampToString() throws Exception { + String input = "SELECT convert(timestampvalue, string) FROM BQT1.SMALLA"; + String output = "SELECT to_char(SmallA.TimestampValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM TIMESTAMP->DATE, TIME, STRING//////// + + ///BEGIN--FROM DATE->TIMESTAMP//////// + @Test public void testDateToTimestamp() throws Exception { + String input = "SELECT convert(convert(datevalue, timestamp), string) FROM BQT1.SMALLA"; + String output = "SELECT to_char(cast(SmallA.DateValue AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM DATE->TIMESTAMP//////// + + ///BEGIN--FROM TIME->TIMESTAMP//////// + @Test public void testTimeToTimestamp() throws Exception { + String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; + //String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; + String output = "SELECT to_char(SmallA.TimeValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM TIME->TIMESTAMP//////// + + +// @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)"); +// } + + + + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,243 @@ +/* + * 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.netezza; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.TranslationHelper; + +/** + */ +public class TestNetezzaTranslatorSourceSystemFunctions { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + + /////SOURCESYSTEM FUNCTION TESTCASES////////////// + /////////////////////////////////////////////// + + + //////////////////BEGIN---STRING FUNCTIONS TESTCASES/////////////////// + + @Test + public void testLcaseUcase() throws Exception { + String input = "select lcase(StringKey), ucase(StringKey) FROM BQT1.SmallA"; + String output = "SELECT lower(SmallA.StringKey), upper(SmallA.StringKey) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output,TRANSLATOR); + } + @Test public void testPad() throws Exception { + String input = "select lpad(smalla.stringkey, 18), rpad(smalla.stringkey, 12) from bqt1.smalla"; //$NON-NLS-1$ + String output = "SELECT lpad(SmallA.StringKey, 18), rpad(SmallA.StringKey, 12) FROM SmallA"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + + @Test + public void testIFNull() throws Exception { + String input = "SELECT ifnull(StringKey, 'otherString') FROM BQT1.SmallA"; + String output = "SELECT NVL(SmallA.StringKey, 'otherString') FROM SmallA"; + //SELECT IFNULL(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT + //SELECT nvl(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + @Test public void testSubstring1() throws Exception { + + //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// + ////////////////////////////////////////////////////// + String input = "SELECT substring(StringKey, 1) FROM BQT1.SmallA"; + String output = "SELECT substring(SmallA.StringKey, 1) FROM SmallA"; + //SELECT substring(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT + //SELECT substr(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + @Test public void testSubstring2() throws Exception { + + //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// + ////////////////////////////////////////////////////// + String input = "SELECT substring(StringKey, 1, 5) FROM BQT1.SmallA"; + String output = "SELECT substring(SmallA.StringKey, 1, 5) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + + @Test public void testConcat_withLiteral() throws Exception { +// String sql = "select stringnum || '1' from BQT1.Smalla"; +// String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; +// helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); + String input = "select stringnum || '1' from BQT1.Smalla"; + String output = "SELECT (SmallA.StringNum || '1') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + + ////BEGIN-LOCATE FUNCTION + @Test public void testLocate() throws Exception { + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; + String output = "SELECT INSTR('234567890', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA WHERE SmallA.IntKey = 26"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; + String output = "SELECT 1 FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, 1) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN (INSTR('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (INSTR('chimp', SmallA.StringNum) + 1) END) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + ////END-LOCATE FUNCTION + + + + + //////////////////BEGIN---NUMERIC FUNCTIONS TESTCASES/////////////////// + @Test public void testCeil() throws Exception { + //select ceiling(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT + //select ceil(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT + String input = "SELECT ceiling(sqrt(INTKEY)) FROM BQT1.SMALLA"; + String output = "SELECT ceil(sqrt(SmallA.IntKey)) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testPower() throws Exception { + + //select power(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT + //select pow(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT + String input = "SELECT power(INTKEY, 2) FROM BQT1.SMALLA"; + String output = "SELECT pow(SmallA.IntKey, 2) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + //////////////////END---NUMERIC FUNCTIONS TESTCASES/////////////////// + + + //////////////////BEGIN---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// + /////////////////////////////////////////////////////////////////// + + @Test public void testBitAnd() throws Exception { + String input = "select bitand(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNand(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitNot() throws Exception { + String input = "select bitnot(intkey) from bqt1.smalla"; + String output = "SELECT intNnot(SmallA.IntKey) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitOr() throws Exception { + String input = "select bitor(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitXor() throws Exception { + String input = "select bitxor(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNxor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + //////////////////END---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// + ///////////////////////////////////////////////////////////////////////////// + + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,246 @@ +/* + * 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.netezza; + +import static org.junit.Assert.assertEquals; + +import java.util.Arrays; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.Expression; +import org.teiid.language.Function; +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; + + +public class TestNetezzaTranslatorTypeMapping { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + private String getTestBQTVDB() { + + return TranslationHelper.BQT_VDB; + } + + + 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(); + } + + + /////////TYPE MAPPING TESTS///////// + /////////////////////////////////// + + @Test public void testCHARtoChar1() throws Exception { + String input = "SELECT convert(StringKey, CHAR) FROM BQT1.SmallA"; + String output = "SELECT cast(SmallA.StringKey AS char(1)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testLongToBigInt() throws Exception { + String input = "SELECT convert(convert(StringKey, long), string) FROM BQT1.SmallA"; + String output = "SELECT cast(cast(SmallA.StringKey AS bigint) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToByte() throws Exception { + String input = "SELECT char(convert(stringnum, byte) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS byteint) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToShort() throws Exception { + String input = "SELECT char(convert(stringnum, short) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS smallint) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToLong() throws Exception { +// String input = "SELECT char(convert(PART_WEIGHT, long) + 100) FROM PARTS"; +// String output = "SELECT chr((cast(PARTS.PART_WEIGHT AS bigint) + 100)) FROM PARTS"; + String input = "SELECT convert(stringnum, long) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS bigint) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToBiginteger() throws Exception { + String input = "SELECT convert(stringnum, BIGINTEGER) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS numeric(38)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToBigdecimal() throws Exception { + String input = "SELECT convert(stringnum, BIGDECIMAL) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS numeric(38,18)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToVarchar() throws Exception { + String input = "SELECT convert(intkey, STRING) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.IntKey AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + + + + ////NON-MAPPED TYPES TEST///////////// + ////////////////////////////////////// + + @Test public void testStringToInteger() throws Exception { + String input = "SELECT char(convert(stringnum, integer) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS integer) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToFloat() throws Exception { + String input = "SELECT convert(stringnum, float) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToReal() throws Exception { + String input = "SELECT convert(stringnum, real) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToDouble() throws Exception { + String input = "SELECT convert(stringnum, double) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS double) FROM SmallA"; + //SELECT cast(MEAS_PRD_ID AS DOUBLE) from ACTG_UNIT_BAL_FACT + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToBoolean() throws Exception { + String input = "SELECT convert(stringnum, boolean) FROM BQT1.SMALLA"; + String output = "SELECT CASE WHEN SmallA.StringNum IN ('false', '0') THEN '0' WHEN SmallA.StringNum IS NOT NULL THEN '1' END FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToDate() throws Exception { + String input = "SELECT convert(stringnum, date) FROM BQT1.SMALLA"; + String output = "SELECT to_date(SmallA.StringNum, 'YYYY-MM-DD') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToTime() throws Exception { + String input = "SELECT convert(stringnum, time) FROM BQT1.SMALLA"; + String output = "SELECT to_timestamp(SmallA.StringNum, 'HH24:MI:SS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToTimestamp() throws Exception { + String input = "SELECT convert(stringnum, timestamp) FROM BQT1.SMALLA"; + String output = "SELECT to_timestamp(SmallA.StringNum, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + + @Test public void testbooleanToIntegerConversion() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntNum = convert(BQT2.SmallB.booleanvalue, integer)"; + String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.IntNum = (CASE WHEN SmallB.BooleanValue IN ( '0', 'FALSE') THEN 0 WHEN SmallB.BooleanValue IS NOT NULL THEN 1 END)"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + + + } + + @Test public void testIntegerToBooleanconversion() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.booleanvalue = convert(BQT2.SmallB.IntNum, boolean) AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.BooleanValue = CASE WHEN SmallB.IntNum = 0 THEN '0' WHEN SmallB.IntNum IS NOT NULL THEN '1' END AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntNum"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + + + } + + +} Added: branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java =================================================================== --- branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java (rev 0) +++ branches/7.1.x/connectors/translator-netezza/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java 2011-11-10 17:48:21 UTC (rev 3631) @@ -0,0 +1,98 @@ +/* + * 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.netezza; + +import java.util.Arrays; + +import junit.framework.TestCase; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +/** + */ +public class TestSubstringFunctionModifier extends TestCase { + + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + + /** + * Constructor for TestSubstringFunctionModifier. + * @param name + */ + public TestSubstringFunctionModifier(String name) { + super(name); + } + + public void helpTestMod(Expression[] args, String expectedStr) throws Exception { + Function func = LANG_FACTORY.createFunction("substring", + Arrays.asList(args), TypeFacility.RUNTIME_TYPES.STRING); + + NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); + trans.start(); + + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(func); + + assertEquals(expectedStr, sqlVisitor.toString()); + } + + public void testTwoArgs() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', 1)"); + } + + public void testThreeArgsWithConstant() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(new Integer(3), Integer.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', 3, 1)"); + } + + public void testThreeArgsWithElement() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createColumnReference("e1", null, null, Integer.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', e1, 1)"); + } + + public void testThreeArgsWithNull() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(null, Integer.class), + LANG_FACTORY.createLiteral(new Integer(5), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', NULL, 5)"); + } + +}